[Home] [Help]
PACKAGE BODY: APPS.EGO_SEARCH_FWK_PUB
Source
1 PACKAGE BODY EGO_SEARCH_FWK_PUB AS
2 /* $Header: EGOPSFWB.pls 120.8 2011/10/24 04:09:18 jawan 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
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
323 and (level_id_query.property_number_value = 60
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')
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
414 and name_query.region_code = level_id_query.region_code
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
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
536
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,
659 X_VERTICALIZATION_ID IN VARCHAR2,
660 X_LOCALIZATION_CODE IN VARCHAR2,
661 X_ORG_ID IN NUMBER,
662 X_SITE_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
768 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
769 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
770 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
771 AND REGION_CODE = X_REGION_CODE;
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 l_classification1 VARCHAR2(200);
803
804 BEGIN
805
806 IF FND_API.To_Boolean(x_init_msg_list) THEN
807 FND_MSG_PUB.Initialize;
808 END IF;
809
810 --begin bug 10384119
811 IF (X_REGION_CODE = 'EGO_ITEM_MATCH_RULE_REGION') THEN
812 SELECT
813 CLASSIFICATION1, NAME INTO l_classification1, l_name
814 FROM
815 EGO_CRITERIA_TEMPLATES_V
816 WHERE
817 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
818 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
819 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
820 AND REGION_CODE = X_REGION_CODE;
821
822 IF (l_classification1 = '-1') THEN
823 SELECT
824 COUNT(*) INTO l_count
825 FROM
826 EGO_IMPORT_OPTION_SETS
827 WHERE
828 DEF_MATCH_RULE_CUST_CODE = X_CUSTOMIZATION_CODE;
829
830 IF (l_count > 0) THEN
831 FND_MESSAGE.Set_Name(g_app_name, 'EGO_MR_RF_EXISTS'); --"The Match Rule \"&NAME\" cannot be deleted because it is referenced by Import Batches or Source Systems."
832 FND_MESSAGE.Set_Token('NAME', l_name);
833 FND_MSG_PUB.Add;
834 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
835 RETURN;
836 END IF;
837 END IF;
838 END IF;
839 --end bug 10384119
840
841 SELECT
842 COUNT(*)
843 INTO
844 l_count
845 FROM
846 EGO_CUSTOMIZATION_RF
847 WHERE
848 RF_CUSTOMIZATION_APPL_ID = X_CUSTOMIZATION_APPLICATION_ID
849 AND RF_CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
850 AND RF_REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
851 AND RF_REGION_CODE = X_REGION_CODE;
852
853 IF (l_count = 0) THEN
854
855 AK_CUSTOMIZATIONS_PKG.DELETE_ROW
856 (
857 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
858 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
859 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
860 X_REGION_CODE => X_REGION_CODE
861 );
862
863 DELETE
864 FROM
865 EGO_CUSTOMIZATION_EXT
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 DELETE
873 FROM
874 EGO_CUSTOMIZATION_RF
875 WHERE
876 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
877 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
878 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
879 AND REGION_CODE = X_REGION_CODE;
880
881 DELETE
882 FROM
883 AK_CRITERIA
884 WHERE
885 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
886 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
887 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
888 AND REGION_CODE = X_REGION_CODE;
889
890 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
891
892 ELSE
893
894 SELECT
895 NAME INTO l_name
896 FROM
897 EGO_CRITERIA_TEMPLATES_V
898 WHERE
899 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
900 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
901 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
902 AND REGION_CODE = X_REGION_CODE;
903
904 FND_MESSAGE.Set_Name(g_app_name, 'EGO_CT_RF_EXISTS');
905 FND_MESSAGE.Set_Token('NAME', l_name);
906 FND_MSG_PUB.Add;
907
908 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
909
910 END IF;
911
912 END Delete_Criteria_Template;
913
914 PROCEDURE translate_criteria_template
915 ( p_customization_application_id IN NUMBER
916 ,p_customization_code IN VARCHAR2
917 ,p_region_application_id IN NUMBER
918 ,p_region_code IN VARCHAR2
919 ,p_customization_level_id IN NUMBER
920 ,p_last_update_date IN VARCHAR2
921 ,p_last_updated_by IN NUMBER
922 ,p_name IN VARCHAR2
923 ,p_description IN VARCHAR2
924 ,x_return_status OUT NOCOPY VARCHAR2
925 ,x_msg_data OUT NOCOPY VARCHAR2
926 ) IS
927
928 CURSOR c_get_last_update_info IS
929 SELECT last_updated_by, last_update_date
930 FROM ak_customizations_tl
931 WHERE customization_application_id = p_customization_application_id
932 AND customization_code = p_customization_code
933 AND region_application_id = p_region_application_id
934 AND region_code = p_region_code
935 AND USERENV('LANG') IN (language, source_lang);
936
937 l_last_update_date ak_customizations.last_update_date%TYPE;
938 l_last_updated_by ak_customizations.last_updated_by%TYPE;
939 l_verticalization_id ak_customizations.verticalization_id%TYPE;
940 l_localization_code ak_customizations.localization_code%TYPE;
941 l_org_id ak_customizations.org_id%TYPE;
942 l_site_id ak_customizations.site_id%TYPE;
943 l_responsibility_id ak_customizations.responsibility_id%TYPE;
944 l_web_user_id ak_customizations.web_user_id%TYPE;
945 l_default_customization_flag ak_customizations.default_customization_flag%TYPE;
946 l_customization_level_id ak_customizations.customization_level_id%TYPE;
947 l_start_date_active ak_customizations.start_date_active%TYPE;
948 l_end_date_active ak_customizations.end_date_active%TYPE;
949
950 BEGIN
951
952 OPEN C_get_last_update_info;
953 FETCH c_get_last_update_info
954 INTO l_last_updated_by, l_last_update_date;
955 CLOSE c_get_last_update_info;
956
957 IF (fnd_load_util.upload_test(p_last_updated_by
958 ,p_last_update_date
959 ,l_last_updated_by
960 ,l_last_update_date
961 ,NULL)) THEN
962 SELECT
963 verticalization_id
964 ,localization_code
965 ,org_id
966 ,site_id
967 ,responsibility_id
968 ,web_user_id
969 ,default_customization_flag
970 ,customization_level_id
971 ,start_date_active
972 ,end_date_active
973 INTO
974 l_verticalization_id
975 ,l_localization_code
976 ,l_org_id
977 ,l_site_id
978 ,l_responsibility_id
979 ,l_web_user_id
980 ,l_default_customization_flag
981 ,l_customization_level_id
982 ,l_start_date_active
983 ,l_end_date_active
984 FROM ak_customizations
985 WHERE CUSTOMIZATION_APPLICATION_ID = p_customization_application_id
986 AND CUSTOMIZATION_CODE = p_customization_code
987 AND REGION_APPLICATION_ID = p_region_application_id
988 AND REGION_CODE = p_region_code
989 AND CUSTOMIZATION_LEVEL_ID = p_customization_level_id;
990
991 AK_CUSTOMIZATIONS_PKG.UPDATE_ROW
992 (
993 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
994 X_CUSTOMIZATION_CODE => p_customization_code,
995 X_REGION_APPLICATION_ID => p_region_application_id,
996 X_REGION_CODE => p_region_code,
997 X_NAME => p_name,
998 X_DESCRIPTION => p_description,
999 X_VERTICALIZATION_ID => l_verticalization_id,
1000 X_LOCALIZATION_CODE => l_localization_code,
1001 X_ORG_ID => l_org_id,
1002 X_SITE_ID => l_site_id,
1003 X_RESPONSIBILITY_ID => l_responsibility_id,
1004 X_WEB_USER_ID => l_web_user_id,
1005 X_DEFAULT_CUSTOMIZATION_FLAG => l_default_customization_flag,
1006 X_CUSTOMIZATION_LEVEL_ID => p_customization_level_id,
1007 X_LAST_UPDATED_BY => p_last_updated_by,
1008 X_LAST_UPDATE_DATE => SYSDATE,
1009 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1010 X_START_DATE_ACTIVE => l_start_date_active,
1011 X_END_DATE_ACTIVE => l_end_date_active
1012 );
1013 END IF;
1014 x_return_status := 'S';
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 x_return_status := 'E';
1018 x_msg_data := SQLERRM;
1019 END translate_criteria_template;
1020
1021
1022 ---------------------------------------------------------
1023 PROCEDURE create_criteria_template_rf
1024 (
1025 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1026 X_CUSTOMIZATION_CODE IN VARCHAR2,
1027 X_REGION_APPLICATION_ID IN NUMBER,
1028 X_REGION_CODE IN VARCHAR2,
1029 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER,
1030 X_RF_CUSTOMIZATION_CODE IN VARCHAR2,
1031 X_RF_REGION_APPLICATION_ID IN NUMBER,
1032 X_RF_REGION_CODE IN VARCHAR2,
1033 X_RF_TAG IN VARCHAR2,
1034 X_CREATED_BY IN NUMBER,
1035 X_CREATION_DATE IN DATE,
1036 X_LAST_UPDATED_BY IN NUMBER,
1037 X_LAST_UPDATE_DATE IN DATE,
1038 X_LAST_UPDATE_LOGIN IN NUMBER,
1039 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1040 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1041 X_ERRORCODE OUT NOCOPY NUMBER
1042 )
1043 IS
1044
1045 l_Sysdate DATE := Sysdate;
1046
1047 BEGIN
1048
1049 IF FND_API.To_Boolean(x_init_msg_list) THEN
1050 FND_MSG_PUB.Initialize;
1051 END IF;
1052
1053 IF (X_RF_CUSTOMIZATION_CODE IS NOT NULL) THEN
1054
1055 INSERT INTO EGO_CUSTOMIZATION_RF
1056 (
1057 CUSTOMIZATION_APPLICATION_ID,
1058 CUSTOMIZATION_CODE,
1059 REGION_APPLICATION_ID,
1060 REGION_CODE,
1061 RF_CUSTOMIZATION_APPL_ID,
1062 RF_CUSTOMIZATION_CODE,
1063 RF_REGION_APPLICATION_ID,
1064 RF_REGION_CODE,
1065 RF_TAG,
1066 CREATED_BY,
1067 CREATION_DATE,
1068 LAST_UPDATED_BY,
1069 LAST_UPDATE_DATE,
1070 LAST_UPDATE_LOGIN
1071 )
1072 VALUES
1073 (
1074 X_CUSTOMIZATION_APPLICATION_ID,
1075 X_CUSTOMIZATION_CODE,
1076 X_REGION_APPLICATION_ID,
1077 X_REGION_CODE,
1078 X_RF_CUSTOMIZATION_APPL_ID,
1079 X_RF_CUSTOMIZATION_CODE,
1080 X_RF_REGION_APPLICATION_ID,
1081 X_RF_REGION_CODE,
1082 X_RF_TAG,
1083 X_CREATED_BY,
1084 X_CREATION_DATE,
1085 X_LAST_UPDATED_BY,
1086 X_LAST_UPDATE_DATE,
1087 X_LAST_UPDATE_LOGIN
1088 );
1089 END IF;
1090
1091 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1092 X_RETURN_STATUS := 'T';
1093
1094
1095 END create_criteria_template_rf;
1096
1097
1098 PROCEDURE update_criteria_template_rf
1099 (
1100 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1101 X_CUSTOMIZATION_CODE IN VARCHAR2,
1102 X_REGION_APPLICATION_ID IN NUMBER,
1103 X_REGION_CODE IN VARCHAR2,
1104 X_RF_CUSTOMIZATION_APPL_ID IN NUMBER,
1105 X_RF_CUSTOMIZATION_CODE IN VARCHAR2,
1106 X_RF_REGION_APPLICATION_ID IN NUMBER,
1107 X_RF_REGION_CODE IN VARCHAR2,
1108 X_RF_TAG IN VARCHAR2,
1109 X_LAST_UPDATED_BY IN NUMBER,
1110 X_LAST_UPDATE_DATE IN DATE,
1111 X_LAST_UPDATE_LOGIN IN NUMBER,
1112 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1113 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1114 X_ERRORCODE OUT NOCOPY NUMBER
1115 )
1116 IS
1117
1118 l_Sysdate DATE := Sysdate;
1119 l_count NUMBER;
1120
1121 BEGIN
1122
1123 IF FND_API.To_Boolean(x_init_msg_list) THEN
1124 FND_MSG_PUB.Initialize;
1125 END IF;
1126
1127 SELECT
1128 COUNT(*) INTO l_count
1129 FROM
1130 EGO_CUSTOMIZATION_RF
1131 WHERE
1132 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1133 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1134 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1135 AND REGION_CODE = X_REGION_CODE
1136 AND RF_TAG = X_RF_TAG;
1137
1138 IF (l_count > 0) THEN
1139
1140 IF (X_RF_CUSTOMIZATION_CODE IS NULL) THEN
1141 DELETE_CRITERIA_TEMPLATE_RF(
1142 X_CUSTOMIZATION_APPLICATION_ID
1143 ,X_CUSTOMIZATION_CODE
1144 ,X_REGION_APPLICATION_ID
1145 ,X_REGION_CODE
1146 ,X_RF_TAG
1147 ,FND_API.G_FALSE
1148 ,X_RETURN_STATUS
1149 ,X_ERRORCODE
1150 );
1151 ELSE
1152
1153
1154 UPDATE EGO_CUSTOMIZATION_RF SET
1155 RF_CUSTOMIZATION_APPL_ID = X_RF_CUSTOMIZATION_APPL_ID,
1156 RF_CUSTOMIZATION_CODE = X_RF_CUSTOMIZATION_CODE,
1157 RF_REGION_APPLICATION_ID = X_RF_REGION_APPLICATION_ID,
1158 RF_REGION_CODE = X_RF_REGION_CODE,
1159 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1161 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1162 WHERE
1163 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1164 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1165 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1166 AND REGION_CODE = X_REGION_CODE
1167 AND RF_TAG = X_RF_TAG;
1168
1169 END IF;
1170
1171 END IF;
1172
1173 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1174 X_RETURN_STATUS := 'T';
1175
1176 END update_criteria_template_rf;
1177
1178
1179 PROCEDURE delete_criteria_template_rf
1180 (
1181 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1182 X_CUSTOMIZATION_CODE IN VARCHAR2,
1183 X_REGION_APPLICATION_ID IN NUMBER,
1184 X_REGION_CODE IN VARCHAR2,
1185 X_RF_TAG IN VARCHAR2,
1186 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1187 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1188 X_ERRORCODE OUT NOCOPY NUMBER
1189 )
1190 IS
1191
1192 l_Sysdate DATE := Sysdate;
1193 l_count NUMBER;
1194
1195 BEGIN
1196
1197 IF FND_API.To_Boolean(x_init_msg_list) THEN
1198 FND_MSG_PUB.Initialize;
1199 END IF;
1200
1201 SELECT
1202 COUNT(*) INTO l_count
1203 FROM
1204 EGO_CUSTOMIZATION_RF
1205 WHERE
1206 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1207 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1208 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1209 AND REGION_CODE = X_REGION_CODE
1210 AND RF_TAG = X_RF_TAG;
1211
1212 IF (l_count > 0) THEN
1213
1214 DELETE FROM EGO_CUSTOMIZATION_RF
1215 WHERE
1216 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1217 AND CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1218 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1219 AND REGION_CODE = X_REGION_CODE
1220 AND RF_TAG = X_RF_TAG;
1221
1222 END IF;
1223
1224 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1225 X_RETURN_STATUS := 'T';
1226
1227 END delete_criteria_template_rf;
1228
1229 ---------------------------------------------------------------
1230 PROCEDURE create_result_format
1231 (
1232 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1233 X_CUSTOMIZATION_CODE IN VARCHAR2,
1234 X_REGION_APPLICATION_ID IN NUMBER,
1235 X_REGION_CODE IN VARCHAR2,
1236 X_NAME IN VARCHAR2,
1237 X_DESCRIPTION IN VARCHAR2,
1238 X_NUM_ROWS_DISPLAYED IN NUMBER,
1239 X_DEFAULT_RESULT_FLAG IN VARCHAR2,
1240 X_SITE_ID IN NUMBER,
1241 X_WEB_USER_ID IN NUMBER,
1242 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
1243 X_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
1244 X_DATA_LEVEL IN VARCHAR2 DEFAULT NULL,
1245 X_CREATED_BY IN NUMBER,
1246 X_CREATION_DATE IN DATE,
1247 X_LAST_UPDATED_BY IN NUMBER,
1248 X_LAST_UPDATE_DATE IN DATE,
1249 X_LAST_UPDATE_LOGIN IN NUMBER,
1250 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
1251 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
1252 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
1253 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1254 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1255 X_ERRORCODE OUT NOCOPY NUMBER
1256 )
1257 IS
1258
1259 l_Sysdate DATE := Sysdate;
1260 l_rowid VARCHAR2(255);
1261
1262 BEGIN
1263
1264 IF FND_API.To_Boolean(x_init_msg_list) THEN
1265 FND_MSG_PUB.Initialize;
1266 END IF;
1267
1268 --first check unique constraint
1269
1270 IF (Results_Format_Name_Exists(X_NAME
1271 ,X_WEB_USER_ID
1272 ,NULL
1273 ,X_CUSTOMIZATION_APPLICATION_ID
1274 ,X_REGION_APPLICATION_ID
1275 ,X_REGION_CODE
1276 ,X_CUSTOMIZATION_LEVEL_ID
1277 ,X_CLASSIFICATION_1
1278 ,X_CLASSIFICATION_2
1279 ,X_CLASSIFICATION_3) = FALSE) THEN
1280
1281 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1282 X_ROWID => l_rowid,
1283 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1284 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1285 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1286 X_REGION_CODE => X_REGION_CODE,
1287 X_PROPERTY_NAME => 'RESULT_NAME',
1288 X_PROPERTY_VARCHAR2_VALUE => X_NAME,
1289 X_PROPERTY_NUMBER_VALUE => NULL,
1290 X_CRITERIA_JOIN_CONDITION => NULL,
1291 X_CREATED_BY => X_CREATED_BY,
1292 X_CREATION_DATE => X_CREATION_DATE,
1293 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1294 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1295 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1296 );
1297
1298 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1299 X_ROWID => l_rowid,
1300 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1301 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1302 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1303 X_REGION_CODE => X_REGION_CODE,
1304 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1305 X_PROPERTY_VARCHAR2_VALUE => X_DESCRIPTION,
1306 X_PROPERTY_NUMBER_VALUE => NULL,
1307 X_CRITERIA_JOIN_CONDITION => NULL,
1308 X_CREATED_BY => X_CREATED_BY,
1309 X_CREATION_DATE => X_CREATION_DATE,
1310 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1311 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1312 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1313 );
1314
1315 -- first thing updating other result format default flag if "Y" in this one
1316
1317 IF( X_DEFAULT_RESULT_FLAG = 'Y') THEN
1318
1319
1320 Update_Result_Format_Default
1321 (
1322 X_CUSTOMIZATION_APPLICATION_ID,
1323 X_REGION_APPLICATION_ID,
1324 X_REGION_CODE,
1325 X_WEB_USER_ID,
1326 X_CUSTOMIZATION_LEVEL_ID,
1327 X_IMPORT_FLAG,
1328 X_CLASSIFICATION_1,
1329 X_CLASSIFICATION_2,
1330 X_CLASSIFICATION_3,
1331 X_DATA_LEVEL
1332 );
1333
1334 END IF;
1335
1336 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1337 X_ROWID => l_rowid,
1338 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1339 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1340 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1341 X_REGION_CODE => X_REGION_CODE,
1342 X_PROPERTY_NAME => 'DEFAULT_RESULT_FLAG',
1343 X_PROPERTY_VARCHAR2_VALUE => X_DEFAULT_RESULT_FLAG,
1344 X_PROPERTY_NUMBER_VALUE => NULL,
1345 X_CRITERIA_JOIN_CONDITION => NULL,
1346 X_CREATED_BY => X_CREATED_BY,
1347 X_CREATION_DATE => X_CREATION_DATE,
1348 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1349 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1350 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1351 );
1352
1353 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1354 X_ROWID => l_rowid,
1355 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1356 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1357 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1358 X_REGION_CODE => X_REGION_CODE,
1359 X_PROPERTY_NAME => 'NUM_ROWS_DISPLAY',
1360 X_PROPERTY_VARCHAR2_VALUE => NULL,
1361 X_PROPERTY_NUMBER_VALUE => X_NUM_ROWS_DISPLAYED,
1362 X_CRITERIA_JOIN_CONDITION => NULL,
1363 X_CREATED_BY => X_CREATED_BY,
1364 X_CREATION_DATE => X_CREATION_DATE,
1365 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1366 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1367 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1368 );
1369
1370 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1371 X_ROWID => l_rowid,
1372 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1373 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1374 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1375 X_REGION_CODE => X_REGION_CODE,
1376 X_PROPERTY_NAME => 'SITE_ID',
1377 X_PROPERTY_VARCHAR2_VALUE => NULL,
1378 X_PROPERTY_NUMBER_VALUE => X_SITE_ID,
1379 X_CRITERIA_JOIN_CONDITION => NULL,
1380 X_CREATED_BY => X_CREATED_BY,
1381 X_CREATION_DATE => X_CREATION_DATE,
1382 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1383 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1384 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1385 );
1386
1387 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1388 X_ROWID => l_rowid,
1389 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1390 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1391 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1392 X_REGION_CODE => X_REGION_CODE,
1393 X_PROPERTY_NAME => 'WEB_USER_ID',
1394 X_PROPERTY_VARCHAR2_VALUE => NULL,
1395 X_PROPERTY_NUMBER_VALUE => X_WEB_USER_ID,
1396 X_CRITERIA_JOIN_CONDITION => NULL,
1397 X_CREATED_BY => X_CREATED_BY,
1398 X_CREATION_DATE => X_CREATION_DATE,
1399 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1400 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1401 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1402 );
1403
1404 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1405 X_ROWID => l_rowid,
1406 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1407 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1408 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1409 X_REGION_CODE => X_REGION_CODE,
1410 X_PROPERTY_NAME => 'CUSTOMIZATION_LEVEL_ID',
1411 X_PROPERTY_VARCHAR2_VALUE => NULL,
1412 X_PROPERTY_NUMBER_VALUE => X_CUSTOMIZATION_LEVEL_ID,
1413 X_CRITERIA_JOIN_CONDITION => NULL,
1414 X_CREATED_BY => X_CREATED_BY,
1415 X_CREATION_DATE => X_CREATION_DATE,
1416 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1417 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1418 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1419 );
1420
1421 IF (X_IMPORT_FLAG IS NOT NULL) THEN
1422
1423 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1424 X_ROWID => l_rowid,
1425 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1426 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1427 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1428 X_REGION_CODE => X_REGION_CODE,
1429 X_PROPERTY_NAME => 'IMPORT_FLAG',
1430 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1431 X_PROPERTY_NUMBER_VALUE => NULL,
1432 X_CRITERIA_JOIN_CONDITION => NULL,
1433 X_CREATED_BY => X_CREATED_BY,
1434 X_CREATION_DATE => X_CREATION_DATE,
1435 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1436 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1437 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1438 );
1439
1440 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1441 X_ROWID => l_rowid,
1442 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1443 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1444 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1445 X_REGION_CODE => X_REGION_CODE,
1446 X_PROPERTY_NAME => 'DATA_LEVEL',
1447 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1448 X_PROPERTY_NUMBER_VALUE => NULL,
1449 X_CRITERIA_JOIN_CONDITION => NULL,
1450 X_CREATED_BY => X_CREATED_BY,
1451 X_CREATION_DATE => X_CREATION_DATE,
1452 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1453 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1454 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1455 );
1456
1457
1458 END IF;
1459
1460 INSERT INTO EGO_CUSTOMIZATION_EXT
1461 (
1462 CUSTOMIZATION_APPLICATION_ID,
1463 CUSTOMIZATION_CODE,
1464 REGION_APPLICATION_ID,
1465 REGION_CODE,
1466 CLASSIFICATION1,
1467 CLASSIFICATION2,
1468 CLASSIFICATION3,
1469 CREATED_BY,
1470 CREATION_DATE,
1471 LAST_UPDATED_BY,
1472 LAST_UPDATE_DATE,
1473 LAST_UPDATE_LOGIN
1474 )
1475 VALUES
1476 (
1477 X_CUSTOMIZATION_APPLICATION_ID,
1478 X_CUSTOMIZATION_CODE,
1479 X_REGION_APPLICATION_ID,
1480 X_REGION_CODE,
1481 X_CLASSIFICATION_1,
1482 X_CLASSIFICATION_2,
1483 X_CLASSIFICATION_3,
1484 g_current_user_id,
1485 l_Sysdate,
1486 g_current_user_id,
1487 l_Sysdate,
1488 g_current_user_id
1489 );
1490
1491 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1492
1493 ELSE
1494
1495
1496 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_RESULTS_FORMAT');
1497 FND_MSG_PUB.Add;
1498
1499 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1500
1501
1502 END IF;
1503
1504 END create_result_format;
1505
1506 ---------------------------------------------------------
1507
1508 PROCEDURE update_result_format
1509 (
1510 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1511 X_CUSTOMIZATION_CODE IN VARCHAR2,
1512 X_REGION_APPLICATION_ID IN NUMBER,
1513 X_REGION_CODE IN VARCHAR2,
1514 X_NAME IN VARCHAR2,
1515 X_DESCRIPTION IN VARCHAR2,
1516 X_NUM_ROWS_DISPLAYED IN NUMBER,
1517 X_DEFAULT_RESULT_FLAG IN VARCHAR2,
1518 X_SITE_ID IN NUMBER,
1519 X_WEB_USER_ID IN NUMBER,
1520 X_CUSTOMIZATION_LEVEL_ID IN NUMBER,
1521 X_IMPORT_FLAG IN VARCHAR2 DEFAULT NULL,
1522 X_DATA_LEVEL IN VARCHAR2 DEFAULT NULL,
1523 X_LAST_UPDATED_BY IN NUMBER,
1524 X_LAST_UPDATE_DATE IN DATE,
1525 X_LAST_UPDATE_LOGIN IN NUMBER,
1526 X_CLASSIFICATION_1 IN VARCHAR2 DEFAULT NULL,
1527 X_CLASSIFICATION_2 IN VARCHAR2 DEFAULT NULL,
1528 X_CLASSIFICATION_3 IN VARCHAR2 DEFAULT NULL,
1529 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1530 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1531 X_ERRORCODE OUT NOCOPY NUMBER
1532 )
1533 IS
1534 l_Sysdate DATE := Sysdate;
1535 l_count NUMBER;
1536 l_rowid VARCHAR2(255);
1537 l_data_level VARCHAR2(4000);
1538
1539 CURSOR get_data_level IS
1540 select data_level_query.property_varchar2_value data_level
1541 from
1542 AK_CUSTOM_REGIONS_TL name_query ,
1543 AK_CUSTOM_REGIONS data_level_query
1544 where name_query.property_name = 'RESULT_NAME'
1545 and name_query.language = USERENV('LANG')
1546 and data_level_query.property_name (+) = 'DATA_LEVEL'
1547 and name_query.customization_application_id = data_level_query.customization_application_id(+)
1548 and name_query.customization_code = data_level_query.customization_code (+)
1549 and name_query.region_application_id = data_level_query.region_application_id(+)
1550 and name_query.region_code = data_level_query.region_code(+)
1551 and name_query.customization_code = x_customization_code
1552 and name_query.customization_application_id = x_customization_application_id
1553 and name_query.region_application_id = x_region_application_id
1554 and name_query.region_code = x_region_code;
1555
1556 BEGIN
1557
1558 IF FND_API.To_Boolean(x_init_msg_list) THEN
1559 FND_MSG_PUB.Initialize;
1560 END IF;
1561
1562 IF (Results_Format_Name_Exists(X_NAME
1563 ,X_WEB_USER_ID
1564 ,X_CUSTOMIZATION_CODE
1565 ,X_CUSTOMIZATION_APPLICATION_ID
1566 ,X_REGION_APPLICATION_ID
1567 ,X_REGION_CODE
1568 ,X_CUSTOMIZATION_LEVEL_ID
1569 ,X_CLASSIFICATION_1
1570 ,X_CLASSIFICATION_2
1571 ,X_CLASSIFICATION_3) = FALSE) THEN
1572
1573 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1574 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1575 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1576 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1577 X_REGION_CODE => X_REGION_CODE,
1578 X_PROPERTY_NAME => 'RESULT_NAME',
1579 X_PROPERTY_VARCHAR2_VALUE => X_NAME,
1580 X_PROPERTY_NUMBER_VALUE => NULL,
1581 X_CRITERIA_JOIN_CONDITION => NULL,
1582 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1583 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1584 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1585 );
1586
1587 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1588 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1589 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1590 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1591 X_REGION_CODE => X_REGION_CODE,
1592 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1593 X_PROPERTY_VARCHAR2_VALUE => X_DESCRIPTION,
1594 X_PROPERTY_NUMBER_VALUE => NULL,
1595 X_CRITERIA_JOIN_CONDITION => NULL,
1596 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1597 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1598 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1599 );
1600
1601 -- first thing updating other result format default flag if "Y" in this one
1602
1603 IF( X_DEFAULT_RESULT_FLAG = 'Y') THEN
1604
1605 FOR data_rec IN get_data_level LOOP
1606 l_data_level := data_rec.data_level;
1607 exit;
1608 END LOOP;
1609
1610 Update_Result_Format_Default
1611 (
1612 X_CUSTOMIZATION_APPLICATION_ID,
1613 X_REGION_APPLICATION_ID,
1614 X_REGION_CODE,
1615 X_WEB_USER_ID,
1616 X_CUSTOMIZATION_LEVEL_ID,
1617 X_IMPORT_FLAG,
1618 X_CLASSIFICATION_1,
1619 X_CLASSIFICATION_2,
1620 X_CLASSIFICATION_3,
1621 l_data_level
1622 );
1623
1624 END IF;
1625
1626
1627 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1628 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1629 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1630 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1631 X_REGION_CODE => X_REGION_CODE,
1632 X_PROPERTY_NAME => 'DEFAULT_RESULT_FLAG',
1633 X_PROPERTY_VARCHAR2_VALUE => X_DEFAULT_RESULT_FLAG,
1634 X_PROPERTY_NUMBER_VALUE => NULL,
1635 X_CRITERIA_JOIN_CONDITION => NULL,
1636 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1637 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1638 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1639 );
1640
1641 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1642 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1643 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1644 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1645 X_REGION_CODE => X_REGION_CODE,
1646 X_PROPERTY_NAME => 'NUM_ROWS_DISPLAY',
1647 X_PROPERTY_VARCHAR2_VALUE => NULL,
1648 X_PROPERTY_NUMBER_VALUE => X_NUM_ROWS_DISPLAYED,
1649 X_CRITERIA_JOIN_CONDITION => NULL,
1650 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1651 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1652 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1653 );
1654
1655 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1656 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1657 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1658 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1659 X_REGION_CODE => X_REGION_CODE,
1660 X_PROPERTY_NAME => 'SITE_ID',
1661 X_PROPERTY_VARCHAR2_VALUE => NULL,
1662 X_PROPERTY_NUMBER_VALUE => X_SITE_ID,
1663 X_CRITERIA_JOIN_CONDITION => NULL,
1664 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1665 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1666 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1667 );
1668
1669 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1670 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1671 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1672 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1673 X_REGION_CODE => X_REGION_CODE,
1674 X_PROPERTY_NAME => 'WEB_USER_ID',
1675 X_PROPERTY_VARCHAR2_VALUE => NULL,
1676 X_PROPERTY_NUMBER_VALUE => X_WEB_USER_ID,
1677 X_CRITERIA_JOIN_CONDITION => NULL,
1678 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1679 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1680 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1681 );
1682
1683 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1684 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1685 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1686 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1687 X_REGION_CODE => X_REGION_CODE,
1688 X_PROPERTY_NAME => 'CUSTOMIZATION_LEVEL_ID',
1689 X_PROPERTY_VARCHAR2_VALUE => NULL,
1690 X_PROPERTY_NUMBER_VALUE => X_CUSTOMIZATION_LEVEL_ID,
1691 X_CRITERIA_JOIN_CONDITION => NULL,
1692 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1693 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1694 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1695 );
1696
1697 SELECT COUNT(*) INTO l_count
1698 FROM AK_CUSTOM_REGIONS
1699 WHERE
1700 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1701 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1702 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1703 AND REGION_CODE = X_REGION_CODE
1704 AND PROPERTY_NAME = 'IMPORT_FLAG';
1705
1706 IF (l_count > 0) THEN
1707
1708 IF (X_IMPORT_FLAG IS NULL) THEN
1709 DELETE FROM AK_CUSTOM_REGIONS
1710 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1711 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1712 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1713 AND REGION_CODE = X_REGION_CODE
1714 AND PROPERTY_NAME = 'IMPORT_FLAG';
1715 ELSE
1716 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1717 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1718 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1719 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1720 X_REGION_CODE => X_REGION_CODE,
1721 X_PROPERTY_NAME => 'IMPORT_FLAG',
1722 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1723 X_PROPERTY_NUMBER_VALUE => NULL,
1724 X_CRITERIA_JOIN_CONDITION => NULL,
1725 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1726 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1727 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1728 );
1729 END IF;
1730
1731 ELSE
1732
1733 IF (X_IMPORT_FLAG IS NOT NULL) THEN
1734
1735 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1736 X_ROWID => l_rowid,
1737 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1738 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1739 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1740 X_REGION_CODE => X_REGION_CODE,
1741 X_PROPERTY_NAME => 'IMPORT_FLAG',
1742 X_PROPERTY_VARCHAR2_VALUE => X_IMPORT_FLAG,
1743 X_PROPERTY_NUMBER_VALUE => NULL,
1744 X_CRITERIA_JOIN_CONDITION => NULL,
1745 X_CREATED_BY => X_LAST_UPDATED_BY,
1746 X_CREATION_DATE => X_LAST_UPDATE_DATE,
1747 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1748 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1749 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1750 );
1751
1752 END IF;
1753
1754 END IF;
1755
1756 --Bug 6011948
1757 -- Update data level also if a value is supplied for it..
1758
1759 IF (X_DATA_LEVEL IS NOT NULL) THEN
1760
1761 SELECT COUNT(*) INTO l_count
1762 FROM AK_CUSTOM_REGIONS
1763 WHERE
1764 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1765 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1766 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1767 AND REGION_CODE = X_REGION_CODE
1768 AND PROPERTY_NAME = 'DATA_LEVEL';
1769
1770 IF (l_count > 0) THEN
1771 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1772 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1773 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1774 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1775 X_REGION_CODE => X_REGION_CODE,
1776 X_PROPERTY_NAME => 'DATA_LEVEL',
1777 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1778 X_PROPERTY_NUMBER_VALUE => NULL,
1779 X_CRITERIA_JOIN_CONDITION => NULL,
1780 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1781 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1782 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1783 );
1784
1785 ELSE
1786
1787 AK_CUSTOM_REGIONS_PKG.INSERT_ROW(
1788 X_ROWID => l_rowid,
1789 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
1790 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
1791 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
1792 X_REGION_CODE => X_REGION_CODE,
1793 X_PROPERTY_NAME => 'DATA_LEVEL',
1794 X_PROPERTY_VARCHAR2_VALUE => X_DATA_LEVEL,
1795 X_PROPERTY_NUMBER_VALUE => NULL,
1796 X_CRITERIA_JOIN_CONDITION => NULL,
1797 X_CREATED_BY => X_LAST_UPDATED_BY,
1798 X_CREATION_DATE => X_LAST_UPDATE_DATE,
1799 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
1800 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
1801 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
1802 );
1803
1804 END IF;
1805 END IF;
1806
1807
1808
1809
1810
1811
1812
1813 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1814
1815 ELSE
1816
1817 FND_MESSAGE.Set_Name(g_app_name, 'EGO_DUP_RESULTS_FORMAT');
1818 FND_MSG_PUB.Add;
1819
1820 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1821
1822 END IF;
1823
1824 END update_result_format;
1825
1826 -------------------------------------------------------------
1827
1828 PROCEDURE delete_result_format
1829 (
1830 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1831 X_CUSTOMIZATION_CODE IN VARCHAR2,
1832 X_REGION_APPLICATION_ID IN NUMBER,
1833 X_REGION_CODE IN VARCHAR2,
1834 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1835 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1836 X_ERRORCODE OUT NOCOPY NUMBER
1837 )
1838 IS
1839
1840 BEGIN
1841
1842 IF FND_API.To_Boolean(x_init_msg_list) THEN
1843 FND_MSG_PUB.Initialize;
1844 END IF;
1845
1846 Check_Result_Format_Deletion
1847 (
1848 X_CUSTOMIZATION_APPLICATION_ID,
1849 X_CUSTOMIZATION_CODE,
1850 X_REGION_APPLICATION_ID,
1851 X_REGION_CODE,
1852 FND_API.G_FALSE,
1853 X_RETURN_STATUS,
1854 X_ERRORCODE
1855 );
1856
1857 IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) THEN
1858
1859 DELETE FROM AK_CUSTOM_REGIONS
1860 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1861 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1862 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1863 AND REGION_CODE = X_REGION_CODE;
1864
1865 DELETE FROM AK_CUSTOM_REGIONS_TL
1866 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1867 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1868 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1869 AND REGION_CODE = X_REGION_CODE;
1870
1871 DELETE FROM EGO_CUSTOMIZATION_EXT
1872 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1873 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1874 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1875 AND REGION_CODE = X_REGION_CODE;
1876
1877 DELETE FROM AK_CUSTOM_REGION_ITEMS
1878 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1879 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1880 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1881 AND REGION_CODE = X_REGION_CODE;
1882
1883 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
1884 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
1885 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
1886 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
1887 AND REGION_CODE = X_REGION_CODE;
1888
1889 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1890
1891 ELSE
1892
1893 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1894
1895 END IF;
1896
1897 END delete_result_format;
1898
1899
1900 PROCEDURE translate_result_format
1901 ( p_customization_application_id IN NUMBER
1902 ,p_customization_code IN VARCHAR2
1903 ,p_region_application_id IN NUMBER
1904 ,p_region_code IN VARCHAR2
1905 ,p_last_update_date IN VARCHAR2
1906 ,p_last_updated_by IN NUMBER
1907 ,p_name IN VARCHAR2
1908 ,p_description IN VARCHAR2
1909 ,x_return_status OUT NOCOPY VARCHAR2
1910 ,x_msg_data OUT NOCOPY VARCHAR2
1911 ) IS
1912
1913 l_last_update_login ak_customizations.last_update_login%TYPE;
1914 l_last_update_date ak_customizations.last_update_date%TYPE;
1915 l_last_updated_by ak_customizations.last_updated_by%TYPE;
1916
1917 CURSOR c_get_last_update_info (cp_property_name IN VARCHAR2) IS
1918 SELECT last_updated_by, last_update_date
1919 FROM ak_custom_regions_tl
1920 WHERE customization_application_id = p_customization_application_id
1921 AND customization_code = p_customization_code
1922 AND region_application_id = p_region_application_id
1923 AND region_code = p_region_code
1924 AND property_name = cp_property_name
1925 AND USERENV('LANG') IN (language, source_lang);
1926
1927 BEGIN
1928
1929 l_last_update_login := FND_GLOBAL.Login_Id;
1930
1931 OPEN c_get_last_update_info (cp_property_name => 'RESULT_NAME');
1932 FETCH c_get_last_update_info
1933 INTO l_last_updated_by, l_last_update_date;
1934 CLOSE c_get_last_update_info;
1935
1936 IF (fnd_load_util.upload_test(p_last_updated_by
1937 ,p_last_update_date
1938 ,l_last_updated_by
1939 ,l_last_update_date
1940 ,NULL)) THEN
1941
1942 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1943 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
1944 X_CUSTOMIZATION_CODE => p_customization_code,
1945 X_REGION_APPLICATION_ID => p_region_application_id,
1946 X_REGION_CODE => p_region_code,
1947 X_PROPERTY_NAME => 'RESULT_NAME',
1948 X_PROPERTY_VARCHAR2_VALUE => p_name,
1949 X_PROPERTY_NUMBER_VALUE => NULL,
1950 X_CRITERIA_JOIN_CONDITION => NULL,
1951 X_LAST_UPDATED_BY => p_last_updated_by,
1952 X_LAST_UPDATE_DATE => SYSDATE,
1953 X_LAST_UPDATE_LOGIN => l_last_update_login
1954 );
1955 END IF;
1956
1957 OPEN c_get_last_update_info (cp_property_name => 'RESULT_DESCRIPTION');
1958 FETCH c_get_last_update_info
1959 INTO l_last_updated_by, l_last_update_date;
1960 CLOSE c_get_last_update_info;
1961
1962 IF (fnd_load_util.upload_test(p_last_updated_by
1963 ,p_last_update_date
1964 ,l_last_updated_by
1965 ,l_last_update_date
1966 ,NULL)) THEN
1967 AK_CUSTOM_REGIONS_PKG.UPDATE_ROW(
1968 X_CUSTOMIZATION_APPLICATION_ID => p_customization_application_id,
1969 X_CUSTOMIZATION_CODE => p_customization_code,
1970 X_REGION_APPLICATION_ID => p_region_application_id,
1971 X_REGION_CODE => p_region_code,
1972 X_PROPERTY_NAME => 'RESULT_DESCRIPTION',
1973 X_PROPERTY_VARCHAR2_VALUE => p_description,
1974 X_PROPERTY_NUMBER_VALUE => NULL,
1975 X_CRITERIA_JOIN_CONDITION => NULL,
1976 X_LAST_UPDATED_BY => p_last_updated_by,
1977 X_LAST_UPDATE_DATE => SYSDATE,
1978 X_LAST_UPDATE_LOGIN => l_last_update_login
1979 );
1980 END IF;
1981 x_return_status := 'S';
1982 EXCEPTION
1983 WHEN OTHERS THEN
1984 x_return_status := 'E';
1985 x_msg_data := SQLERRM;
1986 END translate_result_format;
1987
1988 ---------------------------------------------------------
1989
1990
1991 PROCEDURE create_result_column
1992 (
1993 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
1994 X_CUSTOMIZATION_CODE IN VARCHAR2,
1995 X_REGION_APPLICATION_ID IN NUMBER,
1996 X_REGION_CODE IN VARCHAR2,
1997 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
1998 X_ATTRIBUTE_CODE IN VARCHAR2,
1999 X_DISPLAY_SEQUENCE IN NUMBER,
2000 X_ORDER_SEQUENCE IN NUMBER,
2001 X_ORDER_DIRECTION IN VARCHAR2,
2002 X_COLUMN_NAME IN VARCHAR2 := NULL,
2003 X_SHOW_TOTAL IN VARCHAR2 := NULL,
2004 X_CREATED_BY IN NUMBER,
2005 X_CREATION_DATE IN DATE,
2006 X_LAST_UPDATED_BY IN NUMBER,
2007 X_LAST_UPDATE_DATE IN DATE,
2008 X_LAST_UPDATE_LOGIN IN NUMBER,
2009 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2010 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2011 X_ERRORCODE OUT NOCOPY NUMBER
2012 )
2013 IS
2014
2015 l_Sysdate DATE := Sysdate;
2016 l_rowid VARCHAR2(255);
2017
2018 BEGIN
2019
2020 IF FND_API.To_Boolean(x_init_msg_list) THEN
2021 FND_MSG_PUB.Initialize;
2022 END IF;
2023
2024 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2025 X_ROWID => l_rowid,
2026 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2027 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2028 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2029 X_REGION_CODE => X_REGION_CODE,
2030 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2031 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2032 X_PROPERTY_NAME => 'DISPLAY_SEQUENCE',
2033 X_PROPERTY_VARCHAR2_VALUE => NULL,
2034 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2035 X_PROPERTY_DATE_VALUE => NULL,
2036 X_CREATED_BY => X_CREATED_BY,
2037 X_CREATION_DATE => X_CREATION_DATE,
2038 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2039 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2040 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2041 );
2042
2043 IF (X_ORDER_SEQUENCE IS NOT NULL) THEN
2044
2045 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2046 X_ROWID => l_rowid,
2047 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2048 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2049 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2050 X_REGION_CODE => X_REGION_CODE,
2051 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2052 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2053 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2054 X_PROPERTY_VARCHAR2_VALUE => NULL,
2055 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2056 X_PROPERTY_DATE_VALUE => NULL,
2057 X_CREATED_BY => X_CREATED_BY,
2058 X_CREATION_DATE => X_CREATION_DATE,
2059 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2060 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2061 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2062 );
2063
2064 END IF;
2065
2066 IF (X_ORDER_DIRECTION IS NOT NULL) THEN
2067
2068 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2069 X_ROWID => l_rowid,
2070 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2071 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2072 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2073 X_REGION_CODE => X_REGION_CODE,
2074 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2075 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2076 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2077 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2078 X_PROPERTY_NUMBER_VALUE => NULL,
2079 X_PROPERTY_DATE_VALUE => NULL,
2080 X_CREATED_BY => X_CREATED_BY,
2081 X_CREATION_DATE => X_CREATION_DATE,
2082 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2083 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2084 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2085 );
2086 END IF;
2087
2088 IF (X_COLUMN_NAME IS NOT NULL) THEN
2089
2090 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2091 X_ROWID => l_rowid,
2092 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2093 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2094 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2095 X_REGION_CODE => X_REGION_CODE,
2096 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2097 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2098 X_PROPERTY_NAME => 'COLUMN_NAME',
2099 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2100 X_PROPERTY_NUMBER_VALUE => NULL,
2101 X_PROPERTY_DATE_VALUE => NULL,
2102 X_CREATED_BY => X_CREATED_BY,
2103 X_CREATION_DATE => X_CREATION_DATE,
2104 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2105 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2106 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2107 );
2108 END IF;
2109
2110 IF (X_SHOW_TOTAL IS NOT NULL) THEN
2111
2112 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2113 X_ROWID => l_rowid,
2114 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2115 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2116 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2117 X_REGION_CODE => X_REGION_CODE,
2118 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2119 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2120 X_PROPERTY_NAME => 'SHOW_TOTAL',
2121 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2122 X_PROPERTY_NUMBER_VALUE => NULL,
2123 X_PROPERTY_DATE_VALUE => NULL,
2124 X_CREATED_BY => X_CREATED_BY,
2125 X_CREATION_DATE => X_CREATION_DATE,
2126 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2127 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2128 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2129 );
2130 END IF;
2131
2132 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2133 X_RETURN_STATUS := 'T';
2134
2135
2136 END create_result_column;
2137
2138
2139 ---------------------------------------------------------
2140
2141
2142 PROCEDURE update_result_column
2143 (
2144 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2145 X_CUSTOMIZATION_CODE IN VARCHAR2,
2146 X_REGION_APPLICATION_ID IN NUMBER,
2147 X_REGION_CODE IN VARCHAR2,
2148 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2149 X_ATTRIBUTE_CODE IN VARCHAR2,
2150 X_DISPLAY_SEQUENCE IN NUMBER,
2151 X_ORDER_SEQUENCE IN NUMBER,
2152 X_ORDER_DIRECTION IN VARCHAR2,
2153 X_COLUMN_NAME IN VARCHAR2 := NULL,
2154 X_SHOW_TOTAL IN VARCHAR2 := NULL,
2155 X_LAST_UPDATED_BY IN NUMBER,
2156 X_LAST_UPDATE_DATE IN DATE,
2157 X_LAST_UPDATE_LOGIN IN NUMBER,
2158 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2159 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2160 X_ERRORCODE OUT NOCOPY NUMBER
2161 )
2162 IS
2163
2164 l_Sysdate DATE := Sysdate;
2165 l_rowid VARCHAR2(255);
2166 l_count NUMBER;
2167
2168 BEGIN
2169
2170 IF FND_API.To_Boolean(x_init_msg_list) THEN
2171 FND_MSG_PUB.Initialize;
2172 END IF;
2173
2174 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2175 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2176 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2177 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2178 X_REGION_CODE => X_REGION_CODE,
2179 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2180 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2181 X_PROPERTY_NAME => 'DISPLAY_SEQUENCE',
2182 X_PROPERTY_VARCHAR2_VALUE => NULL,
2183 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2184 X_PROPERTY_DATE_VALUE => NULL,
2185 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2186 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2187 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2188 );
2189
2190 SELECT
2191 COUNT(*) INTO l_count
2192 FROM
2193 AK_CUSTOM_REGION_ITEMS
2194 WHERE
2195 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2196 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2197 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2198 AND REGION_CODE = X_REGION_CODE
2199 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2200 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2201 AND PROPERTY_NAME = 'ORDER_SEQUENCE';
2202
2203 IF (X_ORDER_SEQUENCE IS NULL) THEN
2204
2205 IF (l_count > 0) THEN
2206 DELETE FROM AK_CUSTOM_REGION_ITEMS
2207 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2208 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2209 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2210 AND REGION_CODE = X_REGION_CODE
2211 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2212 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2213 AND PROPERTY_NAME = 'ORDER_SEQUENCE';
2214 END IF;
2215
2216 ELSE
2217
2218 IF (l_count > 0) THEN
2219
2220 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2221 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2222 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2223 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2224 X_REGION_CODE => X_REGION_CODE,
2225 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2226 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2227 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2228 X_PROPERTY_VARCHAR2_VALUE => NULL,
2229 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2230 X_PROPERTY_DATE_VALUE => NULL,
2231 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2232 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2233 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2234 );
2235
2236 ELSE
2237
2238 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2239 X_ROWID => l_rowid,
2240 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2241 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2242 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2243 X_REGION_CODE => X_REGION_CODE,
2244 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2245 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2246 X_PROPERTY_NAME => 'ORDER_SEQUENCE',
2247 X_PROPERTY_VARCHAR2_VALUE => NULL,
2248 X_PROPERTY_NUMBER_VALUE => X_ORDER_SEQUENCE,
2249 X_PROPERTY_DATE_VALUE => NULL,
2250 X_CREATED_BY => X_LAST_UPDATED_BY,
2251 X_CREATION_DATE => SYSDATE,
2252 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2253 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2254 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2255 );
2256
2257 END IF;
2258
2259 END IF;
2260
2261
2262 SELECT
2263 COUNT(*) INTO l_count
2264 FROM
2265 AK_CUSTOM_REGION_ITEMS
2266 WHERE
2267 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2268 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2269 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2270 AND REGION_CODE = X_REGION_CODE
2271 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2272 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2273 AND PROPERTY_NAME = 'ORDER_DIRECTION';
2274
2275 IF (X_ORDER_DIRECTION IS NULL) THEN
2276
2277 IF (l_count > 0) THEN
2278 DELETE FROM AK_CUSTOM_REGION_ITEMS
2279 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2280 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2281 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2282 AND REGION_CODE = X_REGION_CODE
2283 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2284 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2285 AND PROPERTY_NAME = 'ORDER_DIRECTION';
2286 END IF;
2287
2288 ELSE
2289
2290 IF (l_count > 0) THEN
2291
2292 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2293 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2294 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2295 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2296 X_REGION_CODE => X_REGION_CODE,
2297 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2298 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2299 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2300 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2301 X_PROPERTY_NUMBER_VALUE => NULL,
2302 X_PROPERTY_DATE_VALUE => NULL,
2303 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2304 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2305 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2306 );
2307
2308 ELSE
2309
2310 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2311 X_ROWID => l_rowid,
2312 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2313 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2314 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2315 X_REGION_CODE => X_REGION_CODE,
2316 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2317 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2318 X_PROPERTY_NAME => 'ORDER_DIRECTION',
2319 X_PROPERTY_VARCHAR2_VALUE => X_ORDER_DIRECTION,
2320 X_PROPERTY_NUMBER_VALUE => NULL,
2321 X_PROPERTY_DATE_VALUE => NULL,
2322 X_CREATED_BY => X_LAST_UPDATED_BY,
2323 X_CREATION_DATE => SYSDATE,
2324 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2325 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2326 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2327 );
2328
2329 END IF;
2330
2331 END IF;
2332
2333 SELECT
2334 COUNT(*) INTO l_count
2335 FROM
2336 AK_CUSTOM_REGION_ITEMS
2337 WHERE
2338 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2339 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2340 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2341 AND REGION_CODE = X_REGION_CODE
2342 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2343 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2344 AND PROPERTY_NAME = 'COLUMN_NAME';
2345
2346 IF (X_COLUMN_NAME IS NULL) THEN
2347
2348 IF (l_count > 0) THEN
2349 DELETE FROM AK_CUSTOM_REGION_ITEMS
2350 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2351 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2352 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2353 AND REGION_CODE = X_REGION_CODE
2354 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2355 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2356 AND PROPERTY_NAME = 'COLUMN_NAME';
2357 END IF;
2358
2359 ELSE
2360
2361 IF (l_count > 0) THEN
2362
2363 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2364 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2365 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2366 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2367 X_REGION_CODE => X_REGION_CODE,
2368 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2369 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2370 X_PROPERTY_NAME => 'COLUMN_NAME',
2371 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2372 X_PROPERTY_NUMBER_VALUE => NULL,
2373 X_PROPERTY_DATE_VALUE => NULL,
2374 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2375 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2376 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2377 );
2378
2379 ELSE
2380
2381 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2382 X_ROWID => l_rowid,
2383 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2384 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2385 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2386 X_REGION_CODE => X_REGION_CODE,
2387 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2388 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2389 X_PROPERTY_NAME => 'COLUMN_NAME',
2390 X_PROPERTY_VARCHAR2_VALUE => X_COLUMN_NAME,
2391 X_PROPERTY_NUMBER_VALUE => NULL,
2392 X_PROPERTY_DATE_VALUE => NULL,
2393 X_CREATED_BY => X_LAST_UPDATED_BY,
2394 X_CREATION_DATE => SYSDATE,
2395 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2396 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2397 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2398 );
2399
2400 END IF;
2401
2402 END IF;
2403
2404 SELECT
2405 COUNT(*) INTO l_count
2406 FROM
2407 AK_CUSTOM_REGION_ITEMS
2408 WHERE
2409 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2410 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2411 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2412 AND REGION_CODE = X_REGION_CODE
2413 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2414 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2415 AND PROPERTY_NAME = 'SHOW_TOTAL';
2416
2417 IF (X_SHOW_TOTAL IS NULL) THEN
2418
2419 IF (l_count > 0) THEN
2420 DELETE FROM AK_CUSTOM_REGION_ITEMS
2421 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2422 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2423 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2424 AND REGION_CODE = X_REGION_CODE
2425 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2426 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2427 AND PROPERTY_NAME = 'SHOW_TOTAL';
2428 END IF;
2429
2430 ELSE
2431
2432 IF (l_count > 0) THEN
2433
2434 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
2435 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2436 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2437 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2438 X_REGION_CODE => X_REGION_CODE,
2439 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2440 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2441 X_PROPERTY_NAME => 'SHOW_TOTAL',
2442 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2443 X_PROPERTY_NUMBER_VALUE => NULL,
2444 X_PROPERTY_DATE_VALUE => NULL,
2445 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2446 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2447 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2448 );
2449
2450 ELSE
2451
2452 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2453 X_ROWID => l_rowid,
2454 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2455 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2456 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2457 X_REGION_CODE => X_REGION_CODE,
2458 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2459 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2460 X_PROPERTY_NAME => 'SHOW_TOTAL',
2461 X_PROPERTY_VARCHAR2_VALUE => X_SHOW_TOTAL,
2462 X_PROPERTY_NUMBER_VALUE => NULL,
2463 X_PROPERTY_DATE_VALUE => NULL,
2464 X_CREATED_BY => X_LAST_UPDATED_BY,
2465 X_CREATION_DATE => SYSDATE,
2466 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2467 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2468 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2469 );
2470
2471 END IF;
2472
2473 END IF;
2474
2475
2476 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2477 X_RETURN_STATUS := 'T';
2478
2479
2480 END update_result_column;
2481
2482 -------------------------------------------------------------
2483
2484 PROCEDURE delete_result_column
2485 (
2486 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2487 X_CUSTOMIZATION_CODE IN VARCHAR2,
2488 X_REGION_APPLICATION_ID IN NUMBER,
2489 X_REGION_CODE IN VARCHAR2,
2490 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2491 X_ATTRIBUTE_CODE IN VARCHAR2,
2492 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2493 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2494 X_ERRORCODE OUT NOCOPY NUMBER
2495 )
2496 IS
2497
2498 BEGIN
2499
2500 IF FND_API.To_Boolean(x_init_msg_list) THEN
2501 FND_MSG_PUB.Initialize;
2502 END IF;
2503
2504 DELETE FROM AK_CUSTOM_REGION_ITEMS
2505 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2506 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2507 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2508 AND REGION_CODE = X_REGION_CODE
2509 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2510 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
2511
2512 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
2513 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2514 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2515 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2516 AND REGION_CODE = X_REGION_CODE
2517 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2518 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
2519
2520 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2521 X_RETURN_STATUS := 'T';
2522
2523
2524 END delete_result_column;
2525 ---------------------------------------------------------
2526
2527 PROCEDURE insert_criterion
2528 (
2529 X_ROWID IN OUT NOCOPY VARCHAR2,
2530 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2531 X_CUSTOMIZATION_CODE IN VARCHAR2,
2532 X_REGION_APPLICATION_ID IN NUMBER,
2533 X_REGION_CODE IN VARCHAR2,
2534 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2535 X_ATTRIBUTE_CODE IN VARCHAR2,
2536 X_SEQUENCE_NUMBER IN NUMBER,
2537 X_OPERATION IN VARCHAR2,
2538 X_VALUE_VARCHAR2 IN VARCHAR2,
2539 X_SECOND_VALUE_VARCHAR2 IN VARCHAR2,
2540 X_VALUE_NUMBER IN NUMBER,
2541 X_SECOND_VALUE_NUMBER IN NUMBER,
2542 X_VALUE_DATE IN DATE,
2543 X_SECOND_VALUE_DATE IN DATE,
2544 X_CREATED_BY IN NUMBER,
2545 X_CREATION_DATE IN DATE,
2546 X_LAST_UPDATED_BY IN NUMBER,
2547 X_LAST_UPDATE_DATE IN DATE,
2548 X_LAST_UPDATE_LOGIN IN NUMBER,
2549 X_START_DATE_ACTIVE IN DATE,
2550 X_END_DATE_ACTIVE IN DATE,
2551 X_USE_KEYWORD_SEARCH IN VARCHAR2 := 'Y',
2552 X_MATCH_CONDITION IN VARCHAR2 := 'ALL',
2553 X_FUZZY IN VARCHAR2 := 'N',
2554 X_STEMMING IN VARCHAR2 := 'N',
2555 X_SYNONYMS IN VARCHAR2 := 'N',
2556 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2557 )
2558 IS
2559
2560 BEGIN
2561
2562 IF FND_API.To_Boolean(x_init_msg_list) THEN
2563 FND_MSG_PUB.Initialize;
2564 END IF;
2565
2566 AK_CRITERIA_PKG.insert_row
2567 (
2568 X_ROWID,
2569 X_CUSTOMIZATION_APPLICATION_ID,
2570 X_CUSTOMIZATION_CODE,
2571 X_REGION_APPLICATION_ID,
2572 X_REGION_CODE,
2573 X_ATTRIBUTE_APPLICATION_ID,
2574 X_ATTRIBUTE_CODE,
2575 X_SEQUENCE_NUMBER,
2576 X_OPERATION,
2577 X_VALUE_VARCHAR2,
2578 X_VALUE_NUMBER,
2579 X_VALUE_DATE,
2580 X_CREATED_BY,
2581 X_CREATION_DATE,
2582 X_LAST_UPDATED_BY,
2583 X_LAST_UPDATE_DATE,
2584 X_LAST_UPDATE_LOGIN,
2585 X_START_DATE_ACTIVE,
2586 X_END_DATE_ACTIVE
2587 );
2588
2589 -- Now we see if we need to insert another (for between operators)
2590 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
2591 AK_CRITERIA_PKG.insert_row
2592 (
2593 X_ROWID,
2594 X_CUSTOMIZATION_APPLICATION_ID,
2595 X_CUSTOMIZATION_CODE,
2596 X_REGION_APPLICATION_ID,
2597 X_REGION_CODE,
2598 X_ATTRIBUTE_APPLICATION_ID,
2599 X_ATTRIBUTE_CODE,
2600 -1*X_SEQUENCE_NUMBER,
2601 X_OPERATION,
2602 X_SECOND_VALUE_VARCHAR2,
2603 X_SECOND_VALUE_NUMBER,
2604 X_SECOND_VALUE_DATE,
2605 X_CREATED_BY,
2606 X_CREATION_DATE,
2607 X_LAST_UPDATED_BY,
2608 X_LAST_UPDATE_DATE,
2609 X_LAST_UPDATE_LOGIN,
2610 X_START_DATE_ACTIVE,
2611 X_END_DATE_ACTIVE
2612 );
2613 END IF;
2614
2615 -- Matching
2616 INSERT INTO EGO_CRITERIA_EXT
2617 (
2618 CUSTOMIZATION_APPLICATION_ID,
2619 CUSTOMIZATION_CODE,
2620 REGION_APPLICATION_ID,
2621 REGION_CODE,
2622 ATTRIBUTE_APPLICATION_ID,
2623 ATTRIBUTE_CODE,
2624 SEQUENCE_NUMBER,
2625 USE_KEYWORD_SEARCH,
2626 MATCH_CONDITION,
2627 FUZZY,
2628 STEMMING,
2629 SYNONYMS,
2630 CREATED_BY,
2631 CREATION_DATE,
2632 LAST_UPDATED_BY,
2633 LAST_UPDATED_DATE,
2634 LAST_UPDATE_LOGIN
2635 )
2636 VALUES
2637 (
2638 X_CUSTOMIZATION_APPLICATION_ID,
2639 X_CUSTOMIZATION_CODE,
2640 X_REGION_APPLICATION_ID,
2641 X_REGION_CODE,
2642 X_ATTRIBUTE_APPLICATION_ID,
2643 X_ATTRIBUTE_CODE,
2644 X_SEQUENCE_NUMBER,
2645 X_USE_KEYWORD_SEARCH,
2646 X_MATCH_CONDITION,
2647 X_FUZZY,
2648 X_STEMMING,
2649 X_SYNONYMS,
2650 X_CREATED_BY,
2651 X_CREATION_DATE,
2652 X_LAST_UPDATED_BY,
2653 X_LAST_UPDATE_DATE,
2654 X_LAST_UPDATE_LOGIN
2655 );
2656 -- Matching
2657
2658 END insert_criterion;
2659
2660 PROCEDURE update_criterion
2661 (
2662 X_ROWID IN OUT NOCOPY VARCHAR2,
2663 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2664 X_CUSTOMIZATION_CODE IN VARCHAR2,
2665 X_REGION_APPLICATION_ID IN NUMBER,
2666 X_REGION_CODE IN VARCHAR2,
2667 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2668 X_ATTRIBUTE_CODE IN VARCHAR2,
2669 X_SEQUENCE_NUMBER IN NUMBER,
2670 X_OPERATION IN VARCHAR2,
2671 X_VALUE_VARCHAR2 IN VARCHAR2,
2672 X_SECOND_VALUE_VARCHAR2 IN VARCHAR2,
2673 X_VALUE_NUMBER IN NUMBER,
2674 X_SECOND_VALUE_NUMBER IN NUMBER,
2675 X_VALUE_DATE IN DATE,
2676 X_SECOND_VALUE_DATE IN DATE,
2677 X_LAST_UPDATED_BY IN NUMBER,
2678 X_LAST_UPDATE_DATE IN DATE,
2679 X_LAST_UPDATE_LOGIN IN NUMBER,
2680 X_START_DATE_ACTIVE IN DATE,
2681 X_END_DATE_ACTIVE IN DATE,
2682 X_USE_KEYWORD_SEARCH IN VARCHAR2 := 'Y',
2683 X_MATCH_CONDITION IN VARCHAR2 := 'ALL',
2684 X_FUZZY IN VARCHAR2 := 'N',
2685 X_STEMMING IN VARCHAR2 := 'N',
2686 X_SYNONYMS IN VARCHAR2 := 'N',
2687 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2688 )
2689 IS
2690
2691 l_count NUMBER;
2692
2693 BEGIN
2694
2695 IF FND_API.To_Boolean(x_init_msg_list) THEN
2696 FND_MSG_PUB.Initialize;
2697 END IF;
2698
2699 AK_CRITERIA_PKG.update_row
2700 (
2701 X_CUSTOMIZATION_APPLICATION_ID,
2702 X_CUSTOMIZATION_CODE,
2703 X_REGION_APPLICATION_ID,
2704 X_REGION_CODE,
2705 X_ATTRIBUTE_APPLICATION_ID,
2706 X_ATTRIBUTE_CODE,
2707 X_SEQUENCE_NUMBER,
2708 X_OPERATION,
2709 X_VALUE_VARCHAR2,
2710 X_VALUE_NUMBER,
2711 X_VALUE_DATE,
2712 X_LAST_UPDATED_BY,
2713 X_LAST_UPDATE_DATE,
2714 X_LAST_UPDATE_LOGIN,
2715 X_START_DATE_ACTIVE,
2716 X_END_DATE_ACTIVE
2717 );
2718
2719 --See if we need to update, delete, or insert a second row
2720
2721 SELECT
2722 COUNT(*) INTO l_count
2723 FROM
2724 AK_CRITERIA
2725 WHERE
2726 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2727 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2728 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2729 AND REGION_CODE = X_REGION_CODE
2730 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2731 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2732 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2733
2734 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
2735 --We either need to insert or update a row
2736 BEGIN
2737
2738 IF (l_count = 0) THEN
2739
2740 AK_CRITERIA_PKG.insert_row
2741 (
2742 X_ROWID,
2743 X_CUSTOMIZATION_APPLICATION_ID,
2744 X_CUSTOMIZATION_CODE,
2745 X_REGION_APPLICATION_ID,
2746 X_REGION_CODE,
2747 X_ATTRIBUTE_APPLICATION_ID,
2748 X_ATTRIBUTE_CODE,
2749 -1*X_SEQUENCE_NUMBER,
2750 X_OPERATION,
2751 X_SECOND_VALUE_VARCHAR2,
2752 X_SECOND_VALUE_NUMBER,
2753 X_SECOND_VALUE_DATE,
2754 X_LAST_UPDATED_BY,
2755 X_LAST_UPDATE_DATE,
2756 X_LAST_UPDATED_BY,
2757 X_LAST_UPDATE_DATE,
2758 X_LAST_UPDATE_LOGIN,
2759 X_START_DATE_ACTIVE,
2760 X_END_DATE_ACTIVE
2761 );
2762
2763 ELSE
2764
2765 UPDATE
2766 AK_CRITERIA
2767 SET
2768 VALUE_VARCHAR2 = X_SECOND_VALUE_VARCHAR2,
2769 VALUE_NUMBER = X_SECOND_VALUE_NUMBER,
2770 VALUE_DATE = X_SECOND_VALUE_DATE
2771 WHERE
2772 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2773 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2774 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2775 AND REGION_CODE = X_REGION_CODE
2776 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2777 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2778 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2779
2780 END IF;
2781
2782 EXCEPTION
2783 WHEN OTHERS THEN NULL;
2784 END;
2785
2786 ELSE
2787
2788 --We may need to delete a row
2789 BEGIN
2790
2791 IF (l_count > 0) THEN
2792
2793 DELETE FROM
2794 AK_CRITERIA
2795 WHERE
2796 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2797 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2798 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2799 AND REGION_CODE = X_REGION_CODE
2800 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2801 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2802 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2803
2804 END IF;
2805
2806 EXCEPTION
2807 WHEN OTHERS THEN NULL;
2808 END;
2809
2810 END IF;
2811 -- Matching
2812 UPDATE
2813 EGO_CRITERIA_EXT
2814 SET
2815 USE_KEYWORD_SEARCH = X_USE_KEYWORD_SEARCH,
2816 MATCH_CONDITION = X_MATCH_CONDITION,
2817 FUZZY = X_FUZZY,
2818 STEMMING = X_STEMMING,
2819 SYNONYMS = X_SYNONYMS
2820 WHERE
2821 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID AND
2822 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE AND
2823 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID AND
2824 REGION_CODE = X_REGION_CODE AND
2825 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID AND
2826 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE AND
2827 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
2828 -- Matching
2829 IF(SQL%ROWCOUNT = 0)
2830 THEN
2831 INSERT INTO EGO_CRITERIA_EXT
2832 (
2833 CUSTOMIZATION_APPLICATION_ID,
2834 CUSTOMIZATION_CODE,
2835 REGION_APPLICATION_ID,
2836 REGION_CODE,
2837 ATTRIBUTE_APPLICATION_ID,
2838 ATTRIBUTE_CODE,
2839 SEQUENCE_NUMBER,
2840 USE_KEYWORD_SEARCH,
2841 MATCH_CONDITION,
2842 FUZZY,
2843 STEMMING,
2844 SYNONYMS,
2845 CREATED_BY,
2846 CREATION_DATE,
2847 LAST_UPDATED_BY,
2848 LAST_UPDATED_DATE,
2849 LAST_UPDATE_LOGIN
2850 )
2851 VALUES
2852 (
2853 X_CUSTOMIZATION_APPLICATION_ID,
2854 X_CUSTOMIZATION_CODE,
2855 X_REGION_APPLICATION_ID,
2856 X_REGION_CODE,
2857 X_ATTRIBUTE_APPLICATION_ID,
2858 X_ATTRIBUTE_CODE,
2859 X_SEQUENCE_NUMBER,
2860 X_USE_KEYWORD_SEARCH,
2861 X_MATCH_CONDITION,
2862 X_FUZZY,
2863 X_STEMMING,
2864 X_SYNONYMS,
2865 X_LAST_UPDATED_BY, -- X_CREATED_BY,
2866 X_LAST_UPDATE_DATE, -- X_CREATION_DATE,
2867 X_LAST_UPDATED_BY,
2868 X_LAST_UPDATE_DATE,
2869 X_LAST_UPDATE_LOGIN
2870 );
2871 END IF;
2872 END update_criterion;
2873
2874 PROCEDURE delete_criterion
2875 (
2876 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2877 X_CUSTOMIZATION_CODE IN VARCHAR2,
2878 X_REGION_APPLICATION_ID IN NUMBER,
2879 X_REGION_CODE IN VARCHAR2,
2880 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2881 X_ATTRIBUTE_CODE IN VARCHAR2,
2882 X_SEQUENCE_NUMBER IN NUMBER,
2883 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2884 )
2885 IS
2886
2887 l_count NUMBER;
2888
2889 BEGIN
2890
2891 IF FND_API.To_Boolean(x_init_msg_list) THEN
2892 FND_MSG_PUB.Initialize;
2893 END IF;
2894
2895 AK_CRITERIA_PKG.delete_row
2896 (
2897 X_CUSTOMIZATION_APPLICATION_ID,
2898 X_CUSTOMIZATION_CODE,
2899 X_REGION_APPLICATION_ID,
2900 X_REGION_CODE,
2901 X_ATTRIBUTE_APPLICATION_ID,
2902 X_ATTRIBUTE_CODE,
2903 X_SEQUENCE_NUMBER
2904 );
2905
2906 SELECT
2907 COUNT(*) INTO l_count
2908 FROM
2909 AK_CRITERIA
2910 WHERE
2911 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
2912 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
2913 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
2914 AND REGION_CODE = X_REGION_CODE
2915 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
2916 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE
2917 AND SEQUENCE_NUMBER = -1*X_SEQUENCE_NUMBER;
2918
2919 IF (l_count > 0) THEN
2920 AK_CRITERIA_PKG.delete_row
2921 (
2922 X_CUSTOMIZATION_APPLICATION_ID,
2923 X_CUSTOMIZATION_CODE,
2924 X_REGION_APPLICATION_ID,
2925 X_REGION_CODE,
2926 X_ATTRIBUTE_APPLICATION_ID,
2927 X_ATTRIBUTE_CODE,
2928 -1*X_SEQUENCE_NUMBER
2929 );
2930
2931 END IF;
2932 -- Matching
2933 DELETE
2934 EGO_CRITERIA_EXT
2935 WHERE
2936 CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID AND
2937 CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE AND
2938 REGION_APPLICATION_ID = X_REGION_APPLICATION_ID AND
2939 REGION_CODE = X_REGION_CODE AND
2940 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID AND
2941 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE AND
2942 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
2943 -- Matching
2944 -- No issues even if the row is not there
2945 END delete_criterion;
2946
2947 PROCEDURE create_result_section
2948 (
2949 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
2950 X_CUSTOMIZATION_CODE IN VARCHAR2,
2951 X_REGION_APPLICATION_ID IN NUMBER,
2952 X_REGION_CODE IN VARCHAR2,
2953 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
2954 X_ATTRIBUTE_CODE IN VARCHAR2,
2955 X_DISPLAY_SEQUENCE IN NUMBER,
2956 X_CREATED_BY IN NUMBER,
2957 X_CREATION_DATE IN DATE,
2958 X_LAST_UPDATED_BY IN NUMBER,
2959 X_LAST_UPDATE_DATE IN DATE,
2960 X_LAST_UPDATE_LOGIN IN NUMBER,
2961 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2962 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2963 X_ERRORCODE OUT NOCOPY NUMBER
2964 )
2965 IS
2966
2967 l_Sysdate DATE := Sysdate;
2968 l_rowid VARCHAR2(255);
2969
2970 BEGIN
2971
2972 IF FND_API.To_Boolean(x_init_msg_list) THEN
2973 FND_MSG_PUB.Initialize;
2974 END IF;
2975
2976 AK_CUSTOM_REGION_ITEMS_PKG.INSERT_ROW(
2977 X_ROWID => l_rowid,
2978 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
2979 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
2980 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
2981 X_REGION_CODE => X_REGION_CODE,
2982 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
2983 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
2984 X_PROPERTY_NAME => 'SECTION_SEQUENCE',
2985 X_PROPERTY_VARCHAR2_VALUE => NULL,
2986 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
2987 X_PROPERTY_DATE_VALUE => NULL,
2988 X_CREATED_BY => X_CREATED_BY,
2989 X_CREATION_DATE => X_CREATION_DATE,
2990 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
2991 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
2992 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
2993 );
2994
2995 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2996 X_RETURN_STATUS := 'T';
2997
2998
2999 END create_result_section;
3000
3001
3002 ---------------------------------------------------------
3003
3004
3005 PROCEDURE update_result_section
3006 (
3007 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
3008 X_CUSTOMIZATION_CODE IN VARCHAR2,
3009 X_REGION_APPLICATION_ID IN NUMBER,
3010 X_REGION_CODE IN VARCHAR2,
3011 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
3012 X_ATTRIBUTE_CODE IN VARCHAR2,
3013 X_DISPLAY_SEQUENCE IN NUMBER,
3014 X_LAST_UPDATED_BY IN NUMBER,
3015 X_LAST_UPDATE_DATE IN DATE,
3016 X_LAST_UPDATE_LOGIN IN NUMBER,
3017 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
3018 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3019 X_ERRORCODE OUT NOCOPY NUMBER
3020 )
3021 IS
3022
3023 l_Sysdate DATE := Sysdate;
3024 l_rowid VARCHAR2(255);
3025
3026 BEGIN
3027
3028 IF FND_API.To_Boolean(x_init_msg_list) THEN
3029 FND_MSG_PUB.Initialize;
3030 END IF;
3031
3032 AK_CUSTOM_REGION_ITEMS_PKG.UPDATE_ROW(
3033 X_CUSTOMIZATION_APPLICATION_ID => X_CUSTOMIZATION_APPLICATION_ID,
3034 X_CUSTOMIZATION_CODE => X_CUSTOMIZATION_CODE,
3035 X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
3036 X_REGION_CODE => X_REGION_CODE,
3037 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
3038 X_ATTRIBUTE_CODE => X_ATTRIBUTE_CODE,
3039 X_PROPERTY_NAME => 'SECTION_SEQUENCE',
3040 X_PROPERTY_VARCHAR2_VALUE => NULL,
3041 X_PROPERTY_NUMBER_VALUE => X_DISPLAY_SEQUENCE,
3042 X_PROPERTY_DATE_VALUE => NULL,
3043 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
3044 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
3045 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
3046 );
3047
3048 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3049 X_RETURN_STATUS := 'T';
3050
3051
3052 END update_result_section;
3053
3054 -------------------------------------------------------------
3055
3056 PROCEDURE delete_result_section
3057 (
3058 X_CUSTOMIZATION_APPLICATION_ID IN NUMBER,
3059 X_CUSTOMIZATION_CODE IN VARCHAR2,
3060 X_REGION_APPLICATION_ID IN NUMBER,
3061 X_REGION_CODE IN VARCHAR2,
3062 X_ATTRIBUTE_APPLICATION_ID IN NUMBER,
3063 X_ATTRIBUTE_CODE IN VARCHAR2,
3064 X_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
3065 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3066 X_ERRORCODE OUT NOCOPY NUMBER
3067 )
3068 IS
3069
3070 BEGIN
3071
3072 IF FND_API.To_Boolean(x_init_msg_list) THEN
3073 FND_MSG_PUB.Initialize;
3074 END IF;
3075
3076 DELETE FROM AK_CUSTOM_REGION_ITEMS
3077 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
3078 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
3079 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
3080 AND REGION_CODE = X_REGION_CODE
3081 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
3082 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
3083
3084 DELETE FROM AK_CUSTOM_REGION_ITEMS_TL
3085 WHERE CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
3086 AND CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
3087 AND REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
3088 AND REGION_CODE = X_REGION_CODE
3089 AND ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID
3090 AND ATTRIBUTE_CODE = X_ATTRIBUTE_CODE;
3091
3092 -- X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3093 X_RETURN_STATUS := 'T';
3094
3095
3096 END delete_result_section;
3097 ---------------------------------------------------------
3098
3099 END EGO_SEARCH_FWK_PUB;