· 8 years ago · Mar 21, 2018, 11:06 AM
1/*
2SQLyog Community v12.5.1 (64 bit)
3MySQL - 10.2.7-MariaDB-10.2.7+maria~xenial-log : Database - elib3devtemp
4*********************************************************************
5*/
6
7/*!40101 SET NAMES utf8 */;
8
9/*!40101 SET SQL_MODE=''*/;
10
11/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
12/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
15CREATE DATABASE /*!32312 IF NOT EXISTS*/`elib3devtemp` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
16
17USE `elib3devtemp`;
18
19/* Function structure for function `CalcValue` */
20
21/*!50003 DROP FUNCTION IF EXISTS `CalcValue` */;
22DELIMITER $$
23
24/*!50003 CREATE FUNCTION `CalcValue`( starting_value INT ) RETURNS int(11)
25 DETERMINISTIC
26BEGIN
27
28 DECLARE total_value INT;
29
30 SET total_value = 0;
31
32 label1: LOOP
33 SET total_value = total_value + starting_value;
34 IF total_value < 850 THEN
35 ITERATE label1;
36 END IF;
37 LEAVE label1;
38 END LOOP label1;
39
40 RETURN total_value;
41
42END */$$
43DELIMITER ;
44
45/* Function structure for function `CG$ERRORS_MsgGetText` */
46
47/*!50003 DROP FUNCTION IF EXISTS `CG$ERRORS_MsgGetText` */;
48DELIMITER $$
49
50/*!50003 CREATE FUNCTION `CG$ERRORS_MsgGetText`( p_MsgNo INT
51 ,p_DfltText VARCHAR(10000)
52 ,p_Subst1 VARCHAR(240)
53 ,p_Subst2 VARCHAR(240)
54 ,p_Subst3 VARCHAR(240)
55 ,p_Subst4 VARCHAR(240)) RETURNS varchar(10000) CHARSET utf8 COLLATE utf8_bin
56 DETERMINISTIC
57BEGIN
58
59 DECLARE l_temp VARCHAR(10000);
60 SET p_Subst4 = IFNULL(p_Subst4, NULL);
61
62 SET l_temp := p_DfltText;
63 SET l_temp := REPLACE(l_temp, '<p>', p_Subst1);
64 SET l_temp := REPLACE(l_temp, '<p1>', p_Subst1);
65 SET l_temp := REPLACE(l_temp, '<p2>', p_Subst2);
66 SET l_temp := REPLACE(l_temp, '<p3>', p_Subst3);
67 SET l_temp := REPLACE(l_temp, '<p4>', p_Subst4);
68
69 RETURN l_temp;
70
71END */$$
72DELIMITER ;
73
74/* Function structure for function `COUNT_FAVORITE_ITEMS` */
75
76/*!50003 DROP FUNCTION IF EXISTS `COUNT_FAVORITE_ITEMS` */;
77DELIMITER $$
78
79/*!50003 CREATE FUNCTION `COUNT_FAVORITE_ITEMS`( p_fav_ids VARCHAR(4000)) RETURNS int(38)
80 DETERMINISTIC
81BEGIN
82 DECLARE v_fav_id_count int(38);
83
84 select (length(p_fav_ids) - length(replace(p_fav_ids,',','')) + 1) into v_fav_id_count;
85
86 return v_fav_id_count;
87
88 /*
89 * For Testing purpose:-
90 * SELECT count_favorite_items('55,22,77') AS RESULT;
91 * SELECT count_favorite_items('68062207,67102215,68059907,68017466,68010600') AS RESULT;
92 * SELECT count_favorite_items('26561730,26521574,24632138,26558994,26559324,27303093,27259806') AS RESULT;
93 */
94
95END */$$
96DELIMITER ;
97
98/* Function structure for function `SEQ_NEXTVAL` */
99
100/*!50003 DROP FUNCTION IF EXISTS `SEQ_NEXTVAL` */;
101DELIMITER $$
102
103/*!50003 CREATE FUNCTION `SEQ_NEXTVAL`(p_seq_id varchar(50)) RETURNS int(38)
104 DETERMINISTIC
105BEGIN
106 DECLARE v_return int(38);
107
108 if lower(p_seq_id) = 'dod_lii_id' then
109 SELECT max(dod_lii_id)+1 INTO v_return from SEQ_VALUES;
110 INSERT INTO SEQ_VALUES (dod_lii_id) VALUES (v_return);
111
112 elseif LOWER(p_seq_id) = 'dod_ori_id' THEN
113 SELECT MAX(dod_ori_id)+1 INTO v_return FROM SEQ_VALUES;
114 INSERT INTO SEQ_VALUES (dod_ori_id) VALUES (v_return);
115
116 end if;
117
118 RETURN v_return;
119
120 /* For Testing Purpose:
121 * SELECT SEQ_NEXTVAL('dod_ori_id');
122 * SELECT SEQ_NEXTVAL('dod_lii_id');
123 */
124
125 END */$$
126DELIMITER ;
127
128/* Function structure for function `SPLIT_STR` */
129
130/*!50003 DROP FUNCTION IF EXISTS `SPLIT_STR` */;
131DELIMITER $$
132
133/*!50003 CREATE FUNCTION `SPLIT_STR`( X VARCHAR(255),
134 delim VARCHAR(12),
135 pos INT ) RETURNS varchar(255) CHARSET utf8 COLLATE utf8_bin
136 DETERMINISTIC
137BEGIN
138
139 RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(X, delim, pos),
140 LENGTH(SUBSTRING_INDEX(X, delim, pos -1)) + 1),
141 delim, '');
142
143END */$$
144DELIMITER ;
145
146/* Function structure for function `CG$ERRORS_LookErrors` */
147
148/*!50003 DROP FUNCTION IF EXISTS `CG$ERRORS_LookErrors` */;
149DELIMITER $$
150
151/*!50003 CREATE FUNCTION `CG$ERRORS_LookErrors`() RETURNS varchar(10000) CHARSET utf8 COLLATE utf8_bin
152 DETERMINISTIC
153BEGIN
154
155 DECLARE maxMessageLength INTEGER;
156 DECLARE agregatedMessage VARCHAR(21845); /* this length was set to 32767 in oracle bt it is too long for MariaDB */
157 DECLARE nextMessage VARCHAR(512);
158
159 SET maxMessageLength := 21845; /* this length was set to 32767 in oracle bt it is too long for MariaDB */
160
161 IF agregatedMessage IS NULL THEN
162 SET agregatedMessage := CONCAT('ERROR-', @cg$err_msgid, '(T', @cg$err_msg_type, ')' , ' : ' , @cg$err_msg , ' on ', @cg$err_loc);
163 ELSE
164 IF ((LENGTH(agregatedMessage)
165 + LENGTH(@cg$err_msg)
166 + LENGTH(@cg$err_msgid) + 16 ) < maxMessageLength ) -- B2401878 : add length check
167 THEN
168 SET agregatedMessage := CONCAT('ERROR-', @cg$err_msgid, '(T', @cg$err_msg_type, ')' , ' : ' , @cg$err_msg , ' on ', @cg$err_loc);
169 END IF;
170 END IF;
171
172 RETURN agregatedMessage;
173
174END */$$
175DELIMITER ;
176
177/* Function structure for function `ELIB_UTILS_GET_1STAUTHOR` */
178
179/*!50003 DROP FUNCTION IF EXISTS `ELIB_UTILS_GET_1STAUTHOR` */;
180DELIMITER $$
181
182/*!50003 CREATE FUNCTION `ELIB_UTILS_GET_1STAUTHOR`(P_TEXT VARCHAR(21844)) RETURNS varchar(21844) CHARSET utf8 COLLATE utf8_bin
183 DETERMINISTIC
184 COMMENT 'Returns first last name of the first author in author string'
185BEGIN
186 /* Returns the first last name part of the first author */
187 /* Smith J -> Smith */
188 /* Smith Adams J -> Smith */
189 /* von Allmen R -> von Allmen */
190
191 DECLARE L_CNT INT;
192
193 /*
194 * utf8 maximum of 21,844 characters
195 */
196
197 DECLARE L_TEXT VARCHAR(21844);
198 DECLARE V_L_TEXT_STR VARCHAR(21844);
199
200 SET P_TEXT = NULLIF(P_TEXT,'');
201 SET L_TEXT = P_TEXT;
202
203 /*
204 * Using LOCATE(substr,str,pos_opt) for Oracle's INSTR()
205 * SUBSTRING(str,pos,len_opt) OR SUBSTR(str,pos,len_opt)
206 * INSTR(str,substr) is not being used in MariaDB
207 */
208
209 IF (LOCATE(',',L_TEXT) > 0) THEN
210 SET V_L_TEXT_STR = SUBSTR(L_TEXT,1,LOCATE(',',L_TEXT) -1);
211 SET L_TEXT = V_L_TEXT_STR;
212 END IF;
213
214 SET L_CNT = 0;
215
216 IF (CHAR_LENGTH(TRIM(L_TEXT)) > 0) THEN
217 SWL_Label:
218 WHILE (LOCATE(' ',L_TEXT,L_CNT+1) > 0) DO
219 SET L_CNT = L_CNT+1;
220 IF (LOWER(SUBSTR(L_TEXT,1,LOCATE(' ',L_TEXT,L_CNT))) <> SUBSTR(L_TEXT,1,LOCATE(' ',L_TEXT,L_CNT))) THEN
221 LEAVE SWL_Label;
222 END IF;
223 END WHILE;
224 END IF;
225
226 IF (L_CNT > 0) THEN
227 SET V_L_TEXT_STR = SUBSTR(L_TEXT,1,LOCATE(' ',L_TEXT,L_CNT)-1);
228 SET L_TEXT = V_L_TEXT_STR;
229 END IF;
230
231 RETURN L_TEXT;
232 /*
233 * For Testing purpose:-
234 * SELECT ELIB_UTILS_GET_1STAUTHOR('Smith J') AS RESULT;
235 * SELECT ELIB_UTILS_GET_1STAUTHOR('Smith Adams J') AS RESULT;
236 * SELECT ELIB_UTILS_GET_1STAUTHOR('von Allmen R') AS RESULT;
237 */
238 END */$$
239DELIMITER ;
240
241/* Function structure for function `ELIB_UTILS_GET_PARAMETER` */
242
243/*!50003 DROP FUNCTION IF EXISTS `ELIB_UTILS_GET_PARAMETER` */;
244DELIMITER $$
245
246/*!50003 CREATE FUNCTION `ELIB_UTILS_GET_PARAMETER`(P_PAR_NAME VARCHAR(50)) RETURNS varchar(4000) CHARSET utf8 COLLATE utf8_bin
247 DETERMINISTIC
248 COMMENT 'Returns ELIB parameter from table PARAMETER'
249Begin
250 DECLARE L_RETURN VARCHAR(4000);
251
252 DECLARE EXIT HANDLER FOR NOT FOUND
253 begin
254 SIGNAL SQLSTATE '02000' SET
255 MESSAGE_TEXT = 'ERROR 1329(ER_SP_FETCH_NO_DATA): No data found- zero rows fetched, selected, or processed';
256 end;
257
258 select PAR_VALUE
259 into L_RETURN
260 from PARAMETER
261 where upper(PAR_NAME) = upper(P_PAR_NAME);
262
263 return L_RETURN;
264 /*
265 * For Testing purpose
266 * SELECT ELIB_UTILS_GET_PARAMETER('ACTPUB_URL');
267 */
268 END */$$
269DELIMITER ;
270
271/* Function structure for function `ELIB_UTILS_GET_WORD` */
272
273/*!50003 DROP FUNCTION IF EXISTS `ELIB_UTILS_GET_WORD` */;
274DELIMITER $$
275
276/*!50003 CREATE FUNCTION `ELIB_UTILS_GET_WORD`(P_TEXT VARCHAR(21844), P_POS INT(38)) RETURNS varchar(21844) CHARSET utf8 COLLATE utf8_bin
277 DETERMINISTIC
278 COMMENT 'Returns the nth word of a string, or the -nth word from the end'
279BEGIN
280
281 DECLARE L_TEXT VARCHAR(21844);
282 DECLARE V_L_TEXT_STR VARCHAR(21844);
283 /*
284 * utf8 maximum of 21,844 characters
285 */
286 SET P_TEXT = NULLIF(P_TEXT,'');
287 SET L_TEXT = LOWER(P_TEXT);
288 SET L_TEXT = REPLACE(L_TEXT,',',' ');
289 SET L_TEXT = REPLACE(L_TEXT,';',' ');
290 SET L_TEXT = REPLACE(L_TEXT,'.',' ');
291 SET L_TEXT = REPLACE(L_TEXT,':',' ');
292 SET L_TEXT = REPLACE(L_TEXT,' ',' ');
293 SET L_TEXT = TRIM(L_TEXT);
294
295 /*
296 * Using LOCATE(substr,str,pos_opt) for Oracle's INSTR()
297 * SUBSTRING(str,pos,len_opt) OR SUBSTR(str,pos,len_opt)
298 * INSTR(str,substr) is not being used in MariaDB
299 */
300
301 IF (P_POS >= 1) THEN
302 SET V_L_TEXT_STR = REPLACE(SUBSTRING(
303 SUBSTRING_INDEX(L_TEXT, ' ', P_POS),
304 LENGTH(SUBSTRING_INDEX(L_TEXT, ' ', P_POS -1)) + 1)
305 ,' ', '');
306 SET L_TEXT = V_L_TEXT_STR;
307
308 ELSEIF(P_POS <= -1) THEN
309 SET V_L_TEXT_STR = REPLACE(SUBSTRING(
310 SUBSTRING_INDEX(L_TEXT, ' ', P_POS),
311 1,
312 LENGTH(SUBSTRING_INDEX(L_TEXT, ' ', P_POS))-
313 LENGTH(SUBSTRING_INDEX(L_TEXT, ' ', P_POS+1)))
314 ,' ','');
315 SET L_TEXT = V_L_TEXT_STR;
316 ELSE
317 SET L_TEXT = '';
318 END IF;
319
320 RETURN L_TEXT;
321 /*
322 * For Testing purpose:-
323 * SELECT ELIB_UTILS_GET_WORD('Infants with single ventricle: a population-based epidemiological study.',2) AS RESULT;
324 * SELECT ELIB_UTILS_GET_WORD('Infants with single ventricle: a population-based epidemiological study.',-5) AS RESULT;
325 */
326 END */$$
327DELIMITER ;
328
329/* Function structure for function `GET_NEW_PMIDS_FOR_ALERT` */
330
331/*!50003 DROP FUNCTION IF EXISTS `GET_NEW_PMIDS_FOR_ALERT` */;
332DELIMITER $$
333
334/*!50003 CREATE FUNCTION `GET_NEW_PMIDS_FOR_ALERT`(P_SAVED_SEARCH_ID INT(38)) RETURNS longtext CHARSET utf8 COLLATE utf8_bin
335 DETERMINISTIC
336BEGIN
337
338 DECLARE V_NEW_PMIDS LONGTEXT;
339 DECLARE V_LAST_ALERT_TERM VARCHAR(4000);
340 DECLARE V_SEARCH_TERM VARCHAR(21000);
341 DECLARE V_CURRENT_INSTANCE DECIMAL(12,0);
342 DECLARE NO_DATA INT DEFAULT 0;
343 DECLARE CUR_PMID BIGINT;
344
345 DECLARE CURSOR_VAR1 CURSOR FOR
346 SELECT PMID FROM(
347 SELECT PMID
348 FROM ALERT_PMID
349 WHERE (SAVED_SEARCH_ID = P_SAVED_SEARCH_ID AND RESULT_SET = 2)
350 AND(PMID) NOT IN(
351 SELECT PMID
352 FROM ALERT_PMID
353 WHERE SAVED_SEARCH_ID = P_SAVED_SEARCH_ID AND RESULT_SET = 1)
354 ) AS TDATA;
355
356 DECLARE CURSOR_VAR2 CURSOR FOR
357 SELECT PMID FROM(
358 SELECT PMID
359 FROM ALERT_PMID
360 WHERE (SAVED_SEARCH_ID = P_SAVED_SEARCH_ID AND RESULT_SET = 1)
361 AND(PMID) NOT IN(
362 SELECT PMID
363 FROM ALERT_PMID
364 WHERE SAVED_SEARCH_ID = P_SAVED_SEARCH_ID AND RESULT_SET = 2)
365 ) AS TDATA;
366
367 DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;
368
369 SELECT TRIM(LAST_ALERT_QUERY), TRIM(SEARCH_TERM), CURRENT_INSTANCE
370 INTO V_LAST_ALERT_TERM,V_SEARCH_TERM,V_CURRENT_INSTANCE
371 FROM SAVED_SEARCH
372 WHERE SAVED_SEARCH_ID = P_SAVED_SEARCH_ID;
373
374 SET V_NEW_PMIDS = '';
375
376 OPEN CURSOR_VAR1;
377 SET NO_DATA = 0;
378 FETCH CURSOR_VAR1 INTO CUR_PMID;
379
380 WHILE
381 NO_DATA = 0 DO
382
383 IF LENGTH(V_NEW_PMIDS) > 0 THEN
384 SET V_NEW_PMIDS = CONCAT(COALESCE(V_NEW_PMIDS,''),',',CUR_PMID);
385 ELSE
386 SET V_NEW_PMIDS = CUR_PMID;
387 END IF;
388
389 SET NO_DATA = 0;
390 FETCH CURSOR_VAR1 INTO CUR_PMID;
391
392 END WHILE;
393
394 SET NO_DATA = 0;
395 CLOSE CURSOR_VAR1;
396
397 SET V_NEW_PMIDS = CONCAT(COALESCE(V_NEW_PMIDS,''),'~');
398
399 IF V_LAST_ALERT_TERM = V_SEARCH_TERM THEN
400
401 OPEN CURSOR_VAR2;
402 SET NO_DATA = 0;
403 FETCH CURSOR_VAR2 INTO CUR_PMID;
404
405 WHILE
406 NO_DATA = 0 DO
407
408 IF LENGTH(V_NEW_PMIDS) > 0 THEN
409 SET V_NEW_PMIDS = CONCAT(COALESCE(V_NEW_PMIDS,''),',',CUR_PMID);
410 ELSE
411 SET V_NEW_PMIDS = CUR_PMID;
412 END IF;
413
414 SET NO_DATA = 0;
415 FETCH CURSOR_VAR2 INTO CUR_PMID;
416
417 END WHILE;
418
419 SET NO_DATA = 0;
420 CLOSE CURSOR_VAR2;
421
422 END IF;
423
424 RETURN V_NEW_PMIDS;
425
426 /*
427 * For Testing purpose:-
428 * SELECT GET_NEW_PMIDS_FOR_ALERT(1);
429 * SELECT GET_NEW_PMIDS_FOR_ALERT(703);
430 * SELECT GET_NEW_PMIDS_FOR_ALERT(722);
431 */
432
433END */$$
434DELIMITER ;
435
436/* Procedure structure for procedure `CG$DOCUMENTDATA_insert_jn` */
437
438/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_insert_jn` */;
439
440DELIMITER $$
441
442/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_insert_jn`(pDOD_ID DECIMAL(38,0), pOPERATION CHAR(3))
443BEGIN
444
445 /*
446 * MariaDB does not have DEFAULT variable. So IFNULL is used
447 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
448 */
449 DECLARE vjn_notes VARCHAR(240);
450
451 -- any OTHER error
452 DECLARE EXIT HANDLER FOR SQLEXCEPTION
453 BEGIN
454 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
455 CALL CG$ERRORS_push(@text,
456 'E',
457 'API',
458 @errno,
459 'cg$DOCUMENTDATA_insert_jn');
460
461 CALL CG$ERRORS_raise_failure;
462
463 END;
464
465 SET pOPERATION = IFNULL(pOPERATION, 'INS');
466 /*
467 * jnNotes is a DOCUMENTDATA_JN column which is not used and not passed from any other procedure. so it is set to NULL.
468 * if it is not Set, MariaDB gives out an error
469 */
470
471 SET vjn_notes = IFNULL(vjn_notes, NULL);
472 IF (pOPERATION = 'INS') THEN
473
474 INSERT INTO DOCUMENTDATA_JN(
475 JN_OPERATION
476 ,JN_ORACLE_USER
477 ,JN_DATETIME
478 ,JN_NOTES
479 ,JN_APPLN
480 ,JN_SESSION
481 ,DOD_ID
482 ,DOD_ORI_ID
483 ,DOD_LII_ID
484 ,DOD_DOCTYPE
485 ,DOD_TITLE
486 ,DOD_JOURNAL_NAME
487 ,DOD_PUBL_YEAR
488 ,DOD_AUTHORS
489 ,DOD_VOLUME
490 ,DOD_ISSUE
491 ,DOD_PAGEINFO
492 ,DOD_LANGUAGE
493 ,DOD_KEYWORDS
494 ,DOD_AFFILIATION
495 ,DOD_ABSTRACT
496 ,DOD_PMID
497 ,DOD_DOI
498 ,DOD_ISSN
499 ,DOD_MESH_TERMS
500 ,DOD_SUBSTANCES
501 ,DOD_PUB_TYPE
502 ,DOD_EDITOR_NAME
503 ,DOD_BOOKTITLE
504 ,DOD_PUBLISHER
505 ,DOD_ISBN
506 ,DOD_CONF_NAME
507 ,DOD_SPONSOR
508 ,DOD_PAT_APPL_DATE
509 ,DOD_PAT_APPL_NUM
510 ,DOD_PAT_PRIO_DATES
511 ,DOD_PAT_PRIO_NUM
512 ,DOD_PAT_PUBL_DATE
513 ,DOD_PAT_PUBL_NUM
514 ,DOD_URL
515 ,DOD_PRODUCT
516 ,DOD_INDICATION
517 ,DOD_KEY_PUBLICATION
518 ,DOD_CLINICAL_ARTICLE
519 ,DOD_COMMENT
520 ,DOD_INSERTEDBY
521 ,DOD_UPDATEDBY
522 ,DOD_CREATED
523 ,DOD_CREATEDBY
524 ,DOD_MODIFIED
525 ,DOD_MODIFIEDBY
526 ,DOD_ORI_DUPE_OF_ORI_ID
527 ,DOD_ORI_ORDERID
528 ,DOD_ORI_PDF_DELIVERY_DATE
529 ,DOD_ORI_STATUS
530 ,DOD_ORI_COMMENT
531 ,DOD_ORI_URGENCY
532 ,DOD_ORI_MAILBODY
533 ,DOD_ORI_ACTPUBMED
534 ,DOD_ORI_CREATEDBY
535 ,DOD_ORI_INSERTEDBY
536 ,DOD_ORI_UPDATEDBY
537 ,DOD_ORI_MODIFIEDBY
538 ,DOD_ORI_CREATED
539 ,DOD_ORI_MODIFIED
540 ,DOD_LII_HIDDEN
541 ,DOD_LII_COMMENT
542 ,DOD_LII_CREATEDBY
543 ,DOD_LII_MODIFIEDBY
544 ,DOD_LII_INSERTEDBY
545 ,DOD_LII_UPDATEDBY
546 ,DOD_LII_CREATED
547 ,DOD_LII_MODIFIED)
548 VALUES(
549 pOPERATION
550 ,USER()
551 ,SYSDATE()
552 ,vjn_notes
553 ,CONCAT('CG$DOCUMENTDATA.',pOPERATION)
554 ,CONNECTION_ID()
555 ,@vitDOD_ID
556 ,@vitDOD_ORI_ID
557 ,@vitDOD_LII_ID
558 ,@vitDOD_DOCTYPE
559 ,@vitDOD_TITLE
560 ,@vitDOD_JOURNAL_NAME
561 ,@vitDOD_PUBL_YEAR
562 ,@vitDOD_AUTHORS
563 ,@vitDOD_VOLUME
564 ,@vitDOD_ISSUE
565 ,@vitDOD_PAGEINFO
566 ,@vitDOD_LANGUAGE
567 ,@vitDOD_KEYWORDS
568 ,@vitDOD_AFFILIATION
569 ,@vitDOD_ABSTRACT
570 ,@vitDOD_PMID
571 ,@vitDOD_DOI
572 ,@vitDOD_ISSN
573 ,@vitDOD_MESH_TERMS
574 ,@vitDOD_SUBSTANCES
575 ,@vitDOD_PUB_TYPE
576 ,@vitDOD_EDITOR_NAME
577 ,@vitDOD_BOOKTITLE
578 ,@vitDOD_PUBLISHER
579 ,@vitDOD_ISBN
580 ,@vitDOD_CONF_NAME
581 ,@vitDOD_SPONSOR
582 ,@vitDOD_PAT_APPL_DATE
583 ,@vitDOD_PAT_APPL_NUM
584 ,@vitDOD_PAT_PRIO_DATES
585 ,@vitDOD_PAT_PRIO_NUM
586 ,@vitDOD_PAT_PUBL_DATE
587 ,@vitDOD_PAT_PUBL_NUM
588 ,@vitDOD_URL
589 ,@vitDOD_PRODUCT
590 ,@vitDOD_INDICATION
591 ,@vitDOD_KEY_PUBLICATION
592 ,@vitDOD_CLINICAL_ARTICLE
593 ,@vitDOD_COMMENT
594 ,@vitDOD_INSERTEDBY
595 ,@vitDOD_UPDATEDBY
596 ,@vitDOD_CREATED
597 ,@vitDOD_CREATEDBY
598 ,@vitDOD_MODIFIED
599 ,@vitDOD_MODIFIEDBY
600 ,@vitDOD_ORI_DUPE_OF_ORI_ID
601 ,@vitDOD_ORI_ORDERID
602 ,@vitDOD_ORI_PDF_DELIVERY_DATE
603 ,@vitDOD_ORI_STATUS
604 ,@vitDOD_ORI_COMMENT
605 ,@vitDOD_ORI_URGENCY
606 ,@vitDOD_ORI_MAILBODY
607 ,@vitDOD_ORI_ACTPUBMED
608 ,@vitDOD_ORI_CREATEDBY
609 ,@vitDOD_ORI_INSERTEDBY
610 ,@vitDOD_ORI_UPDATEDBY
611 ,@vitDOD_ORI_MODIFIEDBY
612 ,@vitDOD_ORI_CREATED
613 ,@vitDOD_ORI_MODIFIED
614 ,@vitDOD_LII_HIDDEN
615 ,@vitDOD_LII_COMMENT
616 ,@vitDOD_LII_CREATEDBY
617 ,@vitDOD_LII_MODIFIEDBY
618 ,@vitDOD_LII_INSERTEDBY
619 ,@vitDOD_LII_UPDATEDBY
620 ,@vitDOD_LII_CREATED
621 ,@vitDOD_LII_MODIFIED);
622
623 ELSEIF (pOPERATION = 'UPD') THEN
624 INSERT INTO DOCUMENTDATA_JN(
625 JN_OPERATION
626 ,JN_ORACLE_USER
627 ,JN_DATETIME
628 ,JN_NOTES
629 ,JN_APPLN
630 ,JN_SESSION
631 ,DOD_ID
632 ,DOD_ORI_ID
633 ,DOD_LII_ID
634 ,DOD_DOCTYPE
635 ,DOD_TITLE
636 ,DOD_JOURNAL_NAME
637 ,DOD_PUBL_YEAR
638 ,DOD_AUTHORS
639 ,DOD_VOLUME
640 ,DOD_ISSUE
641 ,DOD_PAGEINFO
642 ,DOD_LANGUAGE
643 ,DOD_KEYWORDS
644 ,DOD_AFFILIATION
645 ,DOD_ABSTRACT
646 ,DOD_PMID
647 ,DOD_DOI
648 ,DOD_ISSN
649 ,DOD_MESH_TERMS
650 ,DOD_SUBSTANCES
651 ,DOD_PUB_TYPE
652 ,DOD_EDITOR_NAME
653 ,DOD_BOOKTITLE
654 ,DOD_PUBLISHER
655 ,DOD_ISBN
656 ,DOD_CONF_NAME
657 ,DOD_SPONSOR
658 ,DOD_PAT_APPL_DATE
659 ,DOD_PAT_APPL_NUM
660 ,DOD_PAT_PRIO_DATES
661 ,DOD_PAT_PRIO_NUM
662 ,DOD_PAT_PUBL_DATE
663 ,DOD_PAT_PUBL_NUM
664 ,DOD_URL
665 ,DOD_PRODUCT
666 ,DOD_INDICATION
667 ,DOD_KEY_PUBLICATION
668 ,DOD_CLINICAL_ARTICLE
669 ,DOD_COMMENT
670 ,DOD_INSERTEDBY
671 ,DOD_UPDATEDBY
672 ,DOD_CREATED
673 ,DOD_CREATEDBY
674 ,DOD_MODIFIED
675 ,DOD_MODIFIEDBY
676 ,DOD_ORI_DUPE_OF_ORI_ID
677 ,DOD_ORI_ORDERID
678 ,DOD_ORI_PDF_DELIVERY_DATE
679 ,DOD_ORI_STATUS
680 ,DOD_ORI_COMMENT
681 ,DOD_ORI_URGENCY
682 ,DOD_ORI_MAILBODY
683 ,DOD_ORI_ACTPUBMED
684 ,DOD_ORI_CREATEDBY
685 ,DOD_ORI_INSERTEDBY
686 ,DOD_ORI_UPDATEDBY
687 ,DOD_ORI_MODIFIEDBY
688 ,DOD_ORI_CREATED
689 ,DOD_ORI_MODIFIED
690 ,DOD_LII_HIDDEN
691 ,DOD_LII_COMMENT
692 ,DOD_LII_CREATEDBY
693 ,DOD_LII_MODIFIEDBY
694 ,DOD_LII_INSERTEDBY
695 ,DOD_LII_UPDATEDBY
696 ,DOD_LII_CREATED
697 ,DOD_LII_MODIFIED)
698 VALUES(
699 pOPERATION
700 ,USER()
701 ,SYSDATE()
702 ,vjn_notes
703 ,CONCAT('CG$DOCUMENTDATA.',pOPERATION)
704 ,CONNECTION_ID()
705 ,@vutDOD_ID
706 ,@vutDOD_ORI_ID
707 ,@vutDOD_LII_ID
708 ,@vutDOD_DOCTYPE
709 ,@vutDOD_TITLE
710 ,@vutDOD_JOURNAL_NAME
711 ,@vutDOD_PUBL_YEAR
712 ,@vutDOD_AUTHORS
713 ,@vutDOD_VOLUME
714 ,@vutDOD_ISSUE
715 ,@vutDOD_PAGEINFO
716 ,@vutDOD_LANGUAGE
717 ,@vutDOD_KEYWORDS
718 ,@vutDOD_AFFILIATION
719 ,@vutDOD_ABSTRACT
720 ,@vutDOD_PMID
721 ,@vutDOD_DOI
722 ,@vutDOD_ISSN
723 ,@vutDOD_MESH_TERMS
724 ,@vutDOD_SUBSTANCES
725 ,@vutDOD_PUB_TYPE
726 ,@vutDOD_EDITOR_NAME
727 ,@vutDOD_BOOKTITLE
728 ,@vutDOD_PUBLISHER
729 ,@vutDOD_ISBN
730 ,@vutDOD_CONF_NAME
731 ,@vutDOD_SPONSOR
732 ,@vutDOD_PAT_APPL_DATE
733 ,@vutDOD_PAT_APPL_NUM
734 ,@vutDOD_PAT_PRIO_DATES
735 ,@vutDOD_PAT_PRIO_NUM
736 ,@vutDOD_PAT_PUBL_DATE
737 ,@vutDOD_PAT_PUBL_NUM
738 ,@vutDOD_URL
739 ,@vutDOD_PRODUCT
740 ,@vutDOD_INDICATION
741 ,@vutDOD_KEY_PUBLICATION
742 ,@vutDOD_CLINICAL_ARTICLE
743 ,@vutDOD_COMMENT
744 ,@vutDOD_INSERTEDBY
745 ,@vutDOD_UPDATEDBY
746 ,@vutDOD_CREATED
747 ,@vutDOD_CREATEDBY
748 ,@vutDOD_MODIFIED
749 ,@vutDOD_MODIFIEDBY
750 ,@vutDOD_ORI_DUPE_OF_ORI_ID
751 ,@vutDOD_ORI_ORDERID
752 ,@vutDOD_ORI_PDF_DELIVERY_DATE
753 ,@vutDOD_ORI_STATUS
754 ,@vutDOD_ORI_COMMENT
755 ,@vutDOD_ORI_URGENCY
756 ,@vutDOD_ORI_MAILBODY
757 ,@vutDOD_ORI_ACTPUBMED
758 ,@vutDOD_ORI_CREATEDBY
759 ,@vutDOD_ORI_INSERTEDBY
760 ,@vutDOD_ORI_UPDATEDBY
761 ,@vutDOD_ORI_MODIFIEDBY
762 ,@vutDOD_ORI_CREATED
763 ,@vutDOD_ORI_MODIFIED
764 ,@vutDOD_LII_HIDDEN
765 ,@vutDOD_LII_COMMENT
766 ,@vutDOD_LII_CREATEDBY
767 ,@vutDOD_LII_MODIFIEDBY
768 ,@vutDOD_LII_INSERTEDBY
769 ,@vutDOD_LII_UPDATEDBY
770 ,@vutDOD_LII_CREATED
771 ,@vutDOD_LII_MODIFIED);
772
773 ELSEIF (pOPERATION = 'DEL') THEN
774 INSERT INTO DOCUMENTDATA_JN(
775 JN_OPERATION
776 ,JN_ORACLE_USER
777 ,JN_DATETIME
778 ,JN_NOTES
779 ,JN_APPLN
780 ,JN_SESSION
781 ,DOD_ID
782 ,DOD_ORI_ID
783 ,DOD_LII_ID
784 ,DOD_DOCTYPE
785 ,DOD_TITLE
786 ,DOD_JOURNAL_NAME
787 ,DOD_PUBL_YEAR
788 ,DOD_AUTHORS
789 ,DOD_VOLUME
790 ,DOD_ISSUE
791 ,DOD_PAGEINFO
792 ,DOD_LANGUAGE
793 ,DOD_KEYWORDS
794 ,DOD_AFFILIATION
795 ,DOD_ABSTRACT
796 ,DOD_PMID
797 ,DOD_DOI
798 ,DOD_ISSN
799 ,DOD_MESH_TERMS
800 ,DOD_SUBSTANCES
801 ,DOD_PUB_TYPE
802 ,DOD_EDITOR_NAME
803 ,DOD_BOOKTITLE
804 ,DOD_PUBLISHER
805 ,DOD_ISBN
806 ,DOD_CONF_NAME
807 ,DOD_SPONSOR
808 ,DOD_PAT_APPL_DATE
809 ,DOD_PAT_APPL_NUM
810 ,DOD_PAT_PRIO_DATES
811 ,DOD_PAT_PRIO_NUM
812 ,DOD_PAT_PUBL_DATE
813 ,DOD_PAT_PUBL_NUM
814 ,DOD_URL
815 ,DOD_PRODUCT
816 ,DOD_INDICATION
817 ,DOD_KEY_PUBLICATION
818 ,DOD_CLINICAL_ARTICLE
819 ,DOD_COMMENT
820 ,DOD_INSERTEDBY
821 ,DOD_UPDATEDBY
822 ,DOD_CREATED
823 ,DOD_CREATEDBY
824 ,DOD_MODIFIED
825 ,DOD_MODIFIEDBY
826 ,DOD_ORI_DUPE_OF_ORI_ID
827 ,DOD_ORI_ORDERID
828 ,DOD_ORI_PDF_DELIVERY_DATE
829 ,DOD_ORI_STATUS
830 ,DOD_ORI_COMMENT
831 ,DOD_ORI_URGENCY
832 ,DOD_ORI_MAILBODY
833 ,DOD_ORI_ACTPUBMED
834 ,DOD_ORI_CREATEDBY
835 ,DOD_ORI_INSERTEDBY
836 ,DOD_ORI_UPDATEDBY
837 ,DOD_ORI_MODIFIEDBY
838 ,DOD_ORI_CREATED
839 ,DOD_ORI_MODIFIED
840 ,DOD_LII_HIDDEN
841 ,DOD_LII_COMMENT
842 ,DOD_LII_CREATEDBY
843 ,DOD_LII_MODIFIEDBY
844 ,DOD_LII_INSERTEDBY
845 ,DOD_LII_UPDATEDBY
846 ,DOD_LII_CREATED
847 ,DOD_LII_MODIFIED)
848 VALUES(
849 pOPERATION
850 ,USER()
851 ,SYSDATE()
852 ,vjn_notes
853 ,CONCAT('CG$DOCUMENTDATA.',pOPERATION)
854 ,CONNECTION_ID()
855 ,@vdtDOD_ID
856 ,@vdtDOD_ORI_ID
857 ,@vdtDOD_LII_ID
858 ,@vdtDOD_DOCTYPE
859 ,@vdtDOD_TITLE
860 ,@vdtDOD_JOURNAL_NAME
861 ,@vdtDOD_PUBL_YEAR
862 ,@vdtDOD_AUTHORS
863 ,@vdtDOD_VOLUME
864 ,@vdtDOD_ISSUE
865 ,@vdtDOD_PAGEINFO
866 ,@vdtDOD_LANGUAGE
867 ,@vdtDOD_KEYWORDS
868 ,@vdtDOD_AFFILIATION
869 ,@vdtDOD_ABSTRACT
870 ,@vdtDOD_PMID
871 ,@vdtDOD_DOI
872 ,@vdtDOD_ISSN
873 ,@vdtDOD_MESH_TERMS
874 ,@vdtDOD_SUBSTANCES
875 ,@vdtDOD_PUB_TYPE
876 ,@vdtDOD_EDITOR_NAME
877 ,@vdtDOD_BOOKTITLE
878 ,@vdtDOD_PUBLISHER
879 ,@vdtDOD_ISBN
880 ,@vdtDOD_CONF_NAME
881 ,@vdtDOD_SPONSOR
882 ,@vdtDOD_PAT_APPL_DATE
883 ,@vdtDOD_PAT_APPL_NUM
884 ,@vdtDOD_PAT_PRIO_DATES
885 ,@vdtDOD_PAT_PRIO_NUM
886 ,@vdtDOD_PAT_PUBL_DATE
887 ,@vdtDOD_PAT_PUBL_NUM
888 ,@vdtDOD_URL
889 ,@vdtDOD_PRODUCT
890 ,@vdtDOD_INDICATION
891 ,@vdtDOD_KEY_PUBLICATION
892 ,@vdtDOD_CLINICAL_ARTICLE
893 ,@vdtDOD_COMMENT
894 ,@vdtDOD_INSERTEDBY
895 ,@vdtDOD_UPDATEDBY
896 ,@vdtDOD_CREATED
897 ,@vdtDOD_CREATEDBY
898 ,@vdtDOD_MODIFIED
899 ,@vdtDOD_MODIFIEDBY
900 ,@vdtDOD_ORI_DUPE_OF_ORI_ID
901 ,@vdtDOD_ORI_ORDERID
902 ,@vdtDOD_ORI_PDF_DELIVERY_DATE
903 ,@vdtDOD_ORI_STATUS
904 ,@vdtDOD_ORI_COMMENT
905 ,@vdtDOD_ORI_URGENCY
906 ,@vdtDOD_ORI_MAILBODY
907 ,@vdtDOD_ORI_ACTPUBMED
908 ,@vdtDOD_ORI_CREATEDBY
909 ,@vdtDOD_ORI_INSERTEDBY
910 ,@vdtDOD_ORI_UPDATEDBY
911 ,@vdtDOD_ORI_MODIFIEDBY
912 ,@vdtDOD_ORI_CREATED
913 ,@vdtDOD_ORI_MODIFIED
914 ,@vdtDOD_LII_HIDDEN
915 ,@vdtDOD_LII_COMMENT
916 ,@vdtDOD_LII_CREATEDBY
917 ,@vdtDOD_LII_MODIFIEDBY
918 ,@vdtDOD_LII_INSERTEDBY
919 ,@vdtDOD_LII_UPDATEDBY
920 ,@vdtDOD_LII_CREATED
921 ,@vdtDOD_LII_MODIFIED);
922
923 END IF;
924
925END */$$
926DELIMITER ;
927
928/* Procedure structure for procedure `CG$DOCUMENTDATA_lck` */
929
930/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_lck` */;
931
932DELIMITER $$
933
934/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_lck`(pDOD_ID INT(38), nowait_flag BOOLEAN)
935BEGIN
936
937 DECLARE vDOD_ID INT(38);
938 DECLARE vDOD_RQI_ID DECIMAL(38,0);
939 DECLARE vDOD_ORI_ID DECIMAL(38,0);
940 DECLARE vDOD_LII_ID DECIMAL(38,0);
941 DECLARE vDOD_DOCTYPE VARCHAR(2);
942 DECLARE vDOD_TITLE TEXT;
943 DECLARE vDOD_JOURNAL_NAME VARCHAR(255);
944 DECLARE vDOD_PUBL_YEAR DECIMAL(4,0);
945 DECLARE vDOD_AUTHORS TEXT;
946 DECLARE vDOD_VOLUME VARCHAR(50);
947 DECLARE vDOD_ISSUE VARCHAR(50);
948 DECLARE vDOD_PAGEINFO VARCHAR(100);
949 DECLARE vDOD_LANGUAGE VARCHAR(100);
950 DECLARE vDOD_KEYWORDS TEXT;
951 DECLARE vDOD_AFFILIATION TEXT;
952 DECLARE vDOD_ABSTRACT TEXT;
953 DECLARE vDOD_PMID DECIMAL(10,0);
954 DECLARE vDOD_DOI VARCHAR(255);
955 DECLARE vDOD_ISSN VARCHAR(10);
956 DECLARE vDOD_MESH_TERMS TEXT;
957 DECLARE vDOD_SUBSTANCES TEXT;
958 DECLARE vDOD_PUB_TYPE VARCHAR(255);
959 DECLARE vDOD_EDITOR_NAME VARCHAR(255);
960 DECLARE vDOD_BOOKTITLE TEXT;
961 DECLARE vDOD_PUBLISHER VARCHAR(255);
962 DECLARE vDOD_ISBN VARCHAR(50);
963 DECLARE vDOD_CONF_NAME VARCHAR(255);
964 DECLARE vDOD_SPONSOR VARCHAR(100);
965 DECLARE vDOD_PAT_APPL_DATE VARCHAR(50);
966 DECLARE vDOD_PAT_APPL_NUM VARCHAR(50);
967 DECLARE vDOD_PAT_PRIO_DATES TEXT;
968 DECLARE vDOD_PAT_PRIO_NUM TEXT;
969 DECLARE vDOD_PAT_PUBL_DATE VARCHAR(50);
970 DECLARE vDOD_PAT_PUBL_NUM VARCHAR(50);
971 DECLARE vDOD_PRODUCT VARCHAR(255);
972 DECLARE vDOD_INDICATION TEXT;
973 DECLARE vDOD_KEY_PUBLICATION VARCHAR(1);
974 DECLARE vDOD_CLINICAL_ARTICLE VARCHAR(1);
975 DECLARE vDOD_COMMENT TEXT;
976 DECLARE vDOD_INSERTEDBY VARCHAR(50);
977 DECLARE vDOD_UPDATEDBY VARCHAR(50);
978 DECLARE vDOD_CREATED DATETIME;
979 DECLARE vDOD_CREATEDBY VARCHAR(30);
980 DECLARE vDOD_MODIFIED DATETIME;
981 DECLARE vDOD_MODIFIEDBY VARCHAR(30);
982 DECLARE vDOD_LINKTYPE VARCHAR(50);
983 DECLARE vDOD_URL TEXT;
984 DECLARE vCUST_COUNTRY VARCHAR(255);
985 DECLARE vDOD_LII_HIDDEN VARCHAR(1);
986 DECLARE vDOD_LII_COMMENT TEXT;
987 DECLARE vDOD_LII_CREATEDBY VARCHAR(50);
988 DECLARE vDOD_LII_MODIFIEDBY VARCHAR(50);
989 DECLARE vDOD_LII_INSERTEDBY VARCHAR(50);
990 DECLARE vDOD_LII_UPDATEDBY VARCHAR(50);
991 DECLARE vDOD_LII_CREATED DATETIME;
992 DECLARE vDOD_LII_MODIFIED DATETIME;
993 DECLARE vDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
994 DECLARE vDOD_ORI_ORDERID VARCHAR(50);
995 DECLARE vDOD_ORI_PDF_DELIVERY_DATE DATETIME;
996 DECLARE vDOD_ORI_STATUS VARCHAR(1);
997 DECLARE vDOD_ORI_COMMENT TEXT;
998 DECLARE vDOD_ORI_URGENCY VARCHAR(1);
999 DECLARE vDOD_ORI_MAILBODY TEXT;
1000 DECLARE vDOD_ORI_ACTPUBMED VARCHAR(1);
1001 DECLARE vDOD_ORI_CREATEDBY VARCHAR(50);
1002 DECLARE vDOD_ORI_INSERTEDBY VARCHAR(50);
1003 DECLARE vDOD_ORI_UPDATEDBY VARCHAR(50);
1004 DECLARE vDOD_ORI_MODIFIEDBY VARCHAR(50);
1005 DECLARE vDOD_ORI_CREATED DATETIME;
1006 DECLARE vDOD_ORI_MODIFIED DATETIME;
1007
1008 /* EXCEPTION HANDLING start */
1009 -- any other error
1010 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1011 BEGIN
1012 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1013 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1014 CALL CG$ERRORS_push(@text,
1015 'E',
1016 'API',
1017 @errno,
1018 'cg$DOCUMENTDATA_lck');
1019 CALL CG$ERRORS_raise_failure;
1020 END;
1021
1022 /* EXCEPTION HANDLING end */
1023
1024 IF (nowait_flag) THEN
1025
1026 #LOCK is not allowed in stored procedures : LOCK outside the procedure
1027 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
1028
1029 IF pDOD_ID IS NOT NULL THEN
1030
1031 SELECT
1032 DOD_ID
1033 , DOD_ORI_ID
1034 , DOD_LII_ID
1035 , DOD_DOCTYPE
1036 , DOD_TITLE
1037 , DOD_JOURNAL_NAME
1038 , DOD_PUBL_YEAR
1039 , DOD_AUTHORS
1040 , DOD_VOLUME
1041 , DOD_ISSUE
1042 , DOD_PAGEINFO
1043 , DOD_LANGUAGE
1044 , DOD_KEYWORDS
1045 , DOD_AFFILIATION
1046 , DOD_ABSTRACT
1047 , DOD_PMID
1048 , DOD_DOI
1049 , DOD_ISSN
1050 , DOD_MESH_TERMS
1051 , DOD_SUBSTANCES
1052 , DOD_PUB_TYPE
1053 , DOD_EDITOR_NAME
1054 , DOD_BOOKTITLE
1055 , DOD_PUBLISHER
1056 , DOD_ISBN
1057 , DOD_CONF_NAME
1058 , DOD_SPONSOR
1059 , DOD_PAT_APPL_DATE
1060 , DOD_PAT_APPL_NUM
1061 , DOD_PAT_PRIO_DATES
1062 , DOD_PAT_PRIO_NUM
1063 , DOD_PAT_PUBL_DATE
1064 , DOD_PAT_PUBL_NUM
1065 , DOD_URL
1066 , DOD_PRODUCT
1067 , DOD_INDICATION
1068 , DOD_KEY_PUBLICATION
1069 , DOD_CLINICAL_ARTICLE
1070 , DOD_COMMENT
1071 , DOD_INSERTEDBY
1072 , DOD_UPDATEDBY
1073 , DOD_CREATED
1074 , DOD_CREATEDBY
1075 , DOD_MODIFIED
1076 , DOD_MODIFIEDBY
1077 , DOD_LII_COMMENT
1078 , DOD_LII_HIDDEN
1079 , DOD_LII_INSERTEDBY
1080 , DOD_LII_UPDATEDBY
1081 , DOD_LII_CREATED
1082 , DOD_LII_CREATEDBY
1083 , DOD_LII_MODIFIED
1084 , DOD_LII_MODIFIEDBY
1085 , DOD_ORI_DUPE_OF_ORI_ID
1086 , DOD_ORI_ORDERID
1087 , DOD_ORI_PDF_DELIVERY_DATE
1088 , DOD_ORI_STATUS
1089 , DOD_ORI_COMMENT
1090 , DOD_ORI_URGENCY
1091 , DOD_ORI_MAILBODY
1092 , DOD_ORI_ACTPUBMED
1093 , DOD_ORI_CREATEDBY
1094 , DOD_ORI_INSERTEDBY
1095 , DOD_ORI_UPDATEDBY
1096 , DOD_ORI_MODIFIEDBY
1097 , DOD_ORI_CREATED
1098 , DOD_ORI_MODIFIED
1099 INTO
1100 @vDOD_ID
1101 , @vDOD_ORI_ID
1102 , @vDOD_LII_ID
1103 , @vDOD_DOCTYPE
1104 , @vDOD_TITLE
1105 , @vDOD_JOURNAL_NAME
1106 , @vDOD_PUBL_YEAR
1107 , @vDOD_AUTHORS
1108 , @vDOD_VOLUME
1109 , @vDOD_ISSUE
1110 , @vDOD_PAGEINFO
1111 , @vDOD_LANGUAGE
1112 , @vDOD_KEYWORDS
1113 , @vDOD_AFFILIATION
1114 , @vDOD_ABSTRACT
1115 , @vDOD_PMID
1116 , @vDOD_DOI
1117 , @vDOD_ISSN
1118 , @vDOD_MESH_TERMS
1119 , @vDOD_SUBSTANCES
1120 , @vDOD_PUB_TYPE
1121 , @vDOD_EDITOR_NAME
1122 , @vDOD_BOOKTITLE
1123 , @vDOD_PUBLISHER
1124 , @vDOD_ISBN
1125 , @vDOD_CONF_NAME
1126 , @vDOD_SPONSOR
1127 , @vDOD_PAT_APPL_DATE
1128 , @vDOD_PAT_APPL_NUM
1129 , @vDOD_PAT_PRIO_DATES
1130 , @vDOD_PAT_PRIO_NUM
1131 , @vDOD_PAT_PUBL_DATE
1132 , @vDOD_PAT_PUBL_NUM
1133 , @vDOD_URL
1134 , @vDOD_PRODUCT
1135 , @vDOD_INDICATION
1136 , @vDOD_KEY_PUBLICATION
1137 , @vDOD_CLINICAL_ARTICLE
1138 , @vDOD_COMMENT
1139 , @vDOD_INSERTEDBY
1140 , @vDOD_UPDATEDBY
1141 , @vDOD_CREATED
1142 , @vDOD_CREATEDBY
1143 , @vDOD_MODIFIED
1144 , @vDOD_MODIFIEDBY
1145 , @vDOD_LII_COMMENT
1146 , @vDOD_LII_HIDDEN
1147 , @vDOD_LII_INSERTEDBY
1148 , @vDOD_LII_UPDATEDBY
1149 , @vDOD_LII_CREATED
1150 , @vDOD_LII_CREATEDBY
1151 , @vDOD_LII_MODIFIED
1152 , @vDOD_LII_MODIFIEDBY
1153 , @vDOD_ORI_DUPE_OF_ORI_ID
1154 , @vDOD_ORI_ORDERID
1155 , @vDOD_ORI_PDF_DELIVERY_DATE
1156 , @vDOD_ORI_STATUS
1157 , @vDOD_ORI_COMMENT
1158 , @vDOD_ORI_URGENCY
1159 , @vDOD_ORI_MAILBODY
1160 , @vDOD_ORI_ACTPUBMED
1161 , @vDOD_ORI_CREATEDBY
1162 , @vDOD_ORI_INSERTEDBY
1163 , @vDOD_ORI_UPDATEDBY
1164 , @vDOD_ORI_MODIFIEDBY
1165 , @vDOD_ORI_CREATED
1166 , @vDOD_ORI_MODIFIED
1167 FROM DOCUMENTDATA
1168 WHERE DOCUMENTDATA.DOD_ID = pDOD_ID
1169 FOR UPDATE;
1170
1171 END IF;
1172
1173 END IF;
1174END */$$
1175DELIMITER ;
1176
1177/* Procedure structure for procedure `CG$DOCUMENTDATA_slct` */
1178
1179/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_slct` */;
1180
1181DELIMITER $$
1182
1183/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_slct`(pDOD_ID INT(38))
1184BEGIN
1185
1186 DECLARE vDOD_ID INT(38);
1187 DECLARE vDOD_ORI_ID DECIMAL(38,0);
1188 DECLARE vDOD_LII_ID DECIMAL(38,0);
1189 DECLARE vDOD_DOCTYPE VARCHAR(2);
1190 DECLARE vDOD_TITLE TEXT;
1191 DECLARE vDOD_JOURNAL_NAME VARCHAR(255);
1192 DECLARE vDOD_PUBL_YEAR DECIMAL(4,0);
1193 DECLARE vDOD_AUTHORS TEXT;
1194 DECLARE vDOD_VOLUME VARCHAR(50);
1195 DECLARE vDOD_ISSUE VARCHAR(50);
1196 DECLARE vDOD_PAGEINFO VARCHAR(100);
1197 DECLARE vDOD_LANGUAGE VARCHAR(100);
1198 DECLARE vDOD_KEYWORDS TEXT;
1199 DECLARE vDOD_AFFILIATION TEXT;
1200 DECLARE vDOD_ABSTRACT TEXT;
1201 DECLARE vDOD_PMID DECIMAL(10,0);
1202 DECLARE vDOD_DOI VARCHAR(255);
1203 DECLARE vDOD_ISSN VARCHAR(10);
1204 DECLARE vDOD_MESH_TERMS TEXT;
1205 DECLARE vDOD_SUBSTANCES TEXT;
1206 DECLARE vDOD_PUB_TYPE VARCHAR(255);
1207 DECLARE vDOD_EDITOR_NAME VARCHAR(255);
1208 DECLARE vDOD_BOOKTITLE TEXT;
1209 DECLARE vDOD_PUBLISHER VARCHAR(255);
1210 DECLARE vDOD_ISBN VARCHAR(50);
1211 DECLARE vDOD_CONF_NAME VARCHAR(255);
1212 DECLARE vDOD_SPONSOR VARCHAR(100);
1213 DECLARE vDOD_PAT_APPL_DATE VARCHAR(50);
1214 DECLARE vDOD_PAT_APPL_NUM VARCHAR(50);
1215 DECLARE vDOD_PAT_PRIO_DATES TEXT;
1216 DECLARE vDOD_PAT_PRIO_NUM TEXT;
1217 DECLARE vDOD_PAT_PUBL_DATE VARCHAR(50);
1218 DECLARE vDOD_PAT_PUBL_NUM VARCHAR(50);
1219 DECLARE vDOD_PRODUCT VARCHAR(255);
1220 DECLARE vDOD_INDICATION TEXT;
1221 DECLARE vDOD_KEY_PUBLICATION VARCHAR(1);
1222 DECLARE vDOD_CLINICAL_ARTICLE VARCHAR(1);
1223 DECLARE vDOD_COMMENT TEXT;
1224 DECLARE vDOD_INSERTEDBY VARCHAR(50);
1225 DECLARE vDOD_UPDATEDBY VARCHAR(50);
1226 DECLARE vDOD_CREATED DATETIME;
1227 DECLARE vDOD_CREATEDBY VARCHAR(30);
1228 DECLARE vDOD_MODIFIED DATETIME;
1229 DECLARE vDOD_MODIFIEDBY VARCHAR(30);
1230 DECLARE vDOD_LINKTYPE VARCHAR(50);
1231 DECLARE vDOD_URL TEXT;
1232 DECLARE vCUST_COUNTRY VARCHAR(255);
1233 DECLARE vDOD_LII_HIDDEN VARCHAR(1);
1234 DECLARE vDOD_LII_COMMENT TEXT;
1235 DECLARE vDOD_LII_CREATEDBY VARCHAR(50);
1236 DECLARE vDOD_LII_MODIFIEDBY VARCHAR(50);
1237 DECLARE vDOD_LII_INSERTEDBY VARCHAR(50);
1238 DECLARE vDOD_LII_UPDATEDBY VARCHAR(50);
1239 DECLARE vDOD_LII_CREATED DATETIME;
1240 DECLARE vDOD_LII_MODIFIED DATETIME;
1241 DECLARE vDOD_ORI_DUPE_OF_ORI_ID DECIMAL(38,0);
1242 DECLARE vDOD_ORI_ORDERID VARCHAR(50);
1243 DECLARE vDOD_ORI_PDF_DELIVERY_DATE DATETIME;
1244 DECLARE vDOD_ORI_STATUS VARCHAR(1);
1245 DECLARE vDOD_ORI_COMMENT TEXT;
1246 DECLARE vDOD_ORI_URGENCY VARCHAR(1);
1247 DECLARE vDOD_ORI_MAILBODY TEXT;
1248 DECLARE vDOD_ORI_ACTPUBMED VARCHAR(1);
1249 DECLARE vDOD_ORI_CREATEDBY VARCHAR(50);
1250 DECLARE vDOD_ORI_INSERTEDBY VARCHAR(50);
1251 DECLARE vDOD_ORI_UPDATEDBY VARCHAR(50);
1252 DECLARE vDOD_ORI_MODIFIEDBY VARCHAR(50);
1253 DECLARE vDOD_ORI_CREATED DATETIME;
1254 DECLARE vDOD_ORI_MODIFIED DATETIME;
1255
1256 /* EXCEPTION HANDLING start */
1257 -- any other error
1258 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1259 BEGIN
1260 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1261 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1262 CALL CG$ERRORS_push(@text,
1263 'E',
1264 'API',
1265 @errno,
1266 'cg$DOCUMENTDATA_slct');
1267 CALL CG$ERRORS_raise_failure;
1268 END;
1269
1270 /* EXCEPTION HANDLING end */
1271
1272 IF pDOD_ID IS NOT NULL THEN
1273 SELECT
1274 DOD_ID
1275 , DOD_ORI_ID
1276 , DOD_LII_ID
1277 , DOD_DOCTYPE
1278 , DOD_TITLE
1279 , DOD_JOURNAL_NAME
1280 , DOD_PUBL_YEAR
1281 , DOD_AUTHORS
1282 , DOD_VOLUME
1283 , DOD_ISSUE
1284 , DOD_PAGEINFO
1285 , DOD_LANGUAGE
1286 , DOD_KEYWORDS
1287 , DOD_AFFILIATION
1288 , DOD_ABSTRACT
1289 , DOD_PMID
1290 , DOD_DOI
1291 , DOD_ISSN
1292 , DOD_MESH_TERMS
1293 , DOD_SUBSTANCES
1294 , DOD_PUB_TYPE
1295 , DOD_EDITOR_NAME
1296 , DOD_BOOKTITLE
1297 , DOD_PUBLISHER
1298 , DOD_ISBN
1299 , DOD_CONF_NAME
1300 , DOD_SPONSOR
1301 , DOD_PAT_APPL_DATE
1302 , DOD_PAT_APPL_NUM
1303 , DOD_PAT_PRIO_DATES
1304 , DOD_PAT_PRIO_NUM
1305 , DOD_PAT_PUBL_DATE
1306 , DOD_PAT_PUBL_NUM
1307 , DOD_URL
1308 , DOD_PRODUCT
1309 , DOD_INDICATION
1310 , DOD_KEY_PUBLICATION
1311 , DOD_CLINICAL_ARTICLE
1312 , DOD_COMMENT
1313 , DOD_INSERTEDBY
1314 , DOD_UPDATEDBY
1315 , DOD_CREATED
1316 , DOD_CREATEDBY
1317 , DOD_MODIFIED
1318 , DOD_MODIFIEDBY
1319 , DOD_LII_COMMENT
1320 , DOD_LII_HIDDEN
1321 , DOD_LII_INSERTEDBY
1322 , DOD_LII_UPDATEDBY
1323 , DOD_LII_CREATED
1324 , DOD_LII_CREATEDBY
1325 , DOD_LII_MODIFIED
1326 , DOD_LII_MODIFIEDBY
1327 , DOD_ORI_DUPE_OF_ORI_ID
1328 , DOD_ORI_ORDERID
1329 , DOD_ORI_PDF_DELIVERY_DATE
1330 , DOD_ORI_STATUS
1331 , DOD_ORI_COMMENT
1332 , DOD_ORI_URGENCY
1333 , DOD_ORI_MAILBODY
1334
1335 , DOD_ORI_ACTPUBMED
1336 , DOD_ORI_CREATEDBY
1337 , DOD_ORI_INSERTEDBY
1338 , DOD_ORI_UPDATEDBY
1339 , DOD_ORI_MODIFIEDBY
1340 , DOD_ORI_CREATED
1341 , DOD_ORI_MODIFIED
1342 INTO
1343 @vDOD_ID
1344 , @vDOD_ORI_ID
1345 , @vDOD_LII_ID
1346 , @vDOD_DOCTYPE
1347 , @vDOD_TITLE
1348 , @vDOD_JOURNAL_NAME
1349 , @vDOD_PUBL_YEAR
1350 , @vDOD_AUTHORS
1351 , @vDOD_VOLUME
1352 , @vDOD_ISSUE
1353 , @vDOD_PAGEINFO
1354 , @vDOD_LANGUAGE
1355 , @vDOD_KEYWORDS
1356 , @vDOD_AFFILIATION
1357 , @vDOD_ABSTRACT
1358 , @vDOD_PMID
1359 , @vDOD_DOI
1360 , @vDOD_ISSN
1361 , @vDOD_MESH_TERMS
1362 , @vDOD_SUBSTANCES
1363 , @vDOD_PUB_TYPE
1364 , @vDOD_EDITOR_NAME
1365 , @vDOD_BOOKTITLE
1366 , @vDOD_PUBLISHER
1367 , @vDOD_ISBN
1368 , @vDOD_CONF_NAME
1369 , @vDOD_SPONSOR
1370 , @vDOD_PAT_APPL_DATE
1371 , @vDOD_PAT_APPL_NUM
1372 , @vDOD_PAT_PRIO_DATES
1373 , @vDOD_PAT_PRIO_NUM
1374 , @vDOD_PAT_PUBL_DATE
1375 , @vDOD_PAT_PUBL_NUM
1376 , @vDOD_URL
1377 , @vDOD_PRODUCT
1378 , @vDOD_INDICATION
1379 , @vDOD_KEY_PUBLICATION
1380 , @vDOD_CLINICAL_ARTICLE
1381 , @vDOD_COMMENT
1382 , @vDOD_INSERTEDBY
1383 , @vDOD_UPDATEDBY
1384 , @vDOD_CREATED
1385 , @vDOD_CREATEDBY
1386 , @vDOD_MODIFIED
1387 , @vDOD_MODIFIEDBY
1388 , @vDOD_LII_COMMENT
1389 , @vDOD_LII_HIDDEN
1390 , @vDOD_LII_INSERTEDBY
1391 , @vDOD_LII_UPDATEDBY
1392 , @vDOD_LII_CREATED
1393 , @vDOD_LII_CREATEDBY
1394 , @vDOD_LII_MODIFIED
1395 , @vDOD_LII_MODIFIEDBY
1396 , @vDOD_ORI_DUPE_OF_ORI_ID
1397 , @vDOD_ORI_ORDERID
1398 , @vDOD_ORI_PDF_DELIVERY_DATE
1399 , @vDOD_ORI_STATUS
1400 , @vDOD_ORI_COMMENT
1401 , @vDOD_ORI_URGENCY
1402 , @vDOD_ORI_MAILBODY
1403 , @vDOD_ORI_ACTPUBMED
1404 , @vDOD_ORI_CREATEDBY
1405 , @vDOD_ORI_INSERTEDBY
1406 , @vDOD_ORI_UPDATEDBY
1407 , @vDOD_ORI_MODIFIEDBY
1408 , @vDOD_ORI_CREATED
1409 , @vDOD_ORI_MODIFIED
1410 FROM DOCUMENTDATA
1411 WHERE DOCUMENTDATA.DOD_ID = pDOD_ID;
1412 END IF;
1413END */$$
1414DELIMITER ;
1415
1416/* Procedure structure for procedure `CG$ERRORS_raise_failure` */
1417
1418/*!50003 DROP PROCEDURE IF EXISTS `CG$ERRORS_raise_failure` */;
1419
1420DELIMITER $$
1421
1422/*!50003 CREATE PROCEDURE `CG$ERRORS_raise_failure`()
1423BEGIN
1424
1425 DECLARE vError_msg VARCHAR(10000);
1426 SET vError_msg := CG$ERRORS_LookErrors();
1427
1428 IF ( LENGTH(vError_msg) > 128) THEN
1429 SET vError_msg := CONCAT(SUBSTR(vError_msg, 1, 124), '...');
1430 END IF;
1431 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = vError_msg;
1432
1433END */$$
1434DELIMITER ;
1435
1436/* Procedure structure for procedure `CG$USERROLES_del` */
1437
1438/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_del` */;
1439
1440DELIMITER $$
1441
1442/*!50003 CREATE PROCEDURE `CG$USERROLES_del`(pURO_ID INT(38), pDO_DEL BOOLEAN)
1443BEGIN
1444 /* EXCEPTION HANDLING start */
1445 -- OTHER exceptions
1446 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1447 BEGIN
1448 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1449 CALL CG$ERRORS_push(@text,
1450 'E',
1451 'API',
1452 @errno,
1453 'cg$USERROLES_del.others');
1454 CALL CG$ERRORS_raise_failure;
1455 END;
1456 /* EXCEPTION HANDLING end */
1457 IF (pDO_DEL) THEN
1458 IF pURO_ID IS NOT NULL THEN
1459 DELETE FROM USERROLES WHERE URO_ID = pURO_ID;
1460 END IF;
1461 END IF;
1462
1463END */$$
1464DELIMITER ;
1465
1466/* Procedure structure for procedure `cursor_proc` */
1467
1468/*!50003 DROP PROCEDURE IF EXISTS `cursor_proc` */;
1469
1470DELIMITER $$
1471
1472/*!50003 CREATE PROCEDURE `cursor_proc`()
1473BEGIN
1474 DECLARE eof BOOL DEFAULT FALSE;
1475 DECLARE v_file, v_position TEXT;
1476
1477 DECLARE cur CURSOR FOR
1478 SELECT `ROL_NAME`, `ROL_DESCRIPTION` FROM ROLES;
1479
1480 #Handler: When a cursor reaches the end of a data set/While no more rows are available
1481 DECLARE CONTINUE HANDLER
1482 FOR NOT FOUND
1483 BEGIN
1484 SET eof := TRUE;
1485 END;
1486
1487 OPEN cur;
1488 lbl_loop : LOOP
1489 FETCH cur INTO v_file, v_position;
1490 IF eof IS TRUE THEN
1491 LEAVE lbl_loop;
1492 END IF;
1493
1494 SELECT CONCAT("I'll do something with ", v_file, v_position);
1495 END LOOP lbl_loop;
1496 CLOSE cur;
1497END */$$
1498DELIMITER ;
1499
1500/* Procedure structure for procedure `ELIB_JOBS_WRITE_JOBLOG` */
1501
1502/*!50003 DROP PROCEDURE IF EXISTS `ELIB_JOBS_WRITE_JOBLOG` */;
1503
1504DELIMITER $$
1505
1506/*!50003 CREATE PROCEDURE `ELIB_JOBS_WRITE_JOBLOG`(P_JOBNAME VARCHAR(100),
1507 P_STATUS VARCHAR(1),
1508 P_MESSAGE TEXT)
1509BEGIN
1510
1511 INSERT INTO JOBLOG
1512 (JLO_JOBNAME,
1513 JLO_STATUS,
1514 JLO_TIMESTAMP,
1515 JLO_MESSAGE)
1516 VALUES
1517 (P_JOBNAME,
1518 P_STATUS,
1519 SYSDATE(),
1520 P_MESSAGE);
1521
1522 COMMIT;
1523
1524END */$$
1525DELIMITER ;
1526
1527/* Procedure structure for procedure `g_errorcode` */
1528
1529/*!50003 DROP PROCEDURE IF EXISTS `g_errorcode` */;
1530
1531DELIMITER $$
1532
1533/*!50003 CREATE PROCEDURE `g_errorcode`()
1534BEGIN
1535 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1536 BEGIN
1537 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
1538 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1539 SET @full_error = CONCAT("Touhid: ERROR ", @errno, " (", @sqlstate, "): ", @text);
1540 SELECT @full_error;
1541
1542 #SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @text;
1543 END;
1544 -- this will produce a 1146 error
1545 SELECT `c` FROM `temptab`;
1546END */$$
1547DELIMITER ;
1548
1549/* Procedure structure for procedure `test_error` */
1550
1551/*!50003 DROP PROCEDURE IF EXISTS `test_error` */;
1552
1553DELIMITER $$
1554
1555/*!50003 CREATE PROCEDURE `test_error`()
1556BEGIN
1557 DECLARE EXIT HANDLER
1558 FOR 1146
1559 BEGIN
1560 SIGNAL SQLSTATE '45000' SET
1561 MESSAGE_TEXT = 'Temporary Tables not found; did you call init() procedure?';
1562 END;
1563 -- this will produce a 1146 error
1564 SELECT `c` FROM `temptab`;
1565END */$$
1566DELIMITER ;
1567
1568/* Procedure structure for procedure `validate_mandatory` */
1569
1570/*!50003 DROP PROCEDURE IF EXISTS `validate_mandatory` */;
1571
1572DELIMITER $$
1573
1574/*!50003 CREATE PROCEDURE `validate_mandatory`(field_name varchar(100), table_name varchar(100), loc varchar(100))
1575BEGIN
1576 declare v_db_field VARCHAR(100);
1577 DECLARE v_table_name VARCHAR(100);
1578 DECLARE v_loc VARCHAR(100);
1579
1580 if ROLES.ROL_NAME is null then
1581 call g_errorcode();
1582 end if;
1583 END */$$
1584DELIMITER ;
1585
1586/* Procedure structure for procedure `while_example` */
1587
1588/*!50003 DROP PROCEDURE IF EXISTS `while_example` */;
1589
1590DELIMITER $$
1591
1592/*!50003 CREATE PROCEDURE `while_example`()
1593BEGIN
1594 declare str VARCHAR(255) default '';
1595 declare x INT default 0;
1596 SET x = 1;
1597
1598 WHILE x <= 5 DO
1599 SET str = CONCAT(str,x,',');
1600 SET x = x + 1;
1601 END WHILE;
1602
1603 select str;
1604END */$$
1605DELIMITER ;
1606
1607/* Procedure structure for procedure `CG$CUSTOMERORDERS_del` */
1608
1609/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_del` */;
1610
1611DELIMITER $$
1612
1613/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_del`(pCUO_ID INT(38), pDO_DEL BOOLEAN)
1614BEGIN
1615 -- AR 180215 : Work in Progress
1616
1617 /* EXCEPTION HANDLING start */
1618 -- OTHER exceptions
1619 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1620 BEGIN
1621
1622 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1623
1624 CALL CG$ERRORS_push(@text,
1625 'E',
1626 'API',
1627 @errno,
1628 'cg$CUSTOMERORDERS_del.others'
1629 );
1630
1631 CALL CG$ERRORS_raise_failure;
1632
1633 END;
1634 /* EXCEPTION HANDLING end */
1635
1636 IF (pDO_DEL) THEN
1637 #SELECT CONCAT('Deleted ID: ', vCUO_ID);
1638
1639 IF pCUO_ID IS NOT NULL THEN
1640
1641 DELETE FROM CUSTOMERORDERS WHERE CUO_ID = pCUO_ID;
1642
1643 END IF;
1644
1645 END IF;
1646
1647END */$$
1648DELIMITER ;
1649
1650/* Procedure structure for procedure `CG$CUSTOMERORDERS_ins` */
1651
1652/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_ins` */;
1653
1654DELIMITER $$
1655
1656/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_ins`(pCUO_ID INT(38), pDO_INS BOOLEAN)
1657BEGIN
1658 -- AR 180215 : Work in Progress
1659
1660 /* EXCEPTION HANDLING start */
1661 -- OTHER exceptions
1662 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1663 BEGIN
1664
1665 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1666
1667 CALL CG$ERRORS_push(@text,
1668 'E',
1669 'API',
1670 @errno,
1671 'cg$CUSTOMERORDERS_ins.others'
1672 );
1673
1674 CALL CG$ERRORS_raise_failure;
1675
1676 END;
1677 /* EXCEPTION HANDLING end */
1678
1679 CALL CG$CUSTOMERORDERS_up_autogen_columns(pCUO_ID,'INS',pDO_INS);
1680
1681 IF (pDO_INS) THEN
1682
1683 INSERT INTO CUSTOMERORDERS(
1684 CUO_ORI_ID,
1685 CUO_CUS_ID)
1686 VALUES(
1687 @vitCUO_ORI_ID,
1688 @vitCUO_CUS_ID);
1689
1690 END IF;
1691
1692END */$$
1693DELIMITER ;
1694
1695/* Procedure structure for procedure `CG$CUSTOMERORDERS_lck` */
1696
1697/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_lck` */;
1698
1699DELIMITER $$
1700
1701/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_lck`(pCUO_ID INT(38), nowait_flag BOOLEAN)
1702BEGIN
1703 -- AR 180215 : Work in Progress
1704
1705 DECLARE vCUO_ID INT(38);
1706 DECLARE vCUO_ORI_ID INT(38);
1707 DECLARE vCUO_CUS_ID INT(38);
1708 /* EXCEPTION HANDLING start */
1709 -- OTHER exceptions
1710 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1711 BEGIN
1712
1713 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1714
1715 CALL CG$ERRORS_push(@text,
1716 'E',
1717 'API',
1718 @errno,
1719 'cg$CUSTOMERORDERS_lck.others'
1720 );
1721
1722 CALL CG$ERRORS_raise_failure;
1723
1724 END;
1725 /* EXCEPTION HANDLING end */
1726
1727
1728 IF (nowait_flag) THEN
1729 #LOCK is not allowed in stored procedures : LOCK outside the procedure
1730 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
1731
1732 IF pCUO_ID IS NOT NULL THEN
1733
1734 SELECT
1735 CUO_ID,
1736 CUO_ORI_ID,
1737 CUO_CUS_ID
1738
1739 INTO
1740 @vCUO_ID,
1741 @vCUO_ORI_ID,
1742 @vCUO_CUS_ID
1743
1744 FROM CUSTOMERORDERS
1745 WHERE CUSTOMERORDERS.CUO_ID = pCUO_ID
1746 FOR UPDATE;
1747
1748 END IF;
1749
1750 END IF;
1751
1752END */$$
1753DELIMITER ;
1754
1755/* Procedure structure for procedure `CG$CUSTOMERORDERS_slct` */
1756
1757/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_slct` */;
1758
1759DELIMITER $$
1760
1761/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_slct`(pCUO_ID INT(38))
1762BEGIN
1763 -- AR 180215 : Work in Progress
1764 DECLARE vCUO_ID INT(38);
1765 DECLARE vCUO_ORI_ID INT(38);
1766 DECLARE vCUO_CUS_ID INT(38);
1767
1768 /* EXCEPTION HANDLING start */
1769 -- OTHER exceptions
1770 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1771 BEGIN
1772
1773 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1774
1775 CALL CG$ERRORS_push(@text,
1776 'E',
1777 'API',
1778 @errno,
1779 'cg$CUSTOMERORDERS_slct.others'
1780 );
1781
1782 CALL CG$ERRORS_raise_failure;
1783
1784 END;
1785 /* EXCEPTION HANDLING end */
1786
1787 IF pCUO_ID IS NOT NULL THEN
1788
1789 SELECT
1790 CUO_ID,
1791 CUO_ORI_ID,
1792 CUO_CUS_ID
1793 INTO
1794 @vCUO_ID,
1795 @vCUO_ORI_ID,
1796 @vCUO_CUS_ID
1797 FROM CUSTOMERORDERS
1798 WHERE CUSTOMERORDERS.CUO_ID = pCUO_ID;
1799
1800 END IF;
1801
1802END */$$
1803DELIMITER ;
1804
1805/* Procedure structure for procedure `CG$CUSTOMERORDERS_upd` */
1806
1807/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_upd` */;
1808
1809DELIMITER $$
1810
1811/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_upd`(pCUO_ID INT(38), pDO_UPD BOOLEAN)
1812BEGIN
1813 -- AR 180215 : Work in Progress
1814
1815 /* EXCEPTION HANDLING start */
1816 -- OTHER exceptions
1817 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1818 BEGIN
1819
1820 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1821
1822 CALL CG$ERRORS_push(@text,
1823 'E',
1824 'API',
1825 @errno,
1826 'cg$CUSTOMERORDERS_upd.others'
1827 );
1828
1829 CALL CG$ERRORS_raise_failure;
1830
1831 END;
1832 /* EXCEPTION HANDLING end */
1833
1834 #CALL CG$CUSTOMERORDERS_slct(pCUO_ID);
1835
1836 /*
1837 * Fetching VALUE OF MODIFIED & MODIFIEDBY
1838 */
1839 CALL CG$CUSTOMERORDERS_up_autogen_columns(pCUO_ID,'UPD',pDO_UPD);
1840
1841 IF (pDO_UPD) THEN
1842
1843 UPDATE
1844 CUSTOMERORDERS
1845 SET
1846 CUO_ORI_ID = @vutCUO_ORI_ID,
1847 CUO_CUS_ID = @vutCUO_CUS_ID
1848 WHERE
1849 CUO_ID = pCUO_ID;
1850
1851 END IF;
1852
1853 #CALL CG$CUSTOMERORDERS_slct(pCUO_ID);
1854
1855END */$$
1856DELIMITER ;
1857
1858/* Procedure structure for procedure `CG$CUSTOMERORDERS_up_autogen_columns` */
1859
1860/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERORDERS_up_autogen_columns` */;
1861
1862DELIMITER $$
1863
1864/*!50003 CREATE PROCEDURE `CG$CUSTOMERORDERS_up_autogen_columns`(pCUO_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
1865BEGIN
1866 -- AR 180215 : Work in Progress
1867
1868 /* EXCEPTION HANDLING start */
1869 -- OTHER exceptions
1870 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1871 BEGIN
1872
1873 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1874
1875 CALL CG$ERRORS_push(@text,
1876 'E',
1877 'API',
1878 @errno,
1879 'cg$CUSTOMERORDERS_up_autogen_columns.others'
1880 );
1881
1882 CALL CG$ERRORS_raise_failure;
1883
1884 END;
1885 /* EXCEPTION HANDLING end */
1886
1887 /*
1888 * MariaDB does not have DEFAULT variable. So IFNULL is used
1889 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
1890 */
1891 SET operation = IFNULL(operation, 'INS');
1892
1893 IF (operation = 'INS') THEN
1894 /*
1895 * CUO_ID is already an Auto-Increment field
1896 * IF (pCUO_ID IS NOT NULL AND @vitCUO_CREATED = TRUE) THEN
1897 * SELECT LAST_INSERT_ID() INTO @vitCUO_ID FROM CUSTOMERORDERS;
1898 * END IF;
1899 */
1900 SET operation = IFNULL(operation, 'INS');
1901 END IF;
1902
1903END */$$
1904DELIMITER ;
1905
1906/* Procedure structure for procedure `CG$CUSTOMERS_del` */
1907
1908/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_del` */;
1909
1910DELIMITER $$
1911
1912/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_del`(pCUS_ID INT(38), pDO_DEL BOOLEAN)
1913BEGIN
1914 -- AR 180215 : Work in Progress
1915 /* EXCEPTION HANDLING start */
1916 -- OTHER exceptions
1917 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1918 BEGIN
1919
1920 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1921
1922 CALL CG$ERRORS_push(@text,
1923 'E',
1924 'API',
1925 @errno,
1926 'cg$CUSTOMERS_del.others'
1927 );
1928
1929 CALL CG$ERRORS_raise_failure;
1930
1931 END;
1932 /* EXCEPTION HANDLING end */
1933
1934 IF (pDO_DEL) THEN
1935
1936 #SELECT CONCAT('Deleted ID: ', vCUS_ID);
1937 IF pCUS_ID IS NOT NULL THEN
1938
1939 DELETE FROM CUSTOMERS WHERE CUS_ID = pCUS_ID;
1940
1941 END IF;
1942
1943 END IF;
1944
1945END */$$
1946DELIMITER ;
1947
1948/* Procedure structure for procedure `CG$CUSTOMERS_ins` */
1949
1950/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_ins` */;
1951
1952DELIMITER $$
1953
1954/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_ins`(pCUS_ID INT(38), pDO_INS BOOLEAN)
1955BEGIN
1956
1957 -- AR 180215 : Work in Progress
1958 /* EXCEPTION HANDLING start */
1959 -- OTHER exceptions
1960 DECLARE EXIT HANDLER FOR SQLEXCEPTION
1961 BEGIN
1962
1963 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
1964
1965 CALL CG$ERRORS_push(@text,
1966 'E',
1967 'API',
1968 @errno,
1969 'cg$CUSTOMERS_ins.others'
1970 );
1971
1972 CALL CG$ERRORS_raise_failure;
1973
1974 END;
1975 /* EXCEPTION HANDLING end */
1976
1977 CALL CG$CUSTOMERS_up_autogen_columns(pCUS_ID,'INS',pDO_INS);
1978
1979 IF (pDO_INS) THEN
1980
1981 INSERT INTO CUSTOMERS(
1982 CUS_EMAIL,
1983 CUS_FIRSTNAME,
1984 CUS_LASTNAME,
1985 CUS_INSERTEDBY,
1986 CUS_UPDATEDBY,
1987 CUS_CREATED,
1988 CUS_CREATEDBY,
1989 CUS_MODIFIED,
1990 CUS_MODIFIEDBY)
1991 VALUES(
1992 @vitCUS_EMAIL,
1993 @vitCUS_FIRSTNAME,
1994 @vitCUS_LASTNAME,
1995 @vitCUS_INSERTEDBY,
1996 @vitCUS_UPDATEDBY,
1997 @viCUS_CREATED,
1998 @viCUS_CREATEDBY,
1999 @viCUS_MODIFIED,
2000 @viCUS_MODIFIEDBY);
2001
2002 END IF;
2003
2004END */$$
2005DELIMITER ;
2006
2007/* Procedure structure for procedure `CG$CUSTOMERS_lck` */
2008
2009/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_lck` */;
2010
2011DELIMITER $$
2012
2013/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_lck`(pCUS_ID INT(38), nowait_flag BOOLEAN)
2014BEGIN
2015 -- AR 180215 : Work in Progress
2016 DECLARE vCUS_ID INT(38);
2017 DECLARE vCUS_EMAIL VARCHAR(100);
2018 DECLARE vCUS_FIRSTNAME VARCHAR(50);
2019 DECLARE vCUS_LASTNAME VARCHAR(50);
2020
2021 DECLARE vCUS_INSERTEDBY VARCHAR(50);
2022 DECLARE vCUS_UPDATEDBY VARCHAR(50);
2023 DECLARE vCUS_CREATED DATETIME(6);
2024 DECLARE vCUS_CREATEDBY VARCHAR(50);
2025 DECLARE vCUS_MODIFIED DATETIME(6);
2026 DECLARE vCUS_MODIFIEDBY VARCHAR(50);
2027 /* EXCEPTION HANDLING start */
2028 -- OTHER exceptions
2029 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2030 BEGIN
2031
2032 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2033
2034 CALL CG$ERRORS_push(@text,
2035 'E',
2036 'API',
2037 @errno,
2038 'cg$CUSTOMERS_lck.others'
2039 );
2040
2041 CALL CG$ERRORS_raise_failure;
2042
2043 END;
2044 /* EXCEPTION HANDLING end */
2045
2046
2047 IF (nowait_flag) THEN
2048
2049 #LOCK is not allowed in stored procedures : LOCK outside the procedure
2050 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
2051 IF pCUS_ID IS NOT NULL THEN
2052
2053 SELECT
2054 CUS_ID,
2055 CUS_EMAIL,
2056 CUS_FIRSTNAME,
2057 CUS_LASTNAME,
2058 CUS_INSERTEDBY,
2059 CUS_UPDATEDBY,
2060 CUS_CREATED,
2061 CUS_CREATEDBY,
2062 CUS_MODIFIED,
2063 CUS_MODIFIEDBY
2064 INTO
2065 @vCUS_ID,
2066 @vCUS_EMAIL,
2067 @vCUS_FIRSTNAME,
2068 @vCUS_LASTNAME,
2069 @vCUS_INSERTEDBY,
2070 @vCUS_UPDATEDBY,
2071 @vCUS_CREATED,
2072 @vCUS_CREATEDBY,
2073 @vCUS_MODIFIED,
2074 @vCUS_MODIFIEDBY
2075 FROM CUSTOMERS
2076 WHERE CUSTOMERS.CUS_ID = pCUS_ID
2077 FOR UPDATE;
2078
2079 END IF;
2080
2081 END IF;
2082
2083END */$$
2084DELIMITER ;
2085
2086/* Procedure structure for procedure `CG$CUSTOMERS_slct` */
2087
2088/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_slct` */;
2089
2090DELIMITER $$
2091
2092/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_slct`(pCUS_ID INT(38))
2093BEGIN
2094 -- AR 180215 : Work in Progress
2095 DECLARE vCUS_ID INT(38);
2096 DECLARE vCUS_EMAIL VARCHAR(100);
2097 DECLARE vCUS_FIRSTNAME VARCHAR(50);
2098 DECLARE vCUS_LASTNAME VARCHAR(50);
2099
2100 DECLARE vCUS_INSERTEDBY VARCHAR(50);
2101 DECLARE vCUS_UPDATEDBY VARCHAR(50);
2102 DECLARE vCUS_CREATED DATETIME(6);
2103 DECLARE vCUS_CREATEDBY VARCHAR(50);
2104 DECLARE vCUS_MODIFIED DATETIME(6);
2105 DECLARE vCUS_MODIFIEDBY VARCHAR(50);
2106 /* EXCEPTION HANDLING start */
2107 -- OTHER exceptions
2108 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2109 BEGIN
2110
2111 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2112
2113 CALL CG$ERRORS_push(@text,
2114 'E',
2115 'API',
2116 @errno,
2117 'cg$CUSTOMERS_slct.others'
2118 );
2119
2120 CALL CG$ERRORS_raise_failure;
2121
2122 END;
2123 /* EXCEPTION HANDLING end */
2124
2125
2126 IF pCUS_ID IS NOT NULL THEN
2127
2128 SELECT
2129 CUS_ID,
2130 CUS_EMAIL,
2131 CUS_FIRSTNAME,
2132 CUS_LASTNAME,
2133 CUS_INSERTEDBY,
2134 CUS_UPDATEDBY,
2135 CUS_CREATED,
2136 CUS_CREATEDBY,
2137 CUS_MODIFIED,
2138 CUS_MODIFIEDBY
2139 INTO
2140 @vCUS_ID,
2141 @vCUS_EMAIL,
2142 @vCUS_FIRSTNAME,
2143 @vCUS_LASTNAME,
2144 @vCUS_INSERTEDBY,
2145 @vCUS_UPDATEDBY,
2146 @vCUS_CREATED,
2147 @vCUS_CREATEDBY,
2148 @vCUS_MODIFIED,
2149 @vCUS_MODIFIEDBY
2150 FROM CUSTOMERS
2151 WHERE CUSTOMERS.CUS_ID = pCUS_ID;
2152
2153 END IF;
2154
2155END */$$
2156DELIMITER ;
2157
2158/* Procedure structure for procedure `CG$CUSTOMERS_upd` */
2159
2160/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_upd` */;
2161
2162DELIMITER $$
2163
2164/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_upd`(pCUS_ID INT(38), pDO_UPD BOOLEAN)
2165BEGIN
2166 -- AR 180215 : Work in Progress
2167/* EXCEPTION HANDLING start */
2168 -- OTHER exceptions
2169 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2170 BEGIN
2171
2172 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2173
2174 CALL CG$ERRORS_push(@text,
2175 'E',
2176 'API',
2177 @errno,
2178 'cg$CUSTOMERS_upd.others'
2179 );
2180
2181 CALL CG$ERRORS_raise_failure;
2182
2183 END;
2184 /* EXCEPTION HANDLING end */
2185
2186 #CALL CG$CUSTOMERS_slct(pCUS_ID);
2187 /* Not Needed Anymore
2188 * Checking: IF NEW COLUMN VALUE IS NOT NULL : O/W RAISE EXCEPTION
2189 * IF @vutCUS_EMAIL IS NOT NULL AND (CHAR_LENGTH(TRIM(@vutCUS_EMAIL)) > 0) THEN
2190 * SET @vuCUS_EMAIL = @vutCUS_EMAIL;
2191 * ELSE
2192 * SIGNAL SQLSTATE '22004' SET
2193 * MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column(CUS_EMAIL)';
2194 * END IF;
2195 */
2196
2197 /*
2198 * Fetching VALUE OF MODIFIED & MODIFIEDBY
2199 */
2200 CALL CG$CUSTOMERS_up_autogen_columns(pCUS_ID,'UPD',pDO_UPD);
2201
2202 IF (pDO_UPD) THEN
2203
2204 UPDATE
2205 CUSTOMERS
2206 SET
2207 CUS_EMAIL = @vutCUS_EMAIL,
2208 CUS_FIRSTNAME = @vutCUS_FIRSTNAME,
2209 CUS_LASTNAME = @vutCUS_LASTNAME,
2210 CUS_INSERTEDBY = @vutCUS_INSERTEDBY,
2211 CUS_UPDATEDBY = @vutCUS_UPDATEDBY,
2212 CUS_MODIFIED = @viCUS_MODIFIED,
2213 CUS_MODIFIEDBY = @viCUS_MODIFIEDBY
2214 WHERE
2215 CUS_ID = pCUS_ID;
2216
2217 END IF;
2218
2219 #CALL CG$CUSTOMERS_slct(pCUS_ID);
2220
2221END */$$
2222DELIMITER ;
2223
2224/* Procedure structure for procedure `CG$CUSTOMERS_up_autogen_columns` */
2225
2226/*!50003 DROP PROCEDURE IF EXISTS `CG$CUSTOMERS_up_autogen_columns` */;
2227
2228DELIMITER $$
2229
2230/*!50003 CREATE PROCEDURE `CG$CUSTOMERS_up_autogen_columns`(pCUS_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
2231BEGIN
2232
2233 -- AR 180215 : Work in Progress
2234 DECLARE viCUS_CREATED DATETIME(6);
2235 DECLARE viCUS_CREATEDBY VARCHAR(50);
2236 DECLARE viCUS_MODIFIED DATETIME(6);
2237 DECLARE viCUS_MODIFIEDBY VARCHAR(50);
2238 /* EXCEPTION HANDLING start */
2239 -- OTHER exceptions
2240 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2241 BEGIN
2242
2243 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2244
2245 CALL CG$ERRORS_push(@text,
2246 'E',
2247 'API',
2248 @errno,
2249 'cg$CUSTOMERS_up_autogen_columns.others'
2250 );
2251
2252 CALL CG$ERRORS_raise_failure;
2253
2254 END;
2255 /* EXCEPTION HANDLING end */
2256
2257
2258 /*
2259 * MariaDB does not have DEFAULT variable. So IFNULL is used
2260 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
2261 */
2262 SET operation = IFNULL(operation, 'INS');
2263
2264 IF (operation = 'INS') THEN
2265 /*
2266 * CUS_ID is already an Auto-Increment field
2267 * IF (pCUS_ID IS NOT NULL AND @vitCUS_CREATED = TRUE) THEN
2268 * SELECT LAST_INSERT_ID() INTO @vitCUS_ID FROM CUSTOMERS;
2269 * END IF;
2270 */
2271
2272 SET @viCUS_CREATED = SYSDATE();
2273 SET @viCUS_CREATEDBY = USER();
2274
2275 END IF;
2276
2277 SET @viCUS_MODIFIED = SYSDATE();
2278 SET @viCUS_MODIFIEDBY = USER();
2279
2280END */$$
2281DELIMITER ;
2282
2283/* Procedure structure for procedure `CG$DOCUMENTDATA_del` */
2284
2285/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_del` */;
2286
2287DELIMITER $$
2288
2289/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_del`(pDOD_ID INT(38), pDO_DEL BOOLEAN)
2290BEGIN
2291
2292 DECLARE vDOD_ID INT(38);
2293
2294 /* EXCEPTION HANDLING start */
2295
2296 -- delete_restrict : An attempt was made to delete a row that is referenced by a foreign key.
2297 DECLARE EXIT HANDLER FOR 1451
2298 BEGIN
2299
2300 -- MariaDB generated error message is too long for condition item 'MESSAGE_TEXT' in SIGNAL - varchar(128)
2301 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2302 CALL CG$ERRORS_push(@text,
2303 'E',
2304 'API',
2305 @errno,
2306 'cg$DOCUMENTDATA_del.delete_restrict');
2307 CALL CG$ERRORS_raise_failure;
2308
2309 END;
2310
2311 -- OTHER exceptions
2312 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2313 BEGIN
2314
2315 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2316
2317 CALL CG$ERRORS_push(@text,
2318 'E',
2319 'API',
2320 @errno,
2321 'cg$DOCUMENTDATA_del.others');
2322
2323 CALL CG$ERRORS_raise_failure;
2324
2325 END;
2326
2327 SET vDOD_ID := pDOD_ID;
2328 SET pDO_DEL = IFNULL(pDO_DEL, TRUE);
2329
2330 /* Delete the record */
2331 IF (pDO_DEL) THEN
2332 BEGIN
2333
2334 IF pDOD_ID IS NOT NULL THEN
2335 DELETE FROM DOCUMENTDATA WHERE DOD_ID = pDOD_ID;
2336 END IF;
2337
2338 END;
2339
2340 END IF;
2341
2342 /*insert into DOCUMENTDATA_JN table */
2343 CALL CG$DOCUMENTDATA_insert_jn(pDOD_ID, 'DEL');
2344
2345END */$$
2346DELIMITER ;
2347
2348/* Procedure structure for procedure `CG$DOCUMENTDATA_err_msg` */
2349
2350/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_err_msg` */;
2351
2352DELIMITER $$
2353
2354/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_err_msg`(msg VARCHAR(240), error_type INTEGER, loc VARCHAR(240))
2355BEGIN
2356
2357 DECLARE con_name VARCHAR(240);
2358 DECLARE APIMSG_UK_VIOLAT VARCHAR(240);
2359 DECLARE API_UQ_CON_VIOLATED INTEGER;
2360
2361 SET loc = IFNULL(loc, '');
2362 SET APIMSG_UK_VIOLAT := 'Unique constraint <p1> on table <p2> violated';
2363 SET API_UQ_CON_VIOLATED := 1006;
2364 /*
2365 * this procedure/function is not needed as gets the column name from a specific oracle error such as:
2366 * ORA-00001: unique constraint (PMS.DOD_ID) violated
2367 * MariaDB gives out error in a user readable format. E.G.
2368 * Duplicate entry 'Test-dup' for key 'ROL_UK'
2369 * references:
2370 * http://www.dba-oracle.com/sf_ora_00001_unique_constraint_violated.htm
2371 * http://www.fromdual.com/mysql-error-codes-and-messages-1550-1599
2372 * mariadb error no 1586
2373 */
2374 /*
2375 set con_name := CG$ERRORS_parse_constraint(msg, error_type);
2376
2377 IF (con_name = 'DOD_PK') THEN
2378 CALL CG$ERRORS_push(nvl(DOD_PK
2379 ,CG$ERRORS_MsgGetText(API_PK_CON_VIOLATED,
2380 APIMSG_PK_VIOLAT,
2381 'DOD_PK','DOCUMENTDATA',NULL, NULL)),
2382 'E',
2383 'API',
2384 API_PK_CON_VIOLATED,
2385 loc);
2386
2387 ELSEIF (con_name = 'DOD_LII_UK') THEN
2388 CALL CG$ERRORS_push(nvl(DOD_PK
2389 ,CG$ERRORS_MsgGetText(API_UQ_CON_VIOLATED,
2390 APIMSG_UK_VIOLAT,
2391 'DOD_LII_UK','DOCUMENTDATA',NULL, NULL)),
2392 'E',
2393 'API',
2394 API_UQ_CON_VIOLATED,
2395 loc);
2396
2397 ELSEIF (con_name = 'DOD_ORI_UK') THEN
2398 CALL CG$ERRORS_push(nvl(DOD_PK
2399 ,CG$ERRORS_MsgGetText(API_UQ_CON_VIOLATED,
2400 APIMSG_UK_VIOLAT,
2401 'DOD_ORI_UK','DOCUMENTDATA',NULL, NULL)),
2402 'E',
2403 'API',
2404 API_UQ_CON_VIOLATED,
2405 loc);
2406
2407
2408
2409 ELSEIF (con_name = 'DOD_ORI_FK') THEN
2410 CALL CG$ERRORS_push(nvl(DOD_PK
2411 ,CG$ERRORS_MsgGetText(API_UQ_CON_VIOLATED,
2412 APIMSG_UK_VIOLAT,
2413 'DOD_ORI_FK','DOCUMENTDATA',NULL, NULL)),
2414 'E',
2415 'API',
2416 API_UQ_CON_VIOLATED,
2417 loc);
2418
2419 ELSEIF (con_name = 'DOD_RQI_FK') THEN
2420 CALL CG$ERRORS_push(nvl(DOD_PK
2421 ,CG$ERRORS_MsgGetText(API_UQ_CON_VIOLATED,
2422 APIMSG_UK_VIOLAT,
2423 'DOD_RQI_FK','DOCUMENTDATA',NULL, NULL)),
2424 'E',
2425 'API',
2426 API_UQ_CON_VIOLATED,
2427 loc);
2428
2429
2430 ELSEIF (con_name = 'DOD_LII_FK') THEN
2431 CALL CG$ERRORS_push(nvl(DOD_PK
2432 ,CG$ERRORS_MsgGetText(API_UQ_CON_VIOLATED,
2433 APIMSG_UK_VIOLAT,
2434 'DOD_LII_FK','DOCUMENTDATA',NULL, NULL)),
2435 'E',
2436 'API',
2437 API_UQ_CON_VIOLATED,
2438 loc);
2439 ELSE
2440 */
2441 CALL CG$ERRORS_push(msg,
2442 'E',
2443 'API',
2444 error_type,
2445 loc);
2446 -- END IF;
2447
2448END */$$
2449DELIMITER ;
2450
2451/* Procedure structure for procedure `CG$DOCUMENTDATA_raise_fk_not_transferable` */
2452
2453/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_raise_fk_not_transferable` */;
2454
2455DELIMITER $$
2456
2457/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_raise_fk_not_transferable`(fk VARCHAR(255))
2458BEGIN
2459
2460 DECLARE API_FOREIGN_KEY_TRANS INTEGER;
2461 DECLARE ERR_FK_TRANS VARCHAR(240);
2462
2463 SET API_FOREIGN_KEY_TRANS := 1002;
2464 SET ERR_FK_TRANS := 'Foreign key <p1> not transferable';
2465
2466 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_FOREIGN_KEY_TRANS,
2467 ERR_FK_TRANS,
2468 fk,NULL,NULL, NULL), -- because MsgGetText recieves 6 parameters
2469 'E',
2470 'API',
2471 API_FOREIGN_KEY_TRANS,
2472 'cg$DOCUMENTDATA_raise_fk_not_transferable' );
2473 CALL CG$ERRORS_raise_failure;
2474
2475END */$$
2476DELIMITER ;
2477
2478/* Procedure structure for procedure `CG$DOCUMENTDATA_raise_uk_not_updateable` */
2479
2480/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_raise_uk_not_updateable` */;
2481
2482DELIMITER $$
2483
2484/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_raise_uk_not_updateable`(IN uk VARCHAR(255))
2485BEGIN
2486
2487 DECLARE API_UNIQUE_KEY_UPDATE INTEGER;
2488 DECLARE ERR_UK_UPDATE VARCHAR(240);
2489
2490 SET API_UNIQUE_KEY_UPDATE := 1001;
2491 SET ERR_UK_UPDATE := 'Unique key <p1> not updateable';
2492
2493 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_UNIQUE_KEY_UPDATE,
2494 ERR_UK_UPDATE,
2495 uk,NULL,NULL, NULL), -- because MsgGetText recieves 6 parameters
2496 'E',
2497 'API',
2498 API_UNIQUE_KEY_UPDATE,
2499 'cg$DOCUMENTDATA_raise_uk_not_updateable' );
2500 CALL CG$ERRORS_raise_failure;
2501END */$$
2502DELIMITER ;
2503
2504/* Procedure structure for procedure `CG$DOCUMENTDATA_up_autogen_columns` */
2505
2506/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_up_autogen_columns` */;
2507
2508DELIMITER $$
2509
2510/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_up_autogen_columns`(pDOD_ID INT(38), pOPERATION VARCHAR(30), do_denorm BOOLEAN )
2511BEGIN
2512
2513 -- TAT : Work in Progress
2514 DECLARE viDOD_INSERTEDBY VARCHAR(50);
2515 DECLARE viDOD_CREATEDBY VARCHAR(50);
2516 DECLARE viDOD_CREATED DATETIME(6);
2517
2518 DECLARE viDOD_ORI_INSERTEDBY VARCHAR(50);
2519 DECLARE viDOD_ORI_CREATED DATETIME(6);
2520 DECLARE viDOD_ORI_CREATEDBY VARCHAR(50);
2521
2522 DECLARE viDOD_LII_INSERTEDBY VARCHAR(50);
2523 DECLARE viDOD_LII_CREATEDBY VARCHAR(50);
2524 DECLARE viDOD_LII_CREATED DATETIME(6);
2525
2526 DECLARE viDOD_MODIFIEDBY VARCHAR(50);
2527 DECLARE viDOD_UPDATEDBY VARCHAR(50);
2528 DECLARE viDOD_MODIFIED DATETIME(6);
2529
2530 DECLARE viDOD_ORI_MODIFIEDBY VARCHAR(50);
2531 DECLARE viDOD_ORI_UPDATEDBY VARCHAR(50);
2532 DECLARE viDOD_ORI_MODIFIED DATETIME(6);
2533
2534 DECLARE viDOD_LII_MODIFIEDBY VARCHAR(50);
2535 DECLARE viDOD_LII_UPDATEDBY VARCHAR(50);
2536 DECLARE viDOD_LII_MODIFIED DATETIME(6);
2537
2538
2539 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2540 BEGIN
2541
2542 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2543 CALL CG$ERRORS_push(@text,
2544 'E',
2545 'API',
2546 @errno,
2547 'cg$DOCUMENTDATA_up_autogen_columns');
2548 CALL CG$ERRORS_raise_failure;
2549
2550 END;
2551 /*
2552 * MariaDB does not have DEFAULT variable. So IFNULL is used
2553 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
2554 */
2555 SET pOPERATION = IFNULL(pOPERATION, 'INS');
2556 SET do_denorm = IFNULL(do_denorm, TRUE);
2557
2558 IF (pOPERATION = 'INS') THEN
2559
2560 /*
2561 * DOD_ID is already an Auto-Increment field
2562 * IF (pDOD_ID is NULL) THEN
2563 * SELECT LAST_INSERT_ID() INTO @vitDOD_ID FROM DUAL;
2564 * END IF;
2565 */
2566 SET @viDOD_INSERTEDBY := USER();
2567 SET @viDOD_CREATEDBY := USER(); /* Server Derived Created By */
2568 SET @viDOD_CREATED := SYSDATE(); /* Server Derived Date Created */
2569 SET @viDOD_ORI_INSERTEDBY := USER();
2570 SET @viDOD_ORI_CREATED := SYSDATE();
2571 SET @viDOD_ORI_CREATEDBY := USER();
2572
2573 SET @viDOD_LII_INSERTEDBY := NULL;
2574 SET @viDOD_LII_CREATEDBY := NULL;
2575 SET @viDOD_LII_CREATED := NULL;
2576
2577
2578 END IF;
2579
2580 /* Statements executed for both 'INS' and 'UPD' */
2581 SET @viDOD_MODIFIEDBY := USER();
2582 SET @viDOD_UPDATEDBY := USER();
2583 SET @viDOD_MODIFIED := SYSDATE();
2584
2585 SET @viDOD_ORI_MODIFIEDBY := USER();
2586 SET @viDOD_ORI_UPDATEDBY := USER();
2587 SET @viDOD_ORI_MODIFIED := SYSDATE();
2588
2589 IF (pOPERATION = 'UPD') THEN
2590 -- Logic for DOD_LII update
2591 IF (@vutDOD_LII_ID IS NOT NULL AND @vutDOD_ORI_STATUS = 'N') THEN
2592
2593 SET @viDOD_LII_INSERTEDBY := USER();
2594 SET @viDOD_LII_CREATEDBY := USER();
2595 SET @viDOD_LII_CREATED := SYSDATE();
2596
2597 ELSEIF (@vutDOD_LII_ID IS NOT NULL AND @vutDOD_ORI_STATUS != 'N') THEN
2598
2599 SET @viDOD_LII_MODIFIEDBY := USER();
2600 SET @viDOD_LII_UPDATEDBY := USER();
2601 SET @viDOD_LII_MODIFIED := SYSDATE();
2602
2603 END IF;
2604 END IF;
2605
2606END */$$
2607DELIMITER ;
2608
2609/* Procedure structure for procedure `CG$DOCUMENTDATA_ins` */
2610
2611/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_ins` */;
2612
2613DELIMITER $$
2614
2615/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_ins`(pDOD_ID INT(38), pDO_INS BOOLEAN)
2616BEGIN
2617 /*
2618 * MariaDB exceptions are handled with HANDLERS which needs to be declared after begin
2619 * cursor or handler declaration needs to be after Variable or condition declaration
2620 */
2621
2622 /* EXCEPTION HANDLING start */
2623 /*
2624 * check violation
2625 * MariaDB checks constraints before trigger fires. So no equivalent error code or Handler
2626 */
2627 -- fk_violation -- equivalent to ORA-02291
2628 -- A foreign key value has no matching primary key value.
2629 DECLARE EXIT HANDLER FOR 1452
2630 BEGIN
2631 -- MariaDB generated error message is too long for condition item 'MESSAGE_TEXT' in SIGNAL - varchar(128)
2632 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2633 CALL CG$DOCUMENTDATA_err_msg(@text, @errno, 'cg$DOCUMENTDATA_ins.fk_violation');
2634 CALL CG$ERRORS_raise_failure;
2635 END;
2636
2637 -- uk_violation -- equivalent to ORA-00001
2638 -- An UPDATE or INSERT statement attempted to insert a duplicate key.
2639 -- TAPI-1062:Duplicate entry 'Test-dup' for key 'ROL_UK'
2640 DECLARE EXIT HANDLER FOR 1062
2641 BEGIN
2642 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2643 CALL CG$DOCUMENTDATA_err_msg(@text, @errno, 'cg$DOCUMENTDATA_ins.uk_violation');
2644 CALL CG$ERRORS_raise_failure;
2645 END;
2646
2647
2648 -- any other error
2649 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2650 BEGIN
2651 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2652 CALL CG$ERRORS_push(@text,
2653 'E',
2654 'API',
2655 @errno,
2656 'cg$DOCUMENTDATA_ins.others');
2657 CALL CG$ERRORS_raise_failure;
2658 END;
2659
2660 /* EXCEPTION HANDLING end */
2661
2662 /*
2663 * MariaDB does not have DEFAULT variable. So IFNULL is used
2664 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
2665 */
2666 SET pDO_INS = IFNULL(pDO_INS, TRUE);
2667
2668 -- default values
2669 IF (@vitDOD_KEY_PUBLICATION IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_KEY_PUBLICATION)) = 0)) THEN
2670 SET @vitDOD_KEY_PUBLICATION := 'N';
2671 END IF;
2672
2673 IF (@vitDOD_CLINICAL_ARTICLE IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_CLINICAL_ARTICLE)) = 0)) THEN
2674 SET @vitDOD_CLINICAL_ARTICLE := 'N';
2675 END IF;
2676
2677 IF (@vitDOD_LII_HIDDEN IS NULL OR (CHAR_LENGTH(TRIM(@vitDOD_LII_HIDDEN)) = 0)) THEN
2678 SET @vitDOD_LII_HIDDEN := 'N';
2679 END IF;
2680
2681 CALL CG$DOCUMENTDATA_up_autogen_columns(pDOD_ID, 'INS', pDO_INS);
2682
2683
2684 IF (pDO_INS) THEN
2685 CALL CG$DOCUMENTDATA_validate_domain(@vitDOD_DOCTYPE);
2686
2687 INSERT INTO DOCUMENTDATA(
2688 DOD_ID
2689 ,DOD_ORI_ID
2690 ,DOD_LII_ID
2691 ,DOD_DOCTYPE
2692 ,DOD_TITLE
2693 ,DOD_JOURNAL_NAME
2694 ,DOD_PUBL_YEAR
2695 ,DOD_AUTHORS
2696 ,DOD_VOLUME
2697 ,DOD_ISSUE
2698 ,DOD_PAGEINFO
2699 ,DOD_LANGUAGE
2700 ,DOD_KEYWORDS
2701 ,DOD_AFFILIATION
2702 ,DOD_ABSTRACT
2703 ,DOD_PMID
2704 ,DOD_DOI
2705 ,DOD_ISSN
2706 ,DOD_MESH_TERMS
2707 ,DOD_SUBSTANCES
2708 ,DOD_PUB_TYPE
2709 ,DOD_EDITOR_NAME
2710 ,DOD_BOOKTITLE
2711 ,DOD_PUBLISHER
2712 ,DOD_ISBN
2713 ,DOD_CONF_NAME
2714 ,DOD_SPONSOR
2715 ,DOD_PAT_APPL_DATE
2716 ,DOD_PAT_APPL_NUM
2717 ,DOD_PAT_PRIO_DATES
2718 ,DOD_PAT_PRIO_NUM
2719 ,DOD_PAT_PUBL_DATE
2720 ,DOD_PAT_PUBL_NUM
2721 ,DOD_URL
2722 ,DOD_PRODUCT
2723 ,DOD_INDICATION
2724 ,DOD_KEY_PUBLICATION
2725 ,DOD_CLINICAL_ARTICLE
2726 ,DOD_COMMENT
2727 ,DOD_INSERTEDBY
2728 ,DOD_UPDATEDBY
2729 ,DOD_CREATED
2730 ,DOD_CREATEDBY
2731 ,DOD_MODIFIED
2732 ,DOD_MODIFIEDBY
2733 ,DOD_LII_COMMENT
2734 ,DOD_LII_HIDDEN
2735 ,DOD_LII_INSERTEDBY
2736 ,DOD_LII_UPDATEDBY
2737 ,DOD_LII_CREATED
2738 ,DOD_LII_CREATEDBY
2739 ,DOD_LII_MODIFIED
2740 ,DOD_LII_MODIFIEDBY
2741 ,DOD_ORI_DUPE_OF_ORI_ID
2742 ,DOD_ORI_ORDERID
2743 ,DOD_ORI_PDF_DELIVERY_DATE
2744 ,DOD_ORI_STATUS
2745 ,DOD_ORI_COMMENT
2746 ,DOD_ORI_URGENCY
2747 ,DOD_ORI_MAILBODY
2748 ,DOD_ORI_ACTPUBMED
2749 ,DOD_ORI_CREATEDBY
2750 ,DOD_ORI_INSERTEDBY
2751 ,DOD_ORI_UPDATEDBY
2752 ,DOD_ORI_MODIFIEDBY
2753 ,DOD_ORI_CREATED
2754 ,DOD_ORI_MODIFIED
2755 )
2756 VALUES(
2757 @vitDOD_ID
2758 ,@vitDOD_ORI_ID
2759 ,@vitDOD_LII_ID
2760 ,@vitDOD_DOCTYPE
2761 ,@vitDOD_TITLE
2762 ,@vitDOD_JOURNAL_NAME
2763 ,@vitDOD_PUBL_YEAR
2764 ,@vitDOD_AUTHORS
2765 ,@vitDOD_VOLUME
2766 ,@vitDOD_ISSUE
2767 ,@vitDOD_PAGEINFO
2768 ,@vitDOD_LANGUAGE
2769 ,@vitDOD_KEYWORDS
2770 ,@vitDOD_AFFILIATION
2771 ,@vitDOD_ABSTRACT
2772 ,@vitDOD_PMID
2773 ,@vitDOD_DOI
2774 ,@vitDOD_ISSN
2775 ,@vitDOD_MESH_TERMS
2776 ,@vitDOD_SUBSTANCES
2777 ,@vitDOD_PUB_TYPE
2778 ,@vitDOD_EDITOR_NAME
2779 ,@vitDOD_BOOKTITLE
2780 ,@vitDOD_PUBLISHER
2781 ,@vitDOD_ISBN
2782 ,@vitDOD_CONF_NAME
2783 ,@vitDOD_SPONSOR
2784 ,@vitDOD_PAT_APPL_DATE
2785 ,@vitDOD_PAT_APPL_NUM
2786 ,@vitDOD_PAT_PRIO_DATES
2787 ,@vitDOD_PAT_PRIO_NUM
2788 ,@vitDOD_PAT_PUBL_DATE
2789 ,@vitDOD_PAT_PUBL_NUM
2790 ,@vitDOD_URL
2791 ,@vitDOD_PRODUCT
2792 ,@vitDOD_INDICATION
2793 ,@vitDOD_KEY_PUBLICATION
2794 ,@vitDOD_CLINICAL_ARTICLE
2795 ,@vitDOD_COMMENT
2796 ,@vitDOD_INSERTEDBY
2797 ,@vitDOD_UPDATEDBY
2798 ,@vitDOD_CREATED
2799 ,@vitDOD_CREATEDBY
2800 ,@vitDOD_MODIFIED
2801 ,@vitDOD_MODIFIEDBY
2802 ,@vitDOD_LII_COMMENT
2803 ,@vitDOD_LII_HIDDEN
2804 ,@vitDOD_LII_INSERTEDBY
2805 ,@vitDOD_LII_UPDATEDBY
2806 ,@vitDOD_LII_CREATED
2807 ,@vitDOD_LII_CREATEDBY
2808 ,@vitDOD_LII_MODIFIED
2809 ,@vitDOD_LII_MODIFIEDBY
2810 ,@vitDOD_ORI_DUPE_OF_ORI_ID
2811 ,@vitDOD_ORI_ORDERID
2812 ,@vitDOD_ORI_PDF_DELIVERY_DATE
2813 ,@vitDOD_ORI_STATUS
2814 ,@vitDOD_ORI_COMMENT
2815 ,@vitDOD_ORI_URGENCY
2816 ,@vitDOD_ORI_MAILBODY
2817 ,@vitDOD_ORI_ACTPUBMED
2818 ,@vitDOD_ORI_CREATEDBY
2819 ,@vitDOD_ORI_INSERTEDBY
2820 ,@vitDOD_ORI_UPDATEDBY
2821 ,@vitDOD_ORI_MODIFIEDBY
2822 ,@vitDOD_ORI_CREATED
2823 ,@vitDOD_ORI_MODIFIED
2824 );
2825
2826 -- CALL CG$DOCUMENTDATA_slct(pDOD_ID);
2827 END IF;
2828
2829 CALL CG$DOCUMENTDATA_insert_jn(pDOD_ID,'INS');
2830
2831END */$$
2832DELIMITER ;
2833
2834/* Procedure structure for procedure `CG$DOCUMENTDATA_upd` */
2835
2836/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_upd` */;
2837
2838DELIMITER $$
2839
2840/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_upd`(pDOD_ID INT(38), pDO_UPD BOOLEAN)
2841BEGIN
2842
2843 -- Error messages declaration
2844 DECLARE ERR_FOREIGN_KEY INTEGER;
2845 DECLARE ERR_FOREIGN_KEY_MSG VARCHAR(128);
2846
2847 /*
2848 * MariaDB exceptions are handled with HANDLERS which needs to be declared after begin
2849 * cursor or handler declaration needs to be after Variable or condition declaration
2850 */
2851
2852 /* EXCEPTION HANDLING start */
2853 /*
2854 * check violation
2855 * MariaDB checks constraints before trigger fires. So no equivalent error code or Handler
2856 */
2857
2858 -- fk_violation -- equivalent to ORA-02291
2859 -- A foreign key value has no matching primary key value.
2860 DECLARE EXIT HANDLER FOR 1452
2861 BEGIN
2862 -- MariaDB generated error message is too long for condition item 'MESSAGE_TEXT' in SIGNAL - varchar(128)
2863 SET ERR_FOREIGN_KEY := 1452;
2864 SET ERR_FOREIGN_KEY_MSG = "A foreign key value has no matching primary key value";
2865 CALL CG$DOCUMENTDATA_err_msg(ERR_FOREIGN_KEY_MSG, ERR_FOREIGN_KEY, 'cg$DOCUMENTDATA_ins.fk_violation');
2866 CALL CG$ERRORS_raise_failure;
2867 END;
2868
2869 -- uk_violation -- equivalent to ORA-00001
2870 -- An UPDATE or INSERT statement attempted to insert a duplicate key.
2871 -- TAPI-1062:Duplicate entry 'Test-dup' for key 'ROL_UK'
2872 DECLARE EXIT HANDLER FOR 1062
2873 BEGIN
2874 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2875 CALL CG$DOCUMENTDATA_err_msg(@text, @errno, 'cg$DOCUMENTDATA_ins.uk_violation');
2876 CALL CG$ERRORS_raise_failure;
2877 END;
2878
2879 -- any other error
2880 DECLARE EXIT HANDLER FOR SQLEXCEPTION
2881 BEGIN
2882 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
2883 CALL CG$ERRORS_push(@text,
2884 'E',
2885 'API',
2886 @errno,
2887 'cg$DOCUMENTDATA_upd.others');
2888 CALL CG$ERRORS_raise_failure;
2889 END;
2890
2891 /* EXCEPTION HANDLING end */
2892
2893 SET pDO_UPD = IFNULL(pDO_UPD, TRUE);
2894
2895 IF NOT ( pDO_UPD ) THEN
2896
2897 /* Report error if attempt to update non updateable Primary Key DOD_ID */
2898 IF (@vutDOD_ID_upd) THEN
2899
2900 CALL CG$DOCUMENTDATA_raise_uk_not_updateable('DOD_ID');
2901
2902 END IF;
2903
2904 END IF;
2905
2906 /*
2907 * Fetching VALUE OF MODIFIED & MODIFIEDBY
2908 */
2909 CALL CG$DOCUMENTDATA_up_autogen_columns(pDOD_ID, 'UPD', pDO_UPD);
2910
2911 -- Now do update if updateable columns exist
2912 IF (pDO_UPD) THEN
2913
2914 BEGIN
2915
2916 CALL cg$DOCUMENTDATA_validate_domain(@vutDOD_DOCTYPE);
2917
2918 IF pDOD_ID IS NOT NULL THEN
2919
2920 UPDATE DOCUMENTDATA
2921 SET
2922 DOD_ORI_ID = @vutDOD_ORI_ID
2923 ,DOD_LII_ID = @vutDOD_LII_ID
2924 ,DOD_DOCTYPE = @vutDOD_DOCTYPE
2925 ,DOD_TITLE = @vutDOD_TITLE
2926 ,DOD_JOURNAL_NAME = @vutDOD_JOURNAL_NAME
2927 ,DOD_PUBL_YEAR = @vutDOD_PUBL_YEAR
2928 ,DOD_AUTHORS = @vutDOD_AUTHORS
2929 ,DOD_VOLUME = @vutDOD_VOLUME
2930 ,DOD_ISSUE = @vutDOD_ISSUE
2931 ,DOD_PAGEINFO = @vutDOD_PAGEINFO
2932 ,DOD_LANGUAGE = @vutDOD_LANGUAGE
2933 ,DOD_KEYWORDS = @vutDOD_KEYWORDS
2934 ,DOD_AFFILIATION = @vutDOD_AFFILIATION
2935 ,DOD_ABSTRACT = @vutDOD_ABSTRACT
2936 ,DOD_PMID = @vutDOD_PMID
2937 ,DOD_DOI = @vutDOD_DOI
2938 ,DOD_ISSN = @vutDOD_ISSN
2939 ,DOD_MESH_TERMS = @vutDOD_MESH_TERMS
2940 ,DOD_SUBSTANCES = @vutDOD_SUBSTANCES
2941 ,DOD_PUB_TYPE = @vutDOD_PUB_TYPE
2942 ,DOD_EDITOR_NAME = @vutDOD_EDITOR_NAME
2943 ,DOD_BOOKTITLE = @vutDOD_BOOKTITLE
2944 ,DOD_PUBLISHER = @vutDOD_PUBLISHER
2945 ,DOD_ISBN = @vutDOD_ISBN
2946 ,DOD_CONF_NAME = @vutDOD_CONF_NAME
2947 ,DOD_SPONSOR = @vutDOD_SPONSOR
2948 ,DOD_PAT_APPL_DATE = @vutDOD_PAT_APPL_DATE
2949 ,DOD_PAT_APPL_NUM = @vutDOD_PAT_APPL_NUM
2950 ,DOD_PAT_PRIO_DATES = @vutDOD_PAT_PRIO_DATES
2951 ,DOD_PAT_PRIO_NUM = @vutDOD_PAT_PRIO_NUM
2952 ,DOD_PAT_PUBL_DATE = @vutDOD_PAT_PUBL_DATE
2953 ,DOD_PAT_PUBL_NUM = @vutDOD_PAT_PUBL_NUM
2954 ,DOD_URL = @vutDOD_URL
2955 ,DOD_PRODUCT = @vutDOD_PRODUCT
2956 ,DOD_INDICATION = @vutDOD_INDICATION
2957 ,DOD_KEY_PUBLICATION = @vutDOD_KEY_PUBLICATION
2958 ,DOD_CLINICAL_ARTICLE = @vutDOD_CLINICAL_ARTICLE
2959 ,DOD_COMMENT = @vutDOD_COMMENT
2960 ,DOD_INSERTEDBY = @vutDOD_INSERTEDBY
2961 ,DOD_UPDATEDBY = @vutDOD_UPDATEDBY
2962 ,DOD_CREATED = @vutDOD_CREATED
2963 ,DOD_CREATEDBY = @vutDOD_CREATEDBY
2964 ,DOD_MODIFIED = @vutDOD_MODIFIED
2965 ,DOD_MODIFIEDBY = @vutDOD_MODIFIEDBY
2966 ,DOD_LII_HIDDEN = @vutDOD_LII_HIDDEN
2967 ,DOD_LII_INSERTEDBY = @vutDOD_LII_INSERTEDBY
2968 ,DOD_LII_UPDATEDBY = @vutDOD_LII_UPDATEDBY
2969 ,DOD_LII_CREATED = @vutDOD_LII_CREATED
2970 ,DOD_LII_CREATEDBY = @vutDOD_LII_CREATEDBY
2971 ,DOD_LII_MODIFIED = @vutDOD_LII_MODIFIED
2972 ,DOD_LII_MODIFIEDBY = @vutDOD_LII_MODIFIEDBY
2973 ,DOD_ORI_DUPE_OF_ORI_ID = @vutDOD_ORI_DUPE_OF_ORI_ID
2974 ,DOD_ORI_ORDERID = @vutDOD_ORI_ORDERID
2975 ,DOD_ORI_PDF_DELIVERY_DATE = @vutDOD_ORI_PDF_DELIVERY_DATE
2976 ,DOD_ORI_STATUS = @vutDOD_ORI_STATUS
2977 ,DOD_ORI_COMMENT = @vutDOD_ORI_COMMENT
2978 ,DOD_ORI_URGENCY = @vutDOD_ORI_URGENCY
2979 ,DOD_ORI_MAILBODY = @vutDOD_ORI_MAILBODY
2980 ,DOD_ORI_ACTPUBMED = @vutDOD_ORI_ACTPUBMED
2981 ,DOD_ORI_CREATEDBY = @vutDOD_ORI_CREATEDBY
2982 ,DOD_ORI_INSERTEDBY = @vutDOD_ORI_INSERTEDBY
2983 ,DOD_ORI_UPDATEDBY = @vutDOD_ORI_UPDATEDBY
2984 ,DOD_ORI_MODIFIEDBY = @vutDOD_ORI_MODIFIEDBY
2985 ,DOD_ORI_CREATED = @vutDOD_ORI_CREATED
2986 ,DOD_ORI_MODIFIED = @vutDOD_ORI_MODIFIED
2987 WHERE DOD_ID = pDOD_ID;
2988
2989 END IF;
2990
2991 END;
2992
2993 END IF;
2994
2995 /*insert into DOCUMENTDATA_JN table */
2996 CALL CG$DOCUMENTDATA_insert_jn(pDOD_ID, 'UPD');
2997
2998END */$$
2999DELIMITER ;
3000
3001/* Procedure structure for procedure `CG$DOCUMENTDATA_validate_domain` */
3002
3003/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_validate_domain` */;
3004
3005DELIMITER $$
3006
3007/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_validate_domain`( pDOD_DOCTYPE VARCHAR(2))
3008BEGIN
3009
3010 DECLARE found_value BOOLEAN;
3011 DECLARE loop_done INT DEFAULT FALSE;
3012 DECLARE c_RV_HIGH_VALUE VARCHAR(100);
3013 DECLARE c_RV_LOW_VALUE VARCHAR(100);
3014
3015 -- error message declaration
3016 DECLARE API_RV_TAB_NOT_FOUND INTEGER;
3017 DECLARE APIMSG_RV_TAB_NOT_FOUND VARCHAR(240);
3018 DECLARE API_RV_LOOKUP_FAIL INTEGER;
3019 DECLARE APIMSG_RV_LOOKUP_FAIL VARCHAR(240);
3020
3021 DECLARE c1 CURSOR FOR
3022 SELECT RV_LOW_VALUE, RV_HIGH_VALUE FROM CG_REF_CODES WHERE RV_DOMAIN = 'DOCUMENTTYPE';
3023
3024 DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done = TRUE; -- declared to determine end of loop
3025
3026 /*
3027 * MariaDB exceptions are handled with HANDLERS which needs to be declared after begin
3028 * cursor or handler declaration needs to be after Variable or condition declaration
3029 */
3030 /* EXCEPTION HANDLING start */
3031 -- view doesn't exist error
3032 DECLARE EXIT HANDLER FOR 1029
3033 BEGIN
3034 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_RV_TAB_NOT_FOUND,
3035 APIMSG_RV_TAB_NOT_FOUND,
3036 'CG_REF_CODES','DOCUMENTDATA', NULL, NULL),
3037 'E',
3038 'API',
3039 API_RV_TAB_NOT_FOUND,
3040 'cg$DOCUMENTDATA_validate_domain.no_reftable_found');
3041
3042 CALL CG$ERRORS_raise_failure;
3043
3044 END;
3045
3046 -- table doesn't exist error
3047 DECLARE EXIT HANDLER FOR 1146
3048 BEGIN
3049 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_RV_TAB_NOT_FOUND,
3050 APIMSG_RV_TAB_NOT_FOUND,
3051 'CG_REF_CODES','DOCUMENTDATA', NULL, NULL),
3052 'E',
3053 'API',
3054 API_RV_TAB_NOT_FOUND,
3055 'cg$DOCUMENTDATA_validate_domain.no_reftable_found');
3056
3057 CALL CG$ERRORS_raise_failure;
3058
3059 END;
3060
3061 /* EXCEPTION HANDLING end */
3062
3063 SET found_value := FALSE; /* MariaDB syntex "SET" keyword needs to be used */
3064 -- error messages
3065 SET API_RV_TAB_NOT_FOUND := 1012;
3066 SET APIMSG_RV_TAB_NOT_FOUND := 'Reference code table <p1> has not been created or used for <p2>';
3067 SET API_RV_LOOKUP_FAIL := 1013;
3068 SET APIMSG_RV_LOOKUP_FAIL := 'Invalid value <p1> for column <p2>.<p3>';
3069
3070 OPEN c1;
3071 IF (pDOD_DOCTYPE IS NOT NULL) THEN
3072
3073 dod_loop: LOOP
3074
3075 FETCH c1 INTO c_RV_LOW_VALUE, c_RV_HIGH_VALUE;
3076 IF loop_done THEN
3077 LEAVE dod_loop;
3078 END IF;
3079
3080 IF (c_RV_HIGH_VALUE IS NULL
3081 AND pDOD_DOCTYPE = c_RV_LOW_VALUE )
3082 OR ( c_RV_HIGH_VALUE IS NOT NULL
3083 AND pDOD_DOCTYPE BETWEEN c_RV_LOW_VALUE AND c_RV_HIGH_VALUE )
3084 THEN
3085 SET found_value := TRUE;
3086 LEAVE dod_loop;
3087 END IF;
3088
3089 END LOOP;
3090
3091
3092 IF NOT (found_value ) THEN
3093
3094 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_RV_LOOKUP_FAIL,
3095 APIMSG_RV_LOOKUP_FAIL,
3096 pDOD_DOCTYPE,'DOCUMENTDATA','DOD_DOCTYPE', NULL),
3097 'E',
3098 'API',
3099 API_RV_LOOKUP_FAIL,
3100 'cg$DOCUMENTDATA_validate_domain' );
3101
3102 CALL CG$ERRORS_raise_failure;
3103
3104 END IF;
3105
3106 END IF;
3107 CLOSE c1;
3108
3109 SET found_value := FALSE;
3110
3111
3112END */$$
3113DELIMITER ;
3114
3115/* Procedure structure for procedure `CG$DOCUMENTS_del` */
3116
3117/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_del` */;
3118
3119DELIMITER $$
3120
3121/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_del`(pDOC_ID INT(38), pDO_DEL BOOLEAN)
3122BEGIN
3123 -- AR 180215 : Work in Progress
3124 /* EXCEPTION HANDLING start */
3125 -- OTHER exceptions
3126 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3127 BEGIN
3128
3129 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3130
3131 CALL CG$ERRORS_push(@text,
3132 'E',
3133 'API',
3134 @errno,
3135 'cg$DOCUMENTS_del.others'
3136 );
3137
3138 CALL CG$ERRORS_raise_failure;
3139
3140 END;
3141 /* EXCEPTION HANDLING end */
3142
3143 IF (pDO_DEL) THEN
3144
3145 #SELECT CONCAT('Deleted ID: ', vDOC_ID);
3146 IF pDOC_ID IS NOT NULL THEN
3147
3148 DELETE FROM DOCUMENTS WHERE DOC_ID = pDOC_ID;
3149
3150 END IF;
3151
3152 END IF;
3153
3154END */$$
3155DELIMITER ;
3156
3157/* Procedure structure for procedure `CG$DOCUMENTS_ins` */
3158
3159/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_ins` */;
3160
3161DELIMITER $$
3162
3163/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_ins`(pCUS_ID INT(38), pDO_INS BOOLEAN)
3164BEGIN
3165
3166 -- AR 180215 : Work in Progress
3167/* EXCEPTION HANDLING start */
3168 -- OTHER exceptions
3169 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3170 BEGIN
3171
3172 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3173
3174 CALL CG$ERRORS_push(@text,
3175 'E',
3176 'API',
3177 @errno,
3178 'cg$DOCUMENTS_ins.others'
3179 );
3180
3181 CALL CG$ERRORS_raise_failure;
3182
3183 END;
3184 /* EXCEPTION HANDLING end */
3185
3186 CALL CG$DOCUMENTS_up_autogen_columns(pCUS_ID,'INS',pDO_INS);
3187
3188 IF (pDO_INS) THEN
3189
3190 INSERT INTO DOCUMENTS(
3191 DOC_LII_ID,
3192 DOC_FILEPATH,
3193 IS_IDORSIA,
3194 DOC_FILENAME,
3195 DOC_MASTERDOC,
3196 DOC_COMMENT,
3197 DOC_MIMETYPE,
3198 DOC_INSERTEDBY,
3199 DOC_UPDATEDBY,
3200 DOC_CREATED,
3201 DOC_CREATEDBY,
3202 DOC_MODIFIED,
3203 DOC_MODIFIEDBY,
3204 DOC_TYPE
3205 )
3206 VALUES(
3207 @vitDOC_LII_ID,
3208 @vitDOC_FILEPATH,
3209 @vitIS_IDORSIA,
3210 @vitDOC_FILENAME,
3211 @vitDOC_MASTERDOC,
3212 @vitDOC_COMMENT,
3213 @vitDOC_MIMETYPE,
3214 @vitDOC_INSERTEDBY,
3215 @vitDOC_UPDATEDBY,
3216 @viDOC_CREATED,
3217 @viDOC_CREATEDBY,
3218 @viDOC_MODIFIED,
3219 @viDOC_MODIFIEDBY,
3220 @vitDOC_TYPE
3221 );
3222
3223 END IF;
3224
3225END */$$
3226DELIMITER ;
3227
3228/* Procedure structure for procedure `CG$DOCUMENTDATA_validate_mandatory` */
3229
3230/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTDATA_validate_mandatory` */;
3231
3232DELIMITER $$
3233
3234/*!50003 CREATE PROCEDURE `CG$DOCUMENTDATA_validate_mandatory`(pDOD_ID INT(38), loc VARCHAR(240), pOPERATION VARCHAR(3))
3235BEGIN
3236
3237 -- Column default prompts. Format PSEQNO_COL
3238 DECLARE P10DOD_ID VARCHAR(240);
3239 DECLARE P60DOD_DOCTYPE VARCHAR(240);
3240 DECLARE P390DOD_KEY_PUBLICATION VARCHAR(240);
3241 DECLARE P391DOD_CLINICAL_ARTICLE VARCHAR(240);
3242 DECLARE P490DOD_GDS_LEF VARCHAR(240);
3243 DECLARE P490DOD_GDS_REVIEW VARCHAR(240);
3244
3245 -- Error messages declaration
3246 DECLARE API_MAND_COLUMN_ISNULL INTEGER;
3247 DECLARE VAL_MAND VARCHAR(240);
3248
3249 SET P10DOD_ID := 'DOD_ID';
3250 SET P60DOD_DOCTYPE := 'DOD_DOCTYPE';
3251 SET P390DOD_KEY_PUBLICATION := 'DOD_KEY_PUBLICATION';
3252 SET P391DOD_CLINICAL_ARTICLE := 'DOD_CLINICAL_ARTICLE';
3253
3254 -- Error messages SET
3255 SET API_MAND_COLUMN_ISNULL := 1008;
3256 SET VAL_MAND := 'Value for <p1> is required';
3257
3258 /*
3259 * MariaDB does not have DEFAULT variable. So IFNULL is used
3260 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
3261 */
3262 SET loc = IFNULL(loc, '');
3263 IF (pOPERATION = 'INS') THEN
3264 IF (@vitDOD_ID IS NULL) THEN
3265 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P10DOD_ID, NULL, NULL, NULL),
3266 'E',
3267 'API',
3268 API_MAND_COLUMN_ISNULL,
3269 loc);
3270 END IF;
3271 IF (@vitDOD_DOCTYPE IS NULL) THEN
3272 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P60DOD_DOCTYPE, NULL, NULL, NULL),
3273 'E',
3274 'API',
3275 API_MAND_COLUMN_ISNULL,
3276 loc);
3277 END IF;
3278 IF (@vitDOD_KEY_PUBLICATION IS NULL) THEN
3279 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P390DOD_KEY_PUBLICATION, NULL, NULL, NULL),
3280 'E',
3281 'API',
3282 API_MAND_COLUMN_ISNULL,
3283 loc);
3284 END IF;
3285 IF (@vitDOD_CLINICAL_ARTICLE IS NULL) THEN
3286 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P391DOD_CLINICAL_ARTICLE, NULL, NULL, NULL),
3287 'E',
3288 'API',
3289 API_MAND_COLUMN_ISNULL,
3290 loc);
3291 END IF;
3292
3293 ELSEIF (pOPERATION = 'UPD') THEN
3294 IF (@vutDOD_ID IS NULL) THEN
3295 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P10DOD_ID, NULL, NULL, NULL),
3296 'E',
3297 'API',
3298 API_MAND_COLUMN_ISNULL,
3299 loc);
3300 END IF;
3301 IF (@vutDOD_DOCTYPE IS NULL) THEN
3302 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P60DOD_DOCTYPE, NULL, NULL, NULL),
3303 'E',
3304 'API',
3305 API_MAND_COLUMN_ISNULL,
3306 loc);
3307 END IF;
3308 IF (@vutDOD_KEY_PUBLICATION IS NULL) THEN
3309 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P390DOD_KEY_PUBLICATION, NULL, NULL, NULL),
3310 'E',
3311 'API',
3312 API_MAND_COLUMN_ISNULL,
3313 loc);
3314 END IF;
3315 IF (@vutDOD_CLINICAL_ARTICLE IS NULL) THEN
3316 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, P391DOD_CLINICAL_ARTICLE, NULL, NULL, NULL),
3317 'E',
3318 'API',
3319 API_MAND_COLUMN_ISNULL,
3320 loc);
3321 END IF;
3322
3323 END IF;
3324END */$$
3325DELIMITER ;
3326
3327/* Procedure structure for procedure `CG$DOCUMENTS_lck` */
3328
3329/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_lck` */;
3330
3331DELIMITER $$
3332
3333/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_lck`(pDOC_ID INT(38), nowait_flag BOOLEAN)
3334BEGIN
3335 -- AR 180215 : Work in Progress
3336 DECLARE vDOC_ID INT(38);
3337 DECLARE vDOC_LII_ID DECIMAL(38,0);
3338 -- DECLARE vDOC_DOCUMENT longblob;
3339 DECLARE vDOC_FILENAME VARCHAR(255);
3340 DECLARE vDOC_MASTERDOC VARCHAR(1);
3341 DECLARE vDOC_COMMENT TEXT;
3342 DECLARE vDOC_MIMETYPE VARCHAR(155);
3343 DECLARE vDOC_INSERTEDBY VARCHAR(50);
3344 DECLARE vDOC_UPDATEDBY VARCHAR(50);
3345 DECLARE vDOC_CREATED DATETIME(6);
3346 DECLARE vDOC_CREATEDBY VARCHAR(30);
3347 DECLARE vDOC_MODIFIED DATETIME(6);
3348 DECLARE vDOC_MODIFIEDBY VARCHAR(30);
3349 DECLARE vDOC_GDI_ID DECIMAL(38,0);
3350 DECLARE vDOC_TYPE VARCHAR(10);
3351 /* EXCEPTION HANDLING start */
3352 -- OTHER exceptions
3353 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3354 BEGIN
3355
3356 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3357
3358 CALL CG$ERRORS_push(@text,
3359 'E',
3360 'API',
3361 @errno,
3362 'cg$DOCUMENTS_lck.others'
3363 );
3364
3365 CALL CG$ERRORS_raise_failure;
3366
3367 END;
3368 /* EXCEPTION HANDLING end */
3369
3370
3371 IF (nowait_flag) THEN
3372
3373 #LOCK is not allowed in stored procedures : LOCK outside the procedure
3374 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
3375 IF pDOC_ID IS NOT NULL THEN
3376
3377 SELECT
3378 DOC_ID,
3379 DOC_LII_ID,
3380 DOC_FILEPATH,
3381 DOC_FILENAME,
3382 DOC_MASTERDOC,
3383 DOC_COMMENT,
3384 DOC_MIMETYPE,
3385 DOC_INSERTEDBY,
3386 DOC_UPDATEDBY,
3387 DOC_CREATED,
3388 DOC_CREATEDBY,
3389 DOC_MODIFIED,
3390 DOC_MODIFIEDBY,
3391 IS_IDORSIA,
3392 DOC_TYPE
3393 INTO
3394 @vDOC_ID,
3395 @vDOC_LII_ID,
3396 @vDOC_FILEPATH,
3397 @vDOC_FILENAME,
3398 @vDOC_MASTERDOC,
3399 @vDOC_COMMENT,
3400 @vDOC_MIMETYPE,
3401 @vDOC_INSERTEDBY,
3402 @vDOC_UPDATEDBY,
3403 @vDOC_CREATED,
3404 @vDOC_CREATEDBY,
3405 @vDOC_MODIFIED,
3406 @vDOC_MODIFIEDBY,
3407 @vIS_IDORSIA,
3408 @vDOC_TYPE
3409 FROM DOCUMENTS
3410 WHERE DOCUMENTS.DOC_ID = pDOC_ID
3411 FOR UPDATE;
3412
3413 END IF;
3414
3415 END IF;
3416
3417END */$$
3418DELIMITER ;
3419
3420/* Procedure structure for procedure `CG$DOCUMENTS_slct` */
3421
3422/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_slct` */;
3423
3424DELIMITER $$
3425
3426/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_slct`(pDOC_ID INT(38))
3427BEGIN
3428 -- AR 180215 : Work in Progress
3429 DECLARE vDOC_ID INT(38);
3430 DECLARE vDOC_LII_ID DECIMAL(38,0);
3431 -- DECLARE vDOC_DOCUMENT longblob;
3432 DECLARE vDOC_FILENAME VARCHAR(255);
3433 DECLARE vDOC_MASTERDOC VARCHAR(1);
3434 DECLARE vDOC_COMMENT TEXT;
3435 DECLARE vDOC_MIMETYPE VARCHAR(155);
3436 DECLARE vDOC_INSERTEDBY VARCHAR(50);
3437 DECLARE vDOC_UPDATEDBY VARCHAR(50);
3438 DECLARE vDOC_CREATED DATETIME(6);
3439 DECLARE vDOC_CREATEDBY VARCHAR(30);
3440 DECLARE vDOC_MODIFIED DATETIME(6);
3441 DECLARE vDOC_MODIFIEDBY VARCHAR(30);
3442 DECLARE vDOC_GDI_ID DECIMAL(38,0);
3443 DECLARE vDOC_TYPE VARCHAR(10);
3444 /* EXCEPTION HANDLING start */
3445 -- OTHER exceptions
3446 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3447 BEGIN
3448
3449 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3450
3451 CALL CG$ERRORS_push(@text,
3452 'E',
3453 'API',
3454 @errno,
3455 'cg$DOCUMENTS_slct.others'
3456 );
3457
3458 CALL CG$ERRORS_raise_failure;
3459
3460 END;
3461 /* EXCEPTION HANDLING end */
3462
3463
3464 IF pDOC_ID IS NOT NULL THEN
3465
3466 SELECT
3467 DOC_LII_ID,
3468 DOC_FILEPATH,
3469 DOC_FILENAME,
3470 DOC_MASTERDOC,
3471 DOC_COMMENT,
3472 DOC_MIMETYPE,
3473 DOC_INSERTEDBY,
3474 DOC_UPDATEDBY,
3475 DOC_CREATED,
3476 DOC_CREATEDBY,
3477 DOC_MODIFIED,
3478 DOC_MODIFIEDBY,
3479 IS_IDORSIA,
3480 DOC_TYPE
3481 INTO
3482 @vDOC_LII_ID,
3483 @vDOC_FILEPATH,
3484 @vDOC_FILENAME,
3485 @vDOC_MASTERDOC,
3486 @vDOC_COMMENT,
3487 @vDOC_MIMETYPE,
3488 @vDOC_INSERTEDBY,
3489 @vDOC_UPDATEDBY,
3490 @vDOC_CREATED,
3491 @vDOC_CREATEDBY,
3492 @vDOC_MODIFIED,
3493 @vDOC_MODIFIEDBY,
3494 @vIS_IDORSIA,
3495 @vDOC_TYPE
3496 FROM DOCUMENTS
3497 WHERE DOCUMENTS.DOC_ID = pDOC_ID;
3498
3499 END IF;
3500
3501END */$$
3502DELIMITER ;
3503
3504/* Procedure structure for procedure `CG$DOCUMENTS_upd` */
3505
3506/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_upd` */;
3507
3508DELIMITER $$
3509
3510/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_upd`(pCUS_ID INT(38), pDO_UPD BOOLEAN)
3511BEGIN
3512 -- AR 180215 : Work in Progress
3513 /* EXCEPTION HANDLING start */
3514 -- OTHER exceptions
3515 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3516 BEGIN
3517
3518 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3519
3520 CALL CG$ERRORS_push(@text,
3521 'E',
3522 'API',
3523 @errno,
3524 'cg$DOCUMENTS_upd.others'
3525 );
3526
3527 CALL CG$ERRORS_raise_failure;
3528
3529 END;
3530 /* EXCEPTION HANDLING end */
3531
3532 #CALL CG$DOCUMENTS_slct(pCUS_ID);
3533
3534 /*
3535 * Fetching VALUE OF MODIFIED & MODIFIEDBY
3536 */
3537 CALL CG$DOCUMENTS_up_autogen_columns(pCUS_ID,'UPD',pDO_UPD);
3538
3539 IF (pDO_UPD) THEN
3540
3541 UPDATE
3542 DOCUMENTS
3543 SET
3544 DOC_LII_ID = @vutDOC_LII_ID,
3545 -- DOC_DOCUMENT = @vutDOC_DOCUMENT,
3546 DOC_FILENAME = @vutDOC_FILENAME,
3547 DOC_MASTERDOC = @vutDOC_MASTERDOC,
3548 DOC_COMMENT = @vutDOC_COMMENT,
3549 DOC_MIMETYPE = @vutDOC_MIMETYPE,
3550 DOC_INSERTEDBY = @vutDOC_INSERTEDBY,
3551 DOC_UPDATEDBY = @vutDOC_UPDATEDBY,
3552 DOC_CREATED = @vutDOC_CREATED,
3553 DOC_CREATEDBY = @vutDOC_CREATEDBY,
3554 DOC_MODIFIED = @vutDOC_MODIFIED,
3555 DOC_MODIFIEDBY = @vutDOC_MODIFIEDBY,
3556 DOC_GDI_ID = @vutDOC_GDI_ID,
3557 DOC_TYPE = @vutDOC_TYPE
3558 WHERE
3559 CUS_ID = pCUS_ID;
3560
3561 END IF;
3562
3563 #CALL CG$DOCUMENTS_slct(pCUS_ID);
3564
3565END */$$
3566DELIMITER ;
3567
3568/* Procedure structure for procedure `CG$DOCUMENTS_up_autogen_columns` */
3569
3570/*!50003 DROP PROCEDURE IF EXISTS `CG$DOCUMENTS_up_autogen_columns` */;
3571
3572DELIMITER $$
3573
3574/*!50003 CREATE PROCEDURE `CG$DOCUMENTS_up_autogen_columns`(pDOC_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
3575BEGIN
3576
3577 -- AR 180215 : Work in Progress
3578 DECLARE viDOC_CREATED DATETIME(6);
3579 DECLARE viDOC_CREATEDBY VARCHAR(50);
3580 DECLARE viDOC_MODIFIED DATETIME(6);
3581 DECLARE viDOC_MODIFIEDBY VARCHAR(50);
3582 /* EXCEPTION HANDLING start */
3583 -- OTHER exceptions
3584 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3585 BEGIN
3586
3587 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3588
3589 CALL CG$ERRORS_push(@text,
3590 'E',
3591 'API',
3592 @errno,
3593 'cg$DOCUMENTS_up_autogen_columns.others'
3594 );
3595
3596 CALL CG$ERRORS_raise_failure;
3597
3598 END;
3599 /* EXCEPTION HANDLING end */
3600
3601
3602 /*
3603 * MariaDB does not have DEFAULT variable. So IFNULL is used
3604 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
3605 */
3606 SET operation = IFNULL(operation, 'INS');
3607
3608 IF (operation = 'INS') THEN
3609 /*
3610 * DOC_ID is already an Auto-Increment field
3611 * IF (pDOC_ID IS NOT NULL AND @vitDOC_CREATED = TRUE) THEN
3612 * SELECT LAST_INSERT_ID() INTO @vitDOC_ID FROM DOCUMENTS;
3613 * END IF;
3614 */
3615
3616 SET @viDOC_CREATED = SYSDATE();
3617 SET @viDOC_CREATEDBY = USER();
3618
3619 END IF;
3620
3621 SET @viDOC_MODIFIED = SYSDATE();
3622 SET @viDOC_MODIFIEDBY = USER();
3623
3624END */$$
3625DELIMITER ;
3626
3627/* Procedure structure for procedure `CG$ERRORS_push` */
3628
3629/*!50003 DROP PROCEDURE IF EXISTS `CG$ERRORS_push` */;
3630
3631DELIMITER $$
3632
3633/*!50003 CREATE PROCEDURE `CG$ERRORS_push`(msg VARCHAR(255), error VARCHAR(255), msg_type VARCHAR(255), msgid INTEGER, loc VARCHAR(255))
3634BEGIN
3635
3636 DECLARE cg$err_msg VARCHAR(512);
3637 DECLARE cg$err_error VARCHAR(1);
3638 DECLARE cg$err_msg_type VARCHAR(3);
3639 DECLARE cg$err_msgid INTEGER;
3640 DECLARE cg$err_loc VARCHAR(240);
3641
3642
3643 /*
3644 * MariaDB does not support default value set in the parameters like oracle do
3645 * so the following codes are to set default value if nothing is passed
3646 */
3647 SET error = IFNULL(error, 'E');
3648 SET msg_type = IFNULL(msg_type, NULL);
3649 SET msgid = IFNULL(msgid, 0);
3650 SET loc = IFNULL(loc, NULL);
3651
3652 /*
3653 * MariaDB does not support collection arrays like oracle do
3654 * So error string is concated into local variable to be used in CG$ERRORS_LookError() function later
3655 */
3656
3657
3658 SET @cg$err_msg := msg;
3659 SET @cg$err_error := error;
3660 SET @cg$err_msg_type := msg_type;
3661 SET @cg$err_msgid := msgid;
3662 SET @cg$err_loc := loc;
3663
3664END */$$
3665DELIMITER ;
3666
3667/* Procedure structure for procedure `CG$JOURNALS_del` */
3668
3669/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_del` */;
3670
3671DELIMITER $$
3672
3673/*!50003 CREATE PROCEDURE `CG$JOURNALS_del`(pJRN_ID INT(38), pDO_DEL BOOLEAN)
3674BEGIN
3675 -- AR 180215 : Work in Progress
3676 /* EXCEPTION HANDLING start */
3677 -- OTHER exceptions
3678 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3679 BEGIN
3680
3681 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3682
3683 CALL CG$ERRORS_push(@text,
3684 'E',
3685 'API',
3686 @errno,
3687 'cg$JOURNALS_del.others'
3688 'cg$JOURNALS_del.others'
3689 );
3690
3691 CALL CG$ERRORS_raise_failure;
3692
3693 END;
3694 /* EXCEPTION HANDLING end */
3695 IF (pDO_DEL) THEN
3696
3697 #SELECT CONCAT('Deleted ID: ', vJRN_ID);
3698 IF pJRN_ID IS NOT NULL THEN
3699
3700 DELETE FROM JOURNALS WHERE JRN_ID = pJRN_ID;
3701
3702 END IF;
3703
3704 END IF;
3705
3706END */$$
3707DELIMITER ;
3708
3709/* Procedure structure for procedure `CG$JOURNALS_ins` */
3710
3711/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_ins` */;
3712
3713DELIMITER $$
3714
3715/*!50003 CREATE PROCEDURE `CG$JOURNALS_ins`(pJRN_ID INT(38), pDO_INS BOOLEAN)
3716BEGIN
3717
3718 -- AR 180215 : Work in Progress
3719 /* EXCEPTION HANDLING start */
3720 -- OTHER exceptions
3721 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3722 BEGIN
3723
3724 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3725
3726 CALL CG$ERRORS_push(@text,
3727 'E',
3728 'API',
3729 @errno,
3730 'cg$JOURNALS_ins.others'
3731 );
3732
3733 CALL CG$ERRORS_raise_failure;
3734
3735 END;
3736 /* EXCEPTION HANDLING end */
3737 CALL CG$JOURNALS_up_autogen_columns(pJRN_ID,'INS',pDO_INS);
3738
3739 IF (pDO_INS) THEN
3740
3741 INSERT INTO JOURNALS(
3742 JRN_EMAIL,
3743 JRN_FIRSTNAME,
3744 JRN_LASTNAME,
3745 JRN_INSERTEDBY,
3746 JRN_UPDATEDBY,
3747 JRN_CREATED,
3748 JRN_CREATEDBY,
3749 JRN_MODIFIED,
3750 JRN_MODIFIEDBY)
3751 VALUES(
3752 @vitJRN_EMAIL,
3753 @vitJRN_FIRSTNAME,
3754 @vitJRN_LASTNAME,
3755 @vitJRN_INSERTEDBY,
3756 @vitJRN_UPDATEDBY,
3757 @viJRN_CREATED,
3758 @viJRN_CREATEDBY,
3759 @viJRN_MODIFIED,
3760 @viJRN_MODIFIEDBY);
3761
3762 END IF;
3763
3764END */$$
3765DELIMITER ;
3766
3767/* Procedure structure for procedure `CG$JOURNALS_lck` */
3768
3769/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_lck` */;
3770
3771DELIMITER $$
3772
3773/*!50003 CREATE PROCEDURE `CG$JOURNALS_lck`(pJRN_ID INT(38), nowait_flag BOOLEAN)
3774BEGIN
3775 -- AR 180215 : Work in Progress
3776 DECLARE vJRN_ID INT(38);
3777 DECLARE vJRN_EMAIL VARCHAR(100);
3778 DECLARE vJRN_FIRSTNAME VARCHAR(50);
3779 DECLARE vJRN_LASTNAME VARCHAR(50);
3780
3781 DECLARE vJRN_INSERTEDBY VARCHAR(50);
3782 DECLARE vJRN_UPDATEDBY VARCHAR(50);
3783 DECLARE vJRN_CREATED DATETIME(6);
3784 DECLARE vJRN_CREATEDBY VARCHAR(50);
3785 DECLARE vJRN_MODIFIED DATETIME(6);
3786 DECLARE vJRN_MODIFIEDBY VARCHAR(50);
3787 /* EXCEPTION HANDLING start */
3788 -- OTHER exceptions
3789 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3790 BEGIN
3791
3792 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3793
3794 CALL CG$ERRORS_push(@text,
3795 'E',
3796 'API',
3797 @errno,
3798 'cg$JOURNALS_lck.others'
3799 );
3800
3801 CALL CG$ERRORS_raise_failure;
3802
3803 END;
3804 /* EXCEPTION HANDLING end */
3805
3806
3807 IF (nowait_flag) THEN
3808
3809 #LOCK is not allowed in stored procedures : LOCK outside the procedure
3810 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
3811 IF pJRN_ID IS NOT NULL THEN
3812
3813 SELECT
3814 JRN_ID,
3815 JRN_EMAIL,
3816 JRN_FIRSTNAME,
3817 JRN_LASTNAME,
3818 JRN_INSERTEDBY,
3819 JRN_UPDATEDBY,
3820 JRN_CREATED,
3821 JRN_CREATEDBY,
3822 JRN_MODIFIED,
3823 JRN_MODIFIEDBY
3824 INTO
3825 @vJRN_ID,
3826 @vJRN_EMAIL,
3827 @vJRN_FIRSTNAME,
3828 @vJRN_LASTNAME,
3829 @vJRN_INSERTEDBY,
3830 @vJRN_UPDATEDBY,
3831 @vJRN_CREATED,
3832 @vJRN_CREATEDBY,
3833 @vJRN_MODIFIED,
3834 @vJRN_MODIFIEDBY
3835 FROM JOURNALS
3836 WHERE JOURNALS.JRN_ID = pJRN_ID
3837 FOR UPDATE;
3838
3839 END IF;
3840
3841 END IF;
3842
3843END */$$
3844DELIMITER ;
3845
3846/* Procedure structure for procedure `CG$JOURNALS_slct` */
3847
3848/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_slct` */;
3849
3850DELIMITER $$
3851
3852/*!50003 CREATE PROCEDURE `CG$JOURNALS_slct`(pJRN_ID INT(38))
3853BEGIN
3854 -- AR 180215 : Work in Progress
3855 DECLARE vJRN_ID INT(38);
3856 DECLARE vJRN_EMAIL VARCHAR(100);
3857 DECLARE vJRN_FIRSTNAME VARCHAR(50);
3858 DECLARE vJRN_LASTNAME VARCHAR(50);
3859
3860 DECLARE vJRN_INSERTEDBY VARCHAR(50);
3861 DECLARE vJRN_UPDATEDBY VARCHAR(50);
3862 DECLARE vJRN_CREATED DATETIME(6);
3863 DECLARE vJRN_CREATEDBY VARCHAR(50);
3864 DECLARE vJRN_MODIFIED DATETIME(6);
3865 DECLARE vJRN_MODIFIEDBY VARCHAR(50);
3866 /* EXCEPTION HANDLING start */
3867 -- OTHER exceptions
3868 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3869 BEGIN
3870
3871 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3872
3873 CALL CG$ERRORS_push(@text,
3874 'E',
3875 'API',
3876 @errno,
3877 'cg$JOURNALS_slct.others'
3878 );
3879
3880 CALL CG$ERRORS_raise_failure;
3881
3882 END;
3883 /* EXCEPTION HANDLING end */
3884
3885
3886 IF pJRN_ID IS NOT NULL THEN
3887
3888 SELECT
3889 JRN_ID,
3890 JRN_EMAIL,
3891 JRN_FIRSTNAME,
3892 JRN_LASTNAME,
3893 JRN_INSERTEDBY,
3894 JRN_UPDATEDBY,
3895 JRN_CREATED,
3896 JRN_CREATEDBY,
3897 JRN_MODIFIED,
3898 JRN_MODIFIEDBY
3899 INTO
3900 @vJRN_ID,
3901 @vJRN_EMAIL,
3902 @vJRN_FIRSTNAME,
3903 @vJRN_LASTNAME,
3904 @vJRN_INSERTEDBY,
3905 @vJRN_UPDATEDBY,
3906 @vJRN_CREATED,
3907 @vJRN_CREATEDBY,
3908 @vJRN_MODIFIED,
3909 @vJRN_MODIFIEDBY
3910 FROM JOURNALS
3911 WHERE JOURNALS.JRN_ID = pJRN_ID;
3912
3913 END IF;
3914
3915END */$$
3916DELIMITER ;
3917
3918/* Procedure structure for procedure `CG$JOURNALS_upd` */
3919
3920/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_upd` */;
3921
3922DELIMITER $$
3923
3924/*!50003 CREATE PROCEDURE `CG$JOURNALS_upd`(pJRN_ID INT(38), pDO_UPD BOOLEAN)
3925BEGIN
3926 -- AR 180215 : Work in Progress
3927/* EXCEPTION HANDLING start */
3928 -- OTHER exceptions
3929 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3930 BEGIN
3931
3932 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3933
3934 CALL CG$ERRORS_push(@text,
3935 'E',
3936 'API',
3937 @errno,
3938 'cg$JOURNALS_upd.others'
3939 );
3940
3941 CALL CG$ERRORS_raise_failure;
3942
3943 END;
3944 /* EXCEPTION HANDLING end */
3945
3946 #CALL CG$JOURNALS_slct(pJRN_ID);
3947 /*
3948 * Fetching VALUE OF MODIFIED & MODIFIEDBY
3949 */
3950 CALL CG$JOURNALS_up_autogen_columns(pJRN_ID,'UPD',pDO_UPD);
3951
3952 IF (pDO_UPD) THEN
3953
3954 UPDATE
3955 JOURNALS
3956 SET
3957 JRN_EMAIL = @vutJRN_EMAIL,
3958 JRN_FIRSTNAME = @vutJRN_FIRSTNAME,
3959 JRN_LASTNAME = @vutJRN_LASTNAME,
3960 JRN_INSERTEDBY = @vutJRN_INSERTEDBY,
3961 JRN_UPDATEDBY = @vutJRN_UPDATEDBY,
3962 JRN_MODIFIED = @viJRN_MODIFIED,
3963 JRN_MODIFIEDBY = @viJRN_MODIFIEDBY
3964 WHERE
3965 JRN_ID = pJRN_ID;
3966
3967 END IF;
3968
3969 #CALL CG$JOURNALS_slct(pJRN_ID);
3970
3971END */$$
3972DELIMITER ;
3973
3974/* Procedure structure for procedure `CG$JOURNALS_up_autogen_columns` */
3975
3976/*!50003 DROP PROCEDURE IF EXISTS `CG$JOURNALS_up_autogen_columns` */;
3977
3978DELIMITER $$
3979
3980/*!50003 CREATE PROCEDURE `CG$JOURNALS_up_autogen_columns`(pJRN_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
3981BEGIN
3982
3983 -- AR 180215 : Work in Progress
3984 DECLARE viJRN_CREATED DATETIME(6);
3985 DECLARE viJRN_CREATEDBY VARCHAR(50);
3986 DECLARE viJRN_MODIFIED DATETIME(6);
3987 DECLARE viJRN_MODIFIEDBY VARCHAR(50);
3988 /* EXCEPTION HANDLING start */
3989 -- OTHER exceptions
3990 DECLARE EXIT HANDLER FOR SQLEXCEPTION
3991 BEGIN
3992
3993 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
3994
3995 CALL CG$ERRORS_push(@text,
3996 'E',
3997 'API',
3998 @errno,
3999 'cg$JOURNALS_up_autogen_columns.others'
4000 );
4001
4002 CALL CG$ERRORS_raise_failure;
4003
4004 END;
4005 /* EXCEPTION HANDLING end */
4006
4007
4008 /*
4009 * MariaDB does not have DEFAULT variable. So IFNULL is used
4010 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
4011 */
4012 SET operation = IFNULL(operation, 'INS');
4013
4014 IF (operation = 'INS') THEN
4015 /*
4016 * JRN_ID is already an Auto-Increment field
4017 * IF (pJRN_ID IS NOT NULL AND @vitJRN_CREATED = TRUE) THEN
4018 * SELECT LAST_INSERT_ID() INTO @vitJRN_ID FROM JOURNALS;
4019 * END IF;
4020 */
4021
4022 SET @viJRN_CREATED = SYSDATE();
4023 SET @viJRN_CREATEDBY = USER();
4024
4025 END IF;
4026
4027 SET @viJRN_MODIFIED = SYSDATE();
4028 SET @viJRN_MODIFIEDBY = USER();
4029
4030END */$$
4031DELIMITER ;
4032
4033/* Procedure structure for procedure `CG$PARAMETER_del` */
4034
4035/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_del` */;
4036
4037DELIMITER $$
4038
4039/*!50003 CREATE PROCEDURE `CG$PARAMETER_del`(pPAR_ID INT(38), pDO_DEL BOOLEAN)
4040BEGIN
4041 -- AR 180215 : Work in Progress
4042/* EXCEPTION HANDLING start */
4043 -- OTHER exceptions
4044 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4045 BEGIN
4046
4047 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4048
4049 CALL CG$ERRORS_push(@text,
4050 'E',
4051 'API',
4052 @errno,
4053 'cg$PARAMETER_del.others'
4054 );
4055
4056 CALL CG$ERRORS_raise_failure;
4057
4058 END;
4059 /* EXCEPTION HANDLING end */
4060
4061 IF (pDO_DEL) THEN
4062 #SELECT CONCAT('Deleted ID: ', vPAR_ID);
4063
4064 IF pPAR_ID IS NOT NULL THEN
4065
4066 DELETE FROM PARAMETER WHERE PAR_ID = pPAR_ID;
4067
4068 END IF;
4069
4070 END IF;
4071
4072END */$$
4073DELIMITER ;
4074
4075/* Procedure structure for procedure `CG$PARAMETER_ins` */
4076
4077/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_ins` */;
4078
4079DELIMITER $$
4080
4081/*!50003 CREATE PROCEDURE `CG$PARAMETER_ins`(pPAR_ID INT(38), pDO_INS BOOLEAN)
4082BEGIN
4083 -- AR 180215 : Work in Progress
4084 /* EXCEPTION HANDLING start */
4085 -- OTHER exceptions
4086 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4087 BEGIN
4088
4089 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4090
4091 CALL CG$ERRORS_push(@text,
4092 'E',
4093 'API',
4094 @errno,
4095 'cg$PARAMETER_ins.others'
4096 );
4097
4098 CALL CG$ERRORS_raise_failure;
4099
4100 END;
4101 /* EXCEPTION HANDLING end */
4102
4103 CALL CG$PARAMETER_up_autogen_columns(pPAR_ID,'INS',pDO_INS);
4104
4105 IF (pDO_INS) THEN
4106
4107 INSERT INTO PARAMETER(
4108 PAR_NAME,
4109 PAR_VALUE,
4110 PAR_DESCRIPTION,
4111 PAR_INSERTEDBY,
4112 PAR_UPDATEDBY,
4113 PAR_CREATED,
4114 PAR_CREATEDBY,
4115 PAR_MODIFIED,
4116 PAR_MODIFIEDBY
4117 )
4118 VALUES(
4119 @vutPAR_NAME,
4120 @vutPAR_VALUE,
4121 @vutPAR_DESCRIPTION,
4122 @vutPAR_INSERTEDBY,
4123 @vutPAR_UPDATEDBY,
4124 @vutPAR_CREATED,
4125 @vutPAR_CREATEDBY,
4126 @vutPAR_MODIFIED,
4127 @vutPAR_MODIFIEDBY
4128 );
4129
4130 END IF;
4131
4132END */$$
4133DELIMITER ;
4134
4135/* Procedure structure for procedure `CG$PARAMETER_lck` */
4136
4137/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_lck` */;
4138
4139DELIMITER $$
4140
4141/*!50003 CREATE PROCEDURE `CG$PARAMETER_lck`(pPAR_ID INT(38), nowait_flag BOOLEAN)
4142BEGIN
4143 -- AR 180215 : Work in Progress
4144 DECLARE vPAR_ID INT(38);
4145 DECLARE vPAR_NAME VARCHAR(50);
4146 DECLARE vPAR_VALUE TEXT;
4147 DECLARE vPAR_DESCRIPTION TEXT;
4148 DECLARE vPAR_INSERTEDBY VARCHAR(50);
4149 DECLARE vPAR_UPDATEDBY VARCHAR(50);
4150 DECLARE vPAR_CREATED DATETIME(6);
4151 DECLARE vPAR_CREATEDBY VARCHAR(30);
4152 DECLARE vPAR_MODIFIED DATETIME(6);
4153 DECLARE vPAR_MODIFIEDBY VARCHAR(30);
4154
4155 /* EXCEPTION HANDLING start */
4156 -- OTHER exceptions
4157 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4158 BEGIN
4159
4160 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4161
4162 CALL CG$ERRORS_push(@text,
4163 'E',
4164 'API',
4165 @errno,
4166 'cg$PARAMETER_lck.others'
4167 );
4168
4169 CALL CG$ERRORS_raise_failure;
4170
4171 END;
4172 /* EXCEPTION HANDLING end */
4173
4174 IF (nowait_flag) THEN
4175 #LOCK is not allowed in stored procedures : LOCK outside the procedure
4176 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
4177
4178 IF pPAR_ID IS NOT NULL THEN
4179
4180 SELECT
4181 PAR_ID,
4182 PAR_NAME,
4183 PAR_VALUE,
4184 PAR_DESCRIPTION,
4185 PAR_INSERTEDBY,
4186 PAR_UPDATEDBY,
4187 PAR_CREATED,
4188 PAR_CREATEDBY,
4189 PAR_MODIFIED,
4190 PAR_MODIFIEDBY
4191 INTO
4192 @vPAR_ID,
4193 @vPAR_NAME,
4194 @vPAR_VALUE,
4195 @vPAR_DESCRIPTION,
4196 @vPAR_INSERTEDBY,
4197 @vPAR_UPDATEDBY,
4198 @vPAR_CREATED,
4199 @vPAR_CREATEDBY,
4200 @vPAR_MODIFIED,
4201 @vPAR_MODIFIEDBY
4202 FROM PARAMETER
4203 WHERE PARAMETER.PAR_ID = pPAR_ID
4204 FOR UPDATE;
4205
4206 END IF;
4207
4208 END IF;
4209
4210END */$$
4211DELIMITER ;
4212
4213/* Procedure structure for procedure `CG$PARAMETER_slct` */
4214
4215/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_slct` */;
4216
4217DELIMITER $$
4218
4219/*!50003 CREATE PROCEDURE `CG$PARAMETER_slct`(pPAR_ID INT(38))
4220BEGIN
4221 -- AR 180215 : Work in Progress
4222 DECLARE vPAR_ID INT(38);
4223 DECLARE vPAR_NAME VARCHAR(50);
4224 DECLARE vPAR_VALUE TEXT;
4225 DECLARE vPAR_DESCRIPTION TEXT;
4226 DECLARE vPAR_INSERTEDBY VARCHAR(50);
4227 DECLARE vPAR_UPDATEDBY VARCHAR(50);
4228 DECLARE vPAR_CREATED DATETIME(6);
4229 DECLARE vPAR_CREATEDBY VARCHAR(30);
4230 DECLARE vPAR_MODIFIED DATETIME(6);
4231 DECLARE vPAR_MODIFIEDBY VARCHAR(30);
4232 /* EXCEPTION HANDLING start */
4233 -- OTHER exceptions
4234 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4235 BEGIN
4236
4237 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4238
4239 CALL CG$ERRORS_push(@text,
4240 'E',
4241 'API',
4242 @errno,
4243 'cg$PARAMETER_slct.others'
4244 );
4245
4246 CALL CG$ERRORS_raise_failure;
4247
4248 END;
4249 /* EXCEPTION HANDLING end */
4250
4251
4252 IF pPAR_ID IS NOT NULL THEN
4253
4254 SELECT
4255 PAR_ID,
4256 PAR_NAME,
4257 PAR_VALUE,
4258 PAR_DESCRIPTION,
4259 PAR_INSERTEDBY,
4260 PAR_UPDATEDBY,
4261 PAR_CREATED,
4262 PAR_CREATEDBY,
4263 PAR_MODIFIED,
4264 PAR_MODIFIEDBY
4265 INTO
4266 @vPAR_ID,
4267 @vPAR_NAME,
4268 @vPAR_VALUE,
4269 @vPAR_DESCRIPTION,
4270 @vPAR_INSERTEDBY,
4271 @vPAR_UPDATEDBY,
4272 @vPAR_CREATED,
4273 @vPAR_CREATEDBY,
4274 @vPAR_MODIFIED,
4275 @vPAR_MODIFIEDBY
4276 FROM PARAMETER
4277 WHERE PARAMETER.PAR_ID = pPAR_ID;
4278
4279 END IF;
4280
4281END */$$
4282DELIMITER ;
4283
4284/* Procedure structure for procedure `CG$PARAMETER_upd` */
4285
4286/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_upd` */;
4287
4288DELIMITER $$
4289
4290/*!50003 CREATE PROCEDURE `CG$PARAMETER_upd`(pPAR_ID INT(38), pDO_UPD BOOLEAN)
4291BEGIN
4292 -- AR 180215 : Work in Progress
4293 /* EXCEPTION HANDLING start */
4294 -- OTHER exceptions
4295 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4296 BEGIN
4297
4298 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4299
4300 CALL CG$ERRORS_push(@text,
4301 'E',
4302 'API',
4303 @errno,
4304 'cg$PARAMETER_upd.others'
4305 );
4306
4307 CALL CG$ERRORS_raise_failure;
4308
4309 END;
4310 /* EXCEPTION HANDLING end */
4311
4312 #CALL CG$PARAMETER_slct(pPAR_ID);
4313
4314 /* Not Needed Anymore
4315 * Checking: IF NEW COLUMN VALUE IS NOT NULL : O/W RAISE EXCEPTION
4316 * IF @vutPAR_ORI_ID IS NOT NULL AND (CHAR_LENGTH(TRIM(@vutPAR_ORI_ID)) > 0) THEN
4317 * SET @vuPAR_ORI_ID = @vutPAR_ORI_ID;
4318 * ELSE
4319 * SIGNAL SQLSTATE '22004' SET
4320 * MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column(PAR_ORI_ID)';
4321 * END IF;
4322 */
4323
4324 /*
4325 * Fetching VALUE OF MODIFIED & MODIFIEDBY
4326 */
4327 CALL CG$PARAMETER_up_autogen_columns(pPAR_ID,'UPD',pDO_UPD);
4328
4329 IF (pDO_UPD) THEN
4330
4331 UPDATE
4332 PARAMETER
4333 SET
4334 PAR_NAME = @vutPAR_NAME,
4335 PAR_VALUE = @vutPAR_VALUE,
4336 PAR_DESCRIPTION = @vutPAR_DESCRIPTION,
4337 PAR_INSERTEDBY = @vutPAR_INSERTEDBY,
4338 PAR_UPDATEDBY = @vutPAR_UPDATEDBY,
4339 PAR_CREATED = @vutPAR_CREATED,
4340 PAR_CREATEDBY = @vutPAR_CREATEDBY,
4341 PAR_MODIFIED = @vutPAR_MODIFIED,
4342 PAR_MODIFIEDBY = @vutPAR_MODIFIEDBY
4343 WHERE
4344 PAR_ID = pPAR_ID;
4345
4346 END IF;
4347
4348 #CALL CG$PARAMETER_slct(pPAR_ID);
4349
4350END */$$
4351DELIMITER ;
4352
4353/* Procedure structure for procedure `CG$PARAMETER_up_autogen_columns` */
4354
4355/*!50003 DROP PROCEDURE IF EXISTS `CG$PARAMETER_up_autogen_columns` */;
4356
4357DELIMITER $$
4358
4359/*!50003 CREATE PROCEDURE `CG$PARAMETER_up_autogen_columns`(pPAR_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
4360BEGIN
4361 -- AR 180215 : Work in Progress
4362/* EXCEPTION HANDLING start */
4363 -- OTHER exceptions
4364 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4365 BEGIN
4366
4367 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4368
4369 CALL CG$ERRORS_push(@text,
4370 'E',
4371 'API',
4372 @errno,
4373 'cg$PARAMETER_up_autogen_columns.others'
4374 );
4375
4376 CALL CG$ERRORS_raise_failure;
4377
4378 END;
4379 /* EXCEPTION HANDLING end */
4380
4381 /*
4382 * MariaDB does not have DEFAULT variable. So IFNULL is used
4383 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
4384 */
4385 SET operation = IFNULL(operation, 'INS');
4386
4387 IF (operation = 'INS') THEN
4388 /*
4389 * PAR_ID is already an Auto-Increment field
4390 * IF (pPAR_ID IS NOT NULL AND @vitPAR_CREATED = TRUE) THEN
4391 * SELECT LAST_INSERT_ID() INTO @vitPAR_ID FROM PARAMETER;
4392 * END IF;
4393 */
4394 SET operation = IFNULL(operation, 'INS');
4395 END IF;
4396
4397END */$$
4398DELIMITER ;
4399
4400/* Procedure structure for procedure `CG$ROLES_del` */
4401
4402/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_del` */;
4403
4404DELIMITER $$
4405
4406/*!50003 CREATE PROCEDURE `CG$ROLES_del`(pROL_ID int(38), pDO_DEL BOOLEAN)
4407BEGIN
4408
4409 /* EXCEPTION HANDLING start */
4410
4411 -- OTHER exceptions
4412 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4413 BEGIN
4414 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4415 CALL CG$ERRORS_push(@text,
4416 'E',
4417 'API',
4418 @errno,
4419 'cg$ROLES_del.others');
4420 CALL CG$ERRORS_raise_failure;
4421
4422 END;
4423
4424 /* EXCEPTION HANDLING end */
4425
4426 IF (pDO_DEL) THEN
4427 #SELECT CONCAT('Deleted ID: ', vROL_ID);
4428 IF pROL_ID IS NOT NULL THEN
4429 DELETE FROM ROLES WHERE ROL_ID = pROL_ID;
4430 END IF;
4431 END IF;
4432
4433END */$$
4434DELIMITER ;
4435
4436/* Procedure structure for procedure `CG$ROLES_ins` */
4437
4438/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_ins` */;
4439
4440DELIMITER $$
4441
4442/*!50003 CREATE PROCEDURE `CG$ROLES_ins`(pROL_ID INT(38), pDO_INS BOOLEAN)
4443BEGIN
4444 -- TI 180213 : Work in Progress
4445
4446 /* EXCEPTION HANDLING Starts */
4447
4448 /*
4449 -- INS: mandatory_null --equivalent to ORA-01407
4450
4451 -- If NULL is inserted delivarately into ROL_NAME FIELD
4452 DECLARE EXIT HANDLER FOR 1048
4453 BEGIN
4454 CALL CG$ROLES_validate_mandatory(5,'CG$ROLES_ins.mandatory_null', 'INS',1048);
4455 CALL CG$ERRORS_raise_failure;
4456 END;
4457
4458 -- If NO value is inserted into ROL_NAME FIELD
4459 DECLARE EXIT HANDLER FOR 1364
4460 BEGIN
4461 CALL CG$ROLES_validate_mandatory(5,'CG$ROLES_ins.mandatory_null', 'INS',1364);
4462 CALL CG$ERRORS_raise_failure;
4463 END;
4464 */
4465
4466 -- For WHEN OTHERS exception handling
4467 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4468 BEGIN
4469 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4470 CALL CG$ERRORS_push(@text,
4471 'E',
4472 'API',
4473 @errno,
4474 'CG$ROLES_ins.others');
4475 CALL CG$ERRORS_raise_failure;
4476 END;
4477
4478 /* EXCEPTION HANDLING end */
4479
4480 CALL CG$ROLES_up_autogen_columns(pROL_ID,'INS',pDO_INS);
4481
4482 IF (pDO_INS) THEN
4483 INSERT INTO ROLES(
4484 ROL_NAME,
4485 ROL_DESCRIPTION,
4486 ROL_INSERTEDBY,
4487 ROL_UPDATEDBY,
4488 ROL_CREATED,
4489 ROL_CREATEDBY,
4490 ROL_MODIFIED,
4491 ROL_MODIFIEDBY)
4492 VALUES(
4493 @vitROL_NAME,
4494 @vitROL_DESCRIPTION,
4495 @vitROL_INSERTEDBY,
4496 @vitROL_UPDATEDBY,
4497 @viROL_CREATED,
4498 @viROL_CREATEDBY,
4499 @viROL_MODIFIED,
4500 @viROL_MODIFIEDBY);
4501 END IF;
4502
4503END */$$
4504DELIMITER ;
4505
4506/* Procedure structure for procedure `CG$ROLES_ins_TEST` */
4507
4508/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_ins_TEST` */;
4509
4510DELIMITER $$
4511
4512/*!50003 CREATE PROCEDURE `CG$ROLES_ins_TEST`(pROL_ID INT(38), pDO_INS BOOLEAN)
4513BEGIN
4514 -- TI 180213 : Work in Progress
4515 DECLARE vitROL_NAME VARCHAR(100);
4516 DECLARE vitTINT INT(38);
4517
4518 /* EXCEPTION HANDLING Starts */
4519
4520 -- upd_mandatory_null --equivalent to ORA-01407
4521 /*DECLARE EXIT HANDLER FOR 1048
4522 BEGIN
4523 CALL CG$ROLES_validate_mandatory(5,'CG$ROLES_ins.mandatory_null', 'INS',1048);
4524 CALL CG$ERRORS_raise_failure;
4525 END;
4526
4527 DECLARE EXIT HANDLER FOR 1364
4528 BEGIN
4529 CALL CG$ROLES_validate_mandatory(5,'CG$ROLES_ins.mandatory_null', 'INS',1364);
4530 CALL CG$ERRORS_raise_failure;
4531 END;*/
4532
4533 -- For WHEN OTHERS exception handling
4534 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4535 BEGIN
4536 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4537 CALL CG$ERRORS_push(@text,
4538 'E',
4539 'API',
4540 @errno,
4541 'CG$ROLES_ins.others');
4542 CALL CG$ERRORS_raise_failure;
4543 END;
4544
4545 /* EXCEPTION HANDLING end */
4546
4547 SET @vitROL_NAME = null;
4548 SET vitTINT = 2;
4549
4550 IF (pDO_INS) THEN
4551 INSERT INTO ROLES_BAK(
4552 #ROL_NAME,
4553 TINT
4554 )
4555 VALUES(
4556 #@vitROL_NAME,
4557 @vitTINT);
4558 END IF;
4559
4560END */$$
4561DELIMITER ;
4562
4563/* Procedure structure for procedure `CG$ROLES_lck` */
4564
4565/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_lck` */;
4566
4567DELIMITER $$
4568
4569/*!50003 CREATE PROCEDURE `CG$ROLES_lck`(pROL_ID int(38), nowait_flag BOOLEAN)
4570BEGIN
4571 -- TI 180212 : Work in Progress
4572 DECLARE vROL_ID int(38);
4573 DECLARE vROL_NAME VARCHAR(50);
4574 DECLARE vROL_DESCRIPTION VARCHAR(255);
4575 DECLARE vROL_INSERTEDBY VARCHAR(50);
4576 DECLARE vROL_UPDATEDBY VARCHAR(50);
4577 DECLARE vROL_CREATED DATETIME(6);
4578 DECLARE vROL_CREATEDBY VARCHAR(50);
4579 DECLARE vROL_MODIFIED DATETIME(6);
4580 DECLARE vROL_MODIFIEDBY VARCHAR(50);
4581
4582 /* EXCEPTION HANDLING Starts */
4583
4584 -- For WHEN OTHERS exception handling
4585 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4586 BEGIN
4587 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4588 CALL CG$ERRORS_push(@text,
4589 'E',
4590 'API',
4591 @errno,
4592 'CG$ROLES_lck.others');
4593 CALL CG$ERRORS_raise_failure;
4594 END;
4595
4596 /* EXCEPTION HANDLING end */
4597
4598 IF (nowait_flag) THEN
4599 #LOCK is not allowed in stored procedures : LOCK outside the procedure
4600 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
4601 IF pROL_ID IS NOT NULL THEN
4602 SELECT
4603 ROL_ID,
4604 ROL_NAME,
4605 ROL_DESCRIPTION,
4606 ROL_INSERTEDBY,
4607 ROL_UPDATEDBY,
4608 ROL_CREATED,
4609 ROL_CREATEDBY,
4610 ROL_MODIFIED,
4611 ROL_MODIFIEDBY
4612
4613 INTO @vROL_ID,
4614 @vROL_NAME,
4615 @vROL_DESCRIPTION,
4616 @vROL_INSERTEDBY,
4617 @vROL_UPDATEDBY,
4618 @vROL_CREATED,
4619 @vROL_CREATEDBY,
4620 @vROL_MODIFIED,
4621 @vROL_MODIFIEDBY
4622
4623 FROM ROLES
4624 WHERE ROLES.ROL_ID = pROL_ID
4625 FOR UPDATE;
4626 END IF;
4627 END IF;
4628 END */$$
4629DELIMITER ;
4630
4631/* Procedure structure for procedure `CG$ROLES_slct` */
4632
4633/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_slct` */;
4634
4635DELIMITER $$
4636
4637/*!50003 CREATE PROCEDURE `CG$ROLES_slct`(pROL_ID int(38))
4638BEGIN
4639 -- TI 180212 : Work in Progress
4640 DECLARE vROL_ID int(38);
4641 DECLARE vROL_NAME VARCHAR(50);
4642 DECLARE vROL_DESCRIPTION VARCHAR(255);
4643 DECLARE vROL_INSERTEDBY VARCHAR(50);
4644 DECLARE vROL_UPDATEDBY VARCHAR(50);
4645 DECLARE vROL_CREATED DATETIME(6);
4646 DECLARE vROL_CREATEDBY VARCHAR(50);
4647 DECLARE vROL_MODIFIED DATETIME(6);
4648 DECLARE vROL_MODIFIEDBY VARCHAR(50);
4649
4650 /* EXCEPTION HANDLING Starts */
4651 -- any other error
4652 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4653 BEGIN
4654 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
4655 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4656 CALL CG$ERRORS_push(@text,
4657 'E',
4658 'API',
4659 @errno,
4660 'CG$ROLES_slct.others');
4661 CALL CG$ERRORS_raise_failure;
4662 END;
4663
4664 /* EXCEPTION HANDLING end */
4665
4666 IF pROL_ID IS NOT NULL THEN
4667 SELECT
4668 ROL_ID,
4669 ROL_NAME,
4670 ROL_DESCRIPTION,
4671 ROL_INSERTEDBY,
4672 ROL_UPDATEDBY,
4673 ROL_CREATED,
4674 ROL_CREATEDBY,
4675 ROL_MODIFIED,
4676 ROL_MODIFIEDBY
4677
4678 INTO
4679 @vROL_ID,
4680 @vROL_NAME,
4681 @vROL_DESCRIPTION,
4682 @vROL_INSERTEDBY,
4683 @vROL_UPDATEDBY,
4684 @vROL_CREATED,
4685 @vROL_CREATEDBY,
4686 @vROL_MODIFIED,
4687 @vROL_MODIFIEDBY
4688
4689 FROM ROLES
4690 WHERE ROLES.ROL_ID = pROL_ID;
4691 END IF;
4692 END */$$
4693DELIMITER ;
4694
4695/* Procedure structure for procedure `CG$ROLES_upd` */
4696
4697/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_upd` */;
4698
4699DELIMITER $$
4700
4701/*!50003 CREATE PROCEDURE `CG$ROLES_upd`(pROL_ID INT(38), pDO_UPD BOOLEAN)
4702BEGIN
4703 -- TI 180212 : Work in Progress
4704
4705 /* EXCEPTION HANDLING Starts */
4706
4707 -- For WHEN OTHERS exception handling
4708 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4709 BEGIN
4710 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4711 CALL CG$ERRORS_push(@text,
4712 'E',
4713 'API',
4714 @errno,
4715 'CG$ROLES_upd.others');
4716 CALL CG$ERRORS_raise_failure;
4717 END;
4718
4719 /* EXCEPTION HANDLING end */
4720
4721 /* Not Needed Anymore
4722 * Checking: IF NEW COLUMN VALUE IS NOT NULL : O/W RAISE EXCEPTION
4723 * IF @vutROL_NAME IS NOT NULL AND (CHAR_LENGTH(TRIM(@vutROL_NAME)) > 0) THEN
4724 * SET @vuROL_NAME = @vutROL_NAME;
4725 * ELSE
4726 * SIGNAL SQLSTATE '22004' SET
4727 * MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column(ROL_NAME)';
4728 * END IF;
4729 */
4730
4731 /*
4732 * Fetching VALUE OF MODIFIED & MODIFIEDBY
4733 */
4734 CALL CG$ROLES_up_autogen_columns(pROL_ID,'UPD',pDO_UPD);
4735
4736 IF (pDO_UPD) THEN
4737 UPDATE
4738 ROLES
4739 SET
4740 ROL_NAME = @vutROL_NAME,
4741 ROL_DESCRIPTION = @vutROL_DESCRIPTION,
4742 ROL_INSERTEDBY = @vutROL_INSERTEDBY,
4743 ROL_UPDATEDBY = @vutROL_UPDATEDBY,
4744 ROL_MODIFIED = @viROL_MODIFIED,
4745 ROL_MODIFIEDBY = @viROL_MODIFIEDBY
4746 WHERE
4747 ROL_ID = pROL_ID;
4748 END IF;
4749
4750 #CALL CG$ROLES_slct(pROL_ID);
4751
4752END */$$
4753DELIMITER ;
4754
4755/* Procedure structure for procedure `CG$ROLES_up_autogen_columns` */
4756
4757/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_up_autogen_columns` */;
4758
4759DELIMITER $$
4760
4761/*!50003 CREATE PROCEDURE `CG$ROLES_up_autogen_columns`(pROL_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
4762BEGIN
4763 -- TI 180213 : Work in Progress
4764 DECLARE viROL_CREATED DATETIME(6);
4765 DECLARE viROL_CREATEDBY VARCHAR(50);
4766 DECLARE viROL_MODIFIED DATETIME(6);
4767 DECLARE viROL_MODIFIEDBY VARCHAR(50);
4768
4769 /* EXCEPTION HANDLING Starts */
4770
4771 -- For WHEN OTHERS exception handling
4772 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4773 BEGIN
4774 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4775 CALL CG$ERRORS_push(@text,
4776 'E',
4777 'API',
4778 @errno,
4779 'CG$ROLES_up_autogen_columns.others');
4780 CALL CG$ERRORS_raise_failure;
4781 END;
4782
4783 /* EXCEPTION HANDLING end */
4784
4785 /*
4786 * MariaDB does not have DEFAULT variable. So IFNULL is used
4787 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
4788 */
4789 SET operation = IFNULL(operation, 'INS');
4790
4791 IF (operation = 'INS') THEN
4792 /*
4793 * ROL_ID is already an Auto-Increment field
4794 * IF (pROL_ID IS NOT NULL AND @vitROL_CREATED = TRUE) THEN
4795 * SELECT LAST_INSERT_ID() INTO @vitROL_ID FROM ROLES;
4796 * END IF;
4797 */
4798
4799 SET @viROL_CREATED = SYSDATE();
4800 SET @viROL_CREATEDBY = USER();
4801
4802 END IF;
4803
4804 SET @viROL_MODIFIED = SYSDATE();
4805 SET @viROL_MODIFIEDBY = USER();
4806
4807END */$$
4808DELIMITER ;
4809
4810/* Procedure structure for procedure `CG$ROLES_validate_mandatory` */
4811
4812/*!50003 DROP PROCEDURE IF EXISTS `CG$ROLES_validate_mandatory` */;
4813
4814DELIMITER $$
4815
4816/*!50003 CREATE PROCEDURE `CG$ROLES_validate_mandatory`(pROL_ID INT(38),
4817 loc VARCHAR(240),
4818 pOPERATION VARCHAR(3),
4819 pERRORNO INT(4))
4820BEGIN
4821 -- Column default prompts. Format PSEQNO_COL
4822 DECLARE vMAND_COL1 VARCHAR(240);
4823
4824 -- Error messages declaration
4825 DECLARE API_MAND_COLUMN_ISNULL INTEGER;
4826 DECLARE VAL_MAND VARCHAR(240);
4827
4828 SET vMAND_COL1 := 'ROL_NAME';
4829
4830 -- Error messages SET
4831 SET API_MAND_COLUMN_ISNULL := pERRORNO;
4832 SET VAL_MAND := 'Value for <p1> is required';
4833
4834 /*
4835 * MariaDB does not have DEFAULT variable. So IFNULL is used
4836 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
4837 */
4838 SET loc = IFNULL(loc, '');
4839 IF (pOPERATION = 'INS') THEN
4840 IF (@vitROL_NAME IS NULL OR (CHAR_LENGTH(TRIM(@vitROL_NAME)) = 0)) THEN
4841 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, vMAND_COL1, NULL, NULL, NULL),
4842 'E',
4843 'API',
4844 API_MAND_COLUMN_ISNULL,
4845 loc);
4846 END IF;
4847 ELSEIF (pOPERATION = 'UPD') THEN
4848 IF (@vutROL_NAME IS NULL OR (CHAR_LENGTH(TRIM(@vutROL_NAME)) = 0)) THEN
4849 CALL CG$ERRORS_push(CG$ERRORS_MsgGetText(API_MAND_COLUMN_ISNULL, VAL_MAND, vMAND_COL1, NULL, NULL, NULL),
4850 'E',
4851 'API',
4852 API_MAND_COLUMN_ISNULL,
4853 loc);
4854 END IF;
4855 END IF;
4856END */$$
4857DELIMITER ;
4858
4859/* Procedure structure for procedure `CG$SCHEDULING_del` */
4860
4861/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_del` */;
4862
4863DELIMITER $$
4864
4865/*!50003 CREATE PROCEDURE `CG$SCHEDULING_del`(pSCH_ID INT(38), pDO_DEL BOOLEAN)
4866BEGIN
4867 -- AR 180215 : Work in Progress
4868 /* EXCEPTION HANDLING start */
4869 -- OTHER exceptions
4870 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4871 BEGIN
4872
4873 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4874
4875 CALL CG$ERRORS_push(@text,
4876 'E',
4877 'API',
4878 @errno,
4879 'cg$SCHEDULING_del.others'
4880 );
4881
4882 CALL CG$ERRORS_raise_failure;
4883
4884 END;
4885 /* EXCEPTION HANDLING end */
4886
4887 IF (pDO_DEL) THEN
4888
4889 #SELECT CONCAT('Deleted ID: ', vSCH_ID);
4890 IF pSCH_ID IS NOT NULL THEN
4891
4892 DELETE FROM SCHEDULING WHERE SCH_ID = pSCH_ID;
4893
4894 END IF;
4895
4896 END IF;
4897
4898END */$$
4899DELIMITER ;
4900
4901/* Procedure structure for procedure `CG$SCHEDULING_ins` */
4902
4903/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_ins` */;
4904
4905DELIMITER $$
4906
4907/*!50003 CREATE PROCEDURE `CG$SCHEDULING_ins`(pSCH_ID INT(38), pDO_INS BOOLEAN)
4908BEGIN
4909
4910 -- AR 180215 : Work in Progress
4911/* EXCEPTION HANDLING start */
4912 -- OTHER exceptions
4913 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4914 BEGIN
4915
4916 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4917
4918 CALL CG$ERRORS_push(@text,
4919 'E',
4920 'API',
4921 @errno,
4922 'cg$SCHEDULING_ins.others'
4923 );
4924
4925 CALL CG$ERRORS_raise_failure;
4926
4927 END;
4928 /* EXCEPTION HANDLING end */
4929
4930 CALL CG$SCHEDULING_up_autogen_columns(pSCH_ID,'INS',pDO_INS);
4931
4932 IF (pDO_INS) THEN
4933
4934 INSERT INTO SCHEDULING(
4935 SCH_TIME,
4936 SCH_TASK,
4937 SCH_RESULT,
4938 SCH_STATUS,
4939 SCH_INSERTEDBY,
4940 SCH_CREATED,
4941 SCH_CREATEDBY
4942 )
4943 VALUES(
4944 @vitSCH_TIME,
4945 @vitSCH_TASK,
4946 @vitSCH_RESULT,
4947 @vitSCH_STATUS,
4948 @vitSCH_INSERTEDBY,
4949 @viSCH_CREATED,
4950 @viSCH_CREATEDBY
4951 );
4952
4953 END IF;
4954
4955END */$$
4956DELIMITER ;
4957
4958/* Procedure structure for procedure `CG$SCHEDULING_lck` */
4959
4960/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_lck` */;
4961
4962DELIMITER $$
4963
4964/*!50003 CREATE PROCEDURE `CG$SCHEDULING_lck`(pSCH_ID INT(38), nowait_flag BOOLEAN)
4965BEGIN
4966 -- AR 180215 : Work in Progress
4967 DECLARE vSCH_ID INT(38);
4968 DECLARE vSCH_TIME DATETIME(6);
4969 DECLARE vSCH_TASK TEXT;
4970 DECLARE vSCH_RESULT TEXT;
4971 DECLARE vSCH_STATUS VARCHAR(1);
4972 DECLARE vSCH_INSERTEDBY VARCHAR(50);
4973 DECLARE vSCH_CREATED DATETIME(6);
4974 DECLARE vSCH_CREATEDBY VARCHAR(30);
4975 /* EXCEPTION HANDLING start */
4976 -- OTHER exceptions
4977 DECLARE EXIT HANDLER FOR SQLEXCEPTION
4978 BEGIN
4979
4980 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
4981
4982 CALL CG$ERRORS_push(@text,
4983 'E',
4984 'API',
4985 @errno,
4986 'cg$SCHEDULING_lck.others'
4987 );
4988
4989 CALL CG$ERRORS_raise_failure;
4990
4991 END;
4992 /* EXCEPTION HANDLING end */
4993
4994
4995
4996 IF (nowait_flag) THEN
4997
4998 #LOCK is not allowed in stored procedures : LOCK outside the procedure
4999 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
5000 IF pSCH_ID IS NOT NULL THEN
5001
5002 SELECT
5003 SCH_ID,
5004 SCH_TIME,
5005 SCH_TASK,
5006 SCH_RESULT,
5007 SCH_STATUS,
5008 SCH_INSERTEDBY,
5009 SCH_CREATED,
5010 SCH_CREATEDBY
5011 INTO
5012 @vSCH_ID,
5013 @vSCH_TIME,
5014 @vSCH_TASK,
5015 @vSCH_RESULT,
5016 @vSCH_STATUS,
5017 @vSCH_INSERTEDBY,
5018 @vSCH_CREATED,
5019 @vSCH_CREATEDBY
5020 FROM SCHEDULING
5021 WHERE SCHEDULING.SCH_ID = pSCH_ID
5022 FOR UPDATE;
5023
5024 END IF;
5025
5026 END IF;
5027
5028END */$$
5029DELIMITER ;
5030
5031/* Procedure structure for procedure `CG$SCHEDULING_slct` */
5032
5033/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_slct` */;
5034
5035DELIMITER $$
5036
5037/*!50003 CREATE PROCEDURE `CG$SCHEDULING_slct`(pSCH_ID INT(38))
5038BEGIN
5039 -- AR 180215 : Work in Progress
5040 DECLARE vSCH_ID INT(38);
5041 DECLARE vSCH_TIME DATETIME(6);
5042 DECLARE vSCH_TASK TEXT;
5043 DECLARE vSCH_RESULT TEXT;
5044 DECLARE vSCH_STATUS VARCHAR(1);
5045 DECLARE vSCH_INSERTEDBY VARCHAR(50);
5046 DECLARE vSCH_CREATED DATETIME(6);
5047 DECLARE vSCH_CREATEDBY VARCHAR(30);
5048 /* EXCEPTION HANDLING start */
5049 -- OTHER exceptions
5050 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5051 BEGIN
5052
5053 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5054
5055 CALL CG$ERRORS_push(@text,
5056 'E',
5057 'API',
5058 @errno,
5059 'cg$SCHEDULING_slct.others'
5060 );
5061
5062 CALL CG$ERRORS_raise_failure;
5063
5064 END;
5065 /* EXCEPTION HANDLING end */
5066
5067
5068
5069 IF pSCH_ID IS NOT NULL THEN
5070
5071 SELECT
5072 SCH_ID,
5073 SCH_TIME,
5074 SCH_TASK,
5075 SCH_RESULT,
5076 SCH_STATUS,
5077 SCH_INSERTEDBY,
5078 SCH_CREATED,
5079 SCH_CREATEDBY
5080 INTO
5081 @vSCH_ID,
5082 @vSCH_TIME,
5083 @vSCH_TASK,
5084 @vSCH_RESULT,
5085 @vSCH_STATUS,
5086 @vSCH_INSERTEDBY,
5087 @vSCH_CREATED,
5088 @vSCH_CREATEDBY
5089 FROM SCHEDULING
5090 WHERE SCHEDULING.SCH_ID = pSCH_ID;
5091
5092 END IF;
5093
5094END */$$
5095DELIMITER ;
5096
5097/* Procedure structure for procedure `CG$SCHEDULING_upd` */
5098
5099/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_upd` */;
5100
5101DELIMITER $$
5102
5103/*!50003 CREATE PROCEDURE `CG$SCHEDULING_upd`(pSCH_ID INT(38), pDO_UPD BOOLEAN)
5104BEGIN
5105 -- AR 180215 : Work in Progress
5106/* EXCEPTION HANDLING start */
5107 -- OTHER exceptions
5108 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5109 BEGIN
5110
5111 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5112
5113 CALL CG$ERRORS_push(@text,
5114 'E',
5115 'API',
5116 @errno,
5117 'cg$SCHEDULING_upd.others'
5118 );
5119
5120 CALL CG$ERRORS_raise_failure;
5121
5122 END;
5123 /* EXCEPTION HANDLING end */
5124
5125 #CALL CG$SCHEDULING_slct(pSCH_ID);
5126
5127 /*
5128 * Fetching VALUE OF MODIFIED & MODIFIEDBY
5129 */
5130 CALL CG$SCHEDULING_up_autogen_columns(pSCH_ID,'UPD',pDO_UPD);
5131
5132 IF (pDO_UPD) THEN
5133
5134 UPDATE
5135 SCHEDULING
5136 SET
5137 SCH_TIME = @vutSCH_TIME,
5138 SCH_TASK = @vutSCH_TASK,
5139 SCH_RESULT = @vutSCH_RESULT,
5140 SCH_STATUS = @vutSCH_STATUS,
5141 SCH_INSERTEDBY = @vutSCH_INSERTEDBY,
5142 SCH_CREATED = @viSCH_CREATED,
5143 SCH_CREATEDBY = @viSCH_CREATEDBY
5144 WHERE
5145 SCH_ID = pSCH_ID;
5146
5147 END IF;
5148
5149 #CALL CG$SCHEDULING_slct(pSCH_ID);
5150
5151END */$$
5152DELIMITER ;
5153
5154/* Procedure structure for procedure `CG$SCHEDULING_up_autogen_columns` */
5155
5156/*!50003 DROP PROCEDURE IF EXISTS `CG$SCHEDULING_up_autogen_columns` */;
5157
5158DELIMITER $$
5159
5160/*!50003 CREATE PROCEDURE `CG$SCHEDULING_up_autogen_columns`(pSCH_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
5161BEGIN
5162
5163 -- AR 180215 : Work in Progress
5164 DECLARE viSCH_CREATED DATETIME(6);
5165 DECLARE viSCH_CREATEDBY VARCHAR(50);
5166
5167 /* EXCEPTION HANDLING start */
5168 -- OTHER exceptions
5169 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5170 BEGIN
5171
5172 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5173
5174 CALL CG$ERRORS_push(@text,
5175 'E',
5176 'API',
5177 @errno,
5178 'cg$SCHEDULING_up_autogen_columns.others'
5179 );
5180
5181 CALL CG$ERRORS_raise_failure;
5182
5183 END;
5184 /* EXCEPTION HANDLING end */
5185
5186
5187 /*
5188 * MariaDB does not have DEFAULT variable. So IFNULL is used
5189 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
5190 */
5191 SET operation = IFNULL(operation, 'INS');
5192
5193 IF (operation = 'INS') THEN
5194 /*
5195 * SCH_ID is already an Auto-Increment field
5196 * IF (pSCH_ID IS NOT NULL AND @vitSCH_CREATED = TRUE) THEN
5197 * SELECT LAST_INSERT_ID() INTO @vitSCH_ID FROM SCHEDULING;
5198 * END IF;
5199 */
5200
5201 SET @viSCH_CREATED = SYSDATE();
5202 SET @viSCH_CREATEDBY = USER();
5203
5204 END IF;
5205
5206END */$$
5207DELIMITER ;
5208
5209/* Procedure structure for procedure `CG$USERROLES_ins` */
5210
5211/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_ins` */;
5212
5213DELIMITER $$
5214
5215/*!50003 CREATE PROCEDURE `CG$USERROLES_ins`(pURO_ID INT(38), pDO_INS BOOLEAN)
5216BEGIN
5217 -- TI 180213 : Work in Progress
5218
5219 /* EXCEPTION HANDLING start */
5220 -- OTHER exceptions
5221 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5222 BEGIN
5223 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5224 CALL CG$ERRORS_push(@text,
5225 'E',
5226 'API',
5227 @errno,
5228 'cg$USERROLES_ins.others');
5229 CALL CG$ERRORS_raise_failure;
5230 END;
5231 /* EXCEPTION HANDLING end */
5232
5233 CALL CG$USERROLES_up_autogen_columns(pURO_ID,'INS',pDO_INS);
5234
5235 IF (pDO_INS) THEN
5236 INSERT INTO USERROLES(
5237 URO_ROL_ID,
5238 URO_USR_ID)
5239 VALUES(
5240 @vitURO_ROL_ID,
5241 @vitURO_USR_ID);
5242 END IF;
5243
5244END */$$
5245DELIMITER ;
5246
5247/* Procedure structure for procedure `CG$USERROLES_lck` */
5248
5249/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_lck` */;
5250
5251DELIMITER $$
5252
5253/*!50003 CREATE PROCEDURE `CG$USERROLES_lck`(pURO_ID INT(38), nowait_flag BOOLEAN)
5254BEGIN
5255 -- TI 180212 : Work in Progress
5256 DECLARE vURO_ID int(38);
5257 DECLARE vURO_ROL_ID int(38);
5258 DECLARE vURO_USR_ID int(38);
5259
5260 /* EXCEPTION HANDLING start */
5261 -- OTHER exceptions
5262 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5263 BEGIN
5264 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5265 CALL CG$ERRORS_push(@text,
5266 'E',
5267 'API',
5268 @errno,
5269 'cg$USERROLES_lck.others');
5270 CALL CG$ERRORS_raise_failure;
5271 END;
5272 /* EXCEPTION HANDLING end */
5273
5274 IF (nowait_flag) THEN
5275 #LOCK is not allowed in stored procedures : LOCK outside the procedure
5276 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
5277 IF pURO_ID IS NOT NULL THEN
5278 SELECT
5279 URO_ID,
5280 URO_ROL_ID,
5281 URO_USR_ID
5282
5283 INTO
5284 @vURO_ID,
5285 @vURO_ROL_ID,
5286 @vURO_USR_ID
5287
5288 FROM USERROLES
5289 WHERE USERROLES.URO_ID = pURO_ID
5290 FOR UPDATE;
5291 END IF;
5292 END IF;
5293 END */$$
5294DELIMITER ;
5295
5296/* Procedure structure for procedure `CG$USERROLES_slct` */
5297
5298/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_slct` */;
5299
5300DELIMITER $$
5301
5302/*!50003 CREATE PROCEDURE `CG$USERROLES_slct`(pURO_ID INT(38))
5303BEGIN
5304 -- TI 180212 : Work in Progress
5305 DECLARE vURO_ID int(38);
5306 DECLARE vURO_ROL_ID int(38);
5307 DECLARE vURO_USR_ID int(38);
5308
5309 /* EXCEPTION HANDLING start */
5310 -- OTHER exceptions
5311 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5312 BEGIN
5313 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5314 CALL CG$ERRORS_push(@text,
5315 'E',
5316 'API',
5317 @errno,
5318 'cg$USERROLES_slct.others');
5319 CALL CG$ERRORS_raise_failure;
5320 END;
5321 /* EXCEPTION HANDLING end */
5322
5323 IF pROL_ID IS NOT NULL THEN
5324 SELECT
5325 URO_ID,
5326 URO_ROL_ID,
5327 URO_USR_ID
5328
5329 INTO
5330 @vURO_ID,
5331 @vURO_ROL_ID,
5332 @vURO_USR_ID
5333
5334 FROM USERROLES
5335 WHERE USERROLES.URO_ID = pURO_ID;
5336 END IF;
5337 END */$$
5338DELIMITER ;
5339
5340/* Procedure structure for procedure `CG$USERROLES_upd` */
5341
5342/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_upd` */;
5343
5344DELIMITER $$
5345
5346/*!50003 CREATE PROCEDURE `CG$USERROLES_upd`(pURO_ID INT(38), pDO_UPD BOOLEAN)
5347BEGIN
5348 -- TI 180212 : Work in Progress
5349
5350 /* EXCEPTION HANDLING start */
5351 -- OTHER exceptions
5352 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5353 BEGIN
5354 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5355 CALL CG$ERRORS_push(@text,
5356 'E',
5357 'API',
5358 @errno,
5359 'cg$USERROLES_upd.others');
5360 CALL CG$ERRORS_raise_failure;
5361 END;
5362 /* EXCEPTION HANDLING end */
5363
5364 /* Not Needed Anymore
5365 * Checking: IF NEW COLUMN VALUE IS NOT NULL : O/W RAISE EXCEPTION
5366 * IF @vutURO_NAME IS NOT NULL AND (CHAR_LENGTH(TRIM(@vutURO_NAME)) > 0) THEN
5367 * SET @vuURO_NAME = @vutURO_NAME;
5368 * ELSE
5369 * SIGNAL SQLSTATE '22004' SET
5370 * MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column(URO_NAME)';
5371 * END IF;
5372 */
5373
5374 /*
5375 * Fetching VALUE OF MODIFIED & MODIFIEDBY
5376 */
5377 CALL CG$USERROLES_up_autogen_columns(pURO_ID,'UPD',pDO_UPD);
5378
5379 IF (pDO_UPD) THEN
5380 UPDATE
5381 USERROLES
5382 SET
5383 URO_ROL_ID = @vutURO_ROL_ID,
5384 URO_USR_ID = @vutURO_USR_ID
5385 WHERE
5386 URO_ID = pURO_ID;
5387 END IF;
5388
5389 #CALL CG$USERROLES_slct(pURO_ID);
5390
5391END */$$
5392DELIMITER ;
5393
5394/* Procedure structure for procedure `CG$USERROLES_up_autogen_columns` */
5395
5396/*!50003 DROP PROCEDURE IF EXISTS `CG$USERROLES_up_autogen_columns` */;
5397
5398DELIMITER $$
5399
5400/*!50003 CREATE PROCEDURE `CG$USERROLES_up_autogen_columns`(pURO_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
5401BEGIN
5402 -- TI 180213 : Work in Progress
5403 DECLARE viURO_CREATED DATETIME(6);
5404 DECLARE viURO_CREATEDBY VARCHAR(50);
5405 DECLARE viURO_MODIFIED DATETIME(6);
5406 DECLARE viURO_MODIFIEDBY VARCHAR(50);
5407
5408 /* EXCEPTION HANDLING start */
5409 -- OTHER exceptions
5410 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5411 BEGIN
5412 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5413 CALL CG$ERRORS_push(@text,
5414 'E',
5415 'API',
5416 @errno,
5417 'cg$USERROLES_up_autogen_columns.others');
5418 CALL CG$ERRORS_raise_failure;
5419 END;
5420 /* EXCEPTION HANDLING end */
5421
5422 /*
5423 * MariaDB does not have DEFAULT variable. So IFNULL is used
5424 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
5425 */
5426 SET operation = IFNULL(operation, 'INS');
5427
5428 IF (operation = 'INS') THEN
5429 /*
5430 * URO_ID is already an Auto-Increment field
5431 * IF (pURO_ID IS NOT NULL AND @vitURO_CREATED = TRUE) THEN
5432 * SELECT LAST_INSERT_ID() INTO @vitURO_ID FROM USERROLES;
5433 * END IF;
5434 */
5435
5436 SET @viURO_CREATED = SYSDATE();
5437 SET @viURO_CREATEDBY = USER();
5438
5439 END IF;
5440
5441 SET @viURO_MODIFIED = SYSDATE();
5442 SET @viURO_MODIFIEDBY = USER();
5443
5444END */$$
5445DELIMITER ;
5446
5447/* Procedure structure for procedure `CG$USERS_del` */
5448
5449/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_del` */;
5450
5451DELIMITER $$
5452
5453/*!50003 CREATE PROCEDURE `CG$USERS_del`(pUSR_ID INT(38), pDO_DEL BOOLEAN)
5454BEGIN
5455 /* EXCEPTION HANDLING start */
5456
5457 -- OTHER exceptions
5458 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5459 BEGIN
5460 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5461 CALL CG$ERRORS_push(@text,
5462 'E',
5463 'API',
5464 @errno,
5465 'CG$USERS_del.others');
5466 CALL CG$ERRORS_raise_failure;
5467 END;
5468 /* EXCEPTION HANDLING end */
5469
5470 IF (pDO_DEL) THEN
5471 #SELECT CONCAT('Deleted ID: ', vUSR_ID);
5472 IF pUSR_ID IS NOT NULL THEN
5473 DELETE FROM USERS WHERE USR_ID = pUSR_ID;
5474 END IF;
5475 END IF;
5476
5477END */$$
5478DELIMITER ;
5479
5480/* Procedure structure for procedure `CG$USERS_ins` */
5481
5482/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_ins` */;
5483
5484DELIMITER $$
5485
5486/*!50003 CREATE PROCEDURE `CG$USERS_ins`(pUSR_ID INT(38), pDO_INS BOOLEAN)
5487BEGIN
5488 -- TI 180213 : Work in Progress
5489
5490 /* EXCEPTION HANDLING start */
5491 -- OTHER exceptions
5492 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5493 BEGIN
5494 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5495 CALL CG$ERRORS_push(@text,
5496 'E',
5497 'API',
5498 @errno,
5499 'cg$USERS_ins.others');
5500 CALL CG$ERRORS_raise_failure;
5501 END;
5502 /* EXCEPTION HANDLING end */
5503
5504 CALL CG$USERS_up_autogen_columns(pUSR_ID,'INS',pDO_INS);
5505
5506 IF (pDO_INS) THEN
5507 INSERT INTO USERS(
5508 USR_USRID,
5509 USR_EMAIL,
5510 USR_FIRSTNAME,
5511 USR_LASTNAME,
5512 USR_INSERTEDBY,
5513 USR_UPDATEDBY,
5514 USR_CREATED,
5515 USR_CREATEDBY,
5516 USR_MODIFIED,
5517 USR_MODIFIEDBY,
5518 USR_COUNTRY,
5519 USR_LOCATION)
5520 VALUES(
5521 @vitUSR_USRID,
5522 @vitUSR_EMAIL,
5523 @vitUSR_FIRSTNAME,
5524 @vitUSR_LASTNAME,
5525 @vitUSR_INSERTEDBY,
5526 @vitUSR_UPDATEDBY,
5527 @viUSR_CREATED,
5528 @viUSR_CREATEDBY,
5529 @viUSR_MODIFIED,
5530 @viUSR_MODIFIEDBY,
5531 @vitUSR_COUNTRY,
5532 @vitUSR_LOCATION);
5533 END IF;
5534
5535END */$$
5536DELIMITER ;
5537
5538/* Procedure structure for procedure `CG$USERS_lck` */
5539
5540/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_lck` */;
5541
5542DELIMITER $$
5543
5544/*!50003 CREATE PROCEDURE `CG$USERS_lck`(pUSR_ID INT(38), nowait_flag BOOLEAN)
5545BEGIN
5546 -- TI 180212 : Work in Progress
5547 DECLARE vUSR_ID INT(38);
5548 DECLARE vUSR_USRID VARCHAR(30);
5549 DECLARE vUSR_EMAIL VARCHAR(100);
5550 DECLARE vUSR_FIRSTNAME VARCHAR(50);
5551 DECLARE vUSR_LASTNAME VARCHAR(50);
5552 DECLARE vUSR_INSERTEDBY VARCHAR(50);
5553 DECLARE vUSR_UPDATEDBY VARCHAR(50);
5554 DECLARE vUSR_CREATED DATETIME(6);
5555 DECLARE vUSR_CREATEDBY VARCHAR(50);
5556 DECLARE vUSR_MODIFIED DATETIME(6);
5557 DECLARE vUSR_MODIFIEDBY VARCHAR(50);
5558 DECLARE vUSR_COUNTRY VARCHAR(200);
5559 DECLARE vUSR_LOCATION VARCHAR(200);
5560
5561 /* EXCEPTION HANDLING start */
5562 -- OTHER exceptions
5563 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5564 BEGIN
5565 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5566 CALL CG$ERRORS_push(@text,
5567 'E',
5568 'API',
5569 @errno,
5570 'cg$USERS_lck.others');
5571 CALL CG$ERRORS_raise_failure;
5572 END;
5573 /* EXCEPTION HANDLING end */
5574
5575 IF (nowait_flag) THEN
5576 #LOCK is not allowed in stored procedures : LOCK outside the procedure
5577 #LOCK TABLE test_table WRITE; SELECT * from test_table; DELETE FROM test_table ; UNLOCK TABLES;
5578 IF pUSR_ID IS NOT NULL THEN
5579 SELECT
5580 USR_ID,
5581 USR_USRID,
5582 USR_EMAIL,
5583 USR_FIRSTNAME,
5584 USR_LASTNAME,
5585 USR_INSERTEDBY,
5586 USR_UPDATEDBY,
5587 USR_CREATED,
5588 USR_CREATEDBY,
5589 USR_MODIFIED,
5590 USR_MODIFIEDBY,
5591 USR_COUNTRY,
5592 USR_LOCATION
5593
5594 INTO
5595 @vUSR_ID,
5596 @vUSR_USRID,
5597 @vUSR_EMAIL,
5598 @vUSR_FIRSTNAME,
5599 @vUSR_LASTNAME,
5600 @vUSR_INSERTEDBY,
5601 @vUSR_UPDATEDBY,
5602 @vUSR_CREATED,
5603 @vUSR_CREATEDBY,
5604 @vUSR_MODIFIED,
5605 @vUSR_MODIFIEDBY,
5606 @vUSR_COUNTRY,
5607 @vUSR_LOCATION
5608
5609 FROM USERS
5610 WHERE USERS.USR_ID = pUSR_ID
5611 FOR UPDATE;
5612 END IF;
5613 END IF;
5614 END */$$
5615DELIMITER ;
5616
5617/* Procedure structure for procedure `CG$USERS_slct` */
5618
5619/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_slct` */;
5620
5621DELIMITER $$
5622
5623/*!50003 CREATE PROCEDURE `CG$USERS_slct`(pUSR_ID INT(38))
5624BEGIN
5625 -- TI 180212 : Work in Progress
5626 DECLARE vUSR_ID INT(38);
5627 DECLARE vUSR_USRID VARCHAR(30);
5628 DECLARE vUSR_EMAIL VARCHAR(100);
5629 DECLARE vUSR_FIRSTNAME VARCHAR(50);
5630 DECLARE vUSR_LASTNAME VARCHAR(50);
5631 DECLARE vUSR_INSERTEDBY VARCHAR(50);
5632 DECLARE vUSR_UPDATEDBY VARCHAR(50);
5633 DECLARE vUSR_CREATED DATETIME(6);
5634 DECLARE vUSR_CREATEDBY VARCHAR(50);
5635 DECLARE vUSR_MODIFIED DATETIME(6);
5636 DECLARE vUSR_MODIFIEDBY VARCHAR(50);
5637 DECLARE vUSR_COUNTRY VARCHAR(200);
5638 DECLARE vUSR_LOCATION VARCHAR(200);
5639
5640 /* EXCEPTION HANDLING start */
5641 -- OTHER exceptions
5642 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5643 BEGIN
5644 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5645 CALL CG$ERRORS_push(@text,
5646 'E',
5647 'API',
5648 @errno,
5649 'cg$USERS_slct.others');
5650 CALL CG$ERRORS_raise_failure;
5651 END;
5652 /* EXCEPTION HANDLING end */
5653
5654 IF pUSR_ID IS NOT NULL THEN
5655 SELECT
5656 USR_ID,
5657 USR_USRID,
5658 USR_EMAIL,
5659 USR_FIRSTNAME,
5660 USR_LASTNAME,
5661 USR_INSERTEDBY,
5662 USR_UPDATEDBY,
5663 USR_CREATED,
5664 USR_CREATEDBY,
5665 USR_MODIFIED,
5666 USR_MODIFIEDBY,
5667 USR_COUNTRY,
5668 USR_LOCATION
5669
5670 INTO
5671 @vUSR_ID,
5672 @vUSR_USRID,
5673 @vUSR_EMAIL,
5674 @vUSR_FIRSTNAME,
5675 @vUSR_LASTNAME,
5676 @vUSR_INSERTEDBY,
5677 @vUSR_UPDATEDBY,
5678 @vUSR_CREATED,
5679 @vUSR_CREATEDBY,
5680 @vUSR_MODIFIED,
5681 @vUSR_MODIFIEDBY,
5682 @vUSR_COUNTRY,
5683 @vUSR_LOCATION
5684
5685 FROM USERS
5686 WHERE USERS.USR_ID = pUSR_ID;
5687 END IF;
5688 END */$$
5689DELIMITER ;
5690
5691/* Procedure structure for procedure `CG$USERS_upd` */
5692
5693/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_upd` */;
5694
5695DELIMITER $$
5696
5697/*!50003 CREATE PROCEDURE `CG$USERS_upd`(pUSR_ID INT(38), pDO_UPD BOOLEAN)
5698BEGIN
5699 -- TI 180212 : Work in Progress
5700
5701 /* EXCEPTION HANDLING start */
5702 -- OTHER exceptions
5703 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5704 BEGIN
5705 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5706 CALL CG$ERRORS_push(@text,
5707 'E',
5708 'API',
5709 @errno,
5710 'cg$USERS_upd.others');
5711 CALL CG$ERRORS_raise_failure;
5712 END;
5713 /* EXCEPTION HANDLING end */
5714
5715 /*
5716 * Not Needed Anymore
5717 * Checking: IF NEW COLUMN VALUE IS NOT NULL : O/W RAISE EXCEPTION
5718 * IF @vutUSR_NAME IS NOT NULL AND (CHAR_LENGTH(TRIM(@vutUSR_NAME)) > 0) THEN
5719 * SET @vuUSR_NAME = @vutUSR_NAME;
5720 * ELSE
5721 * SIGNAL SQLSTATE '22004' SET
5722 * MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column(USR_NAME)';
5723 * END IF;
5724 */
5725
5726 /*
5727 * Fetching VALUE OF MODIFIED & MODIFIEDBY
5728 */
5729 CALL CG$USERS_up_autogen_columns(pUSR_ID,'UPD',pDO_UPD);
5730
5731 IF (pDO_UPD) THEN
5732 UPDATE
5733 USERS
5734 SET
5735 USR_USRID = @vutUSR_USRID,
5736 USR_EMAIL = @vutUSR_EMAIL,
5737 USR_FIRSTNAME = @vutUSR_FIRSTNAME,
5738 USR_LASTNAME = @vutUSR_LASTNAME,
5739 USR_INSERTEDBY = @vutUSR_INSERTEDBY,
5740 USR_UPDATEDBY = @vutUSR_UPDATEDBY,
5741 USR_MODIFIED = @viUSR_MODIFIED,
5742 USR_MODIFIEDBY = @viUSR_MODIFIEDBY,
5743 USR_COUNTRY = @vutUSR_COUNTRY,
5744 USR_LOCATION = @vutUSR_LOCATION
5745 WHERE
5746 USR_ID = pUSR_ID;
5747 END IF;
5748
5749 #CALL CG$USERS_slct(pUSR_ID);
5750
5751END */$$
5752DELIMITER ;
5753
5754/* Procedure structure for procedure `CG$USERS_up_autogen_columns` */
5755
5756/*!50003 DROP PROCEDURE IF EXISTS `CG$USERS_up_autogen_columns` */;
5757
5758DELIMITER $$
5759
5760/*!50003 CREATE PROCEDURE `CG$USERS_up_autogen_columns`(pUSR_ID INT(38), operation VARCHAR(30),do_denorm BOOLEAN)
5761BEGIN
5762 -- TI 180213 : Work in Progress
5763 DECLARE viUSR_CREATED DATETIME(6);
5764 DECLARE viUSR_CREATEDBY VARCHAR(50);
5765 DECLARE viUSR_MODIFIED DATETIME(6);
5766 DECLARE viUSR_MODIFIEDBY VARCHAR(50);
5767
5768 /* EXCEPTION HANDLING start */
5769 -- OTHER exceptions
5770 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5771 BEGIN
5772 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5773 CALL CG$ERRORS_push(@text,
5774 'E',
5775 'API',
5776 @errno,
5777 'cg$USERS_up_autogen_columns.others');
5778 CALL CG$ERRORS_raise_failure;
5779 END;
5780 /* EXCEPTION HANDLING end */
5781
5782 /*
5783 * MariaDB does not have DEFAULT variable. So IFNULL is used
5784 * IFNULL returns 1st expression if it is not null. Otherwise it returns the 2nd expression.
5785 */
5786 SET operation = IFNULL(operation, 'INS');
5787
5788 IF (operation = 'INS') THEN
5789 /*
5790 * USR_ID is already an Auto-Increment field
5791 * IF (pUSR_ID IS NOT NULL AND @vitUSR_CREATED = TRUE) THEN
5792 * SELECT LAST_INSERT_ID() INTO @vitUSR_ID FROM USERS;
5793 * END IF;
5794 */
5795
5796 SET @viUSR_CREATED = SYSDATE();
5797 SET @viUSR_CREATEDBY = USER();
5798
5799 END IF;
5800
5801 SET @viUSR_MODIFIED = SYSDATE();
5802 SET @viUSR_MODIFIEDBY = USER();
5803
5804END */$$
5805DELIMITER ;
5806
5807/* Procedure structure for procedure `CHECK_ALERT` */
5808
5809/*!50003 DROP PROCEDURE IF EXISTS `CHECK_ALERT` */;
5810
5811DELIMITER $$
5812
5813/*!50003 CREATE PROCEDURE `CHECK_ALERT`()
5814BEGIN
5815 DECLARE NO_DATA INT DEFAULT FALSE;
5816 declare v_SAVED_SEARCH_ID int(38);
5817 declare v_NEXT_ALERT_EXECUTION datetime;
5818 DECLARE CURSOR_VAR1 CURSOR FOR select SAVED_SEARCH_ID, NEXT_ALERT_EXECUTION from SAVED_SEARCH where IS_ALERT = 1;
5819
5820 DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = TRUE; -- declared to determine end of loop
5821
5822 /* EXCEPTION HANDLING start */
5823 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5824 BEGIN
5825 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5826 CALL ELIB_JOBS_WRITE_JOBLOG('check_alert', 'F', @sqlstate);
5827 END;
5828 /* EXCEPTION HANDLING end */
5829
5830 OPEN CURSOR_VAR1;
5831
5832 CHECK_ALERT_LOOP: LOOP
5833
5834 FETCH CURSOR_VAR1 INTO v_SAVED_SEARCH_ID, v_NEXT_ALERT_EXECUTION;
5835
5836 IF NO_DATA THEN
5837 LEAVE CHECK_ALERT_LOOP;
5838 END IF;
5839
5840 IF (DATE_FORMAT(v_NEXT_ALERT_EXECUTION, '%Y-%m-%d') = DATE_FORMAT(SYSDATE(), '%Y-%m-%d')) THEN
5841
5842 -- procedure not used in MariaDB
5843 -- call send_alert(v_SAVED_SEARCH_ID);
5844
5845 UPDATE SAVED_SEARCH
5846 SET LAST_ALERT_EXECUTION = DATE_FORMAT(SYSDATE(), '%Y-%m-%d'),
5847 NEXT_ALERT_EXECUTION = (CASE WHEN UPPER(ALERT_FREQUENCY) = 'MONTHLY' THEN DATE_ADD(NEXT_ALERT_EXECUTION, INTERVAL 1 MONTH)
5848 WHEN UPPER(ALERT_FREQUENCY) = 'WEEKLY' THEN DATE_ADD(NEXT_ALERT_EXECUTION, INTERVAL 1 WEEk)
5849 WHEN UPPER(ALERT_FREQUENCY) = 'DAILY' THEN DATE_ADD(NEXT_ALERT_EXECUTION, INTERVAL 1 DAY) END )
5850 WHERE IS_ALERT = 1 AND SAVED_SEARCH_ID = v_SAVED_SEARCH_ID;
5851 COMMIT;
5852
5853 END IF;
5854
5855 END LOOP CHECK_ALERT_LOOP;
5856
5857 CLOSE CURSOR_VAR1;
5858
5859 call ELIB_JOBS_WRITE_JOBLOG('check_alert', 'C', 'ok');
5860
5861 /*
5862 * For Testing purpose
5863 * CALL CHECK_ALERT();
5864 */
5865END */$$
5866DELIMITER ;
5867
5868/* Procedure structure for procedure `DI$CUSTOMERS_sync` */
5869
5870/*!50003 DROP PROCEDURE IF EXISTS `DI$CUSTOMERS_sync` */;
5871
5872DELIMITER $$
5873
5874/*!50003 CREATE PROCEDURE `DI$CUSTOMERS_sync`(pUSR_ID INT(38), pCUS_EMAIL VARCHAR(100))
5875BEGIN
5876
5877 -- AR 180215 : Work in Progress
5878 DECLARE vCUS_FIRSTNAME VARCHAR(50);
5879 DECLARE vCUS_LASTNAME VARCHAR(50);
5880 DECLARE vCUS_EMAIL VARCHAR(100);
5881 DECLARE vCUS_LOCATION VARCHAR(200);
5882 DECLARE vCOUNT INT;
5883
5884 /* EXCEPTION HANDLING start */
5885 -- OTHER exceptions
5886 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5887 BEGIN
5888
5889 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5890
5891 CALL CG$ERRORS_push(@text,
5892 'E',
5893 'API',
5894 @errno,
5895 'DI$CUSTOMERS_sync.others'
5896 );
5897
5898 CALL CG$ERRORS_raise_failure;
5899
5900 END;
5901 /* EXCEPTION HANDLING end */
5902
5903 SELECT USR_FIRSTNAME,USR_LASTNAME,USR_EMAIL,USR_LOCATION
5904 INTO @vCUS_FIRSTNAME,@vCUS_LASTNAME,@vCUS_EMAIL,@vCUS_LOCATION
5905 FROM USERS
5906 WHERE USR_ID = pUSR_ID;
5907
5908 SELECT COUNT(*) INTO vCOUNT
5909 FROM CUSTOMERS WHERE CUS_EMAIL = @vCUS_EMAIL;
5910
5911 if (pCUS_EMAIL is not null) then
5912 -- select vCOUNT, pCUS_EMAIL, @vCUS_EMAIL ;
5913 IF (pCUS_EMAIL = @vCUS_EMAIL) AND (vCOUNT > 0) THEN
5914
5915 UPDATE
5916 CUSTOMERS
5917 SET
5918 CUS_FIRSTNAME = @vCUS_FIRSTNAME,
5919 CUS_LASTNAME = @vCUS_LASTNAME,
5920 CUS_EMAIL = @vCUS_EMAIL,
5921 CUS_LOCATION = @vCUS_LOCATION
5922 WHERE CUS_EMAIL = @vCUS_EMAIL;
5923 ELSE
5924 -- inserting for the new email
5925 INSERT INTO CUSTOMERS (CUS_FIRSTNAME,CUS_LASTNAME,CUS_EMAIL,CUS_LOCATION)
5926 VALUES (@vCUS_FIRSTNAME,@vCUS_LASTNAME,@vCUS_EMAIL,@vCUS_LOCATION);
5927
5928 -- what to do for the old email(pCUS_EMAIL) for the update case?
5929 -- ...if an user change email is it possible from application?
5930
5931 END IF;
5932 end if;
5933
5934END */$$
5935DELIMITER ;
5936
5937/* Procedure structure for procedure `DI$DOCUMENTDATA_sync` */
5938
5939/*!50003 DROP PROCEDURE IF EXISTS `DI$DOCUMENTDATA_sync` */;
5940
5941DELIMITER $$
5942
5943/*!50003 CREATE PROCEDURE `DI$DOCUMENTDATA_sync`(pJID INT(38))
5944BEGIN
5945
5946 -- AR 180215 : Work in Progress
5947 /* EXCEPTION HANDLING start */
5948 -- OTHER exceptions
5949 DECLARE EXIT HANDLER FOR SQLEXCEPTION
5950 BEGIN
5951
5952 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
5953
5954 CALL DI$ERRORS_push(@text,
5955 'E',
5956 'API',
5957 @errno,
5958 'DI$DOCUMENTDATA_sync.others'
5959 );
5960
5961 CALL DI$ERRORS_raise_failure;
5962
5963 END;
5964 /* EXCEPTION HANDLING end */
5965
5966 UPDATE DOCUMENTDATA DD
5967 INNER JOIN JOURNAL_SYNC JS
5968 ON ( ( DD.DOD_ISSN = JS.ISSN_PRINT OR DD.DOD_ISSN = JS.ISSN_ELECTRONIC )
5969 OR ( DD.DOD_JOURNAL_NAME = JS.JOUR_FULL_NAME )
5970 OR ( DD.DOD_JOURNAL_NAME = JS.JOUR_AVVR_NAME )
5971 )
5972 SET DD.JID = JS.JID
5973 WHERE JS.JID = pJID
5974 ;
5975
5976END */$$
5977DELIMITER ;
5978
5979/* Procedure structure for procedure `ELIB_UTILS_SET_PARAMETER` */
5980
5981/*!50003 DROP PROCEDURE IF EXISTS `ELIB_UTILS_SET_PARAMETER` */;
5982
5983DELIMITER $$
5984
5985/*!50003 CREATE PROCEDURE `ELIB_UTILS_SET_PARAMETER`(P_PAR_NAME VARCHAR(50),P_PAR_VALUE VARCHAR(4000))
5986 COMMENT 'Writes ELIB parameter to table PARAMETER'
5987BEGIN
5988 SET P_PAR_NAME = NULLIF(P_PAR_NAME,'');
5989 SET P_PAR_VALUE = NULLIF(P_PAR_VALUE,'');
5990
5991 if P_PAR_NAME is null or P_PAR_VALUE is null then
5992 SIGNAL SQLSTATE '22004' SET
5993 MESSAGE_TEXT = 'ERROR 1263(ER_WARN_NULL_TO_NOTNULL): NULL is supplied to NOT NULL column';
5994
5995 else
5996 begin
5997
5998 DECLARE continue HANDLER FOR 1062
5999 begin
6000 update PARAMETER
6001 set PAR_VALUE = P_PAR_VALUE
6002 where PAR_NAME = P_PAR_NAME;
6003 end;
6004
6005 insert into PARAMETER(PAR_NAME,PAR_VALUE) values(P_PAR_NAME,P_PAR_VALUE);
6006
6007 end;
6008 end if;
6009 /*
6010 * For Testing purpose
6011 * CALL ELIB_UTILS_SET_PARAMETER('Parameter Name','Parameter Value');
6012 */
6013END */$$
6014DELIMITER ;
6015
6016/* Procedure structure for procedure `GET_NEW_PMIDS_FOR_ALERT_OLD` */
6017
6018/*!50003 DROP PROCEDURE IF EXISTS `GET_NEW_PMIDS_FOR_ALERT_OLD` */;
6019
6020DELIMITER $$
6021
6022/*!50003 CREATE PROCEDURE `GET_NEW_PMIDS_FOR_ALERT_OLD`(in p_saved_search_id int,
6023 out p_out_new_pmids text)
6024BEGIN
6025 DECLARE eof BOOL DEFAULT FALSE;
6026 DECLARE v_new_pmids,v_corr_pmids TEXT DEFAULT '';
6027
6028 DECLARE cur CURSOR FOR
6029 SELECT pmid
6030 FROM ALERT_PMID t2
6031 WHERE result_set = 2
6032 AND saved_search_id = p_saved_search_id
6033 AND NOT EXISTS (SELECT pmid
6034 FROM ALERT_PMID t1
6035 WHERE result_set = 1
6036 AND t1.saved_search_id=t2.saved_search_id
6037 AND t1.pmid=t2.pmid
6038 );
6039
6040 #Handler: When a cursor reaches the end of a data set/While no more rows are available
6041 DECLARE CONTINUE HANDLER
6042 FOR NOT FOUND
6043 BEGIN
6044 SET eof := TRUE;
6045 END;
6046
6047 OPEN cur;
6048 lbl_loop : LOOP
6049 FETCH cur INTO v_new_pmids;
6050 IF eof IS TRUE THEN
6051 LEAVE lbl_loop;
6052 END IF;
6053
6054 set v_corr_pmids = concat(",",v_new_pmids,v_corr_pmids);
6055 -- SET str = CONCAT(str,X,',');
6056
6057 END LOOP lbl_loop;
6058 set p_out_new_pmids = substr(v_corr_pmids,2) ;
6059 CLOSE cur;
6060END */$$
6061DELIMITER ;
6062
6063/* Procedure structure for procedure `MANAGE_USER_FAVORITES` */
6064
6065/*!50003 DROP PROCEDURE IF EXISTS `MANAGE_USER_FAVORITES` */;
6066
6067DELIMITER $$
6068
6069/*!50003 CREATE PROCEDURE `MANAGE_USER_FAVORITES`(P_USERNAME VARCHAR(200), P_DBTYPE VARCHAR(20), P_FAV_IDS VARCHAR(4000), P_OP_TYPE VARCHAR(4000))
6070BEGIN
6071
6072 DECLARE V_USERNAME VARCHAR(200);
6073 DECLARE V_DB_TYPE VARCHAR(20);
6074 DECLARE V_COUNT INT;
6075 DECLARE V_MERGED_VALUES VARCHAR(10000);
6076 DECLARE V_FAV_IDS VARCHAR(10000);
6077
6078 /* EXCEPTION HANDLING start */
6079 DECLARE EXIT HANDLER FOR SQLEXCEPTION
6080 BEGIN
6081 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
6082 CALL CG$ERRORS_push(@text,
6083 'E',
6084 'API',
6085 @errno,
6086 'Stand-Alone Function: (MANAGE_USER_FAVORITES) ');
6087 CALL CG$ERRORS_raise_failure;
6088 END;
6089 /* EXCEPTION HANDLING end */
6090
6091 SET P_USERNAME = NULLIF(P_USERNAME,'');
6092 SET P_DBTYPE = NULLIF(P_DBTYPE,'');
6093 SET P_FAV_IDS = NULLIF(P_FAV_IDS,'');
6094 SET P_OP_TYPE = NULLIF(P_OP_TYPE,'');
6095
6096 SELECT COUNT(*)
6097 INTO V_COUNT
6098 FROM USER_FAVORITE_ITEMS
6099 WHERE UPPER(USERNAME) = UPPER(P_USERNAME)
6100 AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6101
6102 IF P_OP_TYPE = 'INS' THEN
6103
6104 IF V_COUNT = 0 AND UPPER(P_DBTYPE) = 'PUBMED' THEN
6105
6106 INSERT INTO USER_FAVORITE_ITEMS(USERNAME, DATABASE_TYPE, FAV_IDS, FAV_IDS_COUNT)
6107 VALUES(P_USERNAME, 'PUBMED', P_FAV_IDS, COUNT_FAVORITE_ITEMS(P_FAV_IDS));
6108
6109 ELSEIF V_COUNT = 0 AND UPPER(P_DBTYPE) = 'MESH' THEN
6110 INSERT INTO USER_FAVORITE_ITEMS(USERNAME, DATABASE_TYPE, FAV_IDS, FAV_IDS_COUNT)
6111 VALUES(P_USERNAME, 'MESH', P_FAV_IDS, COUNT_FAVORITE_ITEMS(P_FAV_IDS));
6112
6113 ELSE
6114 UPDATE USER_FAVORITE_ITEMS
6115 SET FAV_IDS = CONCAT_WS('',FAV_IDS,',',P_FAV_IDS)
6116 WHERE UPPER(USERNAME) = UPPER(P_USERNAME)
6117 AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6118
6119 END IF;
6120
6121 CALL SPLIT_FAV_DATA(P_USERNAME,P_DBTYPE,P_FAV_IDS,NULL);
6122
6123 ELSEIF P_OP_TYPE = 'DEL_PARTIAL' THEN
6124
6125 CALL SPLIT_FAV_DATA(P_USERNAME,P_DBTYPE,P_FAV_IDS,'DEL_PARTIAL');
6126
6127 ELSEIF P_OP_TYPE = 'DEL_FULL' THEN
6128
6129 UPDATE USER_FAVORITE_ITEMS
6130 SET FAV_IDS = NULL,FAV_IDS_COUNT = NULL
6131 WHERE UPPER(USERNAME) = UPPER(P_USERNAME)
6132 AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6133
6134 END IF;
6135
6136 COMMIT;
6137
6138 /*
6139 * For Testing purpose
6140 * CALL MANAGE_USER_FAVORITES('rahmamu1','PUBMED','323','INS');
6141 */
6142
6143END */$$
6144DELIMITER ;
6145
6146/* Procedure structure for procedure `RECENT_SEARCH_INSERT` */
6147
6148/*!50003 DROP PROCEDURE IF EXISTS `RECENT_SEARCH_INSERT` */;
6149
6150DELIMITER $$
6151
6152/*!50003 CREATE PROCEDURE `RECENT_SEARCH_INSERT`(P_SEARCH_DATE DATETIME,
6153 P_SEARCH_DATABASE VARCHAR(50),
6154 P_SEARCH_TYPE VARCHAR(50),
6155 P_LDAP_USER_NAME VARCHAR(30),
6156 P_LDAP_EMAIL_ADDRESS VARCHAR(100),
6157 P_SEARCH_TERM TEXT,
6158 P_ITEM_FOUND TEXT)
6159BEGIN
6160
6161 DECLARE V_IS_EXIST INTEGER;
6162 DECLARE V_SEARCH_NO VARCHAR(255);
6163
6164 SELECT MAX(SEARCH_NO)+1
6165 INTO V_SEARCH_NO
6166 FROM RECENT_SEARCH
6167 WHERE LDAP_USER_NAME=P_LDAP_USER_NAME ;
6168
6169 SELECT COUNT(*)
6170 INTO V_IS_EXIST
6171 FROM RECENT_SEARCH
6172 WHERE STRCMP(LOWER(SEARCH_TERM),LOWER(TRIM(P_SEARCH_TERM))) = 0
6173 AND LDAP_USER_NAME = P_LDAP_USER_NAME
6174 AND LOWER(SEARCH_DATABASE) = LOWER(P_SEARCH_DATABASE)
6175 AND LOWER(SEARCH_TYPE) = 'search';
6176
6177 IF LOWER(P_SEARCH_TYPE) != 'search' THEN
6178 SET V_SEARCH_NO := NULL;
6179 END IF;
6180
6181 IF V_SEARCH_NO IS NULL THEN
6182 SET V_SEARCH_NO := 1;
6183 END IF;
6184
6185 IF V_IS_EXIST = 0 THEN
6186
6187 INSERT INTO RECENT_SEARCH(USER_ID ,
6188 SEARCH_DATE ,
6189 SEARCH_DATABASE ,
6190 SEARCH_TYPE ,
6191 LDAP_USER_NAME ,
6192 LDAP_EMAIL_ADDRESS ,
6193 SEARCH_TERM ,
6194 SEARCH_NO ,
6195 ITEM_FOUND ,
6196 SEARCH_DATETIME)
6197 VALUES (NULL,
6198 P_SEARCH_DATE ,
6199 P_SEARCH_DATABASE ,
6200 P_SEARCH_TYPE ,
6201 P_LDAP_USER_NAME ,
6202 P_LDAP_EMAIL_ADDRESS ,
6203 P_SEARCH_TERM ,
6204 V_SEARCH_NO ,
6205 P_ITEM_FOUND ,
6206 NULL);
6207
6208 ELSE
6209
6210 UPDATE RECENT_SEARCH
6211 SET ITEM_FOUND = P_ITEM_FOUND ,
6212 SEARCH_DATETIME=SYSDATE()
6213 WHERE LOWER(LDAP_USER_NAME) = LOWER(P_LDAP_USER_NAME)
6214 AND STRCMP(LOWER(SEARCH_TERM),LOWER(TRIM(P_SEARCH_TERM))) = 0
6215 AND LOWER(SEARCH_DATABASE)=LOWER(P_SEARCH_DATABASE)
6216 AND LOWER(SEARCH_TYPE)='search';
6217
6218 END IF;
6219
6220 /*
6221 * For Testing purpose
6222 * CALL RECENT_SEARCH_INSERT(
6223 '2018-03-02 00:00:00.000000',
6224 'pubmed',
6225 'search',
6226 'tahmina',
6227 'tahmina.aziz@impulsebdltd.com',
6228 'test data',
6229 '1');
6230
6231 or
6232
6233 CALL RECENT_SEARCH_INSERT(
6234 '2018-03-02',
6235 'pubmed',
6236 'search',
6237 'tahmina',
6238 'tahmina.aziz@impulsebdltd.com',
6239 'test data2',
6240 '1');
6241 */
6242
6243END */$$
6244DELIMITER ;
6245
6246/* Procedure structure for procedure `SPLIT_FAV_DATA` */
6247
6248/*!50003 DROP PROCEDURE IF EXISTS `SPLIT_FAV_DATA` */;
6249
6250DELIMITER $$
6251
6252/*!50003 CREATE PROCEDURE `SPLIT_FAV_DATA`(P_USERNAME VARCHAR(200), P_DBTYPE VARCHAR(20), P_FAV_IDS VARCHAR(4000), P_OP_TYPE VARCHAR(4000))
6253BEGIN
6254
6255 DECLARE V_MERGE_FAV_DATA VARCHAR(10000);
6256 DECLARE V_LOOP_COUNTER INT(38);
6257 DECLARE NO_DATA INT DEFAULT 0;
6258 DECLARE CUR_VAL_FAV_IDS VARCHAR(4000);
6259 DECLARE V_USR_FAV_ID VARCHAR(4000);
6260 DECLARE V_USERNAME VARCHAR(200);
6261 DECLARE V_DATABASE_TYPE VARCHAR(20);
6262 DECLARE V_FAV_IDS VARCHAR(4000);
6263 DECLARE TEMP_VAL INT(38) DEFAULT 1;
6264 DECLARE TEMP_FAV_IDS VARCHAR(4000);
6265
6266 DECLARE CURSOR_VAR1 CURSOR FOR SELECT DISTINCT FAV_IDS FROM USER_FAV_SPLIT_DATA WHERE UPPER(USERNAME) = UPPER(P_USERNAME) AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6267 DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;
6268
6269 /* EXCEPTION HANDLING start */
6270 DECLARE EXIT HANDLER FOR SQLEXCEPTION
6271 BEGIN
6272
6273 GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
6274 CALL CG$ERRORS_push(@text,
6275 'E',
6276 'API',
6277 @errno,
6278 'Stand-Alone Function: (SPLIT_FAV_DATA) ');
6279 CALL CG$ERRORS_raise_failure;
6280
6281 END;
6282 /* EXCEPTION HANDLING end */
6283
6284 SET P_USERNAME = NULLIF(P_USERNAME,'');
6285 SET P_DBTYPE = NULLIF(P_DBTYPE,'');
6286 SET P_FAV_IDS = NULLIF(P_FAV_IDS,'');
6287 SET P_OP_TYPE = NULLIF(P_OP_TYPE,'');
6288
6289 SELECT USR_FAV_ID, USERNAME, DATABASE_TYPE, FAV_IDS, LENGTH(FAV_IDS)-LENGTH(REPLACE(FAV_IDS,',',''))
6290 INTO V_USR_FAV_ID, V_USERNAME, V_DATABASE_TYPE, V_FAV_IDS, V_LOOP_COUNTER
6291 FROM USER_FAVORITE_ITEMS
6292 WHERE UPPER(USERNAME) = UPPER(P_USERNAME)
6293 AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6294
6295 SET V_LOOP_COUNTER = V_LOOP_COUNTER + 1;
6296
6297 WHILE TEMP_VAL <= V_LOOP_COUNTER DO
6298
6299 SELECT REPLACE(SUBSTRING(
6300 SUBSTRING_INDEX(V_FAV_IDS, ',', TEMP_VAL),
6301 LENGTH(SUBSTRING_INDEX(V_FAV_IDS, ',', TEMP_VAL-1)) + 1)
6302 ,',', '') INTO TEMP_FAV_IDS;
6303
6304 INSERT INTO USER_FAV_SPLIT_DATA(USR_FAV_ID, USERNAME, DATABASE_TYPE, FAV_IDS)
6305 VALUES(V_USR_FAV_ID,V_USERNAME,V_DATABASE_TYPE,TRIM(TEMP_FAV_IDS));
6306
6307 SET TEMP_VAL = TEMP_VAL + 1;
6308
6309 END WHILE;
6310
6311 IF P_OP_TYPE = 'DEL_PARTIAL' THEN
6312
6313 SET @SWV_Stmt = CONCAT('DELETE FROM USER_FAV_SPLIT_DATA where FAV_IDS in (''',P_FAV_IDS,''' ) and
6314 upper(username) = upper(''',P_USERNAME,''' ) and upper(database_type) = upper( ''',P_DBTYPE,''' )');
6315 PREPARE SWT_Stmt FROM @SWV_Stmt;
6316 EXECUTE SWT_Stmt;
6317 DEALLOCATE PREPARE SWT_Stmt;
6318
6319 END IF;
6320
6321 OPEN CURSOR_VAR1;
6322 SET NO_DATA = 0;
6323 FETCH CURSOR_VAR1 INTO CUR_VAL_FAV_IDS;
6324
6325 WHILE
6326 NO_DATA = 0 DO
6327 SET V_MERGE_FAV_DATA = CONCAT(COALESCE(V_MERGE_FAV_DATA,''),',',CUR_VAL_FAV_IDS);
6328 SET NO_DATA = 0;
6329 FETCH CURSOR_VAR1 INTO CUR_VAL_FAV_IDS;
6330 END WHILE;
6331
6332 SET NO_DATA = 0;
6333
6334 CLOSE CURSOR_VAR1;
6335
6336 UPDATE USER_FAVORITE_ITEMS
6337 SET
6338 FAV_IDS = TRIM(LEADING ',' FROM TRIM(TRAILING ',' FROM V_MERGE_FAV_DATA)),
6339 FAV_IDS_COUNT = COUNT_FAVORITE_ITEMS(FAV_IDS)
6340 WHERE UPPER(USERNAME) = UPPER(P_USERNAME)
6341 AND UPPER(DATABASE_TYPE) = UPPER(P_DBTYPE);
6342
6343 SET @SWV_Stmt = 'TRUNCATE TABLE USER_FAV_SPLIT_DATA';
6344 PREPARE SWT_Stmt FROM @SWV_Stmt;
6345 EXECUTE SWT_Stmt;
6346 DEALLOCATE PREPARE SWT_Stmt;
6347
6348 COMMIT;
6349
6350 /*
6351 * For Testing purpose
6352 * CALL MANAGE_USER_FAVORITES();
6353 */
6354
6355END */$$
6356DELIMITER ;
6357
6358/*Table structure for table `DOCUMENTDATA_V` */
6359
6360DROP TABLE IF EXISTS `DOCUMENTDATA_V`;
6361
6362/*!50001 DROP VIEW IF EXISTS `DOCUMENTDATA_V` */;
6363/*!50001 DROP TABLE IF EXISTS `DOCUMENTDATA_V` */;
6364
6365/*!50001 CREATE TABLE `DOCUMENTDATA_V`(
6366 `DOV_DOD_ID` int(38) ,
6367 `DOV_DOD_AUTHORS` text ,
6368 `DOV_DOD_JOURNAL_NAME` varchar(255) ,
6369 `DOV_DOD_PUBL_YEAR` decimal(4,0) ,
6370 `DOV_DOD_LANGUAGE` varchar(100) ,
6371 `DOV_DOD_KEYWORDS` text ,
6372 `DOV_DOD_ISSN` varchar(10) ,
6373 `DOV_DOD_DOI` varchar(255) ,
6374 `DOV_DOD_TITLE` text
6375)*/;
6376
6377/*Table structure for table `DOCUMENTDATA_VIEW` */
6378
6379DROP TABLE IF EXISTS `DOCUMENTDATA_VIEW`;
6380
6381/*!50001 DROP VIEW IF EXISTS `DOCUMENTDATA_VIEW` */;
6382/*!50001 DROP TABLE IF EXISTS `DOCUMENTDATA_VIEW` */;
6383
6384/*!50001 CREATE TABLE `DOCUMENTDATA_VIEW`(
6385 `PMID` decimal(10,0) ,
6386 `FILEPATH` varchar(100)
6387)*/;
6388
6389/*Table structure for table `DOCUMENT_VIEW` */
6390
6391DROP TABLE IF EXISTS `DOCUMENT_VIEW`;
6392
6393/*!50001 DROP VIEW IF EXISTS `DOCUMENT_VIEW` */;
6394/*!50001 DROP TABLE IF EXISTS `DOCUMENT_VIEW` */;
6395
6396/*!50001 CREATE TABLE `DOCUMENT_VIEW`(
6397 `LIB_ID` decimal(38,0) ,
6398 `PMID` decimal(10,0) ,
6399 `AUTHORS` text ,
6400 `PUBL_YEAR` decimal(4,0) ,
6401 `DOC_ID` int(38) ,
6402 `MASTERDOC` varchar(1) ,
6403 `FILENAME` varchar(255) ,
6404 `MIMETYPE` varchar(155) ,
6405 `FILEPATH` varchar(100) ,
6406 `IS_IDORSIA` decimal(2,0) ,
6407 `DOI` varchar(255) ,
6408 `URL` text ,
6409 `LINKTYPE` varchar(50)
6410)*/;
6411
6412/*Table structure for table `RSS_VIEW` */
6413
6414DROP TABLE IF EXISTS `RSS_VIEW`;
6415
6416/*!50001 DROP VIEW IF EXISTS `RSS_VIEW` */;
6417/*!50001 DROP TABLE IF EXISTS `RSS_VIEW` */;
6418
6419/*!50001 CREATE TABLE `RSS_VIEW`(
6420 `EID` varchar(42) ,
6421 `PDF_ID` int(38) ,
6422 `MIMETYPE` varchar(155) ,
6423 `MASTERDOC` varchar(1) ,
6424 `FILENAME` varchar(255) ,
6425 `DOD_ID` int(38) ,
6426 `DOCUMENT_TYPE` varchar(2) ,
6427 `TITLE` text ,
6428 `AUTHORS` text ,
6429 `JOURNAL_NAME` varchar(255) ,
6430 `PUBL_YEAR` decimal(4,0) ,
6431 `VOLUME` varchar(50) ,
6432 `ISSUE` varchar(50) ,
6433 `PAGEINFO` varchar(100) ,
6434 `DOC_LANGUAGE` varchar(100) ,
6435 `KEYWORDS` text ,
6436 `AFFILIATION` text ,
6437 `ABSTRACT` text ,
6438 `PMID` decimal(10,0) ,
6439 `DOI` varchar(255) ,
6440 `ISSN` varchar(10) ,
6441 `MESH_TERMS` text ,
6442 `SUBSTANCES` text ,
6443 `PUBLICATION_TYPE` varchar(255) ,
6444 `EDITOR` varchar(255) ,
6445 `BOOKTITLE` text ,
6446 `PUBLISHER` varchar(255) ,
6447 `ISBN` varchar(50) ,
6448 `CONFERENCE_NAME` varchar(255) ,
6449 `SPONSOR` varchar(100) ,
6450 `PAT_APPL_DATE` varchar(50) ,
6451 `PAT_APPL_NUM` varchar(50) ,
6452 `PAT_PRIO_DATES` text ,
6453 `PAT_PRIO_NUM` text ,
6454 `PAT_PUBL_DATE` varchar(50) ,
6455 `PAT_PUBL_NUM` varchar(50) ,
6456 `URL` text ,
6457 `PRODUCT` varchar(255) ,
6458 `INDICATION` text ,
6459 `KEY_PUBLICATION` varchar(1) ,
6460 `CLINICAL_ARTICLE` varchar(1) ,
6461 `LINK_TYPE` varchar(50) ,
6462 `LIBRARY_COMMENT` text ,
6463 `CREATION_DATE` datetime(6) ,
6464 `MODIFICATION_DATE` datetime(6) ,
6465 `DETAILS_LINK` mediumtext ,
6466 `PDF_LINK` varchar(1)
6467)*/;
6468
6469/*Table structure for table `V_DISPLAY_ALERT_GROUP` */
6470
6471DROP TABLE IF EXISTS `V_DISPLAY_ALERT_GROUP`;
6472
6473/*!50001 DROP VIEW IF EXISTS `V_DISPLAY_ALERT_GROUP` */;
6474/*!50001 DROP TABLE IF EXISTS `V_DISPLAY_ALERT_GROUP` */;
6475
6476/*!50001 CREATE TABLE `V_DISPLAY_ALERT_GROUP`(
6477 `PERIOD` varchar(10) ,
6478 `SAVED_SEARCH_ID` int(38) ,
6479 `SEARCH_ID` decimal(38,0) ,
6480 `USER_ID` decimal(12,0) ,
6481 `SEARCH_NAME` text ,
6482 `SEARCH_DATE` datetime(6) ,
6483 `SEARCH_DATABASE` varchar(50) ,
6484 `SEARCH_TYPE` varchar(50) ,
6485 `IS_SAVED` decimal(38,0) ,
6486 `IS_ALERT` decimal(38,0) ,
6487 `ALERT_FREQUENCY` varchar(30) ,
6488 `ALERT_DAY` varchar(50) ,
6489 `LAST_ALERT_EXECUTION` datetime(6) ,
6490 `NEXT_ALERT_EXECUTION` datetime(6) ,
6491 `EMAIL_UPDATE` decimal(12,0) ,
6492 `LAST_ALERT_COUNT` decimal(38,0) ,
6493 `LAST_ALERT_QUERY` text ,
6494 `LDAP_USER_NAME` varchar(30) ,
6495 `LDAP_EMAIL_ADDRESS` varchar(100) ,
6496 `SEARCH_TERM` text
6497)*/;
6498
6499/*View structure for view DOCUMENTDATA_V */
6500
6501/*!50001 DROP TABLE IF EXISTS `DOCUMENTDATA_V` */;
6502/*!50001 DROP VIEW IF EXISTS `DOCUMENTDATA_V` */;
6503
6504/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `DOCUMENTDATA_V` AS select `DOD`.`DOD_ID` AS `DOV_DOD_ID`,`DOD`.`DOD_AUTHORS` AS `DOV_DOD_AUTHORS`,`DOD`.`DOD_JOURNAL_NAME` AS `DOV_DOD_JOURNAL_NAME`,`DOD`.`DOD_PUBL_YEAR` AS `DOV_DOD_PUBL_YEAR`,`DOD`.`DOD_LANGUAGE` AS `DOV_DOD_LANGUAGE`,`DOD`.`DOD_KEYWORDS` AS `DOV_DOD_KEYWORDS`,`DOD`.`DOD_ISSN` AS `DOV_DOD_ISSN`,`DOD`.`DOD_DOI` AS `DOV_DOD_DOI`,`DOD`.`DOD_TITLE` AS `DOV_DOD_TITLE` from `DOCUMENTDATA` `DOD` where `DOD`.`DOD_ORI_ID` is not null */;
6505
6506/*View structure for view DOCUMENTDATA_VIEW */
6507
6508/*!50001 DROP TABLE IF EXISTS `DOCUMENTDATA_VIEW` */;
6509/*!50001 DROP VIEW IF EXISTS `DOCUMENTDATA_VIEW` */;
6510
6511/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `DOCUMENTDATA_VIEW` AS select `DOD`.`DOD_PMID` AS `PMID`,`DOC`.`DOC_FILEPATH` AS `FILEPATH` from (`DOCUMENTDATA` `DOD` join `DOCUMENTS` `DOC` on(`DOC`.`DOC_LII_ID` = `DOD`.`DOD_LII_ID` and `DOD`.`DOD_LII_HIDDEN` = 'N' and `DOC`.`DOC_MASTERDOC` = 'Y')) */;
6512
6513/*View structure for view DOCUMENT_VIEW */
6514
6515/*!50001 DROP TABLE IF EXISTS `DOCUMENT_VIEW` */;
6516/*!50001 DROP VIEW IF EXISTS `DOCUMENT_VIEW` */;
6517
6518/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `DOCUMENT_VIEW` AS select `DOD`.`DOD_LII_ID` AS `LIB_ID`,`DOD`.`DOD_PMID` AS `PMID`,`DOD`.`DOD_AUTHORS` AS `AUTHORS`,`DOD`.`DOD_PUBL_YEAR` AS `PUBL_YEAR`,`DOC`.`DOC_ID` AS `DOC_ID`,`DOC`.`DOC_MASTERDOC` AS `MASTERDOC`,`DOC`.`DOC_FILENAME` AS `FILENAME`,`DOC`.`DOC_MIMETYPE` AS `MIMETYPE`,`DOC`.`DOC_FILEPATH` AS `FILEPATH`,`DOC`.`IS_IDORSIA` AS `IS_IDORSIA`,`DOD`.`DOD_DOI` AS `DOI`,`DOD`.`DOD_URL` AS `URL`,`DOD`.`DOD_LINKTYPE` AS `LINKTYPE` from (`DOCUMENTDATA` `DOD` left join `DOCUMENTS` `DOC` on(`DOC`.`DOC_LII_ID` = `DOD`.`DOD_LII_ID` and `DOD`.`DOD_LII_HIDDEN` = 'N')) */;
6519
6520/*View structure for view RSS_VIEW */
6521
6522/*!50001 DROP TABLE IF EXISTS `RSS_VIEW` */;
6523/*!50001 DROP VIEW IF EXISTS `RSS_VIEW` */;
6524
6525/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `RSS_VIEW` AS select concat_ws('','EID',`DOCUMENTDATA`.`DOD_LII_ID`) AS `EID`,`DOCUMENTS`.`DOC_ID` AS `PDF_ID`,`DOCUMENTS`.`DOC_MIMETYPE` AS `MIMETYPE`,`DOCUMENTS`.`DOC_MASTERDOC` AS `MASTERDOC`,`DOCUMENTS`.`DOC_FILENAME` AS `FILENAME`,`DOCUMENTDATA`.`DOD_ID` AS `DOD_ID`,`DOCUMENTDATA`.`DOD_DOCTYPE` AS `DOCUMENT_TYPE`,`DOCUMENTDATA`.`DOD_TITLE` AS `TITLE`,`DOCUMENTDATA`.`DOD_AUTHORS` AS `AUTHORS`,`DOCUMENTDATA`.`DOD_JOURNAL_NAME` AS `JOURNAL_NAME`,`DOCUMENTDATA`.`DOD_PUBL_YEAR` AS `PUBL_YEAR`,`DOCUMENTDATA`.`DOD_VOLUME` AS `VOLUME`,`DOCUMENTDATA`.`DOD_ISSUE` AS `ISSUE`,`DOCUMENTDATA`.`DOD_PAGEINFO` AS `PAGEINFO`,`DOCUMENTDATA`.`DOD_LANGUAGE` AS `DOC_LANGUAGE`,`DOCUMENTDATA`.`DOD_KEYWORDS` AS `KEYWORDS`,`DOCUMENTDATA`.`DOD_AFFILIATION` AS `AFFILIATION`,`DOCUMENTDATA`.`DOD_ABSTRACT` AS `ABSTRACT`,`DOCUMENTDATA`.`DOD_PMID` AS `PMID`,`DOCUMENTDATA`.`DOD_DOI` AS `DOI`,`DOCUMENTDATA`.`DOD_ISSN` AS `ISSN`,`DOCUMENTDATA`.`DOD_MESH_TERMS` AS `MESH_TERMS`,`DOCUMENTDATA`.`DOD_SUBSTANCES` AS `SUBSTANCES`,`DOCUMENTDATA`.`DOD_PUB_TYPE` AS `PUBLICATION_TYPE`,`DOCUMENTDATA`.`DOD_EDITOR_NAME` AS `EDITOR`,`DOCUMENTDATA`.`DOD_BOOKTITLE` AS `BOOKTITLE`,`DOCUMENTDATA`.`DOD_PUBLISHER` AS `PUBLISHER`,`DOCUMENTDATA`.`DOD_ISBN` AS `ISBN`,`DOCUMENTDATA`.`DOD_CONF_NAME` AS `CONFERENCE_NAME`,`DOCUMENTDATA`.`DOD_SPONSOR` AS `SPONSOR`,`DOCUMENTDATA`.`DOD_PAT_APPL_DATE` AS `PAT_APPL_DATE`,`DOCUMENTDATA`.`DOD_PAT_APPL_NUM` AS `PAT_APPL_NUM`,`DOCUMENTDATA`.`DOD_PAT_PRIO_DATES` AS `PAT_PRIO_DATES`,`DOCUMENTDATA`.`DOD_PAT_PRIO_NUM` AS `PAT_PRIO_NUM`,`DOCUMENTDATA`.`DOD_PAT_PUBL_DATE` AS `PAT_PUBL_DATE`,`DOCUMENTDATA`.`DOD_PAT_PUBL_NUM` AS `PAT_PUBL_NUM`,`DOCUMENTDATA`.`DOD_URL` AS `URL`,`DOCUMENTDATA`.`DOD_PRODUCT` AS `PRODUCT`,`DOCUMENTDATA`.`DOD_INDICATION` AS `INDICATION`,`DOCUMENTDATA`.`DOD_KEY_PUBLICATION` AS `KEY_PUBLICATION`,`DOCUMENTDATA`.`DOD_CLINICAL_ARTICLE` AS `CLINICAL_ARTICLE`,`DOCUMENTDATA`.`DOD_LINKTYPE` AS `LINK_TYPE`,`DOCUMENTDATA`.`DOD_LII_COMMENT` AS `LIBRARY_COMMENT`,`DOCUMENTDATA`.`DOD_LII_CREATED` AS `CREATION_DATE`,`DOCUMENTDATA`.`DOD_LII_MODIFIED` AS `MODIFICATION_DATE`,(select concat(`PARAMETER`.`PAR_VALUE`,'/library/details/?id=',`DOCUMENTDATA`.`DOD_LII_ID`,'&redirect=library___list') from `PARAMETER` where `PARAMETER`.`PAR_NAME` = 'SERVER_URL') AS `DETAILS_LINK`,(select if(`DOCUMENTS`.`DOC_ID`,(select `PARAMETER`.`PAR_VALUE` from `PARAMETER` where `PARAMETER`.`PAR_NAME` = 'SERVER_URL') or '/documentdata/showDocument?id=' or `DOCUMENTS`.`DOC_ID` <> 0,'') from `PARAMETER` where `PARAMETER`.`PAR_NAME` = 'SERVER_URL') AS `PDF_LINK` from (`DOCUMENTDATA` left join `DOCUMENTS` on(`DOCUMENTDATA`.`DOD_LII_ID` = `DOCUMENTS`.`DOC_LII_ID`)) order by `DOCUMENTDATA`.`DOD_LII_CREATED` desc */;
6526
6527/*View structure for view V_DISPLAY_ALERT_GROUP */
6528
6529/*!50001 DROP TABLE IF EXISTS `V_DISPLAY_ALERT_GROUP` */;
6530/*!50001 DROP VIEW IF EXISTS `V_DISPLAY_ALERT_GROUP` */;
6531
6532/*!50001 CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `V_DISPLAY_ALERT_GROUP` AS select case when `SAVED_SEARCH`.`SEARCH_DATE` = cast(sysdate() as date) then 'Today' when `SAVED_SEARCH`.`SEARCH_DATE` = cast(sysdate() as date) - interval 1 day then 'Yesterday' when `SAVED_SEARCH`.`SEARCH_DATE` between cast(sysdate() as date) - interval 7 day and cast(sysdate() as date) - interval 2 day then 'Last Week' when (`SAVED_SEARCH`.`SEARCH_DATE` <= cast(sysdate() as date) - interval 8 day and date_format(`SAVED_SEARCH`.`SEARCH_DATE`,'%M') = date_format(sysdate(),'%M')) then 'This Month' when date_format(`SAVED_SEARCH`.`SEARCH_DATE`,'%M') = date_format(sysdate() + interval -1 month,'%M') then 'Last Month' else 'Older' end AS `PERIOD`,`SAVED_SEARCH`.`SAVED_SEARCH_ID` AS `SAVED_SEARCH_ID`,`SAVED_SEARCH`.`SEARCH_ID` AS `SEARCH_ID`,`SAVED_SEARCH`.`USER_ID` AS `USER_ID`,`SAVED_SEARCH`.`SEARCH_NAME` AS `SEARCH_NAME`,`SAVED_SEARCH`.`SEARCH_DATE` AS `SEARCH_DATE`,`SAVED_SEARCH`.`SEARCH_DATABASE` AS `SEARCH_DATABASE`,`SAVED_SEARCH`.`SEARCH_TYPE` AS `SEARCH_TYPE`,`SAVED_SEARCH`.`IS_SAVED` AS `IS_SAVED`,`SAVED_SEARCH`.`IS_ALERT` AS `IS_ALERT`,`SAVED_SEARCH`.`ALERT_FREQUENCY` AS `ALERT_FREQUENCY`,`SAVED_SEARCH`.`ALERT_DAY` AS `ALERT_DAY`,`SAVED_SEARCH`.`LAST_ALERT_EXECUTION` AS `LAST_ALERT_EXECUTION`,`SAVED_SEARCH`.`NEXT_ALERT_EXECUTION` AS `NEXT_ALERT_EXECUTION`,`SAVED_SEARCH`.`EMAIL_UPDATE` AS `EMAIL_UPDATE`,`SAVED_SEARCH`.`LAST_ALERT_COUNT` AS `LAST_ALERT_COUNT`,`SAVED_SEARCH`.`LAST_ALERT_QUERY` AS `LAST_ALERT_QUERY`,`SAVED_SEARCH`.`LDAP_USER_NAME` AS `LDAP_USER_NAME`,`SAVED_SEARCH`.`LDAP_EMAIL_ADDRESS` AS `LDAP_EMAIL_ADDRESS`,`SAVED_SEARCH`.`SEARCH_TERM` AS `SEARCH_TERM` from `SAVED_SEARCH` where `SAVED_SEARCH`.`IS_ALERT` = 1 and `SAVED_SEARCH`.`SEARCH_DATE` is not null order by `SAVED_SEARCH`.`SEARCH_DATE` desc */;
6533
6534/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
6535/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
6536/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
6537/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;