SQL & PL/SQL

Dear All,
I have been thinking for sometime if there would be a another (replaced better with another since better is debatable ;) ) way of doing this.
Sample data

CREATE TABLE pro (tran_effect VARCHAR2(1),qty NUMBER,VAL number,tran_date DATE)
ALTER TABLE pro ADD (pro_id VARCHAR(10))

INSERT INTO PRO VALUES ('R', 10, 10, SYSDATE - 10, 'A');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 9, 'A');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 8, 'A');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 7, 'A');


INSERT INTO PRO VALUES ('R', 10, 10, SYSDATE - 10, 'B');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 9, 'B');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 8, 'B');
INSERT INTO PRO VALUES ('S', 2, 2, SYSDATE - 7, 'B');

Background:
Tran_effect signifies if stock is brought IN( eg: IMPORT) depicted by 'R' (Recieved) or Sent out (eg: Invoiced) depicted by 'S'.
At any given date i need to find what was the opening stock and value on that date and what is the current stock and value.
Based on above data:
If i have to find the stock position as on 19-nov-2009 i should get

PRO_ID	OP_STK	OP_VAL	CURR_STK	CURR_VAL
A	10	10	  4	          4
B	10	10	  4	          4

Logic is I imported stock on 18th, no sales before 19th hence opening is 10 pcs for both the products however as of date i have current stock as 4 since 6 pcs are already sold.
If i have to find the stock position as on 20-nov-2009 i should get

PRO_ID	OP_STK	OP_VAL	CURR_STK	CURR_VAL
A	8	8	  4	          4
B	8	8	  4	          4

This is the query i came up with

SELECT PRO_ID,
       SUM(OP_STK) OP_STK,
       SUM(OP_VAL) OP_VAL,
       SUM(CURR_STK) CURR_STK,
       SUM(CURR_VAL) CURR_VAL
  FROM (SELECT PRO_ID,
               CASE
                 WHEN TRAN_DATE < &INPUT_DATE THEN
                  DECODE(TRAN_EFFECT, 'R', QTY, 'S', -QTY)
                 ELSE
                  0
               END OP_STK,
               
               DECODE(TRAN_EFFECT, 'R', QTY, 'S', -QTY) CURR_STK,
               CASE
                 WHEN TRAN_DATE < &INPUT_DATE THEN
                  DECODE(TRAN_EFFECT, 'R', VAL, 'S', -VAL)
                 ELSE
                  0
               END OP_VAL,
               
               DECODE(TRAN_EFFECT, 'R', VAL, 'S', -VAL) CURR_VAL
          FROM PRO)
 GROUP BY PRO_ID

Also,
Tran_date cannot be greater than current calendar date.

Looking forward for all your replies.

Cheers!!!
Bhushan

Edited by: Formating output