create or replace view V_Costs_Specs_Per_Wearer_Extra as--CostSpecificationPerWearer.PAV.rpt
select /*+parallel*/ x.linetype, x.invlinetype_desc, x.invoiceheader_id, x.changesperweek, x.invLinePRICE, x.customer_id, x.customernumber, x.wearernumber, x.wearerName,
       x.product_id, x.productCode, x.productDesc, x.languageCode, x.invoicelinetype_id , x.invoicebusinessunit_id, x.endweek, x.endyear, x.invoicedate, x.invoicenumber, x.purchaseorder, x.startdate, x.enddate,
       x.billtocustomer_link_id, x.billtocustomernumber, x.billtocustomername, x.deltocustomer_link_id, x.deltocustomernumber, x.deltocustomername,
       x.street, x.street2, x.city, x.state, x.zipcode, x.deltostreet, x.deltostreet2, x.deltocity, x.deltostate, x.deltozipcode,
       x.plant_id, x.plantCode, x.plantName, x.plantPhoneNo, x.plantFax, x.plantStreet, x.plantStreet2, x.plantCity, x.plantState, x.plantZipcode
from
(select -- RENT - SETS - ILP Wearer Emp = SQ Wearer Emp + NULL ILP Wearer Emp
      1 linetype, ih.invoiceheader_id,
      (select iltd.description from invoicelinetype_desc iltd where iltd.invoicelinetype_id = 1 and iltd.language_id = l.language_id) invlinetype_desc,
      sum(si.maxinv) changesperweek,
      max(il.price * (100 - il.discountpercentage)/100) invLinePRICE,
      c.customer_id, c.customernumber,
      we.wearernumber,  we.wearername wearerName,
      p.product_id, p.code productCode,
      (select pd.description from product_desc pd where pd.product_id = p.product_id and pd.language_id = l.language_id) productDesc,
       l.code languageCode,
       il.invoicelinetype_id --rent line
      , ih.invoicebusinessunit_id, ih.endweek, ih.endyear,
       ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate,
       ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername,
       ih.street, ih.street2, ih.city, ih.state, ih.zipcode,
       ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code plantCode, pla.name plantName, pla.phoneno plantPhoneNo, pla.faxno plantFax,
       pa.street plantStreet, pa.street2 plantStreet2, pa.city plantCity, pa.state plantState, pa.zipcode plantZipcode
 from sqsetincirculation si
      inner join statusquoheader sh on si.statusquoheader_id = sh.statusquoheader_id      
      inner join weareremployment_link we on si.weareremployment_link_id = we.weareremployment_link_id
      inner join customer c on si.customer_link_id = c.customer_id
      inner join product p on si.product_link_id = p.product_id
      inner join invoicelineproduct ilp
            inner join invoiceline il
                  inner join invoiceheader ih
                        inner join businessunit ibu 
                              inner join plant pla
                                    inner join address pa on pla.address_id = pa.address_id
                              on ibu.plant_id = pla.plant_id
                        on ih.invoicebusinessunit_id = ibu.businessunit_id
                  on il.invoiceheader_id = ih.invoiceheader_id
            on ilp.invoiceline_id = il.invoiceline_id
      on si.product_link_id = ilp.product_link_id and si.finishingmethod_id = ilp.finishingmethod_id
         and (si.sizedefinition_link_id = ilp.sizedefinition_link_id or ilp.sizedefinition_link_id = 
                                          (select sd.sizedefinition_id from sizedefinition sd where sd.code = '*'))
      , LANGUAGES L
 where sh.statusquotype = 2 --ONLY SETS      and si.numberemploymentscharge > 0 -- commented for CBD/PAV
       and coalesce(ilp.weareremployment_link_id, si.weareremployment_link_id) = we.WEAREREMPLOYMENT_LINK_ID
       and il.invoicelinetype_id = 1--rent
       and ilp.billingmethod in (1, 3)
       and ih.deltocustomer_link_id = c.customer_id
       and we.distributionpoint_link_id = si.distributionpoint_link_id
       and exists (select 1 from costreportinfo cri where cri.invoiceline_id = il.invoiceline_id
                                   and cri.year || lpad(cri.week,2,'0') = sh.year || lpad(sh.week,2,'0'))
      group by ih.invoiceheader_id,il.invoicelinetype_id, c.customer_id,c.customernumber,we.wearernumber,we.wearername,p.product_id, p.code,l.language_id,l.code, 
       ih.invoicebusinessunit_id, ih.endweek, ih.endyear, ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate, ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername, ih.street, ih.street2, ih.city, ih.state, ih.zipcode, ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code, pla.name, pla.phoneno, pla.faxno, pa.street, pa.street2, pa.city , pa.state, pa.zipcode
 union all   --RENT - ITEMS - ILP Wearer Emp = SQ Wearer Employment +  NULL ILP Wearer Employment
 select 2 linetype, ih.invoiceheader_id,
        (select iltd.description from invoicelinetype_desc iltd where iltd.invoicelinetype_id = 1 and iltd.language_id = l.language_id) invlinetype_desc,
        sum(case when (select max(plpc.inventorybillingtype)
                 from productlistpercustomer plpc where plpc.customer_id = c.customer_id and plpc.product_id = p.product_id
                 and plpc.sizedefinition_id = case when si.sizedefinition_link_id = plpc.sizedefinition_id
                                         then si.sizedefinition_link_id
                                         else (select sd.sizedefinition_id from sizedefinition sd where sd.code = '*')
                                         end
                 and plpc.finishingmethod_id = si.finishingmethod_id
                 and plpc.dateactive <= ih.invoicedate and plpc.dateinactive >= ih.invoicedate ) = 1 --Show REAL INV
            then si.QtyRentedPerPiece
            else si.maxinv end) changesperweek,
        max(il.price * (100 - il.discountpercentage)/100) invLinePRICE,
        c.customer_id, c.customernumber,
        we.wearernumber,  we.wearername wearerName,
        p.product_id, p.code productCode,
       (select pd.description from product_desc pd where pd.product_id = p.product_id and pd.language_id = l.language_id) productDesc,
       l.code languageCode,
       1 invoicelinetype_id
      , ih.invoicebusinessunit_id, ih.endweek, ih.endyear,
       ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate,
       ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername,
       ih.street, ih.street2, ih.city, ih.state, ih.zipcode,
       ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code plantCode, pla.name plantName, pla.phoneno plantPhoneNo, pla.faxno plantFax,
       pa.street plantStreet, pa.street2 plantStreet2, pa.city plantCity, pa.state plantState, pa.zipcode plantZipcode
 from sqitemincirculation si
      inner join statusquoheader sh on si.statusquoheader_id = sh.statusquoheader_id      
      inner join weareremployment_link we on si.weareremployment_link_id = we.weareremployment_link_id
      inner join customer c on si.customer_link_id = c.customer_id
      inner join product p on si.product_link_id = p.product_id
      inner join invoicelineproduct ilp
            inner join invoiceline il
                  inner join invoiceheader ih
                        inner join businessunit ibu 
                              inner join plant pla
                                    inner join address pa on pla.address_id = pa.address_id
                              on ibu.plant_id = pla.plant_id
                        on ih.invoicebusinessunit_id = ibu.businessunit_id
                  on il.invoiceheader_id = ih.invoiceheader_id
            on ilp.invoiceline_id = il.invoiceline_id
      on si.product_link_id = ilp.product_link_id and si.finishingmethod_id = ilp.finishingmethod_id
         and (si.sizedefinition_link_id = ilp.sizedefinition_link_id or ilp.sizedefinition_link_id = 
                                          (select sd.sizedefinition_id from sizedefinition sd where sd.code = '*'))
      , LANGUAGES L
 where sh.statusquotype = 1 --Items In Circ      and si.numberemploymentscharge > 0 -- commented for CBD/PAV
       and coalesce(ilp.weareremployment_link_id, si.weareremployment_link_id) = we.WEAREREMPLOYMENT_LINK_ID
       and il.invoicelinetype_id = 1 --rent
       and ilp.billingmethod in (1, 3)
       and ih.deltocustomer_link_id = c.customer_id
       and we.distributionpoint_link_id = si.distributionpoint_link_id
       and exists (select 1 from costreportinfo cri where cri.invoiceline_id = il.invoiceline_id
                                   and cri.year || lpad(cri.week,2,'0') = sh.year || lpad(sh.week,2,'0'))
 group by ih.invoiceheader_id,il.invoicelinetype_id, c.customer_id,c.customernumber,we.wearernumber,we.wearername,p.product_id, p.code,l.language_id,l.code, 
       ih.invoicebusinessunit_id, ih.endweek, ih.endyear, ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate, ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername, ih.street, ih.street2, ih.city, ih.state, ih.zipcode, ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code, pla.name, pla.phoneno, pla.faxno, pa.street, pa.street2, pa.city , pa.state, pa.zipcode
 union all -- WASH - ILP Wearer Emp = SQ Wearer Emp + NULL ILP Wearer Emp
 select 3 linetype, ih.invoiceheader_id,
        (select iltd.description from invoicelinetype_desc iltd where iltd.invoicelinetype_id = 2 and iltd.language_id = l.language_id) invlinetype_desc,
        count(ds.uniqueitem_link_id) changesperweek,
        PACK_PRICE.Wash_Price(c.customer_id, p.product_id, ds.sizedefinition_link_id, ds.finishingmethod_id, pn.deliverydate) invLinePRICE,
        c.customer_id, c.customernumber,
        we.wearernumber, we.wearername wearerName,
        p.product_id, p.code productCode,
        (select pd.description from product_desc pd where pd.product_id = p.product_id and pd.language_id = l.language_id) productDesc,
               l.code languageCode, 
        2 invoicelinetype_id
      , ih.invoicebusinessunit_id, ih.endweek, ih.endyear,
       ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate,
       ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername,
       ih.street, ih.street2, ih.city, ih.state, ih.zipcode,
       ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code plantCode, pla.name plantName, pla.phoneno plantPhoneNo, pla.faxno plantFax,
       pa.street plantStreet, pa.street2 plantStreet2, pa.city plantCity, pa.state plantState, pa.zipcode plantZipcode
 from deliveryscan ds
      inner join weareremployment_link we on ds.weareremployment_link_id = we.weareremployment_link_id and ds.distributionpoint_link_id = we.distributionpoint_link_id 
      inner join customer c on ds.customer_link_id = c.customer_id
      inner join packingnote pn 
            inner join packingnoteline pnl 
                  inner join invoiceheader ih
                        inner join businessunit ibu 
                              inner join plant pla
                                    inner join address pa on pla.address_id = pa.address_id
                              on ibu.plant_id = pla.plant_id
                        on ih.invoicebusinessunit_id = ibu.businessunit_id
                  on pnl.invoiceheader_id = ih.invoiceheader_id
            on pn.packingnote_id = pnl.packingnote_id
      on ds.packingnote_id = pn.packingnote_id
      inner join product p on ds.product_link_id = p.product_id
      , LANGUAGES L
 where ds.chargewashing = 'Y' and ds.outscannodelivery in (0,2)
       --and il.invoicelinetype_id = 2 --Delivery (wash)
       and ih.deltocustomer_link_id = c.customer_id
       and pnl.product_id = p.product_id and pnl.sizedefinition_id = ds.sizedefinition_link_id and pnl.finishingmethod_id = ds.finishingmethod_id
 group by ih.invoiceheader_id, c.customer_id, c.customernumber,we.wearernumber, we.wearername, p.product_id, p.code, l.language_id, l.code, 
       ih.invoicebusinessunit_id, ih.endweek, ih.endyear, ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate, ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername, ih.street, ih.street2, ih.city, ih.state, ih.zipcode, ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code, pla.name, pla.phoneno, pla.faxno, pa.street, pa.street2, pa.city, pa.state, pa.zipcode, 
       PACK_PRICE.Wash_Price(c.customer_id, p.product_id, ds.sizedefinition_link_id, ds.finishingmethod_id, pn.deliverydate)
 union all -- REPAIR
 select 4 linetype, ih.invoiceheader_id,
      (select rtd.description from repairtype_desc rtd where rh.repairtype_id = rtd.repairtype_id and rtd.language_id = l.language_id) invlinetype_desc,
      sum(rh.quantity) changesperweek,
      coalesce(rh.unitprice, 0) invLinePRICE,
      c.customer_id, c.customernumber,
      we.wearernumber,  we.wearername wearerName,
      p.product_id, p.code productCode,
      (select pd.description from product_desc pd where pd.product_id = p.product_id and pd.language_id = l.language_id) productDesc,
      l.code languageCode, 
      6 invoicelinetype_id--repair charges
      , ih.invoicebusinessunit_id, ih.endweek, ih.endyear,
       ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate,
       ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername,
       ih.street, ih.street2, ih.city, ih.state, ih.zipcode,
       ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code plantCode, pla.name plantName, pla.phoneno plantPhoneNo, pla.faxno plantFax,
       pa.street plantStreet, pa.street2 plantStreet2, pa.city plantCity, pa.state plantState, pa.zipcode plantZipcode
 from invoiceheader ih
      inner join repairhistory rh 
           left outer join weareremployment_link we on rh.weareremployment_link_id = we.weareremployment_link_id
           left outer join product p on rh.product_link_id = p.product_id 
      on rh.invoiceheader_id = ih.invoiceheader_id 
      inner join customer c on ih.deltocustomer_link_id = c.customer_id
      inner join businessunit ibu 
            inner join plant pla
                  inner join address pa on pla.address_id = pa.address_id
            on ibu.plant_id = pla.plant_id
      on ih.invoicebusinessunit_id = ibu.businessunit_id
 , LANGUAGES L
 where rh.status = 0
       and coalesce(we.distributionpoint_link_id, rh.distributionpoint_link_id) = rh.distributionpoint_link_id
 group by ih.invoiceheader_id, rh.repairtype_id, c.customer_id,c.customernumber,we.wearernumber,we.wearername,p.product_id, p.code,l.language_id,l.code, 
       ih.invoicebusinessunit_id, ih.endweek, ih.endyear, ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate, ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername, ih.street, ih.street2, ih.city, ih.state, ih.zipcode, ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code, pla.name, pla.phoneno, pla.faxno, pa.street, pa.street2, pa.city , pa.state, pa.zipcode, 
       rh.unitprice
 union all -- SPECIAL CHARGES
 select 5 linetype, ih.invoiceheader_id,
      il.description invlinetype_desc,
      sum(il.billedqty) changesperweek,
      case when (il.price * (100 - il.discountpercentage)/100) = 0 
           then case when il.billedqty = 0 then 0 else il.amount / il.billedqty end
      else (il.price * (100 - il.discountpercentage)/100) end invLinePRICE,
      c.customer_id, c.customernumber,
      we.wearernumber,  we.wearername wearerName,
      p.product_id, p.code productCode,
      (select pd.description from product_desc pd where pd.product_id = p.product_id and pd.language_id = l.language_id) productDesc,
      l.code languageCode, 
      il.invoicelinetype_id --special charges
      , ih.invoicebusinessunit_id, ih.endweek, ih.endyear,
       ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate,
       ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername,
       ih.street, ih.street2, ih.city, ih.state, ih.zipcode,
       ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code plantCode, pla.name plantName, pla.phoneno plantPhoneNo, pla.faxno plantFax,
       pa.street plantStreet, pa.street2 plantStreet2, pa.city plantCity, pa.state plantState, pa.zipcode plantZipcode
 from invoiceheader ih
      inner join invoiceline il
            left outer join invoicelineproduct ilp
                  left outer join product p on ilp.product_link_id = p.product_id 
                  left outer join weareremployment_link we on ilp.weareremployment_link_id = we.WEAREREMPLOYMENT_LINK_ID
            on ilp.invoiceline_id = il.invoiceline_id
      on ih.invoiceheader_id = il.invoiceheader_id
      inner join customer c on ih.deltocustomer_link_id = c.customer_id
      inner join businessunit ibu 
            inner join plant pla
                  inner join address pa on pla.address_id = pa.address_id
            on ibu.plant_id = pla.plant_id
      on ih.invoicebusinessunit_id = ibu.businessunit_id
      , LANGUAGES L
 where il.invoicelinetype_id in (16)--Special Charges
 group by ih.invoiceheader_id,il.invoicelinetype_id, c.customer_id,c.customernumber,we.wearernumber,we.wearername,p.product_id, p.code,l.language_id,l.code, 
       ih.invoicebusinessunit_id, ih.endweek, ih.endyear, ih.invoicedate, ih.invoicenumber, ih.purchaseorder, ih.startdate, ih.enddate, ih.billtocustomer_link_id, ih.billtocustomernumber, ih.billtocustomername,
       ih.deltocustomer_link_id, ih.deltocustomernumber, ih.deltocustomername, ih.street, ih.street2, ih.city, ih.state, ih.zipcode, ih.deltostreet, ih.deltostreet2, ih.deltocity, ih.deltostate, ih.deltozipcode,
       pla.plant_id, pla.code, pla.name, pla.phoneno, pla.faxno, pa.street, pa.street2, pa.city , pa.state, pa.zipcode, 
       case when (il.price * (100 - il.discountpercentage)/100) = 0 
       then case when il.billedqty = 0 then 0 else il.amount / il.billedqty end
       else (il.price * (100 - il.discountpercentage)/100) end, il.description
) x
/
