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,
             c.losschargestartdate losschargestartdate 
        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,
             c.losschargestartdate losschargestartdate 
        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,
                  status         s
            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
              and s.status_id = h.status_id
              and s.code = '2' --finalized
          ) 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,
           lastdeliverydate
           )
        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,
           trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
        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
              and trunc(ui.lastoutscandate) >= vcursor1.losschargestartdate)
              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 trunc(ui.lastscandate) >= vcursor1.losschargestartdate)
              or 
             (vcursor1.losschargetrigger = 2 
              and ap.dateactive <= trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
              and ap.dateinactive >= trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
              and nvl(ui.lastdeliverydate, ui.lastoutscandate) <= ap.cutoffdate
              and nvl(ui.lastdeliverydate, ui.lastoutscandate) >= vcursor1.losschargestartdate )
             )
              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))
              and not exists 
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and uil.lastdeliverydate = trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate)));
        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,
           lastdeliverydate
           )
        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,
           trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
        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
              and trunc(ui.lastoutscandate) >= vcursor1.losschargestartdate)
              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 trunc(ui.lastscandate) >= vcursor1.losschargestartdate)              
              or 
             (vcursor1.losschargetrigger = 2 
              and ap.dateactive <= trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
              and ap.dateinactive >= trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate))
              and nvl(ui.lastdeliverydate, ui.lastoutscandate) <= ap.cutoffdate
              and nvl(ui.lastdeliverydate, ui.lastoutscandate) >= vcursor1.losschargestartdate )
             )
         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))
              and not exists 
         (select uil.uniqueitemloss_id
                  from uniqueitemloss uil
                 where uil.uniqueitem_link_id = ui.uniqueitem_id
                   and uil.lastdeliverydate = trunc(nvl(ui.lastdeliverydate, ui.lastoutscandate)));
        commit;

    end loop;

-- STEP 4 Service charge Loss for pool--------------------------------------------------------------
    gv_message := 'STEP 4 Loss for pool item';
    write_log(gv_message);
    Begin
     merge into repairhistory rh
     using (select buse.variableservice_id, c.customer_id, dp.distributionpoint_id, ui.customerowned,
         c.variableservices,  rt.defaulttime, ui.uniqueitem_id,ui.primaryid,ui.businessunit_id,p.product_id,
         F_RepairPrice(plpc.customer_id,rt.repairtype_id, sysdate,1) repairprice,c.currency_id,
         p.supplier_id,
         (select min(CUSTOMERACTIVITIES.INVOICEBUSINESSUNIT_ID)
             from CUSTOMERACTIVITIES, PRODUCTGROUP, PRODUCT
           where CUSTOMERACTIVITIES.CUSTOMER_ID = c.customer_id and
                 CUSTOMERACTIVITIES.ACTIVITY_ID = PRODUCTGROUP.ACTIVITY_ID and
                 PRODUCT.PRODUCTGROUP_ID = PRODUCTGROUP.PRODUCTGROUP_ID and
                 PRODUCT.PRODUCT_ID = p.product_id)invbu_id
       from  uniqueitem ui,
             uniqueitemloss uil,
             department dep,
             distributionpoint dp,
             productlistpercustomer plpc,
             product                p,
             customer  c,
             buservicechargeevent buse, businessunit bu, repairtype rt
       where ui.poolitem = 1 --(pool item)
         and ui.distributionpoint_id = dp.distributionpoint_id
         and dp.department_id = dep.department_id
         and dep.customer_id = c.customer_id
         and uil.uniqueitem_link_id = ui.uniqueitem_id
         and uil.lossstatus=0 --to be charged
         and ui.lastoutscandate is not null
         and plpc.customer_id = dep.customer_id
         and plpc.product_id = ui.product_id
         and plpc.dateactive <= trunc(ui.lastoutscandate)
         and plpc.dateinactive >= trunc(ui.lastoutscandate)
         and plpc.customer_id = c.customer_id
         and p.product_id = plpc.product_id
         and p.chargelossforpool = 'Y'
         and plpc.chargelossforpool = 'Y'
         and ((plpc.sizedefinition_id = ui.sizedefinition_id)
             or (plpc.sizedefinition_id=(select a.sizedefinition_id from sizedefinition a where a.code='*')))
         and plpc.finishingmethod_id = ui.finishingmethod_id
         and buse.businessunit_id=ui.businessunit_id
         and buse.servicechargeevent_id=7--Loss for pool item
         and buse.variableservice_id =rt.repairtype_id
         and ui.businessunit_id=bu.businessunit_id
         and bu.plant_id= gv_plantid
         and (c.variableservices =1  or (c.variableservices=2 and F_RepairPrice(plpc.customer_id,rt.repairtype_id, sysdate,1) is not null ))
         order by bu.businessunit_id, c.customer_id,dp.distributionpoint_id,p.product_id,  ui.uniqueitem_id ) MERGE_SUBQUERY
           on  (rh.uniqueitem_link_id=MERGE_SUBQUERY.uniqueitem_id
           and rh.customer_link_id=MERGE_SUBQUERY.customer_id
           and rh.distributionPoint_Link_id=MERGE_SUBQUERY.distributionpoint_id
           and rh.repairtype_id=MERGE_SUBQUERY.variableservice_id
           and rh.customerowned=MERGE_SUBQUERY.customerowned
           and trunc(rh.timestamp)=trunc(sysdate)
          and ((rh.status= 1 and MERGE_SUBQUERY.variableservices  = 1) or
            (rh.status= 0 and MERGE_SUBQUERY.variableservices  = 2))  )
    when matched then
      Update
      set rh.quantity=rh.quantity+1
      WHEN NOT MATCHED THEN
        INSERT
                (REPAIRHISTORY_ID,
                SUPPLIER_ID,
                CURRENCY_ID,
                PRODUCT_LINK_ID,
                DISTRIBUTIONPOINT_LINK_ID,
                WEAREREMPLOYMENT_LINK_ID,
                CUSTOMER_LINK_ID,
                REPAIRTYPE_ID,
                UNIQUEITEM_LINK_ID,
                BUSINESSUNIT_ID, REPAIRDATE,
                MINUTES,
                YEAR, WEEK, DAY,
                POOL,
                UNITPRICE,
                PRICEPERMINUTE,
                SYSTEMUSER_ID,
                TIMESTAMP,
                STATUS,
                INVOICEBUSINESSUNIT_ID,
                INVOICEHEADER_ID,
                PRIMARYID,
                AMOUNT,
                QUANTITY,
                CUSTOMEROWNED)
         VALUES
               (SEQ_REPAIRHISTORY.NEXTVAL,
               MERGE_SUBQUERY.SUPPLIER_ID,
               MERGE_SUBQUERY.CURRENCY_ID,
               MERGE_SUBQUERY.PRODUCT_ID,
               MERGE_SUBQUERY.DISTRIBUTIONPOINT_ID,
               NULL,
               MERGE_SUBQUERY.CUSTOMER_ID,
               MERGE_SUBQUERY.VARIABLESERVICE_ID,
               MERGE_SUBQUERY.UNIQUEITEM_ID,
               MERGE_SUBQUERY.BUSINESSUNIT_ID,
               SYSDATE,
               MERGE_SUBQUERY.DEFAULTTIME,
               YEAR(SYSDATE),GETISOWEEK(SYSDATE), GETDAYNUMBER(SYSDATE),
               'Y',
               NULL,
               NULL,
               gv_systemuserid ,
               SYSDATE,
               CASE  WHEN MERGE_SUBQUERY.VARIABLESERVICES= 1 THEN 1
                 ELSE 0 END ,--STATUS
               MERGE_SUBQUERY.INVBU_ID,
               NULL,
               MERGE_SUBQUERY.PRIMARYID,
               0,
               1,
               MERGE_SUBQUERY.CUSTOMEROWNED
              );
       commit;
       exception when no_data_found then null;
      end;

    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;
/
