First, regarding the stored procedure recommendation, it depends on what you are trying to do. The decision to go with a stored procedure vs a view depends on how this fits into the rest of your application.
Here is what I would do for the SQL though:
WITH base_agg AS (
select part_no,
sum(cast when trans_type='REC' then trans_qty else 0) as "received",
sum(cast when trans_type='ALL' then trans_qty else 0) as "allocated",
sum(cast when trans_type='SHP' then trans_qty else 0) as "shipped"
from inventory_transaction_table
group by part_no
)
SELECT shipped, allocated, received - allocated - shipped as on_hand from base_agg;
