XOG: General Ledger Account

You can use the XOG for general ledger (GL) accounts, allocation rules, periods, and transactions.
ccppmop144
You can use the XOG for general ledger (GL) accounts, allocation rules, periods, and transactions.
2
XOG: General Ledger Account
The general ledger (GL) account XOG object represents the chart of accounts that are used to process chargebacks. Use this XOG object to view inbound and outbound general ledger account attributes.
Schema Name
nikuxog_glaccount.xsd
Read and Write XML Files
The following XML files are included:
  • pac_glaccount_read.xml. Use this file to export GL accounts from 
    Clarity Project and Portfolio Management (PPM)
    .
  • pac_glaccount_write.xml. Use this file to import GL accounts that were previously exported from 
    Clarity Project and Portfolio Management (PPM)
    .
Prerequisites
The following conditions must be met before using this XOG:
  • The entities that the GL Accounts reference must exist before importing GL accounts.
  • The Account Class must be a valid lookup value in 
    Clarity Project and Portfolio Management (PPM)
    .
  • The Account Type must be a valid lookup value in 
    Clarity Project and Portfolio Management (PPM)
    .
Business Rules and Processing
The following business rules and processing apply to this XOG object:
  • The GL chart of accounts is imported into 
    Clarity Project and Portfolio Management (PPM)
     from an external accounting system. The Chargeback feature uses these accounts to capture charges and credits.
  • The GL accounts schema is defined as part of the first step to GL integration.
  • To enable GL transactions between systems, 
    Clarity Project and Portfolio Management (PPM)
     allows for inbound processing to define GL Accounts, Periods, and Entities. GL Accounts establish the accounts to which transactions can be posted.
Read Filters
The following explicit read filters are used:
  • MAIN_ACCOUNT_ID. This is used for filtering by mainAcctId, which is a part of the natural GL account code.
  • SUB_ACCOUNT_ID. This is used for filtering by subAcctId, which is a part of the natural GL account code.
  • ACCOUNT_DESCRIPTION. This is used for filtering by description.
Error Handling
If a GL Account file is unsuccessful due to an error, the following fields are output:
  • entity
  • accountNumber
  • externalId
  • externalSource
Schema Mappings
The following schema mapping is provided for the outbound Financial Transaction tag name, GLAccount.
GLAccount Schema Tag
This tag is part of the schema mapping for the General Ledger XOG object. The values in this table are unlike other lookup values. A text string must be provided, not a lookup code.
The GLAccount schema tag has the following attributes:
  • entity
    Defines the entity name for the GL account code. This attribute is a unique primary key, browse field. Lookup to DPT_ENTITY_DEPT.
    Table and Column:
    Entity
    Type:
    Lookup
  • mainAcctId
    Required. Part of the natural GL account code.
    Table and Column:
    MAIN_ACCOUNT_ID
    Type:
    String
  • subAcctId
    Required. Part of the natural GL account code.
    Table and Column:
    SUB_ACCOUNT_ID
    Type:
    String
  • Overhead
    Optional. Indicates if the GL account is an overhead account.
    Table and Column:
    OVERHEAD
    Type:
    Boolean
  • Capitalexpense
    Indicates if the GL account is a capital expense account.
    Table and Column:
    CAPITAL_EXPENSE
    Type:
    Boolean
  • noncashexpense
    Indicates if the GL account is a non-cash expense account.
    Table and Column:
    NONCASH_EXPENSE
    Type:
    Boolean
  • description
    Defines the description of the general ledger account.
    Table and Column:
    Description
    Type:
    String
  • Account Type
    Required. Defines the GL account type. This attribute determines whether the GL account is a Balance Sheet account or a P&L account. A default is set in the background. Lookup to PAC_CHG_GL_ACCOUNT_TYPE
    Table and Column:
    ACCOUNT_TYPE
    Type:
    Number
    Default:
    Lookup
  • accountClass
    Optional. This attribute determines whether the GL account is an asset or liability. A default is set in the background. Lookup to PAC_CHG_GL_ACCOUNT_CLASS.
    Default:
    0
    Table and Column:
    ACCOUNT_CLASS
    Type:
    Lookup
  • Active
    Indicates if the general ledger account is active.
    Values:
    • 0. Not Active
    • 1. Active
    Table and Column:
    Active
    Type:
    Boolean
    Default:
    1
  • externalID
    Defines the originating unique identifier.
    Table and Column:
    External_ID
    Type:
    String
  • externalSource
    Defines the external source. The lookup value is the originating system ID.
    Example:
    Oracle
    Table and Column:
    External_Source_ID
    Type:
    String in Schema, but stored as Number in 
    Clarity Project and Portfolio Management (PPM)
    .
XOG: General Ledger Allocation Rule
The GL Allocation Rule XOG object represents the debit and credit rules in chargebacks. Use the General Ledger Allocations Rule XOG object to import and export GL allocation rules.
Schema Name
nikuxog_glallocation.xsd
Read and Write XML Files
The following XML files are included:
  • cbk_allocation_read.xml. Use this file to export GL allocations from 
    Clarity Project and Portfolio Management (PPM)
    .
  • cbk_allocations_read.xml. Use this file to import GL allocations that were previously exported from 
    Clarity Project and Portfolio Management (PPM)
    .
Prerequisites
Before you import the GL allocation rules, verify that the entities that are referenced by these rules exist.
Business Rules and Processing
The insert or update of GL Allocation rules are based on the existence of the GL Allocation code in 
Clarity Project and Portfolio Management (PPM)
. The GL Allocation code is unique.
Read Filters
The following explicit read filters are used:
  • ALLOCATION_CODE. A unique code that is used for filtering by the allocation code.
  • STATUS. This is used for filtering by status (Active, Inactive, or On Hold).
  • CBK_TYPE. This is used for filtering by chargeback type (Debit, Credit).
  • CBK_SUB_TYPE. This is used for filtering by chargeback sub type (Standard, Investment, or Overhead).
Schema Mapping
The following schema tag is included.
GL Allocation Rule Schema Tag
The GL allocation rule tag is part of the schema mapping for the General Ledger Allocation Rule XOG object. It has the following attributes:
  • entityCode
    Optional. Defines the unique identifier of the entity that is tied to the GL allocation rule.
    Table and Column: 
    ENTITY_ID
    Type:
     String
  • locationCode
    Optional. Defines the location unique identifier that is tied to the GL allocation rule.
    Table and Column: 
    LOCATION_ID
    Type:
     String
  • departmentCode
    Optional. Defines the department unique identifier that is tied to the GL allocation rule.
    Table and Column: 
    DEPARTMENT_ID
    Type:
     Boolean
  • resourceClassCode
    Optional. Defines the resource class unique identifier that is tied to the GL allocation rule.
    Table and Column: 
    RESOURCECLASS_ID
    Type:
     Boolean
  • chargeCode
    Optional. Defines the charge code unique identifier that is tied to the GL allocation rule.
    Table and Column: 
    PRCHARGECODE_ID
    Type:
     Boolean
  • investmentCode
    Optional. Defines the investment unique identifier that is tied to the GL allocation rule.
    Table and Column:
     INVESTMENT_ID
    Type:
     Number
  • utilityCode1
    Optional. The lookup to BROWSE_USR_VAL1_ALL.
    Table and Column: 
    UTILITY_CODE_1
    Type:
     Lookup
  • utilityCode2
    Optional. The lookup to PRTIMEENTRY_USERLOV2.
    Table and Column: 
    UTILITY_CODE_2
    Type:
     Lookup
  • transactionClassCode
    Optional. The lookup to FIN_TRANSCLASSES.
    Table and Column: 
    TRANSCLASS
    Type:
     Lookup
  • typeCode
    Optional. The lookup to LOOKUP_INPUT_TYPES.
    Table and Column: 
    PRTYPECODE_unique identifier
    Type:
     Lookup
  • statusCode
    Required. The lookup to STATUS_CODE.
    Values:
     Open and Closed
    Table and Column: 
    PAC_CHG_STATUS
    Type:
     Lookup
  • chargeRemToOverhead
    Optional. Indicates if the rule charges the reminder to overhead.
    Table and Column: 
    CHG_REM_TO_OVERHEAD
    Type:
     Boolean
  • cbkType
    Required. Specifies the chargeback type.
    Values:
     DEBIT and CREDIT
    Table and Column: 
    CHARGEBACK_TYPE
    Type:
     String
  • cbkSubtype
    Required. Specifies the chargeback subtype.
    Values:
     STANDARD, INVESTMENT, and OVERHEAD
    Table and Column: 
    CHARGEBACK_SUBTYPE
    Type:
     String
Allocation Details
  • glAccountMain, glAccountSub
    Required. Defines the main GL account. Lookup to SCH_BROWSE_GL_ACCTS.
    Table and Column: 
    GL_ACCOUNT_ID
    Type:
     Lookup
  • department
    Required. Defines the unique identifier of the department to charge. Lookup to SCH_BROWSE_DEPT.
    Table and Column: 
    DEPARTMENT_ID
    Type:
     Lookup
  • flatAmount
    Optional. This attribute is not used.
    Table and Column: 
    FLAT_AMOUNT
    Type: 
    Numeric
  • weightable
    Optional. This attribute is not used.
    Table and Column:
    WEIGHTAGE
    Type: 
    Numeric
XOG: General Ledger Period
Use the general ledger period XOG object to view inbound and outbound general ledger period attributes.
Schema Name
xog_glperiod.xsd
Read and Write XML Files
The following XML files are included:
  • pac_glperiod_read.xml. Use this file to export GL periods from 
    Clarity Project and Portfolio Management (PPM)
    .
  • pac_glperiod_write.xml. Use this file to import GL periods that were previously exported from 
    Clarity Project and Portfolio Management (PPM)
    .
Business Rules and Processing
GL Periods are only defined for inbound (write) processing to 
Clarity Project and Portfolio Management (PPM)
. This schema is defined as part of the first step to GL integration.
Read Filters
None
Error Handling
If a GL Period file is unsuccessful due to an error, the following fields are output:
  • Period Name
  • Period Type
  • Period Number
  • Description
  • Quarter
  • Year
  • Start Date
Schema Mappings
Schema mappings are described for the following outbound General Ledger Period tag name.
Glperiod Schema Tag
This tag is part of the schema mapping for the General Ledger Period XOG object. This tag has the following attributes:
  • entity
    Required. The unique primary key. A browse field associating the period to an entity.
    Table and Column: 
    Entity
    Type:
     String
  • Period
    Required. Defines the unique primary key. The fiscal period (that is, date) posted for the selected entity.
    Table and Column: 
    Period
    Type:
     Date
  • currentPeriod
    Required. Defines the status of the resource.
    Values: 
    • 1. True
    • 0. False
    Default: 
    1
    Table and Column: 
    Currentperiod
    Type:
     Boolean
  • externalId
    Required. The originating unique ID.
    Table and Column: 
    External_ID
    Type:
     String
  • externalSource
    Required. A lookup value is the originating system ID (for example, Oracle).
    Table and Column: 
    External_Source_ID
    Type:
     String in schema, but stored as Number in the database.
XOG: General Ledger Transaction
GL transactions represent an entry in the General Ledger. The transaction includes information such as the accounts credited or debited and other financial transaction information. Use the general ledger transaction XOG object to view outbound general ledger attributes.
Schema Names
nikuxog_transaction.xsd
Read and Write XML Files
The following XML files are included:
  • pac_gltransactions_read.xml. Use this file to export GL transactions from 
    Clarity Project and Portfolio Management (PPM)
    .
  • pac_gltransactions_write.xml. Use this file to import GL transactions that were previously exported from 
    Clarity Project and Portfolio Management (PPM)
    .
Prerequisites
The GL transaction must belong to an invoice.
Business Rules and Processing
The GL Transactions schema is defined for outbound (read) GL processing. The GL Transaction object is used to export the data from the CBK_GL_TXNS and CBK_GL_TXN_VALUES tables.
Read Filters
The following explicit read filters are used:
  • transactionSource. Filters GL transactions by the transaction source (W for WIP, A for Adjusted, or R for Reversed).
  • entity. Filters by GL transactions by entity.
  • periodStart. Filters GL transactions by the fiscal period start date, a date filter.
  • periodEnd. Filters GL transactions by the fiscal period end date, a date filter.
  • investment_id. Filters GL Transactions by the investment.
XOG allows for outbound processing of GL Transactions that are based on the value within the glposted field.
When querying the database, by default the query returns all GL transactions where glposted is not equal to 'Y' (that is, transactions are awaiting posting). Once selected and invoices processes, the glposted field is updated to 'Y' to indicate that they have been sent to the GL.
Error Handling
Read Transactions
Error handling for read transactions from 
Clarity Project and Portfolio Management (PPM)
 databases are due to an invalid formats or database unavailability. The adaptor or middleware must handle transaction-level error handling when mapping and transporting into the accounting system. If one transaction is found to be in error, the entire file is not committed. The file must be fixed and resubmitted to keep the balance of debits and credits.
XOG does not have control of processing once an output file is successfully created. If you find an error in the output, rollback the entire batch to keep debits and credits intact.
If a single record within the batch is found to be in error, the entire batch is rejected. Then:
  • The external system (adaptor or middleware) must call the Update Transactions schema and must provide the error information element tag and the key fields of the error records.
  • XOG processes the input file and copies all the GL transaction records from the GLCONTROL table into the GLEXCEPTION table.
  • XOG deletes the records from the GL Control table so they exist in the GLException table.
  • XOG resets the GLPOSTED field for the transactions from the batch in the PPA_WIP and PPA_BILLING table to 'N' from 'P' (depending if the transaction source is B or W).
Fix the error batches using 
Clarity Project and Portfolio Management (PPM)
, re-post to GLControl, and rerun the XOG to extract the GL transaction records.
Update Transactions
If the entire file cannot be committed, it must be fixed and resubmitted. This is important as all debits and credits must be kept in sync across applications. If an error is found, it is written to the error log. The following fields help to identify the transaction in error:
  • transactionNumber
  • transactionSource
  • sequenceNumber
Schema Mappings
Schema mappings are described for the following outbound General Ledger Transaction tag name.
GLtransaction Schema Tag
This tag is part of the schema mapping for the General Ledger Transaction XOG object. This tag has the following attributes:
  • entity
    Optional. The name of the entity for the GL transaction.
    Table and Column: 
    CBK_GL_TXNS.ENTITY_ID
    Type: 
    String
  • accountCode
    Required. The GL account code.
    Table and Column:
     CBK_GL_TXNS.GL_ACCOUNT_ID
    Type:
     String
  • amount
    Optional. The amount of the transaction.
    Table and Column:
     CBK_GL_TXN_VALUES.AMOUNT
    Type:
     Float
  • currency
    Optional. The currency code of the transaction amount.
    Table and Column: 
    CBK_GL_TXN_VALUES.CURRENCY_TYPE, CBK_GL_TXN_VALUES.CURRENCY_CODE
    Type: 
    String
  • transactionNumber
    Required. A unique primary key. The transaction number from WIP or PPA-billings.
    Table and Column: 
    CBK_GL_TXN_VALUES.TRANSACTION_ID
    Type:
     Positive Integer
  • transactionSource
    Required. A unique primary key. This key allows you to define the GL distribution of a transaction that is based on the module where it originated.
    Values:
    • W. From WIP.
    • A. From billing.
    • D. From credit.
    Table and Column: 
    TRANSACTION_SOURCE
    Type:
     String
  • period
    Required. The gl period for the transaction.
    Table and Column:
     PPA_WIP.GLPERIOD
    Type:
     String
  • InvoiceDate
    Required. The date of the invoice to which the GL transaction belongs. This date must be between the project start and end dates.
    Table and Column: 
    CBK_INVOICE.INVOICE_DATE
    Type: 
    Date
  • department
    Required. The department id of the transaction
    Table and Column:
     CBK_GL_TXNS.DEPARTMENT_ID
    Type:
     String
  • transactionDate
    Required. The date of the transaction.
    Table and Column:
     CBK_GL_TXNS.TRANSACTION_DATE
    Type:
     Date
  • investment
    Required. The investment on which the transaction is posted.
    Table and Column:
     CBK_GL_TXNS.TRN_INV_ID
    Type:
     String