create or replace view v_costsspecificationperclient as
select 1 line_type, null labellayout_id
, il.invoiceline_id l_invoiceline_id, ih.invoiceheader_id invoiceheader_id, ih.invoicenumber l_invoicenumber, ih.documenttype l_documenttype
, ilh.invoiceline_id h_invoiceline_id, ihh.invoicenumber h_invoicenumber, ihh.documenttype h_documenttype
, ih.invoicebusinessunit_id ihinvoicebusinessunit_id, cdn.invoicebusinessunit_id cdninvoicebusinessunit_id
, cdn.processbusinessunit_id, cdn.customer_link_id Customer_Id, cdn.deliverydate
, (select crr.invoiceamountdecimals from currency crr where cus.currency_id=crr.currency_id) invoiceamountdecimals
, (select crr.decimals from currency crr where cus.currency_id=crr.currency_id) pricedecimals
, cdn.closingdate--, cbi.residentialservicesallowed
, crs.customerclients, cr.laundryservicesparticipant
, v.departmentnumber, v.Dep_desc
, cdn.roomnumber, cdn.clientdeliverynotenumber, cli.clientnumber
, cli.searchname, cli.name Client_name
, cus.name Customer_name, cus.customernumber, a.city
, coalesce((select plpc.customcode from productlistpercustomer plpc where plpc.productlistpercustomer_id=F_GETIDCOLUMNFROMACTIVEPLPC(cus.customer_id, p.product_id, s.sizedefinition_id, f.finishingmethod_id, s.sizedefinition_id, cdn.deliverydate, 0)), p.code) Prod_code
, coalesce((select plpc.customdescription from productlistpercustomer plpc where plpc.productlistpercustomer_id=F_GETIDCOLUMNFROMACTIVEPLPC(cus.customer_id, p.product_id, s.sizedefinition_id, f.finishingmethod_id, s.sizedefinition_id, cdn.deliverydate, 0)),(select pd.description from product_desc pd where pd.product_id=p.product_id and pd.language_id = l.language_id)) Prod_Desc
, (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id=s.sizedefinition_id and sdd.language_id = l.language_id) Sizedef_Desc
, (select fmd.description from finishingmethod_desc fmd where fmd.finishingmethod_id=f.finishingmethod_id and fmd.language_id = l.language_id) Finish_Desc
, case when '1' in (select max(cast(sst.value as varchar2(60))) from systemsetting sst, businessunit bu where sst.plant_id = bu.plant_id and sst.name = 'CLIENTINVOICEDBASEDON' and (bu.businessunit_id = ih.invoicebusinessunit_id OR bu.businessunit_id =cdn.processbusinessunit_id))
  then (cdl.qtyscannedin + cdl.qtycountedin)
  else (cdl.qtyscannedout + cdl.qtycountedout) end Quantity, 0 weightdecimals
, gpl.code generalpricelistcode
, gpl.description generalpricelistdescr
, il.price Il_Price
, nvl((select gwp.price from generalwashprice gwp
   where gwp.generalpricelist_id = gpl.generalpricelist_id
        and gwp.product_id = cdl.product_link_id
         and gwp.sizedefinition_id = cdl.sizedefinition_link_id
          and gwp.finishingmethod_id = cdl.finishingmethod_id
           and gwp.pricetype = 1
           and gwp.startdate <= cdn.deliverydate
        and not exists
 (select 1
          from generalwashprice gwp2
         where gwp2.generalpricelist_id = gwp.generalpricelist_id
           and gwp2.product_id = gwp.product_id
           and gwp2.startdate <= cdn.deliverydate
           and gwp2.startdate > gwp.startdate)), 0) gwp_price
, gpl_billto.code generalpricelistcode_billto
, gpl_billto.description generalpricelistdescr_billto
, nvl((select gwp_billto.price from generalwashprice gwp_billto
   where gwp_billto.generalpricelist_id = gpl_billto.generalpricelist_id
        and gwp_billto.product_id = cdl.product_link_id
         and gwp_billto.sizedefinition_id = cdl.sizedefinition_link_id
          and gwp_billto.finishingmethod_id = cdl.finishingmethod_id
           and gwp_billto.pricetype = 1
           and gwp_billto.startdate <= cdn.deliverydate
        and not exists
 (select 1
          from generalwashprice gwp_billto2
         where gwp_billto2.generalpricelist_id = gwp_billto.generalpricelist_id
           and gwp_billto2.product_id = gwp_billto.product_id
           and gwp_billto2.startdate <= cdn.deliverydate
           and gwp_billto2.startdate > gwp_billto.startdate)), 0) gwp_price_billto
, cdl.price
, cdl.invoicebusinessunit_id LineInvoicebusinessunit_id
, l.code Language_code
, s.code Size_code, f.code Finishing_code, f.washtype FMwashtype
, ctx.TAXPERCENTAGE, ctx.TAXAMOUNT--SUM(ctx.TAXAMOUNT) TAXAMOUNT
, txg.code taxgroup_code, txg.description taxgroup_desc
, (select ba.batchdate from batch ba where ba.batch_id = cdn.batch_id) batchdate
, (select max(cast(sst.value as varchar2(60))) from systemsetting sst, businessunit bu where sst.plant_id = bu.plant_id and sst.name = 'CLIENTINVOICEDBASEDON' and (bu.businessunit_id = ih.invoicebusinessunit_id OR bu.businessunit_id =cdn.processbusinessunit_id)) CLIENTINVOICEDBASEDON
, coalesce((select case when cdn.deliverydate < tg.startdatetaxrate then tg.oldtaxrate else tg.taxrate end
      from taxgroup tg where tg.code = il.taxgroupcode and tg.country_id = cus.country_id), ctx.TAXPERCENTAGE, 0) TaxRate
from clientdeliverynoteline cdl
left outer join invoiceline il
     join (invoiceheader ih
            join
            (Customerresidentialsettings crs_dellto
              left outer join generalpricelist gpl_billto on crs_dellto.enduserpricelist_id = gpl_billto.generalpricelist_id)
            on ih.deltocustomer_link_id = crs_dellto.customer_id
            )
                on (ih.invoiceheader_id=il.invoiceheader_id)
on cdl.invoiceline_id=il.invoiceline_id
, client cli --left outer join clientbillinginfo cbi on cbi.client_id = cli.client_id
left outer join clientresident cr on cli.client_id=cr.client_id
, v_ClientDepartment v
, address a, customer cus
left outer join (Customerresidentialsettings crs left outer join generalpricelist gpl on crs.enduserpricelist_id = gpl.generalpricelist_id) on cus.customer_id  =crs.customer_id
, clientdeliverynote cdn
  left outer join invoiceline ilh
       join invoiceheader ihh on (ihh.invoiceheader_id = ilh.invoiceheader_id)
  on cdn.invoiceline_id = ilh.invoiceline_id
  left outer join clientdeliverynotetax ctx
       join taxgroup txg on (ctx.taxgroup_id = txg.taxgroup_id)
  ON ctx.CLIENTDELIVERINOTE_ID = cdn.ClientDeliveryNote_ID
, languages l
, product p, sizedefinition s, finishingmethod f
where cdl.clientdeliverinote_id = cdn.clientdeliverynote_id
and cdn.customer_link_id = cus.customer_id
and cli.client_id = cdn.client_link_id
and cus.visitaddress_id = a.address_id
and cdl.product_link_id = p.product_id
and cdl.sizedefinition_link_id = s.sizedefinition_id
and cdl.finishingmethod_id = f.finishingmethod_id
and cli.client_id=v.client_id
and cdn.closingdate is not null
and cus.language_id=l.language_id
union all
select 2 line_type, null labellayout_id
, il.invoiceline_id l_invoiceline_id, ih.invoiceheader_id invoiceheader_id, ih.invoicenumber l_invoicenumber, ih.documenttype l_documenttype
, ilh.invoiceline_id h_invoiceline_id, ihh.invoicenumber h_invoicenumber, ihh.documenttype h_documenttype
, ih.invoicebusinessunit_id ihinvoicebusinessunit_id, cdn.invoicebusinessunit_id cdninvoicebusinessunit_id
, cdn.processbusinessunit_id, cdn.customer_link_id Customer_Id, cdn.deliverydate
, (select crr.invoiceamountdecimals from currency crr where cus.currency_id=crr.currency_id) invoiceamountdecimals
, (select crr.decimals from currency crr where cus.currency_id=crr.currency_id) pricedecimals
, cdn.closingdate--, cbi.residentialservicesallowed
, crs.customerclients, cr.laundryservicesparticipant
, v.departmentnumber, v.Dep_desc
, cdn.roomnumber, cdn.clientdeliverynotenumber, cli.clientnumber
, cli.searchname, cli.name Client_name
, cus.name Customer_name, cus.customernumber, a.city
, sc.code Prod_code
, (select scd.description from specialcharge_desc scd where sc.specialcharge_id=scd.specialcharge_id and scd.language_id = l.language_id) Prod_Desc
, '1' Sizedef_Desc
, '11' Finish_desc
, cdc.quantity, 0 weightdecimals
, '' generalpricelistcode
, '' generalpricelistdescr
, il.price Il_Price
, 0 gwp_price
, '' generalpricelistcode_billto
, '' generalpricelistdescr_billto
, 0 gwp_price_billto
, cdc.price
, -2 LineInvoicebusinessunit_id
, l.code Language_code
, '' Size_code, '' Finishing_code,  -1 FMwashtype
, ctx.TAXPERCENTAGE, ctx.TAXAMOUNT--SUM(ctx.TAXAMOUNT) TAXAMOUNT
, txg.code taxgroup_code, txg.description taxgroup_desc
, (select ba.batchdate from batch ba where ba.batch_id = cdn.batch_id) batchdate
, (select max(cast(sst.value as varchar2(60))) from systemsetting sst, businessunit bu where sst.plant_id = bu.plant_id and sst.name = 'CLIENTINVOICEDBASEDON' and (bu.businessunit_id = ih.invoicebusinessunit_id OR bu.businessunit_id =cdn.processbusinessunit_id)) CLIENTINVOICEDBASEDON
, coalesce((select case when cdn.deliverydate < tg.startdatetaxrate then tg.oldtaxrate else tg.taxrate end
      from taxgroup tg where tg.code = il.taxgroupcode and tg.country_id = cus.country_id), ctx.TAXPERCENTAGE, 0) TaxRate
from clientdeliverynotespeccharge cdc
left outer join invoiceline il
     join invoiceheader ih on (ih.invoiceheader_id=il.invoiceheader_id)
on cdc.invoiceline_id=il.invoiceline_id
, client cli --left outer join clientbillinginfo cbi on cbi.client_id = cli.client_id
left outer join clientresident cr on cli.client_id=cr.client_id
, v_ClientDepartment v
, address a, customer cus
left outer join Customerresidentialsettings crs on cus.customer_id=crs.customer_id
, clientdeliverynote cdn left outer join invoiceline ilh
       join invoiceheader ihh on (ihh.invoiceheader_id = ilh.invoiceheader_id)
  on cdn.invoiceline_id = ilh.invoiceline_id
  left outer join clientdeliverynotetax ctx
       join taxgroup txg on (ctx.taxgroup_id = txg.taxgroup_id)
  ON ctx.CLIENTDELIVERINOTE_ID = cdn.ClientDeliveryNote_ID
, languages l
, specialcharge sc
where cdc.clientdeliverinote_id=cdn.clientdeliverynote_id
and cdn.customer_link_id=cus.customer_id
and cli.client_id=cdn.client_link_id
and cus.visitaddress_id=a.address_id
and cdc.specialcharge_id=sc.specialcharge_id
and cli.client_id=v.client_id
and cdn.closingdate is not null
and cus.language_id=l.language_id
union all
select 3 line_type, null labellayout_id
, il.invoiceline_id l_invoiceline_id, ih.invoiceheader_id invoiceheader_id,ih.invoicenumber l_invoicenumber, ih.documenttype l_documenttype
, il.invoiceline_id h_invoiceline_id, ih.invoicenumber h_invoicenumber, ih.documenttype h_documenttype
, ih.invoicebusinessunit_id ihinvoicebusinessunit_id, cdn.invoicebusinessunit_id cdninvoicebusinessunit_id
, cdn.processbusinessunit_id, cdn.customer_link_id Customer_Id, cdn.deliverydate
, (select crr.invoiceamountdecimals from currency crr where cus.currency_id=crr.currency_id) invoiceamountdecimals
, (select crr.decimals from currency crr where cus.currency_id=crr.currency_id) pricedecimals
, cdn.closingdate--, cbi.residentialservicesallowed
, crs.customerclients, cr.laundryservicesparticipant
, v.departmentnumber, v.Dep_desc
, cdn.roomnumber, cdn.clientdeliverynotenumber, cli.clientnumber
, cli.searchname, cli.name Client_name
, cus.name Customer_name, cus.customernumber, a.city
, wc.code Prod_code
, (select wcd.description from weighingcategory_desc wcd where wcd.weighingcategory_id=wc.weighingcategory_id and wcd.language_id = l.language_id) Prod_Desc
, '1' Sizedef_Desc
, '11' Finish_desc
, cdn.invoiceweight Quantity, cdn.weightdecimals
, '' generalpricelistcode
, '' generalpricelistdescr
, il.price Il_Price
, 0 gwp_price
, '' generalpricelistcode_billto
, '' generalpricelistdescr_billto
, 0 gwp_price_billto
, cdn.weightprice Price
, -3 LineInvoicebusinessunit_id
, l.code Language_code
, '' Size_code, '' Finishing_code,  -1 FMwashtype
, ctx.TAXPERCENTAGE, ctx.TAXAMOUNT--SUM(ctx.TAXAMOUNT) TAXAMOUNT
, txg.code taxgroup_code, txg.description taxgroup_desc
, (select ba.batchdate from batch ba where ba.batch_id = cdn.batch_id) batchdate
, (select max(cast(sst.value as varchar2(60))) from systemsetting sst, businessunit bu where sst.plant_id = bu.plant_id and sst.name = 'CLIENTINVOICEDBASEDON' and (bu.businessunit_id = ih.invoicebusinessunit_id OR bu.businessunit_id =cdn.processbusinessunit_id)) CLIENTINVOICEDBASEDON
, coalesce((select case when cdn.deliverydate < tg.startdatetaxrate then tg.oldtaxrate else tg.taxrate end
      from taxgroup tg where tg.code = il.taxgroupcode and tg.country_id = cus.country_id), ctx.TAXPERCENTAGE, 0) TaxRate
from clientdeliverynote cdn
  left outer join invoiceline il
     join invoiceheader ih on (ih.invoiceheader_id=il.invoiceheader_id)
  on cdn.invoiceline_id=il.invoiceline_id
  left outer join clientdeliverynotetax ctx
       join taxgroup txg on (ctx.taxgroup_id = txg.taxgroup_id)
  ON ctx.CLIENTDELIVERINOTE_ID = cdn.ClientDeliveryNote_ID
, client cli --left outer join clientbillinginfo cbi on cbi.client_id = cli.client_id
left outer join clientresident cr on cli.client_id=cr.client_id
, v_ClientDepartment v
, address a, customer cus
left outer join Customerresidentialsettings crs on cus.customer_id=crs.customer_id
, languages l
, weighingcategory wc
where cdn.customer_link_id=cus.customer_id
and cli.client_id=cdn.client_link_id
and cus.visitaddress_id=a.address_id
and wc.weighingcategory_id=cdn.weighingcategory_link_id
and cli.client_id=v.client_id
and cdn.closingdate is not null
and cus.language_id=l.language_id
union all
select 4 line_type, ll.labellayout_id
, il.invoiceline_id l_invoiceline_id,ih.invoiceheader_id invoiceheader_id,ih.invoicenumber l_invoicenumber, ih.documenttype l_documenttype
, ilh.invoiceline_id h_invoiceline_id, ihh.invoicenumber h_invoicenumber, ihh.documenttype h_documenttype
, ih.invoicebusinessunit_id ihinvoicebusinessunit_id, cdn.invoicebusinessunit_id cdninvoicebusinessunit_id
, cdn.processbusinessunit_id, cdn.customer_link_id Customer_Id, cdn.deliverydate
, crr.invoiceamountdecimals, crr.decimals pricedecimals
, cdn.closingdate--, cbi.residentialservicesallowed
, crs.customerclients, cr.laundryservicesparticipant
, v.departmentnumber, v.Dep_desc
, cdn.roomnumber, cdn.clientdeliverynotenumber, cli.clientnumber
, cli.searchname, cli.name Client_name
, cus.name Customer_name, cus.customernumber, a.city
, ll.code Prod_code
, pqr.preprinted Sizedef_Desc
, pqr.chargeable Prod_Desc
, '11' Finish_desc
, count(*) quantity, 0 weightdecimals
, '' generalpricelistcode
, '' generalpricelistdescr
, il.price Il_Price
, 0 gwp_price
, '' generalpricelistcode_billto
, '' generalpricelistdescr_billto
, 0 gwp_price_billto
, pqr.price
, pqr.invoicebusinessunit_id LineInvoicebusinessunit_id
, l.code Language_code
, '' Size_code, '' Finishing_code,  -1 FMwashtype
, ctx.TAXPERCENTAGE, ctx.TAXAMOUNT--SUM(ctx.TAXAMOUNT) TAXAMOUNT
, txg.code taxgroup_code, txg.description taxgroup_desc
, (select ba.batchdate from batch ba where ba.batch_id = cdn.batch_id) batchdate
, (select max(cast(sst.value as varchar2(60))) from systemsetting sst, businessunit bu where sst.plant_id = bu.plant_id and sst.name = 'CLIENTINVOICEDBASEDON' and (bu.businessunit_id = ih.invoicebusinessunit_id OR bu.businessunit_id =cdn.processbusinessunit_id)) CLIENTINVOICEDBASEDON
, coalesce((select case when cdn.deliverydate < tg.startdatetaxrate then tg.oldtaxrate else tg.taxrate end
      from taxgroup tg where tg.code = il.taxgroupcode and tg.country_id = cus.country_id), ctx.TAXPERCENTAGE, 0) TaxRate
from printqueueresidentialitem pqr
left outer join invoiceline il
     join invoiceheader ih on (ih.invoiceheader_id=il.invoiceheader_id)
on pqr.invoiceline_id=il.invoiceline_id
, client cli --left outer join clientbillinginfo cbi on cbi.client_id = cli.client_id
left outer join clientresident cr on cli.client_id=cr.client_id
, v_ClientDepartment v
, address a, customer cus
left outer join Customerresidentialsettings crs on cus.customer_id=crs.customer_id
, currency crr
, clientdeliverynote cdn
  left outer join invoiceline ilh
       join invoiceheader ihh on (ihh.invoiceheader_id = ilh.invoiceheader_id)
  on cdn.invoiceline_id = ilh.invoiceline_id
  left outer join clientdeliverynotetax ctx
       join taxgroup txg on (ctx.taxgroup_id = txg.taxgroup_id)
  ON ctx.CLIENTDELIVERINOTE_ID = cdn.ClientDeliveryNote_ID
, languages l
, labellayout ll
where pqr.clientdeliverynote_id=cdn.clientdeliverynote_id
and cdn.customer_link_id=cus.customer_id
and cli.client_id=cdn.client_link_id
and cus.visitaddress_id=a.address_id
and cus.currency_id=crr.currency_id
and pqr.labellayout_id=ll.labellayout_id
and cli.client_id=v.client_id
and cdn.closingdate is not null
and cus.language_id=l.language_id
group by ll.labellayout_id, il.invoiceline_id, pqr.chargeable , pqr.preprinted
, ih.invoiceheader_id , ih.invoicenumber, ih.invoicebusinessunit_id
, cdn.processbusinessunit_id, cdn.customer_link_id, cdn.deliverydate
, cdn.closingdate--, cbi.residentialservicesallowed
, v.departmentnumber, v.Dep_desc
, cdn.roomnumber, cdn.clientdeliverynotenumber, cli.clientnumber
, cli.searchname, cli.name
, cus.name , cus.customernumber, a.city
, ll.code, il.price, pqr.price, cdn.invoicebusinessunit_id, l.code
, ih.documenttype , ilh.invoiceline_id , ihh.invoicenumber , ihh.documenttype
, crr.invoiceamountdecimals, crr.decimals, crs.customerclients, cr.laundryservicesparticipant
, pqr.invoicebusinessunit_id
, ctx.TAXPERCENTAGE, ctx.TAXAMOUNT, txg.code, txg.description, cdn.batch_id
, l.language_id, cus.country_id, il.taxgroupcode
/
