Common data > Projects > Financial overview queries

Financial overview queries

Use this function to link structured query language (SQL) queries to specific columns in a Project management Financial overview structure.

Financial overview structure

SQL queries are used to define the content of specific columns – columns identified as a 'Free column' (Free column 1-10) – in a Financial overview. You can use them to track the costs of a project to provide an overview of its financial situation, or in financial reports based on a financial overview structure.

The ability to evaluate and track, in real time, the actual financial status of a project relative to its budgets is critical. Project management uses financial views to dynamically obtain and display financial data associated with a project.

Project management financial overviews are produced by the interpretation of a report specification.

1. You use the Financial overview structure (PJMBUDTRS) function to define or customize the structure of a report (the list of columns to be displayed).
Each report structure is based on a financial view enabled for data tracking in Miscellaneous table 388 - Financial view, such as expenses and revenue.

2. For the content of each column identified in the outline report structure as a 'Free column' (Free column 1-10), you use this function to specify exactly which data to use in it.

3. To then evaluate and track, in real time, the actual financial status of a project relative to its budget you use the Financial overview action in the Project management (GESPJM) function, or the Financial overview (PJMFINOV0) function.
The resulting Financial overview reflects the defined report specification and applied structured query language (SQL) queries.

Use the SQL query tool (GESALQ) function to view the master SQL queries provided with your solution, and to modify or add new queries. Master Project management queries provided with your solution are prefixed 'PJM'.

Miscellaneous table 388 - Financial view controls which financial overview data can be displayed.

Project management includes standard, or master SQL queries for both expense and revenue financial overviews. Delivered by default with your solution are master SQL queries for Financial views '10' (expenses) and '20' (revenue). You can modify these master SQL queries, or create new SQL queries from scratch (for example, for a view 30) to suit your organization, your processes or your users. You are advised, however, to get a basic understanding of how your system works before changing a master query.

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

Tips and Tricks

Prerequisites

SEEREFERTTO Refer to documentation Implementation

Screen management

The Financial overview queries function displays a table (list) of SQL queries linked to Project management Financial overview structures.

Presentation

Each line of the table represents information that will be accessed to populate a column identified as a 'Free column' (Free column 1-10) in a financial report structure. That is, where the field Column content (field CODDES) in the Financial overview structure (PJMBUDTRS) function is set to 'Free column 1-10'. The view number represents a view that has been activated in Miscellaneous table 388 - Financial view. For example, '10' represents an expense view and '20' represents a revenue view.

Five free columns in a financial report structure are predefined specifically for managing costs (expenses) – committed, pre-committed, actual and posted amounts. These are identified as Free columns 1-5. By default, one expense column tracks 'commitments' and one expense column tracks 'actuals'.

Five additional 'miscellaneous' type columns are predefined for tracking revenue linked to a project, such as orders or invoices. These are identified as Free columns 6-10.

All other columns defined in the Financial overview structure (PJMBUDTRS) function are managed by the system. You cannot link an SQL query to these columns.

Predefined SQL queries for both view '10' and view '20' are delivered by default with your solution.

Fields

The following fields are present in this block :

Financial view (field PRPGRP)

Use this field to identify the financial view to be linked to an SQL query. Select a view code from the codes activated in Miscellaneous table 388 - Financial view. For example, for an expense view select view '10' or a revenue view, select view '20'. This field is mandatory.

By default, Miscellaneous table 388 enables data tracking on selected financial views such as expenses and revenue. Table 388 therefore controls which financial overview data can be displayed.

Project management includes standard, or master SQL queries for both expense and revenue financial overviews. Delivered by default with your solution are master SQL queries for Financial views '10' (expenses) and '20' (revenue). You can modify these master SQL queries, or create new SQL queries from scratch to suit your organization, your processes or your users. You are advised, however, to get a basic understanding of how your system works before changing a master query.

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

Active (field ACTIVE)

Click this check box if this SQL query is active within the context of a group of SQL queries assigned to a column identified as a 'Free column' (Free column 1-10) in a financial report structure. That is, where the field Column content (field CODDES) in the Financial overview structure (PJMBUDTRS) function is set to 'Free column 1-10'.

Query no. (field PBTCOD)

Use this field to define a query sequence number. This field is mandatory.

Query name (field CODDES)

Use this field to describe this SQL query. The name should reflect the type of financial data to be retrieved (extracted) by this SQL query.

Label (field LABEL)

This field is associated with field Query name (field CODDES). Type in a heading that reflects the description defined for the field Query name (field CODDES).

SQL query (field SQLQRY)

Use this field to define the code of the SQL query to be used (executed) to retrieve values for a column identified in the outline report structure as a 'Free column' (Free column 1-10). That is, where the field Column content (field CODDES) in the Financial overview structure (PJMBUDTRS) function is set to 'Free column 1-10'. This field is mandatory.

Tips and Tricks

Default cost type (field DEFCTYPE)

Use this field to define the default cost type to be associated with a project expense. This field is mandatory.

Appendix: Project costing – Cost type initialization rules

Free column (field FREECOL)

Use this field to link this SQL query to a column identified as a 'Free column' in the report structure for the selected financial view. That is, where the field Column content (field CODDES) in the Financial overview structure (PJMBUDTRS) function is set to 'Free column 1-10'. This field is mandatory.

This field links this SQL query to the data structure (one of the 'free columns' (Free columns 1-10) in the financial overview database tables); the field Structure link (field PJMPBTCOD) links this query to the report structure for the selected financial view.

If this SQL query retrieves expense values you can assign it to Free columns 1-5 as these columns are predefined specifically for managing costs (expenses) – committed, pre-committed, actual and posted amounts. By default, one expense column tracks 'commitments' and one expense column tracks 'actuals'.
If this SQL query retrieves revenue linked to a project, such as orders or invoices you can assign it to Free columns 6-10.

Structure link (field PJMPBTCOD)

Use this field to link this SQL query to a specific column sequence number in the report structure for the selected financial view.

You can only link this SQL query to a sequence number for a column that is defined as a 'Free column'. That is, where the field Column content (field CODDES) in the Financial overview structure (PJMBUDTRS) function is set to 'Free column 1-10'. The column sequence number must equate to the free column specified in the field Free column (field FREECOL).

This field links this SQL query to the report structure for the selected financial view; the field Free column (field FREECOL) links this query to the data structure (one of the 'free columns' (Free columns 1-10) in the financial overview database tables).

Object (field PBTOBJ)

Use this field to define the object code to be launched from the Actions icon for an expense line at the lowest level of the Cost types structure in a financial overview. For example, if this SQL query must extract purchasing data you could define the object 'POH' (to launch the Purchase order (GESPOH) function from the Purchase order header table); to extract labor costs you might define 'MFG' (to launch the Work orders (GESMFG) function from the Work orders table).

Context (field CONTXT)

Use this field to indicate the context of the data to be retrieved (extracted) by this SQL query.

This field has a direct impact on the way in which the defined SQL query retrieves and processes values (quantities or amounts) for a column identified in the outline report structure as a 'Free column' (Free column 1-10).

Positive valuation (field POSVAL)

Click this check box if you expect the quantity or amount retrieved by this SQL query to increase a value in the Financial overview. If you expect the quantity or amount retrieved by this SQL query to decrease a value in the Financial overview, leave this check box clear.

For example, if this SQL query retrieves a Purchase invoice this should increase the actual value in the Financial overview whereas a Purchase return should decrease it.

Subcol1. calculation (field CUMSCOL)

Click this check box if the Financial overview calculation engine must total the value retrieved by this SQL query for subcolumn 1.

If you do not require the values in subcolum 1 to be totalled, leave this check box clear. The quantities will only be displayed in the detail screen for the selected object code (as launched from the Actions icon for an expense line in a financial overview).

Subcolumn 1 is typically used for quantities. You should only select this flag if you are sure the SQL query retrieves quantities that can be converted and totalled in the subcolumn.

The default only assumes an aggregation of time unit. Consolidating (totalling) product units is not advisable as units can vary from one document type to another.

Remain. budget calc. (field CUMRTCO)

Click this check box if the value retrieved by this SQL query (quantity or amount) must be included in the calculations for the column identified in the outline report structure as the 'Budget remaining'.

If the value retrieved by this SQL query has no impact on the remaining budget, leave this check box clear.

Action panel

Click the Save action to create or add these new Financial overview query details.

Click the quick Edit icon on the individual lines to change an existing query to suit your organization.

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 (PJMBUDTRQ));
Disable the master SQL query and enable your new SQL query.
Using the SQL query column in the table (grid), filter for SQL queries using the master query code. Edit the Query no. (number) for the appropriate Financial view (click the Edit icon). 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 (PJMBUDTRS) 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 (PJMBUDTRS) 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.
Save a copy of a master SQL query before you modify it.

Generic messages

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

SEEREFERTTO Refer to documentation Implementation

Appendix: Structured query language (SQL) queries

Project costing:

Project costing (default setup): Expenses

SQL query	Event	Budget	Committed		Actual
SQL query	Event	Budget	Time	Amount	Time	Amount
		10000
Purchases Applied to document 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 'On 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
Materials 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 'On stock'. Components are valued for products managed 'On stock'. Components managed 'By order' are valued using purchase documents, as described in Purchases above. Amount only.
PJM013	Materials on Work orders (Remaining to consume * Unit price)			300
PJM014	Material tracking on Work orders (Consumed quantity * Unit price)					200
PJM015	Materials 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 'On stock'. 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 document 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. Note also that for manual journal entries the default cost type to be associated with a project expense must be defined in this function (PJMBUDTRQ).
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

Project costing (default setup): Revenue

SQL query	Event	Budget	Revenue
SQL query	Event	Budget	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.

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.