[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ISDF_RPT
Source
1 PACKAGE BODY PAY_JP_ISDF_RPT AS
2 /* $Header: pyjpisrp.pkb 120.32.12020000.5 2012/09/25 07:08:52 mdubasi ship $ */
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 c_st_upd_date_2012 constant date := to_date('2012/01/01','YYYY/MM/DD');
23 --
24 /****************************************************************************
25 Name : get_amendment_flag
26 Description : This fucntion return the include_or_exclude flag for an
27 assignment id.
28 *****************************************************************************/
29 FUNCTION get_amendment_flag
30 (
31 p_assignment_id IN NUMBER,
32 p_assignment_set_id IN NUMBER
33 )
34 RETURN VARCHAR2 IS
35 l_inc_or_exc HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
36 --
37 BEGIN
38 SELECT INCLUDE_OR_EXCLUDE
39 INTO l_inc_or_exc
40 FROM HR_ASSIGNMENT_SET_AMENDMENTS
41 WHERE ASSIGNMENT_ID = p_assignment_id
42 AND ASSIGNMENT_SET_ID = p_assignment_set_id;
43 --
44 RETURN l_inc_or_exc;
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 RETURN 'ZZ';
48 END get_amendment_flag;
49 --
50 /****************************************************************************
51 Name : chk_ass_set
52 Description : This fucntion checks if for the passed assignment_id an
53 assignment action is to be created or not. It checks for
54 assignment set by criteria also taking into account if any
55 amendment is defined for that assignment id.
56 *****************************************************************************/
57 FUNCTION chk_ass_set(
58 p_assignment_id IN NUMBER,
59 p_assignment_set_id IN NUMBER,
60 p_formula_id IN NUMBER,
61 p_effective_date IN DATE,
62 p_dummy IN NUMBER) RETURN BOOLEAN
63 IS
64 l_result BOOLEAN;
65 l_amendment_flag HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
66 --
67 BEGIN
68 IF (p_dummy = 1)THEN
69 l_amendment_flag := get_amendment_flag(p_assignment_id,p_assignment_set_id);
70 IF (l_amendment_flag = 'ZZ') THEN
71 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date);
72 ELSIF l_amendment_flag = 'E' THEN
73 l_result := false;
74 ELSIF l_amendment_flag = 'I' THEN
75 l_result := true;
76 END IF;
77 ELSE
78 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date);
79 END IF;
80 --
81 RETURN l_result;
82 --
83 EXCEPTION
84 WHEN OTHERS THEN
85 hr_utility.set_location('chk_ass_set'||substr(sqlerrm,1,200),99);
86 RETURN FALSE;
87 END chk_ass_set;
88 --
89 /****************************************************************************
90 Name : chk_ass_set_mixed
91 Description : This fucntion checks if the assignment set passed is based
92 on both criteria and amendment or not.
93 *****************************************************************************/
94 FUNCTION chk_ass_set_mixed(
95 p_assignment_set_id IN NUMBER) RETURN NUMBER
96 IS
97 l_dummy NUMBER;
98 --
99 BEGIN
100 SELECT 1
101 INTO l_dummy
102 FROM HR_ASSIGNMENT_SET_AMENDMENTS
103 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
104 AND ROWNUM = 1;
105 RETURN l_dummy;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 l_dummy := 0;
109 RETURN l_dummy;
110 END chk_ass_set_mixed;
111 --
112 /****************************************************************************
113 Name : chk_all_exclusions
114 Description : This fucntion checks if the assignment set passed has only
115 exclusions.
116 *****************************************************************************/
117 FUNCTION chk_all_exclusions(
118 p_assignment_set_id IN NUMBER) RETURN NUMBER
119 IS
120 l_dummy NUMBER;
121 BEGIN
122 SELECT 0
123 INTO l_dummy
124 FROM HR_ASSIGNMENT_SET_AMENDMENTS
125 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
126 AND INCLUDE_OR_EXCLUDE = 'I'
127 AND ROWNUM = 1;
128 RETURN l_dummy;
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 l_dummy := 1;
132 RETURN l_dummy;
133 END chk_all_exclusions;
134 --
135 function cnv_str(
136 p_text in varchar2,
137 p_start in number default null,
138 p_end in number default null)
139 return varchar2
140 is
141 --
142 l_text varchar2(4000);
143 --
144 begin
145 --
146 l_text := ltrim(rtrim(replace(p_text,to_multi_byte(' '),' ')));
147 --
148 if p_start is not null
149 and p_end is not null then
150 --
151 l_text := substr(l_text,p_start,p_end);
152 --
153 end if;
154 --
155 return l_text;
156 --
157 end cnv_str;
158 --
159 function htmlspchar(
160 p_text in varchar2)
161 return varchar2
162 is
163 --
164 l_htmlspchar varchar2(1) := 'N';
165 --
166 begin
167 --
168 if nvl(instr(p_text,'<'),0) > 0 then
169 l_htmlspchar := 'Y';
170 end if;
171 --
172 if l_htmlspchar = 'N'
173 and nvl(instr(p_text,'>'),0) > 0 then
174 l_htmlspchar := 'Y';
175 end if;
176 --
177 if l_htmlspchar = 'N'
178 and nvl(instr(p_text,'&'),0) > 0 then
179 l_htmlspchar := 'Y';
180 end if;
181 --
182 if l_htmlspchar = 'N'
183 and nvl(instr(p_text,''''),0) > 0 then
184 l_htmlspchar := 'Y';
185 end if;
186 --
187 if l_htmlspchar = 'N'
188 and nvl(instr(p_text,'"'),0) > 0 then
189 l_htmlspchar := 'Y';
190 end if;
191 --
192 if l_htmlspchar = 'Y' then
193 return '<![CDATA['||p_text||']]>';
194 else
195 return p_text;
196 end if;
197 end htmlspchar;
198 --
199 /****************************************************************************
200 Name : PRINT_CLOB
201 Description : This procedure prints contents of a CLOB object passed as
202 parameter.
203 *****************************************************************************/
204 PROCEDURE PRINT_CLOB
205 (
206 p_clob CLOB
207 ) AS
208 ln_chars number;
209 ln_offset number;
210 lv_buf varchar2(255);
211 BEGIN
212 ln_chars := 240;
213 ln_offset := 1;
214 LOOP
215 lv_buf := null;
216 dbms_lob.read(
217 p_clob,
218 ln_chars,
219 ln_offset,
220 lv_buf
221 );
222 hr_utility.trace(lv_buf);
223 ln_offset := ln_offset + ln_chars;
224 END LOOP;
225 EXCEPTION
226 WHEN NO_DATA_FOUND THEN
227 hr_utility.trace ('CLOB contents end.');
228 END PRINT_CLOB;
229 --
230 /****************************************************************************
231 Name : range_cursor
232 Arguments : p_payroll_action_id
233 p_sqlstr to return the SQL Statement
234 Description : This procedure defines a SQL statement
235 to fetch all the people to be included in the report.
236 This SQL statement is used to define the 'chunks' for
237 multi-threaded operation
238 *****************************************************************************/
239 PROCEDURE range_cursor
240 (
241 P_PAYROLL_ACTION_ID number,
242 P_SQLSTR OUT NOCOPY varchar2
243 ) AS
244 l_proc_name varchar2(100);
245 BEGIN
246 l_proc_name := g_proc_name || 'RANGE_CURSOR';
247 hr_utility.trace ('Entering '||l_proc_name);
248 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
249 p_sqlstr := ' select distinct p.person_id'||
250 ' from per_people_f p,'||
251 ' pay_payroll_actions pa'||
252 ' where pa.payroll_action_id = :payroll_action_id'||
253 ' and p.business_group_id = pa.business_group_id'||
254 ' order by p.person_id ';
255 hr_utility.trace ('Range cursor query : ' || p_sqlstr);
256 hr_utility.trace ('Leaving '||l_proc_name);
257 END range_cursor;
258 --
259 /****************************************************************************
260 Name : action_creation
261 Arguments : p_payroll_action_id
262 p_start_person_id
263 p_end_person_id
264 p_chunk_number
265 Description :This procedure creates assignment actions for the
266 payroll_action_id passed as parameter.
267 *****************************************************************************/
268 PROCEDURE action_creation
269 (
270 P_PAYROLL_ACTION_ID number,
271 P_START_PERSON_ID number,
272 P_END_PERSON_ID number,
273 P_CHUNK number
274 ) AS
275 CURSOR c_assact(pay_act_id pay_payroll_actions.payroll_action_id%TYPE,trans_stat varchar2)
276 IS
277 SELECT distinct pjiav.assignment_id, pjiav.effective_date
278 FROM per_all_assignments_f paa,
279 per_all_people_f pap,
280 pay_assignment_actions pas,
281 pay_jp_isdf_assact_v pjiav
282 WHERE paa.person_id between p_start_person_id and p_end_person_id
283 AND paa.person_id = pap.person_id
284 AND sysdate between pap.effective_start_date and pap.effective_end_date
285 AND sysdate between paa.effective_start_date and paa.effective_end_date
286 AND pas.assignment_id = paa.assignment_id
287 AND pas.payroll_action_id = pay_act_id
288 AND pjiav.assignment_action_id = pas.assignment_action_id
289 AND pjiav.assignment_id = pas.assignment_id
290 AND (pjiav.transaction_status = decode(trans_stat,'N','A')
291 or pjiav.transaction_status = decode(trans_stat,'N','F')
292 or pjiav.transaction_status = decode(trans_stat,'Y','A')
293 or pjiav.transaction_status = decode(trans_stat,'Y','F')
294 or pjiav.transaction_status = decode(trans_stat,'Y','N')
295 or pjiav.transaction_status = decode(trans_stat,'Y','U')); -- Last condition in where clause added for Bug Fix:5487428
296 --
297 l_assact pay_assignment_actions.assignment_action_id%type ;
298 l_proc_name VARCHAR2(60);
299 l_old_pact_id NUMBER;
300 l_cur_pact NUMBER;
301 l_legislative_parameters VARCHAR2(2000);
302 l_ass_set_id NUMBER;
303 l_result1 VARCHAR2(30);
304 l_result2 BOOLEAN;
305 l_formula_id NUMBER;
306 --
307 BEGIN
308 --
309 SELECT legislative_parameters
310 INTO l_legislative_parameters
311 FROM pay_payroll_actions
312 WHERE payroll_action_id = P_PAYROLL_ACTION_ID;
313 --
314 l_old_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',l_legislative_parameters));
315 l_ass_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',l_legislative_parameters));
316 l_prn_app_opt:= pay_core_utils.get_parameter('PUBLISH_CRITERION',l_legislative_parameters); --Bug Fix:5487428
317 --
318 IF g_debug THEN
319 l_proc_name := g_proc_name || 'ACTION_CREATION';
320 hr_utility.trace ('Entering '||l_proc_name);
321 hr_utility.trace ('Parameters ....');
322 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
323 hr_utility.trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
324 hr_utility.trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
325 hr_utility.trace ('P_CHUNK = '|| P_CHUNK);
326 hr_utility.trace ('P_OLD_PAYROLL_ACTION-ID = '||l_old_pact_id);
327 hr_utility.trace ('P_ASS_SET_ID = '||l_ass_set_id);
328 hr_utility.trace ('PRN_EMP_NO = '||l_emp_no_opt);
329 hr_utility.trace ('PUBLISH_CRITERION = '||l_prn_app_opt);
330 END IF;
331 --
332 if g_bg_id is null then
333 Select p.business_group_id into g_bg_id
334 from pay_payroll_actions p
335 where p.payroll_action_id = p_payroll_action_id ;
336 end if ;
337 --
338 IF (g_dummy = -99) THEN
339 -- IF condition to ensure that functions are called only once.
340 g_dummy := chk_ass_set_mixed(l_ass_set_id);
341 g_all_exclusions_flag := chk_all_exclusions(l_ass_set_id);
342 END IF ;
343 --
344 FOR i IN c_assact(l_old_pact_id,l_prn_app_opt) LOOP
345 -- Added NVL to overcome NULL issue.
346 IF (NVL(l_ass_set_id,0) = 0) THEN
347 -- NO assignment set passed as parameter
348 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
349 hr_nonrun_asact.insact(l_assact,
350 i.assignment_id ,
351 p_payroll_action_id,
352 p_chunk,
353 null);
354 ELSE
355 -- assignment set is passed as parameter
356 SELECT formula_id INTO l_formula_id
357 FROM hr_assignment_sets
358 WHERE assignment_set_id = l_ass_set_id;
359 --
360 IF l_formula_id IS NULL THEN
361 -- assignment set by ammmendment passed
362 IF (g_all_exclusions_flag = 0) THEN
363 -- assignment set by ammmendment passed is not all exclusions.
364 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
365 IF (l_result1 = 'I') THEN
366 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
367 hr_nonrun_asact.insact(l_assact,
368 i.assignment_id ,
369 p_payroll_action_id,
370 p_chunk,
371 null);
372 END IF;
373 --
374 ELSE
375 -- assignment set by ammmendment passed is all exclusions.
376 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
377 IF (l_result1 <> 'E') THEN
378 SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
379 hr_nonrun_asact.insact(l_assact,
380 i.assignment_id ,
381 p_payroll_action_id,
382 p_chunk,
383 null);
384 END IF;
385 --
386 END IF;
387 --
388 ELSE
389 -- assignment set by criteria passed
390 l_result2 := chk_ass_set(i.assignment_id, l_ass_set_id, l_formula_id, i.effective_date, g_dummy);
391 IF (l_result2 = TRUE) THEN
392 SELECT pay_assignment_actions_s.nextval into l_assact from dual;
393 hr_nonrun_asact.insact(l_assact,
394 i.assignment_id ,
395 p_payroll_action_id,
396 p_chunk,
397 null);
398 END IF;
399 --
400 END IF;
401 --
402 END IF;
403 --
404 END LOOP;
405 --
406 END action_creation;
407 --
408 /****************************************************************************
409 Name : init_code
410 Description : None
411 *****************************************************************************/
412 PROCEDURE INIT_CODE ( P_PAYROLL_ACTION_ID IN NUMBER) IS
413 BEGIN
414 hr_utility.trace ('inside INIT_CODE ');
415 NULL;
416 END;
417 --
418 /****************************************************************************
419 Name : archive_code
420 Description : None
421 *****************************************************************************/
422 PROCEDURE ARCHIVE_CODE ( P_ASSIGNMENT_ACTION_ID IN NUMBER,
423 P_EFFECTIVE_DATE IN DATE ) IS
424 BEGIN
425 hr_utility.trace ('inside ARCHIVE_CODE ');
426 NULL;
427 END ;
428 --
429 /****************************************************************************
430 Name : assact_xml
431 Arguments : p_assignment_action_id
432 Description : This procedure creates xml for the assignment_action_id passed
433 as parameter. It then writes the xml into vXMLTable.
434 *****************************************************************************/
435 PROCEDURE assact_xml(
436 p_assignment_action_id IN NUMBER)
437 IS
438 --
439 CURSOR cur_isdf_employer(p_mag_asg_action_id NUMBER)
440 IS
441 SELECT to_char(pjip.effective_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') year,
442 pjip.effective_date,
443 pjip.tax_office_name,
444 pjip.salary_payer_name,
445 pjip.salary_payer_address
446 FROM pay_jp_isdf_pact_v pjip,
447 pay_assignment_actions paa
448 WHERE paa.assignment_action_id = p_mag_asg_action_id
449 AND paa.payroll_action_id = pjip.payroll_action_id;
450 --
451 isdf_employer_c cur_isdf_employer%ROWTYPE;
452 --
453 CURSOR cur_isdf_emp(p_mag_asg_action_id NUMBER)
454 IS
455 SELECT pjie.last_name_kana,
456 pjie.first_name_kana,
457 pjie.last_name,
458 pjie.first_name,
459 pjie.address,
460 pjie.employee_number /* Enh:5671124 : Employee_number addition */
461 FROM pay_jp_isdf_emp_v pjie
462 WHERE pjie.assignment_action_id = p_mag_asg_action_id;
463 --
464 isdf_emp_c cur_isdf_emp%ROWTYPE;
465 --
466 cursor cur_isdf_calc(p_mag_asg_action_id NUMBER)
467 is
468 select decode(pjicd.life_gen_ins_prem, 0,null,pjicd.life_gen_ins_prem) life_gen_ins_prem,
469 decode(pjicd.life_pens_ins_prem, 0,null,pjicd.life_pens_ins_prem) life_pens_ins_prem,
470 decode(pjicd.life_gen_ins_calc_prem, 0,null,pjicd.life_gen_ins_calc_prem) life_gen_ins_calc_prem,
471 decode(pjicd.life_pens_ins_calc_prem, 0,null,pjicd.life_pens_ins_calc_prem) life_pens_ins_calc_prem,
472 decode(pjicd.life_gen_new_ins_prem, 0,null,pjicd.life_gen_new_ins_prem) life_gen_new_ins_prem,
473 decode(pjicd.life_pens_new_ins_prem, 0,null,pjicd.life_pens_new_ins_prem) life_pens_new_ins_prem,
474 decode(pjicd.life_gen_new_ins_calc_prem, 0,null,pjicd.life_gen_new_ins_calc_prem) life_gen_new_ins_calc_prem,
475 decode(pjicd.life_pens_new_ins_calc_prem, 0,null,pjicd.life_pens_new_ins_calc_prem) life_pens_new_ins_calc_prem,
476 decode(pjicd.life_ins_deduction, 0,null,pjicd.life_ins_deduction) life_ins_deduction,
477 decode(pjicd.earthquake_ins_prem, 0,null,pjicd.earthquake_ins_prem) earthquake_ins_prem,
478 decode(pjicd.nonlife_long_ins_prem, 0,null,pjicd.nonlife_long_ins_prem) nonlife_long_ins_prem,
479 decode(pjicd.nonlife_short_ins_prem, 0,null,pjicd.nonlife_short_ins_prem) nonlife_short_ins_prem,
480 decode(pjicd.earthquake_ins_calc_prem, 0,null,pjicd.earthquake_ins_calc_prem) earthquake_ins_calc_prem,
481 decode(pjicd.nonlife_long_ins_calc_prem, 0,null,pjicd.nonlife_long_ins_calc_prem) nonlife_long_ins_calc_prem,
482 decode(pjicd.nonlife_short_ins_calc_prem, 0,null,pjicd.nonlife_short_ins_calc_prem) nonlife_short_ins_calc_prem,
483 decode(pjicd.nonlife_ins_deduction, 0,null,pjicd.nonlife_ins_deduction) nonlife_ins_deduction,
484 decode(pjicd.social_ins_deduction, 0,null,pjicd.social_ins_deduction) social_ins_deduction,
485 decode(pjicd.mutual_aid_deduction, 0,null,pjicd.mutual_aid_deduction) mutual_aid_deduction,
486 decode(pjicd.sp_earned_income_calc, 0,null,pjicd.sp_earned_income_calc) sp_earned_income_calc,
487 decode(pjicd.sp_business_income_calc, 0,null,pjicd.sp_business_income_calc) sp_business_income_calc,
488 decode(pjicd.sp_miscellaneous_income_calc,0,null,pjicd.sp_miscellaneous_income_calc) sp_miscellaneous_income_calc,
489 decode(pjicd.sp_dividend_income_calc, 0,null,pjicd.sp_dividend_income_calc) sp_dividend_income_calc,
490 decode(pjicd.sp_real_estate_income_calc, 0,null,pjicd.sp_real_estate_income_calc) sp_real_estate_income_calc,
491 decode(pjicd.sp_retirement_income_calc, 0,null,pjicd.sp_retirement_income_calc) sp_retirement_income_calc,
492 decode(pjicd.sp_other_income_calc, 0,null,pjicd.sp_other_income_calc) sp_other_income_calc,
493 decode(pjicd.sp_income_calc, 0,null,pjicd.sp_income_calc) sp_income_calc,
494 decode(pjicd.spouse_income, 0,null,pjicd.spouse_income) spouse_income,
495 decode(pjicd.spouse_deduction, 0,null,pjicd.spouse_deduction) spouse_deduction
496 from pay_jp_isdf_calc_dct_v pjicd
497 where pjicd.assignment_action_id=p_mag_asg_action_id
498 and pjicd.status <> 'D';
499 --
500 isdf_calc_c cur_isdf_calc%ROWTYPE;
501 --
502 cursor cur_isdf_calc_new(p_mag_asg_action_id NUMBER)
503 is
504 select decode(pjicd1.care_med_new_ins_prem, 0,null,pjicd1.care_med_new_ins_prem) care_med_new_ins_prem,
505 decode(pjicd1.care_med_new_ins_calc_prem, 0,null,pjicd1.care_med_new_ins_calc_prem) care_med_new_ins_calc_prem
506 from pay_jp_isdf_calc_dct_v2 pjicd1
507 where pjicd1.assignment_action_id=p_mag_asg_action_id
508 and pjicd1.status <> 'D';
509 --
510 isdf_calc_new_c cur_isdf_calc_new%ROWTYPE;
511
512 CURSOR cur_isdf_mutual(p_mag_asg_action_id NUMBER)
513 IS
514 SELECT pjima.enterprise_contract_prem,
515 pjima.pension_prem,
516 pjima.disable_sup_contract_prem
517 FROM pay_jp_isdf_mutual_aid_v pjima
518 WHERE pjima.assignment_action_id=p_mag_asg_action_id
519 and pjima.status <> 'D';
520 --
521 isdf_mutual_c cur_isdf_mutual%ROWTYPE;
522 --
523 CURSOR cur_isdf_spouse(p_mag_asg_action_id NUMBER)
524 IS
525 SELECT pjis.full_name_kana,
526 pjis.full_name,
527 pjis.address,
528 pjis.emp_income
529 FROM pay_jp_isdf_spouse_v pjis
530 WHERE pjis.assignment_action_id=p_mag_asg_action_id
531 and pjis.status <> 'D';
532 --
533 isdf_spouse_c cur_isdf_spouse%ROWTYPE;
534 --
535 CURSOR cur_isdf_spouse_inc(p_mag_asg_action_id NUMBER)
536 IS
537 SELECT pjisi.sp_earned_income,
538 pjisi.sp_business_income,
539 pjisi.sp_business_income_exp,
540 pjisi.sp_miscellaneous_income,
541 pjisi.sp_miscellaneous_income_exp,
542 pjisi.sp_dividend_income,
543 pjisi.sp_dividend_income_exp,
544 pjisi.sp_real_estate_income,
545 pjisi.sp_real_estate_income_exp,
546 pjisi.sp_retirement_income,
547 pjisi.sp_retirement_income_exp,
548 pjisi.sp_other_income,
549 pjisi.sp_other_income_exp,
550 pjisi.sp_other_income_exp_dct
551 FROM pay_jp_isdf_spouse_inc_v pjisi
552 WHERE pjisi.assignment_action_id=p_mag_asg_action_id
553 and pjisi.status <> 'D';
554 --
555 isdf_spouse_inc_c cur_isdf_spouse_inc%ROWTYPE;
556 --
557 -- LIFE GEN
558 CURSOR cur_isdf_life_gen(p_mag_act_info_id NUMBER)
559 IS
560 SELECT pjilg.ins_company_name,
561 pjilg.ins_type,
562 pjilg.ins_period,
563 pjilg.contractor_name,
564 pjilg.beneficiary_name,
565 pjilg.beneficiary_relship,
566 pjilg.annual_prem,
567 pjilg.gen_ins_class_old_new
568 FROM pay_jp_isdf_life_gen_v pjilg
569 WHERE pjilg.assignment_action_id=p_mag_act_info_id
570 and pjilg.status <> 'D';
571 --
572 TYPE isdf_ins_company_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
573 TYPE isdf_ins_type_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
574 TYPE isdf_ins_period_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
575 TYPE isdf_contractor_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
576 TYPE isdf_beneficiary_name_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
577 TYPE isdf_beneficiary_relship_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
578 TYPE isdf_annual_prem_lg IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
579 TYPE isdf_class_new_old_lg IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
580 --
581 ins_company_name_lg isdf_ins_company_name_lg;
582 ins_type_lg isdf_ins_type_lg;
583 ins_period_lg isdf_ins_period_lg;
584 contractor_name_lg isdf_contractor_name_lg;
585 beneficiary_name_lg isdf_beneficiary_name_lg;
586 beneficiary_relship_lg isdf_beneficiary_relship_lg;
587 annual_prem_lg isdf_annual_prem_lg;
588 class_new_old_lg isdf_class_new_old_lg;
589 --
590 -- CARE MED
591 CURSOR cur_isdf_care_med(p_mag_act_info_id NUMBER)
592 IS
593 SELECT pjcmi.ins_company_name,
594 pjcmi.ins_type,
595 pjcmi.ins_period,
596 pjcmi.contractor_name,
597 pjcmi.beneficiary_name,
598 pjcmi.beneficiary_relship,
599 pjcmi.annual_prem
600 FROM pay_jp_isdf_care_med_v pjcmi
601 WHERE pjcmi.assignment_action_id=p_mag_act_info_id
602 and pjcmi.status <> 'D';
603 --
604 TYPE isdf_ins_company_name_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
605 TYPE isdf_ins_type_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
606 TYPE isdf_ins_period_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
607 TYPE isdf_contractor_name_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
608 TYPE isdf_beneficiary_name_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
609 TYPE isdf_beneficiary_relship_lcm IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
610 TYPE isdf_annual_prem_lcm IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
611 --
612 ins_company_name_lcm isdf_ins_company_name_lcm;
613 ins_type_lcm isdf_ins_type_lcm;
614 ins_period_lcm isdf_ins_period_lcm;
615 contractor_name_lcm isdf_contractor_name_lcm;
616 beneficiary_name_lcm isdf_beneficiary_name_lcm;
617 beneficiary_relship_lcm isdf_beneficiary_relship_lcm;
618 annual_prem_lcm isdf_annual_prem_lcm;
619 --
620 -- LIFE PENS
621 CURSOR cur_isdf_life_pens(p_mag_act_info_id NUMBER)
622 IS
623 SELECT pjilp.ins_company_name,
624 pjilp.ins_type,
625 pjilp.ins_period_start_date,
626 pjilp.ins_period,
627 pjilp.contractor_name,
628 pjilp.beneficiary_name,
629 pjilp.beneficiary_relship,
630 pjilp.annual_prem,
631 pjilp.pens_ins_class_old_new
632 FROM pay_jp_isdf_life_pens_v pjilp
633 WHERE pjilp.assignment_action_id=p_mag_act_info_id
634 and pjilp.status <> 'D';
635 --
636 TYPE isdf_ins_company_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
637 TYPE isdf_ins_type_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
638 TYPE isdf_ins_period_start_date_lp IS TABLE OF DATE INDEX BY BINARY_INTEGER;
639 TYPE isdf_ins_period_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
640 TYPE isdf_contractor_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
641 TYPE isdf_beneficiary_name_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
642 TYPE isdf_beneficiary_relship_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
643 TYPE isdf_annual_prem_lp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
644 TYPE isdf_class_new_old_lp IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
645 --
646 ins_company_name_lp isdf_ins_company_name_lp;
647 ins_type_lp isdf_ins_type_lp;
648 ins_period_start_date_lp isdf_ins_period_start_date_lp;
649 ins_period_lp isdf_ins_period_lp;
650 contractor_name_lp isdf_contractor_name_lp;
651 beneficiary_name_lp isdf_beneficiary_name_lp;
652 beneficiary_relship_lp isdf_beneficiary_relship_lp;
653 annual_prem_lp isdf_annual_prem_lp;
654 class_new_old_lp isdf_class_new_old_lp;
655 --
656 --NONLIFE
657 cursor cur_isdf_nonlife(p_mag_act_info_id number)
658 is
659 select pjin.nonlife_ins_term_type,
660 pjin.ins_company_name,
661 pjin.ins_type,
662 pjin.ins_period,
663 pjin.contractor_name,
664 pjin.beneficiary_name,
665 pjin.beneficiary_relship,
666 pjin.maturity_repayment,
667 pjin.annual_prem
668 from pay_jp_isdf_nonlife_v pjin
669 where pjin.assignment_action_id=p_mag_act_info_id
670 and pjin.status <> 'D';
671 --
672 type isdf_nonlife_ins_term_type_nl is table of varchar2(240) index by binary_integer;
673 TYPE isdf_ins_company_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
674 TYPE isdf_ins_type_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
675 TYPE isdf_ins_period_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
676 TYPE isdf_contractor_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
677 TYPE isdf_beneficiary_name_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
678 TYPE isdf_beneficiary_relship_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
679 TYPE isdf_maturity_repayment_nl IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
680 TYPE isdf_annual_prem_nl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
681 --
682 nonlife_ins_term_type_nl isdf_nonlife_ins_term_type_nl;
683 ins_company_name_nl isdf_ins_company_name_nl;
684 ins_type_nl isdf_ins_type_nl;
685 ins_period_nl isdf_ins_period_nl;
686 contractor_name_nl isdf_contractor_name_nl;
687 beneficiary_name_nl isdf_beneficiary_name_nl;
688 beneficiary_relship_nl isdf_beneficiary_relship_nl;
689 maturity_repayment_nl isdf_maturity_repayment_nl;
690 annual_prem_nl isdf_annual_prem_nl;
691 --
692 --SOCIAL
693 CURSOR cur_isdf_social(p_mag_act_info_id NUMBER)
694 IS
695 SELECT pjis.ins_type,
696 pjis.ins_payee_name,
697 pjis.debtor_name,
698 pjis.beneficiary_relship,
699 pjis.annual_prem
700 FROM pay_jp_isdf_social_v pjis
701 WHERE pjis.assignment_action_id=p_mag_act_info_id
702 and pjis.status <> 'D';
703 --
704 TYPE isdf_ins_type_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
705 TYPE isdf_ins_payee_name_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
706 TYPE isdf_debtor_name_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
707 TYPE isdf_beneficiary_relship_s IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
708 TYPE isdf_annual_prem_s IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
709 --
710 ins_type_s isdf_ins_type_s;
711 ins_payee_name_s isdf_ins_payee_name_s;
712 debtor_name_s isdf_debtor_name_s;
713 beneficiary_relship_s isdf_beneficiary_relship_s;
714 annual_prem_s isdf_annual_prem_s;
715 --
716 --Variables-----
717 k NUMBER;
718 a NUMBER;
719 b NUMBER;
720 c NUMBER;
721 d NUMBER;
722 e NUMBER;
723 f NUMBER;
724 g NUMBER;
725 h NUMBER;
726 i NUMBER;
727 j NUMBER;
728 l1 NUMBER;
729 l2 NUMBER;
730 no_of_pages NUMBER;
731 n_life_gen NUMBER;
732 n_life_pens NUMBER;
733 n_nonlife NUMBER;
734 n_social NUMBER;
735 count_life_gen NUMBER;
736 count_life_pens NUMBER;
737 count_nonlife NUMBER;
738 count_social NUMBER;
739 l_xml VARCHAR2(4000);
740 l_xml2 VARCHAR2(4000);
741 l_common_xml_page1 VARCHAR2(4000);
742 l_common_xml VARCHAR2(4000);
743 l_xml_begin VARCHAR2(200);
744 first_digit VARCHAR2(1);
745 second_digit VARCHAR2(1);
746 ps_date_length NUMBER;
747 ps_date VARCHAR2(30);
748 spded_value NUMBER(6,2);
749 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
750 l_emp_no VARCHAR2(80);
751 line_life_gen NUMBER;
752 count_care_med NUMBER;
753 line_life_pen NUMBER;
754 l3 number;
755 l4 number;
756 n_care_med NUMBER;
757 --
758 l_c13 varchar2(60);
759 l_c14 varchar2(60);
760 l_c15 varchar2(60);
761 l_c16 varchar2(60);
762 l_t48 varchar2(60);
763 l_t48a varchar2(60);
764 l_t48b varchar2(60);
765 l_t56 varchar2(60);
766 l_t56a varchar2(60);
767 l_t56b varchar2(60);
768
769 l_c32 NUMBER;
770 l_c10 number;
771 l_c33 number;
772 l_c34 number;
773 l_c38 number;
774 l_c11 number;
775 l_c39 number;
776 l_c40 number;
777 l_t131a varchar2(60);
778 l_t131b varchar2(60);
779 l_t132a varchar2(60);
780 l_t132b varchar2(60);
781 l_t133a varchar2(60);
782 l_t133b varchar2(60);
783 l_t134a varchar2(60);
784 l_t134b varchar2(60);
785 l_t135a varchar2(60);
786 l_t135b varchar2(60);
787 l_t136a varchar2(60);
788 l_t136b varchar2(60);
789 l_t137a varchar2(60);
790 l_t137b varchar2(60);
791 --
792 BEGIN
793 --
794 if g_msg_circle is null then
795 --
796 fnd_message.set_name('PER','HR_JP_CIRCLE');
797 g_msg_circle := fnd_message.get;
798 --
799 g_msg_circle := substr(g_msg_circle,0,1);
800 --
801 end if;
802 --
803 vXMLTable.DELETE;
804 vCtr := 0;
805 --
806 --hr_utility.set_location('Entering : ' || c_proc, 10);
807 hr_utility.trace('isdf_xml');
808 --
809 l_xml_begin := '<isdf>'||EOL||'<isdf1>' || EOL;
810 vXMLTable(vCtr).xmlstring := l_xml_begin;
811 vCtr := vCtr + 1;
812 l_mag_asg_action_id :=p_assignment_action_id;
813 --
814 OPEN cur_isdf_employer(l_mag_asg_action_id);
815 FETCH cur_isdf_employer INTO isdf_employer_c;
816 CLOSE cur_isdf_employer;
817 --
818 OPEN cur_isdf_emp(l_mag_asg_action_id);
819 FETCH cur_isdf_emp INTO isdf_emp_c;
820 CLOSE cur_isdf_emp;
821 --
822 OPEN cur_isdf_calc(l_mag_asg_action_id);
823 FETCH cur_isdf_calc INTO isdf_calc_c;
824 CLOSE cur_isdf_calc;
825 --
826 OPEN cur_isdf_calc_new(l_mag_asg_action_id);
827 FETCH cur_isdf_calc_new INTO isdf_calc_new_c;
828 CLOSE cur_isdf_calc_new;
829 --
830 OPEN cur_isdf_mutual(l_mag_asg_action_id);
831 FETCH cur_isdf_mutual INTO isdf_mutual_c;
832 CLOSE cur_isdf_mutual;
833 --
834 OPEN cur_isdf_spouse(l_mag_asg_action_id);
835 FETCH cur_isdf_spouse INTO isdf_spouse_c;
836 CLOSE cur_isdf_spouse;
837 --
838 OPEN cur_isdf_spouse_inc(l_mag_asg_action_id);
839 FETCH cur_isdf_spouse_inc INTO isdf_spouse_inc_c;
840 CLOSE cur_isdf_spouse_inc;
841 --
842 -- Code to find the two digits for the spouse deduction field starts
843 spded_value := isdf_calc_c.spouse_deduction/10000;
844 --
845 -- assumption, spded_value should consist under 2 digits.
846 -- (no business case of decimal or more than 3 digits)
847 -- based on current design, 0 is treated as null, so no output.
848 -- (not sure whether replacing null is desired or not..)
849 first_digit := substrb(lpad(to_char(trunc(spded_value)),2,'0'),1,1);
850 --
851 if first_digit = '0' then
852 first_digit := null;
853 end if;
854 --
855 -- Code to find the two digits for the spouse deduction field ends
856 second_digit := substrb(lpad(to_char(trunc(spded_value)),2,'0'),2,1);
857 --
858 --
859 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
860 l_c13 := to_char(to_number(isdf_calc_c.nonlife_long_ins_prem),fnd_currency.get_format_mask('JPY',40));
861 l_c14 := to_char(to_number(isdf_calc_c.nonlife_short_ins_prem),fnd_currency.get_format_mask('JPY',40));
862 l_c15 := to_char(to_number(isdf_calc_c.nonlife_long_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
863 l_c16 := to_char(to_number(isdf_calc_c.nonlife_short_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
864 else
865 l_c13 := to_char(to_number(isdf_calc_c.earthquake_ins_prem),fnd_currency.get_format_mask('JPY',40));
866 l_c14 := to_char(to_number(isdf_calc_c.nonlife_long_ins_prem),fnd_currency.get_format_mask('JPY',40));
867 l_c15 := to_char(to_number(isdf_calc_c.earthquake_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
868 l_c16 := to_char(to_number(isdf_calc_c.nonlife_long_ins_calc_prem),fnd_currency.get_format_mask('JPY',40));
869 end if;
870 --
871 IF isdf_employer_c.effective_date >= c_st_upd_date_2012 then
872 l_c32 := nvl(to_number(isdf_calc_c.life_gen_new_ins_calc_prem),0);
873 l_c10 := nvl(to_number(isdf_calc_c.life_gen_ins_calc_prem),0);
874 l_c33 := least(l_c32 + l_c10,40000) ;
875 l_c34 := GREATEST(l_c33,l_c10);
876
877 l_c38 := nvl(to_number(isdf_calc_c.life_pens_new_ins_calc_prem),0);
878 l_c11 := nvl(to_number(isdf_calc_c.life_pens_ins_calc_prem),0);
879 l_c39 := least(l_c38 + l_c11,40000);
880 l_c40 := GREATEST(l_c39,l_c11);
881
882 END IF;
883
884 -- Creating xml string for pages after first page (repeating page for over lines of printing data records)
885 l_common_xml :=
886 '<c1>' ||isdf_employer_c.year ||'</c1>' ||EOL|| --year
887 '<c2>' ||htmlspchar(cnv_str(isdf_employer_c.tax_office_name)) ||'</c2>' ||EOL|| --tax_office_name
888 '<c3>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_name)) ||'</c3>' ||EOL|| --employer_full_name
889 '<c4>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_address)) ||'</c4>' ||EOL|| --employer_address
890 '<c5>' ||htmlspchar(cnv_str(isdf_emp_c.last_name_kana)||''||cnv_str(isdf_emp_c.first_name_kana)) ||'</c5>' ||EOL|| --kana_name
891 '<c6>' ||htmlspchar(cnv_str(isdf_emp_c.last_name)||''||cnv_str(isdf_emp_c.first_name)) ||'</c6>' ||EOL|| --name
892 '<c7>' ||htmlspchar(cnv_str(isdf_emp_c.address)) ||'</c7>' ||EOL|| --address
893 '<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
894 '<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
895 '<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
896 '<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
897 '<c12>'||to_char(to_number(isdf_calc_c.life_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c12>'||EOL|| --life_ins_deduction
898 '<c31>'||to_char(to_number(isdf_calc_c.life_gen_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c31>'||EOL|| --life_gen_new_ins_prem
899 '<c32>'||to_char(to_number(isdf_calc_c.life_gen_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c32>'||EOL|| --life_gen_new_ins_calc_prem
900 '<c33>'||to_char(l_c33,fnd_currency.get_format_mask('JPY',40)) ||'</c33>'||EOL|| --life_gen_total_ded_1
901 '<c34>'||to_char(l_c34,fnd_currency.get_format_mask('JPY',40)) ||'</c34>'||EOL|| --life_gen_total_ded_2
902 '<c35>'||to_char(to_number(isdf_calc_new_c.care_med_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c35>'||EOL|| --care_med_new_ins_prem
903 '<c36>'||to_char(to_number(isdf_calc_new_c.care_med_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c36>'||EOL|| --care_med_new_ins_calc_prem
904 '<c37>'||to_char(to_number(isdf_calc_c.life_pens_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c37>'||EOL|| --life_pens_new_ins_prem
905 '<c38>'||to_char(to_number(isdf_calc_c.life_pens_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c38>'||EOL|| --life_pens_new_ins_calc_prem
906 '<c39>'||to_char(l_c39,fnd_currency.get_format_mask('JPY',40)) ||'</c39>'||EOL|| --life_pens_total_ded_1
907 '<c40>'||to_char(l_c40,fnd_currency.get_format_mask('JPY',40)) ||'</c40>'||EOL|| --life_pens_total_ded_2
908 '<c13>'||l_c13 ||'</c13>'||EOL|| --nonlife_long_ins_prem
909 '<c14>'||l_c14 ||'</c14>'||EOL|| --nonlife_short_ins_prem
910 '<c15>'||l_c15 ||'</c15>'||EOL|| --nonlife_long_ins_calc_prem
911 '<c16>'||l_c16 ||'</c16>'||EOL|| --nonlife_short_ins_calc_prem
912 '<c17>'||to_char(to_number(isdf_calc_c.nonlife_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c17>'||EOL|| --nonlife_ins_deduction
913 '<c18>'||to_char(to_number(isdf_calc_c.social_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c18>'||EOL|| --social_ins_deduction
914 '<c19>'||to_char(to_number(isdf_calc_c.mutual_aid_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c19>'||EOL|| --mutual_aid_deduction
915 '<c30>'||isdf_employer_c.year ||'</c30>';
916 --
917 --Parameter support for print Employee No option --
918 --
919 if l_emp_no_opt = 'N' then
920 --
921 l_emp_no := '';
922 --
923 -- l_emp_no_opt = Y (SRS) or null (SS)
924 else
925 --
926 l_emp_no :=cnv_str(isdf_emp_c.employee_number);
927 --
928 end if;
929 --
930 -- Creating common xml string for page1
931 l_common_xml_page1 :=
932 '<c1>' ||isdf_employer_c.year ||'</c1>' ||EOL|| --year
933 '<c2>' ||htmlspchar(cnv_str(isdf_employer_c.tax_office_name)) ||'</c2>' ||EOL|| --tax_office_name
934 '<c3>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_name)) ||'</c3>' ||EOL|| --employer_full_name
935 '<c4>' ||htmlspchar(cnv_str(isdf_employer_c.salary_payer_address)) ||'</c4>' ||EOL|| --employer_address
936 '<c5>' ||htmlspchar(cnv_str(isdf_emp_c.last_name_kana)||''||cnv_str(isdf_emp_c.first_name_kana)) ||'</c5>' ||EOL|| --kana_name
937 '<c6>' ||htmlspchar(cnv_str(isdf_emp_c.last_name)||''||cnv_str(isdf_emp_c.first_name)) ||'</c6>' ||EOL|| --name
938 '<c7>' ||htmlspchar(cnv_str(isdf_emp_c.address)) ||'</c7>' ||EOL|| --address
939 '<c7a>'||htmlspchar(l_emp_no) ||'</c7a>'||EOL|| -- employee number
940 '<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
941 '<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
942 '<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
943 '<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
944 '<c12>'||to_char(to_number(isdf_calc_c.life_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c12>'||EOL|| --life_ins_deduction
945 '<c31>'||to_char(to_number(isdf_calc_c.life_gen_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c31>'||EOL|| --life_gen_new_ins_prem
946 '<c32>'||to_char(to_number(isdf_calc_c.life_gen_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c32>'||EOL|| --life_gen_new_ins_calc_prem
947 '<c33>'||to_char(l_c33,fnd_currency.get_format_mask('JPY',40)) ||'</c33>'||EOL|| --life_gen_total_ded_1
948 '<c34>'||to_char(l_c34,fnd_currency.get_format_mask('JPY',40)) ||'</c34>'||EOL|| --life_gen_total_ded_2
949 '<c35>'||to_char(to_number(isdf_calc_new_c.care_med_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c35>'||EOL|| --care_med_new_ins_prem
950 '<c36>'||to_char(to_number(isdf_calc_new_c.care_med_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c36>'||EOL|| --care_med_new_ins_calc_prem
951 '<c37>'||to_char(to_number(isdf_calc_c.life_pens_new_ins_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c37>'||EOL|| --life_pens_new_ins_prem
952 '<c38>'||to_char(to_number(isdf_calc_c.life_pens_new_ins_calc_prem),fnd_currency.get_format_mask('JPY',40))||'</c38>'||EOL|| --life_pens_new_ins_calc_prem
953 '<c39>'||to_char(l_c39,fnd_currency.get_format_mask('JPY',40)) ||'</c39>'||EOL|| --life_pens_total_ded_1
954 '<c40>'||to_char(l_c40,fnd_currency.get_format_mask('JPY',40)) ||'</c40>'||EOL|| --life_pens_total_ded_2
955 '<c13>'||l_c13 ||'</c13>'||EOL|| --nonlife_long_ins_prem
956 '<c14>'||l_c14 ||'</c14>'||EOL|| --nonlife_short_ins_prem
957 '<c15>'||l_c15 ||'</c15>'||EOL|| --nonlife_long_ins_calc_prem
958 '<c16>'||l_c16 ||'</c16>'||EOL|| --nonlife_short_ins_calc_prem
959 '<c17>'||to_char(to_number(isdf_calc_c.nonlife_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c17>'||EOL|| --nonlife_ins_deduction
960 '<c18>'||to_char(to_number(isdf_calc_c.social_ins_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c18>'||EOL|| --social_ins_deduction
961 '<c19>'||to_char(to_number(isdf_calc_c.mutual_aid_deduction),fnd_currency.get_format_mask('JPY',40)) ||'</c19>'||EOL|| --mutual_aid_deduction
962 '<c20>'||to_char(to_number(isdf_calc_c.spouse_income),fnd_currency.get_format_mask('JPY',40)) ||'</c20>'||EOL|| --spouse_income
963 '<c21>'||first_digit ||'</c21>'||EOL|| --first_digit
964 '<c22>'||second_digit ||'</c22>'||EOL|| --second_digit
965 '<c23>'||to_char(to_number(isdf_mutual_c.enterprise_contract_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c23>'||EOL|| --enterprise_contract_prem
966 '<c24>'||to_char(to_number(isdf_mutual_c.pension_prem),fnd_currency.get_format_mask('JPY',40)) ||'</c24>'||EOL|| --pension_prem
967 '<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
968 '<c26>'||htmlspchar(cnv_str(isdf_spouse_c.full_name_kana)) ||'</c26>'||EOL|| --sp_full_name_kana
969 '<c27>'||htmlspchar(cnv_str(isdf_spouse_c.full_name)) ||'</c27>'||EOL|| --sp_full_name
970 '<c28>'||htmlspchar(cnv_str(isdf_spouse_c.address)) ||'</c28>'||EOL|| --sp_address
971 '<c29>'||to_char(to_number(isdf_spouse_c.emp_income),fnd_currency.get_format_mask('JPY',40)) ||'</c29>'||EOL|| --sp_emp_income
972 '<c30>'||isdf_employer_c.year ||'</c30>'||EOL|| --year
973 '<c41>'||to_char(to_number(isdf_calc_c.spouse_deduction ),fnd_currency.get_format_mask('JPY',40)) ||'</c41>'||EOL|| --Spouse Deduction from 2012
974 '<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
975 '<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
976 '<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
977 '<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
978 '<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
979 '<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
980 '<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
981 '<p8>' ||to_char(to_number(isdf_calc_c.sp_income_calc),fnd_currency.get_format_mask('JPY',40)) ||'</p8>' ||EOL|| --sp_income_calc
982 '<p9>' ||to_char(to_number(isdf_spouse_inc_c.sp_earned_income),fnd_currency.get_format_mask('JPY',40)) ||'</p9>' ||EOL|| --sp_earned_income
983 '<p10>'||to_char(to_number(isdf_spouse_inc_c.sp_business_income),fnd_currency.get_format_mask('JPY',40)) ||'</p10>'||EOL|| --sp_business_income
984 '<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
985 '<p12>'||to_char(to_number(isdf_spouse_inc_c.sp_miscellaneous_income),fnd_currency.get_format_mask('JPY',40)) ||'</p12>'||EOL|| --sp_miscellaneous_income
986 '<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
987 '<p14>'||to_char(to_number(isdf_spouse_inc_c.sp_dividend_income),fnd_currency.get_format_mask('JPY',40)) ||'</p14>'||EOL|| --sp_dividend_income
988 '<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
989 '<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
990 '<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
991 '<p18>'||to_char(to_number(isdf_spouse_inc_c.sp_retirement_income),fnd_currency.get_format_mask('JPY',40)) ||'</p18>'||EOL|| --sp_retirement_income
992 '<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
993 '<p20>'||to_char(to_number(isdf_spouse_inc_c.sp_other_income),fnd_currency.get_format_mask('JPY',40)) ||'</p20>'||EOL|| --sp_other_income
994 '<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
995 '<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
996 --
997 -- Code to determine the number of pages start.
998 --
999 SELECT count(DISTINCT(action_information_id))
1000 INTO count_life_gen
1001 FROM pay_jp_isdf_life_gen_v pjilg
1002 WHERE pjilg.assignment_action_id = l_mag_asg_action_id
1003 and pjilg.status <> 'D';
1004 --
1005 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1006 IF (count_life_gen = 0) THEN
1007 n_life_gen := 1;
1008 ELSIF ( mod(count_life_gen,3) = 0) THEN
1009 n_life_gen := (count_life_gen/3);
1010 ELSE
1011 n_life_gen := ((count_life_gen - mod(count_life_gen,3))/3) + 1;
1012 END IF;
1013 ELSE
1014 IF (count_life_gen = 0) THEN
1015 n_life_gen := 1;
1016 ELSIF ( mod(count_life_gen,4) = 0) THEN
1017 n_life_gen := (count_life_gen/4);
1018 ELSE
1019 n_life_gen := ((count_life_gen - mod(count_life_gen,4))/4) + 1;
1020 END IF;
1021 END IF;
1022 --
1023 SELECT count(DISTINCT(action_information_id))
1024 INTO count_life_pens
1025 FROM pay_jp_isdf_life_pens_v pjilp
1026 WHERE pjilp.assignment_action_id = l_mag_asg_action_id
1027 and pjilp.status <> 'D';
1028 --
1029 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1030 IF (count_life_pens = 0) THEN
1031 n_life_pens := 1;
1032 ELSIF ( mod(count_life_pens, 2) = 0) THEN
1033 n_life_pens := (count_life_pens/2);
1034 ELSE
1035 n_life_pens := ((count_life_pens - mod(count_life_pens,2))/2) + 1;
1036 END IF;
1037 ELSE
1038 IF (count_life_pens = 0) THEN
1039 n_life_pens := 1;
1040 ELSIF ( mod(count_life_pens, 3) = 0) THEN
1041 n_life_pens := (count_life_pens/3);
1042 ELSE
1043 n_life_pens := ((count_life_pens - mod(count_life_pens,3))/3) + 1;
1044 END IF;
1045 END IF;
1046 --
1047 SELECT count(DISTINCT(action_information_id))
1048 INTO count_nonlife
1049 FROM pay_jp_isdf_nonlife_v pjin
1050 WHERE pjin.assignment_action_id = l_mag_asg_action_id
1051 and pjin.status <> 'D';
1052 --
1053 IF (count_nonlife = 0) THEN
1054 n_nonlife := 1;
1055 ELSIF ( mod(count_nonlife, 2) = 0) THEN
1056 n_nonlife := (count_nonlife/2);
1057 ELSE
1058 n_nonlife := ((count_nonlife - mod(count_nonlife,2))/2) + 1;
1059 END IF;
1060 --
1061 SELECT count(DISTINCT(action_information_id))
1062 INTO count_social
1063 FROM pay_jp_isdf_social_v pjis
1064 WHERE pjis.assignment_action_id = l_mag_asg_action_id
1065 and pjis.status <> 'D';
1066 --
1067 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1068 IF (count_social = 0) THEN
1069 n_social := 1;
1070 ELSIF ( mod(count_social, 3) = 0) THEN
1071 n_social := (count_social/3);
1072 ELSE
1073 n_social := ((count_social - mod(count_social,3))/3) + 1;
1074 END IF;
1075 ELSE
1076 IF (count_social = 0) THEN
1077 n_social := 1;
1078 ELSIF ( mod(count_social, 2) = 0) THEN
1079 n_social := (count_social/2);
1080 ELSE
1081 n_social := ((count_social - mod(count_social,2))/2) + 1;
1082 END IF;
1083 END IF;
1084
1085 IF isdf_employer_c.effective_date >= c_st_upd_date_2012 then
1086 SELECT count(DISTINCT(action_information_id))
1087 INTO count_care_med
1088 FROM pay_jp_isdf_care_med_v pjicm
1089 WHERE pjicm.assignment_action_id = l_mag_asg_action_id
1090 and pjicm.status <> 'D';
1091 --
1092 IF (count_care_med = 0) THEN
1093 n_care_med := 1;
1094 ELSIF ( mod(count_care_med, 2) = 0) THEN
1095 n_care_med := (count_care_med/2);
1096 ELSE
1097 n_care_med := ((count_care_med - mod(count_care_med,2))/2) + 1;
1098 END IF;
1099 END IF;
1100
1101 --
1102 IF (n_life_gen >= n_life_pens) THEN
1103 l1 := n_life_gen;
1104 ELSE
1105 l1 := n_life_pens;
1106 END IF;
1107 --
1108 IF (n_nonlife >= n_social) THEN
1109 l2 := n_nonlife;
1110 ELSE
1111 l2 := n_social;
1112 END IF;
1113 --
1114 IF n_care_med <> 0 THEN
1115 l3 := n_care_med;
1116 ELSE
1117 l3 := 0;
1118 END IF;
1119
1120 IF (l1 >= l2) THEN
1121 l4 := l1;
1122 ELSE
1123 l4 := l2;
1124 END IF;
1125
1126 IF (l3 >= l4) THEN
1127 no_of_pages := l3;
1128 ELSE
1129 no_of_pages := l4;
1130 END IF;
1131 --
1132 -- Code to determine the number of pages end.
1133 OPEN cur_isdf_life_gen (l_mag_asg_action_id);
1134 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, class_new_old_lg;
1135 CLOSE cur_isdf_life_gen;
1136 --
1137 OPEN cur_isdf_care_med (l_mag_asg_action_id);
1138 FETCH cur_isdf_care_med BULK COLLECT INTO ins_company_name_lcm, ins_type_lcm, ins_period_lcm, contractor_name_lcm, beneficiary_name_lcm, beneficiary_relship_lcm, annual_prem_lcm;
1139 CLOSE cur_isdf_care_med;
1140 --
1141 OPEN cur_isdf_life_pens (l_mag_asg_action_id);
1142 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, class_new_old_lp;
1143 CLOSE cur_isdf_life_pens;
1144 --
1145 OPEN cur_isdf_nonlife (l_mag_asg_action_id);
1146 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;
1147 CLOSE cur_isdf_nonlife;
1148 --
1149 OPEN cur_isdf_social (l_mag_asg_action_id);
1150 FETCH cur_isdf_social BULK COLLECT INTO ins_type_s, ins_payee_name_s, debtor_name_s, beneficiary_relship_s, annual_prem_s;
1151 CLOSE cur_isdf_social;
1152 --
1153 i := 0;
1154 --
1155 hr_utility.set_location('NO. OF PAGES :', no_of_pages);
1156 --
1157 WHILE i < no_of_pages
1158 LOOP
1159 --
1160 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1161 a := 3 * i + 1;
1162 b := 3 * i + 2;
1163 c := 3 * i + 3;
1164 d := 2 * i + 1;
1165 e := 2 * i + 2;
1166 ELSE
1167 a := 4 * i + 1;
1168 b := 4 * i + 2;
1169 c := 4 * i + 3;
1170 f := 4 * i + 4;
1171 g := 3 * i + 1;
1172 h := 3 * i + 2;
1173 j := 3 * i + 3;
1174 d := 2 * i + 1;
1175 e := 2 * i + 2;
1176
1177 END IF;
1178 --
1179 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML A:', a);
1180 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML B:', b);
1181 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML C:', c);
1182 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML D:', d);
1183 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML E:', e);
1184 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML f:', f);
1185 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML g:', g);
1186 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML h:', h);
1187 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML i:', i);
1188 hr_utility.set_location('value of VARIABLES IN ISDF COMP XML j:', j);
1189
1190 --
1191 IF (i = 0) THEN
1192 l_xml := '<page>'||EOL||l_common_xml_page1||EOL;
1193 ELSE
1194 l_xml := '<page>'||EOL||l_common_xml||EOL;
1195 END IF;
1196 --
1197 -- writing first part of xml to vXMLtable
1198 vXMLTable(vCtr).xmlstring := l_xml;
1199 vCtr := vCtr + 1;
1200 --
1201 l_t48 := null;
1202 l_t56 := null;
1203 --
1204 l_t48a := null;
1205 l_t56a := null;
1206 l_t48b := null;
1207 l_t56b := null;
1208
1209 --
1210 l_t131a := null;
1211 l_t131b := null;
1212 l_t132a := null;
1213 l_t132b := null;
1214 l_t133a := null;
1215 l_t133b := null;
1216 l_t134a := null;
1217 l_t134b := null;
1218 l_t135a := null;
1219 l_t135b := null;
1220 l_t136a := null;
1221 l_t136b := null;
1222 l_t137a := null;
1223 l_t137b := null;
1224
1225 --
1226 IF (ins_company_name_lg.count >= a) THEN
1227 --
1228 if class_new_old_lg(a) = 'N' then
1229 --
1230 l_t131a := g_msg_circle;
1231 --
1232 elsif class_new_old_lg(a) = 'O' then
1233 --
1234 l_t131b := g_msg_circle;
1235 --
1236 end if;
1237 -- hr_utility.set_location('value of VARIABLES IN ISDF COMP XML E:', to_char(class_new_old_lg(a)));
1238 l_xml := '<t1>'||htmlspchar(cnv_str(ins_company_name_lg(a))) ||'</t1>'||EOL; --ins_company_name_lg1
1239 l_xml := l_xml||'<t2>'||htmlspchar(cnv_str(ins_type_lg(a))) ||'</t2>'||EOL; --ins_type_lg1
1240 l_xml := l_xml||'<t3>'||htmlspchar(cnv_str(ins_period_lg(a))) ||'</t3>'||EOL; --ins_period_lg1
1241 l_xml := l_xml||'<t4>'||htmlspchar(cnv_str(contractor_name_lg(a))) ||'</t4>'||EOL; --contractor_name_lg1
1242 l_xml := l_xml||'<t5>'||htmlspchar(cnv_str(beneficiary_name_lg(a))) ||'</t5>'||EOL; --beneficiary_name_lg1
1243 l_xml := l_xml||'<t6>'||htmlspchar(cnv_str(beneficiary_relship_lg(a)))||'</t6>'||EOL; --beneficiary_relship_lg1
1244 l_xml := l_xml|| '<t131a>'||l_t131a ||'</t131a>'||EOL; --class_new_old_lg1
1245 l_xml := l_xml|| '<t131b>'||l_t131b ||'</t131b>'||EOL; --class_new_old_lg1
1246 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
1247 --
1248 ELSE
1249 --
1250 l_xml := '<t1>'||' '||'</t1>'||EOL; --ins_company_name_lg1
1251 l_xml := l_xml||'<t2>'||' '||'</t2>'||EOL; --ins_type_lg1
1252 l_xml := l_xml||'<t3>'||' '||'</t3>'||EOL; --ins_period_lg1
1253 l_xml := l_xml||'<t4>'||' '||'</t4>'||EOL; --contractor_name_lg1
1254 l_xml := l_xml||'<t5>'||' '||'</t5>'||EOL; --beneficiary_name_lg1
1255 l_xml := l_xml||'<t6>'||' '||'</t6>'||EOL; --beneficiary_relship_lg1
1256 l_xml := l_xml|| '<t131a>'||' '||'</t131a>'||EOL; --class_new_old_lg1
1257 l_xml := l_xml|| '<t131b>'||' '||'</t131b>'||EOL; --class_new_old_lg1
1258 l_xml := l_xml||'<t7>'||' '||'</t7>'||EOL; --annual_prem_lg1
1259 --
1260 END IF;
1261 --
1262 IF (ins_company_name_lg.count >= b) THEN
1263 --
1264 if class_new_old_lg(b) = 'N' then
1265 --
1266 l_t132a := g_msg_circle;
1267 --
1268 elsif class_new_old_lg(b) = 'O' then
1269 --
1270 l_t132b := g_msg_circle;
1271 --
1272 end if;
1273 l_xml := l_xml||'<t8>' ||htmlspchar(cnv_str(ins_company_name_lg(b))) ||'</t8>' ||EOL; --ins_company_name_lg2
1274 l_xml := l_xml||'<t9>' ||htmlspchar(cnv_str(ins_type_lg(b))) ||'</t9>' ||EOL; --ins_type_lg2
1275 l_xml := l_xml||'<t10>'||htmlspchar(cnv_str(ins_period_lg(b))) ||'</t10>'||EOL; --ins_period_lg2
1276 l_xml := l_xml||'<t11>'||htmlspchar(cnv_str(contractor_name_lg(b))) ||'</t11>'||EOL; --contractor_name_lg2
1277 l_xml := l_xml||'<t12>'||htmlspchar(cnv_str(beneficiary_name_lg(b))) ||'</t12>'||EOL; --beneficiary_name_lg2
1278 l_xml := l_xml||'<t13>'||htmlspchar(cnv_str(beneficiary_relship_lg(b)))||'</t13>'||EOL; --beneficiary_relship_lg2
1279 l_xml := l_xml|| '<t132a>'||l_t132a ||'</t132a>'||EOL; --class_new_old_lg2
1280 l_xml := l_xml|| '<t132b>'||l_t132b ||'</t132b>'||EOL; --class_new_old_lg2
1281 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
1282 --
1283 ELSE
1284 --
1285 l_xml := l_xml||'<t8>' ||' '||'</t8>' ||EOL; --ins_company_name_lg2
1286 l_xml := l_xml||'<t9>' ||' '||'</t9>' ||EOL; --ins_type_lg2
1287 l_xml := l_xml||'<t10>'||' '||'</t10>'||EOL; --ins_period_lg2
1288 l_xml := l_xml||'<t11>'||' '||'</t11>'||EOL; --contractor_name_lg2
1289 l_xml := l_xml||'<t12>'||' '||'</t12>'||EOL; --beneficiary_name_lg2
1290 l_xml := l_xml||'<t13>'||' '||'</t13>'||EOL; --beneficiary_relship_lg2
1291 l_xml := l_xml|| '<t132a>'||' '||'</t132a>'||EOL; --class_new_old_lg2
1292 l_xml := l_xml|| '<t132b>'||' '||'</t132b>'||EOL; --class_new_old_lg2
1293 l_xml := l_xml||'<t14>'||' '||'</t14>'||EOL; --annual_prem_lg2
1294 --
1295 END IF;
1296 --
1297 IF (ins_company_name_lg.count >= c) THEN
1298 --
1299 if class_new_old_lg(c) = 'N' then
1300 --
1301 l_t133a := g_msg_circle;
1302 --
1303 elsif class_new_old_lg(c) = 'O' then
1304 --
1305 l_t133b := g_msg_circle;
1306 --
1307 end if;
1308 l_xml := l_xml||'<t15>'||htmlspchar(cnv_str(ins_company_name_lg(c))) ||'</t15>'||EOL; --ins_company_name_lg3
1309 l_xml := l_xml||'<t16>'||htmlspchar(cnv_str(ins_type_lg(c))) ||'</t16>'||EOL; --ins_type_lg3
1310 l_xml := l_xml||'<t17>'||htmlspchar(cnv_str(ins_period_lg(c))) ||'</t17>'||EOL; --ins_period_lg3
1311 l_xml := l_xml||'<t18>'||htmlspchar(cnv_str(contractor_name_lg(c))) ||'</t18>'||EOL; --contractor_name_lg3
1312 l_xml := l_xml||'<t19>'||htmlspchar(cnv_str(beneficiary_name_lg(c))) ||'</t19>'||EOL; --beneficiary_name_lg3
1313 l_xml := l_xml||'<t20>'||htmlspchar(cnv_str(beneficiary_relship_lg(c)))||'</t20>'||EOL; --beneficiary_relship_lg3
1314 l_xml := l_xml|| '<t133a>'||l_t133a ||'</t133a>'||EOL; --class_new_old_lg3
1315 l_xml := l_xml|| '<t133b>'||l_t133b ||'</t133b>'||EOL; --class_new_old_lg3
1316 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
1317 --
1318 ELSE
1319 --
1320 l_xml := l_xml||'<t15>'||' '||'</t15>'||EOL; --ins_company_name_lg3
1321 l_xml := l_xml||'<t16>'||' '||'</t16>'||EOL; --ins_type_lg3
1322 l_xml := l_xml||'<t17>'||' '||'</t17>'||EOL; --ins_period_lg3
1323 l_xml := l_xml||'<t18>'||' '||'</t18>'||EOL; --contractor_name_lg3
1324 l_xml := l_xml||'<t19>'||' '||'</t19>'||EOL; --beneficiary_name_lg3
1325 l_xml := l_xml||'<t20>'||' '||'</t20>'||EOL; --beneficiary_relship_lg3
1326 l_xml := l_xml|| '<t133a>'||' '||'</t133a>'||EOL; --class_new_old_lg3
1327 l_xml := l_xml|| '<t133b>'||' '||'</t133b>'||EOL; --class_new_old_lg3
1328 l_xml := l_xml||'<t21>'||' '||'</t21>'||EOL; --annual_prem_lg3
1329 --
1330 END IF;
1331 --
1332 IF isdf_employer_c.effective_date >= c_st_upd_date_2012 then
1333 IF (ins_company_name_lg.count >= f) THEN
1334 --
1335 if class_new_old_lg(f) = 'N' then
1336 --
1337 l_t134a := g_msg_circle;
1338 --
1339 elsif class_new_old_lg(f) = 'O' then
1340 --
1341 l_t134b := g_msg_circle;
1342 --
1343 end if;
1344 l_xml := l_xml||'<t100>'||htmlspchar(cnv_str(ins_company_name_lg(f))) ||'</t100>'||EOL; --ins_company_name_lg4
1345 l_xml := l_xml||'<t101>'||htmlspchar(cnv_str(ins_type_lg(f))) ||'</t101>'||EOL; --ins_type_lg4
1346 l_xml := l_xml||'<t102>'||htmlspchar(cnv_str(ins_period_lg(f))) ||'</t102>'||EOL; --ins_period_lg4
1347 l_xml := l_xml||'<t103>'||htmlspchar(cnv_str(contractor_name_lg(f))) ||'</t103>'||EOL; --contractor_name_lg4
1348 l_xml := l_xml||'<t104>'||htmlspchar(cnv_str(beneficiary_name_lg(f))) ||'</t104>'||EOL; --beneficiary_name_lg4
1349 l_xml := l_xml||'<t105>'||htmlspchar(cnv_str(beneficiary_relship_lg(f)))||'</t105>'||EOL; --beneficiary_relship_lg4
1350 l_xml := l_xml|| '<t134a>'||l_t134a ||'</t134a>'||EOL; --class_new_old_lg4
1351 l_xml := l_xml|| '<t134b>'||l_t134b ||'</t134b>'||EOL; --class_new_old_lg4
1352 l_xml := l_xml||'<t106>'||htmlspchar(to_char(to_number(annual_prem_lg(f)),fnd_currency.get_format_mask('JPY',40)))||'</t106>'||EOL; --annual_prem_lg4
1353 --
1354 ELSE
1355 --
1356 l_xml := l_xml||'<t100>'||' '||'</t100>'||EOL; --ins_company_name_lg4
1357 l_xml := l_xml||'<t101>'||' '||'</t101>'||EOL; --ins_type_lg4
1358 l_xml := l_xml||'<t102>'||' '||'</t102>'||EOL; --ins_period_lg4
1359 l_xml := l_xml||'<t103>'||' '||'</t103>'||EOL; --contractor_name_lg4
1360 l_xml := l_xml||'<t104>'||' '||'</t104>'||EOL; --beneficiary_name_lg4
1361 l_xml := l_xml||'<t105>'||' '||'</t105>'||EOL; --beneficiary_relship_lg4
1362 l_xml := l_xml|| '<t134a>'||' '||'</t134a>'||EOL; --class_new_old_lg4
1363 l_xml := l_xml|| '<t134b>'||' '||'</t134b>'||EOL; --class_new_old_lg4
1364 l_xml := l_xml||'<t106>'||' '||'</t106>'||EOL; --annual_prem_lg4
1365 --
1366 END IF;
1367 END IF;
1368 IF isdf_employer_c.effective_date >= c_st_upd_date_2012 then
1369 IF (ins_company_name_lcm.count >= d) THEN
1370 --
1371 l_xml := l_xml||'<t107>'||htmlspchar(cnv_str(ins_company_name_lcm(d))) ||'</t107>'||EOL; --ins_company_name_lcm1
1372 l_xml := l_xml||'<t108>'||htmlspchar(cnv_str(ins_type_lcm(d))) ||'</t108>'||EOL; --ins_type_lcm1
1373 l_xml := l_xml||'<t109>'||htmlspchar(cnv_str(ins_period_lcm(d))) ||'</t109>'||EOL; --ins_period_lcm1
1374 l_xml := l_xml||'<t110>'||htmlspchar(cnv_str(contractor_name_lcm(d))) ||'</t110>'||EOL; --contractor_name_lcm1
1375 l_xml := l_xml||'<t111>'||htmlspchar(cnv_str(beneficiary_name_lcm(d))) ||'</t111>'||EOL; --beneficiary_name_lcm1
1376 l_xml := l_xml||'<t112>'||htmlspchar(cnv_str(beneficiary_relship_lcm(d)))||'</t112>'||EOL; --beneficiary_relship_lcm1
1377 l_xml := l_xml||'<t113>'||htmlspchar(to_char(to_number(annual_prem_lcm(d)),fnd_currency.get_format_mask('JPY',40)))||'</t113>'||EOL; --annual_prem_lcm1
1378 --
1379 ELSE
1380 --
1381 l_xml := l_xml||'<t107>'||' '||'</t107>'||EOL; --ins_company_name_lcm1
1382 l_xml := l_xml||'<t108>'||' '||'</t108>'||EOL; --ins_type_lcm1
1383 l_xml := l_xml||'<t109>'||' '||'</t109>'||EOL; --ins_period_lcm1
1384 l_xml := l_xml||'<t110>'||' '||'</t110>'||EOL; --contractor_name_lcm1
1385 l_xml := l_xml||'<t111>'||' '||'</t111>'||EOL; --beneficiary_name_lcm1
1386 l_xml := l_xml||'<t112>'||' '||'</t112>'||EOL; --beneficiary_relship_lcm1
1387 l_xml := l_xml||'<t113>'||' '||'</t113>'||EOL; --annual_prem_lcm1
1388 --
1389 END IF;
1390
1391 IF (ins_company_name_lcm.count >= e) THEN
1392 --
1393
1394 l_xml := l_xml||'<t114>'||htmlspchar(cnv_str(ins_company_name_lcm(e))) ||'</t114>'||EOL; --ins_company_name_lcm2
1395 l_xml := l_xml||'<t115>'||htmlspchar(cnv_str(ins_type_lcm(e))) ||'</t115>'||EOL; --ins_type_lcm2
1396 l_xml := l_xml||'<t116>'||htmlspchar(cnv_str(ins_period_lcm(e))) ||'</t116>'||EOL; --ins_period_lcm2
1397 l_xml := l_xml||'<t117>'||htmlspchar(cnv_str(contractor_name_lcm(e))) ||'</t117>'||EOL; --contractor_name_lcm2
1398 l_xml := l_xml||'<t118>'||htmlspchar(cnv_str(beneficiary_name_lcm(e))) ||'</t118>'||EOL; --beneficiary_name_lcm2
1399 l_xml := l_xml||'<t119>'||htmlspchar(cnv_str(beneficiary_relship_lcm(e)))||'</t119>'||EOL; --beneficiary_relship_lcm2
1400 l_xml := l_xml||'<t120>'||htmlspchar(to_char(to_number(annual_prem_lcm(e)),fnd_currency.get_format_mask('JPY',40)))||'</t120>'||EOL; --annual_prem_lcm2
1401 --
1402 ELSE
1403 --
1404 l_xml := l_xml||'<t114>'||' '||'</t114>'||EOL; --ins_company_name_lcm2
1405 l_xml := l_xml||'<t115>'||' '||'</t115>'||EOL; --ins_type_lcm2
1406 l_xml := l_xml||'<t116>'||' '||'</t116>'||EOL; --ins_period_lcm2
1407 l_xml := l_xml||'<t117>'||' '||'</t117>'||EOL; --contractor_name_lcm2
1408 l_xml := l_xml||'<t118>'||' '||'</t118>'||EOL; --beneficiary_name_lcm2
1409 l_xml := l_xml||'<t119>'||' '||'</t119>'||EOL; --beneficiary_relship_lcm2
1410 l_xml := l_xml||'<t120>'||' '||'</t120>'||EOL; --annual_prem_lcm2
1411 --
1412 END IF;
1413 END IF;
1414
1415 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1416 IF (ins_company_name_lp.count >= d) THEN
1417 --
1418 select to_char(ins_period_start_date_lp(d),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1419 into ps_date
1420 from dual;
1421 --
1422 -- ps_date_length := length(ins_period_start_date_lp(d));
1423 ps_date_length := length(ps_date);
1424 --
1425 l_xml := l_xml||'<t22>'||htmlspchar(cnv_str(ins_company_name_lp(d))) ||'</t22>'||EOL; --ins_company_name_lp1
1426 l_xml := l_xml||'<t23>'||htmlspchar(cnv_str(ins_type_lp(d))) ||'</t23>'||EOL; --ins_type_lp1
1427 l_xml := l_xml||'<t24>'||substr(ps_date,ps_date_length - 5,2) ||'</t24>'||EOL; --ins_period_start_year_lp1
1428 l_xml := l_xml||'<t25>'||substr(ps_date,ps_date_length - 3,2) ||'</t25>'||EOL; --ins_period_start_month_lp1
1429 l_xml := l_xml||'<t26>'||substr(ps_date,ps_date_length - 1,2) ||'</t26>'||EOL; --ins_period_start_day_lp1
1430 l_xml := l_xml||'<t27>'||htmlspchar(cnv_str(ins_period_lp(d))) ||'</t27>'||EOL; --ins_period_lp1
1431 l_xml := l_xml||'<t28>'||htmlspchar(cnv_str(contractor_name_lp(d))) ||'</t28>'||EOL; --contractor_name_lp1
1432 l_xml := l_xml||'<t29>'||htmlspchar(cnv_str(beneficiary_name_lp(d))) ||'</t29>'||EOL; --beneficiary_name_lp1
1433 l_xml := l_xml||'<t30>'||htmlspchar(cnv_str(beneficiary_relship_lp(d)))||'</t30>'||EOL; --beneficiary_relship_lp1
1434 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
1435 --
1436 ELSE
1437 --
1438 l_xml := l_xml||'<t22>'||' '||'</t22>'||EOL; --ins_company_name_lp1
1439 l_xml := l_xml||'<t23>'||' '||'</t23>'||EOL; --ins_type_lp1
1440 l_xml := l_xml||'<t24>'||' '||'</t24>'||EOL; --ins_period_start_year_lp1
1441 l_xml := l_xml||'<t25>'||' '||'</t25>'||EOL; --ins_period_start_month_lp1
1442 l_xml := l_xml||'<t26>'||' '||'</t26>'||EOL; --ins_period_start_day_lp1
1443 l_xml := l_xml||'<t27>'||' '||'</t27>'||EOL; --ins_period_lp1
1444 l_xml := l_xml||'<t28>'||' '||'</t28>'||EOL; --contractor_name_lp1
1445 l_xml := l_xml||'<t29>'||' '||'</t29>'||EOL; --beneficiary_name_lp1
1446 l_xml := l_xml||'<t30>'||' '||'</t30>'||EOL; --beneficiary_relship_ lp1
1447 l_xml := l_xml||'<t31>'||' '||'</t31>'||EOL; --annual_prem_lp1
1448 --
1449 END IF;
1450 --
1451 IF (ins_company_name_lp.count >= e) THEN
1452 --
1453 select to_char(ins_period_start_date_lp(e),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1454 into ps_date
1455 from dual;
1456 --
1457 ps_date_length := length(ps_date);
1458 --
1459 l_xml := l_xml||'<t32>'||htmlspchar(cnv_str(ins_company_name_lp(e))) ||'</t32>'||EOL; --ins_company_name_lp2
1460 l_xml := l_xml||'<t33>'||htmlspchar(cnv_str(ins_type_lp(e))) ||'</t33>'||EOL; --ins_type_lp2
1461 l_xml := l_xml||'<t34>'||substr(ps_date,ps_date_length - 5,2) ||'</t34>'||EOL; --ins_period_start_year_lp2
1462 l_xml := l_xml||'<t35>'||substr(ps_date, ps_date_length - 3,2) ||'</t35>'||EOL; --ins_period_start_month_lp2
1463 l_xml := l_xml||'<t36>'||substr(ps_date, ps_date_length - 1,2) ||'</t36>'||EOL; --ins_period_start_day_lp2
1464 l_xml := l_xml||'<t37>'||htmlspchar(cnv_str(ins_period_lp(e))) ||'</t37>'||EOL; --ins_period_lp2
1465 l_xml := l_xml||'<t38>'||htmlspchar(cnv_str(contractor_name_lp(e))) ||'</t38>'||EOL; --contractor_name_lp2
1466 l_xml := l_xml||'<t39>'||htmlspchar(cnv_str(beneficiary_name_lp(e))) ||'</t39>'||EOL; --beneficiary_name_lp2
1467 l_xml := l_xml||'<t40>'||htmlspchar(cnv_str(beneficiary_relship_lp(e)))||'</t40>'||EOL; --beneficiary_relship_lp2
1468 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
1469 --
1470 ELSE
1471 --
1472 l_xml := l_xml||'<t32>'||' '||'</t32>'||EOL; --ins_company_name_lp2
1473 l_xml := l_xml||'<t33>'||' '||'</t33>'||EOL; --ins_type_lp2
1474 l_xml := l_xml||'<t34>'||' '||'</t34>'||EOL; --ins_period_start_year_lp2
1475 l_xml := l_xml||'<t35>'||' '||'</t35>'||EOL; --ins_period_start_month_lp2
1476 l_xml := l_xml||'<t36>'||' '||'</t36>'||EOL; --ins_period_start_day_lp2
1477 l_xml := l_xml||'<t37>'||' '||'</t37>'||EOL; --ins_period_lp2
1478 l_xml := l_xml||'<t38>'||' '||'</t38>'||EOL; --contractor_name_lp2
1479 l_xml := l_xml||'<t39>'||' '||'</t39>'||EOL; --beneficiary_name_lp2
1480 l_xml := l_xml||'<t40>'||' '||'</t40>'||EOL; --beneficiary_relship_lp2
1481 l_xml := l_xml||'<t41>'||' '||'</t41>'||EOL; --annual_prem_lp2
1482 --
1483 END IF;
1484 ELSE
1485 IF (ins_company_name_lp.count >= g) THEN
1486 --
1487 select to_char(ins_period_start_date_lp(g),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1488 into ps_date
1489 from dual;
1490 --
1491 ps_date_length := length(ps_date);
1492 --
1493 if class_new_old_lp(g) = 'N' then
1494 --
1495 l_t135a := g_msg_circle;
1496 --
1497 elsif class_new_old_lp(g) = 'O' then
1498 --
1499 l_t135b := g_msg_circle;
1500 --
1501 end if;
1502 l_xml := l_xml||'<t22>'||htmlspchar(cnv_str(ins_company_name_lp(g))) ||'</t22>'||EOL; --ins_company_name_lp1
1503 l_xml := l_xml||'<t23>'||htmlspchar(cnv_str(ins_type_lp(g))) ||'</t23>'||EOL; --ins_type_lp1
1504 l_xml := l_xml||'<t24>'||substr(ps_date,ps_date_length - 5,2) ||'</t24>'||EOL; --ins_period_start_year_lp1
1505 l_xml := l_xml||'<t25>'||substr(ps_date,ps_date_length - 3,2) ||'</t25>'||EOL; --ins_period_start_month_lp1
1506 l_xml := l_xml||'<t26>'||substr(ps_date,ps_date_length - 1,2) ||'</t26>'||EOL; --ins_period_start_day_lp1
1507 l_xml := l_xml||'<t27>'||htmlspchar(cnv_str(ins_period_lp(g))) ||'</t27>'||EOL; --ins_period_lp1
1508 l_xml := l_xml||'<t28>'||htmlspchar(cnv_str(contractor_name_lp(g))) ||'</t28>'||EOL; --contractor_name_lp1
1509 l_xml := l_xml||'<t29>'||htmlspchar(cnv_str(beneficiary_name_lp(g))) ||'</t29>'||EOL; --beneficiary_name_lp1
1510 l_xml := l_xml||'<t30>'||htmlspchar(cnv_str(beneficiary_relship_lp(g)))||'</t30>'||EOL; --beneficiary_relship_lp1
1511 l_xml := l_xml||'<t135a>'||l_t135a ||'</t135a>'||EOL; --class_new_old_lp1
1512 l_xml := l_xml||'<t135b>'||l_t135b ||'</t135b>'||EOL; --class_new_old_lp1
1513 l_xml := l_xml||'<t31>'||htmlspchar(to_char(to_number(annual_prem_lp(g)),fnd_currency.get_format_mask('JPY',40)))||'</t31>'||EOL; --annual_prem_lp1
1514 --
1515 ELSE
1516 --
1517 l_xml := l_xml||'<t22>'||' '||'</t22>'||EOL; --ins_company_name_lp1
1518 l_xml := l_xml||'<t23>'||' '||'</t23>'||EOL; --ins_type_lp1
1519 l_xml := l_xml||'<t24>'||' '||'</t24>'||EOL; --ins_period_start_year_lp1
1520 l_xml := l_xml||'<t25>'||' '||'</t25>'||EOL; --ins_period_start_month_lp1
1521 l_xml := l_xml||'<t26>'||' '||'</t26>'||EOL; --ins_period_start_day_lp1
1522 l_xml := l_xml||'<t27>'||' '||'</t27>'||EOL; --ins_period_lp1
1523 l_xml := l_xml||'<t28>'||' '||'</t28>'||EOL; --contractor_name_lp1
1524 l_xml := l_xml||'<t29>'||' '||'</t29>'||EOL; --beneficiary_name_lp1
1525 l_xml := l_xml||'<t30>'||' '||'</t30>'||EOL; --beneficiary_relship_ lp1
1526 l_xml := l_xml||'<t135a>'||' '||'</t135a>'||EOL; --class_new_old_lp1
1527 l_xml := l_xml||'<t135b>'||' '||'</t135b>'||EOL; --class_new_old_lp1
1528 l_xml := l_xml||'<t31>'||' '||'</t31>'||EOL; --annual_prem_lp1
1529 --
1530 END IF;
1531 --
1532 IF (ins_company_name_lp.count >= h) THEN
1533 --
1534 select to_char(ins_period_start_date_lp(h),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1535 into ps_date
1536 from dual;
1537 --
1538 ps_date_length := length(ps_date);
1539 --
1540 if class_new_old_lp(h) = 'N' then
1541 --
1542 l_t136a := g_msg_circle;
1543 --
1544 elsif class_new_old_lp(h) = 'O' then
1545 --
1546 l_t136b := g_msg_circle;
1547 --
1548 end if;
1549
1550 l_xml := l_xml||'<t32>'||htmlspchar(cnv_str(ins_company_name_lp(h))) ||'</t32>'||EOL; --ins_company_name_lp2
1551 l_xml := l_xml||'<t33>'||htmlspchar(cnv_str(ins_type_lp(h)) ) ||'</t33>'||EOL; --ins_type_lp2
1552 l_xml := l_xml||'<t34>'||substr(ps_date,ps_date_length - 5,2) ||'</t34>'||EOL; --ins_period_start_year_lp2
1553 l_xml := l_xml||'<t35>'||substr(ps_date, ps_date_length - 3,2) ||'</t35>'||EOL; --ins_period_start_month_lp2
1554 l_xml := l_xml||'<t36>'||substr(ps_date, ps_date_length - 1,2) ||'</t36>'||EOL; --ins_period_start_day_lp2
1555 l_xml := l_xml||'<t37>'||htmlspchar(cnv_str(ins_period_lp(h))) ||'</t37>'||EOL; --ins_period_lp2
1556 l_xml := l_xml||'<t38>'||htmlspchar(cnv_str(contractor_name_lp(h))) ||'</t38>'||EOL; --contractor_name_lp2
1557 l_xml := l_xml||'<t39>'||htmlspchar(cnv_str(beneficiary_name_lp(h))) ||'</t39>'||EOL; --beneficiary_name_lp2
1558 l_xml := l_xml||'<t40>'||htmlspchar(cnv_str(beneficiary_relship_lp(h)))||'</t40>'||EOL; --beneficiary_relship_lp2
1559 l_xml := l_xml||'<t136a>'||l_t136a ||'</t136a>'||EOL; --class_new_old_lp2
1560 l_xml := l_xml||'<t136b>'||l_t136b ||'</t136b>'||EOL; --class_new_old_lp2
1561 l_xml := l_xml||'<t41>'||htmlspchar(to_char(to_number(annual_prem_lp(h)),fnd_currency.get_format_mask('JPY',40)))||'</t41>'||EOL; --annual_prem_lp2
1562 --
1563 ELSE
1564 --
1565 l_xml := l_xml||'<t32>'||' '||'</t32>'||EOL; --ins_company_name_lp2
1566 l_xml := l_xml||'<t33>'||' '||'</t33>'||EOL; --ins_type_lp2
1567 l_xml := l_xml||'<t34>'||' '||'</t34>'||EOL; --ins_period_start_year_lp2
1568 l_xml := l_xml||'<t35>'||' '||'</t35>'||EOL; --ins_period_start_month_lp2
1569 l_xml := l_xml||'<t36>'||' '||'</t36>'||EOL; --ins_period_start_day_lp2
1570 l_xml := l_xml||'<t37>'||' '||'</t37>'||EOL; --ins_period_lp2
1571 l_xml := l_xml||'<t38>'||' '||'</t38>'||EOL; --contractor_name_lp2
1572 l_xml := l_xml||'<t39>'||' '||'</t39>'||EOL; --beneficiary_name_lp2
1573 l_xml := l_xml||'<t40>'||' '||'</t40>'||EOL; --beneficiary_relship_lp2
1574 l_xml := l_xml||'<t136a>'||' '||'</t136a>'||EOL; --class_new_old_lp2
1575 l_xml := l_xml||'<t136b>'||' '||'</t136b>'||EOL; --class_new_old_lp2
1576 l_xml := l_xml||'<t41>'||' '||'</t41>'||EOL; --annual_prem_lp2
1577 --
1578 END IF;
1579 --
1580 IF (ins_company_name_lp.count >= j) THEN
1581 --
1582 select to_char(ins_period_start_date_lp(j),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
1583 into ps_date
1584 from dual;
1585 --
1586 ps_date_length := length(ps_date);
1587 --
1588 if class_new_old_lp(j) = 'N' then
1589 --
1590 l_t137a := g_msg_circle;
1591 --
1592 elsif class_new_old_lp(j) = 'O' then
1593 --
1594 l_t137b := g_msg_circle;
1595 --
1596 end if;
1597
1598 l_xml := l_xml||'<t121>'||htmlspchar(cnv_str(ins_company_name_lp(j))) ||'</t121>'||EOL; --ins_company_name_lp3
1599 l_xml := l_xml||'<t122>'||htmlspchar(cnv_str(ins_type_lp(j))) ||'</t122>'||EOL; --ins_type_lp3
1600 l_xml := l_xml||'<t126>'||substr(ps_date,ps_date_length - 5,2) ||'</t126>'||EOL; --ins_period_start_year_lp3
1601 l_xml := l_xml||'<t127>'||substr(ps_date, ps_date_length - 3,2) ||'</t127>'||EOL; --ins_period_start_month_lp3
1602 l_xml := l_xml||'<t128>'||substr(ps_date, ps_date_length - 1,2) ||'</t128>'||EOL; --ins_period_start_day_lp3
1603 l_xml := l_xml||'<t123>'||htmlspchar(cnv_str(ins_period_lp(j))) ||'</t123>'||EOL; --ins_period_lp3
1604 l_xml := l_xml||'<t124>'||htmlspchar(cnv_str(contractor_name_lp(j))) ||'</t124>'||EOL; --contractor_name_lp3
1605 l_xml := l_xml||'<t125>'||htmlspchar(cnv_str(beneficiary_name_lp(j))) ||'</t125>'||EOL; --beneficiary_name_lp3
1606 l_xml := l_xml||'<t129>'||htmlspchar(cnv_str(beneficiary_relship_lp(j)))||'</t129>'||EOL; --beneficiary_relship_lp3
1607 l_xml := l_xml||'<t137a>'||l_t137a ||'</t137a>'||EOL; --class_new_old_lp3
1608 l_xml := l_xml||'<t137b>'||l_t137b ||'</t137b>'||EOL; --class_new_old_lp3
1609 l_xml := l_xml||'<t130>'||htmlspchar(to_char(to_number(annual_prem_lp(j)),fnd_currency.get_format_mask('JPY',40)))||'</t130>'||EOL; --annual_prem_lp3
1610 --
1611 ELSE
1612 --
1613 l_xml := l_xml||'<t121>'||' '||'</t121>'||EOL; --ins_company_name_lp3
1614 l_xml := l_xml||'<t122>'||' '||'</t122>'||EOL; --ins_type_lp3
1615 l_xml := l_xml||'<t126>'||' '||'</t126>'||EOL; --ins_period_start_year_lp3
1616 l_xml := l_xml||'<t127>'||' '||'</t127>'||EOL; --ins_period_start_month_lp3
1617 l_xml := l_xml||'<t128>'||' '||'</t128>'||EOL; --ins_period_start_day_lp3
1618 l_xml := l_xml||'<t123>'||' '||'</t123>'||EOL; --ins_period_lp3
1619 l_xml := l_xml||'<t124>'||' '||'</t124>'||EOL; --contractor_name_lp3
1620 l_xml := l_xml||'<t125>'||' '||'</t125>'||EOL; --beneficiary_name_lp3
1621 l_xml := l_xml||'<t129>'||' '||'</t129>'||EOL; --beneficiary_relship_lp3
1622 l_xml := l_xml||'<t137a>'||' '||'</t137a>'||EOL; --class_new_old_lp3
1623 l_xml := l_xml||'<t137b>'||' '||'</t137b>'||EOL; --class_new_old_lp3
1624 l_xml := l_xml||'<t130>'||' '||'</t130>'||EOL; --annual_prem_lp3
1625 --
1626 END IF;
1627
1628 END IF;
1629 --
1630 -- Writing l_xml to vXMLTable.
1631 vXMLTable(vCtr).xmlstring := l_xml;
1632 vCtr := vCtr + 1;
1633 --
1634 IF (ins_company_name_nl.count >= d) THEN
1635 --
1636 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
1637 --
1638 l_t48 := cnv_str(maturity_repayment_nl(d),1,3);
1639 --
1640 else
1641 --
1642 if nonlife_ins_term_type_nl(d) = 'EQ' then
1643 --
1644 l_t48a := g_msg_circle;
1645 --
1646 elsif nonlife_ins_term_type_nl(d) = 'L' then
1647 --
1648 l_t48b := g_msg_circle;
1649 --
1650 end if;
1651 --
1652 end if;
1653 --
1654 l_xml := '<t42>' ||htmlspchar(cnv_str(ins_company_name_nl(d))) ||'</t42>' ||EOL; --ins_company_name_nl1
1655 l_xml := l_xml||'<t43>' ||htmlspchar(cnv_str(ins_type_nl(d))) ||'</t43>' ||EOL; --ins_type_nl1
1656 l_xml := l_xml||'<t44>' ||htmlspchar(cnv_str(ins_period_nl(d))) ||'</t44>' ||EOL; --ins_period_nl1
1657 l_xml := l_xml||'<t45>' ||htmlspchar(cnv_str(contractor_name_nl(d))) ||'</t45>' ||EOL; --contractor_name_nl1
1658 l_xml := l_xml||'<t46>' ||htmlspchar(cnv_str(beneficiary_name_nl(d))) ||'</t46>' ||EOL; --beneficiary_name_nl1
1659 l_xml := l_xml||'<t47>' ||htmlspchar(cnv_str(beneficiary_relship_nl(d)))||'</t47>' ||EOL; --beneficiary_relship_nl1
1660 l_xml := l_xml||'<t48>' ||htmlspchar(l_t48) ||'</t48>' ||EOL; --maturity_repayment_nl1
1661 l_xml := l_xml||'<t48a>'||l_t48a ||'</t48a>'||EOL; --nonlife_ins_term_type_nl1
1662 l_xml := l_xml||'<t48b>'||l_t48b ||'</t48b>'||EOL; --nonlife_ins_term_type_nl1
1663 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
1664 --
1665 ELSE
1666 --
1667 l_xml := '<t42>' ||' '||'</t42>' ||EOL; --ins_company_name_nl1
1668 l_xml := l_xml||'<t43>' ||' '||'</t43>' ||EOL; --ins_type_nl1
1669 l_xml := l_xml||'<t44>' ||' '||'</t44>' ||EOL; --ins_period_nl1
1670 l_xml := l_xml||'<t45>' ||' '||'</t45>' ||EOL; --contractor_name_nl1
1671 l_xml := l_xml||'<t46>' ||' '||'</t46>' ||EOL; --beneficiary_name_nl1
1672 l_xml := l_xml||'<t47>' ||' '||'</t47>' ||EOL; --beneficiary_relship_nl1
1673 l_xml := l_xml||'<t48>' ||' '||'</t48>' ||EOL; --maturity_repayment_nl1
1674 l_xml := l_xml||'<t48a>'||' '||'</t48a>'||EOL; --nonlife_ins_term_type_nl1
1675 l_xml := l_xml||'<t48b>'||' '||'</t48b>'||EOL; --nonlife_ins_term_type_nl1
1676 l_xml := l_xml||'<t49>' ||' '||'</t49>' ||EOL; --annual_prem_nl1
1677 --
1678 END IF;
1679 --
1680 IF (ins_company_name_nl.count >= e) THEN
1681 --
1682 if isdf_employer_c.effective_date < c_st_upd_date_2007 then
1683 --
1684 l_t56 := cnv_str(maturity_repayment_nl(e));
1685 --
1686 else
1687 --
1688 if nonlife_ins_term_type_nl(e) = 'EQ' then
1689 --
1690 l_t56a := g_msg_circle;
1691 --
1692 elsif nonlife_ins_term_type_nl(e) = 'L' then
1693 --
1694 l_t56b := g_msg_circle;
1695 --
1696 end if;
1697 --
1698 end if;
1699 --
1700 l_xml := l_xml||'<t50>' ||htmlspchar(cnv_str(ins_company_name_nl(e))) ||'</t50>' ||EOL; --ins_company_name_nl2
1701 l_xml := l_xml||'<t51>' ||htmlspchar(cnv_str(ins_type_nl(e))) ||'</t51>' ||EOL; --ins_type_nl2
1702 l_xml := l_xml||'<t52>' ||htmlspchar(cnv_str(ins_period_nl(e))) ||'</t52>' ||EOL; --ins_period_nl2
1703 l_xml := l_xml||'<t53>' ||htmlspchar(cnv_str(contractor_name_nl(e))) ||'</t53>' ||EOL; --contractor_name_nl2
1704 l_xml := l_xml||'<t54>' ||htmlspchar(cnv_str(beneficiary_name_nl(e))) ||'</t54>' ||EOL; --beneficiary_name_nl2
1705 l_xml := l_xml||'<t55>' ||htmlspchar(cnv_str(beneficiary_relship_nl(e)))||'</t55>' ||EOL; --beneficiary_relship_nl2
1706 l_xml := l_xml||'<t56>' ||htmlspchar(l_t56) ||'</t56>' ||EOL; --maturity_repayment_nl2
1707 l_xml := l_xml||'<t56a>'||l_t56a ||'</t56a>'||EOL; --nonlife_ins_term_type_nl2
1708 l_xml := l_xml||'<t56b>'||l_t56b ||'</t56b>'||EOL; --nonlife_ins_term_type_nl2
1709 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
1710 --
1711 ELSE
1712 --
1713 l_xml := l_xml||'<t50>' ||' '||'</t50>' ||EOL; --ins_company_name_nl2
1714 l_xml := l_xml||'<t51>' ||' '||'</t51>' ||EOL; --ins_type_nl2
1715 l_xml := l_xml||'<t52>' ||' '||'</t52>' ||EOL; --ins_period_nl2
1716 l_xml := l_xml||'<t53>' ||' '||'</t53>' ||EOL; --contractor_name_nl2
1717 l_xml := l_xml||'<t54>' ||' '||'</t54>' ||EOL; --beneficiary_name_nl2
1718 l_xml := l_xml||'<t55>' ||' '||'</t55>' ||EOL; --beneficiary_relship_nl2
1719 l_xml := l_xml||'<t56>' ||' '||'</t56>' ||EOL; --maturity_repayment_nl2
1720 l_xml := l_xml||'<t56a>'||' '||'</t56a>'||EOL; --nonlife_ins_term_type_nl2
1721 l_xml := l_xml||'<t56b>'||' '||'</t56b>'||EOL; --nonlife_ins_term_type_nl2
1722 l_xml := l_xml||'<t57>' ||' '||'</t57>' ||EOL; --annual_prem_nl2
1723 --
1724 END IF;
1725 --
1726 IF isdf_employer_c.effective_date < c_st_upd_date_2012 then
1727 IF (ins_type_s.count >= a) THEN
1728 --
1729 l_xml := l_xml||'<t58>'||htmlspchar(cnv_str(ins_type_s(a))) ||'</t58>'||EOL; --ins_type_s1
1730 l_xml := l_xml||'<t59>'||htmlspchar(cnv_str(ins_payee_name_s(a))) ||'</t59>'||EOL; --ins_payee_name_s1
1731 l_xml := l_xml||'<t60>'||htmlspchar(cnv_str(debtor_name_s(a))) ||'</t60>'||EOL; --debtor_name_s1
1732 l_xml := l_xml||'<t61>'||htmlspchar(cnv_str(beneficiary_relship_s(a)))||'</t61>'||EOL; --beneficiary_relship_s1
1733 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
1734 --
1735 ELSE
1736 --
1737 l_xml := l_xml||'<t58>'||' '||'</t58>'||EOL; --ins_type_s1
1738 l_xml := l_xml||'<t59>'||' '||'</t59>'||EOL; --ins_payee_name_s1
1739 l_xml := l_xml||'<t60>'||' '||'</t60>'||EOL; --debtor_name_s1
1740 l_xml := l_xml||'<t61>'||' '||'</t61>'||EOL; --beneficiary_relship_s1
1741 l_xml := l_xml||'<t62>'||' '||'</t62>'||EOL; --annual_prem_s1
1742 --
1743 END IF;
1744 --
1745 IF (ins_type_s.count >= b) THEN
1746 --
1747 l_xml := l_xml||'<t63>'||htmlspchar(cnv_str(ins_type_s(b))) ||'</t63>'||EOL; --ins_type_s2
1748 l_xml := l_xml||'<t64>'||htmlspchar(cnv_str(ins_payee_name_s(b))) ||'</t64>'||EOL; --ins_payee_name_s2
1749 l_xml := l_xml||'<t65>'||htmlspchar(cnv_str(debtor_name_s(b))) ||'</t65>'||EOL; --debtor_name_s2
1750 l_xml := l_xml||'<t66>'||htmlspchar(cnv_str(beneficiary_relship_s(b)))||'</t66>'||EOL; --beneficiary_relship_s2
1751 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
1752 --
1753 ELSE
1754 --
1755 l_xml := l_xml||'<t63>'||' '||'</t63>'||EOL; --ins_type_s2
1756 l_xml := l_xml||'<t64>'||' '||'</t64>'||EOL; --ins_payee_name_s2
1757 l_xml := l_xml||'<t65>'||' '||'</t65>'||EOL; --debtor_name_s2
1758 l_xml := l_xml||'<t66>'||' '||'</t66>'||EOL; --beneficiary_relship_s2
1759 l_xml := l_xml||'<t67>'||' '||'</t67>'||EOL; --annual_prem_s2
1760 --
1761 END IF;
1762 --
1763 IF (ins_type_s.count >= c) THEN
1764 --
1765 l_xml := l_xml||'<t68>'||htmlspchar(cnv_str(ins_type_s(c))) ||'</t68>'||EOL; --ins_type_s3
1766 l_xml := l_xml||'<t69>'||htmlspchar(cnv_str(ins_payee_name_s(c))) ||'</t69>'||EOL; --ins_payee_name_s3
1767 l_xml := l_xml||'<t70>'||htmlspchar(cnv_str(debtor_name_s(c))) ||'</t70>'||EOL; --debtor_name_s3
1768 l_xml := l_xml||'<t71>'||htmlspchar(cnv_str(beneficiary_relship_s(c)))||'</t71>'||EOL; --beneficiary_relship_s3
1769 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
1770 --
1771 ELSE
1772 --
1773 l_xml := l_xml||'<t68>'||' '||'</t68>'||EOL; --ins_type_s3
1774 l_xml := l_xml||'<t69>'||' '||'</t69>'||EOL; --ins_payee_name_s3
1775 l_xml := l_xml||'<t70>'||' '||'</t70>'||EOL; --debtor_name_s3
1776 l_xml := l_xml||'<t71>'||' '||'</t71>'||EOL; --beneficiary_relship_s3
1777 l_xml := l_xml||'<t72>'||' '||'</t72>'||EOL; --annual_prem_s3
1778 --
1779 END IF;
1780 --
1781 ELSE
1782 IF (ins_type_s.count >= d) THEN
1783 --
1784 l_xml := l_xml||'<t58>'||htmlspchar(cnv_str(ins_type_s(d))) ||'</t58>'||EOL; --ins_type_s1
1785 l_xml := l_xml||'<t59>'||htmlspchar(cnv_str(ins_payee_name_s(d))) ||'</t59>'||EOL; --ins_payee_name_s1
1786 l_xml := l_xml||'<t60>'||htmlspchar(cnv_str(debtor_name_s(d))) ||'</t60>'||EOL; --debtor_name_s1
1787 l_xml := l_xml||'<t61>'||htmlspchar(cnv_str(beneficiary_relship_s(d)))||'</t61>'||EOL; --beneficiary_relship_s1
1788 l_xml := l_xml||'<t62>'||to_char(to_number(annual_prem_s(d)),fnd_currency.get_format_mask('JPY',40))||'</t62>'||EOL; --annual_prem_s1
1789 --
1790 ELSE
1791 --
1792 l_xml := l_xml||'<t58>'||' '||'</t58>'||EOL; --ins_type_s1
1793 l_xml := l_xml||'<t59>'||' '||'</t59>'||EOL; --ins_payee_name_s1
1794 l_xml := l_xml||'<t60>'||' '||'</t60>'||EOL; --debtor_name_s1
1795 l_xml := l_xml||'<t61>'||' '||'</t61>'||EOL; --beneficiary_relship_s1
1796 l_xml := l_xml||'<t62>'||' '||'</t62>'||EOL; --annual_prem_s1
1797 --
1798 END IF;
1799 --
1800 IF (ins_type_s.count >= e) THEN
1801 --
1802 l_xml := l_xml||'<t63>'||htmlspchar(cnv_str(ins_type_s(e))) ||'</t63>'||EOL; --ins_type_s2
1803 l_xml := l_xml||'<t64>'||htmlspchar(cnv_str(ins_payee_name_s(e))) ||'</t64>'||EOL; --ins_payee_name_s2
1804 l_xml := l_xml||'<t65>'||htmlspchar(cnv_str(debtor_name_s(e))) ||'</t65>'||EOL; --debtor_name_s2
1805 l_xml := l_xml||'<t66>'||htmlspchar(cnv_str(beneficiary_relship_s(e)))||'</t66>'||EOL; --beneficiary_relship_s2
1806 l_xml := l_xml||'<t67>'||to_char(to_number(annual_prem_s(e)),fnd_currency.get_format_mask('JPY',40))||'</t67>'||EOL; --annual_prem_s2
1807 --
1808 ELSE
1809 --
1810 l_xml := l_xml||'<t63>'||' '||'</t63>'||EOL; --ins_type_s2
1811 l_xml := l_xml||'<t64>'||' '||'</t64>'||EOL; --ins_payee_name_s2
1812 l_xml := l_xml||'<t65>'||' '||'</t65>'||EOL; --debtor_name_s2
1813 l_xml := l_xml||'<t66>'||' '||'</t66>'||EOL; --beneficiary_relship_s2
1814 l_xml := l_xml||'<t67>'||' '||'</t67>'||EOL; --annual_prem_s2
1815 --
1816 END IF;
1817 END IF;
1818 --
1819 l_xml := l_xml||'</page>'||EOL;
1820 --
1821 vXMLTable(vCtr).xmlstring := l_xml;
1822 vCtr := vCtr + 1;
1823 --
1824 i := i + 1;
1825 --
1826 END LOOP;
1827 --
1828 -- Code to generate XML for second page of template starts
1829 -- dummy field is added to get as many prints of second page as
1830 -- the number of employees for whom the report is run.
1831 l_xml2 := '</isdf1>'||EOL||
1832 '<isdf2>' ||EOL||
1833 '<dummy></dummy>'||EOL|| -- This is dummy field
1834 '</isdf2>'||EOL||'</isdf>'||EOL ;
1835 --
1836 vXMLTable(vCtr).xmlstring := l_xml2;
1837 vCtr := vCtr + 1;
1838 --
1839 END assact_xml;
1840 --
1841 /****************************************************************************
1842 Name : WritetoCLOB
1843 Arguments : returns XML
1844 Description : This procedure selects the xml from vXMLTable and writes it
1845 into a clob variable. This clob variable is then returned.
1846 *****************************************************************************/
1847 PROCEDURE WritetoCLOB (p_write_xml OUT NOCOPY CLOB)
1848 IS
1849 l_xfdf_string CLOB;
1850 ctr_table NUMBER;
1851 tempclob clob;
1852 BEGIN
1853 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1854 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1855 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1856 LOOP
1857 dbms_lob.writeAppend(l_xfdf_string,
1858 length(vXMLTable(ctr_table).xmlstring),
1859 vXMLTable(ctr_table).xmlstring );
1860 END LOOP;
1861 p_write_xml := l_xfdf_string;
1862 hr_utility.set_location('Out of loop ', 99);
1863 dbms_lob.close(l_xfdf_string);
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1867 HR_UTILITY.RAISE_ERROR;
1868 END WritetoCLOB;
1869 --
1870 /****************************************************************************
1871 Name : get_cp_xml
1872 Arguments : p_assignment_action_id
1873 p_xml
1874 Description : This procedure creates and returns the xml for the
1875 assignment_action_id passed as parameter.
1876 *****************************************************************************/
1877 PROCEDURE get_cp_xml(p_assignment_action_id IN NUMBER,
1878 p_xml OUT NOCOPY CLOB) IS
1879 BEGIN
1880 assact_xml(p_assignment_action_id);
1881 WritetoCLOB (p_xml);
1882 END get_cp_xml;
1883 --
1884 /****************************************************************************
1885 Name : get_ss_xml
1886 Arguments : p_assignment_action_id
1887 p_xml
1888 Description : This procedure creates and returns the xml for the
1889 assignment_action_id passed as parameter. This is called
1890 for single report from Self-Service page.
1891 *****************************************************************************/
1892 PROCEDURE get_ss_xml(p_assignment_action_id IN NUMBER,
1893 p_xml OUT NOCOPY CLOB) IS
1894 p_ss_xml CLOB;
1895 l_header CHAR(200);
1896 l_footer VARCHAR2(50);
1897 l_xml CLOB;
1898 BEGIN
1899 l_header := '<?xml version="1.0" encoding="UTF-8"?>' || EOL ||'<ROOT>';
1900 l_footer := '</ROOT>';
1901 assact_xml(p_assignment_action_id);
1902 WritetoCLOB(p_ss_xml);
1903 dbms_lob.createtemporary(l_xml,TRUE) ;
1904 dbms_lob.writeAppend(l_xml,
1905 length(l_header),
1906 l_header);
1907 dbms_lob.append(l_xml, p_ss_xml);
1908 dbms_lob.writeAppend(l_xml,
1909 length(l_footer),
1910 l_footer);
1911 p_xml := l_xml ;
1912 END get_ss_xml;
1913 --
1914 /****************************************************************************
1915 Name : generate_xml
1916 Description : This procedure fetches archived data, converts it to XML
1917 format and appends to pay_mag_tape.g_clob_value.
1918 *****************************************************************************/
1919 PROCEDURE generate_xml AS
1920 l_old_assact_id NUMBER;
1921 l_final_xml_string CLOB;
1922 xml_string1 VARCHAR2(2000);
1923 l_pact_id NUMBER;
1924 l_cur_pact NUMBER;
1925 l_legislative_parameters VARCHAR(2000);
1926 l_cur_assact NUMBER ;
1927 l_proc_name VARCHAR2(60) ;
1928 l_offset NUMBER;
1929 l_amount NUMBER;
1930 --
1931 BEGIN
1932 --
1933 IF g_debug THEN
1934 l_proc_name := g_proc_name || 'GENERATE_XML';
1935 hr_utility.trace ('Entering '||l_proc_name);
1936 END IF ;
1937 --
1938 l_cur_assact := pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID' );
1939 l_cur_pact := pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID' );
1940 --
1941 SELECT legislative_parameters
1942 INTO l_legislative_parameters
1943 FROM pay_payroll_actions
1944 WHERE payroll_action_id = l_cur_pact;
1945 --
1946 l_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',l_legislative_parameters));
1947 l_emp_no_opt := pay_core_utils.get_parameter('PRN_EMP_NO',l_legislative_parameters);
1948 --
1949 SELECT paa1.assignment_action_id
1950 INTO l_old_assact_id
1951 FROM pay_assignment_actions paa,
1952 pay_assignment_actions paa1
1953 WHERE paa.assignment_action_id = l_cur_assact
1954 AND paa.assignment_id = paa1.assignment_id
1955 AND paa1.payroll_action_id = l_pact_id;
1956 --
1957 get_cp_xml(l_old_assact_id, l_final_xml_string);
1958 --
1959 l_offset := 1 ;
1960 l_amount := 500;
1961 --
1962 LOOP
1963 xml_string1 := null;
1964 dbms_lob.read(l_final_xml_string,l_amount,l_offset,xml_string1);
1965 pay_core_files.write_to_magtape_lob(xml_string1);
1966 l_offset := l_offset + l_amount ;
1967 END LOOP;
1968 EXCEPTION
1969 WHEN no_data_found THEN
1970 hr_utility.trace ('exiting from loop');
1971 --
1972 IF g_debug THEN
1973 hr_utility.trace ('Leaving '||l_proc_name);
1974 END IF ;
1975 END generate_xml;
1976 --
1977 /****************************************************************************
1978 Name : gen_xml_header
1979 Description : This procedure generates XML header information and appends to
1980 pay_mag_tape.g_clob_value.
1981 *****************************************************************************/
1982 PROCEDURE gen_xml_header AS
1983 l_proc_name varchar2(100);
1984 l_buf varchar2(2000);
1985 --
1986 BEGIN
1987 if g_debug then
1988 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
1989 hr_utility.trace ('Entering '||l_proc_name);
1990 end if ;
1991 --
1992 vXMLTable.DELETE; -- delete the pl/sql table
1993 --
1994 -- l_buf := '<?xml version="1.0" encoding="UTF-8"?>'||EOL ;
1995 l_buf := EOL ||'<ROOT>'||EOL ;
1996 --
1997 pay_core_files.write_to_magtape_lob(l_buf);
1998 --
1999 if g_debug then
2000 hr_utility.trace ('CLOB contents after appending header information');
2001 hr_utility.trace ('Leaving '||l_proc_name);
2002 end if ;
2003 END gen_xml_header;
2004 --
2005 /****************************************************************************
2006 Name : gen_xml_footer
2007 Desc : Footer
2008 *****************************************************************************/
2009 PROCEDURE gen_xml_footer AS
2010 l_buf varchar2(2000) ;
2011 l_proc_name varchar2(100);
2012 BEGIN
2013 --
2014 if g_debug then
2015 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
2016 hr_utility.trace ('Entering '||l_proc_name);
2017 end if ;
2018 l_buf := '</ROOT>' ;
2019 --
2020 pay_core_files.write_to_magtape_lob(l_buf);
2021 --
2022 if g_debug then
2023 hr_utility.trace ('CLOB contents after appending footer information');
2024 hr_utility.trace ('Leaving '||l_proc_name);
2025 end if ;
2026 --
2027 END gen_xml_footer;
2028 --
2029 /****************************************************************************
2030 Function Name : submit_report
2031 Arguments :
2032 Description :
2033 *****************************************************************************/
2034 -- decommission in accordance with parameter change by bug 10273341 for 12.2
2035 -- since this file is now dual checkin maintenanced
2036 -- and not used in any product code
2037 /*
2038 --function submit_report(p_pact_id IN NUMBER,
2039 -- p_assset_id IN NUMBER,
2040 -- p_eff_date IN VARCHAR2) return number
2041 --is
2042 -- l_request_id number;
2043 -- l_phase VARCHAR2(100);
2044 -- l_status VARCHAR2(100);
2045 -- l_dev_status VARCHAR2(100);
2046 -- l_dev_phase VARCHAR2(100);
2047 -- l_message VARCHAR2(2000);
2048 -- l_action_completed BOOLEAN;
2049 -- l_req_id NUMBER;
2050 ----
2051 --begin
2052 ---- Submit the request
2053 ----
2054 -- l_request_id := fnd_request.submit_request( Application => 'PAY',
2055 -- Program => 'PAYJPXML',
2056 -- Description => 'JP Life Insurance Notification Report',
2057 -- argument1 => 'ARCHIVE',
2058 -- argument2 => 'XML',
2059 -- argument3 => 'JP',
2060 -- argument4 => NULL,
2061 -- argument5 => p_eff_date,
2062 -- argument6 => 'XML',
2063 -- argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
2064 -- argument8 => NULL,
2065 -- argument9 => NULL,
2066 -- argument10 => p_pact_id,
2067 -- argument11 => 'PAYROLL_ACTION_ID='||p_pact_id,
2068 -- argument12 => p_assset_id,
2069 -- argument13 => 'ASSIGNMENT_SET_ID='||p_assset_id);
2070 ----
2071 -- -- Check the status
2072 -- if l_request_id <> 0 then
2073 -- -- Save the request and wait for completion
2074 -- Commit;
2075 -- l_dev_phase := 'ZZZ';
2076 -- WHILE (l_dev_phase <> 'COMPLETE')
2077 -- LOOP
2078 -- l_action_completed := FND_CONCURRENT.WAIT_FOR_REQUEST(
2079 -- request_id => l_request_id
2080 -- ,interval => 1
2081 -- ,max_wait => 10
2082 -- ,phase => l_phase
2083 -- ,status => l_status
2084 -- ,dev_phase => l_dev_phase
2085 -- ,dev_status => l_dev_status
2086 -- ,message => l_message);
2087 -- END LOOP;
2088 -- end if;
2089 --return l_request_id;
2090 --end submit_report;
2091 */
2092 --
2093 END pay_jp_isdf_rpt;