CREATE OR REPLACE PACKAGE pack_sqitemsincirculation IS

  SQ_ITEMS_IN_CIRCULATION    statusquoheader.statusquotype%TYPE := 1;
  AUTOMATICFLAG_SUBSTITUTE   automaticflag.automaticflag_id%TYPE := 12;
  AUTOMATICFLAG_REPLACEMENT  automaticflag.automaticflag_id%TYPE := 4;
  BILL_CIRCULATING_INVENTORY productlistpercustomer.billingmethod%TYPE := 3;
  BILL_RENT_BASED_MINIMUM    productlistpercustomer.billingmethod%TYPE := 2;
  TYPEOFMERCHANDISE_GARMENT  product.typeofmerchandise%TYPE := 2;
  STAY_TYPE_GARMENT          stay.staytype%TYPE := 1;
  STAY_NR_DEPOSITSTOCK       stay.staynr%TYPE := 37;
  underuseMethodSS           numeric := 0;
  INVENTORY_BILLING_TYPE_MAX productlistpercustomer.inventorybillingtype%TYPE := 2;
  bm3MaxInvReduceDepositSS   numeric := 0;

  PROCEDURE createSqItemsInCirculation(sqHeaderID          OUT statusquoheader.statusquoheader_id%TYPE,
                                       processBuID         businessunit.businessunit_id%TYPE,
                                       invoiceBuID         businessunit.businessunit_id%TYPE,
                                       sqDate              DATE,
                                       sqYear              INT,
                                       sqWeek              INT,
                                       sqMonth             INT,
                                       sqSubType           INT,
                                       systemUserID        systemuser.systemuser_id%TYPE,
                                       checkInvoiceBu      INT,
                                       useRentOnStock      INT,
                                       inactivePLPC        INT,
                                       plantID             plant.plant_id%TYPE,
                                       invoiceReplacements INT,
                                       invoiceSubstitutes  INT,
                                       sqOnWearerLevel     INT,
                                       useRentBasedOnReturnWeeks INT,
                                       settlementActive          INT,
                                       chargeReplacedProduct INT,
                                       abslogprocessjob    INT);

  PROCEDURE createSqItemInCircOnWearerInv(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                          processBuID         businessunit.businessunit_id%TYPE,
                                          invoiceBuID         businessunit.businessunit_id%TYPE,
                                          checkInvoiceBu      INT,
                                          inactivePLPC        INT,
                                          sizeStarID          sizedefinition.sizedefinition_id%TYPE,
                                          flagsubstituteid    flag.flag_id%TYPE,
                                          flagreplacementid   flag.flag_id%TYPE,
                                          invoiceReplacements INT,
                                          invoiceSubstitutes  INT,
                                          chargeReplacedProduct INT,
                                          sqOnWearerLevel     INT);
  FUNCTION getOldMaxInventory(wearerinventoryID     wearerinventory.wearerinventory_id%TYPE,
                              oldMaxInv             OUT INT) RETURN BOOLEAN;

  PROCEDURE createOrStoreOnSqForWearerInv(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                          inactivePLPC          INT,
                                          sqOnWearerLevel       INT,
                                          wearerInventoryID     wearerinventory.wearerinventory_id%TYPE,
                                          customerID            customer.customer_id%TYPE,
                                          departmentID          department.department_id%TYPE,
                                          distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                          productID             product.product_id%TYPE,
                                          sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                          finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                          wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                          changesperweek        wearerinventory.changesperweek%TYPE,
                                          maxfreeofcharge       wearerinventory.maxfreeofcharge%TYPE,
                                          emblemtemplateid      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                                          sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                                          flagsubstituteid      flag.flag_id%TYPE,
                                          flagreplacementid     flag.flag_id%TYPE,
                                          invoiceReplacements   INT,
                                          invoiceSubstitutes    INT,
                                          repl_ToBeCharged      wearerinventory.repl_stilltobecharged%TYPE,
                                          sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE);

  FUNCTION getPLPC(customerID            customer.customer_id%TYPE,
                   productID             product.product_id%TYPE,
                   sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                   finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                   sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                   inactivePLPC          INT,
                   sqonwearerlevel       INT,
                   v_billingMethod       OUT productlistpercustomer.billingMethod%TYPE,
                   seasontypeID          OUT seasontype.seasontype_id%TYPE,
                   emblemTemplateID      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                   v_depositStockAllowed OUT productlistpercustomer.depositStockAllowed%TYPE,
                   v_percAfterDeprec     OUT productlistpercustomer.percentageafterdepreciation%TYPE,
                   plpcID                OUT productlistpercustomer.productlistpercustomer_id%TYPE,
                   isActive              OUT BOOLEAN,
                   v_inventoryBillingType OUT productlistpercustomer.inventoryBillingType%TYPE,
                   v_seasonalRent        OUT productlistpercustomer.seasonalrent%TYPE) RETURN BOOLEAN;

  PROCEDURE insertValuesIntoSqItemInCirc(sqItemInCirculationID    IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                         sqHeaderID               statusquoheader.statusquoheader_id%TYPE,
                                         customerID               customer.customer_id%TYPE,
                                         departmentID             department.department_id%TYPE,
                                         distributionPointID      distributionpoint.distributionpoint_id%TYPE,
                                         wearerEmploymentID       weareremployment.weareremployment_id%TYPE,
                                         productID                product.product_id%TYPE,
                                         sizeDefinitionID         sizedefinition.sizedefinition_id%TYPE,
                                         finishingMethodID        finishingmethod.finishingmethod_id%TYPE,
                                         v_maxinv                 wearerinventory.maxinventory%TYPE,
                                         v_qtytotal               sqitemincirculation.totalcircinv%TYPE,
                                         v_changesPerWeek         wearerinventory.changesPerWeek%TYPE,
                                         v_maxFreeOfCharge        wearerinventory.maxFreeOfCharge%TYPE,
                                         v_qtyfree                sqitemincirculation.freeissuedcircinv%TYPE,
                                         v_cogcircinv             sqitemincirculation.cogcircinv%TYPE,
                                         v_qtyfreeseason          sqitemincirculation.freeseasoncircinv%TYPE,
                                         v_beyondlifetypecirc     sqitemincirculation.beyondlifetimecirc%TYPE,
                                         v_qtyrentedperpiece      sqitemincirculation.qtyrentedperpiece%TYPE,
                                         v_qtyrentother           sqitemincirculation.qtyrentedother%TYPE,
                                         v_qtycustrentstop        sqitemincirculation.qtycustomerrentstop%TYPE,
                                         v_qtyrentstop            sqitemincirculation.qtyrentstop%TYPE,
                                         emblemTemplateID         emblemtemplate.emblemtemplate_id%TYPE,
                                         v_qtyrentemblem          sqitemincirculation.qtyrentemblem%TYPE,
                                         v_qtyreplacement         sqitemincirculation.qtyreplacement%TYPE,
                                         v_qtyassigned            sqitemincirculation.qtyassigned%TYPE,
                                         v_qtyrenteddepositstock  sqitemincirculation.qtyrenteddepositstock%TYPE,
                                         v_qtysubstitute          sqitemincirculation.qtysubstitute%TYPE,
                                         v_chargeafterdepreciation VARCHAR2,
                                         v_qtyrentedperpiecepool  sqitemincirculation.qtyrentedperpiecepool%TYPE,
                                         v_qtyrentedreturnrent    sqitemincirculation.qtyrentedreturnrent %TYPE,
                                         v_qtyrentedminrent       sqitemincirculation.qtyrentedminrent%TYPE,
                                         v_qtyfullyamortized      sqitemincirculation.qtyfullyamortized%TYPE);

  --PROCEDURE initializePLPC;

  PROCEDURE updatePLPC(plpcid                productlistpercustomer.productlistpercustomer_id%TYPE,
                       qtytotalcircinv       sqitemincirculation.totalcircinv%TYPE,
                       qtyrentedperpiece     sqitemincirculation.qtyrentedperpiece%TYPE,
                       qtyrentother          sqitemincirculation.qtyrentedother%TYPE,
                       qtyrenteddepositstock sqitemincirculation.qtyrenteddepositstock%TYPE);

  PROCEDURE createSQItemInCircOnUniqueItem(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                           processBUID         businessunit.businessunit_id%TYPE,
                                           invoiceBUID         businessunit.businessunit_id%TYPE,
                                           sizeStarID          sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu      INT,
                                           sqonwearerlevel     INT,
                                           inactiveplpc        INT,
                                           flagSubstituteID    INT,
                                           flagReplacementID   INT,
                                           stayDepositStockID  INT,
                                           invoicereplacements INT,
                                           invoicesubstitutes  INT);

  FUNCTION getStatusID(vstatustype status.statustype %TYPE,
                       vstatuscode status.code%TYPE)
    RETURN status.status_id%TYPE;

  PROCEDURE createSqItemInCircOnUniqueRent(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                           processBuID                  businessunit.businessunit_id%TYPE,
                                           invoiceBuID                  businessunit.businessunit_id%TYPE,
                                           statusidforuniqueiteminstock status.status_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu               INT,
                                           sqOnWearerLevel              INT,
                                           inactivePLPC                 INT);

  FUNCTION getSqItemInCircOnUiRentForStmt(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                          distributionPointID weareremployment.distributionpoint_id%TYPE,
                                          wearerEmploymentID  weareremployment.weareremployment_id%TYPE,
                                          productID           uniqueitem.product_id%TYPE,
                                          sizeDefinitionID    uniqueitem.sizedefinition_id%TYPE,
                                          finishingMethodID   INT)
    RETURN sqitemincirculation.sqitemincirculation_id%TYPE;

  PROCEDURE createOrStoreOnSqForUiRent(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                       customerID            customer.customer_id%TYPE,
                                       departmentID          department.department_id%TYPE,
                                       distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                       productID             product.product_id%TYPE,
                                       sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                       finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                       wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                       sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                       noOfUniqueItems       INT,
                                       inactivePLPC          INT,
                                       sizeStarID            sizedefinition.sizedefinition_id%TYPE);

  FUNCTION getStayID(vStayType stay.staytype %TYPE,
                     vStayNr   stay.staynr%TYPE) RETURN stay.stay_id%TYPE;

  PROCEDURE createOrStoreOnSQforUniqueItem(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                           inactivePLPC          INT,
                                           customerID            customer.customer_id%TYPE,
                                           departmentID          department.department_id%TYPE,
                                           distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                           productID             product.product_id%TYPE,
                                           sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                           finishingMethodID     IN OUT finishingmethod.finishingmethod_id%TYPE,
                                           wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                           emblemTemplateID      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                                           sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                                           invoiceReplacements   INT,
                                           invoiceSubstitutes    INT,
                                           sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                           sqOnWearerLevel       INT,
                                           qtyTotal              IN OUT sqitemincirculation.totalcircinv%TYPE, --countNUI
                                           qtyCOG                IN OUT sqitemincirculation.cogcircinv%TYPE, --countCustomerOwnedUI
                                           qtyAssigned           IN OUT sqitemincirculation.qtyassigned%TYPE, --countQtyAssigned
                                           qtyFree               IN OUT sqitemincirculation.freeissuedcircinv%TYPE, --countExtraGarmentUI
                                           qtyCustRentStop       IN OUT sqitemincirculation.qtycustomerrentstop%TYPE, --countCustomerRentStop
                                           qtyRentStop           IN OUT sqitemincirculation.qtyrentstop%TYPE, --countWearerInvRentStop
                                           qtyFreeSeason         IN OUT sqitemincirculation.freeseasoncircinv%TYPE, --countQtyFreeSeason
                                           qtyReplacement        IN OUT sqitemincirculation.qtyreplacement%TYPE, --countQtyReplacement
                                           qtySubstitute         IN OUT sqitemincirculation.qtysubstitute%TYPE, --countQtySubstitute
                                           qtyDepositStock       IN OUT sqitemincirculation.qtyrenteddepositstock%TYPE, -- countQtyRentDepositStock
                                           qtyBeyondLifeTime     IN OUT sqitemincirculation.beyondlifetimecirc%TYPE, --countFullRentUI
                                           qtyLease              IN OUT sqitemincirculation.qtyrentedother%TYPE, --countQtyLease
                                           qtyFullyAmortized     IN OUT sqitemincirculation.qtyfullyamortized%TYPE, --countQtyFullyAmortized
                                           qtyRentedPerPiecePool IN OUT sqitemincirculation.qtyrentedperpiecepool%TYPE);

  FUNCTION getFinMethodFromWearerInv(distributionPointID distributionpoint.distributionpoint_id%TYPE,
                                     productID           product.product_id%TYPE,
                                     sizeDefinitionID    sizedefinition.sizedefinition_id%TYPE,
                                     wearerEmploymentID  weareremployment.weareremployment_id%TYPE,
                                     sqonwearerlevel     INT)
    RETURN finishingmethod.finishingmethod_id%TYPE;

  FUNCTION getWearerEmploymentLinkID(wearerEmploymentID weareremployment.weareremployment_id%TYPE)
    RETURN weareremployment.weareremployment_id%TYPE;

  PROCEDURE createOrStoreOnSqForPoolItems(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                       customerID            customer.customer_id%TYPE,
                                       departmentID          department.department_id%TYPE,
                                       distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                       wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                       productID             product.product_id%TYPE,
                                       sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                       finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                       sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                       noOfUniqueItems       INT,
                                       qtyRentStop INT);

  PROCEDURE createSqItemInCircForPoolItems(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                           processBuID                  businessunit.businessunit_id%TYPE,
                                           invoiceBuID                  businessunit.businessunit_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu               INT);
  PROCEDURE createSqItemInCircForPoolDummy(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                               processBuID                  businessunit.businessunit_id%TYPE,
                                               invoiceBuID                  businessunit.businessunit_id%TYPE,
                                               sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                               checkInvoiceBu               INT);
  PROCEDURE createSqItemInCircOnReturnRent(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           sqonwearerlevel              INT,
                                           inactivePLPC                 INT);
  PROCEDURE createOrStoreOnSqOnReturnRent(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                         inactivePLPC          INT,
                                         customerID            customer.customer_id%TYPE,
                                         departmentID          department.department_id%TYPE,
                                         distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                         wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                         productID             product.product_id%TYPE,
                                         sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                         sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                         finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                         sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                         noOfUniqueItems       INT,
                                         returnrenttype        INT);


END pack_sqitemsincirculation;
/
CREATE OR REPLACE PACKAGE BODY pack_sqitemsincirculation IS
  sq_systemUserid systemUser.systemUser_id%TYPE;
  sq_date         DATE;
  sq_year         INT;
  sq_week         INT;
  sq_plantId      plant.plant_id%TYPE;

PROCEDURE write_log(fp_err_msg  IN VARCHAR2,
                      fp_priority IN NUMBER DEFAULT 1) 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_err_msg,
                             sq_systemUserid);
  END write_log;

  PROCEDURE createSqItemsInCirculation(sqHeaderID          OUT statusquoheader.statusquoheader_id%TYPE,
                                       processBuID         businessunit.businessunit_id%TYPE,
                                       invoiceBuID         businessunit.businessunit_id%TYPE,
                                       sqDate              DATE,
                                       sqYear              INT,
                                       sqWeek              INT,
                                       sqMonth             INT,
                                       sqSubType           INT,
                                       systemUserID        systemUser.systemUser_id%TYPE,
                                       checkInvoiceBu      INT,
                                       useRentOnStock      INT,
                                       inactivePLPC        INT,
                                       plantID             plant.plant_id%TYPE,
                                       invoiceReplacements INT,
                                       invoiceSubstitutes  INT,
                                       sqOnWearerLevel     INT,
                                       useRentBasedOnReturnWeeks INT,
                                       settlementActive          INT,
                                       chargeReplacedProduct     INT,
                                       abslogprocessjob INT) IS

    sizeStarID                   sizedefinition.sizedefinition_id%TYPE;
    flagSubstituteID             flag.flag_id%TYPE;
    flagReplacementID            flag.flag_id%TYPE;
    flagEvent                    VARCHAR(1000);
    statusIDforUniqueItemInStock status.status_id%TYPE;
    stayDepositStockID           stay.stay_id%TYPE;

  BEGIN

    sq_systemUserid := systemUserID;
    sq_date         := sqDate;
    sq_year         := sqYear;
    sq_week         := sqWeek;
    sq_plantId      := plantID;

    -- size *
    SELECT sizedefinition_id
      INTO sizeStarID
      FROM sizedefinition
     WHERE code = '*';

    --autoflag substitute
    SELECT a.event
      INTO flagEvent
      FROM automaticflag a
     WHERE a.automaticflag_id = automaticflag_substitute;
    SELECT coalesce((SELECT af.flag_id
                      FROM automaticflag af
                     WHERE af.event = flagEvent
                           AND af.plant_id = plantID),
                    (SELECT af.flag_id
                       FROM automaticflag af
                      WHERE af.event = flagEvent
                            AND af.plant_id IS NULL))
      INTO flagSubstituteID
      FROM dual;

    --autoflag replacement
    SELECT a.event
      INTO flagEvent
      FROM automaticflag a
     WHERE a.automaticflag_id = automaticflag_replacement;
    SELECT coalesce((SELECT af.flag_id
                      FROM automaticflag af
                     WHERE af.event = flagEvent
                           AND af.plant_id = plantID),
                    (SELECT af.flag_id
                       FROM automaticflag af
                      WHERE af.event = flagEvent
                            AND af.plant_id IS NULL))
      INTO flagReplacementID
      FROM dual;

    --status_id for UI In stock (used for createSQItemInCircOnUniqueRent )
    statusIDforUniqueItemInStock := getStatusID('UI',
                                                '10');

    stayDepositStockID := getStayID(STAY_TYPE_GARMENT,
                                    STAY_NR_DEPOSITSTOCK);

    --system setting underuseMethodSS
    select ss.value
    into underuseMethodSS
    from systemsetting ss
    where upper(ss.name) like upper( 'UNDERUSEMETHOD')
    and ss.groupname = 'Billing'
    and ss.plant_id = plantID;

    --system setting bm3MaxInvReduceDepositSS
    select ss.value
    into bm3MaxInvReduceDepositSS
    from systemsetting ss
    where upper(ss.name) like upper( 'BM3MAXINVREDUCEDEPOSIT')
    and ss.groupname = 'Billing'
    and ss.plant_id = plantID;

    -- StatusQuoHeader

BEGIN
     delete statusquoheader sh
     where sh.year = sqYear
     and sh.week = sqWeek
     and sh.processbusinessunit_id = processBuID
     and sh.invoicebusinessunit_id = invoiceBuID
     and sh.STATUSQUOTYPE = 1
     and sh.totalquantity = 0;
    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,
       processBuID,
       invoiceBuID,
       sqDate,
       SQ_ITEMS_IN_CIRCULATION,
       0,
       sqYear,
       sqWeek,
       sqMonth,
       sqSubType,
       abslogprocessjob,
       sq_systemUserid,
       SYSDATE)
    RETURNING statusquoheader_id INTO sqHeaderID;
EXCEPTION
WHEN OTHERS THEN
write_log ('Creating a new statusquoheader record for processed BU' || processBuID  || ' , invoiced BU' || invoiceBuID ||' , statusquodate ' || sqDate || ' and system user '|| sq_systemUserid || '  has failed! Please check your data! ', 3);
commit;
raise DUP_VAL_ON_INDEX;
END;

   --initialize plpc with 0
   --initializePLPC(); --initialization is done in Java Code

    -- createSQItemInCirc based on Wearer Inventory
    createSqItemInCircOnWearerInv(sqHeaderID,
                                  processBuID,
                                  invoiceBuID,
                                  checkInvoiceBu,
                                  inactivePLPC,
                                  sizeStarID,
                                  flagSubstituteID,
                                  flagReplacementID,
                                  invoiceReplacements,
                                  invoiceSubstitutes,
                                  chargeReplacedProduct,
                                  sqOnWearerLevel);

    -- createSQItemInCirc based on Unique Items
    createSQItemInCircOnUniqueItem(sqHeaderID,
                                   processBUID,
                                   invoiceBUID,
                                   sizeStarID,
                                   checkInvoiceBu,
                                   sqOnWearerLevel,
                                   inactiveplpc,
                                   flagSubstituteID,
                                   flagReplacementID,
                                   stayDepositStockID,
                                   invoicereplacements,
                                   invoicesubstitutes);

    -- createSQItemInCirc based on Unique Item (for Pool items)
  createSqItemInCircForPoolItems(sqHeaderID,
                                     processBuID,
                                     invoiceBuID,
                                     sizeStarID,
                                     checkInvoiceBu);
    -- createSQItemInCirc based on Unique Item (for Non Pool items - based on Wearer Dummy for Pool)
    createSqItemInCircForPoolDummy(sqHeaderID,
                                     processBuID,
                                     invoiceBuID,
                                     sizeStarID,
                                     checkInvoiceBu);

    -- createSQItemInCirc based on Unique Item for Rent
    IF (useRentOnStock = 1) THEN
      --useRentOnStock = true
      createSqItemInCircOnUniqueRent(sqHeaderID,
                                     processBuID,
                                     invoiceBuID,
                                     statusIDforUniqueItemInStock,
                                     sizeStarID,
                                     checkInvoiceBu,
                                     sqOnWearerLevel,
                                     inactivePLPC);
    END IF;

    -- createSQItemInCirc based on Unique Item Return Rent
    IF (useRentBasedOnReturnWeeks = 1 AND settlementActive = 0) THEN
        createSqItemInCircOnReturnRent(sqHeaderID,
                                     sizeStarID,
                                     sqOnWearerLevel,
                                     inactiveplpc);

    END IF;

    -- update StatusQuoHeader.totalquantity
BEGIN
    UPDATE statusquoheader
       SET totalquantity = (SELECT nvl(SUM(sqitemincirculation.totalcircinv),
                                       0)
                              FROM sqitemincirculation
                             WHERE sqitemincirculation.statusquoheader_id =
                                   statusquoheader.statusquoheader_id)
     WHERE statusquoheader_id = sqHeaderID;

     COMMIT;

EXCEPTION
WHEN OTHERS THEN
write_log(' Totalquantity in statusquoheader table for statusquoheader id '|| sqHeaderID|| '  was not updated correctly. Please check! ' );
commit;
END;

  END createSqItemsInCirculation;

 FUNCTION getPLPC(customerID            customer.customer_id%TYPE,
                   productID             product.product_id%TYPE,
                   sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                   finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                   sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                   inactivePLPC          INT,
                   sqOnWearerLevel       INT,
                   v_billingMethod       OUT productlistpercustomer.billingMethod%TYPE,
                   seasontypeID          OUT seasontype.seasontype_id%TYPE,
                   emblemTemplateID      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                   v_depositStockAllowed OUT productlistpercustomer.depositStockAllowed%TYPE,
                   v_percAfterDeprec     OUT productlistpercustomer.percentageafterdepreciation%TYPE,
                   plpcID                OUT productlistpercustomer.productlistpercustomer_id%TYPE,
                   isActive              OUT BOOLEAN,
                   v_inventoryBillingType OUT productlistpercustomer.inventoryBillingType%TYPE,
                   v_seasonalRent         OUT productlistpercustomer.seasonalrent%TYPE) RETURN BOOLEAN IS
    v_dateInactive DATE;
    customerNr     customer.customernumber%TYPE;
    productCode    product.code%TYPE;
    sizeCode       sizedefinition.code%TYPE;
    finmethCode    finishingmethod.code%TYPE;
    v_emblemTemplateID emblemtemplate.emblemtemplate_id%TYPE:= -1;

  BEGIN
    BEGIN
      IF inactivePLPC <> 2 THEN
        SELECT x1.billingMethod,
               nvl(x1.emblemtemplate_id, -1),
               x1.depositStockAllowed,
               x1.percentageafterdepreciation,
               x1.dateinactive,
               x1.productlistpercustomer_id,
               x1.inventoryBillingType,
               x1.seasonalrent
         INTO  v_billingMethod,
               v_emblemTemplateID,
               v_depositStockAllowed,
               v_percAfterDeprec,
               v_dateInactive,
               plpcID,
               v_inventoryBillingType,
               v_seasonalRent
          FROM productlistpercustomer x1
         WHERE x1.productlistpercustomer_id =(
             select coalesce((select x.productlistpercustomer_id
                      FROM productlistpercustomer x
                         WHERE x.customer_id = customerID
                           AND x.product_id = productID
                           AND x.sizedefinition_id = sizeDefinitionID
                           AND x.finishingmethod_id = finishingMethodID
                           AND x.dateactive <= sq_date
                           AND x.dateinactive =  (SELECT MAX(productlistpercustomer.dateinactive)
                                                  FROM productlistpercustomer
                                                 WHERE customer_id = customerID
                                                   AND product_id = productID
                                                   AND sizedefinition_id = sizeDefinitionID
                                                   AND finishingmethod_id = finishingMethodID
                                                   AND productlistpercustomer.dateactive <= sq_date))
                  ,
                      (select x.productlistpercustomer_id
                          FROM productlistpercustomer x, sizedefinition sz
                             WHERE x.customer_id = customerID
                               AND x.product_id = productID
                               and x.sizedefinition_id = sz.sizedefinition_id
                               AND x.sizedefinition_id = sizeStarID
                               AND x.finishingmethod_id = finishingMethodID
                               AND x.dateactive <= sq_date
                               AND x.dateinactive =  (SELECT MAX(productlistpercustomer.dateinactive)
                                                      FROM productlistpercustomer
                                                     WHERE customer_id = customerID
                                                       AND product_id = productID
                                                       AND sizedefinition_id = sizeStarID
                                                       AND finishingmethod_id = finishingMethodID
                                                       AND productlistpercustomer.dateactive <= sq_date))

                    ) from dual);
      ELSE
        SELECT x1.billingMethod,
               nvl(x1.emblemtemplate_id, -1),
               x1.depositStockAllowed,
               x1.percentageafterdepreciation,
               x1.dateinactive,
               x1.productlistpercustomer_id,
               x1.inventoryBillingType,
               x1.seasonalrent
          INTO v_billingMethod,
               v_emblemTemplateID,
               v_depositStockAllowed,
               v_percAfterDeprec,
               v_dateInactive,
               plpcID,
               v_inventoryBillingType,
               v_seasonalRent
          FROM productlistpercustomer x1/*, sizedefinition sz
             WHERE x.customer_id = customerID
               AND x.product_id = productID
               and x.sizedefinition_id = sz.sizedefinition_id
               AND (x.sizedefinition_id = sizeDefinitionID or
                   x.sizedefinition_id = sizeStarID)
               AND x.finishingmethod_id = finishingMethodID
               AND x.dateactive <= sq_date
               AND x.dateinactive >= sq_date
               order by sz.code desc*/
                WHERE x1.productlistpercustomer_id =(
             select coalesce((select x.productlistpercustomer_id
                      FROM productlistpercustomer x
                         WHERE x.customer_id = customerID
                           AND x.product_id = productID
                           AND x.sizedefinition_id = sizeDefinitionID
                           AND x.finishingmethod_id = finishingMethodID
                           AND x.dateactive <= sq_date
                           AND x.dateinactive =  (SELECT MAX(productlistpercustomer.dateinactive)
                                                  FROM productlistpercustomer
                                                 WHERE customer_id = customerID
                                                   AND product_id = productID
                                                   AND sizedefinition_id = sizeDefinitionID
                                                   AND finishingmethod_id = finishingMethodID
                                                   AND productlistpercustomer.dateactive <= sq_date))
                  ,
                      (select x.productlistpercustomer_id
                          FROM productlistpercustomer x
                             WHERE x.customer_id = customerID
                               AND x.product_id = productID
                               AND x.sizedefinition_id = sizeStarID
                               AND x.finishingmethod_id = finishingMethodID
                               AND x.dateactive <= sq_date
                               AND x.dateinactive =  (SELECT MAX(productlistpercustomer.dateinactive)
                                                      FROM productlistpercustomer
                                                     WHERE customer_id = customerID
                                                       AND product_id = productID
                                                       AND sizedefinition_id = sizeStarID
                                                       AND finishingmethod_id = finishingMethodID
                                                       AND productlistpercustomer.dateactive <= sq_date)))
                  from dual);
      END IF;

      IF (sqOnWearerLevel = 0 and v_emblemTemplateID > 0) THEN
        emblemTemplateID := v_emblemTemplateID;
      END IF;
      IF (v_dateInactive > sq_date) THEN
        isActive := TRUE;
      ELSE
        isActive := FALSE;
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        BEGIN
         -- out parameters, set up with default values
          v_billingMethod       := -1;
          seasontypeID          := -1;
          v_depositStockAllowed := 'N';
          v_percAfterDeprec     := 0;
          plpcID                := -1;
          isActive              := FALSE;
          v_inventoryBillingType  := -1;
          emblemTemplateID      := -1;
          v_seasonalRent        := 0;
          SELECT cu.customernumber
            INTO customerNr
            FROM customer cu
           WHERE cu.customer_id = customerID;
          SELECT pr.code
            INTO productCode
            FROM product pr
           WHERE pr.product_id = productID;
          SELECT sz.code
            INTO sizeCode
            FROM sizedefinition sz
           WHERE sz.sizedefinition_id = sizeDefinitionID;
          SELECT fm.code
            INTO finmethCode
            FROM finishingmethod fm
           WHERE fm.finishingmethod_id = finishingMethodID;
          /*write_log('No active PLPC was found for customer ' || customerNr ||
                    ' productCode ' || productCode || ' sizeCode ' ||
                    sizeCode || ' finmeth ' || finmethCode || ' in date ' ||
                    sq_date || ' . Inactive date for PLPC is ' ||
                    v_dateInactive,
                    3);*/
          RETURN FALSE;
        END;
    END;
    BEGIN
      SELECT a.seasontype_id
        INTO seasontypeID
        FROM seasontype a, productlistpercustomer plpc
       WHERE a.seasontype_id = plpc.seasontype_id
             AND plpc.productlistpercustomer_id = plpcID
             AND a.active = 'Y'
            /* AND NOT EXISTS (SELECT b.seasontype_id
                FROM season b, seasonweek c
               WHERE a.seasontype_id = b.seasontype_id
                     AND b.season_id = c.season_id
                     AND c.weeknumber = sq_week)*/;
    EXCEPTION
      WHEN no_data_found THEN
        seasontypeID := -1;
    END;
    RETURN TRUE;
  END getPLPC;

  /*
   PROCEDURE initializePLPC IS
    CURSOR stmt IS
      SELECT plpc.productlistpercustomer_id
        FROM product                p,
             productgroup           pg,
             customeractivities     ca,
             businessunit           bu,
             productlistpercustomer plpc
       WHERE p.product_id = plpc.product_id
             AND pg.productgroup_id = p.productgroup_id
             AND pg.activity_id = ca.activity_id
             AND ca.customer_id = plpc.customer_id
             AND ca.processbusinessunit_id = bu.businessunit_id
             AND p.typeofmerchandise = TYPEOFMERCHANDISE_GARMENT
             AND bu.plant_id = sq_plantID;
  BEGIN
    FOR rs IN stmt LOOP
      UPDATE productlistpercustomer plpc
         SET plpc.totalincirculation        = 0,
             plpc.totalincirculationcharged = 0,
             plpc.timestamp = sysdate,
             plpc.systemuser_id = sq_systemUserid
       WHERE plpc.productlistpercustomer_id = rs.productlistpercustomer_id;
    END LOOP;
  END initializePLPC;
*/

  PROCEDURE updatePLPC(plpcID                productlistpercustomer.productlistpercustomer_id%TYPE,
                       qtytotalcircinv       sqitemincirculation.totalcircinv%TYPE,
                       qtyrentedperpiece     sqitemincirculation.qtyrentedperpiece%TYPE,
                       qtyrentother          sqitemincirculation.qtyrentedother%TYPE,
                       qtyrenteddepositstock sqitemincirculation.qtyrenteddepositstock%TYPE) IS
  BEGIN

BEGIN
    UPDATE productlistpercustomer plpc
       SET plpc.totalincirculation        = nvl(plpc.totalincirculation,
                                                0) +
                                            qtytotalcircinv,
           plpc.totalincirculationcharged = nvl(plpc.totalincirculationcharged,
                                                0) +
                                            qtyrentedperpiece +
                                            qtyrentother +
                                            qtyrenteddepositstock
           --plpc.timestamp = sysdate,
           --plpc.systemuser_id = sq_systemUserid
     WHERE plpc.productlistpercustomer_id = plpcID;

    COMMIT;

EXCEPTION
WHEN OTHERS THEN
write_log('Please check plpcID '|| plpcID || '  An error has occured and the information was not updated correctly! ');
commit;
END;

  END updatePLPC;

  -- createSQItemInCirc based on Unique Items
  PROCEDURE createSqItemInCircOnWearerInv(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                          processBuID         businessunit.businessunit_id%TYPE,
                                          invoiceBuID         businessunit.businessunit_id%TYPE,
                                          checkInvoiceBu      INT,
                                          inactivePLPC        INT,
                                          sizeStarID          sizedefinition.sizedefinition_id%TYPE,
                                          flagSubstituteID    flag.flag_id%TYPE,
                                          flagReplacementID   flag.flag_id%TYPE,
                                          invoiceReplacements INT,
                                          invoiceSubstitutes  INT,
                                          chargeReplacedProduct INT,
                                          sqOnWearerLevel     INT) IS

    prevcustomerID          customer.customer_id%TYPE := -1;
    prevdepartmentID        department.department_id%TYPE := -1;
    prevdistributionPointID distributionpoint.distributionpoint_id%TYPE := -1;
    prevproductID           product.product_id%TYPE := -1;
    prevsizeDefinitionID    sizedefinition.sizedefinition_id%TYPE := -1;
    prevfinishingMethodID   finishingmethod.finishingmethod_id%TYPE := -1;
    sqItemInCirculationID   sqitemincirculation.sqitemincirculation_id%TYPE := -1;

    CURSOR stmtAcheckbu IS
      SELECT a.wearerinventory_id,
             d.department_id,
             c.distributionpoint_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                nvl(b.weareremployment_id,
                    -1)
               ELSE
                -1
             END weareremploymentID,
             a.product_id,
             a.sizedefinition_id,
             a.finishingmethod_id,
             d.customer_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                nvl(a.emblemtemplate_id,
                    -1)
               ELSE
                -1
             END emblemtemplateID,
             a.changesPerWeek,
             a.maxFreeOfCharge,
             CASE chargeReplacedProduct
               WHEN 1 THEN
                 nvl(a.repl_stilltobecharged, 'Y')
               ELSE
                 'N'
             END repl_stilltobecharged
        FROM wearerinventory    a,
             weareremployment   b,
             distributionpoint  c,
             department         d,
             product            e,
             productgroup       f,
             customeractivities g
       WHERE a.weareremployment_id = b.weareremployment_id
             AND b.distributionpoint_id = c.distributionpoint_id
             AND c.department_id = d.department_id
             AND a.product_id = e.product_id
             AND e.productgroup_id = f.productgroup_id
             AND abs(f.activity_id) = abs(g.activity_id)
             AND d.customer_id = g.customer_id
             AND g.processbusinessunit_id = processBuID
             AND
             (checkinvoicebu = 0 OR g.invoicebusinessunit_id = invoiceBuID)
       ORDER BY d.customer_id,
                c.distributionpoint_id,
                a.product_id,
                a.sizedefinition_id,
                a.finishingmethod_id,
                d.department_id;

  BEGIN
    --  IF (checkInvoiceBu = 1) THEN
    FOR rs IN stmtAcheckbu LOOP
      IF (sqOnWearerLevel = 1 OR
         NOT (prevcustomerID = rs.customer_id AND
          prevdepartmentID = rs.department_id AND
          prevdistributionPointID = rs.distributionpoint_id AND
          prevproductID = rs.product_id AND
          prevfinishingMethodID = rs.finishingmethod_id AND
          prevsizeDefinitionID = rs.sizedefinition_id)) THEN
        sqItemInCirculationID := -1;
      END IF;
      createOrStoreOnSqForWearerInv(sqHeaderID,
                                    inactivePLPC,
                                    sqOnWearerLevel,
                                    rs.wearerinventory_id,
                                    rs.customer_id,
                                    rs.department_id,
                                    rs.distributionpoint_id,
                                    rs.product_id,
                                    rs.sizedefinition_id,
                                    rs.finishingmethod_id,
                                    rs.weareremploymentID,
                                    rs.changesPerWeek,
                                    rs.maxFreeOfCharge,
                                    rs.emblemtemplateID,
                                    sizeStarID,
                                    flagSubstituteID,
                                    flagReplacementID,
                                    invoiceReplacements,
                                    invoiceSubstitutes,
                                    rs.repl_stilltobecharged,
                                    sqItemInCirculationID);
    END LOOP;
    /* ELSE
      FOR rs IN stmtA LOOP
        IF (sqOnWearerLevel = 1 OR
           NOT (prevcustomerID = rs.customer_id AND
            prevdepartmentID = rs.department_id AND
            prevdistributionPointID = rs.distributionpoint_id AND
            prevproductID = rs.product_id AND
            prevfinishingMethodID = rs.finishingmethod_id AND
            prevsizeDefinitionID = rs.sizedefinition_id)) THEN
          sqItemInCirculationID := -1;
        END IF;
        createOrStoreOnSqForWearerInv(sqHeaderID,
                                      inactivePLPC,
                                      rs.wearerinventory_id,
                                      rs.customer_id,
                                      rs.department_id,
                                      rs.distributionpoint_id,
                                      rs.product_id,
                                      rs.sizedefinition_id,
                                      rs.finishingmethod_id,
                                      rs.weareremploymentID,
                                      rs.changesPerWeek,
                                      rs.maxFreeOfCharge,
                                      rs.emblemtemplateID,
                                      sizeStarID,
                                      flagSubstituteID,
                                      flagReplacementID,
                                      invoiceReplacements,
                                      invoiceSubstitutes,
                                      sqItemInCirculationID);
      END LOOP;
    END IF; */

  END createSqItemInCircOnWearerInv;

  PROCEDURE createOrStoreOnSqForWearerInv(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                          inactivePLPC          INT,
                                          sqOnWearerLevel       INT,
                                          wearerinventoryID     wearerinventory.wearerinventory_id%TYPE,
                                          customerID            customer.customer_id%TYPE,
                                          departmentID          department.department_id%TYPE,
                                          distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                          productID             product.product_id%TYPE,
                                          sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                          finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                          wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                          changesPerWeek        wearerinventory.changesPerWeek%TYPE,
                                          maxFreeOfCharge       wearerinventory.maxFreeOfCharge%TYPE,
                                          emblemTemplateID      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                                          sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                                          flagSubstituteID      flag.flag_id%TYPE,
                                          flagReplacementID     flag.flag_id%TYPE,
                                          invoiceReplacements   INT,
                                          invoiceSubstitutes    INT,
                                          repl_ToBeCharged      wearerinventory.repl_stilltobecharged%TYPE,
                                          sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE) IS

    maxinv                 wearerinventory.maxinventory%TYPE := 0;
    qtytotal               sqitemincirculation.totalcircinv%TYPE := 0; --countNUI
    qtytotalcircinv        sqitemincirculation.totalcircinv%TYPE := 0;
    qtyassigned            sqitemincirculation.qtyassigned%TYPE := 0; --countQtyAssigned
    qtyfree                sqitemincirculation.freeissuedcircinv%TYPE := 0; --countFreeNUI
    qtycustrentstop        sqitemincirculation.qtycustomerrentstop%TYPE := 0; --countCustomerRentStop
    qtyrentstop            sqitemincirculation.qtyrentstop%TYPE := 0; --countWearerInvRentStop
    qtyfreeseason          sqitemincirculation.freeseasoncircinv%TYPE := 0; --countQtyFreeSeason
    qtyreplacement         sqitemincirculation.qtyreplacement%TYPE := 0; --countQtyReplacement
    qtysubstitute          sqitemincirculation.qtysubstitute%TYPE := 0; --countQtySubstitute
    foundplpc              BOOLEAN;
    billingMethod          productlistpercustomer.billingMethod%TYPE := -1;
    seasontypeid           seasontype.seasontype_id%TYPE := -1;
    depositStockAllowed    productlistpercustomer.depositStockAllowed%TYPE;
    percafterdeprec        productlistpercustomer.percentageafterdepreciation%TYPE := 0;
    plpcID                 productlistpercustomer.productlistpercustomer_id%TYPE := -1;
    isActive               BOOLEAN := FALSE;
    qtyrentedperpiece      sqitemincirculation.qtyrentedperpiece%TYPE := 0;
    qtyrentother           sqitemincirculation.qtyrentedother%TYPE := 0;
    qtyrentemblem          sqitemincirculation.qtyrentemblem%TYPE := 0;
    chargeafterdepreciation VARCHAR2(1);
    typeOfMerchandise      product.typeofmerchandise%TYPE;
    qtyReplacement_used    sqitemincirculation.qtyreplacement%TYPE := 0; --countQtyReplacement
    qtySubstitute_used     sqitemincirculation.qtysubstitute%TYPE := 0; --countQtySubstitute
    inventoryBillingType   productlistpercustomer.inventoryBillingType%TYPE := -1;
    seasonalRent           productlistpercustomer.seasonalrent%TYPE := 0;
    oldmaxinv              wearerinventory.maxinventory%TYPE := 0;
    foundOldmaxinv         BOOLEAN;
  BEGIN

    --calculate fields
    BEGIN
      SELECT coalesce((SELECT wi.maxinventory
                        FROM wearerinventory  wi,
                             weareremployment we,
                             wearer           w
                       WHERE wi.wearerinventory_id = wearerinventoryid
                             AND wi.weareremployment_id =
                             we.weareremployment_id
                             AND we.wearer_id = w.wearer_id
                             AND sq_date BETWEEN wi.dateactive AND
                             wi.dateinactive
                             AND sq_date BETWEEN w.dateactive AND
                             w.dateinactive
                             AND wi.circinventory > 0
                             AND ((sq_date < wi.temporaryrentstopstartdate OR
                             sq_date > 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')))),
                      0)
        INTO maxinv
        FROM dual;
    EXCEPTION
      WHEN no_data_found THEN
        maxinv := 0;
    END;

    BEGIN
      SELECT COUNT(x.wearerinventory_id)
        INTO qtytotal
        FROM nonuniqueitem x
       WHERE x.wearerinventory_id = wearerinventoryid;
      qtytotalcircinv := qtytotal;
    EXCEPTION
      WHEN no_data_found THEN
        qtytotal        := 0;
        qtytotalcircinv := 0;
    END;

    BEGIN
      SELECT COUNT(x.wearerinventory_id)
        INTO qtyassigned
        FROM nonuniqueitem x
       WHERE x.wearerinventory_id = wearerinventoryid
             AND x.startrentdate > sq_date;
    EXCEPTION
      WHEN no_data_found THEN
        qtyassigned := 0;
    END;

    BEGIN
      SELECT COUNT(x.nonuniqueitem_id)
        INTO qtyfree
        FROM wearerinventory wi, nonuniqueitem x
       WHERE wi.wearerinventory_id = wearerinventoryid
             AND x.startrentdate <= sq_date
             AND wi.wearerinventory_id = x.wearerinventory_id
             AND x.freeitem = 'Y';
    EXCEPTION
      WHEN no_data_found THEN
        qtyfree := 0;
    END;

    BEGIN
      SELECT COUNT(t.temporarygarmentrentstop_id)
        INTO qtycustrentstop
        FROM temporarygarmentrentstop t
       WHERE t.customer_id = customerID
             AND f_isinyearweeklifespan(startyear,
                                        startweek,
                                        sq_year,
                                        sq_week,
                                        endyear,
                                        endweek) = 1;
    EXCEPTION
      WHEN no_data_found THEN
        qtycustrentstop := 0;
    END;

    BEGIN
      SELECT COUNT(x.nonuniqueitem_id)
        INTO qtyrentstop
        FROM wearerinventory wi, nonuniqueitem x
       WHERE wi.wearerinventory_id = wearerinventoryid
             AND wi.wearerinventory_id = x.wearerinventory_id
             AND x.startrentdate <= sq_date
             AND NOT (x.freeitem = 'Y')
             AND sq_date BETWEEN wi.temporaryrentstopstartdate AND
             wi.temporaryrentstopenddate;
    EXCEPTION
      WHEN no_data_found THEN
        qtyrentstop := 0;
    END;

    BEGIN
      SELECT COUNT(x.nonuniqueitem_id)
        INTO qtyfreeseason
        FROM wearerinventory wi, nonuniqueitem x
       WHERE wi.wearerinventory_id = wearerinventoryid
             AND wi.wearerinventory_id = x.wearerinventory_id
             AND x.startrentdate <= sq_date
             AND NOT (x.freeitem = 'Y')
             AND ((sq_date < wi.temporaryrentstopstartdate OR
             sq_date > 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 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 = sq_week);
    EXCEPTION
      WHEN no_data_found THEN
        qtyfreeseason := 0;
    END;

    BEGIN
      SELECT COUNT(x.nonuniqueitem_id)
        INTO qtyreplacement
        FROM wearerinventory wi, nonuniqueitem x
       WHERE wi.wearerinventory_id = wearerinventoryid
             AND wi.wearerinventory_id = x.wearerinventory_id
             AND x.startrentdate <= sq_date
             AND NOT (x.freeitem = 'Y')
             AND ((sq_date < wi.temporaryrentstopstartdate OR
             sq_date > 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 (nvl(wi.season_id,
                      0) = 0 OR EXISTS
              (SELECT sw.season_id
                     FROM seasonweek sw
                    WHERE sw.season_id = wi.season_id
                          AND sw.weeknumber = sq_week))
             AND x.flag_id = flagReplacementID --flagReplacement
             AND x.flagstartdate <= sq_date;
    EXCEPTION
      WHEN no_data_found THEN
        qtyreplacement := 0;
    END;

    BEGIN
      SELECT COUNT(x.nonuniqueitem_id)
        INTO qtysubstitute
        FROM wearerinventory wi, nonuniqueitem x
       WHERE wi.wearerinventory_id = wearerinventoryid
             AND wi.wearerinventory_id = x.wearerinventory_id
             AND x.startrentdate <= sq_date
             AND NOT (x.freeitem = 'Y')
             AND ((sq_date < wi.temporaryrentstopstartdate OR
             sq_date > 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 (nvl(wi.season_id,
                      0) = 0 OR EXISTS
              (SELECT sw.season_id
                     FROM seasonweek sw
                    WHERE sw.season_id = wi.season_id
                          AND sw.weeknumber = sq_week))
             AND x.flag_id = flagSubstituteID --falgSubstitute
             AND x.flagstartdate <= sq_date;
    EXCEPTION
      WHEN no_data_found THEN
        qtysubstitute := 0;
    END;

    --find plpc for statusQuo.Date
BEGIN
    foundplpc := getPLPC(customerID,
                         productID,
                         sizeDefinitionID,
                         finishingMethodID,
                         sizeStarID,
                         inactivePLPC,
                         sqOnWearerLevel,
                         billingMethod,
                         seasontypeid,
                         emblemTemplateID,
                         depositStockAllowed,
                         percAfterDeprec,
                         plpcID,
                         isActive,
                         inventoryBillingType, seasonalRent);
EXCEPTION
WHEN OTHERS THEN
/*write_log('No active PLPC was found for customerID ' || customerID ||
                    ' productID ' || productID || ' sizeDefinitionID ' ||
                    sizeDefinitionID || ' finishingMethodID ' || finishingMethodID || ' in date ' ||
                    sq_date,
                    3);*/
                    commit;
END;

    --checks
    IF (foundplpc) THEN
      IF (isActive = FALSE AND inactivePLPC = 2) THEN
        qtytotalcircinv := 0;
      END IF;

      IF (qtycustrentstop > 0) THEN
        qtycustrentstop   := qtytotal;
        qtyrentstop       := 0;
        qtyfreeseason     := 0;
        qtyreplacement    := 0;
        qtysubstitute     := 0;
        qtyrentedperpiece := 0;
        qtyrentother      := 0;
        qtyrentemblem     := 0;
        maxinv            := 0;
      ELSE
        qtyrentother := 0;
        IF ((billingMethod = bill_circulating_inventory OR
            (billingMethod = BILL_RENT_BASED_MINIMUM AND underuseMethodSS = 4))
           AND (isActive OR inactivePLPC = 0)) THEN
          IF (billingMethod = bill_circulating_inventory and inventoryBillingType = 2 and repl_ToBeCharged = 'Y') THEN
            foundOldmaxinv := getOldMaxInventory(wearerinventoryid, oldmaxinv);
            IF (foundOldmaxinv and oldmaxinv > 0) THEN
               maxinv := oldmaxinv;
            END IF;
          END IF;
          IF (invoiceReplacements = 1) THEN
            qtyReplacement_used := 0;
          END IF;
          IF (invoiceSubstitutes = 1) THEN
            qtySubstitute_used := 0;
          END IF;

          IF (seasontypeid <> -1 and seasonalRent = 2) THEN
            qtyfreeseason := 0;
          END IF;
          qtyrentedperpiece := qtytotal - qtyassigned - qtyfree -
                               qtyrentstop - qtyfreeseason -
                               qtyReplacement_used - qtySubstitute_used;
          IF (emblemTemplateID <>-1) THEN
            qtyrentemblem := qtytotal - qtyassigned - qtyfree - qtyrentstop -
                             qtyfreeseason;
          END IF;
        ELSE
          qtyreplacement    := 0;
          qtysubstitute     := 0;
          qtyrentedperpiece := 0;
          IF (isActive OR inactivePLPC = 1) THEN
            IF (seasontypeid <> -1 and seasonalRent = 2) THEN
               qtyfreeseason := 0;
            END IF;
            qtyrentother := qtytotal - qtyassigned - qtyfree - qtyrentstop -
                            qtyfreeseason;
            IF (emblemTemplateID <>-1) THEN
              qtyrentemblem := qtytotal - qtyassigned - qtyfree -
                               qtyrentstop - qtyfreeseason;
            END IF;
          END IF;
        END IF;
        IF (qtyrentstop > 0) THEN
          maxinv            := 0;
        END IF;
      END IF;
      chargeafterdepreciation := 'N';
      IF (billingMethod = bill_circulating_inventory) AND
         percafterdeprec > 0 THEN
        chargeafterdepreciation := 'Y';
      END IF;
      IF (qtyrentedperpiece < 0) THEN
        qtyrentedperpiece := 0;
      END IF;
      IF (qtyrentother < 0) THEN
        qtyrentother := 0;
      END IF;
      IF (emblemTemplateID = -1) THEN
         emblemTemplateID := null;
      END IF;
      --insertion/update
      insertValuesIntoSqItemInCirc(sqItemInCirculationID,
                                   sqHeaderID,
                                   customerID,
                                   departmentID,
                                   distributionPointID,
                                   wearerEmploymentID,
                                   productID,
                                   sizeDefinitionID,
                                   finishingMethodID,
                                   maxinv,
                                   qtytotalcircinv, --countNUI
                                   changesPerWeek,
                                   maxFreeOfCharge,
                                   qtyfree,
                                   0, --COGCIRCINV
                                   qtyfreeseason,
                                   0, --BEYONDLIFETIMECIRC
                                   qtyrentedperpiece,
                                   qtyrentother,
                                   qtycustrentstop,
                                   qtyrentstop,
                                   emblemTemplateID,
                                   qtyrentemblem,
                                   qtyreplacement,
                                   qtyassigned,
                                   0, --QTYRENTEDDEPOSITSTOCK
                                   qtysubstitute, --QTYSUBSTITUTE
                                   chargeafterdepreciation,
                                   0, --QtyRentedPerPiecePool
                                   0,
                                   0, 0);

      --update plpc
      SELECT pr.typeofmerchandise
        INTO typeOfMerchandise
        FROM product pr
       WHERE pr.product_id = productID;

     IF (typeofMerchandise = TYPEOFMERCHANDISE_Garment) THEN
        updatePLPC(plpcID,
                   qtytotalcircinv,
                   qtyrentedperpiece,
                   qtyrentother,
                   0);
      END IF;

    END IF;

  END createOrStoreOnSqForWearerInv;

  FUNCTION getOldMaxInventory(wearerinventoryID     wearerinventory.wearerinventory_id%TYPE,
                              oldMaxInv             OUT INT) RETURN BOOLEAN IS

  --Check if there are any items in circulation for replacement WO. If NOT, old max inventory should be taken into account
  --Update WearerInventory since it should not be invoiced anymore
    noOfItemsInCirc INT;

    BEGIN
      noOfItemsInCirc := 0;
      oldMaxInv :=0;
      BEGIN
        select count(1) lvresult
         into noOfItemsInCirc
         from wearerinventory     wi,
               workorderline       wol,
               uniqueitemfixedinfo uinp,
               uniqueitem          ui,
               status              s
         where wi.wearerinventory_id = wearerinventoryID
           and wol.oldwearerinventory_id = wearerinventoryID
           and ((wol.product_link_id <> wi.product_id)
                or (wol.product_link_id = wi.product_id
                    and wol.sizedefinition_link_id <> wi.sizedefinition_id))
           and uinp.issuedbyworkorder_id = wol.workorder_id
           and ui.uniqueitem_id = uinp.uniqueitem_id
           and ui.product_id = wol.product_link_id
           and ui.sizedefinition_id = wol.sizedefinition_link_id
           and s.status_id = ui.status_id
           and s.code = '20';
      END;
      write_log ('noOfItemsInCirc ' || noOfItemsInCirc  || ' , wearerinventoryID ' || wearerinventoryID , 3);

      IF (noOfItemsInCirc > 0) THEN
        BEGIN
          UPDATE wearerinventory wi
             SET wi.repl_stilltobecharged = 'N'
          WHERE wi.wearerinventory_id = wearerinventoryID;
          COMMIT;

          EXCEPTION
          WHEN OTHERS THEN
          write_log('Please check wearerinventoryID '|| wearerinventoryID || '  An error has occured and the information was not updated correctly! ');
          commit;
        END;
        RETURN FALSE;
      ELSE
        BEGIN
          select nvl(wi.old_maxinventory, 0)
          into oldMaxInv
          from wearerinventory     wi
          where wi.wearerinventory_id = wearerinventoryID;
        END;
      END IF;
      write_log('oldMaxInv ' || oldMaxInv  || ' , wearerinventoryID ' || wearerinventoryID , 3);
      RETURN TRUE;
  END getOldMaxInventory;


  PROCEDURE insertValuesIntoSqItemInCirc(sqItemInCirculationID    IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                         sqHeaderID               statusquoheader.statusquoheader_id%TYPE,
                                         customerID               customer.customer_id%TYPE,
                                         departmentID             department.department_id%TYPE,
                                         distributionPointID      distributionpoint.distributionpoint_id%TYPE,
                                         wearerEmploymentID       weareremployment.weareremployment_id%TYPE,
                                         productID                product.product_id%TYPE,
                                         sizeDefinitionID         sizedefinition.sizedefinition_id%TYPE,
                                         finishingMethodID        finishingmethod.finishingmethod_id%TYPE,
                                         v_maxinv                 wearerinventory.maxinventory%TYPE,
                                         v_qtytotal               sqitemincirculation.totalcircinv%TYPE,
                                         v_changesPerWeek         wearerinventory.changesPerWeek%TYPE,
                                         v_maxFreeOfCharge        wearerinventory.maxFreeOfCharge%TYPE,
                                         v_qtyfree                sqitemincirculation.freeissuedcircinv%TYPE,
                                         v_cogcircinv             sqitemincirculation.cogcircinv%TYPE,
                                         v_qtyfreeseason          sqitemincirculation.freeseasoncircinv%TYPE,
                                         v_beyondlifetypecirc     sqitemincirculation.beyondlifetimecirc%TYPE,
                                         v_qtyrentedperpiece      sqitemincirculation.qtyrentedperpiece%TYPE,
                                         v_qtyrentother           sqitemincirculation.qtyrentedother%TYPE,
                                         v_qtycustrentstop        sqitemincirculation.qtycustomerrentstop%TYPE,
                                         v_qtyrentstop            sqitemincirculation.qtyrentstop%TYPE,
                                         emblemTemplateID         emblemtemplate.emblemtemplate_id%TYPE,
                                         v_qtyrentemblem          sqitemincirculation.qtyrentemblem%TYPE,
                                         v_qtyreplacement         sqitemincirculation.qtyreplacement%TYPE,
                                         v_qtyassigned            sqitemincirculation.qtyassigned%TYPE,
                                         v_qtyrenteddepositstock  sqitemincirculation.qtyrenteddepositstock%TYPE,
                                         v_qtysubstitute          sqitemincirculation.qtysubstitute%TYPE,
                                         v_chargeafterdepreciation VARCHAR2,
                                         v_qtyrentedperpiecepool  sqitemincirculation.qtyrentedperpiecepool%TYPE,
                                         v_qtyrentedreturnrent    sqitemincirculation.qtyrentedreturnrent %TYPE,
                                         v_qtyrentedminrent       sqitemincirculation.qtyrentedminrent%TYPE,
                                         v_qtyfullyamortized      sqitemincirculation.qtyfullyamortized%TYPE) IS
    v_wearerEmploymentID weareremployment.weareremployment_id%TYPE;

  BEGIN
    v_wearerEmploymentID := getWearerEmploymentLinkID(wearerEmploymentID);

    IF (sqItemInCirculationID = -1) THEN
      BEGIN
        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,
           qtyrentedperpiecepool,
           variationrentedperpiecepool,
           chargeafterdepreciation,
           yearweek,
           qtyrentedreturnrent,
           variationqtyrentedreturnrent,
           qtyrentedminrent,
           variationqtyrentedminrent,
           qtyfullyamortized,
           variationqtyfullyamortized)
        VALUES
          (seq_sqitemincirculation.NEXTVAL,
           sqHeaderID,
           customerID,
           departmentID,
           distributionPointID,
           v_wearerEmploymentID,
           productID,
           sizeDefinitionID,
           finishingMethodID,
           v_maxinv,
           v_qtytotal,
           v_changesPerWeek,
           v_maxFreeOfCharge,
           v_qtyfree,
           v_cogcircinv,
           v_qtyfreeseason,
           v_beyondlifetypecirc,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0, --variation will be updated afterwords
           sq_systemUserid,
           SYSDATE,
           v_qtyrentedperpiece,
           v_qtyrentother,
           0, --variation
           v_qtycustrentstop,
           0, --variation
           v_qtyrentstop,
           0, --variation
           emblemTemplateID,
           v_qtyrentemblem,
           0, --variation
           v_qtyreplacement,
           0, --variation
           v_qtyassigned,
           0, --variation
           v_qtyrenteddepositstock,
           0, --variation
           v_qtysubstitute,
           0, --variation
           v_qtyrentedperpiecepool,
           0, --variation
           v_chargeafterdepreciation,
           sq_year * 100 + sq_week,
           v_qtyrentedreturnrent,
           0, --variation
           v_qtyrentedminrent,
           0, --variation
           v_qtyfullyamortized,
           0)
        RETURNING sqitemincirculation_id INTO sqItemInCirculationID;

      EXCEPTION
        WHEN dup_val_on_index THEN

BEGIN
          UPDATE sqitemincirculation
             SET maxinv                = maxinv + v_maxinv,
                 totalcircinv          = totalcircinv + v_qtytotal,
                 changesPerWeek        = changesPerWeek + v_changesPerWeek,
                 freeissuedcircinv     = freeissuedcircinv + v_qtyfree,
                 cogcircinv            = cogcircinv + v_cogcircinv,
                 freeseasoncircinv     = freeseasoncircinv + v_qtyfreeseason,
                 beyondlifetimecirc    = beyondlifetimecirc +
                                         v_beyondlifetypecirc,
                 qtyrentedperpiece     = qtyrentedperpiece +
                                         v_qtyrentedperpiece,
                 qtyrentedother        = qtyrentedother + v_qtyrentother,
                 qtycustomerrentstop   = qtycustomerrentstop +
                                         v_qtycustrentstop,
                 qtyrentstop           = qtyrentstop + v_qtyrentstop,
                 qtyrentemblem         = qtyrentemblem + v_qtyrentemblem,
                 qtyreplacement        = qtyreplacement + v_qtyreplacement,
                 qtyassigned           = qtyassigned + v_qtyassigned,
                 qtyrenteddepositstock = qtyrenteddepositstock +
                                         v_qtyrenteddepositstock,
                 qtysubstitute         = qtysubstitute + v_qtysubstitute,
                 qtyrentedperpiecepool = qtyrentedperpiecepool + v_qtyrentedperpiecepool,
                 qtyrentedreturnrent   = qtyrentedreturnrent + v_qtyrentedreturnrent,
                 qtyrentedminrent      = qtyrentedminrent + v_qtyrentedminrent,
                 qtyfullyamortized     = qtyfullyamortized + v_qtyfullyamortized,
                 systemUser_id         = sq_systemUserid,
                 TIMESTAMP             = SYSDATE
           WHERE statusquoheader_id = sqHeaderID
                 AND customer_link_id = customerID
                 AND department_link_id = departmentID
                 AND distributionpoint_link_id = distributionPointID
                 AND (weareremployment_link_id = v_wearerEmploymentID OR weareremployment_link_id IS NULL)
                 AND product_link_id = productID
                 AND sizedefinition_link_id = sizeDefinitionID
                 AND finishingmethod_id = finishingMethodID;
EXCEPTION
WHEN OTHERS THEN
write_log('Update record in Sqitemincirculation table for sqItemInCirculationID ' || sqItemInCirculationID || ' has failed for statusquoheader_id: ' || sqHeaderID || '  customerID ' || customerID|| ', departmentID '|| departmentID|| ', distributionPointID'
||distributionPointID || ' ,wearerEmploymentID '|| wearerEmploymentID || ' , productID' || productID|| ' ,sizeDefinitionID' || sizeDefinitionID || ' ,finishingMethodID '|| finishingMethodID || ' Please check!');
commit;
END;
      END;
    ELSE
BEGIN
      UPDATE sqitemincirculation
         SET maxinv                = maxinv + v_maxinv,
             totalcircinv          = totalcircinv + v_qtytotal,
             freeissuedcircinv     = freeissuedcircinv + v_qtyfree,
             cogcircinv            = cogcircinv + v_cogcircinv,
             freeseasoncircinv     = freeseasoncircinv + v_qtyfreeseason,
             beyondlifetimecirc    = beyondlifetimecirc +
                                     v_beyondlifetypecirc,
             qtyrentedperpiece     = qtyrentedperpiece + v_qtyrentedperpiece,
             qtyrentedother        = qtyrentedother + v_qtyrentother,
             qtycustomerrentstop   = qtycustomerrentstop + v_qtycustrentstop,
             qtyrentstop           = qtyrentstop + v_qtyrentstop,
             qtyrentemblem         = qtyrentemblem + v_qtyrentemblem,
             qtyreplacement        = qtyreplacement + v_qtyreplacement,
             qtyassigned           = qtyassigned + v_qtyassigned,
             qtyrenteddepositstock = qtyrenteddepositstock +
                                     v_qtyrenteddepositstock,
             qtysubstitute         = qtysubstitute + v_qtysubstitute,
             qtyrentedperpiecepool = qtyrentedperpiecepool + v_qtyrentedperpiecepool,
             qtyrentedreturnrent   = qtyrentedreturnrent + v_qtyrentedreturnrent,
             qtyrentedminrent      = qtyrentedminrent + v_qtyrentedminrent,
             qtyfullyamortized     = qtyfullyamortized + v_qtyfullyamortized,
             systemUser_id         = sq_systemUserid,
             TIMESTAMP             = SYSDATE
       WHERE /*statusquoheader_id = sqHeaderID
                                 and customer_id = customerID
                                 and department_id = departmentID
                                 and distributionpoint_id = distributionpointID
                                 and weareremployment_id = weareremploymentID
                                 and product_link_id = productID
                           and sizedefinition_link_id = sizeID
                      and finishingmethod_id = finMethID*/
       sqitemincirculation_id = sqItemInCirculationID;
      --    COMMIT;
EXCEPTION
WHEN OTHERS THEN
write_log('Update record in Sqitemincirculation table for sqItemInCirculationID ' || sqItemInCirculationID || ' has failed. Please check!');
commit;
END;
    END IF;
  END insertValuesIntoSqItemInCirc;

  PROCEDURE createSQItemInCircOnUniqueItem(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                           processBUID         businessunit.businessunit_id%TYPE,
                                           invoiceBUID         businessunit.businessunit_id%TYPE,
                                           sizeStarID          sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu      INT,
                                           sqonwearerlevel     INT,
                                           inactiveplpc        INT,
                                           flagSubstituteID    INT,
                                           flagReplacementID   INT,
                                           stayDepositStockID  INT,
                                           invoicereplacements INT,
                                           invoicesubstitutes  INT) IS

    -- declaration of variables
    emblemTemplateID      emblemtemplate.emblemtemplate_Id%TYPE := -1;
    sqItemInCirculationID sqitemincirculation.sqitemincirculation_id%TYPE := -1;

    --status_id for UI 'In Circulation' and 'Assigned' (used for createSQItemInCircOnUniqueItem)
    statusUIInCirculation status.status_id%TYPE := getstatusid('UI',
                                                               '20');
    statusAssignedID      status.status_id%TYPE := getstatusid('UI',
                                                               '30');
    v_qtyTotal   number := 0;
     v_qtyCOG   number:= 0;
     v_qtyAssigned   number:= 0;
     v_qtyFree   number:= 0;
     v_qtyCustRentStop   number:= 0;
     v_qtyRentStop   number:= 0;
     v_qtyFreeSeason   number:= 0;
     v_qtyReplacement   number:= 0;
     v_qtySubstitute   number:= 0;
     v_qtyDepositStock   number:= 0;
     v_qtyBeyondLifeTime   number:= 0;
     v_qtyLease              number:= 0;
     v_qtyRentedPerPiecePool  number := 0;
     v_qtyFullyAmortized      number := 0;
   --  v_maxCust      customer.customer_id%type := 99000;
   -- i              number := 0;
   -- quantile       number := 4;


    CURSOR stmtBOnWearerLevel is
      SELECT /*+index(wi XAK1WEARERINVENTORY) index_ffs(a XAK2UNIQUEITEM) index_ffs(b XAK1UNIQUEITEMNONPOOL)*/ 
             e.department_id,
             d.distributionpoint_id,
             c.weareremployment_id,
             wi.product_id,
             wi.sizedefinition_id,
             nvl(wi.finishingmethod_id,
                 -1) finishingmethod_id,
             e.customer_id,
             i.customernumber,
             f.code,
             wi.emblemtemplate_id,
             i.ChargeAmortizedUniqueItems
        FROM uniqueitem         a,
             uniqueitemnonpool  b,
             weareremployment   c,
             distributionpoint  d,
             department         e,
             product            f,
             productgroup       g,
             customeractivities h,
             customer           i,
             wearerinventory    wi
       WHERE a.uniqueitem_id = b.uniqueitem_id
             AND b.weareremployment_id = c.weareremployment_id
             AND c.distributionpoint_id = d.distributionpoint_id
             AND d.department_id = e.department_id
             AND wi.product_id = f.product_id
             AND f.productgroup_id = g.productgroup_id
             AND abs(g.activity_id) = abs(h.activity_id)
             AND e.customer_id = h.customer_id
             AND e.customer_id = i.customer_id
             AND b.wearerinventory_id = wi.wearerinventory_id
             AND h.processbusinessunit_id = processBUID
             AND
             (checkinvoicebu = 0 OR h.invoicebusinessunit_id = invoiceBUID)
             AND abs(h.processbusinessunit_id) = abs(a.businessunit_id)
             AND a.status_id IN (statusUIInCirculation, statusAssignedID)
           GROUP BY e.customer_id,
                c.weareremployment_id,
                d.distributionpoint_id,
                e.department_id,
                wi.product_id,
                wi.sizedefinition_id,
                wi.finishingmethod_id,
                wi.emblemtemplate_id,
                i.customernumber,
                f.code,
                i.ChargeAmortizedUniqueItems;

  BEGIN

      FOR rs IN stmtBOnWearerLevel LOOP
        --   IF (sqItemInCirculationID = -1) THEN
        --search if exists an sqitemincircID
        BEGIN
          SELECT getsqitemincirconuirentforstmt(sqheaderid,
                                                rs.distributionpoint_id,
                                                case sqonwearerlevel when 1 then
                                                rs.weareremployment_ID
                                                else -1 end,
                                                rs.product_id,
                                                rs.sizedefinition_id,
                                                rs.finishingmethod_id)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;
        --   END IF;
SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyTotal
                   FROM uniqueitem x, uniqueitemnonpool b, wearerinventory w
                  WHERE x.uniqueitem_id = b.uniqueitem_id
                  and b.weareremployment_id = rs.weareremployment_id
                  and b.wearerinventory_id = w.wearerinventory_id
                  and w.product_id = rs.product_id
                  and w.finishingmethod_id = rs.finishingmethod_id
                  and w.sizedefinition_id = rs.sizedefinition_id
                  and x.status_id IN (statusUIInCirculation, statusAssignedID);

       SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyCOG
                   FROM uniqueitem x, uniqueitemnonpool b, wearerinventory w
                  WHERE x.uniqueitem_id = b.uniqueitem_id
                   AND x.status_id = statusUIInCirculation
                   AND x.customerowned = 'Y'
                   and b.weareremployment_id = rs.weareremployment_id
                  and b.wearerinventory_id = w.wearerinventory_id
                  and w.product_id = rs.product_id
                  and w.finishingmethod_id = rs.finishingmethod_id
                  and w.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyFree
                   FROM uniqueitem x, uniqueitemnonpool b, wearerinventory w
                  WHERE x.uniqueitem_id = b.uniqueitem_id
                       AND nvl(x.leaseweeks,
                                0) = 0
                        AND x.status_id = statusUIInCirculation
                        AND b.freeextragarment = 'Y'
                        AND (b.enddatefreeextra > sq_date OR
                        nvl(b.enddatefreeextra,
                                 to_date('01/01/0001',
                                         'dd/mm/yyyy')) =
                        to_date('01/01/0001',
                                     'dd/mm/yyyy'))
                        AND NOT (x.customerowned = 'Y')
                        and b.weareremployment_id = rs.weareremployment_id
                        and b.wearerinventory_id = w.wearerinventory_id
                        and w.product_id = rs.product_id
                        and w.finishingmethod_id = rs.finishingmethod_id
                        and w.sizedefinition_id = rs.sizedefinition_id;

             SELECT COUNT(t.temporarygarmentrentstop_id)
              into v_qtyCustRentStop
                   FROM temporarygarmentrentstop t
                  WHERE t.customer_id = rs.customer_id
                        AND f_isinyearweeklifespan(startyear,
                                                   startweek,
                                                   sq_year,
                                                   sq_week,
                                                   endyear,
                                                   endweek) = 1 ;

             SELECT nvl(sum(case when z.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyRentStop
                   FROM uniqueitemnonpool   x,
                        wearerinventory     wi,
                        uniqueitem          z,
                        uniqueitemfixedinfo zfi
                  WHERE x.wearerinventory_id = wi.wearerinventory_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND zfi.uniqueitem_id = z.uniqueitem_id
                        AND nvl(z.leaseweeks,
                                0) = 0
                        AND z.status_id = statusUIInCirculation
                        AND NOT (x.freeextragarment = 'Y' AND
                         (x.enddatefreeextra > sq_date OR
                         nvl(x.enddatefreeextra,
                                      to_date('01/01/0001',
                                              'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                          'dd/mm/yyyy')))
                        AND NOT (z.customerowned = 'Y')
                        AND sq_date BETWEEN wi.temporaryrentstopstartdate AND wi.temporaryrentstopenddate
                        and x.weareremployment_id = rs.weareremployment_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyFreeSeason
                   FROM uniqueitem        x,
                        uniqueitemnonpool y,
                        wearerinventory   wi
                  WHERE  x.uniqueitem_id = y.uniqueitem_id
                        AND y.wearerinventory_id = wi.wearerinventory_id
                        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 = sq_week)
                        AND nvl(x.leaseweeks,
                                0) = 0
                        AND x.status_id = statusUIInCirculation
                        AND NOT ((y.freeextragarment = 'Y' AND
                         (y.enddatefreeextra > sq_date OR
                         nvl(y.enddatefreeextra,
                                       to_date('01/01/0001',
                                               'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                           'dd/mm/yyyy'))))
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (x.customerowned = 'Y')
                         and y.weareremployment_id = rs.weareremployment_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                    into v_qtyBeyondLifeTime
                   FROM uniqueitem          x,
                        uniqueitemnonpool   y,
                        uniqueitemfixedinfo z,
                        wearerinventory     wi
                  WHERE x.uniqueitem_id = y.uniqueitem_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND y.wearerinventory_id = wi.wearerinventory_id
                        AND nvl(x.leaseweeks,
                                0) = 0
                        AND x.status_id = statusUIInCirculation
                        AND z.enddatefullrent < sq_date
                        AND NOT ((y.freeextragarment = 'Y' AND
                         (y.enddatefreeextra > sq_date OR
                         nvl(y.enddatefreeextra,
                                       to_date('01/01/0001',
                                               'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                           'dd/mm/yyyy'))))
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (nvl(wi.season_id,
                                 0) = 0 OR EXISTS
                         (SELECT sw.season_id
                                FROM seasonweek sw
                               WHERE sw.season_id = wi.season_id
                                     AND sw.weeknumber = sq_week))
                        AND NOT (x.customerowned = 'Y')
                        and y.weareremployment_id = rs.weareremployment_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyAssigned
                   FROM uniqueitem x, uniqueitemnonpool b, wearerinventory wi
                  WHERE x.uniqueitem_id = b.uniqueitem_id
                        AND x.status_id = statusAssignedID
                        and x.uniqueitem_id= b.uniqueitem_id
                         and b.weareremployment_id = rs.weareremployment_id
                         and b.wearerinventory_id = wi.wearerinventory_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyLease
                   FROM uniqueitem x, uniqueitemnonpool b, wearerinventory wi
                  WHERE x.uniqueitem_id = b.uniqueitem_id
                        AND x.leaseweeks IS NOT NULL
                        AND x.status_id = statusUIInCirculation
                        and x.uniqueitem_id= b.uniqueitem_id
                         and b.weareremployment_id = rs.weareremployment_id
                         and b.wearerinventory_id = wi.wearerinventory_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when z.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyReplacement
                   FROM uniqueitemnonpool   x,
                        wearerinventory     wi,
                        uniqueitem          z,
                        uniqueitemfixedinfo zfi
                  WHERE x.wearerinventory_id = wi.wearerinventory_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND zfi.uniqueitem_id = z.uniqueitem_id
                        AND nvl(z.leaseweeks,
                                0) = 0
                        AND z.status_id = statusUIInCirculation
                        AND NOT (x.freeextragarment = 'Y' AND
                         (x.enddatefreeextra > sq_date OR
                         nvl(x.enddatefreeextra,
                                      to_date('01/01/0001',
                                              'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                          'dd/mm/yyyy')))
                        AND NOT (z.customerowned = 'Y')
                        AND NOT (zfi.enddatefullrent < sq_date)
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (nvl(wi.season_id,
                                 0) = 0 OR EXISTS
                         (SELECT sw.season_id
                                FROM seasonweek sw
                               WHERE sw.season_id = wi.season_id
                                     AND sw.weeknumber = sq_week))
                        AND EXISTS
                  (SELECT r.uniqueitem_id
                           FROM uniqueitemflag r
                          WHERE r.uniqueitem_id = z.uniqueitem_id
                                AND r.flag_id = flagReplacementID
                                AND r.startdate <= sq_date)
                         and x.weareremployment_id = rs.weareremployment_id
                         and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;


             SELECT nvl(sum(case when z.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtySubstitute
                   FROM uniqueitemnonpool   x,
                        wearerinventory     wi,
                        uniqueitem          z,
                        uniqueitemfixedinfo zfi
                  WHERE x.wearerinventory_id = wi.wearerinventory_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND zfi.uniqueitem_id = z.uniqueitem_id
                        AND nvl(z.leaseweeks,
                                0) = 0
                        AND z.status_id = statusUIInCirculation
                        AND NOT (x.freeextragarment = 'Y' AND
                         (x.enddatefreeextra > sq_date OR
                         nvl(x.enddatefreeextra,
                                      to_date('01/01/0001',
                                              'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                          'dd/mm/yyyy')))
                        AND NOT (z.customerowned = 'Y')
                        AND NOT (zfi.enddatefullrent < sq_date)
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (nvl(wi.season_id,
                                 0) = 0 OR EXISTS
                         (SELECT sw.season_id
                                FROM seasonweek sw
                               WHERE sw.season_id = wi.season_id
                                     AND sw.weeknumber = sq_week))
                        AND NOT EXISTS
                  (SELECT r.uniqueitem_id
                           FROM uniqueitemflag r
                          WHERE r.uniqueitem_id = z.uniqueitem_id
                                AND r.flag_id = flagReplacementID
                                AND r.startdate <= sq_date)
                        AND EXISTS
                  (SELECT r.uniqueitem_id
                           FROM uniqueitemflag r
                          WHERE r.uniqueitem_id = z.uniqueitem_id
                                AND r.flag_id = flagSubstituteID
                                AND r.startdate <= sq_date)
                   and x.weareremployment_id = rs.weareremployment_id
                         and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;

             SELECT nvl(sum(case when z.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
                   into v_qtyDepositStock
                   FROM uniqueitemnonpool   x,
                        wearerinventory     wi,
                        uniqueitem          z,
                        uniqueitemfixedinfo zfi
                  WHERE x.wearerinventory_id = wi.wearerinventory_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND zfi.uniqueitem_id = z.uniqueitem_id
                        AND nvl(z.leaseweeks,
                                0) = 0
                        AND z.stay_id = stayDepositStockID
                        AND z.status_id = statusUIInCirculation
                        AND NOT (x.freeextragarment = 'Y' AND
                         (x.enddatefreeextra > sq_date OR
                         nvl(x.enddatefreeextra,
                                      to_date('01/01/0001',
                                              'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                          'dd/mm/yyyy')))
                        AND NOT (z.customerowned = 'Y')
                        AND NOT (zfi.enddatefullrent < sq_date)
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (nvl(wi.season_id,
                                 0) = 0 OR EXISTS
                         (SELECT sw.season_id
                                FROM seasonweek sw
                               WHERE sw.season_id = wi.season_id
                                     AND sw.weeknumber = sq_week))
                        AND NOT EXISTS
                  (SELECT r.uniqueitem_id
                           FROM uniqueitemflag r
                          WHERE r.uniqueitem_id = z.uniqueitem_id
                                AND r.flag_id = flagReplacementID
                                AND r.startdate <= sq_date)
                        AND NOT EXISTS
                  (SELECT r.uniqueitem_id
                           FROM uniqueitemflag r
                          WHERE r.uniqueitem_id = z.uniqueitem_id
                                AND r.flag_id = flagSubstituteID
                                AND r.startdate <= sq_date)
                  and x.weareremployment_id = rs.weareremployment_id
                         and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id;
                        
        if (rs.ChargeAmortizedUniqueItems = 0) then
        SELECT nvl(sum(case when X.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
        into v_qtyFullyAmortized
                   FROM uniqueitem          x,
                        uniqueitemnonpool   y,
                        uniqueitemfixedinfo z,
                        wearerinventory     wi
                  WHERE  x.uniqueitem_id = y.uniqueitem_id
                        AND x.uniqueitem_id = z.uniqueitem_id
                        AND y.wearerinventory_id = wi.wearerinventory_id
                        and y.weareremployment_id = rs.weareremployment_id
                        and wi.product_id = rs.product_id
                        and wi.finishingmethod_id = rs.finishingmethod_id
                        and wi.sizedefinition_id = rs.sizedefinition_id
                        AND nvl(x.leaseweeks,
                                0) = 0
                        AND x.status_id = statusUIInCirculation
                        AND z.enddatefullrent >= sq_date
                        AND NOT ((y.freeextragarment = 'Y' AND
                         (y.enddatefreeextra > sq_date OR
                         nvl(y.enddatefreeextra,
                                       to_date('01/01/0001',
                                               'dd/mm/yyyy')) =
                         to_date('01/01/0001',
                                           'dd/mm/yyyy'))))
                        AND ((sq_date < wi.temporaryrentstopstartdate OR
                        sq_date > 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 (nvl(wi.season_id,
                                 0) = 0 OR EXISTS
                         (SELECT sw.season_id
                                FROM seasonweek sw
                               WHERE sw.season_id = wi.season_id
                                     AND sw.weeknumber = sq_week))
                        AND NOT (x.customerowned = 'Y')
                        AND x.ResidualValueAmount = 0;  
		else v_qtyFullyAmortized:=0;				
       end if;
                                         
       SELECT nvl(sum(case when ui.BUSINESSUNIT_ID = processBUID then 1 else 0 end),0)
       into v_qtyRentedPerPiecePool
                 FROM uniqueitem ui, uniqueitemnonpool b, wearerinventory wi
                WHERE ui.uniqueitem_id = b.uniqueitem_id 
                 AND b.wearerinventory_id = wi.wearerinventory_id
                and b.weareremployment_id = rs.weareremployment_id
                and wi.product_id = rs.product_id
                and wi.finishingmethod_id = rs.finishingmethod_id
                and wi.sizedefinition_id = rs.sizedefinition_id
                 AND ui.status_id = statusUIInCirculation
                 AND ui.stay_id = PACK_SQITEMSINCIRCULATION.GETSTAYID(STAY_TYPE_GARMENT, 20)  
                  AND trunc(ui.lastoutscandate) < trunc(sysdate) -
                     7 *
                     (SELECT plpc.chargerentnoreturnpoolafterwks
                        FROM productlistpercustomer plpc
                       WHERE plpc.productlistpercustomer_id =
                             (SELECT coalesce((SELECT plpc1.productlistpercustomer_id
                                                FROM productlistpercustomer plpc1
                                               WHERE plpc1.billingmethod = 3
                                                 AND plpc1.inventorybillingtype = 4
                                                 AND plpc1.chargerentnotreturned <> 0 
                                                 AND plpc1.chargerentnoreturnpoolafterwks > 0
                                                 AND plpc1.codeuniquely = 'Y'
                                                 AND plpc1.customer_id = rs.customer_id
                                                 AND plpc1.product_id = rs.product_id
                                                 AND plpc1.sizedefinition_id = ui.sizedefinition_id
                                                 AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                                 AND plpc1.dateactive =
                                                     (select max(plpc2.dateactive)
                                                      from productlistpercustomer plpc2
                                                     where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                       and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                       and plpc2.product_id = plpc1.product_id
                                                       and plpc2.customer_id = plpc1.customer_id
                                                       and plpc2.dateactive < sysdate
                                                       and plpc2.billingmethod = 3
                                                       and plpc2.inventorybillingtype = 4
                                                       and plpc2.chargerentnotreturned <> 0
                                                       and plpc2.chargerentnoreturnpoolafterwks > 0
                                                       and plpc2.codeuniquely = 'Y')),                                              
                                                 coalesce((SELECT plpc1.productlistpercustomer_id
                                                            FROM productlistpercustomer plpc1
                                                           WHERE plpc1.billingmethod = 3
                                                             AND plpc1.inventorybillingtype = 4
                                                             AND plpc1.chargerentnotreturned <> 0 
                                                             AND plpc1.chargerentnoreturnpoolafterwks > 0
                                                             AND plpc1.codeuniquely = 'Y'
                                                             AND plpc1.customer_id = rs.customer_id
                                                             AND plpc1.product_id = rs.product_id
                                                             AND plpc1.sizedefinition_id =
                                                                 sizeStarID
                                                             AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                                             AND plpc1.dateactive =
                                                             (select max(plpc2.dateactive)
                                                              from productlistpercustomer plpc2
                                                             where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                               and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                               and plpc2.product_id = plpc1.product_id
                                                               and plpc2.customer_id = plpc1.customer_id
                                                               and plpc2.dateactive < sysdate
                                                               and plpc2.billingmethod = 3
                                                               and plpc2.inventorybillingtype = 4
                                                               and plpc2.chargerentnotreturned <> 0
                                                               and plpc2.chargerentnoreturnpoolafterwks > 0
                                                               and plpc2.codeuniquely = 'Y')),
                                                          -1)
                                             )
                                    FROM dual  )); 
                  
        
        -- Create or Store
        emblemTemplateID := case sqonwearerlevel when 1 
                              then rs.emblemtemplate_ID
                              else -1 
                            end;
        createOrStoreOnSQforUniqueItem(sqheaderid,
                                       inactiveplpc,
                                       rs.customer_id,
                                       rs.department_id,
                                       rs.distributionpoint_id,
                                       rs.product_id,
                                       rs.sizedefinition_id,
                                       rs.finishingmethod_id,
                                       case sqonwearerlevel when 1 then
                                           rs.weareremployment_ID
                                         else 
                                           -1 
                                       end,
                                       emblemTemplateID,
                                       sizeStarID,
                                       invoiceReplacements,
                                       invoiceSubstitutes,
                                       sqItemInCirculationID,
                                       sqOnWearerLevel,
                                        v_qtyTotal,
                                       v_qtyCOG,
                                       v_qtyAssigned,
                                       v_qtyFree,
                                       v_qtyCustRentStop,
                                       v_qtyRentStop,
                                       v_qtyFreeSeason,
                                       v_qtyReplacement,
                                       v_qtySubstitute,
                                       v_qtyDepositStock,
                                       v_qtyBeyondLifeTime,
                                       v_qtyLease,
                                       v_qtyFullyAmortized,
                                       v_qtyRentedPerPiecePool);
      END LOOP;
   
  END; --createSQItemInCircOnUniqueItem

  FUNCTION getStatusID(vstatustype status.statustype %TYPE,
                       vstatuscode status.code%TYPE)
    RETURN status.status_id%TYPE IS

    --declaration of variables
    vstatusid status.status_id%TYPE;

  BEGIN

    BEGIN
      SELECT st.status_id
        INTO vstatusid
        FROM status st
       WHERE st.statustype = vstatustype
             AND st.code = vstatuscode;
    EXCEPTION
      WHEN no_data_found THEN
        vstatusid := -1;
    END;

    RETURN vstatusid;

  END getStatusID;

  PROCEDURE createSqItemInCircOnUniqueRent(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                           processBuID                  businessunit.businessunit_id%TYPE,
                                           invoiceBuID                  businessunit.businessunit_id%TYPE,
                                           statusIDforUniqueItemInStock status.status_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu               INT,
                                           sqOnWearerLevel              INT,
                                           inactivePLPC                 INT) IS

    -- declation of variables
    sqItemInCirculationID sqitemincirculation.sqitemincirculation_id%TYPE := -1;

    --declaration of cursors
    CURSOR stmtC IS
      SELECT we.distributionpoint_id,
             dp.department_id,
             we.weareremployment_id,
             ui.product_id,
             ui.sizedefinition_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                coalesce(ui.finishingmethod_id,
                         (SELECT w2.finishingmethod_id
                            FROM wearerinventory w2
                           WHERE w2.weareremployment_id =
                                 we.weareremployment_id
                                 AND w2.product_id = ui.product_id
                                 AND
                                 w2.sizedefinition_id = ui.sizedefinition_id
                                 AND rownum = 1))
               ELSE
                coalesce(ui.finishingmethod_id,
                         (SELECT w2.finishingmethod_id
                            FROM wearerinventory w2, weareremployment e2
                           WHERE w2.weareremployment_id =
                                 e2.weareremployment_id
                                 AND e2.distributionpoint_id =
                                 we.distributionpoint_id
                                 AND w2.product_id = ui.product_id
                                 AND
                                 w2.sizedefinition_id = ui.sizedefinition_id
                                 AND rownum = 1))
             END finishingMethodID,
             w.customer_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                we.weareremployment_id
               ELSE
                NULL
             END wearerEmploymentID,
             COUNT(ui.uniqueitem_id) noOfUniqueItems
        FROM uniqueitem          ui,
             uniqueitemfixedinfo uifi,
             reasoncode          rc,
             weareremployment    we,
             wearer              w,
             distributionpoint   dp,
             product             p,
             productgroup        pg,
             customeractivities  ca
       WHERE ui.status_id = statusIDforUniqueItemInStock
             AND ui.uniqueitem_id = uifi.uniqueitem_id
             AND uifi.reasoncode_id = rc.reasoncode_id
             AND rc.calculaterentonstock = 'Y'
             AND ui.lastweareremployment_link_id = we.weareremployment_id
             AND we.wearer_id = w.wearer_id
             AND we.distributionpoint_id = dp.distributionpoint_id
             AND p.product_id = ui.product_id
             AND pg.productgroup_id = p.productgroup_id
             AND ca.activity_id = pg.activity_id
             AND ca.customer_id = w.customer_id
             AND ca.processbusinessunit_id = processBuID

             AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
             AND
             'Y' =
             (SELECT p.depositStockAllowed
                FROM productlistpercustomer p
               WHERE p.productlistpercustomer_id =
                     (SELECT coalesce((SELECT productlistpercustomer_id
                                        FROM productlistpercustomer
                                       WHERE customer_id = w.customer_id
                                             AND product_id = ui.product_id
                                             AND sizedefinition_id =
                                             ui.sizedefinition_id
                                             AND
                                             finishingmethod_id =
                                             (CASE sqOnWearerLevel WHEN 1 THEN
                                              coalesce(ui.finishingmethod_id,
                                                       (SELECT w2.finishingmethod_id
                                                          FROM wearerinventory w2
                                                         WHERE w2.weareremployment_id =
                                                               we.weareremployment_id
                                                               AND w2.product_id =
                                                               ui.product_id
                                                               AND
                                                               w2.sizedefinition_id =
                                                               ui.sizedefinition_id
                                                               AND rownum = 1)) ELSE
                                              coalesce(ui.finishingmethod_id,
                                                       (SELECT w2.finishingmethod_id
                                                          FROM wearerinventory  w2,
                                                               weareremployment e2
                                                         WHERE w2.weareremployment_id =
                                                               e2.weareremployment_id
                                                               AND
                                                               e2.distributionpoint_id =
                                                               we.distributionpoint_id
                                                               AND w2.product_id =
                                                               ui.product_id
                                                               AND
                                                               w2.sizedefinition_id =
                                                               ui.sizedefinition_id
                                                               AND rownum = 1)) END)
                                             AND productlistpercustomer.dateactive <=
                                             sq_date
                                             AND productlistpercustomer.dateinactive >=
                                             sq_date),
                                      (CASE ui.sizedefinition_id
                                        WHEN sizeStarID THEN
                                         -1
                                        ELSE
                                         coalesce((SELECT productlistpercustomer_id
                                                    FROM productlistpercustomer
                                                   WHERE customer_id =
                                                         w.customer_id
                                                         AND product_id =
                                                         ui.product_id
                                                         AND sizedefinition_id =
                                                         sizeStarID
                                                         AND
                                                         finishingmethod_id =
                                                         (CASE sqOnWearerLevel WHEN 1 THEN
                                                          coalesce(ui.finishingmethod_id,
                                                                   (SELECT w2.finishingmethod_id
                                                                      FROM wearerinventory w2
                                                                     WHERE w2.weareremployment_id =
                                                                           we.weareremployment_id
                                                                           AND
                                                                           w2.product_id =
                                                                           ui.product_id
                                                                           AND
                                                                           w2.sizedefinition_id =
                                                                           ui.sizedefinition_id
                                                                           AND
                                                                           rownum = 1)) ELSE
                                                          coalesce(ui.finishingmethod_id,
                                                                   (SELECT w2.finishingmethod_id
                                                                      FROM wearerinventory  w2,
                                                                           weareremployment e2
                                                                     WHERE w2.weareremployment_id =
                                                                           e2.weareremployment_id
                                                                           AND
                                                                           e2.distributionpoint_id =
                                                                           we.distributionpoint_id
                                                                           AND
                                                                           w2.product_id =
                                                                           ui.product_id
                                                                           AND
                                                                           w2.sizedefinition_id =
                                                                           ui.sizedefinition_id
                                                                           AND
                                                                           rownum = 1)) END)
                                                         AND productlistpercustomer.dateactive <=
                                                         sq_date
                                                         AND productlistpercustomer.dateinactive >=
                                                         sq_date),
                                                  -1)
                                      END))
                        FROM dual))
             AND uifi.enddatefullrent > sq_date
       GROUP BY we.weareremployment_id,
                dp.department_id,
                w.customer_id,
                we.distributionpoint_id,
                ui.product_id,
                ui.sizedefinition_id,
                ui.finishingmethod_id,
                6;

    CURSOR stmtC_checkinvoicedbu IS
      SELECT we.distributionpoint_id,
             dp.department_id,
             we.weareremployment_id,
             ui.product_id,
             ui.sizedefinition_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                coalesce(ui.finishingmethod_id,
                         (SELECT w2.finishingmethod_id
                            FROM wearerinventory w2
                           WHERE w2.weareremployment_id =
                                 we.weareremployment_id
                                 AND w2.product_id = ui.product_id
                                 AND
                                 w2.sizedefinition_id = ui.sizedefinition_id
                                 AND rownum = 1))
               ELSE
                coalesce(ui.finishingmethod_id,
                         (SELECT w2.finishingmethod_id
                            FROM wearerinventory w2, weareremployment e2
                           WHERE w2.weareremployment_id =
                                 e2.weareremployment_id
                                 AND e2.distributionpoint_id =
                                 we.distributionpoint_id
                                 AND w2.product_id = ui.product_id
                                 AND
                                 w2.sizedefinition_id = ui.sizedefinition_id
                                 AND rownum = 1))
             END finishingMethodID,
             w.customer_id,
             CASE sqOnWearerLevel
               WHEN 1 THEN
                we.weareremployment_id
               ELSE
                NULL
             END wearerEmploymentID,
             COUNT(ui.uniqueitem_id) noOfUniqueItems
        FROM uniqueitem          ui,
             uniqueitemfixedinfo uifi,
             reasoncode          rc,
             weareremployment    we,
             wearer              w,
             distributionpoint   dp,
             product             p,
             productgroup        pg,
             customeractivities  ca
       WHERE ui.status_id = statusIDforUniqueItemInStock
             AND ui.uniqueitem_id = uifi.uniqueitem_id
             AND uifi.reasoncode_id = rc.reasoncode_id
             AND rc.calculaterentonstock = 'Y'
             AND ui.lastweareremployment_link_id = we.weareremployment_id
             AND we.wearer_id = w.wearer_id
             AND we.distributionpoint_id = dp.distributionpoint_id
             AND p.product_id = ui.product_id
             AND pg.productgroup_id = p.productgroup_id
             AND ca.activity_id = pg.activity_id
             AND ca.customer_id = w.customer_id
             AND ca.processbusinessunit_id = processBuID
             AND ca.invoicebusinessunit_id = invoiceBuID
             AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
             AND
             'Y' =
             (SELECT p.depositStockAllowed
                FROM productlistpercustomer p
               WHERE p.productlistpercustomer_id =
                     (SELECT coalesce((SELECT productlistpercustomer_id
                                        FROM productlistpercustomer
                                       WHERE customer_id = w.customer_id
                                             AND product_id = ui.product_id
                                             AND sizedefinition_id =
                                             ui.sizedefinition_id
                                             AND
                                             finishingmethod_id =
                                             (CASE sqOnWearerLevel WHEN 1 THEN
                                              coalesce(ui.finishingmethod_id,
                                                       (SELECT w2.finishingmethod_id
                                                          FROM wearerinventory w2
                                                         WHERE w2.weareremployment_id =
                                                               we.weareremployment_id
                                                               AND w2.product_id =
                                                               ui.product_id
                                                               AND
                                                               w2.sizedefinition_id =
                                                               ui.sizedefinition_id
                                                               AND rownum = 1)) ELSE
                                              coalesce(ui.finishingmethod_id,
                                                       (SELECT w2.finishingmethod_id
                                                          FROM wearerinventory  w2,
                                                               weareremployment e2
                                                         WHERE w2.weareremployment_id =
                                                               e2.weareremployment_id
                                                               AND
                                                               e2.distributionpoint_id =
                                                               we.distributionpoint_id
                                                               AND w2.product_id =
                                                               ui.product_id
                                                               AND
                                                               w2.sizedefinition_id =
                                                               ui.sizedefinition_id
                                                               AND rownum = 1)) END)
                                             AND productlistpercustomer.dateactive <=
                                             sq_date

                                             AND productlistpercustomer.dateinactive >=
                                             sq_date),
                                      (CASE ui.sizedefinition_id
                                        WHEN sizeStarID THEN
                                         -1
                                        ELSE
                                         coalesce((SELECT productlistpercustomer_id
                                                    FROM productlistpercustomer
                                                   WHERE customer_id =
                                                         w.customer_id
                                                         AND product_id =
                                                         ui.product_id
                                                         AND sizedefinition_id =
                                                         sizeStarID
                                                         AND
                                                         finishingmethod_id =
                                                         (CASE sqOnWearerLevel WHEN 1 THEN
                                                          coalesce(ui.finishingmethod_id,
                                                                   (SELECT w2.finishingmethod_id
                                                                      FROM wearerinventory w2
                                                                     WHERE w2.weareremployment_id =
                                                                           we.weareremployment_id
                                                                           AND
                                                                           w2.product_id =
                                                                           ui.product_id
                                                                           AND
                                                                           w2.sizedefinition_id =
                                                                           ui.sizedefinition_id
                                                                           AND
                                                                           rownum = 1)) ELSE
                                                          coalesce(ui.finishingmethod_id,
                                                                   (SELECT w2.finishingmethod_id
                                                                      FROM wearerinventory  w2,
                                                                           weareremployment e2
                                                                     WHERE w2.weareremployment_id =
                                                                           e2.weareremployment_id
                                                                           AND
                                                                           e2.distributionpoint_id =
                                                                           we.distributionpoint_id
                                                                           AND
                                                                           w2.product_id =
                                                                           ui.product_id
                                                                           AND
                                                                           w2.sizedefinition_id =
                                                                           ui.sizedefinition_id
                                                                           AND
                                                                           rownum = 1)) END)
                                                         AND productlistpercustomer.dateactive <=
                                                         sq_date
                                                         AND productlistpercustomer.dateinactive >=
                                                         sq_date),
                                                  -1)
                                      END))
                        FROM dual))
             AND uifi.enddatefullrent > sq_date
       GROUP BY we.weareremployment_id,
                dp.department_id,
                w.customer_id,
                we.distributionpoint_id,
                ui.product_id,
                ui.sizedefinition_id,
                ui.finishingmethod_id,
                6;

  BEGIN

    IF (checkInvoiceBu = 1) THEN
      FOR rs_checkBu IN stmtC_checkinvoicedbu LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs_checkBu.distributionpoint_id,
                                                rs_checkBu.wearerEmploymentID,
                                                rs_checkBu.product_id,
                                                rs_checkBu.sizedefinition_id,
                                                rs_checkBu.finishingMethodID)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForUiRent(sqHeaderID,
                                   rs_checkBu.customer_id,
                                   rs_checkBu.department_id,
                                   rs_checkBu.distributionpoint_id,
                                   rs_checkBu.product_id,
                                   rs_checkBu.sizedefinition_id,
                                   rs_checkBu.finishingMethodID,
                                   rs_checkBu.weareremployment_id,
                                   sqItemInCirculationID,
                                   rs_checkBu.noOfUniqueItems,
                                   inactivePLPC,
                                   sizeStarID);
      END LOOP;
    ELSE
      FOR rs IN stmtC LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs.distributionpoint_id,
                                                rs.wearerEmploymentID,
                                                rs.product_id,
                                                rs.sizedefinition_id,
                                                rs.finishingMethodID)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForUiRent(sqHeaderID,
                                   rs.customer_id,
                                   rs.department_id,
                                   rs.distributionpoint_id,
                                   rs.product_id,
                                   rs.sizedefinition_id,
                                   rs.finishingMethodID,
                                   rs.weareremployment_id,
                                   sqItemInCirculationID,
                                   rs.noOfUniqueItems,
                                   inactivePLPC,
                                   sizeStarID);
      END LOOP;
    END IF;

  END createSqItemInCircOnUniqueRent;

  FUNCTION getSqItemInCircOnUiRentForStmt(sqHeaderID          statusquoheader.statusquoheader_id%TYPE,
                                          distributionPointID weareremployment.distributionpoint_id%TYPE,
                                          wearerEmploymentID  weareremployment.weareremployment_id%TYPE,
                                          productID           uniqueitem.product_id%TYPE,
                                          sizeDefinitionID    uniqueitem.sizedefinition_id%TYPE,
                                          finishingMethodID   INT)
    RETURN sqitemincirculation.sqitemincirculation_id%TYPE IS

    -- declaration of variables
    sqItemInCirculationID sqitemincirculation.sqitemincirculation_id%TYPE := -1;

  BEGIN
    BEGIN
      SELECT coalesce((CASE coalesce(wearerEmploymentID,
                                 -1)
                        WHEN -1 THEN
                         (CASE coalesce(finishingMethodID, -1)
                        WHEN -1 THEN
                         (SELECT MIN(x.sqitemincirculation_id)
                            FROM sqitemincirculation x
                           WHERE x.statusquoheader_id = sqHeaderID
                                 AND x.distributionpoint_link_id =
                                 distributionPointID
                                 AND nvl(x.weareremployment_link_id,
                                         0) = 0
                                 AND x.product_link_id = productID
                                 AND
                                 x.sizedefinition_link_id = sizeDefinitionID)
                        ELSE
                         (SELECT min(x.sqitemincirculation_id)
                            FROM sqitemincirculation x
                           WHERE x.statusquoheader_id = sqHeaderID
                                 AND x.distributionpoint_link_id =
                                 distributionPointID
                                 AND nvl(x.weareremployment_link_id,
                                         0) = 0
                                 AND x.product_link_id = productID
                                 AND
                                 x.sizedefinition_link_id = sizeDefinitionID
                                 AND
                                 x.finishingmethod_id = finishingMethodID)
                      END) ELSE(CASE coalesce(finishingMethodID,
                                     -1)
                             WHEN -1 THEN
                              (SELECT MIN(sqitemincirculation_id)
                                 FROM sqitemincirculation x
                                WHERE x.statusquoheader_id = sqHeaderID
                                      AND x.distributionpoint_link_id =
                                      distributionPointID
                                      AND x.weareremployment_link_id =
                                      wearerEmploymentID
                                      AND x.product_link_id = productID
                                      AND x.sizedefinition_link_id =
                                      sizeDefinitionID)
                             ELSE
                              (SELECT min(sqitemincirculation_id)
                                 FROM sqitemincirculation x
                                WHERE x.statusquoheader_id = sqHeaderID
                                      AND x.distributionpoint_link_id =
                                      distributionPointID
                                      AND x.weareremployment_link_id =
                                      wearerEmploymentID
                                      AND x.product_link_id = productID
                                      AND x.sizedefinition_link_id =
                                      sizeDefinitionID
                                      AND x.finishingmethod_id =
                                      finishingMethodID)
                           END) END), -1)
        INTO sqItemInCirculationID
        FROM dual        ;
    EXCEPTION
      WHEN no_data_found THEN
        sqItemInCirculationID := -1;
    END;

    RETURN sqItemInCirculationID;

  END getSqItemInCircOnUiRentForStmt;

  PROCEDURE createOrStoreOnSqForUiRent(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                       customerID            customer.customer_id%TYPE,
                                       departmentID          department.department_id%TYPE,
                                       distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                       productID             product.product_id%TYPE,
                                       sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                       finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                       wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                       sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                       noOfUniqueItems       INT,
                                       inactivePLPC          INT,
                                       sizeStarID            sizedefinition.sizedefinition_id%TYPE) --
   IS

    --declaration of variables
    vQtyRentedDepositStock INT;
    plpcID                 productlistpercustomer.productlistpercustomer_id%TYPE := -1;
    billingMethod          productlistpercustomer.billingMethod%TYPE := -1;
    seasonTypeID           seasontype.seasontype_id%TYPE := -1;
    depositStockAllowed    productlistpercustomer.depositStockAllowed%TYPE;
    percAfterDepreciation  productlistpercustomer.percentageafterdepreciation%TYPE := 0;
    emblemTemplateID       emblemtemplate.emblemtemplate_id%TYPE;
    isActive               BOOLEAN := FALSE;
    found_plpc             BOOLEAN;
    typeOfMerchandise      product.typeofmerchandise%TYPE;
    inventorybillingtype   productlistpercustomer.inventorybillingtype%TYPE := -1;
    seasonalRent            productlistpercustomer.seasonalrent%TYPE := 0;
  BEGIN

    --  compute qtyrenteddepositstock
    vQtyRentedDepositStock := noOfUniqueItems;

    -- search for SQItemInCirculationIDOnUniqueItemForRent
    IF (sqItemInCirculationID = -1 AND wearerEmploymentID > 0) THEN
      BEGIN
        SELECT sq.sqitemincirculation_id
          INTO sqItemInCirculationID
          FROM sqitemincirculation sq
         WHERE sq.statusquoheader_id = sqHeaderID
               AND sq.distributionpoint_link_id = distributionPointID
               AND sq.weareremployment_link_id = wearerEmploymentID
               AND sq.product_link_id = productID
               AND sq.sizedefinition_link_id = sizeDefinitionID
               AND sq.finishingmethod_id = finishingMethodID;
      EXCEPTION
        WHEN no_data_found THEN
          sqItemInCirculationID := -1;
      END;
    END IF;

    --insert / update
    insertValuesIntoSqItemInCirc(sqItemInCirculationID,
                                 sqHeaderID,
                                 customerID,
                                 departmentID,
                                 distributionPointID,
                                 wearerEmploymentID,
                                 productID,
                                 sizeDefinitionID,
                                 finishingMethodID,
                                 0, --maxinv
                                 0, --qtytotalcircinv
                                 0, --changesPerWeek,
                                 0, --maxFreeOfCharge,
                                 0, --qtyfree
                                 0, --cogcircin
                                 0, --qtyfreeseason
                                 0, --beyondlifetimecirc
                                 0, --qtyrentedperpiece
                                 0, --qtyrentother
                                 0, --qtycustrentstop
                                 0, --qtyrentstop
                                 NULL, -- emblemTemplateID,
                                 0, --qtyrentemblem
                                 0, --qtyreplacement
                                 0, --qtyassigned
                                 vQtyRentedDepositStock, --qtyrenteddepositstock
                                 0, --qtysubstitute
                                 'N', --chargeafterdepreciation
                                 0, -- QtyRentedPerPiecePool
                                 0, -- QtyRentedReturnRent
                                 0, -- QtyRentedMinRent
                                 0);

    --find plpc for statusQuo.Date
BEGIN
    found_plpc := getPLPC(customerID,
                          productID,
                          sizeDefinitionID,
                          finishingMethodID,
                          sizeStarID,
                          inactivePLPC,
                          0,
                          billingMethod,
                          seasontypeid,
                          emblemTemplateID,
                          depositStockAllowed,
                          percafterdepreciation,
                          plpcID,
                          isActive,
                          inventoryBillingType, seasonalRent);
EXCEPTION
WHEN OTHERS THEN
/*write_log('No active PLPC was found for customerID ' || customerID ||
                    ' productID ' || productID || ' sizeDefinitionID ' ||
                    sizeDefinitionID || ' finishingMethodID ' || finishingMethodID || ' in date ' ||
                    sq_date,
                    3);*/
                    commit;
END;
    --update plpc
    BEGIN
      SELECT pro.typeofmerchandise
        INTO typeOfMerchandise
        FROM product pro
       WHERE pro.product_id = productID;
    EXCEPTION
      WHEN no_data_found THEN
        typeOfMerchandise := -1;
    END;

    IF (typeOfMerchandise = TYPEOFMERCHANDISE_Garment) THEN
      updatePLPC(plpcID,
                 0, --qtytotalcircinv
                 0, --qtyrentedperpiece
                 0, --qtyrentother
                 vQtyRentedDepositStock);
    END IF;
  END createOrStoreOnSqForUiRent;

  FUNCTION getStayID(vStayType stay.staytype %TYPE,
                     vStayNr   stay.staynr%TYPE) RETURN stay.stay_id%TYPE IS

    --declaration of variables
    vStayID stay.stay_id%TYPE;

  BEGIN

    BEGIN
      SELECT st.stay_id
        INTO vStayID
        FROM stay st
       WHERE st.staytype = vStayType
             AND st.staynr = vStayNr;
    EXCEPTION
      WHEN no_data_found THEN
        vStayID := -1;
    END;

    RETURN vStayID;

  END;

  PROCEDURE createOrStoreOnSQforUniqueItem(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                           inactivePLPC          INT,
                                           customerID            customer.customer_id%TYPE,
                                           departmentID          department.department_id%TYPE,
                                           distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                           productID             product.product_id%TYPE,
                                           sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                           finishingMethodID     IN OUT finishingmethod.finishingmethod_id%TYPE,
                                           wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                           emblemTemplateID      IN OUT emblemtemplate.emblemtemplate_id%TYPE,
                                           sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                                           invoiceReplacements   INT,
                                           invoiceSubstitutes    INT,
                                           sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                           sqOnWearerLevel       INT,
                                           qtyTotal              IN OUT sqitemincirculation.totalcircinv%TYPE, --countNUI
                                           qtyCOG                IN OUT sqitemincirculation.cogcircinv%TYPE, --countCustomerOwnedUI
                                           qtyAssigned           IN OUT sqitemincirculation.qtyassigned%TYPE, --countQtyAssigned
                                           qtyFree               IN OUT sqitemincirculation.freeissuedcircinv%TYPE, --countExtraGarmentUI
                                           qtyCustRentStop       IN OUT sqitemincirculation.qtycustomerrentstop%TYPE, --countCustomerRentStop
                                           qtyRentStop           IN OUT sqitemincirculation.qtyrentstop%TYPE, --countWearerInvRentStop
                                           qtyFreeSeason         IN OUT sqitemincirculation.freeseasoncircinv%TYPE, --countQtyFreeSeason
                                           qtyReplacement        IN OUT sqitemincirculation.qtyreplacement%TYPE, --countQtyReplacement
                                           qtySubstitute         IN OUT sqitemincirculation.qtysubstitute%TYPE, --countQtySubstitute
                                           qtyDepositStock       IN OUT sqitemincirculation.qtyrenteddepositstock%TYPE, -- countQtyRentDepositStock
                                           qtyBeyondLifeTime     IN OUT sqitemincirculation.beyondlifetimecirc%TYPE, --countFullRentUI
                                           qtyLease              IN OUT sqitemincirculation.qtyrentedother%TYPE, --countQtyLease
                                           qtyFullyAmortized     IN OUT sqitemincirculation.qtyfullyamortized%TYPE, --countQtyFullyAmortized
                                           qtyRentedPerPiecePool IN OUT sqitemincirculation.qtyrentedperpiecepool%TYPE --qtyRentedPerPiecePool
                                           ) IS

    qtyTotalCircInv     sqitemincirculation.totalcircinv%TYPE := qtyTotal; --countNUI
    qtyReplacement_used sqitemincirculation.qtyreplacement%TYPE := 0; --countQtyReplacement
    qtySubstitute_used  sqitemincirculation.qtysubstitute%TYPE := 0; --countQtySubstitute

    foundPLPC           BOOLEAN;
    billingMehod        productlistpercustomer.billingmethod%TYPE := -1;
    seasonTypeID        seasontype.seasontype_id%TYPE := -1;
    depositStockAllowed productlistpercustomer.depositstockallowed%TYPE := 'N';
    percAfterDeprec     productlistpercustomer.percentageafterdepreciation%TYPE := 0;
    plpcID              productlistpercustomer.productlistpercustomer_id%TYPE := -1;
    isActive            BOOLEAN := FALSE;
    qtyRentedPerPiece   sqitemincirculation.qtyrentedperpiece%TYPE := 0;
    qtyRentOther        sqitemincirculation.qtyrentedother%TYPE := 0;
    qtyRentEmblem       sqitemincirculation.qtyrentemblem%TYPE := 0;

    chargeAfterDepreciation VARCHAR2(1);
    typeOfMerchandise      product.typeofmerchandise%TYPE;
    inventorybillingtype   productlistpercustomer.inventorybillingtype%TYPE := -1;
    maxinv                 wearerinventory.maxinventory%TYPE := 0;
    seasonalRent           productlistpercustomer.seasonalrent%TYPE := 0;
    diff INT := 0;

  BEGIN

    --calculate SUM fields

    IF (finishingMethodID = -1) THEN
      finishingMethodID := getFinMethodFromWearerInv(distributionPointID,
                                                     productID,
                                                     sizeDefinitionID,
                                                     wearerEmploymentID,
                                                     sqOnWearerLevel);
    END IF;

    --find plpc for statusQuo.Date
BEGIN
    foundPLPC := getplpc(customerID,
                         productID,
                         sizeDefinitionID,
                         finishingMethodID,
                         sizestarID,
                         inactivePLPC,
                         sqonwearerlevel,
                         billingMehod,
                         seasonTypeID,
                         emblemTemplateID,
                         depositStockAllowed,
                         percAfterDeprec,
                         plpcID,
                         isActive,
                         inventoryBillingType, seasonalRent);
EXCEPTION
WHEN OTHERS THEN
/*write_log('No active PLPC was found for customerID ' || customerID ||
                    ' productID ' || productID || ' sizeDefinitionID ' ||
                    sizeDefinitionID || ' finishingMethodID ' || finishingMethodID || ' in date ' ||
                    sq_date,
                    3);*/
                    commit;
END;

    --checks
    -- IF (foundPLPC) THEN --LCO

    IF (isActive = FALSE AND inactivePLPC = 2) THEN
      qtyTotalCircInv := 0;
    END IF;

    qtyRentOther := qtyLease;
    diff         := qtyTotal - qtyAssigned - qtyCOG - qtyLease - qtyFree;

    IF (qtyCustRentStop > 0) THEN
      qtyCustRentStop   := diff;
      qtyRentStop       := 0;
      qtyFreeSeason     := 0;
      qtyBeyondLifeTime := 0;
      qtyFullyAmortized := 0;
      qtyReplacement    := 0;
      qtySubstitute     := 0;
      qtyRentedPerPiece := 0;
      qtyRentEmblem     := 0;
      qtyRentedPerPiecePool     := 0;
    ELSE
      qtyCustRentStop := 0;

      IF (invoicereplacements = 1) THEN
        qtyReplacement_used := 0;
      END IF;

      IF (invoicesubstitutes = 1) THEN
        qtySubstitute_used := 0;
      END IF;

      IF (seasontypeID <> -1 and seasonalRent = 2) THEN
         qtyFreeSeason := 0;
      END IF;

      diff := diff - qtyRentStop - qtyFreeSeason - qtyFullyAmortized;


      IF ((foundPLPC AND billingMehod = bill_circulating_inventory OR
          (billingMehod = BILL_RENT_BASED_MINIMUM AND underuseMethodSS = 4))
         AND (isActive OR inactiveplpc = 0)) THEN

        diff := diff - qtyBeyondLifeTime;
        IF (depositStockAllowed = 'Y') THEN
          qtyRentedPerPiece := diff - qtyReplacement_used -
                               qtySubstitute_used - qtyDepositStock;
          IF (inventoryBillingType = INVENTORY_BILLING_TYPE_MAX AND bm3MaxInvReduceDepositSS = 1
                    AND sqItemInCirculationID > -1) THEN
            BEGIN
              SELECT x.maxinv
                INTO maxinv
                FROM sqitemincirculation x
               WHERE x.sqitemincirculation_id = sqItemInCirculationID and x.maxinv >= qtyDepositStock;
            EXCEPTION
              WHEN no_data_found THEN
                maxinv := 0;
            END;
            IF (maxinv > 0) THEN
              maxinv := - qtyDepositStock;
            END IF;
          END IF;
        ELSE
          qtyDepositStock := 0;
          IF (isActive = TRUE OR inactivePLPC = 0) THEN
            qtyRentedPerPiece := diff - qtyReplacement_used -
                                 qtySubstitute_used;
          END IF;
        END IF;

        IF (emblemtemplateID <> -1) THEN
          qtyRentEmblem := diff;
        END IF;
      ELSE
        qtyDepositStock   := 0;
        qtyBeyondLifeTime := 0;
        qtyRentedPerPiece := 0;

        IF (isActive OR inactiveplpc = 1) THEN
          qtyBeyondLifeTime := 0;
          qtyRentOther      := diff;
          IF (emblemtemplateID <> -1) THEN
            qtyRentEmblem := diff;
          END IF;
        END IF;
      END IF;
    END IF;

    chargeAfterDepreciation := 'N';
    IF (billingMehod = BILL_CIRCULATING_INVENTORY AND PercAfterDeprec > 0) THEN
      chargeAfterDepreciation := 'Y';
    END IF;
    IF (qtyRentedPerPiece < 0) THEN
      qtyRentedPerPiece := 0;
    END IF;
    IF (qtyRentedPerPiecePool < 0) THEN
      qtyRentedPerPiecePool := 0;
    END IF;
    IF (qtyRentOther < 0) THEN
      qtyRentOther := 0;
    END IF;
    IF (emblemTemplateID = -1) THEN
       emblemTemplateID := null;
    END IF;

    --insertion/update
    insertvaluesintosqitemincirc(sqItemInCirculationID,
                                 sqHeaderID,
                                 customerID,
                                 departmentID,
                                 distributionpointID,
                                 weareremploymentID,
                                 productID,
                                 sizedefinitionID,
                                 finishingmethodID,
                                 maxinv,
                                 qtyTotalCircInv,
                                 0,
                                 0,
                                 qtyFree,
                                 qtyCOG, --COGCIRCINV
                                 qtyFreeSeason,
                                 qtyBeyondLifeTime, --BEYONDLIFETIMECIRC
                                 qtyRentedPerPiece,
                                 qtyRentOther,
                                 qtyCustRentStop,
                                 qtyRentStop,
                                 emblemTemplateID,
                                 qtyRentEmblem,
                                 qtyReplacement,
                                 qtyAssigned,
                                 qtyDepositStock, --QTYRENTEDDEPOSITSTOCK
                                 qtySubstitute, --QTYSUBSTITUTE
                                 chargeAfterDepreciation,
                                 qtyRentedPerPiecePool, -- QtyRentedPerPiecePool
                                 0, -- QtyRentedReturnRent
                                 0, -- QtyRentedMinRent
                                 qtyFullyAmortized);

    --update plpc
    SELECT pr.typeofmerchandise
      INTO typeOfMerchandise
      FROM product pr
     WHERE pr.product_id = productid;

    IF (typeofMerchandise = TYPEOFMERCHANDISE_Garment) THEN
      updatePLPC(plpcID,
                 qtyTotalCircInv,
                 qtyRentedPerPiece,
                 qtyRentOther,
                 qtyDepositStock);
    END IF;

    --  END IF;

  END;

  FUNCTION getFinMethodFromWearerInv(distributionPointID distributionpoint.distributionpoint_id%TYPE,
                                     productID           product.product_id%TYPE,
                                     sizeDefinitionID    sizedefinition.sizedefinition_id%TYPE,
                                     wearerEmploymentID  weareremployment.weareremployment_id%TYPE,
                                     sqonwearerlevel     INT)
    RETURN finishingmethod.finishingmethod_id%TYPE IS

    --declaration of variables
    finMethID finishingmethod.finishingmethod_id%TYPE;

  BEGIN

    IF (sqonwearerlevel = 1) THEN
      BEGIN
        SELECT w.finishingmethod_id
          INTO finMethID
          FROM wearerinventory w
         WHERE w.weareremployment_id = wearerEmploymentID
               AND w.product_id = productID
               AND w.sizedefinition_id = sizeDefinitionID;
      EXCEPTION
        WHEN no_data_found THEN
          finMethID := -1;
      END;
    ELSE
      BEGIN
        SELECT w.finishingmethod_id
          INTO finMethID
          FROM wearerinventory w, weareremployment e
         WHERE w.weareremployment_id = e.weareremployment_id
               AND e.distributionpoint_id = distributionPointID
               AND w.product_id = productID
               AND w.sizedefinition_id = sizeDefinitionID;
      EXCEPTION
        WHEN no_data_found THEN
          finMethID := -1;
      END;
    END IF;

    RETURN finMethID;
  END; --getFinishingMethodFromWearerInventory

  FUNCTION getWearerEmploymentLinkID(wearerEmploymentID weareremployment.weareremployment_id%TYPE)
    RETURN weareremployment.weareremployment_id%TYPE IS
    v_WearerEmploymentLinkID weareremployment.weareremployment_id%TYPE;
  BEGIN
    IF wearerEmploymentID = -1 THEN
      v_WearerEmploymentLinkID := NULL;
    ELSE
      v_WearerEmploymentLinkID := wearerEmploymentID;
    END IF;
    RETURN v_WearerEmploymentLinkID;
  END getWearerEmploymentLinkID;

----- mba

  PROCEDURE createOrStoreOnSqForPoolItems(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                       customerID            customer.customer_id%TYPE,
                                       departmentID          department.department_id%TYPE,
                                       distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                       wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                       productID             product.product_id%TYPE,
                                       sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                       finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                       sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                       noOfUniqueItems       INT,
                                       qtyRentStop           INT) --
   IS

    --declaration of variables
    vQtyRentedPerPiecePool INT;
    qtycustrentstop INT;
  BEGIN
    qtycustrentstop := 0; --countCustomerRentStop
    --  compute qtyrentedperPiecePool
    vQtyRentedPerPiecePool := noOfUniqueItems;
    BEGIN
      SELECT COUNT(t.temporarygarmentrentstop_id)
        INTO qtycustrentstop
        FROM temporarygarmentrentstop t
       WHERE t.customer_id = customerID
             AND f_isinyearweeklifespan(startyear,
                                        startweek,
                                        sq_year,
                                        sq_week,
                                        endyear,
                                        endweek) = 1;
    EXCEPTION
      WHEN no_data_found THEN
        qtycustrentstop := 0;
    END;

    IF (qtycustrentstop > 0) THEN
      qtycustrentstop   := noOfUniqueItems;
      vQtyRentedPerPiecePool := 0;
    END IF;
    -- search for SQItemInCirculationIDForPoolItems
    IF (sqItemInCirculationID = -1) THEN
      BEGIN
        SELECT sq.sqitemincirculation_id
          INTO sqItemInCirculationID
          FROM sqitemincirculation sq
         WHERE sq.statusquoheader_id = sqHeaderID
               AND sq.distributionpoint_link_id = distributionPointID
               AND coalesce(sq.weareremployment_link_id , -1) = coalesce(wearerEmploymentID, -1)
               AND sq.product_link_id = productID
               AND sq.sizedefinition_link_id = sizeDefinitionID
               AND sq.finishingmethod_id = finishingMethodID;
      EXCEPTION
        WHEN no_data_found THEN
          sqItemInCirculationID := -1;
      END;
    END IF;

    --insert / update
    insertValuesIntoSqItemInCirc(sqItemInCirculationID,
                                 sqHeaderID,
                                 customerID,
                                 departmentID,
                                 distributionPointID,
                                 wearerEmploymentID, --weareremployment
                                 productID,
                                 sizeDefinitionID,
                                 finishingMethodID,
                                 0, --maxinv
                                 0, --qtytotalcircinv
                                 0, --changesPerWeek,
                                 0, --maxFreeOfCharge,
                                 0, --qtyfree
                                 0, --cogcircin
                                 0, --qtyfreeseason
                                 0, --beyondlifetimecirc
                                 0, --qtyrentedperpiece
                                 0, --qtyrentother
                                 qtycustrentstop, --qtycustrentstop
                                 qtyRentStop, --qtyrentstop
                                 null, -- emblemTemplateID,
                                 0, --qtyrentemblem
                                 0, --qtyreplacement
                                 0, --qtyassigned
                                 0, --qtyrenteddepositstock
                                 0, --qtysubstitute
                                 'N', --chargeafterdepreciation
                                 vQtyRentedPerPiecePool - qtyRentStop,
                                 0, -- QtyRentedReturnRent
                                 0, -- QtyRentedMinRent
                                 0);

  END createOrStoreOnSqForPoolItems;

  PROCEDURE createSqItemInCircForPoolItems(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                           processBuID                  businessunit.businessunit_id%TYPE,
                                           invoiceBuID                  businessunit.businessunit_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           checkInvoiceBu               INT) IS

    -- declation of variables
    sqItemInCirculationID sqitemincirculation.sqitemincirculation_id%TYPE := -1;

    --declaration of cursors
    CURSOR stmtC IS
SELECT dp.department_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       ui.distributionpoint_id,
       dep.customer_id,
       COUNT(ui.uniqueitem_id) noOfUniqueItems,
       sum(coalesce( (select 1 from standarddeliveryline y, productlistperdistribpoint plpdp
                 where y.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id and
                   plpdp.distributionpoint_id = ui.distributionpoint_id and
                 --  y.productlistpercustomer_id = plpc.productlistpercustomer_id and
                   plpdp.product_id = p.product_id and
                   plpdp.sizedefinition_id = sd.sizedefinition_id and
                   plpdp.finishingmethod_id = ui.finishingmethod_id and
                       exists (select tds.standarddeliveryline_id from temporarydeliverystop tds
                               where tds.standarddeliveryline_id = y.standarddeliveryline_id and
                                     ((tds.tempstoprentforweek = 'Y' and
                                       (sq_week between tds.temporarystopstartweek and tds.temporarystopendweek) or
                                       (tds.temporarystopstartweek > tds.temporarystopendweek and ((sq_week <= tds.temporarystopendweek and 0 < sq_week ) or (temporarystopstartweek <= sq_week and sq_week <= 53)))) or
                                      (tds.tempstoprentforyearweek = 'Y' and
                                       f_isinyearweeklifespan(tds.temporarystopstartyear, tds.tempstopstartweekforyear,
                                                               sq_year, sq_week,
                                                               tds.temporarystopendyear, tds.tempstopendweekforyear) = 1) or
                                       (tds.tempstoprentfordate = 'Y' and (tds.startdate<= sysdate and sysdate <= tds.enddate)) ))
                       )
           , 0))  rentStop
  FROM uniqueitem         ui,
       department         dep,
       distributionpoint  dp,
       product            p,
       sizedefinition     sd
  WHERE ( UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '20') OR UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '30'))
   AND ui.stay_id = PACK_SQITEMSINCIRCULATION.GETSTAYID(STAY_TYPE_GARMENT, 20)
   AND ui.distributionpoint_id = dp.distributionpoint_id
   AND dp.department_id = dep.department_id
   AND p.product_id = nvl(ui.substituteforproduct_id, ui.product_id)
   AND exists (select 1 from productgroup pg, customeractivities ca
             where abs(ca.activity_id) = abs(pg.activity_id)
             AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
             AND pg.productgroup_id = p.productgroup_id
             AND ca.customer_id = dep.customer_id
             AND ca.processbusinessunit_id = processBuID)
   AND not exists (select 1 from uniqueitemloss uil where uil.uniqueitem_link_id = ui.uniqueitem_id and
                (uil.lossstatus = 0 or uil.lossstatus = 1))
   AND sd.sizedefinition_id = nvl(ui.substituteforsizedefinition_id, ui.sizedefinition_id)
   AND trunc(ui.lastoutscandate) < trunc(sysdate) -
       7 *
       (SELECT plpc.chargerentnoreturnpoolafterwks
          FROM productlistpercustomer plpc
         WHERE plpc.productlistpercustomer_id =
               (SELECT coalesce((SELECT plpc1.productlistpercustomer_id
                                  FROM productlistpercustomer plpc1
                                 WHERE plpc1.billingmethod = 3
                                   AND plpc1.inventorybillingtype = 4
                                   AND plpc1.chargerentnoreturnpoolafterwks > 0
                                   AND plpc1.chargerentnotreturned <> 1
                                   AND plpc1.codeuniquely = 'Y'
                                   AND p.uniquepoolitem ='Y'
                                   AND plpc1.customer_id = dep.customer_id
                                   AND plpc1.product_id = p.product_id
                                   AND plpc1.sizedefinition_id = sd.sizedefinition_id
                                   AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                   AND plpc1.dateactive =
                                       (select max(plpc2.dateactive)
                                        from productlistpercustomer plpc2
                                       where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                         and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                         and plpc2.product_id = plpc1.product_id
                                         and plpc2.customer_id = plpc1.customer_id
                                         and plpc2.dateactive < sysdate
                                         and plpc2.billingmethod = 3
                                         and plpc2.inventorybillingtype = 4
                                         and plpc2.chargerentnoreturnpoolafterwks > 0
                                         AND plpc2.chargerentnotreturned <> 1
                                         and plpc2.codeuniquely = 'Y')),
                                (CASE sd.sizedefinition_id
                                  WHEN sizeStarID THEN
                                   -1
                                  ELSE
                                   coalesce((SELECT plpc1.productlistpercustomer_id
                                              FROM productlistpercustomer plpc1
                                             WHERE plpc1.billingmethod = 3
                                               AND plpc1.inventorybillingtype = 4
                                               AND plpc1.chargerentnoreturnpoolafterwks > 0
                                               AND plpc1.chargerentnotreturned <> 1
                                               AND plpc1.codeuniquely = 'Y'
                                               AND p.uniquepoolitem = 'Y'
                                               AND plpc1.customer_id = dep.customer_id
                                               AND plpc1.product_id = p.product_id
                                               AND plpc1.sizedefinition_id =
                                                   sizeStarID
                                               AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                               AND plpc1.dateactive =
                                               (select max(plpc2.dateactive)
                                                from productlistpercustomer plpc2
                                               where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                 and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                 and plpc2.product_id = plpc1.product_id
                                                 and plpc2.customer_id = plpc1.customer_id
                                                 and plpc2.dateactive < sysdate
                                                 and plpc2.billingmethod = 3
                                                 and plpc2.inventorybillingtype = 4
                                                 and plpc2.chargerentnoreturnpoolafterwks > 0
                                                 AND plpc2.chargerentnotreturned <> 1
                                                 and plpc2.codeuniquely = 'Y')),
                                            -1)
                                END))
                  FROM dual))
 GROUP BY dp.department_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       ui.distributionpoint_id,
       dep.customer_id;

    CURSOR stmtC_checkinvoicedbu IS
SELECT dp.department_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       ui.distributionpoint_id,
       dep.customer_id,
       COUNT(ui.uniqueitem_id) noOfUniqueItems,
       sum(coalesce( (select 1 from standarddeliveryline y, productlistperdistribpoint plpdp
                 where y.productlistperdistribpoint_id = plpdp.productlistperdistribpoint_id and
                   plpdp.distributionpoint_id = ui.distributionpoint_id and
                 --  y.productlistpercustomer_id = plpc.productlistpercustomer_id and
                   plpdp.product_id = p.product_id and
                   plpdp.sizedefinition_id = sd.sizedefinition_id and
                   plpdp.finishingmethod_id = ui.finishingmethod_id and
                       exists (select tds.standarddeliveryline_id from temporarydeliverystop tds
                               where tds.standarddeliveryline_id = y.standarddeliveryline_id and
                                     ((tds.tempstoprentforweek = 'Y' and
                                       (sq_week between tds.temporarystopstartweek and tds.temporarystopendweek) or
                                       (tds.temporarystopstartweek > tds.temporarystopendweek and ((sq_week <= tds.temporarystopendweek and 0 < sq_week ) or (temporarystopstartweek <= sq_week and sq_week <= 53)))) or
                                      (tds.tempstoprentforyearweek = 'Y' and
                                       f_isinyearweeklifespan(tds.temporarystopstartyear, tds.tempstopstartweekforyear,
                                                               sq_year, sq_week,
                                                               tds.temporarystopendyear, tds.tempstopendweekforyear) = 1)or
                                       (tds.tempstoprentfordate = 'Y' and (tds.startdate<= sysdate and sysdate <= tds.enddate)) ))
                  )
           , 0)) rentStop
  FROM uniqueitem          ui,
       department         dep,
       distributionpoint  dp,
       product             p,
       sizedefinition      sd
  WHERE ( UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '20') OR UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '30'))
   AND ui.stay_id = PACK_SQITEMSINCIRCULATION.GETSTAYID(STAY_TYPE_GARMENT, 20)
   AND ui.distributionpoint_id = dp.distributionpoint_id
   AND dp.department_id = dep.department_id
   AND p.product_id = nvl(ui.substituteforproduct_id, ui.product_id)
   AND exists (select 1 from productgroup pg, customeractivities ca
             where abs(ca.activity_id) = abs(pg.activity_id)
             AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
             AND pg.productgroup_id = p.productgroup_id
             AND ca.customer_id = dep.customer_id
             AND ca.processbusinessunit_id = processBuID
             AND ca.invoicebusinessunit_id = invoiceBuID)
   AND sd.sizedefinition_id = nvl(ui.substituteforsizedefinition_id, ui.sizedefinition_id)
   AND not exists (select 1 from uniqueitemloss uil where uil.uniqueitem_link_id = ui.uniqueitem_id and
             (uil.lossstatus = 0 or uil.lossstatus = 1))
   AND trunc(ui.lastoutscandate) < trunc(sysdate) -
       7 *
       (SELECT plpc.chargerentnoreturnpoolafterwks
          FROM productlistpercustomer plpc
         WHERE plpc.productlistpercustomer_id =
               (SELECT coalesce((SELECT plpc1.productlistpercustomer_id
                                  FROM productlistpercustomer plpc1
                                 WHERE plpc1.billingmethod = 3
                                   AND plpc1.inventorybillingtype = 4
                                   AND plpc1.chargerentnoreturnpoolafterwks > 0
                                   AND plpc1.chargerentnotreturned <> 1
                                   AND plpc1.codeuniquely = 'Y'
                                   AND p.uniquepoolitem = 'Y'
                                   AND plpc1.customer_id = dep.customer_id
                                   AND plpc1.product_id = p.product_id
                                   AND plpc1.sizedefinition_id = sd.sizedefinition_id
                                   AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                   AND plpc1.dateactive =
                                   (select max(plpc2.dateactive)
                                    from productlistpercustomer plpc2
                                   where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                     and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                     and plpc2.product_id = plpc1.product_id
                                     and plpc2.customer_id = plpc1.customer_id
                                     and plpc2.dateactive < sysdate
                                     and plpc2.billingmethod = 3
                                     and plpc2.inventorybillingtype = 4
                                     and plpc2.chargerentnoreturnpoolafterwks > 0
                                     AND plpc2.chargerentnotreturned <> 1
                                     and plpc2.codeuniquely = 'Y')),
                                (CASE sd.sizedefinition_id
                                  WHEN sizeStarID THEN
                                   -1
                                  ELSE
                                   coalesce((SELECT plpc1.productlistpercustomer_id
                                              FROM productlistpercustomer plpc1
                                             WHERE plpc1.billingmethod = 3
                                               AND plpc1.inventorybillingtype = 4
                                               AND plpc1.chargerentnoreturnpoolafterwks > 0
                                               AND plpc1.chargerentnotreturned <> 1
                                               AND plpc1.codeuniquely = 'Y'
                                               AND p.uniquepoolitem = 'Y'
                                               AND plpc1.customer_id = dep.customer_id
                                               AND plpc1.product_id = p.product_id
                                               AND plpc1.sizedefinition_id =
                                                   sizeStarID
                                               AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                               AND plpc1.dateactive =
                                                   (select max(plpc2.dateactive)
                                                    from productlistpercustomer plpc2
                                                   where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                     and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                     and plpc2.product_id = plpc1.product_id
                                                     and plpc2.customer_id = plpc1.customer_id
                                                     and plpc2.dateactive < sysdate
                                                     and plpc2.billingmethod = 3
                                                     and plpc2.inventorybillingtype = 4
                                                     and plpc2.chargerentnoreturnpoolafterwks > 0
                                                     AND plpc2.chargerentnotreturned <> 1
                                                     and plpc2.codeuniquely = 'Y')),
                                            -1)
                                END))
                  FROM dual))
 GROUP BY dp.department_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       ui.distributionpoint_id,
       dep.customer_id;

  BEGIN

    IF (checkInvoiceBu = 1) THEN
      FOR rs_checkBu IN stmtC_checkinvoicedbu LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs_checkBu.distributionpoint_id,
                                                null,
                                                rs_checkBu.product_id,
                                                rs_checkBu.sizedefinition_id,
                                                rs_checkBu.finishingmethod_id)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForPoolItems(sqHeaderID,
                                   rs_checkBu.customer_id,
                                   rs_checkBu.department_id,
                                   rs_checkBu.distributionpoint_id,
                                   null,
                                   rs_checkBu.product_id,
                                   rs_checkBu.sizedefinition_id,
                                   rs_checkBu.finishingmethod_id,
                                   sqItemInCirculationID,
                                   rs_checkBu.noOfUniqueItems,
                                   rs_checkBu.rentStop);
      END LOOP;
    ELSE
      FOR rs IN stmtC LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs.distributionpoint_id,
                                                null,
                                                rs.product_id,
                                                rs.sizedefinition_id,
                                                rs.finishingmethod_id)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForPoolItems(sqHeaderID,
                                   rs.customer_id,
                                   rs.department_id,
                                   rs.distributionpoint_id,
                                   null,
                                   rs.product_id,
                                   rs.sizedefinition_id,
                                   rs.finishingmethod_id,
                                   sqItemInCirculationID,
                                   rs.noOfUniqueItems,
                                   rs.rentStop);
      END LOOP;
    END IF;

  END createSqItemInCircForPoolItems;

  PROCEDURE createSqItemInCircForPoolDummy(sqHeaderID                   statusquoheader.statusquoheader_id%TYPE,
                                               processBuID                  businessunit.businessunit_id%TYPE,
                                               invoiceBuID                  businessunit.businessunit_id%TYPE,
                                               sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                               checkInvoiceBu               INT) IS

    -- declation of variables
    sqItemInCirculationID sqitemincirculation.sqitemincirculation_id%TYPE := -1;

    --declaration of cursors
    CURSOR stmtC IS
SELECT dp.department_id, we.weareremployment_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       we.distributionpoint_id,
       dep.customer_id,
       COUNT(ui.uniqueitem_id) noOfUniqueItems
  FROM uniqueitem         ui, uniqueitemnonpool uinp, weareremployment we,
       department         dep,
       distributionpoint  dp,
       product            p,
       sizedefinition     sd
 WHERE ( UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '20') OR UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '30'))
   AND ui.stay_id = PACK_SQITEMSINCIRCULATION.GETSTAYID(STAY_TYPE_GARMENT, 20)
   AND ui.poolitem = 2
   AND ui.uniqueitem_id=uinp.uniqueitem_id
   AND uinp.weareremployment_id=we.weareremployment_id
   AND exists (select 1 from wearer w where we.wearer_id=w.wearer_id AND w.dummyforpool='Y' AND w.customer_id=dep.customer_id)
   AND we.distributionpoint_id = dp.distributionpoint_id
   AND dp.department_id = dep.department_id
   AND p.product_id = nvl(ui.substituteforproduct_id, ui.product_id)
   AND exists (select 1 from productgroup pg, customeractivities ca
               where abs(ca.activity_id) = abs(pg.activity_id)
               AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
               AND pg.productgroup_id = p.productgroup_id
               AND ca.customer_id = dep.customer_id
               AND ca.processbusinessunit_id = processBuID)
   AND sd.sizedefinition_id = nvl(ui.substituteforsizedefinition_id, ui.sizedefinition_id)
   AND trunc(ui.lastoutscandate) < trunc(sysdate) -
       7 *
       (SELECT plpc.chargerentnoreturnpoolafterwks
          FROM productlistpercustomer plpc
         WHERE plpc.productlistpercustomer_id =
               (SELECT coalesce((SELECT plpc1.productlistpercustomer_id
                                  FROM productlistpercustomer plpc1
                                 WHERE plpc1.billingmethod = 3
                                   AND plpc1.inventorybillingtype = 4
                                   AND plpc1.chargerentnotreturned <> 1
                                   AND plpc1.chargerentnoreturnpoolafterwks > 0
                                   AND plpc1.codeuniquely = 'Y'
                                   AND p.uniquepoolitem = 'Y'
                                   AND plpc1.customer_id = dep.customer_id
                                   AND plpc1.product_id = p.product_id
                                   AND plpc1.sizedefinition_id = sd.sizedefinition_id
                                   AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                   AND plpc1.dateactive =
                                       (select max(plpc2.dateactive)
                                        from productlistpercustomer plpc2
                                       where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                         and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                         and plpc2.product_id = plpc1.product_id
                                         and plpc2.customer_id = plpc1.customer_id
                                         and plpc2.dateactive < sysdate
                                         and plpc2.billingmethod = 3
                                         and plpc2.inventorybillingtype = 4
                                         and plpc2.chargerentnoreturnpoolafterwks > 0
                                         AND plpc2.chargerentnotreturned <> 1
                                         and plpc2.codeuniquely = 'Y')),
                                (CASE sd.sizedefinition_id
                                  WHEN sizeStarID THEN
                                   -1
                                  ELSE
                                   coalesce((SELECT plpc1.productlistpercustomer_id
                                              FROM productlistpercustomer plpc1
                                             WHERE plpc1.billingmethod = 3
                                               AND plpc1.inventorybillingtype = 4
                                               AND plpc1.chargerentnoreturnpoolafterwks > 0
                                               AND plpc1.chargerentnotreturned <> 1
                                               AND plpc1.codeuniquely = 'Y'
                                               AND p.uniquepoolitem = 'Y'
                                               AND plpc1.customer_id = dep.customer_id
                                               AND plpc1.product_id = p.product_id
                                               AND plpc1.sizedefinition_id =
                                                   sizeStarID
                                               AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                               AND plpc1.dateactive =
                                               (select max(plpc2.dateactive)
                                                from productlistpercustomer plpc2
                                               where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                 and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                 and plpc2.product_id = plpc1.product_id
                                                 and plpc2.customer_id = plpc1.customer_id
                                                 and plpc2.dateactive < sysdate
                                                 and plpc2.billingmethod = 3
                                                 and plpc2.inventorybillingtype = 4
                                                 and plpc2.chargerentnoreturnpoolafterwks > 0
                                                 AND plpc2.chargerentnotreturned <> 1
                                                 and plpc2.codeuniquely = 'Y')),
                                            -1)
                                END))
                  FROM dual))
 GROUP BY dp.department_id, we.weareremployment_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       we.distributionpoint_id,
       dep.customer_id;

    CURSOR stmtC_checkinvoicedbu IS
SELECT dp.department_id, we.weareremployment_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       we.distributionpoint_id,
       dep.customer_id,
       COUNT(ui.uniqueitem_id) noOfUniqueItems
  FROM uniqueitem          ui, uniqueitemnonpool uinp, weareremployment we,
       department         dep,
       distributionpoint  dp,
       product             p,
       sizedefinition      sd
 WHERE ( UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '20') OR UI.STATUS_ID = PACK_SQITEMSINCIRCULATION.GETSTATUSID('UI', '30'))
   AND ui.stay_id = PACK_SQITEMSINCIRCULATION.GETSTAYID(STAY_TYPE_GARMENT, 20)
   AND ui.poolitem = 2
   AND ui.uniqueitem_id=uinp.uniqueitem_id
   AND uinp.weareremployment_id=we.weareremployment_id
   AND exists (select 1 from wearer w where we.wearer_id=w.wearer_id AND w.dummyforpool='Y' AND w.customer_id=dep.customer_id)
   AND we.distributionpoint_id = dp.distributionpoint_id
   AND dp.department_id = dep.department_id
   AND p.product_id = nvl(ui.substituteforproduct_id, ui.product_id)
   AND exists (select 1 from productgroup pg, customeractivities ca
               where abs(ca.activity_id) = abs(pg.activity_id)
               AND abs(ca.processbusinessunit_id) = abs(ui.businessunit_id)
               AND pg.productgroup_id = p.productgroup_id
               AND ca.customer_id = dep.customer_id
               AND ca.processbusinessunit_id = processBuID
               AND ca.invoicebusinessunit_id = invoiceBuID)
   AND sd.sizedefinition_id = nvl(ui.substituteforsizedefinition_id, ui.sizedefinition_id)
   AND trunc(ui.lastoutscandate) < trunc(sysdate) -
       7 *
       (SELECT plpc.chargerentnoreturnpoolafterwks
          FROM productlistpercustomer plpc
         WHERE plpc.productlistpercustomer_id =
               (SELECT coalesce((SELECT productlistpercustomer_id
                                  FROM productlistpercustomer plpc1
                                 WHERE plpc1.billingmethod = 3
                                   AND plpc1.inventorybillingtype = 4
                                   AND plpc1.chargerentnoreturnpoolafterwks > 0
                                   AND plpc1.chargerentnotreturned <> 1
                                   AND plpc1.codeuniquely = 'Y'
                                   AND p.uniquepoolitem = 'Y'
                                   AND plpc1.customer_id = dep.customer_id
                                   AND plpc1.product_id = p.product_id
                                   AND plpc1.sizedefinition_id = sd.sizedefinition_id
                                   AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                   AND plpc1.dateactive =
                                   (select max(plpc2.dateactive)
                                    from productlistpercustomer plpc2
                                   where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                     and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                     and plpc2.product_id = plpc1.product_id
                                     and plpc2.customer_id = plpc1.customer_id
                                     and plpc2.dateactive < sysdate)),
                                (CASE sd.sizedefinition_id
                                  WHEN sizeStarID THEN
                                   -1
                                  ELSE
                                   coalesce((SELECT plpc1.productlistpercustomer_id
                                              FROM productlistpercustomer plpc1
                                             WHERE plpc1.billingmethod = 3
                                               AND plpc1.inventorybillingtype = 4
                                               AND plpc1.chargerentnoreturnpoolafterwks > 0
                                               AND plpc1.chargerentnotreturned <> 1
                                               AND plpc1.codeuniquely = 'Y'
                                               AND p.uniquepoolitem = 'Y'
                                               AND plpc1.customer_id = dep.customer_id
                                               AND plpc1.product_id = p.product_id
                                               AND plpc1.sizedefinition_id =
                                                   sizeStarID
                                               AND plpc1.finishingmethod_id = ui.finishingmethod_id
                                               AND plpc1.dateactive =
                                                   (select max(plpc2.dateactive)
                                                    from productlistpercustomer plpc2
                                                   where plpc2.finishingmethod_id = plpc1.finishingmethod_id
                                                     and plpc2.sizedefinition_id = plpc1.sizedefinition_id
                                                     and plpc2.product_id = plpc1.product_id
                                                     and plpc2.customer_id = plpc1.customer_id
                                                     and plpc2.dateactive < sysdate)),
                                            -1)
                                END))
                  FROM dual))
 GROUP BY dp.department_id, we.weareremployment_id,
       p.product_id,
       sd.sizedefinition_id,
       ui.finishingmethod_id,
       we.distributionpoint_id,
       dep.customer_id;

  BEGIN

    IF (checkInvoiceBu = 1) THEN
      FOR rs_checkBu IN stmtC_checkinvoicedbu LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs_checkBu.distributionpoint_id,
                                                rs_checkBu.weareremployment_id,
                                                rs_checkBu.product_id,
                                                rs_checkBu.sizedefinition_id,
                                                rs_checkBu.finishingmethod_id)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForPoolItems(sqHeaderID,
                                   rs_checkBu.customer_id,
                                   rs_checkBu.department_id,
                                   rs_checkBu.distributionpoint_id,
                                   rs_checkBu.weareremployment_id,
                                   rs_checkBu.product_id,
                                   rs_checkBu.sizedefinition_id,
                                   rs_checkBu.finishingmethod_id,
                                   sqItemInCirculationID,
                                   rs_checkBu.noOfUniqueItems,
                                   0);
      END LOOP;
    ELSE
      FOR rs IN stmtC LOOP
        BEGIN
          --search if exists an sqitemincircID
          SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                                rs.distributionpoint_id,
                                                rs.weareremployment_id,
                                                rs.product_id,
                                                rs.sizedefinition_id,
                                                rs.finishingmethod_id)
            INTO sqItemInCirculationID
            FROM dual;
        EXCEPTION
          WHEN no_data_found THEN
            sqItemInCirculationID := -1;
        END;

        -- create / store a sqitemincirc
        createOrStoreOnSqForPoolItems(sqHeaderID,
                                   rs.customer_id,
                                   rs.department_id,
                                   rs.distributionpoint_id,
                                   rs.weareremployment_id,
                                   rs.product_id,
                                   rs.sizedefinition_id,
                                   rs.finishingmethod_id,
                                   sqItemInCirculationID,
                                   rs.noOfUniqueItems,
                                   0);
      END LOOP;
    END IF;

  END createSqItemInCircForPoolDummy;

  PROCEDURE createSqItemInCircOnReturnRent(sqHeaderID                  statusquoheader.statusquoheader_id%TYPE,
                                           sizeStarID                   sizedefinition.sizedefinition_id%TYPE,
                                           sqOnWearerLevel              INT,
                                           inactivePLPC                 INT) IS

    -- declation of variables
    sqItemInCirculationID  sqitemincirculation.sqitemincirculation_id%TYPE := -1;
    v_wearerEmploymentID   weareremployment.weareremployment_id%TYPE := -1;
    --declaration of cursors
    CURSOR stmtReturnRent IS
    select count(1) noofuniqueitems,
           uirr.returnrenttype,
           uirr.customer_link_id,
           dp.department_id,
           we.distributionpoint_id,
           uirr.weareremployment_link_id,
           uirr.product_link_id,
           uirr.sizedefinition_link_id,
           uirr.finishingmethod_id
      from uniqueitemreturnrent uirr,
           weareremployment     we,
           distributionpoint    dp,
           department           d
     where uirr.returnrenttype in (1, 2)
       and uirr.rentyear = sq_year
       and uirr.rentweek = sq_week
       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 = sizeStarID)
               and plpc.finishingmethod_id = uirr.finishingmethod_id
               and plpc.dateactive <= sq_date
               and plpc.billingmethod = 3
               and plpc.inventorybillingtype in (1, 2))
       and we.weareremployment_id = uirr.weareremployment_link_id
       and we.distributionpoint_id = dp.distributionpoint_id
       and dp.department_id = d.department_id
       and exists (select sqitemincirculation_id
          from sqitemincirculation  sqi
         where sqi.statusquoheader_id = 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.returnrenttype,
              uirr.customer_link_id,
              dp.department_id,
              we.distributionpoint_id,
              uirr.weareremployment_link_id,
              uirr.product_link_id,
              uirr.sizedefinition_link_id,
              uirr.finishingmethod_id;

  BEGIN

  FOR rs IN stmtReturnRent LOOP
     IF (sqOnWearerLevel = 1) THEN
        v_wearerEmploymentID := rs.weareremployment_link_id;
     ELSE
        v_wearerEmploymentID := -1;
     END IF;

    BEGIN
      --search if exists an sqitemincircID
      SELECT getSqItemInCircOnUiRentForStmt(sqHeaderID,
                                            rs.distributionpoint_id,
                                            v_wearerEmploymentID,
                                            rs.product_link_id,
                                            rs.sizedefinition_link_id,
                                            rs.finishingmethod_id)
        INTO sqItemInCirculationID
        FROM dual;
    EXCEPTION
      WHEN no_data_found THEN
        sqItemInCirculationID := -1;
    END;

    -- create / store a sqitemincirc
    createOrStoreOnSqOnReturnRent(sqHeaderID,
                               inactivePLPC,
                               rs.customer_link_id,
                               rs.department_id,
                               rs.distributionpoint_id,
                               v_wearerEmploymentID,
                               rs.product_link_id,
                               rs.sizedefinition_link_id,
                               sizeStarID,
                               rs.finishingmethod_id,
                               sqItemInCirculationID,
                               rs.noOfUniqueItems,
                               rs.returnrenttype);
  END LOOP;

  END createSqItemInCircOnReturnRent;

  PROCEDURE createOrStoreOnSqOnReturnRent(sqHeaderID            statusquoheader.statusquoheader_id%TYPE,
                                       inactivePLPC          INT,
                                       customerID            customer.customer_id%TYPE,
                                       departmentID          department.department_id%TYPE,
                                       distributionPointID   distributionpoint.distributionpoint_id%TYPE,
                                       wearerEmploymentID    weareremployment.weareremployment_id%TYPE,
                                       productID             product.product_id%TYPE,
                                       sizeDefinitionID      sizedefinition.sizedefinition_id%TYPE,
                                       sizeStarID            sizedefinition.sizedefinition_id%TYPE,
                                       finishingMethodID     finishingmethod.finishingmethod_id%TYPE,
                                       sqItemInCirculationID IN OUT sqitemincirculation.sqitemincirculation_id%TYPE,
                                       noOfUniqueItems       INT,
                                       returnrenttype        INT)
   IS

    --declaration of variables
    foundplpc               BOOLEAN;
    vQtyRentedPerReturnRent INT;
    vQtyRentedPerMinRent    INT;
    emblemTemplateID       emblemTemplate.emblemTemplate_ID%TYPE := -1;
    billingMethod          productlistpercustomer.billingMethod%TYPE := -1;
    seasontypeid           seasontype.seasontype_id%TYPE := -1;
    depositStockAllowed    productlistpercustomer.depositStockAllowed%TYPE;
    percafterdeprec        productlistpercustomer.percentageafterdepreciation%TYPE := 0;
    plpcID                 productlistpercustomer.productlistpercustomer_id%TYPE := -1;
    isActive               BOOLEAN := FALSE;
    inventoryBillingType   productlistpercustomer.inventoryBillingType%TYPE := -1;
    maxinv                 wearerinventory.maxinventory%TYPE := 0;
    qtycustrentstop INT;
    qtyrentstop INT;
    vQtyRentedPerPiece INT;
    seasonalRent           productlistpercustomer.seasonalrent%TYPE := 0;
  BEGIN
    qtycustrentstop := 0; --countCustomerRentStop
    qtyrentstop := 0;

    BEGIN
      SELECT COUNT(t.temporarygarmentrentstop_id)
        INTO qtycustrentstop
        FROM temporarygarmentrentstop t
       WHERE t.customer_id = customerID
             AND f_isinyearweeklifespan(startyear,
                                        startweek,
                                        sq_year,
                                        sq_week,
                                        endyear,
                                        endweek) = 1;
    EXCEPTION
      WHEN no_data_found THEN
        qtycustrentstop := 0;
    END;

    BEGIN
      SELECT COUNT(WI.WEARERINVENTORY_ID) INTO qtyrentstop
      FROM WEARERINVENTORY WI
      WHERE WI.WEAREREMPLOYMENT_ID = wearerEmploymentID AND WI.PRODUCT_ID = productID AND
            WI.SIZEDEFINITION_ID = sizeDefinitionID AND WI.FINISHINGMETHOD_ID = finishingMethodID AND
            sysdate BETWEEN WI.TEMPORARYRENTSTOPSTARTDATE AND WI.TEMPORARYRENTSTOPENDDATE;
    EXCEPTION
      WHEN no_data_found THEN
        qtyrentstop := 0;
    END;

    --  compute
    IF (returnrenttype = 1) THEN
        vQtyRentedPerReturnRent := noOfUniqueItems;
        vQtyRentedPerMinRent := 0;
    ELSE
        vQtyRentedPerReturnRent := 0;
        vQtyRentedPerMinRent := noOfUniqueItems;
    END IF;

    IF (qtycustrentstop > 0 OR qtyrentstop > 0) THEN
      vQtyRentedPerReturnRent := 0;
      vQtyRentedPerMinRent := 0;
      vQtyRentedPerPiece :=0;
    ELSE
      vQtyRentedPerPiece   := noOfUniqueItems;
    END IF;

    IF (qtycustrentstop > 0) THEN
      qtycustrentstop   := noOfUniqueItems;
    END IF;
    IF (qtyrentstop > 0) THEN
      qtyrentstop   := noOfUniqueItems;
    END IF;

    -- search for SQItemInCirculationID
    IF (sqItemInCirculationID = -1) THEN
      BEGIN
        SELECT sq.sqitemincirculation_id
          INTO sqItemInCirculationID
          FROM sqitemincirculation sq
         WHERE sq.statusquoheader_id = sqHeaderID
               AND sq.distributionpoint_link_id = distributionPointID
               AND sq.weareremployment_link_id is null
               AND sq.product_link_id = productID
               AND sq.sizedefinition_link_id = sizeDefinitionID
               AND sq.finishingmethod_id = finishingMethodID;
      EXCEPTION
        WHEN no_data_found THEN
          sqItemInCirculationID := -1;
      END;
    END IF;

    --find plpc for statusQuo.Date
    BEGIN
            foundplpc := getPLPC(customerID,
                         productID,
                         sizeDefinitionID,
                         finishingMethodID,
                         sizeStarID,
                         inactivePLPC,
                         0,
                         billingMethod,
                         seasontypeid,
                         emblemTemplateID,
                         depositStockAllowed,
                         percAfterDeprec,
                         plpcID,
                         isActive,
                         inventoryBillingType, seasonalRent);
    EXCEPTION
    WHEN OTHERS THEN
    /*write_log('No active PLPC was found for customerID ' || customerID ||
                        ' productID ' || productID || ' sizeDefinitionID ' ||
                        sizeDefinitionID || ' finishingMethodID ' || finishingMethodID || ' in date ' ||
                        sq_date,
                        3);*/
                        commit;
    END;

    --checks
    IF (foundplpc AND (isActive OR inactivePLPC < 2)) THEN
      IF (inventoryBillingType = 2) THEN
        maxinv := noOfUniqueItems;
      END IF;
        --insert / update
      insertValuesIntoSqItemInCirc(sqItemInCirculationID,
                                   sqHeaderID,
                                   customerID,
                                   departmentID,
                                   distributionPointID,
                                   wearerEmploymentID, --weareremployment
                                   productID,
                                   sizeDefinitionID,
                                   finishingMethodID,
                                   maxinv,
                                   0, --qtytotalcircinv
                                   0, --changesPerWeek,
                                   0, --maxFreeOfCharge,
                                   0, --qtyfree
                                   0, --cogcircin
                                   0, --qtyfreeseason
                                   0, --beyondlifetimecirc
                                   vQtyRentedPerPiece, --qtyrentedperpiece
                                   0, --qtyrentother
                                   qtycustrentstop, --qtycustrentstop
                                   qtyrentstop, --qtyrentstop
                                   null, -- emblemTemplateID,
                                   0, --qtyrentemblem
                                   0, --qtyreplacement
                                   0, --qtyassigned
                                   0, --qtyrenteddepositstock
                                   0, --qtysubstitute
                                   'N', --chargeafterdepreciation
                                   0,
                                   vQtyRentedPerReturnRent,
                                   vQtyRentedPerMinRent,
                                   0);

    END IF;

  END createOrStoreOnSqOnReturnRent;

END pack_sqitemsincirculation;
/
