PURCHASE ORDER QUERY

         PURCHASE ORDER QUERY                                                            
                                                                     
                                             
SELECT PHA.PO_HEADER_ID ,
    pha.SEGMENT1,
    pha.revision_num,
    pha.type_lookup_code,
    trunc(pha.creation_date) Cdate,
    PV.VENDOR_NAME        ,
    PVS.VENDOR_SITE_CODE    ,
    (PVC.last_NAME||','||PVC.firsT_NAME)  ContactName,
    H1.LOCATION_CODE       ShipTO,
    H2.LOCATION_CODE       BillTo,
    pha.currency_code      CUrr,
    PPF.FULL_NAME       Buyer,
    pha.AUTHORIZATION_STATUS     Status,
    pha.COMMENTS        PODesc,
    SUM(pla.quantity*pla.unit_price)         PoTotal
FROM  PO_HEADERS_ALL PHA,
   PO_VENDORS  PV ,
   PO_VENDOR_SITES_ALL PVS,
   PO_VENDOR_CONTACTS  PVC,
   HR_LOCATIONS    H1 ,
   HR_LOCATIONS    H2 ,
   PER_ALL_PEOPLE_F   ppf,
   PO_LINES_ALL    pla
WHERE pha.SEGMENT1         = '3478'
AND   pha.TYPE_LOOKUP_CODE = 'STANDARD'
AND   PV.VENDOR_ID     = PHA.VENDOR_ID
AND   PVS.VENDOR_SITE_ID   = PHA.VENDOR_SITE_ID
AND   PVC.VENDOR_CONTACT_ID= PHA.VENDOR_CONTACT_ID
AND   H1.LOCATION_ID    = PHA.SHIP_TO_LOCATION_ID
AND   H2.LOCATION_ID    = PHA.BILL_TO_LOCATION_ID
AND   ppf.person_id     = PHA.AGENT_ID
AND   pha.po_header_id    = PLA.po_header_id
GROUP BY 
pha.SEGMENT1,
pha.revision_num,
pha.type_lookup_code,
trunc(pha.creation_date), 
PV.VENDOR_NAME        ,
PVS.VENDOR_SITE_CODE    ,
(PVC.last_NAME||','||PVC.firsT_NAME)  ,
H1.LOCATION_CODE       ,
H2.LOCATION_CODE      ,
pha.currency_code     ,
PPF.FULL_NAME      ,
pha.AUTHORIZATION_STATUS    ,
pha.COMMENTS ,
PHA.PO_HEADER_ID     -   12160


Line Level 
===========
SELECT  PLA.LINE_NUM,
  PLT.LINE_TYPE,
  MSI.SEGMENT1  Item,
  (mc.segment1||','||mc.segment2) Category,
  msi.description   Itemdesc,
  MSI.PRIMARY_UOM_CODE UOM,
  pla.quantity,
  pla.unit_price
FROM    PO_LINES_ALL       PLA,
  PO_LINE_TYPES      PLT,
  MTL_SYSTEM_ITEMS_B MSI,
  MTL_CATEGORIES    MC
WHERE   PLA.PO_HEADER_ID      = 12160
AND  PLA.LINE_TYPE_ID      = PLT.LINE_TYPE_ID
AND  MSI.ORGANIZATION_ID   = PLA.ORG_ID
AND  MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
and  mc.CATEGORY_ID    = pla.CATEGORY_ID