JA India - Creditors Ledger- Not Supported: Reserved For Future Use

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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.invoice_id = apd.invoice_id and 	      
       apil.invoice_id = api.invoice_id  AND          
       apil.line_number = apd.invoice_line_number AND 
       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 
       apd.accounting_event_id = xah.event_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,
       to_char(ac.check_number) 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, 
       ap_checks_all ac , 
       ap_invoice_payments_all app  
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
       api.invoice_id =  app.invoice_id AND
       app.check_id = ac.check_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_PAYMENTS' AND 
       xte.source_id_int_1 = ac.check_id AND  
       xah.event_id = app.accounting_event_id AND 
      ac.status_lookup_code IN ('CLEARED', 'NEGOTIABLE','VOIDED','RECONCILED UNACCOUNTED', 'RECONCILED', 'CLEARED BUT UNACCOUNTED')  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', xal.accounted_cr,0) acct_dr,
       DECODE(xal.accounting_class_code,'LOSS',xal.accounted_dr,0)  acct_cr,
       null payment_num,
       to_char(xah.accounting_date, 'dd-MON-yyyy')  pay_accounting_date,
       to_char(ac.check_number) 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, 
       ap_checks_all ac , 
       ap_invoice_payments_all app  
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
       api.invoice_id =  app.invoice_id AND
       app.check_id = ac.check_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_PAYMENTS' AND 
       xte.source_id_int_1 = ac.check_id AND  
       xah.event_id = app.accounting_event_id AND 
       ac.status_lookup_code IN ('CLEARED', 'NEGOTIABLE','VOIDED','RECONCILED UNACCOUNTED', 'RECONCILED', 'CLEARED BUT UNACCOUNTED')  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 
DISTINCT a.vendor_site_code,
        NULL invoice_type_lookup_code,
        null invoice_num,
        SYSDATE INVOICE_DATE ,
        null description,
        a.accts_pay_code_combination_id ccid,
        null invoice_currency_code,
        0  rate ,
        0 dr_val,
        0  cr_val,
        0 acct_dr,
        0 acct_cr,
        null payment_num,
        null  pay_accounting_date,
        null check_number,
        b.segment1 ,
        b.vendor_name,
        null,
        0 distribution_id,
        null,
        a.org_id,
        0 batch_id,
        sysdate exchange_date,
        0 invoice_id,
        sysdate accounting_date, 
	JA_JAINAPCR_XMLP_PKG.cf_addrformula(null) 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(a.org_id, null, 0, null, null, sysdate, 0, 0) CF_dr_re, 
	JA_JAINAPCR_XMLP_PKG.cf_cr_reformula(a.org_id, null, 0, null, null, sysdate, 0, null) CF_cr_re, 
	JA_JAINAPCR_XMLP_PKG.cf_batchformula(0) 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, 
          ap_invoices_all c, 
	  ap_invoice_lines_all apil,      
          AP_INVOICE_DISTRIBUTIONS_ALL D 
where    apil.invoice_id = c.invoice_id  AND           
         apil.line_number = d.invoice_line_number AND  
         a.vendor_id = b.vendor_id and
         a.vendor_id = c.vendor_id
         AND A.VENDOR_SITE_ID = C.VENDOR_SITE_ID
         AND C.INVOICE_ID = D.INVOICE_ID 
         AND D.ACCOUNTING_DATE NOT BETWEEN :p_from_date AND :p_to_date
         AND C.vendor_id = NVL(:p_vendor_id,c.vendor_id)
         and   A.VENDOR_SITE_ID not in (select VENDOR_SITE_ID 
	                                  from ap_invoices_all A ,
					       ap_invoice_distributions_all d
			                 where  VENDOR_ID  =  NVL(:p_vendor_id,vendor_id) 
					   and  A.INVOICE_ID = D.INVOICE_ID 
					   AND accounting_date between :p_from_date AND :p_to_date) 
 and b.segment1=:segment2 
 and a.vendor_site_code=:vendor_site_code2
ORDER BY 17 ASC,18 ASC,16 ASC,21 ASC,1 ASC , 24 , 25
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Vendor Name 
 
 LOV Oracle
Vendor Number 
 
 LOV Oracle
Vendor Type 
 
 LOV Oracle
From Date 
 
 Date
To Date 
 
 Date
Vendor Site ID 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: