Home » SQL & PL/SQL » SQL & PL/SQL » Help! How to calculate the balance daily without manually key in Date (OS platform: Window 2000, version Oracle SQL Developer 1.5.3)
Help! How to calculate the balance daily without manually key in Date [message #408150] |
Sun, 14 June 2009 22:05  |
jeat
Messages: 1 Registered: June 2009
|
Junior Member |
|
|
Hi expert, i need to keep update the inventory balance everyday, if database doesn't exist the record it will insert a new. My problem now is, how do i check yesterday balance n carry forward to today and bring today balance to tomorrow balance without manually insert dInDate date to calculate specific date? Can provide some sort of logic? Here is my stored procedure:
PROCEDURE Inventory_Calculation (dInDate DATE, vInsertFlag VARCHAR2) IS
rDayBeforeInvRec Wste_Waste_Inventory%ROWTYPE;
rInDateInvRec Wste_Waste_Inventory%ROWTYPE;
fInDateReceiveQty FLOAT;
fInDateShipoutQty FLOAT;
fInDateOnHandQty FLOAT;
fInDateOpeningQty FLOAT;
nComponentId NUMBER(25);
vCompDept VARCHAR2(20);
vCompName VARCHAR2(80);
vWasteType VARCHAR2(2);
nGroupId NUMBER(25);
vComponentName VARCHAR2(80);
CURSOR Day_Before_Fail_Inv_Bal IS
SELECT * FROM wste_waste_inventory
WHERE wwin_date = dInDate -1;
CURSOR InDate_Rec_Exist IS
SELECT * FROM wste_waste_inventory
WHERE wwin_date = dInDate
AND wwin_wwco_id = nComponentId
AND wwin_comp_dept = vCompDept
AND wwin_waste_type = vWasteType
AND wwin_wwgr_id = nGroupId
AND wwin_component_name = vComponentName;
BEGIN
OPEN Day_Before_Fail_Inv_Bal;
LOOP
FETCH Day_Before_Fail_Inv_Bal INTO rDayBeforeInvRec;
nComponentId := rDayBeforeInvRec.wwin_wwco_id;
vCompDept := rDayBeforeInvRec.wwin_comp_dept;
vCompName := rDayBeforeInvRec.wwin_comp_name;
vWasteType := rDayBeforeInvRec.wwin_waste_type;
nGroupId := rDayBeforeInvRec.wwin_wwgr_id;
vComponentName := rDayBeforeInvRec.wwin_component_name;
OPEN InDate_Rec_Exist;
FETCH InDate_Rec_Exist INTO rInDateInvRec;
IF InDate_Rec_Exist%FOUND THEN
fInDateOpeningQty := rDayBeforeInvRec.wwin_onhand;
fInDateOnHandQty := rDayBeforeInvRec.wwin_onhand - rInDateInvRec.wwin_ship_out + rInDateInvRec.wwin_receive;
UPDATE wste_waste_inventory SET
WWIN_OPENING = fInDateOpeningQty,
WWIN_ONHAND = fInDateOnHandQty
WHERE wwin_date = dInDate
AND wwin_wwco_id = nComponentId
AND wwin_comp_dept = vCompDept
AND wwin_waste_type = vWasteType
AND wwin_wwgr_id = nGroupId
AND wwin_component_name = vComponentName;
ELSE
--IF vInsertFlag = 'Yes' THEN
fInDateOpeningQty := rDayBeforeInvRec.wwin_onhand;
fInDateOnHandQty := rDayBeforeInvRec.wwin_onhand;
INSERT INTO wste_waste_inventory (
WWIN_ID,
WWIN_WWCO_ID,
WWIN_RECEIVE,
WWIN_SHIP_OUT,
WWIN_ONHAND,
WWIN_OPENING,
WWIN_DATE,
WWIN_COMP_DEPT,
WWIN_COMP_NAME,
WWIN_WASTE_TYPE,
WWIN_WWGR_ID,
WWIN_COMPONENT_NAME )
VALUES (
SAPP_SEQ.nextval,
nComponentId,
0,
0,
fInDateOpeningQty,
fInDateOnHandQty,
TO_DATE(dInDate, 'DD-MON-YY'),
vCompDept,
vCompName,
vWasteType,
nGroupId,
vComponentName );
--END IF;
END IF;
CLOSE InDate_Rec_Exist;
EXIT WHEN Day_Before_Fail_Inv_Bal%NOTFOUND;
END LOOP;
CLOSE Day_Before_Fail_Inv_Bal;
COMMIT;
END Inventory_Calculation;
|
|
|
Re: Help! How to calculate the balance daily without manually key in Date [message #408152 is a reply to message #408150] |
Sun, 14 June 2009 22:13   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
PROCEDURE Inventory_calculation
(dindate DATE,
vinsertflag VARCHAR2)
IS
rdaybeforeinvrec wste_waste_inventory%ROWTYPE;
rindateinvrec wste_waste_inventory%ROWTYPE;
findatereceiveqty FLOAT;
findateshipoutqty FLOAT;
findateonhandqty FLOAT;
findateopeningqty FLOAT;
ncomponentid NUMBER(25);
vcompdept VARCHAR2(20);
vcompname VARCHAR2(80);
vwastetype VARCHAR2(2);
ngroupid NUMBER(25);
vcomponentname VARCHAR2(80);
CURSOR day_before_fail_inv_bal IS
SELECT *
FROM wste_waste_inventory
WHERE wwin_date = dindate - 1;
CURSOR indate_rec_exist IS
SELECT *
FROM wste_waste_inventory
WHERE wwin_date = dindate
AND wwin_wwco_id = ncomponentid
AND wwin_comp_dept = vcompdept
AND wwin_waste_type = vwastetype
AND wwin_wwgr_id = ngroupid
AND wwin_component_name = vcomponentname;
BEGIN
OPEN day_before_fail_inv_bal;
LOOP
FETCH day_before_fail_inv_bal INTO rdaybeforeinvrec;
ncomponentid := rdaybeforeinvrec.wwin_wwco_id;
vcompdept := rdaybeforeinvrec.wwin_comp_dept;
vcompname := rdaybeforeinvrec.wwin_comp_name;
vwastetype := rdaybeforeinvrec.wwin_waste_type;
ngroupid := rdaybeforeinvrec.wwin_wwgr_id;
vcomponentname := rdaybeforeinvrec.wwin_component_name;
OPEN indate_rec_exist;
FETCH indate_rec_exist INTO rindateinvrec;
IF indate_rec_exist%FOUND THEN
findateopeningqty := rdaybeforeinvrec.wwin_onhand;
findateonhandqty := rdaybeforeinvrec.wwin_onhand - rindateinvrec.wwin_ship_out + rindateinvrec.wwin_receive;
UPDATE wste_waste_inventory
SET wwin_opening = findateopeningqty,
wwin_onhand = findateonhandqty
WHERE wwin_date = dindate
AND wwin_wwco_id = ncomponentid
AND wwin_comp_dept = vcompdept
AND wwin_waste_type = vwastetype
AND wwin_wwgr_id = ngroupid
AND wwin_component_name = vcomponentname;
ELSE
--IF vInsertFlag = 'Yes' THEN
findateopeningqty := rdaybeforeinvrec.wwin_onhand;
findateonhandqty := rdaybeforeinvrec.wwin_onhand;
INSERT INTO wste_waste_inventory
(wwin_id,
wwin_wwco_id,
wwin_receive,
wwin_ship_out,
wwin_onhand,
wwin_opening,
wwin_date,
wwin_comp_dept,
wwin_comp_name,
wwin_waste_type,
wwin_wwgr_id,
wwin_component_name)
VALUES (sapp_seq.nextval,
ncomponentid,
0,
0,
findateopeningqty,
findateonhandqty,
To_date(dindate,'DD-MON-YY'),
vcompdept,
vcompname,
vwastetype,
ngroupid,
vcomponentname);
--END IF;
END IF;
CLOSE indate_rec_exist;
EXIT WHEN day_before_fail_inv_bal%NOTFOUND;
END LOOP;
CLOSE day_before_fail_inv_bal;
COMMIT;
END inventory_calculation;
[Updated on: Sun, 14 June 2009 22:29] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 22:24:35 CST 2025
|