1 PACKAGE BODY hxc_timecard_util AS
2 /* $Header: hxcutiltc.pkb 120.0.12010000.2 2009/12/31 10:02:51 amakrish ship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- |---------------------< get_period_end >------------------------------------|
7 -- ----------------------------------------------------------------------------
8 /* This function return the end period for
9 a start period given
10 */
11 FUNCTION get_end_period
12 (p_start_date in date,
13 p_number_per_fiscal_year in number,
14 p_duration_in_days in number)
15 return date is
16
17 l_period_end date;
18
19 BEGIN
20
21 IF p_number_per_fiscal_year = 1 THEN
22 l_period_end := (add_months(p_start_date,12) - 1);
23 ELSIF p_number_per_fiscal_year = 2 THEN
24 l_period_end := (add_months(p_start_date,6) - 1);
25 ELSIF p_number_per_fiscal_year = 4 THEN
26 l_period_end := (add_months(p_start_date,3) - 1);
27 ELSIF p_number_per_fiscal_year = 6 THEN
28 l_period_end := (add_months(p_start_date,2) - 1);
29 ELSIF p_number_per_fiscal_year = 12 THEN
30 l_period_end := (add_months(p_start_date,1) - 1);
31 ELSIF p_number_per_fiscal_year = 13 THEN
32 l_period_end := p_start_date + 27;
33 ELSIF p_number_per_fiscal_year = 24 THEN
34 l_period_end := p_start_date + 14;
35 ELSIF p_number_per_fiscal_year = 26 THEN
36 l_period_end := p_start_date + 13;
37 ELSIF p_number_per_fiscal_year = 52 THEN
38 l_period_end := p_start_date + 6;
39 ELSE
40 l_period_end := p_start_date + p_duration_in_days - 1;
41 END IF;
42
43 return(l_period_end);
44
45 END get_end_period;
46
47
48
49 --
50 -- ----------------------------------------------------------------------------
51 -- |-----------------------------< get_first_empty_period >--------------------|
52 -- ----------------------------------------------------------------------------
53 /* This function return the first empty period (no timecard)
54 after the start_date given in parameter
55 */
56
57 FUNCTION get_first_empty_period
58 (p_resource_id in number,
59 p_start_date in date,
60 p_period_type in varchar2,
61 p_number_per_fiscal_year in number,
62 p_duration_in_days in number
63 ) return varchar2 is
64
65 l_period_end date;
66 l_period_start date;
67 l_found_first_empty_period boolean := false;
68 lv_exists varchar2(6) := NULL;
69 l_dummy varchar2(1);
70
71 Cursor c_period_exists(cp_resource_id in number,
72 cp_period_start in date) is
73 Select 'exists'
74 From sys.dual
75 Where EXISTS (
76 Select 'x'
77 From hxc_time_building_blocks htb
78 Where htb.scope = 'TIMECARD'
79 And htb.type = 'RANGE'
80 And htb.date_to = hr_general.end_of_time
81 And htb.resource_type = 'PERSON'
82 And htb.resource_id = cp_resource_id
83 And htb.start_time = l_period_start);
84
85 CURSOR c_person_valid(p_resource_id in number)
86 IS
87 select 1 from per_all_people_f where person_id=p_resource_id;
88
89
90 BEGIN
91
92 open c_person_valid(p_resource_id);
93 fetch c_person_valid into l_dummy;
94
95 if(c_person_valid%NOTFOUND) then
96 -- Raise an error as the resource_id isnt valid
97
98 fnd_message.set_name('HXC', 'HXC_INVALID_RESOURCE_ID');
99 fnd_message.set_token('RES_ID',p_resource_id);
100 hr_utility.raise_error;
101 END IF;
102
103
104 --
105 -- set the first existing period for the person
106 --
107 l_period_start := p_start_date;
108 l_period_end := get_end_period(l_period_start,
109 p_number_per_fiscal_year,
110 p_duration_in_days);
111 --
112 -- Open the loop still
113 -- find the first empty period
114 -- or the the end of period > end_of_time
115 --
116 LOOP
117 --
118 -- try to
119 -- find the first empty period
120 --
121 open c_period_exists(p_resource_id,
122 l_period_start);
123 fetch c_period_exists into lv_exists;
124 close c_period_exists;
125
126 IF lv_exists is null THEN
127 l_found_first_empty_period := true;
128 ELSE
129 lv_exists := NULL;
130 END IF;
131 --
132 -- if not found then jump to the next period
133 --
134 IF (l_found_first_empty_period = false) THEN
135 l_period_start := l_period_end + 1;
136 l_period_end := get_end_period(l_period_start,
137 p_number_per_fiscal_year,
138 p_duration_in_days);
139 END IF;
140 EXIT WHEN (l_found_first_empty_period = true);
141 EXIT WHEN (l_period_start > hr_general.end_of_time);
142 END LOOP;
143
144
145 return (to_char(l_period_start,'YYYY/MM/DD')||'|'||to_char(l_period_end,'YYYY/MM/DD'));
146
147 END get_first_empty_period;
148 --
149 -- ----------------------------------------------------------------------------
150 -- |---------------------< get_submission_date >------------------------------|
151 -- ----------------------------------------------------------------------------
152 /* This function return the end period for
153 a start period given
154 */
155 FUNCTION get_submission_date
156 (p_timecard_id in number,
157 p_timecard_ovn in number)
158 return date is
159
160 CURSOR c_timecard_day_sub_date
161 (p_timecard_id in number,
162 p_timecard_ovn in number)
163 IS
164 select max(ht.transaction_date)
165 from hxc_dep_transactions ht,
166 hxc_dep_transaction_details htd,
167 hxc_time_building_blocks htb
168 where htb.parent_building_block_id = p_timecard_id
169 and htb.parent_building_block_ovn = p_timecard_ovn
170 and htb.scope = 'DAY'
171 and htd.time_building_block_id = htb.time_building_block_id
172 and htd.time_building_block_ovn = htb.object_version_number
173 and htd.status='SUCCESS'
174 and ht.transaction_id=htd.transaction_id
175 and ht.type='DEPOSIT'
176 and ht.status='SUCCESS' ;
177
178 CURSOR c_timecard_detail_sub_date
179 (p_timecard_id in number,
180 p_timecard_ovn in number)
181 IS
182 select max(ht.transaction_date)
183 from hxc_dep_transactions ht,
184 hxc_dep_transaction_details htd,
185 hxc_time_building_blocks htb_day,
186 hxc_time_building_blocks htb_detail
187 where htb_day.parent_building_block_id = p_timecard_id
188 and htb_day.parent_building_block_ovn = p_timecard_ovn
189 and htb_day.scope = 'DAY'
190 and htb_detail.parent_building_block_id = htb_day.time_building_block_id
191 and htb_detail.parent_building_block_ovn = htb_day.object_version_number
192 and htb_detail.scope = 'DETAIL'
193 and htd.time_building_block_id = htb_detail.time_building_block_id
194 and htd.time_building_block_ovn = htb_detail.object_version_number
195 and htd.status='SUCCESS'
196 and ht.transaction_id=htd.transaction_id
197 and ht.type='DEPOSIT'
198 and ht.status='SUCCESS' ;
199
200 l_return_date date := null;
201
202 BEGIN
203
204 open c_timecard_detail_sub_date(p_timecard_id,p_timecard_ovn);
205 fetch c_timecard_detail_sub_date into l_return_date;
206 close c_timecard_detail_sub_date;
207
208 if (l_return_date is null) then
209
210 open c_timecard_day_sub_date(p_timecard_id,p_timecard_ovn);
211 fetch c_timecard_day_sub_date into l_return_date;
212 close c_timecard_day_sub_date;
213
214 end if;
215
216
217 return l_return_date;
218
219 END get_submission_date;
220 END hxc_timecard_util;
[Home] [Help]