JE Polish Supplier Statement- Not Supported: Reserved For Future Use

SELECT
DECODE(:p_report_currency, 'F', :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(aid.base_amount,aid.amount))	TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID
, ai.vendor_site_id			VENDOR_SITE_ID
, 
	JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code))) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', :CF_TRX_PRECISION) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoices_all ai, 
           ap_invoice_distributions_all aid,  
           ap_lookup_codes alc
WHERE aid.accounting_date between :lp_start_date and :lp_end_date
AND ( aid.line_type_lookup_code <> 'AWT' and aid.line_type_lookup_code <> 'PREPAY' )
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
AND aid.posted_flag LIKE DECODE(:P_POSTED,'N', '%','Y')	
AND ai.invoice_type_lookup_code <> 'PREPAYMENT'
&P_WHERE
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_aid
and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_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,ai.vendor_site_id
--,aid.amount,aid.base_amount
UNION
SELECT
DECODE(:p_report_currency, 'F', :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(aid.base_amount,aid.amount))	TRANS_BASE_AMT
, ai.vendor_id				VENDOR_ID
, ai.vendor_site_id				VENDOR_SITE_ID
, 
	JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code))) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', :CF_TRX_PRECISION) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoices_all ai, 
           ap_invoice_distributions_all aid,  
           ap_lookup_codes alc
WHERE 'APPROVAL' = :cp_create_awt_dists_type
AND  aid.accounting_date between :lp_start_date and :lp_end_date
AND aid.line_type_lookup_code = 'AWT'
AND aid.invoice_id = ai.invoice_id
AND alc.lookup_type =  'INVOICE DISTRIBUTION TYPE'
AND alc.lookup_code = aid.line_type_lookup_code 
AND aid.posted_flag LIKE DECODE(:P_POSTED,'N', '%','Y')	
&P_WHERE
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_aid
and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_id1
GROUP BY ai.invoice_id, ai.invoice_currency_code ,aid.accounting_date, aid.line_type_lookup_code, alc.displayed_field,ai.vendor_id,ai.vendor_site_id
--,aid.amount,aid.base_amount
UNION
SELECT
DECODE(:p_report_currency, 'F', :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
, ai.vendor_site_id				VENDOR_SITE_ID
, JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code))) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) ), 'APPLIED', :CF_TRX_PRECISION) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aip.amount ), sum ( nvl ( aip.invoice_base_amount , aip.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoice_payments_all aip ,
           ap_checks_all ac,
           ap_invoices_all ai, 
           ap_lookup_codes alc
WHERE aip.accounting_date between :lp_start_date and :lp_end_date
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 ai.invoice_id IN (
SELECT invoice_id	
FROM ap_invoice_distributions_all aid
       	WHERE aid. invoice_id = ai.invoice_id 
                      	AND posted_flag  LIKE DECODE(:P_POSTED,'N', '%','Y')	
                      	AND rownum =1)
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_ac
&p_org_where_aip
and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_id1
GROUP BY aip.check_id,ac.currency_code,ac.check_date,ac.payment_method_lookup_code,alc.displayed_field,ac.check_number,ac.vendor_id
,ai.vendor_site_id
--,aip.amount,aip.invoice_base_amount
,ai.invoice_currency_code,ai.invoice_id,ac.currency_code,ai.vendor_id
UNION
SELECT 
DECODE(:p_report_currency, 'F', :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(aid.base_amount,aid.amount))	TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID
, ai.vendor_site_id			VENDOR_SITE_ID
, 
	JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code))) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', :CF_TRX_PRECISION) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) , JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoices_all ai,
           ap_invoice_distributions_all aid, 
           ap_invoice_payments_all aip,  
           ap_lookup_codes alc
WHERE 'PAYMENT' = :cp_create_awt_dists_type 
AND  aid.accounting_date between :lp_start_date and :lp_end_date
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
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 aid.posted_flag LIKE DECODE(:P_POSTED,'N', '%','Y')	
&P_WHERE
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_aid
&p_org_where_aip
and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_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,ai.vendor_site_id
--,aid.amount,aid.base_amount
,ai.invoice_id
UNION
SELECT 
DECODE(:p_report_currency, 'F', :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
, ai.vendor_site_id			VENDOR_SITE_ID
, 
	JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', :CF_TRX_PRECISION)) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV', JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoices_all ai, 
           ap_invoice_distributions_all aid, 
           ap_lookup_codes alc
WHERE aid.accounting_date between :lp_start_date and :lp_end_date
AND ai.invoice_type_lookup_code =  'PREPAYMENT'
AND ai.payment_status_flag != 'N'  
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
AND aid.posted_flag LIKE DECODE(:P_POSTED,'N', '%','Y')	
&P_WHERE
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_aid
and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_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,ai.vendor_site_id
--,aid.amount,aid.prepay_amount_remaining
,ai.exchange_rate
HAVING nvl(sum(aid.prepay_amount_remaining),-1) !=-1
UNION
SELECT 
DECODE(:p_report_currency, 'F', :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(aid.base_amount,aid.amount)) *-1 TRANS_BASE_AMT
, ai.vendor_id			VENDOR_ID
, ai.vendor_site_id			VENDOR_SITE_ID
, 
	JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, 
	JE_JEPLPSSR_XMLP_PKG.cf_vendor_checkformula(ai.vendor_id) CF_VENDOR_CHECK, 
	JE_JEPLPSSR_XMLP_PKG.cf_ending_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))),JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_ENDING_BALANCE, 
	--&group_currency CF_GROUP_CURR,
	JE_JEPLPSSR_XMLP_PKG.CP_TEXT_p CP_TEXT, 
	JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )) CF_AMOUNT_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code))) CF_AMOUNT_TO_APPLY, 
	JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, 
	JE_JEPLPSSR_XMLP_PKG.cf_remain_balanceformula(JE_JEPLPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ), JE_JEPLPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'F' , :c_base_currency_code , ai.invoice_currency_code ))), JE_JEPLPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', 'INV',JE_JEPLPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) )), JE_JEPLPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( aid.base_amount , aid.amount ) ), 'APPLIED', JE_JEPLPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)))) CF_REMAIN_BALANCE, 
	JE_JEPLPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE, 
	JE_JEPLPSSR_XMLP_PKG.cf_1formula(ai.invoice_id) CF_category_name, 
	--&trans_date CF_Trans_Date,
	--JE_JEPLPSSR_XMLP_PKG.CF_TRANS_DATEFORMULA(:TRANS_DATE),
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM,
	JE_JEPLPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE
FROM ap_invoices_all ai, 
     ap_invoice_distributions_all aid,
     ap_lookup_codes alc
WHERE aid.accounting_date <= :lp_end_date
AND ai.invoice_type_lookup_code =  'PREPAYMENT'
AND ai.invoice_id = aid.invoice_id
AND alc.lookup_type =  'INVOICE TYPE'
AND ai.invoice_type_lookup_code = alc.lookup_code
AND aid.posted_flag LIKE DECODE(:P_POSTED,'N', '%','Y')	
&P_WHERE
&P_WHERE_CAT
&p_org_where_ai 
&p_org_where_aid 
 and ai.vendor_id=:vendor_id1 
 and ai.vendor_site_id=:vendor_site_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,ai.vendor_site_id
--,aid.amount,aid.base_amount
HAVING nvl(sum(aid.prepay_amount_remaining),-1) != 0
ORDER BY  1 ASC,14 ASC,15 ASC,2 ASC ,vendor_id,vendor_site_id,
TRANS_DATE,RELATE_TRANS_TYPE,TRANS_ID,TRANS_ORDER