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