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 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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

Re: Help! How to calculate the balance daily without manually key in Date [message #408235 is a reply to message #408150] Mon, 15 June 2009 07:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're going to need to spend more time explaining what exactly the problem is, as I (and I suspect many others) lack the enthusiasm to wade through your code to try and work out what it is and isn't doing.
Previous Topic: Reading Excel File columns
Next Topic: Calling webservice and send request in plsql
Goto Forum:
  


Current Time: Sat Dec 10 06:52:36 CST 2016

Total time taken to generate the page: 0.10982 seconds