|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 14th, 2004, 03:34 AM
|
|||
|
|||
|
Fifo
Hi All
This Is my First Post I have two tables 1.purchase 2.sold ************************************************ PURCHASE TABLE ************************************************ SHARE CODE DATE OF PURCHASE QUANTITY RATE VALUE QUANTIY SOLD BALANCE STOCK 1 ATL 01-SEP-04 100 15.75 1575 2 BPL 02-SEP-04 800 20.00 16000 3 ATL 04-SEP-04 500 17.00 8500 4 ATL 06-SEP-04 750 20.00 15000 5 BPL 07-SEP-04 250 15.00 3750 6 ATL 07-SEP-04 100 15.00 1500 7 ATL 08-SEP-04 250 16.00 4000 8 BPL 09-SEP-04 300 17.00 5100 ************************************************ SOLD TABLE ********************************************* SNO SHARE CODE DATE OF SALE QUANTITY RATE VALUE COST PROFIT 1 ATL 02-SEP-04 150 16.00 800 2 ATL 06-SEP-04 500 15.00 7500 3 ATL 08-SEP-04 300 20.00 6000 4 BPL 07-SEP-04 300 25.00 7500 5 ATL 10-SEP-04 100 15.00 1500 6 ATL 08-SEP-04 250 16.00 4000 7 BPL 10-SEP-04 1000 18.00 18000 8 ATL 11-SEP-04 400 20.00 8000 Here EQUITY SHARES ARE BOUGHT AND SOLD IN LARGE VOLUMES DAILY,Using THE FIFO(FIRST IN FIRST OUT) METHOD TO DETERMINE THE ACTUAL PROFIT OR LOSS ON TRANSACTIONS. i have written a pl/sql block. DECLARE lsqty NUMBER(10,2):=0; lsrate NUMBER(10,2); lcost_price NUMBER(10,2); lselling_price NUMBER(10,2); lpurchase_qty NUMBER(10,2); lsold_qty NUMBER(10,2); lpurchase_rate NUMBER(10,2); lremaining_stock NUMBER(10,2); lprofit number(10,2); lvalue number(10,2); CURSOR r IS SELECT * FROM share_sld ORDER BY s_code,dos; rec r%ROWTYPE; CURSOR r1 IS SELECT * FROM share_bgt WHERE s_code=rec.s_code AND p_qty-qty_sold> 0 ORDER BY s_code,dop; rec1 r1%ROWTYPE; cannot_sell EXCEPTION; BEGIN OPEN r; LOOP FETCH r INTO rec; EXIT WHEN r%NOTFOUND; lsqty:=rec.s_qty; lsrate:=rec.rate; lcost_price:=0; lselling_price:=0; lvalue:=rec.value; OPEN r1; LOOP FETCH r1 INTO rec1; IF r1%NOTFOUND THEN RAISE cannot_sell; ELSE lsold_qty:=rec1.qty_sold; lpurchase_qty:=rec1.p_qty; lpurchase_rate:=rec1.rate; IF lsqty > (lpurchase_qty-lsold_qty) THEN lcost_price:=lcost_price+(lpurchase_qty-lsold_qty)*lpurchase_rate; lprofit:=lvalue-lcost_price; UPDATE share_bgt SET qty_sold=lpurchase_qty,remaining_stock=0 WHERE dop=rec1.dop AND s_code=rec1.s_code; UPDATE share_sld SET cost_price=lcost_price,profit=lprofit WHERE dos=rec.dos AND s_code=rec.s_code; lsqty:=lsqty-(lpurchase_qty-lsold_qty); ELSE lsold_qty:=lsold_qty+lsqty; lremaining_stock:=lpurchase_qty-lsold_qty; lcost_price:=lcost_price+(lsqty*lpurchase_rate); lprofit:=lvalue-lcost_price; UPDATE share_bgt SET qty_sold=lsold_qty,remaining_stock=lremaining_stock WHERE dop=rec1.dop AND s_code=rec1.s_code; UPDATE share_sld SET cost_price=lcost_price,profit=lprofit WHERE dos=rec.dos AND s_code=rec.s_code; lsqty:=0; END IF; IF lsqty=0 THEN CLOSE r1; EXIT; END IF; END IF; END LOOP; END LOOP; CLOSE r; EXCEPTION WHEN cannot_sell THEN RAISE_APPLICATION_ERROR(-20001,'CANNOT SELL SHARE FOR NOT PURCHASED'); END; here comes my problem. while entering the data user had made mistakes at three places. In sold table sno=3 qty should be 400 and rate should be 19.75 In purchase table sno=7 qty=150 16.50 these are the new values which i want to update and restart the fifo process from the point of update. i have tried this using a procedure passing parameters (purchase/sale,sno,qtyupdated, rate); if the first parameter is purchase then update should be done in purchase table for the same sno which doesnot change . Thanks In Advance.. Saritha.S
|
|
#2
October 14th, 2004, 09:27 AM
|
|||
|
|||
|
IF you edit your post above using [ c o d e ] [ / c o d e ] (no spaces )
around your data and around your PL/SQL, I could probably read it, and help you. I can't read what you posted.
|
|
#3
October 14th, 2004, 10:03 AM
|
|||
|
|||
|
Hi Jim
I have two tables 1.purchase 2.sold ************************************************ PURCHASE TABLE ************************************************ SHARE CODE DATE OF PURCHASE QUANTITY RATE VALUE QUANTIY SOLD BALANCE STOCK 1 ATL 01-SEP-04 100 15.75 1575 2 BPL 02-SEP-04 800 20.00 16000 3 ATL 04-SEP-04 500 17.00 8500 4 ATL 06-SEP-04 750 20.00 15000 5 BPL 07-SEP-04 250 15.00 3750 6 ATL 07-SEP-04 100 15.00 1500 7 ATL 08-SEP-04 250 16.00 4000 8 BPL 09-SEP-04 300 17.00 5100 ************************************************ SOLD TABLE ********************************************* SNO SHARE CODE DATE OF SALE QUANTITY RATE VALUE COST PROFIT 1 ATL 02-SEP-04 150 16.00 800 2 ATL 06-SEP-04 500 15.00 7500 3 ATL 08-SEP-04 300 20.00 6000 4 BPL 07-SEP-04 300 25.00 7500 5 ATL 10-SEP-04 100 15.00 1500 6 ATL 08-SEP-04 250 16.00 4000 7 BPL 10-SEP-04 1000 18.00 18000 8 ATL 11-SEP-04 400 20.00 8000 Here EQUITY SHARES ARE BOUGHT AND SOLD IN LARGE VOLUMES DAILY,Using THE FIFO(FIRST IN FIRST OUT) METHOD TO DETERMINE THE ACTUAL PROFIT OR LOSS ON TRANSACTIONS. i have written a pl/sql block. ******************************************* this is the pl/sql block which i have written for the fifo process ******************************************** DECLARE lsqty NUMBER(10,2):=0; lsrate NUMBER(10,2); lcost_price NUMBER(10,2); lselling_price NUMBER(10,2); lpurchase_qty NUMBER(10,2); lsold_qty NUMBER(10,2); lpurchase_rate NUMBER(10,2); lremaining_stock NUMBER(10,2); lprofit number(10,2); lvalue number(10,2); CURSOR r IS SELECT * FROM share_sld ORDER BY s_code,dos; rec r%ROWTYPE; CURSOR r1 IS SELECT * FROM share_bgt WHERE s_code=rec.s_code AND p_qty-qty_sold> 0 ORDER BY s_code,dop; rec1 r1%ROWTYPE; cannot_sell EXCEPTION; BEGIN OPEN r; LOOP FETCH r INTO rec; EXIT WHEN r%NOTFOUND; lsqty:=rec.s_qty; lsrate:=rec.rate; lcost_price:=0; lselling_price:=0; lvalue:=rec.value; OPEN r1; LOOP FETCH r1 INTO rec1; IF r1%NOTFOUND THEN RAISE cannot_sell; ELSE lsold_qty:=rec1.qty_sold; lpurchase_qty:=rec1.p_qty; lpurchase_rate:=rec1.rate; IF lsqty > (lpurchase_qty-lsold_qty) THEN lcost_price:=lcost_price+(lpurchase_qty-lsold_qty)*lpurchase_rate; lprofit:=lvalue-lcost_price; UPDATE share_bgt SET qty_sold=lpurchase_qty,remaining_stock=0 WHERE dop=rec1.dop AND s_code=rec1.s_code; UPDATE share_sld SET cost_price=lcost_price,profit=lprofit WHERE dos=rec.dos AND s_code=rec.s_code; lsqty:=lsqty-(lpurchase_qty-lsold_qty); ELSE lsold_qty:=lsold_qty+lsqty; lremaining_stock:=lpurchase_qty-lsold_qty; lcost_price:=lcost_price+(lsqty*lpurchase_rate); lprofit:=lvalue-lcost_price; UPDATE share_bgt SET qty_sold=lsold_qty,remaining_stock=lremaining_stock WHERE dop=rec1.dop AND s_code=rec1.s_code; UPDATE share_sld SET cost_price=lcost_price,profit=lprofit WHERE dos=rec.dos AND s_code=rec.s_code; lsqty:=0; END IF; IF lsqty=0 THEN CLOSE r1; EXIT; END IF; END IF; END LOOP; END LOOP; CLOSE r; EXCEPTION WHEN cannot_sell THEN RAISE_APPLICATION_ERROR(-20001,'CANNOT SELL SHARE FOR NOT PURCHASED'); END; ***************************************** here comes my problem. while entering the data user had made mistakes at three places. In sold table sno=3 qty should be 400 and rate should be 19.75 In purchase table sno=7 qty=150 16.50 these are the new values which i want to update and restart the fifo process from the point of update. i have tried this using a procedure passing parameters (purchase/sale,sno,qtyupdated, rate); if the first parameter is purchase then update should be done in purchase table for the same sno which doesnot change . hope this is clear Thanks In Advance.. Sarithas70@yahoo.com
|
|
#4
October 14th, 2004, 11:05 AM
|
|||
|
|||
|
Code:
************************************************
PURCHASE TABLE
************************************************
SHARE CODE DATE OF PURCHASE QUANTITY RATE VALUE QUANTIY SOLD BALANCE STOCK
1 ATL 01-SEP-04 100 15.75 1575
2 BPL 02-SEP-04 800 20.00 16000
3 ATL 04-SEP-04 500 17.00 8500
4 ATL 06-SEP-04 750 20.00 15000
5 BPL 07-SEP-04 250 15.00 3750
6 ATL 07-SEP-04 100 15.00 1500
7 ATL 08-SEP-04 250 16.00 4000
8 BPL 09-SEP-04 300 17.00 5100
************************************************
SOLD TABLE
*********************************************
SNO SHARE CODE DATE OF SALE QUANTITY RATE VALUE COST PROFIT
1 ATL 02-SEP-04 150 16.00 800
2 ATL 06-SEP-04 500 15.00 7500
3 ATL 08-SEP-04 300 20.00 6000
4 BPL 07-SEP-04 300 25.00 7500
5 ATL 10-SEP-04 100 15.00 1500
6 ATL 08-SEP-04 250 16.00 4000
7 BPL 10-SEP-04 1000 18.00 18000
8 ATL 11-SEP-04 400 20.00 8000
Code:
DECLARE
lsqty NUMBER(10,2):=0;
lsrate NUMBER(10,2);
lcost_price NUMBER(10,2);
lselling_price NUMBER(10,2);
lpurchase_qty NUMBER(10,2);
lsold_qty NUMBER(10,2);
lpurchase_rate NUMBER(10,2);
lremaining_stock NUMBER(10,2);
lprofit number(10,2);
lvalue number(10,2);
CURSOR r IS
SELECT *
FROM share_sld
ORDER BY s_code,dos;
rec r%ROWTYPE;
CURSOR r1 IS
SELECT *
FROM share_bgt
WHERE s_code=rec.s_code AND
p_qty-qty_sold> 0
ORDER BY s_code,dop;
rec1 r1%ROWTYPE;
cannot_sell EXCEPTION;
BEGIN
OPEN r;
LOOP
FETCH r INTO rec;
EXIT WHEN r%NOTFOUND;
lsqty:=rec.s_qty;
lsrate:=rec.rate;
lcost_price:=0;
lselling_price:=0;
lvalue:=rec.value;
OPEN r1;
LOOP
FETCH r1 INTO rec1;
IF r1%NOTFOUND
THEN
RAISE cannot_sell;
ELSE
lsold_qty:=rec1.qty_sold;
lpurchase_qty:=rec1.p_qty;
lpurchase_rate:=rec1.rate;
IF lsqty > (lpurchase_qty-lsold_qty)
THEN
lcost_price:=lcost_price+(lpurchase_qty-lsold_qty)*lpurchase_rate;
lprofit:=lvalue-lcost_price;
UPDATE share_bgt
SET
qty_sold=lpurchase_qty,remaining_stock=0
WHERE
dop=rec1.dop AND
s_code=rec1.s_code;
UPDATE share_sld
SET
cost_price=lcost_price,profit=lprofit
WHERE
dos=rec.dos AND
s_code=rec.s_code;
lsqty:=lsqty-(lpurchase_qty-lsold_qty);
ELSE
lsold_qty:=lsold_qty+lsqty;
lremaining_stock:=lpurchase_qty-lsold_qty;
lcost_price:=lcost_price+(lsqty*lpurchase_rate);
lprofit:=lvalue-lcost_price;
UPDATE share_bgt
SET
qty_sold=lsold_qty,
remaining_stock=lremaining_stock
WHERE
dop=rec1.dop AND
s_code=rec1.s_code;
UPDATE share_sld
SET
cost_price=lcost_price,
profit=lprofit
WHERE
dos=rec.dos AND
s_code=rec.s_code;
lsqty:=0;
END IF;
IF lsqty=0 THEN
CLOSE r1;
EXIT;
END IF;
END IF;
END LOOP;
END LOOP;
CLOSE r;
EXCEPTION
WHEN cannot_sell THEN
RAISE_APPLICATION_ERROR(-20001,'CANNOT SELL SHARE FOR NOT PURCHASED');
END;
|
|
#5
October 14th, 2004, 11:17 AM
|
|||
|
|||
|
IF you're doing this for a US company be aware of SOX regulations - you will have to document on paper why each record was updated BEFORE you update anything.
What I would do: Do not raise exceptions. Modfiy the PL/SQL script to identify bad tranactions ONLY. Dont' update anything. Call DBMS_OUTPUT to write out your exceptions, and keep on going. Update the transactions your script identifies with a straight SQL script, after you review what needs to be doine for each bad transaction. Don't assume there is only one. Then you can run your updating PL/SQL script without having to worry about those errors.
|
|
#6
October 14th, 2004, 11:20 AM
|
|||
|
|||
|
PS: I put code tags around your stuff - note how it is now readable.
Your PL/SQL 'format' technique is not wonderful. Even if this is a one-time script, I would try to write it with comments and with good indentation. Because. Next month the same thing will happen again and you will have to "decode" your code to make sense out of it - increasing the likelihood of you making errors.
|
|
#7
October 15th, 2004, 01:07 AM
|
|||
|
|||
|
Hi Jim
Thanks a Lot For Your Suggestions.As I am New To This Field I have Very Little Idea About All This Self Documentation.Here After I will Follow As per Your Advice. Regards Saritha
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Fifo |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
