[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CHECK_PVT
Source
1 package body PA_PROJECT_CHECK_PVT as
2 /*$Header: PAPMPCVB.pls 120.17 2011/11/18 13:14:08 krkondur ship $*/
3
4 --Global constants to be used in error messages
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECT_CHECK_PUB';
6 G_PROJECT_CODE CONSTANT VARCHAR2(7) := 'PROJECT';
7 G_CUSTOMER_CODE CONSTANT VARCHAR2(8) := 'CUSTOMER';
8 G_KEY_MEMBER CONSTANT VARCHAR2(10) := 'KEY_MEMBER';
9 G_CLASS_CATEGORY_CODE CONSTANT VARCHAR2(14) := 'CLASS_CATEGORY';
10 G_TASK_CODE CONSTANT VARCHAR2(4) := 'TASK';
11
12 --package global to be used during updates
13 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
14 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
15
16 --------------------------------------------------------------------------------
17 -- Name: Check_Delete_Task_OK_pvt
18 -- Type: PL/SQL Procedure
19 -- Decscription: This procedure returns 'Y' if it is OK to delete a task.
20 -- Otherwise, it returns 'N'.
21 --
22 -- Called Subprograms: Convert_Pm_Projref_To_Id
23 -- , Convert_Pm_Taskref_To_Id
24 -- History: 15-AUG-96 Created jwhite
25 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
26 -- procedure.
27 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
28 -- 20-NOV-96 Update lwerker Changed handling of error messages
29 -- 02-DEC-96 Update lwerker Removed Savepoint and Rollbacks
30
31 PROCEDURE Check_Delete_Task_OK_pvt
32 ( p_api_version_number IN NUMBER
33 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
34 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
35 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
36 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
37 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
38 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
39 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
40 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
41 --Project Structure changes done for bug 2765115
42 , p_structure_type IN VARCHAR2 := 'FINANCIAL'
43 , p_task_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
44 --END Project Structure changes done for bug 2765115
45 , p_delete_task_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
46 )
47 IS
48 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Task_Ok_Pvt';
49 l_value_conversion_error BOOLEAN := FALSE;
50 l_return_status VARCHAR2(1);
51 l_msg_count INTEGER;
52
53 l_err_code NUMBER := -1;
54 l_err_stage VARCHAR2(2000) := NULL;
55 l_err_stack VARCHAR2(2000) := NULL;
56
57 l_project_id_out NUMBER := 0;
58 l_task_id_out NUMBER := 0;
59
60 l_amg_segment1 VARCHAR2(25);
61 l_amg_task_number VARCHAR2(50);
62
63 --needed to get the field values associated to a AMG message
64
65 CURSOR l_amg_project_csr
66 (p_pa_project_id pa_projects.project_id%type)
67 IS
68 SELECT segment1
69 FROM pa_projects p
70 WHERE p.project_id = p_pa_project_id;
71
72 CURSOR l_amg_task_csr
73 (p_pa_task_id pa_tasks.task_id%type)
74 IS
75 SELECT task_number
76 FROM pa_tasks p
77 WHERE p.task_id = p_pa_task_id;
78
79 --bug 2765115
80 l_structure_version_id NUMBER;
81 l_task_version_id NUMBER;
82 l_error_message_code VARCHAR2(4000);
83 --bug 2765115
84
85 l_versioning_enabled VARCHAR2(1) := 'N';
86 l_workplan_enabled VARCHAR2(1) := 'N';
87
88 -- Bug Fix 5263429
89 l_Published_version_exists VARCHAR2(1);
90 l_IS_WP_SEPARATE_FROM_FN VARCHAR2(1);
91 l_IS_WP_VERSIONING_ENABLED VARCHAR2(1);
92 l_structure_type VARCHAR2(30);
93
94 BEGIN
95
96
97 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
98 p_api_version_number ,
99 l_api_name ,
100 G_PKG_NAME )
101 THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 IF FND_API.TO_BOOLEAN( p_init_msg_list )
106 THEN
107 FND_MSG_PUB.initialize;
108 END IF;
109
110 p_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 PA_PROJECT_PVT.Convert_pm_projref_to_id
113 ( p_pm_project_reference => p_pm_project_reference
114 , p_pa_project_id => p_project_id
115 , p_out_project_id => l_project_id_out
116 , p_return_status => l_return_status );
117
118 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
119 THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121
122 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
123 THEN
124
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 -- Added for the bug 4728670
129 /* Bug #5050424: Calling the API Convert_pm_taskref_to_id_all instead
130 of Convert_pm_taskref_to_id so that the Structure Type also gets
131 passed. */
132
133 /* Bug Fix 5263429
134 Issue :
135
136 When ever a task was deleted then the error "Task ID invalid" was thrown and the task deletion failed.
137
138 Analysis:
139
140 This error was also coming up from the Convert_pm_taskref_to_id_all API. There was a call to the
141 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all API in the flow. MSP calls the PA_PROJECT_PUB.check_task_mfd API
142 to determine whether a task can be deleted at all or not. This in turn calls the pa_project_pub.Check_Delete_Task_OK
143 API and that in turn calls the pa_project_check_pvt.Check_Delete_Task_OK_pvt API.
144 In this API the call to PA_PROJECT_PVT.Convert_pm_taskref_to_id_all the structure type is also passed as shown below.
145 As the structure type 'FINANCIAL' was passed the Convert_pm_taskref_to_id_all API was looking for the task in the
146 PA_TASKS table in the cursor l_task_id_csr. As this was not returning any rows the above error is thrown. The task
147 is there but due to the structure type 'WORKPLAN' it is residing in the elements tables as it is not yet published as well.
148 Ideally we should pass 'WORKPLAN' as the structure type. This is being done in the PA_PROJECT_PUB.DELETE_TASK API.
149 -- If the following criteria is satisfied,
150 -- switch flow to WORKPLAN
151 -- i) Workplan is enabled;
152 -- ii) Structure is SHARED;
153 -- iii) Published version exists;
154
155 Solution:
156
157 So we need to pass the structure type as 'WORKPLAN' if the following three conditions are satified.
158 -- i) Workplan is enabled;
159 -- ii) Structure is SHARED;
160 -- iii) Published version exists;
161
162 */
163
164 l_Published_version_exists := PA_PROJ_TASK_STRUC_PUB.Published_version_exists( p_project_id );
165 l_IS_WP_SEPARATE_FROM_FN := PA_PROJ_TASK_STRUC_PUB.IS_WP_SEPARATE_FROM_FN( p_project_id );
166 l_IS_WP_VERSIONING_ENABLED := PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED( p_project_id );
167
168 l_structure_type := p_structure_type;
169
170 IF l_Published_version_exists = 'Y'
171 AND l_IS_WP_SEPARATE_FROM_FN = 'N'
172 AND l_IS_WP_VERSIONING_ENABLED = 'Y'
173 AND p_structure_type = 'FINANCIAL'
174 THEN
175 --Change the flow to WORKPLAN
176 l_structure_type := 'WORKPLAN';
177 END IF;
178
179 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
180 ( p_pa_project_id => l_project_id_out
181 , p_pa_task_id => p_task_id
182 , p_pm_task_reference => p_pm_task_reference
183 , p_structure_type => l_structure_type
184 , p_out_task_id => l_task_id_out
185 , p_return_status => l_return_status );
186
187 -- End of Bug Fix 5263429
188 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
189 THEN
190
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
192
193 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
194 THEN
195
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198 -- end for the bug 4738608
199
200 --Project Structure changes done for bug 2765115
201
202 l_versioning_enabled := PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED( l_project_id_out );
203 l_workplan_enabled := PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS( l_project_id_out );
204
205 IF ( p_task_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
206 OR p_task_version_id IS NULL ) AND
207 l_workplan_enabled = 'Y' AND
208 l_versioning_enabled = 'Y'
209 THEN
210 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
211 THEN
212 pa_interface_utils_pub.map_new_amg_msg
213 ( p_old_message_code => 'PA_PS_TSK_VER_REQ_WP'
214 ,p_msg_attribute => 'CHANGE'
215 ,p_resize_flag => 'N'
216 ,p_msg_context => 'GENERAL'
217 ,p_attribute1 => ''
218 ,p_attribute2 => ''
219 ,p_attribute3 => ''
220 ,p_attribute4 => ''
221 ,p_attribute5 => '');
222 END IF;
223 RAISE FND_API.G_EXC_ERROR;
224 END IF;
225
226 --get structure_version_id and task_version_id
227
228 PA_PROJ_TASK_STRUC_PUB.get_version_ids(
229 p_task_id => l_task_id_out --p_task_id changed for Bug No 4738608
230 ,p_task_version_id => p_task_version_id
231 ,p_project_id => l_project_id_out --p_project_id changed for Bug No 4738608
232 ,x_structure_version_id => l_structure_version_id
233 ,x_task_version_id => l_task_version_id
234 );
235 --Project Structure changes done for bug 2765115
236
237 /*
238 PA_PROJECT_PVT.Convert_pm_projref_to_id
239 ( p_pm_project_reference => p_pm_project_reference
240 , p_pa_project_id => p_project_id
241 , p_out_project_id => l_project_id_out
242 , p_return_status => l_return_status );
243
244 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
245 THEN
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247
248 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
249 THEN
250
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 */ --moved up
254
255
256 --bug 2893028
257
258 IF l_structure_version_id IS NOT NULL AND
259 PA_PROJECT_STRUCTURE_UTILS.is_structure_version_updatable( l_structure_version_id ) = 'N' AND
260 l_workplan_enabled = 'Y'
261 THEN
262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
263 THEN
264 pa_interface_utils_pub.map_new_amg_msg
265 ( p_old_message_code => 'PA_STRUCT_VER_NO_UPDATE'
266 ,p_msg_attribute => 'CHANGE'
267 ,p_resize_flag => 'N'
268 ,p_msg_context => 'GENERAL'
269 ,p_attribute1 => ''
270 ,p_attribute2 => ''
271 ,p_attribute3 => ''
272 ,p_attribute4 => ''
273 ,p_attribute5 => '');
274 END IF;
275 RAISE FND_API.G_EXC_ERROR;
276 END IF;
277 --bug 2893028
278
279 IF p_structure_type = 'FINANCIAL' --Project Structure changes done for bug 2765115
280 THEN
281
282 IF p_task_id IS NOT NULL AND p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
283 THEN
284 l_amg_task_number := null;
285 OPEN l_amg_task_csr( p_task_id );
286 FETCH l_amg_task_csr INTO l_amg_task_number;
287 CLOSE l_amg_task_csr;
288 END IF;
289
290 IF l_versioning_enabled = 'Y'
291 AND PA_PROJ_TASK_STRUC_PUB.Published_version_exists( l_project_id_out ) = 'Y'
292 AND l_amg_task_number IS NULL --if the task is not in pa_tasks
293 THEN
294 --deleting task from a working version when there is a published structure exists
295 null; --in this case there wont be any task in pa_task
296 ELSE
297
298 /* Commented below code and moved it above the call to PA_PROJ_TASK_STRUC_PUB.get_version_ids for bug 4738608
299 PA_PROJECT_PVT.Convert_pm_taskref_to_id
300 ( p_pa_project_id => l_project_id_out
301 , p_pa_task_id => p_task_id
302 , p_pm_task_reference => p_pm_task_reference
303 , p_out_task_id => l_task_id_out
304 , p_return_status => l_return_status );
305
306 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
307 THEN
308
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310
311 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
312 THEN
313
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316 */
317 l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
318 (p_task_number=> ''
319 ,p_task_reference => p_pm_task_reference
320 ,p_task_id => l_task_id_out);
321
322 END IF; --<< check if published version exists and
323
324 END IF;
325
326 -- Get segment1 for AMG messages
327
328 OPEN l_amg_project_csr( l_project_id_out );
329 FETCH l_amg_project_csr INTO l_amg_segment1;
330 CLOSE l_amg_project_csr;
331 /*
332 OPEN l_amg_task_csr( l_task_id_out );
333 FETCH l_amg_task_csr INTO l_amg_task_number;
334 CLOSE l_amg_task_csr;
335 */
336 /* l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
337 (p_task_number=> ''
338 ,p_task_reference => p_pm_task_reference
339 ,p_task_id => l_task_id_out);
340 */
341
342 /* Project Structure changes done for bug 2765115
343 PA_TASK_UTILS.Check_Delete_Task_Ok
344 ( x_task_id => l_task_id_out
345 --bug 3010538 , x_validation_mode => 'R' --bug 2947492
346 , x_err_code => l_err_code
347 , x_err_stage => l_err_stage
348 , x_err_stack => l_err_stack );
349
350 */
351 --Project Structure changes done for bug 2765115
352 PA_PROJ_ELEMENTS_UTILS.Check_Delete_task_Ver_Ok
353 (
354 p_project_id => p_project_id
355 ,p_task_version_id => l_task_version_id
356 ,p_parent_structure_ver_id => l_structure_version_id
357 --bug 3010538 ,p_validation_mode => 'R' --bug 2947492
358 ,x_return_status => l_return_status
359 ,x_error_message_code => l_error_message_code
360 );
361
362 --Project Structure changes done for bug 2765115
363
364 -- IF l_err_code > 0
365 IF l_return_status <> 'S' --Project Structure changes done for bug 2765115
366 THEN
367 p_delete_task_ok_flag := 'N';
368
369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
370 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
371 THEN
372 pa_interface_utils_pub.map_new_amg_msg
373 ( p_old_message_code => 'PA_CHECK_DELETE_TASK_FAILED'
374 ,p_msg_attribute => 'CHANGE'
375 ,p_resize_flag => 'Y'
376 ,p_msg_context => 'DELT'
377 ,p_attribute1 => l_amg_segment1
378 ,p_attribute2 => l_amg_task_number
379 ,p_attribute3 => ''
380 ,p_attribute4 => ''
381 ,p_attribute5 => '');
382 ELSE
383 pa_interface_utils_pub.map_new_amg_msg
384 ( p_old_message_code => l_error_message_code
385 ,p_msg_attribute => 'SPLIT'
386 ,p_resize_flag => 'Y'
387 ,p_msg_context => 'DELT'
388 ,p_attribute1 => l_amg_segment1
389 ,p_attribute2 => l_amg_task_number
390 ,p_attribute3 => ''
391 ,p_attribute4 => ''
392 ,p_attribute5 => '');
393 END IF;
394 END IF;
395 --Project Structure changes done for bug 2765115
396 /* ELSIF l_err_code < 0
397 THEN
398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
399 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
400 THEN
401 pa_interface_utils_pub.map_new_amg_msg
402 ( p_old_message_code => 'PA_CHECK_DELETE_TASK_FAILED'
403 ,p_msg_attribute => 'CHANGE'
404 ,p_resize_flag => 'Y'
405 ,p_msg_context => 'DELT'
406 ,p_attribute1 => l_amg_segment1
407 ,p_attribute2 => l_amg_task_number
408 ,p_attribute3 => ''
409 ,p_attribute4 => ''
410 ,p_attribute5 => '');
411 ELSE
412 pa_interface_utils_pub.map_new_amg_msg
413 ( p_old_message_code => l_err_stage
414 ,p_msg_attribute => 'SPLIT'
415 ,p_resize_flag => 'Y'
416 ,p_msg_context => 'DELT'
417 ,p_attribute1 => l_amg_segment1
418 ,p_attribute2 => l_amg_task_number
419 ,p_attribute3 => ''
420 ,p_attribute4 => ''
421 ,p_attribute5 => '');
422 END IF;
423 END IF;
424 */
425 --Project Structure changes done for bug 2765115
426 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 RAISE FND_API.G_EXC_ERROR;
428
429 ELSE --l_err_code = 0
430 p_delete_task_ok_flag := 'Y';
431 END IF;
432
433 EXCEPTION
434
435 WHEN FND_API.G_EXC_ERROR
436 THEN
437 p_return_status := FND_API.G_RET_STS_ERROR;
438
439 -- 4537865 : RESET OUT PARAM VALUES
440 p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
441
442 FND_MSG_PUB.Count_And_Get
443 (
444 p_encoded => 'F', -- Added for Issues found during Unit Testing 4096218
445 p_count => p_msg_count ,
446 p_data => p_msg_data );
447
448 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
449 THEN
450 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451
452 -- 4537865 : RESET OUT PARAM VALUES
453 p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
454
455 FND_MSG_PUB.Count_And_Get
456 ( p_count => p_msg_count ,
457 p_data => p_msg_data );
458
459 WHEN OTHERS
460 THEN
461 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462
463 -- 4537865 : RESET OUT PARAM VALUES
464 p_delete_task_ok_flag := 'N' ; -- Made this value as 'N' as per logic in the API
465
466 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
467 THEN
468 FND_MSG_PUB.add_exc_msg
469 ( p_pkg_name => G_PKG_NAME
470 , p_procedure_name => l_api_name );
471 END IF;
472 FND_MSG_PUB.Count_And_Get
473 ( p_count => p_msg_count ,
474 p_data => p_msg_data );
475
476
477 END Check_Delete_Task_Ok_pvt;
478
479 --------------------------------------------------------------------------------
480 -- Name: Check_Add_Subtask_OK_pvt
481 -- Type: PL/SQL Procedure
482 -- Decscription: This procedure returns 'Y' if it is OK to add subtask, 'N' otherwise.
483 --
484 -- Called Subprograms: Convert_Pm_Projref_To_Id
485 -- , Convert_Pm_Taskref_To_Id
486 -- History: 15-AUG-96 Created jwhite
487 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
488 -- procedure.
489 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
490 -- 20-NOV-96 Update lwerker Changed handling of error messages
491 -- 02-DEC-96 Update lwerker Removed Savepoint and Rollbacks
492 --
493
494 PROCEDURE Check_Add_Subtask_OK_pvt
495 (p_api_version_number IN NUMBER
496 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
497 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
498 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
499 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
500 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
501 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
502 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
503 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
504 , p_add_subtask_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
505 )
506 IS
507 l_api_name CONSTANT VARCHAR2(30) := 'Check_Add_Subtask_Ok_Pvt';
508 l_return_status VARCHAR2(1);
509 l_msg_count INTEGER;
510
511 l_err_code NUMBER := -1;
512 l_err_stage VARCHAR2(2000) := NULL;
513 l_err_stack VARCHAR2(2000) := NULL;
514
515 l_project_id_out NUMBER := 0;
516 l_task_id_out NUMBER := 0;
517
518 l_amg_segment1 VARCHAR2(25);
519 l_amg_task_number VARCHAR2(50);
520
521 --needed to get the field values associated to a AMG message
522
523 CURSOR l_amg_project_csr
524 (p_pa_project_id pa_projects.project_id%type)
525 IS
526 SELECT segment1
527 FROM pa_projects p
528 WHERE p.project_id = p_pa_project_id;
529
530 CURSOR l_amg_task_csr
531 (p_pa_task_id pa_tasks.task_id%type)
532 IS
533 SELECT task_number
534 FROM pa_tasks p
535 WHERE p.task_id = p_pa_task_id;
536
537 BEGIN
538
539 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
540 p_api_version_number ,
541 l_api_name ,
542 G_PKG_NAME )
543 THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547
548 IF FND_API.TO_BOOLEAN( p_init_msg_list )
549 THEN
550 FND_MSG_PUB.initialize;
551 END IF;
552
553 p_return_status := FND_API.G_RET_STS_SUCCESS;
554
555 PA_PROJECT_PVT.Convert_pm_projref_to_id
556 ( p_pm_project_reference => p_pm_project_reference
557 , p_pa_project_id => p_project_id
558 , p_out_project_id => l_project_id_out
559 , p_return_status => l_return_status );
560
561 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
562 THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564
565 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
566 THEN
567
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570
571
572 PA_PROJECT_PVT.Convert_pm_taskref_to_id
573 ( p_pa_project_id => l_project_id_out
574 , p_pa_task_id => p_task_id
575 , p_pm_task_reference => p_pm_task_reference
576 , p_out_task_id => l_task_id_out
577 , p_return_status => l_return_status );
578
579 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
580 THEN
581
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583
584 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
585 THEN
586
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589
590 -- Get segment1 for AMG messages
591
592 OPEN l_amg_project_csr( l_project_id_out );
593 FETCH l_amg_project_csr INTO l_amg_segment1;
594 CLOSE l_amg_project_csr;
595 /*
596 OPEN l_amg_task_csr( l_task_id_out );
597 FETCH l_amg_task_csr INTO l_amg_task_number;
598 CLOSE l_amg_task_csr;
599 */
600 l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
601 (p_task_number=> ''
602 ,p_task_reference => p_pm_task_reference
603 ,p_task_id => l_task_id_out);
604
605 PA_TASK_UTILS.Check_Create_Subtask_Ok
606 ( x_task_id => l_task_id_out
607 --bug3010538 , x_validation_mode => 'R' --bug 2947492
608 , x_err_code => l_err_code
609 , x_err_stage => l_err_stage
610 , x_err_stack => l_err_stack );
611
612
613 IF l_err_code > 0
614 THEN
615 p_add_subtask_ok_flag := 'N';
616 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
617 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
618 THEN
619 pa_interface_utils_pub.map_new_amg_msg
620 ( p_old_message_code => 'PA_CHECK_ADD_SUBTASK_FAILED'
621 ,p_msg_attribute => 'CHANGE'
622 ,p_resize_flag => 'Y'
623 ,p_msg_context => 'ADDT'
624 ,p_attribute1 => l_amg_segment1
625 ,p_attribute2 => l_amg_task_number
626 ,p_attribute3 => ''
627 ,p_attribute4 => ''
628 ,p_attribute5 => '');
629 ELSE
630 pa_interface_utils_pub.map_new_amg_msg
631 ( p_old_message_code => l_err_stage
632 ,p_msg_attribute => 'SPLIT'
633 ,p_resize_flag => 'Y'
634 ,p_msg_context => 'ADDST'
635 ,p_attribute1 => l_amg_segment1
636 ,p_attribute2 => l_amg_task_number
637 ,p_attribute3 => ''
638 ,p_attribute4 => ''
639 ,p_attribute5 => '');
640 END IF;
641 END IF;
642
643 ELSIF l_err_code < 0
644 THEN
645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
646 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
647 THEN
648 pa_interface_utils_pub.map_new_amg_msg
649 ( p_old_message_code => 'PA_CHECK_ADD_SUBTASK_FAILED'
650 ,p_msg_attribute => 'CHANGE'
651 ,p_resize_flag => 'Y'
652 ,p_msg_context => 'ADDT'
653 ,p_attribute1 => l_amg_segment1
654 ,p_attribute2 => l_amg_task_number
655 ,p_attribute3 => ''
656 ,p_attribute4 => ''
657 ,p_attribute5 => '');
658 ELSE
659 pa_interface_utils_pub.map_new_amg_msg
660 ( p_old_message_code => l_err_stage
661 ,p_msg_attribute => 'SPLIT'
662 ,p_resize_flag => 'Y'
663 ,p_msg_context => 'ADDST'
664 ,p_attribute1 => l_amg_segment1
665 ,p_attribute2 => l_amg_task_number
666 ,p_attribute3 => ''
667 ,p_attribute4 => ''
668 ,p_attribute5 => '');
669 END IF;
670 END IF;
671
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673
674 ELSE --l_err_code = 0
675
676 p_add_subtask_ok_flag := 'Y';
677
678 END IF;
679
680
681 EXCEPTION
682
683 WHEN FND_API.G_EXC_ERROR
684 THEN
685 p_return_status := FND_API.G_RET_STS_ERROR;
686
687 -- 4537865 RESET OUT param value
688 p_add_subtask_ok_flag := 'N' ;
689
690 FND_MSG_PUB.Count_And_Get
691 ( p_count => p_msg_count ,
692 p_data => p_msg_data );
693
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
696 THEN
697 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698
699 -- 4537865 RESET OUT param value
700 p_add_subtask_ok_flag := 'N' ;
701
702 FND_MSG_PUB.Count_And_Get
703 ( p_count => p_msg_count ,
704 p_data => p_msg_data );
705
706
707 WHEN OTHERS
708 THEN
709 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710
711 -- 4537865 RESET OUT param value
712 p_add_subtask_ok_flag := 'N' ;
713
714 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
715 THEN
716 FND_MSG_PUB.add_exc_msg
717 ( p_pkg_name => G_PKG_NAME
718 , p_procedure_name => l_api_name );
719 END IF;
720 FND_MSG_PUB.Count_And_Get
721 ( p_count => p_msg_count ,
722 p_data => p_msg_data );
723
724 END Check_Add_Subtask_Ok_pvt;
725
726
727 --------------------------------------------------------------------------------
728 -- Name: Check_Unique_Task_Ref_pvt
729 -- Type: PL/SQL Procedure
730 -- Decscription: This procedure returns 'Y' if the task reference does not exist,
731 -- 'N' otherwise.
732 --
733 -- Called Subprograms: Convert_Pm_Projref_To_Id
734 --
735 -- History: 15-AUG-96 Created jwhite
736 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
737 -- procedure.
738 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
739 -- 20-NOV-96 Update lwerker Added use of cursor and changed handling of error messages
740 -- 02-DEC-96 Update lwerker Removed Savepoint and Rollbacks
741 --
742 PROCEDURE Check_Unique_Task_Ref_pvt
743 (p_api_version_number IN NUMBER
744 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
745 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
746 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
747 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
748 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
749 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
750 , p_pm_task_reference IN VARCHAR2
751 , p_unique_task_ref_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
752 )
753 IS
754
755 CURSOR l_unique_task_ref_csr (p_project_id IN NUMBER
756 ,p_pm_task_reference IN VARCHAR2 )
757 IS
758 SELECT 1
759 FROM pa_tasks
760 WHERE pm_task_reference = p_pm_task_reference
761 AND project_id = p_project_id;
762
763
764
765 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique_Task_Ref_Pvt';
766 l_value_conversion_error BOOLEAN := FALSE;
767 l_return_status VARCHAR2(1);
768 l_msg_count INTEGER;
769
770 l_err_code NUMBER := -1;
771 l_err_stage VARCHAR2(2000) := NULL;
772 l_err_stack VARCHAR2(2000) := NULL;
773
774 l_project_id_out NUMBER := 0;
775 l_task_id_out NUMBER := 0;
776 l_dummy NUMBER;
777 l_exists_status NUMBER := 1;
778
779 BEGIN
780
781 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
782 p_api_version_number ,
783 l_api_name ,
784 G_PKG_NAME )
785 THEN
786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
787 END IF;
788
789 IF FND_API.TO_BOOLEAN( p_init_msg_list )
790 THEN
791 FND_MSG_PUB.initialize;
792 END IF;
793
794 p_return_status := FND_API.G_RET_STS_SUCCESS;
795
796 PA_PROJECT_PVT.Convert_pm_projref_to_id
797 ( p_pm_project_reference => p_pm_project_reference
798 , p_pa_project_id => p_project_id
799 , p_out_project_id => l_project_id_out
800 , p_return_status => l_return_status );
801
802 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
803 THEN
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805
806 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
807 THEN
808
809 RAISE FND_API.G_EXC_ERROR;
810 END IF;
811
812 OPEN l_unique_task_ref_csr( l_project_id_out, p_pm_task_reference );
813 FETCH l_unique_task_ref_csr INTO l_dummy;
814
815 IF l_unique_task_ref_csr%FOUND
816 THEN
817 p_unique_task_ref_flag := 'N';
818
819 ELSE
820 p_unique_task_ref_flag := 'Y';
821
822 END IF;
823
824 CLOSE l_unique_task_ref_csr;
825
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR
829 THEN
830 p_return_status := FND_API.G_RET_STS_ERROR;
831 -- 4537865 : RESET OUT param values.
832 p_unique_task_ref_flag := 'N' ;
833
834 FND_MSG_PUB.Count_And_Get
835 ( p_count => p_msg_count ,
836 p_data => p_msg_data );
837
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
839 THEN
840 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 -- 4537865 : RESET OUT param values.
842 p_unique_task_ref_flag := 'N' ;
843
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => p_msg_count ,
846 p_data => p_msg_data );
847
848 WHEN OTHERS
849 THEN
850 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
851 -- 4537865 : RESET OUT param values.
852 p_unique_task_ref_flag := 'N' ;
853
854 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
855 THEN
856 FND_MSG_PUB.add_exc_msg
857 ( p_pkg_name => G_PKG_NAME
858 , p_procedure_name => l_api_name );
859 END IF;
860
861 FND_MSG_PUB.Count_And_Get
862 ( p_count => p_msg_count ,
863 p_data => p_msg_data );
864
865
866 END Check_Unique_Task_Ref_pvt;
867
868
869 --------------------------------------------------------------------------------
870 -- Name: Check_Unique_Project_Ref_pvt
871 -- Type: PL/SQL Procedure
872 -- Decscription: This procedure returns 'Y' if the project reference does not exist
873 -- , 'N' otherwise.
874 --
875 -- Called Subprograms: none.
876 --
877 -- History: 15-AUG-96 Created jwhite
878 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
879 -- procedure.
880 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
881 --
882 -- 02-DEC-96 Update lwerker Added use of cursor and changed handling of error messages
883 -- Removed Savepoint and Rollbacks
884 --
885 PROCEDURE Check_Unique_Project_Ref_pvt
886 (p_api_version_number IN NUMBER
887 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
888 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
889 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
890 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
891 , p_pm_project_reference IN VARCHAR2
892 , p_unique_project_ref_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
893 )
894 IS
895
896 CURSOR l_unique_project_ref_csr (p_pm_project_reference IN VARCHAR2 )
897 IS
898 SELECT 1
899 FROM pa_projects
900 WHERE pm_project_reference = p_pm_project_reference;
901
902
903 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique_Project_Ref_Pvt';
904
905 l_return_status VARCHAR2(1);
906 l_msg_count INTEGER;
907
908 l_err_code NUMBER := -1;
909 l_err_stage VARCHAR2(2000) := NULL;
910 l_err_stack VARCHAR2(2000) := NULL;
911
912 l_project_id_out NUMBER := 0;
913 l_task_id_out NUMBER := 0;
914 l_dummy NUMBER := 0;
915 l_exists_status NUMBER := 1;
916
917 BEGIN
918
919 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
920 p_api_version_number ,
921 l_api_name ,
922 G_PKG_NAME )
923 THEN
924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925 END IF;
926
927 IF FND_API.TO_BOOLEAN( p_init_msg_list )
928 THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931
932 p_return_status := FND_API.G_RET_STS_SUCCESS;
933
934 OPEN l_unique_project_ref_csr( p_pm_project_reference );
935 FETCH l_unique_project_ref_csr INTO l_dummy;
936
937 IF l_unique_project_ref_csr%FOUND
938 THEN
939 p_unique_project_ref_flag := 'N';
940
941 ELSE
942 p_unique_project_ref_flag := 'Y';
943
944 END IF;
945
946 CLOSE l_unique_project_ref_csr;
947
948 EXCEPTION
949
950 WHEN FND_API.G_EXC_ERROR
951 THEN
952 p_return_status := FND_API.G_RET_STS_ERROR;
953 -- 4537865 : RESET OUT param values
954 p_unique_project_ref_flag := 'N';
955
956 FND_MSG_PUB.Count_And_Get
957 ( p_count => p_msg_count ,
958 p_data => p_msg_data );
959
960 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
961 THEN
962 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
963 -- 4537865 : RESET OUT param values
964 p_unique_project_ref_flag := 'N';
965
966 FND_MSG_PUB.Count_And_Get
967 ( p_count => p_msg_count ,
968 p_data => p_msg_data );
969
970 WHEN OTHERS
971 THEN
972 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 -- 4537865 : RESET OUT param values
974 p_unique_project_ref_flag := 'N';
975
976 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
977 THEN
978 FND_MSG_PUB.add_exc_msg
979 ( p_pkg_name => G_PKG_NAME
980 , p_procedure_name => l_api_name );
981 END IF;
982
983 FND_MSG_PUB.Count_And_Get
984 ( p_count => p_msg_count ,
985 p_data => p_msg_data );
986
987 END Check_Unique_Project_Ref_pvt;
988
989 --------------------------------------------------------------------------------
990 -- Name: Check_Delete_Project_OK_pvt
991 -- Type: PL/SQL Procedure
992 -- Decscription: This procedure returns 'Y' if the project can be deleted, 'N' otherwise.
993 --
994 -- Called Subprograms: Convert_Pm_Projref_To_Id
995 --
996 -- History: 15-AUG-96 Created jwhite
997 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
998 -- procedure.
999 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
1000 -- 02-DEC-96 Update lwerker Changed handling of error messages
1001 -- Removed Savepoint and Rollbacks
1002 --
1003
1004 PROCEDURE Check_Delete_Project_OK_pvt
1005 (p_api_version_number IN NUMBER
1006 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1007 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1008 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
1009 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1010 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1011 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1012 , p_delete_project_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1013 )
1014 IS
1015 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete_Project_Ok_Pvt';
1016 l_return_status VARCHAR2(1);
1017 l_msg_count INTEGER;
1018
1019 l_err_code NUMBER := -1;
1020 l_err_stage VARCHAR2(2000) := NULL;
1021 l_err_stack VARCHAR2(2000) := NULL;
1022
1023 l_project_id_out NUMBER := 0;
1024 l_task_id_out NUMBER := 0;
1025
1026 l_amg_segment1 VARCHAR2(25);
1027
1028 --needed to get the field values associated to a AMG message
1029
1030 CURSOR l_amg_project_csr
1031 (p_pa_project_id pa_projects.project_id%type)
1032 IS
1033 SELECT segment1
1034 FROM pa_projects p
1035 WHERE p.project_id = p_pa_project_id;
1036
1037 BEGIN
1038
1039 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1040 p_api_version_number ,
1041 l_api_name ,
1042 G_PKG_NAME )
1043 THEN
1044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1045 END IF;
1046
1047 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1048 THEN
1049 FND_MSG_PUB.initialize;
1050 END IF;
1051
1052 p_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054
1055 PA_PROJECT_PVT.Convert_pm_projref_to_id
1056 ( p_pm_project_reference => p_pm_project_reference
1057 , p_pa_project_id => p_project_id
1058 , p_out_project_id => l_project_id_out
1059 , p_return_status => l_return_status
1060 );
1061
1062 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1063
1064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065
1066 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1067
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070
1071 -- Get segment1 for AMG messages
1072
1073 OPEN l_amg_project_csr( l_project_id_out );
1074 FETCH l_amg_project_csr INTO l_amg_segment1;
1075 CLOSE l_amg_project_csr;
1076
1077 PA_PROJECT_UTILS.Check_Delete_Project_Ok
1078 ( x_project_id => l_project_id_out
1079 --bug3010538 , x_validation_mode => 'R' --bug 2947492
1080 , x_err_code => l_err_code
1081 , x_err_stage => l_err_stage
1082 , x_err_stack => l_err_stack);
1083
1084 IF l_err_code > 0
1085 THEN
1086 p_delete_project_ok_flag := 'N';
1087 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1088 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
1089 THEN
1090 pa_interface_utils_pub.map_new_amg_msg
1091 ( p_old_message_code => 'PA_CHECK_DEL_PROJECT_FAILED'
1092 ,p_msg_attribute => 'CHANGE'
1093 ,p_resize_flag => 'Y'
1094 ,p_msg_context => 'PROJ'
1095 ,p_attribute1 => l_amg_segment1
1096 ,p_attribute2 => ''
1097 ,p_attribute3 => ''
1098 ,p_attribute4 => ''
1099 ,p_attribute5 => '');
1100 ELSE
1101 pa_interface_utils_pub.map_new_amg_msg
1102 ( p_old_message_code => l_err_stage
1103 ,p_msg_attribute => 'SPLIT'
1104 ,p_resize_flag => 'Y'
1105 ,p_msg_context => 'DELP'
1106 ,p_attribute1 => l_amg_segment1
1107 ,p_attribute2 => ''
1108 ,p_attribute3 => ''
1109 ,p_attribute4 => ''
1110 ,p_attribute5 => '');
1111 END IF;
1112 END IF;
1113
1114 ELSIF l_err_code < 0
1115 THEN
1116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1117 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
1118 THEN
1119 pa_interface_utils_pub.map_new_amg_msg
1120 ( p_old_message_code => 'PA_CHECK_DEL_PROJECT_FAILED'
1121 ,p_msg_attribute => 'CHANGE'
1122 ,p_resize_flag => 'Y'
1123 ,p_msg_context => 'PROJ'
1124 ,p_attribute1 => l_amg_segment1
1125 ,p_attribute2 => ''
1126 ,p_attribute3 => ''
1127 ,p_attribute4 => ''
1128 ,p_attribute5 => '');
1129 ELSE
1130 pa_interface_utils_pub.map_new_amg_msg
1131 ( p_old_message_code => l_err_stage
1132 ,p_msg_attribute => 'SPLIT'
1133 ,p_resize_flag => 'Y'
1134 ,p_msg_context => 'DELP'
1135 ,p_attribute1 => l_amg_segment1
1136 ,p_attribute2 => ''
1137 ,p_attribute3 => ''
1138 ,p_attribute4 => ''
1139 ,p_attribute5 => '');
1140 END IF;
1141 END IF;
1142
1143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144
1145 ELSE --l_err_code = 0
1146
1147 p_delete_project_ok_flag := 'Y';
1148
1149 END IF;
1150
1151
1152 EXCEPTION
1153
1154 WHEN FND_API.G_EXC_ERROR
1155 THEN
1156 p_return_status := FND_API.G_RET_STS_ERROR;
1157 -- 4537865 : RESET OUT PARAM VALUES
1158 p_delete_project_ok_flag := 'N' ;
1159
1160 FND_MSG_PUB.Count_And_Get
1161 ( p_count => p_msg_count ,
1162 p_data => p_msg_data );
1163
1164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1165 THEN
1166 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1167 -- 4537865 : RESET OUT PARAM VALUES
1168 p_delete_project_ok_flag := 'N' ;
1169
1170 FND_MSG_PUB.Count_And_Get
1171 ( p_count => p_msg_count ,
1172 p_data => p_msg_data );
1173
1174 WHEN OTHERS
1175 THEN
1176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1177 -- 4537865 : RESET OUT PARAM VALUES
1178 p_delete_project_ok_flag := 'N' ;
1179
1180 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1181 THEN
1182 FND_MSG_PUB.add_exc_msg
1183 ( p_pkg_name => G_PKG_NAME
1184 , p_procedure_name => l_api_name );
1185 END IF;
1186
1187 FND_MSG_PUB.Count_And_Get
1188 ( p_count => p_msg_count ,
1189 p_data => p_msg_data );
1190
1191
1192 END Check_Delete_Project_Ok_pvt;
1193
1194 --------------------------------------------------------------------------------
1195 -- Name: Check_Change_Parent_OK_pvt
1196 -- Type: PL/SQL Procedure
1197 -- Decscription: This procedure returns 'Y' if the task can be moved to another parent; 'N' otherwise.
1198 --
1199 -- Called Subprograms: Convert_Pm_Projref_To_Id
1200 --
1201 -- History: 15-AUG-96 Created jwhite
1202 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
1203 -- procedure.
1204 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
1205 -- 02-DEC-96 Update lwerker Changed handling of return values.
1206 -- Removed Savepoint and Rollbacks
1207 --
1208 PROCEDURE Check_Change_Parent_OK_pvt
1209 (p_api_version_number IN NUMBER
1210 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1211 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1212 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
1213 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1214 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1215 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1216 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1217 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1218 , p_new_parent_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1219 , p_pm_new_parent_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1220 , p_change_parent_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1221 )
1222 IS
1223
1224 l_api_name CONSTANT VARCHAR2(30) := 'Check_Change_Parent_Ok_Pvt';
1225
1226 l_return_status VARCHAR2(1);
1227 l_msg_count INTEGER;
1228
1229 l_err_code NUMBER := -1;
1230 l_err_stage VARCHAR2(2000) := NULL;
1231 l_err_stack VARCHAR2(2000) := NULL;
1232
1233 l_project_id_out NUMBER := 0;
1234 l_task_id_out NUMBER := 0;
1235 l_parent_task_id_out NUMBER := 0;
1236 l_top_task_id NUMBER := 0;
1237 l_new_parent_top_task_id NUMBER := 0;
1238 l_change_parent_ok_flag VARCHAR2(1) := NULL;
1239
1240 BEGIN
1241
1242 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1243 p_api_version_number ,
1244 l_api_name ,
1245 G_PKG_NAME )
1246 THEN
1247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1248 END IF;
1249
1250 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1251 THEN
1252 FND_MSG_PUB.initialize;
1253 END IF;
1254
1255 p_return_status := FND_API.G_RET_STS_SUCCESS;
1256
1257
1258 PA_PROJECT_PVT.Convert_pm_projref_to_id
1259 ( p_pm_project_reference => p_pm_project_reference
1260 , p_pa_project_id => p_project_id
1261 , p_out_project_id => l_project_id_out
1262 , p_return_status => l_return_status
1263 );
1264
1265 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1266 THEN
1267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1268
1269 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1270 THEN
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273
1274 PA_PROJECT_PVT.Convert_pm_taskref_to_id
1275 ( p_pa_project_id => l_project_id_out
1276 , p_pa_task_id => p_task_id
1277 , p_pm_task_reference => p_pm_task_reference
1278 , p_out_task_id => l_task_id_out
1279 , p_return_status => l_return_status
1280 );
1281
1282 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1283 THEN
1284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1285
1286 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1287 THEN
1288 RAISE FND_API.G_EXC_ERROR;
1289 END IF;
1290
1291 PA_PROJECT_PVT.Convert_pm_taskref_to_id
1292 ( p_pa_project_id => l_project_id_out
1293 , p_pa_task_id => p_new_parent_task_id
1294 , p_pm_task_reference => p_pm_new_parent_task_reference
1295 , p_out_task_id => l_parent_task_id_out
1296 , p_return_status => l_return_status );
1297
1298 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1299 THEN
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301
1302 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1303 THEN
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306
1307 BEGIN
1308 SELECT top_task_id
1309 INTO l_top_task_id
1310 FROM pa_tasks
1311 WHERE task_id = l_task_id_out;
1312
1313 IF (l_task_id_out = l_top_task_id)
1314 THEN
1315 l_change_parent_ok_flag := 'N'; --Top tasks can not be moved.
1316 END IF;
1317
1318 EXCEPTION
1319 WHEN OTHERS
1320 THEN RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1321 END;
1322
1323
1324 IF l_change_parent_ok_flag IS NULL
1325 THEN
1326 BEGIN
1327 SELECT top_task_id
1328 INTO l_new_parent_top_task_id
1329 FROM pa_tasks
1330 WHERE task_id = l_parent_task_id_out;
1331
1332 IF (l_top_task_id <> l_new_parent_top_task_id )
1333 THEN
1334 l_change_parent_ok_flag := 'N'; --a task can not be moved to another top task
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN OTHERS
1339 THEN RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END;
1341 END IF;
1342
1343 IF l_change_parent_ok_flag IS NULL
1344 THEN
1345 p_change_parent_ok_flag := 'Y';
1346 ELSE
1347 p_change_parent_ok_flag := l_change_parent_ok_flag;
1348 END IF;
1349
1350 EXCEPTION
1351 WHEN FND_API.G_EXC_ERROR
1352 THEN
1353 p_return_status := FND_API.G_RET_STS_ERROR;
1354
1355 -- 4537865
1356 p_change_parent_ok_flag := 'N' ;
1357
1358 FND_MSG_PUB.Count_And_Get
1359 ( p_count => p_msg_count ,
1360 p_data => p_msg_data );
1361
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1363 THEN
1364 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1365 -- 4537865
1366 p_change_parent_ok_flag := 'N' ;
1367
1368 FND_MSG_PUB.Count_And_Get
1369 ( p_count => p_msg_count ,
1370 p_data => p_msg_data );
1371
1372 WHEN OTHERS
1373 THEN
1374 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1375
1376 -- 4537865
1377 p_change_parent_ok_flag := 'N' ;
1378
1379 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1380 THEN
1381 FND_MSG_PUB.add_exc_msg
1382 ( p_pkg_name => G_PKG_NAME
1383 , p_procedure_name => l_api_name );
1384 END IF;
1385
1386 FND_MSG_PUB.Count_And_Get
1387 ( p_count => p_msg_count ,
1388 p_data => p_msg_data );
1389
1390 END Check_Change_Parent_OK_pvt;
1391
1392 --------------------------------------------------------------------------------
1393 -- Name: Check_Change_Proj_Org_OK_pvt
1394 -- Type: PL/SQL Procedure
1395 -- Decscription: This procedure returns 'Y' if the project organization can be changed, 'N' otherwise.
1396 --
1397 -- Called Subprograms: Convert_Pm_Projref_To_Id
1398 --
1399 -- History: 15-AUG-96 Created jwhite
1400 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
1401 -- procedure.
1402 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
1403 -- 02-DEC-96 Update lwerker Changed error handling
1404 -- Removed Savepoint and Rollbacks
1405 --
1406
1407 PROCEDURE Check_Change_Proj_Org_OK_pvt
1408 (p_api_version_number IN NUMBER
1409 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1410 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1411 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
1412 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1413 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1414 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1415 , p_change_project_org_ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1416 )
1417 IS
1418
1419 l_api_name CONSTANT VARCHAR2(30) := 'Check_Change_Proj_Org_OK_Pvt';
1420
1421 l_return_status VARCHAR2(1);
1422 l_msg_count INTEGER;
1423
1424 l_err_code NUMBER := -1;
1425 l_err_stage VARCHAR2(2000) := NULL;
1426 l_err_stack VARCHAR2(2000) := NULL;
1427
1428 l_project_id_out NUMBER := 0;
1429 l_task_id_out NUMBER := 0;
1430
1431 l_amg_segment1 VARCHAR2(25);
1432
1433 --needed to get the field values associated to a AMG message
1434
1435 CURSOR l_amg_project_csr
1436 (p_pa_project_id pa_projects.project_id%type)
1437 IS
1438 SELECT segment1
1439 FROM pa_projects p
1440 WHERE p.project_id = p_pa_project_id;
1441
1442 BEGIN
1443
1444 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1445 p_api_version_number ,
1446 l_api_name ,
1447 G_PKG_NAME )
1448 THEN
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451
1452 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1453 THEN
1454 FND_MSG_PUB.initialize;
1455 END IF;
1456
1457 p_return_status := FND_API.G_RET_STS_SUCCESS;
1458
1459
1460 PA_PROJECT_PVT.Convert_pm_projref_to_id
1461 ( p_pm_project_reference => p_pm_project_reference
1462 , p_pa_project_id => p_project_id
1463 , p_out_project_id => l_project_id_out
1464 , p_return_status => l_return_status
1465 );
1466
1467 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1468 THEN
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1471 THEN
1472 RAISE FND_API.G_EXC_ERROR;
1473 END IF;
1474
1475 -- Get segment1 for AMG messages
1476
1477 OPEN l_amg_project_csr( l_project_id_out );
1478 FETCH l_amg_project_csr INTO l_amg_segment1;
1479 CLOSE l_amg_project_csr;
1480
1481 PA_PROJECT_UTILS.Change_Pt_Org_Ok
1482 ( x_project_id => l_project_id_out
1483 , x_err_code => l_err_code
1484 , x_err_stage => l_err_stage
1485 , x_err_stack => l_err_stack);
1486
1487 IF l_err_code > 0
1488 THEN
1489 p_change_project_org_ok_flag := 'N';
1490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1491 THEN
1492 IF NOT pa_project_pvt.check_valid_message (l_err_stage)
1493 THEN
1494 pa_interface_utils_pub.map_new_amg_msg
1495 ( p_old_message_code => 'PA_PR_CANT_CHG_PROJ_TYPE'
1496 ,p_msg_attribute => 'CHANGE'
1497 ,p_resize_flag => 'N'
1498 ,p_msg_context => 'PROJ'
1499 ,p_attribute1 => l_amg_segment1
1500 ,p_attribute2 => ''
1501 ,p_attribute3 => ''
1502 ,p_attribute4 => ''
1503 ,p_attribute5 => '');
1504 ELSE
1505 pa_interface_utils_pub.map_new_amg_msg
1506 ( p_old_message_code => l_err_stage
1507 ,p_msg_attribute => 'CHANGE'
1508 ,p_resize_flag => 'N'
1509 ,p_msg_context => 'PROJ'
1510 ,p_attribute1 => l_amg_segment1
1511 ,p_attribute2 => ''
1512 ,p_attribute3 => ''
1513 ,p_attribute4 => ''
1514 ,p_attribute5 => '');
1515 END IF;
1516 END IF;
1517
1518 ELSIF l_err_code < 0
1519 THEN
1520 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1521 THEN
1522 IF NOT pa_project_pvt.check_valid_message (l_err_stage)
1523 THEN
1524 pa_interface_utils_pub.map_new_amg_msg
1525 ( p_old_message_code => 'PA_PR_CANT_CHG_PROJ_TYPE'
1526 ,p_msg_attribute => 'CHANGE'
1527 ,p_resize_flag => 'N'
1528 ,p_msg_context => 'PROJ'
1529 ,p_attribute1 => l_amg_segment1
1530 ,p_attribute2 => ''
1531 ,p_attribute3 => ''
1532 ,p_attribute4 => ''
1533 ,p_attribute5 => '');
1534 ELSE
1535 pa_interface_utils_pub.map_new_amg_msg
1536 ( p_old_message_code => l_err_stage
1537 ,p_msg_attribute => 'CHANGE'
1538 ,p_resize_flag => 'N'
1539 ,p_msg_context => 'PROJ'
1540 ,p_attribute1 => l_amg_segment1
1541 ,p_attribute2 => ''
1542 ,p_attribute3 => ''
1543 ,p_attribute4 => ''
1544 ,p_attribute5 => '');
1545 END IF;
1546 END IF;
1547
1548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1549
1550 ELSE --l_err_code = 0
1551
1552 p_change_project_org_ok_flag := 'Y';
1553
1554 END IF;
1555
1556 EXCEPTION
1557 WHEN FND_API.G_EXC_ERROR
1558 THEN
1559 p_return_status := FND_API.G_RET_STS_ERROR;
1560 -- 4537865 : RESET OUT PARAM VALUES
1561 p_change_project_org_ok_flag := 'N' ;
1562
1563 FND_MSG_PUB.Count_And_Get
1564 ( p_count => p_msg_count ,
1565 p_data => p_msg_data );
1566
1567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1568 THEN
1569 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1570 -- 4537865 : RESET OUT PARAM VALUES
1571 p_change_project_org_ok_flag := 'N' ;
1572
1573 FND_MSG_PUB.Count_And_Get
1574 ( p_count => p_msg_count ,
1575 p_data => p_msg_data );
1576
1577 WHEN OTHERS
1578 THEN
1579 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580
1581 -- 4537865 : RESET OUT PARAM VALUES
1582 p_change_project_org_ok_flag := 'N' ;
1583
1584 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1585 THEN
1586 FND_MSG_PUB.add_exc_msg
1587 ( p_pkg_name => G_PKG_NAME
1588 , p_procedure_name => l_api_name );
1589 END IF;
1590 FND_MSG_PUB.Count_And_Get
1591 ( p_count => p_msg_count ,
1592 p_data => p_msg_data );
1593
1594
1595 END Check_Change_Proj_Org_Ok_pvt;
1596
1597 --------------------------------------------------------------------------------
1598 -- Name: Check_Unique_Task_Number_pvt
1599 -- Type: PL/SQL Procedure
1600 -- Decscription: This procedure returns 'Y' if the task number does NOT already exist; 'N' otherwise.
1601 --
1602 -- Called Subprograms: Convert_Pm_Projref_To_Id
1603 --
1604 -- History: 15-AUG-96 Created jwhite
1605 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
1606 -- procedure.
1607 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
1608 -- 02-DEC-96 Update lwerker Changed handling of return values
1609 -- Removed Savepoint and Rollbacks
1610 --
1611
1612 PROCEDURE Check_Unique_Task_Number_pvt
1613 (p_api_version_number IN NUMBER
1614 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1615 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1616 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
1617 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1618 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1619 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1620 , p_task_number IN VARCHAR2
1621 , p_unique_task_number_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1622 )
1623 IS
1624
1625 CURSOR l_unique_task_num_csr (p_project_id IN NUMBER
1626 ,p_task_number IN VARCHAR2 )
1627 IS
1628 SELECT 1
1629 FROM pa_tasks
1630 WHERE task_number = p_task_number
1631 AND project_id = p_project_id;
1632
1633
1634 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique_Task_Number_Pvt';
1635
1636 l_return_status VARCHAR2(1);
1637 l_msg_count INTEGER;
1638
1639 l_err_code NUMBER := -1;
1640 l_err_stage VARCHAR2(2000) := NULL;
1641 l_err_stack VARCHAR2(2000) := NULL;
1642
1643 l_project_id_out NUMBER := 0;
1644 l_dummy NUMBER := 0;
1645
1646
1647 BEGIN
1648
1649 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1650 p_api_version_number ,
1651 l_api_name ,
1652 G_PKG_NAME )
1653 THEN
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 END IF;
1656
1657 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1658 THEN
1659 FND_MSG_PUB.initialize;
1660 END IF;
1661
1662 p_return_status := FND_API.G_RET_STS_SUCCESS;
1663
1664
1665 PA_PROJECT_PVT.Convert_pm_projref_to_id
1666 ( p_pm_project_reference => p_pm_project_reference
1667 , p_pa_project_id => p_project_id
1668 , p_out_project_id => l_project_id_out
1669 , p_return_status => l_return_status
1670 );
1671
1672 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1673 THEN
1674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1676 THEN
1677 RAISE FND_API.G_EXC_ERROR;
1678 END IF;
1679
1680 OPEN l_unique_task_num_csr( l_project_id_out, p_task_number );
1681 FETCH l_unique_task_num_csr INTO l_dummy;
1682
1683 IF l_unique_task_num_csr%FOUND
1684 THEN
1685 p_unique_task_number_flag := 'N';
1686
1687 ELSE
1688 p_unique_task_number_flag := 'Y';
1689
1690 END IF;
1691
1692 CLOSE l_unique_task_num_csr;
1693
1694
1695 EXCEPTION
1696 WHEN FND_API.G_EXC_ERROR
1697 THEN
1698 p_return_status := FND_API.G_RET_STS_ERROR;
1699 -- 4537865 : RESET OUT PARAM VALUES
1700 p_unique_task_number_flag := 'N';
1701
1702 FND_MSG_PUB.Count_And_Get
1703 ( p_count => p_msg_count ,
1704 p_data => p_msg_data );
1705
1706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1707 THEN
1708 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1709 -- 4537865 : RESET OUT PARAM VALUES
1710 p_unique_task_number_flag := 'N';
1711
1712 FND_MSG_PUB.Count_And_Get
1713 ( p_count => p_msg_count ,
1714 p_data => p_msg_data );
1715
1716 WHEN OTHERS
1717 THEN
1718 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719 -- 4537865 : RESET OUT PARAM VALUES
1720 p_unique_task_number_flag := 'N';
1721
1722 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1723 THEN
1724 FND_MSG_PUB.add_exc_msg
1725 ( p_pkg_name => G_PKG_NAME
1726 , p_procedure_name => l_api_name );
1727 END IF;
1728
1729 FND_MSG_PUB.Count_And_Get
1730 ( p_count => p_msg_count ,
1731 p_data => p_msg_data );
1732
1733 END Check_Unique_Task_Number_pvt;
1734
1735 --------------------------------------------------------------------------------
1736 -- Name: Check_Task_Numb_Change_Ok_pvt
1737 -- Type: PL/SQL Procedure
1738 -- Decscription: This procedure returns 'Y' if it is OK to change a LOWEST task.
1739 -- Otherwise, it returns 'N'.
1740 --
1741 -- Called Subprograms: Convert_Pm_Projref_To_Id
1742 -- , Convert_Pm_Taskref_To_Id
1743 -- History: 15-AUG-96 Created jwhite
1744 -- 23-AUG-96 Update jwhite replaced local convert procedure with library
1745 -- procedure.
1746 -- 26-AUG-96 Update jwhite Applied latest messaging standards.
1747 -- 02-DEC-96 Update lwerker Changed the way return values are handled
1748 -- Removed Savepoint and Rollbacks
1749
1750 PROCEDURE Check_Task_Numb_Change_Ok_pvt
1751 ( p_api_version_number IN NUMBER
1752 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1753 , p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1754 , p_msg_count OUT NOCOPY NUMBER /*Added the nocopy check for 4537865 */
1755 , p_msg_data OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1756 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1757 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1758 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1759 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1760 , p_task_number_change_Ok_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1761 )
1762 IS
1763
1764 l_api_name CONSTANT VARCHAR2(30) := ' Check_Task_Numb_Change_Ok_Pvt';
1765
1766 l_return_status VARCHAR2(1);
1767 l_msg_count INTEGER;
1768
1769 l_err_code NUMBER := -1;
1770 l_err_stage VARCHAR2(2000) := NULL;
1771 l_err_stack VARCHAR2(2000) := NULL;
1772
1773 l_project_id_out NUMBER := 0;
1774 l_task_id_out NUMBER := 0;
1775
1776 l_amg_segment1 VARCHAR2(25);
1777 l_amg_task_number VARCHAR2(50);
1778
1779 --needed to get the field values associated to a AMG message
1780
1781 CURSOR l_amg_project_csr
1782 (p_pa_project_id pa_projects.project_id%type)
1783 IS
1784 SELECT segment1
1785 FROM pa_projects p
1786 WHERE p.project_id = p_pa_project_id;
1787
1788 CURSOR l_amg_task_csr
1789 (p_pa_task_id pa_tasks.task_id%type)
1790 IS
1791 SELECT task_number
1792 FROM pa_tasks p
1793 WHERE p.task_id = p_pa_task_id;
1794
1795 BEGIN
1796
1797 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1798 p_api_version_number ,
1799 l_api_name ,
1800 G_PKG_NAME )
1801 THEN
1802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1803 END IF;
1804
1805 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1806 THEN
1807 FND_MSG_PUB.initialize;
1808 END IF;
1809
1810 p_return_status := FND_API.G_RET_STS_SUCCESS;
1811
1812
1813 PA_PROJECT_PVT.Convert_pm_projref_to_id
1814 ( p_pm_project_reference => p_pm_project_reference
1815 , p_pa_project_id => p_project_id
1816 , p_out_project_id => l_project_id_out
1817 , p_return_status => l_return_status
1818 );
1819
1820 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1821 THEN
1822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1823 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1824 THEN
1825 RAISE FND_API.G_EXC_ERROR;
1826 END IF;
1827
1828 PA_PROJECT_PVT.Convert_pm_taskref_to_id
1829 ( p_pa_project_id => l_project_id_out
1830 , p_pa_task_id => p_task_id
1831 , p_pm_task_reference => p_pm_task_reference
1832 , p_out_task_id => l_task_id_out
1833 , p_return_status => l_return_status
1834 );
1835
1836 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1837 THEN
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
1840 THEN
1841 RAISE FND_API.G_EXC_ERROR;
1842 END IF;
1843
1844 -- Get segment1 for AMG messages
1845
1846 OPEN l_amg_project_csr( l_project_id_out );
1847 FETCH l_amg_project_csr INTO l_amg_segment1;
1848 CLOSE l_amg_project_csr;
1849 /*
1850 OPEN l_amg_task_csr( l_task_id_out );
1851 FETCH l_amg_task_csr INTO l_amg_task_number;
1852 CLOSE l_amg_task_csr;
1853 */
1854 l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
1855 (p_task_number=> ''
1856 ,p_task_reference => p_pm_task_reference
1857 ,p_task_id => l_task_id_out);
1858
1859 PA_TASK_UTILS.Change_Lowest_Task_Num_Ok
1860 ( x_task_id => l_task_id_out
1861 , x_err_code => l_err_code
1862 , x_err_stage => l_err_stage
1863 , x_err_stack => l_err_stack
1864 );
1865
1866 IF l_err_code > 0
1867 THEN
1868 p_task_number_change_Ok_flag := 'N';
1869 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1870 THEN
1871 IF NOT pa_project_pvt.check_valid_message (l_err_stage)
1872 THEN
1873 pa_interface_utils_pub.map_new_amg_msg
1874 ( p_old_message_code => 'PA_CHANGE_TASK_NUM_OK_FAILED'
1875 ,p_msg_attribute => 'CHANGE'
1876 ,p_resize_flag => 'Y'
1877 ,p_msg_context => 'MODT'
1878 ,p_attribute1 => l_amg_segment1
1879 ,p_attribute2 => l_amg_task_number
1880 ,p_attribute3 => ''
1881 ,p_attribute4 => ''
1882 ,p_attribute5 => '');
1883 ELSE
1884 pa_interface_utils_pub.map_new_amg_msg
1885 ( p_old_message_code => l_err_stage
1886 ,p_msg_attribute => 'CHANGE'
1887 ,p_resize_flag => 'N'
1888 ,p_msg_context => 'MODT'
1889 ,p_attribute1 => l_amg_segment1
1890 ,p_attribute2 => l_amg_task_number
1891 ,p_attribute3 => ''
1892 ,p_attribute4 => ''
1893 ,p_attribute5 => '');
1894 END IF;
1895 END IF;
1896
1897 ELSIF l_err_code < 0
1898 THEN
1899
1900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1901 THEN
1902 IF NOT pa_project_pvt.check_valid_message (l_err_stage)
1903 THEN
1904 pa_interface_utils_pub.map_new_amg_msg
1905 ( p_old_message_code => 'PA_CHANGE_TASK_NUM_OK_FAILED'
1906 ,p_msg_attribute => 'CHANGE'
1907 ,p_resize_flag => 'Y'
1908 ,p_msg_context => 'MODT'
1909 ,p_attribute1 => l_amg_segment1
1910 ,p_attribute2 => l_amg_task_number
1911 ,p_attribute3 => ''
1912 ,p_attribute4 => ''
1913 ,p_attribute5 => '');
1914 ELSE
1915 pa_interface_utils_pub.map_new_amg_msg
1916 ( p_old_message_code => l_err_stage
1917 ,p_msg_attribute => 'CHANGE'
1918 ,p_resize_flag => 'N'
1919 ,p_msg_context => 'MODT'
1920 ,p_attribute1 => l_amg_segment1
1921 ,p_attribute2 => l_amg_task_number
1922 ,p_attribute3 => ''
1923 ,p_attribute4 => ''
1924 ,p_attribute5 => '');
1925 END IF;
1926 END IF;
1927
1928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1929
1930 ELSE --l_err_code = 0
1931
1932 p_task_number_change_Ok_flag := 'Y';
1933
1934 END IF;
1935
1936
1937 EXCEPTION
1938 WHEN FND_API.G_EXC_ERROR
1939 THEN
1940 p_return_status := FND_API.G_RET_STS_ERROR;
1941 -- 4537865 : RESET OUT PARAM VALUES
1942 p_task_number_change_Ok_flag := 'N' ;
1943
1944 FND_MSG_PUB.Count_And_Get
1945 ( p_count => p_msg_count ,
1946 p_data => p_msg_data );
1947
1948 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1949 THEN
1950 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1951 -- 4537865 : RESET OUT PARAM VALUES
1952 p_task_number_change_Ok_flag := 'N' ;
1953
1954 FND_MSG_PUB.Count_And_Get
1955 ( p_count => p_msg_count ,
1956 p_data => p_msg_data );
1957
1958 WHEN OTHERS
1959 THEN
1960 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1961 -- 4537865 : RESET OUT PARAM VALUES
1962 p_task_number_change_Ok_flag := 'N' ;
1963
1964 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1965 FND_MSG_PUB.add_exc_msg
1966 ( p_pkg_name => G_PKG_NAME
1967 , p_procedure_name => l_api_name );
1968 END IF;
1969
1970 FND_MSG_PUB.Count_And_Get
1971 ( p_count => p_msg_count ,
1972 p_data => p_msg_data );
1973
1974 END Check_Task_Numb_Change_Ok_Pvt;
1975
1976 --====================================================================================
1977 --Name: Validate_billing_info_Pvt
1978 --
1979 --Type: Procedure
1980 --Description: This procedure can be used to validate billing information
1981 -- for contract type projects
1982 --
1983 --
1984 --Called subprograms: none
1985 --
1986 --
1987 --
1988 --History:
1989 -- automn-1996 Ramesh K. Created
1990 --
1991 PROCEDURE Validate_billing_info_Pvt
1992 (p_project_id IN NUMBER, --Added for Bug 5643876
1993 p_project_class_code IN VARCHAR2,
1994 p_in_task_rec IN pa_project_pub.task_in_rec_type,
1995 p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
1996 ) IS
1997 --Added for Bug 5643876
1998 l_project_id pa_projects_all.project_id%type;
1999
2000 /* Commented for Bug No 4721987
2001 CURSOR l_labor_bill_rate_org_csr (l_org_id IN NUMBER ) IS
2002 SELECT 'x' FROM
2003 pa_organizations_v o,
2004 pa_std_bill_rate_schedules brs
2005 WHERE o.organization_id = l_org_id
2006 AND o.organization_id = brs.organization_id
2007 AND brs.schedule_type <> 'NON-LABOR';*/
2008
2009 --- Start of Addition for Bug no 4721987
2010 /*Start of changes for Bug 5643876. Modifying the cursors l_job_brs_csr and l_emp_brs_csr.*/
2011
2012 /*CURSOR l_job_brs_csr (l_job_rate_schdid IN NUMBER) IS
2013 SELECT 'x'
2014 FROM
2015 pa_std_bill_rate_schedules brs
2016 WHERE brs.schedule_type = 'JOB'
2017 AND brs.BILL_RATE_SCH_ID = l_job_rate_schdid;
2018
2019 CURSOR l_emp_brs_csr (l_emp_rate_schdid IN NUMBER) IS
2020 SELECT 'x'
2021 FROM
2022 pa_std_bill_rate_schedules brs
2023 WHERE brs.schedule_type = 'EMPLOYEE'
2024 AND brs.BILL_RATE_SCH_ID = l_emp_rate_schdid;*/
2025
2026 CURSOR l_job_brs_csr( l_job_rate_schdid NUMBER)
2027 IS
2028 SELECT 'x'
2029 FROM pa_std_bill_rate_schedules_all brs, pa_project_types_all pt, pa_projects pa
2030 WHERE bill_rate_sch_id = l_job_rate_schdid
2031 AND pa.project_id = l_project_id
2032 AND brs.job_group_id = pt.bill_job_group_id
2033 and brs.schedule_type = 'JOB'
2034 AND pa.project_type = pt.project_type
2035 AND pa.org_id = pt.org_id --added for Bug 5675391
2036 AND ( pa.multi_currency_BILLING_flag = 'Y'
2037 OR (pa.multi_currency_billing_flag = 'N'
2038 AND brs.rate_sch_currency_code = pa.projfunc_currency_code))
2039 AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
2040 or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'N'
2041 and brs.org_id = pa.org_id))
2042 and (brs.share_across_ou_flag = 'Y'
2043 OR (brs.share_across_ou_flag = 'N'
2044 and brs.org_id = pa.org_id
2045 ));
2046
2047 CURSOR l_emp_brs_csr( l_emp_rate_schdid NUMBER)
2048 IS
2049 SELECT 'x'
2050 FROM pa_std_bill_rate_schedules_all brs, pa_projects pa
2051 WHERE brs.bill_rate_sch_id = l_emp_rate_schdid
2052 AND pa.project_id = l_project_id
2053 and brs.schedule_type = 'EMPLOYEE'
2054 AND ( pa.multi_currency_BILLING_flag = 'Y'
2055 OR (pa.multi_currency_billing_flag='N'
2056 AND brs.rate_sch_currency_code = pa.projfunc_currency_code))
2057 AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
2058 or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'N'
2059 and brs.org_id = pa.org_id))
2060 and (brs.share_across_ou_flag = 'Y'
2061 OR (brs.share_across_ou_flag = 'N'
2062 and brs.org_id = pa.org_id
2063 ));
2064
2065 /* End of changes for bug 5643876*/
2066
2067 -- End of addition for bug 4721987
2068
2069 CURSOR l_non_labor_bill_rate_org_csr (l_org_id IN NUMBER ) IS
2070 SELECT 'x' FROM
2071 pa_organizations_v o,
2072 pa_std_bill_rate_schedules brs
2073 WHERE o.organization_id = l_org_id
2074 AND o.organization_id = brs.organization_id
2075 AND brs.schedule_type = 'NON-LABOR';
2076
2077 /* Commented for Bug no 4721987
2078 CURSOR l_labor_brs_csr (l_org_id IN NUMBER ,l_bill_rate_schdl IN VARCHAR2) IS
2079 SELECT 'x'
2080 FROM
2081 pa_std_bill_rate_schedules brs,
2082 pa_lookups l
2083 WHERE organization_id = l_org_id
2084 AND brs.schedule_type <> 'NON-LABOR'
2085 AND l.lookup_type = 'SCHEDULE TYPE'
2086 AND l.lookup_code (+) = brs.schedule_type
2087 AND brs.std_bill_rate_schedule = l_bill_rate_schdl;*/
2088
2089 CURSOR l_non_labor_brs_csr (l_org_id IN NUMBER,l_bill_rate_schdl IN VARCHAR2) IS
2090 SELECT 'x'
2091 FROM
2092 pa_std_bill_rate_schedules brs,
2093 pa_lookups l
2094 WHERE organization_id = l_org_id
2095 AND brs.schedule_type = 'NON-LABOR'
2096 AND l.lookup_type = 'SCHEDULE TYPE'
2097 AND l.lookup_code (+) = brs.schedule_type
2098 AND brs.std_bill_rate_schedule = l_bill_rate_schdl;
2099
2100 CURSOR l_burden_sch_csr (l_sch_id IN NUMBER ) IS
2101 SELECT 'x'
2102 FROM pa_lookups l, pa_ind_rate_schedules irs
2103 WHERE l.lookup_type = 'IND RATE SCHEDULE TYPE'
2104 AND l.lookup_code = irs.ind_rate_schedule_type
2105 AND irs.ind_rate_sch_id = l_sch_id
2106 AND irs.project_id IS NULL
2107 AND TRUNC(SYSDATE) BETWEEN TRUNC(irs.start_date_active )
2108 AND NVL (irs.end_date_active,TRUNC(SYSDATE)) ;
2109
2110 l_task_rec pa_project_pub.task_in_rec_type;
2111 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Billing_Info_Pvt';
2112 l_dummy VARCHAR2(1);
2113 l_amg_segment1 VARCHAR2(25);
2114 l_amg_task_number VARCHAR2(50);
2115 l_amg_project_id NUMBER;
2116
2117
2118 --needed to get the field values associated to a AMG message
2119
2120 CURSOR l_amg_project_csr
2121 (p_pa_project_id pa_projects.project_id%type)
2122 IS
2123 SELECT segment1
2124 FROM pa_projects p
2125 WHERE p.project_id = p_pa_project_id;
2126
2127 CURSOR l_amg_task_csr
2128 (p_pa_task_id pa_tasks.task_id%type)
2129 IS
2130 SELECT project_id
2131 FROM pa_tasks p
2132 WHERE p.task_id = p_pa_task_id;
2133
2134 BEGIN
2135
2136 -- Initialize p_return_status as Success
2137 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2138 l_project_id := p_project_id; -- Added for bug 5643876
2139 l_task_rec := p_in_task_rec;
2140
2141 -- Get segment1 for AMG messages
2142 /*
2143 OPEN l_amg_task_csr( l_task_rec.pa_task_id );
2144 FETCH l_amg_task_csr INTO l_amg_project_id;
2145 CLOSE l_amg_task_csr;
2146
2147 OPEN l_amg_project_csr( l_amg_project_id );
2148 FETCH l_amg_project_csr INTO l_amg_segment1;
2149 CLOSE l_amg_project_csr;
2150
2151 l_amg_task_number := l_task_rec.pa_task_number;
2152 */
2153
2154 -- Need to validate all fields if CONTRACT project
2155 IF p_project_class_code = 'CONTRACT' THEN
2156
2157 IF (l_task_rec.labor_sch_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2158 AND l_task_rec.labor_sch_type IS NOT NULL)
2159 AND l_task_rec.labor_sch_type NOT IN ('I','B')
2160 THEN
2161 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2162 THEN
2163 pa_interface_utils_pub.map_new_amg_msg
2164 ( p_old_message_code => 'PA_INVALID_LABOR_SCH_TYPE'
2165 ,p_msg_attribute => 'CHANGE'
2166 ,p_resize_flag => 'N'
2167 ,p_msg_context => 'GENERAL'
2168 ,p_attribute1 => ''
2169 ,p_attribute2 => ''
2170 ,p_attribute3 => ''
2171 ,p_attribute4 => ''
2172 ,p_attribute5 => '');
2173 END IF;
2174 RAISE FND_API.G_EXC_ERROR;
2175 END IF;
2176
2177 IF (l_task_rec.non_labor_sch_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2178 AND l_task_rec.non_labor_sch_type IS NOT NULL)
2179 AND l_task_rec.non_labor_sch_type NOT IN ('I','B')
2180 THEN
2181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2182 THEN
2183 pa_interface_utils_pub.map_new_amg_msg
2184 ( p_old_message_code => 'PA_INVALID_NON_LABOR_SCH_TYPE'
2185 ,p_msg_attribute => 'CHANGE'
2186 ,p_resize_flag => 'Y'
2187 ,p_msg_context => 'GENERAL'
2188 ,p_attribute1 => ''
2189 ,p_attribute2 => ''
2190 ,p_attribute3 => ''
2191 ,p_attribute4 => ''
2192 ,p_attribute5 => '');
2193 END IF;
2194 RAISE FND_API.G_EXC_ERROR;
2195 END IF;
2196 /* Commented for Bug No 4721987
2197 IF l_task_rec.labor_sch_type = 'I' -- (I- burden schedule B-Bill rate sch)
2198 -- ensure that bill rate orgid is not supplied
2199 AND (l_task_rec.labor_bill_rate_org_id IS NOT NULL
2200 AND l_task_rec.labor_bill_rate_org_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2201 THEN
2202 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2203 THEN
2204 pa_interface_utils_pub.map_new_amg_msg
2205 ( p_old_message_code => 'PA_LBR_ORG_ID_NOT_VALID'
2206 ,p_msg_attribute => 'CHANGE'
2207 ,p_resize_flag => 'N'
2208 ,p_msg_context => 'GENERAL'
2209 ,p_attribute1 => ''
2210 ,p_attribute2 => ''
2211 ,p_attribute3 => ''
2212 ,p_attribute4 => ''
2213 ,p_attribute5 => '');
2214 END IF;
2215 RAISE FND_API.G_EXC_ERROR;
2216 END IF;*/
2217
2218 IF l_task_rec.non_labor_sch_type = 'I' --(I- burden schedule B-Bill rate sch)
2219 -- ensure that bill rate orgid is not supplied
2220 AND (l_task_rec.non_labor_bill_rate_org_id IS NOT NULL
2221 AND l_task_rec.non_labor_bill_rate_org_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2222 THEN
2223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2224 THEN
2225 pa_interface_utils_pub.map_new_amg_msg
2226 ( p_old_message_code => 'PA_NON_LBR_ORG_ID_NOT_VALID'
2227 ,p_msg_attribute => 'CHANGE'
2228 ,p_resize_flag => 'Y'
2229 ,p_msg_context => 'GENERAL'
2230 ,p_attribute1 => ''
2231 ,p_attribute2 => ''
2232 ,p_attribute3 => ''
2233 ,p_attribute4 => ''
2234 ,p_attribute5 => '');
2235 END IF;
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239
2240 IF (l_task_rec.labor_sch_type = 'I' OR l_task_rec.non_labor_sch_type = 'I' )
2241 THEN
2242
2243 -- ensure that invoice schedule is specified if labor or non labor
2244 -- sch types are burden schedules
2245
2246 IF l_task_rec.inv_ind_rate_sch_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2247 OR l_task_rec.inv_ind_rate_sch_id IS NULL
2248 THEN
2249 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2250 THEN
2251 pa_interface_utils_pub.map_new_amg_msg
2252 ( p_old_message_code => 'PA_INV_IND_RATE_SCH_ID_REQD'
2253 ,p_msg_attribute => 'CHANGE'
2254 ,p_resize_flag => 'Y'
2255 ,p_msg_context => 'GENERAL'
2256 ,p_attribute1 => ''
2257 ,p_attribute2 => ''
2258 ,p_attribute3 => ''
2259 ,p_attribute4 => ''
2260 ,p_attribute5 => '');
2261 END IF;
2262 RAISE FND_API.G_EXC_ERROR;
2263 END IF;
2264
2265 -- ensure that revenue schedule is specified
2266 IF l_task_rec.rev_ind_rate_sch_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2267 OR l_task_rec.rev_ind_rate_sch_id IS NULL
2268 THEN
2269 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2270 THEN
2271 pa_interface_utils_pub.map_new_amg_msg
2272 ( p_old_message_code => 'PA_REV_IND_RATE_SCH_ID_REQD'
2273 ,p_msg_attribute => 'CHANGE'
2274 ,p_resize_flag => 'Y'
2275 ,p_msg_context => 'GENERAL'
2276 ,p_attribute1 => ''
2277 ,p_attribute2 => ''
2278 ,p_attribute3 => ''
2279 ,p_attribute4 => ''
2280 ,p_attribute5 => '');
2281 END IF;
2282 RAISE FND_API.G_EXC_ERROR;
2283 END IF;
2284
2285 -- validate invoice schedule
2286 OPEN l_burden_sch_csr (l_task_rec.inv_ind_rate_sch_id);
2287 FETCH l_burden_sch_csr INTO l_dummy;
2288
2289 IF l_burden_sch_csr%NOTFOUND
2290 THEN
2291
2292 CLOSE l_burden_sch_csr;
2293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2294 THEN
2295 pa_interface_utils_pub.map_new_amg_msg
2296 ( p_old_message_code => 'PA_INV_IND_RATE_SCH_ID_INV'
2297 ,p_msg_attribute => 'CHANGE'
2298 ,p_resize_flag => 'N'
2299 ,p_msg_context => 'GENERAL'
2300 ,p_attribute1 => ''
2301 ,p_attribute2 => ''
2302 ,p_attribute3 => ''
2303 ,p_attribute4 => ''
2304 ,p_attribute5 => '');
2305 END IF;
2306 RAISE FND_API.G_EXC_ERROR;
2307
2308 ELSE
2309 CLOSE l_burden_sch_csr;
2310 END IF;
2311
2312 OPEN l_burden_sch_csr (l_task_rec.rev_ind_rate_sch_id);
2313 FETCH l_burden_sch_csr INTO l_dummy;
2314
2315 IF l_burden_sch_csr%NOTFOUND
2316 THEN
2317 CLOSE l_burden_sch_csr;
2318 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2319 THEN
2320 pa_interface_utils_pub.map_new_amg_msg
2321 ( p_old_message_code => 'PA_REV_IND_RATE_SCH_ID_INV'
2322 ,p_msg_attribute => 'CHANGE'
2323 ,p_resize_flag => 'N'
2324 ,p_msg_context => 'GENERAL'
2325 ,p_attribute1 => ''
2326 ,p_attribute2 => ''
2327 ,p_attribute3 => ''
2328 ,p_attribute4 => ''
2329 ,p_attribute5 => '');
2330 END IF;
2331 RAISE FND_API.G_EXC_ERROR;
2332
2333 ELSE
2334 CLOSE l_burden_sch_csr;
2335 END IF;
2336
2337 END IF;
2338 /*Commented for Bug no 4721987
2339 IF l_task_rec.labor_sch_type = 'B' -- (I- burden schedule B-Bill rate sch)
2340 THEN
2341
2342 IF (l_task_rec.labor_bill_rate_org_id IS NULL
2343 OR l_task_rec.labor_bill_rate_org_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2344 THEN
2345 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2346 THEN
2347 pa_interface_utils_pub.map_new_amg_msg
2348 ( p_old_message_code => 'PA_LBR_ORG_ID_REQD'
2349 ,p_msg_attribute => 'CHANGE'
2350 ,p_resize_flag => 'N'
2351 ,p_msg_context => 'GENERAL'
2352 ,p_attribute1 => ''
2353 ,p_attribute2 => ''
2354 ,p_attribute3 => ''
2355 ,p_attribute4 => ''
2356 ,p_attribute5 => '');
2357 END IF;
2358 RAISE FND_API.G_EXC_ERROR;
2359 END IF;
2360
2361 OPEN l_labor_bill_rate_org_csr (l_task_rec.labor_bill_rate_org_id);
2362 FETCH l_labor_bill_rate_org_csr INTO l_dummy;
2363
2364 IF l_labor_bill_rate_org_csr%NOTFOUND
2365 THEN
2366 CLOSE l_labor_bill_rate_org_csr;
2367 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2368 THEN
2369 pa_interface_utils_pub.map_new_amg_msg
2370 ( p_old_message_code => 'PA_LBR_ORG_ID_INVALID'
2371 ,p_msg_attribute => 'CHANGE'
2372 ,p_resize_flag => 'N'
2373 ,p_msg_context => 'GENERAL'
2374 ,p_attribute1 => ''
2375 ,p_attribute2 => ''
2376 ,p_attribute3 => ''
2377 ,p_attribute4 => ''
2378 ,p_attribute5 => '');
2379 END IF;
2380 RAISE FND_API.G_EXC_ERROR;
2381
2382 ELSE
2383 CLOSE l_labor_bill_rate_org_csr;
2384 END IF;
2385
2386 IF (l_task_rec.labor_std_bill_rate_schdl IS NOT NULL
2387 AND l_task_rec.labor_std_bill_rate_schdl <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
2388 THEN
2389 OPEN l_labor_brs_csr (l_task_rec.labor_bill_rate_org_id,
2390 l_task_rec.labor_std_bill_rate_schdl );
2391 FETCH l_labor_brs_csr INTO l_dummy;
2392
2393 IF l_labor_brs_csr%NOTFOUND
2394 THEN
2395 CLOSE l_labor_brs_csr;
2396 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2397 THEN
2398 pa_interface_utils_pub.map_new_amg_msg
2399 ( p_old_message_code => 'PA_LBR_BRS_INVALID'
2400 ,p_msg_attribute => 'CHANGE'
2401 ,p_resize_flag => 'N'
2402 ,p_msg_context => 'GENERAL'
2403 ,p_attribute1 => ''
2404 ,p_attribute2 => ''
2405 ,p_attribute3 => ''
2406 ,p_attribute4 => ''
2407 ,p_attribute5 => '');
2408 END IF;
2409 RAISE FND_API.G_EXC_ERROR;
2410
2411 ELSE
2412 CLOSE l_labor_brs_csr;
2413 END IF;
2414 END IF;
2415
2416 END IF;*/
2417
2418 /*Start of addition for Bug No 4721987
2419 For labor_sch_type = 'B' If PJR is licensed then a valid job_bill_rate_schedule_id is mandatory
2420 Else either a valid job_bill_rate_schedule_id or emp_bill_rate_schedule_id should be present*/
2421
2422 IF l_task_rec.labor_sch_type = 'B' -- (I- burden schedule B-Bill rate sch)
2423 THEN
2424 IF PA_INSTALL.IS_PRM_LICENSED = 'Y'
2425 THEN
2426 IF (l_task_rec.job_bill_rate_schedule_id IS NULL
2427 OR l_task_rec.job_bill_rate_schedule_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2428 THEN
2429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2430 THEN
2431 pa_interface_utils_pub.map_new_amg_msg
2432 ( p_old_message_code => 'PA_JOB_SCH_ID_NOT_NULL'
2433 ,p_msg_attribute => 'CHANGE'
2434 ,p_resize_flag => 'N'
2435 ,p_msg_context => 'GENERAL'
2436 ,p_attribute1 => ''
2437 ,p_attribute2 => ''
2438 ,p_attribute3 => ''
2439 ,p_attribute4 => ''
2440 ,p_attribute5 => '');
2441 END IF;
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444 ELSE
2445 IF (l_task_rec.job_bill_rate_schedule_id IS NULL
2446 OR l_task_rec.job_bill_rate_schedule_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) AND
2447 (l_task_rec.emp_bill_rate_schedule_id IS NULL
2448 OR l_task_rec.emp_bill_rate_schedule_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2449 THEN
2450 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2451 THEN
2452 pa_interface_utils_pub.map_new_amg_msg
2453 ( p_old_message_code => 'PA_EJ_BILL_RT_SCH_NOT_NULL'
2454 ,p_msg_attribute => 'CHANGE'
2455 ,p_resize_flag => 'N'
2456 ,p_msg_context => 'GENERAL'
2457 ,p_attribute1 => ''
2458 ,p_attribute2 => ''
2459 ,p_attribute3 => ''
2460 ,p_attribute4 => ''
2461 ,p_attribute5 => '');
2462 END IF;
2463 RAISE FND_API.G_EXC_ERROR;
2464 END IF;
2465 END IF; -- end PA_INSTALL.IS_PRM_LICENSED = 'Y'
2466 IF (l_task_rec.job_bill_rate_schedule_id IS NOT NULL
2467 AND l_task_rec.job_bill_rate_schedule_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2468 THEN
2469 OPEN l_job_brs_csr (l_task_rec.job_bill_rate_schedule_id );
2470 FETCH l_job_brs_csr INTO l_dummy;
2471
2472 IF l_job_brs_csr%NOTFOUND
2473 THEN
2474 CLOSE l_job_brs_csr;
2475 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2476 THEN
2477 pa_interface_utils_pub.map_new_amg_msg
2478 ( p_old_message_code => 'PA_JOB_SCH_ID_NOT_NULL'
2479 ,p_msg_attribute => 'CHANGE'
2480 ,p_resize_flag => 'N'
2481 ,p_msg_context => 'GENERAL'
2482 ,p_attribute1 => ''
2483 ,p_attribute2 => ''
2484 ,p_attribute3 => ''
2485 ,p_attribute4 => ''
2486 ,p_attribute5 => '');
2487 END IF;
2488 RAISE FND_API.G_EXC_ERROR;
2489 ELSE
2490 CLOSE l_job_brs_csr;
2491 END IF;
2492 END IF;
2493 IF (l_task_rec.emp_bill_rate_schedule_id IS NOT NULL
2494 AND l_task_rec.emp_bill_rate_schedule_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2495 THEN
2496 OPEN l_emp_brs_csr (l_task_rec.emp_bill_rate_schedule_id );
2497 FETCH l_emp_brs_csr INTO l_dummy;
2498
2499 IF l_emp_brs_csr%NOTFOUND
2500 THEN
2501 CLOSE l_emp_brs_csr;
2502 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2503 THEN
2504 pa_interface_utils_pub.map_new_amg_msg
2505 ( p_old_message_code => 'PA_INVALID_EMP_SCH_ID'
2506 ,p_msg_attribute => 'CHANGE'
2507 ,p_resize_flag => 'N'
2508 ,p_msg_context => 'GENERAL'
2509 ,p_attribute1 => ''
2510 ,p_attribute2 => ''
2511 ,p_attribute3 => ''
2512 ,p_attribute4 => ''
2513 ,p_attribute5 => '');
2514 END IF;
2515 RAISE FND_API.G_EXC_ERROR;
2516 ELSE
2517 CLOSE l_emp_brs_csr;
2518 END IF;
2519 END IF;
2520
2521 END IF; -- IF l_task_rec.labor_sch_type = 'B'
2522
2523 /* End of addition for bug 4721987 */
2524
2525 IF l_task_rec.non_labor_sch_type = 'B'--(I- burden schedule B-Bill rate sch)
2526 THEN
2527
2528 IF (l_task_rec.non_labor_bill_rate_org_id IS NULL
2529 OR l_task_rec.non_labor_bill_rate_org_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )
2530 THEN
2531 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2532 THEN
2533 pa_interface_utils_pub.map_new_amg_msg
2534 ( p_old_message_code => 'PA_NON_LBR_ORG_ID_REQD'
2535 ,p_msg_attribute => 'CHANGE'
2536 ,p_resize_flag => 'N'
2537 ,p_msg_context => 'GENERAL'
2538 ,p_attribute1 => ''
2539 ,p_attribute2 => ''
2540 ,p_attribute3 => ''
2541 ,p_attribute4 => ''
2542 ,p_attribute5 => '');
2543 END IF;
2544 RAISE FND_API.G_EXC_ERROR;
2545 END IF;
2546
2547 OPEN l_non_labor_bill_rate_org_csr
2548 (l_task_rec.non_labor_bill_rate_org_id);
2549 FETCH l_non_labor_bill_rate_org_csr INTO l_dummy;
2550
2551 IF l_non_labor_bill_rate_org_csr%NOTFOUND
2552 THEN
2553 CLOSE l_non_labor_bill_rate_org_csr;
2554 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2555 THEN
2556 pa_interface_utils_pub.map_new_amg_msg
2557 ( p_old_message_code => 'PA_NON_LBR_ORG_ID_INVALID'
2558 ,p_msg_attribute => 'CHANGE'
2559 ,p_resize_flag => 'N'
2560 ,p_msg_context => 'GENERAL'
2561 ,p_attribute1 => ''
2562 ,p_attribute2 => ''
2563 ,p_attribute3 => ''
2564 ,p_attribute4 => ''
2565 ,p_attribute5 => '');
2566 END IF;
2567 RAISE FND_API.G_EXC_ERROR;
2568
2569 ELSE
2570 CLOSE l_non_labor_bill_rate_org_csr;
2571 END IF;
2572
2573 IF (l_task_rec.non_labor_std_bill_rate_schdl IS NOT NULL
2574 AND l_task_rec.non_labor_std_bill_rate_schdl <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
2575 THEN
2576
2577 OPEN l_non_labor_brs_csr (l_task_rec.non_labor_bill_rate_org_id,
2578 l_task_rec.non_labor_std_bill_rate_schdl );
2579 FETCH l_non_labor_brs_csr INTO l_dummy;
2580
2581 IF l_non_labor_brs_csr%NOTFOUND
2582 THEN
2583 CLOSE l_non_labor_brs_csr;
2584 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2585 THEN
2586 pa_interface_utils_pub.map_new_amg_msg
2587 ( p_old_message_code => 'PA_NON_LBR_BRS_INVALID'
2588 ,p_msg_attribute => 'CHANGE'
2589 ,p_resize_flag => 'N'
2590 ,p_msg_context => 'GENERAL'
2591 ,p_attribute1 => ''
2592 ,p_attribute2 => ''
2593 ,p_attribute3 => ''
2594 ,p_attribute4 => ''
2595 ,p_attribute5 => '');
2596 END IF;
2597 RAISE FND_API.G_EXC_ERROR;
2598
2599 ELSE
2600 CLOSE l_non_labor_brs_csr;
2601 END IF;
2602 END IF;
2603
2604 END IF;
2605 END IF;
2606
2607
2608 EXCEPTION
2609
2610 WHEN FND_API.G_EXC_ERROR THEN
2611 p_return_status := FND_API.G_RET_STS_ERROR;
2612
2613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2614 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2615
2616 WHEN OTHERS THEN
2617 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618
2619 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2620 THEN
2621 FND_MSG_PUB.add_exc_msg
2622 ( p_pkg_name => G_PKG_NAME
2623 , p_procedure_name => l_api_name );
2624
2625 END IF;
2626
2627 END Validate_billing_info_Pvt;
2628
2629 --====================================================================================
2630 --Name: check_start_end_date_Pvt
2631 --Type: Procedure
2632 --Description: This procedure can be used to pass old and new start_dates
2633 -- and old and new end_dates, from the PUBLIC API's. This procedure
2634 -- will check whether the new situation is going to be valid, and returns
2635 -- flags indicating whether start_date or end_date needs updating.
2636 --
2637 --
2638 --Called subprograms: none
2639 --
2640 --
2641 --
2642 --History:
2643 -- 03-DEC-1996 L. de Werker Created
2644 --
2645 PROCEDURE check_start_end_date_Pvt
2646 ( p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
2647 ,p_old_start_date IN DATE
2648 ,p_new_start_date IN DATE
2649 ,p_old_end_date IN DATE
2650 ,p_new_end_date IN DATE
2651 ,p_update_start_date_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
2652 ,p_update_end_date_flag OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
2653 )
2654 IS
2655
2656 l_api_name CONSTANT VARCHAR2(30) := 'check_start_end_date_Pvt';
2657
2658 l_start_date DATE;
2659 l_end_date DATE;
2660
2661 BEGIN
2662
2663 p_return_status := FND_API.G_RET_STS_SUCCESS;
2664
2665 IF p_new_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
2666 AND p_new_start_date IS NOT NULL --redundant, but added for clarity
2667 THEN
2668 IF p_new_start_date <> NVL(p_old_start_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2669 THEN
2670 p_update_start_date_flag := 'Y';
2671 l_start_date := p_new_start_date;
2672 ELSE
2673 p_update_start_date_flag := 'N';
2674 l_start_date := p_new_start_date;
2675 END IF;
2676
2677 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
2678 AND p_new_end_date IS NOT NULL --redundant, but added for clarity
2679 THEN
2680 IF p_new_end_date <> NVL(p_old_end_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2681 THEN
2682 p_update_end_date_flag := 'Y';
2683 l_end_date := p_new_end_date;
2684 ELSE
2685 p_update_end_date_flag := 'N';
2686 l_end_date := p_new_end_date;
2687 END IF;
2688
2689 IF l_start_date > l_end_date
2690 THEN
2691 IF FND_MSG_PUB.check_msg_level
2692 (FND_MSG_PUB.G_MSG_LVL_ERROR)
2693 THEN
2694 pa_interface_utils_pub.map_new_amg_msg
2695 ( p_old_message_code => 'PA_INVALID_START_DATE'
2696 ,p_msg_attribute => 'CHANGE'
2697 ,p_resize_flag => 'N'
2698 ,p_msg_context => 'GENERAL'
2699 ,p_attribute1 => ''
2700 ,p_attribute2 => ''
2701 ,p_attribute3 => ''
2702 ,p_attribute4 => ''
2703 ,p_attribute5 => '');
2704 END IF;
2705 RAISE FND_API.G_EXC_ERROR;
2706 END IF;
2707
2708 ELSIF p_new_end_date IS NULL
2709 THEN
2710 IF p_old_end_date IS NOT NULL
2711 THEN
2712 p_update_end_date_flag := 'Y';
2713 ELSE
2714 p_update_end_date_flag := 'N';
2715 END IF;
2716 ELSE
2717
2718 p_update_end_date_flag := 'N';
2719
2720 IF p_old_end_date IS NULL
2721 THEN
2722 NULL;
2723 ELSE
2724
2725 IF l_start_date > p_old_end_date THEN
2726 IF FND_MSG_PUB.check_msg_level
2727 (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2728 pa_interface_utils_pub.map_new_amg_msg
2729 ( p_old_message_code => 'PA_INVALID_START_DATE'
2730 ,p_msg_attribute => 'CHANGE'
2731 ,p_resize_flag => 'N'
2732 ,p_msg_context => 'GENERAL'
2733 ,p_attribute1 => ''
2734 ,p_attribute2 => ''
2735 ,p_attribute3 => ''
2736 ,p_attribute4 => ''
2737 ,p_attribute5 => '');
2738 END IF;
2739 RAISE FND_API.G_EXC_ERROR;
2740 END IF;
2741 END IF;
2742 END IF;
2743
2744 ELSIF p_new_start_date IS NULL
2745 THEN
2746 IF p_old_start_date IS NOT NULL
2747 THEN
2748 p_update_start_date_flag := 'Y';
2749 ELSE
2750 p_update_start_date_flag := 'N';
2751 END IF;
2752
2753 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
2754 AND p_new_end_date IS NOT NULL
2755 THEN
2756 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2757 THEN
2758 pa_interface_utils_pub.map_new_amg_msg
2759 ( p_old_message_code => 'PA_DATES_INVALID_3'
2760 ,p_msg_attribute => 'CHANGE'
2761 ,p_resize_flag => 'N'
2762 ,p_msg_context => 'GENERAL'
2763 ,p_attribute1 => ''
2764 ,p_attribute2 => ''
2765 ,p_attribute3 => ''
2766 ,p_attribute4 => ''
2767 ,p_attribute5 => '');
2768 END IF;
2769
2770 RAISE FND_API.G_EXC_ERROR;
2771
2772 ELSIF p_new_end_date IS NULL
2773 THEN
2774 IF p_old_end_date IS NOT NULL
2775 THEN
2776 p_update_end_date_flag := 'Y';
2777 ELSE
2778 p_update_end_date_flag := 'N';
2779 END IF;
2780 ELSE
2781
2782 p_update_end_date_flag := 'N';
2783
2784 IF p_old_end_date IS NOT NULL --start_date is null
2785 THEN
2786 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2787 THEN
2788 pa_interface_utils_pub.map_new_amg_msg
2789 ( p_old_message_code => 'PA_DATES_INVALID_3'
2790 ,p_msg_attribute => 'CHANGE'
2791 ,p_resize_flag => 'N'
2792 ,p_msg_context => 'GENERAL'
2793 ,p_attribute1 => ''
2794 ,p_attribute2 => ''
2795 ,p_attribute3 => ''
2796 ,p_attribute4 => ''
2797 ,p_attribute5 => '');
2798 END IF;
2799
2800 RAISE FND_API.G_EXC_ERROR;
2801 END IF;
2802 END IF;
2803
2804 ELSE --p_new_start_date was not passed
2805
2806 p_update_start_date_flag := 'N';
2807
2808 IF p_new_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
2809 AND p_new_end_date IS NOT NULL
2810 THEN
2811 IF p_new_end_date <> nvl(p_old_end_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2812 THEN
2813 p_update_end_date_flag := 'Y';
2814
2815 IF p_old_start_date IS NULL
2816 OR p_old_start_date > p_new_end_date
2817 THEN
2818 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2819 THEN
2820 pa_interface_utils_pub.map_new_amg_msg
2821 ( p_old_message_code => 'PA_INVALID_START_DATE'
2822 ,p_msg_attribute => 'CHANGE'
2823 ,p_resize_flag => 'N'
2824 ,p_msg_context => 'GENERAL'
2825 ,p_attribute1 => ''
2826 ,p_attribute2 => ''
2827 ,p_attribute3 => ''
2828 ,p_attribute4 => ''
2829 ,p_attribute5 => '');
2830 END IF;
2831
2832 RAISE FND_API.G_EXC_ERROR;
2833 END IF;
2834
2835 ELSE
2836 p_update_end_date_flag := 'N';
2837
2838 END IF;
2839
2840 ELSIF p_new_end_date IS NULL
2841 THEN
2842 IF p_old_end_date IS NOT NULL
2843 THEN
2844 p_update_end_date_flag := 'Y';
2845
2846 ELSE
2847 p_update_end_date_flag := 'N';
2848
2849 END IF;
2850 ELSE
2851 p_update_end_date_flag := 'N';
2852
2853 END IF;
2854 END IF;
2855
2856
2857 EXCEPTION
2858
2859 WHEN FND_API.G_EXC_ERROR
2860 THEN
2861
2862 p_return_status := FND_API.G_RET_STS_ERROR;
2863 -- 4537865
2864 p_update_end_date_flag := NULL ;
2865 p_update_start_date_flag := NULL ;
2866
2867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2868 THEN
2869
2870 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2871 -- 4537865
2872 p_update_end_date_flag := NULL ;
2873 p_update_start_date_flag := NULL ;
2874
2875 WHEN OTHERS THEN
2876
2877 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2878 -- 4537865
2879 p_update_end_date_flag := NULL ;
2880 p_update_start_date_flag := NULL ;
2881
2882 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2883 THEN
2884 FND_MSG_PUB.add_exc_msg
2885 ( p_pkg_name => G_PKG_NAME
2886 , p_procedure_name => l_api_name );
2887
2888 END IF;
2889
2890 END check_start_end_date_Pvt;
2891
2892 --------------------------------------------------------------------------------
2893 --Name: check_for_one_manager
2894 --Type: Procedure
2895 --Description: See below.
2896 --
2897 --Called subprograms:
2898 --
2899 --
2900 --
2901 --History:
2902 -- 31-JUL-1996 R. Krishnamurthy Created
2903 -- 03-DEC-1996 L. de Werker Moved from pa_project_pub to pa_project_pvt
2904 -- 12-JUL-2000 Mohnish
2905 -- added code for ROLE BASED SECURITY:
2906 -- added the call to PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY
2907 -- 19-JUL-2000 Mohnish incorporated PA_PROJECT_PARTIES_PUB API changes
2908 --
2909 PROCEDURE check_for_one_manager_Pvt
2910 (p_project_id IN NUMBER
2911 ,p_person_id IN NUMBER
2912 ,p_key_members IN pa_project_pub.project_role_tbl_type
2913 ,p_start_date IN DATE
2914 ,p_end_date IN DATE
2915 ,p_return_status OUT NOCOPY VARCHAR2 ) /*Added the nocopy check for 4537865 */
2916 IS
2917
2918 CURSOR l_current_project_man_csr
2919 IS
2920 SELECT person_id,
2921 start_date_active,
2922 end_date_active
2923 -- begin NEW code for ROLE BASED SECURITY
2924 , PROJECT_PARTY_ID
2925 , RESOURCE_ID
2926 , RESOURCE_TYPE_ID
2927 , RECORD_VERSION_NUMBER
2928 , scheduled_flag
2929 -- end NEW code for ROLE BASED SECURITY
2930 FROM pa_project_players
2931 WHERE project_id = p_project_id
2932 AND project_role_type = 'PROJECT MANAGER'
2933 -- AND nvl(end_date_active,nvl(p_start_date,sysdate)) between nvl(p_start_date,sysdate) and nvl(p_end_date,sysdate);
2934 AND nvl(end_date_active,nvl(p_start_date,sysdate)) between nvl(p_start_date,sysdate) and nvl(p_end_date,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE); -- Added for Bug 5183218
2935
2936 l_wf_type VARCHAR2(250);
2937 l_wf_item_type VARCHAR2(250);
2938 l_wf_process VARCHAR2(250);
2939 l_assignment_id NUMBER;
2940 l_current_project_man_rec l_current_project_man_csr%ROWTYPE;
2941 l_manager_dates_overlap VARCHAR2(1) := 'N';
2942 l_current_manager_updated VARCHAR2(1) := 'N';
2943 l_new_end_date DATE;
2944 -- begin NEW code for ROLE BASED SECURITY
2945 x_return_status VARCHAR2(255);
2946 x_msg_count NUMBER;
2947 x_msg_data VARCHAR2(2000);
2948 -- end NEW code for ROLE BASED SECURITY
2949
2950 --needed to get the field values associated to a AMG message
2951 -- added COMPLETION_DATE to the cursor l_amg_project_csr for ROLE BASED SECURITY
2952
2953 CURSOR l_amg_project_csr
2954 (p_pa_project_id pa_projects.project_id%type)
2955 IS
2956 SELECT segment1, COMPLETION_DATE
2957 FROM pa_projects p
2958 WHERE p.project_id = p_pa_project_id;
2959
2960 l_amg_segment1 VARCHAR2(25);
2961 v_completion_date DATE;
2962 l_project_role_id NUMBER;
2963 v_end_date DATE;
2964 v_null_number NUMBER:= to_number(NULL);
2965 v_null_char VARCHAR2(1):= to_char(NULL);
2966
2967 BEGIN
2968 /*
2969 If a project manager is sought to be created, then check whether
2970 there is already a project manager for the project. If so, check
2971 whether this is the same person. If not,then check the start and
2972 end dates for the existing manager. If dates overlap,then
2973 check the input table to see whether the existing project manager
2974 is being de-activated. If so,go ahead and create a new project manager and
2975 update the end date of the existing manager with the date provided
2976 Else update the end date of the existing manager to either
2977 (a) new manager's start date -1 or (b) sysdate -1
2978 (being done in check_for_one_manager);
2979 */
2980 p_return_status := FND_API.G_RET_STS_SUCCESS;
2981 --dbms_output.put_line('Inside check_for one_manager_pvt');
2982
2983 -- begin NEW code for ROLE BASED SECURITY
2984 -- getting the project_role_id for call to PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY
2985 Select project_role_id
2986 Into l_project_role_id
2987 From pa_project_role_types
2988 Where project_role_type='PROJECT MANAGER';
2989 -- end NEW code for ROLE BASED SECURITY
2990 -- Get segment1 for AMG messages
2991 --dbms_output.put_line('Value of l_project_role_id '||l_project_role_id);
2992
2993 OPEN l_amg_project_csr( p_project_id );
2994 FETCH l_amg_project_csr INTO l_amg_segment1,v_completion_date;
2995 CLOSE l_amg_project_csr;
2996
2997 OPEN l_current_project_man_csr;
2998 FETCH l_current_project_man_csr INTO l_current_project_man_rec;
2999
3000 IF l_current_project_man_csr%NOTFOUND
3001 THEN
3002 CLOSE l_current_project_man_csr;
3003 RETURN;
3004 END IF;
3005 CLOSE l_current_project_man_csr;
3006
3007 --dbms_output.put_line('value of l_current_project_man_rec.person_id'||l_current_project_man_rec.person_id);
3008 -- dbms_output.put_line('value of l_current_project_man_rec.start_date_active'||l_current_project_man_rec.start_date_active);
3009 -- dbms_output.put_line('value of l_current_project_man_rec.end_date_active'||l_current_project_man_rec.end_date_active);
3010 -- dbms_output.put_line('value of l_current_project_man_rec.PROJECT_PARTY_ID'||l_current_project_man_rec.PROJECT_PARTY_ID);
3011 -- dbms_output.put_line('value of l_current_project_man_rec.RESOURCE_ID'||l_current_project_man_rec.RESOURCE_ID);
3012 IF l_current_project_man_rec.person_id <> p_person_id
3013 THEN
3014
3015 IF l_current_project_man_rec.start_date_active > nvl(p_start_date,sysdate)
3016 THEN
3017 -- if the start date of the existing manager is > than the
3018 -- new manager's start date, then raise error
3019
3020 -- This check is added so that the user is allowed to enter back-dated project managers records.
3021 -- Bug 4300015
3022 If l_current_project_man_rec.start_date_active < nvl(p_end_date,sysdate)
3023 THEN
3024
3025 pa_interface_utils_pub.map_new_amg_msg
3026 ( p_old_message_code => 'PA_PR_TOO_MANY_MGRS'
3027 ,p_msg_attribute => 'CHANGE'
3028 ,p_resize_flag => 'N'
3029 ,p_msg_context => 'PROJ'
3030 ,p_attribute1 => l_amg_segment1
3031 ,p_attribute2 => ''
3032 ,p_attribute3 => ''
3033 ,p_attribute4 => ''
3034 ,p_attribute5 => '');
3035 RAISE FND_API.G_EXC_ERROR;
3036 End If;
3037 END IF;
3038 l_current_manager_updated := 'N';
3039
3040 FOR i in 1..p_key_members.COUNT LOOP
3041
3042 IF (p_key_members(i).person_id = l_current_project_man_rec.person_id
3043 AND p_key_members(i).project_role_type = 'PROJECT MANAGER')
3044 THEN
3045 /*
3046 -- begin OLD code before changes for ROLE BASED SECURITY
3047 IF p_key_members(i).end_date < p_start_date
3048 THEN
3049 UPDATE pa_project_players
3050 SET end_date_active = p_key_members(i).end_date
3051 WHERE project_id = p_project_id
3052 AND person_id = l_current_project_man_rec.person_id
3053 AND project_role_type = 'PROJECT MANAGER';
3054 ELSE
3055 UPDATE pa_project_players
3056 SET end_date_active =
3057 Decode( nvl(p_start_date,
3058 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
3059 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
3060 SYSDATE-1,p_start_date-1)
3061 WHERE project_id = p_project_id
3062 AND person_id =
3063 l_current_project_man_rec.person_id
3064 AND project_role_type = 'PROJECT MANAGER';
3065 END IF;
3066 -- end OLD code before changes for ROLE BASED SECURITY
3067 */
3068 -- begin NEW code for ROLE BASED SECURITY
3069 IF p_key_members(i).end_date < p_start_date
3070 THEN
3071 v_end_date := p_key_members(i).end_date;
3072 ELSE
3073 Select Decode( nvl(p_start_date,
3074 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
3075 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
3076 SYSDATE-1,p_start_date-1)
3077 Into v_end_date
3078 From dual;
3079 END IF;
3080 /*Added the OR condition in the below statement for the bug 2846478*/
3081 IF l_current_project_man_rec.scheduled_flag ='N' OR l_current_project_man_rec.scheduled_flag IS NULL
3082 THEN
3083 PA_PROJECT_PARTIES_PUB.update_project_party(
3084 p_api_version => 1.0 -- p_api_version
3085 , p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
3086 , p_commit => FND_API.G_FALSE -- p_commit /* bug#2417448 */
3087 , p_validate_only => FND_API.G_FALSE -- p_validate_only
3088 , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
3089 , p_debug_mode => 'N' -- p_debug_mode
3090 , p_object_id => p_project_id -- p_object_id
3091 , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
3092 , p_project_role_id => l_project_role_id -- p_project_role_id
3093 , p_project_role_type => 'PROJECT MANAGER' -- p_project_role_type
3094 , p_resource_type_id => l_current_project_man_rec.resource_type_id -- p_resource_type_id
3095 , p_resource_source_id => l_current_project_man_rec.person_id -- p_resource_source_id
3096 , p_resource_id => l_current_project_man_rec.resource_id -- Bug 6631033
3097 , p_resource_name => v_null_char -- p_resource_name
3098 , p_start_date_active => l_current_project_man_rec.start_date_active -- p_start_date_active
3099 , p_scheduled_flag => l_current_project_man_rec.scheduled_flag -- p_scheduled_flag
3100 , p_record_version_number => l_current_project_man_rec.record_version_number -- p_record_version_number
3101 , p_calling_module => 'FORM' -- p_calling_module
3102 , p_project_id => p_project_id -- p_project_id
3103 , p_project_end_date => v_completion_date -- p_project_end_date
3104 , p_project_party_id => l_current_project_man_rec.project_party_id -- p_project_party_id
3105 , p_end_date_active => v_end_date -- p_end_date_active
3106 , x_wf_type => l_wf_type
3107 , x_wf_item_type => l_wf_item_type
3108 , x_wf_process => l_wf_process
3109 , x_assignment_id => l_assignment_id
3110 , x_return_status =>x_return_status -- x_return_status
3111 , x_msg_count => x_msg_count -- x_msg_count
3112 , x_msg_data => x_msg_data -- x_msg_data
3113 );
3114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3115 p_return_status := x_return_status;
3116 -- p_msg_count := x_msg_count;
3117 -- p_msg_data := SUBSTR(p_msg_data||x_msg_data,1,2000);
3118 END IF;
3119 END IF;
3120 -- end NEW code for ROLE BASED SECURITY
3121 l_current_manager_updated := 'Y';
3122 EXIT; -- come out of the loop
3123 END IF;
3124 END LOOP;
3125 IF l_current_manager_updated = 'N' THEN
3126 IF ( p_start_date IS NULL OR
3127 p_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ) THEN
3128 l_new_end_date := SYSDATE - 1;
3129 ELSE
3130 l_new_end_date := p_start_date - 1;
3131 END IF;
3132 /*
3133 -- begin OLD code before changes for ROLE BASED SECURITY
3134 UPDATE pa_project_players
3135 SET end_date_active =
3136 Decode (SIGN(start_date_active-l_new_end_date),1,
3137 start_date_active, l_new_end_date)
3138 WHERE project_id = p_project_id
3139 AND person_id =
3140 l_current_project_man_rec.person_id
3141 AND project_role_type = 'PROJECT MANAGER';
3142 -- end OLD code before changes for ROLE BASED SECURITY
3143 */
3144 -- begin NEW code for ROLE BASED SECURITY
3145
3146 /* Added the code below for Bug 5196620 */
3147 IF (p_end_date IS NULL or p_end_date=v_completion_date) THEN /* bug#10222753 */ /*12927273*/
3148 IF SIGN(l_current_project_man_rec.start_date_active - l_new_end_date) = 1 then
3149 v_end_date := l_current_project_man_rec.start_date_active;
3150 ELSE
3151 v_end_date := l_new_end_date;
3152 End if;
3153 END IF; /* bug#10222753 */
3154
3155 /* Bug 5196620 : Commented the query to fetch end-date as its incorrect. This would fail if
3156 we have same person_id as PM for different dates. It would fecth multiple
3157 records in this scenario
3158 Select
3159 Decode (SIGN(start_date_active-l_new_end_date),1,
3160 start_date_active, l_new_end_date)
3161 Into v_end_date
3162 From pa_project_players
3163 Where project_id = p_project_id
3164 And person_id = l_current_project_man_rec.person_id
3165 And project_role_type = 'PROJECT MANAGER'; */
3166
3167 /* End of code changes for Bug 5196620 */
3168 /*Added the OR condition in the below statement for the bug 2846478*/
3169 /* IF l_current_project_man_rec.scheduled_flag ='N' OR l_current_project_man_rec.scheduled_flag IS NULL
3170 THEN */ -- Commented FOR Bug 6631033
3171 PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY(
3172 p_api_version => 1.0 -- p_api_version
3173 , p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
3174 , p_commit => FND_API.G_FALSE -- p_commit /* bug#2417448 */
3175 , p_validate_only => FND_API.G_FALSE -- p_validate_only
3176 , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
3177 , p_debug_mode => 'N' -- p_debug_mode
3178 , p_object_id => p_project_id -- p_object_id
3179 , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
3180 , p_project_role_id => l_project_role_id -- p_project_role_id
3181 , p_project_role_type => 'PROJECT MANAGER' -- p_project_role_type
3182 , p_resource_type_id => l_current_project_man_rec.resource_type_id -- p_resource_type_id
3183 , p_resource_source_id => l_current_project_man_rec.person_id -- p_resource_source_id
3184 , p_resource_id => l_current_project_man_rec.resource_id --Added resource_id parameter for Bug 6631033
3185 , p_resource_name => v_null_char -- p_resource_name
3186 , p_start_date_active => l_current_project_man_rec.start_date_active -- p_start_date_active
3187 , p_scheduled_flag => l_current_project_man_rec.scheduled_flag -- p_scheduled_flag
3188 , p_record_version_number => l_current_project_man_rec.record_version_number -- p_record_version_number
3189 , p_calling_module => 'FORM' -- p_calling_module
3190 , p_project_id => p_project_id -- p_project_id
3191 , p_project_end_date => v_completion_date -- p_project_end_date
3192 , p_project_party_id => l_current_project_man_rec.project_party_id -- p_project_party_id
3193 , p_end_date_active => v_end_date -- p_end_date_active
3194 , x_assignment_id => l_assignment_id
3195 , x_wf_type => l_wf_type
3196 , x_wf_item_type => l_wf_item_type
3197 , x_wf_process => l_wf_process
3198 , x_return_status => x_return_status -- x_return_status
3199 , x_msg_count => x_msg_count -- x_msg_count
3200 , x_msg_data => x_msg_data -- x_msg_data
3201 );
3202 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) Then
3203 p_return_status := x_return_status;
3204 -- p_msg_count := x_msg_count;
3205 -- p_msg_data := SUBSTR(p_msg_data||x_msg_data,1,2000);
3206 END IF;
3207 END IF;
3208 -- end NEW code for ROLE BASED SECURITY
3209 -- END IF; -- Commented FOR Bug 6631033
3210
3211 END IF;
3212 -- 4537865
3213 EXCEPTION
3214
3215 WHEN FND_API.G_EXC_ERROR
3216 THEN
3217
3218 p_return_status := FND_API.G_RET_STS_ERROR;
3219
3220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3221 THEN
3222
3223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3224
3225 WHEN OTHERS THEN
3226
3227 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3228
3229 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3230 THEN
3231 FND_MSG_PUB.add_exc_msg
3232 ( p_pkg_name => G_PKG_NAME
3233 , p_procedure_name => 'check_for_one_manager_Pvt');
3234
3235 END IF;
3236
3237 END check_for_one_manager_Pvt;
3238
3239 Procedure handle_task_number_change_Pvt
3240 (p_project_id IN NUMBER,
3241 p_task_id IN NUMBER,
3242 p_array_cell_number IN NUMBER,
3243 p_in_task_number IN VARCHAR2,
3244 p_in_task_tbl IN pa_project_pub.task_in_tbl_type,
3245 p_proceed_with_update_flag OUT NOCOPY VARCHAR2, /*Added the nocopy check for 4537865 */
3246 p_return_status OUT NOCOPY VARCHAR2 /*Added the nocopy check for 4537865 */
3247 ) IS
3248
3249 CURSOR l_get_task_number_csr (p_task_id IN NUMBER)
3250 IS
3251 SELECT task_number
3252 FROM pa_tasks
3253 WHERE task_id = p_task_id;
3254
3255 CURSOR l_get_task_id_csr (p_project_id IN NUMBER,
3256 p_task_number IN VARCHAR2 )
3257 IS
3258 SELECT task_id,pm_task_reference
3259 FROM pa_tasks
3260 WHERE project_id = p_project_id
3261 AND task_number = p_task_number ;
3262
3263 --needed to get the field values associated to a AMG message
3264
3265 CURSOR l_amg_project_csr
3266 (p_pa_project_id pa_projects.project_id%type)
3267 IS
3268 SELECT segment1
3269 FROM pa_projects p
3270 WHERE p.project_id = p_pa_project_id;
3271
3272 l_amg_segment1 VARCHAR2(25);
3273 l_amg_task_number VARCHAR2(50);
3274
3275 l_task_number VARCHAR2(30);
3276 l_task_id NUMBER := 0;
3277 l_pm_task_reference VARCHAR2(30);
3278 l_tot_task_count NUMBER := 0;
3279 l_count NUMBER := 0;
3280 p_multiple_task_msg VARCHAR2(1) := 'T';
3281
3282 BEGIN
3283
3284 p_return_status := FND_API.G_RET_STS_SUCCESS;
3285
3286 p_proceed_with_update_flag := 'Y';
3287
3288 -- check whether task number is changing
3289
3290 IF p_in_task_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR
3291 p_in_task_number IS NULL THEN
3292 p_proceed_with_update_flag := 'Y';
3293 RETURN;
3294 END IF;
3295
3296 -- Get segment1 for AMG messages
3297
3298 OPEN l_amg_project_csr( p_project_id );
3299 FETCH l_amg_project_csr INTO l_amg_segment1;
3300 CLOSE l_amg_project_csr;
3301
3302 OPEN l_get_task_number_csr (p_task_id);
3303 FETCH l_get_task_number_csr INTO l_task_number;
3304 CLOSE l_get_task_number_csr;
3305
3306 -- If task number is not being changed, then need not proceed further
3307 -- can proceed with the regular update
3308
3309 IF l_task_number = substrb(p_in_task_number,1,25) THEN --bug 6193314 added substrb
3310 p_proceed_with_update_flag := 'Y';
3311 RETURN;
3312 END IF;
3313
3314 -- If the new task number would result in unique constraint violation
3315 -- then scan the array to check whether the task which presently
3316 -- has the task number to which this task is changing to, is also being
3317 -- changed.
3318 -- Eg : If we are processing Task number 2.1 which is getting changed to
3319 -- 2.3, then check whether the existing task with task number 2.3, is
3320 -- also getting changed in the same session. If it is not getting changed
3321 -- then we cannot update this task to 2.3 and would raise an error
3322
3323 IF pa_task_utils.check_unique_task_number
3324 (p_project_id,p_in_task_number,NULL) = 0 THEN
3325 -- get the task id and task reference for the task whose
3326 -- present task number = the task number which is being changed to
3327
3328 OPEN l_get_task_id_csr (p_project_id,substrb(p_in_task_number,1,25)); --bug 6193314 added substrb
3329 FETCH l_get_task_id_csr
3330 INTO l_task_id,
3331 l_pm_task_reference;
3332 IF l_get_task_id_csr%NOTFOUND THEN
3333 CLOSE l_get_task_id_csr;
3334 RETURN;
3335 ELSE
3336 CLOSE l_get_task_id_csr;
3337 END IF;
3338
3339 -- scan the input array to check whether
3340 -- the fetched task is also getting changed
3341
3342 l_tot_task_count := p_in_task_tbl.COUNT;
3343 --FOR i IN 1..l_tot_task_count LOOP,
3344 --commented and added following by rtarway for bug fix 4016583
3345 FOR i IN p_in_task_tbl.FIRST..p_in_task_tbl.LAST LOOP
3346 IF p_in_task_tbl(i).pa_task_id = l_task_id OR
3347 p_in_task_tbl(i).pm_task_reference = l_pm_task_reference THEN
3348 IF p_in_task_tbl(i).pa_task_number = p_in_task_number THEN
3349 -- The task number for this task is not getting changed
3350 -- Hence , we cannot update the task that we are processing
3351 -- to the new task number.
3352 -- Eg: We are processing task 2.1
3353 -- It is getting changed to 2.3. There is already a task
3354 -- in the database with task number = 2.3. Unless, this
3355 -- is getting changed to something else, we cannot update
3356 -- 2.1 to 2.3
3357 l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
3358 (p_task_number=> p_in_task_tbl(i).task_name
3359 ,p_task_reference => p_in_task_tbl(i).pm_task_reference
3360 ,p_task_id => l_task_id);
3361
3362 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3363 THEN
3364 pa_interface_utils_pub.map_new_amg_msg
3365 ( p_old_message_code => 'PA_TASK_NUMBER_NOT_UNIQUE'
3366 ,p_msg_attribute => 'CHANGE'
3367 ,p_resize_flag => 'N'
3368 ,p_msg_context => 'TASK'
3369 ,p_attribute1 => l_amg_segment1
3370 ,p_attribute2 => l_amg_task_number
3371 ,p_attribute3 => ''
3372 ,p_attribute4 => ''
3373 ,p_attribute5 => '');
3374 END IF;
3375 p_multiple_task_msg := 'F';
3376 -- RAISE FND_API.G_EXC_ERROR;
3377 END IF;
3378 IF G_task_num_updated_index_tbl.EXISTS(1) THEN
3379 l_count := G_task_num_updated_index_tbl.COUNT;
3380 l_count := l_count + 1;
3381 ELSE
3382 l_count := 1;
3383 END IF;
3384 G_index_counter := G_index_counter + 1;
3385 G_task_num_updated_index_tbl(l_count).task_index
3386 := p_array_cell_number;
3387 G_task_num_updated_index_tbl(l_count).task_id
3388 := p_task_id;
3389 -- Now update the processing task number to a temporary value
3390 UPDATE pa_tasks
3391 SET task_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ||
3392 TO_CHAR(G_index_counter)
3393 WHERE task_id = p_task_id;
3394 p_proceed_with_update_flag := 'N';
3395 EXIT;
3396 END IF;
3397 END LOOP;
3398 IF p_multiple_task_msg = 'F'
3399 THEN
3400 RAISE FND_API.G_EXC_ERROR;
3401 END IF;
3402 END IF;
3403
3404 EXCEPTION
3405
3406 WHEN FND_API.G_EXC_ERROR
3407 THEN
3408
3409 p_return_status := FND_API.G_RET_STS_ERROR;
3410
3411 -- 4537865
3412 p_proceed_with_update_flag := 'N' ;
3413
3414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3415 THEN
3416
3417 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3418 -- 4537865
3419 p_proceed_with_update_flag := 'N' ;
3420
3421 WHEN OTHERS THEN
3422
3423 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3424 -- 4537865
3425 p_proceed_with_update_flag := 'N';
3426
3427 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3428 THEN
3429 FND_MSG_PUB.add_exc_msg
3430 ( p_pkg_name => G_PKG_NAME
3431 , p_procedure_name =>
3432 'handle_task_number_change' );
3433
3434 END IF;
3435
3436 END handle_task_number_change_Pvt;
3437
3438 Procedure check_parent_child_tk_dts_Pvt
3439 (p_project_id IN NUMBER,
3440 p_return_status OUT NOCOPY VARCHAR2 ) /*Added the nocopy check for 4537865 */
3441 IS
3442
3443 CURSOR l_get_tasks_csr IS
3444 SELECT task_id,parent_task_id,TRUNC(start_date) start_date,TRUNC(completion_date) completion_date,task_name, -- Bug Fix 4705139
3445 pm_task_reference
3446 FROM pa_tasks pt where project_id = p_project_id
3447 AND
3448 (
3449 parent_task_id IS NOT NULL
3450 OR EXISTS
3451 (SELECT 'x' FROM pa_tasks pt2
3452 WHERE parent_task_id = pt.task_id));
3453
3454 CURSOR l_get_child_dates_csr (l_project_id NUMBER,l_task_id NUMBER )
3455 IS
3456 SELECT min(TRUNC(start_date)),max(TRUNC(completion_date)) FROM -- Bug Fix 4705139
3457 PA_TASKS
3458 WHERE project_id = l_project_id
3459 AND parent_task_id = l_task_id;
3460
3461 CURSOR l_get_parent_dates_csr (l_project_id NUMBER,l_task_id NUMBER)
3462 IS
3463 SELECT TRUNC(start_date) start_date,TRUNC(completion_date) completion_date -- Bug Fix 4705139
3464 FROM
3465 PA_TASKS
3466 WHERE project_id = l_project_id
3467 AND task_id = l_task_id;
3468
3469 l_get_tasks_rec l_get_tasks_csr%rowtype;
3470 l_min_child_start_date DATE;
3471 l_max_child_completion_date DATE;
3472 l_parent_start_date DATE;
3473 l_parent_completion_date DATE;
3474
3475 l_amg_segment1 VARCHAR2(25);
3476 l_amg_task_number VARCHAR2(50);
3477
3478 --needed to get the field values associated to a AMG message
3479
3480 CURSOR l_amg_project_csr
3481 (p_pa_project_id pa_projects.project_id%type)
3482 IS
3483 SELECT segment1
3484 FROM pa_projects p
3485 WHERE p.project_id = p_pa_project_id;
3486
3487 CURSOR l_amg_task_csr
3488 (p_pa_task_id pa_tasks.task_id%type)
3489 IS
3490 SELECT task_number
3491 FROM pa_tasks p
3492 WHERE p.task_id = p_pa_task_id;
3493
3494 p_multiple_task_msg VARCHAR2(1) := 'T';
3495
3496 BEGIN
3497 p_return_status := FND_API.G_RET_STS_SUCCESS;
3498
3499 -- Get segment1 for AMG messages
3500
3501 OPEN l_amg_project_csr( p_project_id );
3502 FETCH l_amg_project_csr INTO l_amg_segment1;
3503 CLOSE l_amg_project_csr;
3504
3505 OPEN l_get_tasks_csr;
3506 LOOP
3507 FETCH l_get_tasks_csr INTO
3508 l_get_tasks_rec;
3509 EXIT WHEN l_get_tasks_csr%NOTFOUND;
3510 /*
3511 OPEN l_amg_task_csr( l_get_tasks_rec.task_id );
3512 FETCH l_amg_task_csr INTO l_amg_task_number;
3513 CLOSE l_amg_task_csr;
3514 */
3515 l_amg_task_number := pa_interface_utils_pub.get_task_number_amg
3516 (p_task_number=> l_get_tasks_rec.task_name
3517 ,p_task_reference => l_get_tasks_rec.pm_task_reference
3518 ,p_task_id => l_get_tasks_rec.task_id);
3519
3520 IF l_get_tasks_rec.parent_task_id IS NOT NULL THEN
3521 OPEN l_get_parent_dates_csr (p_project_id,
3522 l_get_tasks_rec.parent_task_id);
3523 FETCH l_get_parent_dates_csr INTO
3524 l_parent_start_date,
3525 l_parent_completion_date;
3526 IF l_get_parent_dates_csr%NOTFOUND THEN
3527 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3528 pa_interface_utils_pub.map_new_amg_msg
3529 ( p_old_message_code => 'PA_PARENT_TASK_MISSING'
3530 ,p_msg_attribute => 'CHANGE'
3531 ,p_resize_flag => 'N'
3532 ,p_msg_context => 'TASK'
3533 ,p_attribute1 => l_amg_segment1
3534 ,p_attribute2 => l_amg_task_number
3535 ,p_attribute3 => ''
3536 ,p_attribute4 => ''
3537 ,p_attribute5 => '');
3538 END IF;
3539 CLOSE l_get_parent_dates_csr;
3540 p_multiple_task_msg := 'F';
3541 -- RAISE FND_API.G_EXC_ERROR;
3542 ELSE
3543 CLOSE l_get_parent_dates_csr;
3544 END IF;
3545 IF l_parent_start_date > l_get_tasks_rec.start_date THEN
3546 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3547 pa_interface_utils_pub.map_new_amg_msg
3548 ( p_old_message_code => 'PA_PARENT_START_LATER'
3549 ,p_msg_attribute => 'CHANGE'
3550 ,p_resize_flag => 'N'
3551 ,p_msg_context => 'TASK'
3552 ,p_attribute1 => l_amg_segment1
3553 ,p_attribute2 => l_amg_task_number
3554 ,p_attribute3 => ''
3555 ,p_attribute4 => ''
3556 ,p_attribute5 => '');
3557 END IF;
3558 CLOSE l_get_tasks_csr;
3559 p_multiple_task_msg := 'F';
3560 -- RAISE FND_API.G_EXC_ERROR;
3561 END IF;
3562
3563 IF l_parent_completion_date < l_get_tasks_rec.completion_date THEN
3564 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3565 pa_interface_utils_pub.map_new_amg_msg
3566 ( p_old_message_code => 'PA_PARENT_COMPLETION_EARLIER'
3567 ,p_msg_attribute => 'CHANGE'
3568 ,p_resize_flag => 'Y'
3569 ,p_msg_context => 'TASK'
3570 ,p_attribute1 => l_amg_segment1
3571 ,p_attribute2 => l_amg_task_number
3572 ,p_attribute3 => ''
3573 ,p_attribute4 => ''
3574 ,p_attribute5 => '');
3575 END IF;
3576 CLOSE l_get_tasks_csr;
3577 p_multiple_task_msg := 'F';
3578 -- RAISE FND_API.G_EXC_ERROR;
3579 END IF;
3580 END IF;
3581
3582 l_min_child_start_date := NULL;
3583 l_max_child_completion_date := NULL;
3584
3585 OPEN l_get_child_dates_csr(p_project_id,l_get_tasks_rec.task_id);
3586 FETCH l_get_child_dates_csr INTO
3587 l_min_child_start_date,
3588 l_max_child_completion_date ;
3589 CLOSE l_get_child_dates_csr;
3590 IF l_min_child_start_date IS NOT NULL THEN
3591 IF l_get_tasks_rec.start_date > l_min_child_start_date
3592 OR
3593 l_min_child_start_date > l_get_tasks_rec.completion_date THEN
3594 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3595 pa_interface_utils_pub.map_new_amg_msg
3596 ( p_old_message_code => 'PA_CHILD_START_EARLIER'
3597 ,p_msg_attribute => 'CHANGE'
3598 ,p_resize_flag => 'N'
3599 ,p_msg_context => 'TASK'
3600 ,p_attribute1 => l_amg_segment1
3601 ,p_attribute2 => l_amg_task_number
3602 ,p_attribute3 => ''
3603 ,p_attribute4 => ''
3604 ,p_attribute5 => '');
3605 END IF;
3606 p_multiple_task_msg := 'F';
3607 -- RAISE FND_API.G_EXC_ERROR;
3608 END IF;
3609 END IF;
3610 IF l_max_child_completion_date IS NOT NULL THEN
3611 IF l_get_tasks_rec.completion_date < l_max_child_completion_date
3612 OR
3613 l_max_child_completion_date < l_get_tasks_rec.start_date
3614 THEN
3615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3616 pa_interface_utils_pub.map_new_amg_msg
3617 ( p_old_message_code => 'PA_PARENT_START_LATER'
3618 ,p_msg_attribute => 'CHANGE'
3619 ,p_resize_flag => 'N'
3620 ,p_msg_context => 'TASK'
3621 ,p_attribute1 => l_amg_segment1
3622 ,p_attribute2 => l_amg_task_number
3623 ,p_attribute3 => ''
3624 ,p_attribute4 => ''
3625 ,p_attribute5 => '');
3626 END IF;
3627 p_multiple_task_msg := 'F';
3628 -- RAISE FND_API.G_EXC_ERROR;
3629 END IF;
3630 END IF;
3631 END LOOP;
3632
3633 IF p_multiple_task_msg = 'F'
3634 THEN
3635 RAISE FND_API.G_EXC_ERROR;
3636 END IF;
3637
3638 CLOSE l_get_tasks_csr;
3639
3640 EXCEPTION
3641
3642 WHEN FND_API.G_EXC_ERROR
3643 THEN
3644 IF l_get_tasks_csr%ISOPEN THEN
3645 CLOSE l_get_tasks_csr;
3646 END IF;
3647
3648 p_return_status := FND_API.G_RET_STS_ERROR;
3649
3650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3651 THEN
3652 IF l_get_tasks_csr%ISOPEN THEN
3653 CLOSE l_get_tasks_csr;
3654 END IF;
3655
3656 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3657
3658 WHEN OTHERS THEN
3659 IF l_get_tasks_csr%ISOPEN THEN
3660 CLOSE l_get_tasks_csr;
3661 END IF;
3662
3663 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3664
3665 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3666 THEN
3667 FND_MSG_PUB.add_exc_msg
3668 ( p_pkg_name => G_PKG_NAME
3669 , p_procedure_name =>
3670 'check_parent_child_task_dates' );
3671
3672 END IF;
3673
3674 END check_parent_child_tk_dts_Pvt;
3675
3676 -- History
3677 --
3678 -- 16-Feb-2005 pkanupar created for bug #2111806
3679
3680 /* This is a wrapper API which is called from the AMG API pa_project_pub to
3681 check the Manager date range on a Project */
3682
3683 Procedure check_manager_date_range
3684 (p_project_id IN NUMBER,
3685 p_return_status OUT NOCOPY VARCHAR2 ) IS /*Added the nocopy check for 4537865 */
3686
3687 l_error_occured VARCHAR2(50);
3688 l_start_no_mgr_date DATE;
3689 l_end_no_mgr_date DATE;
3690
3691 BEGIN
3692 p_return_status := FND_API.G_RET_STS_SUCCESS;
3693 --dbms_output.put_line('is this called');
3694 PA_PROJECT_PARTIES_UTILS.validate_manager_date_range( p_mode => 'AMG'
3695 ,p_project_id => p_project_id
3696 ,x_start_no_mgr_date => l_start_no_mgr_date
3697 ,x_end_no_mgr_date => l_end_no_mgr_date
3698 ,x_error_occured => l_error_occured);
3699
3700 --dbms_output.put_line('Value of l_error_occured'||l_error_occured);
3701 IF l_error_occured = 'PA_PR_NO_MGR_DATE_RANGE' THEN
3702
3703 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3704
3705 pa_utils.add_message
3706 ( p_app_short_name => 'PA'
3707 ,p_msg_name => 'PA_PR_NO_MGR_DATE_RANGE'
3708 ,p_token1 => 'START_DATE'
3709 ,p_value1 => l_start_no_mgr_date
3710 ,p_token2 => 'END_DATE'
3711 ,p_value2 => l_end_no_mgr_date
3712 );
3713 END IF;
3714 RAISE FND_API.G_EXC_ERROR;
3715
3716 END IF;
3717
3718 EXCEPTION
3719
3720 WHEN FND_API.G_EXC_ERROR
3721 THEN
3722 p_return_status := FND_API.G_RET_STS_ERROR;
3723
3724 WHEN OTHERS THEN
3725 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3726
3727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3728 THEN
3729 FND_MSG_PUB.add_exc_msg
3730 ( p_pkg_name => G_PKG_NAME
3731 , p_procedure_name => 'check_manager_date_range' );
3732
3733 END IF;
3734
3735 END check_manager_date_range;
3736
3737 --------------------------------------------------------------------------------
3738 end PA_PROJECT_CHECK_PVT;