PO Headers

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

SQL 96 lines

Parameters 12

ParameterTypeList of values SQL
Operating UnitLOV
haouv.name=:operating_unit
SupplierLOV
aps.vendor_name=:vendor_name
Supplier SiteLOV
assa.vendor_site_code=:supplier_site
PO NumberLOV
pha.segment1=:po_number
TypeLOV
pha.release_num is null
Payment StatusLOV
aia.payment_status_flag=decode(:payment_status,'Partially Paid','P','Not Paid','N','paid','Y')
Show Invoice DetailsLOV Oracle
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,
Invoice existsLOV Oracle
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))
Receiving TRX existsLOV Oracle
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))
Creation Date FromDate
pha.creation_date>=:po_date_from
Creation Date ToDate
pha.creation_date<:po_date_to+1
Created ByLOV
pha.created_by=xxen_util.user_id(:created_by)