allineamento.sql
10.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
var colonna1 varchar2(20);
var colonna2 varchar2(20);
var prefisso varchar2(20);
var numerotel varchar2(20);
prompt ###### - 1) -- Cancellazione vecchi records da test
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE FROM CLL.POSIZB WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
DELETE FROM CLL.POSIZC WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
DELETE FROM CLL.CONTR WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
DELETE FROM CLL.CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S';
COMMIT;
END;
/
prompt ###### - 2) EFFETTUARE LE SELECT IN PRODUZIONE -3) Effettuare le insert in test
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CONTRCLI USING SELECT * FROM CLL.CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S';
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CONTR USING SELECT * FROM CLL.CONTR WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.POSIZB USING SELECT * FROM CLL.POSIZB WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.POSIZC USING SELECT * FROM CLL.POSIZC WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1' AND GSTD_F_ESIST='S');
prompt ###### - 4) reset sicurezza base
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE CLL.SICCLI WHERE CLI_C = '&1';
Insert into CLL.SICCLI (CLI_C, SICCLI_S, GSTD_F_ESIST, GSTD_X_TIP_MODF, GSTD_D_ULT_MODF_RECORD, GSTD_X_USER, GSTD_M_NOM_ULT_MODF, SICCLI_H_CREAZ_PIN, SICCLI_H_INIZ_VAL, SICCLI_H_FINE_VAL, SICCLI_C_PIN_1, SICCLI_S_INVIO_PIN_1, SICCLI_H_INVIO_PIN_1, SICCLI_C_PIN_2, SICCLI_S_INVIO_PIN_2, SICCLI_H_INVIO_PIN_2, SICCLI_C_SBLOCCO, SICCLI_S_SBLOCCO, SICCLI_N_ERRORI_PIN_1, SICCLI_N_ERRORI_PIN_2, SICCLI_S_BLOCCO_PIN_1, SICCLI_S_BLOCCO_PIN_2, SICCLI_H_CREAZ_PIN_1, SICCLI_H_CREAZ_PIN_2)Values('&1', 'X', 'S', 'U', SYSDATE, 'BANCLL23 ', 'ACC-TVER ', TO_DATE('01/09/2007 07:20:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/09/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '28A238F85661F381', 'S', TO_DATE('01/09/2007 07:20:18', 'MM/DD/YYYY HH24:MI:SS'), '28A238F85661F381', 'X', TO_DATE('01/09/2007 07:20:18', 'MM/DD/YYYY HH24:MI:SS'), 'F7574E5AA4C0CAA78849C90C4D689AD7', 'N', 0, 0, 'N', 'N', TO_DATE('01/09/2007 07:20:18', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/09/2007 07:20:18', 'MM/DD/YYYY HH24:MI:SS'));
DELETE ONL.CODICE WHERE IDUTENTE = '&1';
Insert into ONL.CODICE(IDUTENTE, IDCODICE, CODICE, FLGCODICEBLOCCATO, DTINIZIOABILITAZIONECODICE, DTFINEABILITAZIONECODICE) Values ('&1', 1, ' ', 'N', SYSDATE, TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ONL.CODICE(IDUTENTE, IDCODICE, CODICE, FLGCODICEBLOCCATO, DTINIZIOABILITAZIONECODICE, DTFINEABILITAZIONECODICE) Values ('&1', 2, ' ', 'N', SYSDATE, TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
DELETE ONL.UTENTE WHERE IDUTENTE = '&1';
Insert into ONL.UTENTE (IDUTENTE, DESCOGNOME, DESNOME, IDLIVELLO, FLGCONNESSIONE, DTCONNESSIONE, IDCONTEGGIO, DTCONTEGGIO, DTINIZIOVALIDITA, DTFINEVALIDITA) Values ('&1', 'ROSSI', 'ANTONIO', 2, 'S', SYSDATE, 0, TO_DATE('09/10/2014 11:28:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/03/2013 17:30:49', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
END;
/
prompt ###### - 5) ALLINEAMENTO STRONG (test)
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE STRAUT.STA_STATOSOGGETTO where SUBJCOD='&1' and SUBJTYPE='C' and GSTD_F_ESIST='S';
SELECT TRIM(TEL_C_PREF) into :prefisso FROM CLL.TEL WHERE CLI_C=&1 AND TEL_C_TIP='F' AND TEL_C='2';
SELECT TRIM(TEL_X_NUM) into :numerotel FROM CLL.TEL WHERE CLI_C=&1 AND TEL_C_TIP='F' AND TEL_C='2';
INSERT INTO STRAUT.STA_STATOSOGGETTO ("SUBJCOD","SUBJTYPE","GSTD_F_ESIST","GSTD_D_ULT_MODF_RECORD","GSTD_M_NOM_ULT_MODF","GSTD_X_TIP_MODF","GSTD_X_USER","OTPSTATUS","STATUSCOD","TKNSERNUM","TKNBRAND","CELNUM","CELVIRTUAL","VIRTUALBRAND","OTPERROR","TKNDTSPED","TKNDTACTIV","CELDTACTIV","TKNDTLASTUSE","CELDTLASTUSE","DTLASTCHG","LEVDTLASTCHG","TKNDTLASTCHG","CELDTLASTCHG","TKNDTBEGSUSP","TKNDTENDSUSP","TKNDTEXT","TKNDTREQ","SENDTYP","TKNCAUSEXT","QTA_RICH_SCELTA_STRAUT","QTA_RICH_CERTFZ_TELEF_CELL") VALUES('&1','C','S',to_date('24/09/2015 11.51.58','DD/MM/RRRR HH24:MI:SS'),'Sp_VerificPwdSMS','U','Sp_VerificPwdSMS','A','CSNA',NULL,'VAS',:prefisso||:numerotel,'V151000175RESPONLY',NULL,0,NULL,NULL,to_date('8/07/2015 19.05.52','DD/MM/RRRR HH24:MI:SS'),NULL,to_date('24/09/2015 11.51.58','DD/MM/RRRR HH24:MI:SS'),to_date('8/07/2015 19.05.52','DD/MM/RRRR HH24:MI:SS'),to_date('8/07/2015 19.05.52','DD/MM/RRRR HH24:MI:SS'),NULL,NULL,NULL,NULL,NULL,NULL,'N',NULL,NULL,0);
DELETE STRAUT.ANAG_CELL_CERT_CLI where COD_CLI='&1';
INSERT INTO STRAUT.ANAG_CELL_CERT_CLI ("COD_CLI","NUM_PREF_TELEF","NUM_TELEF","COD_CERTFZ","DAT_RICH_CERTFZ","DAT_CERTFZ","COD_STATO_CERTFZ","QTA_ERRORI_CERTFZ","GSTD_M_NOM_ULT_MODF","GSTD_X_USER","GSTD_D_ULT_MODF_RECORD","GSTD_D_INS_RECORD","GSTD_X_TIP_MODF","GSTD_F_ESIST") VALUES ('&1',:prefisso,:numerotel,'775',to_date('9/07/2015 11.26.01','DD/MM/RRRR HH24:MI:SS'),to_date('9/07/2015 11.28.42','DD/MM/RRRR HH24:MI:SS'),'6',0,'SP_CERTIF_CELL ','SP_CREATE_CODE ',to_date('9/07/2015 11.28.42','DD/MM/RRRR HH24:MI:SS'),to_date('9/07/2015 11.26.01','DD/MM/RRRR HH24:MI:SS'),'U','S');
COMMIT;
END;
/
prompt ###### - 6) AGGIORNARE PRIVACY SMS
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE PRIVACY WHERE CLI_C='&1';
COMMIT;
END;
/
prompt ###### - 7) Eseguire select in produzione - 8) Inserire in test I dati della privacy appena recuperate da PRODUZIONE
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT PRIVACY USING SELECT * FROM CLL.PRIVACY WHERE CLI_C='&1';
prompt ###### - 9) Attivazione sms login (da eseguire in test)
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE CLL.CLIINFOSERVICE where CLI_C='&1' AND INFOSERVICE_C='286';
INSERT INTO CLL.CLIINFOSERVICE ("CLI_C","INFOSERVICE_C","GSTD_F_ESIST","GSTD_X_TIP_MODF","GSTD_D_ULT_MODF_RECORD","GSTD_M_NOM_ULT_MODF","GSTD_X_USER","CLIINFOSERVICE_C_STATO","CLIINFOSERVICE_D_INIZ_VAL","CLIINFOSERVICE_D_FINE_VAL","CLIINFOSERVICE_C_STATO_SMS","CLIINFOSERVICE_D_INIZ_VAL_SMS","CLIINFOSERVICE_D_FINE_VAL_SMS","CLIINFOSERVICE_N_PREF","CLIINFOSERVICE_N_NUM","CLIINFOSERVICE_C_STATO_MAIL","CLIINFOSERVICE_D_INIZ_VAL_MAIL","CLIINFOSERVICE_D_FINE_VAL_MAIL","CLIINFOSERVICE_X_MAIL","CLIINFOSERVICE_PROD_C","CLIINFOSERVICE_CONTR_N","CLIINFOSERVICE_X_FREQ","CLIINFOSERVICE_N_CRITERIO","CLIINFOSERVICE_C_SOGLIA","CLIINFOSERVICE_N_LIM_SUP","CLIINFOSERVICE_N_LIM_INF","CLIINFOSERVICE_F_MOD_ATTIV","CLIINFOSERVICE_D_LAST_SEND","CLIINFOSERVICE_F_TIP_SALDO","CLIINFOSERVICE_D_ULT_MODF") VALUES ('&1','286','S','I',to_date('13/04/2010 15.12.02','DD/MM/RRRR HH24:MI:SS'),'ACN','ACN','A',to_date('23/03/2005 00.00.00','DD/MM/RRRR HH24:MI:SS'),to_date('31/12/9999 00.00.00','DD/MM/RRRR HH24:MI:SS'),'A',to_date('23/03/2005 14.30.43','DD/MM/RRRR HH24:MI:SS'),to_date('31/12/9999 00.00.00','DD/MM/RRRR HH24:MI:SS'),'F','2','D',to_date('1/01/1753 00.00.00','DD/MM/RRRR HH24:MI:SS'),to_date('1/01/1753 00.00.00','DD/MM/RRRR HH24:MI:SS'),NULL,'ALL','ALL',NULL,NULL,NULL,NULL,NULL,'N',to_date('24/09/2015 11.48.54','DD/MM/RRRR HH24:MI:SS'),NULL,to_date('23/03/2005 14.30.43','DD/MM/RRRR HH24:MI:SS'));
COMMIT;
END;
/
prompt ###### -10) allineamento TITOLI
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
DELETE FROM CLL.CONTRN_MERCATO WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
DELETE FROM CLL.TRANSCODIFICACLIENTE_TB WHERE USER_ID IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
DELETE FROM CLL.PORTFOLIO_TB WHERE USER_ID IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
DELETE FROM CLL.CANONECLI WHERE CLI_C='&1';
DELETE FROM CLL.CANONEUSERID WHERE CLI_C='&1';
DELETE FROM CLL.CONDSWITCH WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
COMMIT;
END;
/
prompt ###### - 11) Allineamento trading da prod a test
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CONTRN_MERCATO USING SELECT * FROM CLL.CONTRN_MERCATO WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.TRANSCODIFICACLIENTE_TB USING SELECT * FROM CLL.TRANSCODIFICACLIENTE_TB WHERE USER_ID IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.PORTFOLIO_TB USING SELECT * FROM CLL.PORTFOLIO_TB WHERE USER_ID IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CANONECLI USING SELECT * FROM CLL.CANONECLI WHERE CLI_C='&1';
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CANONEUSERID USING SELECT * FROM CLL.CANONEUSERID WHERE CLI_C= '&1';
COPY FROM fbook/fbook@DBAZ_PROD TO internetbank/bank01@172.23.25.33:1521/CD_DBAZST INSERT CLL.CONDSWITCH USING SELECT * FROM CLL.CONDSWITCH WHERE CONTR_N IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
prompt ###### -12) reset password TITOLI IN TEST
CONNECT internetbank/bank01@172.23.25.33:1521/CD_DBAZST;
BEGIN
UPDATE CLL.TRANSCODIFICACLIENTE_TB SET PASSWORD='7B1AABA64F5899CE' WHERE USER_ID IN (SELECT CONTR_N FROM CONTRCLI WHERE CLI_C='&1');
COMMIT;
END;
/
prompt ###### - 13 reset password
BEGIN
update CLL.siccli set siccli_c_pin_1 = '28A238F85661F381', Siccli_C_Pin_2 = 'B37FB351045107C0' where cli_c = '&1';
COMMIT;
UPDATE utente SET IDCONTEGGIO = 0 WHERE idutente = '&1';
COMMIT;
Update Codice Set Flgcodicebloccato = 'N', Dtfineabilitazionecodice = To_Date('31/12/9999', 'DD/MM/YYYY') WHERE idutente = '&1';
COMMIT;
Update Straut.Sta_Statosoggetto Set Otpstatus='A',Otperror='0' where subjcod='&1' and subjtype ='C' and gstd_f_esist = 'S';
COMMIT;
END;
/