AP Prepayments Status

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Prepayments Status Report (XML)
Short Name: APXINPSR_XML
DB package: AP_APXINPSR_XMLP_PKG
SELECT  	i.vendor_id                     	C_vendor_id,
                   i.invoice_currency_code||i.payment_currency_code  c_inv_pmt_curr,
	i.invoice_currency_code         	C_invoice_currency_code,
	i.payment_currency_code         	C_payment_currency_code,
	i.invoice_type_lookup_code      	C_invoice_type_lookup_code,
	DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 1, 2)  C_sort_type,
	 l1.displayed_field              	C_invoice_type_dsp,
	i.invoice_id                    	C_invoice_id,
	i.invoice_num                   	C_invoice_num,
        NVL(to_char(i.doc_sequence_value), i.voucher_num)		C_inv_voucher_num,
        --11864080: Added call to FND_DATE API
	fnd_date.date_to_chardate(dateval=>i.invoice_date,calendar_aware=> :C_CALENDAR_AWARE_ALT)  C_invoice_date,
 	DECODE( i.invoice_type_lookup_code, 'PREPAYMENT',
		DECODE(i.earliest_settlement_date, '','PERMANENT',
		fnd_date.date_to_chardate(dateval=> i.earliest_settlement_date,
	 	calendar_aware => :C_CALENDAR_AWARE_ALT )),/*Bug11774150*/
		'')  C_earliest_settlement_date,
        	i.invoice_amount  C_invoice_amount,
       	i.description                   	C_description,
       	i.payment_status_flag           	C_payment_status_flag,
	nvl( AP_INVOICES_UTILITY_PKG.get_amount_withheld( i.invoice_id ), 0)  C_withheld_amount,
	DECODE( 	i.invoice_type_lookup_code, 'PREPAYMENT',
		AP_PREPAY_UTILS_PKG.get_prepay_amount_remaining( i.invoice_id ),
		sum( nvl(aps.amount_remaining,0) ) ) C_amount_remaining,
                 i.payment_cross_rate  C_payment_cross_rate,
	AP_APXINPSR_XMLP_PKG.c_amount_for_sumformula(i.invoice_type_lookup_code, DECODE ( i.invoice_type_lookup_code , 'PREPAYMENT' , AP_PREPAY_UTILS_PKG.get_prepay_amount_remaining ( i.invoice_id ) , sum ( nvl ( aps.amount_remaining , 0 ) ) ), i.payment_status_flag, DECODE ( i.invoice_type_lookup_code , 'PREPAYMENT' , DECODE ( i.earliest_settlement_date , '' , 'PERMANENT' , fnd_date.date_to_chardate (dateval => i.earliest_settlement_date, calendar_aware => :C_CALENDAR_AWARE_ALT ) ) , '' ), i.payment_cross_rate, i.invoice_currency_code) C_amount_for_sum,/*Bug11774150*/
	AP_APXINPSR_XMLP_PKG.c_amount_remaining_convertform(i.payment_cross_rate, DECODE ( i.invoice_type_lookup_code , 'PREPAYMENT' , AP_PREPAY_UTILS_PKG.get_prepay_amount_remaining ( i.invoice_id ) , sum ( nvl ( aps.amount_remaining , 0 ) ) ), i.invoice_currency_code, i.invoice_type_lookup_code) C_amount_remaining_convert,
	AP_APXINPSR_XMLP_PKG.c_amount_remaining_dspformula(i.invoice_type_lookup_code, i.payment_status_flag, :C_amount_remaining_trans) C_amount_remaining_dsp
FROM    ap_lookup_codes l1, ap_invoices i, ap_payment_schedules_all aps
WHERE  i.cancelled_date IS NULL
AND     aps.invoice_id = i.invoice_id
AND     l1.lookup_type = 'INVOICE TYPE'
AND     l1.lookup_code = i.invoice_type_lookup_code
&C_invoice_dynamic_SQL
 and i.vendor_id=:C_vendor_id1
GROUP BY i.vendor_id,
	i.invoice_currency_code||i.payment_currency_code,
                  	 i.invoice_currency_code, i.payment_currency_code,
         	 i.invoice_type_lookup_code, DECODE(i.invoice_type_lookup_code, 'PREPAYMENT', 1, 2),
	 l1.displayed_field, i.invoice_id, i.invoice_num, 	nvl(to_char(i.doc_sequence_value),i.voucher_num),
	  i.invoice_date,
	 DECODE( i.invoice_type_lookup_code, 'PREPAYMENT',
	 DECODE(i.earliest_settlement_date, '','PERMANENT',
	 fnd_date.date_to_chardate(dateval => i.earliest_settlement_date,calendar_aware => :C_CALENDAR_AWARE_ALT )),''),/*Bug11774150*/
	 i.invoice_amount,
	 i.description, i.payment_status_flag,
	 nvl( AP_INVOICES_UTILITY_PKG.get_amount_withheld( i.invoice_id ), 0),
	i.payment_cross_rate
--ORDER BY  5,i.invoice_date, i.invoice_num
ORDER BY 3 ASC,   4 ASC,   2 ASC,   1 ASC,   6 ASC,   5,   i.invoice_date,   i.invoice_num
Ask a question
Parameter Name SQL text Validation
Show Prepayment Lines
 
LOV Oracle
Prepayment Type
 
LOV Oracle
To Date
 
Date
From Date
 
Date
Supplier Name
 
LOV Oracle
Supplier Type
 
LOV Oracle
Include Credit/Debit Memos
 
LOV Oracle
Include Invoices
 
LOV Oracle