create or replace view v_lossforpoolperproductdept as
select cl.customernumber,
       cl.name,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate periodenddate,
       iflp.year,
       iflp.period,
       coalesce(iflp.enddate-iflp.cutoffdate+ 1, (select max(plpc.chargelossuniquepooldays )
             from productlistpercustomer plpc where plpc.customer_id=cl.customer_id and plpc.product_id = ui.product_id
                  and plpc.sizedefinition_id = ui.sizedefinition_id and plpc.finishingmethod_id = ui.finishingmethod_id
                  and plpc.dateactive <= sysdate and plpc.dateinactive > sysdate )) daysinperiod,
       1 chargetype,
       p.code productcode,
       (select pd.description
          from product_desc pd
         where pd.product_id = ui.product_id
           and pd.language_id = c.language_id) productdescription,
       sum(uil.creditamount) amount, count(1) qty, uil.lossstatus,
       (select sd.code from sizedefinition sd where sd.sizedefinition_id = ui.sizedefinition_id) size_code,
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = ui.sizedefinition_id and sdd.language_id = c.language_id) size_desc,
       (select lsg.code from lossgracescheme lsg where lsg.lossgracescheme_id = c.lossgracescheme_id) lossgraceschemecode
from uniqueitemloss         uil
       left outer join invoicefrequencylossperiod iflp on uil.invoicefrequencylossperiod_id = iflp.invoicefrequencylossperiod_id,
       invoiceheader          ih,
       customer_link          cl
       left outer join customer c on c.customer_id = cl.customer_id,
       distributionpoint_link dpl,
       department_link        dl,       --lossperiods            lp,
       uniqueitem             ui,
       product                p
where uil.creditinvoiceheader_id = ih.invoiceheader_id
   and uil.customer_link_id = cl.customer_link_id
   and uil.distributionpoint_link_id = dpl.distributionpoint_link_id
   and dpl.department_link_id = dl.department_link_id   --and uil.lossperiods_id = lp.lossperiods_id
   and uil.uniqueitem_link_id = ui.uniqueitem_id
   and ui.product_id = p.product_id
group by cl.customernumber,
       cl.name,
       ih.currency_id,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate,
       iflp.year,
       iflp.period,
       iflp.enddate-iflp.cutoffdate+ 1,
       p.code,
       ui.product_id, ui.sizedefinition_id, ui.finishingmethod_id, cl.customer_id, uil.creditamount, uil.lossstatus, c.language_id, c.lossgracescheme_id
union all
select cl.customernumber,
       cl.name,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate periodenddate,
       iflp.year,
       iflp.period,
       coalesce(iflp.enddate-iflp.cutoffdate+ 1, (select max(plpc.chargelossuniquepooldays )
             from productlistpercustomer plpc where plpc.customer_id=cl.customer_id and plpc.product_id = ui.product_id
                  and plpc.sizedefinition_id = ui.sizedefinition_id and plpc.finishingmethod_id = ui.finishingmethod_id
                  and plpc.dateactive <= sysdate and plpc.dateinactive > sysdate )) daysinperiod,
       2 chargetype,
       p.code productcode,
       (select pd.description
          from product_desc pd
         where pd.product_id = ui.product_id
           and pd.language_id = c.language_id) productdescription,
       sum(uil.lossamount) amount, count(1) qty, uil.lossstatus,
       (select sd.code from sizedefinition sd where sd.sizedefinition_id = ui.sizedefinition_id) size_code,
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = ui.sizedefinition_id and sdd.language_id = c.language_id) size_desc,
       (select lsg.code from lossgracescheme lsg where lsg.lossgracescheme_id = c.lossgracescheme_id) lossgraceschemecode
from uniqueitemloss         uil
       left outer join invoicefrequencylossperiod iflp on uil.invoicefrequencylossperiod_id = iflp.invoicefrequencylossperiod_id,
       invoiceheader          ih,
       customer_link          cl
       left outer join customer c on c.customer_id = cl.customer_id,
       distributionpoint_link dpl,
       department_link        dl,       --lossperiods            lp,
       uniqueitem             ui,
       product                p
where uil.lossinvoiceheader_id = ih.invoiceheader_id
   and uil.customer_link_id = cl.customer_link_id
   and uil.distributionpoint_link_id = dpl.distributionpoint_link_id
   and dpl.department_link_id = dl.department_link_id   --and uil.lossperiods_id = lp.lossperiods_id
   and uil.uniqueitem_link_id = ui.uniqueitem_id
   and ui.product_id = p.product_id
group by cl.customernumber,
       cl.name,
       ih.currency_id,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate,
       iflp.year,
       iflp.period,
       iflp.enddate-iflp.cutoffdate+ 1,
       p.code,
       ui.product_id, ui.sizedefinition_id, ui.finishingmethod_id, cl.customer_id, uil.lossamount, uil.lossstatus, c.language_id, c.lossgracescheme_id
union all
select cl.customernumber,
       cl.name,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate periodenddate,
       iflp.year,
       iflp.period,
       coalesce(iflp.enddate-iflp.cutoffdate+ 1, (select max(plpc.chargelossuniquepooldays )
             from productlistpercustomer plpc where plpc.customer_id=cl.customer_id and plpc.product_id = ri.product_link_id
                  and plpc.sizedefinition_id = ri.sizedefinition_link_id --and plpc.finishingmethod_id = any
                  and plpc.dateactive <= sysdate and plpc.dateinactive > sysdate )) daysinperiod,
       1 chargetype,
       p.code productcode,
       (select pd.description
          from product_desc pd
         where pd.product_id = ri.product_link_id
           and pd.language_id = c.language_id) productdescription,
       sum(uil.creditamount) amount, count(1) qty, uil.lossstatus,
       (select sd.code from sizedefinition sd where sd.sizedefinition_id = ri.sizedefinition_link_id) size_code,
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = ri.sizedefinition_link_id and sdd.language_id = c.language_id) size_desc,
       (select lsg.code from lossgracescheme lsg where lsg.lossgracescheme_id = c.lossgracescheme_id) lossgraceschemecode
from uniqueitemloss         uil
       left outer join invoicefrequencylossperiod iflp on uil.invoicefrequencylossperiod_id = iflp.invoicefrequencylossperiod_id,
       invoiceheader          ih,
       customer_link          cl
       left outer join customer c on c.customer_id = cl.customer_id,
       distributionpoint_link dpl,
       department_link        dl,       --lossperiods            lp,
       raggeditem             ri,
       product                p
where uil.creditinvoiceheader_id = ih.invoiceheader_id
   and uil.customer_link_id = cl.customer_link_id
   and uil.distributionpoint_link_id = dpl.distributionpoint_link_id
   and dpl.department_link_id = dl.department_link_id   --and uil.lossperiods_id = lp.lossperiods_id
   and uil.uniqueitem_link_id = ri.raggeditem_id
   and ri.product_link_id = p.product_id
group by  cl.customernumber,
       cl.name,
       ih.currency_id,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate,
       iflp.year,
       iflp.period,
       iflp.enddate-iflp.cutoffdate+ 1,
       p.code,
       ri.product_link_id, ri.sizedefinition_link_id, cl.customer_id, uil.creditamount, uil.lossstatus, c.language_id, c.lossgracescheme_id
union all
select cl.customernumber,
       cl.name,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate periodenddate,
       iflp.year,
       iflp.period,
       coalesce(iflp.enddate-iflp.cutoffdate+ 1, (select max(plpc.chargelossuniquepooldays )
             from productlistpercustomer plpc where plpc.customer_id=cl.customer_id and plpc.product_id = ri.product_link_id
                  and plpc.sizedefinition_id = ri.sizedefinition_link_id --and plpc.finishingmethod_id = any
                  and plpc.dateactive <= sysdate and plpc.dateinactive > sysdate )) daysinperiod,
       2 chargetype,
       p.code productcode,
       (select pd.description
          from product_desc pd
         where pd.product_id = ri.product_link_id
           and pd.language_id = c.language_id) productdescription,
       sum(uil.lossamount) amount, count(1) qty, uil.lossstatus,
       (select sd.code from sizedefinition sd where sd.sizedefinition_id = ri.sizedefinition_link_id) size_code,
       (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = ri.sizedefinition_link_id and sdd.language_id = c.language_id) size_desc,
       (select lsg.code from lossgracescheme lsg where lsg.lossgracescheme_id = c.lossgracescheme_id) lossgraceschemecode
from uniqueitemloss         uil
       left outer join invoicefrequencylossperiod iflp on uil.invoicefrequencylossperiod_id = iflp.invoicefrequencylossperiod_id,
       invoiceheader          ih,
       customer_link          cl
       left outer join customer c on c.customer_id = cl.customer_id,
       distributionpoint_link dpl,
       department_link        dl,       --lossperiods            lp,
       raggeditem             ri,
       product                p
where uil.lossinvoiceheader_id = ih.invoiceheader_id
   and uil.customer_link_id = cl.customer_link_id
   and uil.distributionpoint_link_id = dpl.distributionpoint_link_id
   and dpl.department_link_id = dl.department_link_id   --and uil.lossperiods_id = lp.lossperiods_id
   and uil.uniqueitem_link_id = ri.raggeditem_id
   and ri.product_link_id = p.product_id
group by cl.customernumber,
       cl.name,
       ih.currency_id,
       dl.departmentnumber,
       dl.description,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.invoicebusinessunit_id,
       iflp.enddate,
       iflp.year,
       iflp.period,
       iflp.enddate-iflp.cutoffdate+ 1,
       p.code,
       ri.product_link_id, ri.sizedefinition_link_id, cl.customer_id, uil.lossamount, uil.lossstatus, c.language_id, c.lossgracescheme_id
/