JA India - Statement of Accounts for Supplier - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Statement of Accounts for Supplier (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTAC_XML
DB package: JA_JAINSTAC_XMLP_PKG
SELECT api.voucher_num,
               api.invoice_id,
               api.invoice_num,
               api.invoice_amount,
               api.amount_paid,
               api.exchange_rate,
               api.invoice_date,
               api.invoice_currency_code,
               api.invoice_type_lookup_code,
               api.description, 
               pov.vendor_name, 
               api.vendor_id,
               api.payment_status_flag,
               api.org_id, 
	JA_JAINSTAC_XMLP_PKG.cf_age2formula(api.vendor_id) CF_age2, 
	JA_JAINSTAC_XMLP_PKG.cf_comp_nameformula(api.org_id) CF_comp_name, 
	JA_JAINSTAC_XMLP_PKG.cf_comp_addrformula(api.org_id) CF_comp_addr, 
	JA_JAINSTAC_XMLP_PKG.cf_amount1formula(api.invoice_type_lookup_code, api.invoice_id, api.invoice_amount, api.amount_paid) CF_amount1, 
	JA_JAINSTAC_XMLP_PKG.cf_inr_amount1formula(api.invoice_type_lookup_code, api.exchange_rate, api.invoice_id, api.invoice_amount, api.amount_paid) CF_inr_amount1, 
	JA_JAINSTAC_XMLP_PKG.cf_remarksformula(api.payment_status_flag) CF_remarks
FROM ap_invoices_all api,   
           po_vendors pov
WHERE api.vendor_id = pov.vendor_id
AND       api.cancelled_date IS NULL 
AND       pov.vendor_id = NVL(:p_vendor_id, pov.vendor_id)
AND       pov.segment1  = NVL(:p_vendor_no, pov.segment1)
AND       pov.vendor_type_lookup_code = NVL(:p_vendor_type, pov.vendor_type_lookup_code)
AND       TRUNC(api.invoice_date) <= TRUNC(:p_as_on_date)
AND   (    
               ( api.invoice_type_lookup_code <> 'PREPAYMENT'  AND  api.payment_status_flag = 'P') 
         OR ( 
                 (api.invoice_type_lookup_code <> 'PREPAYMENT' )  AND  (api.payment_status_flag = 'N') AND
                 (api.invoice_id IN 
                     (SELECT apd.invoice_id FROM ap_invoice_distributions_all apd
                         WHERE match_status_flag='A' 
                         AND apd.invoice_id = api.invoice_id 
                         AND rownum < 2)
                 )
               )
         OR ( api.invoice_type_lookup_code = 'PREPAYMENT' AND api.payment_status_flag='P' ) 
         OR ( api.invoice_type_lookup_code = 'PREPAYMENT' AND api.payment_status_flag='Y' AND 
                 api.invoice_id in (SELECT invoice_id  FROM ap_invoice_distributions_all 
	       GROUP BY invoice_id HAVING invoice_id = api.invoice_id  and 
	 SUM(NVL(prepay_amount_remaining,1)) <> 0  )
               )
          )
AND       api.org_id = NVL(:p_org_id, api.org_id)
ORDER BY  api.invoice_date
Parameter Name SQL text Validation
Org ID
 
Number
No of Intervals
 
Number
Ageing Interval Days
 
Number
As On Date
 
Date
Vendor Name
 
LOV Oracle