PM DACs: Reference Data

In this topic, you can find information about the data access classes (DACs) that contain reference data that is used in most other DACs in project accounting.

Project Accounting Preferences

The PMSetup DAC contains the general project accounting preferences, which are specified on the Projects Preferences (PM101000) form.

You can use the following example to obtain the project preferences of a particular tenant.
SELECT 
    * 
FROM 
    PMSetup s 
WHERE 
    s.CompanyID = 2

Account Groups

The PMAccountGroup DAC contains account groups for projects. The account groups are defined on the Account Groups (PM201000) form.

You can use the following example to select the list of active account groups with the Expense type.
SELECT 
    * 
FROM 
    PMAccountGroup a 
WHERE 
    a.IsActive=1 
    AND a.type='E'

Rules for Project Billing

The PMBillingRule DAC contains the list of rules for project billing, which are defined on the Billing Rules (PM207000) form.

The following example shows how to select all rules with the Time and Material type.
SELECT 
    * 
FROM 
    PMBillingRule b 
WHERE 
    b.type='T'

Change Order Classes

The PMChangeOrderClass DAC contains the change order class data, which is defined on the Change Order Classes (PM203000) form.

The following example shows how to select the change order class with the specified ID.
SELECT 
    * 
FROM 
    PMChangeOrderClass c 
WHERE 
    c.ClassID = 'DEFAULT'

Project Rate Tables

If a project is billed with the Time and Material step of the billing rule and the @Rate parameter is used in the billing rule formula, the relevant rate from the configured rate table is used in the pro forma invoices.

A user configures each rate by performing the following steps:

  1. Adding a rate table on the Rate Tables (PM204200) form. The rate table data is stored in the PMRateTable DAC.
  2. Adding a rate type on the Rate Types (PM204100) form. The rate type data is stored in the PMRateType DAC.
  3. Defining a rule on the Rate Lookup Rules (PM205000) form. The rule data is stored in the PMRateDefinition DAC. A definition of a rule includes the addition of sequences and the defining of conditions for each combination of a rate table and rate type. Conditions can be defined for the following entities:
  4. Adding a rate sequence for each combination of rate table, rate type, and rate code on the Rate Tables (PM206000) form. The rate sequence data is stored in the PMRateSequence DAC.
  5. Adding a rate on the Rate Tables (PM206000) form. The rate data is stored in the PMRate DAC.

For details on the configuration of billing rates, see Billing Rates: General Information.

The following diagram shows the relationships between the project rate tables.
Figure 1. Project rate tables


Projects, Project Templates, Customer Contracts, and Contract Templates

The Contract DAC contains the master data of the entities that are created and edited on the following data entry forms:

The following examples show how to obtain entities that satisfy particular conditions:
  • The selection of all projects with the In Planning status
    SELECT 
    * 
FROM 
    Contract p 
WHERE 
    p.Status='D' 
    AND p.BaseType='P' 
    AND p.NonProject=0 
    AND IsTemplate=0
  • The selection of Non-Project items
    SELECT 
    * 
FROM 
    Contract 
WHERE 
    NonProject=1

Project Tasks

The PMTask DAC contains the tasks that are defined in the table on the Tasks tab of the Projects (PM301000) form—that is, the tasks of a particular project.

The following examples illustrate the selection of project tasks that meet specific conditions:

  • The selection of the tasks for the specified project

    SELECT 
    * 
FROM 
    PMTask t 
WHERE 
    t.ProjectID = 3312

  • The selection of all common tasks

    SELECT 
    * 
FROM 
    PMTask 
    INNER JOIN Contract 
        ON PMTask.ProjectID = Contract.ContractID 
WHERE 
    Contract.NonProject = 1

The PMTaskAllocTotal DAC contains the quantity and the previously allocated amount for the project tasks with the Allocate Budget allocation method specified on the Calculation Rules tab of the Allocation Rules (PM207500) form. (The previously allocated amount, which is expressed in the project currency, is stored in the PMTaskAllocTotal.Amount field.) The class contains data that is not available in the UI but that can be useful for troubleshooting and data migration.

Project Budget Lines

The PMBudget DAC contains the project budget lines that are defined on the following forms:

Project budget lines aggregate the project balance amounts and quantities by project ID, project task ID, cost code, account group, and inventory ID.

The selection of project budget lines that meet particular conditions is illustrated in the following examples:

  • The selection of project budget lines for the specified project with the specified account group

    SELECT 
    * 
FROM 
    PMBudget b 
WHERE 
    b.ProjectID = 3293 
    AND b.AccountGroupID=23

  • The selection of the cost project budget of the specified project

    SELECT 
    * 
FROM 
    PMBudget 
WHERE 
    Type = 'E' 
    AND ProjectID = 3293

  • The selection of the budget lines entered for the out-of-balance account group

    SELECT 
    * 
FROM 
    PMBudget 
    INNER JOIN PMAccountGroup 
        ON PMBudget.AccountGroupID = PMAccountGroup.GroupID 
WHERE 
    PMAccountGroup.Type = 'O'

Change Order Budgets

The PMChangeOrderBudget DAC contains the lines that are defined on the Revenue Budget and Cost Budget tabs of the Change Orders (PM308000) form.

The following example shows the retrieval of the list of change order budgets with the specified project and account group.

SELECT 
    * 
FROM 
    PMChangeOrderBudget c 
WHERE 
    c.ProjectID = 29 
    AND c.AccountGroupID = 2