Additional Information About GL Reconciliation in Project Analytics

The subledger to General Ledger account reconciliation is a common task in the accounting process. The reconciliation process involves comparing the balances accounts between the General Ledger (GL) and a sub ledger, such as Projects.

Balance differences between an account in the GL and the sub ledger are then explained, or "Reconciled" by finding unmatched journal entries. The differences might happen due to the asynchronous nature of Cost/Revenue Distributions processes from Projects module and the GL Journals creation/posting in the Finance module. For example, we can have cost distributions which are transferred to the sub ledger but the corresponding journal was either not created or not posted to the GL.

Reconciliations provide assurance that numbers in the GL are correct and synchronized with their corresponding drill down distributions, which is important as these numbers are used to generate financial statements.

To assist project accountants in reconciling the Project sub ledger with the GL, Oracle Project Analytics introduces a reconciliation solution that identifies six situations, or use cases, that explain why the GL and the Project sub ledger are not balanced. Section 3, below, identifies and explains these use cases and what do they mean for each adapter.

From Oracle Business Intelligence Applications release 11.1.1.7.1 onward, the reconciliation solution is available for project cost and revenue transactions, for E-Business Suite 11.5.10, E-Business Suite R12x and PeopleSoft 9x source systems.

For this solution, Oracle Business Intelligence Applications introduces two new subject areas and over 30 new metrics. The Catalog includes two new dashboard pages in the Project Executive dashboard and 22 reports. The reports in the dashboard pages show the count of exceptions found for each of the use cases and their total amount. Users can slice these reports by time, organization and project when related to cost and revenue lines, and by time, ledger and natural segment when related to journal lines.

The reports are designed to help users find where they need to take action to reconcile the Projects sub ledger and the GL. For this, the reports identify the cost lines, revenue lines, and journal lines that explain the differences between the sub ledger and the GL.

Notice that at implementation time, and depending of the customer source system, customizations to the ETL code and metadata might be needed to enable support for some use cases. This document lists the FSM tasks that contain these instructions for the E-Business Suite and PeopleSoft source systems and the use cases for each one.

ETL Parameters

Project GL reconciliation ETL runs for a specific period window. Customers can specify the period for which they want to identify reconciliation issues by configuring the following two variables. These variables should not be null.

PROJ_GL_PERIODS_TO_LOAD
- Specifies the number of periods to include for reconciliation.
- Default (installed) value: 1.
- Permissible values: positive integers 0,1,2,3 and so on.
PROJ_GL_RECON_DT
- This is the date we start counting from (going backward) the number of periods, when loading data for Reconciliation.
- Default (installed): "DEFAULT". When this variable has value "DEFAULT" it means it will use SYSDATE to identify the current period.
- Permissible values: String "DEFAULT" or a date in YYYY-MM-DD format.

Examples

Sample Values	Behavior
PROJ_GL_RECON_DT: DEFAULT PROJ_GL_PERIODS_TO_LOAD: 1	When both these variables have default values, the ETL will run to reconcile data for current period (based on SYSDATE) and 1 previous period.
PROJ_GL_RECON_DT: DEFAULT PROJ_GL_PERIODS_TO_LOAD: 3	With these values, the ETL will run to reconcile data for current period (based on SYSDATE) and 3 previous periods.
PROJ_GL_RECON_DT: 2012-12-31 PROJ_GL_PERIODS_TO_LOAD: 1	With these values, the ETL will run to reconcile data for the period in which 31-DEC-2012 falls and 1 previous period. So if the calendar is monthly, it would reconcile for the current period which would be DEC-2012 and previous period would be NOV-2012.
PROJ_GL_RECON_DT: 2012-06-30 PROJ_GL_PERIODS_TO_LOAD: 3	With these values, the ETL will run to reconcile data for the period in which 30-JUN-2012 falls and 3 previous periods. So if the calendar is monthly, it would reconcile for the current period which would be JUN-2012 and previous periods would be MAR-2012, APR-2012, MAY-2012.

Sample Values

Behavior

PROJ_GL_RECON_DT: DEFAULT

PROJ_GL_PERIODS_TO_LOAD: 1

When both these variables have default values, the ETL will run to reconcile data for current period (based on SYSDATE) and 1 previous period.

PROJ_GL_RECON_DT: DEFAULT

PROJ_GL_PERIODS_TO_LOAD: 3

With these values, the ETL will run to reconcile data for current period (based on SYSDATE) and 3 previous periods.

PROJ_GL_RECON_DT: 2012-12-31

PROJ_GL_PERIODS_TO_LOAD: 1

With these values, the ETL will run to reconcile data for the period in which 31-DEC-2012 falls and 1 previous period.

So if the calendar is monthly, it would reconcile for the current period which would be DEC-2012 and previous period would be NOV-2012.

PROJ_GL_RECON_DT: 2012-06-30

PROJ_GL_PERIODS_TO_LOAD: 3

With these values, the ETL will run to reconcile data for the period in which 30-JUN-2012 falls and 3 previous periods.

So if the calendar is monthly, it would reconcile for the current period which would be JUN-2012 and previous periods would be MAR-2012, APR-2012, MAY-2012.