Untitled

 --cap nhat trang thai yeu cau nhan vien
   PROCEDURE UPDATE_ADDRESS_REQUEST (pCUS_ID             NUMBER,
                                     pACTION             NUMBER,
                                     pGROUPADDRESS_ID    NUMBER)
   IS
      vID      NUMBER;
      vROLE    NUMBER;
      vCHECK   NUMBER;
   BEGIN
--      RAISE_APPLICATION_ERROR(-20001, 'INVALID USER');
      IF pACTION = 1
      THEN
         SELECT COUNT (*)
           INTO vID
           FROM CUS_SETTING_ADDRESS
          WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID;

         IF vID > 0
         THEN
            SELECT PARENT_ID
              INTO vID
              FROM CUS_SETTING_ADDRESS
             WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID;

            SELECT CUS_ROLE
              INTO vROLE
              FROM CUS_CUSTOMER
             WHERE CUS_ID = vID;
         END IF;

         UPDATE CUS_SETTING_ADDRESS
            SET IS_ACTIVE = 1
          WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID;

         UPDATE CUS_SETTING_ADDRESS
            SET IS_ACTIVE = 2
          WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID <> pGROUPADDRESS_ID;

         UPDATE CUS_CUSTOMER
            SET PARNER = vID, CUS_ROLE = vROLE
          WHERE CUS_ID = pCUS_ID;

         UPDATE CUS_GROUPADDRESS
            SET ACTIVE = 'N'
          WHERE     GROUPADDRESS_ID <> pGROUPADDRESS_ID
                AND CUS_ID = pCUS_ID
                AND ACTIVE = 'Y';

         SELECT COUNT (*)
           INTO vCHECK
           FROM CUS_GROUPADDRESS
          WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND CUS_ID = pCUS_ID;

         IF vCHECK > 0
         THEN
            UPDATE CUS_GROUPADDRESS
               SET ACTIVE = 'Y', CUS_ID_NUMBER = vID
             WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND CUS_ID = pCUS_ID;
         ELSE
            INSERT INTO CUS_GROUPADDRESS (GROUPADDRESS_ID,
                                          CUS_ID,
                                          POST_ID,
                                          NAME,
                                          VALUE,
                                          EVTPCODE,
                                          PHONE,
                                          ADDRESS,
                                          PROVINCE_ID,
                                          DISTRICT_ID,
                                          WARDS_ID,
                                          ROUTE,
                                          COUNTRY,
                                          TYPE,
                                          CREATEDBYUSERID,
                                          CREATEDONDATE,
                                          LASTMODIFIEDBYUSERID,
                                          LASTMODIFIEDONDATE,
                                          ACTIVE,
                                          CUS_ID_NUMBER)
               SELECT CUS_GROUPADDRESS_SEQ.NEXTVAL,
                      CUS_ID,
                      0,
                      DISPLAYNAME,
                      0,
                      '',
                      PHONE,
                      ADDRESS,
                      PROVINCE_ID,
                      DISTRICT_ID,
                      WARDS_ID,
                      0,
                      0,
                      2,
                      CUS_ID,
                      SYSDATE,
                      CUS_ID,
                      SYSDATE,
                      'Y',
                      vID
                 FROM CUS_CUSTOMER
                WHERE CUS_ID = pCUS_ID;
         END IF;
      ELSE
         --UPDATE CUS_SETTING_ADDRESS SET IS_ACTIVE = 1 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID;
         UPDATE CUS_SETTING_ADDRESS
            SET IS_ACTIVE = 2
          WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID;
          
         SELECT PARENT_ID INTO vCHECK FROM ERP_CUS.CUS_SETTING_ADDRESS WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID AND IS_ACTIVE = 2;
         
         IF vCHECK > 0 THEN 

            UPDATE ERP_CUS.CUS_SETTING_ADDRESS  SET IS_ACTIVE = 2 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID <> pGROUPADDRESS_ID AND IS_ACTIVE = 1 AND  SETTING_PATH LIKE '!_' || vCHECK || '!_%' ESCAPE '!';
            COMMIT;
            
         END IF;
         
      --UPDATE CUS_GROUPADDRESS SET ACTIVE = 'N' WHERE GROUPADDRESS_ID <> pGROUPADDRESS_ID AND  CUS_ID = pCUS_ID AND ACTIVE = 'Y';
      --UPDATE CUS_GROUPADDRESS SET ACTIVE = 'Y' WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND  CUS_ID = pCUS_ID AND ACTIVE = 'N';
      END IF;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;