[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CORE
Source
1 package body PA_PROJECT_CORE as
2 -- $Header: PAXPCORB.pls 120.13.12020000.4 2013/04/10 10:25:43 jlraj ship $
3
4
5 --
6 -- FUNCTION
7 --
8 -- Get_Message_from_stack
9 -- This function returns message from the stack and if does not
10 -- find one then returns whatever message passed to it.
11 -- HISTORY
12 -- 12-DEC-01 MAansari -Created
13
14 FUNCTION Get_Message_from_stack( p_err_stage IN VARCHAR2 ) RETURN VARCHAR2 IS
15 x_msg_count NUMBER;
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(2000);
18 l_data VARCHAR2(2000);
19 l_msg_index_out NUMBER;
20 l_app_name VARCHAR2(2000) := 'PA';
21 l_temp_name VARCHAR2(2000);
22 BEGIN
23 x_msg_count := FND_MSG_PUB.count_msg;
24
25 FND_MSG_PUB.get (
26 p_msg_index => 1,
27 p_encoded => FND_API.G_TRUE,
28 p_data => l_data,
29 p_msg_index_out => l_msg_index_out );
30
31 if l_data is not null then
32 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE => l_data,
33 APP_SHORT_NAME => l_app_name,
34 MESSAGE_NAME => l_msg_data);
35
36 FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
37 else
38 l_msg_data := p_err_stage;
39 end if;
40
41 return l_msg_data;
42
43 END Get_Message_from_stack;
44
45 --
46 -- PROCEDURE
47 -- delete_project
48 -- PURPOSE
49 -- This objective of this API is to delete projects from
50 -- the PA system. All project detail information will be
51 -- deleted. This procedure can be used by Enter Project
52 -- form and other external systems.
53 --
54 -- In order to delete a project, a project must NOT
55 -- have any of the following:
56 --
57 -- * Event
58 -- * Expenditure item
59 -- * Puchase order line
60 -- * Requisition line
61 -- * Supplier invoice (ap invoice)
62 -- * Funding
63 -- * Budget
64 -- * Committed transactions
65 -- * Compensation rule sets
66 -- * Project is referenced by others
67 --
68 -- HISTORY
69 -- 24-OCT-95 R. Chiu Created
70 -- 17-JUL-2000 Mohnish
71 -- added code for ROLE BASED SECURITY:
72 -- added the call to PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY
73 -- 19-JUL-2000 Mohnish incorporated PA_PROJECT_PARTIES_PUB API changes
74 -- 18-NOV-2002 Added call to PA_EGO_WRAPPER_PUB.check_delete_project_ok and delete_all_item_assocs for PLM
75 procedure delete_project ( x_project_id IN number
76 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
77 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
78 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
79 , x_err_stack IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
80 , x_commit IN VARCHAR2 := FND_API.G_FALSE)
81 is
82
83 old_stack varchar2(630);
84 status_code number;
85 temp_stack varchar2(630);
86 -- begin NEW code for ROLE BASED SECURITY
87 v_null_number NUMBER;
88 v_null_char VARCHAR2(255);
89 v_null_date DATE;
90 x_return_status VARCHAR2(255);
91 x_msg_count NUMBER;
92 x_msg_data VARCHAR2(2000);
93
94 -- end NEW code for ROLE BASED SECURITY
95 temp_flag VARCHAR2(1);
96
97 --new code for adding call to PA_LIFECYCLES_PUB.check_delete_project_ok
98 l_delete_ok VARCHAR2(1);
99
100 -- Bug 2898598
101 x_status VARCHAR2(30);
102 x_result VARCHAR2(30);
103
104 cursor l_project_csr (t_project_id NUMBER) is
105 select '1'
106 from dual
107 where exists (select object_id
108 from pa_project_parties
109 where object_id = t_project_id
110 and object_type = 'PA_PROJECTS');
111
112 CURSOR get_template_flag IS
113 SELECT template_flag
114 FROM PA_PROJECTS_ALL
115 WHERE project_id = x_project_id;
116
117 -- added for bug#3693197
118 CURSOR get_all_tasks IS
119 SELECT task_id
120 FROM PA_TASKS
121 WHERE project_id = x_project_id;
122
123 -- added for bug#3693197
124 l_tasks_tbl PA_PLSQL_DATATYPES.IdTabTyp;
125
126
127
128 l_template_flag VARCHAR2(1);
129 l_wp_enabled VARCHAR2(1);
130 begin
131 SAVEPOINT delete_project;
132 x_err_code := 0;
133 old_stack := x_err_stack;
134
135 x_err_stack := x_err_stack || '->delete_project';
136
137 OPEN get_template_flag;
138 FETCH get_template_flag INTO l_template_flag;
139 CLOSE get_template_flag;
140
141 --Bug 3610949 : Check for workplan enabled before deleting the data from the tables
142 --in delete_project_structure
143 l_wp_enabled := PA_PROJECT_STRUCTURE_UTILS.check_workplan_enabled(x_project_id);
144
145 -- mrajput added.
146 -- 18 Nov 2002. For Product Lifecycle Management.
147
148 PA_EGO_WRAPPER_PUB.check_delete_project_ok(
149 p_api_version => 1.0 ,
150 p_project_id => x_project_id ,
151 p_init_msg_list => NULL ,
152 x_delete_ok => l_delete_ok ,
153 x_return_status => x_return_status ,
154 x_errorcode => x_err_code ,
155 x_msg_count => x_msg_count ,
156 x_msg_data => x_msg_data );
157
158 if((x_err_code <> 0) OR (l_delete_ok <> FND_API.G_TRUE)) then
159 x_err_code := 10;
160 x_err_stack := x_err_stack || '->check PA_EGO_WRAPPER_PUB.check_delete_project_ok '|| x_project_id;
161 if l_template_flag = 'Y' then
162 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
163 else
164 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
165 end if;
166 rollback to delete_project;
167 return;
168 end if;
169
170 -- anlee
171 -- Added for ENG integration
172
173 PA_EGO_WRAPPER_PUB.check_delete_project_ok_eng(
174 p_api_version => 1.0 ,
175 p_project_id => x_project_id ,
176 p_init_msg_list => NULL ,
177 x_delete_ok => l_delete_ok ,
178 x_return_status => x_return_status ,
179 x_errorcode => x_err_code ,
180 x_msg_count => x_msg_count ,
181 x_msg_data => x_msg_data );
182
183 if((x_err_code <> 0) OR (l_delete_ok <> FND_API.G_TRUE)) then
184 x_err_code := 20;
185 x_err_stack := x_err_stack || '->check PA_EGO_WRAPPER_PUB.check_delete_project_ok_eng '|| x_project_id;
186 if l_template_flag = 'Y' then
187 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
188 else
189 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
190 end if;
191 rollback to delete_project;
192 return;
193 end if;
194
195 pa_project_utils.check_delete_project_ok(
196 x_project_id => x_project_id,
197 x_validation_mode => x_validation_mode, --bug 2947492
198 x_err_code => x_err_code,
199 x_err_stage => x_err_stage,
200 x_err_stack => x_err_stack);
201
202 if (x_err_code <> 0) then
203 --Added for bug 3617393
204 rollback to delete_project;
205 --End bug 3617393
206 return;
207 end if;
208
209 -- Delete project options
210 delete from pa_project_options
211 where project_id = x_project_id;
212
213 -- Delete project copy overides
214 delete from pa_project_copy_overrides
215 where project_id = x_project_id;
216
217 -- sacgupta
218 -- Bug 2898598 changes Start
219
220 -- Delete Resource Assignments
221 PA_ASSIGNMENTS_PUB.DELETE_PJR_TXNS
222 (p_project_id => x_project_id
223 ,p_calling_module => FND_API.G_MISS_CHAR
224 ,p_api_version => 1.0
225 ,p_init_msg_list => FND_API.G_FALSE
226 ,p_commit => FND_API.G_FALSE
227 ,p_validate_only => FND_API.G_FALSE
228 ,p_max_msg_count => FND_API.G_MISS_NUM
229 ,x_return_status => x_return_status
230 ,x_msg_count => x_msg_count
231 ,x_msg_data => x_msg_data );
232
233 IF (x_return_status <> 'S') THEN
234 x_err_code := 30;
235 x_err_stack := x_err_stack || '->Delete_PJR_Txns: '|| x_project_id;
236 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
237 ROLLBACK TO delete_project;
238 RETURN;
239 END IF;
240
241 -- Delete project subteams
242 FOR i IN (SELECT rowid row_id
243 ,project_subteam_id
244 FROM pa_project_subteams
245 WHERE object_type = 'PA_PROJECTS'
246 AND object_id = x_project_id)
247 LOOP
248
249 PA_PROJECT_SUBTEAMS_PVT.Delete_Subteam
250 ( p_api_version => 1.0
251 ,p_init_msg_list => FND_API.G_FALSE
252 ,p_commit => FND_API.G_FALSE
253 ,p_validate_only => FND_API.G_FALSE
254 ,p_validation_level => FND_API.g_valid_level_full
255 -- ,p_calling_module => NULL
256 ,p_debug_mode => 'N'
257 ,p_max_msg_count => FND_API.G_MISS_NUM
258 ,p_subteam_row_id => i.row_id
259 ,p_subteam_id => i.project_subteam_id
260 ,p_record_version_number => FND_API.G_MISS_NUM
261 ,x_return_status => x_return_status
262 ,x_msg_count => x_msg_count
263 ,x_msg_data => x_msg_data );
264
265 IF (x_return_status <> 'S') THEN
266 x_err_code := 30;
267 x_err_stack := x_err_stack || '->Delete_Subteam: '|| x_project_id;
268 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
269 ROLLBACK TO delete_project;
270 RETURN;
271 END IF;
272 END LOOP;
273
274
275 -- Bug 2898598 changes End
276
277 Open l_project_csr (x_project_id);
278 Fetch l_project_csr into temp_flag;
279 Close l_project_csr;
280
281
282 if nvl(temp_flag, 'N') = '1' then
283
284 -- Delete project players
285 -- begin OLD code before changes for ROLE BASED SECURITY
286 -- delete from a_project_players
287 -- where project_id = x_project_id;
288 -- end OLD code before changes for ROLE BASED SECURITY
289 -- begin NEW code for ROLE BASED SECURITY
290 v_null_number := to_number(NULL);
291 v_null_char := to_char(NULL);
292 v_null_date := to_date(NULL);
293 PA_PROJECT_PARTIES_PUB.DELETE_PROJECT_PARTY(
294 p_api_version => 1.0 -- p_api_version
295 , p_init_msg_list => FND_API.G_FALSE -- p_init_msg_list
296 , p_commit => FND_API.G_FALSE -- p_commit --before it was passed TRUE.
297 , p_validate_only => FND_API.G_FALSE -- p_validate_only
298 , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
299 , p_debug_mode => 'N' -- p_debug_mode
300 , p_record_version_number => v_null_number -- p_record_version_number
301 , p_calling_module => 'FORM' -- p_calling_module
302 , p_project_id => x_project_id -- p_project_id
303 , p_project_party_id => v_null_number -- p_project_party_id
304 , p_scheduled_flag => 'N' -- p_scheduled_flag
305 , x_return_status => x_return_status -- x_return_status
306 , x_msg_count => x_msg_count -- x_msg_count
307 , x_msg_data => x_msg_data -- x_msg_data
308 );
309
310 IF (x_return_status <> 'S') Then
311 x_err_code := 30;
312 x_err_stack := x_err_stack || '->delete_project_party: '|| x_project_id;
313 if l_template_flag = 'Y' then
314 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
315 else
316 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
317 end if;
318 rollback to delete_project;
319 return;
320 END IF;
321
322 -- end NEW code for ROLE BASED SECURITY
323
324 end if;
325
326 -- Delete project classes
327 delete from pa_project_classes
328 where project_id = x_project_id;
329
330 -- Delete project customers
331 delete from pa_project_customers
332 where project_id = x_project_id;
333
334 -- Delete project contacts
335 delete from pa_project_contacts
336 where project_id = x_project_id;
337
338 -- Delete cost distribution overrides
339 delete from pa_cost_dist_overrides
340 where project_id = x_project_id;
341
342 -- Delete credit receivers
343 delete from pa_credit_receivers
344 where project_id = x_project_id;
345
346 -- Delete transaction controls
347 delete from pa_transaction_controls
348 where project_id = x_project_id;
349
350 -- Delete billing assignments
351 delete from pa_billing_assignments
352 where project_id = x_project_id;
353
354 -- anlee
355 -- Commented out for performance bug 2800018
356 -- Delete labor multipliers
357 -- delete from pa_labor_multipliers
358 -- where (project_id = x_project_id
359 -- or task_id in (select task_id from pa_tasks
360 -- where project_id = x_project_id));
361
362 -- added for bug#3693197
363 open get_all_tasks ;
364 fetch get_all_tasks bulk collect into l_tasks_tbl;
365 close get_all_tasks ;
366
367 DELETE FROM PA_LABOR_MULTIPLIERS
368 WHERE PROJECT_ID = x_project_id;
369
370 -- commented for bug#3693197 and replaced with bulk delete
371 -- DELETE FROM PA_LABOR_MULTIPLIERS
372 -- WHERE ( TASK_ID IN (SELECT TASK_ID
373 -- FROM PA_TASKS
374 -- WHERE PROJECT_ID = x_project_id ));
375
376 if nvl(l_tasks_tbl.last,0) > 0 then
377 forall i in l_tasks_tbl.first..l_tasks_tbl.last
378 DELETE FROM PA_LABOR_MULTIPLIERS
379 WHERE task_id = l_tasks_tbl(i);
380 end if ;
381
382 -- anlee
383 -- Commented out for performance bug 2800074
384 -- Delete job bill rate overrides
385 -- delete from pa_job_bill_rate_overrides
386 -- where (project_id = x_project_id
387 -- or task_id in (select task_id from pa_tasks
388 -- where project_id = x_project_id));
389
390 DELETE FROM PA_JOB_BILL_RATE_OVERRIDES
391 WHERE PROJECT_ID = x_project_id ;
392
393 -- commented for bug#3693197 and replaced with bulk delete
394 -- DELETE FROM PA_JOB_BILL_RATE_OVERRIDES
395 -- WHERE ( TASK_ID IN (SELECT TASK_ID
396 -- FROM PA_TASKS
397 -- WHERE PROJECT_ID = x_project_id ));
398
399 if nvl(l_tasks_tbl.last,0) > 0 then
400 forall i in l_tasks_tbl.first..l_tasks_tbl.last
401 DELETE FROM PA_JOB_BILL_RATE_OVERRIDES
402 WHERE task_id = l_tasks_tbl(i);
403 end if ;
404
405 -- anlee
406 -- Commented out for performance
407 -- Delete job bill title overrides
408 -- delete from pa_job_bill_title_overrides
409 -- where (project_id = x_project_id
410 -- or task_id in (select task_id from pa_tasks
411 -- where project_id = x_project_id));
412
413 DELETE FROM pa_job_bill_title_overrides
414 WHERE PROJECT_ID = x_project_id ;
415
416 -- commented for bug#3693197 and replaced with bulk delete
417 -- DELETE FROM pa_job_bill_title_overrides
418 -- WHERE ( TASK_ID IN (SELECT TASK_ID
419 -- FROM PA_TASKS
420 -- WHERE PROJECT_ID = x_project_id ));
421
422 if nvl(l_tasks_tbl.last,0) > 0 then
423 forall i in l_tasks_tbl.first..l_tasks_tbl.last
424 DELETE FROM pa_job_bill_title_overrides
425 WHERE task_id = l_tasks_tbl(i);
426 end if ;
427
428 -- anlee
429 -- Commented out for performance
430 -- Delete job assignment overrides
431 -- delete from pa_job_assignment_overrides
432 -- where (project_id = x_project_id
433 -- or task_id in (select task_id from pa_tasks
434 -- where project_id = x_project_id));
435
436 DELETE FROM pa_job_assignment_overrides
437 WHERE PROJECT_ID = x_project_id ;
438
439 -- commented for bug#3693197 and replaced with bulk delete
440 -- DELETE FROM pa_job_assignment_overrides
441 -- WHERE ( TASK_ID IN (SELECT TASK_ID
442 -- FROM PA_TASKS
443 -- WHERE PROJECT_ID = x_project_id ));
444
445 if nvl(l_tasks_tbl.last,0) > 0 then
446 forall i in l_tasks_tbl.first..l_tasks_tbl.last
447 DELETE FROM pa_job_assignment_overrides
448 WHERE task_id = l_tasks_tbl(i);
449 end if ;
450
451 -- anlee
452 -- Commented out for performance
453 -- Delete emp bill rate overrides
454 -- delete from pa_emp_bill_rate_overrides
455 -- where (project_id = x_project_id
456 -- or task_id in (select task_id from pa_tasks
457 -- where project_id = x_project_id));
458
459 DELETE FROM pa_emp_bill_rate_overrides
460 WHERE PROJECT_ID = x_project_id ;
461
462 -- commented for bug#3693197 and replaced with bulk delete
463 -- DELETE FROM pa_emp_bill_rate_overrides
464 -- WHERE ( TASK_ID IN (SELECT TASK_ID
465 -- FROM PA_TASKS
466 -- WHERE PROJECT_ID = x_project_id ));
467
468 if nvl(l_tasks_tbl.last,0) > 0 then
469 forall i in l_tasks_tbl.first..l_tasks_tbl.last
470 DELETE FROM pa_emp_bill_rate_overrides
471 WHERE task_id = l_tasks_tbl(i);
472 end if ;
473
474 -- anlee
475 -- Commented out for performance bug 2800083
476 -- Delete non-labor bill rate overrides
477 -- delete from pa_nl_bill_rate_overrides
478 -- where (project_id = x_project_id
479 -- or task_id in (select task_id from pa_tasks
480 -- where project_id = x_project_id));
481
482 DELETE FROM PA_NL_BILL_RATE_OVERRIDES
483 WHERE PROJECT_ID = x_project_id ;
484
485 -- commented for bug#3693197 and replaced with bulk delete
486 -- DELETE FROM PA_NL_BILL_RATE_OVERRIDES
487 -- WHERE ( TASK_ID IN (SELECT TASK_ID
488 -- FROM PA_TASKS
489 -- WHERE PROJECT_ID = x_project_id ));
490
491 if nvl(l_tasks_tbl.last,0) > 0 then
492 forall i in l_tasks_tbl.first..l_tasks_tbl.last
493 DELETE FROM PA_NL_BILL_RATE_OVERRIDES
494 WHERE task_id = l_tasks_tbl(i);
495 end if ;
496
497
498 -- Delete compiled multipliers, details of compiled set id
499 delete from pa_compiled_multipliers
500 where ind_compiled_set_id in
501 (select ics.ind_compiled_set_id
502 from pa_ind_compiled_sets ics,
503 pa_ind_rate_sch_revisions rev,
504 pa_ind_rate_schedules sch
505 where ics.ind_rate_sch_revision_id =
506 rev.ind_rate_sch_revision_id
507 and rev.ind_rate_sch_id = sch.ind_rate_sch_id
508 and sch.project_id = x_project_id);
509
510 -- Delete compiled compiled set
511 delete from pa_ind_compiled_sets
512 where ind_rate_sch_revision_id in
513 (select rev.ind_rate_sch_revision_id
514 from pa_ind_rate_sch_revisions rev,
515 pa_ind_rate_schedules sch
516 where rev.ind_rate_sch_id = sch.ind_rate_sch_id
517 and sch.project_id = x_project_id);
518
519 -- Delete ind cost multipliers, details of ind rate sch revisions
520 delete from pa_ind_cost_multipliers
521 where ind_rate_sch_revision_id in
522 (select rev.ind_rate_sch_revision_id
523 from pa_ind_rate_sch_revisions rev, pa_ind_rate_schedules sch
524 where rev.ind_rate_sch_id = sch.ind_rate_sch_id
525 and sch.project_id = x_project_id);
526
527 -- Delete ind rate sch revisions, details of ind rate schedules
528 delete from pa_ind_rate_sch_revisions
529 where ind_rate_sch_id in
530 (select ind_rate_sch_id
531 from pa_ind_rate_schedules
532 where project_id = x_project_id );
533
534 -- Delete ind rate schedules
535 delete from pa_ind_rate_schedules
536 where project_id = x_project_id;
537
538 -- Delete project asset assigments
539 delete from pa_project_asset_assignments
540 where project_id = x_project_id;
541
542 -- Delete project asset
543 delete from pa_project_assets
544 where project_id = x_project_id;
545
546 -- Delete resource list uses, details of resource list assignments
547 delete from pa_resource_list_uses
548 where resource_list_assignment_id in
549 (select resource_list_assignment_id
550 from pa_resource_list_assignments
551 where project_id = x_project_id );
552
553 -- Delete resource list assignments
554 delete from pa_resource_list_assignments
555 where project_id = x_project_id ;
556
557 /* Bug#3480409 : FP.M Changes: Added code for deleting object exceptions, starts here */
558 BEGIN
559 PA_PERF_EXCP_UTILS.delete_object_exceptions
560 ( p_object_type => 'PA_PROJECTS'
561 ,p_object_id => x_project_id
562 ,x_return_status => x_return_status
563 ,x_msg_count => x_msg_count
564 ,x_msg_data => x_msg_data );
565
566 IF (x_return_status <> 'S') Then
567 x_err_code := 35;
568 x_err_stack := x_err_stack || '->delete_object_exception: '|| x_project_id;
569 if l_template_flag = 'Y' then
570 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
571 else
572 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
573 end if;
574 rollback to delete_project;
575 return;
576 END IF;
577 EXCEPTION WHEN OTHERS THEN
578 x_err_code := 35;
579 x_err_stage := 'delete_object_exception: '||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
580 rollback to delete_project;
581 return;
582 END;
583 /* Bug#3480409 : FP.M Changes: Added code for copying Perf/Score rules, ends here */
584
585
586 --Ansari
587 -- Delete opportunity value
588 PA_OPPORTUNITY_MGT_PVT.delete_project_attributes
589 ( p_project_id => x_project_id,
590 x_return_status => x_return_status,
591 x_msg_count => x_msg_count,
592 x_msg_data => x_msg_data );
593
594 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
595 x_err_code := 40;
596 x_err_stack := x_err_stack || '->delete_project_attributes: '|| x_project_id;
597 if l_template_flag = 'Y' then
598 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
599 else
600 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
601 end if;
602 rollback to delete_project;
603 return;
604 END IF;
605 --Ansari
606
607 --Retention Changes --Ansari bug 2362168
608 PA_RETENTION_UTIL.delete_retention_rules(
609 p_project_id => x_project_id,
610 p_task_id => null,
611 x_return_status => x_return_status,
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data );
614
615 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
616 x_err_code := 50;
617 x_err_stack := x_err_stack || '->delete_retention_rules: '|| x_project_id;
618 if l_template_flag = 'Y' then
619 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
620 else
621 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
622 end if;
623 rollback to delete_project;
624 return;
625 END IF;
626
627 --Retention Changes --Ansari
628
629 -- Delete percent complete .We shall be deleting all rows for the
630 -- project including the task rows.
631
632 delete from pa_percent_completes
633 where project_id = x_project_id;
634
635 --Bug 3617393
636 --Moved the delete_task code before delete_project_structure as PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP
637 --call in delete_task requires data in pa_proj_elements and pa_proj_element_versions
638 -- Delete task
639
640 temp_stack := x_err_stack;
641
642 for task_rec in (select t.task_id
643 from pa_tasks t
644 where t.project_id = x_project_id
645 and t.task_id = t.top_task_id) loop
646
647 x_err_stack := NULL;
648
649 pa_project_core.delete_task(
650 x_task_id => task_rec.task_id,
651 x_validation_mode => x_validation_mode, --bug 2947492
652 x_err_code => x_err_code,
653 x_err_stage => x_err_stage,
654 x_err_stack => x_err_stack);
655
656
657 if (x_err_code <> 0) then
658 --Added for bug 3617393
659 rollback to delete_project;
660 --End bug 3617393
661 return;
662 end if;
663 end loop;
664
665 x_err_stack := temp_stack;
666 --End bug 3617393 move
667 --Ansari integration of project structtures with Forms, Slef Service and AMG
668 PA_PROJ_TASK_STRUC_PUB.delete_project_structure(
669 p_calling_module => 'FORMS'
670 ,p_project_id => x_project_id
671 ,x_msg_count => x_msg_count
672 ,x_msg_data => x_msg_data
673 ,x_return_status => x_return_status );
674
675 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
676 x_err_code := 60;
677 x_err_stack := x_err_stack || '->delete_project_structure: '|| x_project_id;
678 if l_template_flag = 'Y' then
679 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
680 else
681 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
682 end if;
683 rollback to delete_project;
684 return;
685 END IF;
686 --Ansari integration of project structtures with Forms, Slef Service and AMG
687
688 --PA K Build 3 Changes maansari
689
690 PA_CONTROL_ITEMS_PVT.DELETE_ALL_CONTROL_ITEMS(
691 p_project_id => x_project_id
692 ,x_msg_count => x_msg_count
693 ,x_msg_data => x_msg_data
694 ,x_return_status => x_return_status );
695
696 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
697 x_err_code := 70;
698 x_err_stack := x_err_stack || '->delete_all_control_items: '|| x_project_id;
699 if l_template_flag = 'Y' then
700 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
701 else
702 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
703 end if;
704 rollback to delete_project;
705 return;
706 END IF;
707 --PA K Build 3 Changes maansari
708
709 --Bug 3617393 : Moved delete_task code up from here
710
711 -- hsiu added.
712 -- 30 Mar 2001. For Project Contracts.
713 -- Delete project structure relationship
714 PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP(
715 p_api_version => 1.0,
716 p_init_msg_list => FND_API.G_FALSE,
717 p_commit => x_commit,
718 p_validate_only => FND_API.G_FALSE,
719 p_debug_mode => 'N',
720 p_task_id => null,
721 p_project_id => x_project_id,
722 x_return_status => x_return_status,
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data
725 );
726 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
727 x_err_code := 80;
728 x_err_stack := x_err_stack || '->delete_relationship: '|| x_project_id;
729 if l_template_flag = 'Y' then
730 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
731 else
732 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
733 end if;
734 rollback to delete_project;
735 return;
736 END IF;
737
738 -- mrajput added.
739 -- 18 Nov 2002. For Product Lifecycle Management.
740 -- Delete Item Associations
741
742 PA_EGO_WRAPPER_PUB.delete_all_item_assocs(
743 p_api_version => 1.0 ,
744 p_project_id => x_project_id ,
745 p_init_msg_list => NULL ,
746 p_commit => x_commit ,
747 x_errorcode => x_err_code ,
748 x_return_status => x_return_status ,
749 x_msg_count => x_msg_count ,
750 x_msg_data => x_msg_data );
751
752 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
753 x_err_code := 90;
754 x_err_stack := x_err_stack || '->delete_all_item_assocs: '|| x_project_id;
755 if l_template_flag = 'Y' then
756 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
757 else
758 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
759 end if;
760 rollback to delete_project;
761 return;
762 END IF;
763
764
765 -- anlee
766 -- Added for intermedia search
767
768 PA_PROJECT_CTX_SEARCH_PVT.DELETE_ROW (
769 p_project_id => x_project_id
770 ,p_template_flag => l_template_flag
771 ,x_return_status => x_return_status );
772
773 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
774 x_err_code := 100;
775 x_err_stack := x_err_stack || '->pa_project_ctx_search_pvt.delete_row: '|| x_project_id;
776 if l_template_flag = 'Y' then
777 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
778 else
779 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
780 end if;
781 rollback to delete_project;
782 return;
783 END IF;
784 -- anlee end of changes
785
786 -- anlee
787 -- Ext Attribute changes
788 -- Bug 2904327
789
790 PA_USER_ATTR_PUB.DELETE_ALL_USER_ATTRS_DATA (
791 p_validate_only => FND_API.G_FALSE
792 ,p_project_id => x_project_id
793 ,x_return_status => x_return_status
794 ,x_msg_count => x_msg_count
795 ,x_msg_data => x_msg_data );
796
797 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
798 x_err_code := 105;
799 x_err_stack := x_err_stack || '->delete_all_user_attrs_data: '|| x_project_id;
800 if l_template_flag = 'Y' then
801 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
802 else
803 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
804 end if;
805 rollback to delete_project;
806 return;
807 END IF;
808 -- anlee end of changes
809
810
811 /* bug 2723705 */
812 PA_PROJECT_SETS_PVT.delete_proj_from_proj_set(
813 p_project_id => x_project_id
814 ,x_return_status => x_return_status);
815
816 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
817 x_err_code := 110;
818 x_err_stack := x_err_stack || '->delete_proj_from_proj_set: '|| x_project_id;
819 if l_template_flag = 'Y' then
820 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
821 else
822 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
823 end if;
824 rollback to delete_project;
825 return;
826 END IF;
827
828 /* bug 2723705 end of changes */
829
830 --bug 3055766
831 PA_TASK_PUB1.Delete_Proj_To_Task_Assoc(
832 p_project_id => x_project_id
833 ,x_return_status => x_return_status
834 ,x_msg_count => x_msg_count
835 ,x_msg_data => x_msg_data );
836
837 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
838 x_err_code := 115;
839 x_err_stack := x_err_stack || '->Delete_Proj_To_Task_Assoc: '|| x_project_id;
840 if l_template_flag = 'Y' then
841 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
842 else
843 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
844 end if;
845 rollback to delete_project;
846 return;
847 END IF;
848 --End bug 3055766
849
850 -- Bug#3491609
851 -- Earlier the call to DELETE_DELIVERABLE_STRUCTURE was placed
852 -- after aborting the WF process. Placed this call before aborting
853 -- WF.
854
855 -- Changes added by skannoji
856 -- Added code for doosan customer
857 IF ( PA_PROJECT_STRUCTURE_UTILS.check_Deliverable_enabled(x_project_id) = 'Y' ) THEN
858 PA_DELIVERABLE_PUB.delete_deliverable_structure
859 (p_project_id => x_project_id
860 ,x_return_status => x_return_status
861 ,x_msg_count => x_msg_count
862 ,x_Msg_data => x_msg_data
863 );
864 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
865 RAISE FND_API.G_EXC_ERROR;
866 END IF;
867 END IF;
868 -- till here by skannoji
869
870 --Bug 3613601
871 Pa_Rbs_Utils.Delete_Proj_Specific_RBS( p_project_id => x_project_id
872 ,x_return_status => x_return_status
873 ,x_msg_count => x_msg_count
874 );
875 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
876 x_err_code := 120;
877 x_err_stack := x_err_stack || '->Delete_Proj_Specific_RBS: '|| x_project_id;
878 IF l_template_flag = 'Y' THEN
879 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
880 ELSE
881 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
882 END IF;
883 ROLLBACK TO delete_project;
884 RETURN;
885 END IF;
886 --Bug 3613601
887
888 --Bug 3594162
889 Pa_Planning_Resource_Utils.Delete_Proj_Specific_Resource( p_project_id => x_project_id
890 ,x_return_status => x_return_status
891 ,x_msg_count => x_msg_count
892 );
893 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
894 x_err_code := 125;
895 x_err_stack := x_err_stack || '->Delete_Proj_Specific_Resource: '|| x_project_id;
896 IF l_template_flag = 'Y' THEN
897 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
898 ELSE
899 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
900 END IF;
901 ROLLBACK TO delete_project;
902 RETURN;
903 END IF;
904 --Bug 3594162pl
905 /* --SMukka Added this plsql block of code
906 BEGIN
907 PA_PERF_EXCP_UTILS.delete_object_exceptions
908 (
909 p_object_type =>'PA_PROJECTS'
910 ,p_object_id =>x_project_id
911 ,x_msg_count =>x_msg_count
912 ,x_msg_data =>x_msg_data
913 ,x_return_status =>x_return_status
914 );
915 IF x_return_status <> 'S' THEN
916 x_err_code := 905;
917 x_err_stack := x_err_stack||'->PA_PERF_EXCP_UTILS.delete_object_exceptions';
918 ROLLBACK TO copy_project;
919 RETURN;
920 END IF;
921 EXCEPTION
922 WHEN OTHERS THEN
923 x_err_code := SQLCODE;
924 x_err_stage := 'PA_PERF_EXCP_UTILS.delete_object_exceptions: '||SUBSTRB(SQLERRM,1,240);
925 ROLLBACK TO copy_project;
926 END;*/
927
928 /****Note added by sdebroy : 12-MAR-2004
929 FP M : 3491609 : FP-M : TRACKING BUG FOR AMG CHANGES FOR DELIVERABLES
930
931 Note :
932 No changes is required for cancelling Project Execution Workflow
933 and Task Execution Workflow which are introduced as part of
934 pathset M . For Project Execution Workflow and Task Execution Workflow
935 we'll maintian records in PA_WF_PROCESS table with entity_key1 = project_id
936 ,hence the existing code should work.
937
938 Note added by sdebroy : 12-MAR-2004 ****/
939
940 -- Start of changes for Bug 2898598
941
942 delete from pa_wf_processes where
943 entity_key1 = TO_CHAR(x_project_id)
944 and item_type <> 'PABUDWF'
945 and (item_key,item_type) not in
946 (select item_key,item_type from wf_items);/*Bug 9276888 Delete those records from
947 pa_wf_processes for which WF Purge has been run. */
948
949 FOR i IN ( SELECT item_type,item_key
950 FROM pa_wf_processes
951 WHERE entity_key1 = TO_CHAR(x_project_id)
952 and item_type <> 'PABUDWF') --Bug 9040747
953
954 LOOP
955
956 wf_engine.itemstatus ( itemtype => i.item_type,
957 itemkey => i.item_key,
958 status => x_status,
959 result => x_result );
960 IF x_status = 'ACTIVE' THEN
961
962 wf_engine.abortprocess ( itemtype => i.item_type,
963 itemkey => i.item_key );
964 END IF;
965
966 wf_purge.total( itemtype => i.item_type,
967 itemkey => i.item_key );
968
969 END LOOP;
970
971 DELETE FROM pa_wf_processes
972 WHERE entity_key1 = TO_CHAR(x_project_id)
973 and item_type <> 'PABUDWF'; --Bug 9040747;
974
975 -- End of changes for bug 2898598
976
977 --Bug 3610949 : Delete budget versions
978 IF ( l_wp_enabled = 'Y' ) THEN
979 PA_FIN_PLAN_PUB.Delete_Version( p_project_id => x_project_id
980 ,p_budget_version_id => null
981 ,p_record_version_number => null
982 ,p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN
983 ,x_return_status => x_return_status
984 ,x_msg_count => x_msg_count
985 ,x_msg_data => x_msg_data
986 );
987 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
988 x_err_code := 130;
989 x_err_stack := x_err_stack || '->Delete_Version: '|| x_project_id;
990 IF l_template_flag = 'Y' THEN
991 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
992 ELSE
993 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
994 END IF;
995 ROLLBACK TO delete_project;
996 RETURN;
997 END IF;
998 END IF;
999 --End : Bug 3610949
1000
1001 --Bug 3610949 : The following code used to be in KEY-DELREC trigger of project_folder block.
1002 --Moved the call here, after delete_version api call
1003 pa_fin_plan_utils.Delete_Fp_Options( p_project_id => x_project_id,
1004 x_err_code => x_err_code );
1005 if x_err_code <> 0 Then
1006 x_err_code := 140;
1007 x_err_stack := x_err_stack ||'->Delete_Fp_Options: '|| x_project_id;
1008 IF l_template_flag = 'Y' THEN
1009 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
1010 ELSE
1011 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
1012 END IF;
1013 ROLLBACK TO delete_project;
1014 RETURN;
1015 end if;
1016 -- Start of Bug 4705154
1017 -- Bug No 4705154:The Following API call is being added to delete entries of project from PA_OBJ_STATUS_CHANGES Table.
1018 PA_CONTROL_ITEMS_UTILS.DELETE_OBJ_STATUS_CHANGES
1019 (
1020 p_object_type =>'PA_PROJECTS'
1021 ,p_object_id => x_project_id
1022 ,x_msg_count =>x_msg_count
1023 ,x_msg_data =>x_msg_data
1024 ,x_return_status =>x_return_status);
1025 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1026 x_err_code := 150;
1027 x_err_stack := x_err_stack || '->Delete_Obj_Status_Changes: '|| x_project_id;
1028 IF l_template_flag = 'Y' THEN
1029 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
1030 ELSE
1031 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
1032 END IF;
1033 ROLLBACK TO delete_project;
1034 return;
1035 END IF;
1036 -- End of Bug no 4705154
1037 -- Delete project
1038 delete pa_projects
1039 where project_id = x_project_id;
1040
1041 x_err_stack := old_stack;
1042
1043 --Bug 3617393
1044 x_err_code := 0;
1045 if x_commit = FND_API.G_TRUE then
1046 commit;
1047 end if;
1048
1049 exception
1050 when others then
1051 x_err_code := SQLCODE;
1052 x_err_stage := 'DELETE PROJECT: '||SUBSTR( SQLERRM,1,1900);
1053 rollback to delete_project;
1054 return;
1055 end delete_project;
1056
1057
1058 --
1059 -- PROCEDURE
1060 -- import_task
1061 -- PURPOSE
1062 -- This objective of this API is to import tasks into
1063 -- PA system. This API can be called by task import system
1064 -- and other external systems. Other task related information
1065 -- can be entered by using Enter Project form or calling table
1066 -- handlers.
1067 --
1068 --
1069 -- HISTORY
1070 -- 24-OCT-95 R. Chiu Created
1071 --
1072 procedure import_task ( x_project_id IN number
1073 , x_task_name IN varchar2
1074 , x_task_number IN varchar2
1075 , x_service_type_code IN varchar2
1076 , x_organization_id IN number
1077 , x_description IN varchar2
1078 , x_task_start_date IN date
1079 , x_task_end_date IN date
1080 , x_parent_task_id IN number
1081 , x_pm_project_id IN number
1082 , x_pm_task_id IN number
1083 , x_manager_id IN number
1084 , x_new_task_id OUT NOCOPY number --File.Sql.39 bug 4440895
1085 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1086 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1087 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1088 is
1089
1090 x_rowid varchar2(18);
1091 x_wbs_level number;
1092 x_task_id number;
1093 x_top_task_id number;
1094 x_address_id number;
1095 x_org_id number;
1096 x_labor_bill_rate_org_id number;
1097 x_labor_std_bill_rate_schd varchar2(20);
1098 x_proj_start_date date;
1099 x_proj_end_date date;
1100 x_labor_schedule_discount number;
1101 x_labor_schedule_fixed_date date;
1102 x_nl_bill_rate_org_id number;
1103 x_nl_std_bill_rate_schd varchar2(30);
1104 x_nl_schedule_discount number;
1105 x_nl_schedule_fixed_date date;
1106 x_labor_sch_type varchar2(1);
1107 x_non_labor_sch_type varchar2(1);
1108 x_cost_ind_rate_sch_id number;
1109 x_cost_ind_sch_fixed_date date;
1110 x_rev_ind_rate_sch_id number;
1111 x_rev_ind_sch_fixed_date date;
1112 x_inv_ind_rate_sch_id number;
1113 x_inv_ind_sch_fixed_date date;
1114 x_serv_type_code varchar2(30);
1115 x_project_type_class_code varchar2(30);
1116 x_billable_flag varchar2(1);
1117 status_code number;
1118 old_stack varchar2(630);
1119 x_adj_on_std_inv varchar2(2); /* Added for 12.2 Payroll billing ER 11847616 */
1120 begin
1121
1122 Savepoint import_task;
1123 -- Check project id
1124 if (x_project_id is null ) then
1125 x_err_code := 10;
1126 x_err_stage := 'PA_NO_PROJ_ID';
1127 return;
1128 end if ;
1129
1130 -- Check task name
1131 if (x_task_name is null ) then
1132 x_err_code := 20;
1133 x_err_stage := 'PA_NO_TASK_NAME';
1134 return;
1135 end if ;
1136
1137 -- Check task number
1138 if (x_task_number is null ) then
1139 x_err_code := 30;
1140 x_err_stage := 'PA_NO_TASK_NUMBER';
1141 return;
1142 end if ;
1143
1144 -- Uniqueness check for task number
1145 x_err_stage := 'check uniqueness for task number '|| x_task_number;
1146 status_code :=
1147 pa_task_utils.check_unique_task_number(x_project_id,
1148 x_task_number,
1149 null);
1150 if ( status_code = 0 ) then
1151 x_err_code := 40;
1152 x_err_stage := 'PA_TASK_NUM_NOT_UNIQUE';
1153 return;
1154 elsif ( status_code < 0 ) then
1155 x_err_code := status_code;
1156 return;
1157 end if;
1158
1159 if (x_parent_task_id is null) then -- creating top task
1160 x_wbs_level := 1;
1161 x_top_task_id := NULL;
1162 else -- creating subtask
1163
1164 -- check create subtask ok
1165 x_err_stage :=
1166 'check create subtask for parent task '|| x_parent_task_id;
1167
1168 --Bug 2947492 The following call is modified to pass params by notation.
1169 pa_task_utils.check_create_subtask_ok(x_task_id => x_parent_task_id,
1170 x_err_code => x_err_code,
1171 x_err_stage => x_err_stage,
1172 x_err_stack => x_err_stack);
1173
1174 -- if application error or oracle error then discontinue.
1175 if ( x_err_code <> 0 ) then
1176 return;
1177 end if;
1178
1179 -- get wbs level for parent task id
1180 x_err_stage :=
1181 'get wbs level for parent task '|| x_parent_task_id;
1182 x_wbs_level := pa_task_utils.get_wbs_level(x_parent_task_id);
1183 if (x_wbs_level is null) then
1184 x_err_code := 50;
1185 x_err_stage := 'PA_WBS_LEVEL_NOT_FOUND';
1186 return;
1187 elsif ( x_wbs_level < 0 ) then -- Oracle error
1188 x_err_code := x_wbs_level;
1189 return;
1190 end if;
1191 x_wbs_level := x_wbs_level + 1;
1192 -- increase level by 1 for child task
1193
1194 -- get top task id for parent task id
1195 x_err_stage :=
1196 'get top task id for parent task '|| x_parent_task_id;
1197 x_top_task_id := pa_task_utils.get_top_task_id(x_parent_task_id);
1198 if (x_top_task_id is null) then
1199 x_err_code := 60;
1200 x_err_stage := 'PA_TASK_ID_NOT_FOUND';
1201 return;
1202 elsif ( x_top_task_id < 0 ) then -- Oracle error
1203 x_err_code := x_top_task_id;
1204 return;
1205 end if;
1206 end if;
1207
1208 -- Get default task information.
1209
1210 x_err_stage := 'get default task information ';
1211
1212 declare
1213 cursor c1 is
1214 SELECT
1215 P.start_date,
1216 P.completion_date,
1217 P.carrying_out_organization_id,
1218 P.labor_bill_rate_org_id,
1219 P.labor_std_bill_rate_schdl,
1220 P.labor_schedule_discount,
1221 P.labor_schedule_fixed_date,
1222 P.non_labor_bill_rate_org_id,
1223 P.non_labor_std_bill_rate_schdl,
1224 P.non_labor_schedule_discount,
1225 P.non_labor_schedule_fixed_date,
1226 P.labor_sch_type,
1227 P.non_labor_sch_type,
1228 P.cost_ind_rate_sch_id,
1229 P.cost_ind_sch_fixed_date,
1230 P.rev_ind_rate_sch_id,
1231 P.rev_ind_sch_fixed_date,
1232 P.inv_ind_rate_sch_id,
1233 P.inv_ind_sch_fixed_date,
1234 PT.service_type_code,
1235 PT.project_type_class_code ,
1236 p.adj_on_std_inv /* Added for 12.2 Payroll billing ER 11847616 */
1237 FROM pa_projects P, pa_project_types PT
1238 WHERE P.project_id = x_project_id
1239 AND P.project_type = PT.project_type;
1240
1241 begin
1242 open c1;
1243 fetch c1 into
1244 x_proj_start_date,
1245 x_proj_end_date,
1246 x_org_id,
1247 x_labor_bill_rate_org_id,
1248 x_labor_std_bill_rate_schd,
1249 x_labor_schedule_discount,
1250 x_labor_schedule_fixed_date,
1251 x_nl_bill_rate_org_id,
1252 x_nl_std_bill_rate_schd,
1253 x_nl_schedule_discount,
1254 x_nl_schedule_fixed_date,
1255 x_labor_sch_type,
1256 x_non_labor_sch_type,
1257 x_cost_ind_rate_sch_id,
1258 x_cost_ind_sch_fixed_date,
1259 x_rev_ind_rate_sch_id,
1260 x_rev_ind_sch_fixed_date,
1261 x_inv_ind_rate_sch_id,
1262 x_inv_ind_sch_fixed_date,
1263 x_serv_type_code,
1264 x_project_type_class_code,
1265 x_adj_on_std_inv /* Added for 12.2 Payroll billing ER 11847616 */
1266 ;
1267
1268 if c1%notfound then
1269 close c1;
1270 x_err_code := 70;
1271 x_err_stage := 'PA_NO_ROW_FOUND';
1272 return;
1273 end if;
1274 close c1;
1275
1276 exception
1277 when others then
1278 close c1;
1279 x_err_code := SQLCODE;
1280 return;
1281 end ;
1282
1283 -- Get default address id
1284 begin
1285 x_err_stage := 'get default address id ';
1286
1287 -- 4363092 TCA changes, replaced RA views with HZ tables
1288 /*
1289 SELECT
1290 DISTINCT A.address_id
1291 INTO x_address_id
1292 FROM ra_addresses A, ra_site_uses SU
1293 WHERE A.address_id = SU.address_id
1294 AND A.customer_id IN
1295 (SELECT customer_id
1296 FROM pa_project_customers
1297 WHERE project_id = x_project_id)
1298 AND NVL(SU.STATUS,'A') = 'A'
1299 AND SU.site_use_code = 'SHIP_TO';
1300 */
1301
1302 SELECT
1303 DISTINCT acct_site.cust_acct_site_id
1304 INTO x_address_id
1305 FROM
1306 hz_cust_acct_sites_all acct_site,
1307 hz_cust_site_uses su
1308 WHERE
1309 acct_site.cust_acct_site_id = su.cust_acct_site_id
1310 AND acct_site.cust_account_id IN
1311 (SELECT customer_id FROM pa_project_customers WHERE project_id = x_project_id)
1312 AND NVL(SU.STATUS,'A') = 'A'
1313 AND SU.site_use_code = 'SHIP_TO';
1314
1315 -- 4363092 end
1316
1317 exception
1318 when NO_DATA_FOUND then
1319 x_address_id := NULL;
1320 when TOO_MANY_ROWS then
1321 x_address_id := NULL;
1322 when others then
1323 x_err_code := SQLCODE;
1324 return;
1325 end ;
1326
1327
1328 -- verify date range
1329 if (x_task_start_date is not null AND x_task_end_date is not null
1330 AND x_task_start_date > x_task_end_date ) then
1331 -- invaid task date range
1332 x_err_code := 80;
1333 x_err_stage := 'PA_SU_INVALID_DATES';
1334 -- existing message name from PAXTKETK
1335 return;
1336 else
1337 if ( (x_task_start_date is null OR
1338 x_proj_start_date is null OR
1339 x_task_start_date >= x_proj_start_date)
1340 AND (x_task_end_date is null OR
1341 x_proj_end_date is null OR
1342 x_task_end_date <= x_proj_end_date) ) then
1343 null; -- task dates are within project dates range
1344 else
1345 x_err_code := 90;
1346 x_err_stage := 'PA_TK_OUTSIDE_PROJECT_RANGE';
1347 -- existing message name from PAXTKETK
1348 return;
1349 end if;
1350 end if;
1351
1352 -- set task billable flag
1353 if (x_project_type_class_code = 'INDIRECT') then
1354 x_billable_flag := 'N';
1355 else
1356 x_billable_flag := 'Y';
1357 end if;
1358
1359
1360 -- Update parent task chargeable flag to 'N'.
1361 -- Only lowest tasks are chargeable.
1362
1363 if (x_parent_task_id is not null) then
1364 x_err_stage := 'update parent task chargeable flag';
1365
1366 update pa_tasks
1367 set chargeable_flag = 'N'
1368 where task_id = x_parent_task_id;
1369 end if;
1370
1371 -- call table handler to insert task
1372 begin
1373 x_err_stage := 'Insert task for project '|| x_project_id;
1374
1375 pa_tasks_pkg.insert_row(
1376 x_rowid,
1377 x_task_id,
1378 x_project_id,
1379 x_task_number,
1380 sysdate,
1381 FND_GLOBAL.USER_ID,
1382 sysdate,
1383 FND_GLOBAL.USER_ID,
1384 FND_GLOBAL.LOGIN_ID,
1385 x_task_name,
1386 x_task_name, -- long name
1387 x_top_task_id,
1388 x_wbs_level,
1389 'N',
1390 'N',
1391 x_parent_task_id,
1392 x_description,
1393 nvl(x_organization_id, x_org_id),
1394 nvl(x_service_type_code, x_serv_type_code),
1395 x_manager_id,
1396 'Y',
1397 x_billable_flag,
1398 'N',
1399 nvl(x_task_start_date, x_proj_start_date),
1400 nvl(x_task_end_date, x_proj_end_date),
1401 x_address_id,
1402 X_Labor_Bill_Rate_Org_Id,
1403 X_Labor_Std_Bill_Rate_Schd,
1404 X_Labor_Schedule_Fixed_Date,
1405 X_Labor_Schedule_Discount,
1406 X_NL_Bill_Rate_Org_Id,
1407 X_NL_Std_Bill_Rate_Schd,
1408 X_NL_Schedule_Fixed_Date,
1409 X_NL_Schedule_Discount,
1410 null,
1411 null,
1412 null,
1413 null,
1414 null,
1415 null,
1416 null,
1417 null,
1418 null,
1419 null,
1420 null,
1421 null,
1422 X_Cost_Ind_Rate_Sch_Id,
1423 X_Rev_Ind_Rate_Sch_Id,
1424 X_Inv_Ind_Rate_Sch_Id,
1425 X_Cost_Ind_Sch_Fixed_Date,
1426 X_Rev_Ind_Sch_Fixed_Date,
1427 X_Inv_Ind_Sch_Fixed_Date,
1428 X_Labor_Sch_Type,
1429 X_Non_Labor_Sch_Type,
1430 NULL,
1431 NULL,
1432 NULL,
1433 NULL,
1434 NULL,
1435 NULL,
1436 NULL,
1437 NULL,
1438 NULL,
1439 NULL,
1440 NULL,
1441 -- 01-APR-2001
1442 -- hsiu Added for forecasting changes
1443 NULL,
1444 NULL,
1445 --MCA Sakthi for MultiAgreementCurreny Project
1446 NULL,
1447 NULL,
1448 NULL,
1449 --MCA Sakthi for MultiAgreementCurreny Project
1450 NULL,
1451 NULL,
1452 --PA L Changes 2872708
1453 'N',
1454 'Y',
1455 null,
1456
1457 --End PA L Changes 2872708
1458 /*FPM Dev -Project setup changes */
1459 null,
1460 null,
1461 null ,
1462 null,
1463 x_adj_on_std_inv /* Added for 12.2 Payroll billing ER 11847616 */
1464 );
1465 exception
1466 when NO_DATA_FOUND then
1467 x_err_code := 100;
1468 x_err_stage := 'PA_NO_ROW_INSERTED';
1469 rollback to import_task;
1470 return;
1471 when others then
1472 x_err_code := SQLCODE;
1473 rollback to import_task;
1474 return;
1475 end;
1476
1477 x_new_task_id := x_task_id;
1478 x_err_stack := old_stack;
1479
1480 exception
1481 when others then
1482 x_err_code := SQLCODE;
1483 rollback to import_task;
1484 return;
1485 end import_task;
1486
1487 --
1488 -- PROCEDURE
1489 -- delete_task
1490 -- PURPOSE
1491 -- This objective of this API is to delete tasks from
1492 -- the PA system. All task detail information along
1493 -- with the specified task will be deleted if there's
1494 -- no transaction charged to the task. This API can
1495 -- be used by Enter Project form and other external systems.
1496 --
1497 -- To delete a top task and its subtasks, the following
1498 -- requirements must be met:
1499 -- * No event at top level task
1500 -- * No funding at top level tasks
1501 -- * No baseline budget at top level task
1502 -- * Meet the following requirements for its children
1503 --
1504 -- To delete a mid level task, it involves checking its
1505 -- children and meeting the following requirements for
1506 -- its lowest level task.
1507 --
1508 -- To delete a lowest level task, the following requirements
1509 -- must be met:
1510 -- * No expenditure item at lowest level task
1511 -- * No puchase order line at lowest level task
1512 -- * No requisition line at lowest level task
1513 -- * No supplier invoice (ap invoice) at lowest level task
1514 -- * No baseline budget at lowest level task
1515 --
1516 -- HISTORY
1517 -- 25-OCT-95 R. Chiu Created
1518 --
1519 procedure delete_task ( x_task_id IN number
1520 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
1521 , x_validate_flag IN varchar2 DEFAULT 'Y' -- Adding paramater x_validate_flag
1522 , x_bulk_flag IN VARCHAR2 DEFAULT 'N' -- 4201927
1523 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1524 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1525 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1526 is
1527
1528 old_stack varchar2(630);
1529 status_code number;
1530 x_parent_task_id number;
1531 l_project_id number;
1532 x_return_status varchar2(1);
1533 x_msg_count number;
1534 x_msg_data varchar2(2000);
1535 l_cc_tax_task_id number; ---- Bug 6629057
1536
1537 cursor l_project_csr is
1538 select project_id from
1539 pa_tasks where
1540 task_id = x_task_id;
1541
1542 --Added for bug 3617393
1543 CURSOR get_template_flag(c_project_id IN NUMBER) IS
1544 SELECT template_flag
1545 FROM pa_projects_all
1546 WHERE project_id = c_project_id;
1547
1548 cursor cc_task_id_csr(x_taskid number) ---- Bug 6629057
1549 is select cc_tax_task_id from pa_projects_all pj
1550 where pj.project_id = l_project_id;
1551
1552 l_template_flag VARCHAR2(1) := 'N';
1553 --Added for bug 3617393
1554
1555 begin
1556
1557 SAVEPOINT delete_task;
1558
1559 old_stack := x_err_stack; -- Fix for Bug # 4513291. It should initialize old_stack before appending values
1560 x_err_stack := x_err_stack || '->delete_task';
1561
1562 x_err_code := 0;
1563
1564 x_err_stage := 'Fetching project id for task '|| x_task_id;
1565
1566 Open l_project_csr;
1567 Fetch l_project_csr into l_project_id;
1568 If l_project_csr%NOTFOUND THEN
1569 close l_project_csr;
1570 RAISE NO_DATA_FOUND;
1571 Else
1572 close l_project_csr;
1573 End if;
1574
1575 --Added for bug 3617393
1576 OPEN get_template_flag ( l_project_id );
1577 FETCH get_template_flag INTO l_template_flag;
1578 CLOSE get_template_flag;
1579 --Added for bug 3617393
1580
1581 -- Fix for Bug # 4513291. Moved this up before appending x_err_stack -- old_stack := x_err_stack;
1582
1583 -- 4201927
1584 IF x_bulk_flag = 'N' THEN
1585 pa_task_utils.check_delete_task_ok(
1586 x_task_id => x_task_id,
1587 x_validation_mode => x_validation_mode, -- bug 2947492
1588 x_err_code => x_err_code,
1589 x_err_stage => x_err_stage,
1590 x_err_stack => x_err_stack);
1591
1592 if (x_err_code <> 0) then
1593 --Added for bug 3617393
1594 rollback to delete_task;
1595 --End bug 3617393
1596 return;
1597 end if;
1598 END IF;
1599
1600 ---- start 6629057
1601 Open cc_task_id_csr(x_task_id);
1602 fetch cc_task_id_csr into l_cc_tax_task_id;
1603 if cc_task_id_csr%notfound then
1604 close cc_task_id_csr;
1605 end if;
1606 if(l_cc_tax_task_id = x_task_id) then
1607 update pa_projects_all
1608 set cc_tax_task_id = null
1609 where project_id = l_project_id;
1610 close cc_task_id_csr;
1611 end if;
1612 ---- end 6629057
1613
1614 -- 4201927 end
1615
1616 delete from pa_billing_assignments
1617 where top_task_id = x_task_id
1618 AND project_id = l_project_id;
1619
1620 -- 3693197
1621 -- Commented the whole code and moved the code
1622 -- inside the anonymous block and used bulk approach.
1623 -- In all the below mentioned deletes following select
1624 -- query :
1625 -- SELECT TASK_ID
1626 -- FROM PA_TASKS
1627 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1628 -- START WITH TASK_ID = x_TASK_ID
1629 -- was getting called repeatedly. Instead used the existing
1630 -- cursor task_cur to fetch all the tasks into PLSQL table
1631 -- and used bulk delete.
1632
1633 -- Delete transaction controls
1634 --x_err_stage := 'Delete txn controls for task '|| x_task_id;
1635 --delete from pa_transaction_controls
1636 --where task_id in
1637 --(SELECT TASK_ID
1638 -- FROM PA_TASKS
1639 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1640 -- START WITH TASK_ID = x_TASK_ID)
1641 -- Added to fix Bug # 1190003
1642 --AND project_id = l_project_id;
1643
1644 -- Delete billing assignments
1645 --x_err_stage := 'Delete billing assignmts for task '|| x_task_id;
1646 -- Delete labor multipliers
1647 -- x_err_stage := 'Delete labor multipliers for task '|| x_task_id;
1648 -- delete from pa_labor_multipliers
1649 -- where task_id in
1650 -- (select task_id
1651 -- from pa_tasks
1652 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1653 -- START WITH TASK_ID = x_TASK_ID);
1654 --
1655 -- -- Delete job bill rate overrides
1656 -- x_err_stage := 'Delete job bill rate overrides for task '|| x_task_id;
1657 -- delete from pa_job_bill_rate_overrides
1658 -- where task_id in
1659 -- (select task_id
1660 -- from pa_tasks
1661 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1662 -- START WITH TASK_ID = x_TASK_ID);
1663 --
1664 -- -- Delete job bill title overrides
1665 -- x_err_stage := 'Delete job bill title overrides for task '|| x_task_id;
1666 -- delete from pa_job_bill_rate_overrides
1667 -- where task_id in
1668 -- (select task_id
1669 -- from pa_tasks
1670 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1671 -- START WITH TASK_ID = x_TASK_ID);
1672 --
1673 -- -- Delete job assignment overrides
1674 -- x_err_stage := 'Delete job assignmt overrides for task '|| x_task_id;
1675 -- delete from pa_job_assignment_overrides
1676 -- where task_id in
1677 -- (select task_id
1678 -- from pa_tasks
1679 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1680 -- START WITH TASK_ID = x_TASK_ID);
1681 --
1682 -- -- Delete emp bill rate overrides
1683 -- x_err_stage := 'Delete emp bill rate overrides for task '|| x_task_id;
1684 -- delete from pa_emp_bill_rate_overrides
1685 -- where task_id in
1686 -- (select task_id
1687 -- from pa_tasks
1688 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1689 -- START WITH TASK_ID = x_TASK_ID);
1690 --
1691 -- -- Delete non-labor bill rate overrides
1692 -- x_err_stage := 'Delete nl bill rate overrides for task '|| x_task_id;
1693 -- delete from pa_nl_bill_rate_overrides
1694 -- where task_id in
1695 -- (select task_id
1696 -- from pa_tasks
1697 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1698 -- START WITH TASK_ID = x_TASK_ID);
1699
1700 -- anlee
1701 -- Commenting out for performance bug 2800129
1702 -- Delete compiled multipliers, details of compiled set id
1703 -- x_err_stage := 'Delete compiled multiplier for task '|| x_task_id;
1704 -- delete from pa_compiled_multipliers
1705 -- where ind_compiled_set_id in
1706 -- (select ics.ind_compiled_set_id
1707 -- from pa_ind_compiled_sets ics,
1708 -- pa_ind_rate_sch_revisions rev,
1709 -- pa_ind_rate_schedules sch
1710 -- where ics.ind_rate_sch_revision_id =
1711 -- rev.ind_rate_sch_revision_id
1712 -- and rev.ind_rate_sch_id = sch.ind_rate_sch_id
1713 -- and sch.task_id in
1714 -- (select task_id
1715 -- from pa_tasks
1716 -- connect by prior task_id = parent_task_id
1717 -- start with task_id = x_task_id));
1718
1719 -- anlee
1720 -- Commenting out for performance bug 2800129
1721 -- Delete compiled compiled set
1722 -- x_err_stage := 'Delete compiled sets for task '|| x_task_id;
1723 -- delete from pa_ind_compiled_sets
1724 -- where ind_rate_sch_revision_id in
1725 -- (select rev.ind_rate_sch_revision_id
1726 -- from pa_ind_rate_sch_revisions rev,
1727 -- pa_ind_rate_schedules sch
1728 -- where rev.ind_rate_sch_id = sch.ind_rate_sch_id
1729 -- and sch.task_id in
1730 -- (select task_id
1731 -- from pa_tasks
1732 -- connect by prior task_id = parent_task_id
1733 -- start with task_id = x_task_id));
1734
1735 -- anlee
1736 -- Commenting out for performance bug 2800129
1737 -- Delete ind cost multipliers, details of ind rate sch revisions
1738 -- x_err_stage := 'Delete ind cost multiplier for task '|| x_task_id;
1739 -- delete from pa_ind_cost_multipliers
1740 -- where ind_rate_sch_revision_id in
1741 -- (select rev.ind_rate_sch_revision_id
1742 -- from pa_ind_rate_sch_revisions rev, pa_ind_rate_schedules sch
1743 -- where rev.ind_rate_sch_id = sch.ind_rate_sch_id
1744 -- and sch.task_id in
1745 -- (select task_id
1746 -- from pa_tasks
1747 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1748 -- START WITH TASK_ID = x_TASK_ID));
1749
1750 -- anlee
1751 -- Commenting out for performance bug 2800129
1752 -- Delete ind rate sch revisions, details of ind rate schedules
1753 -- x_err_stage := 'Delete ind rate sch revision for task '|| x_task_id;
1754 -- delete from pa_ind_rate_sch_revisions
1755 -- where ind_rate_sch_id in
1756 -- (select ind_rate_sch_id
1757 -- from pa_ind_rate_schedules
1758 -- where task_id in
1759 -- (select task_id
1760 -- from pa_tasks
1761 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1762 -- START WITH TASK_ID = x_TASK_ID));
1763
1764 /*
1765 * Anonymous Block to delete burdening setup starts.
1766 */
1767 declare
1768 cursor task_cur ( l_start_task_id pa_tasks.task_id%TYPE )
1769 is
1770 select task_id
1771 from pa_tasks
1772 connect by prior task_id = parent_task_id
1773 start with task_id = l_start_task_id
1774 ;
1775 cursor sch_cur ( l_task_id IN pa_tasks.task_id%TYPE )
1776 is
1777 select sch.ind_rate_sch_id
1778 from pa_ind_rate_schedules sch
1779 where sch.task_id = l_task_id
1780 ;
1781 cursor rev_cur ( l_ind_rate_sch_id IN pa_ind_rate_schedules_all_bg.ind_rate_sch_id%TYPE )
1782 is
1783 select rev.ind_rate_sch_revision_id
1784 from pa_ind_rate_sch_revisions rev
1785 where rev.ind_rate_sch_id = l_ind_rate_sch_id
1786 ;
1787 cursor ics_cur ( l_ind_rate_sch_revision_id IN pa_ind_rate_sch_revisions.ind_rate_sch_revision_id%TYPE )
1788 is
1789 select ics.ind_compiled_set_id
1790 from pa_ind_compiled_sets ics
1791 where ics.ind_rate_sch_revision_id = l_ind_rate_sch_revision_id
1792 ;
1793 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1794 l_ind_rate_sch_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1795 l_ind_rate_sch_rev_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1796 l_ind_comp_set_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1797 begin
1798
1799 open task_cur ( x_task_id );
1800 fetch task_cur
1801 bulk collect
1802 into l_task_id_tab;
1803 close task_cur;
1804
1805 -- added for bug#3693197
1806 if nvl(l_task_id_tab.LAST,0) >0 then
1807
1808 -- Delete transaction controls
1809 x_err_stage := 'Delete txn controls for task '|| x_task_id;
1810 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1811 delete from pa_transaction_controls
1812 where task_id =l_task_id_tab(i)
1813 and project_id = l_project_id;
1814
1815
1816 -- Delete labor multipliers
1817 x_err_stage := 'Delete labor multipliers for task '|| x_task_id;
1818 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1819 delete from pa_labor_multipliers
1820 where task_id =l_task_id_tab(i) ;
1821
1822 -- Delete job bill rate overrides
1823 x_err_stage := 'Delete job bill rate overrides for task '|| x_task_id;
1824 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1825 delete from pa_job_bill_rate_overrides
1826 where task_id =l_task_id_tab(i) ;
1827
1828 -- Delete job assignment overrides
1829 x_err_stage := 'Delete job assignment overrides for task '|| x_task_id;
1830 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1831 delete from pa_job_assignment_overrides
1832 where task_id =l_task_id_tab(i) ;
1833
1834 -- Delete emp bill rate overrides
1835 x_err_stage := 'Delete emp bill rate overrides for task '|| x_task_id;
1836 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1837 delete from pa_emp_bill_rate_overrides
1838 where task_id =l_task_id_tab(i) ;
1839
1840 -- Delete nl bill rate overrides
1841 x_err_stage := 'Delete nl bill rate overrides for task '|| x_task_id;
1842 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1843 delete from pa_nl_bill_rate_overrides
1844 where task_id =l_task_id_tab(i) ;
1845
1846 -- Delete project asset assignments
1847 x_err_stage := 'Delete project asset assignments for task '|| x_task_id;
1848 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1849 delete from pa_project_asset_assignments
1850 where task_id =l_task_id_tab(i) ;
1851
1852 end if ;
1853
1854
1855 for i in 1 .. l_task_id_tab.count
1856 loop
1857 open sch_cur ( l_task_id_tab (i) );
1858 fetch sch_cur
1859 bulk collect
1860 into l_ind_rate_sch_id_tab;
1861 close sch_cur;
1862
1863 for i in 1 .. l_ind_rate_sch_id_tab.count
1864 loop
1865 open rev_cur ( l_ind_rate_sch_id_tab (i) );
1866 fetch rev_cur
1867 bulk collect
1868 into l_ind_rate_sch_rev_id_tab;
1869 close rev_cur;
1870
1871 for i in 1 .. l_ind_rate_sch_rev_id_tab.count
1872 loop
1873 open ics_cur ( l_ind_rate_sch_rev_id_tab (i) );
1874 fetch ics_cur
1875 bulk collect
1876 into l_ind_comp_set_id_tab;
1877 close ics_cur;
1878
1879 if ( l_ind_comp_set_id_tab.count > 0 )
1880 then
1881 forall i in l_ind_comp_set_id_tab.first .. l_ind_comp_set_id_tab.last
1882 delete
1883 from pa_compiled_multipliers comp_mult
1884 where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
1885 ;
1886 forall i in l_ind_comp_set_id_tab.first .. l_ind_comp_set_id_tab.last
1887 delete
1888 from pa_ind_compiled_sets ics
1889 where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
1890 ;
1891 end if;
1892 end loop;
1893
1894 if ( l_ind_rate_sch_rev_id_tab.count > 0 )
1895 then
1896 forall i in l_ind_rate_sch_rev_id_tab.first .. l_ind_rate_sch_rev_id_tab.last
1897 delete
1898 from pa_ind_cost_multipliers icm
1899 where icm.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
1900 ;
1901 forall i in l_ind_rate_sch_rev_id_tab.first .. l_ind_rate_sch_rev_id_tab.last
1902 delete
1903 from pa_ind_rate_sch_revisions rev
1904 where rev.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
1905 ;
1906 end if;
1907
1908 end loop; -- schedule
1909
1910 if ( l_ind_rate_sch_id_tab.count > 0 )
1911 then
1912 forall i in l_ind_rate_sch_id_tab.first .. l_ind_rate_sch_id_tab.last
1913 delete
1914 from pa_ind_rate_schedules sch
1915 where sch.ind_rate_sch_id = l_ind_rate_sch_id_tab(i)
1916 ;
1917 end if;
1918
1919 end loop; -- task
1920 exception
1921 when others
1922 then
1923 x_err_code := SQLCODE;
1924 rollback to delete_task;
1925 return;
1926 end; -- end of anonymous block to delete burdening setup.
1927 /* New code to delete burdening setup ends **/
1928
1929 -- Delete project asset assigments
1930 -- 3693197 : Commented and moved above in the anonymous block
1931
1932 --x_err_stage := 'Delete proj asset assignmt for task '|| x_task_id;
1933 -- delete from pa_project_asset_assignments
1934 -- where task_id in
1935 -- (select task_id
1936 -- from pa_tasks
1937 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1938 -- START WITH TASK_ID = x_TASK_ID);
1939
1940 --Retention Changes --Ansari bug 2362168
1941 PA_RETENTION_UTIL.delete_retention_rules(
1942 p_project_id => l_project_id,
1943 p_task_id => x_TASK_ID,
1944 x_return_status => x_return_status,
1945 x_msg_count => x_msg_count,
1946 x_msg_data => x_msg_data );
1947
1948 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1949 --Added for bug 3617393
1950 x_err_code := 150;
1951 x_err_stack := x_err_stack || '->delete_retention_rules: '|| l_project_id;
1952 IF l_template_flag = 'Y' THEN
1953 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
1954 ELSE
1955 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
1956 END IF;
1957 ROLLBACK TO delete_task ;
1958 --End bug 3617393
1959 RETURN;
1960 END IF;
1961
1962 --Retention Changes --Ansari
1963
1964
1965 -- Delete percent complete .Need to pass project id
1966 -- since that is the leading key in the index
1967
1968 x_err_stage := 'Delete task percent complete ';
1969 delete from pa_percent_completes
1970 where project_id = l_project_id
1971 and task_id in
1972 (select task_id
1973 from pa_tasks
1974 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1975 START WITH TASK_ID = x_TASK_ID);
1976
1977 -- hsiu added.
1978 -- 30 Mar 2001. For Project Contracts.
1979 -- Delete project structure relationship
1980 x_err_stage := 'Delete project structure relationship for task ';
1981 PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP(
1982 p_api_version => 1.0,
1983 p_init_msg_list => FND_API.G_FALSE,
1984 p_commit => FND_API.G_TRUE,
1985 p_validate_only => FND_API.G_FALSE,
1986 p_debug_mode => 'N',
1987 p_task_id => x_TASK_ID,
1988 p_project_id => null,
1989 x_return_status => x_return_status,
1990 x_msg_count => x_msg_count,
1991 x_msg_data => x_msg_data
1992 );
1993 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1994 --Added for bug 3617393
1995 x_err_code := 160;
1996 x_err_stack := x_err_stack || '->DELETE_RELATIONSHIP: '|| l_project_id;
1997 IF l_template_flag = 'Y' THEN
1998 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
1999 ELSE
2000 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
2001 END IF;
2002 ROLLBACK TO delete_task ;
2003 --End bug 3617393
2004 return;
2005 END IF;
2006 -- end delete project structure relationship
2007
2008 --Commented for the Bug 14213218
2009 /* -- Delete task
2010 x_err_stage := 'Delete any task in the subtree of task '|| x_task_id;
2011 delete from pa_tasks
2012 where task_id in
2013 (select task_id
2014 from pa_tasks
2015 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2016 START WITH TASK_ID = x_TASK_ID); */
2017 --Bug 14213218 End
2018
2019 -- get parent task id
2020 x_err_stage := 'get parent task id for task '|| x_task_id;
2021 x_parent_task_id := pa_task_utils.get_parent_task_id(x_task_id);
2022
2023 if ( x_parent_task_id < 0 ) then -- Oracle error
2024 x_err_code := x_parent_task_id;
2025 return;
2026 end if;
2027
2028 if (x_parent_task_id is not null ) then
2029 -- Check if task is last child
2030 x_err_stage := 'check last child for '|| x_task_id;
2031 status_code := pa_task_utils.check_last_child(x_task_id);
2032
2033 if ( status_code = 1 ) then
2034 -- set parent task's chargeable_flag to 'Y
2035 x_err_stage := 'update parent task chargeable flag';
2036
2037 update pa_tasks
2038 set chargeable_flag = 'Y'
2039 where task_id = x_parent_task_id;
2040
2041 elsif ( status_code < 0 ) then
2042 x_err_code := status_code;
2043 return;
2044 end if;
2045 end if;
2046
2047 -- commenting changes done for bug#16083858 below as it is not necessary.
2048 /* bug#16083858 CBS enhancement
2049 * deleting the alt_task_id corresponding to the tasks that are being removed.
2050 */
2051 /*
2052 Declare
2053 l_alt_task_Ids SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();
2054 cursor alt_task_cur ( l_task_id pa_tasks.task_id%TYPE )
2055 is
2056 select alt_task_id from pa_alternate_tasks
2057 where proj_element_id in
2058 (select task_id
2059 from pa_tasks
2060 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2061 START WITH TASK_ID = l_task_id)
2062 ;
2063 Begin
2064 open alt_task_cur ( x_task_id );
2065 fetch alt_task_cur
2066 bulk collect
2067 into l_alt_task_Ids;
2068 close alt_task_cur;
2069
2070 PA_ALTERNATE_TASK_PVT.Delete_Alt_Task(
2071 p_Alt_Task_Id => l_alt_task_Ids,
2072 x_return_status => x_return_status,
2073 x_msg_count => x_msg_count,
2074 x_msg_data => x_msg_data );
2075 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2076
2077 x_err_code := 165;
2078 x_err_stack := x_err_stack || '->Delete_Alt_Task: '|| x_task_id;
2079 x_err_stage := 'Deleting Alternate tasks for the task failed :: '|| x_task_id;
2080 ROLLBACK TO delete_task ;
2081 return;
2082 END IF;
2083 End;
2084 */
2085
2086 --Bug 14213218: Deleting the task after updating the parent task chargeable flag to 'Y'
2087 -- Delete task
2088 x_err_stage := 'Delete any task in the subtree of task '|| x_task_id;
2089 delete from pa_tasks
2090 where task_id in
2091 (select task_id
2092 from pa_tasks
2093 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2094 START WITH TASK_ID = x_TASK_ID);
2095
2096 --Bug 14213218 End
2097
2098 x_err_stack := old_stack;
2099
2100 exception
2101 when others then
2102 x_err_code := SQLCODE;
2103 rollback to delete_task;
2104 return;
2105 end delete_task;
2106
2107 --
2108 -- PROCEDURE
2109 -- delete_project_type
2110 --
2111 -- HISTORY
2112 -- 01-NOV-02 Mansari Created
2113 --
2114 procedure delete_project_type (
2115 x_project_type_id IN number
2116 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2117 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2118 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2119 )
2120 is
2121
2122 l_return_status varchar2(1);
2123 begin
2124
2125 SAVEPOINT delete_project_type;
2126
2127 x_return_status := FND_API.G_RET_STS_SUCCESS;
2128
2129 --No need to call this API here
2130 /* PA_PROJECT_UTILS.check_delete_project_type_ok(
2131 p_project_type_id => x_project_type_id
2132 ,x_return_status => x_return_status
2133 ,x_error_message_code => x_msg_data
2134 );
2135
2136 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2137 THEN
2138 RETURN;
2139 END IF;
2140 */
2141
2142 -- Delete project type
2143 delete pa_project_types_all --bug 4584792
2144 where project_type_id = x_project_type_id;
2145
2146 exception
2147 when others then
2148 x_return_status := FND_API.G_RET_STS_SUCCESS;
2149 rollback to delete_project;
2150 return;
2151 end delete_project_type;
2152
2153 --
2154 -- PROCEDURE
2155 -- delete_class_category
2156 --
2157 -- HISTORY
2158 -- 01-NOV-02 Mansari Created
2159 --
2160 procedure delete_class_category (
2161 x_class_category IN VARCHAR2
2162 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2163 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2164 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2165 )
2166 is
2167
2168 l_return_status varchar2(1);
2169 begin
2170
2171 SAVEPOINT delete_class_category;
2172
2173 x_return_status := FND_API.G_RET_STS_SUCCESS;
2174
2175 -- Delete class_category
2176 delete pa_class_categories
2177 where class_category = x_class_category
2178 ;
2179
2180 exception
2181 when others then
2182 x_return_status := FND_API.G_RET_STS_SUCCESS;
2183 rollback to delete_class_category;
2184 return;
2185 end delete_class_category;
2186
2187 --
2188 -- PROCEDURE
2189 -- delete_class_code
2190 --
2191 -- HISTORY
2192 -- 01-NOV-02 Mansari Created
2193 --
2194 procedure delete_class_code (
2195 x_class_category IN VARCHAR2
2196 , x_class_code IN VARCHAR2
2197 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2198 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2199 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2200 )
2201 is
2202
2203 l_return_status varchar2(1);
2204 begin
2205
2206 SAVEPOINT delete_class_code;
2207
2208 x_return_status := FND_API.G_RET_STS_SUCCESS;
2209
2210 -- Delete class_category
2211 delete pa_class_codes
2212 where class_category = x_class_category
2213 and class_code = x_class_code
2214 ;
2215
2216 exception
2217 when others then
2218 x_return_status := FND_API.G_RET_STS_SUCCESS;
2219 rollback to delete_class_code;
2220 return;
2221 end delete_class_code;
2222
2223 end PA_PROJECT_CORE;