R12 Multiple Transaction Entities Created in XLA for Payments or Invoices (Doc ID 1381782.1)

Last updated on FEBRUARY 17, 2023

Applies to:

Symptoms

Multiple Entities exists in XLA.XLA_TRANSACTION_ENTITIES for a single source id.

This will cause various issues will occur like:

·         Incorrect Payment Status
·         Invoices on Hold
·         Failure in Create Accounting

Expect single entity per transaction.

Identification SQL:
===================
Input Parameter: Ledger ID.

SELECT  xte2.application_id , xte2.entity_id ,
        xte2.ledger_id , xte2.entity_code ,
    xte2.SOURCE_ID_INT_1 , xte2.transaction_number
    , count(distinct xe2.event_id) event_count  
FROM xla_transaction_entities_upg xte2
     , xla_events xe2
WHERE ( xte2.application_id ,
        xte2.ledger_id ,
    xte2.entity_code ,
    NVL( xte2.SOURCE_ID_INT_1 , -99 )
      ) IN
      (
    select    xte.application_id ,
            xte.ledger_id ,
        xte.entity_code ,
        xte.source_id_int_1   
    from xla_transaction_entities_upg xte
    where  xte.application_id =200
    and    xte.ledger_id = &Ledger_ID
    and    xte.entity_code <> 'MANUAL'
    group by xte.application_id , xte.source_id_int_1 , xte.entity_code , xte.ledger_id
    having count(1) > 1  
     )
AND xe2.application_id(+) = xte2.application_id
AND xe2.entity_id(+) = xte2.entity_id
group by xte2.application_id , xte2.entity_id ,
         xte2.ledger_id , xte2.entity_code ,
         xte2.SOURCE_ID_INT_1 , xte2.transaction_number;

Cause

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