1 PACKAGE hxc_timekeeper_utilities AS
2 /* $Header: hxctkutil.pkh 120.4 2006/07/13 03:48:24 sechandr noship $ */
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 --------- GLOBAL DECLARATION
31 ---------------------------------------------------------------------------
32 g_resource_perftab tk_resource_pref_tab; --index by resource_id
33 g_start_stop_pref_cache tk_resource_pref_tab; --index by start_index of bulk pref table
34 ----------------------------------------------------------------------------
35 --ADD_BLOCK used to add a row in timecard block
36 ----------------------------------------------------------------------------
37 PROCEDURE add_block (p_timecard in out NOCOPY HXC_BLOCK_TABLE_TYPE,
38 p_timecard_id in NUMBER,
39 p_ovn in NUMBER,
40 p_parent_id in NUMBER,
41 p_parent_ovn in NUMBER,
42 p_approval_style_id in NUMBER,
43 p_measure in NUMBER,
44 p_scope in VARCHAR2,
45 p_date_to in date default null,
46 p_date_from in date default null,
47 p_start_period in date,
48 p_end_period in date,
49 p_resource_id in number,
50 p_changed in VARCHAR2,
51 p_comment_text in varchar2,
52 p_submit_flg in BOOLEAN,
53 p_application_set_id in hxc_time_building_blocks.application_set_id%type,
54 p_timecard_index_info in out NOCOPY hxc_timekeeper_process.t_timecard_index_info);
55
56 -------------------------------------------------------------------------------
57 -- this procedure add a attribute in the attribute_table
58 -------------------------------------------------------------------------------
59 PROCEDURE add_attribute (p_attribute in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
60 p_attribute_id in NUMBER,
61 p_tbb_id in NUMBER,
62 p_tbb_ovn in NUMBER,
63 p_blk_type in VARCHAR2,
64 p_blk_id in NUMBER,
65 p_att_category in VARCHAR2,
66 p_att_1 in VARCHAR2,
67 p_att_2 in VARCHAR2,
68 p_att_3 in VARCHAR2,
69 p_att_4 in varchar2,
70 p_att_5 in varchar2 default NULL,
71 p_att_6 in VARCHAR2 default NULL,
72 p_att_7 in VARCHAR2 default NULL,
73 p_att_8 in varchar2 default NULL,
74 p_attribute_index_info in out NOCOPY hxc_timekeeper_process.t_attribute_index_info
75 );
76 ----------------------------------------------------------------------------
77 --gets attributes data from hxc_time_attributes table
78 ----------------------------------------------------------------------------
79 PROCEDURE create_attribute_structure
80 (p_timecard_id in number,
81 p_timecard_ovn in number,
82 p_resource_id in number,
83 p_start_period in date,
84 p_end_period in date,
85 p_attributes out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
86 p_add_hours_type_id in number,
87 p_attribute_index_info out NOCOPY hxc_timekeeper_process.t_attribute_index_info
88 );
89 -------------------------------------------------------------------------------
90 --------------DEBUG PROCEDURE--------------------------------------------------
91 -------------------------------------------------------------------------------
92 PROCEDURE dump_timkeeper_data
93 (p_timekeeper_data IN hxc_timekeeper_process.t_timekeeper_table);
94 PROCEDURE dump_buffer_table
95 (p_buffer_table hxc_timekeeper_process.t_buffer_table);
96 PROCEDURE dump_resource_tc_table
97 (l_resource_tc_table hxc_timekeeper_process.t_resource_tc_table);
98 PROCEDURE dump_timecard
99 (p_timecard in HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO);
100 -------------------------------------------------------------------------------
101 --Used to get the attribute category for the detail dff saved in timecard
102 -------------------------------------------------------------------------------
103 PROCEDURE add_dff_attribute (p_attribute in out NOCOPY HXC_ATTRIBUTE_TABLE_TYPE,
104 p_attribute_id in NUMBER,
105 p_tbb_id in NUMBER,
106 p_tbb_ovn in NUMBER,
107 p_blk_type in VARCHAR2,
108 p_blk_id in NUMBER,
109 p_att_category in VARCHAR2,
110 p_att_1 in VARCHAR2,
111 p_att_2 in VARCHAR2,
112 p_att_3 in VARCHAR2,
113 p_att_4 in varchar2,
114 p_att_5 in varchar2,
115 p_att_6 in varchar2,
116 p_att_7 in varchar2,
117 p_att_8 in varchar2,
118 p_att_9 in varchar2,
119 p_att_10 in varchar2,
120 p_att_11 in varchar2,
121 p_att_12 in varchar2,
122 p_att_13 in varchar2,
123 p_att_14 in varchar2,
124 p_att_15 in varchar2,
125 p_att_16 in varchar2,
126 p_att_17 in varchar2,
127 p_att_18 in varchar2,
128 p_att_19 in varchar2,
129 p_att_20 in varchar2,
130 p_att_21 in varchar2,
131 p_att_22 in varchar2,
132 p_att_23 in varchar2,
133 p_att_24 in varchar2,
134 p_att_25 in varchar2,
135 p_att_26 in varchar2,
136 p_att_27 in varchar2,
137 p_att_28 in varchar2,
138 p_att_29 in varchar2,
139 p_att_30 in varchar2 ,
140 p_attribute_index_info in out NOCOPY hxc_timekeeper_process.t_attribute_index_info);
141 PROCEDURE order_building_blocks
142 ( p_timecard in out NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO,
143 p_ord_timecard in out NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.TIMECARD_INFO);
144 -------------------------------------------------------------------------------
145 -- This procedure used to get which attribute in layout is used to decide the
146 -- attribute category.
147 -------------------------------------------------------------------------------
148 FUNCTION get_TK_dff_attrname(p_tkid number,
149 p_insert_detail in hxc_timekeeper_process.t_time_info,
150 p_base_dff in varchar2,
151 p_att_tab in hxc_alias_utility.t_alias_att_info)
152 return varchar2;
153 -------------------------------------------------------------------------------
154 -- this procedure used to give all timecards including midperiod timecards
155 -- saved in that range
156 -------------------------------------------------------------------------------
157 Procedure populate_tc_tab( resource_id in number,
158 tc_frdt in date,
159 tc_todt in date,
160 emp_tc_info out nocopy hxc_timekeeper_utilities.emptctab) ;
161 -------------------------------------------------------------------------------
162 -- this procedure used to query mid period timecards
163 -------------------------------------------------------------------------------
164 Procedure populate_query_tc_tab( resource_id in number,
165 tc_frdt in date,
166 tc_todt in date,
167 emp_qry_tc_info out nocopy hxc_timekeeper_utilities.emptctab);
168 -------------------------------------------------------------------------------
169 -- this procedure gives split of timecards
170 -- Used in save procedure to break the timecard
171 --when monthly timecard is
172 -------------------------------------------------------------------------------
173 procedure split_timecard( p_resource_id in number,
174 p_start_date in date,
175 p_end_date in date,
176 p_spemp_tc_info in hxc_timekeeper_utilities.emptctab,
177 p_TC_list out nocopy hxc_timecard_utilities.periods) ;
178 ----------------------------------------------------------------------------
179 -- Called from timekeeper process to get the preference
180 -- associated with a resource
181 -- instead of calling preference evaluation cache the info.
182 ----------------------------------------------------------------------------
183 procedure get_emp_pref(p_resource_id in number,
184 neg_pref out nocopy varchar2,
185 recpref out nocopy number,
186 appstyle out nocopy number,
187 layout1 out nocopy number,
188 layout2 out nocopy number,
189 layout3 out nocopy number,
190 layout4 out nocopy number,
191 layout5 out nocopy number,
192 layout6 out nocopy number,
193 layout7 out nocopy number,
194 layout8 out nocopy number,
195 edits out nocopy varchar2,
196 l_pastdate out nocopy varchar2,
197 l_futuredate out nocopy varchar2,
198 l_emp_start_date out nocopy date,
199 l_emp_terminate_date out nocopy date,
200 l_audit_enabled out nocopy varchar2
201 );
202 ----------------------------------------------------------------------------
203 -- get_resource_time_periods return the list of period for
204 -- a range of time
205 -- The p_check_assignment is not used for the moment.
206 ----------------------------------------------------------------------------
207 PROCEDURE get_resource_time_periods(
208 p_resource_id IN VARCHAR2
209 ,p_resource_type IN VARCHAR2
210 ,p_current_date IN DATE
211 ,p_max_date_in_futur IN DATE
212 ,p_max_date_in_past IN DATE
213 ,p_recurring_period_id IN NUMBER
214 ,p_check_assignment IN BOOLEAN
215 ,p_periodtab IN OUT NOCOPY hxc_timecard_utilities.periods
216 );
217 ----------------------------------------------------------------------------
218 -- add_resource_to_perftab is used to popluate the global pl/sql resource
219 -- preference table
220 ----------------------------------------------------------------------------
221 Procedure add_resource_to_perftab (
222 p_resource_id IN NUMBER,
223 p_pref_code IN VARCHAR2,
224 p_attribute1 IN VARCHAR2,
225 p_attribute2 IN VARCHAR2,
226 p_attribute3 IN VARCHAR2,
227 p_attribute4 IN VARCHAR2,
228 p_attribute5 IN VARCHAR2,
229 p_attribute6 IN VARCHAR2,
230 p_attribute7 IN VARCHAR2,
231 p_attribute8 IN VARCHAR2,
232 p_attribute11 IN VARCHAR2
233 );
234 ----------------------------------------------------------------------------
235 -- This procedure is used to find if timecard update is allowed or not
236 ----------------------------------------------------------------------------
237 PROCEDURE tc_edit_allowed (
238 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
239 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
240 ,p_timecard_status varchar2
241 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
242 ,p_edit_allowed IN OUT NOCOPY VARCHAR2
243 ) ;
244 ----------------------------------------------------------------------------
245 -- This procedure is used to convert message object type to message pl/sql table
246 ----------------------------------------------------------------------------
247 Procedure convert_type_to_message_table
248 (p_old_messages in hxc_message_table_type
249 ,p_messages out nocopy hxc_self_service_time_deposit.message_table);
250 ------------------------------------------------------------------------------------------
251 -- These procedure are moved to make the timekeeper_process package in small program units
252 -----------------------------------------------------------------------------------------
253 Procedure manage_attributes ( p_attribute_number IN NUMBER
254 ,p_insert_data_details IN hxc_timekeeper_process.t_time_info
255 ,p_old_value IN OUT NOCOPY varchar2
256 ,p_new_value IN OUT NOCOPY varchar2
257 );
258 Procedure manage_timeinfo ( p_day_counter IN NUMBER
259 ,p_insert_detail IN hxc_timekeeper_process.t_time_info
260 ,p_measure IN OUT NOCOPY NUMBER
261 ,p_detail_id IN OUT NOCOPY hxc_time_building_blocks.time_building_block_id%TYPE
262 ,p_detail_ovn IN OUT NOCOPY NUMBER
263 ,p_detail_time_in IN OUT NOCOPY DATE
264 ,p_detail_time_out IN OUT NOCOPY DATE
265 );
266 Procedure manage_detaildffinfo ( p_detail_id IN hxc_time_building_blocks.time_building_block_id%TYPE
267 ,p_detail_ovn IN NUMBER
268 ,p_det_details IN OUT NOCOPY hxc_timekeeper_process.g_detail_data%TYPE
269 ,p_attributes IN OUT NOCOPY HXC_ATTRIBUTE_TABLE_TYPE
270 ,p_attribute_category IN VARCHAR2
271 ,p_tbb_id_reference_table IN OUT NOCOPY hxc_alias_utility.t_tbb_id_reference
272 ,p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info
273 ,p_timecard_index_info IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info
274 );
275 PROCEDURE check_msg_set_process_flag
276 ( p_blocks in out nocopy HXC_BLOCK_TABLE_TYPE
277 , p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
278 , p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
279 );
280 TYPE r_group IS RECORD
281 (
282 group_name VARCHAR2(80),
283 recurring_period_id NUMBER,
284 group_id NUMBER,
285 recurring_period_name VARCHAR2(80),
286 start_date DATE,
287 end_date DATE,
288 period_type VARCHAR2(80),
289 duration_in_days NUMBER,
290 show_group_name VARCHAR2(200)
291 );
292 TYPE t_group_list is TABLE OF r_group
293 INDEX BY BINARY_INTEGER;
294 ------------------------------------------------------------------------------------------
295 -- This procedure is used to cache the employees preference in a timekeeper group
296 -----------------------------------------------------------------------------------------
297 PROCEDURE cache_employee_pref_in_group ( p_group_id in number
298 ,p_timekeeper_id in number
299 );
300 ------------------------------------------------------------------------------------------
301 -- This procedure is used to get the recurring period list in a group
302 -----------------------------------------------------------------------------------------
303 PROCEDURE get_group_period_list ( p_group_id in number,
304 p_business_group_id in NUMBER,
305 p_periodname_list OUT NOCOPY hxc_timekeeper_utilities.t_group_list
306 );
307 ------------------------------------------------------------------------------------------
308 -- This procedure is used to get the sql for the alternate name attached to attributes
309 -----------------------------------------------------------------------------------------
310 PROCEDURE get_type_sql
311 (p_aliasid IN NUMBER,
312 p_person_type IN VARCHAR2 DEFAULT NULL,
313 p_alias_typ OUT NOCOPY VARCHAR2 ,
314 p_alias_sql OUT NOCOPY long ,
315 p_maxsize out NOCOPY number,
316 p_minvalue out NOCOPY number,
317 p_maxvalue out NOCOPY number,
318 p_precision out NOCOPY number,
319 p_colmtype out NOCOPY varchar2);
320 Type att_alias_rec is record
321 (
322 attr_name varchar2(30),
323 alias_id number(15),
324 alias_sql long ,
325 alias_type varchar2(80),
326 alias_maxsize number,
327 alias_minvalue number,
328 alias_maxvalue number,
329 alias_precision number,
330 alias_lovcoltype varchar2(10)
331 );
332 TYPE att_alias_list is TABLE OF att_alias_rec
333 INDEX BY BINARY_INTEGER;
334 Type tk_layout_rec is record
335 (
336 tk_timeflag varchar2(10),
337 tk_empno varchar2(10),
338 tk_empname varchar2(10),
339 tk_base_attr varchar2(30),
340 tk_applset varchar2(30),
341 tk_audit_enabled VARCHAR2(10),
342 tk_data_entry_required VARCHAR2(10),
343 tk_notification_to varchar2(100),
344 tk_notification_type varchar2(100)
345 );
346 Type tk_layout_tab is table of tk_layout_rec index by BINARY_INTEGER;
347 PROCEDURE populate_alias_table( p_timekeeper_id IN NUMBER,
348 p_tk_layout_info OUT NOCOPY hxc_timekeeper_utilities.tk_layout_tab,
349 p_att_alias_table OUT NOCOPY hxc_timekeeper_utilities.att_alias_list
350 );
351 PROCEDURE populate_disable_tc_tab( resource_id IN number,
352 tc_frdt IN date,
353 tc_todt IN date,
354 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab
355 ) ;
356 PROCEDURE new_timecard( p_resource_id in number,
357 p_start_date in date,
358 p_end_date in date,
359 p_emptcinfo OUT NOCOPY hxc_timekeeper_utilities.emptctab);
360 Type hxc_tk_detail_temp_tab IS TABLE OF hxc_tk_detail_temp%ROWTYPE INDEX BY BINARY_INTEGER;
361 g_hxc_tk_detail_temp_tab hxc_tk_detail_temp_tab;
362 PROCEDURE populate_detail_temp(p_Action in number);
363 FUNCTION get_exp_type_from_alias (p_alias_value_id in varchar2) return varchar2;
364 FUNCTION check_global_context
365 (p_context_prefix in VARCHAR2) return boolean ;
366 end hxc_timekeeper_utilities;
[Home] [Help]