[Home] [Help]
PACKAGE BODY: APPS.PER_NL_ABSENCE_TRACK_PKG
Source
1 PACKAGE BODY PER_NL_ABSENCE_TRACK_PKG AS
2 /* $Header: penlabst.pkb 120.2 2006/09/07 11:13:40 niljain noship $ */
3 g_package varchar2(33) := ' PER_NL_ABSENCE_TRACK_PKG.';
4
5 --
6 -- Creates Default Absence Actions for a Employee if no actions
7 -- are entered for a employee
8 PROCEDURE create_Default_Absence_Actions
9 (p_absence_attendance_id number,
10 p_effective_date date,
11 p_return_status in out nocopy varchar2
12 ) IS
13
14 l_business_group_id number;
15 l_user_table_name pay_user_tables.user_table_name%TYPE;
16
17
18 l_action pay_user_column_instances_f.value%TYPE;
19 l_time_period pay_user_column_instances_f.value%TYPE;
20 l_units pay_user_column_instances_f.value%TYPE;
21
22
23 l_expected_date date;
24 l_absence_action_id number ;
25 l_object_version_number number ;
26
27 /* Bug # 5225587 - added extra parameter vp_effective_date date */
28
29 CURSOR c_user_rows(vp_business_group_id number,
30 vp_user_table_name varchar2,
31 vp_effective_date date) IS
32 SELECT pur.row_low_range_or_name
33 FROM pay_user_rows_f pur,
34 pay_user_tables put
35 WHERE put.user_table_id=pur.user_table_id
36 and put.business_group_id = vp_business_group_id
37 and put.user_table_name =vp_user_table_name
38 and vp_effective_date between
39 pur.effective_start_date
40 and pur.effective_end_date;
41
42
43 CURSOR c_abs_actions IS
44 SELECT '1' from PER_NL_ABSENCE_ACTIONS
45 where absence_attendance_id = p_absence_attendance_id;
46
47
48
49 l_row_number pay_user_rows_f.row_low_range_or_name%TYPE;
50 l_proc varchar2(72) := g_package || '.create_Default_Absence_Actions';
51 l_date_start date;
52 l_setup_exists varchar2(30);
53 l_actions_exists varchar2(30);
54 BEGIN
55
56 l_setup_exists:= 'E';
57 l_actions_exists :='N';
58
59 hr_utility.set_location('Entering ' || l_proc, 100);
60
61
62 --Fetch the Business Group Id and User Table Name
63 chk_Abs_Action_Setup_Exists
64 (p_absence_attendance_id ,
65 l_business_group_id,l_user_table_name,
66 l_date_start,l_setup_exists);
67
68 p_return_status := l_setup_exists;
69
70 hr_utility.set_location(' l_business_group_id : ' || l_business_group_id, 110);
71 hr_utility.set_location(' l_user_table_name ' || l_user_table_name, 120);
72 hr_utility.set_location(' l_date_start ' || l_date_start, 130);
73 hr_utility.set_location(' p_return_status ' || p_return_status, 140);
74
75
76 OPEN c_abs_actions;
77 FETCH c_abs_actions INTO l_actions_exists;
78 IF c_abs_actions%FOUND THEN
79 --Absence Actions have already been created for the Absence
80 l_actions_exists:='Y' ;
81 else
82 --Absence Actions are not created for this Absence
83 --continue with the rest
84 l_actions_exists:='N' ;
85 END IF;
86 CLOSE c_abs_actions;
87
88 --Create the Default Absence Action, Only if the
89 --User Table is set up and
90 --Absence Actions has not been entered for the Absence.
91
92 --2651341
93 --Added Savepoint to Rollback
94 --In Case User do not completely define the User Table
95 SAVEPOINT InvalidUDTSetup;
96
97 IF l_setup_exists='S' AND l_actions_exists='N' THEN
98 hr_utility.set_location('Abs Action Setup exists ' || l_setup_exists, 200);
99
100 --Fetch the Rows from the User Table
101 OPEN c_user_rows(l_business_group_id,l_user_table_name,l_date_start);
102 LOOP
103 FETCH c_user_rows INTO l_row_number;
104 EXIT WHEN c_user_rows%NOTFOUND;
105 hr_utility.set_location('Fetching Action Rows ' || l_row_number, 210);
106
107 l_expected_date := null;
108 l_action := null;
109 l_time_period := null;
110 l_units := null;
111
112
113 begin
114 -- Bug# 5476730, pass l_date_start instead of p_effective_date
115
116 /*l_action := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
117 'ACTION',l_row_number,p_effective_date);
118 l_time_period := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
119 'TIME_PERIOD',l_row_number,p_effective_date);
120 l_units := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
121 'TIME_UNITS',l_row_number,p_effective_date); */
122 l_action := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
123 'ACTION',l_row_number,l_date_start);
124 l_time_period := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
125 'TIME_PERIOD',l_row_number,l_date_start);
126 l_units := hruserdt.get_table_value(l_business_group_id,l_user_table_name,
127 'TIME_UNITS',l_row_number,l_date_start);
128 if l_units='D' then
129 l_expected_date := l_date_start + l_time_period;
130 elsif l_units='W' then
131 l_expected_date := l_date_start + l_time_period * 7;
132 elsif l_units='M' then
133 l_expected_date := add_months(l_date_start,l_time_period);
134 else
135 p_return_status := 'E';
136 exit;
137 end if;
138
139
140 hr_utility.set_location('Row Number ' || l_row_number, 220);
141 hr_utility.set_location('Action ' || l_action, 230);
142 hr_utility.set_location('Time Period ' || l_time_period, 240);
143 hr_utility.set_location('Unit ' || l_units, 250);
144 hr_utility.set_location('Expected Date ' || l_expected_date, 260);
145
146 exception
147 when Others then
148 p_return_status := 'E';
149 hr_utility.set_location(' Others :' || SQLERRM(SQLCODE),900);
150 exit;
151 end;
152 --Call the SWI Wrapper for Creating the Action
153 hr_nl_absence_action_swi.create_absence_action
154 (p_absence_attendance_id =>p_absence_attendance_id,
155 p_enabled =>'Y',
156 p_expected_date =>l_expected_date,
157 p_description =>l_action,
158 p_absence_action_id =>l_absence_action_id,
159 p_object_version_number =>l_object_version_number,
160 p_return_status =>p_return_status
161 ) ;
162 hr_utility.set_location('p_absence_action_id ' || l_absence_action_id, 910);
163 hr_utility.set_location('p_object_version_number ' || l_object_version_number, 920);
164 hr_utility.set_location('p_return_status ' || p_return_status, 930);
165
166 if p_return_status<>'S' then
167 exit;
168 end if;
169 END LOOP;
170 END IF;
171 --2651341
172 --Rollback to Savepoint if Errors are encountered
173 --While creating Default Actions.
174 IF p_return_status = 'E' THEN
175 ROLLBACK to InvalidUDTSetup;
176 END IF;
177
178 hr_utility.set_location('p_return_status ' || p_return_status, 400);
179 hr_utility.set_location('Leaving' || l_proc, 500);
180 EXCEPTION
181 WHEN OTHERS THEN
182 p_return_status := 'E';
183 ROLLBACK to InvalidUDTSetup;
184 hr_utility.set_location(' Others :' || SQLERRM(SQLCODE),900);
185 END create_Default_Absence_Actions;
186
187 PROCEDURE chk_Abs_Action_Setup_Exists
188 (p_absence_attendance_id IN number ,
189 p_business_group_id OUT nocopy NUMBER,
190 p_user_table_name OUT nocopy VARCHAR2,
191 p_start_date OUT nocopy DATE,
192 p_setup_exists OUT nocopy varchar2)IS
193
194 CURSOR c_abs_cat IS
195 select abs.absence_attendance_id,abs.business_group_id,
196 abs.absence_attendance_type_id,atyp.absence_category,
197 nvl(abs.date_start,abs.date_projected_start) start_date
198 from per_absence_attendances abs,
199 per_absence_attendance_types atyp
200 where absence_attendance_id =p_absence_attendance_id
201 and abs.absence_attendance_type_id= atyp.absence_attendance_type_id;
202
203 vc_abs_cat c_abs_cat%ROWTYPE;
204
205 CURSOR c_abs_utab(vp_business_group_id varchar2,
206 vp_user_table_name varchar2) IS
207 select user_table_id,user_table_name
208 from pay_user_tables utab
209 where business_group_id =vp_business_group_id
210 and user_table_name =vp_user_table_name;
211 vc_abs_utab c_abs_utab%ROWTYPE;
212
213 l_proc varchar2(72) := g_package || '.chk_Abs_Action_Setup_Exists';
214 BEGIN
215
216 hr_utility.set_location('Entering ' || l_proc, 100);
217 p_setup_exists := 'E';
218
219 --Fetch the Absence Category
220 OPEN c_abs_cat;
221 FETCH c_abs_cat INTO vc_abs_cat;
222 CLOSE c_abs_cat;
223
224 hr_utility.set_location('Absence Category ' || vc_abs_cat.Absence_Category, 110);
225 --If Absence Category is Not Null,
226 --Check if User Table is Set up
227 IF vc_abs_cat.Absence_Category IS NOT NULL THEN
228
229 OPEN c_abs_utab(vc_abs_cat.business_group_id,
230 'NL_ABS_ACTION_'||vc_abs_cat.absence_category);
231 FETCH c_abs_utab INTO vc_abs_utab;
232 IF c_abs_utab%FOUND THEN
233 --Default Absence Actions User Table exists
234 p_business_group_id := vc_abs_cat.Business_Group_Id;
235 p_user_table_name := 'NL_ABS_ACTION_'||vc_abs_cat.absence_category;
236 p_setup_exists := 'S';
237 p_start_date := vc_abs_cat.start_date;
238 hr_utility.set_location('p_setup_exists ' || p_setup_exists, 120);
239 END IF;
240 CLOSE c_abs_utab;
241 END IF;
242
243 hr_utility.set_location('p_setup_exists ' || p_setup_exists, 120);
244 hr_utility.set_location('Leaving ' || l_proc, 500);
245
246 END chk_Abs_Action_Setup_Exists;
247
248 END PER_NL_ABSENCE_TRACK_PKG;