Appendix 2: Structured query language (SQL) queries

Open: Setup > Usage > Reports > SQL query tool

Open: Common data > Projects > Financial overview queries

Project management includes standard or master structured query language (SQL) queries for tracking the costs of a project. This appendix lists the SQL queries delivered with Project management.

You can modify a master or create new queries. You are advised, however, to get a basic understanding of how your system works before changing a master format.

Use the Sage X3 SQL query tool function (GESALQ) to view the master SQL queries provided. The master queries are prefixed PJM.

Sage advises that you save a copy of a master query before modifying it.

Create new SQL queries from the master SQL queries instead of changing a master query or starting a new query from scratch.

Use the SQL query tool function (GESALQ).
Copy a master SQL query to create a new SQL query.
Modify the copy.
Use the Financial overview queries function (GESPJQ) to disable the master query then to enable the new query.

The new query must be attached to the correct free column in the working table (Free column field) and to the correct column of the financial overview (Structure link field) as defined in the Financial overview structure function (GESPJS).

You can define up to 5 free columns related to costs (free columns 1 - 5). You can use these to manage committed, pre-committed, actual and posted amounts. The total of these 5 free columns are used for the calculation of the total costs.

Five additional "miscellaneous" type columns are available to add extra information to the financial overview, if needed (financial view 20 provides an example of this in a revenue tracking context).

Each free column is subdivided into two subcolumns that can be linked to a quantity or to an amount (as defined in the Financial overview structure function (GESPJS)). The two subcolumns must be loaded by the SQL query.

Adding new SQL queries:

You must link new SQL queries to the financial overview structure.

Use the Financial overview structure function (GESPJS) and the Financial overview queries function (GESPJQ) to do this.

Modifying existing SQL queries:

You can disable an existing SQL query.

Use the Financial overview queries function (GESPJQ) to do this.

You can reallocate an existing SQL query to a different column.

For example, to track a new "Pre-commitment" column in the financial overview you could use the existing Purchase requests query to populate the new column.

Use the Financial overview queries function (GESPJQ) to do this.

A #Module tag is included in the first line of the text of each SQL query to identify the module concerned by the query. The SQL query is not executed if the module is not activated on the folder.

Project costing: Expenses

SQL query (Expenses)	Event	Budget	Committed		Actual
SQL query (Expenses)	Event	Budget	Time	Amount	Time	Amount
		10,000
Purchases Applied to transaction lines linked to the project. Products must be managed By order or they must be added to the material tasks (product records added to a project material task are treated as managed By order, irrespective of their management mode, By order or Available stock). Amount only.
PJM001	Purchase requests Requested quantity * Unit price			100
PJM002	Purchase orders Quantity to be received * Order price			300
PJM003	Purchase receipts Receipt not yet invoiced * Order price			100
PJM004	Purchase returns Returned Quantity * Order price			-20
PJM005	Purchase invoices Invoiced quantity * Unit price					100
PJM006	Purchase credit memos Invoiced quantity * Unit price (negative)					-20
Components Applied to work orders linked to the project. Products to be released must be managed By order or they must be added to the material task. Product records added to a project material task are treated as managed By order, irrespective of their management mode, By order or Available stock. Components are valued for products managed as Available stock. Components managed By order are valued using purchase transactions, as described in Purchases above. SQL query PJM014 can track the cost of unexpected operations or products on work orders in the WIP valuation table (WIPCOST). SQL queries PJM015 and PJM016 can track the cost of subcontract orders linked to a project. Amount only.
PJM013	Components on work orders Remaining to consume * Unit price			300
PJM014	Material tracking on work orders Consumed quantity * Unit price					200
PJM015	Components on subcontract orders Remaining to consume * Unit price			300
PJM016	Material tracking on subcontract orders Consumed quantity * Unit price					200
Work order operations Applied to work orders linked to the project. Products to be released must be managed By order or they must be added to the material task. Product records added to a project material task are treated as managed By order, irrespective of their management mode, By order or Available stock. SQL query PJM012 can track the cost of unexpected operations or products on work orders in the WIP valuation table (WIPCOST). Amount and time.
PJM011	Work order operations Remaining load * Unit price		16	160
PJM012	Time entries on work order operations Time spent * Unit price				4	40
Time on project Amount and time.
PJM007	Task operations Remaining load * Unit price		8	80
PJM008	Time entries on task operations Time spent * Unit price				4	40
PJM009	Time entries on tasks Time spent * Unit price				5	50
PJM010	Time entries on budgets Time spent * Unit price				15	200
Expenses in Finance Applied to transaction lines linked to the project. Amount only. To adjust a project financially you can link manual journal entries (PJM020 and PJM021) to the project. You must, however, customize the SQL queries that read the manual journal entries to fit with your organization and with your expectations in the project financial overview. Accounting entries linked at the project header level and the journals generated by automatic journals are excluded. Note also that for manual journal entries the default cost type to be associated with a project expense must be defined in the Financial overview queries function (GESPJQ).
PJM017	Expense notes Expense notes amount					100
PJM018	BP invoices Invoiced amount					100
PJM019	BP invoices Invoiced amount (negative)					-100
PJM020	Journal entries Credit amount					100
PJM021	Journal entries Debit amount					-100
Stock Applied to transaction lines linked to the project. Amount only.
PJM022	Miscellaneous issues (Debit amount)					100
PJM023	Miscellaneous receipts (Credit amount)					-100

Project costing: Revenue

SQL query (Revenue)	Event	Budget	Revenue
SQL query (Revenue)	Event	Budget	Sub col. 1	Sub col. 2
		1,000
Sales documents Applied to document lines linked to the project. Amount only.
PJMRN001	Sales quotes To be converted (Sub. Col1) and Quoted (Sub. Col2)		100	100
PJMRN002	Sales orders To be invoiced (Sub. Col1) and Ordered (Sub. Col2)		100	100
PJMRN003	Sales invoices To be paid (Sub. Col1) and Invoiced (Sub. Col2)		100	100
PJMRN004	Sales outstanding orders Total in progress (including Quotes, Orders, Invoices)			100
PJMRN005	Sales BP invoices Invoiced amount			100

Project costing: Cost type initialization rules

Project management applies a cascading order of priority (or set of rules) to determine the cost type associated with a specific expense.

Reminder: Cost type initialization rulesCost type initialization rules are applied in a cascading order of priority, that is, if rule 1 returns "false", rule 2 is applied, and so on to the final rule.

Cost type information is transmitted in column 15 of the SQL query. If you create specific queries you must transmit the cost type in column 15 or leave column 15 empty to let the standard process determine the default cost type.

The following diagrams demonstrate how the cost type initialization rules are applied to the different types of expense (materials, labor, expense notes, finance, time entries). The cost type settings and the links established with work centers, products and expense codes are critical to this process.

Estimated progress

SQL query	Event
PJMPRG	Estimated project progress Calculates the estimated progress percentage by comparing the budget for the project to the total expenses recorded against the project, as calculated in the project Financial status section.
PJMPRG2	Estimated operation progress Calculates the estimated progress percentage of a project by comparing the sum of consumed load against planned load for the labor task operations. This SQL query is particularly useful if your organization is in the services sector and you use labor tasks exclusively.
PJMPRG3	Estimated project progress Calculates the estimated progress percentage of a project by comparing the sum of actuals against the provisional cost price, as calculated in the project Financial status section.
PJMPRGB	Estimated budget progress Calculates the estimated progress percentage of a project budget by comparing the budget for the project to the total expenses recorded against the project, as calculated in the cost structure Financial status section.
PJMPRGB2	Estimated operation/budget progress Calculates the estimated progress percentage of a project budget by comparing the sum of consumed load against planned load for the labor task operations. This SQL query is particularly useful if your organization is in the services sector and you use labor tasks exclusively.
PJMPRGB3	Estimated budget progress Calculates the estimated progress percentage of a project budget by comparing the sum of actuals against the provisional cost price, as calculated in the project Financial status section.
PJMPRGT	Estimated task progress Calculates the estimated progress percentage by comparing Planned load with Consumed load, as calculated in the task planning Activity section.

Estimated revenue

SQL query	Event
PJMREV	Estimated project revenue Calculates the estimated revenue at completion from the sales orders linked to the project.

SQL query

Event

PJMREV

Estimated project revenue

Calculates the estimated revenue at completion from the sales orders linked to the project.