[Home] [Help]
PACKAGE BODY: APPS.AMS_COLUMNS_METADATA_PKG
Source
1 package body AMS_COLUMNS_METADATA_PKG as
2 /* $Header: amslcmdb.pls 120.1 2005/06/27 05:38:21 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_COLUMNS_METADATA_ID in NUMBER,
6 X_SEARCH_EXCLUSION_GROUP in VARCHAR2,
7 X_SEARCH_OPERATOR_EXCLUSION_ID in NUMBER,
8 X_SEARCH_COLSPAN in VARCHAR2,
9 X_APPLICATION_ID in NUMBER,
10 X_SEARCH_CONDITION_ALLOWED_FLA in VARCHAR2,
11 X_HTML_FORM_PARAM in VARCHAR2,
12 X_DB_TABLE_NAME in VARCHAR2,
13 X_DB_COLUMN_NAME in VARCHAR2,
14 X_SEARCH_TYPE in VARCHAR2,
15 X_SEARCH_QUERY in VARCHAR2,
16 X_SEARCH_PARAM1 in VARCHAR2,
17 X_SECURITY_GROUP_ID in NUMBER,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 X_OBJECT_ATTRIBUTE in VARCHAR2,
20 X_OBJECT_TYPE in VARCHAR2,
21 X_ACTIVITY_TYPE_CODE in VARCHAR2,
22 X_AK_REGION_CODE in VARCHAR2,
23 X_AK_ATTRIBUTE_CODE in VARCHAR2,
24 X_SEEDED_FLAG in VARCHAR2,
25 X_SEARCH_DISPLAY_ALLOWED_FLAG in VARCHAR2,
26 X_SEARCH_GROUP in VARCHAR2,
27 X_SEARCH_PARAM11 in VARCHAR2,
28 X_SEARCH_PARAM12 in VARCHAR2,
29 X_SEARCH_PARAM13 in VARCHAR2,
30 X_SEARCH_PARAM14 in VARCHAR2,
31 X_SEARCH_PARAM15 in VARCHAR2,
32 X_OBJECT_RULE_ID in NUMBER,
33 X_SEARCH_PARAM2 in VARCHAR2,
34 X_SEARCH_PARAM3 in VARCHAR2,
35 X_SEARCH_PARAM4 in VARCHAR2,
36 X_SEARCH_PARAM5 in VARCHAR2,
37 X_SEARCH_PARAM6 in VARCHAR2,
38 X_SEARCH_PARAM7 in VARCHAR2,
39 X_SEARCH_PARAM8 in VARCHAR2,
40 X_SEARCH_PARAM9 in VARCHAR2,
41 X_SEARCH_PARAM10 in VARCHAR2,
42 X_SEARCH_DISPLAY_SCALE in VARCHAR2,
43 X_CREATION_DATE in DATE,
44 X_CREATED_BY in NUMBER,
45 X_LAST_UPDATE_DATE in DATE,
46 X_LAST_UPDATED_BY in NUMBER,
47 X_LAST_UPDATE_LOGIN in NUMBER
48 ) is
49
50 begin
51 insert into AMS_COLUMNS_METADATA (
52 SEARCH_EXCLUSION_GROUP,
53 SEARCH_OPERATOR_EXCLUSION_ID,
54 SEARCH_COLSPAN,
55 APPLICATION_ID,
56 SEARCH_CONDITION_ALLOWED_FLAG,
57 HTML_FORM_PARAM,
58 DB_TABLE_NAME,
59 DB_COLUMN_NAME,
60 SEARCH_TYPE,
61 SEARCH_QUERY,
62 SEARCH_PARAM1,
63 LAST_UPDATE_DATE,
64 --SECURITY_GROUP_ID,
65 OBJECT_VERSION_NUMBER,
66 OBJECT_ATTRIBUTE,
67 OBJECT_TYPE,
68 ACTIVITY_TYPE_CODE,
69 AK_REGION_CODE,
70 AK_ATTRIBUTE_CODE,
71 SEEDED_FLAG,
72 SEARCH_DISPLAY_ALLOWED_FLAG,
73 SEARCH_GROUP,
74 COLUMNS_METADATA_ID,
75 LAST_UPDATED_BY,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_LOGIN,
79 SEARCH_PARAM11,
80 SEARCH_PARAM12,
81 SEARCH_PARAM13,
82 SEARCH_PARAM14,
83 SEARCH_PARAM15,
84 OBJECT_RULE_ID,
85 SEARCH_PARAM2,
86 SEARCH_PARAM3,
87 SEARCH_PARAM4,
88 SEARCH_PARAM5,
89 SEARCH_PARAM6,
90 SEARCH_PARAM7,
91 SEARCH_PARAM8,
92 SEARCH_PARAM9,
93 SEARCH_PARAM10,
94 SEARCH_DISPLAY_SCALE
95 ) values (
96 X_SEARCH_EXCLUSION_GROUP,
97 X_SEARCH_OPERATOR_EXCLUSION_ID,
98 X_SEARCH_COLSPAN,
99 X_APPLICATION_ID,
100 X_SEARCH_CONDITION_ALLOWED_FLA,
101 X_HTML_FORM_PARAM,
102 X_DB_TABLE_NAME,
103 X_DB_COLUMN_NAME,
104 X_SEARCH_TYPE,
105 X_SEARCH_QUERY,
106 X_SEARCH_PARAM1,
107 X_LAST_UPDATE_DATE,
108 --X_SECURITY_GROUP_ID,
109 X_OBJECT_VERSION_NUMBER,
110 X_OBJECT_ATTRIBUTE,
111 X_OBJECT_TYPE,
112 X_ACTIVITY_TYPE_CODE,
113 X_AK_REGION_CODE,
114 X_AK_ATTRIBUTE_CODE,
115 X_SEEDED_FLAG,
116 X_SEARCH_DISPLAY_ALLOWED_FLAG,
117 X_SEARCH_GROUP,
118 X_COLUMNS_METADATA_ID,
119 X_LAST_UPDATED_BY,
120 X_CREATION_DATE,
121 X_CREATED_BY,
122 X_LAST_UPDATE_LOGIN,
123 X_SEARCH_PARAM11,
124 X_SEARCH_PARAM12,
125 X_SEARCH_PARAM13,
126 X_SEARCH_PARAM14,
127 X_SEARCH_PARAM15,
128 X_OBJECT_RULE_ID,
129 X_SEARCH_PARAM2,
130 X_SEARCH_PARAM3,
131 X_SEARCH_PARAM4,
132 X_SEARCH_PARAM5,
133 X_SEARCH_PARAM6,
134 X_SEARCH_PARAM7,
135 X_SEARCH_PARAM8,
136 X_SEARCH_PARAM9,
137 X_SEARCH_PARAM10,
138 X_SEARCH_DISPLAY_SCALE
139 );
140
141 end INSERT_ROW;
142
143 procedure LOCK_ROW (
144 X_COLUMNS_METADATA_ID in NUMBER,
145 X_SEARCH_EXCLUSION_GROUP in VARCHAR2,
146 X_SEARCH_OPERATOR_EXCLUSION_ID in NUMBER,
147 X_SEARCH_COLSPAN in VARCHAR2,
148 X_APPLICATION_ID in NUMBER,
149 X_SEARCH_CONDITION_ALLOWED_FLA in VARCHAR2,
150 X_HTML_FORM_PARAM in VARCHAR2,
151 X_DB_TABLE_NAME in VARCHAR2,
152 X_DB_COLUMN_NAME in VARCHAR2,
153 X_SEARCH_TYPE in VARCHAR2,
154 X_SEARCH_QUERY in VARCHAR2,
155 X_SEARCH_PARAM1 in VARCHAR2,
156 X_SECURITY_GROUP_ID in NUMBER,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_OBJECT_ATTRIBUTE in VARCHAR2,
159 X_OBJECT_TYPE in VARCHAR2,
160 X_ACTIVITY_TYPE_CODE in VARCHAR2,
161 X_AK_REGION_CODE in VARCHAR2,
162 X_AK_ATTRIBUTE_CODE in VARCHAR2,
163 X_SEEDED_FLAG in VARCHAR2,
164 X_SEARCH_DISPLAY_ALLOWED_FLAG in VARCHAR2,
165 X_SEARCH_GROUP in VARCHAR2,
166 X_SEARCH_PARAM11 in VARCHAR2,
167 X_SEARCH_PARAM12 in VARCHAR2,
168 X_SEARCH_PARAM13 in VARCHAR2,
169 X_SEARCH_PARAM14 in VARCHAR2,
170 X_SEARCH_PARAM15 in VARCHAR2,
171 X_OBJECT_RULE_ID in NUMBER,
172 X_SEARCH_PARAM2 in VARCHAR2,
173 X_SEARCH_PARAM3 in VARCHAR2,
174 X_SEARCH_PARAM4 in VARCHAR2,
175 X_SEARCH_PARAM5 in VARCHAR2,
176 X_SEARCH_PARAM6 in VARCHAR2,
177 X_SEARCH_PARAM7 in VARCHAR2,
178 X_SEARCH_PARAM8 in VARCHAR2,
179 X_SEARCH_PARAM9 in VARCHAR2,
180 X_SEARCH_PARAM10 in VARCHAR2,
181 X_SEARCH_DISPLAY_SCALE in VARCHAR2
182 ) is
183 cursor c1 is select
184 SEARCH_EXCLUSION_GROUP,
185 SEARCH_OPERATOR_EXCLUSION_ID,
186 SEARCH_COLSPAN,
187 APPLICATION_ID,
188 SEARCH_CONDITION_ALLOWED_FLAG,
189 HTML_FORM_PARAM,
190 DB_TABLE_NAME,
191 DB_COLUMN_NAME,
192 SEARCH_TYPE,
193 SEARCH_QUERY,
194 SEARCH_PARAM1,
195 SECURITY_GROUP_ID,
196 OBJECT_VERSION_NUMBER,
197 OBJECT_ATTRIBUTE,
198 OBJECT_TYPE,
199 ACTIVITY_TYPE_CODE,
200 AK_REGION_CODE,
201 AK_ATTRIBUTE_CODE,
202 SEEDED_FLAG,
203 SEARCH_DISPLAY_ALLOWED_FLAG,
204 SEARCH_GROUP,
205 SEARCH_PARAM11,
206 SEARCH_PARAM12,
207 SEARCH_PARAM13,
208 SEARCH_PARAM14,
209 SEARCH_PARAM15,
210 OBJECT_RULE_ID,
211 SEARCH_PARAM2,
212 SEARCH_PARAM3,
213 SEARCH_PARAM4,
214 SEARCH_PARAM5,
215 SEARCH_PARAM6,
216 SEARCH_PARAM7,
217 SEARCH_PARAM8,
218 SEARCH_PARAM9,
219 SEARCH_PARAM10,
220 SEARCH_DISPLAY_SCALE,
221 COLUMNS_METADATA_ID
222 from AMS_COLUMNS_METADATA
223 where COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID
224 for update of COLUMNS_METADATA_ID nowait;
225 begin
226 for tlinfo in c1 loop
227 if ( (tlinfo.COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID)
228 AND ((tlinfo.SEARCH_EXCLUSION_GROUP = X_SEARCH_EXCLUSION_GROUP)
229 OR ((tlinfo.SEARCH_EXCLUSION_GROUP is null) AND (X_SEARCH_EXCLUSION_GROUP is null)))
230 AND ((tlinfo.SEARCH_OPERATOR_EXCLUSION_ID = X_SEARCH_OPERATOR_EXCLUSION_ID)
231 OR ((tlinfo.SEARCH_OPERATOR_EXCLUSION_ID is null) AND (X_SEARCH_OPERATOR_EXCLUSION_ID is null)))
232 AND ((tlinfo.SEARCH_COLSPAN = X_SEARCH_COLSPAN)
233 OR ((tlinfo.SEARCH_COLSPAN is null) AND (X_SEARCH_COLSPAN is null)))
234 AND ((tlinfo.APPLICATION_ID = X_APPLICATION_ID)
235 OR ((tlinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
236 AND ((tlinfo.SEEDED_FLAG = X_SEEDED_FLAG)
237 OR ((tlinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
238 AND ((tlinfo.SEARCH_CONDITION_ALLOWED_FLAG = X_SEARCH_CONDITION_ALLOWED_FLA)
239 OR ((tlinfo.SEARCH_CONDITION_ALLOWED_FLAG is null) AND (X_SEARCH_CONDITION_ALLOWED_FLA is null)))
240 AND ((tlinfo.HTML_FORM_PARAM = X_HTML_FORM_PARAM)
241 OR ((tlinfo.HTML_FORM_PARAM is null) AND (X_HTML_FORM_PARAM is null)))
242 AND ((tlinfo.DB_TABLE_NAME = X_DB_TABLE_NAME)
243 OR ((tlinfo.DB_TABLE_NAME is null) AND (X_DB_TABLE_NAME is null)))
244 AND ((tlinfo.DB_COLUMN_NAME = X_DB_COLUMN_NAME)
245 OR ((tlinfo.DB_COLUMN_NAME is null) AND (X_DB_COLUMN_NAME is null)))
246 AND ((tlinfo.SEARCH_TYPE = X_SEARCH_TYPE)
247 OR ((tlinfo.SEARCH_TYPE is null) AND (X_SEARCH_TYPE is null)))
248 AND ((tlinfo.SEARCH_QUERY = X_SEARCH_QUERY)
249 OR ((tlinfo.SEARCH_QUERY is null) AND (X_SEARCH_QUERY is null)))
250 AND ((tlinfo.SEARCH_PARAM1 = X_SEARCH_PARAM1)
251 OR ((tlinfo.SEARCH_PARAM1 is null) AND (X_SEARCH_PARAM1 is null)))
252 --AND ((tlinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
253 -- OR ((tlinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
254 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
255 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
256 AND ((tlinfo.OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE)
257 OR ((tlinfo.OBJECT_ATTRIBUTE is null) AND (X_OBJECT_ATTRIBUTE is null)))
258 AND ((tlinfo.OBJECT_TYPE = X_OBJECT_TYPE)
259 OR ((tlinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
260 AND ((tlinfo.ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE)
261 OR ((tlinfo.ACTIVITY_TYPE_CODE is null) AND (X_ACTIVITY_TYPE_CODE is null)))
262 AND ((tlinfo.AK_REGION_CODE = X_AK_REGION_CODE)
263 OR ((tlinfo.AK_REGION_CODE is null) AND (X_AK_REGION_CODE is null)))
264 AND ((tlinfo.AK_ATTRIBUTE_CODE = X_AK_ATTRIBUTE_CODE)
265 OR ((tlinfo.AK_ATTRIBUTE_CODE is null) AND (X_AK_ATTRIBUTE_CODE is null)))
266 AND ((tlinfo.SEARCH_DISPLAY_ALLOWED_FLAG = X_SEARCH_DISPLAY_ALLOWED_FLAG)
267 OR ((tlinfo.SEARCH_DISPLAY_ALLOWED_FLAG is null) AND (X_SEARCH_DISPLAY_ALLOWED_FLAG is null)))
268 AND ((tlinfo.SEARCH_GROUP = X_SEARCH_GROUP)
269 OR ((tlinfo.SEARCH_GROUP is null) AND (X_SEARCH_GROUP is null)))
270 AND ((tlinfo.SEARCH_PARAM11 = X_SEARCH_PARAM11)
271 OR ((tlinfo.SEARCH_PARAM11 is null) AND (X_SEARCH_PARAM11 is null)))
272 AND ((tlinfo.SEARCH_PARAM12 = X_SEARCH_PARAM12)
273 OR ((tlinfo.SEARCH_PARAM12 is null) AND (X_SEARCH_PARAM12 is null)))
274 AND ((tlinfo.SEARCH_PARAM13 = X_SEARCH_PARAM13)
275 OR ((tlinfo.SEARCH_PARAM13 is null) AND (X_SEARCH_PARAM13 is null)))
276 AND ((tlinfo.SEARCH_PARAM14 = X_SEARCH_PARAM14)
277 OR ((tlinfo.SEARCH_PARAM14 is null) AND (X_SEARCH_PARAM14 is null)))
278 AND ((tlinfo.SEARCH_PARAM15 = X_SEARCH_PARAM15)
279 OR ((tlinfo.SEARCH_PARAM15 is null) AND (X_SEARCH_PARAM15 is null)))
280 AND ((tlinfo.OBJECT_RULE_ID = X_OBJECT_RULE_ID)
281 OR ((tlinfo.OBJECT_RULE_ID is null) AND (X_OBJECT_RULE_ID is null)))
282 AND ((tlinfo.SEARCH_PARAM2 = X_SEARCH_PARAM2)
283 OR ((tlinfo.SEARCH_PARAM2 is null) AND (X_SEARCH_PARAM2 is null)))
284 AND ((tlinfo.SEARCH_PARAM3 = X_SEARCH_PARAM3)
285 OR ((tlinfo.SEARCH_PARAM3 is null) AND (X_SEARCH_PARAM3 is null)))
286 AND ((tlinfo.SEARCH_PARAM4 = X_SEARCH_PARAM4)
287 OR ((tlinfo.SEARCH_PARAM4 is null) AND (X_SEARCH_PARAM4 is null)))
288 AND ((tlinfo.SEARCH_PARAM5 = X_SEARCH_PARAM5)
289 OR ((tlinfo.SEARCH_PARAM5 is null) AND (X_SEARCH_PARAM5 is null)))
290 AND ((tlinfo.SEARCH_PARAM6 = X_SEARCH_PARAM6)
291 OR ((tlinfo.SEARCH_PARAM6 is null) AND (X_SEARCH_PARAM6 is null)))
292 AND ((tlinfo.SEARCH_PARAM7 = X_SEARCH_PARAM7)
293 OR ((tlinfo.SEARCH_PARAM7 is null) AND (X_SEARCH_PARAM7 is null)))
294 AND ((tlinfo.SEARCH_PARAM8 = X_SEARCH_PARAM8)
295 OR ((tlinfo.SEARCH_PARAM8 is null) AND (X_SEARCH_PARAM8 is null)))
296 AND ((tlinfo.SEARCH_PARAM9 = X_SEARCH_PARAM9)
297 OR ((tlinfo.SEARCH_PARAM9 is null) AND (X_SEARCH_PARAM9 is null)))
298 AND ((tlinfo.SEARCH_PARAM10 = X_SEARCH_PARAM10)
299 OR ((tlinfo.SEARCH_PARAM10 is null) AND (X_SEARCH_PARAM10 is null)))
300 AND ((tlinfo.SEARCH_DISPLAY_SCALE = X_SEARCH_DISPLAY_SCALE)
301 OR ((tlinfo.SEARCH_DISPLAY_SCALE is null) AND (X_SEARCH_DISPLAY_SCALE is null)))
302 ) then
303 null;
304 else
305 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
306 app_exception.raise_exception;
307 end if;
308 end loop;
309 return;
310 end LOCK_ROW;
311
312 procedure UPDATE_ROW (
313 X_COLUMNS_METADATA_ID in NUMBER,
314 X_SEARCH_EXCLUSION_GROUP in VARCHAR2,
315 X_SEARCH_OPERATOR_EXCLUSION_ID in NUMBER,
316 X_SEARCH_COLSPAN in VARCHAR2,
317 X_APPLICATION_ID in NUMBER,
318 X_SEARCH_CONDITION_ALLOWED_FLA in VARCHAR2,
319 X_HTML_FORM_PARAM in VARCHAR2,
320 X_DB_TABLE_NAME in VARCHAR2,
321 X_DB_COLUMN_NAME in VARCHAR2,
322 X_SEARCH_TYPE in VARCHAR2,
323 X_SEARCH_QUERY in VARCHAR2,
324 X_SEARCH_PARAM1 in VARCHAR2,
325 X_SECURITY_GROUP_ID in NUMBER,
326 X_OBJECT_VERSION_NUMBER in NUMBER,
327 X_OBJECT_ATTRIBUTE in VARCHAR2,
328 X_OBJECT_TYPE in VARCHAR2,
329 X_ACTIVITY_TYPE_CODE in VARCHAR2,
330 X_AK_REGION_CODE in VARCHAR2,
331 X_AK_ATTRIBUTE_CODE in VARCHAR2,
332 X_SEEDED_FLAG in VARCHAR2,
333 X_SEARCH_DISPLAY_ALLOWED_FLAG in VARCHAR2,
334 X_SEARCH_GROUP in VARCHAR2,
335 X_SEARCH_PARAM11 in VARCHAR2,
336 X_SEARCH_PARAM12 in VARCHAR2,
337 X_SEARCH_PARAM13 in VARCHAR2,
338 X_SEARCH_PARAM14 in VARCHAR2,
339 X_SEARCH_PARAM15 in VARCHAR2,
340 X_OBJECT_RULE_ID in NUMBER,
341 X_SEARCH_PARAM2 in VARCHAR2,
342 X_SEARCH_PARAM3 in VARCHAR2,
343 X_SEARCH_PARAM4 in VARCHAR2,
344 X_SEARCH_PARAM5 in VARCHAR2,
345 X_SEARCH_PARAM6 in VARCHAR2,
346 X_SEARCH_PARAM7 in VARCHAR2,
347 X_SEARCH_PARAM8 in VARCHAR2,
348 X_SEARCH_PARAM9 in VARCHAR2,
349 X_SEARCH_PARAM10 in VARCHAR2,
350 X_SEARCH_DISPLAY_SCALE in VARCHAR2,
351 X_LAST_UPDATE_DATE in DATE,
352 X_LAST_UPDATED_BY in NUMBER,
353 X_LAST_UPDATE_LOGIN in NUMBER
354 ) is
355 begin
356 update AMS_COLUMNS_METADATA set
357 SEARCH_EXCLUSION_GROUP = X_SEARCH_EXCLUSION_GROUP,
358 SEARCH_OPERATOR_EXCLUSION_ID = X_SEARCH_OPERATOR_EXCLUSION_ID,
359 SEARCH_COLSPAN = X_SEARCH_COLSPAN,
360 APPLICATION_ID = X_APPLICATION_ID,
361 SEARCH_CONDITION_ALLOWED_FLAG = X_SEARCH_CONDITION_ALLOWED_FLA,
362 HTML_FORM_PARAM = X_HTML_FORM_PARAM,
363 DB_TABLE_NAME = X_DB_TABLE_NAME,
364 DB_COLUMN_NAME = X_DB_COLUMN_NAME,
365 SEARCH_TYPE = X_SEARCH_TYPE,
366 SEARCH_QUERY = X_SEARCH_QUERY,
367 SEARCH_PARAM1 = X_SEARCH_PARAM1,
368 --SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
369 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
370 OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE,
371 OBJECT_TYPE = X_OBJECT_TYPE,
372 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
373 AK_REGION_CODE = X_AK_REGION_CODE,
374 AK_ATTRIBUTE_CODE = X_AK_ATTRIBUTE_CODE,
375 SEEDED_FLAG = X_SEEDED_FLAG,
376 SEARCH_DISPLAY_ALLOWED_FLAG = X_SEARCH_DISPLAY_ALLOWED_FLAG,
377 SEARCH_GROUP = X_SEARCH_GROUP,
378 SEARCH_PARAM11 = X_SEARCH_PARAM11,
379 SEARCH_PARAM12 = X_SEARCH_PARAM12,
380 SEARCH_PARAM13 = X_SEARCH_PARAM13,
381 SEARCH_PARAM14 = X_SEARCH_PARAM14,
382 SEARCH_PARAM15 = X_SEARCH_PARAM15,
383 OBJECT_RULE_ID = X_OBJECT_RULE_ID,
384 SEARCH_PARAM2 = X_SEARCH_PARAM2,
385 SEARCH_PARAM3 = X_SEARCH_PARAM3,
386 SEARCH_PARAM4 = X_SEARCH_PARAM4,
387 SEARCH_PARAM5 = X_SEARCH_PARAM5,
388 SEARCH_PARAM6 = X_SEARCH_PARAM6,
389 SEARCH_PARAM7 = X_SEARCH_PARAM7,
390 SEARCH_PARAM8 = X_SEARCH_PARAM8,
391 SEARCH_PARAM9 = X_SEARCH_PARAM9,
392 SEARCH_PARAM10 = X_SEARCH_PARAM10,
393 SEARCH_DISPLAY_SCALE = X_SEARCH_DISPLAY_SCALE,
394 COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID,
395 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
396 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
397 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
398 where COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
399
400 if (sql%notfound) then
401 raise no_data_found;
402 end if;
403 end UPDATE_ROW;
404
405 procedure DELETE_ROW (
406 X_COLUMNS_METADATA_ID in NUMBER
407 ) is
408 begin
409 delete from AMS_COLUMNS_METADATA
410 where COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
411
412 if (sql%notfound) then
413 raise no_data_found;
414 end if;
415
416 end DELETE_ROW;
417
418 procedure LOAD_ROW (
419 X_COLUMNS_METADATA_ID in NUMBER,
420 X_SEARCH_EXCLUSION_GROUP in VARCHAR2,
421 X_SEARCH_OPERATOR_EXCLUSION_ID in NUMBER,
422 X_SEARCH_COLSPAN in VARCHAR2,
423 X_APPLICATION_ID in NUMBER,
424 X_SEARCH_CONDITION_ALLOWED_FLA in VARCHAR2,
425 X_HTML_FORM_PARAM in VARCHAR2,
426 X_DB_TABLE_NAME in VARCHAR2,
427 X_DB_COLUMN_NAME in VARCHAR2,
428 X_SEARCH_TYPE in VARCHAR2,
429 X_SEARCH_QUERY in VARCHAR2,
430 X_SEARCH_PARAM1 in VARCHAR2,
431 X_SECURITY_GROUP_ID in NUMBER,
432 X_OBJECT_VERSION_NUMBER in NUMBER,
433 X_OBJECT_ATTRIBUTE in VARCHAR2,
434 X_OBJECT_TYPE in VARCHAR2,
435 X_ACTIVITY_TYPE_CODE in VARCHAR2,
436 X_AK_REGION_CODE in VARCHAR2,
437 X_AK_ATTRIBUTE_CODE in VARCHAR2,
438 X_SEEDED_FLAG in VARCHAR2,
439 X_SEARCH_DISPLAY_ALLOWED_FLAG in VARCHAR2,
440 X_SEARCH_GROUP in VARCHAR2,
441 X_SEARCH_PARAM11 in VARCHAR2,
442 X_SEARCH_PARAM12 in VARCHAR2,
443 X_SEARCH_PARAM13 in VARCHAR2,
444 X_SEARCH_PARAM14 in VARCHAR2,
445 X_SEARCH_PARAM15 in VARCHAR2,
446 X_OBJECT_RULE_ID in NUMBER,
447 X_SEARCH_PARAM2 in VARCHAR2,
448 X_SEARCH_PARAM3 in VARCHAR2,
449 X_SEARCH_PARAM4 in VARCHAR2,
450 X_SEARCH_PARAM5 in VARCHAR2,
451 X_SEARCH_PARAM6 in VARCHAR2,
452 X_SEARCH_PARAM7 in VARCHAR2,
453 X_SEARCH_PARAM8 in VARCHAR2,
454 X_SEARCH_PARAM9 in VARCHAR2,
455 X_SEARCH_PARAM10 in VARCHAR2,
456 X_SEARCH_DISPLAY_SCALE in VARCHAR2,
457 X_OWNER in VARCHAR2)is
458 l_user_id number := 0;
459 l_COLUMNS_METADATA_ID number;
460 l_obj_verno number;
461 l_dummy_char varchar2(1);
462 l_row_id varchar2(100);
463
464
465 -- Commented after OZF is migrated from AMS : aranka
466
467 cursor c_obj_verno is
468 select object_version_number, columns_metadata_id
469 from AMS_COLUMNS_METADATA
470 -- where OBJECT_TYPE = X_OBJECT_TYPE
471 -- and OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
472 -- and nvl(ACTIVITY_TYPE_CODE,0) = nvl(X_ACTIVITY_TYPE_CODE, 0)
473 -- and HTML_FORM_PARAM = X_HTML_FORM_PARAM
474 -- and APPLICATION_ID = X_APPLICATION_ID;
475 where COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
476
477 -- Commented after OZF is migrated from AMS : aranka
478 cursor c_chk_colmtd_exists is
479 select 'x'
480 from AMS_COLUMNS_METADATA
481 -- where OBJECT_TYPE = X_OBJECT_TYPE
482 -- and OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
483 -- and nvl(ACTIVITY_TYPE_CODE,0) = nvl(X_ACTIVITY_TYPE_CODE, 0)
484 -- and HTML_FORM_PARAM = X_HTML_FORM_PARAM
485 -- and APPLICATION_ID = X_APPLICATION_ID;
486 where COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
487
488 cursor c_get_colmtd_id is
489 select AMS_COLUMNS_METADATA_S.nextval
490 from dual;
491
492 BEGIN
493 if X_OWNER = 'SEED' then
494 l_user_id := 1;
495 end if;
496
497 open c_chk_colmtd_exists;
498 fetch c_chk_colmtd_exists into l_dummy_char;
499 if c_chk_colmtd_exists%notfound
500 then
501 close c_chk_colmtd_exists;
502 if X_COLUMNS_METADATA_ID is null
503 then
504 open c_get_colmtd_id;
505 fetch c_get_colmtd_id into l_COLUMNS_METADATA_ID;
506 close c_get_colmtd_id;
507 else
508 l_COLUMNS_METADATA_ID := X_COLUMNS_METADATA_ID;
509 end if;
510
511 AMS_COLUMNS_METADATA_PKG.INSERT_ROW (
512 X_ROWID => l_row_id,
513 X_COLUMNS_METADATA_ID => l_COLUMNS_METADATA_ID,
514 X_SEARCH_EXCLUSION_GROUP => X_SEARCH_EXCLUSION_GROUP,
515 X_SEARCH_OPERATOR_EXCLUSION_ID => X_SEARCH_OPERATOR_EXCLUSION_ID,
516 X_SEARCH_COLSPAN => X_SEARCH_COLSPAN,
517 X_APPLICATION_ID => X_APPLICATION_ID,
518 X_SEEDED_FLAG => X_SEEDED_FLAG,
519 X_SEARCH_CONDITION_ALLOWED_FLA => X_SEARCH_CONDITION_ALLOWED_FLA,
520 X_HTML_FORM_PARAM => X_HTML_FORM_PARAM,
521 X_DB_TABLE_NAME => X_DB_TABLE_NAME,
522 X_DB_COLUMN_NAME => X_DB_COLUMN_NAME,
523 X_SEARCH_TYPE => X_SEARCH_TYPE,
524 X_SEARCH_QUERY => X_SEARCH_QUERY,
525 X_SEARCH_PARAM1 => X_SEARCH_PARAM1,
526 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
527 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
528 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
529 X_OBJECT_TYPE => X_OBJECT_TYPE,
530 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
531 X_AK_REGION_CODE => X_AK_REGION_CODE,
532 X_AK_ATTRIBUTE_CODE => X_AK_ATTRIBUTE_CODE,
533 X_SEARCH_DISPLAY_ALLOWED_FLAG => X_SEARCH_DISPLAY_ALLOWED_FLAG,
534 X_SEARCH_GROUP => X_SEARCH_GROUP,
535 X_SEARCH_PARAM11 => X_SEARCH_PARAM11,
536 X_SEARCH_PARAM12 => X_SEARCH_PARAM12,
537 X_SEARCH_PARAM13 => X_SEARCH_PARAM13,
538 X_SEARCH_PARAM14 => X_SEARCH_PARAM14,
539 X_SEARCH_PARAM15 => X_SEARCH_PARAM15,
540 X_OBJECT_RULE_ID => X_OBJECT_RULE_ID,
541 X_SEARCH_PARAM2 => X_SEARCH_PARAM2,
542 X_SEARCH_PARAM3 => X_SEARCH_PARAM3,
543 X_SEARCH_PARAM4 => X_SEARCH_PARAM4,
544 X_SEARCH_PARAM5 => X_SEARCH_PARAM5,
545 X_SEARCH_PARAM6 => X_SEARCH_PARAM6,
546 X_SEARCH_PARAM7 => X_SEARCH_PARAM7,
547 X_SEARCH_PARAM8 => X_SEARCH_PARAM8,
548 X_SEARCH_PARAM9 => X_SEARCH_PARAM9,
549 X_SEARCH_PARAM10 => X_SEARCH_PARAM10,
550 X_SEARCH_DISPLAY_SCALE => X_SEARCH_DISPLAY_SCALE,
551 X_CREATION_DATE => sysdate,
552 X_CREATED_BY => l_user_id,
553 X_LAST_UPDATE_DATE => sysdate,
554 X_LAST_UPDATED_BY => l_user_id,
555 X_LAST_UPDATE_LOGIN => 0
556 );
557 else
558 close c_chk_colmtd_exists;
559 open c_obj_verno;
560 fetch c_obj_verno into l_obj_verno, l_COLUMNS_METADATA_ID;
561 close c_obj_verno;
562 -- assigning value for l_user_status_id
563 --l_COLUMNS_METADATA_ID := X_COLUMNS_METADATA_ID;
564 AMS_COLUMNS_METADATA_PKG.UPDATE_ROW(
565 X_COLUMNS_METADATA_ID => l_COLUMNS_METADATA_ID,
566 X_SEARCH_EXCLUSION_GROUP => X_SEARCH_EXCLUSION_GROUP,
567 X_SEARCH_OPERATOR_EXCLUSION_ID => X_SEARCH_OPERATOR_EXCLUSION_ID,
568 X_SEARCH_COLSPAN => X_SEARCH_COLSPAN,
569 X_APPLICATION_ID => X_APPLICATION_ID,
570 X_SEEDED_FLAG => X_SEEDED_FLAG,
571 X_SEARCH_CONDITION_ALLOWED_FLA => X_SEARCH_CONDITION_ALLOWED_FLA,
572 X_HTML_FORM_PARAM => X_HTML_FORM_PARAM,
573 X_DB_TABLE_NAME => X_DB_TABLE_NAME,
574 X_DB_COLUMN_NAME => X_DB_COLUMN_NAME,
575 X_SEARCH_TYPE => X_SEARCH_TYPE,
576 X_SEARCH_QUERY => X_SEARCH_QUERY,
577 X_SEARCH_PARAM1 => X_SEARCH_PARAM1,
578 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
579 X_OBJECT_VERSION_NUMBER => l_obj_verno,
580 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
581 X_OBJECT_TYPE => X_OBJECT_TYPE,
582 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
583 X_AK_REGION_CODE => X_AK_REGION_CODE,
584 X_AK_ATTRIBUTE_CODE => X_AK_ATTRIBUTE_CODE,
585 X_SEARCH_DISPLAY_ALLOWED_FLAG => X_SEARCH_DISPLAY_ALLOWED_FLAG,
586 X_SEARCH_GROUP => X_SEARCH_GROUP,
587 X_SEARCH_PARAM11 => X_SEARCH_PARAM11,
588 X_SEARCH_PARAM12 => X_SEARCH_PARAM12,
589 X_SEARCH_PARAM13 => X_SEARCH_PARAM13,
590 X_SEARCH_PARAM14 => X_SEARCH_PARAM14,
591 X_SEARCH_PARAM15 => X_SEARCH_PARAM15,
592 X_OBJECT_RULE_ID => X_OBJECT_RULE_ID,
593 X_SEARCH_PARAM2 => X_SEARCH_PARAM2,
594 X_SEARCH_PARAM3 => X_SEARCH_PARAM3,
595 X_SEARCH_PARAM4 => X_SEARCH_PARAM4,
596 X_SEARCH_PARAM5 => X_SEARCH_PARAM5,
597 X_SEARCH_PARAM6 => X_SEARCH_PARAM6,
598 X_SEARCH_PARAM7 => X_SEARCH_PARAM7,
599 X_SEARCH_PARAM8 => X_SEARCH_PARAM8,
600 X_SEARCH_PARAM9 => X_SEARCH_PARAM9,
601 X_SEARCH_PARAM10 => X_SEARCH_PARAM10,
602 X_SEARCH_DISPLAY_SCALE => X_SEARCH_DISPLAY_SCALE,
603 X_LAST_UPDATE_DATE => SYSDATE,
604 X_LAST_UPDATED_BY => l_user_id,
605 X_LAST_UPDATE_LOGIN => 0
606 );
607 END IF;
608
609 end LOAD_ROW;
610
611 end AMS_COLUMNS_METADATA_PKG;