[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DEF_REPORT_PKG
Source
1 package body pay_jp_def_report_pkg as
2 /* $Header: pyjpdefr.pkb 120.1 2010/12/15 04:19:56 keyazawa noship $ */
3 --
4 c_package constant varchar2(30) := 'pay_jp_def_report_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6 g_detail_debug varchar2(1);
7 --
8 c_eol varchar2(30) := fnd_global.local_chr(10);
9 --
10 c_st_upd_date_2011 constant date := to_date('2011/01/01','YYYY/MM/DD');
11 --
12 c_circle varchar2(60) := substr(fnd_message.get_string('PER','HR_JP_CIRCLE'),0,1);
13 c_self varchar2(100) := fnd_message.get_string('PER','HR_JP_SELF');
14 c_husband varchar2(100) := fnd_message.get_string('PER','HR_JP_HUSBAND');
15 c_wife varchar2(100) := fnd_message.get_string('PER','HR_JP_WIFE');
16 --
17 c_dep_row_cnt number := 5;
18 c_dep_oe_row_cnt number := 2;
19 c_dep_os_row_cnt number := 2;
20 c_dep_ltx_row_cnt number := 3;
21 --
22 g_xml_assact_ss clob;
23 --
24 -- -------------------------------------------------------------------------
25 -- sequence of process.
26 -- 1. range_cursor (inc. init_pact, archive_pact)
27 -- 2. assignment_action_creation <= invoked by each ranges
28 -- 3. archinit (inc. init_pact) <= invoked by each threads, start from here in mark-for-retry)
29 -- 4. archive_data (inc. init_assact, archive_assact)
30 -- 5. deinitialize_code
31 -- -------------------------------------------------------------------------
32 -- init_pact
33 -- -------------------------------------------------------------------------
34 procedure init_pact(
35 p_payroll_action_id in number)
36 is
37 --
38 l_proc varchar2(80) := c_package||'init_pact';
39 --
40 l_detail_debug varchar2(1) := pay_jp_report_pkg.g_detail_debug;
41 --
42 cursor csr_action
43 is
44 select /*+ ORDERED */
45 ppa.business_group_id,
46 ppa.effective_date,
47 ppa.legislative_parameters,
48 pbg.legislation_code
49 from pay_payroll_actions ppa,
50 per_business_groups_perf pbg
51 where ppa.payroll_action_id = g_payroll_action_id
52 and pbg.business_group_id = ppa.business_group_id;
53 --
54 cursor csr_action_arch
55 is
56 select /*+ ORDERED */
57 ppa.business_group_id,
58 ppa.effective_date,
59 pbg.legislation_code
60 from pay_payroll_actions ppa,
61 per_business_groups_perf pbg
62 where ppa.payroll_action_id = g_arch_payroll_action_id
63 and pbg.business_group_id = ppa.business_group_id;
64 --
65 l_csr_action csr_action%rowtype;
66 l_csr_action_arch csr_action_arch%rowtype;
67 --
68 begin
69 --
70 pay_jp_report_pkg.set_detail_debug('JP_DEBUG_PAYJPDEF_REPORT',l_detail_debug);
71 g_detail_debug := pay_jp_report_pkg.g_detail_debug;
72 --
73 if g_debug then
74 hr_utility.set_location(l_proc,0);
75 end if;
76 --
77 if g_payroll_action_id is null
78 or g_payroll_action_id <> p_payroll_action_id then
79 --
80 if g_debug
81 and g_detail_debug = 'Y' then
82 hr_utility.set_location(l_proc,10);
83 hr_utility.trace('no cache : g_pact_id('||g_payroll_action_id||'),p_pact_id('||p_payroll_action_id||')');
84 end if;
85 --
86 g_payroll_action_id := p_payroll_action_id;
87 --
88 open csr_action;
89 fetch csr_action into l_csr_action;
90 if csr_action%notfound then
91 close csr_action;
92 fnd_message.set_name('PAY','PAY_34985_INVALID_PAY_ACTION');
93 fnd_message.raise_error;
94 end if;
95 close csr_action;
96 --
97 if nvl(g_call_type,'X') = 'SS' then
98 --
99 g_arch_payroll_action_id := p_payroll_action_id;
100 --
101 else
102 --
103 g_arch_payroll_action_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',l_csr_action.legislative_parameters));
104 --
105 end if;
106 --
107 open csr_action_arch;
108 fetch csr_action_arch into l_csr_action_arch;
109 if csr_action_arch%notfound then
110 close csr_action_arch;
111 fnd_message.set_name('PAY','PAY_34985_INVALID_PAY_ACTION');
112 fnd_message.raise_error;
113 end if;
114 close csr_action_arch;
115 --
116 g_effective_date := l_csr_action_arch.effective_date;
117 g_effective_date_eeyy := pay_jp_report_pkg.jp_era_date(g_effective_date,'FMEEYY');
118 g_business_group_id := l_csr_action_arch.business_group_id;
119 g_legislation_code := l_csr_action_arch.legislation_code;
120 g_assignment_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',l_csr_action.legislative_parameters));
121 g_sort_order := pay_core_utils.get_parameter('SORT_ORDER',l_csr_action.legislative_parameters);
122 g_txn_status_b := pay_core_utils.get_parameter('ENABLE_TXN_STATUS',l_csr_action.legislative_parameters);
123 g_txn_status := g_txn_status_b;
124 -- set opposite, currently set disbaled flag in srs to sync with ISDF behavior
125 if g_txn_status = 'Y' then
126 --
127 g_txn_status := 'N';
128 --
129 else
130 --
131 g_txn_status := 'Y';
132 --
133 end if;
134 --
135 if nvl(g_call_type,'X') = 'SS' then
136 --
137 g_emp_no := 'Y';
138 --
139 else
140 --
141 g_emp_no := pay_core_utils.get_parameter('ENABLE_EMP_NO',l_csr_action.legislative_parameters);
142 --
143 end if;
144 --
145 g_remove_act := pay_core_utils.get_parameter('REMOVE_ACT',l_csr_action.legislative_parameters);
146 --
147 g_ass_set_formula_id := null;
148 g_ass_set_amendment_type := null;
149 --
150 if g_assignment_set_id is not null then
151 --
152 hr_jp_ast_utility_pkg.get_assignment_set_info(g_assignment_set_id,g_ass_set_formula_id,g_ass_set_amendment_type);
153 --
154 end if;
155 --
156 if g_debug then
157 hr_utility.trace('g_payroll_action_id : '||to_char(g_payroll_action_id));
158 hr_utility.trace('g_arch_payroll_action_id : '||to_char(g_arch_payroll_action_id));
159 hr_utility.trace('g_effective_date : '||to_char(g_effective_date,'YYYY/MM/DD'));
160 hr_utility.trace('g_effective_date_eeyy : '||g_effective_date_eeyy);
161 hr_utility.trace('g_business_group_id : '||to_char(g_business_group_id));
162 hr_utility.trace('g_legislation_code : '||g_legislation_code);
163 hr_utility.trace('g_assignment_set_id : '||to_char(g_assignment_set_id));
164 hr_utility.trace('g_sort_order : '||g_sort_order);
165 hr_utility.trace('g_txn_status_b : '||g_txn_status_b);
166 hr_utility.trace('g_txn_status : '||g_txn_status);
167 hr_utility.trace('g_emp_no : '||g_emp_no);
168 hr_utility.trace('g_remove_act : '||g_remove_act);
169 hr_utility.trace('g_ass_set_formula_id : '||to_char(g_ass_set_formula_id));
170 hr_utility.trace('g_ass_set_amendment_type : '||g_ass_set_amendment_type);
171 hr_utility.trace('g_call_type : '||g_call_type);
172 end if;
173 --
174 end if;
175 --
176 if g_debug then
177 hr_utility.set_location(l_proc,1000);
178 end if;
179 --
180 end init_pact;
181 --
182 -- -------------------------------------------------------------------------
183 -- archive_pact
184 -- -------------------------------------------------------------------------
185 --procedure archive_pact(
186 -- p_payroll_action_id in number)
187 --is
188 ----
189 -- l_proc varchar2(80) := c_package||'archive_pact';
190 ----
191 --begin
192 ----
193 -- if g_debug
194 -- and g_detail_debug = 'Y' then
195 -- hr_utility.set_location(l_proc,0);
196 -- end if;
197 ----
198 -- if g_debug
199 -- and g_detail_debug = 'Y' then
200 -- hr_utility.set_location(l_proc,1000);
201 -- end if;
202 ----
203 --end archive_pact;
204 --
205 -- -------------------------------------------------------------------------
206 -- range_cursor
207 -- -------------------------------------------------------------------------
208 procedure range_cursor(
209 p_payroll_action_id in number,
210 p_sqlstr out nocopy varchar2)
211 is
212 --
213 l_proc varchar2(80) := c_package||'range_cursor';
214 --
215 begin
216 --
217 if g_debug then
218 hr_utility.set_location(l_proc,0);
219 end if;
220 --
221 init_pact(p_payroll_action_id);
222 --
223 if g_debug
224 and g_detail_debug = 'Y' then
225 hr_utility.set_location(l_proc,10);
226 end if;
227 --
228 -- archive_pact(p_payroll_action_id);
229 ----
230 -- if g_debug
231 -- and g_detail_debug = 'Y' then
232 -- hr_utility.set_location(l_proc,20);
233 -- end if;
234 --
235 ---- no create assact when process assignments flag is set.
236 ----
237 --if g_process_assignments_flag = 'N' then
238 ----
239 -- if g_debug then
240 -- hr_utility.set_location(l_proc,25);
241 -- end if;
242 ----
243 -- p_sqlstr :=
244 -- 'select 1
245 -- from dual
246 -- where :payroll_action_id < 0';
247 ----
248 --else
249 --
250 if g_debug
251 and g_detail_debug = 'Y' then
252 hr_utility.set_location(l_proc,30);
253 end if;
254 --
255 p_sqlstr :=
256 'select distinct ppos.person_id
257 from pay_payroll_actions ppa,
258 per_all_people_f pp,
259 per_periods_of_service ppos
260 where ppa.payroll_action_id = :payroll_action_id
261 and pp.business_group_id = ppa.business_group_id + 0
262 and ppos.person_id = pp.person_id
263 and ppos.business_group_id + 0 = pp.business_group_id
264 and ppos.date_start <= fnd_date.canonical_to_date(''i_effective_date'')
265 and nvl(ppos.final_process_date,fnd_date.canonical_to_date(''i_effective_date'')) >= fnd_date.canonical_to_date(''i_effective_date'')
266 order by
267 ppos.person_id';
268 --
269 p_sqlstr := replace(p_sqlstr,'i_effective_date',fnd_date.date_to_canonical(g_effective_date));
270 --
271 if g_debug then
272 hr_utility.set_location(l_proc,1000);
273 end if;
274 --
275 end range_cursor;
276 --
277 -- -------------------------------------------------------------------------
278 -- assignment_action_creation
279 -- -------------------------------------------------------------------------
280 procedure assignment_action_creation(
281 p_payroll_action_id in number,
282 p_start_person_id in number,
283 p_end_person_id in number,
284 p_chunk_number in number)
285 is
286 --
287 l_proc varchar2(80) := c_package||'assignment_action_creation';
288 --
289 l_assignment_action_id number;
290 --
291 l_ass_cnt number := 0;
292 --
293 l_ass_valid boolean;
294 --
295 --
296 -- treat rehired multiple assignment
297 --
298 -- need to join pjde to exclude transaction U (no employee information)
299 cursor csr_proc_ass
300 is
301 select /*+ ORDERED */
302 pjda.assignment_id,
303 pjda.assignment_action_id,
304 pjda.effective_date
305 from per_periods_of_service ppos,
306 per_all_assignments_f pa,
307 pay_assignment_actions paa,
308 pay_jp_def_assact_v pjda,
309 pay_jp_def_emp_v pjde
310 where ppos.person_id
311 between p_start_person_id and p_end_person_id
312 and ppos.business_group_id + 0 = g_business_group_id
313 and ppos.date_start <= g_effective_date
314 and nvl(ppos.final_process_date,g_effective_date) >= g_effective_date
315 and pa.period_of_service_id = ppos.period_of_service_id
316 and pa.primary_flag = 'Y'
317 and g_effective_date
318 between pa.effective_start_date and pa.effective_end_date
319 and paa.assignment_id = pa.assignment_id
320 and paa.payroll_action_id = g_arch_payroll_action_id
321 and pjda.assignment_action_id = paa.assignment_action_id
322 and decode(nvl(g_txn_status,'Y'),'Y',pjda.transaction_status,'F') in ('F', 'A')
323 and pjde.assignment_action_id = pjda.assignment_action_id
324 and pjde.effective_date = pjda.effective_date;
325 --
326 cursor csr_proc_hasa
327 is
328 select /*+ ORDERED */
329 pjda.assignment_id,
330 pjda.assignment_action_id,
331 pjda.effective_date,
332 hasa.include_or_exclude
333 from per_periods_of_service ppos,
334 per_all_assignments_f pa,
335 pay_assignment_actions paa,
336 hr_assignment_set_amendments hasa,
337 pay_jp_def_assact_v pjda,
338 pay_jp_def_emp_v pjde
339 where ppos.person_id
340 between p_start_person_id and p_end_person_id
341 and ppos.business_group_id + 0 = g_business_group_id
342 and ppos.date_start <= g_effective_date
343 and nvl(ppos.final_process_date,g_effective_date) >= g_effective_date
344 and pa.period_of_service_id = ppos.period_of_service_id
345 and pa.primary_flag = 'Y'
346 and g_effective_date
347 between pa.effective_start_date and pa.effective_end_date
348 and paa.assignment_id = pa.assignment_id
349 and paa.payroll_action_id = g_arch_payroll_action_id
350 and to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
351 decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,paa.assignment_id),
352 paa.assignment_id)) = paa.assignment_id
353 and hasa.assignment_set_id (+) = g_assignment_set_id
354 and hasa.assignment_id (+) = paa.assignment_id
355 and nvl(hasa.include_or_exclude,'I') <> 'E'
356 and pjda.assignment_action_id = paa.assignment_action_id
357 and decode(nvl(g_txn_status,'Y'),'Y',pjda.transaction_status,'F') in ('F', 'A')
358 and pjde.assignment_action_id = pjda.assignment_action_id
359 and pjde.effective_date = pjda.effective_date;
360 --
361 l_csr_proc_ass csr_proc_ass%rowtype;
362 l_csr_proc_hasa csr_proc_hasa%rowtype;
363 --
364 begin
365 --
366 if g_debug then
367 hr_utility.set_location(l_proc,0);
368 hr_utility.trace('p_start_person_id : '||to_char(p_start_person_id));
369 hr_utility.trace('p_end_person_id : '||to_char(p_end_person_id));
370 end if;
371 --
372 -- need set for multiple ranges.
373 init_pact(p_payroll_action_id);
374 --
375 if g_debug
376 and g_detail_debug = 'Y' then
377 hr_utility.set_location(l_proc,10);
378 hr_utility.trace('g_assignment_set_id : '||to_char(g_assignment_set_id));
379 hr_utility.trace('g_ass_set_amendment_type : '||g_ass_set_amendment_type);
380 end if;
381 --
382 if g_assignment_set_id is not null
383 and g_ass_set_amendment_type is not null
384 and g_ass_set_amendment_type <> 'N' then
385 --
386 -- #2243411 bulk collect bug fix is available from 9.2
387 open csr_proc_hasa;
388 loop
389 --
390 fetch csr_proc_hasa into l_csr_proc_hasa;
391 exit when csr_proc_hasa%notfound;
392 --
393 if g_debug
394 and g_detail_debug = 'Y' then
395 hr_utility.set_location(l_proc,20);
396 hr_utility.trace('l_csr_proc_hasa.assignment_id : '||to_char(l_csr_proc_hasa.assignment_id));
397 end if;
398 --
399 l_ass_valid := true;
400 --
401 if g_ass_set_formula_id is not null
402 and l_csr_proc_hasa.include_or_exclude is null then
403 --
404 l_ass_valid := hr_jp_ast_utility_pkg.formula_validate(
405 p_formula_id => g_ass_set_formula_id,
406 p_assignment_id => l_csr_proc_hasa.assignment_id,
407 p_effective_date => l_csr_proc_hasa.effective_date,
408 p_populate_fs => true);
409 --
410 end if;
411 --
412 if l_ass_valid then
413 --
414 if g_debug
415 and g_detail_debug = 'Y' then
416 hr_utility.set_location(l_proc,30);
417 end if;
418 --
419 select pay_assignment_actions_s.nextval
420 into l_assignment_action_id
421 from dual;
422 --
423 hr_nonrun_asact.insact(
424 lockingactid => l_assignment_action_id,
425 assignid => l_csr_proc_hasa.assignment_id,
426 pactid => p_payroll_action_id,
427 chunk => p_chunk_number,
428 greid => null,
429 source_act => l_csr_proc_hasa.assignment_action_id);
430 --
431 l_ass_cnt := l_ass_cnt + 1;
432 --
433 end if;
434 --
435 end loop;
436 close csr_proc_hasa;
437 --
438 else
439 --
440 -- #2243411 bulk collect bug fix is available from 9.2
441 open csr_proc_ass;
442 loop
443 --
444 fetch csr_proc_ass into l_csr_proc_ass;
445 exit when csr_proc_ass%notfound;
446 --
447 if g_debug
448 and g_detail_debug = 'Y' then
449 hr_utility.set_location(l_proc,40);
450 hr_utility.trace('l_csr_proc_ass.assignment_id : '||to_char(l_csr_proc_ass.assignment_id));
451 end if;
452 --
453 l_ass_valid := true;
454 --
455 if g_ass_set_formula_id is not null then
456 --
457 l_ass_valid := hr_jp_ast_utility_pkg.formula_validate(
458 p_formula_id => g_ass_set_formula_id,
459 p_assignment_id => l_csr_proc_ass.assignment_id,
460 p_effective_date => l_csr_proc_ass.effective_date,
461 p_populate_fs => true);
462 --
463 end if;
464 --
465 if l_ass_valid then
466 --
467 if g_debug
468 and g_detail_debug = 'Y' then
469 hr_utility.set_location(l_proc,50);
470 end if;
471 --
472 select pay_assignment_actions_s.nextval
473 into l_assignment_action_id
474 from dual;
475 --
476 hr_nonrun_asact.insact(
477 lockingactid => l_assignment_action_id,
478 assignid => l_csr_proc_ass.assignment_id,
479 pactid => p_payroll_action_id,
480 chunk => p_chunk_number,
481 greid => null,
482 source_act => l_csr_proc_ass.assignment_action_id);
483 --
484 l_ass_cnt := l_ass_cnt + 1;
485 --
486 end if;
487 --
488 end loop;
489 close csr_proc_ass;
490 --
491 end if;
492 --
493 if g_debug then
494 hr_utility.trace('l_ass_cnt : '||to_char(l_ass_cnt));
495 hr_utility.set_location(l_proc,1000);
496 end if;
497 --
498 end assignment_action_creation;
499 --
500 -- -------------------------------------------------------------------------
501 -- archinit
502 -- -------------------------------------------------------------------------
503 procedure archinit(
504 p_payroll_action_id in number)
505 is
506 --
507 l_proc varchar2(80) := c_package||'archinit';
508 --
509 begin
510 --
511 if g_debug then
512 hr_utility.set_location(l_proc,0);
513 end if;
514 --
515 -- invoke for mark for retry.
516 init_pact(p_payroll_action_id);
517 --
518 if g_debug then
519 --
520 hr_utility.set_location(l_proc,1000);
521 --
522 end if;
523 --
524 end archinit;
525 --
526 -- -------------------------------------------------------------------------
527 -- init_assact
528 -- -------------------------------------------------------------------------
529 --procedure init_assact(
530 -- p_assignment_action_id in number,
531 -- p_assignment_id in number)
532 --is
533 ----
534 -- l_proc varchar2(80) := c_package||'init_assact';
535 ----
536 --begin
537 ----
538 -- if g_debug then
539 -- hr_utility.set_location(l_proc,0);
540 -- end if;
541 -- --
542 -- if g_debug then
543 -- hr_utility.set_location(l_proc,1000);
544 -- end if;
545 ----
546 --end init_assact;
547 --
548 -- -------------------------------------------------------------------------
549 -- archive_assact
550 -- -------------------------------------------------------------------------
551 --procedure archive_assact(
552 -- p_assignment_action_id in number,
553 -- p_assignment_id in number)
554 --is
555 ----
556 -- l_proc varchar2(80) := c_package||'archive_assact';
557 ----
558 --begin
559 ----
560 -- if g_debug then
561 -- hr_utility.set_location(l_proc,0);
562 -- end if;
563 ----
564 -- if g_debug
565 -- and g_detail_debug = 'Y' then
566 -- hr_utility.set_location(l_proc,1000);
567 -- end if;
568 ----
569 --end archive_assact;
570 --
571 -- -------------------------------------------------------------------------
572 -- xml_assact
573 -- -------------------------------------------------------------------------
574 procedure xml_assact(
575 p_assignment_action_id in number)
576 is
577 --
578 l_proc varchar2(80) := c_package||'xml_assact';
579 --
580 l_data_dep_cnt number;
581 l_data_dep_oe_cnt number;
582 l_data_dep_os_cnt number;
583 l_data_dep_ltx_cnt number;
584 l_data_rowset_cnt number;
585 l_data_dep_rowset_cnt number;
586 l_data_dep_oe_rowset_cnt number;
587 l_data_dep_os_rowset_cnt number;
588 l_data_dep_ltx_rowset_cnt number;
589 --
590 l_xml_assact_temp varchar2(32767);
591 l_xml_assact varchar2(32767);
592 --
593 type t_body_rec is record(
594 assignment_action_id number,
595 effective_date date,
596 o_effective_date_eeyy varchar2(30),
597 o_tax_office_name varchar2(240),
598 o_district_name varchar2(240),
599 o_itax_org_name varchar2(240),
600 o_itax_org_address varchar2(240),
601 o_employee_number varchar2(30),
602 o_full_name_kana varchar2(500),
603 o_full_name varchar2(500),
604 a_birth_date date,
605 o_birth_date_era_m varchar2(30),
606 o_birth_date_era_t varchar2(30),
607 o_birth_date_era_s varchar2(30),
608 o_birth_date_era_h varchar2(30),
609 o_birth_date_era_yy varchar2(30),
610 o_birth_date_era_mm varchar2(30),
611 o_birth_date_era_dd varchar2(30),
612 a_sex_b varchar2(30),
613 o_household_head_full_name varchar2(500),
614 a_household_head_con_type_b varchar2(30),
615 o_household_head_contact_type varchar2(80),
616 a_postal_code varchar2(240),
617 o_postal_code1 varchar2(30),
618 o_postal_code2 varchar2(30),
619 o_address varchar2(240),
620 a_married_flag varchar2(60),
621 o_spouse_exist_y varchar2(30),
622 o_spouse_exist_n varchar2(30),
623 a_sec_sal_submit_flag varchar2(60),
624 o_sec_sal_submit_flag varchar2(30),
625 a_emp_disability_type_b varchar2(30),
626 o_emp_disabled_flag varchar2(30),
627 o_emp_svr_disabled_flag varchar2(30),
628 a_emp_aged_type_b varchar2(30),
629 o_emp_aged_flag varchar2(30),
630 a_emp_widow_type_b varchar2(30),
631 o_emp_widow_flag varchar2(30),
632 o_emp_sp_widow_flag varchar2(30),
633 o_emp_widower_flag varchar2(30),
634 a_emp_working_st_type_b varchar2(30),
635 o_emp_working_st_flag varchar2(30),
636 a_emp_disability_details varchar2(500),
637 a_emp_aged_details varchar2(500),
638 a_emp_widow_details varchar2(500),
639 a_emp_working_st_details varchar2(500),
640 a_emp_change_date date,
641 o_emp_change_date varchar2(60),
642 o_emp_change_reason varchar2(240),
643 o_sp_full_name varchar2(500),
644 a_sp_birth_date date,
645 o_sp_birth_date_era_m varchar2(30),
646 o_sp_birth_date_era_t varchar2(30),
647 o_sp_birth_date_era_s varchar2(30),
648 o_sp_birth_date_era_h varchar2(30),
649 o_sp_birth_date_era_yy varchar2(30),
650 o_sp_birth_date_era_mm varchar2(30),
651 o_sp_birth_date_era_dd varchar2(30),
652 a_sp_dep_type_b varchar2(30),
653 o_sp_aged_flag varchar2(30),
654 o_sp_occupation varchar2(240),
655 o_sp_address varchar2(240),
656 o_sp_est_annual_inc number,
657 a_sp_change_date date,
658 o_sp_change_date varchar2(60),
659 o_sp_change_reason varchar2(240),
660 a_sp_disability_type_b varchar2(30),
661 o_sp_disabled_flag varchar2(30),
662 o_sp_svr_disabled_flag varchar2(30),
663 o_sp_svr_disabled_lt_flag varchar2(30),
664 a_sp_disability_details varchar2(500),
665 o_dep_disabled_flag varchar2(30),
666 o_dep_disabled_cnt number,
667 o_dep_svr_disabled_flag varchar2(30),
668 o_dep_svr_disabled_cnt number,
669 o_dep_svr_disabled_lt_flag varchar2(30),
670 o_dep_svr_disabled_lt_cnt number,
671 a_dep_disability_details varchar2(2000),
672 a_dep_ltx_disability_details varchar2(2000),
673 o_details varchar2(5000));
674 l_body_rec t_body_rec;
675 --
676 type t_data_dep_rec is record(
677 o_dep_full_name varchar2(500),
678 a_dep_contact_type_b varchar2(30),
679 o_dep_contact_type varchar2(80),
680 a_dep_birth_date date,
681 o_dep_birth_date_era_m varchar2(30),
682 o_dep_birth_date_era_t varchar2(30),
683 o_dep_birth_date_era_s varchar2(30),
684 o_dep_birth_date_era_h varchar2(30),
685 o_dep_birth_date_era_yy varchar2(30),
686 o_dep_birth_date_era_mm varchar2(30),
687 o_dep_birth_date_era_dd varchar2(30),
688 a_dep_dep_type_b varchar2(30),
689 o_dep_aged_prnt_lt_flag varchar2(30),
690 o_dep_aged_flag varchar2(30),
691 o_dep_sp_flag varchar2(30),
692 o_dep_occupation varchar2(240),
693 o_dep_address varchar2(240),
694 o_dep_est_annual_inc number,
695 a_dep_change_date date,
696 o_dep_change_date varchar2(60),
697 o_dep_change_reason varchar2(240),
698 a_dep_disability_type_b varchar2(30),
699 a_dep_disability_details varchar2(500));
700 l_data_dep_rec t_data_dep_rec;
701 l_data_dep_dummy_rec t_data_dep_rec;
702 type t_data_dep_tbl is table of t_data_dep_rec index by binary_integer;
703 l_data_dep_tbl t_data_dep_tbl;
704 l_data_dep_ind_tbl t_data_dep_tbl;
705 --
706 type t_data_dep_oe_rec is record(
707 o_dep_oe_full_name varchar2(500),
708 a_dep_oe_contact_type_b varchar2(30),
709 o_dep_oe_contact_type varchar2(80),
710 a_dep_oe_birth_date date,
711 o_dep_oe_birth_date_era_m varchar2(30),
712 o_dep_oe_birth_date_era_t varchar2(30),
713 o_dep_oe_birth_date_era_s varchar2(30),
714 o_dep_oe_birth_date_era_h varchar2(30),
715 o_dep_oe_birth_date_era_yy varchar2(30),
716 o_dep_oe_birth_date_era_mm varchar2(30),
717 o_dep_oe_birth_date_era_dd varchar2(30),
718 o_dep_oe_occupation varchar2(240),
719 o_dep_oe_address varchar2(240),
720 a_dep_oe_change_date date,
721 o_dep_oe_change_date varchar2(60),
722 o_dep_oe_change_reason varchar2(240),
723 o_dep_oe_oe_full_name varchar2(240),
724 a_dep_oe_oe_contact_type_b varchar2(30),
725 o_dep_oe_oe_contact_type varchar2(80),
726 o_dep_oe_oe_address varchar2(240));
727 l_data_dep_oe_rec t_data_dep_oe_rec;
728 l_data_dep_oe_dummy_rec t_data_dep_oe_rec;
729 type t_data_dep_oe_tbl is table of t_data_dep_oe_rec index by binary_integer;
730 l_data_dep_oe_tbl t_data_dep_oe_tbl;
731 l_data_dep_oe_ind_tbl t_data_dep_oe_tbl;
732 --
733 type t_data_dep_os_rec is record(
734 o_dep_os_full_name varchar2(500),
735 a_dep_os_contact_type_b varchar2(30),
736 o_dep_os_contact_type varchar2(80),
737 a_dep_os_birth_date date,
738 o_dep_os_birth_date_era_m varchar2(30),
739 o_dep_os_birth_date_era_t varchar2(30),
740 o_dep_os_birth_date_era_s varchar2(30),
741 o_dep_os_birth_date_era_h varchar2(30),
742 o_dep_os_birth_date_era_yy varchar2(30),
743 o_dep_os_birth_date_era_mm varchar2(30),
744 o_dep_os_birth_date_era_dd varchar2(30),
745 o_dep_os_occupation varchar2(240),
746 o_dep_os_os_sal_er_name varchar2(240),
747 o_dep_os_os_sal_er_address varchar2(240));
748 l_data_dep_os_rec t_data_dep_os_rec;
749 l_data_dep_os_dummy_rec t_data_dep_os_rec;
750 type t_data_dep_os_tbl is table of t_data_dep_os_rec index by binary_integer;
751 l_data_dep_os_tbl t_data_dep_os_tbl;
752 l_data_dep_os_ind_tbl t_data_dep_os_tbl;
753 --
754 type t_data_dep_ltx_rec is record(
755 o_dep_ltx_full_name varchar2(500),
756 a_dep_ltx_contact_type_b varchar2(30),
757 o_dep_ltx_contact_type varchar2(80),
758 a_dep_ltx_birth_date date,
759 --o_dep_ltx_birth_date_era_h varchar2(30),
760 o_dep_ltx_birth_date_era_yy varchar2(30),
761 o_dep_ltx_birth_date_era_mm varchar2(30),
762 o_dep_ltx_birth_date_era_dd varchar2(30),
763 o_dep_ltx_address varchar2(240),
764 o_dep_ltx_est_annual_inc number,
765 a_dep_ltx_change_date date,
766 o_dep_ltx_change_date varchar2(60),
767 o_dep_ltx_change_reason varchar2(240),
768 a_dep_ltx_disability_type_b varchar2(30),
769 a_dep_ltx_disability_details varchar2(500));
770 l_data_dep_ltx_rec t_data_dep_ltx_rec;
771 l_data_dep_ltx_dummy_rec t_data_dep_ltx_rec;
772 type t_data_dep_ltx_tbl is table of t_data_dep_ltx_rec index by binary_integer;
773 l_data_dep_ltx_tbl t_data_dep_ltx_tbl;
774 l_data_dep_ltx_ind_tbl t_data_dep_ltx_tbl;
775 --
776 cursor csr_data_ass
777 is
778 select /*+ ORDERED */
779 pjde.action_information_id,
780 pjdp.tax_office_name,
781 pjde.district_name,
782 pjdp.salary_payer_name,
783 pjdp.salary_payer_address,
784 pjde.employee_number,
785 pjde.last_name_kana,
786 pjde.first_name_kana,
787 pjde.last_name,
788 pjde.first_name,
789 pjde.date_of_birth,
790 pjde.sex,
791 pjde.household_head_ctr_id,
792 pjde.household_head_full_name,
793 pjde.household_head_contact_type,
794 pjde.postal_code,
795 pjde.address,
796 pjde.married_flag,
797 pjde.sec_sal_submit_flag,
798 pjde.disability_type,
799 pjde.aged_type,
800 pjde.widow_type,
801 pjde.working_student_type,
802 pjde.disability_details,
803 pjde.aged_details,
804 pjde.widow_details,
805 pjde.working_student_details,
806 pjde.change_date,
807 pjde.change_reason
808 from pay_assignment_actions paa,
809 pay_jp_def_pact_v pjdp,
810 pay_jp_def_assact_v pjda,
811 pay_jp_def_emp_v pjde
812 where paa.assignment_action_id = p_assignment_action_id
813 and pjdp.payroll_action_id = paa.payroll_action_id
814 and pjda.assignment_action_id = paa.assignment_action_id
815 and pjde.assignment_action_id = pjda.assignment_action_id
816 and pjde.effective_date = pjda.effective_date;
817 --
818 cursor csr_data_spouse
819 is
820 select /*+ ORDERED */
821 pjdd.action_information_id,
822 pjdd.last_name,
823 pjdd.first_name,
824 pjdd.date_of_birth,
825 pjdd.dep_type,
826 pjdd.occupation,
827 pjdd.address,
828 pjdd.estimated_annual_income,
829 pjdd.change_date,
830 pjdd.change_reason,
831 pjdd.disability_type,
832 pjdd.disability_details
833 from pay_jp_def_dep_v pjdd
834 where pjdd.assignment_action_id = p_assignment_action_id
835 and pjdd.contact_type = 'S'
836 and pjdd.status <> 'D';
837 --
838 cursor csr_data_dep
839 is
840 select /*+ ORDERED */
841 pjdd.last_name,
842 pjdd.first_name,
843 pjdd.contact_type,
844 pjdd.date_of_birth,
845 pjdd.dep_type,
846 pjdd.occupation,
847 pjdd.address,
848 pjdd.estimated_annual_income,
849 pjdd.change_date,
850 pjdd.change_reason,
851 pjdd.disability_type,
852 pjdd.disability_details
853 from pay_jp_def_dep_v pjdd
854 where pjdd.assignment_action_id = p_assignment_action_id
855 and pjdd.contact_type <> 'S'
856 and pjdd.status <> 'D'
857 and nvl(pjdd.dep_type,'0') <> '5'
858 order by
859 pjdd.date_of_birth,
860 pjdd.last_name_kana,
861 pjdd.first_name_kana;
862 --
863 cursor csr_data_dep_oe
864 is
865 select /*+ ORDERED */
866 pjddo.last_name,
867 pjddo.first_name,
868 pjddo.contact_type,
869 pjddo.date_of_birth,
870 pjddo.occupation,
871 pjddo.address,
872 pjddo.change_date,
873 pjddo.change_reason,
874 pjddo.oe_full_name,
875 pjddo.oe_contact_type,
876 pjddo.oe_address
877 from pay_jp_def_dep_oe_v pjddo
878 where pjddo.assignment_action_id = p_assignment_action_id
879 and pjddo.status <> 'D'
880 order by
881 decode(pjddo.contact_type,'S',1,2),
882 pjddo.date_of_birth,
883 pjddo.last_name_kana,
884 pjddo.first_name_kana;
885 --
886 cursor csr_data_dep_os
887 is
888 select /*+ ORDERED */
889 pjddo.last_name,
890 pjddo.first_name,
891 pjddo.contact_type,
892 pjddo.date_of_birth,
893 pjddo.occupation,
894 pjddo.os_salary_payer_name,
895 pjddo.os_salary_payer_address
896 from pay_jp_def_dep_os_v pjddo
897 where pjddo.assignment_action_id = p_assignment_action_id
898 and pjddo.status <> 'D'
899 order by
900 decode(pjddo.contact_type,'S',1,2),
901 pjddo.date_of_birth,
902 pjddo.last_name_kana,
903 pjddo.first_name_kana;
904 --
905 cursor csr_data_dep_ltx
906 is
907 select /*+ ORDERED */
908 pjdd.last_name,
909 pjdd.first_name,
910 pjdd.contact_type,
911 pjdd.date_of_birth,
912 pjdd.address,
913 pjdd.estimated_annual_income,
914 pjdd.change_date,
915 pjdd.change_reason,
916 pjdd.disability_type,
917 pjdd.disability_details
918 from pay_jp_def_dep_v pjdd
919 where pjdd.assignment_action_id = p_assignment_action_id
920 and pjdd.contact_type <> 'S'
921 and nvl(pjdd.dep_type,'0') = '5'
922 and pjdd.status <> 'D'
923 order by
924 pjdd.date_of_birth,
925 pjdd.last_name_kana,
926 pjdd.first_name_kana;
927 --
928 l_csr_data_ass csr_data_ass%rowtype;
929 l_csr_data_spouse csr_data_spouse%rowtype;
930 l_csr_data_dep csr_data_dep%rowtype;
931 l_csr_data_dep_oe csr_data_dep_oe%rowtype;
932 l_csr_data_dep_os csr_data_dep_os%rowtype;
933 l_csr_data_dep_ltx csr_data_dep_ltx%rowtype;
934 --
935 begin
936 --
937 if g_debug
938 and g_detail_debug = 'Y' then
939 hr_utility.set_location(l_proc,0);
940 hr_utility.trace('p_assignment_action_id : '||to_char(p_assignment_action_id));
941 end if;
942 --
943 l_data_rowset_cnt := 0;
944 --
945 open csr_data_ass;
946 fetch csr_data_ass into l_csr_data_ass;
947 close csr_data_ass;
948 --
949 if g_debug
950 and g_detail_debug = 'Y' then
951 hr_utility.set_location(l_proc,10);
952 hr_utility.trace('l_csr_data_ass.action_information_id : '||to_char(l_csr_data_ass.action_information_id));
953 end if;
954 --
955 open csr_data_spouse;
956 fetch csr_data_spouse into l_csr_data_spouse;
957 close csr_data_spouse;
958 --
959 if g_debug
960 and g_detail_debug = 'Y' then
961 hr_utility.set_location(l_proc,20);
962 hr_utility.trace('l_csr_data_spouse.action_information_id : '||to_char(l_csr_data_spouse.action_information_id));
963 end if;
964 --
965 -- -----------------------------------------------------
966 -- l_body_rec setup
967 -- -----------------------------------------------------
968 l_body_rec.assignment_action_id := p_assignment_action_id;
969 l_body_rec.effective_date := g_effective_date;
970 l_body_rec.o_effective_date_eeyy := g_effective_date_eeyy;
971 l_body_rec.o_tax_office_name := l_csr_data_ass.tax_office_name;
972 l_body_rec.o_district_name := l_csr_data_ass.district_name;
973 l_body_rec.o_itax_org_name := l_csr_data_ass.salary_payer_name;
974 l_body_rec.o_itax_org_address := l_csr_data_ass.salary_payer_address;
975 --
976 if nvl(g_emp_no,'N') = 'Y' then
977 l_body_rec.o_employee_number := l_csr_data_ass.employee_number;
978 end if;
979 --
980 l_body_rec.o_full_name_kana := trim(l_csr_data_ass.last_name_kana||' '||l_csr_data_ass.first_name_kana);
981 l_body_rec.o_full_name := trim(l_csr_data_ass.last_name||' '||l_csr_data_ass.first_name);
982 l_body_rec.a_birth_date := l_csr_data_ass.date_of_birth;
983 l_body_rec.o_birth_date_era_m := pay_jp_report_pkg.set_era_code(l_csr_data_ass.date_of_birth,'M');
984 l_body_rec.o_birth_date_era_t := pay_jp_report_pkg.set_era_code(l_csr_data_ass.date_of_birth,'T');
985 l_body_rec.o_birth_date_era_s := pay_jp_report_pkg.set_era_code(l_csr_data_ass.date_of_birth,'S');
986 l_body_rec.o_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_ass.date_of_birth,'H');
987 l_body_rec.o_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_ass.date_of_birth,'FMEYY'),2,2);
988 l_body_rec.o_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_ass.date_of_birth,'FMMM');
989 l_body_rec.o_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_ass.date_of_birth,'FMDD');
990 l_body_rec.a_sex_b := l_csr_data_ass.sex;
991 --
992 l_body_rec.o_household_head_full_name := l_body_rec.o_full_name;
993 l_body_rec.a_household_head_con_type_b := l_csr_data_ass.household_head_contact_type;
994 l_body_rec.o_household_head_contact_type := c_self;
995 if l_csr_data_ass.household_head_ctr_id is not null then
996 --
997 l_body_rec.o_household_head_full_name := l_csr_data_ass.household_head_full_name;
998 l_body_rec.o_household_head_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_ass.household_head_contact_type);
999 if l_csr_data_ass.household_head_contact_type = 'S' then
1000 --
1001 l_body_rec.o_household_head_contact_type := c_husband;
1002 if l_csr_data_ass.sex = 'M' then
1003 --
1004 l_body_rec.o_household_head_contact_type := c_wife;
1005 --
1006 end if;
1007 --
1008 end if;
1009 --
1010 end if;
1011 --
1012 l_body_rec.a_postal_code := l_csr_data_ass.postal_code;
1013 l_body_rec.o_postal_code1 := substr(replace(replace(trim(l_csr_data_ass.postal_code),' ',''),to_multi_byte(' '),''),1,3);
1014 l_body_rec.o_postal_code2 := substr(replace(replace(trim(l_csr_data_ass.postal_code),' ',''),to_multi_byte(' '),''),-4);
1015 l_body_rec.o_address := l_csr_data_ass.address;
1016 l_body_rec.a_married_flag := l_csr_data_ass.married_flag;
1017 l_body_rec.o_spouse_exist_y := pay_jp_report_pkg.set_circle(l_csr_data_ass.married_flag,'Y');
1018 l_body_rec.o_spouse_exist_n := pay_jp_report_pkg.set_circle(l_csr_data_ass.married_flag,'N');
1019 l_body_rec.a_sec_sal_submit_flag := l_csr_data_ass.sec_sal_submit_flag;
1020 l_body_rec.o_sec_sal_submit_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.sec_sal_submit_flag,'Y');
1021 l_body_rec.a_emp_disability_type_b := l_csr_data_ass.disability_type;
1022 l_body_rec.o_emp_disabled_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.disability_type,'1');
1023 l_body_rec.o_emp_svr_disabled_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.disability_type,'2');
1024 l_body_rec.a_emp_aged_type_b := l_csr_data_ass.aged_type;
1025 l_body_rec.o_emp_aged_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.aged_type,'1');
1026 l_body_rec.a_emp_widow_type_b := l_csr_data_ass.widow_type;
1027 l_body_rec.o_emp_widow_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.widow_type,'1');
1028 l_body_rec.o_emp_sp_widow_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.widow_type,'2');
1029 l_body_rec.o_emp_widower_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.widow_type,'3');
1030 l_body_rec.a_emp_working_st_type_b := l_csr_data_ass.working_student_type;
1031 l_body_rec.o_emp_working_st_flag := pay_jp_report_pkg.set_circle(l_csr_data_ass.working_student_type,'1');
1032 --
1033 if l_csr_data_ass.disability_type <> '0' then
1034 --
1035 l_body_rec.a_emp_disability_details := hr_general.decode_lookup('JP_DEF_DISABILITY_TYPE',l_csr_data_ass.disability_type)||
1036 '('||l_csr_data_ass.disability_details||')';
1037 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_emp_disability_details);
1038 --
1039 end if;
1040 --
1041 if l_csr_data_ass.aged_type <> '0' then
1042 --
1043 l_body_rec.a_emp_aged_details := hr_general.decode_lookup('JP_DEF_AGED_TYPE',l_csr_data_ass.aged_type)||
1044 '('||l_csr_data_ass.aged_details||')';
1045 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_emp_aged_details);
1046 --
1047 end if;
1048 --
1049 if l_csr_data_ass.widow_type <> '0' then
1050 --
1051 l_body_rec.a_emp_widow_details := hr_general.decode_lookup('JP_DEF_WIDOW_TYPE',l_csr_data_ass.widow_type)||
1052 '('||l_csr_data_ass.widow_details||')';
1053 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_emp_widow_details);
1054 --
1055 end if;
1056 --
1057 if l_csr_data_ass.working_student_type <> '0' then
1058 --
1059 l_body_rec.a_emp_working_st_details := hr_general.decode_lookup('JP_DEF_WORKING_STUDENT_TYPE',l_csr_data_ass.working_student_type)||
1060 '('||l_csr_data_ass.working_student_details||')';
1061 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_emp_working_st_details);
1062 --
1063 end if;
1064 --
1065 -- change date and change reason have been set by sshr user (archive default is null, validation within same year is not required here)
1066 l_body_rec.a_emp_change_date := l_csr_data_ass.change_date;
1067 l_body_rec.o_emp_change_date := pay_jp_report_pkg.jp_era_date(l_csr_data_ass.change_date,'FMEYY.MM.DD');
1068 l_body_rec.o_emp_change_reason := l_csr_data_ass.change_reason;
1069 --
1070 if g_debug
1071 and g_detail_debug = 'Y' then
1072 hr_utility.set_location(l_proc,30);
1073 end if;
1074 --
1075 l_body_rec.o_sp_full_name := trim(l_csr_data_spouse.last_name||' '||l_csr_data_spouse.first_name);
1076 l_body_rec.a_sp_birth_date := l_csr_data_spouse.date_of_birth;
1077 l_body_rec.o_sp_birth_date_era_m := pay_jp_report_pkg.set_era_code(l_csr_data_spouse.date_of_birth,'M');
1078 l_body_rec.o_sp_birth_date_era_t := pay_jp_report_pkg.set_era_code(l_csr_data_spouse.date_of_birth,'T');
1079 l_body_rec.o_sp_birth_date_era_s := pay_jp_report_pkg.set_era_code(l_csr_data_spouse.date_of_birth,'S');
1080 l_body_rec.o_sp_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_spouse.date_of_birth,'H');
1081 l_body_rec.o_sp_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_spouse.date_of_birth,'FMEYY'),2,2);
1082 l_body_rec.o_sp_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_spouse.date_of_birth,'FMMM');
1083 l_body_rec.o_sp_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_spouse.date_of_birth,'FMDD');
1084 l_body_rec.a_sp_dep_type_b := l_csr_data_spouse.dep_type;
1085 l_body_rec.o_sp_aged_flag := pay_jp_report_pkg.set_circle(l_csr_data_spouse.dep_type,'20');
1086 l_body_rec.o_sp_occupation := l_csr_data_spouse.occupation;
1087 l_body_rec.o_sp_address := l_csr_data_spouse.address;
1088 l_body_rec.o_sp_est_annual_inc := l_csr_data_spouse.estimated_annual_income;
1089 l_body_rec.a_sp_change_date := l_csr_data_spouse.change_date;
1090 l_body_rec.o_sp_change_date := pay_jp_report_pkg.jp_era_date(l_csr_data_spouse.change_date,'FMEYY.MM.DD');
1091 l_body_rec.o_sp_change_reason := l_csr_data_spouse.change_reason;
1092 l_body_rec.a_sp_disability_type_b := l_csr_data_spouse.disability_type;
1093 l_body_rec.o_sp_disabled_flag := pay_jp_report_pkg.set_circle(l_csr_data_spouse.disability_type,'10');
1094 l_body_rec.o_sp_svr_disabled_flag := pay_jp_report_pkg.set_circle(l_csr_data_spouse.disability_type,'20');
1095 l_body_rec.o_sp_svr_disabled_lt_flag := pay_jp_report_pkg.set_circle(l_csr_data_spouse.disability_type,'30');
1096 --
1097 if l_csr_data_spouse.disability_type <> '0' then
1098 --
1099 l_body_rec.a_sp_disability_details := l_body_rec.o_sp_full_name||
1100 '('||hr_general.decode_lookup('JP_DEF_DEP_DISABILITY_TYPE',l_csr_data_spouse.disability_type)||
1101 ','||l_csr_data_spouse.disability_details||')';
1102 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_sp_disability_details);
1103 --
1104 end if;
1105 --
1106 if g_debug
1107 and g_detail_debug = 'Y' then
1108 hr_utility.set_location(l_proc,40);
1109 end if;
1110 --
1111 l_data_dep_cnt := 0;
1112 l_data_dep_rowset_cnt := 0;
1113 open csr_data_dep;
1114 loop
1115 --
1116 fetch csr_data_dep into l_csr_data_dep;
1117 exit when csr_data_dep%notfound;
1118 --
1119 l_data_dep_tbl(l_data_dep_cnt).o_dep_full_name := trim(l_csr_data_dep.last_name||' '||l_csr_data_dep.first_name);
1120 l_data_dep_tbl(l_data_dep_cnt).a_dep_contact_type_b := l_csr_data_dep.contact_type;
1121 l_data_dep_tbl(l_data_dep_cnt).a_dep_birth_date := l_csr_data_dep.date_of_birth;
1122 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_m := pay_jp_report_pkg.set_era_code(l_csr_data_dep.date_of_birth,'M');
1123 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_t := pay_jp_report_pkg.set_era_code(l_csr_data_dep.date_of_birth,'T');
1124 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_s := pay_jp_report_pkg.set_era_code(l_csr_data_dep.date_of_birth,'S');
1125 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_dep.date_of_birth,'H');
1126 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_dep.date_of_birth,'FMEYY'),2,2);
1127 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_dep.date_of_birth,'FMMM');
1128 l_data_dep_tbl(l_data_dep_cnt).o_dep_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_dep.date_of_birth,'FMDD');
1129 l_data_dep_tbl(l_data_dep_cnt).a_dep_dep_type_b := l_csr_data_dep.dep_type;
1130 l_data_dep_tbl(l_data_dep_cnt).o_dep_aged_prnt_lt_flag := pay_jp_report_pkg.set_circle(l_csr_data_dep.dep_type,'30');
1131 l_data_dep_tbl(l_data_dep_cnt).o_dep_aged_flag := pay_jp_report_pkg.set_circle(l_csr_data_dep.dep_type,'20');
1132 l_data_dep_tbl(l_data_dep_cnt).o_dep_sp_flag := pay_jp_report_pkg.set_circle(l_csr_data_dep.dep_type,'10');
1133 l_data_dep_tbl(l_data_dep_cnt).o_dep_occupation := l_csr_data_dep.occupation;
1134 l_data_dep_tbl(l_data_dep_cnt).o_dep_address := l_csr_data_dep.address;
1135 l_data_dep_tbl(l_data_dep_cnt).o_dep_est_annual_inc := l_csr_data_dep.estimated_annual_income;
1136 l_data_dep_tbl(l_data_dep_cnt).a_dep_change_date := l_csr_data_dep.change_date;
1137 l_data_dep_tbl(l_data_dep_cnt).o_dep_change_date := pay_jp_report_pkg.jp_era_date(l_csr_data_dep.change_date,'FMEYY.MM.DD');
1138 l_data_dep_tbl(l_data_dep_cnt).o_dep_change_reason := l_csr_data_dep.change_reason;
1139 l_data_dep_tbl(l_data_dep_cnt).a_dep_disability_type_b := l_csr_data_dep.disability_type;
1140 --
1141 l_data_dep_tbl(l_data_dep_cnt).o_dep_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_dep.contact_type);
1142 if l_csr_data_dep.contact_type = 'S' then
1143 --
1144 l_data_dep_tbl(l_data_dep_cnt).o_dep_contact_type := c_husband;
1145 if l_csr_data_ass.sex = 'M' then
1146 --
1147 l_data_dep_tbl(l_data_dep_cnt).o_dep_contact_type := c_wife;
1148 --
1149 end if;
1150 --
1151 end if;
1152 --
1153 if l_csr_data_dep.disability_type <> '0' then
1154 --
1155 l_data_dep_tbl(l_data_dep_cnt).a_dep_disability_details := l_data_dep_tbl(l_data_dep_cnt).o_dep_full_name||
1156 '('||hr_general.decode_lookup('JP_DEF_DEP_DISABILITY_TYPE',l_csr_data_dep.disability_type)||
1157 ','||l_csr_data_dep.disability_details||')';
1158 --
1159 if l_csr_data_dep.disability_type = '10' then
1160 --
1161 l_body_rec.o_dep_disabled_flag := c_circle;
1162 l_body_rec.o_dep_disabled_cnt := nvl(l_body_rec.o_dep_disabled_cnt,0) + 1;
1163 --
1164 elsif l_csr_data_dep.disability_type = '20' then
1165 --
1166 l_body_rec.o_dep_svr_disabled_flag := c_circle;
1167 l_body_rec.o_dep_svr_disabled_cnt := nvl(l_body_rec.o_dep_svr_disabled_cnt,0) + 1;
1168 --
1169 elsif l_csr_data_dep.disability_type = '30' then
1170 --
1171 l_body_rec.o_dep_svr_disabled_lt_flag := c_circle;
1172 l_body_rec.o_dep_svr_disabled_lt_cnt := nvl(l_body_rec.o_dep_svr_disabled_lt_cnt,0) + 1;
1173 --
1174 end if;
1175 --
1176 l_body_rec.a_dep_disability_details := pay_jp_report_pkg.concatenate(l_body_rec.a_dep_disability_details,l_data_dep_tbl(l_data_dep_cnt).a_dep_disability_details);
1177 --
1178 end if;
1179 --
1180 l_data_dep_cnt := l_data_dep_cnt + 1;
1181 --
1182 if l_data_dep_cnt > (l_data_dep_rowset_cnt + 1) * c_dep_row_cnt then
1183 --
1184 l_data_dep_rowset_cnt := l_data_dep_rowset_cnt + 1;
1185 --
1186 end if;
1187 --
1188 l_data_dep_ind_tbl((l_data_dep_rowset_cnt * 100) + (l_data_dep_cnt - (l_data_dep_rowset_cnt * c_dep_row_cnt))) := l_data_dep_tbl(l_data_dep_cnt - 1);
1189 --
1190 end loop;
1191 close csr_data_dep;
1192 --
1193 if l_data_dep_cnt < (l_data_dep_rowset_cnt + 1) * c_dep_row_cnt then
1194 --
1195 for i in 1..((l_data_dep_rowset_cnt + 1) * c_dep_row_cnt - l_data_dep_cnt) loop
1196 --
1197 l_data_dep_ind_tbl((l_data_dep_rowset_cnt * 100) + (l_data_dep_cnt - (l_data_dep_rowset_cnt * c_dep_row_cnt)) + i) := l_data_dep_dummy_rec;
1198 --
1199 end loop;
1200 --
1201 end if;
1202 --
1203 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_dep_disability_details);
1204 l_data_rowset_cnt := greatest(l_data_rowset_cnt,l_data_dep_rowset_cnt);
1205 --
1206 if g_debug
1207 and g_detail_debug = 'Y' then
1208 hr_utility.set_location(l_proc,50);
1209 hr_utility.trace('l_data_dep_cnt : '||to_char(l_data_dep_cnt));
1210 hr_utility.trace('l_data_dep_rowset_cnt : '||to_char(l_data_dep_rowset_cnt));
1211 hr_utility.trace('l_data_rowset_cnt : '||to_char(l_data_rowset_cnt));
1212 hr_utility.trace('l_data_dep_tbl.count : '||to_char(l_data_dep_tbl.count));
1213 hr_utility.trace('l_data_dep_ind_tbl.count : '||to_char(l_data_dep_ind_tbl.count));
1214 end if;
1215 --
1216 l_data_dep_oe_cnt := 0;
1217 l_data_dep_oe_rowset_cnt := 0;
1218 open csr_data_dep_oe;
1219 loop
1220 --
1221 fetch csr_data_dep_oe into l_csr_data_dep_oe;
1222 exit when csr_data_dep_oe%notfound;
1223 --
1224 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_full_name := trim(l_csr_data_dep_oe.last_name||' '||l_csr_data_dep_oe.first_name);
1225 l_data_dep_oe_tbl(l_data_dep_oe_cnt).a_dep_oe_contact_type_b := l_csr_data_dep_oe.contact_type;
1226 l_data_dep_oe_tbl(l_data_dep_oe_cnt).a_dep_oe_birth_date := l_csr_data_dep_oe.date_of_birth;
1227 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_m := pay_jp_report_pkg.set_era_code(l_csr_data_dep_oe.date_of_birth,'M');
1228 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_t := pay_jp_report_pkg.set_era_code(l_csr_data_dep_oe.date_of_birth,'T');
1229 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_s := pay_jp_report_pkg.set_era_code(l_csr_data_dep_oe.date_of_birth,'S');
1230 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_dep_oe.date_of_birth,'H');
1231 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_dep_oe.date_of_birth,'FMEYY'),2,2);
1232 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_oe.date_of_birth,'FMMM');
1233 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_oe.date_of_birth,'FMDD');
1234 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_occupation := l_csr_data_dep_oe.occupation;
1235 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_address := l_csr_data_dep_oe.address;
1236 l_data_dep_oe_tbl(l_data_dep_oe_cnt).a_dep_oe_change_date := l_csr_data_dep_oe.change_date;
1237 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_change_date := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_oe.change_date,'FMEYY.MM.DD');
1238 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_change_reason := l_csr_data_dep_oe.change_reason;
1239 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_oe_full_name := l_csr_data_dep_oe.oe_full_name;
1240 l_data_dep_oe_tbl(l_data_dep_oe_cnt).a_dep_oe_oe_contact_type_b := l_csr_data_dep_oe.oe_contact_type;
1241 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_oe_address := l_csr_data_dep_oe.oe_address;
1242 --
1243 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_dep_oe.contact_type);
1244 if l_csr_data_dep_oe.contact_type = 'S' then
1245 --
1246 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_contact_type := c_husband;
1247 if l_csr_data_ass.sex = 'M' then
1248 --
1249 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_contact_type := c_wife;
1250 --
1251 end if;
1252 --
1253 end if;
1254 --
1255 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_oe_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_dep_oe.oe_contact_type);
1256 if l_csr_data_dep_oe.oe_contact_type = 'S' then
1257 --
1258 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_oe_contact_type := c_husband;
1259 if l_csr_data_ass.sex = 'M' then
1260 --
1261 l_data_dep_oe_tbl(l_data_dep_oe_cnt).o_dep_oe_oe_contact_type := c_wife;
1262 --
1263 end if;
1264 --
1265 end if;
1266 --
1267 l_data_dep_oe_cnt := l_data_dep_oe_cnt + 1;
1268 --
1269 if l_data_dep_oe_cnt > (l_data_dep_oe_rowset_cnt + 1) * c_dep_oe_row_cnt then
1270 --
1271 l_data_dep_oe_rowset_cnt := l_data_dep_oe_rowset_cnt + 1;
1272 --
1273 end if;
1274 --
1275 l_data_dep_oe_ind_tbl((l_data_dep_oe_rowset_cnt * 100) + (l_data_dep_oe_cnt - (l_data_dep_oe_rowset_cnt * c_dep_oe_row_cnt))) := l_data_dep_oe_tbl(l_data_dep_oe_cnt - 1);
1276 --
1277 end loop;
1278 close csr_data_dep_oe;
1279 --
1280 if l_data_dep_oe_cnt < (l_data_dep_oe_rowset_cnt + 1) * c_dep_oe_row_cnt then
1281 --
1282 for j in 1..((l_data_dep_oe_rowset_cnt + 1) * c_dep_oe_row_cnt - l_data_dep_oe_cnt) loop
1283 --
1284 l_data_dep_oe_ind_tbl((l_data_dep_oe_rowset_cnt * 100) + (l_data_dep_oe_cnt - (l_data_dep_oe_rowset_cnt * c_dep_oe_row_cnt)) + j) := l_data_dep_oe_dummy_rec;
1285 --
1286 end loop;
1287 --
1288 end if;
1289 --
1290 l_data_rowset_cnt := greatest(l_data_rowset_cnt,l_data_dep_oe_rowset_cnt);
1291 --
1292 if g_debug
1293 and g_detail_debug = 'Y' then
1294 hr_utility.set_location(l_proc,60);
1295 hr_utility.trace('l_data_dep_oe_cnt : '||to_char(l_data_dep_oe_cnt));
1296 hr_utility.trace('l_data_dep_oe_rowset_cnt : '||to_char(l_data_dep_oe_rowset_cnt));
1297 hr_utility.trace('l_data_rowset_cnt : '||to_char(l_data_rowset_cnt));
1298 hr_utility.trace('l_data_dep_oe_tbl.count : '||to_char(l_data_dep_oe_tbl.count));
1299 hr_utility.trace('l_data_dep_oe_ind_tbl.count : '||to_char(l_data_dep_oe_ind_tbl.count));
1300 end if;
1301 --
1302 if g_effective_date < c_st_upd_date_2011 then
1303 --
1304 l_data_dep_os_cnt := 0;
1305 l_data_dep_os_rowset_cnt := 0;
1306 open csr_data_dep_os;
1307 loop
1308 --
1309 fetch csr_data_dep_os into l_csr_data_dep_os;
1310 exit when csr_data_dep_os%notfound;
1311 --
1312 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_full_name := trim(l_csr_data_dep_os.last_name||' '||l_csr_data_dep_os.first_name);
1313 l_data_dep_os_tbl(l_data_dep_os_cnt).a_dep_os_contact_type_b := l_csr_data_dep_os.contact_type;
1314 l_data_dep_os_tbl(l_data_dep_os_cnt).a_dep_os_birth_date := l_csr_data_dep_os.date_of_birth;
1315 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_m := pay_jp_report_pkg.set_era_code(l_csr_data_dep_os.date_of_birth,'M');
1316 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_t := pay_jp_report_pkg.set_era_code(l_csr_data_dep_os.date_of_birth,'T');
1317 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_s := pay_jp_report_pkg.set_era_code(l_csr_data_dep_os.date_of_birth,'S');
1318 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_dep_os.date_of_birth,'H');
1319 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_dep_os.date_of_birth,'FMEYY'),2,2);
1320 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_os.date_of_birth,'FMMM');
1321 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_os.date_of_birth,'FMDD');
1322 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_occupation := l_csr_data_dep_os.occupation;
1323 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_os_sal_er_name := l_csr_data_dep_os.os_salary_payer_name;
1324 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_os_sal_er_address := l_csr_data_dep_os.os_salary_payer_address;
1325 --
1326 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_dep_os.contact_type);
1327 if l_csr_data_dep_os.contact_type = 'S' then
1328 --
1329 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_contact_type := c_husband;
1330 if l_csr_data_ass.sex = 'M' then
1331 --
1332 l_data_dep_os_tbl(l_data_dep_os_cnt).o_dep_os_contact_type := c_wife;
1333 --
1334 end if;
1335 --
1336 end if;
1337 --
1338 l_data_dep_os_cnt := l_data_dep_os_cnt + 1;
1339 --
1340 if l_data_dep_os_cnt > (l_data_dep_os_rowset_cnt + 1) * c_dep_os_row_cnt then
1341 --
1342 l_data_dep_os_rowset_cnt := l_data_dep_os_rowset_cnt + 1;
1343 --
1344 end if;
1345 --
1346 l_data_dep_os_ind_tbl((l_data_dep_os_rowset_cnt * 100) + (l_data_dep_os_cnt - (l_data_dep_os_rowset_cnt * c_dep_os_row_cnt))) := l_data_dep_os_tbl(l_data_dep_os_cnt - 1);
1347 --
1348 end loop;
1349 close csr_data_dep_os;
1350 --
1351 if l_data_dep_os_cnt < (l_data_dep_os_rowset_cnt + 1) * c_dep_os_row_cnt then
1352 --
1353 for k in 1..((l_data_dep_os_rowset_cnt + 1) * c_dep_os_row_cnt - l_data_dep_os_cnt) loop
1354 --
1355 l_data_dep_os_ind_tbl((l_data_dep_os_rowset_cnt * 100) + (l_data_dep_os_cnt - (l_data_dep_os_rowset_cnt * c_dep_os_row_cnt)) + k) := l_data_dep_os_dummy_rec;
1356 --
1357 end loop;
1358 --
1359 end if;
1360 --
1361 l_data_rowset_cnt := greatest(l_data_rowset_cnt,l_data_dep_os_rowset_cnt);
1362 --
1363 if g_debug
1364 and g_detail_debug = 'Y' then
1365 hr_utility.set_location(l_proc,70);
1366 hr_utility.trace('l_data_dep_os_cnt : '||to_char(l_data_dep_os_cnt));
1367 hr_utility.trace('l_data_dep_os_rowset_cnt : '||to_char(l_data_dep_os_rowset_cnt));
1368 hr_utility.trace('l_data_rowset_cnt : '||to_char(l_data_rowset_cnt));
1369 hr_utility.trace('l_data_dep_os_tbl.count : '||to_char(l_data_dep_os_tbl.count));
1370 hr_utility.trace('l_data_dep_os_ind_tbl.count : '||to_char(l_data_dep_os_ind_tbl.count));
1371 end if;
1372 --
1373 else
1374 --
1375 l_data_dep_ltx_cnt := 0;
1376 l_data_dep_ltx_rowset_cnt := 0;
1377 open csr_data_dep_ltx;
1378 loop
1379 --
1380 fetch csr_data_dep_ltx into l_csr_data_dep_ltx;
1381 exit when csr_data_dep_ltx%notfound;
1382 --
1383 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_full_name := trim(l_csr_data_dep_ltx.last_name||' '||l_csr_data_dep_ltx.first_name);
1384 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_contact_type_b := l_csr_data_dep_ltx.contact_type;
1385 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_birth_date := l_csr_data_dep_ltx.date_of_birth;
1386 --l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_birth_date_era_h := pay_jp_report_pkg.set_era_code(l_csr_data_dep_ltx.date_of_birth,'H');
1387 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_birth_date_era_yy := substrb(pay_jp_report_pkg.jp_era_date(l_csr_data_dep_ltx.date_of_birth,'FMEYY'),2,2);
1388 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_birth_date_era_mm := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_ltx.date_of_birth,'FMMM');
1389 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_birth_date_era_dd := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_ltx.date_of_birth,'FMDD');
1390 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_address := l_csr_data_dep_ltx.address;
1391 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_est_annual_inc := l_csr_data_dep_ltx.estimated_annual_income;
1392 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_change_date := l_csr_data_dep_ltx.change_date;
1393 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_change_date := pay_jp_report_pkg.jp_era_date(l_csr_data_dep_ltx.change_date,'FMEYY.MM.DD');
1394 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_change_reason := l_csr_data_dep_ltx.change_reason;
1395 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_disability_type_b := l_csr_data_dep_ltx.disability_type;
1396 --
1397 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_contact_type := hr_general.decode_lookup('CONTACT',l_csr_data_dep_ltx.contact_type);
1398 if l_csr_data_dep_ltx.contact_type = 'S' then
1399 --
1400 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_contact_type := c_husband;
1401 if l_csr_data_ass.sex = 'M' then
1402 --
1403 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_contact_type := c_wife;
1404 --
1405 end if;
1406 --
1407 end if;
1408 --
1409 -- cumulate for dep info
1410 if l_csr_data_dep_ltx.disability_type <> '0' then
1411 --
1412 l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_disability_details := l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).o_dep_ltx_full_name||
1413 '('||hr_general.decode_lookup('JP_DEF_DEP_DISABILITY_TYPE',l_csr_data_dep_ltx.disability_type)||
1414 ','||l_csr_data_dep_ltx.disability_details||')';
1415 --
1416 if l_csr_data_dep_ltx.disability_type = '10' then
1417 --
1418 l_body_rec.o_dep_disabled_flag := c_circle;
1419 l_body_rec.o_dep_disabled_cnt := nvl(l_body_rec.o_dep_disabled_cnt,0) + 1;
1420 --
1421 elsif l_csr_data_dep_ltx.disability_type = '20' then
1422 --
1423 l_body_rec.o_dep_svr_disabled_flag := c_circle;
1424 l_body_rec.o_dep_svr_disabled_cnt := nvl(l_body_rec.o_dep_svr_disabled_cnt,0) + 1;
1425 --
1426 elsif l_csr_data_dep_ltx.disability_type = '30' then
1427 --
1428 l_body_rec.o_dep_svr_disabled_lt_flag := c_circle;
1429 l_body_rec.o_dep_svr_disabled_lt_cnt := nvl(l_body_rec.o_dep_svr_disabled_lt_cnt,0) + 1;
1430 --
1431 end if;
1432 --
1433 l_body_rec.a_dep_ltx_disability_details := pay_jp_report_pkg.concatenate(l_body_rec.a_dep_ltx_disability_details,l_data_dep_ltx_tbl(l_data_dep_ltx_cnt).a_dep_ltx_disability_details);
1434 --
1435 end if;
1436 --
1437 l_data_dep_ltx_cnt := l_data_dep_ltx_cnt + 1;
1438 --
1439 if l_data_dep_ltx_cnt > (l_data_dep_ltx_rowset_cnt + 1) * c_dep_ltx_row_cnt then
1440 --
1441 l_data_dep_ltx_rowset_cnt := l_data_dep_ltx_rowset_cnt + 1;
1442 --
1443 end if;
1444 --
1445 l_data_dep_ltx_ind_tbl((l_data_dep_ltx_rowset_cnt * 100) + (l_data_dep_ltx_cnt - (l_data_dep_ltx_rowset_cnt * c_dep_ltx_row_cnt))) := l_data_dep_ltx_tbl(l_data_dep_ltx_cnt - 1);
1446 --
1447 end loop;
1448 close csr_data_dep_ltx;
1449 --
1450 if l_data_dep_ltx_cnt < (l_data_dep_ltx_rowset_cnt + 1) * c_dep_ltx_row_cnt then
1451 --
1452 for l in 1..((l_data_dep_ltx_rowset_cnt + 1) * c_dep_ltx_row_cnt - l_data_dep_ltx_cnt) loop
1453 --
1454 l_data_dep_ltx_ind_tbl((l_data_dep_ltx_rowset_cnt * 100) + (l_data_dep_ltx_cnt - (l_data_dep_ltx_rowset_cnt * c_dep_ltx_row_cnt)) + l) := l_data_dep_ltx_dummy_rec;
1455 --
1456 end loop;
1457 --
1458 end if;
1459 --
1460 l_body_rec.o_details := pay_jp_report_pkg.concatenate(l_body_rec.o_details,l_body_rec.a_dep_ltx_disability_details);
1461 l_data_rowset_cnt := greatest(l_data_rowset_cnt,l_data_dep_ltx_rowset_cnt);
1462 --
1463 if g_debug
1464 and g_detail_debug = 'Y' then
1465 hr_utility.set_location(l_proc,80);
1466 hr_utility.trace('l_data_dep_ltx_cnt : '||to_char(l_data_dep_ltx_cnt));
1467 hr_utility.trace('l_data_dep_ltx_rowset_cnt : '||to_char(l_data_dep_ltx_rowset_cnt));
1468 hr_utility.trace('l_data_rowset_cnt : '||to_char(l_data_rowset_cnt));
1469 hr_utility.trace('l_data_dep_ltx_tbl.count : '||to_char(l_data_dep_ltx_tbl.count));
1470 hr_utility.trace('l_data_dep_ltx_ind_tbl.count : '||to_char(l_data_dep_ltx_ind_tbl.count));
1471 end if;
1472 --
1473 end if;
1474 --
1475 -- -----------------------------------------------------
1476 -- construct xml
1477 -- -----------------------------------------------------
1478 if g_debug
1479 and g_detail_debug = 'Y'
1480 and g_show_tag = 'Y' then
1481 --
1482 for m in 0..l_data_rowset_cnt loop
1483 --
1484 -- first page
1485 if m = 0 then
1486 --
1487 hr_utility.trace(pay_jp_report_pkg.add_tag ('x1' ,l_body_rec.o_effective_date_eeyy ));
1488 hr_utility.trace(pay_jp_report_pkg.add_tag ('x2' ,l_body_rec.o_effective_date_eeyy ));
1489 hr_utility.trace(pay_jp_report_pkg.add_tag ('x3' ,l_body_rec.o_effective_date_eeyy ));
1490 hr_utility.trace(pay_jp_report_pkg.add_tag ('x4' ,l_body_rec.o_effective_date_eeyy ));
1491 hr_utility.trace(pay_jp_report_pkg.add_tag ('x5' ,l_body_rec.o_effective_date_eeyy ));
1492 hr_utility.trace(pay_jp_report_pkg.add_tag ('x6' ,l_body_rec.o_effective_date_eeyy ));
1493 hr_utility.trace(pay_jp_report_pkg.add_tag ('e1' ,l_body_rec.o_employee_number ));
1494 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e2' ,l_body_rec.o_tax_office_name ));
1495 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e3' ,l_body_rec.o_district_name ));
1496 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e4' ,l_body_rec.o_itax_org_name ));
1497 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e5' ,l_body_rec.o_itax_org_address ));
1498 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e6' ,l_body_rec.o_full_name_kana ));
1499 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e7' ,l_body_rec.o_full_name ));
1500 hr_utility.trace(pay_jp_report_pkg.add_tag ('e8' ,l_body_rec.o_birth_date_era_m ));
1501 hr_utility.trace(pay_jp_report_pkg.add_tag ('e9' ,l_body_rec.o_birth_date_era_t ));
1502 hr_utility.trace(pay_jp_report_pkg.add_tag ('e10' ,l_body_rec.o_birth_date_era_s ));
1503 hr_utility.trace(pay_jp_report_pkg.add_tag ('e11' ,l_body_rec.o_birth_date_era_h ));
1504 hr_utility.trace(pay_jp_report_pkg.add_tag ('e12' ,l_body_rec.o_birth_date_era_yy ));
1505 hr_utility.trace(pay_jp_report_pkg.add_tag ('e13' ,l_body_rec.o_birth_date_era_mm ));
1506 hr_utility.trace(pay_jp_report_pkg.add_tag ('e14' ,l_body_rec.o_birth_date_era_dd ));
1507 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e15' ,l_body_rec.o_household_head_full_name ));
1508 hr_utility.trace(pay_jp_report_pkg.add_tag ('e16' ,l_body_rec.o_household_head_contact_type));
1509 hr_utility.trace(pay_jp_report_pkg.add_tag ('e17' ,l_body_rec.o_postal_code1 ));
1510 hr_utility.trace(pay_jp_report_pkg.add_tag ('e18' ,l_body_rec.o_postal_code2 ));
1511 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e19' ,l_body_rec.o_address ));
1512 hr_utility.trace(pay_jp_report_pkg.add_tag ('e20' ,l_body_rec.o_spouse_exist_y ));
1513 hr_utility.trace(pay_jp_report_pkg.add_tag ('e21' ,l_body_rec.o_spouse_exist_n ));
1514 hr_utility.trace(pay_jp_report_pkg.add_tag ('e22' ,l_body_rec.o_sec_sal_submit_flag ));
1515 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed1' ,l_body_rec.o_emp_disabled_flag ));
1516 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed2' ,l_body_rec.o_emp_svr_disabled_flag ));
1517 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed3' ,l_body_rec.o_emp_aged_flag ));
1518 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed4' ,l_body_rec.o_emp_widow_flag ));
1519 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed5' ,l_body_rec.o_emp_sp_widow_flag ));
1520 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed6' ,l_body_rec.o_emp_widower_flag ));
1521 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed7' ,l_body_rec.o_emp_working_st_flag ));
1522 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ed8' ,l_body_rec.o_details ));
1523 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed9' ,l_body_rec.o_emp_change_date ));
1524 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ed10' ,l_body_rec.o_emp_change_reason ));
1525 hr_utility.trace(pay_jp_report_pkg.add_tag_v('s1' ,l_body_rec.o_sp_full_name ));
1526 hr_utility.trace(pay_jp_report_pkg.add_tag ('s2' ,l_body_rec.o_sp_birth_date_era_m ));
1527 hr_utility.trace(pay_jp_report_pkg.add_tag ('s3' ,l_body_rec.o_sp_birth_date_era_t ));
1528 hr_utility.trace(pay_jp_report_pkg.add_tag ('s4' ,l_body_rec.o_sp_birth_date_era_s ));
1529 hr_utility.trace(pay_jp_report_pkg.add_tag ('s5' ,l_body_rec.o_sp_birth_date_era_h ));
1530 hr_utility.trace(pay_jp_report_pkg.add_tag ('s6' ,l_body_rec.o_sp_birth_date_era_yy ));
1531 hr_utility.trace(pay_jp_report_pkg.add_tag ('s7' ,l_body_rec.o_sp_birth_date_era_mm ));
1532 hr_utility.trace(pay_jp_report_pkg.add_tag ('s8' ,l_body_rec.o_sp_birth_date_era_dd ));
1533 hr_utility.trace(pay_jp_report_pkg.add_tag ('s9' ,l_body_rec.o_sp_aged_flag ));
1534 hr_utility.trace(pay_jp_report_pkg.add_tag ('s10' ,l_body_rec.o_sp_occupation ));
1535 hr_utility.trace(pay_jp_report_pkg.add_tag ('s11' ,l_body_rec.o_sp_address ));
1536 hr_utility.trace(pay_jp_report_pkg.add_tag_m('s12' ,l_body_rec.o_sp_est_annual_inc ));
1537 hr_utility.trace(pay_jp_report_pkg.add_tag ('s13' ,l_body_rec.o_sp_change_date ));
1538 hr_utility.trace(pay_jp_report_pkg.add_tag ('s14' ,l_body_rec.o_sp_change_reason ));
1539 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd1' ,l_body_rec.o_sp_disabled_flag ));
1540 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd2' ,l_body_rec.o_sp_svr_disabled_flag ));
1541 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd3' ,l_body_rec.o_sp_svr_disabled_lt_flag ));
1542 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd1' ,l_body_rec.o_dep_disabled_flag ));
1543 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd2' ,l_body_rec.o_dep_disabled_cnt ));
1544 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd3' ,l_body_rec.o_dep_svr_disabled_flag ));
1545 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd4' ,l_body_rec.o_dep_svr_disabled_cnt ));
1546 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd5' ,l_body_rec.o_dep_svr_disabled_lt_flag ));
1547 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd6' ,l_body_rec.o_dep_svr_disabled_lt_cnt ));
1548 --
1549 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae1' ,l_body_rec.a_birth_date ));
1550 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae2' ,l_body_rec.a_sex_b ));
1551 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae3' ,l_body_rec.a_household_head_con_type_b ));
1552 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae4' ,l_body_rec.a_postal_code ));
1553 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae5' ,l_body_rec.a_married_flag ));
1554 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae6' ,l_body_rec.a_sec_sal_submit_flag ));
1555 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed1' ,l_body_rec.a_emp_disability_type_b ));
1556 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed2' ,l_body_rec.a_emp_aged_type_b ));
1557 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed3' ,l_body_rec.a_emp_widow_type_b ));
1558 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed4' ,l_body_rec.a_emp_working_st_type_b ));
1559 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed5' ,l_body_rec.a_emp_disability_details ));
1560 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed6' ,l_body_rec.a_emp_aged_details ));
1561 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed7' ,l_body_rec.a_emp_widow_details ));
1562 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed8' ,l_body_rec.a_emp_working_st_details ));
1563 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed9' ,l_body_rec.a_emp_change_date ));
1564 --hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed10',l_body_rec.a_dep_disability_details ));
1565 hr_utility.trace(pay_jp_report_pkg.add_tag ('as1' ,l_body_rec.a_sp_birth_date ));
1566 hr_utility.trace(pay_jp_report_pkg.add_tag ('as2' ,l_body_rec.a_sp_dep_type_b ));
1567 hr_utility.trace(pay_jp_report_pkg.add_tag ('as3' ,l_body_rec.a_sp_change_date ));
1568 hr_utility.trace(pay_jp_report_pkg.add_tag ('as4' ,l_body_rec.a_sp_disability_type_b ));
1569 hr_utility.trace(pay_jp_report_pkg.add_tag_v('asd1' ,l_body_rec.a_sp_disability_details ));
1570 --
1571 for n in 1..c_dep_row_cnt loop
1572 --
1573 l_data_dep_rec := l_data_dep_dummy_rec;
1574 if m <= l_data_dep_rowset_cnt then
1575 --
1576 l_data_dep_rec := l_data_dep_ind_tbl(m * 100 + n);
1577 --
1578 end if;
1579 --
1580 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 1 ),l_data_dep_rec.o_dep_full_name ));
1581 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 2 ),l_data_dep_rec.o_dep_contact_type ));
1582 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 3 ),l_data_dep_rec.o_dep_birth_date_era_m ));
1583 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 4 ),l_data_dep_rec.o_dep_birth_date_era_t ));
1584 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 5 ),l_data_dep_rec.o_dep_birth_date_era_s ));
1585 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 6 ),l_data_dep_rec.o_dep_birth_date_era_h ));
1586 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 7 ),l_data_dep_rec.o_dep_birth_date_era_yy ));
1587 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 8 ),l_data_dep_rec.o_dep_birth_date_era_mm ));
1588 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 9 ),l_data_dep_rec.o_dep_birth_date_era_dd ));
1589 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 10),l_data_dep_rec.o_dep_aged_prnt_lt_flag ));
1590 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 11),l_data_dep_rec.o_dep_aged_flag ));
1591 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 12),l_data_dep_rec.o_dep_sp_flag ));
1592 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 13),l_data_dep_rec.o_dep_occupation ));
1593 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 14),l_data_dep_rec.o_dep_address ));
1594 hr_utility.trace(pay_jp_report_pkg.add_tag_m('d' ||to_char(n * 100 + 15),l_data_dep_rec.o_dep_est_annual_inc ));
1595 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 16),l_data_dep_rec.o_dep_change_date ));
1596 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 17),l_data_dep_rec.o_dep_change_reason ));
1597 --
1598 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 1 ),l_data_dep_rec.a_dep_contact_type_b ));
1599 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 2 ),l_data_dep_rec.a_dep_birth_date ));
1600 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 3 ),l_data_dep_rec.a_dep_dep_type_b ));
1601 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 4 ),l_data_dep_rec.a_dep_change_date ));
1602 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 5 ),l_data_dep_rec.a_dep_disability_type_b ));
1603 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ad'||to_char(n * 100 + 6 ),l_data_dep_rec.a_dep_disability_details));
1604 --
1605 end loop;
1606 --
1607 for o in 1..c_dep_oe_row_cnt loop
1608 --
1609 l_data_dep_oe_rec := l_data_dep_oe_dummy_rec;
1610 if m <= l_data_dep_oe_rowset_cnt then
1611 --
1612 l_data_dep_oe_rec := l_data_dep_oe_ind_tbl(m * 100 + o);
1613 --
1614 end if;
1615 --
1616 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 1 ),l_data_dep_oe_rec.o_dep_oe_full_name ));
1617 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 2 ),l_data_dep_oe_rec.o_dep_oe_contact_type ));
1618 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 3 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_m ));
1619 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 4 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_t ));
1620 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 5 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_s ));
1621 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 6 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_h ));
1622 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 7 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_yy));
1623 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 8 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_mm));
1624 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 9 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_dd));
1625 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 10),l_data_dep_oe_rec.o_dep_oe_occupation ));
1626 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 11),l_data_dep_oe_rec.o_dep_oe_address ));
1627 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 12),l_data_dep_oe_rec.o_dep_oe_change_date ));
1628 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 13),l_data_dep_oe_rec.o_dep_oe_change_reason ));
1629 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 14),l_data_dep_oe_rec.o_dep_oe_oe_full_name ));
1630 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 15),l_data_dep_oe_rec.o_dep_oe_oe_contact_type ));
1631 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 16),l_data_dep_oe_rec.o_dep_oe_oe_address ));
1632 --
1633 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 1 ),l_data_dep_oe_rec.a_dep_oe_contact_type_b ));
1634 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 2 ),l_data_dep_oe_rec.a_dep_oe_birth_date ));
1635 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 3 ),l_data_dep_oe_rec.a_dep_oe_change_date ));
1636 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 4 ),l_data_dep_oe_rec.a_dep_oe_oe_contact_type_b));
1637 --
1638 end loop;
1639 --
1640 if g_effective_date < c_st_upd_date_2011 then
1641 --
1642 for p in 1..c_dep_os_row_cnt loop
1643 --
1644 l_data_dep_os_rec := l_data_dep_os_dummy_rec;
1645 if m <= l_data_dep_os_rowset_cnt then
1646 --
1647 l_data_dep_os_rec := l_data_dep_os_ind_tbl(m * 100 + p);
1648 --
1649 end if;
1650 --
1651 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 1 ),l_data_dep_os_rec.o_dep_os_full_name ));
1652 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 2 ),l_data_dep_os_rec.o_dep_os_contact_type ));
1653 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 3 ),l_data_dep_os_rec.o_dep_os_birth_date_era_m ));
1654 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 4 ),l_data_dep_os_rec.o_dep_os_birth_date_era_t ));
1655 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 5 ),l_data_dep_os_rec.o_dep_os_birth_date_era_s ));
1656 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 6 ),l_data_dep_os_rec.o_dep_os_birth_date_era_h ));
1657 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 7 ),l_data_dep_os_rec.o_dep_os_birth_date_era_yy));
1658 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 8 ),l_data_dep_os_rec.o_dep_os_birth_date_era_mm));
1659 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 9 ),l_data_dep_os_rec.o_dep_os_birth_date_era_dd));
1660 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 10),l_data_dep_os_rec.o_dep_os_occupation ));
1661 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 11),l_data_dep_os_rec.o_dep_os_os_sal_er_name ));
1662 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 12),l_data_dep_os_rec.o_dep_os_os_sal_er_address));
1663 --
1664 hr_utility.trace(pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 1 ),l_data_dep_os_rec.a_dep_os_contact_type_b ));
1665 hr_utility.trace(pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 2 ),l_data_dep_os_rec.a_dep_os_birth_date ));
1666 --
1667 end loop;
1668 --
1669 else
1670 --
1671 for q in 1..c_dep_ltx_row_cnt loop
1672 --
1673 l_data_dep_ltx_rec := l_data_dep_ltx_dummy_rec;
1674 if m <= l_data_dep_ltx_rowset_cnt then
1675 --
1676 l_data_dep_ltx_rec := l_data_dep_ltx_ind_tbl(m * 100 + q);
1677 --
1678 end if;
1679 --
1680 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.o_dep_ltx_full_name ));
1681 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_contact_type ));
1682 --hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_h ));
1683 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_yy ));
1684 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_mm ));
1685 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_dd ));
1686 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 6 ),l_data_dep_ltx_rec.o_dep_ltx_address ));
1687 hr_utility.trace(pay_jp_report_pkg.add_tag_m('dl' ||to_char(q * 100 + 7 ),l_data_dep_ltx_rec.o_dep_ltx_est_annual_inc ));
1688 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 8 ),l_data_dep_ltx_rec.o_dep_ltx_change_date ));
1689 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 9 ),l_data_dep_ltx_rec.o_dep_ltx_change_reason ));
1690 --
1691 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.a_dep_ltx_contact_type_b ));
1692 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.a_dep_ltx_birth_date ));
1693 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.a_dep_ltx_change_date ));
1694 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.a_dep_ltx_disability_type_b ));
1695 hr_utility.trace(pay_jp_report_pkg.add_tag_v('adl'||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.a_dep_ltx_disability_details));
1696 --
1697 end loop;
1698 --
1699 end if;
1700 --
1701 -- subsequent page by over records
1702 else
1703 --
1704 hr_utility.trace(pay_jp_report_pkg.add_tag ('x1' ,l_body_rec.o_effective_date_eeyy ));
1705 hr_utility.trace(pay_jp_report_pkg.add_tag ('x2' ,l_body_rec.o_effective_date_eeyy ));
1706 hr_utility.trace(pay_jp_report_pkg.add_tag ('x3' ,l_body_rec.o_effective_date_eeyy ));
1707 hr_utility.trace(pay_jp_report_pkg.add_tag ('x4' ,l_body_rec.o_effective_date_eeyy ));
1708 hr_utility.trace(pay_jp_report_pkg.add_tag ('x5' ,l_body_rec.o_effective_date_eeyy ));
1709 hr_utility.trace(pay_jp_report_pkg.add_tag ('x6' ,l_body_rec.o_effective_date_eeyy ));
1710 hr_utility.trace(pay_jp_report_pkg.add_tag ('e1' ,l_body_rec.o_employee_number ));
1711 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e2' ,l_body_rec.o_tax_office_name ));
1712 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e3' ,l_body_rec.o_district_name ));
1713 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e4' ,l_body_rec.o_itax_org_name ));
1714 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e5' ,l_body_rec.o_itax_org_address ));
1715 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e6' ,l_body_rec.o_full_name_kana ));
1716 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e7' ,l_body_rec.o_full_name ));
1717 hr_utility.trace(pay_jp_report_pkg.add_tag ('e8' ,l_body_rec.o_birth_date_era_m ));
1718 hr_utility.trace(pay_jp_report_pkg.add_tag ('e9' ,l_body_rec.o_birth_date_era_t ));
1719 hr_utility.trace(pay_jp_report_pkg.add_tag ('e10' ,l_body_rec.o_birth_date_era_s ));
1720 hr_utility.trace(pay_jp_report_pkg.add_tag ('e11' ,l_body_rec.o_birth_date_era_h ));
1721 hr_utility.trace(pay_jp_report_pkg.add_tag ('e12' ,l_body_rec.o_birth_date_era_yy ));
1722 hr_utility.trace(pay_jp_report_pkg.add_tag ('e13' ,l_body_rec.o_birth_date_era_mm ));
1723 hr_utility.trace(pay_jp_report_pkg.add_tag ('e14' ,l_body_rec.o_birth_date_era_dd ));
1724 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e15' ,l_body_rec.o_household_head_full_name ));
1725 hr_utility.trace(pay_jp_report_pkg.add_tag ('e16' ,l_body_rec.o_household_head_contact_type));
1726 hr_utility.trace(pay_jp_report_pkg.add_tag ('e17' ,l_body_rec.o_postal_code1 ));
1727 hr_utility.trace(pay_jp_report_pkg.add_tag ('e18' ,l_body_rec.o_postal_code2 ));
1728 hr_utility.trace(pay_jp_report_pkg.add_tag_v('e19' ,l_body_rec.o_address ));
1729 hr_utility.trace(pay_jp_report_pkg.add_tag ('e20' ,l_body_rec.o_spouse_exist_y ));
1730 hr_utility.trace(pay_jp_report_pkg.add_tag ('e21' ,l_body_rec.o_spouse_exist_n ));
1731 hr_utility.trace(pay_jp_report_pkg.add_tag ('e22' ,l_body_rec.o_sec_sal_submit_flag ));
1732 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed1' ,to_char(null) ));
1733 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed2' ,to_char(null) ));
1734 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed3' ,to_char(null) ));
1735 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed4' ,to_char(null) ));
1736 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed5' ,to_char(null) ));
1737 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed6' ,to_char(null) ));
1738 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed7' ,to_char(null) ));
1739 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ed8' ,to_char(null) ));
1740 hr_utility.trace(pay_jp_report_pkg.add_tag ('ed9' ,to_char(null) ));
1741 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ed10' ,to_char(null) ));
1742 hr_utility.trace(pay_jp_report_pkg.add_tag_v('s1' ,to_char(null) ));
1743 hr_utility.trace(pay_jp_report_pkg.add_tag ('s2' ,to_char(null) ));
1744 hr_utility.trace(pay_jp_report_pkg.add_tag ('s3' ,to_char(null) ));
1745 hr_utility.trace(pay_jp_report_pkg.add_tag ('s4' ,to_char(null) ));
1746 hr_utility.trace(pay_jp_report_pkg.add_tag ('s5' ,to_char(null) ));
1747 hr_utility.trace(pay_jp_report_pkg.add_tag ('s6' ,to_char(null) ));
1748 hr_utility.trace(pay_jp_report_pkg.add_tag ('s7' ,to_char(null) ));
1749 hr_utility.trace(pay_jp_report_pkg.add_tag ('s8' ,to_char(null) ));
1750 hr_utility.trace(pay_jp_report_pkg.add_tag ('s9' ,to_char(null) ));
1751 hr_utility.trace(pay_jp_report_pkg.add_tag ('s10' ,to_char(null) ));
1752 hr_utility.trace(pay_jp_report_pkg.add_tag ('s11' ,to_char(null) ));
1753 hr_utility.trace(pay_jp_report_pkg.add_tag_m('s12' ,to_number(null) ));
1754 hr_utility.trace(pay_jp_report_pkg.add_tag ('s13' ,to_char(null) ));
1755 hr_utility.trace(pay_jp_report_pkg.add_tag ('s14' ,to_char(null) ));
1756 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd1' ,to_char(null) ));
1757 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd2' ,to_char(null) ));
1758 hr_utility.trace(pay_jp_report_pkg.add_tag ('sd3' ,to_char(null) ));
1759 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd1' ,to_char(null) ));
1760 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd2' ,to_number(null) ));
1761 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd3' ,to_char(null) ));
1762 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd4' ,to_number(null) ));
1763 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd5' ,to_char(null) ));
1764 hr_utility.trace(pay_jp_report_pkg.add_tag ('dd6' ,to_number(null) ));
1765 --
1766 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae1' ,l_body_rec.a_birth_date ));
1767 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae2' ,l_body_rec.a_sex_b ));
1768 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae3' ,l_body_rec.a_household_head_con_type_b ));
1769 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae4' ,l_body_rec.a_postal_code ));
1770 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae5' ,l_body_rec.a_married_flag ));
1771 hr_utility.trace(pay_jp_report_pkg.add_tag ('ae6' ,l_body_rec.a_sec_sal_submit_flag ));
1772 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed1' ,to_char(null) ));
1773 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed2' ,to_char(null) ));
1774 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed3' ,to_char(null) ));
1775 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed4' ,to_char(null) ));
1776 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed5' ,to_char(null) ));
1777 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed6' ,to_char(null) ));
1778 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed7' ,to_char(null) ));
1779 hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed8' ,to_char(null) ));
1780 hr_utility.trace(pay_jp_report_pkg.add_tag ('aed9' ,to_date(null) ));
1781 --hr_utility.trace(pay_jp_report_pkg.add_tag_v('aed10',to_char(null) ));
1782 hr_utility.trace(pay_jp_report_pkg.add_tag ('as1' ,to_date(null) ));
1783 hr_utility.trace(pay_jp_report_pkg.add_tag ('as2' ,to_char(null) ));
1784 hr_utility.trace(pay_jp_report_pkg.add_tag ('as3' ,to_date(null) ));
1785 hr_utility.trace(pay_jp_report_pkg.add_tag ('as4' ,to_char(null) ));
1786 hr_utility.trace(pay_jp_report_pkg.add_tag_v('asd1' ,to_char(null) ));
1787 --
1788 for n in 1..c_dep_row_cnt loop
1789 --
1790 l_data_dep_rec := l_data_dep_dummy_rec;
1791 if m <= l_data_dep_rowset_cnt then
1792 --
1793 l_data_dep_rec := l_data_dep_ind_tbl(m * 100 + n);
1794 --
1795 end if;
1796 --
1797 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 1 ),l_data_dep_rec.o_dep_full_name ));
1798 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 2 ),l_data_dep_rec.o_dep_contact_type ));
1799 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 3 ),l_data_dep_rec.o_dep_birth_date_era_m ));
1800 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 4 ),l_data_dep_rec.o_dep_birth_date_era_t ));
1801 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 5 ),l_data_dep_rec.o_dep_birth_date_era_s ));
1802 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 6 ),l_data_dep_rec.o_dep_birth_date_era_h ));
1803 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 7 ),l_data_dep_rec.o_dep_birth_date_era_yy ));
1804 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 8 ),l_data_dep_rec.o_dep_birth_date_era_mm ));
1805 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 9 ),l_data_dep_rec.o_dep_birth_date_era_dd ));
1806 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 10),l_data_dep_rec.o_dep_aged_prnt_lt_flag ));
1807 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 11),l_data_dep_rec.o_dep_aged_flag ));
1808 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 12),l_data_dep_rec.o_dep_sp_flag ));
1809 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 13),l_data_dep_rec.o_dep_occupation ));
1810 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 14),l_data_dep_rec.o_dep_address ));
1811 hr_utility.trace(pay_jp_report_pkg.add_tag_m('d' ||to_char(n * 100 + 15),l_data_dep_rec.o_dep_est_annual_inc ));
1812 hr_utility.trace(pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 16),l_data_dep_rec.o_dep_change_date ));
1813 hr_utility.trace(pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 17),l_data_dep_rec.o_dep_change_reason ));
1814 --
1815 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 1 ),l_data_dep_rec.a_dep_contact_type_b ));
1816 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 2 ),l_data_dep_rec.a_dep_birth_date ));
1817 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 3 ),l_data_dep_rec.a_dep_dep_type_b ));
1818 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 4 ),l_data_dep_rec.a_dep_change_date ));
1819 hr_utility.trace(pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 5 ),l_data_dep_rec.a_dep_disability_type_b ));
1820 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ad'||to_char(n * 100 + 6 ),l_data_dep_rec.a_dep_disability_details));
1821 --
1822 end loop;
1823 --
1824 for o in 1..c_dep_oe_row_cnt loop
1825 --
1826 l_data_dep_oe_rec := l_data_dep_oe_dummy_rec;
1827 if m <= l_data_dep_oe_rowset_cnt then
1828 --
1829 l_data_dep_oe_rec := l_data_dep_oe_ind_tbl(m * 100 + o);
1830 --
1831 end if;
1832 --
1833 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 1 ),l_data_dep_oe_rec.o_dep_oe_full_name ));
1834 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 2 ),l_data_dep_oe_rec.o_dep_oe_contact_type ));
1835 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 3 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_m ));
1836 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 4 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_t ));
1837 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 5 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_s ));
1838 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 6 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_h ));
1839 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 7 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_yy));
1840 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 8 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_mm));
1841 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 9 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_dd));
1842 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 10),l_data_dep_oe_rec.o_dep_oe_occupation ));
1843 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 11),l_data_dep_oe_rec.o_dep_oe_address ));
1844 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 12),l_data_dep_oe_rec.o_dep_oe_change_date ));
1845 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 13),l_data_dep_oe_rec.o_dep_oe_change_reason ));
1846 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 14),l_data_dep_oe_rec.o_dep_oe_oe_full_name ));
1847 hr_utility.trace(pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 15),l_data_dep_oe_rec.o_dep_oe_oe_contact_type ));
1848 hr_utility.trace(pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 16),l_data_dep_oe_rec.o_dep_oe_oe_address ));
1849 --
1850 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 1 ),l_data_dep_oe_rec.a_dep_oe_contact_type_b ));
1851 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 2 ),l_data_dep_oe_rec.a_dep_oe_birth_date ));
1852 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 3 ),l_data_dep_oe_rec.a_dep_oe_change_date ));
1853 hr_utility.trace(pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 4 ),l_data_dep_oe_rec.a_dep_oe_oe_contact_type_b));
1854 --
1855 end loop;
1856 --
1857 if g_effective_date < c_st_upd_date_2011 then
1858 --
1859 for p in 1..c_dep_os_row_cnt loop
1860 --
1861 l_data_dep_os_rec := l_data_dep_os_dummy_rec;
1862 if m <= l_data_dep_os_rowset_cnt then
1863 --
1864 l_data_dep_os_rec := l_data_dep_os_ind_tbl(m * 100 + p);
1865 --
1866 end if;
1867 --
1868 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 1 ),l_data_dep_os_rec.o_dep_os_full_name ));
1869 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 2 ),l_data_dep_os_rec.o_dep_os_contact_type ));
1870 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 3 ),l_data_dep_os_rec.o_dep_os_birth_date_era_m ));
1871 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 4 ),l_data_dep_os_rec.o_dep_os_birth_date_era_t ));
1872 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 5 ),l_data_dep_os_rec.o_dep_os_birth_date_era_s ));
1873 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 6 ),l_data_dep_os_rec.o_dep_os_birth_date_era_h ));
1874 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 7 ),l_data_dep_os_rec.o_dep_os_birth_date_era_yy));
1875 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 8 ),l_data_dep_os_rec.o_dep_os_birth_date_era_mm));
1876 hr_utility.trace(pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 9 ),l_data_dep_os_rec.o_dep_os_birth_date_era_dd));
1877 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 10),l_data_dep_os_rec.o_dep_os_occupation ));
1878 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 11),l_data_dep_os_rec.o_dep_os_os_sal_er_name ));
1879 hr_utility.trace(pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 12),l_data_dep_os_rec.o_dep_os_os_sal_er_address));
1880 --
1881 hr_utility.trace(pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 1 ),l_data_dep_os_rec.a_dep_os_contact_type_b ));
1882 hr_utility.trace(pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 2 ),l_data_dep_os_rec.a_dep_os_birth_date ));
1883 --
1884 end loop;
1885 --
1886 else
1887 --
1888 for q in 1..c_dep_ltx_row_cnt loop
1889 --
1890 l_data_dep_ltx_rec := l_data_dep_ltx_dummy_rec;
1891 if m <= l_data_dep_ltx_rowset_cnt then
1892 --
1893 l_data_dep_ltx_rec := l_data_dep_ltx_ind_tbl(m * 100 + q);
1894 --
1895 end if;
1896 --
1897 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.o_dep_ltx_full_name ));
1898 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_contact_type ));
1899 --hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_h ));
1900 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_yy ));
1901 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_mm ));
1902 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_dd ));
1903 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 6 ),l_data_dep_ltx_rec.o_dep_ltx_address ));
1904 hr_utility.trace(pay_jp_report_pkg.add_tag_m('dl' ||to_char(q * 100 + 7 ),l_data_dep_ltx_rec.o_dep_ltx_est_annual_inc ));
1905 hr_utility.trace(pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 8 ),l_data_dep_ltx_rec.o_dep_ltx_change_date ));
1906 hr_utility.trace(pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 9 ),l_data_dep_ltx_rec.o_dep_ltx_change_reason ));
1907 --
1908 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.a_dep_ltx_contact_type_b ));
1909 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.a_dep_ltx_birth_date ));
1910 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.a_dep_ltx_change_date ));
1911 hr_utility.trace(pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.a_dep_ltx_disability_type_b ));
1912 hr_utility.trace(pay_jp_report_pkg.add_tag_v('adl'||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.a_dep_ltx_disability_details));
1913 --
1914 end loop;
1915 --
1916 end if;
1917 --
1918 end if;
1919 --
1920 end loop;
1921 --
1922 end if;
1923 --
1924 if g_debug
1925 and g_detail_debug = 'Y' then
1926 hr_utility.set_location(l_proc,90);
1927 end if;
1928 --
1929 for m in 0..l_data_rowset_cnt loop
1930 --
1931 if g_debug
1932 and g_detail_debug = 'Y' then
1933 hr_utility.set_location(l_proc,100);
1934 hr_utility.trace('m : '||to_char(m));
1935 end if;
1936 --
1937 -- first page
1938 if m = 0 then
1939 --
1940 if g_debug
1941 and g_detail_debug = 'Y' then
1942 hr_utility.set_location(l_proc,110);
1943 end if;
1944 --
1945 l_xml_assact :=
1946 '<g_emp>'||c_eol||
1947 pay_jp_report_pkg.add_tag ('x1' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1948 pay_jp_report_pkg.add_tag ('x2' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1949 pay_jp_report_pkg.add_tag ('x3' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1950 pay_jp_report_pkg.add_tag ('x4' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1951 pay_jp_report_pkg.add_tag ('x5' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1952 pay_jp_report_pkg.add_tag ('x6' ,l_body_rec.o_effective_date_eeyy )||c_eol||
1953 pay_jp_report_pkg.add_tag ('e1' ,l_body_rec.o_employee_number )||c_eol||
1954 pay_jp_report_pkg.add_tag_v('e2' ,l_body_rec.o_tax_office_name )||c_eol||
1955 pay_jp_report_pkg.add_tag_v('e3' ,l_body_rec.o_district_name )||c_eol||
1956 pay_jp_report_pkg.add_tag_v('e4' ,l_body_rec.o_itax_org_name )||c_eol||
1957 pay_jp_report_pkg.add_tag_v('e5' ,l_body_rec.o_itax_org_address )||c_eol||
1958 pay_jp_report_pkg.add_tag_v('e6' ,l_body_rec.o_full_name_kana )||c_eol||
1959 pay_jp_report_pkg.add_tag_v('e7' ,l_body_rec.o_full_name )||c_eol||
1960 pay_jp_report_pkg.add_tag ('e8' ,l_body_rec.o_birth_date_era_m )||c_eol||
1961 pay_jp_report_pkg.add_tag ('e9' ,l_body_rec.o_birth_date_era_t )||c_eol||
1962 pay_jp_report_pkg.add_tag ('e10' ,l_body_rec.o_birth_date_era_s )||c_eol||
1963 pay_jp_report_pkg.add_tag ('e11' ,l_body_rec.o_birth_date_era_h )||c_eol||
1964 pay_jp_report_pkg.add_tag ('e12' ,l_body_rec.o_birth_date_era_yy )||c_eol||
1965 pay_jp_report_pkg.add_tag ('e13' ,l_body_rec.o_birth_date_era_mm )||c_eol||
1966 pay_jp_report_pkg.add_tag ('e14' ,l_body_rec.o_birth_date_era_dd )||c_eol||
1967 pay_jp_report_pkg.add_tag_v('e15' ,l_body_rec.o_household_head_full_name )||c_eol||
1968 pay_jp_report_pkg.add_tag ('e16' ,l_body_rec.o_household_head_contact_type)||c_eol||
1969 pay_jp_report_pkg.add_tag ('e17' ,l_body_rec.o_postal_code1 )||c_eol||
1970 pay_jp_report_pkg.add_tag ('e18' ,l_body_rec.o_postal_code2 )||c_eol||
1971 pay_jp_report_pkg.add_tag_v('e19' ,l_body_rec.o_address )||c_eol||
1972 pay_jp_report_pkg.add_tag ('e20' ,l_body_rec.o_spouse_exist_y )||c_eol||
1973 pay_jp_report_pkg.add_tag ('e21' ,l_body_rec.o_spouse_exist_n )||c_eol||
1974 pay_jp_report_pkg.add_tag ('e22' ,l_body_rec.o_sec_sal_submit_flag )||c_eol||
1975 pay_jp_report_pkg.add_tag ('ed1' ,l_body_rec.o_emp_disabled_flag )||c_eol||
1976 pay_jp_report_pkg.add_tag ('ed2' ,l_body_rec.o_emp_svr_disabled_flag )||c_eol||
1977 pay_jp_report_pkg.add_tag ('ed3' ,l_body_rec.o_emp_aged_flag )||c_eol||
1978 pay_jp_report_pkg.add_tag ('ed4' ,l_body_rec.o_emp_widow_flag )||c_eol||
1979 pay_jp_report_pkg.add_tag ('ed5' ,l_body_rec.o_emp_sp_widow_flag )||c_eol||
1980 pay_jp_report_pkg.add_tag ('ed6' ,l_body_rec.o_emp_widower_flag )||c_eol||
1981 pay_jp_report_pkg.add_tag ('ed7' ,l_body_rec.o_emp_working_st_flag )||c_eol||
1982 pay_jp_report_pkg.add_tag_v('ed8' ,l_body_rec.o_details )||c_eol||
1983 pay_jp_report_pkg.add_tag ('ed9' ,l_body_rec.o_emp_change_date )||c_eol||
1984 pay_jp_report_pkg.add_tag_v('ed10' ,l_body_rec.o_emp_change_reason )||c_eol||
1985 pay_jp_report_pkg.add_tag_v('s1' ,l_body_rec.o_sp_full_name )||c_eol||
1986 pay_jp_report_pkg.add_tag ('s2' ,l_body_rec.o_sp_birth_date_era_m )||c_eol||
1987 pay_jp_report_pkg.add_tag ('s3' ,l_body_rec.o_sp_birth_date_era_t )||c_eol||
1988 pay_jp_report_pkg.add_tag ('s4' ,l_body_rec.o_sp_birth_date_era_s )||c_eol||
1989 pay_jp_report_pkg.add_tag ('s5' ,l_body_rec.o_sp_birth_date_era_h )||c_eol||
1990 pay_jp_report_pkg.add_tag ('s6' ,l_body_rec.o_sp_birth_date_era_yy )||c_eol||
1991 pay_jp_report_pkg.add_tag ('s7' ,l_body_rec.o_sp_birth_date_era_mm )||c_eol||
1992 pay_jp_report_pkg.add_tag ('s8' ,l_body_rec.o_sp_birth_date_era_dd )||c_eol||
1993 pay_jp_report_pkg.add_tag ('s9' ,l_body_rec.o_sp_aged_flag )||c_eol||
1994 pay_jp_report_pkg.add_tag ('s10' ,l_body_rec.o_sp_occupation )||c_eol||
1995 pay_jp_report_pkg.add_tag ('s11' ,l_body_rec.o_sp_address )||c_eol||
1996 pay_jp_report_pkg.add_tag_m('s12' ,l_body_rec.o_sp_est_annual_inc )||c_eol||
1997 pay_jp_report_pkg.add_tag ('s13' ,l_body_rec.o_sp_change_date )||c_eol||
1998 pay_jp_report_pkg.add_tag ('s14' ,l_body_rec.o_sp_change_reason )||c_eol||
1999 pay_jp_report_pkg.add_tag ('sd1' ,l_body_rec.o_sp_disabled_flag )||c_eol||
2000 pay_jp_report_pkg.add_tag ('sd2' ,l_body_rec.o_sp_svr_disabled_flag )||c_eol||
2001 pay_jp_report_pkg.add_tag ('sd3' ,l_body_rec.o_sp_svr_disabled_lt_flag )||c_eol||
2002 pay_jp_report_pkg.add_tag ('dd1' ,l_body_rec.o_dep_disabled_flag )||c_eol||
2003 pay_jp_report_pkg.add_tag ('dd2' ,l_body_rec.o_dep_disabled_cnt )||c_eol||
2004 pay_jp_report_pkg.add_tag ('dd3' ,l_body_rec.o_dep_svr_disabled_flag )||c_eol||
2005 pay_jp_report_pkg.add_tag ('dd4' ,l_body_rec.o_dep_svr_disabled_cnt )||c_eol||
2006 pay_jp_report_pkg.add_tag ('dd5' ,l_body_rec.o_dep_svr_disabled_lt_flag )||c_eol||
2007 pay_jp_report_pkg.add_tag ('dd6' ,l_body_rec.o_dep_svr_disabled_lt_cnt )||c_eol||
2008 pay_jp_report_pkg.add_tag ('ae1' ,l_body_rec.a_birth_date )||c_eol||
2009 pay_jp_report_pkg.add_tag ('ae2' ,l_body_rec.a_sex_b )||c_eol||
2010 pay_jp_report_pkg.add_tag ('ae3' ,l_body_rec.a_household_head_con_type_b )||c_eol||
2011 pay_jp_report_pkg.add_tag ('ae4' ,l_body_rec.a_postal_code )||c_eol||
2012 pay_jp_report_pkg.add_tag ('ae5' ,l_body_rec.a_married_flag )||c_eol||
2013 pay_jp_report_pkg.add_tag ('ae6' ,l_body_rec.a_sec_sal_submit_flag )||c_eol||
2014 pay_jp_report_pkg.add_tag ('aed1' ,l_body_rec.a_emp_disability_type_b )||c_eol||
2015 pay_jp_report_pkg.add_tag ('aed2' ,l_body_rec.a_emp_aged_type_b )||c_eol||
2016 pay_jp_report_pkg.add_tag ('aed3' ,l_body_rec.a_emp_widow_type_b )||c_eol||
2017 pay_jp_report_pkg.add_tag ('aed4' ,l_body_rec.a_emp_working_st_type_b )||c_eol||
2018 pay_jp_report_pkg.add_tag_v('aed5' ,l_body_rec.a_emp_disability_details )||c_eol||
2019 pay_jp_report_pkg.add_tag_v('aed6' ,l_body_rec.a_emp_aged_details )||c_eol||
2020 pay_jp_report_pkg.add_tag_v('aed7' ,l_body_rec.a_emp_widow_details )||c_eol||
2021 pay_jp_report_pkg.add_tag_v('aed8' ,l_body_rec.a_emp_working_st_details )||c_eol||
2022 pay_jp_report_pkg.add_tag ('aed9' ,l_body_rec.a_emp_change_date )||c_eol||
2023 --pay_jp_report_pkg.add_tag_v('aed10',l_body_rec.a_dep_disability_details )||c_eol||
2024 pay_jp_report_pkg.add_tag ('as1' ,l_body_rec.a_sp_birth_date )||c_eol||
2025 pay_jp_report_pkg.add_tag ('as2' ,l_body_rec.a_sp_dep_type_b )||c_eol||
2026 pay_jp_report_pkg.add_tag ('as3' ,l_body_rec.a_sp_change_date )||c_eol||
2027 pay_jp_report_pkg.add_tag ('as4' ,l_body_rec.a_sp_disability_type_b )||c_eol||
2028 pay_jp_report_pkg.add_tag_v('asd1' ,l_body_rec.a_sp_disability_details )||c_eol;
2029 --
2030 if g_debug
2031 and g_detail_debug = 'Y' then
2032 hr_utility.set_location(l_proc,120);
2033 end if;
2034 --
2035 l_xml_assact_temp := null;
2036 for n in 1..c_dep_row_cnt loop
2037 --
2038 l_data_dep_rec := l_data_dep_dummy_rec;
2039 if m <= l_data_dep_rowset_cnt then
2040 --
2041 l_data_dep_rec := l_data_dep_ind_tbl(m * 100 + n);
2042 --
2043 end if;
2044 --
2045 if g_debug
2046 and g_detail_debug = 'Y' then
2047 hr_utility.trace('n : '||to_char(n));
2048 end if;
2049 --
2050 l_xml_assact_temp := l_xml_assact_temp||
2051 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 1 ),l_data_dep_rec.o_dep_full_name )||c_eol||
2052 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 2 ),l_data_dep_rec.o_dep_contact_type )||c_eol||
2053 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 3 ),l_data_dep_rec.o_dep_birth_date_era_m )||c_eol||
2054 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 4 ),l_data_dep_rec.o_dep_birth_date_era_t )||c_eol||
2055 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 5 ),l_data_dep_rec.o_dep_birth_date_era_s )||c_eol||
2056 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 6 ),l_data_dep_rec.o_dep_birth_date_era_h )||c_eol||
2057 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 7 ),l_data_dep_rec.o_dep_birth_date_era_yy )||c_eol||
2058 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 8 ),l_data_dep_rec.o_dep_birth_date_era_mm )||c_eol||
2059 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 9 ),l_data_dep_rec.o_dep_birth_date_era_dd )||c_eol||
2060 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 10),l_data_dep_rec.o_dep_aged_prnt_lt_flag )||c_eol||
2061 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 11),l_data_dep_rec.o_dep_aged_flag )||c_eol||
2062 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 12),l_data_dep_rec.o_dep_sp_flag )||c_eol||
2063 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 13),l_data_dep_rec.o_dep_occupation )||c_eol||
2064 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 14),l_data_dep_rec.o_dep_address )||c_eol||
2065 pay_jp_report_pkg.add_tag_m('d' ||to_char(n * 100 + 15),l_data_dep_rec.o_dep_est_annual_inc )||c_eol||
2066 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 16),l_data_dep_rec.o_dep_change_date )||c_eol||
2067 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 17),l_data_dep_rec.o_dep_change_reason )||c_eol||
2068 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 1 ),l_data_dep_rec.a_dep_contact_type_b )||c_eol||
2069 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 2 ),l_data_dep_rec.a_dep_birth_date )||c_eol||
2070 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 3 ),l_data_dep_rec.a_dep_dep_type_b )||c_eol||
2071 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 4 ),l_data_dep_rec.a_dep_change_date )||c_eol||
2072 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 5 ),l_data_dep_rec.a_dep_disability_type_b )||c_eol||
2073 pay_jp_report_pkg.add_tag_v('ad'||to_char(n * 100 + 6 ),l_data_dep_rec.a_dep_disability_details)||c_eol;
2074 --
2075 end loop;
2076 --
2077 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2078 --
2079 if g_debug
2080 and g_detail_debug = 'Y' then
2081 hr_utility.set_location(l_proc,130);
2082 end if;
2083 --
2084 l_xml_assact_temp := null;
2085 for o in 1..c_dep_oe_row_cnt loop
2086 --
2087 l_data_dep_oe_rec := l_data_dep_oe_dummy_rec;
2088 if m <= l_data_dep_oe_rowset_cnt then
2089 --
2090 l_data_dep_oe_rec := l_data_dep_oe_ind_tbl(m * 100 + o);
2091 --
2092 end if;
2093 --
2094 if g_debug
2095 and g_detail_debug = 'Y' then
2096 hr_utility.trace('o : '||to_char(o));
2097 end if;
2098 --
2099 l_xml_assact_temp := l_xml_assact_temp||
2100 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 1 ),l_data_dep_oe_rec.o_dep_oe_full_name )||c_eol||
2101 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 2 ),l_data_dep_oe_rec.o_dep_oe_contact_type )||c_eol||
2102 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 3 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_m )||c_eol||
2103 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 4 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_t )||c_eol||
2104 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 5 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_s )||c_eol||
2105 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 6 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_h )||c_eol||
2106 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 7 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_yy)||c_eol||
2107 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 8 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_mm)||c_eol||
2108 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 9 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_dd)||c_eol||
2109 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 10),l_data_dep_oe_rec.o_dep_oe_occupation )||c_eol||
2110 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 11),l_data_dep_oe_rec.o_dep_oe_address )||c_eol||
2111 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 12),l_data_dep_oe_rec.o_dep_oe_change_date )||c_eol||
2112 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 13),l_data_dep_oe_rec.o_dep_oe_change_reason )||c_eol||
2113 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 14),l_data_dep_oe_rec.o_dep_oe_oe_full_name )||c_eol||
2114 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 15),l_data_dep_oe_rec.o_dep_oe_oe_contact_type )||c_eol||
2115 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 16),l_data_dep_oe_rec.o_dep_oe_oe_address )||c_eol||
2116 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 1 ),l_data_dep_oe_rec.a_dep_oe_contact_type_b )||c_eol||
2117 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 2 ),l_data_dep_oe_rec.a_dep_oe_birth_date )||c_eol||
2118 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 3 ),l_data_dep_oe_rec.a_dep_oe_change_date )||c_eol||
2119 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 4 ),l_data_dep_oe_rec.a_dep_oe_oe_contact_type_b)||c_eol;
2120 --
2121 end loop;
2122 --
2123 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2124 --
2125 if g_debug
2126 and g_detail_debug = 'Y' then
2127 hr_utility.set_location(l_proc,140);
2128 end if;
2129 --
2130 if g_effective_date < c_st_upd_date_2011 then
2131 --
2132 l_xml_assact_temp := null;
2133 for p in 1..c_dep_os_row_cnt loop
2134 --
2135 l_data_dep_os_rec := l_data_dep_os_dummy_rec;
2136 if m <= l_data_dep_os_rowset_cnt then
2137 --
2138 l_data_dep_os_rec := l_data_dep_os_ind_tbl(m * 100 + p);
2139 --
2140 end if;
2141 --
2142 if g_debug
2143 and g_detail_debug = 'Y' then
2144 hr_utility.trace('p : '||to_char(p));
2145 end if;
2146 --
2147 l_xml_assact_temp := l_xml_assact_temp||
2148 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 1 ),l_data_dep_os_rec.o_dep_os_full_name )||c_eol||
2149 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 2 ),l_data_dep_os_rec.o_dep_os_contact_type )||c_eol||
2150 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 3 ),l_data_dep_os_rec.o_dep_os_birth_date_era_m )||c_eol||
2151 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 4 ),l_data_dep_os_rec.o_dep_os_birth_date_era_t )||c_eol||
2152 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 5 ),l_data_dep_os_rec.o_dep_os_birth_date_era_s )||c_eol||
2153 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 6 ),l_data_dep_os_rec.o_dep_os_birth_date_era_h )||c_eol||
2154 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 7 ),l_data_dep_os_rec.o_dep_os_birth_date_era_yy)||c_eol||
2155 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 8 ),l_data_dep_os_rec.o_dep_os_birth_date_era_mm)||c_eol||
2156 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 9 ),l_data_dep_os_rec.o_dep_os_birth_date_era_dd)||c_eol||
2157 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 10),l_data_dep_os_rec.o_dep_os_occupation )||c_eol||
2158 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 11),l_data_dep_os_rec.o_dep_os_os_sal_er_name )||c_eol||
2159 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 12),l_data_dep_os_rec.o_dep_os_os_sal_er_address)||c_eol||
2160 pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 1 ),l_data_dep_os_rec.a_dep_os_contact_type_b )||c_eol||
2161 pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 2 ),l_data_dep_os_rec.a_dep_os_birth_date )||c_eol;
2162 --
2163 end loop;
2164 --
2165 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2166 --
2167 if g_debug
2168 and g_detail_debug = 'Y' then
2169 hr_utility.set_location(l_proc,150);
2170 end if;
2171 --
2172 else
2173 --
2174 l_xml_assact_temp := null;
2175 for q in 1..c_dep_ltx_row_cnt loop
2176 --
2177 l_data_dep_ltx_rec := l_data_dep_ltx_dummy_rec;
2178 if m <= l_data_dep_ltx_rowset_cnt then
2179 --
2180 l_data_dep_ltx_rec := l_data_dep_ltx_ind_tbl(m * 100 + q);
2181 --
2182 end if;
2183 --
2184 if g_debug
2185 and g_detail_debug = 'Y' then
2186 hr_utility.trace('q : '||to_char(q));
2187 end if;
2188 --
2189 l_xml_assact_temp := l_xml_assact_temp||
2190 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.o_dep_ltx_full_name )||c_eol||
2191 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_contact_type )||c_eol||
2192 --pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_h )||c_eol||
2193 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_yy )||c_eol||
2194 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_mm )||c_eol||
2195 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_dd )||c_eol||
2196 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 6 ),l_data_dep_ltx_rec.o_dep_ltx_address )||c_eol||
2197 pay_jp_report_pkg.add_tag_m('dl' ||to_char(q * 100 + 7 ),l_data_dep_ltx_rec.o_dep_ltx_est_annual_inc )||c_eol||
2198 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 8 ),l_data_dep_ltx_rec.o_dep_ltx_change_date )||c_eol||
2199 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 9 ),l_data_dep_ltx_rec.o_dep_ltx_change_reason )||c_eol||
2200 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.a_dep_ltx_contact_type_b )||c_eol||
2201 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.a_dep_ltx_birth_date )||c_eol||
2202 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.a_dep_ltx_change_date )||c_eol||
2203 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.a_dep_ltx_disability_type_b )||c_eol||
2204 pay_jp_report_pkg.add_tag_v('adl'||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.a_dep_ltx_disability_details)||c_eol;
2205 --
2206 end loop;
2207 --
2208 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2209 --
2210 if g_debug
2211 and g_detail_debug = 'Y' then
2212 hr_utility.set_location(l_proc,160);
2213 end if;
2214 --
2215 end if;
2216 --
2217 -- subsequent page by over records
2218 else
2219 --
2220 if g_debug
2221 and g_detail_debug = 'Y' then
2222 hr_utility.set_location(l_proc,170);
2223 end if;
2224 --
2225 -- added for page break
2226 l_xml_assact := l_xml_assact||pay_jp_report_pkg.add_tag('pb',' ')||c_eol;
2227 --
2228 l_xml_assact := l_xml_assact||'</g_emp>'||c_eol;
2229 --
2230 l_xml_assact := l_xml_assact||
2231 '<g_emp>'||c_eol||
2232 pay_jp_report_pkg.add_tag ('x1' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2233 pay_jp_report_pkg.add_tag ('x2' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2234 pay_jp_report_pkg.add_tag ('x3' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2235 pay_jp_report_pkg.add_tag ('x4' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2236 pay_jp_report_pkg.add_tag ('x5' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2237 pay_jp_report_pkg.add_tag ('x6' ,l_body_rec.o_effective_date_eeyy )||c_eol||
2238 pay_jp_report_pkg.add_tag ('e1' ,l_body_rec.o_employee_number )||c_eol||
2239 pay_jp_report_pkg.add_tag_v('e2' ,l_body_rec.o_tax_office_name )||c_eol||
2240 pay_jp_report_pkg.add_tag_v('e3' ,l_body_rec.o_district_name )||c_eol||
2241 pay_jp_report_pkg.add_tag_v('e4' ,l_body_rec.o_itax_org_name )||c_eol||
2242 pay_jp_report_pkg.add_tag_v('e5' ,l_body_rec.o_itax_org_address )||c_eol||
2243 pay_jp_report_pkg.add_tag_v('e6' ,l_body_rec.o_full_name_kana )||c_eol||
2244 pay_jp_report_pkg.add_tag_v('e7' ,l_body_rec.o_full_name )||c_eol||
2245 pay_jp_report_pkg.add_tag ('e8' ,l_body_rec.o_birth_date_era_m )||c_eol||
2246 pay_jp_report_pkg.add_tag ('e9' ,l_body_rec.o_birth_date_era_t )||c_eol||
2247 pay_jp_report_pkg.add_tag ('e10' ,l_body_rec.o_birth_date_era_s )||c_eol||
2248 pay_jp_report_pkg.add_tag ('e11' ,l_body_rec.o_birth_date_era_h )||c_eol||
2249 pay_jp_report_pkg.add_tag ('e12' ,l_body_rec.o_birth_date_era_yy )||c_eol||
2250 pay_jp_report_pkg.add_tag ('e13' ,l_body_rec.o_birth_date_era_mm )||c_eol||
2251 pay_jp_report_pkg.add_tag ('e14' ,l_body_rec.o_birth_date_era_dd )||c_eol||
2252 pay_jp_report_pkg.add_tag_v('e15' ,l_body_rec.o_household_head_full_name )||c_eol||
2253 pay_jp_report_pkg.add_tag ('e16' ,l_body_rec.o_household_head_contact_type)||c_eol||
2254 pay_jp_report_pkg.add_tag ('e17' ,l_body_rec.o_postal_code1 )||c_eol||
2255 pay_jp_report_pkg.add_tag ('e18' ,l_body_rec.o_postal_code2 )||c_eol||
2256 pay_jp_report_pkg.add_tag_v('e19' ,l_body_rec.o_address )||c_eol||
2257 pay_jp_report_pkg.add_tag ('e20' ,l_body_rec.o_spouse_exist_y )||c_eol||
2258 pay_jp_report_pkg.add_tag ('e21' ,l_body_rec.o_spouse_exist_n )||c_eol||
2259 pay_jp_report_pkg.add_tag ('e22' ,l_body_rec.o_sec_sal_submit_flag )||c_eol||
2260 pay_jp_report_pkg.add_tag ('ed1' ,to_char(null) )||c_eol||
2261 pay_jp_report_pkg.add_tag ('ed2' ,to_char(null) )||c_eol||
2262 pay_jp_report_pkg.add_tag ('ed3' ,to_char(null) )||c_eol||
2263 pay_jp_report_pkg.add_tag ('ed4' ,to_char(null) )||c_eol||
2264 pay_jp_report_pkg.add_tag ('ed5' ,to_char(null) )||c_eol||
2265 pay_jp_report_pkg.add_tag ('ed6' ,to_char(null) )||c_eol||
2266 pay_jp_report_pkg.add_tag ('ed7' ,to_char(null) )||c_eol||
2267 pay_jp_report_pkg.add_tag_v('ed8' ,to_char(null) )||c_eol||
2268 pay_jp_report_pkg.add_tag ('ed9' ,to_char(null) )||c_eol||
2269 pay_jp_report_pkg.add_tag_v('ed10' ,to_char(null) )||c_eol||
2270 pay_jp_report_pkg.add_tag_v('s1' ,to_char(null) )||c_eol||
2271 pay_jp_report_pkg.add_tag ('s2' ,to_char(null) )||c_eol||
2272 pay_jp_report_pkg.add_tag ('s3' ,to_char(null) )||c_eol||
2273 pay_jp_report_pkg.add_tag ('s4' ,to_char(null) )||c_eol||
2274 pay_jp_report_pkg.add_tag ('s5' ,to_char(null) )||c_eol||
2275 pay_jp_report_pkg.add_tag ('s6' ,to_char(null) )||c_eol||
2276 pay_jp_report_pkg.add_tag ('s7' ,to_char(null) )||c_eol||
2277 pay_jp_report_pkg.add_tag ('s8' ,to_char(null) )||c_eol||
2278 pay_jp_report_pkg.add_tag ('s9' ,to_char(null) )||c_eol||
2279 pay_jp_report_pkg.add_tag ('s10' ,to_char(null) )||c_eol||
2280 pay_jp_report_pkg.add_tag ('s11' ,to_char(null) )||c_eol||
2281 pay_jp_report_pkg.add_tag_m('s12' ,to_number(null) )||c_eol||
2282 pay_jp_report_pkg.add_tag ('s13' ,to_char(null) )||c_eol||
2283 pay_jp_report_pkg.add_tag ('s14' ,to_char(null) )||c_eol||
2284 pay_jp_report_pkg.add_tag ('sd1' ,to_char(null) )||c_eol||
2285 pay_jp_report_pkg.add_tag ('sd2' ,to_char(null) )||c_eol||
2286 pay_jp_report_pkg.add_tag ('sd3' ,to_char(null) )||c_eol||
2287 pay_jp_report_pkg.add_tag ('dd1' ,to_char(null) )||c_eol||
2288 pay_jp_report_pkg.add_tag ('dd2' ,to_number(null) )||c_eol||
2289 pay_jp_report_pkg.add_tag ('dd3' ,to_char(null) )||c_eol||
2290 pay_jp_report_pkg.add_tag ('dd4' ,to_number(null) )||c_eol||
2291 pay_jp_report_pkg.add_tag ('dd5' ,to_char(null) )||c_eol||
2292 pay_jp_report_pkg.add_tag ('dd6' ,to_number(null) )||c_eol||
2293 pay_jp_report_pkg.add_tag ('ae1' ,l_body_rec.a_birth_date )||c_eol||
2294 pay_jp_report_pkg.add_tag ('ae2' ,l_body_rec.a_sex_b )||c_eol||
2295 pay_jp_report_pkg.add_tag ('ae3' ,l_body_rec.a_household_head_con_type_b )||c_eol||
2296 pay_jp_report_pkg.add_tag ('ae4' ,l_body_rec.a_postal_code )||c_eol||
2297 pay_jp_report_pkg.add_tag ('ae5' ,l_body_rec.a_married_flag )||c_eol||
2298 pay_jp_report_pkg.add_tag ('ae6' ,l_body_rec.a_sec_sal_submit_flag )||c_eol||
2299 pay_jp_report_pkg.add_tag ('aed1' ,to_char(null) )||c_eol||
2300 pay_jp_report_pkg.add_tag ('aed2' ,to_char(null) )||c_eol||
2301 pay_jp_report_pkg.add_tag ('aed3' ,to_char(null) )||c_eol||
2302 pay_jp_report_pkg.add_tag ('aed4' ,to_char(null) )||c_eol||
2303 pay_jp_report_pkg.add_tag_v('aed5' ,to_char(null) )||c_eol||
2304 pay_jp_report_pkg.add_tag_v('aed6' ,to_char(null) )||c_eol||
2305 pay_jp_report_pkg.add_tag_v('aed7' ,to_char(null) )||c_eol||
2306 pay_jp_report_pkg.add_tag_v('aed8' ,to_char(null) )||c_eol||
2307 pay_jp_report_pkg.add_tag ('aed9' ,to_date(null) )||c_eol||
2308 --pay_jp_report_pkg.add_tag_v('aed10',to_char(null) )||c_eol||
2309 pay_jp_report_pkg.add_tag ('as1' ,to_date(null) )||c_eol||
2310 pay_jp_report_pkg.add_tag ('as2' ,to_char(null) )||c_eol||
2311 pay_jp_report_pkg.add_tag ('as3' ,to_date(null) )||c_eol||
2312 pay_jp_report_pkg.add_tag ('as4' ,to_char(null) )||c_eol||
2313 pay_jp_report_pkg.add_tag_v('asd1' ,to_char(null) )||c_eol;
2314 --
2315 if g_debug
2316 and g_detail_debug = 'Y' then
2317 hr_utility.set_location(l_proc,180);
2318 end if;
2319 --
2320 l_xml_assact_temp := null;
2321 for n in 1..c_dep_row_cnt loop
2322 --
2323 l_data_dep_rec := l_data_dep_dummy_rec;
2324 if m <= l_data_dep_rowset_cnt then
2325 --
2326 l_data_dep_rec := l_data_dep_ind_tbl(m * 100 + n);
2327 --
2328 end if;
2329 --
2330 if g_debug
2331 and g_detail_debug = 'Y' then
2332 hr_utility.trace('n : '||to_char(n));
2333 end if;
2334 --
2335 l_xml_assact_temp := l_xml_assact_temp||
2336 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 1 ),l_data_dep_rec.o_dep_full_name )||c_eol||
2337 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 2 ),l_data_dep_rec.o_dep_contact_type )||c_eol||
2338 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 3 ),l_data_dep_rec.o_dep_birth_date_era_m )||c_eol||
2339 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 4 ),l_data_dep_rec.o_dep_birth_date_era_t )||c_eol||
2340 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 5 ),l_data_dep_rec.o_dep_birth_date_era_s )||c_eol||
2341 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 6 ),l_data_dep_rec.o_dep_birth_date_era_h )||c_eol||
2342 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 7 ),l_data_dep_rec.o_dep_birth_date_era_yy )||c_eol||
2343 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 8 ),l_data_dep_rec.o_dep_birth_date_era_mm )||c_eol||
2344 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 9 ),l_data_dep_rec.o_dep_birth_date_era_dd )||c_eol||
2345 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 10),l_data_dep_rec.o_dep_aged_prnt_lt_flag )||c_eol||
2346 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 11),l_data_dep_rec.o_dep_aged_flag )||c_eol||
2347 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 12),l_data_dep_rec.o_dep_sp_flag )||c_eol||
2348 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 13),l_data_dep_rec.o_dep_occupation )||c_eol||
2349 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 14),l_data_dep_rec.o_dep_address )||c_eol||
2350 pay_jp_report_pkg.add_tag_m('d' ||to_char(n * 100 + 15),l_data_dep_rec.o_dep_est_annual_inc )||c_eol||
2351 pay_jp_report_pkg.add_tag ('d' ||to_char(n * 100 + 16),l_data_dep_rec.o_dep_change_date )||c_eol||
2352 pay_jp_report_pkg.add_tag_v('d' ||to_char(n * 100 + 17),l_data_dep_rec.o_dep_change_reason )||c_eol||
2353 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 1 ),l_data_dep_rec.a_dep_contact_type_b )||c_eol||
2354 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 2 ),l_data_dep_rec.a_dep_birth_date )||c_eol||
2355 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 3 ),l_data_dep_rec.a_dep_dep_type_b )||c_eol||
2356 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 4 ),l_data_dep_rec.a_dep_change_date )||c_eol||
2357 pay_jp_report_pkg.add_tag ('ad'||to_char(n * 100 + 5 ),l_data_dep_rec.a_dep_disability_type_b )||c_eol||
2358 pay_jp_report_pkg.add_tag_v('ad'||to_char(n * 100 + 6 ),l_data_dep_rec.a_dep_disability_details)||c_eol;
2359 --
2360 end loop;
2361 --
2362 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2363 --
2364 if g_debug
2365 and g_detail_debug = 'Y' then
2366 hr_utility.set_location(l_proc,190);
2367 end if;
2368 --
2369 l_xml_assact_temp := null;
2370 for o in 1..c_dep_oe_row_cnt loop
2371 --
2372 l_data_dep_oe_rec := l_data_dep_oe_dummy_rec;
2373 if m <= l_data_dep_oe_rowset_cnt then
2374 --
2375 l_data_dep_oe_rec := l_data_dep_oe_ind_tbl(m * 100 + o);
2376 --
2377 end if;
2378 --
2379 if g_debug
2380 and g_detail_debug = 'Y' then
2381 hr_utility.trace('o : '||to_char(o));
2382 end if;
2383 --
2384 l_xml_assact_temp := l_xml_assact_temp||
2385 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 1 ),l_data_dep_oe_rec.o_dep_oe_full_name )||c_eol||
2386 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 2 ),l_data_dep_oe_rec.o_dep_oe_contact_type )||c_eol||
2387 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 3 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_m )||c_eol||
2388 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 4 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_t )||c_eol||
2389 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 5 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_s )||c_eol||
2390 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 6 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_h )||c_eol||
2391 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 7 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_yy)||c_eol||
2392 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 8 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_mm)||c_eol||
2393 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 9 ),l_data_dep_oe_rec.o_dep_oe_birth_date_era_dd)||c_eol||
2394 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 10),l_data_dep_oe_rec.o_dep_oe_occupation )||c_eol||
2395 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 11),l_data_dep_oe_rec.o_dep_oe_address )||c_eol||
2396 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 12),l_data_dep_oe_rec.o_dep_oe_change_date )||c_eol||
2397 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 13),l_data_dep_oe_rec.o_dep_oe_change_reason )||c_eol||
2398 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 14),l_data_dep_oe_rec.o_dep_oe_oe_full_name )||c_eol||
2399 pay_jp_report_pkg.add_tag ('de' ||to_char(o * 100 + 15),l_data_dep_oe_rec.o_dep_oe_oe_contact_type )||c_eol||
2400 pay_jp_report_pkg.add_tag_v('de' ||to_char(o * 100 + 16),l_data_dep_oe_rec.o_dep_oe_oe_address )||c_eol||
2401 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 1 ),l_data_dep_oe_rec.a_dep_oe_contact_type_b )||c_eol||
2402 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 2 ),l_data_dep_oe_rec.a_dep_oe_birth_date )||c_eol||
2403 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 3 ),l_data_dep_oe_rec.a_dep_oe_change_date )||c_eol||
2404 pay_jp_report_pkg.add_tag ('ade'||to_char(o * 100 + 4 ),l_data_dep_oe_rec.a_dep_oe_oe_contact_type_b)||c_eol;
2405 --
2406 end loop;
2407 --
2408 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2409 --
2410 if g_debug
2411 and g_detail_debug = 'Y' then
2412 hr_utility.set_location(l_proc,200);
2413 end if;
2414 --
2415 if g_effective_date < c_st_upd_date_2011 then
2416 --
2417 l_xml_assact_temp := null;
2418 for p in 1..c_dep_os_row_cnt loop
2419 --
2420 l_data_dep_os_rec := l_data_dep_os_dummy_rec;
2421 if m <= l_data_dep_os_rowset_cnt then
2422 --
2423 l_data_dep_os_rec := l_data_dep_os_ind_tbl(m * 100 + p);
2424 --
2425 end if;
2426 --
2427 if g_debug
2428 and g_detail_debug = 'Y' then
2429 hr_utility.trace('p : '||to_char(p));
2430 end if;
2431 --
2432 l_xml_assact_temp := l_xml_assact_temp||
2433 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 1 ),l_data_dep_os_rec.o_dep_os_full_name )||c_eol||
2434 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 2 ),l_data_dep_os_rec.o_dep_os_contact_type )||c_eol||
2435 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 3 ),l_data_dep_os_rec.o_dep_os_birth_date_era_m )||c_eol||
2436 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 4 ),l_data_dep_os_rec.o_dep_os_birth_date_era_t )||c_eol||
2437 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 5 ),l_data_dep_os_rec.o_dep_os_birth_date_era_s )||c_eol||
2438 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 6 ),l_data_dep_os_rec.o_dep_os_birth_date_era_h )||c_eol||
2439 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 7 ),l_data_dep_os_rec.o_dep_os_birth_date_era_yy)||c_eol||
2440 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 8 ),l_data_dep_os_rec.o_dep_os_birth_date_era_mm)||c_eol||
2441 pay_jp_report_pkg.add_tag ('ds' ||to_char(p * 100 + 9 ),l_data_dep_os_rec.o_dep_os_birth_date_era_dd)||c_eol||
2442 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 10),l_data_dep_os_rec.o_dep_os_occupation )||c_eol||
2443 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 11),l_data_dep_os_rec.o_dep_os_os_sal_er_name )||c_eol||
2444 pay_jp_report_pkg.add_tag_v('ds' ||to_char(p * 100 + 12),l_data_dep_os_rec.o_dep_os_os_sal_er_address)||c_eol||
2445 pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 1 ),l_data_dep_os_rec.a_dep_os_contact_type_b )||c_eol||
2446 pay_jp_report_pkg.add_tag ('ads'||to_char(p * 100 + 2 ),l_data_dep_os_rec.a_dep_os_birth_date )||c_eol;
2447 --
2448 end loop;
2449 --
2450 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2451 --
2452 if g_debug
2453 and g_detail_debug = 'Y' then
2454 hr_utility.set_location(l_proc,210);
2455 end if;
2456 --
2457 else
2458 --
2459 l_xml_assact_temp := null;
2460 for q in 1..c_dep_ltx_row_cnt loop
2461 --
2462 l_data_dep_ltx_rec := l_data_dep_ltx_dummy_rec;
2463 if m <= l_data_dep_ltx_rowset_cnt then
2464 --
2465 l_data_dep_ltx_rec := l_data_dep_ltx_ind_tbl(m * 100 + q);
2466 --
2467 end if;
2468 --
2469 if g_debug
2470 and g_detail_debug = 'Y' then
2471 hr_utility.trace('q : '||to_char(q));
2472 end if;
2473 --
2474 l_xml_assact_temp := l_xml_assact_temp||
2475 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.o_dep_ltx_full_name )||c_eol||
2476 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_contact_type )||c_eol||
2477 --pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_h )||c_eol||
2478 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_yy )||c_eol||
2479 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_mm )||c_eol||
2480 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.o_dep_ltx_birth_date_era_dd )||c_eol||
2481 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 6 ),l_data_dep_ltx_rec.o_dep_ltx_address )||c_eol||
2482 pay_jp_report_pkg.add_tag_m('dl' ||to_char(q * 100 + 7 ),l_data_dep_ltx_rec.o_dep_ltx_est_annual_inc )||c_eol||
2483 pay_jp_report_pkg.add_tag ('dl' ||to_char(q * 100 + 8 ),l_data_dep_ltx_rec.o_dep_ltx_change_date )||c_eol||
2484 pay_jp_report_pkg.add_tag_v('dl' ||to_char(q * 100 + 9 ),l_data_dep_ltx_rec.o_dep_ltx_change_reason )||c_eol||
2485 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 1 ),l_data_dep_ltx_rec.a_dep_ltx_contact_type_b )||c_eol||
2486 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 2 ),l_data_dep_ltx_rec.a_dep_ltx_birth_date )||c_eol||
2487 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 3 ),l_data_dep_ltx_rec.a_dep_ltx_change_date )||c_eol||
2488 pay_jp_report_pkg.add_tag ('adl'||to_char(q * 100 + 4 ),l_data_dep_ltx_rec.a_dep_ltx_disability_type_b )||c_eol||
2489 pay_jp_report_pkg.add_tag_v('adl'||to_char(q * 100 + 5 ),l_data_dep_ltx_rec.a_dep_ltx_disability_details)||c_eol;
2490 --
2491 end loop;
2492 --
2493 l_xml_assact := l_xml_assact||l_xml_assact_temp;
2494 --
2495 if g_debug
2496 and g_detail_debug = 'Y' then
2497 hr_utility.set_location(l_proc,220);
2498 end if;
2499 --
2500 end if;
2501 --
2502 end if;
2503 --
2504 if g_debug
2505 and g_detail_debug = 'Y' then
2506 hr_utility.set_location(l_proc,230);
2507 hr_utility.trace('l_xml_assact_temp lengthb : '||to_char(lengthb(l_xml_assact_temp)));
2508 hr_utility.trace('l_xml_assact lengthb : '||to_char(lengthb(l_xml_assact)));
2509 end if;
2510 --
2511 if nvl(g_call_type,'X') = 'SS' then
2512 --
2513 dbms_lob.writeappend(g_xml_assact_ss,length(l_xml_assact),l_xml_assact);
2514 --
2515 else
2516 --
2517 -- write lob for each page because limitation size of l_xml_assact
2518 pay_core_files.write_to_magtape_lob(l_xml_assact);
2519 --
2520 end if;
2521 --
2522 l_xml_assact_temp := null;
2523 l_xml_assact := null;
2524 --
2525 if g_debug
2526 and g_detail_debug = 'Y' then
2527 hr_utility.set_location(l_proc,240);
2528 end if;
2529 --
2530 end loop;
2531 --
2532 -- added for page break
2533 l_xml_assact := l_xml_assact||pay_jp_report_pkg.add_tag('pb',' ')||c_eol;
2534 --
2535 l_xml_assact := l_xml_assact||'</g_emp>';
2536 --
2537 if g_debug
2538 and g_detail_debug = 'Y' then
2539 hr_utility.set_location(l_proc,250);
2540 hr_utility.trace('l_xml_assact lengthb : '||to_char(lengthb(l_xml_assact)));
2541 end if;
2542 --
2543 if nvl(g_call_type,'X') = 'SS' then
2544 --
2545 dbms_lob.writeappend(g_xml_assact_ss,length(l_xml_assact),l_xml_assact);
2546 --
2547 else
2548 --
2549 pay_core_files.write_to_magtape_lob(l_xml_assact);
2550 --
2551 end if;
2552 --
2553 if g_debug
2554 and g_detail_debug = 'Y' then
2555 hr_utility.set_location(l_proc,1000);
2556 end if;
2557 --
2558 end xml_assact;
2559 --
2560 -- -------------------------------------------------------------------------
2561 -- post_assact
2562 -- -------------------------------------------------------------------------
2563 --procedure post_assact(
2564 -- p_action_information_id in number,
2565 -- p_object_version_number in out nocopy number)
2566 --is
2567 ----
2568 -- l_proc varchar2(80) := c_package||'post_assact';
2569 ----
2570 --begin
2571 ----
2572 -- if g_debug then
2573 -- hr_utility.set_location(l_proc,0);
2574 -- end if;
2575 ----
2576 -- if g_debug then
2577 -- hr_utility.set_location(l_proc,1000);
2578 -- end if;
2579 ----
2580 --end post_assact;
2581 --
2582 -- -------------------------------------------------------------------------
2583 -- gen_xml_header
2584 -- -------------------------------------------------------------------------
2585 procedure gen_xml_header
2586 is
2587 --
2588 l_proc varchar2(80) := c_package||'gen_xml_header';
2589 --
2590 l_xml_header varchar2(32767);
2591 --
2592 begin
2593 --
2594 if g_debug
2595 and g_detail_debug = 'Y' then
2596 hr_utility.set_location(l_proc,0);
2597 end if;
2598 --
2599 l_xml_header := c_eol||'<def_xml>'||c_eol;
2600 --
2601 pay_core_files.write_to_magtape_lob(l_xml_header);
2602 --
2603 if g_debug
2604 and g_detail_debug = 'Y' then
2605 hr_utility.set_location(l_proc,1000);
2606 end if;
2607 --
2608 end gen_xml_header;
2609 --
2610 -- -------------------------------------------------------------------------
2611 -- gen_xml_body
2612 -- -------------------------------------------------------------------------
2613 procedure gen_xml_body
2614 is
2615 --
2616 l_proc varchar2(80) := c_package||'gen_xml_body';
2617 --
2618 l_mag_assignment_action_id number;
2619 l_arch_assignment_action_id number;
2620 --
2621 cursor csr_proc_assact
2622 is
2623 select source_action_id
2624 from pay_assignment_actions proc_paa
2625 where assignment_action_id = l_mag_assignment_action_id;
2626 --
2627 begin
2628 --
2629 if g_debug
2630 and g_detail_debug = 'Y' then
2631 hr_utility.set_location(l_proc,0);
2632 end if;
2633 --
2634 l_mag_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2635 --
2636 if g_debug
2637 and g_detail_debug = 'Y' then
2638 hr_utility.set_location(l_proc,10);
2639 hr_utility.trace('l_mag_assignment_action_id : '||to_char(l_mag_assignment_action_id));
2640 end if;
2641 --
2642 open csr_proc_assact;
2643 fetch csr_proc_assact into l_arch_assignment_action_id;
2644 close csr_proc_assact;
2645 --
2646 if g_debug
2647 and g_detail_debug = 'Y' then
2648 hr_utility.set_location(l_proc,20);
2649 hr_utility.trace('l_arch_assignment_action_id : '||to_char(l_arch_assignment_action_id));
2650 end if;
2651 --
2652 if l_arch_assignment_action_id is not null then
2653 --
2654 xml_assact(l_arch_assignment_action_id);
2655 --
2656 end if;
2657 --
2658 if g_debug
2659 and g_detail_debug = 'Y' then
2660 hr_utility.set_location(l_proc,1000);
2661 end if;
2662 --
2663 end gen_xml_body;
2664 --
2665 -- -------------------------------------------------------------------------
2666 -- gen_xml_footer
2667 -- -------------------------------------------------------------------------
2668 procedure gen_xml_footer
2669 is
2670 --
2671 l_proc varchar2(80) := c_package||'gen_xml_footer';
2672 --
2673 l_xml_footer varchar2(32767);
2674 --
2675 begin
2676 --
2677 if g_debug
2678 and g_detail_debug = 'Y' then
2679 hr_utility.set_location(l_proc,0);
2680 end if;
2681 --
2682 l_xml_footer := '</def_xml>';
2683 --
2684 pay_core_files.write_to_magtape_lob(l_xml_footer);
2685 --
2686 if g_debug
2687 and g_detail_debug = 'Y' then
2688 hr_utility.set_location(l_proc,1000);
2689 end if;
2690 --
2691 end gen_xml_footer;
2692 --
2693 -- -------------------------------------------------------------------------
2694 -- gen_xml_ss
2695 -- -------------------------------------------------------------------------
2696 procedure gen_xml_ss(
2697 p_assignment_action_id in number,
2698 p_xml out nocopy clob)
2699 is
2700 --
2701 l_proc varchar2(80) := c_package||'gen_xml_ss';
2702 --
2703 l_payroll_action_id number;
2704 --
2705 l_xml_header varchar2(32767);
2706 l_xml_footer varchar2(32767);
2707 --
2708 cursor csr_action
2709 is
2710 select payroll_action_id
2711 from pay_assignment_actions
2712 where assignment_action_id = p_assignment_action_id;
2713 --
2714 begin
2715 --
2716 if g_debug
2717 and g_detail_debug = 'Y' then
2718 hr_utility.set_location(l_proc,0);
2719 hr_utility.trace('p_assignment_action_id : '||to_char(p_assignment_action_id));
2720 end if;
2721 --
2722 open csr_action;
2723 fetch csr_action into l_payroll_action_id;
2724 close csr_action;
2725 --
2726 if g_debug
2727 and g_detail_debug = 'Y' then
2728 hr_utility.set_location(l_proc,10);
2729 hr_utility.trace('l_payroll_action_id : '||to_char(l_payroll_action_id));
2730 end if;
2731 --
2732 g_call_type := 'SS';
2733 init_pact(l_payroll_action_id);
2734 --
2735 if g_debug
2736 and g_detail_debug = 'Y' then
2737 hr_utility.set_location(l_proc,20);
2738 end if;
2739 --
2740 l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>'||c_eol||'<def_xml>'||c_eol;
2741 l_xml_footer := '</def_xml>';
2742 g_xml_assact_ss := empty_clob();
2743 --
2744 if g_debug
2745 and g_detail_debug = 'Y' then
2746 hr_utility.set_location(l_proc,30);
2747 end if;
2748 --
2749 dbms_lob.createtemporary(g_xml_assact_ss,true);
2750 --
2751 if g_debug
2752 and g_detail_debug = 'Y' then
2753 hr_utility.set_location(l_proc,40);
2754 end if;
2755 --
2756 dbms_lob.writeappend(g_xml_assact_ss,length(l_xml_header),l_xml_header);
2757 --
2758 if g_debug
2759 and g_detail_debug = 'Y' then
2760 hr_utility.set_location(l_proc,50);
2761 end if;
2762 --
2763 xml_assact(p_assignment_action_id);
2764 --
2765 if g_debug
2766 and g_detail_debug = 'Y' then
2767 hr_utility.set_location(l_proc,60);
2768 end if;
2769 --
2770 dbms_lob.writeappend(g_xml_assact_ss,length(l_xml_footer),l_xml_footer);
2771 --
2772 if g_debug
2773 and g_detail_debug = 'Y' then
2774 hr_utility.set_location(l_proc,70);
2775 end if;
2776 --
2777 p_xml := g_xml_assact_ss;
2778 --
2779 g_call_type := null;
2780 g_xml_assact_ss := empty_clob();
2781 --
2782 if g_debug
2783 and g_detail_debug = 'Y' then
2784 hr_utility.set_location(l_proc,1000);
2785 end if;
2786 --
2787 end gen_xml_ss;
2788 --
2789 -- -------------------------------------------------------------------------
2790 -- archive_data
2791 -- -------------------------------------------------------------------------
2792 procedure archive_data(
2793 p_assignment_action_id in number,
2794 p_effective_date in date)
2795 is
2796 --
2797 l_proc varchar2(80) := c_package||'archive_data';
2798 --
2799 l_assignment_id number;
2800 l_tax_type pay_element_entry_values_f.screen_entry_value%type;
2801 --
2802 l_action_information_id number;
2803 l_object_version_number number;
2804 --
2805 begin
2806 --
2807 if g_debug then
2808 hr_utility.set_location(l_proc,0);
2809 end if;
2810 --
2811 --init_assact(
2812 -- p_assignment_action_id => p_assignment_action_id,
2813 -- p_assignment_id => l_assignment_id);
2814 --
2815 --if g_debug then
2816 -- hr_utility.set_location(l_proc,10);
2817 --end if;
2818 --
2819 --archive_assact(
2820 -- p_assignment_action_id => p_assignment_action_id,
2821 -- p_assignment_id => l_assignment_id);
2822 --
2823 --if g_debug then
2824 -- hr_utility.set_location(l_proc,20);
2825 --end if;
2826 --
2827 --post_assact(
2828 -- p_action_information_id => l_action_information_id,
2829 -- p_object_version_number => l_object_version_number);
2830 --
2831 if g_debug then
2832 hr_utility.set_location(l_proc,1000);
2833 end if;
2834 --
2835 end archive_data;
2836 --
2837 -- -------------------------------------------------------------------------
2838 -- deinitialize_code
2839 -- -------------------------------------------------------------------------
2840 procedure deinitialize_code(
2841 p_payroll_action_id in number)
2842 is
2843 --
2844 l_proc varchar2(80) := c_package||'deinitialize_code';
2845 --
2846 begin
2847 --
2848 if g_debug then
2849 hr_utility.set_location(l_proc,0);
2850 end if;
2851 --
2852 --archive_pact(p_payroll_action_id);
2853 --
2854 if g_debug
2855 and g_detail_debug = 'Y' then
2856 hr_utility.set_location(l_proc,10);
2857 end if;
2858 --
2859 --pay_core_xdo_utils.archive_deinit(p_payroll_action_id);
2860 pay_core_xdo_utils.standard_deinit(p_payroll_action_id);
2861 --
2862 if g_debug then
2863 hr_utility.set_location(l_proc,1000);
2864 end if;
2865 --
2866 end deinitialize_code;
2867 --
2868 -- -------------------------------------------------------------------------
2869 -- sort_code
2870 -- -------------------------------------------------------------------------
2871 --procedure sort_code(
2872 -- p_payroll_action_id in number,
2873 -- p_sqlstr in out nocopy varchar2,
2874 -- p_length out number)
2875 --is
2876 ----
2877 -- l_proc varchar2(80) := c_package||'sort_code';
2878 ----
2879 --begin
2880 ----
2881 -- if g_debug then
2882 -- hr_utility.set_location(l_proc,0);
2883 -- end if;
2884 ----
2885 -- p_sqlstr :=
2886 -- 'select paa.rowid
2887 -- from pay_payroll_actions ppa,
2888 -- pay_assignment_actions paa,
2889 -- pay_jp_def_assact_v pjda,
2890 -- pay_jp_def_emp_v pjde
2891 -- where ppa.payroll_action_id = :pactid
2892 -- and paa.payroll_action_id = ppa.payroll_action_id
2893 -- and pjda.assignment_action_id = paa.source_action_id
2894 -- and pjde.assignment_action_id = pjda.assignment_action_id
2895 -- and pjde.effective_date = pjda.effective_date
2896 -- order by
2897 -- decode(g_sort_order,
2898 -- ''EMPLOYEE_NAME'',pjde.last_name_kana||'' ''||pjde.first_name_kana,
2899 -- lpad(pjde.employee_number,30,'' ''))';
2900 ----
2901 -- p_length := lengthb(p_sqlstr);
2902 ----
2903 -- if g_debug then
2904 -- hr_utility.set_location(l_proc,1000);
2905 -- end if;
2906 ----
2907 --end sort_code;
2908 --
2909 end pay_jp_def_report_pkg;