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