JE Polish Supplier Statement - draft

➜ Back to Library
Description
Categories: BI Publisher
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 Name SQL text Validation
Organization ID 
 
Minimum Precision 
 
 Number
Ledger ID 
 
Approved Only 
 
 LOV Oracle
Posted Only 
 
 LOV Oracle
Document Category 
 
 LOV Oracle
End Date 
 
 Date
Start Date 
 
 Date
Supplier Taxpayer ID 
 
 LOV Oracle
Supplier Name To 
 
 LOV Oracle
Supplier Name From 
 
 LOV Oracle
Report Currency 
 
 LOV Oracle
Reporting Context 
 
 LOV Oracle
Reporting Level 
 
 LOV Oracle
Ask a question
Download
Related Blitz Reports
AP Withholding Tax by Invoice AP Open Balances Revaluation AP Supplier Statement JL Chilean Payables Supplier Statement - draft JL Argentine Payables Supplier Statement - draft IGI CIS2007 Registered Supplier Listing JA India - Creditors Ledger - draft PA Supplier Cost Audit IGI CIS2007 Subcontractor Verification Status
Related Topics
JE Spanish Inter-EU Invoice Format (Documento Equivalente)JE Hungarian Accumulated Depreciation Movement Schedule - draftJE Annual Online VAT Register Reporting for SpainJE Italian Receivables Deferred VAT RegisterJE Czech Export TaxJE Italian Journal LedgerJE Italian Payables Summary VATFND W3H_User_ObjectsJE Italian Purchase VAT RegisterPA Refresh Project Summary Amounts
Blitz Report In Action