create or replace view v_emblempicklist as
select supplier.suppliernumber, supplier.name suppliername, addresssuplier.street supplierstreet,
       addresssuplier.zipcode supplierzip, addresssuplier.city suppliercity,
       supplier.phone supplierphone, supplier.fax supplierfax,
       coalesce(v_sk.businessunitcode, v_sk2.businessunitcode) businessunitcode,
       coalesce(v_sk.businessunitdesc, v_sk2.businessunitdesc) businessunitdesc,
       coalesce(v_sk.plantstreet, v_sk2.plantstreet) plantstreet,
       coalesce(v_sk.plantzip, v_sk2.plantzip) plantzip,
       coalesce(v_sk.plantcity, v_sk2.plantcity) plantcity,
       coalesce(v_sk.plantphone, v_sk2.plantphone) plantphone,
       coalesce(v_sk.plantfax, v_sk2.plantfax) plantfax,
       coalesce(v_sk.plantcode, v_sk2.plantcode) plantcode,
       coalesce(v_sk.stockroomcode, v_sk2.stockroomcode) stockroomcode,
       coalesce(v_sk.stockroomdesc, v_sk2.stockroomdesc) stockroomdesc, route.routenumber, day.daynumber,
       (select dd.description from day_desc dd where dd.day_id = day.day_id
               and dd.language_id = l.language_id) daydesc, 
       w.emblemname nameonlabel, wo.workordernumber, wo.neededdate requestdate,
       coalesce(c.customernumber, c2.customernumber) customernumber,
       coalesce(c.searchname, c2.searchname) customersearchname,
       coalesce(c.name, c2.name) customername, w.wearernumber, w.fullname wearername, 
       v_sk.rowcode, v_sk.locationcode, coalesce(p.code, empt.code) emblemnr,
       coalesce((select prd.description from product_desc prd where  prd.product_id = p.product_id
                        and prd.language_id = l.language_id), 
                 empl.description,
                 (txt.code || ' ' || fabric2.code)) emblemdescription, 
       empl.emblemtype, s.code sizeshape, coalesce(fabric.code, fabric2.code) fabric,
       coalesce(emblemfont.description, ef.description) emblem_font,
       coalesce(c1.code, color.code) background, coalesce(text.code, txt.code) text,
       coalesce(border.code, c2.code) border, woem.qtyrequested quantityrequested,
       l.code languagecode, empl.description emblemtemplateline_desc
 from workorderlineemblem woem 
      left outer join emblemtemplateline empl
           inner join emblemtemplate empt on empl.emblemtemplate_id = empt.emblemtemplate_id
           inner join sizedefinition s on empl.sizedefinition_id = s.sizedefinition_id
           inner join fabric fabric2 on empl.fabric_id = fabric2.fabric_id
           left outer join emblemfont ef on empl.emblemfont_id = ef.emblemfont_id
           left outer join color on empl.bkgcolor_id = color.color_id
           left outer join color c2 on empl.bordercolor_id = c2.color_id
           left outer join placement on empl.placement_id = placement.placement_id
           left outer join color txt on empl.textcolor_id = txt.color_id
      on woem.emblemtemplateline_id = empl.emblemtemplateline_id
      inner join workorderline wol
            inner join workorder wo 
                  left outer join route
                       inner join day on route.drivingday_id = day.day_id 
                  on wo.route_id = route.route_id
                  left outer join distributionpoint dp
                       inner join department t 
                             inner join customer c2 on t.customer_id = c2.customer_id 
                       on dp.department_id = t.department_id
                  on wo.distributionpoint_link_id = dp.distributionpoint_id
                  left outer join weareremployment we
                       inner join wearer w 
                             inner join customer c on w.customer_id = c.customer_id 
                       on we.wearer_id = w.wearer_id
                  on wo.weareremployment_link_id = we.weareremployment_id
                  left outer join v_emblempicklist_stock_userdef v_sk2 on wo.processingstockroom_id = v_sk2.stockroom_id
            on wol.workorder_id = wo.workorder_id
      on woem.workorderline_id = wol.workorderline_id
      left outer join product p
           inner join fabric on p.fabric_id = fabric.fabric_id
           inner join color c1 on p.color_id = c1.color_id
           inner join supplier
                 inner join address addresssuplier on supplier.address_id = addresssuplier.address_id
           on p.supplier_id = supplier.supplier_id
           inner join emblemproduct emp
                 left outer join color text on emp.textcolor_id = text.color_id
                 left outer join color border on emp.bordercolor_id = border.color_id
                 left outer join emblemfont on emp.emblemfont_id = emblemfont.emblemfont_id 
           on p.product_id = emp.product_id
      on coalesce(woem.product_id, empl.product_id) = p.product_id
      left outer join v_emblempicklist_stock v_sk on (v_sk.product_id = p.product_id and v_sk.sizedefinition_id = empl.sizedefinition_id)
      , languages l
/
