[Home] [Help]
PACKAGE BODY: APPS.PA_EXPENDITURES_UTILS
Source
1 PACKAGE BODY PA_EXPENDITURES_UTILS AS
2 /* $Header: PAXEXUTB.pls 120.3.12010000.2 2010/02/05 20:02:20 djanaswa ship $ */
3
4 G_Organization_id NUMBER(15);
5 G_Organization_id_1 NUMBER(15);
6 /* Bug No. 2487147 ; Change done for UTF8:- Changed G_Organization_Name from VARCHAR2(60) to %TYPE */
7 /* G_Organization_Name VARCHAR2(60); */
8 G_Organization_Name hr_all_organization_units_tl.name%TYPE;
9 G_Organization_Name_1 hr_all_organization_units_tl.name%TYPE;
10 /* Bug 3637411 : Added the following variable for buffering the Organisation Name
11 in the Base Language */
12 G_Organization_Name_US hr_all_organization_units.name%TYPE;
13 G_Job_Id NUMBER(15);
14 G_Job_Name VARCHAR2(240);
15
16 FUNCTION GetOrgTlName ( P_Organization_Id IN NUMBER ) RETURN VARCHAR2 IS
17 x_org hr_all_organization_units_tl.name%TYPE;
18
19 BEGIN
20 If P_Organization_Id is NULL THEN
21 RETURN ( NULL );
22 End If;
23 /*
24 If G_Organization_Name is null OR
25 P_Organization_Id <> G_Organization_id Then
26 select tl.name
27 into G_Organization_Name
28 from hr_org_units_no_join o,
29 hr_all_organization_units_tl tl
30 where
31 o.organization_id(+) = P_Organization_Id
32 and o.organization_id = tl.organization_id(+)
33 and ( ( tl.organization_id is null and
34 1 = 1)
35 or ( tl.organization_id is not null and
36 tl.language = userenv('LANG'))) ;
37
38 --Bug 1777404. Got rid of decode due to performance team request
39 -- and decode(tl.organization_id,null,'1',tl.language) =
40 -- decode(tl.organization_id,null,'1',userenv('LANG')) ;
41 --
42
43 If G_Organization_Name IS NOT NULL Then
44 G_Organization_id := P_Organization_Id;
45
46 End If;
47 End If;
48 */
49 -- Fix for bug : 4005004
50 If G_Organization_id IS NULL then
51 select tl.name
52 into G_Organization_Name
53 from hr_org_units_no_join o,
54 hr_all_organization_units_tl tl
55 where
56 o.organization_id(+) = P_Organization_Id
57 and o.organization_id = tl.organization_id(+)
58 and ( ( tl.organization_id is null and
59 1 = 1)
60 or ( tl.organization_id is not null and
61 tl.language = userenv('LANG'))) ;
62
63 G_Organization_id := P_Organization_Id;
64 x_org := G_Organization_Name ;
65
66 elsif G_Organization_id <> P_Organization_Id then
67 if NVL(G_Organization_id_1, 0) <> P_Organization_Id then
68 select tl.name
69 into G_Organization_Name_1
70 from hr_org_units_no_join o,
71 hr_all_organization_units_tl tl
72 where
73 o.organization_id(+) = P_Organization_Id
74 and o.organization_id = tl.organization_id(+)
75 and ( ( tl.organization_id is null and
76 1 = 1)
77 or ( tl.organization_id is not null and
78 tl.language = userenv('LANG'))) ;
79
80 G_Organization_id_1 := P_Organization_Id;
81 x_org := G_Organization_Name_1 ;
82
83 else
84 x_org := G_Organization_Name_1 ; -- G_Organization_id_1 = P_Organization_Id
85
86 end if; -- end if for NVL(G_Organization_id_1, 0) <> P_Organization_Id
87 else -- G_Organization_id = P_Organization_Id
88
89 x_org := G_Organization_Name ;
90
91 end if ;
92
93 -- return ( G_Organization_Name );
94 return ( x_org );
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 RAISE;
99
100 End GetOrgTlName;
101
102 /* Added the following function to get the Organization Name in the
103 Base Language */
104 FUNCTION GetOrgName ( P_Organization_Id IN NUMBER ) RETURN VARCHAR2
105
106 IS
107
108 BEGIN
109 If P_Organization_Id is NULL THEN
110 RETURN ( NULL );
111 End If;
112
113 If G_Organization_Name_US is null
114 OR P_Organization_Id <> NVL(G_Organization_id,-99) Then /* Added nvl() for bug 4240184*/
115 select name
116 into G_Organization_Name_US
117 from hr_org_units_no_join
118 where organization_id = P_Organization_Id;
119 G_Organization_id := P_Organization_Id; /* Added for bug 4240184*/
120 End If;
121
122 return ( G_Organization_Name_US );
123
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 RETURN (NULL);
127 WHEN OTHERS THEN
128 RAISE;
129
130 End GetOrgName;
131
132 /* New function GET_ORG_NAME added for Bug 6450225 Start */
133 FUNCTION GET_ORG_NAME ( P_ORG_ID IN NUMBER , P_ORG_CTL IN VARCHAR ) RETURN VARCHAR2
134 IS
135 X_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE ;
136
137 BEGIN
138 IF P_ORG_ID = NULL THEN
139 RETURN(NULL);
140 END IF;
141
142 IF ( P_ORG_CTL = 'CC_PRVDR') THEN
143 IF ( P_ORG_ID = PREV_CC_PRVDR_ORG_ID ) THEN
144 X_ORG_NAME := PREV_CC_PRVDR_ORG_NAME;
145 ELSE
146 PREV_CC_PRVDR_ORG_ID := P_ORG_ID ;
147 SELECT
148 TL.NAME
149 INTO
150 X_ORG_NAME
151 FROM
152 HR_ORG_UNITS_NO_JOIN O,
153 HR_ALL_ORGANIZATION_UNITS_TL TL
154 WHERE
155 O.ORGANIZATION_ID(+) = P_ORG_ID
156 AND
157 O.ORGANIZATION_ID = TL.ORGANIZATION_ID(+)
158 AND
159 ( ( TL.ORGANIZATION_ID IS NULL AND 1 = 1)
160 OR
161 ( TL.ORGANIZATION_ID IS NOT NULL AND TL.LANGUAGE = USERENV('LANG'))) ;
162 PREV_CC_PRVDR_ORG_NAME := X_ORG_NAME ;
163 END IF;
164
165 ELSIF ( P_ORG_CTL = 'CC_RECVR') THEN
166 IF ( P_ORG_ID = PREV_CC_RECVR_ORG_ID ) THEN
167 X_ORG_NAME := PREV_CC_RECVR_ORG_NAME;
168 ELSE
169 PREV_CC_RECVR_ORG_ID := P_ORG_ID ;
170 SELECT
171 TL.NAME
172 INTO
173 X_ORG_NAME
174 FROM
175 HR_ORG_UNITS_NO_JOIN O,
176 HR_ALL_ORGANIZATION_UNITS_TL TL
177 WHERE
178 O.ORGANIZATION_ID(+) = P_ORG_ID
179 AND
180 O.ORGANIZATION_ID = TL.ORGANIZATION_ID(+)
181 AND
182 ( ( TL.ORGANIZATION_ID IS NULL AND 1 = 1)
183 OR
184 ( TL.ORGANIZATION_ID IS NOT NULL AND TL.LANGUAGE = USERENV('LANG'))) ;
185 PREV_CC_RECVR_ORG_NAME := X_ORG_NAME ;
186 END IF;
187
188 ELSIF ( P_ORG_CTL = 'PRVDR') THEN
189 IF ( P_ORG_ID = PREV_PRVDR_ORG_ID ) THEN
190 X_ORG_NAME := PREV_PRVDR_ORG_NAME;
191 ELSE
192 PREV_PRVDR_ORG_ID := P_ORG_ID ;
193 SELECT
194 TL.NAME
195 INTO
196 X_ORG_NAME
197 FROM
198 HR_ORG_UNITS_NO_JOIN O,
199 HR_ALL_ORGANIZATION_UNITS_TL TL
200 WHERE
201 O.ORGANIZATION_ID(+) = P_ORG_ID
202 AND
203 O.ORGANIZATION_ID = TL.ORGANIZATION_ID(+)
204 AND
205 ( ( TL.ORGANIZATION_ID IS NULL AND 1 = 1)
206 OR
207 ( TL.ORGANIZATION_ID IS NOT NULL AND TL.LANGUAGE = USERENV('LANG'))) ;
208 PREV_PRVDR_ORG_NAME := X_ORG_NAME ;
209 END IF;
210
211 ELSIF ( P_ORG_CTL = 'RECVR') THEN
212 IF ( P_ORG_ID = PREV_RECVR_ORG_ID ) THEN
213 X_ORG_NAME := PREV_RECVR_ORG_NAME;
214 ELSE
215 PREV_RECVR_ORG_ID := P_ORG_ID ;
216 SELECT
217 TL.NAME
218 INTO
219 X_ORG_NAME
220 FROM
221 HR_ORG_UNITS_NO_JOIN O,
222 HR_ALL_ORGANIZATION_UNITS_TL TL
223 WHERE
224 O.ORGANIZATION_ID(+) = P_ORG_ID
225 AND
226 O.ORGANIZATION_ID = TL.ORGANIZATION_ID(+)
227 AND
228 ( ( TL.ORGANIZATION_ID IS NULL AND 1 = 1)
229 OR
230 ( TL.ORGANIZATION_ID IS NOT NULL AND TL.LANGUAGE = USERENV('LANG'))) ;
231 PREV_RECVR_ORG_NAME := X_ORG_NAME ;
232 END IF;
233
234 ELSIF ( P_ORG_CTL = 'NLR') THEN
235 IF ( P_ORG_ID = PREV_NLR_ORG_ID ) THEN
236 X_ORG_NAME := PREV_NLR_ORG_NAME;
237 ELSE
238 PREV_NLR_ORG_ID := P_ORG_ID ;
239 SELECT
240 TL.NAME
241 INTO
242 X_ORG_NAME
243 FROM
244 HR_ORG_UNITS_NO_JOIN O,
245 HR_ALL_ORGANIZATION_UNITS_TL TL
246 WHERE
247 O.ORGANIZATION_ID(+) = P_ORG_ID
248 AND
249 O.ORGANIZATION_ID = TL.ORGANIZATION_ID(+)
250 AND
251 ( ( TL.ORGANIZATION_ID IS NULL AND 1 = 1)
252 OR
253 ( TL.ORGANIZATION_ID IS NOT NULL AND TL.LANGUAGE = USERENV('LANG'))) ;
254 PREV_NLR_ORG_NAME := X_ORG_NAME ;
255 END IF;
256 END IF;
257
258 RETURN (X_ORG_NAME);
259
260 EXCEPTION
261
262 WHEN OTHERS THEN
263 PREV_CC_PRVDR_ORG_NAME := NULL;
264 PREV_CC_RECVR_ORG_NAME := NULL;
265 PREV_PRVDR_ORG_NAME := NULL;
266 PREV_RECVR_ORG_NAME := NULL;
267 PREV_NLR_ORG_NAME := NULL;
268 RAISE ;
269
270 END GET_ORG_NAME;
271 /* New function GET_ORG_NAME added for Bug 6450225 End */
272 FUNCTION GetJobName ( P_Job_Id IN NUMBER ) RETURN VARCHAR2
273
274 IS
275
276 BEGIN
277
278 If P_Job_id is NULL THEN
279 return ( NULL );
280 End If;
281 If G_Job_Name is null OR
282 P_Job_Id <> G_Job_Id Then
283
284 select name
285 into G_job_name
286 from per_jobs
287 where job_id = p_job_id;
288
289 If G_Job_Name is not null then
290 G_job_id := p_job_id;
291 End If;
292
293 End If;
294
295 return ( G_Job_Name );
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 RAISE;
300
301 END GetJobName;
302
303 --------------------------------------------------------------
304
305 -- PROCEDURE
306 -- Check_Expenditure_Type
307 -- PURPOSE
308 -- This procedure validates if the given
309 -- Expenditure Type is valid on the given date.
310 -- HISTORY
311 -- 21-NOV-2000 P. Bandla Created
312 PROCEDURE Check_Expenditure_Type(
313 p_expenditure_type IN VARCHAR2,
314 p_date IN DATE,
315 x_valid OUT NOCOPY VARCHAR2,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_error_message_code OUT NOCOPY VARCHAR2) IS
318
319 BEGIN
320
321 IF (PA_UTILS.CheckExpTypeActive(X_expenditure_type =>p_expenditure_type, X_date => p_date )) THEN
322
323 x_valid := 'Y';
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 ELSE
327
328 x_valid := 'N';
329 x_return_status := FND_API.G_RET_STS_ERROR;
330 x_error_message_code := 'PA_EXPTYPE_INVALID';
331
332 END IF;
333
334 EXCEPTION
335 --WHEN NO_DATA_FOUND THEN
336 --x_return_status := FND_API.G_RET_STS_ERROR;
337 --x_error_message_code := 'PA_EXPTYPE_INVALID';
338 WHEN OTHERS THEN
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
340 x_error_message_code := NULL;
341 x_valid := 'N';
342
343 END Check_Expenditure_Type;
344
345 -- PROCEDURE
346 -- Check_Exp_Type_Class_Code
347 -- PURPOSE
348 -- This procedure does the following
349 -- If meaning is passed converts it to the id
350 -- If code is passed,
351 -- based on the check_id_flag validates it
352 -- HISTORY
353 -- 21-NOV-2000 P. Bandla Created
354 PROCEDURE Check_Exp_Type_Class_Code(
355 p_sys_link_func IN VARCHAR2,
356 p_exp_meaning IN VARCHAR2,
357 p_check_id_flag IN VARCHAR2,
358 x_sys_link_func OUT NOCOPY VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_error_message_code OUT NOCOPY VARCHAR2 )
361 IS
362
363 BEGIN
364
365 IF p_sys_link_func IS NOT NULL THEN
366
367 IF p_check_id_flag = 'Y' THEN
368
369 SELECT function
370 INTO x_sys_link_func
371 FROM pa_system_linkages
372 WHERE function = p_sys_link_func;
373
374 ELSE
375
376 x_sys_link_func := p_sys_link_func;
377
378 END IF;
379
380 ELSE
381
382 SELECT function
383 INTO x_sys_link_func
384 FROM pa_system_linkages
385 WHERE meaning = p_exp_meaning;
386
387 END IF;
388
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 x_error_message_code := 'PA_EXPCODE_INVALID';
395 x_sys_link_func := Null;
396 WHEN TOO_MANY_ROWS THEN
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 x_error_message_code := 'PA_EXPCODE_INVALID';
399 x_sys_link_func := Null;
400 WHEN OTHERS THEN
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 x_error_message_code := Null;
403 x_sys_link_func := Null;
404
405 END Check_Exp_Type_Class_Code;
406
407 -- PROCEDURE
408 -- Check_Exp_Type_Sys_Link_Combo
409 -- PURPOSE
410 -- This procedure validates the combination
411 -- of expenditure type and system linkage function
412 -- HISTORY
413 -- 21-NOV-2000 P. Bandla Created
414 PROCEDURE Check_Exp_Type_Sys_Link_Combo(
415 p_exp_type IN VARCHAR2,
416 p_ei_date IN DATE,
417 p_sys_link_func IN VARCHAR2,
418 x_valid OUT NOCOPY VARCHAR2,
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_error_message_code OUT NOCOPY VARCHAR2)
421 IS
422 l_dummy NUMBER DEFAULT 0;
423
424 BEGIN
425
426 IF (p_sys_link_func NOT IN ('OT', 'ST')) THEN
427
428 x_return_status := FND_API.G_RET_STS_ERROR;
429 x_error_message_code := 'PA_SYSLINK_NOT_OTST';
430 return;
431
432 ELSE
433
434 select count(*)
435 into l_dummy
436 from pa_expenditure_types_expend_v
437 where p_ei_date between expnd_typ_start_date_active
438 and nvl(expnd_typ_end_date_active,p_ei_date)
439 and p_ei_date between SYS_LINK_START_DATE_ACTIVE
440 and nvl(sys_link_end_date_active,p_ei_date)
441 and system_linkage_function = p_sys_link_func
442 and expenditure_type = p_exp_type;
443
444 END IF;
445
446 IF (l_dummy = 0) THEN
447 x_valid := 'N';
448 ELSE
449 x_valid := 'Y';
450 END IF;
451
452 x_return_status := FND_API.G_RET_STS_SUCCESS;
453
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 x_valid := 'N';
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 x_error_message_code := 'PA_EXPTYPE_SYSLINK_INVALID';
459 WHEN OTHERS THEN
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
461 x_error_message_code := Null;
462 x_valid := 'N';
463
464 END Check_Exp_Type_Sys_Link_Combo;
465 /* New function GET_LATEST_DATE_PERIOD_NAME added for Bug 6450225 Start */
466 FUNCTION GET_LATEST_DATE_PERIOD_NAME ( P_EXP_ITEM_ID IN NUMBER,
467 P_FUN_CTL IN VARCHAR ) RETURN VARCHAR2
468
469 IS
470
471 BEGIN
472
473 IF P_EXP_ITEM_ID = NVL(G_EXP_ITEM_ID,-999) THEN
474
475 IF P_FUN_CTL = 'GL_DATE' THEN
476 RETURN TO_CHAR(G_GL_DATE);
477 ELSIF P_FUN_CTL = 'PA_DATE' THEN
478 RETURN TO_CHAR(G_PA_DATE);
479 ELSIF P_FUN_CTL = 'RECVR_PA_DATE' THEN
480 RETURN TO_CHAR(G_RECVR_PA_DATE);
481 ELSIF P_FUN_CTL = 'RECVR_GL_DATE' THEN
482 RETURN TO_CHAR(G_RECVR_GL_DATE);
483 ELSIF P_FUN_CTL = 'PA_PERIOD_NAME' THEN
484 RETURN G_PA_PERIOD_NAME;
485 ELSIF P_FUN_CTL = 'GL_PERIOD_NAME' THEN
486 RETURN G_GL_PERIOD_NAME;
487 ELSIF P_FUN_CTL = 'RECVR_PA_PERIOD_NAME' THEN
488 RETURN G_RECVR_PA_PERIOD_NAME;
489 ELSIF P_FUN_CTL = 'RECVR_GL_PERIOD_NAME' THEN
490 RETURN G_RECVR_GL_PERIOD_NAME;
491 END IF;
492
493 ELSE
494
495 SELECT PA_DATE,
496 GL_DATE,
497 RECVR_PA_DATE,
498 RECVR_GL_DATE,
499 PA_PERIOD_NAME,
500 GL_PERIOD_NAME,
501 RECVR_PA_PERIOD_NAME,
502 RECVR_GL_PERIOD_NAME
503 INTO
504 G_PA_DATE,
505 G_GL_DATE,
506 G_RECVR_PA_DATE,
507 G_RECVR_GL_DATE,
508 G_PA_PERIOD_NAME,
509 G_GL_PERIOD_NAME,
510 G_RECVR_PA_PERIOD_NAME,
511 G_RECVR_GL_PERIOD_NAME
512 FROM PA_COST_DISTRIBUTION_LINES_ALL
513 WHERE EXPENDITURE_ITEM_ID = P_EXP_ITEM_ID
514 AND LINE_TYPE = 'R'
515 AND LINE_NUM_REVERSED IS NULL
516 AND REVERSED_FLAG IS NULL ;
517
518 G_EXP_ITEM_ID := P_EXP_ITEM_ID;
519
520 END IF;
521
522 IF P_FUN_CTL = 'GL_DATE' THEN
523 RETURN TO_CHAR(G_GL_DATE);
524 ELSIF P_FUN_CTL = 'PA_DATE' THEN
525 RETURN TO_CHAR(G_PA_DATE);
526 ELSIF P_FUN_CTL = 'RECVR_PA_DATE' THEN
527 RETURN TO_CHAR(G_RECVR_PA_DATE);
528 ELSIF P_FUN_CTL = 'RECVR_GL_DATE' THEN
529 RETURN TO_CHAR(G_RECVR_GL_DATE);
530 ELSIF P_FUN_CTL = 'PA_PERIOD_NAME' THEN
531 RETURN G_PA_PERIOD_NAME;
532 ELSIF P_FUN_CTL = 'GL_PERIOD_NAME' THEN
533 RETURN G_GL_PERIOD_NAME;
534 ELSIF P_FUN_CTL = 'RECVR_PA_PERIOD_NAME' THEN
535 RETURN G_RECVR_PA_PERIOD_NAME;
536 ELSIF P_FUN_CTL = 'RECVR_GL_PERIOD_NAME' THEN
537 RETURN G_RECVR_GL_PERIOD_NAME;
538 END IF;
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 G_EXP_ITEM_ID := P_EXP_ITEM_ID;
543 G_PA_DATE := NULL;
544 G_GL_DATE := NULL;
545 G_RECVR_PA_DATE := NULL;
546 G_RECVR_GL_DATE := NULL;
547 G_PA_PERIOD_NAME := NULL;
548 G_GL_PERIOD_NAME := NULL;
549 G_RECVR_PA_PERIOD_NAME := NULL;
550 G_RECVR_GL_PERIOD_NAME := NULL;
551 RETURN (NULL);
552
553 END GET_LATEST_DATE_PERIOD_NAME;
554 /* New function GET_LATEST_DATE_PERIOD_NAME added for Bug 6450225 End */
555
556 Function Get_Latest_GL_Date(P_Exp_Item_Id IN NUMBER) return DATE is
557
558 Begin
559
560 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
561
562 Return G_GL_Date;
563
564 Else
565
566 Select pa_date,
567 gl_date,
568 recvr_pa_date,
569 recvr_gl_date,
570 pa_period_name,
571 gl_period_name,
572 recvr_pa_period_name,
573 recvr_gl_period_name
574 Into
575 g_pa_date,
576 g_gl_date,
577 g_recvr_pa_date,
578 g_recvr_gl_date,
579 g_pa_period_name,
580 g_gl_period_name,
581 g_recvr_pa_period_name,
582 g_recvr_Gl_period_name
583 From pa_cost_distribution_lines_all
584 Where expenditure_item_id = P_Exp_Item_Id
585 And line_type = 'R'
586 And line_num = (
587 Select max(line_num)
588 From pa_cost_distribution_lines_all
589 Where expenditure_item_id = P_Exp_Item_Id
590 And line_type = 'R');
591
592 G_exp_item_id := P_Exp_item_Id;
593
594 End If;
595
596 Return G_GL_Date;
597
598 Exception
599 When OTHERS Then
600 G_exp_item_id := P_Exp_item_Id;
601 G_Pa_Date := Null;
602 G_Gl_Date := Null;
603 G_Recvr_Pa_Date := Null;
604 G_Recvr_Gl_Date := Null;
605 G_Pa_Period_Name := Null;
606 G_Gl_Period_Name := Null;
607 G_Recvr_Pa_Period_Name := Null;
608 G_Recvr_Gl_Period_Name := Null;
609 Return (NULL);
610
611 End Get_Latest_GL_Date;
612
613 Function Get_Latest_PA_Date(P_Exp_Item_Id IN NUMBER) return DATE is
614
615 Begin
616
617 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
618
619 Return G_Pa_Date;
620
621 Else
622
623 Select pa_date,
624 gl_date,
625 recvr_pa_date,
626 recvr_gl_date,
627 pa_period_name,
628 gl_period_name,
629 recvr_pa_period_name,
630 recvr_gl_period_name
631 Into
632 g_pa_date,
633 g_gl_date,
634 g_recvr_pa_date,
635 g_recvr_gl_date,
636 g_pa_period_name,
637 g_gl_period_name,
638 g_recvr_pa_period_name,
639 g_recvr_Gl_period_name
640 From pa_cost_distribution_lines_all
641 Where expenditure_item_id = P_Exp_Item_Id
642 And line_type = 'R'
643 And line_num = (
644 Select max(line_num)
645 From pa_cost_distribution_lines_all
646 Where expenditure_item_id = P_Exp_Item_Id
647 And line_type = 'R');
648
649 G_exp_item_id := P_Exp_item_Id;
650
651 End If;
652
653 Return G_PA_Date;
654
655 Exception
656 When OTHERS Then
657 G_exp_item_id := P_Exp_item_Id;
658 G_Pa_Date := Null;
659 G_Gl_Date := Null;
660 G_Recvr_Pa_Date := Null;
661 G_Recvr_Gl_Date := Null;
662 G_Pa_Period_Name := Null;
663 G_Gl_Period_Name := Null;
664 G_Recvr_Pa_Period_Name := Null;
665 G_Recvr_Gl_Period_Name := Null;
666 Return (NULL);
667
668 End Get_Latest_PA_Date;
669
670 Function Get_Latest_Recvr_Pa_Date(P_Exp_Item_Id IN NUMBER) return DATE is
671
672 Begin
673
674 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
675
676 Return G_Recvr_Pa_Date;
677
678 Else
679
680 Select pa_date,
681 gl_date,
682 recvr_pa_date,
683 recvr_gl_date,
684 pa_period_name,
685 gl_period_name,
686 recvr_pa_period_name,
687 recvr_gl_period_name
688 Into
689 g_pa_date,
690 g_gl_date,
691 g_recvr_pa_date,
692 g_recvr_gl_date,
693 g_pa_period_name,
694 g_gl_period_name,
695 g_recvr_pa_period_name,
696 g_recvr_Gl_period_name
697 From pa_cost_distribution_lines_all
698 Where expenditure_item_id = P_Exp_Item_Id
699 And line_type = 'R'
700 And line_num = (
701 Select max(line_num)
702 From pa_cost_distribution_lines_all
703 Where expenditure_item_id = P_Exp_Item_Id
704 And line_type = 'R');
705
706 G_exp_item_id := P_Exp_item_Id;
707
708 End If;
709
710 Return G_Recvr_Pa_Date;
711
712 Exception
713 When OTHERS Then
714 G_exp_item_id := P_Exp_item_Id;
715 G_Pa_Date := Null;
716 G_Gl_Date := Null;
717 G_Recvr_Pa_Date := Null;
718 G_Recvr_Gl_Date := Null;
719 G_Pa_Period_Name := Null;
720 G_Gl_Period_Name := Null;
721 G_Recvr_Pa_Period_Name := Null;
722 G_Recvr_Gl_Period_Name := Null;
723 Return (NULL);
724
725 End Get_Latest_Recvr_Pa_Date;
726
727
728 Function Get_Latest_Recvr_Gl_Date(P_Exp_Item_Id IN NUMBER) return DATE is
729
730 Begin
731
732 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
733
734 Return G_Recvr_Gl_Date;
735
736 Else
737
738 Select pa_date,
739 gl_date,
740 recvr_pa_date,
741 recvr_gl_date,
742 pa_period_name,
743 gl_period_name,
744 recvr_pa_period_name,
745 recvr_gl_period_name
746 Into
747 g_pa_date,
748 g_gl_date,
749 g_recvr_pa_date,
750 g_recvr_gl_date,
751 g_pa_period_name,
752 g_gl_period_name,
753 g_recvr_pa_period_name,
754 g_recvr_Gl_period_name
755 From pa_cost_distribution_lines_all
756 Where expenditure_item_id = P_Exp_Item_Id
757 And line_type = 'R'
758 And line_num = (
759 Select max(line_num)
760 From pa_cost_distribution_lines_all
761 Where expenditure_item_id = P_Exp_Item_Id
762 And line_type = 'R');
763
764 G_exp_item_id := P_Exp_item_Id;
765
766 End If;
767
768 Return G_Recvr_Gl_Date;
769
770 Exception
771 When OTHERS Then
772 G_exp_item_id := P_Exp_item_Id;
773 G_Pa_Date := Null;
774 G_Gl_Date := Null;
775 G_Recvr_Pa_Date := Null;
776 G_Recvr_Gl_Date := Null;
777 G_Pa_Period_Name := Null;
778 G_Gl_Period_Name := Null;
779 G_Recvr_Pa_Period_Name := Null;
780 G_Recvr_Gl_Period_Name := Null;
781 Return (NULL);
782
783 End Get_Latest_Recvr_Gl_Date;
784
785
786 Function Get_Latest_Pa_Per_Name(P_Exp_Item_Id IN NUMBER) return VARCHAR2 is
787
788 Begin
789
790 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
791
792 Return G_Pa_Period_Name;
793
794 Else
795
796 Select pa_date,
797 gl_date,
798 recvr_pa_date,
799 recvr_gl_date,
800 pa_period_name,
801 gl_period_name,
802 recvr_pa_period_name,
803 recvr_gl_period_name
804 Into
805 g_pa_date,
806 g_gl_date,
807 g_recvr_pa_date,
808 g_recvr_gl_date,
809 g_pa_period_name,
810 g_gl_period_name,
811 g_recvr_pa_period_name,
812 g_recvr_Gl_period_name
813 From pa_cost_distribution_lines_all
814 Where expenditure_item_id = P_Exp_Item_Id
815 And line_type = 'R'
816 And line_num = (
817 Select max(line_num)
818 From pa_cost_distribution_lines_all
819 Where expenditure_item_id = P_Exp_Item_Id
820 And line_type = 'R');
821
822 G_exp_item_id := P_Exp_item_Id;
823
824 End If;
825
826 Return G_Pa_Period_Name;
827
828 Exception
829 When OTHERS Then
830 G_exp_item_id := P_Exp_item_Id;
831 G_Pa_Date := Null;
832 G_Gl_Date := Null;
833 G_Recvr_Pa_Date := Null;
834 G_Recvr_Gl_Date := Null;
835 G_Pa_Period_Name := Null;
836 G_Gl_Period_Name := Null;
837 G_Recvr_Pa_Period_Name := Null;
838 G_Recvr_Gl_Period_Name := Null;
839 Return (NULL);
840
841 End Get_Latest_Pa_Per_Name;
842
843
844 Function Get_Latest_Gl_Per_Name(P_Exp_Item_Id IN NUMBER) return VARCHAR2 is
845
846 Begin
847
848 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
849
850 Return G_Gl_Period_Name;
851
852 Else
853
854 Select pa_date,
855 gl_date,
856 recvr_pa_date,
857 recvr_gl_date,
858 pa_period_name,
859 gl_period_name,
860 recvr_pa_period_name,
861 recvr_gl_period_name
862 Into
863 g_pa_date,
864 g_gl_date,
865 g_recvr_pa_date,
866 g_recvr_gl_date,
867 g_pa_period_name,
868 g_gl_period_name,
869 g_recvr_pa_period_name,
870 g_recvr_Gl_period_name
871 From pa_cost_distribution_lines_all
872 Where expenditure_item_id = P_Exp_Item_Id
873 And line_type = 'R'
874 And line_num = (
875 Select max(line_num)
876 From pa_cost_distribution_lines_all
877 Where expenditure_item_id = P_Exp_Item_Id
878 And line_type = 'R');
879
880 G_exp_item_id := P_Exp_item_Id;
881
882 End If;
883
884 Return G_Gl_Period_Name;
885
886 Exception
887 When OTHERS Then
888 G_exp_item_id := P_Exp_item_Id;
889 G_Pa_Date := Null;
890 G_Gl_Date := Null;
891 G_Recvr_Pa_Date := Null;
892 G_Recvr_Gl_Date := Null;
893 G_Pa_Period_Name := Null;
894 G_Gl_Period_Name := Null;
895 G_Recvr_Pa_Period_Name := Null;
896 G_Recvr_Gl_Period_Name := Null;
897 Return (NULL);
898
899 End Get_Latest_Gl_Per_Name;
900
901
902 Function Get_Latest_Recvr_Pa_Per_Name(P_Exp_Item_Id IN NUMBER) return VARCHAR2 is
903
904 Begin
905
906 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
907
908 Return G_Recvr_Pa_Period_Name;
909
910 Else
911
912 Select pa_date,
913 gl_date,
914 recvr_pa_date,
915 recvr_gl_date,
916 pa_period_name,
917 gl_period_name,
918 recvr_pa_period_name,
919 recvr_gl_period_name
920 Into
921 g_pa_date,
922 g_gl_date,
923 g_recvr_pa_date,
924 g_recvr_gl_date,
925 g_pa_period_name,
926 g_gl_period_name,
927 g_recvr_pa_period_name,
928 g_recvr_Gl_period_name
929 From pa_cost_distribution_lines_all
930 Where expenditure_item_id = P_Exp_Item_Id
931 And line_type = 'R'
932 And line_num = (
933 Select max(line_num)
934 From pa_cost_distribution_lines_all
935 Where expenditure_item_id = P_Exp_Item_Id
936 And line_type = 'R');
937
938 G_exp_item_id := P_Exp_item_Id;
939
940 End If;
941
942 Return G_Recvr_Pa_Period_Name;
943
944 Exception
945 When OTHERS Then
946 G_exp_item_id := P_Exp_item_Id;
947 G_Pa_Date := Null;
948 G_Gl_Date := Null;
949 G_Recvr_Pa_Date := Null;
950 G_Recvr_Gl_Date := Null;
951 G_Pa_Period_Name := Null;
952 G_Gl_Period_Name := Null;
953 G_Recvr_Pa_Period_Name := Null;
954 G_Recvr_Gl_Period_Name := Null;
955 Return (NULL);
956
957 End Get_Latest_Recvr_Pa_Per_Name;
958
959
960 Function Get_Latest_Recvr_Gl_Per_Name(P_Exp_Item_Id IN NUMBER) return VARCHAR2 is
961
962 Begin
963
964 If P_Exp_Item_Id = nvl(G_Exp_Item_Id,-999) Then
965
966 Return G_Recvr_Gl_Period_Name;
967
968 Else
969
970 Select pa_date,
971 gl_date,
972 recvr_pa_date,
973 recvr_gl_date,
974 pa_period_name,
975 gl_period_name,
976 recvr_pa_period_name,
977 recvr_gl_period_name
978 Into
979 g_pa_date,
980 g_gl_date,
981 g_recvr_pa_date,
982 g_recvr_gl_date,
983 g_pa_period_name,
984 g_gl_period_name,
985 g_recvr_pa_period_name,
986 g_recvr_gl_period_name
987 From pa_cost_distribution_lines_all
988 Where expenditure_item_id = P_Exp_Item_Id
989 And line_type = 'R'
990 And line_num = (
991 Select max(line_num)
992 From pa_cost_distribution_lines_all
993 Where expenditure_item_id = P_Exp_Item_Id
994 And line_type = 'R');
995
996 G_exp_item_id := P_Exp_item_Id;
997
998 End If;
999
1000 Return G_Recvr_Gl_Period_Name;
1001
1002 Exception
1003 When OTHERS Then
1004 G_exp_item_id := P_Exp_item_Id;
1005 G_Pa_Date := Null;
1006 G_Gl_Date := Null;
1007 G_Recvr_Pa_Date := Null;
1008 G_Recvr_Gl_Date := Null;
1009 G_Pa_Period_Name := Null;
1010 G_Gl_Period_Name := Null;
1011 G_Recvr_Pa_Period_Name := Null;
1012 G_Recvr_Gl_Period_Name := Null;
1013 Return (NULL);
1014
1015 End Get_Latest_Recvr_Gl_Per_Name;
1016
1017
1018 /* New function GET_ORG_NAME_WOSEC added for Bug 9321568 Start */
1019 FUNCTION GET_ORG_NAME_WOSEC ( P_Org_ID IN NUMBER ) RETURN VARCHAR2
1020 IS
1021 X_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE ;
1022
1023 BEGIN
1024 IF P_ORG_ID IS NULL THEN
1025 RETURN(NULL);
1026 END IF;
1027
1028 SELECT
1029 TL.NAME
1030 INTO
1031 X_ORG_NAME
1032 FROM
1033 HR_ALL_ORGANIZATION_UNITS_TL TL
1034 WHERE
1035 TL.ORGANIZATION_ID = P_ORG_ID
1036 AND TL.LANGUAGE = USERENV('LANG') ;
1037
1038 RETURN (X_ORG_NAME);
1039
1040 EXCEPTION
1041
1042 WHEN OTHERS THEN
1043 Return (NULL);
1044 END GET_ORG_NAME_WOSEC;
1045
1046
1047
1048
1049 END PA_EXPENDITURES_UTILS;