Financial overview queries

Screen management

The Financial overview queries function displays a single screen for defining an SQL query to link to a Financial overview structure.

Entry screen

Tips and Tricks

• Create new SQL queries from the master SQL queries instead of changing a master query or starting a new query from scratch. Do this by following these steps:
• Open the SQL query tool (GESALQ) function;
• View the master SQL queries provided with your solution. A master Project management query has the prefix 'PJM';
• Create a new SQL query by copying a master SQL query.
  Select the master SQL query closest to your requirements, change the code in the Query field then click the Create action;
• Modify your copy SQL query and Save;
• Open this function (Financial overview queries (GESPJQ));
• Disable the master SQL query and enable your new SQL query.
  Using the SQL query column in the selection panel, filter for SQL queries using the master query code. Select the Query no. (number) for the appropriate Financial view. To disable the master query and enable your new query simply change the code displayed in the SQL query field for your new SQL query code;
• Attach your new SQL query to the financial structure (as defined in the Financial overview structure (GESPJS) function). You must attach your new query to the correct free column in the working table (field Free column) and to the correct column sequence number of the Financial overview (field Structure link), then Save.
  
  

• You can link an SQL query to any column identified as a 'Free column' (Free column 1-10) in a financial report structure.
• Five free columns (Free columns 1-5) are specifically for managing costs (expenses) – committed, pre-committed, actual and posted amounts. The sum of these 5 free columns is used in the calculation of the total cost.
  By default, one expense column tracks 'commitments' and one expense column tracks 'actuals'.
• Five additional 'miscellaneous' type columns (Free columns 6-10) are predefined for tracking revenue linked to a project, such as orders or invoices.

 Master Financial view 20 – this Financial view provides an example of the use of free columns in a revenue tracking context.

 Each free column is subdivided into two subcolumns. The type of data represented in each subcolumn (as defined in the Financial overview structure (GESPJS) function) is defined as a quantity figure or an amount. Your SQL query must load both subcolumns.

• You can use an existing SQL query in a column. For example, you could allocate the master 'Purchase request' query to populate a new 'Pre-commitment' column in the financial overview.
  
  
• A 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.
  
  
• Save a copy of a master SQL query before you modify it.

Error messages

In addition to the generic error messages, the following messages can appear during the entry :

Miscellaneous table [table number] : Code [Financial view] does not exist

This function is used in conjunction with Miscellaneous table 388 - Financial view. You have entered a financial view code which has not been enabled for data tracking in table 388. Select a valid financial view code or add the code you require to Miscellaneous table 388 first.

Tables used

Refer to documentation Implementation

Appendix: Structured query language (SQL) queries

Project costing:

• Expenses
• Revenue
• Cost type initialization rules
  
  

 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 (default setup): Expenses

Project costing (default setup): Revenue

SQL query	Event	Budget	Committed		Actual
			Time	Amount	Time	Amount
		10000
Purchases Applied to transaction lines linked to the project. Products must be managed 'By order' or 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 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 (WIPCOST) table. 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 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 (WIPCOST) table. 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.
PJM017	Expense notes (Expense notes amount)					100
PJM018	Supplier BP invoices (Invoiced amount)					100
PJM019	Supplier BP invoices/credit memos (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

SQL query	Event	Budget	Revenue
			Subcol. 1	Subcol. 2
		1000
Sales documents Applied to document lines linked to the project. Amount only.
PJMRN001	Sales quotes (To be converted (Subcol. 1) and Quoted (Subcol. 2))		100	100
PJMRN002	Sales orders (To be invoiced (Subcol. 1) and Ordered (Subcol. 2))		100	100
PJMRN003	Sales invoices (To be paid (Subcol. 1) and Invoiced (Subcol. 2))		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. The rules are 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.