Stock Evaluation

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

 

[lava1178]

Hi tyb :
I would think your issues table would also be looking as receipts e.g : -Except of course date and time would be changed
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

Q: do you want to keep track of balance figure in a table or do you want to report that prints ISsues, Receipts and how is the total price of Receipts arriaved at.

E.g : Receipt
Date Issue-Qty ReceiptQty IssuePrice Balance Totalamt
09-01-04 1500 - 6.50 1500 -
09-15-04 1000 - 8.00 2500 -

09-29-04 - 1000 - 1500 1000@6.50
09-30-04 - 1000 - 500 500@6.50+
1000@8.00

In which case you can write a pl/sql package , perform you computations and create a temp. table for populating requd. fields and call in the report 'Before_Report Trigger'
and drop the table after your report has been successfully run in after_report trigger by again calling pl/sql procedure.


But in case you want to store this balance data permanently in database then you create another table Balance-Issue
with following columns:
a) Srno
b) Date of Issue
c) Balance Qty
d) Price

Populate this table after Receipts table has been populated. You would then need to keep this table always in sync whenever you update/delete the receipts table/issues table.

If you can be more specific about your requirement I may be able to help you. I understand by FIFO stock evaluation.

Regards
Lavanya