6
1 PACKAGE BODY PA_PROJ_STRUC_MAPPING_PUB AS
2 /* $Header: PAPSMPPB.pls 120.3.12020000.2 2012/07/12 05:58:33 djambhek ship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_PROJ_STRUC_MAPPING_PUB';
5 Invalid_Arg_Exc_WP Exception;
7 -- Procedure : DELETE_MAPPING
8 -- Type : Public Procedure
9 -- Purpose : This API will be called from task details relationships sub tab.
10 -- : This will be called when the user clicks on the delete icon
11 -- : in the mapping region of the fin task details page
12 -- Note : This API can work in 3 modes.
13 -- : --If both WkpTask ID and Fin Task ID are passed --- The correspondinf mapping is removed
14 -- : --Only FP task id is passed. --- All the mapping for this FP task id will be removed
15 -- : --Only WP task id is passed. --- The mapping corresponding to this Wkp Task ID will be removed
16 -- : The parameter p_wp_from_task_name is not used currently but will be used in future.
17 -- :
18 -- Assumptions :
19
20 -- Parameters Type Required Description and Purpose
21 -- --------------------------- ------ -------- --------------------------------------------------------
22 -- p_wp_from_task_name VARCHAR2 NO Indicates the workplan tasks name. This can be a string of workplan tasks.
23 -- p_wp_task_version_id NUMBER NO Task Version ID of workplan task for which mapping needs to be deleted.
24 -- p_fp_task_version_id NUMEBR NO Task Version ID of Financial Task for which mapping needs to be deleted.
25
26 PROCEDURE DELETE_MAPPING
27 (
28 p_api_version IN NUMBER := 1.0
29 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
30 , p_commit IN VARCHAR2 := FND_API.G_FALSE
31 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
32 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
33 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
34 , p_debug_mode IN VARCHAR2 := 'N'
35 , p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
36 , p_wp_from_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
37 , p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
38 , p_fp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
39 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
40 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
41 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
42 )
43 IS
44
45 l_msg_count NUMBER := 0;
46 l_data VARCHAR2(2000);
47 l_msg_data VARCHAR2(2000);
48 l_msg_index_out NUMBER;
49 l_debug_mode VARCHAR2(1);
50 l_object_relationship_id NUMBER;
51 l_rec_version_num NUMBER;
52
53 l_debug_level2 CONSTANT NUMBER := 2;
54 l_debug_level3 CONSTANT NUMBER := 3;
55 l_debug_level4 CONSTANT NUMBER := 4;
56 l_debug_level5 CONSTANT NUMBER := 5;
57
58
59 --This cursor selects relationship id, record version number from pa_object_relationship for the passed work plan task id
60 CURSOR c_get_mapping_frm_wp_task (l_wp_task_version_id NUMBER)
61 IS
62 SELECT
63 obj.object_relationship_id
64 , obj.record_version_number
65 FROM
66 PA_OBJECT_RELATIONSHIPS obj
67 WHERE obj.object_id_from1 = l_wp_task_version_id
68 AND obj.relationship_type='M';
69
70 --This cursor selects relationship id , record version number from pa_object_relationship for the passed work plan and financial task
71 --This will return more than a row
72 CURSOR c_get_mapping_frm_fp_task ( l_fp_task_version_id NUMBER)
73 IS
74 SELECT
75 object_relationship_id
76 , record_version_number
77 FROM
78 PA_OBJECT_RELATIONSHIPS
79 WHERE
80 object_id_to1 = l_fp_task_version_id
81 AND relationship_type='M';
82
83 --This cursor selects relationship id ,record version number from pa_object_relationship for the passed WP task id and FP task id
84 CURSOR c_get_mapping_frm_wp_fp_task (l_wp_task_version_id NUMBER , l_fp_task_version_id NUMBER)
85 IS
86 SELECT
87 obj.object_relationship_id
88 , obj.record_version_number
89 FROM
90 PA_OBJECT_RELATIONSHIPS obj
91 WHERE obj.object_id_from1 = l_wp_task_version_id
92 AND obj.object_id_to1 = l_fp_task_version_id
93 AND obj.relationship_type='M';
94
95 BEGIN
96
97 x_msg_count := 0;
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
101 IF l_debug_mode = 'Y' THEN
102 PA_DEBUG.set_curr_function( p_function => 'DELETE_MAPPING',
103 p_debug_mode => l_debug_mode );
104 END IF;
105
106 IF l_debug_mode = 'Y' THEN
107 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Printing Input parameters';
108 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
109 l_debug_level3);
110
111 Pa_Debug.WRITE(g_module_name,'p_wp_from_task_name'||':'||p_wp_from_task_name,
112 l_debug_level3);
113
114 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
115 l_debug_level3);
116
120
117 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
118 l_debug_level3);
119 END IF;
121 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
122 FND_MSG_PUB.initialize;
123 END IF;
124
125 IF (p_commit = FND_API.G_TRUE) THEN
126 savepoint DELETE_MAPPING_PUBLIC;
127 END IF;
128
129 IF l_debug_mode = 'Y' THEN
130 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Validating Input parameters';
131 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
132 l_debug_level3);
133 END IF;
134
135 IF (
136 ( p_wp_task_version_id IS NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM ) AND
137 ( p_fp_task_version_id IS NULL OR p_fp_task_version_id = FND_API.G_MISS_NUM )
138 )
139 THEN
140 IF l_debug_mode = 'Y' THEN
141 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Both p_wp_task_version_id and p_fp_task_version_id are null';
142 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
143 l_debug_level3);
144 END IF;
145 RAISE Invalid_Arg_Exc_WP;
146 END IF;
147 --Following is added to implement locking
148 -- If both WP task ID and FP Task ID are passed from self service
149 IF ( ( p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
150 AND
151 ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
152 )
153 THEN
154 --Get the object_relationship_id
155 OPEN c_get_mapping_frm_wp_fp_task ( p_wp_task_version_id , p_fp_task_version_id );
156 FETCH c_get_mapping_frm_wp_fp_task INTO l_object_relationship_id , l_rec_version_num ;
157 -- If no row found
158 -- Raise exception and populate error message
159 IF ( c_get_mapping_frm_wp_fp_task%NOTFOUND )
160 THEN
161 IF l_debug_mode = 'Y' THEN
162 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : No Mapping Exists';
163 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
164 l_debug_level3);
165 END IF;
166 -- Bug 4142254 : Record has been changes error make sense for Self Service, but from AMG
167 -- it should be "Mapping does not exist between the given tasks."
168 IF p_calling_module = 'AMG' THEN
169 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
170 p_msg_name => 'PA_TASK_MAPPING_NOT_EXIST' );
171 RAISE FND_API.G_EXC_ERROR ;
172 ELSE
173 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
174 p_msg_name => 'PA_RECORD_CHANGED' );
175 RAISE FND_API.G_EXC_ERROR ;
176 END IF;
177 END IF;
178 CLOSE c_get_mapping_frm_wp_fp_task;
179 -- if any row found, delete the row
180 IF (l_object_relationship_id IS NOT NULL)
181 THEN
182 IF l_debug_mode = 'Y' THEN
183 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Calling delete';
184 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
185 l_debug_level3);
186 END IF;
187
188 -- Call public API for deleting the relationship
189
190 PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
191 (
192 p_api_version => p_api_version
193 , p_init_msg_list => FND_API.G_FALSE
197 , p_debug_mode => l_debug_mode
194 , p_commit => p_commit
195 , p_validate_only => p_validate_only
196 , p_calling_module => 'SELF_SERVICE'
198 , p_object_relationship_id => l_object_relationship_id
199 , p_record_version_number => l_rec_version_num
200 , x_return_status => x_return_status
201 , x_msg_count => x_msg_count
202 , x_msg_data => x_msg_data
203 );
204 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
205 THEN
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208 END IF;
209
210 -- If the WP task ID is not NULL, get the relationship ID and delete mapping
211 ELSIF (p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
212 THEN
213 OPEN c_get_mapping_frm_wp_task ( p_wp_task_version_id );
214 FETCH c_get_mapping_frm_wp_task INTO l_object_relationship_id , l_rec_version_num ;
215 CLOSE c_get_mapping_frm_wp_task;
216
217 IF (l_object_relationship_id IS NOT NULL)
218 THEN
219 IF l_debug_mode = 'Y' THEN
220 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Calling delete';
221 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
222 l_debug_level3);
223 END IF;
224
225 -- Call public API for deleting the relationship
226
227 PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
228 (
229 p_api_version => p_api_version
230 , p_init_msg_list => FND_API.G_FALSE
231 , p_commit => p_commit
232 , p_validate_only => p_validate_only
233 , p_calling_module => 'SELF_SERVICE'
234 , p_debug_mode => l_debug_mode
235 , p_object_relationship_id => l_object_relationship_id
236 , p_record_version_number => l_rec_version_num
237 , x_return_status => x_return_status
238 , x_msg_count => x_msg_count
239 , x_msg_data => x_msg_data
240 );
241
242 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
243 THEN
244 RAISE FND_API.G_EXC_ERROR;
245 END IF;
246
247 ELSE
248 IF l_debug_mode = 'Y' THEN
249 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : Mapping does not exist for the passed WP task ID';
250 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
251 l_debug_level3);
252 END IF;
253 END IF;--IF (l_object_relationship_id IS NOT NULL) ENDS
254
255
256 -- If financial task id is passed get the mapping with FP task id
257 ELSIF ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
258 THEN
259 FOR map_rec IN c_get_mapping_frm_fp_task ( p_fp_task_version_id ) LOOP
260
261 PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
262 (
263 p_api_version => p_api_version
264 , p_init_msg_list => FND_API.G_FALSE
265 , p_commit => p_commit
266 , p_validate_only => p_validate_only
267 , p_calling_module => 'SELF_SERVICE'
268 , p_debug_mode => l_debug_mode
269 , p_object_relationship_id => map_rec.object_relationship_id
270 , p_record_version_number => map_rec.record_version_number
271 , x_return_status => x_return_status
272 , x_msg_count => x_msg_count
273 , x_msg_data => x_msg_data
274 );
275 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
276 THEN
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279
280 END LOOP;
281 END IF;
282
283
284 IF (p_commit = FND_API.G_TRUE) THEN
285 COMMIT;
286 END IF;
287
288
289 EXCEPTION
290
291 WHEN FND_API.G_EXC_ERROR THEN
292
293 x_return_status := Fnd_Api.G_RET_STS_ERROR;
294 l_msg_count := Fnd_Msg_Pub.count_msg;
295
296 IF p_commit = FND_API.G_TRUE THEN
297 ROLLBACK TO DELETE_MAPPING_PUBLIC;
298 END IF;
299
300 IF c_get_mapping_frm_wp_task%ISOPEN THEN
301 CLOSE c_get_mapping_frm_wp_task;
302 END IF;
303
304 IF c_get_mapping_frm_fp_task%ISOPEN THEN
305 CLOSE c_get_mapping_frm_wp_task;
306 END IF;
307
308 IF c_get_mapping_frm_wp_fp_task%ISOPEN THEN
309 CLOSE c_get_mapping_frm_wp_fp_task;
310 END IF;
311
312 IF l_msg_count = 1 AND x_msg_data IS NULL
313 THEN
314 Pa_Interface_Utils_Pub.get_messages
315 ( p_encoded => Fnd_Api.G_TRUE
319 , p_data => l_data
316 , p_msg_index => 1
317 , p_msg_count => l_msg_count
318 , p_msg_data => l_msg_data
320 , p_msg_index_out => l_msg_index_out);
321 x_msg_data := l_data;
322 x_msg_count := l_msg_count;
323 ELSE
324 x_msg_count := l_msg_count;
325 END IF;
326 IF l_debug_mode = 'Y' THEN
327 Pa_Debug.reset_curr_function;
328 END IF;
329
330 WHEN Invalid_Arg_Exc_WP THEN
331
332 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
333 x_msg_count := 1;
334 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
335
336 IF p_commit = FND_API.G_TRUE THEN
337 ROLLBACK TO DELETE_MAPPING_PUBLIC;
338 END IF;
339 IF c_get_mapping_frm_wp_task%ISOPEN THEN
340 CLOSE c_get_mapping_frm_wp_task;
341 END IF;
342
343 IF c_get_mapping_frm_fp_task%ISOPEN THEN
344 CLOSE c_get_mapping_frm_wp_task;
345 END IF;
346
347 IF c_get_mapping_frm_wp_fp_task%ISOPEN THEN
348 CLOSE c_get_mapping_frm_wp_fp_task;
349 END IF;
350
351 Fnd_Msg_Pub.add_exc_msg
352 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
353 , p_procedure_name => 'DELETE_MAPPING'
354 , p_error_text => x_msg_data);
355
356 IF l_debug_mode = 'Y' THEN
357 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
358 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
359 l_debug_level5);
360 Pa_Debug.reset_curr_function;
361 END IF;
362 RAISE;
363
364 WHEN OTHERS THEN
365
366 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
367 x_msg_count := 1;
368 x_msg_data := SQLERRM;
369
370 IF p_commit = FND_API.G_TRUE THEN
371 ROLLBACK TO DELETE_MAPPING_PUBLIC;
372 END IF;
373
374 IF c_get_mapping_frm_wp_task%ISOPEN THEN
375 CLOSE c_get_mapping_frm_wp_task;
376 END IF;
377
378 IF c_get_mapping_frm_fp_task%ISOPEN THEN
379 CLOSE c_get_mapping_frm_wp_task;
380 END IF;
381
382 IF c_get_mapping_frm_wp_fp_task%ISOPEN THEN
383 CLOSE c_get_mapping_frm_wp_fp_task;
384 END IF;
385
386 Fnd_Msg_Pub.add_exc_msg
387 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
388 , p_procedure_name => 'DELETE_MAPPING'
389 , p_error_text => x_msg_data);
390
391 IF l_debug_mode = 'Y' THEN
392 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
393 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
394 l_debug_level5);
395 Pa_Debug.reset_curr_function;
396 END IF;
397 RAISE;
398 END DELETE_MAPPING ;
399
400 -- Procedure : CREATE_MAPPING
401 -- Type : Public Procedure
402 -- Purpose : This API will be used to create the mapping for the passed tasks.
403 -- : This will be called from Create Mapping pages.
404 -- : This will also be called from update_mapping api.
405 -- Note : Using the two task IDs as input, the mapping is created. First CHECK_CREATE_MAPPING_OK is called.
406 -- : If task name is passed instead of task ID, task id is fetched from
407 -- : the table using parent_structure_version_id and used.
408 -- :
409 -- Assumptions : The FP task will have only one version
410
411 -- Parameters Type Required Description and Purpose
412 -- --------------------------- ------ -------- --------------------------------------------------------
413 -- p_wp_task_name VARCHAR2 NO If WP task version id is null, then task name should be passed
414 -- p_wp_task_version_id NUMBER NO The WP task id from which mapping has to be done
415 -- p_parent_str_version_id NUMBER NO It is required to get the task id from task name.
416 -- p_fp_task_version_id NUMBER NO FP task version id to which mapping wil be created
417 -- p_fp_task_name VARCHAR2 NO IF FP task id is not passed, task name will be required
418 -- p_project_id NUMBER Yes The project id will be used in case of getting task ids from task names
419
420 PROCEDURE CREATE_MAPPING
421 (
422 p_api_version IN NUMBER := 1.0
423 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
424 , p_commit IN VARCHAR2 := FND_API.G_FALSE
425 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
426 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
427 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
428 , p_debug_mode IN VARCHAR2 := 'N'
429 , p_wp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
430 , p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
431 , p_parent_str_version_id IN NUMBER := FND_API.G_MISS_NUM
432 , p_fp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
433 , p_fp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
434 , p_project_id IN NUMBER
435 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
439 IS
436 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
437 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
438 )
440
441 l_msg_count NUMBER := 0;
442 l_data VARCHAR2(2000);
443 l_msg_data VARCHAR2(2000);
444 l_msg_index_out NUMBER;
445 l_debug_mode VARCHAR2(1);
446
447 l_wp_task_version_id NUMBER ;
448 l_fp_task_version_id NUMBER ;
449 l_create_mapping_ok VARCHAR2(1);
450 l_error_message_code VARCHAR2(32);
451 l_relationship_id VARCHAR2(20);
452
453 l_debug_level2 CONSTANT NUMBER := 2;
454 l_debug_level3 CONSTANT NUMBER := 3;
455 l_debug_level4 CONSTANT NUMBER := 4;
456 l_debug_level5 CONSTANT NUMBER := 5;
457
458 /*Bug 8574986 BEGIN*/
459 l_msg_type VARCHAR2(2);
460 l_fp_task_id number;
461 l_msg_token1 VARCHAR2(1000);
462 /*Bug 8574986 END*/
463
464 --select the wp_task_version_id for the corresponding name
465 CURSOR c_get_wp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER, l_parent_str_version_id NUMBER)
466 IS
467 SELECT
468 ppv.element_version_id
469
470 FROM
471 pa_proj_element_versions ppv
472 , pa_proj_elements pae
473 WHERE pae.name = l_task_name
474 AND pae.project_id = l_project_id
475 AND ppv.project_id = l_project_id
476 AND ppv.proj_element_id = pae.proj_element_id
477 AND ppv.parent_structure_version_id = l_parent_str_version_id
478 AND pae.object_type = 'PA_TASKS'
479 AND pae.project_id = ppv.project_id ;
480
481 --select the fp_task_version_id for the corresponding name , it does not require parent str ver id
482 CURSOR c_get_fp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER)
483 IS
484 SELECT
485 ppv.element_version_id
486
487 FROM
488 pa_proj_element_versions ppv
489 , pa_proj_elements pae
490 , pa_proj_elem_ver_structure str_ver
491 , pa_proj_structure_types str_type
492 , pa_structure_types
493
494 WHERE pae.name = l_task_name
495 AND pae.project_id = l_project_id
496 AND ppv.project_id = l_project_id
497 AND ppv.proj_element_id = pae.proj_element_id
498 AND ppv.parent_structure_version_id = str_ver.element_version_id
499 AND pae.object_type = 'PA_TASKS'
500 AND ppv.object_type = 'PA_TASKS'
501 AND str_ver.project_id = l_project_id
502 AND str_ver.proj_element_id = str_type.proj_element_id
503 AND str_type.structure_type_id = pa_structure_types.structure_type_id
504 AND pa_structure_types.structure_type = 'FINANCIAL'
505 AND pae.project_id = ppv.project_id ;
506
507
508 BEGIN
509
510 x_msg_count := 0;
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
513
514 IF l_debug_mode = 'Y' THEN
515 PA_DEBUG.set_curr_function( p_function => 'CREATE_MAPPING',
516 p_debug_mode => l_debug_mode );
517 END IF;
518
519 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
520 FND_MSG_PUB.initialize;
521 END IF;
522
523 IF (p_commit = FND_API.G_TRUE) THEN
524 savepoint CREATE_MAPPING_PUBLIC;
525 END IF;
526
527 IF l_debug_mode = 'Y' THEN
528 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Printing Input parameters';
529 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
530 l_debug_level3);
531
532 Pa_Debug.WRITE(g_module_name,'p_wp_task_name'||':'||p_wp_task_name,
533 l_debug_level3);
534
535 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
536 l_debug_level3);
537
538 Pa_Debug.WRITE(g_module_name,'p_parent_str_version_id'||':'||p_parent_str_version_id,
539 l_debug_level3);
540
541 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
542 l_debug_level3);
543 Pa_Debug.WRITE(g_module_name,'p_fp_task_name'||':'||p_fp_task_name,
544 l_debug_level3);
545 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
546 l_debug_level3);
547 END IF;
548
549 IF l_debug_mode = 'Y' THEN
550 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Validating Input parameters';
551 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
552 END IF;
553
554 --Check if project id is null , raise an error
555 IF (p_project_id is NULL)
556 THEN
557 IF l_debug_mode = 'Y' THEN
558 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : project id can not be null';
559 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
560 l_debug_level3);
561 END IF;
562 RAISE Invalid_Arg_Exc_WP;
563 END IF;
564
565 /*Bug 8574986 BEGIN*/
566
567 select proj_element_id
568 into l_fp_task_id
569 from pa_proj_element_versions where element_version_id=p_fp_task_version_id;
573 , X_msg_data=> l_msg_data
570
571 PA_TRANSACTIONS_PUB.validate_task(X_project_id=> p_project_id
572 , X_task_id=> l_fp_task_id
574 , X_msg_type=> l_msg_type
575 , X_msg_token1=>l_msg_token1
576 , X_msg_count=> l_msg_count);
577
578
579 IF (l_msg_count>0 and l_msg_type='E') Then
580 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
581 p_msg_name => 'PA_CM_SUB_TASK_MAP',
582 p_token1=>'PATC_MSG_TOKEN1',
583 p_value1=>l_msg_token1);
584 RAISE FND_API.G_EXC_ERROR ;
585
586
587 End If;
588 /*Bug 8574986 END*/
589
590 -- if wp task id and wp task name both are null , or
591 -- if fp task id and and fp task name both are null
592 -- raise error
593 IF (
594 (
595 ( p_wp_task_name is NULL OR p_wp_task_name = FND_API.G_MISS_CHAR ) AND
596 ( p_wp_task_version_id is NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM )
597 ) OR
598 (
599 ( p_fp_task_name is NULL OR p_fp_task_name = FND_API.G_MISS_CHAR ) AND
600 ( p_fp_task_version_id is NULL OR p_fp_task_version_id = FND_API.G_MISS_NUM )
601 )
602 )
603 THEN
604 IF l_debug_mode = 'Y' THEN
605 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Both work plan task id and task name are null';
606 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
607 l_debug_level3);
608 END IF;
609 RAISE Invalid_Arg_Exc_WP;
610 END IF;
611
612 -- Parent structure version id is essential to get task id using task name , so it should be validated
613 IF (
614 ( p_wp_task_version_id IS NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM ) AND
615 (
616 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR ) AND
617 ( p_parent_str_version_id IS NULL OR p_parent_str_version_id = FND_API.G_MISS_NUM )
618 )
619 )
620 THEN
621 IF l_debug_mode = 'Y' THEN
622 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : parent structue version id can not be null, if wp task name is not null';
623 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
624 l_debug_level3);
625 END IF;
626 RAISE Invalid_Arg_Exc_WP;
627 END IF;
628
629 -- If the task name is passed insteand of task id, we need to fetch the task id
630
631 IF ( p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
632 THEN
633 l_wp_task_version_id := p_wp_task_version_id;
634
635 ELSIF
636 ( ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR )AND
637 ( p_parent_str_version_id IS NOT NULL AND p_parent_str_version_id <> FND_API.G_MISS_NUM )
638 )
639 THEN
640 --select the wp_task_version_id for the corresponding name and store it in l_wp_task_version_id
641
642 OPEN c_get_wp_task_ver_id_frm_name ( p_wp_task_name , p_project_id , p_parent_str_version_id );
643
644 FETCH c_get_wp_task_ver_id_frm_name INTO l_wp_task_version_id ;
645 IF ( c_get_wp_task_ver_id_frm_name%NOTFOUND )
646 THEN
647 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
648 p_msg_name => 'PA_OBJECT_NAME_INV' );
649 RAISE FND_API.G_EXC_ERROR ;
650 END IF;
651 CLOSE c_get_wp_task_ver_id_frm_name ;
652
653 END IF;
654
655 IF ( ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
656 )
657 THEN
658 l_fp_task_version_id := p_fp_task_version_id;
659
660 ELSIF ( p_fp_task_name IS NOT NULL AND p_fp_task_name <> FND_API.G_MISS_CHAR )
661 THEN
662 -- get the fp_task_version_id and place it in l_fp_task_version_id;
663 -- Assuming there will be only one version for the FP task.
664 Pa_Debug.WRITE('test',l_fp_task_version_id, l_debug_level3);
665 OPEN c_get_fp_task_ver_id_frm_name ( p_fp_task_name , p_project_id );
666 FETCH c_get_fp_task_ver_id_frm_name INTO l_fp_task_version_id ;
667 IF ( c_get_fp_task_ver_id_frm_name%NOTFOUND )
668 THEN
669 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
670 p_msg_name => 'PA_OBJECT_NAME_INV' );
671 RAISE FND_API.G_EXC_ERROR ;
672 END IF;
673 CLOSE c_get_fp_task_ver_id_frm_name ;
674
675 END IF;
676
677 IF l_debug_mode = 'Y' THEN
678 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Calling PA_PROJ_STRUC_MAPPING_UTILS.CHECK_CREATE_MAPPING_OK ';
679 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
680 END IF;
681
682 -- Call util API to check whether the mapping can be created
683 PA_PROJ_STRUC_MAPPING_UTILS.CHECK_CREATE_MAPPING_OK
684 ( p_task_version_id_WP => l_wp_task_version_id
685 , p_task_version_id_FP => l_fp_task_version_id
686 , p_api_version => p_api_version
687 , p_calling_module => p_calling_module
688 , x_return_status => l_create_mapping_ok
689 , x_msg_count => x_msg_count
690 , x_msg_data => x_msg_data
691 , x_error_message_code => l_error_message_code
692 );
693
694 x_return_status := l_create_mapping_ok;
695
699 -- Mapping can't be created
696 IF l_create_mapping_ok <> FND_API.G_RET_STS_SUCCESS
697 THEN
698
700 IF l_debug_mode = 'Y' THEN
701 Pa_Debug.g_err_stage:= ' PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : '|| l_error_message_code;
702 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
703 l_debug_level3);
704 END IF;
705 Pa_Utils.ADD_MESSAGE
706 ( p_app_short_name => 'PA',
707 p_msg_name => l_error_message_code);
708 RAISE FND_API.G_EXC_ERROR;
709 ELSE
710 -- Mapping Can be created, call public API to create mapping
711 IF l_debug_mode = 'Y' THEN
712 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Calling create'||l_wp_task_version_id||l_fp_task_version_id;
713 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
714 l_debug_level3);
715 END IF;
716
717 --Call public API to create the mapping
718
719 PA_RELATIONSHIP_PUB.CREATE_RELATIONSHIP
720 (
721 p_init_msg_list => FND_API.G_FALSE
722 , p_commit => p_commit
723 , p_debug_mode => l_debug_mode
724 , p_project_id_from => p_project_id
725 , p_task_version_id_from => l_wp_task_version_id
726 , p_project_id_to => p_project_id
727 , p_task_version_id_to => l_fp_task_version_id
728 , p_structure_type => NULL
729 , p_relationship_type => 'M'
730 , p_initiating_element => NULL
731 , x_object_relationship_id => l_relationship_id
732 , x_return_status => x_return_status
733 , x_msg_count => x_msg_count
734 , x_msg_data => x_msg_data
735
736 );
737 END IF;
738 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 IF (p_commit = FND_API.G_TRUE) THEN
742 COMMIT;
743 END IF;
744
745 EXCEPTION
746
747 WHEN FND_API.G_EXC_ERROR THEN
748
749 x_return_status := Fnd_Api.G_RET_STS_ERROR;
750 l_msg_count := Fnd_Msg_Pub.count_msg;
751
752 IF (p_commit = FND_API.G_TRUE) THEN
753 ROLLBACK TO CREATE_MAPPING_PUBLIC;
754 END IF;
755
756 IF l_msg_count = 1 AND x_msg_data IS NULL
757 THEN
758 Pa_Interface_Utils_Pub.get_messages
759 ( p_encoded => Fnd_Api.G_TRUE
760 , p_msg_index => 1
761 , p_msg_count => l_msg_count
762 , p_msg_data => l_msg_data
763 , p_data => l_data
764 , p_msg_index_out => l_msg_index_out);
765 x_msg_data := l_data;
766 x_msg_count := l_msg_count;
767 ELSE
768 x_msg_count := l_msg_count;
769 END IF;
770 IF l_debug_mode = 'Y' THEN
771 Pa_Debug.reset_curr_function;
772 END IF;
773
774 WHEN Invalid_Arg_Exc_WP THEN
775
776 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
777 x_msg_count := 1;
778 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : NULL arguments are passed to the procedure';
779
780 IF (p_commit = FND_API.G_TRUE) THEN
781 ROLLBACK TO CREATE_MAPPING_PUBLIC;
782 END IF;
783
784 Fnd_Msg_Pub.add_exc_msg
785 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
786 , p_procedure_name => 'CREATE_MAPPING'
787 , p_error_text => x_msg_data);
788
789 IF l_debug_mode = 'Y' THEN
790 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
791 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
792 l_debug_level5);
793 Pa_Debug.reset_curr_function;
794 END IF;
795 RAISE;
796
797
798 WHEN OTHERS THEN
799
800 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
801 x_msg_count := 1;
802 x_msg_data := SQLERRM;
803
804 IF (p_commit = FND_API.G_TRUE) THEN
805 ROLLBACK TO CREATE_MAPPING_PUBLIC;
806 END IF;
807 Pa_Debug.g_err_stage:= 'x_msg_count='||x_msg_count;
808 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level5);
809
810
811 Fnd_Msg_Pub.add_exc_msg
812 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
813 , p_procedure_name => 'CREATE_MAPPING'
814 , p_error_text => x_msg_data);
815 Pa_Debug.g_err_stage:= 'x_msg_count='||x_msg_count;
816 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level5);
817
818
819 IF l_debug_mode = 'Y' THEN
820 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
821 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
822 l_debug_level5);
823 Pa_Debug.reset_curr_function;
824 END IF;
825 RAISE;
826 END CREATE_MAPPING ;
827
828
829
830 -- Procedure : UPDATE_MAPPING
834 -- : 1.Map Workplan to Financial Task (HGRID - All Tasks)
831 -- Type : Public Procedure
832 -- Purpose : This API will be used to update the mapping for existing tasks.
833 -- : This API will be called from following pages:
835 -- : 2.Map Workplan Tasks (Selected)
836 -- : 3.Map Financial Tasks
837 -- : 4.UPDATE TASK page
838
839 -- Note : This API will update the mapping , In workplan context
840 -- : -- If no mapping is existing, it will create the mapping if the passed fp_task is valid.
841 -- :
842 -- : -- If mapping exists, and fp task name is changed on the page, the mapping will deleted and new mapping
843 -- : -- with new fp task name will be created.
844 -- : In Financial context
845 -- : -- Similar to workplan
846 -- : If called from self service, the object relationship id will be passed.
847 -- : If this is null,new mapping will be created. If this is not null, exitsting mapping will be deleted.
848 -- : The record version number will also be passed from self service application.
849 -- Assumptions : From Financial context, many WP names will be passed and from Workplan context, only one Wp task ID or Task Name will be passed
850
851 -- Parameters Type Required Description and Purpose
852 -- --------------------------- ------ -------- --------------------------------------------------------
853 -- p_structure_type VARCHAR2 Yes The value will contain the context for which the Update is happening. Its value will be FINANCIAL or WORKPLAN.
854 -- p_project_id NUMBER Yes The project id will be used in case of getting task ids from task names
855 -- p_wp_task_name VARCHAR2 NO If WP task version id is null, then task name should be passed. Incase of financial context this will be multiple names
856 -- p_wp_prnt_str_ver_id NUMBER NO It is required to get the task id from task name.
857 -- p_wp_task_version_id NUMBER Yes The WP task id from which mapping has to be updated
858 -- p_fp_task_name VARCHAR2 NO IF FP task id is not passed, task name will be required
859 -- p_fp_task_version_id NUMBER Yes FP task id in mapping which needs to b updated
860 -- p_object_relationship_id NUMBER NO The Object relationship Id of the existing mapping
861
862 PROCEDURE UPDATE_MAPPING
863 (
864 p_api_version IN NUMBER := 1.0
865 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
866 , p_commit IN VARCHAR2 := FND_API.G_FALSE
867 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
868 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
869 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
870 , p_debug_mode IN VARCHAR2 := 'N'
871 , p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
872 , p_structure_type IN VARCHAR2 := 'WORKPLAN'
873 , p_project_id IN NUMBER
874 , p_wp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
875 , p_wp_prnt_str_ver_id IN NUMBER := FND_API.G_MISS_NUM
876 , p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
877 , p_fp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
878 , p_fp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
879 , p_object_relationship_id IN NUMBER := FND_API.G_MISS_NUM
880 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
881 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
882 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
883 )
884 IS
885 l_msg_count NUMBER := 0;
886 l_data VARCHAR2(2000);
887 l_msg_data VARCHAR2(2000);
888 l_msg_index_out NUMBER;
889 l_debug_mode VARCHAR2(1);
890 l_wp_task_version_id NUMBER ;
891 l_fp_task_version_id NUMBER ;
892 l_notfound BOOLEAN;
893 l_wp_task_version_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
894 l_map_wp_task_ver_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
895 l_wp_task_name_table PA_PROJ_STRUC_MAPPING_PUB.OBJECT_NAME_TABLE_TYPE ;
896 l_parse_return_message VARCHAR2(30);
897 l_proj_element_version_id NUMBER;
898 l_object_relationship_id NUMBER;
899 l_rec_version_num NUMBER;
900
901 l_debug_level2 CONSTANT NUMBER := 2;
902 l_debug_level3 CONSTANT NUMBER := 3;
903 l_debug_level4 CONSTANT NUMBER := 4;
904 l_debug_level5 CONSTANT NUMBER := 5;
905
906
907 -- This cursor selects the task version id given task name , project_id and parent str ver id
908 -- This same cursor can be used for WP tasks
909 CURSOR c_get_wp_task_ver_id_frm_name (l_wp_task_name VARCHAR2 , l_projectid NUMBER ,l_prnt_str_ver_id NUMBER )
910 IS
911 SELECT ppv.element_version_id
912 FROM pa_proj_element_versions ppv, pa_proj_elements pae
913 WHERE pae.name = l_wp_task_name
914 AND pae.project_id = l_projectid
915 AND ppv.proj_element_id = pae.proj_element_id
916 AND ppv.parent_structure_version_id = l_prnt_str_ver_id
917 AND pae.object_type = 'PA_TASKS'
918 AND pae.project_id = ppv.project_id;
919
920
921 -- Select the object relationship id corresponding to existing mapping
922 CURSOR cur_get_object_relationship_id (l_wp_task_version_id NUMBER )
923 IS
927 AND relationship_type = 'M';
924 SELECT object_relationship_id , record_version_number
925 FROM pa_object_relationships
926 WHERE object_id_from1 = l_wp_task_version_id
928
929
930 --select the fp_task_version_id for the corresponding name , it does not require parent str ver id
931 CURSOR c_get_fp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER)
932 IS
933 SELECT
934 ppv.element_version_id
935 FROM
936 pa_proj_element_versions ppv
937 , pa_proj_elements pae
938 , pa_proj_elem_ver_structure str_ver
939 , pa_proj_structure_types str_type
940 , pa_structure_types
941 WHERE pae.name = l_task_name
942 AND pae.project_id = l_project_id
943 AND ppv.project_id = l_project_id
944 AND ppv.proj_element_id = pae.proj_element_id
945 AND ppv.parent_structure_version_id = str_ver.element_version_id
946 AND pae.object_type = 'PA_TASKS'
947 AND ppv.object_type = 'PA_TASKS'
948 AND str_ver.project_id = l_project_id
949 AND str_ver.proj_element_id = str_type.proj_element_id
950 AND str_type.structure_type_id = pa_structure_types.structure_type_id
951 AND pa_structure_types.structure_type = 'FINANCIAL'
952 AND ppv.project_id = pae.project_id;
953
954 BEGIN
955
956 x_msg_count := 0;
957 x_return_status := FND_API.G_RET_STS_SUCCESS;
958 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
959
960
961 IF l_debug_mode = 'Y' THEN
962 PA_DEBUG.set_curr_function( p_function => 'UPDATE_MAPPING',
963 p_debug_mode => l_debug_mode );
964 END IF;
965
966 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
967 FND_MSG_PUB.initialize;
968 END IF;
969
970 IF ( p_commit = FND_API.G_TRUE ) THEN
971 savepoint UPDATE_MAPPING_PUBLIC;
972 END IF;
973
974 IF l_debug_mode = 'Y' THEN
975 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Printing Input parameters';
976 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
977 l_debug_level3);
978
979 Pa_Debug.WRITE(g_module_name,'p_structure_type'||':'||p_structure_type,
980 l_debug_level3);
981
982 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
983 l_debug_level3);
984
985 Pa_Debug.WRITE(g_module_name,'p_wp_task_name'||':'||p_wp_task_name,
986 l_debug_level3);
987
988 Pa_Debug.WRITE(g_module_name,'p_wp_prnt_str_ver_id'||':'||p_wp_prnt_str_ver_id,
989 l_debug_level3);
990 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
991 l_debug_level3);
992 Pa_Debug.WRITE(g_module_name,'p_fp_task_name'||':'||p_fp_task_name,
993 l_debug_level3);
994
995 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
996 l_debug_level3);
997 END IF;
998
999 IF l_debug_mode = 'Y' THEN
1000 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Validating Input parameters';
1001 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1002 l_debug_level3);
1003 END IF;
1004
1005 -- if PROJECT id IS NULL RAISE ERROR MESSAGE
1006 IF (p_project_id is NULL)
1007 THEN
1008 IF l_debug_mode = 'Y' THEN
1009 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : project id can not be null';
1010 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1011 l_debug_level3);
1012 END IF;
1013 RAISE Invalid_Arg_Exc_WP;
1014 END IF;
1015
1016 -- if wp task id and wp task name both are null , or
1017 -- if fp task id and and fp task name both are null
1018 -- raise error
1019 IF (
1020 (
1021 ( p_wp_task_name is NULL OR p_wp_task_name = FND_API.G_MISS_CHAR ) AND
1022 ( p_wp_task_version_id is NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM )
1023 ) AND
1024 (
1025 ( p_fp_task_name is NULL OR p_fp_task_name = FND_API.G_MISS_CHAR ) AND
1026 ( p_fp_task_version_id is NULL OR p_fp_task_version_id = FND_API.G_MISS_NUM )
1027 )
1028 )
1029 THEN
1030 IF l_debug_mode = 'Y' THEN
1031 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Both of tasks id and tasks name are null';
1032 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1033 l_debug_level3);
1034 END IF;
1035 RAISE Invalid_Arg_Exc_WP;
1036 END IF;
1037
1038 -- Parent structure version id is essential to get task id using task name , so it should be validated
1039 IF (
1040 ( p_wp_task_version_id IS NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM ) AND
1041 (
1042 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_NUM ) AND
1043 ( p_wp_prnt_str_ver_id IS NULL OR p_wp_prnt_str_ver_id = FND_API.G_MISS_NUM )
1044 )
1045 )
1046 THEN
1047 IF l_debug_mode = 'Y' THEN
1048 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : parent structue version id can not be null, if wp task name is not null';
1052 RAISE Invalid_Arg_Exc_WP;
1049 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1050 l_debug_level3);
1051 END IF;
1053 END IF;
1054
1055 IF ( p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
1056 THEN
1057 l_wp_task_version_id := p_wp_task_version_id;
1058 ELSIF (
1059 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR ) AND
1060 ( p_wp_prnt_str_ver_id IS NOT NULL AND p_wp_prnt_str_ver_id <> FND_API.G_MISS_NUM )
1061 )
1062 THEN
1063 --get the wp_task_version_id and place it in l_wp_task_version_id;
1064 OPEN c_get_wp_task_ver_id_frm_name ( p_wp_task_name , p_project_id , p_wp_prnt_str_ver_id );
1065 FETCH c_get_wp_task_ver_id_frm_name INTO l_wp_task_version_id;
1066 IF (c_get_wp_task_ver_id_frm_name%NOTFOUND)
1067 THEN
1068 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1069 p_msg_name => 'PA_OBJECT_NAME_INV' );
1070 RAISE FND_API.G_EXC_ERROR ;
1071 END IF;
1072 CLOSE c_get_wp_task_ver_id_frm_name;
1073
1074 END IF;
1075 IF ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
1076 THEN
1077 l_fp_task_version_id := p_fp_task_version_id;
1078
1079 ELSIF ( p_fp_task_name IS NOT NULL AND p_fp_task_name <> FND_API.G_MISS_CHAR
1080 )
1081 THEN
1082 -- get the fp_task_version_id and place it in l_fp_task_version_id;
1083 -- Assuming there will be only one version for the FP task.Parent structure version id is passed
1084 -- in view that in future there wil be versions for financial tasks also
1085 OPEN c_get_fp_task_ver_id_frm_name ( p_fp_task_name, p_project_id );
1086 FETCH c_get_fp_task_ver_id_frm_name INTO l_fp_task_version_id;
1087 IF (c_get_fp_task_ver_id_frm_name%NOTFOUND)
1088 THEN
1089 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1090 p_msg_name => 'PA_OBJECT_NAME_INV' );
1091 RAISE FND_API.G_EXC_ERROR ;
1092 END IF;
1093 CLOSE c_get_fp_task_ver_id_frm_name;
1094 END IF;
1095
1096 IF ( ( p_object_relationship_id IS NULL OR p_object_relationship_id = FND_API.G_MISS_NUM )
1097 AND
1098 p_calling_module <> 'SELF_SERVICE'
1099 )
1100 THEN
1101 OPEN cur_get_object_relationship_id (l_wp_task_version_id);
1102 FETCH cur_get_object_relationship_id INTO l_object_relationship_id , l_rec_version_num;
1103 CLOSE cur_get_object_relationship_id;
1104 ELSE
1105 l_object_relationship_id := p_object_relationship_id;
1106 l_rec_version_num := p_record_version_number;
1107 END IF;
1108
1109 --If l_object_relationship_id is not null
1110 IF ( l_object_relationship_id IS NOT NULL AND l_object_relationship_id <> FND_API.G_MISS_NUM )
1111 THEN
1112 --delete the existing mapping
1113 PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
1114 (
1115 p_api_version => p_api_version
1116 , p_init_msg_list => FND_API.G_FALSE
1117 , p_commit => p_commit
1118 , p_validate_only => p_validate_only
1119 , p_calling_module => 'SELF_SERVICE'
1120 , p_debug_mode => l_debug_mode
1121 , p_object_relationship_id => l_object_relationship_id
1122 , p_record_version_number => l_rec_version_num
1123 , x_return_status => x_return_status
1124 , x_msg_count => x_msg_count
1125 , x_msg_data => x_msg_data
1126 );
1127 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1128 THEN
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131 END IF;
1132
1133 --IF l_fp_task_ver_id is not null
1134 IF (
1135 (l_fp_task_version_id IS NOT NULL AND l_fp_task_version_id <> FND_API.G_MISS_NUM ) AND
1136 (l_wp_task_version_id IS NOT NULL AND l_wp_task_version_id <> FND_API.G_MISS_NUM )
1137 )
1138 THEN
1139 --create new mapping with l_wp_task_ver_id and l_fp_task_ver_id
1140 -- Call CREATE_MAPPING
1141 PA_PROJ_STRUC_MAPPING_PUB.CREATE_MAPPING(
1142 p_wp_task_version_id => l_wp_task_version_id
1143 , p_fp_task_version_id => l_fp_task_version_id
1144 , p_project_id => p_project_id
1145 , p_init_msg_list => FND_API.G_FALSE
1146 , p_commit => p_commit
1147 , p_debug_mode => l_debug_mode
1148 , x_return_status => x_return_status
1149 , x_msg_count => x_msg_count
1150 , x_msg_data => x_msg_data
1151 );
1152 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
1153 THEN
1154 RAISE FND_API.G_EXC_ERROR;
1155 END IF;
1156 END IF;
1157
1158 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1159 THEN
1160 RAISE FND_API.G_EXC_ERROR;
1161 END IF;
1162
1163 IF ( p_commit = FND_API.G_TRUE ) THEN
1164 COMMIT;
1165 END IF;
1166
1167 -- Bug 10053423
1168 IF l_debug_mode = 'Y' THEN
1169 pa_debug.reset_curr_function;
1170 pa_debug.write(g_module_name, 'UPDATE_MAPPING: Exiting succesfully',5);
1171 END IF;
1172
1173 EXCEPTION
1174
1175 WHEN FND_API.G_EXC_ERROR THEN
1179
1176
1177 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1178 l_msg_count := Fnd_Msg_Pub.count_msg;
1180 IF ( p_commit = FND_API.G_TRUE ) THEN
1181 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1182 END IF;
1183
1184 IF cur_get_object_relationship_id%ISOPEN THEN
1185 CLOSE cur_get_object_relationship_id;
1186 END IF;
1187
1188 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1189 CLOSE c_get_fp_task_ver_id_frm_name;
1190 END IF;
1191
1192 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1193 CLOSE c_get_wp_task_ver_id_frm_name;
1194 END IF;
1195
1196 IF l_msg_count = 1 AND x_msg_data IS NULL
1197 THEN
1198 Pa_Interface_Utils_Pub.get_messages
1199 ( p_encoded => Fnd_Api.G_TRUE
1200 , p_msg_index => 1
1201 , p_msg_count => l_msg_count
1202 , p_msg_data => l_msg_data
1203 , p_data => l_data
1204 , p_msg_index_out => l_msg_index_out);
1205 x_msg_data := l_data;
1206 x_msg_count := l_msg_count;
1207 ELSE
1208 x_msg_count := l_msg_count;
1209 END IF;
1210 IF l_debug_mode = 'Y' THEN
1211 Pa_Debug.reset_curr_function;
1212 END IF;
1213
1214 WHEN Invalid_Arg_Exc_WP THEN
1215
1216 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1217 x_msg_count := Fnd_Msg_Pub.count_msg;
1218 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Some parameters are NULL';
1219
1220 IF ( p_commit = FND_API.G_TRUE ) THEN
1221 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1222 END IF;
1223
1224 IF cur_get_object_relationship_id%ISOPEN THEN
1225 CLOSE cur_get_object_relationship_id;
1226 END IF;
1227
1228 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1229 CLOSE c_get_wp_task_ver_id_frm_name;
1230 END IF;
1231
1232 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1233 CLOSE c_get_fp_task_ver_id_frm_name;
1234 END IF;
1235
1236 Fnd_Msg_Pub.add_exc_msg
1237 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1238 , p_procedure_name => 'UPDATE_MAPPING'
1239 , p_error_text => x_msg_data);
1240
1241 IF l_debug_mode = 'Y' THEN
1242 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1243 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1244 l_debug_level5);
1245 Pa_Debug.reset_curr_function;
1246 END IF;
1247
1248
1249 WHEN OTHERS THEN
1250
1251 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1252 x_msg_count := 1;
1253 x_msg_data := SQLERRM;
1254
1255 IF ( p_commit = FND_API.G_TRUE ) THEN
1256 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1257 END IF;
1258
1259 IF cur_get_object_relationship_id%ISOPEN THEN
1260 CLOSE cur_get_object_relationship_id;
1261 END IF;
1262
1263 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1264 CLOSE c_get_fp_task_ver_id_frm_name;
1265 END IF;
1266
1267 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1268 CLOSE c_get_wp_task_ver_id_frm_name;
1269 END IF;
1270
1271 Fnd_Msg_Pub.add_exc_msg
1272 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1273 , p_procedure_name => 'UPDATE_MAPPING'
1274 , p_error_text => x_msg_data);
1275
1276 IF l_debug_mode = 'Y' THEN
1277 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1278 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1279 l_debug_level5);
1280 Pa_Debug.reset_curr_function;
1281 END IF;
1282
1283 END UPDATE_MAPPING ;
1284
1285
1286 -- Procedure : COPY_MAPPING
1287 -- Type : Public Procedure
1288 -- Purpose : This API copies the mappings from source structure tasks to destinations structure tasks
1289 -- : This will be called from copy_project, create working version, publish version api.
1290 -- :
1291 -- Note : The API's function will depend on the P_CONTEXT.
1292 -- : 1. P_CONTEXT is COPY_PROJECT: The structure version IDs passed will be ignored in this case and they will taken from the database.
1293 -- : Mapping will be created depending as in existing src stuructures
1294 -- : 2. P_CONTEXT is (create or publish): In this context, the structure version ids will be passed and src project id will be ignored
1295 -- :
1296 -- Assumptions : This will be called after the copy project has been called ,
1297 -- : so that the element version ids of the destination projects are available.
1298 -- : Only for split structure
1299
1300 -- Parameters Type Required Description and Purpose
1301 -- --------------------------- ------ -------- --------------------------------------------------------
1305 -- p_src_str_version_id IN NUMBER The structure version id from source project
1302 -- p_context IN VARCHAR2 This will tell whether the context is copy_project or other
1303 -- p_src_project_id IN NUMBER The project id which is being copied
1304 -- p_dest_project_id IN NUMBER The project id to which it is being copied
1306 -- p_dest_str_version_id IN NUMBER The structure version id from source at the destinantion project
1307
1308 PROCEDURE COPY_MAPPING
1309 (
1310 p_api_version IN NUMBER := 1.0
1311 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1312 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1313 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
1314 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1315 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1316 , p_debug_mode IN VARCHAR2 := 'N'
1317 , p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1318 , p_context IN VARCHAR2
1319 , p_src_project_id IN NUMBER := FND_API.G_MISS_NUM
1320 , p_dest_project_id IN NUMBER := FND_API.G_MISS_NUM
1321 , p_src_str_version_id IN NUMBER := FND_API.G_MISS_NUM
1322 , p_dest_str_version_id IN NUMBER := FND_API.G_MISS_NUM
1323 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1324 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1325 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1326
1327 )
1328 IS
1329
1330 l_msg_count NUMBER := 0;
1331 l_data VARCHAR2(2000);
1332 l_msg_data VARCHAR2(2000);
1333 l_msg_index_out NUMBER;
1334 l_debug_mode VARCHAR2(1);
1335 l_user_id NUMBER;
1336 l_debug_level2 CONSTANT NUMBER := 2;
1337 l_debug_level3 CONSTANT NUMBER := 3;
1338 l_debug_level4 CONSTANT NUMBER := 4;
1339 l_debug_level5 CONSTANT NUMBER := 5;
1340
1341
1342
1343 -- This cursor will select the element_version_id , source structure version id and structure type for the passed
1344 -- project id.
1345 CURSOR c_get_dest_structures ( l_dest_project_id NUMBER )
1346 IS
1347 SELECT
1348 -- pelever.element_version_id AS structure_version Commented for Bug14197220
1349 DISTINCT pelever.attribute15 AS src_str_version_id
1350 --, pstrType.structure_type AS structure_type Commented for Bug 14197220 As these column not at all used
1351 FROM
1352 pa_proj_element_versions pelever,
1353 pa_proj_structure_types prjstrType,
1354 pa_structure_types pstrType
1355 WHERE
1356 pelever.object_type = 'PA_STRUCTURES'
1357 AND
1358 pelever.project_id = l_dest_project_id
1359 AND
1360 prjstrType.proj_element_id = pelever.proj_element_id
1361 AND
1362 pstrType.structure_type_id = prjstrType.structure_type_id
1363 AND
1364 pstrType.structure_type = 'WORKPLAN'
1365 AND
1366 pstrType.structure_type_class_code = 'WORKPLAN';
1367
1368
1369 --This cursor will select all the tasks which are mapped for a particular structure which needs to be passed.
1370 CURSOR c_get_mapped_tasks (l_parent_str_version_id NUMBER, l_project_id NUMBER)
1371 IS
1372 SELECT
1373 paObrel.OBJECT_ID_FROM1 as OBJECT_ID_FROM1
1374 , paObrel.OBJECT_ID_TO1 as OBJECT_ID_TO1
1375 FROM
1376 pa_proj_element_versions elever
1377 , pa_object_relationships paObrel
1378 WHERE elever.PARENT_STRUCTURE_VERSION_ID = l_parent_str_version_id
1379 AND paObrel.OBJECT_ID_FROM1 = elever.element_version_id
1380 AND elever.project_id = l_project_id
1381 AND paObrel.RELATIONSHIP_TYPE = 'M'
1382 AND elever.object_type = 'PA_TASKS';
1383
1384 -- This cursor will get the task version id from pa_proj_element_versions , where src task version id is passed
1385 CURSOR c_get_mapped_task_version ( l_src_task_version_id NUMBER, l_project_id NUMBER )
1386 IS
1387 SELECT ELEMENT_VERSION_ID
1388 FROM PA_PROJ_ELEMENT_VERSIONS ELEVER
1389 WHERE ELEVER.ATTRIBUTE15 = l_src_task_version_id
1390 AND ELEVER.OBJECT_TYPE = 'PA_TASKS'
1391 AND ELEVER.PROJECT_ID = l_project_id;
1392
1393 CURSOR c_get_mapped_task_id ( l_task_ver_id NUMBER , l_str_version_id NUMBER, l_project_id NUMBER )
1394 IS
1395 SELECT proj_element_id
1396 FROM pa_proj_element_versions
1397 WHERE OBJECT_TYPE = 'PA_TASKS'
1398 AND element_version_id = l_task_ver_id
1399 AND parent_structure_version_id = l_str_version_id
1400 AND pa_proj_element_versions.project_id = l_project_id;
1401
1402 CURSOR c_get_mapped_task_ver_id ( l_task_id NUMBER , l_str_version_id NUMBER, l_project_id NUMBER )
1403 IS
1404 SELECT element_version_id
1405 FROM pa_proj_element_versions
1406 WHERE OBJECT_TYPE = 'PA_TASKS'
1407 AND parent_structure_version_id = l_str_version_id
1408 AND proj_element_id = l_task_id
1409 AND pa_proj_element_versions.project_id = l_project_id;
1410
1411 l_src_from_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1412 l_src_to_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1413
1414 l_dest_from_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1415 l_dest_to_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1416
1417 l_from_task_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_ID_TABLE_TYPE;
1418 l_to_task_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_ID_TABLE_TYPE;
1419
1420 l_from_dest_task_ver_id PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1424
1421 l_to_dest_task_ver_id PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1422
1423 BEGIN
1425
1426 x_msg_count := 0;
1427 l_user_id := FND_GLOBAL.USER_ID;
1428 x_return_status := FND_API.G_RET_STS_SUCCESS;
1429 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1430
1431
1432 IF l_debug_mode = 'Y' THEN
1433 PA_DEBUG.set_curr_function( p_function => 'COPY_MAPPING',
1434 p_debug_mode => l_debug_mode );
1438 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : Printing Input parameters';
1435 END IF;
1436
1437 IF l_debug_mode = 'Y' THEN
1439 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1440 l_debug_level3);
1441 Pa_Debug.WRITE(g_module_name,'p_context'||p_context,l_debug_level3);
1442 Pa_Debug.WRITE(g_module_name,'p_src_project_id'||p_src_project_id,l_debug_level3);
1443 Pa_Debug.WRITE(g_module_name,'p_dest_project_id'||p_dest_project_id,l_debug_level3);
1444 Pa_Debug.WRITE(g_module_name,'p_src_str_version_id'||p_src_str_version_id,l_debug_level3);
1445 Pa_Debug.WRITE(g_module_name,'p_dest_str_version_id'||p_dest_str_version_id,l_debug_level3);
1446 END IF;
1447
1448 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1449 FND_MSG_PUB.initialize;
1450 END IF;
1451
1452 IF l_debug_mode = 'Y' THEN
1453 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : Validating Input parameters';
1454 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1455 l_debug_level3);
1456 END IF;
1457
1458 IF ( p_context IS NULL ) THEN
1459 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : p_context is mandatory and cant be null';
1460 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1461 l_debug_level3);
1462
1463 RAISE Invalid_Arg_Exc_WP;
1464 END IF;
1465
1466 --If context is copy_project, src and destination project id must be passed and they should not be same
1467 IF ( p_context ='COPY_PROJECT' ) THEN
1468 IF
1469 (
1470 ( p_src_project_id IS NULL OR p_src_project_id = FND_API.G_MISS_NUM )
1471 OR ( p_dest_project_id IS NULL OR p_dest_project_id = FND_API.G_MISS_NUM )
1472 OR ( p_src_project_id = p_dest_project_id )
1473 )
1474 THEN
1475 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : if p_context is Copy Project , both src and destination project id will be required';
1476 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1477 l_debug_level3);
1478
1479 RAISE Invalid_Arg_Exc_WP;
1480
1481 ELSE
1482 -- Code for copy-project context
1483 --get the destination structures
1484 --This for loop gets all the workplan structures in the destination project id.
1485 --For each workplan structure , find the mapped task version id in source project
1486 --and corresponding task version id in destination project
1487 FOR str_rec IN c_get_dest_structures (p_dest_project_id) LOOP
1488
1489 --IF str_rec.structure_type = 'WORKPLAN' THEN
1490
1491 --For each workplan structure , find the mapped task version id in source project
1492 --delete all elements from the table before using it;
1493 l_src_to_tasks_id_tbl.DELETE;
1494 l_src_from_tasks_id_tbl.DELETE;
1495 OPEN c_get_mapped_tasks ( str_rec.src_str_version_id , p_src_project_id );
1496 FETCH c_get_mapped_tasks BULK COLLECT
1497 INTO l_src_from_tasks_id_tbl , l_src_to_tasks_id_tbl;
1498 CLOSE c_get_mapped_tasks ;
1499
1500 --For each task version id in source, get the corresponding one in destination project
1501 l_dest_from_tasks_id_tbl.DELETE;
1502 FOR iCounter in 1..l_src_from_tasks_id_tbl.COUNT LOOP
1503
1504 OPEN c_get_mapped_task_version ( l_src_from_tasks_id_tbl ( iCounter ) , p_dest_project_id ) ;
1505 FETCH c_get_mapped_task_version INTO l_dest_from_tasks_id_tbl ( iCounter ) ;
1506 CLOSE c_get_mapped_task_version ;
1507
1508 END LOOP;
1509 --For each task version id in source, get the corresponding one in destination project
1510 l_dest_to_tasks_id_tbl.DELETE;
1514 FETCH c_get_mapped_task_version INTO l_dest_to_tasks_id_tbl ( iCounter ) ;
1511 FOR iCounter in 1..l_src_to_tasks_id_tbl.COUNT LOOP
1512
1513 OPEN c_get_mapped_task_version ( l_src_to_tasks_id_tbl ( iCounter ) , p_dest_project_id ) ;
1515 CLOSE c_get_mapped_task_version ;
1516
1517 END LOOP;
1518
1519
1520 -- Create Mapping from the dest task ids using BULK INSERT
1521 --added if condition bug.3578265,3574885
1522 --rtarway bug 3916440, the forall should be called only when l_src_to_tasks_id_tbl is not empty
1523 IF (l_dest_from_tasks_id_tbl.count > 0 AND l_dest_to_tasks_id_tbl.COUNT > 0)
1524 THEN
1525 FORALL iCounter IN l_dest_from_tasks_id_tbl.FIRST..l_dest_from_tasks_id_tbl.LAST
1526
1527 INSERT INTO PA_OBJECT_RELATIONSHIPS
1528 (
1529 object_relationship_id,
1530 object_type_from,
1531 object_id_from1,
1532 object_id_from2,
1533 object_id_from3,
1534 object_id_from4,
1535 object_id_from5,
1536 object_type_to,
1537 object_id_to1,
1538 object_id_to2,
1539 object_id_to3,
1540 object_id_to4,
1541 object_id_to5,
1542 relationship_type,
1543 relationship_subtype,
1544 lag_day,
1545 imported_lag,
1546 priority,
1547 pm_product_code,
1548 Record_Version_Number,
1549 CREATED_BY,
1550 CREATION_DATE,
1551 LAST_UPDATED_BY,
1552 LAST_UPDATE_DATE,
1553 LAST_UPDATE_LOGIN,
1554 weighting_percentage
1555 )
1556
1557 VALUES ( pa_object_relationships_s.nextval,
1558 'PA_TASKS',
1559 l_dest_from_tasks_id_tbl(iCounter),
1560 NULL,
1561 NULL,
1562 NULL,
1563 NULL,
1564 'PA_TASKS',
1565 l_dest_to_tasks_id_tbl(iCounter),
1566 NULL,
1567 NULL,
1568 NULL,
1569 NULL,
1570 'M',
1571 NULL,
1572 NULL,
1573 NULL,
1574 NULL,
1575 NULL,
1576 1,
1577 l_user_id,
1578 sysdate,
1579 l_user_id,
1580 sysdate,
1581 l_user_id,
1582 NULL
1583 );
1584 END IF;
1585 --END IF;--If the condition is workplan
1586 END LOOP;--End of For loop
1587 END IF;-- if p_src_project_id is not null
1588
1589 -- For other than copy mapping, e.g. create working version or publish ,
1590 -- src_project_id and structure version id should be considered and hence these cant be null
1594 THEN
1591 -- It is assumed that p_src_project_id will be passed in this case
1592 -- src_project_id will make the c_get_mapped_tasks efficient with project Id filter.
1593 ELSIF (p_context = 'CREATE_WORKING_VERSION' OR p_context = 'PUBLISH_VERSION')
1595 IF ( ( p_src_str_version_id IS NULL OR p_src_str_version_id = FND_API.G_MISS_NUM )
1596 OR( p_dest_str_version_id IS NULL OR p_dest_str_version_id = FND_API.G_MISS_NUM )
1597 OR( p_src_project_id IS NULL OR p_src_project_id = FND_API.G_MISS_NUM )
1598 ) THEN
1599
1600 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : if p_context is create_working_version,both src and destination str ver id will be required';
1601 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1602 l_debug_level3);
1603 RAISE Invalid_Arg_Exc_WP;
1604 END IF;
1605
1606 -- get the mapped task-version-id for the source str version id.
1607 l_src_from_tasks_id_tbl.DELETE;
1608 l_src_to_tasks_id_tbl.DELETE;
1609 OPEN c_get_mapped_tasks ( p_src_str_version_id , p_src_project_id );
1610 FETCH c_get_mapped_tasks BULK COLLECT
1611 INTO l_src_from_tasks_id_tbl , l_src_to_tasks_id_tbl;
1612 CLOSE c_get_mapped_tasks ;
1613 -- for each task version id mapped, get the proj_element_id
1614 l_from_task_id_tbl.DELETE;
1615 FOR iCounter in 1..l_src_from_tasks_id_tbl.COUNT LOOP
1616 OPEN c_get_mapped_task_id ( l_src_from_tasks_id_tbl ( iCounter ),
1617 p_src_str_version_id , p_src_project_id );
1618 FETCH c_get_mapped_task_id INTO l_from_task_id_tbl ( iCounter );
1619 CLOSE c_get_mapped_task_id ;
1620 END LOOP;
1621
1622
1623
1624 --for each task id mapped, get the task version ids for the dest structure version id, project should be same
1625 l_from_dest_task_ver_id.DELETE;
1626 FOR iCounter in 1..l_from_task_id_tbl.COUNT LOOP
1627 OPEN c_get_mapped_task_ver_id ( l_from_task_id_tbl (iCounter) ,
1628 p_dest_str_version_id , p_src_project_id );
1629 FETCH c_get_mapped_task_ver_id INTO l_from_dest_task_ver_id ( iCounter );
1630 CLOSE c_get_mapped_task_ver_id;
1631 END LOOP;
1632
1633 Pa_Debug.WRITE(g_module_name,'l_from_task_id_tbl.COUNT '||l_from_task_id_tbl.COUNT ,l_debug_level3);
1634
1635 -- Insert into PA_OBJECT_RELATIONSHIPS
1636 --bug 3574885, the forall should be called only when l_from_dest_task_ver_id is not empty
1637 --rtarway ,bug 3916440, the forall should be called only when l_src_to_tasks_id_tbl is not empty
1638 IF (l_from_dest_task_ver_id.COUNT > 0 AND l_src_to_tasks_id_tbl.count > 0)
1639 THEN
1640 FORALL iCounter IN l_from_dest_task_ver_id.FIRST..l_from_dest_task_ver_id.LAST
1641
1642 INSERT INTO PA_OBJECT_RELATIONSHIPS (
1643 object_relationship_id,
1644 object_type_from,
1645 object_id_from1,
1646 object_id_from2,
1647 object_id_from3,
1648 object_id_from4,
1649 object_id_from5,
1650 object_type_to,
1651 object_id_to1,
1652 object_id_to2,
1653 object_id_to3,
1654 object_id_to4,
1655 object_id_to5,
1656 relationship_type,
1657 relationship_subtype,
1658 lag_day,
1659 imported_lag,
1660 priority,
1661 pm_product_code,
1662 Record_Version_Number,
1663 CREATED_BY,
1664 CREATION_DATE,
1665 LAST_UPDATED_BY,
1666 LAST_UPDATE_DATE,
1667 LAST_UPDATE_LOGIN,
1668 weighting_percentage)
1669 VALUES ( pa_object_relationships_s.nextval,
1670 'PA_TASKS',
1671 l_from_dest_task_ver_id(iCounter),
1672 NULL,
1673 NULL,
1674 NULL,
1675 NULL,
1676 'PA_TASKS',
1677 l_src_to_tasks_id_tbl(iCounter),
1678 NULL,
1679 NULL,
1680 NULL,
1681 NULL,
1682 'M',
1683 NULL,
1684 NULL,
1685 NULL,
1686 NULL,
1687 NULL,
1688 1,
1689 l_user_id,
1690 sysdate,
1691 l_user_id,
1692 sysdate,
1693 l_user_id,
1694 NULL
1695 );
1696 END IF;
1697 END IF;
1698
1699 Pa_Debug.WRITE(g_module_name,'After Completing insert',l_debug_level3);
1700
1701 EXCEPTION
1702
1703 WHEN FND_API.G_EXC_ERROR THEN
1704
1705 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1706 l_msg_count := Fnd_Msg_Pub.count_msg;
1707
1708 IF l_msg_count = 1 AND x_msg_data IS NULL
1709 THEN
1713 , p_msg_count => l_msg_count
1710 Pa_Interface_Utils_Pub.get_messages
1711 ( p_encoded => Fnd_Api.G_TRUE
1712 , p_msg_index => 1
1714 , p_msg_data => l_msg_data
1715 , p_data => l_data
1716 , p_msg_index_out => l_msg_index_out);
1717 x_msg_data := l_data;
1718 x_msg_count := l_msg_count;
1719 ELSE
1720 x_msg_count := l_msg_count;
1721 END IF;
1722 IF l_debug_mode = 'Y' THEN
1723 Pa_Debug.reset_curr_function;
1724 END IF;
1725
1726 WHEN Invalid_Arg_Exc_WP THEN
1727
1728 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1729 x_msg_count := 1;
1730 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
1731
1732 Fnd_Msg_Pub.add_exc_msg
1733 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1734 , p_procedure_name => 'COPY_MAPPING'
1735 , p_error_text => x_msg_data);
1736
1737 IF l_debug_mode = 'Y' THEN
1738 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1739 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1740 l_debug_level5);
1741 Pa_Debug.reset_curr_function;
1742 END IF;
1743 RAISE;
1744
1745 WHEN OTHERS THEN
1746
1747 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1748 x_msg_count := 1;
1749 x_msg_data := SQLERRM;
1750
1751
1752 Fnd_Msg_Pub.add_exc_msg
1753 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1754 , p_procedure_name => 'COPY_MAPPING'
1755 , p_error_text => x_msg_data);
1756
1757 IF l_debug_mode = 'Y' THEN
1758 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1759 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1760 l_debug_level5);
1761 Pa_Debug.reset_curr_function;
1762 END IF;
1763 RAISE;
1764
1765 END COPY_MAPPING ;
1766
1767
1768
1769 -- Procedure : DELETE_ALL_MAPPING
1770 -- Type : Public Procedure
1771 -- Purpose : This API will be called when we change a split mapping project to split no mapping.
1772 -- :
1773 -- Note : 1. Get all the financial tasks of the passed project id.
1774 -- : 2. If any of these tasks is existing in PA_OBJECT_RELATIONSHIPS, with relationship_type 'M'
1775 -- : delete the record in pa_object_relationships
1776 -- Assumptions : The financial structure will have only one version in any case whether versioning enabled or disabled
1777
1778 -- Parameters Type Required Description and Purpose
1779 -- --------------------------- ------ -------- --------------------------------------------------------
1780 -- p_project_id NUMBER Y The project id for which the mappings have to be deleted
1781
1782 PROCEDURE DELETE_ALL_MAPPING
1783 (
1784 p_api_version IN NUMBER := 1.0
1785 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1786 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1787 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
1788 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1789 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1790 , p_debug_mode IN VARCHAR2 := 'N'
1791 , p_project_id IN NUMBER
1792 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1793 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1794 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1795 )
1796 IS
1797 l_msg_count NUMBER := 0;
1798 l_data VARCHAR2(2000);
1799 l_msg_data VARCHAR2(2000);
1800 l_msg_index_out NUMBER;
1801 l_debug_mode VARCHAR2(1);
1802
1803
1804 l_debug_level2 CONSTANT NUMBER := 2;
1805 l_debug_level3 CONSTANT NUMBER := 3;
1806 l_debug_level4 CONSTANT NUMBER := 4;
1807 l_debug_level5 CONSTANT NUMBER := 5;
1808 --Table to collect all the mapped financial task version id
1809 l_mapped_obj_rel_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJ_REL_ID_TABLE_TYPE;
1810
1811 --This cursor will give all mapped financial tasks and object_relationships_id for the passed project id.
1812 CURSOR c_get_fin_task_ver_id (l_project_id NUMBER)
1813 IS
1814 SELECT
1815 obRel.object_relationship_id
1816 FROM
1817 pa_proj_element_versions elever1
1818 , pa_proj_element_versions elever2
1819 -- , pa_proj_structure_types projStrType Bug 3693235 Performance Fix
1820 -- , pa_structure_types strType Bug 3693235 Performance Fix
1821 , pa_object_relationships obRel
1822 WHERE
1823 elever1.object_type = 'PA_TASKS'
1824 AND elever1.parent_structure_version_id = elever2.element_version_id
1825 AND elever2.object_type = 'PA_STRUCTURES'
1826 -- Bug 3693235 Performance Fix
1827 --AND elever2.proj_element_id = projStrType.proj_element_id
1828 --AND projStrType.structure_type_id = strType.structure_type_id
1829 --AND strType.structure_type = 'FINANCIAL'
1833 AND elever1.project_id = elever2.project_id
1830 AND exists (SELECT 'xyz' FROM pa_proj_structure_types WHERE proj_element_id = elever2.proj_element_id and structure_type_id = 6) -- Bug 3693235 Performance Fix
1831 AND elever1.project_id = l_project_id
1832 AND elever2.project_id = l_project_id
1834 AND elever1.element_version_id = obRel.object_id_to1
1835 AND obRel.relationship_type='M'
1836 -- Bug 3693235 Performance Fix
1837 AND obRel.object_type_from = 'PA_TASKS'
1838 AND obRel.object_type_to = 'PA_TASKS'
1839 ;
1840
1841
1842 BEGIN
1843
1844 x_msg_count := 0;
1845 x_return_status := FND_API.G_RET_STS_SUCCESS;
1846
1847 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1848 --l_debug_mode := NVL(p_debug_mode,'N');
1849 IF l_debug_mode = 'Y' THEN
1850 PA_DEBUG.set_curr_function( p_function => 'DELETE_ALL_MAPPING',
1851 p_debug_mode => l_debug_mode );
1852 END IF;
1853
1854 IF l_debug_mode = 'Y' THEN
1855 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Printing Input parameters';
1856 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1857 l_debug_level3);
1858
1859 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
1860 l_debug_level3);
1861 END IF;
1862
1863 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1864 FND_MSG_PUB.initialize;
1865 END IF;
1866
1867 IF (p_commit = FND_API.G_TRUE) THEN
1868 savepoint DELETE_ALL_MAPPING_PUBLIC;
1869 END IF;
1870
1871 IF l_debug_mode = 'Y' THEN
1872 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Validating Input parameters';
1873 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1874 l_debug_level3);
1875 END IF;
1876
1877 IF ( p_project_id IS NULL )
1878 THEN
1879 IF l_debug_mode = 'Y' THEN
1880 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : p_project_id can not be null';
1881 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1882 l_debug_level3);
1883 END IF;
1884 RAISE Invalid_Arg_Exc_WP;
1885 END IF;
1886
1887 --Get all the mapped financial tasks
1888 l_mapped_obj_rel_id_tbl.DELETE;
1889 OPEN c_get_fin_task_ver_id ( p_project_id );
1890 FETCH c_get_fin_task_ver_id BULK COLLECT
1891 INTO l_mapped_obj_rel_id_tbl ;
1892 CLOSE c_get_fin_task_ver_id ;
1893
1894 IF (l_mapped_obj_rel_id_tbl IS NOT NULL AND l_mapped_obj_rel_id_tbl.COUNT > 0 )
1895 THEN
1896 FORALL iCounter IN l_mapped_obj_rel_id_tbl.FIRST..l_mapped_obj_rel_id_tbl.LAST
1897
1898 DELETE FROM PA_OBJECT_RELATIONSHIPS
1899 WHERE
1900 OBJECT_RELATIONSHIP_ID = l_mapped_obj_rel_id_tbl(iCounter);
1901 END IF;
1902 IF (p_commit = FND_API.G_TRUE) THEN
1903 COMMIT;
1904 END IF;
1905
1906 EXCEPTION
1907
1908 WHEN FND_API.G_EXC_ERROR THEN
1909
1910 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1911 l_msg_count := Fnd_Msg_Pub.count_msg;
1912
1913 IF p_commit = FND_API.G_TRUE THEN
1914 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1915 END IF;
1916 IF c_get_fin_task_ver_id%ISOPEN THEN
1917 CLOSE c_get_fin_task_ver_id;
1918 END IF;
1919 IF l_msg_count = 1 AND x_msg_data IS NULL
1920 THEN
1921 Pa_Interface_Utils_Pub.get_messages
1922 ( p_encoded => Fnd_Api.G_TRUE
1923 , p_msg_index => 1
1924 , p_msg_count => l_msg_count
1925 , p_msg_data => l_msg_data
1926 , p_data => l_data
1927 , p_msg_index_out => l_msg_index_out);
1928 x_msg_data := l_data;
1929 x_msg_count := l_msg_count;
1930 ELSE
1931 x_msg_count := l_msg_count;
1932 END IF;
1933 IF l_debug_mode = 'Y' THEN
1934 Pa_Debug.reset_curr_function;
1935 END IF;
1936
1937 WHEN Invalid_Arg_Exc_WP THEN
1938
1939 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1940 x_msg_count := 1;
1941 --x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
1942
1943 IF p_commit = FND_API.G_TRUE THEN
1944 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1945 END IF;
1946 IF c_get_fin_task_ver_id%ISOPEN THEN
1947 CLOSE c_get_fin_task_ver_id;
1948 END IF;
1949 Fnd_Msg_Pub.add_exc_msg
1950 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1951 , p_procedure_name => 'DELETE_ALL_MAPPING'
1952 , p_error_text => x_msg_data);
1953
1954 IF l_debug_mode = 'Y' THEN
1955 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1956 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1957 l_debug_level5);
1958 Pa_Debug.reset_curr_function;
1959 END IF;
1960 RAISE;
1961
1962 WHEN OTHERS THEN
1963
1964 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1965 x_msg_count := 1;
1966 x_msg_data := SQLERRM;
1967
1968 IF p_commit = FND_API.G_TRUE THEN
1969 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1970 END IF;
1971
1972 IF c_get_fin_task_ver_id%ISOPEN THEN
1973 CLOSE c_get_fin_task_ver_id;
1974 END IF;
1975
1976 Fnd_Msg_Pub.add_exc_msg
1977 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1978 , p_procedure_name => 'DELETE_ALL_MAPPING'
1979 , p_error_text => x_msg_data);
1980
1981 IF l_debug_mode = 'Y' THEN
1982 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1983 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1984 l_debug_level5);
1985 Pa_Debug.reset_curr_function;
1986 END IF;
1987 RAISE;
1988 END DELETE_ALL_MAPPING ;
1989
1990 END PA_PROJ_STRUC_MAPPING_PUB;
[Home] [Help]