create or replace view v_invoiceappendixlossforpool as
select uil.uniqueitemloss_id, --credit
       uil.uniqueitem_link_id,
       cl.customernumber,
       cl.name,
       dl.departmentnumber,
       dl.description,
       dpl.distributionpointnumber,
       dpl.description distributionpointdescription,
       (select cc.code
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcentercode,
       (select cc.description
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcenterdescription,
       uil.creditinvoiceheader_id,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.startdate,
       ih.enddate,
       ih.invoicebusinessunit_id,
       ih.invoicefrequency_id,
       ih.cluster_id,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       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 = p.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,
       trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) outscandate,
       uil.returneddate,
       uil.returneddate - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1 daysofuse,
       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,
       ui.primaryid,
       uil.creditamount amount,
       uil.losschargetrigger,
       uil.lastscandate,
       (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
   and uil.lossstatus <> 5
union
select uil.uniqueitemloss_id, --loss charges
       uil.uniqueitem_link_id,
       cl.customernumber,
       cl.name,
       dl.departmentnumber,
       dl.description,
       dpl.distributionpointnumber,
       dpl.description distributionpointdescription,
       (select cc.code
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcentercode,
       (select cc.description
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcenterdescription,
       uil.lossinvoiceheader_id,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.startdate,
       ih.enddate,
       ih.invoicebusinessunit_id,
       ih.invoicefrequency_id,
       ih.cluster_id,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       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 = p.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,
       trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) outscandate,
       null returneddate,
       (case
         when uil.returneddate = to_date('31-12-2099', 'dd-mm-yyyy') then
          trunc(coalesce(iflp.enddate, ih.enddate)) - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1
         else
          uil.returneddate - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1
       end) daysofuse,
       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,
       ui.primaryid,
       uil.lossamount amount,
       uil.losschargetrigger,
       uil.lastscandate,
       (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
   and uil.lossstatus <> 5
union
select uil.uniqueitemloss_id, --credit ragged
       uil.uniqueitem_link_id,
       cl.customernumber,
       cl.name,
       dl.departmentnumber,
       dl.description,
       dpl.distributionpointnumber,
       dpl.description distributionpointdescription,
       (select cc.code
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcentercode,
       (select cc.description
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcenterdescription,
       uil.creditinvoiceheader_id,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.startdate,
       ih.enddate,
       ih.invoicebusinessunit_id,
       ih.invoicefrequency_id,
       ih.cluster_id,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       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 = p.product_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,
       trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) outscandate,
       uil.returneddate,
       uil.returneddate - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1 daysofuse,
       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,
       ri.primaryid,
       uil.creditamount amount,
       uil.losschargetrigger,
       uil.lastscandate,
       (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
   and uil.lossstatus <> 5
union
select uil.uniqueitemloss_id, --loss charges ragged
       uil.uniqueitem_link_id,
       cl.customernumber,
       cl.name,
       dl.departmentnumber,
       dl.description,
       dpl.distributionpointnumber,
       dpl.description distributionpointdescription,
       (select cc.code
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcentercode,
       (select cc.description
          from costcenter cc, distributionpoint dp
         where dp.costcenter_id = cc.costcenter_id
           and dpl.distributionpoint_id = dp.distributionpoint_id) costcenterdescription,
       uil.lossinvoiceheader_id,
       ih.invoiceheader_id,
       ih.invoicenumber,
       ih.invoicedate,
       ih.startdate,
       ih.enddate,
       ih.invoicebusinessunit_id,
       ih.invoicefrequency_id,
       ih.cluster_id,
       (select cc.code from currency cc where cc.currency_id = ih.currency_id) currencycode,
       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 = p.product_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,
       trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) outscandate,
       null returneddate,
       (case
         when uil.returneddate = to_date('31-12-2099', 'dd-mm-yyyy') then
          trunc(coalesce(iflp.enddate, ih.enddate)) - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1
         else
          uil.returneddate - trunc(decode(uil.losschargetrigger,1,uil.lastscandate,uil.outscandate)) + 1
       end) daysofuse,
       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,
       ri.primaryid,
       uil.lossamount amount,
       uil.losschargetrigger,
       uil.lastscandate,
       (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
   and uil.lossstatus <> 5
/