RSS

#29 Dynamic Audit Trail on any MSSQL Table


Recently one of my client asked me to have audit trail on their ERP data on creditor and debtor master. BY Default in the system there was no such Audit trail available. So I end up creating a trigger that will capture the detail of any column changes in debtor and creditor table and store in a separate table called “AUDIT_TRAIL”.

I made this trigger as dynamic as possible so that this can be used on any table that we want to Audit. I am sharing following script that will create a trigger on “DR_ACCS” table.

 

‘*********************************************************************

CREATE TRIGGER [dbo].[Audit_DRACCS]

ON [dbo].[DR_ACCS]

FOR UPDATE

AS

BEGIN

SET NOCOUNT ON

/* */

— Declare Variables

Declare @LoopCounter INT, @MaxCOL INT, @COLNAME varchar(50)

Declare @OLDVALUE varchar(100), @NEWVALUE varchar(100)

Declare @SQLSTR as varchar(500)

 

if not exists (select * from sysobjects where name=’temp_GlobalVariables’ and xtype=’U’)

CREATE TABLE temp_GlobalVariables( Oldvalue VARCHAR(100),Newvalue Varchar(100))

 

if not exists (select * from sysobjects where name=’AUDIT_TRAIL’ and xtype=’U’)

CREATE TABLE [AUDIT_TRAIL]([SEQNO] [int] IDENTITY(1,1) NOT NULL,[TABLENAME] [varchar](60) NULL,[LINE_SEQNO] [int] NULL,[FIELDNAME] [varchar](60) NULL,[OLD_VALUE] [varchar](100) NULL,[NEW_VALUE] [varchar](100) NULL,[MODIFIEDBY] [varchar](100) NULL,[MODIFIEDDATE] [datetime] NULL,PRIMARY KEY CLUSTERED ([SEQNO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

 

SELECT @LoopCounter = Min(ORDINAL_POSITION), @MaxCOL = max(ORDINAL_POSITION) FROM

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’DR_ACCS’

 

WHILE(@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxCOL)

BEGIN

SELECT @COLNAME = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = @LoopCounter and TABLE_NAME = N’DR_ACCS’

Set @SQLSTR = ‘Delete from temp_GlobalVariables’

EXEC(@SQLSTR)

if exists (select * from sysobjects where name=’TEMPACC’ and xtype=’U’)   DROP table TEMPACC

 

Select * into TEMPACC from Deleted

 

Set @SQLSTR =’Insert into temp_GlobalVariables (Oldvalue,Newvalue) (select D.’ + @COLNAME + ‘,I.’ + @COLNAME + ‘ from TEMPACC D inner join DR_ACCS I on D.ACCNO = I.ACCNO)’

EXEC(@SQLSTR)

 

if (Select top 1 Oldvalue from temp_GlobalVariables)<> (Select top 1 Newvalue from temp_GlobalVariables) and @COLNAME not in (‘LAST_UPDATED’,’AGEDBAL0′,’AGEDBAL1′,’AGEDBAL2′,’BALANCE’,’LASTMONTH’,’MONTHVAL’,’PRIOR_AGEDBAL0′,’PRIOR_BALANCE’,’YEARVAL’)

Begin

INSERT INTO [dbo].[AUDIT_TRAIL] ([TABLENAME],[LINE_SEQNO],[FIELDNAME],[OLD_VALUE],[NEW_VALUE],[MODIFIEDBY],[MODIFIEDDATE])

(Select ‘DR_ACCS’,i.ACCNO, @COLNAME , (Select top 1 Oldvalue from temp_GlobalVariables), (Select top 1 Newvalue from temp_GlobalVariables),HOST_NAME(),GETDATE() from TEMPACC I inner join DR_ACCS D on D.ACCNO = I.ACCNO )

End

SET @LoopCounter = @LoopCounter + 1

END

END

 

 

‘****************************************************************************

 

This trigger automatically create table called “Audit_Trail” if not there in the database. You can also avoid any column that you do not want to track by adding a column name in the “if condition”.

Note: Yes this process is resource incentive and will affect the performance of updating the data in respective table. So it is advised to not apply this on the transaction table but if required you can.

I have written this in short time so there may be lots of improvement options that will make this trigger efficient.

Aggyey

 
Leave a comment

Posted by on May 10, 2016 in ERP

 

#28 On-Premises or cloud based ERP


Date: 16 June 2015

Back in 90s when many Accountants and book keepers getting themselves familiarized with new Information technology world and learning new solutions, it was considered that if somehow one is entering and saving the day-to-day transactions in the computer he is up to date with the changing technology world. Right so, because the comparison was between book keeping on the hard ledger books and data stored in computers. But after about 2 decade that is not true. If one is only saving their day-to-day business transaction on a server and getting Basic reports out from the data that he has entered, he is not utilizing and keeping up with the new and advance technology rather he is just surviving in this era of globalization.

Let’s look at how the change in technology has shaped the commerce around the world. Without any doubt one of the significant achievement of technological advancement is that it has almost removed the barrier of physical distance between people. You can easily communicate, transfer large amount of data and information, doing any type of transaction from any part of world and is now on our finger tips. This only indicate that even a small business or a startup is no longer limited to the geographical area that it will do business in. A small startup in any third world country can do its business on a global scale and sell it’s product and services to anyone around the globe who is connected to the internet/Cloud. One other thing that is shaping the world since a decade is social media. More and more data and information are today shared via social media. All the successful business are using the information and data available on social media to draft there strategies and make business decisions (it’s a Part of Business Intelligence that I will cover in another post).

Now coming back to the topic of this post. On-premises ERP is the one that is installed and configured on the server which is located in the same premises or at the location that is connected via LAN. A workstation or a client software is installed on the end user machine which is used to key in data and/or generate required report for the data stored in the database of the solution. On the other hand Cloud based ERP is the one where the Complete ERP Package is hosted on the server that is accessible to anyone via internet. Its web based so only Web browser is required to use the application.

Comparison between on-premises and Cloud ERP solution

1. Accessibility: One of the biggest advantage that one can get using a cloud base ERP is the accessibility. The performance of Enterprise solution is no longer be dependent on the devise that is used. It all depend on the Internet that is been used and the platform where the Cloud ERP is hosted.

Capture

On the other hand On-Premises ERP is hosted on a local server and it can be accessed via PC or a devise where the client is installed. Different devices may causes issues and even different version of Operating System may have issues running the ERP. If the ERP was not supporting Linux or Macintosh then it will not work on those Operating system and hence the company’s options are limited to choose the devices and Operating systems.

2. System upgrades: Every Enterprise solution releases system upgrades or new versions periodically. In case of on-premises ERP we need to upgrade or install whole Solution again and also install workstation/client on every machine using ERP. It can be a time consuming and expensive exercise if there are lots of custom written applications or reports. On the other hand with Cloud base ERP customer/Users do not need to worry about new and upgraded version of ERP as the Upgrades and versions are maintained by the providers itself. Companies and Users will get the latest version as and when it is release by provider. It also will not cost any extra amount for the company to use latest version of the application it just be the part of their subscription.

3. Hardware Maintenance: The performance of the on-premises ERP is very much depend on the resources available on the server where ERP in installed. Also each workstations need to fulfill minimum system requirement in order to run the solution efficiently. Whereas the cloud base ERP just need a web browser to run the solution. The performance of the Cloud base ERP will be based on the speed of the Internet. This means moving to Cloud will reduce the hardware maintenance cost and company do not need to spend anything on IT specialist and so not need to buy any expensive Servers and work stations. They only need a device to run a browser to take advantage of all the features provided by Cloud base ERP solution. Since only Web browser is required to run the ERP solution now company do not even need to stick to any expensive Operating system. They can even chose any Open source Operating system.

4. Implementation and Scalability: As compare to On-premises ERP solution the Time of Implementation cloud base solution is less. No Setup or installation is require so from day one ERP id up and running and users can start using it. Similarly with the growth of an organization it’s easier for cloud base ERP to adapt and fulfill growing needs of the business as compare to on-premises ERP Solution.

5. Globalization: If a company has its operations in multiple locations or in different countries it’s far easier for Cloud base ERP solution to adapt as compare to on-Premises ERP. No extra infrastructure is required to fulfill the need of people located in different locations and working for same company.

6. Security: In case of On-Premises ERP the security of data is the responsibility of the organization itself. The company’s data is as secure as there network are. However the responsibility of the data security in case of cloud base ERP is in the hands of provider.

Aggyey

 
Leave a comment

Posted by on June 19, 2015 in Cloud ERP

 

Tags: , ,

# 27 Cloud Computing and its future


Recently I read a phrase somewhere that says “You can survive today with the technology of yesterday but you cannot operate tomorrow”.

In last 2 decade, technology has changed a lot. Mankind progressing exponentially and the growth of last 2 decade is more than past millennia. As a part of this advancement, ERPs also provide new and improved tools for Enterprise functioning and decision making. The latest in this trend in Cloud based ERP solutions.

What is this Cloud computing?

A simple explanation is that it is a metaphor for Internet. When we do anything on Internet it means we are doing it in cloud. It range from simply browsing the internet to storing information or data over Internet. Cloud is nothing but the representation of something hosted somewhere on the server that can be accessed via internet from anywhere in the world.

What cloud is not about is your hard disk or software installed on your computer locally. Cloud is all about internet so accessibility and speed depends on the speed and availability of internet.

Cloud in business

There are different types of cloud that business can use. (SAAS) software as a service where a business subscribe to a software service that it uses over Internet. There are examples like Salesforce.com, Dropbox, NetSuite, Acumatica etc. There is also (IAAS) Infrastructure as a service where a backbone like servers, storage space etc. can be supplied by another company. Small companies can also opt for (PAAS) Platform as service where they can host there application sand software on a remote data centers.

This concept of cloud computing is growing and gaining popularity significantly. There are pro and cons of using cloud technology. In coming years we will see more and more application hosted as service.

Aggyey

 
Leave a comment

Posted by on June 9, 2015 in Accpac Hot Fix

 

#26 Sage Project and job Costing Technical Information


Date: 2 June 2013

Because Sage 300 ERP has the capacity to run on local area networks, there may be times when more than one user needs to use the same Project and Job Costing database. Sage 300 ERP has two basic rules for protecting data integrity when workstations share the same data:

• Make sure no more than one user is working in the same batch at the same time.
• Make sure no one is working in a batch that you want to print or post.

Sage 300 ERP uses the following access types to protect data in a multiuser environment:

Global Locks – While you run a global lock process, no other process can be run on the system until processing is finished.
Single-User Processes – While you run any one of the processes in this group, no other process in the group can be run. However, users may work in other Project and Job Costing forms not included in the group.
Passive Concurrency (Multiuser Data Protection) – You can run any of these processes while others are using them, but Project and Job Costing protects the data by preventing more than one user from making changes to the same record at the same time.

Aggyey

 

Tags: ,

#25 Periodic Processing in Sage 300 ERP PJC Module


Date: 26 May 2013

This post describes the various processing and reporting tasks you perform at period‐end or at other regular intervals in your Project and Job Costing system. The icons available to do the periodic task in PJC are:-

1

• You use icons from the A/R Billing folder to generate, edit, and post entries for invoice batches that Project and Job costing sends to Accounts Receivable.
• You use icons in the Revenue Recognition folder to generate and post entries to recognize project revenues and matching costs.
• You use icons in the Reopen Closed Projects folder to create and post worksheets that reopen projects and reverse revenue that was recognized on completion of the projects.
• If you do not use the option to create general ledger transactions during posting, you choose the Create G/L Batch icon from the Periodic Processing folder to produce batches of general ledger transactions.
• You use the Clear History icon to remove from your database printed posting journals, posted documents, closed contracts, transactions for closed contracts, and posting error journals that you no longer need.

Icons from A/R Billing folder:

2

• You use the Create Billing Worksheet form to generate invoice entries for projects that you want to bill. The entries are added to a billing worksheet, which you can view, edit, and post.
• The Billing Worksheet form lets you edit the invoice entries you generated using the Create Billing Worksheet form. You can change the debit amount and the credit amount, as well as the percentage complete (for projects that use a percentage completion accounting method). For some time and materials transactions, you can change a non-billable detail to billable. The Billing Worksheet form also lets you post individual worksheets.
• You use the Post Billing Worksheets form to post a range of billing worksheets. Unless you use the option to append invoices to an existing invoice batch, Project and Job Costing sends each worksheet as a separate invoice batch to Accounts Receivable.

Icons from Revenue Recognition:

3

• You use the Create Revenue Recognition Worksheet form to generate entries to recognize revenue for all your projects or for a selected range of projects or project categories. Revenue recognition also closes projects that you have marked Completed.
• You use the Revenue Recognition Worksheet form to review the revenue recognition entries before posting them. For projects that use a percentage complete accounting method, you can change the percentage complete, and then recalculate the amounts. Once you are satisfied that the entries are correct, you post the entries from the Revenue Recognition Worksheet form.

Icons from Reopen Closed Project:

4

• You use the Create Reopen Projects Worksheet form to select closed projects that you want to reopen and to generate reversing entries for any revenue that was recognized on completion of the projects. The entries are added to a Reopen Projects Worksheet that you can view, edit, and post.
• You use the Reopen Projects Worksheet form to check or delete the entries generated by the Create Reopen Projects Worksheet form. You also use the form to post the worksheet.

Note: You can close a project that has outstanding retainage payable or retainage receivable, however, you will have to reopen the project if you need to post retainage invoices, later.

Aggyey

 

Tags: , , ,

#24 SAGE 300 ERP PROJECT AND JOB COSTING – CONTRACT MANAGEMENT


Date: 13 MAY 2013

In Sage 300 ERP Project and Job Costing (PJC), Contract Maintenance form is used to add and maintain the contract and their associated projects and categories. Before adding a new contract the following records must be added in PJC:-
• Account Sets
• Projects
• Categories
• Employees
• Equipment
• Subcontractors
• Miscellaneous Expenses
• Overhead Expenses
• Charges
• Optional Fields
Also if you plan to raise Job Related invoices in Account Receivable with items then you have to create items in Account Related. For General Ledger you need to create the following accounts in the Chart of Accounts:-
• Work In Progress
• Cost of Sales
• Billings
• Revenue
• Payroll Expenses
• Employee Expenses
• Labour
• Overhead
• Equipment
• Cost
The Contract Maintenance form is used to perform the following tasks:-
• Specify the contract setting, for example when to recognise revenue, Project type etc.
• Default tax group.
• Assigning Projects (i.e. the revenue stream) to the contract.
• Assign resources and cost categories to the projects within a contract. Further PJC will match the cost with the revenue they generate .
• Enter estimates for the projects that will make up the estimate of the contract as a whole.
• Allow to change the status of the contract and projects.
• Use project, category and resource to track the cost for each element of the contract.
• View the summary information for contract and individual projects.
You can use Contract Maintenance form to create and maintain the contract, project and categories. As shown in the figure below:-

ContractMaintain

By clicking the Zoom button (Blue triangle button on the top) you can open a wizard where you can specify the default setup to open a new contract. Then you can add a new project to the contract and categories to the project. As a visual cue the project icons are in different colours depending on their status. The detail is as follows:-

PJCFolder

At the time of defining new contract it will pick all the setting from the options screen in the PJC Settings, but you can change the settings at contract and at project level.

To be Continue…

Aggyey

 
Leave a comment

Posted by on May 19, 2013 in Accpac Hot Fix

 
 
Follow

Get every new post delivered to your Inbox.

Join 110 other followers

%d bloggers like this: