VIEW: APPS.AR_INV_ACT_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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