[Home] [Help]
PACKAGE BODY: APPS.QLTCOMPB
Source
1 PACKAGE BODY QLTCOMPB AS
2 /* $Header: qltcompb.plb 115.9 2003/10/20 15:46:05 saugupta ship $ */
3
4 -- Compare functions for the server
5 -- 2/8/95 created
6 -- Kevin Wiggen
7
8 -- The following three functions are overloaded for the three datatypes
9 -- we support. Aside from that they are exactly the same. They take in
10 -- the operator, value, and low and high and compare them, returning a
11 -- boolean value.
12 --
13 -- The values for operators are:
14 -- CODE OPERATOR
15 -- ---- --------
16 -- 1 =
17 -- 2 <>
18 -- 3 >=
19 -- 4 <=
20 -- 5 >
21 -- 6 <
22 -- 7 NOT NULL
23 -- 8 NULL
24 -- 9 BETWEEN
25 -- 10 OUTSIDE
26 -- 110.IN -- NOT SUPPORTED! We do not support this operator for actions
27
28 FUNCTION compare(value1 IN VARCHAR2,
29 operator IN NUMBER,
30 value2 IN VARCHAR2,
31 value3 IN VARCHAR2)
32 RETURN BOOLEAN IS
33 BEGIN
34 --MESSAGE('In compare (char)');
35 IF operator = 1 THEN
36 RETURN(value1 = value2);
37 ELSIF operator = 2 THEN
38 RETURN(value1 <> value2);
39 ELSIF operator = 3 THEN
40 RETURN(value1 >= value2);
41 ELSIF operator = 4 THEN
42 RETURN(value1 <= value2);
43 ELSIF operator = 5 THEN
44 RETURN(value1 > value2);
45 ELSIF operator = 6 THEN
46 RETURN(value1 < value2);
47 ELSIF operator = 7 THEN
48 RETURN(value1 IS NOT NULL);
49 ELSIF operator = 8 THEN
50 RETURN(value1 IS NULL);
51 ELSIF operator = 9 THEN
52 RETURN(value1 BETWEEN value2 AND value3);
53 ELSIF operator = 10 THEN
54 RETURN(value1 < value2 OR value1 > value3);
55 ELSE
56 APP_EXCEPTION.INVALID_ARGUMENT('q_res.compare',
57 'operator', TO_CHAR(operator));
58 END IF;
59 END compare;
60
61
62 FUNCTION compare(value1 IN NUMBER,
63 operator IN NUMBER,
64 value2 IN NUMBER,
65 value3 IN NUMBER)
66 RETURN BOOLEAN IS
67 BEGIN
68 --MESSAGE('In compare (number)');
69 IF operator = 1 THEN
70 RETURN(value1 = value2);
71 ELSIF operator = 2 THEN
72 RETURN(value1 <> value2);
73 ELSIF operator = 3 THEN
74 RETURN(value1 >= value2);
75 ELSIF operator = 4 THEN
76 RETURN(value1 <= value2);
77 ELSIF operator = 5 THEN
78 RETURN(value1 > value2);
79 ELSIF operator = 6 THEN
80 RETURN(value1 < value2);
81 ELSIF operator = 7 THEN
82 RETURN(value1 IS NOT NULL);
83 ELSIF operator = 8 THEN
84 RETURN(value1 IS NULL);
85 ELSIF operator = 9 THEN
86 RETURN(value1 BETWEEN value2 AND value3);
87 ELSIF operator = 10 THEN
88 RETURN(value1 < value2 OR value1 > value3);
89 ELSE
90 APP_EXCEPTION.INVALID_ARGUMENT('q_res.compare',
91 'operator', TO_CHAR(operator));
92 END IF;
93 END compare;
94
95
96 FUNCTION compare(value1 IN DATE,
97 operator IN NUMBER,
98 value2 IN DATE,
99 value3 IN DATE)
100 RETURN BOOLEAN IS
101 BEGIN
102 --MESSAGE('In compare (date)');
103 IF operator = 1 THEN
104 RETURN(value1 = value2);
105 ELSIF operator = 2 THEN
106 RETURN(value1 <> value2);
107 ELSIF operator = 3 THEN
108 RETURN(value1 >= value2);
109 ELSIF operator = 4 THEN
110 RETURN(value1 <= value2);
111 ELSIF operator = 5 THEN
112 RETURN(value1 > value2);
113 ELSIF operator = 6 THEN
114 RETURN(value1 < value2);
115 ELSIF operator = 7 THEN
116 RETURN(value1 IS NOT NULL);
117 ELSIF operator = 8 THEN
118 RETURN(value1 IS NULL);
119 ELSIF operator = 9 THEN
120 RETURN(value1 BETWEEN value2 AND value3);
121 ELSIF operator = 10 THEN
122 RETURN(value1 < value2 OR value1 > value3);
123 ELSE
124 APP_EXCEPTION.INVALID_ARGUMENT('q_res.compare',
125 'operator', TO_CHAR(operator));
126 END IF;
127 END compare;
128
129
130 -- Bug2336153. This function is written to support the sequence datatype.
131
132 FUNCTION compare_seq(value1 IN VARCHAR2,
133 operator IN NUMBER,
134 value2 IN VARCHAR2,
135 value3 IN VARCHAR2)
136 RETURN BOOLEAN IS
137 BEGIN
138
139 --MESSAGE('In compare (sequence)');
140 IF operator = 1 THEN
141 RETURN(value1 = value2);
142 ELSIF operator = 2 THEN
143 RETURN(value1 <> value2);
144 ELSIF operator = 3 THEN
145 RETURN(value1 >= value2);
146 ELSIF operator = 4 THEN
147 RETURN(value1 <= value2);
148 ELSIF operator = 5 THEN
149 RETURN(value1 > value2);
150 ELSIF operator = 6 THEN
151 RETURN(value1 < value2);
152 ELSIF operator = 7 THEN
153 RETURN(value1 IS NOT NULL);
154 ELSIF operator = 8 THEN
155 RETURN(value1 IS NULL);
156 ELSIF operator = 9 THEN
157 RETURN(value1 BETWEEN value2 AND value3);
158 ELSIF operator = 10 THEN
159 RETURN(value1 < value2 OR value1 > value3);
160 ELSE
161 APP_EXCEPTION.INVALID_ARGUMENT('q_res.compare',
162 'operator', TO_CHAR(operator));
163 END IF;
164 RETURN NULL;
165 END compare_seq;
166
167
168 FUNCTION compare(value1 IN VARCHAR2,
169 operator IN NUMBER,
170 value2 IN VARCHAR2,
171 value3 IN VARCHAR2,
172 datatype NUMBER)
173 RETURN BOOLEAN IS
174 --
175 -- Calls the correct version of compare depending on the datatype. It
176 -- returns a boolean based on whether the condition is true or not.
177 --
178 BEGIN
179 --MESSAGE('Check if '||value1||' '''||to_char(operator)
180 -- ||''' '||value2||' and '||value3||' ('||to_char(datatype)||')');
181 IF datatype = 1 THEN -- character
182 RETURN(compare(value1,operator,value2,value3));
183 ELSIF datatype = 2 THEN -- number
184 RETURN(compare(qltdate.canon_to_number(value1),operator,
185 qltdate.canon_to_number(value2),
186 qltdate.canon_to_number(value3)));
187 ELSIF datatype = 3 THEN -- date
188 RETURN(compare(qltdate.any_to_date(value1),operator,
189 qltdate.any_to_date(value2),
190 qltdate.any_to_date(value3)));
191
192 -- Bug2336153. Added the below code to support Sequence Datatype.
193
194 ELSIF datatype = 5 THEN
195 RETURN(compare_seq(value1, operator, value2, value3));
196
197 -- Bug 3179845. Timezone Project. rponnusa
198 -- added datetime datatype
199
200 ELSIF datatype = 6 THEN -- datetime
201 RETURN(compare(qltdate.any_to_datetime(value1),operator,
202 qltdate.any_to_datetime(value2),
203 qltdate.any_to_datetime(value3)));
204
205
206 END IF;
207 END; -- compare
208
209
210 END QLTCOMPB;
211