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....