ARECCDLCP: ECC AR Data Load Ending In Error ORA-06502 : ORA-06512: at "APPS.FND_NUMBER" (Doc ID 2756904.1)

Last updated on JUNE 05, 2024

Applies to:

Symptoms

ECC Receivables

When attempting to run the ARECCDLCP: Receivables Command Center Data Load
the following error occurs.

ERROR
-----------------------

The Receivables Command Center Data Load job failed as the child job failed

log file shows

ECCRUNDL: ECC Run Data Load

Shows
Running Full Load
JobId -> 13621
status-> RUNNING
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . done
status (155 secs) ---> FAILURE

data load concurrent program failed

Details of auditDataset#:
    dataset: ar-trx
    status: FAILURE
    total elapsed time:
        auditLoadRuleId:
        DatasetLoadRuleId:
        Load Type:FULL_LOAD
        sequence:1
            auditLoadDetailId:
            operation:INSERT
            sqlQuery:SELECT * from (SELECT /*+ leading ( trx_v.temp.trx_t trx_v.temp.ps trx_v.temp.trx) full (trx_v.temp.trx_t ) cardinality (trx_v.temp.trx_t 10) use_nl(trx_v.temp.trx) */trx_v.ACCOUNT_NUMBER,trx_v.BILL_TO_CUSTOMER,trx_v.BILL_TO_LOCATION,trx_v.SHIP_TO_CUSTOMER,trx_v.SHIP_TO_LOCATION,trx_v.COLLECTOR,trx_v.PROFILE_CLASS,trx_v.TRANSACTION_NUMBER,trx_v.TRANSACTION_CLASS,trx_v.TRANSACTION_TYPE,trx_v.TRANSACTION_SOURCE,trx_v.CURRENCY_CODE,trx_v.CURRENCY,trx_v.PURCHASE_ORDER_NUMBER,trx_v.SALES_ORDER_NUMBER,trx_v.SALESPERSON,trx_v.OPERATING_UNIT,trx_v.TRANSACTION_CONTEXT,trx_v.TRANSACTION_REFERENCE,trx_v.TRANSACTION_CURRENCY_CODE,trx_v.TRANSACTION_CURRENCY,trx_v.LEDGER_CURRENCY_CODE,trx_v.LEDGER_CURRENCY,trx_v.LEDGER,trx_v.DUE_ACCOUNTED_AMOUNT,trx_v.ACCOUNTED_AMOUNT,trx_v.DUE_DATE,trx_v.TRANSACTION_DATE,trx_v.ACCOUNTING_DATE,trx_v.PURCHASE_ORDER_DATE,trx_v.ECC_SPEC_ID,trx_v.PAYMENT_SCHEDULE_ID,trx_v.INSTALLMENT,trx_v.TRANSACTION_CLASS_CODE,trx_v.TRANSACTION_AMOUNT,trx_v.DUE_AMOUNT,trx_v.APPLIED_AMOUNT,trx_v.STATUS,trx_v.TRANSACTION_ID,trx_v.CREDIT_MEMO_REASON_CODE,trx_v.CREDIT_MEMO_REASON,trx_v.COMMENTS,trx_v.INTERNAL_NOTES,trx_v.SPECIAL_INSTRUCTION,trx_v.SHIP_TO_CUSTOMER_NUMBER,trx_v.ORG_ID,trx_v.BILL_TO_CUSTOMER_ID,trx_v.BILL_TO_SITE_USE_ID,trx_v.BILL_TO_CONTACT,trx_v.ECC_LAST_UPDATE_DATE,trx_v.RECORD_TYPE,trx_v.ATTRIBUTE_CATEGORY,trx_v.ATTRIBUTE1,trx_v.ATTRIBUTE2,trx_v.ATTRIBUTE3,trx_v.ATTRIBUTE4,trx_v.ATTRIBUTE5,trx_v.ATTRIBUTE6,trx_v.ATTRIBUTE7,trx_v.ATTRIBUTE8,trx_v.ATTRIBUTE9,trx_v.ATTRIBUTE10,trx_v.ATTRIBUTE11,trx_v.ATTRIBUTE12,trx_v.ATTRIBUTE13,trx_v.ATTRIBUTE14,trx_v.ATTRIBUTE15,dfv.*,idfv.*,       trx_v.aging_buckets,       trx_v.ar_aging_buckets,       trx_v.discount_expiration_date,       trx_v.discount_flag,       trx_v.discount_amount, trx_v.LANGUAGE  FROM ARI_ECC_TRX_V trx_v  , (select ROW_ID "'AR_ROW_ID'",CONTEXT_VALUE "'AR_CONTEXT_VALUE'",to_char(EOB_BATCH_NUMBER) "'AR_EOB_BATCH_NUMBER'",EOB_FROM_DATE "'AR_EOB_FROM_DATE'",EOB_THRU_DATE "'AR_EOB_THRU_DATE'",CARRIER_HQ_CODE "'AR_CARRIER_HQ_CODE'",to_char(CARRIER_HQ_ID) "'AR_CARRIER_HQ_ID'",CONCATENATED_SEGMENTS "'AR_CONCATENATED_SEGMENTS'" from RA_CUSTOMER_TRX_ALL_DFV) dfv , (select 'ARINT_ROW_ID','ARINT_CONTEXT_VALUE','ARINT_CLAIM_NUMBER','ARINT_CUSTOMER_REFERENCE','ARINT_CUSTOMER_REASON','ARINT_CLAIM_REASON','ARINT_BILLING_PARTY_ID','ARINT_BILLING_SITE_USE_ID','ARINT_BILLING_INTERFACE_REQUEST_ID','ARINT_BILLING_PERIOD','ARINT_BILL_TO_PARTY_ID','ARINT_PRIVATE_LABEL','ARINT_INVOICE_GROUP','ARINT_INVOICE_TYPE','ARINT_TERMINATION_QUOTE_NUMBER','ARINT_OKL_SOURCE_BILLING_TRANSACTION','ARINT_PROJECT_NUM___CONSOLIDATED_BG','ARINT_DRAFT_CONSOLIDATED_INVOICE_NUM','ARINT_AGREEMENT_NUMBER','ARINT_PROJECT_ORGANIZATION','ARINT_PROJECT_MANAGER','ARINT_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,CLAIM_NUMBER,CUSTOMER_REFERENCE,CUSTOMER_REASON,CLAIM_REASON,BILLING_PARTY_ID,BILLING_SITE_USE_ID,BILLING_INTERFACE_REQUEST_ID,BILLING_PERIOD,BILL_TO_PARTY_ID,PRIVATE_LABEL,INVOICE_GROUP,INVOICE_TYPE,TERMINATION_QUOTE_NUMBER,OKL_SOURCE_BILLING_TRANSACTION,PROJECT_NUM___CONSOLIDATED_BG,DRAFT_CONSOLIDATED_INVOICE_NUM,AGREEMENT_NUMBER,PROJECT_ORGANIZATION,PROJECT_MANAGER,CONCATENATED_SEGMENTS from RA_CUSTOMER_TRX_ALL2_DFV) idfv  WHERE trx_v.row_id = dfv."'AR_ROW_ID'"(+) AND trx_v.row_id = idfv."'ARINT_ROW_ID'"(+) and language in ('US'))   PIVOT (max(SALESPERSON) as SALESPERSON, max(AGING_BUCKETS) as AGING_BUCKETS, max(AR_AGING_BUCKETS) as AR_AGING_BUCKETS,max(TRANSACTION_CLASS) as TRANSACTION_CLASS,max(CREDIT_MEMO_REASON) as CREDIT_MEMO_REASON, max(OPERATING_UNIT) as OPERATING_UNIT,          max(TRANSACTION_CURRENCY) as TRANSACTION_CURRENCY, max(LEDGER_CURRENCY) as LEDGER_CURRENCY,max(CURRENCY) as CURRENCY          for LANGUAGE in ('US' "US"))

            status: FAILURE

            statusMessage: There is an error while trying to ingest data for this load rule AR_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO with dataset ar-trx for job 13,621 ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "APPS.FND_NUMBER", line 20
oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)

 --------------------

Steps to Reproduce:

Responsibility: Receivables Command Center
Navigation: Submit Request

1. Run the ARECCDLCP: Receivables Command Center Data Load
2. The child request fails. Unable to load the data
3. Workaround is to set the FND: ECC Disable Descriptive Flexfields for RA_CUSTOMER_TRX

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot Load the AR data with accompanying Flexfield information.

Changes

Cause

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