-- Comment out in early part of release as I plan to add to this.
--__IF_EXISTS__
--SELECT 1
--  FROM CI_UT_INSTL_DTL
--WHERE SCRIPT_FILE_NAME = 'FW45010_POSTDATA_UGBUPD-10242.SQL'
--   AND END_DTTM IS NOT NULL
--/
--__THEN__
--__EXIT__
--__ENDIF__

-- Remove the CMable column values.  

update F1_BUS_OBJ_STATUS set BATCH_CD = ' ' where BUS_OBJ_CD = 'F1-MessageFromDevice' and BO_STATUS_CD = 'ERROR' and BATCH_CD = 'F1-RMMSG'
/
update F1_BUS_OBJ_STATUS set BATCH_CD = ' ' where BUS_OBJ_CD = 'F1-MessageToDevice' and BO_STATUS_CD = 'QUEUED' and BATCH_CD = 'F1-RMMSG'
/
update CI_FK_REF SET ZONE_CD = ' ' WHERE FK_REF_CD = 'F1-MDT' AND ZONE_CD = 'F1-MDTQRY'
/

DELETE FROM CI_ALG_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_ALG B WHERE A.ALG_CD = B.ALG_CD) AND A.ALG_CD IN (
'F1-CREMDTODO', 
'F1-MDTINFO', 
'F1-RMMSGINF',
'F1-MOBCMPINF',
'F1-DEPLY-AUD',
'F1-DEPL-INFO',
'F1-DEPTY-AUD',
'F1-DEPRT-AUD') 
/
DELETE FROM CI_ALG_VER A WHERE NOT EXISTS (SELECT 'X' FROM CI_ALG B WHERE A.ALG_CD = B.ALG_CD) AND A.ALG_CD IN (
'F1-CREMDTODO', 
'F1-MDTINFO', 
'F1-RMMSGINF',
'F1-MOBCMPINF',
'F1-DEPLY-AUD',
'F1-DEPL-INFO',
'F1-DEPTY-AUD',
'F1-DEPRT-AUD') 
/
DELETE FROM CI_ALG_PARM A WHERE NOT EXISTS (SELECT 'X' FROM CI_ALG B WHERE A.ALG_CD = B.ALG_CD) AND A.ALG_CD IN (
'F1-CREMDTODO', 
'F1-MDTINFO', 
'F1-RMMSGINF',
'F1-MOBCMPINF',
'F1-DEPLY-AUD',
'F1-DEPL-INFO',
'F1-DEPTY-AUD',
'F1-DEPRT-AUD') 
/
DELETE FROM CI_ALG_TYPE_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_ALG_TYPE B WHERE A.ALG_TYPE_CD = B.ALG_TYPE_CD) AND A.ALG_TYPE_CD IN (
'F1-CREMDTODO', 
'F1-MDTINFO', 
'F1-RMMSGINF',
'F1-MOBCMPINF',
'F1-DEPLY-AUD',
'F1-DEPL-INFO',
'F1-DEPTY-AUD',
'F1-DEPRT-AUD')
/
DELETE FROM CI_ALG_TYPE_PRM_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_ALG_TYPE B WHERE A.ALG_TYPE_CD = B.ALG_TYPE_CD) AND A.ALG_TYPE_CD IN (
'F1-CREMDTODO', 
'F1-MDTINFO', 
'F1-RMMSGINF',
'F1-MOBCMPINF',
'F1-DEPLY-AUD',
'F1-DEPL-INFO',
'F1-DEPTY-AUD',
'F1-DEPRT-AUD')
/
DELETE FROM CI_BATCH_CTRL_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_BATCH_CTRL B WHERE A.BATCH_CD = B.BATCH_CD) AND A.BATCH_CD IN (
'F1-RMMSG',
'F1-RMCRL',
'F1MDMDTX',
'F1-BMCOM',
'F1MOBCPX',
'F1DEPLPX',
'F1DEPL2X',
'F1MDTTEX',
'F1-DPLOY',
'F1-DPUTD')
/
DELETE FROM CI_BATCH_CTRL_P A WHERE NOT EXISTS (SELECT 'X' FROM CI_BATCH_CTRL B WHERE A.BATCH_CD = B.BATCH_CD) AND A.BATCH_CD IN (
'F1-RMMSG',
'F1-RMCRL',
'F1MDMDTX',
'F1-BMCOM',
'F1MOBCPX',
'F1DEPLPX',
'F1DEPL2X',
'F1MDTTEX',
'F1-DPLOY',
'F1-DPUTD')
/
DELETE FROM CI_BATCH_CTRL_P_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_BATCH_CTRL B WHERE A.BATCH_CD = B.BATCH_CD) AND A.BATCH_CD IN (
'F1-RMMSG',
'F1-RMCRL',
'F1MDMDTX',
'F1-BMCOM',
'F1MOBCPX',
'F1DEPLPX',
'F1DEPL2X',
'F1MDTTEX',
'F1-DPLOY',
'F1-DPUTD')
/
DELETE FROM CI_MD_FLD_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_FLD B WHERE A.FLD_NAME = B.FLD_NAME) AND A.FLD_NAME IN (
'F1DEPTYP_AN_LBL',
'F1DPL_AN_LBL',
'F1DPLPRT_AN_LBL',
'F1DPLQRY_AN_LBL',
'F1MDTMNT_AN_LBL',
'F1MDTQRY_AN_LBL',
'F1MDTTYP_AN_LBL',
'F1MOBCMP_AN_LBL',
'F1MOBCPQ_AN_LBL',
'F1REMSGQ_AN_LBL',
'F1REMSG_AN_LBL',
'F1_CREATE_MDTS_LBL',
'F1_DEP_MAINSEC_LBL',
'F1_DEP_PART_ENTS_LBL',
'F1_DEP_PART_MAINSEC_LBL',
'F1_DEP_TYPE_MAINSEC_LBL',
'F1_LOG_FILES_LBL',
'F1_MDT_MAINSEC_LBL',
'F1_MDT_TAG_PREFIX',
'F1_MDT_TO_COPY_LBL',
'F1_MDT_TYPE_MAINSEC_LBL',
'F1_MDTS_COUNT',
'F1_MDTS_STARTNUM',
'F1_MOBILE_COMP_MAIN_LBL',
'F1_MOBILE_COMP_MAIN_MSTDV_LBL',
'F1_MSG_FROM_DEV_MAIN_LBL',
'F1_MSG_TO_DEV_MAIN_LBL',
'F1_MOB_APP_INVITE_LBL',
'F1_MOB_APP_INVITE_LINK_LBL')
/
DELETE FROM CI_MD_MENU_ITEM_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_MENU_ITEM B WHERE A.MENU_ITEM_ID = B.MENU_ITEM_ID) AND A.MENU_ITEM_ID IN (
'F129802486',
'F130500521',
'F170032168',
'F174679180',
'F180726581',
'F191946600',
'F106285844',
'F121671255',
'F155731410',
'F169323131',
'F117167914',
'F119151844',
'F140089158',
'F143008442',
'F143295846',
'F168545736',
'F172586868',
'F174285300',
'F179325275',
'F187108638',
'F189032197')
/
DELETE FROM CI_MD_MENU_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_MENU B WHERE A.MENU_NAME = B.MENU_NAME) AND A.MENU_NAME IN (
'F1MDTMNT-ADD',
'F1MDTMNT-PA',
'F1REMSG-PA',
'F1-MOBCMP-PA',
'F1MOBCMP-ADD',
'F1DPL-PA',
'F1DEPTYP-ADD',
'F1DPLPRT-ADD',
'F1MDTTYP-ADD')
/
DELETE FROM CI_MD_MENU_LINE_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_MENU_LINE B WHERE A.MENU_LINE_ID = B.MENU_LINE_ID) AND A.MENU_LINE_ID IN (
'F106591162',
'F116680106',
'F164125290',
'F186213958',
'F188877696',
'F146257651',
'F160588254',
'F174620193',
'F108339072',
'F115635696',
'F121027752',
'F136374196',
'F155816152',
'F157926662',
'F163469364',
'F193353262')
/
DELETE FROM CI_MD_SVC_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_SVC B WHERE A.SVC_NAME = B.SVC_NAME) AND A.SVC_NAME IN (
'F1MDTMNT',
'F1MDTQRY',
'F1REMSG',
'F1REMSGQ',
'F1MOBCMP',
'F1MOBCPQ',
'F1DEPTYP',
'F1DPL',
'F1DPLPRT',
'F1DPLQRY',
'F1MDTTYP')
/
DELETE FROM CI_MD_SVC_PRG A WHERE NOT EXISTS (SELECT 'X' FROM CI_MD_SVC B WHERE A.SVC_NAME = B.SVC_NAME) AND A.SVC_NAME IN (
'F1MDTMNT',
'F1MDTQRY',
'F1REMSG',
'F1REMSGQ',
'F1MOBCMP',
'F1MOBCPQ',
'F1DEPTYP',
'F1DPL',
'F1DPLPRT',
'F1DPLQRY',
'F1MDTTYP')
/
DELETE FROM CI_MSG_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_MSG B WHERE A.MESSAGE_CAT_NBR = B.MESSAGE_CAT_NBR AND A.MESSAGE_NBR = B.MESSAGE_NBR) 
AND A.MESSAGE_CAT_NBR = '11023' AND A.MESSAGE_NBR IN (
'10060',
'10061')
/
DELETE FROM CI_NAV_OPT_CTXT A WHERE NOT EXISTS (SELECT 'X' FROM CI_NAV_OPT B WHERE A.NAV_OPT_CD = B.NAV_OPT_CD) AND A.NAV_OPT_CD IN (
'f1remsgTabMenu',
'f1remsgqTabMenu',
'f1MDTAdd',
'f1mdtmntTabMenu',
'f1mdtqryTabMenu',
'f1mobcmpTabMenu',
'f1mobcpqTabMenu',
'f1mobcompAdd',
'f1dplTabMenu',
'f1dplqryTabMenu',
'f1deptypTabMenu',
'f1DepTypeAdd',
'f1DplPartAdd',
'f1MDTTypeAdd',
'f1dplprtTabMenu',
'f1mdttypTabMenu')
/
DELETE FROM CI_NAV_OPT_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_NAV_OPT B WHERE A.NAV_OPT_CD = B.NAV_OPT_CD) AND A.NAV_OPT_CD IN (
'f1remsgTabMenu',
'f1remsgqTabMenu',
'f1MDTAdd',
'f1mdtmntTabMenu',
'f1mdtqryTabMenu',
'f1mobcmpTabMenu',
'f1mobcpqTabMenu',
'f1mobcompAdd',
'f1dplTabMenu',
'f1dplqryTabMenu',
'f1deptypTabMenu',
'f1DepTypeAdd',
'f1DplPartAdd',
'f1MDTTypeAdd',
'f1dplprtTabMenu',
'f1mdttypTabMenu')
/
DELETE FROM CI_NAV_OPT_USG A WHERE NOT EXISTS (SELECT 'X' FROM CI_NAV_OPT B WHERE A.NAV_OPT_CD = B.NAV_OPT_CD) AND A.NAV_OPT_CD IN (
'f1remsgTabMenu',
'f1remsgqTabMenu',
'f1MDTAdd',
'f1mdtmntTabMenu',
'f1mdtqryTabMenu',
'f1mobcmpTabMenu',
'f1mobcpqTabMenu',
'f1mobcompAdd',
'f1dplTabMenu',
'f1dplqryTabMenu',
'f1deptypTabMenu',
'f1DepTypeAdd',
'f1DplPartAdd',
'f1MDTTypeAdd',
'f1dplprtTabMenu',
'f1mdttypTabMenu')
/
DELETE FROM CI_PORTAL_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_PORTAL B WHERE A.PORTAL_CD = B.PORTAL_CD) AND A.PORTAL_CD IN (
'F1MDTLOGSCH',
'F1MDTMNT',
'F1MDTQRY',
'F1REMSG',
'F1REMSGLG',
'F1REMSGQ',
'F1MOBCMP',
'F1MOBCPQ',
'F1DPL',
'F1DPLQRY',
'F1DEPTYP',
'F1DPLPRT',
'F1MDTTYP')
/
DELETE FROM CI_PORTAL_OPT A WHERE NOT EXISTS (SELECT 'X' FROM CI_PORTAL B WHERE A.PORTAL_CD = B.PORTAL_CD) AND A.PORTAL_CD IN (
'F1MDTLOGSCH',
'F1MDTMNT',
'F1MDTQRY',
'F1REMSG',
'F1REMSGLG',
'F1REMSGQ',
'F1MOBCMP',
'F1MOBCPQ',
'F1DPL',
'F1DPLQRY',
'F1DEPTYP',
'F1DPLPRT',
'F1MDTTYP')
/
DELETE FROM CI_PORTAL_ZONE A WHERE NOT EXISTS (SELECT 'X' FROM CI_PORTAL B WHERE A.PORTAL_CD = B.PORTAL_CD) AND A.PORTAL_CD IN (
'F1MDTLOGSCH',
'F1MDTMNT',
'F1MDTQRY',
'F1REMSG',
'F1REMSGLG',
'F1REMSGQ',
'F1MOBCMP',
'F1MOBCPQ',
'F1DPL',
'F1DPLQRY',
'F1DEPTYP',
'F1DPLPRT',
'F1MDTTYP')
/
DELETE FROM CI_SCR_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_SCR B WHERE A.SCR_CD = B.SCR_CD) AND A.SCR_CD IN (
'F1-CreMDToDo',
'F1-CrMDTsBPA',
'F1-CreDplymt',
'F1-CreMDTs',
'F1-DepPrPrep',
'F1-DepPrtAud',
'F1-DepTypAud',
'F1-DeplInfo',
'F1-DeployDis',
'F1-DeplyAud',
'F1-DpTyPMain',
'F1-DplMain',
'F1-DplPtMain',
'F1-DpprtAdE',
'F1-GetDplFld',
'F1-InvMobApp',
'F1-InvMobVis',
'F1-InvUsrMob',
'F1-MCEditor',
'F1-MDTLgVw',
'F1-MDTLogFls',
'F1-MdtInfo',
'F1-MDTMain',
'F1-MDTTpMain',
'F1-MobCoMain',
'F1-MobIdDis',
'F1-MobIdMCfg',
'F1-MobIdPre',
'F1-MobCmpInf',
'F1-MsgFrmDvD',
'F1-MsgToDvD',
'F1-PPDepUTD',
'F1-PreMDT',
'F1-PreMDTLog',
'F1-ReMsgMain',
'F1-ReqMDTLog',
'F1-RmMsgInf',
'F1-ShwDfltDt')
/
DELETE FROM CI_SCR_STEP_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_SCR B WHERE A.SCR_CD = B.SCR_CD) AND A.SCR_CD IN (
'F1-CreMDToDo',
'F1-CrMDTsBPA',
'F1-CreDplymt',
'F1-CreMDTs',
'F1-DepPrPrep',
'F1-DepPrtAud',
'F1-DepTypAud',
'F1-DeplInfo',
'F1-DeployDis',
'F1-DeplyAud',
'F1-DpTyPMain',
'F1-DplMain',
'F1-DplPtMain',
'F1-DpprtAdE',
'F1-GetDplFld',
'F1-InvMobApp',
'F1-InvMobVis',
'F1-InvUsrMob',
'F1-MCEditor',
'F1-MDTLgVw',
'F1-MDTLogFls',
'F1-MdtInfo',
'F1-MDTMain',
'F1-MDTTpMain',
'F1-MobCoMain',
'F1-MobIdDis',
'F1-MobIdMCfg',
'F1-MobIdPre',
'F1-MobCmpInf',
'F1-MsgFrmDvD',
'F1-MsgToDvD',
'F1-PPDepUTD',
'F1-PreMDT',
'F1-PreMDTLog',
'F1-ReMsgMain',
'F1-ReqMDTLog',
'F1-RmMsgInf',
'F1-ShwDfltDt')
/
DELETE FROM CI_ZONE_L A WHERE NOT EXISTS (SELECT 'X' FROM CI_ZONE B WHERE A.ZONE_CD = B.ZONE_CD) AND A.ZONE_CD IN (
'F1-DEPTYINFO',
'F1-DPLDSPLY',
'F1-DPLITEM',
'F1-DPLOFD',
'F1-DPLPARTS',
'F1-DPLPRTDSP',
'F1-DPLPRTINF',
'F1-DPLQRY',
'F1-DPLTYPDSP',
'F1-DPPRTAE',
'F1-DPPRTYPS',
'F1-INVMOBAPP',
'F1-MDTDSPLY',
'F1-MCBYTYP',
'F1-MDTLOGSCH',
'F1-MDTLOGSQ',
'F1-MDTQRY',
'F1-MDTQRYM',
'F1-MDTTYINFO',
'F1-MDTTYPDSP',
'F1-MDTTYPES',
'F1-MOBCMPDIS',
'F1-MOBCONEDT',
'F1-MOBCPQ',
'F1-MOBCPQ1',
'F1-MOBLOG',
'F1-REMSGDISP',
'F1-REMSGLOG',
'F1-REMSGQRY1',
'F1-REMSGQRY2',
'F1-REMSGQRY3',
'F1-REMSGQRYS',
'F1-RETDPDPTY',
'F1-RMSTDS')
/
DELETE FROM F1_BUS_OBJ_ALG A WHERE NOT EXISTS (SELECT 'X' FROM F1_BUS_OBJ B WHERE A.BUS_OBJ_CD = B.BUS_OBJ_CD) AND A.BUS_OBJ_CD IN (
'F1-MobileConfigurations',
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_BUS_OBJ_L A WHERE NOT EXISTS (SELECT 'X' FROM F1_BUS_OBJ B WHERE A.BUS_OBJ_CD = B.BUS_OBJ_CD) AND A.BUS_OBJ_CD IN (
'F1-MobileConfigurations',
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_BUS_OBJ_OPT A WHERE NOT EXISTS (SELECT 'X' FROM F1_BUS_OBJ B WHERE A.BUS_OBJ_CD = B.BUS_OBJ_CD) AND A.BUS_OBJ_CD IN (
'F1-MobileConfigurations',
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_BUS_SVC_L A WHERE NOT EXISTS (SELECT 'X' FROM F1_BUS_SVC B WHERE A.BUS_SVC_CD = B.BUS_SVC_CD) AND A.BUS_SVC_CD IN (
'F1-MCBYTYP',
'F1-MDTTYPES',
'F1-DPLPARTS',
'F1-RETDPDPTY',
'F1-RMSTDS')
/
DELETE FROM F1_IWS_SVC_L A WHERE NOT EXISTS (SELECT 'X' FROM F1_IWS_SVC B WHERE A.IN_SVC_NAME = B.IN_SVC_NAME) AND A.IN_SVC_NAME IN (
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_IWS_SVC_LOG A WHERE NOT EXISTS (SELECT 'X' FROM F1_IWS_SVC B WHERE A.IN_SVC_NAME = B.IN_SVC_NAME) AND A.IN_SVC_NAME IN (
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_IWS_SVC_LOG_PARM A WHERE NOT EXISTS (SELECT 'X' FROM F1_IWS_SVC B WHERE A.IN_SVC_NAME = B.IN_SVC_NAME) AND A.IN_SVC_NAME IN (
'F1-MobileIdentityConfiguration')
/
DELETE FROM F1_MAP_L A WHERE NOT EXISTS (SELECT 'X' FROM F1_MAP B WHERE A.MAP_CD = B.MAP_CD) AND A.MAP_CD IN (
'F1-CreateMDTs',
'F1-DeploymentItemFilter',
'F1-DeploymentPartMaint',
'F1-DeploymentRecordAction',
'F1-DeploymentSearchFilter',
'F1-DepPartEntityAddSrchFltr',
'F1-MDTActionsFrag',
'F1-MDTCloudLogFilter',
'F1-MDTLogFilter',
'F1-MDTLogLaunch',
'F1-MDTLogSearch',
'F1-MDTSelectMDTType',
'F1-MessageFromDeviceMaintUH',
'F1-MobAppInviteContext',
'F1-MobCompDsblFld',
'F1-MobCompSearchFit',
'F1-MobileComponentEditor',
'F1-RemoteMessageRelatedEntity',
'F1-RemoteMessageFilter')
/
DELETE FROM SC_APP_SERVICE_L A WHERE NOT EXISTS (SELECT 'X' FROM SC_APP_SERVICE B WHERE A.APP_SVC_ID = B.APP_SVC_ID) AND A.APP_SVC_ID IN (
'F1-BMCOM',
'F1-RMCRL',
'F1-RMMSG',
'F1DEPTYP',
'F1DPL',
'F1DPLPRT',
'F1DPLQRY',
'F1MDTMNT',
'F1MDTQRY',
'F1MDTTYP',
'F1MOBCMP',
'F1MOBCPQ',
'F1REMSG',
'F1REMSGQ',
'F1-DPLOY',
'F1-DPUTD')
/
-- Delete from CI_USR_ZONE_SAVE, CI_USR_NAV_LINK, (CI_USR_PORTAL?)
DELETE FROM CI_USR_NAV_LINK WHERE NAV_OPT_CD IN (
'f1remsgTabMenu',
'f1remsgqTabMenu',
'f1MDTAdd',
'f1mdtmntTabMenu',
'f1mdtqryTabMenu',
'f1mobcmpTabMenu',
'f1mobcpqTabMenu',
'f1mobcompAdd',
'f1dplTabMenu',
'f1dplqryTabMenu',
'f1deptypTabMenu',
'f1DepTypeAdd',
'f1DplPartAdd',
'f1MDTTypeAdd',
'f1dplprtTabMenu',
'f1mdttypTabMenu')
/

-- Delete from CI_USR_SCR
DELETE FROM CI_USR_SCR WHERE SCR_CD IN (
'F1-CrMDTsBPA',
'F1-CreDplymt',
'F1-DpTyPMain',
'F1-DplMain',
'F1-DplPtMain',
'F1-DpprtAdE',
'F1-MDTLgVw',
'F1-MDTMain',
'F1-MDTTpMain',
'F1-MobCoMain',
'F1-ReMsgMain')
/

-- MOs were coded for GDE but that has been removed.
DELETE FROM F1_DATA_EXPORT_CTRL WHERE MAINT_OBJ_CD IN ('F1-MDT', 'F1-MOBCM', 'F1-DPLTY')
/