[Home] [Help]
PACKAGE BODY: APPS.PA_XC_PROJECT_PUB
Source
1 PACKAGE BODY PA_XC_PROJECT_PUB AS
2 /*$Header: PAXCPR1B.pls 120.7.12010000.2 2008/08/22 11:47:52 rballamu ship $*/
3
4 --
5 --Name: import_task
6 --Type: Procedure
7 --Description: This procedure can be used to upload task information
8 -- into Global memory table.
9 --
10 --Called subprograms: Nil
11 --
12 --
13 --
14 --History:
15 -- 31-MAR-2000 - Sakthi - Created
16 -- 03-APR-2003 - Amit - Bug 2873691 - In import_task, in the cursor
17 -- get_parent_id_csr, added a join of parent_structure_version_id
18 -- 09-Feb-2004 - Sulkumar Bug 3302732: Added functions
19 -- generate_new_task_reference
20 -- check_ref_unique
21 -- For Procedure fetch_task_idchanged parameter p_task_index type to VARCHAR2
22 -- from NUMBER. Changed the logic for populating task reference. It is now
23 -- used as VARCHAR2 instead of number. All changes are tagged by bug no.3302732
24 -- 22-Jul-2004 - dthakker 3693934 Changed pa_proj_elements_csr cursor for performance fix
25 -- Changed pa_schedule_dates_csr cursor for performance fix
26 -- Commented l_get_working_version_csr existing cursor definition and added new definition
27 -- for the same
28 -- 23-JUL-04 adarora Bug 3627124 :
29 -- 3696234 : Added the parameter, p_pass_entire_structure in the procedure call to
30 -- pa_project_pub.execute_update_project in the procedure Import_project.
31 -- 15-MAR-2005 -- adarora Bug 3601700:Modified check_ref_unique to handle split case.
32 -- 23-May-2006 sliburd for amksingh Bug 5233777 : Added new parameter p_resp_appl_id in import_project
33 --
34 -- 09-OCT-2006 Ram Namburi Bug 5465108: Added the parameter p_long_task_name in the procedure
35 -- call to PA_PROJECT_PUB.load_task
36 -- 22-AUG-2008 rballamu Bug 7245488: Passed Schedule start/finish dates to PA_PROJECT_PUB.load_task.
37
38 PROCEDURE import_task
39 ( p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
40 ,p_task_reference IN PA_VC_1000_25
41 ,p_task_name IN PA_VC_1000_150
42 ,p_task_start_date IN PA_VC_1000_10
43 ,p_task_end_date IN PA_VC_1000_10
44 ,p_parent_task_reference IN PA_VC_1000_25
45 ,p_task_number IN PA_VC_1000_25
46 ,p_wbs_level IN PA_NUM_1000_NUM
47 ,p_milestone IN PA_VC_1000_150
48 ,p_duration IN PA_VC_1000_150
49 ,p_duration_unit IN PA_VC_1000_150
50 ,p_early_start_date IN PA_VC_1000_10
51 ,p_early_finish_date IN PA_VC_1000_10
52 ,p_late_start_date IN PA_VC_1000_10
53 ,p_late_finish_date IN PA_VC_1000_10
54 ,p_display_seq IN PA_VC_1000_150
55 ,p_login_user_name IN PA_VC_1000_150:= PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
56 ,p_critical_path IN PA_VC_1000_150
57 ,p_sub_project_id IN PA_VC_1000_150
58 ,p_attribute7 IN PA_VC_1000_150
59 ,p_attribute8 IN PA_VC_1000_150
60 ,p_attribute9 IN PA_VC_1000_150
61 ,p_attribute10 IN PA_VC_1000_150
62 ,p_progress_report IN PA_VC_1000_4000
63 ,p_progress_status IN PA_VC_1000_150
64 ,p_progress_comments IN PA_VC_1000_150
65 ,p_progress_asof_date IN PA_VC_1000_10
66 ,p_predecessors IN PA_VC_1000_2000
67 ,p_language IN VARCHAR2 default 'US'
68 ,p_delimiter IN VARCHAR2 default ','
69 ,p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
70 ,p_calling_mode IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
71 )
72
73 IS
74
75 l_api_name CONSTANT VARCHAR2(30):='Import_Task';
76 l_return_status VARCHAR2(1);
77 l_responsibility_id NUMBER;
78 i NUMBER := 0;
79 l_task_reference NUMBER := 0;
80 l_count NUMBER := 0;
81
82 l_task_manager_flag VARCHAR2(1) := 'Y';
83 l_early_start_date DATE := NULL;
84 l_early_finish_date DATE := NULL;
85 l_late_start_date DATE := NULL;
86 l_late_finish_date DATE := NULL;
87
88 l_task_start_date DATE := NULL;
89 l_task_finish_date DATE := NULL;
90 l_sched_start_date DATE; /* 7245488 */
91 l_sched_fin_date DATE; /* 7245488 */
92 l_long_task_name pa_tasks.long_task_name%TYPE := null; -- Added for bug 5465108
93
94 l_task_id NUMBER :=NULL;
95 l_task_manager_id NUMBER :=NULL;
96
97 l_milestone VARCHAR2(1) := 'N';
98 l_critical_path VARCHAR2(1) := 'N';
99
100 CURSOR l_get_employee_id (l_login_user_name VARCHAR2)
101 IS
102 SELECT EMPLOYEE_ID
103 FROM FND_USER
104 WHERE UPPER(USER_NAME) = upper(l_login_user_name);
105
106
107 CURSOR l_get_temp_record (c_project_id NUMBER)
108 IS
109 SELECT PROJECT_ID
110 FROM PA_TEMP_IMPORT_TASKS
111 WHERE PROJECT_ID = c_project_id;
112
113 -- Retrieve tasks from PA_PROJ_ELEMENTS table
114 CURSOR pa_proj_elements_csr(c_structure_version_id NUMBER, c_project_id NUMBER)
115 IS
116 SELECT ppe.proj_element_id, ppe.name, ppe.element_number, ppe.description, ppe.pm_source_reference, ppe.manager_person_id, ppe.carrying_out_organization_id
117 FROM PA_PROJ_ELEMENTS ppe, PA_PROJ_ELEMENT_VERSIONS ppev
118 WHERE ppe.project_id = c_project_id
119 AND ppe.project_id = ppev.project_id -- 3693934 added for peformance fix
120 AND ppe.object_type = 'PA_TASKS'
121 AND ppe.proj_element_id = ppev.proj_element_id
122 AND ppev.parent_structure_version_id = c_structure_version_id
123 AND ppev.financial_task_flag = 'Y'
124 ORDER BY ppev.display_sequence;
125
126 l_proj_elements_rec pa_proj_elements_csr%ROWTYPE;
127
128 -- Retrieve task Scheduled Start and Scheduled End Date from PA_PROJ_ELEM_VER_SCHEDULE table
129 CURSOR pa_schedule_dates_csr (c_structure_version_id NUMBER, c_proj_element_id NUMBER)
130 IS
131 SELECT ppevs.scheduled_start_date, ppevs.scheduled_finish_date
132 FROM PA_PROJ_ELEMENT_VERSIONS ppev, PA_PROJ_ELEM_VER_SCHEDULE ppevs
133 WHERE ppev.element_version_id = ppevs.element_version_id
134 AND ppev.project_id = ppevs.project_id -- 3693934 added for peformance fix
135 AND ppev.object_type = 'PA_TASKS'
136 AND ppev.proj_element_id = c_proj_element_id
137 AND ppev.parent_structure_version_id = c_structure_version_id;
138
139 -- Retrieve task Start and End Date from PA_TASKS table
140 CURSOR pa_tasks_dates_csr (c_task_id NUMBER)
141 IS
142 SELECT start_date, completion_date, long_task_name -- Modified for bug 5465108
143 FROM pa_tasks
144 WHERE task_id = c_task_id;
145
146 CURSOR get_parent_id_csr (c_proj_element_id NUMBER)
147 IS
148 SELECT ppev2.proj_element_id
149 FROM PA_OBJECT_RELATIONSHIPS por,
150 PA_PROJ_ELEMENT_VERSIONS ppev,
151 PA_PROJ_ELEMENT_VERSIONS ppev2
152 WHERE por.relationship_type = 'S'
153 AND por.object_id_to1 = ppev.element_version_id
154 AND por.object_type_from = 'PA_TASKS'
155 AND ppev.proj_element_id = c_proj_element_id
156 AND ppev.parent_structure_version_id = p_structure_version_id -- Bug 2873691
157 AND ppev2.element_version_id = por.object_id_from1;
158
159 l_pa_parent_task_id NUMBER;
160 l_pm_parent_task_reference VARCHAR2(25);
161 l_source_ref_count NUMBER;
162 l_pm_source_reference VARCHAR2(25);
163
164 TYPE SourceRefs IS TABLE OF VARCHAR2(25)
165 INDEX BY BINARY_INTEGER;
166
167 l_source_ref_table SourceRefs;
168
169 --hsiu added
170 --Bug 3302732: Commenting this cursor.
171 /*cursor l_pm_source_ref(c_structure_version_id NUMBER, c_project_id NUMBER) IS
172 select min(to_Number(ppe.pm_source_reference))
173 FROM PA_PROJ_ELEMENTS ppe, PA_PROJ_ELEMENT_VERSIONS ppev
174 WHERE ppe.project_id = c_project_id
175 AND ppe.object_type = 'PA_TASKS'
176 AND ppe.proj_element_id = ppev.proj_element_id
177 AND ppev.parent_structure_version_id = c_structure_version_id; */
178
179 --hyau added for debugging unhandled exception stage
180 l_stage VARCHAR2(250);
181 l_counter NUMBER;
182
183 --hsiu added for initializing pm_source_reference
184 CURSOR get_pa_task(c_proj_element_id NUMBER) IS
185 select task_id from pa_tasks
186 where task_id = c_proj_element_id;
187 l_exist_pa_task_id NUMBER;
188
189 l_debug_mode VARCHAR2(1); -- Fix for Bug # 4513291.
190
191 BEGIN
192
193 -- Fix for Bug # 4513291. Added Debug write calls
194 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
195 IF l_debug_mode = 'Y' THEN
196 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_TASK', x_Msg => 'PA_XC_PROJECT_PUB.IMPORT_TASK Start : Passed Parameters :', x_Log_Level=> 3);
197 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_TASK', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
198 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_TASK', x_Msg => 'p_structure_version_id='||p_structure_version_id, x_Log_Level=> 3);
199 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_TASK', x_Msg => 'p_calling_mode='||p_calling_mode, x_Log_Level=> 3);
200 END IF;
201
202 l_stage := '1';
203
204 if p_calling_mode = 'PUBLISH' then
205
206 --bug 2738747 : The global task table should be initialized before adding any tasks in the pl/sql table.
207 --From project connect after receiveing a project plan we can always add tasks and subsequently publish
208 --the structure. Project connect will load the tasks to update(exisiting tasks) and add(new tasks) in
209 --pl/sql table and then calls the publishing API. The publish_structure api calls this API to load tasks
210 --in the same pl/sql table. If we dont flush out the tasks already loaded by project connect update
211 --then this api will load the same tasks again and publish_structure will fail.
212
213 pa_project_pub.G_tasks_in_tbl.delete;
214
215 --dbms_output.put_line('Inside publish');
216
217 -- Delete tasks from the temp database table
218
219 OPEN l_get_temp_record (p_project_id);
220 FETCH l_get_temp_record INTO l_count;
221
222 IF l_get_temp_record%FOUND THEN
223 CLOSE l_get_temp_record;
224 delete from PA_TEMP_IMPORT_TASKS where project_id = p_project_id;
225 ELSE
226 CLOSE l_get_temp_record;
227 END IF;
228
229 l_stage := '2';
230
231 -- Need to generate source reference numbers for those tasks that dont have one
232 -- hsiu added
233
234 -- Bug 3302732: Commenting this logic to get task_reference
235
236 /* open l_pm_source_ref(p_structure_version_id, p_project_id);
237 FETCH l_pm_source_ref INTO l_source_ref_count;
238 IF l_pm_source_ref%NOTFOUND THEN
239 l_source_ref_count := -1;
240 ELSE
241 IF (l_source_ref_count IS NULL) THEN
242 l_source_ref_count := -1;
243 ELSE
244 l_source_ref_count := l_source_ref_count - 1;
245 END IF;
246 END IF;
247 close l_pm_source_ref; */
248
249 l_stage := '3';
250 l_counter :=0;
251
252 -- Fetch task info from PA_PROJ_ELEMENTS table
253
254 OPEN PA_PROJ_ELEMENTS_CSR(p_structure_version_id, p_project_id);
255 LOOP
256
257 FETCH PA_PROJ_ELEMENTS_CSR INTO l_proj_elements_rec;
258 EXIT WHEN PA_PROJ_ELEMENTS_CSR%NOTFOUND;
259
260 l_counter := l_counter+1;
261 l_stage := '3.1 Loop '||to_char(l_counter);
262
263 if l_proj_elements_rec.pm_source_reference is null then
264
265 -- if the task has no source reference, create one
266 -- Bug 3302732 l_pm_source_reference := l_source_ref_count;
267
268 -- Bug 3302732: Now a new task reference is generated via new function generate_new_task_reference
269 l_pm_source_reference := generate_new_task_reference(p_project_id, l_proj_elements_rec.proj_element_id);
270
271 --hsiu added for initializing tasks
272
273 OPEN get_pa_task(l_proj_elements_rec.proj_element_id);
274 FETCH get_pa_task into l_exist_pa_task_id;
275
276 IF (get_pa_task%FOUND) THEN
277
278 update pa_tasks
279 set pm_task_reference = l_pm_source_reference -- Bug 3302732 l_source_ref_count
280 where task_id = l_exist_pa_task_id;
281
282 update pa_proj_elements
283 set pm_source_reference = l_pm_source_reference -- Bug 3302732 l_source_ref_count
284 where proj_element_id = l_exist_pa_task_id;
285
286 END IF;
287 CLOSE get_pa_task;
288
289 l_source_ref_table(l_proj_elements_rec.proj_element_id) := l_pm_source_reference;
290 -- l_source_ref_count := l_source_ref_count - 1; -- Bug 3302732 : Commented this.
291 else
292 l_pm_source_reference := l_proj_elements_rec.pm_source_reference;
293 l_source_ref_table(l_proj_elements_rec.proj_element_id) := l_proj_elements_rec.pm_source_reference;
294 end if;
295
296 l_stage := '3.2';
297
298 -- fetch the task id from PA_TASKS based on the source reference
299
300 PA_XC_PROJECT_PUB.fetch_task_id
301 ( p_task_index => l_pm_source_reference -- Bug 3302732 to_number(l_pm_source_reference)
302 ,p_project_id => p_project_id
303 ,p_pm_task_reference => l_pm_source_reference
304 ,x_task_id => l_task_id);
305
306 l_stage := '3.3';
307
308 -- get the task id of the parent of this task
309
310 OPEN get_parent_id_csr(l_proj_elements_rec.proj_element_id);
311 FETCH get_parent_id_csr into l_pa_parent_task_id;
312 --hsiu
313 IF get_parent_id_csr%NOTFOUND THEN
314 l_pa_parent_task_id := NULL;
315 END IF;
316 CLOSE get_parent_id_csr;
317
318 l_stage := '3.4';
319
320 -- get the source reference of the parent task
321 if(l_pa_parent_task_id is not null) then
322 l_pm_parent_task_reference := l_source_ref_table(l_pa_parent_task_id);
323 else
324 l_pm_parent_task_reference := null;
325 end if;
326
327 l_stage := '3.5';
328
329 OPEN pa_tasks_dates_csr (l_proj_elements_rec.proj_element_id);
330 FETCH pa_tasks_dates_csr INTO l_task_start_date, l_task_finish_date, l_long_task_name; -- Modified for bug 5465108
331 --bug 2858227 see update *** AAKASH 03/19/03 10:09 pm *** senario 3
332 IF pa_tasks_dates_csr%NOTFOUND
333 THEN
334 --Commented and replpaced following for BUG 4278979, rtarway
335 --l_task_start_date := null;
336 --l_task_finish_date := null;
337
338 l_task_start_date := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
339 l_task_finish_date := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
340 l_long_task_name := null; -- Added for bug 5465108
341
342
343 END IF;
344 --bug 2858227
345 CLOSE pa_tasks_dates_csr;
346
347 --end if;
348
349 l_stage := '3.6';
350
351 -- populate the temp table
352 -- note that we are populating with the proj_element_id from PA_PROJ_ELEMENTS,
353 -- not the task_id from fetch_task_id
354 -- this is because fetch_task_id will only return those task ids in PA_TASKS, but
355 -- we need the proj_element_id to sync up with the task ids created by AMG later
356
357 INSERT INTO PA_TEMP_IMPORT_TASKS
358 ( PROJECT_ID
359 ,TASK_REFERENCE
360 ,TASK_ID)
361 VALUES
362 ( p_project_id
363 ,l_pm_source_reference
364 ,l_proj_elements_rec.proj_element_id);
365
366 l_stage := '3.7';
367
368 OPEN pa_schedule_dates_csr(p_structure_version_id,l_proj_elements_rec.proj_element_id); /* 7245488 */
369 FETCH pa_schedule_dates_csr INTO l_sched_start_date,l_sched_fin_date;
370 CLOSE pa_schedule_dates_csr;
371
372 PA_PROJECT_PUB.load_task (
373 p_api_version_number => G_API_VERSION_NUMBER
374 ,p_return_status => l_return_status
375 ,p_pm_task_reference => l_pm_source_reference
376 ,p_pa_task_id => l_task_id
377 ,p_task_name => l_proj_elements_rec.name
378 ,p_long_task_name => nvl(l_long_task_name, l_proj_elements_rec.name) -- Added for bug 5465108
379 ,p_pa_task_number => l_proj_elements_rec.element_number
380 ,p_task_description => l_proj_elements_rec.description
381 ,p_task_start_date => l_task_start_date
382 ,p_task_completion_date => l_task_finish_date
383 ,p_scheduled_start_date => l_sched_start_date --7245488
384 ,p_scheduled_finish_date => l_sched_fin_date --7245488
385 ,p_pm_parent_task_reference => l_pm_parent_task_reference
386 ,p_pa_parent_task_id => null
387 ,p_carrying_out_organization_id => l_proj_elements_rec.carrying_out_organization_id
388 ,p_task_manager_person_id => l_proj_elements_rec.manager_person_id
389 ,p_attribute1 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
390 ,p_attribute2 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
391 ,p_attribute3 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
392 ,p_attribute4 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
393 ,p_attribute5 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
394 ,p_attribute6 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
395 ,p_attribute7 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
396 ,p_attribute8 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
397 ,p_attribute9 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
398 ,p_attribute10 => PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
399 );
400
401 l_stage := '3.8';
402
403 END LOOP;
404 --hyau
405
406 CLOSE PA_PROJ_ELEMENTS_CSR;
407
408 l_stage := '4';
409
410 end if; -- calling_mode
411
412 l_stage := '6';
413
414 EXCEPTION
415 WHEN OTHERS THEN
416
417 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_XC_PROJECT_PUB',
418 p_procedure_name => 'IMPORT_TASK',
419 p_error_text => SUBSTRB(SQLERRM,1,240)||' - stage '||l_stage);
420 raise;
421
422 END Import_task;
423
424 --
425 -- ================================================
426 --
427 --
428 --Name: import_project
429 --Type: Procedure
430 --Description: This procedure can be used to update a project on basis
431 -- of an existing project or template.
432 --
433 --Called subprograms: Nil
434 --
435 --
436 --
437 --History:
438 -- 31-MAR-2000 - Sakthi - Created
439 --
440
441
442 -- Procedure Import Project.
443
444 PROCEDURE import_project
445 ( p_user_id IN NUMBER
446 ,p_commit IN VARCHAR2 default 'N'
447 ,p_debug_mode IN VARCHAR2 default 'N'
448 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
449 ,p_project_mpx_start_date IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
450 ,p_project_mpx_end_date IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
451 ,p_task_mgr_override IN VARCHAR2 default 'N'
452 ,p_task_pgs_override IN VARCHAR2 default 'N'
453 ,p_process_id IN NUMBER default -1
454 ,p_language IN VARCHAR2 default 'US'
455 ,p_delimiter IN VARCHAR2 default ','
456 ,p_responsibility_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
457 ,p_structure_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
458 ,p_structure_version_id IN NUMBER
459 ,p_calling_mode IN VARCHAR2
460 ,p_resp_appl_id IN NUMBER default 275 -- 5233777
461 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
462 ,x_msg_data IN OUT NOCOPY PA_VC_1000_2000 --File.Sql.39 bug 4440895
463 ,x_return_status IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
464
465 IS
466
467 l_api_name CONSTANT VARCHAR2(30):='Import_Project';
468 l_project_name VARCHAR2(30);
469 l_pm_product_code CONSTANT VARCHAR2(30):='MSPROJECT';
470 l_return_status VARCHAR2(1) ;
471 l_workflow_started VARCHAR2(1) := 'N' ;
472 l_responsibility_id NUMBER;
473 l_task_reference VARCHAR2(25);
474 l_task_index NUMBER;
475 l_task_id NUMBER;
476 l_out_project_id NUMBER;
477 l_out_task_id NUMBER;
478 l_pm_product_code2 VARCHAR2(30);
479
480 l_task_return_status VARCHAR2(1);
481 l_pm_task_reference VARCHAR2(25);
482 l_project_reference VARCHAR2(25);
483 l_project_mpx_start_date DATE;
484 l_project_mpx_end_date DATE;
485
486 l_dummy pa_projects_all.name%TYPE;
487
488 l_err_code NUMBER := 0;
489 l_err_stage VARCHAR2(2000);
490 l_err_stack VARCHAR2(2000);
491
492 l_msg_count NUMBER := 0;
493 l_msg_data PA_VC_1000_2000 := PA_VC_1000_2000(1);
494
495 p_msg_count NUMBER;
496 p_msg_data VARCHAR2(2000);
497 l_text VARCHAR2(2000);
498 API_ERROR EXCEPTION;
499
500 l_task_start_date PA_VC_1000_10 := PA_VC_1000_10(1);
501 l_task_end_date PA_VC_1000_10 := PA_VC_1000_10(1);
502
503
504 -- needed to get the field values associated to a particular Task_Id
505 CURSOR l_get_task_csr (p_project_id NUMBER)
506 IS
507 SELECT task_id
508 FROM pa_tasks
509 WHERE project_id = p_project_id;
510
511 -- needed to get the field values associated to a particular Project_id
512 CURSOR l_get_project_csr (p_project_id NUMBER)
513 IS
514 SELECT name
515 FROM pa_projects
516 WHERE project_id = p_project_id;
517
518 -- Fix for Bug # 4513291. Commented below cusror and added new
519 /*
520 -- needed to get the the set of tasks to be deleted
521 CURSOR l_get_temp_task_csr(l_project_id NUMBER)
522 IS
523 SELECT PM_TASK_REFERENCE, task_id
524 FROM pa_tasks
525 WHERE project_id = l_project_id
526 MINUS
527 SELECT TASK_REFERENCE, task_id
528 FROM pa_temp_import_tasks
529 WHERE project_id = l_project_id
530 ORDER BY 1;
531 */
532
533 CURSOR l_get_temp_task_csr(l_project_id NUMBER)
534 IS
535 SELECT PM_TASK_REFERENCE, task_id
536 FROM pa_tasks ttask
537 WHERE project_id = l_project_id
538 AND task_id not in
539 (
540 SELECT task_id
541 FROM pa_temp_import_tasks
542 WHERE project_id = l_project_id
543 AND task_id = ttask.task_id
544 )
545 ORDER BY ttask.wbs_level desc;
546
547 l_msg_index_out NUMBER;
548 l_data VARCHAR2(2000);
549 temp_msg_data VARCHAR2(2000);
550 l_rowid VARCHAR2(100);
551
552 -- needed to lock the project so users cannot import plans for the same
553 -- project simultaneously
554 CURSOR lock_project_record (c_project_id NUMBER)
555 IS
556 SELECT name
557 FROM PA_PROJECTS_ALL
558 WHERE project_id = c_project_id
559 FOR UPDATE of name NOWAIT;
560
561 l_org_id NUMBER;
562
563 -- checks whether the structure is a financial structure type
564 CURSOR l_check_financial_purpose_csr(c_structure_id NUMBER)
565 IS
566 SELECT 'Y'
567 FROM PA_PROJ_STRUCTURE_TYPES ppst,
568 PA_STRUCTURE_TYPES pst
569 WHERE ppst.proj_element_id = c_structure_id
570 AND ppst.structure_type_id = pst.structure_type_id
571 AND (pst.structure_type = 'FINANCIAL');
572
573 -- checks whether the structure is a workplan structure type
574 CURSOR l_check_workplan_purpose_csr(c_structure_id NUMBER)
575 IS
576 SELECT 'Y'
577 FROM PA_PROJ_STRUCTURE_TYPES ppst,
578 PA_STRUCTURE_TYPES pst
579 WHERE ppst.proj_element_id = c_structure_id
580 AND ppst.structure_type_id = pst.structure_type_id
581 AND pst.structure_type = 'WORKPLAN';
582
583 -- checks whethere there are any published versions
584 CURSOR l_check_published_csr(c_structure_id NUMBER)
585 IS
586 SELECT 'Y'
587 FROM DUAL
588 WHERE NOT EXISTS
589 (SELECT 'Y'
590 FROM PA_PROJ_ELEM_VER_STRUCTURE
591 WHERE proj_element_id = c_structure_id
592 AND published_date is not null);
593
594 l_temp VARCHAR2(1);
595 l_sync_import boolean;
596
597 -- cursor to get the number of structures in a project
598 CURSOR l_get_structure_count_csr(c_project_id NUMBER)
599 IS
600 SELECT count(proj_element_id)
601 FROM pa_proj_elements
602 WHERE project_id = c_project_id
603 and object_type = 'PA_STRUCTURES'
604 GROUP BY proj_element_id;
605
606 -- cursor to get the number of structures in a project
607 CURSOR l_get_structure_id_csr(c_project_id NUMBER)
608 IS
609 SELECT proj_element_id
610 FROM pa_proj_elements
611 WHERE project_id = c_project_id
612 and object_type = 'PA_STRUCTURES';
613
614 -- 3693934 for performance bug fix commented below cursor definition
615
616 -- get working structure version
617 /*
618 CURSOR l_get_working_version_csr(c_structure_id NUMBER)
619 IS
620 SELECT ppev.element_version_id
621 FROM pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
622 WHERE ppev.proj_element_id = c_structure_id
623 AND ppev.element_version_id = ppevs.element_version_id
624 AND ppevs.published_date is null;
625 */
626
627 -- 3693934 for performance bug fix , channged cursor definiton of the above
628
629 CURSOR l_get_working_version_csr(c_structure_id NUMBER)
630 IS
631 SELECT ppevs.element_version_id
632 FROM pa_proj_elements ppev, pa_proj_elem_ver_structure ppevs
633 WHERE ppev.proj_element_id = c_structure_id
634 AND ppev.project_id = ppevs.project_id
635 AND ppev.proj_element_id = ppevs.proj_element_id
636 AND ppevs.published_date is null;
637
638 -- HY get structure id
639 CURSOR l_get_struct_id_csr(c_structure__version_id NUMBER)
640 IS
641 SELECT proj_element_id
642 FROM pa_proj_element_versions
643 WHERE element_version_id = c_structure__version_id;
644
645 -- HY check for valid project id, structure id, structure version id combination.
646 CURSOR l_check_proj_struct_ver_id_csr(c_project_id NUMBER, c_structure_id NUMBER,
647 c_structure_version_id NUMBER)
648 IS
649 SELECT 'Y'
650 FROM pa_proj_element_versions
651 WHERE project_id = c_project_id
652 and proj_element_id = c_structure_id
653 and element_version_id = c_structure_version_id;
654
655 -- HY check for valid project id, structure id combination.
656 CURSOR l_check_proj_struct_id_csr(c_project_id NUMBER, c_structure_id NUMBER)
657 IS
658 SELECT 'Y'
659 FROM pa_proj_elements
660 WHERE project_id = c_project_id
661 and proj_element_id = c_structure_id;
662
663 -- HY check for valid project id, structure version id combination.
664 CURSOR l_check_proj_ver_id_csr(c_project_id NUMBER,
665 c_structure_version_id NUMBER)
666 IS
667 SELECT 'Y'
668 FROM pa_proj_element_versions
669 WHERE project_id = c_project_id
670 and element_version_id = c_structure_version_id;
671
672 l_project_id NUMBER;
673 l_struct_count NUMBER;
674 l_structure_id NUMBER;
675 l_structure_version_id NUMBER;
676 l_financial_purpose VARCHAR2(1);
677 l_workplan_purpose VARCHAR2(1);
678 l_validate_flag VARCHAR2(1);
679
680 -- 4363092 MOAC Changes, Added cursor to retrieve
681 -- operating unit id of the project
682 CURSOR proj_ou_id_csr
683 IS
684 select org_id from pa_projects_all where project_id = p_project_id;
685
686 BEGIN
687 -- Fix for Bug # 4513291. Added Debug.write calls instead of debug.debug
688 IF p_debug_mode = 'Y' THEN
689 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'PA_XC_PROJECT_PUB.IMPORT_PROJECT Start : Passed Parameters :', x_Log_Level=> 3);
690 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
691 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_project_mpx_start_date='||p_project_mpx_start_date, x_Log_Level=> 3);
692 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_project_mpx_end_date='||p_project_mpx_end_date, x_Log_Level=> 3);
693 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_responsibility_id='||p_responsibility_id, x_Log_Level=> 3);
694 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_structure_version_id='||p_structure_version_id, x_Log_Level=> 3);
695 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_calling_mode='||p_calling_mode, x_Log_Level=> 3);
696 END IF;
697
698 SAVEPOINT import_project;
699 x_return_status := 'S';
700
701 -- HY Check to make sure that the project id, the structure id, and the structure version id passed
702 -- in are a valid combination
703
704 if (p_structure_id is not NULL and p_structure_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and p_structure_id <>0) then
705 NULL;
706 /*
707 -- Both Structure Id and Structure Version Id are not empty.
708 if (p_structure_version_id is not NULL and p_structure_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and p_structure_version_id <>0) then
709 OPEN l_check_proj_struct_ver_id_csr (p_project_id, p_structure_id, p_structure_version_id);
710 FETCH l_check_proj_struct_ver_id_csr INTO l_validate_flag;
711 if l_check_proj_struct_ver_id_csr%NOTFOUND then
712 x_msg_count := x_msg_count + 1;
713 x_msg_data.extend(1);
714 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_INVLD_PROJ_STRUCT_VER');
715 x_return_status := 'E';
716 -- dbms_output.put_line('IMP_PROJECT: Raising ERROR PA_INVLD_PROJ_STRUCT_VER');
717 raise API_ERROR;
718 end if;
719 CLOSE l_check_proj_struct_ver_id_csr;
720
721 else -- Structure Id is not empty, but Structure Version is empty.
722 OPEN l_check_proj_struct_id_csr (p_project_id, p_structure_id);
723 FETCH l_check_proj_struct_id_csr INTO l_validate_flag;
724 if l_check_proj_struct_id_csr%NOTFOUND then
725 x_msg_count := x_msg_count + 1;
726 x_msg_data.extend(1);
727 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_INVLD_PROJ_STRUCT_PR');
728 x_return_status := 'E';
729 -- dbms_output.put_line('IMP_PROJECT: Raising ERROR PA_INVLD_PROJ_STRUCT_PR');
730 raise API_ERROR;
731 end if;
732 CLOSE l_check_proj_struct_id_csr;
733
734 end if;
735 else
736 --Structure ID is empty and Structure Version ID is not empty
737 if (p_structure_version_id is not NULL and p_structure_version_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM and p_structure_version_id <>0) then
738 OPEN l_check_proj_ver_id_csr (p_project_id, p_structure_version_id);
739 FETCH l_check_proj_ver_id_csr INTO l_validate_flag;
740 if l_check_proj_ver_id_csr%NOTFOUND then
741 -- fnd_message.set_name('PA', 'PA_INVLD_PROJ_VER_PR');
742 x_msg_count := x_msg_count + 1;
743 x_msg_data.extend(1);
744 -- fnd_msg_pub.add;
745 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_INVLD_PROJ_VER_PR');
746 -- x_msg_data(x_msg_count) := fnd_message.get;
747 x_return_status := 'E';
748 raise API_ERROR;
749 end if;
750 CLOSE l_check_proj_ver_id_csr;
751
752 end if;
753 end if;
754
755 -- Get structure/structure version id as necessary
756 if p_structure_version_id is NULL OR p_structure_version_id = 0 then
757 if p_structure_id is NULL OR p_structure_version_id = 0 then
758 OPEN l_get_structure_count_csr(p_project_id);
759 FETCH l_get_structure_count_csr INTO l_struct_count;
760
761 if l_struct_count <> 1 then
762 CLOSE l_get_structure_count_csr;
763 -- fnd_message.set_name('PA', 'PA_NO_STRUCTURE_ID');
764 x_msg_count := x_msg_count + 1;
765 x_msg_data.extend(1);
766 -- fnd_msg_pub.add;
767 -- x_msg_data(x_msg_count) := fnd_message.get;
768 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_NO_STRUCTURE_ID');
769 x_return_status := 'E';
770 raise API_ERROR;
771 else
772 OPEN l_get_structure_id_csr(p_project_id);
773 FETCH l_get_structure_id_csr INTO l_structure_id;
774 CLOSE l_get_structure_id_csr;
775 end if;
776 else
777 l_structure_id := p_structure_id;
778 end if;
779
780 OPEN l_check_financial_purpose_csr(l_structure_id);
781 FETCH l_check_financial_purpose_csr INTO l_financial_purpose;
782 if l_check_financial_purpose_csr%NOTFOUND then
783 l_financial_purpose := 'N';
784 end if;
785 CLOSE l_check_financial_purpose_csr;
786
787 OPEN l_check_workplan_purpose_csr(l_structure_id);
788 FETCH l_check_workplan_purpose_csr INTO l_workplan_purpose;
789 if l_check_workplan_purpose_csr%NOTFOUND then
790 l_workplan_purpose := 'N';
791 end if;
792 CLOSE l_check_workplan_purpose_csr;
793
794 if l_financial_purpose = 'Y' and l_workplan_purpose = 'Y' then
795 OPEN l_get_working_version_csr(l_structure_id);
796 FETCH l_get_working_version_csr INTO l_structure_version_id;
797 -- bug fix 2358590: If it does not exist throw an error
798 if l_get_working_version_csr%NOTFOUND then
799 x_msg_count := x_msg_count + 1;
800 x_msg_data.extend(1);
801 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_PS_WP_VERSION_NOT_EXIST');
802 x_return_status := 'E';
803 raise API_ERROR;
804 end if;
805 CLOSE l_get_working_version_csr;
806 elsif l_financial_purpose = 'Y' then
807 OPEN l_get_working_version_csr(l_structure_id);
808 FETCH l_get_working_version_csr INTO l_structure_version_id;
809 CLOSE l_get_working_version_csr;
810 elsif l_workplan_purpose = 'Y' then
811 -- fnd_message.set_name('PA', 'PA_NO_STRUCTURE_VER_ID');
812 x_msg_count := x_msg_count + 1;
813 x_msg_data.extend(1);
814 -- fnd_msg_pub.add;
815 -- x_msg_data(x_msg_count) := fnd_message.get;
816 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_NO_STRUCTURE_VER_ID');
817 x_return_status := 'E';
818 raise API_ERROR;
819 end if;
820 */
821 else
822 l_structure_version_id := p_structure_version_id;
823 --HY Get the Structure ID if it's not passed in.
824 if (p_structure_id is not null or p_structure_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) then
825 l_structure_id := p_structure_id;
826 else
827 OPEN l_get_struct_id_csr (l_structure_version_id);
828 FETCH l_get_struct_id_csr INTO l_structure_id;
829 if l_get_struct_id_csr%NOTFOUND then
830 -- hyau fnd_message.set_name('PA', 'PA_NO_STRUCTURE_VER_ID');
831 x_msg_count := x_msg_count + 1;
832 x_msg_data.extend(1);
833 -- hyau fnd_msg_pub.add;
834 -- hyau x_msg_data(x_msg_count) := fnd_message.get;
835 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_NO_STRUCTURE_VER_ID');
836 x_return_status := 'E';
837 raise API_ERROR;
838 end if;
839 CLOSE l_get_struct_id_csr;
840 end if;
841
842 end if;
843
844 -- To Set global info like responsibility_id and user_id
845
846 IF (p_responsibility_id is NULL) OR (p_responsibility_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
847 -- Fix for Bug # 4513291. There was an old fix done thru Bug 2358887, do not put msgs in stack
848 -- This was wrong as the calling env. except MSP(I guess) does not look at the x_msg_data array.
849 -- Other calling envs. rely on fnd_msg_pub.count_msg, for which we need to add msgs in stack
850
851 IF p_debug_mode = 'Y' THEN
852 pa_debug.write(x_Module=>'PA_XC_PROJECT_PUB.IMPORT_PROJECT', x_Msg => 'p_responsibility_id is not specified', x_Log_Level=> 3);
853 END IF;
854
855 x_msg_count := x_msg_count + 1;
856 x_msg_data.extend(1);
857 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_FUNCTION_SECURITY_ENFORCED');
858 --added for bug 2192242
859 fnd_message.set_name('PA', 'PA_FUNCTION_SECURITY_ENFORCED'); -- Fix for Bug # 4513291. Uncommented and moved below.
860 fnd_msg_pub.add; -- Fix for Bug # 4513291. Uncommented and moved below.
861
862 -- x_msg_data(x_msg_count) := fnd_message.get;
863 x_return_status := 'E';
864 raise API_ERROR;
865 END IF;
866
867 -- 4363092 MOAC Changes, Added below code to retrieve org_id of the project
868 -- and passing it to set_global_info procedure call
869
870 OPEN proj_ou_id_csr;
871 FETCH proj_ou_id_csr into l_org_id;
872 CLOSE proj_ou_id_csr;
873
874 -- 4363092 end
875
876 PA_INTERFACE_UTILS_PUB.Set_Global_Info
877 ( p_api_version_number => G_API_VERSION_NUMBER
878 ,p_responsibility_id => p_responsibility_id
879 ,p_user_id => p_user_id
880 ,p_calling_mode => p_calling_mode --bug 2783845
881 ,p_operating_unit_id => l_org_id -- 4363092 MOAC Changes
882 ,p_resp_appl_id => p_resp_appl_id -- 5233777
883 ,p_msg_count => p_msg_count
884 ,p_msg_data => p_msg_data
885 ,p_return_status => l_return_status);
886 --dbms_output.put_line('set global info: '||l_return_status);
887
888 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893
894 -- 4363092 MOAC Changes, commented below code to call set_client_info procedure
895 /*
896 --Sakthi
897 -- Need to remove after Omkar able to take care this portion of code in Java.
898 BEGIN
899
900 select org_id into l_org_id from pa_projects_all where project_id = p_project_id;
901 dbms_application_info.set_client_info(l_org_id);
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 x_msg_count := x_msg_count + 1;
906 x_msg_data.extend(1);
907 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_INVALID_PROJECT_ID');
908 --added for bug 2192242
909 fnd_message.set_name('PA', 'PA_INVALID_PROJECT_ID');-- Fix for Bug # 4513291. Uncommented and moved below.
910 fnd_msg_pub.add; -- Fix for Bug # 4513291. Uncommented and moved below.
911 -- x_msg_data(x_msg_count) := fnd_message.get;
912 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_INVALID_PROJECT_ID');
913 x_return_status := 'E';
914 raise API_ERROR;
915 END;
916 --Sakthi
917 */
918 -- 4363092 end
919 -----------------------
920 --INIT_CREATE_PROJECT
921
922 --dbms_output.put_line('Calling init project ');
923
924 if (p_debug_mode = 'Y') then
925 pa_debug.debug('Import-Project : Calling init project Program Starts ');
926 end if;
927
928 -- pa_project_pub.init_project;
929
930 -- Check project id
931
932 if (p_project_id is null or p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) then
933 fnd_message.set_name('PA', 'PA_NO_PROJECT_ID');
934 x_msg_count := x_msg_count + 1;
935 x_msg_data.extend(1);
936 --added for bug 2192242
937 fnd_msg_pub.add;
938 -- x_msg_data(x_msg_count) := fnd_message.get;
939 x_msg_data(x_msg_count) := fnd_message.get_string('PA', 'PA_NO_PROJECT_ID');
940 x_return_status := 'E';
941
942 raise API_ERROR;
943 end if ;
944
945 -- Lock record
946 /*
947 OPEN lock_project_record(p_project_id);
948 FETCH lock_project_record into l_dummy;
949
950 if (lock_project_record%NOTFOUND) then
951 if (p_debug_mode = 'Y') then
952 pa_debug.debug('Update Project Detals : Error PA_XC_NO_DATA_FOUND');
953 end if;
954
955 CLOSE lock_project_record;
956 fnd_message.set_name('PA', 'PA_XC_NO_DATA_FOUND');
957 FND_MESSAGE.Set_token('ENTITY', 'PA_PROJECTS_ALL');
958 FND_MESSAGE.Set_token('PROJECT',to_char(p_project_id));
959 FND_MESSAGE.Set_token('TASK',NULL);
960 x_msg_count := x_msg_count + 1;
961 x_msg_data.extend(1);
962 --added for bug 2192242
963 fnd_msg_pub.add;
964 x_msg_data(x_msg_count) := fnd_message.get;
965 x_return_status := 'E';
966
967 raise API_ERROR;
968 end if;
969 */
970
971 if p_calling_mode = 'PUBLISH' then
972
973 -- Sync pa_proj_elements with pa_tasks
974
975 -- get the values associated to the project_id
976
977 OPEN l_get_project_csr(p_project_id);
978 FETCH l_get_project_csr INTO l_project_name;
979 CLOSE l_get_project_csr;
980
981 -- dbms_output.put_line('Getting Load Project here ');
982
983 l_project_mpx_start_date := fnd_date.canonical_to_date(p_project_mpx_start_date);
984 l_project_mpx_end_date := fnd_date.canonical_to_date(p_project_mpx_end_date);
985
986 if (p_debug_mode = 'Y') then
987 pa_debug.debug('Import-Project : pa_project_pub.load_project Program Starts ');
988 end if;
989
990 pa_project_pub.load_project
991 ( p_api_version_number => G_API_VERSION_NUMBER
992 ,p_init_msg_list => 'F'
993 ,p_return_status => l_return_status
994 ,p_project_name => l_project_name
995 ,p_pa_project_id => p_project_id
996 ,p_start_date => l_project_mpx_start_date
997 ,p_completion_date => l_project_mpx_end_date);
998
999 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1002 RAISE FND_API.G_EXC_ERROR;
1003 END IF; -- IF l_return_status
1004
1005 ------------------------
1006 --DELETE_TASK (loop for multiple tasks)
1007 ------------------------
1008
1009 if (p_debug_mode = 'Y') then
1010 pa_debug.debug('Import-Project : Deleting Task Program Starts ...');
1011 end if;
1012
1013 OPEN l_get_temp_task_csr(p_project_id);
1014 LOOP
1015
1016 FETCH l_get_temp_task_csr
1017 INTO l_task_reference
1018 ,l_task_id;
1019 EXIT WHEN l_get_temp_task_csr%NOTFOUND;
1020
1021 if (p_debug_mode = 'Y') then
1022 pa_debug.debug('Import-Project : Delete_Task Program Starts ...');
1023 end if;
1024
1025 l_msg_count := x_msg_count;
1026 l_msg_data := x_msg_data;
1027 l_return_status := x_return_status;
1028
1029 -- Call PA_TASK_UTILS.check_delete_task_ok, if returns successful
1030 -- Continue, otherwise terminate.
1031
1032 PA_PROJECT_CORE.delete_task
1033 ( x_task_id => l_task_id
1034 --bug 3010538 ,x_validation_mode => 'R' -- Bug 2947492
1035 ,x_err_code => l_err_code
1036 ,x_err_stage => l_err_stage
1037 ,x_err_stack => l_err_stack);
1038
1039 if (l_err_code <> 0) then
1040
1041 l_return_status := 'E';
1042
1043 -- Need to add message to the message log.
1044
1045 if (p_debug_mode = 'Y') then
1046 pa_debug.debug('Import-Project : Error occured in PA_task_Utils.CHECK_DELETE_TASK_OK Procedure ');
1047 end if;
1048
1049 x_return_status := l_return_status;
1050
1051 --dbms_output.put_line(l_return_status);
1052
1053 -- Sakthi
1054 fnd_message.set_name('PA', l_err_stage);
1055 x_msg_count := x_msg_count + 1;
1056 x_msg_data.extend(1);
1057 --added for bug 2192242
1058 fnd_msg_pub.add;
1059 x_msg_data(x_msg_count) := fnd_message.get;
1060 -- Sakthi
1061
1062 raise API_ERROR;
1063 end if;
1064
1065 END LOOP;
1066
1067 CLOSE l_get_temp_task_csr;
1068
1069 -----------------------
1070 --EXECUTE_UPDATE_PROJECT
1071 -----------------------
1072 --dbms_output.put_line('Calling Execute Update Project here ');
1073
1074 if (p_debug_mode = 'Y') then
1075 pa_debug.debug('Import-Project : Execute_Update_Project Program Starts ...');
1076 end if;
1077
1078 IF p_calling_mode = 'PUBLISH' THEN
1079 l_pm_product_code2 := 'WORKPLAN';
1080 ELSE
1081 l_pm_product_code2 := l_pm_product_code;
1082 END IF;
1083
1084 pa_project_pub.execute_update_project
1085 ( P_API_VERSION_NUMBER => G_API_VERSION_NUMBER
1086 ,p_commit => FND_API.G_FALSE
1087 ,p_init_msg_list => FND_API.G_FALSE
1088 ,p_msg_count => p_msg_count
1089 ,p_msg_data => p_msg_data
1090 ,p_return_status => l_return_status
1091 ,p_workflow_started => l_workflow_started
1092 ,p_pm_product_code => l_pm_product_code2
1093 ,p_pass_entire_structure => 'Y' ); -- Added for bug 3696234 : BUg 3627124
1094
1095 --dbms_output.put_line('AFTER Calling Execute Update Project here ');
1096 --dbms_output.put_line(l_return_status);
1097 --dbms_output.put_line(p_msg_data);
1098
1099 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF; -- IF l_return_status
1104
1105 --hsiu added for publishing bug
1106 update PA_TASKS pt
1107 set pt.parent_task_id =
1108 (select a.task_id
1109 from pa_tasks b, pa_temp_import_tasks a
1110 where a.project_id = p_project_id
1111 and a.task_reference = b.pm_task_reference -- Bug 3302732: added this
1112 -- Bug 3302732 and to_number(a.task_reference) = to_number(b.pm_task_reference) : Commented this line
1113 and b.task_id = pt.parent_task_id)
1114 where pt.project_id = p_project_id;
1115
1116 --hsiu added for publishing bug
1117 update PA_TASKS pt
1118 set pt.top_task_id =
1119 (select a.task_id
1120 from pa_tasks b, pa_temp_import_tasks a
1121 where a.project_id = p_project_id
1122 and a.task_reference = b.pm_task_reference
1123 and b.task_id = pt.top_task_id)
1124 where pt.project_id = p_project_id;
1125
1126 -- Need to update pa_tasks so that the newly created financial task have the same task_id
1127 -- as the tasks in pa_proj_elements
1128 UPDATE PA_TASKS pt
1129 SET pt.task_id =
1130 (select task_id
1131 from PA_TEMP_IMPORT_TASKS temp
1132 where temp.project_id = p_project_id
1133 and temp.task_reference = pt.pm_task_reference)
1134 WHERE pt.project_id = p_project_id;
1135
1136 UPDATE PA_PROJ_ELEMENTS e
1137 set e.pm_source_reference = (
1138 select PM_TASK_REFERENCE
1139 from PA_TASKS t
1140 where t.task_id = e.proj_element_id
1141 ) where e.project_id = p_project_id;
1142
1143 end if; -- if p_calling_mode = 'PUBLISH' OR l_sync_import = true then
1144
1145 -----------------------
1146 --Transactions Commit
1147
1148 if (p_debug_mode = 'Y') then
1149 pa_debug.debug('Import-Project : Transactions Commit Program Starts ...');
1150 end if;
1151
1152 if p_commit = 'Y' then
1153 commit;
1154 end if;
1155
1156 if (p_debug_mode = 'Y') then
1157 pa_debug.debug('Import-Project : Calling init project Program Starts ...');
1158 end if;
1159
1160 pa_project_pub.init_project;
1161
1162 if (p_debug_mode = 'Y') then
1163 pa_debug.debug('Import-Project : Program Ends ...');
1164 end if;
1165
1166
1167 EXCEPTION
1168 WHEN FND_API.G_EXC_ERROR THEN
1169 p_msg_count := fnd_msg_pub.count_msg;
1170 for i in 1..p_msg_count loop
1171 --dbms_output.put_line('INSIDE THE ERROR HANDLING PORTION ');
1172
1173 if (p_debug_mode = 'Y') then
1174 pa_debug.debug('Import-Project : Inside Error Handling Program ...');
1175 end if;
1176
1177 -- Fix for Bug # 4513291. Ideally this call should use p_encoded as F
1178 -- So that it gets decoded messages. But not changing Right now.
1179 PA_INTERFACE_UTILS_PUB.get_messages (
1180 p_msg_count => 1
1181 ,p_msg_index => i
1182 ,p_msg_data => p_msg_data
1183 ,p_data => l_data
1184 ,p_msg_index_out => l_msg_index_out);
1185
1186 x_msg_count := x_msg_count + 1;
1187 x_msg_data.extend(1);
1188 x_msg_data(x_msg_count) := l_data;
1189
1190 end loop;
1191
1192 x_return_status := FND_API.G_RET_STS_ERROR ;
1193
1194 --hsiu added
1195 IF (p_calling_mode <> 'PUBLISH') THEN
1196 pa_project_pub.clear_project;
1197 --hsiu added
1198 END IF;
1199
1200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1201 --dbms_output.put_line('UNHANDLED Exception Error Handling ');
1202
1203 if (p_debug_mode = 'Y') then
1204 pa_debug.debug('Import-Project : Unhandled Expection ...');
1205 end if;
1206
1207 p_msg_count := fnd_msg_pub.count_msg;
1208 for i in 1..p_msg_count loop
1209
1210 if (p_debug_mode = 'Y') then
1211 pa_debug.debug('Import-Project : Inside Error Handling Program ...');
1212 end if;
1213
1214 PA_INTERFACE_UTILS_PUB.get_messages (
1215 p_msg_count => 1
1216 ,p_msg_index => i
1217 ,p_msg_data => p_msg_data
1218 ,p_data => l_data
1219 ,p_msg_index_out => l_msg_index_out);
1220
1221 x_msg_count := x_msg_count + 1;
1222 x_msg_data.extend(1);
1223 x_msg_data(x_msg_count) := l_data;
1224
1225 end loop;
1226
1227 x_return_status := FND_API.G_RET_STS_ERROR;
1228 --hsiu added
1229 IF (p_calling_mode <> 'PUBLISH') THEN
1230 pa_project_pub.clear_project;
1231 --hsiu added
1232 END IF;
1233
1234 WHEN API_ERROR THEN
1235
1236 IF (p_calling_mode <> 'PUBLISH') THEN
1237 pa_project_pub.clear_project;
1238 END IF;
1239 WHEN NO_DATA_FOUND THEN
1240 -- dbms_output.put_line('NO-DATA-FOUND : YOU ARE IN MAIN PROCEDURE ');
1241
1242 if (p_debug_mode = 'Y') then
1243 pa_debug.debug('Import-Project : Inside No Data Found Exception ...');
1244 end if;
1245
1246 fnd_message.set_name('PA', 'PA_XC_NO_DATA_FOUND');
1247 x_msg_count := x_msg_count + 1;
1248 x_msg_data.extend(1);
1249 --added for bug 2192242
1250 fnd_msg_pub.add;
1251 x_msg_data(x_msg_count) := fnd_message.get;
1252 x_return_status := 'E';
1253
1254 WHEN ROW_ALREADY_LOCKED THEN
1255 x_return_status := FND_API.G_RET_STS_ERROR;
1256
1257 if (p_debug_mode = 'Y') then
1258 pa_debug.debug('Import-Project : Inside Row Already Locked Exception ...');
1259 end if;
1260
1261 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1262 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
1263 FND_MESSAGE.Set_token('ENTITY', 'PA_PROJECTS_ALL');
1264 FND_MESSAGE.Set_token('PROJECT',to_char(P_PROJECT_ID));
1265 FND_MESSAGE.Set_token('TASK',NULL);
1266 x_msg_count := x_msg_count + 1;
1267 x_msg_data.extend(1);
1268 --added for bug 2192242
1269 fnd_msg_pub.add;
1270 x_msg_data(x_msg_count) := FND_MESSAGE.get;
1271 x_return_status := 'E';
1272 END IF;
1273
1274 WHEN OTHERS THEN
1275 if (p_debug_mode = 'Y') then
1276 pa_debug.debug('Import-Project : Inside Other Exception ...');
1277 end if;
1278
1279 l_text := SQLCODE||SUBSTRB(SQLERRM,1,1000); -- 4537865 : Changed substr to substrb
1280 x_msg_count := x_msg_count + 1;
1281 x_msg_data.extend(1);
1282 x_msg_data(x_msg_count) := l_text;
1283 x_return_status := 'U';
1284
1285 END Import_project;
1286
1287 --Name: fetch_task_id
1288 --Type: Procedure
1289 --Description: This procedure can be used to get the task_id from database
1290 -- for correspondening task reference.
1291 --
1292 --Called subprograms:
1293 --
1294
1295 PROCEDURE fetch_task_id
1296 ( p_task_index IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3302732 : Changed type to varchar2
1297 ,p_project_id IN NUMBER
1298 ,p_pm_task_reference IN VARCHAR2
1299 ,x_task_id OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
1300 IS
1301
1302 l_task_id NUMBER := 0;
1303 l_task_reference NUMBER := 0;
1304
1305 --needed to get the field values associated to a particular Task_reference
1306
1307 CURSOR l_get_task_csr (c_project_id NUMBER, c_pm_task_reference VARCHAR2)
1308 IS
1309 SELECT task_id
1310 FROM pa_tasks
1311 WHERE project_id = c_project_id
1312 AND pm_task_reference = c_pm_task_reference;
1313
1314 BEGIN
1315
1316 OPEN l_get_task_csr(p_project_id, p_pm_task_reference);
1317 FETCH l_get_task_csr INTO l_task_id;
1318
1319 IF l_get_task_csr%NOTFOUND
1320 THEN
1321
1322 CLOSE l_get_task_csr;
1323 --hy PA_PROJECT_PUB.G_tasks_in_tbl(to_number(p_pm_task_reference)).pa_task_id := NULL;
1324 x_task_id :=NULL;
1325 return;
1326 END IF;
1327
1328 CLOSE l_get_task_csr;
1329
1330 --hy PA_PROJECT_PUB.G_tasks_in_tbl(to_number(p_pm_task_reference)).pa_task_id := l_task_id;
1331
1332 x_task_id := l_task_id;
1333
1334 EXCEPTION --4537865
1335 WHEN OTHERS THEN
1336 x_task_id := NULL;
1337 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_XC_PROJECT_PUB'
1338 ,p_procedure_name => 'fetch_task_id'
1339 ,p_error_text => SUBSTRB(SQLERRM,1,240));
1340 RAISE;
1341 END fetch_task_id;
1342
1343
1344 --------------------------------------------------------------------------------
1345 --Name: fetch_proj_element_id
1346 --Type: Procedure
1347 --Description: This procedure can be used to get the proj_elememt_id from database
1348 -- for correspondening task reference.
1349 --
1350 --Called subprograms:
1351 --
1352
1353 PROCEDURE fetch_proj_element_id
1354 ( p_task_index IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3302732
1355 ,p_project_id IN NUMBER
1356 ,p_pm_task_reference IN VARCHAR2
1357 ,x_task_id OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
1358 IS
1359
1360 l_task_id NUMBER := 0;
1361 l_task_reference NUMBER := 0;
1362
1363 --needed to get the field values associated to a particular Task_reference
1364
1365 CURSOR l_get_task_csr (c_project_id NUMBER, c_pm_task_reference VARCHAR2)
1366 IS
1367 SELECT proj_element_id
1368 FROM pa_proj_elements
1369 WHERE project_id = c_project_id
1370 AND pm_source_reference = c_pm_task_reference;
1371
1372 BEGIN
1373
1374 --dbms_output.put_line(p_pm_task_reference);
1375
1376 OPEN l_get_task_csr(p_project_id, p_pm_task_reference);
1377 FETCH l_get_task_csr INTO l_task_id;
1378
1379 IF l_get_task_csr%NOTFOUND
1380 THEN
1381 CLOSE l_get_task_csr;
1382 --hy PA_PROJECT_PUB.G_tasks_in_tbl(to_number(p_pm_task_reference)).pa_task_id := NULL;
1383 x_task_id :=NULL;
1384 return;
1385 END IF;
1386
1387 CLOSE l_get_task_csr;
1388
1389 --hy PA_PROJECT_PUB.G_tasks_in_tbl(to_number(p_pm_task_reference)).pa_task_id := l_task_id;
1390
1391 x_task_id := l_task_id;
1392
1393 EXCEPTION --4537865
1394 WHEN OTHERS THEN
1395 x_task_id := NULL;
1396 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_XC_PROJECT_PUB'
1397 ,p_procedure_name => 'fetch_proj_element_id'
1398 ,p_error_text => SUBSTRB(SQLERRM,1,240));
1399 RAISE;
1400 END fetch_proj_element_id;
1401
1402 --
1403 -- FUNCTION
1404 -- is_number
1405 --
1406 --History:
1407 -- 13-DEC-2000 - anlee Created.
1408 --
1409 FUNCTION is_number(value_in IN VARCHAR2) RETURN BOOLEAN
1410 IS
1411 val NUMBER;
1412
1413 BEGIN
1414 val := TO_NUMBER(value_in);
1415 return TRUE;
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 return FALSE;
1419 END is_number;
1420
1421
1422 -- Bug 3302732: Added this function. This generates task reference for the passed in
1423 -- project and proj_element_id
1424
1425 FUNCTION generate_new_task_reference(p_project_id in NUMBER, p_proj_element_id IN NUMBER)
1426 RETURN VARCHAR2
1427 IS
1428
1429 l_new_task_reference VARCHAR2(25);
1430 is_unique VARCHAR2(1);
1431 i number := 0;
1432
1433 BEGIN
1434 l_new_task_reference := TO_CHAR(p_proj_element_id);
1435
1436 LOOP
1437 i := i + 1;
1438 is_unique := check_ref_unique(p_project_id, l_new_task_reference) ;
1439 if is_unique = 'N' then
1440 l_new_task_reference := TO_CHAR(p_proj_element_id)||'_'||to_char(i);
1441 else
1442 exit;
1443 end if;
1444
1445 END LOOP;
1446
1447 RETURN l_new_task_reference;
1448
1449 END generate_new_task_reference;
1450
1451
1452 -- Bug 3302732: Added this new function. This checks if the passed in task reference is
1453 -- unique in context to a project.
1454
1455 FUNCTION check_ref_unique(p_project_id in NUMBER, p_new_task_reference IN VARCHAR2
1456 )
1457 RETURN VARCHAR2
1458 IS
1459 l_unique VARCHAR2(1) :='Y';
1460 BEGIN
1461
1462 select 'N' into l_unique
1463 from pa_proj_elements
1464 where project_id = p_project_id
1465 and pm_source_reference = p_new_task_reference
1466 AND parent_structure_id = ( SELECT ppe.proj_element_id --Added this subQry for bug# 3601700
1467 FROM pa_proj_elements ppe,
1468 pa_proj_structure_types ppst,
1469 pa_structure_types pst
1470 WHERE ppe.proj_element_id = ppst.proj_element_id
1471 AND ppe.project_id = p_project_id
1472 AND ppst.structure_type_id = pst.structure_type_id
1473 AND pst.structure_type = 'WORKPLAN' --specified as workplan as this will only called while publishing workplan strcuture
1474 )
1475 AND OBJECT_TYPE = 'PA_TASKS';
1476 -- and rownum = 1; --Commented for Bug 3601700
1477
1478 return l_unique;
1479
1480 EXCEPTION
1481 WHEN NO_DATA_FOUND THEN
1482 RETURN l_unique;
1483
1484 END check_ref_unique;
1485
1486 --------------------------------------------------------------------------------
1487
1488 end PA_XC_PROJECT_PUB;