create or replace package pack_lossforpool is

  -- author  : alin.n
  -- created : 01-08-2011 13:19:16
  -- purpose : loss for pool scheduled process
  -- updated : 20-02-2014 HVH order 20014641 Redesign Loss for Pool Module
  -- updated : 16-06-2014 HVH order 20015191 Added option to bill based on last scandate in addition to outscandate
  -- public function and procedure declarations
  function lossforpoolprocess(fp_plantprocessid in plantprocess.plantprocess_id%type)
    return number;

end pack_lossforpool;
/
create or replace package body pack_lossforpool is

  -- private type declarations
  gv_userexception exception;

  -- private constant declarations
  return_ok                    constant integer := 0;
  return_failed_no_lossperiod  constant integer := 1;
  return_failed_unknown        constant integer := -1;
  gc_max_date                  constant date    := to_date('31-12-2099', 'dd-mm-yyyy');

  priority_message constant abslogprocessjobline.priority%type := 1;
--priority_warning constant abslogprocessjobline.priority%type := 2;
  priority_error constant abslogprocessjobline.priority%type   := 3;
  priority_debug constant abslogprocessjobline.priority%type   := 5;


  -- private variable declarations
  gv_processname            abslogprocessjobpart.processname%type;
  gv_systemuserid           systemuser.systemuser_id%type := 0;
  gv_abslogprocessjobid     abslogprocessjob.abslogprocessjob_id%type;
  gv_abslogprocessjobpartid abslogprocessjobpart.abslogprocessjobpart_id%type;
  gv_message                abslog.message%type;
  gv_returnvalue            number := return_ok;
  gv_plantid                plant.plant_id%type;


  ---------------------------------------------------------------------------------------------------
  procedure write_log(fp_message  in abslogprocessjobline.message%type,
                      fp_priority in abslogprocessjobline.priority%type default priority_message) is
  begin
    --write_log part
    if gv_abslogprocessjobpartid is null and
       gv_abslogprocessjobid is not null then
      gv_abslogprocessjobpartid := abslogwrite.abslogprocessjobpart_write(gv_abslogprocessjobid,
                                                                          gv_processname,
                                                                          gv_systemuserid);
    end if;
    if (gv_abslogprocessjobpartid is not null) then
      abslogwrite.abslogprocessjobline_write(gv_abslogprocessjobpartid,
                                             fp_priority,
                                             fp_message,
                                             gv_systemuserid);
    end if;
  exception
    when others then
      begin
        dbms_output.put_line(fp_message);
        dbms_output.put_line(sqlerrm);
        gv_returnvalue := return_failed_unknown;
        raise gv_userexception;
      end;
  end write_log;


  ---------------------------------------------------------------------------------------------------
  procedure end_log is
  begin
    --end_log
    if gv_abslogprocessjobid is not null then
      abslogwrite.abslogprocessjob_update(gv_abslogprocessjobid,
                                          sysdate,
                                          gv_systemuserid);
      gv_abslogprocessjobid := null;
    end if;
  exception
    when others then
      begin
        dbms_output.put_line(gv_message);
        dbms_output.put_line(sqlerrm);
      end;
  end end_log;

  ---------------------------------------------------------------------------------------------------
  procedure get_abslogprocessjobid(fp_plantprocessid in plantprocess.plantprocess_id%type,
                                   fp_plantid        in plant.plant_id%type) is
  begin
    --write_log
    gv_abslogprocessjobid := abslogwrite.abslogprocessjob_write(fp_plantid,
                                                                sysdate,
                                                                null,
                                                                null,
                                                                fp_plantprocessid,
                                                                gv_systemuserid);
  end get_abslogprocessjobid;

  -------------------------- MAIN ---------------------------------------------------------------------
  function lossforpoolprocess(fp_plantprocessid in plantprocess.plantprocess_id%type)
    return number is
    lv_nr_of_lossperiods number;
    lv_lossforpoolmethod number;
    lv_lossforpoolprice  number;

    cursor cust is
      select c.customer_id,
             v_period.cutoffdate,
             v_period.invoicefrequencylossperiod_id lossperiod_id,
             cu.invoiceamountdecimals decimals,
             c.losschargetrigger losschargetrigger
        from customer      c,
             businessunit  b,
             systemsetting s,
             currency     cu,
             (select iflp.invoicefrequency_id,
                     iflp.cutoffdate,
                     iflp.invoicefrequencylossperiod_id
                from invoicefrequencylossperiod iflp
               where iflp.enddate =
                     (select max(iflp2.enddate)
                        from invoicefrequencylossperiod iflp2
                       where iflp2.invoicefrequency_id = iflp.invoicefrequency_id
                         and iflp2.enddate <= trunc(sysdate))) v_period
       where c.chargelossforpoolitems = 1
         and b.businessunit_id = c.defaultbusinessunit_id
         and b.plant_id = gv_plantid
         and cu.currency_id = c.currency_id
         and v_period.invoicefrequency_id = c.invoicefrequency_id
         and s.plant_id = b.plant_id
         and s.name = 'LOSSFORPOOLMETHOD'
         and to_number(nvl(s.value, s.factorydefault))=1 -- loss for pool method = 1
    union all
      select c.customer_id,
             trunc(sysdate) - c.chargelossuniquepooldays custoffdate,
             null lossperiod_id,
             cu.invoiceamountdecimals decimals,
             c.losschargetrigger losschargetrigger
        from customer      c,
             businessunit  b,
             systemsetting s,
             currency      cu
       where c.chargelossforpoolitems = 1
         and b.businessunit_id = c.defaultbusinessunit_id
         and b.plant_id = gv_plantid
         and cu.currency_id = c.currency_id
         and s.plant_id = b.plant_id
         and s.name = 'LOSSFORPOOLMETHOD'
         and to_number(nvl(s.value, s.factorydefault))=2;  -- loss for pool method = 2


  begin
    -- STEP 0 Initialization -----------------------------------------------------------------------
    gv_abslogprocessjobid := null;
    gv_abslogprocessjobpartid := null;
    gv_processname        := 'PACK_LOSSFORPOOL.LOSSFORPOOLPROCESS';
    gv_message            := 'STEP 0 Initialization';
    gv_returnvalue        := return_ok;

    --check parameter not null
    if fp_plantprocessid is null then
      begin
        gv_message     := 'Parameter fp_plantprocessid cannot be left empty!';
        write_log(gv_message, priority_error);
        gv_returnvalue := return_failed_unknown;
        raise gv_userexception;
      end;
    end if;

    --get plant id
    gv_message := 'Get plant_id for plantprocess_id ' || to_char(fp_plantprocessid);
    select pp.plant_id, pp.systemuser_id
      into gv_plantid, gv_systemuserid
      from plantprocess pp
     where pp.plantprocess_id = fp_plantprocessid;
    --get abslog process job id
    get_abslogprocessjobid(fp_plantprocessid, gv_plantid);

    write_log('START PACK_LOSSFORPOOL.LOSSFORPOOLPROCESS');

    -- get system setting LOSSFORPOOLMETHOD
    gv_message := 'Read systemsetting LOSSFORPOOLMETHOD for plant_id ' || to_char(gv_plantid);
    select to_number(nvl(ss.value, ss.factorydefault))
      into lv_lossforpoolmethod
      from systemsetting ss
     where ss.plant_id = gv_plantid
       and ss.name = 'LOSSFORPOOLMETHOD';

    -- get system setting LOSSFORPOOLPRICE
    gv_message := 'Read systemsetting LOSSFORPOOLPRICE for plant_id ' || to_char(gv_plantid);
    select to_number(nvl(ss.value, ss.factorydefault))
      into lv_lossforpoolprice
      from systemsetting ss
     where ss.plant_id = gv_plantid
       and ss.name = 'LOSSFORPOOLPRICE';

    if lv_lossforpoolmethod = 1 then
      gv_message := 'Check for existence loss for pool periods per invoice frequency';
      lv_nr_of_lossperiods := 0;
      for j in (select ifc.invoicefrequency_id, ifc.factorydescription
                  from invoicefrequency ifc,
                       plantinvoicefrequency pif
                 where pif.invoicefrequency_id = ifc.invoicefrequency_id
                   and pif.plant_id = gv_plantid
                   and ifc.invoicefrequencynumber <> 200  -- exclude manual
                   and exists
                       (select c.customer_id
                          from customer c, businessunit b
                         where c.invoicefrequency_id = ifc.invoicefrequency_id
                           and c.chargelossforpoolitems = 1
                           and b.businessunit_id = c.defaultbusinessunit_id
                           and b.plant_id = gv_plantid)
               )
        loop
          select count(1)
            into lv_nr_of_lossperiods
            from invoicefrequencylossperiod iflp
           where iflp.invoicefrequency_id = j.invoicefrequency_id
             and iflp.enddate >= trunc(sysdate);
            if (lv_nr_of_lossperiods = 0) then
              gv_message     := 'There are no current or future loss charge periods defined for invoice frequency ' || j.factorydescription || ' Please define periods.';
              write_log(gv_message, priority_error);
              gv_returnvalue := return_failed_no_lossperiod;
            end if;
        end loop;
      if gv_returnvalue = return_failed_no_lossperiod then
        raise gv_userexception;
      end if;
    end if; --  lv_lossforpoolmethod = 1

    -- STEP 1 Credit Returned Items -----------------------------------------------------------------------
    gv_message := 'STEP 1 Credit Returned Items';
    write_log(gv_message);
    merge into uniqueitemloss using
          (select uil.uniqueitemloss_id,
                  nvl((select lgsd.percentage
                         from lossgraceschemedetail lgsd
                        where lgsd.lossgracescheme_id = c.lossgracescheme_id
                          and lgsd.numberofdays =
                             (select min(lgsd2.numberofdays)
                                from lossgraceschemedetail lgsd2
                               where lgsd2.lossgracescheme_id = c.lossgracescheme_id
                                 and lgsd2.numberofdays >= uil.returneddate - h.invoicedate + 1)),0) percentage,
                  uil.lossamount,
                  cu.invoiceamountdecimals decimals
             from uniqueitemloss uil,
                  uniqueitem     ui,
                  businessunit   b,
                  invoiceheader  h,
                  currency       cu,
                  customer       c
            where ui.uniqueitem_id = uil.uniqueitem_link_id
              and b.businessunit_id = c.defaultbusinessunit_id
              and b.plant_id = gv_plantid
              and uil.lossstatus = 1 -- charged for loss
              and uil.returneddate <> gc_max_date --to_date('31/12/2099', 'dd/mm/yyyy') -- this means returned
              and h.invoiceheader_id = uil.lossinvoiceheader_id
              and cu.currency_id = h.currency_id
              and c.customer_id = uil.customer_link_id
          ) merge_subquery
       on (uniqueitemloss.uniqueitemloss_id = merge_subquery.uniqueitemloss_id)
    when matched then update
       set lossstatus    = case
                             when merge_subquery.percentage = 0 then 4  -- 4 = returned not credited
                             when merge_subquery.percentage = 100 then 2 -- 2 = returned credited 100%
                             else 3 -- returned credited < 100%
                           end,
           creditamount  = case
                             when merge_subquery.percentage = 0 then 0
                             when merge_subquery.percentage = 100 then merge_subquery.lossamount * -1
                             else round(merge_subquery.lossamount * merge_subquery.percentage / 100 * -1,
                                        merge_subquery.decimals)
                           end,
           systemuser_id = gv_systemuserid,
           timestamp     = sysdate;
    commit;


    -- STEP 2 Update items that are returned before loss is charged -------------------------------------------
    gv_message := 'STEP 2 Update items that are returned before loss is charged';
    write_log(gv_message);
    merge into uniqueitemloss using
          (select uil.uniqueitemloss_id
             from uniqueitemloss uil,
                  customer c,
                  businessunit b
            where c.customer_id = uil.customer_link_id
              and b.businessunit_id = c.defaultbusinessunit_id
              and b.plant_id = gv_plantid
              and uil.lossstatus = 0
              and uil.returneddate <>  gc_max_date --to_date('31/12/2099', 'dd/mm/yyyy') -- this means returned
          ) merge_subquery
       on (uniqueitemloss.uniqueitemloss_id = merge_subquery.uniqueitemloss_id)
    when matched then update
       set lossstatus    = 5,
           lossamount    = 0,
           creditamount  = 0,
           systemuser_id = gv_systemuserid,
           timestamp     = sysdate;
    commit;


    -- STEP 3 Charge loss for not returned items  -------------------------------------------------------
    gv_message := 'STEP 3 Charge loss for not returned items';
    write_log(gv_message);
    for vcursor1 in cust loop
        -- step 3A exact match on plpc.size
        insert into uniqueitemloss
          (uniqueitemloss_id,
           uniqueitem_link_id,
           customer_link_id,
           distributionpoint_link_id,
           lossstatus,
           outscandate,
           returneddate,
           lossinvoiceheader_id,
           creditinvoiceheader_id,
           lossamount,
           creditamount,
           systemuser_id,
           timestamp,
           invoicefrequencylossperiod_id,
           losschargetrigger,
           lastscandate,
           losschargedate
           )
        select /*+ index(ui XIF12UNIQUEITEM) */    -- force using index on distribution point
           seq_uniqueitemloss.nextval,
           ui.uniqueitem_id,
           vcursor1.customer_id,
           ui.distributionpoint_id,
           0,
           ui.lastoutscandate,
           gc_max_date,
           null,
           null,
           case when lv_lossforpoolprice = 2 -- then charge residual value, else full replacement price
           then
             round(ap.price *
             (case
               when ap.calculationbasis = 1 -- 1=weeks in use, 2=washes
                then
                get_depreciation_percent(ap.residualvaluescheme_id,
                                         ceil((uif.daysincircprevissue +
                                              (trunc(sysdate) -
                                              uif.lastissuedate)) / 7))
               else
                get_depreciation_percent(ap.residualvaluescheme_id,
                                         ui.washestotal)
             end) / 100, vcursor1.decimals)     -- round(lv_price * lv_percetange / 100, lv_decimals);
           else ap.price
           end, -- detetermine price
           0,
           gv_systemuserid,
           sysdate,
           vcursor1.lossperiod_id,
           vcursor1.losschargetrigger,
           case when vcursor1.losschargetrigger=1
            then ui.lastscandate
            else null
           end lastscandate,
           sysdate
        from uniqueitem ui,
             uniqueitemfixedinfo uif,
             department dep,
             distributionpoint dp,
             (select plpc.customer_id,
                     plpc.product_id,
                     plpc.sizedefinition_id,
                     plpc.finishingmethod_id,
                     plpc.dateactive,
                     plpc.dateinactive,
                     plpc.chargelossuniquepooldays,
                     plpc.residualvaluescheme_id,
                     rv.calculationbasis,
                     case when lv_lossforpoolmethod = 1
                        then vcursor1.cutoffdate
                        else trunc(sysdate) - plpc.chargelossuniquepooldays
                     end cutoffdate,
                     pack_price.replacement_price(plpc.customer_id,
                                                  plpc.product_id,
                                                  plpc.sizedefinition_id,
                                                  plpc.finishingmethod_id) price
                from productlistpercustomer plpc,
                     product                p,
                     residualvaluescheme    rv
               where plpc.customer_id = vcursor1.customer_id  -- only for customer in this iteration
                 and rv.residualvaluescheme_id = plpc.residualvaluescheme_id
                 and p.product_id = plpc.product_id
                 and p.chargelossforpool = 'Y'
                 and plpc.chargelossforpool = 'Y' --(charge loss for unreturned pool)
             ) ap  -- inline view for applicable_products
       where ui.poolitem = 1 --(pool item)
         and ui.distributionpoint_id = dp.distributionpoint_id
         and dp.department_id = dep.department_id
         and dep.customer_id = vcursor1.customer_id  -- only for customer in this iteration
         and uif.uniqueitem_id = ui.uniqueitem_id
         and ui.lastoutscandate is not null
         and ap.customer_id = dep.customer_id
         and ap.product_id = ui.product_id
         and ap.sizedefinition_id = ui.sizedefinition_id
         and ap.finishingmethod_id = ui.finishingmethod_id
         and (
             (vcursor1.losschargetrigger = 0 -- based on outscan date
              and ap.dateactive <= trunc(ui.lastoutscandate)
              and ap.dateinactive >= trunc(ui.lastoutscandate)
              and trunc(ui.lastoutscandate) <= ap.cutoffdate)  -- most important: lastoutscandate before cutoffdate
              or
             (vcursor1.losschargetrigger = 1 -- based on outscan date
              and ap.dateactive <= trunc(ui.lastscandate)
              and ap.dateinactive >= trunc(ui.lastscandate)
              and trunc(ui.lastscandate) <= ap.cutoffdate)  -- most important: lastscandate before cutoffdate
             )
              and not exists  -- and not already marked as lost via outscan date
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and trunc(uil.outscandate) = trunc(ui.lastoutscandate))
              and not exists  -- and not already marked as lost via last scan date
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and trunc(uil.lastscandate) = trunc(ui.lastscandate));
        commit;

        -- step 3B size * in plpc
        insert into uniqueitemloss
          (uniqueitemloss_id,
           uniqueitem_link_id,
           customer_link_id,
           distributionpoint_link_id,
           lossstatus,
           outscandate,
           returneddate,
           lossinvoiceheader_id,
           creditinvoiceheader_id,
           lossamount,
           creditamount,
           systemuser_id,
           timestamp,
           invoicefrequencylossperiod_id,
           losschargetrigger,
           lastscandate,
           losschargedate
           )
        select /*+ index(ui XIF12UNIQUEITEM) */    -- force using index on distribution point
           seq_uniqueitemloss.nextval,
           ui.uniqueitem_id,
           vcursor1.customer_id,
           ui.distributionpoint_id,
           0,
           ui.lastoutscandate,
           gc_max_date,
           null,
           null,
           case when lv_lossforpoolprice = 2 -- then charge residual value, else full replacement price
           then
             round(ap.price *
             (case
               when ap.calculationbasis = 1 -- 1=weeks in use, 2=washes
                then
                get_depreciation_percent(ap.residualvaluescheme_id,
                                         ceil((uif.daysincircprevissue +
                                              (trunc(sysdate) -
                                              uif.lastissuedate)) / 7))
               else
                get_depreciation_percent(ap.residualvaluescheme_id,
                                         ui.washestotal)
             end) / 100, vcursor1.decimals)     -- round(lv_price * lv_percetange / 100, lv_decimals);
           else ap.price
           end, -- detetermine price
           0,
           gv_systemuserid,
           sysdate,
           vcursor1.lossperiod_id,
           vcursor1.losschargetrigger,
           case when vcursor1.losschargetrigger=1
            then ui.lastscandate
            else null
           end lastscandate,
           sysdate
        from uniqueitem ui,
             uniqueitemfixedinfo uif,
             department dep,
             distributionpoint dp,
             (select plpc.customer_id,
                     plpc.product_id,
                     plpc.sizedefinition_id,
                     plpc.finishingmethod_id,
                     plpc.dateactive,
                     plpc.dateinactive,
                     plpc.chargelossuniquepooldays,
                     plpc.residualvaluescheme_id,
                     rv.calculationbasis,
                     case when lv_lossforpoolmethod = 1
                        then vcursor1.cutoffdate
                        else trunc(sysdate) - plpc.chargelossuniquepooldays
                     end cutoffdate,
                     pack_price.replacement_price(plpc.customer_id,
                                                  plpc.product_id,
                                                  plpc.sizedefinition_id,
                                                  plpc.finishingmethod_id) price
                from productlistpercustomer plpc,
                     product                p,
                     sizedefinition         sd,
                     residualvaluescheme    rv
               where plpc.customer_id = vcursor1.customer_id  -- only for customer in this iteration
                 and rv.residualvaluescheme_id = plpc.residualvaluescheme_id
                 and p.product_id = plpc.product_id
                 and p.chargelossforpool = 'Y'
                 and sd.sizedefinition_id = plpc.sizedefinition_id
                 and sd.code = '*'
                 and plpc.chargelossforpool = 'Y' --(charge loss for unreturned pool, inventorybillingtype = 5 or BM2)
             ) ap  -- inline view for applicable_products
       where ui.poolitem = 1 --(pool item)
         and ui.distributionpoint_id = dp.distributionpoint_id
         and dp.department_id = dep.department_id
         and dep.customer_id = vcursor1.customer_id  -- only for customer in this iteration
         and uif.uniqueitem_id = ui.uniqueitem_id
         and ui.lastoutscandate is not null
         and ap.customer_id = dep.customer_id
         and ap.product_id = ui.product_id
         and ap.finishingmethod_id = ui.finishingmethod_id
         and (
             (vcursor1.losschargetrigger = 0 -- based on outscan date
              and ap.dateactive <= trunc(ui.lastoutscandate)
              and ap.dateinactive >= trunc(ui.lastoutscandate)
              and trunc(ui.lastoutscandate) <= ap.cutoffdate)  -- most important: lastoutscandate before cutoffdate
              or
             (vcursor1.losschargetrigger = 1 -- based on outscan date
              and ap.dateactive <= trunc(ui.lastscandate)
              and ap.dateinactive >= trunc(ui.lastscandate)
              and trunc(ui.lastscandate) <= ap.cutoffdate)  -- most important: lastscandate before cutoffdate
             )
         and not exists  -- and not already marked as lost via outscan date
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and trunc(uil.outscandate) = trunc(ui.lastoutscandate))
              and not exists  -- and not already marked as lost via last scan date
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and trunc(uil.lastscandate) = trunc(ui.lastscandate));
        commit;

    end loop;

    write_log('END PACK_LOSSFORPOOL.LOSSFORPOOLPROCESS SUCCESSFUL');
    --close files
    end_log;

    commit;

    return gv_returnvalue;
  exception
    when gv_userexception then
      begin
        rollback;
        write_log('...return1 ' || to_char(gv_returnvalue));
        end_log;
        if gv_returnvalue = return_ok then
          gv_returnvalue := 2;
        end if;
        return gv_returnvalue;
      end;
    when others then
      begin
        write_log(sqlerrm, priority_debug);
        write_log(gv_message, priority_error);
        write_log('...return2 ' || to_char(gv_returnvalue));
        rollback;
        end_log;
        if gv_returnvalue = return_ok then
          gv_returnvalue := return_failed_unknown;
        end if;
        return gv_returnvalue;
      end;
  end lossforpoolprocess;

end pack_lossforpool;
/
