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