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

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Polish Supplier Statement
Application: European Localizations
Source: Polish Supplier Statement (XML) - Not Supported: Reserved For Future Use
Short Name: JEPLPSSR_XML
DB package: JE_JEPLPSSR_XMLP_PKG
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
Parameter NameSQL textValidation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Report Currency
 
LOV Oracle
Supplier Name From
 
LOV Oracle
Supplier Name To
 
LOV Oracle
Supplier Taxpayer ID
 
LOV Oracle
Start Date
 
Date
End Date
 
Date
Document Category
 
LOV Oracle
Posted Only
 
LOV Oracle
Approved Only
 
LOV Oracle
Download
Blitz Report™