1 Reply Latest reply on Dec 29, 2011 8:24 AM by HrishikeshJ

    Receipt_no for PO created ** Help Required **

    756798
      Hello everyone,
      I need to write a script at detailed level where i need the following :
      *1. FACTORY*
      *2. VENDOR_ID*
      *3. VENDOR_NAME*
      *4. PO_NO*
      *5. PO_DATE*
      *6. SKU*
      *7. DESCRIPTION*
      *8. ORDERED_QUANTITY*
      *9. ACCEPTED_QUANTITY*
      *10.ACCEPT_DATE*
      *11.PRICE*
      *12.TAX_AMT*
      *13.RECEIPT_NUM*

      /*Script Raw Materials Dispatched*/
      SELECT DEPO.DEPO_NAME FACTORY
      ,PV.VENDOR_ID VENDOR_ID
      ,PV.VENDOR_NAME VENDOR_NAME
      ,PHA.SEGMENT1 PO_NO
      ,PLA.CREATION_DATE PO_DATE
      ,MSIB.SEGMENT1 SKU
      ,MSIB.DESCRIPTION DESCRIPTION
      ,PLA.QUANTITY ORDERED_QUANTITY
      ,PLLA.QUANTITY_ACCEPTED ACCEPTED_QUANTITY
      ,PLLA.LAST_ACCEPT_DATE ACCEPT_DATE
      ,PLLA.QUANTITY_ACCEPTED*PLA.UNIT_PRICE PRICE
      ,NVL(TAX.TAX_AMOUNT,0) TAX_AMT
      *,CHALAN.RECEIPT_NUM RECEIPT_NUM*
      FROM BPIL.MTL_SYSTEM_ITEMS_B MSIB,
      BPIL.PO_LINES_ALL PLA,
      BPIL.PO_HEADERS_ALL PHA,
      BPIL.PO_VENDORS PV,
      BPIL.PO_LINE_LOCATIONS_ALL PLLA,
      (
      SELECT NAME DEPO_NAME
      ,LOCATION_ID
      FROM HR_ALL_ORGANIZATION_UNITS
      WHERE ATTRIBUTE1 = 'FACTORY'
      ) DEPO,
      (
      SELECT JPT.PO_HEADER_ID
      ,PHA.SEGMENT1
      ,PLLA.LINE_LOCATION_ID
      ,SUM(TAX_AMOUNT) TAX_AMOUNT
      FROM BPIL.JAI_PO_TAXES JPT
      ,BPIL.PO_HEADERS_ALL PHA
      ,BPIL.PO_LINE_LOCATIONS_ALL PLLA
      WHERE JPT.PO_HEADER_ID = PHA.PO_HEADER_ID
      AND PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID
      AND PLLA.LINE_LOCATION_ID = JPT.LINE_LOCATION_ID
      AND TO_NUMBER(TO_CHAR(PHA.CREATION_DATE,'YYYYMMDD')) = 20111201
      --AND TO_NUMBER(TO_CHAR(PHA.CREATION_DATE,'YYYYMMDD')) <= 20111201
      AND PLLA.CALCULATE_TAX_FLAG = 'Y'
      GROUP BY JPT.PO_HEADER_ID
      ,PHA.SEGMENT1
      ,PLLA.LINE_LOCATION_ID
      ORDER BY 2
      ) TAX,
      *(*
      SELECT DISTINCT RSH.RECEIPT_NUM RECEIPT_NUM,
      RCT.PO_LINE_ID
      FROM RCV_SHIPMENT_HEADERS RSH,
      RCV_SHIPMENT_LINES RSL,
      RCV_TRANSACTIONS RCT,
      PO_HEADERS_ALL PHA,
      PO_LINES_ALL POL
      WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
      AND RSL.PO_HEADER_ID = RCT.PO_HEADER_ID
      AND RSL.PO_LINE_ID   = RCT.PO_LINE_ID
      AND RCT.PO_HEADER_ID = PHA.PO_HEADER_ID
      AND RCT.PO_LINE_ID   = POL.PO_LINE_ID
      AND PHA.PO_HEADER_ID = POL.PO_HEADER_ID
      AND RCT.TRANSACTION_TYPE = 'RECEIVE'
      *) CHALAN*
      WHERE MSIB.ORGANIZATION_ID = '102'
      AND MSIB.SEGMENT1 LIKE 'R%'
      AND PLA.ITEM_ID = MSIB.INVENTORY_ITEM_ID
      AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
      AND PV.VENDOR_ID = PHA.VENDOR_ID
      AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
      AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
      AND PHA.PO_HEADER_ID = TAX.PO_HEADER_ID(+)
      AND PHA.SEGMENT1 = TAX.SEGMENT1(+)
      AND PHA.ATTRIBUTE2 = 'RM'
      AND DEPO.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID
      AND TO_NUMBER(TO_CHAR(PLA.CREATION_DATE,'YYYYMMDD')) = 20111201
      --AND TO_NUMBER(TO_CHAR(PLA.CREATION_DATE,'YYYYMMDD')) <= 20111201
      AND PLA.PO_LINE_ID                  = CHALAN.PO_LINE_ID;

      The whole query is working correctly but the receipt part when implemented is giving unwanted result.
      Please throw some light against this.

      Edited by: Ron on Dec 28, 2011 6:36 PM
        • 1. Re: Receipt_no for PO created ** Help Required **
          HrishikeshJ
          Hi,

          First you have used toomany joins that are no exactly helping, following should be sufficient.
          (
          SELECT DISTINCT RSH.RECEIPT_NUM RECEIPT_NUM, RSL.PO_LINE_ID
          FROM RCV_SHIPMENT_HEADERS RSH,
          RCV_SHIPMENT_LINES RSL
          WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
          ) CHALAN


          But i guess, the problem u are facing is due to the fact that you are not linking Receipt number to the quantities correctly.
          E.g. you are taking quantities from PLLA but multiple receipts can account for that. so eventually whenever you have multiple receipts,
          you will end up in getting duplicate rows.

          Hrishikesh