AP Prepayments Status

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