oracle-databaseplsqlreportinvoiceoracle-ebs

How to join ChargeBacks with original transaction invoice in Oracle AR query?

I want to join chargeback transaction to its original invoice but I cannot find a link to join in table. Both transactions exist independently in table : ar_payment_schedules_all

I need to find a join like some transaction_id etc.

Solution

  • They join through the AR_ADJUSTMENTS_ALL table.

    Join: 

    AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID = AR_ADJUSTMENTS.CHARGEBACK_CUSTOMER_TRX_ID.

    ... to get the adjustment record(s) that associate the chargeback with the original transaction(s). Then join:

    AR_ADJUSTMENTS.PAYMENT_SCHEDULE_ID = AR_PAYMENT_SCHEDULES_ALL.PAYMENT_SCHEDULE_ID

    ... to get the payment schedule(s) of the original transaction(s).

    See the DDL for the seeded view AR_CHARGEBACKS_V for code.