Introduction:
The will displays all the Paid invoices details .
Select
“Operating Unit”
,”Invoice Number”
,”Invoice Date”
,”Supplier Number”
,”Supplier Name”
,”Supplier Site”
,”Supplier Pay Terms”
,”Supplier Payment Method”
,”Supplier Pay Group”
,”Invoice Due Date”
,”Terms Base Date”
,”Invoice Payment Date”
,”Invoice Payment Status”
,”Invoice – GL Posting Date”
,sum(“Invoice Amount”) “Inv Amount”
,”Transaction Currency”
,sum(“Invoice Amount(USD)”) “Inv Amount Base”
,”Invoice Payment Method”
,”Invoice Payment Document”
,”Invoice Line Type”
,”Country”
,”GL Code Combination”
,”GL Code Description”
,”Location”
,”PO Number”
,”PO Revision Number”
,”PO Date”
,”PO Approval Date”
,”PO Transmission Date”
,”PO Line Amount”
,”PO Transaction Currency”
,”Amount Received”
,”Amount Billed”
,”PO Supplier Item Number”
,”Item Description”
,”Requisition Number”
,”Requisition Creation Date”
,”Requisition Approved Date”
,”Catalog Type”
,”Item ID”
From
(Select
hrou.name “Operating Unit”,
api.invoice_num “Invoice Number”,
api.invoice_date “Invoice Date”,
aps.segment1 “Supplier Number”,
aps.vendor_name “Supplier Name”,
site.vendor_site_code “Supplier Site”,
(Select t.name From ap_terms_tl t
Where t.term_id = api.terms_id and t.language = ‘US’) “Supplier Pay Terms”,
api.terms_date “Terms Base Date”,
api.payment_method_code “Supplier Payment Method”,
api.pay_group_lookup_code “Supplier Pay Group”,
(Select ps.due_date From ap_payment_schedules_all ps
Where api.invoice_id = ps.invoice_id and rownum < 2) “Invoice Due Date”,
pay.accounting_date “Invoice Payment Date”,
(SELECT DISTINCT lv.MEANING “Invoice Payment Status” FROM FND_LOOKUP_VALUES LV
WHERE LV.LANGUAGE = userenv(‘LANG’)
AND LV.VIEW_APPLICATION_ID = 200
AND LV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE, LV.VIEW_APPLICATION_ID)
AND LV.LOOKUP_TYPE (+) = ‘INVOICE PAYMENT STATUS’
AND LV.LOOKUP_CODE (+) = API.PAYMENT_STATUS_FLAG) “Invoice Payment Status”,
api.gl_date “Invoice – GL Posting Date”,
aid.amount “Invoice Amount”,
api.invoice_currency_code “Transaction Currency”,
nvl(aid.base_amount,aid.amount) “Invoice Amount(USD)”,
api.payment_method_code “Invoice Payment Method”,
(Select apc.check_number From ap_invoice_payments_all pay, ap_checks_all apc
Where api.invoice_id = pay.invoice_id(+) and api.org_id = pay.org_id(+)
and pay.check_id = apc.check_id and rownum <2) “Invoice Payment Document”,
aid.line_type_lookup_code “Invoice Line Type”,
site.COUNTRY “Country”,
cc.concatenated_segments “GL Code Combination”
,cc.segment2 “Location”
,SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,1,cc.SEGMENT1),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,2,cc.SEGMENT2),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,3,cc.SEGMENT3),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,4,cc.SEGMENT4),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,5,cc.SEGMENT5),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,6,cc.SEGMENT6),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,7,cc.SEGMENT7),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,8,cc.SEGMENT8),1,40) ||’.’||
SUBSTR (APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,9,cc.SEGMENT9),1,40) “GL Code Description”
,(Select poh.segment1 From po_headers_all poh,po_distributions_all pod
Where aid.org_id = poh.org_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id) “PO Number”
,(Select poh.revision_num From po_headers_all poh,po_distributions_all pod
Where aid.org_id = poh.org_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id) “PO Revision Number”
,(Select poh.creation_date From po_headers_all poh,po_distributions_all pod
Where aid.org_id = poh.org_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id) “PO Date”
,(Select poh.approved_date From po_headers_all poh,po_distributions_all pod
Where aid.org_id = poh.org_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id) “PO Approval Date”
,(Select max(pah.action_date)
From po_action_history pah,po_headers_all poh,po_distributions_all pod
Where pah.object_id = poh.po_header_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id
and pah.object_type_code in ( ‘PO’,’RELEASE’) )”PO Transmission Date”
,(Select pol.unit_price * nvl(pol.quantity,1)
From po_lines_all pol,po_distributions_all pod
Where aid.org_id = pol.org_id and aid.po_distribution_id = pod.po_distribution_id and pol.org_id = pod.org_id
and pod.po_line_id = pol.po_line_id) “PO Line Amount”
,(Select poh.currency_code From po_headers_all poh,po_distributions_all pod
Where aid.org_id = poh.org_id and poh.po_header_id = pod.po_header_id and aid.po_distribution_id = pod.po_distribution_id) “PO Transaction Currency”
,(Select pod.quantity_delivered * pol.unit_price
From po_lines_all pol,po_distributions_all pod
Where aid.org_id = pol.org_id and aid.po_distribution_id = pod.po_distribution_id and pol.org_id = pod.org_id
and pod.po_line_id = pol.po_line_id) “Amount Received”
,(Select pod.amount_billed
From po_distributions_all pod
Where aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id) “Amount Billed”
,(Select porl.suggested_vendor_product_code From po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_distributions_all pod
Where aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id
AND POD.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID (+)
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID (+) ) “PO Supplier Item Number”
,(Select pol.item_description
From po_lines_all pol,po_distributions_all pod
Where aid.org_id = pol.org_id and aid.po_distribution_id = pod.po_distribution_id and pol.org_id = pod.org_id
and pod.po_line_id = pol.po_line_id) “Item Description”
,(Select pol.item_id
From po_lines_all pol,po_distributions_all pod
Where 1=1 –aid.org_id = pol.org_id
and aid.po_distribution_id = pod.po_distribution_id(+)
and pol.org_id = pod.org_id
and pod.po_line_id = pol.po_line_id) “Item ID”
,(Select porh.segment1 From po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_distributions_all pod
Where aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id
AND POD.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID (+)
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID (+) ) “Requisition Number”
,(Select porh.creation_date From po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_distributions_all pod
Where aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id
AND POD.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID (+)
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID (+) ) “Requisition Creation Date”
,(Select max(pah.action_date) From po_action_history pah,po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_distributions_all pod
Where pah.object_id = porh.requisition_header_id
and pah.action_code = ‘APPROVE’
and pah.object_type_code = ‘REQUISITION’
AND aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id
AND POD.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID (+)
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID (+) ) “Requisition Approved Date”
,(Select porl.catalog_type From po_requisition_headers_all porh,po_requisition_lines_all porl,po_req_distributions_all pord,po_distributions_all pod
Where aid.org_id = pod.org_id and aid.po_distribution_id = pod.po_distribution_id
AND POD.REQ_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID (+)
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID(+)
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID (+) ) “Catalog Type”
From
ap_invoices_all api
,ap_invoice_distributions_all aid
,ap_suppliers aps
,ap_supplier_sites_all site
,gl_code_combinations_kfv cc
,hr_all_organization_units hrou
,ap_invoice_payments_all pay
Where 1=1
and api.invoice_id = aid.invoice_id
and api.org_id = aid.org_id
and api.vendor_id = aps.vendor_id
and api.vendor_site_id = site.vendor_site_id
and api.org_id = site.org_id
and aid.dist_code_combination_id = cc.code_combination_id(+)
and hrou.organization_id = api.org_id
and api.invoice_type_lookup_code <> ‘EXPENSE REPORT’
and api.invoice_id = pay.invoice_id(+)
and api.org_id = pay.org_id(+)
)
Group by
“Operating Unit”
,”Invoice Number”
,”Invoice Date”
,”Supplier Number”
,”Supplier Name”
,”Supplier Site”
,”Supplier Pay Terms”
,”Supplier Payment Method”
,”Supplier Pay Group”
,”Invoice Due Date”
,”Terms Base Date”
,”Invoice Payment Date”
,”Invoice Payment Status”
,”Invoice – GL Posting Date”
,”Transaction Currency”
,”Invoice Payment Method”
,”Invoice Payment Document”
,”Invoice Line Type”
,”Country”
,”GL Code Combination”
,”GL Code Description”
,”Location”
,”PO Number”
,”PO Revision Number”
,”PO Date”
,”PO Approval Date”
,”PO Transmission Date”
,”PO Line Amount”
,”PO Transaction Currency”
,”Amount Received”
,”Amount Billed”
,”PO Supplier Item Number”
,”Item Description”
,”Requisition Number”
,”Requisition Creation Date”
,”Requisition Approved Date”
,”Item ID”
,”Catalog Type”
Summary
This report will use to identify the Paid invoices for all the OU’s
