1 PACKAGE BODY PA_UTILS AS
2 /* $Header: PAXGUTLB.pls 120.12.12010000.6 2008/12/19 09:19:03 anuragar ship $ */
3 -- ==========================================================================
4 -- Added for bug 5067511 : These global variables should be only used in the
5 -- function GetPersonInfo
6 -- ==========================================================================
7 L_PERSON_ID per_all_people_f.person_id%TYPE;
8 L_PERSON_FULL_NAME per_all_people_f.full_name%TYPE;
9 L_PERSON_LAST_NAME per_all_people_f.last_name%TYPE;
10 L_PERSON_FIRST_NAME per_all_people_f.first_name%TYPE;
11 L_PERSON_MIDDLE_NAMES per_all_people_f.middle_names%TYPE;
12 L_PERSON_EMPLOYEE_NUMBER per_all_people_f.employee_number%TYPE;
13
14 -- ==========================================================================
15 -- = PROCEDURE GetProjInfo
16 -- ==========================================================================
17
18 PROCEDURE GetProjInfo ( X_proj_id IN NUMBER
19 , X_proj_num OUT NOCOPY VARCHAR2
20 , X_proj_name OUT NOCOPY VARCHAR2 )
21 IS
22 BEGIN
23
24 SELECT
25 segment1
26 , name
27 INTO
28 X_proj_num
29 , X_proj_name
30 FROM
31 pa_projects_all
32 WHERE
33 project_id = X_proj_id;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 X_proj_num := NULL;
38 X_proj_name := NULL;
39
40 END GetProjInfo;
41
42
43 -- ==========================================================================
44 -- = PROCEDURE SetGlobalEmpId
45 -- ==========================================================================
46
47 PROCEDURE SetGlobalEmpId ( p_emp_id NUMBER )
48 IS
49 BEGIN
50 pa_utils.Global_employee_id := p_emp_id;
51 END SetGlobalEmpId;
52
53 -- ==========================================================================
54 -- = FUNCTION GetGlobalEmpId
55 -- ==========================================================================
56
57 FUNCTION GetGlobalEmpId RETURN NUMBER
58 IS
59 BEGIN
60 RETURN ( pa_utils.Global_employee_id );
61 END GetGlobalEmpId;
62
63 -- ==========================================================================
64 -- = PROCEDURE GetTaskInfo
65 -- ==========================================================================
66
67 PROCEDURE GetTaskInfo ( X_task_id IN NUMBER
68 , X_task_num OUT NOCOPY VARCHAR2
69 , X_task_name OUT NOCOPY VARCHAR2 )
70 IS
71 BEGIN
72
73 IF (x_task_id = G_PREV_TASK_ID) THEN
74
75 x_task_num := G_PREV_TASK_NUM;
76 x_task_name := G_PREV_TASK_NAME;
77
78 ELSE
79
80 G_PREV_TASK_ID := x_task_id;
81
82 SELECT
83 task_number
84 ,task_name
85 INTO
86 X_task_num
87 ,X_task_name
88 FROM
89 pa_tasks
90 WHERE
91 task_id = X_task_id;
92
93 G_PREV_TASK_NUM := x_task_num;
94 G_PREV_TASK_NAME := x_task_name;
95
96 END IF;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 G_PREV_TASK_ID := x_task_id;
101 G_PREV_TASK_NUM := NULL;
102 G_PREV_TASK_NAME := NULL;
103 X_task_num := NULL;
104 X_task_name := NULL;
105
106 END GetTaskInfo;
107
108
109
110 -- ==========================================================================
111 -- = FUNCTION GetProjId
112 -- ==========================================================================
113
114 FUNCTION GetProjId ( X_project_num IN VARCHAR2 ) RETURN NUMBER
115 IS
116 X_project_id NUMBER;
117 BEGIN
118
119 IF (x_project_num = G_PREV_PROJ_NUM) THEN
120
121 RETURN (G_PREV_PROJECT_ID);
122
123 ELSE
124
125 G_PREV_PROJ_NUM := x_project_num;
126
127 SELECT
128 project_id
129 INTO
130 X_project_id
131 FROM
132 pa_projects_all
133 WHERE
134 segment1 = X_project_num;
135
136 G_PREV_PROJECT_ID := x_project_id;
137 RETURN ( X_project_id );
138
139 END IF;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 G_PREV_PROJ_NUM := x_project_num;
144 G_PREV_PROJECT_ID := NULL;
145 RETURN ( NULL );
146
147 END GetProjId;
148
149 -- ==========================================================================
150 -- = FUNCTION GetEmpId
151 -- ==========================================================================
152
153 FUNCTION GetEmpId ( X_emp_num IN VARCHAR2 ) RETURN NUMBER
154 IS
155 X_person_id NUMBER;
156 BEGIN
157 SELECT
158 person_id
159 INTO
160 X_person_id
161 FROM
162 pa_employees
163 WHERE
164 employee_number = X_emp_num;
165
166 RETURN ( X_person_id );
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 RETURN ( NULL );
171
172 END GetEmpId;
173
174
175
176 -- ==========================================================================
177 -- = FUNCTION GetEmpIdFromUser
178 -- ==========================================================================
179
180 FUNCTION GetEmpIdFromUser ( X_userid IN NUMBER ) RETURN NUMBER
181 IS
182 X_person_id NUMBER;
183 BEGIN
184 SELECT
185 employee_id
186 INTO
187 X_person_id
188 FROM
189 fnd_user
190 WHERE
191 user_id = X_userid;
192
193 RETURN ( X_person_id );
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 RETURN ( NULL );
198
199 END GetEmpIdFromUser;
200
201
202
203 -- ==========================================================================
204 -- = FUNCTION GetEmpName
205 -- ==========================================================================
206
207 FUNCTION GetEmpName ( X_person_id IN NUMBER ) RETURN VARCHAR2
208 IS
209 X_person_name VARCHAR2(240);
210 BEGIN
211 SELECT
212 full_name
213 INTO
214 X_person_name
215 FROM
216 pa_employees
217 WHERE
218 person_id = X_person_id;
219
220 RETURN ( X_person_name );
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 RETURN ( NULL );
225
226 END GetEmpName;
227
228
229
230 -- ==========================================================================
231 -- = FUNCTION GetTaskId
232 -- ==========================================================================
233
234 FUNCTION GetTaskId ( X_proj_id IN NUMBER
235 , X_task_num IN VARCHAR2 ) RETURN NUMBER
236 IS
237 X_task_id NUMBER;
238 BEGIN
239
240 IF (x_proj_id = G_PREV_PROJ_ID2 AND
241 X_task_num = G_PREV_TASK_NUM2) THEN
242
243 RETURN(G_PREV_TASK_ID2);
244
245 ELSE
246
247 G_PREV_PROJ_ID2 := x_proj_id;
248 G_PREV_TASK_NUM2 := x_task_num;
249
250 SELECT
251 task_id
252 INTO
253 X_task_id
254 FROM
255 pa_tasks
256 WHERE
257 project_id = X_proj_id
258 AND task_number = X_task_num;
259
260 G_PREV_TASK_ID2 := x_task_id;
261 RETURN ( X_task_id );
262
263 END IF;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 G_PREV_PROJ_ID2 := x_proj_id;
268 G_PREV_TASK_NUM2 := x_task_num;
269 G_PREV_TASK_ID2 := NULL;
270 RETURN ( NULL );
271
272 END GetTaskId;
273 -- ==========================================================================
274 -- = FUNCTION GetOrdId
275 -- ==========================================================================
276
277 FUNCTION GetOrgId ( X_org_name IN VARCHAR2 ) RETURN NUMBER
278 IS
279 X_org_id NUMBER;
280 BEGIN
281 SELECT
282 organization_id
283 INTO
284 X_org_id
285 FROM
286 hr_organization_units o,
287 pa_implementations i
288 WHERE name = X_org_name
289 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
290 AND o.business_group_id = i.business_group_id)
291 OR (pa_utils.IsCrossBGProfile_WNPS = 'Y'));
292
293 RETURN ( X_org_id );
294
295 EXCEPTION
296 WHEN OTHERS THEN
297 RETURN ( NULL );
298
299 END GetOrgId;
300
301
302 -- ==========================================================================
303 -- = PROCEDURE GetOrgnId
304 --Bug 3010848. Added to distinguish if for the given organization name
305 --one or many organization_id are found
306 -- ==========================================================================
307
308 PROCEDURE GetOrgnId ( X_org_name IN VARCHAR2
309 , X_bg_id IN NUMBER DEFAULT NULL
310 , X_Orgn_Id OUT NOCOPY Number
311 , X_Return_Status OUT NOCOPY Varchar2) IS
312 BEGIN
313 X_Return_Status := NULL;
314
315 If X_bg_id is not null Then
316 SELECT
317 organization_id
318 INTO
319 X_orgn_id
320 FROM
321 hr_organization_units o,
322 pa_implementations i
323 WHERE name = X_org_name
324 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
325 AND o.business_group_id = i.business_group_id)
326 OR (pa_utils.IsCrossBGProfile_WNPS = 'Y'
327 AND o.business_group_id = X_bg_id ));
328 Else
329 SELECT
330 organization_id
331 INTO
332 X_orgn_id
333 FROM
334 hr_organization_units o,
335 pa_implementations i
336 WHERE name = X_org_name
337 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
338 AND o.business_group_id = i.business_group_id)
339 OR (pa_utils.IsCrossBGProfile_WNPS = 'Y' ));
340 End If;
341
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 X_Orgn_Id := NULL;
345 --return status not set for no_data_found
346 --since in trx import different messages are set depending on organization type
347 WHEN TOO_MANY_ROWS THEN
348 X_Return_Status := 'PA_TOO_MANY_ORGN';
349
350 END GetOrgnId;
351
352
353 -- ==========================================================================
354 -- = FUNCTION GetOrgName
355 -- ==========================================================================
356
357 FUNCTION GetOrgName ( X_org_id IN NUMBER ) RETURN VARCHAR2
358 IS
359 /* Bug No:- 2487147, UTF8 change : changed X_org_name to %TYPE */
360 /* X_org_name VARCHAR2(60); */
361 X_org_name hr_organization_units.name%TYPE;
362 BEGIN
363 SELECT
364 name
365 INTO
366 X_org_name
367 FROM
368 hr_organization_units o,
369 pa_implementations i
370 WHERE
371 organization_id = X_org_id
372 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
373 AND o.business_group_id = i.business_group_id)
374 OR pa_utils.IsCrossBGProfile_WNPS = 'Y' )
375 ;
376
377
378 RETURN ( X_org_name );
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 RETURN ( NULL );
383
384 END GetOrgName;
385
386 -- ==========================================================================
387 -- = FUNCTION GetWeekEnding
388 -- ==========================================================================
389
390 FUNCTION GetWeekEnding ( X_date IN DATE ) RETURN DATE
391 IS
392 X_week_ending DATE;
393 X_week_ending_day VARCHAR2(80);
394 X_week_ending_day_index number;
395 BEGIN
396
397 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
398 into X_week_ending_day_index
399 FROM pa_implementations;
400
401 select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
402 into X_week_ending_day from dual;
403
404 SELECT
405 next_day( trunc( X_date )-1, X_week_ending_day ) /* BUG#3118592 */
406 INTO
407 X_week_ending
408 FROM
409 sys.dual;
410
411 RETURN ( X_week_ending );
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 RETURN ( NULL );
416
417 END GetWeekEnding;
418
419
420 -- ==========================================================================
421 -- = FUNCTION DateInExpWeek
422 -- ==========================================================================
423
424 FUNCTION DateInExpWeek ( X_date IN DATE
425 , X_week_end IN DATE ) RETURN BOOLEAN
426 IS
427 dummy NUMBER;
428 BEGIN
429
430 IF (trunc(x_date) = G_PREV_DATE AND
431 trunc(x_week_end) = G_PREV_WEEK_END) THEN
432
433 IF G_PREV_DATEIN = 1 THEN
434 RETURN (TRUE);
435 ELSE
436 RETURN (FALSE);
437 END IF;
438
439 ELSE
440
441 G_PREV_DATE := trunc(x_date);
442 G_PREV_WEEK_END := trunc(x_week_end);
443
444 SELECT
445 count(1)
446 INTO
447 dummy
448 FROM
449 sys.dual
450 WHERE
451 trunc(X_date) BETWEEN trunc(trunc( X_week_end )-6 ) /* BUG#3118592 */
452 AND trunc( X_week_end );
453
454 IF ( dummy = 0 ) THEN
455 G_PREV_DATEIN := 0;
456 RETURN ( FALSE );
457 ELSE
458 G_PREV_DATEIN := 1;
459 RETURN ( TRUE );
460 END IF;
461
462 END IF;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466
467 G_PREV_DATE := trunc(x_date);
468 G_PREV_WEEK_END := trunc(x_week_end);
469 G_PREV_DATEIN := 0;
470 RETURN ( FALSE );
471
472 END DateInExpWeek;
473
474
475 -- ==========================================================================
476 -- = FUNCTION GetEmpOrgId
477 -- ==========================================================================
478
479 /* cwk changes : Modified function to derive the Organization Id for a Person Id of a contingent worker also*/
480
481 FUNCTION GetEmpOrgId ( X_person_id IN NUMBER
482 , X_date IN DATE ) RETURN NUMBER
483 IS
484 X_org_id NUMBER;
485 X_Cross_BG_Profile VARCHAR2(2); /* Added local variable for 3194743 */
486 X_business_group_id NUMBER; /* Added local variable for 3194743 */
487 BEGIN
488
489 IF (x_person_id = G_PREV_PERSON_ID2 AND
490 trunc(x_date) = G_PREV_DATE4) THEN
491
492 RETURN (G_PREV_ORG_ID2);
493
494 ELSE
495
496 G_PREV_PERSON_ID2 := x_person_id;
497 G_PREV_DATE4 := x_date;
498
499 X_Cross_BG_Profile:= pa_utils.IsCrossBGProfile_WNPS; --Moved the function call from inside where clause to here
500
501 SELECT business_group_id --Moved selection of BG id from inside where clause to here
502 INTO X_business_group_id
503 FROM pa_implementations;
504
505 SELECT
506 max(a.organization_id)
507 INTO
508 X_org_id
509 FROM
510 per_assignment_status_types s
511 , per_all_assignments_f a -- Modified for bug 4699231
512 WHERE
513 a.person_id = X_person_id
514 AND a.primary_flag = 'Y'
515 AND a.assignment_type in ('E', 'C')
516 AND a.assignment_status_type_id = s.assignment_status_type_id
517 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
518 AND X_date BETWEEN trunc( a.effective_start_date )
519 AND trunc( a.effective_end_date )
520 /* Added for bug 2009830 */
521 AND ((X_Cross_BG_Profile = 'N'
522 AND X_business_group_id = a.business_group_id+0)
523 OR X_Cross_BG_Profile = 'Y' )
524 ; /*Bug 7645561 Changed the Query to include TERM_ASSIGN */
525
526 -- tsaifee 01/29/97 : Bug 442432 - Perf. for the above query.
527 -- the last line of the query modified : 0 added to a.business_group_id
528 -- so as not to use that index, as its use was giving an index merge.
529
530 G_PREV_ORG_ID2 := x_org_id;
531 RETURN ( X_org_id );
532
533 END IF;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 G_PREV_PERSON_ID2 := x_person_id;
538 G_PREV_DATE4 := x_date;
539 G_PREV_ORG_ID2 := NULL;
540 RETURN ( NULL );
541
542 END GetEmpOrgId;
543
544
545 -- ==========================================================================
546 -- = FUNCTION GetEmpCostRate
547 -- ==========================================================================
548
549 --
550 -- Date : 31-OCT-02
551 -- Updated By : Sandeep Bharathan
552 --
553 -- For any new functionality that requires the employee cost rate please
554 -- use PA_COST_RATE_PUB.GetEmpCostRate rather than using this function
555 --
556
557 FUNCTION GetEmpCostRate ( X_person_id IN NUMBER
558 , X_date IN DATE ) RETURN NUMBER
559 IS
560 X_cost_rate NUMBER(22,5);
561 l_job_id pa_expenditure_items_all.job_id%type;
562 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
563 l_start_date_active date;
564 l_end_date_active date;
565 l_organization_id number;
566 l_org_id number; /*2879644*/
567 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
568 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
569 l_override_type pa_compensation_details.override_type%type;
570 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
571 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
572 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
573 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
574 l_acct_cost_rate pa_compensation_details.acct_exchange_rate%type;
575 l_ot_project_id pa_projects_all.project_id%type;
576 l_ot_task_id pa_tasks.task_id%type;
577 l_err_code varchar2(200);
578 l_err_stage number;
579 l_return_value varchar2(100);
580
581 BEGIN
582
583 --
584 -- Changed for labor costing enhancements
585 --
586 PA_COST_RATE_PUB.get_labor_rate(p_person_id =>x_person_id
587 ,x_job_id =>l_job_id
588 ,p_calling_module =>'STAFFED'
589 ,p_org_id => l_org_id /*2879644*/
590 ,p_txn_date =>x_date
591 ,x_organization_id =>l_organization_id
592 ,x_cost_rate =>x_cost_rate
593 ,x_start_date_active =>l_start_date_active
594 ,x_end_date_active =>l_end_date_active
595 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
596 ,x_costing_rule =>l_costing_rule
597 ,x_rate_sch_id =>l_rate_sch_id
598 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
599 ,x_acct_rate_type =>l_acct_rate_type
600 ,x_acct_rate_date_code =>l_acct_rate_date_code
601 ,x_acct_exch_rate =>l_acct_exch_rate
602 ,x_ot_project_id =>l_ot_project_id
603 ,x_ot_task_id =>l_ot_task_id
604 ,x_err_stage =>l_err_stage
605 ,x_err_code =>l_err_code
606 );
607
608 /*
609 SELECT
610 cd.hourly_cost_rate
611 INTO
612 X_cost_rate
613 FROM
614 pa_compensation_details cd
615 WHERE
616 cd.person_id = X_person_id
617 AND X_date BETWEEN cd.start_date_active
618 AND nvl( cd.end_date_active, X_date );
619 */
620 RETURN ( X_cost_rate );
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 RETURN ( NULL );
625
626 END GetEmpCostRate;
627
628
629
630 -- ==========================================================================
631 -- = FUNCTION GetExpTypeCostRate
632 -- ==========================================================================
633
634 FUNCTION GetExpTypeCostRate ( X_expenditure_type IN VARCHAR2
635 , X_date IN DATE ) RETURN NUMBER
636 IS
637 X_exp_type_cost_rate NUMBER(22,5);
638 BEGIN
639 SELECT
640 nvl( r.cost_rate, 1 )
641 INTO
642 X_exp_type_cost_rate
643 FROM
644 pa_expenditure_cost_rates r
645 WHERE
646 r.expenditure_type = X_expenditure_type
647 AND X_date BETWEEN r.start_date_active
648 AND nvl( r.end_date_active, X_date );
649
650 RETURN ( X_exp_type_cost_rate );
651
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 RETURN ( 1 );
655 WHEN OTHERS THEN
656 RETURN ( NULL );
657
658 END GetExpTypeCostRate;
659
660
661
662 -- ==========================================================================
663 -- = FUNCTION GetEmpJobId
664 -- ==========================================================================
665
666 /* cwk changes: Modified function to derive the job id for a contingent worker person id also */
667 /* cwk changes: Modified function to derive job id for the entered PO number and line - Bug 4044057 */
668
669 FUNCTION GetEmpJobId ( X_person_id IN NUMBER
670 , X_date IN DATE
671 , X_person_type IN VARCHAR2 DEFAULT NULL
672 , X_po_number IN VARCHAR2 DEFAULT NULL
673 , X_po_line_num IN NUMBER DEFAULT NULL
674 , X_po_header_id IN NUMBER DEFAULT NULL
675 , X_po_line_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
676 IS
677 X_emp_job_id NUMBER;
678 X_Cross_BG_Profile VARCHAR2(2); /* Added local variable for 3194743 */
679 X_business_group_id NUMBER; /* Added local variable for 3194743 */
680 l_person_type VARCHAR2(1) ;
681 l_assignment_status VARCHAR2(20);
682 l_po_header_id NUMBER;
683 l_po_line_id NUMBER;
684 l_po_number VARCHAR2(20);
685 l_po_line_num NUMBER;
686
687 BEGIN
688 If x_person_type is NOT NULL then
689 if x_person_type = 'EMP' then
690 l_person_type := 'E' ;
691 l_assignment_status := 'ACTIVE_ASSIGN' ;
692 else
693 l_person_type := 'C';
694 l_assignment_status := 'ACTIVE_CWK' ;
695 end if;
696 end if ;
697
698 IF (x_person_id = G_PREV_PERSON_ID AND
699 trunc(x_date) = G_PREV_DATE3 AND
700 x_po_number is null AND
701 x_po_header_id is null) THEN
702
703 RETURN (G_PREV_EMPJOB_ID);
704
705 ELSE
706
707 G_PREV_PERSON_ID := x_person_id;
708 G_PREV_DATE3 := trunc(x_date);
709
710 X_Cross_BG_Profile:= pa_utils.IsCrossBGProfile_WNPS; --Moved the function call from inside where clause to here
711
712 SELECT business_group_id --Moved selection of BG id from inside where clause to here
713 INTO X_business_group_id
714 FROM pa_implementations;
715
716 If x_po_header_id is not null then --Bug 4044057
717
718 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
719 /* Bug 6978184 : Added Condition (and not...) */
720 if ( (x_po_header_id <> l_po_header_id or x_po_line_id <> l_po_line_id) and not (PO_PA_INTEGRATION_GRP.is_PO_active(X_person_id, X_date,x_po_header_id,x_po_line_id)) ) then
721 RETURN( NULL );
722 end if;
723
724 elsIf x_po_number is not null then --Bug 4044057
725
726 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
727
728 BEGIN
729 select poh.segment1, pol.line_num
730 into l_po_number, l_po_line_num
731 from po_headers poh,
732 po_lines pol
733 where poh.po_header_id = pol.po_header_id
734 and poh.po_header_id = l_po_header_id
735 and pol.po_line_id = l_po_line_id;
736 EXCEPTION /* Bug 6978184 : Added Exception Block */
737 WHEN NO_DATA_FOUND THEN
738 NULL;
739 WHEN OTHERS THEN
740 Raise;
741 END;
742
743
744 if (l_po_number <> x_po_number or l_po_line_num <> x_po_line_num ) then
745 /* Bug 6978184 : Added Query and IF below */
746 select poh.po_header_id, pol.po_line_id
747 into l_po_header_id, l_po_line_id
748 from po_headers poh,
749 po_lines pol
750 where poh.po_header_id = pol.po_header_id
751 and poh.type_lookup_code = 'STANDARD'
752 and poh.segment1 = x_po_number
753 and pol.line_num = x_po_line_num;
754
755 if NOT PO_PA_INTEGRATION_GRP.is_PO_active(X_person_id, X_date, l_po_header_id, l_po_line_id) then
756 RETURN( NULL );
757 end if;
758 end if;
759
760 end if; -- End of Bug 4044057
761
762
763 If x_person_type IS NULL then
764
765 SELECT
766 max(a.job_id)
767 INTO
768 X_emp_job_id
769 FROM
770 per_assignment_status_types s
771 , per_all_assignments_f a -- Modified for bug 4699231
772 WHERE
773 a.job_id IS NOT NULL
774 AND a.primary_flag = 'Y'
775 AND X_date BETWEEN trunc( a.effective_start_date )
776 AND trunc( a.effective_end_date )
777 AND a.person_id = X_person_id
778 AND a.assignment_type in ('E', 'C')
779 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
780 AND s.assignment_status_type_id = a.assignment_status_type_id
781 /* Added for bug 2009830 */
782 AND (( X_Cross_BG_Profile = 'N'
783 AND X_business_group_id = a.business_group_id+0)
784 OR X_Cross_BG_Profile = 'Y' ) ;
785 G_PREV_EMPJOB_ID := x_emp_job_id;
786 RETURN( X_emp_job_id );
787 /* Bug 7645561 Changed the Query to include TERM_ASSIGN */
788
789 else
790
791 SELECT
792 a.job_id
793 INTO
794 X_emp_job_id
795 FROM
796 per_assignment_status_types s
797 , per_all_assignments_f a -- for Bug 4699231
798 WHERE
799 a.job_id IS NOT NULL
800 AND a.primary_flag = 'Y'
801 AND X_date BETWEEN trunc( a.effective_start_date )
802 AND trunc( a.effective_end_date )
803 AND a.person_id = X_person_id
804 -- AND a.assignment_type in ('E', 'C') -- commented out for bug : 3568109
805 AND a.assignment_type = l_person_type
806 -- AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- commented out for bug : 3568109
807 AND s.per_system_status = l_assignment_status
808 AND s.assignment_status_type_id = a.assignment_status_type_id
809 /* Added for bug 2009830 */
810 AND (( X_Cross_BG_Profile = 'N'
811 AND X_business_group_id = a.business_group_id+0)
812 OR X_Cross_BG_Profile = 'Y' ) ;
813 G_PREV_EMPJOB_ID := x_emp_job_id;
814 RETURN( X_emp_job_id );
815
816 end if ; -- end if for x_person_type
817 END IF;
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 G_PREV_PERSON_ID := x_person_id;
822 G_PREV_DATE3 := x_date;
823 G_PREV_EMPJOB_ID := NULL;
824 RETURN( NULL );
825
826 END GetEmpJobId;
827
828 -- ==========================================================================
829 -- = FUNCTION GetNextEiId
830 -- ==========================================================================
831
832 FUNCTION GetNextEiId RETURN NUMBER
833 IS
834 X_expenditure_item_id NUMBER(15);
835 BEGIN
836 SELECT
837 pa_expenditure_items_s.nextval
838 INTO
839 X_expenditure_item_id
840 FROM
841 sys.dual;
842
843 RETURN( X_expenditure_item_id );
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 RETURN( NULL );
848
849 END GetNextEiId;
850
851 -- ==========================================================================
852 -- = FUNCTION CheckExpTypeActive
853 -- ==========================================================================
854
855 FUNCTION CheckExpTypeActive( X_expenditure_type IN VARCHAR2
856 , X_date IN DATE )
857 RETURN BOOLEAN
858 IS
859 dummy NUMBER;
860 BEGIN
861 SELECT
862 count(*)
863 INTO
864 dummy
865 FROM
866 pa_expenditure_types et
867 WHERE
868 et.expenditure_type = X_expenditure_type
869 AND X_date BETWEEN et.start_date_active
870 AND nvl( et.end_date_active, X_date );
871
872 IF ( dummy = 0 ) THEN
873 RETURN ( FALSE );
874 ELSE
875 RETURN ( TRUE );
876 END IF;
877
878 END CheckExpTypeActive;
879
880
881 -- ==========================================================================
882 -- = FUNCTION get_org_hierarchy_top
883 -- ==========================================================================
884
885 FUNCTION get_org_hierarchy_top ( X_org_structure_version_id IN NUMBER )
886 RETURN NUMBER
887 IS
888 X_top_org_id NUMBER(15);
889 BEGIN
890 -- index on org_structure_version_id is turned off assuming there won't be
891 -- that many version of org hierarchy. Hence better performance.
892 -- added the below optimiser hint based on the suggestion of performance team for bug 2474299
893 SELECT /*+ index_ffs(se1 PER_ORG_STRUCTURE_ELEMENTS_N50) */
894 DISTINCT
895 se1.organization_id_parent
896 INTO
897 X_top_org_id
898 FROM
899 per_org_structure_elements se1
900 WHERE
901 se1.org_structure_version_id||'' = X_org_structure_version_id
902 AND NOT exists
903 ( SELECT null
904 FROM per_org_structure_elements se2
905 WHERE se2.org_structure_version_id = X_org_structure_version_id
906 AND se2.organization_id_child = se1.organization_id_parent );
907
908 RETURN( X_top_org_id );
909
910 END get_org_hierarchy_top;
911
912
913 -- ==========================================================================
914 -- = FUNCTION business_group_id
915 -- ==========================================================================
916
917 FUNCTION business_group_id RETURN NUMBER
918 IS
919 X_business_group_id NUMBER(15);
920 BEGIN
921 SELECT DISTINCT business_group_id /*Distinct added for Bug 6043451*/
922 INTO X_business_group_id
923 FROM pa_implementations;
924
925 RETURN(X_business_group_id);
926
927 END business_group_id;
928
929 -- ==========================================================================
930 -- FUNCTION Get_business_group_id
931 -- ==========================================================================
932
933 FUNCTION Get_business_group_id RETURN NUMBER
934 IS
935 l_business_group_id NUMBER;
936 BEGIN
937 IF G_Business_Group_Id IS NULL THEN
938 l_business_group_id := pa_utils.business_group_id;
939 ELSE
940 l_business_group_id := G_Business_Group_Id;
941 END IF;
942
943 RETURN l_business_group_id;
944
945 EXCEPTION
946 WHEN OTHERS THEN RAISE;
947
948 END Get_business_group_id;
949
950 -- ==========================================================================
951 -- PROCEDURE Set_business_group_id
952 -- ==========================================================================
953
954 PROCEDURE Set_business_group_id
955 IS
956 BEGIN
957 G_Business_Group_Id := pa_utils.business_group_id;
958
959 END Set_business_group_id;
960
961 -- ==========================================================================
962 -- = FUNCTION is_project_costing_installed
963 -- ==========================================================================
964
965 FUNCTION is_project_costing_installed RETURN VARCHAR2
966 IS
967 x_pa_costing_installed VARCHAR2(2);
968 BEGIN
969
970 if (fnd_profile.value('PA_PROJECT_COSTING_INSTALLED') = 'Y') then
971 x_pa_costing_installed := 'Y';
972 else
973 x_pa_costing_installed := 'N';
974 end if;
975 return(x_pa_costing_installed);
976
977 EXCEPTION
978 when OTHERS then
979 return('N');
980 END is_project_costing_installed;
981
982 -- ==========================================================================
983 -- = FUNCTION IsCrossChargeable
984 -- ==========================================================================
985
986 FUNCTION IsCrossChargeable( X_Project_Id Number ) RETURN BOOLEAN
987 IS
988
989 dummy NUMBER(15);
990 BEGIN
991
992 IF (x_project_id = G_PREV_PROJ_ID) THEN
993
994 IF G_PREV_CHARGE = 1 then
995 RETURN TRUE;
996 ELSE
997 RETURN FALSE;
998 END IF;
999
1000 ELSE
1001
1002 Select 1
1003 Into dummy
1004 From sys.dual
1005 Where exists
1006 ( Select null
1007 From pa_projects_expend_v p
1008 Where p.project_Id = X_project_Id );
1009
1010 G_PREV_PROJ_ID := x_project_id;
1011
1012 If dummy = 1 then
1013 G_PREV_CHARGE := 1;
1014 Return TRUE ;
1015 ELSE
1016 G_PREV_CHARGE := 0;
1017 RETURN FALSE;
1018 End if;
1019
1020 END IF;
1021
1022 EXCEPTION
1023 WHEN NO_DATA_FOUND THEN
1024 G_PREV_PROJ_ID := x_project_id;
1025 G_PREV_CHARGE := 0;
1026 return FALSE;
1027 WHEN OTHERS THEN
1028 Return TRUE ;
1029 END ;
1030
1031
1032 -- ==========================================================================
1033 -- = FUNCTION pa_morg_implemented
1034 -- ==========================================================================
1035
1036 FUNCTION pa_morg_implemented RETURN VARCHAR2
1037 IS
1038 x_dummy VARCHAR2(1);
1039
1040 BEGIN
1041
1042 SELECT 'Y'
1043 INTO x_dummy
1044 FROM sys.dual
1045 WHERE EXISTS (
1046 SELECT NULL
1047 FROM pa_implementations_all
1048 WHERE org_id IS NOT NULL );
1049
1050 IF ( x_dummy = 'Y' ) THEN
1051 RETURN( 'Y' );
1052 ELSE
1053 RETURN( 'N' );
1054 END IF;
1055
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN
1058 RETURN( 'N' );
1059
1060
1061 END pa_morg_implemented;
1062
1063
1064 -- ==========================================================================
1065 -- = FUNCTION CheckProjectOrg
1066 -- ==========================================================================
1067
1068 FUNCTION CheckProjectOrg (x_org_id IN NUMBER) RETURN VARCHAR2 IS
1069
1070 -- This function returns 'Y' if a given org is a project organization ,
1071 -- otherwise , it returns 'N'
1072
1073 CURSOR l_proj_org_csr IS
1074 SELECT DISTINCT 'x'
1075 FROM pa_organizations_proj_all_bg_v
1076 WHERE organization_id = x_org_id;
1077
1078 l_dummy VARCHAR2(1);
1079 BEGIN
1080
1081 OPEN l_proj_org_csr;
1082 FETCH l_proj_org_csr INTO l_dummy;
1083 IF l_proj_org_csr%NOTFOUND THEN
1084 CLOSE l_proj_org_csr;
1085 RETURN 'N';
1086 ELSE
1087 CLOSE l_proj_org_csr;
1088 RETURN 'Y';
1089 END IF;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 RETURN 'N';
1094 END CheckProjectOrg;
1095
1096 ----------------------------------------------------------------------
1097 -- Function : get_pa_date
1098 -- Derive PA date from GL date and ei date .
1099 -- This function accepts the expenditure item date and the GL date
1100 -- and derives the period name based on this. This is mainly used
1101 -- for AP invoices and transactions imported from other systems
1102 -- where the GL date is known in advance and the PA date has to
1103 -- be determined. In the current logic, the PA date is derived solely
1104 -- based on the EI date. The GL date which is passed as a parameter is
1105 -- ignored. However, it is still retained as a parameter in case the
1106 -- logic for the derivation of the PA date is changed on a later date.
1107 -----------------------------------------------------------------------
1108
1109 FUNCTION get_pa_date( x_ei_date IN date, x_gl_date IN date ) return date
1110 IS
1111 l_pa_date date ;
1112 BEGIN
1113
1114 -- The PA date is derived solely from the EI date as the earliest open
1115 -- or future period on or after the EI date...sparames Nov 14, 1997
1116
1117 SELECT MIN(pap.end_date)
1118 INTO l_pa_date
1119 FROM pa_periods pap
1120 WHERE status in ('O','F')
1121 AND pap.end_date >= x_ei_date;
1122
1123 return(l_pa_date) ;
1124
1125 EXCEPTION
1126 WHEN NO_DATA_FOUND THEN
1127 l_pa_date :=NULL ;
1128 WHEN OTHERS THEN
1129 RAISE ;
1130 END get_pa_date ;
1131
1132 ----------------------------------------------------------------------
1133 -- Function : get_pa_end_date
1134 -- Derive the period end date based on the period name
1135 --
1136 -- This function accepts the period name and gets the period end
1137 -- date from the pa_periods table. The function created for
1138 -- burden cost accounting.
1139 -- Created by Sandeep 04-MAR-1998
1140 -----------------------------------------------------------------------
1141
1142 FUNCTION get_pa_end_date( x_pa_period_name IN VARCHAR2 ) return date
1143 IS
1144 l_pa_end_date date ;
1145 BEGIN
1146
1147 SELECT pap.end_date
1148 INTO l_pa_end_date
1149 FROM pa_periods pap
1150 WHERE pap.period_name = x_pa_period_name;
1151
1152 return(l_pa_end_date) ;
1153
1154 EXCEPTION
1155 WHEN NO_DATA_FOUND THEN
1156 l_pa_end_date :=NULL ;
1157 WHEN OTHERS THEN
1158 RAISE ;
1159 END get_pa_end_date ;
1160
1161 ------------------------------------------------------------------
1162 -- Function : get_pa_period_name
1163 -- Derive PA date from GL date and ei date .
1164 -- This function accepts the expenditure item date and the GL date
1165 -- and derives the period name based on this. This is mainly used
1166 -- for AP invoices and transactions imported from other systems
1167 -- where the GL date is known in advance and the PA date has to
1168 -- be determined. This function is identical to the
1169 -- pa_date_from_gl_date function except that it returns the
1170 -- corresponding period name instead of the PA date
1171 -------------------------------------------------------------------
1172
1173 FUNCTION get_pa_period_name( x_ei_date IN date, x_gl_date IN date ) return varchar2
1174 IS
1175 l_period_name pa_periods_all.period_name%TYPE;
1176 BEGIN
1177
1178 -- The PA date is derived as the end date of the earliest open or
1179 -- future PA period on or after the EI date. The GL date which is
1180 -- passed as a parameter to this function is not used at present but
1181 -- is retained for future use...sparames Nov 14,1997
1182
1183 SELECT pa_periods.period_name
1184 INTO l_period_name
1185 FROM pa_periods
1186 WHERE pa_periods.end_Date =
1187 (SELECT MIN(pap.end_date)
1188 FROM pa_periods pap
1189 WHERE status in ('O','F')
1190 AND pap.end_date >= x_ei_date)
1191 AND status in ('O','F'); /* Added the check for bug #1550929 */
1192
1193 return(l_period_name) ;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 l_period_name :=NULL ;
1198 WHEN OTHERS THEN
1199 RAISE ;
1200 END get_pa_period_name;
1201
1202 -- ==========================================================================
1203 -- = FUNCTION GetETypeClassCode
1204 -- ==========================================================================
1205
1206 FUNCTION GetETypeClassCode ( X_system_linkage IN VARCHAR2) RETURN VARCHAR2
1207 IS
1208 etypeclass_code VARCHAR2(3) ;
1209 BEGIN
1210
1211 IF (X_system_linkage = G_PREV_SYS_LINK) THEN
1212
1213 RETURN (G_PREV_FUNCTION);
1214 ELSE
1215
1216 G_PREV_SYS_LINK := X_system_linkage;
1217
1218 SELECT function
1219 INTO etypeclass_code
1220 FROM pa_system_linkages
1221 WHERE function = X_system_linkage ;
1222
1223 G_PREV_FUNCTION := etypeclass_code;
1224 RETURN( etypeclass_code ) ;
1225
1226 END IF;
1227
1228 EXCEPTION
1229 WHEN NO_DATA_FOUND THEN
1230 G_PREV_SYS_LINK := X_system_linkage;
1231 G_PREV_FUNCTION := NULL;
1232 etypeclass_code := NULL ;
1233 RETURN( etypeclass_code ) ;
1234 END GetEtypeClassCode;
1235
1236 -- ==========================================================================
1237 -- = FUNCTION Get_Org_Window_Title
1238 -- ==========================================================================
1239 /* These comments are added for bug 1812275.
1240 Please have a look at the solution provided in this bug if you face any issue
1241 with this procedure like no_data_found in pa_implementations.
1242 We are not suppressing any exceptions because and exception should be raised
1243 if there are no records found in pa_implementations
1244 */
1245
1246
1247 FUNCTION Get_Org_Window_Title RETURN VARCHAR2
1248 IS
1249
1250 l_multi_org VARCHAR2(1);
1251 l_multi_cur VARCHAR2(1);
1252 /* Bug 2657833 - UTF8 change Impact */
1253 /* l_wnd_context VARCHAR2(80); */
1254 l_wnd_context HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
1255 l_id VARCHAR2(15);
1256
1257 BEGIN
1258
1259 /*
1260 ***
1261 *** Get multi-org and MRC information on the current
1262 *** product installation.
1263 ***
1264 */
1265
1266 SELECT nvl(multi_org_flag, 'N')
1267 ---- Bug#MRC_SCHEMA_ELIM , nvl(multi_currency_flag, 'N')
1268 INTO l_multi_org
1269 ---, l_multi_cur
1270 FROM fnd_product_groups;
1271
1272 --Added this check that does the same job as the above select
1273 If gl_mc_info.alc_enabled(275) Then -- 12i MOAC changes
1274 -- IF gl_mc_info.mrc_enabled(275) THEN -- 12i MOAC changes
1275 l_multi_cur := 'Y';
1276 ELSE
1277 l_multi_cur := 'N';
1278 END IF;
1279
1280 /*
1281 ***
1282 *** Case #1 : Non-Multi-Org or Multi-SOB
1283 ***
1284 *** A. MRC not installed, OR
1285 *** MRC installed, Non-Primary/Reporting Books
1286 *** Form Name (SOB Short Name) - Context Info
1287 *** e.g. Maintain Forecast(US OPS) - Forecast Context Info
1288 ***
1289 *** B. MRC installed, Primary Books
1290 *** Form Name (SOB Short Name: Primary Currency) - Context Info
1291 *** e.g. Maintain Forecast(US OPS: USD) - Forecast Context Info
1292 ***
1293 *** C. MRC installed, Reporting Books
1294 *** Form Name (SOB Short Name: Reporting Currency) - Context Info
1295 *** e.g. Maintain Forecast(US OPS: EUR) - Forecast Context Info
1296 ***
1297 */
1298 IF (l_multi_org = 'N') THEN
1299
1300 select g.short_name ||
1301 decode(g.mrc_sob_type_code, 'N', NULL,
1302 decode(l_multi_cur, 'N', NULL,
1303 ': ' || g.currency_code))
1304 into l_wnd_context
1305 from gl_sets_of_books g
1306 , pa_implementations c
1307 where c.set_of_books_id = g.set_of_books_id;
1308
1309 /*
1310 ***
1311 *** Case #2 : Multi-Org
1312 ***
1313 *** A. MRC not installed, OR
1314 *** MRC installed, Non-Primary/Reporting Books
1315 *** Form Name (OU Name) - Context Info
1316 *** e.g. Maintain Forecast(US West) - Forecast Context Info
1317 ***
1318 *** B. MRC installed, Primary Books
1319 *** Form Name (OU Name: Primary Currency) - Context Info
1320 *** e.g. Maintain Forecast(US West: USD) - Forecast Context Info
1321 ***
1322 *** C. MRC installed, Reporting Books
1323 *** Form Name (OU Name: Reporting Currency) - Context Info
1324 *** e.g. Maintain Forecast(US West: EUR) - Forecast Context Info
1325 ***
1326 */
1327 ELSE
1328
1329 -- start 12i MOAC changes
1330 -- FND_PROFILE.GET ('ORG_ID', l_id);
1331 l_id := Pa_Moac_Utils.Get_Current_Org_Id;
1332 -- end 12i MOAC changes
1333
1334 /* Bug 2657833 - UTF8 change Impact, Using substr for organization name,
1335 selecting length 55 instead of 60 as 5 characters(for currency code) are concatenated here*/
1336 /* Bug6884654 - Changed substr to substrb */
1337 select substrb(h.name,1,55) ||
1338 decode(g.mrc_sob_type_code, 'N', NULL,
1339 decode(l_multi_cur, 'N', NULL,
1340 ': ' || g.currency_code))
1341 into l_wnd_context
1342 from gl_sets_of_books g
1343 , pa_implementations c
1344 , hr_operating_units h
1345 where h.organization_id = to_number(l_id)
1346 and c.set_of_books_id = g.set_of_books_id;
1347
1348
1349 END IF;
1350
1351 return l_wnd_context;
1352
1353
1354 END Get_Org_Window_Title;
1355
1356 ---------------------------------------------------------------
1357 -- Procedure : Get_Encoded_Msg
1358 -- This procedure serves as a wrapper to the function
1359 -- FND_MSG_PUB.Get. It is needed to access the call from
1360 -- client FORMS.
1361 ---------------------------------------------------------------
1362
1363 Procedure Get_Encoded_Msg(p_index IN NUMBER,
1364 p_msg_out IN OUT NOCOPY VARCHAR2 ) IS
1365 l_message VARCHAR2(2000);
1366 BEGIN
1367 p_msg_out := fnd_msg_pub.get(p_msg_index => p_index,
1368 p_encoded => FND_API.G_FALSE);
1369
1370 END Get_Encoded_Msg;
1371
1372
1373 ---------------------------------------------------------------
1374 -- Procedure : Add_Message
1375 -- This procedure serves as a wrapper to the FND_MEG_PUB
1376 -- procedures to add the specified message onto the message
1377 -- stack.
1378 -- 25-APR-02 MAnsari Modified call FND_MESSAGE.SET_NAME to
1379 -- use SUBSTR.
1380 ---------------------------------------------------------------
1381
1382 Procedure Add_Message( p_app_short_name IN VARCHAR2,
1383 p_msg_name IN VARCHAR2,
1384 p_token1 IN VARCHAR2 ,
1385 p_value1 IN VARCHAR2 ,
1386 p_token2 IN VARCHAR2 ,
1387 p_value2 IN VARCHAR2 ,
1388 p_token3 IN VARCHAR2 ,
1389 p_value3 IN VARCHAR2 ,
1390 p_token4 IN VARCHAR2 ,
1391 p_value4 IN VARCHAR2 ,
1392 p_token5 IN VARCHAR2 ,
1393 p_value5 IN VARCHAR2 ) IS
1394
1395 BEGIN
1396
1397 FND_MESSAGE.Set_Name(p_app_short_name, SUBSTR( p_msg_name, 1, 30 ));
1398 IF (p_token1 IS NOT NULL) THEN
1399 FND_MESSAGE.Set_Token(p_token1, p_value1);
1400 END IF;
1401 IF (p_token2 IS NOT NULL) THEN
1402 FND_MESSAGE.Set_Token(p_token2, p_value2);
1403 END IF;
1404 IF (p_token3 IS NOT NULL) THEN
1405 FND_MESSAGE.Set_Token(p_token3, p_value3);
1406 END IF;
1407 IF (p_token4 IS NOT NULL) THEN
1408 FND_MESSAGE.Set_Token(p_token4, p_value4);
1409 END IF;
1410 IF (p_token5 IS NOT NULL) THEN
1411 FND_MESSAGE.Set_Token(p_token5, p_value5);
1412 END IF;
1413
1414 FND_MSG_PUB.Add;
1415
1416 END Add_Message;
1417
1418
1419 --------------------------------------------------------------
1420 FUNCTION IsCrossBGProfile_WNPS
1421 RETURN VARCHAR2
1422
1423 IS
1424
1425 BEGIN
1426
1427 RETURN(FND_PROFILE.VALUE_WNPS('HR_CROSS_BUSINESS_GROUP'));
1428
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 RAISE ;
1432
1433 END IsCrossBGProfile_WNPS ;
1434 --------------------------------------------------------------
1435
1436 ---------------------------------------------------------------
1437 -- Function : Conv_Special_JS_Chars
1438 -- This function converts special characters in javascript link.
1439 -- Currently, this function only handles apostrophe sign.
1440 ---------------------------------------------------------------
1441 FUNCTION Conv_Special_JS_Chars(p_string in varchar2) RETURN VARCHAR2
1442 IS
1443
1444 BEGIN
1445
1446 RETURN icx_util.replace_quotes(p_string);
1447
1448 END Conv_Special_JS_Chars;
1449
1450 ---------------------------------------------------------------
1451 -- Function :Pa_Round_Currency
1452 -- This function rounds the amount to the required precsion based
1453 -- on the currency code
1454 ---------------------------------------------------------------
1455 FUNCTION Pa_Round_Currency
1456 (P_Amount IN NUMBER
1457 ,P_Currency_Code IN VARCHAR2)
1458 RETURN NUMBER IS
1459 l_rounded_amount NUMBER;
1460 BEGIN
1461
1462 SELECT decode(FC.minimum_accountable_unit,
1463 NULL, round(P_Amount, FC.precision),
1464 round(P_Amount/FC.minimum_accountable_unit) *
1465 FC.minimum_accountable_unit)
1466 INTO l_rounded_amount
1467 FROM fnd_currencies FC
1468 WHERE FC.currency_code = P_Currency_Code;
1469
1470 RETURN(l_rounded_amount);
1471
1472 EXCEPTION
1473
1474 WHEN NO_DATA_FOUND THEN
1475 RETURN (NULL);
1476 END PA_ROUND_CURRENCY;
1477
1478 ---------------------------------------------------------------
1479 FUNCTION get_party_id (
1480 p_user_id in number )
1481 return number
1482 IS
1483 Cursor external is
1484 select person_party_id from fnd_user -- For Bug 4527617.
1485 where user_id = p_user_id;
1486
1487 Cursor internal is
1488 select h.party_id
1489 from hz_parties h
1490 ,fnd_user f
1491 where h.orig_system_reference = CONCAT('PER:',f.employee_id)
1492 and f.user_id = p_user_id;
1493
1494 l_party_id number;
1495
1496 Begin
1497 Open internal;
1498 fetch internal into l_party_id;
1499 if (internal%NOTFOUND) then
1500 l_party_id := NULL;
1501 end if;
1502 close internal;
1503
1504 if (l_party_id IS NULL) then
1505 Open external;
1506 fetch external into l_party_id;
1507 if (external%NOTFOUND) then
1508 l_party_id := NULL;
1509 end if;
1510 close external;
1511 end if;
1512
1513 return l_party_id;
1514 Exception
1515 When others then
1516 RAISE;
1517 End get_party_id;
1518
1519 --
1520 /*==========================================================================*/
1521
1522 -- PROCEDURE GetEmpOrgJobId
1523 --
1524 /*==========================================================================*/
1525
1526 /* cwk changes : Modified Procedure to derive the organization and job for a contingent worker person Id also */
1527 /* cwk changes: Modified function to derive job id for the entered PO number and line - Bug 4044057 */
1528
1529 PROCEDURE GetEmpOrgJobId ( X_person_id IN NUMBER
1530 , X_date IN DATE
1531 , X_Emp_Org_Id OUT NOCOPY NUMBER
1532 , X_Emp_Job_Id OUT NOCOPY NUMBER
1533 , X_po_number IN VARCHAR2 DEFAULT NULL --Bug 4044057
1534 , X_po_line_num IN NUMBER DEFAULT NULL ) --Bug 4044057
1535 IS
1536 l_po_header_id NUMBER;
1537 l_po_line_id NUMBER;
1538 l_po_number VARCHAR2(20);
1539 l_po_line_num NUMBER;
1540 X_Cross_BG_Profile VARCHAR2(2); /*Bug 6355926*/
1541
1542 BEGIN
1543 X_Cross_BG_Profile := pa_utils.IsCrossBGProfile_WNPS; /*Bug 6355926*/
1544 If (G_PersonIdPrev = X_Person_Id AND
1545 trunc(G_DatePrev) = trunc(X_Date) AND
1546 x_po_number is null AND
1547 x_po_line_num is null) Then
1548
1549 X_Emp_Org_Id := G_EmpOrgId;
1550 X_Emp_Job_Id := G_EmpJobId;
1551
1552 Else
1553
1554 If x_po_number is not null then -- Bug 4044057
1555
1556 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
1557
1558 BEGIN
1559 select poh.segment1, pol.line_num
1560 into l_po_number, l_po_line_num
1561 from po_headers poh,
1562 po_lines pol
1563 where poh.po_header_id = pol.po_header_id
1564 and poh.po_header_id = l_po_header_id
1565 and pol.po_line_id = l_po_line_id;
1566 EXCEPTION /* Bug 6978184 : Added Exception Block */
1567 WHEN NO_DATA_FOUND THEN
1568 NULL;
1569 WHEN OTHERS THEN
1570 Raise;
1571 END;
1572
1573
1574 if (l_po_number <> x_po_number or l_po_line_num <> x_po_line_num) then
1575 /* Bug 6978184 : Added Query and IF below */
1576 select poh.po_header_id, pol.po_line_id
1577 into l_po_header_id, l_po_line_id
1578 from po_headers poh,
1579 po_lines pol
1580 where poh.po_header_id = pol.po_header_id
1581 and poh.type_lookup_code = 'STANDARD'
1582 and poh.segment1 = x_po_number
1583 and pol.line_num = x_po_line_num;
1584
1585 if NOT PO_PA_INTEGRATION_GRP.is_PO_active(X_person_id, X_date, l_po_header_id, l_po_line_id) then
1586 X_emp_job_id := NULL;
1587 X_Emp_Org_Id := NULL;
1588 RETURN;
1589 end if;
1590 end if;
1591
1592 end if; -- End of Bug 4044057
1593
1594 SELECT
1595 a.job_id,
1596 a.organization_id
1597 INTO
1598 X_emp_job_id,
1599 X_Emp_Org_Id
1600 FROM
1601 per_assignment_status_types s
1602 , per_all_assignments_f a -- modified for Bug 4699231
1603 , pa_implementations i
1604 WHERE
1605 a.job_id IS NOT NULL
1606 AND a.primary_flag = 'Y'
1607 AND trunc(X_date) BETWEEN trunc( a.effective_start_date )
1608 AND trunc( a.effective_end_date )
1609 AND a.person_id = X_person_id
1610 AND ((X_Cross_BG_Profile ='N' AND a.business_group_id = i.business_group_id) OR
1611 X_Cross_BG_Profile ='Y') /*bug6355926*/
1612 AND a.assignment_type in ('E', 'C')
1613 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
1614 AND s.assignment_status_type_id = a.assignment_status_type_id;
1615
1616 G_EmpOrgId := X_Emp_Org_Id;
1617 G_EmpJobID := X_Emp_Job_Id;
1618 G_PersonIdPrev := X_Person_Id;
1619 G_DatePrev := X_Date;
1620
1621 End If;
1622
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 G_PersonIdPrev := X_Person_Id;
1627 G_DatePrev := X_Date;
1628 X_emp_job_id := NULL;
1629 X_Emp_Org_Id := NULL;
1630
1631 END GetEmpOrgJobId;
1632
1633 -- ==========================================================================
1634 -- = FUNCTION NewGetWeekEnding
1635 -- ==========================================================================
1636
1637 FUNCTION NewGetWeekEnding ( X_date IN DATE ) RETURN DATE
1638 IS
1639
1640 X_week_ending DATE;
1641 X_week_ending_day VARCHAR2(80);
1642 X_week_ending_day_index number;
1643 x_week_start number; /*Bug 7601460 */
1644 l_Found BOOLEAN := FALSE;
1645
1646 BEGIN
1647
1648 Begin
1649
1650 select exp_cycle_start_day_code into x_week_start from pa_implementations; /*Bug 7601460 */
1651
1652 -- Check if there are any records in the pl/sql table.
1653 If G_WeekEndDateTab.COUNT > 0 Then
1654
1655 -- Get the Project Number from the pl/sql table.
1656 -- If there is no index with the value of the project_id passed
1657 -- in then an ora-1403: no_data_found is generated.
1658 X_Week_Ending := G_WeekEndDateTab(to_number(to_char(X_Date,'YYYYMMDD')||to_char(x_week_start))); /*Bug 7601460 */
1659 l_Found := TRUE;
1660
1661 End If;
1662
1663 Exception
1664 When No_Data_Found Then
1665 X_Week_Ending := null;
1666 l_Found := FALSE;
1667 When Others Then
1668 RAISE;
1669
1670 End;
1671
1672 If Not l_Found Then
1673
1674 -- Since the ei date has not been cached yet, will need to add it.
1675 -- So check to see if there are already 200 records in the pl/sql table.
1676 -- We don't want the pl/sql table to get large than 200 records.
1677 If G_WeekEndDateTab.COUNT > 200 Then
1678
1679 G_WeekEndDateTab.Delete;
1680
1681 End If;
1682
1683 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1684 into X_week_ending_day_index
1685 FROM pa_implementations;
1686
1687 select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
1688 into X_week_ending_day
1689 from dual;
1690
1691 SELECT Next_Day( trunc( X_date )-1, X_Week_Ending_Day ) /* BUG#3118592 */
1692 INTO X_Week_Ending
1693 FROM sys.dual;
1694
1695 -- Add the week ending date to the pl/sql table using the ei date
1696 -- as the index value.
1697 G_WeekEndDateTab(to_number(to_char(X_date,'YYYYMMDD')||to_char(x_week_start))) := X_Week_Ending; /*Bug 7601460 */
1698
1699 End If;
1700
1701 RETURN ( X_Week_Ending );
1702
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 RETURN ( NULL );
1706
1707 END NewGetWeekEnding;
1708
1709 /* Added for bug 5067511 */
1710
1711 -- ==========================================================================
1712 -- FUNCTION GetPersonInfo : Used only in the view PA_PROJECT_PLAYERS_V
1713 -- Returns the requested data in l_data. Returns 'NOT_FOUND' when no records
1714 -- found or invalid l_data
1715 -- This Function Can also be used whenever pa_employees is not scanned by index and we want to force index scan on pa_employees view.
1716 -- ==========================================================================
1717
1718 FUNCTION GetPersonInfo( p_person_id IN per_all_people_f.person_id%TYPE,
1719 p_data IN VARCHAR2 DEFAULT 'PERSON_ID') RETURN VARCHAR2
1720 IS
1721 BEGIN
1722 If NVL(L_PERSON_ID, 0) <> p_person_id Then
1723 Begin
1724 select PERSON_ID
1725 , FULL_NAME
1726 , LAST_NAME
1727 , FIRST_NAME
1728 , MIDDLE_NAMES
1729 , EMPLOYEE_NUMBER
1730 into
1731 L_PERSON_ID
1732 , L_PERSON_FULL_NAME
1733 , L_PERSON_LAST_NAME
1734 , L_PERSON_FIRST_NAME
1735 , L_PERSON_MIDDLE_NAMES
1736 , L_PERSON_EMPLOYEE_NUMBER
1737 from pa_employees where person_id = p_person_id;
1738 Exception
1739 When No_Data_Found Then
1740 RETURN('NOT_FOUND');
1741 END;
1742 End If;
1743
1744 If p_data = 'PERSON_ID' Then
1745 Return ('FOUND');
1746 End If;
1747 If p_data = 'FULL_NAME' Then
1748 Return (L_PERSON_FULL_NAME);
1749 End If;
1750 If p_data = 'LAST_NAME' Then
1751 Return (L_PERSON_LAST_NAME);
1752 End If;
1753 If p_data = 'FIRST_NAME' Then
1754 Return (L_PERSON_FIRST_NAME);
1755 End If;
1756 If p_data = 'MIDDLE_NAMES' Then
1757 Return (L_PERSON_MIDDLE_NAMES);
1758 End If;
1759 If p_data = 'EMPLOYEE_NUMBER' Then
1760 Return (L_PERSON_EMPLOYEE_NUMBER);
1761 End If;
1762
1763 Return ('NOT_FOUND');
1764 END GetPersonInfo;
1765
1766 END pa_utils;
[Home] [Help]