create or replace package PACK_SQMaxItemInCirculation is

  SQ_MAX_ITEMS_IN_CIRCULATION statusquoheader.statusquotype%type := 13;

  procedure createSQMaxItemsInCirculation(sqHeaderID   out statusquoheader.statusquoheader_id%type,
                                          processBUID  businessunit.businessunit_id%type,
                                          invoiceBUID  businessunit.businessunit_id%type,
                                          sqDate       date,
                                          sqYear       int,
                                          sqWeek       int,
                                          systemUserID systemuser.systemuser_id%type,
                                          checkInvoiceBu int);

  procedure createSQItemOnUniqueItem(sqHeaderID            statusquoheader.statusquoheader_id%type,
                                     processBUID           businessunit.businessunit_id%type,
                                     invoiceBUID           businessunit.businessunit_id%type,
                                     sqDate                date,
                                     sqYear                int,
                                     sqWeek                int,
                                     statusUIInCirculation status.status_id%type,
                                     sizeStarID            sizedefinition.sizedefinition_id%type,
                                     systemUserID          systemuser.systemuser_id%type,
                                     checkInvoiceBu        int);

  procedure createSQItemOnRentalQtyAdj(sqHeaderID   statusquoheader.statusquoheader_id%type,
                                       processBUID  businessunit.businessunit_id%type,
                                       invoiceBUID  businessunit.businessunit_id%type,
                                       sqDate       date,
                                       sqYear       int,
                                       sqWeek       int,
                                       systemUserID systemuser.systemuser_id%type,
                                       checkInvoiceBu int);

  procedure createOrStoreOnSQ(sqHeaderID   statusquoheader.statusquoheader_id%type,
                              costCenterID costcenter.costcenter_id%type,
                              productID    product.product_id%type,
                              sizeID       sizedefinition.sizedefinition_id%type,
                              finMethID    finishingmethod.finishingmethod_id%type,
                              qtyPerPiece  sqmaxitemincirculation.qtyrentedperpiece%type,
                              qtyPlusAdj   sqmaxitemincirculation.qtyplusadjust%type,
                              qtyRentStop  sqmaxitemincirculation.qtyrentstop%type,
                              qtyPlusAdjRentStop    sqmaxitemincirculation.qtyplusadjustrentstop%type,
                              systemUserID systemuser.systemuser_id%type);

  procedure createOrStoreOnTrigger_UI(uniqueItemID uniqueitem.uniqueitem_id%type,
                                      qty          int,
                                      returnFromCustomerDate statusquoheader.statusquodate%type,
                                      checkInvoiceBu        int);

  procedure createOrStoreOnTrigger_RQA(plpdipID productlistperdistribpoint.productlistperdistribpoint_id%type,
                                       qty      int,
                                       returnFromCustomerDate statusquoheader.statusquodate%type,
                                       checkInvoiceBu        int);

  procedure createOrStoreOnTrigger(processBUID  businessunit.businessunit_id%type,
                                   invoiceBUID  businessunit.businessunit_id%type,
                                   costCenterID costcenter.costcenter_id%type,
                                   productID    product.product_id%type,
                                   sizeID       sizedefinition.sizedefinition_id%type,
                                   finMethID    finishingmethod.finishingmethod_id%type,
                                   qty          int,
                                   returnFromCustomerDate statusquoheader.statusquodate%type,
                                   tempDelStops int);

  procedure t_createOrStoreOnTrigger_UI(uniqueItemID           uniqueitem.uniqueitem_id%type,
                                        qty                    int,
                                        returnFromCustomerDate statusquoheader.statusquodate%type,
                                        productID              product.product_id%type,
                                        sizeID                 sizedefinition.sizedefinition_id%type,
                                        finishingMethodID      finishingmethod.finishingmethod_id%type,
                                        weareremploymentID     uniqueitemnonpool.weareremployment_id%type
                                        );

  procedure t_createOrStoreOnTrigger_RQA(plpdipID productlistperdistribpoint.productlistperdistribpoint_id%type,
                                         qty      int,
                                         returnFromCustomerDate statusquoheader.statusquodate%type
                                          );

end PACK_SQMaxItemInCirculation;
/
create or replace package body PACK_SQMaxItemInCirculation is

  procedure createSQMaxItemsInCirculation(sqHeaderID   out statusquoheader.statusquoheader_id%type,
                                          processBUID  businessunit.businessunit_id%type,
                                          invoiceBUID  businessunit.businessunit_id%type,
                                          sqDate       date,
                                          sqYear       int,
                                          sqWeek       int,
                                          systemUserID systemuser.systemuser_id%type,
                                          checkInvoiceBu int) is
    statusUIInCirculation status.status_id%type;
    sizeStarID            sizedefinition.sizedefinition_id%type;
  begin
    -- In Circulation
    select status_id
      into statusUIInCirculation
      from status
     where statustype = 'UI'
       and code = '20';
    -- size *
    select sizedefinition_id
      into sizeStarID
      from sizedefinition
     where code = '*';

    -- StatusQuoHeader
    insert into statusquoheader
      (statusquoheader_id,
       processbusinessunit_id,
       invoicebusinessunit_id,
       statusquodate,
       statusquotype,
       totalquantity,
       year,
       week,
       month,
       systemuser_id,
       timestamp)
    values
      (seq_statusquoheader.nextval,
       processBUID,
       invoiceBUID,
       sqDate,
       SQ_MAX_ITEMS_IN_CIRCULATION,
       0,
       sqYear,
       sqWeek,
       0,
       systemUserID,
       sysdate)
    returning statusquoheader_id into sqHeaderID;

    -- SQMaxItemInCirculation based on RentalQuantityAdjustment
    createSQItemOnRentalQtyAdj(sqHeaderID,
                               processBUID,
                               invoiceBUID,
                               sqDate,
                               sqYear,
                               sqWeek,
                               systemUserID,
                               checkInvoiceBu);

    -- SQMaxItemInCirculation based on UniqueItems
    createSQItemOnUniqueItem(sqHeaderID,
                             processBUID,
                             invoiceBUID,
                             sqDate,
                             sqYear,
                             sqWeek,
                             statusUIInCirculation,
                             sizeStarID,
                             systemUserID,
                             checkInvoiceBu);
    -- update StatusQuoHeader.totalquantity
    update statusquoheader
       set totalquantity = (select nvl(sum(qtyrentedperpiece), 0)
                              from sqmaxitemincirculation
                             where sqmaxitemincirculation.statusquoheader_id =
                                   statusquoheader.statusquoheader_id)
     where statusquoheader_id = sqHeaderID;
    commit;
  end;

  procedure createSQItemOnUniqueItem(sqHeaderID            statusquoheader.statusquoheader_id%type,
                                     processBUID           businessunit.businessunit_id%type,
                                     invoiceBUID           businessunit.businessunit_id%type,
                                     sqDate                date,
                                     sqYear                int,
                                     sqWeek                int,
                                     statusUIInCirculation status.status_id%type,
                                     sizeStarID            sizedefinition.sizedefinition_id%type,
                                     systemUserID          systemuser.systemuser_id%type,
                                     checkInvoiceBu        int) is

    cursor stmt is
      select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.product_id = p.product_id
   and ui.sizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and ca.invoicebusinessunit_id = invoiceBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and

       not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and

       ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek)) or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is null
   and ui.substituteforsizedefinition_id is null
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id
union all --union with the unique items that are substitutes item for product and size
select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.substituteforproduct_id = p.product_id
   and ui.substituteforsizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and ca.invoicebusinessunit_id = invoiceBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek))or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is not null
   and ui.substituteforsizedefinition_id is not null
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id
 union all --union with the unique items that has the size as substitute this means and ui.substituteforsizedefinition_id is not null
select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and ui.product_id = p.product_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.substituteforsizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and ca.invoicebusinessunit_id = invoiceBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek)) or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is null --the product is not a substitute
   and ui.substituteforsizedefinition_id is not null --only the size is a substitute
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id;
cursor stmt2 is --this is made for system setting checkinvoiceBU
      select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.product_id = p.product_id
   and ui.sizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and

       not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and

       ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek)) or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is null
   and ui.substituteforsizedefinition_id is null
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id
union all
select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.substituteforproduct_id = p.product_id
   and ui.substituteforsizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek)) or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is not null
   and ui.substituteforsizedefinition_id is not null
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id
union all
select dip.costcenter_id,
       p.product_id,
       s.sizedefinition_id,
       f.finishingmethod_id,
       count(ui.uniqueitem_id) qtyPerPiece
  from uniqueitem             ui,
       uniqueitemfixedinfo    uifi,
       uniqueitemnonpool      uinp,
       weareremployment       we,
       wearerinventory        wi,
       distributionpoint      dip,
       department             dep,
       customeractivities     ca,
       product                p,
       productgroup           pg,
       sizedefinition         s,
       finishingmethod        f,
       productlistpercustomer plpc
 where ui.uniqueitem_id = uinp.uniqueitem_id
   and ui.uniqueitem_id = uifi.uniqueitem_id
   and uinp.weareremployment_id = we.weareremployment_id
   and ui.product_id = p.product_id
   and we.distributionpoint_id = dip.distributionpoint_id
   and dip.department_id = dep.department_id
   and ui.substituteforsizedefinition_id = s.sizedefinition_id
   and ui.finishingmethod_id = f.finishingmethod_id
   and p.productgroup_id = pg.productgroup_id
   and abs(pg.activity_id) = abs(ca.activity_id)
   and dep.customer_id = ca.customer_id
   and uinp.wearerinventory_id = wi.wearerinventory_id
   and ca.processbusinessunit_id = processBUID
   and abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
   and ui.status_id = statusUIInCirculation
   and plpc.customer_id = dep.customer_id
   and plpc.product_id = p.product_id
   and (plpc.sizedefinition_id = s.sizedefinition_id or
       plpc.sizedefinition_id = sizeStarID)
   and plpc.finishingmethod_id = f.finishingmethod_id
   and sqDate between plpc.dateactive and plpc.dateinactive
   and plpc.billingmethod = 3
   and plpc.inventorybillingtype = 1
   and ui.customerowned = 'N'
   and ui.leaseweeks is null
   and not
        (uinp.freeextragarment = 'Y' and
        (uinp.enddatefreeextra > sqDate or uinp.enddatefreeextra is null))
   and not exists (select t.temporarygarmentrentstop_id
          from temporarygarmentrentstop t
         where t.customer_id = dep.customer_id
           and f_isinyearweeklifespan(startyear,
                                      startweek,
                                      sqYear,
                                      sqWeek,
                                      endyear,
                                      endweek) = 1)
   and ((sqDate < wi.temporaryrentstopstartdate or
       sqDate > temporaryrentstopenddate) or
       (wi.temporaryrentstopstartdate is null and
       wi.temporaryrentstopenddate is null))
   and ((wi.season_id is null or exists
        (select sw.season_id
           from seasonweek sw
          where sw.season_id = wi.season_id
            and sw.weeknumber = sqWeek)) or
          (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 = sqWeek)
          and plpc.seasonalrent = 2)))
   and ((sqDate <= uifi.enddatefullrent and
       plpc.percentageafterdepreciation = 0) or
       (plpc.percentageafterdepreciation > 0))
   and ui.substituteforproduct_id is null -- the product is not a substitute
   and ui.substituteforsizedefinition_id is not null
 group by dip.costcenter_id,
          p.product_id,
          s.sizedefinition_id,
          f.finishingmethod_id;
  begin
    if (checkInvoiceBu = 1) then
      for rs in stmt loop
        createOrStoreOnSQ(sqHeaderID,
                          rs.costcenter_id,
                          rs.product_id,
                          rs.sizedefinition_id,
                          rs.finishingmethod_id,
                          rs.qtyPerPiece,
                          0,
                          0,
                          0,
                          systemUserID);
      end loop;
     else
      for rs in stmt2 loop
        createOrStoreOnSQ(sqHeaderID,
                          rs.costcenter_id,
                          rs.product_id,
                          rs.sizedefinition_id,
                          rs.finishingmethod_id,
                          rs.qtyPerPiece,
                          0,
                          0,
                          0,
                          systemUserID);
      end loop;
     end if;

  end;

  procedure createSQItemOnRentalQtyAdj(sqHeaderID   statusquoheader.statusquoheader_id%type,
                                       processBUID  businessunit.businessunit_id%type,
                                       invoiceBUID  businessunit.businessunit_id%type,
                                       sqDate       date,
                                       sqYear       int,
                                       sqWeek       int,
                                       systemUserID systemuser.systemuser_id%type,
                                       checkInvoiceBu int) is

    cursor stmt is
      select dip.costcenter_id,
             p.product_id,
             s.sizedefinition_id,
             f.finishingmethod_id,
             sum(rqa.quantity) qtyPerPiece,
             nvl((select sum(rqx.quantity)
                   from rentalqtyadjustment        rqx,
                        productlistperdistribpoint plpdipx,
                        distributionpoint          dipx,
                        standarddeliveryline stdlx
                  where rqx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and stdlx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and plpdipx.distributionpoint_id =
                        dipx.distributionpoint_id
                    and dipx.costcenter_id = dip.costcenter_id
                    and plpdipx.product_id = p.product_id
                    and plpdipx.sizedefinition_id = s.sizedefinition_id
                    and plpdipx.finishingmethod_id = f.finishingmethod_id
                    and stdlx.dateactive <= sqDate
                    and stdlx.dateinactive >= sqDate
                    and rqx.adjustmentdate > sqDate
                    and getyearofweek(rqx.adjustmentdate) = sqYear
                    and getisoweek(rqx.adjustmentdate) = sqWeek),
                 0) qtyPlusAdj,
                 (select count(*)
                  from temporarydeliverystop tds,
                       standarddeliveryline  stdlx,
                       productlistperdistribpoint plpdipx,
                       distributionpoint          dipx
                  where tds.standarddeliveryline_id = stdlx.standarddeliveryline_id
                    and stdlx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and plpdipx.distributionpoint_id =
                        dipx.distributionpoint_id
                    and dipx.costcenter_id = dip.costcenter_id
                    and plpdipx.product_id = p.product_id
                    and plpdipx.sizedefinition_id = s.sizedefinition_id
                    and plpdipx.finishingmethod_id = f.finishingmethod_id
                    and stdlx.dateactive <= sqDate
                    and stdlx.dateinactive >= sqDate
                  and ((tds.tempstoprentforweek = 'Y' and tds.temporarystopstartweek <= sqWeek and sqWeek <= tds.temporarystopendweek) or
                       (tds.tempstoprentforyearweek = 'Y' and (
                               (tds.temporarystopstartyear < sqYear and sqYear < tds.temporarystopendyear) or
                               (tds.temporarystopstartyear = sqYear and sqYear < tds.temporarystopendyear and tds.tempstopstartweekforyear <= sqWeek) or
                               (tds.temporarystopstartyear < sqYear and sqYear = tds.temporarystopendyear and sqWeek <= tds.tempstopendweekforyear) or
                               (tds.temporarystopstartyear = sqYear and sqYear = tds.temporarystopendyear and tds.tempstopstartweekforyear <= sqWeek and sqWeek <= tds.tempstopendweekforyear)
                               )
                       ) or
                       (tds.tempstoprentfordate = 'Y' and tds.startdate <= sqDate and sqDate <= tds.enddate)
                   )
                  ) tempDelStops
        from rentalqtyadjustment        rqa,
             productlistperdistribpoint plpdip,
             distributionpoint          dip,
             department                 dep,
             customeractivities         ca,
             product                    p,
             productgroup               pg,
             sizedefinition             s,
             finishingmethod            f,
             standarddeliveryline       stdl
             --,productlistpercustomer     plpc
       where rqa.productlistperdistribpoint_id =
             plpdip.productlistperdistribpoint_id
         and plpdip.distributionpoint_id = dip.distributionpoint_id
         and stdl.productlistperdistribpoint_id = plpdip.productlistperdistribpoint_id
         and dip.department_id = dep.department_id
         and plpdip.product_id = p.product_id
         and plpdip.sizedefinition_id = s.sizedefinition_id
         and plpdip.finishingmethod_id = f.finishingmethod_id
         and p.productgroup_id = pg.productgroup_id
         and abs(pg.activity_id) = abs(ca.activity_id)
         and dep.customer_id = ca.customer_id
         and stdl.dateactive <= sqDate
         and stdl.dateinactive >= sqDate
         and rqa.adjustmentdate <= sqDate
         and ca.processbusinessunit_id = processBUID
         and ca.invoicebusinessunit_id = invoiceBUID
         --and plpc.productlistpercustomer_id = stdl.productlistpercustomer_id
         --and plpc.dateactive <= sqDate
         --and plpc.dateinactive >= sqDate
       group by dip.costcenter_id,
                p.product_id,
                s.sizedefinition_id,
                f.finishingmethod_id;

   cursor stmt2 is
      select dip.costcenter_id,
             p.product_id,
             s.sizedefinition_id,
             f.finishingmethod_id,
             sum(rqa.quantity) qtyPerPiece,
             nvl((select sum(rqx.quantity)
                   from rentalqtyadjustment        rqx,
                        productlistperdistribpoint plpdipx,
                        distributionpoint          dipx,
                        standarddeliveryline stdlx
                  where rqx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and stdlx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and plpdipx.distributionpoint_id =
                        dipx.distributionpoint_id
                    and dipx.costcenter_id = dip.costcenter_id
                    and plpdipx.product_id = p.product_id
                    and plpdipx.sizedefinition_id = s.sizedefinition_id
                    and plpdipx.finishingmethod_id = f.finishingmethod_id
                    and stdlx.dateactive <= sqDate
                    and stdlx.dateinactive >= sqDate
                    and rqx.adjustmentdate > sqDate
                    and getyearofweek(rqx.adjustmentdate) = sqYear
                    and getisoweek(rqx.adjustmentdate) = sqWeek),
                 0) qtyPlusAdj,
                 (select count(*)
                  from temporarydeliverystop tds,
                       standarddeliveryline  stdlx,
                       productlistperdistribpoint plpdipx,
                       distributionpoint          dipx
                  where tds.standarddeliveryline_id = stdlx.standarddeliveryline_id
                    and stdlx.productlistperdistribpoint_id =
                        plpdipx.productlistperdistribpoint_id
                    and plpdipx.distributionpoint_id =
                        dipx.distributionpoint_id
                    and dipx.costcenter_id = dip.costcenter_id
                    and plpdipx.product_id = p.product_id
                    and plpdipx.sizedefinition_id = s.sizedefinition_id
                    and plpdipx.finishingmethod_id = f.finishingmethod_id
                    and stdlx.dateactive <= sqDate
                    and stdlx.dateinactive >= sqDate
                  and ((tds.tempstoprentforweek = 'Y' and tds.temporarystopstartweek <= sqWeek and sqWeek <= tds.temporarystopendweek) or
                       (tds.tempstoprentforyearweek = 'Y' and (
                               (tds.temporarystopstartyear < sqYear and sqYear < tds.temporarystopendyear) or
                               (tds.temporarystopstartyear = sqYear and sqYear < tds.temporarystopendyear and tds.tempstopstartweekforyear <= sqWeek) or
                               (tds.temporarystopstartyear < sqYear and sqYear = tds.temporarystopendyear and sqWeek <= tds.tempstopendweekforyear) or
                               (tds.temporarystopstartyear = sqYear and sqYear = tds.temporarystopendyear and tds.tempstopstartweekforyear <= sqWeek and sqWeek <= tds.tempstopendweekforyear)
                               )
                       ) or
                       (tds.tempstoprentfordate = 'Y' and tds.startdate <= sqDate and sqDate <= tds.enddate)
                   )
                  ) tempDelStops
        from rentalqtyadjustment        rqa,
             productlistperdistribpoint plpdip,
             distributionpoint          dip,
             department                 dep,
             customeractivities         ca,
             product                    p,
             productgroup               pg,
             sizedefinition             s,
             finishingmethod            f,
             standarddeliveryline       stdl
             --,productlistpercustomer     plpc
       where rqa.productlistperdistribpoint_id =
             plpdip.productlistperdistribpoint_id
         and plpdip.distributionpoint_id = dip.distributionpoint_id
         and stdl.productlistperdistribpoint_id = plpdip.productlistperdistribpoint_id
         and dip.department_id = dep.department_id
         and plpdip.product_id = p.product_id
         and plpdip.sizedefinition_id = s.sizedefinition_id
         and plpdip.finishingmethod_id = f.finishingmethod_id
         and p.productgroup_id = pg.productgroup_id
         and abs(pg.activity_id) = abs(ca.activity_id)
         and dep.customer_id = ca.customer_id
         and stdl.dateactive <= sqDate
         and stdl.dateinactive >= sqDate
         and rqa.adjustmentdate <= sqDate
         and ca.processbusinessunit_id = processBUID
         --and plpc.productlistpercustomer_id = stdl.productlistpercustomer_id
         --and plpc.dateactive <= sqDate
         --and plpc.dateinactive >= sqDate
       group by dip.costcenter_id,
                p.product_id,
                s.sizedefinition_id,
                f.finishingmethod_id;


  begin

    if (checkInvoiceBu =1 ) then
      for rs in stmt loop
        if rs.tempDelStops > 0 then
          createOrStoreOnSQ(sqHeaderID,
                            rs.costcenter_id,
                            rs.product_id,
                            rs.sizedefinition_id,
                            rs.finishingmethod_id,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            systemUserID);
        else
          createOrStoreOnSQ(sqHeaderID,
                            rs.costcenter_id,
                            rs.product_id,
                            rs.sizedefinition_id,
                            rs.finishingmethod_id,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            0,
                            0,
                            systemUserID);
        end if;
      end loop;
     else
      for rs in stmt2 loop
        if rs.tempDelStops > 0 then
          createOrStoreOnSQ(sqHeaderID,
                            rs.costcenter_id,
                            rs.product_id,
                            rs.sizedefinition_id,
                            rs.finishingmethod_id,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            systemUserID);
        else
          createOrStoreOnSQ(sqHeaderID,

                            rs.costcenter_id,
                            rs.product_id,
                            rs.sizedefinition_id,
                            rs.finishingmethod_id,
                            rs.qtyPerPiece,
                            rs.qtyPlusAdj,
                            0,
                            0,
                            systemUserID);
        end if;
      end loop;
     end if;
  end;

  procedure createOrStoreOnSQ(sqHeaderID   statusquoheader.statusquoheader_id%type,
                              costCenterID costcenter.costcenter_id%type,
                              productID    product.product_id%type,
                              sizeID       sizedefinition.sizedefinition_id%type,
                              finMethID    finishingmethod.finishingmethod_id%type,
                              qtyPerPiece  sqmaxitemincirculation.qtyrentedperpiece%type,
                              qtyPlusAdj   sqmaxitemincirculation.qtyplusadjust%type,
                              qtyRentStop  sqmaxitemincirculation.qtyrentstop%type,
                              qtyPlusAdjRentStop    sqmaxitemincirculation.qtyplusadjustrentstop%type,
                              systemUserID systemuser.systemuser_id%type) is
  begin
    begin
      insert into sqmaxitemincirculation
        (sqmaxitemincirculation_id,
         statusquoheader_id,
         costcenter_link_id,
         product_link_id,
         sizedefinition_link_id,
         finishingmethod_id,
         qtyrentedperpiece,
         qtyplusadjust,
         qtyrentstop,
         qtyplusadjustrentstop,
         systemuser_id,
         timestamp)
      values
        (seq_sqmaxitemincirculation.nextval,
         sqHeaderID,
         costCenterID,
         productID,
         sizeID,
         finMethID,
         qtyPerPiece,
         qtyPlusAdj,
         qtyRentStop,
         qtyPlusAdjRentStop,
         systemUserID,
         sysdate);
    exception
      when dup_val_on_index then
        update sqmaxitemincirculation
           set qtyrentedperpiece = qtyrentedperpiece + qtyPerPiece,
               qtyplusadjust     = qtyplusadjust + qtyPlusAdj,
               qtyrentstop       = qtyrentstop + qtyRentStop,
               qtyplusadjustrentstop = qtyplusadjustrentstop + qtyPlusAdjRentStop,
               systemuser_id     = systemUserID,
               timestamp         = sysdate
         where statusquoheader_id = sqHeaderID
           and costcenter_link_id = costCenterID
           and product_link_id = productID
           and sizedefinition_link_id = sizeID
           and finishingmethod_id = finMethID;
    end;
  end;

  procedure createOrStoreOnTrigger_UI(uniqueItemID uniqueitem.uniqueitem_id%type,
                                      qty          int,
                                      returnFromCustomerDate statusquoheader.statusquodate%type,
                                      checkInvoiceBu        int) is
    cursor stmt is
      select dip.costcenter_id,
             ui.product_id,
             ui.sizedefinition_id,
             ui.finishingmethod_id,
             ca.processbusinessunit_id,
             ca.invoicebusinessunit_id,
             (SELECT COUNT(t.temporarygarmentrentstop_id)
                   FROM temporarygarmentrentstop t
                  WHERE t.customer_id = dep.customer_id
                        AND f_isinyearweeklifespan(startyear,
                                                   startweek,
                                                   getyearofweek(sysdate),
                                                   getisoweek(sysdate),
                                                   endyear,
                                                   endweek) = 1) qtyCustRentStop,
             (SELECT COUNT(wi.wearerinventory_id)
                   FROM wearerinventory wi
                   WHERE wi.weareremployment_id = we.weareremployment_id
                         AND wi.product_id = ui.product_id
                         AND wi.sizedefinition_id = ui.sizedefinition_id
                         AND wi.finishingmethod_id = ui.finishingmethod_id
                         AND sysdate BETWEEN wi.temporaryrentstopstartdate AND
                         wi.temporaryrentstopenddate) qtyRentStop
        from uniqueitem         ui,
             uniqueitemnonpool  uinp,
             weareremployment   we,
             distributionpoint  dip,
             department         dep,
             product            p,
             productgroup       pg,
             customeractivities ca
       where ui.uniqueitem_id = uniqueItemID
         and ui.uniqueitem_id = uinp.uniqueitem_id
         and uinp.weareremployment_id = we.weareremployment_id
         and we.distributionpoint_id = dip.distributionpoint_id
         and ui.product_id = p.product_id
         and p.productgroup_id = pg.productgroup_id
         and pg.activity_id = ca.activity_id
         and dip.department_id = dep.department_id
         and dep.customer_id = ca.customer_id;
  begin
    for rs in stmt loop
      if (checkInvoiceBu = 1)then --checkInvoiceBu = true
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.invoicebusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.qtyCustRentStop + rs.qtyRentStop );
      else
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.processbusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.qtyCustRentStop + rs.qtyRentStop );
       end if;
    end loop;
  end;

  procedure createOrStoreOnTrigger_RQA(plpdipID productlistperdistribpoint.productlistperdistribpoint_id%type,
                                       qty      int,
                                       returnFromCustomerDate statusquoheader.statusquodate%type,
                                       checkInvoiceBu        int) is
    currentyearnumber integer;
    currentweeknumber integer;
    cursor stmt is
      select dip.costcenter_id,
             plpdip.product_id,
             plpdip.sizedefinition_id,
             plpdip.finishingmethod_id,
             ca.processbusinessunit_id,
             ca.invoicebusinessunit_id,
             (select count(*)
              from temporarydeliverystop tds,
                   standarddeliveryline  stdl
              where tds.standarddeliveryline_id = stdl.standarddeliveryline_id
                and stdl.productlistperdistribpoint_id = plpdip.productlistperdistribpoint_id
                and stdl.dateactive <= sysdate
                and stdl.dateinactive >= sysdate
                and ((tds.tempstoprentforweek = 'Y' and tds.temporarystopstartweek <= currentweeknumber and currentweeknumber <= tds.temporarystopendweek) or
                     (tds.tempstoprentforyearweek = 'Y' and (
                           (tds.temporarystopstartyear < currentyearnumber and currentyearnumber < tds.temporarystopendyear) or
                           (tds.temporarystopstartyear = currentyearnumber and currentyearnumber < tds.temporarystopendyear and tds.tempstopstartweekforyear <= currentweeknumber) or
                           (tds.temporarystopstartyear < currentyearnumber and currentyearnumber = tds.temporarystopendyear and currentweeknumber <= tds.tempstopendweekforyear) or
                           (tds.temporarystopstartyear = currentyearnumber and currentyearnumber = tds.temporarystopendyear and tds.tempstopstartweekforyear <= currentweeknumber and currentweeknumber <= tds.tempstopendweekforyear)
                           )
                     ) or
                     (tds.tempstoprentfordate = 'Y' and tds.startdate <= sysdate and sysdate <= tds.enddate)
                 )
              ) tempDelStops
        from productlistperdistribpoint plpdip,
             distributionpoint          dip,
             department                 dep,
             product                    p,
             productgroup               pg,
             customeractivities         ca
       where plpdip.productlistperdistribpoint_id = plpdipID
         and plpdip.distributionpoint_id = dip.distributionpoint_id
         and plpdip.product_id = p.product_id
         and p.productgroup_id = pg.productgroup_id
         and pg.activity_id = ca.activity_id
         and dip.department_id = dep.department_id
         and dep.customer_id = ca.customer_id;
  begin
    currentyearnumber:=cast(to_char(sysdate, 'IYYY') as numeric);
    currentweeknumber:=getisoweek(sysdate);
    for rs in stmt loop
      if (checkInvoiceBu = 1)then --checkInvoiceBu = true
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.invoicebusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.tempdelstops );
      else
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.processbusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.tempdelstops );
      end if;
    end loop;
  end;

  procedure createOrStoreOnTrigger(processBUID  businessunit.businessunit_id%type,
                                   invoiceBUID  businessunit.businessunit_id%type,
                                   costCenterID costcenter.costcenter_id%type,
                                   productID    product.product_id%type,
                                   sizeID       sizedefinition.sizedefinition_id%type,
                                   finMethID    finishingmethod.finishingmethod_id%type,
                                   qty          int,
                                   returnFromCustomerDate statusquoheader.statusquodate%type,
                                   tempDelStops int) is
    sqHeaderID statusquoheader.statusquoheader_id%type;
    sqID       sqmaxitemincirculation.sqmaxitemincirculation_id%type;
    newQty     int;
    statusDate statusquoheader.statusquodate%type;
  begin

    begin
      select sq.statusquoheader_id, sq.statusquodate
        into sqHeaderID , statusDate
        from statusquoheader sq
       where sq.statusquotype = SQ_MAX_ITEMS_IN_CIRCULATION
         and getisoweek(sq.statusquodate) = getisoweek(sysdate)
         and getyearofweek(sq.statusquodate) = getyearofweek(sysdate)
         and sq.processbusinessunit_id = processBUID
         and sq.invoicebusinessunit_id = invoiceBUID;

      if (trunc(returnFromCustomerDate)=trunc(to_date('1900-01-01','yyyy-mm-dd'))) then
        update sqmaxitemincirculation
           set qtyplusadjust = qtyplusadjust + qty,
               qtyplusadjustrentstop  = qtyplusadjustrentstop +
                                      (case when tempDelStops > 0 then qty else 0 end),
               systemuser_id = 1,
               timestamp     = sysdate
         where statusquoheader_id = sqHeaderID
           and costcenter_link_id = costCenterID
           and product_link_id = productID
           and sizedefinition_link_id = sizeID
           and finishingmethod_id = finMethID returning
         sqmaxitemincirculation_id, qtyplusadjust into sqID, newQty;
        if (newQty < 0) then
          update sqmaxitemincirculation
             set qtyplusadjust = 0,
             qtyplusadjustrentstop  = (case when tempDelStops > 0 then 0 else qtyplusadjustrentstop end)
           where sqmaxitemincirculation_id = sqID;
         end if;
      else
        if (qty < 0 and returnFromCustomerDate < statusDate) then
             update sqmaxitemincirculation
             set qtyminadjust = qtyminadjust + 1,
                 qtyplusadjustrentstop  = qtyplusadjustrentstop + (case when tempDelStops > 0 then 1 else 0 end),
                 systemuser_id = 1,
                 timestamp     = sysdate
           where statusquoheader_id = sqHeaderID
             and costcenter_link_id = costCenterID
             and product_link_id = productID
             and sizedefinition_link_id = sizeID
             and finishingmethod_id = finMethID;
         end if;
       end if;

      if (qty > 0 and sql%rowcount = 0) then
        insert into sqmaxitemincirculation
          (sqmaxitemincirculation_id,
           statusquoheader_id,
           costcenter_link_id,
           product_link_id,
           sizedefinition_link_id,
           finishingmethod_id,
           qtyrentedperpiece,
           qtyplusadjust,
           qtyplusadjustrentstop,
           systemuser_id,
           timestamp)
        values
          (seq_sqmaxitemincirculation.nextval,
           sqHeaderID,
           costCenterID,
           productID,
           sizeID,
           finMethID,
           0,
           qty,
           (case when tempDelStops > 0 then qty else 0 end),
           1,
           sysdate);
      end if;

    exception
      when no_data_found then
        null;
    end;
  end;

 procedure t_createOrStoreOnTrigger_UI(uniqueItemID           uniqueitem.uniqueitem_id%type,
                                        qty                    int,
                                        returnFromCustomerDate statusquoheader.statusquodate%type,
                                        productID              product.product_id%type,
                                        sizeID                 sizedefinition.sizedefinition_id%type,
                                        finishingMethodID      finishingmethod.finishingmethod_id%type,
                                        weareremploymentID     uniqueitemnonpool.weareremployment_id%type
                                        ) is
    cursor stmt is
      select dip.costcenter_id,
             ca.processbusinessunit_id,
             ca.invoicebusinessunit_id,
             (SELECT COUNT(t.temporarygarmentrentstop_id)
                   FROM temporarygarmentrentstop t
                  WHERE t.customer_id = dep.customer_id
                        AND f_isinyearweeklifespan(startyear,
                                                   startweek,
                                                   getyearofweek(sysdate),
                                                   getisoweek(sysdate),
                                                   endyear,
                                                   endweek) = 1) qtyCustRentStop,
             (SELECT COUNT(wi.wearerinventory_id)
                   FROM wearerinventory wi
                   WHERE wi.weareremployment_id = weareremploymentID
                         AND wi.product_id = productID
                         AND wi.sizedefinition_id = sizeID
                         AND wi.finishingmethod_id = finishingMethodID
                         AND sysdate BETWEEN wi.temporaryrentstopstartdate AND
                         wi.temporaryrentstopenddate) qtyRentStop
        from weareremployment   we,
             distributionpoint  dip,
             department         dep,
             product            p,
             productgroup       pg,
             customeractivities ca
       where weareremploymentID = we.weareremployment_id
         and we.distributionpoint_id = dip.distributionpoint_id
         and productID = p.product_id
         and p.productgroup_id = pg.productgroup_id
         and pg.activity_id = ca.activity_id
         and dip.department_id = dep.department_id
         and dep.customer_id = ca.customer_id;
     checkInvoiceBu int :=1;
  begin
    for rs in stmt loop
      BEGIN
     select case ss.value when 'Y' then 1
                                   else -1
            end
      into   checkInvoiceBu
      from   businessunit bu
             join systemsetting ss
                  on bu.plant_id = ss.plant_id
      where  bu.businessunit_id = rs.processbusinessunit_id
             and upper(ss.name) = 'CHECKINVOICEBUSINESSUNIT'
             and ss.groupname = 'Billing';
      EXCEPTION
        WHEN no_data_found THEN
          checkInvoiceBu := -1;
      END;

      if (checkInvoiceBu = 1)then --checkInvoiceBu = true
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.invoicebusinessunit_id,
                                                         rs.costcenter_id,
                                                         productID  ,
                                                         sizeID,
                                                         finishingMethodID,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.qtyCustRentStop + rs.qtyRentStop );
      else
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.processbusinessunit_id,
                                                         rs.costcenter_id,
                                                         productID  ,
                                                         sizeID,
                                                         finishingMethodID,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.qtyCustRentStop + rs.qtyRentStop );
       end if;
    end loop;
  end;

  procedure t_createOrStoreOnTrigger_RQA(plpdipID productlistperdistribpoint.productlistperdistribpoint_id%type,
                                       qty      int,
                                       returnFromCustomerDate statusquoheader.statusquodate%type
                                        ) is
    currentyearnumber integer;
    currentweeknumber integer;
    cursor stmt is
      select dip.costcenter_id,
             plpdip.product_id,
             plpdip.sizedefinition_id,
             plpdip.finishingmethod_id,
             ca.processbusinessunit_id,
             ca.invoicebusinessunit_id,
             (select count(*)
              from temporarydeliverystop tds,
                   standarddeliveryline  stdl
              where tds.standarddeliveryline_id = stdl.standarddeliveryline_id
                and stdl.productlistperdistribpoint_id = plpdip.productlistperdistribpoint_id
                and stdl.dateactive <= sysdate
                and stdl.dateinactive >= sysdate
                and ((tds.tempstoprentforweek = 'Y' and tds.temporarystopstartweek <= currentweeknumber and currentweeknumber <= tds.temporarystopendweek) or
                     (tds.tempstoprentforyearweek = 'Y' and (
                           (tds.temporarystopstartyear < currentyearnumber and currentyearnumber < tds.temporarystopendyear) or
                           (tds.temporarystopstartyear = currentyearnumber and currentyearnumber < tds.temporarystopendyear and tds.tempstopstartweekforyear <= currentweeknumber) or
                           (tds.temporarystopstartyear < currentyearnumber and currentyearnumber = tds.temporarystopendyear and currentweeknumber <= tds.tempstopendweekforyear) or
                           (tds.temporarystopstartyear = currentyearnumber and currentyearnumber = tds.temporarystopendyear and tds.tempstopstartweekforyear <= currentweeknumber and currentweeknumber <= tds.tempstopendweekforyear)
                           )
                     ) or
                     (tds.tempstoprentfordate = 'Y' and tds.startdate <= sysdate and sysdate <= tds.enddate)
                 )
              ) tempDelStops
        from productlistperdistribpoint plpdip,
             distributionpoint          dip,
             department                 dep,
             product                    p,
             productgroup               pg,
             customeractivities         ca
       where plpdip.productlistperdistribpoint_id = plpdipID
         and plpdip.distributionpoint_id = dip.distributionpoint_id
         and plpdip.product_id = p.product_id
         and p.productgroup_id = pg.productgroup_id
         and pg.activity_id = ca.activity_id
         and dip.department_id = dep.department_id
         and dep.customer_id = ca.customer_id;
    checkInvoiceBu int;
  begin
    currentyearnumber:=cast(to_char(sysdate, 'IYYY') as numeric);
    currentweeknumber:=getisoweek(sysdate);

    for rs in stmt loop
     BEGIN
     select case ss.value when 'Y' then 1
                                   else -1
            end
      into   checkInvoiceBu
      from   businessunit bu
             join systemsetting ss
                  on bu.plant_id = ss.plant_id
      where  bu.businessunit_id = rs.processbusinessunit_id
             and upper(ss.name) = 'CHECKINVOICEBUSINESSUNIT'
             and ss.groupname = 'Billing';
      EXCEPTION
        WHEN no_data_found THEN
          checkInvoiceBu := -1;
      END;
      if (checkInvoiceBu = 1)then --checkInvoiceBu = true
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.invoicebusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.tempdelstops );
      else
         pack_sqmaxitemincirculation.createOrStoreOnTrigger(rs.processbusinessunit_id,
                                                         rs.processbusinessunit_id,
                                                         rs.costcenter_id,
                                                         rs.product_id,
                                                         rs.sizedefinition_id,
                                                         rs.finishingmethod_id,
                                                         qty,
                                                         returnFromCustomerDate,
                                                         rs.tempdelstops );
      end if;
    end loop;
  end;

end PACK_SQMaxItemInCirculation;
/
