11i & 12i Queries for Internal Requisition and Internal Sales Order

Source - metalink.oracle.com

1. Getting data from oe lines iface all based on PO requisition number (Before Order Import)select * from oe_lines_iface_all
where order_source_id = 10 --order_source_id for 'Internal'
and orig_sys_document_ref =
(select to_char(requisition_header_id)
from po_requisition_headers_all prh
where prh.segment1 = '&requisition_number');

2. Getting data from order header/lines based on PO requistion number (after Order Import) SELECT oeh.order_number, oeh.header_id, oel.line_id, oel.line_number
FROM
oe_order_lines_all oel,
oe_order_headers_all oeh,
po_requisition_headers_all porh,
po_requisition_lines_all porl
WHERE oeh.header_id = oel.header_id
AND oel.source_document_id = porh.requisition_header_id
AND oel.source_document_line_id = porl.requisition_line_id
AND porh.requisition_header_id = porl.requisition_header_id
AND oel.order_source_id = 10 --order_source_id for 'Internal'
AND oel.orig_sys_document_ref = '&requisition_number'
AND oel.org_id = porh.org_id
ORDER BY oeh.header_id, oel.line_id;


3. The following sql can be used to confirm the Location defined in Oracle Purchasing is tied to a Customer in Order Management. The script shows that last ten locations created in order of creation date.

SELECT rtrim(hl.location_code) location_code, hl.location_id, ood.organization_code, pla.organization_id, hl.ship_to_site_flag, hl.receiving_site_flag, pla.customer_id, rtrim(rc.customer_name) customer_name
FROM hr_locations_all hl,
org_organization_definitions ood,
po_location_associations_all pla,
ra_customers rc WHERE pla.location_id = hl.location_id
AND rc.customer_id(+) = pla.customer_id
AND ood.organization_id(+) = pla.organization_id
AND rownum < order_source_id =" posp.order_source_id" org_id =" posp.org_id" requisition_header_id =" ool.source_document_id" requisition_line_id =" ool.source_document_line_id" requisition_header_id =" porl.requisition_header_id" requisition_line_id =" pord.requisition_line_id" requisition_line_id =" rcv.requisition_line_id" distribution_id =" rcv.req_distribution_id" shipment_header_id =" rsh.shipment_header_id" org_id =" posp.org_id" header_id =" ooh.header_id"> 0
AND ool.orig_sys_line_ref not like '%OE_ORDER_LINES_ALL%'
AND ool.source_document_line_id is not null