1 package body hr_nz_element_entry_hook as
2 -- $Header: penzlhee.pkb 120.0.12010000.1 2008/07/28 05:03:54 appldev ship $
3 --
4 -- Copyright (C) 2000 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- NZ HRMS element entry legislative hook package.
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Reference Description
13 -- -----------+--------+---------+------------------------------------------
14 -- 25-SEP-2000 HNainani Bug#1412369 ABS_INFORMATION_CATEGORY not being
15 -- correctly populated
16 -- 07 Feb 2000 JTurner 1098494 Now also populates the
17 -- ABS_INFORMATION_CATEGORY column
18 -- 19 Jan 2000 JTurner 1098494 Now uses CREATOR_ID instead of SOURCE_ID
19 -- to join element entries to absences
20 -- 18 JAN 2000 JTURNER 1098494 Created
21
22 -- -------------------------------------------------------------------------
23 -- populate_absence_dev_desc_flex procedure
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 populate_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 l_procedure_name varchar2(61) := 'hr_nz_element_entry_hook.populate_absence_dev_desc_flex' ;
37 l_batch_id per_absence_attendances.batch_id%type ;
38 l_seasonal_shutdown pay_element_entry_values_f.screen_entry_value%type ;
39 l_number_of_complete_weeks pay_element_entry_values_f.screen_entry_value%type ;
40 l_absence_category per_absence_attendance_types.absence_category%type;
41 l_element_name pay_element_types_f.element_name%type;
42
43 -- cursor to get the batch ID for the absence
44 cursor c_absence (p_absence_attendance_id number) is
45 select aa.batch_id
46 from per_absence_attendances aa
47 where aa.absence_attendance_id = p_absence_attendance_id ;
48
49 -- cursor to get input value
50 cursor c_input_value (p_element_entry_id number
51 ,p_effective_date date
52 ,p_input_name varchar2) is
53 select eev.screen_entry_value
54 from pay_input_values_f iv
55 , pay_element_entry_values_f eev
56 where eev.element_entry_id = p_element_entry_id
57 and p_effective_date between eev.effective_start_date
58 and eev.effective_end_date
59 and iv.input_value_id = eev.input_value_id
60 and iv.name = p_input_name
61 and p_effective_date between iv.effective_start_date
62 and iv.effective_end_date ;
63
64
65 cursor c_absence_category (p_element_link_id number,
66 p_effective_date date ,
67 p_element_entry_id number) is
68 select paa.absence_category,
69 element_name
70 from
71 pay_element_entries_f pee
72 , pay_element_links_f pef
73 , pay_element_types_f pet
74 , pay_input_values_F piv
75 , per_absence_attendance_types paa
76 where pee.element_link_id= pef.element_link_id
77 and pef.element_type_id = pet.element_type_id
78 and pet.element_type_id = piv.element_type_id
79 and paa.input_value_id = piv.input_value_id
80 and pee.element_link_id = p_element_link_id
81 and pee.element_entry_id = p_element_entry_id
82 and p_effective_date between piv.effective_start_date
83 and piv.effective_end_date
84 and p_Effective_date between pee.effective_start_date
85 and pee.effective_end_date
86 and p_Effective_date between pef.effectivE_start_date
87 and pef.effective_end_date
88 and p_effective_date between pet.effective_start_date
89 and pet.effective_end_date;
90
91 begin
92 hr_utility.trace('In: ' || l_procedure_name) ;
93 -- check to see if the element entry is for an absence
94 if p_creator_type = 'A'
95 then
96
97 -- we've got an absence element entry so now check to see if the absence
98 -- was created by batch element entry (BEE)
99
100 open c_absence(p_creator_id) ;
101 fetch c_absence
102 into l_batch_id ;
103 close c_absence ;
104
105 if l_batch_id is not null
106 then
107
108 open c_absence_category(p_element_link_id,
109 p_effective_date,
110 p_element_entry_id);
111 loop
112
113 fetch c_absence_category
114 into l_absence_category,l_element_name;
115 exit when c_absence_category%notfound;
116
117
118 /* Single element linked to more than one absence category */
119
120 if c_absence_category%ROWCOUNT > 1
121 then
122 fnd_message.set_name('PAY', 'HR_AU_NZ_DUP_ELEMENT_FOUND');
123 fnd_message.set_token('ELEMENT', l_element_name);
124 fnd_message.raise_error;
125 end if;
126 end loop;
127
128 close c_absence_category;
129
130 -- we've got an absence created by BEE so get values for seasonal
131 -- shutdown and number of complete weeks input values to put into
132 -- corresponding segments of PER_ABS_DEVELOPER_DF descriptive
133 -- flexfield.
134
135
136 if l_absence_category='NZAL' then
137
138 -- look for seasonal shutdown
139 open c_input_value(p_element_entry_id
140 ,p_effective_date
141 ,'Seasonal Shutdown') ;
142
143 fetch c_input_value
144 into l_seasonal_shutdown ;
145
146 if c_input_value%notfound
147 or l_seasonal_shutdown is null
148 then
149 l_seasonal_shutdown := 'N' ;
150 end if ;
151
152 close c_input_value ;
153
154 update per_absence_attendances
155 set abs_information1 = l_seasonal_shutdown
156 , abs_information_category = 'NZ_' || l_absence_category
157 where absence_attendance_id = p_creator_id ;
158
159
160 elsif l_absence_category='NZSL'
161 then
162 -- look for number of complete weeks
163 open c_input_value(p_element_entry_id
164 ,p_effective_date
165 ,'Number of Complete Weeks') ;
166
167 fetch c_input_value
168 into l_number_of_complete_weeks ;
169
170 if c_input_value%notfound
171 or l_number_of_complete_weeks is null
172 then
173 l_number_of_complete_weeks := '0' ;
174 end if ;
175
176 close c_input_value ;
177
178 -- now update the DF segments
179 update per_absence_attendances
180 set abs_information_category = 'NZ_' || l_absence_category
181 , abs_information2 = l_number_of_complete_weeks
182 where absence_attendance_id = p_creator_id ;
183
184 end if; -- Absence Category
185
186 end if ; -- l_batch_id is not null
187
188 end if ; -- p_creator_type = 'A'
189
190 hr_utility.trace('Out: ' || l_procedure_name) ;
191 end populate_absence_dev_desc_flex ;
192
193 end hr_nz_element_entry_hook ;
[Home] [Help]