Introduction
This Post illustrates the steps required to retrieve Internal Requisition and Internal Sales Order Details in Oracle EBS R12.
Script to Query retrive Internal Requisition and Internal Sales Order
SELECT prh.segment1 ir_number,
prh.creation_date ir_date,
ppf.full_name requestor,
hl.location_code,
ood.organization_code destination_org_code,
ood.organization_name destination_org_name,
ood1.organization_code source_org_code,
ood1.organization_name source_org_name,
prh.authorization_status ir_status,
(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 ) ir_approved_date,
msi.segment1 item_code,
msi.description item_description,
prl.quantity,
prl.unit_meas_lookup_code,
ooh.order_number internal_so_number,
ooh.ordered_date sale_order_date,
(select distinct request_number
from wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where wdd.source_header_id=ooh.header_id
and wdd.move_order_line_id=mtrl.line_id
and mtrl.header_id=mtrh.header_id)move_order_ref,
(select mtrh.creation_date
from wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
where wdd.source_header_id=ooh.header_id
and wdd.move_order_line_id=mtrl.line_id
and mtrl.header_id=mtrh.header_id
and rownum=1)transact_move_order_date,
(select global_attribute16
from wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
where wdd.source_header_id=ooh.header_id
and wdd.delivery_detail_id=wda.delivery_detail_id
and wda.delivery_id=wnd.delivery_id
and rownum=1)actual_dispatch_date,
(select rsh.receipt_num
from rcv_transactions rcv,
rcv_shipment_headers rsh
where rcv.requisition_line_id = prl.requisition_line_id
and rcv.shipment_header_id = rsh.shipment_header_id
and rownum=1)destination_org_mrn_number,
(select rsh.creation_date
from rcv_transactions rcv,
rcv_shipment_headers rsh
where rcv.requisition_line_id = prl.requisition_line_id
and rcv.shipment_header_id = rsh.shipment_header_id
and rownum=1)destination_org_mrn_date
— mtrl.reference move_order_ref
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
per_all_people_f ppf,
hr_locations hl,
org_organization_definitions ood,
org_organization_definitions ood1,
mtl_system_items_b msi,
oe_order_headers_all ooh
— oe_order_lines_all oola
WHERE 1=1
AND prh.segment1 = ‘10172000065’–111597–1043
AND prh.requisition_header_id = prl.requisition_header_id
AND prl.to_person_id = ppf.person_id
AND ppf.effective_end_date > sysdate
AND prl.deliver_to_location_id = hl.location_id(+)
AND prl.destination_organization_id = ood.organization_id
AND prl.source_organization_id = ood1.organization_id
AND prl.item_id = msi.inventory_item_id
AND prl.source_organization_id = msi.organization_id
AND prh.requisition_header_id = ooh.source_document_id
–AND oola.header_id = ooha.header_id(+)
What we expect in the script.
This script helps us to retrieve internal requisition and internal sales order details.. A couple of tables which is being used in the scripts are
po_requisition_headers_all, per_all_people,org_organization_definitions and etc
summary
This Post described the script for internal requisition and internal sales order details in Oracle EBS R12.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
