[Home] [Help]
PACKAGE BODY: APPS.EGO_SEARCH_FWK_PUB
Source
1 PACKAGE BODY EGO_SEARCH_FWK_PUB AS
2 /* $Header: EGOPSFWB.pls 120.5.12000000.2 2007/05/03 12:38:19 ksathupa ship $ */
3 /*---------------------------------------------------------------------------+
4 | This package contains public API for Search Framework |
5 +---------------------------------------------------------------------------*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EGO_SEARCH_FWK_PUB';
8 g_current_user_id NUMBER := FND_GLOBAL.User_Id;
9 g_current_login_id NUMBER := FND_GLOBAL.Login_Id;
10 g_app_name VARCHAR2(3) := 'EGO';
11 g_null_value VARCHAR2(6) := '*NULL*';
12
13
14 --Update the default information
15 PROCEDURE Update_Result_Format_Default
16 (
17 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
18 X_REGION_APPLICATION_ID IN NUMBER,
19 X_REGION_CODE IN VARCHAR2,
20 X_WEB_USER_ID IN NUMBER,
21 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
22 X_IMPORT_FLAG IN VARCHAR2,
23 X_CLASSIFICATION_1 IN VARCHAR2,
24 X_CLASSIFICATION_2 IN VARCHAR2,
25 X_CLASSIFICATION_3 IN VARCHAR2,
26 X_DATA_LEVEL IN VARCHAR2
27 )
28 IS
29
30 l_count NUMBER;
31 l_name VARCHAR2(2000);
32
33 CURSOR fetch_custmztn_code IS
34 select name_query.customization_code customization_code
35 from
36 AK_CUSTOM_REGIONS_TL name_query ,
37 AK_CUSTOM_REGIONS level_id_query ,
38 AK_CUSTOM_REGIONS user_id_query ,
39 EGO_CUSTOMIZATION_EXT ect ,
40 AK_CUSTOM_REGIONS data_level_query
41 where name_query.property_name = 'RESULT_NAME'
42 and name_query.language = USERENV('LANG')
43 and name_query.customization_application_id = level_id_query.customization_application_id
44 and name_query.customization_code = level_id_query.customization_code
45 and name_query.region_application_id = level_id_query.region_application_id
46 and name_query.region_code = level_id_query.region_code
47 and level_id_query.property_name = 'CUSTOMIZATION_LEVEL_ID'
48 and name_query.customization_application_id = ect.customization_application_id
49 and name_query.customization_code = ect.customization_code
50 and name_query.region_application_id = ect.region_application_id
51 and name_query.region_code = ect.region_code
52 and data_level_query.property_name (+) = 'DATA_LEVEL'
53 and name_query.customization_application_id = data_level_query.customization_application_id(+)
54 and name_query.customization_code = data_level_query.customization_code (+)
55 and name_query.region_application_id = data_level_query.region_application_id(+)
56 and name_query.region_code = data_level_query.region_code(+)
57 and user_id_query.property_name = 'WEB_USER_ID'
58 and name_query.customization_application_id = user_id_query.customization_application_id
59 and name_query.customization_code = user_id_query.customization_code
60 and name_query.region_application_id = user_id_query.region_application_id
61 and name_query.region_code = user_id_query.region_code
62 and name_query.customization_application_id = x_customization_application_id
63 and name_query.region_application_id = x_region_application_id
64 and name_query.region_code = x_region_code
65 and level_id_query.property_number_value = x_customization_level_id
66 and nvl(user_id_query.property_number_value, -1) = nvl(x_web_user_id, -1)
67 and nvl(ect.classification1, g_null_value) = nvl(x_classification_1, g_null_value)
68 and nvl(ect.classification2, g_null_value) = nvl(x_classification_2, g_null_value)
69 and nvl(ect.classification3, g_null_value) = nvl(x_classification_3, g_null_value)
70 and nvl(data_level_query.property_varchar2_value, g_null_value) = nvl(x_data_level, g_null_value);
71
72 TYPE custmztn_code_tbl IS TABLE OF EGO_RESULTS_FORMAT_V.CUSTOMIZATION_CODE%TYPE INDEX BY BINARY_INTEGER;
73
74 l_custmztn_code_tbl custmztn_code_tbl;
75
76 BEGIN
77
78 OPEN fetch_custmztn_code;
79 FETCH fetch_custmztn_code BULK COLLECT INTO l_custmztn_code_tbl;
80 CLOSE fetch_custmztn_code;
81
82 FORALL i IN 1..l_custmztn_code_tbl.COUNT
83 UPDATE AK_CUSTOM_REGIONS
84 SET
85 PROPERTY_VARCHAR2_VALUE = 'N'
86 WHERE
87 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
88 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
89 AND REGION_CODE = X_REGION_CODE
90 AND PROPERTY_NAME = 'DEFAULT_RESULT_FLAG'
91 AND CUSTOMIZATION_CODE = l_custmztn_code_tbl(i);
92
93 FORALL i IN 1..l_custmztn_code_tbl.COUNT
94 UPDATE AK_CUSTOM_REGIONS_TL
95 SET
96 PROPERTY_VARCHAR2_VALUE = 'N'
97 WHERE
98 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
99 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
100 AND REGION_CODE = X_REGION_CODE
101 AND PROPERTY_NAME = 'DEFAULT_RESULT_FLAG'
102 AND CUSTOMIZATION_CODE = l_custmztn_code_tbl(i);
103
104 END;
105
106 --Test whether or not this criteria template name is in existence
107
108 PROCEDURE Check_Result_Format_Deletion
109 (
110 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
111 X_CUSTOMIZATION_CODE IN VARCHAR2,
112 X_REGION_APPLICATION_ID IN NUMBER,
113 X_REGION_CODE IN VARCHAR2,
114 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
115 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
116 X_ERRORCODE OUT NOCOPY NUMBER
117 )
118 IS
119
120 l_count NUMBER;
121 l_name VARCHAR2(2000);
122
123 CURSOR get_name IS
124 select name_query.property_varchar2_value name
125 from AK_CUSTOM_REGIONS_TL name_query
126 where name_query.property_name = 'RESULT_NAME'
127 and name_query.language = USERENV('LANG')
128 and name_query.customization_application_id = x_customization_application_id
129 and name_query.customization_code = x_customization_code
130 and name_query.region_application_id = x_region_application_id
131 and name_query.region_code = x_region_code;
132
133 BEGIN
134
135 IF FND_API.To_Boolean(x_init_msg_list) THEN
136 FND_MSG_PUB.Initialize;
137 END IF;
138
139 SELECT
140 COUNT(*)
141 INTO
142 l_count
143 FROM
144 EGO_CUSTOMIZATION_EXT
145 WHERE
146 RF_CUSTOMIZATION_APPL_ID = X_CUSTOMIZATION_APPLICATION_ID
147 AND RF_CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
148 AND RF_REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
149 AND RF_REGION_CODE = X_REGION_CODE;
150
151 IF (l_count > 0) THEN
152 FOR name_rec IN get_name LOOP
153 l_name := name_rec.name;
154 exit;
155 END LOOP;
156
157 FND_MESSAGE.Set_Name(g_app_name, 'EGO_RF_RF_EXISTS');
158 FND_MESSAGE.Set_Token('NAME', l_name);
159 FND_MSG_PUB.Add;
160
161 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
162
163 ELSE
164
165 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
166
167 END IF;
168
169 END Check_Result_Format_Deletion;
170
171 FUNCTION Criteria_Template_Name_Exists
172 (
173 X_NAME IN VARCHAR2,
174 X_WEB_USER_ID IN NUMBER,
175 X_CUSTOMIZATION_CODE IN VARCHAR2 DEFAULT NULL,
176 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
177 X_REGION_APPLICATION_ID IN NUMBER,
178 X_REGION_CODE IN VARCHAR2,
179 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
180 X_CLASSIFICATION1 IN VARCHAR2,
181 X_CLASSIFICATION2 IN VARCHAR2,
182 X_CLASSIFICATION3 IN VARCHAR2
183 )
184 RETURN BOOLEAN
185 IS
186
187 l_count NUMBER;
188
189 BEGIN
190
191 IF (X_CUSTOMIZATION_CODE IS NOT NULL) THEN
192
193 IF (X_CUSTOMIZATION_LEVEL_ID = 30) THEN
194
195 SELECT
196 COUNT(*)
197 INTO
198 l_count
199 FROM
200 EGO_CRITERIA_TEMPLATES_V
201 WHERE
202 NAME = X_NAME
203 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
204 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
205 AND REGION_CODE = X_REGION_CODE
206 AND (CUSTOMIZATION_LEVEL_ID = 60 OR (CUSTOMIZATION_LEVEL_ID = 30 AND WEB_USER_ID = X_WEB_USER_ID))
207 AND CUSTOMIZATION_CODE <> X_CUSTOMIZATION_CODE
208 AND NVL(X_CLASSIFICATION1, g_null_value) = NVL(CLASSIFICATION1, g_null_value)
209 AND NVL(X_CLASSIFICATION2, g_null_value) = NVL(CLASSIFICATION2, g_null_value)
210 AND NVL(X_CLASSIFICATION3, g_null_value) = NVL(CLASSIFICATION3, g_null_value);
211
212 ELSE
213
214 SELECT
215 COUNT(*)
216 INTO
217 l_count
218 FROM
219 EGO_CRITERIA_TEMPLATES_V
220 WHERE
221 NAME = X_NAME
222 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
223 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
224 AND REGION_CODE = X_REGION_CODE
225 AND CUSTOMIZATION_CODE <> X_CUSTOMIZATION_CODE
226 AND NVL(X_CLASSIFICATION1, g_null_value) = NVL(CLASSIFICATION1, g_null_value)
227 AND NVL(X_CLASSIFICATION2, g_null_value) = NVL(CLASSIFICATION2, g_null_value)
228 AND NVL(X_CLASSIFICATION3, g_null_value) = NVL(CLASSIFICATION3, g_null_value);
229
230 END IF;
231
232 ELSE
233
234 IF (X_CUSTOMIZATION_LEVEL_ID = 30) THEN
235
236 SELECT
237 COUNT(*)
238 INTO
239 l_count
240 FROM
241 EGO_CRITERIA_TEMPLATES_V
242 WHERE
243 NAME = X_NAME
244 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
245 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
246 AND REGION_CODE = X_REGION_CODE
247 AND (CUSTOMIZATION_LEVEL_ID = 60 OR (CUSTOMIZATION_LEVEL_ID = 30 AND WEB_USER_ID = X_WEB_USER_ID))
248 AND NVL(X_CLASSIFICATION1, g_null_value) = NVL(CLASSIFICATION1, g_null_value)
249 AND NVL(X_CLASSIFICATION2, g_null_value) = NVL(CLASSIFICATION2, g_null_value)
250 AND NVL(X_CLASSIFICATION3, g_null_value) = NVL(CLASSIFICATION3, g_null_value);
251
252 ELSE
253
254 SELECT
255 COUNT(*)
256 INTO
257 l_count
258 FROM
259 EGO_CRITERIA_TEMPLATES_V
260 WHERE
261 NAME = X_NAME
262 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
263 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
264 AND REGION_CODE = X_REGION_CODE
265 AND NVL(X_CLASSIFICATION1, g_null_value) = NVL(CLASSIFICATION1, g_null_value)
266 AND NVL(X_CLASSIFICATION2, g_null_value) = NVL(CLASSIFICATION2, g_null_value)
267 AND NVL(X_CLASSIFICATION3, g_null_value) = NVL(CLASSIFICATION3, g_null_value);
268
269 END IF;
270
271 END IF;
272
273 RETURN l_count > 0;
274
275 END Criteria_Template_Name_Exists;
276
277 FUNCTION Results_Format_Name_Exists
278 (
279 X_NAME IN VARCHAR2,
280 X_WEB_USER_ID IN NUMBER,
281 X_CUSTOMIZATION_CODE IN VARCHAR2,
282 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
283 X_REGION_APPLICATION_ID IN NUMBER,
284 X_REGION_CODE IN VARCHAR2,
285 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
286 X_CLASSIFICATION1 IN VARCHAR2,
287 X_CLASSIFICATION2 IN VARCHAR2,
288 X_CLASSIFICATION3 IN VARCHAR2
289 )
290 RETURN BOOLEAN
291 IS
292
293 l_count NUMBER;
294
295 -- get count with customization code (wcc) , with level id (wlv)
296 CURSOR get_cnt_wccwlv IS
297 select count(*) count
298 from
299 AK_CUSTOM_REGIONS_TL name_query ,
300 AK_CUSTOM_REGIONS level_id_query ,
301 AK_CUSTOM_REGIONS user_id_query ,
302 EGO_CUSTOMIZATION_EXT ect
303 where name_query.property_name = 'RESULT_NAME'
304 and name_query.language = USERENV('LANG')
305 and name_query.customization_application_id = level_id_query.customization_application_id
306 and name_query.customization_code = level_id_query.customization_code
307 and name_query.region_application_id = level_id_query.region_application_id
308 and name_query.region_code = level_id_query.region_code
309 and level_id_query.property_name = 'CUSTOMIZATION_LEVEL_ID'
310 and name_query.customization_application_id = ect.customization_application_id
311 and name_query.customization_code = ect.customization_code
312 and name_query.region_application_id = ect.region_application_id
313 and name_query.region_code = ect.region_code
314 and user_id_query.property_name = 'WEB_USER_ID'
315 and name_query.customization_application_id = user_id_query.customization_application_id
316 and name_query.customization_code = user_id_query.customization_code
317 and name_query.region_application_id = user_id_query.region_application_id
318 and name_query.region_code = user_id_query.region_code
319 and name_query.property_varchar2_value = x_name
323 and (level_id_query.property_number_value = 60
320 and name_query.customization_application_id = x_customization_application_id
321 and name_query.region_application_id = x_region_application_id
322 and name_query.region_code = x_region_code
324 OR
325 (level_id_query.property_number_value = 30 AND
326 user_id_query.property_number_value = x_web_user_id))
327 and name_query.customization_code <> x_customization_code
328 and nvl(ect.classification1, g_null_value) = nvl(x_classification1, g_null_value)
329 and nvl(ect.classification2, g_null_value) = nvl(x_classification2, g_null_value)
330 and nvl(ect.classification3, g_null_value) = nvl(x_classification3, g_null_value);
331
332 -- get count with customization code (wcc) , without level id (wolv)
333 CURSOR get_cnt_wccwolv IS
334 select count(*) count
335 from
336 AK_CUSTOM_REGIONS_TL name_query ,
337 AK_CUSTOM_REGIONS level_id_query ,
338 AK_CUSTOM_REGIONS user_id_query ,
339 EGO_CUSTOMIZATION_EXT ect
340 where name_query.property_name = 'RESULT_NAME'
341 and name_query.language = USERENV('LANG')
342 and name_query.customization_application_id = level_id_query.customization_application_id
343 and name_query.customization_code = level_id_query.customization_code
344 and name_query.region_application_id = level_id_query.region_application_id
345 and name_query.region_code = level_id_query.region_code
346 and level_id_query.property_name = 'CUSTOMIZATION_LEVEL_ID'
347 and name_query.customization_application_id = ect.customization_application_id
348 and name_query.customization_code = ect.customization_code
349 and name_query.region_application_id = ect.region_application_id
350 and name_query.region_code = ect.region_code
351 and user_id_query.property_name = 'WEB_USER_ID'
352 and name_query.customization_application_id = user_id_query.customization_application_id
353 and name_query.customization_code = user_id_query.customization_code
354 and name_query.region_application_id = user_id_query.region_application_id
355 and name_query.region_code = user_id_query.region_code
356 and name_query.property_varchar2_value = x_name
357 and name_query.customization_application_id = x_customization_application_id
358 and name_query.region_application_id = x_region_application_id
359 and name_query.region_code = x_region_code
360 and name_query.customization_code <> x_customization_code
361 and nvl(ect.classification1, g_null_value) = nvl(x_classification1, g_null_value)
362 and nvl(ect.classification2, g_null_value) = nvl(x_classification2, g_null_value)
363 and nvl(ect.classification3, g_null_value) = nvl(x_classification3, g_null_value);
364
365 -- get count without customization code (wocc) , with level id (wlv)
366 CURSOR get_cnt_woccwlv IS
367 select count(*) count
368 from
369 AK_CUSTOM_REGIONS_TL name_query ,
370 AK_CUSTOM_REGIONS level_id_query ,
371 AK_CUSTOM_REGIONS user_id_query ,
372 EGO_CUSTOMIZATION_EXT ect
373 where name_query.property_name = 'RESULT_NAME'
374 and name_query.language = USERENV('LANG')
375 and name_query.customization_application_id = level_id_query.customization_application_id
376 and name_query.customization_code = level_id_query.customization_code
377 and name_query.region_application_id = level_id_query.region_application_id
378 and name_query.region_code = level_id_query.region_code
379 and level_id_query.property_name = 'CUSTOMIZATION_LEVEL_ID'
380 and name_query.customization_application_id = ect.customization_application_id
381 and name_query.customization_code = ect.customization_code
382 and name_query.region_application_id = ect.region_application_id
383 and name_query.region_code = ect.region_code
384 and user_id_query.property_name = 'WEB_USER_ID'
385 and name_query.customization_application_id = user_id_query.customization_application_id
386 and name_query.customization_code = user_id_query.customization_code
387 and name_query.region_application_id = user_id_query.region_application_id
388 and name_query.region_code = user_id_query.region_code
389 and name_query.property_varchar2_value = x_name
390 and name_query.customization_application_id = x_customization_application_id
391 and name_query.region_application_id = x_region_application_id
392 and name_query.region_code = x_region_code
393 and (level_id_query.property_number_value = 60
394 OR
395 (level_id_query.property_number_value = 30 AND
396 user_id_query.property_number_value = x_web_user_id))
397 and nvl(ect.classification1, g_null_value) = nvl(x_classification1, g_null_value)
398 and nvl(ect.classification2, g_null_value) = nvl(x_classification2, g_null_value)
399 and nvl(ect.classification3, g_null_value) = nvl(x_classification3, g_null_value);
400
401 -- get count without customization code (wocc) , without level id (wolv)
402 CURSOR get_cnt_woccwolv IS
403 select count(*) count
404 from
405 AK_CUSTOM_REGIONS_TL name_query ,
406 AK_CUSTOM_REGIONS level_id_query ,
407 AK_CUSTOM_REGIONS user_id_query ,
408 EGO_CUSTOMIZATION_EXT ect
409 where name_query.property_name = 'RESULT_NAME'
410 and name_query.language = USERENV('LANG')
414 and name_query.region_code = level_id_query.region_code
411 and name_query.customization_application_id = level_id_query.customization_application_id
412 and name_query.customization_code = level_id_query.customization_code
413 and name_query.region_application_id = level_id_query.region_application_id
415 and level_id_query.property_name = 'CUSTOMIZATION_LEVEL_ID'
416 and name_query.customization_application_id = ect.customization_application_id
417 and name_query.customization_code = ect.customization_code
418 and name_query.region_application_id = ect.region_application_id
419 and name_query.region_code = ect.region_code
420 and user_id_query.property_name = 'WEB_USER_ID'
421 and name_query.customization_application_id = user_id_query.customization_application_id
422 and name_query.customization_code = user_id_query.customization_code
423 and name_query.region_application_id = user_id_query.region_application_id
424 and name_query.region_code = user_id_query.region_code
425 and name_query.property_varchar2_value = x_name
426 and name_query.customization_application_id = x_customization_application_id
427 and name_query.region_application_id = x_region_application_id
428 and name_query.region_code = x_region_code
429 and nvl(ect.classification1, g_null_value) = nvl(x_classification1, g_null_value)
430 and nvl(ect.classification2, g_null_value) = nvl(x_classification2, g_null_value)
431 and nvl(ect.classification3, g_null_value) = nvl(x_classification3, g_null_value);
432
433 BEGIN
434
435 IF (X_CUSTOMIZATION_CODE IS NOT NULL) THEN
436
437 IF (X_CUSTOMIZATION_LEVEL_ID = 30) THEN
438
439 FOR count_rec IN get_cnt_wccwlv LOOP
440 l_count := count_rec.count;
441 END LOOP;
442
443 ELSE
444
445 FOR count_rec IN get_cnt_wccwolv LOOP
446 l_count := count_rec.count;
447 END LOOP;
448
449 END IF;
450
451 ELSE
452
453 IF (X_CUSTOMIZATION_LEVEL_ID = 30) THEN
454
455 FOR count_rec IN get_cnt_woccwlv LOOP
456 l_count := count_rec.count;
457 END LOOP;
458
459 ELSE
460
461 FOR count_rec IN get_cnt_woccwolv LOOP
462 l_count := count_rec.count;
463 END LOOP;
464
465 END IF;
466
467 END IF;
468
469 RETURN l_count > 0;
470
471 END Results_Format_Name_Exists;
472
473
474 PROCEDURE Create_Criteria_Template
475 (
476 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
477 X_CUSTOMIZATION_CODE IN VARCHAR2,
478 X_REGION_APPLICATION_ID IN NUMBER,
479 X_REGION_CODE IN VARCHAR2,
480 X_NAME IN VARCHAR2,
481 X_DESCRIPTION IN VARCHAR2,
482 X_VERTICALIZATION_ID IN VARCHAR2,
483 X_LOCALIZATION_CODE IN VARCHAR2,
484 X_ORG_ID IN NUMBER,
485 X_SITE_ID IN NUMBER,
486 X_RESPONSIBILITY_ID IN NUMBER,
487 X_WEB_USER_ID IN NUMBER,
488 X_DEFAULT_CUSTOMIZATION_FLAG IN VARCHAR2,
489 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
490 X_CREATED_BY IN NUMBER,
491 X_CREATION_DATE IN DATE,
492 X_LAST_UPDATED_BY IN NUMBER,
493 X_LAST_UPDATE_DATE IN DATE,
494 X_LAST_UPDATE_LOGIN IN NUMBER,
495 X_START_DATE_ACTIVE IN DATE,
496 X_END_DATE_ACTIVE IN DATE,
497 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
498 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
499 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
500 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER DEFAULT NULL,
501 X_RF_CUSTOMIZATION_CODE IN VARCHAR2 DEFAULT NULL,
502 X_RF_REGION_APPLICATION_ID IN NUMBER DEFAULT NULL,
503 X_RF_REGION_CODE IN VARCHAR2 DEFAULT NULL,
504 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
505 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
506 X_ERRORCODE OUT NOCOPY NUMBER
507 )
508 IS
509
510 l_Sysdate DATE := Sysdate;
511 l_rowid VARCHAR2(255);
512 l_current_user NUMBER;
513
514 BEGIN
515
516 IF (FND_API.To_Boolean(x_init_msg_list) = TRUE) THEN
517 FND_MSG_PUB.Initialize;
518 END IF;
519
520 --first check unique constraint
521
522 IF (Criteria_Template_Name_Exists(X_NAME
523 ,X_WEB_USER_ID
524 ,NULL
525 ,X_CUSTOMIZATION_APPLICATION_ID
526 ,X_REGION_APPLICATION_ID
527 ,X_REGION_CODE
528 ,X_CUSTOMIZATION_LEVEL_ID
529 ,X_CLASSIFICATION_1
530 ,X_CLASSIFICATION_2
531 ,X_CLASSIFICATION_3) = FALSE) THEN
532
536
533 -- first thing updating other criteria template default flag if "Y" in this one
534 -- only update the one with same customization_level
535 -- if user level customization, only update his default
537 IF(X_DEFAULT_CUSTOMIZATION_FLAG = 'Y') THEN
538
539 UPDATE
540 AK_CUSTOMIZATIONS
541 SET
542 DEFAULT_CUSTOMIZATION_FLAG = 'N'
543 WHERE
544 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
545 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
546 AND REGION_CODE = X_REGION_CODE
547 AND CUSTOMIZATION_LEVEL_ID = X_CUSTOMIZATION_LEVEL_ID
548 AND NVL(WEB_USER_ID, -1) = NVL(X_WEB_USER_ID, -1)
549 AND CUSTOMIZATION_CODE IN (
550 SELECT
551 CUSTOMIZATION_CODE
552 FROM
553 EGO_CUSTOMIZATION_EXT
554 WHERE
555 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
556 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
557 AND REGION_CODE = X_REGION_CODE
558 AND NVL(CLASSIFICATION1, ' ') = NVL(X_CLASSIFICATION_1, ' ')
559 AND NVL(CLASSIFICATION2, ' ') = NVL(X_CLASSIFICATION_2, ' ')
560 AND NVL(CLASSIFICATION3, ' ') = NVL(X_CLASSIFICATION_3, ' ')
561 );
562 END IF;
563
564 IF( X_CREATED_BY IS NOT NULL) THEN --added for bug 3964722;
565 l_current_user := X_CREATED_BY;
566 ELSE
567 l_current_user := g_current_user_id;
568 END IF;
569
570
571 AK_CUSTOMIZATIONS_PKG.INSERT_ROW
572 (
573 X_ROWID => l_rowid,
574 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
575 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
576 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
577 X_REGION_CODE => X_REGION_CODE,
578 X_NAME => X_NAME,
579 X_DESCRIPTION => X_DESCRIPTION,
580 X_VERTICALIZATION_ID => X_VERTICALIZATION_ID,
581 X_LOCALIZATION_CODE => X_LOCALIZATION_CODE,
582 X_ORG_ID => X_ORG_ID,
583 X_SITE_ID => X_SITE_ID,
584 X_RESPONSIBILITY_ID => X_RESPONSIBILITY_ID,
585 X_WEB_USER_ID => X_WEB_USER_ID,
586 X_DEFAULT_CUSTOMIZATION_FLAG => X_DEFAULT_CUSTOMIZATION_FLAG,
587 X_CUSTOMIZATION_LEVEL_ID => X_CUSTOMIZATION_LEVEL_ID,
588 X_CREATED_BY => X_CREATED_BY,
589 X_CREATION_DATE => X_CREATION_DATE,
590 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
591 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
592 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
593 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
594 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE
595 );
596
597 INSERT INTO EGO_CUSTOMIZATION_EXT
598 (
599 CUSTOMIZATION_APPLICATION_ID,
600 CUSTOMIZATION_CODE,
601 REGION_APPLICATION_ID,
602 REGION_CODE,
603 CLASSIFICATION1,
604 CLASSIFICATION2,
605 CLASSIFICATION3,
606 RF_CUSTOMIZATION_APPL_ID,
607 RF_CUSTOMIZATION_CODE,
608 RF_REGION_APPLICATION_ID,
609 RF_REGION_CODE,
610 CREATED_BY,
611 CREATION_DATE,
612 LAST_UPDATED_BY,
613 LAST_UPDATE_DATE,
614 LAST_UPDATE_LOGIN
615 )
616 VALUES
617 (
618 X_CUSTOMIZATION_APPLICATION_ID,
619 X_CUSTOMIZATION_CODE,
620 X_REGION_APPLICATION_ID,
621 X_REGION_CODE,
622 X_CLASSIFICATION_1,
623 X_CLASSIFICATION_2,
624 X_CLASSIFICATION_3,
625 X_RF_CUSTOMIZATION_APPL_ID,
626 X_RF_CUSTOMIZATION_CODE,
627 X_RF_REGION_APPLICATION_ID,
628 X_RF_REGION_CODE,
629 l_current_user,
630 l_Sysdate,
631 l_current_user,
632 l_Sysdate,
633 l_current_user
634 );
635
636 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
637
638 ELSE
639
640 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_CRITERIA_TEMPLATE');
641 FND_MSG_PUB.Add;
642
643 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
644
645 END IF;
646
647 END Create_Criteria_Template;
648
649 -------------------------------------------------------------
650
651 PROCEDURE Update_Criteria_Template
652 (
653 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
654 X_CUSTOMIZATION_CODE IN VARCHAR2,
655 X_REGION_APPLICATION_ID IN NUMBER,
656 X_REGION_CODE IN VARCHAR2,
657 X_NAME IN VARCHAR2,
658 X_DESCRIPTION IN VARCHAR2,
662 X_SITE_ID IN NUMBER,
659 X_VERTICALIZATION_ID IN VARCHAR2,
660 X_LOCALIZATION_CODE IN VARCHAR2,
661 X_ORG_ID IN NUMBER,
663 X_RESPONSIBILITY_ID IN NUMBER,
664 X_WEB_USER_ID IN NUMBER,
665 X_DEFAULT_CUSTOMIZATION_FLAG IN VARCHAR2,
666 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
667 X_LAST_UPDATED_BY IN NUMBER,
668 X_LAST_UPDATE_DATE IN DATE,
669 X_LAST_UPDATE_LOGIN IN NUMBER,
670 X_START_DATE_ACTIVE IN DATE,
671 X_END_DATE_ACTIVE IN DATE,
672 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
673 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
674 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
675 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER DEFAULT NULL,
676 X_RF_CUSTOMIZATION_CODE IN VARCHAR2 DEFAULT NULL,
677 X_RF_REGION_APPLICATION_ID IN NUMBER DEFAULT NULL,
678 X_RF_REGION_CODE IN VARCHAR2 DEFAULT NULL,
679 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
680 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
681 X_ERRORCODE OUT NOCOPY NUMBER
682 )
683 IS
684
685 l_Sysdate DATE := Sysdate;
686 l_rowid VARCHAR2(255);
687
688 BEGIN
689
690 IF FND_API.To_Boolean(x_init_msg_list) THEN
691 FND_MSG_PUB.Initialize;
692 END IF;
693
694 IF (Criteria_Template_Name_Exists(X_NAME
695 ,X_WEB_USER_ID
696 ,X_CUSTOMIZATION_CODE
697 ,X_CUSTOMIZATION_APPLICATION_ID
698 ,X_REGION_APPLICATION_ID
699 ,X_REGION_CODE
700 ,X_CUSTOMIZATION_LEVEL_ID
701 ,X_CLASSIFICATION_1
702 ,X_CLASSIFICATION_2
703 ,X_CLASSIFICATION_3) = FALSE) THEN
704
705 -- first thing updating other criteria template default flag if "Y" in this one
706
707 IF( X_DEFAULT_CUSTOMIZATION_FLAG = 'Y') THEN
708
709 UPDATE
710 AK_CUSTOMIZATIONS
711 SET
712 DEFAULT_CUSTOMIZATION_FLAG = 'N'
713 WHERE
714 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
715 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
716 AND REGION_CODE = X_REGION_CODE
717 AND CUSTOMIZATION_LEVEL_ID = X_CUSTOMIZATION_LEVEL_ID
718 AND NVL(WEB_USER_ID, -1) = NVL(X_WEB_USER_ID, -1)
719 AND CUSTOMIZATION_CODE IN (
720 SELECT
721 CUSTOMIZATION_CODE
722 FROM
723 EGO_CUSTOMIZATION_EXT
724 WHERE
725 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
726 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
727 AND REGION_CODE = X_REGION_CODE
728 AND NVL(CLASSIFICATION1, ' ') = NVL(X_CLASSIFICATION_1, ' ')
729 AND NVL(CLASSIFICATION2, ' ') = NVL(X_CLASSIFICATION_2, ' ')
730 AND NVL(CLASSIFICATION3, ' ') = NVL(X_CLASSIFICATION_3, ' ')
731 );
732 END IF;
733
734
735 AK_CUSTOMIZATIONS_PKG.UPDATE_ROW
736 (
737 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
738 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
739 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
740 X_REGION_CODE => X_REGION_CODE,
741 X_NAME => X_NAME,
742 X_DESCRIPTION => X_DESCRIPTION,
743 X_VERTICALIZATION_ID => X_VERTICALIZATION_ID,
744 X_LOCALIZATION_CODE => X_LOCALIZATION_CODE,
745 X_ORG_ID => X_ORG_ID,
746 X_SITE_ID => X_SITE_ID,
747 X_RESPONSIBILITY_ID => X_RESPONSIBILITY_ID,
748 X_WEB_USER_ID => X_WEB_USER_ID,
749 X_DEFAULT_CUSTOMIZATION_FLAG => X_DEFAULT_CUSTOMIZATION_FLAG,
750 X_CUSTOMIZATION_LEVEL_ID => X_CUSTOMIZATION_LEVEL_ID,
751 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
752 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
753 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
754 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
755 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE
756 );
757
758 --Now we try to update the results format
759
760 UPDATE
761 EGO_CUSTOMIZATION_EXT
762 SET
763 RF_CUSTOMIZATION_APPL_ID = X_RF_CUSTOMIZATION_APPL_ID,
764 RF_CUSTOMIZATION_CODE = X_RF_CUSTOMIZATION_CODE,
765 RF_REGION_APPLICATION_ID = X_RF_REGION_APPLICATION_ID,
766 RF_REGION_CODE = X_RF_REGION_CODE
767 WHERE
771 AND REGION_CODE = X_REGION_CODE;
768 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
769 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
770 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
772
773 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
774
775 ELSE
776
777 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_CRITERIA_TEMPLATE');
778 FND_MSG_PUB.Add;
779
780 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
781
782 END IF;
783
784 END Update_Criteria_Template;
785
786 -------------------------------------------------------------
787
788 PROCEDURE Delete_Criteria_Template
789 (
790 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
791 X_CUSTOMIZATION_CODE IN VARCHAR2,
792 X_REGION_APPLICATION_ID IN NUMBER,
793 X_REGION_CODE IN VARCHAR2,
794 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
795 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
796 X_ERRORCODE OUT NOCOPY NUMBER
797 )
798 IS
799
800 l_name VARCHAR2(2000);
801 l_count NUMBER := NULL;
802
803 BEGIN
804
805 IF FND_API.To_Boolean(x_init_msg_list) THEN
806 FND_MSG_PUB.Initialize;
807 END IF;
808
809 SELECT
810 COUNT(*)
811 INTO
812 l_count
813 FROM
814 EGO_CUSTOMIZATION_RF
815 WHERE
816 RF_CUSTOMIZATION_APPL_ID = X_CUSTOMIZATION_APPLICATION_ID
817 AND RF_CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
818 AND RF_REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
819 AND RF_REGION_CODE = X_REGION_CODE;
820
821 IF (l_count = 0) THEN
822
823 AK_CUSTOMIZATIONS_PKG.DELETE_ROW
824 (
825 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
826 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
827 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
828 X_REGION_CODE => X_REGION_CODE
829 );
830
831 DELETE
832 FROM
833 EGO_CUSTOMIZATION_EXT
834 WHERE
835 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
836 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
837 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
838 AND REGION_CODE = X_REGION_CODE;
839
840 DELETE
841 FROM
842 EGO_CUSTOMIZATION_RF
843 WHERE
844 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
845 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
846 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
847 AND REGION_CODE = X_REGION_CODE;
848
849 DELETE
850 FROM
851 AK_CRITERIA
852 WHERE
853 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
854 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
855 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
856 AND REGION_CODE = X_REGION_CODE;
857
858 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
859
860 ELSE
861
862 SELECT
863 NAME INTO l_name
864 FROM
865 EGO_CRITERIA_TEMPLATES_V
866 WHERE
867 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
868 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
869 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
870 AND REGION_CODE = X_REGION_CODE;
871
872 FND_MESSAGE.Set_Name(g_app_name, 'EGO_CT_RF_EXISTS');
873 FND_MESSAGE.Set_Token('NAME', l_name);
874 FND_MSG_PUB.Add;
875
876 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
877
878 END IF;
879
880 END Delete_Criteria_Template;
881
882 PROCEDURE translate_criteria_template
883 ( p_customization_application_id IN NUMBER
884 ,p_customization_code IN VARCHAR2
885 ,p_region_application_id IN NUMBER
886 ,p_region_code IN VARCHAR2
887 ,p_customization_level_id IN NUMBER
888 ,p_last_update_date IN VARCHAR2
889 ,p_last_updated_by IN NUMBER
890 ,p_name IN VARCHAR2
891 ,p_description IN VARCHAR2
892 ,x_return_status OUT NOCOPY VARCHAR2
893 ,x_msg_data OUT NOCOPY VARCHAR2
894 ) IS
895
896 CURSOR c_get_last_update_info IS
897 SELECT last_updated_by, last_update_date
898 FROM ak_customizations_tl
899 WHERE customization_application_id = p_customization_application_id
900 AND customization_code = p_customization_code
901 AND region_application_id = p_region_application_id
902 AND region_code = p_region_code
903 AND USERENV('LANG') IN (language, source_lang);
904
905 l_last_update_date ak_customizations.last_update_date%TYPE;
906 l_last_updated_by ak_customizations.last_updated_by%TYPE;
907 l_verticalization_id ak_customizations.verticalization_id%TYPE;
908 l_localization_code ak_customizations.localization_code%TYPE;
912 l_web_user_id ak_customizations.web_user_id%TYPE;
909 l_org_id ak_customizations.org_id%TYPE;
910 l_site_id ak_customizations.site_id%TYPE;
911 l_responsibility_id ak_customizations.responsibility_id%TYPE;
913 l_default_customization_flag ak_customizations.default_customization_flag%TYPE;
914 l_customization_level_id ak_customizations.customization_level_id%TYPE;
915 l_start_date_active ak_customizations.start_date_active%TYPE;
916 l_end_date_active ak_customizations.end_date_active%TYPE;
917
918 BEGIN
919
920 OPEN C_get_last_update_info;
921 FETCH c_get_last_update_info
922 INTO l_last_updated_by, l_last_update_date;
923 CLOSE c_get_last_update_info;
924
925 IF (fnd_load_util.upload_test(p_last_updated_by
926 ,p_last_update_date
927 ,l_last_updated_by
928 ,l_last_update_date
929 ,NULL)) THEN
930 SELECT
931 verticalization_id
932 ,localization_code
933 ,org_id
934 ,site_id
935 ,responsibility_id
936 ,web_user_id
937 ,default_customization_flag
938 ,customization_level_id
939 ,start_date_active
940 ,end_date_active
941 INTO
942 l_verticalization_id
943 ,l_localization_code
944 ,l_org_id
945 ,l_site_id
946 ,l_responsibility_id
947 ,l_web_user_id
948 ,l_default_customization_flag
949 ,l_customization_level_id
950 ,l_start_date_active
951 ,l_end_date_active
952 FROM ak_customizations
953 WHERE CUSTOMIZATION_APPLICATION_ID = p_customization_application_id
954 AND CUSTOMIZATION_CODE = p_customization_code
955 AND REGION_APPLICATION_ID = p_region_application_id
956 AND REGION_CODE = p_region_code
957 AND CUSTOMIZATION_LEVEL_ID = p_customization_level_id;
958
959 AK_CUSTOMIZATIONS_PKG.UPDATE_ROW
960 (
961 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
962 X_CUSTOMIZATION_CODE => p_customization_code,
963 X_REGION_APPLICATION_ID => p_region_application_id,
964 X_REGION_CODE => p_region_code,
965 X_NAME => p_name,
966 X_DESCRIPTION => p_description,
967 X_VERTICALIZATION_ID => l_verticalization_id,
968 X_LOCALIZATION_CODE => l_localization_code,
969 X_ORG_ID => l_org_id,
970 X_SITE_ID => l_site_id,
971 X_RESPONSIBILITY_ID => l_responsibility_id,
972 X_WEB_USER_ID => l_web_user_id,
973 X_DEFAULT_CUSTOMIZATION_FLAG => l_default_customization_flag,
974 X_CUSTOMIZATION_LEVEL_ID => p_customization_level_id,
975 X_LAST_UPDATED_BY => p_last_updated_by,
976 X_LAST_UPDATE_DATE => SYSDATE,
977 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
978 X_START_DATE_ACTIVE => l_start_date_active,
979 X_END_DATE_ACTIVE => l_end_date_active
980 );
981 END IF;
982 x_return_status := 'S';
983 EXCEPTION
984 WHEN OTHERS THEN
985 x_return_status := 'E';
986 x_msg_data := SQLERRM;
987 END translate_criteria_template;
988
989
990 ---------------------------------------------------------
991 PROCEDURE create_criteria_template_rf
992 (
993 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
994 X_CUSTOMIZATION_CODE IN VARCHAR2,
995 X_REGION_APPLICATION_ID IN NUMBER,
996 X_REGION_CODE IN VARCHAR2,
997 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER,
998 X_RF_CUSTOMIZATION_CODE IN VARCHAR2,
999 X_RF_REGION_APPLICATION_ID IN NUMBER,
1000 X_RF_REGION_CODE IN VARCHAR2,
1001 X_RF_TAG IN VARCHAR2,
1002 X_CREATED_BY IN NUMBER,
1003 X_CREATION_DATE IN DATE,
1004 X_LAST_UPDATED_BY IN NUMBER,
1005 X_LAST_UPDATE_DATE IN DATE,
1006 X_LAST_UPDATE_LOGIN IN NUMBER,
1007 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1008 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1009 X_ERRORCODE OUT NOCOPY NUMBER
1010 )
1011 IS
1012
1013 l_Sysdate DATE := Sysdate;
1014
1015 BEGIN
1016
1017 IF FND_API.To_Boolean(x_init_msg_list) THEN
1018 FND_MSG_PUB.Initialize;
1019 END IF;
1020
1021 IF (X_RF_CUSTOMIZATION_CODE IS NOT NULL) THEN
1022
1023 INSERT INTO EGO_CUSTOMIZATION_RF
1024 (
1025 CUSTOMIZATION_APPLICATION_ID,
1026 CUSTOMIZATION_CODE,
1027 REGION_APPLICATION_ID,
1028 REGION_CODE,
1029 RF_CUSTOMIZATION_APPL_ID,
1030 RF_CUSTOMIZATION_CODE,
1031 RF_REGION_APPLICATION_ID,
1032 RF_REGION_CODE,
1033 RF_TAG,
1034 CREATED_BY,
1035 CREATION_DATE,
1036 LAST_UPDATED_BY,
1040 VALUES
1037 LAST_UPDATE_DATE,
1038 LAST_UPDATE_LOGIN
1039 )
1041 (
1042 X_CUSTOMIZATION_APPLICATION_ID,
1043 X_CUSTOMIZATION_CODE,
1044 X_REGION_APPLICATION_ID,
1045 X_REGION_CODE,
1046 X_RF_CUSTOMIZATION_APPL_ID,
1047 X_RF_CUSTOMIZATION_CODE,
1048 X_RF_REGION_APPLICATION_ID,
1049 X_RF_REGION_CODE,
1050 X_RF_TAG,
1051 X_CREATED_BY,
1052 X_CREATION_DATE,
1053 X_LAST_UPDATED_BY,
1054 X_LAST_UPDATE_DATE,
1055 X_LAST_UPDATE_LOGIN
1056 );
1057 END IF;
1058
1059 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1060 X_RETURN_STATUS := 'T';
1061
1062
1063 END create_criteria_template_rf;
1064
1065
1066 PROCEDURE update_criteria_template_rf
1067 (
1068 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1069 X_CUSTOMIZATION_CODE IN VARCHAR2,
1070 X_REGION_APPLICATION_ID IN NUMBER,
1071 X_REGION_CODE IN VARCHAR2,
1072 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER,
1073 X_RF_CUSTOMIZATION_CODE IN VARCHAR2,
1074 X_RF_REGION_APPLICATION_ID IN NUMBER,
1075 X_RF_REGION_CODE IN VARCHAR2,
1076 X_RF_TAG IN VARCHAR2,
1077 X_LAST_UPDATED_BY IN NUMBER,
1078 X_LAST_UPDATE_DATE IN DATE,
1079 X_LAST_UPDATE_LOGIN IN NUMBER,
1080 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1081 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1082 X_ERRORCODE OUT NOCOPY NUMBER
1083 )
1084 IS
1085
1086 l_Sysdate DATE := Sysdate;
1087 l_count NUMBER;
1088
1089 BEGIN
1090
1091 IF FND_API.To_Boolean(x_init_msg_list) THEN
1092 FND_MSG_PUB.Initialize;
1093 END IF;
1094
1095 SELECT
1096 COUNT(*) INTO l_count
1097 FROM
1098 EGO_CUSTOMIZATION_RF
1099 WHERE
1100 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1101 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1102 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1103 AND REGION_CODE = X_REGION_CODE
1104 AND RF_TAG = X_RF_TAG;
1105
1106 IF (l_count > 0) THEN
1107
1108 IF (X_RF_CUSTOMIZATION_CODE IS NULL) THEN
1109 DELETE_CRITERIA_TEMPLATE_RF(
1110 X_CUSTOMIZATION_APPLICATION_ID
1111 ,X_CUSTOMIZATION_CODE
1112 ,X_REGION_APPLICATION_ID
1113 ,X_REGION_CODE
1114 ,X_RF_TAG
1115 ,FND_API.G_FALSE
1116 ,X_RETURN_STATUS
1117 ,X_ERRORCODE
1118 );
1119 ELSE
1120
1121
1122 UPDATE EGO_CUSTOMIZATION_RF SET
1123 RF_CUSTOMIZATION_APPL_ID = X_RF_CUSTOMIZATION_APPL_ID,
1124 RF_CUSTOMIZATION_CODE = X_RF_CUSTOMIZATION_CODE,
1125 RF_REGION_APPLICATION_ID = X_RF_REGION_APPLICATION_ID,
1126 RF_REGION_CODE = X_RF_REGION_CODE,
1127 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1128 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1129 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1130 WHERE
1131 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1132 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1133 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1134 AND REGION_CODE = X_REGION_CODE
1135 AND RF_TAG = X_RF_TAG;
1136
1137 END IF;
1138
1139 END IF;
1140
1141 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1142 X_RETURN_STATUS := 'T';
1143
1144 END update_criteria_template_rf;
1145
1146
1147 PROCEDURE delete_criteria_template_rf
1148 (
1149 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1150 X_CUSTOMIZATION_CODE IN VARCHAR2,
1151 X_REGION_APPLICATION_ID IN NUMBER,
1152 X_REGION_CODE IN VARCHAR2,
1153 X_RF_TAG IN VARCHAR2,
1154 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1155 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1156 X_ERRORCODE OUT NOCOPY NUMBER
1157 )
1158 IS
1159
1160 l_Sysdate DATE := Sysdate;
1161 l_count NUMBER;
1162
1163 BEGIN
1164
1165 IF FND_API.To_Boolean(x_init_msg_list) THEN
1166 FND_MSG_PUB.Initialize;
1167 END IF;
1168
1169 SELECT
1170 COUNT(*) INTO l_count
1171 FROM
1172 EGO_CUSTOMIZATION_RF
1173 WHERE
1174 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1175 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1176 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1177 AND REGION_CODE = X_REGION_CODE
1178 AND RF_TAG = X_RF_TAG;
1179
1180 IF (l_count > 0) THEN
1181
1182 DELETE FROM EGO_CUSTOMIZATION_RF
1183 WHERE
1184 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1188 AND RF_TAG = X_RF_TAG;
1185 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1186 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1187 AND REGION_CODE = X_REGION_CODE
1189
1190 END IF;
1191
1192 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1193 X_RETURN_STATUS := 'T';
1194
1195 END delete_criteria_template_rf;
1196
1197 ---------------------------------------------------------------
1198 PROCEDURE create_result_format
1199 (
1200 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1201 X_CUSTOMIZATION_CODE IN VARCHAR2,
1202 X_REGION_APPLICATION_ID IN NUMBER,
1203 X_REGION_CODE IN VARCHAR2,
1204 X_NAME IN VARCHAR2,
1205 X_DESCRIPTION IN VARCHAR2,
1206 X_NUM_ROWS_DISPLAYED IN NUMBER,
1207 X_DEFAULT_RESULT_FLAG IN VARCHAR2,
1208 X_SITE_ID IN NUMBER,
1209 X_WEB_USER_ID IN NUMBER,
1210 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
1211 X_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
1212 X_DATA_LEVEL IN VARCHAR2 DEFAULT NULL,
1213 X_CREATED_BY IN NUMBER,
1214 X_CREATION_DATE IN DATE,
1215 X_LAST_UPDATED_BY IN NUMBER,
1216 X_LAST_UPDATE_DATE IN DATE,
1217 X_LAST_UPDATE_LOGIN IN NUMBER,
1218 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
1219 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
1220 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
1221 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1222 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1223 X_ERRORCODE OUT NOCOPY NUMBER
1224 )
1225 IS
1226
1227 l_Sysdate DATE := Sysdate;
1228 l_rowid VARCHAR2(255);
1229
1230 BEGIN
1231
1232 IF FND_API.To_Boolean(x_init_msg_list) THEN
1233 FND_MSG_PUB.Initialize;
1234 END IF;
1235
1236 --first check unique constraint
1237
1238 IF (Results_Format_Name_Exists(X_NAME
1239 ,X_WEB_USER_ID
1240 ,NULL
1241 ,X_CUSTOMIZATION_APPLICATION_ID
1242 ,X_REGION_APPLICATION_ID
1243 ,X_REGION_CODE
1244 ,X_CUSTOMIZATION_LEVEL_ID
1245 ,X_CLASSIFICATION_1
1246 ,X_CLASSIFICATION_2
1247 ,X_CLASSIFICATION_3) = FALSE) THEN
1248
1249 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1250 X_ROWID => l_rowid,
1251 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1252 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1253 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1254 X_REGION_CODE => X_REGION_CODE,
1255 X_PROPERTY_NAME => 'RESULT_NAME',
1256 X_PROPERTY_VARCHAR2_VALUE => X_NAME,
1257 X_PROPERTY_NUMBER_VALUE => NULL,
1258 X_CRITERIA_JOIN_CONDITION => NULL,
1259 X_CREATED_BY => X_CREATED_BY,
1260 X_CREATION_DATE => X_CREATION_DATE,
1261 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1262 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1263 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1264 );
1265
1266 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1267 X_ROWID => l_rowid,
1268 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1269 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1270 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1271 X_REGION_CODE => X_REGION_CODE,
1272 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1273 X_PROPERTY_VARCHAR2_VALUE => X_DESCRIPTION,
1274 X_PROPERTY_NUMBER_VALUE => NULL,
1275 X_CRITERIA_JOIN_CONDITION => NULL,
1276 X_CREATED_BY => X_CREATED_BY,
1277 X_CREATION_DATE => X_CREATION_DATE,
1278 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1279 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1280 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1281 );
1282
1283 -- first thing updating other result format default flag if "Y" in this one
1284
1285 IF( X_DEFAULT_RESULT_FLAG = 'Y') THEN
1286
1287
1288 Update_Result_Format_Default
1289 (
1290 X_CUSTOMIZATION_APPLICATION_ID,
1291 X_REGION_APPLICATION_ID,
1292 X_REGION_CODE,
1293 X_WEB_USER_ID,
1294 X_CUSTOMIZATION_LEVEL_ID,
1295 X_IMPORT_FLAG,
1296 X_CLASSIFICATION_1,
1297 X_CLASSIFICATION_2,
1298 X_CLASSIFICATION_3,
1299 X_DATA_LEVEL
1300 );
1301
1302 END IF;
1303
1304 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1305 X_ROWID => l_rowid,
1306 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1307 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1308 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1309 X_REGION_CODE => X_REGION_CODE,
1310 X_PROPERTY_NAME => 'DEFAULT_RESULT_FLAG',
1314 X_CREATED_BY => X_CREATED_BY,
1311 X_PROPERTY_VARCHAR2_VALUE => X_DEFAULT_RESULT_FLAG,
1312 X_PROPERTY_NUMBER_VALUE => NULL,
1313 X_CRITERIA_JOIN_CONDITION => NULL,
1315 X_CREATION_DATE => X_CREATION_DATE,
1316 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1317 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1318 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1319 );
1320
1321 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1322 X_ROWID => l_rowid,
1323 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1324 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1325 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1326 X_REGION_CODE => X_REGION_CODE,
1327 X_PROPERTY_NAME => 'NUM_ROWS_DISPLAY',
1328 X_PROPERTY_VARCHAR2_VALUE => NULL,
1329 X_PROPERTY_NUMBER_VALUE => X_NUM_ROWS_DISPLAYED,
1330 X_CRITERIA_JOIN_CONDITION => NULL,
1331 X_CREATED_BY => X_CREATED_BY,
1332 X_CREATION_DATE => X_CREATION_DATE,
1333 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1334 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1335 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1336 );
1337
1338 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1339 X_ROWID => l_rowid,
1340 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1341 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1342 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1343 X_REGION_CODE => X_REGION_CODE,
1344 X_PROPERTY_NAME => 'SITE_ID',
1345 X_PROPERTY_VARCHAR2_VALUE => NULL,
1346 X_PROPERTY_NUMBER_VALUE => X_SITE_ID,
1347 X_CRITERIA_JOIN_CONDITION => NULL,
1348 X_CREATED_BY => X_CREATED_BY,
1349 X_CREATION_DATE => X_CREATION_DATE,
1350 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1351 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1352 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1353 );
1354
1355 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1356 X_ROWID => l_rowid,
1357 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1358 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1359 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1360 X_REGION_CODE => X_REGION_CODE,
1361 X_PROPERTY_NAME => 'WEB_USER_ID',
1362 X_PROPERTY_VARCHAR2_VALUE => NULL,
1363 X_PROPERTY_NUMBER_VALUE => X_WEB_USER_ID,
1364 X_CRITERIA_JOIN_CONDITION => NULL,
1365 X_CREATED_BY => X_CREATED_BY,
1366 X_CREATION_DATE => X_CREATION_DATE,
1367 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1368 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1369 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1370 );
1371
1372 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1373 X_ROWID => l_rowid,
1374 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1375 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1376 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1377 X_REGION_CODE => X_REGION_CODE,
1378 X_PROPERTY_NAME => 'CUSTOMIZATION_LEVEL_ID',
1379 X_PROPERTY_VARCHAR2_VALUE => NULL,
1380 X_PROPERTY_NUMBER_VALUE => X_CUSTOMIZATION_LEVEL_ID,
1381 X_CRITERIA_JOIN_CONDITION => NULL,
1382 X_CREATED_BY => X_CREATED_BY,
1383 X_CREATION_DATE => X_CREATION_DATE,
1384 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1385 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1386 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1387 );
1388
1389 IF (X_IMPORT_FLAG IS NOT NULL) THEN
1390
1391 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1392 X_ROWID => l_rowid,
1393 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1394 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1395 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1396 X_REGION_CODE => X_REGION_CODE,
1397 X_PROPERTY_NAME => 'IMPORT_FLAG',
1398 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1399 X_PROPERTY_NUMBER_VALUE => NULL,
1400 X_CRITERIA_JOIN_CONDITION => NULL,
1401 X_CREATED_BY => X_CREATED_BY,
1402 X_CREATION_DATE => X_CREATION_DATE,
1403 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1404 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1405 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1406 );
1407
1408 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1409 X_ROWID => l_rowid,
1410 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1411 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1412 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1413 X_REGION_CODE => X_REGION_CODE,
1414 X_PROPERTY_NAME => 'DATA_LEVEL',
1415 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1416 X_PROPERTY_NUMBER_VALUE => NULL,
1417 X_CRITERIA_JOIN_CONDITION => NULL,
1418 X_CREATED_BY => X_CREATED_BY,
1419 X_CREATION_DATE => X_CREATION_DATE,
1420 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1421 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1422 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1423 );
1424
1425
1426 END IF;
1427
1428 INSERT INTO EGO_CUSTOMIZATION_EXT
1432 REGION_APPLICATION_ID,
1429 (
1430 CUSTOMIZATION_APPLICATION_ID,
1431 CUSTOMIZATION_CODE,
1433 REGION_CODE,
1434 CLASSIFICATION1,
1435 CLASSIFICATION2,
1436 CLASSIFICATION3,
1437 CREATED_BY,
1438 CREATION_DATE,
1439 LAST_UPDATED_BY,
1440 LAST_UPDATE_DATE,
1441 LAST_UPDATE_LOGIN
1442 )
1443 VALUES
1444 (
1445 X_CUSTOMIZATION_APPLICATION_ID,
1446 X_CUSTOMIZATION_CODE,
1447 X_REGION_APPLICATION_ID,
1448 X_REGION_CODE,
1449 X_CLASSIFICATION_1,
1450 X_CLASSIFICATION_2,
1451 X_CLASSIFICATION_3,
1452 g_current_user_id,
1453 l_Sysdate,
1454 g_current_user_id,
1455 l_Sysdate,
1456 g_current_user_id
1457 );
1458
1459 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1460
1461 ELSE
1462
1463
1464 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_RESULTS_FORMAT');
1465 FND_MSG_PUB.Add;
1466
1467 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1468
1469
1470 END IF;
1471
1472 END create_result_format;
1473
1474 ---------------------------------------------------------
1475
1476 PROCEDURE update_result_format
1477 (
1478 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1479 X_CUSTOMIZATION_CODE IN VARCHAR2,
1480 X_REGION_APPLICATION_ID IN NUMBER,
1481 X_REGION_CODE IN VARCHAR2,
1482 X_NAME IN VARCHAR2,
1483 X_DESCRIPTION IN VARCHAR2,
1484 X_NUM_ROWS_DISPLAYED IN NUMBER,
1485 X_DEFAULT_RESULT_FLAG IN VARCHAR2,
1486 X_SITE_ID IN NUMBER,
1487 X_WEB_USER_ID IN NUMBER,
1488 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
1489 X_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
1490 X_DATA_LEVEL IN VARCHAR2 DEFAULT NULL,
1491 X_LAST_UPDATED_BY IN NUMBER,
1492 X_LAST_UPDATE_DATE IN DATE,
1493 X_LAST_UPDATE_LOGIN IN NUMBER,
1494 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
1495 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
1496 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
1497 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1498 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1499 X_ERRORCODE OUT NOCOPY NUMBER
1500 )
1501 IS
1502 l_Sysdate DATE := Sysdate;
1503 l_count NUMBER;
1504 l_rowid VARCHAR2(255);
1505 l_data_level VARCHAR2(4000);
1506
1507 CURSOR get_data_level IS
1508 select data_level_query.property_varchar2_value data_level
1509 from
1510 AK_CUSTOM_REGIONS_TL name_query ,
1511 AK_CUSTOM_REGIONS data_level_query
1512 where name_query.property_name = 'RESULT_NAME'
1513 and name_query.language = USERENV('LANG')
1514 and data_level_query.property_name (+) = 'DATA_LEVEL'
1515 and name_query.customization_application_id = data_level_query.customization_application_id(+)
1516 and name_query.customization_code = data_level_query.customization_code (+)
1517 and name_query.region_application_id = data_level_query.region_application_id(+)
1518 and name_query.region_code = data_level_query.region_code(+)
1519 and name_query.customization_code = x_customization_code
1520 and name_query.customization_application_id = x_customization_application_id
1521 and name_query.region_application_id = x_region_application_id
1522 and name_query.region_code = x_region_code;
1523
1524 BEGIN
1525
1526 IF FND_API.To_Boolean(x_init_msg_list) THEN
1527 FND_MSG_PUB.Initialize;
1528 END IF;
1529
1530 IF (Results_Format_Name_Exists(X_NAME
1531 ,X_WEB_USER_ID
1532 ,X_CUSTOMIZATION_CODE
1533 ,X_CUSTOMIZATION_APPLICATION_ID
1534 ,X_REGION_APPLICATION_ID
1535 ,X_REGION_CODE
1536 ,X_CUSTOMIZATION_LEVEL_ID
1537 ,X_CLASSIFICATION_1
1538 ,X_CLASSIFICATION_2
1539 ,X_CLASSIFICATION_3) = FALSE) THEN
1540
1541 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1542 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1543 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1544 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1545 X_REGION_CODE => X_REGION_CODE,
1546 X_PROPERTY_NAME => 'RESULT_NAME',
1547 X_PROPERTY_VARCHAR2_VALUE => X_NAME,
1548 X_PROPERTY_NUMBER_VALUE => NULL,
1549 X_CRITERIA_JOIN_CONDITION => NULL,
1550 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1551 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1552 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1553 );
1554
1555 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1556 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1557 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1558 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1559 X_REGION_CODE => X_REGION_CODE,
1563 X_CRITERIA_JOIN_CONDITION => NULL,
1560 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1561 X_PROPERTY_VARCHAR2_VALUE => X_DESCRIPTION,
1562 X_PROPERTY_NUMBER_VALUE => NULL,
1564 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1565 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1566 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1567 );
1568
1569 -- first thing updating other result format default flag if "Y" in this one
1570
1571 IF( X_DEFAULT_RESULT_FLAG = 'Y') THEN
1572
1573 FOR data_rec IN get_data_level LOOP
1574 l_data_level := data_rec.data_level;
1575 exit;
1576 END LOOP;
1577
1578 Update_Result_Format_Default
1579 (
1580 X_CUSTOMIZATION_APPLICATION_ID,
1581 X_REGION_APPLICATION_ID,
1582 X_REGION_CODE,
1583 X_WEB_USER_ID,
1584 X_CUSTOMIZATION_LEVEL_ID,
1585 X_IMPORT_FLAG,
1586 X_CLASSIFICATION_1,
1587 X_CLASSIFICATION_2,
1588 X_CLASSIFICATION_3,
1589 l_data_level
1590 );
1591
1592 END IF;
1593
1594
1595 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1596 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1597 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1598 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1599 X_REGION_CODE => X_REGION_CODE,
1600 X_PROPERTY_NAME => 'DEFAULT_RESULT_FLAG',
1601 X_PROPERTY_VARCHAR2_VALUE => X_DEFAULT_RESULT_FLAG,
1602 X_PROPERTY_NUMBER_VALUE => NULL,
1603 X_CRITERIA_JOIN_CONDITION => NULL,
1604 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1605 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1606 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1607 );
1608
1609 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1610 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1611 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1612 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1613 X_REGION_CODE => X_REGION_CODE,
1614 X_PROPERTY_NAME => 'NUM_ROWS_DISPLAY',
1615 X_PROPERTY_VARCHAR2_VALUE => NULL,
1616 X_PROPERTY_NUMBER_VALUE => X_NUM_ROWS_DISPLAYED,
1617 X_CRITERIA_JOIN_CONDITION => NULL,
1618 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1619 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1620 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1621 );
1622
1623 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1624 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1625 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1626 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1627 X_REGION_CODE => X_REGION_CODE,
1628 X_PROPERTY_NAME => 'SITE_ID',
1629 X_PROPERTY_VARCHAR2_VALUE => NULL,
1630 X_PROPERTY_NUMBER_VALUE => X_SITE_ID,
1631 X_CRITERIA_JOIN_CONDITION => NULL,
1632 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1633 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1634 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1635 );
1636
1637 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1638 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1639 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1640 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1641 X_REGION_CODE => X_REGION_CODE,
1642 X_PROPERTY_NAME => 'WEB_USER_ID',
1643 X_PROPERTY_VARCHAR2_VALUE => NULL,
1644 X_PROPERTY_NUMBER_VALUE => X_WEB_USER_ID,
1645 X_CRITERIA_JOIN_CONDITION => NULL,
1646 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1647 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1648 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1649 );
1650
1651 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1652 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1653 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1654 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1655 X_REGION_CODE => X_REGION_CODE,
1656 X_PROPERTY_NAME => 'CUSTOMIZATION_LEVEL_ID',
1657 X_PROPERTY_VARCHAR2_VALUE => NULL,
1658 X_PROPERTY_NUMBER_VALUE => X_CUSTOMIZATION_LEVEL_ID,
1659 X_CRITERIA_JOIN_CONDITION => NULL,
1660 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1661 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1662 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1663 );
1664
1665 SELECT COUNT(*) INTO l_count
1666 FROM AK_CUSTOM_REGIONS
1667 WHERE
1668 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1669 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1670 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1671 AND REGION_CODE = X_REGION_CODE
1672 AND PROPERTY_NAME = 'IMPORT_FLAG';
1673
1677 DELETE FROM AK_CUSTOM_REGIONS
1674 IF (l_count > 0) THEN
1675
1676 IF (X_IMPORT_FLAG IS NULL) THEN
1678 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1679 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1680 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1681 AND REGION_CODE = X_REGION_CODE
1682 AND PROPERTY_NAME = 'IMPORT_FLAG';
1683 ELSE
1684 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1685 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1686 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1687 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1688 X_REGION_CODE => X_REGION_CODE,
1689 X_PROPERTY_NAME => 'IMPORT_FLAG',
1690 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1691 X_PROPERTY_NUMBER_VALUE => NULL,
1692 X_CRITERIA_JOIN_CONDITION => NULL,
1693 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1694 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1695 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1696 );
1697 END IF;
1698
1699 ELSE
1700
1701 IF (X_IMPORT_FLAG IS NOT NULL) THEN
1702
1703 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1704 X_ROWID => l_rowid,
1705 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1706 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1707 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1708 X_REGION_CODE => X_REGION_CODE,
1709 X_PROPERTY_NAME => 'IMPORT_FLAG',
1710 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1711 X_PROPERTY_NUMBER_VALUE => NULL,
1712 X_CRITERIA_JOIN_CONDITION => NULL,
1713 X_CREATED_BY => X_LAST_UPDATED_BY,
1714 X_CREATION_DATE => X_LAST_UPDATE_DATE,
1715 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1716 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1717 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1718 );
1719
1720 END IF;
1721
1722 END IF;
1723
1724 --Bug 6011948
1725 -- Update data level also if a value is supplied for it..
1726
1727 IF (X_DATA_LEVEL IS NOT NULL) THEN
1728
1729 SELECT COUNT(*) INTO l_count
1730 FROM AK_CUSTOM_REGIONS
1731 WHERE
1732 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1733 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1734 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1735 AND REGION_CODE = X_REGION_CODE
1736 AND PROPERTY_NAME = 'DATA_LEVEL';
1737
1738 IF (l_count > 0) THEN
1739 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1740 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1741 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1742 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1743 X_REGION_CODE => X_REGION_CODE,
1744 X_PROPERTY_NAME => 'DATA_LEVEL',
1745 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1746 X_PROPERTY_NUMBER_VALUE => NULL,
1747 X_CRITERIA_JOIN_CONDITION => NULL,
1748 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1749 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1750 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1751 );
1752
1753 ELSE
1754
1755 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1756 X_ROWID => l_rowid,
1757 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1758 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1759 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1760 X_REGION_CODE => X_REGION_CODE,
1761 X_PROPERTY_NAME => 'DATA_LEVEL',
1762 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1763 X_PROPERTY_NUMBER_VALUE => NULL,
1764 X_CRITERIA_JOIN_CONDITION => NULL,
1765 X_CREATED_BY => X_LAST_UPDATED_BY,
1766 X_CREATION_DATE => X_LAST_UPDATE_DATE,
1767 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1768 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1769 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1770 );
1771
1772 END IF;
1773 END IF;
1774
1775
1776
1777
1778
1779
1780
1781 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1782
1783 ELSE
1784
1785 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_RESULTS_FORMAT');
1786 FND_MSG_PUB.Add;
1787
1788 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1789
1790 END IF;
1791
1792 END update_result_format;
1793
1794 -------------------------------------------------------------
1795
1796 PROCEDURE delete_result_format
1797 (
1798 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1799 X_CUSTOMIZATION_CODE IN VARCHAR2,
1800 X_REGION_APPLICATION_ID IN NUMBER,
1801 X_REGION_CODE IN VARCHAR2,
1802 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1803 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1804 X_ERRORCODE OUT NOCOPY NUMBER
1805 )
1806 IS
1807
1811 FND_MSG_PUB.Initialize;
1808 BEGIN
1809
1810 IF FND_API.To_Boolean(x_init_msg_list) THEN
1812 END IF;
1813
1814 Check_Result_Format_Deletion
1815 (
1816 X_CUSTOMIZATION_APPLICATION_ID,
1817 X_CUSTOMIZATION_CODE,
1818 X_REGION_APPLICATION_ID,
1819 X_REGION_CODE,
1820 FND_API.G_FALSE,
1821 X_RETURN_STATUS,
1822 X_ERRORCODE
1823 );
1824
1825 IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) THEN
1826
1827 DELETE FROM AK_CUSTOM_REGIONS
1828 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1829 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1830 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1831 AND REGION_CODE = X_REGION_CODE;
1832
1833 DELETE FROM AK_CUSTOM_REGIONS_TL
1834 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1835 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1836 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1837 AND REGION_CODE = X_REGION_CODE;
1838
1839 DELETE FROM EGO_CUSTOMIZATION_EXT
1840 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1841 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1842 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1843 AND REGION_CODE = X_REGION_CODE;
1844
1845 DELETE FROM AK_CUSTOM_REGION_ITEMS
1846 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1847 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1848 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1849 AND REGION_CODE = X_REGION_CODE;
1850
1851 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
1852 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1853 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1854 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1855 AND REGION_CODE = X_REGION_CODE;
1856
1857 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1858
1859 ELSE
1860
1861 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1862
1863 END IF;
1864
1865 END delete_result_format;
1866
1867
1868 PROCEDURE translate_result_format
1869 ( p_customization_application_id IN NUMBER
1870 ,p_customization_code IN VARCHAR2
1871 ,p_region_application_id IN NUMBER
1872 ,p_region_code IN VARCHAR2
1873 ,p_last_update_date IN VARCHAR2
1874 ,p_last_updated_by IN NUMBER
1875 ,p_name IN VARCHAR2
1876 ,p_description IN VARCHAR2
1877 ,x_return_status OUT NOCOPY VARCHAR2
1878 ,x_msg_data OUT NOCOPY VARCHAR2
1879 ) IS
1880
1881 l_last_update_login ak_customizations.last_update_login%TYPE;
1882 l_last_update_date ak_customizations.last_update_date%TYPE;
1883 l_last_updated_by ak_customizations.last_updated_by%TYPE;
1884
1885 CURSOR c_get_last_update_info (cp_property_name IN VARCHAR2) IS
1886 SELECT last_updated_by, last_update_date
1887 FROM ak_custom_regions_tl
1888 WHERE customization_application_id = p_customization_application_id
1889 AND customization_code = p_customization_code
1890 AND region_application_id = p_region_application_id
1891 AND region_code = p_region_code
1892 AND property_name = cp_property_name
1893 AND USERENV('LANG') IN (language, source_lang);
1894
1895 BEGIN
1896
1897 l_last_update_login := FND_GLOBAL.Login_Id;
1898
1899 OPEN c_get_last_update_info (cp_property_name => 'RESULT_NAME');
1900 FETCH c_get_last_update_info
1901 INTO l_last_updated_by, l_last_update_date;
1902 CLOSE c_get_last_update_info;
1903
1904 IF (fnd_load_util.upload_test(p_last_updated_by
1905 ,p_last_update_date
1906 ,l_last_updated_by
1907 ,l_last_update_date
1908 ,NULL)) THEN
1909
1910 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1911 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
1912 X_CUSTOMIZATION_CODE => p_customization_code,
1913 X_REGION_APPLICATION_ID => p_region_application_id,
1914 X_REGION_CODE => p_region_code,
1915 X_PROPERTY_NAME => 'RESULT_NAME',
1916 X_PROPERTY_VARCHAR2_VALUE => p_name,
1917 X_PROPERTY_NUMBER_VALUE => NULL,
1918 X_CRITERIA_JOIN_CONDITION => NULL,
1919 X_LAST_UPDATED_BY => p_last_updated_by,
1920 X_LAST_UPDATE_DATE => SYSDATE,
1921 X_LAST_UPDATE_LOGIN => l_last_update_login
1922 );
1923 END IF;
1924
1925 OPEN c_get_last_update_info (cp_property_name => 'RESULT_DESCRIPTION');
1926 FETCH c_get_last_update_info
1927 INTO l_last_updated_by, l_last_update_date;
1928 CLOSE c_get_last_update_info;
1929
1930 IF (fnd_load_util.upload_test(p_last_updated_by
1931 ,p_last_update_date
1932 ,l_last_updated_by
1933 ,l_last_update_date
1934 ,NULL)) THEN
1935 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1936 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
1937 X_CUSTOMIZATION_CODE => p_customization_code,
1938 X_REGION_APPLICATION_ID => p_region_application_id,
1942 X_PROPERTY_NUMBER_VALUE => NULL,
1939 X_REGION_CODE => p_region_code,
1940 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1941 X_PROPERTY_VARCHAR2_VALUE => p_description,
1943 X_CRITERIA_JOIN_CONDITION => NULL,
1944 X_LAST_UPDATED_BY => p_last_updated_by,
1945 X_LAST_UPDATE_DATE => SYSDATE,
1946 X_LAST_UPDATE_LOGIN => l_last_update_login
1947 );
1948 END IF;
1949 x_return_status := 'S';
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 x_return_status := 'E';
1953 x_msg_data := SQLERRM;
1954 END translate_result_format;
1955
1956 ---------------------------------------------------------
1957
1958
1959 PROCEDURE create_result_column
1960 (
1961 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1962 X_CUSTOMIZATION_CODE IN VARCHAR2,
1963 X_REGION_APPLICATION_ID IN NUMBER,
1964 X_REGION_CODE IN VARCHAR2,
1965 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
1966 X_ATTRIBUTE_CODE IN VARCHAR2,
1967 X_DISPLAY_SEQUENCE IN NUMBER,
1968 X_ORDER_SEQUENCE IN NUMBER,
1969 X_ORDER_DIRECTION IN VARCHAR2,
1970 X_COLUMN_NAME IN VARCHAR2 := NULL,
1971 X_SHOW_TOTAL IN VARCHAR2 := NULL,
1972 X_CREATED_BY IN NUMBER,
1973 X_CREATION_DATE IN DATE,
1974 X_LAST_UPDATED_BY IN NUMBER,
1975 X_LAST_UPDATE_DATE IN DATE,
1976 X_LAST_UPDATE_LOGIN IN NUMBER,
1977 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1978 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1979 X_ERRORCODE OUT NOCOPY NUMBER
1980 )
1981 IS
1982
1983 l_Sysdate DATE := Sysdate;
1984 l_rowid VARCHAR2(255);
1985
1986 BEGIN
1987
1988 IF FND_API.To_Boolean(x_init_msg_list) THEN
1989 FND_MSG_PUB.Initialize;
1990 END IF;
1991
1992 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
1993 X_ROWID => l_rowid,
1994 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1995 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1996 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1997 X_REGION_CODE => X_REGION_CODE,
1998 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
1999 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2000 X_PROPERTY_NAME => 'DISPLAY_SEQUENCE',
2001 X_PROPERTY_VARCHAR2_VALUE => NULL,
2002 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2003 X_PROPERTY_DATE_VALUE => NULL,
2004 X_CREATED_BY => X_CREATED_BY,
2005 X_CREATION_DATE => X_CREATION_DATE,
2006 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2007 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2008 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2009 );
2010
2011 IF (X_ORDER_SEQUENCE IS NOT NULL) THEN
2012
2013 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2014 X_ROWID => l_rowid,
2015 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2016 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2017 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2018 X_REGION_CODE => X_REGION_CODE,
2019 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2020 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2021 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2022 X_PROPERTY_VARCHAR2_VALUE => NULL,
2023 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2024 X_PROPERTY_DATE_VALUE => NULL,
2025 X_CREATED_BY => X_CREATED_BY,
2026 X_CREATION_DATE => X_CREATION_DATE,
2027 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2028 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2029 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2030 );
2031
2032 END IF;
2033
2034 IF (X_ORDER_DIRECTION IS NOT NULL) THEN
2035
2036 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2037 X_ROWID => l_rowid,
2038 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2039 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2040 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2041 X_REGION_CODE => X_REGION_CODE,
2042 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2043 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2044 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2045 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2046 X_PROPERTY_NUMBER_VALUE => NULL,
2047 X_PROPERTY_DATE_VALUE => NULL,
2048 X_CREATED_BY => X_CREATED_BY,
2049 X_CREATION_DATE => X_CREATION_DATE,
2050 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2051 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2052 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2053 );
2054 END IF;
2055
2056 IF (X_COLUMN_NAME IS NOT NULL) THEN
2057
2058 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2062 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2059 X_ROWID => l_rowid,
2060 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2061 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2063 X_REGION_CODE => X_REGION_CODE,
2064 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2065 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2066 X_PROPERTY_NAME => 'COLUMN_NAME',
2067 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2068 X_PROPERTY_NUMBER_VALUE => NULL,
2069 X_PROPERTY_DATE_VALUE => NULL,
2070 X_CREATED_BY => X_CREATED_BY,
2071 X_CREATION_DATE => X_CREATION_DATE,
2072 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2073 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2074 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2075 );
2076 END IF;
2077
2078 IF (X_SHOW_TOTAL IS NOT NULL) THEN
2079
2080 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2081 X_ROWID => l_rowid,
2082 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2083 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2084 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2085 X_REGION_CODE => X_REGION_CODE,
2086 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2087 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2088 X_PROPERTY_NAME => 'SHOW_TOTAL',
2089 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2090 X_PROPERTY_NUMBER_VALUE => NULL,
2091 X_PROPERTY_DATE_VALUE => NULL,
2092 X_CREATED_BY => X_CREATED_BY,
2093 X_CREATION_DATE => X_CREATION_DATE,
2094 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2095 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2096 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2097 );
2098 END IF;
2099
2100 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2101 X_RETURN_STATUS := 'T';
2102
2103
2104 END create_result_column;
2105
2106
2107 ---------------------------------------------------------
2108
2109
2110 PROCEDURE update_result_column
2111 (
2112 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2113 X_CUSTOMIZATION_CODE IN VARCHAR2,
2114 X_REGION_APPLICATION_ID IN NUMBER,
2115 X_REGION_CODE IN VARCHAR2,
2116 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2117 X_ATTRIBUTE_CODE IN VARCHAR2,
2118 X_DISPLAY_SEQUENCE IN NUMBER,
2119 X_ORDER_SEQUENCE IN NUMBER,
2120 X_ORDER_DIRECTION IN VARCHAR2,
2121 X_COLUMN_NAME IN VARCHAR2 := NULL,
2122 X_SHOW_TOTAL IN VARCHAR2 := NULL,
2123 X_LAST_UPDATED_BY IN NUMBER,
2124 X_LAST_UPDATE_DATE IN DATE,
2125 X_LAST_UPDATE_LOGIN IN NUMBER,
2126 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2127 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2128 X_ERRORCODE OUT NOCOPY NUMBER
2129 )
2130 IS
2131
2132 l_Sysdate DATE := Sysdate;
2133 l_rowid VARCHAR2(255);
2134 l_count NUMBER;
2135
2136 BEGIN
2137
2138 IF FND_API.To_Boolean(x_init_msg_list) THEN
2139 FND_MSG_PUB.Initialize;
2140 END IF;
2141
2142 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2143 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2144 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2145 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2146 X_REGION_CODE => X_REGION_CODE,
2147 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2148 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2149 X_PROPERTY_NAME => 'DISPLAY_SEQUENCE',
2150 X_PROPERTY_VARCHAR2_VALUE => NULL,
2151 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2152 X_PROPERTY_DATE_VALUE => NULL,
2153 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2154 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2155 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2156 );
2157
2158 SELECT
2159 COUNT(*) INTO l_count
2160 FROM
2161 AK_CUSTOM_REGION_ITEMS
2162 WHERE
2163 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2164 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2165 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2166 AND REGION_CODE = X_REGION_CODE
2167 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2168 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2169 AND PROPERTY_NAME = 'ORDER_SEQUENCE';
2170
2171 IF (X_ORDER_SEQUENCE IS NULL) THEN
2172
2173 IF (l_count > 0) THEN
2174 DELETE FROM AK_CUSTOM_REGION_ITEMS
2175 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2176 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2177 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2178 AND REGION_CODE = X_REGION_CODE
2179 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2180 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2184 ELSE
2181 AND PROPERTY_NAME = 'ORDER_SEQUENCE';
2182 END IF;
2183
2185
2186 IF (l_count > 0) THEN
2187
2188 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2189 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2190 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2191 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2192 X_REGION_CODE => X_REGION_CODE,
2193 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2194 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2195 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2196 X_PROPERTY_VARCHAR2_VALUE => NULL,
2197 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2198 X_PROPERTY_DATE_VALUE => NULL,
2199 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2200 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2201 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2202 );
2203
2204 ELSE
2205
2206 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2207 X_ROWID => l_rowid,
2208 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2209 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2210 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2211 X_REGION_CODE => X_REGION_CODE,
2212 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2213 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2214 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2215 X_PROPERTY_VARCHAR2_VALUE => NULL,
2216 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2217 X_PROPERTY_DATE_VALUE => NULL,
2218 X_CREATED_BY => X_LAST_UPDATED_BY,
2219 X_CREATION_DATE => SYSDATE,
2220 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2221 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2222 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2223 );
2224
2225 END IF;
2226
2227 END IF;
2228
2229
2230 SELECT
2231 COUNT(*) INTO l_count
2232 FROM
2233 AK_CUSTOM_REGION_ITEMS
2234 WHERE
2235 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2236 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2237 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2238 AND REGION_CODE = X_REGION_CODE
2239 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2240 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2241 AND PROPERTY_NAME = 'ORDER_DIRECTION';
2242
2243 IF (X_ORDER_DIRECTION IS NULL) THEN
2244
2245 IF (l_count > 0) THEN
2246 DELETE FROM AK_CUSTOM_REGION_ITEMS
2247 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2248 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2249 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2250 AND REGION_CODE = X_REGION_CODE
2251 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2252 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2253 AND PROPERTY_NAME = 'ORDER_DIRECTION';
2254 END IF;
2255
2256 ELSE
2257
2258 IF (l_count > 0) THEN
2259
2260 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2261 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2262 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2263 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2264 X_REGION_CODE => X_REGION_CODE,
2265 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2266 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2267 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2268 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2269 X_PROPERTY_NUMBER_VALUE => NULL,
2270 X_PROPERTY_DATE_VALUE => NULL,
2271 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2272 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2273 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2274 );
2275
2276 ELSE
2277
2278 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2279 X_ROWID => l_rowid,
2280 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2281 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2282 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2283 X_REGION_CODE => X_REGION_CODE,
2284 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2285 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2286 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2287 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2288 X_PROPERTY_NUMBER_VALUE => NULL,
2289 X_PROPERTY_DATE_VALUE => NULL,
2290 X_CREATED_BY => X_LAST_UPDATED_BY,
2291 X_CREATION_DATE => SYSDATE,
2292 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2293 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2294 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2295 );
2296
2297 END IF;
2298
2299 END IF;
2300
2301 SELECT
2302 COUNT(*) INTO l_count
2303 FROM
2307 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2304 AK_CUSTOM_REGION_ITEMS
2305 WHERE
2306 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2308 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2309 AND REGION_CODE = X_REGION_CODE
2310 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2311 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2312 AND PROPERTY_NAME = 'COLUMN_NAME';
2313
2314 IF (X_COLUMN_NAME IS NULL) THEN
2315
2316 IF (l_count > 0) THEN
2317 DELETE FROM AK_CUSTOM_REGION_ITEMS
2318 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2319 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2320 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2321 AND REGION_CODE = X_REGION_CODE
2322 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2323 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2324 AND PROPERTY_NAME = 'COLUMN_NAME';
2325 END IF;
2326
2327 ELSE
2328
2329 IF (l_count > 0) THEN
2330
2331 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2332 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2333 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2334 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2335 X_REGION_CODE => X_REGION_CODE,
2336 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2337 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2338 X_PROPERTY_NAME => 'COLUMN_NAME',
2339 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2340 X_PROPERTY_NUMBER_VALUE => NULL,
2341 X_PROPERTY_DATE_VALUE => NULL,
2342 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2343 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2344 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2345 );
2346
2347 ELSE
2348
2349 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2350 X_ROWID => l_rowid,
2351 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2352 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2353 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2354 X_REGION_CODE => X_REGION_CODE,
2355 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2356 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2357 X_PROPERTY_NAME => 'COLUMN_NAME',
2358 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2359 X_PROPERTY_NUMBER_VALUE => NULL,
2360 X_PROPERTY_DATE_VALUE => NULL,
2361 X_CREATED_BY => X_LAST_UPDATED_BY,
2362 X_CREATION_DATE => SYSDATE,
2363 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2364 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2365 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2366 );
2367
2368 END IF;
2369
2370 END IF;
2371
2372 SELECT
2373 COUNT(*) INTO l_count
2374 FROM
2375 AK_CUSTOM_REGION_ITEMS
2376 WHERE
2377 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2378 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2379 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2380 AND REGION_CODE = X_REGION_CODE
2381 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2382 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2383 AND PROPERTY_NAME = 'SHOW_TOTAL';
2384
2385 IF (X_SHOW_TOTAL IS NULL) THEN
2386
2387 IF (l_count > 0) THEN
2388 DELETE FROM AK_CUSTOM_REGION_ITEMS
2389 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2390 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2391 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2392 AND REGION_CODE = X_REGION_CODE
2393 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2394 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2395 AND PROPERTY_NAME = 'SHOW_TOTAL';
2396 END IF;
2397
2398 ELSE
2399
2400 IF (l_count > 0) THEN
2401
2402 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2403 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2404 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2405 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2406 X_REGION_CODE => X_REGION_CODE,
2407 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2408 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2409 X_PROPERTY_NAME => 'SHOW_TOTAL',
2410 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2411 X_PROPERTY_NUMBER_VALUE => NULL,
2412 X_PROPERTY_DATE_VALUE => NULL,
2413 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2414 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2415 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2416 );
2417
2418 ELSE
2419
2420 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2421 X_ROWID => l_rowid,
2422 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2423 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2424 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2425 X_REGION_CODE => X_REGION_CODE,
2429 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2426 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2427 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2428 X_PROPERTY_NAME => 'SHOW_TOTAL',
2430 X_PROPERTY_NUMBER_VALUE => NULL,
2431 X_PROPERTY_DATE_VALUE => NULL,
2432 X_CREATED_BY => X_LAST_UPDATED_BY,
2433 X_CREATION_DATE => SYSDATE,
2434 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2435 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2436 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2437 );
2438
2439 END IF;
2440
2441 END IF;
2442
2443
2444 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2445 X_RETURN_STATUS := 'T';
2446
2447
2448 END update_result_column;
2449
2450 -------------------------------------------------------------
2451
2452 PROCEDURE delete_result_column
2453 (
2454 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2455 X_CUSTOMIZATION_CODE IN VARCHAR2,
2456 X_REGION_APPLICATION_ID IN NUMBER,
2457 X_REGION_CODE IN VARCHAR2,
2458 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2459 X_ATTRIBUTE_CODE IN VARCHAR2,
2460 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2461 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2462 X_ERRORCODE OUT NOCOPY NUMBER
2463 )
2464 IS
2465
2466 BEGIN
2467
2468 IF FND_API.To_Boolean(x_init_msg_list) THEN
2469 FND_MSG_PUB.Initialize;
2470 END IF;
2471
2472 DELETE FROM AK_CUSTOM_REGION_ITEMS
2473 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2474 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2475 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2476 AND REGION_CODE = X_REGION_CODE
2477 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2478 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
2479
2480 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
2481 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2482 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2483 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2484 AND REGION_CODE = X_REGION_CODE
2485 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2486 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
2487
2488 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2489 X_RETURN_STATUS := 'T';
2490
2491
2492 END delete_result_column;
2493 ---------------------------------------------------------
2494
2495 PROCEDURE insert_criterion
2496 (
2497 X_ROWID IN OUT NOCOPY VARCHAR2,
2498 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2499 X_CUSTOMIZATION_CODE IN VARCHAR2,
2500 X_REGION_APPLICATION_ID IN NUMBER,
2501 X_REGION_CODE IN VARCHAR2,
2502 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2503 X_ATTRIBUTE_CODE IN VARCHAR2,
2504 X_SEQUENCE_NUMBER IN NUMBER,
2505 X_OPERATION IN VARCHAR2,
2506 X_VALUE_VARCHAR2 IN VARCHAR2,
2507 X_SECOND_VALUE_VARCHAR2 IN VARCHAR2,
2508 X_VALUE_NUMBER IN NUMBER,
2509 X_SECOND_VALUE_NUMBER IN NUMBER,
2510 X_VALUE_DATE IN DATE,
2511 X_SECOND_VALUE_DATE IN DATE,
2512 X_CREATED_BY IN NUMBER,
2513 X_CREATION_DATE IN DATE,
2514 X_LAST_UPDATED_BY IN NUMBER,
2515 X_LAST_UPDATE_DATE IN DATE,
2516 X_LAST_UPDATE_LOGIN IN NUMBER,
2517 X_START_DATE_ACTIVE IN DATE,
2518 X_END_DATE_ACTIVE IN DATE,
2519 X_USE_KEYWORD_SEARCH IN VARCHAR2 := 'Y',
2520 X_MATCH_CONDITION IN VARCHAR2 := 'ALL',
2521 X_FUZZY IN VARCHAR2 := 'N',
2522 X_STEMMING IN VARCHAR2 := 'N',
2523 X_SYNONYMS IN VARCHAR2 := 'N',
2524 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2525 )
2526 IS
2527
2528 BEGIN
2529
2530 IF FND_API.To_Boolean(x_init_msg_list) THEN
2531 FND_MSG_PUB.Initialize;
2532 END IF;
2533
2534 AK_CRITERIA_PKG.insert_row
2535 (
2536 X_ROWID,
2537 X_CUSTOMIZATION_APPLICATION_ID,
2538 X_CUSTOMIZATION_CODE,
2539 X_REGION_APPLICATION_ID,
2540 X_REGION_CODE,
2541 X_ATTRIBUTE_APPLICATION_ID,
2542 X_ATTRIBUTE_CODE,
2543 X_SEQUENCE_NUMBER,
2544 X_OPERATION,
2545 X_VALUE_VARCHAR2,
2546 X_VALUE_NUMBER,
2547 X_VALUE_DATE,
2548 X_CREATED_BY,
2549 X_CREATION_DATE,
2550 X_LAST_UPDATED_BY,
2551 X_LAST_UPDATE_DATE,
2552 X_LAST_UPDATE_LOGIN,
2553 X_START_DATE_ACTIVE,
2554 X_END_DATE_ACTIVE
2555 );
2556
2557 -- Now we see if we need to insert another (for between operators)
2558 IF (X_SECOND_VALUE_NUMBER IS NOT NULL OR X_SECOND_VALUE_DATE IS NOT NULL OR X_SECOND_VALUE_VARCHAR2 IS NOT NULL) THEN
2559 AK_CRITERIA_PKG.insert_row
2560 (
2561 X_ROWID,
2562 X_CUSTOMIZATION_APPLICATION_ID,
2563 X_CUSTOMIZATION_CODE,
2567 X_ATTRIBUTE_CODE,
2564 X_REGION_APPLICATION_ID,
2565 X_REGION_CODE,
2566 X_ATTRIBUTE_APPLICATION_ID,
2568 -1*X_SEQUENCE_NUMBER,
2569 X_OPERATION,
2570 X_SECOND_VALUE_VARCHAR2,
2571 X_SECOND_VALUE_NUMBER,
2572 X_SECOND_VALUE_DATE,
2573 X_CREATED_BY,
2574 X_CREATION_DATE,
2575 X_LAST_UPDATED_BY,
2576 X_LAST_UPDATE_DATE,
2577 X_LAST_UPDATE_LOGIN,
2578 X_START_DATE_ACTIVE,
2579 X_END_DATE_ACTIVE
2580 );
2581 END IF;
2582
2583 -- Matching
2584 INSERT INTO EGO_CRITERIA_EXT
2585 (
2586 CUSTOMIZATION_APPLICATION_ID,
2587 CUSTOMIZATION_CODE,
2588 REGION_APPLICATION_ID,
2589 REGION_CODE,
2590 ATTRIBUTE_APPLICATION_ID,
2591 ATTRIBUTE_CODE,
2592 SEQUENCE_NUMBER,
2593 USE_KEYWORD_SEARCH,
2594 MATCH_CONDITION,
2595 FUZZY,
2596 STEMMING,
2597 SYNONYMS,
2598 CREATED_BY,
2599 CREATION_DATE,
2600 LAST_UPDATED_BY,
2601 LAST_UPDATED_DATE,
2602 LAST_UPDATE_LOGIN
2603 )
2604 VALUES
2605 (
2606 X_CUSTOMIZATION_APPLICATION_ID,
2607 X_CUSTOMIZATION_CODE,
2608 X_REGION_APPLICATION_ID,
2609 X_REGION_CODE,
2610 X_ATTRIBUTE_APPLICATION_ID,
2611 X_ATTRIBUTE_CODE,
2612 X_SEQUENCE_NUMBER,
2613 X_USE_KEYWORD_SEARCH,
2614 X_MATCH_CONDITION,
2615 X_FUZZY,
2616 X_STEMMING,
2617 X_SYNONYMS,
2618 X_CREATED_BY,
2619 X_CREATION_DATE,
2620 X_LAST_UPDATED_BY,
2621 X_LAST_UPDATE_DATE,
2622 X_LAST_UPDATE_LOGIN
2623 );
2624 -- Matching
2625
2626 END insert_criterion;
2627
2628 PROCEDURE update_criterion
2629 (
2630 X_ROWID IN OUT NOCOPY VARCHAR2,
2631 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2632 X_CUSTOMIZATION_CODE IN VARCHAR2,
2633 X_REGION_APPLICATION_ID IN NUMBER,
2634 X_REGION_CODE IN VARCHAR2,
2635 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2636 X_ATTRIBUTE_CODE IN VARCHAR2,
2637 X_SEQUENCE_NUMBER IN NUMBER,
2638 X_OPERATION IN VARCHAR2,
2639 X_VALUE_VARCHAR2 IN VARCHAR2,
2640 X_SECOND_VALUE_VARCHAR2 IN VARCHAR2,
2641 X_VALUE_NUMBER IN NUMBER,
2642 X_SECOND_VALUE_NUMBER IN NUMBER,
2643 X_VALUE_DATE IN DATE,
2644 X_SECOND_VALUE_DATE IN DATE,
2645 X_LAST_UPDATED_BY IN NUMBER,
2646 X_LAST_UPDATE_DATE IN DATE,
2647 X_LAST_UPDATE_LOGIN IN NUMBER,
2648 X_START_DATE_ACTIVE IN DATE,
2649 X_END_DATE_ACTIVE IN DATE,
2650 X_USE_KEYWORD_SEARCH IN VARCHAR2 := 'Y',
2651 X_MATCH_CONDITION IN VARCHAR2 := 'ALL',
2652 X_FUZZY IN VARCHAR2 := 'N',
2653 X_STEMMING IN VARCHAR2 := 'N',
2654 X_SYNONYMS IN VARCHAR2 := 'N',
2655 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2656 )
2657 IS
2658
2659 l_count NUMBER;
2660
2661 BEGIN
2662
2663 IF FND_API.To_Boolean(x_init_msg_list) THEN
2664 FND_MSG_PUB.Initialize;
2665 END IF;
2666
2667 AK_CRITERIA_PKG.update_row
2668 (
2669 X_CUSTOMIZATION_APPLICATION_ID,
2670 X_CUSTOMIZATION_CODE,
2671 X_REGION_APPLICATION_ID,
2672 X_REGION_CODE,
2673 X_ATTRIBUTE_APPLICATION_ID,
2674 X_ATTRIBUTE_CODE,
2675 X_SEQUENCE_NUMBER,
2676 X_OPERATION,
2677 X_VALUE_VARCHAR2,
2678 X_VALUE_NUMBER,
2679 X_VALUE_DATE,
2680 X_LAST_UPDATED_BY,
2681 X_LAST_UPDATE_DATE,
2682 X_LAST_UPDATE_LOGIN,
2683 X_START_DATE_ACTIVE,
2684 X_END_DATE_ACTIVE
2685 );
2686
2687 --See if we need to update, delete, or insert a second row
2688
2689 SELECT
2690 COUNT(*) INTO l_count
2691 FROM
2692 AK_CRITERIA
2693 WHERE
2694 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2695 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2696 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2697 AND REGION_CODE = X_REGION_CODE
2698 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2699 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2700 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2701
2702 IF (X_SECOND_VALUE_NUMBER IS NOT NULL OR X_SECOND_VALUE_DATE IS NOT NULL OR X_SECOND_VALUE_VARCHAR2 IS NOT NULL) THEN
2703 --We either need to insert or update a row
2704 BEGIN
2705
2706 IF (l_count = 0) THEN
2707
2708 AK_CRITERIA_PKG.insert_row
2709 (
2710 X_ROWID,
2711 X_CUSTOMIZATION_APPLICATION_ID,
2712 X_CUSTOMIZATION_CODE,
2713 X_REGION_APPLICATION_ID,
2714 X_REGION_CODE,
2715 X_ATTRIBUTE_APPLICATION_ID,
2716 X_ATTRIBUTE_CODE,
2717 -1*X_SEQUENCE_NUMBER,
2718 X_OPERATION,
2719 X_SECOND_VALUE_VARCHAR2,
2720 X_SECOND_VALUE_NUMBER,
2724 X_LAST_UPDATED_BY,
2721 X_SECOND_VALUE_DATE,
2722 X_LAST_UPDATED_BY,
2723 X_LAST_UPDATE_DATE,
2725 X_LAST_UPDATE_DATE,
2726 X_LAST_UPDATE_LOGIN,
2727 X_START_DATE_ACTIVE,
2728 X_END_DATE_ACTIVE
2729 );
2730
2731 ELSE
2732
2733 UPDATE
2734 AK_CRITERIA
2735 SET
2736 VALUE_VARCHAR2 = X_SECOND_VALUE_VARCHAR2,
2737 VALUE_NUMBER = X_SECOND_VALUE_NUMBER,
2738 VALUE_DATE = X_SECOND_VALUE_DATE
2739 WHERE
2740 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2741 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2742 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2743 AND REGION_CODE = X_REGION_CODE
2744 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2745 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2746 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2747
2748 END IF;
2749
2750 EXCEPTION
2751 WHEN OTHERS THEN NULL;
2752 END;
2753
2754 ELSE
2755
2756 --We may need to delete a row
2757 BEGIN
2758
2759 IF (l_count > 0) THEN
2760
2761 DELETE FROM
2762 AK_CRITERIA
2763 WHERE
2764 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2765 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2766 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2767 AND REGION_CODE = X_REGION_CODE
2768 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2769 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2770 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2771
2772 END IF;
2773
2774 EXCEPTION
2775 WHEN OTHERS THEN NULL;
2776 END;
2777
2778 END IF;
2779 -- Matching
2780 UPDATE
2781 EGO_CRITERIA_EXT
2782 SET
2783 USE_KEYWORD_SEARCH = X_USE_KEYWORD_SEARCH,
2784 MATCH_CONDITION = X_MATCH_CONDITION,
2785 FUZZY = X_FUZZY,
2786 STEMMING = X_STEMMING,
2787 SYNONYMS = X_SYNONYMS
2788 WHERE
2789 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID AND
2790 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE AND
2791 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID AND
2792 REGION_CODE = X_REGION_CODE AND
2793 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID AND
2794 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE AND
2795 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
2796 -- Matching
2797 IF(SQL%ROWCOUNT = 0)
2798 THEN
2799 INSERT INTO EGO_CRITERIA_EXT
2800 (
2801 CUSTOMIZATION_APPLICATION_ID,
2802 CUSTOMIZATION_CODE,
2803 REGION_APPLICATION_ID,
2804 REGION_CODE,
2805 ATTRIBUTE_APPLICATION_ID,
2806 ATTRIBUTE_CODE,
2807 SEQUENCE_NUMBER,
2808 USE_KEYWORD_SEARCH,
2809 MATCH_CONDITION,
2810 FUZZY,
2811 STEMMING,
2812 SYNONYMS,
2813 CREATED_BY,
2814 CREATION_DATE,
2815 LAST_UPDATED_BY,
2816 LAST_UPDATED_DATE,
2817 LAST_UPDATE_LOGIN
2818 )
2819 VALUES
2820 (
2821 X_CUSTOMIZATION_APPLICATION_ID,
2822 X_CUSTOMIZATION_CODE,
2823 X_REGION_APPLICATION_ID,
2824 X_REGION_CODE,
2825 X_ATTRIBUTE_APPLICATION_ID,
2826 X_ATTRIBUTE_CODE,
2827 X_SEQUENCE_NUMBER,
2828 X_USE_KEYWORD_SEARCH,
2829 X_MATCH_CONDITION,
2830 X_FUZZY,
2831 X_STEMMING,
2832 X_SYNONYMS,
2833 X_LAST_UPDATED_BY, -- X_CREATED_BY,
2834 X_LAST_UPDATE_DATE, -- X_CREATION_DATE,
2835 X_LAST_UPDATED_BY,
2836 X_LAST_UPDATE_DATE,
2837 X_LAST_UPDATE_LOGIN
2838 );
2839 END IF;
2840 END update_criterion;
2841
2842 PROCEDURE delete_criterion
2843 (
2844 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2845 X_CUSTOMIZATION_CODE IN VARCHAR2,
2846 X_REGION_APPLICATION_ID IN NUMBER,
2847 X_REGION_CODE IN VARCHAR2,
2848 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2849 X_ATTRIBUTE_CODE IN VARCHAR2,
2850 X_SEQUENCE_NUMBER IN NUMBER,
2851 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2852 )
2853 IS
2854
2855 l_count NUMBER;
2856
2857 BEGIN
2858
2859 IF FND_API.To_Boolean(x_init_msg_list) THEN
2860 FND_MSG_PUB.Initialize;
2861 END IF;
2862
2863 AK_CRITERIA_PKG.delete_row
2864 (
2865 X_CUSTOMIZATION_APPLICATION_ID,
2866 X_CUSTOMIZATION_CODE,
2867 X_REGION_APPLICATION_ID,
2868 X_REGION_CODE,
2869 X_ATTRIBUTE_APPLICATION_ID,
2870 X_ATTRIBUTE_CODE,
2871 X_SEQUENCE_NUMBER
2872 );
2873
2874 SELECT
2875 COUNT(*) INTO l_count
2876 FROM
2877 AK_CRITERIA
2878 WHERE
2879 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2880 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2881 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2882 AND REGION_CODE = X_REGION_CODE
2886
2883 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2884 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2885 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2887 IF (l_count > 0) THEN
2888 AK_CRITERIA_PKG.delete_row
2889 (
2890 X_CUSTOMIZATION_APPLICATION_ID,
2891 X_CUSTOMIZATION_CODE,
2892 X_REGION_APPLICATION_ID,
2893 X_REGION_CODE,
2894 X_ATTRIBUTE_APPLICATION_ID,
2895 X_ATTRIBUTE_CODE,
2896 -1*X_SEQUENCE_NUMBER
2897 );
2898
2899 END IF;
2900 -- Matching
2901 DELETE
2902 EGO_CRITERIA_EXT
2903 WHERE
2904 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID AND
2905 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE AND
2906 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID AND
2907 REGION_CODE = X_REGION_CODE AND
2908 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID AND
2909 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE AND
2910 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
2911 -- Matching
2912 -- No issues even if the row is not there
2913 END delete_criterion;
2914
2915 PROCEDURE create_result_section
2916 (
2917 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2918 X_CUSTOMIZATION_CODE IN VARCHAR2,
2919 X_REGION_APPLICATION_ID IN NUMBER,
2920 X_REGION_CODE IN VARCHAR2,
2921 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2922 X_ATTRIBUTE_CODE IN VARCHAR2,
2923 X_DISPLAY_SEQUENCE IN NUMBER,
2924 X_CREATED_BY IN NUMBER,
2925 X_CREATION_DATE IN DATE,
2926 X_LAST_UPDATED_BY IN NUMBER,
2927 X_LAST_UPDATE_DATE IN DATE,
2928 X_LAST_UPDATE_LOGIN IN NUMBER,
2929 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2930 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2931 X_ERRORCODE OUT NOCOPY NUMBER
2932 )
2933 IS
2934
2935 l_Sysdate DATE := Sysdate;
2936 l_rowid VARCHAR2(255);
2937
2938 BEGIN
2939
2940 IF FND_API.To_Boolean(x_init_msg_list) THEN
2941 FND_MSG_PUB.Initialize;
2942 END IF;
2943
2944 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2945 X_ROWID => l_rowid,
2946 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2947 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2948 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2949 X_REGION_CODE => X_REGION_CODE,
2950 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2951 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2952 X_PROPERTY_NAME => 'SECTION_SEQUENCE',
2953 X_PROPERTY_VARCHAR2_VALUE => NULL,
2954 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2955 X_PROPERTY_DATE_VALUE => NULL,
2956 X_CREATED_BY => X_CREATED_BY,
2957 X_CREATION_DATE => X_CREATION_DATE,
2958 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2959 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2960 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2961 );
2962
2963 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2964 X_RETURN_STATUS := 'T';
2965
2966
2967 END create_result_section;
2968
2969
2970 ---------------------------------------------------------
2971
2972
2973 PROCEDURE update_result_section
2974 (
2975 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2976 X_CUSTOMIZATION_CODE IN VARCHAR2,
2977 X_REGION_APPLICATION_ID IN NUMBER,
2978 X_REGION_CODE IN VARCHAR2,
2979 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2980 X_ATTRIBUTE_CODE IN VARCHAR2,
2981 X_DISPLAY_SEQUENCE IN NUMBER,
2982 X_LAST_UPDATED_BY IN NUMBER,
2983 X_LAST_UPDATE_DATE IN DATE,
2984 X_LAST_UPDATE_LOGIN IN NUMBER,
2985 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2986 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2987 X_ERRORCODE OUT NOCOPY NUMBER
2988 )
2989 IS
2990
2991 l_Sysdate DATE := Sysdate;
2992 l_rowid VARCHAR2(255);
2993
2994 BEGIN
2995
2996 IF FND_API.To_Boolean(x_init_msg_list) THEN
2997 FND_MSG_PUB.Initialize;
2998 END IF;
2999
3000 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
3001 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
3002 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
3003 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
3004 X_REGION_CODE => X_REGION_CODE,
3005 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
3006 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
3007 X_PROPERTY_NAME => 'SECTION_SEQUENCE',
3008 X_PROPERTY_VARCHAR2_VALUE => NULL,
3009 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
3010 X_PROPERTY_DATE_VALUE => NULL,
3011 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
3012 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
3013 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
3014 );
3015
3016 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3017 X_RETURN_STATUS := 'T';
3018
3019
3020 END update_result_section;
3021
3022 -------------------------------------------------------------
3023
3024 PROCEDURE delete_result_section
3025 (
3026 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
3027 X_CUSTOMIZATION_CODE IN VARCHAR2,
3028 X_REGION_APPLICATION_ID IN NUMBER,
3029 X_REGION_CODE IN VARCHAR2,
3030 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
3031 X_ATTRIBUTE_CODE IN VARCHAR2,
3032 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
3033 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3034 X_ERRORCODE OUT NOCOPY NUMBER
3035 )
3036 IS
3037
3038 BEGIN
3039
3040 IF FND_API.To_Boolean(x_init_msg_list) THEN
3041 FND_MSG_PUB.Initialize;
3042 END IF;
3043
3044 DELETE FROM AK_CUSTOM_REGION_ITEMS
3045 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
3046 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
3047 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
3048 AND REGION_CODE = X_REGION_CODE
3049 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
3050 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
3051
3052 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
3053 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
3054 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
3055 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
3056 AND REGION_CODE = X_REGION_CODE
3057 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
3058 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
3059
3060 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3061 X_RETURN_STATUS := 'T';
3062
3063
3064 END delete_result_section;
3065 ---------------------------------------------------------
3066
3067 END EGO_SEARCH_FWK_PUB;