Overview: This query is use to extract fixed assets invoices which is cancelled in Oracle Application R12.
SELECT hou.name Org_Name,
xep.name Entity,
asp.segment1 Vendor_number,
asp.vendor_name,
assa.vendor_site_code,
aia.doc_sequence_value voucher_number,
aia.invoice_id Invoice_id,
aia.invoice_num Invoice_Number,
aia.payment_status_flag,
aia.invoice_date Invoice_date,
— aia.PAY_GROUP_LOOKUP_CODE,
aia.invoice_currency_code Invoice_Currency,
aia.invoice_Amount Invoice_Amount,
poh.segment1 PO_number,
rsh.receipt_num Receipt_Number,
fu.user_name Requestor_name,
aia.attribute8 SR_number,
aia.attribute9 miscellaneous_Info,
aia.Attribute3 Lease_Sch_No,
aia.Attribute5 Lease_Amt,
aia.Attribute6 Lease_vendor_Code,
aia.Attribute7 Lease_vendor_name,
aia.Attribute11 Lease_vendor_Site,
Case when trunc(aia.cancelled_date) <=SYsDATE
then ‘Y’
else
‘N’ END Cancelled_flag
FROM hr_operating_units hou,
xle_entity_profiles xep,
ap_invoices_All aia,
ap_suppliers asp,
ap_supplier_sites_all assa,
(SELECT DISTINCT invoice_id, po_header_id, rcv_transaction_id
FROM ap_invoice_lines_all
WHERE line_type_lookup_code = ‘ITEM’) aila,
po_headers_all poh,
rcv_shipment_headers rsh,
rcv_transactions rct,
fnd_user fu
WHERE aia.org_id = hou.organization_id
AND hou.default_legal_context_id = xep.legal_entity_id
AND fu.user_id = aia.created_by
AND aia.vendor_id = asp.vendor_id
AND asp.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND aia.invoice_id = aila.invoice_id
AND aila.po_header_id = poh.po_header_id(+)
AND aila.rcv_transaction_id = rct.transaction_id(+)
AND rct.shipment_header_id = rsh.shipment_header_id(+)
AND aia.org_id = assa.org_id
