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