[Home] [Help]
PACKAGE BODY: APPS.HXC_ABS_INTG_PKG
Source
1 PACKAGE BODY HXC_ABS_INTG_PKG as
2 /* $Header: hxcabsint.pkb 120.1.12010000.8 2009/10/15 06:23:37 sabvenug noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 PROCEDURE HXC_TC_CHECK (p_resource_id IN VARCHAR2,
7 p_start_time IN DATE,
8 p_stop_time IN DATE,
9 p_tc_sum_exists OUT NOCOPY BOOLEAN,
10 p_tc_lock_exists OUT NOCOPY BOOLEAN)
11
12 IS
13
14 l_sum_check VARCHAR2(1):='0';
15 l_lock_check VARCHAR2(1):='0';
16
17 CURSOR tc_sum_check(p_resource_id IN NUMBER,
18 p_start_time IN DATE,
19 p_stop_time IN DATE)
20 IS
21
22 select '1'
23 from hxc_timecard_summary
24 where resource_id = p_resource_id
25 and start_time <= p_stop_time
26 and stop_time >= p_start_time;
27 --and approval_status <> 'ERROR'; -- Added Error TC check Bug 9014012
28
29
30 CURSOR tc_lock_check(p_resource_id IN NUMBER,
31 p_start_time IN DATE,
32 p_stop_time IN DATE)
33 IS
34
35 select '1'
36 from hxc_locks
37 where resource_id = p_resource_id
38 and start_time <= p_stop_time
39 and stop_time >= p_start_time
40 and (sysdate - lock_date) < (20/(24*60)); -- Added 20 min max locking period Bug 9014012
41
42
43
44
45 BEGIN
46
47 -- Timecard Summary check
48 if g_debug then
49 hr_utility.trace('Entered HXC_TC_CHECK');
50 end if;
51
52
53 open tc_sum_check(p_resource_id,
54 p_start_time,
55 p_stop_time);
56
57 Fetch tc_sum_check into l_sum_check;
58
59 IF (tc_sum_check%FOUND) then
60
61 p_tc_sum_exists := TRUE;
62
63 if g_debug then
64 hr_utility.trace('p_tc_sum_exists is TRUE');
65 end if;
66
67 ELSE
68
69 p_tc_sum_exists := FALSE;
70
71 if g_debug then
72 hr_utility.trace('p_tc_sum_exists is FALSE');
73 end if;
74
75 END IF;
76
77 CLOSE tc_sum_check;
78
79
80
81
82 -- Hxc Locks Check
83
84 open tc_lock_check(p_resource_id,
85 p_start_time,
86 p_stop_time + g_one_day);
87
88 Fetch tc_lock_check into l_sum_check;
89
90 IF (tc_lock_check%FOUND) then
91
92 p_tc_lock_exists := TRUE;
93
94 if g_debug then
95 hr_utility.trace('p_tc_sum_exists is TRUE');
96 end if;
97
98 ELSE
99
100 p_tc_lock_exists := FALSE;
101
102 if g_debug then
103 hr_utility.trace('p_tc_sum_exists is FALSE');
104 end if;
105
106 END IF;
107
108 CLOSE tc_lock_check;
109
110 if g_debug then
111 hr_utility.trace('Leaving HXC_TC_CHECK');
112 end if;
113
114 END; -- hxc_tc_check
115
116
117
118
119
120
121
122
123 --------------------------------------------------------------------------------------------
124
125
126 PROCEDURE otl_timecard_chk(p_person_id IN NUMBER,
127 p_start_time IN DATE,
128 p_stop_time IN DATE,
129 p_error_code OUT NOCOPY VARCHAR2,
130 p_error_level OUT NOCOPY NUMBER,
131 p_abs_att_id IN NUMBER DEFAULT -1)
132 IS
133
134 l_tc_sum_exists BOOLEAN:=FALSE;
135 l_tc_lock_exists BOOLEAN:=FALSE;
136
137 l_pref_set VARCHAR2(1):='N';
138 l_profile_set VARCHAR2(1):='N';
139
140 l_pref_table hxc_preference_evaluation.t_pref_table;
141 l_pref_index NUMBER;
142
143 l_abs_att_id_count NUMBER:=0;
144
145 /* Bug 9014012 -- Abs Attendance id imported to OTL check */
146 CURSOR abs_att_id_check (p_abs_att_id IN NUMBER)
147 IS
148 select 1
149 from hxc_absence_type_elements
150 where absence_attendance_type_id = p_abs_att_id;
151
152
153
154 BEGIN
155
156 if g_debug then
157 hr_utility.trace('Entered otl_timecard_chk');
158 end if;
159
160 OPEN abs_att_id_check(p_abs_att_id);
161 FETCH abs_att_id_check into l_abs_att_id_count;
162
163 IF (abs_att_id_check%FOUND OR p_abs_att_id=-1) then
164
165 l_profile_set:= NVL(fnd_profile.value('HR_ABS_OTL_INTEGRATION'),'N');
166
167 if g_debug then
168 hr_utility.trace('Profile Value = '|| l_profile_set );
169 end if;
170
171
172 IF l_profile_set<>'Y' then
173
174 p_error_level:=0;
175 p_error_code:= 'HR OTL Integration Profile Not set';
176
177
178 if g_debug then
179 hr_utility.trace(' No OTL Intg Profile Set');
180 end if;
181
182
183 ELSE
184
185 if g_debug then
186 hr_utility.trace('Going to call OTL Pref');
187 end if;
188
189 hxc_preference_evaluation.resource_preferences
190 (p_person_id,
191 p_start_time,
192 p_start_time,
193 l_pref_table);
194
195 if g_debug then
196 hr_utility.trace('Came out of OTL Pref');
197 end if;
198
199 l_pref_index := l_pref_table.FIRST;
200 LOOP
201 IF l_pref_table(l_pref_index).preference_code = 'TS_ABS_PREFERENCES'
202 THEN
203 l_pref_set := NVL(l_pref_table(l_pref_index).attribute1,
204 'N');
205 EXIT;
206 END IF;
207 l_pref_index := l_pref_table.NEXT(l_pref_index);
208 EXIT WHEN NOT l_pref_table.EXISTS(l_pref_index);
209 END LOOP;
210
211 if g_debug then
212 hr_utility.trace('OTL Pref Setting for this resource = '||l_pref_set);
213 end if;
214
215 IF l_pref_set <>'Y' then
216
217 p_error_level:=0;
218 p_error_code:= 'No OTL Absence Preferences';
219
220 if g_debug then
221 hr_utility.trace('No OTL Pref');
222 end if;
223
224 ELSE
225
226 hxc_tc_check (p_resource_id => p_person_id,
227 p_start_time => p_start_time,
228 p_stop_time => p_stop_time,
229 p_tc_sum_exists => l_tc_sum_exists,
230 p_tc_lock_exists => l_tc_lock_exists);
231
232 if g_debug then
233 hr_utility.trace('Came out of hxc_tc_check');
234 end if;
235
236
237 if p_start_time > p_stop_time then
238
239 p_error_level:=-1;
240 p_error_code:='Error: Start Date Greater than End Date';
241
242 if g_debug then
243 hr_utility.trace('p_error_level1 = '||p_error_level);
244 end if;
245
246 elsif (l_tc_lock_exists) then
247
248 p_error_level:=2;
249 p_error_code:= 'OTL Timecard is locked';
250
251 if g_debug then
252 hr_utility.trace('p_error_level2 = '||p_error_level);
253 end if;
254
255 ELSIF (l_tc_sum_exists) then
256
257 p_error_level:=1;
258 p_error_code:= 'OTL Timecard Exists';
259
260 if g_debug then
261 hr_utility.trace('p_error_level3 = '||p_error_level);
262 end if;
263
264 ELSE
265
266 p_error_level:=0;
267 p_error_code:='No OTL Timecard Exists';
268
269 if g_debug then
270 hr_utility.trace('p_error_level4 = '||p_error_level);
271 end if;
272
273 END IF; -- l_tc_lock_exists and l_tc_sum_exists
274
275 END IF; -- l_pref_set
276
277 END IF ; -- l_profile_set
278
279 ELSE -- abs_att_id_check%FOUND
280
281 p_error_level:=0;
282 p_error_code:='Element Not Imported to OTL';
283
284 END IF; -- abs_att_id_check%FOUND
285
286
287 CLOSE abs_att_id_check;
288
289
290
291 if g_debug then
292 hr_utility.trace('Leaving otl_timecard_chk');
293 end if;
294
295 END; --otl_timecard_chk ;
296
297 -----------------------------------------------------------------------------------------
298
299
300
301
302 end;