9 Replies Latest reply on Nov 20, 2013 7:15 AM by Priyasagi

    Stock Transfer between warehouses

    Adeel Qadir

      I want to create a form of Transfer stock one warehouse to another in Form 6i . I have two fields (1. From Warehouse , 2. To warehouse )

      after save the record from warehouse minus quantity but to warehouse is not + quantity.

       

      My trigger code is below.

      FWID is from warehouse ID

      TWID is to warehouse ID

       

      create or replace trigger STOCKTRNS_AFT_INS

        after insert on stock_transfer 

        for each row

      declare

        -- local variables here

        V_SDATE   DATE := :NEW.TRDATE;

        V_INSTOCK NUMBER;

        V_FWID    NUMBER := :NEW.FWID;

        V_TWID    NUMBER := :NEW.TWID;

       

      begin

       

       

        SELECT COUNT(*)

          INTO V_INSTOCK

          FROM STOCK S

         WHERE S.ITID = :NEW.ITID

           AND S.WID = :NEW.FWID;

       

        IF V_FWID IS NOT NULL THEN

       

          IF V_INSTOCK = 0 THEN

         

            RAISE_APPLICATION_ERROR(-20000,

                                    'Item record not found in stock for Transfer');

         

          ELSE

            -------- UPDATE THE RECORD.....

            UPDATE STOCK S

               SET S.QTY = S.QTY - :NEW.QTY

             WHERE S.ITID = :NEW.ITID

               AND S.WID = :NEW.FWID;

         

          END IF;

       

          update items i

             set i.itcrqty = itcrqty - :new.qty

           where i.itid = :new.itid;

       

       

          ELSIF V_TWID IS NOT NULL THEN

          ---------- RETURN CASE

          IF V_INSTOCK = 0 THEN

         

            ------- INSERT NEW RECORD....              

            INSERT INTO STOCK

              (ITID, WID, QTY, LASTINDATE, LASTINQTY)

            VALUES

              (:NEW.ITID, :NEW.TWID, :NEW.QTY, V_SDATE, :NEW.QTY);

         

          ELSE

            UPDATE STOCK S

               SET S.QTY = S.QTY + :NEW.QTY

             WHERE S.ITID = :NEW.ITID

               AND S.WID = :NEW.TWID;

          END IF;

       

          update items i

             set i.itcrqty = itcrqty + :new.qty

           where i.itid = :new.itid;

       

        END IF;

       

      end;

       

      Expert plz help me.

        • 1. Re: Stock Transfer between warehouses
          Priyasagi

          9fe8573d-b08b-477f-a600-380b99ca0767 wrote:

           

           

          create or replace trigger STOCKTRNS_AFT_INS

            after insert on stock_transfer

            for each row

          declare

            -- local variables here

            V_SDATE   DATE := :NEW.TRDATE;

            V_INSTOCK NUMBER;

            V_FWID    NUMBER := :NEW.FWID;

            V_TWID    NUMBER := :NEW.TWID;

           

          begin

           

           

            SELECT COUNT(*)  SUM(S.QTY)

              INTO V_INSTOCK

              FROM STOCK S

             WHERE S.ITID = :NEW.ITID

               AND S.WID = :NEW.FWID;

           

            IF V_FWID IS NOT NULL AND V_TWID IS NOT NULL THEN

           

             IF V_INSTOCK = 0 THEN  IF V_INSTOCK < :NEW.QTY THEN

           

              INSERT INTO STOCK

                  (ITID, WID, QTY, LASTINDATE, LASTINQTY)

                VALUES

                  (:NEW.ITID, :NEW.TWID, :NEW.QTY, V_SDATE, :NEW.QTY);

           

                RAISE_APPLICATION_ERROR(-20000,

                                        'Item record not found in stock for Transfer'); 'Due to Low Stock New Quantity Inserted');

           

              ELSE

                -------- UPDATE THE RECORD.....

                UPDATE STOCK S

                   SET S.QTY = S.QTY - :NEW.QTY

                 WHERE S.ITID = :NEW.ITID

                   AND S.WID = :NEW.FWID;

           

              END IF;

           

              update items i

                 set i.itcrqty = itcrqty - :new.qty

               where i.itid = :new.itid;

           

           

             ELSIF V_TWID IS NOT NULL THEN

              ---------- RETURN CASE

              IF V_INSTOCK = 0 THEN

           

                ------- INSERT NEW RECORD....            

                INSERT INTO STOCK

                  (ITID, WID, QTY, LASTINDATE, LASTINQTY)

                VALUES

                  (:NEW.ITID, :NEW.TWID, :NEW.QTY, V_SDATE, :NEW.QTY);

           

              ELSE

                UPDATE STOCK S

                   SET S.QTY = S.QTY + :NEW.QTY

                 WHERE S.ITID = :NEW.ITID

                   AND S.WID = :NEW.TWID;

             END IF;

           

              update items i

                 set i.itcrqty = itcrqty + :new.qty

               where i.itid = :new.itid;


          END IF;

           

          ELSE

          RAISE_APPLICATION_ERROR(-20000,'V_FWID AND V_TWID MUST BE ENTERED..');

          END IF;

           

          end;

           

          Expert plz help me.

          Do the above corrections and let me know the result.

          1 person found this helpful
          • 2. Re: Stock Transfer between warehouses
            Adeel Qadir

            Thanks for your quick reply 

             

            Update case is working perfectly, but insert case is not working.

            • 3. Re: Stock Transfer between warehouses
              Priyasagi

              If stock available then you are updating both the side.  Its working perfectly. ok.

              If low stock what do you like to do?

               

              In my previous post simply inserting one record to stock with new quantity. And displaying the message low stock.

              So you please explain your problem little more clear.

              • 4. Re: Stock Transfer between warehouses
                Adeel Qadir

                i want to do this

                Suppose i have item name Pepsi Cola 10 pcs in Home warehouse, now i want to transfer pepsi cola 5 pcs to shop, Now i will select Home warehouse in Fwid and Shop warehouse in Twid. if Twid  have already Pepsi Cola record then Transaction successfully completed and -5 pcs in Home warehouse and +5 pcs into Shop warehouse

                 

                but

                 

                if Twid does not have already record of Pepsi Cola then i Want to Insert new Record of Pepsi Cola

                 

                This case is not working. If both warehouses have pepsi cola record then transaction success and if twid does not have record trigger is not inserting a new record of pepsi cola.

                 

                Sorry for my poor English.

                • 5. Re: Stock Transfer between warehouses
                  Priyasagi

                  Ok, copy the following code to your form and let me know the result:

                   

                  create or replace trigger STOCKTRNS_AFT_INS

                    after insert on stock_transfer 

                    for each row

                  declare

                    -- local variables here

                    V_SDATE   DATE := :NEW.TRDATE;

                    V_FINSTOCK NUMBER;

                    V_TINSTOCK NUMBER;

                    V_FWID    NUMBER := :NEW.FWID;

                    V_TWID    NUMBER := :NEW.TWID;

                   

                  BEGIN

                   

                  SELECT SUM(DECODE(S.WID,:NEW.FWID,S.QTY,0)),SUM(DECODE(S.WID,:NEW.TWID,S.QTY,0))
                      INTO V_FINSTOCK,V_TINSTOCK
                      FROM STOCK S
                      WHERE S.ITID = :NEW.ITID
                      AND S.WID IN (:NEW.FWID,:NEW.TWID);

                   

                  IF V_FWID IS NOT NULL AND V_TWID IS NOT NULL THEN

                   

                     IF V_FINSTOCK < :NEW.QTY THEN

                           INSERT INTO STOCK
                           (ITID, WID, QTY, LASTINDATE, LASTINQTY)
                           VALUES
                           (:NEW.ITID, :NEW.FWID, :NEW.QTY, V_SDATE, :NEW.QTY);

                           RAISE_APPLICATION_ERROR(-20000,'Due to Low Stock New Quantity Inserted into home warehouse');
                     ELSE
                           UPDATE STOCK S
                           SET S.QTY = S.QTY - :NEW.QTY
                           WHERE S.ITID = :NEW.ITID
                           AND S.WID = :NEW.FWID;

                   

                           update items i
                           set i.itcrqty = itcrqty - :new.qty
                           where i.itid = :new.itid;
                     END IF;

                     IF V_TINSTOCK < :NEW.QTY THEN
                           INSERT INTO STOCK
                           (ITID, WID, QTY, LASTINDATE, LASTINQTY)
                           VALUES
                           (:NEW.ITID, :NEW.TWID, :NEW.QTY, V_SDATE, :NEW.QTY);

                           RAISE_APPLICATION_ERROR(-20000,'Due to Low Stock New Quantity Inserted into shop warehouse');
                     ELSE

                           UPDATE STOCK S
                           SET S.QTY = S.QTY + :NEW.QTY
                           WHERE S.ITID = :NEW.ITID
                           AND S.WID = :NEW.TWID;

                          

                           update items i
                           set i.itcrqty = itcrqty + :new.qty
                           where i.itid = :new.itid;

                     END IF;

                  ELSE
                     RAISE_APPLICATION_ERROR(-20000,'V_FWID AND V_TWID MUST BE ENTERED..');
                  END IF;


                  END;

                  1 person found this helpful
                  • 6. Re: Stock Transfer between warehouses
                    Priyasagi

                    If you dont like to add insufficient quantity to your home warehouse then the following code is efficient:


                    create or replace trigger STOCKTRNS_AFT_INS

                      after insert on stock_transfer 

                      for each row

                    declare

                      -- local variables here

                      V_SDATE   DATE := :NEW.TRDATE;

                      V_FINSTOCK NUMBER;

                      V_TINSTOCK NUMBER;

                      V_FWID    NUMBER := :NEW.FWID;

                      V_TWID    NUMBER := :NEW.TWID;

                     

                    BEGIN

                     

                    SELECT SUM(DECODE(S.WID,:NEW.FWID,S.QTY,0)),SUM(DECODE(S.WID,:NEW.TWID,S.QTY,0))
                        INTO V_FINSTOCK,V_TINSTOCK
                        FROM STOCK S
                        WHERE S.ITID = :NEW.ITID
                        AND S.WID IN (:NEW.FWID,:NEW.TWID);

                     

                    IF V_FWID IS NOT NULL AND V_TWID IS NOT NULL THEN

                     

                       IF V_FINSTOCK < :NEW.QTY THEN
                             RAISE_APPLICATION_ERROR(-20000,'Low Stock available Quantity: '||to_char(v_finstock));
                       ELSE
                             UPDATE STOCK S
                             SET S.QTY = S.QTY - :NEW.QTY
                             WHERE S.ITID = :NEW.ITID
                             AND S.WID = :NEW.FWID;

                     

                             update items i
                             set i.itcrqty = itcrqty - :new.qty
                             where i.itid = :new.itid;
                       END IF;

                       IF NVL(V_TINSTOCK,0) = 0 THEN
                             INSERT INTO STOCK
                             (ITID, WID, QTY, LASTINDATE, LASTINQTY)
                             VALUES
                             (:NEW.ITID, :NEW.TWID, :NEW.QTY, V_SDATE, :NEW.QTY);

                             RAISE_APPLICATION_ERROR(-20000,'New Quantity Inserted into shop warehouse');
                       ELSE

                             UPDATE STOCK S
                             SET S.QTY = S.QTY + :NEW.QTY
                             WHERE S.ITID = :NEW.ITID
                             AND S.WID = :NEW.TWID;

                            

                             update items i
                             set i.itcrqty = itcrqty + :new.qty
                             where i.itid = :new.itid;

                       END IF;

                    ELSE
                       RAISE_APPLICATION_ERROR(-20000,'V_FWID AND V_TWID MUST BE ENTERED..');
                    END IF;


                    END;

                     

                    I hope this is your absolute requirement.

                    • 7. Re: Stock Transfer between warehouses
                      Adeel Qadir

                      Thank you soo much my dear and respected Priyasagi

                       

                      Your trigger is working fine and perfect.

                      • 8. Re: Stock Transfer between warehouses
                        Adeel Qadir

                        I need More help from you.

                         

                        i want to show Calendar on my form like below picture

                         

                         

                        [IMG]http://i39.tinypic.com/2ziubmp.jpg[/IMG]

                         

                         

                         

                         

                         

                        This is Calendar Object

                        When i run the form ocx is show blank

                        please tell me its trigger of when new form instance to display this calendar

                        • 9. Re: Stock Transfer between warehouses
                          Priyasagi

                          Hi,

                           

                          This thread is closed as answered, so you have to create a new thread with the new title 'Create Calendar on Forms'.

                          This is more help full to all volunteers of forum to give you proper solution.