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.

 

Close

Fields presentation

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

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

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

 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.

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

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

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

Close

Action panel

Save

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

 Click the quick Edit

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

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

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

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