View: AR_INV_ACT_V

Product:	AR - Receivables
Description:	(Release 11.5 Only)
Implementation/DBA Data:	APPS.AR_INV_ACT_V

View Text

SELECT APP.APPLIED_PAYMENT_SCHEDULE_ID
, APP.ROWID
, PS.TRX_NUMBER
, ARLK1.MEANING
, APP.APPLY_DATE
, -NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, NVL2(APP.AMOUNT_APPLIED_FROM
, NVL( (SELECT CURRENCY_CODE
FROM AR_CASH_RECEIPTS_ALL RCT
WHERE RCT.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID)
, (SELECT INVOICE_CURRENCY_CODE
FROM RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = APP.APPLIED_CUSTOMER_TRX_ID))
, CT.INVOICE_CURRENCY_CODE)
, ARLK2.MEANING
, APP.CASH_RECEIPT_ID
, APP.CUSTOMER_TRX_ID
, NVL(ARLK3.MEANING
, ARLK4.MEANING)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) LINE_AMOUNT
, NVL(PS.TAX_ORIGINAL
, 0) TAX_AMOUNT
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_AMOUNT
, NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0) CHARGES_AMOUNT
, (NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) + NVL(PS.FREIGHT_ORIGINAL
, 0) + NVL(PS.TAX_ORIGINAL
, 0) + NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0)) TOTAL_AMOUNT
FROM RA_CUSTOMER_TRX_ALL CT
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_LOOKUPS ARLK1
, AR_LOOKUPS ARLK2
, AR_LOOKUPS ARLK3
, AR_LOOKUPS ARLK4
WHERE APP.STATUS = 'APP'
AND APP.DISPLAY = 'Y'
AND APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND ARLK1.LOOKUP_TYPE(+) = 'INV/CM'
AND ARLK1.LOOKUP_CODE(+) = PS.CLASS
AND ARLK2.LOOKUP_TYPE(+) = 'PAYMENT_TYPE'
AND ARLK2.LOOKUP_CODE(+) = APP.STATUS
AND ARLK3.LOOKUP_TYPE(+) = 'INVOICING_REASON'
AND ARLK3.LOOKUP_CODE(+) = CT.REASON_CODE
AND ARLK4.LOOKUP_TYPE(+) = 'CREDIT_MEMO_REASON'
AND ARLK4.LOOKUP_CODE(+) = CT.REASON_CODE UNION ALL /* SHOW ACTIVE APPLICATIONS FOR A GIVEN CM PAYMENT SCHEDULE */ SELECT APP.PAYMENT_SCHEDULE_ID
, APP.ROWID
, PS.TRX_NUMBER
, ARLK1.MEANING
, APP.APPLY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, NVL2(APP.AMOUNT_APPLIED_FROM
, NVL( (SELECT CURRENCY_CODE
FROM AR_CASH_RECEIPTS_ALL RCT
WHERE RCT.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID)
, (SELECT INVOICE_CURRENCY_CODE
FROM RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUSTOMER_TRX_ID = APP.APPLIED_CUSTOMER_TRX_ID))
, CT.INVOICE_CURRENCY_CODE)
, ARLK2.MEANING
, TO_NUMBER(NULL)
, APP.APPLIED_CUSTOMER_TRX_ID
, NVL( ARLK3.MEANING
, ARLK4.MEANING)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) LINE_AMOUNT
, NVL(PS.TAX_ORIGINAL
, 0) TAX_AMOUNT
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_AMOUNT
, NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0) CHARGES_AMOUNT
, (NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) + NVL(PS.FREIGHT_ORIGINAL
, 0) + NVL(PS.TAX_ORIGINAL
, 0) + NVL(PS.RECEIVABLES_CHARGES_CHARGED
, 0)) TOTAL_AMOUNT
FROM RA_CUSTOMER_TRX_ALL CT
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_LOOKUPS ARLK1
, AR_LOOKUPS ARLK2
, AR_LOOKUPS ARLK3
, AR_LOOKUPS ARLK4
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY = 'Y'
AND APP.APPLICATION_TYPE = 'CM'
AND APP.STATUS = 'APP'
AND APP.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND ARLK1.LOOKUP_TYPE(+) = 'INV/CM'
AND ARLK1.LOOKUP_CODE(+) = PS.CLASS
AND ARLK2.LOOKUP_TYPE(+) = 'PAYMENT_TYPE'
AND ARLK2.LOOKUP_CODE(+) = APP.STATUS
AND ARLK3.LOOKUP_TYPE(+) = 'INVOICING_REASON'
AND ARLK3.LOOKUP_CODE(+) = CT.REASON_CODE
AND ARLK4.LOOKUP_TYPE(+) = 'CREDIT_MEMO_REASON'
AND ARLK4.LOOKUP_CODE(+) = CT.REASON_CODE UNION ALL /* SHOW ADJUSTMENTS AGAINST A GIVEN PAYMENT SCHEDULE */ SELECT ADJ.PAYMENT_SCHEDULE_ID
, ADJ.ROWID
, ADJ.ADJUSTMENT_NUMBER
, ARLK1.MEANING
, ADJ.APPLY_DATE
, ADJ.AMOUNT
, TO_CHAR(NULL)
, ARLK2.MEANING
, TO_NUMBER(NULL)
, ADJ.CUSTOMER_TRX_ID
, ARLK3.MEANING
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 0
, 0
, 0
, 0
, 0
FROM AR_ADJUSTMENTS ADJ
, AR_LOOKUPS ARLK1
, AR_LOOKUPS ARLK2
, AR_LOOKUPS ARLK3
WHERE ADJ.STATUS NOT IN ('R'
, 'U')
AND ADJ.RECEIVABLES_TRX_ID <> -1
AND ARLK1.LOOKUP_TYPE(+) = 'MISC_PHRASES'
AND ARLK1.LOOKUP_CODE(+) = DECODE (ADJ.RECEIVABLES_TRX_ID
, -15
, 'ASSIGNMENT'
, 'ADJUSTMENT')
AND ARLK2.LOOKUP_TYPE(+) = 'APPROVAL_TYPE'
AND ARLK2.LOOKUP_CODE(+) = ADJ.STATUS
AND ARLK3.LOOKUP_TYPE(+) = 'ADJUST_REASON'
AND ARLK3.LOOKUP_CODE(+) = ADJ.REASON_CODE UNION ALL /* SHOW ADJUSTMENTS DUE TO DEPOSIT APPLICATION */ SELECT ADJ.PAYMENT_SCHEDULE_ID
, ADJ.ROWID
, PS.TRX_NUMBER
, ARLK1.MEANING
, ADJ.APPLY_DATE
, ADJ.AMOUNT
, TO_CHAR(NULL)
, ARLK2.MEANING
, TO_NUMBER(NULL)
, PS.CUSTOMER_TRX_ID
, ARLK3.MEANING
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, 0
, 0
, 0
, 0
, 0
FROM AR_ADJUSTMENTS ADJ
, RA_CUSTOMER_TRX_ALL CT
, AR_PAYMENT_SCHEDULES PS
, AR_LOOKUPS ARLK1
, AR_LOOKUPS ARLK2
, AR_LOOKUPS ARLK3
WHERE ADJ.STATUS NOT IN ('R'
, 'U')
AND ADJ.RECEIVABLES_TRX_ID = -1
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND ADJ.SUBSEQUENT_TRX_ID IS NULL
AND PS.CUSTOMER_TRX_ID = CT.INITIAL_CUSTOMER_TRX_ID
AND PS.TERMS_SEQUENCE_NUMBER = 1
AND ARLK1.LOOKUP_TYPE(+) = 'INV/CM'
AND ARLK1.LOOKUP_CODE(+) = PS.CLASS
AND ARLK2.LOOKUP_TYPE(+) = 'APPROVAL_TYPE'
AND ARLK2.LOOKUP_CODE(+) = ADJ.STATUS
AND ARLK3.LOOKUP_TYPE(+) = 'ADJUST_REASON'
AND ARLK3.LOOKUP_CODE(+) = ADJ.REASON_CODE

Columns