1 Package hxc_time_category_utils_pkg as
2 /* $Header: hxchtcutl.pkh 120.2 2006/08/29 20:58:24 arundell noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_time_category_utils_pkg.'; -- Global package name
9
10 g_tc_bb_not_ok_string VARCHAR2(32000);
11
12 CURSOR csr_get_time_sql ( p_time_category_id NUMBER ) IS
13 SELECT htc.time_sql
14 FROM hxc_time_categories htc
15 WHERE htc.time_category_id = p_time_category_id;
16
17 -- *******************************************
18 -- structures for backward compatibilty start
19
20 TYPE r_time_category IS RECORD (
21 bld_blk_info_type hxc_time_attributes.attribute_category%TYPE
22 , segment fnd_descr_flex_column_usages.end_user_column_name%TYPE
23 , value_id hxc_time_attributes.attribute1%TYPE );
24
25 TYPE t_time_category IS TABLE OF r_time_category INDEX BY BINARY_INTEGER;
26
27 g_time_category_tab t_time_category;
28
29 TYPE r_seg_info IS RECORD (
30 application_column_name VARCHAR2(2000)
31 , segment_name VARCHAR2(2000)
32 , column_prompt VARCHAR2(2000)
33 , value_set NUMBER
34 , validation_type VARCHAR2(2000)
35 , sql_text LONG
36 , sql_ok BOOLEAN
37 , no_sql BOOLEAN );
38
39 TYPE t_seg_info IS TABLE OF r_seg_info INDEX BY BINARY_INTEGER;
40
41 TYPE r_context IS RECORD ( context_code fnd_descr_flex_column_usages.descriptive_flex_context_code%TYPE );
42 TYPE t_context IS TABLE OF r_context INDEX BY BINARY_INTEGER;
43
44 -- structures for backward compatibilty end
45 -- *******************************************
46
47 TYPE r_vs_comp IS RECORD (
48 time_category_id number(15)
49 , time_category_comp_id number(15)
50 , component_type_id number(15)
51 , is_null varchar2(1)
52 , equal_to varchar2(1)
53 , flex_value_set_id hxc_time_category_comps.flex_value_set_id%TYPE
54 , sql_string hxc_time_category_comp_sql.sql_string%TYPE
55 , last_update_date DATE );
56
57 TYPE t_vs_comp IS TABLE OF r_vs_comp INDEX BY BINARY_INTEGER;
58
59 TYPE r_an_comp IS RECORD ( sql_string hxc_time_category_comp_sql.sql_string%TYPE );
60 TYPE t_an_comp IS TABLE OF r_an_comp INDEX BY BINARY_INTEGER;
61
62 TYPE r_tc_comp IS RECORD ( ref_tc_id hxc_time_category_comps.time_category_id%TYPE );
63 TYPE t_tc_comp IS TABLE OF r_tc_comp INDEX BY BINARY_INTEGER;
64
65 TYPE r_master_tc_info IS RECORD (
66 time_category_id hxc_time_categories.time_category_id%TYPE
67 , time_card_id hxc_time_building_blocks.time_building_Block_id%TYPE
68 , operator hxc_time_categories.operator%TYPE
69 , attribute_count number );
70
71 TYPE r_tc_bb_ok IS RECORD ( bb_id_ok varchar2(1) );
72
73 TYPE t_tc_bb_ok IS TABLE OF r_tc_bb_ok INDEX BY BINARY_INTEGER;
74
75 g_tc_bb_ok_tab t_tc_bb_ok;
76
77 g_tc_in_bb_ok hxc_time_categories.time_category_id%TYPE;
78
79 g_time_category_id hxc_time_categories.time_category_id%TYPE;
80
81 g_tc_bb_ok_string VARCHAR2(32000);
82
83 g_master_tc_info_rec r_master_tc_info;
84
85
86
87 PROCEDURE mapping_component_string ( p_time_category_id NUMBER
88 , p_time_sql IN OUT NOCOPY LONG );
89
90 PROCEDURE alternate_name_string ( p_alias_value_id NUMBER
91 , p_operator VARCHAR2
92 , p_is_null VARCHAR2
93 , p_equal_to VARCHAR2
94 , p_time_sql IN OUT NOCOPY LONG );
95
96 -- public procedure
97 -- push_timecard
98 --
99 -- description
100 --
101 -- Takes the timecard stored in the block and attributes tables structures and
102 -- inserts it into the corresponding temporary tables
103
104 -- parameters
105 -- p_blocks - the timecard block table
106 -- p_attributes - the timecard attribute table
107 -- p_detail_blocks_only -
108 -- passes detail block only
109 -- this is used for approvals which creates these structures
110 -- from the database. NOTE: must denormalise the DAY start
111 -- and STOP times when passing only DETAILS
112
113 PROCEDURE push_timecard ( p_blocks hxc_block_table_type,
114 p_attributes hxc_attribute_table_type,
115 p_detail_blocks_only BOOLEAN DEFAULT FALSE );
116
117
118 PROCEDURE push_attributes ( p_attributes hxc_self_service_time_deposit.building_block_attribute_info );
119
120
121 -- public procedure
122 -- evaluate_time_category
123 --
124 -- description
125 --
126 -- Evaluates the given time category against the timecard
127 -- stored in the temporary table
128
129 -- Returns a table of DETAIL time building blocks and string
130 -- which satisfied the given time category
131
132 -- parameters
133 -- p_time_category_id - Time Category ID
134 -- p_tc_bb_ok_tab - Table of Valid bb ids
135 -- p_tc_bb_ok_string - string of the valid building blocks
136 -- p_tc_bb_not_ok_string - string of the invalid building blocks
137 -- p_use_tc_cache - uses the Time Category cache
138 -- - default to TRUE.
139 -- p_use_tc_bb_cache - uses the Time Category building block cache
140 -- - Used in ELP where we want to evaluate the
141 -- - timecard for the old structure too. We do
142 -- - not want this evaluation to use the cache
143 -- - or maintain it.
144 -- p_use_temp_table - Whether the time categry is evaluated against
145 -- the temp table (called from Self Service)
146 -- or the live table
147 -- p_scope - scope of the time building block and ovn supplied
148 -- p_tbb_id - buildibg block to be evaluated (non Self Service call)
149 -- p_tbb_ovn - building block ovn to be evaluated (non Self Service call)
150
151 PROCEDURE evaluate_time_category ( p_time_category_id IN NUMBER
152 , p_tc_bb_ok_tab IN OUT NOCOPY t_tc_bb_ok
153 , p_tc_bb_ok_string IN OUT NOCOPY VARCHAR2
154 , p_tc_bb_not_ok_string IN OUT NOCOPY VARCHAR2
155 , p_use_tc_cache IN BOOLEAN DEFAULT TRUE
156 , p_use_tc_bb_cache IN BOOLEAN DEFAULT TRUE
157 , p_use_temp_table IN BOOLEAN DEFAULT TRUE
158 , p_scope IN VARCHAR2 DEFAULT 'TIME'
159 , p_tbb_id IN NUMBER DEFAULT NULL
160 , p_tbb_ovn IN NUMBER DEFAULT NULL );
161
162 -- public procedure
163 -- sum_tc_bb_ok_hrs
164 --
165 -- description
166 --
167 -- Sums the hours on the timecard which satisfy the time category
168
169 -- parameters
170 -- p_tc_bb_ok_string - string of the valid building blocks
171 -- p_hrs - sum of hours on the timecard
172 -- p_period_start - time entry rule period start
173 -- p_period_end - time entry rule period end
174
175 PROCEDURE sum_tc_bb_ok_hrs ( p_tc_bb_ok_string VARCHAR2
176 , p_hrs IN OUT NOCOPY NUMBER
177 , p_period_start DATE
178 , p_period_end DATE );
179
180
181 -- public function
182 -- chk_tc_bb_ok
183
184 -- description
185
186 -- Does a simple EXISTS on the global table g_tc_bb_ok_tab. If the bb id exists
187 -- in the table then return TRUE otherwise return FALSE.
188
189 FUNCTION chk_tc_bb_ok (
190 p_tbb_id NUMBER ) RETURN BOOLEAN;
191
192
193
194 PROCEDURE insert_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type );
195 PROCEDURE update_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type );
196 PROCEDURE delete_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type );
197
198
199
200 -- ----------------------------------------------------------------------------
201 -- |----------------------------< get_value_set_sql >-------------------------|
202 -- ----------------------------------------------------------------------------
203 --
204 -- public function
205 -- get_value_set_sql
206 --
207 -- description
208 -- get the SQL associated with a particular value set
209
210
211 FUNCTION get_value_set_sql
212 (p_flex_value_set_id IN NUMBER,
213 p_session_date IN DATE ) RETURN LONG;
214
215
216
217 -- public procedure
218 -- get_flex_info
219 --
220 -- description
221 -- get flex field context segment info. In particular information
222 -- on the validation and value set associated with each segment
223 -- within the context
224 -- Used in the Time Categories form to dynamically set the LOV associated
225 -- with each mapping component chosen.
226
227 PROCEDURE get_flex_info (
228 p_context_code IN VARCHAR2
229 , p_seg_info OUT NOCOPY t_seg_info
230 , p_session_date IN DATE );
231
232
233
234 -- public function
235 -- get_flex_value
236 --
237 -- description
238 -- retrieves the value based on the id and flex value set id
239 -- used in the hxc_time_category_comps_v view.
240
241 FUNCTION get_flex_value ( p_flex_value_set_id NUMBER
242 , p_id VARCHAR2 ) RETURN VARCHAR2;
243
244
245
246 -- prublic function
247 -- get_time_category_id
248 --
249 -- description
250 -- get time category id based on time category name
251
252 FUNCTION get_time_category_id ( p_time_category_name VARCHAR2 ) RETURN NUMBER;
253
254
255
256 -- PUBLIC function for backward compatibility with Phase I Time Categories
257
258 PROCEDURE initialise_time_category (
259 p_time_category_id NUMBER
260 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info );
261
262
263
264 -- PUBLIC function for backward compatibility with Phase I Time Categories
265
266 PROCEDURE initialise_time_category (
267 p_time_category_id NUMBER
268 , p_tco_att hxc_attribute_table_type );
269
270
271
272 -- public function
273 -- category_timecard_hrs
274 --
275 -- description
276 -- Returns the number of hours for timecard
277 -- for a specified time category name
278
279 FUNCTION category_timecard_hrs (
280 p_tbb_id NUMBER
281 , p_tbb_ovn NUMBER
282 , p_time_category_name VARCHAR2 ) RETURN NUMBER;
283
284 -- public function
285 -- category_timecard_hrs_ind
286 --
287 -- description
288 -- Returns the number of hours for timecard
289 -- for a specified time category name
290 -- Similar to category_timecard_hrs but
291 -- it also processes the hour value according
292 -- to decimal precision and rounding rule
293 -- as set in preference
294
295 FUNCTION category_timecard_hrs_ind (
296 p_tbb_id NUMBER
297 , p_tbb_ovn NUMBER
298 , p_time_category_name VARCHAR2 ) RETURN NUMBER;
299
300 -- public function
301 -- category_detail_hrs (Overloaded)
302 --
303 -- description
304 -- Returns the number of hours for 1 DETAIL time building block
305 -- for a specified time category name
306
307 FUNCTION category_detail_hrs (
308 p_tbb_id NUMBER
309 , p_tbb_ovn NUMBER
310 , p_time_category_name VARCHAR2 ) RETURN NUMBER;
311
312 -- public function
313 -- category_detail_hrs (Overloaded)
314 --
315 -- description
316 -- Returns the number of hours for 1 DETAIL time building block
317 -- (the global variable g time category id is presumed to be set)
318
319 FUNCTION category_detail_hrs (
320 p_tbb_id NUMBER
321 , p_tbb_ovn NUMBER ) RETURN NUMBER;
322
323
324 -- public function
325 -- category_detail_hrs
326 --
327 -- description
328 -- Returns the number of hours for 1 DETAIL time building block
329 -- for a specified time category id
330
331 FUNCTION category_detail_hrs (
332 p_tbb_id NUMBER
333 , p_tbb_ovn NUMBER
334 , p_time_category_id NUMBER ) RETURN NUMBER;
335
336
337
338 -- public function
339 -- category_app_period_tc_hrs
340 --
341 -- description
342 -- Returns the number of hours for person within a date range
343 -- and specified time category and application_period_id
344
345 FUNCTION category_app_period_tc_hrs (
346 p_period_start_time IN DATE
347 , p_period_stop_time IN DATE
348 , p_resource_id IN NUMBER
349 , p_time_category_name IN VARCHAR2
350 , p_application_period_id IN NUMBER ) RETURN NUMBER;
351
352
353
354 -- public procedure
355 -- process_tc_timecard
356 --
357 -- description
358 --
359 -- This procedure is obsolete. Users should use evaluate_time_category
360 --
361
362 PROCEDURE process_tc_timecard (
363 p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
364 , p_time_cat t_time_category
365 , p_bb_ok_tab IN OUT NOCOPY t_tc_bb_ok
366 , p_operator VARCHAR2 default 'OR' );
367
368
369 -- public procedure
370 -- time_category_String
371 --
372 -- description
373 --
374 -- This procedure is obsolete. Users should use evaluate_time_category
375 --
376
377 PROCEDURE time_category_string ( p_time_category_id NUMBER
378 , p_dyn_or_tab IN VARCHAR2
379 , p_dyn_sql IN OUT NOCOPY LONG
380 , p_category_tab IN OUT NOCOPY t_time_category
381 , p_operator IN OUT NOCOPY VARCHAR2 );
382
383
384 PROCEDURE alias_value_ref_int_chk ( p_alias_value_id NUMBER
385 , p_action VARCHAR2 );
386
387 PROCEDURE alias_definition_ref_int_chk ( p_alias_definition_id NUMBER );
388
389 PROCEDURE alias_type_comp_ref_int_chk ( p_alias_type_id NUMBER );
390 --
391 -- ---------------------------------------------------------------------------
392 -- |------------------------< reset_cache >----------------------------------|
393 -- ---------------------------------------------------------------------------
394 -- {Start Of Comments}
395 --
396 -- Description:
397 -- This procedure resets the internal time category cache, and corresponding
398 -- categorized time cache. This ensures that if any changes have been made
399 -- to the time category definition at runtime, the new version of the time
400 -- category is used and not the existing categorization. This function is
401 -- currently called from the Project Manager approval package when time
402 -- categories are created and modified dynamically. Added for bug 5469357
403 --
404 -- Prerequisites:
405 -- None.
406 --
407 -- In Parameters:
408 -- Name Reqd Type Description
409 --
410 -- Out Parameters:
411 -- Name Reqd Type Description
412 --
413 -- Post Success:
414 -- Internal caches are cleared.
415 --
416 -- Post Failure:
417 -- Failure should not occur, but if so, function returns false indicating
418 -- a failure to clear the internal time category caches.
419 --
420 -- Access Status:
421 -- Public - HRMS Development Only.
422 --
423 -- {End Of Comments}
424 --
425 Function reset_cache Return Boolean;
426
427 end hxc_time_category_utils_pkg;
[Home] [Help]