SELECT
rsh.receipt_num,rsh.creation_date GRN_creation_date,
pha.segment1 po_number, pha.creation_date PO_creation_date,
rt.AMOUNT_BILLED,
aia.invoice_num,
aia.invoice_currency_code,
aia.invoice_date,
aia.gl_date,
aia.invoice_amount,
APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
(
AIA.INVOICE_ID
,AIA.INVOICE_AMOUNT
,AIA.PAYMENT_STATUS_FLAG
,AIA.INVOICE_TYPE_LOOKUP_CODE
) Approval_Status
,DECODE(apps.AP_INVOICES_PKG.GET_POSTING_STATUS( aia.invoice_id ),
'P', 'Partial',
'N', 'Unaccounted',
'Y', 'Accounted')Account_Status
,aps.vendor_name
,assa.vendor_site_code
,(Select gcc.CONCATENATED_SEGMENTS from rcv_receiving_sub_ledger rrs, GL_CODE_COMBINATIONs_kfv gcc
Where rrs.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND rrs.RCV_TRANSACTION_ID = Rt.TRANSACTION_ID
AND rrs.ACCOUNTING_LINE_TYPE ='Receiving Inspection' ) RECEIVING_ACC --Accrual,Charge
,(Select gcc.CONCATENATED_SEGMENTS from rcv_receiving_sub_ledger rrs, GL_CODE_COMBINATIONs_kfv gcc
Where rrs.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND rrs.RCV_TRANSACTION_ID = Rt.TRANSACTION_ID
AND rrs.ACCOUNTING_LINE_TYPE ='Accrual' ) Accrual_ACC --Accrual,Charge
FROM
ap_invoices_all aia,
ap_invoice_lines_all ail,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_distributions_all pda,
po_headers_all pha,
ap_suppliers aps,
ap_supplier_sites_all assa
--fnd_user fu
WHERE 1=1
AND ail.invoice_id = aia.invoice_id
AND ail.rcv_transaction_id = rt.transaction_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id
AND pda.po_header_id = pha.po_header_id
AND pda.po_distribution_id = rt.po_distribution_id
AND aia.vendor_id = aps.vendor_id
AND aps.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
--AND aia.created_by = fu.user_id
ORDER BY rsh.creation_date,
aps.vendor_name
/