create or replace view v_deliverynote as
select pn.correctednotenumber,
       (select cn.deliverydate from packingnote cn
          where pn.correctednotenumber = cn.notenumber and pn.correctednotenumber <> 0) correctiondate,
       pn.notenumber, cu.customernumber, cu.name customername, cu.legalname,
       ad.street customerstreet, ad.street2 customerstreet2, ad.zipcode customerzip, ad.city customercity,
       ad.state customerstate, cco.code custcountry, cco.description custcountry_desc, ad.county customercounty,
       de.departmentnumber, de.description departmentdescription, dp.distributionpointnumber, 
       dp.description distributionpointdescription, delp.code deliverypointcode, delp.description deliverypointdescription, 
       pn.sequencenumber, (select tad.description from transportarticle_desc tad
                          where tad.transportarticle_id = pn.transportarticle_id
                          and tad.language_id = lan.language_id) transportarticledescription,
       pn.occupancy, emp.name employeename, pn.deliverydate, pn.originaldeliverydate, pn.remark, 
       procbu.description processbudescription, pl.code plantcode, pl.name plantname, pl.website plantwebsite, pl.logo plantlogo,
       pl.email plantemail, pla.street plantstreet, pla.zipcode plantzipcode, pla.city plantcity, pla.state plantstate,
       pl.phoneno plantphoneno, pl.faxno plantfaxno, pco.code plantcountry_code, pco.description plantcountry_desc,
       cast(ss.value as varchar2(60)) weightunit,
       cast(ss1.value as varchar2(1)) printdelnotepercontainer,
       cast(ss2.value as varchar2(1)) containerrecordingactive,
       cast(ss3.value as varchar2(1)) printdelnotezerolines,
       ro.routenumber, ro.description routedescription, coalesce(rc.loadingdock, ro.loadingdock) loadingdock,
       pn.stopnumber, pn.systemuser_id, pn.timestamp, pn.creationtimestamp, pn.processedtimestamp, pn.packingnote_id, pn.enddateforuse,
       pn.origin, fl.code deliverylayoutcode, (select dd.description from day_desc dd
                  where dd.language_id = lan.language_id and dd.day_id = pn.daynumber) daydescription,
       cast(pn.week as varchar2(60)) week, lan.code languagecode, cula.code customerlanguagecode,
       pn.productiondate productiondate, cu.scansondeliverynote,
       (case when clm.customer_id = null then 'N' else 'Y' end) haslockeranagement, clm.chargeservice, clm.chargerent,
       clm.uselockerobligated, clm.fixednumberbank, pn.numberofgarmentsinlocker, pn.numberoflockers,
       coalesce((select count(pnc.packingnote_id) from packingnotecontainer pnc
                  where pnc.packingnote_id = pn.packingnote_id), 0) noofcontainers,
       pld.code plant_dep_code, pld.description plant_dep_desc, pn.freeofchargereason_id,
       (select r.code from reasoncode r where r.reasoncode_id = pn.freeofchargereason_id) free_reason_code,
       (select rd.description from reasoncode_desc rd where rd.reasoncode_id = pn.freeofchargereason_id
            and rd.language_id = lan.language_id) free_reason_code_desc,
       coalesce((select max(ds.packingnote_id) from deliveryscan ds where ds.packingnote_id = pn.packingnote_id), 
                        (select max(o.packingnote_id) from outofcirculationhistory o
                        where o.packingnote_id = pn.packingnote_id), 0) numberofpossiblelineswithscans,
       pn.year, pn.pricescalculated, pn.totalamount, pn.totalamountexcludingvat, pn.weighingticket_id, 
       curr.decimals, curr.invoiceamountdecimals, curr.code currencycode, invbu.vatnumber vatnumber,
       pn.referencenumber, invbu.internalvatnumber, cu.searchname, procbu.code processbucode,
       ro.putupreportpercustomer, cu.signaturerequired, dsig.personidentification personidentification,
       dsig.signature signature, rc.drivingdate signaturedate, to_char(st.departuretime, 'fmhhfm:MI:AM') signaturetime,
       to_char(st.departuretime, 'fmhh24fm:MI') signaturetime24, pn.distributionpoint_id, pn.processbusinessunit_id,
       delp.signaturebarcode, cast(ss4.value as varchar2(60)) containertypedescription,
       (select max(wo.referencenumber) from deliveryscan ds, issuehistory ih, workorder wo
          where ds.uniqueitem_link_id = ih.uniqueitem_link_id
            and ih.issuehistory_id = (select max(ih2.issuehistory_id) from issuehistory ih2
                                   where ih2.uniqueitem_link_id = ds.uniqueitem_link_id)
            and ih.workorder_id = wo.workorder_id and ds.packingnote_id = pn.packingnote_id
            and wo.deliverfrom = 2) worefnumber,
       (select dnrd.description from deliverynoteremark_desc dnrd
          where dnrd.deliverynoteremark_id = pn.deliverynoteremark_id
            and dnrd.language_id = cula.language_id) deliverynoteremarkdesc,  
 (select listagg(X.reference, CHR(13))  within group(order by x.deliverydate) 
  from (select dv.reference, min(dv.deliverydate) deliverydate from deliveryvariations dv 
     where dv.packingnote_id = pn.packingnote_id and dv.reference is not null
     group by dv.reference) X) DV_Reference
, de.delnotetoemail, decode (dsig.scannedyn, 'Y', 'Yes','N','No') scannedyn, dsig.scantimestamp
from packingnote pn
  left outer join employee emp on pn.employee_id = emp.employee_id
  left outer join route ro on pn.route_id = ro.route_id
  left outer join stop st 
       left outer join routecontrol rc on st.routecontrol_id = rc.routecontrol_id
  on pn.stop_id = st.stop_id
  left outer join deliverysignature dsig on pn.stop_id = dsig.stop_id and pn.deliverysignature_id = dsig.stop_id
  inner join distributionpoint dp 
        inner join department de 
              inner join customer cu
                    left outer join formlayout fl on cu.deliverynotelayout_id = fl.formlayout_id
                    left outer join customerlockermanagement clm on cu.customer_id = clm.customer_id
                    inner join country cco on cu.country_id = cco.country_id
                    inner join languages cula on cu.language_id = cula.language_id
                    inner join currency curr on cu.currency_id = curr.currency_id
              on de.customer_id = cu.customer_id
        on dp.department_id = de.department_id
  on pn.distributionpoint_id = dp.distributionpoint_id
  inner join deliverypoint delp 
        inner join address ad on delp.deliveryaddress_id = ad.address_id
  on pn.deliverypoint_id = delp.deliverypoint_id
  inner join status sta on pn.status_id = sta.status_id and sta.statustype = 'PN'
                        and (sta.code = 30 or sta.code = 20 or (pn.origin = 14 and sta.code = 40))
  inner join businessunit procbu
        inner join plant pl 
              left outer join systemsetting ss on pl.plant_id = ss.plant_id and ss.name = 'WEIGHTUNIT'
              left outer join systemsetting ss1 on pl.plant_id = ss1.plant_id and ss1.name = 'PRINTDELNOTEPERCONTAINER'
              left outer join systemsetting ss2 on pl.plant_id = ss2.plant_id and ss2.name = 'CONTAINERRECORDINGACTIVE'
              left outer join systemsetting ss3 on pl.plant_id = ss3.plant_id and ss3.name = 'PRINTDELNOTEZEROLINES'
              left outer join systemsetting ss4 on pl.plant_id = ss4.plant_id and ss4.name = 'CONTAINERTYPEUNQBRCLABEL'
              inner join address pla on pl.address_id = pla.address_id
              inner join country pco on pl.country_id = pco.country_id
        on procbu.plant_id = pl.plant_id
  on pn.processbusinessunit_id = procbu.businessunit_id
  inner join plantdepartment pld on pn.plantdepartment_id = pld.plantdepartment_id
  inner join businessunit invbu on pn.invoicebusinessunit_id = invbu.businessunit_id,
 languages lan
/
