1 PACKAGE BODY PA_UTILS3 AS
2 /* $Header: PAXGUT3B.pls 120.10 2011/01/18 13:16:14 ethella ship $ */
3
4 G_Projects_Tab Pa_Utils3.ProjectsTab;
5 G_Tasks_Tab Pa_Utils3.TasksTab;
6 G_EmpInfo_Tab Pa_Utils3.EmpInfoTab;
7 G_OrgName_Tab Pa_Utils3.OrgNameTab;
8 G_OrgId_Tab Pa_Utils3.OrgIdTab;
9 /* R12 Changes Start */
10 G_OUName_Tab PA_Utils3.OUNameTab;
11 /* R12 Changes End */
12
13 Function Get_System_Linkage ( P_Expenditure_Type IN varchar2,
14 P_System_Linkage_Function IN varchar2,
15 P_System_Linkage_M IN varchar2 ) RETURN VARCHAR2
16 Is
17
18 l_dummy NUMBER;
19
20 Begin
21
22 Select 1
23 Into l_dummy
24 From pa_expenditure_types_expend_v
25 Where expenditure_type = p_expenditure_type
26 And system_linkage_function <> p_system_linkage_function;
27
28 Return ( '-' || p_system_linkage_m);
29
30 Exception
31 When NO_DATA_FOUND Then
32 RETURN(NULL);
33 When TOO_MANY_ROWS Then
34 RETURN( '-' || p_system_linkage_m);
35
36 End get_system_linkage;
37
38 Procedure GetCachedProjNum (P_Project_Id IN NUMBER,
39 X_Project_Number OUT NOCOPY VARCHAR2)
40
41 Is
42
43 l_Found BOOLEAN := FALSE;
44
45 Begin
46
47 -- Check if there are any records in the pl/sql table.
48 If G_Projects_Tab.COUNT > 0 Then
49
50 Begin
51
52 -- Get the Project Number from the pl/sql table.
53 -- If there is no index with the value of the project_id passed
54 -- in then an ora-1403: no_data_found is generated.
55 X_Project_Number := G_Projects_Tab(P_Project_Id).Project_Number;
56 l_Found := TRUE;
57
58 Exception
59 When No_Data_Found Then
60 l_Found := FALSE;
61 When Others Then
62 Raise;
63
64 End;
65
66 End If;
67
68 If Not l_Found Then
69
70 -- Since the project has not been cached yet, will need to add it.
71 -- So check to see if there are already 200 records in the pl/sql table.
72 If G_Projects_Tab.COUNT > 199 Then
73
74 G_Projects_Tab.Delete;
75
76 End If;
77
78 -- Get the project_number.
79 Select
80 Segment1
81 Into
82 X_Project_Number
83 From
84 Pa_Projects_All
85 Where
86 Project_Id = P_Project_Id;
87
88 -- Add the project number to the pl/sql table using the project_id
89 -- as the index value. This makes things fast.
90 G_Projects_Tab(P_Project_Id).Project_Number := X_Project_Number;
91
92 End If;
93
94 Exception
95 When Others Then
96 Raise;
97
98 End GetCachedProjNum;
99
100 Procedure GetCachedTaskNum (P_Task_Id IN NUMBER,
101 X_Task_Number OUT NOCOPY VARCHAR2)
102
103 Is
104
105 l_Found BOOLEAN := FALSE;
106
107 Begin
108
109 -- Check if there are any records in the pl/sql table.
110 If G_Tasks_Tab.COUNT > 0 Then
111
112 Begin
113
114 -- Get the Task Number from the pl/sql table.
115 -- If there is no index with the value of the task_id passed
116 -- in then an ora-1403: no_data_found is generated.
117 X_Task_Number := G_Tasks_Tab(P_Task_Id).Task_Number;
118 l_Found := TRUE;
119
120 Exception
121 When No_Data_Found Then
122 l_Found := FALSE;
123 When Others Then
124 Raise;
125
126 End;
127
128 End If;
129
130 If Not l_Found Then
131
132 -- Since the task has not been cached yet, will need to add it.
133 -- So check to see if there are already 200 records in the pl/sql table.
134 If G_Tasks_Tab.COUNT > 199 Then
135
136 G_Tasks_Tab.Delete;
137
138 End If;
139
140 -- Get the task number.
141 Select
142 Task_Number
143 Into
144 X_Task_Number
145 From
146 Pa_Tasks
147 Where
148 Task_Id = P_Task_Id;
149
150 -- Add the task number to the pl/sql table using the task_id
151 -- as the index value. This makes things fast.
152 G_Tasks_Tab(P_Task_Id).Task_Number := X_Task_Number;
153
154 End If;
155
156 Exception
157 When Others Then
158 Raise;
159
160 End GetCachedTaskNum;
161
162 Procedure GetCachedEmpInfo (P_Inc_By_Per_Id IN NUMBER,
163 P_exp_date IN DATE , --bug 9853319
164 X_Inc_By_Per_Number OUT NOCOPY VARCHAR2,
165 X_Business_Group_Name OUT NOCOPY VARCHAR2)
166
167 Is
168
169 l_Found BOOLEAN := FALSE;
170
174 If G_EmpInfo_Tab.COUNT > 0 Then
171 Begin
172
173 -- Check if there are any records in the pl/sql table.
175
176 Begin
177
178 -- Get the Employee Number and business group from the pl/sql table.
179 -- If there is no index with the value of the inc by person_id passed
180 -- in then an ora-1403: no_data_found is generated.
181 X_Inc_By_Per_Number := G_EmpInfo_Tab(P_Inc_By_Per_Id).Employee_Number;
182 X_Business_Group_Name := G_EmpInfo_Tab(P_Inc_By_Per_Id).Business_Group_Name;
183 l_Found := TRUE;
184
185 Exception
186 When No_Data_Found Then
187 l_Found := FALSE;
188 When Others Then
189 Raise;
190
191 End;
192
193 End If;
194
195 If Not l_Found Then
196
197 -- Since the employee info has not been cached yet, will need to add it.
198 -- So check to see if there are already 200 records in the pl/sql table.
199 If G_EmpInfo_Tab.COUNT > 199 Then
200
201 G_EmpInfo_Tab.Delete;
202
203 End If;
204
205 -- Get the employee number/npw number and business group name
206 -- If the current_employee_flag is 'N' then we are dealing with contingent worker
207 -- and want to grab the npw_number inplace of the employee_number.
208 Select
209 Decode(P.Current_Employee_Flag,'Y',P.Employee_Number,P.Npw_Number),
210 O.Name
211 Into
212 X_Inc_By_Per_Number,
213 X_Business_Group_Name
214 From
215 --FP M To reduce dependency on HR, changing the per_people_x to per_all_people_f
216 --and added the date check
217 --Per_People_X P,
218 Per_All_People_F P,
219 Hr_Organization_Units O
220 Where
221 P.Person_Id = P_Inc_By_Per_Id
222 and O.Organization_Id = P.Business_Group_Id
223 and trunc(P_exp_date) between trunc(p.effective_start_date) and trunc(p.effective_end_date);
224
225 -- Add the employee info to the pl/sql table using the P_Inc_By_Per_Id
226 -- as the index value. This makes things fast.
227 G_EmpInfo_Tab(P_Inc_By_Per_Id).Employee_Number := X_Inc_By_Per_Number;
228 G_EmpInfo_Tab(P_Inc_By_Per_Id).Business_Group_Name := X_Business_Group_Name;
229
230 End If;
231
232 Exception
233 When Others Then
234 Raise;
235
236 End GetCachedEmpInfo;
237
238 Procedure GetCachedOrgName (P_Inc_By_Per_Id IN NUMBER,
239 P_Exp_Item_Date IN DATE,
240 X_Inc_By_Org_Name OUT NOCOPY VARCHAR2)
241
242 Is
243
244 l_Found BOOLEAN := FALSE;
245 l_Index BINARY_INTEGER;
246
247 Begin
248
249 -- Check if there are any records in the pl/sql table.
250 If G_OrgName_Tab.COUNT > 0 Then
251
252 -- Get the Inc by Org Name if it exist by looping thru the pl/sql table.
253 For i in G_OrgName_Tab.First .. G_OrgName_Tab.Last
254 Loop
255
256 If to_char(P_Inc_By_Per_Id) || to_char(P_Exp_Item_Date) = G_OrgName_Tab(i).PersonId_Date Then
257
258 X_Inc_By_Org_Name := G_OrgName_Tab(i).Org_Name;
259 l_Found := TRUE;
260 Exit;
261
262 End If;
263
264 End Loop;
265
266 End If;
267
268 If Not l_Found Then
269
270 -- Since the employee info has not been cached yet, will need to add it.
271 -- So check to see if there are already 200 records in the pl/sql table.
272 If G_OrgName_Tab.COUNT > 199 Then
273
274 G_OrgName_Tab.Delete;
275
276 End If;
277
278 -- Get the organization name
279 Select
280 O.Name
281 Into
282 X_Inc_By_Org_Name
283 From
284 Per_Assignments_F A,
285 Hr_All_Organization_Units O
286 Where
287 O.Organization_id = A.Organization_Id
288 And A.person_id = P_Inc_By_Per_Id
289 And A.Primary_Flag = 'Y'
290 And A.Organization_Id is not null
291 And A.Job_Id is not null
292 And A.Assignment_Type in ('E','C')
293 And P_Exp_Item_Date between A.Effective_Start_Date
294 and nvl(A.Effective_End_Date, P_Exp_Item_Date) ;
295
296 -- Find the next availabe index to use.
297 l_Index := G_OrgName_Tab.COUNT + 1;
298
299 -- Insert the employee info into the pl/sql table.
300 G_OrgName_Tab(l_Index).PersonId_Date := to_char(P_Inc_By_Per_Id) || to_char(P_Exp_Item_Date);
301 G_OrgName_Tab(l_Index).Org_Name := X_Inc_By_Org_Name;
302
303 End If;
304
305 Exception
306 When Others Then
307 Raise;
308
309 End GetCachedOrgName;
310
311 Procedure GetCachedOrgId (P_Inc_By_Per_Id IN NUMBER,
312 P_Exp_Item_Date IN DATE,
313 X_Inc_By_Org_Id OUT NOCOPY NUMBER)
314
315 Is
316
320 l_End_Date DATE;
317 l_Found BOOLEAN := FALSE;
318 l_Index BINARY_INTEGER;
319 l_Start_Date DATE;
321
322 Begin
323
324 -- Check if there are any records in the pl/sql table.
325 If G_OrgId_Tab.COUNT > 0 Then
326
327 -- Get the Inc by Org Id if it exist by looping thru the pl/sql table.
328 For i in G_OrgId_Tab.First .. G_OrgId_Tab.Last
329 Loop
330
331 If P_Inc_By_Per_Id = G_OrgId_Tab(i).Person_Id And
332 trunc(P_Exp_Item_Date) >= trunc(G_OrgId_Tab(i).Start_Date) And
333 trunc(P_Exp_Item_Date) <= trunc(nvl(G_OrgId_Tab(i).End_Date,P_Exp_Item_Date)) Then
334
335 X_Inc_By_Org_Id := G_OrgId_Tab(i).Org_Id;
336 l_Found := TRUE;
337 Exit;
338
339 End If;
340
341 End Loop;
342
343 End If;
344
345 If Not l_Found Then
346
347 -- Since the employee info has not been cached yet, will need to add it.
348 -- So check to see if there are already 200 records in the pl/sql table.
349 If G_OrgId_Tab.COUNT > 199 Then
350
351 G_OrgId_Tab.Delete;
352
353 End If;
354
355 -- Get the organization Id
356 Select
357 Organization_Id,
358 Effective_Start_Date,
359 Effective_End_Date
360 Into
361 X_Inc_By_Org_Id,
362 l_Start_Date,
363 l_End_Date
364 From
365 Per_Assignments_F
366 Where
367 Person_id = P_Inc_By_Per_Id
368 And Primary_Flag = 'Y'
369 And Organization_Id is not null
370 And Job_Id is not null
371 And Assignment_Type in ('E','C')
372 And P_Exp_Item_Date between Effective_Start_Date
373 and nvl(Effective_End_Date, P_Exp_Item_Date) ;
374
375 -- Find the next availabe index to use.
376 l_Index := G_OrgId_Tab.COUNT + 1;
377
378 -- Insert the employee info into the pl/sql table.
379 G_OrgId_Tab(l_Index).Person_Id := to_char(P_Inc_By_Per_Id);
380 G_OrgId_Tab(l_Index).Start_Date := l_Start_Date;
381 G_OrgId_Tab(l_Index).End_Date := l_End_Date;
382 G_OrgId_Tab(l_Index).Org_Id := X_Inc_By_Org_Id;
383
384 End If;
385
386 Exception
387 When Others Then
388 Raise;
389
390 End GetCachedOrgId;
391
392 Function GetCachedProjNum (P_Project_Id IN NUMBER) RETURN pa_projects_all.segment1%TYPE
393 Is
394 l_Project_Number pa_projects_all.segment1%TYPE;
395 Begin
396
397 If P_Project_Id > 0 THEN
398 GetCachedProjNum(P_Project_Id => P_Project_Id,
399 X_Project_Number => l_Project_Number);
400 END IF;
401
402 return l_Project_Number;
403
404 End GetCachedProjNum;
405
406 Function GetCachedTaskNum (P_Task_Id IN NUMBER) RETURN pa_tasks.task_number%TYPE
407 Is
408 l_Task_Number pa_tasks.task_number%TYPE;
409 Begin
410
411 If P_Task_Id > 0 THEN
412 GetCachedTaskNum(P_Task_Id => P_Task_Id,
413 X_Task_Number => l_Task_Number);
414 End If;
415
416 Return l_Task_Number;
417
418 End GetCachedTaskNum;
419
420 Function GetEmpNum (P_Person_Id IN NUMBER,P_ei_date IN DATE DEFAULT sysdate ) RETURN per_people_f.employee_number%TYPE --bug 9853319
421 Is
422 l_emp_Number Per_People_F.employee_number%TYPE;
423 l_Business_Group_Name Hr_Organization_Units.Name%TYPE;
424 Begin
425
426 If P_Person_Id > 0 THEN
427 GetCachedEmpInfo (P_Inc_By_Per_Id => P_Person_id,
428 P_Exp_Date => P_ei_date , --bug 9853319
429 X_Inc_By_Per_Number => l_emp_Number,
430 X_Business_Group_Name => l_Business_Group_Name);
431 End If;
432
433 Return l_emp_Number;
434
435 End GetEmpNum;
436
437 -- Function to get the project and Task details of the Ei.
438 -- In case of adjusted Ei, the function returns the Project/Task
439 -- info of the new EI(Transferred To). And for the new Ei, it
440 -- returns the Project/Task info of the adj Ei(Transferred From).
441
442 Function GetEiProjTask (P_exp_item_id IN NUMBER,
443 P_Net_Zero_Flag IN VARCHAR2,
444 P_Transferred_from_exp_id IN NUMBER)
445 RETURN VARCHAR2
446 Is
447 l_proj_task_info VARCHAR2(100);
448 Begin
449
450 IF (P_Net_Zero_Flag = 'Y')
451 OR ( P_Net_Zero_Flag = 'N' AND P_Transferred_from_exp_id >0) THEN
452 IF P_Transferred_from_exp_id >0 THEN
453 -- Transferred from project and Task Details.
454 BEGIN
455 SELECT proj.segment1||'/'||TASK.task_number
456 INTO l_proj_task_info
457 FROM pa_expenditure_items ei
458 ,pa_projects_all proj
459 ,pa_tasks task
460 WHERE ei.expenditure_item_id = P_Transferred_from_exp_id
461 AND ei.task_id = task.task_id
462 AND proj.project_id = task.project_id;
463 EXCEPTION
467 ELSE
464 WHEN OTHERS THEN
465 NULL;
466 END;
468 -- Transferred To project and Task Details.
469 BEGIN
470 SELECT proj.segment1||'/'||TASK.task_number
471 INTO l_proj_task_info
472 FROM pa_expenditure_items ei
473 ,pa_projects_all proj
474 ,pa_tasks task
475 WHERE ei.transferred_from_exp_item_id = P_exp_item_id
476 AND ei.task_id = task.task_id
477 AND proj.project_id = task.project_id;
478 EXCEPTION
479 WHEN OTHERS THEN
480 NULL;
481 END;
482 END IF;
483 END IF;
484 RETURN l_proj_task_info;
485 End GetEiProjTask;
486 /***************************************************************************
487 Procedure : get_asset_addition_flag
488 Purpose : When Expense Reports are sent to AP from PA,
489 the intermediate tables ap_expense_report_headers_all
490 and ap_expense_report_lines_all are populated. A Process
491 process in AP then populates the
492 Invoice Distribution tables. As there is no way in the
493 intermediate tables, to find out if the expense report is
494 associated with a 'Capital Project', which should not be
495 interfaced from AP to FA, unlike Invoice Distribution line
496 table, where asset_addition_flag is used. This API is to
497 find out if the given project_id is a 'CAPITAL' project
498 and if so, populate the 'out' vairable to 'P', else 'U'.
499 Arguments : p_project_id IN - project id
500 x_asset_addition_flag OUT - asset addition flag
501 ****************************************************************************/
502
503
504 PROCEDURE get_asset_addition_flag
505 (p_project_id IN pa_projects_all.project_id%TYPE,
506 x_asset_addition_flag OUT NOCOPY ap_invoice_distributions_all.assets_addition_flag%TYPE)
507 IS
508
509 l_project_type_class_code pa_project_types_all.project_type_class_code%TYPE;
510
511 BEGIN
512
513 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
514 SELECT ptype.project_type_class_code
515 INTO l_project_type_class_code
516 FROM pa_project_types_all ptype,
517 pa_projects_all proj
518 WHERE ptype.project_type = proj.project_type
519 AND NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
520 AND proj.project_id = p_project_id;
521
522 /* IF Project is CAPITAL then set asset_addition_flag to 'P' else 'U' */
523
524 IF (l_project_type_class_code = 'CAPITAL') THEN
525
526 x_asset_addition_flag := 'P';
527
528 ELSE
529
530 x_asset_addition_flag := 'U';
531
532 END IF;
533
534 EXCEPTION
535
536 WHEN OTHERS THEN
537 RAISE;
538
539 END get_asset_addition_flag;
540
541 /***************************************************************************
542 Function : Get_Project_Type
543 Purpose : This function will check if the project id passed to this
544 is a 'CAPITAL' Project.If it is then this will return
545 'P' otherwise 'U'
546 Arguments : p_project_id IN - project id
547 Returns 'P' if the project is Capital otherwise 'U'
548 ****************************************************************************/
549
550 FUNCTION Get_Project_Type
551 (p_project_id IN pa_projects_all.project_id%TYPE)RETURN VARCHAR2 IS
552 l_project_type VARCHAR2(1);
553
554 BEGIN
555
556 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
557
558 SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
559 INTO l_project_type
560 FROM pa_project_types_all ptype,
561 pa_projects_all proj
562 WHERE proj.project_type = ptype.project_type
563 /* AND NVL(ptype.org_id, -99) = NVL(proj.org_id, -99) commented and removed nvl to make use of the index also in R12, org is populated --Bug 5912873*/
564 AND ptype.org_id = proj.org_id
565 AND proj.project_id = p_project_id ;
566
567 RETURN l_project_type;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 RAISE;
572 END Get_Project_Type;
573
574
575 /* R12 Changes Start */
576 /***************************************************************************
577 Function : GetPastEmpNum
578 Purpose : This function will get Past Employee Number.
579 Arguments : P_Person_Id IN Person_Id
580 P_ei_date IN EXPENDITURE_ITEM_DATE DEFAULT sysdate
581 Returns Employee Number
582 ****************************************************************************/
583
584 FUNCTION GetPastEmpNum
585 ( P_Person_Id IN NUMBER,
586 P_ei_date IN DATE DEFAULT sysdate)
587 RETURN per_people_f.employee_number%TYPE IS
588
589 l_emp_Number Per_People_F.employee_number%TYPE;
590
591 BEGIN
592
593 IF P_Person_Id > 0 THEN
594 Select
595 MAX(P.Employee_Number)
596 Into
597 l_emp_Number
598 From
599 Per_All_People_F P,
600 Hr_Organization_Units O
601 Where
602 P.Person_Id = P_Person_Id
603 and O.Organization_Id = P.Business_Group_Id
604 and trunc(P_ei_date) > = trunc(p.effective_start_date)
605 AND P.Employee_Number IS NOT NULL;
606
607 END IF;
608
609 RETURN l_emp_Number ;
610
611 EXCEPTION
612 WHEN Others THEN
613 Raise;
614 END GetPastEmpNum;
615 /* R12 Changes End */
616
617 /***************************************************************************
618 Function : GetCachedOUName
619 Purpose : This function caches Operating Unit Identifier and names
620 in a PL/SQL table and retrieves the OU Name using the
621 Org ID passedas a input parameter.
622 Arguments : P_Org_ID - Organization Identifier
623 Return : Operating Unit Name
624 ***************************************************************************/
625 FUNCTION GetCachedOUName (P_Org_ID HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE)
626 RETURN HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE IS
627 l_OU_Name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
628
629 CURSOR C_GetOUName_Cur(P_Org_ID HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE) IS
630 SELECT Name
631 FROM HR_ALL_ORGANIZATION_UNITS_TL
632 WHERE Organization_ID = P_Org_ID
633 AND Language = USERENV('LANG');
634 BEGIN
635 IF G_OUName_Tab.COUNT > 0 THEN
636
637 FOR i IN G_OUName_Tab.FIRST..G_OUName_Tab.LAST LOOP
638
639 IF G_OUName_Tab(i).Org_ID = P_Org_ID THEN
640
641 l_OU_Name := G_OUName_Tab(i).OU_Name;
642 EXIT;
643
644 END IF;
645
646 END LOOP;
647
648 END IF;
649
650 IF l_OU_Name IS NULL THEN
651
652 OPEN C_GetOUName_Cur(P_Org_ID);
653 FETCH C_GetOUName_Cur INTO l_OU_Name;
654 CLOSE C_GetOUName_Cur;
655
656 G_OUName_Tab(NVL(G_OUName_Tab.LAST+1,0)).OU_Name := l_OU_Name;
657 G_OUName_Tab(NVL(G_OUName_Tab.LAST+1,0)).Org_ID := P_Org_ID;
658
659 END IF;
660
661 RETURN l_OU_Name;
662
663 END GetCachedOUName;
664
665 END pa_utils3;
[Home] [Help]