Oracle EBS: PO and OM Queries

 Oracle  applications Order Management and  Purchasing integration Queries

While working on Oracle Purchasing and Order management ,some times we need how to get order number for a particular Purchase order ..

Please find the solution as below



SELECT POH. PO_HEADER_ID,
  POH. SEGMENT1 "PO NO" ,
  PRHA .SEGMENT1 "REQUISTION NO" ,  prha.interface_source_line_id
FROM PO_HEADERS_ALL POH,
  PO_DISTRIBUTIONS_ALL PDA ,
  PO_REQ_DISTRIBUTIONS_ALL PRDA ,
  PO_REQUISITION_LINES_ALL PRLA ,
  PO_REQUISITION_HEADERS_ALL PRHA
WHERE POH. PO_HEADER_ID         = PDA. PO_HEADER_ID
AND PDA. REQ_DISTRIBUTION_ID    = PRDA.DISTRIBUTION_ID
AND PRDA. REQUISITION_LINE_ID   = PRLA. REQUISITION_LINE_ID
AND PRLA. REQUISITION_HEADER_ID = PRHA. REQUISITION_HEADER_ID
AND poh.segment1                ='9191'

Highlighted one is line id in the oe_order_lines_all



select  header_id from oe_order_lines_all
where line_id=13667638--PO_REQUISITION_HEADERS_ALL .interface_source_line_id

Pass header_id in the following query to get order_number

select * from oe_order_headers_all
where header_id=7351274---order_number 6116920