Oracle Apps : Query to get the Purchase Order (PO) details along with the Requisition details in R12.1.3

 The below gives the details of a Purchase Order (PO) details along with the Requisition details in Oracle Apps R12.1.3.

 SELECT  poh.segment1          po_number  
        ,mp.organization_code  org_code
        ,haou.name             org_name  
        ,porh.segment1         po_requisition_num  
        ,poh.creation_date     po_creation_date  
 FROM  po_headers_all              poh  
      ,po_lines_all                pol  
      ,po_line_locations_all       pll  
      ,po_distributions_all        pda  
      ,po_req_distributions_all    pord  
      ,po_requisition_lines_all    porl  
      ,po_requisition_headers_all  porh  
      ,mtl_parameters              mp  
      ,hr_all_organization_units   haou  
 WHERE 1=1
 AND   poh.po_header_id           = pol.po_header_id  
 AND   pll.po_header_id           = pol.po_header_id  
 AND   pll.po_line_id             = pol.po_line_id  
 AND   pda.po_header_id           = pol.po_header_id  
 AND   pda.po_line_id             = pol.po_line_id  
 AND   pda.line_location_id       = pll.line_location_id  
 AND   pda.req_distribution_id    = pord.distribution_id(+)  
 AND   pda.org_id                 = pord.org_id(+)  
 AND   pord.requisition_line_id   = porl.requisition_line_id(+)  
 AND   porl.requisition_header_id = porh.requisition_header_id(+)  
 AND   mp.organization_id         = poh.org_id  
 AND   haou.organization_id       = poh.org_id
 AND   poh.segment1               = '31200000'  
 AND   poh.org_id                 = 0 -- depending on which operating unit  
;  

Edited: An updated query with some more information fetched from other tables.

 SELECT poh.segment1          po_number  
       ,mp.organization_code  
       ,haou.organization_id  
       ,haou.name             org_name  
       ,porh.segment1         po_requisition_num  
       ,poh.creation_date     po_creation_date  
       ,aps.segment1          supplier_number  
       ,aps.vendor_name       supplier_name  
       ,apss.attribute4       cloud_supplier_site_id  
       ,apt.name terms  
       ,poh.vendor_id  
       ,poh.vendor_site_id  
       ,pol.line_num  
       ,pol.quantity          line_quantity_ordered     
       ,msib.segment1         line_item  
       ,msib.description  
       ,pol.item_description  
       ,pll.shipment_num  
       ,pda.distribution_num  
       ,pda.destination_subinventory  
       ,pda.deliver_to_person_id  
       ,(select ppf.full_name   
        from    per_all_people_f ppf  
        where ppf.person_id = pda.deliver_to_person_id  
        and   sysdate between ppf.effective_start_date and ppf.effective_end_date) deliver_to_person_name  
       ,pda.quantity_ordered dist_quantity_ordered  
 FROM  po_headers_all poh  
      ,po_lines_all pol  
      ,po_line_locations_all pll  
      ,po_distributions_all pda  
      ,po_req_distributions_all pord  
      ,po_requisition_lines_all porl  
      ,po_requisition_headers_all porh  
      ,mtl_parameters mp  
      ,hr_all_organization_units haou  
      ,ap_suppliers aps  
      ,ap_supplier_sites_all apss  
      ,ap_terms apt  
      ,mtl_system_items_b msib  
 WHERE 1=1  
 AND  poh.po_header_id           = pol.po_header_id  
 AND  pll.po_header_id           = pol.po_header_id  
 AND  pll.po_line_id             = pol.po_line_id  
 AND  pda.po_header_id           = pol.po_header_id  
 AND  pda.po_line_id             = pol.po_line_id  
 AND  pda.line_location_id       = pll.line_location_id  
 AND  pda.req_distribution_id    = pord.distribution_id(+)  
 AND  pda.org_id                 = pord.org_id(+)  
 AND  pord.requisition_line_id   = porl.requisition_line_id(+)  
 AND  porl.requisition_header_id = porh.requisition_header_id(+)  
 AND  mp.organization_id         = poh.org_id  
 AND  haou.organization_id       = poh.org_id  
 AND  aps.vendor_id              = poh.vendor_id  
 AND  apss.vendor_id             = aps.vendor_id   
 AND  apss.vendor_site_id        = poh.vendor_site_id  
 AND  apt.term_id                = poh.terms_id  
 AND  msib.organization_id (+)   = pol.org_id  
 AND  msib.inventory_item_id(+)  = pol.item_id  
 AND  poh.segment1               = '31200000'
 AND  poh.org_id                 = 0 -- depending on which operating unit;  

Feel free to point out if anything is missing/wrong in this blog.