SP to calc shipments vs receipts

I have an inventory transaction table with several fields,
specifically:
part_no
trans_type
trans_qty

part_no | trans_type | trans_qty
abc        REC    5000    (receipt)
abc        REC    400    (receipt)
abc        SHP    1000    (shipment)
abc        ALL    1000    (allocated)

Looking for the best way to show following totals with SQL

on_hand    |    allocated    | available
3400        1000                 4400

Thinking of writing a stored procedure that has the aggregate queries,
and returns the values defined above for this example.

Is this a recommended way?

B Stern