create or replace package pack_sqitemsincirculation_new is

  /****************************************************************************************************\
  * New version 08-dec-2017                                                                            *
  * Version 2.1d  Pack_SQItemsInCirculation                                                            *
  * Updated:                                                                                           *
  *   08-Dec-2017 totally new version ABS-55074 Rework PACKAGE pack_sqitemsincirculation from scratch  *
  *   12-Mar-2018 updates the PLPC fields totalincirculation totalincirculationcharged as well         *
  *   20-Mar-2018 take into account fp_inactiveplpc also inactive customers                            *
  \****************************************************************************************************/

  --Main procedure createsqitemsincirculation
  procedure createsqitemsincirculation(fp_sqheaderid                out nocopy statusquoheader.statusquoheader_id%type,
                                       fp_processbuid               in businessunit.businessunit_id%type,
                                       fp_invoicebuid               in businessunit.businessunit_id%type,
                                       fp_sqdate                    in date,
                                       fp_sqyear                    in int,
                                       fp_sqweek                    in int,
                                       fp_sqmonth                   in int,
                                       fp_sqsubtype                 in int,
                                       fp_systemuserid              in systemuser.systemuser_id%type,
                                       fp_checkinvoicebu            in int,
                                       fp_userentonstock            in int,
                                       fp_inactiveplpc              in int,
                                       fp_plantid                   in plant.plant_id%type,
                                       fp_invoicereplacements       in int,
                                       fp_invoicesubstitutes        in int,
                                       fp_sqonwearerlevel           in int,
                                       fp_userentbasedonreturnweeks in int,
                                       fp_settlementactive          in int,
                                       fp_chargereplacedproduct     in int,
                                       fp_abslogprocessjob          in int);

  --Check_Duplicate_PLPC make sure there are no duplicate PLPC's
  procedure check_duplicate_plpc;

end pack_sqitemsincirculation_new;
/
create or replace package body pack_sqitemsincirculation_new is
  --external procedure
  procedure createsqitemsincirculation(fp_sqheaderid                out nocopy statusquoheader.statusquoheader_id%type,
                                       fp_processbuid               in businessunit.businessunit_id%type,
                                       fp_invoicebuid               in businessunit.businessunit_id%type,
                                       fp_sqdate                    in date,
                                       fp_sqyear                    in int,
                                       fp_sqweek                    in int,
                                       fp_sqmonth                   in int,
                                       fp_sqsubtype                 in int,
                                       fp_systemuserid              in systemuser.systemuser_id%type,
                                       fp_checkinvoicebu            in int,
                                       fp_userentonstock            in int,
                                       fp_inactiveplpc              in int,
                                       fp_plantid                   in plant.plant_id%type,
                                       fp_invoicereplacements       in int,
                                       fp_invoicesubstitutes        in int,
                                       fp_sqonwearerlevel           in int,
                                       fp_userentbasedonreturnweeks in int,
                                       fp_settlementactive          in int,
                                       fp_chargereplacedproduct     in int,
                                       fp_abslogprocessjob          in int) is
    --global variables
    /*Parameter fp_inactiveplpc
     This setting is applicable both for SQ items in circulation and SQ mats in circulation.
       0 = All items are processed, also with inactive PLPC.
           When PLPC is inactive the billing method from the last (last dateinactive) PLPC is taken.
       1 = All items are processed, also with inactive PLPC.
       2 = Process only items with active PLPC.
    In case of mats in circulation option 0 and 1 are the same.
    */
    gv_message                  abslog.message%type;
    gv_sizestarid               sizedefinition.sizedefinition_id%type;
    gv_flagsubstituteid         flag.flag_id%type;
    gv_flagreplacementid        flag.flag_id%type;
    gv_statusidforuniqueinstock status.status_id%type;
    gv_statusincirculation      status.status_id%type;
    gv_statusassigned           status.status_id%type;
    gv_underusemethodss         systemsetting.value%type;
    gv_bm3maxinvreducedepositss systemsetting.value%type;
    gv_staydepositstockid       stay.stay_id%type;
    gv_sqheaderid               statusquoheader.statusquoheader_id%type;

    --interal procedures
    procedure write_log(fp_message in varchar2, fp_priority in number) is
    begin
      /* writes logs into database.
      priority
      1 = Message
      2 = Warning
      3 = Error
      4 = Stack trace
      5 = Debug info  */
      abslogwrite.abslog_write(5, 'Pack_SQItemInCirculation', null, fp_priority, fp_message,
                               fp_systemuserid);
      begin
        dbms_output.put_line(fp_message);
      exception
        when others then
          null;
      end;
    end write_log;

    procedure initialize is
    begin
      --initialize
      -- size *
      gv_message := 'get size *';
      select sizedefinition_id into gv_sizestarid from sizedefinition where code = '*';
      --automaticflag substitute
      gv_message := 'automaticflag substitute (12)';
      select coalesce((select af.flag_id
                       from   automaticflag af
                       where  af.event = a.event
                       and    af.plant_id = fp_plantid),
                       (select af.flag_id
                         from   automaticflag af
                         where  af.event = a.event
                         and    af.plant_id is null))
      into   gv_flagsubstituteid
      from   automaticflag a
      where  a.automaticflag_id = 12;
      --automaticflag replacement
      gv_message := 'automaticflag replacement (4)';
      select coalesce((select af.flag_id
                       from   automaticflag af
                       where  af.event = a.event
                       and    af.plant_id = fp_plantid),
                       (select af.flag_id
                         from   automaticflag af
                         where  af.event = a.event
                         and    af.plant_id is null))
      into   gv_flagreplacementid
      from   automaticflag a
      where  a.automaticflag_id = 4;
      --status_id for UI Assigned
      gv_message := 'get status UI, 30';
      select st.status_id
      into   gv_statusassigned
      from   status st
      where  st.statustype = 'UI'
      and    st.code = '30';
      --status_id for UI In circulation
      gv_message := 'get status UI, 20';
      select st.status_id
      into   gv_statusincirculation
      from   status st
      where  st.statustype = 'UI'
      and    st.code = '20';
      --status_id for UI In stock (used for createSQItemInCircOnUniqueRent )
      gv_message := 'get status UI, 10';
      select st.status_id
      into   gv_statusidforuniqueinstock
      from   status st
      where  st.statustype = 'UI'
      and    st.code = '10';
      --gv_staydepositstockid
      select st.stay_id
      into   gv_staydepositstockid
      from   stay st
      where  st.staytype = 1
      and    st.staynr = 37;
      --system setting underuseMethodSS
      gv_message := 'UNDERUSEMETHOD for plant id ' || fp_plantid;
      select ss.value
      into   gv_underusemethodss
      from   systemsetting ss
      where  upper(ss.name) like upper('UNDERUSEMETHOD')
      and    ss.groupname = 'Billing'
      and    ss.plant_id = fp_plantid;
      --system setting bm3MaxInvReduceDepositSS
      gv_message := 'BM3MAXINVREDUCEDEPOSIT for plant id ' || fp_plantid;
      select ss.value
      into   gv_bm3maxinvreducedepositss
      from   systemsetting ss
      where  upper(ss.name) like upper('BM3MAXINVREDUCEDEPOSIT')
      and    ss.groupname = 'Billing'
      and    ss.plant_id = fp_plantid;
    end initialize;

    procedure create_sq_header is
    begin
      --cleanup old sq
      gv_message := 'cleanup old sq';
      delete statusquoheader sh
      where  sh.year = fp_sqyear
      and    sh.week = fp_sqweek
      and    sh.processbusinessunit_id = fp_processbuid
      and    sh.invoicebusinessunit_id = fp_invoicebuid
      and    sh.statusquotype = 1
      and    sh.totalquantity = 0;
      --create_sq_header
      gv_message := 'create_sq_header';
      insert into statusquoheader
        (statusquoheader_id, processbusinessunit_id, invoicebusinessunit_id, statusquodate,
         statusquotype, totalquantity, year, week, month, subtype, abslogprocessjob_id,
         systemuser_id, timestamp)
      values
        (seq_statusquoheader.nextval, fp_processbuid, fp_invoicebuid, fp_sqdate, 1, 0, fp_sqyear,
         fp_sqweek, fp_sqmonth, fp_sqsubtype, fp_abslogprocessjob, fp_systemuserid, sysdate)
      returning statusquoheader_id into gv_sqheaderid;
    end create_sq_header;

    procedure add_non_uniqueitem is
    begin
      --Add_NON_uniqueitem
      gv_message := 'Add_NON_uniqueitem';
      insert into sqitemincirculation
        (sqitemincirculation_id, statusquoheader_id, customer_link_id, department_link_id,
         distributionpoint_link_id, weareremployment_link_id, product_link_id,
         sizedefinition_link_id, finishingmethod_id, maxinv, totalcircinv, changesperweek,
         maxfreeinv, freeissuedcircinv, cogcircinv, freeseasoncircinv, beyondlifetimecirc,
         variationmaxinv, variationtotalcircinv, variationchangesperweek, variationmaxfreeinv,
         variationrentedperpiece, variationfreeissuedcircinv, variationcogcircinv,
         variationfreeseasoncircinv, variationbeyondlifetimecirc, systemuser_id, timestamp,
         qtyrentedperpiece, qtyrentedother, variationrentedother, qtycustomerrentstop,
         variationqtycustomerrentstop, qtyrentstop, variationqtyrentstop, emblemtemplate_id,
         qtyrentemblem, variationqtyrentemblem, qtyreplacement, variationqtyreplacement, qtyassigned,
         variationqtyassigned, qtyrenteddepositstock, variationrenteddepositstock, qtysubstitute,
         variationqtysubstitute, chargeafterdepreciation, yearweek, qtyrentedperpiecepool,
         variationrentedperpiecepool, wearerinvoicecategory_id, qtyfullyamortized,
         variationqtyfullyamortized, qtyrentedreturnrent, variationqtyrentedreturnrent,
         qtyrentedminrent, variationqtyrentedminrent)
        select seq_sqitemincirculation.nextval, gv_sqheaderid, x.customer_id, x.department_id,
               x.distributionpoint_id, x.weareremployment_id, x.product_id, x.sizedefinition_id,
               x.finishingmethod_id, x.maxinventory, x.totalcircinv, x.changesperweek,
               x.maxfreeofcharge, x.freeissuedcircinv, 0 /*cogcircinv*/, x.freeseasoncircinv, 0
               /*beyondlifetimecirc*/, 0 /*variationmaxinv*/, 0 /*variationtotalcircinv*/, 0
               /*variationchangesperweek*/, 0 /*variationmaxfreeinv*/, 0 /*variationrentedperpiece*/,
               0 /*variationfreeissuedcircinv*/, 0 /*variationcogcircinv*/, 0
               /*variationfreeseasoncircinv*/, 0 /*variationbeyondlifetimecirc*/, fp_systemuserid,
               sysdate /*timestamp*/, x.qtyrentedperpiece /*qtyrentedperpiece*/, x.qtyrentedother, 0
               /*variationrentedother*/, x.qtycustomerrentstop, 0 /*variationqtycustomerrentstop*/,
               x.qtyrentstop, 0 /*variationqtyrentstop*/, x.emblemtemplate_id, x.qtyrentemblem, 0
               /*variationqtyrentemblem*/, x.qtyreplacement, 0 /*variationqtyreplacement*/,
               x.qtyassigned, 0 /*variationqtyassigned*/, 0 /*qtyrenteddepositstock*/, 0
               /*variationrenteddepositstock*/, x.qtysubstitute, 0 /*variationqtysubstitute*/,
               x.chargeafterdepreciation, (fp_sqyear * 100 + fp_sqweek) /*yearweek*/, 0
               /*qtyrentedperpiecepool*/, 0 /*variationrentedperpiecepool*/, null
               /*wearerinvoicecategory_id*/, 0 /*qtyfullyamortized*/, 0
               /*variationqtyfullyamortized*/, 0 /*qtyrentedreturnrent*/, 0
               /*variationqtyrentedreturnrent*/, 0 /*qtyrentedminrent*/, 0 /*variationqtyrentedminrent*/
        from   (select a.customer_id, a.department_id, a.distributionpoint_id, a.weareremployment_id,
                        a.product_id, a.sizedefinition_id, a.finishingmethod_id,
                        sum(case
                              when (fp_sqdate between wi.dateactive and wi.dateinactive and
                                   wi.circinventory > 0 and a.qtyrentstop = 0) then
                               wi.maxinventory
                              else
                               0
                            end) maxinventory, sum(a.totalcircinv) totalcircinv,
                        sum(wi.changesperweek) changesperweek, sum(wi.maxfreeofcharge) maxfreeofcharge,
                        sum(a.freeissuedcircinv) freeissuedcircinv,
                        sum(a.freeseasoncircinv) freeseasoncircinv,
                        sum(a.qtycustomerrentstop) qtycustomerrentstop, sum(a.qtyrentstop) qtyrentstop,
                        max(a.emblemtemplate_id) emblemtemplate_id,
                        sum(a.qtyreplacement) qtyreplacement, sum(a.qtyassigned) qtyassigned,
                        sum(a.qtysubstitute) qtysubstitute,
                        max(a.chargeafterdepreciation) chargeafterdepreciation,
                        sum((case
                              when (a.billingmethod = 3 or
                                   (gv_underusemethodss = 4 and a.billingmethod = 2)) then
                               (a.totalcircinv - a.qtyassigned - a.freeissuedcircinv - a.qtyrentstop -
                               a.freeseasoncircinv - a.qtysubstitute - a.qtyreplacement)
                              else
                               0
                            end)) qtyrentedperpiece,
                        sum((case a.billingmethod
                               when 3 then
                                0
                               else
                                (a.totalcircinv - a.qtyassigned - a.freeissuedcircinv - a.qtyrentstop -
                                a.freeseasoncircinv)
                             end)) qtyrentedother,
                        sum((case
                              when a.emblemtemplate_id is null then
                               0
                              else
                               (a.totalcircinv - a.qtyassigned - a.freeissuedcircinv - a.qtyrentstop -
                               a.freeseasoncircinv)
                            end)) qtyrentemblem
                 from   (select t.customer_id, t.department_id, dp.distributionpoint_id,
                                 case fp_sqonwearerlevel
                                    when 1 then
                                     wi.weareremployment_id
                                    else
                                     null
                                  end weareremployment_id, wi.product_id, wi.sizedefinition_id,
                                 wi.finishingmethod_id, count(non.nonuniqueitem_id) totalcircinv,
                                 wi.wearerinventory_id,
                                 sum((case
                                       when non.freeitem = 'Y' then
                                        1
                                       else
                                        0
                                     end)) freeissuedcircinv,
                                 sum((case
                                       when (coalesce(l.seasonalrent, ls.seasonalrent, -1) in (0, 1) and
                                            non.startrentdate <= fp_sqdate and not (non.freeitem = 'Y') and
                                            (fp_sqdate not between
                                            nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                            nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) and
                                            wi.season_id is not null and not exists
                                             (select sw.season_id
                                              from   seasonweek sw
                                              where  sw.season_id = wi.season_id
                                              and    sw.weeknumber = fp_sqweek)) then
                                        1
                                       else
                                        0
                                     end)) freeseasoncircinv,
                                 sum((case
                                       when (non.startrentdate <= fp_sqdate and not (non.freeitem = 'Y') and
                                            exists
                                             (select 1
                                              from   temporarygarmentrentstop trs
                                              where  trs.customer_id = t.customer_id
                                              and    (trs.startyear < fp_sqyear and fp_sqyear < trs.endyear)
                                              or     (trs.startyear = fp_sqyear and
                                                    trs.startweek <= fp_sqweek and fp_sqyear < trs.endyear)
                                              or     (trs.startyear < fp_sqyear and fp_sqyear = endyear and
                                                    fp_sqweek <= trs.endweek)
                                              or     (trs.startyear = fp_sqyear and
                                                    trs.startweek <= fp_sqweek and fp_sqyear = trs.endyear and
                                                    fp_sqweek <= trs.endweek))) then
                                        1
                                       else
                                        0
                                     end)) qtycustomerrentstop,
                                 sum((case
                                       when (non.startrentdate <= fp_sqdate and not (non.freeitem = 'Y') and
                                            (fp_sqdate between
                                            nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                            nvl(wi.temporaryrentstopenddate, fp_sqdate - 1))) then
                                        1
                                       else
                                        0
                                     end)) qtyrentstop,
                                 max(coalesce(l.emblemtemplate_id, ls.emblemtemplate_id)) emblemtemplate_id,
                                 sum((case
                                       when fp_invoicereplacements <> 1 and
                                            non.flag_id = gv_flagreplacementid and
                                            non.flagstartdate <= fp_sqdate and
                                            non.startrentdate <= fp_sqdate and not (non.freeitem = 'Y') and
                                            (fp_sqdate not between
                                            nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                            nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) then
                                        1
                                       else
                                        0
                                     end)) qtyreplacement,
                                 sum((case
                                       when non.startrentdate > fp_sqdate then
                                        1
                                       else
                                        0
                                     end)) qtyassigned,
                                 sum((case
                                       when fp_invoicesubstitutes <> 1 and non.flag_id = gv_flagsubstituteid and
                                            non.flagstartdate <= fp_sqdate and
                                            non.startrentdate <= fp_sqdate and not (non.freeitem = 'Y') and
                                            (fp_sqdate not between
                                            nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                            nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) then
                                        1
                                       else
                                        0
                                     end)) qtysubstitute,
                                 max((case
                                       when coalesce(l.percentageafterdepreciation,
                                                     ls.percentageafterdepreciation) > 0 and
                                            coalesce(l.billingmethod, ls.billingmethod, -1) = 3 then
                                        'Y'
                                       else
                                        'N'
                                     end)) chargeafterdepreciation,
                                 coalesce(l.billingmethod, ls.billingmethod, -1) billingmethod
                          from   wearerinventory wi
                          left   outer join nonuniqueitem non
                          on     non.wearerinventory_id = wi.wearerinventory_id
                          join   weareremployment we
                          on     we.weareremployment_id = wi.weareremployment_id
                          join   wearer w
                          on     w.wearer_id = we.wearer_id
                          join   product p
                          on     p.product_id = wi.product_id
                          join   productgroup pg
                          on     pg.productgroup_id = p.productgroup_id
                          join   distributionpoint dp
                          on     dp.distributionpoint_id = we.distributionpoint_id
                          join   department t
                          on     t.department_id = dp.department_id
                          left   outer join productlistpercustomer l
                          on     (l.customer_id = t.customer_id and l.product_id = wi.product_id and
                                 l.sizedefinition_id = wi.sizedefinition_id and
                                 l.finishingmethod_id = wi.finishingmethod_id and
                                 fp_sqdate between l.dateactive and l.dateinactive)
                          left   outer join productlistpercustomer ls
                          on     (ls.customer_id = t.customer_id and ls.product_id = wi.product_id and
                                 ls.sizedefinition_id = gv_sizestarid and
                                 ls.finishingmethod_id = wi.finishingmethod_id and
                                 fp_sqdate between ls.dateactive and ls.dateinactive)
                          where  (coalesce(l.billingmethod, ls.billingmethod, -1) <> -1 or
                                 fp_inactiveplpc <> 2)
                          and    exists (select 1
                                  from   customeractivities ca
                                  where  ca.customer_id = t.customer_id
                                  and    ca.processbusinessunit_id = fp_processbuid
                                  and    ca.activity_id = pg.activity_id
                                  and    (fp_checkinvoicebu = 0 or
                                        ca.invoicebusinessunit_id = fp_invoicebuid))
                          group  by wi.wearerinventory_id, t.customer_id, t.department_id,
                                    dp.distributionpoint_id,
                                    case fp_sqonwearerlevel
                                      when 1 then
                                       wi.weareremployment_id
                                      else
                                       null
                                    end, wi.product_id, wi.sizedefinition_id, wi.finishingmethod_id,
                                    coalesce(l.billingmethod, ls.billingmethod, -1)) a
                 join   wearerinventory wi
                 on     wi.wearerinventory_id = a.wearerinventory_id
                 group  by a.customer_id, a.department_id, a.distributionpoint_id,
                           a.weareremployment_id, a.product_id, a.sizedefinition_id,
                           a.finishingmethod_id) x;
    end add_non_uniqueitem;

    procedure add_uniqueitem is
    begin
      --Add_uniqueitem
      gv_message := 'Add_uniqueitem';
      merge into sqitemincirculation sqi
      using (select a.customer_id, a.department_id, a.distributionpoint_id, a.weareremployment_id,
                    a.product_id, a.sizedefinition_id, a.finishingmethod_id,
                    sum(a.maxinventory) maxinventory, sum(a.totalcircinv) totalcircinv,
                    sum(a.changesperweek) changesperweek, sum(a.maxfreeinv) maxfreeinv,
                    max(a.emblemtemplate_id) emblemtemplate_id, sum(a.qtyassigned) qtyassigned,
                    sum(a.cogcircinv) cogcircinv, sum(a.freeissuedcircinv) freeissuedcircinv,
                    sum(a.qtyrentstop) qtyrentstop, sum(a.qtycustomerrentstop) qtycustomerrentstop,
                    sum(a.freeseasoncircinv) freeseasoncircinv,
                    sum(a.beyondlifetimecirc) beyondlifetimecirc,
                    sum(case
                          when (a.billingmethod = 3 or (gv_underusemethodss = 4 and a.billingmethod = 2)) then
                           (a.qtyrentedother)
                          else
                           case
                             when (a.totalcircinv - a.qtyassigned - a.cogcircinv - a.qtyrentedother -
                                  a.freeissuedcircinv - a.qtyrentstop - a.freeseasoncircinv -
                                  a.qtyfullyamortized) > 0 then
                              (a.totalcircinv - a.qtyassigned - a.cogcircinv - a.qtyrentedother -
                              a.freeissuedcircinv - a.qtyrentstop - a.freeseasoncircinv - a.qtyfullyamortized)
                             else
                              0
                           end
                        end) qtyrentedother, sum(a.qtyreplacement) qtyreplacement,
                    sum(a.qtysubstitute) qtysubstitute,
                    max(a.chargeafterdepreciation) chargeafterdepreciation,
                    sum(a.qtyrentedperpiecepool) qtyrentedperpiecepool,
                    sum(a.qtyfullyamortized) qtyfullyamortized,
                    sum(a.qtyrenteddepositstock) qtyrenteddepositstock,
                    sum((case
                          when (a.billingmethod = 3 or (gv_underusemethodss = 4 and a.billingmethod = 2) and
                               (a.totalcircinv - a.qtyassigned - a.freeissuedcircinv -
                               a.qtyrentstop - a.freeseasoncircinv - a.qtysubstitute -
                               a.qtyreplacement - a.beyondlifetimecirc) > 0) then
                           (a.totalcircinv - a.qtyassigned - a.freeissuedcircinv - a.qtyrentstop -
                           a.freeseasoncircinv - a.qtysubstitute - a.qtyreplacement - a.beyondlifetimecirc -
                           a.qtyrenteddepositstock)
                          else
                           0
                        end)) qtyrentedperpiece
             from   (select t.customer_id, t.department_id, dp.distributionpoint_id,
                             case fp_sqonwearerlevel
                                when 1 then
                                 wi.weareremployment_id
                                else
                                 null
                              end weareremployment_id, wi.product_id, wi.sizedefinition_id,
                             wi.finishingmethod_id, 0 maxinventory, count(u.uniqueitem_id) totalcircinv,
                             0 changesperweek, max(wi.maxfreeofcharge) maxfreeinv,
                             coalesce(l.billingmethod, ls.billingmethod, -1) billingmethod,
                             max(coalesce(l.emblemtemplate_id, ls.emblemtemplate_id)) emblemtemplate_id,
                             sum((case
                                   when u.status_id = gv_statusassigned then
                                    1
                                   else
                                    0
                                 end)) qtyassigned,
                             sum((case
                                   when u.status_id = gv_statusincirculation and u.customerowned = 'Y' then
                                    1
                                   else
                                    0
                                 end)) cogcircinv,
                             sum((case
                                   when u.status_id = gv_statusincirculation and nvl(u.leaseweeks, 0) = 0 and
                                        nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate and
                                        un.freeextragarment = 'Y' and u.customerowned <> 'Y' then
                                    1
                                   else
                                    0
                                 end)) freeissuedcircinv,
                             sum((case
                                   when u.status_id = gv_statusincirculation and nvl(u.leaseweeks, 0) = 0 and
                                        not (nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate and
                                         un.freeextragarment = 'Y') and u.customerowned <> 'Y' and
                                        fp_sqdate between wi.temporaryrentstopstartdate and
                                        wi.temporaryrentstopenddate then
                                    1
                                   else
                                    0
                                 end)) qtyrentstop,
                             sum((case
                                   when u.status_id = gv_statusincirculation and exists
                                    (select 1
                                         from   temporarygarmentrentstop trs
                                         where  trs.customer_id = t.customer_id
                                         and    (trs.startyear < fp_sqyear and fp_sqyear < trs.endyear)
                                         or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                               fp_sqyear < trs.endyear)
                                         or     (trs.startyear < fp_sqyear and fp_sqyear = endyear and
                                               fp_sqweek <= trs.endweek)
                                         or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                               fp_sqyear = trs.endyear and fp_sqweek <= trs.endweek)) then
                                    1
                                   else
                                    0
                                 end)) qtycustomerrentstop,
                             sum((case
                                   when u.status_id = gv_statusincirculation and
                                        (coalesce(l.seasonalrent, ls.seasonalrent, -1) in (0, 1)  and
                                        nvl(u.leaseweeks, 0) = 0 and not (u.customerowned = 'Y') and
                                        not (un.freeextragarment = 'Y' and
                                         nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate) and
                                        (fp_sqdate not between
                                        nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                        nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) and
                                        wi.season_id is not null and not exists
                                         (select sw.season_id
                                          from   seasonweek sw
                                          where  sw.season_id = wi.season_id
                                          and    sw.weeknumber = fp_sqweek)) then
                                    1
                                   else
                                    0
                                 end)) freeseasoncircinv,
                             sum((case
                                   when nvl(u.leaseweeks, 0) = 0 and u.status_id = gv_statusincirculation and
                                        uf.enddatefullrent < fp_sqdate and
                                        not (un.freeextragarment = 'Y' and
                                             (nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate)) and
                                        (fp_sqdate < nvl(wi.temporaryrentstopstartdate, fp_sqdate + 1) or
                                         fp_sqdate > nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) and
                                        (nvl(wi.season_id, 0) = 0 or
                                         (coalesce(l.seasontype_id, ls.seasontype_id, -1) <> -1 and exists
                                          (select sw.season_id
                                           from   seasonweek sw
                                           where  sw.season_id = wi.season_id
                                           and    sw.weeknumber = fp_sqweek))) and not (u.customerowned = 'Y') then
                                    1
                                   else
                                    0
                                 end)) beyondlifetimecirc,
                             sum((case
                                   when (u.status_id = gv_statusincirculation and u.leaseweeks is not null) then
                                    1
                                   else
                                    0
                                 end)) qtyrentedother,
                             sum((case
                                   when fp_invoicereplacements <> 1 and u.status_id = gv_statusincirculation and
                                        nvl(u.leaseweeks, 0) = 0 and not uf.enddatefullrent < fp_sqdate and
                                        not (nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate and
                                         un.freeextragarment = 'Y') and u.customerowned <> 'Y' and
                                        (fp_sqdate not between
                                        nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                        nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) and
                                        (nvl(wi.season_id, 0) = 0 or
                                        (coalesce(l.seasontype_id, ls.seasontype_id, -1) <> -1 and exists
                                         (select sw.season_id
                                           from   seasonweek sw
                                           where  sw.season_id = wi.season_id
                                           and    sw.weeknumber = fp_sqweek))) and exists
                                    (select 1
                                         from   uniqueitemflag ff
                                         where  ff.uniqueitem_id = u.uniqueitem_id
                                         and    ff.flag_id = gv_flagreplacementid
                                         and    ff.startdate <= fp_sqdate) then
                                    1
                                   else
                                    0
                                 end)) qtyreplacement,
                             sum((case
                                   when fp_invoicesubstitutes <> 1 and u.status_id = gv_statusincirculation and
                                        nvl(u.leaseweeks, 0) = 0 and not uf.enddatefullrent < fp_sqdate and
                                        not (nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate and
                                         un.freeextragarment = 'Y') and u.customerowned <> 'Y' and
                                        (fp_sqdate not between
                                        nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                        nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) and
                                        (nvl(wi.season_id, 0) = 0 or
                                        (coalesce(l.seasontype_id, ls.seasontype_id, -1) <> -1 and exists
                                         (select sw.season_id
                                           from   seasonweek sw
                                           where  sw.season_id = wi.season_id
                                           and    sw.weeknumber = fp_sqweek))) and not exists
                                    (select 1
                                         from   uniqueitemflag ff
                                         where  ff.uniqueitem_id = u.uniqueitem_id
                                         and    ff.flag_id = gv_flagreplacementid
                                         and    ff.startdate <= fp_sqdate) and exists
                                    (select 1
                                         from   uniqueitemflag ff
                                         where  ff.uniqueitem_id = u.uniqueitem_id
                                         and    ff.flag_id = gv_flagsubstituteid
                                         and    ff.startdate <= fp_sqdate) then
                                    1
                                   else
                                    0
                                 end)) qtysubstitute,
                             max((case
                                   when coalesce(l.percentageafterdepreciation, ls.percentageafterdepreciation,
                                                 0) > 0 and coalesce(l.billingmethod, ls.billingmethod, -1) = 3 then
                                    'Y'
                                   else
                                    'N'
                                 end)) chargeafterdepreciation,
                             sum((case
                                   when u.status_id = gv_statusincirculation and
                                        trunc(u.lastoutscandate) <
                                        trunc(fp_sqdate) -
                                        7 * coalesce(l.chargerentnoreturnpoolafterwks,
                                                     ls.chargerentnoreturnpoolafterwks) and
                                        coalesce(l.billingmethod, ls.billingmethod, -1) = 3 and
                                        coalesce(l.inventorybillingtype, ls.inventorybillingtype, -1) = 4 and
                                        coalesce(l.chargerentnotreturned, ls.chargerentnotreturned, -1) <> 0 and
                                        coalesce(l.chargerentnoreturnpoolafterwks,
                                                 ls.chargerentnoreturnpoolafterwks, -1) > 0 and
                                        coalesce(l.codeuniquely, ls.codeuniquely, 'N') = 'Y' and
                                        u.stay_id in (select st.stay_id
                                                      from   stay st
                                                      where  st.staytype = 1
                                                      and    st.staynr in (20, 27, 28)) then
                                    1
                                   else
                                    0
                                 end)) qtyrentedperpiecepool,
                             sum((case
                                   when c.chargeamortizeduniqueitems = 0 and u.residualvalueamount = 0 and
                                        u.status_id = gv_statusincirculation and nvl(u.leaseweeks, 0) = 0 and
                                        uf.enddatefullrent >= fp_sqdate and
                                        not (nvl(un.enddatefreeextra, fp_sqdate + 1) > fp_sqdate and
                                         un.freeextragarment = 'Y') and u.customerowned <> 'Y' and
                                        (fp_sqdate not between
                                        nvl(wi.temporaryrentstopstartdate, fp_sqdate - 1) and
                                        nvl(wi.temporaryrentstopenddate, fp_sqdate - 1)) then
                                    1
                                   else
                                    0
                                 end)) qtyfullyamortized,
                             sum(case
                                   when 
                                     u.stay_id = gv_staydepositstockid and
                                     u.status_id = gv_statusincirculation and
                                     u.BUSINESSUNIT_ID = fp_processbuid and
                                     nvl(u.leaseweeks, 0) = 0 and
                                     NOT (un.freeextragarment = 'Y' AND
                                       (un.enddatefreeextra > fp_sqdate OR
                                        nvl(un.enddatefreeextra, to_date('01/01/0001','dd/mm/yyyy')) =
                                           to_date('01/01/0001','dd/mm/yyyy'))) and
                                     NOT (u.customerowned = 'Y') and 
                                     NOT (uf.enddatefullrent < fp_sqdate) and
                                     ((fp_sqdate < wi.temporaryrentstopstartdate OR
                                        fp_sqdate > temporaryrentstopenddate) OR
                                        (nvl(wi.temporaryrentstopstartdate,
                                        to_date('01/01/0001', 'dd/mm/yyyy')) = to_date('01/01/0001','dd/mm/yyyy') AND
                                        nvl(wi.temporaryrentstopenddate, to_date('01/01/0001','dd/mm/yyyy')) =
                                         to_date('01/01/0001', 'dd/mm/yyyy'))) and
                                    NOT EXISTS (SELECT r.uniqueitem_id
                                       FROM uniqueitemflag r
                                         WHERE r.uniqueitem_id = u.uniqueitem_id
                                            AND r.flag_id = gv_flagreplacementid
                                            AND r.startdate <= fp_sqdate) and
                                    NOT EXISTS (SELECT r.uniqueitem_id
                                       FROM uniqueitemflag r
                                         WHERE r.uniqueitem_id = u.uniqueitem_id
                                           AND r.flag_id = gv_flagSubstituteID
                                           AND r.startdate <= fp_sqdate)
                                    then
                                    1
                                   else 0
                                 end) qtyrenteddepositstock, 
                             un.wearerinventory_id,
                             coalesce(l.productlistpercustomer_id, ls.productlistpercustomer_id, -1) plpc_id
                      from   uniqueitem u
                      join   uniqueitemnonpool un
                      on     un.uniqueitem_id = u.uniqueitem_id
                      left   outer join uniqueitemfixedinfo uf
                      on     uf.uniqueitem_id = u.uniqueitem_id
                      join   weareremployment we
                      on     we.weareremployment_id = un.weareremployment_id
                      join   wearerinventory wi
                      on     wi.wearerinventory_id = un.wearerinventory_id
                      join   distributionpoint dp
                      on     dp.distributionpoint_id = we.distributionpoint_id
                      join   department t
                      on     t.department_id = dp.department_id
                      join   customer c
                      on     c.customer_id = t.customer_id
                      join   product p
                      on     p.product_id = wi.product_id
                      join   productgroup pg
                      on     pg.productgroup_id = p.productgroup_id
                      left   outer join productlistpercustomer l
                      on     (l.customer_id = t.customer_id and l.product_id = wi.product_id and
                             l.sizedefinition_id = wi.sizedefinition_id and
                             l.finishingmethod_id = wi.finishingmethod_id and
                             fp_sqdate between l.dateactive and l.dateinactive)
                      left   outer join productlistpercustomer ls
                      on     (ls.customer_id = t.customer_id and ls.product_id = wi.product_id and
                             ls.sizedefinition_id = gv_sizestarid and
                             ls.finishingmethod_id = wi.finishingmethod_id and
                             fp_sqdate between ls.dateactive and ls.dateinactive)
                      where  (coalesce(l.billingmethod, ls.billingmethod, -1) <> -1 or
                             fp_inactiveplpc <> 2)
                      and    exists
                       (select 1
                              from   customeractivities ca
                              where  ca.customer_id = t.customer_id
                              and    ca.processbusinessunit_id = fp_processbuid
                              and    ca.activity_id = pg.activity_id
                              and    (fp_checkinvoicebu = 0 or ca.invoicebusinessunit_id = fp_invoicebuid))
                      group  by t.customer_id, t.department_id, dp.distributionpoint_id,
                                case fp_sqonwearerlevel
                                  when 1 then
                                   wi.weareremployment_id
                                  else
                                   null
                                end, un.wearerinventory_id, wi.product_id, wi.sizedefinition_id,
                                wi.finishingmethod_id, coalesce(l.billingmethod, ls.billingmethod, -1),
                                coalesce(l.productlistpercustomer_id, ls.productlistpercustomer_id, -1)) a
             group  by a.customer_id, a.department_id, a.distributionpoint_id, a.weareremployment_id,
                       a.product_id, a.sizedefinition_id, a.finishingmethod_id) x
      on (sqi.statusquoheader_id = gv_sqheaderid and sqi.distributionpoint_link_id = x.distributionpoint_id and (sqi.weareremployment_link_id = x.weareremployment_id or (sqi.weareremployment_link_id is null and x.weareremployment_id is null)) and sqi.product_link_id = x.product_id and sqi.sizedefinition_link_id = x.sizedefinition_id and sqi.finishingmethod_id = x.finishingmethod_id)
      when matched then
        update
        set    sqi.maxinv = sqi.maxinv + x.maxinventory,
               sqi.totalcircinv = sqi.totalcircinv + x.totalcircinv,
               sqi.changesperweek = sqi.changesperweek + x.changesperweek,
               sqi.maxfreeinv = sqi.maxfreeinv + x.maxfreeinv,
               sqi.freeissuedcircinv = sqi.freeissuedcircinv + x.freeissuedcircinv,
               sqi.cogcircinv = sqi.cogcircinv + x.cogcircinv,
               sqi.freeseasoncircinv = sqi.freeseasoncircinv + x.freeseasoncircinv,
               sqi.beyondlifetimecirc = sqi.beyondlifetimecirc + x.beyondlifetimecirc,
               sqi.qtyrentedperpiecepool = sqi.qtyrentedperpiecepool + x.qtyrentedperpiecepool,
               sqi.qtyassigned = sqi.qtyassigned + x.qtyassigned,
               sqi.qtyrentedperpiece = sqi.qtyrentedperpiece + x.qtyrentedperpiece,
               sqi.qtyrentedother = sqi.qtyrentedother + x.qtyrentedother,
               sqi.qtysubstitute = sqi.qtysubstitute + x.qtysubstitute,
               sqi.qtyrentemblem = sqi.qtyrentemblem + case
                                      when x.emblemtemplate_id is null then
                                       0
                                      else
                                       (x.totalcircinv - x.qtyassigned - x.freeissuedcircinv - x.qtyrentstop - x.freeseasoncircinv)
                                    end,
               sqi.qtyreplacement = sqi.qtyreplacement + x.qtyreplacement,
               sqi.qtycustomerrentstop = sqi.qtycustomerrentstop + x.qtycustomerrentstop,
               sqi.qtyrentstop = sqi.qtyrentstop + x.qtyrentstop,
               sqi.qtyfullyamortized = sqi.qtyfullyamortized + x.qtyfullyamortized,
               sqi.qtyrenteddepositstock = sqi.qtyrenteddepositstock + x.qtyrenteddepositstock,
               sqi.emblemtemplate_id = nvl(sqi.emblemtemplate_id, x.emblemtemplate_id),
               sqi.systemuser_id = fp_systemuserid, sqi.timestamp = sysdate
      when not matched then
        insert
          (sqitemincirculation_id, statusquoheader_id, customer_link_id, department_link_id,
           distributionpoint_link_id, weareremployment_link_id, product_link_id, sizedefinition_link_id,
           finishingmethod_id, maxinv, totalcircinv, changesperweek, maxfreeinv, freeissuedcircinv,
           cogcircinv, freeseasoncircinv, beyondlifetimecirc, variationmaxinv, variationtotalcircinv,
           variationchangesperweek, variationmaxfreeinv, variationrentedperpiece,
           variationfreeissuedcircinv, variationcogcircinv, variationfreeseasoncircinv,
           variationbeyondlifetimecirc, systemuser_id, timestamp, qtyrentedperpiece, qtyrentedother,
           variationrentedother, qtycustomerrentstop, variationqtycustomerrentstop, qtyrentstop,
           variationqtyrentstop, emblemtemplate_id, qtyrentemblem, variationqtyrentemblem,
           qtyreplacement, variationqtyreplacement, qtyassigned, variationqtyassigned,
           qtyrenteddepositstock, variationrenteddepositstock, qtysubstitute, variationqtysubstitute,
           chargeafterdepreciation, yearweek, qtyrentedperpiecepool, variationrentedperpiecepool,
           wearerinvoicecategory_id, qtyfullyamortized, variationqtyfullyamortized, qtyrentedreturnrent,
           variationqtyrentedreturnrent, qtyrentedminrent, variationqtyrentedminrent)
        values
          (seq_sqitemincirculation.nextval, gv_sqheaderid, x.customer_id, x.department_id,
           x.distributionpoint_id, x.weareremployment_id, x.product_id, x.sizedefinition_id,
           x.finishingmethod_id, x.maxinventory, x.totalcircinv, x.changesperweek, x.maxfreeinv,
           x.freeissuedcircinv, x.cogcircinv, x.freeseasoncircinv, x.beyondlifetimecirc, 0
           /*variationmaxinv*/, 0 /*variationtotalcircinv*/, 0 /*variationchangesperweek*/, 0
           /*variationmaxfreeinv*/, 0 /*variationrentedperpiece*/, 0 /*variationfreeissuedcircinv*/, 0
           /*variationcogcircinv*/, 0 /*variationfreeseasoncircinv*/, 0 /*variationbeyondlifetimecirc*/,
           fp_systemuserid, sysdate /*timestamp*/, x.qtyrentedperpiece, x.qtyrentedother, 0
           /*variationrentedother*/, x.qtycustomerrentstop, 0 /*variationqtycustomerrentstop*/,
           x.qtyrentstop, 0 /*variationqtyrentstop*/, x.emblemtemplate_id,
           case
              when x.emblemtemplate_id is null then
               0
              else
               (x.totalcircinv - x.qtyassigned - x.freeissuedcircinv - x.qtyrentstop - x.freeseasoncircinv)
            end /*qtyrentemblem*/, 0 /*variationqtyrentemblem*/, x.qtyreplacement, 0
           /*variationqtyreplacement*/, x.qtyassigned, 0 /*variationqtyassigned*/, 0
           /*qtyrenteddepositstock*/, 0 /*variationrenteddepositstock*/, x.qtysubstitute, 0
           /*variationqtysubstitute*/, nvl(x.chargeafterdepreciation, 'N'),
           (fp_sqyear * 100 + fp_sqweek)
           /*yearweek*/, x.qtyrentedperpiecepool, 0 /*variationrentedperpiecepool*/, null
           /*wearerinvoicecategory_id*/, x.qtyfullyamortized, 0 /*variationqtyfullyamortized*/, 0
           /*qtyrentedreturnrent*/, 0 /*variationqtyrentedreturnrent*/, 0 /*qtyrentedminrent*/, 0
           /*variationqtyrentedminrent*/);
    end add_uniqueitem;

    procedure add_uniquepoolitem is
    begin
      --add_uniquepoolitem
      gv_message := 'add_uniquepoolitem';
      merge into sqitemincirculation sqi
      using (select a.customer_id, a.department_id, a.distributionpoint_id, a.weareremployment_id,
                    a.product_id, a.sizedefinition_id, a.finishingmethod_id,
                    sum(a.maxinventory) maxinventory, sum(a.totalcircinv) totalcircinv,
                    sum(a.changesperweek) changesperweek, sum(a.maxfreeinv) maxfreeinv,
                    max(a.emblemtemplate_id) emblemtemplate_id, sum(a.qtyassigned) qtyassigned,
                    sum(a.cogcircinv) cogcircinv, sum(a.freeissuedcircinv) freeissuedcircinv,
                    sum(a.qtyrentstop) qtyrentstop, sum(a.qtycustomerrentstop) qtycustomerrentstop,
                    sum(a.freeseasoncircinv) freeseasoncircinv,
                    sum(a.beyondlifetimecirc) beyondlifetimecirc, sum(a.qtyrentedother) qtyrentedother,
                    sum(a.qtyreplacement) qtyreplacement, sum(a.qtysubstitute) qtysubstitute,
                    max(a.chargeafterdepreciation) chargeafterdepreciation,
                    sum(a.qtyrentedperpiecepool) qtyrentedperpiecepool,
                    sum(a.qtyfullyamortized) qtyfullyamortized
             from   (select t.customer_id, t.department_id, dp.distributionpoint_id,
                             null weareremployment_id, u.product_id, u.sizedefinition_id,
                             u.finishingmethod_id, 0 maxinventory, 0 totalcircinv, 0 changesperweek,
                             0 maxfreeinv, coalesce(l.billingmethod, ls.billingmethod, -1) billingmethod,
                             max(coalesce(l.emblemtemplate_id, ls.emblemtemplate_id)) emblemtemplate_id,
                             0 qtyassigned, 0 cogcircinv, 0 freeissuedcircinv,
                             sum(coalesce((select 1
                                           from   standarddeliveryline y
                                           join   productlistperdistribpoint plpdp
                                           on     plpdp.productlistperdistribpoint_id =
                                                  y.productlistperdistribpoint_id
                                           where  plpdp.distributionpoint_id = u.distributionpoint_id
                                           and    plpdp.product_id = u.product_id
                                           and    plpdp.sizedefinition_id = u.sizedefinition_id
                                           and    plpdp.finishingmethod_id = u.finishingmethod_id
                                           and    exists
                                            (select tds.standarddeliveryline_id
                                                   from   temporarydeliverystop tds
                                                   where  tds.standarddeliveryline_id =
                                                          y.standarddeliveryline_id
                                                   and    ((tds.tempstoprentforweek = 'Y' and
                                                         (fp_sqweek between tds.temporarystopstartweek and
                                                         tds.temporarystopendweek) or
                                                         (tds.temporarystopstartweek >
                                                         tds.temporarystopendweek and
                                                         ((fp_sqweek <= tds.temporarystopendweek and
                                                         0 < fp_sqweek) or
                                                         (temporarystopstartweek <= fp_sqweek and
                                                         fp_sqweek <= 53)))) or
                                                         (tds.tempstoprentforyearweek = 'Y' and
                                                         f_isinyearweeklifespan(tds.temporarystopstartyear,
                                                                                   tds.tempstopstartweekforyear,
                                                                                   fp_sqyear, fp_sqweek,
                                                                                   tds.temporarystopendyear,
                                                                                   tds.tempstopendweekforyear) = 1) or
                                                         (tds.tempstoprentfordate = 'Y' and
                                                         (tds.startdate <= sysdate and
                                                         sysdate <= tds.enddate))))), 0)) qtyrentstop,
                             sum((case
                                   when u.status_id = gv_statusincirculation and exists
                                    (select 1
                                         from   temporarygarmentrentstop trs
                                         where  trs.customer_id = t.customer_id
                                         and    (trs.startyear < fp_sqyear and fp_sqyear < trs.endyear)
                                         or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                               fp_sqyear < trs.endyear)
                                         or     (trs.startyear < fp_sqyear and fp_sqyear = endyear and
                                               fp_sqweek <= trs.endweek)
                                         or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                               fp_sqyear = trs.endyear and fp_sqweek <= trs.endweek)) then
                                    1
                                   else
                                    0
                                 end)) qtycustomerrentstop, 0 freeseasoncircinv, 0 beyondlifetimecirc,
                             0 qtyrentedother, 0 qtyreplacement, 0 qtysubstitute,
                             'N' chargeafterdepreciation, count(u.uniqueitem_id) qtyrentedperpiecepool,
                             0 qtyfullyamortized
                      from   uniqueitem u
                      join   uniqueitemfixedinfo uf
                      on     uf.uniqueitem_id = u.uniqueitem_id
                      join   product p
                      on     p.product_id = u.product_id
                      join   productgroup pg
                      on     pg.productgroup_id = p.productgroup_id
                      join   distributionpoint dp
                      on     dp.distributionpoint_id = u.distributionpoint_id
                      join   department t
                      on     t.department_id = dp.department_id
                      join   customer c
                      on     c.customer_id = t.customer_id
                      left   outer join productlistpercustomer l
                      on     (l.customer_id = t.customer_id and l.product_id = u.product_id and
                             l.sizedefinition_id = u.sizedefinition_id and
                             l.finishingmethod_id = u.finishingmethod_id and
                             fp_sqdate between l.dateactive and l.dateinactive)
                      left   outer join productlistpercustomer ls
                      on     (ls.customer_id = t.customer_id and ls.product_id = u.product_id and
                             ls.sizedefinition_id = gv_sizestarid and
                             ls.finishingmethod_id = u.finishingmethod_id and
                             fp_sqdate between ls.dateactive and ls.dateinactive)
                      where  u.status_id in (gv_statusincirculation, gv_statusassigned)
                      and    u.poolitem <> 0 --not on stock
                      and    u.finishingmethod_id is not null --must exist for sqitems
                      and    coalesce(l.chargerentnoreturnpoolafterwks, ls.chargerentnoreturnpoolafterwks,
                                      0) > 0
                      and    trunc(u.lastoutscandate) <
                             trunc(sysdate) -
                             7 * coalesce(l.chargerentnoreturnpoolafterwks,
                                           ls.chargerentnoreturnpoolafterwks, 0)
                      and    (coalesce(l.billingmethod, ls.billingmethod, -1) <> -1 or
                            fp_inactiveplpc <> 2)
                      and    exists
                       (select 1
                              from   customeractivities ca
                              where  ca.customer_id = t.customer_id
                              and    ca.processbusinessunit_id = fp_processbuid
                              and    ca.activity_id = pg.activity_id
                              and    (fp_checkinvoicebu = 0 or ca.invoicebusinessunit_id = fp_invoicebuid))
                      and    not exists (select 1
                              from   uniqueitemloss uil
                              where  uil.uniqueitem_link_id = u.uniqueitem_id
                              and    (uil.lossstatus = 0 or uil.lossstatus = 1)
                              and    u.lastoutscandate = uil.outscandate)
                      group  by t.customer_id, t.department_id, dp.distributionpoint_id, u.product_id,
                                u.sizedefinition_id, u.finishingmethod_id,
                                coalesce(l.billingmethod, ls.billingmethod, -1)) a
             group  by a.customer_id, a.department_id, a.distributionpoint_id, a.weareremployment_id,
                       a.product_id, a.sizedefinition_id, a.finishingmethod_id) x
      on (sqi.statusquoheader_id = gv_sqheaderid and sqi.distributionpoint_link_id = x.distributionpoint_id and (sqi.weareremployment_link_id = x.weareremployment_id or (sqi.weareremployment_link_id is null and x.weareremployment_id is null)) and sqi.product_link_id = x.product_id and sqi.sizedefinition_link_id = x.sizedefinition_id and sqi.finishingmethod_id = x.finishingmethod_id)
      when matched then
        update
        set    sqi.qtycustomerrentstop = sqi.qtycustomerrentstop + x.qtycustomerrentstop,
               sqi.qtyrentedperpiecepool = sqi.qtyrentedperpiecepool + x.qtyrentedperpiecepool -
                                            x.qtyrentstop,
               sqi.qtyrentstop = sqi.qtyrentstop + x.qtyrentstop,
               sqi.emblemtemplate_id = nvl(sqi.emblemtemplate_id, x.emblemtemplate_id),
               sqi.qtyrentemblem = sqi.qtyrentemblem + case
                                      when x.emblemtemplate_id is null then
                                       0
                                      else
                                       (x.totalcircinv - x.qtyassigned - x.freeissuedcircinv - x.qtyrentstop - x.freeseasoncircinv)
                                    end,
               sqi.systemuser_id = fp_systemuserid, sqi.timestamp = sysdate
      when not matched then
        insert
          (sqitemincirculation_id, statusquoheader_id, customer_link_id, department_link_id,
           distributionpoint_link_id, weareremployment_link_id, product_link_id, sizedefinition_link_id,
           finishingmethod_id, maxinv, totalcircinv, changesperweek, maxfreeinv, freeissuedcircinv,
           cogcircinv, freeseasoncircinv, beyondlifetimecirc, variationmaxinv, variationtotalcircinv,
           variationchangesperweek, variationmaxfreeinv, variationrentedperpiece,
           variationfreeissuedcircinv, variationcogcircinv, variationfreeseasoncircinv,
           variationbeyondlifetimecirc, systemuser_id, timestamp, qtyrentedperpiece, qtyrentedother,
           variationrentedother, qtycustomerrentstop, variationqtycustomerrentstop, qtyrentstop,
           variationqtyrentstop, emblemtemplate_id, qtyrentemblem, variationqtyrentemblem,
           qtyreplacement, variationqtyreplacement, qtyassigned, variationqtyassigned,
           qtyrenteddepositstock, variationrenteddepositstock, qtysubstitute, variationqtysubstitute,
           chargeafterdepreciation, yearweek, qtyrentedperpiecepool, variationrentedperpiecepool,
           wearerinvoicecategory_id, qtyfullyamortized, variationqtyfullyamortized, qtyrentedreturnrent,
           variationqtyrentedreturnrent, qtyrentedminrent, variationqtyrentedminrent)
        values
          (seq_sqitemincirculation.nextval, gv_sqheaderid, x.customer_id, x.department_id,
           x.distributionpoint_id, x.weareremployment_id, x.product_id, x.sizedefinition_id,
           x.finishingmethod_id, x.maxinventory, x.totalcircinv, x.changesperweek, x.maxfreeinv,
           x.freeissuedcircinv, x.cogcircinv, x.freeseasoncircinv, x.beyondlifetimecirc, 0
           /*variationmaxinv*/, 0 /*variationtotalcircinv*/, 0 /*variationchangesperweek*/, 0
           /*variationmaxfreeinv*/, 0 /*variationrentedperpiece*/, 0 /*variationfreeissuedcircinv*/, 0
           /*variationcogcircinv*/, 0 /*variationfreeseasoncircinv*/, 0 /*variationbeyondlifetimecirc*/,
           fp_systemuserid, sysdate /*timestamp*/,
           (x.totalcircinv - x.qtyassigned - x.freeissuedcircinv - x.qtyrentstop - x.freeseasoncircinv)
           /*qtyrentedperpiece*/, x.qtyrentedother, 0 /*variationrentedother*/, x.qtycustomerrentstop, 0
           /*variationqtycustomerrentstop*/, x.qtyrentstop, 0 /*variationqtyrentstop*/,
           x.emblemtemplate_id,
           case
              when x.emblemtemplate_id is null then
               0
              else
               (x.totalcircinv - x.qtyassigned - x.freeissuedcircinv - x.qtyrentstop - x.freeseasoncircinv)
            end /*qtyrentemblem*/, 0 /*variationqtyrentemblem*/, x.qtyreplacement, 0
           /*variationqtyreplacement*/, x.qtyassigned, 0 /*variationqtyassigned*/, 0
           /*qtyrenteddepositstock*/, 0 /*variationrenteddepositstock*/, x.qtysubstitute, 0
           /*variationqtysubstitute*/, nvl(x.chargeafterdepreciation, 'N'),
           (fp_sqyear * 100 + fp_sqweek)
           /*yearweek*/, x.qtyrentedperpiecepool, 0 /*variationrentedperpiecepool*/, null
           /*wearerinvoicecategory_id*/, x.qtyfullyamortized, 0 /*variationqtyfullyamortized*/, 0
           /*qtyrentedreturnrent*/, 0 /*variationqtyrentedreturnrent*/, 0 /*qtyrentedminrent*/, 0
           /*variationqtyrentedminrent*/);
    end add_uniquepoolitem;

    procedure add_uniqueitemreturnrent is
    begin
      --createSQItemInCirc based on Unique Item Return Rent
      if (fp_userentbasedonreturnweeks = 1 and fp_settlementactive = 0) then
        begin
          --add_uniqueitemreturnrent
          gv_message := 'add_uniqueitemreturnrent';
          merge into sqitemincirculation sqi
          using (select sum((case
                              when uirr.returnrenttype = 1 then
                               1
                              else
                               0
                            end)) variationqtyrentedminrent,
                        sum((case
                              when uirr.returnrenttype = 2 then
                               1
                              else
                               0
                            end)) qtyrentedminrent, uirr.customer_link_id, dp.department_id,
                        we.distributionpoint_id,
                        case fp_sqonwearerlevel
                           when 1 then
                            uirr.weareremployment_link_id
                           else
                            null
                         end weareremployment_id, uirr.product_link_id, uirr.sizedefinition_link_id,
                        uirr.finishingmethod_id,
                        sum((case
                              when exists (select 1
                                    from   temporarygarmentrentstop trs
                                    where  trs.customer_id = d.customer_id
                                    and    (trs.startyear < fp_sqyear and fp_sqyear < trs.endyear)
                                    or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                          fp_sqyear < trs.endyear)
                                    or     (trs.startyear < fp_sqyear and fp_sqyear = endyear and
                                          fp_sqweek <= trs.endweek)
                                    or     (trs.startyear = fp_sqyear and trs.startweek <= fp_sqweek and
                                          fp_sqyear = trs.endyear and fp_sqweek <= trs.endweek)) then
                               1
                              else
                               0
                            end)) qtycustomerrentstop
                 from   uniqueitemreturnrent uirr
                 join   weareremployment we
                 on     we.weareremployment_id = uirr.weareremployment_link_id
                 join   distributionpoint dp
                 on     dp.distributionpoint_id = we.distributionpoint_id
                 join   department d
                 on     d.department_id = dp.department_id
                 where  uirr.returnrenttype in (1, 2)
                 and    uirr.rentyear = fp_sqyear
                 and    uirr.rentweek = fp_sqweek
                 and    exists (select productlistpercustomer_id
                         from   productlistpercustomer plpc
                         where  plpc.customer_id = uirr.customer_link_id
                         and    plpc.product_id = uirr.product_link_id
                         and    (plpc.sizedefinition_id = uirr.sizedefinition_link_id or
                               plpc.sizedefinition_id = gv_sizestarid)
                         and    plpc.finishingmethod_id = uirr.finishingmethod_id
                         and    fp_sqdate between plpc.dateactive and plpc.dateinactive
                         and    plpc.billingmethod = 3
                         and    plpc.inventorybillingtype in (1, 2))
                 and    exists (select sqitemincirculation_id
                         from   sqitemincirculation sqi
                         where  sqi.statusquoheader_id = gv_sqheaderid
                         and    sqi.customer_link_id = uirr.customer_link_id
                         and    sqi.product_link_id = uirr.product_link_id
                         and    sqi.totalcircinv > 0)
                 group  by uirr.customer_link_id, dp.department_id, we.distributionpoint_id,
                           case fp_sqonwearerlevel
                             when 1 then
                              uirr.weareremployment_link_id
                             else
                              null
                           end, uirr.product_link_id, uirr.sizedefinition_link_id, uirr.finishingmethod_id) x
          on (sqi.statusquoheader_id = gv_sqheaderid and sqi.distributionpoint_link_id = x.distributionpoint_id and (sqi.weareremployment_link_id = x.weareremployment_id or (sqi.weareremployment_link_id is null and x.weareremployment_id is null)) and sqi.product_link_id = x.product_link_id and sqi.sizedefinition_link_id = x.sizedefinition_link_id and sqi.finishingmethod_id = x.finishingmethod_id)
          when matched then
            update
            set    sqi.variationqtyrentedminrent = sqi.variationqtyrentedminrent + case
                                                      when qtycustomerrentstop = 0 then
                                                       x.variationqtyrentedminrent
                                                      else
                                                       0
                                                    end,
                   sqi.qtyrentedminrent = sqi.qtyrentedminrent + case
                                             when qtycustomerrentstop = 0 then
                                              x.qtyrentedminrent
                                             else
                                              0
                                           end, sqi.systemuser_id = fp_systemuserid, sqi.timestamp = sysdate;
        end;
      end if;
    end add_uniqueitemreturnrent;

    procedure finalize is
      lv_totalquantity number;
    begin
      --finalize
      gv_message := 'finalize, update statusquoheader';
      update statusquoheader sqh
      set    sqh.totalquantity = nvl((select sum(sqi.totalcircinv)
                                      from   sqitemincirculation sqi
                                      where  sqi.statusquoheader_id = sqh.statusquoheader_id), 0)
      where  statusquoheader_id = gv_sqheaderid
      returning totalquantity into lv_totalquantity;
      commit;
      --return variable
      fp_sqheaderid := gv_sqheaderid;
      --write log
      write_log('Ready SQIemincirculation ' || lv_totalquantity ||
                ' totalcircinv, for statusquoheader_id ' || fp_sqheaderid, 1);
    end finalize;

    procedure update_plpc is
    begin
      --update_plpc
      gv_message := 'update_plpc';
      update productlistpercustomer l
      set    l.totalincirculation =
              (select sum(sqi.totalcircinv)
               from   statusquoheader sqh
               join   sqitemincirculation sqi
               on     sqi.statusquoheader_id = sqh.statusquoheader_id
               where  sqh.statusquoheader_id = gv_sqheaderid
               and    sqi.customer_link_id = l.customer_id
               and    sqi.product_link_id = l.product_id
               and    (sqi.sizedefinition_link_id = l.sizedefinition_id or
                     l.sizedefinition_id = gv_sizestarid)
               and    l.finishingmethod_id = sqi.finishingmethod_id
               and    sqh.statusquodate between l.dateactive and l.dateinactive),
             l.totalincirculationcharged =
              (select sum(sqi.qtyrentedperpiece) + sum(sqi.qtyrentedother) +
                       sum(sqi.qtyrenteddepositstock) + sum(case sqi.chargeafterdepreciation
                                                              when 'Y' then
                                                               sqi.beyondlifetimecirc
                                                              else
                                                               0
                                                            end) totalincirculationcharged
               from   statusquoheader sqh
               join   sqitemincirculation sqi
               on     sqi.statusquoheader_id = sqh.statusquoheader_id
               where  sqh.statusquoheader_id = gv_sqheaderid
               and    sqi.customer_link_id = l.customer_id
               and    sqi.product_link_id = l.product_id
               and    (sqi.sizedefinition_link_id = l.sizedefinition_id or
                     l.sizedefinition_id = gv_sizestarid)
               and    l.finishingmethod_id = sqi.finishingmethod_id
               and    sqh.statusquodate between l.dateactive and l.dateinactive)
      where  exists (select 1
              from   statusquoheader sqh
              join   sqitemincirculation sqi
              on     sqi.statusquoheader_id = sqh.statusquoheader_id
              where  sqh.statusquoheader_id = gv_sqheaderid
              and    sqi.customer_link_id = l.customer_id
              and    sqi.product_link_id = l.product_id
              and    (sqi.sizedefinition_link_id = l.sizedefinition_id or
                    l.sizedefinition_id = gv_sizestarid)
              and    l.finishingmethod_id = sqi.finishingmethod_id
              and    sqh.statusquodate between l.dateactive and l.dateinactive);
      --commit
      commit;
    end update_plpc;
  begin
    --Main program part createsqitemsincirculation
    --initialize
    initialize;
    --Create SQ Header
    create_sq_header;
    --Add_NON_uniqueitem
    add_non_uniqueitem;
    --Add_uniqueitem
    add_uniqueitem;
    --Add_uniquepoolitem
    add_uniquepoolitem;
    --add_uniqueitemreturnrent
    add_uniqueitemreturnrent;
    --finalize
    finalize;
    --update_plpc
    update_plpc;
  exception
    when no_data_found then
      begin
        rollback;
        fp_sqheaderid := null;
        write_log('No data found ' || gv_message || ' ' || sqlerrm, 3);
      end;
    when others then
      begin
        rollback;
        fp_sqheaderid := null;
        write_log(gv_message || ' ' || sqlerrm, 3);
      end;
  end createsqitemsincirculation;

  procedure check_duplicate_plpc is
    gv_separator varchar2(1) := ';';
    gv_counter   number := 0;

    --interal procedures
    procedure write_log(fp_message in varchar2, fp_priority in number) is
    begin
      /* writes logs into database.
      priority
      1 = Message
      2 = Warning
      3 = Error
      4 = Stack trace
      5 = Debug info  */
      abslogwrite.abslog_write(5, 'Pack_SQItemInCirculation', null, fp_priority,
                               'check_duplicate_plpc ' || fp_message);
      begin
        dbms_output.put_line(fp_message);
      exception
        when others then
          null;
      end;
    end write_log;
  begin
    --check_duplicate_plpc
    for zz in (select l.productlistpercustomer_id, c.customernumber, p.code productcode,
                      s.code sizecode, f.code finishingmethod, l.dateactive, l.dateinactive
               from   productlistpercustomer l
               join   customer c
               on     c.customer_id = l.customer_id
               join   product p
               on     p.product_id = l.product_id
               join   sizedefinition s
               on     s.sizedefinition_id = l.sizedefinition_id
               join   finishingmethod f
               on     f.finishingmethod_id = l.finishingmethod_id
               where  exists (select 1
                       from   productlistpercustomer x
                       where  x.customer_id = l.customer_id
                       and    x.product_id = l.product_id
                       and    x.sizedefinition_id = l.sizedefinition_id
                       and    x.finishingmethod_id = l.finishingmethod_id
                       and    x.productlistpercustomer_id <> l.productlistpercustomer_id
                       and    (x.dateactive between l.dateactive and l.dateinactive or
                             x.dateinactive between l.dateactive and l.dateinactive))
               order  by c.customernumber, p.code, s.code, f.code, l.productlistpercustomer_id) loop
      begin
        --
        if gv_counter = 0 then
          begin
            --output Header
            write_log('Productlistpercustomer_id' || gv_separator || 'Customernumber' ||
                      gv_separator || 'Productcode' || gv_separator || 'Sizecode' || gv_separator ||
                      'Finishingmethod' || gv_separator || 'Dateactive' || gv_separator ||
                      'Dateinactive' || gv_separator, 3);
          end;
        end if;
        --output duplicate PLPC's
        write_log(zz.productlistpercustomer_id || gv_separator || zz.customernumber ||
                  gv_separator || zz.productcode || gv_separator || zz.sizecode || gv_separator ||
                  zz.finishingmethod || gv_separator || to_char(zz.dateactive, 'dd-mm-yyyy') ||
                  gv_separator || to_char(zz.dateinactive, 'dd-mm-yyyy') || gv_separator, 3);
        --count duplicates
        gv_counter := gv_counter + 1;
      end;
    end loop;
    if gv_counter > 0 then
      begin
        write_log('Error ' || gv_counter || ' duplicate PLPC line(s)!', 3);
      end;
    else
      begin
        write_log('All ok, no duplicate PLPC lines', 1);
      end;
    end if;
  exception
    when others then
      begin
        rollback;
        write_log(sqlerrm, 3);
      end;
  end check_duplicate_plpc;

end pack_sqitemsincirculation_new;
/
