1 PACKAGE BODY PA_UTILS4 AS
2 /* $Header: PAXGUT4B.pls 120.10.12010000.2 2008/08/28 09:22:52 byeturi ship $ */
3
4 PROCEDURE print_msg(p_msg varchar2) IS
5 BEGIN
6 --dbms_output.put_line('Log:PA_UTILS4:'||p_msg);
7 --r_debug.r_msg('Log:PA_UTILS4:'||p_msg);
8 null;
9 END print_msg;
10
11 /* This API returns true if thera are any transactions exists in contract commitment
12 * module for the given project and Task
13 */
14 FUNCTION CheckCCTxnsExists
15 (p_project_id NUmber
16 ,p_task_id Number )
17 RETURN VARCHAR2 IS
18
19 l_CC_txn_Exists Varchar2(1) := 'N';
20
21 BEGIN
22
23 IF p_project_id is NOT NULL AND p_task_id is NOT NULL Then
24 SELECT 'Y'
25 INTO l_CC_txn_Exists
26 FROM dual
27 WHERE EXISTS (
28 SELECT 'CC TXNS'
29 FROM igc_cc_acct_lines igc
30 ,pa_projects_all pp
31 WHERE igc.project_id IS NOT NULL
32 AND igc.project_id = pp.project_id
33 AND pp.project_id = p_project_id
34 AND igc.task_id IN ( SELECT task.task_id
35 FROM pa_tasks task
36 WHERE task.project_id = pp.project_id
37 CONNECT BY PRIOR task.TASK_ID = task.PARENT_TASK_ID
38 START WITH task.TASK_ID = p_task_id
39 )
40 );
41 ElsIF p_project_id is NOT NULL AND p_task_id is NULL Then
42
43 SELECT 'Y'
44 INTO l_CC_txn_Exists
45 FROM dual
46 WHERE EXISTS (
47 SELECT 'CC TXNS'
48 FROM igc_cc_acct_lines igc
49 ,pa_projects_all pp
50 WHERE igc.project_id IS NOT NULL
51 AND igc.project_id = pp.project_id
52 AND pp.project_id = p_project_id
53 );
54 END IF;
55 Return l_CC_txn_Exists;
56
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 l_CC_txn_Exists := 'N';
60 Return l_CC_txn_Exists;
61
62 WHEN OTHERS THEN
63 RAISE;
64 END CheckCCTxnsExists;
65
66 /** This API derives the assignment_id and work_type for the given
67 * person,project,task and transaction date
68 * If the person is having more than one assignment or No assignment then
69 * api returns Assignment_id will be ZERO and name will be null
70 * work type is derivation logic as follows
71 * If The defaulting work_type is set as project assignment
72 * If work_type derived based on assignment is not null then
73 * return project assignment work type
74 * Else if work_type derived based on Task level is not null then
75 * return Task level work type
76 * Else if work_type derived based on project level is not null then
77 * return project level work type
78 * End if;
79 * If The defaulting work_type is set as Task then
80 * If work_type derived based on Task level is not null then
81 * return Task level work type
82 * If work_type derived based on assignment is not null then
83 * return project assignment work type
84 * Else if work_type derived based on project level is not null then
85 * return project level work type
86 * End if;
87 **/
88 PROCEDURE get_work_assignment(p_person_id IN NUMBER
89 ,p_project_id IN NUMBER
90 ,p_task_id IN NUMBER
91 ,p_ei_date IN DATE
92 ,p_system_linkage IN VARCHAR2
93 ,x_tp_amt_type_code OUT NOCOPY VARCHAR2
94 ,x_assignment_id OUT NOCOPY NUMBER
95 ,x_assignment_name IN OUT NOCOPY VARCHAR2
96 ,x_work_type_id OUT NOCOPY NUMBER
97 ,x_work_type_name IN VARCHAR2
98 ,x_return_status OUT NOCOPY VARCHAR2
99 ,x_error_message_code OUT NOCOPY VARCHAR2 ) IS
100
101 cursor cur_work is
102 SELECT work_type_id
103 FROM pa_work_types_v w
104 WHERE w.name = x_work_type_name ;
105
106
107 BEGIN
108
109 x_return_status := 'S';
110 x_error_message_code := null;
111
112 If p_system_linkage in ('ST','OT','ER') Then -- Bug 4092732 and nvl(PA_INSTALL.is_prm_licensed,'N') = 'Y' then
113
114 /** call the api which derives assignment_id and assignment_name **/
115
116 BEGIN
117
118 PA_ASSIGNMENT_UTILS.Get_Person_Asgmt
119 ( p_person_id => p_person_id
120 ,p_project_id => p_project_id
121 ,p_ei_date => p_ei_date
122 ,x_assignment_name => x_assignment_name
123 ,x_assignment_id => x_assignment_id
124 ,x_return_status => x_return_status
125 ,x_error_message_code => x_error_message_code );
126
127 --if the assignment name is passed validate the assignment and if any error
128 -- in validation just return no further processing is required
129 IF x_return_status <> 'S' and x_assignment_name is not null and
130 x_error_message_code = 'PA_NO_ASSIGNMENT' then
131 Return;
132
133 ElsIf x_return_status <> 'S' or x_assignment_id is NULL then
134 x_assignment_id := 0;
135 --x_assignment_name := null;
136 x_return_status := 'S';
137 x_error_message_code := null;
138 End If;
139
140 EXCEPTION
141 WHEN no_data_found then
142 x_assignment_id := 0;
143 --x_assignment_name := null;
144 x_return_status := 'S';
145 x_error_message_code := null;
146 WHEN too_many_rows then
147 --x_assignment_id := 0;
148 --x_assignment_name := null;
149 x_return_status := 'S';
150 x_error_message_code := null;
151 WHEN others then
152 Raise;
153 END;
154
155 Else -- other than system linkage ST,ER and OT set the assignment id to zero
156 --x_assignment_id := 0;
157 NULL;
158
159 End if;
160
161 If x_work_type_name is not null then
162 -- validate the work type
163 OPEN cur_work;
164 FETCH cur_work INTO x_work_type_id;
165 IF cur_work%NOTFOUND then
166 x_return_status := 'E';
167 x_error_message_code := 'INVALID_WORK_TYPE';
168 End If;
169 CLOSE cur_work;
170
171 IF x_return_status <> 'S' and x_error_message_code = 'INVALID_WORK_TYPE' then
172 Return;
173
174 Elsif x_work_type_id is Not null then
175 x_tp_amt_type_code := get_tp_amt_type_code
176 (p_work_type_id => x_work_type_id);
177
178 END IF;
179 End if;
180
181 /** call the api which derives work type id **/
182 If x_work_type_id is null and nvl(pa_utils4.is_exp_work_type_enabled,'N') = 'Y' Then
183
184 x_work_type_id := Get_work_type_id
185 ( p_project_id =>p_project_id
186 ,p_task_id =>p_task_id
187 ,p_assignment_id =>nvl(x_assignment_id ,0)
188 );
189
190 /** added this code to raise error if the work type id not set while
191 * defining the project, task or setting the profile after defining the
192 * project , task in both cases the transaction import program should
193 * reject the transaction
194 **/
195
196 If x_work_type_id is Null and pa_utils4.is_exp_work_type_enabled = 'Y' then
197 x_error_message_code := 'INVALID_WORK_TYPE';
198 x_return_status := 'E';
199 Return;
200 End if;
201
202 If x_work_type_id is NOT NULL and x_work_type_name is NULL then
203 x_tp_amt_type_code := get_tp_amt_type_code
204 (p_work_type_id => x_work_type_id);
205 End if;
206 End if;
207
208 Return;
209
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 x_return_status := 'U';
214 x_error_message_code := sqlcode||sqlerrm;
215 RAISE;
216
217
218 END get_work_assignment;
219
220 FUNCTION get_work_type_id ( p_project_id IN NUMBER
221 ,p_task_id IN NUMBER
222 ,p_assignment_id IN NUMBER
223 ) RETURN NUMBER is
224
225 l_work_type_id number := NULL;
226
227 /* Bug fix: 2667770 removed the old cursor for performance issues
228 * Please refer the previous version of this File for old cursor
229 * New Logic : Based on p_assignment_id open the different cursor
230 */
231
232 CURSOR cur_Assn_worktype IS
233 select DECODE(nvl(pp.assign_precedes_task,'N')
234 ,'Y',decode(ppasgn.work_type_id, NULL,
235 decode(t.work_type_id,NULL,pp.work_type_id,t.work_type_id),ppasgn.work_type_id),
236 'N', decode(t.work_type_id,NULL,
237 decode(ppasgn.work_type_id,NULL,pp.work_type_id,ppasgn.work_type_id)
238 ,t.work_type_id)
239 ) work_type_id
240 FROM pa_projects_all pp
241 ,pa_project_assignments ppasgn
242 ,pa_tasks t
243 WHERE pp.project_id = p_project_id
244 AND t.task_id = p_task_id
245 AND t.project_id = pp.project_id
246 AND ppasgn.assignment_id = p_assignment_id
247 AND ( pp.project_id = ppasgn.project_id
248 OR ( ppasgn.project_id is null
249 and rownum = 1
250 )
251 );
252
253 CURSOR cur_Task_worktype IS
254 select Decode(t.work_type_id,NULL,pp.work_type_id,t.work_type_id) work_type_id
255 FROM pa_projects_all pp
256 ,pa_tasks t
257 WHERE pp.project_id = p_project_id
258 AND t.task_id = p_task_id
259 AND t.project_id = pp.project_id ;
260
261 --l_Found Boolean := FALSE;
262 --l_plsql_index Number;
263
264 BEGIN
265
266 /* If nvl(pa_utils4.is_exp_work_type_enabled,'N') = 'Y' Then commented for bug 3661894*/
267
268 If (G_PrevWkPrjId = p_project_id and
269 G_PrevWkTskId = p_task_id and
270 Nvl(G_PrevWkAsgnId,0) = Nvl(p_assignment_id,0)) Then
271
272 print_msg('Parameter same as previous, G_PrevWkTypeId = '|| G_PrevWkTypeId);
273 l_work_type_id := G_PrevWkTypeId;
274
275 Else
276
277 print_msg('Parameter not same as previous');
278 IF Nvl(p_assignment_id,0) <> 0 Then
279
280 OPEN cur_Assn_worktype;
281 FETCH cur_Assn_worktype INTO l_work_type_id;
282 CLOSE cur_Assn_worktype;
283
284 Else
285 OPEN cur_Task_worktype;
286 FETCH cur_Task_worktype INTO l_work_type_id;
287 CLOSE cur_Task_worktype;
288 End If;
289
290 G_PrevWkPrjId := p_project_id;
291 G_PrevWkTskId := p_task_id;
292 G_PrevWkAsgnId := p_assignment_id;
293 G_PrevWkTypeId := l_work_type_id;
294
295 print_msg('G_PrevWkTypeId = ' || G_PrevWkTypeId);
296
297 End If;
298
299 /* End If; commented for bug 3661894 end of is_exp_work_type_enabled */
300
301
302 Return l_work_type_id;
303
304 EXCEPTION
305
306 WHEN OTHERS THEN
307 RAISE;
308
309 END get_work_type_id;
310
311 FUNCTION get_work_type_name(p_work_type_id IN NUMBER)
312 RETURN varchar2 IS
313
314 l_work_type_name VARCHAR2(80) := Null;
315 l_worktypeid NUMBER;
316 l_found boolean := FALSE;
317
318 BEGIN
319 l_workTypeId := nvl(p_work_type_id,99999999);
320
321 -- Check if there are any records in the pl/sql table
322 If pa_utils4.G_WorkTypeNameRecTab.count > 0 then
323 Begin
324
325 -- Get the Project Number from the pl/sql table.
326 -- If there is no index with the value of the project_id passed
327 -- in then an ora-1403: no_data_found is generated.
328 l_work_type_name := pa_utils4.G_WorkTypeNameRecTab(l_workTypeId).work_type_name;
329 l_Found := TRUE;
330 print_msg('Retreiving workTypeName from cache['||l_work_type_name||']' );
331
332 Exception
333 When No_Data_Found Then
334 l_Found := FALSE;
335 When Others Then
336 Raise;
337
338 End;
339
340 End If;
341
342 -- Since the project has not been cached yet, will need to add it.
343 -- So check to see if there are already 200 records in the pl/sql table.
344 If pa_utils4.G_WorkTypeNameRecTab.COUNT > 199 Then
345
346 pa_utils4.G_WorkTypeNameRecTab.Delete;
347 l_Found := FALSE;
348
349 End If;
350
351 If Not l_Found then
352 If p_work_type_id is NOT NULL then
353 SELECT name
354 INTO l_work_type_name
355 FROM pa_work_types_tl
356 WHERE work_type_id = p_work_type_id
357 and language = userenv('LANG');
358 End if;
359 pa_utils4.G_WorkTypeNameRecTab(l_workTypeId).work_type_name := l_work_type_name;
360 End If;
361
362 Return l_work_type_name;
363
364 EXCEPTION
365 when no_data_found then
366 return null;
367
368 when others then
369 Raise;
370
371 END get_work_type_name;
372
373 FUNCTION get_assignment_name(p_assignment_id IN NUMBER) RETURN varchar2 IS
374
375 l_assignment_name VARCHAR2(80);
376 l_found boolean := FALSE;
377 l_assignmentId number;
378
379 BEGIN
380
381 l_assignmentId := nvl(p_assignment_id,0);
382
383 If pa_utils4.G_AssignNameRecTab.COUNT > 0 then
384 Begin
385
386 l_assignment_name := pa_utils4.G_AssignNameRecTab(l_assignmentId).assignment_name;
387 l_found := TRUE;
388 Exception
389 when no_data_found then
390 l_found := FALSE;
391
392 when others then
393 raise;
394 End;
395
396 End If;
397
398 If pa_utils4.G_AssignNameRecTab.COUNT > 199 then
399
400 pa_utils4.G_AssignNameRecTab.delete;
401 l_found := FALSE;
402
403 End If;
404
405 If Not l_found Then
406 If p_assignment_id is NOT NULL then
407 If p_assignment_id = 0 then
408
409 select meaning
410 into l_assignment_name
411 from pa_lookups
412 where lookup_type = 'PA_EXP_ASSGN_ENTRY'
413 and lookup_code = 'UNSCHEDULED';
414
415 Else
416
417 SELECT assignment_name
418 INTO l_assignment_name
419 FROM pa_project_assignments
420 WHERE assignment_id = p_assignment_id;
421
422 End If;
423 pa_utils4.G_AssignNameRecTab(l_assignmentId).assignment_name := l_assignment_name;
424
425 End if;
426 End If;
427 Return l_assignment_name;
428
429 EXCEPTION
430 when no_data_found then
431 return null;
432
433 when others then
434 Raise;
435
436 END get_assignment_name;
437
438 /** This is an wrapper api which in turn calls procedure
439 * which derives assignment_id and assignment_name
440 */
441 FUNCTION get_assignment_id(p_person_id IN NUMBER
442 ,p_project_id IN NUMBER
443 ,p_task_id IN NUMBER
444 ,p_ei_date IN DATE
445 ) RETURN NUMBER IS
446
447 x_assignment_id number := NULL;
448 x_assignment_name varchar2(80);
449 x_return_status varchar2(80);
450 x_error_message_code varchar2(1000);
451 -- l_plsql_index number;
452 -- l_found Boolean := FALSE;
453
454 BEGIN
455 /* l_plsql_index := nvl(p_project_id,0)||nvl(p_task_id,0)||nvl(p_person_id,0)||
456 nvl(to_char(trunc(p_ei_date),'DDMMYYYY'),99999999);
457
458 print_msg('l_plsql_index in get_assignment_id api['||l_plsql_index||']' );
459
460 If pa_utils4.G_AssignIdRecTab.COUNT > 0 then
461 Begin
462 x_assignment_id := pa_utils4.G_AssignIdRecTab(l_plsql_index).assignment_id;
463 print_msg('Retreiving assignment_id from Cache['||x_assignment_id||']' );
464 l_found := true;
465
466 Exception
467 when no_data_found then
468 l_found := FALSE;
469 when others then
470 raise;
471 End;
472
473 End If;
474
475 If pa_utils4.G_AssignIdRecTab.COUNT > 199 then
476 pa_utils4.G_AssignIdRecTab.delete;
477 l_found := FALSE;
478 End if;
479
480 If Not l_found Then
481
482 print_msg('global profile G_PRM_INSTALLED_FLAG:['||PA_UTILS4.G_PRM_INSTALLED_FLAG||']' );
483
484 If PA_UTILS4.G_PRM_INSTALLED_FLAG is NULL then
485 PA_UTILS4.G_PRM_INSTALLED_FLAG := nvl(PA_INSTALL.is_prm_licensed,'N');
486 print_msg('Executing query to get profile value G_PRM_INSTALLED_FLAG:['
487 ||PA_UTILS4.G_PRM_INSTALLED_FLAG||']' );
488 End If;
489
490 --If nvl(PA_INSTALL.is_prm_licensed,'N') = 'Y' Then commented out for performance
491
492 IF PA_UTILS4.G_PRM_INSTALLED_FLAG = 'Y' then
493 -- call the api which derives assignment_id and assignment_name
494 PA_ASSIGNMENT_UTILS.Get_Person_Asgmt
495 ( p_person_id => p_person_id
496 ,p_project_id => p_project_id
497 ,p_ei_date => p_ei_date
498 ,x_assignment_name => x_assignment_name
499 ,x_assignment_id => x_assignment_id
500 ,x_return_status => x_return_status
501 ,x_error_message_code => x_error_message_code );
502
503 If x_return_status <> 'S' or x_assignment_id is NULL then
504 x_assignment_id := 0;
505 End If;
506 End if;
507 print_msg('Retreiving assignment_id from query['||x_assignment_id||']' );
508 pa_utils4.G_AssignIdRecTab(l_plsql_index).assignment_id := x_assignment_id ;
509
510 End If;
511
512 */
513
514 /* Bug 4092732 : Regardless of PJR license, matching assignment should be stamped against
515 an expenditure item. (assignment_id should be populated on
516 pa_expenditure_items_all based on project assignment)
517 If PA_UTILS4.G_PRM_INSTALLED_FLAG is NULL then
518
519 PA_UTILS4.G_PRM_INSTALLED_FLAG := nvl(PA_INSTALL.is_prm_licensed,'N');
520
521 print_msg('Executing query to get profile value G_PRM_INSTALLED_FLAG:['
522 ||PA_UTILS4.G_PRM_INSTALLED_FLAG||']' );
523 End If;
524
525 */
526
527 ---- Bug 4092732 IF PA_UTILS4.G_PRM_INSTALLED_FLAG = 'Y' then
528
529
530 If (G_PrevAsgPerId = p_person_id and
531 G_PrevAsgPrjId = p_project_id and
532 trunc(G_PrevAsgEIDate) = trunc(p_ei_date)) Then
533
534 print_msg('Parameters same as previous, G_PrevAsgAsgnId = '|| G_PrevAsgAsgnId);
535 x_assignment_id := G_PrevAsgAsgnId;
536
537 Else
538
539 print_msg('Parameters not same as previous, call API');
540 /** call the api which derives assignment_id and assignment_name **/
541 PA_ASSIGNMENT_UTILS.Get_Person_Asgmt
542 ( p_person_id => p_person_id
543 ,p_project_id => p_project_id
544 ,p_ei_date => p_ei_date
545 ,x_assignment_name => x_assignment_name
546 ,x_assignment_id => x_assignment_id
547 ,x_return_status => x_return_status
548 ,x_error_message_code => x_error_message_code );
549
550 If x_return_status <> 'S' or x_assignment_id is NULL then
551 x_assignment_id := 0;
552 End If;
553
554 G_PrevAsgPerId := p_person_id;
555 G_PrevAsgPrjId := p_project_id;
556 G_PrevAsgEIDate := p_ei_date;
557 G_PrevAsgAsgnId := x_assignment_id;
558
559 print_msg('G_PrevAsgAsgnId = ' || G_PrevAsgAsgnId);
560
561 End If;
562
563 --- Bug 4092732 End if;
564
565 RETURN x_assignment_id;
566
567
568 EXCEPTION
569 WHEN NO_DATA_FOUND THEN
570 RETURN 0;
571
572 WHEN OTHERS THEN
573 RAISE;
574 END get_assignment_id;
575
576 FUNCTION get_tp_amt_type_code(p_work_type_id IN Number)
577 RETURN varchar2 IS
578
579 l_tp_amt_code VARCHAR2(80);
580 l_found boolean := FALSE;
581 l_workTypeId NUMBER;
582
583 BEGIN
584 l_workTypeId := nvl(p_work_type_id,99999999);
585
586 -- Check if there are any records in the pl/sql table
587 If pa_utils4.G_TpAmtTypeRecTab.count > 0 then
588 Begin
589
590 -- Get the Project Number from the pl/sql table.
591 -- If there is no index with the value of the project_id passed
592 -- in then an ora-1403: no_data_found is generated.
593 l_tp_amt_code := pa_utils4.G_TpAmtTypeRecTab(l_workTypeId).tp_amt_type_code;
594 l_Found := TRUE;
595 print_msg('Retreiving l_tp_amt_code from cache['||l_tp_amt_code||']' );
596
597 Exception
598 When No_Data_Found Then
599 l_Found := FALSE;
600 When Others Then
601 Raise;
602
603 End;
604
605 End If;
606
607 -- Since the project has not been cached yet, will need to add it.
608 -- So check to see if there are already 200 records in the pl/sql table.
609 If pa_utils4.G_TpAmtTypeRecTab.COUNT > 199 Then
610
611 pa_utils4.G_TpAmtTypeRecTab.Delete;
612 l_Found := FALSE;
613
614 End If;
615
616 If Not l_Found Then
617
618
619 If p_work_type_id is NOT NULL then
620 SELECT tp_amt_type_code
621 INTO l_tp_amt_code
622 FROM pa_work_types_b
623 WHERE work_type_id = p_work_type_id
624 AND trunc(sysdate) between start_date_active
625 and nvl(end_date_active,sysdate);
626 End if;
627 pa_utils4.G_TpAmtTypeRecTab(l_workTypeId).tp_amt_type_code := l_tp_amt_code;
628 End If;
629
630 Return l_tp_amt_code;
631
632 EXCEPTION
633 when no_data_found then
634 return null;
635
636 when others then
637 Raise;
638
639 END get_tp_amt_type_code;
640
641 /** This api derives the site level profile value of
642 * Transaction work type enabled
643 **/
644 FUNCTION is_exp_work_type_enabled RETURN VARCHAR2 IS
645
646 l_enabled_flag varchar2(1);
647 BEGIN
648 print_msg('global value G_WORKTYPE_ENABLED['||PA_UTILS4.G_WORKTYPE_ENABLED||']' );
649 /* cache the profile value in global variable and return it */
650
651 IF PA_UTILS4.G_WORKTYPE_ENABLED is NULL then
652
653 SELECT nvl(fnd_profile.value_specific('PA_EN_NEW_WORK_TYPE_PROCESS'),'N')
654 INTO l_enabled_flag
655 FROM dual;
656 PA_UTILS4.G_WORKTYPE_ENABLED := l_enabled_flag;
657 print_msg('Executing query to get profile G_WORKTYPE_ENABLED['||PA_UTILS4.G_WORKTYPE_ENABLED||']' );
658
659 End If;
660
661 /* In Grants Implemented in OU , work type is not supported */
662 IF PA_UTILS4.G_WORKTYPE_ENABLED = 'Y' then
663 IF (GMS_INSTALL.enabled) THEN
664 PA_UTILS4.G_WORKTYPE_ENABLED := 'N';
665 END IF;
666 END IF;
667
668 return PA_UTILS4.G_WORKTYPE_ENABLED;
669 EXCEPTION
670 WHEN NO_DATA_FOUND then
671 PA_UTILS4.G_WORKTYPE_ENABLED := 'N';
672 return PA_UTILS4.G_WORKTYPE_ENABLED;
673
674 WHEN OTHERS THEN
675 RAISE;
676
677 END is_exp_work_type_enabled;
678 /** This api derives the site level profile value of
679 * Transaction Billablity derived from work type
680 **/
681 FUNCTION is_worktype_billable_enabled RETURN VARCHAR2 IS
682
683 l_enabled_flag varchar2(1);
684 BEGIN
685 print_msg('global profile G_WORKTYPE_BILLABILITY value: ['||PA_UTILS4.G_WORKTYPE_BILLABILITY||']' );
686 --Added this check as if the work type profile is not enabled then
687 --work type billablity cannot be enabled
688 IF is_exp_work_type_enabled = 'Y' then
689
690 IF PA_UTILS4.G_WORKTYPE_BILLABILITY is NULL then
691
692 SELECT nvl(fnd_profile.value_specific('PA_TRXN_BILLABLE_WORK_TYPE'),'N')
693 INTO l_enabled_flag
694 FROM dual;
695 PA_UTILS4.G_WORKTYPE_BILLABILITY := l_enabled_flag;
696 print_msg('Executing query to get profile G_WORKTYPE_BILLABILITY: ['
697 ||PA_UTILS4.G_WORKTYPE_BILLABILITY||']' );
698 End If;
699
700
701 ELse
702 /* l_enabled_flag := 'N'; commented out for performance issue */
703 PA_UTILS4.G_WORKTYPE_BILLABILITY := 'N';
704 End if;
705
706 /* return l_enabled_flag; */
707 return PA_UTILS4.G_WORKTYPE_BILLABILITY;
708 EXCEPTION
709
710 WHEN OTHERS THEN
711 RAISE;
712
713 END is_worktype_billable_enabled;
714 /** This api derives the billability of the
715 * transaction based on the work type and profile option
716 * if p_tc_extn_bill_flag is billable flag derived from client extension is null then
717 * and profile option = Y then api returns billable flag derived from work type
718 * if p_tc_extn_bill_flag is not null and profile option = N then api returns client extension
719 * if p_tc_extn_bill_flag is not null and profile option = N then api returns N
720 * NOTE : This API is called from PATC,PA_ADJUSTMENTS,TRXN_IMPORTS API please before modifying
721 * this api do impact analysis
722 **/
723 FUNCTION get_trxn_work_billabilty(p_work_type_id IN NUMBER
724 ,p_tc_extn_bill_flag IN VARCHAR2 )
725 RETURN varchar2 IS
726
727 CURSOR cur_billwork IS
728 SELECT BILLABLE_CAPITALIZABLE_FLAG
729 FROM pa_work_types_b -- bug 4668816 changed from pa_work_types_v to pa_work_types_b
730 WHERE work_type_id = p_work_type_id
731 AND trunc(sysdate) between start_date_active and
732 nvl(end_date_active,sysdate);
733
734 l_billable_flag varchar2(10);
735 l_temp_flag varchar2(10);
736
737 BEGIN
738
739 l_temp_flag := null;
740
741 -- if the profile option PA: Require Work Type Entry for Expenditures set to NO
742 -- then profile PA: Transaction Billablity derived from work type cannot be set to YES
743 -- based on the above profile option return the billable flag
744
745 IF is_exp_work_type_enabled = 'Y' and is_worktype_billable_enabled = 'Y' then
746 OPEN cur_billwork;
747 FETCH cur_billwork INTO l_billable_flag;
748 CLOSE cur_billwork;
749
750 If l_billable_flag is NOT NULL then
751 l_temp_flag := l_billable_flag;
752 Else
753 l_temp_flag := p_tc_extn_bill_flag;
754 End if;
755 Else
756 l_temp_flag := p_tc_extn_bill_flag;
757 End if;
758
759 Return l_temp_flag;
760
761 EXCEPTION
762
763 WHEN OTHERS THEN
764 RAISE;
765
766 END get_trxn_work_billabilty;
767
768 /* added the function below for BUG 3220230 */
769 --------------------------------------------------------------------------------------------------
770 -- FUNCTION GetOrig_EiBillability_SST() derives the billability of the reversed EIs based on their
771 -- parent EI in the ei table.The argument to this function is the EID of the parent.
772 -- This function only takes care of reversed EI's of ORACLE SELF SERVICE TIME.
773 ---------------------------------------------------------------------------------------------------
774 FUNCTION GetOrig_EiBillability_SST(orig_eid IN NUMBER,billable_flag IN VARCHAR2,trans_source IN VARCHAR2) RETURN VARCHAR2 IS
775 l_billable_flag varchar2(10);
776 l_param_billable_flag varchar2(1) ;
777 BEGIN
778 -- BUG: 4590927
779 -- PJ.R12:DI1:APLINES: ADJUSTING EXPENDITURE ITEM CREATED HAS INCORRECT FLAGS
780 l_param_billable_flag := 'N' ;
781 IF trans_source not in ( 'Oracle Self Service Time',
782 'ORACLE TIME AND LABOR', -- bug 5297060
783 'AP EXPENSE',
784 'AP INVOICE',
785 'AP NRTAX' ,
786 'INTERCOMPANY_AP_INVOICES',
787 'INTERPROJECT_AP_INVOICES',
788 'AP VARIANCE',
789 'AP DISCOUNTS',
790 'AP ERV', /* Bug 5284323 */
791 'PO RECEIPT',
792 'PO RECEIPT NRTAX',
793 'PO RECEIPT PRICE ADJ',
794 'PO RECEIPT NRTAX PRICE ADJ' ) THEN
795
796 l_param_billable_flag := 'Y' ;
797
798 END IF ;
799
800 IF l_param_billable_flag = 'Y' OR orig_eid is NULL THEN
801
802 l_billable_flag := billable_flag;
803
804 ELSE
805
806 SELECT billable_flag
807 INTO l_billable_flag
808 FROM pa_expenditure_items_all ei
809 WHERE ei.expenditure_item_id=orig_eid;
810
811 END IF ;
812
813 RETURN l_billable_flag;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 RAISE;
818
819 END;
820
821
822 /* Added the function below for Bug# 4057474 */
823 ------------------------------------------------------------------------------------------------------
824 -- FUNCTION GetOrig_EiBill_hold() derives the value of bill_hold_flag of the reversed EIs based on
825 -- their parent EI in the ei table. The argument to this function is the EID of the parent.
826 -- This function takes care of reversed EI's of external transaction sources like ORACLE TIME AND LABOR.
827 -------------------------------------------------------------------------------------------------------
828 FUNCTION GetOrig_EiBill_hold(orig_eid IN NUMBER,bill_hold_flag IN VARCHAR2) RETURN VARCHAR2 IS
829 l_bill_hold_flag varchar2(10);
830 BEGIN
831 IF orig_eid is NULL THEN
832 l_bill_hold_flag := bill_hold_flag;
833 ELSE
834 SELECT bill_hold_flag
835 INTO l_bill_hold_flag
836 FROM pa_expenditure_items_all ei
837 WHERE ei.expenditure_item_id=orig_eid;
838 END IF ;
839 RETURN l_bill_hold_flag;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 RAISE;
844
845 END;
846
847
848
849 PROCEDURE check_txn_exists (p_project_id IN NUMBER,
850 p_task_id IN NUMBER ,
851 x_status_code OUT NOCOPY NUMBER,
852 x_err_code OUT NOCOPY VARCHAR2,
853 x_err_stage OUT NOCOPY VARCHAR2) IS
854
855 x_used_in_OTL BOOLEAN; --To pass to OTL API.
856 l_CCTrxexists Varchar2(100) := 'N';
857
858 BEGIN
859
860 -- Check if task has expenditure item
861 x_err_stage := 'check expenditure item for project:'|| p_project_id;
862
863 x_status_code:=PA_PROJ_TSK_UTILS.check_exp_item_exists(p_project_id,p_task_id);
864 IF ( x_status_code = 1) THEN
865 x_err_code :=50;
866 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
867 return;
868 ELSIF ( x_status_code< 0 ) THEN
869 x_err_code :=x_status_code;
870 return;
871 END IF;
872
873 -- Check if task has purchase order distribution
874 x_err_stage := 'check purchase order for project:'|| p_project_id;
875
876 x_status_code :=pa_proj_tsk_utils.check_po_dist_exists(p_project_id, p_task_id);
877
878 IF ( x_status_code = 1 ) then
879 x_err_code := 60;
880 x_err_stage := 'PA_TSK_PO_DIST_EXIST';
881 return;
882 ELSIF ( x_status_code < 0 ) then
883 x_err_code := x_status_code;
884 return;
885 END IF;
886
887 -- Check if task has purchase order requisition
888 x_err_stage := 'check purchase order requisition for project: '|| p_project_id;
889 x_status_code := pa_proj_tsk_utils.check_po_req_dist_exists(p_project_id, p_task_id);
890
891 IF ( x_status_code = 1 ) then
892 x_err_code := 70;
893 x_err_stage := 'PA_TSK_PO_REQ_DIST_EXIST';
894 return;
895 ELSIF ( x_status_code < 0 ) then
896 x_err_code := x_status_code;
897 return;
898 END IF;
899
900 -- Check if task has supplier invoices
901 x_err_stage := 'check supplier invoice for project:'|| p_project_id;
902 x_status_code := pa_proj_tsk_utils.check_ap_invoice_exists(p_project_id, p_task_id);
903 IF ( x_status_code = 1 ) then
904 x_err_code := 80;
905 x_err_stage := 'PA_TSK_AP_INV_EXIST';
906 return;
907 ELSIF ( x_status_code < 0 ) then
908 x_err_code := x_status_code;
909 return;
910 END IF;
911
912 -- Check if task has supplier invoice distribution
913 x_err_stage := 'check supplier inv distribution for project: '|| p_project_id;
914 x_status_code := pa_proj_tsk_utils.check_ap_inv_dist_exists(p_project_id, p_task_id);
915 IF ( x_status_code = 1 ) then
916 x_err_code := 90;
917 x_err_stage := 'PA_TSK_AP_INV_DIST_EXIST';
918 return;
919 ELSIF ( x_status_code < 0 ) then
920 x_err_code := x_status_code;
921 return;
922 END IF;
923
924 -- Check to see if the project has any Contract Commitment Trxns exists
925 x_err_stage := 'check if project used in ContractCommitments for project: '|| p_project_id;
926 l_CCTrxexists := CheckCCTxnsExists(p_project_id=> p_project_id,p_task_id =>p_task_id);
927
928 IF l_CCTrxexists = 'Y' Then
929 x_status_code := 1;
930 x_err_code := 100;
931 x_err_stage := 'PA_TASK_CC_TXN_EXIST';
932 return;
933 End If;
934
935 --Check to see if the project has been used in OTL--Added by Ansari
936 x_err_stage := 'check if project used in OTL for project: '|| p_project_id;
937 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'PROJECT_ID',
938 p_search_value => p_project_id,
939 x_used => x_used_in_OTL );
940 --If exists in OTL
941 IF x_used_in_OTL THEN
942 x_err_code := 200;
943 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
944 return;
945 END IF;
946
947
948 IF x_status_code IS NULL THEN
949 x_status_code:=0;
950 END IF;
951
952 IF x_err_code IS NULL THEN
953 x_err_code:=0;
954 END IF;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 x_err_code := SQLCODE;
959 rollback;
960 return;
961 END check_txn_exists;
962
963 /** This api checks if a bill rate schedule is used
964 * in any organization assignment.
965 */
966 FUNCTION IsUsedInCosting(p_bill_rate_sch_id IN NUMBER )
967 RETURN BOOLEAN IS
968
969 CURSOR c1 IS
970 SELECT 'X'
971 FROM sys.dual
972 WHERE EXISTS ( SELECT 'x'
973 FROM pa_org_labor_sch_rule a
974 WHERE (a.cost_rate_sch_id = p_bill_rate_sch_id
975 OR
976 a.FORECAST_COST_RATE_SCH_ID = p_bill_rate_sch_id
977 )
978 ) ;
979 /* Bug # 3613754 In ISUSEDINCOSTING Function, splitted the cursor in two cursor */
980
981 Cursor C2 Is
982 SELECT 'X'
983 FROM sys.dual
984 WHERE EXISTS ( SELECT 'y'
985 FROM pa_compensation_details_all b
986 WHERE b.rate_schedule_id = p_bill_rate_sch_id
987 ) ;
988
989
990
991 l_check_if_exists varchar2(1);
992
993 BEGIN
994
995 OPEN C1 ;
996 FETCH c1 INTO l_check_if_exists;
997 CLOSE c1 ;
998 IF l_check_if_exists = 'X' THEN
999 RETURN (TRUE);
1000 ELSE
1001 Open C2 ;
1002 FETCH C2 INTO l_check_if_exists;
1003 CLOSE C2 ;
1004 If l_check_if_exists = 'X' Then
1005 RETURN (TRUE);
1006 Else
1007 RETURN (FALSE);
1008 End If;
1009 END IF ;
1010
1011 EXCEPTION
1012
1013 WHEN OTHERS THEN
1014
1015 RAISE;
1016
1017 END IsUsedInCosting;
1018
1019 /** This API validates the given IN param is Number or Not
1020 * If not Number return -9999
1021 */
1022
1023 FUNCTION getNumericString(p_reference1 IN varchar2) RETURN NUMBER IS
1024
1025 l_return_number Number := -9999;
1026
1027 BEGIN
1028 If p_reference1 IS Not Null Then
1029
1030 SELECT TO_NUMBER(p_reference1)
1031 INTO l_return_number
1032 FROM dual;
1033
1034 End If;
1035
1036 Return l_return_number;
1037
1038 EXCEPTION
1039
1040 WHEN OTHERS THEN
1041 l_return_number := -9999;
1042 RETURN l_return_number;
1043
1044 END getNumericString;
1045
1046 /** This API will return Implementaion OrgId and uses cacheing logic
1047 */
1048 FUNCTION get_org_id RETURN NUMBER IS
1049
1050 BEGIN
1051
1052 IF PA_UTILS4.G_imp_org_id IS NOT NULL Then
1053 Return G_imp_org_id;
1054 End IF;
1055
1056 get_imp_values(x_prim_sob =>PA_UTILS4.G_imp_sob_id
1057 ,x_org_id =>PA_UTILS4.G_imp_org_id
1058 ,x_book_type_code =>PA_UTILS4.G_imp_book_type_code
1059 ,x_business_group =>PA_UTILS4.G_imp_bus_group
1060 );
1061
1062 Return PA_UTILS4.G_imp_org_id;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 Return PA_UTILS4.G_imp_org_id;
1067
1068 END get_org_id;
1069
1070 /** This API returns set_of_books_id from the implementations
1071 **/
1072 FUNCTION get_primary_sob RETURN NUMBER IS
1073
1074 BEGIN
1075 IF PA_UTILS4.G_imp_sob_id IS NOT NULL Then
1076 Return PA_UTILS4.G_imp_sob_id;
1077 End IF;
1078
1079 get_imp_values(x_prim_sob =>PA_UTILS4.G_imp_sob_id
1080 ,x_org_id =>PA_UTILS4.G_imp_org_id
1081 ,x_book_type_code =>PA_UTILS4.G_imp_book_type_code
1082 ,x_business_group =>PA_UTILS4.G_imp_bus_group
1083 );
1084
1085 Return PA_UTILS4.G_imp_sob_id;
1086
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 Return PA_UTILS4.G_imp_sob_id;
1090 END get_primary_sob;
1091
1092 /** This API returns the Implementation values **/
1093 PROCEDURE get_imp_values(x_prim_sob OUT NOCOPY Number
1094 ,x_org_id OUT NOCOPY NUmber
1095 ,x_book_type_code OUT NOCOPY varchar2
1096 ,x_business_group OUT NOCOPY number
1097 ) IS
1098
1099 BEGIN
1100
1101 IF (PA_UTILS4.G_imp_sob_id is NULL OR PA_UTILS4.G_imp_org_id IS NULL or
1102 PA_UTILS4.G_imp_book_type_code <> 'X' or PA_UTILS4.G_imp_bus_group is NULL) Then
1103
1104 SELECT SET_OF_BOOKS_ID
1105 ,ORG_ID
1106 ,nvl(BOOK_TYPE_CODE,'X')
1107 ,BUSINESS_GROUP_ID
1108 INTO PA_UTILS4.G_imp_sob_id
1109 ,PA_UTILS4.G_imp_org_id
1110 ,PA_UTILS4.G_imp_book_type_code
1111 ,PA_UTILS4.G_imp_bus_group
1112 FROM pa_implementations;
1113
1114 End IF;
1115
1116 x_prim_sob := PA_UTILS4.G_imp_sob_id;
1117 x_org_id := PA_UTILS4.G_imp_org_id;
1118 x_book_type_code := PA_UTILS4.G_imp_book_type_code;
1119 x_business_group := PA_UTILS4.G_imp_bus_group;
1120
1121 EXCEPTION
1122 when no_data_found then
1123 PA_UTILS4.G_imp_sob_id := Null;
1124 PA_UTILS4.G_imp_org_id := Null;
1125 PA_UTILS4.G_imp_book_type_code := Null;
1126 PA_UTILS4.G_imp_bus_group := Null;
1127 x_prim_sob := PA_UTILS4.G_imp_sob_id;
1128 x_org_id := PA_UTILS4.G_imp_org_id;
1129 x_book_type_code := PA_UTILS4.G_imp_book_type_code;
1130 x_business_group := PA_UTILS4.G_imp_bus_group;
1131 RETURN;
1132 when others then
1133 Raise;
1134
1135 END get_imp_values;
1136
1137 /** This API returns the Business group Id for the given Organization Id **/
1138 FUNCTION GetOrgBusinessGrpId(p_organization_id IN Number)
1139
1140 RETURN NUMBER IS
1141 l_business_grp_id Number := Null;
1142
1143 BEGIN
1144 IF p_organization_id IS NOT NULL THEN
1145
1146 SELECT BUSINESS_GROUP_ID
1147 INTO l_business_grp_id
1148 FROM HR_ALL_ORGANIZATION_UNITS
1149 WHERE ORGANIZATION_ID = p_organization_id;
1150
1151 END IF;
1152
1153 RETURN l_business_grp_id;
1154
1155 EXCEPTION
1156 WHEN NO_DATA_FOUND THEN
1157 RETURN NULL;
1158
1159 WHEN OTHERS THEN
1160 RAISE;
1161
1162 END GetOrgBusinessGrpId;
1163
1164 /* This is an public API, which in turn calls a private function CheckCCTxnsExists
1165 * This api will be called from project and task Form before deleting
1166 * any of the task or project
1167 */
1168 PROCEDURE Check_CC_TxnExists(p_project_id Number
1169 ,p_task_id Number
1170 ,x_return_status OUT NOCOPY varchar2
1171 ,x_msg_data OUT NOCOPY varchar2
1172 ,x_msg_count OUT NOCOPY Number ) IS
1173
1174 l_error_msg_code varchar2(100);
1175 l_msg_count Number;
1176 l_msg_data Varchar2(1000);
1177 l_trx_exists varchar2(1);
1178
1179 BEGIN
1180 x_return_status := 'S';
1181 x_msg_data := Null;
1182 x_msg_count := 0;
1183
1184 /** clear the message stack **/
1185 fnd_msg_pub.INITIALIZE;
1186
1187 l_trx_exists := CheckCCTxnsExists(p_project_id=> p_project_id,p_task_id =>p_task_id);
1188 If l_trx_exists <> 'N' Then
1189 x_msg_data := 'PA_TASK_CC_TXN_EXIST';
1190 x_return_status := 'E';
1191 x_msg_count := 1;
1192
1193 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1194 ,p_msg_name =>x_msg_data
1195 );
1196
1197 End if;
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 RAISE;
1201
1202 END Check_CC_TxnExists;
1203
1204 /* This is an public API, which in turn calls a private functions
1205 * This api will be called from budgetary controls form to check any
1206 * transactions exists for project or task. If so the budgetary control form
1207 * will be modified to read only mode
1208 */
1209 PROCEDURE CheckToEnableBdgtCtrl(p_project_id Number
1210 ,p_task_id Number
1211 ,p_mode Varchar2 Default 'BDGTCTRL'
1212 ,x_return_status OUT NOCOPY varchar2
1213 ,x_error_msg_code OUT NOCOPY varchar2
1214 ,x_error_stage OUT NOCOPY varchar2
1215 ) IS
1216
1217 l_stage Varchar2(1000);
1218 l_status_code Number := 0;
1219 l_param varchar2(100);
1220 l_project_id Number;
1221 l_task_id Number;
1222 l_CCTrxexists varchar2(1);
1223 BEGIN
1224
1225 -- Initialize the Out variables
1226 x_return_status := 'S';
1227 x_error_msg_code := NULL;
1228 x_error_stage := NULL;
1229 l_status_code := 0;
1230
1231 l_stage := 'Validating IN params';
1232 IF p_project_id is NULL and p_task_id is NULL Then
1233 Return;
1234 ElsIf p_mode = 'BDGTCTRL' Then
1235 l_project_id := p_project_id;
1236 l_task_id := NULL;
1237 Else
1238 l_project_id := p_project_id;
1239 l_task_id := p_task_id;
1240 End If;
1241
1242 l_param := ':P_mode['||p_mode||']ProjectId['||l_project_id||']TasId['||l_task_id||']';
1243
1244 -- Check for Requisitions
1245 l_stage := 'Check for Requisitions';
1246 IF x_return_status = 'S' Then
1247 l_status_code := pa_proj_tsk_utils.check_po_req_dist_exists(l_project_id, l_task_id);
1248 IF ( l_status_code = 1 ) then
1249 x_error_msg_code := 'PA_PRJ_PO_REQ_DIST_EXIST';
1250 x_error_stage := l_stage||l_param;
1251 x_return_status := 'E';
1252 END IF;
1253 End If;
1254
1255 -- Check for Purchase order
1256 IF x_return_status = 'S' Then
1257 l_stage := 'Check purchase order';
1258 l_status_code := pa_proj_tsk_utils.check_po_dist_exists(l_project_id, l_task_id);
1259 IF ( l_status_code = 1 ) then
1260 x_error_msg_code := 'PA_PRJ_PO_DIST_EXIST';
1261 x_error_stage := l_stage||l_param;
1262 x_return_status := 'E';
1263 END IF;
1264 End If;
1265
1266
1267 /* Bug 6153950: start
1268 Bug 6153950: It is sufficient to query AP Invoice Distributions to check for AP Txns
1269 Hence call to pa_proj_tsk_utils.check_ap_invoice_existsis is commented here
1270
1271 -- Check for Supplier Invoices
1272 IF x_return_status = 'S' Then
1273 l_stage := 'Check for Supplier Invoices';
1274 l_status_code := pa_proj_tsk_utils.check_ap_invoice_exists(l_project_id, l_task_id);
1275 IF ( l_status_code = 1 ) then
1276 x_error_msg_code := 'PA_PRJ_AP_INV_EXIST';
1277 x_error_stage := l_stage||l_param;
1278 x_return_status := 'E';
1279 END IF;
1280 End If;
1281 Bug 6153950: end */
1282
1283
1284 -- Check if task has supplier invoice distribution
1285 IF x_return_status = 'S' Then
1286 l_stage := 'Check for Supplier Invoice Distributions';
1287 l_status_code := pa_proj_tsk_utils.check_ap_inv_dist_exists(l_project_id, l_task_id);
1288 IF ( l_status_code = 1 ) then
1289 x_error_msg_code := 'PA_PRJ_AP_INV_DIST_EXIST';
1290 x_error_stage := l_stage||l_param;
1291 x_return_status := 'E';
1292 END IF;
1293 End If;
1294
1295 -- Check for Contract Commitments
1296 IF x_return_status = 'S' Then
1297 l_stage := 'Check for Contract Commitments';
1298 l_CCTrxexists := CheckCCTxnsExists(p_project_id=> l_project_id,p_task_id =>l_task_id);
1299 IF l_CCTrxexists = 'Y' Then
1300 x_error_msg_code:= 'PA_PRJ_CC_TXN_EXIST';
1301 x_error_stage := l_stage||l_param;
1302 x_return_status := 'E';
1303 End If;
1304 End if;
1305
1306 RETURN;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 x_error_msg_code := SQLCODE||SQLERRM;
1311 x_error_stage := l_stage||l_param;
1312 x_return_status := 'U';
1313 Raise;
1314
1315
1316 END CheckToEnableBdgtCtrl;
1317
1318
1319 -- New functions added for PJM changes.
1320 Function get_unit_of_measure ( p_expenditure_type IN VARCHAR2 ) return VARCHAR2 IS
1321
1322 l_uom VARCHAR2(30) := NULL ;
1323 begin
1324 select unit_of_measure
1325 into l_uom
1326 from pa_expenditure_types
1327 where expenditure_type = p_expenditure_type ;
1328 return l_uom ;
1329 Exception
1330 when NO_DATA_FOUND THEN
1331 return l_uom ;
1332 end ;
1333
1334 Function get_unit_of_measure_m ( p_unit_of_measure IN VARCHAR2 ,
1335 p_expenditure_type IN VARCHAR2) return VARCHAR2 IS
1336
1337 l_uom_m VARCHAR2(80) := NULL ;
1338 begin
1339 -- If unit_of_measure in pa_expenditure_items_all is populated
1340 If p_unit_of_measure IS NOT NULL THEN
1341 select l.meaning
1342 into l_uom_m
1343 from pa_lookups l
1344 where lookup_type = 'UNIT'
1345 and lookup_code = p_unit_of_measure ;
1346 -- If unit_of_measure in pa_expenditure_items_all is NOT populated
1347 else
1348 select l.meaning
1349 into l_uom_m
1350 from pa_lookups l,
1351 pa_expenditure_types et
1352 where lookup_type = 'UNIT'
1353 and lookup_code = et.unit_of_measure
1354 and et.expenditure_type = p_expenditure_type ;
1355 End if ;
1356
1357 return l_uom_m ;
1358 Exception
1359 when NO_DATA_FOUND THEN
1360 return l_uom_m ;
1361 end get_unit_of_measure_m;
1362
1363 Function GET_EMP_NAME_NUMBER( p_incurred_by_person_id IN NUMBER,
1364 p_expenditure_ending_date IN DATE,
1365 p_mode IN VARCHAR2 ) Return VARCHAR2 IS
1366 begin
1367 if NVL (G_INCURRED_BY_PERSON_ID,0) <> p_incurred_by_person_id THEN
1368 select P.FULL_NAME,NVL(P.EMPLOYEE_NUMBER,P.NPW_NUMBER)
1369 into G_full_name, G_employee_number
1370 from PER_PEOPLE_F P
1371 WHERE P.PERSON_ID = p_incurred_by_person_id
1372 AND TRUNC(p_EXPENDITURE_ENDING_DATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE ;
1373 end if ;
1374
1375 if p_mode = 'EMP_NAME' then
1376 Return G_full_name;
1377 else
1378 Return G_employee_number ;
1379 end if ;
1380 EXCEPTION
1381 WHEN NO_DATA_FOUND THEN
1382 return NULL ;
1383 end ;
1384
1385 Function get_wip_resource_code(p_wip_resource_id IN NUMBER ) Return VARCHAR2 IS
1386 l_resource_code VARCHAR2(30) := null;
1387 begin
1388 select resource_code
1389 into l_resource_code
1390 from bom_resources
1391 where resource_id = p_wip_resource_id ;
1392 return l_resource_code;
1393
1394 Exception
1395 WHEN NO_DATA_FOUND then
1396 return l_resource_code ;
1397 end ;
1398
1399 FUNCTION get_inventory_item(p_inventory_item_id IN NUMBER) Return VARCHAR2 IS
1400 l_inventory_item VARCHAR2(4000):= NULL ; /* Modified the size for the bug 6652655 */
1401 begin
1402 Select Concatenated_Segments
1403 into l_inventory_item
1404 from Mtl_System_Items_Kfv
1405 where Inventory_Item_Id = p_inventory_item_id
1406 and rownum = 1 ;
1407 --and Organization_Id = p_Incurred_By_Organization_Id ; -- fix for bug : 3181386
1408 return l_inventory_item ;
1409 Exception
1410 when NO_DATA_FOUND then
1411 return l_inventory_item ;
1412 end ;
1413
1414 -- New function, created for AP Invoice Lines Uptake for R12, to get the check number for passed invoice payment id.
1415
1416 FUNCTION get_invoice_payment_num(p_transaction_source IN VARCHAR2,p_inv_payment_id IN VARCHAR2) Return NUMBER IS
1417 l_inv_payment_id NUMBER:= NULL ;
1418 Begin
1419
1420 If (PA_UTILS4.get_ledger_cash_basis_flag = 'Y' or p_transaction_source = 'AP DISCOUNTS') Then -- Accounting Method is Cash.
1421
1422 select chk.check_number
1423 into l_inv_payment_id
1424 from ap_checks chk,
1425 ap_invoice_payments pay
1426 where pay.check_id = chk.check_id
1427 and pay.invoice_payment_id = to_number(NVL2(LTRIM(p_inv_payment_id,'0123456789'), NULL, p_inv_payment_id));
1428
1429 End If;
1430
1431 return l_inv_payment_id;
1432
1433 Exception
1434 when NO_DATA_FOUND then
1435 return l_inv_payment_id;
1436 End ;
1437
1438 -- New function, created for AP Invoice Lines Uptake for R12, to get sla cash basis flag.
1439
1440 FUNCTION get_ledger_cash_basis_flag Return VARCHAR2 IS
1441 l_ledger_cash_basis_flag VARCHAR2(1) := 'N';
1442 Begin
1443
1444 select nvl(glsla.sla_ledger_cash_basis_flag,'N')
1445 into l_ledger_cash_basis_flag
1446 from gl_ledgers glsla,
1447 pa_implementations imp
1448 where glsla.ledger_id = imp.set_of_books_id;
1449
1450 return l_ledger_cash_basis_flag;
1451
1452 Exception
1453 when NO_DATA_FOUND then
1454 return l_ledger_cash_basis_flag;
1455 End ;
1456
1457 /*----------------------------------------------------------------------------*/
1458 -- Start of Comments
1459 -- API name : IsProjectsImplemented
1460 -- Type : Public Function
1461 -- Pre-reqs : None
1462 -- Function : To check if Projects is implemented for a given OU
1463 -- Return Value : VARCHAR2
1464 -- Prameters
1465 -- p_org_id IN NUMBER REQUIRED
1466 -- History
1467 -- 05-MAY-05 Vgade -Created
1468 --
1469 /*----------------------------------------------------------------------------*/
1470 FUNCTION IsProjectsImplemented(p_org_id IN Number) RETURN VARCHAR2 IS
1471
1472 l_pa_implemented varchar2(1) := 'N';
1473
1474 BEGIN
1475 SELECT 'Y'
1476 INTO l_pa_implemented
1477 FROM pa_implementations_all
1478 WHERE org_id = p_org_id;
1479
1480 Return l_pa_implemented;
1481
1482 EXCEPTION
1483
1484 WHEN OTHERS THEN
1485 l_pa_implemented := 'N';
1486 RETURN l_pa_implemented;
1487
1488 END IsProjectsImplemented;
1489
1490 END pa_utils4;
[Home] [Help]