Begin
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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N'' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N'' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N'' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N'' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) ELSE 0 END ) + (CASE WHEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N'' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) and TO_CHAR(Hireg001,''ddmmyyyy'')=:Mis_Date AND pk_hireg002=:RunSKEY AND pk_hireg004=:LeSKEY) is not null THEN (select nvl(sum(nvl(MSRG1601,0)),0) from (DEFD1920) where (( HREG0538 = ''N'' AND HREG2319 = ''N''')|| TO_CLOB(''' AND HREG2336 = ''N'' AND HREG1101 = ''HTM'' AND HREG2304 = ''N'' AND HREG1015 = ''ASSET'' AND HREG1249 IN ( ''1-4FAMCONLOAN'' , ''OTHCONLOAN'' ) AND HREG1200 = ''IMPR'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) 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 (( HIADJ001 = ''BHCKM709WORK'' AND HIADJ002 = ''FRY-9C'' )) 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 def.ki_definition_key
                      FROM fct_kri_def def
                     WHERE def.v_desc_attr_4 = 'BHCKM709WORK'
                       and def.v_desc_attr_3 = 'FRY-9C'
                       and def.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));

commit;
exception
  when others then
    rollback ;
end;
/