ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D470A651A9E05362FB09050512 SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code) GROUP_CURRENCY ,'APPLIED' SECTION , 1 TRANS_ORDER ,'INV' RELATE_TRANS_TYPE , ai.invoice_id TRANS_ID ,'INV' TRANS_TYPE , ai.invoice_currency_code TRANS_CURRENCY , ai.invoice_date TRANS_DATE , ai.invoice_type_lookup_code TYPE_CODE , alc.displayed_field TYPE_DESC , ai.invoice_num TRANS_NUM , sum(aid.amount ) TRANS_AMT , sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT , ai.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) ) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) ), 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoices ai, &lp_ap_invoice_distributions aid, ap_lookup_codes alc, xla_distribution_links lk, xla_ae_headers aeh, xla_ae_lines ael WHERE aid.accounting_date between :p_start_date and :p_end_date AND ( aid.line_type_lookup_code <> 'AWT' and aid.line_type_lookup_code <> 'PREPAY' ) AND aid.posted_flag = 'Y' AND aeh.ae_header_id = ael.ae_header_id and lk.ae_header_id = ael.ae_header_id and lk.ae_line_num = ael.ae_line_num and lk.application_id = 200 and lk.source_distribution_type ='AP_INV_DIST' and ( lk.accounting_line_code = 'AP_LIAB_INV' or lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM') and aid.invoice_distribution_id = lk.source_distribution_id_num_1 and ael.ledger_id = :p_sob_id &LP_AP_INVOICES_WH AND ai.invoice_id = aid.invoice_id AND alc.lookup_type = 'INVOICE TYPE' AND ai.invoice_type_lookup_code = alc.lookup_code GROUP BY ai.invoice_id, ai.invoice_currency_code, ai.invoice_date, ai.invoice_type_lookup_code,alc.displayed_field,ai.invoice_num,ai.vendor_id UNION SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code) GROUP_CURRENCY ,'APPLIED' SECTION , 2 TRANS_ORDER ,'INV' RELATE_TRANS_TYPE , ai.invoice_id TRANS_ID ,'AWT' TRANS_TYPE , ai.invoice_currency_code TRANS_CURRENCY , aid.accounting_date TRANS_DATE , aid.line_type_lookup_code TYPE_CODE , alc. displayed_field TYPE_DESC , NULL TRANS_NUM , sum(aid.amount) TRANS_AMT ,sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT , ai.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr)))) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr)))) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) , 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoices ai, &lp_ap_invoice_distributions aid, ap_lookup_codes alc, xla_distribution_links lk, xla_ae_headers aeh, xla_ae_lines ael WHERE 'APPROVAL' = :cp_create_awt_dists_type AND aid.accounting_date between :p_start_date and :p_end_date AND aid.line_type_lookup_code = 'AWT' AND aid.posted_flag = 'Y' AND aeh.ae_header_id = ael.ae_header_id and lk.ae_header_id = ael.ae_header_id and lk.ae_line_num = ael.ae_line_num and lk.application_id = 200 and lk.source_distribution_type ='AP_INV_DIST' and (lk.accounting_line_code = 'AP_LIAB_INV' OR lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM') and aid.invoice_distribution_id = lk.source_distribution_id_num_1 and ael.ledger_id = :p_sob_id &LP_AP_INVOICES_WH AND aid.invoice_id = ai.invoice_id AND alc.lookup_type = 'INVOICE DISTRIBUTION TYPE' AND alc.lookup_code = aid.line_type_lookup_code GROUP BY ai.invoice_id, ai.invoice_currency_code ,aid.accounting_date, aid.line_type_lookup_code, alc.displayed_field,ai.vendor_id UNION SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ac.currency_code) GROUP_CURRENCY ,'APPLIED' SECTION , 3 TRANS_ORDER , 'PMT' RELATE_TRANS_TYPE , aip.check_id TRANS_ID , 'PMT' TRANS_TYPE , ac.currency_code TRANS_CURRENCY , ac.check_date TRANS_DATE , ac.payment_method_lookup_code TYPE_CODE , alc.displayed_field TYPE_DESC , to_char(ac.check_number) TRANS_NUM , sum(aip.amount)*-1 TRANS_AMT , sum(nvl(aip.invoice_base_amount,aip.amount))*-1 TRANS_BASE_AMT , ac.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ac.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code ),JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ac.currency_code ))) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ac.currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', aip.check_id, sum(aip.amount)*-1, sum(nvl(aip.invoice_base_amount,aip.amount))*-1, 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ac.currency_code)) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', aip.check_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoice_payments aip , &lp_ap_checks ac, &lp_ap_invoices ai, ap_lookup_codes alc WHERE aip.accounting_date between :p_start_date and :p_end_date AND aip.posted_flag = 'Y' &LP_AP_INVOICES_WH &LP_AP_CHECKS_WH AND aip.invoice_id = ai.invoice_id AND aip.check_id = ac.check_id AND alc.lookup_type = 'PAYMENT METHOD' AND ac. payment_method_lookup_code = alc.lookup_code GROUP BY aip.check_id,ac.currency_code,ac.check_date,ac.payment_method_lookup_code,alc.displayed_field,to_char(ac.check_number),ac.vendor_id UNION SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code) GROUP_CURRENCY ,'APPLIED' SECTION ,4 TRANS_ORDER ,'PMT' RELATE_TRANS_TYPE , aip.check_id TRANS_ID ,'AWT' TRANS_TYPE , ai.invoice_currency_code TRANS_CURRENCY , aid.accounting_date TRANS_DATE , aid.line_type_lookup_code TYPE_CODE , alc.displayed_field TYPE_DESC , NULL TRANS_NUM , sum(aid.amount) TRANS_AMT , sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) TRANS_BASE_AMT , ai.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum ( aid.amount ), sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) )) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', aip.check_id , sum ( aid.amount ), sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))), 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) ) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', aip.check_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoices ai, &lp_ap_invoice_distributions aid, &lp_ap_invoice_payments aip, ap_lookup_codes alc, xla_distribution_links lk, xla_ae_headers aeh, xla_ae_lines ael WHERE 'PAYMENT' = :cp_create_awt_dists_type AND aid.accounting_date between :p_start_date and :p_end_date AND aid.awt_invoice_payment_id = aip.invoice_payment_id AND aid.posted_flag = 'Y' AND aeh.ae_header_id = ael.ae_header_id and lk.ae_header_id = ael.ae_header_id and lk.ae_line_num = ael.ae_line_num and lk.application_id = 200 and lk.source_distribution_type ='AP_INV_DIST' and ( lk.accounting_line_code = 'AP_LIAB_INV' or lk.accounting_line_code='AP_LIAB_CM' or lk.accounting_line_code='AP_LIAB_DM') and aid.invoice_distribution_id = lk.source_distribution_id_num_1 and ael.ledger_id = :p_sob_id &LP_AP_INVOICES_WH AND aid.invoice_id = ai.invoice_id AND aid.line_type_lookup_code = 'AWT' AND alc.lookup_type = 'INVOICE DISTRIBUTION TYPE' AND alc.lookup_code = aid.line_type_lookup_code GROUP BY aip.check_id ,ai.invoice_currency_code ,aid.accounting_date,aid.line_type_lookup_code, alc.displayed_field,'X',0,ai.vendor_id UNION SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code) GROUP_CURRENCY ,'APPLIED' SECTION ,5 TRANS_ORDER ,'PRPMT' RELATE_TRANS_TYPE , ai.invoice_id TRANS_ID ,'PRPMT' TRANS_TYPE , ai.invoice_currency_code TRANS_CURRENCY , ai.invoice_date TRANS_DATE , ai.invoice_type_lookup_code TYPE_CODE , alc.displayed_field TYPE_DESC , ai.invoice_num TRANS_NUM , (nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1 TRANS_AMT ,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1 TRANS_BASE_AMT , ai.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula((nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula((nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, (nvl(sum(aid.amount),0)-nvl(sum(aid.prepay_amount_remaining),0)) *-1 ,(nvl(sum(aid.amount*NVL(ai.exchange_rate,1)),0)-nvl(sum(aid.prepay_amount_remaining*NVL(ai.exchange_rate,1)),0)) *-1, 'APPLIED',JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) ) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoices ai, &lp_ap_invoice_distributions aid, ap_lookup_codes alc WHERE aid.accounting_date between :p_start_date and :p_end_date AND ai.invoice_type_lookup_code = 'PREPAYMENT' AND aid.line_type_lookup_code = 'ITEM' AND ai.payment_status_flag != 'N' AND aid.posted_flag = 'Y' &LP_AP_INVOICES_WH AND ai.invoice_id = aid.invoice_id AND alc.lookup_type = 'INVOICE TYPE' AND ai.invoice_type_lookup_code = alc.lookup_code GROUP BY ai.invoice_id, ai.invoice_currency_code, ai.invoice_date, ai.invoice_type_lookup_code, alc.displayed_field, ai.invoice_num, ai.vendor_id HAVING nvl(sum(aid.prepay_amount_remaining),-1) !=-1 UNION SELECT DECODE(:p_report_currency, 'FUNCTIONAL', :c_base_currency_code, ai.invoice_currency_code) GROUP_CURRENCY ,'UNAPPLIED' SECTION , 6 TRANS_ORDER ,'PRPMT' RELATE_TRANS_TYPE , ai.invoice_id TRANS_ID ,'PRPMT' TRANS_TYPE , ai.invoice_currency_code TRANS_CURRENCY , ai.invoice_date TRANS_DATE , ai.invoice_type_lookup_code TYPE_CODE , alc.displayed_field TYPE_DESC , ai.invoice_num TRANS_NUM , sum(aid.amount) *-1 TRANS_AMT , sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1 TRANS_BASE_AMT , ai.vendor_id VENDOR_ID, JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code )) CF_GROUP_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_supp_bbformula(ai.vendor_id, DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ), JL_JLZZPSSR_XMLP_PKG.cf_group_precisionformula(DECODE ( :p_report_currency , 'FUNCTIONAL' , :c_base_currency_code , ai.invoice_currency_code ))) CF_SUPP_BB, -- JL_JLZZPSSR_XMLP_PKG.cf_ending_balanceformula(:CF_SUPP_BB, :CS_ACCUM_BALANCE) CF_ENDING_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code) CF_TRX_PRECISION, JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aid.amount) *-1, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1) CF_AMOUNT_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_amount_to_applyformula('APPLIED', :CF_AMOUNT_CURRENCY, :CF_AMOUNT_DUE_CURRENCY) JL_JLZZPSSR_XMLP_PKG.cf_amount_currencyformula(sum(aid.amount) *-1, sum(nvl(nvl(ael.accounted_dr, ael.entered_dr ),nvl( ael.accounted_cr, ael.entered_cr))) *-1) CF_AMOUNT_TO_APPLY, JL_JLZZPSSR_XMLP_PKG.cf_amount_due_currencyformula('INV', ai.invoice_id, sum(aid.amount) *-1, sum ( nvl ( nvl ( ael.accounted_dr , ael.entered_dr ) , nvl ( ael.accounted_cr , ael.entered_cr ) ) ), 'APPLIED', JL_JLZZPSSR_XMLP_PKG.cf_trx_precisionformula(ai.invoice_currency_code)) CF_AMOUNT_DUE_CURRENCY, -- JL_JLZZPSSR_XMLP_PKG.cf_remain_balanceformula(:CF_SUPP_BB, :CS_AMOUNT_APPLIED) CF_REMAIN_BALANCE, JL_JLZZPSSR_XMLP_PKG.cf_ref_trans_dateformula('INV', ai.invoice_id) CF_REF_TRANS_DATE -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_NUM_p CP_REF_TRANS_NUM, -- JL_JLZZPSSR_XMLP_PKG.CP_REF_TRANS_TYPE_p CP_REF_TRANS_TYPE FROM &lp_ap_invoices ai, &lp_ap_invoice_distributions aid, ap_lookup_codes alc, xla_distribution_links lk, xla_ae_headers aeh, xla_ae_lines ael WHERE aid.accounting_date <= :p_end_date &LP_AP_INVOICES_WH AND ai.invoice_type_lookup_code = 'PREPAYMENT' AND aid.posted_flag = 'Y' AND aid.line_type_lookup_code = 'ITEM' AND aeh.ae_header_id = ael.ae_header_id and lk.ae_header_id = ael.ae_header_id and lk.ae_line_num = ael.ae_line_num and lk.application_id = 200 and lk.source_distribution_type ='AP_INV_DIST' and lk.accounting_line_code = 'AP_LIAB_PREPAY' and aid.invoice_distribution_id = lk.source_distribution_id_num_1 and ael.ledger_id = :p_sob_id AND ai.cancelled_date is NULL AND ai.invoice_id = aid.invoice_id AND alc.lookup_type = 'INVOICE TYPE' AND ai.invoice_type_lookup_code = alc.lookup_code and ai.vendor_id=:vendor_id1 GROUP BY ai.invoice_id, ai.invoice_currency_code, ai.invoice_date, ai.invoice_type_lookup_code, alc.displayed_field, ai.invoice_num, ai.vendor_id HAVING nvl(sum(aid.prepay_amount_remaining),-1) != 0 ORDER BY TRANS_DATE,RELATE_TRANS_TYPE,TRANS_ID,TRANS_ORDER JL JLARPSSR_XML US JL Argentine Payables Supplier Statement- Not Supported: Reserved For Future Use Imported from BI Publisher Description: Argentine Payables Supplier Statement Application: Latin America Localizations Source: Argentine Payables Supplier Statement (XML) - Not Supported: Reserved For Future Use Short Name: JLARPSSR_XML DB package: JL_JLZZPSSR_XMLP_PKG BI Publisher &lp_ap_checks &lp_ap_checks_wh &lp_ap_invoice_distributions &lp_ap_invoice_payments &lp_ap_invoices &lp_ap_invoices_wh :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 :cp_create_awt_dists_type :cp_date4_format :cp_end_date :cp_message :cp_ref_trans_num :cp_ref_trans_type :cp_sob_id :cp_start_date :lp_ap_checks :lp_ap_checks_wh :lp_ap_invoice_distributions :lp_ap_invoice_distributions_wh :lp_ap_invoice_payments :lp_ap_invoice_payments_wh :lp_ap_invoices :lp_ap_invoices_wh :lp_ap_system_parameters :lp_supplier :p_ca_org_id :p_ca_set_of_books_id :p_conc_request_id :p_country_code :p_debug_switch :p_end_date :p_legal_entity_id :p_min_precision :p_mrcsobtype :p_org_id :p_report_currency :p_reporting_context :p_reporting_level :p_sob_id :p_start_date :p_supplier_name_from :p_supplier_name_to :p_supplier_tax_id :p_trace_switch :vendor_id1 1 -40 :p_sob_id Char fnd_profile.value('GL_SET_OF_BKS_ID') Y US Ledger ID Ledger ID 2 -30 :p_org_id Char fnd_profile.value('ORG_ID') Y US Organization ID Organization ID 3 -20 :p_min_precision Number fnd_profile.value('CURRENCY:MIXED_PRECISION') Y US Minimum Precision Minimum Precision 4 10 :p_reporting_level LOV Oracle XLE_LEGAL_REPORTING_LEVEL Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='FND_MO_REPORTING_LEVEL' order by value,description Y US Reporting Level Reporting Level 5 20 :p_reporting_context LOV Oracle XLE_LEGAL_REPORTING_CONTEXT Y select entity_id id, entity_name value, null description from fnd_mo_reporting_entities_v where reporting_level=:$flex$.xle_legal_reporting_level order by value,description Y US Reporting Context Reporting Context 6 30 :p_legal_entity_id LOV Oracle ZX_COMPANY_NAME Y select legal_entity_id id, legal_entity_name value, null description from gl_ledger_le_v where :$flex$.xle_legal_reporting_level='1000' and ledger_id=:$flex$.xle_legal_reporting_context order by value,description US Legal Entity Name Company Name 7 40 :p_report_currency LOV Oracle AP_SRS_REPORT_CURRENCY Y select lookup_code id, displayed_field value, description description from ap_lookup_codes where lookup_type='SRS REPORT CURRENCY' and sysdate<nvl(inactive_date,sysdate+1) order by value,description SELECT displayed_field FROM ap_lookup_codes WHERE lookup_type = 'SRS REPORT CURRENCY' AND lookup_code = 'ORIGINAL' Y US Report Currency Report Currency 8 50 :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 9 60 :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 10 70 :p_supplier_tax_id Char US Supplier Registration Number Supplier Registration Number 11 80 :p_start_date Date sysdate Y US Start Date Start Date 12 90 :p_end_date Date sysdate Y US End Date End Date :$flex$.xle_legal_reporting_context Reporting Context Legal Entity Name :$flex$.xle_legal_reporting_level Reporting Level Legal Entity Name :$flex$.xle_legal_reporting_level Reporting Level Reporting Context