create or replace view v_deliverynotes2sendclient as
select pn.packingnote_id,
       pn.notenumber,
       bu.businessunit_id,
       bu.code,
       cc.contactpersonnumber cp_number,
       cc.name                cp_name,
       cc.email               cp_email,
       cu.customernumber,
       cu.name                cu_name,
       cu.email               cu_email,
       pn.origin
  from packingnote       pn,
       businessunit      bu,
       distributionpoint cp,
       department        dp,
       customer          cu,
       contactperson     cc
 where bu.businessunit_id = pn.processbusinessunit_id
   and cp.distributionpoint_id = pn.distributionpoint_id
   and dp.department_id = cp.department_id
   and cu.customer_id = dp.customer_id
   and cc.contactperson_id = dp.contactperson_id
   and dp.delnotetoemail = 'Y'
   and pn.emailstatus = 1
   and pn.emailmaybesent = 'Y'
   and pn.status_id != (select st.status_id from status st where st.statustype = 'PN' and st.code = '40')
   and pn.origin not in (6,7);
	/