Category Archives: ERP

#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.




ON [dbo].[DR_ACCS]





/* */

— 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’)






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



Set @SQLSTR = ‘Delete from temp_GlobalVariables’


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)’



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’)



(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 )


SET @LoopCounter = @LoopCounter + 1







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.


Leave a comment

Posted by on May 10, 2016 in ERP


#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.



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:-


• 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:


• 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:


• 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:


• 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.



Tags: , , ,

#22 Understanding Sage 300 ERP Project and Job Costing Settings

Date: 6 May 2013

Sage 300 ERP PJC settings screen allows us to define how PJC is going to work for a particular organisation. It starts with the naming conversion of contract, Project and Categories. Sage PJC allows naming the contract, project and category to the company specific naming conversion. For instance if in a company contract is called as agreement and each project under agreement is called assignment then we can change the level 1, 2 names in “PJC Setting” to be agreement and assignment. Sage 300 ERP will respect the naming convention defined in PJC settings in all its modules.

Following is the Sage 300 ERP PJC “Settings” screen where we can define the setup.
PJC setup screen

Default labour Type: is the method how Labour cost is going to allocate to the projects. It gives you three options “None”, “Flat Rate Per Labor Hour/Unit”, “Percentage Of Labor Cost”.

  • None – System will not allocate labour cost to the Projects.
  • Flat Rate Per Labor Hour/Unit– System will allocate the predefined fixed labour cost per unit (Defined in Labor Rate field) to the Project.
  • Percentage of Labor cost– System will allocate the percentage of the actual labour cost (Defined in Labor Percentage field) to the Project.

Default Overhead Type: Overheads are the cost that cannot be directly allocated to the project but are necessary to complete the project like periodic expenses, such as utilities, rent, and administrative salaries are indirect costs that you often need to allocate to projects on some rational basis. “Default Overhead type” allows defining method how overhead cost is going to allocate to the projects. It gives you three options “None”, “Flat Rate per Unit”, “Percentage of Cost”.

  • None – System will not allocate Overhead cost to the Projects. You can allocate overhead manually by assigning overhead expenses to categories within a project.
  • Flat Rate Per Unit– System will allocate the predefined fixed overhead cost per unit (Defined in Overhead Rate field) to the Project. This method will be used when overhead cost is in the proportion to the number of units of equipment used in the project.
  • Percentage of cost– System will allocate the percentage of the Overhead cost (Defined in Overhead Percentage field) to the Project. This method is used if the overhead is in the proportion to the total project cost.

Force Listing of Transactions: if selected, this option will force you to print the transaction listing before you can post the transaction.

Default Accounting Method: Accounting method determines how the system will recognise the revenue. Accounting Method selected in the “Default accounting method” field will be the default for the new contract or project. The options that we can select in this field are:

  • Completed Project
  • Total Cost Percentage Complete
  • Labour Hours Percentage Complete
  • Billings and Costs
  • Project Percentage Complete
  • Category Percentage Complete
  • Accrual Basis

Each accounting method will recognise revenue differently. I will explain each accounting method in detail in my next post.

Default Contract Style: Style tells system at which level to track the cost for each project. Selected contract style will be default for new contract but can be changed for each contract. Similarly selected style for contract will be the default for the project under that contract but can be change for each project. Different contract styles that are available under this option are:

  • Standard- Under Standard style the cost is tracked at the resource level. System allows you to use separate forms to assign resources to a project.
  • Basic- Under Basic style you can only track cost at up to category level.

Default A/R Item Number: For “time and materials” and “fixed price” projects if “item type” Invoice is issues it will take default A/R item selected in this option. Default item on the item type invoice can be set different for each project.

Default A/R Unit of Measure: Unit of measure selected in this option will be default for the item-type invoices. Each project can have different Unit of measurement as default.

Aging Periods: Here you can define five aging periods that can be used to classify the documents as current and overdue on reports.

Update Budget: If selected, system will update the budget whenever you process a transaction. System will take long time if it needs to update the budget for each transaction. So select this option only if you need to update the budget for each transaction.

Update Payroll: If this option is selected system will use the payroll expense and employee expense accounts from PJC when update Payroll option is used in PJC. If not selected system will use the accounts specified in US/Canadian Payroll system.

Percentage Complete Method: In this option we can specify when to clear billings and WIP Accounts for “Percentage Complete Accounting Method” Projects. Options are:

  • Clear Billings and WIP during Revenue Recognition: Generally used for short term contracts.
  • Clear Billings and WIP during Project Close.


To be Continue…


Tags: , , ,

#20 Understanding Project and Job Costing Life Cycle

Date: 21 April 2012

In Sage 300 PJC the work flow starts by building a contract in the system. Contract represents an agreement with the customer to do certain work for a give price in a certain period of time. Contract records various projects that define the work that needs to be done for the costumer and in turn are also the revenue stream. Further the cost incurred to accomplish the projects is recorded in categories and Resources under project.
Contract maintenance form is used to assign project to a contract and to assign categories and resources to each project in the contract. In Sage 300 PJC contract, projects and category terminology can be setup as per the company and industry where PJC implemented.


Contract, Project and category (or Resources) need to be identified for each cost detail. Sage PJC will consolidate the cost at the project and contract level and further match the coat with revenue to get the profitability.
The PJC process starts with setting up contract in the system. This involves setting up contract, Projects and categories. Here we can put estimates and approve it to open the contract at each project level. As we can see in the dataflow diagram below how transactions are flowing to complete the PJC life cycle. It also explains the various transactions involve at each step.


Cost Transaction is processed against each category and resources under project and contract, PJC match revenues to the cost that generate them. Contract of each project is the point where it is specified where to recognised revenue, how to bill to the customers.
To be Continue…



1 Comment

Posted by on April 21, 2013 in ERP, Sage 300 ERP, Sage 300 ERP PJC


Tags: , , ,

#19 Sage 300 Project and Job costing

Date: 2 April 2013

Sage ERP Project and job costing (PJC) module is a project management module to assist organizations in developing plans, estimates, assigning resources to tasks, tracking progress, managing budget, costing, billing and analyzing workload so that Work can be delivered on time and be in budget only to be more profitable.
If in an organization where project managers are spending too much time on tracking there projects and related activities then it will be a wise choice to go for Sage ERP 300 PJC Module. Benefit will not only that all project managers will able to track and maintain their projects at a central place but also get lot of added advantage in term of budgeting, estimating, allocate resources, cost and billing to be name few. Not only it will eliminate the time spend by project managers to track projects they are working on but also it will help to consolidate all the project company is working, so consolidated reporting can be generated, hence provide an eagle eye to the decision makes so that better decision making can be done.
Sage PJC assist to maintain categories that are easily customized to meet virtually any specification needed, it’s flexible enough to manage large or small projects for any business environment. Featuring streamlined integration with key Sage ERP 300 modules and extensive custom reporting capabilities, Project and Job Costing puts you in control of project costs with better tracking, enhanced efficiency, and greater accuracy.
With PJC you can:

• Keep track of committed against actual costs with Purchase Order integration.
• Set up, track, and maintain jobs at multiple levels of detail.
• Quickly assign staff, material, subcontractors, equipment, miscellaneous items, and overheads to each project within each job.
• Track and bill inventory items by contract, project, or category as well as by cost types.
• Track and bill equipment usage by contract, project, or category.
• Empower employees to easily manage their own time and billing records using an Internet browser anywhere.
• Manage and track time spend by people on various projects
• Mix and match accounting methods to fit any job or project from very complex to very simple.
• Automatically generate invoices based on the transactions that are due to be billed.
• Easily review contract or project changes and instantly update revenue and cost estimates.
• And Much More!!!

PJC Module integrates seamlessly with other key Sage ERP system modules like Purchase order, Account Receivable and General Ledger. This will insure that you can control your job revenue and cost with PJC more effectively and efficiently.

In nut sell you can keep your eye on the bottom line with extensive, robust analytical capabilities. With Project and Job Costing, you can track retainage, calculate overhead, gauge profitability, and access numerous analytical reports. At the micro-level, you’ll be able to spot errors, inconsistencies,
or overages before you’ve sent the final bill. And at the macro-level, you’ll be able to understand what kinds of jobs tend to earn you the highest margin, which kinds of jobs are draining your resources, and why.

To be continue …



Posted by on April 9, 2013 in ERP


Tags: ,

%d bloggers like this: