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

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_TYPE_p CP_REF_TRANS_TYPE
  ,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 = :cp_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
	and ai.vendor_id=:vendor_id1
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('AWT', 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('AWT', ai.invoice_id) CF_REF_TRANS_DATE
,JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
  ,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 = :cp_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
	and ai.vendor_id=:vendor_id1
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('PMT', 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('PMT', aip.check_id) CF_REF_TRANS_DATE
,JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
  ,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
	and ac.vendor_id=:vendor_id1
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('AWT', 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('AWT', aip.check_id) CF_REF_TRANS_DATE
,JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
  ,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 = :cp_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
	and ai.vendor_id=:vendor_id1
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('PRPMT', ai.invoice_id) CF_REF_TRANS_DATE
,JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
  ,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
	and ai.vendor_id=:vendor_id1
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 ( :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) *-1, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1) CF_AMOUNT_CURRENCY,
--	JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('UNAPPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY)
JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aid.amount) *-1, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1) CF_AMOUNT_TO_APPLY,
	JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('PRPMT', ai.invoice_id, sum(aid.amount) *-1, 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('PRPMT', ai.invoice_id) CF_REF_TRANS_DATE
,JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
  ,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 <= :p_end_date
&LP_AP_INVOICES_WH
AND ai.invoice_type_lookup_code =  'PREPAYMENT'
AND aid.posted_flag = 'Y'
AND aid.line_type_lookup_code = 'ITEM'
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_PREPAY'
and aid.invoice_distribution_id = lk.source_distribution_id_num_1
and ael.ledger_id = :cp_sob_id
AND ai.cancelled_date is NULL
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
 and ai.vendor_id=:vendor_id1
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) != 0
--ORDER BY  TRANS_DATE,RELATE_TRANS_TYPE,TRANS_ID,TRANS_ORDER
ORDER BY 1 ASC,14 ASC,2 ASC , TRANS_DATE , RELATE_TRANS_TYPE , TRANS_ID , TRANS_ORDER