Saturday, 15 November 2014

how to find purchase order based on requisition in oracle

 This is the query to find out the purchase order number from requisition number

/* Formatted on 11/15/2014 10:59:20 AM (QP5 v5.115.810.9015) */
SELECT   DISTINCT pha.segment1
  FROM   po_headers_all pha,
         po_distributions_all pda,
         po_req_distributions_all rda,
         po_requisition_headers_all rha,
         po_requisition_lines_all rla
 WHERE       pha.po_header_id = pda.po_header_id
         AND pda.req_distribution_id = rda.distribution_id
         AND rda.requisition_line_id = rla.requisition_line_id
         AND rla.requisition_header_id = rha.requisition_header_id
         AND rha.segment1 = '14376'
You can also find out the requisition number from purchase order number by using query given below
/* Formatted on 11/15/2014 11:00:30 AM (QP5 v5.115.810.9015) */
SELECT   DISTINCT rha.segment1
  FROM   po_requisition_headers_all rha,
         po_requisition_lines_all rla,
         po_req_distributions_all rda,
         po_distributions_all pda,
         po_headers_all pha
 WHERE       rha.requisition_header_id = rla.requisition_header_id
         AND rla.requisition_line_id = rda.requisition_line_id
         AND rda.distribution_id = pda.req_distribution_id
         AND pda.po_header_id = pha.po_header_id
         AND pha.segment1 = '&PO_Number'

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.