AP Invoice Interface rejected Records

-- ---------------------------------------------------------------------------
-- Purpose : AP Invoice Interface rejected Records for Analysis
-- Date      : 28.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


SELECT pv.vendor_name,
aii.INVOICE_ID,
aii.SOURCE,
aii.invoice_num,
(select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all
where org_id = aii.ORG_ID
and invoice_num = aii.INVOICE_NUM) Inv_in_Base,
aii.CREATION_DATE,
aii.invoice_date,
aii.GL_DATE,
ail.LINE_NUMBER,
aii.REQUEST_ID,
aii.invoice_currency_code,
nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason,
(select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All
where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus
from ap_invoice_lines_interface ail,
AP_INTERFACE_REJECTIONS airh,
AP_INTERFACE_REJECTIONS airl,
ap_invoices_interface aii,
po_vendors pv
where aii.INVOICE_ID = ail.INVOICE_ID
and airh.PARENT_ID = aii.INVOICE_ID
and ail.INVOICE_LINE_ID = airl.PARENT_ID
and aii.VENDOR_ID = pv.VENDOR_ID
order by 2,6;

-- ---------------------------------------------------------------------------