create or replace view v_summarydeliveredproducts as
select -- + INDEX(pn, XSPEEDPACKINGNOTE) INDEX(ro, XPKROUTE) INDEX(de, XPKDEPOT) TD26279
pn.deliverydate,
  pr.typeofmerchandise,
  pg.code productgroupcode,
  pr.code productcode,
  (select prd.description from product_desc prd where prd.product_id = pr.product_id
    and prd.language_id = l.language_id) productdesc,
  fm.code finmethcode, sz.code sizecode,
  (select szd.description from sizedefinition_desc szd where szd.sizedefinition_id = sz.sizedefinition_id
    and szd.language_id = l.language_id) sizedesc,
  (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id = fm.finishingmethod_id
    and fmd.language_id = l.language_id) finmethdesc,
  ro.routenumber, coalesce(ro.description, '') routedesc, coalesce(ro.plant_id, bu.plant_id) routeplant_id,
   coalesce(de.code,'-') depotcode,
    Coalesce(de.description,'-') depotdesc,
  pld.code plantdepcode, pld.description plantdepdesc,
  dip.description distribpointdesc,
  de.code departmentcode,
  cu.customernumber, cu.name customername,
  pnl.deliveredamount,
  bu.businessunit_id, bu.plant_id, internalbu.businessunit_id internalBU,
  l.code languagecode
from PackingNoteLine PNL
     inner join PackingNote PN
      left outer join businessunit internalbu on pn.internalprocessingbu_id=internalbu.businessunit_id 
           inner join status st on st.status_id = pn.status_id
           inner join businessunit bu on bu.businessunit_id = pn.processbusinessunit_id
               left outer join route ro
                 left outer join depot de on ro.depot_id = de.depot_id
           on pn.route_id = ro.route_id 
           inner join plantdepartment pld on pld.plantdepartment_id = pn.plantdepartment_id
           inner join distributionpoint dip
                 inner join department dp
                       inner join customer cu on cu.customer_id = dp.customer_id
                 on dp.department_id = dip.department_id
           on dip.distributionpoint_id = pn.distributionpoint_id
     on pn.packingnote_id = pnl.packingnote_id
     inner join product pr
           inner join productgroup pg on pr.productgroup_id = pg.productgroup_id
     on pr.product_id = pnl.product_id
     inner join sizedefinition sz on  sz.sizedefinition_id = pnl.sizedefinition_id
     inner join finishingmethod fm on fm.finishingmethod_id = pnl.finishingmethod_id
, languages l
where
   ((bu.plant_id = pld.plant_id
  and exists (select v.customer_id from v_customersperbusinessunitdp v, businessunit b
              where v.customer_id=cu.customer_id and v.businessunit_id=b.businessunit_id
                and b.plant_id = bu.plant_id)) or
  (internalbu.plant_id = pld.plant_id
  and exists (select v.customer_id from v_customersperbusinessunitdp v, businessunit b
              where v.customer_id=cu.customer_id and v.businessunit_id=b.businessunit_id
                and b.plant_id = internalbu.plant_id)))
  and coalesce(de.plant_id, bu.plant_id) = bu.plant_id --in case depot is null
  and st.code = '30' -- processed
  and pn.origin <> 6 --TD21371 exclude Bulk Notes
;
/