[Home] [Help]
PACKAGE BODY: APPS.HXC_TER_UPLOAD_PKG
Source
1 PACKAGE BODY hxc_ter_upload_pkg AS
2 /* $Header: hxcterupl.pkb 120.2 2005/09/23 09:20:53 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 PROCEDURE load_ter_row (
7 p_name IN VARCHAR2
8 , p_legislation_code IN VARCHAR2
9 , p_rule_usage IN VARCHAR2
10 , p_mapping_name IN VARCHAR2
11 , p_formula_name IN VARCHAR2
12 , p_attribute_category IN VARCHAR2
13 , p_attribute1 IN Varchar2
14 , p_attribute2 IN varchar2
15 , p_attribute3 IN Varchar2
16 , p_attribute4 IN varchar2
17 , p_attribute5 IN Varchar2
18 , p_attribute6 IN varchar2
19 , p_attribute7 IN Varchar2
20 , p_attribute8 IN varchar2
21 , p_attribute9 IN Varchar2
22 , p_attribute10 IN varchar2
23 , p_attribute11 IN Varchar2
24 , p_attribute12 IN varchar2
25 , p_attribute13 IN Varchar2
26 , p_attribute14 IN varchar2
27 , p_attribute15 IN Varchar2
28 , p_attribute16 IN varchar2
29 , p_attribute17 IN Varchar2
30 , p_attribute18 IN varchar2
31 , p_attribute19 IN Varchar2
32 , p_attribute20 IN varchar2
33 , p_attribute21 IN Varchar2
34 , p_attribute22 IN varchar2
35 , p_attribute23 IN Varchar2
36 , p_attribute24 IN varchar2
37 , p_attribute25 IN Varchar2
38 , p_attribute26 IN varchar2
39 , p_attribute27 IN Varchar2
40 , p_attribute28 IN varchar2
41 , p_attribute29 IN Varchar2
42 , p_attribute30 IN varchar2
43 , p_description IN VARCHAR2
44 , p_start_date IN VARCHAR2
45 , p_end_date IN VARCHAR2
46 , p_owner IN VARCHAR2
47 , p_custom_mode IN VARCHAR2 ) IS
48
49 l_time_entry_rule_id hxc_time_entry_rules.time_entry_rule_id%TYPE;
50 l_mapping_id hxc_mappings.mapping_id%TYPE;
51 l_formula_id ff_formulas_f.formula_id%TYPE;
52 l_ovn hxc_deposit_processes.object_version_number%TYPE := NULL;
53 l_owner VARCHAR2(6);
54 l_formula_name Varchar2(150);
55
56 l_attribute_category hxc_time_entry_rules.attribute_category%TYPE;
57 l_attribute1 hxc_time_entry_rules.attribute1%TYPE;
58 l_attribute2 hxc_time_entry_rules.attribute1%TYPE;
59
60 FUNCTION get_ff_id ( p_formula_name VARCHAR2 ) RETURN NUMBER IS
61
62 CURSOR csr_get_ff_id IS
63 SELECT formula_id
64 FROM ff_formulas_f ff
65 WHERE ff.formula_name = p_formula_name
66 AND TO_DATE(p_start_date, 'DD-MM-YYYY') BETWEEN ff.effective_start_date AND ff.effective_end_date;
67
68 l_formula_id ff_formulas_f.formula_id%TYPE;
69
70 BEGIN
71
72 OPEN csr_get_ff_id;
73 FETCH csr_get_ff_id INTO l_formula_id;
74 CLOSE csr_get_ff_id;
75
76 RETURN l_formula_id;
77
78 END get_ff_id;
79
80 FUNCTION get_app_id ( p_time_recipient_name VARCHAR2 ) RETURN NUMBER IS
81
82 l_tr_id hxc_time_recipients.time_recipient_id%TYPE;
83
84 CURSOR csr_get_app_id IS
85 SELECT tr.time_recipient_id
86 FROM hxc_time_recipients tr
87 WHERE tr.name = p_time_recipient_name;
88
89 BEGIN
90
91 OPEN csr_get_app_id;
92 FETCH csr_get_app_id INTO l_tr_id;
93 CLOSE csr_get_app_id;
94
95 RETURN l_tr_id;
96
97 END get_app_id;
98
99 FUNCTION get_tc_id ( p_time_category_name VARCHAR2 ) RETURN NUMBER IS
100
101 l_tc_id hxc_time_categories.time_category_id%TYPE;
102
103 CURSOR csr_get_tc_id IS
104 SELECT htc.time_category_id
105 FROM hxc_time_categories htc
106 WHERE htc.time_category_name = p_time_category_name;
107
108 BEGIN
109
110 OPEN csr_get_tc_id;
111 FETCH csr_get_tc_id INTO l_tc_id;
112 CLOSE csr_get_tc_id;
113
114 RETURN l_tc_id;
115
116 END get_tc_id;
117
118
119 BEGIN -- load_ter_row
120
121 g_debug := hr_utility.debug_enabled;
122
123 l_mapping_id := hxc_mcu_upload_pkg.get_mapping_id ( p_mapping_name );
124 l_formula_id := get_ff_id ( p_formula_name );
125 l_attribute_category := p_formula_name;
126
127 IF ( p_formula_name = 'HXC_ELP' )
128 THEN
129 l_attribute1 := get_app_id ( p_attribute1 );
130 l_attribute2 := get_tc_id ( p_attribute2 );
131
132 ELSIF ( p_formula_name = 'HXC_CLA_CHANGE_FORMULA' or p_formula_name = 'HXC_CLA_LATE_FORMULA')
133 THEN
134 if (p_attribute1 is not null) then
135 l_attribute1 := get_tc_id (p_attribute1);
136 else
137 l_attribute1 := null;
138 end if;
139
140 l_attribute2 := p_attribute2;
141
142 ELSE
143
144 l_attribute1 := p_attribute1;
145 l_attribute2 := p_attribute2;
146
147 END IF;
148
149 if g_debug then
150 hr_utility.set_location('gaz', 1);
151 end if;
152
153 SELECT time_entry_rule_id
154 , object_version_number
155 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
156 INTO l_time_entry_rule_id
157 , l_ovn
158 , l_owner
159 FROM hxc_time_entry_rules
160 WHERE name = P_NAME;
161
162 if g_debug then
163 hr_utility.set_location('gaz', 2);
164 end if;
165
166 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
167 THEN
168
169 if g_debug then
170 hr_utility.set_location('gaz', 3);
171 end if;
172 hxc_time_entry_rule_api.update_time_entry_rule (
173 p_effective_date => to_date(p_start_date, 'DD-MM-YYYY')
174 , p_time_entry_rule_id => l_time_entry_rule_id
175 , p_name => p_name
176 , p_business_group_id => null
177 , p_legislation_code => p_legislation_code
178 , p_rule_usage => p_rule_usage
179 , p_start_date => to_date(p_start_date, 'DD-MM-YYYY')
180 , p_mapping_id => l_mapping_id
181 , p_formula_id => l_formula_id
182 , p_description => p_description
183 , p_end_date => to_date(p_end_date, 'DD-MM-YYYY')
184 , p_attribute_category => l_attribute_category
185 , p_attribute1 => l_attribute1
186 , p_attribute2 => l_attribute2
187 , p_attribute3 => p_attribute3
188 , p_attribute4 => p_attribute4
189 , p_attribute5 => p_attribute5
190 , p_attribute6 => p_attribute6
191 , p_attribute7 => p_attribute7
192 , p_attribute8 => p_attribute8
193 , p_attribute9 => p_attribute9
194 , p_attribute10 => p_attribute10
195 , p_attribute11 => p_attribute11
196 , p_attribute12 => p_attribute12
197 , p_attribute13 => p_attribute13
198 , p_attribute14 => p_attribute14
199 , p_attribute15 => p_attribute15
200 , p_attribute16 => p_attribute16
201 , p_attribute17 => p_attribute17
202 , p_attribute18 => p_attribute18
203 , p_attribute19 => p_attribute19
204 , p_attribute20 => p_attribute20
205 , p_attribute21 => p_attribute21
206 , p_attribute22 => p_attribute22
207 , p_attribute23 => p_attribute23
208 , p_attribute24 => p_attribute24
209 , p_attribute25 => p_attribute25
210 , p_attribute26 => p_attribute26
211 , p_attribute27 => p_attribute27
212 , p_attribute28 => p_attribute28
213 , p_attribute29 => p_attribute29
214 , p_attribute30 => p_attribute30
215 , p_object_version_number => l_ovn );
216
217 END IF;
218
219 EXCEPTION WHEN NO_DATA_FOUND
220 THEN
221
222 if g_debug then
223 hr_utility.set_location('gaz', 4);
224 end if;
225 hxc_time_entry_rule_api.create_time_entry_rule (
226 p_effective_date => to_date(p_start_date, 'DD-MM-YYYY')
227 , p_name => p_name
228 , p_business_group_id => null
229 , p_legislation_code => p_legislation_code
230 , p_rule_usage => p_rule_usage
231 , p_start_date => to_date(p_start_date, 'DD-MM-YYYY')
232 , p_mapping_id => l_mapping_id
233 , p_formula_id => l_formula_id
234 , p_description => p_description
235 , p_end_date => to_date(p_end_date, 'DD-MM-YYYY')
236 , p_time_entry_rule_id => l_time_entry_rule_id
237 , p_attribute_category => l_attribute_category
238 , p_attribute1 => l_attribute1
239 , p_attribute2 => l_attribute2
240 , p_attribute3 => p_attribute3
241 , p_attribute4 => p_attribute4
242 , p_attribute5 => p_attribute5
243 , p_attribute6 => p_attribute6
244 , p_attribute7 => p_attribute7
245 , p_attribute8 => p_attribute8
246 , p_attribute9 => p_attribute9
247 , p_attribute10 => p_attribute10
248 , p_attribute11 => p_attribute11
249 , p_attribute12 => p_attribute12
250 , p_attribute13 => p_attribute13
251 , p_attribute14 => p_attribute14
252 , p_attribute15 => p_attribute15
253 , p_attribute16 => p_attribute16
254 , p_attribute17 => p_attribute17
255 , p_attribute18 => p_attribute18
256 , p_attribute19 => p_attribute19
257 , p_attribute20 => p_attribute20
258 , p_attribute21 => p_attribute21
259 , p_attribute22 => p_attribute22
260 , p_attribute23 => p_attribute23
261 , p_attribute24 => p_attribute24
262 , p_attribute25 => p_attribute25
263 , p_attribute26 => p_attribute26
264 , p_attribute27 => p_attribute27
265 , p_attribute28 => p_attribute28
266 , p_attribute29 => p_attribute29
267 , p_attribute30 => p_attribute30
268 , p_object_version_number => l_ovn );
269
270 END load_ter_row;
271
272 PROCEDURE load_daru_row (
273 p_time_entry_rule_name IN VARCHAR2
274 , p_approval_style_name IN VARCHAR2
275 , p_time_recipient IN VARCHAR2
276 , p_owner IN VARCHAR2
277 , p_custom_mode IN VARCHAR2 ) IS
278
279 l_data_app_rule_usage_id hxc_data_app_rule_usages.data_app_rule_usage_id%TYPE;
280 l_time_entry_rule_id hxc_data_app_rule_usages.time_entry_rule_id%TYPE;
281 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
282 l_time_recipient_id hxc_time_recipients.time_recipient_id%TYPE;
283 l_ovn hxc_deposit_processes.object_version_number%TYPE;
284 l_owner VARCHAR2(6);
285
286 FUNCTION get_time_entry_rule_id ( p_time_entry_rule_name VARCHAR2 ) RETURN NUMBER IS
287
288 CURSOR csr_get_ter_id IS
289 SELECT time_entry_rule_id
290 FROM hxc_time_entry_rules
291 WHERE name = p_time_entry_rule_name;
292
293 l_time_entry_rule_id hxc_time_entry_rules.time_entry_rule_id%TYPE;
294
295 BEGIN
296
297 OPEN csr_get_ter_id;
298 FETCH csr_get_ter_id INTO l_time_entry_rule_id;
299 CLOSE csr_get_ter_id;
300
301 RETURN l_time_entry_rule_id;
302
303 END get_time_entry_rule_id;
304
305
306
307 FUNCTION get_approval_style_id ( p_approval_style_name VARCHAR2 ) RETURN NUMBER IS
308
309 CURSOR csr_get_as_id IS
310 SELECT approval_style_id
311 FROM hxc_approval_styles
312 WHERE name = p_approval_style_name;
313
314 l_approval_style_id hxc_approval_styles.approval_style_id%TYPE;
315
316 BEGIN
317
318 OPEN csr_get_as_id;
319 FETCH csr_get_as_id INTO l_approval_style_id;
320 CLOSE csr_get_as_id;
321
322 RETURN l_approval_style_id;
323
324 END get_approval_style_id;
325
326 BEGIN
327
328 l_time_entry_rule_id := get_time_entry_rule_id ( p_time_entry_rule_name );
329 l_approval_style_id := get_approval_style_id ( p_approval_style_name );
330 l_time_recipient_id := hxc_ret_upload_pkg.get_time_recipient_id ( p_time_recipient );
331
332 SELECT data_app_rule_usage_id
333 , object_version_number
334 , DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
335 INTO l_data_app_rule_usage_id
336 , l_ovn
337 , l_owner
338 FROM hxc_data_app_rule_usages
339 WHERE time_entry_rule_id = l_time_entry_rule_id
340 AND approval_style_id = l_approval_style_id;
341
342 IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
343 THEN
344
345 hxc_data_app_rule_usages_api.update_data_app_rule_usages (
346 p_effective_date => sysdate
347 , p_approval_style_id => l_approval_style_id
348 , p_time_entry_rule_id => l_time_entry_rule_id
349 , p_time_recipient_id => l_time_recipient_id
350 , p_data_app_rule_usage_id => l_data_app_rule_usage_id
351 , p_object_version_number => l_ovn );
352
353 END IF;
354
355 EXCEPTION WHEN NO_DATA_FOUND
356 THEN
357
358 hxc_data_app_rule_usages_api.create_data_app_rule_usages (
359 p_effective_date => sysdate
360 , p_approval_style_id => l_approval_style_id
361 , p_time_entry_rule_id => l_time_entry_rule_id
362 , p_time_recipient_id => l_time_recipient_id
363 , p_data_app_rule_usage_id => l_data_app_rule_usage_id
364 , p_object_version_number => l_ovn );
365
366 END load_daru_row;
367
368 END hxc_ter_upload_pkg;