5.11.2.6 Setting up Global Temporary Table

This step is applicable for loading ledger data from Type III. Calendar dates present in the data of Load table are converted to the corresponding Fiscal Year/Month. Conversion from calendar date to fiscal year & month is done based on the START_MONTH column present in FSI_FISCAL_YEAR_INFO table. These derived fiscal year & fiscal month are then inserted in an intermediate Global Temporary Table (GTT) after aggregating the rows of same months/years. Therefore, if 12 rows are present for the same fiscal year each corresponding to a different month, then global temporary table may have maximum of one row corresponding to the fiscal months, these 12 rows represent.

GTT needs to contain valid dimension member identifiers and numeric codes. Since staging table contains alphanumeric identifiers and codes, a view is created on STG_GL_DATA table joining with other relevant dimension and CD/MLS tables before being used in the GTT creation.

Global temporary table can be created in 2 ways:

• Using PL/SQL

  Declare output number; Begin Output:= fn_ledger_load_create_gtt('BATCH_ID', 'AS_OF_DATE', 'TABLE_NAME'); End; AS_OF_DATE is the date for which GTT is created, in YYYYMMDD format. TABLE_NAME is the staging table name STG_GL_DATA. An example of running the function from SQL*Plus is as follows: SQL> var output number; SQL> execute :output:= fn_ledger_load_create_gtt('BATCH_ID', '20100519', 'STG_GL_DATA');

• Using OFSAAI Batch Maintenance

  To execute the procedure from OFSAAI Batch Maintenance, run the batch mentioned following and specify the following parameters:

  • Datastore Type: Select appropriate datastore from list
  • Datastore Name: Select appropriate name from the list
  • IP address: Select the IP address from the list
  • Rule Name: fn_ledgerLoadGTTCreation
  • Parameter List: AS_OF_DATE and TABLE_NAME

  TABLE_NAME is the staging table name STG_GL_DATA.

  AS_OF_DATE should be passed as 'YYYYMMDD' format.

Note:

BATCHID will be passed explicitly in Batch Maintenance. The appropriate table parameters are enclosed in single quotes.