1 PACKAGE BODY PY_FI_TAX_CARD AS
2 /* $Header: pyfitaxc.pkb 120.2.12000000.3 2007/02/27 06:16:49 dbehera noship $ */
3
4 g_package CONSTANT varchar2(33) := 'hr_fi_taxcard_api.';
5 g_debug BOOLEAN := hr_utility.debug_enabled;
6
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------------< ins >---------------------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This API will insert a tax and tax card entry for a Finland Assignment.
15 -- This API delegates to the create_element_entry procedure of the
16 -- pay_element_entry_api package.
17 --
18 -- Prerequisites:
19 -- The element link should exist for the given assignment
20 -- and business group.
21 --
22 -- In Parameters:
23 -- Name Reqd Type Description
24 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
25 -- p_effective_date Yes DATE The current effective date
26 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
27 -- p_person_id Yes VARCHAR2 Person ID for the record.
28 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
29 -- p_element_entry_id_tc Yes VARCHAR2 Element entry Id for Tax Card Element.
30 -- p_element_entry_id_t Yes VARCHAR2 Element entry Id for Tax Element.
31 -- p_taxcard_type VARCHAR2 Tax Card Type.
32 -- p_method_of_receipt VARCHAR2 Method of Receipt.
33 -- p_base_rate NUMBER Base Rate.
34 -- p_tax_municipality VARCHAR2 Tax Municipality
35 -- p_additional_rate NUMBER Additional Rate.
36 -- p_override_manual_upd VARCHAR2 Override Manual Update Flag.
37 -- p_previous_income NUMBER Previous Income.
38 -- p_yearly_income_limit NUMBER Yearly Income Limit.
39 -- p_date_returned DATE Date Returned.
40 -- p_registration_date DATE Registration Date.
41 -- p_lower_income_percentage Number Lower Income Percentage
42 -- p_primary_employment VARCHAR2 Primary Employment Flag.
43 -- p_extra_income_rate NUMBER Extra Income Rate.
44 -- p_extra_income_add_rate NUMBER Extra Income Additional Rate.
45 -- p_extra_income_limit NUMBER Extra Income Limit.
46 -- p_prev_extra_income NUMBER Previous Extra Income.
47 --
48 -- Post Success:
49 -- The API successfully inserts a tax card and/or tax entry.
50 --
51 -- Post Failure:
52 -- The API will raise an error.
53 --
54 -- Access Status:
55 -- Private. For Internal Development Use only.
56 --
57 -- {End Of Comments}
58 --
59 PROCEDURE ins (
60 p_legislation_code IN VARCHAR2
61 ,p_effective_date IN DATE
62 ,p_assignment_id IN VARCHAR2
63 ,p_person_id IN VARCHAR2
64 ,p_business_group_id IN VARCHAR2
65 ,p_element_entry_id_tc IN VARCHAR2
66 ,p_element_entry_id_t IN VARCHAR2
67 ,p_taxcard_type IN VARCHAR2 DEFAULT NULL
68 ,p_method_of_receipt IN VARCHAR2 DEFAULT NULL
69 ,p_base_rate IN NUMBER DEFAULT NULL
70 ,p_tax_municipality IN VARCHAR2 DEFAULT NULL
71 ,p_additional_rate IN NUMBER DEFAULT NULL
72 ,p_override_manual_upd IN VARCHAR2 DEFAULT NULL
73 ,p_previous_income IN NUMBER DEFAULT NULL
74 ,p_yearly_income_limit IN NUMBER DEFAULT NULL
75 ,p_date_returned IN DATE DEFAULT NULL
76 ,p_registration_date IN DATE DEFAULT NULL
77 ,p_lower_income_percentage IN NUMBER DEFAULT NULL
78 ,p_primary_employment IN VARCHAR2 DEFAULT NULL
79 ,p_extra_income_rate IN NUMBER DEFAULT NULL
80 ,p_extra_income_add_rate IN NUMBER DEFAULT NULL
81 ,p_extra_income_limit IN NUMBER DEFAULT NULL
82 ,p_prev_extra_income IN NUMBER DEFAULT NULL
83 ) IS
84 -- declarations here
85 l_proc varchar2(72) := g_package||'ins.';
86 l_primary_asg_id per_all_assignments_f.assignment_id%TYPE;
87
88 CURSOR cPrimaryAsg(asgid IN per_all_assignments_f.assignment_id%TYPE,
89 effective_date IN DATE) IS
90 SELECT
91 pasg.assignment_id
92 FROM
93 per_all_assignments_f pasg,
94 per_all_assignments_f asg
95 WHERE asg.assignment_id = asgid
96 AND fnd_date.canonical_to_date(effective_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
97 AND pasg.person_id = asg.person_id
98 AND pasg.primary_flag = 'Y'
99 AND fnd_date.canonical_to_date(effective_date) BETWEEN pasg.effective_start_date AND pasg.effective_end_date;
100 BEGIN
101
102 if g_debug then
103 hr_utility.set_location('Entering:'|| l_proc, 1);
104 end if;
105 -- Check if the assignment is a primary Assignment.
106 -- If 'Yes' then call insert_taxcard and then insert_tax
107 -- If 'No' then call only insert_tax.
108 IF is_primary_asg(p_assignment_id , p_effective_date) = true THEN
109 if g_debug then
110 hr_utility.set_location('Entering:'|| l_proc, 2);
111 end if;
112 -- Get Primary Assignment Id for the given asg id.
113 -- and pass it to the insert_taxcard procedure
114 OPEN cPrimaryAsg(p_assignment_id , p_effective_date);
115 FETCH cPrimaryAsg INTO l_primary_asg_id;
116 CLOSE cPrimaryAsg;
117 -- Call insert_taxcard procedure
118 insert_taxcard (
119 p_legislation_code => p_legislation_code
120 ,p_effective_date => p_effective_date
121 ,p_assignment_id => p_assignment_id
122 ,p_person_id => p_person_id
123 ,p_business_group_id => p_business_group_id
124 ,p_element_entry_id_tc => p_element_entry_id_tc
125 ,p_taxcard_type => p_taxcard_type
126 ,p_method_of_receipt => p_method_of_receipt
127 ,p_base_rate => p_base_rate
128 ,p_tax_municipality => p_tax_municipality
129 ,p_additional_rate => p_additional_rate
130 ,p_override_manual_upd => p_override_manual_upd
131 ,p_previous_income => p_previous_income
132 ,p_yearly_income_limit => p_yearly_income_limit
133 ,p_date_returned => fnd_date.canonical_to_date(p_date_returned)
134 ,p_registration_date => fnd_date.canonical_to_date(p_registration_date)
135 ,p_lower_income_percentage => p_lower_income_percentage);
136 if g_debug then
137 hr_utility.set_location('Leaving:'|| l_proc, 2);
138 end if;
139
140 if g_debug then
141 hr_utility.set_location('Entering:'|| l_proc, 3);
142 end if;
143 -- Now call insert_tax procedure
144 insert_tax (
145 p_legislation_code => p_legislation_code
146 ,p_effective_date => p_effective_date
147 ,p_assignment_id => p_assignment_id
148 ,p_person_id => p_person_id
149 ,p_business_group_id => p_business_group_id
150 ,p_element_entry_id_t => p_element_entry_id_t
151 ,p_primary_employment => p_primary_employment
152 ,p_extra_income_rate => p_extra_income_rate
153 ,p_extra_income_add_rate => p_extra_income_add_rate
154 ,p_extra_income_limit => p_extra_income_limit
155 ,p_prev_extra_income => p_prev_extra_income);
156 if g_debug then
157 hr_utility.set_location('Leaving:'|| l_proc, 3);
158 end if;
159 ELSIF is_primary_asg(p_assignment_id , p_effective_date) = false THEN
160 if g_debug then
161 hr_utility.set_location('Entering:'|| l_proc, 4);
162 end if;
163 insert_tax (
164 p_legislation_code => p_legislation_code
165 ,p_effective_date => p_effective_date
166 ,p_assignment_id => p_assignment_id
167 ,p_person_id => p_person_id
168 ,p_business_group_id => p_business_group_id
169 ,p_element_entry_id_t => p_element_entry_id_t
170 ,p_primary_employment => p_primary_employment
171 ,p_extra_income_rate => p_extra_income_rate
172 ,p_extra_income_add_rate => p_extra_income_add_rate
173 ,p_extra_income_limit => p_extra_income_limit
174 ,p_prev_extra_income => p_prev_extra_income);
175 if g_debug then
176 hr_utility.set_location('Leaving:'|| l_proc, 4);
177 end if;
178 END IF;
179 if g_debug then
180 hr_utility.set_location('Leaving:'|| l_proc, 1);
181 end if;
182 -- Do not COMMIT here. COMMIT should be done thru the OAF Application only.
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 RAISE;
187 END ins;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |----------------------< insert_taxcard >----------------------------------|
191 -- ----------------------------------------------------------------------------
192 -- {Start Of Comments}
193 --
194 -- Description:
195 -- This API will insert a tax card entry for a Finland Assignment.
196 -- This API delegates to the create_element_entry procedure of the
197 -- pay_element_entry_api package.
198 --
199 -- Prerequisites:
200 -- The element link should exist for the given assignment
201 -- and business group.
202 --
203 -- In Parameters:
204 -- Name Reqd Type Description
205 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
206 -- p_effective_date Yes DATE The current effective date
207 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
208 -- p_person_id Yes VARCHAR2 Person ID for the record.
209 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
210 -- p_element_entry_id_tc Yes VARCHAR2 Element entry Id for Tax Card Element.
211 -- p_element_entry_id_t Yes VARCHAR2 Element entry Id for Tax Element.
212 -- p_taxcard_type VARCHAR2 Tax Card Type.
213 -- p_method_of_receipt VARCHAR2 Method of Receipt.
214 -- p_base_rate NUMBER Base Rate.
215 -- p_tax_municipality VARCHAR2 Tax Municipality
216 -- p_additional_rate NUMBER Additional Rate.
217 -- p_override_manual_upd VARCHAR2 Override Manual Update Flag.
218 -- p_previous_income NUMBER Previous Income.
219 -- p_yearly_income_limit NUMBER Yearly Income Limit.
220 -- p_date_returned DATE Date Returned.
221 -- p_registration_date DATE Registration Date.
222 -- p_lower_income_percentage Number Lower Income Percentage
223
224 --
225 -- Post Success:
226 -- The API successfully inserts the tax card entry.
227 --
228 -- Post Failure:
229 -- The API will raise an error.
230 --
231 -- Access Status:
232 -- Private. For Internal Development Use only.
233 --
234 -- {End Of Comments}
235 --
236 PROCEDURE insert_taxcard (
237 p_legislation_code IN VARCHAR2
238 ,p_effective_date IN DATE
239 ,p_assignment_id IN VARCHAR2
240 ,p_person_id IN VARCHAR2
241 ,p_business_group_id IN VARCHAR2
242 ,p_element_entry_id_tc IN VARCHAR2
243 ,p_taxcard_type IN VARCHAR2 DEFAULT NULL
244 ,p_method_of_receipt IN VARCHAR2 DEFAULT NULL
245 ,p_base_rate IN NUMBER DEFAULT NULL
246 ,p_tax_municipality IN VARCHAR2 DEFAULT NULL
247 ,p_additional_rate IN NUMBER DEFAULT NULL
248 ,p_override_manual_upd IN VARCHAR2 DEFAULT NULL
249 ,p_previous_income IN NUMBER DEFAULT NULL
250 ,p_yearly_income_limit IN NUMBER DEFAULT NULL
251 ,p_date_returned IN DATE DEFAULT NULL
252 ,p_registration_date IN DATE DEFAULT NULL
253 ,p_lower_income_percentage IN NUMBER DEFAULT NULL
254 ) IS
255
256 -- Declarations here
257 l_start_date DATE;
258 l_end_date DATE;
259 l_warning BOOLEAN;
260 l_element_entry_id NUMBER(15);
261 l_ovn NUMBER(9);
262 l_element_link_id pay_element_links_f.element_link_id%TYPE;
263 l_element_type_id pay_element_types_f.element_type_id%TYPE;
264 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
265 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
266 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
267 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
268 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
269 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
270 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
271 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
272 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
273 l_input_value_id10 pay_input_values_f.input_value_id%TYPE;
274 l_input_value_id11 pay_input_values_f.input_value_id%TYPE;
275 l_proc varchar2(72) := g_package||'insert_taxcard.';
276
277 CURSOR input_values_csr IS
278 SELECT
279 et.element_type_id,
280 MIN(DECODE(iv.name, 'Tax Card Type', iv.input_value_id, null)) iv1,
281 MIN(DECODE(iv.name, 'Method of Receipt', iv.input_value_id, null)) iv2,
282 MIN(DECODE(iv.name, 'Base Rate', iv.input_value_id, null)) iv3,
283 MIN(DECODE(iv.name, 'Tax Municipality', iv.input_value_id, null)) iv4,
284 MIN(DECODE(iv.name, 'Additional Rate', iv.input_value_id, null)) iv5,
285 MIN(DECODE(iv.name, 'Override Manual Update', iv.input_value_id, null)) iv6,
286 MIN(DECODE(iv.name, 'Previous Income', iv.input_value_id, null)) iv7,
287 MIN(DECODE(iv.name, 'Yearly Income Limit', iv.input_value_id, null)) iv8,
288 MIN(DECODE(iv.name, 'Date Returned', iv.input_value_id, null)) iv9,
289 MIN(DECODE(iv.name, 'Registration Date', iv.input_value_id, null)) iv10,
290 MIN(DECODE(iv.name, 'Lower Income Percentage', iv.input_value_id, null)) iv11
291 FROM
292 pay_element_types_f et,
293 pay_input_values_f iv
294 WHERE et.element_name = 'Tax Card'
295 AND et.legislation_code = 'FI'
296 AND et.business_group_id is null
297 AND fnd_date.canonical_to_date(p_effective_date) BETWEEN
298 et.effective_start_date AND et.effective_end_date
299 AND iv.element_type_id = et.element_type_id
300 AND fnd_date.canonical_to_date(p_effective_date)
301 BETWEEN iv.effective_start_date AND iv.effective_end_date
302 GROUP BY
303 et.element_type_id;
304
305 CURSOR CSR_CHECK_FND_SESSION
306 is
307 select session_id
308 from fnd_sessions
309 where session_id = userenv('sessionid')
310 and effective_date = p_effective_date;
311 LR_CHECK_FND_SESSION CSR_CHECK_FND_SESSION%ROWTYPE;
312
313 BEGIN
314
315 if g_debug then
316 hr_utility.set_location('Entering:'|| l_proc, 1);
317 end if;
318
319 --Insert row into fnd_Sessions table.
320 OPEN CSR_CHECK_FND_SESSION;
321 FETCH CSR_CHECK_FND_SESSION INTO LR_CHECK_FND_SESSION;
322 IF CSR_CHECK_FND_SESSION%notfound
323 THEN
324 INSERT INTO fnd_sessions(session_id, effective_date) VALUES(userenv('sessionid'), p_effective_date);
325 END IF;
326
327 CLOSE CSR_CHECK_FND_SESSION;
328
329
330
331
332 if g_debug then
333 hr_utility.set_location('Entering:'|| l_proc, 2);
334 end if;
335 -- fetch element link id
336 l_element_link_id := pay_fi_tc_dp_upload.get_element_link_id(
337 p_assignment_id, p_business_group_id,
338 fnd_date.date_to_canonical(p_effective_date),
339 'Tax Card');
340 l_element_entry_id := p_element_entry_id_tc;
341 if g_debug then
342 hr_utility.set_location('Leaving:'|| l_proc, 2);
343 end if;
344
345 if g_debug then
346 hr_utility.set_location('Entering:'|| l_proc, 3);
347 end if;
348 -- fetch all input value id's
349 OPEN input_values_csr;
350 FETCH input_values_csr INTO l_element_type_id, l_input_value_id1, l_input_value_id2,
351 l_input_value_id3, l_input_value_id4, l_input_value_id5, l_input_value_id6,
352 l_input_value_id7, l_input_value_id8,l_input_value_id9,l_input_value_id10,l_input_value_id11;
353 CLOSE input_values_csr;
354
355 if g_debug then
356 hr_utility.set_location('Leaving:'|| l_proc, 3);
357 end if;
358 if g_debug then
359 hr_utility.set_location('Entering:'|| l_proc, 4);
360 end if;
361 -- insert records into pay_element_entries_f and pay_element_entry_values_f
362 pay_element_entry_api.create_element_entry
363 (p_effective_date => p_effective_date
364 ,p_business_group_id => p_business_group_id
365 ,p_assignment_id => p_assignment_id
366 ,p_element_link_id => l_element_link_id
367 ,p_entry_type => 'E'
368 ,p_input_value_id1 => l_input_value_id1
369 ,p_input_value_id2 => l_input_value_id2
370 ,p_input_value_id3 => l_input_value_id3
371 ,p_input_value_id4 => l_input_value_id4
372 ,p_input_value_id5 => l_input_value_id5
373 ,p_input_value_id6 => l_input_value_id6
374 ,p_input_value_id7 => l_input_value_id7
375 ,p_input_value_id8 => l_input_value_id8
376 ,p_input_value_id9 => l_input_value_id9
377 ,p_input_value_id10 => l_input_value_id10
378 ,p_input_value_id11 => l_input_value_id11
379 ,p_entry_value1 => p_taxcard_type
380 ,p_entry_value2 => p_method_of_receipt
381 ,p_entry_value3 => p_base_rate
382 ,p_entry_value4 => p_tax_municipality
383 ,p_entry_value5 => p_additional_rate
384 ,p_entry_value6 => p_override_manual_upd
385 ,p_entry_value7 => p_previous_income
386 ,p_entry_value8 => p_yearly_income_limit
387 ,p_entry_value9 => fnd_date.canonical_to_date(p_date_returned)
388 ,p_entry_value10 => fnd_date.canonical_to_date(p_registration_date)
389 ,p_entry_value11 => p_lower_income_percentage
390 ,p_effective_start_date => l_start_date
391 ,p_effective_end_date => l_end_date
392 ,p_element_entry_id => l_element_entry_id
393 ,p_object_version_number => l_ovn
394 ,p_create_warning => l_warning
395 );
396
397 if g_debug then
398 hr_utility.set_location('Leaving:'|| l_proc, 4);
399 end if;
400
401 if g_debug then
402 hr_utility.set_location('Leaving:'|| l_proc, 1);
403 end if;
404 -- Do not COMMIT here. COMMIT should be done thru the OAF Application only.
405 EXCEPTION
406 WHEN OTHERS THEN
407 RAISE;
408 END insert_taxcard;
409 --
410 -- ----------------------------------------------------------------------------
411 -- |------------------------< insert_tax >-------------------------------------|
412 -- ----------------------------------------------------------------------------
413 -- {Start Of Comments}
414 --
415 -- Description:
416 -- This API will insert a tax entry for a Finland Assignment.
417 -- This API delegates to the create_element_entry procedure of the
418 -- pay_element_entry_api package.
419 --
420 -- Prerequisites:
421 -- The element link should exist for the given assignment
422 -- and business group.
423 --
424 -- In Parameters:
425 -- Name Reqd Type Description
426 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
427 -- p_effective_date Yes DATE The current effective date
428 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
429 -- p_person_id Yes VARCHAR2 Person ID for the record.
430 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
431 -- p_element_entry_id_tc Yes VARCHAR2 Element entry Id for Tax Card Element.
432 -- p_element_entry_id_t Yes VARCHAR2 Element entry Id for Tax Element.
433 -- p_primary_employment VARCHAR2 Primary Employment Flag.
434 -- p_extra_income_rate NUMBER Extra Income Rate.
435 -- p_extra_income_add_rate NUMBER Extra Income Additional Rate.
436 -- p_extra_income_limit NUMBER Extra Income Limit.
437 -- p_prev_extra_income NUMBER Previous Extra Income.
438 --
439 -- Post Success:
440 -- The API successfully inserts the tax card entry.
441 --
442 -- Post Failure:
443 -- The API will raise an error.
444 --
445 -- Access Status:
446 -- Private. For Internal Development Use only.
447 --
448 -- {End Of Comments}
449 --
450 PROCEDURE insert_tax (
451 p_legislation_code IN VARCHAR2
452 ,p_effective_date IN DATE
453 ,p_assignment_id IN VARCHAR2
454 ,p_person_id IN VARCHAR2
455 ,p_business_group_id IN VARCHAR2
456 ,p_element_entry_id_t IN VARCHAR2
457 ,p_primary_employment IN VARCHAR2 DEFAULT NULL
458 ,p_extra_income_rate IN NUMBER DEFAULT NULL
459 ,p_extra_income_add_rate IN NUMBER DEFAULT NULL
460 ,p_extra_income_limit IN NUMBER DEFAULT NULL
461 ,p_prev_extra_income IN NUMBER DEFAULT NULL
462 ) IS
463
464 -- Declarations here
465 l_start_date DATE;
466 l_end_date DATE;
467 l_warning BOOLEAN;
468 l_element_entry_id NUMBER(15);
469 l_ovn NUMBER(9);
470 l_element_link_id pay_element_links_f.element_link_id%TYPE;
471 l_element_type_id pay_element_types_f.element_type_id%TYPE;
472 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
473 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
474 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
475 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
476 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
477
478 CURSOR input_values_csr IS
479 SELECT
480 et.element_type_id,
481 MIN(DECODE(iv.name, 'Primary Employment', iv.input_value_id, null)) iv1,
482 MIN(DECODE(iv.name, 'Extra Income Rate', iv.input_value_id, null)) iv2,
483 MIN(DECODE(iv.name, 'Extra Income Additional Rate', iv.input_value_id, null)) iv3,
484 MIN(DECODE(iv.name, 'Extra Income Limit', iv.input_value_id, null)) iv4,
485 MIN(DECODE(iv.name, 'Previous Extra Income', iv.input_value_id, null)) iv5
486 FROM
487 pay_element_types_f et,
488 pay_input_values_f iv
489 WHERE et.element_name = 'Tax'
490 AND et.legislation_code = 'FI'
491 AND et.business_group_id is null
492 AND fnd_date.canonical_to_date(p_effective_date)
493 BETWEEN et.effective_start_date AND et.effective_end_date
494 AND iv.element_type_id = et.element_type_id
495 AND fnd_date.canonical_to_date(p_effective_date)
496 BETWEEN iv.effective_start_date AND iv.effective_end_date
497 GROUP BY
498 et.element_type_id;
499
500 CURSOR CSR_CHECK_FND_SESSION
501 is
502 select session_id
503 from fnd_sessions
504 where session_id = userenv('sessionid')
505 and effective_date = p_effective_date;
506 LR_CHECK_FND_SESSION CSR_CHECK_FND_SESSION%ROWTYPE;
507
508 CURSOR cIsElementAttached(l_asgid IN pay_element_entries_f.assignment_id%TYPE,
509 l_business_grp_id IN pay_element_links_f.business_group_id%TYPE,
510 l_element_name IN pay_element_types_f.element_name%TYPE,
511 l_effective_date IN VARCHAR2) IS
512 SELECT pee.element_entry_id,pee.OBJECT_VERSION_NUMBER EE_OVN
513 FROM pay_element_types_f pet ,
514 pay_element_links_f pel ,
515 pay_element_entries_f pee
516 WHERE pet.element_name = l_element_name
517 AND pet.legislation_code = 'FI'
518 AND pet.business_group_id IS NULL
519 AND fnd_date.chardate_to_date(l_effective_date)
520 BETWEEN pet.effective_start_date AND pet.effective_end_date
521 AND pel.element_type_id = pet.element_type_id
522 AND pel.business_group_id = l_business_grp_id
523 AND fnd_date.chardate_to_date(l_effective_date)
524 BETWEEN pel.effective_start_date AND pel.effective_end_date
525 AND pee.element_link_id = pel.element_link_id
526 AND fnd_date.chardate_to_date(l_effective_date)
527 BETWEEN pee.effective_start_date AND pee.effective_end_date
528 AND pee.assignment_id = l_asgid;
529 lrIsElementAttached cIsElementAttached%ROWTYPE;
530 l_proc varchar2(72) := g_package||'insert_tax.';
531 l_datetrack_update_mode VARCHAR2(255);
532 l_record_started_today BOOLEAN;
533 BEGIN
534 --hr_utility.trace_on(NULL,'TELL');
535 if g_debug then
536 hr_utility.set_location('Entering:'|| l_proc, 1);
537 end if;
538 --Insert row into fnd_Sessions table.
539 OPEN CSR_CHECK_FND_SESSION;
540 FETCH CSR_CHECK_FND_SESSION INTO LR_CHECK_FND_SESSION;
541 IF CSR_CHECK_FND_SESSION%notfound
542 THEN
543 INSERT INTO fnd_sessions(session_id, effective_date) VALUES(userenv('sessionid'), p_effective_date);
544 END IF;
545
546 CLOSE CSR_CHECK_FND_SESSION;
547
548 if g_debug then
549 hr_utility.set_location('Entering:'|| l_proc, 2);
550 end if;
551 -- fetch element link id
552 l_element_link_id := pay_fi_tc_dp_upload.get_element_link_id(
553 p_assignment_id, p_business_group_id,
554 fnd_date.date_to_canonical(p_effective_date),
555 'Tax');
556 l_element_entry_id := p_element_entry_id_t;
557 if g_debug then
558 hr_utility.set_location('Leaving:'|| l_proc, 2);
559 end if;
560
561 if g_debug then
562 hr_utility.set_location('Entering:'|| l_proc, 3);
563 end if;
564 -- fetch all input value id's
565 OPEN input_values_csr;
566 FETCH input_values_csr INTO l_element_type_id, l_input_value_id1, l_input_value_id2,
567 l_input_value_id3, l_input_value_id4, l_input_value_id5;
568 hr_utility.set_location('Entering:'|| l_element_type_id, 1);
569 hr_utility.set_location('Entering:'|| l_input_value_id1, 1);
570 hr_utility.set_location('Entering:'|| l_input_value_id2, 1);
571 hr_utility.set_location('Entering:'|| l_input_value_id3, 1);
572 hr_utility.set_location('Entering:'|| l_input_value_id4, 1);
573 hr_utility.set_location('Entering:'|| l_input_value_id5, 1);
574
575 CLOSE input_values_csr;
576
577 if g_debug then
578 hr_utility.set_location('Leaving:'|| l_proc, 3);
579 end if;
580
581 if g_debug then
582 hr_utility.set_location('Entering:'|| l_proc, 4);
583 end if;
584 -- insert records into pay_element_entries_f and pay_element_entry_values_f
585 OPEN cIsElementAttached(p_assignment_id , p_business_group_id , 'Tax' , p_effective_date);
586 FETCH cIsElementAttached INTO lrIsElementAttached;
587 IF cIsElementAttached%NOTFOUND THEN
588
589 pay_element_entry_api.create_element_entry
590 (p_effective_date => p_effective_date
591 ,p_business_group_id => p_business_group_id
592 ,p_assignment_id => p_assignment_id
593 ,p_element_link_id => l_element_link_id
594 ,p_entry_type => 'E'
595 ,p_input_value_id1 => l_input_value_id1
596 ,p_input_value_id2 => l_input_value_id2
597 ,p_input_value_id3 => l_input_value_id3
598 ,p_input_value_id4 => l_input_value_id4
599 ,p_input_value_id5 => l_input_value_id5
600 ,p_entry_value1 => p_primary_employment
601 ,p_entry_value2 => p_extra_income_rate
602 ,p_entry_value3 => p_extra_income_add_rate
603 ,p_entry_value4 => p_extra_income_limit
604 ,p_entry_value5 => p_prev_extra_income
605 ,p_effective_start_date => l_start_date
606 ,p_effective_end_date => l_end_date
607 ,p_element_entry_id => l_element_entry_id
608 ,p_object_version_number => l_ovn
609 ,p_create_warning => l_warning);
610 ELSE
611 hr_utility.set_location('is element attached true:', 3);
612
613 -- Check if the record started today. If yes then
614 -- Change the datetrack mode to correction.
615 l_record_started_today := is_element_started_today(p_assignment_id, 'Tax', p_effective_date);
616 l_datetrack_update_mode := 'CORRECTION'; -- doing this bcoz the user specified mode is to be given prio.
617 if l_record_started_today = true then
618 l_datetrack_update_mode := 'CORRECTION';
619 end if;
620
621 -- update Tax Element
622 pay_element_entry_api.update_element_entry
623 (p_validate => FALSE
624 ,p_object_version_number => lrIsElementAttached.EE_OVN
625 ,p_update_warning => l_warning
626 ,p_datetrack_update_mode => l_datetrack_update_mode
627 ,p_effective_date => p_effective_date
628 ,p_business_group_id => p_business_group_id
629 ,p_input_value_id1 => l_input_value_id1
630 ,p_input_value_id2 => l_input_value_id2
631 ,p_input_value_id3 => l_input_value_id3
632 ,p_input_value_id4 => l_input_value_id4
633 ,p_input_value_id5 => l_input_value_id5
634 ,p_entry_value1 => p_primary_employment
635 ,p_entry_value2 => p_extra_income_rate
636 ,p_entry_value3 => p_extra_income_add_rate
637 ,p_entry_value4 => p_extra_income_limit
638 ,p_entry_value5 => p_prev_extra_income
639 ,p_effective_start_date => l_start_date
640 ,p_effective_end_date => l_end_date
641 ,p_element_entry_id => lrIsElementAttached.element_entry_id
642 ,p_cost_allocation_keyflex_id => hr_api.g_number
643 ,p_updating_action_id => hr_api.g_number
644 ,p_original_entry_id => hr_api.g_number
645 ,p_creator_type => hr_api.g_varchar2
646 ,p_comment_id => hr_api.g_number
647 ,p_creator_id => hr_api.g_number
648 ,p_reason => hr_api.g_varchar2
649 ,p_subpriority => hr_api.g_number
650 ,p_date_earned => hr_api.g_date
651 ,p_personal_payment_method_id => hr_api.g_number
652 ,p_attribute_category => hr_api.g_varchar2
653 ,p_attribute1 => hr_api.g_varchar2
654 ,p_attribute2 => hr_api.g_varchar2
655 ,p_attribute3 => hr_api.g_varchar2
656 ,p_attribute4 => hr_api.g_varchar2
657 ,p_attribute5 => hr_api.g_varchar2
658 ,p_attribute6 => hr_api.g_varchar2
659 ,p_attribute7 => hr_api.g_varchar2
660 ,p_attribute8 => hr_api.g_varchar2
661 ,p_attribute9 => hr_api.g_varchar2
662 ,p_attribute10 => hr_api.g_varchar2
663 ,p_attribute11 => hr_api.g_varchar2
664 ,p_attribute12 => hr_api.g_varchar2
665 ,p_attribute13 => hr_api.g_varchar2
666 ,p_attribute14 => hr_api.g_varchar2
667 ,p_attribute15 => hr_api.g_varchar2
668 ,p_attribute16 => hr_api.g_varchar2
669 ,p_attribute17 => hr_api.g_varchar2
670 ,p_attribute18 => hr_api.g_varchar2
671 ,p_attribute19 => hr_api.g_varchar2
672 ,p_attribute20 => hr_api.g_varchar2
673 ,p_updating_action_type => hr_api.g_varchar2
674 ,p_entry_information_category => hr_api.g_varchar2
675 ,p_entry_information1 => hr_api.g_varchar2
676 ,p_entry_information2 => hr_api.g_varchar2
677 ,p_entry_information3 => hr_api.g_varchar2
678 ,p_entry_information4 => hr_api.g_varchar2
679 ,p_entry_information5 => hr_api.g_varchar2
680 ,p_entry_information6 => hr_api.g_varchar2
681 ,p_entry_information7 => hr_api.g_varchar2
682 ,p_entry_information8 => hr_api.g_varchar2
683 ,p_entry_information9 => hr_api.g_varchar2
684 ,p_entry_information10 => hr_api.g_varchar2
685 ,p_entry_information11 => hr_api.g_varchar2
686 ,p_entry_information12 => hr_api.g_varchar2
687 ,p_entry_information13 => hr_api.g_varchar2
688 ,p_entry_information14 => hr_api.g_varchar2
689 ,p_entry_information15 => hr_api.g_varchar2
690 ,p_entry_information16 => hr_api.g_varchar2
691 ,p_entry_information17 => hr_api.g_varchar2
692 ,p_entry_information18 => hr_api.g_varchar2
693 ,p_entry_information19 => hr_api.g_varchar2
694 ,p_entry_information20 => hr_api.g_varchar2
695 ,p_entry_information21 => hr_api.g_varchar2
696 ,p_entry_information22 => hr_api.g_varchar2
697 ,p_entry_information23 => hr_api.g_varchar2
698 ,p_entry_information24 => hr_api.g_varchar2
699 ,p_entry_information25 => hr_api.g_varchar2
700 ,p_entry_information26 => hr_api.g_varchar2
701 ,p_entry_information27 => hr_api.g_varchar2
702 ,p_entry_information28 => hr_api.g_varchar2
703 ,p_entry_information29 => hr_api.g_varchar2
704 ,p_entry_information30 => hr_api.g_varchar2);
705 ---------------------------------------------
706 hr_utility.set_location('EE a :'|| lrIsElementAttached.element_entry_id, 1);
707 END IF;
708 CLOSE cIsElementAttached;
709
710 if g_debug then
711 hr_utility.set_location('Leaving:'|| l_proc, 4);
712 end if;
713
714 if g_debug then
715 hr_utility.set_location('Leaving:'|| l_proc, 1);
716 end if;
717 -- Do not COMMIT here. COMMIT should be done thru the OAF Application only.
718 EXCEPTION
719 WHEN OTHERS THEN
720 RAISE;
721 END insert_tax;
722 --
723 -- ----------------------------------------------------------------------------
724 -- |--------------------------------< upd >-----------------------------------|
725 -- ----------------------------------------------------------------------------
726 -- {Start Of Comments}
727 --
728 -- Description:
729 -- This API will update a tax and tax card entry for a Finland Assignment.
730 -- This API delegates to the update_element_entry procedure of the
731 -- pay_element_entry_api package.
732 --
733 -- Prerequisites:
734 -- The element entry (of element type 'Tax Card' and 'Tax) and the
735 -- corresponding element link should exist for the given assignment
736 -- and business group.
737 --
738 -- In Parameters:
739 -- Name Reqd Type Description
740 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
741 -- p_effective_date Yes DATE The current effective date
742 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
743 -- p_person_id Yes VARCHAR2 Person ID for the record.
744 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
745 -- p_element_entry_id_tc Yes VARCHAR2 Element entry Id for Tax Card Element.
746 -- p_element_entry_id_t Yes VARCHAR2 Element entry Id for Tax Element.
747 -- p_taxcard_type VARCHAR2 Tax Card Type.
748 -- p_method_of_receipt VARCHAR2 Method of Receipt.
749 -- p_base_rate NUMBER Base Rate.
750 -- p_tax_municipality VARCHAR2 Tax Municipality
751 -- p_additional_rate NUMBER Additional Rate.
752 -- p_override_manual_upd VARCHAR2 Override Manual Update Flag.
753 -- p_previous_income NUMBER Previous Income.
754 -- p_yearly_income_limit NUMBER Yearly Income Limit.
755 -- p_date_returned DATE Date Returned.
756 -- p_registration_date DATE Registration Date.
757 -- p_lower_income_percentage NUMBER Lower Income Percentage
758 -- p_primary_employment VARCHAR2 Primary Employment Flag.
759 -- p_extra_income_rate NUMBER Extra Income Rate.
760 -- p_extra_income_add_rate NUMBER Extra Income Additional Rate.
761 -- p_extra_income_limit NUMBER Extra Income Limit.
762 -- p_prev_extra_income NUMBER Previous Extra Income.
763 -- p_input_value_id1 VARCHAR2 Input Value Id for Entry 1
764 -- p_input_value_id2 VARCHAR2 Input Value Id for Entry 2
765 -- p_input_value_id3 VARCHAR2 Input Value Id for Entry 3
766 -- p_input_value_id4 VARCHAR2 Input Value Id for Entry 4
767 -- p_input_value_id5 VARCHAR2 Input Value Id for Entry 5
768 -- p_input_value_id6 VARCHAR2 Input Value Id for Entry 6
769 -- p_input_value_id7 VARCHAR2 Input Value Id for Entry 7
770 -- p_input_value_id8 VARCHAR2 Input Value Id for Entry 8
771 -- p_input_value_id9 VARCHAR2 Input Value Id for Entry 9
772 -- p_input_value_id10 VARCHAR2 Input Value Id for Entry 10
773 -- p_input_value_id11 VARCHAR2 Input Value Id for Entry 11
774 -- p_input_value_id12 VARCHAR2 Input Value Id for Entry 12
775 -- p_input_value_id13 VARCHAR2 Input Value Id for Entry 13
776 -- p_input_value_id14 VARCHAR2 Input Value Id for Entry 14
777 -- p_input_value_id15 VARCHAR2 Input Value Id for Entry 15
778 -- p_input_value_id16 VARCHAR2 Input Value Id for Entry 16
779 -- p_datetrack_update_mode VARCHAR2 The date track mode.
780 -- p_object_version_number_tc VARCHAR2 Object Version Number for Tax Card.
781 -- p_object_version_number_t VARCHAR2 Object Version Number for Tax.
782 --
783 -- Post Success:
784 -- The API successfully updates the tax card and/or tax entry.
785 --
786 -- Post Failure:
787 -- The API will raise an error.
788 --
789 -- Access Status:
790 -- Private. For Internal Development Use only.
791 --
792 -- {End Of Comments}
793 --
794 PROCEDURE upd (
795 p_legislation_code IN VARCHAR2
796 ,p_effective_date IN DATE
797 ,p_assignment_id IN VARCHAR2
798 ,p_person_id IN VARCHAR2
799 ,p_business_group_id IN VARCHAR2
800 ,p_element_entry_id_tc IN VARCHAR2
801 ,p_element_entry_id_t IN VARCHAR2
802 ,p_taxcard_type IN VARCHAR2 DEFAULT NULL
803 ,p_method_of_receipt IN VARCHAR2 DEFAULT NULL
804 ,p_base_rate IN NUMBER DEFAULT NULL
805 ,p_tax_municipality IN VARCHAR2 DEFAULT NULL
806 ,p_additional_rate IN NUMBER DEFAULT NULL
807 ,p_override_manual_upd IN VARCHAR2 DEFAULT NULL
808 ,p_previous_income IN NUMBER DEFAULT NULL
809 ,p_yearly_income_limit IN NUMBER DEFAULT NULL
810 ,p_date_returned IN DATE DEFAULT NULL
811 ,p_registration_date IN DATE DEFAULT NULL
812 ,p_lower_income_percentage IN NUMBER DEFAULT NULL
813 ,p_primary_employment IN VARCHAR2 DEFAULT NULL
814 ,p_extra_income_rate IN NUMBER DEFAULT NULL
815 ,p_extra_income_add_rate IN NUMBER DEFAULT NULL
816 ,p_extra_income_limit IN NUMBER DEFAULT NULL
817 ,p_prev_extra_income IN NUMBER DEFAULT NULL
818 ,p_input_value_id1 IN VARCHAR2 DEFAULT NULL
819 ,p_input_value_id2 IN VARCHAR2 DEFAULT NULL
820 ,p_input_value_id3 IN VARCHAR2 DEFAULT NULL
821 ,p_input_value_id4 IN VARCHAR2 DEFAULT NULL
822 ,p_input_value_id5 IN VARCHAR2 DEFAULT NULL
823 ,p_input_value_id6 IN VARCHAR2 DEFAULT NULL
824 ,p_input_value_id7 IN VARCHAR2 DEFAULT NULL
825 ,p_input_value_id8 IN VARCHAR2 DEFAULT NULL
826 ,p_input_value_id9 IN VARCHAR2 DEFAULT NULL
827 ,p_input_value_id10 IN VARCHAR2 DEFAULT NULL
828 ,p_input_value_id11 IN VARCHAR2 DEFAULT NULL
829 ,p_input_value_id12 IN VARCHAR2 DEFAULT NULL
830 ,p_input_value_id13 IN VARCHAR2 DEFAULT NULL
831 ,p_input_value_id14 IN VARCHAR2 DEFAULT NULL
832 ,p_input_value_id15 IN VARCHAR2 DEFAULT NULL
833 ,p_input_value_id16 IN VARCHAR2 DEFAULT NULL
834 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
835 ,p_object_version_number_tc IN VARCHAR2
836 ,p_object_version_number_t IN VARCHAR2 DEFAULT NULL
837 ) IS
838 --declarations here
839 l_proc varchar2(72) := g_package||'upd.';
840 l_primary_asg_id per_all_assignments_f.assignment_id%TYPE;
841
842 CURSOR cPrimaryAsg(asgid IN per_all_assignments_f.assignment_id%TYPE,
843 effective_date IN VARCHAR2) IS
844 SELECT
845 pasg.assignment_id
846 FROM
847 per_all_assignments_f pasg,
848 per_all_assignments_f asg
849 WHERE asg.assignment_id = asgid
850 AND fnd_date.canonical_to_date(effective_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
851 AND pasg.person_id = asg.person_id
852 AND pasg.primary_flag = 'Y'
853 AND fnd_date.canonical_to_date(effective_date) BETWEEN pasg.effective_start_date AND pasg.effective_end_date;
854 l_datetrack_update_mode VARCHAR2(255);
855 l_record_started_today BOOLEAN;
856 BEGIN
857 l_datetrack_update_mode := p_datetrack_update_mode;
858 -- hr_utility.trace_on(null,'T123');
859 if g_debug then
860 hr_utility.set_location('Entering:'|| l_proc, 1);
861 end if;
862 -- Check if the assignment is a primary Assignment.
863 -- If 'Yes' then call update_taxcard and then update_tax
864 -- If 'No' then call only update_tax.
865 IF is_primary_asg(p_assignment_id , p_effective_date) = true THEN
866 if g_debug then
867 hr_utility.set_location('Entering:'|| l_proc, 2);
868 end if;
869 -- Get Primary Assignment Id for the given asg id.
870 -- and pass it to the update_taxcard procedure
871 OPEN cPrimaryAsg(p_assignment_id , p_effective_date);
872 FETCH cPrimaryAsg INTO l_primary_asg_id;
873 CLOSE cPrimaryAsg;
874
875 -- Check if the record started today. If yes then
876 -- Change the datetrack mode to correction.
877 l_record_started_today := is_element_started_today(p_assignment_id, 'Tax Card', p_effective_date);
878 l_datetrack_update_mode := p_datetrack_update_mode; -- doing this bcoz the user specified mode is to be given prio.
879 if l_record_started_today = true then
880 l_datetrack_update_mode := 'CORRECTION';
881 end if;
882
883 -- Call update_taxcard procedure
884 IF is_element_attached(p_assignment_id, p_business_group_id, 'Tax Card', p_effective_date) = true THEN
885 update_taxcard (
886 p_legislation_code => p_legislation_code
887 ,p_effective_date => p_effective_date
888 ,p_assignment_id => p_assignment_id
889 ,p_person_id => p_person_id
890 ,p_business_group_id => p_business_group_id
891 ,p_element_entry_id_tc => p_element_entry_id_tc
892 ,p_taxcard_type => p_taxcard_type
893 ,p_method_of_receipt => p_method_of_receipt
894 ,p_base_rate => p_base_rate
895 ,p_tax_municipality => p_tax_municipality
896 ,p_additional_rate => p_additional_rate
897 ,p_override_manual_upd => p_override_manual_upd
898 ,p_previous_income => p_previous_income
899 ,p_yearly_income_limit => p_yearly_income_limit
900 ,p_date_returned => p_date_returned
901 ,p_registration_date => p_registration_date
902 ,p_lower_income_percentage => p_lower_income_percentage
903 ,p_input_value_id1 => p_input_value_id1
904 ,p_input_value_id2 => p_input_value_id2
905 ,p_input_value_id3 => p_input_value_id3
906 ,p_input_value_id4 => p_input_value_id4
907 ,p_input_value_id5 => p_input_value_id5
908 ,p_input_value_id6 => p_input_value_id6
909 ,p_input_value_id7 => p_input_value_id7
910 ,p_input_value_id8 => p_input_value_id8
911 ,p_input_value_id9 => p_input_value_id9
912 ,p_input_value_id10 => p_input_value_id10
913 ,p_input_value_id11 => p_input_value_id11
914 ,p_datetrack_update_mode => l_datetrack_update_mode
915 ,p_object_version_number => p_object_version_number_tc);
916 ELSE
917 insert_taxcard ( p_legislation_code => p_legislation_code
918 ,p_effective_date => p_effective_date
919 ,p_assignment_id => p_assignment_id
920 ,p_person_id => p_person_id
921 ,p_business_group_id => p_business_group_id
922 ,p_element_entry_id_tc => p_element_entry_id_tc
923 ,p_taxcard_type => p_taxcard_type
924 ,p_method_of_receipt => p_method_of_receipt
925 ,p_base_rate => p_base_rate
926 ,p_tax_municipality => p_tax_municipality
927 ,p_additional_rate => p_additional_rate
928 ,p_override_manual_upd => p_override_manual_upd
929 ,p_previous_income => p_previous_income
930 ,p_yearly_income_limit => p_yearly_income_limit
931 ,p_date_returned => fnd_date.canonical_to_date(p_date_returned)
932 ,p_registration_date => fnd_date.canonical_to_date(p_registration_date)
933 ,p_lower_income_percentage => p_lower_income_percentage);
934
935
936 END IF;
937 if g_debug then
938 hr_utility.set_location('Leaving:'|| l_proc, 2);
939 end if;
940 END IF;
941
942 -- Update Tax Element if tax element already present. Otherwise insert
943 -- the Tax Element.
944 if g_debug then
945 hr_utility.set_location('Entering:'|| l_proc, 3);
946 end if;
947 IF is_element_attached(p_assignment_id, p_business_group_id, 'Tax', p_effective_date) = true THEN
948 hr_utility.set_location('is element attached true:', 3);
949
950 -- Check if the record started today. If yes then
951 -- Change the datetrack mode to correction.
952 l_record_started_today := is_element_started_today(p_assignment_id, 'Tax', p_effective_date);
953 l_datetrack_update_mode := p_datetrack_update_mode; -- doing this bcoz the user specified mode is to be given prio.
954 if l_record_started_today = true then
955 l_datetrack_update_mode := 'CORRECTION';
956 end if;
957
958 -- update Tax Element
959 update_tax (
960 p_legislation_code => p_legislation_code
961 ,p_effective_date => p_effective_date
962 ,p_assignment_id => p_assignment_id
963 ,p_person_id => p_person_id
964 ,p_business_group_id => p_business_group_id
965 ,p_element_entry_id_t => p_element_entry_id_t
966 ,p_primary_employment => p_primary_employment
967 ,p_extra_income_rate => p_extra_income_rate
968 ,p_extra_income_add_rate => p_extra_income_add_rate
969 ,p_extra_income_limit => p_extra_income_limit
970 ,p_prev_extra_income => p_prev_extra_income
971 ,p_input_value_id1 => p_input_value_id12
972 ,p_input_value_id2 => p_input_value_id13
973 ,p_input_value_id3 => p_input_value_id14
974 ,p_input_value_id4 => p_input_value_id15
975 ,p_input_value_id5 => p_input_value_id16
976 ,p_datetrack_update_mode => p_datetrack_update_mode
977 ,p_object_version_number => p_object_version_number_t);
978 ELSIF is_element_attached(p_assignment_id, p_business_group_id, 'Tax', p_effective_date) = false THEN
979 -- insert Tax Element.
980 hr_utility.set_location('is element attached false:', 3);
981 insert_tax (
982 p_legislation_code => p_legislation_code
983 ,p_effective_date => p_effective_date
984 ,p_assignment_id => p_assignment_id
985 ,p_person_id => p_person_id
986 ,p_business_group_id => p_business_group_id
987 ,p_element_entry_id_t => p_element_entry_id_t
988 ,p_primary_employment => p_primary_employment
989 ,p_extra_income_rate => p_extra_income_rate
990 ,p_extra_income_add_rate => p_extra_income_add_rate
991 ,p_extra_income_limit => p_extra_income_limit
992 ,p_prev_extra_income => p_prev_extra_income);
993 END IF;
994 if g_debug then
995 hr_utility.set_location('Leaving:'|| l_proc, 3);
996 end if;
997
998 if g_debug then
999 hr_utility.set_location('Leaving:'|| l_proc, 1);
1000 end if;
1001 -- Do not COMMIT here. COMMIT should be done thru the OAF Application only.
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 RAISE;
1006 END upd;
1007 --
1008 -- ----------------------------------------------------------------------------
1009 -- |----------------------< update_taxcard >----------------------------------|
1010 -- ----------------------------------------------------------------------------
1011 -- {Start Of Comments}
1012 --
1013 -- Description:
1014 -- This API will update a tax card entry for a Finland Assignment.
1015 -- This API delegates to the update_element_entry procedure of the
1016 -- pay_element_entry_api package.
1017 --
1018 -- Prerequisites:
1019 -- The element entry (of element type 'Tax Card' ) and the
1020 -- corresponding element link should exist for the given assignment
1021 -- and business group.
1022 --
1023 -- In Parameters:
1024 -- Name Reqd Type Description
1025 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
1026 -- p_effective_date Yes DATE The current effective date
1027 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
1028 -- p_person_id Yes VARCHAR2 Person ID for the record.
1029 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
1030 -- p_element_entry_id_tc Yes VARCHAR2 Element entry Id for Tax Card Element.
1031 -- p_taxcard_type VARCHAR2 Tax Card Type.
1032 -- p_method_of_receipt VARCHAR2 Method of Receipt.
1033 -- p_base_rate NUMBER Base Rate.
1034 -- p_tax_municipality VARCHAR2 Tax Municipality
1035 -- p_additional_rate NUMBER Additional Rate.
1036 -- p_override_manual_upd VARCHAR2 Override Manual Update Flag.
1037 -- p_previous_income NUMBER Previous Income.
1038 -- p_yearly_income_limit NUMBER Yearly Income Limit.
1039 -- p_date_returned DATE Date Returned.
1040 -- p_registration_date DATE Registration Date.
1041 -- p_lower_income_percentage Number Lower Income Percentage
1042 -- p_input_value_id1 VARCHAR2 Input Value Id for Entry 1
1043 -- p_input_value_id2 VARCHAR2 Input Value Id for Entry 2
1044 -- p_input_value_id3 VARCHAR2 Input Value Id for Entry 3
1045 -- p_input_value_id4 VARCHAR2 Input Value Id for Entry 4
1046 -- p_input_value_id5 VARCHAR2 Input Value Id for Entry 5
1047 -- p_input_value_id6 VARCHAR2 Input Value Id for Entry 6
1048 -- p_input_value_id7 VARCHAR2 Input Value Id for Entry 7
1049 -- p_input_value_id8 VARCHAR2 Input Value Id for Entry 8
1050 -- p_input_value_id9 VARCHAR2 Input Value Id for Entry 9
1051 -- p_input_value_id10 VARCHAR2 Input Value Id for Entry 10
1052 -- p_input_value_id11 VARCHAR2 Input Value Id for Entry 11
1053 -- p_datetrack_update_mode VARCHAR2 The date track mode.
1054 -- p_object_version_number VARCHAR2 Object Version Number for Tax Card.
1055 --
1056 -- Post Success:
1057 -- The API successfully updates the tax card and/or tax entry.
1058 --
1059 -- Post Failure:
1060 -- The API will raise an error.
1061 --
1062 -- Access Status:
1063 -- Private. For Internal Development Use only.
1064 --
1065 -- {End Of Comments}
1066 --
1067 PROCEDURE update_taxcard (
1068 p_legislation_code IN VARCHAR2
1069 ,p_effective_date IN DATE
1070 ,p_assignment_id IN VARCHAR2
1071 ,p_person_id IN VARCHAR2
1072 ,p_business_group_id IN VARCHAR2
1073 ,p_element_entry_id_tc IN VARCHAR2
1074 ,p_taxcard_type IN VARCHAR2 DEFAULT NULL
1075 ,p_method_of_receipt IN VARCHAR2 DEFAULT NULL
1076 ,p_base_rate IN NUMBER DEFAULT NULL
1077 ,p_tax_municipality IN VARCHAR2 DEFAULT NULL
1078 ,p_additional_rate IN NUMBER DEFAULT NULL
1079 ,p_override_manual_upd IN VARCHAR2 DEFAULT NULL
1080 ,p_previous_income IN NUMBER DEFAULT NULL
1081 ,p_yearly_income_limit IN NUMBER DEFAULT NULL
1082 ,p_date_returned IN DATE DEFAULT NULL
1083 ,p_registration_date IN DATE DEFAULT NULL
1084 ,p_lower_income_percentage IN NUMBER DEFAULT NULL
1085 ,p_input_value_id1 IN VARCHAR2 DEFAULT NULL
1086 ,p_input_value_id2 IN VARCHAR2 DEFAULT NULL
1087 ,p_input_value_id3 IN VARCHAR2 DEFAULT NULL
1088 ,p_input_value_id4 IN VARCHAR2 DEFAULT NULL
1089 ,p_input_value_id5 IN VARCHAR2 DEFAULT NULL
1090 ,p_input_value_id6 IN VARCHAR2 DEFAULT NULL
1091 ,p_input_value_id7 IN VARCHAR2 DEFAULT NULL
1092 ,p_input_value_id8 IN VARCHAR2 DEFAULT NULL
1093 ,p_input_value_id9 IN VARCHAR2 DEFAULT NULL
1094 ,p_input_value_id10 IN VARCHAR2 DEFAULT NULL
1095 ,p_input_value_id11 IN VARCHAR2 DEFAULT NULL
1096 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
1097 ,p_object_version_number IN VARCHAR2) IS
1098 --declarations here
1099 l_start_date DATE;
1100 l_end_date DATE;
1101 l_warning BOOLEAN;
1102 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1103 l_ovn pay_element_entries_f.object_version_number%TYPE;
1104
1105 l_proc varchar2(72) := g_package||'update_taxcard';
1106 BEGIN
1107 if g_debug then
1108 hr_utility.set_location('Entering:'|| l_proc, 1);
1109 end if;
1110
1111 l_ovn := to_number(p_object_version_number);
1112
1113 --l_element_entry_id := find_element_entry_id(p_assignment_id, p_business_group_id,
1114 --'Tax Card',p_effective_date);
1115
1116 l_element_entry_id := p_element_entry_id_tc;
1117
1118 -- insert records into pay_element_entries_f and pay_element_entry_values_f
1119 pay_element_entry_api.update_element_entry
1120 (p_validate => FALSE
1121 ,p_object_version_number => l_ovn
1122 ,p_update_warning => l_warning
1123 ,p_datetrack_update_mode => p_datetrack_update_mode
1124 ,p_effective_date => p_effective_date
1125 ,p_business_group_id => p_business_group_id
1126 ,p_input_value_id1 => p_input_value_id1
1127 ,p_input_value_id2 => p_input_value_id2
1128 ,p_input_value_id3 => p_input_value_id3
1129 ,p_input_value_id4 => p_input_value_id4
1130 ,p_input_value_id5 => p_input_value_id5
1131 ,p_input_value_id6 => p_input_value_id6
1132 ,p_input_value_id7 => p_input_value_id7
1133 ,p_input_value_id8 => p_input_value_id8
1134 ,p_input_value_id9 => p_input_value_id9
1135 ,p_input_value_id10 => p_input_value_id10
1136 ,p_input_value_id11 => p_input_value_id11
1137 ,p_entry_value1 => p_taxcard_type
1138 ,p_entry_value2 => p_method_of_receipt
1139 ,p_entry_value3 => p_base_rate
1140 ,p_entry_value4 => p_tax_municipality
1141 ,p_entry_value5 => p_additional_rate
1142 ,p_entry_value6 => p_override_manual_upd
1143 ,p_entry_value7 => p_previous_income
1144 ,p_entry_value8 => p_yearly_income_limit
1145 ,p_entry_value9 => p_date_returned
1146 ,p_entry_value10 => p_registration_date
1147 ,p_entry_value11 => p_lower_income_percentage
1148 ,p_effective_start_date => l_start_date
1149 ,p_effective_end_date => l_end_date
1150 ,p_element_entry_id => l_element_entry_id
1151 ,p_cost_allocation_keyflex_id => hr_api.g_number
1152 ,p_updating_action_id => hr_api.g_number
1153 ,p_original_entry_id => hr_api.g_number
1154 ,p_creator_type => hr_api.g_varchar2
1155 ,p_comment_id => hr_api.g_number
1156 ,p_creator_id => hr_api.g_number
1157 ,p_reason => hr_api.g_varchar2
1158 ,p_subpriority => hr_api.g_number
1159 ,p_date_earned => hr_api.g_date
1160 ,p_personal_payment_method_id => hr_api.g_number
1161 ,p_attribute_category => hr_api.g_varchar2
1162 ,p_attribute1 => hr_api.g_varchar2
1163 ,p_attribute2 => hr_api.g_varchar2
1164 ,p_attribute3 => hr_api.g_varchar2
1165 ,p_attribute4 => hr_api.g_varchar2
1166 ,p_attribute5 => hr_api.g_varchar2
1167 ,p_attribute6 => hr_api.g_varchar2
1168 ,p_attribute7 => hr_api.g_varchar2
1169 ,p_attribute8 => hr_api.g_varchar2
1170 ,p_attribute9 => hr_api.g_varchar2
1171 ,p_attribute10 => hr_api.g_varchar2
1172 ,p_attribute11 => hr_api.g_varchar2
1173 ,p_attribute12 => hr_api.g_varchar2
1174 ,p_attribute13 => hr_api.g_varchar2
1175 ,p_attribute14 => hr_api.g_varchar2
1176 ,p_attribute15 => hr_api.g_varchar2
1177 ,p_attribute16 => hr_api.g_varchar2
1178 ,p_attribute17 => hr_api.g_varchar2
1179 ,p_attribute18 => hr_api.g_varchar2
1180 ,p_attribute19 => hr_api.g_varchar2
1181 ,p_attribute20 => hr_api.g_varchar2
1182 ,p_updating_action_type => hr_api.g_varchar2
1183 ,p_entry_information_category => hr_api.g_varchar2
1184 ,p_entry_information1 => hr_api.g_varchar2
1185 ,p_entry_information2 => hr_api.g_varchar2
1186 ,p_entry_information3 => hr_api.g_varchar2
1187 ,p_entry_information4 => hr_api.g_varchar2
1188 ,p_entry_information5 => hr_api.g_varchar2
1189 ,p_entry_information6 => hr_api.g_varchar2
1190 ,p_entry_information7 => hr_api.g_varchar2
1191 ,p_entry_information8 => hr_api.g_varchar2
1192 ,p_entry_information9 => hr_api.g_varchar2
1193 ,p_entry_information10 => hr_api.g_varchar2
1194 ,p_entry_information11 => hr_api.g_varchar2
1195 ,p_entry_information12 => hr_api.g_varchar2
1196 ,p_entry_information13 => hr_api.g_varchar2
1197 ,p_entry_information14 => hr_api.g_varchar2
1198 ,p_entry_information15 => hr_api.g_varchar2
1199 ,p_entry_information16 => hr_api.g_varchar2
1200 ,p_entry_information17 => hr_api.g_varchar2
1201 ,p_entry_information18 => hr_api.g_varchar2
1202 ,p_entry_information19 => hr_api.g_varchar2
1203 ,p_entry_information20 => hr_api.g_varchar2
1204 ,p_entry_information21 => hr_api.g_varchar2
1205 ,p_entry_information22 => hr_api.g_varchar2
1206 ,p_entry_information23 => hr_api.g_varchar2
1207 ,p_entry_information24 => hr_api.g_varchar2
1208 ,p_entry_information25 => hr_api.g_varchar2
1209 ,p_entry_information26 => hr_api.g_varchar2
1210 ,p_entry_information27 => hr_api.g_varchar2
1211 ,p_entry_information28 => hr_api.g_varchar2
1212 ,p_entry_information29 => hr_api.g_varchar2
1213 ,p_entry_information30 => hr_api.g_varchar2);
1214
1215 if g_debug then
1216 hr_utility.set_location('Entering:'|| l_proc, 1);
1217 end if;
1218 -- Do not COMMIT here. COMMIT should be done from the OAF Application Only.
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 RAISE;
1222 END update_taxcard;
1223 --
1224 -- ----------------------------------------------------------------------------
1225 -- |-------------------------< update_tax >-----------------------------------|
1226 -- ----------------------------------------------------------------------------
1227 -- {Start Of Comments}
1228 --
1229 -- Description:
1230 -- This API will update a tax entry for a Finland Assignment.
1231 -- This API delegates to the update_element_entry procedure of the
1232 -- pay_element_entry_api package.
1233 --
1234 -- Prerequisites:
1235 -- The element entry (of element type 'Tax) and the
1236 -- corresponding element link should exist for the given assignment
1237 -- and business group.
1238 --
1239 -- In Parameters:
1240 -- Name Reqd Type Description
1241 -- p_legislation_code Yes VARCHAR2 The Legislation Code.
1242 -- p_effective_date Yes DATE The current effective date
1243 -- p_assignment_id Yes VARCHAR2 Assignment ID of the record.
1244 -- p_person_id Yes VARCHAR2 Person ID for the record.
1245 -- p_business_group_id Yes VARCHAR2 Current Business Group Id.
1246 -- p_element_entry_id_t Yes VARCHAR2 Element entry Id for Tax Element.
1247 -- p_primary_employment VARCHAR2 Primary Employment Flag.
1248 -- p_extra_income_rate NUMBER Extra Income Rate.
1249 -- p_extra_income_add_rate NUMBER Extra Income Additional Rate.
1250 -- p_extra_income_limit NUMBER Extra Income Limit.
1251 -- p_prev_extra_income NUMBER Previous Extra Income.
1252 -- p_input_value_id1 VARCHAR2 Input Value Id for Entry 1
1253 -- p_input_value_id2 VARCHAR2 Input Value Id for Entry 2
1254 -- p_input_value_id3 VARCHAR2 Input Value Id for Entry 3
1255 -- p_input_value_id4 VARCHAR2 Input Value Id for Entry 4
1256 -- p_input_value_id5 VARCHAR2 Input Value Id for Entry 5
1257 -- p_datetrack_update_mode VARCHAR2 The date track mode.
1258 -- p_object_version_number VARCHAR2 Object Version Number for Tax.
1259 --
1260 -- Post Success:
1261 -- The API successfully updates the tax card and/or tax entry.
1262 --
1263 -- Post Failure:
1264 -- The API will raise an error.
1265 --
1266 -- Access Status:
1267 -- Private. For Internal Development Use only.
1268 --
1269 -- {End Of Comments}
1270 --
1271 PROCEDURE update_tax (
1272 p_legislation_code IN VARCHAR2
1273 ,p_effective_date IN DATE
1274 ,p_assignment_id IN VARCHAR2
1275 ,p_person_id IN VARCHAR2
1276 ,p_business_group_id IN VARCHAR2
1277 ,p_element_entry_id_t IN VARCHAR2
1278 ,p_primary_employment IN VARCHAR2 DEFAULT NULL
1279 ,p_extra_income_rate IN NUMBER DEFAULT NULL
1280 ,p_extra_income_add_rate IN NUMBER DEFAULT NULL
1281 ,p_extra_income_limit IN NUMBER DEFAULT NULL
1282 ,p_prev_extra_income IN NUMBER DEFAULT NULL
1283 ,p_input_value_id1 IN VARCHAR2 DEFAULT NULL
1284 ,p_input_value_id2 IN VARCHAR2 DEFAULT NULL
1285 ,p_input_value_id3 IN VARCHAR2 DEFAULT NULL
1286 ,p_input_value_id4 IN VARCHAR2 DEFAULT NULL
1287 ,p_input_value_id5 IN VARCHAR2 DEFAULT NULL
1288 ,p_datetrack_update_mode IN VARCHAR2 DEFAULT NULL
1289 ,p_object_version_number IN VARCHAR2) IS
1290
1291 --declarations here
1292 l_start_date DATE;
1293 l_end_date DATE;
1294 l_warning BOOLEAN;
1295 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1296 l_ovn pay_element_entries_f.object_version_number%TYPE;
1297
1298 l_proc varchar2(72) := g_package||'update_tax';
1299 BEGIN
1300 if g_debug then
1301 hr_utility.set_location('Entering:'|| l_proc, 1);
1302 end if;
1303
1304 l_ovn := to_number(p_object_version_number);
1305 --l_element_entry_id := find_element_entry_id(p_assignment_id, p_business_group_id,
1306 -- 'Tax',p_effective_date);
1307 l_element_entry_id := p_element_entry_id_t;
1308
1309 -- insert records into pay_element_entries_f and pay_element_entry_values_f
1310 pay_element_entry_api.update_element_entry
1311 (p_validate => FALSE
1312 ,p_object_version_number => l_ovn
1313 ,p_update_warning => l_warning
1314 ,p_datetrack_update_mode => p_datetrack_update_mode
1315 ,p_effective_date => p_effective_date
1316 ,p_business_group_id => p_business_group_id
1317 ,p_input_value_id1 => p_input_value_id1
1318 ,p_input_value_id2 => p_input_value_id2
1319 ,p_input_value_id3 => p_input_value_id3
1320 ,p_input_value_id4 => p_input_value_id4
1321 ,p_input_value_id5 => p_input_value_id5
1322 ,p_entry_value1 => p_primary_employment
1323 ,p_entry_value2 => p_extra_income_rate
1324 ,p_entry_value3 => p_extra_income_add_rate
1325 ,p_entry_value4 => p_extra_income_limit
1326 ,p_entry_value5 => p_prev_extra_income
1327 ,p_effective_start_date => l_start_date
1328 ,p_effective_end_date => l_end_date
1329 ,p_element_entry_id => l_element_entry_id
1330 ,p_cost_allocation_keyflex_id => hr_api.g_number
1331 ,p_updating_action_id => hr_api.g_number
1332 ,p_original_entry_id => hr_api.g_number
1333 ,p_creator_type => hr_api.g_varchar2
1334 ,p_comment_id => hr_api.g_number
1335 ,p_creator_id => hr_api.g_number
1336 ,p_reason => hr_api.g_varchar2
1337 ,p_subpriority => hr_api.g_number
1338 ,p_date_earned => hr_api.g_date
1339 ,p_personal_payment_method_id => hr_api.g_number
1340 ,p_attribute_category => hr_api.g_varchar2
1341 ,p_attribute1 => hr_api.g_varchar2
1342 ,p_attribute2 => hr_api.g_varchar2
1343 ,p_attribute3 => hr_api.g_varchar2
1344 ,p_attribute4 => hr_api.g_varchar2
1345 ,p_attribute5 => hr_api.g_varchar2
1346 ,p_attribute6 => hr_api.g_varchar2
1347 ,p_attribute7 => hr_api.g_varchar2
1348 ,p_attribute8 => hr_api.g_varchar2
1349 ,p_attribute9 => hr_api.g_varchar2
1350 ,p_attribute10 => hr_api.g_varchar2
1351 ,p_attribute11 => hr_api.g_varchar2
1352 ,p_attribute12 => hr_api.g_varchar2
1353 ,p_attribute13 => hr_api.g_varchar2
1354 ,p_attribute14 => hr_api.g_varchar2
1355 ,p_attribute15 => hr_api.g_varchar2
1356 ,p_attribute16 => hr_api.g_varchar2
1357 ,p_attribute17 => hr_api.g_varchar2
1358 ,p_attribute18 => hr_api.g_varchar2
1359 ,p_attribute19 => hr_api.g_varchar2
1360 ,p_attribute20 => hr_api.g_varchar2
1361 ,p_updating_action_type => hr_api.g_varchar2
1362 ,p_entry_information_category => hr_api.g_varchar2
1363 ,p_entry_information1 => hr_api.g_varchar2
1364 ,p_entry_information2 => hr_api.g_varchar2
1365 ,p_entry_information3 => hr_api.g_varchar2
1366 ,p_entry_information4 => hr_api.g_varchar2
1367 ,p_entry_information5 => hr_api.g_varchar2
1368 ,p_entry_information6 => hr_api.g_varchar2
1369 ,p_entry_information7 => hr_api.g_varchar2
1370 ,p_entry_information8 => hr_api.g_varchar2
1371 ,p_entry_information9 => hr_api.g_varchar2
1372 ,p_entry_information10 => hr_api.g_varchar2
1373 ,p_entry_information11 => hr_api.g_varchar2
1374 ,p_entry_information12 => hr_api.g_varchar2
1375 ,p_entry_information13 => hr_api.g_varchar2
1376 ,p_entry_information14 => hr_api.g_varchar2
1377 ,p_entry_information15 => hr_api.g_varchar2
1378 ,p_entry_information16 => hr_api.g_varchar2
1379 ,p_entry_information17 => hr_api.g_varchar2
1380 ,p_entry_information18 => hr_api.g_varchar2
1381 ,p_entry_information19 => hr_api.g_varchar2
1382 ,p_entry_information20 => hr_api.g_varchar2
1383 ,p_entry_information21 => hr_api.g_varchar2
1384 ,p_entry_information22 => hr_api.g_varchar2
1385 ,p_entry_information23 => hr_api.g_varchar2
1386 ,p_entry_information24 => hr_api.g_varchar2
1387 ,p_entry_information25 => hr_api.g_varchar2
1388 ,p_entry_information26 => hr_api.g_varchar2
1389 ,p_entry_information27 => hr_api.g_varchar2
1390 ,p_entry_information28 => hr_api.g_varchar2
1391 ,p_entry_information29 => hr_api.g_varchar2
1392 ,p_entry_information30 => hr_api.g_varchar2);
1393
1394 if g_debug then
1395 hr_utility.set_location('Entering:'|| l_proc, 1);
1396 end if;
1397 -- Do not COMMIT here. COMMIT should be done from the OAF Application Only.
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 RAISE;
1401 END update_tax;
1402 --
1403 -- ----------------------------------------------------------------------------
1404 -- |------------------< find_element_entry_id >-------------------------------|
1405 -- ----------------------------------------------------------------------------
1406 -- {Start Of Comments}
1407 --
1408 -- Description:
1409 -- This API returns the element entry id of the element whose name is specified , that is attached to
1410 -- the assignment on the given effective date
1411 --
1412 -- Prerequisites:
1413 -- The assignment and the element entry should exist as of the effective date specified.
1414 --
1415 -- In Parameters:
1416 -- Name Reqd Type Description
1417 -- p_effective_date Yes VARCHAR2 The effective date of the change.
1418 -- p_assignment_id Yes per_all_assignments_f.assignment_id%TYPE ID of the assignment
1419 -- p_business_group_id Yes pay_element_links_f.business_group_id%TYPE Business Group Id.
1420 -- p_element_name Yes pay_element_types_f.element_name%TYPE Name of the Element to be checked.
1421 --
1422 --
1423 -- Post Success:
1424 -- The function returns the id of the element entry.
1425 --
1426 -- Access Status:
1427 -- Private. For Internal Development Use only.
1428 --
1429 -- {End Of Comments}
1430 FUNCTION find_element_entry_id (
1431 p_assignment_id IN pay_element_entries_f.assignment_id%TYPE,
1432 p_business_group_id IN pay_element_links_f.business_group_id%TYPE,
1433 p_element_name IN pay_element_types_f.element_name%TYPE,
1434 p_effective_date IN VARCHAR2) RETURN pay_element_entries_f.element_entry_id%TYPE IS
1435
1436 --declarations here
1437 l_attached_flag BOOLEAN;
1438 l_csr_result pay_element_entries_f.element_entry_id%TYPE;
1439 CURSOR cGetElementEntryId(l_asgid IN pay_element_entries_f.assignment_id%TYPE,
1440 l_business_grp_id IN pay_element_links_f.business_group_id%TYPE,
1441 l_element_name IN pay_element_types_f.element_name%TYPE,
1442 l_effective_date IN VARCHAR2) IS
1443 SELECT pee.element_entry_id
1444 FROM pay_element_types_f pet ,
1445 pay_element_links_f pel ,
1446 pay_element_entries_f pee
1447 WHERE pet.element_name = l_element_name
1448 AND pet.legislation_code = 'FI'
1449 AND pet.business_group_id IS NULL
1450 AND fnd_date.canonical_to_date(l_effective_date)
1451 BETWEEN pet.effective_start_date AND pet.effective_end_date
1452 AND pel.element_type_id = pet.element_type_id
1453 AND pel.business_group_id = l_business_grp_id
1454 AND fnd_date.canonical_to_date(l_effective_date)
1455 BETWEEN pel.effective_start_date AND pel.effective_end_date
1456 AND pee.element_link_id = pel.element_link_id
1457 AND fnd_date.canonical_to_date(l_effective_date)
1458 BETWEEN pee.effective_start_date AND pee.effective_end_date
1459 AND pee.assignment_id = l_asgid;
1460
1461 BEGIN
1462 OPEN cGetElementEntryId(p_assignment_id , p_business_group_id , p_element_name , p_effective_date);
1463 FETCH cGetElementEntryId INTO l_csr_result;
1464 CLOSE cGetElementEntryId;
1465 return l_csr_result;
1466 END find_element_entry_id;
1467 --
1468 -- ----------------------------------------------------------------------------
1469 -- |---------------< is_element_started_today >-------------------------------|
1470 -- ----------------------------------------------------------------------------
1471 -- {Start Of Comments}
1472 --
1473 -- Description:
1474 -- This API returns true / false based on whether the given
1475 -- elements start date is the effective date.
1476 --
1477 -- Prerequisites:
1478 -- The assignment and the element entry should exist as of the effective date specified.
1479 --
1480 -- In Parameters:
1481 -- Name Reqd Type Description
1482 -- p_effective_date Yes VARCHAR2 The effective date of the change.
1483 -- p_assignment_id Yes per_all_assignments_f.assignment_id%TYPE ID of the assignment
1484 -- p_element_name Yes pay_element_types_f.element_name%TYPE Name of the Element to be checked.
1485 --
1486 --
1487 -- Post Success:
1488 -- The function returns true if the start date of the element is equals the effectived date
1489 --
1490 -- Access Status:
1491 -- Private. For Internal Development Use only.
1492 --
1493 -- {End Of Comments}
1494 FUNCTION is_element_started_today (
1495 p_assignment_id IN pay_element_entries_f.assignment_id%TYPE,
1496 p_element_name IN pay_element_types_f.element_name%TYPE,
1497 p_effective_date IN VARCHAR2) RETURN BOOLEAN IS
1498
1499 --declarations here
1500 Cursor cElementStartedToday(c_effective_date VARCHAR2, c_element_name pay_element_types_f.element_name%TYPE , c_asg_id per_all_assignments_f.assignment_id%TYPE) IS
1501 select pee.element_entry_id
1502 from pay_element_entries_f pee,
1503 pay_element_types_f pet
1504 where pee.element_type_id = pet.element_type_id
1505 and pet.element_name = c_element_name
1506 and c_effective_date between pet.effective_start_date and pet.effective_end_date
1507 and pee.assignment_id = c_asg_id
1508 and c_effective_date between pee.effective_start_date and pee.effective_end_date
1509 and pee.effective_start_date = c_effective_date;
1510 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1511 BEGIN
1512 open cElementStartedToday(p_effective_date,p_element_name,p_assignment_id);
1513 fetch cElementStartedToday into l_element_entry_id;
1514 IF cElementStartedToday%NOTFOUND THEN
1515 CLOSE cElementStartedToday;
1516 return false;
1517 ELSE
1518 CLOSE cElementStartedToday;
1519 return true;
1520 END IF;
1521 END;
1522 --
1523 -- ----------------------------------------------------------------------------
1524 -- |----------------------------< is_primary_asg >----------------------------|
1525 -- ----------------------------------------------------------------------------
1526 -- {Start Of Comments}
1527 --
1528 -- Description:
1529 -- This API returns true / false based on whether the given
1530 -- assignment Id is a Primary Assignment or not.
1531 --
1532 -- Prerequisites:
1533 -- The assignment Id should exist as of the effective date specified.
1534 --
1535 -- In Parameters:
1536 -- Name Reqd Type Description
1537 -- p_effective_date Yes VARCHAR2 The effective date of the change.
1538 -- p_assignment_id Yes per_all_assignments_f.assignment_id%TYPE ID of the assignment
1539 --
1540 --
1541 -- Post Success:
1542 -- The function returns true if the assignment is Primary and false otherwise
1543 --
1544 -- Access Status:
1545 -- Private. For Internal Development Use only.
1546 --
1547 -- {End Of Comments}
1548 FUNCTION is_primary_asg (
1549 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1550 p_effective_date IN VARCHAR2) RETURN BOOLEAN IS
1551
1552 --declarations here
1553 l_primary_flag BOOLEAN;
1554 l_csr_result per_all_assignments_f.primary_flag%TYPE;
1555 CURSOR cIsPrimaryAsg(asgid IN per_all_assignments_f.assignment_id%TYPE,
1556 effective_date IN VARCHAR2) IS
1557 SELECT asg.primary_flag
1558 FROM per_all_assignments_f asg
1559 WHERE asg.assignment_id = asgid
1560 AND fnd_date.chardate_to_date(effective_date) BETWEEN
1561 asg.effective_start_date AND asg.effective_end_date;
1562 BEGIN
1563 OPEN cIsPrimaryAsg(p_assignment_id , p_effective_date);
1564 FETCH cIsPrimaryAsg INTO l_csr_result;
1565 CLOSE cIsPrimaryAsg;
1566
1567 IF l_csr_result = 'Y' THEN
1568 l_primary_flag := true;
1569 ELSIF l_csr_result = 'N' THEN
1570 l_primary_flag := false;
1571 END IF;
1572
1573 return l_primary_flag;
1574 END is_primary_asg;
1575 --
1576 -- ----------------------------------------------------------------------------
1577 -- |---------------------------< is_element_attached >------------------------|
1578 -- ----------------------------------------------------------------------------
1579 -- {Start Of Comments}
1580 --
1581 -- Description:
1582 -- This API returns true / false based on whether the given
1583 -- element is attached to the assignment Id on the given effective date
1584 --
1585 -- Prerequisites:
1586 -- The assignment and the element entry should exist as of the effective date specified.
1587 --
1588 -- In Parameters:
1589 -- Name Reqd Type Description
1590 -- p_effective_date Yes VARCHAR2 The effective date of the change.
1591 -- p_assignment_id Yes per_all_assignments_f.assignment_id%TYPE ID of the assignment
1592 -- p_business_group_id Yes pay_element_links_f.business_group_id%TYPE Business Group Id.
1593 -- p_element_name Yes pay_element_types_f.element_name%TYPE Name of the Element to be checked.
1594 --
1595 --
1596 -- Post Success:
1597 -- The function returns true if the element is attached to the assignment and false otherwise.
1598 --
1599 -- Access Status:
1600 -- Private. For Internal Development Use only.
1601 --
1602 -- {End Of Comments}
1603 FUNCTION is_element_attached (
1604 p_assignment_id IN pay_element_entries_f.assignment_id%TYPE,
1605 p_business_group_id IN pay_element_links_f.business_group_id%TYPE,
1606 p_element_name IN pay_element_types_f.element_name%TYPE,
1607 p_effective_date IN VARCHAR2) RETURN BOOLEAN IS
1608
1609 --declarations here
1610 l_attached_flag BOOLEAN;
1611 l_csr_result pay_element_entries_f.element_entry_id%TYPE;
1612 CURSOR cIsElementAttached(l_asgid IN pay_element_entries_f.assignment_id%TYPE,
1613 l_business_grp_id IN pay_element_links_f.business_group_id%TYPE,
1614 l_element_name IN pay_element_types_f.element_name%TYPE,
1615 l_effective_date IN VARCHAR2) IS
1616 SELECT pee.element_entry_id
1617 FROM pay_element_types_f pet ,
1618 pay_element_links_f pel ,
1619 pay_element_entries_f pee
1620 WHERE pet.element_name = l_element_name
1621 AND pet.legislation_code = 'FI'
1622 AND pet.business_group_id IS NULL
1623 AND fnd_date.chardate_to_date(l_effective_date)
1624 BETWEEN pet.effective_start_date AND pet.effective_end_date
1625 AND pel.element_type_id = pet.element_type_id
1626 AND pel.business_group_id = l_business_grp_id
1627 AND fnd_date.chardate_to_date(l_effective_date)
1628 BETWEEN pel.effective_start_date AND pel.effective_end_date
1629 AND pee.element_link_id = pel.element_link_id
1630 AND fnd_date.chardate_to_date(l_effective_date)
1631 BETWEEN pee.effective_start_date AND pee.effective_end_date
1632 AND pee.assignment_id = l_asgid;
1633
1634 BEGIN
1635 OPEN cIsElementAttached(p_assignment_id , p_business_group_id , p_element_name , p_effective_date);
1636 FETCH cIsElementAttached INTO l_csr_result;
1637 IF cIsElementAttached%NOTFOUND THEN
1638 CLOSE cIsElementAttached;
1639 return false;
1640 ELSE
1641 CLOSE cIsElementAttached;
1642 return true;
1643 END IF;
1644 END is_element_attached;
1645 --
1646 -- ----------------------------------------------------------------------------
1647 -- |------------------------< find_dt_upd_modes >-----------------------------|
1648 -- ----------------------------------------------------------------------------
1649 -- {Start Of Comments}
1650 --
1651 -- Description:
1652 -- This API returns the DT modes for pay_element_entries_f for a given
1653 -- element_entry_id (base key value) on a specified date
1654 --
1655 -- Prerequisites:
1656 -- The element_entry (p_base_key_value) must exist as of the effective date
1657 -- of the change (p_effective_date).
1658 --
1659 -- In Parameters:
1660 -- Name Reqd Type Description
1661 -- p_effective_date Yes DATE The effective date of the change.
1662 -- p_base_key_value Yes NUMBER ID of the element entry.
1663 --
1664 --
1665 -- Post Success:
1666 --
1667 -- The API sets the following out parameters:
1668 --
1669 -- Name Type Description
1670 -- p_correction BOOLEAN True if correction mode is valid.
1671 -- p_update BOOLEAN True if update mode is valid.
1672 -- p_update_override BOOLEAN True if update override mode is valid.
1673 -- p_update_change_insert BOOLEAN True if update change insert mode is valid.
1674 -- p_update_start_date DATE Start date for Update record.
1675 -- p_update_end_date DATE End date for Update record.
1676 -- p_override_start_date DATE Start date for Override.
1677 -- p_override_end_date DATE End date for Overrride.
1678 -- p_upd_chg_start_date DATE Start date for Update Change.
1679 -- p_upd_chg_end_date DATE End date for Update Change.
1680
1681 -- Post Failure:
1682 -- The API will raise an error.
1683 --
1684 -- Access Status:
1685 -- Private. For Internal Development Use only.
1686 --
1687 -- {End Of Comments}
1688 --
1689 PROCEDURE find_dt_upd_modes
1690 (p_effective_date IN DATE
1691 ,p_base_key_value IN NUMBER
1692 ,p_correction OUT NOCOPY BOOLEAN
1693 ,p_update OUT NOCOPY BOOLEAN
1694 ,p_update_override OUT NOCOPY BOOLEAN
1695 ,p_update_change_insert OUT NOCOPY BOOLEAN
1696 ,p_correction_start_date OUT NOCOPY DATE
1697 ,p_correction_end_date OUT NOCOPY DATE
1698 ,p_update_start_date OUT NOCOPY DATE
1699 ,p_update_end_date OUT NOCOPY DATE
1700 ,p_override_start_date OUT NOCOPY DATE
1701 ,p_override_end_date OUT NOCOPY DATE
1702 ,p_upd_chg_start_date OUT NOCOPY DATE
1703 ,p_upd_chg_end_date OUT NOCOPY DATE
1704 ) IS
1705
1706 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
1707
1708 BEGIN
1709 if g_debug then
1710 hr_utility.set_location('Entering:'|| l_proc, 1);
1711 end if;
1712 --
1713 -- Call the corresponding datetrack api
1714 --
1715 dt_api.find_dt_upd_modes_and_dates(
1716 p_effective_date => p_effective_date
1717 ,p_base_table_name => 'pay_element_entries_f'
1718 ,p_base_key_column => 'ELEMENT_ENTRY_ID'
1719 ,p_base_key_value => p_base_key_value
1720 ,p_correction => p_correction
1721 ,p_update => p_update
1722 ,p_update_override => p_update_override
1723 ,p_update_change_insert => p_update_change_insert
1724 ,p_correction_start_date => p_correction_start_date
1725 ,p_correction_end_date => p_correction_end_date
1726 ,p_update_start_date => p_update_start_date
1727 ,p_update_end_date => p_update_end_date
1728 ,p_override_start_date => p_override_start_date
1729 ,p_override_end_date => p_override_end_date
1730 ,p_upd_chg_start_date => p_upd_chg_start_date
1731 ,p_upd_chg_end_date => p_upd_chg_end_date);
1732 if g_debug then
1733 hr_utility.set_location('Entering:'|| l_proc, 2);
1734 end if;
1735 --
1736 --hr_utility.set_location(' Leaving:'||l_proc, 10);
1737 EXCEPTION
1738 WHEN OTHERS THEN
1739 RAISE;
1740 END find_dt_upd_modes;
1741
1742 END py_fi_tax_card;
1743
[Home] [Help]