1 PACKAGE BODY py_no_tax_card AS
2 /* $Header: pynotaxc.pkb 120.1.12000000.1 2007/05/23 05:13:05 rlingama noship $ */
3 --
4 -- Package Variables
5 --
6 g_package CONSTANT varchar2(33) := ' hr_no_taxcard_api.';
7 g_debug BOOLEAN := hr_utility.debug_enabled;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_taxcard >----------------------------|
11 -- ----------------------------------------------------------------------------
12 -- {Start Of Comments}
13 --
14 -- Description:
15 -- This API will insert a tax card entry for a Norway Assignment.
16 -- This API delegates to the create_element_entry procedure of the
17 -- pay_element_entry_api package.
18 --
19 -- Prerequisites:
20 -- The element entry (of element type 'Tax Card') and the corresponding
21 -- element link should exist for the given assignment and business group.
22 --
23 -- In Parameters:
24 -- Name Reqd Type Description
25 -- p_legislation_code Yes VARCHAR2 Legislation code.
26 -- p_effective_date Yes DATE The effective date of the
27 -- change.
28 -- p_assignment_id Yes VARCHAR2 Id of the assignment.
29 -- p_person_id Yes VARCHAR2 Id of the person.
30 -- p_business_group_id Yes VARCHAR2 Id of the business group.
31 -- p_entry_value4 NUMBER Element entry value.
32 -- p_entry_value7 NUMBER Element entry value.
33 -- p_entry_value8 DATE Element entry value.
34 -- p_entry_value9 DATE Element entry value.
35 -- p_entry_value1 VARCHAR2 Element entry value.
36 -- p_entry_value2 VARCHAR2 Element entry value.
37 -- p_entry_value3 VARCHAR2 Element entry value.
38 -- p_entry_value5 VARCHAR2 Element entry value.
39 -- p_entry_value6 VARCHAR2 Element entry value.
40 -- p_element_entry_id VARCHAR2 Id of the element entry.
41 -- p_element_link_id VARCHAR2 Id of the element link.
42 --
43 --
44 -- Post Success:
45 --
46 -- The API successfully updates the tax card entry.
47 --
48 -- Post Failure:
49 -- The API will raise an error.
50 --
51 -- Access Status:
52 -- Private. For Internal Development Use only.
53 --
54 -- {End Of Comments}
55 --
56 PROCEDURE insert_taxcard (
57 p_legislation_code IN VARCHAR2
58 ,p_effective_date IN DATE
59 ,p_assignment_id IN VARCHAR2
60 ,p_person_id IN VARCHAR2
61 ,p_business_group_id IN VARCHAR2
62 ,p_entry_value4 IN NUMBER DEFAULT NULL
63 ,p_entry_value7 IN NUMBER DEFAULT NULL
64 ,p_entry_value8 IN DATE DEFAULT NULL
65 ,p_entry_value9 IN DATE DEFAULT NULL
66 ,p_entry_value1 IN VARCHAR2 DEFAULT NULL
67 ,p_entry_value2 IN VARCHAR2 DEFAULT NULL
68 ,p_entry_value3 IN VARCHAR2 DEFAULT NULL
69 ,p_entry_value5 IN VARCHAR2 DEFAULT NULL
70 ,p_entry_value6 IN VARCHAR2 DEFAULT NULL
71 ) IS
72 -- Declarations here
73 l_start_date DATE;
74 l_end_date DATE;
75 l_warning BOOLEAN;
76 l_element_entry_id NUMBER(15);
77 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
78 l_ovn NUMBER(9);
79 l_element_link_id pay_element_links_f.element_link_id%TYPE;
80 l_element_type_id pay_element_types_f.element_type_id%TYPE;
81 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
82 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
83 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
84 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
85 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
86 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
87 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
88 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
89 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
90
91 CURSOR input_values_csr IS
92 SELECT
93 et.element_type_id,
94 MIN(DECODE(iv.name, 'Method of Receipt', iv.input_value_id, null)) iv1,
95 MIN(DECODE(iv.name, 'Tax Municipality', iv.input_value_id, null)) iv2,
96 MIN(DECODE(iv.name, 'Tax Card Type', iv.input_value_id, null)) iv3,
97 MIN(DECODE(iv.name, 'Tax Percentage', iv.input_value_id, null)) iv4,
98 MIN(DECODE(iv.name, 'Tax Table Number', iv.input_value_id, null)) iv5,
99 MIN(DECODE(iv.name, 'Tax Table Type', iv.input_value_id, null)) iv6,
100 MIN(DECODE(iv.name, 'Tax Free Threshold', iv.input_value_id, null)) iv7,
101 MIN(DECODE(iv.name, 'Registration Date', iv.input_value_id, null)) iv8,
102 MIN(DECODE(iv.name, 'Date Returned', iv.input_value_id, null)) iv9
103 FROM
104 pay_element_types_f et,
105 pay_input_values_f iv
106 WHERE et.element_name = 'Tax Card'
107 AND et.legislation_code = 'NO'
108 AND et.business_group_id is null
109 AND fnd_date.canonical_to_date(p_effective_date) BETWEEN
110 et.effective_start_date AND et.effective_end_date
111 AND iv.element_type_id = et.element_type_id
112 AND fnd_date.canonical_to_date(p_effective_date)
113 BETWEEN iv.effective_start_date AND iv.effective_end_date
114 GROUP BY
115 et.element_type_id;
116 l_proc varchar2(72) := g_package||'insert_taxcard';
117
118 CURSOR csr_check_fnd_session
119 IS
120 SELECT session_id
121 FROM fnd_sessions
122 WHERE session_id = userenv('sessionid')
123 AND effective_date = p_effective_date;
124
125 LR_CHECK_FND_SESSION CSR_CHECK_FND_SESSION%ROWTYPE;
126
127 BEGIN
128 if g_debug then
129 hr_utility.set_location('Entering:'|| l_proc, 1);
130 end if;
131 --Insert row into fnd_Sessions table.
132 /*INSERT INTO fnd_sessions(session_id,effective_date)
133 VALUES(userenv('sessionid'), p_effective_date);*/
134 --Insert row into fnd_Sessions table.
135 --Pgopal - Moved into a cursor
136 OPEN csr_check_fnd_session;
137 FETCH csr_check_fnd_session INTO lr_check_fnd_session;
138 IF csr_check_fnd_session%NOTFOUND
139 THEN
140 INSERT INTO fnd_sessions(session_id, effective_date) VALUES(userenv('sessionid'), p_effective_date);
141 END IF;
142 CLOSE csr_check_fnd_session;
143
144
145 -- fetch element link id
146 l_element_link_id := pay_no_tc_dp_upload.get_element_link_id(
147 p_assignment_id, p_business_group_id,
148 fnd_date.date_to_canonical(p_effective_date),
149 'Tax Card');
150 if g_debug then
151 hr_utility.set_location('Entering:'|| l_proc, 2);
152 end if;
153 -- fetch all input value id's
154 OPEN input_values_csr;
155 FETCH input_values_csr INTO l_element_type_id, l_input_value_id1, l_input_value_id2,
156 l_input_value_id3, l_input_value_id4, l_input_value_id5, l_input_value_id6,
157 l_input_value_id7, l_input_value_id8, l_input_value_id9;
158 CLOSE input_values_csr;
159
160
161 if g_debug then
162 hr_utility.set_location('Entering:'|| l_proc, 3);
163 end if;
164 /* Commented out this code as element has Tax Percentage input value mandatory */
165 /*--If taxcard type is "NTC" or "TF" , then the tax percentage need not be stored.
166 /* Uncommented code and modified to ensure Global value is stored if the value passed is null*/
167 /*IF p_entry_value3 = 'NTC' OR p_entry_value3 = 'TF' THEN*/
168 IF p_entry_value3 = 'NTC' THEN
169 l_entry_value4 := get_global_value('NO_TAX_PERCENTAGE','NO',p_effective_date);
170 ELSE
171 l_entry_value4 := p_entry_value4;
172 END IF;
173
174 /*l_entry_value4 := p_entry_value4;*/
175 if g_debug then
176 hr_utility.set_location('Entering:'|| l_proc, 4);
177 end if;
178 -- insert records into pay_element_entries_f and pay_element_entry_values_f
179 pay_element_entry_api.create_element_entry
180 (p_effective_date => p_effective_date
181 ,p_business_group_id => p_business_group_id
182 ,p_assignment_id => p_assignment_id
183 ,p_element_link_id => l_element_link_id
184 ,p_entry_type => 'E'
185 ,p_input_value_id1 => l_input_value_id1
186 ,p_input_value_id2 => l_input_value_id2
187 ,p_input_value_id3 => l_input_value_id3
188 ,p_input_value_id4 => l_input_value_id4
189 ,p_input_value_id5 => l_input_value_id5
190 ,p_input_value_id6 => l_input_value_id6
191 ,p_input_value_id7 => l_input_value_id7
192 ,p_input_value_id8 => l_input_value_id8
193 ,p_input_value_id9 => l_input_value_id9
194 ,p_entry_value1 => p_entry_value1
195 ,p_entry_value2 => p_entry_value2
196 ,p_entry_value3 => p_entry_value3
197 ,p_entry_value4 => l_entry_value4
198 ,p_entry_value5 => p_entry_value5
199 ,p_entry_value6 => p_entry_value6
200 ,p_entry_value7 => p_entry_value7
201 /*fnd_date_to_canonical removed */
202 ,p_entry_value8 => (p_entry_value8)
203 ,p_entry_value9 => (p_entry_value9)
204 ,p_effective_start_date => l_start_date
205 ,p_effective_end_date => l_end_date
206 ,p_element_entry_id => l_element_entry_id
207 ,p_object_version_number => l_ovn
208 ,p_create_warning => l_warning
209 );
210
211 if g_debug then
212 hr_utility.set_location('Entering:'|| l_proc, 5);
213 end if;
214 -- Do not COMMIT here. COMMIT should be done thru the OAF Application only.
215 EXCEPTION
216 WHEN OTHERS THEN
217 RAISE;
218 END insert_taxcard;
219 --
220 -- ----------------------------------------------------------------------------
221 -- |--------------------------< update_taxcard >----------------------------|
222 -- ----------------------------------------------------------------------------
223 -- {Start Of Comments}
224 --
225 -- Description:
226 -- This API will update the tax card entry for a Norway Assignment.
227 -- This API delegates to the update_element_entry procedure of the
228 -- pay_element_entry_api package.
229 --
230 -- Prerequisites:
231 -- The element entry (of element type 'Tax Card') and the corresponding
232 -- element link should exist for the given assignment and business group.
233 --
234 -- In Parameters:
235 -- Name Reqd Type Description
236 -- p_legislation_code Yes VARCHAR2 Legislation code.
237 -- p_effective_date Yes DATE The effective date of the
238 -- change.
239 -- p_assignment_id Yes VARCHAR2 Id of the assignment.
240 -- p_person_id Yes VARCHAR2 Id of the person.
241 -- p_business_group_id Yes VARCHAR2 Id of the business group.
242 -- p_entry_value4 NUMBER Element entry value.
243 -- p_entry_value7 NUMBER Element entry value.
244 -- p_entry_value8 DATE Element entry value.
245 -- p_entry_value9 DATE Element entry value.
246 -- p_entry_value1 VARCHAR2 Element entry value.
247 -- p_entry_value2 VARCHAR2 Element entry value.
248 -- p_entry_value3 VARCHAR2 Element entry value.
249 -- p_entry_value5 VARCHAR2 Element entry value.
250 -- p_entry_value6 VARCHAR2 Element entry value.
251 -- p_element_entry_id VARCHAR2 Id of the element entry.
252 -- p_element_link_id VARCHAR2 Id of the element link.
253 -- p_object_version_number Yes VARCHAR2 Version number of the element
254 -- entry record.
255 -- p_input_value_id1 VARCHAR2 Id of the input value 1 for the
256 -- element.
257 -- p_input_value_id2 VARCHAR2 Id of the input value 2 for the
258 -- element.
259 -- p_input_value_id3 VARCHAR2 Id of the input value 3 for the
260 -- element.
261 -- p_input_value_id4 VARCHAR2 Id of the input value 4 for the
262 -- element.
263 -- p_input_value_id5 VARCHAR2 Id of the input value 5 for the
264 -- element.
265 -- p_input_value_id6 VARCHAR2 Id of the input value 6 for the
266 -- element.
267 -- p_input_value_id7 VARCHAR2 Id of the input value 7 for the
268 -- element.
269 -- p_input_value_id8 VARCHAR2 Id of the input value 8 for the
270 -- element.
271 -- p_input_value_id9 VARCHAR2 Id of the input value 9 for the
272 -- element.
273 -- p_datetrack_update_mode VARCHAR2 The date track update mode for
274 -- the record
275 --
276 --
277 -- Post Success:
278 --
279 -- The API successfully updates the tax card entry.
280 --
281 -- Post Failure:
282 -- The API will raise an error.
283 --
284 -- Access Status:
285 -- Private. For Internal Development Use only.
286 --
287 -- {End Of Comments}
288 --
289 PROCEDURE update_taxcard (
290 p_legislation_code IN VARCHAR2
291 ,p_effective_date IN DATE
292 ,p_assignment_id IN VARCHAR2
293 ,p_person_id IN VARCHAR2
294 ,p_business_group_id IN VARCHAR2
295 ,p_entry_value4 IN NUMBER DEFAULT NULL
296 ,p_entry_value7 IN NUMBER DEFAULT NULL
297 ,p_entry_value8 IN DATE DEFAULT NULL
298 ,p_entry_value9 IN DATE DEFAULT NULL
299 ,p_entry_value1 IN VARCHAR2 DEFAULT NULL
300 ,p_entry_value2 IN VARCHAR2 DEFAULT NULL
301 ,p_entry_value3 IN VARCHAR2 DEFAULT NULL
302 ,p_entry_value5 IN VARCHAR2 DEFAULT NULL
303 ,p_entry_value6 IN VARCHAR2 DEFAULT NULL
304 ,p_element_entry_id IN VARCHAR2
305 ,p_element_link_id IN VARCHAR2
306 ,p_object_version_number IN VARCHAR2
307 ,p_input_value_id1 IN VARCHAR2 DEFAULT NULL
308 ,p_input_value_id2 IN VARCHAR2 DEFAULT NULL
309 ,p_input_value_id3 IN VARCHAR2 DEFAULT NULL
310 ,p_input_value_id4 IN VARCHAR2 DEFAULT NULL
311 ,p_input_value_id5 IN VARCHAR2 DEFAULT NULL
312 ,p_input_value_id6 IN VARCHAR2 DEFAULT NULL
313 ,p_input_value_id7 IN VARCHAR2 DEFAULT NULL
314 ,p_input_value_id8 IN VARCHAR2 DEFAULT NULL
315 ,p_input_value_id9 IN VARCHAR2 DEFAULT NULL
316 ,p_datetrack_update_mode IN VARCHAR2
317 ) IS
318
319 l_start_date DATE;
320 l_end_date DATE;
321 l_warning BOOLEAN;
322 l_element_entry_id NUMBER(15);
323 l_ovn NUMBER(9);
324 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
325
326 l_proc varchar2(72) := g_package||'update_taxcard';
327
328 CURSOR csr_check_fnd_session
329 IS
330 SELECT session_id
331 FROM fnd_sessions
332 WHERE session_id = userenv('sessionid')
333 AND effective_date = p_effective_date;
334
335 lr_check_fnd_session csr_check_fnd_session%ROWTYPE;
336 BEGIN
337 if g_debug then
338 hr_utility.set_location('Entering:'|| l_proc, 1);
339 end if;
340 --pgopal - Insert row into fnd_Sessions table.
341 OPEN csr_check_fnd_session;
342 FETCH csr_check_fnd_session INTO lr_check_fnd_session;
343 IF csr_check_fnd_session%NOTFOUND
344 THEN
345 INSERT INTO fnd_sessions(session_id, effective_date) VALUES(userenv('sessionid'), p_effective_date);
346 END IF;
347 CLOSE csr_check_fnd_session;
348
349 --l_start_date := sysdate;
350 l_element_entry_id := to_number(p_element_entry_id);
351 l_ovn := to_number(p_object_version_number);
352
353 /* Commented out this code as element has Tax Percentage input value mandatory */
354 /*--If taxcard type is "NTC" or "TF" , then the tax percentage need not be stored.
355 if p_entry_value3 = 'NTC' or p_entry_value3 = 'TF' then
356 l_entry_value4 := null;
357 else
358 l_entry_value4 := p_entry_value4;
359 end if;*/
360
361 l_entry_value4 := p_entry_value4;
362
363 if g_debug then
364 hr_utility.set_location('Entering:'|| l_proc, 2);
365 end if;
366
367 -- insert records into pay_element_entries_f and pay_element_entry_values_f
368
369 pay_element_entry_api.update_element_entry
370 (p_validate => FALSE
371 ,p_object_version_number => l_ovn
372 ,p_update_warning => l_warning
373 ,p_datetrack_update_mode => p_datetrack_update_mode
374 ,p_effective_date => p_effective_date
375 ,p_business_group_id => p_business_group_id
376 ,p_input_value_id1 => p_input_value_id1
377 ,p_input_value_id2 => p_input_value_id2
378 ,p_input_value_id3 => p_input_value_id3
379 ,p_input_value_id4 => p_input_value_id4
380 ,p_input_value_id5 => p_input_value_id5
381 ,p_input_value_id6 => p_input_value_id6
382 ,p_input_value_id7 => p_input_value_id7
383 ,p_input_value_id8 => p_input_value_id8
384 ,p_input_value_id9 => p_input_value_id9
385 ,p_entry_value1 => p_entry_value1
386 ,p_entry_value2 => p_entry_value2
387 ,p_entry_value3 => p_entry_value3
388 ,p_entry_value4 => to_number(l_entry_value4)
389 ,p_entry_value5 => p_entry_value5
390 ,p_entry_value6 => p_entry_value6
391 ,p_entry_value7 => to_number(p_entry_value7)
392 ,p_entry_value8 => (p_entry_value8)
393 ,p_entry_value9 => (p_entry_value9)
394 ,p_effective_start_date => l_start_date
395 ,p_effective_end_date => l_end_date
396 ,p_element_entry_id => l_element_entry_id
397 ,p_cost_allocation_keyflex_id => hr_api.g_number
398 ,p_updating_action_id => hr_api.g_number
399 ,p_original_entry_id => hr_api.g_number
400 ,p_creator_type => hr_api.g_varchar2
401 ,p_comment_id => hr_api.g_number
402 ,p_creator_id => hr_api.g_number
403 ,p_reason => hr_api.g_varchar2
404 ,p_subpriority => hr_api.g_number
405 ,p_date_earned => hr_api.g_date
406 ,p_personal_payment_method_id => hr_api.g_number
407 ,p_attribute_category => hr_api.g_varchar2
408 ,p_attribute1 => hr_api.g_varchar2
409 ,p_attribute2 => hr_api.g_varchar2
410 ,p_attribute3 => hr_api.g_varchar2
411 ,p_attribute4 => hr_api.g_varchar2
412 ,p_attribute5 => hr_api.g_varchar2
413 ,p_attribute6 => hr_api.g_varchar2
414 ,p_attribute7 => hr_api.g_varchar2
415 ,p_attribute8 => hr_api.g_varchar2
416 ,p_attribute9 => hr_api.g_varchar2
417 ,p_attribute10 => hr_api.g_varchar2
418 ,p_attribute11 => hr_api.g_varchar2
419 ,p_attribute12 => hr_api.g_varchar2
420 ,p_attribute13 => hr_api.g_varchar2
421 ,p_attribute14 => hr_api.g_varchar2
422 ,p_attribute15 => hr_api.g_varchar2
423 ,p_attribute16 => hr_api.g_varchar2
424 ,p_attribute17 => hr_api.g_varchar2
425 ,p_attribute18 => hr_api.g_varchar2
426 ,p_attribute19 => hr_api.g_varchar2
427 ,p_attribute20 => hr_api.g_varchar2
428 ,p_updating_action_type => hr_api.g_varchar2
429 ,p_entry_information_category => hr_api.g_varchar2
430 ,p_entry_information1 => hr_api.g_varchar2
431 ,p_entry_information2 => hr_api.g_varchar2
432 ,p_entry_information3 => hr_api.g_varchar2
433 ,p_entry_information4 => hr_api.g_varchar2
434 ,p_entry_information5 => hr_api.g_varchar2
435 ,p_entry_information6 => hr_api.g_varchar2
436 ,p_entry_information7 => hr_api.g_varchar2
437 ,p_entry_information8 => hr_api.g_varchar2
438 ,p_entry_information9 => hr_api.g_varchar2
439 ,p_entry_information10 => hr_api.g_varchar2
440 ,p_entry_information11 => hr_api.g_varchar2
441 ,p_entry_information12 => hr_api.g_varchar2
442 ,p_entry_information13 => hr_api.g_varchar2
443 ,p_entry_information14 => hr_api.g_varchar2
444 ,p_entry_information15 => hr_api.g_varchar2
445 ,p_entry_information16 => hr_api.g_varchar2
446 ,p_entry_information17 => hr_api.g_varchar2
447 ,p_entry_information18 => hr_api.g_varchar2
448 ,p_entry_information19 => hr_api.g_varchar2
449 ,p_entry_information20 => hr_api.g_varchar2
450 ,p_entry_information21 => hr_api.g_varchar2
451 ,p_entry_information22 => hr_api.g_varchar2
452 ,p_entry_information23 => hr_api.g_varchar2
453 ,p_entry_information24 => hr_api.g_varchar2
454 ,p_entry_information25 => hr_api.g_varchar2
455 ,p_entry_information26 => hr_api.g_varchar2
456 ,p_entry_information27 => hr_api.g_varchar2
457 ,p_entry_information28 => hr_api.g_varchar2
458 ,p_entry_information29 => hr_api.g_varchar2
459 ,p_entry_information30 => hr_api.g_varchar2);
460
461 if g_debug then
462 hr_utility.set_location('Entering:'|| l_proc, 3);
463 end if;
464 -- Do not COMMIT here. COMMIT should be done from the OAF Application Only.
465 EXCEPTION
466 WHEN OTHERS THEN
467 RAISE;
468
469 END update_taxcard;
470 --
471 -- ----------------------------------------------------------------------------
472 -- |--------------------------< find_dt_upd_modes >----------------------------|
473 -- ----------------------------------------------------------------------------
474 -- {Start Of Comments}
475 --
476 -- Description:
477 -- This API returns the DT modes for pay_element_entries_f for a given
478 -- element_entry_id (base key value) on a specified date
479 --
480 -- Prerequisites:
481 -- The element_entry (p_base_key_value) must exist as of the effective date
482 -- of the change (p_effective_date).
483 --
484 -- In Parameters:
485 -- Name Reqd Type Description
486 -- p_effective_date Yes DATE The effective date of the
487 -- change.
488 -- p_base_key_value Yes NUMBER ID of the element entry.
489 --
490 --
491 -- Post Success:
492 --
493 -- The API sets the following out parameters:
494 --
495 -- Name Type Description
496 -- p_correction BOOLEAN True if correction mode is valid.
497 -- p_update BOOLEAN True if update mode is valid.
498 -- p_update_override BOOLEAN True if update override mode is valid.
499 -- p_update_change_insert BOOLEAN True if update change insert mode is
500 -- valid.
501 -- p_update_start_date DATE Start date for Update record.
502 -- p_update_end_date DATE End date for Update record.
503 -- p_override_start_date DATE Start date for Override.
504 -- p_override_end_date DATE End date for Overrride.
505 -- p_upd_chg_start_date DATE Start date for Update Change.
506 -- p_upd_chg_end_date DATE End date for Update Change.
507
508 -- Post Failure:
509 -- The API will raise an error.
510 --
511 -- Access Status:
512 -- Private. For Internal Development Use only.
513 --
514 -- {End Of Comments}
515 --
516 PROCEDURE find_dt_upd_modes
517 (p_effective_date IN DATE
518 ,p_base_key_value IN NUMBER
519 ,p_correction OUT NOCOPY BOOLEAN
520 ,p_update OUT NOCOPY BOOLEAN
521 ,p_update_override OUT NOCOPY BOOLEAN
522 ,p_update_change_insert OUT NOCOPY BOOLEAN
523 ,p_correction_start_date OUT NOCOPY DATE
524 ,p_correction_end_date OUT NOCOPY DATE
525 ,p_update_start_date OUT NOCOPY DATE
526 ,p_update_end_date OUT NOCOPY DATE
527 ,p_override_start_date OUT NOCOPY DATE
528 ,p_override_end_date OUT NOCOPY DATE
529 ,p_upd_chg_start_date OUT NOCOPY DATE
530 ,p_upd_chg_end_date OUT NOCOPY DATE
531 ) IS
532
533 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
534
535 BEGIN
536 if g_debug then
537 hr_utility.set_location('Entering:'|| l_proc, 1);
538 end if;
539 --
540 -- Call the corresponding datetrack api
541 --
542 dt_api.find_dt_upd_modes_and_dates(
543 p_effective_date => p_effective_date
544 ,p_base_table_name => 'pay_element_entries_f'
545 ,p_base_key_column => 'ELEMENT_ENTRY_ID'
546 ,p_base_key_value => p_base_key_value
547 ,p_correction => p_correction
548 ,p_update => p_update
549 ,p_update_override => p_update_override
550 ,p_update_change_insert => p_update_change_insert
551 ,p_correction_start_date => p_correction_start_date
552 ,p_correction_end_date => p_correction_end_date
553 ,p_update_start_date => p_update_start_date
554 ,p_update_end_date => p_update_end_date
555 ,p_override_start_date => p_override_start_date
556 ,p_override_end_date => p_override_end_date
557 ,p_upd_chg_start_date => p_upd_chg_start_date
558 ,p_upd_chg_end_date => p_upd_chg_end_date);
559 if g_debug then
560 hr_utility.set_location('Entering:'|| l_proc, 2);
561 end if;
562 --
563 --hr_utility.set_location(' Leaving:'||l_proc, 10);
564 EXCEPTION
565 WHEN OTHERS THEN
566 RAISE;
567 END find_dt_upd_modes;
568 --
569 -- -----------------------------------------------------------------------------
570 -- |--------------------------< get_global_value >-----------------------------|
571 -- -----------------------------------------------------------------------------
572 --
573 -- {Start of Comments}
574 --
575 -- Description:
576 -- Returns the value for the global on a given date.
577 --
578 -- Prerequisites:
579 -- None
580 --
581 -- In Parameters
582 -- Name Reqd Type Description
583 -- p_global_name Yes VARCHAR2 Assignment id
584 -- p_legislation_code Yes VARCHAR2 Legislation Code
585 -- p_effective_date Yes DATE Effective date
586 --
587 -- Post Success:
588 -- The value of the global of type FF_GLOBALS_F.GLOBAL_VALUE is returned
589 --
590 -- Post Failure:
591 -- An error is raised
592 --
593 -- Access Status:
594 -- Internal Development Use Only
595 --
596 -- {End of Comments}
597 --
598 FUNCTION get_global_value(
599 p_global_name VARCHAR2,
600 p_legislation_code VARCHAR2,
601 p_effective_date DATE)
602 RETURN ff_globals_f.global_value%TYPE IS
603
604 CURSOR csr_globals IS
605 SELECT global_value
606 FROM ff_globals_f
607 WHERE global_name = p_global_name
608 AND legislation_code = p_legislation_code
609 AND business_group_id IS NULL
610 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
611
612 l_global_value ff_globals_f.global_value%TYPE;
613 l_proc varchar2(72) := g_package||'get_global_value';
614
615 BEGIN
616 if g_debug then
617 hr_utility.set_location('Entering:'|| l_proc, 1);
618 end if;
619
620 OPEN csr_globals;
621 FETCH csr_globals INTO l_global_value;
622 CLOSE csr_globals;
623
624 if g_debug then
625 hr_utility.set_location('Entering:'|| l_proc, 2);
626 end if;
627
628 RETURN l_global_value;
629 END get_global_value;
630
631 END py_no_tax_card;
[Home] [Help]