create or replace view v_amountofpackingnotes as
select /*view v_amountofpackingnotes  version 0.4*/
 pk.deliverydate, max(sa.code) salesarea, c.customernumber, max(c.name) customername,
 dela.zipcode deliverypointzipcode, max(r.routenumber) routenumber,
 tad.description transportarticledescription, count(1) noteqt, max(c.remark) customer_remark,
 round(sum((select sum(nvl(p.theoreticalweight * pkl.deliveredamount, 0))
            from   packingnoteline pkl
            join   product p
            on     p.product_id = pkl.product_id
            where  pkl.packingnote_id = pk.packingnote_id)), 2) theoreticalweight
from   packingnote pk
join   deliverypoint delp
on     delp.deliverypoint_id = pk.deliverypoint_id
join   address dela
on     dela.address_id = delp.deliveryaddress_id
join   customer c
on     c.customer_id = delp.customer_id
join   salesarea sa
on     sa.salesarea_id = c.salesarea_id
join   transportarticle_desc tad
on     (tad.transportarticle_id = pk.transportarticle_id and tad.language_id = c.language_id)
left   outer join route r
on     r.route_id = pk.route_id
where  pk.status_id <> (select st.status_id
                        from   status st
                        where  st.statustype = 'PN'
                        and    st.code = '40') 
group  by pk.deliverydate, c.customernumber, dela.zipcode, tad.description
/