1 Package hxc_time_entry_rules_utils_pkg AUTHID CURRENT_USER as
2 /* $Header: hxcterutl.pkh 120.5 2006/12/08 09:16:38 sgadipal noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hxc_time_entry_rules_utils_pkg.';
7 --
8
9 CURSOR csr_get_rules ( p_terg_id VARCHAR2
10 , p_start_date DATE
11 , p_end_date DATE ) IS
12 SELECT dar.name
13 , NVL( dar.description, dar.name ) ter_message_name
14 , dar.rule_usage
15 , dar.formula_id
16 , dar.mapping_id
17 , dar.attribute1
18 , dar.attribute2
19 , dar.attribute3
20 , dar.attribute4
21 , dar.attribute5
22 , dar.attribute6
23 , dar.attribute7
24 , dar.attribute8
25 , dar.attribute9
26 , dar.attribute10
27 , dar.attribute11
28 , dar.attribute12
29 , dar.attribute13
30 , dar.attribute14
31 , dar.attribute15
32 , ff.formula_name
33 , terc.attribute1 rule_outcome
34 FROM ff_formulas_f ff
35 , hxc_time_entry_rules dar
36 , hxc_time_entry_rule_comps_v terc
37 WHERE
38 terc.time_entry_rule_group_id = TO_NUMBER(p_terg_id)
39 AND
40 dar.time_entry_rule_id = terc.time_entry_rule_id AND
41 ( p_start_date BETWEEN
42 dar.start_date AND dar.end_date
43 OR
44 p_end_date BETWEEN
45 dar.start_date AND dar.end_date )
46 AND
47 ff.formula_id(+) = dar.formula_id AND
48 dar.start_date BETWEEN
49 ff.effective_start_date(+) AND ff.effective_end_date(+)
50 ORDER BY
51 dar.start_date;
52
53 CURSOR csr_get_period_info ( p_recurring_period_id NUMBER ) IS
54 SELECT
55 period_type
56 , duration_in_days
57 , start_date
58 FROM
59 hxc_recurring_periods
60 WHERE
61 recurring_period_id = p_recurring_period_id;
62
63 -- record and table for period information
64
65 TYPE r_period IS RECORD ( period_start DATE
66 , period_end DATE
67 , db_pre_period_start DATE
68 , db_pre_period_end DATE
69 , db_post_period_start DATE
70 , db_post_period_end DATE
71 , db_ref_period_start DATE
72 , db_ref_period_end DATE );
73
74 TYPE t_period IS TABLE OF r_period INDEX BY BINARY_INTEGER;
75
76 -- record for timecard information
77
78 TYPE r_timecard_info IS RECORD (
79 start_date hxc_time_building_blocks.start_time%TYPE
80 , end_date hxc_time_building_blocks.stop_time%TYPE
81 , resource_id hxc_time_building_blocks.resource_id%TYPE
82 , timecard_bb_id hxc_time_building_blocks.time_building_block_id%TYPE
83 , timecard_ovn hxc_time_building_blocks.object_version_number%TYPE
84 , approval_status hxc_time_building_blocks.approval_status%TYPE
85 , bg_id per_business_groups.business_group_id%TYPE
86 , new varchar2(1)
87 , deleted varchar2(1) );
88
89 -- record for TER information
90
91 TYPE r_ter_record IS RECORD ( ter_name hxc_time_entry_rules.name%TYPE,
92 ter_message_name hxc_time_entry_rules.description%TYPE,
93 ter_usage hxc_time_entry_rules.rule_usage%TYPE,
94 ter_formula_name ff_formulas_f.formula_name%TYPE,
95 ter_inc_pto_plan_id pay_accrual_plans.accrual_plan_id%TYPE );
96 g_ter_record r_ter_record;
97
98 TYPE r_assignment_info IS RECORD (
99 assignment_id per_all_assignments_f.assignment_id%TYPE
100 , submission_date DATE
101 , start_date DATE
102 , end_date DATE );
103
104 TYPE t_assignment_info IS TABLE OF r_assignment_info INDEX BY BINARY_INTEGER;
105
106 g_assignment_info t_assignment_info;
107
108 -- public procedure
109 -- get_timecard_info
110 --
111 -- description
112 -- gets the timecard info for a given TCO passed from the SS timecard
113 -- (see r_timecard_info TYPE defined above for what timecard info is retrieved)
114
115 PROCEDURE get_timecard_info (
116 p_time_building_blocks hxc_self_service_time_deposit.timecard_info
117 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
118 , p_timecard_rec IN OUT NOCOPY r_timecard_info );
119
120 -- public procedure
121 -- get_timecard_info
122 --
123 -- description
124 -- overloaded version of above procedure which does not use
125 -- the time attributes table - in this case the bg_id will not be populated.
126
127 PROCEDURE get_timecard_info (
128 p_time_building_blocks hxc_self_service_time_deposit.timecard_info
129 , p_timecard_rec IN OUT NOCOPY r_timecard_info );
130
131
132 -- public procedure
133 -- get_timecard_info
134 --
135 -- description
136 -- overloaded version of above procedure using new HXC_BLOCK_TABLE_TYPE
137
138 PROCEDURE get_timecard_info (
139 p_time_building_blocks HXC_BLOCK_TABLE_TYPE
140 , p_timecard_rec IN OUT NOCOPY r_timecard_info );
141
142
143
144 -- public procedure
145 -- calc_timecard_periods
146 --
147 -- description
148 -- populates a table of periods based on a given period start and start time
149 -- duration in days and the timecard period start and stop time and calculates
150 -- which the windows which actually fall within the timecard start and stop time
151 -- This is used to determine which hours to sum from the TCO and which to sum
152 -- from the database
153
154 PROCEDURE calc_timecard_periods (
155 p_timecard_period_start DATE
156 , p_timecard_period_end DATE
157 , p_period_start_date DATE
158 , p_period_end_date DATE
159 , p_duration_in_days NUMBER
160 , p_periods_tab IN OUT NOCOPY t_period );
161
162
163 -- public procedure
164 -- calc_reference_periods
165 --
166 -- description
167 -- populates the same table of periods based on a given period start and start time
168 -- duration in days and the timecard period start and stop time and calculates
169 -- the windows which actually fall within the timecard start and stop time
170 -- for the reference periods
171 -- This is used to determine which hours to sum from the TCO and which to sum
172 -- from the database
173
174 PROCEDURE calc_reference_periods (
175 p_timecard_period_start DATE
176 , p_timecard_period_end DATE
177 , p_ref_period_start DATE
178 , p_ref_period_end DATE
179 , p_period_start_date DATE
180 , p_period_end_date DATE
181 , p_duration_in_days NUMBER
182 , p_periods_tab IN OUT NOCOPY t_period );
183
184
185 PROCEDURE add_error_to_table (
186 p_message_table in out nocopy HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE
187 , p_message_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
188 , p_message_token in VARCHAR2
189 , p_message_level in VARCHAR2
190 , p_message_field in VARCHAR2
191 , p_application_short_name IN VARCHAR2 default 'HXC'
192 , p_timecard_bb_id in NUMBER
193 , p_time_attribute_id in NUMBER
194 , p_timecard_bb_ovn in NUMBER default null
195 , p_time_attribute_ovn in NUMBER default null
196 , p_message_extent in VARCHAR2 default null); --Bug#2873563
197
198
199 -- ----------------------------------------------------------------------------
200 -- |------------------------< execute_time_entry_rules >----------------------|
201 -- ----------------------------------------------------------------------------
202 --
203 -- Description:
204 --
205 -- This procedure is used to intialise and call the Time Entry fast formula
206 -- defined for a user. It is intended to be called from the timecard submission
207 --
208 -- Prerequisites:
209 --
210 -- None
211 --
212 -- In Parameters:
213 -- Name Reqd Type Description
214 --
215 --
216 -- Post Success:
217 --
218 -- function returns TRUE if period maximu not violated
219 --
220 -- Post Failure:
221 --
222 -- function returns FALSE if the period maximum violated
223 --
224 -- Access Status:
225 -- Public.
226 --
227
228 PROCEDURE execute_time_entry_rules (
229 p_operation VARCHAR2
230 , p_time_building_blocks hxc_self_service_time_deposit.timecard_info
231 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
232 , p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
233 , p_resubmit VARCHAR2
234 , p_blocks hxc_block_table_type
235 , p_attributes hxc_attribute_table_type );
236
237 --
238 --
239 -- ----------------------------------------------------------------------------
240 -- |-----------------------------< period_maximum >---------------------------|
241 -- ----------------------------------------------------------------------------
242 --
243 -- Description:
244 --
245 -- This function returns 1 or -1 depending on whether or not the person
246 -- has exceeded the period maximum over a spcified period.
247 --
248 --
249 -- Prerequisites:
250 --
251 -- None
252 --
253 -- In Parameters:
254 -- Name Reqd Type Description
255 --
256 -- p_resource_id Yes number resource id of the person
257 -- p_submission_date Yes varchar2 the date of the time being submitted
258 -- p_period_maximum Yes number period maximum
259 -- p_period Yes number recurring_period_id of the period we are interested in
260 -- p_reference_period Yes number over how many periods
261 -- p_pre_period_start Yes varchar2 time card hrs on the db before timecard period start
262 -- p_pre_period_end Yes varchar2 time card hrs on the db before timecard period end
263 -- p_post_period_start No varchar2 time card hrs on the db after timecard period start
264 -- p_post_period_end No varchar2 time card hrs on the db after timecard period end
265 -- p_ref_period_start No varchar2 time card hrs on the db for the reference period
266 -- p_ref_period_end No varchar2 time card hrs on the db for the reference period
267 -- p_duration_in_days No number the duration in days of the TER period
268 -- p_timecard_hrs No number the number of hours on the self service timecard
269 --
270 -- Post Success:
271 --
272 -- function returns 1 if period maximum not violated
273 --
274 -- Post Failure:
275 --
276 -- function returns -1 if the period maximum violated
277 --
278 -- Access Status:
279 -- Public.
280 --
281 FUNCTION period_maximum (
282 p_resource_id NUMBER
283 , p_submission_date VARCHAR2
284 , p_period_maximum NUMBER
285 , p_period NUMBER default 1
286 , p_reference_period NUMBER default 1
287 , p_pre_period_start VARCHAR2
288 , p_pre_period_end VARCHAR2
289 , p_post_period_start VARCHAR2 default null
290 , p_post_period_end VARCHAR2 default null
291 , p_ref_period_start VARCHAR2 default null
292 , p_ref_period_end VARCHAR2 default null
293 , p_duration_in_days NUMBER default 1
294 , p_timecard_hrs NUMBER default 0 ) RETURN NUMBER;
295 --
296
297 FUNCTION period_maximum (
298 p_resource_id NUMBER
299 , p_submission_date VARCHAR2
300 , p_period_maximum NUMBER
301 , p_period NUMBER default 1
302 , p_reference_period NUMBER default 1
303 , p_pre_period_start VARCHAR2
304 , p_pre_period_end VARCHAR2
305 , p_post_period_start VARCHAR2 default null
306 , p_post_period_end VARCHAR2 default null
307 , p_ref_period_start VARCHAR2 default null
308 , p_ref_period_end VARCHAR2 default null
309 , p_duration_in_days NUMBER default 1
310 , p_timecard_hrs NUMBER default 0
311 , p_operator VARCHAR2 ) RETURN NUMBER;
312
313
314 FUNCTION asg_status_id ( p_assignment_id NUMBER
315 , p_effective_date VARCHAR2 ) RETURN NUMBER;
316
317 PROCEDURE tc_edit_allowed (
318 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
319 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
320 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
321 ,p_edit_allowed IN OUT nocopy VARCHAR2
322 );
323
324 --
325 -- ----------------------------------------------------------------------------
326 -- |----------------------< tc_edit_allowed >-------------------------|
327 -- ----------------------------------------------------------------------------
328 -- {Start Of Comments}
329 --
330 -- Description:
331 -- This procedure returns true in the edit allowed return variable
332 -- if the user is allowed to edit this timecard based on their
333 -- status allowing edits preference. This version of the function
334 -- is introduced to support this check from the self service timecard
335 -- and API, where the set up validation package is issued from the
336 -- timecard properties package, where the status has already been
337 -- obtained. If the timecard id and ovn are known, but the status
338 -- is not known, you can either call this function passing NULL
339 -- for the status, or call the overloaded function. The status must
340 -- correspond to the actual timecard status, as derived from
341 -- the application period building blocks, i.e. the value from
342 -- HXC_TIMECARD_SUMMARY, and not the value from the building block
343 -- table.
344 --
345 -- Prerequisites:
346 -- This function requires a valid timecard id and object version number
347 -- at minimum, and a valid value for the timecard edit allowed preference
348 -- e.g. NEW_WORKING_REJECTED, SUBMITTED, APPROVALS_INITIATED or RETRO.
349 --
350 -- In Parameters:
351 -- Name Reqd Type Description
352 -- p_row_data N varchar2 Row data object, as built
353 -- when retrieving the
354 -- timecard.
355 -- p_timecard_id Y number Timecard Id
356 -- p_timecard_ovn Y number Timecard Ovn
357 -- p_timecard_status N varchar2 Timecard approval status
358 -- p_edit_allowed_preference Y varchar2 Status allowing edits pref
359 -- p_edit_allowed IN OUT nocopy Y boolean Return variable.
360 --
361 -- Post Success:
362 -- True if the user is allowed to edit the timecard, false otherwise.
363 --
364 -- Post Failure:
365 -- If the timecard is not found, i.e. the timecard id and ovn are invalid,
366 -- then an error message is raised.
367 --
368 -- Access Status:
369 -- Internal Development Use Only.
370 --
371 -- {End Of Comments}
372 --
373 PROCEDURE tc_edit_allowed (
374 p_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
375 ,p_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
376 ,p_timecard_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE
377 ,p_edit_allowed_preference HXC_PREF_HIERARCHIES.ATTRIBUTE1%TYPE
378 ,p_edit_allowed IN OUT nocopy VARCHAR2
379 );
380
381 -- public function
382 -- calc_timecard_hrs (Overloaded)
383 --
384 -- description
385 -- New time category phase II function
386
387 FUNCTION calc_timecard_hrs (
388 p_hrs_period_start DATE
389 , p_hrs_period_end DATE
390 , p_tco_bb HXC_BLOCK_TABLE_TYPE
391 , p_tco_att HXC_ATTRIBUTE_TABLE_TYPE
392 , p_time_category_id NUMBER )
393 RETURN NUMBER;
394
395
396
397 FUNCTION calc_timecard_hrs (
398 p_hrs_period_start DATE
399 , p_hrs_period_end DATE
400 , p_tco_bb hxc_self_service_time_deposit.timecard_info
401 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info )
402 RETURN NUMBER;
403
404 FUNCTION calc_timecard_hrs (
405 p_hrs_period_start DATE
406 , p_hrs_period_end DATE
407 , p_tco_bb hxc_self_service_time_deposit.timecard_info
408 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
409 , p_time_category_name VARCHAR2 )
410 RETURN NUMBER;
411
412 FUNCTION calc_timecard_hrs (
413 p_hrs_period_start DATE
414 , p_hrs_period_end DATE
415 , p_tco_bb hxc_self_service_time_deposit.timecard_info
416 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
417 , p_time_category_id NUMBER )
418 RETURN NUMBER;
419
420 FUNCTION chk_pto_plan ( p_assignment_id NUMBER
421 , p_accrual_plan_id NUMBER
422 , p_effective_date VARCHAR2 )
423 RETURN NUMBER;
424
425 PROCEDURE EXECUTE_ELP_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS HXC_BLOCK_TABLE_TYPE
426 ,P_TIME_ATTRIBUTES HXC_ATTRIBUTE_TABLE_TYPE
427 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
428 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER);
429
430 PROCEDURE EXECUTE_CLA_TIME_ENTRY_RULES( P_TIME_BUILDING_BLOCKS hxc_self_service_time_deposit.timecard_info
431 ,P_TIME_ATTRIBUTES hxc_self_service_time_deposit.building_block_attribute_info
432 ,P_MESSAGES in out NOCOPY hxc_self_service_time_deposit.MESSAGE_TABLE
433 ,P_TIME_ENTRY_RULE_GROUP_ID NUMBER);
434
435 Type t_change_att_rec is record
436 ( attribute_category hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
437 changed_attribute VARCHAR2(80),
438 field_name hxc_mapping_components.field_name%TYPE,
439 org_attribute_category hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
440 org_changed_attribute VARCHAR2(80)
441 );
442
443 Type t_change_att_tab is table of t_change_att_rec index by binary_integer;
444
445 PROCEDURE GET_PROMPTS (p_block_id in NUMBER,
446 p_blk_ovn in NUMBER,
447 p_attribute in VARCHAR2,
448 p_blk_type in VARCHAR2,
449 p_prompt in out nocopy VARCHAR2);
450
451 -- public procedure
452 -- publish_message
453 --
454 -- description
455 -- populates the message structure used in Self Service as opposed to
456 -- setting a raising messages using fnd_message
457
458 -- To display the message at Page level leave the p_time_building_block_id
459 -- and p_time_attribute_id parameters NULL.
460
461 -- To display the message at the Field Level enter either a time building
462 -- block id or time attribute id
463
464 -- parameters
465 -- p_name - the name of the message as defined in FND_NEW_MESSAGES
466 -- p_message_level - What type of message?
467 -- Valid values for message_level are ERROR, WARNING
468 -- or BUSINESS_MESSAGE
469 -- p_token_name - the token name associated with the message (if appropriate)
470 -- p_token_value - the token value associated with the message (if appropriate)
471 -- NOTE: token value can be up to 2000 chars long but fnd message only supports
472 -- message text of 2000
473 -- p_application_short_name - the application short code the message is registered against.
474 -- p_time_building_block_id - the time building block id of the item associated with the error
475 -- p_time_attribute_id - the time attribute id of the item associated with the error
476 -- p_message_extent - if you want you error to appear at the page level then
477 -- set this parameter to 'hxc_timecard.c_blk_children_extent'
478 -- otherwise leave it NULL and it will appear against the time
479 -- building block id or time attribute id specified.
480
481
482 PROCEDURE publish_message (
483 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
484 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
485 , p_token_name in VARCHAR2 DEFAULT NULL
486 , p_token_value in VARCHAR2 DEFAULT NULL
487 , p_application_short_name IN VARCHAR2 default 'HXC'
488 , p_time_building_block_id in NUMBER
489 , p_time_attribute_id in NUMBER DEFAULT NULL
490 , p_message_extent in VARCHAR2 DEFAULT NULL );
491
492
493 -- public procedure
494 -- publish_message
495 --
496 -- description
497 -- populates the message structure used in Self Service as opposed to
498 -- setting a raising messages using fnd_message
499
500 -- To display the message at Page level leave the p_time_building_block_id
501 -- and p_time_attribute_id parameters NULL.
502
503 -- To display the message at the Field Level enter either a time building
504 -- block id or time attribute id
505
506 -- parameters
507 -- p_name - the name of the message as defined in FND_NEW_MESSAGES
508 -- p_message_level - What type of message?
509 -- Valid values for message_level are ERROR, WARNING
510 -- or BUSINESS_MESSAGE
511 -- p_token_string - a string containing token name / token value pairs in that order
512 -- delimited by the ampersand character (&)
513 -- NOTE: token values embedded with this string can be up to 2000 chars long but fnd message
514 -- only supports message text of 2000
515 -- p_application_short_name - the application short code the message is registered against.
516 -- p_time_building_block_id - the time building block id of the item associated with the error
517 -- p_time_attribute_id - the time attribute id of the item associated with the error
518 -- p_message_extent - if you want you error to appear at the page level then
519 -- set this parameter to 'hxc_timecard.c_blk_children_extent'
520 -- otherwise leave it NULL and it will appear against the time
521 -- building block id or time attribute id specified.
522 PROCEDURE publish_message (
523 p_name in FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
524 , p_message_level in VARCHAR2 DEFAULT 'ERROR'
525 , p_token_string in VARCHAR2 DEFAULT NULL
526 , p_application_short_name IN VARCHAR2 default 'HXC'
527 , p_time_building_block_id in NUMBER
528 , p_time_attribute_id in NUMBER DEFAULT NULL
529 , p_message_extent in VARCHAR2 DEFAULT NULL );
530
531 FUNCTION return_archived_status (p_period IN r_period)
532 RETURN BOOLEAN;
533
534 function check_valid_calc_date_accrual(
535 p_resource_id NUMBER
536 , p_calculate_date DATE) return varchar2;
537
538 end hxc_time_entry_rules_utils_pkg;
539
540
[Home] [Help]