defensive-sql-util/testOutput.sql at master · SeanTCoder/defensive-sql-util · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
SELECT COUNT(*)
INTO CNT_V
FROM CROWN.VALIDATION_ERROR_MESSAGE
WHERE ERROR_CODE = 12519;

IF CNT_V = 0 THEN
	INSERT INTO CROWN.VALIDATION_ERROR_MESSAGE (ERROR_CODE,NAME,LABEL,ACTIVE,SORT_ORDER,CATEGORY,SUB_CATEGORY,IS_WARNING,CREATE_TIME,UPDATE_TIME) values (12519,'CW 4.0','Pre-Dialysis Weight must be greater than zero (0).',1,707,2,1,0,to_date('14-MAY-12','DD-MON-RR'),to_date('14-MAY-12','DD-MON-RR'));
ELSE
	UPDATE CROWN.VALIDATION_ERROR_MESSAGE
		SET ERROR_CODE = 12519,
NAME = 'CW 4.0',
LABEL = 'Pre-Dialysis Weight must be greater than zero (0).',
ACTIVE = 1,
SORT_ORDER = 707,
CATEGORY = 2,
SUB_CATEGORY = 1,
IS_WARNING = 0,
CREATE_TIME = to_date('14-MAY-12','DD-MON-RR'),
UPDATE_TIME = to_date('14-MAY-12','DD-MON-RR')
	WHERE ERROR_CODE = 12519;
END IF;

-- reset the counter
CNT_V := 0;

SELECT COUNT(*)
INTO CNT_V
FROM CROWN.VALIDATION_ERROR_MESSAGE
WHERE ERROR_CODE = 12629;

IF CNT_V = 0 THEN
	INSERT INTO CROWN.VALIDATION_ERROR_MESSAGE (ERROR_CODE,NAME,LABEL,ACTIVE,SORT_ORDER,CATEGORY,SUB_CATEGORY,IS_WARNING,CREATE_TIME,UPDATE_TIME) values (12629,'CW 4.0','Serum BUN is mandatory when Clinic Weight, Creatinine Clearance, Dialysate Volume, Dialysate Urea Nitrogen, Dialysate Creatinine, Urine Volume, or Serum Creatinine is populated.',1,758,2,1,0,to_date('14-MAY-12','DD-MON-RR'),to_date('14-MAY-12','DD-MON-RR'));
ELSE
	UPDATE CROWN.VALIDATION_ERROR_MESSAGE
		SET ERROR_CODE = 12629,
NAME = 'CW 4.0',
LABEL = 'Serum BUN is mandatory when Clinic Weight, Creatinine Clearance, Dialysate Volume, Dialysate Urea Nitrogen, Dialysate Creatinine, Urine Volume, or Serum Creatinine is populated.',
ACTIVE = 1,
SORT_ORDER = 758,
CATEGORY = 2,
SUB_CATEGORY = 1,
IS_WARNING = 0,
CREATE_TIME = to_date('14-MAY-12','DD-MON-RR'),
UPDATE_TIME = to_date('14-MAY-12','DD-MON-RR')
	WHERE ERROR_CODE = 12629;
END IF;

-- reset the counter
CNT_V := 0;

SELECT COUNT(*)
INTO CNT_V
FROM CROWN.VALIDATION_ERROR_MESSAGE
WHERE ERROR_CODE = 13079;

IF CNT_V = 0 THEN
	INSERT INTO CROWN.VALIDATION_ERROR_MESSAGE (ERROR_CODE,NAME,LABEL,ACTIVE,SORT_ORDER,CATEGORY,SUB_CATEGORY,IS_WARNING,CREATE_TIME,UPDATE_TIME) values (13079,'CW 4.0','When Hepatitis C Screen Test field is ''''Other'''', then Other Hepatitis C Screen Test field must have a value.',1,1069,2,1,0,to_date('14-MAY-12','DD-MON-RR'),to_date('14-MAY-12','DD-MON-RR'));
ELSE
	UPDATE CROWN.VALIDATION_ERROR_MESSAGE
		SET ERROR_CODE = 13079,
NAME = 'CW 4.0',
LABEL = 'When Hepatitis C Screen Test field is ''''Other'''', then Other Hepatitis C Screen Test field must have a value.',
ACTIVE = 1,
SORT_ORDER = 1069,
CATEGORY = 2,
SUB_CATEGORY = 1,
IS_WARNING = 0,
CREATE_TIME = to_date('14-MAY-12','DD-MON-RR'),
UPDATE_TIME = to_date('14-MAY-12','DD-MON-RR')
	WHERE ERROR_CODE = 13079;
END IF;

-- reset the counter
CNT_V := 0;

SELECT COUNT(*)
INTO CNT_V
FROM CROWN.VALIDATION_ERROR_MESSAGE
WHERE ERROR_CODE = 12629;

IF CNT_V = 0 THEN
	INSERT INTO CROWN.VALIDATION_ERROR_MESSAGE (ERROR_CODE,NAME,LABEL,ACTIVE,SORT_ORDER,CATEGORY,SUB_CATEGORY,IS_WARNING,CREATE_TIME,UPDATE_TIME) values (12629,'CW 4.0','Serum BUN is mandatory when Clinic Weight, Creatinine Clearance, Dialysate Volume, Dialysate Urea Nitrogen, Dialysate Creatinine, Urine Volume, or
Serum Creatinine is populated.',1,758,2,1,0,to_date('09-APR-12','DD-MON-RR'),to_date('09-APR-12','DD-MON-RR'));
ELSE
	UPDATE CROWN.VALIDATION_ERROR_MESSAGE
		SET ERROR_CODE = 12629,
NAME = 'CW 4.0',
LABEL = 'Serum BUN is mandatory when Clinic Weight, Creatinine Clearance, Dialysate Volume, Dialysate Urea Nitrogen, Dialysate Creatinine, Urine Volume, or
Serum Creatinine is populated.',
ACTIVE = 1,
SORT_ORDER = 758,
CATEGORY = 2,
SUB_CATEGORY = 1,
IS_WARNING = 0,
CREATE_TIME = to_date('09-APR-12','DD-MON-RR'),
UPDATE_TIME = to_date('09-APR-12','DD-MON-RR')
	WHERE ERROR_CODE = 12629;
END IF;

-- reset the counter
CNT_V := 0;