1 PACKAGE BODY PA_PROJ_STRUC_MAPPING_PUB AS
2 /* $Header: PAPSMPPB.pls 120.1 2005/08/19 16:46:15 mwasowic noship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_PROJ_STRUC_MAPPING_PUB';
5 Invalid_Arg_Exc_WP Exception;
6
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
117 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
118 l_debug_level3);
119 END IF;
120
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
194 , p_commit => p_commit
195 , p_validate_only => p_validate_only
196 , p_calling_module => 'SELF_SERVICE'
197 , p_debug_mode => l_debug_mode
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
316 , p_msg_index => 1
317 , p_msg_count => l_msg_count
318 , p_msg_data => l_msg_data
319 , p_data => l_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
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 )
439 IS
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 --select the wp_task_version_id for the corresponding name
459 CURSOR c_get_wp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER, l_parent_str_version_id NUMBER)
460 IS
461 SELECT
462 ppv.element_version_id
463
464 FROM
465 pa_proj_element_versions ppv
466 , pa_proj_elements pae
467 WHERE pae.name = l_task_name
468 AND pae.project_id = l_project_id
469 AND ppv.project_id = l_project_id
470 AND ppv.proj_element_id = pae.proj_element_id
471 AND ppv.parent_structure_version_id = l_parent_str_version_id
472 AND pae.object_type = 'PA_TASKS'
473 AND pae.project_id = ppv.project_id ;
474
475 --select the fp_task_version_id for the corresponding name , it does not require parent str ver id
476 CURSOR c_get_fp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER)
477 IS
478 SELECT
479 ppv.element_version_id
480
481 FROM
482 pa_proj_element_versions ppv
483 , pa_proj_elements pae
484 , pa_proj_elem_ver_structure str_ver
485 , pa_proj_structure_types str_type
486 , pa_structure_types
487
488 WHERE pae.name = l_task_name
489 AND pae.project_id = l_project_id
490 AND ppv.project_id = l_project_id
491 AND ppv.proj_element_id = pae.proj_element_id
492 AND ppv.parent_structure_version_id = str_ver.element_version_id
493 AND pae.object_type = 'PA_TASKS'
494 AND ppv.object_type = 'PA_TASKS'
495 AND str_ver.project_id = l_project_id
496 AND str_ver.proj_element_id = str_type.proj_element_id
497 AND str_type.structure_type_id = pa_structure_types.structure_type_id
498 AND pa_structure_types.structure_type = 'FINANCIAL'
499 AND pae.project_id = ppv.project_id ;
500
501
502 BEGIN
503
504 x_msg_count := 0;
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
507
508 IF l_debug_mode = 'Y' THEN
509 PA_DEBUG.set_curr_function( p_function => 'CREATE_MAPPING',
510 p_debug_mode => l_debug_mode );
511 END IF;
512
513 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
514 FND_MSG_PUB.initialize;
515 END IF;
516
517 IF (p_commit = FND_API.G_TRUE) THEN
518 savepoint CREATE_MAPPING_PUBLIC;
519 END IF;
520
521 IF l_debug_mode = 'Y' THEN
522 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Printing Input parameters';
523 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
524 l_debug_level3);
525
526 Pa_Debug.WRITE(g_module_name,'p_wp_task_name'||':'||p_wp_task_name,
527 l_debug_level3);
528
529 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
530 l_debug_level3);
531
532 Pa_Debug.WRITE(g_module_name,'p_parent_str_version_id'||':'||p_parent_str_version_id,
533 l_debug_level3);
534
535 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
536 l_debug_level3);
537 Pa_Debug.WRITE(g_module_name,'p_fp_task_name'||':'||p_fp_task_name,
538 l_debug_level3);
539 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
540 l_debug_level3);
541 END IF;
542
543 IF l_debug_mode = 'Y' THEN
544 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Validating Input parameters';
545 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
546 END IF;
547
548 --Check if project id is null , raise an error
549 IF (p_project_id is NULL)
550 THEN
551 IF l_debug_mode = 'Y' THEN
552 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : project id can not be null';
553 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
554 l_debug_level3);
555 END IF;
556 RAISE Invalid_Arg_Exc_WP;
557 END IF;
558
559 -- if wp task id and wp task name both are null , or
560 -- if fp task id and and fp task name both are null
561 -- raise error
562 IF (
563 (
564 ( p_wp_task_name is NULL OR p_wp_task_name = FND_API.G_MISS_CHAR ) AND
565 ( p_wp_task_version_id is NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM )
566 ) OR
567 (
568 ( p_fp_task_name is NULL OR p_fp_task_name = FND_API.G_MISS_CHAR ) AND
569 ( p_fp_task_version_id is NULL OR p_fp_task_version_id = FND_API.G_MISS_NUM )
570 )
571 )
572 THEN
573 IF l_debug_mode = 'Y' THEN
574 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Both work plan task id and task name are null';
575 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
576 l_debug_level3);
577 END IF;
578 RAISE Invalid_Arg_Exc_WP;
579 END IF;
580
581 -- Parent structure version id is essential to get task id using task name , so it should be validated
582 IF (
583 ( p_wp_task_version_id IS NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM ) AND
584 (
585 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR ) AND
586 ( p_parent_str_version_id IS NULL OR p_parent_str_version_id = FND_API.G_MISS_NUM )
587 )
588 )
589 THEN
590 IF l_debug_mode = 'Y' THEN
591 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';
592 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
593 l_debug_level3);
594 END IF;
595 RAISE Invalid_Arg_Exc_WP;
596 END IF;
597
598 -- If the task name is passed insteand of task id, we need to fetch the task id
599
600 IF ( p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
601 THEN
602 l_wp_task_version_id := p_wp_task_version_id;
603
604 ELSIF
605 ( ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR )AND
606 ( p_parent_str_version_id IS NOT NULL AND p_parent_str_version_id <> FND_API.G_MISS_NUM )
607 )
608 THEN
609 --select the wp_task_version_id for the corresponding name and store it in l_wp_task_version_id
610
611 OPEN c_get_wp_task_ver_id_frm_name ( p_wp_task_name , p_project_id , p_parent_str_version_id );
612
613 FETCH c_get_wp_task_ver_id_frm_name INTO l_wp_task_version_id ;
614 IF ( c_get_wp_task_ver_id_frm_name%NOTFOUND )
615 THEN
616 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
617 p_msg_name => 'PA_OBJECT_NAME_INV' );
618 RAISE FND_API.G_EXC_ERROR ;
619 END IF;
620 CLOSE c_get_wp_task_ver_id_frm_name ;
621
622 END IF;
623
624 IF ( ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
625 )
626 THEN
627 l_fp_task_version_id := p_fp_task_version_id;
628
629 ELSIF ( p_fp_task_name IS NOT NULL AND p_fp_task_name <> FND_API.G_MISS_CHAR )
630 THEN
631 -- get the fp_task_version_id and place it in l_fp_task_version_id;
632 -- Assuming there will be only one version for the FP task.
633 Pa_Debug.WRITE('test',l_fp_task_version_id, l_debug_level3);
634 OPEN c_get_fp_task_ver_id_frm_name ( p_fp_task_name , p_project_id );
635 FETCH c_get_fp_task_ver_id_frm_name INTO l_fp_task_version_id ;
636 IF ( c_get_fp_task_ver_id_frm_name%NOTFOUND )
637 THEN
638 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
639 p_msg_name => 'PA_OBJECT_NAME_INV' );
640 RAISE FND_API.G_EXC_ERROR ;
641 END IF;
642 CLOSE c_get_fp_task_ver_id_frm_name ;
643
644 END IF;
645
646 IF l_debug_mode = 'Y' THEN
647 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Calling PA_PROJ_STRUC_MAPPING_UTILS.CHECK_CREATE_MAPPING_OK ';
648 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
649 END IF;
650
651 -- Call util API to check whether the mapping can be created
652 PA_PROJ_STRUC_MAPPING_UTILS.CHECK_CREATE_MAPPING_OK
653 ( p_task_version_id_WP => l_wp_task_version_id
654 , p_task_version_id_FP => l_fp_task_version_id
655 , p_api_version => p_api_version
656 , p_calling_module => p_calling_module
657 , x_return_status => l_create_mapping_ok
658 , x_msg_count => x_msg_count
659 , x_msg_data => x_msg_data
660 , x_error_message_code => l_error_message_code
661 );
662
663 x_return_status := l_create_mapping_ok;
664
665 IF l_create_mapping_ok <> FND_API.G_RET_STS_SUCCESS
666 THEN
667
668 -- Mapping can't be created
669 IF l_debug_mode = 'Y' THEN
670 Pa_Debug.g_err_stage:= ' PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : '|| l_error_message_code;
671 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
672 l_debug_level3);
673 END IF;
674 Pa_Utils.ADD_MESSAGE
675 ( p_app_short_name => 'PA',
676 p_msg_name => l_error_message_code);
677 RAISE FND_API.G_EXC_ERROR;
678 ELSE
679 -- Mapping Can be created, call public API to create mapping
680 IF l_debug_mode = 'Y' THEN
681 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : Calling create'||l_wp_task_version_id||l_fp_task_version_id;
682 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
683 l_debug_level3);
684 END IF;
685
686 --Call public API to create the mapping
687
688 PA_RELATIONSHIP_PUB.CREATE_RELATIONSHIP
689 (
690 p_init_msg_list => FND_API.G_FALSE
691 , p_commit => p_commit
692 , p_debug_mode => l_debug_mode
693 , p_project_id_from => p_project_id
694 , p_task_version_id_from => l_wp_task_version_id
695 , p_project_id_to => p_project_id
696 , p_task_version_id_to => l_fp_task_version_id
697 , p_structure_type => NULL
698 , p_relationship_type => 'M'
699 , p_initiating_element => NULL
700 , x_object_relationship_id => l_relationship_id
701 , x_return_status => x_return_status
702 , x_msg_count => x_msg_count
703 , x_msg_data => x_msg_data
704
705 );
706 END IF;
707 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710 IF (p_commit = FND_API.G_TRUE) THEN
711 COMMIT;
712 END IF;
713
714 EXCEPTION
715
716 WHEN FND_API.G_EXC_ERROR THEN
717
718 x_return_status := Fnd_Api.G_RET_STS_ERROR;
719 l_msg_count := Fnd_Msg_Pub.count_msg;
720
721 IF (p_commit = FND_API.G_TRUE) THEN
722 ROLLBACK TO CREATE_MAPPING_PUBLIC;
723 END IF;
724
725 IF l_msg_count = 1 AND x_msg_data IS NULL
726 THEN
727 Pa_Interface_Utils_Pub.get_messages
728 ( p_encoded => Fnd_Api.G_TRUE
729 , p_msg_index => 1
730 , p_msg_count => l_msg_count
731 , p_msg_data => l_msg_data
732 , p_data => l_data
733 , p_msg_index_out => l_msg_index_out);
734 x_msg_data := l_data;
735 x_msg_count := l_msg_count;
736 ELSE
737 x_msg_count := l_msg_count;
738 END IF;
739 IF l_debug_mode = 'Y' THEN
740 Pa_Debug.reset_curr_function;
741 END IF;
742
743 WHEN Invalid_Arg_Exc_WP THEN
744
745 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
746 x_msg_count := 1;
747 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : CREATE_MAPPING : NULL arguments are passed to the procedure';
748
749 IF (p_commit = FND_API.G_TRUE) THEN
750 ROLLBACK TO CREATE_MAPPING_PUBLIC;
751 END IF;
752
753 Fnd_Msg_Pub.add_exc_msg
754 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
755 , p_procedure_name => 'CREATE_MAPPING'
756 , p_error_text => x_msg_data);
757
758 IF l_debug_mode = 'Y' THEN
759 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
760 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
761 l_debug_level5);
762 Pa_Debug.reset_curr_function;
763 END IF;
764 RAISE;
765
766
767 WHEN OTHERS THEN
768
769 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
770 x_msg_count := 1;
771 x_msg_data := SQLERRM;
772
773 IF (p_commit = FND_API.G_TRUE) THEN
774 ROLLBACK TO CREATE_MAPPING_PUBLIC;
775 END IF;
776 Pa_Debug.g_err_stage:= 'x_msg_count='||x_msg_count;
777 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level5);
778
779
780 Fnd_Msg_Pub.add_exc_msg
781 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
782 , p_procedure_name => 'CREATE_MAPPING'
783 , p_error_text => x_msg_data);
784 Pa_Debug.g_err_stage:= 'x_msg_count='||x_msg_count;
785 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level5);
786
787
788 IF l_debug_mode = 'Y' THEN
789 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
790 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
791 l_debug_level5);
792 Pa_Debug.reset_curr_function;
793 END IF;
794 RAISE;
795 END CREATE_MAPPING ;
796
797
798
799 -- Procedure : UPDATE_MAPPING
800 -- Type : Public Procedure
801 -- Purpose : This API will be used to update the mapping for existing tasks.
802 -- : This API will be called from following pages:
803 -- : 1.Map Workplan to Financial Task (HGRID - All Tasks)
804 -- : 2.Map Workplan Tasks (Selected)
805 -- : 3.Map Financial Tasks
806 -- : 4.UPDATE TASK page
807
808 -- Note : This API will update the mapping , In workplan context
809 -- : -- If no mapping is existing, it will create the mapping if the passed fp_task is valid.
810 -- :
811 -- : -- If mapping exists, and fp task name is changed on the page, the mapping will deleted and new mapping
812 -- : -- with new fp task name will be created.
813 -- : In Financial context
814 -- : -- Similar to workplan
815 -- : If called from self service, the object relationship id will be passed.
816 -- : If this is null,new mapping will be created. If this is not null, exitsting mapping will be deleted.
817 -- : The record version number will also be passed from self service application.
818 -- 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
819
820 -- Parameters Type Required Description and Purpose
821 -- --------------------------- ------ -------- --------------------------------------------------------
822 -- p_structure_type VARCHAR2 Yes The value will contain the context for which the Update is happening. Its value will be FINANCIAL or WORKPLAN.
823 -- p_project_id NUMBER Yes The project id will be used in case of getting task ids from task names
824 -- 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
825 -- p_wp_prnt_str_ver_id NUMBER NO It is required to get the task id from task name.
826 -- p_wp_task_version_id NUMBER Yes The WP task id from which mapping has to be updated
827 -- p_fp_task_name VARCHAR2 NO IF FP task id is not passed, task name will be required
828 -- p_fp_task_version_id NUMBER Yes FP task id in mapping which needs to b updated
829 -- p_object_relationship_id NUMBER NO The Object relationship Id of the existing mapping
830
831 PROCEDURE UPDATE_MAPPING
832 (
833 p_api_version IN NUMBER := 1.0
834 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
835 , p_commit IN VARCHAR2 := FND_API.G_FALSE
836 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
837 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
838 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
839 , p_debug_mode IN VARCHAR2 := 'N'
840 , p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
841 , p_structure_type IN VARCHAR2 := 'WORKPLAN'
842 , p_project_id IN NUMBER
843 , p_wp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
844 , p_wp_prnt_str_ver_id IN NUMBER := FND_API.G_MISS_NUM
845 , p_wp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
846 , p_fp_task_name IN VARCHAR2 := FND_API.G_MISS_CHAR
847 , p_fp_task_version_id IN NUMBER := FND_API.G_MISS_NUM
848 , p_object_relationship_id IN NUMBER := FND_API.G_MISS_NUM
849 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
850 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
851 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
852 )
853 IS
854 l_msg_count NUMBER := 0;
855 l_data VARCHAR2(2000);
856 l_msg_data VARCHAR2(2000);
857 l_msg_index_out NUMBER;
858 l_debug_mode VARCHAR2(1);
859 l_wp_task_version_id NUMBER ;
860 l_fp_task_version_id NUMBER ;
861 l_notfound BOOLEAN;
862 l_wp_task_version_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
863 l_map_wp_task_ver_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
864 l_wp_task_name_table PA_PROJ_STRUC_MAPPING_PUB.OBJECT_NAME_TABLE_TYPE ;
865 l_parse_return_message VARCHAR2(30);
866 l_proj_element_version_id NUMBER;
867 l_object_relationship_id NUMBER;
868 l_rec_version_num NUMBER;
869
870 l_debug_level2 CONSTANT NUMBER := 2;
871 l_debug_level3 CONSTANT NUMBER := 3;
872 l_debug_level4 CONSTANT NUMBER := 4;
873 l_debug_level5 CONSTANT NUMBER := 5;
874
875
876 -- This cursor selects the task version id given task name , project_id and parent str ver id
877 -- This same cursor can be used for WP tasks
878 CURSOR c_get_wp_task_ver_id_frm_name (l_wp_task_name VARCHAR2 , l_projectid NUMBER ,l_prnt_str_ver_id NUMBER )
879 IS
880 SELECT ppv.element_version_id
881 FROM pa_proj_element_versions ppv, pa_proj_elements pae
882 WHERE pae.name = l_wp_task_name
883 AND pae.project_id = l_projectid
884 AND ppv.proj_element_id = pae.proj_element_id
885 AND ppv.parent_structure_version_id = l_prnt_str_ver_id
886 AND pae.object_type = 'PA_TASKS'
887 AND pae.project_id = ppv.project_id;
888
889
890 -- Select the object relationship id corresponding to existing mapping
891 CURSOR cur_get_object_relationship_id (l_wp_task_version_id NUMBER )
892 IS
893 SELECT object_relationship_id , record_version_number
894 FROM pa_object_relationships
895 WHERE object_id_from1 = l_wp_task_version_id
896 AND relationship_type = 'M';
897
898
899 --select the fp_task_version_id for the corresponding name , it does not require parent str ver id
900 CURSOR c_get_fp_task_ver_id_frm_name ( l_task_name VARCHAR2, l_project_id NUMBER)
901 IS
902 SELECT
903 ppv.element_version_id
904 FROM
905 pa_proj_element_versions ppv
906 , pa_proj_elements pae
907 , pa_proj_elem_ver_structure str_ver
908 , pa_proj_structure_types str_type
909 , pa_structure_types
910 WHERE pae.name = l_task_name
911 AND pae.project_id = l_project_id
912 AND ppv.project_id = l_project_id
913 AND ppv.proj_element_id = pae.proj_element_id
914 AND ppv.parent_structure_version_id = str_ver.element_version_id
915 AND pae.object_type = 'PA_TASKS'
916 AND ppv.object_type = 'PA_TASKS'
917 AND str_ver.project_id = l_project_id
918 AND str_ver.proj_element_id = str_type.proj_element_id
919 AND str_type.structure_type_id = pa_structure_types.structure_type_id
920 AND pa_structure_types.structure_type = 'FINANCIAL'
921 AND ppv.project_id = pae.project_id;
922
923 BEGIN
924
925 x_msg_count := 0;
926 x_return_status := FND_API.G_RET_STS_SUCCESS;
927 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
928
929 IF l_debug_mode = 'Y' THEN
930 PA_DEBUG.set_curr_function( p_function => 'UPDATE_MAPPING',
931 p_debug_mode => l_debug_mode );
932 END IF;
933
934 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
935 FND_MSG_PUB.initialize;
936 END IF;
937
938 IF ( p_commit = FND_API.G_TRUE ) THEN
939 savepoint UPDATE_MAPPING_PUBLIC;
940 END IF;
941
942 IF l_debug_mode = 'Y' THEN
943 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Printing Input parameters';
944 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
945 l_debug_level3);
946
947 Pa_Debug.WRITE(g_module_name,'p_structure_type'||':'||p_structure_type,
948 l_debug_level3);
949
950 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
951 l_debug_level3);
952
953 Pa_Debug.WRITE(g_module_name,'p_wp_task_name'||':'||p_wp_task_name,
954 l_debug_level3);
955
956 Pa_Debug.WRITE(g_module_name,'p_wp_prnt_str_ver_id'||':'||p_wp_prnt_str_ver_id,
957 l_debug_level3);
958 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
959 l_debug_level3);
960 Pa_Debug.WRITE(g_module_name,'p_fp_task_name'||':'||p_fp_task_name,
961 l_debug_level3);
962
963 Pa_Debug.WRITE(g_module_name,'p_fp_task_version_id'||':'||p_fp_task_version_id,
964 l_debug_level3);
965 END IF;
966
967 IF l_debug_mode = 'Y' THEN
968 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Validating Input parameters';
969 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
970 l_debug_level3);
971 END IF;
972
973 -- if PROJECT id IS NULL RAISE ERROR MESSAGE
974 IF (p_project_id is NULL)
975 THEN
976 IF l_debug_mode = 'Y' THEN
977 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : project id can not be null';
978 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
979 l_debug_level3);
980 END IF;
981 RAISE Invalid_Arg_Exc_WP;
982 END IF;
983
984 -- if wp task id and wp task name both are null , or
985 -- if fp task id and and fp task name both are null
986 -- raise error
987 IF (
988 (
989 ( p_wp_task_name is NULL OR p_wp_task_name = FND_API.G_MISS_CHAR ) AND
990 ( p_wp_task_version_id is NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM )
991 ) AND
992 (
993 ( p_fp_task_name is NULL OR p_fp_task_name = FND_API.G_MISS_CHAR ) AND
994 ( p_fp_task_version_id is NULL OR p_fp_task_version_id = FND_API.G_MISS_NUM )
995 )
996 )
997 THEN
998 IF l_debug_mode = 'Y' THEN
999 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Both of tasks id and tasks name are null';
1000 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1001 l_debug_level3);
1002 END IF;
1003 RAISE Invalid_Arg_Exc_WP;
1004 END IF;
1005
1006 -- Parent structure version id is essential to get task id using task name , so it should be validated
1007 IF (
1008 ( p_wp_task_version_id IS NULL OR p_wp_task_version_id = FND_API.G_MISS_NUM ) AND
1009 (
1010 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_NUM ) AND
1011 ( p_wp_prnt_str_ver_id IS NULL OR p_wp_prnt_str_ver_id = FND_API.G_MISS_NUM )
1012 )
1013 )
1014 THEN
1015 IF l_debug_mode = 'Y' THEN
1016 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';
1017 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1018 l_debug_level3);
1019 END IF;
1020 RAISE Invalid_Arg_Exc_WP;
1021 END IF;
1022
1023 IF ( p_wp_task_version_id IS NOT NULL AND p_wp_task_version_id <> FND_API.G_MISS_NUM )
1024 THEN
1025 l_wp_task_version_id := p_wp_task_version_id;
1026 ELSIF (
1027 ( p_wp_task_name IS NOT NULL AND p_wp_task_name <> FND_API.G_MISS_CHAR ) AND
1028 ( p_wp_prnt_str_ver_id IS NOT NULL AND p_wp_prnt_str_ver_id <> FND_API.G_MISS_NUM )
1029 )
1030 THEN
1031 --get the wp_task_version_id and place it in l_wp_task_version_id;
1032 OPEN c_get_wp_task_ver_id_frm_name ( p_wp_task_name , p_project_id , p_wp_prnt_str_ver_id );
1033 FETCH c_get_wp_task_ver_id_frm_name INTO l_wp_task_version_id;
1034 IF (c_get_wp_task_ver_id_frm_name%NOTFOUND)
1035 THEN
1036 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1037 p_msg_name => 'PA_OBJECT_NAME_INV' );
1038 RAISE FND_API.G_EXC_ERROR ;
1039 END IF;
1040 CLOSE c_get_wp_task_ver_id_frm_name;
1041
1042 END IF;
1043 IF ( p_fp_task_version_id IS NOT NULL AND p_fp_task_version_id <> FND_API.G_MISS_NUM )
1044 THEN
1045 l_fp_task_version_id := p_fp_task_version_id;
1046
1047 ELSIF ( p_fp_task_name IS NOT NULL AND p_fp_task_name <> FND_API.G_MISS_CHAR
1048 )
1049 THEN
1050 -- get the fp_task_version_id and place it in l_fp_task_version_id;
1051 -- Assuming there will be only one version for the FP task.Parent structure version id is passed
1052 -- in view that in future there wil be versions for financial tasks also
1053 OPEN c_get_fp_task_ver_id_frm_name ( p_fp_task_name, p_project_id );
1054 FETCH c_get_fp_task_ver_id_frm_name INTO l_fp_task_version_id;
1055 IF (c_get_fp_task_ver_id_frm_name%NOTFOUND)
1056 THEN
1057 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1058 p_msg_name => 'PA_OBJECT_NAME_INV' );
1059 RAISE FND_API.G_EXC_ERROR ;
1060 END IF;
1061 CLOSE c_get_fp_task_ver_id_frm_name;
1062 END IF;
1063
1064 IF ( ( p_object_relationship_id IS NULL OR p_object_relationship_id = FND_API.G_MISS_NUM )
1065 AND
1066 p_calling_module <> 'SELF_SERVICE'
1067 )
1068 THEN
1069 OPEN cur_get_object_relationship_id (l_wp_task_version_id);
1070 FETCH cur_get_object_relationship_id INTO l_object_relationship_id , l_rec_version_num;
1071 CLOSE cur_get_object_relationship_id;
1072 ELSE
1073 l_object_relationship_id := p_object_relationship_id;
1074 l_rec_version_num := p_record_version_number;
1075 END IF;
1076
1077 --If l_object_relationship_id is not null
1078 IF ( l_object_relationship_id IS NOT NULL AND l_object_relationship_id <> FND_API.G_MISS_NUM )
1079 THEN
1080 --delete the existing mapping
1081 PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP
1082 (
1083 p_api_version => p_api_version
1084 , p_init_msg_list => FND_API.G_FALSE
1085 , p_commit => p_commit
1086 , p_validate_only => p_validate_only
1087 , p_calling_module => 'SELF_SERVICE'
1088 , p_debug_mode => l_debug_mode
1089 , p_object_relationship_id => l_object_relationship_id
1090 , p_record_version_number => l_rec_version_num
1091 , x_return_status => x_return_status
1092 , x_msg_count => x_msg_count
1093 , x_msg_data => x_msg_data
1094 );
1095 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1096 THEN
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099 END IF;
1100
1101 --IF l_fp_task_ver_id is not null
1102 IF (
1103 (l_fp_task_version_id IS NOT NULL AND l_fp_task_version_id <> FND_API.G_MISS_NUM ) AND
1104 (l_wp_task_version_id IS NOT NULL AND l_wp_task_version_id <> FND_API.G_MISS_NUM )
1105 )
1106 THEN
1107 --create new mapping with l_wp_task_ver_id and l_fp_task_ver_id
1108 -- Call CREATE_MAPPING
1109 PA_PROJ_STRUC_MAPPING_PUB.CREATE_MAPPING(
1110 p_wp_task_version_id => l_wp_task_version_id
1111 , p_fp_task_version_id => l_fp_task_version_id
1112 , p_project_id => p_project_id
1113 , p_init_msg_list => FND_API.G_FALSE
1114 , p_commit => p_commit
1115 , p_debug_mode => l_debug_mode
1116 , x_return_status => x_return_status
1117 , x_msg_count => x_msg_count
1118 , x_msg_data => x_msg_data
1119 );
1120 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
1121 THEN
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1124 END IF;
1125
1126 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1127 THEN
1128 RAISE FND_API.G_EXC_ERROR;
1129 END IF;
1130
1131 IF ( p_commit = FND_API.G_TRUE ) THEN
1132 COMMIT;
1133 END IF;
1134
1135 EXCEPTION
1136
1137 WHEN FND_API.G_EXC_ERROR THEN
1138
1139 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1140 l_msg_count := Fnd_Msg_Pub.count_msg;
1141
1142 IF ( p_commit = FND_API.G_TRUE ) THEN
1143 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1144 END IF;
1145
1146 IF cur_get_object_relationship_id%ISOPEN THEN
1147 CLOSE cur_get_object_relationship_id;
1148 END IF;
1149
1150 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1151 CLOSE c_get_fp_task_ver_id_frm_name;
1152 END IF;
1153
1154 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1155 CLOSE c_get_wp_task_ver_id_frm_name;
1156 END IF;
1157
1158 IF l_msg_count = 1 AND x_msg_data IS NULL
1159 THEN
1160 Pa_Interface_Utils_Pub.get_messages
1161 ( p_encoded => Fnd_Api.G_TRUE
1162 , p_msg_index => 1
1163 , p_msg_count => l_msg_count
1164 , p_msg_data => l_msg_data
1165 , p_data => l_data
1166 , p_msg_index_out => l_msg_index_out);
1167 x_msg_data := l_data;
1168 x_msg_count := l_msg_count;
1169 ELSE
1170 x_msg_count := l_msg_count;
1171 END IF;
1172 IF l_debug_mode = 'Y' THEN
1173 Pa_Debug.reset_curr_function;
1174 END IF;
1175
1176 WHEN Invalid_Arg_Exc_WP THEN
1177
1178 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1179 x_msg_count := Fnd_Msg_Pub.count_msg;
1180 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : UPDATE_MAPPING : Some parameters are NULL';
1181
1182 IF ( p_commit = FND_API.G_TRUE ) THEN
1183 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1184 END IF;
1185
1186 IF cur_get_object_relationship_id%ISOPEN THEN
1187 CLOSE cur_get_object_relationship_id;
1188 END IF;
1189
1190 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1191 CLOSE c_get_wp_task_ver_id_frm_name;
1192 END IF;
1193
1194 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1195 CLOSE c_get_fp_task_ver_id_frm_name;
1196 END IF;
1197
1198 Fnd_Msg_Pub.add_exc_msg
1199 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1200 , p_procedure_name => 'UPDATE_MAPPING'
1201 , p_error_text => x_msg_data);
1202
1203 IF l_debug_mode = 'Y' THEN
1204 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1205 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1206 l_debug_level5);
1207 Pa_Debug.reset_curr_function;
1208 END IF;
1209
1210
1211 WHEN OTHERS THEN
1212
1213 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1214 x_msg_count := 1;
1215 x_msg_data := SQLERRM;
1216
1217 IF ( p_commit = FND_API.G_TRUE ) THEN
1218 ROLLBACK TO UPDATE_MAPPING_PUBLIC;
1219 END IF;
1220
1221 IF cur_get_object_relationship_id%ISOPEN THEN
1222 CLOSE cur_get_object_relationship_id;
1223 END IF;
1224
1225 IF c_get_fp_task_ver_id_frm_name%ISOPEN THEN
1226 CLOSE c_get_fp_task_ver_id_frm_name;
1227 END IF;
1228
1229 IF c_get_wp_task_ver_id_frm_name%ISOPEN THEN
1230 CLOSE c_get_wp_task_ver_id_frm_name;
1231 END IF;
1232
1233 Fnd_Msg_Pub.add_exc_msg
1234 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1235 , p_procedure_name => 'UPDATE_MAPPING'
1236 , p_error_text => x_msg_data);
1237
1238 IF l_debug_mode = 'Y' THEN
1239 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1240 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1241 l_debug_level5);
1242 Pa_Debug.reset_curr_function;
1243 END IF;
1244
1245 END UPDATE_MAPPING ;
1246
1247
1248 -- Procedure : COPY_MAPPING
1249 -- Type : Public Procedure
1250 -- Purpose : This API copies the mappings from source structure tasks to destinations structure tasks
1251 -- : This will be called from copy_project, create working version, publish version api.
1252 -- :
1253 -- Note : The API's function will depend on the P_CONTEXT.
1254 -- : 1. P_CONTEXT is COPY_PROJECT: The structure version IDs passed will be ignored in this case and they will taken from the database.
1255 -- : Mapping will be created depending as in existing src stuructures
1256 -- : 2. P_CONTEXT is (create or publish): In this context, the structure version ids will be passed and src project id will be ignored
1257 -- :
1258 -- Assumptions : This will be called after the copy project has been called ,
1259 -- : so that the element version ids of the destination projects are available.
1260 -- : Only for split structure
1261
1262 -- Parameters Type Required Description and Purpose
1263 -- --------------------------- ------ -------- --------------------------------------------------------
1264 -- p_context IN VARCHAR2 This will tell whether the context is copy_project or other
1265 -- p_src_project_id IN NUMBER The project id which is being copied
1266 -- p_dest_project_id IN NUMBER The project id to which it is being copied
1267 -- p_src_str_version_id IN NUMBER The structure version id from source project
1268 -- p_dest_str_version_id IN NUMBER The structure version id from source at the destinantion project
1269
1270 PROCEDURE COPY_MAPPING
1271 (
1272 p_api_version IN NUMBER := 1.0
1273 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1274 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1275 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
1276 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1277 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1278 , p_debug_mode IN VARCHAR2 := 'N'
1279 , p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1280 , p_context IN VARCHAR2
1281 , p_src_project_id IN NUMBER := FND_API.G_MISS_NUM
1282 , p_dest_project_id IN NUMBER := FND_API.G_MISS_NUM
1283 , p_src_str_version_id IN NUMBER := FND_API.G_MISS_NUM
1284 , p_dest_str_version_id IN NUMBER := FND_API.G_MISS_NUM
1285 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1286 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1287 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1288
1289 )
1290 IS
1291
1292 l_msg_count NUMBER := 0;
1293 l_data VARCHAR2(2000);
1294 l_msg_data VARCHAR2(2000);
1295 l_msg_index_out NUMBER;
1296 l_debug_mode VARCHAR2(1);
1297 l_user_id NUMBER;
1298 l_debug_level2 CONSTANT NUMBER := 2;
1299 l_debug_level3 CONSTANT NUMBER := 3;
1300 l_debug_level4 CONSTANT NUMBER := 4;
1301 l_debug_level5 CONSTANT NUMBER := 5;
1302
1303
1304
1305 -- This cursor will select the element_version_id , source structure version id and structure type for the passed
1306 -- project id.
1307 CURSOR c_get_dest_structures ( l_dest_project_id NUMBER )
1308 IS
1309 SELECT
1310 pelever.element_version_id AS structure_version
1311 , pelever.attribute15 AS src_str_version_id
1312 , pstrType.structure_type AS structure_type
1313 FROM
1314 pa_proj_element_versions pelever,
1315 pa_proj_structure_types prjstrType,
1316 pa_structure_types pstrType
1317 WHERE
1318 pelever.object_type = 'PA_STRUCTURES'
1319 AND
1320 pelever.project_id = l_dest_project_id
1321 AND
1322 prjstrType.proj_element_id = pelever.proj_element_id
1323 AND
1324 pstrType.structure_type_id = prjstrType.structure_type_id
1325 AND
1326 pstrType.structure_type = 'WORKPLAN'
1327 AND
1328 pstrType.structure_type_class_code = 'WORKPLAN';
1329
1330
1331 --This cursor will select all the tasks which are mapped for a particular structure which needs to be passed.
1332 CURSOR c_get_mapped_tasks (l_parent_str_version_id NUMBER, l_project_id NUMBER)
1333 IS
1334 SELECT
1335 paObrel.OBJECT_ID_FROM1 as OBJECT_ID_FROM1
1336 , paObrel.OBJECT_ID_TO1 as OBJECT_ID_TO1
1337 FROM
1338 pa_proj_element_versions elever
1339 , pa_object_relationships paObrel
1340 WHERE elever.PARENT_STRUCTURE_VERSION_ID = l_parent_str_version_id
1341 AND paObrel.OBJECT_ID_FROM1 = elever.element_version_id
1342 AND elever.project_id = l_project_id
1343 AND paObrel.RELATIONSHIP_TYPE = 'M'
1344 AND elever.object_type = 'PA_TASKS';
1345
1346 -- This cursor will get the task version id from pa_proj_element_versions , where src task version id is passed
1347 CURSOR c_get_mapped_task_version ( l_src_task_version_id NUMBER, l_project_id NUMBER )
1348 IS
1349 SELECT ELEMENT_VERSION_ID
1350 FROM PA_PROJ_ELEMENT_VERSIONS ELEVER
1351 WHERE ELEVER.ATTRIBUTE15 = l_src_task_version_id
1352 AND ELEVER.OBJECT_TYPE = 'PA_TASKS'
1353 AND ELEVER.PROJECT_ID = l_project_id;
1354
1355 CURSOR c_get_mapped_task_id ( l_task_ver_id NUMBER , l_str_version_id NUMBER, l_project_id NUMBER )
1356 IS
1357 SELECT proj_element_id
1358 FROM pa_proj_element_versions
1359 WHERE OBJECT_TYPE = 'PA_TASKS'
1360 AND element_version_id = l_task_ver_id
1361 AND parent_structure_version_id = l_str_version_id
1362 AND pa_proj_element_versions.project_id = l_project_id;
1363
1364 CURSOR c_get_mapped_task_ver_id ( l_task_id NUMBER , l_str_version_id NUMBER, l_project_id NUMBER )
1365 IS
1366 SELECT element_version_id
1367 FROM pa_proj_element_versions
1368 WHERE OBJECT_TYPE = 'PA_TASKS'
1369 AND parent_structure_version_id = l_str_version_id
1370 AND proj_element_id = l_task_id
1371 AND pa_proj_element_versions.project_id = l_project_id;
1372
1373 l_src_from_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1374 l_src_to_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1375
1376 l_dest_from_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1377 l_dest_to_tasks_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1378
1379 l_from_task_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_ID_TABLE_TYPE;
1380 l_to_task_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJECT_ID_TABLE_TYPE;
1381
1382 l_from_dest_task_ver_id PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1383 l_to_dest_task_ver_id PA_PROJ_STRUC_MAPPING_PUB.OBJECT_VERSION_ID_TABLE_TYPE;
1384
1385 BEGIN
1386
1387
1388 x_msg_count := 0;
1389 l_user_id := FND_GLOBAL.USER_ID;
1390 x_return_status := FND_API.G_RET_STS_SUCCESS;
1391 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1392
1393
1394 IF l_debug_mode = 'Y' THEN
1395 PA_DEBUG.set_curr_function( p_function => 'COPY_MAPPING',
1396 p_debug_mode => l_debug_mode );
1397 END IF;
1398
1399 IF l_debug_mode = 'Y' THEN
1400 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : Printing Input parameters';
1401 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1402 l_debug_level3);
1403 Pa_Debug.WRITE(g_module_name,'p_context'||p_context,l_debug_level3);
1404 Pa_Debug.WRITE(g_module_name,'p_src_project_id'||p_src_project_id,l_debug_level3);
1405 Pa_Debug.WRITE(g_module_name,'p_dest_project_id'||p_dest_project_id,l_debug_level3);
1406 Pa_Debug.WRITE(g_module_name,'p_src_str_version_id'||p_src_str_version_id,l_debug_level3);
1407 Pa_Debug.WRITE(g_module_name,'p_dest_str_version_id'||p_dest_str_version_id,l_debug_level3);
1408 END IF;
1409
1410 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1411 FND_MSG_PUB.initialize;
1412 END IF;
1413
1414 IF l_debug_mode = 'Y' THEN
1415 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : Validating Input parameters';
1416 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1417 l_debug_level3);
1418 END IF;
1419
1420 IF ( p_context IS NULL ) THEN
1421 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : p_context is mandatory and cant be null';
1422 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1423 l_debug_level3);
1424
1425 RAISE Invalid_Arg_Exc_WP;
1426 END IF;
1427
1428 --If context is copy_project, src and destination project id must be passed and they should not be same
1429 IF ( p_context ='COPY_PROJECT' ) THEN
1430 IF
1431 (
1432 ( p_src_project_id IS NULL OR p_src_project_id = FND_API.G_MISS_NUM )
1433 OR ( p_dest_project_id IS NULL OR p_dest_project_id = FND_API.G_MISS_NUM )
1434 OR ( p_src_project_id = p_dest_project_id )
1435 )
1436 THEN
1437 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';
1438 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1439 l_debug_level3);
1440
1441 RAISE Invalid_Arg_Exc_WP;
1442
1443 ELSE
1444 -- Code for copy-project context
1445 --get the destination structures
1446 --This for loop gets all the workplan structures in the destination project id.
1447 --For each workplan structure , find the mapped task version id in source project
1448 --and corresponding task version id in destination project
1449 FOR str_rec IN c_get_dest_structures (p_dest_project_id) LOOP
1450
1451 --IF str_rec.structure_type = 'WORKPLAN' THEN
1452
1453 --For each workplan structure , find the mapped task version id in source project
1454 --delete all elements from the table before using it;
1455 l_src_to_tasks_id_tbl.DELETE;
1456 l_src_from_tasks_id_tbl.DELETE;
1457 OPEN c_get_mapped_tasks ( str_rec.src_str_version_id , p_src_project_id );
1458 FETCH c_get_mapped_tasks BULK COLLECT
1459 INTO l_src_from_tasks_id_tbl , l_src_to_tasks_id_tbl;
1460 CLOSE c_get_mapped_tasks ;
1461
1462 --For each task version id in source, get the corresponding one in destination project
1463 l_dest_from_tasks_id_tbl.DELETE;
1464 FOR iCounter in 1..l_src_from_tasks_id_tbl.COUNT LOOP
1465
1466 OPEN c_get_mapped_task_version ( l_src_from_tasks_id_tbl ( iCounter ) , p_dest_project_id ) ;
1467 FETCH c_get_mapped_task_version INTO l_dest_from_tasks_id_tbl ( iCounter ) ;
1468 CLOSE c_get_mapped_task_version ;
1469
1470 END LOOP;
1471 --For each task version id in source, get the corresponding one in destination project
1472 l_dest_to_tasks_id_tbl.DELETE;
1473 FOR iCounter in 1..l_src_to_tasks_id_tbl.COUNT LOOP
1474
1475 OPEN c_get_mapped_task_version ( l_src_to_tasks_id_tbl ( iCounter ) , p_dest_project_id ) ;
1476 FETCH c_get_mapped_task_version INTO l_dest_to_tasks_id_tbl ( iCounter ) ;
1477 CLOSE c_get_mapped_task_version ;
1478
1479 END LOOP;
1480
1481
1482 -- Create Mapping from the dest task ids using BULK INSERT
1483 --added if condition bug.3578265,3574885
1484 --rtarway bug 3916440, the forall should be called only when l_src_to_tasks_id_tbl is not empty
1485 IF (l_dest_from_tasks_id_tbl.count > 0 AND l_dest_to_tasks_id_tbl.COUNT > 0)
1486 THEN
1487 FORALL iCounter IN l_dest_from_tasks_id_tbl.FIRST..l_dest_from_tasks_id_tbl.LAST
1488
1489 INSERT INTO PA_OBJECT_RELATIONSHIPS
1490 (
1491 object_relationship_id,
1492 object_type_from,
1493 object_id_from1,
1494 object_id_from2,
1495 object_id_from3,
1496 object_id_from4,
1497 object_id_from5,
1498 object_type_to,
1499 object_id_to1,
1500 object_id_to2,
1501 object_id_to3,
1502 object_id_to4,
1503 object_id_to5,
1504 relationship_type,
1505 relationship_subtype,
1506 lag_day,
1507 imported_lag,
1508 priority,
1509 pm_product_code,
1510 Record_Version_Number,
1511 CREATED_BY,
1512 CREATION_DATE,
1513 LAST_UPDATED_BY,
1514 LAST_UPDATE_DATE,
1515 LAST_UPDATE_LOGIN,
1516 weighting_percentage
1517 )
1518
1519 VALUES ( pa_object_relationships_s.nextval,
1520 'PA_TASKS',
1521 l_dest_from_tasks_id_tbl(iCounter),
1522 NULL,
1523 NULL,
1524 NULL,
1525 NULL,
1526 'PA_TASKS',
1527 l_dest_to_tasks_id_tbl(iCounter),
1528 NULL,
1529 NULL,
1530 NULL,
1531 NULL,
1532 'M',
1533 NULL,
1534 NULL,
1535 NULL,
1536 NULL,
1537 NULL,
1538 1,
1539 l_user_id,
1540 sysdate,
1541 l_user_id,
1542 sysdate,
1543 l_user_id,
1544 NULL
1545 );
1546 END IF;
1547 --END IF;--If the condition is workplan
1548 END LOOP;--End of For loop
1549 END IF;-- if p_src_project_id is not null
1550
1551 -- For other than copy mapping, e.g. create working version or publish ,
1552 -- src_project_id and structure version id should be considered and hence these cant be null
1553 -- It is assumed that p_src_project_id will be passed in this case
1554 -- src_project_id will make the c_get_mapped_tasks efficient with project Id filter.
1555 ELSIF (p_context = 'CREATE_WORKING_VERSION' OR p_context = 'PUBLISH_VERSION')
1556 THEN
1557 IF ( ( p_src_str_version_id IS NULL OR p_src_str_version_id = FND_API.G_MISS_NUM )
1558 OR( p_dest_str_version_id IS NULL OR p_dest_str_version_id = FND_API.G_MISS_NUM )
1559 OR( p_src_project_id IS NULL OR p_src_project_id = FND_API.G_MISS_NUM )
1560 ) THEN
1561
1562 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';
1563 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1564 l_debug_level3);
1565 RAISE Invalid_Arg_Exc_WP;
1566 END IF;
1567
1568 -- get the mapped task-version-id for the source str version id.
1569 l_src_from_tasks_id_tbl.DELETE;
1570 l_src_to_tasks_id_tbl.DELETE;
1571 OPEN c_get_mapped_tasks ( p_src_str_version_id , p_src_project_id );
1572 FETCH c_get_mapped_tasks BULK COLLECT
1573 INTO l_src_from_tasks_id_tbl , l_src_to_tasks_id_tbl;
1574 CLOSE c_get_mapped_tasks ;
1575 -- for each task version id mapped, get the proj_element_id
1576 l_from_task_id_tbl.DELETE;
1577 FOR iCounter in 1..l_src_from_tasks_id_tbl.COUNT LOOP
1578 OPEN c_get_mapped_task_id ( l_src_from_tasks_id_tbl ( iCounter ),
1579 p_src_str_version_id , p_src_project_id );
1580 FETCH c_get_mapped_task_id INTO l_from_task_id_tbl ( iCounter );
1581 CLOSE c_get_mapped_task_id ;
1582 END LOOP;
1583
1584
1585
1586 --for each task id mapped, get the task version ids for the dest structure version id, project should be same
1587 l_from_dest_task_ver_id.DELETE;
1588 FOR iCounter in 1..l_from_task_id_tbl.COUNT LOOP
1589 OPEN c_get_mapped_task_ver_id ( l_from_task_id_tbl (iCounter) ,
1590 p_dest_str_version_id , p_src_project_id );
1591 FETCH c_get_mapped_task_ver_id INTO l_from_dest_task_ver_id ( iCounter );
1592 CLOSE c_get_mapped_task_ver_id;
1593 END LOOP;
1594
1595 Pa_Debug.WRITE(g_module_name,'l_from_task_id_tbl.COUNT '||l_from_task_id_tbl.COUNT ,l_debug_level3);
1596
1597 -- Insert into PA_OBJECT_RELATIONSHIPS
1598 --bug 3574885, the forall should be called only when l_from_dest_task_ver_id is not empty
1599 --rtarway ,bug 3916440, the forall should be called only when l_src_to_tasks_id_tbl is not empty
1600 IF (l_from_dest_task_ver_id.COUNT > 0 AND l_src_to_tasks_id_tbl.count > 0)
1601 THEN
1602 FORALL iCounter IN l_from_dest_task_ver_id.FIRST..l_from_dest_task_ver_id.LAST
1603
1604 INSERT INTO PA_OBJECT_RELATIONSHIPS (
1605 object_relationship_id,
1606 object_type_from,
1607 object_id_from1,
1608 object_id_from2,
1609 object_id_from3,
1610 object_id_from4,
1611 object_id_from5,
1612 object_type_to,
1613 object_id_to1,
1614 object_id_to2,
1615 object_id_to3,
1616 object_id_to4,
1617 object_id_to5,
1618 relationship_type,
1619 relationship_subtype,
1620 lag_day,
1621 imported_lag,
1622 priority,
1623 pm_product_code,
1624 Record_Version_Number,
1625 CREATED_BY,
1626 CREATION_DATE,
1627 LAST_UPDATED_BY,
1628 LAST_UPDATE_DATE,
1629 LAST_UPDATE_LOGIN,
1630 weighting_percentage)
1631 VALUES ( pa_object_relationships_s.nextval,
1632 'PA_TASKS',
1633 l_from_dest_task_ver_id(iCounter),
1634 NULL,
1635 NULL,
1636 NULL,
1637 NULL,
1638 'PA_TASKS',
1639 l_src_to_tasks_id_tbl(iCounter),
1640 NULL,
1641 NULL,
1642 NULL,
1643 NULL,
1644 'M',
1645 NULL,
1646 NULL,
1647 NULL,
1648 NULL,
1649 NULL,
1650 1,
1651 l_user_id,
1652 sysdate,
1653 l_user_id,
1654 sysdate,
1655 l_user_id,
1656 NULL
1657 );
1658 END IF;
1659 END IF;
1660
1661 Pa_Debug.WRITE(g_module_name,'After Completing insert',l_debug_level3);
1662
1663 EXCEPTION
1664
1665 WHEN FND_API.G_EXC_ERROR THEN
1666
1667 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1668 l_msg_count := Fnd_Msg_Pub.count_msg;
1669
1670 IF l_msg_count = 1 AND x_msg_data IS NULL
1671 THEN
1672 Pa_Interface_Utils_Pub.get_messages
1673 ( p_encoded => Fnd_Api.G_TRUE
1674 , p_msg_index => 1
1675 , p_msg_count => l_msg_count
1676 , p_msg_data => l_msg_data
1677 , p_data => l_data
1678 , p_msg_index_out => l_msg_index_out);
1679 x_msg_data := l_data;
1680 x_msg_count := l_msg_count;
1681 ELSE
1682 x_msg_count := l_msg_count;
1683 END IF;
1684 IF l_debug_mode = 'Y' THEN
1685 Pa_Debug.reset_curr_function;
1686 END IF;
1687
1688 WHEN Invalid_Arg_Exc_WP THEN
1689
1690 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1691 x_msg_count := 1;
1692 x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : COPY_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
1693
1694 Fnd_Msg_Pub.add_exc_msg
1695 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1696 , p_procedure_name => 'COPY_MAPPING'
1697 , p_error_text => x_msg_data);
1698
1699 IF l_debug_mode = 'Y' THEN
1700 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1701 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1702 l_debug_level5);
1703 Pa_Debug.reset_curr_function;
1704 END IF;
1705 RAISE;
1706
1707 WHEN OTHERS THEN
1708
1709 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1710 x_msg_count := 1;
1711 x_msg_data := SQLERRM;
1712
1713
1714 Fnd_Msg_Pub.add_exc_msg
1715 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1716 , p_procedure_name => 'COPY_MAPPING'
1717 , p_error_text => x_msg_data);
1718
1719 IF l_debug_mode = 'Y' THEN
1720 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1721 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1722 l_debug_level5);
1723 Pa_Debug.reset_curr_function;
1724 END IF;
1725 RAISE;
1726
1727 END COPY_MAPPING ;
1728
1729
1730
1731 -- Procedure : DELETE_ALL_MAPPING
1732 -- Type : Public Procedure
1733 -- Purpose : This API will be called when we change a split mapping project to split no mapping.
1734 -- :
1735 -- Note : 1. Get all the financial tasks of the passed project id.
1736 -- : 2. If any of these tasks is existing in PA_OBJECT_RELATIONSHIPS, with relationship_type 'M'
1737 -- : delete the record in pa_object_relationships
1738 -- Assumptions : The financial structure will have only one version in any case whether versioning enabled or disabled
1739
1740 -- Parameters Type Required Description and Purpose
1741 -- --------------------------- ------ -------- --------------------------------------------------------
1742 -- p_project_id NUMBER Y The project id for which the mappings have to be deleted
1743
1744 PROCEDURE DELETE_ALL_MAPPING
1745 (
1746 p_api_version IN NUMBER := 1.0
1747 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1748 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1749 , p_validate_only IN VARCHAR2 := FND_API.G_FALSE
1750 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1751 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1752 , p_debug_mode IN VARCHAR2 := 'N'
1753 , p_project_id IN NUMBER
1754 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1755 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1756 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1757 )
1758 IS
1759 l_msg_count NUMBER := 0;
1760 l_data VARCHAR2(2000);
1761 l_msg_data VARCHAR2(2000);
1762 l_msg_index_out NUMBER;
1763 l_debug_mode VARCHAR2(1);
1764
1765
1766 l_debug_level2 CONSTANT NUMBER := 2;
1767 l_debug_level3 CONSTANT NUMBER := 3;
1768 l_debug_level4 CONSTANT NUMBER := 4;
1769 l_debug_level5 CONSTANT NUMBER := 5;
1770 --Table to collect all the mapped financial task version id
1771 l_mapped_obj_rel_id_tbl PA_PROJ_STRUC_MAPPING_PUB.OBJ_REL_ID_TABLE_TYPE;
1772
1773 --This cursor will give all mapped financial tasks and object_relationships_id for the passed project id.
1774 CURSOR c_get_fin_task_ver_id (l_project_id NUMBER)
1775 IS
1776 SELECT
1777 obRel.object_relationship_id
1778 FROM
1779 pa_proj_element_versions elever1
1780 , pa_proj_element_versions elever2
1781 -- , pa_proj_structure_types projStrType Bug 3693235 Performance Fix
1782 -- , pa_structure_types strType Bug 3693235 Performance Fix
1783 , pa_object_relationships obRel
1784 WHERE
1785 elever1.object_type = 'PA_TASKS'
1786 AND elever1.parent_structure_version_id = elever2.element_version_id
1787 AND elever2.object_type = 'PA_STRUCTURES'
1788 -- Bug 3693235 Performance Fix
1789 --AND elever2.proj_element_id = projStrType.proj_element_id
1790 --AND projStrType.structure_type_id = strType.structure_type_id
1791 --AND strType.structure_type = 'FINANCIAL'
1792 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
1793 AND elever1.project_id = l_project_id
1794 AND elever2.project_id = l_project_id
1795 AND elever1.project_id = elever2.project_id
1796 AND elever1.element_version_id = obRel.object_id_to1
1797 AND obRel.relationship_type='M'
1798 -- Bug 3693235 Performance Fix
1799 AND obRel.object_type_from = 'PA_TASKS'
1800 AND obRel.object_type_to = 'PA_TASKS'
1801 ;
1802
1803
1804 BEGIN
1805
1806 x_msg_count := 0;
1807 x_return_status := FND_API.G_RET_STS_SUCCESS;
1808
1809 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1810 --l_debug_mode := NVL(p_debug_mode,'N');
1811 IF l_debug_mode = 'Y' THEN
1812 PA_DEBUG.set_curr_function( p_function => 'DELETE_ALL_MAPPING',
1813 p_debug_mode => l_debug_mode );
1814 END IF;
1815
1816 IF l_debug_mode = 'Y' THEN
1817 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Printing Input parameters';
1818 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1819 l_debug_level3);
1820
1821 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
1822 l_debug_level3);
1823 END IF;
1824
1825 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1826 FND_MSG_PUB.initialize;
1827 END IF;
1828
1829 IF (p_commit = FND_API.G_TRUE) THEN
1830 savepoint DELETE_ALL_MAPPING_PUBLIC;
1831 END IF;
1832
1833 IF l_debug_mode = 'Y' THEN
1834 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : Validating Input parameters';
1835 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1836 l_debug_level3);
1837 END IF;
1838
1839 IF ( p_project_id IS NULL )
1840 THEN
1841 IF l_debug_mode = 'Y' THEN
1842 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : p_project_id can not be null';
1843 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1844 l_debug_level3);
1845 END IF;
1846 RAISE Invalid_Arg_Exc_WP;
1847 END IF;
1848
1849 --Get all the mapped financial tasks
1850 l_mapped_obj_rel_id_tbl.DELETE;
1851 OPEN c_get_fin_task_ver_id ( p_project_id );
1852 FETCH c_get_fin_task_ver_id BULK COLLECT
1853 INTO l_mapped_obj_rel_id_tbl ;
1854 CLOSE c_get_fin_task_ver_id ;
1855
1856 IF (l_mapped_obj_rel_id_tbl IS NOT NULL AND l_mapped_obj_rel_id_tbl.COUNT > 0 )
1857 THEN
1858 FORALL iCounter IN l_mapped_obj_rel_id_tbl.FIRST..l_mapped_obj_rel_id_tbl.LAST
1859
1860 DELETE FROM PA_OBJECT_RELATIONSHIPS
1861 WHERE
1862 OBJECT_RELATIONSHIP_ID = l_mapped_obj_rel_id_tbl(iCounter);
1863 END IF;
1864 IF (p_commit = FND_API.G_TRUE) THEN
1865 COMMIT;
1866 END IF;
1867
1868 EXCEPTION
1869
1870 WHEN FND_API.G_EXC_ERROR THEN
1871
1872 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1873 l_msg_count := Fnd_Msg_Pub.count_msg;
1874
1875 IF p_commit = FND_API.G_TRUE THEN
1876 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1877 END IF;
1878 IF c_get_fin_task_ver_id%ISOPEN THEN
1879 CLOSE c_get_fin_task_ver_id;
1880 END IF;
1881 IF l_msg_count = 1 AND x_msg_data IS NULL
1882 THEN
1883 Pa_Interface_Utils_Pub.get_messages
1884 ( p_encoded => Fnd_Api.G_TRUE
1885 , p_msg_index => 1
1886 , p_msg_count => l_msg_count
1887 , p_msg_data => l_msg_data
1888 , p_data => l_data
1889 , p_msg_index_out => l_msg_index_out);
1890 x_msg_data := l_data;
1891 x_msg_count := l_msg_count;
1892 ELSE
1893 x_msg_count := l_msg_count;
1894 END IF;
1895 IF l_debug_mode = 'Y' THEN
1896 Pa_Debug.reset_curr_function;
1897 END IF;
1898
1899 WHEN Invalid_Arg_Exc_WP THEN
1900
1901 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1902 x_msg_count := 1;
1903 --x_msg_data := 'PA_PROJ_STRUC_MAPPING_PUB : DELETE_ALL_MAPPING : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
1904
1905 IF p_commit = FND_API.G_TRUE THEN
1906 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1907 END IF;
1908 IF c_get_fin_task_ver_id%ISOPEN THEN
1909 CLOSE c_get_fin_task_ver_id;
1910 END IF;
1911 Fnd_Msg_Pub.add_exc_msg
1912 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1913 , p_procedure_name => 'DELETE_ALL_MAPPING'
1914 , p_error_text => x_msg_data);
1915
1916 IF l_debug_mode = 'Y' THEN
1917 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1918 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1919 l_debug_level5);
1920 Pa_Debug.reset_curr_function;
1921 END IF;
1922 RAISE;
1923
1924 WHEN OTHERS THEN
1925
1926 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1927 x_msg_count := 1;
1928 x_msg_data := SQLERRM;
1929
1930 IF p_commit = FND_API.G_TRUE THEN
1931 ROLLBACK TO DELETE_ALL_MAPPING_PUBLIC;
1932 END IF;
1933
1934 IF c_get_fin_task_ver_id%ISOPEN THEN
1935 CLOSE c_get_fin_task_ver_id;
1936 END IF;
1937
1938 Fnd_Msg_Pub.add_exc_msg
1939 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_PUB'
1940 , p_procedure_name => 'DELETE_ALL_MAPPING'
1941 , p_error_text => x_msg_data);
1942
1943 IF l_debug_mode = 'Y' THEN
1944 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1945 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1946 l_debug_level5);
1947 Pa_Debug.reset_curr_function;
1948 END IF;
1949 RAISE;
1950 END DELETE_ALL_MAPPING ;
1951
1952 END PA_PROJ_STRUC_MAPPING_PUB;
[Home] [Help]