[Home] [Help]
PACKAGE BODY: APPS.PA_ALTERNATE_TASK_PVT
Source
1 PACKAGE BODY PA_ALTERNATE_TASK_PVT AS
2 --$Header: PAALTKVB.pls 120.10.12020000.7 2013/05/28 12:44:56 djambhek noship $
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.PA_ALTERNATE_TASK_PVT';
5 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7
8 procedure PRINT_MSG(P_MSG VARCHAR2
9 ,p_dbug_flag VARCHAR2 default 'N') is
10
11 BEGIN
12 IF (P_PA_DEBUG_MODE = 'Y' ) Then
13 pa_debug.g_err_stage := substr('LOG:'||p_msg,1,240);
14 PA_DEBUG.write
15 (x_Module => g_module_name
16 ,x_Msg => pa_debug.g_err_stage
17 ,x_Log_Level => 3);
18 END IF;
19 Return;
20 END PRINT_MSG;
21
22
23 FUNCTION PFCHAR(P_CHAR IN VARCHAR2 DEFAULT to_char(NULL) ) RETURN VARCHAR2 IS
24 begin
25
26 if p_char IS NOT NULL and p_char = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
27 return to_char(NULL);
28 elsif p_char IS NOT NULL and p_char = FND_API.G_MISS_CHAR THEN
29 return to_char(NULL);
30 elsif p_char IS NULL THEN
31 return fnd_api.g_miss_char;
32 else
33 return p_char;
34 end if;
35
36 EXCEPTION WHEN OTHERS THEN
37 RETURN P_CHAR;
38
39 END PFCHAR;
40
41 /*
42 * This API is used to create a new Alternate Task
43 * with the unique combination of Proj element and Cost code.
44 *
45 */
46 Procedure Create_Alt_Task(
47 p_Proj_Element_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
48 p_Cbs_Element_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
49 p_attribute_category_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
50 p_attribute1 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
51 p_attribute2 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
52 p_attribute3 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
53 p_attribute4 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
54 p_attribute5 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
55 p_attribute6 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
56 p_attribute7 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
57 p_attribute8 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
58 p_attribute9 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
59 p_attribute10 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
60 p_attribute11 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
61 p_attribute12 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
62 p_attribute13 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
63 p_attribute14 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
64 p_attribute15 IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_150_TBL_TYPE(),
65 X_Return_Status OUT NOCOPY Varchar2,
66 X_Msg_Data OUT NOCOPY Varchar2,
67 X_Msg_Count OUT NOCOPY Number)
68 IS
69
70 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
71 l_new_task_id PA_TASKS.TASK_ID%type;
72 l_count Number := -1;
73 l_attribute_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
74 l_attribute1 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
75 l_attribute2 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
76 l_attribute3 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
77 l_attribute4 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
78 l_attribute5 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
79 l_attribute6 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
80 l_attribute7 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
81 l_attribute8 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
82 l_attribute9 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
83 l_attribute10 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
84 l_attribute11 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
85 l_attribute12 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
86 l_attribute13 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
87 l_attribute14 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
88 l_attribute15 SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
89
90 BEGIN
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 x_msg_data := NULL;
94 x_msg_count := 0;
95
96 IF p_pa_debug_mode = 'Y' Then
97 pa_debug.init_err_stack('PA_ALTERNATE_TASK_PVT.Create_Alt_Task');
98 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
99 End If;
100
101 print_msg('PA_ALTERNATE_TASK_PVT.Create_Alt_Task IN param(Scalar) values');
102
103 PA_ALTERNATE_TASK_PVT.Validate_Alt_Tasks_Gen(
104 p_Proj_Element_Id => p_Proj_Element_Id,
105 p_Cbs_Element_Id => p_Cbs_Element_Id,
106 X_Valid_Status => l_valid_status_tbl
107 );
108
109 l_attribute_category_tbl := l_attribute_category_tbl;
110 l_attribute1 := p_attribute1;
111 l_attribute2 := p_attribute2;
112 l_attribute3 := p_attribute3;
113 l_attribute4 := p_attribute4;
114 l_attribute5 := p_attribute5;
115 l_attribute6 := p_attribute6;
116 l_attribute7 := p_attribute7;
117 l_attribute8 := p_attribute8;
118 l_attribute9 := p_attribute9;
119 l_attribute10 := p_attribute10;
120 l_attribute11 := p_attribute11;
121 l_attribute12 := p_attribute12;
122 l_attribute13 := p_attribute13;
123 l_attribute14 := p_attribute14;
124 l_attribute15 := p_attribute15;
125
126 if p_attribute_category_tbl.count=0 then
127 l_count := 0;
128 end if;
129
130 IF (p_Proj_Element_Id.COUNT > 0) THEN
131 FOR i IN p_Proj_Element_Id.FIRST .. p_Proj_Element_Id.LAST LOOP
132 print_msg('p_Proj_Element_Id :: ' || p_Proj_Element_Id(i));
133 print_msg('p_Cbs_Element_Id :: ' || p_Cbs_Element_Id(i));
134 print_msg('l_valid_status_tbl :: ' || l_valid_status_tbl(i));
135
136 IF l_count = 0 THEN
137 l_attribute_category_tbl.extend;
138 l_attribute_category_tbl(i) := null;
139 l_attribute1.extend;
140 l_attribute1(i) := null;
141 l_attribute2.extend;
142 l_attribute2(i) := null;
143 l_attribute3.extend;
144 l_attribute3(i) := null;
145 l_attribute4.extend;
146 l_attribute4(i) := null;
147 l_attribute5.extend;
148 l_attribute5(i) := null;
149 l_attribute6.extend;
150 l_attribute6(i) := null;
151 l_attribute7.extend;
152 l_attribute7(i) := null;
153 l_attribute8.extend;
154 l_attribute8(i) := null;
155 l_attribute9.extend;
156 l_attribute9(i) := null;
157 l_attribute10.extend;
158 l_attribute10(i) := null;
159 l_attribute11.extend;
160 l_attribute11(i) := null;
161 l_attribute12.extend;
162 l_attribute12(i) := null;
163 l_attribute13.extend;
164 l_attribute13(i) := null;
165 l_attribute14.extend;
166 l_attribute14(i) := null;
167 l_attribute15.extend;
168 l_attribute15(i) := null;
169 END IF;
170
171
172 IF l_valid_status_tbl(i) = 'V' THEN -- Insert records which are valid
173 print_msg('Inside IF loop :: ');
174
175 select PA_TASKS_S.NEXTVAL INTO l_new_task_id from sys.dual;
176
177
178 INSERT INTO PA_ALTERNATE_TASKS (
179 ALT_TASK_ID ,
180 PROJ_ELEMENT_ID ,
181 CBS_ELEMENT_ID ,
182 LAST_UPDATE_DATE ,
183 LAST_UPDATED_BY ,
184 CREATION_DATE ,
185 CREATED_BY ,
186 LAST_UPDATE_LOGIN ,
187 ATTRIBUTE_CATEGORY ,
188 ATTRIBUTE1 ,
189 ATTRIBUTE2 ,
190 ATTRIBUTE3 ,
191 ATTRIBUTE4 ,
192 ATTRIBUTE5 ,
193 ATTRIBUTE6 ,
194 ATTRIBUTE7 ,
195 ATTRIBUTE8 ,
196 ATTRIBUTE9 ,
197 ATTRIBUTE10 ,
198 ATTRIBUTE11 ,
199 ATTRIBUTE12 ,
200 ATTRIBUTE13 ,
201 ATTRIBUTE14 ,
202 ATTRIBUTE15
203 )
204 VALUES (
205 l_new_task_id ,
206 p_Proj_Element_Id(i) ,
207 p_Cbs_Element_Id(i) ,
208 SysDate ,
209 Fnd_Global.User_Id ,
210 SysDate ,
211 Fnd_Global.User_Id ,
212 Fnd_Global.Login_Id ,
213 l_attribute_category_tbl(i) ,
214 l_attribute1(i) ,
215 l_attribute2(i) ,
216 l_attribute3(i) ,
217 l_attribute4(i) ,
218 l_attribute5(i) ,
219 l_attribute6(i) ,
220 l_attribute7(i) ,
221 l_attribute8(i) ,
222 l_attribute9(i) ,
223 l_attribute10(i) ,
224 l_attribute11(i) ,
225 l_attribute12(i) ,
226 l_attribute13(i) ,
227 l_attribute14(i) ,
228 l_attribute15(i)
229 );
230 print_msg('After Insert :: ');
231 END IF;
232 print_msg('Before End of For Loop :: ');
233 END LOOP;
234 END IF;
235 print_msg('PA_ALTERNATE_TASK_PVT.Create_Alt_Task :: End');
236 EXCEPTION
237 When Others Then
238 X_Return_Status := 'U';
239 X_Msg_Data := SqlErrm;
240 X_Msg_Count := 1;
241 Raise;
242
243 END Create_Alt_Task;
244
245 /*
246 * This API is used to Delete existing Alternate Task.
247 * In this it needs to validate whether the alternate task can be deleted or not.
248 *
249 */
250 PROCEDURE Delete_Alt_Task(
251 p_Alt_Task_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
252 X_Return_Status OUT NOCOPY Varchar2,
253 X_Msg_Data OUT NOCOPY Varchar2,
254 X_Msg_Count OUT NOCOPY Number)
255 IS
256 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
257 BEGIN
258
259
260 x_return_status := FND_API.G_RET_STS_SUCCESS;
261 x_msg_data := NULL;
262 x_msg_count := 0;
263
264 IF p_pa_debug_mode = 'Y' Then
265 pa_debug.init_err_stack('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task');
266 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
267 End If;
268
269 print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task IN param(Scalar) values');
270
271 PA_ALTERNATE_TASK_PVT.Validate_Del_Alt_Tasks(
272 p_Alt_Task_Id => p_Alt_Task_Id,
273 X_Valid_Status => l_valid_status_tbl
274 );
275
279 print_msg('p_Alt_Task_Id :: ' || p_Alt_Task_Id(i));
276 IF (p_Alt_Task_Id.COUNT > 0) THEN
277
278 FOR i IN p_Alt_Task_Id.FIRST .. p_Alt_Task_Id.LAST LOOP
280 print_msg('l_valid_status_tbl :: ' || l_valid_status_tbl(i));
281
282 IF l_valid_status_tbl(i) = 'V' THEN -- Delete records which are valid
283
284 DELETE FROM PA_ALTERNATE_TASKS where ALT_TASK_ID = p_Alt_Task_Id(i);
285
286 END IF;
287
288 END LOOP;
289
290 END IF;
291
292 print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task :: End');
293 EXCEPTION
294 When Others Then
295 X_Return_Status := 'U';
296 X_Msg_Data := SqlErrm;
297 X_Msg_Count := 1;
298 Raise;
299 END Delete_Alt_Task;
300
301 /*
302 * This API is used to Validate whether already an Alternate Task is
303 * existing for the given proj element and the cost code.
304 *
305 */
306 Procedure Validate_Alt_Tasks_Gen(
307 p_Proj_Element_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
308 p_Cbs_Element_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
309 X_Valid_Status OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE)
310 IS
311 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
312 L_TEMP VARCHAR2(1) := 'N';
313
314
315 cursor isCostCodeAdded(c_Proj_Element_Id IN NUMBER, c_Cbs_Element_Id IN NUMBER) IS
316 select 'Y' from dual where
317 EXISTS
318 (SELECT *
319 FROM PA_ALTERNATE_TASKS pat , PA_RBS_ELEMENTS pre
320 WHERE (pat.CBS_ELEMENT_ID = c_Cbs_Element_Id
321 AND pat.PROJ_ELEMENT_ID = c_Proj_Element_Id)
322 OR (pat.CBS_ELEMENT_ID = pre.RBS_ELEMENT_ID --bug#16311830 checking if cost code is disabled
323 AND pat.CBS_ELEMENT_ID = c_Cbs_Element_Id
324 AND nvl(pre.ENABLE_FLAG,'Y') = 'N'));
325
326 BEGIN
327
328 print_msg('PA_ALTERNATE_TASK_PVT.Validate_Tasks :: Start');
329 IF (p_Proj_Element_Id.COUNT > 0) THEN
330 FOR i IN p_Proj_Element_Id.FIRST .. p_Proj_Element_Id.LAST LOOP
331 L_TEMP:='N';
332 open isCostCodeAdded(p_Proj_Element_Id(i), p_Cbs_Element_Id(i)) ;
333 fetch isCostCodeAdded into L_TEMP ;
334 close isCostCodeAdded ;
335 l_valid_status_tbl.extend;
336 IF L_TEMP='Y' THEN
337 l_valid_status_tbl(i) := 'I'; -- Invalid Status
338 ELSE
339 l_valid_status_tbl(i) := 'V'; -- Valid Status
340 END IF;
341 print_msg('l_valid_status_tbl(' || i || ') ::' || l_valid_status_tbl(i));
342 print_msg('p_Proj_Element_Id(' || i || ') ::' || p_Proj_Element_Id(i));
343 print_msg('p_Cbs_Element_Id(' || i || ') ::' || p_Cbs_Element_Id(i));
344 END LOOP;
345 END IF;
346 X_Valid_Status := l_valid_status_tbl;
347
348 print_msg('PA_ALTERNATE_TASK_PVT.Validate_Tasks :: End');
349
350 EXCEPTION
351
352 When Others Then
353 Raise;
354
355 END Validate_Alt_Tasks_Gen;
356
357 /*
358 * This API is used to Validate whether the Alternate Task is
359 * eligible for deletion or not.
360 *
361 */
362 Procedure Validate_Del_Alt_Tasks(
363 p_Alt_Task_Id IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
364 p_context IN VARCHAR2 := 'COST_CODE',
365 X_Valid_Status OUT NOCOPY SYSTEM.PA_VARCHAR2_1_TBL_TYPE
366 --x_error_code OUT NOCOPY NUMBER,
367 --x_error_msg_code OUT NOCOPY VARCHAR2
368 )
369 IS
370 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
371 L_TEMP VARCHAR2(1);
372 l_dummy number:=0;
373 l_project_id number;
374 Is_IEX_Installed BOOLEAN := False;
375 l_Attribute_Exists Boolean := False;
376 x_exist NUMBER := 0;
377
378 cursor isCostCodeUsed(c_Alt_Task_Id IN NUMBER) IS
379 SELECT 'Y' validation_success /* If cursor returns a record, deletion is not allowed */
380 FROM DUAL
381 WHERE EXISTS (
382 SELECT 1
383 FROM pa_resource_assignments r
384 WHERE r.task_id = (select proj_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id)
385 AND r.cbs_element_id = (select cbs_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id)
386 );
387
388 cursor getProjectId(c_Alt_Task_Id IN NUMBER) IS
389 select project_id from pa_tasks where task_id =
390 (select proj_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id);
391
392 Cursor getTaskandCbsId(c_Alt_Task_Id IN NUMBER) IS
393 Select proj_element_id,cbs_element_id
394 From pa_alternate_tasks where alt_task_id = c_Alt_Task_Id;
395
396 l_proj_element_id Number;
397 l_cbs_element_id Number;
398
399 BEGIN
400
401 print_msg('PA_ALTERNATE_TASK_PVT.Validate_Del_Alt_Tasks :: Start');
402 --x_error_code := 0;
403 --x_error_msg_code := NULL ;
404 IF (p_Alt_Task_Id.COUNT > 0) THEN
405 FOR i IN p_Alt_Task_Id.FIRST .. p_Alt_Task_Id.LAST LOOP
406 L_TEMP := 'N';
407
408 open getProjectId(p_Alt_Task_Id(i)) ;
409 fetch getProjectId into l_project_id ;
410 close getProjectId ;
411
412 Open getTaskandCbsId(p_Alt_Task_Id(i));
413 fetch getTaskandCbsId into l_proj_element_id,l_cbs_element_id;
414 close getTaskandCbsId;
415
416 l_valid_status_tbl.extend;
417 l_valid_status_tbl(i) := 'V';
418
419 IF p_context = 'COST_CODE' THEN/* start of if loop for COST_CODE context */
420 -- Check if task has purchase order distribution
421 BEGIN
422 l_dummy := 0;
423 SELECT
424 1 into l_dummy
425 FROM
426 sys.dual
427 WHERE
431 AND poa.TASK_ID = p_Alt_Task_Id(i));
428 exists (SELECT NULL
429 FROM po_distributions_all poa
430 where poa.project_id = l_project_id
432
433 IF l_dummy = 1 THEN
434 --x_error_code :=60;
435 --x_error_msg_code := 'PA_TSK_PO_DIST_EXIST';
436 l_valid_status_tbl(i) := 'I'; -- Invalid Status
437 continue;
438 ELSE
439 l_valid_status_tbl(i) := 'V'; -- Valid Status
440 END IF;
441
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 print_msg('API : TASK_VALIDATIONS : No purchase order distribution exist in the entire task hierarchy');
445
446 WHEN OTHERS THEN
447 --x_error_code := SQLCODE;
448 --x_error_msg_code := substrb(SQLERRM,1,120);
449 l_valid_status_tbl(i) := 'I'; -- Invalid Status
450 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order distribution');
451
452 --return;
453 continue;
454 END;
455
456 -- Check if task has purchase order requisition
457 BEGIN
458 l_dummy := 0;
459 SELECT
460 1 into l_dummy
461 FROM
462 sys.dual
463 WHERE
464 exists (SELECT NULL
465 FROM po_req_distributions_all prd
466 where prd.project_id = l_project_id
467 AND prd.TASK_ID = p_Alt_Task_Id(i));
468
469 IF l_dummy = 1 THEN
470 --x_error_code :=70;
471 --x_error_msg_code :='PA_TSK_PO_REQ_DIST_EXIST';
472 l_valid_status_tbl(i) := 'I'; -- Invalid Status
473 continue;
474 ELSE
475 l_valid_status_tbl(i) := 'V'; -- Valid Status
476 END IF;
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 print_msg('API : TASK_VALIDATIONS : No purchase order requisition exist in the entire task hierarchy');
481
482 WHEN OTHERS THEN
483 --x_error_code := SQLCODE;
484 --x_error_msg_code := substrb(SQLERRM,1,120);
485 l_valid_status_tbl(i) := 'I'; -- Invalid Status
486 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking purchase order requisition');
487
488
489 --return;
490 continue;
491 END;
492
493 -- Check if task has supplier invoices
494 BEGIN
495 l_dummy := 0;
496 SELECT
497 1 into l_dummy
498 FROM
499 sys.dual
500 WHERE
501 exists (SELECT NULL
502 FROM ap_invoices_all aia
503 where aia.project_id = l_project_id
504 AND aia.TASK_ID = p_Alt_Task_Id(i));
505
506 IF l_dummy = 1 THEN
507 --x_error_code :=80;
508 --x_error_msg_code :='PA_TSK_AP_INV_EXIST';
509 l_valid_status_tbl(i) := 'I'; -- Invalid Status
510 continue;
511 ELSE
512 l_valid_status_tbl(i) := 'V'; -- Valid Status
513 END IF;
514
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 print_msg('API : TASK_VALIDATIONS : No supplier invoices exist in the entire task hierarchy' );
518
519 WHEN OTHERS THEN
520 --x_error_code := SQLCODE;
521 --x_error_msg_code := substrb(SQLERRM,1,120);
522 l_valid_status_tbl(i) := 'I'; -- Invalid Status
523 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoices');
524
525 -- return;
526 continue;
527 END;
528
529 -- check if task has supplier invoice distribution
530 BEGIN
531 l_dummy := 0;
532 SELECT
533 1 into l_dummy
534 FROM
535 sys.dual
536 WHERE
537 exists (SELECT NULL
538 FROM ap_invoice_distributions_all aid
539 where aid.project_id = l_project_id
540 AND aid.TASK_ID = p_Alt_Task_Id(i));
541
542 IF l_dummy = 1 THEN
543 --x_error_code :=90;
544 --x_error_msg_code :='PA_TSK_AP_INV_DIST_EXIST';
545 l_valid_status_tbl(i) := 'I'; -- Invalid Status
546 continue;
547 ELSE
548 l_valid_status_tbl(i) := 'V'; -- Valid Status
549 END IF;
550
551 EXCEPTION
552 WHEN NO_DATA_FOUND THEN
553 print_msg('API : TASK_VALIDATIONS : No supplier invoice distribution exist');
554
555 WHEN OTHERS THEN
556 --x_error_code := SQLCODE;
557 --x_error_msg_code := substrb(SQLERRM,1,120);
558 l_valid_status_tbl(i) := 'I'; -- Invalid Status
559 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking supplier invoice distribution' );
560
561 -- return;
562 continue;
563 END;
564
565 /* added these checks as a part of bug fix 6079887 */
566 -- Check if task has iExpense records
567 BEGIN
568 l_dummy := 0;
569 SELECT
570 1 into l_dummy
571 FROM
572 sys.dual
573 WHERE
574 exists (SELECT NULL
575 FROM ap_exp_report_dists_all er
576 where er.project_id = l_project_id
577 AND er.TASK_ID = p_Alt_Task_Id(i));
578
579 IF l_dummy = 1 THEN
580 --x_error_code :=180;
581 --x_error_msg_code := 'PA_TSK_IEXP_EXIST';
582 l_valid_status_tbl(i) := 'I'; -- Invalid Status
583 continue;
584 ELSE
585 l_valid_status_tbl(i) := 'V'; -- Valid Status
586 END IF;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590 print_msg('API : TASK_VALIDATIONS : No IExpenses exist in the entire task hierarchy');
591
592 WHEN OTHERS THEN
593 --x_error_code := SQLCODE;
594 --x_error_msg_code := substrb(SQLERRM,1,120);
595 l_valid_status_tbl(i) := 'I'; -- Invalid Status
596 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking IExpense Records');
597
598 -- return;
599 continue;
600 END;
601
602 -- Check if task has Inventory Transaction records
603 BEGIN
604 l_dummy := 0;
605 SELECT
606 1 into l_dummy
607 FROM
608 sys.dual
609 WHERE
610 exists (SELECT NULL
611 FROM mtl_material_transactions mtl
612 where mtl.project_id = l_project_id
613 AND mtl.TASK_ID = p_Alt_Task_Id(i));
614
615 IF l_dummy = 1 THEN
616 --x_error_code :=190;
617 --x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
618 l_valid_status_tbl(i) := 'I'; -- Invalid Status
619 continue;
620 ELSE
621 l_valid_status_tbl(i) := 'V'; -- Valid Status
622 END IF;
623
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 print_msg('API : TASK_VALIDATIONS : No Inventory transactions exist in the entire task hierarchy');
627
628 WHEN OTHERS THEN
629 --x_error_code := SQLCODE;
630 --x_error_msg_code := substrb(SQLERRM,1,120);
631 l_valid_status_tbl(i) := 'I'; -- Invalid Status
632 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking Inventory Records');
633
634 --return;
635 continue;
636 END;
637
638 --Check if task has expenditure item
639 BEGIN
640 l_dummy := 0;
641 SELECT
642 1 into l_dummy
643 FROM
644 sys.dual
645 WHERE
646 exists (SELECT NULL
647 FROM PA_EXPENDITURE_ITEMS_all pei
648 where pei.CBS_ELEMENT_ID = l_cbs_element_id
649 and pei.task_id=l_proj_element_id);
650
651 IF l_dummy = 1 THEN
652 --x_error_code :=190;
653 --x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
654 l_valid_status_tbl(i) := 'I'; -- Invalid Status
655 continue;
656 ELSE
657 l_valid_status_tbl(i) := 'V'; -- Valid Status
658 END IF;
659
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 print_msg('API : TASK_VALIDATIONS : No Expenditure Items exist in the entire task hierarchy');
663
664 WHEN OTHERS THEN
665 --x_error_code := SQLCODE;
666 --x_error_msg_code := substrb(SQLERRM,1,120);
667 l_valid_status_tbl(i) := 'I'; -- Invalid Status
668 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking Expenditure Items');
669
670 --return;
671 continue;
672 END;
673
674 /* Currently cbs_element_id column should be added into pa_commitment_txns table
675 --Check if task has commitment transaction
676 BEGIN
677 l_dummy := 0;
678 SELECT
679 1 into l_dummy
680 FROM
681 sys.dual
682 WHERE
683 exists (SELECT NULL
684 FROM pa_commitment_txns pct
685 where pct.CBS_ELEMENT_ID = p_Alt_Task_Id(i));
686
687 IF l_dummy = 1 THEN
688 --x_error_code :=190;
689 --x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
690 l_valid_status_tbl(i) := 'I'; -- Invalid Status
691 return;
692 ELSE
693 l_valid_status_tbl(i) := 'V'; -- Valid Status
694 END IF;
695
696 EXCEPTION
697 WHEN NO_DATA_FOUND THEN
701 --x_error_code := SQLCODE;
698 print_msg('API : TASK_VALIDATIONS : No commitment transaction exist');
699
700 WHEN OTHERS THEN
702 --x_error_msg_code := substrb(SQLERRM,1,120);
703 l_valid_status_tbl(i) := 'I'; -- Invalid Status
704 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking commitment transaction');
705
706 --return;
707 END;
708 */
709 -- Check if task has draft invoices
710 BEGIN
711 l_dummy := 0;
712 SELECT
713 1 into l_dummy
714 FROM
715 sys.dual
716 WHERE
717 exists (SELECT NULL
718 FROM pa_draft_invoice_details_all pdi
719 where pdi.CC_TAX_TASK_ID = p_Alt_Task_Id(i));
720
721 IF l_dummy = 1 THEN
722 --x_error_code :=190;
723 --x_error_msg_code := 'PA_TSK_INV_TRANS_EXIST';
724 l_valid_status_tbl(i) := 'I'; -- Invalid Status
725 continue;
726 ELSE
727 l_valid_status_tbl(i) := 'V'; -- Valid Status
728 END IF;
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 print_msg('API : TASK_VALIDATIONS : No draft invoice exist');
733
734 WHEN OTHERS THEN
735 --x_error_code := SQLCODE;
736 --x_error_msg_code := substrb(SQLERRM,1,120);
737 l_valid_status_tbl(i) := 'I'; -- Invalid Status
738 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking draft invoices');
739
740 --return;
741 continue;
742 END;
743 END IF; /* end of if loop for COST_CODE context */
744
745 l_Attribute_Exists := Hxc_Integration_Layer_V1_Grp.Chk_Mapping_Exists (
746 P_Bld_Blk_Info_Type => 'PROJECTS',
747 P_Field_Name => 'Task_Id',
748 P_Field_Value => p_Alt_Task_Id(i),
749 P_status => 'WORKING',
750 P_Scope => 'DETAIL');
751 IF l_Attribute_Exists THEN
752 l_valid_status_tbl(i) := 'I'; -- Invalid Status
753 print_msg(' Check contract association for task ');
754 continue;
755 END IF;
756
757 Is_IEX_Installed := pa_install.is_product_installed('IEX');
758 print_msg(' check if task is charged in iexpense ');
759 BEGIN
760 x_exist := 0;
761 SELECT 1
762 INTO x_exist
763 FROM AP_EXPENSE_REPORT_LINES_ALL A
764 WHERE A.TASK_ID IS NOT NULL
765 AND A.TASK_ID = p_Alt_Task_Id(i)
766 AND EXISTS ( SELECT 1 FROM AP_EXPENSE_REPORT_HEADERS_ALL B
767 WHERE A.REPORT_HEADER_ID = B.REPORT_HEADER_ID
768 AND B.SOURCE <> 'Oracle Project Accounting'
769 AND B.VOUCHNO = 0)
770 AND rownum = 1;
771
772 IF x_exist = 1 THEN
773 l_valid_status_tbl(i) := 'I'; -- Invalid Status
774 continue;
775 ELSE
776 l_valid_status_tbl(i) := 'V'; -- Valid Status
777 END IF;
778
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 print_msg('API : TASK_VALIDATIONS : No draft invoice exist');
782
783 WHEN OTHERS THEN
784 --x_error_code := SQLCODE;
785 --x_error_msg_code := substrb(SQLERRM,1,120);
786 l_valid_status_tbl(i) := 'I'; -- Invalid Status
787 print_msg(' TASK_VALIDATIONS :Unexpected Error occured while checking draft invoices');
788
789 --return;
790 continue;
791 END;
792
793
794 open isCostCodeUsed(p_Alt_Task_Id(i)) ;
795 fetch isCostCodeUsed into L_TEMP ;
796 close isCostCodeUsed ;
797 IF L_TEMP='Y' THEN
798 l_valid_status_tbl(i) := 'I'; -- Invalid Status
799 continue;
800 ELSE
801 l_valid_status_tbl(i) := 'V'; -- Valid Status
802 END IF;
803
804 END LOOP;
805 END IF;
806
807 X_Valid_Status := l_valid_status_tbl;
808 print_msg('PA_ALTERNATE_TASK_PVT.Validate_Del_Alt_Tasks :: End');
809
810 EXCEPTION
811
812 When Others Then
813 Raise;
814
815 END Validate_Del_Alt_Tasks;
816
817 /*
818 * This API is used to copy cost codes from a source task to the
819 * targeted task.
820 *
821 */
822 Procedure Copy_Cost_Codes_From_Task(
823 p_Source_Task_Id IN Number,
824 p_Target_Task_Id IN Number,
825 X_Return_Status OUT NOCOPY Varchar2,
826 X_Msg_Data OUT NOCOPY Varchar2,
827 X_Msg_Count OUT NOCOPY Number)
828 IS
829
830 l_Proj_Element_Id SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();
831 l_Cbs_Element_Id SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();
832 L_TEMP VARCHAR2(1);
833
834 cursor getCostCodes(c_Task_Id IN NUMBER) IS
835 select CBS_ELEMENT_ID from PA_ALTERNATE_TASKS
836 where PROJ_ELEMENT_ID = p_Source_Task_Id;
837
838
839 BEGIN
840
841 print_msg('PA_ALTERNATE_TASK_PVT.Copy_Cost_Codes_From_Task :: Start');
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843 x_msg_data := NULL;
844 x_msg_count := 0;
845
846 select CBS_ELEMENT_ID
847 BULK COLLECT INTO l_Cbs_Element_Id
848 from PA_ALTERNATE_TASKS
849 where PROJ_ELEMENT_ID = p_Source_Task_Id;
850
851 IF (l_Cbs_Element_Id.COUNT > 0) THEN
852
853 FOR i IN l_Cbs_Element_Id.FIRST .. l_Cbs_Element_Id.LAST LOOP
854 l_Proj_Element_Id.extend;
855 l_Proj_Element_Id(i) := p_Target_Task_Id;
856
857 END LOOP;
858
859 PA_ALTERNATE_TASK_PVT.Create_Alt_Task(
860 p_Proj_Element_Id => l_Proj_Element_Id,
861 p_Cbs_Element_Id => l_Cbs_Element_Id,
862 X_Return_Status => X_Return_Status,
863 X_Msg_Count => X_Msg_Count,
864 X_Msg_Data => X_Msg_Data);
865
866 END IF;
867
868
869 print_msg('PA_ALTERNATE_TASK_PVT.Copy_Cost_Codes_From_Task :: End');
870
871 EXCEPTION
872 When Others Then
873 X_Return_Status := 'U';
874 X_Msg_Data := SqlErrm;
875 X_Msg_Count := 1;
876 Raise;
877
878 END Copy_Cost_Codes_From_Task;
879
880 /*
881 * This Function is used to check whether the project is CBS enabled or not
882 *
883 */
884 FUNCTION Is_Cbs_Enabled(p_Project_Id IN Number)
885 RETURN VARCHAR2
886 IS
887 L_Cbs_Enabled VARCHAR2(1) := 'N';
888 L_TEMP VARCHAR2(1) := 'N';
889
890 CURSOR check_cost_code_enabled(c_project_id NUMBER) IS
891 select 'Y' from dual where
892 EXISTS
893 (SELECT *
894 FROM pa_projects_all
895 WHERE project_id = c_project_id
896 AND CBS_VERSION_ID IS NOT NULL
897 AND CBS_ENABLE_FLAG = 'Y');
898
899 BEGIN
900
901 OPEN check_cost_code_enabled(p_Project_Id);
902 FETCH check_cost_code_enabled INTO L_TEMP;
903 CLOSE check_cost_code_enabled;
904 IF L_TEMP='Y' THEN
905 L_Cbs_Enabled := 'Y'; -- CBS enabled
906 ELSE
907 L_Cbs_Enabled := 'N'; -- CBS disabled
908 END IF;
909 return L_Cbs_Enabled;
910 END Is_Cbs_Enabled;
911
912 /*
913 * This API is used to validate cbs_element_id for a given task to the
914 * specified project is valid or not.
915 *
916 */
917 PROCEDURE Is_Cost_Element_Valid(
918 p_Cbs_Element_Id IN Number,
919 p_Task_Id IN Number,
920 p_Project_Id IN Number,
921 X_Return_Status OUT NOCOPY Varchar2,
922 X_Msg_Data OUT NOCOPY Varchar2,
923 X_Msg_Count OUT NOCOPY Number)
924 IS
925 cursor validateCostCodes(c_Cbs_Element_Id IN NUMBER, c_Task_Id IN NUMBER) IS
926 select 'Y' from dual where
927 EXISTS
928 (SELECT *
929 FROM PA_ALTERNATE_TASKS
930 WHERE proj_element_id = c_Task_Id
931 AND cbs_element_id = c_Cbs_Element_Id
932 );
933
934 cursor validateCBSStructure(c_Project_Id IN NUMBER, c_Cbs_Element_Id IN NUMBER) IS
935 select 'Y' from dual where
936 EXISTS
937 (SELECT *
938 FROM pa_projects_all
939 WHERE project_id = c_Project_Id
940 AND CBS_VERSION_ID =
941 (select rbs_version_id from pa_rbs_elements
942 where rbs_element_id = c_Cbs_Element_Id)
943 );
944
945 l_Cbs_Enabled VARCHAR2(1) := 'N';
946 l_Cbs_Struct_Valid VARCHAR2(1) := 'N';
947 l_Cost_Code_Valid VARCHAR2(1) := 'N';
948 L_TEMP VARCHAR2(1) := 'N';
949
950 BEGIN
951 print_msg('PA_ALTERNATE_TASK_PVT.Is_Cost_Element_Valid :: Start');
952 x_return_status := FND_API.G_RET_STS_SUCCESS;
953 x_msg_data := NULL;
954 x_msg_count := 0;
955 print_msg('Input paramters to PA_ALTERNATE_TASK_PVT.Is_Cost_Element_Valid');
956 print_msg('p_Cbs_Element_Id :: ' || p_Cbs_Element_Id);
957 print_msg('p_Task_Id :: ' || p_Task_Id);
958 print_msg('p_Project_Id :: ' || p_Project_Id);
959
960 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => P_PROJECT_ID);
961
962 IF l_Cbs_Enabled = 'N' THEN
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 x_msg_data := 'PA_CBS_PROJ_NOT_ENABLED';
965 x_msg_count := 1;
966 ELSE
967 OPEN validateCBSStructure(p_Project_Id, p_Cbs_Element_Id);
968 FETCH validateCBSStructure INTO L_TEMP;
969 CLOSE validateCBSStructure;
970 IF L_TEMP='Y' THEN
971 l_Cbs_Struct_Valid := 'Y';
972 ELSE
973 l_Cbs_Struct_Valid := 'N';
974 END IF;
975 IF l_Cbs_Struct_Valid = 'N' THEN
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 x_msg_data := 'PA_CBS_INVALID_CBS_ELEM_ID';
978 x_msg_count := 1;
979 ELSE
980 L_TEMP := 'N';
981 OPEN validateCostCodes(p_Cbs_Element_Id , p_Task_Id);
982 FETCH validateCostCodes INTO L_TEMP;
983 CLOSE validateCostCodes;
984 IF L_TEMP='Y' THEN
985 l_Cost_Code_Valid := 'Y';
986 ELSE
987 l_Cost_Code_Valid := 'N';
988 END IF;
989 IF l_Cost_Code_Valid = 'N' THEN
990 x_return_status := FND_API.G_RET_STS_ERROR;
991 x_msg_data := 'PA_CBS_ELEM_MISMATCH';
992 x_msg_count := 1;
993 END IF;
994 END IF;
995 END IF;
996
997 print_msg('PA_ALTERNATE_TASK_PVT.Is_Cost_Element_Valid :: End');
998
999 EXCEPTION
1000 When Others Then
1001 X_Return_Status := 'U';
1002 X_Msg_Data := SqlErrm;
1003 X_Msg_Count := 1;
1004 Raise;
1005
1006 END Is_Cost_Element_Valid;
1007
1008
1009 /*
1010 * This API is used to validate cost codes for a given task to the
1011 * specified project is valid or not.
1012 *
1013 */
1014 PROCEDURE Is_Cost_Code_Valid(
1015 p_Cost_Code IN Varchar2,
1016 p_Task_Id IN Number,
1017 p_Project_Id IN Number,
1018 X_Return_Status OUT NOCOPY Varchar2,
1019 X_Msg_Data OUT NOCOPY Varchar2,
1020 X_Msg_Count OUT NOCOPY Number)
1021 IS
1022 cursor validateCostCodes(c_Cost_Code IN Varchar2, c_Task_Id IN NUMBER, c_Project_Id IN NUMBER) IS
1023 select 'Y' from dual where
1024 EXISTS
1025 (SELECT *
1026 FROM PA_ALTERNATE_TASKS
1027 WHERE proj_element_id = c_Task_Id
1028 AND cbs_element_id = (select rbs_element_id from pa_rbs_elements
1029 where cost_code = c_Cost_Code
1030 and rbs_version_id =
1031 (select cbs_version_id from pa_projects_all where project_id = c_Project_Id))
1032 );
1033
1034 cursor validateCBSStructure(c_Project_Id IN NUMBER, c_Cost_Code IN Varchar2) IS
1035 select 'Y' from dual where
1036 EXISTS
1037 (select * from pa_rbs_elements
1038 where cost_code = c_Cost_Code
1039 and rbs_version_id =
1040 (select cbs_version_id from pa_projects_all where project_id = c_Project_Id)
1041 );
1042
1043 l_Cbs_Enabled VARCHAR2(1) := 'N';
1044 l_Cbs_Struct_Valid VARCHAR2(1) := 'N';
1045 l_Cost_Code_Valid VARCHAR2(1) := 'N';
1046 L_TEMP VARCHAR2(1) := 'N';
1047
1048 BEGIN
1049 print_msg('PA_ALTERNATE_TASK_PVT.Is_Cost_Code_Valid :: Start');
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051 x_msg_data := NULL;
1052 x_msg_count := 0;
1053 print_msg('Input paramters to PA_ALTERNATE_TASK_PVT.Is_Cost_Code_Valid');
1054 print_msg('p_Cost_Code :: ' || p_Cost_Code);
1055 print_msg('p_Task_Id :: ' || p_Task_Id);
1056 print_msg('p_Project_Id :: ' || p_Project_Id);
1057
1058 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => P_PROJECT_ID);
1059
1060 IF l_Cbs_Enabled = 'N' THEN
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 x_msg_data := 'PA_CBS_PROJ_NOT_ENABLED';
1063 x_msg_count := 1;
1064 ELSE
1065 OPEN validateCBSStructure(p_Project_Id, p_Cost_Code);
1066 FETCH validateCBSStructure INTO L_TEMP;
1067 CLOSE validateCBSStructure;
1068 IF L_TEMP='Y' THEN
1069 l_Cbs_Struct_Valid := 'Y';
1070 ELSE
1071 l_Cbs_Struct_Valid := 'N';
1072 END IF;
1073 IF l_Cbs_Struct_Valid = 'N' THEN
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 x_msg_data := 'PA_CBS_INVALID_CBS_ELEM_ID';
1076 x_msg_count := 1;
1077 ELSE
1078 L_TEMP := 'N';
1079 OPEN validateCostCodes(p_Cost_Code , p_Task_Id, p_Project_Id);
1080 FETCH validateCostCodes INTO L_TEMP;
1081 CLOSE validateCostCodes;
1082 IF L_TEMP='Y' THEN
1083 l_Cost_Code_Valid := 'Y';
1084 ELSE
1085 l_Cost_Code_Valid := 'N';
1086 END IF;
1087 IF l_Cost_Code_Valid = 'N' THEN
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 x_msg_data := 'PA_CBS_ELEM_MISMATCH';
1090 x_msg_count := 1;
1091 END IF;
1092 END IF;
1093 END IF;
1094
1095 print_msg('PA_ALTERNATE_TASK_PVT.Is_Cost_Code_Valid :: End');
1096
1097 EXCEPTION
1098 When Others Then
1099 X_Return_Status := 'U';
1100 X_Msg_Data := SqlErrm;
1101 X_Msg_Count := 1;
1102 Raise;
1103
1104 END Is_Cost_Code_Valid;
1105
1106 /*
1107 * This API is used to validate planning options for a budget version
1108 * of cbs enabled project can be used or not.
1109 *
1110 */
1111 PROCEDURE Is_Planning_Options_Valid(
1112 p_budget_version_id IN Number,
1113 X_Return_Status OUT NOCOPY Varchar2,
1114 X_Msg_Data OUT NOCOPY Varchar2,
1115 X_Msg_Count OUT NOCOPY Number)
1116 IS
1117
1118 cursor getProjectId IS
1119 select project_id from pa_budget_versions where
1120 budget_version_id = p_budget_version_id;
1121
1122 cursor validateLowestTask IS
1123 select 'Y' from dual where
1124 EXISTS
1125 (select * from pa_proj_fp_options
1126 where (REVENUE_FIN_PLAN_LEVEL_CODE = 'P' OR
1127 COST_FIN_PLAN_LEVEL_CODE = 'P' OR
1128 ALL_FIN_PLAN_LEVEL_CODE = 'P') and FIN_PLAN_VERSION_ID = p_budget_version_id
1129 );
1130
1131 cursor validateStaffPlan IS
1132 select 'Y' from dual where
1133 EXISTS
1134 (select * from pa_proj_fp_options
1135 where (GEN_ALL_ETC_SRC_CODE = 'RESOURCE_SCHEDULE' OR
1136 GEN_ALL_SRC_CODE = 'RESOURCE_SCHEDULE' OR
1137 GEN_REV_SRC_CODE = 'RESOURCE_SCHEDULE' OR
1138 GEN_COST_SRC_CODE = 'RESOURCE_SCHEDULE'OR
1139 GEN_COST_ETC_SRC_CODE = 'RESOURCE_SCHEDULE' OR
1140 GEN_REV_ETC_SRC_CODE = 'RESOURCE_SCHEDULE') and FIN_PLAN_VERSION_ID = p_budget_version_id
1141 );
1142
1143 cursor validateBillEvent IS
1144 select 'Y' from dual where
1145 EXISTS
1146 (select * from pa_proj_fp_options
1147 where (GEN_REV_INCL_BILL_EVENT_FLAG = 'Y' OR
1148 GEN_ALL_INCL_BILL_EVENT_FLAG = 'Y') and FIN_PLAN_VERSION_ID = p_budget_version_id
1149 );
1150
1151 cursor validateRevDerv IS
1152 select 'Y' from dual where
1153 EXISTS
1154 (select * from pa_proj_fp_options
1155 where (REVENUE_DERIVATION_METHOD = 'EVENT' ) and FIN_PLAN_VERSION_ID = p_budget_version_id
1156 );
1157
1158 l_Cbs_Enabled VARCHAR2(1) := 'N';
1159 l_project_id number;
1160 L_TEMP VARCHAR2(1) := 'N';
1161
1162 BEGIN
1163 print_msg('PA_ALTERNATE_TASK_PVT.Is_Planning_Options_Valid :: Start');
1164 x_return_status := FND_API.G_RET_STS_SUCCESS;
1165 x_msg_data := NULL;
1166 x_msg_count := 0;
1167 print_msg('Input paramters to PA_ALTERNATE_TASK_PVT.Is_Planning_Options_Valid');
1168 print_msg('p_budget_version_id :: ' || p_budget_version_id);
1169
1170 print_msg('PA_ALTERNATE_TASK_PVT.Is_Planning_Options_Valid :: End');
1171
1172 OPEN getProjectId;
1173 FETCH getProjectId INTO l_project_id;
1174 CLOSE getProjectId;
1175 l_Cbs_Enabled := PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(p_Project_Id => l_project_id);
1176 print_msg('l_Cbs_Enabled :: ' || l_Cbs_Enabled);
1177
1178 IF l_Cbs_Enabled = 'Y' THEN
1179 L_TEMP := 'N';
1180 OPEN validateLowestTask;
1181 FETCH validateLowestTask INTO L_TEMP;
1182 CLOSE validateLowestTask;
1183 IF L_TEMP='Y' THEN
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 x_msg_data := 'PA_CBS_INVALID_LEVEL';
1186 x_msg_count := 1;
1187 END IF;
1188
1189 L_TEMP := 'N';
1190 OPEN validateStaffPlan;
1191 FETCH validateStaffPlan INTO L_TEMP;
1192 CLOSE validateStaffPlan;
1193 IF L_TEMP='Y' THEN
1194 x_return_status := FND_API.G_RET_STS_ERROR;
1195 x_msg_data := 'PA_CBS_INVALID_STAFF_PLAN';
1196 x_msg_count := 1;
1197 END IF;
1198
1199 L_TEMP := 'N';
1200 OPEN validateBillEvent;
1201 FETCH validateBillEvent INTO L_TEMP;
1202 CLOSE validateBillEvent;
1203 IF L_TEMP='Y' THEN
1204 x_return_status := FND_API.G_RET_STS_ERROR;
1205 x_msg_data := 'PA_CBS_INVALID_BILL_EVENT';
1206 x_msg_count := 1;
1207 END IF;
1208
1209 L_TEMP := 'N';
1210 OPEN validateRevDerv;
1211 FETCH validateRevDerv INTO L_TEMP;
1212 CLOSE validateRevDerv;
1213 IF L_TEMP='Y' THEN
1214 x_return_status := FND_API.G_RET_STS_ERROR;
1215 x_msg_data := 'PA_CBS_INVALID_REV_DERV';
1216 x_msg_count := 1;
1217 END IF;
1218
1219 END IF;
1220
1221 EXCEPTION
1222 When Others Then
1223 X_Return_Status := 'U';
1224 X_Msg_Data := SqlErrm;
1225 X_Msg_Count := 1;
1226 Raise;
1227
1228 END Is_Planning_Options_Valid;
1229
1230 FUNCTION Check_Disabled_Cost_Codes(
1231 P_Project_Id IN Number
1232 ) RETURN VARCHAR2
1233 IS
1234 disable_flag VARCHAR2(1):='N';
1235 CURSOR C1 IS
1236 SELECT DECODE(COUNT(*),0,'N','Y') DISABLED_EXISTS
1237 from pa_alternate_tasks pat , pa_rbs_elements pre , pa_proj_elements ppe
1238 where pat.cbs_element_id = pre.rbs_element_id
1239 and ppe.proj_element_id = pat.proj_element_id
1240 and pre.ENABLE_FLAG='N' and ppe.project_id=P_Project_Id;
1241
1242 BEGIN
1243 OPEN C1;
1244 FETCH C1 into disable_flag;
1245 CLOSE C1;
1246
1247 return disable_flag;
1248 END;
1249
1250
1251 -- This API will return the cbs element id of cost code used in project
1252 FUNCTION convert_cost_code_to_id(
1253 p_cost_code IN VARCHAR2,
1254 p_project_id IN NUMBER
1255 ) RETURN NUMBER
1256 IS
1257 l_cbs_element_id NUMBER(15) :=null;
1258 CURSOR C1 IS
1259 SELECT PRE.RBS_ELEMENT_ID CBS_ELEMENT_ID
1260 FROM PA_RBS_ELEMENTS PRE , PA_PROJECTS_ALL PPA
1261 WHERE PRE.RBS_VERSION_ID=PPA.CBS_VERSION_ID
1262 AND PPA.PROJECT_ID=p_project_id
1263 AND PRE.COST_CODE=p_cost_code;
1264
1265 BEGIN
1266 OPEN C1;
1267 FETCH C1 into l_cbs_element_id;
1268 CLOSE C1;
1269 return l_cbs_element_id;
1270
1271 END convert_cost_code_to_id;
1272
1273 PROCEDURE Delete_ALL_Alt_Task(
1274 p_project_id IN NUMBER,
1275 X_Return_Status OUT NOCOPY Varchar2,
1276 X_Msg_Data OUT NOCOPY Varchar2)
1277 IS
1278 l_valid_status_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();
1279 l_Alt_Task_Id_tbl SYSTEM.pa_num_tbl_type :=SYSTEM.PA_NUM_TBL_TYPE();
1280 X_Msg_Count Number;
1281
1282 BEGIN
1283
1284
1285 x_return_status := FND_API.G_RET_STS_SUCCESS;
1286 x_msg_data := NULL;
1287 x_msg_count := 0;
1288
1289 IF p_pa_debug_mode = 'Y' Then
1290 pa_debug.init_err_stack('PA_ALTERNATE_TASK_PVT.Delete_ALL_Alt_Task');
1291 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
1292 End If;
1293 SELECT DISTINCT ALT_TASK_ID BULK COLLECT INTO l_alt_task_id_tbl
1294 FROM PA_PROJ_ELEMENTS PPE, PA_ALTERNATE_TASKS PAT
1295 WHERE PPE.PROJECT_ID=p_project_id
1296 AND PAT.PROJ_ELEMENT_ID=PPE.PROJ_ELEMENT_ID;
1297
1298 if l_Alt_Task_Id_tbl.count > 0 then
1299 PA_ALTERNATE_TASK_PVT.Delete_Alt_Task(
1300 p_Alt_Task_Id =>l_alt_task_id_tbl,
1301 X_Return_Status => X_Return_Status,
1302 X_Msg_Data =>X_Msg_Data,
1303 X_Msg_Count=>X_Msg_Count);
1304 END IF;
1305 print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task :: End');
1306 EXCEPTION
1307 When Others Then
1308 X_Return_Status := 'E';
1309 X_Msg_Count := 1;
1310 END Delete_ALL_Alt_Task;
1311
1312
1313 /*
1314 * This API is used to copy all cost codes from a source project to the
1315 * targeted project.
1316 *
1317 */
1318 Procedure Copy_Cost_Codes_From_Project(
1319 p_Source_Project_Id IN Number,
1320 p_Target_Project_Id IN Number,
1321 X_Return_Status OUT NOCOPY Varchar2,
1322 X_Msg_Data OUT NOCOPY Varchar2,
1323 X_Msg_Count OUT NOCOPY Number)
1324 IS
1325
1326 l_Source_Proj_Element_Id SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();
1327 l_Target_Proj_Element_Id SYSTEM.pa_num_tbl_type := SYSTEM.PA_NUM_TBL_TYPE();
1328
1329 Cursor C_GET_PROJECT_ELEMENTS IS
1330 Select distinct PPE1.Proj_element_id Source_Task_Id, PPE2.Proj_element_id Target_Task_Id
1331 From PA_PROJ_ELEMENTS PPE1, PA_PROJ_ELEMENTS PPE2
1332 Where PPE1.name=PPE2.name AND PPE1.Element_Number=PPE2.Element_Number AND
1333 PPE1.project_id=p_Source_Project_Id AND PPE2.project_id=p_Target_Project_Id
1334 and PPE1.Proj_element_id not in (Select pt.Task_Id from pa_tasks pt where pt.project_id=p_Source_Project_Id)
1335 and PPE2.Proj_element_id not in (Select pt.Task_Id from pa_tasks pt where pt.project_id=p_Target_Project_Id)
1336 and PPE1.OBJECT_TYPE=PPE2.OBJECT_TYPE and PPE1.OBJECT_TYPE='PA_TASKS'
1337 UNION
1338 Select distinct PPE1.task_id Source_Task_Id, PPE2.task_id Target_Task_Id
1339 From PA_TASKS PPE1, PA_TASKS PPE2
1340 Where PPE1.task_name=PPE2.task_name AND PPE1.task_Number=PPE2.task_Number AND
1341 PPE1.project_id=p_Source_Project_Id AND PPE2.project_id=p_Target_Project_Id;
1342
1343 BEGIN
1344 print_msg('PA_ALTERNATE_TASK_PVT.Copy_Cost_Codes_From_Project :: Begin');
1345
1346 OPEN C_GET_PROJECT_ELEMENTS;
1347 FETCH C_GET_PROJECT_ELEMENTS BULK COLLECT INTO l_Source_Proj_Element_Id,l_Target_Proj_Element_Id;
1348 CLOSE C_GET_PROJECT_ELEMENTS;
1349
1350
1351 for i in l_Source_Proj_Element_Id.FIRST..l_Source_Proj_Element_Id.LAST LOOP
1352 Copy_Cost_Codes_From_Task(
1353 p_Source_Task_Id =>l_Source_Proj_Element_Id(i),
1354 p_Target_Task_Id =>l_Target_Proj_Element_Id(i),
1355 X_Return_Status =>X_Return_Status,
1356 X_Msg_Data =>X_Msg_Data,
1357 X_Msg_Count=>X_Msg_Count);
1358 END LOOP;
1359 print_msg('PA_ALTERNATE_TASK_PVT.Copy_Cost_Codes_From_Project :: END');
1360
1361 EXCEPTION
1362 When Others Then
1363 X_Return_Status := 'E';
1364 X_Msg_Count := 1;
1365 END Copy_Cost_Codes_From_Project;
1366
1367 END PA_ALTERNATE_TASK_PVT;