1 PACKAGE BODY PAY_NO_ABSENCE_USER AS
2 /*$Header: pynoabsusr.pkb 120.0.12010000.2 2008/08/06 08:03:53 ubhat 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
57 -- Cursor to fetch previous sickness and part-time sickness absences for
58 -- find out the starting linking absence.
59 CURSOR CSR_CONT_LINK (l_person_id IN number,l_abs_st_date IN date) IS
60 SELECT paa.absence_attendance_id
61 ,paa.date_start
62 ,paa.date_end
63 FROM per_absence_attendances paa, per_absence_attendance_types pat
64 WHERE paa.person_id = l_person_id
65 AND paa.date_start < l_abs_st_date
66 AND paa.date_end < l_abs_st_date
67 AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL
68 AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
69 AND pat.absence_category IN ('S','PTS')
70 ORDER BY paa.date_end desc ;
71
72 -- Cursor to fetch global values
73 CURSOR csr_get_glb_value(p_global_name IN VARCHAR2, p_effective_date IN DATE) IS
74 SELECT to_number(global_value)
75 FROM ff_globals_f
76 WHERE global_name = p_global_name
77 AND legislation_code = 'NO'
78 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
79
80 l_start_date date;
81 l_end_date date;
82 l_absent_reason number;
83 l_start_time varchar2(20);
84 l_end_time varchar2(20);
85 l_abs_cat_meaning varchar2(100);
86 --l_initial_abs_creator_id NUMBER ;
87 l_initial_abs_creator_id varchar2(60) ;
88 l_intial_absence CHAR;
89 l_loop_start_date date;
90 prev_link_abs_exist varchar2(10);
91 l_abs_attn_id number;
92 l_abs_link_period number;
93
94 BEGIN
95
96 -- Fetch absence attendance details
97 BEGIN
98 SELECT abs.date_start
99 ,abs.date_end
100 ,abs.ABS_ATTENDANCE_REASON_ID
101 ,abs.TIME_START
102 ,abs.TIME_END
103 ,abs.abs_information15
104 ,abs.abs_information16
105 INTO l_start_date
106 ,l_end_date
107 ,l_absent_reason
108 ,l_start_time
109 ,l_end_time
110 ,l_intial_absence
111 ,l_initial_abs_creator_id
112 FROM per_absence_attendances abs
113 WHERE abs.absence_attendance_id = p_absence_attendance_id;
114 EXCEPTION
115 WHEN OTHERS THEN
116 NULL;
117 END;
118
119 IF p_absence_category in ('S', 'PTS') AND
120 nvl(l_intial_absence,'Y') = 'Y' THEN
121 OPEN csr_get_glb_value('NO_ABS_LINK_PERIOD',l_start_date);
122 FETCH csr_get_glb_value INTO l_abs_link_period;
123 CLOSE csr_get_glb_value;
124
125 l_loop_start_date := l_start_date ; /* assign the current absence start date */
126 prev_link_abs_exist := 'N';
127 FOR i in CSR_CONT_LINK (p_person_id, l_start_date) LOOP
128 IF ( l_loop_start_date - i.date_end ) <= l_abs_link_period then
129 prev_link_abs_exist := 'Y';
130 l_abs_attn_id := i.absence_attendance_id;
131 ELSE -- No linking absence exists.
132 EXIT;
133 END IF;
134 l_loop_start_date := i.date_start;
135 END LOOP;
136
137 IF prev_link_abs_exist = 'Y' THEN
138 OPEN csr_get_intial_entry_id(to_char(l_abs_attn_id),p_assignment_id);
139 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
140 CLOSE csr_get_intial_entry_id;
141 END IF;
142 ELSE -- Other than S and PTS Categories
143 IF l_intial_absence = 'N' THEN
144 OPEN csr_get_intial_entry_id(l_initial_abs_creator_id,p_assignment_id);
145 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
146 CLOSE csr_get_intial_entry_id;
147 /* Commented Setting original entry id for initial absences*/
148 /*ELSE
149 If initial absence = 'Y' then set the element entry id as original entry id
150 OPEN csr_get_intial_entry_id(p_absence_attendance_id);
151 FETCH csr_get_intial_entry_id INTO p_original_entry_id;
152 CLOSE csr_get_intial_entry_id;*/
153 END IF ;
154 END IF;
155 -- Check if absence category is S ( Sickness )
156 IF p_absence_category in ('S', 'PTS', 'CMS', 'PA', 'PTP', 'M', 'PTM', 'IE_AL', 'VAC','PTA') THEN
157 p_input_value_name1 := 'Start Date';
158 p_input_value1 := fnd_date.date_to_displaydate(l_start_date); -- date conversion for 6850183
159 p_input_value_name2 := 'End Date';
160 p_input_value2 := fnd_date.date_to_displaydate(l_end_date); -- date conversion for 6850183
161 p_input_value_name5 := 'Absence Category';
162
163 -- To select absence category meaning by passing code
164 BEGIN
165 SELECT MEANING
166 INTO l_abs_cat_meaning
167 --FROM FND_LOOKUP_VALUES
168 -- Version 115.1 , Changed query to get value from hr_lookups
169 FROM HR_LOOKUPS
170 WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
171 AND ENABLED_FLAG = 'Y'
172 AND LOOKUP_CODE = p_absence_category;
173 EXCEPTION
174 WHEN OTHERS THEN
175 l_abs_cat_meaning := null;
176 END;
177
178 p_input_value5 := l_abs_cat_meaning;
179 p_input_value_name6 := 'CREATOR_ID';
180 p_input_value6 := p_absence_attendance_id;
181
182 END IF;
183
184 -- Return Y indicating to process the element for the input assignment id.
185 RETURN 'Y';
186 END Element_populate;
187
188 END PAY_NO_ABSENCE_USER;
[Home] [Help]