Introduction:
This will displays all the unpaid invoices details
select
hrou.name Operating_Unit,
api.invoice_num Invoice_Number,
decode(aid.MATCH_STATUS_FLAG, ‘A’,’Validated’,
null, ‘Never Validated’,
‘N’, ‘Never Validated’,
‘T’,’Validated’,
‘S’,’Needs Revalidation’,
aid.MATCH_STATUS_FLAG) Invoice_Status,
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 to_char(max(DUE_DATE),’DD-MON-RRRR’) From ap_payment_schedules_all ps
Where api.invoice_id = ps.invoice_id ) Invoice_Due_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) Functional_Amount,
(Select sum( nvl(pay.payment_base_amount, pay.amount) ) From ap_invoice_payments_all pay
Where api.invoice_id = pay.invoice_id and api.org_id = pay.org_id
and aid.INVOICE_DISTRIBUTION_ID = (select min(aid2.INVOICE_DISTRIBUTION_ID)
from ap_invoice_distributions_all aid2
where aid2.invoice_id = api.invoice_id ) ) Invoice_Amount_Paid,
api.payment_method_code Invoice_Payment_Method,
aid.line_type_lookup_code Invoice_Line_Type,
site.COUNTRY Country
,cc.concatenated_segments GL_Code_Combination
,cc.segment2 Location
,cc.segment5 GL_Account
,APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (cc.CHART_OF_ACCOUNTS_ID,5,cc.SEGMENT5) GL_Account_Description
,cc.gl_account_type GL_Account_Type_Code
,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
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.CANCELLED_BY is null
–and api.invoice_num=’4567′
and api.invoice_type_lookup_code <> ‘EXPENSE REPORT’
and api.PAYMENT_STATUS_FLAG in (‘N’,’P’) — Not Paid, Partially Paid Ignores Y – Fully Paid –8305
and exists (select 1 from apps.ap_payment_schedules_all apsa where apsa.invoice_id = api.invoice_id
)
Summary:
This report will use to business to identity the unpaid invoices details
Know more about post.
Doyen.ebiz@gmail.com
