[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_LTAX_IMP_PKG
Source
1 package body pay_jp_ltax_imp_pkg as
2 /* $Header: pyjpltxi.pkb 120.4 2011/07/29 10:15:55 keyazawa noship $ */
3 --
4 c_package constant varchar2(30) := 'pay_jp_ltax_imp_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6 --
7 c_com_ltx_info_elm constant varchar2(80) := 'COM_LTX_INFO';
8 c_com_itx_info_elm constant varchar2(80) := 'COM_ITX_INFO';
9 c_itx_org_iv constant varchar2(80) := 'WITHHOLD_AGENT';
10 c_non_res_iv constant varchar2(80) := 'NRES_FLAG';
11 c_com_nres_info_elm constant varchar2(80) := 'COM_NRES_INFO';
12 c_non_res_date_iv constant varchar2(80) := 'NRES_START_DATE';
13 c_res_date_iv constant varchar2(80) := 'PROJECTED_RES_DATE';
14 --
15 c_com_ltx_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_ltx_info_elm,null,'JP');
16 c_com_itx_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_itx_info_elm,null,'JP');
17 c_itx_org_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_itx_info_elm_id,c_itx_org_iv);
18 c_non_res_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_itx_info_elm_id,c_non_res_iv);
19 c_com_nres_info_elm_id constant number := hr_jp_id_pkg.element_type_id(c_com_nres_info_elm,null,'JP');
20 c_non_res_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_nres_info_elm_id,c_non_res_date_iv);
21 c_res_date_iv_id constant number := hr_jp_id_pkg.input_value_id(c_com_nres_info_elm_id,c_res_date_iv);
22 --
23 type t_file_rec is record(
24 file_name varchar2(80),
25 file_out utl_file.file_type);
26 type t_file_tbl is table of t_file_rec index by binary_integer;
27 g_file_tbl t_file_tbl;
28 g_file_tbl_cnt number;
29 --
30 type t_data_rec is record(
31 file_id number,
32 line number,
33 i_swot_number pay_jp_swot_numbers.swot_number%type,
34 i_personal_number pay_element_entry_values_f.screen_entry_value%type,
35 i_employee_number per_all_people_f.employee_number%type,
36 i_address varchar2(800),
37 i_address_kana varchar2(800),
38 i_full_name varchar2(400),
39 i_full_name_kana varchar2(400),
40 i_sp_ltax number,
41 i_ltax_6 number,
42 i_ltax_7 number,
43 i_ltax_8 number,
44 i_ltax_9 number,
45 i_ltax_10 number,
46 i_ltax_11 number,
47 i_ltax_12 number,
48 i_ltax_1 number,
49 i_ltax_2 number,
50 i_ltax_3 number,
51 i_ltax_4 number,
52 i_ltax_5 number,
53 i_district_code per_addresses.town_or_city%type,
54 assignment_id number,
55 assignment_number per_all_assignments_f.assignment_number%type);
56 type t_data_tbl is table of t_data_rec index by binary_integer;
57 g_imp_data_tbl t_data_tbl;
58 g_imp_data_tbl_cnt number;
59 g_ass_data_tbl t_data_tbl;
60 --
61 type t_num_tbl is table of number index by binary_integer;
62 g_ass_id_tbl t_num_tbl;
63 g_ass_id_tbl_cnt number;
64 --
65 type t_ass_rec is record(
66 assignment_id number,
67 payroll_id number,
68 assignment_number per_all_assignments_f.assignment_number%type,
69 final_process_date date);
70 type t_ass_tbl is table of t_ass_rec index by binary_integer;
71 g_ass_ind_tbl t_ass_tbl;
72 --
73 type t_ass_amd_rec is record(
74 assignment_id number,
75 assignment_number per_all_assignments_f.assignment_number%type,
76 include_or_exclude hr_assignment_set_amendments.include_or_exclude%type);
77 type t_ass_amd_tbl is table of t_ass_amd_rec index by binary_integer;
78 g_ass_amd_ind_tbl t_ass_amd_tbl;
79 --
80 type t_imp_file_rec is record(
81 district_code per_addresses.town_or_city%type,
82 organization_id number,
83 swot_number pay_jp_swot_numbers.swot_number%type,
84 input_file_name pay_jp_swot_numbers.input_file_name%type);
85 type t_imp_file_tbl is table of t_imp_file_rec index by binary_integer;
86 g_imp_file_ind_tbl t_imp_file_tbl;
87 --
88 type t_mth_rec is record(
89 payroll_id number,
90 mth_cnt number,
91 payment_date_1 date,
92 payment_date_2 date,
93 payment_date_3 date,
94 payment_date_4 date,
95 payment_date_5 date,
96 payment_date_6 date,
97 payment_date_7 date,
98 payment_date_8 date,
99 payment_date_9 date,
100 payment_date_10 date,
101 payment_date_11 date,
102 payment_date_12 date,
103 upload_date_1 date,
104 upload_date_2 date,
105 upload_date_3 date,
106 upload_date_4 date,
107 upload_date_5 date,
108 upload_date_6 date,
109 upload_date_7 date,
110 upload_date_8 date,
111 upload_date_9 date,
112 upload_date_10 date,
113 upload_date_11 date,
114 upload_date_12 date);
115 type t_mth_tbl is table of t_mth_rec index by binary_integer;
116 g_mth_tbl t_mth_tbl;
117 --
118 type t_ee_rec is record(
119 period_year number,
120 period_num number,
121 upload_date date);
122 type t_ee_tbl is table of t_ee_rec index by binary_integer;
123 g_ee_tbl t_ee_tbl;
124 --
125 type t_wng_tbl is table of varchar2(2000) index by binary_integer;
126 g_dup_file_wng_tbl t_wng_tbl;
127 g_dup_file_wng_tbl_cnt number;
128 g_no_file_wng_tbl t_wng_tbl;
129 g_no_file_wng_tbl_cnt number;
130 g_diff_ltax_wng_tbl t_wng_tbl;
131 g_diff_ltax_wng_tbl_cnt number;
132 g_inv_data_wng_tbl t_wng_tbl;
133 g_inv_data_wng_tbl_cnt number;
134 g_incon_data_wng_tbl t_wng_tbl;
135 g_incon_data_wng_tbl_cnt number;
136 g_no_ass_wng_tbl t_wng_tbl;
137 g_no_ass_wng_tbl_cnt number;
138 g_non_res_wng_tbl t_wng_tbl;
139 g_non_res_wng_tbl_cnt number;
140 g_dup_ass_wng_tbl t_wng_tbl;
141 g_dup_ass_wng_tbl_cnt number;
142 g_upd_eev_wng_tbl t_wng_tbl;
143 g_upd_eev_wng_tbl_cnt number;
144 g_no_upd_wng_tbl t_wng_tbl;
145 g_no_upd_wng_tbl_cnt number;
146 g_sp_with_wng_tbl t_wng_tbl;
147 g_sp_with_wng_tbl_cnt number;
148 g_inv_ass_wng_tbl t_wng_tbl;
149 g_inv_ass_wng_tbl_cnt number;
150 --
151 -- -------------------------------------------------------------------------
152 -- insert_session
153 -- -------------------------------------------------------------------------
154 procedure insert_session(
155 p_effective_date in date)
156 is
157 begin
158 --
159 insert into fnd_sessions(
160 session_id,
161 effective_date)
162 select
163 userenv('sessionid'),
164 p_effective_date
165 from dual
166 where not exists(
167 select null
168 from fnd_sessions
169 where session_id = userenv('sessionid')
170 and effective_date = p_effective_date);
171 --
172 commit;
173 --
174 end insert_session;
175 --
176 -- -------------------------------------------------------------------------
177 -- delete_session
178 -- -------------------------------------------------------------------------
179 procedure delete_session
180 is
181 begin
182 --
183 delete from fnd_sessions
184 where session_id = userenv('sessionid');
185 --
186 commit;
187 --
188 end delete_session;
189 --
190 -- -------------------------------------------------------------------------
191 -- set_file_prefix
192 -- -------------------------------------------------------------------------
193 procedure set_file_prefix(
194 p_file_prefix in varchar2)
195 is
196 begin
197 --
198 pay_jp_ltax_imp_pkg.g_file_prefix := p_file_prefix;
199 --
200 end set_file_prefix;
201 --
202 -- -------------------------------------------------------------------------
203 -- set_file_suffix
204 -- -------------------------------------------------------------------------
205 procedure set_file_suffix(
206 p_file_suffix in varchar2)
207 is
208 begin
209 --
210 pay_jp_ltax_imp_pkg.g_file_suffix := p_file_suffix;
211 --
212 end set_file_suffix;
213 --
214 -- -------------------------------------------------------------------------
215 -- set_file_extension
216 -- -------------------------------------------------------------------------
217 procedure set_file_extension(
218 p_file_extension in varchar2)
219 is
220 begin
221 --
222 pay_jp_ltax_imp_pkg.g_file_extension := p_file_extension;
223 --
224 end set_file_extension;
225 --
226 -- -------------------------------------------------------------------------
227 -- set_file_split
228 -- -------------------------------------------------------------------------
229 procedure set_file_split(
230 p_file_split in varchar2)
231 is
232 begin
233 --
234 pay_jp_ltax_imp_pkg.g_file_split := p_file_split;
235 --
236 end set_file_split;
237 --
238 -- -------------------------------------------------------------------------
239 -- set_datetrack_eev
240 -- -------------------------------------------------------------------------
241 procedure set_datetrack_eev(
242 p_datetrack_eev in varchar2)
243 is
244 begin
245 --
246 pay_jp_ltax_imp_pkg.g_datetrack_eev := p_datetrack_eev;
247 --
248 end set_datetrack_eev;
249 --
250 -- -------------------------------------------------------------------------
251 -- set_detail_debug
252 -- -------------------------------------------------------------------------
253 procedure set_detail_debug(
254 p_yn in varchar2)
255 is
256 begin
257 --
258 pay_jp_ltax_imp_pkg.g_detail_debug := p_yn;
259 --
260 end set_detail_debug;
261 --
262 -- -------------------------------------------------------------------------
263 -- default_file_name
264 -- -------------------------------------------------------------------------
265 function default_file_name(
266 p_district_code in varchar2,
267 p_organization_id in number,
268 p_swot_number in pay_jp_swot_numbers.swot_number%type)
269 return varchar2
270 is
271 --
272 l_file_name varchar2(80);
273 l_file_end varchar2(80);
274 l_sub_name varchar2(15);
275 --
276 begin
277 --
278 l_file_end := pay_jp_ltax_imp_pkg.g_file_extension;
279 if pay_jp_ltax_imp_pkg.g_file_suffix is not null then
280 --
281 l_file_end := c_file_spliter||pay_jp_ltax_imp_pkg.g_file_suffix||l_file_end;
282 --
283 end if;
284 --
285 if p_organization_id is null then
286 --
287 if p_district_code is not null then
288 --
289 l_file_name := pay_jp_ltax_imp_pkg.g_file_prefix||
290 c_file_spliter||p_district_code||l_file_end;
291 --
292 else
293 --
294 l_file_name := pay_jp_ltax_imp_pkg.g_file_prefix||
295 l_file_end;
296 --
297 end if;
298 --
299 else
300 --
301 l_sub_name := to_char(p_organization_id);
302 if p_swot_number is not null then
303 --
304 l_sub_name := p_swot_number;
305 --
306 end if;
307 --
308 l_file_name := pay_jp_ltax_imp_pkg.g_file_prefix||
309 c_file_spliter||p_district_code||
310 c_file_spliter||l_sub_name||
311 l_file_end;
312 --
313 end if;
314 --
315 if lengthb(l_file_name) > 80 then
316 --
317 fnd_message.set_name('PAY','PAY_JP_SPR_INV_FILE_NAME');
318 fnd_message.raise_error;
319 --
320 end if;
321 --
322 return l_file_name;
323 end default_file_name;
324 --
325 -- -------------------------------------------------------------------------
326 -- cnv_num
327 -- -------------------------------------------------------------------------
328 function cnv_num(
329 p_text in varchar2)
330 return number
331 is
332 --
333 l_text number;
334 --
335 begin
336 --
337 l_text := to_number(p_text);
338 --
339 return l_text;
340 exception
341 when others then
342 --
343 return l_text;
344 --
345 end cnv_num;
346 --
347 -- -------------------------------------------------------------------------
348 -- set_upload_date
349 -- -------------------------------------------------------------------------
350 function set_upload_date(
351 p_period_date in date,
352 p_base_date in date)
353 return date
354 is
355 --
356 l_diff_yyyy number;
357 l_diff_mm number;
358 l_upload_date date;
359 --
360 begin
361 --
362 l_upload_date := p_period_date;
363 --
364 if p_base_date is not null then
365 --
366 l_diff_yyyy := to_number(to_char(p_period_date,'YYYY')) - to_number(to_char(p_base_date,'YYYY'));
367 l_diff_mm := to_number(to_char(p_period_date,'MM')) - to_number(to_char(p_base_date,'MM'));
368 --
369 if l_diff_mm < 0 then
370 l_diff_yyyy := l_diff_yyyy - 1;
371 l_diff_mm := 12 + l_diff_mm;
372 end if;
373 --
374 l_upload_date := add_months(p_base_date,l_diff_yyyy * 12 + l_diff_mm);
375 --
376 end if;
377 --
378 return l_upload_date;
379 end set_upload_date;
380 --
381 -- -------------------------------------------------------------------------
382 -- set_ee_tbl
383 -- -------------------------------------------------------------------------
384 procedure set_ee_tbl(
385 p_mth in number,
386 p_payment_date in date,
387 p_upload_date in date)
388 is
389 --
390 l_proc varchar2(80) := c_package||'set_ee_tbl';
391 --
392 begin
393 --
394 g_ee_tbl(p_mth).period_year := to_number(to_char(p_payment_date,'YYYY'));
395 g_ee_tbl(p_mth).period_num := to_number(to_char(p_payment_date,'MM'));
396 g_ee_tbl(p_mth).upload_date := set_upload_date(p_upload_date,g_upload_date);
397 --
398 end set_ee_tbl;
399 --
400 -- -------------------------------------------------------------------------
401 -- create_asg_set_amd
402 -- -------------------------------------------------------------------------
403 procedure create_asg_set_amd(
404 p_business_group_id in number,
405 p_payroll_id in number,
406 p_assignment_id in number,
407 p_assignment_set_id in out nocopy number,
408 p_assignment_set_name in out nocopy varchar2)
409 is
410 begin
411 --
412 if g_create_asg_set_for_errored = 'Y' then
413 --
414 if p_assignment_set_id is null then
415 --
416 hr_jp_ast_utility_pkg.create_asg_set_with_request_id(
417 p_prefix => c_asg_set_prefix,
418 p_business_group_id => p_business_group_id,
419 p_payroll_id => p_payroll_id,
420 p_assignment_set_id => p_assignment_set_id,
421 p_assignment_set_name => p_assignment_set_name);
422 --
423 commit;
424 --
425 end if;
426 --
427 hr_jp_ast_utility_pkg.create_asg_set_amd(
428 p_assignment_set_id => p_assignment_set_id,
429 p_assignment_id => p_assignment_id,
430 p_include_or_exclude => 'I');
431 --
432 commit;
433 --
434 end if;
435 --
436 end create_asg_set_amd;
437 --
438 -- -------------------------------------------------------------------------
439 -- init
440 -- -------------------------------------------------------------------------
441 procedure init(
442 p_business_group_id in number,
443 p_subject_yyyymm in varchar2,
444 p_upload_date in date,
445 p_organization_id in number,
446 p_district_code in varchar2,
447 p_assignment_set_id in number,
448 p_file_suffix in varchar2,
449 p_file_split in varchar2,
450 p_datetrack_eev in varchar2,
451 p_show_dup_file in varchar2,
452 p_show_no_file in varchar2,
453 p_valid_diff_ltax in varchar2,
454 p_valid_incon_data in varchar2,
455 p_show_incon_data in varchar2,
456 p_valid_non_res in varchar2,
457 p_valid_dup_ass in varchar2,
458 p_show_upd_eev in varchar2,
459 p_valid_no_upd in varchar2,
460 p_show_no_upd in varchar2,
461 p_valid_sp_with in varchar2,
462 p_action_if_exists in varchar2,
463 p_reject_if_future_changes in varchar2,
464 p_create_entry_if_not_exist in varchar2,
465 p_create_asg_set_for_errored in varchar2)
466 is
467 --
468 l_proc varchar2(80) := c_package||'init';
469 --
470 l_detail_debug varchar2(1) := pay_jp_ltax_imp_pkg.g_detail_debug;
471 --
472 cursor csr_file_dir
473 is
474 select fcp.plsql_dir
475 from fnd_concurrent_requests fcr,
476 fnd_concurrent_processes fcp
477 where fcr.request_id = g_request_id
478 and fcp.concurrent_process_id = fcr.controlling_manager;
479 --
480 begin
481 --
482 if g_debug then
483 hr_utility.set_location(l_proc,0);
484 end if;
485 --
486 --if g_business_group_id is null
487 --or g_business_group_id <> p_business_group_id then
488 --
489 g_file_tbl.delete;
490 g_imp_data_tbl.delete;
491 g_ass_data_tbl.delete;
492 g_ass_id_tbl.delete;
493 g_ass_ind_tbl.delete;
494 g_ass_amd_ind_tbl.delete;
495 g_imp_file_ind_tbl.delete;
496 g_mth_tbl.delete;
497 --
498 g_dup_file_wng_tbl.delete;
499 g_no_file_wng_tbl.delete;
500 g_diff_ltax_wng_tbl.delete;
501 g_inv_data_wng_tbl.delete;
502 g_incon_data_wng_tbl.delete;
503 g_no_ass_wng_tbl.delete;
504 g_non_res_wng_tbl.delete;
505 g_dup_ass_wng_tbl.delete;
506 g_upd_eev_wng_tbl.delete;
507 g_no_upd_wng_tbl.delete;
508 g_sp_with_wng_tbl.delete;
509 g_inv_ass_wng_tbl.delete;
510 --
511 g_file_tbl_cnt := 0;
512 g_imp_data_tbl_cnt := 0;
513 g_ass_id_tbl_cnt := 0;
514 --
515 g_dup_file_wng_tbl_cnt := 0;
516 g_no_file_wng_tbl_cnt := 0;
517 g_diff_ltax_wng_tbl_cnt := 0;
518 g_inv_data_wng_tbl_cnt := 0;
519 g_incon_data_wng_tbl_cnt := 0;
520 g_no_ass_wng_tbl_cnt := 0;
521 g_non_res_wng_tbl_cnt := 0;
522 g_dup_ass_wng_tbl_cnt := 0;
523 g_upd_eev_wng_tbl_cnt := 0;
524 g_no_upd_wng_tbl_cnt := 0;
525 g_sp_with_wng_tbl_cnt := 0;
526 g_inv_ass_wng_tbl_cnt := 0;
527 --
528 g_request_id := fnd_global.conc_request_id;
529 g_business_group_id := p_business_group_id;
530 g_effective_yyyymm := p_subject_yyyymm;
531 g_effective_som := to_date(g_effective_yyyymm||'01','YYYYMMDD');
532 g_effective_eom := last_day(g_effective_som);
533 g_effective_soy := trunc(g_effective_som,'YYYY');
534 g_effective_eoy := add_months(g_effective_soy,12) - 1;
535 --
536 g_upload_date := p_upload_date;
537 --
538 g_session_date := g_upload_date;
539 if g_session_date is null then
540 --
541 g_session_date := g_effective_som;
542 --
543 end if;
544 --
545 g_organization_id := p_organization_id;
546 g_district_code := p_district_code;
547 g_assignment_set_id := p_assignment_set_id;
548 --
549 g_ass_set_formula_id := null;
550 g_ass_set_amendment_type := null;
551 if g_assignment_set_id is not null then
552 --
553 hr_jp_ast_utility_pkg.get_assignment_set_info(g_assignment_set_id,g_ass_set_formula_id,g_ass_set_amendment_type);
554 --
555 end if;
556 --
557 g_file_dir := null;
558 open csr_file_dir;
559 fetch csr_file_dir into g_file_dir;
560 close csr_file_dir;
561 --
562 if g_file_dir is null then
563 --
564 fnd_message.set_name('FND','CONC-GET PLSQL FILE NAMES');
565 fnd_message.raise_error;
566 --
567 end if;
568 --
569 if pay_jp_report_pkg.g_char_set is null then
570 --
571 pay_jp_report_pkg.set_char_set(c_char_set);
572 --
573 end if;
574 --
575 pay_jp_report_pkg.set_db_char_set;
576 --
577 if pay_jp_ltax_imp_pkg.g_file_prefix is null then
578 --
579 set_file_prefix(c_file_prefix);
580 --
581 end if;
582 --
583 if pay_jp_ltax_imp_pkg.g_file_extension is null then
584 --
585 set_file_extension(c_file_extension);
586 --
587 end if;
588 --
589 if p_file_suffix is not null then
590 --
591 set_file_suffix(p_file_suffix);
592 --
593 end if;
594 --
595 if p_file_split is not null then
596 --
597 set_file_split(p_file_split);
598 --
599 end if;
600 --
601 if p_datetrack_eev is not null then
602 --
603 set_datetrack_eev(p_datetrack_eev);
604 --
605 end if;
606 --
607 g_show_dup_file := p_show_dup_file;
608 g_show_no_file := p_show_no_file;
609 g_valid_diff_ltax := p_valid_diff_ltax;
610 g_valid_incon_data := p_valid_incon_data;
611 g_show_incon_data := p_show_incon_data;
612 g_valid_non_res := p_valid_non_res;
613 g_valid_dup_ass := p_valid_dup_ass;
614 g_show_upd_eev := p_show_upd_eev;
615 g_valid_no_upd := p_valid_no_upd;
616 g_show_no_upd := p_show_no_upd;
617 g_valid_sp_with := p_valid_sp_with;
618 --
619 g_action_if_exists := p_action_if_exists;
620 g_reject_if_future_changes := p_reject_if_future_changes;
621 if g_datetrack_eev = 'Y' then
622 --
623 if g_action_if_exists = 'I' then
624 --
625 g_action_if_exists := 'U';
626 --
627 end if;
628 --
629 if g_reject_if_future_changes = 'Y' then
630 --
631 g_reject_if_future_changes := 'N';
632 --
633 end if;
634 --
635 end if;
636 --
637 g_create_entry_if_not_exist := p_create_entry_if_not_exist;
638 g_create_asg_set_for_errored := p_create_asg_set_for_errored;
639 --
640 g_err_ass_set_id := null;
641 g_err_ass_set_name := null;
642 --
643 --end if;
644 --
645 set_detail_debug(l_detail_debug);
646 --
647 if g_debug then
648 --
649 hr_utility.trace('g_request_id : '||to_char(g_request_id));
650 hr_utility.trace('g_business_group_id : '||to_char(g_business_group_id));
651 hr_utility.trace('g_effective_yyyymm : '||g_effective_yyyymm);
652 hr_utility.trace('g_effective_som : '||to_char(g_effective_som,'YYYY/MM/DD'));
653 hr_utility.trace('g_effective_eom : '||to_char(g_effective_eom,'YYYY/MM/DD'));
654 hr_utility.trace('g_effective_soy : '||to_char(g_effective_soy,'YYYY/MM/DD'));
655 hr_utility.trace('g_effective_eoy : '||to_char(g_effective_eoy,'YYYY/MM/DD'));
656 hr_utility.trace('g_upload_date : '||to_char(g_upload_date,'YYYY/MM/DD'));
657 hr_utility.trace('g_session_date : '||to_char(g_session_date,'YYYY/MM/DD'));
658 hr_utility.trace('g_organization_id : '||to_char(g_organization_id));
659 hr_utility.trace('g_district_code : '||g_district_code);
660 hr_utility.trace('g_assignment_set_id : '||to_char(g_assignment_set_id));
661 hr_utility.trace('g_ass_set_formula_id : '||to_char(g_ass_set_formula_id));
662 hr_utility.trace('g_ass_set_amendment_type : '||g_ass_set_amendment_type);
663 hr_utility.trace('g_file_dir : '||g_file_dir);
664 hr_utility.trace('g_file_suffix : '||g_file_suffix);
665 hr_utility.trace('g_file_split : '||g_file_split);
666 hr_utility.trace('g_datetrack_eev : '||g_datetrack_eev);
667 hr_utility.trace('g_show_dup_file : '||g_show_dup_file);
668 hr_utility.trace('g_show_no_file : '||g_show_no_file);
669 hr_utility.trace('g_valid_diff_ltax : '||g_valid_diff_ltax);
670 hr_utility.trace('g_valid_incon_data : '||g_valid_incon_data);
671 hr_utility.trace('g_show_incon_data : '||g_show_incon_data);
672 hr_utility.trace('g_valid_non_res : '||g_valid_non_res);
673 hr_utility.trace('g_valid_dup_ass : '||g_valid_dup_ass);
674 hr_utility.trace('g_show_upd_eev : '||g_show_upd_eev);
675 hr_utility.trace('g_valid_no_upd : '||g_valid_no_upd);
676 hr_utility.trace('g_show_no_upd : '||g_show_no_upd);
677 hr_utility.trace('g_valid_sp_with : '||g_valid_sp_with);
678 hr_utility.trace('g_action_if_exists : '||g_action_if_exists);
679 hr_utility.trace('g_reject_if_future_changes : '||g_reject_if_future_changes);
680 hr_utility.trace('g_create_entry_if_not_exist : '||g_create_entry_if_not_exist);
681 hr_utility.trace('g_create_asg_set_for_errored : '||g_create_asg_set_for_errored);
682 hr_utility.trace('pay_jp_report_pkg.g_char_set : '||pay_jp_report_pkg.g_char_set);
683 hr_utility.trace('pay_jp_report_pkg.g_db_char_set : '||pay_jp_report_pkg.g_db_char_set);
684 --
685 end if;
686 --
687 if g_debug then
688 hr_utility.set_location(l_proc,1000);
689 end if;
690 --
691 end init;
692 --
693 -- -------------------------------------------------------------------------
694 -- imp_file_data
695 -- -------------------------------------------------------------------------
696 procedure imp_file_data
697 is
698 --
699 l_proc varchar2(80) := c_package||'imp_file_data';
700 --
701 l_assignment_id per_all_assignments_f.assignment_id%type;
702 l_include_or_exclude hr_assignment_set_amendments.include_or_exclude%type;
703 --
704 l_district_code_o per_addresses.town_or_city%type;
705 l_organization_id_o number;
706 l_input_file_name_o pay_jp_swot_numbers.input_file_name%type;
707 --
708 l_input_file_name pay_jp_swot_numbers.input_file_name%type;
709 l_file_line number;
710 --
711 l_ass_id_tbl t_num_tbl;
712 l_ass_id_tbl_cnt number;
713 --
714 l_ass_amd_tbl t_ass_amd_tbl;
715 l_ass_amd_tbl_cnt number;
716 l_ass_amd_ind_tbl t_ass_amd_tbl;
717 --
718 type t_ass_file_rec is record(
719 assignment_id number,
720 district_code per_addresses.town_or_city%type,
721 organization_id number,
722 swot_number pay_jp_swot_numbers.swot_number%type,
723 input_file_name pay_jp_swot_numbers.input_file_name%type,
724 assignment_number per_all_assignments_f.assignment_number%type,
725 include_or_exclude hr_assignment_set_amendments.include_or_exclude%type,
726 regular_payment_date date);
727 type t_ass_file_tbl is table of t_ass_file_rec index by binary_integer;
728 l_ass_file_tbl t_ass_file_tbl;
729 --
730 l_imp_file_tbl t_imp_file_tbl;
731 l_imp_file_tbl_cnt number;
732 l_imp_file_ind_tbl t_imp_file_tbl;
733 --
734 l_file_tbl t_file_tbl;
735 l_file_tbl_cnt number;
736 l_file_data_tbl pay_jp_report_pkg.t_file_data_tbl;
737 --
738 l_cnv_data varchar2(32767);
739 --
740 l_imp_file boolean;
741 --
742 cursor csr_imp_file
743 is
744 select nvl(pjsn_act.report_district_code,pjsn_act.district_code) rep_district_code,
745 pjsn_act.organization_id,
746 pjsn_rep.swot_number,
747 pjsn_rep.input_file_name
748 from hr_organization_information hoi,
749 hr_all_organization_units hou,
750 pay_jp_swot_numbers pjsn_act,
751 pay_jp_swot_numbers pjsn_rep
752 where hoi.org_information_context = 'CLASS'
753 and hoi.org_information1 = 'JP_TAX_SWOT'
754 and hoi.organization_id = nvl(g_organization_id,hoi.organization_id)
755 and hou.organization_id = hoi.organization_id
756 and hou.business_group_id + 0 = g_business_group_id
757 and hou.date_from <= g_effective_eoy
758 and nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
759 and pjsn_act.organization_id = hou.organization_id
760 and pjsn_rep.organization_id = pjsn_act.organization_id
761 and pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
762 and substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
763 and nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
764 and (pjsn_rep.swot_number is not null
765 or pjsn_rep.input_file_name is not null)
766 group by
767 nvl(pjsn_act.report_district_code,pjsn_act.district_code),
768 pjsn_act.organization_id,
769 pjsn_rep.swot_number,
770 pjsn_rep.input_file_name
771 order by
772 pjsn_rep.input_file_name,
773 nvl(pjsn_act.report_district_code,pjsn_act.district_code),
774 pjsn_rep.swot_number,
775 pjsn_act.organization_id;
776 --
777 cursor csr_imp_file_ass
778 is
779 select /*+ ORDERED */
780 pa.assignment_id,
781 pa.assignment_number,
782 nvl(pjsn_act.report_district_code,pjsn_act.district_code) rep_district_code,
783 pjsn_act.organization_id,
784 pjsn_rep.swot_number,
785 pjsn_rep.input_file_name,
786 ptp.regular_payment_date
787 from hr_organization_information hoi,
788 hr_all_organization_units hou,
789 pay_jp_swot_numbers pjsn_act,
790 pay_jp_swot_numbers pjsn_rep,
791 per_addresses pad,
792 per_periods_of_service ppos,
793 per_all_assignments_f pa,
794 per_time_periods ptp
795 where hoi.org_information_context = 'CLASS'
796 and hoi.org_information1 = 'JP_TAX_SWOT'
797 and hoi.organization_id = nvl(g_organization_id,hoi.organization_id)
798 and hou.organization_id = hoi.organization_id
799 and hou.business_group_id + 0 = g_business_group_id
800 and hou.date_from <= g_effective_eoy
801 and nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
802 and pjsn_act.organization_id = hou.organization_id
803 and pjsn_rep.organization_id = pjsn_act.organization_id
804 and pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
805 and substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
806 and nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
807 and (pjsn_rep.swot_number is not null
808 or pjsn_rep.input_file_name is not null)
809 -- group by required for reporting and actual swot
810 and pad.town_or_city = substrb(pjsn_act.district_code,1,5)
811 and pad.address_type in ('JP_R','JP_C')
812 and pad.business_group_id + 0 = g_business_group_id
813 and ppos.person_id = pad.person_id
814 and nvl(ppos.actual_termination_date, g_effective_soy)
815 between pad.date_from and nvl(pad.date_to,hr_api.g_eot)
816 and pa.period_of_service_id = ppos.period_of_service_id
817 and pa.effective_start_date <= g_effective_eom
818 and pa.effective_end_date >= g_effective_som
819 and pa.primary_flag = 'Y'
820 and ptp.payroll_id = pa.payroll_id
821 and ptp.regular_payment_date
822 between g_effective_som and g_effective_eom
823 and ptp.regular_payment_date
824 between pa.effective_start_date and pa.effective_end_date
825 and pay_jp_balance_pkg.get_entry_value_number(c_itx_org_iv_id,pa.assignment_id,ptp.regular_payment_date) = pjsn_act.organization_id
826 group by
827 pa.assignment_id,
828 pa.assignment_number,
829 nvl(pjsn_act.report_district_code,pjsn_act.district_code),
830 pjsn_act.organization_id,
831 pjsn_rep.swot_number,
832 pjsn_rep.input_file_name,
833 ptp.regular_payment_date
834 order by
835 pjsn_rep.input_file_name,
836 nvl(pjsn_act.report_district_code,pjsn_act.district_code),
837 pjsn_rep.swot_number,
838 pjsn_act.organization_id,
839 pa.assignment_id;
840 --
841 l_csr_imp_file_ass csr_imp_file_ass%rowtype;
842 --
843 cursor csr_ass_amd
844 is
845 select hasa.assignment_id,
846 pa.assignment_number,
847 hasa.include_or_exclude
848 from hr_assignment_set_amendments hasa,
849 per_all_assignments_f pa,
850 per_time_periods ptp
851 where hasa.assignment_set_id = g_assignment_set_id
852 and pa.assignment_id = hasa.assignment_id
853 and pa.effective_start_date <= g_effective_eom
854 and pa.effective_end_date >= g_effective_som
855 and pa.primary_flag = 'Y'
856 and ptp.payroll_id = pa.payroll_id
857 and ptp.regular_payment_date
858 between g_effective_som and g_effective_eom
859 and ptp.regular_payment_date
860 between pa.effective_start_date and pa.effective_end_date;
861 --
862 begin
863 --
864 if g_debug then
865 hr_utility.set_location(l_proc,0);
866 hr_utility.trace('g_file_split : '||g_file_split);
867 end if;
868 --
869 l_file_tbl_cnt := 0;
870 l_ass_id_tbl_cnt := 0;
871 l_ass_amd_tbl_cnt := 0;
872 l_imp_file_tbl_cnt := 0;
873 --
874 if g_file_split is null
875 or g_file_split = 'Y' then
876 --
877 if g_debug then
878 hr_utility.set_location(l_proc,10);
879 end if;
880 --
881 if g_assignment_set_id is null then
882 --
883 open csr_imp_file;
884 fetch csr_imp_file bulk collect into l_imp_file_tbl;
885 close csr_imp_file;
886 --
887 if g_debug then
888 hr_utility.set_location(l_proc,20);
889 hr_utility.trace('l_imp_file_tbl.count : '||to_char(l_imp_file_tbl.count));
890 end if;
891 --
892 else
893 --
894 if g_ass_set_amendment_type is not null
895 and g_ass_set_amendment_type <> 'N' then
896 --
897 open csr_ass_amd;
898 fetch csr_ass_amd bulk collect into l_ass_amd_tbl;
899 close csr_ass_amd;
900 --
901 if l_ass_amd_tbl.count > 0 then
902 --
903 for i in 1..l_ass_amd_tbl.count loop
904 --
905 l_ass_amd_ind_tbl(l_ass_amd_tbl(i).assignment_id).assignment_id := l_ass_amd_tbl(i).assignment_id;
906 l_ass_amd_ind_tbl(l_ass_amd_tbl(i).assignment_id).include_or_exclude := l_ass_amd_tbl(i).include_or_exclude;
907 --
908 --
909 end loop;
910 --
911 end if;
912 --
913 end if;
914 --
915 if g_debug then
916 hr_utility.set_location(l_proc,30);
917 hr_utility.trace('g_ass_set_amendment_type : '||g_ass_set_amendment_type);
918 hr_utility.trace('l_ass_amd_tbl.count : '||to_char(l_ass_amd_tbl.count));
919 hr_utility.trace('l_ass_amd_ind_tbl.count : '||to_char(l_ass_amd_ind_tbl.count));
920 end if;
921 --
922 open csr_imp_file_ass;
923 loop
924 --
925 fetch csr_imp_file_ass into l_csr_imp_file_ass;
926 exit when csr_imp_file_ass%notfound;
927 --
928 if l_ass_amd_ind_tbl.count > 0 then
929 --
930 begin
931 --
932 l_assignment_id := l_ass_amd_ind_tbl(l_csr_imp_file_ass.assignment_id).assignment_id;
933 l_include_or_exclude := l_ass_amd_ind_tbl(l_csr_imp_file_ass.assignment_id).include_or_exclude;
934 --
935 -- not store all csr_ass_amd to reduce non record in file
936 g_ass_amd_ind_tbl(l_assignment_id).assignment_id := l_assignment_id;
937 g_ass_amd_ind_tbl(l_assignment_id).include_or_exclude := l_include_or_exclude;
938 --
939 if l_include_or_exclude is null
940 or l_include_or_exclude = 'I' then
941 --
942 l_ass_id_tbl_cnt := l_ass_id_tbl_cnt + 1;
943 l_ass_id_tbl(l_ass_id_tbl_cnt) := l_csr_imp_file_ass.assignment_id;
944 --
945 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_id := l_csr_imp_file_ass.assignment_id;
946 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).district_code := l_csr_imp_file_ass.rep_district_code;
947 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).organization_id := l_csr_imp_file_ass.organization_id;
948 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).swot_number := l_csr_imp_file_ass.swot_number;
949 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).input_file_name := l_csr_imp_file_ass.input_file_name;
950 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_number := l_csr_imp_file_ass.assignment_number;
951 -- set to skip below formula validation if included amendment
952 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).include_or_exclude := l_include_or_exclude;
953 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).regular_payment_date := l_csr_imp_file_ass.regular_payment_date;
954 --
955 end if;
956 --
957 exception
958 when no_data_found then
959 --
960 if (g_ass_set_amendment_type is not null
961 and g_ass_set_amendment_type = 'E')
962 or g_ass_set_formula_id is not null then
963 --
964 l_ass_id_tbl_cnt := l_ass_id_tbl_cnt + 1;
965 l_ass_id_tbl(l_ass_id_tbl_cnt) := l_csr_imp_file_ass.assignment_id;
966 --
967 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_id := l_csr_imp_file_ass.assignment_id;
968 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).district_code := l_csr_imp_file_ass.rep_district_code;
969 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).organization_id := l_csr_imp_file_ass.organization_id;
970 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).swot_number := l_csr_imp_file_ass.swot_number;
971 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).input_file_name := l_csr_imp_file_ass.input_file_name;
972 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_number := l_csr_imp_file_ass.assignment_number;
973 -- go through formula validation
974 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).include_or_exclude := null;
975 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).regular_payment_date := l_csr_imp_file_ass.regular_payment_date;
976 --
977 end if;
978 --
979 end;
980 --
981 else
982 --
983 -- no amendment
984 --
985 l_ass_id_tbl_cnt := l_ass_id_tbl_cnt + 1;
986 l_ass_id_tbl(l_ass_id_tbl_cnt) := l_csr_imp_file_ass.assignment_id;
987 --
988 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_id := l_csr_imp_file_ass.assignment_id;
989 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).district_code := l_csr_imp_file_ass.rep_district_code;
990 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).organization_id := l_csr_imp_file_ass.organization_id;
991 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).swot_number := l_csr_imp_file_ass.swot_number;
992 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).input_file_name := l_csr_imp_file_ass.input_file_name;
993 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).assignment_number := l_csr_imp_file_ass.assignment_number;
994 -- go through formula validation
995 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).include_or_exclude := null;
996 l_ass_file_tbl(l_csr_imp_file_ass.assignment_id).regular_payment_date := l_csr_imp_file_ass.regular_payment_date;
997 --
998 end if;
999 --
1000 end loop;
1001 close csr_imp_file_ass;
1002 --
1003 if g_debug then
1004 hr_utility.set_location(l_proc,40);
1005 hr_utility.trace('l_ass_id_tbl.count : '||to_char(l_ass_id_tbl.count));
1006 hr_utility.trace('l_ass_file_tbl.count : '||to_char(l_ass_file_tbl.count));
1007 hr_utility.trace('g_ass_amd_ind_tbl.count : '||to_char(g_ass_amd_ind_tbl.count));
1008 end if;
1009 --
1010 if l_ass_id_tbl.count > 0 then
1011 --
1012 for i in 1..l_ass_id_tbl.count loop
1013 --
1014 l_imp_file := false;
1015 --
1016 if g_ass_set_formula_id is not null
1017 and l_ass_file_tbl(l_ass_id_tbl(i)).include_or_exclude is null then
1018 --
1019 if hr_jp_ast_utility_pkg.formula_validate(
1020 p_formula_id => g_ass_set_formula_id,
1021 p_assignment_id => l_ass_id_tbl(i),
1022 p_effective_date => l_ass_file_tbl(l_ass_id_tbl(i)).regular_payment_date,
1023 p_populate_fs => true) then
1024 --
1025 if l_imp_file_tbl.count > 0
1026 and l_imp_file_tbl_cnt > 0 then
1027 --
1028 if nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).input_file_name,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).input_file_name,'X')
1029 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).district_code,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).district_code,'X')
1030 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).organization_id,-1) <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).organization_id,-1)
1031 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).swot_number,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).swot_number,'X') then
1032 --
1033 l_imp_file := true;
1034 --
1035 end if;
1036 --
1037 end if;
1038 --
1039 if l_imp_file_tbl_cnt = 0
1040 or l_imp_file then
1041 --
1042 l_imp_file_tbl_cnt := l_imp_file_tbl_cnt + 1;
1043 l_imp_file_tbl(l_imp_file_tbl_cnt).district_code := l_ass_file_tbl(l_ass_id_tbl(i)).district_code;
1044 l_imp_file_tbl(l_imp_file_tbl_cnt).organization_id := l_ass_file_tbl(l_ass_id_tbl(i)).organization_id;
1045 l_imp_file_tbl(l_imp_file_tbl_cnt).swot_number := l_ass_file_tbl(l_ass_id_tbl(i)).swot_number;
1046 l_imp_file_tbl(l_imp_file_tbl_cnt).input_file_name := l_ass_file_tbl(l_ass_id_tbl(i)).input_file_name;
1047 --
1048 end if;
1049 --
1050 end if;
1051 --
1052 else
1053 --
1054 if l_imp_file_tbl.count > 0
1055 and l_imp_file_tbl_cnt > 0
1056 and (l_ass_file_tbl(l_ass_id_tbl(i)).include_or_exclude is null
1057 or l_ass_file_tbl(l_ass_id_tbl(i)).include_or_exclude <> 'E') then
1058 --
1059 if nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).input_file_name,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).input_file_name,'X')
1060 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).district_code,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).district_code,'X')
1061 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).organization_id,-1) <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).organization_id,-1)
1062 or nvl(l_imp_file_tbl(l_imp_file_tbl_cnt).swot_number,'X') <> nvl(l_ass_file_tbl(l_ass_id_tbl(i)).swot_number,'X') then
1063 --
1064 l_imp_file := true;
1065 --
1066 end if;
1067 --
1068 end if;
1069 --
1070 if (l_imp_file_tbl_cnt = 0
1071 or l_imp_file) then
1072 --
1073 l_imp_file_tbl_cnt := l_imp_file_tbl_cnt + 1;
1074 l_imp_file_tbl(l_imp_file_tbl_cnt).district_code := l_ass_file_tbl(l_ass_id_tbl(i)).district_code;
1075 l_imp_file_tbl(l_imp_file_tbl_cnt).organization_id := l_ass_file_tbl(l_ass_id_tbl(i)).organization_id;
1076 l_imp_file_tbl(l_imp_file_tbl_cnt).swot_number := l_ass_file_tbl(l_ass_id_tbl(i)).swot_number;
1077 l_imp_file_tbl(l_imp_file_tbl_cnt).input_file_name := l_ass_file_tbl(l_ass_id_tbl(i)).input_file_name;
1078 --
1079 end if;
1080 --
1081 end if;
1082 --
1083 end loop;
1084 --
1085 end if;
1086 --
1087 if g_debug then
1088 hr_utility.set_location(l_proc,60);
1089 hr_utility.trace('l_imp_file_tbl.count : '||to_char(l_imp_file_tbl.count));
1090 end if;
1091 --
1092 end if;
1093 --
1094 if g_debug then
1095 hr_utility.set_location(l_proc,70);
1096 end if;
1097 --
1098 if l_imp_file_tbl.count > 0 then
1099 --
1100 for i in 1..l_imp_file_tbl.count loop
1101 --
1102 if l_imp_file_tbl(i).input_file_name is not null then
1103 --
1104 l_input_file_name := l_imp_file_tbl(i).input_file_name;
1105 --
1106 if l_input_file_name_o is not null
1107 and l_input_file_name_o = l_input_file_name then
1108 --
1109 if g_show_dup_file = 'Y' then
1110 --
1111 g_dup_file_wng_tbl_cnt := g_dup_file_wng_tbl_cnt + 1;
1112 g_dup_file_wng_tbl(g_dup_file_wng_tbl_cnt) := l_input_file_name||' : '||
1113 to_char(l_organization_id_o)||','||l_district_code_o||' - '||
1114 to_char(l_imp_file_tbl(i).organization_id)||','||l_imp_file_tbl(i).district_code;
1115 --
1116 end if;
1117 --
1118 else
1119 --
1120 l_file_tbl_cnt := l_file_tbl_cnt + 1;
1121 --
1122 l_file_tbl(l_file_tbl_cnt).file_name := l_input_file_name;
1123 --
1124 l_imp_file_ind_tbl(l_file_tbl_cnt).district_code := l_imp_file_tbl(i).district_code;
1125 l_imp_file_ind_tbl(l_file_tbl_cnt).organization_id := l_imp_file_tbl(i).organization_id;
1126 l_imp_file_ind_tbl(l_file_tbl_cnt).swot_number := l_imp_file_tbl(i).swot_number;
1127 l_imp_file_ind_tbl(l_file_tbl_cnt).input_file_name := l_imp_file_tbl(i).input_file_name;
1128 --
1129 l_input_file_name_o := l_input_file_name;
1130 l_district_code_o := l_imp_file_tbl(i).district_code;
1131 l_organization_id_o := l_imp_file_tbl(i).organization_id;
1132 --
1133 end if;
1134 --
1135 else
1136 --
1137 --l_input_file_name := default_file_name(l_imp_file_tbl(i).district_code,null,null);
1138 l_input_file_name := default_file_name(l_imp_file_tbl(i).district_code,l_imp_file_tbl(i).organization_id,l_imp_file_tbl(i).swot_number);
1139 --
1140 -- basically this validation will not be required because swot num should be unique
1141 if l_input_file_name_o is not null
1142 and l_input_file_name_o = l_input_file_name then
1143 --
1144 if g_show_dup_file = 'Y' then
1145 --
1146 g_dup_file_wng_tbl_cnt := g_dup_file_wng_tbl_cnt + 1;
1147 g_dup_file_wng_tbl(g_dup_file_wng_tbl_cnt) := l_input_file_name||' : '||
1148 to_char(l_organization_id_o)||','||l_district_code_o||' - '||
1149 to_char(l_imp_file_tbl(i).organization_id)||','||l_imp_file_tbl(i).district_code;
1150 --
1151 end if;
1152 --
1153 else
1154 --
1155 --if l_district_code_o is not null
1156 --and l_district_code_o = l_imp_file_tbl(i).district_code then
1157 ----
1158 -- l_file_tbl(l_file_tbl_cnt - 1).file_name := default_file_name(l_imp_file_tbl(i - 1).district_code,l_imp_file_tbl(i - 1).organization_id,l_imp_file_tbl(i - 1).swot_number);
1159 -- l_file_tbl(l_file_tbl_cnt).file_name := default_file_name(l_imp_file_tbl(i).district_code,l_imp_file_tbl(i).organization_id,l_imp_file_tbl(i).swot_number);
1160 ----
1161 --end if;
1162 --
1163 l_file_tbl_cnt := l_file_tbl_cnt + 1;
1164 --
1165 l_file_tbl(l_file_tbl_cnt).file_name := l_input_file_name;
1166 --
1167 l_imp_file_ind_tbl(l_file_tbl_cnt).district_code := l_imp_file_tbl(i).district_code;
1168 l_imp_file_ind_tbl(l_file_tbl_cnt).organization_id := l_imp_file_tbl(i).organization_id;
1169 l_imp_file_ind_tbl(l_file_tbl_cnt).swot_number := l_imp_file_tbl(i).swot_number;
1170 l_imp_file_ind_tbl(l_file_tbl_cnt).input_file_name := l_imp_file_tbl(i).input_file_name;
1171 --
1172 l_input_file_name_o := l_input_file_name;
1173 l_district_code_o := l_imp_file_tbl(i).district_code;
1174 l_organization_id_o := l_imp_file_tbl(i).organization_id;
1175 --
1176 end if;
1177 --
1178 end if;
1179 --
1180 end loop;
1181 --
1182 end if;
1183 --
1184 if g_debug then
1185 hr_utility.set_location(l_proc,80);
1186 hr_utility.trace('l_file_tbl.count : '||to_char(l_file_tbl.count));
1187 hr_utility.trace('l_imp_file_ind_tbl.count : '||to_char(l_imp_file_ind_tbl.count));
1188 hr_utility.trace('g_show_dup_file : '||g_show_dup_file);
1189 hr_utility.trace('g_dup_file_wng_tbl.count : '||to_char(g_dup_file_wng_tbl.count));
1190 end if;
1191 --
1192 else
1193 --
1194 if g_debug then
1195 hr_utility.set_location(l_proc,90);
1196 end if;
1197 --
1198 l_file_tbl_cnt := l_file_tbl_cnt + 1;
1199 l_file_tbl(l_file_tbl_cnt).file_name := default_file_name(null,null,null);
1200 --
1201 if g_debug then
1202 hr_utility.set_location(l_proc,100);
1203 end if;
1204 --
1205 if g_ass_set_amendment_type is not null
1206 and g_ass_set_amendment_type <> 'N' then
1207 --
1208 open csr_ass_amd;
1209 fetch csr_ass_amd bulk collect into l_ass_amd_tbl;
1210 close csr_ass_amd;
1211 --
1212 if l_ass_amd_tbl.count > 0 then
1213 --
1214 for i in 1..l_ass_amd_tbl.count loop
1215 --
1216 g_ass_amd_ind_tbl(l_ass_amd_tbl(i).assignment_id).assignment_id := l_ass_amd_tbl(i).assignment_id;
1217 g_ass_amd_ind_tbl(l_ass_amd_tbl(i).assignment_id).include_or_exclude := l_ass_amd_tbl(i).include_or_exclude;
1218 --
1219 end loop;
1220 --
1221 end if;
1222 --
1223 end if;
1224 --
1225 if g_debug then
1226 hr_utility.set_location(l_proc,110);
1227 hr_utility.trace('l_ass_amd_tbl.count : '||to_char(l_ass_amd_tbl.count));
1228 hr_utility.trace('g_ass_amd_ind_tbl.count : '||to_char(g_ass_amd_ind_tbl.count));
1229 end if;
1230 --
1231 end if;
1232 --
1233 if g_debug then
1234 hr_utility.set_location(l_proc,120);
1235 hr_utility.trace('l_file_tbl.count : '||to_char(l_file_tbl.count));
1236 end if;
1237 --
1238 if g_debug
1239 and g_detail_debug = 'Y' then
1240 --
1241 if l_file_tbl.count > 0 then
1242 --
1243 for i in 1..l_file_tbl.count loop
1244 --
1245 hr_utility.trace(to_char(i)||' : '||l_file_tbl(i).file_name);
1246 --
1247 end loop;
1248 --
1249 end if;
1250 --
1251 end if;
1252 --
1253 if l_file_tbl.count > 0 then
1254 --
1255 for i in 1..l_file_tbl.count loop
1256 --
1257 if pay_jp_report_pkg.check_file(
1258 l_file_tbl(i).file_name,
1259 g_file_dir) then
1260 --
1261 g_file_tbl_cnt := g_file_tbl_cnt + 1;
1262 g_file_tbl(g_file_tbl_cnt).file_name := l_file_tbl(i).file_name;
1263 --
1264 -- set only case g_file_split = Y
1265 if l_imp_file_ind_tbl.count > 0 then
1266 --
1267 g_imp_file_ind_tbl(g_file_tbl_cnt).district_code := l_imp_file_ind_tbl(i).district_code;
1268 g_imp_file_ind_tbl(g_file_tbl_cnt).organization_id := l_imp_file_ind_tbl(i).organization_id;
1269 g_imp_file_ind_tbl(g_file_tbl_cnt).swot_number := l_imp_file_ind_tbl(i).swot_number;
1270 g_imp_file_ind_tbl(g_file_tbl_cnt).input_file_name := l_imp_file_ind_tbl(i).input_file_name;
1271 --
1272 end if;
1273 --
1274 else
1275 --
1276 if g_show_no_file = 'Y' then
1277 --
1278 g_no_file_wng_tbl_cnt := g_no_file_wng_tbl_cnt + 1;
1279 g_no_file_wng_tbl(g_no_file_wng_tbl_cnt) := l_file_tbl(i).file_name;
1280 --
1281 end if;
1282 --
1283 end if;
1284 --
1285 end loop;
1286 --
1287 end if;
1288 --
1289 if g_debug then
1290 hr_utility.set_location(l_proc,130);
1291 hr_utility.trace('g_file_tbl.count : '||to_char(g_file_tbl.count));
1292 hr_utility.trace('g_imp_file_ind_tbl.count : '||to_char(g_imp_file_ind_tbl.count));
1293 hr_utility.trace('g_show_no_file : '||g_show_no_file);
1294 hr_utility.trace('g_no_file_wng_tbl.count : '||to_char(g_no_file_wng_tbl.count));
1295 end if;
1296 --
1297 if g_debug
1298 and g_detail_debug = 'Y' then
1299 --
1300 if g_file_tbl.count > 0 then
1301 --
1302 for i in 1..g_file_tbl.count loop
1303 --
1304 hr_utility.trace(to_char(i)||' : '||g_file_tbl(i).file_name);
1305 --
1306 end loop;
1307 --
1308 end if;
1309 --
1310 end if;
1311 --
1312 if g_file_tbl.count > 0 then
1313 --
1314 <<loop_file_tbl>>
1315 for i in 1..g_file_tbl.count loop
1316 --
1317 pay_jp_report_pkg.open_file(
1318 g_file_tbl(i).file_name,
1319 g_file_dir,
1320 g_file_tbl(i).file_out,
1321 'r');
1322 --
1323 l_file_line := 0;
1324 --
1325 pay_jp_report_pkg.read_file(
1326 g_file_tbl(i).file_name,
1327 g_file_tbl(i).file_out,
1328 l_file_data_tbl);
1329 --
1330 if g_debug
1331 and g_detail_debug = 'Y' then
1332 --
1333 hr_utility.trace(g_file_tbl(i).file_name||' : '||l_file_data_tbl.count);
1334 --
1335 end if;
1336 --
1337 if l_file_data_tbl.count > 0 then
1338 --
1339 <<loop_data_tbl>>
1340 for j in l_file_data_tbl.first..l_file_data_tbl.last loop
1341 --
1342 l_cnv_data := pay_jp_report_pkg.cnv_db_txt(l_file_data_tbl(j));
1343 --
1344 if g_show_data = 'Y' then
1345 --
1346 pay_jp_report_pkg.show_debug(l_cnv_data);
1347 --
1348 <<loop_split_data>>
1349 for k in 1..21 loop
1350 --
1351 hr_utility.trace(to_char(k)||' : '||pay_jp_report_pkg.split_str(l_cnv_data,k));
1352 --
1353 end loop loop_split_data;
1354 --
1355 end if;
1356 --
1357 g_imp_data_tbl_cnt := g_imp_data_tbl_cnt + 1;
1358 l_file_line := l_file_line + 1;
1359 g_imp_data_tbl(g_imp_data_tbl_cnt).file_id := i;
1360 g_imp_data_tbl(g_imp_data_tbl_cnt).line := l_file_line;
1361 --
1362 g_imp_data_tbl(g_imp_data_tbl_cnt).i_swot_number := substrb(pay_jp_report_pkg.split_str(l_cnv_data,1),1,15);
1363 g_imp_data_tbl(g_imp_data_tbl_cnt).i_personal_number := substrb(pay_jp_report_pkg.split_str(l_cnv_data,2),1,60);
1364 g_imp_data_tbl(g_imp_data_tbl_cnt).i_employee_number := substrb(pay_jp_report_pkg.split_str(l_cnv_data,3),1,30);
1365 g_imp_data_tbl(g_imp_data_tbl_cnt).i_address := substrb(pay_jp_report_pkg.split_str(l_cnv_data,4),1,800);
1366 g_imp_data_tbl(g_imp_data_tbl_cnt).i_address_kana := substrb(pay_jp_report_pkg.split_str(l_cnv_data,5),1,800);
1367 g_imp_data_tbl(g_imp_data_tbl_cnt).i_full_name := substrb(pay_jp_report_pkg.split_str(l_cnv_data,6),1,400);
1368 g_imp_data_tbl(g_imp_data_tbl_cnt).i_full_name_kana := substrb(pay_jp_report_pkg.split_str(l_cnv_data,7),1,400);
1369 g_imp_data_tbl(g_imp_data_tbl_cnt).i_sp_ltax := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,8));
1370 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_6 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,9));
1371 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_7 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,10));
1372 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_8 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,11));
1373 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_9 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,12));
1374 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_10 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,13));
1375 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_11 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,14));
1376 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_12 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,15));
1377 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_1 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,16));
1378 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_2 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,17));
1379 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_3 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,18));
1380 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_4 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,19));
1381 g_imp_data_tbl(g_imp_data_tbl_cnt).i_ltax_5 := cnv_num(pay_jp_report_pkg.split_str(l_cnv_data,20));
1382 g_imp_data_tbl(g_imp_data_tbl_cnt).i_district_code := substrb(pay_jp_report_pkg.split_str(l_cnv_data,21),1,6);
1383 --
1384 -- validate date is all numeric
1385 if lengthb(ltrim(rtrim(g_imp_data_tbl(g_imp_data_tbl_cnt).i_district_code))) = 5
1386 and (lengthb(replace(translate(ltrim(rtrim(g_imp_data_tbl(g_imp_data_tbl_cnt).i_district_code)),'0123456789','*'),'*','')) is null
1387 or lengthb(replace(translate(ltrim(rtrim(g_imp_data_tbl(g_imp_data_tbl_cnt).i_district_code)),'0123456789','*'),'*','')) = 0) then
1388 --
1389 g_imp_data_tbl(g_imp_data_tbl_cnt).i_district_code := ltrim(rtrim(substrb(pay_jp_report_pkg.split_str(l_cnv_data,21),1,6)))
1390 ||per_jp_validations.district_code_check_digit(ltrim(rtrim(substrb(pay_jp_report_pkg.split_str(l_cnv_data,21),1,6))));
1391 --
1392 end if;
1393 --
1394 end loop loop_data_tbl;
1395 --
1396 end if;
1397 --
1398 pay_jp_report_pkg.close_file(
1399 g_file_tbl(i).file_name,
1400 g_file_tbl(i).file_out,
1401 'r');
1402 --
1403 end loop loop_file_tbl;
1404 --
1405 end if;
1406 --
1407 if g_debug then
1408 hr_utility.set_location(l_proc,140);
1409 hr_utility.trace('g_imp_data_tbl.count : '||to_char(g_imp_data_tbl.count));
1410 end if;
1411 --
1412 if g_debug
1413 and g_detail_debug = 'Y' then
1414 --
1415 if g_imp_data_tbl.count > 0 then
1416 --
1417 for i in 1..g_imp_data_tbl.count loop
1418 --
1419 hr_utility.trace(to_char(i)||' :'||to_char(g_imp_data_tbl(i).file_id)||','||to_char(g_imp_data_tbl(i).line));
1420 --
1421 end loop;
1422 --
1423 end if;
1424 --
1425 end if;
1426 --
1427 if g_debug then
1428 hr_utility.set_location(l_proc,1000);
1429 end if;
1430 --
1431 end imp_file_data;
1432 --
1433 -- -------------------------------------------------------------------------
1434 -- get_ltxi_assignment
1435 -- -------------------------------------------------------------------------
1436 procedure get_ltxi_assignment(
1437 p_swot_number in varchar2,
1438 p_employee_number in varchar2,
1439 p_district_code in varchar2,
1440 p_assignment_id out nocopy number,
1441 p_payroll_id out nocopy number,
1442 p_assignment_number out nocopy varchar2,
1443 p_regular_payment_date out nocopy date,
1444 p_final_process_date out nocopy date)
1445 is
1446 --
1447 l_proc varchar2(80) := c_package||'get_ltxi_assignment';
1448 --
1449 l_assignment_id per_all_assignments_f.assignment_id%type;
1450 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1451 l_assignment_number per_all_assignments_f.assignment_number%type;
1452 l_regular_payment_date date;
1453 l_final_process_date date;
1454 --
1455 cursor csr_ass
1456 is
1457 select pa.assignment_id,
1458 pa.payroll_id,
1459 pa.assignment_number,
1460 ptp.regular_payment_date,
1461 greatest(ppos.actual_termination_date,nvl(ppos.final_process_date,ppos.actual_termination_date)) final_process_date
1462 from per_all_people_f pp,
1463 per_all_assignments_f pa,
1464 per_time_periods ptp,
1465 per_periods_of_service ppos
1466 where pp.business_group_id + 0 = g_business_group_id
1467 and pp.effective_start_date <= g_effective_eom
1468 and pp.effective_end_date >= g_effective_som
1469 and pp.employee_number = p_employee_number
1470 and pa.person_id = pp.person_id
1471 and pa.primary_flag = 'Y'
1472 and pa.effective_start_date <= g_effective_eom
1473 and pa.effective_end_date >= g_effective_som
1474 and ptp.payroll_id = pa.payroll_id
1475 and ptp.regular_payment_date
1476 between g_effective_som and g_effective_eom
1477 and ptp.regular_payment_date
1478 between pa.effective_start_date and pa.effective_end_date
1479 and ptp.regular_payment_date
1480 between pp.effective_start_date and pp.effective_end_date
1481 and ppos.person_id = pp.person_id
1482 and ppos.period_of_service_id = pa.period_of_service_id
1483 and ptp.regular_payment_date
1484 between ppos.date_start and nvl(greatest(ppos.actual_termination_date,nvl(ppos.final_process_date,ppos.actual_termination_date)),ptp.regular_payment_date)
1485 and exists(
1486 select null
1487 from per_addresses pad,
1488 pay_jp_swot_numbers pjsn_act,
1489 pay_jp_swot_numbers pjsn_rep,
1490 hr_all_organization_units hou,
1491 hr_organization_information hoi
1492 where pad.person_id = pp.person_id
1493 and pad.address_type in ('JP_C','JP_R')
1494 and nvl(ppos.actual_termination_date,g_effective_soy)
1495 between pad.date_from and nvl(pad.date_to,hr_api.g_eot)
1496 and substrb(pjsn_act.district_code,1,5) = pad.town_or_city
1497 and pjsn_rep.organization_id = pjsn_act.organization_id
1498 and pjsn_rep.district_code = nvl(pjsn_act.report_district_code,pjsn_act.district_code)
1499 and substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5) = nvl(g_district_code,substrb(nvl(pjsn_act.report_district_code,pjsn_act.district_code),1,5))
1500 and nvl(pjsn_act.report_district_code,pjsn_act.district_code) = p_district_code
1501 and nvl(pjsn_rep.import_exclusive_flag,'N') = 'N'
1502 and pjsn_rep.swot_number = p_swot_number
1503 and hou.organization_id = pjsn_rep.organization_id
1504 and hou.business_group_id + 0 = g_business_group_id
1505 and hou.date_from <= g_effective_eoy
1506 and nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
1507 and hou.organization_id = nvl(g_organization_id,hou.organization_id)
1508 and hou.organization_id = hoi.organization_id
1509 and hoi.org_information_context = 'CLASS'
1510 and hoi.org_information1 = 'JP_TAX_SWOT'
1511 --exclude different itax are mixed in one file (swot_number should be different).
1512 and pay_jp_balance_pkg.get_entry_value_number(c_itx_org_iv_id,pa.assignment_id,ptp.regular_payment_date) = hoi.organization_id);
1513 --
1514 begin
1515 --
1516 if g_debug
1517 and g_detail_debug = 'Y' then
1518 hr_utility.set_location(l_proc,0);
1519 end if;
1520 --
1521 open csr_ass;
1522 fetch csr_ass into
1523 l_assignment_id,
1524 l_payroll_id,
1525 l_assignment_number,
1526 l_regular_payment_date,
1527 l_final_process_date;
1528 close csr_ass;
1529 --
1530 p_assignment_id := l_assignment_id;
1531 p_payroll_id := l_payroll_id;
1532 p_assignment_number := l_assignment_number;
1533 p_regular_payment_date := l_regular_payment_date;
1534 p_final_process_date := l_final_process_date;
1535 --
1536 if g_debug
1537 and g_detail_debug = 'Y' then
1538 hr_utility.trace('p_swot_number : '||p_swot_number);
1539 hr_utility.trace('p_employee_number : '||p_employee_number);
1540 hr_utility.trace('p_district_code : '||p_district_code);
1541 hr_utility.trace('l_assignment_id : '||to_char(l_assignment_id));
1542 hr_utility.trace('l_payroll_id : '||to_char(l_payroll_id));
1543 hr_utility.trace('l_assignment_number : '||l_assignment_number);
1544 hr_utility.trace('l_regular_payment_date : '||to_char(l_regular_payment_date,'YYYY/MM/DD'));
1545 hr_utility.trace('l_final_process_date : '||to_char(l_final_process_date,'YYYY/MM/DD'));
1546 hr_utility.set_location(l_proc,1000);
1547 end if;
1548 --
1549 end get_ltxi_assignment;
1550 --
1551 -- -------------------------------------------------------------------------
1552 -- valid_ltxi_assignment
1553 -- -------------------------------------------------------------------------
1554 procedure valid_ltxi_assignment(
1555 p_assignment_id in number,
1556 p_assignment_number in varchar2,
1557 p_regular_payment_date in date,
1558 p_imp_data_tbl_ind in number,
1559 p_valid out nocopy boolean)
1560 --
1561 is
1562 --
1563 l_proc varchar2(80) := c_package||'valid_ltxi_assignment';
1564 --
1565 l_valid boolean;
1566 --
1567 l_assignment_id per_all_assignments_f.assignment_id%type;
1568 l_include_or_exclude hr_assignment_set_amendments.include_or_exclude%type;
1569 --
1570 l_non_res_date date;
1571 l_res_date date;
1572 l_non_res_flag hr_lookups.lookup_code%type;
1573 --
1574 begin
1575 --
1576 if g_debug
1577 and g_detail_debug = 'Y' then
1578 hr_utility.set_location(l_proc,0);
1579 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
1580 hr_utility.trace('g_assignment_set_id : '||to_char(g_assignment_set_id));
1581 hr_utility.trace('g_ass_amd_ind_tbl.count : '||to_char(g_ass_amd_ind_tbl.count));
1582 end if;
1583 --
1584 l_valid := true;
1585 --
1586 if p_assignment_id is not null
1587 and g_assignment_set_id is not null then
1588 --
1589 if g_ass_amd_ind_tbl.count > 0 then
1590 --
1591 begin
1592 --
1593 l_assignment_id := g_ass_amd_ind_tbl(p_assignment_id).assignment_id;
1594 l_include_or_exclude := g_ass_amd_ind_tbl(p_assignment_id).include_or_exclude;
1595 --
1596 if l_include_or_exclude is not null
1597 and l_include_or_exclude = 'E' then
1598 --
1599 l_valid := false;
1600 --
1601 end if;
1602 --
1603 exception
1604 when no_data_found then
1605 --
1606 if (g_ass_set_amendment_type is not null
1607 and g_ass_set_amendment_type = 'E')
1608 or g_ass_set_formula_id is not null then
1609 --
1610 null;
1611 --
1612 else
1613 --
1614 l_valid := false;
1615 --
1616 end if;
1617 --
1618 end;
1619 --
1620 end if;
1621 --
1622 if g_ass_set_formula_id is not null
1623 and l_include_or_exclude is null then
1624 --
1625 if not hr_jp_ast_utility_pkg.formula_validate(
1626 p_formula_id => g_ass_set_formula_id,
1627 p_assignment_id => p_assignment_id,
1628 p_effective_date => p_regular_payment_date,
1629 p_populate_fs => true) then
1630 --
1631 l_valid := false;
1632 --
1633 end if;
1634 --
1635 end if;
1636 --
1637 end if;
1638 --
1639 if g_debug
1640 and g_detail_debug = 'Y' then
1641 --
1642 hr_utility.set_location(l_proc,10);
1643 --
1644 if l_valid then
1645 hr_utility.trace('ass set valid true');
1646 end if;
1647 --
1648 end if;
1649 --
1650 if l_valid
1651 and p_assignment_id is null then
1652 --
1653 l_valid := false;
1654 --
1655 if ((g_file_split is null
1656 or g_file_split = 'Y')
1657 or (g_file_split = 'N'
1658 and g_district_code is null
1659 and g_organization_id is null)) then
1660 --
1661 g_no_ass_wng_tbl_cnt := g_no_ass_wng_tbl_cnt + 1;
1662 g_no_ass_wng_tbl(g_no_ass_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(p_imp_data_tbl_ind).file_id).file_name||' ('||to_char(g_imp_data_tbl(p_imp_data_tbl_ind).line)||') : '||
1663 g_imp_data_tbl(p_imp_data_tbl_ind).i_swot_number||','||g_imp_data_tbl(p_imp_data_tbl_ind).i_employee_number||','||g_imp_data_tbl(p_imp_data_tbl_ind).i_district_code;
1664 --
1665 end if;
1666 --
1667 end if;
1668 --
1669 if g_debug
1670 and g_detail_debug = 'Y' then
1671 --
1672 hr_utility.set_location(l_proc,20);
1673 hr_utility.trace('g_no_ass_wng_tbl.count : '||to_char(g_no_ass_wng_tbl.count));
1674 --
1675 if l_valid then
1676 hr_utility.trace('g_no_ass_wng_tbl valid true');
1677 end if;
1678 --
1679 end if;
1680 --
1681 if l_valid then
1682 --
1683 l_non_res_date := pay_jp_balance_pkg.get_entry_value_date(c_non_res_date_iv_id,p_assignment_id,p_regular_payment_date);
1684 l_res_date := nvl(pay_jp_balance_pkg.get_entry_value_date(c_res_date_iv_id,p_assignment_id,p_regular_payment_date),hr_api.g_eot);
1685 --
1686 if g_debug
1687 and g_detail_debug = 'Y' then
1688 hr_utility.set_location(l_proc,30);
1689 hr_utility.trace('l_non_res_date : '||to_char(l_non_res_date,'YYYY/MM/DD'));
1690 hr_utility.trace('l_res_date : '||to_char(l_res_date,'YYYY/MM/DD'));
1691 end if;
1692 --
1693 if l_non_res_date is not null then
1694 --
1695 l_non_res_flag := 'N';
1696 if l_non_res_date <= p_regular_payment_date
1697 and p_regular_payment_date < l_res_date then
1698 --
1699 l_non_res_flag := 'Y';
1700 --
1701 end if;
1702 --
1703 else
1704 --
1705 l_non_res_flag := nvl(pay_jp_balance_pkg.get_entry_value_char(c_non_res_iv_id,p_assignment_id,p_regular_payment_date),'N');
1706 --
1707 end if;
1708 --
1709 if g_debug
1710 and g_detail_debug = 'Y' then
1711 hr_utility.set_location(l_proc,40);
1712 hr_utility.trace('l_non_res_flag : '||l_non_res_flag);
1713 end if;
1714 --
1715 if l_non_res_flag is not null
1716 and l_non_res_flag = 'Y' then
1717 --
1718 g_non_res_wng_tbl_cnt := g_non_res_wng_tbl_cnt + 1;
1719 g_non_res_wng_tbl(g_non_res_wng_tbl_cnt) := p_assignment_number||' ('||to_char(p_assignment_id)||')';
1720 --
1721 if g_valid_non_res = 'Y' then
1722 --
1723 l_valid := false;
1724 --
1725 end if;
1726 --
1727 end if;
1728 --
1729 end if;
1730 --
1731 if g_debug
1732 and g_detail_debug = 'Y' then
1733 --
1734 hr_utility.set_location(l_proc,50);
1735 hr_utility.trace('g_valid_non_res : '||g_valid_non_res);
1736 hr_utility.trace('g_non_res_wng_tbl.count : '||to_char(g_non_res_wng_tbl.count));
1737 --
1738 if l_valid then
1739 hr_utility.trace('g_non_res_wng_tbl valid true');
1740 end if;
1741 --
1742 end if;
1743 --
1744 if l_valid then
1745 --
1746 if g_ass_ind_tbl.count > 0 then
1747 --
1748 begin
1749 --
1750 l_assignment_id := g_ass_ind_tbl(p_assignment_id).assignment_id;
1751 --
1752 g_dup_ass_wng_tbl_cnt := g_dup_ass_wng_tbl_cnt + 1;
1753 g_dup_ass_wng_tbl(g_dup_ass_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(p_imp_data_tbl_ind).file_id).file_name||' ('||to_char(g_imp_data_tbl(p_imp_data_tbl_ind).line)||') - '||
1754 g_file_tbl(g_ass_data_tbl(l_assignment_id).file_id).file_name||' ('||to_char(g_ass_data_tbl(l_assignment_id).line)||') : '||
1755 p_assignment_number||' ('||to_char(p_assignment_id)||') : '||
1756 g_imp_data_tbl(p_imp_data_tbl_ind).i_swot_number||','||g_imp_data_tbl(p_imp_data_tbl_ind).i_employee_number||','||g_imp_data_tbl(p_imp_data_tbl_ind).i_district_code;
1757 --
1758 if g_valid_dup_ass = 'Y' then
1759 --
1760 l_valid := false;
1761 --
1762 end if;
1763 --
1764 exception
1765 when no_data_found then
1766 --
1767 null;
1768 --
1769 end;
1770 --
1771 end if;
1772 --
1773 end if;
1774 --
1775 if g_debug
1776 and g_detail_debug = 'Y' then
1777 --
1778 hr_utility.set_location(l_proc,60);
1779 hr_utility.trace('g_ass_ind_tbl.count : '||to_char(g_ass_ind_tbl.count));
1780 hr_utility.trace('g_valid_dup_ass : '||g_valid_dup_ass);
1781 hr_utility.trace('g_dup_ass_wng_tbl.count : '||to_char(g_dup_ass_wng_tbl.count));
1782 --
1783 end if;
1784 --
1785 p_valid := l_valid;
1786 --
1787 if g_debug
1788 and g_detail_debug = 'Y' then
1789 hr_utility.set_location(l_proc,1000);
1790 end if;
1791 --
1792 end valid_ltxi_assignment;
1793 --
1794 -- -------------------------------------------------------------------------
1795 -- valid_file_data
1796 -- -------------------------------------------------------------------------
1797 procedure valid_file_data
1798 is
1799 --
1800 l_proc varchar2(80) := c_package||'valid_file_data';
1801 --
1802 l_assignment_id per_all_assignments_f.assignment_id%type;
1803 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1804 l_assignment_number per_all_assignments_f.assignment_number%type;
1805 l_regular_payment_date date;
1806 l_final_process_date date;
1807 --
1808 l_valid boolean;
1809 --
1810 begin
1811 --
1812 if g_debug
1813 and g_detail_debug = 'Y' then
1814 hr_utility.set_location(l_proc,0);
1815 hr_utility.trace('g_imp_data_tbl.count : '||to_char(g_imp_data_tbl.count));
1816 hr_utility.trace('g_imp_file_ind_tbl.count : '||to_char(g_imp_file_ind_tbl.count));
1817 end if;
1818 --
1819 if g_imp_data_tbl.count > 0 then
1820 --
1821 <<loop_data_tbl>>
1822 for i in 1..g_imp_data_tbl.count loop
1823 --
1824 l_valid := true;
1825 --
1826 if g_imp_data_tbl(i).i_swot_number is not null
1827 and g_imp_data_tbl(i).i_employee_number is not null
1828 and g_imp_data_tbl(i).i_district_code is not null then
1829 --
1830 if g_imp_data_tbl(i).i_ltax_7 is not null
1831 and g_imp_data_tbl(i).i_ltax_8 is not null
1832 and g_imp_data_tbl(i).i_ltax_9 is not null
1833 and g_imp_data_tbl(i).i_ltax_10 is not null
1834 and g_imp_data_tbl(i).i_ltax_11 is not null
1835 and g_imp_data_tbl(i).i_ltax_12 is not null
1836 and g_imp_data_tbl(i).i_ltax_1 is not null
1837 and g_imp_data_tbl(i).i_ltax_2 is not null
1838 and g_imp_data_tbl(i).i_ltax_3 is not null
1839 and g_imp_data_tbl(i).i_ltax_4 is not null
1840 and g_imp_data_tbl(i).i_ltax_5 is not null then
1841 --
1842 if not (
1843 g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_8
1844 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_9
1845 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_10
1846 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_11
1847 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_12
1848 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_1
1849 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_2
1850 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_3
1851 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_4
1852 and g_imp_data_tbl(i).i_ltax_7 = g_imp_data_tbl(i).i_ltax_5) then
1853 --
1854 g_diff_ltax_wng_tbl_cnt := g_diff_ltax_wng_tbl_cnt + 1;
1855 g_diff_ltax_wng_tbl(g_diff_ltax_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(i).file_id).file_name||' ('||to_char(g_imp_data_tbl(i).line)||') : '||
1856 to_char(g_imp_data_tbl(i).i_ltax_7)||','||
1857 to_char(g_imp_data_tbl(i).i_ltax_8)||','||
1858 to_char(g_imp_data_tbl(i).i_ltax_9)||','||
1859 to_char(g_imp_data_tbl(i).i_ltax_10)||','||
1860 to_char(g_imp_data_tbl(i).i_ltax_11)||','||
1861 to_char(g_imp_data_tbl(i).i_ltax_12)||','||
1862 to_char(g_imp_data_tbl(i).i_ltax_1)||','||
1863 to_char(g_imp_data_tbl(i).i_ltax_2)||','||
1864 to_char(g_imp_data_tbl(i).i_ltax_3)||','||
1865 to_char(g_imp_data_tbl(i).i_ltax_4)||','||
1866 to_char(g_imp_data_tbl(i).i_ltax_5);
1867 --
1868 if g_valid_diff_ltax = 'Y' then
1869 --
1870 l_valid := false;
1871 --
1872 end if;
1873 --
1874 end if;
1875 --
1876 else
1877 --
1878 g_diff_ltax_wng_tbl_cnt := g_diff_ltax_wng_tbl_cnt + 1;
1879 g_diff_ltax_wng_tbl(g_diff_ltax_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(i).file_id).file_name||' ('||to_char(g_imp_data_tbl(i).line)||') : '||
1880 to_char(g_imp_data_tbl(i).i_ltax_7)||','||
1881 to_char(g_imp_data_tbl(i).i_ltax_8)||','||
1882 to_char(g_imp_data_tbl(i).i_ltax_9)||','||
1883 to_char(g_imp_data_tbl(i).i_ltax_10)||','||
1884 to_char(g_imp_data_tbl(i).i_ltax_11)||','||
1885 to_char(g_imp_data_tbl(i).i_ltax_12)||','||
1886 to_char(g_imp_data_tbl(i).i_ltax_1)||','||
1887 to_char(g_imp_data_tbl(i).i_ltax_2)||','||
1888 to_char(g_imp_data_tbl(i).i_ltax_3)||','||
1889 to_char(g_imp_data_tbl(i).i_ltax_4)||','||
1890 to_char(g_imp_data_tbl(i).i_ltax_5);
1891 --
1892 if g_valid_diff_ltax = 'Y' then
1893 --
1894 l_valid := false;
1895 --
1896 end if;
1897 --
1898 end if;
1899 --
1900 else
1901 --
1902 g_inv_data_wng_tbl_cnt := g_inv_data_wng_tbl_cnt + 1;
1903 g_inv_data_wng_tbl(g_inv_data_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(i).file_id).file_name||' ('||to_char(g_imp_data_tbl(i).line)||') : '||
1904 g_imp_data_tbl(i).i_swot_number||','||g_imp_data_tbl(i).i_employee_number||','||g_imp_data_tbl(i).i_district_code;
1905 --
1906 l_valid := false;
1907 --
1908 end if;
1909 --
1910 if l_valid
1911 and (g_show_incon_data = 'Y'
1912 or g_valid_incon_data = 'Y')
1913 -- valid only case g_file_split = Y
1914 and g_imp_file_ind_tbl.count > 0 then
1915 --
1916 if g_imp_data_tbl(i).i_district_code <> g_imp_file_ind_tbl(g_imp_data_tbl(i).file_id).district_code
1917 or g_imp_data_tbl(i).i_swot_number <> g_imp_file_ind_tbl(g_imp_data_tbl(i).file_id).swot_number then
1918 --
1919 if g_show_incon_data = 'Y' then
1920 --
1921 g_incon_data_wng_tbl_cnt := g_incon_data_wng_tbl_cnt + 1;
1922 g_incon_data_wng_tbl(g_incon_data_wng_tbl_cnt) := g_file_tbl(g_imp_data_tbl(i).file_id).file_name||' ('||to_char(g_imp_data_tbl(i).line)||') : '||
1923 g_imp_file_ind_tbl(g_imp_data_tbl(i).file_id).swot_number||','||g_imp_file_ind_tbl(g_imp_data_tbl(i).file_id).district_code||' : '||
1924 g_imp_data_tbl(i).i_swot_number||','||g_imp_data_tbl(i).i_employee_number||','||g_imp_data_tbl(i).i_district_code;
1925 --
1926 end if;
1927 --
1928 if g_valid_incon_data = 'Y' then
1929 --
1930 l_valid := false;
1931 --
1932 end if;
1933 --
1934 end if;
1935 --
1936 end if;
1937 --
1938 if l_valid then
1939 --
1940 get_ltxi_assignment(
1941 g_imp_data_tbl(i).i_swot_number,
1942 g_imp_data_tbl(i).i_employee_number,
1943 g_imp_data_tbl(i).i_district_code,
1944 l_assignment_id,
1945 l_payroll_id,
1946 l_assignment_number,
1947 l_regular_payment_date,
1948 l_final_process_date);
1949 --
1950 valid_ltxi_assignment(
1951 l_assignment_id,
1952 l_assignment_number,
1953 l_regular_payment_date,
1954 i,
1955 l_valid);
1956 --
1957 if l_valid then
1958 --
1959 g_ass_id_tbl_cnt := g_ass_id_tbl_cnt + 1;
1960 g_ass_id_tbl(g_ass_id_tbl_cnt) := l_assignment_id;
1961 --
1962 g_ass_ind_tbl(l_assignment_id).assignment_id := l_assignment_id;
1963 g_ass_ind_tbl(l_assignment_id).payroll_id := l_payroll_id;
1964 g_ass_ind_tbl(l_assignment_id).assignment_number := l_assignment_number;
1965 g_ass_ind_tbl(l_assignment_id).final_process_date := l_final_process_date;
1966 --
1967 g_ass_data_tbl(l_assignment_id).file_id := g_imp_data_tbl(i).file_id;
1968 g_ass_data_tbl(l_assignment_id).line := g_imp_data_tbl(i).line;
1969 g_ass_data_tbl(l_assignment_id).i_swot_number := g_imp_data_tbl(i).i_swot_number;
1970 g_ass_data_tbl(l_assignment_id).i_personal_number := g_imp_data_tbl(i).i_personal_number;
1971 g_ass_data_tbl(l_assignment_id).i_employee_number := g_imp_data_tbl(i).i_employee_number;
1972 g_ass_data_tbl(l_assignment_id).i_address := g_imp_data_tbl(i).i_address;
1973 g_ass_data_tbl(l_assignment_id).i_address_kana := g_imp_data_tbl(i).i_address_kana;
1974 g_ass_data_tbl(l_assignment_id).i_full_name := g_imp_data_tbl(i).i_full_name;
1975 g_ass_data_tbl(l_assignment_id).i_full_name_kana := g_imp_data_tbl(i).i_full_name_kana;
1976 g_ass_data_tbl(l_assignment_id).i_sp_ltax := g_imp_data_tbl(i).i_sp_ltax;
1977 g_ass_data_tbl(l_assignment_id).i_ltax_6 := g_imp_data_tbl(i).i_ltax_6;
1978 g_ass_data_tbl(l_assignment_id).i_ltax_7 := g_imp_data_tbl(i).i_ltax_7;
1979 g_ass_data_tbl(l_assignment_id).i_ltax_8 := g_imp_data_tbl(i).i_ltax_8;
1980 g_ass_data_tbl(l_assignment_id).i_ltax_9 := g_imp_data_tbl(i).i_ltax_9;
1981 g_ass_data_tbl(l_assignment_id).i_ltax_10 := g_imp_data_tbl(i).i_ltax_10;
1982 g_ass_data_tbl(l_assignment_id).i_ltax_11 := g_imp_data_tbl(i).i_ltax_11;
1983 g_ass_data_tbl(l_assignment_id).i_ltax_12 := g_imp_data_tbl(i).i_ltax_12;
1984 g_ass_data_tbl(l_assignment_id).i_ltax_1 := g_imp_data_tbl(i).i_ltax_1;
1985 g_ass_data_tbl(l_assignment_id).i_ltax_2 := g_imp_data_tbl(i).i_ltax_2;
1986 g_ass_data_tbl(l_assignment_id).i_ltax_3 := g_imp_data_tbl(i).i_ltax_3;
1987 g_ass_data_tbl(l_assignment_id).i_ltax_4 := g_imp_data_tbl(i).i_ltax_4;
1988 g_ass_data_tbl(l_assignment_id).i_ltax_5 := g_imp_data_tbl(i).i_ltax_5;
1989 g_ass_data_tbl(l_assignment_id).i_district_code := g_imp_data_tbl(i).i_district_code;
1990 g_ass_data_tbl(l_assignment_id).assignment_id := l_assignment_id;
1991 g_ass_data_tbl(l_assignment_id).assignment_number := l_assignment_number;
1992 --
1993 end if;
1994 --
1995 end if;
1996 --
1997 end loop loop_data_tbl;
1998 --
1999 end if;
2000 --
2001 if g_debug
2002 and g_detail_debug = 'Y' then
2003 hr_utility.trace('g_ass_id_tbl.count : '||to_char(g_ass_id_tbl.count));
2004 hr_utility.trace('g_ass_ind_tbl.count : '||to_char(g_ass_ind_tbl.count));
2005 hr_utility.trace('g_ass_data_tbl.count : '||to_char(g_ass_data_tbl.count));
2006 hr_utility.trace('g_valid_diff_ltax : '||g_valid_diff_ltax);
2007 hr_utility.trace('g_diff_ltax_wng_tbl.count : '||to_char(g_diff_ltax_wng_tbl.count));
2008 hr_utility.trace('g_inv_data_wng_tbl.count : '||to_char(g_inv_data_wng_tbl.count));
2009 hr_utility.trace('g_valid_incon_data : '||g_valid_incon_data);
2010 hr_utility.trace('g_show_incon_data : '||g_show_incon_data);
2011 hr_utility.trace('g_incon_data_wng_tbl.count : '||to_char(g_incon_data_wng_tbl.count));
2012 hr_utility.trace('g_no_ass_wng_tbl.count : '||to_char(g_no_ass_wng_tbl.count));
2013 hr_utility.trace('g_valid_non_res : '||g_valid_non_res);
2014 hr_utility.trace('g_non_res_wng_tbl.count : '||to_char(g_non_res_wng_tbl.count));
2015 hr_utility.trace('g_valid_dup_ass : '||g_valid_dup_ass);
2016 hr_utility.trace('g_dup_ass_wng_tbl.count : '||to_char(g_dup_ass_wng_tbl.count));
2017 hr_utility.set_location(l_proc,1000);
2018 end if;
2019 --
2020 end valid_file_data;
2021 --
2022 -- -------------------------------------------------------------------------
2023 -- assignment_process
2024 -- -------------------------------------------------------------------------
2025 procedure assignment_process(
2026 p_assignment_id in number,
2027 p_batch_id in number)
2028 is
2029 --
2030 l_proc varchar2(80) := c_package||'assignment_process';
2031 --
2032 l_payroll_id number;
2033 l_final_process_date date;
2034 l_payment_date date;
2035 l_upload_date date;
2036 l_each_mth_cnt number;
2037 --
2038 l_ltax_start_mth varchar2(6);
2039 l_ltax_end_mth varchar2(6);
2040 l_ltax_ini number;
2041 l_ltax_2nd number;
2042 --
2043 l_com_ltx_info_ee_rec pay_jp_bee_utility_pkg.t_ee_rec;
2044 l_com_ltx_info_eev_rec pay_jp_bee_utility_pkg.t_eev_rec;
2045 l_new_value_tbl pay_jp_bee_utility_pkg.t_varchar2_tbl;
2046 --
2047 l_upd_eev boolean;
2048 l_is_different boolean;
2049 l_change_type hr_lookups.lookup_code%type;
2050 l_write_all boolean;
2051 l_batch_line_id number;
2052 l_batch_line_ovn number;
2053 --
2054 cursor csr_each_mth(
2055 p_payroll_id in number)
2056 is
2057 select ptp.regular_payment_date payment_date,
2058 ptp.start_date upload_date
2059 from per_time_periods ptp
2060 where ptp.payroll_id = p_payroll_id
2061 and ptp.period_type = 'Calendar Month'
2062 and ptp.regular_payment_date >= g_effective_som
2063 and ptp.regular_payment_date < add_months(g_effective_som,12)
2064 order by ptp.start_date;
2065 --
2066 begin
2067 --
2068 if g_debug
2069 and g_detail_debug = 'Y' then
2070 hr_utility.set_location(l_proc,0);
2071 hr_utility.trace('p_assignment_id : '||to_char(p_assignment_id));
2072 hr_utility.trace('p_batch_id : '||to_char(p_batch_id));
2073 hr_utility.trace('g_create_entry_if_not_exist : '||g_create_entry_if_not_exist);
2074 hr_utility.trace('g_mth_tbl.count : '||to_char(g_mth_tbl.count));
2075 hr_utility.trace('g_ass_ind_tbl.count : '||to_char(g_ass_ind_tbl.count));
2076 hr_utility.trace('g_datetrack_eev : '||g_datetrack_eev);
2077 hr_utility.trace('g_valid_term_flag : '||g_valid_term_flag);
2078 end if;
2079 --
2080 begin
2081 --
2082 l_payroll_id := g_mth_tbl(g_ass_ind_tbl(p_assignment_id).payroll_id).payroll_id;
2083 --
2084 exception
2085 when no_data_found then
2086 --
2087 l_each_mth_cnt := 0;
2088 l_payroll_id := g_ass_ind_tbl(p_assignment_id).payroll_id;
2089 --
2090 open csr_each_mth(l_payroll_id);
2091 <<each_mth_loop>>
2092 loop
2093 --
2094 fetch csr_each_mth into l_payment_date, l_upload_date;
2095 exit when csr_each_mth%notfound;
2096 --
2097 l_each_mth_cnt := l_each_mth_cnt + 1;
2098 --
2099 g_mth_tbl(l_payroll_id).mth_cnt := l_each_mth_cnt;
2100 --
2101 if l_each_mth_cnt = 1 then
2102 g_mth_tbl(l_payroll_id).payroll_id := l_payroll_id;
2103 g_mth_tbl(l_payroll_id).payment_date_1 := l_payment_date;
2104 g_mth_tbl(l_payroll_id).upload_date_1 := l_upload_date;
2105 elsif l_each_mth_cnt = 2 then
2106 g_mth_tbl(l_payroll_id).payment_date_2 := l_payment_date;
2107 g_mth_tbl(l_payroll_id).upload_date_2 := l_upload_date;
2108 elsif l_each_mth_cnt = 3 then
2109 g_mth_tbl(l_payroll_id).payment_date_3 := l_payment_date;
2110 g_mth_tbl(l_payroll_id).upload_date_3 := l_upload_date;
2111 elsif l_each_mth_cnt = 4 then
2112 g_mth_tbl(l_payroll_id).payment_date_4 := l_payment_date;
2113 g_mth_tbl(l_payroll_id).upload_date_4 := l_upload_date;
2114 elsif l_each_mth_cnt = 5 then
2115 g_mth_tbl(l_payroll_id).payment_date_5 := l_payment_date;
2116 g_mth_tbl(l_payroll_id).upload_date_5 := l_upload_date;
2117 elsif l_each_mth_cnt = 6 then
2118 g_mth_tbl(l_payroll_id).payment_date_6 := l_payment_date;
2119 g_mth_tbl(l_payroll_id).upload_date_6 := l_upload_date;
2120 elsif l_each_mth_cnt = 7 then
2121 g_mth_tbl(l_payroll_id).payment_date_7 := l_payment_date;
2122 g_mth_tbl(l_payroll_id).upload_date_7 := l_upload_date;
2123 elsif l_each_mth_cnt = 8 then
2124 g_mth_tbl(l_payroll_id).payment_date_8 := l_payment_date;
2125 g_mth_tbl(l_payroll_id).upload_date_8 := l_upload_date;
2126 elsif l_each_mth_cnt = 9 then
2127 g_mth_tbl(l_payroll_id).payment_date_9 := l_payment_date;
2128 g_mth_tbl(l_payroll_id).upload_date_9 := l_upload_date;
2129 elsif l_each_mth_cnt = 10 then
2130 g_mth_tbl(l_payroll_id).payment_date_10 := l_payment_date;
2131 g_mth_tbl(l_payroll_id).upload_date_10 := l_upload_date;
2132 elsif l_each_mth_cnt = 11 then
2133 g_mth_tbl(l_payroll_id).payment_date_11 := l_payment_date;
2134 g_mth_tbl(l_payroll_id).upload_date_11 := l_upload_date;
2135 elsif l_each_mth_cnt = 12 then
2136 g_mth_tbl(l_payroll_id).payment_date_12 := l_payment_date;
2137 g_mth_tbl(l_payroll_id).upload_date_12 := l_upload_date;
2138 end if;
2139 --
2140 if g_datetrack_eev is null
2141 or g_datetrack_eev <> 'Y' then
2142 --
2143 exit each_mth_loop;
2144 --
2145 end if;
2146 --
2147 end loop each_mth_loop;
2148 close csr_each_mth;
2149 --
2150 end;
2151 --
2152 if g_debug
2153 and g_detail_debug = 'Y' then
2154 hr_utility.set_location(l_proc,10);
2155 hr_utility.trace('l_payroll_id : '||to_char(l_payroll_id));
2156 hr_utility.trace('g_payroll_id : '||to_char(g_payroll_id));
2157 hr_utility.trace('g_mth_tbl.count : '||to_char(g_mth_tbl.count));
2158 end if;
2159 --
2160 if g_mth_tbl.count > 0 then
2161 --
2162 if g_payroll_id is null
2163 or g_payroll_id <> l_payroll_id then
2164 --
2165 g_ee_tbl.delete;
2166 g_payroll_id := l_payroll_id;
2167 --
2168 if g_datetrack_eev = 'Y'
2169 and g_mth_tbl(l_payroll_id).mth_cnt = 12 then
2170 --
2171 set_ee_tbl(1,g_mth_tbl(l_payroll_id).payment_date_1,g_mth_tbl(l_payroll_id).upload_date_1);
2172 set_ee_tbl(2,g_mth_tbl(l_payroll_id).payment_date_2,g_mth_tbl(l_payroll_id).upload_date_2);
2173 set_ee_tbl(3,g_mth_tbl(l_payroll_id).payment_date_3,g_mth_tbl(l_payroll_id).upload_date_3);
2174 set_ee_tbl(4,g_mth_tbl(l_payroll_id).payment_date_4,g_mth_tbl(l_payroll_id).upload_date_4);
2175 set_ee_tbl(5,g_mth_tbl(l_payroll_id).payment_date_5,g_mth_tbl(l_payroll_id).upload_date_5);
2176 set_ee_tbl(6,g_mth_tbl(l_payroll_id).payment_date_6,g_mth_tbl(l_payroll_id).upload_date_6);
2177 set_ee_tbl(7,g_mth_tbl(l_payroll_id).payment_date_7,g_mth_tbl(l_payroll_id).upload_date_7);
2178 set_ee_tbl(8,g_mth_tbl(l_payroll_id).payment_date_8,g_mth_tbl(l_payroll_id).upload_date_8);
2179 set_ee_tbl(9,g_mth_tbl(l_payroll_id).payment_date_9,g_mth_tbl(l_payroll_id).upload_date_9);
2180 set_ee_tbl(10,g_mth_tbl(l_payroll_id).payment_date_10,g_mth_tbl(l_payroll_id).upload_date_10);
2181 set_ee_tbl(11,g_mth_tbl(l_payroll_id).payment_date_11,g_mth_tbl(l_payroll_id).upload_date_11);
2182 set_ee_tbl(12,g_mth_tbl(l_payroll_id).payment_date_12,g_mth_tbl(l_payroll_id).upload_date_12);
2183 --
2184 else
2185 --
2186 set_ee_tbl(1,g_mth_tbl(l_payroll_id).payment_date_1,g_mth_tbl(l_payroll_id).upload_date_1);
2187 --
2188 end if;
2189 --
2190 end if;
2191 --
2192 else
2193 --
2194 hr_utility.trace('l_mth_tbl.count is 0');
2195 --
2196 end if;
2197 --
2198 if g_debug
2199 and g_detail_debug = 'Y' then
2200 hr_utility.set_location(l_proc,20);
2201 hr_utility.trace('g_ee_tbl.count : '||to_char(g_ee_tbl.count));
2202 end if;
2203 --
2204 if g_ee_tbl.count > 0 then
2205 --
2206 l_upd_eev := true;
2207 --
2208 for i in 1..g_ee_tbl.count loop
2209 --
2210 l_new_value_tbl.delete;
2211 l_com_ltx_info_ee_rec := null;
2212 l_com_ltx_info_eev_rec := null;
2213 --
2214 l_write_all := false;
2215 l_change_type := null;
2216 l_is_different := false;
2217 l_batch_line_id := null;
2218 l_batch_line_ovn := null;
2219 --
2220 l_final_process_date := g_ass_ind_tbl(p_assignment_id).final_process_date;
2221 --
2222 if g_debug
2223 and g_detail_debug = 'Y' then
2224 hr_utility.set_location(l_proc,30);
2225 hr_utility.trace('l_final_process_date : '||to_char(l_final_process_date,'YYYY/MM/DD'));
2226 hr_utility.trace('g_ee_tbl('||to_char(i)||').upload_date : '||to_char(g_ee_tbl(i).upload_date,'YYYY/MM/DD'));
2227 end if;
2228 --
2229 if g_valid_term_flag is null
2230 or g_valid_term_flag= 'N'
2231 or (g_valid_term_flag = 'Y'
2232 and (l_final_process_date is null
2233 or (l_final_process_date is not null
2234 and g_ee_tbl(i).upload_date <= l_final_process_date))) then
2235 --
2236 l_ltax_start_mth := to_char(g_ee_tbl(i).period_year)||lpad(to_char(g_ee_tbl(i).period_num),2,'0');
2237 --
2238 pay_jp_bee_utility_pkg.get_ee(
2239 p_assignment_id,
2240 c_com_ltx_info_elm_id,
2241 g_ee_tbl(i).upload_date,
2242 l_com_ltx_info_ee_rec,
2243 l_com_ltx_info_eev_rec);
2244 --
2245 if g_debug
2246 and g_detail_debug = 'Y' then
2247 hr_utility.set_location(l_proc,40);
2248 hr_utility.trace('l_ltax_start_mth : '||l_ltax_start_mth);
2249 hr_utility.trace('l_com_ltx_info_ee_rec.element_entry_id : '||to_char(l_com_ltx_info_ee_rec.element_entry_id));
2250 end if;
2251 --
2252 if l_com_ltx_info_ee_rec.element_entry_id is not null
2253 and l_com_ltx_info_eev_rec.entry_value_tbl.count > 0 then
2254 --
2255 if g_show_upd_eev = 'Y' then
2256 --
2257 g_upd_eev_wng_tbl_cnt := g_upd_eev_wng_tbl_cnt + 1;
2258 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt)
2259 := g_file_tbl(g_ass_data_tbl(p_assignment_id).file_id).file_name||
2260 ' ('||to_char(g_ass_data_tbl(p_assignment_id).line)||') : '||
2261 g_ass_ind_tbl(p_assignment_id).assignment_number||' ('||
2262 to_char(p_assignment_id)||') : '||
2263 fnd_date.date_to_canonical(g_ee_tbl(i).upload_date)||' : '||
2264 fnd_date.date_to_canonical(l_com_ltx_info_ee_rec.effective_start_date)||'-'||
2265 fnd_date.date_to_canonical(l_com_ltx_info_ee_rec.effective_end_date);
2266 --
2267 if g_detail_eev = 'Y' then
2268 --
2269 for j in 1..l_com_ltx_info_eev_rec.entry_value_tbl.count loop
2270 --
2271 if j = 1 then
2272 --
2273 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt) :=
2274 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt)||
2275 ' : ';
2276 --
2277 end if;
2278 --
2279 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt) :=
2280 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt)||
2281 l_com_ltx_info_eev_rec.entry_value_tbl(j);
2282 --
2283 if j <> l_com_ltx_info_eev_rec.entry_value_tbl.count then
2284 --
2285 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt) :=
2286 g_upd_eev_wng_tbl(g_upd_eev_wng_tbl_cnt)||
2287 ',';
2288 --
2289 end if;
2290 --
2291 end loop;
2292 --
2293 end if;
2294 --
2295 end if;
2296 --
2297 if i = 1
2298 and (g_valid_no_upd = 'Y'
2299 or g_show_no_upd = 'Y')
2300 and l_com_ltx_info_eev_rec.entry_value_tbl(3) is not null
2301 and l_com_ltx_info_eev_rec.entry_value_tbl(3) >= l_ltax_start_mth then
2302 --
2303 if g_show_no_upd = 'Y' then
2304 --
2305 g_no_upd_wng_tbl_cnt := g_no_upd_wng_tbl_cnt + 1;
2306 g_no_upd_wng_tbl(g_no_upd_wng_tbl_cnt)
2307 := g_file_tbl(g_ass_data_tbl(p_assignment_id).file_id).file_name||
2308 ' ('||to_char(g_ass_data_tbl(p_assignment_id).line)||') : '||
2309 g_ass_ind_tbl(p_assignment_id).assignment_number||' ('||
2310 to_char(p_assignment_id)||') : '||
2311 l_com_ltx_info_eev_rec.entry_value_tbl(3)||'-'||
2312 l_ltax_start_mth;
2313 --
2314 end if;
2315 --
2316 if g_valid_no_upd = 'Y' then
2317 --
2318 l_upd_eev := false;
2319 --
2320 end if;
2321 --
2322 end if;
2323 --
2324 if i = 1
2325 and l_com_ltx_info_eev_rec.entry_value_tbl(1) is not null
2326 and l_com_ltx_info_eev_rec.entry_value_tbl(1) ='N' then
2327 --
2328 g_sp_with_wng_tbl_cnt := g_sp_with_wng_tbl_cnt + 1;
2329 g_sp_with_wng_tbl(g_sp_with_wng_tbl_cnt)
2330 := g_file_tbl(g_ass_data_tbl(p_assignment_id).file_id).file_name||
2331 ' ('||to_char(g_ass_data_tbl(p_assignment_id).line)||') : '||
2332 g_ass_ind_tbl(p_assignment_id).assignment_number||' ('||
2333 to_char(p_assignment_id)||') : '||
2334 l_com_ltx_info_eev_rec.entry_value_tbl(1);
2335 --
2336 if g_valid_sp_with = 'Y' then
2337 --
2338 l_upd_eev := false;
2339 --
2340 end if;
2341 --
2342 end if;
2343 --
2344 end if;
2345 --
2346 if (l_upd_eev
2347 and (l_com_ltx_info_ee_rec.element_entry_id is not null
2348 or g_create_entry_if_not_exist = 'Y')) then
2349 --
2350 l_ltax_end_mth := l_ltax_start_mth;
2351 --
2352 if g_ee_tbl(i).period_num = 1 then
2353 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_1;
2354 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_2;
2355 elsif g_ee_tbl(i).period_num = 2 then
2356 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_2;
2357 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_3;
2358 elsif g_ee_tbl(i).period_num = 3 then
2359 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_3;
2360 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_4;
2361 elsif g_ee_tbl(i).period_num = 4 then
2362 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_4;
2363 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_5;
2364 elsif g_ee_tbl(i).period_num = 5 then
2365 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_5;
2366 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_6;
2367 elsif g_ee_tbl(i).period_num = 6 then
2368 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_6;
2369 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_7;
2370 elsif g_ee_tbl(i).period_num = 7 then
2371 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_7;
2372 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_8;
2373 elsif g_ee_tbl(i).period_num = 8 then
2374 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_8;
2375 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_9;
2376 elsif g_ee_tbl(i).period_num = 9 then
2377 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_9;
2378 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_10;
2379 elsif g_ee_tbl(i).period_num = 10 then
2380 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_10;
2381 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_11;
2382 elsif g_ee_tbl(i).period_num = 11 then
2383 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_11;
2384 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_12;
2385 elsif g_ee_tbl(i).period_num = 12 then
2386 l_ltax_ini := g_ass_data_tbl(p_assignment_id).i_ltax_12;
2387 l_ltax_2nd := g_ass_data_tbl(p_assignment_id).i_ltax_1;
2388 end if;
2389 --
2390 if i = 12 then
2391 --
2392 l_ltax_2nd := null;
2393 --
2394 end if;
2395 --
2396 if g_ee_tbl.count = 1 then
2397 --
2398 l_ltax_end_mth := to_char(add_months(to_date(l_ltax_start_mth||'01','YYYYMMDD'),12)-1,'YYYYMM');
2399 --
2400 end if;
2401 --
2402 l_new_value_tbl(1) := 'Y';
2403 l_new_value_tbl(2) := g_ass_data_tbl(p_assignment_id).i_district_code;
2404 l_new_value_tbl(3) := l_ltax_start_mth;
2405 l_new_value_tbl(4) := l_ltax_end_mth;
2406 l_new_value_tbl(5) := to_char(l_ltax_ini);
2407 l_new_value_tbl(6) := to_char(l_ltax_2nd);
2408 l_new_value_tbl(7) := g_ass_data_tbl(p_assignment_id).i_personal_number;
2409 --
2410 if g_debug
2411 and g_detail_debug = 'Y' then
2412 --
2413 hr_utility.set_location(l_proc,50);
2414 --
2415 for j in 1..7 loop
2416 --
2417 hr_utility.trace('l_new_value_tbl('||to_char(j)||') : '||l_new_value_tbl(j));
2418 --
2419 end loop;
2420 --
2421 end if;
2422 --
2423 pay_jp_bee_utility_pkg.set_eev(
2424 p_ee_rec => l_com_ltx_info_ee_rec,
2425 p_eev_rec => l_com_ltx_info_eev_rec,
2426 p_value_if_null_tbl => c_value_if_null_tbl,
2427 p_new_value_tbl => l_new_value_tbl,
2428 p_is_different => l_is_different);
2429 --
2430 if l_is_different then
2431 --
2432 if l_com_ltx_info_ee_rec.element_entry_id is null then
2433 --
2434 l_change_type := 'I';
2435 l_write_all := true;
2436 --
2437 else
2438 --
2439 if l_com_ltx_info_ee_rec.effective_start_date = g_ee_tbl(i).upload_date then
2440 l_change_type := 'C';
2441 else
2442 l_change_type := 'U';
2443 end if;
2444 --
2445 end if;
2446 --
2447 if g_debug
2448 and g_detail_debug = 'Y' then
2449 hr_utility.set_location(l_proc,60);
2450 hr_utility.trace('l_change_type : '||l_change_type);
2451 end if;
2452 --
2453 pay_jp_bee_utility_pkg.out(
2454 p_full_name => g_ass_data_tbl(p_assignment_id).i_full_name,
2455 p_assignment_number => g_ass_data_tbl(p_assignment_id).assignment_number,
2456 p_effective_date => g_ee_tbl(i).upload_date,
2457 p_change_type => l_change_type,
2458 p_eev_rec => l_com_ltx_info_eev_rec,
2459 p_new_value_tbl => l_new_value_tbl,
2460 p_write_all => l_write_all);
2461 --
2462 l_com_ltx_info_eev_rec.entry_value_tbl := l_new_value_tbl;
2463 --
2464 pay_jp_bee_utility_pkg.create_batch_line(
2465 p_batch_id => p_batch_id,
2466 p_assignment_id => p_assignment_id,
2467 p_assignment_number => g_ass_data_tbl(p_assignment_id).assignment_number,
2468 p_element_type_id => c_com_ltx_info_elm_id,
2469 p_element_name => c_com_ltx_info_elm,
2470 p_effective_date => g_ee_tbl(i).upload_date,
2471 p_ee_rec => l_com_ltx_info_ee_rec,
2472 p_eev_rec => l_com_ltx_info_eev_rec,
2473 p_batch_line_id => l_batch_line_id,
2474 p_object_version_number => l_batch_line_ovn);
2475 --
2476 commit;
2477 --
2478 if g_debug
2479 and g_detail_debug = 'Y' then
2480 hr_utility.set_location(l_proc,70);
2481 hr_utility.trace('l_batch_line_id : '||to_char(l_batch_line_id));
2482 hr_utility.trace('l_batch_line_ovn : '||to_char(l_batch_line_ovn));
2483 end if;
2484 --
2485 end if;
2486 --
2487 end if;
2488 --
2489 end if;
2490 --
2491 end loop;
2492 --
2493 else
2494 --
2495 hr_utility.trace('g_ee_tbl.count is 0');
2496 --
2497 end if;
2498 --
2499 if g_debug
2500 and g_detail_debug = 'Y' then
2501 hr_utility.trace('g_show_upd_eev : '||g_show_upd_eev);
2502 hr_utility.trace('g_upd_eev_wng_tbl.count : '||to_char(g_upd_eev_wng_tbl.count));
2503 hr_utility.trace('g_valid_no_upd : '||g_valid_no_upd);
2504 hr_utility.trace('g_show_no_upd : '||g_show_no_upd);
2505 hr_utility.trace('g_no_upd_wng_tbl.count : '||to_char(g_no_upd_wng_tbl.count));
2506 hr_utility.trace('g_valid_sp_with : '||g_valid_sp_with);
2507 hr_utility.trace('g_sp_with_wng_tbl.count : '||to_char(g_sp_with_wng_tbl.count));
2508 hr_utility.set_location(l_proc,1000);
2509 end if;
2510 --
2511 exception
2512 when others then
2513 --
2514 if g_debug
2515 and g_detail_debug = 'Y' then
2516 hr_utility.set_location(l_proc,-1000);
2517 end if;
2518 --
2519 g_inv_ass_wng_tbl_cnt := g_inv_ass_wng_tbl_cnt + 1;
2520 g_inv_ass_wng_tbl(g_inv_ass_wng_tbl_cnt)
2521 := g_file_tbl(g_ass_data_tbl(p_assignment_id).file_id).file_name||
2522 ' ('||to_char(g_ass_data_tbl(p_assignment_id).line)||') : '||
2523 g_ass_data_tbl(p_assignment_id).i_swot_number||','||
2524 g_ass_data_tbl(p_assignment_id).i_employee_number||','||
2525 g_ass_data_tbl(p_assignment_id).i_district_code||
2526 ' : '||g_ass_ind_tbl(p_assignment_id).assignment_number||
2527 ' ('||to_char(p_assignment_id)||') : '||
2528 to_char(sqlcode)||':'||substrb(sqlerrm,1,100);
2529 --
2530 if g_debug
2531 and g_detail_debug = 'Y' then
2532 hr_utility.set_location(l_proc,-1100);
2533 hr_utility.trace('g_inv_ass_wng_tbl.count : '||to_char(g_inv_ass_wng_tbl.count));
2534 hr_utility.trace('g_ass_ind_tbl.count : '||to_char(g_ass_ind_tbl.count));
2535 end if;
2536 --
2537 if g_ass_ind_tbl.count > 0 then
2538 --
2539 create_asg_set_amd(
2540 p_business_group_id => g_business_group_id,
2541 p_payroll_id => g_ass_ind_tbl(p_assignment_id).payroll_id,
2542 p_assignment_id => p_assignment_id,
2543 p_assignment_set_id => g_err_ass_set_id,
2544 p_assignment_set_name => g_err_ass_set_name);
2545 --
2546 end if;
2547 --
2548 end assignment_process;
2549 --
2550 -- -------------------------------------------------------------------------
2551 -- transfer_imp_ltax_info_to_bee
2552 -- -------------------------------------------------------------------------
2553 procedure transfer_imp_ltax_info_to_bee(
2554 p_errbuf out nocopy varchar2,
2555 p_retcode out nocopy varchar2,
2556 p_business_group_id in number,
2557 p_subject_yyyymm in varchar2,
2558 p_upload_date in varchar2,
2559 p_batch_name in varchar2,
2560 p_action_if_exists in varchar2,
2561 p_reject_if_future_changes in varchar2,
2562 p_date_effective_changes in varchar2,
2563 p_purge_after_transfer in varchar2,
2564 p_create_entry_if_not_exist in varchar2,
2565 p_create_asg_set_for_errored in varchar2,
2566 p_organization_id in number,
2567 p_district_code in varchar2,
2568 p_assignment_set_id in number,
2569 p_file_suffix in varchar2,
2570 p_file_split in varchar2,
2571 p_datetrack_eev in varchar2,
2572 p_valid_diff_ltax in varchar2,
2573 p_valid_incon_data in varchar2,
2574 p_valid_non_res in varchar2,
2575 p_valid_dup_ass in varchar2,
2576 p_valid_no_upd in varchar2,
2577 p_valid_sp_with in varchar2,
2578 p_show_dup_file in varchar2 default 'N',
2579 p_show_no_file in varchar2 default 'N',
2580 p_show_incon_data in varchar2 default 'N',
2581 p_show_upd_eev in varchar2 default 'N',
2582 p_show_no_upd in varchar2 default 'N')
2583 is
2584 --
2585 l_proc varchar2(80) := c_package||'transfer_imp_ltax_info_to_bee';
2586 --
2587 l_upload_date date;
2588 --
2589 l_batch_reference pay_batch_headers.batch_reference%type;
2590 l_batch_source pay_batch_headers.batch_source%type;
2591 l_date_effective_changes pay_batch_headers.date_effective_changes%type;
2592 l_batch_id number;
2593 l_batch_ovn number;
2594 --
2595 begin
2596 --
2597 if g_debug then
2598 hr_utility.set_location(l_proc,0);
2599 end if;
2600 --
2601 pay_jp_bee_utility_pkg.g_num_of_outs := 0;
2602 --
2603 l_upload_date := fnd_date.canonical_to_date(p_upload_date);
2604 l_date_effective_changes := p_date_effective_changes;
2605 --
2606 if g_debug then
2607 hr_utility.set_location(l_proc,10);
2608 hr_utility.trace('pay_jp_bee_utility_pkg.g_num_of_outs : '||to_char(pay_jp_bee_utility_pkg.g_num_of_outs));
2609 hr_utility.trace('l_upload_date : '||to_char(l_upload_date,'YYYY/MM/DD'));
2610 end if;
2611 --
2612 init(
2613 p_business_group_id,
2614 p_subject_yyyymm,
2615 l_upload_date,
2616 p_organization_id,
2617 p_district_code,
2618 p_assignment_set_id,
2619 p_file_suffix,
2620 p_file_split,
2621 p_datetrack_eev,
2622 p_show_dup_file,
2623 p_show_no_file,
2624 p_valid_diff_ltax,
2625 p_valid_incon_data,
2626 p_show_incon_data,
2627 p_valid_non_res,
2628 p_valid_dup_ass,
2629 p_show_upd_eev,
2630 p_valid_no_upd,
2631 p_show_no_upd,
2632 p_valid_sp_with,
2633 p_action_if_exists,
2634 p_reject_if_future_changes,
2635 p_create_entry_if_not_exist,
2636 p_create_asg_set_for_errored);
2637 --
2638 if g_debug then
2639 hr_utility.set_location(l_proc,20);
2640 end if;
2641 --
2642 pay_jp_bee_utility_pkg.chk_date_effective_changes(
2643 g_action_if_exists,
2644 g_reject_if_future_changes,
2645 l_date_effective_changes);
2646 --
2647 if g_debug then
2648 hr_utility.set_location(l_proc,30);
2649 end if;
2650 --
2651 insert_session(g_session_date);
2652 --
2653 if g_debug then
2654 hr_utility.set_location(l_proc,40);
2655 end if;
2656 --
2657 pay_batch_element_entry_api.create_batch_header(
2658 p_validate => false,
2659 p_session_date => g_session_date,
2660 p_batch_name => substrb(p_batch_name,1,30),
2661 p_business_group_id => g_business_group_id,
2662 p_action_if_exists => g_action_if_exists,
2663 p_batch_reference => l_batch_reference,
2664 p_batch_source => l_batch_source,
2665 p_date_effective_changes => l_date_effective_changes,
2666 p_purge_after_transfer => p_purge_after_transfer,
2667 p_reject_if_future_changes => g_reject_if_future_changes,
2668 p_batch_id => l_batch_id,
2669 p_object_version_number => l_batch_ovn);
2670 --
2671 if g_debug then
2672 hr_utility.set_location(l_proc,50);
2673 end if;
2674 --
2675 imp_file_data;
2676 --
2677 if g_debug then
2678 hr_utility.set_location(l_proc,60);
2679 end if;
2680 --
2681 valid_file_data;
2682 --
2683 if g_debug then
2684 hr_utility.set_location(l_proc,70);
2685 end if;
2686 --
2687 if g_ass_id_tbl.count > 0 then
2688 --
2689 for i in 1..g_ass_id_tbl.count loop
2690 --
2691 assignment_process(
2692 g_ass_id_tbl(i),
2693 l_batch_id);
2694 --
2695 end loop;
2696 --
2697 end if;
2698 --
2699 if g_debug then
2700 hr_utility.set_location(l_proc,80);
2701 end if;
2702 --
2703 delete_session;
2704 --
2705 if g_debug then
2706 hr_utility.set_location(l_proc,90);
2707 end if;
2708 --
2709 if g_dup_file_wng_tbl_cnt > 0 then
2710 --
2711 fnd_file.put_line(fnd_file.log,' ');
2712 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2713 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_DUP_FILE'));
2714 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2715 --
2716 for i in 1..g_dup_file_wng_tbl_cnt loop
2717 --
2718 fnd_file.put_line(fnd_file.log,g_dup_file_wng_tbl(i));
2719 --
2720 end loop;
2721 --
2722 end if;
2723 --
2724 if g_debug then
2725 hr_utility.set_location(l_proc,100);
2726 end if;
2727 --
2728 if g_no_file_wng_tbl_cnt > 0 then
2729 --
2730 fnd_file.put_line(fnd_file.log,' ');
2731 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2732 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_NO_FILE'));
2733 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2734 --
2735 for i in 1..g_no_file_wng_tbl_cnt loop
2736 --
2737 fnd_file.put_line(fnd_file.log,g_no_file_wng_tbl(i));
2738 --
2739 end loop;
2740 --
2741 end if;
2742 --
2743 if g_debug then
2744 hr_utility.set_location(l_proc,110);
2745 end if;
2746 --
2747 if g_diff_ltax_wng_tbl_cnt > 0 then
2748 --
2749 fnd_file.put_line(fnd_file.log,' ');
2750 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2751 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_DIFF_LTAX'));
2752 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2753 --
2754 for i in 1..g_diff_ltax_wng_tbl_cnt loop
2755 --
2756 fnd_file.put_line(fnd_file.log,g_diff_ltax_wng_tbl(i));
2757 --
2758 end loop;
2759 --
2760 end if;
2761 --
2762 if g_debug then
2763 hr_utility.set_location(l_proc,120);
2764 end if;
2765 --
2766 if g_inv_data_wng_tbl_cnt > 0 then
2767 --
2768 fnd_file.put_line(fnd_file.log,' ');
2769 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2770 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_INV_DATA'));
2771 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2772 --
2773 for i in 1..g_inv_data_wng_tbl_cnt loop
2774 --
2775 fnd_file.put_line(fnd_file.log,g_inv_data_wng_tbl(i));
2776 --
2777 end loop;
2778 --
2779 end if;
2780 --
2781 if g_debug then
2782 hr_utility.set_location(l_proc,130);
2783 end if;
2784 --
2785 if g_incon_data_wng_tbl_cnt > 0 then
2786 --
2787 fnd_file.put_line(fnd_file.log,' ');
2788 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2789 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_INCON_DATA'));
2790 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2791 --
2792 for i in 1..g_incon_data_wng_tbl_cnt loop
2793 --
2794 fnd_file.put_line(fnd_file.log,g_incon_data_wng_tbl(i));
2795 --
2796 end loop;
2797 --
2798 end if;
2799 --
2800 if g_debug then
2801 hr_utility.set_location(l_proc,140);
2802 end if;
2803 --
2804 if g_no_ass_wng_tbl_cnt > 0 then
2805 --
2806 fnd_file.put_line(fnd_file.log,' ');
2807 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2808 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_NO_ASS'));
2809 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2810 --
2811 for i in 1..g_no_ass_wng_tbl_cnt loop
2812 --
2813 fnd_file.put_line(fnd_file.log,g_no_ass_wng_tbl(i));
2814 --
2815 end loop;
2816 --
2817 end if;
2818 --
2819 if g_debug then
2820 hr_utility.set_location(l_proc,150);
2821 end if;
2822 --
2823 if g_non_res_wng_tbl_cnt > 0 then
2824 --
2825 fnd_file.put_line(fnd_file.log,' ');
2826 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2827 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_NON_RES'));
2828 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2829 --
2830 for i in 1..g_non_res_wng_tbl_cnt loop
2831 --
2832 fnd_file.put_line(fnd_file.log,g_non_res_wng_tbl(i));
2833 --
2834 end loop;
2835 --
2836 end if;
2837 --
2838 if g_debug then
2839 hr_utility.set_location(l_proc,160);
2840 end if;
2841 --
2842 if g_dup_ass_wng_tbl_cnt > 0 then
2843 --
2844 fnd_file.put_line(fnd_file.log,' ');
2845 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2846 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_DUP_ASS'));
2847 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2848 --
2849 for i in 1..g_dup_ass_wng_tbl_cnt loop
2850 --
2851 fnd_file.put_line(fnd_file.log,g_dup_ass_wng_tbl(i));
2852 --
2853 end loop;
2854 --
2855 end if;
2856 --
2857 if g_debug then
2858 hr_utility.set_location(l_proc,170);
2859 end if;
2860 --
2861 if g_upd_eev_wng_tbl_cnt > 0 then
2862 --
2863 fnd_file.put_line(fnd_file.log,' ');
2864 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2865 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_UPD_EEV'));
2866 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2867 --
2868 for i in 1..g_upd_eev_wng_tbl_cnt loop
2869 --
2870 fnd_file.put_line(fnd_file.log,g_upd_eev_wng_tbl(i));
2871 --
2872 end loop;
2873 --
2874 end if;
2875 --
2876 if g_debug then
2877 hr_utility.set_location(l_proc,180);
2878 end if;
2879 --
2880 if g_no_upd_wng_tbl_cnt > 0 then
2881 --
2882 fnd_file.put_line(fnd_file.log,' ');
2883 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2884 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_NO_UPD'));
2885 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2886 --
2887 for i in 1..g_no_upd_wng_tbl_cnt loop
2888 --
2889 fnd_file.put_line(fnd_file.log,g_no_upd_wng_tbl(i));
2890 --
2891 end loop;
2892 --
2893 end if;
2894 --
2895 if g_debug then
2896 hr_utility.set_location(l_proc,190);
2897 end if;
2898 --
2899 if g_sp_with_wng_tbl_cnt > 0 then
2900 --
2901 fnd_file.put_line(fnd_file.log,' ');
2902 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2903 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_SP_WITH'));
2904 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2905 --
2906 for i in 1..g_sp_with_wng_tbl_cnt loop
2907 --
2908 fnd_file.put_line(fnd_file.log,g_sp_with_wng_tbl(i));
2909 --
2910 end loop;
2911 --
2912 end if;
2913 --
2914 if g_debug then
2915 hr_utility.set_location(l_proc,200);
2916 end if;
2917 --
2918 if g_inv_ass_wng_tbl_cnt > 0 then
2919 --
2920 fnd_file.put_line(fnd_file.log,' ');
2921 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2922 fnd_file.put_line(fnd_file.log,fnd_message.get_string('PAY','PAY_JP_LTAX_IMP_INV_ASS'));
2923 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
2924 --
2925 for i in 1..g_inv_ass_wng_tbl_cnt loop
2926 --
2927 fnd_file.put_line(fnd_file.log,g_inv_ass_wng_tbl(i));
2928 --
2929 end loop;
2930 --
2931 end if;
2932 --
2933 if g_debug then
2934 hr_utility.set_location(l_proc,210);
2935 end if;
2936 --
2937 if g_err_ass_set_id is not null then
2938 --
2939 fnd_message.set_name('PAY','PAY_JP_BEE_UTIL_ASG_SET_CREATE');
2940 fnd_message.set_token('ASSIGNMENT_SET_NAME',g_err_ass_set_name);
2941 fnd_file.put_line(fnd_file.log,fnd_message.get);
2942 --
2943 end if;
2944 --
2945 if g_debug then
2946 hr_utility.set_location(l_proc,220);
2947 hr_utility.trace('pay_jp_bee_utility_pkg.g_num_of_outs : '||to_char(pay_jp_bee_utility_pkg.g_num_of_outs));
2948 end if;
2949 --
2950 if pay_jp_bee_utility_pkg.g_num_of_outs = 0 then
2951 --
2952 pay_batch_element_entry_api.delete_batch_header(
2953 p_validate => false,
2954 p_batch_id => l_batch_id,
2955 p_object_version_number => l_batch_ovn);
2956 --
2957 commit;
2958 --
2959 fnd_message.set_name('PAY','PAY_JP_BEE_UTIL_NO_ASGS');
2960 fnd_file.put_line(fnd_file.log,fnd_message.get);
2961 --
2962 end if;
2963 --
2964 if g_debug then
2965 hr_utility.set_location(l_proc,230);
2966 end if;
2967 --
2968 if g_inv_ass_wng_tbl_cnt > 0 then
2969 --
2970 p_retcode := 1;
2971 --
2972 else
2973 --
2974 p_retcode := 0;
2975 --
2976 end if;
2977 --
2978 if g_debug then
2979 hr_utility.trace('p_retcode : '||to_char(p_retcode));
2980 hr_utility.trace('p_errbuf : '||p_errbuf);
2981 hr_utility.set_location(l_proc,1000);
2982 end if;
2983 --
2984 end transfer_imp_ltax_info_to_bee;
2985 --
2986 end pay_jp_ltax_imp_pkg;