Produced by Araxis Merge on 7/9/2017 11:00:46 PM Eastern Daylight 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_3.0_Iter2_Build_360.zip\Unredacted\PS_PPS_DataMgmt\src\main\resources\PPSN3.0\DDL\migration_scripts | PPSN_2_migration_v10.sql | Fri Jun 30 19:07:22 2017 UTC |
2 | PPS_N_3.0_Iter2_Build_360.zip\Unredacted\PS_PPS_DataMgmt\src\main\resources\PPSN3.0\DDL\migration_scripts | PPSN_2_migration_v10.sql | Wed Jul 5 13:05:17 2017 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 3 | 2992 |
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.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 # | |
14 | REM # Us age: | |
15 | REM # In put Parame ters: | |
16 | REM # 1) None | |
17 | REM # Ou tput: | |
18 | REM # 1) Terminal | |
19 | REM # In ternal Scr ipt Parame ters: | |
20 | REM # 1) None | |
21 | REM # | |
22 | REM ###### ########## ########## ########## ########## ########## ########## ######### | |
23 | REM # Chan ge History | |
24 | REM # | |
25 | REM # DATE Use r Name DESCR IPTION | |
26 | REM # ---- ------ --- ---------- ---- ----- ---------- ---------- ---------- -------- | |
27 | REM # 04/2 9/2014 Dun can Shelle y Creat ed script | |
28 | REM # | |
29 | REM ###### ########## ########## ########## ########## ########## ########## ######## | |
30 | ||
31 | DECLARE | |
32 | -- CONS TANTS | |
33 | ||
34 | cc_sche ma_name CONSTANT V ARCHAR2 (3 5) := 'PPS NEPL'; | |
35 | ||
36 | -- VARI ABLES | |
37 | ||
38 | vc_sql_ statement V ARCHAR2 (4 000); | |
39 | vc_errm V ARCHAR2 (6 4); | |
40 | vn_code N UMBER; | |
41 | vn_cont inue N UMBER; | |
42 | ||
43 | vn_row_ count N UMBER; | |
44 | ||
45 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
46 | -- NAME : check_for_ ppsn2 | |
47 | -- TYPE : Function | |
48 | -- DESC RIPTSION: Check to s ee if PPS_ N 2.0 obje ct already created | |
49 | -- INPU TS: None | |
50 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
51 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
52 | FUNCTIO N check_fo r_ppsn2 | |
53 | RETU RN NUMBER | |
54 | IS | |
55 | BEGIN | |
56 | SELE CT COUNT ( *) | |
57 | IN TO vn_row_ count | |
58 | FR OM sys.dba _tables | |
59 | WHE RE own er = cc_sc hema_name | |
60 | AND tab le_name IN ('EPL_NDF _UPDATE_FI LE' | |
61 | ,'EPL_STA TUS' | |
62 | ,'EPL_IEN _GENERATOR ' | |
63 | ,'EPL_NDF _OUTGOING_ DIFFERENCE S' | |
64 | ,'EPL_VIS TA_UPDATE' | |
65 | ,'EPL_VIS TA_STATUS' | |
66 | ,'TEMP_EP L_NDCS' | |
67 | ,'EPL_PAC KAGE_SIZES ' | |
68 | ,'EPL_DDI _SEVERITY' ); | |
69 | ||
70 | IF v n_row_coun t > 0 | |
71 | THEN | |
72 | R ETURN (1); | |
73 | END IF; | |
74 | ||
75 | SELE CT COUNT ( *) | |
76 | IN TO vn_row_ count | |
77 | FR OM sys.dba _views | |
78 | WHE RE own er = cc_sc hema_name | |
79 | AND vie w_name IN ('FDB_GCNS EQNO_PEM_V ' | |
80 | ,'FDB_GCNS EQNO_PLBLW _V' | |
81 | ,'FDB_MONO GRAPH_PEM_ V' | |
82 | ,'FDB_PLBL WARNINGS_V '); | |
83 | ||
84 | IF v n_row_coun t > 0 | |
85 | THEN | |
86 | R ETURN (1); | |
87 | END IF; | |
88 | ||
89 | RETU RN (0); | |
90 | EXCEPTI ON | |
91 | WHEN OTHERS | |
92 | THEN | |
93 | v n_code := SQLCODE; | |
94 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
95 | D BMS_OUTPUT .put_line ( | |
96 | 'check_f or_ppsn2: Error code ' || vn_c ode || ': ' || vc_er rm); | |
97 | s ys.DBMS_OU TPUT.put_l ine ('ERRO R - EX ITING SCRI PT...'); | |
98 | R ETURN (1); | |
99 | END che ck_for_pps n2; | |
100 | ||
101 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
102 | -- NAME : create_pps n_tables | |
103 | -- TYPE : Function | |
104 | -- DESC RIPTSION: Create PPS -N 2.0 tab les | |
105 | -- INPU TS: None | |
106 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
107 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
108 | FUNCTIO N create_p psn_tables | |
109 | RETU RN NUMBER | |
110 | IS | |
111 | BEGIN | |
112 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
113 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: cre ate_ppsn_t ables'); | |
114 | ||
115 | -- C reate sequ ences sect ion ------ ---------- ---------- ---------- ---------- --- | |
116 | vc_s ql_stateme nt := | |
117 | ' CREATE SEQ UENCE ppsn epl.update _file_sequ ence START WITH 1 IN CREMENT BY 1 NOCACHE NOCYCLE'; | |
118 | ||
119 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
120 | ||
121 | -- C reate tabl es section --------- ---------- ---------- ---------- ---------- | |
122 | ||
123 | -- T able PPSNE PL.EPL_NDF _OUTGOING_ DIFFERENCE S | |
124 | ||
125 | vc_s ql_stateme nt := | |
126 | 'CREATE TABLE ppsn epl.epl_nd f_outgoing _differenc es (' | |
127 | | | ' ndf_ou tgoing_dif ferences_i d NUMBER (38, 0) N OT NULL ' | |
128 | | | ',new_va lue VARCHA R2 (256) NULL ' | |
129 | | | ',old_va lue VARCHA R2 (256) NULL ' | |
130 | | | ',vista_ file_numbe r VARCHA R2 (20) NO T NULL ' | |
131 | | | ',vista_ field_numb er VARCHA R2 (10) NO T NULL ' | |
132 | | | ',vista_ ien VARCHA R2 (50) NO T NULL ' | |
133 | | | ',ndc_up date_file_ fk NUMBER (38, 0) ' | |
134 | | | ',action _type VARCHA R2 (10) NO T NULL ' | |
135 | | | ',create d_by VARCHA R2 (50) NO T NULL ' | |
136 | | | ',create d_dtm TIMEST AMP (6) NO T NULL) ' | |
137 | | | 'TABLESP ACE ppsnep l_data'; | |
138 | ||
139 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
140 | ||
141 | vc_s ql_stateme nt := | |
142 | ' 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'; | |
143 | ||
144 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
145 | ||
146 | -- N ew constra int | |
147 | vc_s ql_stateme nt := | |
148 | ' 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'; | |
149 | ||
150 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
151 | ||
152 | ||
153 | -- T able PPSNE PL.EPL_IEN _GENERATOR | |
154 | ||
155 | vc_s ql_stateme nt := | |
156 | 'CREATE TABLE ppsn epl.epl_ie n_generato r ( ' | |
157 | | | 'CONCEPT VARCHAR 2(50) NOT NULL, ' | |
158 | | | 'LAST_US ED_IEN NUMBER( 30 , 0) NO T NULL, ' | |
159 | | | 'CREATED _BY VARCHAR 2(50) NOT NULL, ' | |
160 | | | 'CREATED _DTM TIMESTA MP NOT NUL L, ' | |
161 | | | 'LAST_MO DIFIED_BY VARCHAR 2(50), ' | |
162 | | | 'LAST_MO DIFIED_DTM TIMESTA MP) ' | |
163 | | | 'TABLESP ACE ppsnep l_data'; | |
164 | ||
165 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
166 | ||
167 | vc_s ql_stateme nt := | |
168 | ' CREATE IND EX PPSNEPL .EPL_IEN_P K_IDX ON P PSNEPL.EPL _IEN_GENER ATOR (CONC EPT ASC) T ABLESPACE ppsnepl_da ta'; | |
169 | ||
170 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
171 | ||
172 | -- N ew constra int | |
173 | vc_s ql_stateme nt := | |
174 | ' 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 '; | |
175 | ||
176 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
177 | ||
178 | ||
179 | -- T able PPSNE PL.EPL_NDF _UPDATE_FI LE | |
180 | ||
181 | vc_s ql_stateme nt := | |
182 | 'CREATE TABLE ppsn epl.epl_nd f_update_f ile( ' | |
183 | | | ' ndf_up date_file_ id N UMERIC (30 ) NOT NULL ' | |
184 | | | ',file_n ame V ARCHAR (20 0) ' | |
185 | | | ',commen ts V ARCHAR (20 00) ' | |
186 | | | ',status _id_fk N UMERIC (30 ) NOT NULL ' | |
187 | | | ',prod_t ransmissio n_date T IMESTAMP ' | |
188 | | | ',test_t ransmissio n_date T IMESTAMP ' | |
189 | | | ',status _modified_ date T IMESTAMP ' | |
190 | | | ',file_d irectory_p ath V ARCHAR (50 0) ' | |
191 | | | ',create d_by V ARCHAR (50 ) NOT NULL ' | |
192 | | | ',create d_dtm T IMESTAMP N OT NULL ' | |
193 | | | ',last_m odified_by V ARCHAR (50 ) ' | |
194 | | | ',last_m odified_dt m T IMESTAMP) ' | |
195 | | | 'TABLESP ACE ppsnep l_data'; | |
196 | ||
197 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
198 | ||
199 | vc_s ql_stateme nt := | |
200 | ' 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' ; | |
201 | ||
202 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
203 | ||
204 | vc_s ql_stateme nt := | |
205 | ' CREATE IND EX ppsnepl .idxstatus _id ON pps nepl.epl_n df_update_ file (stat us_id_fk) TABLESPACE ppsnepl_d ata'; | |
206 | ||
207 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
208 | ||
209 | vc_s ql_stateme nt := | |
210 | ' 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' ; | |
211 | ||
212 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
213 | ||
214 | ||
215 | -- T able PPSNE PL.EPL_STA TUS | |
216 | ||
217 | vc_s ql_stateme nt := | |
218 | 'CREATE TABLE ppsn epl.epl_st atus( ' | |
219 | | | ' status _id NUMERI C (30) NOT NULL ' | |
220 | | | ',status _name VARCHA R (200) ' | |
221 | | | ',status _desc VARCHA R (1000) ' | |
222 | | | ',create d_by VARCHA R (50) NOT NULL ' | |
223 | | | ',create d_dtm TIMEST AMP NOT NU LL ' | |
224 | | | ',last_m odified_by VARCHA R (50) ' | |
225 | | | ',last_m odified_dt m TIMEST AMP) ' | |
226 | | | ' TABLES PACE ppsne pl_data'; | |
227 | ||
228 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
229 | ||
230 | vc_s ql_stateme nt := | |
231 | ' CREATE UNI QUE INDEX ppsnepl.pk status ON ppsnepl.ep l_status ( status_id) TABLESPAC E ppsnepl_ data'; | |
232 | ||
233 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
234 | ||
235 | vc_s ql_stateme nt := | |
236 | ' ALTER TABL E ppsnepl. epl_status ADD CONST RAINT pkst atus PRIMA RY KEY (st atus_id) U SING INDEX ppsnepl.p kstatus'; | |
237 | ||
238 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
239 | ||
240 | ||
241 | -- T able PPSNE PL.EPL_VIS TA_UPDATE | |
242 | ||
243 | vc_s ql_stateme nt := | |
244 | 'CREATE TABLE ppsn epl.epl_vi sta_update ( ' | |
245 | | | ' vista_ update_id NUMERI C (30) NOT NULL ' | |
246 | | | ',file_f k NUMERI C (30) NOT NULL ' | |
247 | | | ',site NUMERI C (30) NOT NULL ' | |
248 | | | ',commen ts VARCHA R (2000) ' | |
249 | | | ',vista_ status_id_ fk NUMERI C (30) NOT NULL ' | |
250 | | | ',transm ission_dat e TIMEST AMP) ' | |
251 | | | 'TABLESP ACE ppsnep l_data'; | |
252 | ||
253 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
254 | ||
255 | vc_s ql_stateme nt := | |
256 | ' 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'; | |
257 | ||
258 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
259 | ||
260 | vc_s ql_stateme nt := | |
261 | ' CREATE IND EX ppsnepl .idxvistas tatusid ON ppsnepl.e pl_vista_u pdate (vis ta_status_ id_fk) TAB LESPACE pp snepl_data '; | |
262 | ||
263 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
264 | ||
265 | vc_s ql_stateme nt := | |
266 | ' 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' ; | |
267 | ||
268 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
269 | ||
270 | ||
271 | -- T able PPSNE PL.EPL_VIS TA_STATUS | |
272 | ||
273 | vc_s ql_stateme nt := | |
274 | 'CREATE TABLE ppsn epl.epl_vi sta_status ( ' | |
275 | | | ' vista_ status_id NUMERI C (30) NOT NULL ' | |
276 | | | ',status _name VARCHA R (200) ' | |
277 | | | ',status _desc VARCHA R (1000) ' | |
278 | | | ',create d_by VARCHA R (50) NOT NULL ' | |
279 | | | ',create d_dtm TIMEST AMP NOT NU LL ' | |
280 | | | ',last_m odified_by VARCHA R (50) ' | |
281 | | | ',last_m odified_dt m TIMEST AMP) ' | |
282 | | | 'TABLESP ACE ppsnep l_data'; | |
283 | ||
284 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
285 | ||
286 | vc_s ql_stateme nt := | |
287 | ' 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'; | |
288 | ||
289 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
290 | ||
291 | vc_s ql_stateme nt := | |
292 | ' 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' ; | |
293 | ||
294 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
295 | ||
296 | ||
297 | -- T able PPSNE PL.EPL_DDI _SEVERITY | |
298 | ||
299 | vc_s ql_stateme nt := | |
300 | 'CREATE TABLE ppsn epl.epl_dd i_severity ( ' | |
301 | | | ' epl_id NUMBER (30, 0) N OT NULL ' | |
302 | | | ' ,name VARCH AR2 (50) N OT NULL ' | |
303 | | | ' ,creat ed_by VARCH AR2 (50) N OT NULL ' | |
304 | | | ' ,creat ed_dtm TIMES TAMP (6) N OT NULL ' | |
305 | | | ' ,last_ modified_b y VARCH AR2 (50) ' | |
306 | | | ' ,last_ modified_d tm TIMES TAMP (6)) ' | |
307 | | | ' TABLES PACE ppsne pl_data'; | |
308 | ||
309 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
310 | ||
311 | vc_s ql_stateme nt := | |
312 | ' 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'; | |
313 | ||
314 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
315 | ||
316 | vc_s ql_stateme nt := | |
317 | ' 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'; | |
318 | ||
319 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
320 | ||
321 | ||
322 | -- T able PPSNE PL.EPL_DRU G_DRUG_INT ERACTION | |
323 | ||
324 | vc_s ql_stateme nt := | |
325 | 'CREATE TABLE ppsn epl.epl_dr ug_drug_in teraction( ' | |
326 | | | ' epl_dr ug_drug_in teraction_ id NUMBE R (38) NOT NULL ' | |
327 | | | ' ,inter action_nam e VARC HAR2 (200) NOT NULL ' | |
328 | | | ' ,dru g_ingredie nt_1 NU MBER (30, 0) NOT NUL L ' | |
329 | | | ' ,dru g_ingredie nt_2 NU MBER (30, 0) NOT NUL L ' | |
330 | | | ' ,sev erity NU MBER (30, 0) NOT NUL L ' | |
331 | | | ' ,nat ionally_en tered NU MBER (1, 0 ) ' | |
332 | | | ' ,tot al_indexes NU MBER (38, 0) ' | |
333 | | | ' ,loc ally_edite d NU MBER (1, 0 ) ' | |
334 | | | ' ,vis ta_ien NU MBER(30 , 0) NOT NUL L ' | |
335 | | | ' ,cre ated_by VA RCHAR2 (50 ) NOT NULL ' | |
336 | | | ' ,cre ated_dtm TI MESTAMP (6 ) NOT NULL ' | |
337 | | | ' ,ina ctivation_ date TI MESTAMP (6 ) ' | |
338 | | | ' ,las t_modified _by VA RCHAR2 (50 ) ' | |
339 | | | ' ,las t_modified _dtm TI MESTAMP (6 )) ' | |
340 | | | ' TABLES PACE ppsne pl_data'; | |
341 | ||
342 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
343 | ||
344 | vc_s ql_stateme nt := | |
345 | ' 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)' ; | |
346 | ||
347 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
348 | ||
349 | vc_s ql_stateme nt := | |
350 | ' 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'; | |
351 | ||
352 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
353 | ||
354 | vc_s ql_stateme nt := | |
355 | ' 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'; | |
356 | ||
357 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
358 | ||
359 | vc_s ql_stateme nt := | |
360 | ' 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'; | |
361 | ||
362 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
363 | ||
364 | vc_s ql_stateme nt := | |
365 | ' CREATE IND EX ppsnepl .severity_ idx ON pps nepl.epl_d rug_drug_i nteraction (severity ) TABLESPA CE ppsnepl _data'; | |
366 | ||
367 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
368 | ||
369 | vc_s ql_stateme nt := | |
370 | ' 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 '; | |
371 | ||
372 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
373 | ||
374 | -- C reate syno nyms secti on ------- ---------- ---------- ---------- ---------- -- | |
375 | ||
376 | -- C reate rela tionships section -- ---------- ---------- ---------- ---------- ------- | |
377 | ||
378 | vc_s ql_stateme nt := | |
379 | ' 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))' ; | |
380 | ||
381 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
382 | ||
383 | vc_s ql_stateme nt := | |
384 | ' 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))' ; | |
385 | ||
386 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
387 | ||
388 | vc_s ql_stateme nt := | |
389 | ' 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))'; | |
390 | ||
391 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
392 | ||
393 | vc_s ql_stateme nt := | |
394 | ' 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)'; | |
395 | ||
396 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
397 | ||
398 | vc_s ql_stateme nt := | |
399 | ' 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)'; | |
400 | ||
401 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
402 | ||
403 | vc_s ql_stateme nt := | |
404 | ' 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)'; | |
405 | ||
406 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
407 | ||
408 | -- G rant permi ssions sec tion ----- ---------- ---------- ---------- ---------- ---- | |
409 | ||
410 | vc_s ql_stateme nt := | |
411 | ' GRANT SELE CT ON ppsn epl.epl_nd f_outgoing _differenc es TO PPSN EPL_READ_O NLY_ROLE'; | |
412 | ||
413 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
414 | ||
415 | vc_s ql_stateme nt := | |
416 | ' GRANT SELE CT ON ppsn epl.epl_ie n_generato r TO PPSNE PL_READ_ON LY_ROLE'; | |
417 | ||
418 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
419 | ||
420 | vc_s ql_stateme nt := | |
421 | ' GRANT SELE CT ON ppsn epl.epl_nd f_update_f ile TO PPS NEPL_READ_ ONLY_ROLE' ; | |
422 | ||
423 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
424 | ||
425 | vc_s ql_stateme nt := | |
426 | ' GRANT SELE CT ON ppsn epl.epl_st atus TO PP SNEPL_READ _ONLY_ROLE '; | |
427 | ||
428 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
429 | ||
430 | vc_s ql_stateme nt := | |
431 | ' GRANT SELE CT ON ppsn epl.epl_vi sta_update TO PPSNEP L_READ_ONL Y_ROLE'; | |
432 | ||
433 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
434 | ||
435 | vc_s ql_stateme nt := | |
436 | ' GRANT SELE CT ON ppsn epl.epl_vi sta_status TO PPSNEP L_READ_ONL Y_ROLE'; | |
437 | ||
438 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
439 | ||
440 | vc_s ql_stateme nt := | |
441 | ' GRANT SELE CT ON ppsn epl.epl_dd i_severity TO PPSNEP L_READ_ONL Y_ROLE'; | |
442 | ||
443 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
444 | ||
445 | vc_s ql_stateme nt := | |
446 | ' GRANT SELE CT ON ppsn epl.epl_dr ug_drug_in teraction TO PPSNEPL _READ_ONLY _ROLE'; | |
447 | ||
448 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
449 | ||
450 | vc_s ql_stateme nt := | |
451 | ' GRANT SELE CT ON ppsn epl.update _file_sequ ence TO PP SNEPL_READ _ONLY_ROLE '; | |
452 | ||
453 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
454 | ||
455 | ||
456 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: creat e_ppsn_tab les'); | |
457 | RETU RN (0); | |
458 | EXCEPTI ON | |
459 | WHEN OTHERS | |
460 | THEN | |
461 | v n_code := SQLCODE; | |
462 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
463 | s ys.DBMS_OU TPUT.put_l ine ( | |
464 | 'create_ ppsn_table s: Error c ode ' || v n_code || ': ' || vc _errm); | |
465 | s ys.DBMS_OU TPUT.put_l ine ( | |
466 | 'create_ ppsn_table s: last va lue for vc _sql_state ment '); | |
467 | s ys.DBMS_OU TPUT.put_l ine ( | |
468 | 'create_ ppsn_table s: ' || vc _sql_state ment); | |
469 | R ETURN (1); | |
470 | END cre ate_ppsn_t ables; | |
471 | ||
472 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
473 | -- NAME : create_pps n_views | |
474 | -- TYPE : Function | |
475 | -- DESC RIPTSION: Create PPS -N 2.0 vie ws | |
476 | -- INPU TS: None | |
477 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
478 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
479 | FUNCTIO N create_p psn_views | |
480 | RETU RN NUMBER | |
481 | IS | |
482 | BEGIN | |
483 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
484 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: cre ate_ppsn_v iews'); | |
485 | ||
486 | -- C reate view s section ---------- ---------- ---------- ---------- --------- | |
487 | ||
488 | -- V iew PPSNEP L.FDB_GCNS EQNO_PEM_V | |
489 | ||
490 | vc_s ql_stateme nt := | |
491 | ' GRANT SELE CT ON fdb_ dif.fdb_gc nseqno_pem TO ppsnep l WITH GRA NT OPTION' ; | |
492 | ||
493 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
494 | ||
495 | vc_s ql_stateme nt := | |
496 | ' CREATE VIE W ppsnepl. fdb_gcnseq no_pem_v A S SELECT * FROM fdb_ dif.fdb_gc nseqno_pem '; | |
497 | ||
498 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
499 | ||
500 | vc_s ql_stateme nt := | |
501 | ' GRANT SELE CT ON ppsn epl.fdb_gc nseqno_pem _v TO ppsn epl_read_o nly_role'; | |
502 | ||
503 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
504 | ||
505 | ||
506 | -- V iew FDB_GC NSEQNO_PLB LW_V | |
507 | ||
508 | vc_s ql_stateme nt := | |
509 | ' GRANT SELE CT ON fdb_ dif.fdb_gc nseqno_plb lw TO ppsn epl WITH G RANT OPTIO N'; | |
510 | ||
511 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
512 | ||
513 | vc_s ql_stateme nt := | |
514 | ' CREATE VIE W ppsnepl. fdb_gcnseq no_plblw_v AS SELECT * FROM fd b_dif.fdb_ gcnseqno_p lblw'; | |
515 | ||
516 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
517 | ||
518 | vc_s ql_stateme nt := | |
519 | ' GRANT SELE CT ON ppsn epl.fdb_gc nseqno_plb lw_v TO pp snepl_read _only_role '; | |
520 | ||
521 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
522 | ||
523 | ||
524 | -- V iew PPSNEP L.FDB_MONO GRAPH_PEM_ V | |
525 | ||
526 | vc_s ql_stateme nt := | |
527 | ' GRANT SELE CT ON fdb_ dif.fdb_mo nograph_pe m TO ppsne pl WITH GR ANT OPTION '; | |
528 | ||
529 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
530 | ||
531 | vc_s ql_stateme nt := | |
532 | ' CREATE VIE W ppsnepl. fdb_monogr aph_pem_v AS SELECT * FROM fdb _dif.fdb_m onograph_p em'; | |
533 | ||
534 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
535 | ||
536 | vc_s ql_stateme nt := | |
537 | ' GRANT SELE CT ON ppsn epl.fdb_mo nograph_pe m_v TO pps nepl_read_ only_role' ; | |
538 | ||
539 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
540 | ||
541 | ||
542 | -- V iew PPSNEP L.FDB_PLBL WARNINGS_V | |
543 | ||
544 | vc_s ql_stateme nt := | |
545 | ' GRANT SELE CT ON fdb_ dif.fdb_pl blwarnings TO ppsnep l WITH GRA NT OPTION' ; | |
546 | ||
547 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
548 | ||
549 | vc_s ql_stateme nt := | |
550 | ' CREATE VIE W ppsnepl. fdb_plblwa rnings_v A S SELECT * FROM fdb_ dif.fdb_pl blwarnings '; | |
551 | ||
552 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
553 | ||
554 | vc_s ql_stateme nt := | |
555 | ' GRANT SELE CT ON ppsn epl.fdb_pl blwarnings _v TO ppsn epl_read_o nly_role'; | |
556 | ||
557 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
558 | ||
559 | ||
560 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: creat e_ppsn_vie ws'); | |
561 | RETU RN (0); | |
562 | EXCEPTI ON | |
563 | WHEN OTHERS | |
564 | THEN | |
565 | v n_code := SQLCODE; | |
566 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
567 | s ys.DBMS_OU TPUT.put_l ine ( | |
568 | 'create_ ppsn_views : Error co de ' || vn _code || ' : ' || vc_ errm); | |
569 | s ys.DBMS_OU TPUT.put_l ine ( | |
570 | 'create_ ppsn_views : last val ue for vc_ sql_statem ent '); | |
571 | s ys.DBMS_OU TPUT.put_l ine ('crea te_ppsn_vi ews: ' || vc_sql_sta tement); | |
572 | R ETURN (1); | |
573 | END cre ate_ppsn_v iews; | |
574 | ||
575 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
576 | -- NAME : insert_pps n2_data | |
577 | -- TYPE : Function | |
578 | -- DESC RIPTSION: INSERT PPS -N 2.0 dat a into tab les | |
579 | -- INPU TS: None | |
580 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
581 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
582 | FUNCTIO N insert_p psn2_data | |
583 | RETU RN NUMBER | |
584 | IS | |
585 | BEGIN | |
586 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
587 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ins ert_ppsn2_ data'); | |
588 | ||
589 | vc_s ql_stateme nt := | |
590 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
591 | | | 'VALUES (1,''Initi ated'',''F ile Creati on Process Has Been Initiated' ',''Initia l Load'',s ysdate)'; | |
592 | ||
593 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
594 | ||
595 | sys. DBMS_OUTPU T.put_line ( | |
596 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
597 | | | TO_CHAR (SQL%ROWCO UNT)); | |
598 | ||
599 | vc_s ql_stateme nt := | |
600 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
601 | | | '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)'; | |
602 | ||
603 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
604 | ||
605 | sys. DBMS_OUTPU T.put_line ( | |
606 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
607 | | | TO_CHAR (SQL%ROWCO UNT)); | |
608 | ||
609 | vc_s ql_stateme nt := | |
610 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
611 | | | '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)'; | |
612 | ||
613 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
614 | ||
615 | sys. DBMS_OUTPU T.put_line ( | |
616 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
617 | | | TO_CHAR (SQL%ROWCO UNT)); | |
618 | ||
619 | vc_s ql_stateme nt := | |
620 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
621 | | | '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 )'; | |
622 | ||
623 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
624 | ||
625 | sys. DBMS_OUTPU T.put_line ( | |
626 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
627 | | | TO_CHAR (SQL%ROWCO UNT)); | |
628 | ||
629 | vc_s ql_stateme nt := | |
630 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
631 | | | '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 )'; | |
632 | ||
633 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
634 | ||
635 | sys. DBMS_OUTPU T.put_line ( | |
636 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
637 | | | TO_CHAR (SQL%ROWCO UNT)); | |
638 | ||
639 | vc_s ql_stateme nt := | |
640 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
641 | | | '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)'; | |
642 | ||
643 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
644 | ||
645 | sys. DBMS_OUTPU T.put_line ( | |
646 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
647 | | | TO_CHAR (SQL%ROWCO UNT)); | |
648 | ||
649 | vc_s ql_stateme nt := | |
650 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
651 | | | '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)'; | |
652 | ||
653 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
654 | ||
655 | sys. DBMS_OUTPU T.put_line ( | |
656 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
657 | | | TO_CHAR (SQL%ROWCO UNT)); | |
658 | ||
659 | vc_s ql_stateme nt := | |
660 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
661 | | | '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)'; | |
662 | ||
663 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
664 | ||
665 | sys. DBMS_OUTPU T.put_line ( | |
666 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
667 | | | TO_CHAR (SQL%ROWCO UNT)); | |
668 | ||
669 | vc_s ql_stateme nt := | |
670 | 'INSERT INTO ppsne pl.epl_sta tus (STATU S_ID,STATU S_NAME,STA TUS_DESC,C REATED_BY, CREATED_D TM) ' | |
671 | | | 'VALUES (99,''NDF Update Pro cess Error '',''An er ror occurr ed during NDF update file proc ess'',''In itial Load '',sysdate )'; | |
672 | ||
673 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
674 | ||
675 | sys. DBMS_OUTPU T.put_line ( | |
676 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_status : ' | |
677 | | | TO_CHAR (SQL%ROWCO UNT)); | |
678 | ||
679 | COMM IT; | |
680 | ||
681 | vc_s ql_stateme nt := | |
682 | '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) ' | |
683 | | | ' VALUES (0,''PPS_ 0PRV_0NEW. DAT'',''In itial Seed Data'',8, SYSDATE,SY SDATE,SYSD ATE,''PPS/ PROD'',''D eveloper'' ,SYSDATE,' 'Developer '',SYSDATE )'; | |
684 | ||
685 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
686 | ||
687 | sys. DBMS_OUTPU T.put_line ( | |
688 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ndf_upda te_file : ' | |
689 | | | TO_CHAR (SQL%ROWCO UNT)); | |
690 | ||
691 | ||
692 | COMM IT; | |
693 | ||
694 | vc_s ql_stateme nt := | |
695 | '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) ' | |
696 | | | 'VALUES (''EPL_DRU G_UNITS'', 10000, '' DEV'', SYS DATE, ''DE V'', SYSDA TE)'; | |
697 | ||
698 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
699 | ||
700 | sys. DBMS_OUTPU T.put_line ( | |
701 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
702 | | | TO_CHAR (SQL%ROWCO UNT)); | |
703 | ||
704 | vc_s ql_stateme nt := | |
705 | '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) ' | |
706 | | | 'VALUES (''EPL_DOS AGE_FORMS' ', 10000, ''DEV'', S YSDATE, '' DEV'', SYS DATE)'; | |
707 | ||
708 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
709 | ||
710 | sys. DBMS_OUTPU T.put_line ( | |
711 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
712 | | | TO_CHAR (SQL%ROWCO UNT)); | |
713 | ||
714 | vc_s ql_stateme nt := | |
715 | '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) ' | |
716 | | | 'VALUES (''EPL_ING REDIENTS'' , 10000, ' 'DEV'', SY SDATE, ''D EV'', SYSD ATE)'; | |
717 | ||
718 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
719 | ||
720 | sys. DBMS_OUTPU T.put_line ( | |
721 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
722 | | | TO_CHAR (SQL%ROWCO UNT)); | |
723 | ||
724 | vc_s ql_stateme nt := | |
725 | '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) ' | |
726 | | | 'VALUES (''EPL_MAN UFACTURERS '', 10000, ''DEV'', SYSDATE, ' 'DEV'', SY SDATE)'; | |
727 | ||
728 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
729 | ||
730 | sys. DBMS_OUTPU T.put_line ( | |
731 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
732 | | | TO_CHAR (SQL%ROWCO UNT)); | |
733 | ||
734 | vc_s ql_stateme nt := | |
735 | '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) ' | |
736 | | | 'VALUES (''EPL_NDC S'', 10000 , ''DEV'', SYSDATE, ''DEV'', S YSDATE)'; | |
737 | ||
738 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
739 | ||
740 | sys. DBMS_OUTPU T.put_line ( | |
741 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
742 | | | TO_CHAR (SQL%ROWCO UNT)); | |
743 | ||
744 | vc_s ql_stateme nt := | |
745 | '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) ' | |
746 | | | 'VALUES (''EPL_PAC KAGE_TYPES '', 10000, ''DEV'', SYSDATE, ' 'DEV'', SY SDATE)'; | |
747 | ||
748 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
749 | ||
750 | sys. DBMS_OUTPU T.put_line ( | |
751 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
752 | | | TO_CHAR (SQL%ROWCO UNT)); | |
753 | ||
754 | vc_s ql_stateme nt := | |
755 | '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) ' | |
756 | | | 'VALUES (''EPL_PRO DUCTS'', 1 0000, ''DE V'', SYSDA TE, ''DEV' ', SYSDATE )'; | |
757 | ||
758 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
759 | ||
760 | sys. DBMS_OUTPU T.put_line ( | |
761 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
762 | | | TO_CHAR (SQL%ROWCO UNT)); | |
763 | ||
764 | vc_s ql_stateme nt := | |
765 | '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) ' | |
766 | | | 'VALUES (''EPL_VA_ DISPENSE_U NITS'', 10 000, ''DEV '', SYSDAT E, ''DEV'' , SYSDATE) '; | |
767 | ||
768 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
769 | ||
770 | sys. DBMS_OUTPU T.put_line ( | |
771 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
772 | | | TO_CHAR (SQL%ROWCO UNT)); | |
773 | ||
774 | vc_s ql_stateme nt := | |
775 | '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) ' | |
776 | | | 'VALUES (''EPL_VA_ DRUG_CLASS ES'', 1000 0, ''DEV'' , SYSDATE, ''DEV'', SYSDATE)'; | |
777 | ||
778 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
779 | ||
780 | sys. DBMS_OUTPU T.put_line ( | |
781 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
782 | | | TO_CHAR (SQL%ROWCO UNT)); | |
783 | ||
784 | vc_s ql_stateme nt := | |
785 | '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) ' | |
786 | | | 'VALUES (''EPL_VA_ GEN_NAMES' ', 10000, ''DEV'', S YSDATE, '' DEV'', SYS DATE)'; | |
787 | ||
788 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
789 | ||
790 | sys. DBMS_OUTPU T.put_line ( | |
791 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
792 | | | TO_CHAR (SQL%ROWCO UNT)); | |
793 | ||
794 | /* - 5/10/ 2017 D.T. JAVA WILL NOW HANDLE THIS OPER ATION **** ********** ********** ********** ********** ********** ********** *** | |
795 | ||
796 | vc_s ql_stateme nt := | |
797 | '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) ' | |
798 | | | 'VALUES (''EPL_DRU G_DRUG_INT ERACTION'' , 10843, ' 'DEV'', SY SDATE, ''D EV'', SYSD ATE)'; | |
799 | ||
800 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
801 | */ | |
802 | ||
803 | sys. DBMS_OUTPU T.put_line ( | |
804 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ien_gene rator : ' | |
805 | | | TO_CHAR (SQL%ROWCO UNT)); | |
806 | ||
807 | COMM IT; | |
808 | ||
809 | vc_s ql_stateme nt := | |
810 | '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) ' | |
811 | | | 'VALUES (1, ''STAR TED'', ''T he process has star. ..'', ''sy stem'', CU RRENT_TIME STAMP, ''s ystem'', C URRENT_TIM ESTAMP)'; | |
812 | ||
813 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
814 | ||
815 | sys. DBMS_OUTPU T.put_line ( | |
816 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_vista_st atus : ' | |
817 | | | TO_CHAR (SQL%ROWCO UNT)); | |
818 | ||
819 | vc_s ql_stateme nt := | |
820 | '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) ' | |
821 | | | 'VALUES (2, ''COMP LETED'', ' 'The proce ss has com p...'', '' system'', CURRENT_TI MESTAMP, ' 'system'', CURRENT_T IMESTAMP)' ; | |
822 | ||
823 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
824 | ||
825 | sys. DBMS_OUTPU T.put_line ( | |
826 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_vista_st atus : ' | |
827 | | | TO_CHAR (SQL%ROWCO UNT)); | |
828 | ||
829 | vc_s ql_stateme nt := | |
830 | '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) ' | |
831 | | | 'VALUES (1, ''CRIT ICAL'', '' 360'', TIM ESTAMP ''2 014-05-09 13:02:39'' , ''360'', TIMESTAMP ''2014-05 -09 13:02: 39'')'; | |
832 | ||
833 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
834 | ||
835 | sys. DBMS_OUTPU T.put_line ( | |
836 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ddi_seve rity : ' | |
837 | | | TO_CHAR (SQL%ROWCO UNT)); | |
838 | ||
839 | vc_s ql_stateme nt := | |
840 | '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) ' | |
841 | | | 'VALUES (2, ''SIGN IFICANT'', ''360'', TIMESTAMP ''2014-05- 09 13:10:0 0'', ''360 '', TIMEST AMP ''2014 -05-09 13: 10:00'')'; | |
842 | ||
843 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
844 | ||
845 | sys. DBMS_OUTPU T.put_line ( | |
846 | 'INFORMA TION - N umber of r ow(s) inse rted into ppsnepl.ep l_ddi_seve rity : ' | |
847 | | | TO_CHAR (SQL%ROWCO UNT)); | |
848 | ||
849 | COMM IT; | |
850 | ||
851 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: inser t_ppsn2_da ta'); | |
852 | RETU RN (0); | |
853 | EXCEPTI ON | |
854 | WHEN OTHERS | |
855 | THEN | |
856 | v n_code := SQLCODE; | |
857 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
858 | s ys.DBMS_OU TPUT.put_l ine ( | |
859 | 'insert_ ppsn2_data : Error co de ' || vn _code || ' : ' || vc_ errm); | |
860 | s ys.DBMS_OU TPUT.put_l ine ( | |
861 | 'insert_ ppsn2_data : last val ue for vc_ sql_statem ent '); | |
862 | s ys.DBMS_OU TPUT.put_l ine ('inse rt_ppsn2_d ata: ' || vc_sql_sta tement); | |
863 | R ETURN (1); | |
864 | END ins ert_ppsn2_ data; | |
865 | ||
866 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
867 | -- NAME : ndc_proces s_1 | |
868 | -- TYPE : Function | |
869 | -- DESC RIPTSION: Create epl _package_s ize table | |
870 | -- INPU TS: None | |
871 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
872 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
873 | FUNCTIO N ndc_proc ess_1 | |
874 | RETU RN NUMBER | |
875 | IS | |
876 | BEGIN | |
877 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
878 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ndc _process_1 '); | |
879 | ||
880 | -- C reate temp table | |
881 | ||
882 | vc_s ql_stateme nt := | |
883 | ' CREATE TAB LE ppsnepl .temp_epl_ ndcs AS SE LECT * FRO M ppsnepl. epl_ndcs'; | |
884 | ||
885 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
886 | ||
887 | vc_s ql_stateme nt := | |
888 | ' ALTER TABL E ppsnepl. temp_epl_n dcs ADD (C ONSTRAINT temp_epl_n dcs_pk PRI MARY KEY ( epl_id))'; | |
889 | ||
890 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
891 | ||
892 | -- C reate epl_ package_si zes table | |
893 | ||
894 | vc_s ql_stateme nt := | |
895 | ' CREATE TAB LE ppsnepl .epl_packa ge_sizes( | |
896 | epl_id NUMBER (30, 0) N OT NULL EN ABLE | |
897 | ,package_s ize NUMBER (13, 6) N OT NULL EN ABLE | |
898 | ,ndf_packa ge_size_ie n NUMBER (30, 0) | |
899 | ,created_b y VARCHA R2 (50 BYT E) NOT NUL L ENABLE | |
900 | ,created_d tm TIMEST AMP (6) NO T NULL ENA BLE | |
901 | ,last_modi fied_by VARCHA R2 (50 BYT E) | |
902 | ,last_modi fied_dtm TIMEST AMP (6)) | |
903 | TABLESPACE ppsnepl_d ata'; | |
904 | ||
905 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
906 | ||
907 | vc_s ql_stateme nt := | |
908 | ' CREATE UNI QUE INDEX ppsnepl.ep l_package_ sizes_pki ON ppsnepl .epl_packa ge_sizes ( epl_id) TA BLESPACE p psnepl_dat a'; | |
909 | ||
910 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
911 | ||
912 | vc_s ql_stateme nt := | |
913 | ' 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)'; | |
914 | ||
915 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
916 | ||
917 | vc_s ql_stateme nt := | |
918 | ' 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 '; | |
919 | ||
920 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
921 | ||
922 | vc_s ql_stateme nt := | |
923 | ' GRANT SELE CT ON ppsn epl.epl_pa ckage_size s TO PPSNE PL_READ_ON LY_ROLE'; | |
924 | ||
925 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
926 | ||
927 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: ndc_p rocess_1') ; | |
928 | RETU RN (0); | |
929 | EXCEPTI ON | |
930 | WHEN OTHERS | |
931 | THEN | |
932 | v n_code := SQLCODE; | |
933 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
934 | s ys.DBMS_OU TPUT.put_l ine ( | |
935 | 'ndc_pro cess_1: Er ror code ' || vn_cod e || ': ' || vc_errm ); | |
936 | s ys.DBMS_OU TPUT.put_l ine ( | |
937 | 'ndc_pro cess_1: la st value f or vc_sql_ statement '); | |
938 | s ys.DBMS_OU TPUT.put_l ine ('ndc_ process_1: ' || vc_s ql_stateme nt); | |
939 | R ETURN (1); | |
940 | END ndc _process_1 ; | |
941 | ||
942 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
943 | -- NAME : ndc_proces s_2 | |
944 | -- TYPE : Function | |
945 | -- DESC RIPTSION: Process nd cs and pac kage size | |
946 | -- INPU TS: None | |
947 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
948 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
949 | FUNCTIO N ndc_proc ess_2 | |
950 | RETU RN NUMBER | |
951 | IS | |
952 | BEGIN | |
953 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
954 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ndc _process_2 '); | |
955 | ||
956 | vc_s ql_stateme nt := | |
957 | ' UPDATE pps nepl.epl_n dcs SET pa ckage_size = NULL WH ERE 1 = 1' ; | |
958 | ||
959 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
960 | ||
961 | vc_s ql_stateme nt := | |
962 | ' ALTER TABL E ppsnepl. epl_ndcs M ODIFY pack age_size N UMBER (30, 0)'; | |
963 | ||
964 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
965 | ||
966 | vc_s ql_stateme nt := | |
967 | ' ALTER TABL E ppsnepl. epl_ndcs R ENAME COLU MN package _size TO p ackage_siz e_id_fk'; | |
968 | ||
969 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
970 | ||
971 | vc_s ql_stateme nt := | |
972 | ' UPDATE pps nepl.epl_n dcs o | |
973 | SET o.p ackage_siz e_id_fk = | |
974 | (SELECT MI N (epl_pac kage_sizes .epl_id) | |
975 | FROM pp snepl.epl_ package_si zes, ppsne pl.temp_ep l_ndcs | |
976 | WHERE epl_pack age_sizes. package_si ze = | |
977 | temp_ epl_ndcs.p ackage_siz e | |
978 | AN D temp_epl _ndcs.epl_ id = o.epl _id) | |
979 | WHERE o.e pl_id IN ( SELECT epl _id FROM p psnepl.tem p_epl_ndcs )'; | |
980 | ||
981 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
982 | ||
983 | sys. DBMS_OUTPU T.put_line ( | |
984 | 'INFORMA TION - N umber of r ow(s) upda ted in pps nepl.epl_n dcs : ' | |
985 | | | TO_CHAR (SQL%ROWCO UNT)); | |
986 | ||
987 | vc_s ql_stateme nt := | |
988 | ' CREATE IND EX ppsnepl .epl_ndc_p ckg_size_f k_idx ON p psnepl.epl _ndcs (pac kage_size_ id_fk)'; | |
989 | ||
990 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
991 | ||
992 | vc_s ql_stateme nt := | |
993 | ' 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) '; | |
994 | ||
995 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
996 | ||
997 | vc_s ql_stateme nt := 'DRO P SEQUENCE ppsnepl.p ackage_siz e_sequence '; | |
998 | ||
999 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
1000 | ||
1001 | COMM IT; | |
1002 | ||
1003 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - E ND: ndc_p rocess_2') ; | |
1004 | RETU RN (0); | |
1005 | EXCEPTI ON | |
1006 | WHEN OTHERS | |
1007 | THEN | |
1008 | v n_code := SQLCODE; | |
1009 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1010 | s ys.DBMS_OU TPUT.put_l ine ( | |
1011 | 'ndc_pro cess_2: Er ror code ' || vn_cod e || ': ' || vc_errm ); | |
1012 | s ys.DBMS_OU TPUT.put_l ine ( | |
1013 | 'ndc_pro cess_2: la st value f or vc_sql_ statement '); | |
1014 | s ys.DBMS_OU TPUT.put_l ine ('ndc_ process_3: ' || vc_s ql_stateme nt); | |
1015 | R ETURN (1); | |
1016 | END ndc _process_2 ; | |
1017 | ||
1018 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1019 | -- NAME : | |
1020 | -- TYPE : Function | |
1021 | -- DESC RIPTSION: Insert dat a into epl _national_ settings t able | |
1022 | -- INPU TS: None | |
1023 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1024 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1025 | ||
1026 | FUNCTI ON insert_ EPL_NAT_SE T_RX | |
1027 | RET URN NUMBER | |
1028 | IS | |
1029 | max _id_epl_1 NUMBER; | |
1030 | max _id_epl_2 NUMBER; | |
1031 | BEGIN | |
1032 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1033 | sys. DBMS_OUTPU T.put_line ('INFORMA TION - S TART: ins ert_EPL_NA T_SET_RX') ; | |
1034 | ||
1035 | SELE CT NVL(MAX (ID), 0) + 1 INTO ma x_id_epl_1 FROM PPSN EPL.EPL_NA TIONAL_SET TINGS; | |
1036 | SELE CT NVL(MAX (ID), 0) + 2 INTO ma x_id_epl_2 FROM PPSN EPL.EPL_NA TIONAL_SET TINGS; | |
1037 | ||
1038 | vc_s ql_stateme nt := | |
1039 | ' INSERT INT O PPSNEPL. EPL_NATION AL_SETTING S (ID, KEY _NAME, CRE ATED_BY, C REATED_DTM ) ' | |
1040 | || 'VALUES ('||max_i d_epl_1||' ,''RXNORM_ UPDATE_RUN _STATE'',' 'DEVELOPER '',SYSDATE )'; | |
1041 | ||
1042 | EXEC UTE IMMEDI ATE vc_sql _statement ; | |
1043 | ||
1044 | FUNCTIO N insert_e pl_nationa l_settings | |
1045 | RETU RN NUMBER | |
1046 | IS | |
1047 | BEGIN | |
1048 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1049 | sys. DBMS_OUTPU T.put_line ( | |
1050 | ' INFORMATIO N - STAR T: insert _epl_natio nal_settin gs'); | |
1051 | ||
1052 | EXEC UTE IMMEDI ATE | |
1053 | ' 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)'; | |
1054 | ||
1055 | EXEC UTE IMMEDI ATE | |
1056 | ' 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)'; | |
1057 | ||
1058 | EXEC UTE IMMEDI ATE | |
1059 | ' 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'') )'; | |
1060 | ||
1061 | EXEC UTE IMMEDI ATE | |
1062 | ' 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''))'; | |
1063 | ||
1064 | EXEC UTE IMMEDI ATE | |
1065 | ' 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' '))'; | |
1066 | ||
1067 | EXEC UTE IMMEDI ATE | |
1068 | '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''))' ; | |
1069 | ||
1070 | EXEC UTE IMMEDI ATE | |
1071 | ' 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''))'; | |
1072 | ||
1073 | EXEC UTE IMMEDI ATE | |
1074 | ' 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''))'; | |
1075 | ||
1076 | EXEC UTE IMMEDI ATE | |
1077 | ' 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'') )'; | |
1078 | ||
1079 | EXEC UTE IMMEDI ATE | |
1080 | ' 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'') )'; | |
1081 | ||
1082 | EXEC UTE IMMEDI ATE | |
1083 | ' 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 ''))'; | |
1084 | ||
1085 | EXEC UTE IMMEDI ATE | |
1086 | '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''))' ; | |
1087 | ||
1088 | EXEC UTE IMMEDI ATE | |
1089 | ' 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''))' ; | |
1090 | ||
1091 | EXEC UTE IMMEDI ATE | |
1092 | ' 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''))' ; | |
1093 | ||
1094 | EXEC UTE IMMEDI ATE | |
1095 | ' 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'' ))'; | |
1096 | ||
1097 | EXEC UTE IMMEDI ATE | |
1098 | ' 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'' ))'; | |
1099 | ||
1100 | EXEC UTE IMMEDI ATE | |
1101 | ' 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)' ; | |
1102 | ||
1103 | EXEC UTE IMMEDI ATE | |
1104 | ' 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)'; | |
1105 | ||
1106 | EXEC UTE IMMEDI ATE | |
1107 | ' 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)'; | |
1108 | ||
1109 | EXEC UTE IMMEDI ATE | |
1110 | ' 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)'; | |
1111 | ||
1112 | EXEC UTE IMMEDI ATE | |
1113 | ' 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)'; | |
1114 | ||
1115 | EXEC UTE IMMEDI ATE | |
1116 | ' 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)'; | |
1117 | ||
1118 | EXEC UTE IMMEDI ATE | |
1119 | ' 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)' ; | |
1120 | ||
1121 | EXEC UTE IMMEDI ATE | |
1122 | ' 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)'; | |
1123 | ||
1124 | EXEC UTE IMMEDI ATE | |
1125 | ' 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)' ; | |
1126 | ||
1127 | EXEC UTE IMMEDI ATE | |
1128 | ' 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)'; | |
1129 | ||
1130 | EXEC UTE IMMEDI ATE | |
1131 | ' 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)'; | |
1132 | ||
1133 | EXEC UTE IMMEDI ATE | |
1134 | ' 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)'; | |
1135 | ||
1136 | EXEC UTE IMMEDI ATE | |
1137 | ' 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)'; | |
1138 | ||
1139 | EXEC UTE IMMEDI ATE | |
1140 | ' 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) '; | |
1141 | ||
1142 | EXEC UTE IMMEDI ATE | |
1143 | ' 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)'; | |
1144 | ||
1145 | EXEC UTE IMMEDI ATE | |
1146 | ' 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)'; | |
1147 | ||
1148 | EXEC UTE IMMEDI ATE | |
1149 | ' 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)'; | |
1150 | ||
1151 | EXEC UTE IMMEDI ATE | |
1152 | ' 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)'; | |
1153 | ||
1154 | EXEC UTE IMMEDI ATE | |
1155 | ' 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) '; | |
1156 | ||
1157 | EXEC UTE IMMEDI ATE | |
1158 | ' 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)'; | |
1159 | ||
1160 | EXEC UTE IMMEDI ATE | |
1161 | ' 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)'; | |
1162 | ||
1163 | EXEC UTE IMMEDI ATE | |
1164 | ' 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)'; | |
1165 | ||
1166 | EXEC UTE IMMEDI ATE | |
1167 | ' 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)'; | |
1168 | ||
1169 | EXEC UTE IMMEDI ATE | |
1170 | ' 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)'; | |
1171 | ||
1172 | EXEC UTE IMMEDI ATE | |
1173 | ' 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) '; | |
1174 | ||
1175 | EXEC UTE IMMEDI ATE | |
1176 | ' 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)' ; | |
1177 | ||
1178 | EXEC UTE IMMEDI ATE | |
1179 | ' 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)'; | |
1180 | ||
1181 | EXEC UTE IMMEDI ATE | |
1182 | ' 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)'; | |
1183 | ||
1184 | EXEC UTE IMMEDI ATE | |
1185 | ' 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)'; | |
1186 | ||
1187 | EXEC UTE IMMEDI ATE | |
1188 | ' 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 )'; | |
1189 | ||
1190 | EXEC UTE IMMEDI ATE | |
1191 | ' 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)'; | |
1192 | ||
1193 | COMM IT; | |
1194 | ||
1195 | EXEC UTE IMMEDI ATE 'SELEC T COUNT (* ) FROM pps nepl.epl_n ational_se ttings' | |
1196 | I NTO vn_row _count; | |
1197 | ||
1198 | sys. DBMS_OUTPU T.put_line ( | |
1199 | 'INFORMA TION - N umber of r ow(s) in p psnepl.epl _national_ settings : ' | |
1200 | | | TO_CHAR (vn_row_co unt)); | |
1201 | ||
1202 | sys. DBMS_OUTPU T.put_line ( | |
1203 | ' INFORMATIO N - END: insert_e pl_nationa l_settings '); | |
1204 | ||
1205 | IF v n_row_coun t = 0 | |
1206 | THEN | |
1207 | R ETURN (1); | |
1208 | END IF; | |
1209 | ||
1210 | RETU RN (0); | |
1211 | EXCEPTI ON | |
1212 | WHEN OTHERS | |
1213 | THEN | |
1214 | v n_code := SQLCODE; | |
1215 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1216 | s ys.DBMS_OU TPUT.put_l ine ( | |
1217 | 'inse rt_epl_nat ional_sett ings: Erro r code ' | |
1218 | || vn_co de | |
1219 | || ': ' | |
1220 | || vc_er rm); | |
1221 | R ETURN (1); | |
1222 | END ins ert_epl_na tional_set tings; | |
1223 | ||
1224 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1225 | -- NAME : update_nat ional_sett ing | |
1226 | -- TYPE : Function | |
1227 | -- DESC RIPTSION: Update the epl_natio nal_settin gs table | |
1228 | -- INPU TS: None | |
1229 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1230 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1231 | FUNCTIO N update_n ational_se tting | |
1232 | RETU RN NUMBER | |
1233 | IS | |
1234 | BEGIN | |
1235 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1236 | sys. DBMS_OUTPU T.put_line ( | |
1237 | ' INFORMATIO N - STAR T: update _national_ setting'); | |
1238 | ||
1239 | EXEC UTE IMMEDI ATE | |
1240 | ' 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'; | |
1241 | ||
1242 | EXEC UTE IMMEDI ATE | |
1243 | ' 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'; | |
1244 | ||
1245 | EXEC UTE IMMEDI ATE | |
1246 | ' 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'; | |
1247 | ||
1248 | EXEC UTE IMMEDI ATE | |
1249 | ' 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'; | |
1250 | ||
1251 | EXEC UTE IMMEDI ATE | |
1252 | ' 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'; | |
1253 | ||
1254 | EXEC UTE IMMEDI ATE | |
1255 | ' 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 '; | |
1256 | ||
1257 | EXEC UTE IMMEDI ATE | |
1258 | ' 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'; | |
1259 | ||
1260 | EXEC UTE IMMEDI ATE | |
1261 | ' 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'; | |
1262 | ||
1263 | EXEC UTE IMMEDI ATE | |
1264 | ' 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'; | |
1265 | ||
1266 | EXEC UTE IMMEDI ATE | |
1267 | ' 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' ; | |
1268 | ||
1269 | EXEC UTE IMMEDI ATE | |
1270 | ' 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'; | |
1271 | ||
1272 | EXEC UTE IMMEDI ATE | |
1273 | ' 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'; | |
1274 | ||
1275 | EXEC UTE IMMEDI ATE | |
1276 | ' 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'; | |
1277 | ||
1278 | EXEC UTE IMMEDI ATE | |
1279 | ' 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'; | |
1280 | ||
1281 | EXEC UTE IMMEDI ATE | |
1282 | ' 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'; | |
1283 | ||
1284 | EXEC UTE IMMEDI ATE | |
1285 | ' 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'; | |
1286 | ||
1287 | EXEC UTE IMMEDI ATE | |
1288 | ' 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 '; | |
1289 | ||
1290 | EXEC UTE IMMEDI ATE | |
1291 | ' 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'; | |
1292 | ||
1293 | EXEC UTE IMMEDI ATE | |
1294 | ' 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'; | |
1295 | ||
1296 | EXEC UTE IMMEDI ATE | |
1297 | ' 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'; | |
1298 | ||
1299 | EXEC UTE IMMEDI ATE | |
1300 | ' 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'; | |
1301 | ||
1302 | EXEC UTE IMMEDI ATE | |
1303 | ' 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'; | |
1304 | ||
1305 | EXEC UTE IMMEDI ATE | |
1306 | ' 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'; | |
1307 | ||
1308 | EXEC UTE IMMEDI ATE | |
1309 | ' 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'; | |
1310 | ||
1311 | COMM IT; | |
1312 | ||
1313 | sys. DBMS_OUTPU T.put_line ( | |
1314 | ' INFORMATIO N - END: update_n ational_se tting'); | |
1315 | ||
1316 | IF v n_row_coun t = 0 | |
1317 | THEN | |
1318 | R ETURN (1); | |
1319 | END IF; | |
1320 | ||
1321 | RETU RN (0); | |
1322 | EXCEPTI ON | |
1323 | WHEN OTHERS | |
1324 | THEN | |
1325 | v n_code := SQLCODE; | |
1326 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1327 | s ys.DBMS_OU TPUT.put_l ine ( | |
1328 | 'upda te_nationa l_setting: Error cod e ' | |
1329 | || vn_co de | |
1330 | || ': ' | |
1331 | || vc_er rm); | |
1332 | R ETURN (1); | |
1333 | END upd ate_nation al_setting ; | |
1334 | ||
1335 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1336 | -- NAME : turn_vista _messaging _off | |
1337 | -- TYPE : Function | |
1338 | -- DESC RIPTSION: Update epl _national_ setting ta ble to tur n vist_mes saging_off | |
1339 | -- INPU TS: None | |
1340 | -- OUTP UTS: Number: (0 - good: > 0 - error) | |
1341 | ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- | |
1342 | FUNCTIO N turn_vis ta_messagi ng_off | |
1343 | RETU RN NUMBER | |
1344 | IS | |
1345 | BEGIN | |
1346 | sys. DBMS_OUTPU T.put_line (CHR (10) ); | |
1347 | sys. DBMS_OUTPU T.put_line ( | |
1348 | ' INFORMATIO N - STAR T: turn_v ista_messa ging_off') ; | |
1349 | ||
1350 | EXEC UTE IMMEDI ATE | |
1351 | ' update pps nepl.epl_n ational_se ttings set boolean_v alue = 0 w here id = 14'; | |
1352 | ||
1353 | EXEC UTE IMMEDI ATE | |
1354 | ' update pps nepl.epl_n ational_se ttings set string_va lue = null where id = 15'; | |
1355 | ||
1356 | EXEC UTE IMMEDI ATE | |
1357 | ' update pps nepl.epl_n ational_se ttings set integer_v alue = 0 w here id = 16'; | |
1358 | ||
1359 | COMM IT; | |
1360 | ||
1361 | sys. DBMS_OUTPU T.put_line ( | |
1362 | ' INFORMATIO N - END: turn_vis ta_messagi ng_off'); | |
1363 | ||
1364 | IF v n_row_coun t = 0 | |
1365 | THEN | |
1366 | R ETURN (1); | |
1367 | END IF; | |
1368 | ||
1369 | RETU RN (0); | |
1370 | EXCEPTI ON | |
1371 | WHEN OTHERS | |
1372 | THEN | |
1373 | v n_code := SQLCODE; | |
1374 | v c_errm := SUBSTR (SQ LERRM, 1, 64); | |
1375 | s ys.DBMS_OU TPUT.put_l ine ( | |
1376 | 'turn _vista_mes saging_off : Error co de ' | |
1377 | || vn_co de | |
1378 | || ': ' | |
1379 | || vc_er rm); | |
1380 | R ETURN (1); | |
1381 | END tur n_vista_me ssaging_of f; | |
1382 | ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- | |
1383 | -- MAIN SE CTION | |
1384 | ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- | |
1385 | BEGIN | |
1386 | sys.DBM S_OUTPUT.p ut_line (C HR (10)); | |
1387 | sys.DBM S_OUTPUT.p ut_line ( | |
1388 | 'INF ORMATION - Checkin g to see i f PPS-N 2. 0 table(s) already e xists.'); | |
1389 | ||
1390 | vn_cont inue := ch eck_for_pp sn2; | |
1391 | ||
1392 | IF vn_c ontinue > 0 | |
1393 | THEN | |
1394 | sys. DBMS_OUTPU T.put_line ( | |
1395 | ' ERROR - ONE OR MORE PPS- N 2.0 OBJE CTS ALREAD Y EXISTS. STOPPING M IGRATION.' ); | |
1396 | sys. DBMS_OUTPU T.put_line ( | |
1397 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1398 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1399 | RETU RN; | |
1400 | END IF; | |
1401 | ||
1402 | vn_cont inue := cr eate_ppsn_ tables; | |
1403 | ||
1404 | IF vn_c ontinue > 0 | |
1405 | THEN | |
1406 | sys. DBMS_OUTPU T.put_line ( | |
1407 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1408 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1409 | RETU RN; | |
1410 | END IF; | |
1411 | ||
1412 | vn_cont inue := cr eate_ppsn_ views; | |
1413 | ||
1414 | IF vn_c ontinue > 0 | |
1415 | THEN | |
1416 | sys. DBMS_OUTPU T.put_line ( | |
1417 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1418 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1419 | RETU RN; | |
1420 | END IF; | |
1421 | ||
1422 | vn_cont inue := in sert_ppsn2 _data; | |
1423 | ||
1424 | IF vn_c ontinue > 0 | |
1425 | THEN | |
1426 | sys. DBMS_OUTPU T.put_line ( | |
1427 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1428 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1429 | RETU RN; | |
1430 | END IF; | |
1431 | ||
1432 | vn_cont inue := nd c_process_ 1; | |
1433 | ||
1434 | IF vn_c ontinue > 0 | |
1435 | THEN | |
1436 | sys. DBMS_OUTPU T.put_line ( | |
1437 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1438 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1439 | RETU RN; | |
1440 | END IF; | |
1441 | ||
1442 | vn_cont inue := nd c_process_ 2; | |
1443 | ||
1444 | IF vn_c ontinue > 0 | |
1445 | THEN | |
1446 | sys. DBMS_OUTPU T.put_line ( | |
1447 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1448 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1449 | RETU RN; | |
1450 | END IF; | |
1451 | ||
1452 | vn_cont inue := in sert_epl_n ational_se ttings; | |
1453 | ||
1454 | IF vn_c ontinue > 0 | |
1455 | THEN | |
1456 | sys. DBMS_OUTPU T.put_line ( | |
1457 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1458 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1459 | RETU RN; | |
1460 | END IF; | |
1461 | ||
1462 | vn_cont inue := up date_natio nal_settin g; | |
1463 | ||
1464 | IF vn_c ontinue > 0 | |
1465 | THEN | |
1466 | sys. DBMS_OUTPU T.put_line ( | |
1467 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1468 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1469 | RETU RN; | |
1470 | END IF; | |
1471 | ||
1472 | vn_cont inue := tu rn_vista_m essaging_o ff; | |
1473 | ||
1474 | IF vn_c ontinue > 0 | |
1475 | THEN | |
1476 | sys. DBMS_OUTPU T.put_line ( | |
1477 | ' ERROR - EXECUTE PPS-N 2.0 ROLLBACK P ROCESS'); | |
1478 | sys. DBMS_OUTPU T.put_line ('ERROR - EXITI NG SCRIPT. ..'); | |
1479 | RETU RN; | |
1480 | END IF; | |
1481 | ||
1482 | -- Reco mpile Sche ma and Gat her Statis tics | |
1483 | DBMS_UT ILITY.comp ile_schema (schema = > 'PPSNEPL '); | |
1484 | DBMS_ST ATS.gather _schema_st ats ('PPSN EPL'); | |
1485 | EXCEPTION | |
1486 | WHEN OT HERS | |
1487 | THEN | |
1488 | vn_c ode := SQL CODE; | |
1489 | vc_e rrm := SUB STR (SQLER RM, 1, 64) ; | |
1490 | sys. DBMS_OUTPU T.put_line ( | |
1491 | ' MAIN: Erro r code ' | | vn_code || ': ' || vc_errm); | |
1492 | END; | |
1493 | / | |
1494 | ||
1495 | SELECT 'Mi gration St opped: ' | | CURRENT_ TIMESTAMP FROM DUAL; | |
1496 | ||
1497 | SPOOL OFF | |
1498 | EXIT |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.