Begin
update dim_ki_measure_query 
     set v_qry_string_1=TO_CLOB('CASE WHEN (select (CASE WHEN (select nvl(sum(nvl(BPFD1743,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG1101 = ''HFT'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(BPFD1743,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG1101 = ''HFT'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) +  (CASE WHEN (select nvl(sum(nvl(MSRG5120,0)),0) from (DEFD1742) where (( HREG5008 = ''N'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG5120,0)),')|| TO_CLOB('0) from (DEFD1742) where (( HREG5008 = ''N'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) +  (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) from dual) is not null THEN (select (CASE WHEN (select nvl(sum(nvl(BPFD1743,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG1101 = ''HFT'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(BPFD1743,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG1101 = ''HFT'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + ')|| TO_CLOB('(CASE WHEN (select nvl(sum(nvl(MSRG5120,0)),0) from (DEFD1742) where (( HREG5008 = ''N'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG5120,0)),0) from (DEFD1742) where (( HREG5008 = ''N'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3547WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END )from dual) ELSE 0 END ')
         
   where v_filter in
         (select distinct kr.ki_definition_key
            from fct_kri_def  kr
           where kr.v_desc_attr_3='FFIEC-041'
             and kr.v_desc_attr_4 like 'RCON3547%'
			 and kr.v_desc_attr_5='2');	
	update dim_ki_measure_query 
     set v_qry_string_1=TO_CLOB('CASE WHEN (select (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG5119,0)),0) from (DEFD1742) where (( HREG5008 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG5119,0)),0) from (DEFD1742) where (( HREG5008 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) +  (CASE WHEN (select nvl(sum(nvl(MSRG5105,0)),0) from (DEFD1751) where (( HREG5004 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG5105,0)),0) from (DEFD1751) where (( HREG5004 = ''Y'' )) and TO_CHAR(Hireg00')|| TO_CLOB('1,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG1501,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''OTH'' , ''NEW'' , ''MSG'' ) AND HREG1101 = ''HFT'' AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG2320 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG1015 = ''DERV'' AND HREG0604 = ''N'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2320 = ''Y'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG3111 = ''Y'' AND HREG2353 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG1501,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''OTH'' , ''NEW'' , ''MSG'' ) AND HREG1101 = ''HFT'' AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG2320 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG1015 = ''DERV'' AND HREG0604 = ''N'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2320 = ''Y'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG3111 = ''Y'' AND HREG2353 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) from dual) is not null THEN (select (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSADJ001,0)),0) from (DEADJ001) where (( HIADJ002 = ''FFIEC-041'' AND HIADJ001 = ''RCON3543WORK'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG5119,0)),0) from (DEFD1742) where (( HREG5008 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not')|| TO_CLOB(' null THEN (select nvl(sum(nvl(MSRG5119,0)),0) from (DEFD1742) where (( HREG5008 = ''Y'' AND HREG3111 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) +   (CASE WHEN (select nvl(sum(nvl(MSRG1501,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''OTH'' , ''NEW'' , ''MSG'' ) AND HREG1101 = ''HFT'' AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG2320 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG1015 = ''DERV'' AND HREG0604 = ''N'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2320 = ''Y'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG3111 = ''Y'' AND HREG2353 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSK')|| TO_CLOB('EY) is not null THEN (select nvl(sum(nvl(MSRG1501,0)),0) from (DEFD1740) where (( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''OTH'' , ''NEW'' , ''MSG'' ) AND HREG1101 = ''HFT'' AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG2320 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 IN ( ''MSG'' , ''OTH'' , ''NEW'' ) AND HREG2304 = ''Y'' AND HREG0502 = ''Y'' AND HREG2336 = ''N'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG1015 = ''DERV'' AND HREG0604 = ''N'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG1101 = ''HFT'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG2320 = ''Y'' AND HREG2353 = ''Y'' AND HREG3111 = ''Y'' ) OR ( HREG2305 = ''N'' AND HREG0604 = ''N'' AND HREG1015 = ''DERV'' AND HREG1343 = ''CONTRA'' AND HREG2304 = ''Y'' AND HREG0502 = ''N'' AND HREG2336 = ''N'' AND HREG3111 = ''Y'' AND HREG2353 = ''Y'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END )from dual) ELSE 0 END ')
         
   where v_filter in
         (select distinct kr.ki_definition_key
            from fct_kri_def  kr
           where kr.v_desc_attr_3='FFIEC-041'
             and kr.v_desc_attr_4 like 'RCON3543%'
			 and kr.v_desc_attr_5='2');	


			 
update DIM_KI_MEASURE_QUERY set v_qry_string_1 = replace(v_qry_string_1,chr(39)||chr(39),chr(39));
update FSI_CELL_BIREPORT_MAP set V_BI_REPORT_NAME = 'LNSSRVCD_1_ACCPARTLVL_1_DRL_DW' where V_BI_REPORT_NAME = 'LOANS_SERVICED_1_DRL_DW';

commit;

exception
  when others then
    rollback ;
end;
/