Introduction
This post is about to Open Purchase Order Details(Standard Po) in Oracle EBS R12.
Script to Open Purchase Open Details (Standard PO)
SELECT poh.type_lookup_code source_type,
prh.segment1 pr_number,
trunc(prh.creation_date) pr_date,
(select action_date from po_action_history
where object_type_code=’REQUISITION’ and action_code=’APPROVE’
and employee_id=prh.preparer_id and object_id=prh.requisition_header_id ) pr_approved_date,
papf.full_name buyer,
poh.segment1 po_number,
poh.creation_date po_creation_date,
aps.vendor_name supplier_name,
apss.vendor_site_code supplier_site,
hla.location_code ship_to_location,
hla1.location_code bill_to_location,
poh.authorization_status po_status,
(select sum(pl.quantity*pl.unit_price) from po_lines_all pl where pl.po_header_id=poh.po_header_id) po_value,
pol.line_num,
mtl.segment1 item_code,
pol.item_description item_description,
pol.quantity quantity,
pol.unit_meas_lookup_code uom,
poll.need_by_date,
pol.unit_price unit_price,
(poll.quantity * poll.price_override) amount,
pap.segment1 project,
pap.name project_name,
pat.task_number project_task,
pat.task_name task_name,
poll.quantity po_qty,
poll.quantity_received,
poll.quantity_billed
FROM
po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
ap_suppliers aps,
ap_supplier_sites_all apss,
hr_locations hla,
hr_locations hla1,
mtl_system_items_b mtl,
per_all_people_f papf,
pa_projects_all pap,
pa_tasks pat
WHERE
1 = 1
AND prl.requisition_header_id = prh.requisition_header_id –(+)
AND prd.requisition_line_id = prl.requisition_line_id –(+)
AND pod.req_distribution_id = prd.distribution_id –(+)
AND poll.line_location_id = pod.line_location_id
AND pol.po_line_id = poll.po_line_id
AND pol.po_header_id = pod.po_header_id
AND pol.po_line_id = pod.po_line_id
AND poh.po_header_id = pol.po_header_id
AND aps.vendor_id = poh.vendor_id
AND poh.vendor_site_id = apss.vendor_site_id
AND aps.vendor_id = apss.vendor_id
AND hla.location_id(+) = poh.ship_to_location_id
AND hla1.location_id(+)=poh.bill_to_location_id
AND mtl.inventory_item_id = pol.item_id
AND mtl.organization_id = poll.ship_to_organization_id
AND poh.agent_id = papf.person_id
AND trunc(poh.creation_date) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
AND poh.type_lookup_code=’STANDARD’
AND pap.project_id(+) = NVL (pod.project_id, -1)
AND pat.task_id(+)=NVL(pod.task_id,-1)
and poh.authorization_status=’APPROVED’
AND poh.cancel_flag <> ‘Y’
AND NVL(poh.closed_code,’OPEN’)=’OPEN’
AND NVL(pol.closed_code,’OPEN’)=’OPEN’
Summary
This post described the script How to Open PO Details (Standard PO) in Oracle EBS R12.Couple of Tables which is being used in the script are. po_requisition_headers_all,po_requisition_lines_all, po_req_distributions_all,po_headers_all,po_lines_all, po_line_locations_all,po_distributions_all,ap_suppliers, ap_supplier_sites_all,hr_locations,hr_locations,mtl_system_items_b,per_all_people_f,pa_projects_all,pa_tasks etc.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
