JL Chilean Payables Supplier Statement - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: Chilean Payables Supplier Statement (XML) - Not Supported: Reserved For Future Use
Short Name: JLCLPSSR_XML
DB package: JL_JLZZPSSR_XMLP_PKG
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
Parameter Name SQL text Validation
Minimum Precision
 
Number
Organization ID
 
Ledger ID
 
End Date
 
Date
Start Date
 
Date
Supplier Registration Number
 
Supplier Name To
 
LOV Oracle
Supplier Name From
 
LOV Oracle
Report Currency
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Legal Entity Name
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle