[Home] [Help]
PACKAGE BODY: APPS.AMS_QUERY_COND_PKG
Source
1 package body AMS_QUERY_COND_PKG as
2 /* $Header: amstqcob.pls 120.0 2005/05/31 20:24:24 appldev noship $ */
3 procedure INSERT_ROW (
4 X_QUERY_CONDITION_ID in NUMBER,
5 X_PARAMETER_KEY in VARCHAR2,
6 X_TEMPLATE_ID in NUMBER,
7 X_JOIN_CONDITION in VARCHAR2,
8 X_OPERAND_DATA_TYPE in VARCHAR2,
9 X_MANDATORY_FLAG in VARCHAR2,
10 X_DEFAULT_FLAG in VARCHAR2,
11 X_CONDITION_IN_USE_FLAG in VARCHAR2,
12 X_LEFT_OPERAND_TYPE in VARCHAR2,
13 X_VALUE1_TYPE in VARCHAR2,
14 X_VALUE2_TYPE in VARCHAR2,
15 X_DISPLAY_COL_NUMBER in NUMBER,
16 X_MAX_TOKEN_COUNT in NUMBER,
17 X_OBJECT_VERSION_NUMBER in NUMBER,
18 X_REQUEST_ID in NUMBER,
19 X_COND_KEYWORD in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26 begin
27 insert into AMS_QUERY_CONDITION (
28 PARAMETER_KEY,
29 QUERY_CONDITION_ID,
30 TEMPLATE_ID,
31 JOIN_CONDITION,
32 OPERAND_DATA_TYPE,
33 MANDATORY_FLAG,
34 DEFAULT_FLAG,
35 CONDITION_IN_USE_FLAG,
36 LEFT_OPERAND_TYPE,
37 VALUE1_TYPE,
38 VALUE2_TYPE,
39 DISPLAY_COL_NUMBER,
40 MAX_TOKEN_COUNT,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_LOGIN,
46 OBJECT_VERSION_NUMBER,
47 REQUEST_ID
48 ) values(
49 X_PARAMETER_KEY,
50 X_QUERY_CONDITION_ID,
51 X_TEMPLATE_ID,
52 X_JOIN_CONDITION,
53 X_OPERAND_DATA_TYPE,
54 X_MANDATORY_FLAG,
55 X_DEFAULT_FLAG,
56 X_CONDITION_IN_USE_FLAG,
57 X_LEFT_OPERAND_TYPE,
58 X_VALUE1_TYPE,
59 X_VALUE2_TYPE,
60 X_DISPLAY_COL_NUMBER,
61 X_MAX_TOKEN_COUNT,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATED_BY,
64 X_CREATION_DATE,
65 X_CREATED_BY,
66 X_LAST_UPDATE_LOGIN,
67 X_OBJECT_VERSION_NUMBER,
68 X_REQUEST_ID);
69
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 X_QUERY_CONDITION_ID in NUMBER,
74 X_PARAMETER_KEY in VARCHAR2,
75 X_TEMPLATE_ID in NUMBER,
76 X_JOIN_CONDITION in VARCHAR2,
77 X_OPERAND_DATA_TYPE in VARCHAR2,
78 X_MANDATORY_FLAG in VARCHAR2,
79 X_DEFAULT_FLAG in VARCHAR2,
80 X_CONDITION_IN_USE_FLAG in VARCHAR2,
81 X_LEFT_OPERAND_TYPE in VARCHAR2,
82 X_VALUE1_TYPE in VARCHAR2,
83 X_VALUE2_TYPE in VARCHAR2,
84 X_DISPLAY_COL_NUMBER in NUMBER,
85 X_MAX_TOKEN_COUNT in NUMBER,
86 X_OBJECT_VERSION_NUMBER in NUMBER,
87 X_REQUEST_ID in NUMBER,
88 X_COND_KEYWORD in VARCHAR2
89 ) is
90 cursor c1 is select
91 PARAMETER_KEY,
92 TEMPLATE_ID,
93 JOIN_CONDITION,
94 OPERAND_DATA_TYPE,
95 MANDATORY_FLAG,
96 DEFAULT_FLAG,
97 CONDITION_IN_USE_FLAG,
98 LEFT_OPERAND_TYPE,
99 VALUE1_TYPE,
100 VALUE2_TYPE,
101 DISPLAY_COL_NUMBER,
102 MAX_TOKEN_COUNT,
103 OBJECT_VERSION_NUMBER,
104 COND_KEYWORD,
105 QUERY_CONDITION_ID
106 from AMS_QUERY_CONDITION
107 where QUERY_CONDITION_ID = X_QUERY_CONDITION_ID
108 for update of QUERY_CONDITION_ID nowait;
109 begin
110 for Recinfo in c1 loop
111 if ( (Recinfo.QUERY_CONDITION_ID = X_QUERY_CONDITION_ID)
112 AND ((Recinfo.PARAMETER_KEY = X_PARAMETER_KEY)
113 OR ((Recinfo.PARAMETER_KEY is null) AND (X_PARAMETER_KEY is null)))
114 AND ((Recinfo.TEMPLATE_ID = X_TEMPLATE_ID)
115 OR ((Recinfo.TEMPLATE_ID is null) AND (X_TEMPLATE_ID is null)))
116 AND ((Recinfo.JOIN_CONDITION = X_JOIN_CONDITION)
117 OR ((Recinfo.JOIN_CONDITION is null) AND (X_JOIN_CONDITION is null)))
118 AND ((Recinfo.OPERAND_DATA_TYPE = X_OPERAND_DATA_TYPE)
119 OR ((Recinfo.OPERAND_DATA_TYPE is null) AND (X_OPERAND_DATA_TYPE is null)))
120 AND ((Recinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
121 OR ((Recinfo.MANDATORY_FLAG is null) AND (X_MANDATORY_FLAG is null)))
122 AND ((Recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
123 OR ((Recinfo.DEFAULT_FLAG is null) AND (X_DEFAULT_FLAG is null)))
124 AND ((Recinfo.CONDITION_IN_USE_FLAG = X_CONDITION_IN_USE_FLAG)
125 OR ((Recinfo.CONDITION_IN_USE_FLAG is null) AND (X_CONDITION_IN_USE_FLAG is null)))
126 AND ((Recinfo.LEFT_OPERAND_TYPE = X_LEFT_OPERAND_TYPE)
127 OR ((Recinfo.LEFT_OPERAND_TYPE is null) AND (X_LEFT_OPERAND_TYPE is null)))
128 AND ((Recinfo.VALUE1_TYPE = X_VALUE1_TYPE)
129 OR ((Recinfo.VALUE1_TYPE is null) AND (X_VALUE1_TYPE is null)))
130 AND ((Recinfo.VALUE2_TYPE = X_VALUE2_TYPE)
131 OR ((Recinfo.VALUE2_TYPE is null) AND (X_VALUE2_TYPE is null)))
132 AND ((Recinfo.DISPLAY_COL_NUMBER = X_DISPLAY_COL_NUMBER)
133 OR ((Recinfo.DISPLAY_COL_NUMBER is null) AND (X_DISPLAY_COL_NUMBER is null)))
134 AND ((Recinfo.MAX_TOKEN_COUNT = X_MAX_TOKEN_COUNT)
135 OR ((Recinfo.MAX_TOKEN_COUNT is null) AND (X_MAX_TOKEN_COUNT is null)))
136 AND ((Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
137 OR ((Recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
138 AND ((Recinfo.COND_KEYWORD = X_COND_KEYWORD)
139 OR ((Recinfo.COND_KEYWORD is null) AND (X_COND_KEYWORD is null)))
140 ) then
141 null;
142 else
143 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
144 app_exception.raise_exception;
145 end if;
146 end loop;
147 return;
148 end LOCK_ROW;
149
150 procedure UPDATE_ROW (
151 X_QUERY_CONDITION_ID in NUMBER,
152 X_PARAMETER_KEY in VARCHAR2,
153 X_TEMPLATE_ID in NUMBER,
154 X_JOIN_CONDITION in VARCHAR2,
155 X_OPERAND_DATA_TYPE in VARCHAR2,
156 X_MANDATORY_FLAG in VARCHAR2,
157 X_DEFAULT_FLAG in VARCHAR2,
158 X_CONDITION_IN_USE_FLAG in VARCHAR2,
159 X_LEFT_OPERAND_TYPE in VARCHAR2,
160 X_VALUE1_TYPE in VARCHAR2,
161 X_VALUE2_TYPE in VARCHAR2,
162 X_DISPLAY_COL_NUMBER in NUMBER,
163 X_MAX_TOKEN_COUNT in NUMBER,
164 X_OBJECT_VERSION_NUMBER in NUMBER,
165 X_REQUEST_ID in NUMBER,
166 X_COND_KEYWORD in VARCHAR2,
167 X_LAST_UPDATE_DATE in DATE,
168 X_LAST_UPDATED_BY in NUMBER,
169 X_LAST_UPDATE_LOGIN in NUMBER
170 ) is
171 begin
172 update AMS_QUERY_CONDITION set
173 PARAMETER_KEY = X_PARAMETER_KEY,
174 TEMPLATE_ID = X_TEMPLATE_ID,
175 JOIN_CONDITION = X_JOIN_CONDITION,
176 OPERAND_DATA_TYPE = X_OPERAND_DATA_TYPE,
177 MANDATORY_FLAG = X_MANDATORY_FLAG,
178 DEFAULT_FLAG = X_DEFAULT_FLAG,
179 CONDITION_IN_USE_FLAG = X_CONDITION_IN_USE_FLAG,
180 LEFT_OPERAND_TYPE = X_LEFT_OPERAND_TYPE,
181 VALUE1_TYPE = X_VALUE1_TYPE,
182 VALUE2_TYPE = X_VALUE2_TYPE,
183 DISPLAY_COL_NUMBER = X_DISPLAY_COL_NUMBER,
184 MAX_TOKEN_COUNT = X_MAX_TOKEN_COUNT,
185 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
186 REQUEST_ID = X_REQUEST_ID,
187 COND_KEYWORD = X_COND_KEYWORD,
188 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
189 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
190 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
191 where QUERY_CONDITION_ID = X_QUERY_CONDITION_ID
192 and object_version_number = X_OBJECT_VERSION_NUMBER;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end UPDATE_ROW;
198
199 procedure DELETE_ROW (
200 X_QUERY_CONDITION_ID in NUMBER
201 ) is
202 begin
203 delete from AMS_QUERY_CONDITION
204 where QUERY_CONDITION_ID = X_QUERY_CONDITION_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 end DELETE_ROW;
211
212
213 PROCEDURE load_row (
214 X_QUERY_CONDITION_ID in NUMBER,
215 X_PARAMETER_KEY in VARCHAR2,
216 X_TEMPLATE_ID in NUMBER,
217 X_JOIN_CONDITION in VARCHAR2,
218 X_OPERAND_DATA_TYPE in VARCHAR2,
219 X_MANDATORY_FLAG in VARCHAR2,
220 X_DEFAULT_FLAG in VARCHAR2,
221 X_CONDITION_IN_USE_FLAG in VARCHAR2,
222 X_LEFT_OPERAND_TYPE in VARCHAR2,
223 X_VALUE1_TYPE in VARCHAR2,
224 X_VALUE2_TYPE in VARCHAR2,
225 X_DISPLAY_COL_NUMBER in NUMBER,
226 X_MAX_TOKEN_COUNT in NUMBER,
227 X_REQUEST_ID in NUMBER,
228 X_COND_KEYWORD in VARCHAR2,
229 x_owner IN VARCHAR2,
230 x_custom_mode IN VARCHAR2
231 ) is
232 l_user_id number := 0;
233 l_obj_verno number;
234 l_dummy_char varchar2(1);
235 l_row_id varchar2(100);
236 l_QUERY_CONDITION_ID number;
237 l_db_luby_id number;
238
239 CURSOR c_obj_verno IS
240 SELECT object_version_number, last_updated_by
241 FROM AMS_QUERY_CONDITION
242 WHERE QUERY_CONDITION_ID = x_QUERY_CONDITION_ID;
243
244 CURSOR c_chk_exists is
245 SELECT 'x'
246 FROM AMS_QUERY_CONDITION
247 WHERE QUERY_CONDITION_ID = x_QUERY_CONDITION_ID;
248
249 CURSOR c_get_id is
250 SELECT AMS_QUERY_CONDITION_s.NEXTVAL
251 FROM DUAL;
252 BEGIN
253 if X_OWNER = 'SEED' then
254 l_user_id := 1;
255 elsif X_OWNER = 'ORACLE' then
256 l_user_id := 2;
257 elsif X_OWNER = 'SYSADMIN' then
258 l_user_id := 0;
259 end if;
260
261 OPEN c_chk_exists;
262 FETCH c_chk_exists INTO l_dummy_char;
263 IF c_chk_exists%notfound THEN
264 CLOSE c_chk_exists;
265
266 IF x_QUERY_CONDITION_ID IS NULL THEN
267 OPEN c_get_id;
268 FETCH c_get_id INTO l_QUERY_CONDITION_ID;
269 CLOSE c_get_id;
270 ELSE
271 l_QUERY_CONDITION_ID := x_QUERY_CONDITION_ID;
272 END IF;
273 l_obj_verno := 1;
274
275 AMS_QUERY_COND_PKG.insert_row (
276 x_QUERY_CONDITION_ID => l_QUERY_CONDITION_ID,
277 x_last_update_date => SYSDATE,
278 x_last_updated_by => l_user_id,
279 x_creation_date => SYSDATE,
280 x_created_by => l_user_id,
281 x_last_update_login => 0,
282 x_object_version_number => l_obj_verno,
283 X_TEMPLATE_ID => X_TEMPLATE_ID,
284 X_PARAMETER_KEY => X_PARAMETER_KEY,
285 X_JOIN_CONDITION => X_JOIN_CONDITION,
286 X_OPERAND_DATA_TYPE => X_OPERAND_DATA_TYPE,
290 X_LEFT_OPERAND_TYPE => X_LEFT_OPERAND_TYPE,
287 X_MANDATORY_FLAG => X_MANDATORY_FLAG,
288 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
289 X_CONDITION_IN_USE_FLAG => X_CONDITION_IN_USE_FLAG,
291 X_VALUE1_TYPE => X_VALUE1_TYPE,
292 X_VALUE2_TYPE => X_VALUE2_TYPE,
293 X_DISPLAY_COL_NUMBER => X_DISPLAY_COL_NUMBER,
294 X_MAX_TOKEN_COUNT => X_MAX_TOKEN_COUNT,
295 X_COND_KEYWORD => X_COND_KEYWORD,
296 X_REQUEST_ID => 0
297 );
298
299
300 ELSE
301 CLOSE c_chk_exists;
302 OPEN c_obj_verno;
303 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
304 CLOSE c_obj_verno;
305
306
307 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
308 then
309
310 AMS_QUERY_COND_PKG.update_row (
311 x_QUERY_CONDITION_ID => x_QUERY_CONDITION_ID,
312 x_last_update_date => SYSDATE,
313 x_last_updated_by => l_user_id,
314 x_last_update_login => 0,
315 x_object_version_number => l_obj_verno,
316 X_TEMPLATE_ID => X_TEMPLATE_ID,
317 X_PARAMETER_KEY => X_PARAMETER_KEY,
318 X_JOIN_CONDITION => X_JOIN_CONDITION,
319 X_OPERAND_DATA_TYPE => X_OPERAND_DATA_TYPE,
320 X_MANDATORY_FLAG => X_MANDATORY_FLAG,
321 X_DEFAULT_FLAG => X_DEFAULT_FLAG,
322 X_CONDITION_IN_USE_FLAG => X_CONDITION_IN_USE_FLAG,
323 X_LEFT_OPERAND_TYPE => X_LEFT_OPERAND_TYPE,
324 X_VALUE1_TYPE => X_VALUE1_TYPE,
325 X_VALUE2_TYPE => X_VALUE2_TYPE,
326 X_DISPLAY_COL_NUMBER => X_DISPLAY_COL_NUMBER,
327 X_MAX_TOKEN_COUNT => X_MAX_TOKEN_COUNT,
328 X_COND_KEYWORD => X_COND_KEYWORD,
329 X_REQUEST_ID => 0
330 );
331
332 end if;
333
334 END IF;
335 END load_row;
336
337
338 end AMS_QUERY_COND_PKG;