[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ISDF_RPT
Source
1 PACKAGE BODY PAY_JP_ISDF_RPT AS
2 /* $Header: pyjpisrp.pkb 120.28 2007/11/29 07:45:50 keyazawa noship $ */
3 --
4 --
5 -- Global Variables
6 --
7 EOL VARCHAR2(5) := fnd_global.local_chr(10);
8 vCtr NUMBER;
9 c_proc VARCHAR2(100);
10 l_xfdf_string CLOB;
11 c_package CONSTANT VARCHAR2(31) := 'pay_jp_isdf_rpt.';
12 g_proc_name VARCHAR2(240);
13 g_debug BOOLEAN;
14 g_bg_id NUMBER;
15 p_write_xml CLOB;
16 g_dummy NUMBER := -99 ;
17 g_all_exclusions_flag NUMBER;
18 l_emp_no_opt VARCHAR2(15);
19 l_prn_app_opt VARCHAR2(15);
20 --
21 c_st_upd_date_2007 constant date := to_date('2007/01/01','YYYY/MM/DD');
22 --
23 /****************************************************************************
24 Name : get_amendment_flag
25 Description : This fucntion return the include_or_exclude flag for an
26 assignment id.
27 *****************************************************************************/
28 FUNCTION get_amendment_flag
29 (
30 p_assignment_id IN NUMBER,
31 p_assignment_set_id IN NUMBER
32 )
33 RETURN VARCHAR2 IS
34 l_inc_or_exc HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
35 --
36 BEGIN
37 SELECT INCLUDE_OR_EXCLUDE
38 INTO l_inc_or_exc
39 FROM HR_ASSIGNMENT_SET_AMENDMENTS
40 WHERE ASSIGNMENT_ID = p_assignment_id
41 AND ASSIGNMENT_SET_ID = p_assignment_set_id;
42 --
43 RETURN l_inc_or_exc;
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 RETURN 'ZZ';
47 END get_amendment_flag;
48 --
49 /****************************************************************************
50 Name : chk_ass_set
51 Description : This fucntion checks if for the passed assignment_id an
52 assignment action is to be created or not. It checks for
53 assignment set by criteria also taking into account if any
54 amendment is defined for that assignment id.
55 *****************************************************************************/
56 FUNCTION chk_ass_set(
57 p_assignment_id IN NUMBER,
58 p_assignment_set_id IN NUMBER,
59 p_formula_id IN NUMBER,
60 p_effective_date IN DATE,
61 p_dummy IN NUMBER) RETURN BOOLEAN
62 IS
63 l_result BOOLEAN;
64 l_amendment_flag HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
65 --
66 BEGIN
67 IF (p_dummy = 1)THEN
68 l_amendment_flag := get_amendment_flag(p_assignment_id,p_assignment_set_id);
69 IF (l_amendment_flag = 'ZZ') THEN
70 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date);
71 ELSIF l_amendment_flag = 'E' THEN
72 l_result := false;
73 ELSIF l_amendment_flag = 'I' THEN
74 l_result := true;
75 END IF;
76 ELSE
77 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date);
78 END IF;
79 --
80 RETURN l_result;
81 --
82 EXCEPTION
83 WHEN OTHERS THEN
84 hr_utility.set_location('chk_ass_set'||substr(sqlerrm,1,200),99);
85 RETURN FALSE;
86 END chk_ass_set;
87 --
88 /****************************************************************************
89 Name : chk_ass_set_mixed
90 Description : This fucntion checks if the assignment set passed is based
91 on both criteria and amendment or not.
92 *****************************************************************************/
93 FUNCTION chk_ass_set_mixed(
94 p_assignment_set_id IN NUMBER) RETURN NUMBER
95 IS
96 l_dummy NUMBER;
97 --
98 BEGIN
99 SELECT 1
100 INTO l_dummy
101 FROM HR_ASSIGNMENT_SET_AMENDMENTS
102 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
103 AND ROWNUM = 1;
104 RETURN l_dummy;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 l_dummy := 0;
108 RETURN l_dummy;
109 END chk_ass_set_mixed;
110 --
111 /****************************************************************************
112 Name : chk_all_exclusions
113 Description : This fucntion checks if the assignment set passed has only
114 exclusions.
115 *****************************************************************************/
116 FUNCTION chk_all_exclusions(
117 p_assignment_set_id IN NUMBER) RETURN NUMBER
118 IS
119 l_dummy NUMBER;
120 BEGIN
121 SELECT 0
122 INTO l_dummy
123 FROM HR_ASSIGNMENT_SET_AMENDMENTS
124 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
125 AND INCLUDE_OR_EXCLUDE = 'I'
126 AND ROWNUM = 1;
127 RETURN l_dummy;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 l_dummy := 1;
131 RETURN l_dummy;
132 END chk_all_exclusions;
133 --
134 function cnv_str(
135 p_text in varchar2,
136 p_start in number default null,
137 p_end in number default null)
138 return varchar2
139 is
140 --
141 l_text varchar2(4000);
142 --
143 begin
144 --
145 l_text := ltrim(rtrim(replace(p_text,to_multi_byte(' '),' ')));
146 --
147 if p_start is not null
148 and p_end is not null then
149 --
150 l_text := substr(l_text,p_start,p_end);
151 --
152 end if;
153 --
154 return l_text;
155 --
156 end cnv_str;
157 --
158 function htmlspchar(
159 p_text in varchar2)
160 return varchar2
161 is
162 --
163 l_htmlspchar varchar2(1) := 'N';
164 --
165 begin
166 --
167 if nvl(instr(p_text,'<'),0) > 0 then
168 l_htmlspchar := 'Y';
169 end if;
170 --
171 if l_htmlspchar = 'N'
172 and nvl(instr(p_text,'>'),0) > 0 then
173 l_htmlspchar := 'Y';
174 end if;
175 --
176 if l_htmlspchar = 'N'
177 and nvl(instr(p_text,'&'),0) > 0 then
178 l_htmlspchar := 'Y';
179 end if;
180 --
181 if l_htmlspchar = 'N'
182 and nvl(instr(p_text,''''),0) > 0 then
183 l_htmlspchar := 'Y';
184 end if;
185 --
186 if l_htmlspchar = 'N'
187 and nvl(instr(p_text,'"'),0) > 0 then
188 l_htmlspchar := 'Y';
189 end if;
190 --
191 if l_htmlspchar = 'Y' then
192 return '<![CDATA['||p_text||']]>';
193 else
194 return p_text;
195 end if;
196 end htmlspchar;
197 --
198 /****************************************************************************
199 Name : PRINT_CLOB
200 Description : This procedure prints contents of a CLOB object passed as
201 parameter.
202 *****************************************************************************/
203 PROCEDURE PRINT_CLOB
204 (
205 p_clob CLOB
206 ) AS
207 ln_chars number;
208 ln_offset number;
209 lv_buf varchar2(255);
210 BEGIN
211 ln_chars := 240;
212 ln_offset := 1;
213 LOOP
214 lv_buf := null;
215 dbms_lob.read(
216 p_clob,
217 ln_chars,
218 ln_offset,
219 lv_buf
220 );
221 hr_utility.trace(lv_buf);
222 ln_offset := ln_offset + ln_chars;
223 END LOOP;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 hr_utility.trace ('CLOB contents end.');
227 END PRINT_CLOB;
228 --
229 /****************************************************************************
230 Name : range_cursor
231 Arguments : p_payroll_action_id
232 p_sqlstr to return the SQL Statement
233 Description : This procedure defines a SQL statement
234 to fetch all the people to be included in the report.
235 This SQL statement is used to define the 'chunks' for
236 multi-threaded operation
237 *****************************************************************************/
238 PROCEDURE range_cursor
239 (
240 P_PAYROLL_ACTION_ID number,
241 P_SQLSTR OUT NOCOPY varchar2
242 ) AS
243 l_proc_name varchar2(100);
244 BEGIN
245 l_proc_name := g_proc_name || 'RANGE_CURSOR';
246 hr_utility.trace ('Entering '||l_proc_name);
247 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
248 p_sqlstr := ' select distinct p.person_id'||
249 ' from per_people_f p,'||
250 ' pay_payroll_actions pa'||
251 ' where pa.payroll_action_id = :payroll_action_id'||
252 ' and p.business_group_id = pa.business_group_id'||
253 ' order by p.person_id ';
254 hr_utility.trace ('Range cursor query : ' || p_sqlstr);
255 hr_utility.trace ('Leaving '||l_proc_name);
256 END range_cursor;
257 --
258 /****************************************************************************
259 Name : action_creation
260 Arguments : p_payroll_action_id
261 p_start_person_id
262 p_end_person_id
263 p_chunk_number
264 Description :This procedure creates assignment actions for the
265 payroll_action_id passed as parameter.
266 *****************************************************************************/
267 PROCEDURE action_creation
268 (
269 P_PAYROLL_ACTION_ID number,
270 P_START_PERSON_ID number,
271 P_END_PERSON_ID number,
272 P_CHUNK number
273 ) AS
274 CURSOR c_assact(pay_act_id pay_payroll_actions.payroll_action_id%TYPE,trans_stat varchar2)
275 IS
276 SELECT distinct pjiav.assignment_id, pjiav.effective_date
277 FROM per_all_assignments_f paa,
278 per_all_people_f pap,
279 pay_assignment_actions pas,
280 pay_jp_isdf_assact_v pjiav
281 WHERE paa.person_id between p_start_person_id and p_end_person_id
282 AND paa.person_id = pap.person_id
283 AND sysdate between pap.effective_start_date and pap.effective_end_date
284 AND sysdate between paa.effective_start_date and paa.effective_end_date
285 AND pas.assignment_id = paa.assignment_id
286 AND pas.payroll_action_id = pay_act_id
287 AND pjiav.assignment_action_id = pas.assignment_action_id
288 AND pjiav.assignment_id = pas.assignment_id
289 AND (pjiav.transaction_status = decode(trans_stat,'N','A')
290 or pjiav.transaction_status = decode(trans_stat,'N','F')
291 or pjiav.transaction_status = decode(trans_stat,'Y','A')
292 or pjiav.transaction_status = decode(trans_stat,'Y','F')
293 or pjiav.transaction_status = decode(trans_stat,'Y','N')
294 or pjiav.transaction_status = decode(trans_stat,'Y','U')); -- Last condition in where clause added for Bug Fix:5487428
295 --
296 l_assact pay_assignment_actions.assignment_action_id%type ;
297 l_proc_name VARCHAR2(60);
298 l_old_pact_id NUMBER;
299 l_cur_pact NUMBER;
300 l_legislative_parameters VARCHAR2(2000);
301 l_ass_set_id NUMBER;
302 l_result1 VARCHAR2(30);
303 l_result2 BOOLEAN;
304 l_formula_id NUMBER;
305 --
306 BEGIN
307 --
308 SELECT legislative_parameters
309 INTO l_legislative_parameters
310 FROM pay_payroll_actions
311 WHERE payroll_action_id = P_PAYROLL_ACTION_ID;
312 --
313 l_old_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',l_legislative_parameters));
314 l_ass_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',l_legislative_parameters));
315 l_prn_app_opt:= pay_core_utils.get_parameter('PUBLISH_CRITERION',l_legislative_parameters); --Bug Fix:5487428
316 --
317 IF g_debug THEN
318 l_proc_name := g_proc_name || 'ACTION_CREATION';
319 hr_utility.trace ('Entering '||l_proc_name);
320 hr_utility.trace ('Parameters ....');
321 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
322 hr_utility.trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
323 hr_utility.trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
324 hr_utility.trace ('P_CHUNK = '|| P_CHUNK);
325 hr_utility.trace ('P_OLD_PAYROLL_ACTION-ID = '||l_old_pact_id);
326 hr_utility.trace ('P_ASS_SET_ID = '||l_ass_set_id);
327 hr_utility.trace ('PRN_EMP_NO = '||l_emp_no_opt);
328 hr_utility.trace ('PUBLISH_CRITERION = '||l_prn_app_opt);
329 END IF;
330 --
331 if g_bg_id is null then
332 Select p.business_group_id into g_bg_id
333 from pay_payroll_actions p
334 where p.payroll_action_id = p_payroll_action_id ;
335 end if ;
336 --
337 IF (g_dummy = -99) THEN
338 -- IF condition to ensure that functions are called only once.
339 g_dummy := chk_ass_set_mixed(l_ass_set_id);
340 g_all_exclusions_flag := chk_all_exclusions(l_ass_set_id);
341 END IF ;
342 --
343 FOR i IN c_assact(l_old_pact_id,l_prn_app_opt) LOOP
344 -- Added NVL to overcome NULL issue.
345 IF (NVL(l_ass_set_id,0) = 0) THEN
346 -- NO assignment set passed as parameter
347 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
348 hr_nonrun_asact.insact(l_assact,
349 i.assignment_id ,
350 p_payroll_action_id,
351 p_chunk,
352 null);
353 ELSE
354 -- assignment set is passed as parameter
355 SELECT formula_id INTO l_formula_id
356 FROM hr_assignment_sets
357 WHERE assignment_set_id = l_ass_set_id;
358 --
359 IF l_formula_id IS NULL THEN
360 -- assignment set by ammmendment passed
361 IF (g_all_exclusions_flag = 0) THEN
362 -- assignment set by ammmendment passed is not all exclusions.
363 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
364 IF (l_result1 = 'I') THEN
365 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
366 hr_nonrun_asact.insact(l_assact,
367 i.assignment_id ,
368 p_payroll_action_id,
369 p_chunk,
370 null);
371 END IF;
372 --
373 ELSE
374 -- assignment set by ammmendment passed is all exclusions.
375 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
376 IF (l_result1 <> 'E') THEN
377 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
378 hr_nonrun_asact.insact(l_assact,
379 i.assignment_id ,
380 p_payroll_action_id,
381 p_chunk,
382 null);
383 END IF;
384 --
385 END IF;
386 --
387 ELSE
388 -- assignment set by criteria passed
389 l_result2 := chk_ass_set(i.assignment_id, l_ass_set_id, l_formula_id, i.effective_date, g_dummy);
390 IF (l_result2 = TRUE) THEN
391 SELECT pay_assignment_actions_s.nextval into l_assact from dual;
392 hr_nonrun_asact.insact(l_assact,
393 i.assignment_id ,
394 p_payroll_action_id,
395 p_chunk,
396 null);
397 END IF;
398 --
399 END IF;
400 --
401 END IF;
402 --
403 END LOOP;
404 --
405 END action_creation;
406 --
407 /****************************************************************************
408 Name : init_code
409 Description : None
410 *****************************************************************************/
411 PROCEDURE INIT_CODE ( P_PAYROLL_ACTION_ID IN NUMBER) IS
412 BEGIN
413 hr_utility.trace ('inside INIT_CODE ');
414 NULL;
415 END;
416 --
417 /****************************************************************************
418 Name : archive_code
419 Description : None
420 *****************************************************************************/
421 PROCEDURE ARCHIVE_CODE ( P_ASSIGNMENT_ACTION_ID IN NUMBER,
422 P_EFFECTIVE_DATE IN DATE ) IS
423 BEGIN
424 hr_utility.trace ('inside ARCHIVE_CODE ');
425 NULL;
426 END ;
427 --
428 /****************************************************************************
429 Name : assact_xml
430 Arguments : p_assignment_action_id
431 Description : This procedure creates xml for the assignment_action_id passed
432 as parameter. It then writes the xml into vXMLTable.
433 *****************************************************************************/
434 PROCEDURE assact_xml(
435 p_assignment_action_id IN NUMBER)
436 IS
437 --
438 CURSOR cur_isdf_employer(p_mag_asg_action_id NUMBER)
439 IS
440 SELECT to_char(pjip.effective_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') year,
441 pjip.effective_date,
442 pjip.tax_office_name,
443 pjip.salary_payer_name,
444 pjip.salary_payer_address
445 FROM pay_jp_isdf_pact_v pjip,
446 pay_assignment_actions paa
447 WHERE paa.assignment_action_id = p_mag_asg_action_id
448 AND paa.payroll_action_id = pjip.payroll_action_id;
449 --
450 isdf_employer_c cur_isdf_employer%ROWTYPE;
451 --
452 CURSOR cur_isdf_emp(p_mag_asg_action_id NUMBER)
453 IS
454 SELECT pjie.last_name_kana,
455 pjie.first_name_kana,
456 pjie.last_name,
457 pjie.first_name,
458 pjie.address,
459 pjie.employee_number /* Enh:5671124 : Employee_number addition */
460 FROM pay_jp_isdf_emp_v pjie
461 WHERE pjie.assignment_action_id = p_mag_asg_action_id;
462 --
463 isdf_emp_c cur_isdf_emp%ROWTYPE;
464 --
465 cursor cur_isdf_calc(p_mag_asg_action_id NUMBER)
466 is
467 select decode(pjicd.life_gen_ins_prem, 0,null,pjicd.life_gen_ins_prem) life_gen_ins_prem,
468 decode(pjicd.life_pens_ins_prem, 0,null,pjicd.life_pens_ins_prem) life_pens_ins_prem,
469 decode(pjicd.life_gen_ins_calc_prem, 0,null,pjicd.life_gen_ins_calc_prem) life_gen_ins_calc_prem,
470 decode(pjicd.life_pens_ins_calc_prem, 0,null,pjicd.life_pens_ins_calc_prem) life_pens_ins_calc_prem,
471 decode(pjicd.life_ins_deduction, 0,null,pjicd.life_ins_deduction) life_ins_deduction,
472 decode(pjicd.earthquake_ins_prem, 0,null,pjicd.earthquake_ins_prem) earthquake_ins_prem,
473 decode(pjicd.nonlife_long_ins_prem, 0,null,pjicd.nonlife_long_ins_prem) nonlife_long_ins_prem,
474 decode(pjicd.nonlife_short_ins_prem, 0,null,pjicd.nonlife_short_ins_prem) nonlife_short_ins_prem,
475 decode(pjicd.earthquake_ins_calc_prem, 0,null,pjicd.earthquake_ins_calc_prem) earthquake_ins_calc_prem,
476 decode(pjicd.nonlife_long_ins_calc_prem, 0,null,pjicd.nonlife_long_ins_calc_prem) nonlife_long_ins_calc_prem,
477 decode(pjicd.nonlife_short_ins_calc_prem, 0,null,pjicd.nonlife_short_ins_calc_prem) nonlife_short_ins_calc_prem,
478 decode(pjicd.nonlife_ins_deduction, 0,null,pjicd.nonlife_ins_deduction) nonlife_ins_deduction,
479 decode(pjicd.social_ins_deduction, 0,null,pjicd.social_ins_deduction) social_ins_deduction,
480 decode(pjicd.mutual_aid_deduction, 0,null,pjicd.mutual_aid_deduction) mutual_aid_deduction,
481 decode(pjicd.sp_earned_income_calc, 0,null,pjicd.sp_earned_income_calc) sp_earned_income_calc,
485 decode(pjicd.sp_real_estate_income_calc, 0,null,pjicd.sp_real_estate_income_calc) sp_real_estate_income_calc,
482 decode(pjicd.sp_business_income_calc, 0,null,pjicd.sp_business_income_calc) sp_business_income_calc,
483 decode(pjicd.sp_miscellaneous_income_calc,0,null,pjicd.sp_miscellaneous_income_calc) sp_miscellaneous_income_calc,
484 decode(pjicd.sp_dividend_income_calc, 0,null,pjicd.sp_dividend_income_calc) sp_dividend_income_calc,
486 decode(pjicd.sp_retirement_income_calc, 0,null,pjicd.sp_retirement_income_calc) sp_retirement_income_calc,
487 decode(pjicd.sp_other_income_calc, 0,null,pjicd.sp_other_income_calc) sp_other_income_calc,
488 decode(pjicd.sp_income_calc, 0,null,pjicd.sp_income_calc) sp_income_calc,
489 decode(pjicd.spouse_income, 0,null,pjicd.spouse_income) spouse_income,
490 decode(pjicd.spouse_deduction, 0,null,pjicd.spouse_deduction) spouse_deduction
491 from pay_jp_isdf_calc_dct_v pjicd
492 where pjicd.assignment_action_id=p_mag_asg_action_id
493 and pjicd.status <> 'D';
494 --
495 isdf_calc_c cur_isdf_calc%ROWTYPE;
496 --
497 CURSOR cur_isdf_mutual(p_mag_asg_action_id NUMBER)
498 IS
499 SELECT pjima.enterprise_contract_prem,
500 pjima.pension_prem,
501 pjima.disable_sup_contract_prem
502 FROM pay_jp_isdf_mutual_aid_v pjima
503 WHERE pjima.assignment_action_id=p_mag_asg_action_id
504 and pjima.status <> 'D';
505 --
506 isdf_mutual_c cur_isdf_mutual%ROWTYPE;
507 --
508 CURSOR cur_isdf_spouse(p_mag_asg_action_id NUMBER)
509 IS
510 SELECT pjis.full_name_kana,
511 pjis.full_name,
512 pjis.address,
513 pjis.emp_income
514 FROM pay_jp_isdf_spouse_v pjis
515 WHERE pjis.assignment_action_id=p_mag_asg_action_id
516 and pjis.status <> 'D';
517 --
518 isdf_spouse_c cur_isdf_spouse%ROWTYPE;
519 --
520 CURSOR cur_isdf_spouse_inc(p_mag_asg_action_id NUMBER)
521 IS
522 SELECT pjisi.sp_earned_income,
523 pjisi.sp_business_income,
524 pjisi.sp_business_income_exp,
525 pjisi.sp_miscellaneous_income,
526 pjisi.sp_miscellaneous_income_exp,
527 pjisi.sp_dividend_income,
528 pjisi.sp_dividend_income_exp,
529 pjisi.sp_real_estate_income,
530 pjisi.sp_real_estate_income_exp,
531 pjisi.sp_retirement_income,
532 pjisi.sp_retirement_income_exp,
533 pjisi.sp_other_income,
534 pjisi.sp_other_income_exp,
535 pjisi.sp_other_income_exp_dct
536 FROM pay_jp_isdf_spouse_inc_v pjisi
537 WHERE pjisi.assignment_action_id=p_mag_asg_action_id
538 and pjisi.status <> 'D';
539 --
540 isdf_spouse_inc_c cur_isdf_spouse_inc%ROWTYPE;
541 --
542 -- LIFE GEN
543 CURSOR cur_isdf_life_gen(p_mag_act_info_id NUMBER)
544 IS
545 SELECT pjilg.ins_company_name,
546 pjilg.ins_type,
547 pjilg.ins_period,
548 pjilg.contractor_name,
549 pjilg.beneficiary_name,
550 pjilg.beneficiary_relship,
551 pjilg.annual_prem
552 FROM pay_jp_isdf_life_gen_v pjilg
553 WHERE pjilg.assignment_action_id=p_mag_act_info_id
554 and pjilg.status <> 'D';
555 --
556 TYPE isdf_ins_company_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
557 TYPE isdf_ins_type_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
558 TYPE isdf_ins_period_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
559 TYPE isdf_contractor_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
560 TYPE isdf_beneficiary_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
561 TYPE isdf_beneficiary_relship_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
562 TYPE isdf_annual_prem_lg IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
563 --
564 ins_company_name_lg isdf_ins_company_name_lg;
565 ins_type_lg isdf_ins_type_lg;
566 ins_period_lg isdf_ins_period_lg;
567 contractor_name_lg isdf_contractor_name_lg;
568 beneficiary_name_lg isdf_beneficiary_name_lg;
569 beneficiary_relship_lg isdf_beneficiary_relship_lg;
570 annual_prem_lg isdf_annual_prem_lg;
571 --
572 -- LIFE PENS
573 CURSOR cur_isdf_life_pens(p_mag_act_info_id NUMBER)
574 IS
575 SELECT pjilp.ins_company_name,
576 pjilp.ins_type,
577 pjilp.ins_period_start_date,
578 pjilp.ins_period,
579 pjilp.contractor_name,
580 pjilp.beneficiary_name,
581 pjilp.beneficiary_relship,
582 pjilp.annual_prem
583 FROM pay_jp_isdf_life_pens_v pjilp
584 WHERE pjilp.assignment_action_id=p_mag_act_info_id
585 and pjilp.status <> 'D';
586 --
587 TYPE isdf_ins_company_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
588 TYPE isdf_ins_type_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
589 TYPE isdf_ins_period_start_date_lp IS TABLE OF DATE INDEX BY BINARY_INTEGER;
590 TYPE isdf_ins_period_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
591 TYPE isdf_contractor_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
592 TYPE isdf_beneficiary_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
593 TYPE isdf_beneficiary_relship_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
594 TYPE isdf_annual_prem_lp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
595 --
596 ins_company_name_lp isdf_ins_company_name_lp;
597 ins_type_lp isdf_ins_type_lp;
598 ins_period_start_date_lp isdf_ins_period_start_date_lp;
602 beneficiary_relship_lp isdf_beneficiary_relship_lp;
599 ins_period_lp isdf_ins_period_lp;
600 contractor_name_lp isdf_contractor_name_lp;
601 beneficiary_name_lp isdf_beneficiary_name_lp;
603 annual_prem_lp isdf_annual_prem_lp;
604 --
605 --NONLIFE
606 cursor cur_isdf_nonlife(p_mag_act_info_id number)
607 is
608 select pjin.nonlife_ins_term_type,
609 pjin.ins_company_name,
610 pjin.ins_type,
611 pjin.ins_period,
612 pjin.contractor_name,
613 pjin.beneficiary_name,
614 pjin.beneficiary_relship,
615 pjin.maturity_repayment,
616 pjin.annual_prem
617 from pay_jp_isdf_nonlife_v pjin
618 where pjin.assignment_action_id=p_mag_act_info_id
619 and pjin.status <> 'D';
620 --
621 type isdf_nonlife_ins_term_type_nl is table of varchar2(240) index by binary_integer;
622 TYPE isdf_ins_company_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
623 TYPE isdf_ins_type_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
624 TYPE isdf_ins_period_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
625 TYPE isdf_contractor_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
626 TYPE isdf_beneficiary_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
627 TYPE isdf_beneficiary_relship_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
628 TYPE isdf_maturity_repayment_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
629 TYPE isdf_annual_prem_nl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
630 --
631 nonlife_ins_term_type_nl isdf_nonlife_ins_term_type_nl;
632 ins_company_name_nl isdf_ins_company_name_nl;
633 ins_type_nl isdf_ins_type_nl;
634 ins_period_nl isdf_ins_period_nl;
635 contractor_name_nl isdf_contractor_name_nl;
636 beneficiary_name_nl isdf_beneficiary_name_nl;
637 beneficiary_relship_nl isdf_beneficiary_relship_nl;
638 maturity_repayment_nl isdf_maturity_repayment_nl;
639 annual_prem_nl isdf_annual_prem_nl;
640 --
641 --SOCIAL
642 CURSOR cur_isdf_social(p_mag_act_info_id NUMBER)
643 IS
644 SELECT pjis.ins_type,
645 pjis.ins_payee_name,
646 pjis.debtor_name,
647 pjis.beneficiary_relship,
648 pjis.annual_prem
649 FROM pay_jp_isdf_social_v pjis
650 WHERE pjis.assignment_action_id=p_mag_act_info_id
651 and pjis.status <> 'D';
652 --
653 TYPE isdf_ins_type_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
654 TYPE isdf_ins_payee_name_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
655 TYPE isdf_debtor_name_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
656 TYPE isdf_beneficiary_relship_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
657 TYPE isdf_annual_prem_s IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
658 --
659 ins_type_s isdf_ins_type_s;
660 ins_payee_name_s isdf_ins_payee_name_s;
661 debtor_name_s isdf_debtor_name_s;
662 beneficiary_relship_s isdf_beneficiary_relship_s;
663 annual_prem_s isdf_annual_prem_s;
664 --
665 --Variables-----
666 k NUMBER;
667 a NUMBER;
668 b NUMBER;
669 c NUMBER;
670 d NUMBER;
671 e NUMBER;
672 i NUMBER;
673 l1 NUMBER;
674 l2 NUMBER;
675 no_of_pages NUMBER;
676 n_life_gen NUMBER;
677 n_life_pens NUMBER;
678 n_nonlife NUMBER;
679 n_social NUMBER;
680 count_life_gen NUMBER;
681 count_life_pens NUMBER;
682 count_nonlife NUMBER;
683 count_social NUMBER;
684 l_xml VARCHAR2(4000);
685 l_xml2 VARCHAR2(4000);
686 l_common_xml_page1 VARCHAR2(4000);
687 l_common_xml VARCHAR2(4000);
688 l_xml_begin VARCHAR2(200);
689 first_digit VARCHAR2(1);
690 second_digit VARCHAR2(1);
691 ps_date_length NUMBER;
692 ps_date VARCHAR2(30);
693 spded_value NUMBER(6,2);
694 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
695 l_emp_no VARCHAR2(80);
696 --
697 l_c13 varchar2(60);
698 l_c14 varchar2(60);
699 l_c15 varchar2(60);
700 l_c16 varchar2(60);
701 l_t48 varchar2(60);
702 l_t48a varchar2(60);
703 l_t48b varchar2(60);
704 l_t56 varchar2(60);
705 l_t56a varchar2(60);
706 l_t56b varchar2(60);
707 --
708 BEGIN
709 --
710 if g_msg_circle is null then
711 --
712 fnd_message.set_name('PER','HR_JP_CIRCLE');
713 g_msg_circle := fnd_message.get;
714 --
715 g_msg_circle := substr(g_msg_circle,0,1);
716 --
717 end if;
718 --
719 vXMLTable.DELETE;
720 vCtr := 0;
721 --
722 --hr_utility.set_location('Entering : ' || c_proc, 10);
723 hr_utility.trace('isdf_xml');
724 --
725 l_xml_begin := '<isdf>'||EOL||'<isdf1>' || EOL;
726 vXMLTable(vCtr).xmlstring := l_xml_begin;
727 vCtr := vCtr + 1;
728 l_mag_asg_action_id :=p_assignment_action_id;
729 --
730 OPEN cur_isdf_employer(l_mag_asg_action_id);
731 FETCH cur_isdf_employer INTO isdf_employer_c;
732 CLOSE cur_isdf_employer;
733 --
734 OPEN cur_isdf_emp(l_mag_asg_action_id);
735 FETCH cur_isdf_emp INTO isdf_emp_c;
736 CLOSE cur_isdf_emp;
737 --
738 OPEN cur_isdf_calc(l_mag_asg_action_id);
739 FETCH cur_isdf_calc INTO isdf_calc_c;
743 FETCH cur_isdf_mutual INTO isdf_mutual_c;
740 CLOSE cur_isdf_calc;
741 --
742 OPEN cur_isdf_mutual(l_mag_asg_action_id);
744 CLOSE cur_isdf_mutual;
745 --
746 OPEN cur_isdf_spouse(l_mag_asg_action_id);
747 FETCH cur_isdf_spouse INTO isdf_spouse_c;
748 CLOSE cur_isdf_spouse;
749 --
750 OPEN cur_isdf_spouse_inc(l_mag_asg_action_id);
751 FETCH cur_isdf_spouse_inc INTO isdf_spouse_inc_c;
752 CLOSE cur_isdf_spouse_inc;
753 --
754 -- Code to find the two digits for the spouse deduction field starts
755 spded_value := isdf_calc_c.spouse_deduction/10000;
756 --
757 -- assumption, spded_value should consist under 2 digits.
758 -- (no business case of decimal or more than 3 digits)
759 -- based on current design, 0 is treated as null, so no output.
760 -- (not sure whether replacing null is desired or not..)
761 first_digit := substrb(lpad(to_char(trunc(spded_value)),2,'0'),1,1);
762 --
763 if first_digit = '0' then
764 first_digit := null;
765 end if;
766 --
767 -- Code to find the two digits for the spouse deduction field ends
768 second_digit := substrb(lpad(to_char(trunc(spded_value)),2,'0'),2,1);
769 --
770 --
771 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
772 l_c13 := to_char(to_number(isdf_calc_c.nonlife_long_ins_prem),fnd_currency.get_format_mask('JPY',40));
773 l_c14 := to_char(to_number(isdf_calc_c.nonlife_short_ins_prem),fnd_currency.get_format_mask('JPY',40));
774 l_c15 := to_char(to_number(isdf_calc_c.nonlife_long_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
775 l_c16 := to_char(to_number(isdf_calc_c.nonlife_short_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
776 else
777 l_c13 := to_char(to_number(isdf_calc_c.earthquake_ins_prem),fnd_currency.get_format_mask('JPY',40));
778 l_c14 := to_char(to_number(isdf_calc_c.nonlife_long_ins_prem),fnd_currency.get_format_mask('JPY',40));
779 l_c15 := to_char(to_number(isdf_calc_c.earthquake_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
780 l_c16 := to_char(to_number(isdf_calc_c.nonlife_long_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
781 end if;
782 --
783 -- Creating xml string for pages after first page (repeating page for over lines of printing data records)
784 l_common_xml :=
785 '<c1>' ||isdf_employer_c.year ||'</c1>' ||EOL|| --year
786 '<c2>' ||htmlspchar(cnv_str(isdf_employer_c.tax_office_name)) ||'</c2>' ||EOL|| --tax_office_name
787 '<c3>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_name)) ||'</c3>' ||EOL|| --employer_full_name
788 '<c4>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_address)) ||'</c4>' ||EOL|| --employer_address
789 '<c5>' ||htmlspchar(cnv_str(isdf_emp_c.last_name_kana)||''||cnv_str(isdf_emp_c.first_name_kana)) ||'</c5>' ||EOL|| --kana_name
790 '<c6>' ||htmlspchar(cnv_str(isdf_emp_c.last_name)||''||cnv_str(isdf_emp_c.first_name)) ||'</c6>' ||EOL|| --name
791 '<c7>' ||htmlspchar(cnv_str(isdf_emp_c.address)) ||'</c7>' ||EOL|| --address
792 '<c8>' ||htmlspchar(to_char(to_number(isdf_calc_c.life_gen_ins_prem),fnd_currency.get_format_mask('JPY',40))) ||'</c8>' ||EOL|| --life_gen_ins_prem
793 '<c9>' ||htmlspchar(to_char(to_number(isdf_calc_c.life_pens_ins_prem),fnd_currency.get_format_mask('JPY',40)))||'</c9>' ||EOL|| --life_pens_ins_prem
794 '<c10>'||to_char(to_number(isdf_calc_c.life_gen_ins_calc_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c10>'||EOL|| --life_gen_ins_calc_prem
795 '<c11>'||to_char(to_number(isdf_calc_c.life_pens_ins_calc_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c11>'||EOL|| --life_pens_ins_calc_prem
796 '<c12>'||to_char(to_number(isdf_calc_c.life_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c12>'||EOL|| --life_ins_deduction
797 '<c13>'||l_c13 ||'</c13>'||EOL|| --nonlife_long_ins_prem
798 '<c14>'||l_c14 ||'</c14>'||EOL|| --nonlife_short_ins_prem
799 '<c15>'||l_c15 ||'</c15>'||EOL|| --nonlife_long_ins_calc_prem
800 '<c16>'||l_c16 ||'</c16>'||EOL|| --nonlife_short_ins_calc_prem
801 '<c17>'||to_char(to_number(isdf_calc_c.nonlife_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c17>'||EOL|| --nonlife_ins_deduction
802 '<c18>'||to_char(to_number(isdf_calc_c.social_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c18>'||EOL|| --social_ins_deduction
803 '<c19>'||to_char(to_number(isdf_calc_c.mutual_aid_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c19>'||EOL|| --mutual_aid_deduction
804 '<c30>'||isdf_employer_c.year ||'</c30>';
805 --
806 --Parameter support for print Employee No option --
807 --
808 if l_emp_no_opt = 'N' then
809 --
810 l_emp_no := '';
811 --
812 -- l_emp_no_opt = Y (SRS) or null (SS)
813 else
814 --
815 l_emp_no :=cnv_str(isdf_emp_c.employee_number);
816 --
817 end if;
818 --
819 -- Creating common xml string for page1
820 l_common_xml_page1 :=
824 '<c4>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_address)) ||'</c4>' ||EOL|| --employer_address
821 '<c1>' ||isdf_employer_c.year ||'</c1>' ||EOL|| --year
822 '<c2>' ||htmlspchar(cnv_str(isdf_employer_c.tax_office_name)) ||'</c2>' ||EOL|| --tax_office_name
823 '<c3>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_name)) ||'</c3>' ||EOL|| --employer_full_name
825 '<c5>' ||htmlspchar(cnv_str(isdf_emp_c.last_name_kana)||''||cnv_str(isdf_emp_c.first_name_kana)) ||'</c5>' ||EOL|| --kana_name
826 '<c6>' ||htmlspchar(cnv_str(isdf_emp_c.last_name)||''||cnv_str(isdf_emp_c.first_name)) ||'</c6>' ||EOL|| --name
827 '<c7>' ||htmlspchar(cnv_str(isdf_emp_c.address)) ||'</c7>' ||EOL|| --address
828 '<c7a>'||htmlspchar(l_emp_no) ||'</c7a>'||EOL|| -- employee number
829 '<c8>' ||htmlspchar(to_char(to_number(isdf_calc_c.life_gen_ins_prem),fnd_currency.get_format_mask('JPY',40))) ||'</c8>' ||EOL|| --life_gen_ins_prem
830 '<c9>' ||htmlspchar(to_char(to_number(isdf_calc_c.life_pens_ins_prem),fnd_currency.get_format_mask('JPY',40))) ||'</c9>' ||EOL|| --life_pens_ins_prem
831 '<c10>'||to_char(to_number(isdf_calc_c.life_gen_ins_calc_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c10>'||EOL|| --life_gen_ins_calc_prem
832 '<c11>'||to_char(to_number(isdf_calc_c.life_pens_ins_calc_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c11>'||EOL|| --life_pens_ins_calc_prem
833 '<c12>'||to_char(to_number(isdf_calc_c.life_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c12>'||EOL|| --life_ins_deduction
834 '<c13>'||l_c13 ||'</c13>'||EOL|| --nonlife_long_ins_prem
835 '<c14>'||l_c14 ||'</c14>'||EOL|| --nonlife_short_ins_prem
836 '<c15>'||l_c15 ||'</c15>'||EOL|| --nonlife_long_ins_calc_prem
837 '<c16>'||l_c16 ||'</c16>'||EOL|| --nonlife_short_ins_calc_prem
838 '<c17>'||to_char(to_number(isdf_calc_c.nonlife_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c17>'||EOL|| --nonlife_ins_deduction
839 '<c18>'||to_char(to_number(isdf_calc_c.social_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c18>'||EOL|| --social_ins_deduction
840 '<c19>'||to_char(to_number(isdf_calc_c.mutual_aid_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c19>'||EOL|| --mutual_aid_deduction
841 '<c20>'||to_char(to_number(isdf_calc_c.spouse_income),fnd_currency.get_format_mask('JPY',40)) ||'</c20>'||EOL|| --spouse_income
842 '<c21>'||first_digit ||'</c21>'||EOL|| --first_digit
843 '<c22>'||second_digit ||'</c22>'||EOL|| --second_digit
844 '<c23>'||to_char(to_number(isdf_mutual_c.enterprise_contract_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c23>'||EOL|| --enterprise_contract_prem
845 '<c24>'||to_char(to_number(isdf_mutual_c.pension_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c24>'||EOL|| --pension_prem
846 '<c25>'||to_char(to_number(isdf_mutual_c.disable_sup_contract_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c25>'||EOL|| --disable_sup_contract_prem
847 '<c26>'||htmlspchar(cnv_str(isdf_spouse_c.full_name_kana)) ||'</c26>'||EOL|| --sp_full_name_kana
848 '<c27>'||htmlspchar(cnv_str(isdf_spouse_c.full_name)) ||'</c27>'||EOL|| --sp_full_name
849 '<c28>'||htmlspchar(cnv_str(isdf_spouse_c.address)) ||'</c28>'||EOL|| --sp_address
850 '<c29>'||to_char(to_number(isdf_spouse_c.emp_income),fnd_currency.get_format_mask('JPY',40)) ||'</c29>'||EOL|| --sp_emp_income
851 '<c30>'||isdf_employer_c.year ||'</c30>'||EOL|| --year
852 '<p1>' ||to_char(to_number(isdf_calc_c.sp_earned_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p1>' ||EOL|| --sp_earned_income_calc
853 '<p2>' ||to_char(to_number(isdf_calc_c.sp_business_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p2>' ||EOL|| --sp_business_income_calc
854 '<p3>' ||to_char(to_number(isdf_calc_c.sp_miscellaneous_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p3>' ||EOL|| --sp_miscellaneous_income_calc
855 '<p4>' ||to_char(to_number(isdf_calc_c.sp_dividend_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p4>' ||EOL|| --sp_dividend_income_calc
856 '<p5>' ||to_char(to_number(isdf_calc_c.sp_real_estate_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p5>' ||EOL|| --sp_real_estate_income_calc
857 '<p6>' ||to_char(to_number(isdf_calc_c.sp_retirement_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p6>' ||EOL|| --sp_retirement_income_calc
858 '<p7>' ||to_char(to_number(isdf_calc_c.sp_other_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p7>' ||EOL|| --sp_other_income_calc
859 '<p8>' ||to_char(to_number(isdf_calc_c.sp_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p8>' ||EOL|| --sp_income_calc
860 '<p9>' ||to_char(to_number(isdf_spouse_inc_c.sp_earned_income),fnd_currency.get_format_mask('JPY',40)) ||'</p9>' ||EOL|| --sp_earned_income
861 '<p10>'||to_char(to_number(isdf_spouse_inc_c.sp_business_income),fnd_currency.get_format_mask('JPY',40)) ||'</p10>'||EOL|| --sp_business_income
862 '<p11>'||to_char(to_number(isdf_spouse_inc_c.sp_business_income_exp),fnd_currency.get_format_mask('JPY',40)) ||'</p11>'||EOL|| --sp_business_income_exp
866 '<p15>'||to_char(to_number(isdf_spouse_inc_c.sp_dividend_income_exp),fnd_currency.get_format_mask('JPY',40)) ||'</p15>'||EOL|| --sp_dividend_income_exp
863 '<p12>'||to_char(to_number(isdf_spouse_inc_c.sp_miscellaneous_income),fnd_currency.get_format_mask('JPY',40)) ||'</p12>'||EOL|| --sp_miscellaneous_income
864 '<p13>'||to_char(to_number(isdf_spouse_inc_c.sp_miscellaneous_income_exp),fnd_currency.get_format_mask('JPY',40))||'</p13>'||EOL|| --sp_misc_income_exp
865 '<p14>'||to_char(to_number(isdf_spouse_inc_c.sp_dividend_income),fnd_currency.get_format_mask('JPY',40)) ||'</p14>'||EOL|| --sp_dividend_income
867 '<p16>'||to_char(to_number(isdf_spouse_inc_c.sp_real_estate_income),fnd_currency.get_format_mask('JPY',40)) ||'</p16>'||EOL|| --sp_real_estate_income
868 '<p17>'||to_char(to_number(isdf_spouse_inc_c.sp_real_estate_income_exp),fnd_currency.get_format_mask('JPY',40)) ||'</p17>'||EOL|| --sp_real_estate_income_exp
869 '<p18>'||to_char(to_number(isdf_spouse_inc_c.sp_retirement_income),fnd_currency.get_format_mask('JPY',40)) ||'</p18>'||EOL|| --sp_retirement_income
870 '<p19>'||to_char(to_number(isdf_spouse_inc_c.sp_retirement_income_exp),fnd_currency.get_format_mask('JPY',40)) ||'</p19>'||EOL|| --sp_retirement_income_exp
871 '<p20>'||to_char(to_number(isdf_spouse_inc_c.sp_other_income),fnd_currency.get_format_mask('JPY',40)) ||'</p20>'||EOL|| --sp_other_income
872 '<p21>'||to_char(to_number(isdf_spouse_inc_c.sp_other_income_exp),fnd_currency.get_format_mask('JPY',40)) ||'</p21>'||EOL|| --sp_other_income_exp
873 '<p22>'||to_char(to_number(isdf_spouse_inc_c.sp_other_income_exp_dct),fnd_currency.get_format_mask('JPY',40)) ||'</p22>'; --sp_include_special_deduction
874 --
875 -- Code to determine the number of pages start.
876 --
877 SELECT count(DISTINCT(action_information_id))
878 INTO count_life_gen
879 FROM pay_jp_isdf_life_gen_v pjilg
880 WHERE pjilg.assignment_action_id = l_mag_asg_action_id
881 and pjilg.status <> 'D';
882 --
883 IF (count_life_gen = 0) THEN
884 n_life_gen := 1;
885 ELSIF ( mod(count_life_gen,3) = 0) THEN
886 n_life_gen := (count_life_gen/3);
887 ELSE
888 n_life_gen := ((count_life_gen - mod(count_life_gen,3))/3) + 1;
889 END IF;
890 --
891 SELECT count(DISTINCT(action_information_id))
892 INTO count_life_pens
893 FROM pay_jp_isdf_life_pens_v pjilp
894 WHERE pjilp.assignment_action_id = l_mag_asg_action_id
895 and pjilp.status <> 'D';
896 --
897 IF (count_life_pens = 0) THEN
898 n_life_pens := 1;
899 ELSIF ( mod(count_life_pens, 2) = 0) THEN
900 n_life_pens := (count_life_pens/2);
901 ELSE
902 n_life_pens := ((count_life_pens - mod(count_life_pens,2))/2) + 1;
903 END IF;
904 --
905 SELECT count(DISTINCT(action_information_id))
906 INTO count_nonlife
907 FROM pay_jp_isdf_nonlife_v pjin
908 WHERE pjin.assignment_action_id = l_mag_asg_action_id
909 and pjin.status <> 'D';
910 --
911 IF (count_nonlife = 0) THEN
912 n_nonlife := 1;
913 ELSIF ( mod(count_nonlife, 2) = 0) THEN
914 n_nonlife := (count_nonlife/2);
915 ELSE
916 n_nonlife := ((count_nonlife - mod(count_nonlife,2))/2) + 1;
917 END IF;
918 --
919 SELECT count(DISTINCT(action_information_id))
920 INTO count_social
921 FROM pay_jp_isdf_social_v pjis
922 WHERE pjis.assignment_action_id = l_mag_asg_action_id
923 and pjis.status <> 'D';
924 --
925 IF (count_social = 0) THEN
926 n_social := 1;
927 ELSIF ( mod(count_social, 3) = 0) THEN
928 n_social := (count_social/3);
929 ELSE
930 n_social := ((count_social - mod(count_social,3))/3) + 1;
931 END IF;
932 --
933 IF (n_life_gen >= n_life_pens) THEN
934 l1 := n_life_gen;
935 ELSE
936 l1 := n_life_pens;
937 END IF;
938 --
939 IF (n_nonlife >= n_social) THEN
940 l2 := n_nonlife;
941 ELSE
942 l2 := n_social;
943 END IF;
944 --
945 IF (l1 >= l2) THEN
946 no_of_pages := l1;
947 ELSE
948 no_of_pages := l2;
949 END IF;
950 --
951 -- Code to determine the number of pages end.
952 OPEN cur_isdf_life_gen (l_mag_asg_action_id);
953 FETCH cur_isdf_life_gen BULK COLLECT INTO ins_company_name_lg, ins_type_lg, ins_period_lg, contractor_name_lg, beneficiary_name_lg, beneficiary_relship_lg, annual_prem_lg;
954 CLOSE cur_isdf_life_gen;
955 --
956 OPEN cur_isdf_life_pens (l_mag_asg_action_id);
957 FETCH cur_isdf_life_pens BULK COLLECT INTO ins_company_name_lp, ins_type_lp, ins_period_start_date_lp, ins_period_lp, contractor_name_lp, beneficiary_name_lp, beneficiary_relship_lp, annual_prem_lp; CLOSE cur_isdf_life_pens;
958 --
959 OPEN cur_isdf_nonlife (l_mag_asg_action_id);
960 FETCH cur_isdf_nonlife BULK COLLECT INTO nonlife_ins_term_type_nl, ins_company_name_nl, ins_type_nl, ins_period_nl, contractor_name_nl, beneficiary_name_nl, beneficiary_relship_nl, maturity_repayment_nl, annual_prem_nl;
961 CLOSE cur_isdf_nonlife;
962 --
963 OPEN cur_isdf_social (l_mag_asg_action_id);
964 FETCH cur_isdf_social BULK COLLECT INTO ins_type_s, ins_payee_name_s, debtor_name_s, beneficiary_relship_s, annual_prem_s;
965 CLOSE cur_isdf_social;
966 --
967 i := 0;
968 --
969 hr_utility.set_location('NO. OF PAGES :', no_of_pages);
970 --
971 WHILE i < no_of_pages
972 LOOP
973 --
974 a := 3 * i + 1;
975 b := 3 * i + 2;
976 c := 3 * i + 3;
977 d := 2 * i + 1;
978 e := 2 * i + 2;
979 --
980 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML A:', a);
981 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML B:', b);
985 --
982 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML C:', c);
983 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML D:', d);
984 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML E:', e);
986 IF (i = 0) THEN
987 l_xml := '<page>'||EOL||l_common_xml_page1||EOL;
988 ELSE
989 l_xml := '<page>'||EOL||l_common_xml||EOL;
990 END IF;
991 --
992 -- writing first part of xml to vXMLtable
993 vXMLTable(vCtr).xmlstring := l_xml;
994 vCtr := vCtr + 1;
995 --
996 l_t48 := null;
997 l_t56 := null;
998 --
999 l_t48a := null;
1000 l_t56a := null;
1001 l_t48b := null;
1002 l_t56b := null;
1003 --
1004 IF (ins_company_name_lg.count >= a) THEN
1005 --
1006 l_xml := '<t1>'||htmlspchar(cnv_str(ins_company_name_lg(a))) ||'</t1>'||EOL; --ins_company_name_lg1
1007 l_xml := l_xml||'<t2>'||htmlspchar(cnv_str(ins_type_lg(a))) ||'</t2>'||EOL; --ins_type_lg1
1008 l_xml := l_xml||'<t3>'||htmlspchar(cnv_str(ins_period_lg(a))) ||'</t3>'||EOL; --ins_period_lg1
1009 l_xml := l_xml||'<t4>'||htmlspchar(cnv_str(contractor_name_lg(a))) ||'</t4>'||EOL; --contractor_name_lg1
1010 l_xml := l_xml||'<t5>'||htmlspchar(cnv_str(beneficiary_name_lg(a))) ||'</t5>'||EOL; --beneficiary_name_lg1
1011 l_xml := l_xml||'<t6>'||htmlspchar(cnv_str(beneficiary_relship_lg(a)))||'</t6>'||EOL; --beneficiary_relship_lg1
1012 l_xml := l_xml||'<t7>'||htmlspchar(to_char(to_number(annual_prem_lg(a)),fnd_currency.get_format_mask('JPY',40)))||'</t7>' ||EOL; --annual_prem_lg1
1013 --
1014 ELSE
1015 --
1016 l_xml := '<t1>'||' '||'</t1>'||EOL; --ins_company_name_lg1
1017 l_xml := l_xml||'<t2>'||' '||'</t2>'||EOL; --ins_type_lg1
1018 l_xml := l_xml||'<t3>'||' '||'</t3>'||EOL; --ins_period_lg1
1019 l_xml := l_xml||'<t4>'||' '||'</t4>'||EOL; --contractor_name_lg1
1020 l_xml := l_xml||'<t5>'||' '||'</t5>'||EOL; --beneficiary_name_lg1
1021 l_xml := l_xml||'<t6>'||' '||'</t6>'||EOL; --beneficiary_relship_lg1
1022 l_xml := l_xml||'<t7>'||' '||'</t7>'||EOL; --annual_prem_lg1
1023 --
1024 END IF;
1025 --
1026 IF (ins_company_name_lg.count >= b) THEN
1027 --
1028 l_xml := l_xml||'<t8>' ||htmlspchar(cnv_str(ins_company_name_lg(b))) ||'</t8>' ||EOL; --ins_company_name_lg2
1029 l_xml := l_xml||'<t9>' ||htmlspchar(cnv_str(ins_type_lg(b))) ||'</t9>' ||EOL; --ins_type_lg2
1030 l_xml := l_xml||'<t10>'||htmlspchar(cnv_str(ins_period_lg(b))) ||'</t10>'||EOL; --ins_period_lg2
1031 l_xml := l_xml||'<t11>'||htmlspchar(cnv_str(contractor_name_lg(b))) ||'</t11>'||EOL; --contractor_name_lg2
1032 l_xml := l_xml||'<t12>'||htmlspchar(cnv_str(beneficiary_name_lg(b))) ||'</t12>'||EOL; --beneficiary_name_lg2
1033 l_xml := l_xml||'<t13>'||htmlspchar(cnv_str(beneficiary_relship_lg(b)))||'</t13>'||EOL; --beneficiary_relship_lg2
1034 l_xml := l_xml||'<t14>'||htmlspchar(to_char(to_number(annual_prem_lg(b)),fnd_currency.get_format_mask('JPY',40)))||'</t14>'||EOL; --annual_prem_lg2
1035 --
1036 ELSE
1037 --
1038 l_xml := l_xml||'<t8>' ||' '||'</t8>' ||EOL; --ins_company_name_lg2
1039 l_xml := l_xml||'<t9>' ||' '||'</t9>' ||EOL; --ins_type_lg2
1040 l_xml := l_xml||'<t10>'||' '||'</t10>'||EOL; --ins_period_lg2
1041 l_xml := l_xml||'<t11>'||' '||'</t11>'||EOL; --contractor_name_lg2
1042 l_xml := l_xml||'<t12>'||' '||'</t12>'||EOL; --beneficiary_name_lg2
1043 l_xml := l_xml||'<t13>'||' '||'</t13>'||EOL; --beneficiary_relship_lg2
1044 l_xml := l_xml||'<t14>'||' '||'</t14>'||EOL; --annual_prem_lg2
1045 --
1046 END IF;
1047 --
1048 IF (ins_company_name_lg.count >= c) THEN
1049 --
1050 l_xml := l_xml||'<t15>'||htmlspchar(cnv_str(ins_company_name_lg(c))) ||'</t15>'||EOL; --ins_company_name_lg3
1051 l_xml := l_xml||'<t16>'||htmlspchar(cnv_str(ins_type_lg(c))) ||'</t16>'||EOL; --ins_type_lg3
1052 l_xml := l_xml||'<t17>'||htmlspchar(cnv_str(ins_period_lg(c))) ||'</t17>'||EOL; --ins_period_lg3
1053 l_xml := l_xml||'<t18>'||htmlspchar(cnv_str(contractor_name_lg(c))) ||'</t18>'||EOL; --contractor_name_lg3
1054 l_xml := l_xml||'<t19>'||htmlspchar(cnv_str(beneficiary_name_lg(c))) ||'</t19>'||EOL; --beneficiary_name_lg3
1055 l_xml := l_xml||'<t20>'||htmlspchar(cnv_str(beneficiary_relship_lg(c)))||'</t20>'||EOL; --beneficiary_relship_lg3
1056 l_xml := l_xml||'<t21>'||htmlspchar(to_char(to_number(annual_prem_lg(c)),fnd_currency.get_format_mask('JPY',40)))||'</t21>'||EOL; --annual_prem_lg3
1057 --
1058 ELSE
1059 --
1060 l_xml := l_xml||'<t15>'||' '||'</t15>'||EOL; --ins_company_name_lg3
1061 l_xml := l_xml||'<t16>'||' '||'</t16>'||EOL; --ins_type_lg3
1062 l_xml := l_xml||'<t17>'||' '||'</t17>'||EOL; --ins_period_lg3
1063 l_xml := l_xml||'<t18>'||' '||'</t18>'||EOL; --contractor_name_lg3
1064 l_xml := l_xml||'<t19>'||' '||'</t19>'||EOL; --beneficiary_name_lg3
1065 l_xml := l_xml||'<t20>'||' '||'</t20>'||EOL; --beneficiary_relship_lg3
1066 l_xml := l_xml||'<t21>'||' '||'</t21>'||EOL; --annual_prem_lg3
1067 --
1068 END IF;
1069 --
1070 IF (ins_company_name_lp.count >= d) THEN
1071 --
1072 select to_char(ins_period_start_date_lp(d),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1073 into ps_date
1074 from dual;
1075 --
1076 -- ps_date_length := length(ins_period_start_date_lp(d));
1077 ps_date_length := length(ps_date);
1078 --
1082 l_xml := l_xml||'<t25>'||substr(ps_date,ps_date_length - 3,2) ||'</t25>'||EOL; --ins_period_start_month_lp1
1079 l_xml := l_xml||'<t22>'||htmlspchar(cnv_str(ins_company_name_lp(d))) ||'</t22>'||EOL; --ins_company_name_lp1
1080 l_xml := l_xml||'<t23>'||htmlspchar(cnv_str(ins_type_lp(d))) ||'</t23>'||EOL; --ins_type_lp1
1081 l_xml := l_xml||'<t24>'||substr(ps_date,ps_date_length - 5,2) ||'</t24>'||EOL; --ins_period_start_year_lp1
1083 l_xml := l_xml||'<t26>'||substr(ps_date,ps_date_length - 1,2) ||'</t26>'||EOL; --ins_period_start_day_lp1
1084 l_xml := l_xml||'<t27>'||htmlspchar(cnv_str(ins_period_lp(d))) ||'</t27>'||EOL; --ins_period_lp1
1085 l_xml := l_xml||'<t28>'||htmlspchar(cnv_str(contractor_name_lp(d))) ||'</t28>'||EOL; --contractor_name_lp1
1086 l_xml := l_xml||'<t29>'||htmlspchar(cnv_str(beneficiary_name_lp(d))) ||'</t29>'||EOL; --beneficiary_name_lp1
1087 l_xml := l_xml||'<t30>'||htmlspchar(cnv_str(beneficiary_relship_lp(d)))||'</t30>'||EOL; --beneficiary_relship_lp1
1088 l_xml := l_xml||'<t31>'||htmlspchar(to_char(to_number(annual_prem_lp(d)),fnd_currency.get_format_mask('JPY',40)))||'</t31>'||EOL; --annual_prem_lp1
1089 --
1090 ELSE
1091 --
1092 l_xml := l_xml||'<t22>'||' '||'</t22>'||EOL; --ins_company_name_lp1
1093 l_xml := l_xml||'<t23>'||' '||'</t23>'||EOL; --ins_type_lp1
1094 l_xml := l_xml||'<t24>'||' '||'</t24>'||EOL; --ins_period_start_year_lp1
1095 l_xml := l_xml||'<t25>'||' '||'</t25>'||EOL; --ins_period_start_month_lp1
1096 l_xml := l_xml||'<t26>'||' '||'</t26>'||EOL; --ins_period_start_day_lp1
1097 l_xml := l_xml||'<t27>'||' '||'</t27>'||EOL; --ins_period_lp1
1098 l_xml := l_xml||'<t28>'||' '||'</t28>'||EOL; --contractor_name_lp1
1099 l_xml := l_xml||'<t29>'||' '||'</t29>'||EOL; --beneficiary_name_lp1
1100 l_xml := l_xml||'<t30>'||' '||'</t30>'||EOL; --beneficiary_relship_ lp1
1101 l_xml := l_xml||'<t31>'||' '||'</t31>'||EOL; --annual_prem_lp1
1102 --
1103 END IF;
1104 --
1105 IF (ins_company_name_lp.count >= e) THEN
1106 --
1107 select to_char(ins_period_start_date_lp(e),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1108 into ps_date
1109 from dual;
1110 --
1111 ps_date_length := length(ps_date);
1112 --
1113 l_xml := l_xml||'<t32>'||htmlspchar(cnv_str(ins_company_name_lp(e))) ||'</t32>'||EOL; --ins_company_name_lp2
1114 l_xml := l_xml||'<t33>'||htmlspchar(cnv_str(ins_type_lp(e))) ||'</t33>'||EOL; --ins_type_lp2
1115 l_xml := l_xml||'<t34>'||substr(ps_date,ps_date_length - 5,2) ||'</t34>'||EOL; --ins_period_start_year_lp2
1116 l_xml := l_xml||'<t35>'||substr(ps_date, ps_date_length - 3,2) ||'</t35>'||EOL; --ins_period_start_month_lp2
1117 l_xml := l_xml||'<t36>'||substr(ps_date, ps_date_length - 1,2) ||'</t36>'||EOL; --ins_period_start_day_lp2
1118 l_xml := l_xml||'<t37>'||htmlspchar(cnv_str(ins_period_lp(e))) ||'</t37>'||EOL; --ins_period_lp2
1119 l_xml := l_xml||'<t38>'||htmlspchar(cnv_str(contractor_name_lp(e))) ||'</t38>'||EOL; --contractor_name_lp2
1120 l_xml := l_xml||'<t39>'||htmlspchar(cnv_str(beneficiary_name_lp(e))) ||'</t39>'||EOL; --beneficiary_name_lp2
1121 l_xml := l_xml||'<t40>'||htmlspchar(cnv_str(beneficiary_relship_lp(e)))||'</t40>'||EOL; --beneficiary_relship_lp2
1122 l_xml := l_xml||'<t41>'||htmlspchar(to_char(to_number(annual_prem_lp(e)),fnd_currency.get_format_mask('JPY',40)))||'</t41>'||EOL; --annual_prem_lp2
1123 --
1124 ELSE
1125 --
1126 l_xml := l_xml||'<t32>'||' '||'</t32>'||EOL; --ins_company_name_lp2
1127 l_xml := l_xml||'<t33>'||' '||'</t33>'||EOL; --ins_type_lp2
1128 l_xml := l_xml||'<t34>'||' '||'</t34>'||EOL; --ins_period_start_year_lp2
1129 l_xml := l_xml||'<t35>'||' '||'</t35>'||EOL; --ins_period_start_month_lp2
1130 l_xml := l_xml||'<t36>'||' '||'</t36>'||EOL; --ins_period_start_day_lp2
1131 l_xml := l_xml||'<t37>'||' '||'</t37>'||EOL; --ins_period_lp2
1132 l_xml := l_xml||'<t38>'||' '||'</t38>'||EOL; --contractor_name_lp2
1133 l_xml := l_xml||'<t39>'||' '||'</t39>'||EOL; --beneficiary_name_lp2
1134 l_xml := l_xml||'<t40>'||' '||'</t40>'||EOL; --beneficiary_relship_lp2
1135 l_xml := l_xml||'<t41>'||' '||'</t41>'||EOL; --annual_prem_lp2
1136 --
1137 END IF;
1138 --
1139 -- Writing l_xml to vXMLTable.
1140 vXMLTable(vCtr).xmlstring := l_xml;
1141 vCtr := vCtr + 1;
1142 --
1143 IF (ins_company_name_nl.count >= d) THEN
1144 --
1145 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
1146 --
1147 l_t48 := cnv_str(maturity_repayment_nl(d),1,3);
1148 --
1149 else
1150 --
1151 if nonlife_ins_term_type_nl(d) = 'EQ' then
1152 --
1153 l_t48a := g_msg_circle;
1154 --
1155 elsif nonlife_ins_term_type_nl(d) = 'L' then
1156 --
1157 l_t48b := g_msg_circle;
1158 --
1159 end if;
1160 --
1161 end if;
1162 --
1163 l_xml := '<t42>' ||htmlspchar(cnv_str(ins_company_name_nl(d))) ||'</t42>' ||EOL; --ins_company_name_nl1
1164 l_xml := l_xml||'<t43>' ||htmlspchar(cnv_str(ins_type_nl(d))) ||'</t43>' ||EOL; --ins_type_nl1
1165 l_xml := l_xml||'<t44>' ||htmlspchar(cnv_str(ins_period_nl(d))) ||'</t44>' ||EOL; --ins_period_nl1
1166 l_xml := l_xml||'<t45>' ||htmlspchar(cnv_str(contractor_name_nl(d))) ||'</t45>' ||EOL; --contractor_name_nl1
1167 l_xml := l_xml||'<t46>' ||htmlspchar(cnv_str(beneficiary_name_nl(d))) ||'</t46>' ||EOL; --beneficiary_name_nl1
1171 l_xml := l_xml||'<t48b>'||l_t48b ||'</t48b>'||EOL; --nonlife_ins_term_type_nl1
1168 l_xml := l_xml||'<t47>' ||htmlspchar(cnv_str(beneficiary_relship_nl(d)))||'</t47>' ||EOL; --beneficiary_relship_nl1
1169 l_xml := l_xml||'<t48>' ||htmlspchar(l_t48) ||'</t48>' ||EOL; --maturity_repayment_nl1
1170 l_xml := l_xml||'<t48a>'||l_t48a ||'</t48a>'||EOL; --nonlife_ins_term_type_nl1
1172 l_xml := l_xml||'<t49>' ||htmlspchar(to_char(to_number(annual_prem_nl(d)), fnd_currency.get_format_mask('JPY',40)))||'</t49>' ||EOL; --annual_prem_nl1
1173 --
1174 ELSE
1175 --
1176 l_xml := '<t42>' ||' '||'</t42>' ||EOL; --ins_company_name_nl1
1177 l_xml := l_xml||'<t43>' ||' '||'</t43>' ||EOL; --ins_type_nl1
1178 l_xml := l_xml||'<t44>' ||' '||'</t44>' ||EOL; --ins_period_nl1
1179 l_xml := l_xml||'<t45>' ||' '||'</t45>' ||EOL; --contractor_name_nl1
1180 l_xml := l_xml||'<t46>' ||' '||'</t46>' ||EOL; --beneficiary_name_nl1
1181 l_xml := l_xml||'<t47>' ||' '||'</t47>' ||EOL; --beneficiary_relship_nl1
1182 l_xml := l_xml||'<t48>' ||' '||'</t48>' ||EOL; --maturity_repayment_nl1
1183 l_xml := l_xml||'<t48a>'||' '||'</t48a>'||EOL; --nonlife_ins_term_type_nl1
1184 l_xml := l_xml||'<t48b>'||' '||'</t48b>'||EOL; --nonlife_ins_term_type_nl1
1185 l_xml := l_xml||'<t49>' ||' '||'</t49>' ||EOL; --annual_prem_nl1
1186 --
1187 END IF;
1188 --
1189 IF (ins_company_name_nl.count >= e) THEN
1190 --
1191 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
1192 --
1193 l_t56 := cnv_str(maturity_repayment_nl(e));
1194 --
1195 else
1196 --
1197 if nonlife_ins_term_type_nl(e) = 'EQ' then
1198 --
1199 l_t56a := g_msg_circle;
1200 --
1201 elsif nonlife_ins_term_type_nl(e) = 'L' then
1202 --
1203 l_t56b := g_msg_circle;
1204 --
1205 end if;
1206 --
1207 end if;
1208 --
1209 l_xml := l_xml||'<t50>' ||htmlspchar(cnv_str(ins_company_name_nl(e))) ||'</t50>' ||EOL; --ins_company_name_nl2
1210 l_xml := l_xml||'<t51>' ||htmlspchar(cnv_str(ins_type_nl(e))) ||'</t51>' ||EOL; --ins_type_nl2
1211 l_xml := l_xml||'<t52>' ||htmlspchar(cnv_str(ins_period_nl(e))) ||'</t52>' ||EOL; --ins_period_nl2
1212 l_xml := l_xml||'<t53>' ||htmlspchar(cnv_str(contractor_name_nl(e))) ||'</t53>' ||EOL; --contractor_name_nl2
1213 l_xml := l_xml||'<t54>' ||htmlspchar(cnv_str(beneficiary_name_nl(e))) ||'</t54>' ||EOL; --beneficiary_name_nl2
1214 l_xml := l_xml||'<t55>' ||htmlspchar(cnv_str(beneficiary_relship_nl(e)))||'</t55>' ||EOL; --beneficiary_relship_nl2
1215 l_xml := l_xml||'<t56>' ||htmlspchar(l_t56) ||'</t56>' ||EOL; --maturity_repayment_nl2
1216 l_xml := l_xml||'<t56a>'||l_t56a ||'</t56a>'||EOL; --nonlife_ins_term_type_nl2
1217 l_xml := l_xml||'<t56b>'||l_t56b ||'</t56b>'||EOL; --nonlife_ins_term_type_nl2
1218 l_xml := l_xml||'<t57>' ||htmlspchar(to_char(to_number(annual_prem_nl(e)),fnd_currency.get_format_mask('JPY',40)))||'</t57>' ||EOL; --annual_prem_nl2
1219 --
1220 ELSE
1221 --
1222 l_xml := l_xml||'<t50>' ||' '||'</t50>' ||EOL; --ins_company_name_nl2
1223 l_xml := l_xml||'<t51>' ||' '||'</t51>' ||EOL; --ins_type_nl2
1224 l_xml := l_xml||'<t52>' ||' '||'</t52>' ||EOL; --ins_period_nl2
1225 l_xml := l_xml||'<t53>' ||' '||'</t53>' ||EOL; --contractor_name_nl2
1226 l_xml := l_xml||'<t54>' ||' '||'</t54>' ||EOL; --beneficiary_name_nl2
1227 l_xml := l_xml||'<t55>' ||' '||'</t55>' ||EOL; --beneficiary_relship_nl2
1228 l_xml := l_xml||'<t56>' ||' '||'</t56>' ||EOL; --maturity_repayment_nl2
1229 l_xml := l_xml||'<t56a>'||' '||'</t56a>'||EOL; --nonlife_ins_term_type_nl2
1230 l_xml := l_xml||'<t56b>'||' '||'</t56b>'||EOL; --nonlife_ins_term_type_nl2
1231 l_xml := l_xml||'<t57>' ||' '||'</t57>' ||EOL; --annual_prem_nl2
1232 --
1233 END IF;
1234 --
1235 IF (ins_type_s.count >= a) THEN
1236 --
1237 l_xml := l_xml||'<t58>'||htmlspchar(cnv_str(ins_type_s(a))) ||'</t58>'||EOL; --ins_type_s1
1238 l_xml := l_xml||'<t59>'||htmlspchar(cnv_str(ins_payee_name_s(a))) ||'</t59>'||EOL; --ins_payee_name_s1
1239 l_xml := l_xml||'<t60>'||htmlspchar(cnv_str(debtor_name_s(a))) ||'</t60>'||EOL; --debtor_name_s1
1240 l_xml := l_xml||'<t61>'||htmlspchar(cnv_str(beneficiary_relship_s(a)))||'</t61>'||EOL; --beneficiary_relship_s1
1241 l_xml := l_xml||'<t62>'||to_char(to_number(annual_prem_s(a)),fnd_currency.get_format_mask('JPY',40))||'</t62>'||EOL; --annual_prem_s1
1242 --
1243 ELSE
1244 --
1245 l_xml := l_xml||'<t58>'||' '||'</t58>'||EOL; --ins_type_s1
1246 l_xml := l_xml||'<t59>'||' '||'</t59>'||EOL; --ins_payee_name_s1
1247 l_xml := l_xml||'<t60>'||' '||'</t60>'||EOL; --debtor_name_s1
1248 l_xml := l_xml||'<t61>'||' '||'</t61>'||EOL; --beneficiary_relship_s1
1249 l_xml := l_xml||'<t62>'||' '||'</t62>'||EOL; --annual_prem_s1
1250 --
1251 END IF;
1252 --
1253 IF (ins_type_s.count >= b) THEN
1254 --
1255 l_xml := l_xml||'<t63>'||htmlspchar(cnv_str(ins_type_s(b))) ||'</t63>'||EOL; --ins_type_s2
1256 l_xml := l_xml||'<t64>'||htmlspchar(cnv_str(ins_payee_name_s(b))) ||'</t64>'||EOL; --ins_payee_name_s2
1257 l_xml := l_xml||'<t65>'||htmlspchar(cnv_str(debtor_name_s(b))) ||'</t65>'||EOL; --debtor_name_s2
1258 l_xml := l_xml||'<t66>'||htmlspchar(cnv_str(beneficiary_relship_s(b)))||'</t66>'||EOL; --beneficiary_relship_s2
1259 l_xml := l_xml||'<t67>'||to_char(to_number(annual_prem_s(b)),fnd_currency.get_format_mask('JPY',40))||'</t67>'||EOL; --annual_prem_s2
1260 --
1261 ELSE
1262 --
1263 l_xml := l_xml||'<t63>'||' '||'</t63>'||EOL; --ins_type_s2
1264 l_xml := l_xml||'<t64>'||' '||'</t64>'||EOL; --ins_payee_name_s2
1265 l_xml := l_xml||'<t65>'||' '||'</t65>'||EOL; --debtor_name_s2
1266 l_xml := l_xml||'<t66>'||' '||'</t66>'||EOL; --beneficiary_relship_s2
1267 l_xml := l_xml||'<t67>'||' '||'</t67>'||EOL; --annual_prem_s2
1268 --
1269 END IF;
1270 --
1271 IF (ins_type_s.count >= c) THEN
1272 --
1273 l_xml := l_xml||'<t68>'||htmlspchar(cnv_str(ins_type_s(c))) ||'</t68>'||EOL; --ins_type_s3
1274 l_xml := l_xml||'<t69>'||htmlspchar(cnv_str(ins_payee_name_s(c))) ||'</t69>'||EOL; --ins_payee_name_s3
1275 l_xml := l_xml||'<t70>'||htmlspchar(cnv_str(debtor_name_s(c))) ||'</t70>'||EOL; --debtor_name_s3
1276 l_xml := l_xml||'<t71>'||htmlspchar(cnv_str(beneficiary_relship_s(c)))||'</t71>'||EOL; --beneficiary_relship_s3
1277 l_xml := l_xml||'<t72>'||to_char(to_number(annual_prem_s(c)),fnd_currency.get_format_mask('JPY',40))||'</t72>'||EOL; --annual_prem_s3
1278 --
1279 ELSE
1280 --
1281 l_xml := l_xml||'<t68>'||' '||'</t68>'||EOL; --ins_type_s3
1282 l_xml := l_xml||'<t69>'||' '||'</t69>'||EOL; --ins_payee_name_s3
1283 l_xml := l_xml||'<t70>'||' '||'</t70>'||EOL; --debtor_name_s3
1284 l_xml := l_xml||'<t71>'||' '||'</t71>'||EOL; --beneficiary_relship_s3
1285 l_xml := l_xml||'<t72>'||' '||'</t72>'||EOL; --annual_prem_s3
1286 --
1287 END IF;
1288 --
1289 l_xml := l_xml||'</page>'||EOL;
1290 --
1291 vXMLTable(vCtr).xmlstring := l_xml;
1292 vCtr := vCtr + 1;
1293 --
1294 i := i + 1;
1295 --
1296 END LOOP;
1297 --
1298 -- Code to generate XML for second page of template starts
1299 -- dummy field is added to get as many prints of second page as
1300 -- the number of employees for whom the report is run.
1301 l_xml2 := '</isdf1>'||EOL||
1302 '<isdf2>' ||EOL||
1303 '<dummy></dummy>'||EOL|| -- This is dummy field
1304 '</isdf2>'||EOL||'</isdf>'||EOL ;
1305 --
1306 vXMLTable(vCtr).xmlstring := l_xml2;
1307 vCtr := vCtr + 1;
1308 --
1309 END assact_xml;
1310 --
1311 /****************************************************************************
1312 Name : WritetoCLOB
1313 Arguments : returns XML
1314 Description : This procedure selects the xml from vXMLTable and writes it
1315 into a clob variable. This clob variable is then returned.
1316 *****************************************************************************/
1317 PROCEDURE WritetoCLOB (p_write_xml OUT NOCOPY CLOB)
1318 IS
1319 l_xfdf_string CLOB;
1320 ctr_table NUMBER;
1321 tempclob clob;
1322 BEGIN
1323 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1327 dbms_lob.writeAppend(l_xfdf_string,
1324 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1325 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1326 LOOP
1328 length(vXMLTable(ctr_table).xmlstring),
1329 vXMLTable(ctr_table).xmlstring );
1330 END LOOP;
1331 p_write_xml := l_xfdf_string;
1332 hr_utility.set_location('Out of loop ', 99);
1333 dbms_lob.close(l_xfdf_string);
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1337 HR_UTILITY.RAISE_ERROR;
1338 END WritetoCLOB;
1339 --
1340 /****************************************************************************
1341 Name : get_cp_xml
1342 Arguments : p_assignment_action_id
1343 p_xml
1344 Description : This procedure creates and returns the xml for the
1345 assignment_action_id passed as parameter.
1346 *****************************************************************************/
1347 PROCEDURE get_cp_xml(p_assignment_action_id IN NUMBER,
1348 p_xml OUT NOCOPY CLOB) IS
1349 BEGIN
1350 assact_xml(p_assignment_action_id);
1351 WritetoCLOB (p_xml);
1352 END get_cp_xml;
1353 --
1354 /****************************************************************************
1355 Name : get_ss_xml
1356 Arguments : p_assignment_action_id
1357 p_xml
1358 Description : This procedure creates and returns the xml for the
1359 assignment_action_id passed as parameter. This is called
1360 for single report from Self-Service page.
1361 *****************************************************************************/
1362 PROCEDURE get_ss_xml(p_assignment_action_id IN NUMBER,
1363 p_xml OUT NOCOPY CLOB) IS
1364 p_ss_xml CLOB;
1365 l_header CHAR(200);
1366 l_footer VARCHAR2(50);
1367 l_xml CLOB;
1368 BEGIN
1369 l_header := '<?xml version="1.0" encoding="UTF-8"?>' || EOL ||'<ROOT>';
1370 l_footer := '</ROOT>';
1371 assact_xml(p_assignment_action_id);
1372 WritetoCLOB(p_ss_xml);
1373 dbms_lob.createtemporary(l_xml,TRUE) ;
1374 dbms_lob.writeAppend(l_xml,
1375 length(l_header),
1376 l_header);
1377 dbms_lob.append(l_xml, p_ss_xml);
1378 dbms_lob.writeAppend(l_xml,
1379 length(l_footer),
1380 l_footer);
1381 p_xml := l_xml ;
1382 END get_ss_xml;
1383 --
1384 /****************************************************************************
1385 Name : generate_xml
1386 Description : This procedure fetches archived data, converts it to XML
1387 format and appends to pay_mag_tape.g_clob_value.
1388 *****************************************************************************/
1389 PROCEDURE generate_xml AS
1390 l_old_assact_id NUMBER;
1391 l_final_xml_string CLOB;
1392 xml_string1 VARCHAR2(2000);
1393 l_pact_id NUMBER;
1394 l_cur_pact NUMBER;
1395 l_legislative_parameters VARCHAR(2000);
1396 l_cur_assact NUMBER ;
1397 l_proc_name VARCHAR2(60) ;
1398 l_offset NUMBER;
1399 l_amount NUMBER;
1400 --
1401 BEGIN
1402 --
1403 IF g_debug THEN
1404 l_proc_name := g_proc_name || 'GENERATE_XML';
1405 hr_utility.trace ('Entering '||l_proc_name);
1406 END IF ;
1407 --
1408 l_cur_assact := pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID' );
1409 l_cur_pact := pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID' );
1410 --
1411 SELECT legislative_parameters
1412 INTO l_legislative_parameters
1413 FROM pay_payroll_actions
1414 WHERE payroll_action_id = l_cur_pact;
1415 --
1416 l_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',l_legislative_parameters));
1417 l_emp_no_opt := pay_core_utils.get_parameter('PRN_EMP_NO',l_legislative_parameters);
1418 --
1419 SELECT paa1.assignment_action_id
1420 INTO l_old_assact_id
1421 FROM pay_assignment_actions paa,
1422 pay_assignment_actions paa1
1423 WHERE paa.assignment_action_id = l_cur_assact
1424 AND paa.assignment_id = paa1.assignment_id
1425 AND paa1.payroll_action_id = l_pact_id;
1426 --
1427 get_cp_xml(l_old_assact_id, l_final_xml_string);
1428 --
1429 l_offset := 1 ;
1430 l_amount := 500;
1431 --
1432 LOOP
1433 xml_string1 := null;
1434 dbms_lob.read(l_final_xml_string,l_amount,l_offset,xml_string1);
1435 pay_core_files.write_to_magtape_lob(xml_string1);
1436 l_offset := l_offset + l_amount ;
1437 END LOOP;
1438 EXCEPTION
1439 WHEN no_data_found THEN
1440 hr_utility.trace ('exiting from loop');
1441 --
1442 IF g_debug THEN
1443 hr_utility.trace ('Leaving '||l_proc_name);
1444 END IF ;
1445 END generate_xml;
1446 --
1447 /****************************************************************************
1448 Name : gen_xml_header
1449 Description : This procedure generates XML header information and appends to
1450 pay_mag_tape.g_clob_value.
1451 *****************************************************************************/
1452 PROCEDURE gen_xml_header AS
1453 l_proc_name varchar2(100);
1454 l_buf varchar2(2000);
1455 --
1456 BEGIN
1457 if g_debug then
1458 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
1459 hr_utility.trace ('Entering '||l_proc_name);
1460 end if ;
1461 --
1462 vXMLTable.DELETE; -- delete the pl/sql table
1463 --
1464 -- l_buf := '<?xml version="1.0" encoding="UTF-8"?>'||EOL ;
1465 l_buf := EOL ||'<ROOT>'||EOL ;
1466 --
1467 pay_core_files.write_to_magtape_lob(l_buf);
1468 --
1469 if g_debug then
1470 hr_utility.trace ('CLOB contents after appending header information');
1471 hr_utility.trace ('Leaving '||l_proc_name);
1472 end if ;
1473 END gen_xml_header;
1474 --
1475 /****************************************************************************
1476 Name : gen_xml_footer
1477 Desc : Footer
1478 *****************************************************************************/
1479 PROCEDURE gen_xml_footer AS
1480 l_buf varchar2(2000) ;
1481 l_proc_name varchar2(100);
1482 BEGIN
1483 --
1484 if g_debug then
1485 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
1486 hr_utility.trace ('Entering '||l_proc_name);
1487 end if ;
1488 l_buf := '</ROOT>' ;
1489 --
1490 pay_core_files.write_to_magtape_lob(l_buf);
1491 --
1492 if g_debug then
1493 hr_utility.trace ('CLOB contents after appending footer information');
1494 hr_utility.trace ('Leaving '||l_proc_name);
1495 end if ;
1496 --
1497 END gen_xml_footer;
1498 --
1499 /****************************************************************************
1500 Function Name : submit_report
1501 Arguments :
1502 Description :
1503 *****************************************************************************/
1504 function submit_report(p_pact_id IN NUMBER,
1505 p_assset_id IN NUMBER,
1506 p_eff_date IN VARCHAR2) return number
1507 is
1508 l_request_id number;
1509 l_phase VARCHAR2(100);
1510 l_status VARCHAR2(100);
1511 l_dev_status VARCHAR2(100);
1512 l_dev_phase VARCHAR2(100);
1513 l_message VARCHAR2(2000);
1514 l_action_completed BOOLEAN;
1515 l_req_id NUMBER;
1516 --
1517 begin
1518 -- Submit the request
1519 --
1520 l_request_id := fnd_request.submit_request( Application => 'PAY',
1521 Program => 'PAYJPXML',
1522 Description => 'JP Life Insurance Notification Report',
1523 argument1 => 'ARCHIVE',
1524 argument2 => 'XML',
1525 argument3 => 'JP',
1526 argument4 => NULL,
1527 argument5 => p_eff_date,
1528 argument6 => 'XML',
1529 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
1530 argument8 => NULL,
1531 argument9 => NULL,
1532 argument10 => p_pact_id,
1533 argument11 => 'PAYROLL_ACTION_ID='||p_pact_id,
1534 argument12 => p_assset_id,
1535 argument13 => 'ASSIGNMENT_SET_ID='||p_assset_id);
1536 --
1537 -- Check the status
1538 if l_request_id <> 0 then
1539 -- Save the request and wait for completion
1540 Commit;
1541 l_dev_phase := 'ZZZ';
1542 WHILE (l_dev_phase <> 'COMPLETE')
1543 LOOP
1544 l_action_completed := FND_CONCURRENT.WAIT_FOR_REQUEST(
1545 request_id => l_request_id
1546 ,interval => 1
1547 ,max_wait => 10
1548 ,phase => l_phase
1549 ,status => l_status
1550 ,dev_phase => l_dev_phase
1551 ,dev_status => l_dev_status
1552 ,message => l_message);
1553 END LOOP;
1554 end if;
1555 return l_request_id;
1556 end submit_report;
1557 --
1558 END pay_jp_isdf_rpt;