SELECT aif.invoice_id
,ailf.invoice_line_id
,aif.invoice_num
,air.reject_lookup_code
,aif.invoice_amount
,ailf.amount line_amount
,ailf.line_number
,aif.source
,ailf.accounting_date
,aif.gl_date
,aif.invoice_date
,aif.creation_date
,(SELECT vendor_name
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_name
,(SELECT end_date_active
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_end_date_active
,(SELECT last_update_date
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_last_update_date
,(SELECT segment1
FROM po_headers_all
WHERE po_header_id = ailf.po_header_id) po_number
,(SELECT name
FROM hr_operating_units
WHERE organization_id = aif.org_id) operating_unit
FROM ap_invoices_interface aif
,ap_interface_rejections air
,ap_invoice_lines_interface ailf
WHERE aif.invoice_id = ailf.invoice_id
AND ailf.invoice_line_id = air.parent_id
-- AND aif.group_id =v_group_id
AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
--AND aif.source = 'ERS'
AND aif.status = 'REJECTED'
UNION
SELECT aif.invoice_id
,ailf.invoice_line_id
,aif.invoice_num
,air.reject_lookup_code
,aif.invoice_amount
,ailf.amount line_amount
,ailf.line_number
,aif.source
,ailf.accounting_date
,aif.gl_date
,aif.invoice_date
,aif.creation_date
,(SELECT vendor_name
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_name
,(SELECT end_date_active
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_end_date_active
,(SELECT last_update_date
FROM ap_suppliers
WHERE vendor_id = aif.vendor_id) vendor_last_update_date
,(SELECT segment1
FROM po_headers_all
WHERE po_header_id = ailf.po_header_id) po_number
,(SELECT name
FROM hr_operating_units
WHERE organization_id = aif.org_id) operating_unit
FROM ap_invoices_interface aif
,ap_interface_rejections air
,ap_invoice_lines_interface ailf
WHERE aif.invoice_id = ailf.invoice_id
AND aif.invoice_id = air.parent_id
-- AND aif.group_id =v_group_id
AND air.parent_table = 'AP_INVOICES_INTERFACE'
--AND aif.source = 'ERS'
AND aif.status = 'REJECTED'
No comments:
Post a Comment