[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CORE2
Source
1 package body PA_PROJECT_CORE2 as
2 -- $Header: PAXPCO2B.pls 120.6.12010000.2 2008/09/04 19:37:12 snizam ship $
3
4
5 --
6 -- PROCEDURE
7 -- copy_task
8 -- PURPOSE
9 --
10 -- The objective of this procedure is to create new
11 -- tasks for a project by copying the tasks of another project.
12 -- Task level information such as transaction controls, billing
13 -- assignment, project asset assignments,
14 -- burden schedules, and overrides will also be copied.
15 --
16 -- Users must pass in x_orig_project_id and x_new_project_id.
17 --
18 procedure copy_task ( x_orig_project_id IN number
19 , x_new_project_id IN number
20 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
21 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
22 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
23 is
24
25 --Below code added for selective copy project. Tracking Bug No. 3464332
26 --This cursor retrieves the values of various flags from the global temporary table
27 CURSOR cur_get_flag(p_flag_name IN VARCHAR2) IS
28 SELECT FLAG
29 FROM PA_PROJECT_COPY_OPTIONS_TMP
30 WHERE CONTEXT = p_flag_name ;
31
32 -- Bug 4188514
33 CURSOR cur_sel_ver_id IS
34 Select version_id
35 from PA_PROJECT_COPY_OPTIONS_TMP
36 where CONTEXT = 'WORKPLAN'
37 AND VERSION_ID IS NOT NULL;
38 -- Bug 4188514
39
40 l_fin_tasks_flag VARCHAR2(1);
41 l_fn_dff_flag VARCHAR2(1);
42 l_copy_dff_flag VARCHAR2(1);
43 l_fin_txn_control_flag VARCHAR2(1);
44 l_fn_cb_overrides_flag VARCHAR2(1);
45 l_fn_asset_assignments_flag VARCHAR2(1);
46
47 x_old_proj_start date;
48 x_new_proj_start date;
49 x_new_proj_completion date;
50 x_old_proj_org_id Number;
51 x_new_proj_org_id Number;
52 x_delta number default NULL;
53
54 old_stack varchar2(630);
55 l_wp_separate_from_fn varchar2(1);
56 -- Bug 4104042
57 l_shared VARCHAR2(1);
58 l_sel_version_id Number := NULL;
59 l_template_flag VARCHAR2(1);
60 l_versioning_enabled VARCHAR2(1) := Null;
61 l_workplan_enabled VARCHAR2(1) := 'N';
62 -- Bug 4104042
63 begin
64
65 x_err_code := 0;
66 old_stack := x_err_stack;
67
68 x_err_stack := x_err_stack || '->copy_task';
69 savepoint copy_task;
70
71 -- 1) get original and new project start and completion dates
72 -- 2) calculate shift day x_delta
73 -- 3) get burden flag for project type of new project
74 declare
75
76 cursor c1 is
77 select old.start_date,
78 new.start_date,
79 new.completion_date,
80 old.carrying_out_organization_id,
81 new.carrying_out_organization_id
82 from pa_projects old, pa_projects new
83 where old.project_id = x_orig_project_id
84 and new.project_id = x_new_project_id;
85
86 -- use min(start_date) as pseudo original project start
87 cursor c2 is
88 select min(start_date) min_start
89 from pa_tasks
90 where project_id = x_orig_project_id;
91
92 c2_rec c2%rowtype;
93
94 begin
95 open c1;
96 fetch c1 into
97 x_old_proj_start,
98 x_new_proj_start,
99 x_new_proj_completion,
100 x_old_proj_org_id,
101 x_new_proj_org_id;
102 close c1;
103
104 if (x_new_proj_start is null) then
105 x_delta := 0;
106 elsif (x_old_proj_start is not null) then
107 --Changed by rtarway for BUG 3875746
108 --x_delta := x_new_proj_start - x_old_proj_start;
109 x_delta := trunc(x_new_proj_start) - trunc(x_old_proj_start);
110 else
111 open c2;
112 fetch c2 into c2_rec;
113 if c2%found then
114 --Changed by rtarway for BUG 3875746
115 --x_delta := x_new_proj_start - c2_rec.min_start;
116 x_delta := trunc(x_new_proj_start) - trunc(c2_rec.min_start);
117 end if;
118 close c2;
119 end if;
120
121 end;
122
123 -- create new tasks for new project with dates adjusted
124 -- if old task's org id = old project's org id and
125 -- new project's org id != old project's org id then
126 -- new task would have the new org id
127 -- else new task would have the old task's org id
128 --Below code added for selective copy project. Tracking Bug No. 3464332
129 --Check whether the Financial Tasks flag is checked or not
130 OPEN cur_get_flag('FN_FIN_TASKS_FLAG');
131 FETCH cur_get_flag INTO l_fin_tasks_flag;
132 CLOSE cur_get_flag;
133
134 OPEN cur_get_flag('FN_DFF_FLAG');
135 FETCH cur_get_flag INTO l_fn_dff_flag;
136 CLOSE cur_get_flag;
137
138 IF 'Y' = l_fn_dff_flag AND 'Y' = l_fin_tasks_flag THEN
139 l_copy_dff_flag := 'Y';
140 ELSE
141 l_copy_dff_flag := 'N';
142 END IF;
143
144 x_err_stage := 'copying tasks for project '|| x_new_project_id ||
145 ' by copying tasks from project '|| x_orig_project_id;
146
147 -- Bug 4104042 - Begin
148
149 l_workplan_enabled := PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS( x_orig_project_id );
150
151 IF NVL( l_workplan_enabled, 'N' ) = 'Y' THEN
152 --Check whether the structures are shared or not in the source project
153 l_shared := PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled( x_orig_project_id );
154 ELSE
155 l_shared := 'N';
156 END IF;
157
158 l_versioning_enabled := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(x_orig_project_id);
159
160 select template_flag into l_template_flag
161 from pa_projects_all
162 where project_id = x_orig_project_id;
163
164 IF l_shared = 'Y' and
165 l_versioning_enabled = 'Y' and -- Bug 4188514
166 l_template_flag = 'N' -- Bug 4188514
167 THEN
168 -- Bug 4188514 : Used Below cursor instead of direct query to avoid no data found
169 OPEN cur_sel_ver_id;
170 FETCH cur_sel_ver_id INTO l_sel_version_id;
171 CLOSE cur_sel_ver_id;
172 END IF;
173
174
175 -- Bug 4104042 - End
176
177 l_wp_separate_from_fn := nvl ( PA_PROJ_TASK_STRUC_PUB.IS_WP_SEPARATE_FROM_FN ( x_orig_project_id ), 'N' ) ;
178
179
180 INSERT INTO pa_tasks (
181 task_id
182 , project_id
183 , task_number
184 , task_name
185 , long_task_name /* Bug#2638968 */
186 , last_update_date
187 , last_updated_by
188 , creation_date
189 , created_by
190 , last_update_login
191 , wbs_level
192 , top_task_id
193 , parent_task_id
194 , ready_to_bill_flag
195 , ready_to_distribute_flag
196 , billable_flag
197 , chargeable_flag
198 , limit_to_txn_controls_flag
199 , description
200 , service_type_code
201 , task_manager_person_id
202 , carrying_out_organization_id
203 , start_date
204 , completion_date
205 , labor_std_bill_rate_schdl
206 , labor_bill_rate_org_id
207 , labor_schedule_fixed_date
208 , labor_schedule_discount
209 , non_labor_std_bill_rate_schdl
210 , non_labor_bill_rate_org_id
211 , non_labor_schedule_fixed_date
212 , non_labor_schedule_discount
213 , attribute_category
214 , attribute1
215 , attribute2
216 , attribute3
217 , attribute4
218 , attribute5
219 , attribute6
220 , attribute7
221 , attribute8
222 , attribute9
223 , attribute10
224 , cost_ind_rate_sch_id
225 , rev_ind_rate_sch_id
226 , inv_ind_rate_sch_id
227 , cost_ind_sch_fixed_date
228 , rev_ind_sch_fixed_date
229 , inv_ind_sch_fixed_date
230 , labor_sch_type
231 , non_labor_sch_type
232 , allow_cross_charge_flag
233 , project_rate_date
234 , project_rate_type
235 , cc_process_labor_flag
236 , labor_tp_schedule_id
237 , labor_tp_fixed_date
238 , cc_process_nl_flag
239 , nl_tp_schedule_id
240 , nl_tp_fixed_date
241 , receive_project_invoice_flag
242 , work_type_id
243 , job_bill_rate_schedule_id
244 , emp_bill_rate_schedule_id
245 , taskfunc_cost_rate_type
246 , taskfunc_cost_rate_date
247 , non_lab_std_bill_rt_sch_id
248 , labor_disc_reason_code
249 , non_labor_disc_reason_code
250 --PA L Changes 2872708
251 , retirement_cost_flag
252 , cint_eligible_flag
253 --End PA L Changes 2872708
254 , gen_etc_source_code --Bug 3846768
255 )
256 -- labor_cost_multiplier_name is deliberately NOT being copied to the new task
257 -- See bug 402125 for details
258 -- Bug 5034402: If the start/end date of the source task is NULL then copied
259 -- start/end date of the target project to start/end date of target task.Added by sunkalya for porting fix from Bug#5014950 to R12
260 SELECT
261 pa_tasks_s.nextval
262 , x_new_project_id
263 , t.task_number
264 , t.task_name
265 , nvl(t.long_task_name, t.task_name) /* Bug#2638968 */
266 , sysdate
267 , FND_GLOBAL.USER_ID
268 , sysdate
269 , FND_GLOBAL.USER_ID
270 , FND_GLOBAL.LOGIN_ID
271 , t.wbs_level
272 , t.top_task_id
273 , t.parent_task_id
274 , t.ready_to_bill_flag
275 , t.ready_to_distribute_flag
276 , t.billable_flag
277 , t.chargeable_flag
278 , t.limit_to_txn_controls_flag
279 , t.description
280 , t.service_type_code
281 , t.task_manager_person_id
282 , decode(t.carrying_out_organization_id,x_old_proj_org_id,
283 x_new_proj_org_id,t.carrying_out_organization_id)
284 , to_date(decode(x_delta, null, to_char(x_new_proj_start,'J'),
285 decode(to_char(t.start_date,'J'), null, to_char(x_new_proj_start,'J'),-- Bug 5034402
286 decode(to_char(x_new_proj_completion,'J'), null, to_char(t.start_date,'J')+x_delta,
287 least(to_char(x_new_proj_completion,'J'), to_char(t.start_date,'J') + x_delta)))),'J')
288 , to_date(decode(x_delta, null, to_char(x_new_proj_completion,'J'),
289 decode(to_char(t.completion_date,'J'), null, to_char(x_new_proj_completion,'J'),-- Bug 5034402
290 decode(to_char(x_new_proj_completion,'J'), null, to_char(t.completion_date,'J')+x_delta,
291 least(to_char(x_new_proj_completion,'J'), to_char(t.completion_date,'J') + x_delta)))),'J')
292 , t.labor_std_bill_rate_schdl
293 , t.labor_bill_rate_org_id
294 , t.labor_schedule_fixed_date
295 , t.labor_schedule_discount
296 , t.non_labor_std_bill_rate_schdl
297 , t.non_labor_bill_rate_org_id
298 , t.non_labor_schedule_fixed_date
299 , t.non_labor_schedule_discount
300 , decode(l_copy_dff_flag,'Y',t.attribute_category,null)
301 , decode(l_copy_dff_flag,'Y',t.attribute1,null)
302 , decode(l_copy_dff_flag,'Y',t.attribute2,null)
303 , decode(l_copy_dff_flag,'Y',t.attribute3,null)
304 , decode(l_copy_dff_flag,'Y',t.attribute4,null)
305 , decode(l_copy_dff_flag,'Y',t.attribute5,null)
306 , decode(l_copy_dff_flag,'Y',t.attribute6,null)
307 , decode(l_copy_dff_flag,'Y',t.attribute7,null)
308 , decode(l_copy_dff_flag,'Y',t.attribute8,null)
309 , decode(l_copy_dff_flag,'Y',t.attribute9,null)
310 , decode(l_copy_dff_flag,'Y',t.attribute10,null)
311 , t.cost_ind_rate_sch_id
312 , t.rev_ind_rate_sch_id
313 , t.inv_ind_rate_sch_id
314 , t.cost_ind_sch_fixed_date
315 , t.rev_ind_sch_fixed_date
316 , t.inv_ind_sch_fixed_date
317 , t.labor_sch_type
318 , t.non_labor_sch_type
319 , t.allow_cross_charge_flag
320 , t.project_rate_date
321 , t.project_rate_type
322 , t.cc_process_labor_flag
323 , t.labor_tp_schedule_id
324 , t.labor_tp_fixed_date
325 , t.cc_process_nl_flag
326 , t.nl_tp_schedule_id
327 , t.nl_tp_fixed_date
328 , t.receive_project_invoice_flag
329 , t.work_type_id
330 , t.job_bill_rate_schedule_id
331 , t.emp_bill_rate_schedule_id
332 , t.taskfunc_cost_rate_type
333 , t.taskfunc_cost_rate_date
334 , t.non_lab_std_bill_rt_sch_id
335 , t.labor_disc_reason_code
336 , t.non_labor_disc_reason_code
337 --PA L Changes 2872708
338 , t.retirement_cost_flag
339 , t.cint_eligible_flag
340 --End PA L Changes 2872708
341 , gen_etc_source_code
342 --commenting out the rest for bug 3924597
343 --gen_etc_source_code column modified by rtarway for BUG 3924597
344 --, decode ( gen_etc_source_code, null ,
345 -- decode (
346 -- l_wp_separate_from_fn,
347 -- 'N', 'WORKPLAN_RESOURCE',
348 -- 'FINANCIAL_PLAN'
349 -- )
350 -- ) --Bug 3846768
351 FROM
352 pa_tasks t
353 WHERE
354 t.project_id = x_orig_project_id;
355
356
357 if (SQL%FOUND) then
358
359 -- Bug 4104042 - Begin
360 x_err_stage := 'Delete extra tasks';
361
362 IF l_template_flag = 'N' AND
363 l_versioning_enabled = 'Y' AND
364 l_shared = 'Y' AND
365 l_sel_version_id is not NULL
366 THEN
367 Delete from pa_tasks t where
368 t.project_id = x_new_project_id
369 and not exists (select 1
370 from pa_proj_element_versions v,
371 pa_tasks old_tsk,
372 pa_tasks new_tsk
373 where new_tsk.project_id = x_new_project_id
374 and old_tsk.project_id = x_orig_project_id
375 and old_tsk.task_number = new_tsk.task_number
376 and v.project_id = x_orig_project_id
377 and v.PARENT_STRUCTURE_VERSION_ID = l_sel_version_id
378 and v.object_type='PA_TASKS'
379 and v.proj_element_id = old_tsk.task_id
380 and t.task_id = new_tsk.task_id);
381 END IF;
382 -- Bug 4104042 - End
383
384 x_err_stage := 'update CC Tax Task Id';
385
389 P.cc_tax_task_id = (
386 UPDATE
387 pa_projects P
388 SET
390 SELECT new_tsk.task_id
391 FROM pa_tasks old_tsk
392 , pa_tasks new_tsk
393 WHERE new_tsk.project_id = x_new_project_id
394 AND old_tsk.project_id = x_orig_project_id
395 AND P.cc_tax_task_id = old_tsk.task_id
396 AND old_tsk.task_number = new_tsk.task_number )
397 WHERE P.project_id = x_new_project_id
398 AND P.cc_tax_task_id is not null;
399
400 x_err_stage := 'update parent task id';
401
402 UPDATE
403 pa_tasks T
404 SET
405 T.parent_task_id = (
406 SELECT new_tsk.task_id
407 FROM pa_tasks old_tsk
408 , pa_tasks new_tsk
409 WHERE new_tsk.project_id = x_new_project_id
410 AND old_tsk.project_id = x_orig_project_id
411 AND T.parent_task_id = old_tsk.task_id
412 AND old_tsk.task_number = new_tsk.task_number )
413 WHERE T.project_id = x_new_project_id;
414
415 x_err_stage := 'update top task id';
416
417 UPDATE
418 pa_tasks T
419 SET
420 T.top_task_id = (
421 SELECT new_tsk.task_id
422 FROM pa_tasks old_tsk
423 , pa_tasks new_tsk
424 WHERE new_tsk.project_id = x_new_project_id
425 AND old_tsk.project_id = x_orig_project_id
426 AND T.top_task_id = old_tsk.task_id
427 AND old_tsk.task_number = new_tsk.task_number )
428 WHERE T.project_id = x_new_project_id;
429
430 /* commenting out the following code and moving it in PA_PROJECT_CORE1.copy_project
431 api with changes made in the cursor
432 --bug 3068506
433 -- This PLSQL block has been added for copying the attachments
434 -- to all the tasks while copying a project
435
436 Begin
437 Declare
438 cursor c_attach_tasks is
439 select orig.task_id orig_task_id,
440 new.task_id new_task_id
441 from pa_tasks orig, pa_tasks new
442 where orig.project_id = x_orig_project_id
443 and new.task_number = orig.task_number
444 and new.project_id = x_new_project_id ;
445
446 c_atch c_attach_tasks%rowtype ;
447
448 begin
449 open c_attach_tasks;
450 loop
451 fetch c_attach_tasks
452 into c_atch ;
453 if c_attach_tasks%notfound then
454 exit ;
455 end if;
456 fnd_attached_documents2_pkg.copy_attachments('PA_TASKS',
457 c_atch.orig_task_id,
458 null, null, null, null,
459 'PA_TASKS',
460 c_atch.new_task_id,
461 null, null, null, null,
462 FND_GLOBAL.USER_ID,
463 FND_GLOBAL.LOGIN_ID,
464 275, null, null);
465
466 end loop ;
467 close c_attach_tasks;
468 exception
469 when NO_DATA_FOUND then
470 null;
471 when others then
472 null ;
473 end ;
474 end ;
475
476 -- End of the attachment call
477 */
478
479 else
480 /*
481 x_err_code := 10;
482 x_err_stage := 'PA_NO_TASK_COPIED';
483 rollback to copy_task;
484 return;
485 */ --bug 2883515
486 null;
490
487 end if;
488
489 -- copying burden schedule for Task:
491 x_err_stage := 'copying task level burden schedules ';
492
493 INSERT INTO pa_ind_rate_schedules (
494 IND_RATE_SCH_ID,
495 IND_RATE_SCH_NAME,
496 BUSINESS_GROUP_ID,
497 DESCRIPTION,
498 START_DATE_ACTIVE,
499 END_DATE_ACTIVE,
500 LAST_UPDATE_DATE,
501 LAST_UPDATED_BY,
502 CREATED_BY,
503 CREATION_DATE,
504 LAST_UPDATE_LOGIN,
505 COST_PLUS_STRUCTURE,
506 IND_RATE_SCHEDULE_TYPE,
507 PROJECT_ID,
508 TASK_ID,
509 COST_OVR_SCH_FLAG,
510 REV_OVR_SCH_FLAG,
511 INV_OVR_SCH_FLAG,
512 ORGANIZATION_STRUCTURE_ID, --Added these 3 columns for the bug 2581491
513 ORG_STRUCTURE_VERSION_ID,
514 START_ORGANIZATION_ID,
515 IND_RATE_SCH_USAGE --bug 3053508
516 )
517 select
518 pa_ind_rate_schedules_s.nextval,
519 to_char(new_tsk.task_id) ||
520 substr(s.ind_rate_sch_name,
521 instr(s.ind_rate_sch_name, '-', -1)),
522 s.business_group_id,
523 s.DESCRIPTION,
524 decode(x_delta, null, sysdate,
525 s.start_date_active + x_delta),
526 decode(x_delta, null, sysdate,
527 s.end_date_active + x_delta),
528 sysdate,
529 FND_GLOBAL.USER_ID,
530 FND_GLOBAL.USER_ID,
531 sysdate,
532 FND_GLOBAL.LOGIN_ID,
533 s.COST_PLUS_STRUCTURE,
534 s.IND_RATE_SCHEDULE_TYPE,
535 x_new_project_id,
536 new_tsk.task_id,
537 s.COST_OVR_SCH_FLAG,
538 s.REV_OVR_SCH_FLAG,
539 s.INV_OVR_SCH_FLAG,
540 s.ORGANIZATION_STRUCTURE_ID, --Added these 3 columns for the bug 2581491
541 s.ORG_STRUCTURE_VERSION_ID,
542 s.START_ORGANIZATION_ID,
543 s.IND_RATE_SCH_USAGE --bug 3053508
544 FROM pa_ind_rate_schedules s
545 , pa_tasks new_tsk
546 , pa_tasks old_tsk
547 WHERE
548 s.project_id = x_orig_project_id
549 AND s.task_id = old_tsk.task_id
550 AND new_tsk.project_id = x_new_project_id
551 AND old_tsk.project_id = x_orig_project_id
552 AND new_tsk.task_number = old_tsk.task_number;
553
554 x_err_stage := 'copying burden schedule revisions - task ';
555
556 insert into pa_ind_rate_sch_revisions (
557 IND_RATE_SCH_REVISION_ID,
558 IND_RATE_SCH_ID,
559 IND_RATE_SCH_REVISION,
560 IND_RATE_SCH_REVISION_TYPE,
561 COMPILED_FLAG,
562 COST_PLUS_STRUCTURE,
563 START_DATE_ACTIVE,
564 END_DATE_ACTIVE,
565 COMPILED_DATE,
566 LAST_UPDATE_DATE,
570 LAST_UPDATE_LOGIN,
567 LAST_UPDATED_BY,
568 CREATED_BY,
569 CREATION_DATE,
571 REQUEST_ID,
572 PROGRAM_APPLICATION_ID,
573 PROGRAM_ID,
574 PROGRAM_UPDATE_DATE,
575 READY_TO_COMPILE_FLAG,
576 ACTUAL_SCH_REVISION_ID,
577 ORGANIZATION_STRUCTURE_ID, --Added these three columns for bug 2581491
578 ORG_STRUCTURE_VERSION_ID,
579 START_ORGANIZATION_ID)
580 select
581 pa_ind_rate_sch_revisions_s.nextval,
582 new_sch.ind_rate_sch_id,
583 rev.IND_RATE_SCH_REVISION,
584 rev.IND_RATE_SCH_REVISION_TYPE,
585 'N',
586 rev.COST_PLUS_STRUCTURE,
587 decode(x_delta, null, sysdate,
588 rev.start_date_active + x_delta),
589 decode(x_delta, null, sysdate,
590 rev.end_date_active + x_delta),
591 null,
592 sysdate,
593 FND_GLOBAL.USER_ID,
594 FND_GLOBAL.USER_ID,
595 sysdate,
596 FND_GLOBAL.LOGIN_ID,
597 rev.REQUEST_ID,
598 NULL,
599 NULL,
600 NULL,
601 'Y',
602 NULL,
603 rev.ORGANIZATION_STRUCTURE_ID, --Added these three columns for bug 2581491
604 rev.ORG_STRUCTURE_VERSION_ID,
605 rev.START_ORGANIZATION_ID
606 from pa_ind_rate_sch_revisions rev,
607 pa_ind_rate_schedules old_sch,
608 pa_ind_rate_schedules new_sch,
609 pa_tasks old_task,
610 pa_tasks new_task
611 where old_sch.project_id = x_orig_project_id
612 and old_sch.ind_rate_sch_id = rev.IND_RATE_SCH_ID
613 and old_sch.task_id = old_task.task_id
614 and new_sch.project_id = x_new_project_id
615 and new_sch.task_id = new_task.task_id
616 and old_task.project_id = x_orig_project_id
617 and new_task.projecT_id = x_new_project_id
618 and old_task.task_number = new_task.task_number
619 and substr(new_sch.ind_rate_sch_name,
620 instr(new_sch.ind_rate_sch_name, '-', -1))
621 = substr(old_sch.ind_rate_sch_name,
622 instr(old_sch.ind_rate_sch_name, '-', -1));
623
624 insert into pa_ind_cost_multipliers (
625 IND_RATE_SCH_REVISION_ID,
626 ORGANIZATION_ID,
627 IND_COST_CODE,
628 MULTIPLIER,
629 LAST_UPDATE_DATE,
630 LAST_UPDATED_BY,
631 CREATED_BY,
632 CREATION_DATE,
633 LAST_UPDATE_LOGIN )
634 select
635 new_rev.IND_RATE_SCH_REVISION_ID,
636 mult.ORGANIZATION_ID,
637 mult.IND_COST_CODE,
638 mult.MULTIPLIER,
639 sysdate,
640 FND_GLOBAL.USER_ID,
641 FND_GLOBAL.USER_ID,
642 sysdate,
643 FND_GLOBAL.LOGIN_ID
647 pa_ind_rate_schedules old_sch,
644 from pa_ind_cost_multipliers mult,
645 pa_ind_rate_sch_revisions old_rev,
646 pa_ind_rate_sch_revisions new_rev,
648 pa_ind_rate_schedules new_sch,
649 pa_tasks old_task,
650 pa_tasks new_task
651 where old_rev.IND_RATE_SCH_REVISION_ID =
652 mult.IND_RATE_SCH_REVISION_ID
653 and old_rev.IND_RATE_SCH_REVISION =
654 new_rev.IND_RATE_SCH_REVISION
655 and old_sch.ind_rate_sch_id = old_rev.IND_RATE_SCH_ID
656 and new_sch.ind_rate_sch_id = new_rev.IND_RATE_SCH_ID
657 and old_sch.project_id = x_orig_project_id
658 and old_sch.task_id = old_task.task_id
659 and new_sch.project_id = x_new_project_id
660 and new_sch.task_id = new_task.task_id
661 and old_task.project_id = x_orig_project_id
662 and new_task.projecT_id = x_new_project_id
663 and old_task.task_number = new_task.task_number
664 and substr(new_sch.ind_rate_sch_name,
665 instr(new_sch.ind_rate_sch_name, '-', -1))
666 = substr(old_sch.ind_rate_sch_name,
667 instr(old_sch.ind_rate_sch_name, '-', -1));
668
669 x_err_stage := 'copying txn controls for tasks ';
670 --Below code added for selective copy project. Tracking Bug No. 3464332
671 --Check whether the Transaction Controls flag is checked or not
672 OPEN cur_get_flag('FN_TXN_CONTROL_FLAG');
673 FETCH cur_get_flag INTO l_fin_txn_control_flag;
674 CLOSE cur_get_flag;
675
676 IF 'Y' = l_fin_txn_control_flag AND 'Y' = l_fin_tasks_flag THEN
677 INSERT INTO pa_transaction_controls (
678 project_id
679 , task_id
680 , start_date_active
681 , chargeable_flag
682 , billable_indicator
683 , creation_date
684 , created_by
685 , last_update_date
686 , last_updated_by
687 , last_update_login
688 , person_id
689 , expenditure_category
690 , expenditure_type
691 , non_labor_resource
692 , scheduled_exp_only
693 , end_date_active
694 -- added for bug 4657420
695 , workplan_res_only_flag
696 , employees_only_flag)
697 SELECT
698 x_new_project_id
699 , new_tsk.task_id
700 /*, nvl( decode(x_delta,
701 null, x_new_proj_start,
702 decode(new_tsk.start_date,
703 null, tc.start_date_active + x_delta,
704 tc.start_date_active +
705 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)
706 ) --added nvl here for bug#5452656 */
707 , nvl(decode(x_delta,null, x_new_proj_start,
708 LEAST(GREATEST(NVL(TO_DATE((tc.start_date_active + x_delta)),NVL(TO_DATE((new_tsk.start_date)),trunc(SYSDATE))) ,
709 NVL(TO_DATE((new_tsk.start_date)),
710 NVL(TO_DATE((tc.start_date_active + x_delta)),trunc(SYSDATE))),x_new_proj_start),
711 nvl(nvl(new_tsk.completion_date,x_new_proj_completion),trunc(SYSDATE)))),trunc(SYSDATE)) --Bug#6880617
712 , tc.chargeable_flag
713 , tc.billable_indicator
714 , sysdate
715 , FND_GLOBAL.USER_ID
716 , sysdate
717 , FND_GLOBAL.USER_ID
718 , FND_GLOBAL.LOGIN_ID
719 , tc.person_id
720 , tc.expenditure_category
721 , tc.expenditure_type
722 , tc.non_labor_resource
723 , tc.scheduled_exp_only
724 , decode(tc.end_date_active, null, null,
725 decode(x_delta,
726 null, tc.end_date_active +
727 (x_new_proj_start - tc.start_date_active),
728 decode(new_tsk.start_date,
729 null, tc.end_date_active + x_delta,
730 tc.end_date_active +
731 (new_tsk.start_date - old_tsk.start_date))))
732 -- added for bug 4657420
733 , tc.workplan_res_only_flag
734 , tc.employees_only_flag
735 FROM
736 pa_transaction_controls tc
737 , pa_tasks new_tsk
738 , pa_tasks old_tsk
739 WHERE
740 tc.project_id = x_orig_project_id
741 AND tc.task_id = old_tsk.task_id
742 AND new_tsk.project_id = x_new_project_id
743 AND old_tsk.project_id = x_orig_project_id
744 AND new_tsk.task_number = old_tsk.task_number;
745 END IF;--IF 'Y' = l_fin_txn_control_flag THEN
746
747
748 -- copy task level billing assignment
749 x_err_stage := 'copying task level billing assignment ';
753 BILLING_EXTENSION_ID,
750
751 INSERT INTO pa_billing_assignments (
752 BILLING_ASSIGNMENT_ID,
754 PROJECT_TYPE,
755 PROJECT_ID,
756 TOP_TASK_ID,
757 AMOUNT,
758 PERCENTAGE,
759 ACTIVE_FLAG,
760 CREATION_DATE,
761 CREATED_BY,
762 LAST_UPDATE_DATE,
763 LAST_UPDATED_BY,
764 LAST_UPDATE_LOGIN,
765 ATTRIBUTE_CATEGORY,
766 ATTRIBUTE1,
767 ATTRIBUTE2,
768 ATTRIBUTE3,
769 ATTRIBUTE4,
770 ATTRIBUTE5,
771 ATTRIBUTE6,
772 ATTRIBUTE7,
773 ATTRIBUTE8,
774 ATTRIBUTE9,
775 ATTRIBUTE10,
776 ATTRIBUTE11,
777 ATTRIBUTE12,
778 ATTRIBUTE13,
779 ATTRIBUTE14,
780 ATTRIBUTE15,
781 DISTRIBUTION_RULE,
782 /* Added columns for bug#2658340 */
783 ORG_ID,
784 RATE_OVERRIDE_CURRENCY_CODE,
785 PROJECT_CURRENCY_CODE,
786 PROJECT_RATE_TYPE,
787 PROJECT_RATE_DATE,
788 PROJECT_EXCHANGE_RATE,
789 PROJFUNC_CURRENCY_CODE,
790 PROJFUNC_RATE_TYPE,
791 PROJFUNC_RATE_DATE,
792 PROJFUNC_EXCHANGE_RATE,
793 FUNDING_RATE_TYPE,
794 FUNDING_RATE_DATE,
795 FUNDING_EXCHANGE_RATE,
796 RECORD_VERSION_NUMBER)
797 select
798 pa_billing_assignments_s.nextval,
799 a.BILLING_EXTENSION_ID,
800 NULL, --for bug 3539025
801 x_new_project_id, -- bug 4189010
802 new_tsk.task_id,
803 a.AMOUNT,
804 a.PERCENTAGE,
805 a.ACTIVE_FLAG,
806 sysdate,
807 FND_GLOBAL.USER_ID,
808 sysdate,
809 FND_GLOBAL.USER_ID,
810 FND_GLOBAL.LOGIN_ID,
811 a.ATTRIBUTE_CATEGORY,
812 a.ATTRIBUTE1,
813 a.ATTRIBUTE2,
814 a.ATTRIBUTE3,
815 a.ATTRIBUTE4,
816 a.ATTRIBUTE5,
817 a.ATTRIBUTE6,
818 a.ATTRIBUTE7,
819 a.ATTRIBUTE8,
820 a.ATTRIBUTE9,
821 a.ATTRIBUTE10,
822 a.ATTRIBUTE11,
823 a.ATTRIBUTE12,
824 a.ATTRIBUTE13,
825 a.ATTRIBUTE14,
826 a.ATTRIBUTE15,
827 NULL,
828 /*new_proj.DISTRIBUTION_RULE */
829 /* Added columns for bug#2658340 */
830 a.ORG_ID,
831 a.RATE_OVERRIDE_CURRENCY_CODE,
832 a.PROJECT_CURRENCY_CODE,
833 a.PROJECT_RATE_TYPE,
834 a.PROJECT_RATE_DATE,
835 a.PROJECT_EXCHANGE_RATE,
836 a.PROJFUNC_CURRENCY_CODE,
837 a.PROJFUNC_RATE_TYPE,
838 a.PROJFUNC_RATE_DATE,
839 a.PROJFUNC_EXCHANGE_RATE,
840 a.FUNDING_RATE_TYPE,
841 a.FUNDING_RATE_DATE,
842 a.FUNDING_EXCHANGE_RATE,
843 1
844 from pa_billing_assignments a,
845 -- pa_projects new_proj, bug 4189010
846 pa_tasks old_tsk,
847 pa_tasks new_tsk
848 where a.project_id = x_orig_project_id
849 and a.top_task_id is not null
850 and a.top_task_id = old_tsk.task_id
851 and old_tsk.task_number = new_tsk.task_number
852 and new_tsk.project_id = x_new_project_id -- new_proj.project_id bug 4189010
853 --and new_proj.project_id = x_new_project_id; -- bug 4189010
854 and old_tsk.project_id = x_orig_project_id; -- bug 4189010
855
856
857 x_err_stage := 'copying task level project asset assignments';
858 --Below code added for selective copy project. Tracking Bug No. 3464332
859 OPEN cur_get_flag('FN_ASSET_ASSIGNMENTS_FLAG');
860 FETCH cur_get_flag INTO l_fn_asset_assignments_flag;
861 CLOSE cur_get_flag;
862
863 -- added UNION to remove bug#604496 : ashia bagai 30-dec-97
864 -- Common Cost asset assignments would have an asset id = 0
865 -- and hence would not have a relevant record in pa_project_assets
866 IF 'Y' = l_fn_asset_assignments_flag AND 'Y' = l_fin_tasks_flag THEN
867 INSERT INTO pa_project_asset_assignments (
868 PROJECT_ASSET_ID,
869 TASK_ID,
870 PROJECT_ID,
874 LAST_UPDATED_BY,
871 CREATION_DATE,
872 CREATED_BY,
873 LAST_UPDATE_DATE,
875 LAST_UPDATE_LOGIN)
876 select
877 new_asset.PROJECT_ASSET_ID,
878 new_tsk.task_id,
879 new_asset.PROJECT_ID,
880 sysdate,
881 FND_GLOBAL.USER_ID,
882 sysdate,
883 FND_GLOBAL.USER_ID,
884 FND_GLOBAL.LOGIN_ID
885 from pa_project_asset_assignments assign,
886 pa_project_assets old_asset,
887 pa_project_assets new_asset,
888 pa_tasks old_tsk,
889 pa_tasks new_tsk
890 where old_asset.project_id = x_orig_project_id
891 and old_asset.project_asset_id = assign.project_asset_id
892 and assign.task_id = old_tsk.task_id
893 /* Added the following conditions for bug#2530093 */
894 and assign.project_id = old_tsk.project_id
895 and old_tsk.project_id = old_asset.project_id /* End of conditions added for bug#2530093 */
896 and old_tsk.task_number = new_tsk.task_number
897 and new_tsk.project_id = new_asset.project_id
898 and old_asset.asset_name = new_asset.asset_name
899 and new_asset.project_id = x_new_project_id
900 UNION
901 select
902 assign.PROJECT_ASSET_ID,
903 new_tsk.task_id,
904 x_new_project_id,
905 sysdate,
906 FND_GLOBAL.USER_ID,
907 sysdate,
908 FND_GLOBAL.USER_ID,
909 FND_GLOBAL.LOGIN_ID
910 from pa_project_asset_assignments assign,
911 pa_tasks old_tsk,
912 pa_tasks new_tsk
913 where assign.project_id = x_orig_project_id
914 and assign.task_id = old_tsk.task_id
915 and old_tsk.task_number = new_tsk.task_number
916 and new_tsk.project_id = x_new_project_id
917 and assign.project_asset_id = 0;
918 END IF;--IF 'Y' = l_fn_asset_assignments_flag
919 -- end of addition for bug#604496
920
921 x_err_stage := 'copying task level job bill rate overrides';
922
923 --Below code added for selective copy project. Tracking Bug No. 3464332
924 OPEN cur_get_flag('FN_COST_BILL_OVERRIDES_FLAG');
925 FETCH cur_get_flag INTO l_fn_cb_overrides_flag;
926 CLOSE cur_get_flag;
927
928 IF 'Y' = l_fn_cb_overrides_flag THEN
929 INSERT INTO pa_job_bill_rate_overrides (
930 JOB_ID
931 , START_DATE_ACTIVE
932 , LAST_UPDATE_DATE
933 , LAST_UPDATED_BY
934 , CREATION_DATE
935 , CREATED_BY
936 , LAST_UPDATE_LOGIN
937 , RATE
938 , BILL_RATE_UNIT
939 , PROJECT_ID
940 , TASK_ID
941 , END_DATE_ACTIVE
942 , JOB_BILL_RATE_OVERRIDE_ID -- added this column for bug 2476862
943 , RATE_CURRENCY_CODE -- added this column for bug 2581491
944 -- FP.K changes msundare
945 , DISCOUNT_PERCENTAGE
946 , RATE_DISC_REASON_CODE )
947 SELECT
948 o.JOB_ID
949 , nvl(decode(x_delta,
950 null, x_new_proj_start,
951 decode(new_tsk.start_date,
952 null, o.start_date_active + x_delta,
953 o.start_date_active +
954 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)) -- Modified for bug# 5961484
955 , sysdate
956 , FND_GLOBAL.USER_ID
957 , sysdate
958 , FND_GLOBAL.USER_ID
959 , FND_GLOBAL.LOGIN_ID
960 , o.RATE
961 , o.BILL_RATE_UNIT
962 , null
963 , new_tsk.task_id
964 , decode( o.end_date_active, null, null,
965 decode( x_delta,
966 null, o.end_date_active +
967 (x_new_proj_start - o.start_date_active),
968 decode(new_tsk.start_date,
969 null, o.end_date_active + x_delta,
970 o.end_date_active +
971 (new_tsk.start_date - old_tsk.start_date))))
972 , pa_job_bill_rate_overrides_s.NEXTVAL -- added this column for bug 2476862
973 , o.RATE_CURRENCY_CODE -- added this column for bug 2581491
974 -- FP.K changes msundare
975 , o.DISCOUNT_PERCENTAGE
976 , o.RATE_DISC_REASON_CODE
977 FROM
978 pa_job_bill_rate_overrides o,
979 pa_tasks old_tsk,
983 and old_tsk.task_number = new_tsk.task_number
980 pa_tasks new_tsk
981 WHERE old_tsk.project_id = x_orig_project_id
982 and o.task_id = old_tsk.task_id
984 and new_tsk.project_id = x_new_project_id;
985 END IF;
986
987 x_err_stage := 'copying task level job bill title overrides';
988 --Below condition added for selective copy project. Tracking Bug No. 3464332
989 IF 'Y' = l_fn_cb_overrides_flag THEN
990 INSERT INTO pa_job_bill_title_overrides (
991 JOB_ID
992 , LAST_UPDATE_DATE
993 , LAST_UPDATED_BY
994 , CREATION_DATE
995 , CREATED_BY
996 , LAST_UPDATE_LOGIN
997 , START_DATE_ACTIVE
998 , BILLING_TITLE
999 , PROJECT_ID
1000 , TASK_ID
1001 , END_DATE_ACTIVE
1002 , JOB_BILL_TITLE_OVERRIDE_ID) --Added the column for the bug 2581491
1003 SELECT
1004 o.JOB_ID
1005 , sysdate
1006 , FND_GLOBAL.USER_ID
1007 , sysdate
1008 , FND_GLOBAL.USER_ID
1009 , FND_GLOBAL.LOGIN_ID
1010 , nvl(decode(x_delta,
1011 null, x_new_proj_start,
1012 decode(new_tsk.start_date,
1013 null, o.start_date_active + x_delta,
1014 o.start_date_active +
1015 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)) -- Modified for bug# 5961484
1016 , o.BILLING_TITLE
1017 , null
1018 , new_tsk.task_id
1019 , decode( o.end_date_active, null, null,
1020 decode( x_delta,
1021 null, o.end_date_active +
1022 (x_new_proj_start - o.start_date_active),
1023 decode(new_tsk.start_date,
1024 null, o.end_date_active + x_delta,
1025 o.end_date_active +
1026 (new_tsk.start_date - old_tsk.start_date))))
1027 , pa_job_bill_title_overrides_s.NEXTVAL --Added the column for the bug 2581491
1028 FROM
1029 pa_job_bill_title_overrides o,
1030 pa_tasks old_tsk,
1031 pa_tasks new_tsk
1032 WHERE old_tsk.project_id = x_orig_project_id
1033 and o.task_id = old_tsk.task_id
1034 and old_tsk.task_number = new_tsk.task_number
1035 and new_tsk.project_id = x_new_project_id;
1036 END IF;
1037
1038 x_err_stage := 'copying task level job assignment overrides';
1039 --Below condition added for selective copy project. Tracking Bug No. 3464332
1040 IF 'Y' = l_fn_cb_overrides_flag THEN
1041 INSERT INTO pa_job_assignment_overrides (
1042 PERSON_ID
1043 , LAST_UPDATE_DATE
1044 , LAST_UPDATED_BY
1045 , CREATION_DATE
1046 , CREATED_BY
1047 , LAST_UPDATE_LOGIN
1048 , START_DATE_ACTIVE
1049 , PROJECT_ID
1050 , TASK_ID
1051 , JOB_ID
1052 , BILLING_TITLE
1053 , END_DATE_ACTIVE
1054 , JOB_ASSIGNMENT_OVERRIDE_ID) --Added the column for the bug 2581491
1055 SELECT
1056 o.PERSON_ID
1057 , sysdate
1058 , FND_GLOBAL.USER_ID
1059 , sysdate
1060 , FND_GLOBAL.USER_ID
1061 , FND_GLOBAL.LOGIN_ID
1062 , nvl(decode(x_delta,
1063 null, x_new_proj_start,
1064 decode(new_tsk.start_date,
1065 null, o.start_date_active + x_delta,
1066 o.start_date_active +
1067 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)) -- Modified for bug# 5961484
1068 , null
1069 , new_tsk.task_id
1070 , o.JOB_ID
1071 , o.BILLING_TITLE
1072 , decode( o.end_date_active, null, null,
1073 decode( x_delta,
1074 null, o.end_date_active +
1075 (x_new_proj_start - o.start_date_active),
1076 decode(new_tsk.start_date,
1077 null, o.end_date_active + x_delta,
1078 o.end_date_active +
1079 (new_tsk.start_date - old_tsk.start_date))))
1080 , pa_job_assignment_overrides_s.NEXTVAL --Added the column for the bug 2581491
1081 FROM
1082 pa_job_assignment_overrides o,
1083 pa_tasks old_tsk,
1084 pa_tasks new_tsk
1085 WHERE old_tsk.project_id = x_orig_project_id
1089 END IF;
1086 and o.task_id = old_tsk.task_id
1087 and old_tsk.task_number = new_tsk.task_number
1088 and new_tsk.project_id = x_new_project_id;
1090
1091 x_err_stage := 'copying task level emp bill rate overrides';
1092 --Below condition added for selective copy project. Tracking Bug No. 3464332
1093 IF 'Y' = l_fn_cb_overrides_flag THEN
1094 INSERT into pa_emp_bill_rate_overrides (
1095 PERSON_ID
1096 , LAST_UPDATE_DATE
1097 , LAST_UPDATED_BY
1098 , CREATION_DATE
1099 , CREATED_BY
1100 , LAST_UPDATE_LOGIN
1101 , RATE
1102 , BILL_RATE_UNIT
1103 , START_DATE_ACTIVE
1104 , PROJECT_ID
1105 , TASK_ID
1106 , END_DATE_ACTIVE
1107 , EMP_BILL_RATE_OVERRIDE_ID --added this column for bug 2476862
1108 , RATE_CURRENCY_CODE -- added this column for bug 2581491
1109 -- Added for FP.K changes msundare
1110 , DISCOUNT_PERCENTAGE
1111 , RATE_DISC_REASON_CODE)
1112 SELECT
1113 o.PERSON_ID
1114 , sysdate
1115 , FND_GLOBAL.USER_ID
1116 , sysdate
1117 , FND_GLOBAL.USER_ID
1118 , FND_GLOBAL.LOGIN_ID
1119 , o.RATE
1120 , o.BILL_RATE_UNIT
1121 , nvl(decode(x_delta,
1122 null, x_new_proj_start,
1123 decode(new_tsk.start_date,
1124 null, o.start_date_active + x_delta,
1125 o.start_date_active +
1126 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)) -- Modified for bug# 5961484
1127 , null
1128 , new_tsk.task_id
1129 , decode( o.end_date_active, null, null,
1130 decode( x_delta,
1131 null, o.end_date_active +
1132 (x_new_proj_start - o.start_date_active),
1133 decode(new_tsk.start_date,
1134 null, o.end_date_active + x_delta,
1135 o.end_date_active +
1136 (new_tsk.start_date - old_tsk.start_date))))
1137 , pa_emp_bill_rate_overrides_s.NEXTVAL -- added this column for bug 2476862
1138 , o.RATE_CURRENCY_CODE -- added this column for bug 2581491
1139 -- Added for FP.K changes msundare
1140 , o.DISCOUNT_PERCENTAGE
1141 , o.RATE_DISC_REASON_CODE
1142 FROM
1143 pa_emp_bill_rate_overrides o,
1144 pa_tasks old_tsk,
1145 pa_tasks new_tsk
1146 WHERE old_tsk.project_id = x_orig_project_id
1147 and o.task_id = old_tsk.task_id
1148 and old_tsk.task_number = new_tsk.task_number
1149 and new_tsk.project_id = x_new_project_id;
1150 END IF;
1151
1152 x_err_stage := 'copying task level nl bill rate overrides';
1153 --Below condition added for selective copy project. Tracking Bug No. 3464332
1154 IF 'Y' = l_fn_cb_overrides_flag THEN
1155 INSERT INTO pa_nl_bill_rate_overrides (
1156 EXPENDITURE_TYPE
1157 , LAST_UPDATE_DATE
1158 , LAST_UPDATED_BY
1159 , CREATION_DATE
1160 , CREATED_BY
1161 , LAST_UPDATE_LOGIN
1162 , START_DATE_ACTIVE
1163 , NON_LABOR_RESOURCE
1164 , MARKUP_PERCENTAGE
1165 , BILL_RATE
1166 , PROJECT_ID
1167 , TASK_ID
1168 , END_DATE_ACTIVE
1169 , NL_BILL_RATE_OVERRIDE_ID -- added this column for bug 2476862
1170 , RATE_CURRENCY_CODE -- added this column for bug 2581491
1171 -- FP.K changes msundare
1172 , DISCOUNT_PERCENTAGE
1173 , RATE_DISC_REASON_CODE )
1174 SELECT
1175 o.EXPENDITURE_TYPE
1176 , sysdate
1177 , FND_GLOBAL.USER_ID
1178 , sysdate
1179 , FND_GLOBAL.USER_ID
1180 , FND_GLOBAL.LOGIN_ID
1181 , nvl(decode(x_delta,
1182 null, x_new_proj_start,
1183 decode(new_tsk.start_date,
1184 null, o.start_date_active + x_delta,
1185 o.start_date_active +
1186 (new_tsk.start_date - old_tsk.start_date))),trunc(o.start_date_active)) -- Modified for bug# 5961484
1187 -- Bug 6058655: overriding fix 5961484: replaced sysdate with o.start_date_active
1188 , o.NON_LABOR_RESOURCE
1189 , o.MARKUP_PERCENTAGE
1190 , o.BILL_RATE
1191 , null
1192 , new_tsk.task_id
1193 , decode( o.end_date_active, null, null,
1194 decode( x_delta,
1198 null, o.end_date_active + x_delta,
1195 null, o.end_date_active +
1196 (x_new_proj_start - o.start_date_active),
1197 decode(new_tsk.start_date,
1199 o.end_date_active +
1200 (new_tsk.start_date - old_tsk.start_date))))
1201 , pa_nl_bill_rate_overrides_s.NEXTVAL -- added this column for bug 2476862
1202 , o.RATE_CURRENCY_CODE -- added this column for bug 2581491
1203 -- FP.K changes msundare
1204 , o.DISCOUNT_PERCENTAGE
1205 , o.RATE_DISC_REASON_CODE
1206 FROM
1207 pa_nl_bill_rate_overrides o,
1208 pa_tasks old_tsk,
1209 pa_tasks new_tsk
1210 WHERE old_tsk.project_id = x_orig_project_id
1211 and o.task_id = old_tsk.task_id
1212 and old_tsk.task_number = new_tsk.task_number
1213 and new_tsk.project_id = x_new_project_id;
1214 END IF;
1215
1216 x_err_stage := 'copying task level labor multipliers ';
1217
1218 INSERT INTO pa_labor_multipliers (
1219 PROJECT_ID
1220 , TASK_ID
1221 , LABOR_MULTIPLIER
1222 , START_DATE_ACTIVE
1223 , END_DATE_ACTIVE
1224 , LAST_UPDATE_DATE
1225 , LAST_UPDATED_BY
1226 , CREATION_DATE
1227 , CREATED_BY
1228 , LAST_UPDATE_LOGIN
1229 , LABOR_MULTIPLIER_ID) --Added this column for the bug 2581491
1230 SELECT
1231 null
1232 , new_tsk.task_id
1233 , m.labor_multiplier
1234 , nvl(decode(x_delta,
1235 null, x_new_proj_start,
1236 decode(new_tsk.start_date,
1237 null, m.start_date_active + x_delta,
1238 m.start_date_active +
1239 (new_tsk.start_date - old_tsk.start_date))),trunc(sysdate)) -- Modified for bug# 5961484
1240 , decode( m.end_date_active, null, null,
1241 decode( x_delta,
1242 null, m.end_date_active +
1243 (x_new_proj_start - m.start_date_active),
1244 decode(new_tsk.start_date,
1245 null, m.end_date_active + x_delta,
1246 m.end_date_active +
1247 (new_tsk.start_date - old_tsk.start_date))))
1248 , sysdate
1249 , FND_GLOBAL.USER_ID
1250 , sysdate
1251 , FND_GLOBAL.USER_ID
1252 , FND_GLOBAL.LOGIN_ID
1253 , pa_labor_multipliers_s.NEXTVAL --Added this column for the bug 2581491
1254 FROM pa_labor_multipliers m,
1255 pa_tasks old_tsk,
1256 pa_tasks new_tsk
1257 WHERE old_tsk.project_id = x_orig_project_id
1258 and m.task_id = old_tsk.task_id
1259 and old_tsk.task_number = new_tsk.task_number
1260 and new_tsk.project_id = x_new_project_id;
1261
1262 -- anlee
1263 -- Classification enhancement changes
1264 x_err_stage := 'copying task level classifications ';
1265
1266 INSERT INTO pa_project_classes (
1267 project_id
1268 , class_code
1269 , class_category
1270 , code_percentage
1271 , object_id
1272 , object_type
1273 , last_update_date
1274 , last_updated_by
1275 , creation_date
1276 , created_by
1277 , last_update_login
1278 , attribute_category
1279 , attribute1
1280 , attribute2
1281 , attribute3
1282 , attribute4
1283 , attribute5
1284 , attribute6
1285 , attribute7
1286 , attribute8
1287 , attribute9
1288 , attribute10
1289 , attribute11
1290 , attribute12
1291 , attribute13
1292 , attribute14
1293 , attribute15 )
1294 SELECT
1295 NULL
1296 , pc.class_code
1297 , pc.class_category
1298 , pc.code_percentage
1299 , new_tsk.task_id
1300 , 'PA_TASKS'
1301 , sysdate
1302 , FND_GLOBAL.USER_ID
1303 , sysdate
1304 , FND_GLOBAL.USER_ID
1305 , FND_GLOBAL.LOGIN_ID
1306 , pc.attribute_category
1307 , pc.attribute1
1308 , pc.attribute2
1309 , pc.attribute3
1310 , pc.attribute4
1311 , pc.attribute5
1312 , pc.attribute6
1313 , pc.attribute7
1314 , pc.attribute8
1315 , pc.attribute9
1316 , pc.attribute10
1317 , pc.attribute11
1318 , pc.attribute12
1319 , pc.attribute13
1320 , pc.attribute14
1321 , pc.attribute15
1322 FROM
1323 pa_project_classes pc,
1324 pa_tasks new_tsk,
1325 pa_tasks old_tsk
1326 WHERE pc.object_id = old_tsk.task_id
1327 AND pc.object_type = 'PA_TASKS'
1328 AND new_tsk.project_id = x_new_project_id
1329 AND old_tsk.project_id = x_orig_project_id
1330 AND new_tsk.task_number = old_tsk.task_number;
1331
1332 -- anlee
1333 -- End changes
1334 x_err_stack := old_stack;
1335
1336 exception
1337 when others then
1338 x_err_code := SQLCODE;
1339 --Added by rtarway for BUG 3875746
1340 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_CORE2',
1341 p_procedure_name => 'COPY_TASK',
1342 p_error_text => SUBSTRB(SQLERRM,1,240));
1343 rollback to copy_task;
1344 raise;
1345 --End Add by rtarway for BUG 3875746
1346 end copy_task;
1347
1348
1349
1350 -- ------------------------------------------------------------
1351 -- Create_Def_Prj_Stus_Controls
1352 -- This procedure creates the default project status controls
1353 -- for a new project status. The defaults are created from
1354 -- the system status of the new status being created.
1355 --
1356 -- This procedure should be called after inserting a new
1357 -- project status.
1358 -- ------------------------------------------------------------
1359
1360 PROCEDURE Create_Def_Prj_Stus_Controls(
1361 X_Project_Status_Code IN VARCHAR2,
1362 X_Project_System_Status_Code IN VARCHAR2,
1363 X_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1364 X_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1365 X_err_stack IN OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
1366
1367
1368 l_old_stack varchar2(630);
1369 CURSOR l_get_controls_csr IS
1370 SELECT 'x' FROM pa_project_status_controls
1371 WHERE project_status_code = X_Project_Status_Code
1372 AND project_system_status_code IS NULL;
1373 l_dummy VARCHAR2(1);
1374 x_status_type VARCHAR2(30);
1375
1376 BEGIN
1377
1378 X_err_code := 0;
1379 l_old_stack := X_err_stack;
1380 X_err_stack := X_err_stack || '->Create_Def_Prj_Stus_Controls';
1381
1382 Select status_type
1383 INTO x_status_type
1384 From pa_project_statuses
1385 Where project_status_code=X_Project_Status_Code ;
1386
1387 INSERT INTO pa_project_status_controls (
1388 PROJECT_STATUS_CODE,
1389 PROJECT_SYSTEM_STATUS_CODE,
1390 ACTION_CODE,
1391 SORT_ORDER,
1392 ALLOW_OVERRIDES_FLAG,
1393 ENABLED_FLAG,
1394 START_DATE_ACTIVE,
1395 END_DATE_ACTIVE,
1396 LAST_UPDATE_DATE,
1397 LAST_UPDATED_BY,
1398 CREATION_DATE,
1399 CREATED_BY,
1400 LAST_UPDATE_LOGIN,
1401 STATUS_TYPE,
1402 COPY_TO_USER_STATUS_FLAG --Added column for bug 2581491
1403 ) SELECT
1404 X_Project_Status_Code,
1405 NULL,
1406 action_code,
1407 sort_order,
1408 allow_overrides_flag,
1409 enabled_flag,
1410 trunc(sysdate),
1411 NULL,
1412 sysdate,
1413 FND_GLOBAL.User_ID,
1414 sysdate,
1415 FND_GLOBAL.User_ID,
1416 FND_GLOBAL.Login_ID,
1417 x_status_type,
1418 COPY_TO_USER_STATUS_FLAG
1419 FROM pa_project_status_controls pas1
1420 WHERE pas1.project_system_status_code = X_Project_System_Status_Code
1421 AND nvl(pas1.copy_to_user_status_flag,'Y') <> 'N'
1422 AND NOT EXISTS
1423 (SELECT 'x' FROM pa_project_status_controls
1424 WHERE project_status_code = X_Project_Status_Code
1425 AND action_code = pas1.action_code
1426 AND project_system_status_code IS NULL);
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 X_err_code := SQLCODE;
1430
1431 END Create_Def_Prj_Stus_Controls;
1432
1433
1434
1435 END PA_PROJECT_CORE2 ;