1 PACKAGE BODY HXT_TIM_COL_UTIL AS
2 /* $Header: hxtclut.pkb 120.4.12010000.2 2009/02/24 14:58:25 asrajago ship $ */
3
4 /*-------------------------------------------------------------------------
5 ||
6 || Private Module Definitions
7 ||
8 -------------------------------------------------------------------------*/
9
10 /****************************************************
11 get_element_cat()
12 Gets earning category of the given element type.
13 ****************************************************/
14 PROCEDURE get_element_cat(i_element_type_id IN NUMBER,
15 o_earning_cat OUT NOCOPY VARCHAR2 )
16 IS
17 BEGIN
18 SELECT eltv.hxt_earning_category
19 INTO o_earning_cat
20 FROM hxt_pay_element_types_f_ddf_v eltv
21 WHERE eltv.element_type_id = i_element_type_id;
22 EXCEPTION
23 WHEN no_data_found THEN
24 o_earning_cat := NULL;
25 WHEN OTHERS THEN
26 o_earning_cat := 'ERR';
27 END get_element_cat;
28
29
30 /*-------------------------------------------------------------------------
31 ||
32 || Public Module Definitions
33 ||
34 -------------------------------------------------------------------------*/
35
36 /********************************************************
37 Because customers will have different definitions
38 of what an Employee Number is, get_person_id exists
39 as a user exit.
40 For base system, this call will return
41 a valid person_id, last_name, and first_name from
42 the per_people_f using the input value employee_number
43 passed in as a parameter.
44 *********************************************************/
45 FUNCTION get_person_id(i_employee_number IN VARCHAR2,
46 i_business_group_id IN NUMBER,
47 i_date_worked IN DATE,
48 o_person_id OUT NOCOPY NUMBER,
49 o_last_name OUT NOCOPY VARCHAR2,
50 o_first_name OUT NOCOPY VARCHAR2)RETURN NUMBER IS
51 BEGIN
52 SELECT person_id,
53 last_name,
54 first_name
55 INTO o_person_id,
56 o_last_name,
57 o_first_name
58 FROM per_people_f
59 WHERE employee_number = i_employee_number
60 AND i_date_worked BETWEEN effective_start_date
61 AND effective_end_date
62 AND business_group_id = i_business_group_id;
63
64 RETURN 0;
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 RETURN 1;
69 WHEN OTHERS THEN
70 RETURN 2;
71
72 END get_person_id;
73 /***********************************************
74 determine_pay_date()
75 Rules to define what day an employee is paid
76 based upon the time punch will vary. We will
77 pass in start_time, end_time, and the person_id
78 as arguments to this function.
79 Phase I merely sets the date worked = to the
80 start time. Future releases will need to determine
81 the date worked based upon the employee's
82 work schedule.
83 ************************************************/
84 FUNCTION determine_pay_date( i_start_time IN DATE,
85 i_end_time IN DATE,
86 i_person_id IN NUMBER,
87 o_date_worked OUT NOCOPY DATE) RETURN NUMBER IS
88 BEGIN
89 o_date_worked := trunc(i_start_time);
90 RETURN 0;
91 END determine_pay_date;
92
93 /****************************************************
94 get_earn_pol_id()
95 Get the earning policy for an assignment number or
96 an earning policy name.
97 ****************************************************/
98 FUNCTION get_earn_pol_id(i_assignment_id IN NUMBER DEFAULT NULL,
99 i_date_worked IN DATE,
100 i_earn_pol_name IN VARCHAR2 DEFAULT NULL,
101 o_earn_pol_id OUT NOCOPY earn_pol_id%TYPE) RETURN NUMBER
102 IS
103
104 BEGIN
105 IF i_earn_pol_name IS NOT NULL THEN
106 /* Earning Policy override from API parameters. */
107 SELECT id
108 INTO o_earn_pol_id
109 FROM hxt_earning_policies
110 WHERE name = i_earn_pol_name
111 AND i_date_worked BETWEEN effective_start_date
112 AND effective_end_date;
113 ELSIF i_assignment_id IS NOT NULL THEN
114 /* No Earning Policy override, get EP for current assignment. */
115 SELECT ep.id
116 INTO o_earn_pol_id
117 FROM hxt_earning_policies ep,
118 hxt_per_aei_ddf_v pafv
119 WHERE pafv.assignment_id = i_assignment_id
120 AND i_date_worked BETWEEN pafv.effective_start_date
121 AND pafv.effective_end_date
122 AND pafv.hxt_earning_policy = ep.id
123 AND i_date_worked BETWEEN ep.effective_start_date
124 AND ep.effective_end_date;
125 ELSE
126 RETURN 2;
127 END IF;
128 RETURN 0;
129
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 RETURN 1;
133 WHEN OTHERS THEN
134 RETURN 2;
135
136 END get_earn_pol_id;
137
138 /****************************************************
139 chk_element_link()
140 Check that the assignment is eligible for the element.
141 ****************************************************/
142 --
143 FUNCTION chk_element_link(p_asg_id IN NUMBER,
144 p_date_worked IN DATE,
145 p_element_type_id IN NUMBER) RETURN NUMBER
146 IS
147 --
148 l_exists VARCHAR2(1);
149 --
150 BEGIN
151
152 SELECT MAX('Y') --5574629
153 INTO l_exists
154 FROM pay_element_links_f ell,
155 per_all_assignments_f asm,
156 hxt_pay_element_types_f_ddf_v eltv
157 WHERE asm.assignment_id = p_asg_id
158 AND p_date_worked BETWEEN asm.effective_start_date
159 AND asm.effective_end_date
160 AND ell.element_type_id = p_element_type_id
161 AND p_date_worked BETWEEN ell.effective_start_date
162 AND ell.effective_end_date
163 -- Condition modified for Bug 2669059
164 -- AND eltv.element_type_id = p_element_type_id
165 AND eltv.element_type_id = ell.element_type_id
166 AND p_date_worked BETWEEN eltv.effective_start_date
167 AND eltv.effective_end_date
168 AND eltv.hxt_earning_category is not null
169 AND nvl(ell.organization_id,nvl(asm.organization_id,-1)) =
170 nvl(asm.organization_id,-1)
171 AND (ell.people_group_id is null
172 or exists (SELECT 'x'
173 FROM pay_assignment_link_usages_f usage
174 WHERE usage.assignment_id = asm.assignment_id
175 AND usage.element_link_id = ell.element_link_id
176 AND p_date_worked BETWEEN usage.effective_start_date
177 AND usage.effective_end_date))
178 AND nvl(ell.job_id, nvl(asm.job_id,-1)) = nvl(asm.job_id,-1)
179 AND nvl(ell.position_id, nvl(asm.position_id,-1)) = nvl(asm.position_id,-1)
180 AND nvl(ell.grade_id,nvl(asm.grade_id,-1)) = nvl(asm.grade_id,-1)
181 AND nvl(ell.location_id,nvl(asm.location_id,-1)) = nvl(asm.location_id,-1)
182 AND nvl(ell.payroll_id,nvl(asm.payroll_id,-1)) = nvl(asm.payroll_id,-1)
183 AND nvl(ell.employment_category, nvl(asm.employment_category,-1)) =
184 nvl(asm.employment_category,-1)
185 AND nvl(ell.pay_basis_id,nvl(asm.pay_basis_id,-1)) =
186 nvl(asm.pay_basis_id,-1)
187 AND nvl(ell.business_group_id, nvl(asm.business_group_id,-1)) =
188 nvl(asm.business_group_id,-1);
189 RETURN 0;
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 RETURN 1;
193 WHEN OTHERS THEN
194 RETURN 2;
195
196 END chk_element_link;
197
198 /****************************************************
199 get_element_type_id()
200 Get the element type id for an element name.
201 ****************************************************/
202 FUNCTION get_element_type_id(i_element_name IN VARCHAR2,
203 i_date_worked IN DATE,
204 i_bg_id IN NUMBER,
205 o_element_type_id OUT NOCOPY NUMBER)RETURN NUMBER
206 IS
207 BEGIN
208 /* SQL query modified for Bug 2597231 */
209 SELECT elt.element_type_id
210 INTO o_element_type_id
211 FROM pay_element_types_f elt,
212 pay_element_types_f_tl eltt
213 WHERE eltt.element_name = i_element_name
214 AND eltt.language = userenv('LANG')
215 AND elt.element_type_id = eltt.element_type_id
216 AND (elt.business_group_id = i_bg_id or elt.business_group_id is null)
217 AND ( legislation_code = ( SELECT legislation_code
218 FROM per_business_groups
219 WHERE business_group_id = i_bg_id) or
220 legislation_code is NULL )
221 AND i_date_worked BETWEEN elt.effective_start_date
222 AND elt.effective_end_date ;
223 RETURN 0;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 RETURN 1;
227 WHEN OTHERS THEN
228 RETURN 2;
229
230 END get_element_type_id;
231
232
233 /****************************************************
234 get_grade_id()
235 Get the grade id for a grade code.
236 ****************************************************/
237 FUNCTION get_grade_id(i_grade_name IN VARCHAR2,
238 i_business_group_id IN NUMBER,
239 i_date_worked IN DATE,
240 o_grade_id OUT NOCOPY NUMBER)RETURN NUMBER
241 IS
242 BEGIN
243 SELECT grade_id
244 INTO o_grade_id
245 FROM per_grades
246 WHERE name = i_grade_name
247 AND business_group_id + 0 = i_business_group_id
248 AND i_date_worked BETWEEN date_from
249 AND nvl(date_to,hr_general.end_of_time);
250 RETURN 0;
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 RETURN 1;
254 WHEN OTHERS THEN
255 RETURN 2;
256 END get_grade_id;
257
258 /****************************************************
259 get_location_id()
260 Get the location id for a location code.
261 ****************************************************/
262 FUNCTION get_location_id(i_location_code IN VARCHAR2,
263 i_date_worked IN DATE,
264 o_location_id OUT NOCOPY NUMBER)RETURN NUMBER
265 IS
266 BEGIN
267
268 SELECT location_id
269 INTO o_location_id
270 FROM hr_locations
271 WHERE location_code = i_location_code
272 AND COUNTRY = 'US'
273 AND i_date_worked <= NVL(INACTIVE_DATE, i_date_worked);
274
275 RETURN 0;
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 RETURN 1;
280 WHEN OTHERS THEN
281 RETURN 2;
282
283 END get_location_id;
284
285
286 /****************************************************
287 get_project_id()
288 Get the project id for a project number.
289 ****************************************************/
290 FUNCTION get_project_id(i_project_number IN VARCHAR2,
291 i_date_worked IN DATE,
292 o_project_id OUT NOCOPY project_id%TYPE) RETURN NUMBER
293 IS
294 BEGIN
295 /* PWM 05-APR-00 Added organization id to select clause */
296 SELECT PRJ.PROJECT_ID
297 INTO o_project_id
298 FROM HXT_ALL_PROJECTS_V PRJ
299 WHERE PRJ.PROJECT_NUMBER = i_project_number;
300
301 /* PWM 05-APR-00 Added organization id to select clause
302 */
303 RETURN 0;
304
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 RETURN 1;
308 WHEN OTHERS THEN
309 RETURN 2;
310
311 END get_project_id;
312
313 /****************************************************
314 get_task_id()
315 Get the task id for a task number.
316 ****************************************************/
317 FUNCTION get_task_id(i_task_number IN VARCHAR2,
318 i_date_worked IN DATE,
319 i_project_id IN NUMBER, /* PWM 05-APR-00 */
320 o_task_id OUT NOCOPY NUMBER)RETURN NUMBER
321 IS
322 BEGIN
323 SELECT TAS.TASK_ID
324 INTO o_task_id
325 FROM HXT_ALL_TASKS_V TAS
326 WHERE TAS.task_number = i_task_number
327 AND TAS.project_id = i_project_id ;
328
329 RETURN 0;
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 RETURN 1;
334 WHEN OTHERS THEN
335 RETURN 2;
336
337 END get_task_id;
338
339
340 /*****************************************************
341 validate_separate_chk_flg()
342 Up-case and validate the separate check flag parameter.
343 *****************************************************/
344 FUNCTION validate_separate_chk_flg
345 (io_separate_check_flag IN OUT NOCOPY VARCHAR2) RETURN NUMBER IS
346 BEGIN
347 io_separate_check_flag := UPPER(io_separate_check_flag);
348
349 IF io_separate_check_flag IN ('Y','N') THEN
350 RETURN 0;
351 ELSE
352 RETURN 1;
353 END IF;
354
355 END validate_separate_chk_flg;
356
357
358 /***********************************************************
359 validate_earn_reason_code()
360 Validate the earning reason code parameter of record_time
361 based on the date_worked and the earning_category for the
362 element_type_id for the hours_type parameter of record_time.
363 ************************************************************/
364 FUNCTION validate_earn_reason_code(i_earn_reason_code IN VARCHAR2,
365 i_date_worked IN DATE) RETURN NUMBER
366 -- i_element_type_id IN NUMBER)RETURN NUMBER
367 IS
368
369 -- hxt_earning_category VARCHAR2(30);
370 hold_reason_code VARCHAR2(80);
371
372 BEGIN
373 -- get_element_cat(i_element_type_id, hxt_earning_category);
374 -- IF hxt_earning_category IS NULL OR
375 -- hxt_earning_category = 'ERR'
376 -- THEN
377 -- RETURN 1;
378 -- ELSE
379 SELECT fcl.meaning
380 INTO hold_reason_code
381 FROM hr_lookups fcl
382 WHERE fcl.lookup_code = i_earn_reason_code
383 AND i_date_worked BETWEEN NVL(fcl.start_date_active, i_date_worked)
384 AND NVL(fcl.end_date_active, i_date_worked)
385 AND fcl.application_id = g_orcl_hr_app_id_cons
386 AND fcl.enabled_flag = 'Y'
387 AND fcl.lookup_type = 'ELE_ENTRY_REASON';
388
389 RETURN 0;
390 -- END IF;
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 RETURN 2;
394 WHEN OTHERS THEN
395 RETURN 3;
396 END validate_earn_reason_code;
397
398
399 /***********************************************************
400 validate_time_summary_id()
401 Validate the time summary id parameter of record_time.
402 ************************************************************/
403 FUNCTION validate_time_summary_id( i_time_summary_id IN NUMBER)RETURN NUMBER
404 IS
405
406 l_dummy CHAR(1);
407
408 BEGIN
409 SELECT '1'
410 INTO l_dummy
411 FROM hxt_sum_hours_worked
412 WHERE id = i_time_summary_id;
413
414 RETURN 0;
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 RETURN 1;
418 WHEN OTHERS THEN
419 RETURN 2;
420 END validate_time_summary_id;
421
422
423 /*******************************************************************
424 validate_cost_center_id()
425 This validates the foreign key via the non-table lookup item(s), an
426 selects the foreign key column(s) into the hidden base table item(s)
427 ********************************************************************/
428 FUNCTION validate_cost_center_id(i_cost_center_id IN NUMBER,
429 i_date_worked IN DATE )RETURN NUMBER
430 IS
431 hold_cost_center_id NUMBER(15);
432 BEGIN
433 --
434 -- Replace ADT code with TA36 cost allocation code.
435 --
436 SELECT PCAK.COST_ALLOCATION_KEYFLEX_ID
437 INTO hold_cost_center_id
438 FROM PAY_COST_ALLOCATION_KEYFLEX PCAK
439 WHERE PCAK.COST_ALLOCATION_KEYFLEX_ID = i_cost_center_id;
440 RETURN 0;
441 EXCEPTION
442 WHEN NO_DATA_FOUND THEN
443 RETURN 1;
444 WHEN OTHERS THEN
445 RETURN 2;
446 END validate_cost_center_id;
447
448
449 /*******************************************************************
450 validate_timecard_source()
451 Validates the timecard source parameter.
452
453 Bug 2398037.
454 the comparison of meaning with i_timecard_source will fail in
455 non-english instances. Instead, we can use lookup code as the function
456 only checks for the existence of lookup.
457
458 ********************************************************************/
459 FUNCTION validate_timecard_source(i_timecard_source IN VARCHAR2,
460 i_date_worked IN DATE,
461 o_timecard_source_code OUT NOCOPY VARCHAR2 )RETURN NUMBER IS
462 BEGIN
463 SELECT fcl.lookup_code
464 INTO o_timecard_source_code
465 FROM hr_lookups fcl
466 /* WHERE fcl.meaning = i_timecard_source bug 2398037 */
467 WHERE fcl.lookup_code = 'S'
468 AND i_date_worked BETWEEN NVL(fcl.start_date_active, i_date_worked)
469 AND NVL(fcl.end_date_active, i_date_worked)
470 AND fcl.application_id = g_orcl_tm_app_id_cons
471 AND fcl.enabled_flag = 'Y'
472 AND fcl.lookup_type = 'HXT_TIMECARD_SOURCE';
473 RETURN 0;
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 RETURN 1;
477 WHEN OTHERS THEN
478 RETURN 2;
479 END validate_timecard_source;
480
481
482 /*******************************************************************
483 validate_wage_code()
484 Validate the prevailing wage code.
485 ********************************************************************/
486 FUNCTION validate_wage_code(i_wage_code IN VARCHAR2,
487 i_date_worked IN DATE )RETURN NUMBER
488 IS
489 hold_wage_code VARCHAR2(10);
490 BEGIN
491 SELECT prev_wage_code
492 INTO hold_wage_code
493 FROM hxt_prev_wage_base pwb
494 WHERE pwb.prev_wage_code = i_wage_code
495 AND i_date_worked BETWEEN pwb.effective_start_date
496 AND pwb.effective_end_date;
497 RETURN 0;
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 RETURN 1;
501 WHEN OTHERS THEN
502 RETURN 2;
503 END validate_wage_code;
504
505
506 /*******************************************************************
507 get_session_date()
508 Get the current session date from fnd_sessions.
509 ********************************************************************/
510 FUNCTION get_session_date(o_sess_date OUT NOCOPY DATE )RETURN NUMBER
511 IS
512 hold_sess_date DATE;
513 BEGIN
514
515 -- Bug 7359347
516 -- Check if there exists a cached value before querying on FND_SESSIONS.
517
518 IF g_session_date.EXISTS(USERENV('SESSIONID'))
519 THEN
520 o_sess_date := g_session_date(USERENV('SESSIONID'));
521 ELSE
522 SELECT effective_date
523 INTO hold_sess_date
524 FROM fnd_sessions
525 WHERE session_id = USERENV('SESSIONID');
526
527 o_sess_date := hold_sess_date;
528 -- Bug 7359347
529 -- cache the new value for next call.
530 g_session_date(USERENV('SESSIONID')) := hold_sess_date;
531
532 END IF;
533
534 RETURN 0;
535 EXCEPTION
536 WHEN NO_DATA_FOUND THEN
537 RETURN 1;
538 END get_session_date;
539
540
541 -- Bug 7359347
542 -- Following function picks up the session date from
543 -- the global g_session_date table instead of FND_SESSIONS if it exists.
544 -- It would call get_session_date above if the value doesnt exist.
545 -- Subsequently, the returned value is cached.
546
547 FUNCTION return_session_date
548 RETURN DATE
549 IS
550
551 l_retcode NUMBER;
552 l_sessdate DATE;
553
554 BEGIN
555
556 -- Check if this session already has one.
557 IF g_session_date.EXISTS(USERENV('SESSIONID'))
558 THEN
559 -- It has one, return it.
560 RETURN g_session_date(USERENV('SESSIONID'));
561 ELSE
562 -- Need to pick up one from FND_SESIONS. Call the above function.
563 l_retcode := get_session_date(l_sessdate);
564 END IF;
565
566 RETURN l_sessdate;
567 END return_session_date;
568
569
570
571
572 END HXT_TIM_COL_UTIL;
[Home] [Help]