Search This Blog

Friday, April 6, 2012

SQL Query to extract Oracle Purchase Order Information


SELECT
  poh.po_header_id, 
  poh.type_lookup_code PO_TYPE,
  poh.authorization_status PO_STATUS,
  poh.segment1 PO_NUMBER,
  pov.vendor_name SUPPLIER_NAME,
  povs.vendor_site_code Location,
  hrls.location_code Ship_To,
  hrlb.location_code Bill_to,
  pol.line_num ,
  msib.segment1 Item,
  pol.unit_price,
  pol.quantity,
  pod.amount_billed Amount,
  pod.destination_subinventory,
  ppf.full_name Buyer_Name,
  poh.closed_Code 
FROM
  PO_HEADERS_ALL poh,
  PO_LINES_ALL pol,
  mtl_system_items_b msib,
  PO_LINE_LOCATIONS_ALL poll,
  PO_DISTRIBUTIONS_ALL pod,
  po_vendors pov,
  po_vendor_sites_All povs,
  hr_locations_all hrls,
  hr_locations_all hrlb,
  per_all_people_f ppf,
  po_line_types polt
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 pol.item_id             = msib.inventory_item_id
AND msib.organization_id    = 204
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 poh.segment1            = &LP1;

2 comments:

  1. It would be a great to share a bit more on this matter. I’m glad I come across your site really informative post. Thanks for sharing this material.

    www.n8fan.net

    ReplyDelete
  2. Can we also generate together it's remaining balance of each PO Number?

    Thanks in advanced

    ReplyDelete