[Home] [Help]
PACKAGE BODY: APPS.AMW_AUDIT_ENGAGEMENT_PVT
Source
1 PACKAGE BODY AMW_AUDIT_ENGAGEMENT_PVT AS
2 /* $Header: amwvengb.pls 120.3 2008/02/08 14:24:50 adhulipa ship $ */
3 /*===========================================================================*/
4
5
6 PROCEDURE copy_scope_from_engagement(
7 p_source_entity_id IN NUMBER,
8 p_target_entity_id IN NUMBER,
9 l_copy_ineff_controls boolean :=false,
10 x_return_status OUT nocopy VARCHAR2
11 ) IS
12
13
14 l_source varchar2(3):= 'PA';
15 l_scope_exits varchar2(1);
16
17 l_parent_task_id AMW_AUDIT_TASKS_B.PARENT_TASK_ID%TYPE;
18 l_top_task_id AMW_AUDIT_TASKS_B.TOP_TASK_ID%TYPE;
19
20 l_return_status VARCHAR2(32767);
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(32767);
23
24 cursor c_child_tasks IS
25 SELECT audit_project_id, task_id, parent_task_id, top_task_id
26 FROM amw_audit_tasks_b
27 WHERE audit_project_id = p_target_entity_id
28 AND parent_task_id is not null;
29
30 BEGIN
31
32 /*---------------------------------------------------+
33 | The scope needs to be copied only when it exists. |
34 | Templates do not have scope. |
35 +---------------------------------------------------*/
36 BEGIN
37 select 'Y' into l_scope_exits
38 from dual
39 where exists ( select 1
40 from amw_execution_scope
41 where entity_type = 'PROJECT'
42 and entity_id = p_source_entity_id);
43 EXCEPTION
44 WHEN no_data_found THEN
45 l_scope_exits := 'N';
46 END;
47
48 /*---------------------------------------------------+
49 | Copy the tasks only when the source is ICM. |
50 | For PA , the tasks are copied in PA. |
51 +---------------------------------------------------*/
52 BEGIN
53 select 'ICM' into l_source
54 from amw_audit_projects
55 where audit_project_id = p_source_entity_id
56 and project_id is null;
57 EXCEPTION
58 WHEN no_data_found THEN
59 l_source := 'PA';
60 END;
61
62 IF (l_scope_exits = 'Y') THEN
63 IF(l_copy_ineff_controls)
64
65 THEN
66 COPY_SCOPE_INEFF_CONTROLS(p_source_entity_id,p_target_entity_id,x_return_status);
67 ElSE
68 INSERT INTO AMW_EXECUTION_SCOPE (
69 EXECUTION_SCOPE_ID,
70 ENTITY_TYPE,
71 ENTITY_ID,
72 CREATED_BY,
73 CREATION_DATE,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN,
77 SCOPE_CHANGED_STATUS,
78 LEVEL_ID,
79 SUBSIDIARY_VS,
80 SUBSIDIARY_CODE,
81 LOB_VS,
82 LOB_CODE,
83 ORGANIZATION_ID,
84 PROCESS_ID,
85 PROCESS_ORG_REV_ID,
86 TOP_PROCESS_ID,
87 PARENT_PROCESS_ID)
88 SELECT amw_execution_scope_s.nextval,
89 'PROJECT',
90 p_target_entity_id,
91 FND_GLOBAL.USER_ID,
92 SYSDATE,
93 SYSDATE,
94 FND_GLOBAL.USER_ID,
95 FND_GLOBAL.USER_ID,
96 'C',
97 LEVEL_ID,
98 SUBSIDIARY_VS,
99 SUBSIDIARY_CODE,
100 LOB_VS,
101 LOB_CODE,
102 ORGANIZATION_ID,
103 PROCESS_ID,
104 PROCESS_ORG_REV_ID,
105 TOP_PROCESS_ID,
106 PARENT_PROCESS_ID
107 FROM AMW_EXECUTION_SCOPE
108 WHERE ENTITY_TYPE = 'PROJECT'
109 AND ENTITY_ID = p_source_entity_id;
110 END IF;
111
112 /* Insert data into entity hierarchies table */
113
114 INSERT INTO AMW_ENTITY_HIERARCHIES(
115 ENTITY_HIERARCHY_ID,
116 ENTITY_TYPE,
117 ENTITY_ID,
118 CREATED_BY,
119 CREATION_DATE,
120 LAST_UPDATE_DATE,
121 LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN,
123 OBJECT_TYPE,
124 OBJECT_ID,
125 PARENT_OBJECT_TYPE,
126 PARENT_OBJECT_ID,
127 LEVEL_ID)
128 SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
129 ENTITY_TYPE,
130 p_target_entity_id,
131 FND_GLOBAL.USER_ID,
132 SYSDATE,
133 SYSDATE,
134 FND_GLOBAL.USER_ID,
135 FND_GLOBAL.USER_ID,
136 OBJECT_TYPE,
137 OBJECT_ID,
138 PARENT_OBJECT_TYPE,
139 PARENT_OBJECT_ID,
140 LEVEL_ID
141 FROM AMW_ENTITY_HIERARCHIES
142 WHERE ENTITY_TYPE = 'PROJECT'
143 AND ENTITY_ID = p_source_entity_id;
144
145
146 END IF; --end of l_scope_exits == 'Y'
147
148 IF (l_source = 'ICM') THEN
149 INSERT into amw_audit_tasks_b(
150 TASK_ID,
151 AUDIT_PROJECT_ID,
152 TASK_NUMBER,
153 TOP_TASK_ID,
154 PARENT_TASK_ID,
155 LEVEL_ID,
156 START_DATE,
157 COMPLETION_DATE,
158 TASK_MANAGER_PERSON_ID,
159 CREATION_DATE,
160 CREATED_BY,
161 LAST_UPDATE_DATE,
162 LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN,
164 OBJECT_VERSION_NUMBER)
165 select amw_audit_tasks_s.nextval,
166 p_target_entity_id,
167 TASK_NUMBER,
168 TOP_TASK_ID,
169 PARENT_TASK_ID,
170 LEVEL_ID,
171 START_DATE,
172 COMPLETION_DATE,
173 TASK_MANAGER_PERSON_ID,
174 SYSDATE,
175 FND_GLOBAL.USER_ID,
176 SYSDATE,
177 FND_GLOBAL.USER_ID,
178 FND_GLOBAL.USER_ID,
179 1
180 from amw_audit_tasks_b
181 where audit_project_id = p_source_entity_id;
182
183 /* Insert data into the tl table */
184
185 INSERT INTO AMW_AUDIT_TASKS_TL(
186 TASK_ID,
187 TASK_NAME,
188 DESCRIPTION,
189 LANGUAGE,
190 SOURCE_LANG,
191 CREATION_DATE,
192 CREATED_BY,
193 LAST_UPDATE_DATE,
194 LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN,
196 OBJECT_VERSION_NUMBER)
197 select
198 b.TASK_ID,
199 stl.TASK_NAME,
200 stl.DESCRIPTION,
201 stl.LANGUAGE,
202 stl.SOURCE_LANG,
203 SYSDATE,
204 FND_GLOBAL.USER_ID,
205 SYSDATE,
206 FND_GLOBAL.USER_ID,
207 FND_GLOBAL.USER_ID,
208 1
209 from amw_audit_tasks_b b,
210 amw_audit_tasks_tl stl,
211 amw_audit_tasks_b sb
212 where sb.audit_project_id = p_source_entity_id
213 and sb.task_id = stl.task_id
214 and b.task_number = sb.task_number
215 and b.audit_project_id = p_target_entity_id;
216
217
218 /* Update the top_task_id for the top_tasks */
219
220 UPDATE amw_audit_tasks_b
221 SET top_task_id = task_id
222 WHERE audit_project_id = p_target_entity_id
223 AND parent_task_id is null;
224
225
226 FOR child_tasks IN c_child_tasks LOOP
227
228 select target.task_id into l_parent_task_id
229 from amw_audit_tasks_b source,
230 amw_audit_tasks_b target
231 where source.task_id = child_tasks.parent_task_id
232 and source.audit_project_id = p_source_entity_id
233 and target.task_number = source.task_number
234 and target.audit_project_id = p_target_entity_id;
235
236 select target.task_id into l_top_task_id
237 from amw_audit_tasks_b source,
238 amw_audit_tasks_b target
239 where source.task_id = child_tasks.top_task_id
240 and source.audit_project_id = p_source_entity_id
241 and target.task_number = source.task_number
242 and target.audit_project_id = p_target_entity_id;
243
244 UPDATE amw_audit_tasks_b
245 SET parent_task_id = l_parent_task_id,
246 top_task_id = l_top_task_id
247 WHERE task_id = child_tasks.task_id;
248
249 END LOOP;
250 END IF;
251 /* populate the denorm tables and build task */
252 IF (l_scope_exits = 'Y') THEN
253 AMW_SCOPE_PVT.populate_proj_denorm_tables(p_audit_project_id => p_target_entity_id);
254
255 IF (l_copy_ineff_controls) THEN
256 AMW_SCOPE_PVT.build_project_audit_task
257 (
258 p_api_version_number => 1.0 ,
259 p_audit_project_id => p_target_entity_id,
260 l_ineff_controls =>true,
261 p_source_project_id => p_source_entity_id,
262 x_return_status => l_return_status,
263 x_msg_count => l_msg_count,
264 x_msg_data => l_msg_data
265 );
266 ELSE
267 AMW_SCOPE_PVT.build_project_audit_task
268 (
269 p_api_version_number => 1.0 ,
270 p_audit_project_id => p_target_entity_id,
271 x_return_status => l_return_status,
272 x_msg_count => l_msg_count,
273 x_msg_data => l_msg_data
274 );
275 END IF;
276 END IF;
277
278 IF(l_copy_ineff_controls)THEN
279 AMW_AUDIT_ENGAGEMENT_PVT.cp_tasks(
280 p_source_project_id=>p_source_entity_id,
281 p_dest_project_id=> p_target_entity_id,
282 x_return_status => x_return_status
283 );
284 ELSE
285 AMW_AUDIT_ENGAGEMENT_PVT.cp_tasks_all(
286 p_source_project_id=>p_source_entity_id,
287 p_dest_project_id=> p_target_entity_id,
288 x_return_status => x_return_status
289 );
290
291 END IF;
292
293
294 x_return_status := 'S';
295
296 EXCEPTION
297 when OTHERS then
298 x_return_status := 'E';
299
300 END copy_scope_from_engagement;
301
302
303 PROCEDURE create_engagement_for_pa(
304 p_project_id IN NUMBER,
305 p_audit_project_id OUT nocopy NUMBER,
306 x_return_status OUT nocopy VARCHAR2
307 ) IS
308
309 l_audit_project_id NUMBER ;
310 l_engagement_type_id NUMBER;
311
312 BEGIN
313
314
315 select AMW_AUDIT_PROJECTS_S.nextval into l_audit_project_id FROM DUAL;
316
317 select typ.work_type_id into l_engagement_type_id
318 from pa_projects_all ppa,
319 amw_work_types_b typ,
320 pa_project_types_all pt
321 where ppa.project_id = p_project_id
322 and ppa.project_type = pt.project_type
323 and pt.project_type_id = typ.project_type_id;
324
325 INSERT INTO AMW_AUDIT_PROJECTS (
326 AUDIT_PROJECT_ID,
327 PROJECT_ID,
328 ENGAGEMENT_TYPE_ID,
329 AUDIT_PROJECT_STATUS,
330 SIGN_OFF_STATUS,
331 TEMPLATE_FLAG,
332 CREATED_BY,
333 CREATION_DATE,
334 LAST_UPDATE_DATE,
335 LAST_UPDATED_BY,
336 LAST_UPDATE_LOGIN,
337 OBJECT_VERSION_NUMBER)
338 SELECT l_audit_project_id,
339 p_project_id,
340 l_engagement_type_id,
341 'ACTI',
342 'NOT_SUBMITTED',
343 'N',
344 FND_GLOBAL.USER_ID,
345 SYSDATE,
346 SYSDATE,
347 FND_GLOBAL.USER_ID,
348 FND_GLOBAL.USER_ID,
349 1
350 FROM dual
351 WHERE not exists (SELECT 'Y'
352 FROM AMW_AUDIT_PROJECTS
353 WHERE PROJECT_ID = p_project_id);
354
355 p_audit_project_id := l_audit_project_id ;
356 x_return_status := 'S';
357 COMMIT;
358 EXCEPTION
359 WHEN OTHERS THEN
360 x_return_status := 'E';
361
362 END create_engagement_for_pa;
363
364
365
366 PROCEDURE create_engagement_in_pa(
367 p_created_from_project_id IN NUMBER,
368 p_project_name IN VARCHAR2,
369 p_project_number IN VARCHAR2,
370 p_project_description IN VARCHAR2,
371 p_project_manager IN NUMBER,
372 p_project_status IN VARCHAR2,
373 p_start_date IN DATE,
374 p_completion_date IN DATE,
375 p_project_id OUT nocopy NUMBER,
376 p_msg_data OUT nocopy VARCHAR2,
377 x_return_status OUT nocopy VARCHAR2
378 ) IS
379
380
381 l_commit VARCHAR2(1) := 'F';
382 l_init_msg_list VARCHAR2(1) := 'F';
383 l_msg_count NUMBER;
384 l_msg_data VARCHAR2(2000);
385 -- x_return_status VARCHAR2(1);
386 l_workflow_started VARCHAR2(1);
387
388 G_PM_PRODUCT_CODE VARCHAR2(30) := 'AMW';
389
390 -- Define local table and record datatypes
391 l_project_rec PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
392 l_project_out PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
393
394 l_task_in PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
395 l_task_out PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
396 l_key_members PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
397 l_class_categories PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
398
399 l_data VARCHAR2(2000);
400 l_msg_index_out NUMBER;
401
402 l_return_status VARCHAR2(1);
403 i NUMBER;
404
405 cursor c_tasks IS
406 select *
407 from pa_tasks
408 where project_id = p_created_from_project_id
409 start with parent_task_id is null
410 connect by prior task_id = parent_task_id;
411
412 l_pm_parent_task_reference varchar2(25);
413
414
415 BEGIN
416
417 i := 1;
418 FOR task_rec IN c_tasks LOOP
419 l_task_in(i).PM_TASK_REFERENCE := task_rec.TASK_NUMBER;
420 l_task_in(i).PA_TASK_NUMBER := task_rec.TASK_NUMBER;
421 l_task_in(i).TASK_NAME := task_rec.TASK_NAME;
422 l_task_in(i).TASK_DESCRIPTION := task_rec.DESCRIPTION;
423
424 if task_rec.PARENT_TASK_ID is NOT NULL then
425 SELECT task_number into l_pm_parent_task_reference
426 FROM pa_tasks
427 WHERE task_id = task_rec.PARENT_TASK_ID;
428
429 l_task_in(i).PM_PARENT_TASK_REFERENCE := l_pm_parent_task_reference;
430 end if;
431
432 i := i+1;
433 END LOOP;
434
435 IF p_project_manager IS NOT NULL THEN
436 l_key_members(1).person_id := p_project_manager;
437 l_key_members(1).project_role_type := 'PROJECT MANAGER';
438 END IF;
439
440
441 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
442 p_api_version_number => 1,
443 p_responsibility_id => FND_GLOBAL.RESP_ID,
444 p_user_id => FND_GLOBAL.USER_ID,
445 p_resp_appl_id => 242,
446 p_msg_count => l_msg_count,
447 p_msg_data => l_msg_data,
448 p_return_status => l_return_status);
449
450
451 -- TO CREATE PROJECT IN PROJECTS
452 l_Project_rec.PM_PROJECT_REFERENCE := p_project_number;
453 l_Project_rec.PROJECT_NAME := p_project_name;
454 l_Project_rec.CREATED_FROM_PROJECT_ID := p_created_from_project_id;
455 l_Project_rec.PROJECT_STATUS_CODE := 'ACTIVE';
456 l_Project_rec.DESCRIPTION := p_project_description;
457 l_Project_rec.START_DATE := p_start_date;
458 l_Project_rec.COMPLETION_DATE := p_completion_date;
459 l_Project_rec.SCHEDULED_START_DATE := p_start_date;
460
461
462 FND_MSG_PUB.initialize;
463
464 PA_PROJECT_PUB.CREATE_PROJECT
465 (p_api_version_number => 1,
466 p_commit => l_commit,
467 p_init_msg_list => l_init_msg_list,
468 p_msg_count => l_msg_count,
469 p_msg_data => l_msg_data,
470 p_return_status => x_return_status,
471 p_workflow_started => l_workflow_started,
472 p_pm_product_code => 'AMW',
473 p_project_in => l_project_rec,
474 p_project_out => l_project_out,
475 p_key_members => l_key_members,
476 p_class_categories => l_class_categories,
477 p_tasks_in => l_task_in,
478 p_tasks_out => l_task_out
479 );
480
481 -- dbms_output.put_line('status :' || x_return_status);
482
483 IF x_return_status <> 'S' THEN
484 if l_msg_count > 0 THEN
485 for i in 1..l_msg_count loop
486 pa_interface_utils_pub.get_messages(
487 p_data => l_data
488 ,p_msg_index => i
489 ,p_msg_count => l_msg_count
490 ,p_msg_data => l_msg_data
491 ,p_msg_index_out => l_msg_index_out );
492
493 if l_data IS NOT NULL then
494 p_msg_data := p_msg_data || l_data;
495 end if;
496 -- dbms_output.put_line(l_data);
497 end loop;
498 end if;
499 END IF;
500 p_project_id := l_project_out.PA_PROJECT_ID;
501
502 END create_engagement_in_pa;
503
504
505
506
507
508 PROCEDURE update_engagement_in_pa(
509 p_project_id IN NUMBER,
510 p_project_name IN VARCHAR2,
511 p_project_number IN VARCHAR2,
512 p_project_description IN VARCHAR2,
513 p_project_manager IN NUMBER ,
514 p_project_status IN VARCHAR2 default 'ACTIVE',
515 p_start_date IN DATE default SYSDATE,
516 p_completion_date IN DATE default NULL,
517 p_sign_off_required IN VARCHAR2,
518 p_msg_data OUT nocopy VARCHAR2,
519 x_return_status OUT nocopy VARCHAR2
520 ) IS
521
522 l_commit VARCHAR2(1) := 'F';
523 l_init_msg_list VARCHAR2(1) := 'F';
524 l_msg_count NUMBER;
525 l_msg_data VARCHAR2(2000);
526 l_workflow_started VARCHAR2(1);
527
528
529 -- Define local table and record datatypes
530 l_project_rec PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
531 l_project_out PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
532
533 l_task_in PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
534 l_task_out PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
535 l_key_members PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
536 l_class_categories PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
537
538 l_data VARCHAR2(2000);
539 l_msg_index_out NUMBER;
540
541 l_return_status VARCHAR2(1);
542 l_created_from_project_id pa_projects_all.created_from_project_id%TYPE;
543 l_project_status pa_projects_all.project_status_code%TYPE;
544 l_project_number pa_projects_all.segment1%TYPE;
545 l_project_name pa_projects_all.name%TYPE;
546
547
548 BEGIN
549
550 select created_from_project_id, project_status_code, segment1, name
551 into l_created_from_project_id, l_project_status, l_project_number, l_project_name
552 from pa_projects_all
553 where project_id = p_project_id;
554
555 -- IF p_project_manager IS NOT NULL THEN
556 -- l_key_members(1).person_id := p_project_manager;
557 -- l_key_members(1).project_role_type := 'PROJECT MANAGER';
558 -- END IF;
559
560
561 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
562 p_api_version_number => 1,
563 p_responsibility_id => FND_GLOBAL.RESP_ID,
564 p_user_id => FND_GLOBAL.USER_ID,
565 p_resp_appl_id => 242,
566 p_msg_count => l_msg_count,
567 p_msg_data => l_msg_data,
568 p_return_status => l_return_status);
569
570
571
572 -- TO CREATE PROJECT IN PROJECTS
573 l_Project_rec.PM_PROJECT_REFERENCE := l_project_number;
574 l_Project_rec.PA_PROJECT_ID := p_project_id;
575 l_Project_rec.PROJECT_NAME := l_project_name;
576 l_Project_rec.CREATED_FROM_PROJECT_ID := l_created_from_project_id;
577 l_Project_rec.PROJECT_STATUS_CODE := l_project_status;
578 l_Project_rec.DESCRIPTION := p_project_description;
579 l_Project_rec.START_DATE := p_start_date;
580 l_Project_rec.COMPLETION_DATE := p_completion_date;
581 l_Project_rec.SCHEDULED_START_DATE := p_start_date;
582
583
584 FND_MSG_PUB.initialize;
585
586 PA_PROJECT_PUB.UPDATE_PROJECT
587 (p_api_version_number => 1,
588 p_commit => l_commit,
589 p_init_msg_list => l_init_msg_list,
590 p_msg_count => l_msg_count,
591 p_msg_data => l_msg_data,
592 p_return_status => x_return_status,
593 p_workflow_started => l_workflow_started,
594 p_pm_product_code => 'AMW',
595 p_project_in => l_project_rec,
596 p_project_out => l_project_out,
597 p_key_members => l_key_members,
598 p_class_categories => l_class_categories,
599 p_tasks_in => l_task_in,
600 p_tasks_out => l_task_out
601 );
602
603 --dbms_output.put_line('status :' || x_return_status);
604
605 IF x_return_status = 'S' THEN
606 /* Update the status in the icm table */
607 UPDATE amw_audit_projects
608 SET audit_project_status = p_project_status
609 WHERE project_id = p_project_id
610 AND audit_project_status <> p_project_status;
611
612 /* Update the signOffRequired flag in the icm table */
613 UPDATE amw_audit_projects
614 SET sign_off_required_flag = p_sign_off_required
615 WHERE project_id = p_project_id
616 AND NVL(sign_off_required_flag,'N') <> p_sign_off_required;
617 END IF;
618
619
620 IF x_return_status <> 'S' THEN
621 if l_msg_count > 0 THEN
622 for i in 1..l_msg_count loop
623 pa_interface_utils_pub.get_messages(
624 p_data => l_data
625 ,p_msg_index => i
626 ,p_msg_count => l_msg_count
627 ,p_msg_data => l_msg_data
628 ,p_msg_index_out => l_msg_index_out );
629
630 if l_data IS NOT NULL then
631 p_msg_data := p_msg_data || l_data;
632 end if;
633 -- dbms_output.put_line(l_data);
634 end loop;
635 end if;
636 END IF;
637
638
639 END update_engagement_in_pa;
640
641 PROCEDURE create_audit_task_in_pa(
642 p_project_id IN NUMBER,
643 p_parent_task_id IN NUMBER,
644 p_task_name IN VARCHAR2,
645 p_task_number IN VARCHAR2,
646 p_task_description IN VARCHAR2,
647 p_task_manager IN NUMBER,
648 p_start_date IN DATE ,
649 p_completion_date IN DATE ,
650 p_task_id OUT nocopy NUMBER,
651 p_msg_data OUT nocopy VARCHAR2,
652 x_return_status OUT nocopy VARCHAR2
653 ) IS
654
655
656 l_commit VARCHAR2(1) := 'F';
657 l_init_msg_list VARCHAR2(1) := 'F';
658 l_msg_count NUMBER;
659 l_msg_data VARCHAR2(2000);
660 l_msg_index_out NUMBER;
661 l_data VARCHAR2(2000);
662
663 l_pa_project_id_out NUMBER;
664 l_pa_project_number_out VARCHAR2(25);
665 l_task_id NUMBER;
666
667 l_prj_number VARCHAR2(20);
668 l_prj_ref VARCHAR2(20);
669 l_return_status VARCHAR2(1);
670
671
672 BEGIN
673
674 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
675 p_api_version_number => 1,
676 p_responsibility_id => FND_GLOBAL.RESP_ID,
677 p_user_id => FND_GLOBAL.USER_ID,
678 p_resp_appl_id => 242,
679 p_msg_count => l_msg_count,
680 p_msg_data => l_msg_data,
681 p_return_status => l_return_status);
682
683 select segment1,pm_project_reference into l_prj_number,l_prj_ref
684 from pa_projects_all
685 where project_id = p_project_id;
686
687 FND_MSG_PUB.initialize;
688
689 PA_PROJECT_PUB.ADD_TASK(
690 p_api_version_number => 1
691 ,p_commit => l_commit
692 ,p_init_msg_list => l_init_msg_list
693 ,p_msg_count => l_msg_count
694 ,p_msg_data => l_msg_data
695 ,p_return_status => x_return_status
696 ,p_pm_product_code => 'AMW'
697 ,p_pm_project_reference => l_prj_ref
698 ,p_pa_project_id => p_project_id
699 ,p_pa_parent_task_id => p_parent_task_id
700 ,p_pm_task_reference => p_task_number
701 ,p_pa_task_number => p_task_number
702 ,p_task_name => p_task_name
703 ,p_task_description => p_task_description
704 ,p_pa_project_id_out => l_pa_project_id_out
705 ,p_pa_project_number_out => l_pa_project_number_out
706 ,p_task_id => p_task_id);
707
708 -- dbms_output.put_line('status :' || x_return_status);
709
710 IF x_return_status <> 'S' THEN
711 if l_msg_count > 0 THEN
712 for i in 1..l_msg_count loop
713 pa_interface_utils_pub.get_messages(
714 p_data => l_data
715 ,p_msg_index => i
716 ,p_msg_count => l_msg_count
717 ,p_msg_data => l_msg_data
718 ,p_msg_index_out => l_msg_index_out );
719
720 if l_data IS NOT NULL then
721 p_msg_data := p_msg_data || l_data;
722 end if;
723 -- dbms_output.put_line(l_data);
724 end loop;
725 end if;
726 END IF;
727
728 END create_audit_task_in_pa;
729
730
731 PROCEDURE update_audit_task_in_pa(
732 p_project_id IN NUMBER,
733 p_task_id IN NUMBER,
734 p_parent_task_id IN NUMBER,
735 p_task_name IN VARCHAR2,
736 p_task_number IN VARCHAR2,
737 p_task_description IN VARCHAR2,
738 p_task_manager IN NUMBER,
739 p_start_date IN DATE ,
740 p_completion_date IN DATE ,
741 p_msg_data OUT nocopy VARCHAR2,
742 x_return_status OUT nocopy VARCHAR2
743 ) IS
744
745
746 l_commit VARCHAR2(1) := 'F';
747 l_init_msg_list VARCHAR2(1) := 'F';
748 l_msg_count NUMBER;
749 l_msg_data VARCHAR2(2000);
750 l_msg_index_out NUMBER;
751 l_data VARCHAR2(2000);
752
753 l_out_pa_task_id NUMBER;
754 l_out_pm_task_reference VARCHAR2(25);
755 l_task_id NUMBER;
756
757 l_prj_number VARCHAR2(20);
758 l_prj_ref VARCHAR2(20);
759 l_return_status VARCHAR2(1);
760
761
762 BEGIN
763
764 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
765 p_api_version_number => 1,
766 p_responsibility_id => FND_GLOBAL.RESP_ID,
767 p_user_id => FND_GLOBAL.USER_ID,
768 p_resp_appl_id => 242,
769 p_msg_count => l_msg_count,
770 p_msg_data => l_msg_data,
771 p_return_status => l_return_status);
772
773 select segment1,pm_project_reference into l_prj_number,l_prj_ref
774 from pa_projects_all
775 where project_id = p_project_id;
776
777 FND_MSG_PUB.initialize;
778
779 PA_PROJECT_PUB.UPDATE_TASK(
780 p_api_version_number => 1
781 ,p_commit => l_commit
782 ,p_init_msg_list => l_init_msg_list
783 ,p_msg_count => l_msg_count
784 ,p_msg_data => l_msg_data
785 ,p_return_status => x_return_status
786 ,p_pm_product_code => 'AMW'
787 ,p_pm_project_reference => l_prj_ref
788 ,p_pa_project_id => p_project_id
789 ,p_pm_task_reference => p_task_number
790 ,p_pa_task_id => p_task_id
791 ,p_task_name => p_task_name
792 ,p_task_number => p_task_number
793 ,p_task_description => p_task_description
794 -- ,p_pa_parent_task_id => p_parent_task_id
795 ,p_out_pa_task_id => l_out_pa_task_id
796 ,p_out_pm_task_reference => l_out_pm_task_reference);
797
798 -- dbms_output.put_line('status :' || x_return_status);
799
800 IF x_return_status <> 'S' THEN
801 if l_msg_count > 0 THEN
802 for i in 1..l_msg_count loop
803 pa_interface_utils_pub.get_messages(
804 p_data => l_data
805 ,p_msg_index => i
806 ,p_msg_count => l_msg_count
807 ,p_msg_data => l_msg_data
808 ,p_msg_index_out => l_msg_index_out );
809
810 if l_data IS NOT NULL then
811 p_msg_data := p_msg_data || l_data;
812 end if;
813 -- dbms_output.put_line(l_data);
814 end loop;
815 end if;
816 END IF;
817
818 END update_audit_task_in_pa;
819
820
821 PROCEDURE delete_audit_task_in_pa(
822 p_project_id IN NUMBER,
823 p_task_id IN NUMBER,
824 -- p_task_number IN VARCHAR2,
825 p_msg_data OUT nocopy VARCHAR2,
826 x_return_status OUT nocopy VARCHAR2
827 ) IS
828
829
830 l_commit VARCHAR2(1) := 'F';
831 l_init_msg_list VARCHAR2(1) := 'F';
832 l_msg_count NUMBER;
833 l_msg_data VARCHAR2(2000);
834 l_msg_index_out NUMBER;
835 l_data VARCHAR2(2000);
836
837 l_task_id NUMBER;
838 l_project_id NUMBER;
839
840 l_prj_number VARCHAR2(20);
841 l_prj_ref VARCHAR2(20);
842 l_return_status VARCHAR2(1);
843
844 l_task_number VARCHAR2(25);
845
846
847 BEGIN
848
849 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
850 p_api_version_number => 1,
851 p_responsibility_id => FND_GLOBAL.RESP_ID,
852 p_user_id => FND_GLOBAL.USER_ID,
853 p_resp_appl_id => 242,
854 p_msg_count => l_msg_count,
855 p_msg_data => l_msg_data,
856 p_return_status => l_return_status);
857
858 select segment1,pm_project_reference into l_prj_number,l_prj_ref
859 from pa_projects_all
860 where project_id = p_project_id;
861
862 select task_number into l_task_number
863 from pa_tasks
864 where task_id = p_task_id
865 and project_id = p_project_id;
866
867 FND_MSG_PUB.initialize;
868
869 PA_PROJECT_PUB.DELETE_TASK(
870 p_api_version_number => 1
871 ,p_commit => l_commit
872 ,p_init_msg_list => l_init_msg_list
873 ,p_msg_count => l_msg_count
874 ,p_msg_data => l_msg_data
875 ,p_return_status => x_return_status
876 ,p_pm_product_code => 'AMW'
877 ,p_pm_project_reference => l_prj_ref
878 ,p_pa_project_id => p_project_id
879 ,p_pm_task_reference => l_task_number
880 ,p_pa_task_id => p_task_id
881 ,p_cascaded_delete_flag => 'Y'
882 ,p_project_id => l_project_id
883 ,p_task_id => l_task_id);
884
885 -- dbms_output.put_line('status :' || x_return_status);
886
887 IF x_return_status <> 'S' THEN
888 if l_msg_count > 0 THEN
889 for i in 1..l_msg_count loop
890 pa_interface_utils_pub.get_messages(
891 p_data => l_data
892 ,p_msg_index => i
893 ,p_msg_count => l_msg_count
894 ,p_msg_data => l_msg_data
895 ,p_msg_index_out => l_msg_index_out );
896
897 if l_data IS NOT NULL then
898 p_msg_data := p_msg_data || l_data;
899 end if;
900 -- dbms_output.put_line(l_data);
901 end loop;
902 end if;
903 END IF;
904
905 END delete_audit_task_in_pa;
906
907
908 PROCEDURE delete_audit_task_in_icm(
909 p_audit_project_id IN NUMBER,
910 p_task_id IN NUMBER,
911 x_return_status OUT nocopy VARCHAR2
912 ) IS
913
914 l_audit_project_id NUMBER ;
915 BEGIN
916
917 DELETE FROM amw_audit_tasks_b
918 WHERE task_id IN (SELECT task_id
919 FROM amw_audit_tasks_b
920 START WITH task_id = p_task_id
921 CONNECT BY PRIOR task_id = parent_task_id);
922
923 DELETE FROM amw_audit_tasks_tl
924 WHERE task_id IN (SELECT task_id
925 FROM amw_audit_tasks_b
926 START WITH task_id = p_task_id
927 CONNECT BY PRIOR task_id = parent_task_id);
928
929 x_return_status := 'S';
930 COMMIT;
931 EXCEPTION
932 WHEN OTHERS THEN
933 x_return_status := 'E';
934
935 END delete_audit_task_in_icm;
936
937 FUNCTION is_workplan_version_shared( p_project_id IN NUMBER) return VARCHAR2
938 IS
939
940 l_fin_structure_id NUMBER;
941 l_published VARCHAR2(1):='N';
942 l_versioned VARCHAR2(1):='N';
943 l_shared VARCHAR2(1):='N';
944 l_dummy VARCHAR2(1):='N';
945 BEGIN
946
947 l_fin_structure_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUCTURE_ID(p_project_id);
948 IF l_fin_structure_id is NOT NULL THEN
949 l_published := PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(p_project_id,l_fin_structure_id);
950 END IF;
951 l_versioned := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_project_id);
952 l_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(p_project_id);
953
954 IF (l_shared = 'Y' AND l_published = 'Y' AND l_versioned = 'Y') THEN
955 l_dummy := 'Y';
956 END IF;
957
958 return l_dummy;
959 END is_workplan_version_shared;
960
961 PROCEDURE cp_tasks
962 ( p_source_project_id IN NUMBER,
963 p_dest_project_id IN NUMBER,
964 x_return_status OUT nocopy VARCHAR2)
965 IS
966 -- Enter the procedure variables here. As shown below
967 l_audit_procedure_id NUMBER;
968 l_audit_procedure_rev_id NUMBER;
969 v_category_id NUMBER;
970 src_task_id NUMBER;
971 dest_task_id NUMBER;
972 org_id NUMBER;
973
974 TYPE ap_cur_type IS REF CURSOR; --RETURN amw_ap_associations%ROWTYPE;
975 my_rec ap_cur_type;
976 x_number amw_ap_associations.audit_procedure_id%TYPE;
977
978 CURSOR c_srceng_ap IS
979 SELECT
980 PROJECT_ID,
981 ATTRIBUTE10,
982 ATTRIBUTE11,
983 ATTRIBUTE12,
984 ATTRIBUTE13,
985 ATTRIBUTE14,
986 ATTRIBUTE15,
987 APPROVAL_STATUS,
988 ORIG_SYSTEM_REFERENCE,
989 REQUESTOR_ID,
990 ATTRIBUTE6,
991 ATTRIBUTE7,
992 ATTRIBUTE8,
993 ATTRIBUTE9,
994 SECURITY_GROUP_ID,
995 END_DATE,
996 APPROVAL_DATE,
997 AUDIT_PROCEDURE_ID,
998 AUDIT_PROCEDURE_REV_ID,
999 CURR_APPROVED_FLAG,
1000 LATEST_REVISION_FLAG,
1001 ATTRIBUTE5,
1002 ATTRIBUTE_CATEGORY,
1003 ATTRIBUTE1,
1004 ATTRIBUTE2,
1005 ATTRIBUTE3,
1006 ATTRIBUTE4,
1007 CLASSIFICATION
1008 FROM AMW_AUDIT_PROCEDURES_B
1009 WHERE PROJECT_ID = p_source_project_id and
1010 (END_DATE>=SYSDATE or END_DATE is null);
1011 cursor c_tasks
1012 is
1013 select src.task_id src_task_id,dest.task_id dest_task_id
1014 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1015 where dest.audit_project_id =p_dest_project_id
1016 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1017 and src.task_number = dest.task_number;
1018
1019 CURSOR c_apdetails IS
1020 select distinct src.pk1 src_pk1,src.pk2 src_pk2,src.pk3 src_pk3,src.pk4 src_pk4,
1021 dest.pk1 dest_pk1,dest.pk2 dest_pk2,dest.pk3 dest_pk3,
1022 dest.pk4 dest_pk4,src.audit_procedure_rev_id src_audit_procedure_rev_id,
1023 dest.audit_procedure_rev_id dest_audit_procedure_rev_id
1024 from amw_ap_associations src ,amw_ap_associations dest
1025 where src.pk1=p_source_project_id and dest.pk1=p_dest_project_id
1026 and src.OBJECT_TYPE='PROJECT' and src.association_creation_date is not null
1027 and dest.OBJECT_TYPE ='PROJECT_NEW' and dest.association_creation_date is not null
1028 and src.pk2=dest.pk2 and src.pk3=dest.pk3
1029 and src.audit_procedure_id =dest.audit_procedure_id ;
1030
1031 Cursor c_task_icm is select src.task_id src_task_id,dest.task_id dest_task_id
1032 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1033 where dest.audit_project_id =p_dest_project_id
1034 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1035 and src.task_number = dest.task_number
1036 and src.source_code='ICM';
1037
1038 BEGIN
1039
1040 FOR ap_rec IN c_srceng_ap
1041 LOOP
1042 select amw_procedures_s.nextval into l_audit_procedure_id from dual;
1043 select amw_procedure_rev_s.nextval into l_audit_procedure_rev_id from dual;
1044 -- l_audit_procedure_id :=amw_procedures_s.nextval;
1045 -- l_audit_procedure_rev_id :=amw_procedure_rev_s.nextval;
1046 insert into AMW_AUDIT_PROCEDURES_B (
1047 PROJECT_ID,
1048 ATTRIBUTE10,
1049 ATTRIBUTE11,
1050 ATTRIBUTE12,
1051 ATTRIBUTE13,
1052 ATTRIBUTE14,
1053 ATTRIBUTE15,
1054 OBJECT_VERSION_NUMBER,
1055 APPROVAL_STATUS,
1056 ORIG_SYSTEM_REFERENCE,
1057 REQUESTOR_ID,
1058 ATTRIBUTE6,
1059 ATTRIBUTE7,
1060 ATTRIBUTE8,
1061 ATTRIBUTE9,
1062 SECURITY_GROUP_ID,
1063 AUDIT_PROCEDURE_ID,
1064 AUDIT_PROCEDURE_REV_ID,
1065 AUDIT_PROCEDURE_REV_NUM,
1066 END_DATE,
1067 APPROVAL_DATE,
1068 CURR_APPROVED_FLAG,
1069 LATEST_REVISION_FLAG,
1070 ATTRIBUTE5,
1071 ATTRIBUTE_CATEGORY,
1072 ATTRIBUTE1,
1073 ATTRIBUTE2,
1074 ATTRIBUTE3,
1075 ATTRIBUTE4,
1076 CREATION_DATE,
1077 CREATED_BY,
1078 LAST_UPDATE_DATE,
1079 LAST_UPDATED_BY,
1080 LAST_UPDATE_LOGIN,
1081 CLASSIFICATION
1082 )
1083 SELECT p_dest_project_id,
1084 ap_rec.ATTRIBUTE10,
1085 ap_rec.ATTRIBUTE11,
1086 ap_rec.ATTRIBUTE12,
1087 ap_rec.ATTRIBUTE13,
1088 ap_rec.ATTRIBUTE14,
1089 ap_rec.ATTRIBUTE15,
1090 1,
1091 ap_rec.APPROVAL_STATUS,
1092 ap_rec.ORIG_SYSTEM_REFERENCE,
1093 ap_rec.REQUESTOR_ID,
1094 ap_rec.ATTRIBUTE6,
1095 ap_rec.ATTRIBUTE7,
1096 ap_rec.ATTRIBUTE8,
1097 ap_rec.ATTRIBUTE9,
1098 ap_rec.SECURITY_GROUP_ID,
1099 l_audit_procedure_id,
1100 l_audit_procedure_rev_id,
1101 1,
1102 ap_rec. END_DATE,
1103 ap_rec.APPROVAL_DATE,
1104 ap_rec.CURR_APPROVED_FLAG,
1105 ap_rec.LATEST_REVISION_FLAG,
1106 ap_rec.ATTRIBUTE5,
1107 ap_rec.ATTRIBUTE_CATEGORY,
1108 ap_rec.ATTRIBUTE1,
1109 ap_rec.ATTRIBUTE2,
1110 ap_rec.ATTRIBUTE3,
1111 ap_rec. ATTRIBUTE4,
1112 SYSDATE,
1113 FND_GLOBAL.USER_ID,
1114 SYSDATE,
1115 FND_GLOBAL.USER_ID,
1116 FND_GLOBAL.LOGIN_ID,
1117 ap_rec.CLASSIFICATION
1118 FROM dual;
1119
1120 insert into AMW_AUDIT_PROCEDURES_TL (
1121 AUDIT_PROCEDURE_REV_ID,
1122 NAME,
1123 DESCRIPTION,
1124 LAST_UPDATE_DATE,
1125 LAST_UPDATED_BY,
1126 CREATION_DATE,
1127 CREATED_BY,
1128 LAST_UPDATE_LOGIN,
1129 SECURITY_GROUP_ID,
1130 LANGUAGE,
1131 SOURCE_LANG
1132 )
1133
1134 select
1135 l_audit_procedure_rev_id,
1136 SYSDATE||B.NAME,
1137 B.DESCRIPTION,
1138 SYSDATE,
1139 FND_GLOBAL.USER_ID,
1140 SYSDATE,
1141 FND_GLOBAL.USER_ID,
1142 B.LAST_UPDATE_LOGIN,
1143 B.SECURITY_GROUP_ID,
1144 B.LANGUAGE,
1145 B.SOURCE_LANG
1146 from AMW_AUDIT_PROCEDURES_TL B
1147 where AUDIT_PROCEDURE_REV_ID =ap_rec.AUDIT_PROCEDURE_REV_ID;
1148 INSERT INTO amw_ap_associations (
1149 ap_association_id,
1150 last_update_date,
1151 last_updated_by,
1152 creation_date,
1153 created_by,
1154 association_creation_date,
1155 last_update_login,
1156 audit_procedure_id,
1157 audit_procedure_rev_id,
1158 pk1,
1159 pk2,
1160 pk3,
1161 pk4,
1162 object_type,
1163 object_version_number)
1164 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1165 SYSDATE ,
1166 FND_GLOBAL.USER_ID,
1167 SYSDATE,
1168 FND_GLOBAL.USER_ID,
1169 SYSDATE,
1170 FND_GLOBAL.USER_ID ,
1171 l_audit_procedure_id,
1172 l_audit_procedure_rev_id,
1173 p_dest_project_id,
1174 apa.pk2 ,
1175 apa.pk3,
1176 apa.pk4,
1177 'PROJECT_NEW',
1178 1
1179 from
1180 amw_ap_associations apa
1181 where pk1=ap_rec.PROJECT_ID
1182 and apa.OBJECT_TYPE='PROJECT' and association_creation_date is not null
1183 and apa.AUDIT_PROCEDURE_ID=ap_rec.AUDIT_PROCEDURE_ID
1184 and NOT EXISTS
1185 (SELECT 'Y' from amw_ap_associations apa2
1186 where apa2.object_type in ('PROJECT','PROJECT_NEW')
1187 AND apa2.pk1 = p_dest_project_id
1188 AND apa2.pk2 = apa.pk2
1189 AND apa2.pk3 = apa.pk3
1190 AND apa2.pk4 = apa.pk4
1191 AND apa2.AUDIT_PROCEDURE_ID=apa.AUDIT_PROCEDURE_ID)
1192 AND (apa.pk3=-1 or apa.pk3 in (select 1 from amw_control_associations WHERE object_type='PROJECT'
1193 AND pk1 = p_source_project_id and control_id=apa.pk3));
1194
1195
1196 OPEN my_rec FOR SELECT audit_procedure_id from amw_ap_associations where
1197 pk1=p_dest_project_id and audit_procedure_id=l_audit_procedure_id ;
1198 LOOP
1199 FETCH my_rec INTO x_number;
1200 EXIT WHEN my_rec%NOTFOUND;
1201 -- DBMS_OUTPUT.PUT_LINE(x_number);
1202 END LOOP;
1203 IF(x_number is not null) THEN
1204 select src.task_id,dest.task_id into src_task_id, dest_task_id
1205 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1206 where dest.audit_project_id =p_dest_project_id
1207 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1208 and src.task_number = dest.task_number
1209 and src.task_id=(select distinct pk4 from amw_ap_associations where
1210 audit_procedure_id=l_audit_procedure_id and
1211 audit_procedure_rev_id=l_audit_procedure_rev_id );
1212
1213 update amw_ap_associations set pk4=dest_task_id
1214 where pk1=p_dest_project_id and pk4=src_task_id
1215 and audit_procedure_id=l_audit_procedure_id and
1216 audit_procedure_rev_id=l_audit_procedure_rev_id;
1217
1218 select distinct pk2 into org_id from amw_ap_associations
1219 where audit_procedure_id=l_audit_procedure_id and
1220 audit_procedure_rev_id=l_audit_procedure_rev_id;
1221
1222 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROJECT_AP',
1223 X_from_pk1_value => p_source_project_id,
1224 X_from_pk2_value =>org_id,
1225 X_from_pk3_value =>src_task_id,
1226 X_from_pk4_value =>ap_rec.AUDIT_PROCEDURE_REV_ID,
1227 X_to_entity_name => 'AMW_PROJECT_AP',
1228 X_to_pk1_value => p_dest_project_id,
1229 X_to_pk2_value => org_id,
1230 X_to_pk3_value => dest_task_id,
1231 X_to_pk4_value => l_audit_procedure_rev_id,
1232 X_FROM_CATEGORY_ID => v_category_id,
1233 X_TO_CATEGORY_ID => v_category_id);
1234 END IF;
1235
1236
1237 END LOOP;
1238 INSERT INTO amw_ap_associations (
1239 ap_association_id,
1240 last_update_date,
1241 last_updated_by,
1242 creation_date,
1243 created_by,
1244 association_creation_date,
1245 last_update_login,
1246 audit_procedure_id,
1247 audit_procedure_rev_id,
1248 pk1,
1249 pk2,
1250 pk3,
1251 pk4,
1252 object_type,
1253 object_version_number)
1254 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1255 SYSDATE ,
1256 FND_GLOBAL.USER_ID,
1257 SYSDATE,
1258 FND_GLOBAL.USER_ID,
1259 SYSDATE,
1260 FND_GLOBAL.USER_ID,
1261 apa.audit_procedure_id,
1262 apa.audit_procedure_rev_id,
1263 p_dest_project_id,
1264 apa.pk2,
1265 apa.pk3,
1266 apa.pk4,
1267 'PROJECT_NEW',
1268 1
1269 from
1270 amw_ap_associations apa
1271 where apa.audit_procedure_id not in (
1272 select distinct audit_procedure_id from amw_audit_procedures_b where
1273 project_id=p_source_project_id
1274 )
1275 and apa.OBJECT_TYPE='PROJECT' and association_creation_date is not null
1276 and apa.pk1=p_source_project_id
1277 and NOT EXISTS
1278 (SELECT 'Y' from amw_ap_associations apa2
1279 where apa2.object_type in ('PROJECT','PROJECT_NEW')
1280 AND apa2.pk1 = p_dest_project_id
1281 AND apa2.pk2 = apa.pk2
1282 AND apa2.pk3 = apa.pk3
1283 AND apa2.pk4 = apa.pk4
1284 AND apa2.AUDIT_PROCEDURE_ID=apa.AUDIT_PROCEDURE_ID)
1285 AND (apa.pk3=-1 or apa.pk3 in (select 1 from amw_control_associations WHERE object_type='PROJECT'
1286 AND pk1 = p_source_project_id and control_id=apa.pk3));
1287 FOR ap_task in c_tasks LOOP
1288 update amw_ap_associations set pk4=ap_task.dest_task_id
1289 where pk1=p_dest_project_id and pk4=ap_task.src_task_id;
1290 END LOOP;
1291
1292 select category_id into v_category_id
1293 from fnd_document_categories where name = 'AMW_WORK_PAPERS';
1294
1295 FOR apdetails_rec IN c_apdetails LOOP
1296 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROJECT_AP',
1297 X_from_pk1_value => p_source_project_id,
1298 X_from_pk2_value =>apdetails_rec.src_pk2,
1299 X_from_pk3_value =>apdetails_rec.src_pk4,
1300 X_from_pk4_value =>apdetails_rec.src_audit_procedure_rev_id,
1301 X_to_entity_name => 'AMW_PROJECT_AP',
1302 X_to_pk1_value => p_dest_project_id,
1303 X_to_pk2_value => apdetails_rec.dest_pk2,
1304 X_to_pk3_value => apdetails_rec.dest_pk4,
1305 X_to_pk4_value => apdetails_rec.dest_audit_procedure_rev_id,
1306 X_FROM_CATEGORY_ID => v_category_id,
1307 X_TO_CATEGORY_ID => v_category_id);
1308
1309 END LOOP;
1310
1311 update amw_ap_associations set object_type = 'PROJECT'
1312 where object_type = 'PROJECT_NEW'
1313 and pk1 = p_dest_project_id;
1314
1315 For ap_task_icm in c_task_icm
1316 LOOP
1317 -- select src.audit_procedure_rev_id into l_audit_procedure_rev_id
1318 -- from amw_ap_associations dest ,amw_ap_associations src
1319 -- where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1320 -- and src.audit_procedure_id=dest.audit_procedure_id
1321 -- and src.pk4=ap_task_icm.src_task_id
1322 -- and src.audit_procedure_rev_id=dest.audit_procedure_rev_id;
1323
1324 update amw_ap_associations
1325 set pk4= ap_task_icm.dest_task_id
1326 where pk1=p_dest_project_id
1327 and audit_procedure_id in (
1328 select src.audit_procedure_id
1329 from amw_ap_associations dest ,amw_ap_associations src
1330 where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1331 and src.audit_procedure_id=dest.audit_procedure_id
1332 and src.pk4=ap_task_icm.src_task_id
1333 and src.association_creation_date is null);
1334
1335 update fnd_attached_documents
1336 set pk3_value=ap_task_icm.dest_task_id
1337 where pk1_value=to_char(p_dest_project_id) and pk3_value=-1
1338 and pk4_value in (
1339 select src.audit_procedure_rev_id
1340 from amw_ap_associations dest ,amw_ap_associations src
1341 where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1342 and src.audit_procedure_id=dest.audit_procedure_id
1343 and src.pk4=ap_task_icm.src_task_id
1344 and src.association_creation_date is null) ;
1345
1346 END LOOP;
1347
1348
1349 END cp_tasks;
1350
1351 PROCEDURE cp_tasks_all(
1352 p_source_project_id IN NUMBER,
1353 p_dest_project_id IN NUMBER,
1354 x_return_status OUT nocopy VARCHAR2
1355 ) IS
1356
1357 -- Enter the procedure variables here. As shown below
1358 l_audit_procedure_id NUMBER;
1359 l_audit_procedure_rev_id NUMBER;
1360 v_category_id NUMBER;
1361 src_task_id NUMBER;
1362 dest_task_id NUMBER;
1363 org_id NUMBER;
1364
1365
1366 CURSOR c_srceng_ap IS
1367 SELECT
1368 PROJECT_ID,
1369 ATTRIBUTE10,
1370 ATTRIBUTE11,
1371 ATTRIBUTE12,
1372 ATTRIBUTE13,
1373 ATTRIBUTE14,
1374 ATTRIBUTE15,
1375 APPROVAL_STATUS,
1376 ORIG_SYSTEM_REFERENCE,
1377 REQUESTOR_ID,
1378 ATTRIBUTE6,
1379 ATTRIBUTE7,
1380 ATTRIBUTE8,
1381 ATTRIBUTE9,
1382 SECURITY_GROUP_ID,
1383 END_DATE,
1384 APPROVAL_DATE,
1385 AUDIT_PROCEDURE_ID,
1386 AUDIT_PROCEDURE_REV_ID,
1387 CURR_APPROVED_FLAG,
1388 LATEST_REVISION_FLAG,
1389 ATTRIBUTE5,
1390 ATTRIBUTE_CATEGORY,
1391 ATTRIBUTE1,
1392 ATTRIBUTE2,
1393 ATTRIBUTE3,
1394 ATTRIBUTE4,
1395 CLASSIFICATION
1396 FROM AMW_AUDIT_PROCEDURES_B
1397 WHERE PROJECT_ID = p_source_project_id and
1398 (END_DATE>=SYSDATE or END_DATE is null);
1399 cursor c_tasks
1400 is
1401 select src.task_id src_task_id,dest.task_id dest_task_id
1402 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1403 where dest.audit_project_id =p_dest_project_id
1404 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1405 and src.task_number = dest.task_number;
1406
1407 CURSOR c_apdetails IS
1408 select distinct src.pk1 src_pk1,src.pk2 src_pk2,src.pk3 src_pk3,src.pk4 src_pk4,
1409 dest.pk1 dest_pk1,dest.pk2 dest_pk2,dest.pk3 dest_pk3,
1410 dest.pk4 dest_pk4,src.audit_procedure_rev_id src_audit_procedure_rev_id,
1411 dest.audit_procedure_rev_id dest_audit_procedure_rev_id
1412 from amw_ap_associations src ,amw_ap_associations dest
1413 where src.pk1=p_source_project_id and dest.pk1=p_dest_project_id
1414 and src.OBJECT_TYPE='PROJECT' and src.association_creation_date is not null
1415 and dest.OBJECT_TYPE ='PROJECT_NEW' and dest.association_creation_date is not null
1416 and src.pk2=dest.pk2 and src.pk3=dest.pk3
1417 and src.audit_procedure_id =dest.audit_procedure_id ;
1418
1419 Cursor c_task_icm is select src.task_id src_task_id,dest.task_id dest_task_id
1420 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1421 where dest.audit_project_id =p_dest_project_id
1422 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1423 and src.task_number = dest.task_number
1424 and src.source_code='ICM';
1425
1426 BEGIN
1427
1428 FOR ap_rec IN c_srceng_ap
1429 LOOP
1430 select amw_procedures_s.nextval into l_audit_procedure_id from dual;
1431 select amw_procedure_rev_s.nextval into l_audit_procedure_rev_id from dual;
1432 -- l_audit_procedure_id :=amw_procedures_s.nextval;
1433 -- l_audit_procedure_rev_id :=amw_procedure_rev_s.nextval;
1434 insert into AMW_AUDIT_PROCEDURES_B (
1435 PROJECT_ID,
1436 ATTRIBUTE10,
1437 ATTRIBUTE11,
1438 ATTRIBUTE12,
1439 ATTRIBUTE13,
1440 ATTRIBUTE14,
1441 ATTRIBUTE15,
1442 OBJECT_VERSION_NUMBER,
1443 APPROVAL_STATUS,
1444 ORIG_SYSTEM_REFERENCE,
1445 REQUESTOR_ID,
1446 ATTRIBUTE6,
1447 ATTRIBUTE7,
1448 ATTRIBUTE8,
1449 ATTRIBUTE9,
1450 SECURITY_GROUP_ID,
1451 AUDIT_PROCEDURE_ID,
1452 AUDIT_PROCEDURE_REV_ID,
1453 AUDIT_PROCEDURE_REV_NUM,
1454 END_DATE,
1455 APPROVAL_DATE,
1456 CURR_APPROVED_FLAG,
1457 LATEST_REVISION_FLAG,
1458 ATTRIBUTE5,
1459 ATTRIBUTE_CATEGORY,
1460 ATTRIBUTE1,
1461 ATTRIBUTE2,
1462 ATTRIBUTE3,
1463 ATTRIBUTE4,
1464 CREATION_DATE,
1465 CREATED_BY,
1466 LAST_UPDATE_DATE,
1467 LAST_UPDATED_BY,
1468 LAST_UPDATE_LOGIN,
1469 CLASSIFICATION
1470 )
1471 SELECT p_dest_project_id,
1472 ap_rec.ATTRIBUTE10,
1473 ap_rec.ATTRIBUTE11,
1474 ap_rec.ATTRIBUTE12,
1475 ap_rec.ATTRIBUTE13,
1476 ap_rec.ATTRIBUTE14,
1477 ap_rec.ATTRIBUTE15,
1478 1,
1479 ap_rec.APPROVAL_STATUS,
1480 ap_rec.ORIG_SYSTEM_REFERENCE,
1481 ap_rec.REQUESTOR_ID,
1482 ap_rec.ATTRIBUTE6,
1483 ap_rec.ATTRIBUTE7,
1484 ap_rec.ATTRIBUTE8,
1485 ap_rec.ATTRIBUTE9,
1486 ap_rec.SECURITY_GROUP_ID,
1487 l_audit_procedure_id,
1488 l_audit_procedure_rev_id,
1489 1,
1490 ap_rec. END_DATE,
1491 ap_rec.APPROVAL_DATE,
1492 ap_rec.CURR_APPROVED_FLAG,
1493 ap_rec.LATEST_REVISION_FLAG,
1494 ap_rec.ATTRIBUTE5,
1495 ap_rec.ATTRIBUTE_CATEGORY,
1496 ap_rec.ATTRIBUTE1,
1497 ap_rec.ATTRIBUTE2,
1498 ap_rec.ATTRIBUTE3,
1499 ap_rec. ATTRIBUTE4,
1500 SYSDATE,
1501 FND_GLOBAL.USER_ID,
1502 SYSDATE,
1503 FND_GLOBAL.USER_ID,
1504 FND_GLOBAL.LOGIN_ID,
1505 ap_rec.CLASSIFICATION
1506 FROM dual;
1507
1508 insert into AMW_AUDIT_PROCEDURES_TL (
1509 AUDIT_PROCEDURE_REV_ID,
1510 NAME,
1511 DESCRIPTION,
1512 LAST_UPDATE_DATE,
1513 LAST_UPDATED_BY,
1514 CREATION_DATE,
1515 CREATED_BY,
1516 LAST_UPDATE_LOGIN,
1517 SECURITY_GROUP_ID,
1518 LANGUAGE,
1519 SOURCE_LANG
1520 )
1521
1522 select
1523 l_audit_procedure_rev_id,
1524 SYSDATE||B.NAME,
1525 B.DESCRIPTION,
1526 SYSDATE,
1527 FND_GLOBAL.USER_ID,
1528 SYSDATE,
1529 FND_GLOBAL.USER_ID,
1530 B.LAST_UPDATE_LOGIN,
1531 B.SECURITY_GROUP_ID,
1532 B.LANGUAGE,
1533 B.SOURCE_LANG
1534 from AMW_AUDIT_PROCEDURES_TL B
1535 where AUDIT_PROCEDURE_REV_ID =ap_rec.AUDIT_PROCEDURE_REV_ID;
1536 INSERT INTO amw_ap_associations (
1537 ap_association_id,
1538 last_update_date,
1539 last_updated_by,
1540 creation_date,
1541 created_by,
1542 association_creation_date,
1543 last_update_login,
1544 audit_procedure_id,
1545 audit_procedure_rev_id,
1546 pk1,
1547 pk2,
1548 pk3,
1549 pk4,
1550 object_type,
1551 object_version_number)
1552 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1553 SYSDATE ,
1554 FND_GLOBAL.USER_ID,
1555 SYSDATE,
1556 FND_GLOBAL.USER_ID,
1557 SYSDATE,
1558 FND_GLOBAL.USER_ID ,
1559 l_audit_procedure_id,
1560 l_audit_procedure_rev_id,
1561 p_dest_project_id,
1562 apa.pk2 ,
1563 apa.pk3,
1564 apa.pk4,
1565 'PROJECT_NEW',
1566 1
1567 from
1568 amw_ap_associations apa
1569 where pk1=ap_rec.PROJECT_ID
1570 and apa.OBJECT_TYPE='PROJECT' and association_creation_date is not null
1571 and apa.AUDIT_PROCEDURE_ID=ap_rec.AUDIT_PROCEDURE_ID
1572 and NOT EXISTS
1573 (SELECT 'Y' from amw_ap_associations apa2
1574 where apa2.object_type in ('PROJECT','PROJECT_NEW')
1575 AND apa2.pk1 = p_dest_project_id
1576 AND apa2.pk2 = apa.pk2
1577 AND apa2.pk3 = apa.pk3
1578 AND apa2.pk4 = apa.pk4
1579 AND apa2.AUDIT_PROCEDURE_ID=apa.AUDIT_PROCEDURE_ID);
1580
1581 select src.task_id,dest.task_id into src_task_id, dest_task_id
1582 from amw_audit_tasks_v src , amw_audit_tasks_v dest
1583 where dest.audit_project_id =p_dest_project_id
1584
1585 and src.audit_project_id =p_source_project_id and src.task_name=dest.task_name
1586 and src.task_number = dest.task_number
1587 and src.task_id=(select distinct pk4 from amw_ap_associations where
1588 audit_procedure_id=l_audit_procedure_id and
1589 audit_procedure_rev_id=l_audit_procedure_rev_id );
1590
1591 update amw_ap_associations set pk4=dest_task_id
1592 where pk1=p_dest_project_id and pk4=src_task_id
1593 and audit_procedure_id=l_audit_procedure_id and
1594 audit_procedure_rev_id=l_audit_procedure_rev_id;
1595
1596 select distinct pk2 into org_id from amw_ap_associations
1597 where audit_procedure_id=l_audit_procedure_id and
1598 audit_procedure_rev_id=l_audit_procedure_rev_id;
1599
1600 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROJECT_AP',
1601 X_from_pk1_value => p_source_project_id,
1602 X_from_pk2_value =>org_id,
1603 X_from_pk3_value =>src_task_id,
1604 X_from_pk4_value =>ap_rec.AUDIT_PROCEDURE_REV_ID,
1605 X_to_entity_name => 'AMW_PROJECT_AP',
1606 X_to_pk1_value => p_dest_project_id,
1607 X_to_pk2_value => org_id,
1608 X_to_pk3_value => dest_task_id,
1609 X_to_pk4_value => l_audit_procedure_rev_id,
1610 X_FROM_CATEGORY_ID => v_category_id,
1611 X_TO_CATEGORY_ID => v_category_id);
1612
1613
1614
1615 END LOOP;
1616 INSERT INTO amw_ap_associations (
1617 ap_association_id,
1618 last_update_date,
1619 last_updated_by,
1620 creation_date,
1621 created_by,
1622 association_creation_date,
1623 last_update_login,
1624 audit_procedure_id,
1625 audit_procedure_rev_id,
1626 pk1,
1627 pk2,
1628 pk3,
1629 pk4,
1630 object_type,
1631 object_version_number)
1632 SELECT AMW_AP_ASSOCIATIONS_S.nextval,
1633 SYSDATE ,
1634 FND_GLOBAL.USER_ID,
1635 SYSDATE,
1636 FND_GLOBAL.USER_ID,
1637 SYSDATE,
1638 FND_GLOBAL.USER_ID,
1639 apa.audit_procedure_id,
1640 apa.audit_procedure_rev_id,
1641 p_dest_project_id,
1642 apa.pk2,
1643 apa.pk3,
1644 apa.pk4,
1645 'PROJECT_NEW',
1646 1
1647 from
1648 amw_ap_associations apa
1649 where apa.audit_procedure_id not in (
1650 select distinct audit_procedure_id from amw_audit_procedures_b where
1651 project_id=p_source_project_id
1652 )
1653 and apa.OBJECT_TYPE='PROJECT' and association_creation_date is not null
1654 and apa.pk1=p_source_project_id
1655 and NOT EXISTS
1656 (SELECT 'Y' from amw_ap_associations apa2
1657 where apa2.object_type in ('PROJECT','PROJECT_NEW')
1658 AND apa2.pk1 = p_dest_project_id
1659 AND apa2.pk2 = apa.pk2
1660 AND apa2.pk3 = apa.pk3
1661 AND apa2.pk4 = apa.pk4
1662 AND apa2.AUDIT_PROCEDURE_ID=apa.AUDIT_PROCEDURE_ID);
1663 FOR ap_task in c_tasks LOOP
1664 update amw_ap_associations set pk4=ap_task.dest_task_id
1665 where pk1=p_dest_project_id and pk4=ap_task.src_task_id;
1666 END LOOP;
1667
1668 select category_id into v_category_id
1669 from fnd_document_categories where name = 'AMW_WORK_PAPERS';
1670
1671 FOR apdetails_rec IN c_apdetails LOOP
1672 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROJECT_AP',
1673 X_from_pk1_value => p_source_project_id,
1674 X_from_pk2_value =>apdetails_rec.src_pk2,
1675 X_from_pk3_value =>apdetails_rec.src_pk4,
1676 X_from_pk4_value =>apdetails_rec.src_audit_procedure_rev_id,
1677 X_to_entity_name => 'AMW_PROJECT_AP',
1678 X_to_pk1_value => p_dest_project_id,
1679 X_to_pk2_value => apdetails_rec.dest_pk2,
1680 X_to_pk3_value => apdetails_rec.dest_pk4,
1681 X_to_pk4_value => apdetails_rec.dest_audit_procedure_rev_id,
1682 X_FROM_CATEGORY_ID => v_category_id,
1683 X_TO_CATEGORY_ID => v_category_id);
1684
1685 END LOOP;
1686
1687 update amw_ap_associations set object_type = 'PROJECT'
1688 where object_type = 'PROJECT_NEW'
1689 and pk1 = p_dest_project_id;
1690
1691 For ap_task_icm in c_task_icm
1692 LOOP
1693 -- select src.audit_procedure_rev_id into l_audit_procedure_rev_id
1694 -- from amw_ap_associations dest ,amw_ap_associations src
1695 -- where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1696 -- and src.audit_procedure_id=dest.audit_procedure_id
1697 -- and src.pk4=ap_task_icm.src_task_id
1698 -- and src.audit_procedure_rev_id=dest.audit_procedure_rev_id;
1699
1700 update amw_ap_associations
1701 set pk4= ap_task_icm.dest_task_id
1702 where pk1=p_dest_project_id
1703 and audit_procedure_id in (
1704 select src.audit_procedure_id
1705 from amw_ap_associations dest ,amw_ap_associations src
1706 where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1707 and src.audit_procedure_id=dest.audit_procedure_id
1708 and src.pk4=ap_task_icm.src_task_id
1709 and src.association_creation_date is null);
1710
1711 update fnd_attached_documents
1712 set pk3_value=ap_task_icm.dest_task_id
1713 where pk1_value=to_char(p_dest_project_id) and pk3_value=-1
1714 and pk4_value in (
1715 select src.audit_procedure_rev_id
1716 from amw_ap_associations dest ,amw_ap_associations src
1717 where dest.pk1=p_dest_project_id and src.pk1=p_source_project_id
1718 and src.audit_procedure_id=dest.audit_procedure_id
1719 and src.pk4=ap_task_icm.src_task_id
1720 and src.association_creation_date is null) ;
1721
1722 END LOOP;
1723
1724
1725 END cp_tasks_all;
1726
1727
1728 PROCEDURE COPY_SCOPE_INEFF_CONTROLS(
1729 p_source_entity_id IN NUMBER,
1730 p_target_entity_id IN NUMBER,
1731 x_return_status OUT nocopy VARCHAR2
1732 ) IS
1733 l_audit_project_id NUMBER;
1734 BEGIN
1735
1736
1737 INSERT INTO AMW_EXECUTION_SCOPE (
1738 EXECUTION_SCOPE_ID,
1739 ENTITY_TYPE,
1740 ENTITY_ID,
1741 CREATED_BY,
1742 CREATION_DATE,
1743 LAST_UPDATE_DATE,
1744 LAST_UPDATED_BY,
1745 LAST_UPDATE_LOGIN,
1746 SCOPE_CHANGED_STATUS,
1747 LEVEL_ID,
1748 SUBSIDIARY_VS,
1749 SUBSIDIARY_CODE,
1750 LOB_VS,
1751 LOB_CODE,
1752 ORGANIZATION_ID,
1753 PROCESS_ID,
1754 PROCESS_ORG_REV_ID,
1755 TOP_PROCESS_ID,
1756 PARENT_PROCESS_ID)
1757
1758 SELECT amw_execution_scope_s.nextval,
1759 'PROJECT',
1760 p_target_entity_id,
1761 FND_GLOBAL.USER_ID,
1762 SYSDATE,
1763 SYSDATE,
1764 FND_GLOBAL.USER_ID,
1765 FND_GLOBAL.USER_ID,
1766 'C',
1767 LEVEL_ID,
1768 SUBSIDIARY_VS,
1769 SUBSIDIARY_CODE,
1770 LOB_VS,
1771 LOB_CODE,
1772 ORGANIZATION_ID,
1773 PROCESS_ID,
1774 PROCESS_ORG_REV_ID,
1775 TOP_PROCESS_ID,
1776 PARENT_PROCESS_ID
1777 FROM AMW_EXECUTION_SCOPE aes
1778 WHERE ENTITY_TYPE = 'PROJECT'
1779 AND ENTITY_ID = p_source_entity_id
1780 AND aes.PROCESS_ID is not null
1781 and exists(
1782 select 1 from amw_control_associations where pk1=p_source_entity_id and object_type='PROJECT'
1783 and control_id not in (select pk1_value from amw_opinions_v where pk2_value =p_source_entity_id
1784 and audit_result_code ='EFFECTIVE' and
1785 object_name='AMW_ORG_CONTROL') and pk3=aes.PROCESS_ID);
1786
1787
1788 INSERT INTO AMW_EXECUTION_SCOPE (
1789 EXECUTION_SCOPE_ID,
1790 ENTITY_TYPE,
1791 ENTITY_ID,
1792 CREATED_BY,
1793 CREATION_DATE,
1794 LAST_UPDATE_DATE,
1795 LAST_UPDATED_BY,
1796 LAST_UPDATE_LOGIN,
1797 SCOPE_CHANGED_STATUS,
1798 LEVEL_ID,
1799 SUBSIDIARY_VS,
1800 SUBSIDIARY_CODE,
1801 LOB_VS,
1802 LOB_CODE,
1803 ORGANIZATION_ID,
1804 PROCESS_ID,
1805 PROCESS_ORG_REV_ID,
1806 TOP_PROCESS_ID,
1807 PARENT_PROCESS_ID)
1808 SELECT amw_execution_scope_s.nextval,
1809 'PROJECT',
1810 p_target_entity_id,
1811 FND_GLOBAL.USER_ID,
1812 SYSDATE,
1813 SYSDATE,
1814 FND_GLOBAL.USER_ID,
1815 FND_GLOBAL.USER_ID,
1816 'C',
1817 LEVEL_ID,
1818 SUBSIDIARY_VS,
1819 SUBSIDIARY_CODE,
1820 LOB_VS,
1821 LOB_CODE,
1822 ORGANIZATION_ID,
1823 PROCESS_ID,
1824 PROCESS_ORG_REV_ID,
1825 TOP_PROCESS_ID,
1826 PARENT_PROCESS_ID
1827 FROM AMW_EXECUTION_SCOPE aes
1828 WHERE ENTITY_TYPE = 'PROJECT'
1829 AND ENTITY_ID = p_source_entity_id
1830 AND aes.PARENT_PROCESS_ID =-1
1831 AND exists(select 1 from AMW_EXECUTION_SCOPE aes2 where
1832 exists (select 1 from amw_control_associations where pk1=p_source_entity_id and object_type='PROJECT'
1833 and control_id not in (select pk1_value from amw_opinions_v where pk2_value =p_source_entity_id
1834 and audit_result_code ='EFFECTIVE' and
1835 object_name='AMW_ORG_CONTROL') and pk3=aes2.PROCESS_ID) and aes.process_id=aes2.parent_process_id );
1836
1837
1838 INSERT INTO AMW_EXECUTION_SCOPE (
1839 EXECUTION_SCOPE_ID,
1840 ENTITY_TYPE,
1841 ENTITY_ID,
1842 CREATED_BY,
1843 CREATION_DATE,
1844 LAST_UPDATE_DATE,
1845 LAST_UPDATED_BY,
1846 LAST_UPDATE_LOGIN,
1847 SCOPE_CHANGED_STATUS,
1848 LEVEL_ID,
1849 SUBSIDIARY_VS,
1850 SUBSIDIARY_CODE,
1851 LOB_VS,
1852 LOB_CODE,
1853 ORGANIZATION_ID,
1854 PROCESS_ID,
1855 PROCESS_ORG_REV_ID,
1856 TOP_PROCESS_ID,
1857 PARENT_PROCESS_ID)
1858 SELECT amw_execution_scope_s.nextval,
1859 'PROJECT',
1860 p_target_entity_id,
1861 FND_GLOBAL.USER_ID,
1862 SYSDATE,
1863 SYSDATE,
1864 FND_GLOBAL.USER_ID,
1865 FND_GLOBAL.USER_ID,
1866 'C',
1867 LEVEL_ID,
1868 SUBSIDIARY_VS,
1869 SUBSIDIARY_CODE,
1870 LOB_VS,
1871 LOB_CODE,
1872 ORGANIZATION_ID,
1873 PROCESS_ID,
1874 PROCESS_ORG_REV_ID,
1875 TOP_PROCESS_ID,
1876 PARENT_PROCESS_ID
1877 FROM AMW_EXECUTION_SCOPE aes
1878 WHERE ENTITY_TYPE = 'PROJECT'
1879 AND ENTITY_ID = p_source_entity_id
1880 AND aes.PROCESS_ID is null
1881 and exists(
1882 select 1 from amw_control_associations where pk1=p_source_entity_id and object_type='PROJECT'
1883 and control_id not in (select pk1_value from amw_opinions_v where pk2_value =p_source_entity_id
1884 and audit_result_code ='EFFECTIVE' and
1885 object_name='AMW_ORG_CONTROL') and pk3 is null);
1886
1887
1888
1889
1890 --select audit_project_id into l_audit_project_id from AMW_AUDIT_SCOPE_PROCESSES where audit_project_id=59134;
1891 --return x_return_status;
1892 END COPY_SCOPE_INEFF_CONTROLS;
1893 END AMW_AUDIT_ENGAGEMENT_PVT;