JA India - Creditors Ledger- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Creditors Ledger - India Local
Application: Asia/Pacific Localizations
Source: India - Creditors Ledger (XML) - Not Supported: Reserved For Future Use
Short Name: JAINAPCR_XML
DB package: JA_JAINAPCR_XMLP_PKG
Description: Creditors Ledger - India Local
Application: Asia/Pacific Localizations
Source: India - Creditors Ledger (XML) - Not Supported: Reserved For Future Use
Short Name: JAINAPCR_XML
DB package: JA_JAINAPCR_XMLP_PKG
Run
JA India - Creditors Ledger- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT povs.vendor_site_code, api.invoice_type_lookup_code, api.invoice_num, api.invoice_date, apd.description description, apd.dist_code_combination_id ccid, api.invoice_currency_code, api.exchange_rate, Decode(api.invoice_type_lookup_code,'CREDIT', abs(z.amt_val) - abs(nvl(api.discount_amount_taken,0) ) , 0) dr_val, Decode(api.invoice_type_lookup_code,'CREDIT',0, z.amt_val - nvl(api.discount_amount_taken,0) ) cr_val, 0 acct_dr, 0 acct_cr, to_char(api.doc_sequence_value) payment_num, to_char(apd.accounting_date, 'dd-MON-yyyy') pay_accounting_date, NULL check_number, pov.segment1, pov.vendor_name, pov.vendor_type_lookup_code, apd.po_distribution_id, api.exchange_rate_type, api.org_id, api.batch_id, api.exchange_date, api.invoice_id, apd.accounting_date, JA_JAINAPCR_XMLP_PKG.cf_addrformula(api.invoice_num) CF_addr, JA_JAINAPCR_XMLP_PKG.cf_po_noformula(apd.po_distribution_id) CF_po_no, JA_JAINAPCR_XMLP_PKG.cf_orgnformula(apd.po_distribution_id) CF_orgn, JA_JAINAPCR_XMLP_PKG.cf_locnformula(apd.po_distribution_id) CF_locn, JA_JAINAPCR_XMLP_PKG.cf_dr_reformula(api.org_id, api.invoice_type_lookup_code, 0, api.exchange_rate_type, api.invoice_currency_code, api.exchange_date, api.exchange_rate, Decode ( api.invoice_type_lookup_code , 'CREDIT' , abs ( z.amt_val ) - abs ( nvl ( api.discount_amount_taken , 0 ) ) , 0 )) CF_dr_re, JA_JAINAPCR_XMLP_PKG.cf_cr_reformula(api.org_id, api.invoice_type_lookup_code, 0, api.exchange_rate_type, api.invoice_currency_code, api.exchange_date, api.exchange_rate, Decode ( api.invoice_type_lookup_code , 'CREDIT' , 0 , z.amt_val - nvl ( api.discount_amount_taken , 0 ) )) CF_cr_re, JA_JAINAPCR_XMLP_PKG.cf_batchformula(api.batch_id) CF_batch, JA_JAINAPCR_XMLP_PKG.cf_accountformula(apd.dist_code_combination_id) CF_account FROM ap_invoices_all api, ap_invoice_lines_all apil, ap_invoice_distributions_all apd, po_vendors pov, po_vendor_sites_all povs, (SELECT NVL(SUM(apd.amount),0) amt_val, api.invoice_id FROM ap_invoices_all api, ap_invoice_lines_all apil, ap_invoice_distributions_all apd, po_vendors pov, po_vendor_sites_all povs WHERE api.invoice_id = apd.invoice_id and apil.invoice_id = api.invoice_id and apil.line_number = apd.invoice_line_number AND api.vendor_id = pov.vendor_id AND api.vendor_id = NVL(:p_vendor_id, api.vendor_id) AND pov.segment1 = NVL(:p_vendor_no, pov.segment1) AND pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) AND api.invoice_type_lookup_code <> 'PREPAYMENT' AND (api.org_id = :p_org_id OR api.org_id IS NULL) AND api.vendor_site_id = povs.vendor_site_id AND api.vendor_site_id = NVL(:p_vendor_site_id, api.vendor_site_id) AND apd.accounting_date BETWEEN :p_from_date AND :p_to_date AND apd.match_status_flag='A' AND apil.line_type_lookup_code <> 'PREPAY' GROUP BY api.invoice_id) z WHERE api.invoice_id = z.invoice_id and api.invoice_id = apd.invoice_id and apil.invoice_id = api.invoice_id and apil.line_number = apd.invoice_line_number AND apd.rowid = ( select rowid from ap_invoice_distributions_all where rownum=1 and invoice_id=apd.invoice_id AND accounting_date BETWEEN :p_from_date AND :p_to_date AND match_status_flag='A') AND api.vendor_id = pov.vendor_id AND api.vendor_id = NVL(:p_vendor_id, api.vendor_id) AND pov.segment1 = NVL(:p_vendor_no, pov.segment1) AND pov.vendor_type_lookup_code = NVL(:p_vendor_type_lookup_code, pov.vendor_type_lookup_code) AND api.invoice_type_lookup_code <> 'PREPAYMENT' AND apd.match_status_flag = 'A' AND (api.org_id = :p_org_id OR api.org_id IS NULL) AND api.vendor_site_id = povs.vendor_site_id AND api.vendor_site_id = NVL(:p_vendor_site_id, api.vendor_site_id) AND ( (api.invoice_type_lookup_code <> 'DEBIT') or ( (api.invoice_type_lookup_code = 'DEBIT') and ( not exists (Select '1' from ap_invoice_payments_all app, ap_checks_all apc where app.check_id = apc.check_id and app.invoice_id = api.invoice_id and apc.payment_type_flag = 'R' ) ) ) ) and pov.segment1=:segment2 and povs.vendor_site_code=:vendor_site_code2 UNION all SELECT povs.vendor_site_code, api.invoice_type_lookup_code, api.invoice_num, api.invoice_date, apd.description description, app.accts_pay_code_combination_id ccid, api.payment_currency_code, apc.exchange_rate , Decode(api.invoice_type_lookup_code,'CREDIT',decode(status_lookup_code,'VOIDED',0,0),app.amount) dr_val, Decode(api.invoice_type_lookup_code,'CREDIT',decode(status_lookup_code,'VOIDED',app.amount,abs(app.amount)),0) cr_val, 0 acct_dr, 0 acct_cr, DECODE(api.payment_status_flag, 'Y', to_char(apc.doc_sequence_value), 'P', to_char(apc.doc_sequence_value), to_char(apc.doc_sequence_value), 'N', NULL) payment_num, DECODE(api.payment_status_flag, 'Y', to_char(app.accounting_date, 'dd-MON-yyyy'), 'P', to_char(app.accounting_date, 'dd-MON-yyyy')) pay_accounting_date, DECODE(api.payment_status_flag, 'Y', to_char(apc.check_number), 'P', to_char(apc.check_number)) check_number, pov.segment1, pov.vendor_name, pov.vendor_type_lookup_code, apd.po_distribution_id, apc.exchange_rate_type, api.org_id, api.batch_id, apc.exchange_date, api.invoice_id, app.accounting_date, JA_JAINAPCR_XMLP_PKG.cf_addrformula(api.invoice_num) CF_addr, JA_JAINAPCR_XMLP_PKG.cf_po_noformula(apd.po_distribution_id) CF_po_no, JA_JAINAPCR_XMLP_PKG.cf_orgnformula(apd.po_distribution_id) CF_orgn, JA_JAINAPCR_XMLP_PKG.cf_locnformula(apd.po_distribution_id) CF_locn, JA_JAINAPCR_XMLP_PKG.cf_dr_reformula(api.org_id, api.invoice_type_lookup_code, 0, apc.exchange_rate_type, api.payment_currency_code, apc.exchange_date, apc.exchange_rate, Decode(api.invoice_type_lookup_code,'CREDIT',decode(status_lookup_code,'VOIDED',0,0),app.amount)) CF_dr_re, JA_JAINAPCR_XMLP_PKG.cf_cr_reformula(api.org_id, api.invoice_type_lookup_code, 0, apc.exchange_rate_type, api.payment_currency_code, apc.exchange_date, apc.exchange_rate,Decode(api.invoice_type_lookup_code,'CREDIT',decode(status_lookup_code,'VOIDED',app.amount,abs(app.amount)),0)) CF_cr_re, JA_JAINAPCR_XMLP_PKG.cf_batchformula(api.batch_id) CF_batch, JA_JAINAPCR_XMLP_PKG.cf_accountformula(app.accts_pay_code_combination_id) CF_account FROM ap_invoices_all api, ap_invoice_lines_all apil, ap_invoice_distributions_all apd, po_vendors pov, ap_invoice_payments_all app, ap_checks_all apc, po_vendor_sites_all povs WHERE api.invoice_id = apd.invoice_id and apil.invoice_id = api.invoice_id and apil.line_number = apd.invoice_line_number AND apd.rowid = (select rowid from ap_invoice_distributions_all where rownum=1 and invoice_id=apd.invoice_id and match_status_flag='A') AND api.vendor_id = pov.vendor_id AND app.invoice_id = api.invoice_id AND app.check_id = apc.check_id AND api.vendor_id = NVL(:p_vendor_id, api.vendor_id) AND pov.segment1 = NVL(:p_vendor_no, pov.segment1) AND app.accounting_date BETWEEN :p_from_date AND :p_to_date AND (api.org_id = :p_org_id OR api.org_id IS NULL) AND apc.status_lookup_code IN ('CLEARED', 'NEGOTIABLE','VOIDED','RECONCILED UNACCOUNTED', 'RECONCILED', 'CLEARED BUT UNACCOUNTED') AND apd.match_status_flag='A' AND api.vendor_site_id = povs.vendor_site_id AND api.vendor_site_id = NVL(:p_vendor_site_id, api.vendor_site_id) and pov.segment1=:segment2 and povs.vendor_site_code=:vendor_site_code2 UNION ALL select a.vendor_site_code, 'GAIN/LOSS' invoice_type_lookup_code, api.invoice_num, xah.ACCOUNTING_DATE INVOICE_DATE , null description, a.accts_pay_code_combination_id ccid, xal.currency_code invoice_currency_code, xal.currency_conversion_rate rate , 0 dr_val, 0 cr_val, DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr, DECODE(xal.accounting_class_code,'LOSS',accounted_dr,0) acct_cr, null payment_num, to_char(xah.accounting_date, 'dd-MON-yyyy') pay_accounting_date, null check_number, b.segment1 , b.vendor_name, b.vendor_type_lookup_code, 0 distribution_id, api.exchange_rate_type, api.org_id, api.batch_id, sysdate exchange_date, api.invoice_id, xah.accounting_date, JA_JAINAPCR_XMLP_PKG.cf_addrformula(api.invoice_num) CF_addr, JA_JAINAPCR_XMLP_PKG.cf_po_noformula(0) CF_po_no, JA_JAINAPCR_XMLP_PKG.cf_orgnformula(0) CF_orgn, JA_JAINAPCR_XMLP_PKG.cf_locnformula(0) CF_locn, JA_JAINAPCR_XMLP_PKG.cf_dr_reformula(api.org_id, 'GAIN/LOSS', 0, api.exchange_rate_type, xal.currency_code, sysdate, xal.currency_conversion_rate, 0) CF_dr_re, JA_JAINAPCR_XMLP_PKG.cf_cr_reformula(api.org_id, 'GAIN/LOSS', 0, api.exchange_rate_type, xal.currency_code, sysdate, xal.currency_conversion_rate, 0) CF_cr_re, JA_JAINAPCR_XMLP_PKG.cf_batchformula(api.batch_id) CF_batch, JA_JAINAPCR_XMLP_PKG.cf_accountformula(a.accts_pay_code_combination_id) CF_account from po_vendor_sites_all a, po_vendors b, xla_ae_lines xal, xla_ae_headers xah, xla_transaction_entities xte, ap_invoices_all api where a.vendor_id = b.vendor_id AND a.vendor_id = api.vendor_id AND a.vendor_site_id = api.vendor_site_id AND b.vendor_id = api.vendor_id AND xal.application_id = 200 AND xal.ae_header_id = xah.ae_header_id AND xal.accounting_class_code in ('GAIN','LOSS') AND xah.application_id = 200 AND xah.entity_id = xte.entity_id AND xte.application_id = 200 AND xte.entity_code = 'AP_INVOICES' AND xte.source_id_int_1 = api.invoice_id AND xah.ACCOUNTING_DATE BETWEEN :p_from_date AND :p_to_date AND b.vendor_id = NVL(:p_vendor_id,b.vendor_id) AND (api.org_id = :p_org_id OR api.org_id IS NULL) AND b.segment1 = NVL(:p_vendor_no, b.segment1) AND api.vendor_id = NVL(:p_vendor_id, api.vendor_id) and b.segment1=:segment2 and a.vendor_site_code=:vendor_site_code2 union all select a.vendor_site_code, 'GAIN/LOSS' invoice_type_lookup_code, api.invoice_num, xah.ACCOUNTING_DATE INVOICE_DATE , null description, a.accts_pay_code_combination_id ccid, xal.currency_code invoice_currency_code, xal.currency_conversion_rate rate , 0 dr_val, 0 cr_val, DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr, DECODE(xal.accounting_class_code,'LOSS',accounted_dr,0) acct_cr, null payment_num, to_char(xah.accounting_date, 'dd-MON-yyyy') pay_accounting_date, null check_number, b.segment1 , b.vendor_name, b.vendor_type_lookup_code, apd.po_distribution_id po_distribution_id, api.exchange_rate_type, api.org_id, api.batch_id, sysdate exchange_date, api.invoice_id, xah.accounting_date, JA_JAINAPCR_XMLP_PKG.cf_addrformula(api.invoice_num) CF_addr, JA_JAINAPCR_XMLP_PKG.cf_po_noformula(apd.po_distribution_id) CF_po_no, JA_JAINAPCR_XMLP_PKG.cf_orgnformula(apd.po_distribution_id) CF_orgn, JA_JAINAPCR_XMLP_PKG.cf_locnformula(apd.po_distribution_id) CF_locn, JA_JAINAPCR_XMLP_PKG.cf_dr_reformula(api.org_id,'GAIN/LOSS', 0, api.exchange_rate_type, xal.currency_code, sysdate, xal.currency_conversion_rate, 0) CF_dr_re, JA_JAINAPCR_XMLP_PKG.cf_cr_reformula(api.org_id, 'GAIN/LOSS', 0, api.exchange_rate_type, xal.currency_code, sysdate, xal.currency_conversion_rate, 0) CF_cr_re, JA_JAINAPCR_XMLP_PKG.cf_batchformula(api.batch_id) CF_batch, JA_JAINAPCR_XMLP_PKG.cf_accountformula(a.accts_pay_code_combination_id) CF_account from po_vendor_sites_all a, po_vendors b, xla_ae_lines xal, xla_ae_headers xah, xla_transaction_entities xte, ap_invoices_all api, ap_invoice_lines_all apil, ap_invoice_distributions_all apd where api |