1 PACKAGE BODY PA_UTILS3 AS
2 /* $Header: PAXGUT3B.pls 120.7.12000000.2 2007/07/17 10:41:14 saraju 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 X_Inc_By_Per_Number OUT NOCOPY VARCHAR2,
164 X_Business_Group_Name OUT NOCOPY VARCHAR2)
165
166 Is
167
168 l_Found BOOLEAN := FALSE;
169
170 Begin
171
172 -- Check if there are any records in the pl/sql table.
173 If G_EmpInfo_Tab.COUNT > 0 Then
174
175 Begin
176
177 -- Get the Employee Number and business group from the pl/sql table.
178 -- If there is no index with the value of the inc by person_id passed
179 -- in then an ora-1403: no_data_found is generated.
180 X_Inc_By_Per_Number := G_EmpInfo_Tab(P_Inc_By_Per_Id).Employee_Number;
181 X_Business_Group_Name := G_EmpInfo_Tab(P_Inc_By_Per_Id).Business_Group_Name;
182 l_Found := TRUE;
183
184 Exception
185 When No_Data_Found Then
186 l_Found := FALSE;
187 When Others Then
188 Raise;
189
190 End;
191
192 End If;
193
194 If Not l_Found Then
195
196 -- Since the employee info has not been cached yet, will need to add it.
197 -- So check to see if there are already 200 records in the pl/sql table.
198 If G_EmpInfo_Tab.COUNT > 199 Then
199
200 G_EmpInfo_Tab.Delete;
201
202 End If;
203
204 -- Get the employee number/npw number and business group name
205 -- If the current_employee_flag is 'N' then we are dealing with contingent worker
206 -- and want to grab the npw_number inplace of the employee_number.
207 Select
208 Decode(P.Current_Employee_Flag,'Y',P.Employee_Number,P.Npw_Number),
209 O.Name
210 Into
211 X_Inc_By_Per_Number,
212 X_Business_Group_Name
213 From
214 --FP M To reduce dependency on HR, changing the per_people_x to per_all_people_f
215 --and added the date check
216 --Per_People_X P,
217 Per_All_People_F P,
218 Hr_Organization_Units O
219 Where
220 P.Person_Id = P_Inc_By_Per_Id
221 and O.Organization_Id = P.Business_Group_Id
222 and trunc(sysdate) between trunc(p.effective_start_date) and trunc(p.effective_end_date);
223
224 -- Add the employee info to the pl/sql table using the P_Inc_By_Per_Id
225 -- as the index value. This makes things fast.
226 G_EmpInfo_Tab(P_Inc_By_Per_Id).Employee_Number := X_Inc_By_Per_Number;
227 G_EmpInfo_Tab(P_Inc_By_Per_Id).Business_Group_Name := X_Business_Group_Name;
228
229 End If;
230
231 Exception
232 When Others Then
233 Raise;
234
235 End GetCachedEmpInfo;
236
237 Procedure GetCachedOrgName (P_Inc_By_Per_Id IN NUMBER,
238 P_Exp_Item_Date IN DATE,
239 X_Inc_By_Org_Name OUT NOCOPY VARCHAR2)
240
241 Is
242
243 l_Found BOOLEAN := FALSE;
244 l_Index BINARY_INTEGER;
245
246 Begin
247
248 -- Check if there are any records in the pl/sql table.
249 If G_OrgName_Tab.COUNT > 0 Then
250
251 -- Get the Inc by Org Name if it exist by looping thru the pl/sql table.
252 For i in G_OrgName_Tab.First .. G_OrgName_Tab.Last
253 Loop
254
255 If to_char(P_Inc_By_Per_Id) || to_char(P_Exp_Item_Date) = G_OrgName_Tab(i).PersonId_Date Then
256
257 X_Inc_By_Org_Name := G_OrgName_Tab(i).Org_Name;
258 l_Found := TRUE;
259 Exit;
260
261 End If;
262
263 End Loop;
264
265 End If;
266
267 If Not l_Found Then
268
269 -- Since the employee info has not been cached yet, will need to add it.
270 -- So check to see if there are already 200 records in the pl/sql table.
271 If G_OrgName_Tab.COUNT > 199 Then
272
273 G_OrgName_Tab.Delete;
274
275 End If;
276
277 -- Get the organization name
278 Select
279 O.Name
280 Into
281 X_Inc_By_Org_Name
282 From
283 Per_Assignments_F A,
284 Hr_All_Organization_Units O
285 Where
286 O.Organization_id = A.Organization_Id
287 And A.person_id = P_Inc_By_Per_Id
288 And A.Primary_Flag = 'Y'
289 And A.Organization_Id is not null
290 And A.Job_Id is not null
291 And A.Assignment_Type in ('E','C')
292 And P_Exp_Item_Date between A.Effective_Start_Date
293 and nvl(A.Effective_End_Date, P_Exp_Item_Date) ;
294
295 -- Find the next availabe index to use.
296 l_Index := G_OrgName_Tab.COUNT + 1;
297
298 -- Insert the employee info into the pl/sql table.
299 G_OrgName_Tab(l_Index).PersonId_Date := to_char(P_Inc_By_Per_Id) || to_char(P_Exp_Item_Date);
300 G_OrgName_Tab(l_Index).Org_Name := X_Inc_By_Org_Name;
301
302 End If;
303
304 Exception
305 When Others Then
306 Raise;
307
308 End GetCachedOrgName;
309
310 Procedure GetCachedOrgId (P_Inc_By_Per_Id IN NUMBER,
311 P_Exp_Item_Date IN DATE,
312 X_Inc_By_Org_Id OUT NOCOPY NUMBER)
313
314 Is
315
316 l_Found BOOLEAN := FALSE;
317 l_Index BINARY_INTEGER;
318 l_Start_Date DATE;
319 l_End_Date DATE;
320
321 Begin
322
323 -- Check if there are any records in the pl/sql table.
324 If G_OrgId_Tab.COUNT > 0 Then
325
326 -- Get the Inc by Org Id if it exist by looping thru the pl/sql table.
327 For i in G_OrgId_Tab.First .. G_OrgId_Tab.Last
328 Loop
329
330 If P_Inc_By_Per_Id = G_OrgId_Tab(i).Person_Id And
331 trunc(P_Exp_Item_Date) >= trunc(G_OrgId_Tab(i).Start_Date) And
332 trunc(P_Exp_Item_Date) <= trunc(nvl(G_OrgId_Tab(i).End_Date,P_Exp_Item_Date)) Then
333
334 X_Inc_By_Org_Id := G_OrgId_Tab(i).Org_Id;
335 l_Found := TRUE;
336 Exit;
337
338 End If;
339
340 End Loop;
341
342 End If;
343
344 If Not l_Found Then
345
346 -- Since the employee info has not been cached yet, will need to add it.
347 -- So check to see if there are already 200 records in the pl/sql table.
348 If G_OrgId_Tab.COUNT > 199 Then
349
350 G_OrgId_Tab.Delete;
351
352 End If;
353
354 -- Get the organization Id
355 Select
356 Organization_Id,
357 Effective_Start_Date,
358 Effective_End_Date
359 Into
360 X_Inc_By_Org_Id,
361 l_Start_Date,
362 l_End_Date
363 From
364 Per_Assignments_F
365 Where
366 Person_id = P_Inc_By_Per_Id
367 And Primary_Flag = 'Y'
368 And Organization_Id is not null
369 And Job_Id is not null
370 And Assignment_Type in ('E','C')
371 And P_Exp_Item_Date between Effective_Start_Date
372 and nvl(Effective_End_Date, P_Exp_Item_Date) ;
373
374 -- Find the next availabe index to use.
375 l_Index := G_OrgId_Tab.COUNT + 1;
376
377 -- Insert the employee info into the pl/sql table.
378 G_OrgId_Tab(l_Index).Person_Id := to_char(P_Inc_By_Per_Id);
379 G_OrgId_Tab(l_Index).Start_Date := l_Start_Date;
380 G_OrgId_Tab(l_Index).End_Date := l_End_Date;
381 G_OrgId_Tab(l_Index).Org_Id := X_Inc_By_Org_Id;
382
383 End If;
384
385 Exception
386 When Others Then
387 Raise;
388
389 End GetCachedOrgId;
390
391 Function GetCachedProjNum (P_Project_Id IN NUMBER) RETURN pa_projects_all.segment1%TYPE
392 Is
393 l_Project_Number pa_projects_all.segment1%TYPE;
394 Begin
395
396 If P_Project_Id > 0 THEN
397 GetCachedProjNum(P_Project_Id => P_Project_Id,
398 X_Project_Number => l_Project_Number);
399 END IF;
400
401 return l_Project_Number;
402
403 End GetCachedProjNum;
404
405 Function GetCachedTaskNum (P_Task_Id IN NUMBER) RETURN pa_tasks.task_number%TYPE
406 Is
407 l_Task_Number pa_tasks.task_number%TYPE;
408 Begin
409
410 If P_Task_Id > 0 THEN
411 GetCachedTaskNum(P_Task_Id => P_Task_Id,
415 Return l_Task_Number;
412 X_Task_Number => l_Task_Number);
413 End If;
414
416
417 End GetCachedTaskNum;
418
419 Function GetEmpNum (P_Person_Id IN NUMBER) RETURN per_people_f.employee_number%TYPE
420 Is
421 l_emp_Number Per_People_F.employee_number%TYPE;
422 l_Business_Group_Name Hr_Organization_Units.Name%TYPE;
423 Begin
424
425 If P_Person_Id > 0 THEN
426 GetCachedEmpInfo (P_Inc_By_Per_Id => P_Person_id,
427 X_Inc_By_Per_Number => l_emp_Number,
428 X_Business_Group_Name => l_Business_Group_Name);
429 End If;
430
431 Return l_emp_Number;
432
433 End GetEmpNum;
434
435 -- Function to get the project and Task details of the Ei.
436 -- In case of adjusted Ei, the function returns the Project/Task
437 -- info of the new EI(Transferred To). And for the new Ei, it
438 -- returns the Project/Task info of the adj Ei(Transferred From).
439
440 Function GetEiProjTask (P_exp_item_id IN NUMBER,
441 P_Net_Zero_Flag IN VARCHAR2,
442 P_Transferred_from_exp_id IN NUMBER)
443 RETURN VARCHAR2
444 Is
445 l_proj_task_info VARCHAR2(100);
446 Begin
447
448 IF (P_Net_Zero_Flag = 'Y')
449 OR ( P_Net_Zero_Flag = 'N' AND P_Transferred_from_exp_id >0) THEN
450 IF P_Transferred_from_exp_id >0 THEN
451 -- Transferred from project and Task Details.
452 BEGIN
453 SELECT proj.segment1||'/'||TASK.task_number
454 INTO l_proj_task_info
455 FROM pa_expenditure_items ei
456 ,pa_projects_all proj
457 ,pa_tasks task
458 WHERE ei.expenditure_item_id = P_Transferred_from_exp_id
459 AND ei.task_id = task.task_id
460 AND proj.project_id = task.project_id;
461 EXCEPTION
462 WHEN OTHERS THEN
463 NULL;
464 END;
465 ELSE
466 -- Transferred To project and Task Details.
467 BEGIN
468 SELECT proj.segment1||'/'||TASK.task_number
469 INTO l_proj_task_info
470 FROM pa_expenditure_items ei
471 ,pa_projects_all proj
472 ,pa_tasks task
473 WHERE ei.transferred_from_exp_item_id = P_exp_item_id
474 AND ei.task_id = task.task_id
475 AND proj.project_id = task.project_id;
476 EXCEPTION
477 WHEN OTHERS THEN
478 NULL;
479 END;
480 END IF;
481 END IF;
482 RETURN l_proj_task_info;
483 End GetEiProjTask;
484 /***************************************************************************
485 Procedure : get_asset_addition_flag
486 Purpose : When Expense Reports are sent to AP from PA,
487 the intermediate tables ap_expense_report_headers_all
488 and ap_expense_report_lines_all are populated. A Process
489 process in AP then populates the
490 Invoice Distribution tables. As there is no way in the
491 intermediate tables, to find out if the expense report is
492 associated with a 'Capital Project', which should not be
493 interfaced from AP to FA, unlike Invoice Distribution line
494 table, where asset_addition_flag is used. This API is to
495 find out if the given project_id is a 'CAPITAL' project
496 and if so, populate the 'out' vairable to 'P', else 'U'.
497 Arguments : p_project_id IN - project id
498 x_asset_addition_flag OUT - asset addition flag
499 ****************************************************************************/
500
501
502 PROCEDURE get_asset_addition_flag
503 (p_project_id IN pa_projects_all.project_id%TYPE,
504 x_asset_addition_flag OUT NOCOPY ap_invoice_distributions_all.assets_addition_flag%TYPE)
505 IS
506
507 l_project_type_class_code pa_project_types_all.project_type_class_code%TYPE;
508
509 BEGIN
510
511 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
512 SELECT ptype.project_type_class_code
513 INTO l_project_type_class_code
514 FROM pa_project_types_all ptype,
515 pa_projects_all proj
516 WHERE ptype.project_type = proj.project_type
517 AND NVL(ptype.org_id, -99) = NVL(proj.org_id, -99)
518 AND proj.project_id = p_project_id;
519
520 /* IF Project is CAPITAL then set asset_addition_flag to 'P' else 'U' */
521
522 IF (l_project_type_class_code = 'CAPITAL') THEN
523
524 x_asset_addition_flag := 'P';
525
526 ELSE
527
528 x_asset_addition_flag := 'U';
529
530 END IF;
531
532 EXCEPTION
533
534 WHEN OTHERS THEN
535 RAISE;
536
537 END get_asset_addition_flag;
538
539 /***************************************************************************
540 Function : Get_Project_Type
544 Arguments : p_project_id IN - project id
541 Purpose : This function will check if the project id passed to this
542 is a 'CAPITAL' Project.If it is then this will return
543 'P' otherwise 'U'
545 Returns 'P' if the project is Capital otherwise 'U'
546 ****************************************************************************/
547
548 FUNCTION Get_Project_Type
549 (p_project_id IN pa_projects_all.project_id%TYPE)RETURN VARCHAR2 IS
550 l_project_type VARCHAR2(1);
551
552 BEGIN
553
554 /* For Given Project Id, Get the Project_Type_Class_Code depending on the Project_Type */
555
556 SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
557 INTO l_project_type
558 FROM pa_project_types_all ptype,
559 pa_projects_all proj
560 WHERE proj.project_type = ptype.project_type
561 /* 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*/
562 AND ptype.org_id = proj.org_id
563 AND proj.project_id = p_project_id ;
564
565 RETURN l_project_type;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 RAISE;
570 END Get_Project_Type;
571
572 /***************************************************************************
573 Function : GetCachedOUName
574 Purpose : This function caches Operating Unit Identifier and names
575 in a PL/SQL table and retrieves the OU Name using the
576 Org ID passedas a input parameter.
577 Arguments : P_Org_ID - Organization Identifier
578 Return : Operating Unit Name
579 ***************************************************************************/
580 FUNCTION GetCachedOUName (P_Org_ID HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE)
581 RETURN HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE IS
582 l_OU_Name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
583
584 CURSOR C_GetOUName_Cur(P_Org_ID HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID%TYPE) IS
585 SELECT Name
586 FROM HR_ALL_ORGANIZATION_UNITS_TL
587 WHERE Organization_ID = P_Org_ID
588 AND Language = USERENV('LANG');
589 BEGIN
590 IF G_OUName_Tab.COUNT > 0 THEN
591
592 FOR i IN G_OUName_Tab.FIRST..G_OUName_Tab.LAST LOOP
593
594 IF G_OUName_Tab(i).Org_ID = P_Org_ID THEN
595
596 l_OU_Name := G_OUName_Tab(i).OU_Name;
597 EXIT;
598
599 END IF;
600
601 END LOOP;
602
603 END IF;
604
605 IF l_OU_Name IS NULL THEN
606
607 OPEN C_GetOUName_Cur(P_Org_ID);
608 FETCH C_GetOUName_Cur INTO l_OU_Name;
609 CLOSE C_GetOUName_Cur;
610
611 G_OUName_Tab(NVL(G_OUName_Tab.LAST+1,0)).OU_Name := l_OU_Name;
612 G_OUName_Tab(NVL(G_OUName_Tab.LAST+1,0)).Org_ID := P_Org_ID;
613
614 END IF;
615
616 RETURN l_OU_Name;
617
618 END GetCachedOUName;
619
620 END pa_utils3;
[Home] [Help]