1 PACKAGE HXC_RETRIEVE_ABSENCES AUTHID CURRENT_USER AS
2 /* $Header: hxcretabs.pkh 120.0.12010000.11 2010/01/08 09:22:17 bbayragi noship $ */
3
4
5 TYPE abs_rec IS RECORD
6 ( abs_date DATE,
7 abs_type_id NUMBER,
8 element_type_id NUMBER,
9 duration NUMBER,
10 abs_attendance_id NUMBER,
11 abs_start DATE,
12 abs_end DATE,
13 prg_appl_id NUMBER,
14 modetype VARCHAR2(50),
15 rec_start_date DATE,
16 rec_end_date DATE,
17 UOM VARCHAR2(50),
18 transaction_id NUMBER,
19 confirmed_flag VARCHAR2(10)
20 );
21
22 TYPE ABS_TAB IS TABLE OF abs_rec INDEX BY BINARY_INTEGER;
23 g_abs_tab ABS_TAB;
24
25 -- Bug 8855103
26 -- Following types added to help processing rec update validations.
27 TYPE ABS_ID_REC IS RECORD
28 ( abs_name VARCHAR2(500),
29 abs_id NUMBER,
30 run_total VARCHAR2(5),
31 UOM VARCHAR2(5)
32 );
33
34
35 TYPE ABS_ID_TAB IS TABLE OF ABS_ID_REC INDEX BY BINARY_INTEGER;
36 g_abs_id_tab ABS_ID_TAB;
37
38
39 TYPE ABS_TABLE_REC IS
40 RECORD
41 ( emp_abs_tab ABS_TAB
42 );
43
44 g_lock_row_id VARCHAR2(50);
45 g_person_id NUMBER;
46 g_start_time DATE;
47 g_stop_time DATE;
48
49 TYPE detail_trans IS RECORD
50 ( detail_bb_id NUMBER,
51 detail_bb_ovn NUMBER);
52
53 TYPE detail_trans_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
54
55 g_detail_trans_tab detail_trans_tab;
56
57 TYPE cached_abs_tab IS TABLE OF ABS_TABLE_REC INDEX BY VARCHAR2(30);
58
59 g_cached_abs_tab cached_abs_tab;
60
61
62 TYPE abs_status_rec IS RECORD
63 (
64 abs_type VARCHAR2(400),
65 abs_start VARCHAR2(30),
66 abs_end VARCHAR2(30),
67 measure VARCHAR2(30),
68 status VARCHAR2(40),
69 UOM VARCHAR2(50),
70 source VARCHAR2(50)
71 );
72
73 TYPE abs_status_tab IS TABLE OF abs_status_rec INDEX BY BINARY_INTEGER;
74
75 -- Bug 8855103
76 -- Added new type to handle assignments.
77 TYPE ASGTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
78 g_asgtab ASGTAB;
79
80
81 TYPE ABSAPIREC IS RECORD
82 ( time_building_block_id NUMBER,
83 element_type_id NUMBER,
84 absence_type_id NUMBER,
85 absence_att_id NUMBER,
86 start_time DATE,
87 stop_time DATE);
88
89 TYPE ABSAPITAB IS TABLE OF ABSAPIREC INDEX BY BINARY_INTEGER;
90
91
92
93
94 TYPE NUMTAB IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
95
96 g_day_tab NUMTAB;
97 g_trace_id NUMBER;
98
99 g_messages HXC_MESSAGE_TABLE_TYPE;
100 g_message_string VARCHAR2(32000);
101
102
103 -- Bug 9019114
104 -- To hold BEE retrieval process id and
105 -- bld_blk_info_type_id for Dummy Element Context.
106 g_bee_retrieval NUMBER;
107 g_bld_blk_info NUMBER;
108
109
110 PROCEDURE retrieve_absences( p_person_id IN NUMBER,
111 p_start_date IN DATE,
112 p_end_date IN DATE,
113 p_abs_tab IN OUT NOCOPY hxc_retrieve_absences.abs_tab);
114
115
116
117
118 PROCEDURE add_absence_types ( p_person_id IN NUMBER,
119 p_start_date IN DATE,
120 p_end_date IN DATE,
121 p_approval_style_id IN NUMBER,
122 p_lock_rowid IN VARCHAR2,
123 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
124 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE );
125
126 -- Overloaded for TK
127
128 PROCEDURE add_absence_types ( p_person_id IN NUMBER,
129 p_start_date IN DATE,
130 p_end_date IN DATE,
131 p_approval_style_id IN NUMBER,
132 p_lock_rowid IN VARCHAR2,
133 p_source IN VARCHAR2 ,
134 p_timekeeper_id IN NUMBER,
135 p_iteration_count IN NUMBER,
136 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
137 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE );
138
139
140 PROCEDURE tc_api_add_absence_types ( p_person_id IN NUMBER,
141 p_start_date IN DATE,
142 p_end_date IN DATE,
143 p_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
144 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info);
145
146
147 PROCEDURE tc_api_add_absence_types ( p_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
148 p_app_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info);
149
150 PROCEDURE create_tc_with_abs ( p_person_id IN NUMBER,
151 p_start_date IN DATE,
152 p_end_date IN DATE,
153 p_approval_style_id IN NUMBER,
154 p_lock_rowid IN VARCHAR2,
155 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
156 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE );
157
158 -- Overloaded for TK
159
160 PROCEDURE create_tc_with_abs ( p_person_id IN NUMBER,
161 p_start_date IN DATE,
162 p_end_date IN DATE,
163 p_approval_style_id IN NUMBER,
164 p_lock_rowid IN VARCHAR2,
165 p_source IN VARCHAR2,
166 p_timekeeper_id IN NUMBER,
167 p_iteration_count IN NUMBER,
168 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
169 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE );
170
171
172 PROCEDURE add_abs_to_tc ( p_person_id IN NUMBER,
173 p_start_date IN DATE,
174 p_end_date IN DATE,
175 p_approval_style_id IN NUMBER,
176 p_block_array IN OUT NOCOPY HXC_BLOCK_TABLE_TYPE,
177 p_attribute_array IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE ,
178 p_lock_rowid IN VARCHAR2 );
179
180
181
182 FUNCTION get_day_block ( p_date IN DATE,
183 p_person_id IN NUMBER,
184 p_approval_style_id IN NUMBER,
185 p_timecard_id IN NUMBER,
186 p_bb_id IN NUMBER)
187 RETURN HXC_BLOCK_TYPE;
188
189
190 FUNCTION get_detail_block ( p_start_time IN DATE,
191 p_stop_time IN DATE,
192 p_measure IN NUMBER,
193 p_person_id IN NUMBER,
194 p_approval_style_id IN NUMBER,
195 p_day_id IN NUMBER,
196 p_bb_id IN NUMBER,
197 p_bb_ovn IN NUMBER,
198 p_date_to IN DATE DEFAULT hr_general.end_of_time)
199 RETURN HXC_BLOCK_TYPE;
200
201 FUNCTION get_attribute_for_detail ( p_bb_id IN NUMBER,
202 p_bb_ovn IN NUMBER,
203 p_element_type_id IN NUMBER,
204 p_abs_att_id IN NUMBER,
205 p_attribute_id IN NUMBER)
206 RETURN HXC_ATTRIBUTE_TYPE;
207
208 FUNCTION get_alias_for_detail ( p_bb_id IN NUMBER,
209 p_bb_ovn IN NUMBER,
210 p_element_type_id IN NUMBER,
211 p_attribute_id IN NUMBER)
212 RETURN HXC_ATTRIBUTE_TYPE;
213
214 PROCEDURE gen_alt_ids ( p_person_id IN NUMBER,
215 p_start_time IN DATE,
216 p_stop_time IN DATE,
217 p_mode IN VARCHAR2);
218
219 PROCEDURE get_abs_statuses ( p_person_id IN NUMBER,
220 p_start_date IN DATE,
221 p_end_date IN DATE,
222 p_abs_status_rec OUT NOCOPY ABS_STATUS_TAB);
223
224
225 PROCEDURE get_abs_statuses ( p_person_id IN NUMBER,
226 p_start_date IN VARCHAR2,
227 p_end_date IN VARCHAR2,
228 p_abs_status_tab OUT NOCOPY HXC_ABS_STATUS_TABLE);
229
230
231 PROCEDURE record_carried_over_absences( p_bb_id IN NUMBER,
232 p_bb_ovn IN NUMBER,
233 p_abs_id IN NUMBER,
234 p_abs_att_id IN NUMBER,
235 p_element IN NUMBER,
236 p_start_date IN DATE,
237 p_end_date IN DATE,
238 p_uom IN VARCHAR2,
239 p_measure IN NUMBER,
240 p_stage IN VARCHAR2,
241 p_resource_id IN NUMBER,
242 p_tc_start IN DATE,
243 p_tc_stop IN DATE,
244 p_lock_rowid IN VARCHAR2,
245 p_transaction_id IN NUMBER DEFAULT NULL,
246 p_action IN VARCHAR2 DEFAULT NULL ,
247 p_conf IN VARCHAR2 DEFAULT 'Y' );
248
249 PROCEDURE update_co_absences ( p_old_bb_id IN NUMBER,
250 p_new_bb_id IN NUMBER,
251 p_start_time IN DATE,
252 p_stop_time IN DATE,
253 p_element_id IN NUMBER);
254
255 PROCEDURE update_co_absences_ovn ( p_old_bb_id IN hxc_time_building_blocks.time_building_block_id%type,
256 p_new_ovn IN NUMBER,
257 p_start_time IN DATE,
258 p_stop_time IN DATE,
259 p_element_id IN NUMBER);
260
261 PROCEDURE delete_other_sessions ( p_resource_id IN NUMBER,
262 p_start_time IN DATE,
263 p_stop_time IN DATE,
264 p_lock_rowid IN VARCHAR2);
265
266 PROCEDURE insert_audit_header ( p_resource_id IN NUMBER,
267 p_start_time IN DATE,
268 p_stop_time IN DATE,
269 p_transaction_id IN OUT NOCOPY NUMBER);
270
271 PROCEDURE insert_audit_details ( p_resource_id IN NUMBER,
272 p_detail_bb_id IN NUMBER,
273 p_detail_ovn IN NUMBER,
274 p_header_id IN NUMBER);
275
276 PROCEDURE manage_retrieval_audit(p_resource_id IN NUMBER,
277 p_start_time IN DATE,
278 p_stop_time IN DATE);
279
283 p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE);
280 PROCEDURE verify_view_only_absences(p_blocks IN HXC_BLOCK_TABLE_TYPE,
281 p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE,
282 p_lock_rowid IN VARCHAR2,
284
285 PROCEDURE clear_prev_sessions(p_resource_id IN NUMBER,
286 p_tc_start IN DATE,
287 p_tc_stop IN DATE,
288 p_lock_rowid IN VARCHAR2);
289
290
291 FUNCTION get_lookup_value( p_lookup_type IN VARCHAR2,
292 p_lookup_code IN VARCHAR2)
293 RETURN VARCHAR2;
294
295
296 -- Bug 8911152
297 -- Added new function to build Layout Attribute
298 FUNCTION get_layout_attribute ( p_bb_id IN NUMBER,
299 p_bb_ovn IN NUMBER,
300 p_attribute_id IN NUMBER)
301
302 RETURN HXC_ATTRIBUTE_TYPE;
303
304 -- Bug 8855103
305 -- Added the below functions to process various
306 -- requests.
307
308 FUNCTION get_absence_id (p_element_type_id IN NUMBER)
309 RETURN NUMBER;
310
311 FUNCTION get_absence_details(p_element_type_id IN NUMBER)
312 RETURN NUMBER;
313
314 FUNCTION get_absence_name(p_element_type_id IN NUMBER)
315 RETURN VARCHAR2;
316
317 FUNCTION get_abs_running(p_element_type_id IN NUMBER)
318 RETURN VARCHAR2;
319
320 FUNCTION get_absence_uom(p_element_type_id IN NUMBER)
321 RETURN VARCHAR2;
322
323 FUNCTION get_assignment_id (p_person_id IN NUMBER,
324 p_start_time IN DATE)
325 RETURN NUMBER;
326
327
328 -- Bug 9019114
329 -- To populate BEE retrieval process id and
330 -- bld_blk_info_type_id for Dummy Element Context into the
331 -- globals.
332
333 PROCEDURE populate_globals;
334
335 -- Added for OTL ABS Integration 8888902
336 -- OTL-ABS START
337 PROCEDURE insert_absence_summary_row;
338 PROCEDURE update_absence_summary_row(p_resource_id IN NUMBER,
339 p_tc_start IN DATE,
340 p_tc_stop IN DATE,
341 p_abs_days IN NUMBER,
342 p_abs_hours IN NUMBER
343 );
344 PROCEDURE clear_absence_summary_rows;
345 PROCEDURE is_absence_element(p_alias_value_id IN NUMBER,
346 p_absence_element_flag OUT NOCOPY VARCHAR2
347 );
348
349 -- OTL-ABS END
350
351
352 END HXC_RETRIEVE_ABSENCES;
353
[Home] [Help]