Common data > Projects > Financial overview queries 

Use this function to link structured query language (SQL) queries to specific columns in a Project management report 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.

  • You use the Financial overview structure (GESPJS) 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.

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

  • 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 single screen for defining an SQL query to link to a Financial overview structure.

    Entry screen

    Presentation

    You use this screen to define the 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 (GESPJS) 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 (GESPJS) 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.

     Tips and Tricks

     Appendix: Project costing – Cost type initialization rules

    Close

     

    Fields

    The following fields are present on this tab :

    Query

    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 advises that you save a copy of a master SQL query before modifying it.

    • Column sequence (field PBTCOD)

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

    • Column content (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.

    • 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 (GESPJS) function is set to 'Free column 1-10'.

    Block number 2

    • Label (field LABEL)

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

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

    • SQL query to run (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 (GESPJS) function is set to 'Free column 1-10'.

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

    • Description (field SQLQRYDES)

    This field describes the event to be run by the defined SQL query.

    Block number 3

    • Working table free column no. (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 (GESPJS) 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.

    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 (GESPJS) function is set to 'Free column 1-10'. The column sequence number must equate to the free column specified in the field Working table free column no. (field FREECOL).

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

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

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

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

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

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

    SQL query

    • SQL query (field SQLQRYTEX)

    This text box displays the declarative statements in the defined SQL query.

    Close

     

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

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

     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

    SQL queryEventBudgetCommittedActual
    TimeAmountTimeAmount
      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)

    		 16160  
    PJM012

    Time entries on Work order operations
    (Time spent * Unit price)

    		   440

    Time on project

    Amount and time.

    PJM007

    Task operations
    (Remaining load * Unit price)

    		 880  
    PJM008

    Time entries on task operations
    (Time spent * Unit price)

    		   440
    PJM009

    Time entries on tasks
    (Time spent * Unit price)

    		   550
    PJM010

    Time entries on budgets
    (Time spent * Unit price)

    		   15200

    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 this function (GESPJQ).

    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

    Project costing (default setup): Revenue

    SQL queryEventBudgetRevenue
    Subcol. 1Subcol. 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))

    		 100100
    PJMRN002

    Sales orders
    (To be invoiced (Subcol. 1) and Ordered (Subcol. 2))

    		 100100
    PJMRN003

    Sales invoices
    (To be paid (Subcol. 1) and Invoiced (Subcol. 2))

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