Saturday, December 30, 2017

Query to Get Purchase Order (PO) Details

  SELECT
         poh.type_lookup_code po_type,
         poh.po_header_id,
         poh.segment1 po_number,
         poh.authorization_status po_status,
         poh.comments description,
         pov.vendor_name supplier_name,
         povs.vendor_site_code site_code,
         hrls.location_code ship_to,
         hrlb.location_code bill_to,
         ppf.full_name buyer_name,
         poh.closed_code,
         polt.line_type,
         pol.line_num,
         pol.item_description,
         pol.unit_price,
         pol.quantity,
         gcc.concatenated_segments
    FROM po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll
         po_distributions_all pod,
         ap_suppliers pov,
         ap_supplier_sites_all povs,
         hr_locations_all hrls,
         hr_locations_all hrlb,
         per_all_people_f ppf,
         po_line_types polt,
         gl_code_combinations_kfv gcc
   WHERE     1 = 1
         AND polt.line_type_id = pol.line_type_id
         AND povs.vendor_site_id = poh.vendor_site_id
         AND pov.vendor_id = poh.vendor_id
         AND poh.po_header_id = pol.po_header_id
         AND pol.po_line_id = pod.po_line_id
         AND poll.line_location_id = pod.line_location_id
         AND poh.ship_to_location_id = hrls.location_id
         AND poh.bill_to_location_id = hrlb.location_id
         AND poh.agent_id = ppf.person_id
         AND pod.code_combination_id = gcc.code_combination_id
         AND pol.org_id = 142

ORDER BY poh.segment1, pol.line_num

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect