JL Argentine Payables Supplier Statement- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Argentine Payables Supplier Statement
Application: Latin America Localizations
Source: Argentine Payables Supplier Statement (XML) - Not Supported: Reserved For Future Use
Short Name: JLARPSSR_XML
DB package: JL_JLZZPSSR_XMLP_PKG
Run JL Argentine Payables Supplier Statement- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code)  GROUP_CURRENCY
,'APPLIED' 			SECTION
, 1				TRANS_ORDER
,'INV'				RELATE_TRANS_TYPE
, ai.invoice_id 		TRANS_ID
,'INV'				TRANS_TYPE
, ai.invoice_currency_code 	TRANS_CURRENCY
, ai.invoice_date		TRANS_DATE
, ai.invoice_type_lookup_code 	TYPE_CODE
, alc.displayed_field 		TYPE_DESC
, ai.invoice_num			TRANS_NUM
, sum(aid.amount )			TRANS_AMT
, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) ) CF_SUPP_BB, 
--	JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_CURRENCY, 
--  JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) 
    JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_TO_APPLY, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) ), 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM &lp_ap_invoices ai,
           &lp_ap_invoice_distributions aid,
           ap_lookup_codes alc,
           xla_distribution_links lk,
           xla_ae_headers aeh,
           xla_ae_lines ael
WHERE aid.accounting_date between :p_start_date and :p_end_date
AND ( aid.line_type_lookup_code <> 'AWT' and aid.line_type_lookup_code <> 'PREPAY' )
AND aid.posted_flag = 'Y'
AND aeh.ae_header_id = ael.ae_header_id
and  lk.ae_header_id = ael.ae_header_id
and  lk.ae_line_num = ael.ae_line_num
and  lk.application_id = 200
and  lk.source_distribution_type ='AP_INV_DIST'
and ( lk.accounting_line_code = 'AP_LIAB_INV' or lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM')
and aid.invoice_distribution_id = lk.source_distribution_id_num_1
and ael.ledger_id = :p_sob_id
&LP_AP_INVOICES_WH
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
GROUP BY ai.invoice_id, ai.invoice_currency_code, ai.invoice_date, ai.invoice_type_lookup_code,alc.displayed_field,ai.invoice_num,ai.vendor_id
UNION
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code)  GROUP_CURRENCY
,'APPLIED' 			SECTION
, 2	 			TRANS_ORDER
,'INV'				RELATE_TRANS_TYPE
, ai.invoice_id			TRANS_ID
,'AWT'			TRANS_TYPE
, ai.invoice_currency_code		TRANS_CURRENCY
, aid.accounting_date		TRANS_DATE
, aid.line_type_lookup_code		TYPE_CODE
, alc. displayed_field 		TYPE_DESC
, NULL				TRANS_NUM
, sum(aid.amount)			TRANS_AMT
,sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
--	JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr)))) CF_AMOUNT_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY)    
    JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr)))) CF_AMOUNT_TO_APPLY, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) , 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM &lp_ap_invoices ai,
           &lp_ap_invoice_distributions aid,
           ap_lookup_codes alc,
           xla_distribution_links lk,
           xla_ae_headers aeh,
           xla_ae_lines ael
WHERE 'APPROVAL' = :cp_create_awt_dists_type
AND  aid.accounting_date between :p_start_date and :p_end_date
AND aid.line_type_lookup_code = 'AWT'
AND aid.posted_flag = 'Y'
AND aeh.ae_header_id = ael.ae_header_id
and  lk.ae_header_id = ael.ae_header_id
and  lk.ae_line_num = ael.ae_line_num
and  lk.application_id = 200
and  lk.source_distribution_type ='AP_INV_DIST'
and  (lk.accounting_line_code = 'AP_LIAB_INV' OR lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM')
and aid.invoice_distribution_id = lk.source_distribution_id_num_1
and ael.ledger_id = :p_sob_id
&LP_AP_INVOICES_WH
AND aid.invoice_id = ai.invoice_id
AND alc.lookup_type =  'INVOICE DISTRIBUTION TYPE'
AND alc.lookup_code = aid.line_type_lookup_code
GROUP BY ai.invoice_id, ai.invoice_currency_code ,aid.accounting_date, aid.line_type_lookup_code, alc.displayed_field,ai.vendor_id
UNION
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ac.currency_code)  GROUP_CURRENCY
,'APPLIED' 			SECTION
, 3				TRANS_ORDER
, 'PMT'			RELATE_TRANS_TYPE
, aip.check_id			TRANS_ID
, 'PMT'			TRANS_TYPE
, ac.currency_code		TRANS_CURRENCY
, ac.check_date 			TRANS_DATE
, ac.payment_method_lookup_code	TYPE_CODE
, alc.displayed_field		TYPE_DESC
, to_char(ac.check_number)		TRANS_NUM
, sum(aip.amount)*-1			TRANS_AMT
, sum(nvl(aip.invoice_base_amount,aip.amount))*-1	TRANS_BASE_AMT
, ac.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ac.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code ),JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code ))) CF_SUPP_BB, 
--	JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ac.currency_code) CF_TRX_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1) CF_AMOUNT_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) 
    JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1) CF_AMOUNT_TO_APPLY, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', aip.check_id, sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1, 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ac.currency_code)) CF_AMOUNT_DUE_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', aip.check_id) CF_REF_TRANS_DATE
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM &lp_ap_invoice_payments aip ,
           &lp_ap_checks ac,
           &lp_ap_invoices ai,
           ap_lookup_codes alc
WHERE aip.accounting_date between :p_start_date and :p_end_date
AND aip.posted_flag = 'Y'
&LP_AP_INVOICES_WH
&LP_AP_CHECKS_WH
AND aip.invoice_id = ai.invoice_id
AND aip.check_id = ac.check_id
AND alc.lookup_type = 'PAYMENT METHOD'
AND ac. payment_method_lookup_code = alc.lookup_code
GROUP BY aip.check_id,ac.currency_code,ac.check_date,ac.payment_method_lookup_code,alc.displayed_field,to_char(ac.check_number),ac.vendor_id
UNION
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code)  GROUP_CURRENCY
,'APPLIED' 			SECTION
,4				TRANS_ORDER
,'PMT'			RELATE_TRANS_TYPE
, aip.check_id 			TRANS_ID
,'AWT'			TRANS_TYPE
, ai.invoice_currency_code 		TRANS_CURRENCY
, aid.accounting_date	 	TRANS_DATE
, aid.line_type_lookup_code 		TYPE_CODE
, alc.displayed_field 		TYPE_DESC
, NULL				TRANS_NUM
, sum(aid.amount)			TRANS_AMT
, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
--	JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) 
JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_TO_APPLY, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', aip.check_id , sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))), 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) ) CF_AMOUNT_DUE_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', aip.check_id) CF_REF_TRANS_DATE
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM &lp_ap_invoices ai,
           &lp_ap_invoice_distributions aid,
           &lp_ap_invoice_payments aip,
           ap_lookup_codes alc,
           xla_distribution_links lk,
           xla_ae_headers aeh,
           xla_ae_lines ael
WHERE 'PAYMENT' = :cp_create_awt_dists_type
AND  aid.accounting_date between :p_start_date and :p_end_date
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
AND aid.posted_flag = 'Y'
AND aeh.ae_header_id = ael.ae_header_id
and  lk.ae_header_id = ael.ae_header_id
and  lk.ae_line_num = ael.ae_line_num
and  lk.application_id = 200
and  lk.source_distribution_type ='AP_INV_DIST'
and  ( lk.accounting_line_code = 'AP_LIAB_INV' or lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM')
and aid.invoice_distribution_id = lk.source_distribution_id_num_1
and ael.ledger_id = :p_sob_id
&LP_AP_INVOICES_WH
AND aid.invoice_id = ai.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND alc.lookup_type =  'INVOICE DISTRIBUTION TYPE'
AND alc.lookup_code = aid.line_type_lookup_code
GROUP BY aip.check_id ,ai.invoice_currency_code ,aid.accounting_date,aid.line_type_lookup_code, alc.displayed_field,'X',0,ai.vendor_id
UNION
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code)  GROUP_CURRENCY
,'APPLIED' 			SECTION
,5				TRANS_ORDER
,'PRPMT'			RELATE_TRANS_TYPE
, ai.invoice_id 			TRANS_ID
,'PRPMT'			TRANS_TYPE
, ai.invoice_currency_code 		TRANS_CURRENCY
, ai.invoice_date			TRANS_DATE
, ai.invoice_type_lookup_code 	TYPE_CODE
, alc.displayed_field 		TYPE_DESC
, ai.invoice_num			TRANS_NUM
, (nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1		TRANS_AMT
,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1 TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
--	JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula((nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1) CF_AMOUNT_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) 
JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula((nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1) CF_AMOUNT_TO_APPLY, 
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, (nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1 ,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1, 'APPLIED',JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) ) CF_AMOUNT_DUE_CURRENCY, 
--	JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, 
	JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
--	JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM &lp_ap_invoices ai,
           &lp_ap_invoice_distributions aid,
           ap_lookup_codes alc
WHERE aid.accounting_date between :p_start_date and :p_end_date
AND ai.invoice_type_lookup_code =  'PREPAYMENT'
AND aid.line_type_lookup_code = 'ITEM'
AND ai.payment_status_flag != 'N'  
AND aid.posted_flag = 'Y'
&LP_AP_INVOICES_WH
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
GROUP BY ai.invoice_id, ai.invoice_currency_code, ai.invoice_date, ai.invoice_type_lookup_code, alc.displayed_field, ai.invoice_num, ai.vendor_id
HAVING nvl(sum(aid.prepay_amount_remaining),-1) !=-1
UNION
SELECT
DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code)  GROUP_CURRENCY
,'UNAPPLIED' 			SECTION
, 6				TRANS_ORDER
,'PRPMT'			RELATE_TRANS_TYPE
, ai.invoice_id 		TRANS_ID
,'PRPMT'			TRANS_TYPE
, ai.invoice_currency_code 	TRANS_CURRENCY
, ai.invoice_date		TRANS_DATE
, ai.invoice_type_lookup_code 	TYPE_CODE
, alc.displayed_field 		TYPE_DESC
, ai.invoice_num		TRANS_NUM
, sum(aid.amount) *-1		TRANS_AMT
, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1 TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID, 
	JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE (</