PO Approved Report

PO Approved Report

SELECT PV.SEGMENT1 SUPNUM,
       PV.VENDOR_NAME SUPNAME,
       POH.SEGMENT1 PONUM,
       POH.CREATION_DATE PODATE,
       PAPF.FULL_NAME BUYER,
       HL1.LOCATION_CODE SHIP_TO,
       HL1.LOCATION_CODE BILL_TO,
       MSI.SEGMENT1 ITEM,
       MSI.DESCRIPTION,
       POL.LINE_NUM,
       POL.QUANTITY,
       POL.UNIT_PRICE,
       (POL.QUANTITY * POL.UNIT_PRICE) AMOUNT,
       GCC.CONCATENATED_SEGMENTS CHARGEACCOUNT
  FROM PO_VENDORS PV,
       PO_HEADERS_ALL POH,
       PER_ALL_PEOPLE_F PAPF,
       HR_LOCATIONS HL1,
       PO_LINES_ALL POL,
       MTL_SYSTEM_ITEMS_B MSI,
       PO_DISTRIBUTIONS_ALL POD,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_SETS_OF_BOOKS GSB
 WHERE PV.VENDOR_ID = POH.VENDOR_ID
   AND POH.SHIP_TO_LOCATION_ID=HL1.LOCATION_ID
   AND POH.BILL_TO_LOCATION_ID=HL1.LOCATION_ID
   AND POH.AGENT_ID=PAPF.PERSON_ID
   AND MSI.INVENTORY_ITEM_ID=POL.ITEM_ID
   AND MSI.ORGANIZATION_ID=POL.ORG_ID
   AND POH.PO_HEADER_ID=POL.PO_HEADER_ID
   AND POL.PO_LINE_ID=POD.PO_LINE_ID
   AND POD.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND POD.SET_OF_BOOKS_ID=GSB.SET_OF_BOOKS_ID
   AND GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND POH.AUTHORIZATION_STATUS = 'APPROVED'
   AND POH.ORG_ID=NVL(:P_ORG_ID,POH.ORG_ID)
   AND POH.TYPE_LOOKUP_CODE=NVL(:P_TYPE,POH.TYPE_LOOKUP_CODE)
   AND POH.SEGMENT1=NVL(:P_PO_NUM,POH.SEGMENT1)
   ORDER BY 3,10