[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CORE
Source
1 package body PA_PROJECT_CORE as
2 -- $Header: PAXPCORB.pls 120.6.12010000.3 2008/09/30 09:40:24 sugupta 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 => FND_API.G_TRUE,
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 => FND_API.G_TRUE ,
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 FOR i IN ( SELECT item_type,item_key
943 FROM pa_wf_processes
944 WHERE entity_key1 = TO_CHAR(x_project_id))
945 LOOP
946
947 wf_engine.itemstatus ( itemtype => i.item_type,
948 itemkey => i.item_key,
949 status => x_status,
950 result => x_result );
951 IF x_status = 'ACTIVE' THEN
952
953 wf_engine.abortprocess ( itemtype => i.item_type,
954 itemkey => i.item_key );
955 END IF;
956
957 wf_purge.total( itemtype => i.item_type,
958 itemkey => i.item_key );
959
960 END LOOP;
961
962 DELETE FROM pa_wf_processes
963 WHERE entity_key1 = TO_CHAR(x_project_id);
964
965 -- End of changes for bug 2898598
966
967 --Bug 3610949 : Delete budget versions
968 IF ( l_wp_enabled = 'Y' ) THEN
969 PA_FIN_PLAN_PUB.Delete_Version( p_project_id => x_project_id
970 ,p_budget_version_id => null
971 ,p_record_version_number => null
972 ,p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN
973 ,x_return_status => x_return_status
974 ,x_msg_count => x_msg_count
975 ,x_msg_data => x_msg_data
976 );
977 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
978 x_err_code := 130;
979 x_err_stack := x_err_stack || '->Delete_Version: '|| x_project_id;
980 IF l_template_flag = 'Y' THEN
981 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
982 ELSE
983 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
984 END IF;
985 ROLLBACK TO delete_project;
986 RETURN;
987 END IF;
988 END IF;
989 --End : Bug 3610949
990
991 --Bug 3610949 : The following code used to be in KEY-DELREC trigger of project_folder block.
992 --Moved the call here, after delete_version api call
993 pa_fin_plan_utils.Delete_Fp_Options( p_project_id => x_project_id,
994 x_err_code => x_err_code );
995 if x_err_code <> 0 Then
996 x_err_code := 140;
997 x_err_stack := x_err_stack ||'->Delete_Fp_Options: '|| x_project_id;
998 IF l_template_flag = 'Y' THEN
999 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
1000 ELSE
1001 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
1002 END IF;
1003 ROLLBACK TO delete_project;
1004 RETURN;
1005 end if;
1006 -- Start of Bug 4705154
1007 -- Bug No 4705154:The Following API call is being added to delete entries of project from PA_OBJ_STATUS_CHANGES Table.
1008 PA_CONTROL_ITEMS_UTILS.DELETE_OBJ_STATUS_CHANGES
1009 (
1010 p_object_type =>'PA_PROJECTS'
1011 ,p_object_id => x_project_id
1012 ,x_msg_count =>x_msg_count
1013 ,x_msg_data =>x_msg_data
1014 ,x_return_status =>x_return_status);
1015 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1016 x_err_code := 150;
1017 x_err_stack := x_err_stack || '->Delete_Obj_Status_Changes: '|| x_project_id;
1018 IF l_template_flag = 'Y' THEN
1019 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_TEMPLATE');
1020 ELSE
1021 x_err_stage := pa_project_core.get_message_from_stack('PA_CANT_DELETE_PROJECT');
1022 END IF;
1023 ROLLBACK TO delete_project;
1024 return;
1025 END IF;
1026 -- End of Bug no 4705154
1027 -- Delete project
1028 delete pa_projects
1029 where project_id = x_project_id;
1030
1031 x_err_stack := old_stack;
1032
1033 --Bug 3617393
1034 x_err_code := 0;
1035 if x_commit = FND_API.G_TRUE then
1036 commit;
1037 end if;
1038
1039 exception
1040 when others then
1041 x_err_code := SQLCODE;
1042 x_err_stage := 'DELETE PROJECT: '||SUBSTR( SQLERRM,1,1900);
1043 rollback to delete_project;
1044 return;
1045 end delete_project;
1046
1047
1048 --
1049 -- PROCEDURE
1050 -- import_task
1051 -- PURPOSE
1052 -- This objective of this API is to import tasks into
1053 -- PA system. This API can be called by task import system
1054 -- and other external systems. Other task related information
1055 -- can be entered by using Enter Project form or calling table
1056 -- handlers.
1057 --
1058 --
1059 -- HISTORY
1060 -- 24-OCT-95 R. Chiu Created
1061 --
1062 procedure import_task ( x_project_id IN number
1063 , x_task_name IN varchar2
1064 , x_task_number IN varchar2
1065 , x_service_type_code IN varchar2
1066 , x_organization_id IN number
1067 , x_description IN varchar2
1068 , x_task_start_date IN date
1069 , x_task_end_date IN date
1070 , x_parent_task_id IN number
1071 , x_pm_project_id IN number
1072 , x_pm_task_id IN number
1073 , x_manager_id IN number
1074 , x_new_task_id OUT NOCOPY number --File.Sql.39 bug 4440895
1075 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1076 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1077 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1078 is
1079
1080 x_rowid varchar2(18);
1081 x_wbs_level number;
1082 x_task_id number;
1083 x_top_task_id number;
1084 x_address_id number;
1085 x_org_id number;
1086 x_labor_bill_rate_org_id number;
1087 x_labor_std_bill_rate_schd varchar2(20);
1088 x_proj_start_date date;
1089 x_proj_end_date date;
1090 x_labor_schedule_discount number;
1091 x_labor_schedule_fixed_date date;
1092 x_nl_bill_rate_org_id number;
1093 x_nl_std_bill_rate_schd varchar2(30);
1094 x_nl_schedule_discount number;
1095 x_nl_schedule_fixed_date date;
1096 x_labor_sch_type varchar2(1);
1097 x_non_labor_sch_type varchar2(1);
1098 x_cost_ind_rate_sch_id number;
1099 x_cost_ind_sch_fixed_date date;
1100 x_rev_ind_rate_sch_id number;
1101 x_rev_ind_sch_fixed_date date;
1102 x_inv_ind_rate_sch_id number;
1103 x_inv_ind_sch_fixed_date date;
1104 x_serv_type_code varchar2(30);
1105 x_project_type_class_code varchar2(30);
1106 x_billable_flag varchar2(1);
1107 status_code number;
1108 old_stack varchar2(630);
1109 begin
1110
1111 Savepoint import_task;
1112 -- Check project id
1113 if (x_project_id is null ) then
1114 x_err_code := 10;
1115 x_err_stage := 'PA_NO_PROJ_ID';
1116 return;
1117 end if ;
1118
1119 -- Check task name
1120 if (x_task_name is null ) then
1121 x_err_code := 20;
1122 x_err_stage := 'PA_NO_TASK_NAME';
1123 return;
1124 end if ;
1125
1126 -- Check task number
1127 if (x_task_number is null ) then
1128 x_err_code := 30;
1129 x_err_stage := 'PA_NO_TASK_NUMBER';
1130 return;
1131 end if ;
1132
1133 -- Uniqueness check for task number
1134 x_err_stage := 'check uniqueness for task number '|| x_task_number;
1135 status_code :=
1136 pa_task_utils.check_unique_task_number(x_project_id,
1137 x_task_number,
1138 null);
1139 if ( status_code = 0 ) then
1140 x_err_code := 40;
1141 x_err_stage := 'PA_TASK_NUM_NOT_UNIQUE';
1142 return;
1143 elsif ( status_code < 0 ) then
1144 x_err_code := status_code;
1145 return;
1146 end if;
1147
1148 if (x_parent_task_id is null) then -- creating top task
1149 x_wbs_level := 1;
1150 x_top_task_id := NULL;
1151 else -- creating subtask
1152
1153 -- check create subtask ok
1154 x_err_stage :=
1155 'check create subtask for parent task '|| x_parent_task_id;
1156
1157 --Bug 2947492 The following call is modified to pass params by notation.
1158 pa_task_utils.check_create_subtask_ok(x_task_id => x_parent_task_id,
1159 x_err_code => x_err_code,
1160 x_err_stage => x_err_stage,
1161 x_err_stack => x_err_stack);
1162
1163 -- if application error or oracle error then discontinue.
1164 if ( x_err_code <> 0 ) then
1165 return;
1166 end if;
1167
1168 -- get wbs level for parent task id
1169 x_err_stage :=
1170 'get wbs level for parent task '|| x_parent_task_id;
1171 x_wbs_level := pa_task_utils.get_wbs_level(x_parent_task_id);
1172 if (x_wbs_level is null) then
1173 x_err_code := 50;
1174 x_err_stage := 'PA_WBS_LEVEL_NOT_FOUND';
1175 return;
1176 elsif ( x_wbs_level < 0 ) then -- Oracle error
1177 x_err_code := x_wbs_level;
1178 return;
1179 end if;
1180 x_wbs_level := x_wbs_level + 1;
1181 -- increase level by 1 for child task
1182
1183 -- get top task id for parent task id
1184 x_err_stage :=
1185 'get top task id for parent task '|| x_parent_task_id;
1186 x_top_task_id := pa_task_utils.get_top_task_id(x_parent_task_id);
1187 if (x_top_task_id is null) then
1188 x_err_code := 60;
1189 x_err_stage := 'PA_TASK_ID_NOT_FOUND';
1190 return;
1191 elsif ( x_top_task_id < 0 ) then -- Oracle error
1192 x_err_code := x_top_task_id;
1193 return;
1194 end if;
1195 end if;
1196
1197 -- Get default task information.
1198
1199 x_err_stage := 'get default task information ';
1200
1201 declare
1202 cursor c1 is
1203 SELECT
1204 P.start_date,
1205 P.completion_date,
1206 P.carrying_out_organization_id,
1207 P.labor_bill_rate_org_id,
1208 P.labor_std_bill_rate_schdl,
1209 P.labor_schedule_discount,
1210 P.labor_schedule_fixed_date,
1211 P.non_labor_bill_rate_org_id,
1212 P.non_labor_std_bill_rate_schdl,
1213 P.non_labor_schedule_discount,
1214 P.non_labor_schedule_fixed_date,
1215 P.labor_sch_type,
1216 P.non_labor_sch_type,
1217 P.cost_ind_rate_sch_id,
1218 P.cost_ind_sch_fixed_date,
1219 P.rev_ind_rate_sch_id,
1220 P.rev_ind_sch_fixed_date,
1221 P.inv_ind_rate_sch_id,
1222 P.inv_ind_sch_fixed_date,
1223 PT.service_type_code,
1224 PT.project_type_class_code
1225 FROM pa_projects P, pa_project_types PT
1226 WHERE P.project_id = x_project_id
1227 AND P.project_type = PT.project_type;
1228
1229 begin
1230 open c1;
1231 fetch c1 into
1232 x_proj_start_date,
1233 x_proj_end_date,
1234 x_org_id,
1235 x_labor_bill_rate_org_id,
1236 x_labor_std_bill_rate_schd,
1237 x_labor_schedule_discount,
1238 x_labor_schedule_fixed_date,
1239 x_nl_bill_rate_org_id,
1240 x_nl_std_bill_rate_schd,
1241 x_nl_schedule_discount,
1242 x_nl_schedule_fixed_date,
1243 x_labor_sch_type,
1244 x_non_labor_sch_type,
1245 x_cost_ind_rate_sch_id,
1246 x_cost_ind_sch_fixed_date,
1247 x_rev_ind_rate_sch_id,
1248 x_rev_ind_sch_fixed_date,
1249 x_inv_ind_rate_sch_id,
1250 x_inv_ind_sch_fixed_date,
1251 x_serv_type_code,
1252 x_project_type_class_code;
1253
1254 if c1%notfound then
1255 close c1;
1256 x_err_code := 70;
1257 x_err_stage := 'PA_NO_ROW_FOUND';
1258 return;
1259 end if;
1260 close c1;
1261
1262 exception
1263 when others then
1264 close c1;
1265 x_err_code := SQLCODE;
1266 return;
1267 end ;
1268
1269 -- Get default address id
1270 begin
1271 x_err_stage := 'get default address id ';
1272
1273 -- 4363092 TCA changes, replaced RA views with HZ tables
1274 /*
1275 SELECT
1276 DISTINCT A.address_id
1277 INTO x_address_id
1278 FROM ra_addresses A, ra_site_uses SU
1279 WHERE A.address_id = SU.address_id
1280 AND A.customer_id IN
1281 (SELECT customer_id
1282 FROM pa_project_customers
1283 WHERE project_id = x_project_id)
1284 AND NVL(SU.STATUS,'A') = 'A'
1285 AND SU.site_use_code = 'SHIP_TO';
1286 */
1287
1288 SELECT
1289 DISTINCT acct_site.cust_acct_site_id
1290 INTO x_address_id
1291 FROM
1292 hz_cust_acct_sites_all acct_site,
1293 hz_cust_site_uses su
1294 WHERE
1295 acct_site.cust_acct_site_id = su.cust_acct_site_id
1296 AND acct_site.cust_account_id IN
1297 (SELECT customer_id FROM pa_project_customers WHERE project_id = x_project_id)
1298 AND NVL(SU.STATUS,'A') = 'A'
1299 AND SU.site_use_code = 'SHIP_TO';
1300
1301 -- 4363092 end
1302
1303 exception
1304 when NO_DATA_FOUND then
1305 x_address_id := NULL;
1306 when TOO_MANY_ROWS then
1307 x_address_id := NULL;
1308 when others then
1309 x_err_code := SQLCODE;
1310 return;
1311 end ;
1312
1313
1314 -- verify date range
1315 if (x_task_start_date is not null AND x_task_end_date is not null
1316 AND x_task_start_date > x_task_end_date ) then
1317 -- invaid task date range
1318 x_err_code := 80;
1319 x_err_stage := 'PA_SU_INVALID_DATES';
1320 -- existing message name from PAXTKETK
1321 return;
1322 else
1323 if ( (x_task_start_date is null OR
1324 x_proj_start_date is null OR
1325 x_task_start_date >= x_proj_start_date)
1326 AND (x_task_end_date is null OR
1327 x_proj_end_date is null OR
1328 x_task_end_date <= x_proj_end_date) ) then
1329 null; -- task dates are within project dates range
1330 else
1331 x_err_code := 90;
1332 x_err_stage := 'PA_TK_OUTSIDE_PROJECT_RANGE';
1333 -- existing message name from PAXTKETK
1334 return;
1335 end if;
1336 end if;
1337
1338 -- set task billable flag
1339 if (x_project_type_class_code = 'INDIRECT') then
1340 x_billable_flag := 'N';
1341 else
1342 x_billable_flag := 'Y';
1343 end if;
1344
1345
1346 -- Update parent task chargeable flag to 'N'.
1347 -- Only lowest tasks are chargeable.
1348
1349 if (x_parent_task_id is not null) then
1350 x_err_stage := 'update parent task chargeable flag';
1351
1352 update pa_tasks
1353 set chargeable_flag = 'N'
1354 where task_id = x_parent_task_id;
1355 end if;
1356
1357 -- call table handler to insert task
1358 begin
1359 x_err_stage := 'Insert task for project '|| x_project_id;
1360
1361 pa_tasks_pkg.insert_row(
1362 x_rowid,
1363 x_task_id,
1364 x_project_id,
1365 x_task_number,
1366 sysdate,
1367 FND_GLOBAL.USER_ID,
1368 sysdate,
1369 FND_GLOBAL.USER_ID,
1370 FND_GLOBAL.LOGIN_ID,
1371 x_task_name,
1372 x_task_name, -- long name
1373 x_top_task_id,
1374 x_wbs_level,
1375 'N',
1376 'N',
1377 x_parent_task_id,
1378 x_description,
1379 nvl(x_organization_id, x_org_id),
1380 nvl(x_service_type_code, x_serv_type_code),
1381 x_manager_id,
1382 'Y',
1383 x_billable_flag,
1384 'N',
1385 nvl(x_task_start_date, x_proj_start_date),
1386 nvl(x_task_end_date, x_proj_end_date),
1387 x_address_id,
1388 X_Labor_Bill_Rate_Org_Id,
1389 X_Labor_Std_Bill_Rate_Schd,
1390 X_Labor_Schedule_Fixed_Date,
1391 X_Labor_Schedule_Discount,
1392 X_NL_Bill_Rate_Org_Id,
1393 X_NL_Std_Bill_Rate_Schd,
1394 X_NL_Schedule_Fixed_Date,
1395 X_NL_Schedule_Discount,
1396 null,
1397 null,
1398 null,
1399 null,
1400 null,
1401 null,
1402 null,
1403 null,
1404 null,
1405 null,
1406 null,
1407 null,
1408 X_Cost_Ind_Rate_Sch_Id,
1409 X_Rev_Ind_Rate_Sch_Id,
1410 X_Inv_Ind_Rate_Sch_Id,
1411 X_Cost_Ind_Sch_Fixed_Date,
1412 X_Rev_Ind_Sch_Fixed_Date,
1413 X_Inv_Ind_Sch_Fixed_Date,
1414 X_Labor_Sch_Type,
1415 X_Non_Labor_Sch_Type,
1416 NULL,
1417 NULL,
1418 NULL,
1419 NULL,
1420 NULL,
1421 NULL,
1422 NULL,
1423 NULL,
1424 NULL,
1425 NULL,
1426 NULL,
1427 -- 01-APR-2001
1428 -- hsiu Added for forecasting changes
1429 NULL,
1430 NULL,
1431 --MCA Sakthi for MultiAgreementCurreny Project
1432 NULL,
1433 NULL,
1434 NULL,
1435 --MCA Sakthi for MultiAgreementCurreny Project
1436 NULL,
1437 NULL,
1438 --PA L Changes 2872708
1439 'N',
1440 'Y',
1441 null,
1442
1443 --End PA L Changes 2872708
1444 /*FPM Dev -Project setup changes */
1445 null,
1446 null,
1447 null
1448 );
1449 exception
1450 when NO_DATA_FOUND then
1451 x_err_code := 100;
1452 x_err_stage := 'PA_NO_ROW_INSERTED';
1453 rollback to import_task;
1454 return;
1455 when others then
1456 x_err_code := SQLCODE;
1457 rollback to import_task;
1458 return;
1459 end;
1460
1461 x_new_task_id := x_task_id;
1462 x_err_stack := old_stack;
1463
1464 exception
1465 when others then
1466 x_err_code := SQLCODE;
1467 rollback to import_task;
1468 return;
1469 end import_task;
1470
1471 --
1472 -- PROCEDURE
1473 -- delete_task
1474 -- PURPOSE
1475 -- This objective of this API is to delete tasks from
1476 -- the PA system. All task detail information along
1477 -- with the specified task will be deleted if there's
1478 -- no transaction charged to the task. This API can
1479 -- be used by Enter Project form and other external systems.
1480 --
1481 -- To delete a top task and its subtasks, the following
1482 -- requirements must be met:
1483 -- * No event at top level task
1484 -- * No funding at top level tasks
1485 -- * No baseline budget at top level task
1486 -- * Meet the following requirements for its children
1487 --
1488 -- To delete a mid level task, it involves checking its
1489 -- children and meeting the following requirements for
1490 -- its lowest level task.
1491 --
1492 -- To delete a lowest level task, the following requirements
1493 -- must be met:
1494 -- * No expenditure item at lowest level task
1495 -- * No puchase order line at lowest level task
1496 -- * No requisition line at lowest level task
1497 -- * No supplier invoice (ap invoice) at lowest level task
1498 -- * No baseline budget at lowest level task
1499 --
1500 -- HISTORY
1501 -- 25-OCT-95 R. Chiu Created
1502 --
1503 procedure delete_task ( x_task_id IN number
1504 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
1505 , x_validate_flag IN varchar2 DEFAULT 'Y' -- Adding paramater x_validate_flag
1506 , x_bulk_flag IN VARCHAR2 DEFAULT 'N' -- 4201927
1507 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1508 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1509 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1510 is
1511
1512 old_stack varchar2(630);
1513 status_code number;
1514 x_parent_task_id number;
1515 l_project_id number;
1516 x_return_status varchar2(1);
1517 x_msg_count number;
1518 x_msg_data varchar2(2000);
1519 l_cc_tax_task_id number; ---- Bug 6629057
1520
1521 cursor l_project_csr is
1522 select project_id from
1523 pa_tasks where
1524 task_id = x_task_id;
1525
1526 --Added for bug 3617393
1527 CURSOR get_template_flag(c_project_id IN NUMBER) IS
1528 SELECT template_flag
1529 FROM pa_projects_all
1530 WHERE project_id = c_project_id;
1531
1532 cursor cc_task_id_csr(x_taskid number) ---- Bug 6629057
1533 is select cc_tax_task_id from pa_projects_all pj
1534 where pj.project_id = l_project_id;
1535
1536 l_template_flag VARCHAR2(1) := 'N';
1537 --Added for bug 3617393
1538
1539 begin
1540
1541 SAVEPOINT delete_task;
1542
1543 old_stack := x_err_stack; -- Fix for Bug # 4513291. It should initialize old_stack before appending values
1544 x_err_stack := x_err_stack || '->delete_task';
1545
1546 x_err_code := 0;
1547
1548 x_err_stage := 'Fetching project id for task '|| x_task_id;
1549
1550 Open l_project_csr;
1551 Fetch l_project_csr into l_project_id;
1552 If l_project_csr%NOTFOUND THEN
1553 close l_project_csr;
1554 RAISE NO_DATA_FOUND;
1555 Else
1556 close l_project_csr;
1557 End if;
1558
1559 --Added for bug 3617393
1560 OPEN get_template_flag ( l_project_id );
1561 FETCH get_template_flag INTO l_template_flag;
1562 CLOSE get_template_flag;
1563 --Added for bug 3617393
1564
1565 -- Fix for Bug # 4513291. Moved this up before appending x_err_stack -- old_stack := x_err_stack;
1566
1567 -- 4201927
1568 IF x_bulk_flag = 'N' THEN
1569 pa_task_utils.check_delete_task_ok(
1570 x_task_id => x_task_id,
1571 x_validation_mode => x_validation_mode, -- bug 2947492
1572 x_err_code => x_err_code,
1573 x_err_stage => x_err_stage,
1574 x_err_stack => x_err_stack);
1575
1576 if (x_err_code <> 0) then
1577 --Added for bug 3617393
1578 rollback to delete_task;
1579 --End bug 3617393
1580 return;
1581 end if;
1582 END IF;
1583
1584 ---- start 6629057
1585 Open cc_task_id_csr(x_task_id);
1586 fetch cc_task_id_csr into l_cc_tax_task_id;
1587 if cc_task_id_csr%notfound then
1588 close cc_task_id_csr;
1589 end if;
1590 if(l_cc_tax_task_id = x_task_id) then
1591 update pa_projects_all
1592 set cc_tax_task_id = null
1593 where project_id = l_project_id;
1594 close cc_task_id_csr;
1595 end if;
1596 ---- end 6629057
1597
1598 -- 4201927 end
1599
1600 delete from pa_billing_assignments
1601 where top_task_id = x_task_id
1602 AND project_id = l_project_id;
1603
1604 -- 3693197
1605 -- Commented the whole code and moved the code
1606 -- inside the anonymous block and used bulk approach.
1607 -- In all the below mentioned deletes following select
1608 -- query :
1609 -- SELECT TASK_ID
1610 -- FROM PA_TASKS
1611 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1612 -- START WITH TASK_ID = x_TASK_ID
1613 -- was getting called repeatedly. Instead used the existing
1614 -- cursor task_cur to fetch all the tasks into PLSQL table
1615 -- and used bulk delete.
1616
1617 -- Delete transaction controls
1618 --x_err_stage := 'Delete txn controls for task '|| x_task_id;
1619 --delete from pa_transaction_controls
1620 --where task_id in
1621 --(SELECT TASK_ID
1622 -- FROM PA_TASKS
1623 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1624 -- START WITH TASK_ID = x_TASK_ID)
1625 -- Added to fix Bug # 1190003
1626 --AND project_id = l_project_id;
1627
1628 -- Delete billing assignments
1629 --x_err_stage := 'Delete billing assignmts for task '|| x_task_id;
1630 -- Delete labor multipliers
1631 -- x_err_stage := 'Delete labor multipliers for task '|| x_task_id;
1632 -- delete from pa_labor_multipliers
1633 -- where task_id in
1634 -- (select task_id
1635 -- from pa_tasks
1636 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1637 -- START WITH TASK_ID = x_TASK_ID);
1638 --
1639 -- -- Delete job bill rate overrides
1640 -- x_err_stage := 'Delete job bill rate overrides for task '|| x_task_id;
1641 -- delete from pa_job_bill_rate_overrides
1642 -- where task_id in
1643 -- (select task_id
1644 -- from pa_tasks
1645 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1646 -- START WITH TASK_ID = x_TASK_ID);
1647 --
1648 -- -- Delete job bill title overrides
1649 -- x_err_stage := 'Delete job bill title overrides for task '|| x_task_id;
1650 -- delete from pa_job_bill_rate_overrides
1651 -- where task_id in
1652 -- (select task_id
1653 -- from pa_tasks
1654 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1655 -- START WITH TASK_ID = x_TASK_ID);
1656 --
1657 -- -- Delete job assignment overrides
1658 -- x_err_stage := 'Delete job assignmt overrides for task '|| x_task_id;
1659 -- delete from pa_job_assignment_overrides
1660 -- where task_id in
1661 -- (select task_id
1662 -- from pa_tasks
1663 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1664 -- START WITH TASK_ID = x_TASK_ID);
1665 --
1666 -- -- Delete emp bill rate overrides
1667 -- x_err_stage := 'Delete emp bill rate overrides for task '|| x_task_id;
1668 -- delete from pa_emp_bill_rate_overrides
1669 -- where task_id in
1670 -- (select task_id
1671 -- from pa_tasks
1672 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1673 -- START WITH TASK_ID = x_TASK_ID);
1674 --
1675 -- -- Delete non-labor bill rate overrides
1676 -- x_err_stage := 'Delete nl bill rate overrides for task '|| x_task_id;
1677 -- delete from pa_nl_bill_rate_overrides
1678 -- where task_id in
1679 -- (select task_id
1680 -- from pa_tasks
1681 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1682 -- START WITH TASK_ID = x_TASK_ID);
1683
1684 -- anlee
1685 -- Commenting out for performance bug 2800129
1686 -- Delete compiled multipliers, details of compiled set id
1687 -- x_err_stage := 'Delete compiled multiplier for task '|| x_task_id;
1688 -- delete from pa_compiled_multipliers
1689 -- where ind_compiled_set_id in
1690 -- (select ics.ind_compiled_set_id
1691 -- from pa_ind_compiled_sets ics,
1692 -- pa_ind_rate_sch_revisions rev,
1693 -- pa_ind_rate_schedules sch
1694 -- where ics.ind_rate_sch_revision_id =
1695 -- rev.ind_rate_sch_revision_id
1696 -- and rev.ind_rate_sch_id = sch.ind_rate_sch_id
1697 -- and sch.task_id in
1698 -- (select task_id
1699 -- from pa_tasks
1700 -- connect by prior task_id = parent_task_id
1701 -- start with task_id = x_task_id));
1702
1703 -- anlee
1704 -- Commenting out for performance bug 2800129
1705 -- Delete compiled compiled set
1706 -- x_err_stage := 'Delete compiled sets for task '|| x_task_id;
1707 -- delete from pa_ind_compiled_sets
1708 -- where ind_rate_sch_revision_id in
1709 -- (select rev.ind_rate_sch_revision_id
1710 -- from pa_ind_rate_sch_revisions rev,
1711 -- pa_ind_rate_schedules sch
1712 -- where 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 ind cost multipliers, details of ind rate sch revisions
1722 -- x_err_stage := 'Delete ind cost multiplier for task '|| x_task_id;
1723 -- delete from pa_ind_cost_multipliers
1724 -- where ind_rate_sch_revision_id in
1725 -- (select rev.ind_rate_sch_revision_id
1726 -- from pa_ind_rate_sch_revisions rev, pa_ind_rate_schedules sch
1727 -- where rev.ind_rate_sch_id = sch.ind_rate_sch_id
1728 -- and sch.task_id in
1729 -- (select task_id
1730 -- from pa_tasks
1731 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1732 -- START WITH TASK_ID = x_TASK_ID));
1733
1734 -- anlee
1735 -- Commenting out for performance bug 2800129
1736 -- Delete ind rate sch revisions, details of ind rate schedules
1737 -- x_err_stage := 'Delete ind rate sch revision for task '|| x_task_id;
1738 -- delete from pa_ind_rate_sch_revisions
1739 -- where ind_rate_sch_id in
1740 -- (select ind_rate_sch_id
1741 -- from pa_ind_rate_schedules
1742 -- where task_id in
1743 -- (select task_id
1744 -- from pa_tasks
1745 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1746 -- START WITH TASK_ID = x_TASK_ID));
1747
1748 /*
1749 * Anonymous Block to delete burdening setup starts.
1750 */
1751 declare
1752 cursor task_cur ( l_start_task_id pa_tasks.task_id%TYPE )
1753 is
1754 select task_id
1755 from pa_tasks
1756 connect by prior task_id = parent_task_id
1757 start with task_id = l_start_task_id
1758 ;
1759 cursor sch_cur ( l_task_id IN pa_tasks.task_id%TYPE )
1760 is
1761 select sch.ind_rate_sch_id
1762 from pa_ind_rate_schedules sch
1763 where sch.task_id = l_task_id
1764 ;
1765 cursor rev_cur ( l_ind_rate_sch_id IN pa_ind_rate_schedules_all_bg.ind_rate_sch_id%TYPE )
1766 is
1767 select rev.ind_rate_sch_revision_id
1768 from pa_ind_rate_sch_revisions rev
1769 where rev.ind_rate_sch_id = l_ind_rate_sch_id
1770 ;
1771 cursor ics_cur ( l_ind_rate_sch_revision_id IN pa_ind_rate_sch_revisions.ind_rate_sch_revision_id%TYPE )
1772 is
1773 select ics.ind_compiled_set_id
1774 from pa_ind_compiled_sets ics
1775 where ics.ind_rate_sch_revision_id = l_ind_rate_sch_revision_id
1776 ;
1777 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1778 l_ind_rate_sch_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1779 l_ind_rate_sch_rev_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1780 l_ind_comp_set_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1781 begin
1782
1783 open task_cur ( x_task_id );
1784 fetch task_cur
1785 bulk collect
1786 into l_task_id_tab;
1787 close task_cur;
1788
1789 -- added for bug#3693197
1790 if nvl(l_task_id_tab.LAST,0) >0 then
1791
1792 -- Delete transaction controls
1793 x_err_stage := 'Delete txn controls for task '|| x_task_id;
1794 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1795 delete from pa_transaction_controls
1796 where task_id =l_task_id_tab(i)
1797 and project_id = l_project_id;
1798
1799
1800 -- Delete labor multipliers
1801 x_err_stage := 'Delete labor multipliers for task '|| x_task_id;
1802 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1803 delete from pa_labor_multipliers
1804 where task_id =l_task_id_tab(i) ;
1805
1806 -- Delete job bill rate overrides
1807 x_err_stage := 'Delete job bill rate overrides for task '|| x_task_id;
1808 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1809 delete from pa_job_bill_rate_overrides
1810 where task_id =l_task_id_tab(i) ;
1811
1812 -- Delete job assignment overrides
1813 x_err_stage := 'Delete job assignment overrides for task '|| x_task_id;
1814 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1815 delete from pa_job_assignment_overrides
1816 where task_id =l_task_id_tab(i) ;
1817
1818 -- Delete emp bill rate overrides
1819 x_err_stage := 'Delete emp bill rate overrides for task '|| x_task_id;
1820 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1821 delete from pa_emp_bill_rate_overrides
1822 where task_id =l_task_id_tab(i) ;
1823
1824 -- Delete nl bill rate overrides
1825 x_err_stage := 'Delete nl bill rate overrides for task '|| x_task_id;
1826 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1827 delete from pa_nl_bill_rate_overrides
1828 where task_id =l_task_id_tab(i) ;
1829
1830 -- Delete project asset assignments
1831 x_err_stage := 'Delete project asset assignments for task '|| x_task_id;
1832 forall i in l_task_id_tab.FIRST..l_task_id_tab.LAST
1833 delete from pa_project_asset_assignments
1834 where task_id =l_task_id_tab(i) ;
1835
1836 end if ;
1837
1838
1839 for i in 1 .. l_task_id_tab.count
1840 loop
1841 open sch_cur ( l_task_id_tab (i) );
1842 fetch sch_cur
1843 bulk collect
1844 into l_ind_rate_sch_id_tab;
1845 close sch_cur;
1846
1847 for i in 1 .. l_ind_rate_sch_id_tab.count
1848 loop
1849 open rev_cur ( l_ind_rate_sch_id_tab (i) );
1850 fetch rev_cur
1851 bulk collect
1852 into l_ind_rate_sch_rev_id_tab;
1853 close rev_cur;
1854
1855 for i in 1 .. l_ind_rate_sch_rev_id_tab.count
1856 loop
1857 open ics_cur ( l_ind_rate_sch_rev_id_tab (i) );
1858 fetch ics_cur
1859 bulk collect
1860 into l_ind_comp_set_id_tab;
1861 close ics_cur;
1862
1863 if ( l_ind_comp_set_id_tab.count > 0 )
1864 then
1865 forall i in l_ind_comp_set_id_tab.first .. l_ind_comp_set_id_tab.last
1866 delete
1867 from pa_compiled_multipliers comp_mult
1868 where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
1869 ;
1870 forall i in l_ind_comp_set_id_tab.first .. l_ind_comp_set_id_tab.last
1871 delete
1872 from pa_ind_compiled_sets ics
1873 where ind_compiled_set_id = l_ind_comp_set_id_tab(i)
1874 ;
1875 end if;
1876 end loop;
1877
1878 if ( l_ind_rate_sch_rev_id_tab.count > 0 )
1879 then
1880 forall i in l_ind_rate_sch_rev_id_tab.first .. l_ind_rate_sch_rev_id_tab.last
1881 delete
1882 from pa_ind_cost_multipliers icm
1883 where icm.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
1884 ;
1885 forall i in l_ind_rate_sch_rev_id_tab.first .. l_ind_rate_sch_rev_id_tab.last
1886 delete
1887 from pa_ind_rate_sch_revisions rev
1888 where rev.ind_rate_sch_revision_id = l_ind_rate_sch_rev_id_tab(i)
1889 ;
1890 end if;
1891
1892 end loop; -- schedule
1893
1894 if ( l_ind_rate_sch_id_tab.count > 0 )
1895 then
1896 forall i in l_ind_rate_sch_id_tab.first .. l_ind_rate_sch_id_tab.last
1897 delete
1898 from pa_ind_rate_schedules sch
1899 where sch.ind_rate_sch_id = l_ind_rate_sch_id_tab(i)
1900 ;
1901 end if;
1902
1903 end loop; -- task
1904 exception
1905 when others
1906 then
1907 x_err_code := SQLCODE;
1908 rollback to delete_task;
1909 return;
1910 end; -- end of anonymous block to delete burdening setup.
1911 /* New code to delete burdening setup ends **/
1912
1913 -- Delete project asset assigments
1914 -- 3693197 : Commented and moved above in the anonymous block
1915
1916 --x_err_stage := 'Delete proj asset assignmt for task '|| x_task_id;
1917 -- delete from pa_project_asset_assignments
1918 -- where task_id in
1919 -- (select task_id
1920 -- from pa_tasks
1921 -- CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1922 -- START WITH TASK_ID = x_TASK_ID);
1923
1924 --Retention Changes --Ansari bug 2362168
1925 PA_RETENTION_UTIL.delete_retention_rules(
1926 p_project_id => l_project_id,
1927 p_task_id => x_TASK_ID,
1928 x_return_status => x_return_status,
1929 x_msg_count => x_msg_count,
1930 x_msg_data => x_msg_data );
1931
1932 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1933 --Added for bug 3617393
1934 x_err_code := 150;
1935 x_err_stack := x_err_stack || '->delete_retention_rules: '|| l_project_id;
1936 IF l_template_flag = 'Y' THEN
1937 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
1938 ELSE
1939 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
1940 END IF;
1941 ROLLBACK TO delete_task ;
1942 --End bug 3617393
1943 RETURN;
1944 END IF;
1945
1946 --Retention Changes --Ansari
1947
1948
1949 -- Delete percent complete .Need to pass project id
1950 -- since that is the leading key in the index
1951
1952 x_err_stage := 'Delete task percent complete ';
1953 delete from pa_percent_completes
1954 where project_id = l_project_id
1955 and task_id in
1956 (select task_id
1957 from pa_tasks
1958 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1959 START WITH TASK_ID = x_TASK_ID);
1960
1961 -- hsiu added.
1962 -- 30 Mar 2001. For Project Contracts.
1963 -- Delete project structure relationship
1964 x_err_stage := 'Delete project structure relationship for task ';
1965 PA_PROJ_STRUCTURE_PUB.DELETE_RELATIONSHIP(
1966 p_api_version => 1.0,
1967 p_init_msg_list => FND_API.G_FALSE,
1968 p_commit => FND_API.G_TRUE,
1969 p_validate_only => FND_API.G_FALSE,
1970 p_debug_mode => 'N',
1971 p_task_id => x_TASK_ID,
1972 p_project_id => null,
1973 x_return_status => x_return_status,
1974 x_msg_count => x_msg_count,
1975 x_msg_data => x_msg_data
1976 );
1977 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1978 --Added for bug 3617393
1979 x_err_code := 160;
1980 x_err_stack := x_err_stack || '->DELETE_RELATIONSHIP: '|| l_project_id;
1981 IF l_template_flag = 'Y' THEN
1982 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_TEMPLATE');
1983 ELSE
1984 x_err_stage := pa_project_core.get_message_from_stack( 'PA_CANT_DELETE_PROJECT');
1985 END IF;
1986 ROLLBACK TO delete_task ;
1987 --End bug 3617393
1988 return;
1989 END IF;
1990 -- end delete project structure relationship
1991
1992 -- Delete task
1993 x_err_stage := 'Delete any task in the subtree of task '|| x_task_id;
1994 delete from pa_tasks
1995 where task_id in
1996 (select task_id
1997 from pa_tasks
1998 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1999 START WITH TASK_ID = x_TASK_ID);
2000
2001 -- get parent task id
2002 x_err_stage := 'get parent task id for task '|| x_task_id;
2003 x_parent_task_id := pa_task_utils.get_parent_task_id(x_task_id);
2004
2005 if ( x_parent_task_id < 0 ) then -- Oracle error
2006 x_err_code := x_parent_task_id;
2007 return;
2008 end if;
2009
2010 if (x_parent_task_id is not null ) then
2011 -- Check if task is last child
2012 x_err_stage := 'check last child for '|| x_task_id;
2013 status_code := pa_task_utils.check_last_child(x_task_id);
2014
2015 if ( status_code = 1 ) then
2016 -- set parent task's chargeable_flag to 'Y
2017 x_err_stage := 'update parent task chargeable flag';
2018
2019 update pa_tasks
2020 set chargeable_flag = 'Y'
2021 where task_id = x_parent_task_id;
2022
2023 elsif ( status_code < 0 ) then
2024 x_err_code := status_code;
2025 return;
2026 end if;
2027 end if;
2028
2029 x_err_stack := old_stack;
2030
2031 exception
2032 when others then
2033 x_err_code := SQLCODE;
2034 rollback to delete_task;
2035 return;
2036 end delete_task;
2037
2038 --
2039 -- PROCEDURE
2040 -- delete_project_type
2041 --
2042 -- HISTORY
2043 -- 01-NOV-02 Mansari Created
2044 --
2045 procedure delete_project_type (
2046 x_project_type_id IN number
2047 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2048 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2049 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2050 )
2051 is
2052
2053 l_return_status varchar2(1);
2054 begin
2055
2056 SAVEPOINT delete_project_type;
2057
2058 x_return_status := FND_API.G_RET_STS_SUCCESS;
2059
2060 --No need to call this API here
2061 /* PA_PROJECT_UTILS.check_delete_project_type_ok(
2062 p_project_type_id => x_project_type_id
2063 ,x_return_status => x_return_status
2064 ,x_error_message_code => x_msg_data
2065 );
2066
2067 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2068 THEN
2069 RETURN;
2070 END IF;
2071 */
2072
2073 -- Delete project type
2074 delete pa_project_types_all --bug 4584792
2075 where project_type_id = x_project_type_id;
2076
2077 exception
2078 when others then
2079 x_return_status := FND_API.G_RET_STS_SUCCESS;
2080 rollback to delete_project;
2081 return;
2082 end delete_project_type;
2083
2084 --
2085 -- PROCEDURE
2086 -- delete_class_category
2087 --
2088 -- HISTORY
2089 -- 01-NOV-02 Mansari Created
2090 --
2091 procedure delete_class_category (
2092 x_class_category IN VARCHAR2
2093 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2094 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2095 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2096 )
2097 is
2098
2099 l_return_status varchar2(1);
2100 begin
2101
2102 SAVEPOINT delete_class_category;
2103
2104 x_return_status := FND_API.G_RET_STS_SUCCESS;
2105
2106 -- Delete class_category
2107 delete pa_class_categories
2108 where class_category = x_class_category
2109 ;
2110
2111 exception
2112 when others then
2113 x_return_status := FND_API.G_RET_STS_SUCCESS;
2114 rollback to delete_class_category;
2115 return;
2116 end delete_class_category;
2117
2118 --
2119 -- PROCEDURE
2120 -- delete_class_code
2121 --
2122 -- HISTORY
2123 -- 01-NOV-02 Mansari Created
2124 --
2125 procedure delete_class_code (
2126 x_class_category IN VARCHAR2
2127 , x_class_code IN VARCHAR2
2128 , x_msg_count OUT NOCOPY number --File.Sql.39 bug 4440895
2129 , x_msg_data OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2130 , x_return_status OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2131 )
2132 is
2133
2134 l_return_status varchar2(1);
2135 begin
2136
2137 SAVEPOINT delete_class_code;
2138
2139 x_return_status := FND_API.G_RET_STS_SUCCESS;
2140
2141 -- Delete class_category
2142 delete pa_class_codes
2143 where class_category = x_class_category
2144 and class_code = x_class_code
2145 ;
2146
2147 exception
2148 when others then
2149 x_return_status := FND_API.G_RET_STS_SUCCESS;
2150 rollback to delete_class_code;
2151 return;
2152 end delete_class_code;
2153
2154 end PA_PROJECT_CORE;