Setup for the LEDGER_STAT load utility

Populate STG_GL_DATAThe following columns in STG_GL_DATA must be populated with valid values:

Option

Description

Column

Description

V_GL_CODE

General Ledger Code value.

FIC_MIS_DATE

This field indicates the current period As of Date applicable to the data being loaded.

V_ORG_UNIT_CODE

Org Unit Code value.

V_SCENARIO_CODE

Populate with a value from the CONSOLIDATION_DISPLAY_CODE column from the FSI_CONSOLIDATION_CD table (ex. ACTUAL, BUDGET).

V_CCY_CODE

ISO Currency Code from FSI_CURRENCIES (ex. USD)

V_PROD_CODE

Product Code value.

V_FINANCIAL_ELEMENT_CODE

Populate with a value from the FINANCIAL_ELEM_CODE column from the DIM_FINANCIAL_ELEMENTS_B table (ex. ENDBAL, AVGBAL).

V_COMMON_COA_CODE

Common COA Code value.

N_AMOUNT_LCY

Balance

The following columns in STG_GL_DATA must be populated because they are defined as NOT NULL but can be defaulted to the value of your choice because they are not used:

V_LV_CODE

V_BRANCH_CODE

F_CONSOLIDATION_FLAG

V_GAAP_CODE

Verify data exists in the view STG_GL_DATA_V. The following SQL statement is used to populate this view:

SELECT v_data_origin DS, f_consolidation_flag ACCUM_TYPE,
                        fcc.consolidation_cd CONSOLIDAT, v_ccy_code ISOCRNCYCD,
                        dfeb.financial_elem_id FINANC_ID, doub.org_unit_id ORG_ID,
                        dglb.gl_account_id GL_ACCT_ID, dccb.common_coa_id CMN_COA_ID, dpb.product_id
                        PRDCT_ID, fic_mis_date AS_OF_DATE, n_amount_lcy VALUE, 0 baltypecdFROM
                        STG_GL_DATA SGD,DIM_GENERAL_LEDGER_B DGLB,DIM_ORG_UNIT_B DOUB,DIM_PRODUCTS_B
                        DPB,DIM_FINANCIAL_ELEMENTS_B DFEB,DIM_COMMON_COA_B DCCB,FSI_CURRENCIES
                        FC,FSI_CONSOLIDATION_CD FCCWHERE NVL(n_amount_lcy, 0) <> 0AND
                        SGD.V_GL_CODE = DGLB.GL_ACCOUNT_CODEAND SGD.V_ORG_UNIT_CODE =
                        DOUB.ORG_UNIT_CODEAND SGD.V_PROD_CODE = DPB.PRODUCT_CODEAND
                        SGD.V_FINANCIAL_ELEMENT_CODE = DFEB.FINANCIAL_ELEM_CODEAND
                        SGD.V_COMMON_COA_CODE = DCCB.COMMON_COA_CODEAND SGD.V_CCY_CODE =
                        FC.ISO_CURRENCY_CDAND SGD.V_SCENARIO_CODE =
                        FCC.CONSOLIDATION_DISPLAY_CODE;

As seen in the code above, the view references the _CODE columns on the dimension tables. For example, COMMON_COA_CODE on DIM_COMMON_COA_B and ORG_UNIT_CODE on DIM_ORG_UNIT_B. These code columns must be populated for data to exist in STG_GL_DATA_V.

The Update_Dimension_Code (fn_updatedimensioncode) program populates these Code columns using data from values in the Code dimension Attribute (for example, COMMON COA CODE, ORG UNIT CODE, and so on).

The BALTYPECD column has a default value of 0 in the View, as this column is not null in LEDGER_STAT. Baltypecd is not a Dimension. It indicates the credit or debit of the same account details. Since same account can hold both credit and debit, this column should be populated in the source with a value. It is the part of the unique Index and Not Null column in LEDGER_STAT.

If using the Type 3 Ledger Stat Load for the first time, run the GTT table creation procedure.

The GTT table creation procedure creates the Global Temporary Table LS_LOAD_TABLE_GTT_V.

The fn_ledger_load_create_gtt function creates the table LS_LOAD_TABLE_GTT_V and the index UK_GTT for use in the Type 3 Ledger Stat Load.

Note:

If the GTT table has not been created and you try to execute the Ledger Stat Load, you will get the following error in FSI_MESSAGE_LOG:WRAPPER_LEDGER_STAT_LOAD- Error: -942: ORA-00942: table or view does not exist

Populate FSI_LS_LOAD_BATCH. You need to populate the following columns:

Option

Description

RUN_FLAG

Y

SEQUENCE

Sequence value (ex. 1)

LOAD_TABLE_NAME

STG_GL_DATA

ONE_MONTH_ONLY

N

UPDATE_MODE

ADD or REPLACE

INSERT_ONLY

Y or N

CREATE_OFFSETS

N

IS_CALENDAR_MONTH

Y

START_CALENDAR_MONTH

Starting date to load in format YYYYMMDD.

END_CALENDAR_MONTH

Ending date to load in format YYYYMMDD.

Run the Ledger Stat Load. Use the following command to run the Type 3 Ledger Stat Load in SQL*Plus as the atomic user:

DECLAREx NUMBER :=0;BEGINx
                        :=ofsa_util.wrapper_ledger_stat_load('BATCH_ID ','MIS_DATE','TABLE_NAME',
                        TABLE_TYPE', 'UPDATE_MODE', 'INSERT_ONLY', 'START_DATE',
                        'END_DATE')dbms_output.put_line ('The return variable is ' || x);END;DECLARE
                        x NUMBER :=0; BEGIN x :=
                        ofsa_util.wrapper_ledger_stat_load('ARALSLOADTYPE3_4','20110111','STG_GL_DATA',
                        'CALENDAR_MONTHS', 'ADD', 'Y', '20101231', '20101231'); dbms_output.put_line
                        ('The return variable is ' || x); END;

After the Ledger Load completes, check the tables FSI_MESSAGE_LOG and FSI_LS_LOAD_BATCH for errors.

Note:

For ledger load table name is ledger_load and data source value is the V_DATA_ORIGIN from STG_GL_DATA. For Ledger_stat with the same data source will have same identity code.