PO Headers

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
PO headers or releases with corresponding invoices and their payment status.
Example: To show all POs or releases which have an invoice but no receiving TRX yet, set following parameters:
Invoice exists=Yes
Receiving TRX exists=No
Run PO Headers and other Oracle EBS reports with Blitz Report™ on our demo environment
select distinct
haouv.name operating_unit,
pha.segment1 po_number,
pha.revision_num revision,
pha.release_num release,
pdtav.type_name type,
aps.vendor_name supplier_name,
ppx.full_name buyer,
po_headers_sv3.get_po_status(pha.po_header_id) status,
pha.comments description,
&invoice_columns
xxen_util.client_time(pha.creation_date) po_creation_date,
xxen_util.user_name(pha.created_by) created_by,
xxen_util.client_time(pha.approved_date_) approved_date,
xxen_util.meaning(nvl(pha.authorization_status_,'INCOMPLETE'),'AUTHORIZATION STATUS',201) authorization_status,
pha.amount_limit,
pha.blanket_total_amount,
pha.currency_code,
nvl(pha.closed_code_,'OPEN') closed_code,
xxen_util.client_time(pha.closed_date) closed_date,
(select sum(rt.quantity) from rcv_transactions rt where pha.po_header_id=rt.po_header_id and nvl(pha.po_release_id,-99)=nvl(rt.po_release_id,-99)) quantity_received,
aps.type_1099,
assa.vendor_site_code,
assa.address_line1,
assa.address_line2,
assa.address_line3,
assa.city,
assa.state,
assa.zip,
assa.country,
decode(assa.phone,null,null,'('||assa.area_code||') '||assa.phone) phone,
decode(assa.fax,null,null,'('||assa.fax_area_code||') '||assa.fax) fax,
decode(pvc.last_name,null,null,pvc.last_name||', '||pvc.first_name) vendor_contact,
at.name term_name,
hlav1.location_code,
hlav2.location_code,
pha.note_to_authorizer,
pha.note_to_receiver,
pha.note_to_vendor_,
pha.print_count,
xxen_util.client_time(pha.printed_date) printed_date,
pha.quote_vendor_quote_number,
pha.end_date,
pha.end_date_active,
pha.start_date,
pha.start_date_active,
pha.po_header_id
from
hr_all_organization_units_vl haouv,
(
select
pha.*,
nvl(pra.approved_date,pha.approved_date) approved_date_,
nvl(pra.authorization_status,pha.authorization_status) authorization_status_,
nvl(pra.note_to_vendor,pha.note_to_vendor) note_to_vendor_,
nvl(pra.closed_code,pha.closed_code) closed_code_,
pra.release_num,
pra.po_release_id
from
po_headers_all pha,
po_releases_all pra
where
pha.po_header_id=pra.po_header_id(+)
) pha,
per_people_x ppx,
ap_suppliers aps,
ap_supplier_sites_all assa,
po_vendor_contacts pvc,
po_document_types_all_vl pdtav,
ap_terms at,
hr_locations_all_vl hlav1,
hr_locations_all_vl hlav2,
(select distinct aila.invoice_id, aila.po_header_id, aila.po_release_id from ap_invoice_lines_all aila) aila,
ap_invoices_all aia
where
1=1 and
haouv.organization_id=pha.org_id and
pha.type_lookup_code in ('PLANNED','CONTRACT','BLANKET','STANDARD') and
pha.agent_id=ppx.person_id(+) and
pha.vendor_id=aps.vendor_id(+) and
pha.vendor_site_id=assa.vendor_site_id(+) and
pha.vendor_contact_id=pvc.vendor_contact_id(+) and
pdtav.document_type_code(+) in ('PO','PA') and
pha.type_lookup_code=pdtav.document_subtype(+) and
pha.org_id=pdtav.org_id(+) and
pha.terms_id=at.term_id(+) and
pha.ship_to_location_id=hlav1.location_id(+) and
pha.bill_to_location_id=hlav2.location_id(+) and
(pha.vendor_contact_id is null or pha.vendor_site_id=pvc.vendor_site_id) and
pha.po_header_id=aila.po_header_id(+) and
nvl(pha.po_release_id,-99)=nvl(aila.po_release_id(+),-99) and
aila.invoice_id=aia.invoice_id(+)
order by
po_creation_date desc
Parameter Name SQL text Validation
Operating Unit 
haouv.name=:operating_unit
 LOV
Supplier 
aps.vendor_name=:vendor_name
 LOV
Supplier Site 
assa.vendor_site_code=:supplier_site
 LOV
PO Number 
pha.segment1=:po_number
 LOV
Type 
pha.release_num is null
 LOV
Payment Status 
aia.payment_status_flag=decode(:payment_status,'Partially Paid','P','Not Paid','N','paid','Y')
 LOV
Show Invoice Details 
aia.invoice_num,
xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
aia.invoice_date,
aia.invoice_amount,
aia.amount_paid,
decode(aia.payment_status_flag,'Y',fnd_message_cache.get_string ('POS','POS_PAID'),'N',fnd_message_cache.get_string ('POS','POS_NOT_PAID'),'P',fnd_message_cache.get_string('POS','POS_PARTIALLY_PAID')) payment_status,
 LOV Oracle
Invoice exists 
exists (select null from ap_invoice_lines_all aila where nvl(aila.discarded_flag,'N')='N' and pha.po_header_id=aila.po_header_id and nvl(pha.po_release_id,-99)=nvl(aila.po_release_id,-99))
 LOV Oracle
Receiving TRX exists 
exists (select null from rcv_transactions rt where pha.po_header_id=rt.po_header_id and nvl(pha.po_release_id,-99)=nvl(rt.po_release_id,-99))
 LOV Oracle
Creation Date From 
pha.creation_date>=:po_date_from
 Date
Creation Date To 
pha.creation_date<:po_date_to+1
 Date
Created By 
pha.created_by=xxen_util.user_id(:created_by)
 LOV
Download
 
Related Blitz Reports
PO Headers and Lines PO Receiving Transactions Pipeline PO Headers 11i XXT OF: PO Headers and Lines XXSUB PO Headers and Lines PO Headers and Lines 11i GL Account Analysis GL Account Analysis (Distributions) ECC Procurement, Agreements
Blitz Report™

Blitz Report™ provides multiple benefits: