1 PACKAGE hxc_timekeeper_utilities AUTHID CURRENT_USER AS
2 /* $Header: hxctkutil.pkh 120.4.12010000.11 2009/09/17 11:48:16 sabvenug ship $ */
3 Type emptcdata is record (timecard_id number(15),
4 resource_id number(15),
5 tc_frdt date,
6 tc_todt date
7 );
8 Type emptctab is table of emptcdata index by binary_integer;
9 Type Tk_resource_perf_rec is record
10 (res_recperiod number,
11 res_negentry varchar2(150),
12 res_appstyle varchar2(150),
13 res_layout1 varchar2(150),
14 res_layout2 varchar2(150),
15 res_layout3 varchar2(150),
16 res_layout4 varchar2(150),
17 res_layout5 varchar2(150),
18 res_layout6 varchar2(150),
19 res_layout7 varchar2(150),
20 res_layout8 varchar2(150),
21 res_edits varchar2(150),
22 res_past_date varchar2(150),
23 res_future_date varchar2(150),
24 res_emp_start_date date,
25 res_emp_terminate_date date,
26 res_audit_enabled varchar2(150)
27 );
28 Type tk_resource_pref_tab is table of Tk_resource_perf_rec index by BINARY_INTEGER;
29
30
31 /* Added for 8775740 HR OTL ABSENCE INTEGRATION
32
33 */
34
35 -- change start
36 TYPE t_tk_prepop_info_rec IS RECORD
37 (ALIAS_VALUE_ID HXC_ALIAS_VALUES.ALIAS_VALUE_ID%TYPE
38 ,ITEM_ATTRIBUTE_CATEGORY VARCHAR2(80)
39 ,ABSENCE_DATE DATE
40 ,ABSENCE_DURATION NUMBER
41 ,ABSENCE_START_TIME DATE -- added
42 ,ABSENCE_STOP_TIME DATE -- added
43 ,ABSENCE_ATTENDANCE_ID NUMBER
44 ,TRANSACTION_ID NUMBER
45 );
46
47 TYPE t_tk_prepop_info_type IS TABLE OF t_tk_prepop_info_rec
48 INDEX BY BINARY_INTEGER;
49 -- change end
50
51
52
53 /*Added for Enh 3303359
54 Caching the pref value for Default Recurring Period*/
55 g_default_rec_period VARCHAR2(15);
56 g_tk_show_absences NUMBER;
57 g_abs_message_string VARCHAR2(32000);
58 g_exception_detected VARCHAR2(1);
59
60 FUNCTION get_pref_setting(p_pref IN VARCHAR2) return NUMBER;
61
62
63 ---------------------------------------------------------------------------
64 --------- GLOBAL DECLARATION
65 ---------------------------------------------------------------------------
66 g_resource_perftab tk_resource_pref_tab; --index by resource_id
67 g_start_stop_pref_cache tk_resource_pref_tab; --index by start_index of bulk pref table
68 ----------------------------------------------------------------------------
69 --ADD_BLOCK used to add a row in timecard block
70 ----------------------------------------------------------------------------
71 PROCEDURE add_block (p_timecard in out NOCOPY HXC_BLOCK_TABLE_TYPE,
72 p_timecard_id in NUMBER,
73 p_ovn in NUMBER,
74 p_parent_id in NUMBER,
75 p_parent_ovn in NUMBER,
76 p_approval_style_id in NUMBER,
77 p_measure in NUMBER,
78 p_scope in VARCHAR2,
79 p_date_to in date default null,
80 p_date_from in date default null,
81 p_start_period in date,
82 p_end_period in date,
83 p_resource_id in number,
84 p_changed in VARCHAR2,
85 p_comment_text in varchar2,
86 p_submit_flg in BOOLEAN,
87 p_application_set_id in hxc_time_building_blocks.application_set_id%type,
88 p_timecard_index_info in out NOCOPY hxc_timekeeper_process.t_timecard_index_info);
89
90 -------------------------------------------------------------------------------
91 -- this procedure add a attribute in the attribute_table
92 -------------------------------------------------------------------------------
93 PROCEDURE add_attribute (p_attribute in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
94 p_attribute_id in NUMBER,
95 p_tbb_id in NUMBER,
96 p_tbb_ovn in NUMBER,
97 p_blk_type in VARCHAR2,
98 p_blk_id in NUMBER,
99 p_att_category in VARCHAR2,
100 p_att_1 in VARCHAR2,
101 p_att_2 in VARCHAR2,
102 p_att_3 in VARCHAR2,
103 p_att_4 in varchar2,
104 p_att_5 in varchar2 default NULL,
105 p_att_6 in VARCHAR2 default NULL,
106 p_att_7 in VARCHAR2 default NULL,
107 p_att_8 in varchar2 default NULL,
108 p_attribute_index_info in out NOCOPY hxc_timekeeper_process.t_attribute_index_info
109 );
110 ----------------------------------------------------------------------------
111 --gets attributes data from hxc_time_attributes table
112 ----------------------------------------------------------------------------
113 PROCEDURE create_attribute_structure
114 (p_timecard_id in number,
115 p_timecard_ovn in number,
116 p_resource_id in number,
117 p_start_period in date,
118 p_end_period in date,
119 p_attributes out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
120 p_add_hours_type_id in number,
121 p_attribute_index_info out NOCOPY hxc_timekeeper_process.t_attribute_index_info
122 );
123 -------------------------------------------------------------------------------
124 --------------DEBUG PROCEDURE--------------------------------------------------
125 -------------------------------------------------------------------------------
126 PROCEDURE dump_timkeeper_data
127 (p_timekeeper_data IN hxc_timekeeper_process.t_timekeeper_table);
128 PROCEDURE dump_buffer_table
129 (p_buffer_table hxc_timekeeper_process.t_buffer_table);
130 PROCEDURE dump_resource_tc_table
131 (l_resource_tc_table hxc_timekeeper_process.t_resource_tc_table);
132 PROCEDURE dump_timecard
133 (p_timecard in HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO);
134 -------------------------------------------------------------------------------
135 --Used to get the attribute category for the detail dff saved in timecard
136 -------------------------------------------------------------------------------
137 PROCEDURE add_dff_attribute (p_attribute in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
138 p_attribute_id in NUMBER,
139 p_tbb_id in NUMBER,
140 p_tbb_ovn in NUMBER,
141 p_blk_type in VARCHAR2,
142 p_blk_id in NUMBER,
143 p_att_category in VARCHAR2,
144 p_att_1 in VARCHAR2,
145 p_att_2 in VARCHAR2,
146 p_att_3 in VARCHAR2,
147 p_att_4 in varchar2,
148 p_att_5 in varchar2,
149 p_att_6 in varchar2,
150 p_att_7 in varchar2,
151 p_att_8 in varchar2,
152 p_att_9 in varchar2,
153 p_att_10 in varchar2,
154 p_att_11 in varchar2,
155 p_att_12 in varchar2,
156 p_att_13 in varchar2,
157 p_att_14 in varchar2,
158 p_att_15 in varchar2,
159 p_att_16 in varchar2,
160 p_att_17 in varchar2,
161 p_att_18 in varchar2,
162 p_att_19 in varchar2,
163 p_att_20 in varchar2,
164 p_att_21 in varchar2,
165 p_att_22 in varchar2,
166 p_att_23 in varchar2,
167 p_att_24 in varchar2,
168 p_att_25 in varchar2,
169 p_att_26 in varchar2,
170 p_att_27 in varchar2,
171 p_att_28 in varchar2,
172 p_att_29 in varchar2,
173 p_att_30 in varchar2 ,
174 p_attribute_index_info in out NOCOPY hxc_timekeeper_process.t_attribute_index_info);
175 PROCEDURE order_building_blocks
176 ( p_timecard in out NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO,
177 p_ord_timecard in out NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO);
178 -------------------------------------------------------------------------------
179 -- This procedure used to get which attribute in layout is used to decide the
180 -- attribute category.
181 -------------------------------------------------------------------------------
182 FUNCTION get_TK_dff_attrname(p_tkid number,
183 p_insert_detail in hxc_timekeeper_process.t_time_info,
184 p_base_dff in varchar2,
185 p_att_tab in hxc_alias_utility.t_alias_att_info)
186 return varchar2;
187 -------------------------------------------------------------------------------
188 -- this procedure used to give all timecards including midperiod timecards
189 -- saved in that range
190 -------------------------------------------------------------------------------
191 Procedure populate_tc_tab( resource_id in number,
192 tc_frdt in date,
193 tc_todt in date,
194 emp_tc_info out nocopy hxc_timekeeper_utilities.emptctab) ;
195 -------------------------------------------------------------------------------
196 -- this procedure used to query mid period timecards
197 -------------------------------------------------------------------------------
198 Procedure populate_query_tc_tab( resource_id in number,
199 tc_frdt in date,
200 tc_todt in date,
201 emp_qry_tc_info out nocopy hxc_timekeeper_utilities.emptctab);
202 -------------------------------------------------------------------------------
203 -- this procedure gives split of timecards
204 -- Used in save procedure to break the timecard
205 --when monthly timecard is
206 -------------------------------------------------------------------------------
207 procedure split_timecard( p_resource_id in number,
208 p_start_date in date,
209 p_end_date in date,
210 p_spemp_tc_info in hxc_timekeeper_utilities.emptctab,
211 p_TC_list out nocopy hxc_timecard_utilities.periods) ;
212 ----------------------------------------------------------------------------
213 -- Called from timekeeper process to get the preference
214 -- associated with a resource
215 -- instead of calling preference evaluation cache the info.
216 ----------------------------------------------------------------------------
217 procedure get_emp_pref(p_resource_id in number,
218 neg_pref out nocopy varchar2,
219 recpref out nocopy number,
220 appstyle out nocopy number,
221 layout1 out nocopy number,
222 layout2 out nocopy number,
223 layout3 out nocopy number,
224 layout4 out nocopy number,
225 layout5 out nocopy number,
226 layout6 out nocopy number,
227 layout7 out nocopy number,
228 layout8 out nocopy number,
229 edits out nocopy varchar2,
230 l_pastdate out nocopy varchar2,
231 l_futuredate out nocopy varchar2,
232 l_emp_start_date out nocopy date,
233 l_emp_terminate_date out nocopy date,
234 l_audit_enabled out nocopy varchar2
235 );
236 ----------------------------------------------------------------------------
237 -- get_resource_time_periods return the list of period for
238 -- a range of time
239 -- The p_check_assignment is not used for the moment.
240 ----------------------------------------------------------------------------
241 PROCEDURE get_resource_time_periods(
242 p_resource_id IN VARCHAR2
243 ,p_resource_type IN VARCHAR2
244 ,p_current_date IN DATE
245 ,p_max_date_in_futur IN DATE
246 ,p_max_date_in_past IN DATE
247 ,p_recurring_period_id IN NUMBER
248 ,p_check_assignment IN BOOLEAN
249 ,p_periodtab IN OUT NOCOPY hxc_timecard_utilities.periods
250 );
251 ----------------------------------------------------------------------------
252 -- add_resource_to_perftab is used to popluate the global pl/sql resource
253 -- preference table
254 ----------------------------------------------------------------------------
255 Procedure add_resource_to_perftab (
256 p_resource_id IN NUMBER,
257 p_pref_code IN VARCHAR2,
258 p_attribute1 IN VARCHAR2,
259 p_attribute2 IN VARCHAR2,
260 p_attribute3 IN VARCHAR2,
261 p_attribute4 IN VARCHAR2,
262 p_attribute5 IN VARCHAR2,
263 p_attribute6 IN VARCHAR2,
264 p_attribute7 IN VARCHAR2,
265 p_attribute8 IN VARCHAR2,
266 p_attribute11 IN VARCHAR2
267 );
268 ----------------------------------------------------------------------------
269 -- This procedure is used to find if timecard update is allowed or not
270 ----------------------------------------------------------------------------
271 PROCEDURE tc_edit_allowed (
272 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
273 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
274 ,p_timecard_status varchar2
275 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
276 ,p_edit_allowed IN OUT NOCOPY VARCHAR2
277 ) ;
278 ----------------------------------------------------------------------------
279 -- This procedure is used to convert message object type to message pl/sql table
280 ----------------------------------------------------------------------------
281 Procedure convert_type_to_message_table
282 (p_old_messages in hxc_message_table_type
283 ,p_messages out nocopy hxc_self_service_time_deposit.message_table);
284 ------------------------------------------------------------------------------------------
285 -- These procedure are moved to make the timekeeper_process package in small program units
286 -----------------------------------------------------------------------------------------
287 Procedure manage_attributes ( p_attribute_number IN NUMBER
288 ,p_insert_data_details IN hxc_timekeeper_process.t_time_info
289 ,p_old_value IN OUT NOCOPY varchar2
290 ,p_new_value IN OUT NOCOPY varchar2
291 );
292 Procedure manage_timeinfo ( p_day_counter IN NUMBER
293 ,p_insert_detail IN hxc_timekeeper_process.t_time_info
294 ,p_measure IN OUT NOCOPY NUMBER
295 ,p_detail_id IN OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
296 ,p_detail_ovn IN OUT NOCOPY NUMBER
297 ,p_detail_time_in IN OUT NOCOPY DATE
298 ,p_detail_time_out IN OUT NOCOPY DATE
299 );
300 Procedure manage_detaildffinfo ( p_detail_id IN hxc_time_building_blocks.time_building_block_id%TYPE
301 ,p_detail_ovn IN NUMBER
302 ,p_det_details IN OUT NOCOPY hxc_timekeeper_process.g_detail_data%TYPE
303 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
304 ,p_attribute_category IN VARCHAR2
305 ,p_tbb_id_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_id_reference
306 ,p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
307 ,p_timecard_index_info IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info
308 );
309 PROCEDURE check_msg_set_process_flag
310 ( p_blocks in out nocopy HXC_BLOCK_TABLE_TYPE
311 , p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
312 , p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
313 );
314 TYPE r_group IS RECORD
315 (
316 group_name VARCHAR2(80),
317 recurring_period_id NUMBER,
318 group_id NUMBER,
319 recurring_period_name VARCHAR2(80),
320 start_date DATE,
321 end_date DATE,
322 period_type VARCHAR2(80),
323 duration_in_days NUMBER,
324 show_group_name VARCHAR2(200)
325 );
326 TYPE t_group_list is TABLE OF r_group
327 INDEX BY BINARY_INTEGER;
328 ------------------------------------------------------------------------------------------
329 -- This procedure is used to cache the employees preference in a timekeeper group
330 -----------------------------------------------------------------------------------------
331 PROCEDURE cache_employee_pref_in_group ( p_group_id in number
332 ,p_timekeeper_id in number
333 );
334 ------------------------------------------------------------------------------------------
335 -- This procedure is used to get the recurring period list in a group
336 -----------------------------------------------------------------------------------------
337 PROCEDURE get_group_period_list ( p_group_id in number,
338 p_business_group_id in NUMBER,
339 p_periodname_list OUT NOCOPY hxc_timekeeper_utilities.t_group_list
340 );
341 ------------------------------------------------------------------------------------------
342 -- This procedure is used to get the sql for the alternate name attached to attributes
343 -----------------------------------------------------------------------------------------
344 PROCEDURE get_type_sql
345 (p_aliasid IN NUMBER,
346 p_person_type IN VARCHAR2 DEFAULT NULL,
347 p_alias_typ OUT NOCOPY VARCHAR2 ,
348 p_alias_sql OUT NOCOPY long ,
349 p_maxsize out NOCOPY number,
350 p_minvalue out NOCOPY number,
351 p_maxvalue out NOCOPY number,
352 p_precision out NOCOPY number,
353 p_colmtype out NOCOPY varchar2);
354 Type att_alias_rec is record
355 (
356 attr_name varchar2(30),
357 alias_id number(15),
358 alias_sql long ,
359 alias_type varchar2(80),
360 alias_maxsize number,
361 alias_minvalue number,
362 alias_maxvalue number,
363 alias_precision number,
364 alias_lovcoltype varchar2(10)
365 );
366 TYPE att_alias_list is TABLE OF att_alias_rec
367 INDEX BY BINARY_INTEGER;
368 Type tk_layout_rec is record
369 (
370 tk_timeflag varchar2(10),
371 tk_empno varchar2(10),
372 tk_empname varchar2(10),
373 tk_base_attr varchar2(30),
374 tk_applset varchar2(30),
375 tk_audit_enabled VARCHAR2(10),
376 tk_data_entry_required VARCHAR2(10),
377 tk_notification_to varchar2(100),
378 tk_notification_type varchar2(100)
379 );
380 Type tk_layout_tab is table of tk_layout_rec index by BINARY_INTEGER;
381 PROCEDURE populate_alias_table( p_timekeeper_id IN NUMBER,
382 p_tk_layout_info OUT NOCOPY hxc_timekeeper_utilities.tk_layout_tab,
383 p_att_alias_table OUT NOCOPY hxc_timekeeper_utilities.att_alias_list
384 );
385 PROCEDURE populate_disable_tc_tab( resource_id IN number,
386 tc_frdt IN date,
387 tc_todt IN date,
388 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
389 ) ;
390 PROCEDURE new_timecard( p_resource_id in number,
391 p_start_date in date,
392 p_end_date in date,
393 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab);
394 Type hxc_tk_detail_temp_tab IS TABLE OF hxc_tk_detail_temp%ROWTYPE INDEX BY BINARY_INTEGER;
395 g_hxc_tk_detail_temp_tab hxc_tk_detail_temp_tab;
396 PROCEDURE populate_detail_temp(p_Action in number);
397 FUNCTION get_exp_type_from_alias (p_alias_value_id in varchar2) return varchar2;
398 FUNCTION check_global_context
399 (p_context_prefix in VARCHAR2) return boolean ;
400
401 /* Added for 8775740 HR OTL ABSENCE INTEGRATION
402
403 */
404
405 -- change start
406
407 FUNCTION get_pref_eval_date
408 (p_resource_id IN NUMBER
409 ,p_tc_start_date IN DATE
410 ,p_tc_end_date IN DATE)
411
412 RETURN DATE ;
413
414 PROCEDURE populate_prepop_detail_id_info
415 (p_timekeeper_data_rec IN hxc_timekeeper_process.t_time_info,
416 p_tk_prepop_detail_id_tab IN OUT NOCOPY hxc_timekeeper_process.g_tk_prepop_detail_id_tab_type
417 );
418
419
420
421 FUNCTION get_abs_co_absence_detail_id
422 (p_absence_duration IN NUMBER DEFAULT NULL,
423 p_absence_start_time IN DATE DEFAULT NULL,
424 p_absence_stop_time IN DATE DEFAULT NULL,
425 p_absence_attendance_id IN NUMBER,
426 p_transaction_id IN NUMBER,
427 p_lock_row_id IN ROWID,
428 p_resource_id IN NUMBER,
429 p_start_period IN DATE,
430 p_end_period IN DATE,
431 p_tc_start IN DATE, -- 8916345 Added for mid period fix
432 p_tc_end IN DATE, -- 8916345 Added for mid period fix
433 p_day_value IN NUMBER
434 )
435 return number ;
436
437
438
439 PROCEDURE build_absence_prepop_table
440 (p_tk_prepop_info IN hxc_timekeeper_utilities.t_tk_prepop_info_type,
441 p_tk_abs_tab OUT NOCOPY hxc_timekeeper_process.t_tk_abs_tab_type,
442 p_start_period IN DATE,
443 p_end_period IN DATE,
444 p_tc_start IN DATE, -- 8916345 Added for mid period fix
445 p_tc_end IN DATE, -- 8916345 Added for mid period fix
446 p_lock_row_id IN ROWID,
447 p_resource_id IN NUMBER,
448 p_timekeeper_id IN NUMBER
449 );
450
451
452
453 PROCEDURE PRE_POPULATE_ABSENCE_DETAILS
454 (p_timekeeper_id IN NUMBER,
455 p_start_period IN DATE,
456 p_end_period IN DATE,
457 p_tc_start IN DATE, -- 8916345 Added for mid period fix
458 p_tc_end IN DATE, -- 8916345
459 p_resource_id IN NUMBER,
460 p_lock_row_id IN ROWID,
461 p_tk_abs_tab OUT NOCOPY hxc_timekeeper_process.t_tk_abs_tab_type
462 );
463
464
465 -- change end
466
467 end hxc_timekeeper_utilities;
[Home] [Help]