1 PACKAGE BODY hxt_retro_val AS
2 /* $Header: hxtrval.pkb 120.4 2007/01/05 18:24:05 nissharm noship $ */
3 -- Global package name
4 g_package CONSTANT VARCHAR2 (33) := 'hxt_retro_val.';
5 g_debug boolean := hr_utility.debug_enabled;
6 -- ancient relic that we need to remove later and place in hxt_batch_process,
7 -- the only code that calss this thing.
8 PROCEDURE mark_rows_complete (p_batch_id IN NUMBER)
9 IS
10 l_user_id fnd_user.user_id%TYPE := fnd_global.user_id;
11 BEGIN
12
13 -- the following sql has been condensed from the view creation statements
14 -- for HXT_BATCH_SUM_HOURS_V
15 -- HXT_BATCH_SUM_AMOUNTS_V
16 -- HXT_BATCH_SUM_AMOUNTS_HOURS_V
17 -- The views were used to transfer to paymix just prior to the call to
18 -- this function. This SQL could be further condensed, but I don't want
19 -- to do that yet, if ever. That's because if the above views change,
20 -- for whatever reason, leaving this sql as is will make it easier to make
21 -- changes here to reflect those changes.
22
23 UPDATE hxt_det_hours_worked_f
24 SET pay_status = 'C',
25 last_update_login = l_user_id,
26 last_update_date = SYSDATE
27 WHERE ROWID IN
28 (
29 /* batch_sum_amounts rows with override Amount<>0, Hours=0*/
30 SELECT hrw.ROWID
31 FROM hxt_timecards_x tim, -- commenting out. using sysdate view, now. RTF
32 hxt_det_hours_worked_x hrw -- commenting out. using sysdate view, now. RTF
33
34 -- begin OHMPERFFIX - performance fix done by Bryan Crissman and Damon
35 -- Grube at OHM. the Where rowid in () or rowid in ()
36 -- construct was causing large numbers of rows to be
37 -- returned.
38 WHERE hrw.parent_id > 0
39 AND tim.id = hrw.tim_id
40 AND tim.batch_id = p_batch_id
41 AND ( ( NVL (hrw.amount, 0) <> 0
42 AND NVL (hrw.hours, 0) = 0
43 )
44
45 /* batch_sum_amounts_hours rows with override Amount=0, Hours <>0*/
46 OR ( NVL (hrw.amount, 0) = 0
47 AND NVL (hrw.hours, 0) <> 0
48 )
49 ));
50 END;
51
52 PROCEDURE val_retro_timecard (
53 p_batch_id IN NUMBER,
54 p_tim_id IN NUMBER,
55 p_valid_retcode IN OUT NOCOPY NUMBER,
56 p_merge_flag IN VARCHAR2 DEFAULT '0',
57 p_merge_batches OUT NOCOPY HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE
58 )
59 IS
60 l_proc VARCHAR2 (72);
61 l_cnt BINARY_INTEGER;
62
63 CURSOR csr_additional_info (p_tim_id hxt_timecards_f.id%TYPE)
64 IS
65 SELECT DISTINCT htx.for_person_id, htx.time_period_id,
66 htx.approv_person_id, htx.auto_gen_flag
67 FROM hxt_timecards_x htx
68 WHERE htx.id = p_tim_id;
69
70 rec_additional_info csr_additional_info%ROWTYPE;
71 BEGIN
72 g_debug :=hr_utility.debug_enabled;
73 if g_debug then
74 l_proc := g_package
75 || 'val_retro_timecard';
76 hr_utility.set_location ( 'Entering:'
77 || l_proc, 10);
78 end if;
79 hxt_batch_val.reset_error_level;
80 hxt_batch_val.delete_prev_val_errors (p_tim_id => p_tim_id);
81 OPEN csr_additional_info (p_tim_id);
82 FETCH csr_additional_info INTO rec_additional_info;
83
84 IF (hxt_batch_val.errors_exist (p_tim_id => p_tim_id))
85 THEN
86 p_valid_retcode := 2;
87 ELSE
88 hxt_batch_val.validate_tc (
89 p_batch_id=> p_batch_id,
90 p_tim_id=> p_tim_id,
91 p_person_id=> rec_additional_info.for_person_id,
92 p_period_id=> rec_additional_info.time_period_id,
93 p_approv_person_id=> rec_additional_info.approv_person_id,
94 p_auto_gen_flag=> rec_additional_info.auto_gen_flag,
95 p_error_level=> p_valid_retcode
96 );
97 END IF;
98
99 /********Bug: 4620315 **********/
100 /*** To record the validated timecards details ***/
101
102 IF p_merge_flag = '1' THEN
103 if g_debug then
104 hr_utility.trace('Populating merge_batches record'||' batch_id: '||p_batch_id||' tc_id '||p_tim_id);
105 end if;
106 l_cnt := NVL(p_merge_batches.LAST,0) +1;
107
108 p_merge_batches(l_cnt).batch_id := p_batch_id;
109 p_merge_batches(l_cnt).tc_id := p_tim_id;
110 p_merge_batches(l_cnt).valid_tc_retcode := p_valid_retcode;
111
112 END IF;
113
114 /********Bug: 4620315 **********/
115
116 CLOSE csr_additional_info;
117 if g_debug then
118 hr_utility.set_location ( 'Leaving:'
119 || l_proc, 100);
120 end if;
121 END val_retro_timecard;
122 END hxt_retro_val;
[Home] [Help]