SQL & PL/SQL

Hello,

Hope you all are at the best of your health.

I am working in Microsoft Windows environment and installed database is Oracle 11gR2.

I have following two table with sample data.

CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype VARCHAR2(15) );

and sample data is following.

INSERT INTO stock_master  VALUES (1,TO_DATE( '25-JAN-19','DD-MON-YY'), 'PURCHASE’ );

INSERT INTO stock_master  VALUES (2, TO_DATE('26-JAN-19','DD-MON-YY'), 'PURCHASE’ );

INSERT INTO stock_master  VALUES (3, TO_DATE('26-JAN-19','DD-MON-YY'), 'SALE’ );

INSERT INTO stock_master  VALUES (4, TO_DATE('27-JAN-19','DD-MON-YY'), 'SALE’ );

INSERT INTO stock_master  VALUES (5, TO_DATE('28-JAN-19','DD-MON-YY'), 'PURCHASE’ );

INSERT INTO stock_master  VALUES (6, TO_DATE('28-JAN-19','DD-MON-YY'), 'SALE’ );

CREATE TABLE stock_detail ( vd_no INTEGER, vno INTEGER, item_code VARCHAR2(8), qty NUMBER, rate NUMBER);

INSERT INTO stock_detail VALUES (1, 1, '001001’, 10, 100 );

INSERT INTO stock_detail VALUES (2, 2, '001001’, 50, 150 );

INSERT INTO stock_detail VALUES (3, 3, '001001’, 15, 160 );

INSERT INTO stock_detail VALUES (4, 4, '001001’, 5, 160 );

INSERT INTO stock_detail VALUES (5, 5, '001001’, 10, 165 );

INSERT INTO stock_detail VALUES (6, 6, '001001’, 50, 170 );

My requirement is to create SQL from above two tables that will show date wise inventory with weighted average cost of item_code as follows: -

           Formula for calculation of AMOUNT column will be as follows: -

         amount = qty * rate

In case of first-time purchase, calculation will be as follows: -

qty_in_hand = qty

avg_rate = rate

avg_amount = amount

           
         Subsequently in case of PURCHASES, QTY_IN_HAND and AVG_AMOUNT will be calculated by adding newly purchased qty and amount in the existing balance like follows:-

qty_in_hand = qty + qty_in_hand (previous row qty_in_hand)

avg_amount = (qty*rate) + avg_amount   (previous row avg_amount)

avg_rate = avg_amount / qty_in_hand

and in case of SALE, AVG_RATE will be simply previous row AVG_RATE but QTY_IN_HAND will be calculated by subtracting selling qty

qty_in_hand = qty_in_hand – qty       (Subtract qty from previous row qty_in_hand)

and AVERAGE_AMOUNT will be reduced by subtracting avg_amount from (qty * avg_rate) as follows:-

avg_amount = avg_amount - (qty * avg_rate)        (previous row avg_amount)