Open Purchase Orders for specific OU or a Ledger
Below is the script which can be used to extract the Open Purchase Orders for a specific operating unit and the ledger..
select
h.name OU_NAME,
(select r.segment1 from
apps.po_requisition_headers_all r,
apps.po_requisition_lines_all rl,
apps.po_req_distributions_all rd
WHERE 1=1
AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID
AND rd.requisition_line_id = rl.requisition_line_id
AND PDA.req_distribution_id = RD.distribution_id AND ROWNUM=1)””REQ_NUMBER””,
po.segment1 “”PO Number””,
po.creation_date “”PO Raised Date””,
aps.vendor_name “”Supplier Name””,
APS.VENDOR_ID “”Supplier Id””,
(select vendor_site_code from apps.ap_supplier_sites_all where vendor_id=aps.vendor_id and rownum=1) “”Supplier SIte Code””,
pl.VENDOR_PRODUCT_NUM “”SUPPLIER_ITEM_NUM””,
PL.ITEM_ID “”Po_item_id””,
(select segment1 from apps.mtl_categories where CATEGORY_ID=pl.CATEGORY_ID and rownum=1)””Po_category””,
po.last_update_date,
po.currency_CODE,po.REVISION_NUM “”Version_Number””,
ppf.full_name Requestor_name,
–po.OBJECT_VERSION_NUMBER,–yesh
po.AUTHORIZATION_STATUS “”po_status””,
pl.LINE_NUM “”po_line_number””,
(select order_type_lookup_code from apps.po_line_types_b where line_type_id = pl.line_type_id)””Line Type””,
replace(pl.item_description,CHR(10),”) item_description,
PL.quantity “”PO Line Qty””,
pl.unit_price “”PO LINE PRICE””,
(PL.quantity*pl.unit_price) “”PO line Amt””,
pl.CANCEL_FLAG “”Po line Status””,
PLLA.NEED_BY_DATE “”PO Line Need By Date””,
PLLA.QUANTITY_RECEIVED “”PO Line Receipted Qty””,
–TO_CHAR(MAX(rt.transaction_date),’MM/DD/YY’) “”POLinE_Last_Receipt Date””,
(PLla.QUANTITY_RECEIVED*pl.unit_price) “”PO Line Receipted Value””,
Pda.QUANTITY_BILLED “”PO Line Billed Qty””,
–nvl2(PDA.AMOUNT_BILLED ,0,(PL.quantity*pl.unit_price)) “”PO Line AMOUNT_BILLED””,
PDA.AMOUNT_BILLED “”invoice match””,
decode(PDA.AMOUNT_BILLED,0,(PL.quantity*pl.unit_price),PDA.AMOUNT_BILLED,(decode(PDA.AMOUNT_BILLED,”,(PL.quantity*pl.unit_price),PDA.AMOUNT_BILLED)) )””PO Line AMOUNT_BILLED””,
(plla.quantity_received * pl.unit_price)- nvl(plla.AMOUNT_BILLED,0) Accural_Amount,
pda.attribute1 project_number,
proj.description project_name,
proj.EFFECTIVE_DATE “”Project Closure Date””,proj.active_flag “”Project Status””,
ffl.description account,
gcc.segment2 COST_CENTER_NUMBER,
GCC.CONCATENATED_SEGMENTS charge_account,
(SELECT AIA.INVOICE_NUM
from APPS.ap_invoices_all aia
,APPS.ap_invoice_lines_all aial
,APPS.ap_invoice_distributions_all aida
WHERE 1 = 1
AND aia.invoice_id = aial.invoice_id
AND aial.invoice_id = aida.invoice_id
AND aial.line_number = aida.invoice_line_number
AND AIDA.po_distribution_id=PDA.po_distribution_id
AND AIA.VENDOR_ID=APS.VENDOR_ID AND ROWNUM=1)Invoice_Num
,(SELECT AIA.creation_date
from APPS.ap_invoices_all aia
,APPS.ap_invoice_lines_all aial
,APPS.ap_invoice_distributions_all aida
WHERE 1 = 1
AND aia.invoice_id = aial.invoice_id
AND aial.invoice_id = aida.invoice_id
AND aial.line_number = aida.invoice_line_number
AND AIDA.po_distribution_id=PDA.po_distribution_id
AND AIA.VENDOR_ID=APS.VENDOR_ID AND ROWNUM=1)Invoice_Creation
,(SELECT AIA.INVOICE_date
from APPS.ap_invoices_all aia
,APPS.ap_invoice_lines_all aial
,APPS.ap_invoice_distributions_all aida
WHERE 1 = 1
AND aia.invoice_id = aial.invoice_id
AND aial.invoice_id = aida.invoice_id
AND aial.line_number = aida.invoice_line_number
AND AIDA.po_distribution_id=PDA.po_distribution_id
AND AIA.VENDOR_ID=APS.VENDOR_ID AND ROWNUM=1)Invoice_Date
,(SELECT
rsh.receipt_num
FROM –apps.po_headers_all pha,
apps.rcv_shipment_lines rsl,
apps.rcv_shipment_headers rsh
WHERE 1=1
AND po.po_header_id=rsl.po_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
–AND pha.segment1=po.header_id
and rownum=1)””Receipt Number””,
(SELECT
rt.transaction_date
FROM –apps.po_headers_all pha,
apps.rcv_transactions rt,
apps.rcv_shipment_lines rsl,
apps.rcv_shipment_headers rsh
WHERE 1=1
AND po.po_header_id=rsl.po_header_id
and rt.po_line_id(+) = pl.po_line_id
AND rsl.shipment_header_id=rsh.shipment_header_id
–AND pha.segment1=po.header_id
and rownum=1)””Receipt Date””
from apps.po_headers_all po,
apps.po_lines_all pl,
apps.po_distributions_all pda,
apps.po_line_locations_all plla,
apps.per_people_x ppf,
apps.hr_operating_units h,
apps.ap_suppliers aps,
APPS.iron_proj_details proj,
–apps.rcv_transactions rt,
apps.GL_CODE_COMBINATIONS_KFV GCC,
apps.fnd_flex_values_vl ffl
where po.po_header_id=pl.po_header_id
AND PO.closed_code =’OPEN’
–AND pO.authorization_status = ‘APPROVED’
and pl.closed_date is null
and pl.CLOSED_CODE=’OPEN’
–and po.segment1 in(‘OX1020069720′,’IM1020053117′,’OX1020071370′)
AND PDA.PO_LINE_ID = PL.PO_LINE_ID
and po.org_id in (82,83)
–and trunc(po.creation_date)>=’01-JAN-21′ and trunc(po.creation_date)<=’31-MAR-21’
AND PDA.PO_HEADER_ID = PO.PO_HEADER_ID
and pda.deliver_to_person_id = ppf.person_id(+)
and po.org_id=h.ORGANIZATION_ID
and Po.VENDOR_ID = APS.VENDOR_ID
and plla.po_line_id(+) = pl.po_line_id
and po.po_header_id = plla.po_header_id(+)
–and plla.quantity <> plla.quantity_billed
–AND PA.PROJECT_ID(+) = PDA.PROJECT_ID
and proj.project_number(+) = pda.attribute1
–and rt.transaction_type(+) = ‘DELIVER’
—AND api.vendor_id=aps.vendor_id
—AND API.INVOICE_ID=APD.INVOICE_ID(+)
—AND PDA.Po_distribution_id = APD.po_distribution_id
—AND API.INVOICE_ID=APIL.INVOICE_ID
—AND APIL.INVOICE_ID=APD.INVOICE_ID
–and rt.po_line_id(+) = pl.po_line_id
AND GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID
and gcc.segment2 = ffl.flex_value
–and gcc.segment1 in(‘16700’)
GROUP BY
h.name ,
po.segment1 ,
po.creation_date ,
aps.vendor_name ,
po.last_update_date,
po.currency_CODE,
ppf.full_name ,
po.AUTHORIZATION_STATUS ,
pl.LINE_NUM ,
pl.item_Description,
PL.quantity ,
pl.unit_price ,
PL.quantity*pl.unit_price ,
PLLA.NEED_BY_DATE ,
PLLA.QUANTITY_RECEIVED ,
pl.CANCEL_FLAG,
PLLA.QUANTITY_BILLED ,
PLLA.quantity_received,
plla.AMOUNT_BILLED,
PDA.AMOUNT_BILLED ,
pda.attribute1 ,
proj.description ,
po.creation_date,
GCC.CONCATENATED_SEGMENTS,
ffl.description,
Pda.QUANTITY_BILLED,
gcc.segment1,
gcc.segment2 ,APS.VENDOR_ID,PDA.po_distribution_id,pl.line_type_id,
pl.VENDOR_PRODUCT_NUM,pl.CATEGORY_ID,po.REVISION_NUM,PDA.req_distribution_id,PL.ITEM_ID,po.po_header_id,proj.EFFECTIVE_DATE,proj.active_flag, pl.po_line_id–rt.transaction_date
order by ou_name,PO.SEGMENT1,PL.LINE_NUM”
