1 package body hr_au_element_entry_hook as
2 -- $Header: peaushee.pkb 120.3 2006/04/02 22:00:09 strussel noship $
3 --
4 -- Copyright (C) 2000 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- AU HRMS element entry legislative hook package.
8 --
9 -- Change List
10 --
11
12 --
13 -- Date Author Reference Description
14 -- -----------+--------+---------+------------------------------------------
15 -- 17 Jun 2001 RAGOVIND 1416342 Created
16 -- 04 Sep 2001 KAVERMA Added update_element_entry_values Procedure
17 -- 27 Mar 2006 SRUSSELL 5115516 Added check for creator_type in
18 -- update_element_entry_values.
19 -- 03 Apr 2006 SRUSSELL 5115516 Arcsd out and in to try to fix arcs
20 -- version problem.
21 -- -------------------------------------------------------------------------
22 -- populate_absence_dev_desc_flex procedure
23
24 --
25 -- This is a public procedure that is called from the PAY_ELEMENT_ENTRIES_F
26 -- after insert and after update hooks.
27 -- -------------------------------------------------------------------------
28
29 procedure insert_absence_dev_desc_flex
30 (p_effective_date in date
31 ,p_element_entry_id in number
32 ,p_creator_type in varchar2
33 ,p_element_link_id in number
34 ,p_creator_id in number) is
35
36
37 l_procedure_name varchar2(61) := 'hr_au_element_entry_hook.insert_absence_dev_desc_flex' ;
38 l_batch_id per_absence_attendances.batch_id%type ;
39 l_certificate_required pay_element_entry_values_f.screen_entry_value%type ;
40 l_certificate_received pay_element_entry_values_f.screen_entry_value%type ;
41 l_absence_category per_absence_attendance_types.absence_category%type;
42 l_element_name pay_element_types_f.element_name%type;
43
44 -- cursor to get the batch ID for the absence
45
46 cursor c_absence (p_absence_attendance_id number)
47 is
48 select aa.batch_id
49 from per_absence_attendances aa
50 where aa.absence_attendance_id = p_absence_attendance_id ;
51
52 -- cursor to get input value
53
54 cursor c_input_value (p_element_entry_id number
55 ,p_effective_date date
56 ,p_input_name varchar2)
57 is
58 select eev.screen_entry_value
59 from pay_input_values_f iv
60 ,pay_element_entry_values_f eev
61 where eev.element_entry_id = p_element_entry_id
62 and p_effective_date between eev.effective_start_date
63 and eev.effective_end_date
64 and iv.input_value_id = eev.input_value_id
65 and iv.name = p_input_name
66 and p_effective_date between iv.effective_start_date
67 and iv.effective_end_date;
68
69 cursor c_absence_category ( p_element_link_id number,
70 p_effective_date date ,
71 p_element_entry_id number)
72 is
73 select paa.absence_category,
74 element_name
75 from pay_element_entries_f pee
76 ,pay_element_links_f pef
77 ,pay_element_types_f pet
78 ,pay_input_values_F piv
79 ,per_absence_attendance_types paa
80 where pee.element_link_id = pef.element_link_id
81 and pef.element_type_id = pet.element_type_id
82 and pet.element_type_id = piv.element_type_id
83 and paa.input_value_id = piv.input_value_id
84 and pee.element_link_id = p_element_link_id
85 and pee.element_entry_id = p_element_entry_id
86 and p_effective_date between piv.effective_start_date
87 and piv.effective_end_date
88 and p_Effective_date between pef.effectivE_start_date
89 and pef.effective_end_date
90 and p_effective_date between pet.effective_start_date
91 and pet.effective_end_date;
92
93 -- commenting this check because the absence element entry is effective for
94 -- that corresponding pay period
95 /*and p_Effective_date between pee.effective_start_date
96 and pee.effective_end_date */
97
98
99 begin
100
101 hr_utility.trace('In: ' || l_procedure_name) ;
102
103 -- tracing the values....
104 hr_utility.trace('p_effective_date : '||p_effective_date);
105 hr_utility.trace('p_element_entry_id :'||p_element_entry_id);
106 hr_utility.trace('p_creator_type :'||p_creator_type);
107 hr_utility.trace('p_element_link_id :'||p_element_link_id);
108 hr_utility.trace('p_creator_id :'||p_creator_id);
109
110 -- check to see if the element entry is for an absence
111
112 if p_creator_type = 'A'
113 then
114
115 -- we've got an absence element entry so now check to see if the absence
116 -- was created by batch element entry (BEE)
117
118 open c_absence(p_creator_id) ;
119 fetch c_absence
120 into l_batch_id ;
121 close c_absence ;
122
123 hr_utility.trace('l_batch_id :'||l_batch_id);
124
125 if l_batch_id is not null
126 then
127
128 open c_absence_category(p_element_link_id,
129 p_effective_date,
130 p_element_entry_id);
131 loop
132
133 fetch c_absence_category
134 into l_absence_category
135 ,l_element_name;
136
137 exit when c_absence_category%notfound;
138
139 /* Single element linked to more than one absence category */
140
141 if c_absence_category%ROWCOUNT > 1
142 then
143 fnd_message.set_name('PAY', 'HR_AU_NZ_DUP_ELEMENT_FOUND');
144 fnd_message.set_token('ELEMENT', l_element_name);
145 fnd_message.raise_error;
146 end if;
147 end loop;
148 close c_absence_category;
149
150 hr_utility.trace('l_absence_category :'||l_absence_category);
151 hr_utility.trace('l_element_name :'||l_element_name);
152
153 -- we've got an absence created by BEE so get values for Certificate Required,
154 -- Certificate Received input values to put into
155 -- corresponding segments of PER_ABS_DEVELOPER_DF descriptive
156 -- flexfield.
157
158 if l_absence_category='AUSL' then
159
160 -- look for Certificate Required
161 open c_input_value(p_element_entry_id
162 ,p_effective_date
163 ,'Certificate Required') ;
164
165 fetch c_input_value
166 into l_Certificate_required ;
167
168 hr_utility.trace('l_Certificate_required :'||l_Certificate_required);
169
170 if c_input_value%notfound
171 or l_Certificate_required is null
172 then
173 l_Certificate_required := 'N' ;
174 end if ;
175
176 close c_input_value ;
177
178 hr_utility.trace('AFTER l_Certificate_required :'||l_Certificate_required);
179
180 update per_absence_attendances
181 set abs_information1 = l_Certificate_required
182 ,abs_information_category = 'AU_' || l_absence_category
183 where absence_attendance_id = p_creator_id ;
184
185 -- look for Certificate Received
186 open c_input_value(p_element_entry_id
187 ,p_effective_date
188 ,'Certificate Received') ;
189
190
191 fetch c_input_value
192 into l_certificate_received ;
193
194 hr_utility.trace('l_Certificate_received :'||l_Certificate_received);
195
196 if c_input_value%notfound
197 or l_certificate_received is null
198 then
199 l_certificate_received := 'N' ;
200 end if ;
201
202 close c_input_value ;
203
204 hr_utility.trace('l_Certificate_received :'||l_Certificate_received);
205
206 -- now update the Descriptive Flexfield segments
207
208 update per_absence_attendances
209 set abs_information_category = 'AU_' || l_absence_category
210 ,abs_information2 = l_certificate_received
211 where absence_attendance_id = p_creator_id ;
212
213 end if; -- Absence Category
214
215 end if ; -- l_batch_id is not null
216
217 end if ; -- p_creator_type = 'A'
218
219 hr_utility.trace('Out: ' || l_procedure_name) ;
220 end insert_absence_dev_desc_flex ;
221
222 -- -------------------------------------------------------------------------
223 -- populate update_element_entry_values procedure
224
225 --
226 -- This is a public procedure that is called from the PAY_ELEMENT_ENTRIES_F
227 -- after insert and after update hooks.
228 -- -------------------------------------------------------------------------
229
230
231 procedure update_element_entry_values
232 (p_effective_date in date
233 ,p_element_entry_id in number
234 ,p_creator_type in varchar2
235 ,p_creator_id in number) is
236
237 l_procedure_name varchar2(61) := 'hr_au_element_entry_hook.update_element_entry_values' ;
238 l_start_date pay_element_entry_values_f.screen_entry_value%type;
239 l_end_date pay_element_entry_values_f.screen_entry_value%type;
240 l_abs_information1 per_absence_attendances.abs_information1%type ;
241 l_abs_information2 per_absence_attendances.abs_information2%type ;
242 l_abs_information3 per_absence_attendances.abs_information3%type ;
243 l_dff_context per_absence_attendances.abs_information_category%type ;
244 l_element_entry_value_id pay_element_entry_values_f.element_entry_value_id%type;
245
246 -- cursor to get DFF context value of Absence Information
247
248 cursor c_get_dff_context(p_creator_id number)
249 is
250 select abs_information_category
251 from per_absence_attendances
252 where absence_attendance_id = p_creator_id;
253
254 -- cursor to get DFF segment values of Absence Information
255
256 cursor c_get_dff_segment_value(p_creator_id number)
257 is
258 select to_char(date_start,'YYYY/MM/DD HH24:MI:SS'),to_char(date_end,'YYYY/MM/DD HH24:MI:SS'),abs_information1, abs_information2, abs_information3
259 from per_absence_attendances
260 where absence_attendance_id = p_creator_id;
261
262 -- cursor to get element_entry_value_id for input values of Absence Element
263
264 cursor get_element_entry_value_id(p_element_entry_id number
265 ,p_input_name varchar2)
266 is
267 select peev.element_entry_value_id
268 from pay_element_entry_values_f peev,pay_input_values_f piv
269 where peev.element_entry_id = p_element_entry_id
270 and piv.name = p_input_name
271 and peev.input_value_id = piv.input_value_id;
272
273 begin
274
275 hr_utility.trace('In: ' || l_procedure_name) ;
276
277 -- tracing the values....
278 hr_utility.trace('p_effective_date : '||p_effective_date);
279 hr_utility.trace('p_element_entry_id :'||p_element_entry_id);
280 hr_utility.trace('p_creator_type :'||p_creator_type);
281 hr_utility.trace('p_creator_id :'||p_creator_id);
282
283 if p_creator_type = 'A'
284 then
285
286 -- get context value
287 open c_get_dff_context(p_creator_id);
288 fetch c_get_dff_context into l_dff_context;
289 close c_get_dff_context;
290
291 --proceed if context is AU Annual leave or AU Long Service Leave
292
293 IF(l_dff_context = 'AU_AUAL' or l_dff_context = 'AU_AULSL') THEN
294
295 open c_get_dff_segment_value(p_creator_id);
296 fetch c_get_dff_segment_value into l_start_date,l_end_date,l_abs_information1,l_abs_information2,l_abs_information3;
297 close c_get_dff_segment_value;
298
299 open get_element_entry_value_id(p_element_entry_id,'Start Date');
300 fetch get_element_entry_value_id into l_element_entry_value_id;
301 close get_element_entry_value_id;
302
303 hr_utility.trace('Updating Start Date entry value as l_start_date :'||l_start_date);
304
305 update pay_element_entry_values_f
306 set screen_entry_value = l_start_date
307 where element_entry_value_id = l_element_entry_value_id;
308
309 open get_element_entry_value_id(p_element_entry_id,'End Date');
310 fetch get_element_entry_value_id into l_element_entry_value_id;
311 close get_element_entry_value_id;
312
313 hr_utility.trace('Updating End Date entry value as l_end_date :'||l_end_date);
314
315 update pay_element_entry_values_f
316 set screen_entry_value = l_end_date
317 where element_entry_value_id = l_element_entry_value_id;
318
319 open get_element_entry_value_id(p_element_entry_id,'Pay Date');
320 fetch get_element_entry_value_id into l_element_entry_value_id;
321 close get_element_entry_value_id;
322
323 hr_utility.trace('Updating Pay Date entry value as abs_information1 :'||l_abs_information1);
324
325 update pay_element_entry_values_f
326 set screen_entry_value = l_abs_information1
327 where element_entry_value_id = l_element_entry_value_id;
328
329 open get_element_entry_value_id(p_element_entry_id,'Advance Defer');
330 fetch get_element_entry_value_id into l_element_entry_value_id;
331 close get_element_entry_value_id;
332
333 hr_utility.trace('Updating Start Advance Defer value as abs_information2 :'||l_abs_information2);
334
335 update pay_element_entry_values_f
336 set screen_entry_value = l_abs_information2
337 where element_entry_value_id = l_element_entry_value_id;
338
339 open get_element_entry_value_id(p_element_entry_id,'Advance Override');
340 fetch get_element_entry_value_id into l_element_entry_value_id;
341 close get_element_entry_value_id;
342
343 hr_utility.trace('Updating Advance Override entry value as abs_information3 :'||l_abs_information3);
344
345 update pay_element_entry_values_f
346 set screen_entry_value = l_abs_information3
347 where element_entry_value_id = l_element_entry_value_id;
348
349 END IF; -- l_dff_context.
350
351 end if; -- p_creator_type = 'A'.
352
353 end update_element_entry_values;
354
355 end hr_au_element_entry_hook ;
[Home] [Help]