stock evaluation problem

[tyb]

Dear Frends

i have a table of stock and i want to track the amount of the balance figure and i want to implement the FIFO (first in first out) method for stock evaluation.

in the table while making entries for issue and receipt i'm saving both the date and time.

in the receipts i may receive the same good at different times at different rates e.g.

Receipt
Sr# Date Qty Rate
1. 09-29-04 1,000 6.50
2. 09-30-04 1,500 8.00
3. 10-02-04 800 5.50

i have total 3,300 units of value 22,900/-

at the time of issue i can check if the balance qty is less than or equal to the last purchase
then i can just get the value of the issued items at the rate in the last purchase and the balance value by just subtracting the previous balance from the current balance.

and if the quantity in balance is greater than the last purchase then there is a share of the previous purchase that might be at some different rate.

plz advise how can i achieve this.

 

[SantaMufasa]

Tyb,

At first glance, it appears to me that the "procedural" aspects of a PL/SQL block would best solve your needs.

As a side note, Tek-Tipsters typically provide syntax help rather than logic help, since we are not privy to so many nuances of your business needs. So, although you have expressed well your business need, we would recommend your taking a stab at the code to do what you want. Then if you have trouble with the code, copy and paste it here and we can offer advice.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:28 (08Oct04) UTC (aka "GMT" and "Zulu"), 08:28 (08Oct04) Mountain Time)

 

[tyb]

dear frend
many thanks for ur reply , but the problem i'm facing is that i'll get it done upto the point where the balance of the item is less than or equal to the last purchase say

here is the pseudocode

select it_qty,rate
into a,b --variables of number type--
from receipt
where it_code=:issue.it_code;
if item.balance <= a)
then
:issue.rate := b;
else

HERE it needs to be in the loop where it goes to the one
step previous purchase, compares the balance with the
sum(purchase_qty) and if balance <= then repeat the upper lines else go to one step back again


NOW I'M NOT GETTING THE IDEA THAT HOW CAN I MOVE BACK STEP BY STEP.

Plz if u can help me

regards.

 

[SantaMufasa]

Tyb:

Presumptions:
1) When you refer to "if the quantity in balance is greater than the last purchase then there is a share of the previous purchase that might be at some different rate" and "it goes to the one step previous purchase", these statements imply LIFO (Last In/First Out) not FIFO.
2) I presume that your "IT_code" means "ticker_code". I did not see "IT_code" in your "receipts" table, however, so I added "IT_code" to "receipts".
3) Since you did not specify that successful requests should "deplete" stock-reciept records (as you would need to do for Securities-Lending applications), I did not include the necessary "UPDATE" statements to deplete available stock receipts as part of the function. You certainly could add code to deplete the stock reciepts for securities-lending applications.


Section 1 -- Here are sample data that I inserted into a sample "receipts" table for test purposes.

create table receipts (it_code varchar2(10), receipt_date date, qty number, rate number);

Table created.

insert into receipts values ('XYZ',to_date('09-29-04','mm-dd-rr'),1000,6.50);

1 row created.

insert into receipts values ('XYZ',to_date('09-30-04','mm-dd-rr'),1500,8.00);

1 row created.

insert into receipts values ('XYZ',to_date('10-02-04','mm-dd-rr'),800,5.50);

1 row created.

select * from receipts;

IT_CODE    RECEIPT_D        QTY       RATE
---------- --------- ---------- ----------
XYZ        29-SEP-04       1000        6.5
XYZ        30-SEP-04       1500          8
XYZ        02-OCT-04        800        5.5

3 rows selected.

Section 2 -- Here is code to handle FIFO, written as a function into which you pass "Request_Qty" (Note: If there are enough shares to meet the request, then the function returns the FIFO value of the shares; if there are not enough shares to meet the request, then the function returns NULL.):

CREATE OR REPLACE FUNCTION stock_valuation (ticker_code varchar2, request_qty number)
		RETURN number IS
	total_available		number;
	current_subtotal	number	:= 0;
	remaining_request	number;
	valuation		number	:= 0;
BEGIN
	SELECT SUM(qty) INTO total_available FROM receipts;
	IF total_available < request_qty THEN
		RETURN null;
	END IF;
	remaining_request := request_qty;
	FOR r IN (SELECT * FROM receipts
			WHERE ticker_code = it_code
			ORDER BY receipt_date asc) LOOP
			-- change "asc" to "desc", above, for LIFO
		IF remaining_request > r.qty then
			valuation := valuation + (r.qty * r.rate);
			remaining_request := remaining_request - r.qty;
		ELSE
			valuation := valuation + (remaining_request * r.rate);
			RETURN valuation;
		END IF;
	END LOOP;
END;
/

Function created.

Section 3 -- Here is a sample script named "Stock_requests.sql" that prompts for ticker code and share quantities to evaluate, then invokes the "stock_valuation" function:

accept ticker prompt "Enter the ticker code for the stock to evaluate: "
accept share_cnt prompt "Enter the number of &ticker shares to evaluate: "
col a heading "Stock valuation requests" format a90
select 'The FIFO valuation of &share_cnt shares of "&ticker" stock is '||
	nvl(to_char(stock_valuation(upper('&ticker'),'&share_cnt'))
		,'unavailable due to deficient shares')||'.' a
	from dual;

Section 4 -- Here are sample invocations of the script, "Stock_requests.sql":

****************************************************************************************
SQL> @stock_requests
Enter the ticker code for the stock to evaluate: xyz
Enter the number of xyz shares to evaluate: 3400

Stock valuation requests
----------------------------------------------------------------------------------------
The FIFO valuation of 3400 shares of "xyz stock" is unavailable due to deficient shares.

SQL> @stock_requests
Enter the ticker code for the stock to evaluate: xyz
Enter the number of xyz shares to evaluate: 3300

Stock valuation requests
----------------------------------------------------------
The FIFO valuation of 3300 shares of "xyz" stock is 22900.

SQL> @stock_requests
Enter the ticker code for the stock to evaluate: xyz
Enter the number of xyz shares to evaluate: 1200

Stock valuation requests
---------------------------------------------------------
The FIFO valuation of 1200 shares of "xyz" stock is 8100.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:52 (11Oct04) UTC (aka "GMT" and "Zulu"), 02:52 (11Oct04) Mountain Time)