1 PACKAGE BODY PAY_NO_ABSENCE_USER AS
2 /*$Header: pynoabsusr.pkb 120.1.12020000.2 2013/03/22 06:35:25 smeduri ship $*/
3 Function Element_populate(p_assignment_id in number,
4 p_person_id in number,
5 p_absence_attendance_id in number,
6 p_element_type_id in number,
7 p_absence_category in varchar2,
8 p_original_entry_id OUT nocopy NUMBER, --pgopal
9 p_input_value_name1 OUT NOCOPY VARCHAR2,
10 p_input_value1 OUT NOCOPY VARCHAR2,
11 p_input_value_name2 OUT NOCOPY VARCHAR2,
12 p_input_value2 OUT NOCOPY VARCHAR2,
13 p_input_value_name3 OUT NOCOPY VARCHAR2,
14 p_input_value3 OUT NOCOPY VARCHAR2,
15 p_input_value_name4 OUT NOCOPY VARCHAR2,
16 p_input_value4 OUT NOCOPY VARCHAR2,
17 p_input_value_name5 OUT NOCOPY VARCHAR2,
18 p_input_value5 OUT NOCOPY VARCHAR2,
19 p_input_value_name6 OUT NOCOPY VARCHAR2,
20 p_input_value6 OUT NOCOPY VARCHAR2,
21 p_input_value_name7 OUT NOCOPY VARCHAR2,
22 p_input_value7 OUT NOCOPY VARCHAR2,
23 p_input_value_name8 OUT NOCOPY VARCHAR2,
24 p_input_value8 OUT NOCOPY VARCHAR2,
25 p_input_value_name9 OUT NOCOPY VARCHAR2,
26 p_input_value9 OUT NOCOPY VARCHAR2,
27 p_input_value_name10 OUT NOCOPY VARCHAR2,
28 p_input_value10 OUT NOCOPY VARCHAR2,
29 p_input_value_name11 OUT NOCOPY VARCHAR2,
30 p_input_value11 OUT NOCOPY VARCHAR2,
31 p_input_value_name12 OUT NOCOPY VARCHAR2,
32 p_input_value12 OUT NOCOPY VARCHAR2,
33 p_input_value_name13 OUT NOCOPY VARCHAR2,
34 p_input_value13 OUT NOCOPY VARCHAR2,
35 p_input_value_name14 OUT NOCOPY VARCHAR2,
36 p_input_value14 OUT NOCOPY VARCHAR2,
37 p_input_value_name15 OUT NOCOPY VARCHAR2,
38 p_input_value15 OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
39
40
41 /*Cursor to get the intial entry id*/
42 CURSOR csr_get_intial_entry_id(p_creator_id IN VARCHAR2 ,p_assignment_id IN NUMBER ) IS
43 SELECT
44 peef.element_entry_id
45 FROM
46 pay_element_entry_values_f peevf
47 ,pay_input_values_f pivf
48 ,pay_element_entries_f peef
49 WHERE
50 peevf.screen_entry_value = p_creator_id
51 AND pivf.input_value_id = peevf.input_value_id
52 AND pivf.NAME = 'CREATOR_ID'
53 AND pivf.legislation_code = 'NO'
54 AND peef.element_entry_id = peevf.element_entry_id
55 AND peef.assignment_id = p_assignment_id
56 and peef.creator_type <> 'EE'; -- bug 16527593
57
58 -- Cursor to fetch previous sickness and part-time sickness absences for
59 -- find out the starting linking absence.
60 CURSOR CSR_CONT_LINK (l_person_id IN number,l_abs_st_date IN date) IS
61 SELECT paa.absence_attendance_id
62 ,paa.date_start
63 ,paa.date_end
64 FROM per_absence_attendances paa, per_absence_attendance_types pat
65 WHERE paa.person_id = l_person_id
66 AND paa.date_start < l_abs_st_date
67 AND paa.date_end < l_abs_st_date
68 AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL
69 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
70 AND pat.absence_category IN ('S','PTS')
71 ORDER BY paa.date_end desc ;
72
73 -- Cursor to fetch global values
74 CURSOR csr_get_glb_value(p_global_name IN VARCHAR2, p_effective_date IN DATE) IS
75 SELECT to_number(global_value)
76 FROM ff_globals_f
77 WHERE global_name = p_global_name
78 AND legislation_code = 'NO'
79 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
80
81 l_start_date date;
82 l_end_date date;
83 l_absent_reason number;
84 l_start_time varchar2(20);
85 l_end_time varchar2(20);
86 l_abs_cat_meaning varchar2(100);
87 --l_initial_abs_creator_id NUMBER ;
88 l_initial_abs_creator_id varchar2(60) ;
89 l_intial_absence CHAR;
90 l_loop_start_date date;
91 prev_link_abs_exist varchar2(10);
92 l_abs_attn_id number;
93 l_abs_link_period number;
94
95 BEGIN
96
97 -- Fetch absence attendance details
98 BEGIN
99 SELECT abs.date_start
100 ,abs.date_end
101 ,abs.ABS_ATTENDANCE_REASON_ID
102 ,abs.TIME_START
103 ,abs.TIME_END
104 ,abs.abs_information15
105 ,abs.abs_information16
106 INTO l_start_date
107 ,l_end_date
108 ,l_absent_reason
109 ,l_start_time
110 ,l_end_time
111 ,l_intial_absence
112 ,l_initial_abs_creator_id
113 FROM per_absence_attendances abs
114 WHERE abs.absence_attendance_id = p_absence_attendance_id;
115 EXCEPTION
116 WHEN OTHERS THEN
117 NULL;
118 END;
119
120 IF p_absence_category in ('S', 'PTS') AND
121 nvl(l_intial_absence,'Y') = 'Y' THEN
122 OPEN csr_get_glb_value('NO_ABS_LINK_PERIOD',l_start_date);
123 FETCH csr_get_glb_value INTO l_abs_link_period;
124 CLOSE csr_get_glb_value;
125
126 l_loop_start_date := l_start_date ; /* assign the current absence start date */
127 prev_link_abs_exist := 'N';
128 FOR i in CSR_CONT_LINK (p_person_id, l_start_date) LOOP
129 IF ( l_loop_start_date - i.date_end ) <= l_abs_link_period then
130 prev_link_abs_exist := 'Y';
131 l_abs_attn_id := i.absence_attendance_id;
132 ELSE -- No linking absence exists.
133 EXIT;
134 END IF;
135 l_loop_start_date := i.date_start;
136 END LOOP;
137
138 IF prev_link_abs_exist = 'Y' THEN
139 OPEN csr_get_intial_entry_id(to_char(l_abs_attn_id),p_assignment_id);
140 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
141 CLOSE csr_get_intial_entry_id;
142 END IF;
143 ELSE -- Other than S and PTS Categories
144 IF l_intial_absence = 'N' THEN
145 OPEN csr_get_intial_entry_id(l_initial_abs_creator_id,p_assignment_id);
146 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
147 CLOSE csr_get_intial_entry_id;
148 /* Commented Setting original entry id for initial absences*/
149 /*ELSE
150 If initial absence = 'Y' then set the element entry id as original entry id
151 OPEN csr_get_intial_entry_id(p_absence_attendance_id);
152 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
153 CLOSE csr_get_intial_entry_id;*/
154 END IF ;
155 END IF;
156 -- Check if absence category is S ( Sickness )
157 IF p_absence_category in ('S', 'PTS', 'CMS', 'PA', 'PTP', 'M', 'PTM', 'IE_AL', 'VAC','PTA') THEN
158 p_input_value_name1 := 'Start Date';
159 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion for 6850183
160 p_input_value_name2 := 'End Date';
161 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion for 6850183
162 p_input_value_name5 := 'Absence Category';
163
164 -- To select absence category meaning by passing code
165 BEGIN
166 SELECT MEANING
167 INTO l_abs_cat_meaning
168 --FROM FND_LOOKUP_VALUES
169 -- Version 115.1 , Changed query to get value from hr_lookups
170 FROM HR_LOOKUPS
171 WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
172 AND ENABLED_FLAG = 'Y'
173 AND LOOKUP_CODE = p_absence_category;
174 EXCEPTION
175 WHEN OTHERS THEN
176 l_abs_cat_meaning := null;
177 END;
178
179 p_input_value5 := l_abs_cat_meaning;
180 p_input_value_name6 := 'CREATOR_ID';
181 p_input_value6 := p_absence_attendance_id;
182
183 END IF;
184
185 -- Return Y indicating to process the element for the input assignment id.
186 RETURN 'Y';
187 END Element_populate;
188
189 END PAY_NO_ABSENCE_USER;
[Home] [Help]