Approved Requisition Report

Approved Requisition Report

SELECT PRHA.SEGMENT1 REQNUM,
       PRHA.TYPE_LOOKUP_CODE REQTYPE,
       PRHA.CREATION_DATE REQDATE,
       PAPF.FULL_NAME PREPARERNAME,
       PRLL.LINE_NUM,
       PRLL.QUANTITY,
       PRLL.UNIT_PRICE,
       MSI.SEGMENT1 ITEM,
       MSI.DESCRIPTION,
       (PRLL.QUANTITY * PRLL.UNIT_PRICE) AMOUNT,
       GCC.CONCATENATED_SEGMENTS CHARGEACCOUNT,
       OOD.ORGANIZATION_CODE ORGNAME,
       PAPF.FULL_NAME REQUESTOR,
       HL.LOCATION_CODE LOCATIONNAME
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLL,
       MTL_SYSTEM_ITEMS_B MSI,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_SETS_OF_BOOKS GSB,
       ORG_ORGANIZATION_DEFINITIONS OOD,
       PER_ALL_PEOPLE_F PAPF,
       HR_LOCATIONS HL
 WHERE PRHA.REQUISITION_HEADER_ID=PRLL.REQUISITION_HEADER_ID
   AND PRLL.ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND PRLL.DESTINATION_ORGANIZATION_ID=MSI.ORGANIZATION_ID
   AND PRLL.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
   AND PRDA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND GSB.SET_OF_BOOKS_ID=PRDA.SET_OF_BOOKS_ID
   AND OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND PAPF.PERSON_ID=PRHA.PREPARER_ID
   AND HL.LOCATION_ID=PRLL.DELIVER_TO_LOCATION_ID
   AND PRHA.AUTHORIZATION_STATUS = 'APPROVED'
   AND PRHA.ORG_ID=204
  -- AND  PRHA.SEGMENT1='2738'  
   AND ROWNUM<200
   ORDER BY 1,5 DESC
 

Total Requisition Code

SELECT  REH.SEGMENT1 REQ_NUM,
        REH.TYPE_LOOKUP_CODE,
        REH.AUTHORIZATION_STATUS,
        PPF.FULL_NAME,
        TO_CHAR((PRL.QUANTITY * PRL.UNIT_PRICE),'99,999.99') TOT_AMOUNT,
        PRL.LINE_NUM,
        PLT.LINE_TYPE TYPE,
        MSI.SEGMENT1 ITEM   ,
        MC.CONCATENATED_SEGMENTS ITEM_CATEGORY,
        MUM.UNIT_OF_MEASURE,
        PRL.QUANTITY,
        PRL.UNIT_PRICE,
        PRL.NEED_BY_DATE,
        PRL.SUGGESTED_VENDOR_NAME  SUP_NAME,
        PRL.SUGGESTED_VENDOR_LOCATION SUP_SITE,
        PRD.DISTRIBUTION_NUM ,
        PRD.REQ_LINE_QUANTITY DIST_QTY,
        GCC.CONCATENATED_SEGMENTS "ACC_NUM"        
  FROM  PO_REQUISITION_HEADERS_ALL REH,
        PO_REQUISITION_LINES_ALL PRL,
        PO_REQ_DISTRIBUTIONS_ALL PRD,
        PER_ALL_PEOPLE_F PPF,
        PO_LINE_TYPES PLT,
        MTL_CATEGORIES_KFV MC,
        MTL_CATEGORY_SETS  MCS,
        MTL_ITEM_CATEGORIES MIC,
        MTL_SYSTEM_ITEMS_B MSI,
        MTL_UNITS_OF_MEASURE MUM,
        GL_CODE_COMBINATIONS_KFV GCC,
        GL_SETS_OF_BOOKS GSB
 WHERE  REH.PREPARER_ID=PPF.PERSON_ID
   AND  REH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
   AND  PRL.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
   AND  PRL.LINE_TYPE_ID=PLT.LINE_TYPE_ID
   AND  PRL.ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND  PRL.ORG_ID=MSI.ORGANIZATION_ID
   AND  PRL.CATEGORY_ID=MC.CATEGORY_ID
   AND  MC.STRUCTURE_ID=MCS.STRUCTURE_ID
   AND  MCS.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
   AND  MIC.CATEGORY_ID=MC.CATEGORY_ID
   AND  MIC.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND  MSI.ORGANIZATION_ID=MIC.ORGANIZATION_ID
   AND  MSI.PRIMARY_UOM_CODE=MUM.UOM_CODE
   AND  PRD.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND  GCC.CHART_OF_ACCOUNTS_ID=GSB.CHART_OF_ACCOUNTS_ID
   AND REH.ORG_ID =NVL(:P_ORG_ID,REH.ORG_ID)
   AND REH.SEGMENT1='5962'