__IF_EXISTS__
SELECT 1
  FROM CI_UT_INSTL_DTL
 WHERE SCRIPT_FILE_NAME = 'FW410_APDATA23.SQL'
   AND END_DTTM IS NOT NULL
/
__THEN__

__EXIT__

__ENDIF__

__IF_EXISTS__
SELECT 1
  FROM CI_INSTALL_PROD
 WHERE INSTALL_OPT_ID = '11111'
   AND OWNER_FLG = 'F1'
/
__ELSE__

__EXIT__

__ENDIF__



UPDATE CI_MD_TBL A
set A.TBL_CLASSIFICATION_FLG = (SELECT DECODE(SUBSTR(B.INDEX_NAME,3,1),'C','F1AN','T','F1TT','M','F1MT','F1UC')
FROM USER_INDEXES B
WHERE UPPER(RTRIM(A.TBL_NAME)) = B.TABLE_NAME
AND B.INDEX_NAME LIKE '%P0' 
AND B.TABLE_NAME NOT LIKE 'ZZ%'
and length(rtrim(B.INDEX_NAME)) = 8 )
WHERE UPPER(RTRIM(A.TBL_NAME)) = (SELECT B.TABLE_NAME
FROM USER_INDEXES B
WHERE UPPER(RTRIM(A.TBL_NAME)) = B.TABLE_NAME
AND B.INDEX_NAME LIKE '%P0' 
AND B.TABLE_NAME NOT LIKE 'ZZ%'
and length(rtrim(B.INDEX_NAME)) = 8 )
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/


UPDATE CI_MD_TBL A
set A.TBL_CLASSIFICATION_FLG = (SELECT DECODE(SUBSTR(B.INDEX_NAME,2,1),'C','F1AN','T','F1TT','M','F1MT','F1UC')
FROM USER_INDEXES B
WHERE UPPER(RTRIM(A.TBL_NAME)) = B.TABLE_NAME
AND B.INDEX_NAME LIKE '%P0' 
AND B.TABLE_NAME NOT LIKE 'ZZ%'
and length(rtrim(B.INDEX_NAME)) = 7 )
WHERE UPPER(RTRIM(A.TBL_NAME)) = (SELECT B.TABLE_NAME
FROM USER_INDEXES B
WHERE UPPER(RTRIM(A.TBL_NAME)) = B.TABLE_NAME
AND B.INDEX_NAME LIKE '%P0' 
AND B.TABLE_NAME NOT LIKE 'ZZ%'
and length(rtrim(B.INDEX_NAME)) = 7 )
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/


UPDATE CI_MD_TBL A
set A.TBL_CLASSIFICATION_FLG = 'F1AS'
WHERE A.SYSTEM_TBL_SW = 'Y'
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/


UPDATE CI_MD_TBL A
set A.TBL_CLASSIFICATION_FLG = 'F1UC'
WHERE RTRIM(A.TBL_CLASSIFICATION_FLG) IS NULL
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/



UPDATE CI_MD_TBL A
set A.TBL_VOLUME_FLG = 'F1LV'
WHERE RTRIM(A.TBL_CLASSIFICATION_FLG) in ('F1AN','F1AS')
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/



UPDATE CI_MD_TBL A
set A.TBL_VOLUME_FLG = 'F1MV'
WHERE RTRIM(A.TBL_CLASSIFICATION_FLG) in ('F1MT','F1TT')
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/


UPDATE CI_MD_TBL A
set A.TBL_VOLUME_FLG = 'F1UC'
WHERE RTRIM(A.TBL_CLASSIFICATION_FLG) = 'F1UC'
AND upper(A.TBL_NAME) NOT LIKE 'ZZ%'
/
