[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_CREATE_CMA_PKG
Source
1 package body pay_jp_create_cma_pkg as
2 /* $Header: pyjpcmap.pkb 120.8 2011/05/31 03:38:44 keyazawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'pay_jp_create_cma_pkg.';
7 c_cma_ele_name constant pay_element_types_f.element_name%TYPE := 'SAL_CMA_PROC';
8 c_itax_elm_name constant pay_element_types_f.element_name%TYPE := 'COM_ITX_INFO';
9 c_non_res_iv_name constant pay_input_values_f.name%TYPE := 'NRES_FLAG';
10 -- need to set irregular def val except hr_api.g_varchar2 (entry_value_tbl exclude setting default value of parameter)
11 c_def_val varchar2(1000) := to_char(hr_api.g_number);
12 c_value_if_null_tbl constant pay_jp_bee_utility_pkg.t_varchar2_tbl
13 := pay_jp_bee_utility_pkg.entry_value_tbl(c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val,c_def_val);
14 c_non_taxable_GlbVal_name constant varchar2(80) := 'G_SAL_CMA_PUBLIC_TRANSPORT_NTXBL_ERN_MAX';
15 c_non_res_elm_name constant pay_element_types_f.element_name%TYPE := 'COM_NRES_INFO';
16 c_non_res_date_iv_name constant pay_input_values_f.name%TYPE := 'NRES_START_DATE';
17 c_res_date_iv_name constant pay_input_values_f.name%TYPE := 'PROJECTED_RES_DATE';
18 c_elm pay_element_types_f.element_name%TYPE;
19 c_elm_tl pay_element_types_f_tl.element_name%TYPE;
20 c_elm_tl_max number := 30;
21 --
22 -- Global Variables
23 --
24 g_element_type_id number;
25 g_non_res_iv_id number;
26 g_non_taxable_limit_1mth number;
27 g_non_res_date_iv_id number;
28 g_res_date_iv_id number;
29 -- ----------------------------------------------------------------------------
30 -- |----------------------------< insert_session >----------------------------|
31 -- ----------------------------------------------------------------------------
32 procedure insert_session(p_effective_date in date)
33 is
34 begin
35 insert into fnd_sessions(
36 session_id,
37 effective_date)
38 values( userenv('sessionid'),
39 p_effective_date);
40 commit;
41 end insert_session;
42 -- ----------------------------------------------------------------------------
43 -- |----------------------------< delete_session >----------------------------|
44 -- ----------------------------------------------------------------------------
45 procedure delete_session
46 is
47 begin
48 delete
49 from fnd_sessions
50 where session_id = userenv('sessionid');
51 commit;
52 end delete_session;
53 --
54 function get_elm_tl(
55 p_elm_name in varchar2)
56 return varchar2
57 is
58 --
59 o_elm_tl pay_element_types_f_tl.element_name%type;
60 --
61 -- support only session lang based on case forms and srs session lang is same
62 cursor csr_elm_tl
63 is
64 select /*+ ORDERD */
65 pett.element_name
66 from pay_element_types_f pet,
67 pay_element_types_f_tl pett
68 where pet.element_name = p_elm_name
69 and pet.legislation_code = 'JP'
70 and pett.element_type_id = pet.element_type_id
71 and pett.language = userenv('LANG');
72 --
73 begin
74 --
75 o_elm_tl := c_elm_tl;
76 --
77 if c_elm is null
78 or c_elm <> p_elm_name then
79 --
80 open csr_elm_tl;
81 fetch csr_elm_tl into o_elm_tl;
82 close csr_elm_tl;
83 --
84 c_elm := p_elm_name;
85 --
86 end if;
87 --
88 return o_elm_tl;
89 --
90 end get_elm_tl;
91 --
92 -- ----------------------------------------------------------------------------
93 -- |-----------------------------< transfer_asg >-----------------------------|
94 -- ----------------------------------------------------------------------------
95 procedure transfer_asg(
96 p_business_group_id in number,
97 p_payroll_id in number,
98 p_assignment_id in number,
99 p_payment_date in date, -- bug 4029525
100 p_effective_date in date,
101 p_upload_date in date,
102 p_assignment_number in varchar2,
103 p_full_name in varchar2,
104 p_batch_id in number,
105 p_create_entry_if_not_exist in varchar2,
106 p_create_asg_set_for_errored in varchar2,
107 p_assignment_set_id in out nocopy number,
108 p_assignment_set_name in out nocopy varchar2)
109 is
110 l_proc varchar2(61);
111 --
112 l_non_res_flag hr_lookups.lookup_code%TYPE;
113 l_cma_rec per_jp_cma_utility_pkg.cma_rec;
114 -- itax_type_is_null exception;
115 multiple_traffic_tool_rec exception;
116 --
117 l_ee_rec pay_jp_bee_utility_pkg.t_ee_rec;
118 l_eev_rec pay_jp_bee_utility_pkg.t_eev_rec;
119 l_new_value_tbl pay_jp_bee_utility_pkg.t_varchar2_tbl;
120 --
121 l_is_different boolean := false;
122 l_record_exist boolean := false;
123 l_change_type hr_lookups.lookup_code%type;
124 l_write_all boolean := false;
125 l_batch_line_id number;
126 l_batch_line_ovn number;
127 --
128 l_index number;
129 --
130 l_non_res_date date;
131 l_res_date date;
132 --
133 procedure create_asg_set_amd
134 is
135 begin
136 if p_create_asg_set_for_errored = 'Y' then
137 if p_assignment_set_id is null then
138 hr_jp_ast_utility_pkg.create_asg_set_with_request_id(
139 p_prefix => 'REQUEST_ID_',
140 p_business_group_id => p_business_group_id,
141 p_payroll_id => p_payroll_id,
142 p_assignment_set_id => p_assignment_set_id,
143 p_assignment_set_name => p_assignment_set_name);
144 commit;
145 end if;
146 --
147 hr_jp_ast_utility_pkg.create_asg_set_amd(
148 p_assignment_set_id => p_assignment_set_id,
149 p_assignment_id => p_assignment_id,
150 p_include_or_exclude => 'I');
151 commit;
152 end if;
153 end create_asg_set_amd;
154 begin
155 --
156 l_proc := c_package || 'transfer_asg';
157 --
158 hr_utility.set_location('Entering : ' || l_proc, 10);
159 --
160 hr_utility.trace('********************');
161 hr_utility.trace('assignment_id : ' || to_char(p_assignment_id));
162 hr_utility.trace('assignment_number : ' || p_assignment_number);
163 hr_utility.trace('upload_date : ' || to_char(p_upload_date));
164 --
165 -- Derive commutation allowance information from PAY_ELEMENT_ENTRIES_F.
166 --
167 pay_jp_bee_utility_pkg.get_ee(
168 p_assignment_id => p_assignment_id,
169 p_element_type_id => g_element_type_id,
170 p_effective_date => p_upload_date,
171 p_ee_rec => l_ee_rec,
172 p_eev_rec => l_eev_rec);
173 --
174 -- BEE line is created
175 -- 1. When element entry exists and one of input values to be transfered needs to be updated.
176 -- 2. When element entry does not exist and p_create_entry_if_not_exist is set to 'Y'.
177 --
178 -- if (l_ee_rec.element_entry_id is not null)
179 -- or (p_create_entry_if_not_exist = 'Y') then
180
181 --
182 -- Derive Non-resident flag as of Upload Date
183 --
184 l_non_res_date := pay_jp_balance_pkg.get_entry_value_date(
185 p_input_value_id => g_non_res_date_iv_id,
186 p_assignment_id => p_assignment_id,
187 p_effective_date => p_upload_date);
188 l_res_date := nvl(pay_jp_balance_pkg.get_entry_value_date(
189 p_input_value_id => g_res_date_iv_id,
190 p_assignment_id => p_assignment_id,
191 p_effective_date => p_upload_date), TO_DATE('47121231','YYYYMMDD'));
192
193 if l_non_res_date is not null then
194 if (l_non_res_date <= p_upload_date) and (p_upload_date < l_res_date) then
195 l_non_res_flag := 'Y';
196 else
197 l_non_res_flag := 'N';
198 end if;
199 else
200 l_non_res_flag := nvl(pay_jp_balance_pkg.get_entry_value_char(
201 p_input_value_id => g_non_res_iv_id,
202 p_assignment_id => p_assignment_id,
203 p_effective_date => p_upload_date), 'N');
204 end if;
205
206 --
207 hr_utility.trace('non_res_flag : ' || l_non_res_flag);
208 --
209 -- Derive commutation allowance information from element entries.
210 --
211 per_jp_cma_utility_pkg.get_cma_info(
212 p_business_group_id => p_business_group_id,
213 p_assignment_id => p_assignment_id,
214 p_payment_date => p_payment_date, -- bug 4029525
215 p_effective_date => p_effective_date,
216 p_non_taxable_limit_1mth => g_non_taxable_limit_1mth,
217 p_cma_rec => l_cma_rec,
218 p_record_exist => l_record_exist);
219 --
220 -- If there're multiple traffic tool record, skip processing for current assignment and output log.
221 --
222 if l_cma_rec.multiple_entry_warning then
223 hr_utility.trace('Multiple car. Skip processing');
224 raise multiple_traffic_tool_rec;
225 end if;
226 --
227 if (l_ee_rec.element_entry_id is not null) or (l_record_exist) then
228 --
229 -- value_if_null is like value used as "default for" clause in FastFormula.
230 --
231 l_new_value_tbl(1) := to_char(l_cma_rec.non_taxable_amount);
232 l_new_value_tbl(2) := to_char(l_cma_rec.mtr_non_taxable_amount);
233 if l_non_res_flag = 'N' then
234 l_new_value_tbl(3) := to_char(l_cma_rec.taxable_amount);
235 l_new_value_tbl(4) := to_char(l_cma_rec.mtr_taxable_amount);
236 -- bug.5438168. Changed to set null instead of '0'.
237 l_new_value_tbl(5) := null;
238 l_new_value_tbl(6) := null;
239 else
240 -- bug.5438168. Changed to set null instead of '0'.
241 l_new_value_tbl(3) := null;
242 l_new_value_tbl(4) := null;
243 l_new_value_tbl(5) := to_char(l_cma_rec.taxable_amount);
244 l_new_value_tbl(6) := to_char(l_cma_rec.mtr_taxable_amount);
245 end if;
246 l_new_value_tbl(7) := to_char(l_cma_rec.si_wage);
247 l_new_value_tbl(8) := to_char(l_cma_rec.mtr_si_wage);
248 l_new_value_tbl(9) := to_char(l_cma_rec.si_wage_adj);
249 l_new_value_tbl(10) := to_char(l_cma_rec.mtr_si_wage_adj);
250 l_new_value_tbl(11) := to_char(l_cma_rec.si_fixed_wage);
251 l_new_value_tbl(12) := to_char(l_cma_rec.ui_wage_adj);
252 --
253 -- Check whether the new_value_tbl is different from eev_rec.entry_value_tbl.
254 -- The following procedure changes new_value_tbl based on eev.
255 --
256 -- bug.5438168. c_value_if_null_tbl is changed to use hr_api.g_varchar2
257 -- because new behavior sets null values for unnecessary input values.
258 --
259 pay_jp_bee_utility_pkg.set_eev(
260 p_ee_rec => l_ee_rec,
261 p_eev_rec => l_eev_rec,
262 p_value_if_null_tbl => c_value_if_null_tbl,
263 p_new_value_tbl => l_new_value_tbl,
264 p_is_different => l_is_different);
265 --
266 if l_is_different then
267 --
268 -- Write to output file
269 --
270 if l_ee_rec.element_entry_id is null then
271 hr_utility.trace('EE not exist. Create EE.');
272 --
273 -- If element does not exist, "Insert" mode.
274 -- Also output all entry values to be transfered.
275 --
276 l_change_type := 'I';
277 l_write_all := true;
278 else
279 hr_utility.trace('EE exists. Compare Start.');
280 --
281 -- Since the element is nonrecurring type, "Correction" mode.
282 -- In this case, only the entry values to be changed are shown in output file.
283 --
284 l_change_type := 'C';
285 end if;
286 pay_jp_bee_utility_pkg.out(
287 p_full_name => p_full_name,
288 p_assignment_number => p_assignment_number,
289 p_effective_date => p_upload_date,
290 p_change_type => l_change_type,
291 p_eev_rec => l_eev_rec,
292 p_new_value_tbl => l_new_value_tbl,
293 p_write_all => l_write_all);
294 --
295 -- Create BEE Line
296 --
297 /* bug.5438168. commented out.
298 l_index := l_new_value_tbl.first;
299 while l_index is not null loop
300 hr_utility.trace('Set zero' || to_char(l_index));
301 --
302 if l_new_value_tbl(l_index) is null then
303 l_new_value_tbl(l_index) := c_value_if_null_tbl(l_index);
304 end if;
305 --
306 l_index := l_new_value_tbl.next(l_index);
307 end loop;
308 */
309 l_eev_rec.entry_value_tbl := l_new_value_tbl;
310 pay_jp_bee_utility_pkg.create_batch_line(
311 p_batch_id => p_batch_id,
312 p_assignment_id => p_assignment_id,
313 p_assignment_number => p_assignment_number,
314 p_element_type_id => g_element_type_id,
315 p_element_name => c_cma_ele_name,
316 p_effective_date => p_upload_date,
317 p_ee_rec => l_ee_rec,
318 p_eev_rec => l_eev_rec,
319 p_batch_line_id => l_batch_line_id,
320 p_object_version_number => l_batch_line_ovn);
321 commit;
322 end if;
323 end if;
324 --
325 hr_utility.set_location('Leaving : ' || l_proc, 20);
326 exception
327 when multiple_traffic_tool_rec then
328 pay_jp_bee_utility_pkg.log(
329 p_full_name => p_full_name,
330 p_assignment_number => p_assignment_number,
331 p_application_short_name => 'PAY',
332 p_message_name => '',
333 p_token1 => 'EFFECTIVE_DATE',
334 p_value1 => fnd_date.date_to_chardate(p_effective_date));
335 create_asg_set_amd;
336 --
337 hr_utility.set_location('Leaving : ' || l_proc, 26);
338 --
339 when others then
340 --
341 pay_jp_bee_utility_pkg.log(
342 p_full_name => p_full_name,
343 p_assignment_number => p_assignment_number,
344 p_application_short_name => 'PAY',
345 p_message_name => '',
346 p_token1 => 'ERROR',
347 p_value1 => to_char(sqlcode)||':'||substrb(sqlerrm,1,100));
348 --
349 hr_utility.trace('transfer_asg ass num : ' || p_assignment_number);
350 --
351 hr_utility.trace('transfer_asg erro code : ' || to_char(sqlcode));
352 hr_utility.trace('transfer_asg erro msg : ' || substrb(sqlerrm,1,100));
353 --
354 hr_utility.set_location('Leaving : ' || l_proc, 30);
355 --
356 end transfer_asg;
357 -- ----------------------------------------------------------------------------
358 -- |---------------------< transfer_from_cma_info_to_bee >--------------------|
359 -- ----------------------------------------------------------------------------
360 procedure transfer_from_cma_info_to_bee(
361 p_errbuf out nocopy varchar2,
362 p_retcode out nocopy varchar2,
363 p_business_group_id in number,
364 p_payroll_id in number,
365 p_time_period_id in number,
366 ---- bug 4029525 ----
367 -- changed parameter order
368 p_payment_date in varchar2,
369 p_upload_date in varchar2,
370 p_effective_date in varchar2,
371 ---------------------
372 p_batch_name in varchar2,
373 p_action_if_exists in varchar2,
374 p_reject_if_future_changes in varchar2,
375 p_date_effective_changes in varchar2,
376 p_purge_after_transfer in varchar2,
377 p_assignment_set_id in number,
378 p_create_entry_if_not_exist in varchar2,
379 p_create_asg_set_for_errored in varchar2)
380 is
381 l_proc varchar2(61);
382 --
383 l_payment_date date; -- bug 4029525
384 l_effective_date date;
385 l_upload_date date;
386 l_period_start_date date;
387 l_period_end_date date;
388 --
389 l_date_effective_changes pay_batch_headers.date_effective_changes%TYPE;
390 l_batch_id number;
391 l_batch_ovn number;
392 --
393 l_asg_rec hr_jp_ast_utility_pkg.t_asg_rec;
394 l_assignment_set_id number;
395 l_assignment_set_name hr_assignment_sets.assignment_set_name%type;
396 begin
397 --
398 l_proc := c_package || 'transfer_process_main';
399 --
400 l_payment_date := fnd_date.canonical_to_date(p_payment_date); -- bug 4029525
401 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
402 l_upload_date := fnd_date.canonical_to_date(p_upload_date);
403 --
404 l_date_effective_changes := p_date_effective_changes;
405 --
406 hr_utility.set_location('Entering : ' || l_proc, 10);
407 --
408 -- Validate p_time_period_id and p_upload_date
409 --
410 pay_jp_bee_utility_pkg.chk_upload_date(
411 p_time_period_id => p_time_period_id,
412 p_upload_date => l_upload_date,
413 p_period_start_date => l_period_start_date,
414 p_period_end_date => l_period_end_date);
415 --
416 -- Create BEE Header
417 -- Batch Source : <Request ID>
418 -- Batch Reference : <Element Name>
419 --
420 -- Validate Date Effective Changes
421 --
422 pay_jp_bee_utility_pkg.chk_date_effective_changes(
423 p_action_if_exists => p_action_if_exists,
424 p_reject_if_future_changes => p_reject_if_future_changes,
425 p_date_effective_changes => l_date_effective_changes);
426 --
427 -- Bug.2760646
428 -- Need to populate record into fnd_sessions
429 -- because the formula with this assignment set possibly calls
430 -- dbis which includes fnd_sessions table, e.g. PER_EMP_NUMBER.
431 -- If no records in fnd_sessions, PER_EMP_NUMBER will raise error
432 -- because FF_USER_ENTITIES.NOTFOUND_ALLOWED_FLAG is "N".
433 -- Note FND_SESSIONS.EFFECTIVE_DATE is not changed during the processing
434 -- while the context DATE_EARNED changes for each assignment.
435 --
436 ---- bug 4029525 ----
437 -- insert_session(l_effective_date);
438 insert_session(l_payment_date);
439 ---------------------
440 --
441 c_elm_tl := get_elm_tl(c_cma_ele_name);
442 if userenv('LANG') = 'JA' then
443 c_elm_tl_max := trunc(30/lengthb(to_multi_byte(' ')));
444 end if;
445 --
446 --
447 pay_batch_element_entry_api.create_batch_header(
448 p_validate => false,
449 ---- bug 4029525 ----
450 -- p_session_date => l_effective_date,
451 p_session_date => l_payment_date,
452 ---------------------
453 p_batch_name => substrb(p_batch_name, 1, 30),
454 p_business_group_id => p_business_group_id,
455 p_action_if_exists => p_action_if_exists,
456 p_batch_reference => substr(c_elm_tl, 1, c_elm_tl_max),
457 p_batch_source => substrb(to_char(fnd_global.conc_request_id), 1, 30),
458 p_date_effective_changes => l_date_effective_changes,
459 p_purge_after_transfer => p_purge_after_transfer,
460 p_reject_if_future_changes => p_reject_if_future_changes,
461 p_batch_id => l_batch_id,
462 p_object_version_number => l_batch_ovn);
463 commit;
464 --
465 hr_utility.trace('batch_id : ' || to_char(l_batch_id));
466 --
467 -- Initialize Global Variables
468 --
469 g_element_type_id := hr_jp_id_pkg.element_type_id(c_cma_ele_name, p_business_group_id);
470 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);
471 --
472 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);
473 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);
474 --
475 select to_number(global_value)
476 into g_non_taxable_limit_1mth
477 from ff_globals_f
478 where
479 ---- bug 4029525 ----
480 -- l_effective_date
481 l_payment_date
482 ---------------------
483 between effective_start_date and effective_end_date
484 and global_name = c_non_taxable_GlbVal_name;
485 --
486 -- Derive payroll assignments to be processed
487 --
488 hr_jp_ast_utility_pkg.pay_asgs(
489 p_payroll_id => p_payroll_id,
490 ---- bug 4029525 ----
491 -- p_effective_date => l_effective_date,
492 p_effective_date => l_payment_date,
493 ---------------------
494 p_start_date => l_upload_date,
495 p_end_date => l_period_end_date,
496 p_assignment_set_id => p_assignment_set_id,
497 p_asg_rec => l_asg_rec);
498 --
499 -- Assignment to be transfered Loop
500 --
501 for i in 1..l_asg_rec.assignment_id_tbl.count loop
502 transfer_asg(
503 p_business_group_id => p_business_group_id,
504 p_payroll_id => p_payroll_id,
505 p_assignment_id => l_asg_rec.assignment_id_tbl(i),
506 p_payment_date => l_payment_date, -- bug 4029525
507 p_effective_date => l_effective_date,
508 p_upload_date => l_asg_rec.effective_date_tbl(i),
509 p_assignment_number => l_asg_rec.assignment_number_tbl(i),
510 p_full_name => l_asg_rec.full_name_tbl(i),
511 p_batch_id => l_batch_id,
512 p_create_entry_if_not_exist => p_create_entry_if_not_exist,
513 p_create_asg_set_for_errored => p_create_asg_set_for_errored,
514 p_assignment_set_id => l_assignment_set_id,
515 p_assignment_set_name => l_assignment_set_name);
516 end loop;
517 --
518 -- Write the assignment_set_name created into log file
519 --
520 if l_assignment_set_id is not null then
521 fnd_message.set_name('PAY', 'PAY_JP_BEE_UTIL_ASG_SET_CREATE');
522 fnd_message.set_token('ASSIGNMENT_SET_NAME', l_assignment_set_name);
523 fnd_file.put_line(fnd_file.log, fnd_message.get);
524 end if;
525 --
526 -- When no batch lines are created, delete batch header and set message as errbuf.
527 --
528 if pay_jp_bee_utility_pkg.g_num_of_outs = 0 then
529 hr_utility.trace('BEE Header deleted');
530 --
531 pay_batch_element_entry_api.delete_batch_header(
532 p_validate => false,
533 p_batch_id => l_batch_id,
534 p_object_version_number => l_batch_ovn);
535 commit;
536 --
537 fnd_message.set_name('PAY', 'PAY_JP_BEE_UTIL_NO_ASGS');
538 p_errbuf := fnd_message.get;
539 fnd_file.put_line(fnd_file.log, p_errbuf);
540 end if;
541 --
542 -- If at least 1 assignment failed to process, set concurrent request status "Incomplete".
543 --
544 if pay_jp_bee_utility_pkg.g_num_of_logs > 0 then
545 p_retcode := 1;
546 else
547 p_retcode := 0;
548 end if;
549 --
550 delete_session;
551 --
552 hr_utility.trace('retcode : ' || p_retcode);
553 hr_utility.trace('errbuf : ' || p_errbuf);
554 hr_utility.set_location('Leaving : ' || l_proc, 20);
555 end transfer_from_cma_info_to_bee;
556 --
557 end pay_jp_create_cma_pkg;