Loading Data to STG_GL_DATA Creates Unique Constraint (Doc ID 2778609.1)

Last updated on DECEMBER 21, 2023

Applies to:

Symptoms

On PFT 8.1.0.1.0 , the unique index for STG_GL_DATA appears to be missing V_DATA_ORIGIN. Per documentation, multiple data sources are accommodated using seeded batch, T2T_MANAGEMENT_LEDGER_MULTI_SOURCE. Based on this same documentation, would also expect V_DATA_ORIGIN to be defined as NOT NULL.

From the PFT User Guide:

_T2T_MANAGEMENT_LEDGER_MULTI_SOURCE

This seeded batch along with the underlying DTs and T2T enables tracking of data from Staging table to Management ledger table using identity codes. For each distinct value of "Data Origin" in the Staging table, a new Identity code will be used to populate Management ledger and FSI_DATA_IDENTITY tables. Also the the value of V_DATA_ORIGIN in the Staging table will be mapped to  FSI_DATA_IDENTITY.description column.

****NOTE ABOVE STATEMENT IMPLIES THAT THERE ARE MULTIPLE VALUES OF DATA ORIGIN IN THE STAGING TABLE****

The seeded batch named _T2T_MANAGEMENT_LEDGER_MULTI_SOURCE will have the below three tasks in the sequence -:

1. A DT named FN_MGMT_LED_LOAD_TEMP_TABLE, which will populate T2T_FSI_D_MANAGEMENT_LEDGER table with unique identity_code values for each of the distinct v_data_origin values in STG_GL_DATA table. It accepts two
 parameters - Target Table Name and Staging Table Name.

2. A T2T named T2T_MANAGEMENT_LEDGER_MULTI_SOURCE, which will use the identity codes from the table T2T_FSI_D_MANAGEMENT_LEDGER for loading data from STG_GL_DATA to FSI_D_MANAGEMENT_LEDGER.

3. Another DT named FN_MGMT_LED_LOAD_DATA_IDENTITY, which will populate FSI_DATA_IDENTITY table with the information about the management ledger load. It maps the V_DATA_ORIGIN column in STG_GL_DATA table to DESCRIPTION
 column in FSI_DATA_IDENTITY table. It will also delete the records from T2T_FSI_D_MANAGEMENT_LEDGER table.

Notes:
  1. Results of DT execution will be written to FSI_MESSAGE_LOG table.
  2. The OOB T2T uses STG_GL_DATA as the staging table. The T2T has to be customized to use any other staging table as the source.
  3. If a custom Staging table is used instead of STG_GL_DATA, the table should have the columns FIC_MIS_DATE and V_DATA_ORIGIN.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.