[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ITAX_REPORT_PKG
Source
1 package body PAY_JP_ITAX_REPORT_PKG as
2 /* $Header: pyjpirep.pkb 120.18.12020000.2 2012/10/11 11:09:17 mdubasi ship $ */
3 --
4 -- Constants
5 --
6 c_package CONSTANT VARCHAR2(31) := 'pay_jp_itax_report_pkg.';
7 c_lf constant varchar2(1) := fnd_global.local_chr(10);
8 --
9 -- Global Variables
10 --
11 g_currency_format_mask varchar2(30);
12 D7 fnd_new_messages.message_text%type;
13 C1 fnd_new_messages.message_text%type;
14 C2 fnd_new_messages.message_text%type;
15 C3 fnd_new_messages.message_text%type;
16 C4 fnd_new_messages.message_text%type;
17 C5 fnd_new_messages.message_text%type;
18 C6 fnd_new_messages.message_text%type;
19 C7 fnd_new_messages.message_text%type;
20 C8 fnd_new_messages.message_text%type;
21 C9 fnd_new_messages.message_text%type;
22 C10 fnd_new_messages.message_text%type;
23 C11 fnd_new_messages.message_text%type;
24 C12 fnd_new_messages.message_text%type;
25 C13 fnd_new_messages.message_text%type;
26 C14 fnd_new_messages.message_text%type;
27 C15 fnd_new_messages.message_text%type;
28 C16 fnd_new_messages.message_text%type;
29 C17 fnd_new_messages.message_text%type;
30 --
31 -- Large XML will elapse more heap size for XML transformation,
32 -- which will raise OutOfMemoryError.
33 -- Increasing maximum heap size(-Xmx128MB) is not right solution.
34 --
35 g_index number := 0;
36 g_chunk_size number := 100;
37 type t_rec is record(
38 assignment_id number,
39 effective_date date,
40 D1 varchar2(240),
41 D2 varchar2(240),
42 D3 varchar2(240),
43 D4 varchar2(240),
44 D5 varchar2(240),
45 D70 varchar2(240),
46 D71 varchar2(240),
47 D6 varchar2(240),
48 D8 varchar2(240),
49 D9 varchar2(240),
50 D10 varchar2(240),
51 D11 varchar2(240),
52 D12 varchar2(240),
53 D13 varchar2(240),
54 D14 varchar2(240),
55 D15 varchar2(240),
56 D16 varchar2(240),
57 D17 varchar2(240),
58 D18 varchar2(240),
59 D19 varchar2(240),
60 D20 varchar2(240),
61 D21 varchar2(240),
62 D22 varchar2(240),
63 D23 varchar2(240),
64 D24 varchar2(240),
65 D25 varchar2(240),
66 D26 varchar2(240),
67 D27 varchar2(240),
68 D28 varchar2(240),
69 D29 varchar2(240),
70 D30 varchar2(240),
71 D31 varchar2(240),
72 D32 varchar2(240),
73 SYSTEM_DESCRIPTION varchar2(480),
74 USER_DESCRIPTION varchar2(480),
75 D34 varchar2(240),
76 D35 varchar2(240),
77 D36 varchar2(240),
78 D37 varchar2(240),
79 D38 varchar2(240),
80 D39 varchar2(240),
81 D40 varchar2(240),
82 D41 varchar2(240),
83 D42 varchar2(240),
84 D43 varchar2(240),
85 D44 varchar2(240),
86 D45 varchar2(240),
87 D46 varchar2(240),
88 D47 varchar2(240),
89 D48 varchar2(240),
90 D49 varchar2(240),
91 D50 varchar2(240),
92 D51 varchar2(240),
93 HIRE_DATE date,
94 ACTUAL_TERMINATION_DATE date,
95 /* bug.6208573
96 D52 number,
97 D53 number,
98 D54 number,
99 */
100 D55 varchar2(240),
101 D56 varchar2(240),
102 D57 varchar2(240),
103 D58 varchar2(240),
104 D59 number,
105 D60 number,
106 D61 number,
107 D62 varchar2(240),
108 D63 varchar2(240),
109 D64 varchar2(240),
110 D65 varchar2(240),
111 D66 varchar2(240),
112 D67 varchar2(240),
113 D68 varchar2(240),
114 D69 varchar2(240),
115 D72 varchar2(240),
116 D73 varchar2(240),
117 D74 varchar2(240),
118 D75 varchar2(240),
119 D76 varchar2(240),
120 include_or_exclude hr_assignment_set_amendments.include_or_exclude%type);
121 type t_tbl is table of t_rec index by binary_integer;
122 g_tbl t_tbl;
123 -- |---------------------------------------------------------------------------|
124 -- |---------------------------------< init >----------------------------------|
125 -- |---------------------------------------------------------------------------|
126 procedure init(
127 p_tax_year in number default null,
128 p_itax_organization_id in number default null,
129 p_exclude_ineligible_flag in varchar2 default null,
130 p_include_terminated_flag in varchar2 default null,
131 p_termination_date_from in date default null,
132 p_termination_date_to in date default null,
133 p_assignment_set_id in number default null,
134 p_action_information_id1 in number default null,
135 p_action_information_id2 in number default null,
136 p_action_information_id3 in number default null,
137 p_action_information_id4 in number default null,
138 p_action_information_id5 in number default null,
139 p_action_information_id6 in number default null,
140 p_action_information_id7 in number default null,
141 p_action_information_id8 in number default null,
142 p_action_information_id9 in number default null,
143 p_action_information_id10 in number default null,
144 p_sort_order in varchar2 default null,
145 p_chunk_size in number default 100)
146 is
147 l_concat_ids varchar2(255);
148 l_concat_id_count number := 0;
149 l_select_clause varchar2(32767);
150 l_from_clause varchar2(32767);
151 l_where_clause varchar2(32767);
152 l_order_by_clause varchar2(255);
153 l_formula_id number;
154 l_amendment_type varchar2(1);
155 l_valid boolean;
156 -- l_temp_tbl hr_jp_standard_pkg.t_varchar2_tbl;
157 --
158 -- Private procedures
159 --
160 procedure append_action_information_id(p_action_information_id in number)
161 is
162 begin
163 if p_action_information_id is not null then
164 if l_concat_ids is not null then
165 l_concat_ids := l_concat_ids || ', ';
166 end if;
167 --
168 l_concat_ids := l_concat_ids || p_action_information_id;
169 l_concat_id_count := l_concat_id_count + 1;
170 end if;
171 end append_action_information_id;
172 --
173 procedure append_select_clause(p_clause in varchar2)
174 is
175 begin
176 l_select_clause := l_select_clause || p_clause || c_lf;
177 end append_select_clause;
178 --
179 procedure append_from_clause(p_clause in varchar2)
180 is
181 begin
182 l_from_clause := l_from_clause || p_clause || c_lf;
183 end append_from_clause;
184 --
185 procedure append_where_clause(p_clause in varchar2)
186 is
187 begin
188 if l_where_clause is null then
189 l_where_clause := 'where ' || p_clause || c_lf;
190 else
191 l_where_clause := l_where_clause || 'and ' || p_clause || c_lf;
192 end if;
193 end append_where_clause;
194 begin
195 append_select_clause(
196 'select person.assignment_id
197 , person.effective_date
198 , to_char(PERSON.EFFECTIVE_DATE, ''YYYY'') D1
199 , hr_jp_standard_pkg.to_jp_char(PERSON.EFFECTIVE_DATE, ''EE'') D2
200 , to_number(hr_jp_standard_pkg.to_jp_char(PERSON.EFFECTIVE_DATE, ''YY'')) D3
201 , ADDRESS_KANJI D4
202 , ltrim(rtrim(LAST_NAME_KANA || '' '' || FIRST_NAME_KANA)) D5
203 , LAST_NAME_KANA D70
204 , FIRST_NAME_KANA D71
205 , ltrim(rtrim(LAST_NAME_KANJI || '' '' || FIRST_NAME_KANJI)) D6
206 , TAX.TAXABLE_INCOME D8
207 , TAX.NET_TAXABLE_INCOME D9
208 , TAX.TOTAL_INCOME_EXEMPT D10
209 , TAX.WITHHOLDING_ITAX D11
210 , TAX.MUTUAL_AID_PREMIUM D12
211 , OTHER.DEPENDENT_SPOUSE_EXISTS_KOU D13
212 , OTHER.DEPENDENT_SPOUSE_NO_EXIST_KOU D14
213 , OTHER.DEPENDENT_SPOUSE_EXISTS_OTSU D15
214 , OTHER.DEPENDENT_SPOUSE_NO_EXIST_OTSU D16
215 , OTHER.AGED_SPOUSE_EXISTS D17
216 , TAX.SPOUSE_SPECIAL_EXEMPT D18
217 , OTHER.NUM_SPECIFIEDS_KOU D19
218 , OTHER.NUM_SPECIFIEDS_OTSU D20
219 , OTHER.NUM_AGED_PARENTS_PARTIAL D21
220 , OTHER.NUM_AGEDS_KOU D22
221 , OTHER.NUM_AGEDS_OTSU D23
222 , OTHER.NUM_DEPENDENTS_KOU D24
223 , OTHER.NUM_DEPENDENTS_OTSU D25
224 , OTHER.NUM_SPECIAL_DISABLEDS_PARTIAL D26
225 , OTHER.NUM_SPECIAL_DISABLEDS D27
226 , OTHER.NUM_DISABLEDS D28
227 , TAX.SOCIAL_INSURANCE_PREMIUM D29
228 , TAX.LIFE_INSURANCE_PREMIUM_EXEMPT D30
229 , TAX.DAMAGE_INSURANCE_PREMIUM_EXEM D31
230 , TAX.HOUSING_TAX_REDUCTION D32
231 , OTHER2.ITW_SYSTEM_DESC2_KANJI SYSTEM_DESCRIPTION
232 , OTHER2.ITW_USER_DESC_KANJI USER_DESCRIPTION
233 , TAX.SPOUSE_NET_TAXABLE_INCOME D34
234 , TAX.PRIVATE_PENSION_PREMIUM D35
235 , TAX.LONG_DAMAGE_INSURANCE_PREMIUM D36
236 , OTHER.HUSBAND_EXISTS D37
237 , OTHER.MINOR D38
238 , OTHER.OTSU D39
239 , OTHER.SPECIAL_DISABLED D40
240 , OTHER.DISABLED D41
241 , OTHER.AGED D42
242 , OTHER.WIDOW D43
243 , OTHER.SPECIAL_WIDOW D44
244 , OTHER.WIDOWER D45
245 , OTHER.WORKING_STUDENT D46
246 , OTHER.DECEASED_TERMINATION D47
247 , OTHER.DISASTERED D48
248 , OTHER.FOREIGNER D49
249 , OTHER.EMPLOYED D50
250 , OTHER.UNEMPLOYED D51
251 , fnd_date.canonical_to_date(PERSON.JP_DATE_START) HIRE_DATE
252 , fnd_date.canonical_to_date(PERSON.ACTUAL_TERMINATION_DATE) ACTUAL_TERMINATION_DATE
253 /* bug.6208573. ACTION_INFORMATION21/22/23 obsolete.
254 , to_number(PERSON.EMPLOYMENT_DATE_YEAR) D52
255 , to_number(PERSON.EMPLOYMENT_DATE_MONTH) D53
256 , to_number(PERSON.EMPLOYMENT_DATE_DAY) D54
257 */
258 , PERSON.DATE_OF_BIRTH_MEIJI D55
259 , PERSON.DATE_OF_BIRTH_TAISHOU D56
260 , PERSON.DATE_OF_BIRTH_SHOUWA D57
261 , PERSON.DATE_OF_BIRTH_HEISEI D58
262 , to_number(PERSON.DATE_OF_BIRTH_YEAR) D59
263 , to_number(PERSON.DATE_OF_BIRTH_MONTH) D60
264 , to_number(PERSON.DATE_OF_BIRTH_DAY) D61
265 , ARCH.EMPLOYER_ADDRESS D62
266 , ARCH.EMPLOYER_NAME D63
267 , ARCH.EMPLOYER_TELEPHONE_NUMBER D64
268 , ARCH.TAX_OFFICE_NUMBER D65
269 , ARCH.REFERENCE_NUMBER D66
270 , TAX.WITHHOLDING_ITAX2 D67
271 , TAX.ITAX_ADJUSTMENT2 D68
272 , OTHER2.ITW_SYSTEM_DESC1_KANJI D69,
273 pjid.num_junior_deps D72
274 , TAX1.OLD_LIFE_INS_PREM D73
275 , TAX1.NEW_LIFE_INS_PREM D74
276 , TAX1.CARE_MED_INS_PREM D75
277 , TAX1.NEW_PEN_INS_PREM D76');
278 --
279 append_from_clause(
280 'from pay_jp_itax_person_v person
281 , pay_jp_itax_arch_v arch
282 , pay_jp_itax_tax_v tax
283 , pay_jp_itax_other_v other
284 , pay_jp_itax_other2_v2 other2,
285 pay_jp_itax_dep_v pjid
286 , pay_jp_itax_tax_v2 tax1');
287 --
288 -- P_ACTION_INFORMATION_IDXX
289 --
290 append_action_information_id(p_action_information_id1);
291 append_action_information_id(p_action_information_id2);
292 append_action_information_id(p_action_information_id3);
293 append_action_information_id(p_action_information_id4);
294 append_action_information_id(p_action_information_id5);
295 append_action_information_id(p_action_information_id6);
296 append_action_information_id(p_action_information_id7);
297 append_action_information_id(p_action_information_id8);
298 append_action_information_id(p_action_information_id9);
299 append_action_information_id(p_action_information_id10);
300 --
301 if l_concat_id_count > 0 then
302 if l_concat_id_count = 1 then
303 append_where_clause('person.action_information_id = ' || l_concat_ids);
304 else
305 append_where_clause('person.action_information_id in (' || l_concat_ids || ')');
306 end if;
307 end if;
308 --
309 -- P_ITAX_ORGANIZATION_ID
310 --
311 if p_itax_organization_id is not null then
312 append_where_clause('person.itax_organization_id = ''' || fnd_number.number_to_canonical(p_itax_organization_id) || '''');
313 end if;
314 --
315 -- P_TAX_YEAR
316 --
317 if p_tax_year is not null then
318 append_where_clause('to_char(person.effective_date, ''YYYY'') = ''' || to_char(p_tax_year, 'FM0999') || '''');
319 end if;
320 --
321 -- P_INCLUDE_TERMINATED_FLAG
322 -- P_TERMINATION_DATE_FROM
323 -- P_TERMINATION_DATE_TO
324 --
325 if p_include_terminated_flag = 'N' then
326 append_where_clause('person.actual_termination_date is null');
327 elsif p_include_terminated_flag = 'Y' then
328 if p_termination_date_from is not null then
329 if p_termination_date_to is not null then
330 append_where_clause(
331 'fnd_date.canonical_to_date(person.actual_termination_date)
332 between fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_termination_date_from) || ''')
333 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_termination_date_to) || ''')');
334 else
335 append_where_clause(
336 'fnd_date.canonical_to_date(person.actual_termination_date) >= fnd_date.canonical_to_date(''' ||
337 fnd_date.date_to_canonical(p_termination_date_from) || ''')');
338 end if;
339 else
340 if p_termination_date_to is not null then
341 append_where_clause(
342 'fnd_date.canonical_to_date(person.actual_termination_date) <= fnd_date.canonical_to_date(''' ||
343 fnd_date.date_to_canonical(p_termination_date_to) || ''')');
344 end if;
345 end if;
346 end if;
347 --
348 -- P_ASSIGNMENT_SET_ID
349 --
350 if p_assignment_set_id is not null then
351 hr_jp_ast_utility_pkg.get_assignment_set_info(p_assignment_set_id, l_formula_id, l_amendment_type);
352 --
353 if l_amendment_type <> 'N' then
354 append_select_clause(', amd.include_or_exclude');
355 append_from_clause(', hr_assignment_set_amendments amd');
356 --
357 if l_formula_id is null and l_amendment_type <> 'E' then
358 append_where_clause(
359 'amd.assignment_set_id = ' || p_assignment_set_id || '
360 and amd.assignment_id = person.assignment_id
361 and amd.include_or_exclude = ''I''');
362 else
363 append_where_clause(
364 'amd.assignment_set_id(+) = ' || p_assignment_set_id || '
365 and amd.assignment_id(+) = person.assignment_id
366 and nvl(amd.include_or_exclude, ''I'') <> ''E''');
367 end if;
368 else
369 append_select_clause(', null');
370 end if;
371 else
372 append_select_clause(', null');
373 end if;
374 --
375 append_where_clause(
376 'arch.action_context_id = person.action_context_id
377 and arch.effective_date = person.effective_date');
378 --
379 -- P_EXCLUDE_INELIGIBLE_FLAG
380 --
381 if p_exclude_ineligible_flag = 'Y' then
382 append_where_clause('arch.submission_required_flag = ''Y''');
383 end if;
384 --
385 append_where_clause(
386 'tax.action_context_id = person.action_context_id
387 and tax.effective_date = person.effective_date
388 and other.action_context_id = person.action_context_id
389 and other.effective_date = person.effective_date
390 and other2.action_context_id = person.action_context_id
391 and other2.effective_date = person.effective_date
392 and pjid.action_context_id (+) = person.action_context_id
393 and pjid.effective_date (+) = person.effective_date
394 and tax1.action_context_id (+)= person.action_context_id
395 and tax1.effective_date (+)= person.effective_date');
396 --
397 -- P_SORT_ORDER
398 --
399 if p_sort_order = 'DISTRICT_CODE' then
400 l_order_by_clause := 'order by person.district_code, lpad(person.employee_number, 30)';
401 elsif p_sort_order = 'EMPLOYEE_NUMBER' then
402 l_order_by_clause := 'order by lpad(person.employee_number, 30)';
403 else
404 l_order_by_clause := null;
405 end if;
406 --
407 /*
408 hr_jp_standard_pkg.to_table(l_select_clause || l_from_clause || l_where_clause || l_order_by_clause, 255, l_temp_tbl);
409 for i in 1..l_temp_tbl.count loop
410 dbms_output.put_line(l_temp_tbl(i));
411 end loop;
412 */
413 --
414 execute immediate
415 l_select_clause ||
416 l_from_clause ||
417 l_where_clause ||
418 l_order_by_clause
419 bulk collect into g_tbl;
420 --
421 for i in 1..g_tbl.count loop
422 l_valid := true;
423 --
424 -- Validate by Assignment Set FastFormula.
425 --
426 if l_formula_id is not null and g_tbl(i).include_or_exclude is null then
427 l_valid := hr_jp_ast_utility_pkg.formula_validate(
428 p_formula_id => l_formula_id,
429 p_assignment_id => g_tbl(i).assignment_id,
430 p_effective_date => g_tbl(i).effective_date,
431 p_populate_fs => true);
432 end if;
433 --
434 if not l_valid then
435 g_tbl.delete(i);
436 end if;
437 end loop;
438 --
439 g_index := g_tbl.next(0);
440 g_chunk_size := p_chunk_size;
441 end init;
442 -- |---------------------------------------------------------------------------|
443 -- |--------------------------------< getXML >---------------------------------|
444 -- |---------------------------------------------------------------------------|
445 --
446 -- DBMS_XMLGEN/DBMS_XMLQUERY cannot be used because of Assignment Set validation.
447 --
448 function getXML return clob
449 is
450 l_clob clob;
451 l_description varchar2(2000);
452 l_counter number := 1;
453 l_jp_date varchar2(6);
454 --
455 procedure append_tag(p_tag in varchar2)
456 is
457 l_value varchar2(2000);
458 begin
459 l_value := p_tag || c_lf;
460 dbms_lob.writeAppend(l_clob, length(l_value), l_value);
461 end append_tag;
462 --
463 procedure append_item(
464 p_tag in varchar2,
465 p_value in varchar2)
466 is
467 l_value varchar2(2000);
468 begin
469 if p_value is not null then
470 l_value := '<' || p_tag || '>' || dbms_xmlgen.convert(p_value) || '</' || p_tag || '>' || c_lf;
471 dbms_lob.writeAppend(l_clob, length(l_value), l_value);
472 --
473 -- Workaround for XDO bug.6129128
474 --
475 l_value := '<' || p_tag || '_2>' || dbms_xmlgen.convert(p_value) || '</' || p_tag || '_2>' || c_lf;
476 dbms_lob.writeAppend(l_clob, length(l_value), l_value);
477 l_value := '<' || p_tag || '_3>' || dbms_xmlgen.convert(p_value) || '</' || p_tag || '_3>' || c_lf;
478 dbms_lob.writeAppend(l_clob, length(l_value), l_value);
479 l_value := '<' || p_tag || '_4>' || dbms_xmlgen.convert(p_value) || '</' || p_tag || '_4>' || c_lf;
480 dbms_lob.writeAppend(l_clob, length(l_value), l_value);
481 end if;
482 end append_item;
483 --
484 procedure append_item(
485 p_tag in varchar2,
486 p_value in number)
487 is
488 begin
489 if p_value is not null then
490 append_item(p_tag, fnd_number.number_to_canonical(p_value));
491 end if;
492 end append_item;
493 --
494 procedure append_item(
495 p_tag in varchar2,
496 p_value in date)
497 is
498 begin
499 if p_value is not null then
500 append_item(p_tag, fnd_date.date_to_canonical(p_value));
501 end if;
502 end append_item;
503 begin
504 if g_index is not null then
505 --
506 -- Construct XML
507 --
508 dbms_lob.createTemporary(l_clob, true, dbms_lob.call);
509 dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
510 append_tag('<?xml version="1.0"?>');
511 append_tag('<ROWSET>');
512 --
513 while g_index is not null and (g_chunk_size <= 0 or l_counter <= g_chunk_size) loop
514 append_tag('<G1>');
515 --
516 append_item('D1', g_tbl(g_index).D1);
517 append_item('D2', g_tbl(g_index).D2);
518 append_item('D3', g_tbl(g_index).D3);
519 append_item('D4', g_tbl(g_index).D4);
520 append_item('D5', g_tbl(g_index).D5);
521 append_item('D70', g_tbl(g_index).D70);
522 append_item('D71', g_tbl(g_index).D71);
523 append_item('D6', g_tbl(g_index).D6);
524 append_item('D8', g_tbl(g_index).D8);
525 append_item('D9', g_tbl(g_index).D9);
526 append_item('D10', g_tbl(g_index).D10);
527 append_item('D11', g_tbl(g_index).D11);
528 append_item('D12', g_tbl(g_index).D12);
529 append_item('D13', g_tbl(g_index).D13);
530 append_item('D14', g_tbl(g_index).D14);
531 append_item('D15', g_tbl(g_index).D15);
532 append_item('D16', g_tbl(g_index).D16);
533 append_item('D17', g_tbl(g_index).D17);
534 append_item('D18', g_tbl(g_index).D18);
535 append_item('D19', g_tbl(g_index).D19);
536 append_item('D20', g_tbl(g_index).D20);
537 append_item('D21', g_tbl(g_index).D21);
538 append_item('D22', g_tbl(g_index).D22);
539 append_item('D23', g_tbl(g_index).D23);
540 append_item('D24', g_tbl(g_index).D24);
541 append_item('D25', g_tbl(g_index).D25);
542 append_item('D26', g_tbl(g_index).D26);
543 append_item('D27', g_tbl(g_index).D27);
544 append_item('D28', g_tbl(g_index).D28);
545 append_item('D29', g_tbl(g_index).D29);
546 append_item('D30', g_tbl(g_index).D30);
547 append_item('D31', g_tbl(g_index).D31);
548 append_item('D32', g_tbl(g_index).D32);
549 --
550 if g_tbl(g_index).user_description is null then
551 l_description := g_tbl(g_index).system_description;
552 elsif g_tbl(g_index).system_description is null then
553 l_description := g_tbl(g_index).user_description;
554 else
555 l_description := g_tbl(g_index).system_description || ',' || g_tbl(g_index).user_description;
556 end if;
557 --
558 append_item('D33', l_description);
559 --
560 append_item('D34', g_tbl(g_index).D34);
561 append_item('D35', g_tbl(g_index).D35);
562 append_item('D36', g_tbl(g_index).D36);
563 append_item('D37', g_tbl(g_index).D37);
564 append_item('D38', g_tbl(g_index).D38);
565 append_item('D39', g_tbl(g_index).D39);
566 append_item('D40', g_tbl(g_index).D40);
567 append_item('D41', g_tbl(g_index).D41);
568 append_item('D42', g_tbl(g_index).D42);
569 append_item('D43', g_tbl(g_index).D43);
570 append_item('D44', g_tbl(g_index).D44);
571 append_item('D45', g_tbl(g_index).D45);
572 append_item('D46', g_tbl(g_index).D46);
573 append_item('D47', g_tbl(g_index).D47);
574 append_item('D48', g_tbl(g_index).D48);
575 append_item('D49', g_tbl(g_index).D49);
576 append_item('D50', g_tbl(g_index).D50);
577 append_item('D51', g_tbl(g_index).D51);
578 --
579 -- bug.6208573
580 -- "Hire Date" and "Actual Termination Date" must be maintained separately,
581 -- so ACTION_INFORMATION21/22/23 of JP_ITAX_PERSON context are obsolete.
582 --
583 if g_tbl(g_index).hire_date is not null
584 and g_tbl(g_index).actual_termination_date is not null then
585 l_jp_date := hr_jp_standard_pkg.to_jp_char(g_tbl(g_index).hire_date, 'YYMMDD');
586 append_item('D52_U', to_number(substr(l_jp_date, 1, 2)));
587 append_item('D53_U', to_number(substr(l_jp_date, 3, 2)));
588 append_item('D54_U', to_number(substr(l_jp_date, 5, 2)));
589 --
590 l_jp_date := hr_jp_standard_pkg.to_jp_char(g_tbl(g_index).actual_termination_date, 'YYMMDD');
591 append_item('D52_L', to_number(substr(l_jp_date, 1, 2)));
592 append_item('D53_L', to_number(substr(l_jp_date, 3, 2)));
593 append_item('D54_L', to_number(substr(l_jp_date, 5, 2)));
594 elsif g_tbl(g_index).hire_date is not null
595 or g_tbl(g_index).actual_termination_date is not null then
596 l_jp_date := hr_jp_standard_pkg.to_jp_char(
597 nvl(g_tbl(g_index).hire_date,
598 g_tbl(g_index).actual_termination_date), 'YYMMDD');
599 append_item('D52_C', to_number(substr(l_jp_date, 1, 2)));
600 append_item('D53_C', to_number(substr(l_jp_date, 3, 2)));
601 append_item('D54_C', to_number(substr(l_jp_date, 5, 2)));
602 end if;
603 --
604 -- Backward Compatibility
605 --
606 if g_tbl(g_index).hire_date is not null
607 or g_tbl(g_index).actual_termination_date is not null then
608 l_jp_date := hr_jp_standard_pkg.to_jp_char(
609 nvl(g_tbl(g_index).actual_termination_date,
610 g_tbl(g_index).hire_date), 'YYMMDD');
611 append_item('D52', to_number(substr(l_jp_date, 1, 2)));
612 append_item('D53', to_number(substr(l_jp_date, 3, 2)));
613 append_item('D54', to_number(substr(l_jp_date, 5, 2)));
614 end if;
615 /*
616 append_item('D52', g_tbl(g_index).D52);
617 append_item('D53', g_tbl(g_index).D53);
618 append_item('D54', g_tbl(g_index).D54);
619 */
620 --
621 append_item('D55', g_tbl(g_index).D55);
622 append_item('D56', g_tbl(g_index).D56);
623 append_item('D57', g_tbl(g_index).D57);
624 append_item('D58', g_tbl(g_index).D58);
625 append_item('D59', g_tbl(g_index).D59);
626 append_item('D60', g_tbl(g_index).D60);
627 append_item('D61', g_tbl(g_index).D61);
628 append_item('D62', g_tbl(g_index).D62);
629 append_item('D63', g_tbl(g_index).D63);
630 append_item('D64', g_tbl(g_index).D64);
631 append_item('D65', g_tbl(g_index).D65);
632 append_item('D66', g_tbl(g_index).D66);
633 append_item('D67', g_tbl(g_index).D67);
634 append_item('D68', g_tbl(g_index).D68);
635 append_item('D69', g_tbl(g_index).D69);
636 --
637 if to_number(g_tbl(g_index).D1) >= 2011 then
638 --
639 append_item('D72', g_tbl(g_index).D72);
640 --
641 end if;
642 --
643 -- 2012 Withholding Changes
644 --
645 if to_number(g_tbl(g_index).D1) >= 2012 then
646 --
647 append_item('D73', g_tbl(g_index).D73);
648 append_item('D74', g_tbl(g_index).D74);
649 append_item('D75', g_tbl(g_index).D75);
650 append_item('D76', g_tbl(g_index).D76);
651 --
652 end if;
653 --
654 append_item('D7', D7);
655 append_item('C1', C1);
656 append_item('C2', C2);
657 append_item('C3', C3);
658 append_item('C4', C4);
659 append_item('C5', C5);
660 append_item('C6', C6);
661 append_item('C7', C7);
662 append_item('C8', C8);
663 append_item('C9', C9);
664 append_item('C10', C10);
665 append_item('C11', C11);
666 append_item('C12', C12);
667 append_item('C13', C13);
668 append_item('C14', C14);
669 append_item('C15', C15);
670 append_item('C16', C16);
671 append_item('C17', C17);
672 --
673 -- append_item('PAGE_BREAK', ' ');
674 append_tag('<PAGE_BREAK> </PAGE_BREAK>');
675 --
676 append_tag('</G1>');
677 --
678 g_index := g_tbl.next(g_index);
679 l_counter := l_counter + 1;
680 end loop;
681 --
682 append_tag('</ROWSET>');
683 end if;
684 --
685 -- Remember to call freeTemporary after the usage.
686 --
687 return l_clob;
688 exception
689 when others then
690 if l_clob is not null then
691 dbms_lob.freeTemporary(l_clob);
692 end if;
693 raise;
694 end getXML;
695 -- |---------------------------------------------------------------------------|
696 -- |--------------------------------< getXML >---------------------------------|
697 -- |---------------------------------------------------------------------------|
698 function getXML(p_action_information_id in number) return clob
699 is
700 begin
701 init(p_action_information_id1 => p_action_information_id);
702 --
703 return getXML;
704 end getXML;
705 -- |---------------------------------------------------------------------------|
706 -- |-----------------------------< gen_bulk_xml >------------------------------|
707 -- |---------------------------------------------------------------------------|
708 -- Deprecated. Use getXML instead.
709 PROCEDURE gen_bulk_xml(
710 p_archive_id in varchar2,
711 p_xml out nocopy clob)
712 IS
713 BEGIN
714 p_xml := getXML(p_archive_id);
715 END gen_bulk_xml;
716 -- |---------------------------------------------------------------------------|
717 -- |------------------------------< gen_per_xml >------------------------------|
718 -- |---------------------------------------------------------------------------|
719 -- Deprecated. Use getXML instead.
720 PROCEDURE gen_per_xml(
721 p_archive_id in varchar2,
722 p_year out nocopy number,
723 p_xml out nocopy clob)
724 IS
725 l_year number;
726 BEGIN
727 gen_bulk_xml(
728 p_archive_id => p_archive_id,
729 p_xml => p_xml);
730 --
731 select to_number(to_char(PERSON.EFFECTIVE_DATE, 'YYYY'))
732 into p_year
733 from pay_jp_itax_person_v person
734 where person.action_information_id = p_archive_id;
735 END gen_per_xml;
736 --
737 BEGIN
738 D7 := fnd_message.get_string('PAY', 'PAY_JP_WIC_EARNINGS_TYPE');
739 C1 := fnd_message.get_string('PAY', 'PAY_JP_ITW_NUMBER');
740 C2 := fnd_message.get_string('PAY', 'PAY_JP_ITW_KANA_NAME');
741 C3 := fnd_message.get_string('PAY', 'PAY_JP_ITW_JOB_NAME');
742 C4 := fnd_message.get_string('PAY', 'PAY_JP_ITW_EX_SPOUSE');
743 C5 := fnd_message.get_string('PAY', 'PAY_JP_ITW_EX_SELF');
744 C6 := fnd_message.get_string('PAY', 'PAY_JP_ITW_DESCRIPTION');
745 C7 := fnd_message.get_string('PAY', 'PAY_JP_ITW_PHONE');
746 C8 := fnd_message.get_string('PAY', 'PAY_JP_ITW_YEAR');
747 C9 := fnd_message.get_string('PAY', 'PAY_JP_ITW_WITHIN');
748 C10 := fnd_message.get_string('PAY', 'PAY_JP_ITW_EXIST');
749 C11 := fnd_message.get_string('PAY', 'PAY_JP_ITW_NOT_EXIST');
750 C12 := fnd_message.get_string('PAY', 'PAY_JP_ITW_FOLLOW');
751 C13 := fnd_message.get_string('PAY', 'PAY_JP_ITW_COUNT');
752 C14 := fnd_message.get_string('PAY', 'PAY_JP_ITW_MEIJI');
753 C15 := fnd_message.get_string('PAY', 'PAY_JP_ITW_TAISHOU');
754 C16 := fnd_message.get_string('PAY', 'PAY_JP_ITW_SHOWA');
755 C17 := fnd_message.get_string('PAY', 'PAY_JP_ITW_HEISEI');
756 END;
757