
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')
/

INSERT INTO CI_ZONE_PRM SELECT distinct a.zone_cd,a.zone_hdl_cd, b.zone_hdl_prm_name,' ',1,a.owner_flg,' ' 
FROM CI_ZONE_HDL_PRM b,
     CI_ZONE a
WHERE a.zone_hdl_cd = b.zone_hdl_cd
  and b.zone_hprm_usg_flg in ('ZONE', 'ZOVR')
  and not exists (select 1 from ci_zone_prm d
                                        where d.zone_cd = a.zone_cd
                                          and d.zone_hdl_cd = b.zone_hdl_cd
                                          and d.zone_hdl_prm_name =  b.zone_hdl_prm_name)
/

insert into CI_ALG_PARM
 select C.ALG_CD, C.EFFDT, A.SEQNO,' ',1,C.OWNER_FLG
from CI_ALG_TYPE_PRM A, CI_ALG B, CI_ALG_VER C
where A.ALG_TYPE_CD = B.ALG_TYPE_CD
and B.ALG_CD = C.ALG_CD
and not exists (select * from CI_ALG_PARM X where C.ALG_CD = x.alg_cd and c.effdt = x.effdt and A.SEQNO = x.seqno)
/