[Home] [Help]
PACKAGE BODY: APPS.IEU_WP_ACTION_PVT
Source
1 PACKAGE BODY IEU_WP_ACTION_PVT AS
2 /* $Header: IEUACFB.pls 120.3 2007/12/17 11:39:21 svidiyal ship $ */
3
4
5 --===================================================================
6 -- NAME
7 -- CREATE_action_map
8 --
9 -- PURPOSE
10 -- Private api to create action map
11 --
12 -- NOTES
13 -- 1. UWQ Admin will use this procedure to create action map
14 --
15 --
16 -- HISTORY
17 -- 8-may-2002 dolee Created
18
19 --===================================================================
20
21
22 PROCEDURE CREATE_action_map (x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 rec_obj IN SYSTEM.IEU_WP_ACTION_MAPS_OBJ
26 ) AS
27
28 l_action_map_id NUMBER(15);
29 sql_stmt varchar2(2000);
30 l_count number:=0;
31 l_responsibility_id number;
32 l_application_id number;
33 BEGIN
34 l_responsibility_id := rec_obj.responsibility_id;
35 l_application_id := rec_obj.application_id;
36 fnd_msg_pub.delete_msg();
37 x_return_status := fnd_api.g_ret_sts_success;
38 FND_MSG_PUB.initialize;
39 x_msg_data := '';
40
41 if (rec_obj.responsibility_id is null) then
42 l_responsibility_id := -2;
43 end if;
44
45 EXECUTE IMMEDIATE 'select count(wp_action_map_id) from ieu_wp_action_maps where responsibility_id = :1 '||
46 ' and application_id =: 2 and action_param_set_id = :3 and action_map_code= :4 and action_map_type_code = :5 '
47 INTO l_count USING l_responsibility_id, l_application_id, rec_obj.action_param_set_id,
48 rec_obj.action_map_code, rec_obj.action_map_type_code;
49
50 IF (l_count = 0) then
51 select IEU_wp_action_maps_S1.NEXTVAL into l_action_map_id from sys.dual;
52 x_msg_data :=x_msg_data || ' , INSERT INTO maps table with id '|| l_action_map_id;
53 sql_stmt := 'insert INTO IEU_wp_action_mapS'||
54 ' (WP_ACTION_MAP_ID,'||
55 ' OBJECT_VERSION_NUMBER,'||
56 ' CREATED_BY,'||
57 ' CREATION_DATE,'||
58 ' LAST_UPDATED_BY,'||
59 ' LAST_UPDATE_DATE,'||
60 ' LAST_UPDATE_LOGIN,'||
61 ' ACTION_PARAM_SET_ID,'||
62 ' APPLICATION_ID,'||
63 ' RESPONSIBILITY_ID,'||
64 ' ACTION_MAP_TYPE_CODE,'||
65 ' ACTION_MAP_CODE,'||
66 ' ACTION_MAP_SEQUENCE,'||
67 ' PANEL_SEC_CAT_CODE,'||
68 ' NOT_VALID_FLAG,'||
69 ' DEV_DATA_FLAG '||
70 ' )'||
71 ' values ('||
72 ' :1,'||
73 ' :2,'||
74 ' :3,'||
75 ' :4,'||
76 ' :5,'||
77 ' :6,'||
78 ' :7,'||
79 ' :8,'||
80 ' :9,'||
81 ' :10,'||
82 ' :11,'||
83 ' :12,'||
84 ' :13,'||
85 ' :14,'||
86 ' :15,'||
87 ' :16 '||
88 ' )';
89 EXECUTE IMMEDIATE sql_stmt USING l_action_map_id, '1',FND_GLOBAL.USER_ID, SYSDATE,
90 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.LOGIN_ID, rec_obj.action_param_set_id, rec_obj.application_id,
91 rec_obj.responsibility_id,rec_obj.action_map_type_code,rec_obj.action_map_code,
92 rec_obj.action_map_sequence, rec_obj.panel_sec_cat_code, rec_obj.not_valid_flag,
93 rec_obj.dev_data_flag;
94 else if (l_count >0 ) then
95 EXECUTE IMMEDIATE 'update ieu_wp_action_maps set responsibility_id = null '||
96 ' where responsibility_id = -2 and action_param_set_id = :2 '||
97 ' and action_map_code= :3 and action_map_type_code = :4 '
98 USING rec_obj.action_param_set_id,
99 rec_obj.action_map_code, rec_obj.action_map_type_code;
100 end if ;
101
102 END if;
103 x_msg_data := x_msg_data || ' , after insert table ';
104
105 COMMIT;
106
107 EXCEPTION
108 WHEN fnd_api.g_exc_error THEN
109 ROLLBACK;
110 x_return_status := fnd_api.g_ret_sts_error;
111
112 WHEN fnd_api.g_exc_unexpected_error THEN
113 ROLLBACK;
114 x_return_status := fnd_api.g_ret_sts_unexp_error;
115
116 WHEN OTHERS THEN
117 ROLLBACK;
118 x_return_status := fnd_api.g_ret_sts_unexp_error;
119
120 END CREATE_action_map;
121 PROCEDURE DELETE_Actions (x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 r_param_set_id IN ieu_wp_act_param_sets_b.action_param_set_id%type
125 ) is
126
127 media_count NUMBER(15);
128 temp_svr_type_id NUMBER(15);
129 l_action_param_set_id NUMBER(15);
130 v_label VARCHAR2(500);
131 v_SelectStmt Varchar2(500);
132 v_CursorID INTEGER;
133 v_Dummy INTEGER;
134 v_param_set_id ieu_wp_act_param_sets_b.action_param_set_id%type;
135
136 CURSOR c_cursor IS
137 SELECT param_property_id
138 FROM ieu_wp_param_props_b
139 WHERE action_param_set_id = v_param_set_id;
140 cur_rec c_cursor%ROWTYPE;
141 begin
142 fnd_msg_pub.delete_msg();
143 x_return_status := fnd_api.g_ret_sts_success;
144 FND_MSG_PUB.initialize;
145 x_msg_data := '';
146
147 v_param_set_id := r_param_set_id;
148 OPEN c_cursor;
149 Loop
150 FETCH c_cursor INTO cur_rec;
151 EXIT WHEN c_cursor%NOTFOUND;
152 delete from IEU_WP_PARAM_PROPS_B
153 where param_property_id = cur_rec.param_property_id;
154
155 delete from IEU_WP_PARAM_PROPS_TL
156 where param_property_id = cur_rec.param_property_id;
157 end LOOP;
158 CLOSE c_cursor;
159
160
161 EXECUTE IMMEDIATE
162 ' delete from IEU_WP_ACT_PARAM_SETS_B '||
163 ' where action_param_set_id = :1 '
164 USING r_param_set_id;
165
166 EXECUTE IMMEDIATE 'delete from IEU_WP_ACT_PARAM_SETS_TL where action_param_set_id = :1 '
167 USING r_param_set_id;
168
169 EXECUTE IMMEDIATE ' delete from IEU_wp_action_mapS where action_param_set_id = :1 '
170 USING r_param_set_id;
171
172
173 if (sql%notfound) then
174 null;
175 end if;
176 COMMIT;
177
178 EXCEPTION
179
180 WHEN fnd_api.g_exc_error THEN
181 ROLLBACK;
182 x_return_status := fnd_api.g_ret_sts_error;
183
184 WHEN fnd_api.g_exc_unexpected_error THEN
185 ROLLBACK;
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187
188 WHEN NO_DATA_FOUND THEN
189 null;
190
191 WHEN OTHERS THEN
192 ROLLBACK;
193 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
194 x_return_status := fnd_api.g_ret_sts_unexp_error;
195
196
197 commit;
198 END DELETE_Actions;
199
200 --===================================================================
201 -- NAME
202 -- Update_MAction
203 --
204 -- PURPOSE
205 -- Private api to update media type
206 --
207 -- NOTES
208 -- 1. UWQ Work Panel Admin will use this procedure to update an action
209 --
210 --
211 -- HISTORY
212 -- 08-MAY-2002 GPAGADAL Created
213
214 --===================================================================
215 PROCEDURE Update_MAction ( x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 r_MACTION_DEF_ID IN NUMBER,
219 r_action_user_label IN VARCHAR2,
220 r_action_description IN VARCHAR2,
221 r_param_set_id IN NUMBER)
222 AS
223 l_language VARCHAR2(4);
224 l_source_lang VARCHAR2(4);
225 v_SelectStmt Varchar2(500);
226 v_CursorID INTEGER;
227 v_Dummy INTEGER;
228
229 BEGIN
230 fnd_msg_pub.delete_msg();
231 x_return_status := fnd_api.g_ret_sts_success;
232 FND_MSG_PUB.initialize;
233 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
234 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
235 x_msg_data := '';
236 execute immediate ' update IEU_WP_ACT_PARAM_SETS_TL ' ||
237 ' set ' ||
238 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, ' ||
239 ' LAST_UPDATE_DATE = SYSDATE, '||
240 ' LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, '||
241 ' ACTION_PARAM_SET_LABEL = :1 , '||
242 ' ACTION_PARAM_SET_DESC = :2 ' ||
243 ' where ACTION_PARAM_SET_ID = :3 ' ||
244 ' and language IN (:4 , :5 )'
245 using r_action_user_label, r_action_description,r_param_set_id, l_language,l_source_lang ;
246
247
248 EXCEPTION
249 WHEN fnd_api.g_exc_error THEN
250 ROLLBACK;
251 x_return_status := fnd_api.g_ret_sts_error;
252
253 WHEN fnd_api.g_exc_unexpected_error THEN
254 ROLLBACK;
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256
257 WHEN OTHERS THEN
258 ROLLBACK;
259 x_return_status := fnd_api.g_ret_sts_unexp_error;
260
261 commit;
262 END Update_MAction;
263
264 --===================================================================
265 -- NAME
266 -- UPDATE_action_map
267 --
268 -- PURPOSE
269 -- Private api to update action map
270 --
271 -- NOTES
272 -- 1. UWQ work panel will use this procedure to update action map
273 --
274 --
275 -- HISTORY
276 -- 8-may-2002 dolee Created
277
278 --===================================================================
279
280
281
282 PROCEDURE UPDATE_action_map (x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 rec_obj IN SYSTEM.IEU_WP_ACTION_MAPS_OBJ
286 ) AS
287
288 l_cur_obj_versn NUMBER;
289
290 BEGIN
291
292 fnd_msg_pub.delete_msg();
293 x_return_status := fnd_api.g_ret_sts_success;
294 FND_MSG_PUB.initialize;
295 x_msg_data := '';
296
297 execute immediate 'select unique(object_version_number) from ieu_wp_action_maps where wp_action_map_ID = :1'
298 into l_cur_obj_versn using rec_obj.wp_action_map_id;
299
300
301 EXECUTE IMMEDIATE
302 ' update IEU_WP_ACTION_MAPS '||
303 ' set ' ||
304 ' OBJECT_VERSION_NUMBER = l_cur_obj_versn+1, '||
305 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
306 ' LAST_UPDATE_DATE = SYSDATE, '||
307 ' LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID, '||
308 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
309 -- ' ACTION_MAP_SEQUENCE = :1, '||
310 ' ACTION_MAP_SEQUENCE = :1 '||
311 /*********************************************************************************/
312
313 ' NOT_VALID_FLAG = :2 '||
314 ' where WP_ACTION_MAP_ID = :3 '
315 USING rec_obj.action_map_sequence, rec_obj.not_valid_flag ,rec_obj.wp_action_map_id;
316
317 commit;
318
319 EXCEPTION
320 WHEN fnd_api.g_exc_error THEN
321 ROLLBACK;
322 x_return_status := fnd_api.g_ret_sts_error;
323
324 WHEN fnd_api.g_exc_unexpected_error THEN
325 ROLLBACK;
326 x_return_status := fnd_api.g_ret_sts_unexp_error;
327
328 WHEN NO_DATA_FOUND THEN
329 null;
330
331 WHEN OTHERS THEN
332 ROLLBACK;
333 x_return_status := fnd_api.g_ret_sts_unexp_error;
334 commit;
335
336 END UPDATE_action_map;
337
338
339 --===================================================================
340 -- NAME
341 -- UPDATE_action_map_sequence
342 --
343 -- PURPOSE
344 -- Private api to update action map sequence
345 --
346 -- NOTES
347 -- 1. UWQ work panel will use this procedure to update action map sequence
348 --
349 --
350 -- HISTORY
351 -- 14-aug-2002 dolee Created
352
353 --===================================================================
354
355
356
357 PROCEDURE UPDATE_action_map_sequence (x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2,
360 r_action_param_set_id IN IEU_WP_ACTION_MAPS.action_param_set_id%type,
361 r_MACTION_DEF_TYPE_FLAG IN IEU_UWQ_MACTION_DEFS_B.MACTION_DEF_TYPE_FLAG %type,
362 r_application_id IN IEU_WP_ACTION_MAPS.application_id%type,
363 r_sel_enum_id IN IEU_UWQ_SEL_ENUMERATORS.sel_enum_id%type,
364 r_action_map_sequence IN IEU_WP_ACTION_MAPS.action_map_sequence%type,
365 r_not_valid_flag IN IEU_WP_ACTION_MAPS.not_valid_flag%type
366 ) AS
367
368 l_cur_obj_versn NUMBER;
369 v_type_code varchar2(100);
370 v_ver_number ieu_wp_action_maps.object_version_number%type;
371 v_MACTION_DEF_TYPE_FLAG ieu_uwq_maction_defs_b.MACTION_DEF_TYPE_FLAG%type;
372 v_action_param_set_id ieu_wp_action_maps.action_param_set_id%type;
373 v_application_id ieu_wp_action_maps.application_id%type;
374 v_sel_enum_id ieu_uwq_sel_enumerators.sel_enum_id%type;
375 CURSOR c_cursor is
376 select m.WP_ACTION_MAP_ID, m.object_version_number
377 from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
378 ieu_wp_act_param_sets_b sb
379 where m.action_map_type_code = v_type_code
380 --and m.application_id = db.application_id
381 and db.maction_def_type_flag= v_MACTION_DEF_TYPE_FLAG
382 and db.maction_def_id = sb.wp_action_def_id
383 and sb.action_param_set_id= m.action_param_set_id
384 and m.action_param_set_id = v_action_param_set_id
385 --and m.APPLICATION_ID = v_application_id
386 and m.ACTION_MAP_CODE = (select ENUM_TYPE_UUID
387 FROM ieu_uwq_sel_enumerators
388 where sel_enum_id = v_sel_enum_id);
389 c_rec c_cursor%ROWTYPE;
390 CURSOR c_cursor2 is
391 select m.WP_ACTION_MAP_ID, m.object_version_number
392 from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db,
393 ieu_wp_act_param_sets_b sb, ieu_uwq_node_ds ds
394 where m.action_map_type_code = v_type_code
395 --and m.application_id = db.application_id
396 and db.maction_def_type_flag= 'F'
397 and db.maction_def_id = sb.wp_action_def_id
398 and sb.action_param_set_id= m.action_param_set_id
399 and m.action_param_set_id =v_action_param_set_id
400 --and m.APPLICATION_ID = v_application_id
401 and m.ACTION_MAP_CODE = to_char(ds.NODE_DS_ID)
402 and ds.ENUM_TYPE_UUID = (select ENUM_TYPE_UUID
403 FROM ieu_uwq_sel_enumerators
404 where sel_enum_id = v_sel_enum_id);
405 c_rec2 c_cursor2%ROWTYPE;
406 BEGIN
407 fnd_msg_pub.delete_msg();
408 x_return_status := fnd_api.g_ret_sts_success;
409 FND_MSG_PUB.initialize;
410 x_msg_data := '';
411
412 if(r_MACTION_DEF_TYPE_FLAG <> 'F') then
413 v_type_code := 'NODE';
414 v_MACTION_DEF_TYPE_FLAG := r_MACTION_DEF_TYPE_FLAG;
415 v_action_param_set_id := r_action_param_set_id;
416 v_application_id := r_application_id;
417 v_sel_enum_id := r_sel_enum_id;
418 OPEN c_cursor;
419 loop
420 FETCH c_cursor INTO c_rec;
421 EXIT WHEN c_cursor%NOTFOUND;
422 if (c_rec.object_version_number is null) then v_ver_number := 1;
423 else
424 v_ver_number := c_rec.object_version_number+1 ;
425 end if;
426 IF r_not_valid_flag IS NOT NULL then
427 EXECUTE immediate
428 ' update IEU_WP_ACTION_MAPS '||
429 ' set ' ||
430 ' OBJECT_VERSION_NUMBER = :1, '||
431 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
432 ' LAST_UPDATE_DATE = SYSDATE, '||
433 ' LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID, '||
434 ' ACTION_MAP_SEQUENCE = :2 , '||
435 ' NOT_VALID_FLAG = :3 '||
436 ' where WP_ACTION_MAP_ID = :4 '
437 USING v_ver_number, r_action_map_sequence, r_not_valid_flag, c_rec.wp_action_map_id;
438 else
439 EXECUTE immediate
440 ' update IEU_WP_ACTION_MAPS ' ||
441 ' set ' ||
442 ' OBJECT_VERSION_NUMBER = :1 , '||
443 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
444 ' LAST_UPDATE_DATE = SYSDATE, '||
445 ' LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID, '||
446 ' ACTION_MAP_SEQUENCE = :2'||
447 ' where WP_ACTION_MAP_ID = :3 '
448 USING v_ver_number, r_action_map_sequence, c_rec.wp_action_map_id;
449 END if;
450 END loop;
451 CLOSE c_cursor;
452 elsif(r_MACTION_DEF_TYPE_FLAG = 'F') then
453 v_type_code := 'NODE_DS';
454 v_action_param_set_id := r_action_param_set_id;
455 v_application_id := r_application_id;
456 v_sel_enum_id := r_sel_enum_id;
457 OPEN c_cursor2;
458 loop
459 FETCH c_cursor2 INTO c_rec2;
460 EXIT WHEN c_cursor2%NOTFOUND;
461 if (c_rec.object_version_number is null) then v_ver_number := 1;
462 else
463 v_ver_number := c_rec.object_version_number+1 ;
464 end if;
465 IF r_not_valid_flag IS NOT NULL then
466 EXECUTE immediate
467 ' update IEU_WP_ACTION_MAPS '||
468 ' SET '||
469 ' OBJECT_VERSION_NUMBER = :1 , '||
470 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
471 ' LAST_UPDATE_DATE = SYSDATE, '||
472 ' LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID, '||
473 ' ACTION_MAP_SEQUENCE = :2 , '||
474 ' NOT_VALID_FLAG = :3 '||
475 ' where WP_ACTION_MAP_ID = :4 '
476 USING v_ver_number, r_action_map_sequence, r_not_valid_flag, c_rec2.wp_action_map_id;
477 else
478 EXECUTE immediate
479 ' update IEU_WP_ACTION_MAPS '||
480 ' SET '||
481 ' OBJECT_VERSION_NUMBER = :1 , '||
482 ' LAST_UPDATED_BY = FND_GLOBAL.USER_ID, '||
483 ' LAST_UPDATE_DATE = SYSDATE, '||
484 ' LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID, '||
485 ' ACTION_MAP_SEQUENCE = :2 '||
486 ' where WP_ACTION_MAP_ID = :3 '
487 USING v_ver_number, r_action_map_sequence, c_rec2.wp_action_map_id;
488 END if;
489 END loop;
490 CLOSE c_cursor2;
491 end if;
492 commit;
493
494 EXCEPTION
495 WHEN fnd_api.g_exc_error THEN
496 ROLLBACK;
497 x_return_status := fnd_api.g_ret_sts_error;
498
499 WHEN fnd_api.g_exc_unexpected_error THEN
500 ROLLBACK;
501 x_return_status := fnd_api.g_ret_sts_unexp_error;
502
503 WHEN NO_DATA_FOUND THEN
504 null;
505
506 WHEN OTHERS THEN
507 ROLLBACK;
508 x_return_status := fnd_api.g_ret_sts_unexp_error;
509 commit;
510
511 END UPDATE_action_map_sequence;
512
513 --===================================================================
514 -- NAME
515 -- DELETE_action_map
516 --
517 -- PURPOSE
518 -- Private api to delete action map
519 --
520 -- NOTES
521 -- 1. UWQ Admin will use this procedure to delete action map
522 --
523 --
524 -- HISTORY
525 -- 8-may-2002 DOLEE Created
526
527 --===================================================================
528
529
530 PROCEDURE DELETE_action_map (x_return_status OUT NOCOPY VARCHAR2,
531 x_msg_count OUT NOCOPY NUMBER,
532 x_msg_data OUT NOCOPY VARCHAR2,
533 r_action_map_id IN NUMBER
534 ) is
535
536 media_count NUMBER(15);
537 temp_svr_type_id NUMBER(15);
538 l_count NUMBER(15) :=0;
539 BEGIN
540 fnd_msg_pub.delete_msg();
541 x_return_status := fnd_api.g_ret_sts_success;
542 FND_MSG_PUB.initialize;
543 x_msg_data := '';
544 execute immediate ' select count(*) '||
545 ' from ieu_wp_action_maps where wp_action_map_id = :1 '||
546 ' and responsibility_id is null'
547 into l_count
548 using r_action_map_id;
549 x_msg_data := x_msg_data || ' count is '|| l_count;
550 if (l_count <> 1) then
551 EXECUTE immediate
552 ' delete from IEU_wp_action_mapS '||
553 ' where wp_action_map_ID = :1 '
554 USING r_action_map_id;
555 else
556 execute immediate
560 end if;
557 ' update ieu_wp_action_maps set responsibility_id = -2 ' ||
558 ' where wp_action_map_id = :1 '
559 using r_action_map_id;
561
562
563 if (sql%notfound) then
564 null;
565 end if;
566 COMMIT;
567
568 EXCEPTION
569
570 WHEN fnd_api.g_exc_error THEN
571 ROLLBACK;
572 x_return_status := fnd_api.g_ret_sts_error;
573
574 WHEN fnd_api.g_exc_unexpected_error THEN
575 ROLLBACK;
576 x_return_status := fnd_api.g_ret_sts_unexp_error;
577
578 WHEN NO_DATA_FOUND THEN
579 null;
580
581 WHEN OTHERS THEN
582 ROLLBACK;
583 x_return_status := fnd_api.g_ret_sts_unexp_error;
584
585 commit;
586
587 END DELETE_action_map;
588
589
590
591
592 -- ===============================================================
593 -- Start of Comments
594 -- ===============================================================
595 -- API Name
596 -- CreateFromAction
597 -- Type
598 -- Private
599 -- Pre-Req
600 --
601 -- Parameters
602 --
603 -- IN
604 --
605 -- r_wp_action_key VARCHAR2
606 --
607 -- OUT
608 -- x_return_status OUT VARCHAR2
609 -- x_msg_count OUT NUMBER
610 -- x_msg_data OUT VARCHAR2
611 --
612 -- End of Comments
613 -- ===============================================================
614 PROCEDURE CreateFromAction( x_return_status OUT NOCOPY VARCHAR2,
615 x_msg_count OUT NOCOPY NUMBER,
616 x_msg_data OUT NOCOPY VARCHAR2,
617 r_wp_action_key IN VARCHAR2,
618 r_language IN VARCHAR2,
619 r_label IN VARCHAR2,
620 r_desc IN VARCHAR2,
621 r_param_set_id IN NUMBER,
622 r_enumId IN VARCHAR2)
623 AS
624
625 l_wp_maction_def_id NUMBER(15);
626 l_param_set_id NUMBER(15);
627 l_language VARCHAR2(4);
628 l_source_lang VARCHAR2(4);
629 l_msg_count NUMBER(2);
630
631 l_msg_data VARCHAR2(2000);
632
633 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
634
635 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
636
637 v_cursor1 NUMBER;
638 sql_stmt varchar2(2000);
639 sql_stmt1 varchar2(2000);
640 l_param_id NUMBER(15);
641 l_property_id NUMBER(15);
642 l_property_value varchar(4000);
643 l_not_valid_flag varchar(5);
644 l_value_override_flag varchar(5);
645 v_numrows1 NUMBER;
646 l_new_param_set_id NUMBER(15);
647 l_wp_action_map_id NUMBER(15);
648 l_temp_map_sequence ieu_wp_action_maps.action_map_sequence%type;
649
650
651 BEGIN
652
653 fnd_msg_pub.delete_msg();
654 x_return_status := fnd_api.g_ret_sts_success;
655 FND_MSG_PUB.initialize;
656 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
657 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
658 x_msg_data := '';
659
660
661 IEU_WP_ACTION_PVT.CreateFromAction2( x_return_status, x_msg_count,
662 x_msg_data, r_wp_action_key,
663 r_language,
664 r_label,
665 r_desc,
666 r_param_set_id,
667 r_enumId,
668 null);
669
670
671 commit;
672 end CreateFromAction;
673
674
675 -- ===============================================================
676 -- Start of Comments
677 -- ===============================================================
678 -- API Name
679 -- CreateFromAction2
680 -- Type
681 -- Private
682 -- Pre-Req
683 --
684 -- Parameters
685 --
686 -- IN
687 --
688 -- r_wp_action_key VARCHAR2
689 --
690 -- OUT
691 -- x_return_status OUT VARCHAR2
692 -- x_msg_count OUT NUMBER
693 -- x_msg_data OUT VARCHAR2
694 --
695 -- End of Comments
696 -- ===============================================================
697
698
699 PROCEDURE CreateFromAction2( x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2,
702 r_wp_action_key IN VARCHAR2,
703 r_language IN VARCHAR2,
704 r_label IN VARCHAR2,
705 r_desc IN VARCHAR2,
706 r_param_set_id IN NUMBER,
707 r_enumId IN VARCHAR2,
708 r_dev_data_flag IN VARCHAR2)
709
710
711 AS
712
713 l_wp_maction_def_id NUMBER(15);
714 l_param_set_id NUMBER(15);
715 l_language VARCHAR2(4);
716 l_source_lang VARCHAR2(4);
717 l_msg_count NUMBER(2);
718 v_wp_action_key varchar2(500);
719 l_msg_data VARCHAR2(2000);
720
721 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
722
723 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
724
725 v_cursor1 NUMBER;
729 l_property_id NUMBER(15);
726 sql_stmt varchar2(2000);
727 sql_stmt1 varchar2(2000);
728 l_param_id NUMBER(15);
730 l_property_value varchar(4000);
731 l_not_valid_flag varchar(5);
732 l_value_override_flag varchar(5);
733 v_numrows1 NUMBER;
734 l_new_param_set_id NUMBER(15);
735 l_wp_action_map_id NUMBER(15);
736 l_temp_map_sequence ieu_wp_action_maps.action_map_sequence%type;
737 param_sets_rec IEU_WP_ACT_PARAM_SETS_SEED_PKG.WP_ACT_PARAM_SETS_rec_type;
738 param_props_rec IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type;
739 v_SelectStmt Varchar2(500);
740 v_CursorID INTEGER;
741 v_Dummy INTEGER;
742 v_param_set_id ieu_wp_param_props_b.action_param_set_id%type;
743 v_language ieu_wp_param_props_tl.language%type;
744 v_enumId ieu_uwq_sel_enumerators.sel_enum_id%type;
745 v_responsibility_id ieu_wp_action_maps.responsibility_id%type;
746 cursor c_cur is
747 SELECT
748 PARAM_ID, PROPERTY_ID,property_value
749 , value_override_flag,not_valid_flag
750 FROM ieu_wp_param_props_b
751 WHERE action_param_set_id in
752 (select a.action_param_set_id
753 from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
754 where a.action_param_set_id = b.action_param_set_id(+)
755 and b.action_param_set_id = v_param_set_id
756 and c.maction_def_key = LTRIM(RTRIM(v_wp_action_key))
757 and b.language = v_language
758 and c.maction_def_id = a.wp_action_def_id
759 and a.action_param_set_id in (select action_param_set_id
760 from ieu_wp_action_maps
761 where action_map_code in (select enum_type_uuid from ieu_uwq_sel_enumerators
762 where sel_enum_id =v_enumId
763 )
764 )
765 );
766 -- this c_cur2 will NOT get responsibility information FROM original action
767 cursor c_cur2 is
768 SELECT
769 object_version_number, application_id, action_map_type_code,
770 action_map_code, panel_sec_cat_code, not_valid_flag
771 FROM ieu_wp_action_maps
772 WHERE responsibility_id = v_responsibility_id
773 AND action_param_set_id in
774 (select a.action_param_set_id
775 from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
776 where a.action_param_set_id = b.action_param_set_id(+)
777 and b.action_param_set_id = v_param_set_id
778 and c.maction_def_key = LTRIM(RTRIM(v_wp_action_key))
779 and b.language = v_language
780 and c.maction_def_id = a.wp_action_def_id)
781 and action_map_code in (select enum_type_uuid from ieu_uwq_Sel_enumerators
782 where sel_enum_id =v_enumId);
783
784
785 c_rec c_cur%ROWTYPE;
786 c_rec2 c_cur2%ROWTYPE;
787 null_string varchar2(200) := null;
788 BEGIN
789
790 fnd_msg_pub.delete_msg();
791 x_return_status := fnd_api.g_ret_sts_success;
792 FND_MSG_PUB.initialize;
793 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
794 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
795 x_msg_data := '';
796
797
798 execute immediate ' select a.wp_action_def_id ' ||
799 ' from ieu_wp_act_param_sets_b a, ieu_uwq_maction_defs_b c ' ||
800 ' where a.action_param_set_id = :1 ' ||
801 ' and c.maction_def_key = :2 ' ||
802 ' and c.maction_def_id = a.wp_action_def_id '
803 into l_wp_maction_def_id using r_param_set_id, r_wp_action_key;
804
805
806
807 if l_wp_maction_def_id is not null then
808 select IEU_wp_act_param_sets_b_S1.NEXTVAL into l_new_param_set_id from sys.dual;
809 /* TYPE IEU_WP_ACT_PARAM_SETS_SEED_PKG.WP_ACT_PARAM_SETS_rec_type IS RECORD (
810 ACTION_PARAM_SET_ID NUMBER(15),
811 WP_ACTION_DEF_ID NUMBER(15),
812 ACTION_PARAM_SET_LABEL VARCHAR2(128),
813 ACTION_PARAM_SET_DESC VARCHAR2(500),
814 created_by NUMBER(15),
815 creation_date DATE,
816 last_updated_by NUMBER(15),
817 last_update_date DATE,
818 last_update_login NUMBER(15),
819 owner VARCHAR2(15) );
820 */
821 param_sets_rec.ACTION_PARAM_SET_ID := l_new_param_set_id;
822 param_sets_rec.WP_ACTION_DEF_ID := l_wp_maction_def_id;
823 param_sets_rec.ACTION_PARAM_SET_LABEL := LTRIM(RTRIM(r_label));
824 param_sets_rec.ACTION_PARAM_SET_DESC := LTRIM(RTRIM(r_desc));
825 param_sets_rec.created_by := FND_GLOBAL.USER_ID;
826 param_sets_rec.creation_date := SYSDATE;
827 param_sets_rec.last_updated_by := FND_GLOBAL.USER_ID;
828 param_sets_rec.last_update_date := SYSDATE;
829 param_sets_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
830 param_sets_rec.owner := 1;
831 IEU_WP_ACT_PARAM_SETS_SEED_PKG.Insert_Row(p_WP_ACT_PARAM_SETS_rec=>param_Sets_rec);
832 end if ;
833
834
835 v_wp_action_key := r_wp_action_key;
836 v_param_set_id := r_param_set_id;
837 v_language := r_language;
838 v_enumId := r_enumId;
839
840 OPEN c_cur;
841 Loop
842 FETCH c_cur INTO c_rec;
843 EXIT WHEN c_cur%NOTFOUND;
844 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
845 /*TYPE IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type IS RECORD (
846 PARAM_PROPERTY_ID NUMBER(15),
847 ACTION_PARAM_SET_ID NUMBER,
848 PARAM_ID NUMBER,
852 VALUE_OVERRIDE_FLAG VARCHAR2(5),
849 PROPERTY_ID NUMBER,
850 PROPERTY_VALUE VARCHAR(4000),
851 PROPERTY_VALUE_TL VARCHAR(4000),
853 created_by NUMBER(15),
854 creation_date DATE,
855 last_updated_by NUMBER(15),
856 last_update_date DATE,
857 last_update_login NUMBER(15),
858 not_valid_flag VARCHAR(4000),
859 owner VARCHAR2(15) );
860 */
861 param_props_rec.PARAM_PROPERTY_ID := l_param_property_id;
862 param_props_rec.ACTION_PARAM_SET_ID := l_new_param_set_id;
863 param_props_rec.PARAM_ID := c_rec.param_id;
864 param_props_rec.PROPERTY_ID := c_rec.property_id;
865 param_props_rec.PROPERTY_VALUE := c_rec.property_value;
866 param_props_rec.PROPERTY_VALUE_TL := c_rec.property_value;
867 param_props_rec.VALUE_OVERRIDE_FLAG := c_rec.value_override_flag;
868 param_props_rec.created_by := FND_GLOBAL.USER_ID;
869 param_props_rec.creation_date := SYSDATE;
870 param_props_rec.last_updated_by := FND_GLOBAL.USER_ID;
871 param_props_rec.last_update_date := SYSDATE;
872 param_props_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
873 param_props_rec.NOT_VALID_FLAG := c_rec.not_valid_flag;
874 param_props_rec.owner := 1;
875 IEU_WP_PARAM_PROPS_SEED_PKG.Insert_Row(p_wp_param_props_rec => param_props_rec);
876 end LOOP;
877 CLOSE c_cur;
878
879
880
881 v_responsibility_id := '-1' ;
882 OPEN c_cur2;
883 Loop
884 FETCH c_cur2 INTO c_rec2;
885 EXIT WHEN c_cur2%NOTFOUND;
886 ReOrdering(x_return_status,x_msg_count,x_msg_data,r_enumId ,c_rec2.application_id ,'W');
887 EXECUTE immediate
888 ' select max(m.action_map_sequence) '||
889 ' from ieu_wp_action_maps m, ieu_uwq_maction_defs_b db, '||
890 ' ieu_wp_act_param_sets_b sb '||
891 -- ' where m.application_id = :1 '||
892 ' where m.action_map_type_code = :1 '||
893 ' and m.action_map_code = :2 '||
894 ' and db.maction_def_type_flag = :3 '||
895 ' and db.maction_def_id = sb.wp_action_def_id '||
896 ' and sb.action_param_set_id = m.action_param_set_id '||
897 ' and m.responsibility_id = -1 '
898 into l_temp_map_sequence USING c_rec2.action_map_type_code, c_rec2.action_map_code, 'W';
899
900 if (l_temp_map_sequence is null) then
901 l_temp_map_sequence := 1;
902 else l_temp_map_sequence := l_temp_map_sequence+1;
903 END if;
904
905
906 --INSERT one RECORD WITH responsibility_id = -1
907 select IEU_WP_ACTION_MAPS_S1.NEXTVAL into l_wp_action_map_id from sys.dual;
908 EXECUTE immediate
909 ' insert INTO IEU_WP_ACTION_MAPS ' ||
910 ' (WP_ACTION_MAP_ID, '||
911 ' CREATED_BY, '||
912 ' CREATION_DATE, '||
913 ' LAST_UPDATED_BY, '||
914 ' LAST_UPDATE_DATE, '||
915 ' LAST_UPDATE_LOGIN, '||
916 ' ACTION_PARAM_SET_ID, '||
917 ' APPLICATION_ID, '||
918 ' RESPONSIBILITY_ID, '||
919 ' ACTION_MAP_TYPE_CODE, '||
920 ' ACTION_MAP_CODE, '||
921 ' PANEL_SEC_CAT_CODE, '||
922 ' NOT_VALID_FLAG, '||
923 ' OBJECT_VERSION_NUMBER, '||
924 ' action_map_sequence, '||
925 ' DEV_DATA_FLAG '||
926 ' ) VALUES ( '||
927 ' :1 , '||
928 ' :2, '||
929 ' :3, '||
930 ' :4, '||
931 ' :5, '||
932 ' :6, '||
933 ' :7 , '||
934 ' :8 , '||
935 ' :9, '||
936 ' :10 , '||
937 ' :11, '||
938 ' :12, '||
939 ' :13, '||
940 ' :14, '||
941 ' :15, '||
942 ' :16 '||
943 ' ) '
944 USING l_wp_action_map_id,FND_GLOBAL.USER_ID,SYSDATE, FND_GLOBAL.USER_ID, SYSDATE,
945 FND_GLOBAL.LOGIN_ID, l_new_param_set_id, c_rec2.application_id, '-1', c_rec2.action_map_type_code,
946 c_rec2.action_map_code, c_rec2.panel_sec_cat_code, c_rec2.not_valid_flag, c_rec2.object_version_number,
947 l_temp_map_sequence, r_dev_data_flag;
948
949
950 --INSERT one RECORD WITH responsibility_id IS null
951 select IEU_WP_ACTION_MAPS_S1.NEXTVAL into l_wp_action_map_id from sys.dual;
952 v_responsibility_id := null;
953 EXECUTE immediate
954 ' insert INTO IEU_WP_ACTION_MAPS '||
955 ' (WP_ACTION_MAP_ID, '||
956 ' CREATED_BY, '||
957 ' CREATION_DATE, '||
958 ' LAST_UPDATED_BY, '||
959 ' LAST_UPDATE_DATE, '||
960 ' LAST_UPDATE_LOGIN, '||
961 ' ACTION_PARAM_SET_ID, '||
962 ' APPLICATION_ID, '||
963 ' ACTION_MAP_TYPE_CODE, '||
964 ' ACTION_MAP_CODE, '||
965 ' PANEL_SEC_CAT_CODE, '||
966 ' NOT_VALID_FLAG, '||
967 ' OBJECT_VERSION_NUMBER, '||
968 ' action_map_sequence, '||
969 ' DEV_DATA_FLAG '||
970 ' ) VALUES ( '||
971 ' :1, '||
972 ' :2, '||
973 ' :3, '||
974 ' :4, '||
975 ' :5, '||
976 ' :6, '||
977 ' :7 , '||
978 ' :8, '||
979 ' :9, '||
980 ' :10, '||
981 ' :11 , '||
982 ' :12 , '||
983 ' :13 , '||
984 ' :14 , '||
985 ' :15 )'
989 l_temp_map_sequence, r_dev_data_flag;
986 USING l_wp_action_map_id,FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.LOGIN_ID,
987 l_new_param_set_id, c_rec2.application_id, c_rec2.action_map_type_code,
988 c_rec2.action_map_code, c_rec2.panel_sec_cat_code, c_rec2.not_valid_flag, c_rec2.object_version_number,
990 end loop;
991 CLOSE c_cur2;
992 EXCEPTION
993 WHEN fnd_api.g_exc_error THEN
994 ROLLBACK;
995 x_return_status := fnd_api.g_ret_sts_error;
996
997 WHEN fnd_api.g_exc_unexpected_error THEN
998 ROLLBACK;
999 x_return_status := fnd_api.g_ret_sts_unexp_error;
1000
1001 WHEN OTHERS THEN
1002 ROLLBACK;
1003 x_return_status := fnd_api.g_ret_sts_unexp_error;
1004
1005
1006 commit;
1007 end CreateFromAction2;
1008
1009
1010
1011
1012 -- ===============================================================
1013 -- Start of Comments
1014 -- ===============================================================
1015 -- API Name
1016 -- UpdtateParamProps
1017 -- Type
1018 -- Private
1019 -- Pre-Req
1020 --
1021 -- Parameters
1022 --
1023 -- OUT
1024 -- x_return_status OUT VARCHAR2
1025 -- x_msg_count OUT NUMBER
1026 -- x_msg_data OUT VARCHAR2
1027 --
1028 -- GPAGADAL updated on 2/21/2003
1029 -- Update the param_props table instead of deleting when the data type is DATE
1030 -- End of Comments
1031 -- ===============================================================
1032
1033 PROCEDURE UpdateParamProps( x_return_status OUT NOCOPY VARCHAR2,
1034 x_msg_count OUT NOCOPY NUMBER,
1035 x_msg_data OUT NOCOPY VARCHAR2,
1036 r_applId IN NUMBER)
1037
1038 AS
1039 l_user_param_set_id IEU_WP_ACT_PARAM_SETS_B.action_param_set_id%type;
1040 l_original_param_set_id IEU_WP_ACT_PARAM_SETS_B.action_param_set_id%type;
1041 v_cursor1 NUMBER;
1042 v_cursor2 NUMBER;
1043 v_cursor NUMBER;
1044 l_param_property_id NUMBER;
1045 l_param_id NUMBER;
1046 l_property_id NUMBER;
1047 l_property_value VARCHAR2(4000);
1048 l_value_override_flag VARCHAR2(5);
1049 l_not_valid_flag VARCHAR2(5);
1050 v_numrows1 NUMBER;
1051 v_numrows NUMBER;
1052 sql_stmt varchar2(2000);
1053 sql_stmt1 varchar2(2000);
1054 sql_stmt2 varchar2(2000);
1055 l_count NUMBER :=0;
1056 l_language VARCHAR2(4);
1057 l_source_lang VARCHAR2(4);
1058 l_delete_param_property_id IEU_WP_PARAM_PROPS_B.param_property_id%type;
1059 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
1060 l_temp IEU_WP_PARAM_PROPS_B.action_param_set_id%type;
1061 l_ptemp IEU_WP_PARAM_PROPS_B.param_id%type;
1062
1063 l_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.action_param_set_id%type;
1064 l_action_temp_id IEU_WP_ACT_PARAM_SETS_B.action_param_set_id%type;
1065 l_index NUMBER:=0;
1066 l_my_count NUMBER:=0;
1067 l_max_property NUMBER :=0;
1068 l_max_action_param_set_id IEU_WP_ACT_PARAM_SETS_B.action_param_set_id%type;
1069 l_param_property_key ieu_wp_param_props_b.param_property_id%type;
1070 param_props_rec IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type;
1071 param_sets_rec IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type;
1072 v_applId ieu_wp_param_defs_b.application_id%type;
1073 TYPE c_cursor5 IS REF CURSOR;
1074 c_ref c_cursor5;
1075
1076 TYPE c_cursor6 IS REF CURSOR;
1077 c_ref2 c_cursor6;
1078
1079 cursor c_cur is
1080 select WP_ACTION_DEF_ID, param_id
1081 from ieu_wp_action_params
1082 WHERE param_id IN (SELECT param_id FROM ieu_wp_param_defs_b
1083 WHERE application_id =v_applId);
1084 c_rec c_cur%ROWTYPE;
1085
1086 -- this cursor is for those properties which component id has been changed 'DATE'
1087 -- In that case, the default value property should not be existed if there is.
1088 v_data_type ieu_wp_param_defs_b.data_type%type;
1089 cursor c_cur2 is
1090 select distinct action_param_set_id, param_id
1091 from ieu_wp_param_props_b
1092 where param_id in (select param_id from ieu_wp_param_defs_b
1093 where DATA_TYPE = v_data_type
1094 AND application_id(+) = v_applId)
1095 order by action_param_set_id;
1096 c_rec2 c_cur2%ROWTYPE;
1097
1098 Begin
1099
1100 fnd_msg_pub.delete_msg();
1101 x_return_status := fnd_api.g_ret_sts_success;
1102 FND_MSG_PUB.initialize;
1103 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1104 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1105 x_msg_data := '';
1106
1107 --delete param props if param has been deleted by some actions
1108 delete from ieu_wp_param_props_b where param_property_id in
1109 ( select param_property_id
1110 from ieu_wp_param_props_b
1111 where param_id not in (select param_id from ieu_wp_param_defs_b));
1112
1113 delete from ieu_wp_param_props_tl where param_property_id in
1114 ( select param_property_id
1115 from ieu_wp_param_props_b
1116 where param_id not in (select param_id from ieu_wp_param_defs_b));
1117
1118 --DBMS_OUTPUT.Put_Line('before for loop');
1119
1120 -- this loop is for action_param_set_id which missing param_id
1121
1122 v_applId := r_applId;
1123 OPEN c_cur;
1124 LOOP
1125 FETCH c_cur INTO c_rec;
1126 EXIT WHEN c_cur%NOTFOUND;
1127 sql_stmt1 := 'select action_param_set_id '||
1128 ' from ieu_wp_act_param_sets_b '||
1129 ' where action_param_set_id in (select distinct action_param_set_id '||
1130 ' from ieu_wp_act_param_sets_b '||
1134 ' (select distinct action_param_set_id '||
1131 ' where wp_action_def_id = :action_id '||
1132 ' ) '||
1133 ' and action_param_set_id not IN '||
1135 ' from ieu_wp_param_props_b '||
1136 ' where param_id = :param_id)';
1137
1138 OPEN c_ref FOR sql_stmt1 USING c_rec.wp_action_def_id, c_rec.param_id;
1139
1140 LOOP
1141 FETCH c_ref INTO l_action_param_set_id;
1142 EXIT WHEN c_ref%NOTFOUND;
1143 l_index :=0; --initialize
1144 sql_stmt := 'select action_param_set_id '||
1145 ' from ieu_wp_act_param_sets_b '||
1146 ' where action_param_set_id in (select distinct action_param_set_id '||
1147 ' from ieu_wp_act_param_sets_b '||
1148 ' where wp_action_def_id = :action_id '||
1149 ') '||
1150 ' and action_param_set_id IN '||
1151 ' (select distinct action_param_set_id '||
1152 ' from ieu_wp_param_props_b '||
1153 ' where param_id = :param_id)';
1154
1155 OPEN c_ref2 FOR sql_stmt USING c_rec.wp_action_def_id, c_rec.param_id;
1156
1157 LOOP
1158 FETCH c_ref2 INTO l_action_temp_id;
1159 EXIT WHEN c_ref2%NOTFOUND;
1160 select count(property_id) into l_index
1161 from ieu_wp_param_props_b
1162 where action_param_set_id = l_action_temp_id
1163 and param_id = c_rec.param_id;
1164
1165 if l_max_property < l_index then
1166 l_max_property := l_index;
1167 l_max_action_param_set_id := l_action_temp_id;
1168 end if;
1169
1170 end loop; --for v_cursor
1171 --DBMS_SQL.CLOSE_CURSOR(v_cursor);
1172 CLOSE c_ref2;
1173
1174 --get property_id, property_value, VALUE_OVERRIDE_FLAG, NOT_VALID_FLAG
1175 If l_max_action_param_set_id IS NOT NULL then
1176 sql_stmt2 := 'select property_id, property_value, VALUE_OVERRIDE_FLAG, NOT_VALID_FLAG '||
1177 ' from ieu_wp_param_props_b '||
1178 ' where param_id = :param_id '||
1179 ' and action_param_set_id = :max_set_id ';
1180 OPEN c_ref2 FOR sql_stmt2 USING c_rec.param_id, l_max_action_param_set_id;
1181 LOOP
1182 FETCH c_ref2 INTO l_property_id, l_property_value,l_value_override_flag,l_not_valid_flag;
1183 EXIT WHEN c_ref2%NOTFOUND;
1184
1185 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
1186 /*TYPE IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type IS RECORD (
1187 PARAM_PROPERTY_ID NUMBER(15),
1188 ACTION_PARAM_SET_ID NUMBER,
1189 PARAM_ID NUMBER,
1190 PROPERTY_ID NUMBER,
1191 PROPERTY_VALUE VARCHAR(4000),
1192 PROPERTY_VALUE_TL VARCHAR(4000),
1193 VALUE_OVERRIDE_FLAG VARCHAR2(5),
1194 created_by NUMBER(15),
1195 creation_date DATE,
1196 last_updated_by NUMBER(15),
1197 last_update_date DATE,
1198 last_update_login NUMBER(15),
1199 not_valid_flag VARCHAR(4000),
1200 owner VARCHAR2(15) );
1201 */
1202 param_props_rec.PARAM_PROPERTY_ID := l_param_property_id;
1203 param_props_rec.ACTION_PARAM_SET_ID := l_action_param_set_id;
1204 param_props_rec.PARAM_ID := c_rec.param_id;
1205 param_props_rec.PROPERTY_ID := l_property_id;
1206 param_props_rec.PROPERTY_VALUE := l_property_value;
1207 param_props_rec.PROPERTY_VALUE_TL := l_property_value;
1208 param_props_rec.VALUE_OVERRIDE_FLAG := l_value_override_flag;
1209 param_props_rec.created_by := FND_GLOBAL.USER_ID;
1210 param_props_rec.creation_date := SYSDATE;
1211 param_props_rec.last_updated_by := FND_GLOBAL.USER_ID;
1212 param_props_rec.last_update_date := SYSDATE;
1213 param_props_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1214 param_props_rec.NOT_VALID_FLAG :=l_not_valid_flag;
1215 param_props_rec.owner := 1;
1216 IEU_WP_PARAM_PROPS_SEED_PKG.Insert_Row(p_wp_param_props_rec => param_props_rec);
1217 end loop;-- for v_cursor2
1218 --DBMS_SQL.CLOSE_CURSOR(v_cursor2);
1219 CLOSE c_ref2;
1220 END IF ; -- for max action param set is not null
1221 end LOOP;-- for v_cursor1
1222 -- DBMS_SQL.CLOSE_CURSOR(v_cursor1);
1223 CLOSE c_ref;
1224
1225
1226 end LOOP; -- for c_cur
1227 CLOSE c_cur;
1228
1229 --DBMS_OUTPUT.Put_Line('beore delete data type loop');
1230 v_data_type := 'DATE';
1231 OPEN c_cur2;
1232 loop
1233 FETCH c_cur2 INTO c_rec2;
1234 EXIT WHEN c_cur2%NOTFOUND;
1235 l_temp := c_rec2.action_param_set_id;
1236 l_ptemp := c_rec2.param_id;
1237 --v_cursor := DBMS_SQL.OPEN_CURSOR;
1238 sql_stmt1 :=' select param_property_id '||
1239 ' from ieu_wp_param_props_b '||
1243 OPEN c_ref2 FOR sql_stmt1 USING l_temp, l_ptemp;
1240 ' where action_param_set_id = :temp '||
1241 ' and property_id = 10003'||
1242 ' and param_id = :ptemp ';
1244 LOOP
1245 FETCH c_ref2 INTO l_delete_param_property_id;
1246 EXIT WHEN c_ref2%NOTFOUND;
1247 select VALUE_TRANSLATABLE_FLAG into l_trans_flag
1248 from ieu_wp_properties_b
1249 where property_id = 10003;
1250
1251 if (l_delete_param_property_id is not null ) then
1252 if (l_trans_flag = 'Y') then
1253 update ieu_wp_param_props_tl set
1254 PROPERTY_VALUE = null
1255 where param_property_id = l_delete_param_property_id;
1256 end if ;
1257
1258 update ieu_wp_param_props_b set
1259 PROPERTY_ID =10022,
1260 PROPERTY_VALUE = null
1261 where param_property_id = l_delete_param_property_id;
1262
1263 end if;
1264
1265 end LOOP;
1266 CLOSE c_ref2;
1267 end LOOP; -- for c_cur2
1268 CLOSE c_cur2;
1269 commit;
1270 EXCEPTION
1271 WHEN fnd_api.g_exc_error THEN
1272 ROLLBACK;
1273 x_return_status := fnd_api.g_ret_sts_error;
1274
1275 WHEN fnd_api.g_exc_unexpected_error THEN
1276 ROLLBACK;
1277 x_return_status := fnd_api.g_ret_sts_unexp_error;
1278
1279 WHEN OTHERS THEN
1280 ROLLBACK;
1281 x_return_status := fnd_api.g_ret_sts_unexp_error;
1282
1283 end UpdateParamProps;
1284
1285
1286
1287 PROCEDURE ReOrdering( x_return_status OUT NOCOPY VARCHAR2,
1288 x_msg_count OUT NOCOPY NUMBER,
1289 x_msg_data OUT NOCOPY VARCHAR2,
1290 r_enumId IN NUMBER,
1291 r_applId IN NUMBER,
1292 r_panel IN VARCHAR2)
1293 As
1294
1295 v_enumId ieu_uwq_Sel_enumerators.sel_enum_id%type;
1296 v_applId ieu_wp_action_maps.application_id%type;
1297 v_panel ieu_uwq_maction_defs_b.maction_def_type_flag%type;
1298
1299 cursor c_cur is
1300 select d.action_param_set_label, b.action_map_sequence,
1301 d.action_param_set_desc , e.action_user_label, b.not_valid_flag,c.action_param_set_id
1302 from ieu_uwq_Sel_enumerators a, ieu_wp_action_maps b,
1303 ieu_wp_act_param_sets_b c, ieu_wp_act_param_sets_tl d,
1304 ieu_uwq_maction_defs_tl e , ieu_uwq_maction_defs_b f
1305 where a.sel_enum_id =v_enumId
1306 and f.maction_def_type_flag = v_panel
1307 and e.language = FND_GLOBAL.CURRENT_LANGUAGE
1308 and a.enum_type_uuid = b.action_map_code
1309 and c.action_param_set_id = b.action_param_set_id
1310 -- and b.application_id = v_applId bug#5585922
1311 and c.wp_action_def_id = e.maction_def_id
1312 and b.responsibility_id = -1
1313 and d.action_param_set_id = c.action_param_set_id
1314 and e.maction_def_id =f.maction_def_id
1315 AND d.language=FND_GLOBAL.CURRENT_LANGUAGE
1316 order by b.action_map_sequence;
1317
1318 c_rec c_cur%ROWTYPE;
1319
1320 v_action_map_type_code ieu_wp_action_maps.action_map_type_code%type;
1321
1322 cursor c_cur2 is
1323 select d.action_param_set_label, b.action_map_sequence,
1324 d.action_param_set_desc , e.action_user_label, b.not_valid_flag,c.action_param_set_id
1325 from ieu_uwq_Sel_enumerators a, ieu_wp_action_maps b,
1326 ieu_wp_act_param_sets_b c, ieu_wp_act_param_sets_tl d,
1327 ieu_uwq_maction_defs_tl e , ieu_uwq_maction_defs_b f, ieu_uwq_node_ds ds
1328 where a.sel_enum_id =v_enumId
1329 and f.maction_def_type_flag = v_panel
1330 and e.language = FND_GLOBAL.CURRENT_LANGUAGE
1331 AND d.language=FND_GLOBAL.CURRENT_LANGUAGE
1332 and a.enum_type_uuid = ds.enum_type_uuid
1333 AND b.ACTION_MAP_TYPE_CODE = v_action_map_type_code
1334 AND to_char(ds.NODE_DS_ID) = b.ACTION_MAP_CODE
1335 and c.action_param_set_id = b.action_param_set_id
1336 -- and b.application_id = v_applId bug#5585922
1337 and c.wp_action_def_id = e.maction_def_id
1338 and b.responsibility_id = -1
1339 and d.action_param_set_id = c.action_param_set_id
1340 and e.maction_def_id =f.maction_def_id
1341 order by b.action_map_sequence;
1342
1343 c_rec2 c_cur2%ROWTYPE;
1344
1345 l_count NUMBER:=1;
1346 begin
1347 fnd_msg_pub.delete_msg();
1348 x_return_status := fnd_api.g_ret_sts_success;
1349 FND_MSG_PUB.initialize;
1350
1351 x_msg_data := '';
1352 if (r_panel = 'F') then
1353 v_enumId := r_enumId;
1354 v_panel := r_panel;
1355 v_applId := r_applId;
1356 v_action_map_type_code := 'NODE_DS';
1357 OPEN c_cur2;
1358 loop
1359 FETCH c_cur2 INTO c_rec2;
1360 EXIT WHEN c_cur2%NOTFOUND;
1361 if (c_rec2.action_map_sequence IS NULL OR l_count <> c_rec2.action_map_sequence) then
1362
1363 IF (c_rec2.action_map_sequence IS null) THEN
1364 EXECUTE immediate
1365 ' update ieu_wp_action_maps '||
1366 ' set action_map_sequence = :1 '||
1367 ' where action_map_sequence IS null '||
1368 ' and ACTION_MAP_TYPE_CODE = :2 '||
1369 ' and action_param_set_id = :3 '||
1370 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
1371 --' and action_param_set_id = :4 '||
1372 /*********************************************************************************/
1373 ' and action_map_code in (select to_char(node_ds_id) ' ||
1374 ' from ieu_uwq_node_ds ' ||
1375 ' where enum_type_uuid in (select enum_type_uuid '||
1376 ' from ieu_uwq_sel_enumerators '||
1380 --USING l_count, r_applId,'NODE_DS', c_rec2.action_param_set_id, r_enumId ;
1377 ' where sel_enum_id = :5 '||
1378 ' ) '||
1379 ' ) '
1381 USING l_count, 'NODE_DS', c_rec2.action_param_set_id, r_enumId ;
1382 ELSE
1383 EXECUTE immediate
1384 ' update ieu_wp_action_maps '||
1385 ' set action_map_sequence = :1 '||
1386 ' where action_map_sequence = :2 '||
1387 ' and ACTION_MAP_TYPE_CODE = :3 ' ||
1388 ' and action_param_set_id = :4 '||
1389 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
1390 --' and action_param_set_id = :5 '||
1391 /*********************************************************************************/
1392 ' and action_map_code in (select to_char(node_ds_id) '||
1393 ' from ieu_uwq_node_ds '||
1394 ' where enum_type_uuid in (select enum_type_uuid '||
1395 ' from ieu_uwq_sel_enumerators '||
1396 ' where sel_enum_id = :6'||
1397 ' ) '||
1398 ' )'
1399 --USING l_count, r_applId, c_rec2.action_map_sequence,'NODE_DS', c_rec2.action_param_set_id,
1400 USING l_count, c_rec2.action_map_sequence,'NODE_DS', c_rec2.action_param_set_id,
1401 c_rec2.action_param_set_id;
1402 END IF ;
1403 end if;
1404 l_count :=l_count+1;
1405 end loop;
1406 CLOSE c_cur2;
1407 else
1408 v_enumId := r_enumId;
1409 v_panel := r_panel;
1410 v_applId := r_applId;
1411 OPEN c_cur;
1412 loop
1413 FETCH c_cur INTO c_rec;
1414 EXIT WHEN c_cur%NOTFOUND;
1415 if (c_rec.action_map_sequence IS NULL OR l_count <> c_rec.action_map_sequence) then
1416 IF (c_rec.action_map_sequence IS null) THEN
1417 EXECUTE immediate
1418 ' update ieu_wp_action_maps '||
1419 ' set action_map_sequence = :1 '||
1420 ' where action_map_sequence IS null '||
1421 ' and action_param_set_id = :2 '||
1422 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
1423 --' and action_param_set_id = :3 '||
1424 /*********************************************************************************/
1425 ' and action_map_code in (select enum_type_uuid '||
1426 ' from ieu_uwq_sel_enumerators '||
1427 ' where sel_enum_id = :3) '
1428 --USING l_count,r_applId, c_rec.action_param_set_id, r_enumId ;
1429 USING l_count, c_rec.action_param_set_id, r_enumId ;
1430 ELSE
1431 EXECUTE immediate
1432 ' update ieu_wp_action_maps '||
1433 ' set action_map_sequence = :1'||
1434 ' where action_map_sequence = :2 '||
1435 ' and action_param_set_id = :3 '||
1436 /*******************ADD FOR FORWARD PORT BUG5585922 BY MAJHA**********************/
1437 -- ' and action_param_set_id = :4 '||
1438 /*********************************************************************************/
1439 ' and action_map_code in (select enum_type_uuid '||
1440 ' from ieu_uwq_sel_enumerators '||
1441 ' where sel_enum_id = :4) '
1442 --USING l_count , r_applId, c_rec.action_map_sequence, c_rec.action_param_set_id, r_enumId;
1443 USING l_count , c_rec.action_map_sequence, c_rec.action_param_set_id, r_enumId;
1444 END IF ;
1445 end if;
1446 l_count :=l_count+1;
1447 end loop;
1448 CLOSE c_cur;
1449 end if ;
1450 commit;
1451 end ReOrdering;
1452
1453
1454
1455
1456 PROCEDURE CreateFromQFilter( x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2,
1459 r_wp_action_key IN VARCHAR2,
1460 r_language IN VARCHAR2,
1461 r_label IN VARCHAR2,
1462 r_desc IN VARCHAR2,
1463 r_param_set_id IN NUMBER,
1464 r_enumId IN VARCHAR2,
1465 r_dev_data_flag IN VARCHAR2)
1466
1467 AS
1468
1469 l_wp_maction_def_id NUMBER(15);
1470 v_wp_action_key varchar2(500);
1471 l_param_set_id NUMBER(15);
1472 l_language VARCHAR2(4);
1473 l_source_lang VARCHAR2(4);
1474 l_msg_count NUMBER(2);
1475
1476 l_msg_data VARCHAR2(2000);
1477
1478 l_param_property_id IEU_WP_PARAM_PROPS_B.PARAM_PROPERTY_ID%TYPE;
1479
1480 l_trans_flag IEU_WP_PROPERTIES_B.VALUE_TRANSLATABLE_FLAG%TYPE;
1481
1482 v_cursor1 NUMBER;
1483 sql_stmt varchar2(2000);
1484 sql_stmt1 varchar2(2000);
1485 l_param_id NUMBER(15);
1486 l_property_id NUMBER(15);
1487 l_property_value varchar(4000);
1488 l_not_valid_flag varchar(5);
1489 l_value_override_flag varchar(5);
1490 v_numrows1 NUMBER;
1491 l_new_param_set_id NUMBER(15);
1492 l_wp_action_map_id NUMBER(15);
1493 l_temp_map_sequence ieu_wp_action_maps.action_map_sequence%type;
1494 param_sets_rec IEU_WP_ACT_PARAM_SETS_SEED_PKG.WP_ACT_PARAM_SETS_rec_type;
1495 param_props_rec IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type;
1496 v_SelectStmt Varchar2(500);
1497 v_CursorID INTEGER;
1498 v_Dummy INTEGER;
1502 l_security_group_id NUMBER(15);
1499 v_param_set_id ieu_wp_act_param_sets_tl.action_param_set_id%type;
1500 v_language ieu_wp_act_param_sets_tl.language%type;
1501 v_enumId ieu_uwq_sel_enumerators.sel_enum_id%type;
1503
1504 cursor c_cur is
1505 SELECT
1506 PARAM_ID, PROPERTY_ID,property_value
1507 , value_override_flag,not_valid_flag
1508 FROM ieu_wp_param_props_b
1509 WHERE action_param_set_id in
1510 (select a.action_param_set_id
1511 from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
1512 where a.action_param_set_id = b.action_param_set_id(+)
1513 and b.action_param_set_id = v_param_set_id
1514 and c.maction_def_key = LTRIM(RTRIM(v_wp_action_key))
1515 and b.language = v_language
1516 and c.maction_def_id = a.wp_action_def_id
1517 and a.action_param_set_id in (select action_param_set_id
1518 from ieu_wp_action_maps
1519 where action_map_code in (SELECT to_char(ds.NODE_DS_ID) FROM ieu_uwq_sel_enumerators e,
1520 ieu_uwq_node_ds ds
1521 WHERE e.sel_enum_id = v_enumId
1522 and e.ENUM_TYPE_UUID = ds.ENUM_TYPE_UUID)
1523 )
1524 )
1525 ;
1526
1527
1528 -- this c_cur2 will NOT get responsibility information FROM original action
1529 cursor c_cur2 is
1530 SELECT
1531 object_version_number, application_id, action_map_type_code,
1532 action_map_code, panel_sec_cat_code, not_valid_flag
1533 FROM ieu_wp_action_maps
1534 WHERE responsibility_id = -1
1535 AND action_param_set_id in
1536 (select a.action_param_set_id
1537 from ieu_wp_act_param_sets_b a, ieu_wp_act_param_sets_tl b, ieu_uwq_maction_defs_b c
1538 where a.action_param_set_id = b.action_param_set_id(+)
1539 and b.action_param_set_id = v_param_set_id
1540 and c.maction_def_key = LTRIM(RTRIM(v_wp_action_key))
1541 and b.language = v_language
1542 and c.maction_def_id = a.wp_action_def_id)
1543 and action_map_code in (SELECT to_char(ds.NODE_DS_ID) FROM ieu_uwq_sel_enumerators e, ieu_uwq_node_ds ds
1544 WHERE e.sel_enum_id = v_enumId
1545 and e.ENUM_TYPE_UUID = ds.ENUM_TYPE_UUID);
1546 c_rec c_cur%ROWTYPE;
1547 c_rec2 c_cur2%ROWTYPE;
1548
1549 BEGIN
1550
1551 fnd_msg_pub.delete_msg();
1552 x_return_status := fnd_api.g_ret_sts_success;
1553 FND_MSG_PUB.initialize;
1554 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1555 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1556 x_msg_data := '';
1557
1558 execute immediate ' select a.wp_action_def_id ' ||
1559 ' from ieu_wp_act_param_sets_b a, ieu_uwq_maction_defs_b c ' ||
1560 ' where a.action_param_set_id = :1 ' ||
1561 ' and c.maction_def_key = :2 ' ||
1562 ' and c.maction_def_id = a.wp_action_def_id '
1563 into l_wp_maction_def_id using r_param_set_id, r_wp_action_key;
1564
1565
1566 if l_wp_maction_def_id is not null then
1567 select IEU_wp_act_param_sets_b_S1.NEXTVAL into l_new_param_set_id from sys.dual;
1568 /* TYPE IEU_WP_ACT_PARAM_SETS_SEED_PKG.WP_ACT_PARAM_SETS_rec_type IS RECORD (
1569 ACTION_PARAM_SET_ID NUMBER(15),
1570 WP_ACTION_DEF_ID NUMBER(15),
1571 ACTION_PARAM_SET_LABEL VARCHAR2(128),
1572 ACTION_PARAM_SET_DESC VARCHAR2(500),
1573 created_by NUMBER(15),
1574 creation_date DATE,
1575 last_updated_by NUMBER(15),
1576 last_update_date DATE,
1577 last_update_login NUMBER(15),
1578 owner VARCHAR2(15) );
1579 */
1580 param_sets_rec.ACTION_PARAM_SET_ID := l_new_param_set_id;
1581 param_sets_rec.WP_ACTION_DEF_ID := l_wp_maction_def_id;
1582 param_sets_rec.ACTION_PARAM_SET_LABEL := LTRIM(RTRIM(r_label));
1583 param_sets_rec.ACTION_PARAM_SET_DESC := LTRIM(RTRIM(r_desc));
1584 param_sets_rec.created_by := FND_GLOBAL.USER_ID;
1585 param_sets_rec.creation_date := SYSDATE;
1586 param_sets_rec.last_updated_by := FND_GLOBAL.USER_ID;
1587 param_sets_rec.last_update_date := SYSDATE;
1588 param_sets_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1589 param_sets_rec.owner := 1;
1590 IEU_WP_ACT_PARAM_SETS_SEED_PKG.Insert_Row(p_WP_ACT_PARAM_SETS_rec=>param_Sets_rec);
1591 end if ;
1592
1593 v_wp_action_key := r_wp_action_key;
1594 v_param_set_id := r_param_set_id;
1595 v_language := r_language;
1596 v_enumId := r_enumId;
1597
1598 OPEN c_cur;
1599 Loop
1600 FETCH c_cur INTO c_rec;
1601 EXIT WHEN c_cur%NOTFOUND;
1602 select IEU_WP_PARAM_PROPS_B_S1.NEXTVAL into l_param_property_id from sys.dual;
1603 /*TYPE IEU_WP_PARAM_PROPS_SEED_PKG.wp_param_props_rec_type IS RECORD (
1604 PARAM_PROPERTY_ID NUMBER(15),
1605 ACTION_PARAM_SET_ID NUMBER,
1606 PARAM_ID NUMBER,
1607 PROPERTY_ID NUMBER,
1608 PROPERTY_VALUE VARCHAR(4000),
1609 PROPERTY_VALUE_TL VARCHAR(4000),
1610 VALUE_OVERRIDE_FLAG VARCHAR2(5),
1611 created_by NUMBER(15),
1612 creation_date DATE,
1613 last_updated_by NUMBER(15),
1614 last_update_date DATE,
1615 last_update_login NUMBER(15),
1616 not_valid_flag VARCHAR(4000),
1617 owner VARCHAR2(15) );
1618 */
1619 param_props_rec.PARAM_PROPERTY_ID := l_param_property_id;
1620 param_props_rec.ACTION_PARAM_SET_ID := l_new_param_set_id;
1621 param_props_rec.PARAM_ID := c_rec.param_id;
1622 param_props_rec.PROPERTY_ID := c_rec.property_id;
1623 param_props_rec.PROPERTY_VALUE := c_rec.property_value;
1624 param_props_rec.PROPERTY_VALUE_TL := c_rec.property_value;
1625 param_props_rec.VALUE_OVERRIDE_FLAG := c_rec.value_override_flag;
1626 param_props_rec.created_by := FND_GLOBAL.USER_ID;
1627 param_props_rec.creation_date := SYSDATE;
1628 param_props_rec.last_updated_by := FND_GLOBAL.USER_ID;
1629 param_props_rec.last_update_date := SYSDATE;
1630 param_props_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1631 param_props_rec.NOT_VALID_FLAG := c_rec.not_valid_flag;
1632 param_props_rec.owner := 1;
1633 IEU_WP_PARAM_PROPS_SEED_PKG.Insert_Row(p_wp_param_props_rec => param_props_rec);
1634 end loop;
1635 CLOSE c_cur;
1636
1637
1638 OPEN c_cur2;
1639 Loop
1640 FETCH c_cur2 INTO c_rec2;
1641 EXIT WHEN c_cur2%NOTFOUND;
1642 l_temp_map_sequence := 1;
1643 --INSERT one RECORD WITH responsibility_id = -1
1644 select IEU_WP_ACTION_MAPS_S1.NEXTVAL into l_wp_action_map_id from sys.dual;
1645 EXECUTE immediate
1646 ' insert INTO IEU_WP_ACTION_MAPS '||
1647 ' (WP_ACTION_MAP_ID, '||
1648 ' CREATED_BY, '||
1649 ' CREATION_DATE, '||
1650 ' LAST_UPDATED_BY, '||
1651 ' LAST_UPDATE_DATE, '||
1652 ' LAST_UPDATE_LOGIN, '||
1653 ' ACTION_PARAM_SET_ID, '||
1654 ' APPLICATION_ID, '||
1655 ' RESPONSIBILITY_ID, '||
1656 ' ACTION_MAP_TYPE_CODE, '||
1657 ' ACTION_MAP_CODE, '||
1658 ' PANEL_SEC_CAT_CODE, '||
1659 ' NOT_VALID_FLAG, '||
1660 ' OBJECT_VERSION_NUMBER, '||
1661 ' Security_group_id, '||
1662 ' action_map_sequence, '||
1663 ' DEV_DATA_FLAG '||
1664 ' ) VALUES ( '||
1665 ' :1, '||
1666 ' :2, '||
1667 ' :3, '||
1668 ' :4, '||
1669 ' :5, '||
1670 ' :6, '||
1671 ' :7 , '||
1672 ' :8 , '||
1673 ' :9, '||
1674 ' :10 , '||
1675 ' :11, '||
1676 ' :12, '||
1677 ' :13, '||
1678 ' :14, '||
1679 ' :15 , '||
1680 ' :16 , '||
1681 ' :17 '||
1682 ' ) '
1683 USING l_wp_action_map_id, FND_GLOBAL.USER_ID, SYSDATE,FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.LOGIN_ID,
1684 l_new_param_set_id, c_rec2.application_id,'-1', c_rec2.action_map_type_code,
1685 c_rec2.action_map_code,c_rec2.panel_sec_cat_code, c_rec2.not_valid_flag, c_rec2.object_version_number,
1686 l_security_group_id, l_temp_map_sequence, r_dev_data_flag;
1687
1688
1689 end loop;
1690 CLOSE c_cur2;
1691 commit;
1692 EXCEPTION
1693 WHEN fnd_api.g_exc_error THEN
1694 ROLLBACK;
1695 x_return_status := fnd_api.g_ret_sts_error;
1696
1697 WHEN fnd_api.g_exc_unexpected_error THEN
1698 ROLLBACK;
1699 x_return_status := fnd_api.g_ret_sts_unexp_error;
1700
1701 WHEN OTHERS THEN
1702 ROLLBACK;
1703 x_return_status := fnd_api.g_ret_sts_unexp_error;
1704
1705
1706 end CreateFromQFilter;
1707
1708
1709 END ieu_wp_action_pvt;