1 PACKAGE BODY PA_UTILS AS
2 /* $Header: PAXGUTLB.pls 120.30.12020000.5 2013/04/03 11:27:18 byeturi 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 AND o.business_group_id = X_bg_id)
327 OR (pa_utils.IsCrossBGProfile_WNPS = 'Y'));
328 -- AND o.business_group_id = X_bg_id ));Commented for bug16068318
329 Else
330 SELECT
331 organization_id
332 INTO
333 X_orgn_id
334 FROM
335 hr_organization_units o,
336 pa_implementations i
337 WHERE name = X_org_name
338 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
339 AND o.business_group_id = i.business_group_id)
340 OR (pa_utils.IsCrossBGProfile_WNPS = 'Y' ));
341 End If;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 X_Orgn_Id := NULL;
346 --return status not set for no_data_found
347 --since in trx import different messages are set depending on organization type
348 WHEN TOO_MANY_ROWS THEN
349 X_Return_Status := 'PA_TOO_MANY_ORGN';
350
351 END GetOrgnId;
352
353
354 -- ==========================================================================
355 -- = FUNCTION GetOrgName
356 -- ==========================================================================
357
358 FUNCTION GetOrgName ( X_org_id IN NUMBER ) RETURN VARCHAR2
359 IS
360 /* Bug No:- 2487147, UTF8 change : changed X_org_name to %TYPE */
361 /* X_org_name VARCHAR2(60); */
362 X_org_name hr_organization_units.name%TYPE;
363 BEGIN
364 SELECT
365 name
366 INTO
367 X_org_name
368 FROM
369 hr_organization_units o,
370 pa_implementations i
371 WHERE
372 organization_id = X_org_id
373 AND ((pa_utils.IsCrossBGProfile_WNPS = 'N'
374 AND o.business_group_id = i.business_group_id)
375 OR pa_utils.IsCrossBGProfile_WNPS = 'Y' )
376 ;
377
378
379 RETURN ( X_org_name );
380
381 EXCEPTION
382 WHEN OTHERS THEN
383 RETURN ( NULL );
384
385 END GetOrgName;
386
387 -- ==========================================================================
388 -- = FUNCTION GetWeekEnding
389 -- ==========================================================================
390
391 FUNCTION GetWeekEnding ( X_date IN DATE ) RETURN DATE
392 IS
393 X_week_ending DATE;
394 X_week_ending_day VARCHAR2(80);
395 X_week_ending_day_index number;
396 BEGIN
397
398 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
399 into X_week_ending_day_index
400 FROM pa_implementations;
401
402 select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
403 into X_week_ending_day from dual;
404
405 SELECT
406 next_day( trunc( X_date )-1, X_week_ending_day ) /* BUG#3118592 */
407 INTO
408 X_week_ending
409 FROM
410 sys.dual;
411
412 RETURN ( X_week_ending );
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 RETURN ( NULL );
417
418 END GetWeekEnding;
419
420
421 -- ==========================================================================
422 -- = FUNCTION DateInExpWeek
423 -- ==========================================================================
424
425 FUNCTION DateInExpWeek ( X_date IN DATE
426 , X_week_end IN DATE ) RETURN BOOLEAN
427 IS
428 dummy NUMBER;
429 BEGIN
430
431 IF (trunc(x_date) = G_PREV_DATE AND
432 trunc(x_week_end) = G_PREV_WEEK_END) THEN
433
434 IF G_PREV_DATEIN = 1 THEN
435 RETURN (TRUE);
436 ELSE
437 RETURN (FALSE);
438 END IF;
439
440 ELSE
441
442 G_PREV_DATE := trunc(x_date);
443 G_PREV_WEEK_END := trunc(x_week_end);
444
445 SELECT
446 count(1)
447 INTO
448 dummy
449 FROM
450 sys.dual
451 WHERE
452 trunc(X_date) BETWEEN trunc(trunc( X_week_end )-6 ) /* BUG#3118592 */
453 AND trunc( X_week_end );
454
455 IF ( dummy = 0 ) THEN
456 G_PREV_DATEIN := 0;
457 RETURN ( FALSE );
458 ELSE
459 G_PREV_DATEIN := 1;
460 RETURN ( TRUE );
461 END IF;
462
463 END IF;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467
468 G_PREV_DATE := trunc(x_date);
469 G_PREV_WEEK_END := trunc(x_week_end);
470 G_PREV_DATEIN := 0;
471 RETURN ( FALSE );
472
473 END DateInExpWeek;
474
475
476 -- ==========================================================================
477 -- = FUNCTION GetEmpOrgId
478 -- ==========================================================================
479
480 /* cwk changes : Modified function to derive the Organization Id for a Person Id of a contingent worker also*/
481
482 FUNCTION GetEmpOrgId ( X_person_id IN NUMBER
483 , X_date IN DATE ) RETURN NUMBER
484 IS
485 X_org_id NUMBER;
486 X_Cross_BG_Profile VARCHAR2(2); /* Added local variable for 3194743 */
487 X_business_group_id NUMBER; /* Added local variable for 3194743 */
488
489 -- bug 11741116 start
490 l_final_process_date per_periods_of_service.final_process_date%TYPE;
491
492 CURSOR cur_final_process_date (X_person_id NUMBER, X_date DATE) IS
493 SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
494 FROM per_periods_of_service
495 WHERE person_id = X_person_id
496 AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
497 -- bug 11741116 end
498 BEGIN
499
500 IF (x_person_id = G_PREV_PERSON_ID2 AND
501 trunc(x_date) = G_PREV_DATE4) THEN
502
503 RETURN (G_PREV_ORG_ID2);
504
505 ELSE
506
507 G_PREV_PERSON_ID2 := x_person_id;
508 G_PREV_DATE4 := x_date;
509
510 X_Cross_BG_Profile:= pa_utils.IsCrossBGProfile_WNPS; --Moved the function call from inside where clause to here
511
512 SELECT business_group_id --Moved selection of BG id from inside where clause to here
513 INTO X_business_group_id
514 FROM pa_implementations;
515
516 BEGIN --added in bug 11741116
517 SELECT
518 max(a.organization_id)
519 INTO
520 X_org_id
521 FROM
522 per_assignment_status_types s
523 , per_all_assignments_f a -- Modified for bug 4699231
524 WHERE
525 a.person_id = X_person_id
526 AND a.primary_flag = 'Y'
527 AND a.assignment_type in ('E', 'C')
528 AND a.assignment_status_type_id = s.assignment_status_type_id
529 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
530 AND trunc(X_date) BETWEEN trunc( a.effective_start_date ) /*Added trunc on X_Date for bug 8278399 */
531 AND trunc( a.effective_end_date )
532 /* Added for bug 2009830 */
533 AND ((X_Cross_BG_Profile = 'N'
534 AND X_business_group_id = a.business_group_id+0)
535 OR X_Cross_BG_Profile = 'Y' )
536 ; /*Bug 7645561 Changed the Query to include TERM_ASSIGN */
537 /*changes to bug 11741116 start*/
538 EXCEPTION WHEN NO_DATA_FOUND THEN
539
540 OPEN cur_final_process_date (X_person_id, X_date);
541 FETCH cur_final_process_date INTO l_final_process_date;
542 CLOSE cur_final_process_date;
543
544
545 SELECT
546 max(a.organization_id)
547 INTO
548 X_org_id
549 FROM
550 per_assignment_status_types s
551 , per_all_assignments_f a -- Modified for bug 4699231
552 WHERE
553 a.person_id = X_person_id
554 AND a.primary_flag = 'Y'
555 AND a.assignment_type in ('E', 'C')
556 AND a.assignment_status_type_id = s.assignment_status_type_id
557 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
558 AND trunc(X_date) BETWEEN trunc( a.effective_start_date ) /*Added trunc on X_Date for bug 8278399 */
559 AND trunc( l_final_process_date ) --added for 11741116
560 and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
561 /* Added for bug 2009830 */
562 AND ((X_Cross_BG_Profile = 'N'
563 AND X_business_group_id = a.business_group_id+0)
564 OR X_Cross_BG_Profile = 'Y' )
565 ; /*Bug 7645561 Changed the Query to include TERM_ASSIGN */
566 END;
567 /* changes to bug 11741116 end */
568 -- tsaifee 01/29/97 : Bug 442432 - Perf. for the above query.
569 -- the last line of the query modified : 0 added to a.business_group_id
570 -- so as not to use that index, as its use was giving an index merge.
571
572 G_PREV_ORG_ID2 := x_org_id;
573 RETURN ( X_org_id );
574
575 END IF;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 G_PREV_PERSON_ID2 := x_person_id;
580 G_PREV_DATE4 := x_date;
581 G_PREV_ORG_ID2 := NULL;
582 RETURN ( NULL );
583
584 END GetEmpOrgId;
585
586
587 -- ==========================================================================
588 -- = FUNCTION GetEmpCostRate
589 -- ==========================================================================
590
591 --
592 -- Date : 31-OCT-02
593 -- Updated By : Sandeep Bharathan
594 --
595 -- For any new functionality that requires the employee cost rate please
596 -- use PA_COST_RATE_PUB.GetEmpCostRate rather than using this function
597 --
598
599 FUNCTION GetEmpCostRate ( X_person_id IN NUMBER
600 , X_date IN DATE ) RETURN NUMBER
601 IS
602 X_cost_rate NUMBER(22,5);
603 l_job_id pa_expenditure_items_all.job_id%type;
604 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
605 l_start_date_active date;
606 l_end_date_active date;
607 l_organization_id number;
608 l_org_id number; /*2879644*/
609 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
610 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
611 l_override_type pa_compensation_details.override_type%type;
612 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
613 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
614 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
615 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
616 l_acct_cost_rate pa_compensation_details.acct_exchange_rate%type;
617 l_ot_project_id pa_projects_all.project_id%type;
618 l_ot_task_id pa_tasks.task_id%type;
619 l_err_code varchar2(200);
620 l_err_stage number;
621 l_return_value varchar2(100);
622
623 BEGIN
624
625 --
626 -- Changed for labor costing enhancements
627 --
628 PA_COST_RATE_PUB.get_labor_rate(p_person_id =>x_person_id
629 ,x_job_id =>l_job_id
630 ,p_calling_module =>'STAFFED'
631 ,p_org_id => l_org_id /*2879644*/
632 ,p_txn_date =>x_date
633 ,x_organization_id =>l_organization_id
634 ,x_cost_rate =>x_cost_rate
635 ,x_start_date_active =>l_start_date_active
636 ,x_end_date_active =>l_end_date_active
637 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
638 ,x_costing_rule =>l_costing_rule
639 ,x_rate_sch_id =>l_rate_sch_id
640 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
641 ,x_acct_rate_type =>l_acct_rate_type
642 ,x_acct_rate_date_code =>l_acct_rate_date_code
643 ,x_acct_exch_rate =>l_acct_exch_rate
644 ,x_ot_project_id =>l_ot_project_id
645 ,x_ot_task_id =>l_ot_task_id
646 ,x_err_stage =>l_err_stage
647 ,x_err_code =>l_err_code
648 );
649
650 /*
651 SELECT
652 cd.hourly_cost_rate
653 INTO
654 X_cost_rate
655 FROM
656 pa_compensation_details cd
657 WHERE
658 cd.person_id = X_person_id
659 AND X_date BETWEEN cd.start_date_active
660 AND nvl( cd.end_date_active, X_date );
661 */
662 RETURN ( X_cost_rate );
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN ( NULL );
667
668 END GetEmpCostRate;
669
670
671
672 -- ==========================================================================
673 -- = FUNCTION GetExpTypeCostRate
674 -- ==========================================================================
675
676 FUNCTION GetExpTypeCostRate ( X_expenditure_type IN VARCHAR2
677 , X_date IN DATE ) RETURN NUMBER
678 IS
679 X_exp_type_cost_rate NUMBER(22,5);
680 BEGIN
681 SELECT
682 nvl( r.cost_rate, 1 )
683 INTO
684 X_exp_type_cost_rate
685 FROM
686 pa_expenditure_cost_rates r
687 WHERE
688 r.expenditure_type = X_expenditure_type
689 AND X_date BETWEEN r.start_date_active
690 AND nvl( r.end_date_active, X_date );
691
692 RETURN ( X_exp_type_cost_rate );
693
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 RETURN ( 1 );
697 WHEN OTHERS THEN
698 RETURN ( NULL );
699
700 END GetExpTypeCostRate;
701
702
703
704 -- ==========================================================================
705 -- = FUNCTION GetEmpJobId
706 -- ==========================================================================
707
708 /* cwk changes: Modified function to derive the job id for a contingent worker person id also */
709 /* cwk changes: Modified function to derive job id for the entered PO number and line - Bug 4044057 */
710
711 FUNCTION GetEmpJobId ( X_person_id IN NUMBER
712 , X_date IN DATE
713 , X_person_type IN VARCHAR2 DEFAULT NULL
714 , X_po_number IN VARCHAR2 DEFAULT NULL
715 , X_po_line_num IN NUMBER DEFAULT NULL
716 , X_po_header_id IN NUMBER DEFAULT NULL
717 , X_po_line_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
718 IS
719 X_emp_job_id NUMBER;
720 X_Cross_BG_Profile VARCHAR2(2); /* Added local variable for 3194743 */
721 X_business_group_id NUMBER; /* Added local variable for 3194743 */
722 l_person_type VARCHAR2(1) ;
723 l_assignment_status VARCHAR2(20);
724 l_po_header_id NUMBER;
725 l_po_line_id NUMBER;
726 l_po_number VARCHAR2(20);
727 l_po_line_num NUMBER;
728 -- bug 11741116 start
729 l_final_process_date per_periods_of_service.final_process_date%TYPE;
730 l_actual_termination_date per_periods_of_service.actual_termination_date%type; --Bug 13843625
731
732 CURSOR cur_final_process_date (X_person_id NUMBER, X_date DATE) IS
733 SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
734 FROM per_periods_of_service
735 WHERE person_id = X_person_id
736 AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
737 -- bug 11741116 end
738
739 /* Bug 13843625: Added cursor cur_actual_termination_date */ /*Modified below cursor in reference to BUG#14698048*/
740 CURSOR cur_actual_termination_date (X_person_id NUMBER, X_date DATE) IS
741 SELECT MAX(nvl(actual_termination_date+1,to_date('4712/12/31', 'YYYY/MM/DD')))
742 FROM per_periods_of_service
743 WHERE person_id = X_person_id
744 AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
745
746 BEGIN
747 OPEN cur_final_process_date (X_person_id, X_date); --Bug 13843625
748 FETCH cur_final_process_date INTO l_final_process_date;
749 CLOSE cur_final_process_date;
750
751 OPEN cur_actual_termination_date (X_person_id, X_date); -- Bug 13843625 /*BUG#14698048*/
752 FETCH cur_actual_termination_date INTO l_actual_termination_date;
753 CLOSE cur_actual_termination_date;
754
755 If x_person_type is NOT NULL then
756 if x_person_type = 'EMP' then
757 l_person_type := 'E' ;
758 /* Bug 13843625: Added if condition for terminated employees */
759 If X_date BETWEEN trunc( l_actual_termination_date ) AND trunc( l_final_process_date ) then
760 l_assignment_status := 'TERM_ASSIGN' ;
761 else
762 l_assignment_status := 'ACTIVE_ASSIGN' ;
763 end if;
764 else
765 l_person_type := 'C';
766 l_assignment_status := 'ACTIVE_CWK' ;
767 end if;
768 end if ;
769
770 IF (x_person_id = G_PREV_PERSON_ID AND
771 trunc(x_date) = G_PREV_DATE3 AND
772 x_po_number is null AND
773 x_po_header_id is null) THEN
774
775 RETURN (G_PREV_EMPJOB_ID);
776
777 ELSE
778
779 G_PREV_PERSON_ID := x_person_id;
780 G_PREV_DATE3 := trunc(x_date);
781
782 X_Cross_BG_Profile:= pa_utils.IsCrossBGProfile_WNPS; --Moved the function call from inside where clause to here
783
784 SELECT business_group_id --Moved selection of BG id from inside where clause to here
785 INTO X_business_group_id
786 FROM pa_implementations;
787
788 If x_po_header_id is not null then --Bug 4044057
789
790 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
791 /* Bug 6978184 : Added Condition (and not...) */
792 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
793 RETURN( NULL );
794 end if;
795
796 elsIf x_po_number is not null then --Bug 4044057
797
798 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
799
800 BEGIN
801 select poh.segment1, pol.line_num
802 into l_po_number, l_po_line_num
803 from po_headers poh,
804 po_lines pol
805 where poh.po_header_id = pol.po_header_id
806 and poh.po_header_id = l_po_header_id
807 and pol.po_line_id = l_po_line_id;
808 EXCEPTION /* Bug 6978184 : Added Exception Block */
809 WHEN NO_DATA_FOUND THEN
810 NULL;
811 WHEN OTHERS THEN
812 Raise;
813 END;
814
815
816 if (l_po_number <> x_po_number or l_po_line_num <> x_po_line_num ) then
817 /* Bug 6978184 : Added Query and IF below */
818 select poh.po_header_id, pol.po_line_id
819 into l_po_header_id, l_po_line_id
820 from po_headers poh,
821 po_lines pol
822 where poh.po_header_id = pol.po_header_id
823 and poh.type_lookup_code = 'STANDARD'
824 and poh.segment1 = x_po_number
825 and pol.line_num = x_po_line_num;
826
827 if NOT PO_PA_INTEGRATION_GRP.is_PO_active(X_person_id, X_date, l_po_header_id, l_po_line_id) then
828 RETURN( NULL );
829 end if;
830 end if;
831
832 end if; -- End of Bug 4044057
833
834
835 If x_person_type IS NULL then
836 BEGIN --added in bug 11741116
837 SELECT
838 max(a.job_id)
839 INTO
840 X_emp_job_id
841 FROM
842 per_assignment_status_types s
843 , per_all_assignments_f a -- Modified for bug 4699231
844 WHERE
845 a.job_id IS NOT NULL
846 AND a.primary_flag = 'Y'
847 AND X_date BETWEEN trunc( a.effective_start_date )
848 AND trunc( a.effective_end_date )
849
850 AND a.person_id = X_person_id
851 AND a.assignment_type in ('E', 'C')
852 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
853 AND s.assignment_status_type_id = a.assignment_status_type_id
854 /* Added for bug 2009830 */
855 AND (( X_Cross_BG_Profile = 'N'
856 AND X_business_group_id = a.business_group_id+0)
857 OR X_Cross_BG_Profile = 'Y' ) ;
858 /*changes to bug 11741116 start */
859 EXCEPTION WHEN NO_DATA_FOUND THEN
860
861 OPEN cur_final_process_date (X_person_id, X_date);
862 FETCH cur_final_process_date INTO l_final_process_date;
863 CLOSE cur_final_process_date;
864
865 SELECT
866 max(a.job_id)
867 INTO
868 X_emp_job_id
869 FROM
870 per_assignment_status_types s
871 , per_all_assignments_f a -- Modified for bug 4699231
872 WHERE
873 a.job_id IS NOT NULL
874 AND a.primary_flag = 'Y'
875 AND X_date BETWEEN trunc( a.effective_start_date )
876 AND trunc( l_final_process_date )
877 and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
878 AND a.person_id = X_person_id
879 AND a.assignment_type in ('E', 'C')
880 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
881 AND s.assignment_status_type_id = a.assignment_status_type_id
882 /* Added for bug 2009830 */
883 AND (( X_Cross_BG_Profile = 'N'
884 AND X_business_group_id = a.business_group_id+0)
885 OR X_Cross_BG_Profile = 'Y' ) ;
886 END;/*changes to bug 11741116 end */
887 G_PREV_EMPJOB_ID := x_emp_job_id;
888 RETURN( X_emp_job_id );
889 /* Bug 7645561 Changed the Query to include TERM_ASSIGN */
890
891 else
892 BEGIN --added in bug 11741116
893 SELECT
894 a.job_id
895 INTO
896 X_emp_job_id
897 FROM
898 per_assignment_status_types s
899 , per_all_assignments_f a -- for Bug 4699231
900 WHERE
901 a.job_id IS NOT NULL
902 AND a.primary_flag = 'Y'
903 AND X_date BETWEEN trunc( a.effective_start_date )
904 AND trunc( a.effective_end_date )
905 AND a.person_id = X_person_id
906 -- AND a.assignment_type in ('E', 'C') -- commented out for bug : 3568109
907 AND a.assignment_type = l_person_type
908 -- AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- commented out for bug : 3568109
909 AND s.per_system_status = l_assignment_status
910 AND s.assignment_status_type_id = a.assignment_status_type_id
911 /* Added for bug 2009830 */
912 AND (( X_Cross_BG_Profile = 'N'
913 AND X_business_group_id = a.business_group_id+0)
914 OR X_Cross_BG_Profile = 'Y' ) ;
915 /*changes to 11741116 start */
916 EXCEPTION WHEN NO_DATA_FOUND THEN
917
918 OPEN cur_final_process_date (X_person_id, X_date);
919 FETCH cur_final_process_date INTO l_final_process_date;
920 CLOSE cur_final_process_date;
921
922
923 SELECT
924 a.job_id
925 INTO
926 X_emp_job_id
927 FROM
928 per_assignment_status_types s
929 , per_all_assignments_f a -- for Bug 4699231
930 WHERE
931 a.job_id IS NOT NULL
932 AND a.primary_flag = 'Y'
933 AND X_date BETWEEN trunc( a.effective_start_date )
934 AND trunc( l_final_process_date )
935 and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
936 AND a.person_id = X_person_id
937 -- AND a.assignment_type in ('E', 'C') -- commented out for bug : 3568109
938 AND a.assignment_type = l_person_type
939 -- AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') -- commented out for bug : 3568109
940 AND s.per_system_status = l_assignment_status
941 AND s.assignment_status_type_id = a.assignment_status_type_id
942 /* Added for bug 2009830 */
943 AND (( X_Cross_BG_Profile = 'N'
944 AND X_business_group_id = a.business_group_id+0)
945 OR X_Cross_BG_Profile = 'Y' ) ;
946 END;/*changes to 11741116 start */
947 G_PREV_EMPJOB_ID := x_emp_job_id;
948 RETURN( X_emp_job_id );
949
950 end if ; -- end if for x_person_type
951 END IF;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 G_PREV_PERSON_ID := x_person_id;
956 G_PREV_DATE3 := x_date;
957 G_PREV_EMPJOB_ID := NULL;
958 RETURN( NULL );
959
960 END GetEmpJobId;
961
962 -- ==========================================================================
963 -- 10253400: Payroll integration enhancement for 12.2.
964 -- New function GetLocationId added
965 -- ==========================================================================
966 FUNCTION GetAsgnLocationId ( p_person_id IN NUMBER
967 , p_ei_date IN DATE)
968 return number IS
969 l_location_id NUMBER;
970 BEGIN
971
972 select location_id
973 into l_location_id
974 from per_all_assignments_f
975 where person_id = p_person_id
976 and p_ei_date between effective_start_date and effective_end_date
977 and primary_flag = 'Y';
978
979 return l_location_id;
980
981 EXCEPTION
982 when no_data_found then
983 return null;
984 -- raise an error here.
985 END GetAsgnLocationId;
986
987 -- ==========================================================================
988 -- 10253400: Payroll integration enhancement for 12.2.
989 -- New function GetLocationId added
990 -- ==========================================================================
991 FUNCTION GetLocationId ( p_location_id IN NUMBER DEFAULT NULL
992 , p_location_code IN VARCHAR2 DEFAULT NULL
993 , X_Date IN DATE)
994 return number IS
995 l_location_id NUMBER;
996 BEGIN
997 IF p_location_id is not null then
998 select l.location_id
999 into l_location_id
1000 from hr_locations l
1001 where X_Date < nvl(l.inactive_date, to_date('4712/12/31', 'YYYY/MM/DD'))
1002 and (l.location_use = 'HR')
1003 and location_id = p_location_id;
1004 ELSE
1005 select l.location_id
1006 into l_location_id
1007 from hr_locations l
1008 where X_Date < nvl(l.inactive_date, to_date('4712/12/31', 'YYYY/MM/DD'))
1009 and (l.location_use = 'HR')
1010 and location_code = p_location_code;
1011 END IF;
1012
1013 return l_location_id;
1014
1015 EXCEPTION
1016 when no_data_found then
1017 return null;
1018 -- raise an error here.
1019 END GetLocationId;
1020 -- ==========================================================================
1021 -- 10253400: Payroll integration enhancement for 12.2.
1022 -- New function getJobId
1023 -- ==========================================================================
1024 /* Start changes for Bug#12646514 */
1025
1026 FUNCTION GetJobId ( X_BUSINESS_GROUP_ID IN NUMBER DEFAULT NULL
1027 , X_job_id IN NUMBER DEFAULT NULL
1028 , X_job_name IN VARCHAR2 DEFAULT NULL
1029 , X_date IN DATE) RETURN NUMBER
1030 IS
1031 l_job_id number;
1032 BEGIN
1033 If X_job_Id is not null then
1034 select job_id
1035 into l_job_id
1036 from per_jobs_v V
1037 where job_id = X_job_id
1038 and ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' AND
1039 fnd_profile.value('PER_BUSINESS_GROUP_ID') = V.BUSINESS_GROUP_ID)
1040 OR
1041 PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y'
1042 )
1043 and date_from <= X_date
1044 and ((date_to is null) or (date_to >= X_date));
1045 else
1046 select job_id
1047 into l_job_id
1048 from per_jobs_v V
1049 where name = X_job_name
1050 AND V.BUSINESS_GROUP_ID = NVL(X_Business_Group_Id,V.BUSINESS_GROUP_ID)
1051 /* and ((PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' AND
1052 fnd_profile.value('PER_BUSINESS_GROUP_ID') = V.BUSINESS_GROUP_ID)
1053 OR
1054 PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'Y'
1055 )*/
1056 and date_from <= X_date
1057 and ((date_to is null) or (date_to >= X_date));
1058 end if;
1059 return l_job_id;
1060
1061 EXCEPTION
1062 when no_data_found then
1063 pa_utils.G_return_status := 'NO_ASSIGNMENT';
1064 return NULL;
1065 -- raise; -- should we handle it?
1066 when too_many_rows then
1067 pa_utils.G_return_status := 'PA_TOO_MANY_JOBS';
1068 return NULL;
1069 -- raise; -- should we handle it?
1070 END getJobId;
1071
1072 -- ==========================================================================
1073 -- = FUNCTION GetNextEiId
1074 -- ==========================================================================
1075
1076 FUNCTION GetNextEiId RETURN NUMBER
1077 IS
1078 X_expenditure_item_id NUMBER(15);
1079 BEGIN
1080 SELECT
1081 pa_expenditure_items_s.nextval
1082 INTO
1083 X_expenditure_item_id
1084 FROM
1085 sys.dual;
1086
1087 RETURN( X_expenditure_item_id );
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 RETURN( NULL );
1092
1093 END GetNextEiId;
1094
1095 -- ==========================================================================
1096 -- = FUNCTION CheckExpTypeActive
1097 -- ==========================================================================
1098
1099 FUNCTION CheckExpTypeActive( X_expenditure_type IN VARCHAR2
1100 , X_date IN DATE )
1101 RETURN BOOLEAN
1102 IS
1103 dummy NUMBER;
1104 BEGIN
1105 SELECT
1106 count(*)
1107 INTO
1108 dummy
1109 FROM
1110 pa_expenditure_types et
1111 WHERE
1112 et.expenditure_type = X_expenditure_type
1113 AND X_date BETWEEN et.start_date_active
1114 AND nvl( et.end_date_active, X_date );
1115
1116 IF ( dummy = 0 ) THEN
1117 RETURN ( FALSE );
1118 ELSE
1119 RETURN ( TRUE );
1120 END IF;
1121
1122 END CheckExpTypeActive;
1123
1124
1125 -- ==========================================================================
1126 -- = FUNCTION get_org_hierarchy_top
1127 -- ==========================================================================
1128
1129 FUNCTION get_org_hierarchy_top ( X_org_structure_version_id IN NUMBER )
1130 RETURN NUMBER
1131 IS
1132 X_top_org_id NUMBER(15);
1133 BEGIN
1134 -- index on org_structure_version_id is turned off assuming there won't be
1135 -- that many version of org hierarchy. Hence better performance.
1136 -- added the below optimiser hint based on the suggestion of performance team for bug 2474299
1137 SELECT /*+ index_ffs(se1 PER_ORG_STRUCTURE_ELEMENTS_N50) */
1138 DISTINCT
1139 se1.organization_id_parent
1140 INTO
1141 X_top_org_id
1142 FROM
1143 per_org_structure_elements se1
1144 WHERE
1145 se1.org_structure_version_id||'' = X_org_structure_version_id
1146 AND NOT exists
1147 ( SELECT null
1148 FROM per_org_structure_elements se2
1149 WHERE se2.org_structure_version_id = X_org_structure_version_id
1150 AND se2.organization_id_child = se1.organization_id_parent );
1151
1152 RETURN( X_top_org_id );
1153
1154 END get_org_hierarchy_top;
1155
1156
1157 -- ==========================================================================
1158 -- = FUNCTION business_group_id
1159 -- ==========================================================================
1160
1161 FUNCTION business_group_id RETURN NUMBER
1162 IS
1163 X_business_group_id NUMBER(15);
1164 BEGIN
1165 SELECT DISTINCT business_group_id /*Distinct added for Bug 6043451*/
1166 INTO X_business_group_id
1167 FROM pa_implementations;
1168
1169 RETURN(X_business_group_id);
1170
1171 END business_group_id;
1172
1173 -- ==========================================================================
1174 -- FUNCTION Get_business_group_id
1175 -- ==========================================================================
1176
1177 FUNCTION Get_business_group_id RETURN NUMBER
1178 IS
1179 l_business_group_id NUMBER;
1180 BEGIN
1181 IF G_Business_Group_Id IS NULL THEN
1182 l_business_group_id := pa_utils.business_group_id;
1183 ELSE
1184 l_business_group_id := G_Business_Group_Id;
1185 END IF;
1186
1187 RETURN l_business_group_id;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN RAISE;
1191
1192 END Get_business_group_id;
1193
1194 -- ==========================================================================
1195 -- PROCEDURE Set_business_group_id
1196 -- ==========================================================================
1197
1198 PROCEDURE Set_business_group_id
1199 IS
1200 BEGIN
1201 G_Business_Group_Id := pa_utils.business_group_id;
1202
1203 END Set_business_group_id;
1204
1205 -- ==========================================================================
1206 -- = FUNCTION is_project_costing_installed
1207 -- ==========================================================================
1208
1209 FUNCTION is_project_costing_installed RETURN VARCHAR2
1210 IS
1211 x_pa_costing_installed VARCHAR2(2);
1212 BEGIN
1213
1214 if (fnd_profile.value('PA_PROJECT_COSTING_INSTALLED') = 'Y') then
1215 x_pa_costing_installed := 'Y';
1216 else
1217 x_pa_costing_installed := 'N';
1218 end if;
1219 return(x_pa_costing_installed);
1220
1221 EXCEPTION
1222 when OTHERS then
1223 return('N');
1224 END is_project_costing_installed;
1225
1226 -- ==========================================================================
1227 -- = FUNCTION IsCrossChargeable
1228 -- ==========================================================================
1229
1230 FUNCTION IsCrossChargeable( X_Project_Id Number ) RETURN BOOLEAN
1231 IS
1232
1233 dummy NUMBER(15);
1234 BEGIN
1235
1236 IF (x_project_id = G_PREV_PROJ_ID) THEN
1237
1238 IF G_PREV_CHARGE = 1 then
1239 RETURN TRUE;
1240 ELSE
1241 RETURN FALSE;
1242 END IF;
1243
1244 ELSE
1245
1246 Select 1
1247 Into dummy
1248 From sys.dual
1249 Where exists
1250 ( Select null
1251 From pa_projects_expend_v p
1252 Where p.project_Id = X_project_Id );
1253
1254 G_PREV_PROJ_ID := x_project_id;
1255
1256 If dummy = 1 then
1257 G_PREV_CHARGE := 1;
1258 Return TRUE ;
1259 ELSE
1260 G_PREV_CHARGE := 0;
1261 RETURN FALSE;
1262 End if;
1263
1264 END IF;
1265
1266 EXCEPTION
1267 WHEN NO_DATA_FOUND THEN
1268 G_PREV_PROJ_ID := x_project_id;
1269 G_PREV_CHARGE := 0;
1270 return FALSE;
1271 WHEN OTHERS THEN
1272 Return TRUE ;
1273 END ;
1274
1275
1276 -- ==========================================================================
1277 -- = FUNCTION pa_morg_implemented
1278 -- ==========================================================================
1279
1280 FUNCTION pa_morg_implemented RETURN VARCHAR2
1281 IS
1282 x_dummy VARCHAR2(1);
1283
1284 BEGIN
1285
1286 SELECT 'Y'
1287 INTO x_dummy
1288 FROM sys.dual
1289 WHERE EXISTS (
1290 SELECT NULL
1291 FROM pa_implementations_all
1292 WHERE org_id IS NOT NULL );
1293
1294 IF ( x_dummy = 'Y' ) THEN
1295 RETURN( 'Y' );
1296 ELSE
1297 RETURN( 'N' );
1298 END IF;
1299
1300 EXCEPTION
1301 WHEN NO_DATA_FOUND THEN
1302 RETURN( 'N' );
1303
1304
1305 END pa_morg_implemented;
1306
1307
1308 -- ==========================================================================
1309 -- = FUNCTION CheckProjectOrg
1310 -- ==========================================================================
1311
1312 FUNCTION CheckProjectOrg (x_org_id IN NUMBER) RETURN VARCHAR2 IS
1313
1314 -- This function returns 'Y' if a given org is a project organization ,
1315 -- otherwise , it returns 'N'
1316
1317 CURSOR l_proj_org_csr IS
1318 SELECT DISTINCT 'x'
1319 FROM pa_organizations_proj_all_bg_v
1320 WHERE organization_id = x_org_id;
1321
1322 l_dummy VARCHAR2(1);
1323 BEGIN
1324
1325 OPEN l_proj_org_csr;
1326 FETCH l_proj_org_csr INTO l_dummy;
1327 IF l_proj_org_csr%NOTFOUND THEN
1328 CLOSE l_proj_org_csr;
1329 RETURN 'N';
1330 ELSE
1331 CLOSE l_proj_org_csr;
1332 RETURN 'Y';
1333 END IF;
1334
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 RETURN 'N';
1338 END CheckProjectOrg;
1339
1340 ----------------------------------------------------------------------
1341 -- Function : get_pa_date
1342 -- Derive PA date from GL date and ei date .
1343 -- This function accepts the expenditure item date and the GL date
1344 -- and derives the period name based on this. This is mainly used
1345 -- for AP invoices and transactions imported from other systems
1346 -- where the GL date is known in advance and the PA date has to
1347 -- be determined. In the current logic, the PA date is derived solely
1348 -- based on the EI date. The GL date which is passed as a parameter is
1349 -- ignored. However, it is still retained as a parameter in case the
1350 -- logic for the derivation of the PA date is changed on a later date.
1351 -----------------------------------------------------------------------
1352
1353 FUNCTION get_pa_date( x_ei_date IN date, x_gl_date IN date ) return date
1354 IS
1355 l_pa_date date ;
1356 BEGIN
1357
1358 -- The PA date is derived solely from the EI date as the earliest open
1359 -- or future period on or after the EI date...sparames Nov 14, 1997
1360
1361 SELECT MIN(pap.end_date)
1362 INTO l_pa_date
1363 FROM pa_periods pap
1364 WHERE status in ('O','F')
1365 AND pap.end_date >= x_ei_date;
1366
1367 return(l_pa_date) ;
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 l_pa_date :=NULL ;
1372 WHEN OTHERS THEN
1373 RAISE ;
1374 END get_pa_date ;
1375
1376 ----------------------------------------------------------------------
1377 -- Function : get_pa_end_date
1378 -- Derive the period end date based on the period name
1379 --
1380 -- This function accepts the period name and gets the period end
1381 -- date from the pa_periods table. The function created for
1382 -- burden cost accounting.
1383 -- Created by Sandeep 04-MAR-1998
1384 -----------------------------------------------------------------------
1385
1386 FUNCTION get_pa_end_date( x_pa_period_name IN VARCHAR2 ) return date
1387 IS
1388 l_pa_end_date date ;
1389 BEGIN
1390
1391 SELECT pap.end_date
1392 INTO l_pa_end_date
1393 FROM pa_periods pap
1394 WHERE pap.period_name = x_pa_period_name;
1395
1396 return(l_pa_end_date) ;
1397
1398 EXCEPTION
1399 WHEN NO_DATA_FOUND THEN
1400 l_pa_end_date :=NULL ;
1401 WHEN OTHERS THEN
1402 RAISE ;
1403 END get_pa_end_date ;
1404
1405 ------------------------------------------------------------------
1406 -- Function : get_pa_period_name
1407 -- Derive PA date from GL date and ei date .
1408 -- This function accepts the expenditure item date and the GL date
1409 -- and derives the period name based on this. This is mainly used
1410 -- for AP invoices and transactions imported from other systems
1411 -- where the GL date is known in advance and the PA date has to
1412 -- be determined. This function is identical to the
1413 -- pa_date_from_gl_date function except that it returns the
1414 -- corresponding period name instead of the PA date
1415 -------------------------------------------------------------------
1416
1417 FUNCTION get_pa_period_name( x_ei_date IN date, x_gl_date IN date ) return varchar2
1418 IS
1419 l_period_name pa_periods_all.period_name%TYPE;
1420 BEGIN
1421
1422 -- The PA date is derived as the end date of the earliest open or
1423 -- future PA period on or after the EI date. The GL date which is
1424 -- passed as a parameter to this function is not used at present but
1425 -- is retained for future use...sparames Nov 14,1997
1426
1427 SELECT pa_periods.period_name
1428 INTO l_period_name
1429 FROM pa_periods
1430 WHERE pa_periods.end_Date =
1431 (SELECT MIN(pap.end_date)
1432 FROM pa_periods pap
1433 WHERE status in ('O','F')
1434 AND pap.end_date >= x_ei_date)
1435 AND status in ('O','F'); /* Added the check for bug #1550929 */
1436
1437 return(l_period_name) ;
1438
1439 EXCEPTION
1440 WHEN NO_DATA_FOUND THEN
1441 l_period_name :=NULL ;
1442 WHEN OTHERS THEN
1443 RAISE ;
1444 END get_pa_period_name;
1445
1446 -- ==========================================================================
1447 -- = FUNCTION GetETypeClassCode
1448 -- ==========================================================================
1449
1450 FUNCTION GetETypeClassCode ( X_system_linkage IN VARCHAR2) RETURN VARCHAR2
1451 IS
1452 etypeclass_code VARCHAR2(3) ;
1453 BEGIN
1454
1455 IF (X_system_linkage = G_PREV_SYS_LINK) THEN
1456
1457 RETURN (G_PREV_FUNCTION);
1458 ELSE
1459
1460 G_PREV_SYS_LINK := X_system_linkage;
1461
1462 SELECT function
1463 INTO etypeclass_code
1464 FROM pa_system_linkages
1465 WHERE function = X_system_linkage ;
1466
1467 G_PREV_FUNCTION := etypeclass_code;
1468 RETURN( etypeclass_code ) ;
1469
1470 END IF;
1471
1472 EXCEPTION
1473 WHEN NO_DATA_FOUND THEN
1474 G_PREV_SYS_LINK := X_system_linkage;
1475 G_PREV_FUNCTION := NULL;
1476 etypeclass_code := NULL ;
1477 RETURN( etypeclass_code ) ;
1478 END GetEtypeClassCode;
1479
1480 -- ==========================================================================
1481 -- = FUNCTION Get_Org_Window_Title
1482 -- ==========================================================================
1483 /* These comments are added for bug 1812275.
1484 Please have a look at the solution provided in this bug if you face any issue
1485 with this procedure like no_data_found in pa_implementations.
1486 We are not suppressing any exceptions because and exception should be raised
1487 if there are no records found in pa_implementations
1488 */
1489
1490
1491 FUNCTION Get_Org_Window_Title RETURN VARCHAR2
1492 IS
1493
1494 l_multi_org VARCHAR2(1);
1495 l_multi_cur VARCHAR2(1);
1496 /* Bug 2657833 - UTF8 change Impact */
1497 /* l_wnd_context VARCHAR2(80); */
1498 l_wnd_context HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
1499 l_id VARCHAR2(15);
1500
1501 BEGIN
1502
1503 /*
1504 ***
1505 *** Get multi-org and MRC information on the current
1506 *** product installation.
1507 ***
1508 */
1509
1510 SELECT nvl(multi_org_flag, 'N')
1511 ---- Bug#MRC_SCHEMA_ELIM , nvl(multi_currency_flag, 'N')
1512 INTO l_multi_org
1513 ---, l_multi_cur
1514 FROM fnd_product_groups;
1515
1516 --Added this check that does the same job as the above select
1517 If gl_mc_info.alc_enabled(275) Then -- 12i MOAC changes
1518 -- IF gl_mc_info.mrc_enabled(275) THEN -- 12i MOAC changes
1519 l_multi_cur := 'Y';
1520 ELSE
1521 l_multi_cur := 'N';
1522 END IF;
1523
1524 /*
1525 ***
1526 *** Case #1 : Non-Multi-Org or Multi-SOB
1527 ***
1528 *** A. MRC not installed, OR
1529 *** MRC installed, Non-Primary/Reporting Books
1530 *** Form Name (SOB Short Name) - Context Info
1531 *** e.g. Maintain Forecast(US OPS) - Forecast Context Info
1532 ***
1533 *** B. MRC installed, Primary Books
1534 *** Form Name (SOB Short Name: Primary Currency) - Context Info
1535 *** e.g. Maintain Forecast(US OPS: USD) - Forecast Context Info
1536 ***
1537 *** C. MRC installed, Reporting Books
1538 *** Form Name (SOB Short Name: Reporting Currency) - Context Info
1539 *** e.g. Maintain Forecast(US OPS: EUR) - Forecast Context Info
1540 ***
1541 */
1542 IF (l_multi_org = 'N') THEN
1543
1544 select g.short_name ||
1545 decode(g.mrc_sob_type_code, 'N', NULL,
1546 decode(l_multi_cur, 'N', NULL,
1547 ': ' || g.currency_code))
1548 into l_wnd_context
1549 from gl_sets_of_books g
1550 , pa_implementations c
1551 where c.set_of_books_id = g.set_of_books_id;
1552
1553 /*
1554 ***
1555 *** Case #2 : Multi-Org
1556 ***
1557 *** A. MRC not installed, OR
1558 *** MRC installed, Non-Primary/Reporting Books
1559 *** Form Name (OU Name) - Context Info
1560 *** e.g. Maintain Forecast(US West) - Forecast Context Info
1561 ***
1562 *** B. MRC installed, Primary Books
1563 *** Form Name (OU Name: Primary Currency) - Context Info
1564 *** e.g. Maintain Forecast(US West: USD) - Forecast Context Info
1565 ***
1566 *** C. MRC installed, Reporting Books
1567 *** Form Name (OU Name: Reporting Currency) - Context Info
1568 *** e.g. Maintain Forecast(US West: EUR) - Forecast Context Info
1569 ***
1570 */
1571 ELSE
1572
1573 -- start 12i MOAC changes
1574 -- FND_PROFILE.GET ('ORG_ID', l_id);
1575 l_id := Pa_Moac_Utils.Get_Current_Org_Id;
1576 -- end 12i MOAC changes
1577
1578 /* Bug 2657833 - UTF8 change Impact, Using substr for organization name,
1579 selecting length 55 instead of 60 as 5 characters(for currency code) are concatenated here*/
1580 /* Bug6884654 - Changed substr to substrb */
1581 select substrb(h.name,1,55) ||
1582 decode(g.mrc_sob_type_code, 'N', NULL,
1583 decode(l_multi_cur, 'N', NULL,
1584 ': ' || g.currency_code))
1585 into l_wnd_context
1586 from gl_sets_of_books g
1587 , pa_implementations c
1588 , hr_operating_units h
1589 where h.organization_id = to_number(l_id)
1590 and c.set_of_books_id = g.set_of_books_id;
1591
1592
1593 END IF;
1594
1595 return l_wnd_context;
1596
1597
1598 END Get_Org_Window_Title;
1599
1600 ---------------------------------------------------------------
1601 -- Procedure : Get_Encoded_Msg
1602 -- This procedure serves as a wrapper to the function
1603 -- FND_MSG_PUB.Get. It is needed to access the call from
1604 -- client FORMS.
1605 ---------------------------------------------------------------
1606
1607 Procedure Get_Encoded_Msg(p_index IN NUMBER,
1608 p_msg_out IN OUT NOCOPY VARCHAR2 ) IS
1609 l_message VARCHAR2(2000);
1610 BEGIN
1611 p_msg_out := fnd_msg_pub.get(p_msg_index => p_index,
1612 p_encoded => FND_API.G_FALSE);
1613
1614 END Get_Encoded_Msg;
1615
1616
1617 ---------------------------------------------------------------
1618 -- Procedure : Add_Message
1619 -- This procedure serves as a wrapper to the FND_MEG_PUB
1620 -- procedures to add the specified message onto the message
1621 -- stack.
1622 -- 25-APR-02 MAnsari Modified call FND_MESSAGE.SET_NAME to
1623 -- use SUBSTR.
1624 ---------------------------------------------------------------
1625
1626 Procedure Add_Message( p_app_short_name IN VARCHAR2,
1627 p_msg_name IN VARCHAR2,
1628 p_token1 IN VARCHAR2 ,
1629 p_value1 IN VARCHAR2 ,
1630 p_token2 IN VARCHAR2 ,
1631 p_value2 IN VARCHAR2 ,
1632 p_token3 IN VARCHAR2 ,
1633 p_value3 IN VARCHAR2 ,
1634 p_token4 IN VARCHAR2 ,
1635 p_value4 IN VARCHAR2 ,
1636 p_token5 IN VARCHAR2 ,
1637 p_value5 IN VARCHAR2 ) IS
1638
1639 BEGIN
1640
1641 FND_MESSAGE.Set_Name(p_app_short_name, SUBSTR( p_msg_name, 1, 30 ));
1642 IF (p_token1 IS NOT NULL) THEN
1643 FND_MESSAGE.Set_Token(p_token1, p_value1);
1644 END IF;
1645 IF (p_token2 IS NOT NULL) THEN
1646 FND_MESSAGE.Set_Token(p_token2, p_value2);
1647 END IF;
1648 IF (p_token3 IS NOT NULL) THEN
1649 FND_MESSAGE.Set_Token(p_token3, p_value3);
1650 END IF;
1651 IF (p_token4 IS NOT NULL) THEN
1652 FND_MESSAGE.Set_Token(p_token4, p_value4);
1653 END IF;
1654 IF (p_token5 IS NOT NULL) THEN
1655 FND_MESSAGE.Set_Token(p_token5, p_value5);
1656 END IF;
1657
1658 FND_MSG_PUB.Add;
1659
1660 END Add_Message;
1661
1662
1663 --------------------------------------------------------------
1664 FUNCTION IsCrossBGProfile_WNPS
1665 RETURN VARCHAR2
1666
1667 IS
1668
1669 BEGIN
1670
1671 RETURN(FND_PROFILE.VALUE_WNPS('HR_CROSS_BUSINESS_GROUP'));
1672
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 RAISE ;
1676
1677 END IsCrossBGProfile_WNPS ;
1678 --------------------------------------------------------------
1679
1680 ---------------------------------------------------------------
1681 -- Function : Conv_Special_JS_Chars
1682 -- This function converts special characters in javascript link.
1683 -- Currently, this function only handles apostrophe sign.
1684 ---------------------------------------------------------------
1685 FUNCTION Conv_Special_JS_Chars(p_string in varchar2) RETURN VARCHAR2
1686 IS
1687
1688 BEGIN
1689
1690 RETURN icx_util.replace_quotes(p_string);
1691
1692 END Conv_Special_JS_Chars;
1693
1694 ---------------------------------------------------------------
1695 -- Function :Pa_Round_Currency
1696 -- This function rounds the amount to the required precsion based
1697 -- on the currency code
1698 ---------------------------------------------------------------
1699 FUNCTION Pa_Round_Currency
1700 (P_Amount IN NUMBER
1701 ,P_Currency_Code IN VARCHAR2)
1702 RETURN NUMBER IS
1703 l_rounded_amount NUMBER;
1704 BEGIN
1705
1706 SELECT decode(FC.minimum_accountable_unit,
1707 NULL, round(P_Amount, FC.precision),
1708 round(P_Amount/FC.minimum_accountable_unit) *
1709 FC.minimum_accountable_unit)
1710 INTO l_rounded_amount
1711 FROM fnd_currencies FC
1712 WHERE FC.currency_code = P_Currency_Code;
1713
1714 RETURN(l_rounded_amount);
1715
1716 EXCEPTION
1717
1718 WHEN NO_DATA_FOUND THEN
1719 RETURN (NULL);
1720 END PA_ROUND_CURRENCY;
1721
1722 ---------------------------------------------------------------
1723 FUNCTION get_party_id (
1724 p_user_id in number )
1725 return number
1726 IS
1727 Cursor external is
1728 select person_party_id from fnd_user -- For Bug 4527617.
1729 where user_id = p_user_id;
1730
1731 Cursor internal is
1732 select h.party_id
1733 from hz_parties h
1734 ,fnd_user f
1735 where h.orig_system_reference = CONCAT('PER:',f.employee_id)
1736 and f.user_id = p_user_id;
1737
1738 l_party_id number;
1739
1740 Begin
1741 Open internal;
1742 fetch internal into l_party_id;
1743 if (internal%NOTFOUND) then
1744 l_party_id := NULL;
1745 end if;
1746 close internal;
1747
1748 if (l_party_id IS NULL) then
1749 Open external;
1750 fetch external into l_party_id;
1751 if (external%NOTFOUND) then
1752 l_party_id := NULL;
1753 end if;
1754 close external;
1755 end if;
1756
1757 return l_party_id;
1758 Exception
1759 When others then
1760 RAISE;
1761 End get_party_id;
1762
1763 --
1764 /*==========================================================================*/
1765
1766 -- PROCEDURE GetEmpOrgJobId
1767 --
1768 /*==========================================================================*/
1769
1770 /* cwk changes : Modified Procedure to derive the organization and job for a contingent worker person Id also */
1771 /* cwk changes: Modified function to derive job id for the entered PO number and line - Bug 4044057 */
1772
1773 PROCEDURE GetEmpOrgJobId ( X_person_id IN NUMBER
1774 , X_date IN DATE
1775 , X_Emp_Org_Id OUT NOCOPY NUMBER
1776 , X_Emp_Job_Id OUT NOCOPY NUMBER
1777 , X_po_number IN VARCHAR2 DEFAULT NULL --Bug 4044057
1778 , X_po_line_num IN NUMBER DEFAULT NULL ) --Bug 4044057
1779 IS
1780 l_po_header_id NUMBER;
1781 l_po_line_id NUMBER;
1782 l_po_number VARCHAR2(20);
1783 l_po_line_num NUMBER;
1784 X_Cross_BG_Profile VARCHAR2(2); /*Bug 6355926*/
1785 -- bug 11741116 start
1786 l_final_process_date per_periods_of_service.final_process_date%TYPE;
1787
1788 CURSOR cur_final_process_date (X_person_id NUMBER, X_date DATE) IS
1789 SELECT MAX(nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')))
1790 FROM per_periods_of_service
1791 WHERE person_id = X_person_id
1792 AND nvl(final_process_date,to_date('4712/12/31', 'YYYY/MM/DD')) > = trunc(X_date);
1793 -- bug 11741116 end
1794
1795
1796 BEGIN
1797 X_Cross_BG_Profile := pa_utils.IsCrossBGProfile_WNPS; /*Bug 6355926*/
1798 If (G_PersonIdPrev = X_Person_Id AND
1799 trunc(G_DatePrev) = trunc(X_Date) AND
1800 x_po_number is null AND
1801 x_po_line_num is null) Then
1802
1803 X_Emp_Org_Id := G_EmpOrgId;
1804 X_Emp_Job_Id := G_EmpJobId;
1805
1806 Else
1807
1808 If x_po_number is not null then -- Bug 4044057
1809
1810 hr_po_info.get_po_for_primary_asg(X_person_id, X_date,l_po_header_id,l_po_line_id);
1811
1812 BEGIN
1813 select poh.segment1, pol.line_num
1814 into l_po_number, l_po_line_num
1815 from po_headers poh,
1816 po_lines pol
1817 where poh.po_header_id = pol.po_header_id
1818 and poh.po_header_id = l_po_header_id
1819 and pol.po_line_id = l_po_line_id;
1820 EXCEPTION /* Bug 6978184 : Added Exception Block */
1821 WHEN NO_DATA_FOUND THEN
1822 NULL;
1823 WHEN OTHERS THEN
1824 Raise;
1825 END;
1826
1827
1828 if (l_po_number <> x_po_number or l_po_line_num <> x_po_line_num) then
1829 /* Bug 6978184 : Added Query and IF below */
1830 select poh.po_header_id, pol.po_line_id
1831 into l_po_header_id, l_po_line_id
1832 from po_headers poh,
1833 po_lines pol
1834 where poh.po_header_id = pol.po_header_id
1835 and poh.type_lookup_code = 'STANDARD'
1836 and poh.segment1 = x_po_number
1837 and pol.line_num = x_po_line_num;
1838
1839 if NOT PO_PA_INTEGRATION_GRP.is_PO_active(X_person_id, X_date, l_po_header_id, l_po_line_id) then
1840 X_emp_job_id := NULL;
1841 X_Emp_Org_Id := NULL;
1842 RETURN;
1843 end if;
1844 end if;
1845
1846 end if; -- End of Bug 4044057
1847 BEGIN
1848 SELECT
1849 a.job_id,
1850 a.organization_id
1851 INTO
1852 X_emp_job_id,
1853 X_Emp_Org_Id
1854 FROM
1855 per_assignment_status_types s
1856 , per_all_assignments_f a -- modified for Bug 4699231
1857 , pa_implementations i
1858 WHERE
1859 a.job_id IS NOT NULL
1860 AND a.primary_flag = 'Y'
1861 AND trunc(X_date) BETWEEN trunc( a.effective_start_date )
1862 AND trunc( a.effective_end_date )
1863 AND a.person_id = X_person_id
1864 AND ((X_Cross_BG_Profile ='N' AND a.business_group_id = i.business_group_id) OR
1865 X_Cross_BG_Profile ='Y') /*bug6355926*/
1866 AND a.assignment_type in ('E', 'C')
1867 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'TERM_ASSIGN') --Bug 13974131
1868 AND s.assignment_status_type_id = a.assignment_status_type_id;
1869 /*changes to 11741116 start */
1870 EXCEPTION WHEN NO_DATA_FOUND THEN
1871
1872 OPEN cur_final_process_date (X_person_id, X_date);
1873 FETCH cur_final_process_date INTO l_final_process_date;
1874 CLOSE cur_final_process_date;
1875
1876 SELECT
1877 a.job_id,
1878 a.organization_id
1879 INTO
1880 X_emp_job_id,
1881 X_Emp_Org_Id
1882 FROM
1883 per_assignment_status_types s
1884 , per_all_assignments_f a -- modified for Bug 4699231
1885 , pa_implementations i
1886 WHERE
1887 a.job_id IS NOT NULL
1888 AND a.primary_flag = 'Y'
1889 AND trunc(X_date) BETWEEN trunc( a.effective_start_date )
1890 AND trunc( l_final_process_date )
1891 and trunc(nvl(a.effective_end_date,l_final_process_date)) >= trunc(l_final_process_date) --added for 11933246
1892 AND a.person_id = X_person_id
1893 AND ((X_Cross_BG_Profile ='N' AND a.business_group_id = i.business_group_id) OR
1894 X_Cross_BG_Profile ='Y') /*bug6355926*/
1895 AND a.assignment_type in ('E', 'C')
1896 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'TERM_ASSIGN') --Bug 13974131
1897 AND s.assignment_status_type_id = a.assignment_status_type_id;
1898
1899 END;/*changes to 11741116 end */
1900 G_EmpOrgId := X_Emp_Org_Id;
1901 G_EmpJobID := X_Emp_Job_Id;
1902 G_PersonIdPrev := X_Person_Id;
1903 G_DatePrev := X_Date;
1904
1905 End If;
1906
1907
1908 EXCEPTION
1909 WHEN OTHERS THEN
1910 G_PersonIdPrev := X_Person_Id;
1911 G_DatePrev := X_Date;
1912 X_emp_job_id := NULL;
1913 X_Emp_Org_Id := NULL;
1914
1915 END GetEmpOrgJobId;
1916
1917 -- ==========================================================================
1918 -- = FUNCTION NewGetWeekEnding
1919 -- ==========================================================================
1920
1921 FUNCTION NewGetWeekEnding ( X_date IN DATE ) RETURN DATE
1922 IS
1923
1924 X_week_ending DATE;
1925 X_week_ending_day VARCHAR2(80);
1926 X_week_ending_day_index number;
1927 x_week_start number; /*Bug 7601460 */
1928 l_Found BOOLEAN := FALSE;
1929
1930 BEGIN
1931
1932 Begin
1933
1934 select exp_cycle_start_day_code into x_week_start from pa_implementations; /*Bug 7601460 */
1935
1936 -- Check if there are any records in the pl/sql table.
1937 If G_WeekEndDateTab.COUNT > 0 Then
1938
1939 -- Get the Project Number from the pl/sql table.
1940 -- If there is no index with the value of the project_id passed
1941 -- in then an ora-1403: no_data_found is generated.
1942 X_Week_Ending := G_WeekEndDateTab(to_number(to_char(X_Date,'YYYYMMDD')||to_char(x_week_start))); /*Bug 7601460 */
1943 l_Found := TRUE;
1944
1945 End If;
1946
1947 Exception
1948 When No_Data_Found Then
1949 X_Week_Ending := null;
1950 l_Found := FALSE;
1951 When Others Then
1952 RAISE;
1953
1954 End;
1955
1956 If Not l_Found Then
1957
1958 -- Since the ei date has not been cached yet, will need to add it.
1959 -- So check to see if there are already 200 records in the pl/sql table.
1960 -- We don't want the pl/sql table to get large than 200 records.
1961 If G_WeekEndDateTab.COUNT > 200 Then
1962
1963 G_WeekEndDateTab.Delete;
1964
1965 End If;
1966
1967 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1968 into X_week_ending_day_index
1969 FROM pa_implementations;
1970
1971 select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
1972 into X_week_ending_day
1973 from dual;
1974
1975 SELECT Next_Day( trunc( X_date )-1, X_Week_Ending_Day ) /* BUG#3118592 */
1976 INTO X_Week_Ending
1977 FROM sys.dual;
1978
1979 -- Add the week ending date to the pl/sql table using the ei date
1980 -- as the index value.
1981 G_WeekEndDateTab(to_number(to_char(X_date,'YYYYMMDD')||to_char(x_week_start))) := X_Week_Ending; /*Bug 7601460 */
1982
1983 End If;
1984
1985 RETURN ( X_Week_Ending );
1986
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 RETURN ( NULL );
1990
1991 END NewGetWeekEnding;
1992
1993 /* Added for bug 5067511 */
1994
1995 -- ==========================================================================
1996 -- FUNCTION GetPersonInfo : Used only in the view PA_PROJECT_PLAYERS_V
1997 -- Returns the requested data in l_data. Returns 'NOT_FOUND' when no records
1998 -- found or invalid l_data
1999 -- 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.
2000 -- ==========================================================================
2001
2002 FUNCTION GetPersonInfo( p_person_id IN per_all_people_f.person_id%TYPE,
2003 p_data IN VARCHAR2 DEFAULT 'PERSON_ID') RETURN VARCHAR2
2004 IS
2005 BEGIN
2006 If NVL(L_PERSON_ID, 0) <> p_person_id Then
2007 Begin
2008 select PERSON_ID
2009 , FULL_NAME
2010 , LAST_NAME
2011 , FIRST_NAME
2012 , MIDDLE_NAMES
2013 , EMPLOYEE_NUMBER
2014 into
2015 L_PERSON_ID
2016 , L_PERSON_FULL_NAME
2017 , L_PERSON_LAST_NAME
2018 , L_PERSON_FIRST_NAME
2019 , L_PERSON_MIDDLE_NAMES
2020 , L_PERSON_EMPLOYEE_NUMBER
2021 from pa_employees where person_id = p_person_id;
2022 Exception
2023 When No_Data_Found Then
2024 RETURN('NOT_FOUND');
2025 END;
2026 End If;
2027
2028 If p_data = 'PERSON_ID' Then
2029 Return ('FOUND');
2030 End If;
2031 If p_data = 'FULL_NAME' Then
2032 Return (L_PERSON_FULL_NAME);
2033 End If;
2034 If p_data = 'LAST_NAME' Then
2035 Return (L_PERSON_LAST_NAME);
2036 End If;
2037 If p_data = 'FIRST_NAME' Then
2038 Return (L_PERSON_FIRST_NAME);
2039 End If;
2040 If p_data = 'MIDDLE_NAMES' Then
2041 Return (L_PERSON_MIDDLE_NAMES);
2042 End If;
2043 If p_data = 'EMPLOYEE_NUMBER' Then
2044 Return (L_PERSON_EMPLOYEE_NUMBER);
2045 End If;
2046
2047 Return ('NOT_FOUND');
2048 END GetPersonInfo;
2049
2050 -- ==========================================================================
2051 -- Procedure get_rate_source_cost_method
2052 -- Returns the requested data in x_rate_source and x_cost_method
2053 -- Returns NULL if the rules are not defined. Calling program handles the situation.
2054 -- ==========================================================================
2055 /*
2056 procedure get_rate_source_cost_method(p_exp_item_id IN NUMBER,
2057 p_exp_item_date IN DATE,
2058 x_rate_source_code OUT NOCOPY VARCHAR2,
2059 x_costing_method OUT NOCOPY VARCHAR2,
2060 x_enable_accrual OUT NOCOPY
2061 VARCHAR2) IS
2062
2063 cursor get_emp_override_rule is
2064 select ei.org_id ORG_ID
2065 ,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
2066 ,nvl(ei.override_to_organization_id
2067 ,exp.incurred_by_organization_id) orgj_id
2068 ,exp.incurred_by_person_id incurred_by_person_id
2069 ,ei.job_id JOB_ID
2070 ,detail.compensation_rule_set
2071 ,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
2072 ,to_char(detail.rate_schedule_id)
2073 ,detail.override_type
2074 ,detail.acct_rate_type
2075 ,detail.acct_rate_date_code
2076 ,detail.acct_exchange_rate
2077 ,detail.base_hours
2078 ,detail.rbc_element_type_id
2079 from pa_compensation_details detail
2080 ,pa_expenditure_items ei
2081 ,pa_expenditures exp
2082 where trunc(p_exp_item_date) between trunc(detail.start_date_active)
2083 and trunc(nvl(detail.end_date_active,p_exp_item_date))
2084 and detail.person_id = exp.incurred_by_person_id
2085 and exp.expenditure_id = ei.expenditure_id
2086 --and ei.source_expenditure_item_id is null
2087 and ei.expenditure_item_id = p_exp_item_id
2088 and ei.po_line_id IS NULL;
2089
2090 l_org_id_tab pa_plsql_datatypes.IdTabTyp;
2091 l_organization_id_tab pa_plsql_datatypes.IdTabTyp;
2092 l_person_id_tab pa_plsql_datatypes.IdTabTyp;
2093 l_job_id_tab pa_plsql_datatypes.IdTabTyp;
2094 l_txn_date_tab pa_plsql_datatypes.Char30TabTyp;
2095 l_override_type_tab pa_plsql_datatypes.Char150TabTyp;
2096 l_calling_module varchar2(50) default 'STAFFED';
2097 l_Called_From varchar2(1) DEFAULT 'O';
2098 l_org_lab_sch_rule_id_tab pa_plsql_datatypes.IdTabTyp;
2099 l_costing_rule_tab pa_plsql_datatypes.Char150TabTyp;
2100 l_rate_sch_id_tab pa_plsql_datatypes.IdTabTyp;
2101 l_ot_project_id_tab pa_plsql_datatypes.IdTabTyp;
2102 l_ot_task_id_tab pa_plsql_datatypes.IdTabTyp;
2103 l_base_hours_tab pa_plsql_datatypes.IdTabTyp; -- 12.2 payroll intg .. ttc
2104 l_rbc_elem_type_tab pa_plsql_datatypes.IdTabTyp;
2105 l_cost_rate_curr_code_tab pa_plsql_datatypes.Char150TabTyp;
2106 l_acct_rate_type_tab pa_plsql_datatypes.Char150TabTyp;
2107 l_acct_rate_date_code_tab pa_plsql_datatypes.Char150TabTyp;
2108 l_acct_exch_rate_tab pa_plsql_datatypes.Char30TabTyp;
2109 l_err_stage_tab pa_plsql_datatypes.NumTabTyp;
2110 l_err_code_tab pa_plsql_datatypes.Char150TabTyp;
2111 l_debug_mode varchar2(1);
2112
2113 begin
2114
2115 if pa_cc_utils.g_debug_mode then
2116 l_debug_mode := 'Y';
2117 else
2118 l_debug_mode := 'N';
2119 end if;
2120
2121 if ( l_debug_mode = 'Y' ) THEN
2122 pa_debug.set_process( x_process => 'PLSQL'
2123 ,x_debug_mode => l_debug_mode
2124 );
2125 pa_cc_utils.set_curr_function('get_rate_source_cost_method');
2126 pa_cc_utils.log_message('Start ');
2127 end if;
2128
2129 open get_emp_override_rule;
2130 fetch get_emp_override_rule bulk collect into
2131 l_org_id_tab
2132 ,l_txn_date_tab
2133 ,l_organization_id_tab
2134 ,l_person_id_tab
2135 ,l_job_id_tab
2136 ,l_costing_rule_tab
2137 ,l_cost_rate_curr_code_tab
2138 ,l_rate_sch_id_tab
2139 ,l_override_type_tab
2140 ,l_acct_rate_type_tab
2141 ,l_acct_rate_date_code_tab
2142 ,l_acct_exch_rate_tab
2143 ,l_base_hours_tab
2144 ,l_rbc_elem_type_tab;
2145 close get_emp_override_rule;
2146
2147 -- l_org_lab_sch_rule_id_tab.extend(1);
2148 -- l_ot_project_id_tab.extend(1);
2149 -- l_ot_task_id_tab.extend(1);
2150 -- l_err_stage_tab.extend(1);
2151 -- l_err_code_tab.extend(1);
2152
2153 -- obtain the default rules defined at the organization and operating unit level also.
2154
2155 IF l_costing_rule_tab(1) IS NULL THEN
2156 pa_cost_rate_pub.get_orgn_lvl_cst_info_set
2157 ( p_org_id_tab => l_org_id_tab
2158 ,p_organization_id_tab => l_organization_id_tab
2159 ,p_person_id_tab => l_person_id_tab
2160 ,p_job_id_tab => l_job_id_tab
2161 ,p_txn_date_tab => l_txn_date_tab
2162 ,p_override_type_tab => l_override_type_tab
2163 ,x_org_labor_sch_rule_id_tab => l_org_lab_sch_rule_id_tab
2164 ,x_costing_rule_tab => l_costing_rule_tab
2165 ,x_rate_sch_id_tab => l_rate_sch_id_tab
2166 ,x_ot_project_id_tab => l_ot_project_id_tab
2167 ,x_ot_task_id_tab => l_ot_task_id_tab
2168 ,x_base_hours_tab =>
2169 l_base_hours_tab -- 12.2 payroll intg .. ttc
2170 ,x_rbc_elem_type_tab => l_rbc_elem_type_tab
2171 ,x_cost_rate_curr_code_tab => l_cost_rate_curr_code_tab
2172 ,x_acct_rate_type_tab => l_acct_rate_type_tab
2173 ,x_acct_rate_date_code_tab => l_acct_rate_date_code_tab
2174 ,x_acct_exch_rate_tab => l_acct_exch_rate_tab
2175 ,x_err_stage_tab => l_err_stage_tab
2176 ,x_err_code_tab => l_err_code_tab
2177 );
2178 END IF;
2179
2180 if l_costing_rule_tab(1) is null then
2181 x_costing_method := null;
2182 x_rate_source_code := null;
2183 x_enable_accrual := null;
2184 end if;
2185
2186 if l_costing_rule_tab(1) is not null then
2187
2188 select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
2189 into x_costing_method, x_rate_source_code,x_enable_accrual
2190 from pa_compensation_rule_sets rule
2191 where rule.compensation_rule_set = l_costing_rule_tab(1);
2192
2193 end if;
2194
2195 exception
2196 when others then
2197 x_costing_method := null;
2198 x_rate_source_code := null;
2199 x_enable_accrual := null;
2200 end get_rate_source_cost_method;
2201 */
2202
2203 procedure get_rate_source_cost_method(p_exp_item_id IN NUMBER,
2204 p_exp_item_date IN DATE,
2205 x_rate_source_code OUT NOCOPY VARCHAR2,
2206 x_costing_method OUT NOCOPY VARCHAR2,
2207 x_enable_accrual OUT NOCOPY VARCHAR2) IS
2208
2209 cursor get_emp_override_rule is
2210 select detail.compensation_rule_set
2211 ,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
2212 ,to_char(detail.rate_schedule_id)
2213 ,detail.override_type
2214 ,detail.acct_rate_type
2215 ,detail.acct_rate_date_code
2216 ,detail.acct_exchange_rate
2217 ,detail.base_hours
2218 ,detail.rbc_element_type_id
2219 from pa_compensation_details detail
2220 ,pa_expenditure_items ei
2221 ,pa_expenditures exp
2222 where trunc(p_exp_item_date) between trunc(detail.start_date_active)
2223 and trunc(nvl(detail.end_date_active,p_exp_item_date))
2224 and detail.person_id = exp.incurred_by_person_id
2225 and exp.expenditure_id = ei.expenditure_id
2226 and ei.expenditure_item_id = p_exp_item_id
2227 and ei.po_line_id IS NULL;
2228
2229 CURSOR get_exp_details IS
2230 SELECT ei.org_id ORG_ID
2231 ,ei.expenditure_item_date
2232 ,nvl(ei.override_to_organization_id
2233 ,exp.incurred_by_organization_id) orgj_id
2234 ,exp.incurred_by_person_id incurred_by_person_id
2235 ,ei.job_id JOB_ID
2236 from pa_expenditure_items ei
2237 ,pa_expenditures exp
2238 where exp.expenditure_id = ei.expenditure_id
2239 and ei.expenditure_item_id = p_exp_item_id
2240 and ei.po_line_id IS NULL;
2241
2242 l_calling_module varchar2(50) default 'STAFFED';
2243 l_Called_From varchar2(1) DEFAULT 'O';
2244 l_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
2245
2246 l_debug_mode varchar2(1);
2247
2248 l_org_id pa_implementations_all.org_id%TYPE;
2249 l_organization_id pa_expenditures_all.incurred_by_organization_id%TYPE;
2250 l_person_id pa_expenditures_all.incurred_by_person_id%TYPE;
2251 l_job_id pa_expenditure_items_all.job_id%TYPE;
2252 l_txn_date pa_expenditure_items_all.expenditure_item_date%TYPE;
2253
2254 lx_override_type varchar2(150);
2255 lx_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
2256 lx_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
2257 lx_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE;
2258 lx_ot_project_id pa_projects_all.project_id%TYPE;
2259 lx_ot_task_id pa_tasks.task_id%TYPE;
2260 lx_base_hours pa_org_labor_sch_rule.base_hours%TYPE;
2261 lx_rbc_elem_type_id pa_org_labor_sch_rule.rbc_element_type_id%TYPE;
2262 lx_cost_rate_curr_code pa_expenditure_items_all.denom_currency_code%TYPE;
2263 lx_acct_rate_type pa_expenditure_items_all.acct_rate_type%TYPE;
2264 lx_acct_rate_date_code pa_implementations_all.acct_rate_date_code%TYPE;
2265 lx_acct_exch_rate pa_compensation_details_all.acct_exchange_rate%TYPE;
2266 lx_err_stage number;
2267 lx_err_code varchar2(150);
2268
2269
2270 begin
2271
2272 open get_emp_override_rule;
2273 fetch get_emp_override_rule into
2274 lx_costing_rule
2275 ,lx_cost_rate_curr_code
2276 ,lx_rate_sch_id
2277 ,lx_override_type
2278 ,lx_acct_rate_type
2279 ,lx_acct_rate_date_code
2280 ,lx_acct_exch_rate
2281 ,lx_base_hours
2282 ,lx_rbc_elem_type_id;
2283 close get_emp_override_rule;
2284
2285 /* obtain the default rules defined at the organization and operating unit level also. */
2286
2287 IF lx_costing_rule IS NULL THEN
2288 OPEN get_exp_details;
2289 FETCH get_exp_details INTO l_org_id, l_txn_date, l_organization_id, l_person_id, l_job_id;
2290 CLOSE get_exp_details;
2291
2292 pa_cost_rate_pub.get_orgn_level_costing_info
2293 ( p_org_id => l_org_id
2294 ,p_organization_id => l_organization_id
2295 ,p_person_id => l_person_id
2296 ,p_job_id => l_job_id
2297 ,p_txn_date => p_exp_item_date
2298 ,p_calling_module => l_calling_module
2299 ,x_org_labor_sch_rule_id => lx_org_labor_sch_rule_id
2300 ,x_costing_rule => lx_costing_rule
2301 ,x_rate_sch_id => lx_rate_sch_id
2302 ,x_ot_project_id => lx_ot_project_id
2303 ,x_ot_task_id => lx_ot_task_id
2304 ,x_base_hours => lx_base_hours
2305 ,x_rbc_elem_type_id => lx_rbc_elem_type_id
2306 ,x_cost_rate_curr_code => lx_cost_rate_curr_code
2307 ,x_acct_rate_type => lx_acct_rate_type
2308 ,x_acct_rate_date_code => lx_acct_rate_date_code
2309 ,x_acct_exch_rate => lx_acct_exch_rate
2310 ,x_err_stage => lx_err_stage
2311 ,x_err_code => lx_err_code
2312 ,p_called_from => l_called_from
2313 );
2314
2315 END IF;
2316
2317 if lx_costing_rule IS NULL then
2318 x_costing_method := null;
2319 x_rate_source_code := null;
2320 x_enable_accrual := null;
2321 end if;
2322
2323 if lx_costing_rule IS NOT NULL then
2324
2325 select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
2326 into x_costing_method, x_rate_source_code,x_enable_accrual
2327 from pa_compensation_rule_sets rule
2328 where rule.compensation_rule_set = lx_costing_rule;
2329
2330 end if;
2331
2332 exception
2333 when others then
2334 x_costing_method := null;
2335 x_rate_source_code := null;
2336 x_enable_accrual := null;
2337 end get_rate_source_cost_method;
2338
2339 ---- Added procedure for payroll billable_flag changes for 12.2 start bug#12360223
2340
2341 procedure get_rate_source_cost_method1(p_incurred_by_person_id IN NUMBER,
2342 p_exp_item_date IN DATE,
2343 p_incurred_by_organization_id IN NUMBER,
2344 p_org_id IN NUMBER ,
2345 x_rate_source_code OUT NOCOPY VARCHAR2,
2346 x_costing_method OUT NOCOPY VARCHAR2,
2347 x_enable_accrual OUT NOCOPY VARCHAR2) IS
2348
2349 cursor get_emp_override_rule is
2350 select detail.compensation_rule_set
2351 ,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
2352 ,to_char(detail.rate_schedule_id)
2353 ,detail.override_type
2354 ,detail.acct_rate_type
2355 ,detail.acct_rate_date_code
2356 ,detail.acct_exchange_rate
2357 ,detail.base_hours
2358 ,detail.rbc_element_type_id
2359 from pa_compensation_details detail
2360 where trunc(p_exp_item_date) between trunc(detail.start_date_active)
2361 and trunc(nvl(detail.end_date_active,p_exp_item_date))
2362 and detail.person_id = p_incurred_by_person_id;
2363
2364
2365 CURSOR get_exp_details IS
2366 SELECT distinct job_id JOB_ID
2367 from per_all_assignments_f
2368 where person_id = p_incurred_by_person_id
2369 and trunc(p_exp_item_date) between trunc(effective_start_date)
2370 and trunc(nvl(effective_end_date,p_exp_item_date));
2371
2372
2373 l_calling_module varchar2(50) default 'STAFFED';
2374 l_Called_From varchar2(1) DEFAULT 'O';
2375 l_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
2376
2377 l_debug_mode varchar2(1);
2378
2379 l_org_id pa_implementations_all.org_id%TYPE;
2380 l_organization_id pa_expenditures_all.incurred_by_organization_id%TYPE;
2381 l_person_id pa_expenditures_all.incurred_by_person_id%TYPE;
2382 l_job_id pa_expenditure_items_all.job_id%TYPE;
2383 l_txn_date pa_expenditure_items_all.expenditure_item_date%TYPE;
2384
2385 lx_override_type varchar2(150);
2386 lx_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
2387 lx_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
2388 lx_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE;
2389 lx_ot_project_id pa_projects_all.project_id%TYPE;
2390 lx_ot_task_id pa_tasks.task_id%TYPE;
2391 lx_base_hours pa_org_labor_sch_rule.base_hours%TYPE;
2392 lx_rbc_elem_type_id pa_org_labor_sch_rule.rbc_element_type_id%TYPE;
2393 lx_cost_rate_curr_code pa_expenditure_items_all.denom_currency_code%TYPE;
2394 lx_acct_rate_type pa_expenditure_items_all.acct_rate_type%TYPE;
2395 lx_acct_rate_date_code pa_implementations_all.acct_rate_date_code%TYPE;
2396 lx_acct_exch_rate pa_compensation_details_all.acct_exchange_rate%TYPE;
2397 lx_err_stage number;
2398 lx_err_code varchar2(150);
2399
2400
2401 begin
2402
2403 open get_emp_override_rule;
2404 fetch get_emp_override_rule into
2405 lx_costing_rule
2406 ,lx_cost_rate_curr_code
2407 ,lx_rate_sch_id
2408 ,lx_override_type
2409 ,lx_acct_rate_type
2410 ,lx_acct_rate_date_code
2411 ,lx_acct_exch_rate
2412 ,lx_base_hours
2413 ,lx_rbc_elem_type_id;
2414 close get_emp_override_rule;
2415
2416 /* obtain the default rules defined at the organization and operating unit level also. */
2417
2418 IF lx_costing_rule IS NULL THEN
2419 OPEN get_exp_details;
2420 FETCH get_exp_details INTO l_job_id;
2421 CLOSE get_exp_details;
2422
2423 pa_cost_rate_pub.get_orgn_level_costing_info
2424 ( p_org_id => p_org_id --OU
2425 ,p_organization_id => p_incurred_by_organization_id ----Services east
2426 ,p_person_id => p_incurred_by_person_id
2427 ,p_job_id => l_job_id
2428 ,p_txn_date => p_exp_item_date
2429 ,p_calling_module => l_calling_module
2430 ,x_org_labor_sch_rule_id => lx_org_labor_sch_rule_id
2431 ,x_costing_rule => lx_costing_rule
2432 ,x_rate_sch_id => lx_rate_sch_id
2433 ,x_ot_project_id => lx_ot_project_id
2434 ,x_ot_task_id => lx_ot_task_id
2435 ,x_base_hours => lx_base_hours
2436 ,x_rbc_elem_type_id => lx_rbc_elem_type_id
2437 ,x_cost_rate_curr_code => lx_cost_rate_curr_code
2438 ,x_acct_rate_type => lx_acct_rate_type
2439 ,x_acct_rate_date_code => lx_acct_rate_date_code
2440 ,x_acct_exch_rate => lx_acct_exch_rate
2441 ,x_err_stage => lx_err_stage
2442 ,x_err_code => lx_err_code
2443 ,p_called_from => l_called_from
2444 );
2445
2446 END IF;
2447
2448 if lx_costing_rule IS NULL then
2449 x_costing_method := null;
2450 x_rate_source_code := null;
2451 x_enable_accrual := null;
2452 end if;
2453
2454 if lx_costing_rule IS NOT NULL then
2455
2456 select rule.costing_method, rule.rate_source_code,rule.enable_accrual_code
2457 into x_costing_method, x_rate_source_code,x_enable_accrual
2458 from pa_compensation_rule_sets rule
2459 where rule.compensation_rule_set = lx_costing_rule;
2460
2461 end if;
2462
2463 exception
2464 when others then
2465 x_costing_method := null;
2466 x_rate_source_code := null;
2467 x_enable_accrual := null;
2468 end get_rate_source_cost_method1;
2469
2470 ---- Added for payroll billable_flag changes for 12.2 end bug#12360223
2471
2472 procedure check_cm_on_related_items(p_exp_item_id NUMBER,
2473 p_xfer_item_id NUMBER,
2474 x_costing_method OUT NOCOPY VARCHAR2) IS
2475
2476 cursor c_traverse_tree(p_xfer_item_id number) is
2477 select distinct x.expenditure_item_id, x.cost_distributed_flag,
2478 b.module_code, x.costing_method,
2479 decode(b.module_code, 'PAXREVTXN', 'A', 'B') ord
2480 from
2481 (select a.expenditure_item_id, cost_distributed_flag, costing_method
2482 from pa_expenditure_items_all a
2483 where a.adjusted_expenditure_item_id IS NULL
2484 --and cost_distributed_flag = 'Y'
2485 start with expenditure_item_id = p_xfer_item_id
2486 connect by prior a.expenditure_item_id = a.transferred_from_exp_item_id) x,
2487 pa_expend_item_adj_activities b
2488 where x.expenditure_item_id = b.expenditure_item_id
2489 and b.module_code in ('PAXREVTXN', 'PAXPRRPE')
2490 order by 1 desc, ord;
2491
2492 cursor check_item_activity(p_exp_item_id number) is
2493 select module_code, decode(module_code, 'PAXREVTXN', 'A', 'B') ord
2494 from pa_expend_item_adj_activities
2495 where expenditure_item_id = p_exp_item_id
2496 and module_code in ('PAXREVTXN', 'PAXPRRPE')
2497 order by ord;
2498
2499 l_module_code pa_expend_item_adj_activities.module_code%TYPE;
2500 l_order varchar2(1);
2501 l_previous_indx number;
2502
2503 TYPE l_item_tbl is table of number index by binary_integer;
2504 TYPE l_vc1_tbl is table of varchar2(1) index by binary_integer;
2505 --TYPE l_mod_code_tbl is table of pa_expend_item_adj_activities.module_code%TYPE index by binary_integer;
2506 TYPE l_cm_tbl is table of varchar2(30) index by binary_integer;
2507
2508 l_item_id_tbl l_item_tbl;
2509 l_cdf_tbl l_vc1_tbl;
2510 l_module_code_tbl l_cm_tbl;
2511 l_costing_method_tbl l_cm_tbl;
2512 l_order_tbl l_vc1_tbl;
2513
2514 begin
2515 -- check the adjustment activities on the parent items to figure out the costing method to be used.
2516 -- if the expenditure item has module_code as 'PAXREVTXN', then the current setup can be used.
2517 -- if the expenditure item has module code of 'PAXPRRPE', then it's an adjustment that originated from Expenditure inquiry.
2518
2519 -- Following rules are applied to figure out the costing method to be used:
2520 -- 1. If the current expenditure item has an entry for PAXREVTCN in the pa_expend_item_adj_activities table
2521 -- then, the item is eligible to use the current costing rules setup.
2522 -- 2. If the current expenditure item has an entry for PAXPRRPE in pa_expend_item_adj_activities table,
2523 -- then traverse the related items up and down to figure out if the items are costed.
2524 -- If they're costed then the same costing method should be used on the current item.
2525 --
2526 -- Need to put some examples.
2527 pa_cc_utils.log_message('start of check_cm_on_related_items');
2528
2529 if p_exp_item_id is not null then
2530 open check_item_activity(p_exp_item_id);
2531 fetch check_item_activity into l_module_code, l_order;
2532 close check_item_activity;
2533 end if;
2534
2535 if l_module_code is null and p_xfer_item_id is null then -- item is newly created/interfaced. so no entry in activity table
2536 x_costing_method := null;
2537 pa_cc_utils.log_message('check_cm: costing method set to null');
2538 elsif l_module_code = 'PAXREVTXN' then
2539 x_costing_method := 'CURRENT';
2540 pa_cc_utils.log_message('check_cm: costing method set to current');
2541 elsif l_module_code = 'PAXPRRPE' then -- traverse the related items to find the costing method.
2542 open c_traverse_tree(p_xfer_item_id);
2543 fetch c_traverse_tree bulk collect into l_item_id_tbl, l_cdf_tbl, l_module_code_tbl,
2544 l_costing_method_tbl, l_order_tbl;
2545 close c_traverse_tree;
2546
2547 l_previous_indx := -1;
2548
2549 if l_item_id_tbl.count > 0 then
2550 x_costing_method := null;
2551 for i in l_item_id_tbl.first..l_item_id_tbl.last loop
2552 if (l_item_id_tbl(i) > p_exp_item_id OR p_exp_item_id is null) and l_cdf_tbl(i) = 'Y' then
2553 x_costing_method := l_costing_method_tbl(i);
2554 pa_cc_utils.log_message('check_cm: costing method set to related item .. xfer item costed first. EI: ' || l_item_id_tbl(i) || ', CM: ' ||x_costing_method);
2555 exit;
2556 elsif l_module_code_tbl(i) = 'PAXREVTXN' and l_cdf_tbl(i) = 'Y' then
2557 x_costing_method := l_costing_method_tbl(i);
2558 pa_cc_utils.log_message('check_cm: costing method set to related item .. item reversal pgm. CM: ' ||x_costing_method);
2559 exit;
2560 elsif l_module_code_tbl(i) = 'PAXREVTXN' and l_cdf_tbl(i) = 'N' then
2561 x_costing_method := 'CURRENT';
2562 pa_cc_utils.log_message('check_cm: costing method set to related item .. item reversal pgm. CM: ' ||x_costing_method);
2563 exit;
2564 elsif l_cdf_tbl(i) = 'Y' then
2565 l_previous_indx := i;
2566 elsif l_cdf_tbl(i) = 'N' and l_costing_method_tbl(i) is not null then
2567 x_costing_method := l_costing_method_tbl(i);
2568 pa_cc_utils.log_message('check_cm: costing method set to related item .. CM: ' ||x_costing_method);
2569 exit;
2570 end if;
2571 end loop;
2572 end if; -- l_item_id_tbl.count > 0
2573 end if; -- module code check
2574
2575 if x_costing_method is null and l_previous_indx > -1 then
2576 if l_module_code_tbl(l_previous_indx) = 'PAXPRRPE' then
2577 x_costing_method := l_costing_method_tbl(l_previous_indx);
2578 pa_cc_utils.log_message('check_cm: costing method set to related item .. parent costed. CM: ' ||x_costing_method);
2579 end if;
2580 end if;
2581
2582 exception
2583 when others then
2584 x_costing_method := null;
2585 end check_cm_on_related_items;
2586
2587 /* Start for Bug 14851933 */
2588
2589 ----------------------------------------------------------------------------------------------------
2590 -- Function :INITIALIZE
2591 -- This function initialize the global variables which are present in procedure pa_security.initialize
2592 ------------------------------------------------------------------------------------------------------
2593
2594 FUNCTION INITIALIZE RETURN VARCHAR2 IS
2595 v_initialize VARCHAR2(1);
2596 BEGIN
2597 pa_security.initialize(FND_GLOBAL.USER_ID(), 'REPORTS');
2598 v_initialize :='Y';
2599 RETURN v_initialize;
2600
2601 EXCEPTION
2602 WHEN OTHERS THEN
2603 v_initialize :='N';
2604 RETURN (NULL);
2605 END initialize;
2606
2607 /* End for Bug 14851933 */
2608
2609 END pa_utils;
[Home] [Help]