ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4708251A9E05362FB09050512 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 Y JE JEPLPSSR_XML US JE Polish Supplier Statement- Not Supported: Reserved For Future Use 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 BI Publisher &group_currency &p_org_where_ac &p_org_where_ai &p_org_where_aid &p_org_where_aip &p_where &p_where_cat &trans_date :applications_template_report :c_base_currency_code :c_base_description :c_base_min_acct_unit :c_base_precision :c_chart_of_accounts_id :c_company_name_header :c_nls_all :c_nls_end_of_report :c_nls_na :c_nls_no :c_nls_no_data_exists :c_nls_void :c_nls_yes :c_report_start_date :cf_trx_precision :cp_approved_flag :cp_create_awt_dists_type :cp_date4_format :cp_document_category :cp_ledger_name :cp_message :cp_parameters_desc :cp_partial_ledger_msg :cp_posted_flag :cp_ref_trans_num :cp_ref_trans_type :cp_report_curr_disp :cp_report_currency :cp_supplier_name_from :cp_supplier_name_to :cp_supplier_tax_id :cp_text :cp_title :lp_end_date :lp_start_date :lp_supplier :p_approved :p_check_ledger_in_sp :p_conc_request_id :p_country_code :p_debug_switch :p_document_category :p_end_date :p_ledger_id :p_ledger_name :p_min_precision :p_org_where_ac :p_org_where_ai :p_org_where_ai2 :p_org_where_aid :p_org_where_aid2 :p_org_where_aip :p_org_where_alc :p_org_where_posit :p_parameters_desc :p_partial_ledger_msg :p_posted :p_report_currency :p_report_currency_disp :p_reporting_entity_id :p_reporting_entity_name :p_reporting_level :p_reporting_level_name :p_start_date :p_supplier_name_from :p_supplier_name_to :p_supplier_tax_id :p_trace_switch :p_vendor_id :p_where :p_where_cat :vendor_id1 :vendor_site_id1 1 -40 :p_ledger_id Char fnd_profile.value('GL_SET_OF_BKS_ID') Y US Ledger ID Ledger ID 2 -30 :p_min_precision Number fnd_profile.value('CURRENCY:MIXED_PRECISION') Y US Minimum Precision Minimum Precision 3 -20 :p_org_id Char fnd_profile.value('ORG_ID') Y US Organization ID Organization ID 4 10 :p_reporting_level LOV Oracle FND_MO_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' and lookup_code in (1000,3000) order by value,description SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'FND_MO_REPORTING_LEVEL' AND rownum=1 Y US Reporting Level Reporting Level 5 20 :p_reporting_entity_id LOV Oracle FND_MO_REPORTING_ENTITY Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.fnd_mo_reporting_level order by value,description SELECT entity_name, entity_id FROM FND_MO_REPORTING_ENTITIES_V WHERE reporting_level = :$flex$.reporting_level and rownum=1 Y US Reporting Context Reporting Context 6 30 :p_report_currency LOV Oracle JGZZ_CURRENCY_TYPE Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='JGZZ_CURRENCY_TYPE' and start_date_active<=sysdate and nvl(end_date_active,sysdate)>=sysdate order by value,description SELECT meaning FROM fnd_lookups WHERE lookup_type='JGZZ_CURRENCY_TYPE' AND lookup_code = 'F' Y US Report Currency Report Currency 7 40 :p_supplier_name_from LOV Oracle AP_SRS_VENDOR_NAME_CHAR Y select po.vendor_name id, po.vendor_name value, null description from po_vendors po where po.vendor_id= (select max(po2.vendor_id) from po_vendors po2 where po2.vendor_name=po.vendor_name) order by po.vendor_name US Supplier Name From Supplier Name From 8 50 :p_supplier_name_to LOV Oracle AP_SRS_VENDOR_NAME_CHAR Y select po.vendor_name id, po.vendor_name value, null description from po_vendors po where po.vendor_id= (select max(po2.vendor_id) from po_vendors po2 where po2.vendor_name=po.vendor_name) order by po.vendor_name US Supplier Name To Supplier Name To 9 60 :p_supplier_tax_id LOV Oracle JLZZ_AP_TAXPAYER_ID Y select nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)) id, nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)) value, null description from (select individual_1099,num_1099,employee_id,global_attribute12 from po_vendors) pv,(select distinct person_id,national_identifier from per_all_people_f where trunc(sysdate) between effective_start_date and effective_end_date) papf where nvl(pv.employee_id,-99)=papf.person_id (+) order by nvl(papf.national_identifier,nvl(pv.individual_1099,pv.num_1099)) US Supplier Taxpayer ID Supplier Taxpayer ID 10 70 :p_start_date Date sysdate Y US Start Date Start Date 11 80 :p_end_date Date sysdate Y US End Date End Date 12 90 :p_document_category LOV Oracle JGZZ_AP_DOC_CATEGORIES Y select code id, name value, null description from fnd_doc_sequence_categories where application_id=200 order by name US Document Category Document Category 13 100 :p_posted LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Posted Only Posted Only 14 110 :p_approved LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Approved Only Approved Only