1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL query to find the non moving inventory

Discussion in 'SQL PL/SQL' started by yogeshyl, Feb 11, 2009.

  1. yogeshyl

    yogeshyl Guest

    I have written a query to calculate the stock for a period between two dates.
    Now i want to find the non moving / slow moving inventory . I want the products / inventory that has not moved from last 30 days, 60 days, 150 days, 180 days etc.

    The user will enter the slab i.e days from which the products has not moved.

    How can i modify the query for the same..

    Yogesh
    Code (Text):
    SELECT
      F_ITEM_CODE, F_ITEM_NAME,
      OPSTK, INWARD, OUTWARD, NVL(OPSTK,0)+NVL(INWARD,0)-NVL(OUTWARD,0) CLSTK,
      OPVAL, IN_VAL, OUT_VAL, NVL(OPVAL,0)+NVL(IN_VAL,0)-NVL(OUT_VAL,0) CL_VAL
    FROM
    (
      SELECT
        ITEM_CODE F_ITEM_CODE,
        ITEM_NAME F_ITEM_NAME
      FROM  OM_ITEM
      WHERE ITEM_CODE LIKE 'F%'
    ),
    (
      SELECT
        SL_ITEM_CODE OP_ITEM_CODE,
        SUM(DECODE(SL_RCVD_ISSD,'R',1,'I',-1)*SL_QTY_BU) OPSTK,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'R',1,'I',-1)*SL_VAL_1),2) OPVAL
      FROM  OS_STK_LEDGER
      WHERE SL_DT <  TO_DATE(:DATE1,'DD-MM-YYYY')
      AND   SL_ITEM_CODE LIKE 'F%'
      GROUP BY SL_ITEM_CODE  
    ),
    (
      SELECT
        SL_ITEM_CODE TX_ITEM_CODE,
        SUM(DECODE(SL_RCVD_ISSD,'R',1)*SL_QTY_BU) INWARD,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'R',1)*SL_VAL_1),2) IN_VAL,
        SUM(DECODE(SL_RCVD_ISSD,'I',1)*SL_QTY_BU) OUTWARD,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'I',1)*SL_VAL_1),2) OUT_VAL
      FROM  OS_STK_LEDGER
      WHERE SL_DT BETWEEN TO_DATE(:DATE1,'DD-MM-YYYY') AND TO_DATE(:DATE2,'DD-MM-YYYY')
      AND   SL_ITEM_CODE LIKE 'F%'
      GROUP BY SL_ITEM_CODE
    )
    WHERE F_ITEM_CODE = OP_ITEM_CODE(+)
    AND   F_ITEM_CODE = TX_ITEM_CODE(+)
    AND   (NVL(OPSTK,0)+NVL(INWARD,0)+NVL(OUTWARD,0)) > 0
    ORDER BY F_ITEM_NAME
     
     

    Attached Files:

  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is always advised to provide the testy case for generating the issue along with create table and insert table scripts.

    Anyway , what do u mean by Non moving Item ? OUTWARD = 0 during the input date range ? Then check for that range.

    I guess your present query may give the stock details when there are stock movement prior to start date (DATE1) and no movements within the date ranges. Its because of Outer join effect.

    Also change the line

    Code (Text):

     WHERE SL_DT BETWEEN TO_DATE(:DATE1,'DD-MM-YYYY') AND TO_DATE(:DATE2,'DD-MM-YYYY')  
     
    to
    Code (Text):

      WHERE TRUNC(SL_DT BETWEEN) TO_DATE(:DATE1,'DD-MM-YYYY') AND TO_DATE(:DATE2,'DD-MM-YYYY')
     
    to get the actual result in case SL_DT stores time component in application .
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Better you check for the condition (NVL(INWARD,0)+NVL(OUTWARD,0)) = 0 and OPSTK >0 as below,

    Code (Text):

    SELECT
      F_ITEM_CODE, F_ITEM_NAME,
      OPSTK, INWARD, OUTWARD, NVL(OPSTK,0)+NVL(INWARD,0)-NVL(OUTWARD,0) CLSTK,
      OPVAL, IN_VAL, OUT_VAL, NVL(OPVAL,0)+NVL(IN_VAL,0)-NVL(OUT_VAL,0) CL_VAL
    FROM
    (
      SELECT
        ITEM_CODE F_ITEM_CODE,
        ITEM_NAME F_ITEM_NAME
      FROM  OM_ITEM
      WHERE ITEM_CODE LIKE 'F%'
    ),
    (
      SELECT
        SL_ITEM_CODE OP_ITEM_CODE,
        SUM(DECODE(SL_RCVD_ISSD,'R',1,'I',-1)*SL_QTY_BU) OPSTK,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'R',1,'I',-1)*SL_VAL_1),2) OPVAL
      FROM  OS_STK_LEDGER
      WHERE SL_DT <  TO_DATE(:DATE1,'DD-MM-YYYY')
      AND   SL_ITEM_CODE LIKE 'F%'
      GROUP BY SL_ITEM_CODE  
    ),
    (
      SELECT
        SL_ITEM_CODE TX_ITEM_CODE,
        SUM(DECODE(SL_RCVD_ISSD,'R',1)*SL_QTY_BU) INWARD,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'R',1)*SL_VAL_1),2) IN_VAL,
        SUM(DECODE(SL_RCVD_ISSD,'I',1)*SL_QTY_BU) OUTWARD,
        ROUND(SUM(DECODE(SL_RCVD_ISSD,'I',1)*SL_VAL_1),2) OUT_VAL
      FROM  OS_STK_LEDGER
      WHERE TRUNC(SL_DT) BETWEEN TO_DATE(:DATE1,'DD-MM-YYYY') AND TO_DATE(:DATE2,'DD-MM-YYYY')
      AND   SL_ITEM_CODE LIKE 'F%'
      GROUP BY SL_ITEM_CODE
    )
    WHERE F_ITEM_CODE = OP_ITEM_CODE(+)
    AND   F_ITEM_CODE = TX_ITEM_CODE(+)
    AND   NVL(INWARD,0)+NVL(OUTWARD,0) = 0
    AND   NVL(OPSTK,0) >0 -- If needed
    ORDER BY F_ITEM_NAME
     

    NOTE: There is an option to merge two OS_STK_LEDGER into a single one . Its slightly tricky . try it out if you are interested.