VIEW: APPS.AR_INVOICE_INSTALLMENTS_V

Source

View Text - Preformatted

SELECT trx.customer_trx_id customer_trx_id, o_type.lookup_code open_trx_only_flag, ps.terms_sequence_number installment_number, NVL( nvl(tax_original, to_number(null)), sum( decode( line_type, 'TAX', extended_amount, 0 ) ) ) tax_amount, NVL( nvl(amount_line_items_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount,0)), to_number(null)) , sum( decode( line_type, 'FREIGHT', 0, 'TAX', 0, extended_amount ) ) ) line_amount, NVL( nvl(freight_original, to_number(null)), sum( decode( line_type, 'FREIGHT', extended_amount, 0 ) ) ) freight_amount, NVL( ps.amount_due_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount,0)), sum( extended_amount) ) total_amount FROM ar_lookups o_type, ar_adjustments com_adj, ra_customer_trx_lines lines, ar_payment_schedules ps, ra_terms_lines tl, ra_terms t, ra_customer_trx trx WHERE ps.payment_schedule_id + DECODE(ps.class,'INV',0,'') = com_adj.payment_schedule_id(+) AND com_adj.subsequent_trx_id is NULL AND 'C' = com_adj.adjustment_type(+) AND trx.customer_trx_id = ps.customer_trx_id(+) AND trx.term_id = tl.term_id(+) AND trx.term_id = t.term_id(+) AND NVL(ps.terms_sequence_number, NVL(tl.sequence_num,0)) = NVL(tl.sequence_num, NVL(ps.terms_sequence_number,0)) AND o_type.lookup_type = 'YES/NO' AND o_type.lookup_code = decode(NVL(ps.amount_due_remaining,0),0,'N','Y') AND lines.customer_trx_id = trx.customer_trx_id GROUP BY trx.customer_trx_id, trx.trx_number, ps.tax_original, ps.freight_original, ps.amount_line_items_original, tl.relative_amount, t.base_amount, ps.terms_sequence_number, ps.amount_due_original, trx.initial_customer_trx_id, com_adj.amount, o_type.lookup_code

View Text - HTML Formatted

SELECT TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, O_TYPE.LOOKUP_CODE OPEN_TRX_ONLY_FLAG
, PS.TERMS_SEQUENCE_NUMBER INSTALLMENT_NUMBER
, NVL( NVL(TAX_ORIGINAL
, TO_NUMBER(NULL))
, SUM( DECODE( LINE_TYPE
, 'TAX'
, EXTENDED_AMOUNT
, 0 ) ) ) TAX_AMOUNT
, NVL( NVL(AMOUNT_LINE_ITEMS_ORIGINAL + DECODE(TRX.INITIAL_CUSTOMER_TRX_ID
, ''
, 0
, NVL(COM_ADJ.AMOUNT
, 0))
, TO_NUMBER(NULL))
, SUM( DECODE( LINE_TYPE
, 'FREIGHT'
, 0
, 'TAX'
, 0
, EXTENDED_AMOUNT ) ) ) LINE_AMOUNT
, NVL( NVL(FREIGHT_ORIGINAL
, TO_NUMBER(NULL))
, SUM( DECODE( LINE_TYPE
, 'FREIGHT'
, EXTENDED_AMOUNT
, 0 ) ) ) FREIGHT_AMOUNT
, NVL( PS.AMOUNT_DUE_ORIGINAL + DECODE(TRX.INITIAL_CUSTOMER_TRX_ID
, ''
, 0
, NVL(COM_ADJ.AMOUNT
, 0))
, SUM( EXTENDED_AMOUNT) ) TOTAL_AMOUNT
FROM AR_LOOKUPS O_TYPE
, AR_ADJUSTMENTS COM_ADJ
, RA_CUSTOMER_TRX_LINES LINES
, AR_PAYMENT_SCHEDULES PS
, RA_TERMS_LINES TL
, RA_TERMS T
, RA_CUSTOMER_TRX TRX
WHERE PS.PAYMENT_SCHEDULE_ID + DECODE(PS.CLASS
, 'INV'
, 0
, '') = COM_ADJ.PAYMENT_SCHEDULE_ID(+)
AND COM_ADJ.SUBSEQUENT_TRX_ID IS NULL
AND 'C' = COM_ADJ.ADJUSTMENT_TYPE(+)
AND TRX.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID(+)
AND TRX.TERM_ID = TL.TERM_ID(+)
AND TRX.TERM_ID = T.TERM_ID(+)
AND NVL(PS.TERMS_SEQUENCE_NUMBER
, NVL(TL.SEQUENCE_NUM
, 0)) = NVL(TL.SEQUENCE_NUM
, NVL(PS.TERMS_SEQUENCE_NUMBER
, 0))
AND O_TYPE.LOOKUP_TYPE = 'YES/NO'
AND O_TYPE.LOOKUP_CODE = DECODE(NVL(PS.AMOUNT_DUE_REMAINING
, 0)
, 0
, 'N'
, 'Y')
AND LINES.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID GROUP BY TRX.CUSTOMER_TRX_ID
, TRX.TRX_NUMBER
, PS.TAX_ORIGINAL
, PS.FREIGHT_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, TL.RELATIVE_AMOUNT
, T.BASE_AMOUNT
, PS.TERMS_SEQUENCE_NUMBER
, PS.AMOUNT_DUE_ORIGINAL
, TRX.INITIAL_CUSTOMER_TRX_ID
, COM_ADJ.AMOUNT
, O_TYPE.LOOKUP_CODE