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

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