[Home] [Help]
PACKAGE BODY: APPS.IEU_WORK_ACTION_PVT
Source
1 PACKAGE BODY IEU_WORK_ACTION_PVT AS
2 /* $Header: IEUWACB.pls 120.1 2007/12/17 11:41:58 svidiyal ship $ */
3
4
5 PROCEDURE Node_Mapping( x_return_status OUT NOCOPY VARCHAR2,
6 x_msg_count OUT NOCOPY NUMBER,
7 x_msg_data OUT NOCOPY VARCHAR2,
8 p_enum_id IN NUMBER,
9 p_mapping_application IN NUMBER,
10 p_param_set_id IN IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type,
11 p_maction_def_type_flag IN VARCHAR2,
12 p_act_application IN NUMBER
13 ) as
14
15 l_language VARCHAR2(4);
16 l_source_lang VARCHAR2(4);
17 l_return_status VARCHAR2(4);
18 l_msg_count NUMBER(2);
19 l_msg_data VARCHAR2(2000);
20 act_map_obj SYSTEM.IEU_wp_action_maps_OBJ;
21 act_map_obj1 SYSTEM.IEU_wp_action_maps_OBJ;
22 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
23 l_temp_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
24 l_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
25 l_panel_sec_cat_code IEU_WP_ACTION_MAPS.PANEL_SEC_CAT_CODE%type;
26 l_section_id IEU_WP_NODE_SECTION_MAPS.SECTION_ID%type;
27 l_section_map_sequence IEU_WP_NODE_SECTION_MAPS.SECTION_MAP_SEQUENCE%type;
28 l_action_map_type_code IEU_WP_ACTION_MAPS.ACTION_MAP_TYPE_CODE%type;
29 l_count NUMBER(5);
30 l_duplicate NUMBER(5);
31 l_wp_node_section_map_id IEU_WP_NODE_SECTION_MAPS.WP_NODE_SECTION_MAP_ID%type;
32 name_fail_exception EXCEPTION;
33
34 BEGIN
35
36 fnd_msg_pub.delete_msg();
37 x_return_status := fnd_api.g_ret_sts_success;
38 FND_MSG_PUB.initialize;
39 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
40 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
41 x_msg_data := '';
42
43
44
45 select enum_type_uuid into l_enum_uuid
46 from ieu_uwq_sel_enumerators
47 where sel_enum_id = p_enum_id;
48
49 -- check if this node already have a action with same label
50 SELECT count(*) INTO l_duplicate
51 FROM ieu_wp_action_maps
52 where action_param_set_id = p_param_set_id
53 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
54
55 --and application_id = p_mapping_application
56 /*********************************************************************************/
57 AND responsibility_id = -1
58 AND action_map_code = l_enum_uuid;
59
60 IF l_duplicate > 0 then
61 RAISE name_fail_exception;
62 else
63 select max(m.action_map_sequence) into l_temp_map_sequence
64 from ieu_wp_action_maps m,
65 ieu_uwq_maction_defs_b db,
66 ieu_wp_act_param_sets_b sb
67 -- where m.application_id = p_mapping_application
68 where m.action_map_type_code = 'NODE'
69 and m.action_map_code = l_enum_uuid
70 -- and m.application_id = db.application_id
71 and db.maction_def_type_flag = p_maction_def_type_flag
72 and db.maction_def_id = sb.wp_action_def_id
73 and sb.action_param_set_id = m.action_param_set_id
74 and m.responsibility_id = -1;
75
76
77
78 if (l_temp_map_sequence IS NULL) then
79 l_map_sequence := 1;
80 else
81 l_map_sequence := l_temp_map_sequence +1;
82 end if;
83
84 if (upper(p_maction_def_type_flag) ='W') then
85 l_section_id := 10002;
86 l_section_map_sequence := 2;
87 l_panel_sec_cat_code := null;
88 l_action_map_type_code := 'NODE';
89
90 elsif (upper(p_maction_def_type_flag) ='I') then
91 l_section_id := 10001;
92 l_section_map_sequence := 1;
93 l_panel_sec_cat_code := 'NOTES';
94 l_action_map_type_code := 'NODE';
95 elsif (upper(p_maction_def_type_flag) ='G') then
96 l_panel_sec_cat_code := null;
97 l_action_map_type_code := 'NODE';
98 elsif (upper(p_maction_def_type_flag) ='F') then
99 l_panel_sec_cat_code := null;
100 end if;
101
102
103
104 act_map_obj := SYSTEM.IEU_wp_action_maps_OBJ(null, p_param_set_id,
105 p_mapping_application, null, 'NODE',
106 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code,
107 'N', 'Y');
108
109 CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj);
110
111
112 act_map_obj1 := SYSTEM.IEU_wp_action_maps_OBJ(null, p_param_set_id,
113 p_mapping_application, -1, 'NODE',
114 l_enum_uuid, l_map_sequence, l_panel_sec_cat_code,
115 'N', 'Y');
116
117 CREATE_action_map(x_return_status,x_msg_count, x_msg_data, act_map_obj1);
118
119 update IEU_UWQ_SEL_ENUMERATORS set
120 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
121 LAST_UPDATE_DATE = SYSDATE,
122 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
123 WORK_PANEL_REGISTERED_FLAG = 'Y'
124 where SEL_ENUM_ID = p_enum_id;
125
126 if (upper(p_maction_def_type_flag) <> 'G' ) then
127
128 select count(*) into l_count
129 from IEU_WP_NODE_SECTION_MAPS
130 where ENUM_TYPE_UUID = l_enum_uuid
131 and APPLICATION_ID = p_mapping_application
132 AND SECTION_ID = l_section_id;
133
134 if (l_count > 0) then
135
136 update IEU_WP_NODE_SECTION_MAPS set
137 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
138 LAST_UPDATE_DATE = SYSDATE,
139 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
140 RESPONSIBILITY_ID = null,
141 SECTION_MAP_SEQUENCE = l_section_map_sequence
142 where ENUM_TYPE_UUID = l_enum_uuid
143 and APPLICATION_ID = p_mapping_application
144 and SECTION_ID = l_section_id;
145
146
147 else
148
149 select IEU_WP_NODE_SECTION_MAPS_S1.nextval into l_wp_node_section_map_id from sys.dual;
150
151 insert INTO IEU_WP_NODE_SECTION_MAPS
152 (WP_NODE_SECTION_MAP_ID,
153 OBJECT_VERSION_NUMBER,
154 CREATED_BY,
155 CREATION_DATE,
156 LAST_UPDATED_BY,
157 LAST_UPDATE_DATE,
158 LAST_UPDATE_LOGIN,
159 SECURITY_GROUP_ID,
160 APPLICATION_ID,
161 RESPONSIBILITY_ID,
162 ENUM_TYPE_UUID,
163 SECTION_ID,
164 SECTION_MAP_SEQUENCE,
165 NOT_VALID_FLAG
166 ) values
167 (l_wp_node_section_map_id,
168 0,
169 FND_GLOBAL.USER_ID,
170 SYSDATE,
171 FND_GLOBAL.USER_ID,
172 SYSDATE,
173 FND_GLOBAL.LOGIN_ID,
174 NULL,
175 p_mapping_application,
176 null,
177 l_enum_uuid,
178 l_section_id,
179 l_section_map_sequence,
180 null);
181
182 end if;
183 end if;
184
185
186 x_return_status := fnd_api.g_ret_sts_success;
187 COMMIT;
188 END if;
189
190 EXCEPTION
191 WHEN name_fail_exception THEN
192 x_return_status := fnd_api.g_ret_sts_unexp_error;
193
194 fnd_message.set_name ('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
195 fnd_msg_pub.add;
196 x_return_status := fnd_api.g_ret_sts_unexp_error;
197 fnd_msg_pub.count_and_get (
198 p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201
202 WHEN fnd_api.g_exc_error THEN
203 --dbms_outPUT.PUT_LINE('Error : '||sqlerrm);
204
205 ROLLBACK;
206 x_return_status := fnd_api.g_ret_sts_error;
207
208 WHEN fnd_api.g_exc_unexpected_error THEN
209 --dbms_outPUT.PUT_LINE('unexpected Error : '||sqlerrm);
210
211 ROLLBACK;
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213
214 WHEN OTHERS THEN
215 --dbms_outPUT.PUT_LINE('other Error : '||sqlerrm);
216
217 ROLLBACK;
218 x_return_status := fnd_api.g_ret_sts_unexp_error;
219
220
221 END Node_Mapping;
222
223 PROCEDURE Validate_Action_Label( x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 p_label IN VARCHAR2,
227 p_param_set_id IN NUMBER
228 )
229 AS
230
231 l_language VARCHAR2(4);
232 l_act_usr_lbl_count NUMBER(10);
233 l_temp_count NUMBER;
234 l_msg_count NUMBER(10);
235 l_msg_data VARCHAR2(2000);
236 temp_act_user_label IEU_UWQ_MACTION_DEFS_TL.action_user_label%type;
237 l_set_id_count NUMBER(10);
238
239
240 BEGIN
241
242
243 fnd_msg_pub.delete_msg();
244 x_return_status := fnd_api.g_ret_sts_success;
245 FND_MSG_PUB.initialize;
246
247 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
248 l_set_id_count :=0;
249 temp_act_user_label := LTRIM(RTRIM(p_label));
250 select count(b.maction_def_id) into l_act_usr_lbl_count
251 from ieu_uwq_maction_defs_b b, ieu_uwq_maction_defs_tl tl,
252 ieu_wp_act_param_sets_b s
253 where
254 b.maction_def_id = tl.maction_def_id
255 and b.maction_def_type_flag = (select maction_def_type_flag
256 from ieu_uwq_maction_defs_b
257 where maction_def_id = (select wp_action_def_id
258 from ieu_wp_act_param_sets_b
259 where action_param_set_id = p_param_set_id))
260 and s.wp_action_def_id = b.maction_def_id
261 and tl.language = l_language
262 and lower(tl.action_user_label) = lower(temp_act_user_label);
263
264 select count(b.maction_def_id) into l_set_id_count
265 from ieu_uwq_maction_defs_b b,
266 ieu_wp_act_param_sets_b s,
267 ieu_wp_act_param_sets_tl stl
268 where
269 b.maction_def_type_flag = (select maction_def_type_flag
270 from ieu_uwq_maction_defs_b
271 where maction_def_id = (select wp_action_def_id
272 from ieu_wp_act_param_sets_b
273 where action_param_set_id = p_param_set_id))
274 and s.wp_action_def_id = b.maction_def_id
275
276 and s.action_param_set_id = stl.action_param_set_id
277 and stl.language = l_language
278 and lower(stl.action_param_set_label) = lower(temp_act_user_label);
279
280
281 --DBMS_OUTPUT.PUT_LINE(' set lable count : '|| l_set_id_count);
282
283 if (l_act_usr_lbl_count <> 0 or l_set_id_count <> 0) then
284 FND_MESSAGE.set_name('IEU', 'IEU_PROV_WP_LABLE_UNIQUE');
285 FND_MSG_PUB.Add;
286 x_return_status := FND_API.G_RET_STS_ERROR;
287 end if;
288
289 -- x_return_status := fnd_api.g_ret_sts_success;
290
291 x_msg_count := fnd_msg_pub.COUNT_MSG();
292
293 FOR i in 1..x_msg_count LOOP
294 l_msg_data := '';
295 l_msg_count := 0;
296 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
297 x_msg_data := x_msg_data || ',' || l_msg_data;
298 END LOOP;
299
300 EXCEPTION
301
302 WHEN FND_API.G_EXC_ERROR THEN
303
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 x_msg_count := fnd_msg_pub.COUNT_MSG();
306
307 FOR i in 1..x_msg_count LOOP
308 l_msg_data := '';
309 l_msg_count := 0;
310 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
311 x_msg_data := x_msg_data || ',' || l_msg_data;
312 END LOOP;
313
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 x_msg_count := fnd_msg_pub.COUNT_MSG();
317
318 FOR i in 1..x_msg_count LOOP
319 l_msg_data := '';
320 l_msg_count := 0;
321 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
322 x_msg_data := x_msg_data || ',' || l_msg_data;
323 END LOOP;
324 WHEN OTHERS THEN
325 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 x_msg_count := fnd_msg_pub.COUNT_MSG();
328
329 FOR i in 1..x_msg_count LOOP
330 l_msg_data := '';
331 l_msg_count := 0;
332 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
333 x_msg_data := x_msg_data || ',' || l_msg_data;
334 END LOOP;
335
336 END Validate_Action_Label;
337
338 --===================================================================
339 -- NAME
340 -- CREATE_action_map
341 --
342 -- PURPOSE
343 -- Private api to create action map
344 --
345 -- NOTES
346 -- 1. UWQ Admin will use this procedure to create action map
347 --
348 --
349 -- HISTORY
350 -- 8-may-2002 dolee Created
351
352 --===================================================================
353
354
355 PROCEDURE CREATE_action_map (x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358 rec_obj IN SYSTEM.IEU_WP_ACTION_MAPS_OBJ
359 ) AS
360
361 l_action_map_id NUMBER(15);
362 BEGIN
363
364 select IEU_wp_action_maps_S1.NEXTVAL into l_action_map_id from sys.dual;
365
366 insert INTO IEU_wp_action_mapS
367 (WP_ACTION_MAP_ID,
368 OBJECT_VERSION_NUMBER,
369 CREATED_BY,
370 CREATION_DATE,
371 LAST_UPDATED_BY,
372 LAST_UPDATE_DATE,
373 LAST_UPDATE_LOGIN,
374 ACTION_PARAM_SET_ID,
375 APPLICATION_ID,
376 RESPONSIBILITY_ID,
377 ACTION_MAP_TYPE_CODE,
378 ACTION_MAP_CODE,
379 ACTION_MAP_SEQUENCE,
380 PANEL_SEC_CAT_CODE,
381 NOT_VALID_FLAG,
382 DEV_DATA_FLAG
383 )
384 values (
385 l_action_map_id,
386 1,
387 FND_GLOBAL.USER_ID,
388 SYSDATE,
389 FND_GLOBAL.USER_ID,
390 SYSDATE,
391 FND_GLOBAL.LOGIN_ID,
392 rec_obj.action_param_set_id,
393 rec_obj.application_id,
394 rec_obj.responsibility_id,
395 rec_obj.action_map_type_code,
396 rec_obj.action_map_code,
397 rec_obj.action_map_sequence,
398 rec_obj.panel_sec_cat_code,
399 rec_obj.not_valid_flag,
400 rec_obj.dev_data_flag
401 );
402
403 COMMIT;
404
405 EXCEPTION
406 WHEN fnd_api.g_exc_error THEN
407 ROLLBACK;
408 x_return_status := fnd_api.g_ret_sts_error;
409
410 WHEN fnd_api.g_exc_unexpected_error THEN
411 ROLLBACK;
412 x_return_status := fnd_api.g_ret_sts_unexp_error;
413
414 WHEN OTHERS THEN
415 ROLLBACK;
416 x_return_status := fnd_api.g_ret_sts_unexp_error;
417
418 END CREATE_action_map;
419
420 PROCEDURE Create_Work_Action (x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_count OUT NOCOPY NUMBER,
422 x_msg_data OUT NOCOPY VARCHAR2,
423 rec_obj IN SYSTEM.IEU_WP_MACT_OBJ,
424 p_maction_def_type_flag IN VARCHAR2)
425 AS
426
427 l_language VARCHAR2(4);
428 l_source_lang VARCHAR2(4);
429 l_return_status VARCHAR2(4);
430 l_msg_count NUMBER(2);
431 l_msg_data VARCHAR2(2000);
432 l_maction_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%TYPE;
433 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%TYPE;
434 l_enum_uuid IEU_UWQ_SEL_ENUMERATORS.ENUM_TYPE_UUID%type;
435 l_temp_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
436 l_map_sequence IEU_WP_ACTION_MAPS.action_map_sequence%type;
437 l_wp_node_section_map_id IEU_WP_NODE_SECTION_MAPS.WP_NODE_SECTION_MAP_ID%type;
438 act_map_obj SYSTEM.IEU_wp_action_maps_OBJ;
439 act_map_obj1 SYSTEM.IEU_wp_action_maps_OBJ;
440 l_count NUMBER(5);
441 l_section_id IEU_WP_NODE_SECTION_MAPS.SECTION_ID%type;
442 l_section_map_sequence IEU_WP_NODE_SECTION_MAPS.SECTION_MAP_SEQUENCE%type;
443 l_panel_sec_cat_code IEU_WP_ACTION_MAPS.PANEL_SEC_CAT_CODE%type;
444 l_action_map_type_code IEU_WP_ACTION_MAPS.ACTION_MAP_TYPE_CODE%type;
445 BEGIN
446 fnd_msg_pub.delete_msg();
447 x_return_status := fnd_api.g_ret_sts_success;
448 FND_MSG_PUB.initialize;
449 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
450 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
451 x_msg_data := '';
452 l_count := 0;
453 l_section_id := 0;
454 l_section_map_sequence := 0;
455
456 IEU_WorkPanel_PVT.Validate_Action ( l_return_status,
457 l_msg_count,
458 l_msg_data,
459 rec_obj, 'Y', p_maction_def_type_flag, null);
460
461 if (l_return_status = 'S') then
462 select IEU_UWQ_MACTION_DEFS_B_S1.NEXTVAL into l_maction_def_id from sys.dual;
463 if ( p_maction_def_type_flag = 'M' ) then
464 insert INTO IEU_UWQ_MACTION_DEFS_B
465 (MACTION_DEF_ID,
466 CREATED_BY,
467 CREATION_DATE,
468 LAST_UPDATED_BY,
469 LAST_UPDATE_DATE,
470 LAST_UPDATE_LOGIN,
471 ACTION_PROC,
472 APPLICATION_ID,
473 MACTION_DEF_TYPE_FLAG,
474 MACTION_DEF_KEY,
475 OBJECT_VERSION_NUMBER,
476 MULTI_SELECT_FLAG
477 )
478 values(
479 l_maction_def_id,
480 FND_GLOBAL.USER_ID,
481 SYSDATE,
482 FND_GLOBAL.USER_ID,
483 SYSDATE,
484 FND_GLOBAL.LOGIN_ID,
485 LTRIM(RTRIM(rec_obj.action_proc)),
486 rec_obj.application_id,
487 null,
488 LTRIM(RTRIM(rec_obj.maction_def_key)),
489 0,
490 rec_obj.multi_select_flag
491 );
492 else
493 insert INTO IEU_UWQ_MACTION_DEFS_B
494 (MACTION_DEF_ID,
495 CREATED_BY,
496 CREATION_DATE,
497 LAST_UPDATED_BY,
498 LAST_UPDATE_DATE,
499 LAST_UPDATE_LOGIN,
500 ACTION_PROC,
501 APPLICATION_ID,
502 MACTION_DEF_TYPE_FLAG,
503 MACTION_DEF_KEY,
504 OBJECT_VERSION_NUMBER,
505 MULTI_SELECT_FLAG
506 )
507 values(
508 l_maction_def_id,
509 FND_GLOBAL.USER_ID,
510 SYSDATE,
511 FND_GLOBAL.USER_ID,
512 SYSDATE,
513 FND_GLOBAL.LOGIN_ID,
514 LTRIM(RTRIM(rec_obj.action_proc)),
515 rec_obj.application_id,
516 p_maction_def_type_flag,
517 LTRIM(RTRIM(rec_obj.maction_def_key)),
518 0,
519 rec_obj.multi_select_flag
520 );
521 end if ;
522
523 insert INTO IEU_UWQ_MACTION_DEFS_TL
524 (MACTION_DEF_ID,
525 LANGUAGE,
526 CREATED_BY,
527 CREATION_DATE,
528 LAST_UPDATED_BY,
529 LAST_UPDATE_DATE,
530 LAST_UPDATE_LOGIN,
531 ACTION_USER_LABEL,
532 SOURCE_LANG,
533 ACTION_DESCRIPTION,
534 OBJECT_VERSION_NUMBER
535 ) values (
536 l_maction_def_id,
537 l_language,
538 FND_GLOBAL.USER_ID,
539 SYSDATE,
540 FND_GLOBAL.USER_ID,
541 SYSDATE,
542 FND_GLOBAL.LOGIN_ID,
543 LTRIM(RTRIM(rec_obj.action_user_label)),
544 l_source_lang,
545 LTRIM(RTRIM(rec_obj.action_description)),
546 0
547 );
548
549 if (p_maction_def_type_flag <> 'N' and p_maction_def_type_flag <> 'M') then
550 select IEU_WP_ACT_PARAM_SETS_B_S1.NEXTVAL into l_action_param_set_id from sys.dual;
551 insert INTO IEU_WP_ACT_PARAM_SETS_B
552 (ACTION_PARAM_SET_ID,
553 CREATED_BY,
554 CREATION_DATE,
555 LAST_UPDATED_BY,
556 LAST_UPDATE_DATE,
557 LAST_UPDATE_LOGIN,
558 WP_ACTION_DEF_ID,
559 OBJECT_VERSION_NUMBER
560 ) values (
561 l_action_param_set_id,
562 FND_GLOBAL.USER_ID,
563 SYSDATE,
564 FND_GLOBAL.USER_ID,
565 SYSDATE,
566 FND_GLOBAL.LOGIN_ID,
567 l_maction_def_id,
568 0
569 );
570 insert INTO IEU_WP_ACT_PARAM_SETS_TL
571 (ACTION_PARAM_SET_ID,
572 CREATED_BY,
573 CREATION_DATE,
574 LAST_UPDATED_BY,
575 LAST_UPDATE_DATE,
576 LAST_UPDATE_LOGIN,
577 ACTION_PARAM_SET_LABEL,
578 LANGUAGE,
579 SOURCE_LANG,
580 ACTION_PARAM_SET_DESC,
581 OBJECT_VERSION_NUMBER
582 ) values (
583 l_action_param_set_id,
584 FND_GLOBAL.USER_ID,
585 SYSDATE,
586 FND_GLOBAL.USER_ID,
587 SYSDATE,
588 FND_GLOBAL.LOGIN_ID,
589 LTRIM(RTRIM(rec_obj.action_user_label)),
590 l_language,
591 l_source_lang,
592 LTRIM(RTRIM(rec_obj.action_description)),
593 0
594 );
595 end if;
596 if (p_maction_def_type_flag <> 'F') then
597
598 select max(m.action_map_sequence) into l_temp_map_sequence
599 from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
600 ieu_wp_act_param_sets_b sb
601 --where m.application_id = rec_obj.application_id
602 where m.action_map_type_code = 'NODE'
603 --and m.application_id = db.application_id
604 and db.maction_def_type_flag = p_maction_def_type_flag
605 and db.maction_def_id = sb.wp_action_def_id
606 and sb.action_param_set_id = m.action_param_set_id
607 and m.responsibility_id = -1;
608
609 if (l_temp_map_sequence IS NULL) then
610 l_map_sequence := 1;
611 else
612 l_map_sequence := l_temp_map_sequence +1;
613 end if;
614
615 end if;
616 /*
617 if (p_maction_def_type_flag ='W') then
618 l_section_id := 10002;
619 l_section_map_sequence := 2;
620 l_panel_sec_cat_code := null;
621 l_action_map_type_code := 'NODE';
622 elsif (p_maction_def_type_flag ='I') then
623 l_section_id := 10001;
624 l_section_map_sequence := 1;
625 l_panel_sec_cat_code := 'NOTES';
626 l_action_map_type_code := 'NODE';
627 elsif (p_maction_def_type_flag ='G') then
628 l_panel_sec_cat_code := null;
629 l_action_map_type_code := 'NODE';
630 elsif (p_maction_def_type_flag = 'F') then
631 l_action_map_type_code := 'NODE_DS';
632 l_map_sequence := 1;
633 l_panel_sec_cat_code := null;
634 end if;
635 */
636 if (p_maction_def_type_flag <> 'F') then
637 update IEU_UWQ_SEL_ENUMERATORS set
638 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
639 LAST_UPDATE_DATE = SYSDATE,
640 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
641 WORK_PANEL_REGISTERED_FLAG = 'Y'
642 where SEL_ENUM_ID = rec_obj.enum_id;
643
644 end if;
645
646
647 x_return_status := fnd_api.g_ret_sts_success;
648 else
649 x_return_status := l_return_status;
650 x_msg_count := l_msg_count;
651 x_msg_data := l_msg_data;
652 end if;
653
654
655 COMMIT;
656
657 EXCEPTION
658 WHEN fnd_api.g_exc_error THEN
659 ROLLBACK;
660 x_return_status := fnd_api.g_ret_sts_error;
661
662 WHEN fnd_api.g_exc_unexpected_error THEN
663 ROLLBACK;
664 x_return_status := fnd_api.g_ret_sts_unexp_error;
665
666 WHEN OTHERS THEN
667 ROLLBACK;
668 x_return_status := fnd_api.g_ret_sts_unexp_error;
669
670 END Create_Work_Action;
671 -- ===============================================================
672 -- Start of Comments
673 -- ===============================================================
674 -- API Name
675 -- CreateFromAction
676 -- Type
677 -- Private
678 -- Pre-Req
679 --
680 -- Parameters
681 --
682 -- IN
683 --
684 -- r_wp_action_key VARCHAR2
685 --
686 -- OUT
687 -- x_return_status OUT VARCHAR2
688 -- x_msg_count OUT NUMBER
689 -- x_msg_data OUT VARCHAR2
690 --
691 -- End of Comments
692 -- ===============================================================
693 PROCEDURE CreateFromAction( x_return_status OUT NOCOPY VARCHAR2,
694 x_msg_count OUT NOCOPY NUMBER,
695 x_msg_data OUT NOCOPY VARCHAR2,
696 r_maction_def_id IN NUMBER,
697 r_language IN VARCHAR2,
698 r_label IN VARCHAR2,
699 r_desc IN VARCHAR2,
700 r_param_set_id IN NUMBER)
701 AS
702
703 l_wp_maction_def_id NUMBER(15);
704 l_param_set_id NUMBER(15);
705 l_language VARCHAR2(4);
706 l_source_lang VARCHAR2(4);
707 l_msg_count NUMBER(2);
708 l_msg_data VARCHAR2(2000);
709 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
710 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
711 v_cursor1 NUMBER;
712 sql_stmt varchar2(2000);
713 sql_stmt1 varchar2(2000);
714 l_param_id NUMBER(15);
715 l_property_id NUMBER(15);
716 l_property_value varchar(4000);
717 l_not_valid_flag varchar(5);
718 l_value_override_flag varchar(5);
719 v_numrows1 NUMBER;
720 l_new_param_set_id NUMBER(15);
721 l_wp_action_map_id NUMBER(15);
722 l_temp_map_sequence ieu_wp_action_maps.action_map_sequence%type;
723
724
725 cursor c_cur is
726 SELECT
727 PARAM_ID, PROPERTY_ID,property_value, value_override_flag,not_valid_flag
728 FROM ieu_wp_param_props_b
729 WHERE action_param_set_id in
730 (select a.action_param_set_id
731 from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
732 where a.action_param_set_id = b.action_param_set_id(+)
733 and b.action_param_set_id = r_param_set_id
734 and c.maction_def_id = r_maction_def_id
735 and b.language = r_language
736 and c.maction_def_id = a.wp_action_def_id
737 );
738 BEGIN
739
740 fnd_msg_pub.delete_msg();
741 x_return_status := fnd_api.g_ret_sts_success;
742 FND_MSG_PUB.initialize;
743 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
744 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
745 x_msg_data := '';
746
747 Validate_Action_Label( x_return_status ,
748 x_msg_count ,
749 x_msg_data ,
750 r_label, r_param_set_id);
751 if x_return_status = 'S' then
752 select IEU_wp_act_param_sets_b_S1.NEXTVAL into l_new_param_set_id from sys.dual;
753
754 insert into IEU_WP_ACT_PARAM_SETS_B
755 ( ACTION_PARAM_SET_ID,
756 CREATED_BY,
757 CREATION_DATE,
758 LAST_UPDATED_BY,
759 LAST_UPDATE_DATE,
760 LAST_UPDATE_LOGIN,
761 WP_ACTION_DEF_ID,
762 OBJECT_VERSION_NUMBER)
763 values( l_new_param_set_id,
764 FND_GLOBAL.USER_ID,
765 SYSDATE,
766 FND_GLOBAL.USER_ID,
767 SYSDATE,
768 FND_GLOBAL.LOGIN_ID,
769 r_maction_def_id,
770 1);
771
772 INSERT INTO ieu_WP_ACT_PARAM_SETS_tl
773 ( ACTION_PARAM_SET_ID,
774 language,
775 created_by,
776 creation_date,
777 last_updated_by,
778 last_update_date,
779 last_update_login,
780 ACTION_PARAM_SET_LABEL,
781 ACTION_PARAM_SET_DESC,
782 source_lang,
783 OBJECT_VERSION_NUMBER)
784 values( l_new_param_set_id,
785 r_language,
786 FND_GLOBAL.USER_ID,
787 SYSDATE,
788 FND_GLOBAL.USER_ID,
789 SYSDATE,
790 FND_GLOBAL.LOGIN_ID,
791 LTRIM(RTRIM(r_label)),
792 LTRIM(RTRIM(r_desc)),
793 l_source_lang,
794 1);
795
796 FOR c_rec in c_cur LOOP
797 begin
798 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
799 insert INTO IEU_WP_PARAM_PROPS_B
800 (PARAM_PROPERTY_ID,
801 CREATED_BY,
802 CREATION_DATE,
803 LAST_UPDATED_BY,
804 LAST_UPDATE_DATE,
805 LAST_UPDATE_LOGIN,
806 ACTION_PARAM_SET_ID,
807 PARAM_ID,
808 PROPERTY_ID,
809 PROPERTY_VALUE,
810 VALUE_OVERRIDE_FLAG,
811 NOT_VALID_FLAG,
812 OBJECT_VERSION_NUMBER)
813 VALUES (l_param_property_id,
814 FND_GLOBAL.USER_ID,
815 SYSDATE,
816 FND_GLOBAL.USER_ID,
817 SYSDATE,
818 FND_GLOBAL.LOGIN_ID,
819 l_new_param_set_id,
820 c_rec.param_id,
821 c_rec.property_id,
822 c_rec.property_value,
823 c_rec.value_override_flag,
824 c_rec.not_valid_flag,
825 1
826 );
827 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
828 from ieu_wp_properties_b
829 where property_id = c_rec.property_id;
830
831 if l_trans_flag = 'Y' then
832
833 insert INTO IEU_WP_PARAM_PROPS_TL
834 (PARAM_PROPERTY_ID,
835 CREATED_BY,
836 CREATION_DATE,
837 LAST_UPDATED_BY,
838 LAST_UPDATE_DATE,
839 LAST_UPDATE_LOGIN,
840 PROPERTY_VALUE,
841 LANGUAGE,
842 SOURCE_LANG,
843 OBJECT_VERSION_NUMBER
844 ) VALUES (
845 l_param_property_id,
846 FND_GLOBAL.USER_ID,
847 SYSDATE,
848 FND_GLOBAL.USER_ID,
849 SYSDATE,
850 FND_GLOBAL.LOGIN_ID,
851 c_rec.property_value,
852 l_language,
853 l_source_lang,
854 1
855 );
856 end if;
857
858
859
860 EXCEPTION
861 WHEN fnd_api.g_exc_error THEN
862 ROLLBACK;
863 x_return_status := fnd_api.g_ret_sts_error;
864
865 WHEN fnd_api.g_exc_unexpected_error THEN
866 ROLLBACK;
867 x_return_status := fnd_api.g_ret_sts_unexp_error;
868
869 WHEN OTHERS THEN
870 ROLLBACK;
871 x_return_status := fnd_api.g_ret_sts_unexp_error;
872 end;
873
874 end loop;
875 end if ;
876 commit;
877 end CreateFromAction;
878
879
880 PROCEDURE Delete_Action_From_Node (
881 x_return_status OUT NOCOPY VARCHAR2,
882 x_msg_count OUT NOCOPY NUMBER,
883 x_msg_data OUT NOCOPY VARCHAR2,
884 x_param_set_id IN NUMBER,
885 x_node_id IN NUMBER,
886 x_maction_id IN NUMBER,
887 x_maction_def_flag IN VARCHAR2
888 ) is
889
890 l_language VARCHAR2(4);
891 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.ACTION_PARAM_SET_ID%type;
892 l_maction_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%type;
893 l_num_map_entries NUMBER;
894 l_num_set_entries NUMBER;
895 l_count_map NUMBER;
896 l_def_type_flag IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_TYPE_FLAG%type;
897 l_mact_def_id IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_ID%type;
898 l_section_id number;
899
900 BEGIN
901 x_return_status := fnd_api.g_ret_sts_success;
902 x_msg_count := 0;
903 x_msg_data := '';
904
905 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
906 -- I. get the maction_def_type_flag
907 --1. determine if this action has 1:1 for action_maps to action_param_sets
908 --2. delete from maps
909 --3. if 1:1 in 1,
910 --a. query if 1:1 between action_param_sets and maction_defs
911 --b. delete from action_param_sets and param_props
912 --c. if 1:1 in 1, delete from maction_Defs and action_params and param_defs
913
914 --I.
915 if (x_maction_id <> -1) then
916 l_def_type_flag := x_maction_def_flag;
917 else
918 SELECT db.maction_def_type_flag into l_def_type_flag
919 FROM ieu_uwq_maction_defs_b db,
920 ieu_wp_act_param_sets_b sb
921 WHERE db.maction_def_id = sb.wp_action_def_id
922 AND sb.action_param_set_id = x_param_set_id;
923 end if;
924 --1.
925 if ( l_def_type_flag <> 'F' and l_def_type_flag <> 'N' and l_def_type_flag <> 'M') then
926
927 SELECT count(unique(action_map_code))
928 INTO l_num_map_entries
929 FROM ieu_wp_action_maps
930 WHERE action_map_type_code = 'NODE' AND action_param_set_id = x_param_set_id;
931
932 elsif ( l_def_type_flag ='F') then
933
934 SELECT count(unique(action_map_code))
935 INTO l_num_map_entries
936 FROM ieu_wp_action_maps
937 WHERE action_map_type_code = 'NODE_DS' AND action_param_set_id = x_param_set_id;
938
939
940 end if;
941
942 --2.
943 if (x_node_id <> 0) then
944 if ( l_def_type_flag <> 'F') then
945
946 DELETE FROM ieu_wp_action_maps
947 WHERE action_param_set_id = x_param_set_id AND
948 action_map_type_code = 'NODE' AND
949 action_map_code IN
950 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
951 WHERE sel_enum_id = x_node_id);
952 -- algupta modified on 8/31/04, if no action/information,
953 -- delete related records in IEU_WP_NODE_SECTION_MAPS.
954 if (l_def_type_flag = 'W' or l_def_type_flag = 'I') then
955 if (l_def_type_flag = 'W') then
956 l_section_id := 10002;
957 else
958 l_section_id := 10001;
959 end if;
960
961 l_count_map := 0;
962 select count(distinct action_param_set_id) into l_count_map
963 from ieu_wp_action_maps
964 where action_map_type_code = 'NODE'
965 AND action_map_code IN
966 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
967 WHERE sel_enum_id = x_node_id)
968 and action_param_set_id in
969 (SELECT action_param_set_id
970 FROM ieu_wp_act_param_sets_b
971 WHERE wp_action_def_id in
972 (select maction_def_id
973 from ieu_uwq_maction_defs_b
974 where maction_def_type_flag = l_def_type_flag
975 ));
976 if (l_count_map = 0) then
977 delete from IEU_WP_NODE_SECTION_MAPS
978 where ENUM_TYPE_UUID IN
979 (SELECT enum_type_uuid FROM ieu_uwq_sel_enumerators
980 WHERE sel_enum_id = x_node_id)
981 and SECTION_ID = l_section_id;
982 end if;
983 end if;
984
985 elsif ( l_def_type_flag ='F') then
986
987 DELETE FROM ieu_wp_action_maps
988 WHERE action_param_set_id = x_param_set_id AND
989 action_map_type_code = 'NODE_DS' AND
990 action_map_code IN
991 (SELECT ds.NODE_DS_ID FROM ieu_uwq_sel_enumerators e, ieu_uwq_node_ds ds
992 WHERE e.sel_enum_id = x_node_id
993 and e.ENUM_TYPE_UUID = ds.ENUM_TYPE_UUID);
994
995 end if;
996 end if;
997 --3.
998 if (x_node_id = 0) then
999 if (l_def_type_flag <> 'N' and l_def_type_flag <>'M') then
1000 --a.
1001 SELECT wp_action_def_id, COUNT(*)
1002 INTO l_maction_def_id, l_num_set_entries
1003 FROM ieu_wp_act_param_sets_b
1004 WHERE wp_action_def_id IN
1005 (SELECT wp_action_def_id FROM ieu_wp_act_param_sets_b
1006 WHERE action_param_set_id = x_param_set_id)
1007 GROUP BY wp_action_def_id;
1008
1009 --b.
1010 DELETE FROM ieu_wp_param_props_tl
1011 WHERE param_property_id IN
1012 (SELECT param_property_id FROM ieu_wp_param_props_b
1013 WHERE
1014 action_param_set_id = x_param_set_id);
1015
1016 DELETE FROM ieu_wp_param_props_b
1017 WHERE action_param_set_id = x_param_set_id;
1018
1019 DELETE FROM ieu_wp_act_param_sets_tl
1020 WHERE action_param_set_id = x_param_set_id;
1021
1022 DELETE FROM ieu_wp_act_param_sets_b
1023 WHERE action_param_set_id = x_param_set_id;
1024
1025 --c.
1026 IF (l_num_set_entries = 1) THEN
1027 DELETE FROM ieu_wp_param_defs_tl
1028 WHERE param_id IN
1029 (SELECT param_id FROM ieu_wp_action_params
1030 WHERE wp_action_def_id = l_maction_def_id);
1031
1032 DELETE FROM ieu_wp_param_defs_b
1033 WHERE param_id IN
1034 (SELECT param_id FROM ieu_wp_action_params
1035 WHERE wp_action_def_id = l_maction_def_id);
1036
1037 DELETE FROM ieu_wp_action_params
1038 WHERE wp_action_def_id = l_maction_def_id;
1039
1040 DELETE FROM ieu_uwq_maction_defs_tl
1041 WHERE maction_def_id = l_maction_def_id;
1042
1043 DELETE FROM ieu_uwq_maction_defs_b
1044 WHERE maction_def_id = l_maction_def_id;
1045 END IF;
1046 else
1047 DELETE FROM ieu_uwq_maction_defs_tl
1048 WHERE maction_def_id = x_maction_id;
1049
1050 DELETE FROM ieu_uwq_maction_defs_b
1051 WHERE maction_def_id = x_maction_id;
1052
1053 end IF;
1054 END IF;
1055
1056 COMMIT;
1057
1058 if (l_def_type_flag <> 'F' and l_def_type_flag <> 'N' and l_def_type_flag <> 'M') then
1059 select count(m.WP_ACTION_MAP_ID) into l_count_map
1060 from IEU_WP_ACTION_MAPS m
1061 where m.ACTION_MAP_CODE = (select ENUM_TYPE_UUID from
1062 ieu_uwq_sel_enumerators where SEL_ENUM_ID = x_node_id)
1063 and m.ACTION_MAP_TYPE_CODE = 'NODE';
1064
1065 if (l_count_map = 0) then
1066
1067 update IEU_UWQ_SEL_ENUMERATORS set
1068 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1069 LAST_UPDATE_DATE = SYSDATE,
1070 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1071 WORK_PANEL_REGISTERED_FLAG = null
1072 where SEL_ENUM_ID = x_node_id;
1073
1074
1075 end if;
1076 end if;
1077 COMMIT;
1078 EXCEPTION
1079 WHEN fnd_api.g_exc_unexpected_error THEN
1080 ROLLBACK;
1081 x_return_status := fnd_api.g_ret_sts_unexp_error;
1082
1083 WHEN OTHERS THEN
1084 ROLLBACK;
1085 x_return_status := fnd_api.g_ret_sts_unexp_error;
1086 END Delete_Action_From_Node;
1087
1088
1089 END ieu_work_action_pvt;
1090