
DELETE FROM SC_USR_GRP_PROF A WHERE A.APP_SVC_ID NOT IN (SELECT APP_SVC_ID FROM SC_APP_SERVICE)
/

DELETE FROM SC_ACCESS_CNTL A WHERE NOT EXISTS (SELECT 'X' FROM CI_APP_SVC_ACC B WHERE A.APP_SVC_ID = B.APP_SVC_ID AND A.ACCESS_MODE = B.ACCESS_MODE)
/

DELETE FROM CI_USR_GRP_SC A WHERE A.APP_SVC_ID NOT IN (SELECT APP_SVC_ID FROM SC_APP_SERVICE)
/

DELETE FROM CI_APP_SVC_SCTY A WHERE A.APP_SVC_ID NOT IN (SELECT APP_SVC_ID FROM SC_APP_SERVICE)
/

TRUNCATE TABLE CI_MPL_EXC REUSE STORAGE
/

DELETE FROM CI_USR_NAV_LINK WHERE NAV_OPT_CD NOT IN (SELECT NAV_OPT_CD FROM CI_NAV_OPT)
/

UPDATE CI_MD_TBL SET AUDIT_PGM_TYPE_FLG = ' ' 
  WHERE RTRIM(AUDIT_PGM_TYPE_FLG) IS NOT NULL 
  AND (RTRIM(AUDIT_PGM_NAME) IS NULL)
/


INSERT INTO CI_ROLE ( ROLE_ID, VERSION ) SELECT
'F1_DFLT   ', 1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM CI_ROLE WHERE ROLE_ID = 'F1_DFLT')
/

INSERT INTO CI_ROLE_L ( ROLE_ID, LANGUAGE_CD, VERSION, DESCR ) 
SELECT 'F1_DFLT   ', A.LANGUAGE_CD, 1, 'System Default Role'
FROM CI_LANGUAGE A
WHERE NOT EXISTS (SELECT 1 FROM CI_ROLE_L WHERE ROLE_ID = 'F1_DFLT' AND LANGUAGE_CD = A.LANGUAGE_CD)
  AND A.SUPPORT_SW = 'Y'
/

INSERT INTO CI_TD_VAL_ROLE ( TD_TYPE_CD, ROLE_ID, VERSION, DEFAULT_SW )
SELECT TD_TYPE_CD, 'F1_DFLT', 1, 'Y'
FROM CI_TD_TYPE A
WHERE NOT EXISTS (SELECT 1 FROM CI_TD_VAL_ROLE WHERE TD_TYPE_CD = A.TD_TYPE_CD)
/

INSERT INTO CI_ROLE_USER ( ROLE_ID, USER_ID, VERSION )  SELECT  'F1_DFLT', 'SYSUSER', 1
FROM DUAL WHERE NOT EXISTS 
(SELECT 1 FROM CI_ROLE_USER 
WHERE ROLE_ID = 'F1_DFLT' AND USER_ID = 'SYSUSER')
/

DELETE FROM CI_XAI_OPTION WHERE XAI_OPTION_FLG in ('SDAC', 'SDAP', 'SDCA', 'SDSA') 
/

UPDATE CI_INSTALL_PROD SET RELEASE_ID_SFX =' ' WHERE OWNER_FLG in ('F1')
/
