scripts/LJCOPY/CCB/FW_TRIM_SCRIPT/FW4202_Trim_SRCH_CHAR_VAL.sql

SPOOL TRIM_SRCH_CHAR_VAL.sql

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

DECLARE

v_tbl_name varchar2(30);
v_fld_name varchar2(30);
v_key varchar2(30);
v_check number(1);
v_precision NUMBER(20,0);

cursor cursor_1 is
WITH A12 AS
(SELECT DISTINCT A.TBL_NAME MAIN1 ,B.TBL_NAME CHAR1,B1.KEY_TYPE_FLG FROM CI_MD_MO_TBL A, CI_MD_MO_TBL B, CI_MD_TBL B1
WHERE TRIM(B.TBL_NAME) IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SRCH_CHAR_VAL'
AND DATA_TYPE = 'VARCHAR2')
AND A.MAINT_OBJ_CD = B.MAINT_OBJ_CD
AND A.TBL_NAME = B1.TBL_NAME
AND A.TBL_ROLE_FLG = 'PRIM')
(SELECT F.CHAR1,CASE CHAR1 WHEN 'SC_USER_CHAR' THEN 'USR' ELSE DECODE(SUBSTR(TRIM(D.FLD_NAME),-2,2),'ID','RNDM','USR') END KEY,D.FLD_NAME,FLD_PRECISION
FROM CI_MD_CONST C, CI_MD_CONST_FLD D, CI_MD_FLD E, A12 F
WHERE C.CONST_ID = D.CONST_ID
AND C.TBL_NAME = F.MAIN1
AND D.FLD_NAME = E.FLD_NAME
AND C.CONST_TYPE_FLG = 'PK'
AND D.FLD_NAME IN (SELECT p.FLD_NAME FROM CI_MD_CONST_FLD p WHERE p.CONST_ID IN (SELECT q.CONST_ID FROM CI_MD_CONST q WHERE q.TBL_NAME = F.CHAR1 AND q.CONST_TYPE_FLG = 'PK'))
AND D.SEQ_NUM = 1
UNION
SELECT F.CHAR1,CASE CHAR1 WHEN 'SC_USER_CHAR' THEN 'USR' ELSE DECODE(SUBSTR(TRIM(D.FLD_NAME),-2,2),'ID','RNDM','USR') END KEY,D.FLD_NAME,FLD_PRECISION
FROM CI_MD_CONST C, CI_MD_CONST_FLD D, CI_MD_FLD E, A12 F
WHERE C.CONST_ID = D.CONST_ID
AND C.TBL_NAME = F.CHAR1
AND D.FLD_NAME = E.FLD_NAME
AND C.CONST_TYPE_FLG = 'PK'
AND D.FLD_NAME not IN (SELECT p.FLD_NAME FROM CI_MD_CONST_FLD p WHERE p.CONST_ID IN (SELECT q.CONST_ID FROM CI_MD_CONST q WHERE q.TBL_NAME = F.MAIN1 AND q.CONST_TYPE_FLG = 'PK'))
AND D.SEQ_NUM = 1);

BEGIN


  SELECT count(1) INTO v_check FROM CI_UT_INSTL_DTL
 WHERE SCRIPT_FILE_NAME = 'FW4202_TRIM_SRCH_CHAR_VAL.SQL'
   AND END_DTTM IS NOT NULL;

   if (v_check = 1) THEN
    DBMS_OUTPUT.PUT_LINE('########################################################################');
    DBMS_OUTPUT.PUT_LINE('Script has already executed once in your environment. No need to re-run.');
    DBMS_OUTPUT.PUT_LINE('########################################################################');
     return;

   else

    INSERT INTO CI_UT_INSTL
(INSTL_LOG_ID,TARGET_VER_ID,UPG_COUNT,PREV_VER_ID,START_DTTM,END_DTTM,RUN_STATUS_FLG,VERSION,SR_NO,OWNER_FLG) SELECT MAX(INSTL_LOG_ID)+1,'V4.2.0.2.0',1,'V4.2.0.2.0',SYSDATE,SYSDATE,'02',1,'*','F1' FROM CI_UT_INSTL;

    INSERT INTO CI_UT_INSTL_DTL
(INSTL_LOG_ID,SEQ_NUM,SCRIPT_FILE_NAME,START_DTTM,END_DTTM,RUN_STATUS_FLG,VERSION,OWNER_FLG) SELECT MAX(INSTL_LOG_ID)+1,1,'FW4202_TRIM_SRCH_CHAR_VAL.SQL',SYSDATE,SYSDATE,'02',1,'F1' FROM CI_UT_INSTL_DTL;
   END IF;


open cursor_1;

  loop
    fetch cursor_1 into v_tbl_name,v_key,v_fld_name,v_precision;
    exit when cursor_1%notfound;

  if ( v_key = 'RNDM') THEN

    FOR A1 IN 0..9 LOOP

DBMS_OUTPUT.PUT_LINE('UPDATE '||v_tbl_name||' A SET A.SRCH_CHAR_VAL = TRIM(A.SRCH_CHAR_VAL) WHERE A.'||v_fld_name||' BETWEEN ''' || RPAD(A1,v_precision,0) || ''' AND ''' || RPAD(A1,v_precision,9) ||''' AND A.CHAR_TYPE_CD = ( SELECT B.CHAR_TYPE_CD FROM CI_CHAR_TYPE B WHERE A.CHAR_TYPE_CD = B.CHAR_TYPE_CD AND (B.CHAR_TYPE_FLG = ''DFV'' OR ( B.CHAR_TYPE_FLG = ''FKV'' AND B.FK_REF_CD IN (SELECT T1.FK_REF_CD FROM CI_FK_REF T1, CI_MD_TBL T2 WHERE T1.TBL_NAME = T2.TBL_NAME AND T1.FK_REF_CD = B.FK_REF_CD AND T2.KEY_TYPE_FLG = ''USR'') ) )) AND TRIM(A.SRCH_CHAR_VAL) IS NOT NULL;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10));
   END LOOP;

  else

DBMS_OUTPUT.PUT_LINE('UPDATE '||v_tbl_name||' A SET A.SRCH_CHAR_VAL = TRIM(A.SRCH_CHAR_VAL) WHERE A.CHAR_TYPE_CD = ( SELECT B.CHAR_TYPE_CD FROM CI_CHAR_TYPE B WHERE A.CHAR_TYPE_CD = B.CHAR_TYPE_CD AND (B.CHAR_TYPE_FLG = ''DFV'' OR ( B.CHAR_TYPE_FLG = ''FKV'' AND B.FK_REF_CD IN (SELECT T1.FK_REF_CD FROM CI_FK_REF T1, CI_MD_TBL T2 WHERE T1.TBL_NAME = T2.TBL_NAME AND T1.FK_REF_CD = B.FK_REF_CD AND T2.KEY_TYPE_FLG = ''USR'') ) )) AND TRIM(A.SRCH_CHAR_VAL) IS NOT NULL;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10));

 END IF;

END LOOP;

close cursor_1;

DBMS_OUTPUT.PUT_LINE('EXIT;');

end;
/

spool off;

exit;