--ABS-153710

--USERGROUP TABLE
ALTER TABLE USERGROUP ADD (ALLOWWEBROUTECHECKIN2 NUMBER DEFAULT 0 NOT NULL);

CREATE OR REPLACE PROCEDURE UPDATEALLOWWEB IS
lv_datatype user_tab_columns.DATA_TYPE%type;
BEGIN

SELECT UT.DATA_TYPE
  INTO LV_DATATYPE
  FROM USER_TAB_COLUMNS UT
 WHERE UT.TABLE_NAME = 'USERGROUP'
   AND UT.COLUMN_NAME = 'ALLOWWEBROUTECHECKIN';
   
if lv_datatype <> 'NUMBER' then    
  FOR X IN (SELECT UG.USERGROUP_ID, UG.ALLOWWEBROUTECHECKIN FROM USERGROUP UG) LOOP
      BEGIN
      IF X.ALLOWWEBROUTECHECKIN = 'N'
          THEN
            UPDATE USERGROUP UG2 SET UG2.ALLOWWEBROUTECHECKIN2 = 0 WHERE UG2.USERGROUP_ID = X.USERGROUP_ID;
          ELSE
            UPDATE USERGROUP UG2 SET UG2.ALLOWWEBROUTECHECKIN2 = 2 WHERE UG2.USERGROUP_ID = X.USERGROUP_ID;
          END IF;
          EXCEPTION
          WHEN OTHERS THEN
            ABSLOGWRITE.abslog_write(5, 'UPDATEALLOWWEB', 1, 1, SQLERRM || x.usergroup_id, 1);
   END;
  END LOOP;
  COMMIT;
  
 end if;
END UPDATEALLOWWEB;
/
EXECUTE UPDATEALLOWWEB;
DROP PROCEDURE UPDATEALLOWWEB;

CREATE OR REPLACE PROCEDURE temp_proc IS
lv_datatype user_tab_columns.DATA_TYPE%type;
BEGIN

SELECT UT.DATA_TYPE
  INTO LV_DATATYPE
  FROM USER_TAB_COLUMNS UT
 WHERE UT.TABLE_NAME = 'USERGROUP'
   AND UT.COLUMN_NAME = 'ALLOWWEBROUTECHECKIN';
   
if lv_datatype <> 'NUMBER' then
  begin
  execute immediate 'ALTER TABLE USERGROUP DROP CONSTRAINT CHK_USERGROUP_ALLOWWEBROUTECHECKIN';
  exception when others then 
    null;
  end;
  execute immediate 'ALTER TABLE USERGROUP DROP COLUMN ALLOWWEBROUTECHECKIN';
  execute immediate 'ALTER TABLE USERGROUP RENAME COLUMN  ALLOWWEBROUTECHECKIN2 TO ALLOWWEBROUTECHECKIN';
  execute immediate 'ALTER TABLE USERGROUP ADD CONSTRAINT CHK_USERGROUP_ALLOWWEBROUTECHECKIN CHECK (ALLOWWEBROUTECHECKIN IN (0, 1, 2))';

else
  execute immediate 'ALTER TABLE USERGROUP DROP COLUMN ALLOWWEBROUTECHECKIN2';
end if;
END temp_proc;
/
EXECUTE temp_proc;
DROP PROCEDURE temp_proc;


COMMENT ON COLUMN USERGROUP.ALLOWWEBROUTECHECKIN IS '0 = No access (default)
1 = Inquiry (read only) 
2 = Modify
';