Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

HOW TO USE LOOP UNTIL DISCARD VALUE (FQTY) IS ZERO

User_ZVGWKOct 27 2022 — edited Oct 27 2022

I want to discard stock of current quantity with multiple batches. The current quantity of every batch should minus from discard quantity until discard quantity is zero.

CREATE OR REPLACE PROCEDURE ADMIN.labconsumption (
   fmedid       VARCHAR2,
   fqty         NUMBER,
   fsubdeptid   VARCHAR2
)
IS
   str      VARCHAR2 (5000);
   exist    NUMBER (1);
   exist1   NUMBER (1);
   rqdate   VARCHAR2 (20);
   dt       VARCHAR2 (20);
BEGIN
   SELECT ADMIN.wvf_inv_isexistsdmeddtlsstd1 (fmedid, fsubdeptid)
     INTO exist
     FROM DUAL;

   SELECT ADMIN.wvf_inv_isexistsdmeddtlsstd2 (fmedid, fsubdeptid)
     INTO exist1
     FROM DUAL;

   IF exist = 1 AND exist1 = 0
   THEN
      UPDATE inventory.dmeddtls
         SET currqty =
                  (SELECT currqty
                     FROM inventory.dmeddtls d
                    WHERE medid = fmedid
                      AND subdeptid = fsubdeptid
                      AND currqty > 0
                      AND ROWID =
                             (SELECT MAX (ROWID)
                                FROM inventory.dmeddtls e
                               WHERE medid = fmedid
                                 AND subdeptid = fsubdeptid
                                 AND e.medid = d.medid))
                - fqty
       WHERE medid = fmedid
         AND subdeptid = fsubdeptid
         AND currqty != '0'
         AND currqty >= fqty;
   ELSE
      IF exist = 1 AND exist1 = 1
      THEN
        LOOP MERGE INTO INVENTORY.DMEDDTLS DTS USING (SELECT CURRQTY FROM (SELECT ROWID,CURRQTY
                     FROM inventory.dmeddtls d
                    WHERE medid = fmedid
                      AND subdeptid = fsubdeptid
                      AND currqty > 0 ORDER BY ROWID DESC) WHERE ROWNUM<2)
                      SRC 
                      ON (SRC.ROWID=DTS.ROWID)
                      WHEN MATCHED THEN
               UPDATE 
                  SET currqty =
                          SRC.CURRQTY  - fqty
                WHERE medid = fmedid
                  AND subdeptid = fsubdeptid
                  AND batchno =
                         (SELECT batchno
                            FROM inventory.dmeddtls d
                           WHERE medid = fmedid
                             AND subdeptid = fsubdeptid
                             AND currqty > 0
                             AND ROWID =
                                    (SELECT MAX (ROWID)
                                       FROM inventory.dmeddtls e
                                      WHERE medid = fmedid
                                        AND subdeptid = fsubdeptid
                                        AND e.medid = d.medid))
                  AND currqty > '0';
                EXIT WHEN SQL%ROWCOUNT = 0;
               END LOOP;
      END IF;
   END IF;
END;
/

this is my sample data discard quantity is 1.3. Now i want that loop first checks the 1st batch minus the value and goes to next one and so on. NOTE : I do not want to define the batch in query....

Comments
Post Details
Added on Oct 27 2022
5 comments
282 views