create or replace view v_purchaseorder as
select supp.suppliernumber suppNumber, supp.name suppName, suad.Street SuppStreet
, suad.Zipcode SuppZipCode, suad.City SuppCity, supp.phone SuppPhone
, supp.fax Suppfax, supp.email Suppemail, supp.suppliertype
, sksp.buyercode, sk.code stockCode, sk.name stockName, plantBU.name plantBUName
, CONCATENATEWITHBLANK(skad.Street, skad.Street2) STOCKstreet
, skad.Zipcode STOCKzipcode, skad.City STOCKcity
, CONCATENATEWITHBLANK3(skad.State,'/',skad.County) STOCKStateCounty
, skad.State STOCKState, skad.County STOCKCounty
, plantBU.phoneno plantBUPhone, plantBU.Faxno plantBUFax, plantBU.email plantBUEmail
, pcy.plantcompany_id, pcy.code plantcompanyCode, pcy.name plantcompanyName, pcad.Street PCYStreet
, pcad.Zipcode PCYZipCode, pcad.City PCYCity
, CONCATENATEWITHBLANK3(pcad.State,'/',pcad.County) PCYStateCounty
, pcad.State PCYState, pcad.County PCYCounty
, CONCATENATEWITHBLANK3(poai.state,'/',poai.county) POAYStateCounty
, poai.state POAYState, poai.county POAYCounty
, pcy.phone plantcompanyPhone, pcy.fax plantcompanyFax, pcy.email plantcompanyEmail, pcy.vatnumber plantcompanyVAT
, ph.purchaseordernumber, ph.orderdate, ph.requesteddate
, supp.discountpercoverall, supp.discountpercdays, supp.discountwithindays, supp.paymentterms, supp.deliveryterms
, ph.remark PHRemark, case when ph.remark is null then 'Y' else 'N' end PHRemark_null
, cast(CONCATENATEWITHBLANK3(suad.State,'/',suad.County) as varchar2(255)) SuppStateCounty
, suad.State SuppState, suad.County SuppCounty
, prl.code productLinkCode, pol.supplierproductcode, pol.suppliersizecode
, coalesce
  (( select splprd.supplierproductdescription from supplierproduct splprd
          where pol.supplierproductcode = splprd.supplierproductcode
             and pol.suppliersizecode = splprd.suppliersizecode
                 and supp.supplier_id = splprd.supplier_id)
, (select prd.description from product_desc prd where prd.product_id = pol.product_link_id and prd.language_id = lan.language_id )) supplierProductDesc
, pol.uniqueitem, pol.quantityordered
, supp.printPriceOnPO
, pol.purchaseprice, (pol.quantityordered * pol.purchaseprice) amount
, pol.remark PLIRemark, case when pol.remark is null then 'Y' else 'N' end PLIRemark_null
, curr.code currencyCode, curr.decimals pricedecimals, curr.invoiceamountdecimals amountdecimals
, ph.purchaseorderheader_id, pol.purchaseorderline_id
, sta.code statusCode, ph.origin, ph.approvaldate, ph.approvaluser_id
, (select stad.description from status_desc stad where stad.status_id = sta.status_id and stad.language_id = lan.language_id ) statusDesc
, pol.quantityreceived, pol.quantitycancelled, pol.quantityconfirmed
, lan.code languageCode
, sdl.code LaundrySizeCode
, (select qg.Code from QualityGrade qg where pol.requestedgrade_id = qg.qualitygrade_id) QualityGradeCode
, poai.name addressiname, poai.street1 addressistreet1, poai.zipcode addressizipcode, poai.city addressicity
, (case when exists (select 1 from v_purchaseorderline_stock vs
                     where vs.purchaseorderheader_id = ph.purchaseorderheader_id
                           and (vs.qtypurchased - vs.qtyordered) > 0)
        then 'Y' else 'N' end) postocklines
, (case when exists (select 1 from v_purchaseorderline_customer vsc
                     where vsc.purchaseorderheader_id = ph.purchaseorderheader_id
                     and vsc.languagecode = lan.code)
        then 'Y' else 'N' end) pocustomerlines
, coalesce((select case when p.typeofmerchandise = 4 then 1 else 0 end from product p where pol.product_link_id = p.product_id), 0) emblempo
, plantBU.Logo PlantLogo
from purchaseorderheader ph
     left outer join plantcompany pcy
          inner join address pcad on pcy.address_id = pcad.Address_Id
     on ph.plantcompany_id = pcy.plantcompany_id
     left outer join stockroomsupplier sksp on ph.stockroom_id = sksp.stockroom_id and ph.supplier_id = sksp.supplier_id
     left outer join stockroom sk
          inner join businessunit bu 
                inner join plant plantBU on bu.plant_id = plantBU.plant_id
          on sk.managedbybu_id = bu.businessunit_id
          inner join address skad on sk.deliveryaddress_id = skad.address_id
     on ph.stockroom_id = sk.stockroom_id
     left outer join purchaseorderline pol
          inner join product_link prl on pol.product_link_id = prl.product_link_id
          inner join sizedefinition_link sdl on pol.sizedefinition_link_id = sdl.sizedefinition_link_id
     on ph.purchaseorderheader_id = pol.purchaseorderheader_id
     left outer join purchaseorderaddressinfo poai on ph.purchaseorderheader_id = poai.purchaseorderheader_id
     inner join supplier supp
           inner join address suad on supp.address_id = suad.address_id
     on ph.supplier_id = supp.supplier_id
     inner join status sta on ph.status_id = sta.status_id
     inner join currency curr on ph.currency_id = curr.currency_id
, languages lan
/