Produced by Araxis Merge on 11/9/2017 4:31:02 PM Central Standard Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.
# | Location | File | Last Modified |
---|---|---|---|
1 | PPS-N_B397.zip\PPS-N_B397\PPS-N_B397\PS_PPS_DataMgmt\src\main\resources\PPSN3.0\DDL\migration_scripts | PPSN_2_migration_v11.sql | Thu Nov 9 15:06:26 2017 UTC |
2 | PPS-N_B397.zip\PPS-N_B397\PPS-N_B397\PS_PPS_DataMgmt\src\main\resources\PPSN3.0\DDL\migration_scripts | PPSN_2_migration_v11.sql | Thu Nov 9 20:35:50 2017 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 3 | 2954 |
Changed | 2 | 4 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
Whitespace | |
---|---|
Character case | Differences in character case are significant |
Line endings | Differences in line endings (CR and LF characters) are ignored |
CR/LF characters | Not shown in the comparison detail |
No regular expressions were active.
1 | SET SERVER OUTPUT ON | |
2 | SET LINESI ZE 2000 | |
3 | SET PAGESI ZE 60 | |
4 | SPOOL PPSN _2_migrati on_v11.log | |
5 | ||
6 | SELECT 'Mi gration St arted: ' | | CURRENT_ TIMESTAMP FROM DUAL; | |
7 | ||
8 | SET ECHO O N | |
9 | ||
10 | REM ###### ########## ########## ########## ########## ########## ########## ######### | |
11 | REM # Des cription: Create PPS -N 2.0 dat abase obje cts | |
12 | REM # | |
13 | REM # Us age: | |
14 | REM # In put Parame ters: | |
15 | REM # 1) None | |
16 | REM # Ou tput: | |
17 | REM # 1) Terminal | |
18 | REM # In ternal Scr ipt Parame ters: | |
19 | REM # 1) None | |
20 | REM # | |
21 | REM ###### ########## ########## ########## ########## ########## ########## ######### | |
22 | REM # Chan ge History | |
23 | REM # | |
24 | REM # DATE Use r Name DESCR IPTION | |
25 | REM # ---- ------ --- ---------- ---- ----- ---------- ---------- ---------- -------- | |
26 | REM # 04/2 9/2014 Dun can Shelle y Creat ed script | |
27 | REM # | |
28 | REM ###### ########## ########## ########## ########## ########## ########## ######## | |
29 | ||
30 | DECLARE | |
31 | -- CONS TANTS | |
32 | ||
33 | cc_sche ma_name CONSTANT V ARCHAR2 (3 5) := 'PPS NEPL'; | |
34 | ||
35 | -- VARI ABLES | |
36 | ||
37 | vc_sql_ statement V ARCHAR2 (4 000); | |
38 | vc_errm V ARCHAR2 (6 4); | |
39 | vn_code N UMBER; | |
40 | vn_cont inue N UMBER; | |
41 | ||
42 | vn_row_ count N UMBER; | |
43 | ||
44 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
45 | -- NAME : check_for_ ppsn2 | |
46 | -- TYPE : Function | |
47 | -- DESC RIPTSION: Check to s ee if PPS_ N 2.0 obje ct already created | |
48 | -- INPU TS: None | |
49 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
50 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
51 | FUNCTIO N check_fo r_ppsn2 | |
52 | RETU RN NUMBER | |
53 | IS | |
54 | BEGIN | |
55 | SELE CT COUNT ( *) | |
56 | IN TO vn_row_ count | |
57 | FR OM sys.dba _tables | |
58 | WHE RE own er = cc_sc hema_name | |
59 | AND tab le_name IN ('EPL_NDF _UPDATE_FI LE' | |
60 | ,'EPL_STA TUS' | |
61 | ,'EPL_IEN _GENERATOR ' | |
62 | ,'EPL_NDF _OUTGOING_ DIFFERENCE S' | |
63 | ,'EPL_VIS TA_UPDATE' | |
64 | ,'EPL_VIS TA_STATUS' | |
65 | ,'TEMP_EP L_NDCS' | |
66 | ,'EPL_PAC KAGE_SIZES ' | |
67 | ,'EPL_DDI _SEVERITY' ); | |
68 | ||
69 | IF v n_row_coun t > 0 | |
70 | THEN | |
71 | R ETURN (1); | |
72 | END IF; | |
73 | ||
74 | SELE CT COUNT ( *) | |
75 | IN TO vn_row_ count | |
76 | FR OM sys.dba _views | |
77 | WHE RE own er = cc_sc hema_name | |
78 | AND vie w_name IN ('FDB_GCNS EQNO_PEM_V ' | |
79 | ,'FDB_GCNS EQNO_PLBLW _V' | |
80 | ,'FDB_MONO GRAPH_PEM_ V' | |
81 | ,'FDB_PLBL WARNINGS_V '); | |
82 | ||
83 | IF v n_row_coun t > 0 | |
84 | THEN | |
85 | R ETURN (1); | |
86 | END IF; | |
87 | ||
88 | RETU RN (0); | |
89 | EXCEPTI ON | |
90 | WHEN OTHERS | |
91 | THEN | |
92 | v n_code := SQLCODE; | |
93 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
94 | D BMS_OUTPUT .put_line ( | |
95 | 'check_f or_ppsn2: Error code ' || vn_c ode || ': ' || vc_er rm); | |
96 | s ys.DBMS_OU TPUT.put_l ine ('ERRO R - EX ITING SCRI PT...'); | |
97 | R ETURN (1); | |
98 | END che ck_for_pps n2; | |
99 | ||
100 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
101 | -- NAME : create_pps n_tables | |
102 | -- TYPE : Function | |
103 | -- DESC RIPTSION: Create PPS -N 2.0 tab les | |
104 | -- INPU TS: None | |
105 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
106 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
107 | FUNCTIO N create_p psn_tables | |
108 | RETU RN NUMBER | |
109 | IS | |
110 | BEGIN | |
111 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
112 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: cre ate_ppsn_t ables'); | |
113 | ||
114 | -- C reate sequ ences sect ion ------ ---------- ---------- ---------- ---------- --- | |
115 | vc_s ql_stateme nt := | |
116 | ' CREATE SEQ UENCE ppsn epl.update _file_sequ ence START WITH 1 IN CREMENT BY 1 NOCACHE NOCYCLE'; | |
117 | ||
118 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
119 | ||
120 | -- C reate tabl es section --------- ---------- ---------- ---------- ---------- | |
121 | ||
122 | -- T able PPSNE PL.EPL_NDF _OUTGOING_ DIFFERENCE S | |
123 | ||
124 | vc_s ql_stateme nt := | |
125 | 'CREATE TABLE ppsn epl.epl_nd f_outgoing _differenc es (' | |
126 | | | ' ndf_ou tgoing_dif ferences_i d NUMBER (38, 0) N OT NULL ' | |
127 | | | ',new_va lue VARCHA R2 (256) NULL ' | |
128 | | | ',old_va lue VARCHA R2 (256) NULL ' | |
129 | | | ',vista_ file_numbe r VARCHA R2 (20) NO T NULL ' | |
130 | | | ',vista_ field_numb er VARCHA R2 (10) NO T NULL ' | |
131 | | | ',vista_ ien VARCHA R2 (50) NO T NULL ' | |
132 | | | ',ndc_up date_file_ fk NUMBER (38, 0) ' | |
133 | | | ',action _type VARCHA R2 (10) NO T NULL ' | |
134 | | | ',create d_by VARCHA R2 (50) NO T NULL ' | |
135 | | | ',create d_dtm TIMEST AMP (6) NO T NULL) ' | |
136 | | | 'TABLESP ACE ppsnep l_data'; | |
137 | ||
138 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
139 | ||
140 | vc_s ql_stateme nt := | |
141 | ' CREATE UNI QUE INDEX ppsnepl.pk ndfoutgoin gdifferenc es ON ppsn epl.epl_nd f_outgoing _differenc es (ndf_ou tgoing_dif ferences_i d) TABLESP ACE ppsnep l_data'; | |
142 | ||
143 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
144 | ||
145 | -- N ew constra int | |
146 | vc_s ql_stateme nt := | |
147 | ' ALTER TABL E PPSNEPL. epl_ndf_ou tgoing_dif ferences A DD CONSTRA INT pkndfo utgoingdif ferences P RIMARY KEY (ndf_outg oing_diffe rences_id) USING IND EX ppsnepl .pkndfoutg oingdiffer ences'; | |
148 | ||
149 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
150 | ||
151 | ||
152 | -- T able PPSNE PL.EPL_IEN _GENERATOR | |
153 | ||
154 | vc_s ql_stateme nt := | |
155 | 'CREATE TABLE ppsn epl.epl_ie n_generato r ( ' | |
156 | | | 'CONCEPT VARCHAR 2(50) NOT NULL, ' | |
157 | | | 'LAST_US ED_IEN NUMBER( 30 , 0) NO T NULL, ' | |
158 | | | 'CREATED _BY VARCHAR 2(50) NOT NULL, ' | |
159 | | | 'CREATED _DTM TIMESTA MP NOT NUL L, ' | |
160 | | | 'LAST_MO DIFIED_BY VARCHAR 2(50), ' | |
161 | | | 'LAST_MO DIFIED_DTM TIMESTA MP) ' | |
162 | | | 'TABLESP ACE ppsnep l_data'; | |
163 | ||
164 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
165 | ||
166 | vc_s ql_stateme nt := | |
167 | ' CREATE IND EX PPSNEPL .EPL_IEN_P K_IDX ON P PSNEPL.EPL _IEN_GENER ATOR (CONC EPT ASC) T ABLESPACE ppsnepl_da ta'; | |
168 | ||
169 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
170 | ||
171 | -- N ew constra int | |
172 | vc_s ql_stateme nt := | |
173 | ' ALTER TABL E PPSNEPL. EPL_IEN_GE NERATOR AD D CONSTRAI NT EPL_IEN _PK PRIMAR Y KEY (CON CEPT) USIN G INDEX PP SNEPL.EPL_ IEN_PK_IDX '; | |
174 | ||
175 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
176 | ||
177 | ||
178 | -- T able PPSNE PL.EPL_NDF _UPDATE_FI LE | |
179 | ||
180 | vc_s ql_stateme nt := | |
181 | 'CREATE TABLE ppsn epl.epl_nd f_update_f ile( ' | |
182 | | | ' ndf_up date_file_ id N UMERIC (30 ) NOT NULL ' | |
183 | | | ',file_n ame V ARCHAR (20 0) ' | |
184 | | | ',commen ts V ARCHAR (20 00) ' | |
185 | | | ',status _id_fk N UMERIC (30 ) NOT NULL ' | |
186 | | | ',prod_t ransmissio n_date T IMESTAMP ' | |
187 | | | ',test_t ransmissio n_date T IMESTAMP ' | |
188 | | | ',status _modified_ date T IMESTAMP ' | |
189 | | | ',file_d irectory_p ath V ARCHAR (50 0) ' | |
190 | | | ',create d_by V ARCHAR (50 ) NOT NULL ' | |
191 | | | ',create d_dtm T IMESTAMP N OT NULL ' | |
192 | | | ',last_m odified_by V ARCHAR (50 ) ' | |
193 | | | ',last_m odified_dt m T IMESTAMP) ' | |
194 | | | 'TABLESP ACE ppsnep l_data'; | |
195 | ||
196 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
197 | ||
198 | vc_s ql_stateme nt := | |
199 | ' CREATE UNI QUE INDEX ppsnepl.pk ndfupdatef ile ON pps nepl.epl_n df_update_ file (ndf_ update_fil e_id) TABL ESPACE pps nepl_data' ; | |
200 | ||
201 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
202 | ||
203 | vc_s ql_stateme nt := | |
204 | ' CREATE IND EX ppsnepl .idxstatus _id ON pps nepl.epl_n df_update_ file (stat us_id_fk) TABLESPACE ppsnepl_d ata'; | |
205 | ||
206 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
207 | ||
208 | vc_s ql_stateme nt := | |
209 | ' ALTER TABL E ppsnepl. epl_ndf_up date_file ADD CONSTR AINT pkndf updatefile PRIMARY K EY (ndf_up date_file_ id) USING INDEX ppsn epl.pkndfu pdatefile' ; | |
210 | ||
211 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
212 | ||
213 | ||
214 | -- T able PPSNE PL.EPL_STA TUS | |
215 | ||
216 | vc_s ql_stateme nt := | |
217 | 'CREATE TABLE ppsn epl.epl_st atus( ' | |
218 | | | ' status _id NUMERI C (30) NOT NULL ' | |
219 | | | ',status _name VARCHA R (200) ' | |
220 | | | ',status _desc VARCHA R (1000) ' | |
221 | | | ',create d_by VARCHA R (50) NOT NULL ' | |
222 | | | ',create d_dtm TIMEST AMP NOT NU LL ' | |
223 | | | ',last_m odified_by VARCHA R (50) ' | |
224 | | | ',last_m odified_dt m TIMEST AMP) ' | |
225 | | | ' TABLES PACE ppsne pl_data'; | |
226 | ||
227 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
228 | ||
229 | vc_s ql_stateme nt := | |
230 | ' CREATE UNI QUE INDEX ppsnepl.pk status ON ppsnepl.ep l_status ( status_id) TABLESPAC E ppsnepl_ data'; | |
231 | ||
232 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
233 | ||
234 | vc_s ql_stateme nt := | |
235 | ' ALTER TABL E ppsnepl. epl_status ADD CONST RAINT pkst atus PRIMA RY KEY (st atus_id) U SING INDEX ppsnepl.p kstatus'; | |
236 | ||
237 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
238 | ||
239 | ||
240 | -- T able PPSNE PL.EPL_VIS TA_UPDATE | |
241 | ||
242 | vc_s ql_stateme nt := | |
243 | 'CREATE TABLE ppsn epl.epl_vi sta_update ( ' | |
244 | | | ' vista_ update_id NUMERI C (30) NOT NULL ' | |
245 | | | ',file_f k NUMERI C (30) NOT NULL ' | |
246 | | | ',site NUMERI C (30) NOT NULL ' | |
247 | | | ',commen ts VARCHA R (2000) ' | |
248 | | | ',vista_ status_id_ fk NUMERI C (30) NOT NULL ' | |
249 | | | ',transm ission_dat e TIMEST AMP) ' | |
250 | | | 'TABLESP ACE ppsnep l_data'; | |
251 | ||
252 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
253 | ||
254 | vc_s ql_stateme nt := | |
255 | ' CREATE UNI QUE INDEX ppsnepl.pk vistaupdat e ON ppsne pl.epl_vis ta_update (vista_upd ate_id) TA BLESPACE p psnepl_dat a'; | |
256 | ||
257 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
258 | ||
259 | vc_s ql_stateme nt := | |
260 | ' CREATE IND EX ppsnepl .idxvistas tatusid ON ppsnepl.e pl_vista_u pdate (vis ta_status_ id_fk) TAB LESPACE pp snepl_data '; | |
261 | ||
262 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
263 | ||
264 | vc_s ql_stateme nt := | |
265 | ' ALTER TABL E ppsnepl. epl_vista_ update ADD CONSTRAIN T pkvistau pdate PRIM ARY KEY (v ista_updat e_id) USIN G INDEX pp snepl.pkvi staupdate' ; | |
266 | ||
267 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
268 | ||
269 | ||
270 | -- T able PPSNE PL.EPL_VIS TA_STATUS | |
271 | ||
272 | vc_s ql_stateme nt := | |
273 | 'CREATE TABLE ppsn epl.epl_vi sta_status ( ' | |
274 | | | ' vista_ status_id NUMERI C (30) NOT NULL ' | |
275 | | | ',status _name VARCHA R (200) ' | |
276 | | | ',status _desc VARCHA R (1000) ' | |
277 | | | ',create d_by VARCHA R (50) NOT NULL ' | |
278 | | | ',create d_dtm TIMEST AMP NOT NU LL ' | |
279 | | | ',last_m odified_by VARCHA R (50) ' | |
280 | | | ',last_m odified_dt m TIMEST AMP) ' | |
281 | | | 'TABLESP ACE ppsnep l_data'; | |
282 | ||
283 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
284 | ||
285 | vc_s ql_stateme nt := | |
286 | ' CREATE UNI QUE INDEX ppsnepl.pk vistastatu s ON ppsne pl.epl_vis ta_status (vista_sta tus_id) TA BLESPACE p psnepl_dat a'; | |
287 | ||
288 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
289 | ||
290 | vc_s ql_stateme nt := | |
291 | ' ALTER TABL E ppsnepl. epl_vista_ status ADD CONSTRAIN T pkvistas tatus PRIM ARY KEY (v ista_statu s_id) USIN G INDEX pp snepl.pkvi stastatus' ; | |
292 | ||
293 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
294 | ||
295 | ||
296 | -- T able PPSNE PL.EPL_DDI _SEVERITY | |
297 | ||
298 | vc_s ql_stateme nt := | |
299 | 'CREATE TABLE ppsn epl.epl_dd i_severity ( ' | |
300 | | | ' epl_id NUMBER (30, 0) N OT NULL ' | |
301 | | | ' ,name VARCH AR2 (50) N OT NULL ' | |
302 | | | ' ,creat ed_by VARCH AR2 (50) N OT NULL ' | |
303 | | | ' ,creat ed_dtm TIMES TAMP (6) N OT NULL ' | |
304 | | | ' ,last_ modified_b y VARCH AR2 (50) ' | |
305 | | | ' ,last_ modified_d tm TIMES TAMP (6)) ' | |
306 | | | ' TABLES PACE ppsne pl_data'; | |
307 | ||
308 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
309 | ||
310 | vc_s ql_stateme nt := | |
311 | ' CREATE UNI QUE INDEX ppsnepl.ep l_ddi_seve rity_pk ON ppsnepl.e pl_ddi_sev erity (epl _id) TABLE SPACE ppsn epl_data'; | |
312 | ||
313 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
314 | ||
315 | vc_s ql_stateme nt := | |
316 | ' ALTER TABL E ppsnepl. epl_ddi_se verity ADD CONSTRAIN T epl_ddi_ severity_p k PRIMARY KEY (epl_i d) USING I NDEX ppsne pl.epl_ddi _severity_ pk'; | |
317 | ||
318 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
319 | ||
320 | ||
321 | -- T able PPSNE PL.EPL_DRU G_DRUG_INT ERACTION | |
322 | ||
323 | vc_s ql_stateme nt := | |
324 | 'CREATE TABLE ppsn epl.epl_dr ug_drug_in teraction( ' | |
325 | | | ' epl_dr ug_drug_in teraction_ id NUMBE R (38) NOT NULL ' | |
326 | | | ' ,inter action_nam e VARC HAR2 (200) NOT NULL ' | |
327 | | | ' ,dru g_ingredie nt_1 NU MBER (30, 0) NOT NUL L ' | |
328 | | | ' ,dru g_ingredie nt_2 NU MBER (30, 0) NOT NUL L ' | |
329 | | | ' ,sev erity NU MBER (30, 0) NOT NUL L ' | |
330 | | | ' ,nat ionally_en tered NU MBER (1, 0 ) ' | |
331 | | | ' ,tot al_indexes NU MBER (38, 0) ' | |
332 | | | ' ,loc ally_edite d NU MBER (1, 0 ) ' | |
333 | | | ' ,vis ta_ien NU MBER(30 , 0) NOT NUL L ' | |
334 | | | ' ,cre ated_by VA RCHAR2 (50 ) NOT NULL ' | |
335 | | | ' ,cre ated_dtm TI MESTAMP (6 ) NOT NULL ' | |
336 | | | ' ,ina ctivation_ date TI MESTAMP (6 ) ' | |
337 | | | ' ,las t_modified _by VA RCHAR2 (50 ) ' | |
338 | | | ' ,las t_modified _dtm TI MESTAMP (6 )) ' | |
339 | | | ' TABLES PACE ppsne pl_data'; | |
340 | ||
341 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
342 | ||
343 | vc_s ql_stateme nt := | |
344 | ' ALTER TABL E ppsnepl. epl_drug_d rug_intera ction ADD CONSTRAINT uk_epl_dd i_1 UNIQUE (drug_ing redient_1, drug_ingr edient_2, severity)' ; | |
345 | ||
346 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
347 | ||
348 | vc_s ql_stateme nt := | |
349 | ' CREATE UNI QUE INDEX ppsnepl.ep ldrugdrugi nteraction id_pk ON p psnepl.epl _drug_drug _interacti on (epl_dr ug_drug_in teraction_ id) TABLES PACE ppsne pl_data'; | |
350 | ||
351 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
352 | ||
353 | vc_s ql_stateme nt := | |
354 | ' CREATE IND EX ppsnepl .drug_ingr edient_1_i dx ON ppsn epl.epl_dr ug_drug_in teraction (drug_ingr edient_1) TABLESPACE ppsnepl_d ata'; | |
355 | ||
356 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
357 | ||
358 | vc_s ql_stateme nt := | |
359 | ' CREATE IND EX ppsnepl .drug_ingr edient_2_i dx ON ppsn epl.epl_dr ug_drug_in teraction (drug_ingr edient_2) TABLESPACE ppsnepl_d ata'; | |
360 | ||
361 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
362 | ||
363 | vc_s ql_stateme nt := | |
364 | ' CREATE IND EX ppsnepl .severity_ idx ON pps nepl.epl_d rug_drug_i nteraction (severity ) TABLESPA CE ppsnepl _data'; | |
365 | ||
366 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
367 | ||
368 | vc_s ql_stateme nt := | |
369 | ' ALTER TABL E ppsnepl. epl_drug_d rug_intera ction ADD CONSTRAINT epldrugdr uginteract ionid_pk PRIMARY KE Y (epl_dru g_drug_int eraction_i d) USING I NDEX ppsne pl.epldrug drugintera ctionid_pk '; | |
370 | ||
371 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
372 | ||
373 | -- C reate syno nyms secti on ------- ---------- ---------- ---------- ---------- -- | |
374 | ||
375 | -- C reate rela tionships section -- ---------- ---------- ---------- ---------- ------- | |
376 | ||
377 | vc_s ql_stateme nt := | |
378 | ' ALTER TABL E ppsnepl. epl_ndf_ou tgoing_dif ferences A DD (CONSTR AINT fk_pk ndfoutgoin gfile FORE IGN KEY (n dc_update_ file_fk) R EFERENCES ppsnepl.ep l_ndf_upda te_file (n df_update_ file_id))' ; | |
379 | ||
380 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
381 | ||
382 | vc_s ql_stateme nt := | |
383 | ' ALTER TABL E ppsnepl. epl_ndf_up date_file ADD (CONST RAINT fk_n dfupdatefi le_status FOREIGN KE Y (status_ id_fk) REF ERENCES pp snepl.epl_ status (st atus_id))' ; | |
384 | ||
385 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
386 | ||
387 | vc_s ql_stateme nt := | |
388 | ' ALTER TABL E ppsnepl. epl_vista_ update ADD (CONSTRAI NT fk_vist aupdate_vi stastatus FOREIGN KE Y (vista_s tatus_id_f k) REFEREN CES ppsnep l.epl_vist a_status ( vista_stat us_id))'; | |
389 | ||
390 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
391 | ||
392 | vc_s ql_stateme nt := | |
393 | ' ALTER TABL E ppsnepl. epl_drug_d rug_intera ction ADD CONSTRAINT fk_epl_dd i_epl_ingr edients_1 FOREIGN KE Y (drug_in gredient_1 ) REFERENC ES ppsnepl .epl_ingre dients (ep l_id)'; | |
394 | ||
395 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
396 | ||
397 | vc_s ql_stateme nt := | |
398 | ' ALTER TABL E ppsnepl. epl_drug_d rug_intera ction ADD CONSTRAINT fk_epl_dd i_epl_ingr edients_2 FOREIGN KE Y (drug_in gredient_2 ) REFERENC ES ppsnepl .epl_ingre dients (ep l_id)'; | |
399 | ||
400 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
401 | ||
402 | vc_s ql_stateme nt := | |
403 | ' ALTER TABL E ppsnepl. epl_drug_d rug_intera ction ADD CONSTRAINT fk_epl_dd i_epl_seve rity FOREI GN KEY (se verity) RE FERENCES p psnepl.epl _ddi_sever ity (epl_i d)'; | |
404 | ||
405 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
406 | ||
407 | -- G rant permi ssions sec tion ----- ---------- ---------- ---------- ---------- ---- | |
408 | ||
409 | vc_s ql_stateme nt := | |
410 | ' GRANT SELE CT ON ppsn epl.epl_nd f_outgoing _differenc es TO PPSN EPL_READ_O NLY_ROLE'; | |
411 | ||
412 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
413 | ||
414 | vc_s ql_stateme nt := | |
415 | ' GRANT SELE CT ON ppsn epl.epl_ie n_generato r TO PPSNE PL_READ_ON LY_ROLE'; | |
416 | ||
417 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
418 | ||
419 | vc_s ql_stateme nt := | |
420 | ' GRANT SELE CT ON ppsn epl.epl_nd f_update_f ile TO PPS NEPL_READ_ ONLY_ROLE' ; | |
421 | ||
422 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
423 | ||
424 | vc_s ql_stateme nt := | |
425 | ' GRANT SELE CT ON ppsn epl.epl_st atus TO PP SNEPL_READ _ONLY_ROLE '; | |
426 | ||
427 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
428 | ||
429 | vc_s ql_stateme nt := | |
430 | ' GRANT SELE CT ON ppsn epl.epl_vi sta_update TO PPSNEP L_READ_ONL Y_ROLE'; | |
431 | ||
432 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
433 | ||
434 | vc_s ql_stateme nt := | |
435 | ' GRANT SELE CT ON ppsn epl.epl_vi sta_status TO PPSNEP L_READ_ONL Y_ROLE'; | |
436 | ||
437 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
438 | ||
439 | vc_s ql_stateme nt := | |
440 | ' GRANT SELE CT ON ppsn epl.epl_dd i_severity TO PPSNEP L_READ_ONL Y_ROLE'; | |
441 | ||
442 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
443 | ||
444 | vc_s ql_stateme nt := | |
445 | ' GRANT SELE CT ON ppsn epl.epl_dr ug_drug_in teraction TO PPSNEPL _READ_ONLY _ROLE'; | |
446 | ||
447 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
448 | ||
449 | vc_s ql_stateme nt := | |
450 | ' GRANT SELE CT ON ppsn epl.update _file_sequ ence TO PP SNEPL_READ _ONLY_ROLE '; | |
451 | ||
452 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
453 | ||
454 | ||
455 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: creat e_ppsn_tab les'); | |
456 | RETU RN (0); | |
457 | EXCEPTI ON | |
458 | WHEN OTHERS | |
459 | THEN | |
460 | v n_code := SQLCODE; | |
461 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
462 | s ys.DBMS_OU TPUT.put_l ine ( | |
463 | 'create_ ppsn_table s: Error c ode ' || v n_code || ': ' || vc _errm); | |
464 | s ys.DBMS_OU TPUT.put_l ine ( | |
465 | 'create_ ppsn_table s: last va lue for vc _sql_state ment '); | |
466 | s ys.DBMS_OU TPUT.put_l ine ( | |
467 | 'create_ ppsn_table s: ' || vc _sql_state ment); | |
468 | R ETURN (1); | |
469 | END cre ate_ppsn_t ables; | |
470 | ||
471 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
472 | -- NAME : create_pps n_views | |
473 | -- TYPE : Function | |
474 | -- DESC RIPTSION: Create PPS -N 2.0 vie ws | |
475 | -- INPU TS: None | |
476 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
477 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
478 | FUNCTIO N create_p psn_views | |
479 | RETU RN NUMBER | |
480 | IS | |
481 | BEGIN | |
482 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
483 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: cre ate_ppsn_v iews'); | |
484 | ||
485 | -- C reate view s section ---------- ---------- ---------- ---------- --------- | |
486 | ||
487 | -- V iew PPSNEP L.FDB_GCNS EQNO_PEM_V | |
488 | ||
489 | vc_s ql_stateme nt := | |
490 | ' GRANT SELE CT ON fdb_ dif.fdb_gc nseqno_pem TO ppsnep l WITH GRA NT OPTION' ; | |
491 | ||
492 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
493 | ||
494 | vc_s ql_stateme nt := | |
495 | ' CREATE VIE W ppsnepl. fdb_gcnseq no_pem_v A S SELECT * FROM fdb_ dif.fdb_gc nseqno_pem '; | |
496 | ||
497 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
498 | ||
499 | vc_s ql_stateme nt := | |
500 | ' GRANT SELE CT ON ppsn epl.fdb_gc nseqno_pem _v TO ppsn epl_read_o nly_role'; | |
501 | ||
502 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
503 | ||
504 | ||
505 | -- V iew FDB_GC NSEQNO_PLB LW_V | |
506 | ||
507 | vc_s ql_stateme nt := | |
508 | ' GRANT SELE CT ON fdb_ dif.fdb_gc nseqno_plb lw TO ppsn epl WITH G RANT OPTIO N'; | |
509 | ||
510 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
511 | ||
512 | vc_s ql_stateme nt := | |
513 | ' CREATE VIE W ppsnepl. fdb_gcnseq no_plblw_v AS SELECT * FROM fd b_dif.fdb_ gcnseqno_p lblw'; | |
514 | ||
515 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
516 | ||
517 | vc_s ql_stateme nt := | |
518 | ' GRANT SELE CT ON ppsn epl.fdb_gc nseqno_plb lw_v TO pp snepl_read _only_role '; | |
519 | ||
520 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
521 | ||
522 | ||
523 | -- V iew PPSNEP L.FDB_MONO GRAPH_PEM_ V | |
524 | ||
525 | vc_s ql_stateme nt := | |
526 | ' GRANT SELE CT ON fdb_ dif.fdb_mo nograph_pe m TO ppsne pl WITH GR ANT OPTION '; | |
527 | ||
528 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
529 | ||
530 | vc_s ql_stateme nt := | |
531 | ' CREATE VIE W ppsnepl. fdb_monogr aph_pem_v AS SELECT * FROM fdb _dif.fdb_m onograph_p em'; | |
532 | ||
533 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
534 | ||
535 | vc_s ql_stateme nt := | |
536 | ' GRANT SELE CT ON ppsn epl.fdb_mo nograph_pe m_v TO pps nepl_read_ only_role' ; | |
537 | ||
538 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
539 | ||
540 | ||
541 | -- V iew PPSNEP L.FDB_PLBL WARNINGS_V | |
542 | ||
543 | vc_s ql_stateme nt := | |
544 | ' GRANT SELE CT ON fdb_ dif.fdb_pl blwarnings TO ppsnep l WITH GRA NT OPTION' ; | |
545 | ||
546 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
547 | ||
548 | vc_s ql_stateme nt := | |
549 | ' CREATE VIE W ppsnepl. fdb_plblwa rnings_v A S SELECT * FROM fdb_ dif.fdb_pl blwarnings '; | |
550 | ||
551 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
552 | ||
553 | vc_s ql_stateme nt := | |
554 | ' GRANT SELE CT ON ppsn epl.fdb_pl blwarnings _v TO ppsn epl_read_o nly_role'; | |
555 | ||
556 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
557 | ||
558 | ||
559 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: creat e_ppsn_vie ws'); | |
560 | RETU RN (0); | |
561 | EXCEPTI ON | |
562 | WHEN OTHERS | |
563 | THEN | |
564 | v n_code := SQLCODE; | |
565 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
566 | s ys.DBMS_OU TPUT.put_l ine ( | |
567 | 'create_ ppsn_views : Error co de ' || vn _code || ' : ' || vc_ errm); | |
568 | s ys.DBMS_OU TPUT.put_l ine ( | |
569 | 'create_ ppsn_views : last val ue for vc_ sql_statem ent '); | |
570 | s ys.DBMS_OU TPUT.put_l ine ('crea te_ppsn_vi ews: ' || vc_sql_sta tement); | |
571 | R ETURN (1); | |
572 | END cre ate_ppsn_v iews; | |
573 | ||
574 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
575 | -- NAME : insert_pps n2_data | |
576 | -- TYPE : Function | |
577 | -- DESC RIPTSION: INSERT PPS -N 2.0 dat a into tab les | |
578 | -- INPU TS: None | |
579 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
580 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
581 | FUNCTIO N insert_p psn2_data | |
582 | RETU RN NUMBER | |
583 | IS | |
584 | BEGIN | |
585 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
586 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ins ert_ppsn2_ data'); | |
587 | ||
588 | vc_s ql_stateme nt := | |
589 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
590 | | | 'VALUES (1,''Initi ated'',''F ile Creati on Process Has Been Initiated' ',''Initia l Load'',s ysdate)'; | |
591 | ||
592 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
593 | ||
594 | sys. DBMS_OUTPU T.put_line ( | |
595 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
596 | | | TO_CHAR (SQL%ROWCO UNT)); | |
597 | ||
598 | vc_s ql_stateme nt := | |
599 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
600 | | | 'VALUES (2,''Creat ed'',''All changes t o drug inf ormation i n PPS-N ha ve been co llected in to a file and placed on the fi le system' ',''Initia l Load'',s ysdate)'; | |
601 | ||
602 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
603 | ||
604 | sys. DBMS_OUTPU T.put_line ( | |
605 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
606 | | | TO_CHAR (SQL%ROWCO UNT)); | |
607 | ||
608 | vc_s ql_stateme nt := | |
609 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
610 | | | 'VALUES (3,''Trans mitted to Test FTP' ',''Transm ission of the Create d file to the test F TP locatio n has been completed '',''Initi al Load'', sysdate)'; | |
611 | ||
612 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
613 | ||
614 | sys. DBMS_OUTPU T.put_line ( | |
615 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
616 | | | TO_CHAR (SQL%ROWCO UNT)); | |
617 | ||
618 | vc_s ql_stateme nt := | |
619 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
620 | | | 'VALUES (4,''Start of Nation al VistA P rocessing' ',''The Te st VistA i nstance ha s started the update file proc ess'',''In itial Load '',sysdate )'; | |
621 | ||
622 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
623 | ||
624 | sys. DBMS_OUTPU T.put_line ( | |
625 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
626 | | | TO_CHAR (SQL%ROWCO UNT)); | |
627 | ||
628 | vc_s ql_stateme nt := | |
629 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
630 | | | 'VALUES (5,''Compl etion of N ational Vi stA Proces sing'',''T he Test Vi stA instan ce has pro cessed the update fi le and is ready for testing. A user may now access the Updat e approval functiona lity of PP S-N'',''In itial Load '',sysdate )'; | |
631 | ||
632 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
633 | ||
634 | sys. DBMS_OUTPU T.put_line ( | |
635 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
636 | | | TO_CHAR (SQL%ROWCO UNT)); | |
637 | ||
638 | vc_s ql_stateme nt := | |
639 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
640 | | | 'VALUES (6,''Appro ved by Tes t'',''A us er has mar ked this u pdate as A pproved in the updat e manageme nt area of PPS-N'',' 'Initial L oad'',sysd ate)'; | |
641 | ||
642 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
643 | ||
644 | sys. DBMS_OUTPU T.put_line ( | |
645 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
646 | | | TO_CHAR (SQL%ROWCO UNT)); | |
647 | ||
648 | vc_s ql_stateme nt := | |
649 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
650 | | | 'VALUES (7,''Rejec ted by Tes t'',''A us er has mar ked this u pdate as R ejected in the updat e manageme nt area of PPS-N'',' 'Initial L oad'',sysd ate)'; | |
651 | ||
652 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
653 | ||
654 | sys. DBMS_OUTPU T.put_line ( | |
655 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
656 | | | TO_CHAR (SQL%ROWCO UNT)); | |
657 | ||
658 | vc_s ql_stateme nt := | |
659 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
660 | | | 'VALUES (8,''Trans mitted to Production VistA'',' 'Transmiss ion of the approved file to th e producti on FTP loc ation has been compl eted'',''I nitial Loa d'',sysdat e)'; | |
661 | ||
662 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
663 | ||
664 | sys. DBMS_OUTPU T.put_line ( | |
665 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
666 | | | TO_CHAR (SQL%ROWCO UNT)); | |
667 | ||
668 | vc_s ql_stateme nt := | |
669 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
670 | | | 'VALUES (99,''NDF Update Pro cess Error '',''An er ror occurr ed during NDF update file proc ess'',''In itial Load '',sysdate )'; | |
671 | ||
672 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
673 | ||
674 | sys. DBMS_OUTPU T.put_line ( | |
675 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
676 | | | TO_CHAR (SQL%ROWCO UNT)); | |
677 | ||
678 | COMM IT; | |
679 | ||
680 | vc_s ql_stateme nt := | |
681 | 'INSERT INTO ppsne pl.epl_ndf _update_fi le (NDF_UP DATE_FILE_ ID,FILE_NA ME,COMMENT S,STATUS_I D_FK,PROD_ TRANSMISSI ON_DATE,TE ST_TRANSMI SSION_DATE ,STATUS_MO DIFIED_DAT E,FILE_DIR ECTORY_PAT H,CREATED_ BY,CREATED _DTM,LAST_ MODIFIED_B Y,LAST_MOD IFIED_DTM) ' | |
682 | | | ' VALUES (0,''PPS_ 0PRV_0NEW. DAT'',''In itial Seed Data'',8, SYSDATE,SY SDATE,SYSD ATE,''PPS/ PROD'',''D eveloper'' ,SYSDATE,' 'Developer '',SYSDATE )'; | |
683 | ||
684 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
685 | ||
686 | sys. DBMS_OUTPU T.put_line ( | |
687 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ndf_upda te_file : ' | |
688 | | | TO_CHAR (SQL%ROWCO UNT)); | |
689 | ||
690 | ||
691 | COMM IT; | |
692 | ||
693 | vc_s ql_stateme nt := | |
694 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
695 | | | 'VALUES (''EPL_DRU G_UNITS'', 10000, '' DEV'', SYS DATE, ''DE V'', SYSDA TE)'; | |
696 | ||
697 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
698 | ||
699 | sys. DBMS_OUTPU T.put_line ( | |
700 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
701 | | | TO_CHAR (SQL%ROWCO UNT)); | |
702 | ||
703 | vc_s ql_stateme nt := | |
704 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
705 | | | 'VALUES (''EPL_DOS AGE_FORMS' ', 10000, ''DEV'', S YSDATE, '' DEV'', SYS DATE)'; | |
706 | ||
707 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
708 | ||
709 | sys. DBMS_OUTPU T.put_line ( | |
710 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
711 | | | TO_CHAR (SQL%ROWCO UNT)); | |
712 | ||
713 | vc_s ql_stateme nt := | |
714 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
715 | | | 'VALUES (''EPL_ING REDIENTS'' , 10000, ' 'DEV'', SY SDATE, ''D EV'', SYSD ATE)'; | |
716 | ||
717 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
718 | ||
719 | sys. DBMS_OUTPU T.put_line ( | |
720 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
721 | | | TO_CHAR (SQL%ROWCO UNT)); | |
722 | ||
723 | vc_s ql_stateme nt := | |
724 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
725 | | | 'VALUES (''EPL_MAN UFACTURERS '', 10000, ''DEV'', SYSDATE, ' 'DEV'', SY SDATE)'; | |
726 | ||
727 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
728 | ||
729 | sys. DBMS_OUTPU T.put_line ( | |
730 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
731 | | | TO_CHAR (SQL%ROWCO UNT)); | |
732 | ||
733 | vc_s ql_stateme nt := | |
734 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
735 | | | 'VALUES (''EPL_NDC S'', 10000 , ''DEV'', SYSDATE, ''DEV'', S YSDATE)'; | |
736 | ||
737 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
738 | ||
739 | sys. DBMS_OUTPU T.put_line ( | |
740 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
741 | | | TO_CHAR (SQL%ROWCO UNT)); | |
742 | ||
743 | vc_s ql_stateme nt := | |
744 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
745 | | | 'VALUES (''EPL_PAC KAGE_TYPES '', 10000, ''DEV'', SYSDATE, ' 'DEV'', SY SDATE)'; | |
746 | ||
747 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
748 | ||
749 | sys. DBMS_OUTPU T.put_line ( | |
750 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
751 | | | TO_CHAR (SQL%ROWCO UNT)); | |
752 | ||
753 | vc_s ql_stateme nt := | |
754 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
755 | | | 'VALUES (''EPL_PRO DUCTS'', 1 0000, ''DE V'', SYSDA TE, ''DEV' ', SYSDATE )'; | |
756 | ||
757 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
758 | ||
759 | sys. DBMS_OUTPU T.put_line ( | |
760 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
761 | | | TO_CHAR (SQL%ROWCO UNT)); | |
762 | ||
763 | vc_s ql_stateme nt := | |
764 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
765 | | | 'VALUES (''EPL_VA_ DISPENSE_U NITS'', 10 000, ''DEV '', SYSDAT E, ''DEV'' , SYSDATE) '; | |
766 | ||
767 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
768 | ||
769 | sys. DBMS_OUTPU T.put_line ( | |
770 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
771 | | | TO_CHAR (SQL%ROWCO UNT)); | |
772 | ||
773 | vc_s ql_stateme nt := | |
774 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
775 | | | 'VALUES (''EPL_VA_ DRUG_CLASS ES'', 1000 0, ''DEV'' , SYSDATE, ''DEV'', SYSDATE)'; | |
776 | ||
777 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
778 | ||
779 | sys. DBMS_OUTPU T.put_line ( | |
780 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
781 | | | TO_CHAR (SQL%ROWCO UNT)); | |
782 | ||
783 | vc_s ql_stateme nt := | |
784 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
785 | | | 'VALUES (''EPL_VA_ GEN_NAMES' ', 10000, ''DEV'', S YSDATE, '' DEV'', SYS DATE)'; | |
786 | ||
787 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
788 | ||
789 | sys. DBMS_OUTPU T.put_line ( | |
790 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
791 | | | TO_CHAR (SQL%ROWCO UNT)); | |
792 | ||
793 | /* - 5/10/ 2017 D.T. JAVA WILL NOW HANDLE THIS OPER ATION **** ********** ********** ********** ********** ********** ********** *** | |
794 | ||
795 | vc_s ql_stateme nt := | |
796 | 'INSERT INTO ppsne pl.epl_ien _generator (CONCEPT, LAST_USED _IEN, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
797 | | | 'VALUES (''EPL_DRU G_DRUG_INT ERACTION'' , 10843, ' 'DEV'', SY SDATE, ''D EV'', SYSD ATE)'; | |
798 | ||
799 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
800 | */ | |
801 | ||
802 | sys. DBMS_OUTPU T.put_line ( | |
803 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
804 | | | TO_CHAR (SQL%ROWCO UNT)); | |
805 | ||
806 | COMM IT; | |
807 | ||
808 | vc_s ql_stateme nt := | |
809 | 'INSERT INTO ppsne pl.epl_vis ta_status (VISTA_STA TUS_ID, ST ATUS_NAME, STATUS_DE SC, CREATE D_BY, CREA TED_DTM, L AST_MODIFI ED_BY, LAS T_MODIFIED _DTM) ' | |
810 | | | 'VALUES (1, ''STAR TED'', ''T he process has star. ..'', ''sy stem'', CU RRENT_TIME STAMP, ''s ystem'', C URRENT_TIM ESTAMP)'; | |
811 | ||
812 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
813 | ||
814 | sys. DBMS_OUTPU T.put_line ( | |
815 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_vista_st atus : ' | |
816 | | | TO_CHAR (SQL%ROWCO UNT)); | |
817 | ||
818 | vc_s ql_stateme nt := | |
819 | 'INSERT INTO ppsne pl.epl_vis ta_status (VISTA_STA TUS_ID, ST ATUS_NAME, STATUS_DE SC, CREATE D_BY, CREA TED_DTM, L AST_MODIFI ED_BY, LAS T_MODIFIED _DTM) ' | |
820 | | | 'VALUES (2, ''COMP LETED'', ' 'The proce ss has com p...'', '' system'', CURRENT_TI MESTAMP, ' 'system'', CURRENT_T IMESTAMP)' ; | |
821 | ||
822 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
823 | ||
824 | sys. DBMS_OUTPU T.put_line ( | |
825 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_vista_st atus : ' | |
826 | | | TO_CHAR (SQL%ROWCO UNT)); | |
827 | ||
828 | vc_s ql_stateme nt := | |
829 | 'INSERT INTO ppsne pl.epl_ddi _severity (EPL_ID , NAME, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
830 | | | 'VALUES (1, ''CRIT ICAL'', '' 360'', TIM ESTAMP ''2 014-05-09 13:02:39'' , ''360'', TIMESTAMP ''2014-05 -09 13:02: 39'')'; | |
831 | ||
832 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
833 | ||
834 | sys. DBMS_OUTPU T.put_line ( | |
835 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ddi_seve rity : ' | |
836 | | | TO_CHAR (SQL%ROWCO UNT)); | |
837 | ||
838 | vc_s ql_stateme nt := | |
839 | 'INSERT INTO ppsne pl.epl_ddi _severity (EPL_ID , NAME, CREA TED_BY, CR EATED_DTM, LAST_MODI FIED_BY, L AST_MODIFI ED_DTM) ' | |
840 | | | 'VALUES (2, ''SIGN IFICANT'', ''360'', TIMESTAMP ''2014-05- 09 13:10:0 0'', ''360 '', TIMEST AMP ''2014 -05-09 13: 10:00'')'; | |
841 | ||
842 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
843 | ||
844 | sys. DBMS_OUTPU T.put_line ( | |
845 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ddi_seve rity : ' | |
846 | | | TO_CHAR (SQL%ROWCO UNT)); | |
847 | ||
848 | COMM IT; | |
849 | ||
850 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: inser t_ppsn2_da ta'); | |
851 | RETU RN (0); | |
852 | EXCEPTI ON | |
853 | WHEN OTHERS | |
854 | THEN | |
855 | v n_code := SQLCODE; | |
856 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
857 | s ys.DBMS_OU TPUT.put_l ine ( | |
858 | 'insert_ ppsn2_data : Error co de ' || vn _code || ' : ' || vc_ errm); | |
859 | s ys.DBMS_OU TPUT.put_l ine ( | |
860 | 'insert_ ppsn2_data : last val ue for vc_ sql_statem ent '); | |
861 | s ys.DBMS_OU TPUT.put_l ine ('inse rt_ppsn2_d ata: ' || vc_sql_sta tement); | |
862 | R ETURN (1); | |
863 | END ins ert_ppsn2_ data; | |
864 | ||
865 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
866 | -- NAME : ndc_proces s_1 | |
867 | -- TYPE : Function | |
868 | -- DESC RIPTSION: Create epl _package_s ize table | |
869 | -- INPU TS: None | |
870 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
871 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
872 | FUNCTIO N ndc_proc ess_1 | |
873 | RETU RN NUMBER | |
874 | IS | |
875 | BEGIN | |
876 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
877 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ndc _process_1 '); | |
878 | ||
879 | -- C reate temp table | |
880 | ||
881 | vc_s ql_stateme nt := | |
882 | ' CREATE TAB LE ppsnepl .temp_epl_ ndcs AS SE LECT * FRO M ppsnepl. epl_ndcs'; | |
883 | ||
884 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
885 | ||
886 | vc_s ql_stateme nt := | |
887 | ' ALTER TABL E ppsnepl. temp_epl_n dcs ADD (C ONSTRAINT temp_epl_n dcs_pk PRI MARY KEY ( epl_id))'; | |
888 | ||
889 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
890 | ||
891 | -- C reate epl_ package_si zes table | |
892 | ||
893 | vc_s ql_stateme nt := | |
894 | ' CREATE TAB LE ppsnepl .epl_packa ge_sizes( | |
895 | epl_id NUMBER (30, 0) N OT NULL EN ABLE | |
896 | ,package_s ize NUMBER (13, 6) N OT NULL EN ABLE | |
897 | ,ndf_packa ge_size_ie n NUMBER (30, 0) | |
898 | ,created_b y VARCHA R2 (50 BYT E) NOT NUL L ENABLE | |
899 | ,created_d tm TIMEST AMP (6) NO T NULL ENA BLE | |
900 | ,last_modi fied_by VARCHA R2 (50 BYT E) | |
901 | ,last_modi fied_dtm TIMEST AMP (6)) | |
902 | TABLESPACE ppsnepl_d ata'; | |
903 | ||
904 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
905 | ||
906 | vc_s ql_stateme nt := | |
907 | ' CREATE UNI QUE INDEX ppsnepl.ep l_package_ sizes_pki ON ppsnepl .epl_packa ge_sizes ( epl_id) TA BLESPACE p psnepl_dat a'; | |
908 | ||
909 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
910 | ||
911 | vc_s ql_stateme nt := | |
912 | ' ALTER TABL E ppsnepl. epl_packag e_sizes AD D (CONSTRA INT epl_pa ckage_size s_pk PRIMA RY KEY (ep l_id) USIN G INDEX pp snepl.epl_ package_si zes_pki EN ABLE VALID ATE)'; | |
913 | ||
914 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
915 | ||
916 | vc_s ql_stateme nt := | |
917 | ' CREATE SEQ UENCE ppsn epl.packag e_size_seq uence MINV ALUE 1 MAX VALUE 9999 9999999999 9999999999 9999 INCRE MENT BY 1 START WITH 108 NOCAC HE NOORDE R NOCYCLE '; | |
918 | ||
919 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
920 | ||
921 | vc_s ql_stateme nt := | |
922 | ' GRANT SELE CT ON ppsn epl.epl_pa ckage_size s TO PPSNE PL_READ_ON LY_ROLE'; | |
923 | ||
924 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
925 | ||
926 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: ndc_p rocess_1') ; | |
927 | RETU RN (0); | |
928 | EXCEPTI ON | |
929 | WHEN OTHERS | |
930 | THEN | |
931 | v n_code := SQLCODE; | |
932 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
933 | s ys.DBMS_OU TPUT.put_l ine ( | |
934 | 'ndc_pro cess_1: Er ror code ' || vn_cod e || ': ' || vc_errm ); | |
935 | s ys.DBMS_OU TPUT.put_l ine ( | |
936 | 'ndc_pro cess_1: la st value f or vc_sql_ statement '); | |
937 | s ys.DBMS_OU TPUT.put_l ine ('ndc_ process_1: ' || vc_s ql_stateme nt); | |
938 | R ETURN (1); | |
939 | END ndc _process_1 ; | |
940 | ||
941 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
942 | -- NAME : ndc_proces s_2 | |
943 | -- TYPE : Function | |
944 | -- DESC RIPTSION: Process nd cs and pac kage size | |
945 | -- INPU TS: None | |
946 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
947 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
948 | FUNCTIO N ndc_proc ess_2 | |
949 | RETU RN NUMBER | |
950 | IS | |
951 | BEGIN | |
952 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
953 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ndc _process_2 '); | |
954 | ||
955 | vc_s ql_stateme nt := | |
956 | ' UPDATE pps nepl.epl_n dcs SET pa ckage_size = NULL WH ERE 1 = 1' ; | |
957 | ||
958 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
959 | ||
960 | vc_s ql_stateme nt := | |
961 | ' ALTER TABL E ppsnepl. epl_ndcs M ODIFY pack age_size N UMBER (30, 0)'; | |
962 | ||
963 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
964 | ||
965 | vc_s ql_stateme nt := | |
966 | ' ALTER TABL E ppsnepl. epl_ndcs R ENAME COLU MN package _size TO p ackage_siz e_id_fk'; | |
967 | ||
968 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
969 | ||
970 | vc_s ql_stateme nt := | |
971 | ' UPDATE pps nepl.epl_n dcs o | |
972 | SET o.package_ size_id_fk = | |
973 | (SELECT MI N (epl_pac kage_sizes .epl_id) | |
974 | FROM pp snepl.epl_ package_si zes, ppsne pl.temp_ep l_ndcs | |
975 | WHERE epl_pack age_sizes. package_si ze = | |
976 | temp_ epl_ndcs.p ackage_siz e | |
977 | AN D temp_epl _ndcs.epl_ id = o.epl _id) | |
978 | WHER E o.epl_id IN (SELEC T epl_id F ROM ppsnep l.temp_epl _ndcs)'; | |
979 | ||
980 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
981 | ||
982 | sys. DBMS_OUTPU T.put_line ( | |
983 | 'INFORMA TION - N umber of r ow(s) upda ted in pps nepl.epl_n dcs : ' | |
984 | | | TO_CHAR (SQL%ROWCO UNT)); | |
985 | ||
986 | vc_s ql_stateme nt := | |
987 | ' CREATE IND EX ppsnepl .epl_ndc_p ckg_size_f k_idx ON p psnepl.epl _ndcs (pac kage_size_ id_fk)'; | |
988 | ||
989 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
990 | ||
991 | vc_s ql_stateme nt := | |
992 | ' ALTER TABL E ppsnepl. epl_ndcs A DD FOREIGN KEY(packa ge_size_id _fk) REFER ENCES ppsn epl.epl_pa ckage_size s (epl_id) '; | |
993 | ||
994 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
995 | ||
996 | vc_s ql_stateme nt := 'DRO P SEQUENCE ppsnepl.p ackage_siz e_sequence '; | |
997 | ||
998 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
999 | ||
1000 | COMM IT; | |
1001 | ||
1002 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: ndc_p rocess_2') ; | |
1003 | RETU RN (0); | |
1004 | EXCEPTI ON | |
1005 | WHEN OTHERS | |
1006 | THEN | |
1007 | v n_code := SQLCODE; | |
1008 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1009 | s ys.DBMS_OU TPUT.put_l ine ( | |
1010 | 'ndc_pro cess_2: Er ror code ' || vn_cod e || ': ' || vc_errm ); | |
1011 | s ys.DBMS_OU TPUT.put_l ine ( | |
1012 | 'ndc_pro cess_2: la st value f or vc_sql_ statement '); | |
1013 | s ys.DBMS_OU TPUT.put_l ine ('ndc_ process_3: ' || vc_s ql_stateme nt); | |
1014 | R ETURN (1); | |
1015 | END ndc _process_2 ; | |
1016 | ||
1017 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1018 | -- NAME : insert_epl _national_ settings | |
1019 | -- TYPE : Function | |
1020 | -- DESC RIPTSION: Insert dat a into epl _national_ settings t able | |
1021 | -- INPU TS: None | |
1022 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1023 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1024 | ||
1025 | FUNCTIO N insert_e pl_nationa l_settings | |
1026 | RETU RN NUMBER | |
1027 | IS | |
1028 | BEGIN | |
1029 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1030 | sys. DBMS_OUTPU T.put_line ( | |
1031 | ' INFORMATIO N - STAR T: insert _epl_natio nal_settin gs'); | |
1032 | ||
1033 | EXEC UTE IMMEDI ATE | |
1034 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,CR EATED_BY,C REATED_DTM ,LAST_MODI FIED_BY,LA ST_MODIFIE D_DTM) Val ues (37, ' 'NDF_UPDAT E_RUN_STAT E'', ''COM PLETED'', ''DEVELOPE R'', SYSDA TE, ''NdfU pdateProce ss'', SYSD ATE)'; | |
1035 | ||
1036 | EXEC UTE IMMEDI ATE | |
1037 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,DATE_ VALUE,CREA TED_BY,CRE ATED_DTM,L AST_MODIFI ED_BY,LAST _MODIFIED_ DTM) Value s (38, ' 'NDF_UPDAT E_LAST_RUN '', NULL, ''DEVELOPE R'', TO_TI MESTAMP('' 1/31/2014 12:00:00.0 00000 AM'' ,''fmMMfm/ fmDDfm/YYY Y fmHH12fm :MI:SS.FF AM''),''Nd fUpdatePro cess'', SY SDATE)'; | |
1038 | ||
1039 | EXEC UTE IMMEDI ATE | |
1040 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (39 ,''NDF_OUT GOING_FILE _TEXT'','' unmatched from the N ational Dr ug File (N DF). The VA Product |name and CMOP ID c orrespondi ng to the unmatched local drug file |nam e are list ed on the indented l ine beneat h each ent ry. An |I nactivatio n Date may be listed for entri es when th is reason |applies. Until you rematch t hese entri es to NDF, they will not |tran smit to CM OP and dru g-drug int eraction c hecks will not check |for thes e products . It is c ritical th at you rem atch these |products immediate ly. You m ay also ne ed to matc h a new |o rderable i tem. Any possible d osages and local pos sible |dos ages for t hese unmat ched produ cts have b een delete d. |Theref ore, the d osages for each unma tched prod uct should |be revie wed after the rematc h or recre ated if th e product |can not b e rematche d to a VA Product th rough the NDF |match ing proces s.'',null, null,null, null,''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''),'' DEVELOPER' ',to_times tamp(''21- MAY-14 12. 00.00.0000 00000 AM'' ,''DD-MON- RR HH.MI.S S.FF AM'') )'; | |
1041 | ||
1042 | EXEC UTE IMMEDI ATE | |
1043 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (40 ,''NDF_OUT PUT_FILE_D IRECTORY'' ,''./tmp/' ',null,nul l,null,nul l,''DEVELO PER'',to_t imestamp(' '21-MAY-14 12.00.00. 000000000 AM'',''DD- MON-RR HH. MI.SS.FF A M''),''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''))'; | |
1044 | ||
1045 | EXEC UTE IMMEDI ATE | |
1046 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (41 ,''NDF_OUT PUT_TST_PR OTOCOL'',' 'FTP'',nul l,null,nul l,null,''D EVELOPER'' ,to_timest amp(''21-M AY-14 12.0 0.00.00000 0000 AM'', ''DD-MON-R R HH.MI.SS .FF AM''), ''DEVELOPE R'',to_tim estamp(''2 1-MAY-14 1 2.00.00.00 0000000 AM '',''DD-MO N-RR HH.MI .SS.FF AM' '))'; | |
1047 | ||
1048 | EXEC UTE IMMEDI ATE | |
1049 | 'Insert in to ppsnepl .epl_natio nal_settin gs (ID,KEY _NAME,STRI NG_VALUE,I NTEGER_VAL UE,DECIMAL _VALUE,BOO LEAN_VALUE ,DATE_VALU E,CREATED_ BY,CREATED _DTM,LAST_ MODIFIED_B Y,LAST_MOD IFIED_DTM) values (4 2,''NDF_OU TPUT_TST_H OSTNAME'', '' IP '',null,nu ll,null,nu ll,''DEVEL OPER'',to_ timestamp( ''21-MAY-1 4 12.00.00 .000000000 AM'',''DD -MON-RR HH .MI.SS.FF AM''),''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''))' ; | |
1050 | ||
1051 | EXEC UTE IMMEDI ATE | |
1052 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (43 ,''NDF_OUT PUT_TST_PO RT'',''21' ',null,nul l,null,nul l,''DEVELO PER'',to_t imestamp(' '21-MAY-14 12.00.00. 000000000 AM'',''DD- MON-RR HH. MI.SS.FF A M''),''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''))'; | |
1053 | ||
1054 | EXEC UTE IMMEDI ATE | |
1055 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (44 ,''NDF_OUT PUT_TST_US ERNAME'',' 'USRVISTA' ',null,nul l,null,nul l,''DEVELO PER'',to_t imestamp(' '21-MAY-14 12.00.00. 000000000 AM'',''DD- MON-RR HH. MI.SS.FF A M''),''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''))'; | |
1056 | ||
1057 | EXEC UTE IMMEDI ATE | |
1058 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (45 ,''NDF_OUT PUT_TST_PA SSWORD'',' ' '',null, null,null, null,''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''),'' DEVELOPER' ',to_times tamp(''21- MAY-14 12. 00.00.0000 00000 AM'' ,''DD-MON- RR HH.MI.S S.FF AM'') )'; | |
1059 | ||
1060 | EXEC UTE IMMEDI ATE | |
1061 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (46 ,''NDF_OUT PUT_TST_DI RECTORY'', ''$1$DGA55 :[PMIS.MAR K]'',null, null,null, null,''DEV ELOPER'',t o_timestam p(''21-MAY -14 12.00. 00.0000000 00 AM'','' DD-MON-RR HH.MI.SS.F F AM''),'' DEVELOPER' ',to_times tamp(''21- MAY-14 12. 00.00.0000 00000 AM'' ,''DD-MON- RR HH.MI.S S.FF AM'') )'; | |
1062 | ||
1063 | EXEC UTE IMMEDI ATE | |
1064 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (47 ,''NDF_OUT PUT_PROD_P ROTOCOL'', ''FTP'',nu ll,null,nu ll,null,'' DEVELOPER' ',to_times tamp(''21- MAY-14 12. 00.00.0000 00000 AM'' ,''DD-MON- RR HH.MI.S S.FF AM'') ,''DEVELOP ER'',to_ti mestamp('' 21-MAY-14 12.00.00.0 00000000 A M'',''DD-M ON-RR HH.M I.SS.FF AM ''))'; | |
1065 | ||
1066 | EXEC UTE IMMEDI ATE | |
1067 | 'Insert in to ppsnepl .epl_natio nal_settin gs (ID,KEY _NAME,STRI NG_VALUE,I NTEGER_VAL UE,DECIMAL _VALUE,BOO LEAN_VALUE ,DATE_VALU E,CREATED_ BY,CREATED _DTM,LAST_ MODIFIED_B Y,LAST_MOD IFIED_DTM) values (4 8,''NDF_OU TPUT_PROD_ HOSTNAME'' ,'' IP '',null,nu ll,null,nu ll,''DEVEL OPER'',to_ timestamp( ''21-MAY-1 4 12.00.00 .000000000 AM'',''DD -MON-RR HH .MI.SS.FF AM''),''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''))' ; | |
1068 | ||
1069 | EXEC UTE IMMEDI ATE | |
1070 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (49 ,''NDF_OUT PUT_PROD_P ORT'',''21 '',null,nu ll,null,nu ll,''DEVEL OPER'',to_ timestamp( ''21-MAY-1 4 12.00.00 .000000000 AM'',''DD -MON-RR HH .MI.SS.FF AM''),''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''))' ; | |
1071 | ||
1072 | EXEC UTE IMMEDI ATE | |
1073 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (50 ,''NDF_OUT PUT_PROD_U SERNAME'', ''USRVISTA '',null,nu ll,null,nu ll,''DEVEL OPER'',to_ timestamp( ''21-MAY-1 4 12.00.00 .000000000 AM'',''DD -MON-RR HH .MI.SS.FF AM''),''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''))' ; | |
1074 | ||
1075 | EXEC UTE IMMEDI ATE | |
1076 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (51 ,''NDF_OUT PUT_PROD_P ASSWORD'', '' '',null ,null,null ,null,''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''),' 'DEVELOPER '',to_time stamp(''21 -MAY-14 12 .00.00.000 000000 AM' ',''DD-MON -RR HH.MI. SS.FF AM'' ))'; | |
1077 | ||
1078 | EXEC UTE IMMEDI ATE | |
1079 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (52 ,''NDF_OUT PUT_PROD_D IRECTORY'' ,''$1$DGA5 5:[PMIS.MA RK]'',null ,null,null ,null,''DE VELOPER'', to_timesta mp(''21-MA Y-14 12.00 .00.000000 000 AM'',' 'DD-MON-RR HH.MI.SS. FF AM''),' 'DEVELOPER '',to_time stamp(''21 -MAY-14 12 .00.00.000 000000 AM' ',''DD-MON -RR HH.MI. SS.FF AM'' ))'; | |
1080 | ||
1081 | EXEC UTE IMMEDI ATE | |
1082 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (54 ,''MESSAGE _STRENGTH_ TEXT'','' The follow ing Streng th(s) have been edit ed or adde d. Any pro duct match ed to| the se product s will be unmatched. If site w ants to co ntinue to use the| p roduct the site must rematch t o local fi le #50 ent ries to th e listed V A| product s.'',null, null,null, null,''DEV ELOPER'',s ysdate,''D EVELOPER'' ,sysdate)' ; | |
1083 | ||
1084 | EXEC UTE IMMEDI ATE | |
1085 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (55 ,''MESSAGE _OVERRIDED OSECHECK_T EXT'','' T he overrid e dose for m checks f ield has c hanged for the follo wing produ cts.|'',nu ll,null,nu ll,null,'' DEVELOPER' ',sysdate, ''DEVELOPE R'',sysdat e)'; | |
1086 | ||
1087 | EXEC UTE IMMEDI ATE | |
1088 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (56 ,''MESSAGE _NATIONALF ORMULARY_T EXT'','' T he Nationa l Formular y Indicato r has chan ged for th e followin g| VA Prod ucts. The National Formulary Indicator will autom atically| be changed in your l ocal DRUG file (#50) . Please review the | VISN and Local For mulary des ignations of these p roducts an d| make ap propriate changes.'' ,null,null ,null,null ,''DEVELOP ER'',sysda te,''DEVEL OPER'',sys date)'; | |
1089 | ||
1090 | EXEC UTE IMMEDI ATE | |
1091 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (57 ,''MESSAGE _INACTIVAT EDCMOP_TEX T'','' The following active VA Products are no lon ger marked for CMOP. | All loca l drug fil e entries matched to these VA Products w ill| be UN MARKED for CMOP. In order to have these entries d ispensed| by CMOP an y local DR UG file (# 50) entrie s matched to these| products m ust be re- matched to another V A product that is ac tively| ma rked for C MOP dispen sing.'',nu ll,null,nu ll,null,'' DEVELOPER' ',sysdate, ''DEVELOPE R'',sysdat e)'; | |
1092 | ||
1093 | EXEC UTE IMMEDI ATE | |
1094 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (58 ,''MESSAGE _ADDEDPROD UCT_TEXT'' ,'' The fo llowing VA Products have been added to t he Nationa l| Drug Fi le. You m ay wish to review, t hen match or unmatch | local dr ug file en tries base d on this updated in formation. '',null,nu ll,null,nu ll,''DEVEL OPER'',sys date,''DEV ELOPER'',s ysdate)'; | |
1095 | ||
1096 | EXEC UTE IMMEDI ATE | |
1097 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (59 ,''MESSAGE _SCHEDULEC HANGE_TEXT '','' The CS Federal Schedule for the fo llowing VA Products has| been added or c hanged. Y ou may wis h to revie w the DRUG | file (#5 0) and mak e appropri ate change s to the D EA, SPECIA L| HDLG fi eld (#3).' ',null,nul l,null,nul l,''DEVELO PER'',sysd ate,''DEVE LOPER'',sy sdate)'; | |
1098 | ||
1099 | EXEC UTE IMMEDI ATE | |
1100 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (60 ,''MESSAGE _POSSIBLED OSAGE_TEXT '','' The Auto-Creat e Possible Dosages s ettings ha ve been ed ited for t he| follow ing VA Pro ducts. Ple ase review your loca l dosages for produc ts| matche d to these entries. Edits to y our site'' ''s possib le dosages or| local possible dosages ma y be neede d.'',null, null,null, null,''DEV ELOPER'',s ysdate,''D EVELOPER'' ,sysdate)' ; | |
1101 | ||
1102 | EXEC UTE IMMEDI ATE | |
1103 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (61 ,''MESSAGE _PRINTNAME CHANGE_TEX T'','' The following VA Print Name(s) ha ve been ed ited or ad ded. Any p roduct mat ched| to t hese produ cts will b e unmatche d. If site wants to continue t o use the| the produ ct the sit e must rem atch to lo cal file # 50 entries to the li sted VA| p roduct the site must rematch t o local fi le #50 ent ries to th e listed V A| product .'',null,n ull,null,n ull,''DEVE LOPER'',sy sdate,''DE VELOPER'', sysdate)'; | |
1104 | ||
1105 | EXEC UTE IMMEDI ATE | |
1106 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (62 ,''MESSAGE _DRUGUNITC HANGE_TEXT '','' The following Unit(s) ha ve been ed ited or ad ded. Any p roduct mat ched to th ese| produ cts will b e unmatche d. If site wants to continue t o use the product th e| site mu st rematch to local file #50 e ntries to the listed VA Produc t.'',null, null,null, null,''DEV ELOPER'',s ysdate,''D EVELOPER'' ,sysdate)' ; | |
1107 | ||
1108 | EXEC UTE IMMEDI ATE | |
1109 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (63 ,''MESSAGE _DISPENSEU NITCHANGE_ TEXT'','' The follow ing VA Dis pense Unit s(s) have been edite d or added . Any prod uct| match ed to thes e products will be u nmatched. If site wa nts to con tinue to u se| the pr oduct the site must rematch to local fil e #50 entr ies to the listed VA | product. '',null,nu ll,null,nu ll,''DEVEL OPER'',sys date,''DEV ELOPER'',s ysdate)'; | |
1110 | ||
1111 | EXEC UTE IMMEDI ATE | |
1112 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (64 ,''MESSAGE _VACLASSCH ANGE_TEXT' ','' The f ollowing V A Drug Cla ss(es) hav e been edi ted or add ed. The VA Class| fo r this pro duct will be automat ically upd ated in fi le #50.'', null,null, null,null, ''DEVELOPE R'',sysdat e,''DEVELO PER'',sysd ate)'; | |
1113 | ||
1114 | EXEC UTE IMMEDI ATE | |
1115 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (65 ,''MESSAGE _CMOPCHANG E_TEXT'',' ' The foll owing VA P roduct Ide ntifier(s) have been edited or added. An y product| matched t o these pr oducts wil l be unmat ched. If s ite wants to continu e to use| the produc t the site must rema tch to loc al file #5 0 entries to the lis ted VA| pr oduct.'',n ull,null,n ull,null,' 'DEVELOPER '',sysdate ,''DEVELOP ER'',sysda te)'; | |
1116 | ||
1117 | EXEC UTE IMMEDI ATE | |
1118 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (66 ,''MESSAGE _FUTUREINA CTIVE_TEXT '','' The following products w ill be ina ctivated o n the date listed. N o alternat ive| produ cts have b een found. '',null,nu ll,null,nu ll,''DEVEL OPER'',sys date,''DEV ELOPER'',s ysdate)'; | |
1119 | ||
1120 | EXEC UTE IMMEDI ATE | |
1121 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (67 ,''MESSAGE _SCHEDULEC HANGEALL_T EXT'','' T he followi ng CS Fede ral Schedu le(s) have been edit ed or adde d. Please review| th e local DE A special handling f ield and m ake edits are requir ed.'',null ,null,null ,null,''DE VELOPER'', sysdate,'' DEVELOPER' ',sysdate) '; | |
1122 | ||
1123 | EXEC UTE IMMEDI ATE | |
1124 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (68 ,''MESSAGE _GENERICNA MECHANGE_T EXT'','' T he followi ng VA Gene ric Name(s ) have bee n edited o r added. A ny product | matched to these p roducts wi ll be unma tched. If site wants to contin ue to use| the produ ct the sit e must rem atch to lo cal file # 50 entries to the li sted VA| p roduct.'', null,null, null,null, ''DEVELOPE R'',sysdat e,''DEVELO PER'',sysd ate)'; | |
1125 | ||
1126 | EXEC UTE IMMEDI ATE | |
1127 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (69 ,''MESSAGE _DOSAGEFOR MCHANGE_TE XT'','' Th e followin g VA Dosag e Form(s) have been edited. An y product matched to | these pr oducts wil l be unmat ched. If s ite wants to continu e to use t he| produc t the site must rema tch to loc al file #5 0 entries to the lis ted VA| pr oduct.'',n ull,null,n ull,null,' 'DEVELOPER '',sysdate ,''DEVELOP ER'',sysda te)'; | |
1128 | ||
1129 | EXEC UTE IMMEDI ATE | |
1130 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (70 ,''MESSAGE _INACTIVAT EDPRODUCT_ TEXT'','' The follow ing produc t(s) have been inact ivated. No alternati ve product s have| be en found.' ',null,nul l,null,nul l,''DEVELO PER'',sysd ate,''DEVE LOPER'',sy sdate)'; | |
1131 | ||
1132 | EXEC UTE IMMEDI ATE | |
1133 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (71 ,''MESSAGE _REACTIVAT EDPRODUCT_ TEXT'','' The follow ing VA Pro duct(s) ha ve been re activated. |'',null,n ull,null,n ull,''DEVE LOPER'',sy sdate,''DE VELOPER'', sysdate)'; | |
1134 | ||
1135 | EXEC UTE IMMEDI ATE | |
1136 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (72 ,''MESSAGE 2_MEDGUIDE _TEXT'','' The FDA M ed Guide f or the fol lowing pro ducts has been chang ed|'',null ,null,null ,null,''DE VELOPER'', sysdate,'' DEVELOPER' ',sysdate) '; | |
1137 | ||
1138 | EXEC UTE IMMEDI ATE | |
1139 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (73 ,''MESSAGE 2_EXCLUEDE DDDI_TEXT' ','' The f ollowing p roducts ha ve been fl agged for exclusion from drug- drug| inte raction ch ecks.'',nu ll,null,nu ll,null,'' DEVELOPER' ',sysdate, ''DEVELOPE R'',sysdat e)'; | |
1140 | ||
1141 | EXEC UTE IMMEDI ATE | |
1142 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (74 ,''MESSAGE 2_PREVEXCL UEDEDDDI_T EXT'','' T he followi ng product s, previou sly flagge d for excl usion from drug-drug | interact ion checks , have bee n changed to be incl uded| in d rug-drug i nteraction checks wh ere approp riate.'',n ull,null,n ull,null,' 'DEVELOPER '',sysdate ,''DEVELOP ER'',sysda te)'; | |
1143 | ||
1144 | EXEC UTE IMMEDI ATE | |
1145 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (75 ,''MESSAGE 2_DDI_TEXT '','' The following interactio ns in Nati onal Drug File (NDF) have been added,| e dited or i nactivated . These c hanges are the resul t of revie w and| rec ommendatio ns from th e NDF supp ort group. '',null,nu ll,null,nu ll,''DEVEL OPER'',sys date,''DEV ELOPER'',s ysdate)'; | |
1146 | ||
1147 | EXEC UTE IMMEDI ATE | |
1148 | ' INSERT INT O ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (76 ,''NATIONA L_SITE_NUM BER'',NULL ,442,NULL, NULL,NULL, ''DEVELOPE R'',SYSDAT E,''DEVELO PER'',SYSD ATE)'; | |
1149 | ||
1150 | EXEC UTE IMMEDI ATE | |
1151 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (77 ,''MESSAGE _INACTIVAT EDWSUGG_TE XT'',''The following product(s ) have bee n inactiva ted. Any f ile #50 en tries matc hed|to the se product s will be unmatched. To contin ue to use the produc t, the sit e|must rem atch the l ocal file #50 entrie s to the l isted VA P roduct.'', null,null, null,null, ''DEVELOPE R'',sysdat e,''DEVELO PER'',sysd ate)'; | |
1152 | ||
1153 | EXEC UTE IMMEDI ATE | |
1154 | ' Insert int o ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (78 ,''MESSAGE _PROPOSEDI NACTIVATED WSUGG_TEXT '',''The f ollowing p roduct(s) WILL be in activated in on the date liste d. Any fil e|#50 entr ies matche d to these products will be un matched on ce the pro duct is|in activated. If a site wants to continue t o use the product, t he site mu st|rematch the local file #50 entries to the liste d VA Produ ct.'',null ,null,null ,null,''DE VELOPER'', sysdate,'' DEVELOPER' ',sysdate) '; | |
1155 | ||
1156 | EXEC UTE IMMEDI ATE | |
1157 | ' INSERT INT O ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) VALUES (79 , ''REPORT _DATA_UPDA TE_START'' , null, nu ll, null, null, null , ''DEVELO PER'', sys date, ''DE VELOPER'', sysdate)' ; | |
1158 | ||
1159 | EXEC UTE IMMEDI ATE | |
1160 | ' INSERT INT O ppsnepl. epl_nation al_setting s (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) VALUES (80 , ''REPORT _DATA_UPDA TE_COMPLET E'', null, null, nul l, null, n ull, ''DEV ELOPER'', sysdate, ' 'DEVELOPER '', sysdat e)'; | |
1161 | ||
1162 | EXEC UTE IMMEDI ATE | |
1163 | ' Insert int o ppsnepl. EPL_NATION AL_SETTING S (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (81 ,''MESSAGE _INACTIVAT EDREMATCHE D_TEXT'',' 'The follo wing produ ct(s) have been inac tivated. A ny file|#5 0 entries matched to these pro ducts will be unmatc hed and re matched to the|local file #50 entries li sted.'',nu ll,null,nu ll,null,'' DEVELOPER' ',sysdate, ''DEVELOPE R'',sysdat e)'; | |
1164 | ||
1165 | EXEC UTE IMMEDI ATE | |
1166 | ' Insert int o ppsnepl. EPL_NATION AL_SETTING S (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (82 ,''MESSAGE _OTHERREMA TCHED_TEXT '',''The f ollowing p roduct(s) have been unmatched. Any file| #50 entrie s matched to these p roducts wi ll be unma tched and rematched to the|loc al file #5 0 entries listed.'', null,null, null,null, ''DEVELOPE R'',sysdat e,''DEVELO PER'',sysd ate)'; | |
1167 | ||
1168 | EXEC UTE IMMEDI ATE | |
1169 | ' Insert int o ppsnepl. EPL_NATION AL_SETTING S (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (83 ,''MESSAGE _OTHERREMA TCHSUGG_TE XT'',''The following product(s ) have bee n unmatche d Any file |#50 entri es matched to these products w ill be unm atched. To continue to use|the product, the site m ust rematc h the loca l file #50 entries t o the list ed VA Prod uct.'',nul l,null,nul l,null,''D EVELOPER'' ,sysdate,' 'DEVELOPER '',sysdate )'; | |
1170 | ||
1171 | EXEC UTE IMMEDI ATE | |
1172 | ' Insert int o ppsnepl. EPL_NATION AL_SETTING S (ID,KEY_ NAME,STRIN G_VALUE,IN TEGER_VALU E,DECIMAL_ VALUE,BOOL EAN_VALUE, DATE_VALUE ,CREATED_B Y,CREATED_ DTM,LAST_M ODIFIED_BY ,LAST_MODI FIED_DTM) values (84 ,''MESSAGE _OTHERNORE MATCH_TEXT '',''The f ollowing p roduct(s) have been unmatched. No altern ative prod ucts|have been found .'',null,n ull,null,n ull,''DEVE LOPER'',sy sdate,''DE VELOPER'', sysdate)'; | |
1173 | ||
1174 | COMM IT; | |
1175 | ||
1176 | EXEC UTE IMMEDI ATE 'SELEC T COUNT (* ) FROM pps nepl.epl_n ational_se ttings' | |
1177 | I NTO vn_row _count; | |
1178 | ||
1179 | sys. DBMS_OUTPU T.put_line ( | |
1180 | 'INFORMA TION - N umber of r ow(s) in p psnepl.epl _national_ settings : ' | |
1181 | | | TO_CHAR (vn_row_co unt)); | |
1182 | ||
1183 | sys. DBMS_OUTPU T.put_line ( | |
1184 | ' INFORMATIO N - END: insert_e pl_nationa l_settings '); | |
1185 | ||
1186 | IF v n_row_coun t = 0 | |
1187 | THEN | |
1188 | R ETURN (1); | |
1189 | END IF; | |
1190 | ||
1191 | RETU RN (0); | |
1192 | EXCEPTI ON | |
1193 | WHEN OTHERS | |
1194 | THEN | |
1195 | v n_code := SQLCODE; | |
1196 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1197 | s ys.DBMS_OU TPUT.put_l ine ( | |
1198 | 'inse rt_epl_nat ional_sett ings: Erro r code ' | |
1199 | || vn_co de | |
1200 | || ': ' | |
1201 | || vc_er rm); | |
1202 | R ETURN (1); | |
1203 | END ins ert_epl_na tional_set tings; | |
1204 | ||
1205 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1206 | -- NAME : update_nat ional_sett ing | |
1207 | -- TYPE : Function | |
1208 | -- DESC RIPTSION: Update the epl_natio nal_settin gs table | |
1209 | -- INPU TS: None | |
1210 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1211 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1212 | FUNCTIO N update_n ational_se tting | |
1213 | RETU RN NUMBER | |
1214 | IS | |
1215 | BEGIN | |
1216 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1217 | sys. DBMS_OUTPU T.put_line ( | |
1218 | ' INFORMATIO N - STAR T: update _national_ setting'); | |
1219 | ||
1220 | EXEC UTE IMMEDI ATE | |
1221 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following Strength( s) have be en edited or added. Any produc t matched to|these p roducts wi ll be unma tched. If site wants to contin ue to use the|produc t the site must rema tch to loc al file #5 0 entries to the lis ted VA|pro ducts.'' w here id =5 3'; | |
1222 | ||
1223 | EXEC UTE IMMEDI ATE | |
1224 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The override dose form checks fie ld has cha nged for t he followi ng product s.|'' wher e id =54'; | |
1225 | ||
1226 | EXEC UTE IMMEDI ATE | |
1227 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The National Formulary Indicator has change d for the following| VA Product s. The Nat ional Form ulary Indi cator will automatic ally|be ch anged in y our local DRUG file (#50). Ple ase review the|VISN and Local Formulary designatio ns of thes e products and|make appropriat e changes. '' where i d =55'; | |
1228 | ||
1229 | EXEC UTE IMMEDI ATE | |
1230 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following active VA Products are no lon ger marked for CMOP. |All local drug file entries m atched to these VA P roducts wi ll|be UNMA RKED for C MOP. In or der to hav e these en tries disp ensed|by C MOP any lo cal DRUG f ile (#50) entries ma tched to t hese|produ cts must b e re-match ed to anot her VA pro duct that is activel y|marked f or CMOP di spensing.' ' where id =56'; | |
1231 | ||
1232 | EXEC UTE IMMEDI ATE | |
1233 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Produc ts have be en added t o the Nati onal|Drug File.You m ay wish to review, t hen match or unmatch |local dru g file ent ries based on this u pdated inf ormation.' ' where id =57'; | |
1234 | ||
1235 | EXEC UTE IMMEDI ATE | |
1236 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The CS Federa l Schedule for the f ollowing V A Products has|been added or c hanged.You may wish to review the DRUG|f ile (#50) and make a ppropriate changes t o the DEA, SPECIAL|H DLG field (#3).'' wh ere id =58 '; | |
1237 | ||
1238 | EXEC UTE IMMEDI ATE | |
1239 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The Auto-Crea te Possibl e Dosages settings h ave been e dited for the|follow ing VA Pro ducts. Ple ase review your loca l dosages for produc ts|matched to these entries. E dits to yo ur site''' 's possibl e dosages or|local p ossible do sages may be needed. '' where i d =59'; | |
1240 | ||
1241 | EXEC UTE IMMEDI ATE | |
1242 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Print Name(s) ha ve been ed ited or ad ded. Any p roduct mat ched|to th ese produc ts will be unmatched . If site wants to c ontinue to use the|t he product the site must remat ch to loca l file #50 entries t o the list ed VA|prod uct the si te must re match to l ocal file #50 entrie s to the l isted VA|p roduct.'' where id = 60'; | |
1243 | ||
1244 | EXEC UTE IMMEDI ATE | |
1245 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following Unit(s) h ave been e dited or a dded. Any product ma tched to t hese|produ cts will b e unmatche d. If site wants to continue t o use the product th e|site mus t rematch to local f ile #50 en tries to t he listed VA Product .'' where id =61'; | |
1246 | ||
1247 | EXEC UTE IMMEDI ATE | |
1248 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Dispen se Units(s ) have bee n edited o r added. A ny product |matched t o these pr oducts wil l be unmat ched. If s ite wants to continu e to use|t he product the site must remat ch to loca l file #50 entries t o the list ed VA|prod uct.'' whe re id =62' ; | |
1249 | ||
1250 | EXEC UTE IMMEDI ATE | |
1251 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Drug C lass(es) h ave been e dited or a dded. The VA Class|f or this pr oduct will be automa tically up dated in f ile #50.'' where id =63'; | |
1252 | ||
1253 | EXEC UTE IMMEDI ATE | |
1254 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Produc t Identifi er(s) have been edit ed or adde d. Any pro duct|match ed to thes e products will be u nmatched. If site wa nts to con tinue to u se|the pro duct the s ite must r ematch to local file #50 entri es to the listed VA| product.'' where id =64'; | |
1255 | ||
1256 | EXEC UTE IMMEDI ATE | |
1257 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following products will be in activated on the dat e listed. No alterna tive|produ cts have b een found. '' where i d =65'; | |
1258 | ||
1259 | EXEC UTE IMMEDI ATE | |
1260 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following CS Federa l Schedule (s) have b een edited or added. Please re view|the l ocal DEA s pecial han dling fiel d and make edits are required. '' where i d =66'; | |
1261 | ||
1262 | EXEC UTE IMMEDI ATE | |
1263 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Generi c Name(s) have been edited or added. Any product|m atched to these prod ucts will be unmatch ed. If sit e wants to continue to use|the product t he site mu st rematch to local file #50 e ntries to the listed VA|produc t.'' where id =67'; | |
1264 | ||
1265 | EXEC UTE IMMEDI ATE | |
1266 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Dosage Form(s) h ave been e dited. Any product m atched to| these prod ucts will be unmatch ed. If sit e wants to continue to use the |product t he site mu st rematch to local file #50 e ntries to the listed VA|produc t.'' where id =68'; | |
1267 | ||
1268 | EXEC UTE IMMEDI ATE | |
1269 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following product(s ) have bee n inactiva ted. No al ternative products h ave|been f ound.'' wh ere id =69 '; | |
1270 | ||
1271 | EXEC UTE IMMEDI ATE | |
1272 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following VA Produc t(s) have been react ivated.|'' where id =70'; | |
1273 | ||
1274 | EXEC UTE IMMEDI ATE | |
1275 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The FDA Med G uide for t he followi ng product s has been changed|' ' where id =71'; | |
1276 | ||
1277 | EXEC UTE IMMEDI ATE | |
1278 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following products have been flagged fo r exclusio n from dru g-drug|int eraction c hecks.'' w here id =7 2'; | |
1279 | ||
1280 | EXEC UTE IMMEDI ATE | |
1281 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following products, previousl y flagged for exclus ion from d rug-drug|i nteraction checks, h ave been c hanged to be include d|in drug- drug inter action che cks where appropriat e.'' where id =73'; | |
1282 | ||
1283 | EXEC UTE IMMEDI ATE | |
1284 | ' UPDATE pps nepl.epl_n ational_se ttings set STRING_VA LUE =''The following interacti ons in Nat ional Drug File (NDF ) have bee n added,|e dited or i nactivated . These ch anges are the result of review and|recom mendations from the NDF suppor t group.'' where id =74'; | |
1285 | ||
1286 | EXEC UTE IMMEDI ATE | |
1287 | ' UPDATE pps nepl.epl_n ational_se ttings set string_va lue = ''Th e followin g VA Print Name(s) h ave been e dited or a dded. Any product ma tched|to t hese produ cts will b e unmatche d. If site wants to continue t o use the| product th e site mus t rematch to local f ile #50 en tries to t he listed VA product .'' where id = 60'; | |
1288 | ||
1289 | EXEC UTE IMMEDI ATE | |
1290 | ' UPDATE pps nepl.epl_n ational_se ttings set string_va lue = ''Th e followin g product( s) have be en edited Any file|# 50 entries matched t o these pr oducts wil l be unmat ched. To c ontinue to use|the p roduct, th e site mus t rematch the local file #50 e ntries to the listed VA Produc t.'' where id = 82'; | |
1291 | ||
1292 | COMM IT; | |
1293 | ||
1294 | sys. DBMS_OUTPU T.put_line ( | |
1295 | ' INFORMATIO N - END: update_n ational_se tting'); | |
1296 | ||
1297 | IF v n_row_coun t = 0 | |
1298 | THEN | |
1299 | R ETURN (1); | |
1300 | END IF; | |
1301 | ||
1302 | RETU RN (0); | |
1303 | EXCEPTI ON | |
1304 | WHEN OTHERS | |
1305 | THEN | |
1306 | v n_code := SQLCODE; | |
1307 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1308 | s ys.DBMS_OU TPUT.put_l ine ( | |
1309 | 'upda te_nationa l_setting: Error cod e ' | |
1310 | || vn_co de | |
1311 | || ': ' | |
1312 | || vc_er rm); | |
1313 | R ETURN (1); | |
1314 | END upd ate_nation al_setting ; | |
1315 | ||
1316 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1317 | -- NAME : turn_vista _messaging _off | |
1318 | -- TYPE : Function | |
1319 | -- DESC RIPTSION: Update epl _national_ setting ta ble to tur n vist_mes saging_off | |
1320 | -- INPU TS: None | |
1321 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1322 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1323 | FUNCTIO N turn_vis ta_messagi ng_off | |
1324 | RETU RN NUMBER | |
1325 | IS | |
1326 | BEGIN | |
1327 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1328 | sys. DBMS_OUTPU T.put_line ( | |
1329 | ' INFORMATIO N - STAR T: turn_v ista_messa ging_off') ; | |
1330 | ||
1331 | EXEC UTE IMMEDI ATE | |
1332 | ' update pps nepl.epl_n ational_se ttings set boolean_v alue = 0 w here id = 14'; | |
1333 | ||
1334 | EXEC UTE IMMEDI ATE | |
1335 | ' update pps nepl.epl_n ational_se ttings set string_va lue = null where id = 15'; | |
1336 | ||
1337 | EXEC UTE IMMEDI ATE | |
1338 | ' update pps nepl.epl_n ational_se ttings set integer_v alue = 0 w here id = 16'; | |
1339 | ||
1340 | COMM IT; | |
1341 | ||
1342 | sys. DBMS_OUTPU T.put_line ( | |
1343 | ' INFORMATIO N - END: turn_vis ta_messagi ng_off'); | |
1344 | ||
1345 | IF v n_row_coun t = 0 | |
1346 | THEN | |
1347 | R ETURN (1); | |
1348 | END IF; | |
1349 | ||
1350 | RETU RN (0); | |
1351 | EXCEPTI ON | |
1352 | WHEN OTHERS | |
1353 | THEN | |
1354 | v n_code := SQLCODE; | |
1355 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1356 | s ys.DBMS_OU TPUT.put_l ine ( | |
1357 | 'turn _vista_mes saging_off : Error co de ' | |
1358 | || vn_co de | |
1359 | || ': ' | |
1360 | || vc_er rm); | |
1361 | R ETURN (1); | |
1362 | END tur n_vista_me ssaging_of f; | |
1363 | ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- | |
1364 | -- MAIN SE CTION | |
1365 | ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- | |
1366 | BEGIN | |
1367 | sys.DBM S_OUTPUT.p ut_line (C HR (10)); | |
1368 | sys.DBM S_OUTPUT.p ut_line ( | |
1369 | 'INF ORMATION - Checkin g to see i f PPS-N 2. 0 table(s) already e xists.'); | |
1370 | ||
1371 | vn_cont inue := ch eck_for_pp sn2; | |
1372 | ||
1373 | IF vn_c ontinue > 0 | |
1374 | THEN | |
1375 | sys. DBMS_OUTPU T.put_line ( | |
1376 | ' ERROR - ONE OR MORE PPS- N 2.0 OBJE CTS ALREAD Y EXISTS. STOPPING M IGRATION.' ); | |
1377 | sys. DBMS_OUTPU T.put_line ( | |
1378 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1379 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1380 | RETU RN; | |
1381 | END IF; | |
1382 | ||
1383 | vn_cont inue := cr eate_ppsn_ tables; | |
1384 | ||
1385 | IF vn_c ontinue > 0 | |
1386 | THEN | |
1387 | sys. DBMS_OUTPU T.put_line ( | |
1388 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1389 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1390 | RETU RN; | |
1391 | END IF; | |
1392 | ||
1393 | vn_cont inue := cr eate_ppsn_ views; | |
1394 | ||
1395 | IF vn_c ontinue > 0 | |
1396 | THEN | |
1397 | sys. DBMS_OUTPU T.put_line ( | |
1398 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1399 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1400 | RETU RN; | |
1401 | END IF; | |
1402 | ||
1403 | vn_cont inue := in sert_ppsn2 _data; | |
1404 | ||
1405 | IF vn_c ontinue > 0 | |
1406 | THEN | |
1407 | sys. DBMS_OUTPU T.put_line ( | |
1408 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1409 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1410 | RETU RN; | |
1411 | END IF; | |
1412 | ||
1413 | vn_cont inue := nd c_process_ 1; | |
1414 | ||
1415 | IF vn_c ontinue > 0 | |
1416 | THEN | |
1417 | sys. DBMS_OUTPU T.put_line ( | |
1418 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1419 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1420 | RETU RN; | |
1421 | END IF; | |
1422 | ||
1423 | vn_cont inue := nd c_process_ 2; | |
1424 | ||
1425 | IF vn_c ontinue > 0 | |
1426 | THEN | |
1427 | sys. DBMS_OUTPU T.put_line ( | |
1428 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1429 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1430 | RETU RN; | |
1431 | END IF; | |
1432 | ||
1433 | vn_cont inue := in sert_epl_n ational_se ttings; | |
1434 | ||
1435 | IF vn_c ontinue > 0 | |
1436 | THEN | |
1437 | sys. DBMS_OUTPU T.put_line ( | |
1438 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1439 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1440 | RETU RN; | |
1441 | END IF; | |
1442 | ||
1443 | vn_cont inue := up date_natio nal_settin g; | |
1444 | ||
1445 | IF vn_c ontinue > 0 | |
1446 | THEN | |
1447 | sys. DBMS_OUTPU T.put_line ( | |
1448 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1449 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1450 | RETU RN; | |
1451 | END IF; | |
1452 | ||
1453 | vn_cont inue := tu rn_vista_m essaging_o ff; | |
1454 | ||
1455 | IF vn_c ontinue > 0 | |
1456 | THEN | |
1457 | sys. DBMS_OUTPU T.put_line ( | |
1458 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1459 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1460 | RETU RN; | |
1461 | END IF; | |
1462 | ||
1463 | -- Reco mpile Sche ma and Gat her Statis tics | |
1464 | DBMS_UT ILITY.comp ile_schema (schema = > 'PPSNEPL '); | |
1465 | DBMS_ST ATS.gather _schema_st ats ('PPSN EPL'); | |
1466 | EXCEPTION | |
1467 | WHEN OT HERS | |
1468 | THEN | |
1469 | vn_c ode := SQL CODE; | |
1470 | vc_e rrm := SUB STR (SQLER RM, 1, 64) ; | |
1471 | sys. DBMS_OUTPU T.put_line ( | |
1472 | ' MAIN: Erro r code ' | | vn_code || ': ' || vc_errm); | |
1473 | END; | |
1474 | / | |
1475 | ||
1476 | SELECT 'Mi gration St opped: ' | | CURRENT_ TIMESTAMP FROM DUAL; | |
1477 | ||
1478 | SPOOL OFF | |
1479 | EXIT |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.