-- ABS-104343 / ABS-104408
CREATE OR REPLACE PROCEDURE UPDATE_LOSSCHARGE_FOR_NOT_CHARGED_CUSTOMER IS
  UIID            NUMBER;
  PRIMARYID       VARCHAR2(24);
  POS             NUMBER;
  DATEINACTIVE    TIMESTAMP;  
  TEXT      VARCHAR2(100);
  CURSOR WIFORLOSS IS
    WITH WI AS
     (SELECT WINV.WEARERINVENTORY_ID,
             WINV.WEAREREMPLOYMENT_ID,
             WINV.DATEINACTIVE        WIINACTIVE
        FROM WEARERINVENTORY  WINV,
             WEARER           W,
             WEAREREMPLOYMENT WEMP,
             CUSTOMER         C
       WHERE WINV.WEAREREMPLOYMENT_ID = WEMP.WEAREREMPLOYMENT_ID
         AND WEMP.WEARER_ID = W.WEARER_ID
         AND W.CUSTOMER_ID = C.CUSTOMER_ID
         AND C.CHARGELOSSCHARGE = 'N'
         AND WINV.MAXINVENTORY - WINV.RETURNEDQTY > 0)

    SELECT 1 POS, UI.PRIMARYID, UI.UNIQUEITEM_ID, WI.WIINACTIVE
      FROM UNIQUEITEMNONPOOL   UINP,
           UNIQUEITEM          UI,
           UNIQUEITEMFIXEDINFO UIFI,
           WI
     WHERE UINP.UNIQUEITEM_ID = UI.UNIQUEITEM_ID
       AND UI.STATUS_ID = 27
       AND UI.CUSTOMEROWNED = 'N'
       AND UIFI.UNIQUEITEM_ID = UI.UNIQUEITEM_ID
       AND NVL(UIFI.LOSSCHARGEINVOICELINE_ID, 0) = 0
       AND UINP.CHARGEDFORLOSS = 'N'
       AND UINP.FREEEXTRAGARMENT = 'N'
       AND UINP.WEARERINVENTORY_ID = WI.WEARERINVENTORY_ID
       AND WI.WIINACTIVE <= SYSDATE
    UNION ALL
    SELECT 2 POS, '', A.NONUNIQUEITEM_ID, WI.WIINACTIVE
      FROM NONUNIQUEITEM A, WI
     WHERE A.CHARGEDFORLOSS = 'N'
      AND A.FREEITEM = 'N'
      AND A.WEARERINVENTORY_ID = WI.WEARERINVENTORY_ID     
      AND WI.WIINACTIVE <= SYSDATE
    UNION ALL
    SELECT 3 POS, UI.PRIMARYID, UI.UNIQUEITEM_ID, WI.WIINACTIVE
      FROM UNIQUEITEMNONPOOL   UINP,
           UNIQUEITEM          UI,
           UNIQUEITEMFLAG      UIF,
           UNIQUEITEMFIXEDINFO UIFI,
           WI
     WHERE UIF.UNIQUEITEM_ID = UINP.UNIQUEITEM_ID 
     AND UINP.WEARERINVENTORY_ID = WI.WEARERINVENTORY_ID 
     AND UI.STATUS_ID = 27 
     AND UI.CUSTOMEROWNED = 'N' 
     AND UI.UNIQUEITEM_ID = UIF.UNIQUEITEM_ID 
     AND UIF.FLAG_ID IN (SELECT F.FLAG_ID FROM AUTOMATICFLAG F WHERE F.AUTOMATICFLAG_ID IN (4, 7))
     AND UIF.STARTDATE <= SYSDATE 
     AND UINP.CHARGEDFORLOSS = 'N' 
     AND UIFI.UNIQUEITEM_ID = UIF.UNIQUEITEM_ID 
     AND NVL(UIFI.LOSSCHARGEINVOICELINE_ID, 0) = 0 
     AND WI.WIINACTIVE > SYSDATE
    UNION ALL
    SELECT 4 POS, '', A.NONUNIQUEITEM_ID, WI.WIINACTIVE
      FROM NONUNIQUEITEM A, WI
     WHERE A.WEARERINVENTORY_ID = WI.WEARERINVENTORY_ID 
     AND A.FLAG_ID IN (SELECT F.FLAG_ID FROM AUTOMATICFLAG F WHERE F.AUTOMATICFLAG_ID IN (4, 7))
     AND A.FLAGSTARTDATE <= SYSDATE 
     AND A.CHARGEDFORLOSS = 'N'     
     AND WI.WIINACTIVE > SYSDATE;

  PROCEDURE WRITE_LOG(FP_MESSAGE  IN VARCHAR2,
                      FP_PRIORITY IN NUMBER DEFAULT 3) IS
  BEGIN
    ABSLOGWRITE.ABSLOG_WRITE(5,
                             'COLLECT_LOSSCHARGE_NOTCHARGECUSTOMERS',
                             NULL,
                             FP_PRIORITY,
                             FP_MESSAGE,
                             0);
  END WRITE_LOG;

BEGIN

  OPEN WIFORLOSS;
  LOOP
    FETCH WIFORLOSS
      INTO POS, PRIMARYID, UIID, DATEINACTIVE;
    EXIT WHEN WIFORLOSS%NOTFOUND;
    IF POS < 3 THEN
      TEXT := 'ITEM COLLECTED FROM WEARER INACTIVE ';
    ELSE
      TEXT := 'ITEM COLLECTED BECAUSE MARKED AS LOST ';
    END IF;
    IF POS = 1 OR POS = 3 THEN
      BEGIN 
        WRITE_LOG('PRIMARYID: ' || PRIMARYID || ' WITH UNIQUEID ' ||
                TO_CHAR(UIID) || TEXT);
        UPDATE UNIQUEITEMNONPOOL UINP SET UINP.CHARGEDFORLOSS = 'Y' WHERE UINP.UNIQUEITEM_ID = UIID; 
       END;          
    ELSE
       BEGIN
         WRITE_LOG('NON UNIQUE ITEM WITH NONUNIQUEID ' || TO_CHAR(UIID) || TEXT);
         UPDATE NONUNIQUEITEM NUI SET NUI.CHARGEDFORLOSS = 'Y' WHERE NUI.NONUNIQUEITEM_ID = UIID;
       END; 
    END IF;
  
  END LOOP;
  CLOSE WIFORLOSS;
  COMMIT;
END UPDATE_LOSSCHARGE_FOR_NOT_CHARGED_CUSTOMER;
/

EXECUTE UPDATE_LOSSCHARGE_FOR_NOT_CHARGED_CUSTOMER ;

DROP PROCEDURE UPDATE_LOSSCHARGE_FOR_NOT_CHARGED_CUSTOMER;