FTP Process with 'Unpriced Account' Method Gets ORA-00001 on Insert into FSI_D_MANAGEMENT_LEDGER (Doc ID 2668984.1)

Last updated on JULY 06, 2021

Applies to:

Symptoms

When running an FTP Process with the 'Unpriced Account' Transfer Pricing method against Management Ledger, the following ORA-00001 error occurs:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [3] -
INSERT Oracle Error: ORA- 00001: unique constraint
(<ATOMIC USER>.UI_FSI_D_MANAGEMENT_LEDGER) violated Driver Function:
drv_oci::Execute() SQL Statement:
insert into FSI_D_MANAGEMENT_LEDGER(identity_code,
FINANCIAL_ELEM_ID, as_of_date, iso_currency_cd, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID,
fiscal_month, fiscal_year, consolidation_cd, strategy_id,
balance_type_cd, entered_balance, functional_balance)
select * .........................etc.

Checking the insert into zML_unpriced_cache_xxxxxx, the Product ID defined in the TP Rule is missing and a NULL exists instead.

insert into zML_unpriced_cache_200311 (TP_PROCESS_SYS_ID, AS_OF_DATE,
ISO_CURRENCY_CD, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, BAL, accrual_factor)
select 200311, '04/ 02/ 2020', ml1.iso_currency_cd, NULL, NULL,
NULL, NULL, NULL, sum(NVL(ml2.entered_balance, 0))/ **************
sum(NVL(ml1.entered_balance, 0)), 0.081967......

In the row with **************, the NULL value should be replaced by the Product ID having the Unpriced Account definition.

Cause

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