PO Report Quries

1.CANCELLED PO’S

SELECT PHA.SEGMENT1,

         PHA.CREATION_DATE,

         PLA.CANCEL_DATE,

         PLA.CANCEL_REASON,

         PLA.ITEM_DESCRIPTION,

         HRL.LOCATION_CODE,

         FU.USER_NAME,

         DECODE (PHA.CANCEL_FLAG,'Y',’CANCELLED’) STATUS

FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA, HR_LOCATIONS HRL, FND_USER FU

         WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

         AND HRL.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID

         AND PHA.CREATED_BY = FU.USER_ID

         AND PHA.CANCEL_FLAG ='Y'

2.PENDING PO’S

SELECT   PHA.SEGMENT1 PO_NUM,

             PHA.CREATION_DATE PO_DATE,

             PLA.ITEM_DESCRIPTION,

             HL.LOCATION_CODE,

             PLA.CANCEL_DATE,

             PLA.CANCEL_REASON,

             FU.USER_NAME USERNAME,

             PHA.VENDOR_SITE_ID,

             DECODE (PHA.CANCEL_FLAG,'Y','CANCELLED','PENDING') STATUS

FROM     PO_HEADERS_ALL PHA,

             PO_LINES_ALL PLA,

             HR_LOCATIONS HL,

             FND_USER FU           

WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID

AND         PHA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID

AND         PHA.CANCEL_FLAG! ='Y'

AND         PHA.CREATED_BY=FU.USER_ID

3.PO SUMMARY REPORT

SELECT PHA.SEGMENT1 PONO,

         PHA.CREATION_DATE PODATE,

         PLA.QUANTITY,

         PLA.UNIT_PRICE,

         PLA.ITEM_DESCRIPTION,

         MSI.SEGMENT1 ITEM,

         PV.VENDOR_NAME,

         PAV.AGENT_NAME

FROM PO_HEADERS_ALL PHA,

     PO_LINES_ALL PLA,

       MTL_SYSTEM_ITEMS_B MSI,

       PO_VENDORS PV,

       PO_AGENTS_V PAV

WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID

AND PLA.ORG_ID = MSI.ORGANIZATION_ID

AND PHA.VENDOR_ID = PV.VENDOR_ID

AND PAV.AGENT_ID = PHA.AGENT_ID

--AND PV.VENDOR_NAME = :SUPPLIER

--AND PAV.AGENT_NAME = :BUYER

4. SUBLEDGER DATA

SELECT        MTT.TRANSACTION_TYPE_NAME TRNX_TYPE,

              OOD.ORGANIZATION_NAME ORG,

              MSIB.SEGMENT1 ITEM,

              MMT.CURRENCY_CODE  BASE_CURRENCY,

              MMT.SUBINVENTORY_CODE SUB_INV,

              MMT.TRANSACTION_ID TRNX_ID,

              MMT.TRANSACTION_QUANTITY TRNX_QTY,

              TRUNC(MMT.TRANSACTION_DATE) TRNX_DATE,

              MTLN.LOT_NUMBER LOT_NO,

              GCCK.CONCATENATED_SEGMENTS ACCNTS,

              GJL.ACCOUNTED_DR BASE_DEBIT,

              GJL.ACCOUNTED_CR BASE_CREDIT,

              GJL.ENTERED_DR BILLING_DEBIT,

              GJL.ENTERED_CR BILLING_CREDIT,

          GJH.JE_SOURCE SOURCE

FROM      MTL_MATERIAL_TRANSACTIONS MMT, 

              MTL_TRANSACTION_TYPES MTT, 

              ORG_ORGANIZATION_DEFINITIONS OOD, 

              MTL_SYSTEM_ITEMS_B MSIB, 

              MTL_TRANSACTION_LOT_NUMBERS MTLN,

              HR_LEGAL_ENTITIES HLE,

              GL_CODE_COMBINATIONS_KFV GCCK,

              GL_JE_HEADERS GJH, 

              GL_JE_LINES GJL,

              HR_OPERATING_UNITS HOU,

              GL_PERIODS GP,

              GL_PERIOD_TYPES GPT,

              GL_SETS_OF_BOOKS GSOB

WHERE         MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID 

AND           MMT.ORGANIZATION_ID=OOD.ORGANIZATION_ID 

AND           MMT.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID 

AND           MMT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID 

AND           MMT.TRANSACTION_ID=MTLN.TRANSACTION_ID 

--AND         MMT.TRANSACTION_DATE BETWEEN :FDATE AND :TDATE

AND     OOD.LEGAL_ENTITY=HLE.ORGANIZATION_ID

--AND         HLE.NAME=:P_LEGAL_ENTITY

AND           HOU.LEGAL_ENTITY_ID=HLE.ORGANIZATION_ID

AND           GJH.JE_HEADER_ID=GJL.JE_HEADER_ID

AND           GJL.CODE_COMBINATION_ID=GCCK.CODE_COMBINATION_ID

AND           GJH.JE_SOURCE='Inventory'

AND     GSOB.SET_OF_BOOKS_ID=HLE.SET_OF_BOOKS_ID

AND     GSOB.PERIOD_SET_NAME=GP.PERIOD_SET_NAME

--AND         GP.PERIOD_YEAR=:FISCAL_YEAR

--AND         GP.PERIOD_NUM=:PERIOD

5.PO-AP REPORT

SELECT PHA.SEGMENT1 PONO,

         PHA.CREATION_DATE PODATE,

         PHA.TYPE_LOOKUP_CODE POTYPE,

         PLA.LINE_NUM,

         PLA.ITEM_DESCRIPTION,

         PLA.UNIT_PRICE,

         PLA.QUANTITY,

         PV.VENDOR_NAME,

         PVS.VENDOR_SITE_CODE,

         PVC.LAST_NAME,

         AIA.INVOICE_NUM,

         AIA.INVOICE_DATE,

         AIA.INVOICE_ID

FROM PO_HEADERS_ALL PHA,

       PO_LINES_ALL PLA,

       PO_VENDORS PV,

       PO_VENDOR_SITES_ALL PVS,

       PO_VENDOR_CONTACTS PVC,

       AP_INVOICES_ALL AIA,

       AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

       PO_DISTRIBUTIONS_ALL POD

WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

AND PV.VENDOR_ID = PHA.VENDOR_ID

AND PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID

AND PVS.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID

AND AIDA.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID

AND AIDA.INVOICE_ID = AIA.INVOICE_ID

AND PLA.PO_LINE_ID = POD.PO_LINE_ID

AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

6. MOVE ORDER REPORT

SELECT   MTRH.REQUEST_NUMBER,

             MTRH.HEADER_STATUS TASK_STATUS,

             MTRL.QUANTITY MOQTY,

             MSIB.SEGMENT1 ITEM_NUM,

             MSIB.DESCRIPTION ITEM_DESC,

             MMT.PRIMARY_QUANTITY,

             MMT.SUBINVENTORY_CODE SOURCE_SUB_INV,

             MMT.TRANSFER_SUBINVENTORY DEST_SUB_INV,

             MMT.TRANSACTION_QUANTITY

FROM  MTL_TXN_REQUEST_HEADERS MTRH,

             MTL_TXN_REQUEST_LINES MTRL,

             MTL_SYSTEM_ITEMS_B MSIB,

             MTL_MATERIAL_TRANSACTIONS MMT

WHERE MMT.ORGANIZATION_ID='204'

AND         MTRH.HEADER_ID=MTRL.HEADER_ID

AND         MTRH.ORGANIZATION_ID=MTRL.ORGANIZATION_ID

AND         MTRL.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID

AND         MTRL.ORGANIZATION_ID=MSIB.ORGANIZATION_ID

AND         MTRH.ORGANIZATION_ID=MMT.ORGANIZATION_ID