[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ITAX_DPNT_INTERFACE_PKG
Source
1 package body pay_jp_itax_dpnt_interface_pkg as
2 /* $Header: pyjpitdp.pkb 120.1 2005/09/05 19:08:44 hikubo noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_itax_dpnt_interface_pkg.';
7 c_element_name constant pay_element_types_f.element_name%TYPE := 'YEA_DEP_EXM_PROC';
8 c_itax_elm_name constant pay_element_types_f.element_name%TYPE := 'COM_ITX_INFO';
9 c_itax_type_iv_name constant pay_input_values_f.name%TYPE := 'ITX_TYPE';
10 c_non_res_iv_name constant pay_input_values_f.name%TYPE := 'NRES_FLAG';
11 c_value_if_null_tbl constant pay_jp_bee_utility_pkg.t_varchar2_tbl
12 := pay_jp_bee_utility_pkg.entry_value_tbl('0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
13 c_non_res_elm_name constant pay_element_types_f.element_name%TYPE := 'COM_NRES_INFO';
14 c_non_res_date_iv_name constant pay_input_values_f.name%TYPE := 'NRES_START_DATE';
15 c_res_date_iv_name constant pay_input_values_f.name%TYPE := 'PROJECTED_RES_DATE';
16 --
17 -- Global Variables
18 --
19 g_element_type_id number;
20 g_itax_type_iv_id number;
21 g_non_res_iv_id number;
22 g_non_res_date_iv_id number;
23 g_res_date_iv_id number;
24 -- ----------------------------------------------------------------------------
25 -- |----------------------------< insert_session >----------------------------|
26 -- ----------------------------------------------------------------------------
27 procedure insert_session(p_effective_date in date)
28 is
29 begin
30 insert into fnd_sessions(
31 session_id,
32 effective_date)
33 values( userenv('sessionid'),
34 p_effective_date);
35 commit;
36 end insert_session;
37 -- ----------------------------------------------------------------------------
38 -- |----------------------------< delete_session >----------------------------|
39 -- ----------------------------------------------------------------------------
40 procedure delete_session
41 is
42 begin
43 delete
44 from fnd_sessions
45 where session_id = userenv('sessionid');
46 commit;
47 end delete_session;
48 -- ----------------------------------------------------------------------------
49 -- |-----------------------------< transfer_asg >-----------------------------|
50 -- ----------------------------------------------------------------------------
51 procedure transfer_asg(
52 p_business_group_id in number,
53 p_payroll_id in number,
54 p_assignment_id in number,
55 p_effective_date in date,
56 p_upload_date in date,
57 p_assignment_number in varchar2,
58 p_full_name in varchar2,
59 p_batch_id in number,
60 p_create_entry_if_not_exist in varchar2,
61 p_create_asg_set_for_errored in varchar2,
62 p_spouse_type_flag in varchar2,
63 p_dpnt_spouse_dsbl_type_flag in varchar2,
64 p_dpnts_flag in varchar2,
65 p_aged_dpnts_flag in varchar2,
66 p_aged_dpnt_parents_lt_flag in varchar2,
67 p_young_dpnts_flag in varchar2,
68 p_minor_dpnts_flag in varchar2,
69 p_dsbl_dpnts_flag in varchar2,
70 p_svr_dsbl_dpnts_flag in varchar2,
71 p_svr_dsbl_dpnts_lt_flag in varchar2,
72 p_assignment_set_id in out nocopy number,
73 p_assignment_set_name in out nocopy varchar2)
74 is
75 l_proc varchar2(61) := c_package || 'transfer_asg';
76 --
77 l_non_res_flag hr_lookups.lookup_code%TYPE;
78 l_itax_type hr_lookups.lookup_code%TYPE;
79 l_itax_dpnt_rec per_jp_ctr_utility_pkg.t_itax_dpnt_rec;
80 itax_type_is_null exception;
81 multiple_spouses exception;
82 --
83 l_ee_rec pay_jp_bee_utility_pkg.t_ee_rec;
84 l_eev_rec pay_jp_bee_utility_pkg.t_eev_rec;
85 l_new_value_tbl pay_jp_bee_utility_pkg.t_varchar2_tbl;
86 --
87 l_is_different boolean := false;
88 l_change_type hr_lookups.lookup_code%type;
89 l_write_all boolean := false;
90 l_batch_line_id number;
91 l_batch_line_ovn number;
92 --
93 l_non_res_date date;
94 l_res_date date;
95 --
96 procedure create_asg_set_amd
97 is
98 begin
99 if p_create_asg_set_for_errored = 'Y' then
100 if p_assignment_set_id is null then
101 hr_jp_ast_utility_pkg.create_asg_set_with_request_id(
102 p_prefix => 'REQUEST_ID_',
103 p_business_group_id => p_business_group_id,
104 p_payroll_id => p_payroll_id,
105 p_assignment_set_id => p_assignment_set_id,
106 p_assignment_set_name => p_assignment_set_name);
107 commit;
108 end if;
109 --
110 hr_jp_ast_utility_pkg.create_asg_set_amd(
111 p_assignment_set_id => p_assignment_set_id,
112 p_assignment_id => p_assignment_id,
113 p_include_or_exclude => 'I');
114 commit;
115 end if;
116 end create_asg_set_amd;
117 begin
118 hr_utility.set_location('Entering : ' || l_proc, 10);
119 --
120 hr_utility.trace('********************');
121 hr_utility.trace('assignment_id : ' || to_char(p_assignment_id));
122 hr_utility.trace('assignment_number : ' || p_assignment_number);
123 hr_utility.trace('upload_date : ' || to_char(p_upload_date));
124 --
125 -- Derive income tax dependent information from PAY_ELEMENT_ENTRIES_F.
126 --
127 pay_jp_bee_utility_pkg.get_ee(
128 p_assignment_id => p_assignment_id,
129 p_element_type_id => g_element_type_id,
130 p_effective_date => p_upload_date,
131 p_ee_rec => l_ee_rec,
132 p_eev_rec => l_eev_rec);
133 --
134 -- BEE line is created
135 -- 1. When element entry exists and one of input values to be transfered needs to be updated.
136 -- 2. When element entry does not exist and p_create_entry_if_not_exist is set to 'Y'.
137 --
138 if (l_ee_rec.element_entry_id is not null)
139 or (p_create_entry_if_not_exist = 'Y') then
140 --
141 -- Derive Non-resident flag as of Upload Date
142 --
143 l_non_res_date := pay_jp_balance_pkg.get_entry_value_date(
144 p_input_value_id => g_non_res_date_iv_id,
145 p_assignment_id => p_assignment_id,
146 p_effective_date => p_upload_date);
147 l_res_date := nvl(pay_jp_balance_pkg.get_entry_value_date(
148 p_input_value_id => g_res_date_iv_id,
149 p_assignment_id => p_assignment_id,
150 p_effective_date => p_upload_date), TO_DATE('47121231','YYYYMMDD'));
151
152 if l_non_res_date is not null then
153 if (l_non_res_date <= p_upload_date) and (p_upload_date < l_res_date) then
154 l_non_res_flag := 'Y';
155 else
156 l_non_res_flag := 'N';
157 end if;
158 else
159 l_non_res_flag := nvl(pay_jp_balance_pkg.get_entry_value_char(
160 p_input_value_id => g_non_res_iv_id,
161 p_assignment_id => p_assignment_id,
162 p_effective_date => p_upload_date), 'N');
163 end if;
164
165 --
166 -- Derive Income Tax Type as of Upload Date
167 -- If non-resident, set to "NON_RES".
168 --
169 if l_non_res_flag = 'Y' then
170 l_itax_type := 'NON_RES';
171 else
172 l_itax_type := pay_jp_balance_pkg.get_entry_value_char(
173 p_input_value_id => g_itax_type_iv_id,
174 p_assignment_id => p_assignment_id,
175 p_effective_date => p_upload_date);
176 --
177 -- If Income Tax Type is null for resident, raise error
178 --
179 if l_itax_type is null then
180 raise itax_type_is_null;
181 end if;
182 end if;
183 --
184 hr_utility.trace('non_res_flag : ' || l_non_res_flag);
185 hr_utility.trace('itax_type : ' || l_itax_type);
186 --
187 -- Derive income tax dependent information from CTR and CEI
188 -- as of Assessment Date, not Upload Date.
189 --
190 per_jp_ctr_utility_pkg.get_itax_dpnt_info(
191 p_assignment_id => p_assignment_id,
192 p_itax_type => l_itax_type,
193 p_effective_date => p_effective_date,
194 p_itax_dpnt_rec => l_itax_dpnt_rec);
195 --
196 -- If there're multiple spouses, skip processing for current assignment and output log.
197 --
198 if l_itax_dpnt_rec.multiple_spouses_warning then
199 hr_utility.trace('Multiple spouses. Skip processing');
200 raise multiple_spouses;
201 end if;
202 --
203 -- value_if_null is like value used as "default for" clause in FastFormula.
204 --
205 if p_spouse_type_flag = 'Y' then
206 l_new_value_tbl(5) := l_itax_dpnt_rec.spouse_type;
207 end if;
208 if p_dpnt_spouse_dsbl_type_flag = 'Y' then
209 l_new_value_tbl(6) := l_itax_dpnt_rec.dpnt_spouse_dsbl_type;
210 end if;
211 if p_dpnts_flag = 'Y' then
212 l_new_value_tbl(7) := fnd_number.number_to_canonical(l_itax_dpnt_rec.dpnts);
213 end if;
214 if p_aged_dpnts_flag = 'Y' then
215 l_new_value_tbl(8) := fnd_number.number_to_canonical(l_itax_dpnt_rec.aged_dpnts);
216 end if;
217 if p_aged_dpnt_parents_lt_flag = 'Y' then
218 l_new_value_tbl(9) := fnd_number.number_to_canonical(l_itax_dpnt_rec.aged_dpnt_parents_lt);
219 end if;
220 if p_young_dpnts_flag = 'Y' then
221 l_new_value_tbl(10) := fnd_number.number_to_canonical(l_itax_dpnt_rec.young_dpnts);
222 end if;
223 if p_minor_dpnts_flag = 'Y' then
224 l_new_value_tbl(11) := fnd_number.number_to_canonical(l_itax_dpnt_rec.minor_dpnts);
225 end if;
226 if p_dsbl_dpnts_flag = 'Y' then
227 l_new_value_tbl(12) := fnd_number.number_to_canonical(l_itax_dpnt_rec.dsbl_dpnts);
228 end if;
229 if p_svr_dsbl_dpnts_flag = 'Y' then
230 l_new_value_tbl(13) := fnd_number.number_to_canonical(l_itax_dpnt_rec.svr_dsbl_dpnts);
231 end if;
232 if p_svr_dsbl_dpnts_lt_flag = 'Y' then
233 l_new_value_tbl(14) := fnd_number.number_to_canonical(l_itax_dpnt_rec.svr_dsbl_dpnts_lt);
234 end if;
235 --
236 -- Check whether the new_value_tbl is different from eev_rec.entry_value_tbl.
237 -- The following procedure changes new_value_tbl based on eev.
238 --
239 pay_jp_bee_utility_pkg.set_eev(
240 p_ee_rec => l_ee_rec,
241 p_eev_rec => l_eev_rec,
242 p_value_if_null_tbl => c_value_if_null_tbl,
243 p_new_value_tbl => l_new_value_tbl,
244 p_is_different => l_is_different);
245 --
246 if l_is_different then
247 --
248 -- Write to output file
249 --
250 if l_ee_rec.element_entry_id is null then
251 hr_utility.trace('EE not exist. Create EE.');
252 --
253 -- If element does not exist, "Insert" mode.
254 -- Also output all entry values to be transfered.
255 --
256 l_change_type := 'I';
257 l_write_all := true;
258 else
259 hr_utility.trace('EE exists. Compare Start.');
260 --
261 -- When the Upload Date is the same date as ESD,
262 -- it is "Correction" mode, or "Update".
263 -- In this case, only the entry values to be changed are shown in output file.
264 --
265 if l_ee_rec.effective_start_date = p_upload_date then
266 l_change_type := 'C';
267 else
268 l_change_type := 'U';
269 end if;
270 end if;
271 pay_jp_bee_utility_pkg.out(
272 p_full_name => p_full_name,
273 p_assignment_number => p_assignment_number,
274 p_effective_date => p_upload_date,
275 p_change_type => l_change_type,
276 p_eev_rec => l_eev_rec,
277 p_new_value_tbl => l_new_value_tbl,
278 p_write_all => l_write_all);
279 --
280 -- Create BEE Line
281 --
282 l_eev_rec.entry_value_tbl := l_new_value_tbl;
283 pay_jp_bee_utility_pkg.create_batch_line(
284 p_batch_id => p_batch_id,
285 p_assignment_id => p_assignment_id,
286 p_assignment_number => p_assignment_number,
287 p_element_type_id => g_element_type_id,
288 p_element_name => c_element_name,
289 p_effective_date => p_upload_date,
290 p_ee_rec => l_ee_rec,
291 p_eev_rec => l_eev_rec,
292 p_batch_line_id => l_batch_line_id,
293 p_object_version_number => l_batch_line_ovn);
294 commit;
295 end if;
296 end if;
297 --
298 hr_utility.set_location('Leaving : ' || l_proc, 20);
299 exception
300 when itax_type_is_null then
301 pay_jp_bee_utility_pkg.log(
302 p_full_name => p_full_name,
303 p_assignment_number => p_assignment_number,
304 p_application_short_name => 'PAY',
305 p_message_name => 'PAY_JP_ITAX_DPNT_NO_ITAX_TYPE',
306 p_token1 => 'EFFECTIVE_DATE',
307 p_value1 => fnd_date.date_to_chardate(p_upload_date));
308 create_asg_set_amd;
309 --
310 hr_utility.set_location('Leaving : ' || l_proc, 25);
311 when multiple_spouses then
312 pay_jp_bee_utility_pkg.log(
313 p_full_name => p_full_name,
314 p_assignment_number => p_assignment_number,
315 p_application_short_name => 'PAY',
316 p_message_name => 'PAY_JP_ITAX_DPNT_MULTI_SPOUSES',
317 p_token1 => 'EFFECTIVE_DATE',
318 p_value1 => fnd_date.date_to_chardate(p_effective_date));
319 create_asg_set_amd;
320 --
321 hr_utility.set_location('Leaving : ' || l_proc, 26);
322 end transfer_asg;
323 -- ----------------------------------------------------------------------------
324 -- |-----------------------< transfer_from_cei_to_bee >-----------------------|
325 -- ----------------------------------------------------------------------------
326 procedure transfer_from_cei_to_bee(
327 p_errbuf out nocopy varchar2,
328 p_retcode out nocopy varchar2,
329 p_business_group_id in number,
330 p_payroll_id in number,
331 p_time_period_id in number,
332 p_effective_date in varchar2,
333 p_upload_date in varchar2,
334 p_batch_name in varchar2,
335 p_action_if_exists in varchar2,
336 p_reject_if_future_changes in varchar2,
337 p_date_effective_changes in varchar2,
338 p_purge_after_transfer in varchar2,
339 p_assignment_set_id in number,
340 p_create_entry_if_not_exist in varchar2,
341 p_create_asg_set_for_errored in varchar2,
342 p_spouse_type_flag in varchar2,
343 p_dpnt_spouse_dsbl_type_flag in varchar2,
344 p_dpnts_flag in varchar2,
345 p_aged_dpnts_flag in varchar2,
346 p_aged_dpnt_parents_lt_flag in varchar2,
347 p_young_dpnts_flag in varchar2,
348 p_minor_dpnts_flag in varchar2,
349 p_dsbl_dpnts_flag in varchar2,
350 p_svr_dsbl_dpnts_flag in varchar2,
351 p_svr_dsbl_dpnts_lt_flag in varchar2)
352 is
353 l_proc varchar2(61) := c_package || 'transfer_from_cei_to_bee';
354 --
355 l_effective_date date := fnd_date.canonical_to_date(p_effective_date);
356 l_upload_date date := fnd_date.canonical_to_date(p_upload_date);
357 l_period_start_date date;
358 l_period_end_date date;
359 --
360 l_date_effective_changes pay_batch_headers.date_effective_changes%TYPE := p_date_effective_changes;
361 l_batch_id number;
362 l_batch_ovn number;
363 --
364 l_asg_rec hr_jp_ast_utility_pkg.t_asg_rec;
365 l_assignment_set_id number;
366 l_assignment_set_name hr_assignment_sets.assignment_set_name%type;
367 begin
368 -- hr_utility.trace_on('F', 'TTAGAWA');
369 hr_utility.set_location('Entering : ' || l_proc, 10);
370 --
371 -- Validate Input Parameters
372 --
373 -- If all input values are out of scope by transfer process,
374 -- stop processing and raise error.
375 --
376 if p_spouse_type_flag = 'N'
377 and p_dpnt_spouse_dsbl_type_flag = 'N'
378 and p_dpnts_flag = 'N'
379 and p_aged_dpnts_flag = 'N'
380 and p_aged_dpnt_parents_lt_flag = 'N'
381 and p_young_dpnts_flag = 'N'
382 and p_minor_dpnts_flag = 'N'
383 and p_dsbl_dpnts_flag = 'N'
384 and p_svr_dsbl_dpnts_flag = 'N'
385 and p_svr_dsbl_dpnts_lt_flag = 'N' then
386 fnd_message.set_name('PAY', 'PAY_JP_BEE_UTIL_NO_TARGET_IV');
387 fnd_message.raise_error;
388 end if;
389 --
390 -- Validate p_time_period_id and p_upload_date
391 --
392 pay_jp_bee_utility_pkg.chk_upload_date(
393 p_time_period_id => p_time_period_id,
394 p_upload_date => l_upload_date,
395 p_period_start_date => l_period_start_date,
396 p_period_end_date => l_period_end_date);
397 --
398 -- Create BEE Header
399 -- Batch Source : <Request ID>
400 -- Batch Reference : <Element Name>
401 --
402 -- Validate Date Effective Changes
403 --
404 pay_jp_bee_utility_pkg.chk_date_effective_changes(
405 p_action_if_exists => p_action_if_exists,
406 p_reject_if_future_changes => p_reject_if_future_changes,
407 p_date_effective_changes => l_date_effective_changes);
408 --
409 -- Bug.2760646
410 -- Need to populate record into fnd_sessions
411 -- because the formula with this assignment set possibly calls
412 -- dbis which includes fnd_sessions table, e.g. PER_EMP_NUMBER.
413 -- If no records in fnd_sessions, PER_EMP_NUMBER will raise error
414 -- because FF_USER_ENTITIES.NOTFOUND_ALLOWED_FLAG is "N".
415 -- Note FND_SESSIONS.EFFECTIVE_DATE is not changed during the processing
416 -- while the context DATE_EARNED changes for each assignment.
417 --
418 insert_session(l_effective_date);
419 --
420 pay_batch_element_entry_api.create_batch_header(
421 p_validate => false,
422 p_session_date => l_effective_date,
423 p_batch_name => substrb(p_batch_name, 1, 30),
424 p_business_group_id => p_business_group_id,
425 p_action_if_exists => p_action_if_exists,
426 p_batch_reference => substrb(c_element_name, 1, 30),
427 p_batch_source => substrb(to_char(fnd_global.conc_request_id), 1, 30),
428 p_date_effective_changes => l_date_effective_changes,
429 p_purge_after_transfer => p_purge_after_transfer,
430 p_reject_if_future_changes => p_reject_if_future_changes,
431 p_batch_id => l_batch_id,
432 p_object_version_number => l_batch_ovn);
433 commit;
434 --
435 hr_utility.trace('batch_id : ' || to_char(l_batch_id));
436 --
437 -- Initialize Global Variables
438 --
439 g_element_type_id := hr_jp_id_pkg.element_type_id(c_element_name, p_business_group_id);
440 g_itax_type_iv_id := hr_jp_id_pkg.input_value_id(c_itax_elm_name, c_itax_type_iv_name, p_business_group_id);
441 g_non_res_iv_id := hr_jp_id_pkg.input_value_id(c_itax_elm_name, c_non_res_iv_name, p_business_group_id);
442 --
443 g_non_res_date_iv_id := hr_jp_id_pkg.input_value_id(c_non_res_elm_name, c_non_res_date_iv_name, p_business_group_id);
444 g_res_date_iv_id := hr_jp_id_pkg.input_value_id(c_non_res_elm_name, c_res_date_iv_name, p_business_group_id);
445 --
446 -- Derive payroll assignments to be processed
447 --
448 hr_jp_ast_utility_pkg.pay_asgs(
449 p_payroll_id => p_payroll_id,
450 p_effective_date => l_effective_date,
451 p_start_date => l_upload_date,
452 p_end_date => l_period_end_date,
453 p_assignment_set_id => p_assignment_set_id,
454 p_asg_rec => l_asg_rec);
455 --
456 -- Assignment to be transfered Loop
457 --
458 for i in 1..l_asg_rec.assignment_id_tbl.count loop
459 transfer_asg(
460 p_business_group_id => p_business_group_id,
461 p_payroll_id => p_payroll_id,
462 p_assignment_id => l_asg_rec.assignment_id_tbl(i),
463 p_effective_date => l_effective_date,
464 p_upload_date => l_asg_rec.effective_date_tbl(i),
465 p_assignment_number => l_asg_rec.assignment_number_tbl(i),
466 p_full_name => l_asg_rec.full_name_tbl(i),
467 p_batch_id => l_batch_id,
468 p_create_entry_if_not_exist => p_create_entry_if_not_exist,
469 p_create_asg_set_for_errored => p_create_asg_set_for_errored,
470 p_spouse_type_flag => p_spouse_type_flag,
471 p_dpnt_spouse_dsbl_type_flag => p_dpnt_spouse_dsbl_type_flag,
472 p_dpnts_flag => p_dpnts_flag,
473 p_aged_dpnts_flag => p_aged_dpnts_flag,
474 p_aged_dpnt_parents_lt_flag => p_aged_dpnt_parents_lt_flag,
475 p_young_dpnts_flag => p_young_dpnts_flag,
476 p_minor_dpnts_flag => p_minor_dpnts_flag,
477 p_dsbl_dpnts_flag => p_dsbl_dpnts_flag,
478 p_svr_dsbl_dpnts_flag => p_svr_dsbl_dpnts_flag,
479 p_svr_dsbl_dpnts_lt_flag => p_svr_dsbl_dpnts_lt_flag,
480 p_assignment_set_id => l_assignment_set_id,
481 p_assignment_set_name => l_assignment_set_name);
482 end loop;
483 --
484 -- Write the assignment_set_name created into log file
485 --
486 if l_assignment_set_id is not null then
487 fnd_message.set_name('PAY', 'PAY_JP_BEE_UTIL_ASG_SET_CREATE');
488 fnd_message.set_token('ASSIGNMENT_SET_NAME', l_assignment_set_name);
489 fnd_file.put_line(fnd_file.log, fnd_message.get);
490 end if;
491 --
492 -- When no batch lines are created, delete batch header and set message as errbuf.
493 --
494 if pay_jp_bee_utility_pkg.g_num_of_outs = 0 then
495 hr_utility.trace('BEE Header deleted');
496 --
497 pay_batch_element_entry_api.delete_batch_header(
498 p_validate => false,
499 p_batch_id => l_batch_id,
500 p_object_version_number => l_batch_ovn);
501 commit;
502 --
503 fnd_message.set_name('PAY', 'PAY_JP_BEE_UTIL_NO_ASGS');
504 p_errbuf := fnd_message.get;
505 fnd_file.put_line(fnd_file.log, p_errbuf);
506 end if;
507 --
508 -- If at least 1 assignment failed to process, set concurrent request status "Incomplete".
509 --
510 if pay_jp_bee_utility_pkg.g_num_of_logs > 0 then
511 p_retcode := 1;
512 else
513 p_retcode := 0;
514 end if;
515 --
516 -- Bug.2760646
517 --
518 delete_session;
519 --
520 hr_utility.trace('retcode : ' || p_retcode);
521 hr_utility.trace('errbuf : ' || p_errbuf);
522 hr_utility.set_location('Leaving : ' || l_proc, 20);
523 end transfer_from_cei_to_bee;
524 --
525 end pay_jp_itax_dpnt_interface_pkg;