[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_REPORT_PKG
Source
1 PACKAGE BODY PAY_JP_REPORT_PKG AS
2 /* $Header: pyjprep.pkb 120.17 2011/07/29 08:33:53 keyazawa ship $ */
3 g_end_of_time date := hr_general.end_of_time;
4 g_meiji_from date := to_date('1868-09-08','YYYY-MM-DD');
5 g_meiji_to date := to_date('1912-07-29','YYYY-MM-DD');
6 g_taishou_from date := to_date('1912-07-30','YYYY-MM-DD');
7 g_taishou_to date := to_date('1926-12-24','YYYY-MM-DD');
8 g_shouwa_from date := to_date('1926-12-25','YYYY-MM-DD');
9 g_shouwa_to date := to_date('1989-01-07','YYYY-MM-DD');
10 g_heisei_from date := to_date('1989-01-08','YYYY-MM-DD');
11 g_heisei_to date := g_end_of_time;
12
13 g_husband_user_name fnd_new_messages.message_text%type := fnd_message.get_string('PAY','PAY_JP_HUSBAND');
14 g_wife_user_name fnd_new_messages.message_text%type := fnd_message.get_string('PAY','PAY_JP_WIFE');
15 --
16 c_legislation_code varchar2(2) := 'JP';
17 c_com_hi_smr_info_elm pay_element_types_f.element_name%type := 'COM_HI_SMR_INFO';
18 c_com_wp_smr_info_elm pay_element_types_f.element_name%type := 'COM_WP_SMR_INFO';
19 c_appl_mth_iv pay_input_values_f.name%type := 'APPLY_MTH';
20 c_appl_cat_iv pay_input_values_f.name%type := 'APPLY_TYPE';
21 c_com_si_info_elm pay_element_types_f.element_name%type := 'COM_SI_INFO';
22 c_hi_org_iv pay_input_values_f.name%type := 'HI_LOCATION';
23 c_wp_org_iv pay_input_values_f.name%type := 'WP_LOCATION';
24 c_wpf_org_iv pay_input_values_f.name%type := 'WPF_LOCATION';
25 c_hi_num_iv pay_input_values_f.name%type := 'HI_CARD_NUM';
26 c_wp_num_iv pay_input_values_f.name%type := 'WP_SERIAL_NUM';
27 c_bp_num_iv pay_input_values_f.name%type := 'BASIC_PENSION_NUM';
28 c_com_si_rep_elm pay_element_types_f.element_name%type := 'COM_SI_REPORT_INFO';
29 c_exc_iv pay_input_values_f.name%type := 'OUTPUT_FLAG';
30 c_san_ele_set pay_element_sets.element_set_name%type := 'SAN';
31 c_gep_ele_set pay_element_sets.element_set_name%type := 'GEP';
32 c_iku_ele_set pay_element_sets.element_set_name%type := 'IKU';
33 --
34 c_com_hi_q_info_elm pay_element_types_f.element_name%type := 'COM_HI_QUALIFY_INFO';
35 c_com_wp_q_info_elm pay_element_types_f.element_name%type := 'COM_WP_QUALIFY_INFO';
36 c_com_wpf_q_info_elm pay_element_types_f.element_name%type := 'COM_WPF_QUALIFY_INFO';
37 c_qd_iv pay_input_values_f.name%type := 'QUALIFY_DATE';
38 c_dqd_iv pay_input_values_f.name%type := 'DISQUALIFY_DATE';
39 --
40 c_hi_number varchar2(20) := 'HI_NUMBER';
41 c_wp_number varchar2(20) := 'WP_NUMBER';
42 c_hi_num_sort number := 1;
43 c_wp_num_sort number := 2;
44 --
45 g_debug boolean := hr_utility.debug_enabled;
46 c_lf constant varchar2(1) := fnd_global.local_chr(10);
47 c_cr constant varchar2(1) := fnd_global.local_chr(13);
48 c_max_line_size binary_integer := 32767;
49 c_comma_delimiter varchar2(1) := ',';
50 c_dot_delimiter varchar2(1) := '.';
51 --
52 c_circle varchar2(60) := substr(fnd_message.get_string('PER','HR_JP_CIRCLE'),0,1);
53 --
54 --------------------------------------------------------------
55 -- INSERT_SESSION_DATE --
56 --------------------------------------------------------------
57 PROCEDURE INSERT_SESSION_DATE(
58 P_EFFECTIVE_DATE IN DATE)
59 IS
60 BEGIN
61 delete_session_date;
62
63 insert into fnd_sessions(session_id,effective_date)
64 select userenv('sessionid'),trunc(p_effective_date)
65 from dual;
66 END INSERT_SESSION_DATE;
67
68 --------------------------------------------------------------
69 -- DELETE_SESSION_DATE --
70 --------------------------------------------------------------
71 PROCEDURE DELETE_SESSION_DATE
72 IS
73 BEGIN
74 delete from fnd_sessions where session_id=userenv('sessionid');
75 END DELETE_SESSION_DATE;
76
77 -----------------------------------------------------
78 -- TO_ERA --
79 -----------------------------------------------------
80 PROCEDURE TO_ERA( p_date IN DATE,
81 p_era_code OUT NOCOPY NUMBER,
82 p_year OUT NOCOPY NUMBER,
83 p_month OUT NOCOPY NUMBER,
84 p_day OUT NOCOPY NUMBER)
85 IS
86 BEGIN
87 if p_date between g_meiji_from and g_meiji_to then
88 p_era_code := '1';
89 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_meiji_from,'YYYY'))+1;
90 p_month := to_number(to_char(p_date,'MM'));
91 p_day := to_number(to_char(p_date,'DD'));
92 elsif p_date between g_taishou_from and g_taishou_to then
93 p_era_code := '3';
94 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_taishou_from,'YYYY'))+1;
95 p_month := to_number(to_char(p_date,'MM'));
96 p_day := to_number(to_char(p_date,'DD'));
97 elsif p_date between g_shouwa_from and g_shouwa_to then
98 p_era_code := '5';
99 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_shouwa_from,'YYYY'))+1;
100 p_month := to_number(to_char(p_date,'MM'));
101 p_day := to_number(to_char(p_date,'DD'));
102 elsif p_date between g_heisei_from and g_heisei_to then
103 p_era_code := '7';
104 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_heisei_from,'YYYY'))+1;
105 p_month := to_number(to_char(p_date,'MM'));
106 p_day := to_number(to_char(p_date,'DD'));
107 else
108 p_era_code := NULL;
109 p_year := NULL;
110 p_month := NULL;
111 p_day := NULL;
112 end if;
113 END TO_ERA;
114
115 -----------------------------------------------------
116 -- GET_CONCATENATED_NUMBERS --
117 -----------------------------------------------------
118 FUNCTION get_concatenated_numbers(
119 p_number1 IN NUMBER,
120 p_number2 IN NUMBER,
121 p_number3 IN NUMBER,
122 p_number4 IN NUMBER,
123 p_number5 IN NUMBER,
124 p_number6 IN NUMBER,
125 p_number7 IN NUMBER,
126 p_number8 IN NUMBER,
127 p_number9 IN NUMBER,
128 p_number10 IN NUMBER) RETURN VARCHAR2
129 IS
130 l_concatenated_numbers VARCHAR2(150);
131 BEGIN
132 l_concatenated_numbers:=NULL;
133
134 if p_number1 is not NULL then
135 if l_concatenated_numbers is NULL then
136 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number1);
137 else
138 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number1);
139 end if;
140 end if;
141 if p_number2 is not NULL then
142 if l_concatenated_numbers is NULL then
143 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number2);
144 else
145 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number2);
146 end if;
147 end if;
148 if p_number3 is not NULL then
149 if l_concatenated_numbers is NULL then
150 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number3);
151 else
152 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number3);
153 end if;
154 end if;
155 if p_number4 is not NULL then
156 if l_concatenated_numbers is NULL then
157 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number4);
158 else
159 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number4);
160 end if;
161 end if;
162 if p_number5 is not NULL then
163 if l_concatenated_numbers is NULL then
164 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number5);
165 else
166 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number5);
167 end if;
168 end if;
169 if p_number6 is not NULL then
170 if l_concatenated_numbers is NULL then
171 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number6);
172 else
173 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number6);
174 end if;
175 end if;
176 if p_number7 is not NULL then
177 if l_concatenated_numbers is NULL then
178 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number7);
179 else
180 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number7);
181 end if;
182 end if;
183 if p_number8 is not NULL then
184 if l_concatenated_numbers is NULL then
185 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number8);
186 else
187 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number8);
188 end if;
189 end if;
190 if p_number9 is not NULL then
191 if l_concatenated_numbers is NULL then
192 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number9);
193 else
194 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number9);
195 end if;
196 end if;
197 if p_number10 is not NULL then
198 if l_concatenated_numbers is NULL then
199 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number10);
200 else
201 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number10);
202 end if;
203 end if;
204
205 return l_concatenated_numbers;
206 END get_concatenated_numbers;
207
208 -----------------------------------------------------
209 -- GET_CONCATENATED_DEPENDENTS --
210 -----------------------------------------------------
211 FUNCTION get_concatenated_dependents(
212 p_person_id IN NUMBER,
213 p_effective_date IN DATE,
214 p_kanji_flag IN VARCHAR2
215 ) RETURN VARCHAR2
216 IS
217 l_first_flag BOOLEAN := TRUE;
218 l_terminator VARCHAR2(10);
219 l_description VARCHAR2(2000);
220 --
221 CURSOR csr_dependent IS
222 -- Pay attention that can't rename the column name of view
223 select pp.last_name EE_LAST_NAME_KANA,
224 pp.per_information18 EE_LAST_NAME,
225 pcon.last_name CON_LAST_NAME_KANA,
226 pcon.first_name CON_FIRST_NAME_KANA,
227 pcon.per_information18 CON_LAST_NAME,
228 pcon.per_information19 CON_FIRST_NAME,
229 decode(pcon.sex,'M',1,'F',2,3) SEX_ORDER,
230 pcon.date_of_birth DATE_OF_BIRTH,
231 decode(pcr.contact_type,'S',decode(pcon.sex,'F',fnd_message.get_string('PAY','PAY_JP_WIFE'),fnd_message.get_string('PAY','PAY_JP_HUSBAND')),flv1.meaning) CONTACT_TYPE,
232 decode(pcr.contact_type,'S',decode(pcon.sex,'F',fnd_message.get_string('PAY','PAY_JP_WIFE_KANA'),fnd_message.get_string('PAY','PAY_JP_HUSBAND_KANA')),flv2.meaning) CONTACT_TYPE_KANA
233 from
234 hr_lookups flv2,
235 hr_lookups flv1,
236 per_all_people_f pcon,
237 per_contact_relationships pcr,
238 per_all_people_f pp
239 where pp.person_id=p_person_id
240 and p_effective_date
241 between pp.effective_start_date and pp.effective_end_date
242 and pcr.person_id=pp.person_id
243 and pcr.dependent_flag='Y'
244 and p_effective_date
245 between pcr.date_start and nvl(pcr.date_end,to_date('4712-12-31','YYYY-MM-DD'))
246 and pcon.person_id=pcr.contact_person_id
247 and ( (p_effective_date
248 between pcon.effective_start_date and pcon.effective_end_date)
249 or (not exists(
250 select NULL
251 from per_all_people_f pcon2
252 where pcon2.person_id=pcon.person_id
253 and p_effective_date
254 between pcon2.effective_start_date and pcon2.effective_end_date)
255 and pcon.effective_start_date=pcon.start_date))
256 and flv1.lookup_type='CONTACT'
257 and flv1.lookup_code=pcr.contact_type
258 and flv2.lookup_type(+)='JP_CONTACT_KANA'
259 and flv2.lookup_code(+)=pcr.contact_type
260 order by 8,7,3,4;
261 BEGIN
262 l_description := NULL;
263
264 for l_rec_dependent in csr_dependent loop
265 if l_first_flag then
266 l_terminator := '';
267 l_first_flag := FALSE;
268 else
269 l_terminator := ',';
270 end if;
271
272 if nvl(p_kanji_flag,'1') = '1' then
273 if l_rec_dependent.ee_last_name <> l_rec_dependent.con_last_name
274 or l_rec_dependent.ee_last_name_kana <> l_rec_dependent.con_last_name_kana then
275 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type || ' ' || l_rec_dependent.con_last_name || ' ' || l_rec_dependent.con_first_name,1,2000);
276 else
277 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type || ' ' || l_rec_dependent.con_first_name,1,2000);
278 end if;
279 else
280 if l_rec_dependent.ee_last_name <> l_rec_dependent.con_last_name
281 or l_rec_dependent.ee_last_name_kana <> l_rec_dependent.con_last_name_kana then
282 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type_kana || ' ' || l_rec_dependent.con_last_name_kana || ' ' || l_rec_dependent.con_first_name_kana,1,2000);
283 else
284 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type_kana || ' ' || l_rec_dependent.con_first_name_kana,1,2000);
285 end if;
286 end if;
287 end loop;
288
289 return l_description;
290 END get_concatenated_dependents;
291
292 -----------------------------------------------------
293 FUNCTION convert2(
294 -----------------------------------------------------
295 str IN VARCHAR2,
296 dest_set IN VARCHAR2) RETURN VARCHAR2
297 IS
298 l_value VARCHAR2(2000);
299 BEGIN
300 l_value := convert(str,dest_set);
301
302 return l_value;
303 END convert2;
304
305 -----------------------------------------------------
306 FUNCTION substrb2(
307 -----------------------------------------------------
308 str IN VARCHAR2,
309 pos IN NUMBER,
310 len IN NUMBER) RETURN VARCHAR2
311 IS
312 l_value VARCHAR2(2000);
313 BEGIN
314 l_value := substrb(str,pos,len);
315
316 return l_value;
317 END substrb2;
318
319 -----------------------------------------------------
320 FUNCTION substr2(
321 -----------------------------------------------------
322 str IN VARCHAR2,
323 pos IN NUMBER,
324 len IN NUMBER) RETURN VARCHAR2
325 IS
326 l_value VARCHAR2(2000);
327 BEGIN
328 l_value := substr(str,pos,len);
329
330 return l_value;
331 END substr2;
332
333 -----------------------------------------------------
334 -- DYNAMIC_SQL --
335 -----------------------------------------------------
336 PROCEDURE dynamic_sql(
337 p_sql_statement IN VARCHAR2,
338 p_bind_variables IN g_tab_bind_variables,
339 p_column_names IN g_tab_column_names)
340 IS
341 i INTEGER;
342 j INTEGER;
343 l_cursor_id INTEGER;
344 l_dummy_integer INTEGER;
345 l_dummy_varchar2 VARCHAR2(255);
346 BEGIN
347 l_cursor_id:=dbms_sql.open_cursor;
348 dbms_sql.parse(l_cursor_id,p_sql_statement,dbms_sql.v7);
349
350 BEGIN
351 i:=0;
352 LOOP
353 i:=i+1;
354 if p_bind_variables(i).datatype='NUMBER' then
355 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,to_number(p_bind_variables(i).value));
356 elsif p_bind_variables(i).datatype='VARCHAR2' then
357 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,p_bind_variables(i).value);
358 elsif p_bind_variables(i).datatype='DATE' then
359 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,fnd_date.canonical_to_date(p_bind_variables(i).value));
360 end if;
361 END LOOP;
362 EXCEPTION
363 WHEN OTHERS THEN
364 i:=i-1;
365 NULL;
366 END;
367
368 BEGIN
369 j:=0;
370 LOOP
371 j:=j+1;
372 l_dummy_varchar2:=p_column_names(j);
373 dbms_sql.define_column(l_cursor_id,j,l_dummy_varchar2,255);
374 END LOOP;
375 EXCEPTION
376 WHEN OTHERS THEN
377 j:=j-1;
378 NULL;
379 END;
380
381 l_dummy_integer:=dbms_sql.execute(l_cursor_id);
382 loop
383 -- dbms_output.put_line(rpad('=',30,'='));
384 l_dummy_integer:=dbms_sql.fetch_rows(l_cursor_id);
385 exit when l_dummy_integer<>1;
386 for i in 1..j loop
387 dbms_sql.column_value(l_cursor_id,i,l_dummy_varchar2);
388 -- dbms_output.put_line(rpad(p_column_names(i),30,' ') || ' = ' || l_dummy_varchar2);
389 end loop;
390 end loop;
391
392 dbms_sql.close_cursor(l_cursor_id);
393 EXCEPTION
394 when others then
395 -- dbms_output.put_line(SQLERRM);
396 dbms_sql.close_cursor(l_cursor_id);
397 END dynamic_sql;
398
399 -----------------------------------------------------
400 -- SET_SPACE_ON_ADDRESSS --
401 -----------------------------------------------------
402 -- This part is out of scope for seed conversion.
403 FUNCTION SET_SPACE_ON_ADDRESS(
404 p_address IN VARCHAR2,
405 p_district_name IN VARCHAR2,
406 p_kana_flag IN NUMBER) RETURN VARCHAR2
407 IS
408 l_text varchar2(80) := p_address;
409 l_district_name varchar2(80) := p_district_name;
410 l_prefecture_name varchar2(80) := replace(l_text,p_district_name);
411
412 BEGIN
413 if l_text is not NULL then
414 if p_kana_flag = 1 then
415 l_district_name := replace(l_district_name,
416 hr_jp_standard_pkg.sjhextochar('B8DEDD'),
417 hr_jp_standard_pkg.sjhextochar('B8DEDD20'));
418 l_district_name := replace(l_district_name,
419 hr_jp_standard_pkg.sjhextochar('20CFB8DEDD20'),
420 hr_jp_standard_pkg.sjhextochar('CFB8DEDD20'));
421 l_district_name := replace(l_district_name,
422 hr_jp_standard_pkg.sjhextochar('20B8DEDD20'),
423 hr_jp_standard_pkg.sjhextochar('20B8DEDD'));
424 --SEIREI SHITEI TOSHI 12
425 l_district_name := replace(l_district_name,
426 hr_jp_standard_pkg.sjhextochar('C1CADEBC'),
427 hr_jp_standard_pkg.sjhextochar('C1CADEBC20'));
428 l_district_name := replace(l_district_name,
429 hr_jp_standard_pkg.sjhextochar('D6BACACFBC'),
430 hr_jp_standard_pkg.sjhextochar('D6BACACFBC20'));
431 l_district_name := replace(l_district_name,
432 hr_jp_standard_pkg.sjhextochar('C5BADED4BC'),
433 hr_jp_standard_pkg.sjhextochar('C5BADED4BC20'));
434 l_district_name := replace(l_district_name,
435 hr_jp_standard_pkg.sjhextochar('BBAFCEDFDBBC'),
436 hr_jp_standard_pkg.sjhextochar('BBAFCEDFDBBC20'));
437 l_district_name := replace(l_district_name,
438 hr_jp_standard_pkg.sjhextochar('BEDDC0DEB2BC'),
439 hr_jp_standard_pkg.sjhextochar('BEDDC0DEB2BC20'));
440 l_district_name := replace(l_district_name,
441 hr_jp_standard_pkg.sjhextochar('BBB2C0CFBC'),
442 hr_jp_standard_pkg.sjhextochar('BBB2C0CFBC20'));
443 l_district_name := replace(l_district_name,
444 hr_jp_standard_pkg.sjhextochar('BCBDDEB5B6BC'),
445 hr_jp_standard_pkg.sjhextochar('BCBDDEB5B6BC20'));
446 l_district_name := replace(l_district_name,
447 hr_jp_standard_pkg.sjhextochar('B7AEB3C4BC'),
448 hr_jp_standard_pkg.sjhextochar('B7AEB3C4BC20'));
449 l_district_name := replace(l_district_name,
450 hr_jp_standard_pkg.sjhextochar('B5B5BBB6BC'),
451 hr_jp_standard_pkg.sjhextochar('B5B5BBB6BC20'));
452 l_district_name := replace(l_district_name,
453 hr_jp_standard_pkg.sjhextochar('BAB3CDDEBC'),
454 hr_jp_standard_pkg.sjhextochar('BAB3CDDEBC20'));
455 l_district_name := replace(l_district_name,
456 hr_jp_standard_pkg.sjhextochar('CBDBBCCFBC'),
457 hr_jp_standard_pkg.sjhextochar('CBDBBCCFBC20'));
458 l_district_name := replace(l_district_name,
459 hr_jp_standard_pkg.sjhextochar('B7C0B7ADB3BCADB3BC'),
460 hr_jp_standard_pkg.sjhextochar('B7C0B7ADB3BCADB3BC20'));
461 l_district_name := replace(l_district_name,
462 hr_jp_standard_pkg.sjhextochar('CCB8B5B6BC'),
463 hr_jp_standard_pkg.sjhextochar('CCB8B5B6BC20'));
464 l_district_name := replace(l_district_name,
465 hr_jp_standard_pkg.sjhextochar('B5B5BCCFB5B5BCCFCFC1'),
466 hr_jp_standard_pkg.sjhextochar('B5B5BCCF20B5B5BCCFCFC1'));
467 --
468 l_district_name := replace(l_district_name,
469 hr_jp_standard_pkg.sjhextochar('C4BCCFC4BCCFD1D7'),
470 hr_jp_standard_pkg.sjhextochar('C4BCCF20C4BCCFD1D7'));
471 l_district_name := replace(l_district_name,
472 hr_jp_standard_pkg.sjhextochar('C6B2BCDECFC6B2BCDECFD1D7'),
473 hr_jp_standard_pkg.sjhextochar('C6B2BCDECF20C6B2BCDECFD1D7'));
474 l_district_name := replace(l_district_name,
475 hr_jp_standard_pkg.sjhextochar('BAB3BDDEBCCFBAB3C2DEBCCFD1D7'),
476 hr_jp_standard_pkg.sjhextochar('BAB3BDDEBCCF20BAB3C2DEBCCFD1D7'));
477 l_district_name := replace(l_district_name,
478 hr_jp_standard_pkg.sjhextochar('D0D4B9BCDECFD0D4B9D1D7'),
479 hr_jp_standard_pkg.sjhextochar('D0D4B9BCDECF20D0D4B9D1D7'));
480 l_district_name := replace(l_district_name,
481 hr_jp_standard_pkg.sjhextochar('D0B8D7BCDECFD0B8D7BCDECFD1D7'),
482 hr_jp_standard_pkg.sjhextochar('D0B8D7BCDECF20D0B8D7BCDECFD1D7'));
483 l_district_name := replace(l_district_name,
484 hr_jp_standard_pkg.sjhextochar('CAC1BCDED6B3BCDECFCAC1BCDED6B3CFC1'),
485 hr_jp_standard_pkg.sjhextochar('CAC1BCDED6B3BCDECF20CAC1BCDED6B3CFC1'));
486 l_district_name := replace(l_district_name,
487 hr_jp_standard_pkg.sjhextochar('B1B5B6DEBCCFB1B5B6DEBCCFD1D7'),
488 hr_jp_standard_pkg.sjhextochar('B1B5B6DEBCCF20B1B5B6DEBCCFD1D7'));
489
490 l_district_name := replace(l_district_name,
491 hr_jp_standard_pkg.sjhextochar('B5B6DEBBDCD7BCAEC4B3B5B6DEBBDCD7D1D7'),
492 hr_jp_standard_pkg.sjhextochar('B5B6DEBBDCD7BCAEC4B320B5B6DEBBDCD7D1D7'));
493 l_text := l_prefecture_name||' '||l_district_name;
494 else
495 l_text := replace(l_text,
496 hr_jp_standard_pkg.sjhextochar('8C53'),
497 hr_jp_standard_pkg.sjhextochar('8C538140'));
498 l_text := replace(l_text,
499 hr_jp_standard_pkg.sjhextochar('8E73'),
500 hr_jp_standard_pkg.sjhextochar('8E738140'));
501 l_text := replace(l_text,
502 hr_jp_standard_pkg.sjhextochar('8CA7'),
503 hr_jp_standard_pkg.sjhextochar('8CA78140'));
504 --
505 l_text := replace(l_text,
506 hr_jp_standard_pkg.sjhextochar('966B8A4393B9'),
507 hr_jp_standard_pkg.sjhextochar('966B8A4393B98140'));
508 l_text := replace(l_text,
509 hr_jp_standard_pkg.sjhextochar('938C8B9E9373'),
510 hr_jp_standard_pkg.sjhextochar('938C8B9E93738140'));
511 l_text := replace(l_text,
512 hr_jp_standard_pkg.sjhextochar('8B9E9373957B'),
513 hr_jp_standard_pkg.sjhextochar('8B9E9373957B8140'));
514 l_text := replace(l_text,
515 hr_jp_standard_pkg.sjhextochar('91E58DE3957B'),
516 hr_jp_standard_pkg.sjhextochar('91E58DE3957B8140'));
517 --
518 l_text := replace(l_text,
519 hr_jp_standard_pkg.sjhextochar('8C5381408C538140'),
520 hr_jp_standard_pkg.sjhextochar('8C5381408C53'));
521 l_text := replace(l_text,
522 hr_jp_standard_pkg.sjhextochar('8C5381408E738140'),
523 hr_jp_standard_pkg.sjhextochar('8C5381408E73'));
524
525 --case of GIFU or FUKUSIMA
526 if substrb(l_text,1,6) = hr_jp_standard_pkg.sjhextochar('8AF2958C8CA7')
527 or substrb(l_text,1,6) = hr_jp_standard_pkg.sjhextochar('959F93878CA7') then
528 l_text := replace(l_text,
529 hr_jp_standard_pkg.sjhextochar('8CA781408C538140'),
530 hr_jp_standard_pkg.sjhextochar('8CA781408C53'));
531 else
532 l_text := replace(l_text,
533 hr_jp_standard_pkg.sjhextochar('8CA781408C538140'),
534 hr_jp_standard_pkg.sjhextochar('8CA78C538140'));
535 end if;
536 l_text := replace(l_text,
537 hr_jp_standard_pkg.sjhextochar('8CA781408E738140'),
538 hr_jp_standard_pkg.sjhextochar('8CA781408E73'));
539 l_text := replace(l_text,
540 hr_jp_standard_pkg.sjhextochar('8E7381408C538140'),
541 hr_jp_standard_pkg.sjhextochar('8E738C538140'));
542 l_text := replace(l_text,
543 hr_jp_standard_pkg.sjhextochar('94AA93FA8E7381408FEA8E73'),
544 hr_jp_standard_pkg.sjhextochar('94AA93FA8E738FEA8E73'));
545 l_text := replace(l_text,
546 hr_jp_standard_pkg.sjhextochar('91E598618C5381408E528E73'),
547 hr_jp_standard_pkg.sjhextochar('91E598618C538E528E73'));
548 l_text := replace(l_text,
549 hr_jp_standard_pkg.sjhextochar('938C8E738140978892AC'),
550 hr_jp_standard_pkg.sjhextochar('938C8E73978892AC'));
551 --
552 l_text := replace(l_text,
553 hr_jp_standard_pkg.sjhextochar('91E5938791E5938792AC'),
554 hr_jp_standard_pkg.sjhextochar('91E59387814091E5938792AC'));
555 l_text := replace(l_text,
556 hr_jp_standard_pkg.sjhextochar('979893879798938791BA'),
557 hr_jp_standard_pkg.sjhextochar('9798938781409798938791BA'));
558 l_text := replace(l_text,
559 hr_jp_standard_pkg.sjhextochar('905693879056938791BA'),
560 hr_jp_standard_pkg.sjhextochar('9056938781409056938791BA'));
561 l_text := replace(l_text,
562 hr_jp_standard_pkg.sjhextochar('905F92C39387905F92C3938791BA'),
563 hr_jp_standard_pkg.sjhextochar('905F92C393878140905F92C3938791BA'));
564 l_text := replace(l_text,
565 hr_jp_standard_pkg.sjhextochar('8E4F91EE93878E4F91EE91BA'),
566 hr_jp_standard_pkg.sjhextochar('8E4F91EE938781408E4F91EE91BA'));
567 l_text := replace(l_text,
568 hr_jp_standard_pkg.sjhextochar('8CE491A093878CE491A0938791BA'),
569 hr_jp_standard_pkg.sjhextochar('8CE491A0938781408CE491A0938791BA'));
570 l_text := replace(l_text,
571 hr_jp_standard_pkg.sjhextochar('94AA8FE4938794AA8FE492AC'),
572 hr_jp_standard_pkg.sjhextochar('94AA8FE49387814094AA8FE492AC'));
573 l_text := replace(l_text,
574 hr_jp_standard_pkg.sjhextochar('90C28350938790C28350938791BA'),
575 hr_jp_standard_pkg.sjhextochar('90C283509387814090C28350938791BA'));
576 l_text := replace(l_text,
577 hr_jp_standard_pkg.sjhextochar('8FAC8A7D8CB48F9493878FAC8A7D8CB491BA'),
578 hr_jp_standard_pkg.sjhextochar('8FAC8A7D8CB48F94938781408FAC8A7D8CB491BA'));
579 end if;
580 end if;
581 return l_text;
582 END set_space_on_address;
583
584 -----------------------------------------------------
585 -- GET_MAX_VALUE --
586 -----------------------------------------------------
587 FUNCTION GET_MAX_VALUE(
588 p_user_table_name IN VARCHAR2,
589 p_udt_column_name IN VARCHAR2,
590 p_effective_date IN DATE ) RETURN NUMBER
591 IS
592 l_value number := null;
593 CURSOR cur_max_value IS
594 select max(to_number(value))
595 from pay_user_tables put,
596 pay_user_columns puc,
597 pay_user_column_instances_f puci
598 where put.user_table_name = p_user_table_name
599 and puc.user_table_id = put.user_table_id
600 and puc.user_column_name = p_udt_column_name
601 and puci.user_column_id = puc.user_column_id
602 and p_effective_date
603 between puci.effective_start_date and puci.effective_end_date;
604 BEGIN
605 open cur_max_value;
606 fetch cur_max_value into l_value;
607 close cur_max_value;
608
609 return l_value;
610 END get_max_value;
611
612 -----------------------------------------------------
613 -- GET_MIN_VALUE --
614 -----------------------------------------------------
615 FUNCTION GET_MIN_VALUE(
616 p_user_table_name IN VARCHAR2,
617 p_udt_column_name IN VARCHAR2,
618 p_effective_date IN DATE ) RETURN NUMBER
619 IS
620 l_value number := null;
621 CURSOR cur_min_value IS
622 select min(to_number(value))
623 from pay_user_tables put,
624 pay_user_columns puc,
625 pay_user_column_instances_f puci
626 where put.user_table_name = p_user_table_name
627 and puc.user_table_id = put.user_table_id
628 and puc.user_column_name = p_udt_column_name
629 and puci.user_column_id = puc.user_column_id
630 and p_effective_date
631 between puci.effective_start_date and puci.effective_end_date;
632 BEGIN
633 open cur_min_value;
634 fetch cur_min_value into l_value;
635 close cur_min_value;
636
637 return l_value;
638 END get_min_value;
639
640 -----------------------------------------------------
641 -- SJTOJIS --
642 -----------------------------------------------------
643 FUNCTION sjtojis(p_src IN VARCHAR2) RETURN VARCHAR2
644 IS
645 l_jis VARCHAR2(2000) := '';
646 l_src VARCHAR2(4);
647 l_b1 VARCHAR2(2);
648 l_b2 VARCHAR2(2);
649 l_b3 VARCHAR2(2);
650 l_b4 VARCHAR2(2);
651 l_kanji NUMBER := 0;
652 l_ank NUMBER := 0;
653
654 BEGIN
655 if length(p_src) is null then
656 return NULL;
657 end if;
658
659 for i in 1.. length(p_src) loop
660 l_src := hr_jp_standard_pkg.chartohex(substr(p_src,i,1),'JA16SJIS');
661 if length(l_src) = 2 then --1byte character
662 if l_kanji = 1 and l_ank = 0 then
663 l_ank := 1;
664 l_kanji := 0;
665 -- escape sequence KO (kanji out, ANK shift code "ESC(H")
666 l_jis := l_jis || '1B2848';
667 end if;
668 l_jis := l_jis || l_src;
669 else --2byte caracter
670 l_b1 := substr(l_src,1,1);
671 l_b2 := substr(l_src,2,1);
672 l_b3 := substr(l_src,3,1);
673 l_b4 := substr(l_src,4,1);
674
675 -- hex A..F -> 10..15
676 if to_number(hr_jp_standard_pkg.chartohex(l_b1,'JA16SJIS')) >= 41 then
677 l_b1 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b1,'JA16SJIS'))) - 31;
678 end if;
679 if to_number(hr_jp_standard_pkg.chartohex(l_b2,'JA16SJIS')) >= 41 then
680 l_b2 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b2,'JA16SJIS'))) - 31;
681 end if;
682 if to_number(hr_jp_standard_pkg.chartohex(l_b3,'JA16SJIS')) >= 41 then
683 l_b3 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b3,'JA16SJIS'))) - 31;
684 end if;
685 if to_number(hr_jp_standard_pkg.chartohex(l_b4,'JA16SJIS')) >= 41 then
686 l_b4 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b4,'JA16SJIS'))) - 31;
687 end if;
688
689 -- if 1byte >= 0xE0 then 1byte := 1byte - 0x40;
690 if to_number(l_b1) >= 14 then
691 l_b1 := to_char(to_number(l_b1) - 4);
692 end if;
693 -- if 2byte >= 0x80 then 2byte := 2byte - 0x01;
694 if to_number(l_b3) >= 8 then
695 if to_number(l_b4) < 1 then
696 l_b3 := to_char(to_number(l_b3) - 1);
697 l_b4 := '15';
698 else
699 l_b4 := to_char(to_number(l_b4) - 1);
700 end if;
701 end if;
702 -- if 2byte >= 0x9E then 1byte := (1byte - 0x70) * 2, 2byte := 2byte - 0x7D;
703 -- else 1byte := ((1byte - 0x70) * 2) - 1, 2byte := 2byte - 0x1F;
704 if to_number(l_b3 || lpad(l_b4,2,'0')) >= 914 then
705 l_b1 := to_char((to_number(l_b1) - 7) * 2);
706 l_b2 := to_char(to_number(l_b2) * 2);
707 if to_number(l_b2) > 15 then
708 l_b1 := to_char(to_number(l_b1) + 1);
709 l_b2 := to_char(to_number(l_b2) - 16);
710 end if;
711 if to_number(l_b4) < 13 then
712 l_b3 := to_char(to_number(l_b3) - 8);
713 l_b4 := to_char(to_number(l_b4) + 3);
714 else
715 l_b3 := to_char(to_number(l_b3) - 7);
716 l_b4 := to_char(to_number(l_b4) - 13);
717 end if;
718 else
719 if to_number(l_b2) < 1 then
720 l_b1 := to_char(((to_number(l_b1) - 7) * 2) - 1);
721 l_b2 := '15';
722 else
723 l_b1 := to_char((to_number(l_b1) - 7) * 2);
724 l_b2 := to_char((to_number(l_b2) * 2) - 1);
725 if to_number(l_b2) > 15 then
726 l_b1 := to_char(to_number(l_b1) + 1);
727 l_b2 := to_char(to_number(l_b2) - 16);
728 end if;
729 end if;
730 if to_number(l_b4) < 15 then
731 l_b3 := to_char(to_number(l_b3) - 2);
732 l_b4 := to_char(to_number(l_b4) + 1);
733 else
734 l_b3 := to_char(to_number(l_b3) - 1);
735 l_b4 := to_char(to_number(l_b4) - 15);
736 end if;
737 end if;
738
739 if l_kanji = 0 then
740 l_kanji := 1;
741 l_ank := 0;
742 -- escape sequence KI (kanji in, KANJI shift code "ESC$@")
743 l_jis := l_jis || '1B2440';
744 end if;
745
746 if to_number(l_b1) > 9 then
747 l_b1 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b1)+31));
748 end if;
749 if to_number(l_b2) > 9 then
750 l_b2 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b2)+31));
751 end if;
752 if to_number(l_b3) > 9 then
753 l_b3 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b3)+31));
754 end if;
755 if to_number(l_b4) > 9 then
756 l_b4 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b4)+31));
757 end if;
758
759 l_jis := l_jis || l_b1 || l_b2 || l_b3 || l_b4;
760 end if;
761 end loop;
762
763 if l_kanji = 1 and l_ank = 0 then
764 -- escape sequence KO (kanji out, ANK shift code "ESC(H")
765 l_jis := l_jis || '1B2848';
766 end if;
767
768 return hr_jp_standard_pkg.sjhextochar(l_jis);
769 -- dbms_output.put_line(l_jis);
770 END sjtojis;
771 -----------------------------------------------------
772 -- ELIGIBLE_FOR_SUBMISSION --
773 -----------------------------------------------------
774 FUNCTION eligible_for_submission (
775 p_year IN NUMBER,
776 p_itax_yea_category IN VARCHAR2,
777 p_gross_taxable_amt IN NUMBER,
778 p_taxable_amt IN NUMBER,
779 p_prev_swot_taxable_amt IN NUMBER,
780 p_executive_flag IN VARCHAR2,
781 p_itax_category IN VARCHAR2
782 ) RETURN VARCHAR2
783 IS
784 l_eligible_flag VARCHAR2(1) := 'N';
785 l_taxable_amt_total NUMBER;
786 l_prev_swot_taxable_amt NUMBER := 0;
787 BEGIN
788 if p_year between 0 and 4712 then
789 -- Total salary doesn't depend on YEA status.
790 if p_itax_yea_category is not NULL then
791 l_taxable_amt_total := p_gross_taxable_amt;
792 else
793 l_taxable_amt_total := p_taxable_amt
794 + l_prev_swot_taxable_amt;
795 end if;
796
797 -- In case YEA is processed.
798 if p_itax_yea_category = '0' then
799 if p_executive_flag = 'Y' then
800 if l_taxable_amt_total > 1500000 then
801 l_eligible_flag := 'Y';
802 end if;
803 else
804 if l_taxable_amt_total > 5000000 then
805 l_eligible_flag := 'Y';
806 end if;
807 end if;
808 else
809 if p_itax_category in ('M_KOU', 'D_KOU') then
810 if p_executive_flag = 'Y' then
811 if l_taxable_amt_total > 500000 then
812 l_eligible_flag := 'Y';
813 end if;
814 else
815 if l_taxable_amt_total > 2500000 then
816 l_eligible_flag := 'Y';
817 end if;
818 end if;
819 else
820 if l_taxable_amt_total > 500000 then
821 l_eligible_flag := 'Y';
822 end if;
823 end if;
824 end if;
825 end if;
826 return l_eligible_flag;
827 END eligible_for_submission;
828 --
829 -----------------------------------------------------
830 -- GET_PREV_SWOT_INFO --
831 -----------------------------------------------------
832 FUNCTION get_prev_swot_info (
833 p_business_group_id in NUMBER,
834 p_assignment_id in NUMBER,
835 p_year in NUMBER,
836 p_itax_organization_id in NUMBER,
837 p_swot_iv_id in NUMBER,
838 p_action_sequence in NUMBER,
839 p_kanji_flag in VARCHAR2,
840 p_media_type in VARCHAR2) RETURN VARCHAR2
841 IS
842 cursor csr_prev_swot_1 is
843 select v1.business_group_id,
844 v1.itax_organization_id,
845 v1.effective_date,
846 v1.date_earned,
847 v1.assignment_id,
848 v1.action_sequence
849 from pay_jp_pre_itax_v1 v1
850 where v1.business_group_id = p_business_group_id
851 and to_char(v1.effective_date, 'YYYY') = p_year
852 and v1.assignment_id = p_assignment_id
853 --
854 and v1.itax_organization_id <> p_itax_organization_id
855 and v1.action_sequence < p_action_sequence
856 order by v1.date_earned desc;
857 --
858 cursor csr_prev_swot_2(cp_itax_organization_id NUMBER, cp_action_sequence NUMBER) is
859 select /* Removed the hint as per Bug# 4767108 */
860 nvl(sum(decode(pai.action_information13, 'TERM',
861 NULL, decode(pai.action_information21, cp_itax_organization_id,
862 pai.action_information2 + pai.action_information3, NULL ))),0) PREV_SWOT_TAXABLE_AMT,
863 nvl(sum(decode(pai.action_information13, 'TERM',
864 NULL, decode(pai.action_information21, cp_itax_organization_id,
865 pai.action_information24 + pai.action_information25, NULL))),0) PREV_SWOT_ITAX,
866 nvl(sum(decode(pai.action_information13, 'TERM',
867 NULL, decode(pai.action_information21, cp_itax_organization_id,
868 pai.action_information6 + pai.action_information9 + pai.action_information12 + pai.action_information20 + pai.action_information14, NULL))),0) PREV_SWOT_SI_PREM,
869 nvl(sum(decode(pai.action_information13, 'TERM',
870 NULL, decode(pai.action_information21, cp_itax_organization_id, pai.action_information14, NULL))), 0) PREV_SWOT_MUTUAL_AID
871 from pay_assignment_actions paa,
872 pay_payroll_actions ppa,
873 pay_action_information pai,
874 per_all_assignments_f pa
875 where paa.assignment_id = p_assignment_id
876 /* Below conditions have already been taken care in Pre-Tax Archiver
877 process. So they are redundant here and removed.
878 for Bug# 5033800 */
879 -- and paa.action_status = 'C'
880 -- and ppa.action_type in ('R', 'Q', 'B', 'I')
881 /* Below conditions were removed, as they are redundant ones.
882 for Bug# 5033800 */
883 -- and pai.action_context_type = 'AAP'
884 -- and pai.assignment_id = pass.assignment_id
885 and ppa.payroll_action_id = paa.payroll_action_id
886 and to_char(ppa.effective_date, 'YYYY') = p_year
887 and pai.action_information_category = 'JP_PRE_TAX_1'
888 and pai.action_information1 = paa.assignment_action_id
889 and ((pai.action_information13 in ('SALARY', 'BONUS', 'SP_BONUS', 'YEA', 'RE_YEA')
890 and paa.action_sequence <= cp_action_sequence)
891 or
892 (pai.action_information13 = 'TERM'))
893 and pai.action_information22 in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU', 'D_HEI')
894 and pa.assignment_id = paa.assignment_id
895 and ppa.effective_date between pa.effective_start_date and pa.effective_end_date
896 --
897 and not exists(
898 select NULL /* Removed the hint as per Bug# 5033800 */
899 from pay_action_interlocks pai2,
900 pay_assignment_actions paa2,
901 pay_payroll_actions ppa2
902 where pai2.locked_action_id = paa.assignment_action_id
903 and paa2.assignment_action_id = pai2.locking_action_id
904 and ppa2.payroll_action_id = paa2.payroll_action_id
905 and ppa2.action_type = 'V');
906 --
907 -- /* Join peev before pee to avoid merge join cartesian */
908 cursor csr_prev_swot(cp_date_earned DATE, cp_itax_organization_id NUMBER) is
909 select /* Removed the hint as per Bug# 4767108 */
910 decode(p_kanji_flag,
911 '1',hoi.org_information1,hoi.org_information2) EMPLOYER_NAME,
912 pay_jp_report_pkg.substrb2(
913 decode(p_kanji_flag,
914 '1',hoi.org_information6||hoi.org_information7||hoi.org_information8,
915 hoi.org_information9||hoi.org_information10||hoi.org_information11),1,255) EMPLOYER_ADDRESS,
916 peev.effective_end_date PREV_SWOT_TERM_DATE
917 from hr_organization_information hoi,
918 pay_element_entry_values_f peev,
919 pay_element_entries_f pee
920 where hoi.organization_id(+) = cp_itax_organization_id
921 and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
922 -- Previous SWOT term date
923 and cp_date_earned between peev.effective_start_date and peev.effective_end_date
924 and peev.input_value_id = p_swot_iv_id
925 and peev.screen_entry_value = hoi.organization_id
926 and pee.element_entry_id = peev.element_entry_id
927 and pee.assignment_id = p_assignment_id
928 and pee.effective_start_date = peev.effective_start_date
929 and pee.effective_end_date = peev.effective_end_date;
930
931 l_prev_swot_rec csr_prev_swot%ROWTYPE;
932
933 l_description varchar2(2000);
934 l_prev_term_era_code NUMBER;
935 l_prev_term_year NUMBER;
936 l_prev_term_month NUMBER;
937 l_prev_term_day NUMBER;
938 l_prev_taxable_amt VARCHAR2(255);
939 l_prev_itax VARCHAR2(255);
940 l_prev_si_prem VARCHAR2(255);
941 l_prev_mutual_aid VARCHAR2(255);
942 l_prev_add VARCHAR2(255);
943 l_prev_name VARCHAR2(255);
944 l_prev_term VARCHAR2(255);
945 l_prev_add_id_for_file VARCHAR2(10) := NULL;
946 l_prev_name_id_for_file VARCHAR2(10) := NULL;
947 l_prev_term_id_for_file VARCHAR2(10) := NULL;
948
949 BEGIN
950
951 l_description := NULL;
952
953 if nvl(p_media_type,'NULL') <> 'NULL' then
954 l_prev_add_id_for_file := 'P_ADDRESS';
955 l_prev_name_id_for_file := 'P_NAME';
956 l_prev_term_id_for_file := 'P_TERM';
957 end if;
958
959 for l_prev_swot_1_rec in csr_prev_swot_1 loop
960 for l_prev_swot_2_rec in csr_prev_swot_2(l_prev_swot_1_rec.itax_organization_id, l_prev_swot_1_rec.action_sequence) loop
961 for l_prev_swot_rec in csr_prev_swot(l_prev_swot_1_rec.date_earned, l_prev_swot_1_rec.itax_organization_id) loop
962
963 l_prev_taxable_amt := NULL;
964 l_prev_itax := NULL;
965 l_prev_si_prem := NULL;
966 l_prev_mutual_aid := NULL;
967 l_prev_add := NULL;
968 l_prev_name := NULL;
969 l_prev_term := NULL;
970
971 pay_jp_report_pkg.to_era(l_prev_swot_rec.prev_swot_term_date,
972 l_prev_term_era_code,
973 l_prev_term_year,
974 l_prev_term_month,
975 l_prev_term_day);
976 l_prev_term_year := l_prev_term_year - trunc(l_prev_term_year,-2);
977
978 if not (l_prev_swot_2_rec.prev_swot_taxable_amt = 0
979 and l_prev_swot_2_rec.prev_swot_itax = 0
980 and l_prev_swot_2_rec.prev_swot_si_prem = 0) then
981
982 if P_KANJI_FLAG = '1' then -- Kanji
983 if (l_description is NULL) then
984 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
985 else
986 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
987 end if;
988 l_prev_taxable_amt := fnd_message.get_string('PAY','PAY_JP_SALARY')
989 || to_char(l_prev_swot_2_rec.prev_swot_taxable_amt)
990 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
991 l_prev_si_prem := ',' ||fnd_message.get_string('PAY','PAY_JP_TRANS_SI')
992 || to_char(l_prev_swot_2_rec.prev_swot_si_prem)
993 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
994 if (l_prev_swot_2_rec.prev_swot_mutual_aid is not NULL) then
995 l_prev_mutual_aid := '(' ||fnd_message.get_string('PAY','PAY_JP_WITHIN')
996 || to_char(l_prev_swot_2_rec.prev_swot_mutual_aid)
997 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX') ||')';
998 end if;
999 l_prev_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX')
1000 || to_char(l_prev_swot_2_rec.prev_swot_itax)
1001 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1002 if nvl(l_prev_swot_rec.employer_address,' ') <> ' ' then
1003 l_prev_add := ',' || l_prev_swot_rec.employer_address;
1004 end if;
1005 if nvl(l_prev_swot_rec.employer_name,' ') <> ' ' then
1006 l_prev_name := ',' || l_prev_swot_rec.employer_name;
1007 end if;
1008 if (l_prev_swot_rec.prev_swot_term_date is not NULL) then
1009 l_prev_term := ','
1010 || lpad(to_char(l_prev_term_year),2,'0') || fnd_message.get_string('PER','HR_JP_YY')
1011 || lpad(to_char(l_prev_term_month),2,'0') || fnd_message.get_string('PER','HR_JP_MM')
1012 || lpad(to_char(l_prev_term_day),2,'0') || fnd_message.get_string('PER','HR_JP_DD')
1013 || fnd_message.get_string('PAY','PAY_JP_TERM');
1014 end if;
1015 else -- Kana
1016 if (l_description is NULL) then
1017 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1018 else
1019 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1020 end if;
1021 l_prev_taxable_amt := fnd_message.get_string('PAY','PAY_JP_TRANS_SALARY_KANA')
1022 || to_char(l_prev_swot_2_rec.prev_swot_taxable_amt)
1023 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1024 l_prev_si_prem := ',' || fnd_message.get_string('PAY','PAY_JP_TRANS_SI_KANA')
1025 || to_char(l_prev_swot_2_rec.prev_swot_si_prem)
1026 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1027 if (l_prev_swot_2_rec.prev_swot_mutual_aid is not NULL) then
1028 l_prev_mutual_aid := '(' ||fnd_message.get_string('PAY','PAY_JP_WITHIN_KANA')
1029 || to_char(l_prev_swot_2_rec.prev_swot_mutual_aid)
1030 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA') || ')';
1031 end if;
1032 l_prev_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX_KANA')
1033 || to_char(l_prev_swot_2_rec.prev_swot_itax)
1034 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1035 if nvl(l_prev_swot_rec.employer_address,' ') <> ' ' then
1036 l_prev_add := ',' || l_prev_swot_rec.employer_address;
1037 end if;
1038 if nvl(l_prev_swot_rec.employer_name,' ') <> ' ' then
1039 l_prev_name := ',' || l_prev_swot_rec.employer_name;
1040 end if;
1041 if (l_prev_swot_rec.prev_swot_term_date is not NULL) then
1042 l_prev_term := ','
1043 || lpad(to_char(l_prev_term_year),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_YY_KANA')
1044 || lpad(to_char(l_prev_term_month),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_MM_KANA')
1045 || lpad(to_char(l_prev_term_day),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_DD_KANA')
1046 || fnd_message.get_string('PAY','PAY_JP_TRANS_TERM_KANA');
1047 end if;
1048 end if;
1049 l_description := pay_jp_report_pkg.substrb2(l_description
1050 || l_prev_taxable_amt || l_prev_si_prem
1051 || l_prev_mutual_aid || l_prev_itax
1052 || l_prev_add_id_for_file || l_prev_add
1053 || l_prev_name_id_for_file || l_prev_name
1054 || l_prev_term_id_for_file || l_prev_term,1,2000);
1055 end if;
1056
1057 end loop;
1058 end loop;
1059 end loop;
1060 return l_description;
1061
1062 END get_prev_swot_info;
1063
1064 -----------------------------------------------------
1065 -- GET_PJOB_INFO --
1066 -----------------------------------------------------
1067 FUNCTION get_pjob_info (
1068 p_assignment_id in NUMBER,
1069 p_effective_date in DATE,
1070 p_business_group_id in NUMBER,
1071 p_pjob_ele_type_id in NUMBER,
1072 p_taxable_amt_iv_id in NUMBER,
1073 p_si_prem_iv_id in NUMBER,
1074 p_mutual_aid_iv_id in NUMBER,
1075 p_itax_iv_id in NUMBER,
1076 p_term_date_iv_id in NUMBER,
1077 p_addr_iv_id in NUMBER,
1078 p_employer_name_iv_id in NUMBER,
1079 p_kanji_flag in VARCHAR2,
1080 p_media_type in VARCHAR2) RETURN VARCHAR2
1081 IS
1082
1083 cursor csr_get_entry_values is
1084 select /*+ ORDERED
1085 NO_MERGE(entry_type_v)
1086 INDEX(TAXABLE_AMT PAY_ELEMENT_ENTRY_VALUES_F_N50)
1087 INDEX(ITAX PAY_ELEMENT_ENTRY_VALUES_F_N50)
1088 INDEX(SI_PREM PAY_ELEMENT_ENTRY_VALUES_F_N50)
1089 INDEX(MUTUAL_AID PAY_ELEMENT_ENTRY_VALUES_F_N50)
1090 INDEX(TERM_DATE PAY_ELEMENT_ENTRY_VALUES_F_N50)
1091 INDEX(ADDR PAY_ELEMENT_ENTRY_VALUES_F_N50)
1092 INDEX(EMPLOYER_NAME PAY_ELEMENT_ENTRY_VALUES_F_N50) */
1093 nvl(taxable_amt.screen_entry_value,0) PJOB_TAXABLE_AMT,
1094 nvl(itax.screen_entry_value,0) PJOB_ITAX,
1095 nvl(si_prem.screen_entry_value,0) PJOB_SI_PREM,
1096 nvl(mutual_aid.screen_entry_value,0) PJOB_MUTUAL_AID,
1097 fnd_date.canonical_to_date(term_date.screen_entry_value) PJOB_TERM_DATE,
1098 addr.screen_entry_value PJOB_ADDR,
1099 employer_name.screen_entry_value PJOB_EMPLOYER_NAME
1100 from (select /*+ ORDERED
1101 INDEX(PETF PAY_ELEMENT_TYPES_F_PK)
1102 INDEX(PELF PAY_ELEMENT_LINKS_F_N7)
1103 INDEX(PEEF PAY_ELEMENT_ENTRIES_F_N51) */
1104 peef.element_entry_id
1105 from pay_element_types_f petf,
1106 pay_element_links_f pelf,
1107 pay_element_entries_f peef
1108 where petf.element_type_id = p_pjob_ele_type_id
1109 and pelf.element_type_id = petf.element_type_id
1110 and pelf.business_group_id +0 = p_business_group_id
1111 and peef.element_link_id = pelf.element_link_id
1112 and peef.assignment_id = p_assignment_id) entry_type_v,
1113 pay_element_entry_values_f taxable_amt,
1114 pay_element_entry_values_f itax,
1115 pay_element_entry_values_f si_prem,
1116 pay_element_entry_values_f mutual_aid,
1117 pay_element_entry_values_f term_date,
1118 pay_element_entry_values_f addr,
1119 pay_element_entry_values_f employer_name
1120 where taxable_amt.element_entry_id = entry_type_v.element_entry_id
1121 and taxable_amt.input_value_id = p_taxable_amt_iv_id
1122 and p_effective_date
1123 between taxable_amt.effective_start_date and taxable_amt.effective_end_date
1124 and itax.element_entry_id = entry_type_v.element_entry_id
1125 and itax.input_value_id = p_itax_iv_id
1126 and p_effective_date
1127 between itax.effective_start_date and itax.effective_end_date
1128 and si_prem.element_entry_id = entry_type_v.element_entry_id
1129 and si_prem.input_value_id = p_si_prem_iv_id
1130 and p_effective_date
1131 between si_prem.effective_start_date and si_prem.effective_end_date
1132 and mutual_aid.element_entry_id = entry_type_v.element_entry_id
1133 and mutual_aid.input_value_id = p_mutual_aid_iv_id
1134 and p_effective_date
1135 between mutual_aid.effective_start_date and mutual_aid.effective_end_date
1136 and term_date.element_entry_id = entry_type_v.element_entry_id
1137 and term_date.input_value_id = p_term_date_iv_id
1138 and p_effective_date
1139 between term_date.effective_start_date and term_date.effective_end_date
1140 and addr.element_entry_id = entry_type_v.element_entry_id
1141 and addr.input_value_id = p_addr_iv_id
1142 and p_effective_date
1143 between addr.effective_start_date and addr.effective_end_date
1144 and employer_name.element_entry_id = entry_type_v.element_entry_id
1145 and employer_name.input_value_id = p_employer_name_iv_id
1146 and p_effective_date
1147 between employer_name.effective_start_date and employer_name.effective_end_date
1148 order by pjob_term_date desc;
1149
1150 l_get_entry_values_rec csr_get_entry_values%ROWTYPE;
1151
1152 l_description VARCHAR2(2000);
1153 l_pjob_taxable_amt VARCHAR2(255);
1154 l_pjob_itax VARCHAR2(255);
1155 l_pjob_si_prem VARCHAR2(255);
1156 l_pjob_mutual_aid VARCHAR2(255);
1157 l_pjob_term_date VARCHAR2(255);
1158 l_pjob_addr VARCHAR2(255);
1159 l_pjob_employer_name VARCHAR2(255);
1160
1161 l_year NUMBER;
1162 l_month NUMBER;
1163 l_day NUMBER;
1164 l_era_code NUMBER;
1165
1166 l_prev_add_id_for_file VARCHAR2(10) := NULL;
1167 l_prev_name_id_for_file VARCHAR2(10) := NULL;
1168 l_prev_term_id_for_file VARCHAR2(10) := NULL;
1169
1170 BEGIN
1171 l_description := NULL;
1172
1173 if nvl(p_media_type,'NULL') <> 'NULL' then
1174 l_prev_add_id_for_file := 'P_ADDRESS';
1175 l_prev_name_id_for_file := 'P_NAME';
1176 l_prev_term_id_for_file := 'P_TERM';
1177 end if;
1178
1179 for l_get_entry_values_rec in csr_get_entry_values loop
1180 l_pjob_taxable_amt := NULL;
1181 l_pjob_si_prem := NULL;
1182 l_pjob_mutual_aid := NULL;
1183 l_pjob_itax := NULL;
1184 l_pjob_term_date := NULL;
1185 l_pjob_addr := NULL;
1186 l_pjob_employer_name := NULL;
1187
1188 pay_jp_report_pkg.to_era(l_get_entry_values_rec.pjob_term_date,
1189 l_era_code,
1190 l_year,
1191 l_month,
1192 l_day);
1193 l_year := l_year - trunc(l_year,-2);
1194
1195 if not (l_get_entry_values_rec.pjob_taxable_amt = 0
1196 and l_get_entry_values_rec.pjob_si_prem = 0
1197 and l_get_entry_values_rec.pjob_itax = 0) then
1198 if p_kanji_flag = '1' then
1199 if (l_description is NULL) then
1200 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
1201 else
1202 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
1203 end if;
1204 l_pjob_taxable_amt := fnd_message.get_string('PAY','PAY_JP_SALARY')
1205 || l_get_entry_values_rec.pjob_taxable_amt
1206 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1207 l_pjob_si_prem := ','||fnd_message.get_string('PAY','PAY_JP_TRANS_SI')
1208 || l_get_entry_values_rec.pjob_si_prem
1209 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1210 if (l_get_entry_values_rec.pjob_mutual_aid is not NULL) then
1211 l_pjob_mutual_aid := '('||fnd_message.get_string('PAY','PAY_JP_WITHIN')
1212 || l_get_entry_values_rec.pjob_mutual_aid
1213 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX')||')';
1214 end if;
1215 l_pjob_itax := ','||fnd_message.get_string('PAY','PAY_JP_TAX')
1216 || l_get_entry_values_rec.pjob_itax
1217 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1218 if nvl(l_get_entry_values_rec.pjob_addr, ' ') <> ' ' then
1219 l_pjob_addr := ',' || l_get_entry_values_rec.pjob_addr;
1220 end if;
1221 if nvl(l_get_entry_values_rec.pjob_employer_name, ' ') <> ' ' then
1222 l_pjob_employer_name := ',' || l_get_entry_values_rec.pjob_employer_name;
1223 end if;
1224 if (l_get_entry_values_rec.pjob_term_date is not NULL) then
1225 l_pjob_term_date := ','
1226 || lpad(to_char(l_year),2,'0') || fnd_message.get_string('PER','HR_JP_YY')
1227 || lpad(to_char(l_month),2,'0') || fnd_message.get_string('PER','HR_JP_MM')
1228 || lpad(to_char(l_day),2,'0') || fnd_message.get_string('PER','HR_JP_DD')
1229 || fnd_message.get_string('PAY','PAY_JP_TERM');
1230 end if;
1231 else
1232 if (l_description is NULL) then
1233 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1234 else
1235 l_description := l_description || ',' || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1236 end if;
1237 l_pjob_taxable_amt := fnd_message.get_string('PAY','PAY_JP_TRANS_SALARY_KANA')
1238 || l_get_entry_values_rec.pjob_taxable_amt
1239 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1240 l_pjob_si_prem := ',' || fnd_message.get_string('PAY','PAY_JP_TRANS_SI_KANA')
1241 || l_get_entry_values_rec.pjob_si_prem
1242 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1243 if (l_get_entry_values_rec.pjob_mutual_aid is not NULL) then
1244 l_pjob_mutual_aid := '('||fnd_message.get_string('PAY','PAY_JP_WITHIN_KANA')
1245 || l_get_entry_values_rec.pjob_mutual_aid
1246 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA') ||')';
1247 end if;
1248 l_pjob_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX_KANA')
1249 || l_get_entry_values_rec.pjob_itax
1250 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1251 if nvl(l_get_entry_values_rec.pjob_addr, ' ') <> ' ' then
1252 l_pjob_addr := ',' || l_get_entry_values_rec.pjob_addr;
1253 end if;
1254 if nvl(l_get_entry_values_rec.pjob_employer_name, ' ') <> ' ' then
1255 l_pjob_employer_name := ',' || l_get_entry_values_rec.pjob_employer_name;
1256 end if;
1257 if (l_get_entry_values_rec.pjob_term_date is not NULL) then
1258 l_pjob_term_date := ','
1259 || lpad(to_char(l_year),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_YY_KANA')
1260 || lpad(to_char(l_month),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_MM_KANA')
1261 || lpad(to_char(l_day),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_DD_KANA')
1262 || fnd_message.get_string('PAY','PAY_JP_TRANS_TERM_KANA');
1263 end if;
1264 end if;
1265 l_description := pay_jp_report_pkg.substrb2(l_description
1266 || l_pjob_taxable_amt || l_pjob_si_prem
1267 || l_pjob_mutual_aid || l_pjob_itax
1268 || l_prev_add_id_for_file || l_pjob_addr
1269 || l_prev_name_id_for_file || l_pjob_employer_name
1270 || l_prev_term_id_for_file || l_pjob_term_date,1,2000);
1271 end if;
1272
1273 end loop;
1274
1275 return l_description;
1276
1277 END get_pjob_info;
1278 --
1279 -----------------------------------------------------
1280 -- CONVERT_TO_WTM_FORMAT --
1281 -----------------------------------------------------
1282 -- This part is out of scope for seed conversion.
1283 FUNCTION convert_to_wtm_format(
1284 p_text IN VARCHAR2,
1285 p_kanji_flag IN VARCHAR2,
1286 p_media_type IN VARCHAR2
1287 ) RETURN VARCHAR2
1288 IS
1289 l_text VARCHAR2(4000) := ltrim(rtrim(substrb(p_text,1,2000)));
1290 BEGIN
1291 if nvl(l_text,' ') = ' ' then
1292 return l_text;
1293 end if;
1294
1295 if nvl(p_media_type,'MT') <> 'MT' then
1296 l_text := replace(l_text,',','');
1297 end if;
1298 if nvl(p_kanji_flag,'1') = '0' then
1299 -- Translate KANA 2 byte to 1 byte
1300 l_text := translate(l_text,
1301 hr_jp_standard_pkg.sjhextochar('83418343834583478349834A834C834E83508352835483568358835A835C835E83608363836583678369836A836B836C836D836E837183748377837A837D837E8380838183828384838683888389838A838B838C838D838F83928393'),
1302 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCA6DD'));
1303
1304 -- for voiced sound
1305 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834B'),hr_jp_standard_pkg.sjhextochar('B6DE'));
1306 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834D'),hr_jp_standard_pkg.sjhextochar('B7DE'));
1307 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834F'),hr_jp_standard_pkg.sjhextochar('B8DE'));
1308 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8351'),hr_jp_standard_pkg.sjhextochar('B9DE'));
1309 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8353'),hr_jp_standard_pkg.sjhextochar('BADE'));
1310 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8355'),hr_jp_standard_pkg.sjhextochar('BBDE'));
1311 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8357'),hr_jp_standard_pkg.sjhextochar('BCDE'));
1312 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8359'),hr_jp_standard_pkg.sjhextochar('BDDE'));
1313 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835B'),hr_jp_standard_pkg.sjhextochar('BEDE'));
1314 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835D'),hr_jp_standard_pkg.sjhextochar('BFDE'));
1315 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835F'),hr_jp_standard_pkg.sjhextochar('C0DE'));
1316 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8361'),hr_jp_standard_pkg.sjhextochar('C1DE'));
1317 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8364'),hr_jp_standard_pkg.sjhextochar('C2DE'));
1318 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8366'),hr_jp_standard_pkg.sjhextochar('C3DE'));
1319 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8368'),hr_jp_standard_pkg.sjhextochar('C4DE'));
1320 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('836F'),hr_jp_standard_pkg.sjhextochar('CADE'));
1321 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8370'),hr_jp_standard_pkg.sjhextochar('CADF'));
1322 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8372'),hr_jp_standard_pkg.sjhextochar('CBDE'));
1323 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8373'),hr_jp_standard_pkg.sjhextochar('CBDF'));
1324 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8375'),hr_jp_standard_pkg.sjhextochar('CCDE'));
1325 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8376'),hr_jp_standard_pkg.sjhextochar('CCDF'));
1326 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8378'),hr_jp_standard_pkg.sjhextochar('CDDE'));
1327 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8379'),hr_jp_standard_pkg.sjhextochar('CDDF'));
1328 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('837B'),hr_jp_standard_pkg.sjhextochar('CEDE'));
1329 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('837C'),hr_jp_standard_pkg.sjhextochar('CEDF'));
1330
1331 -- for double consonant and so on
1332 l_text := translate(l_text,
1333 hr_jp_standard_pkg.sjhextochar('834083428344834683488383838583878362A7A8A9AAABACADAEAF'),
1334 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5D4D5D6C2B1B2B3B4B5D4D5D6C2'));
1335
1336 -- for others
1337 l_text := translate(l_text,
1338 hr_jp_standard_pkg.sjhextochar('81428175817681418145815B'),
1339 hr_jp_standard_pkg.sjhextochar('A1A2A3A4A5B0'));
1340
1341 -- for space
1342 l_text := translate(l_text,hr_jp_standard_pkg.sjhextochar('8140'),' ');
1343
1344 else
1345 -- Translate 1 byte to 2 byte
1346 l_text := to_multi_byte(l_text);
1347
1348 -- for voiced sound and so on
1349 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B6DE'),hr_jp_standard_pkg.sjhextochar('834B'));
1350 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B7DE'),hr_jp_standard_pkg.sjhextochar('834D'));
1351 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B8DE'),hr_jp_standard_pkg.sjhextochar('834F'));
1352 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B9DE'),hr_jp_standard_pkg.sjhextochar('8351'));
1353 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BADE'),hr_jp_standard_pkg.sjhextochar('8353'));
1354 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BBDE'),hr_jp_standard_pkg.sjhextochar('8355'));
1355 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BCDE'),hr_jp_standard_pkg.sjhextochar('8357'));
1356 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BDDE'),hr_jp_standard_pkg.sjhextochar('8359'));
1357 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BEDE'),hr_jp_standard_pkg.sjhextochar('835B'));
1358 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BFDE'),hr_jp_standard_pkg.sjhextochar('835D'));
1359 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C0DE'),hr_jp_standard_pkg.sjhextochar('835F'));
1360 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C1DE'),hr_jp_standard_pkg.sjhextochar('8361'));
1361 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C2DE'),hr_jp_standard_pkg.sjhextochar('8364'));
1362 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C3DE'),hr_jp_standard_pkg.sjhextochar('8366'));
1363 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C4DE'),hr_jp_standard_pkg.sjhextochar('8368'));
1364 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CADE'),hr_jp_standard_pkg.sjhextochar('836F'));
1365 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CADF'),hr_jp_standard_pkg.sjhextochar('8370'));
1366 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CBDE'),hr_jp_standard_pkg.sjhextochar('8372'));
1367 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CBDF'),hr_jp_standard_pkg.sjhextochar('8373'));
1368 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CCDE'),hr_jp_standard_pkg.sjhextochar('8375'));
1369 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CCDF'),hr_jp_standard_pkg.sjhextochar('8376'));
1370 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CDDE'),hr_jp_standard_pkg.sjhextochar('8378'));
1371 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CDDF'),hr_jp_standard_pkg.sjhextochar('8379'));
1372 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CEDE'),hr_jp_standard_pkg.sjhextochar('837B'));
1373 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CEDF'),hr_jp_standard_pkg.sjhextochar('837C'));
1374
1375 -- for KANA
1376 l_text := translate(l_text,
1377 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCA6DD'),
1378 hr_jp_standard_pkg.sjhextochar('83418343834583478349834A834C834E83508352835483568358835A835C835E83608363836583678369836A836B836C836D836E837183748377837A837D837E8380838183828384838683888389838A838B838C838D838F83928393'));
1379
1380 -- for double consonant and so on
1381 l_text := translate(l_text,
1382 hr_jp_standard_pkg.sjhextochar('A7A8A9AAABACADAEAF'),
1383 hr_jp_standard_pkg.sjhextochar('834083428344834683488383838583878362'));
1384
1385 -- for others
1386 l_text := translate(l_text,
1387 hr_jp_standard_pkg.sjhextochar('A1A2A3A4A5B0'),
1388 hr_jp_standard_pkg.sjhextochar('81428175817681418145815B'));
1389
1390 -- for space
1391 l_text := translate(l_text,' ',hr_jp_standard_pkg.sjhextochar('8140'));
1392 end if;
1393
1394
1395 return ltrim(rtrim(substrb(l_text,1,2000)));
1396
1397 END convert_to_wtm_format;
1398 --
1399 FUNCTION get_concatenated_disability(
1400 p_person_id IN NUMBER,
1401 p_effective_date IN DATE) RETURN VARCHAR2 IS
1402 --
1403 CURSOR cel_disability_details IS
1404 SELECT /*+ ORDERED
1405 INDEX(PCR PER_CONTACT_RELATIONSHIPS_N2)
1406 INDEX(PCEIF PER_CONTACT_EXTRA_INFO_N1)
1407 INDEX(PAPF PER_PEOPLE_F_PK) */
1408 DECODE(pceif.cei_information7,
1409 NULL,SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 ||
1410 DECODE(pceif.cei_information6,
1411 '20', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY') || ')',
1412 '30', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER') || ')' , NULL), 1, 2000),
1413 SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 || ' (' || pceif.cei_information7 ||
1414 DECODE(pceif.cei_information6,
1415 '20', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY'),
1416 '30', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER'), NULL) || ')',1,2000)) details
1417 FROM per_contact_relationships pcr,
1418 per_contact_extra_info_f pceif,
1419 per_all_people_f papf
1420 WHERE pcr.person_id = p_person_id
1421 AND pcr.cont_information_category = 'JP'
1422 AND pcr.cont_information1 = 'Y'
1423 AND p_effective_date
1424 BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
1425 AND pceif.contact_relationship_id = pcr.contact_relationship_id
1426 AND pceif.information_type = 'JP_ITAX_DEPENDENT'
1427 AND pceif.cei_information6 <> '0'
1428 AND p_effective_date
1429 BETWEEN pceif.effective_start_date AND pceif.effective_end_date
1430 AND papf.person_id = pcr.contact_person_id
1431 AND p_effective_date
1432 BETWEEN papf.effective_start_date AND papf.effective_end_date
1433 ORDER BY pcr.cont_information2,
1434 papf.date_of_birth;
1435 --
1436 l_first_flag BOOLEAN := TRUE;
1437 l_celrec_disability_details cel_disability_details%ROWTYPE;
1438 l_terminator VARCHAR2(5);
1439 l_disability_details VARCHAR2(2000);
1440 --
1441 BEGIN
1442 --
1443 FOR l_celrec_disability_details IN cel_disability_details LOOP
1444 --
1445 IF l_first_flag THEN
1446 --
1447 l_terminator := '';
1448 l_first_flag := FALSE;
1449 --
1450 ELSE
1451 --
1452 l_terminator := fnd_global.local_chr(10);
1453 --
1454 END IF;
1455 --
1456 l_disability_details := SUBSTRB(l_disability_details || l_terminator || l_celrec_disability_details.details, 1, 2000);
1457 --
1458 END LOOP;
1459 --
1460 RETURN(l_disability_details);
1461 --
1462 END get_concatenated_disability;
1463 --
1464 -- -----------------------------------------------------------------------------
1465 -- get_hi_dependent_exists
1466 -- -----------------------------------------------------------------------------
1467 --
1468 -- This function will be obsolete according to superseded with get_hi_dependent_number.
1469 --
1470 FUNCTION get_hi_dependent_exists(
1471 p_person_id IN NUMBER,
1472 p_effective_date IN DATE)
1473 --
1474 RETURN VARCHAR2 IS
1475 --
1476 l_return VARCHAR2(1);
1477 --
1478 CURSOR cel_hi_dependent_exists
1479 IS
1480 SELECT 'Y'
1481 FROM dual
1482 WHERE EXISTS(
1483 SELECT /*+ ORDERED */
1484 NULL
1485 FROM per_contact_relationships pcr,
1486 per_contact_extra_info_f pceif
1487 WHERE pcr.person_id = p_person_id
1488 AND pcr.cont_information_category = 'JP'
1489 AND pcr.cont_information1 = 'Y'
1490 AND p_effective_date
1491 BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
1492 AND pceif.contact_relationship_id = pcr.contact_relationship_id
1493 AND pceif.information_type LIKE 'JP_HI%'
1494 AND p_effective_date
1495 between pceif.effective_start_date and pceif.effective_end_date
1496 AND p_effective_date
1497 between DECODE(pceif.information_type,
1498 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
1499 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
1500 null)
1501 and nvl(DECODE(pceif.information_type,
1502 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
1503 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
1504 null),pceif.effective_end_date));
1505 --
1506 BEGIN
1507 --
1508 OPEN cel_hi_dependent_exists;
1509 FETCH cel_hi_dependent_exists INTO l_return;
1510 --
1511 IF cel_hi_dependent_exists%NOTFOUND THEN
1512 --
1513 l_return := 'N';
1514 --
1515 END IF;
1516 --
1517 CLOSE cel_hi_dependent_exists;
1518 --
1519 RETURN(l_return);
1520 --
1521 END get_hi_dependent_exists;
1522 --
1523 -- -----------------------------------------------------------------------------
1524 -- get_hi_dependent_number
1525 -- -----------------------------------------------------------------------------
1526 --
1527 function get_hi_dependent_number(
1528 --
1529 p_person_id in number,
1530 p_effective_date in date)
1531 --
1532 return number is
1533 --
1534 l_return number := 0;
1535 --
1536 -- Owing to distinguish contact is qualified/disqualified,
1537 -- It is required to point historical(date track) data on target date.
1538 --
1539 -- eg. 1. Employee HI qualified at 2004/01/01 (Session Date 2004/02/01)
1540 -- Contact HI_SPOUSE DFF
1541 -- QD : 2004/01/01
1542 -- DQD : Null
1543 -- ESD : 2004/02/01
1544 -- EED : 4712/12/31
1545 -- 2. Contact Start work at 2004/03/01 (Session Date 2004/03/01)
1546 -- Contact HI_SPOUSE DFF
1547 -- QD : 2004/01/01 2004/01/01
1548 -- DQD : Null 2004/03/01
1549 -- ESD : 2004/02/01 2004/03/01
1550 -- EED : 2004/02/29 4712/12/31
1551 -- 3. Contact Back family at 2004/04/01 (Session Date 2004/04/01)
1552 -- Contact HI_SPOUSE DFF
1553 -- QD : 2004/01/01 2004/01/01 2004/04/01
1554 -- DQD : Null 2004/03/01 Null
1555 -- ESD : 2004/02/01 2004/03/01 2004/04/01
1556 -- EED : 2004/02/29 2004/03/31 4712/12/31
1557 -- Employee is qualified in January, But Contact is not spouse
1558 -- since there is no data in the period. This is bad operation.
1559 -- Session Date(ESD) should be same or earlier than qualified date.
1560 -- If Employee is disqualified in Feburary, Contact is spouse.
1561 -- If Employee is disqualified in March, Contact is not spouse.
1562 -- If Employee is disqualified in April, Contact is spouse.
1563 --
1564 -- Following are example of coverage.
1565 --
1566 -- p_effective_date = 2004/01/01
1567 --
1568 -- Case1. o Case2. o Case3. x Case4. x Case5. x Case6. x Case7. x
1569 -- ESD 1990/01/01 1990/01/01 1990/01/01 1990/01/01 1990/01/01 2005/01/01 1990/01/01
1570 -- EED 4712/12/31 4712/12/31 4712/12/31 4712/12/31 4712/12/31 4712/12/31 2003/01/01
1571 -- QD 2003/01/01 2003/01/01 N/A N/A 2010/12/31 2003/01/01 2003/01/01
1572 -- DQD 2010/12/31 N/A 2010/12/31 N/A 2003/01/01 2010/12/31 2010/12/31
1573 --
1574 cursor cel_hi_dependent_number
1575 is
1576 select count(pcr.person_id)
1577 from per_contact_relationships pcr
1578 where pcr.person_id = p_person_id
1579 and pcr.cont_information_category = 'JP'
1580 and pcr.cont_information1 = 'Y'
1581 and p_effective_date
1582 between nvl(pcr.date_start, p_effective_date) and nvl(pcr.date_end, p_effective_date)
1583 and exists(
1584 select null
1585 from per_contact_extra_info_f pceif
1586 where pceif.contact_relationship_id = pcr.contact_relationship_id
1587 and p_effective_date
1588 between pceif.effective_start_date and pceif.effective_end_date
1589 and pceif.information_type like 'JP_HI%'
1590 and p_effective_date
1591 between decode(pceif.information_type,
1592 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
1593 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
1594 null)
1595 and nvl(DECODE(pceif.information_type,
1596 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
1597 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
1598 null),pceif.effective_end_date));
1599 --
1600 begin
1601 --
1602 open cel_hi_dependent_number;
1603 fetch cel_hi_dependent_number into l_return;
1604 close cel_hi_dependent_number;
1605 --
1606 return(l_return);
1607 --
1608 end get_hi_dependent_number;
1609 --
1610 -- -----------------------------------------------------------------------------
1611 -- chk_use_contact_extra_info
1612 -- -----------------------------------------------------------------------------
1613 function chk_use_contact_extra_info(
1614 p_business_group_id in number)
1615 return varchar2
1616 is
1617 --
1618 l_dpnt_control_method hr_organization_information.org_information1%type;
1619 l_return varchar2(1);
1620 --
1621 cursor csr_get_bg_info
1622 is
1623 select hoi.org_information1
1624 from hr_organization_information hoi
1625 where hoi.organization_id = p_business_group_id
1626 and hoi.org_information_context = 'JP_BUSINESS_GROUP_INFO';
1627 --
1628 begin
1629 --
1630 open csr_get_bg_info;
1631 fetch csr_get_bg_info into l_dpnt_control_method;
1632 close csr_get_bg_info;
1633 --
1634 -- Distinguish if contact relationship data can be used.
1635 --
1636 -- if bg info is null then return N
1637 l_return := 'N';
1638 --
1639 if l_dpnt_control_method = 'CEI' then
1640 l_return := 'Y';
1641 end if;
1642 --
1643 return(l_return);
1644 --
1645 end chk_use_contact_extra_info;
1646 --
1647 FUNCTION get_si_dependent_report_type(
1648 p_person_id per_all_people_f.person_id%TYPE,
1649 p_qualified_date DATE) RETURN NUMBER IS
1650 --
1651 CURSOR cel_added IS
1652 SELECT 1 FROM per_jp_si_dependent_transfer_v
1653 WHERE person_id = p_person_id
1654 AND dependent_type IN ('S', 'D')
1655 AND transfer_type = 'I'
1656 AND TRUNC(transfer_date) <> p_qualified_date;
1657 --
1658 CURSOR cel_hi_removed IS
1659 SELECT 2 FROM per_jp_si_dependent_transfer_v
1660 WHERE person_id = p_person_id
1661 AND dependent_type IN ('S', 'D')
1662 AND transfer_type = 'E';
1663 --
1664 CURSOR cel_np_added IS
1665 SELECT 4 FROM per_jp_si_dependent_transfer_v
1666 WHERE person_id = p_person_id
1667 AND dependent_type = '3'
1668 AND transfer_type = 'I'
1669 AND TRUNC(transfer_date) <> p_qualified_date;
1670 --
1671 CURSOR cel_np_removed IS
1672 SELECT 8 FROM per_jp_si_dependent_transfer_v
1673 WHERE person_id = p_person_id
1674 AND dependent_type = '3'
1675 AND transfer_type = 'E'
1676 AND type3_disqualified_notice = 'Y';
1677 --
1678 l_cursor NUMBER;
1679 l_return NUMBER;
1680 --
1681 BEGIN
1682 --
1683 l_return := 0;
1684 --
1685 OPEN cel_added;
1686 FETCH cel_added INTO l_cursor;
1687 --
1688 IF cel_added%FOUND THEN
1689 --
1690 l_return := l_cursor;
1691 --
1692 END IF;
1693 --
1694 CLOSE cel_added;
1695 --
1696 OPEN cel_hi_removed;
1697 FETCH cel_hi_removed INTO l_cursor;
1698 --
1699 IF cel_hi_removed%FOUND THEN
1700 --
1701 l_return := l_return + l_cursor;
1702 --
1703 END IF;
1704 --
1705 CLOSE cel_hi_removed;
1706 --
1707 OPEN cel_np_added;
1708 FETCH cel_np_added INTO l_cursor;
1709 --
1710 IF cel_np_added%FOUND THEN
1711 --
1712 l_return := l_return + l_cursor;
1713 --
1714 END IF;
1715 --
1716 CLOSE cel_np_added;
1717 --
1718 OPEN cel_np_removed;
1719 FETCH cel_np_removed INTO l_cursor;
1720 --
1721 IF cel_np_removed%FOUND THEN
1722 --
1723 l_return := l_return + l_cursor;
1724 --
1725 END IF;
1726 --
1727 CLOSE cel_np_removed;
1728 --
1729 RETURN(l_return);
1730 --
1731 END get_si_dependent_report_type;
1732 --
1733 FUNCTION get_si_dep_ee_effective_date(
1734 p_person_id per_all_people_f.person_id%TYPE,
1735 p_date_from DATE,
1736 p_date_to DATE,
1737 p_report_type hr_lookups.lookup_code%TYPE) RETURN DATE IS
1738 --
1739 CURSOR cel_max_effective_date IS
1740 SELECT transfer_date
1741 FROM per_jp_si_dependent_transfer_v
1742 WHERE person_id = p_person_id
1743 AND DECODE(transfer_type, 'I', transfer_date, 'E', transfer_date + 1) BETWEEN p_date_from AND p_date_to
1744 AND (p_report_type = '0'
1745 OR (p_report_type = '10'
1746 AND dependent_type IN ('S', 'D'))
1747 OR (p_report_type = '20'
1748 AND dependent_type = '3'))
1749 ORDER BY transfer_date DESC;
1750 --
1751 l_return DATE;
1752 --
1753 BEGIN
1754 --
1755 OPEN cel_max_effective_date;
1756 FETCH cel_max_effective_date INTO l_return;
1757 CLOSE cel_max_effective_date;
1758 --
1759 RETURN(l_return);
1760 --
1761 END get_si_dep_ee_effective_date;
1762 --
1763 -----------------------------------------------------
1764 -- DECODE_ASS_SET_NAME --
1765 -----------------------------------------------------
1766 --
1767 FUNCTION decode_ass_set_name(
1768 p_assignment_set_id in hr_assignment_sets.assignment_set_id%type)
1769 RETURN VARCHAR2
1770 IS
1771 --
1772 l_meaning varchar2(80) := null;
1773 --
1774 cursor csr_ass_set_name
1775 is
1776 select assignment_set_name
1777 from hr_assignment_sets
1778 where assignment_set_id = p_assignment_set_id;
1779 --
1780 BEGIN
1781 --
1782 -- Only open the cursor if the parameter is going to retrieve anything
1783 --
1784 if p_assignment_set_id is not null then
1785 open csr_ass_set_name;
1786 fetch csr_ass_set_name into l_meaning;
1787 close csr_ass_set_name;
1788 end if;
1789 --
1790 return l_meaning;
1791 --
1792 END decode_ass_set_name;
1793 --
1794 function get_si_rec_id(
1795 p_rec_name in varchar2)
1796 return number
1797 is
1798 --
1799 l_elm_id number;
1800 l_rslt_id number;
1801 --
1802 begin
1803 --
1804 if pay_jp_report_pkg.g_legislation_code is null
1805 or pay_jp_report_pkg.g_legislation_code <> c_legislation_code then
1806 --
1807 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1808 pay_jp_report_pkg.g_si_rec.hi_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_org_iv);
1809 pay_jp_report_pkg.g_si_rec.wp_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_org_iv);
1810 pay_jp_report_pkg.g_si_rec.wpf_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wpf_org_iv);
1811 pay_jp_report_pkg.g_si_rec.hi_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_num_iv);
1812 pay_jp_report_pkg.g_si_rec.wp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_num_iv);
1813 pay_jp_report_pkg.g_si_rec.bp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_bp_num_iv);
1814 --
1815 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1816 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1817 --
1818 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1819 pay_jp_report_pkg.g_si_rec.hi_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1820 pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1821 --
1822 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1823 pay_jp_report_pkg.g_si_rec.wp_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1824 pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1825 --
1826 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1827 pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1828 pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1829 --
1830 pay_jp_report_pkg.g_legislation_code := c_legislation_code;
1831 --
1832 end if;
1833 --
1834 if p_rec_name = 'hi_org_iv_id' then
1835 if pay_jp_report_pkg.g_si_rec.hi_org_iv_id is null then
1836 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1837 pay_jp_report_pkg.g_si_rec.hi_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_org_iv);
1838 end if;
1839 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_org_iv_id;
1840 elsif p_rec_name = 'wp_org_iv_id' then
1841 if pay_jp_report_pkg.g_si_rec.wp_org_iv_id is null then
1842 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1843 pay_jp_report_pkg.g_si_rec.wp_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_org_iv);
1844 end if;
1845 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_org_iv_id;
1846 elsif p_rec_name = 'wpf_org_iv_id' then
1847 if pay_jp_report_pkg.g_si_rec.wpf_org_iv_id is null then
1848 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1849 pay_jp_report_pkg.g_si_rec.wpf_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wpf_org_iv);
1850 end if;
1851 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_org_iv_id;
1852 elsif p_rec_name = 'hi_num_iv_id' then
1853 if pay_jp_report_pkg.g_si_rec.hi_num_iv_id is null then
1854 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1855 pay_jp_report_pkg.g_si_rec.hi_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_num_iv);
1856 end if;
1857 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_num_iv_id;
1858 elsif p_rec_name = 'wp_num_iv_id' then
1859 if pay_jp_report_pkg.g_si_rec.wp_num_iv_id is null then
1860 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1861 pay_jp_report_pkg.g_si_rec.wp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_num_iv);
1862 end if;
1863 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_num_iv_id;
1864 elsif p_rec_name = 'bp_num_iv_id' then
1865 if pay_jp_report_pkg.g_si_rec.bp_num_iv_id is null then
1866 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1867 pay_jp_report_pkg.g_si_rec.bp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_bp_num_iv);
1868 end if;
1869 l_rslt_id := pay_jp_report_pkg.g_si_rec.bp_num_iv_id;
1870 elsif p_rec_name = 'exc_iv_id' then
1871 if pay_jp_report_pkg.g_si_rec.exc_iv_id is null then
1872 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1873 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1874 end if;
1875 l_rslt_id := pay_jp_report_pkg.g_si_rec.exc_iv_id;
1876 elsif p_rec_name = 'hi_qd_iv_id' then
1877 if pay_jp_report_pkg.g_si_rec.hi_qd_iv_id is null then
1878 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1879 pay_jp_report_pkg.g_si_rec.hi_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1880 end if;
1881 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_qd_iv_id;
1882 elsif p_rec_name = 'wp_qd_iv_id' then
1883 if pay_jp_report_pkg.g_si_rec.wp_qd_iv_id is null then
1884 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1885 pay_jp_report_pkg.g_si_rec.wp_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1886 end if;
1887 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_qd_iv_id;
1888 elsif p_rec_name = 'wpf_qd_iv_id' then
1889 if pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id is null then
1890 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1891 pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1892 end if;
1893 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id;
1894 elsif p_rec_name = 'hi_dqd_iv_id' then
1895 if pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id is null then
1896 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1897 pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1898 end if;
1899 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id;
1900 elsif p_rec_name = 'wp_dqd_iv_id' then
1901 if pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id is null then
1902 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1903 pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1904 end if;
1905 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id;
1906 elsif p_rec_name = 'wpf_dqd_iv_id' then
1907 if pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id is null then
1908 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1909 pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1910 end if;
1911 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id;
1912 end if;
1913 --
1914 return l_rslt_id;
1915 end get_si_rec_id;
1916 --
1917 function get_gs_rec_id(
1918 p_rec_name in varchar2)
1919 return number
1920 is
1921 l_ele_set pay_element_sets.element_set_name%type;
1922 l_elm_id number;
1923 l_rslt_id number;
1924 --
1925 cursor csr_ele_set
1926 is
1927 select pes.element_set_id
1928 from pay_element_sets pes
1929 where pes.legislation_code = c_legislation_code
1930 and pes.element_set_name = l_ele_set;
1931 --
1932 begin
1933 --
1934 if pay_jp_report_pkg.g_legislation_code is null
1935 or pay_jp_report_pkg.g_legislation_code <> c_legislation_code then
1936 --
1937 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,-1,c_legislation_code);
1938 pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_mth_iv);
1939 pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_cat_iv);
1940 --
1941 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,-1,c_legislation_code);
1942 pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_mth_iv);
1943 pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_cat_iv);
1944 --
1945 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1946 pay_jp_report_pkg.g_si_rec.hi_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_org_iv);
1947 pay_jp_report_pkg.g_si_rec.wp_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_org_iv);
1948 pay_jp_report_pkg.g_si_rec.wpf_org_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wpf_org_iv);
1949 pay_jp_report_pkg.g_si_rec.hi_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_hi_num_iv);
1950 pay_jp_report_pkg.g_si_rec.wp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_wp_num_iv);
1951 pay_jp_report_pkg.g_si_rec.bp_num_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_bp_num_iv);
1952 --
1953 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1954 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1955 --
1956 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1957 pay_jp_report_pkg.g_si_rec.hi_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1958 pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1959 --
1960 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1961 pay_jp_report_pkg.g_si_rec.wp_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1962 pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1963 --
1964 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1965 pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_qd_iv);
1966 pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_dqd_iv);
1967 --
1968 l_ele_set := c_san_ele_set;
1969 open csr_ele_set;
1970 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
1971 close csr_ele_set;
1972 --
1973 l_ele_set := c_gep_ele_set;
1974 open csr_ele_set;
1975 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
1976 close csr_ele_set;
1977 --
1978 l_ele_set := c_iku_ele_set;
1979 open csr_ele_set;
1980 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
1981 close csr_ele_set;
1982 --
1983 pay_jp_report_pkg.g_legislation_code := c_legislation_code;
1984 --
1985 end if;
1986 --
1987 if p_rec_name = 'hi_appl_mth_iv_id' then
1988 if pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id is null then
1989 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,null,c_legislation_code);
1990 pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_mth_iv);
1991 end if;
1992 l_rslt_id := pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id;
1993 elsif p_rec_name = 'wp_appl_mth_iv_id' then
1994 if pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id is null then
1995 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,null,c_legislation_code);
1996 pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_mth_iv);
1997 end if;
1998 l_rslt_id := pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id;
1999 elsif p_rec_name = 'hi_appl_cat_iv_id' then
2000 if pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id is null then
2001 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,null,c_legislation_code);
2002 pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_cat_iv);
2003 end if;
2004 l_rslt_id := pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id;
2005 elsif p_rec_name = 'wp_appl_cat_iv_id' then
2006 if pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id is null then
2007 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,null,c_legislation_code);
2008 pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_appl_cat_iv);
2009 end if;
2010 l_rslt_id := pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id;
2011 elsif p_rec_name = 'san_ele_set_id' then
2012 if pay_jp_report_pkg.g_gs_rec.san_ele_set_id is null then
2013 open csr_ele_set;
2014 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
2015 close csr_ele_set;
2016 end if;
2017 l_rslt_id := pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
2018 elsif p_rec_name = 'gep_ele_set_id' then
2019 if pay_jp_report_pkg.g_gs_rec.gep_ele_set_id is null then
2020 open csr_ele_set;
2021 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
2022 close csr_ele_set;
2023 end if;
2024 l_rslt_id := pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
2025 elsif p_rec_name = 'iku_ele_set_id' then
2026 if pay_jp_report_pkg.g_gs_rec.iku_ele_set_id is null then
2027 open csr_ele_set;
2028 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
2029 close csr_ele_set;
2030 end if;
2031 l_rslt_id := pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
2032 end if;
2033 --
2034 return l_rslt_id;
2035 end get_gs_rec_id;
2036 --
2037 function chk_hi_wp(
2038 p_sort_order in varchar2,
2039 p_submit_type in number,
2040 p_si_type in number)
2041 return number
2042 is
2043 --
2044 l_hi_wp number := c_hi_num_sort;
2045 --
2046 begin
2047 --
2048 if p_sort_order = c_wp_number then
2049 --
2050 l_hi_wp := c_wp_num_sort;
2051 --
2052 else
2053 --
2054 if p_submit_type in (3,7) then
2055 --
2056 if p_si_type in (2,6) then
2057 --
2058 l_hi_wp := c_wp_num_sort;
2059 --
2060 end if;
2061 --
2062 end if;
2063 --
2064 end if;
2065 --
2066 return l_hi_wp;
2067 end chk_hi_wp;
2068 --
2069 procedure get_latest_std_mth_comp_info(
2070 p_assignment_id in number,
2071 p_effective_date in date,
2072 p_date_earned in date,
2073 p_applied_mth_iv_id in number,
2074 p_new_std_mth_comp_iv_id in number,
2075 p_old_std_mth_comp_iv_id in number,
2076 p_latest_applied_date out nocopy date,
2077 p_latest_std_mth_comp out nocopy varchar2)
2078 is
2079 --
2080 /* Limitation: This logic does not check whether employee is qualified at the past time. */
2081 /* Only related to current status of qualification. */
2082 /* Include updating entry and new entry as qualification. */
2083 cursor csr_past_std_mth_comp is
2084 select /*+ ORDERED
2085 USE_NL(PLIV1, PLIV2, PEE, PEEV1, PEEV2)
2086 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV1)
2087 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV2)
2088 INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
2089 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV1)
2090 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV2) */
2091 pee.element_entry_id ee_id,
2092 pee.effective_start_date ee_esd,
2093 pee.effective_end_date ee_eed,
2094 peev1.screen_entry_value applied_mth,
2095 peev2.screen_entry_value new_std_mth_comp
2096 from pay_link_input_values_f pliv1,
2097 pay_link_input_values_f pliv2,
2098 pay_element_entries_f pee,
2099 pay_element_entry_values_f peev1,
2100 pay_element_entry_values_f peev2
2101 where pliv1.input_value_id = p_applied_mth_iv_id
2102 and pliv2.input_value_id = p_new_std_mth_comp_iv_id
2103 and pee.assignment_id = p_assignment_id
2104 /* use not eed but esd to include entry data as qualification */
2105 /* DBItem Entry is referred by date earned, */
2106 /* but if update recurring has been occurred, */
2107 /* all future entry are updating from effective date. */
2108 /* therefore, don't need to include future entry till date earned. */
2109 and pee.entry_type = 'E'
2110 and pee.effective_start_date < p_effective_date
2111 and pee.element_link_id = pliv1.element_link_id
2112 and pee.element_link_id = pliv2.element_link_id
2113 and pee.effective_start_date
2114 between pliv1.effective_start_date and pliv1.effective_end_date
2115 and pee.effective_start_date
2116 between pliv2.effective_start_date and pliv2.effective_end_date
2117 and peev1.element_entry_id = pee.element_entry_id
2118 and peev1.input_value_id = pliv1.input_value_id
2119 and peev1.effective_start_date = pee.effective_start_date
2120 and peev1.effective_end_date = pee.effective_end_date
2121 and peev2.element_entry_id = pee.element_entry_id
2122 and peev2.input_value_id = pliv2.input_value_id
2123 and peev2.effective_start_date = pee.effective_start_date
2124 and peev2.effective_end_date = pee.effective_end_date
2125 order by pee.effective_start_date desc;
2126 --
2127 l_csr_past_std_mth_comp csr_past_std_mth_comp%rowtype;
2128 l_applied_mth_one_day_before varchar2(60);
2129 l_applied_mth varchar2(60);
2130 l_applied_mth_old varchar2(60);
2131 l_std_mth_comp_old varchar2(60);
2132 --
2133 begin
2134 --
2135 l_applied_mth := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id,p_assignment_id,p_effective_date);
2136 l_applied_mth_one_day_before := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id,p_assignment_id,p_effective_date - 1);
2137 --
2138 if trunc(to_date(nvl(l_applied_mth_one_day_before,'000101')||'01','YYYYMMDD'),'MM')
2139 < trunc(p_date_earned,'MM') then
2140 l_applied_mth_old := l_applied_mth_one_day_before;
2141 if nvl(l_applied_mth,'000101') <> nvl(l_applied_mth_one_day_before,'000101') then
2142 l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_old_std_mth_comp_iv_id,p_assignment_id,p_effective_date);
2143 else
2144 if l_applied_mth is not null and l_applied_mth_one_day_before is not null then
2145 /* This case is for entry that is not updated(process) ie. qualificaiton data */
2146 l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_new_std_mth_comp_iv_id,p_assignment_id,p_date_earned);
2147 end if;
2148 end if;
2149 else
2150 --
2151 open csr_past_std_mth_comp;
2152 loop
2153 fetch csr_past_std_mth_comp into l_csr_past_std_mth_comp;
2154 exit when csr_past_std_mth_comp%notfound;
2155 /* Applied data on the Same Date Earned Month can not be applicable since short term. */
2156 /* Geppen Applied 9 > Santei(8) => Previous entry should be used.) */
2157 /* Date Earned of Santei is always 8/1, One of Geppen is always X/1 */
2158 if trunc(to_date(nvl(l_csr_past_std_mth_comp.applied_mth,'000101')||'01','YYYYMMDD'),'MM')
2159 < trunc(p_date_earned,'MM') then
2160 l_applied_mth_old := l_csr_past_std_mth_comp.applied_mth;
2161 if l_applied_mth_old is not null then
2162 l_std_mth_comp_old := l_csr_past_std_mth_comp.new_std_mth_comp;
2163 --l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_new_std_mth_comp_iv_id,p_assignment_id,l_csr_past_std_mth_comp.ee_esd);
2164 end if;
2165 exit;
2166 end if;
2167 end loop;
2168 close csr_past_std_mth_comp;
2169 --
2170 end if;
2171 --
2172 if l_applied_mth_old is not null then
2173 p_latest_applied_date := to_date(l_applied_mth_old||'01','YYYYMMDD');
2174 else
2175 p_latest_applied_date := null;
2176 end if;
2177 --
2178 p_latest_std_mth_comp := l_std_mth_comp_old;
2179 --
2180 end get_latest_std_mth_comp_info;
2181 --
2182 function chk_hi_wp_invalid(
2183 p_qualified_date in date,
2184 p_disqualified_date in date,
2185 p_date_earned in date)
2186 return number
2187 is
2188 --
2189 l_qualified_date date := p_qualified_date;
2190 l_disqualified_date date := p_disqualified_date;
2191 --
2192 /* 0: N, 1: Y */
2193 l_hi_wp_invalid number := 0;
2194 --
2195 begin
2196 --
2197 if l_qualified_date is null then
2198 -- no entry value, not insured.
2199 if l_disqualified_date is null then
2200 --
2201 l_qualified_date := hr_api.g_eot;
2202 l_disqualified_date := hr_api.g_sot;
2203 -- this paterns identify "not insured" as shortage of data.
2204 -- qualified date should be under disqualified date.
2205 else
2206 --
2207 l_qualified_date := hr_api.g_eot;
2208 --
2209 end if;
2210 --
2211 else
2212 --
2213 -- This is normal patern.
2214 -- disqualified date should be over qualified date.
2215 if l_disqualified_date is null then
2216 --
2217 l_disqualified_date := hr_api.g_eot;
2218 --
2219 end if;
2220 --
2221 end if;
2222 --
2223 if p_date_earned < l_qualified_date
2224 or l_disqualified_date <= p_date_earned then
2225 --
2226 l_hi_wp_invalid := 1;
2227 --
2228 end if;
2229 --
2230 return l_hi_wp_invalid;
2231 end chk_hi_wp_invalid;
2232 --
2233 function get_applied_date_old(
2234 p_hi_invalid in number,
2235 p_wp_invalid in number,
2236 p_hi_applied_date_old in date,
2237 p_wp_applied_date_old in date,
2238 p_si_submit_type in number)
2239 return date
2240 is
2241 --
2242 l_applied_date_old date;
2243 --
2244 begin
2245 --
2246 if p_hi_invalid = 0 and p_wp_invalid = 0 then
2247 --
2248 if trunc(nvl(p_hi_applied_date_old,hr_api.g_sot),'MM') < trunc(nvl(p_wp_applied_date_old,hr_api.g_sot),'MM')
2249 or p_si_submit_type = 2 then
2250 --
2251 l_applied_date_old := p_wp_applied_date_old;
2252 --
2253 else
2254 --
2255 l_applied_date_old := p_hi_applied_date_old;
2256 --
2257 end if;
2258 --
2259 else
2260 --
2261 if p_hi_invalid = 1 then
2262 --
2263 l_applied_date_old := p_wp_applied_date_old;
2264 --
2265 end if;
2266 --
2267 if p_wp_invalid = 1 then
2268 --
2269 l_applied_date_old := p_hi_applied_date_old;
2270 --
2271 end if;
2272 --
2273 end if;
2274 --
2275 return l_applied_date_old;
2276 end get_applied_date_old;
2277 --
2278 function get_user_elm_name(p_base_elm_name in varchar2)
2279 return varchar2
2280 is
2281 --
2282 l_user_elm_name pay_element_types_f_tl.element_name%type;
2283 --
2284 cursor csr_user_elm_name
2285 is
2286 select pett.element_name
2287 from pay_element_types_f pet,
2288 pay_element_types_f_tl pett
2289 where pet.element_name = p_base_elm_name
2290 and pett.element_type_id = pet.element_type_id
2291 and pett.language = userenv('LANG');
2292 --
2293 begin
2294 --
2295 open csr_user_elm_name;
2296 fetch csr_user_elm_name into l_user_elm_name;
2297 close csr_user_elm_name;
2298 --
2299 return l_user_elm_name;
2300 end get_user_elm_name;
2301 --
2302 -- -------------------------------------------------------------------------
2303 -- append_select_clause
2304 -- -------------------------------------------------------------------------
2305 procedure append_select_clause(
2306 p_clause in varchar2,
2307 p_select_clause in out nocopy varchar2)
2308 is
2309 begin
2310 --
2311 p_select_clause := p_select_clause||p_clause||c_lf;
2312 --
2313 end append_select_clause;
2314 --
2315 -- -------------------------------------------------------------------------
2316 -- append_from_clause
2317 -- -------------------------------------------------------------------------
2318 procedure append_from_clause(
2319 p_clause in varchar2,
2320 p_from_clause in out nocopy varchar2,
2321 p_top in varchar2 default 'N')
2322 is
2323 begin
2324 --
2325 if p_from_clause is null then
2326 --
2327 p_from_clause := 'from '||p_clause||c_lf;
2328 --
2329 else
2330 --
2331 if p_top = 'Y' then
2332 --
2333 p_from_clause := 'from '||p_clause||substrb(p_from_clause,5,lengthb(p_from_clause) - 4)||c_lf;
2334 --
2335 else
2336 --
2337 p_from_clause := p_from_clause||p_clause||c_lf;
2338 --
2339 end if;
2340 --
2341 end if;
2342 --
2343 end append_from_clause;
2344 --
2345 -- -------------------------------------------------------------------------
2346 -- append_where_clause
2347 -- -------------------------------------------------------------------------
2348 procedure append_where_clause(
2349 p_clause in varchar2,
2350 p_where_clause in out nocopy varchar2)
2351 is
2352 begin
2353 --
2354 if p_where_clause is null then
2355 --
2356 p_where_clause := 'where '||p_clause||c_lf;
2357 --
2358 else
2359 --
2360 p_where_clause := p_where_clause||'and '||p_clause||c_lf;
2361 --
2362 end if;
2363 --
2364 end append_where_clause;
2365 --
2366 -- -------------------------------------------------------------------------
2367 -- append_order_clause
2368 -- -------------------------------------------------------------------------
2369 procedure append_order_clause(
2370 p_clause in varchar2,
2371 p_order_clause in out nocopy varchar2)
2372 is
2373 begin
2374 --
2375 if p_order_clause is null then
2376 --
2377 p_order_clause := 'order by '||p_clause||c_lf;
2378 --
2379 else
2380 --
2381 p_order_clause := p_order_clause||', '||p_clause||c_lf;
2382 --
2383 end if;
2384 --
2385 end append_order_clause;
2386 --
2387 -- -------------------------------------------------------------------------
2388 -- show_debug
2389 -- -------------------------------------------------------------------------
2390 procedure show_debug(
2391 p_text in varchar2)
2392 is
2393 --
2394 c_max number := 200;
2395 --
2396 l_max number;
2397 l_len number := 0;
2398 l_text_len number := lengthb(p_text);
2399 --
2400 begin
2401 --
2402 if p_text is not null then
2403 --
2404 <<loop_show_debug>>
2405 loop
2406 --
2407 if l_len >= l_text_len then
2408 exit loop_show_debug;
2409 end if;
2410 --
2411 l_max := l_text_len - l_len;
2412 if l_max > c_max then
2413 --
2414 l_max := c_max;
2415 --
2416 end if;
2417 --
2418 hr_utility.trace(substrb(p_text,l_len + 1,l_max));
2419 --
2420 l_len := l_len + l_max;
2421 --
2422 end loop loop_show_debug;
2423 --
2424 end if;
2425 --
2426 end show_debug;
2427 --
2428 -- -------------------------------------------------------------------------
2429 -- show_warning
2430 -- -------------------------------------------------------------------------
2431 procedure show_warning(
2432 p_which in number,
2433 p_text in varchar2)
2434 is
2435 --
2436 c_max number := 200;
2437 --
2438 l_max number;
2439 l_len number := 0;
2440 l_max_char_len number;
2441 -- use length (not lengthb)
2442 l_text_len number := length(p_text);
2443 --
2444 begin
2445 --
2446 if p_text is not null then
2447 --
2448 l_max_char_len := trunc(c_max/lengthb(to_multi_byte(' ')));
2449 --
2450 <<loop_show_warning>>
2451 loop
2452 --
2453 if l_len >= l_text_len then
2454 exit loop_show_warning;
2455 end if;
2456 --
2457 l_max := l_text_len - l_len;
2458 if l_max > c_max then
2459 --
2460 l_max := c_max;
2461 --
2462 end if;
2463 --
2464 -- use substr (not substrb)
2465 fnd_file.put_line(p_which,substr(p_text,l_len + 1,l_max));
2466 --
2467 l_len := l_len + l_max;
2468 --
2469 end loop loop_show_warning;
2470 --
2471 end if;
2472 --
2473 end show_warning;
2474 --
2475 -- -------------------------------------------------------------------------
2476 -- set_char_set
2477 -- -------------------------------------------------------------------------
2478 procedure set_char_set(
2479 p_char_set in varchar2)
2480 is
2481 begin
2482 --
2483 pay_jp_report_pkg.g_char_set := p_char_set;
2484 --
2485 end set_char_set;
2486 --
2487 -- -------------------------------------------------------------------------
2488 -- set_db_char_set
2489 -- -------------------------------------------------------------------------
2490 procedure set_db_char_set(
2491 p_db_char_set in varchar2 default null)
2492 is
2493 --
2494 cursor csr_db_char_set
2495 is
2496 --select tag
2497 select lookup_code
2498 from fnd_lookup_values
2499 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
2500 and lookup_code = substr(userenv('language'),instr(userenv('language'),'.') + 1)
2501 and language = 'US';
2502 --
2503 begin
2504 --
2505 if p_db_char_set is not null then
2506 --
2507 pay_jp_report_pkg.g_db_char_set := p_db_char_set;
2508 --
2509 else
2510 --
2511 if pay_jp_report_pkg.g_db_char_set is null then
2512 --
2513 open csr_db_char_set;
2514 fetch csr_db_char_set into pay_jp_report_pkg.g_db_char_set;
2515 close csr_db_char_set;
2516 --
2517 end if;
2518 --
2519 end if;
2520 --
2521 end set_db_char_set;
2522 --
2523 -- -------------------------------------------------------------------------
2524 -- check_file
2525 -- -------------------------------------------------------------------------
2526 function check_file(
2527 p_file_name in varchar2,
2528 p_file_dir in varchar2)
2529 return boolean
2530 is
2531 --
2532 l_check_file boolean := false;
2533 l_file_size number;
2534 l_block_size number;
2535 --
2536 begin
2537 --
2538 utl_file.fgetattr(p_file_dir,p_file_name,l_check_file,l_file_size,l_block_size);
2539 --
2540 -- workaround for some bug
2541 if l_check_file is null
2542 and l_file_size = 0
2543 and l_block_size = 0 then
2544 --
2545 l_check_file := false;
2546 --
2547 end if;
2548 --
2549 return l_check_file;
2550 --
2551 exception
2552 when others then
2553 --
2554 if g_debug then
2555 hr_utility.trace('check_file : others');
2556 end if;
2557 --
2558 return l_check_file;
2559 --
2560 end check_file;
2561 --
2562 -- -------------------------------------------------------------------------
2563 -- open_file
2564 -- -------------------------------------------------------------------------
2565 procedure open_file(
2566 p_file_name in varchar2,
2567 p_file_dir in varchar2,
2568 p_file_out out nocopy utl_file.file_type,
2569 p_file_type in varchar2 default 'a')
2570 is
2571 --
2572 l_file_out utl_file.file_type;
2573 l_user_error varchar2(255);
2574 --
2575 begin
2576 --
2577 if p_file_type = 'a'
2578 or p_file_type = 'w' then
2579 --
2580 l_file_out := utl_file.fopen(p_file_dir,p_file_name,p_file_type);
2581 --
2582 begin
2583 --
2584 utl_file.fclose(l_file_out);
2585 --
2586 exception
2587 when others then
2588 null;
2589 end;
2590 --
2591 end if;
2592 --
2593 p_file_out := utl_file.fopen(p_file_dir,p_file_name,p_file_type,c_max_line_size);
2594 --
2595 exception
2596 when utl_file.invalid_path then
2597 --
2598 if g_debug then
2599 hr_utility.trace('open_file : invalid_path');
2600 end if;
2601 --
2602 fnd_message.set_name('FND','CONC-FILE_ERROR');
2603 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2604 l_user_error := substrb(fnd_message.get,1,255);
2605 --
2606 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_PATH');
2607 fnd_message.set_token('FILE_DIR',p_file_dir,false);
2608 --
2609 raise_application_error(-20100,l_user_error);
2610 --
2611 when utl_file.invalid_mode then
2612 --
2613 if g_debug then
2614 hr_utility.trace('open_file : invalid_mode');
2615 end if;
2616 --
2617 fnd_message.set_name('FND','CONC-FILE_ERROR');
2618 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2619 l_user_error := substrb(fnd_message.get,1,255);
2620 --
2621 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_MODE');
2622 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2623 fnd_message.set_token('FILE_MODE','a',false);
2624 --
2625 raise_application_error(-20100,l_user_error);
2626 --
2627 when utl_file.invalid_operation then
2628 --
2629 if g_debug then
2630 hr_utility.trace('open_file : invalid_operation');
2631 end if;
2632 --
2633 fnd_message.set_name('FND','CONC-FILE_ERROR');
2634 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2635 l_user_error := substrb(fnd_message.get,1,255);
2636 --
2637 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_OPERATN');
2638 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2639 fnd_message.set_token('TEMP_DIR',p_file_dir,false);
2640 --
2641 raise_application_error(-20100,l_user_error);
2642 --
2643 when utl_file.invalid_maxlinesize then
2644 --
2645 if g_debug then
2646 hr_utility.trace('open_file : invalid_maxlinesize');
2647 end if;
2648 --
2649 fnd_message.set_name('FND','CONC-FILE_ERROR');
2650 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2651 l_user_error := substrb(fnd_message.get,1,255);
2652 --
2653 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_MAXLINE');
2654 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2655 fnd_message.set_token('MAXLINE',c_max_line_size,false);
2656 --
2657 raise_application_error(-20100,l_user_error);
2658 --
2659 when others then
2660 --
2661 if g_debug then
2662 hr_utility.trace('open_file : others');
2663 end if;
2664 --
2665 raise;
2666 --
2667 end open_file;
2668 --
2669 -- -------------------------------------------------------------------------
2670 -- read_file
2671 -- -------------------------------------------------------------------------
2672 procedure read_file(
2673 p_file_name in varchar2,
2674 p_file_out in utl_file.file_type,
2675 p_file_data_tbl out nocopy t_file_data_tbl)
2676 is
2677 --
2678 l_file_data_tbl t_file_data_tbl;
2679 l_file_data_tbl_cnt number;
2680 --
2681 l_user_error varchar2(255);
2682 --
2683 begin
2684 --
2685 l_file_data_tbl.delete;
2686 l_file_data_tbl_cnt := 0;
2687 --
2688 loop
2689 --
2690 begin
2691 --
2692 utl_file.get_line(p_file_out,l_file_data_tbl(l_file_data_tbl_cnt));
2693 -- remove carriage return for linux
2694 l_file_data_tbl(l_file_data_tbl_cnt) := replace(l_file_data_tbl(l_file_data_tbl_cnt),c_cr,null);
2695 l_file_data_tbl_cnt := l_file_data_tbl_cnt + 1;
2696 --
2697 exception
2698 when no_data_found then
2699 exit;
2700 end;
2701 --
2702 end loop;
2703 --
2704 p_file_data_tbl := l_file_data_tbl;
2705 --
2706 exception
2707 when utl_file.invalid_filehandle then
2708 --
2709 if g_debug then
2710 hr_utility.trace('read_file : invalid_filehandle');
2711 end if;
2712 --
2713 fnd_message.set_name('FND','CONC-FILE_ERROR');
2714 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2715 l_user_error := substrb(fnd_message.get,1,255);
2716 --
2717 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_HANDLE');
2718 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2719 --
2720 raise_application_error(-20100,l_user_error);
2721 --
2722 when utl_file.invalid_operation then
2723 --
2724 if g_debug then
2725 hr_utility.trace('read_file : invalid_operation');
2726 end if;
2727 --
2728 fnd_message.set_name('FND','CONC-FILE_ERROR');
2729 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2730 l_user_error := substrb(fnd_message.get,1,255);
2731 --
2732 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_OPERATN');
2733 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2734 --
2735 raise_application_error(-20100,l_user_error);
2736 --
2737 when utl_file.read_error then
2738 --
2739 if g_debug then
2740 hr_utility.trace('read_file : read_error');
2741 end if;
2742 --
2743 fnd_message.set_name('FND','CONC-FILE_ERROR');
2744 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2745 l_user_error := substrb(fnd_message.get,1,255);
2746 --
2747 fnd_message.set_name('FND','CONC-TEMPFILE_READ_ERROR');
2748 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2749 --
2750 raise_application_error(-20100,l_user_error);
2751 --
2752 when others then
2753 --
2754 if g_debug then
2755 hr_utility.trace('write_file : others');
2756 end if;
2757 --
2758 raise;
2759 --
2760 end read_file;
2761 --
2762 -- -------------------------------------------------------------------------
2763 -- write_file
2764 -- -------------------------------------------------------------------------
2765 procedure write_file(
2766 p_file_name in varchar2,
2767 p_file_out in utl_file.file_type,
2768 p_line in varchar2,
2769 p_char_set in varchar2 default null)
2770 is
2771 --
2772 l_max_char_len number;
2773 l_line_len number;
2774 l_char_len number := 0;
2775 l_char_s number := 1;
2776 l_loop_cnt number := 0;
2777 l_user_error varchar2(255);
2778 l_char_set varchar2(30);
2779 --
2780 begin
2781 --
2782 if p_line is not null
2783 and lengthb(p_line) > c_max_line_size then
2784 --
2785 if g_debug then
2786 hr_utility.trace('write_file over length');
2787 end if;
2788 --
2789 l_char_set := p_char_set;
2790 if l_char_set is null then
2791 --
2792 --if pay_jp_report_pkg.g_char_set is null then
2793 -- hr_utility.set_message(800,'HR_7944_CHECK_FMT_BAD_FORMAT');
2794 -- hr_utility.raise_error;
2795 --else
2796 l_char_set := pay_jp_report_pkg.g_char_set;
2797 --end if;
2798 --
2799 end if;
2800 --
2801 if l_char_set is null then
2802 l_max_char_len := trunc(c_max_line_size/lengthb(to_multi_byte(' ')));
2803 else
2804 l_max_char_len := trunc(c_max_line_size/lengthb(convert(to_multi_byte(' '),l_char_set)));
2805 end if;
2806 --
2807 -- use length (not lengthb)
2808 l_line_len := length(p_line);
2809 --
2810 <<loop_write_line>>
2811 loop
2812 --
2813 if l_char_s > l_line_len then
2814 exit loop_write_line;
2815 else
2816 --
2817 l_loop_cnt := l_loop_cnt + 1;
2818 --
2819 if l_max_char_len * l_loop_cnt > l_line_len then
2820 l_char_len := l_line_len - (l_max_char_len * (l_loop_cnt - 1));
2821 else
2822 l_char_len := l_max_char_len;
2823 end if;
2824 --
2825 -- use substr (not substrb)
2826 utl_file.put(p_file_out, substr(p_line,l_char_s,l_char_len));
2827 -- mandatory for except ORA-29285 (limitation max char size in 1 line)
2828 utl_file.new_line(p_file_out,1);
2829 --
2830 l_char_s := l_char_s + l_char_len;
2831 --
2832 end if;
2833 --
2834 end loop loop_write_line;
2835 --
2836 utl_file.new_line(p_file_out,1);
2837 --
2838 else
2839 --
2840 if g_debug then
2841 hr_utility.trace('write_file normal length');
2842 end if;
2843 --
2844 utl_file.put_line(p_file_out,p_line);
2845 --
2846 end if;
2847 --
2848 exception
2849 when utl_file.invalid_filehandle then
2850 --
2851 if g_debug then
2852 hr_utility.trace('write_file : invalid_filehandle');
2853 end if;
2854 --
2855 fnd_message.set_name('FND','CONC-FILE_ERROR');
2856 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2857 l_user_error := substrb(fnd_message.get,1,255);
2858 --
2859 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_HANDLE');
2860 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2861 --
2862 raise_application_error(-20100,l_user_error);
2863 --
2864 when utl_file.invalid_operation then
2865 --
2866 if g_debug then
2867 hr_utility.trace('write_file : invalid_operation');
2868 end if;
2869 --
2870 fnd_message.set_name('FND','CONC-FILE_ERROR');
2871 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2872 l_user_error := substrb(fnd_message.get,1,255);
2873 --
2874 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_OPERATN');
2875 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2876 --
2877 raise_application_error(-20100,l_user_error);
2878 --
2879 when utl_file.write_error then
2880 --
2881 if g_debug then
2882 hr_utility.trace('write_file : write_error');
2883 end if;
2884 --
2885 fnd_message.set_name('FND','CONC-FILE_ERROR');
2886 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2887 l_user_error := substrb(fnd_message.get,1,255);
2888 --
2889 fnd_message.set_name('FND','CONC-TEMPFILE_WRITE_ERROR');
2890 fnd_message.set_token('TEMP_FILE',p_file_name,false);
2891 --
2892 raise_application_error(-20100,l_user_error);
2893 --
2894 when others then
2895 --
2896 if g_debug then
2897 hr_utility.trace('write_file : others');
2898 end if;
2899 --
2900 raise;
2901 --
2902 end write_file;
2903 --
2904 -- -------------------------------------------------------------------------
2905 -- close_file
2906 -- -------------------------------------------------------------------------
2907 procedure close_file(
2908 p_file_name in varchar2,
2909 p_file_out in out nocopy utl_file.file_type,
2910 p_file_type in varchar2 default 'a')
2911 is
2912 --
2913 begin
2914 --
2915 -- fflush is not necessary if the case is to refer file while writing
2916 -- exclude case in w, a because of performance reason
2917 if p_file_type = 'f' then
2918 --
2919 utl_file.fflush(p_file_out);
2920 --
2921 end if;
2922 --
2923 utl_file.fclose(p_file_out);
2924 --
2925 exception
2926 when others then
2927 --
2928 if g_debug then
2929 hr_utility.trace('close file error : '||p_file_name);
2930 end if;
2931 --
2932 raise;
2933 --
2934 end close_file;
2935 --
2936 -- -------------------------------------------------------------------------
2937 -- delete_file
2938 -- -------------------------------------------------------------------------
2939 procedure delete_file(
2940 p_file_dir in varchar2,
2941 p_file_name in varchar2)
2942 is
2943 --
2944 l_file_chk boolean;
2945 l_file_size number;
2946 l_block_size number;
2947 --
2948 begin
2949 --
2950 utl_file.fgetattr(p_file_dir,p_file_name,l_file_chk,l_file_size,l_block_size);
2951 if l_file_chk then
2952 --
2953 utl_file.fremove(p_file_dir,p_file_name);
2954 --
2955 end if;
2956 --
2957 exception
2958 when others then
2959 --
2960 if g_debug then
2961 hr_utility.trace('delete file error : '||p_file_name);
2962 end if;
2963 --
2964 raise;
2965 --
2966 end delete_file;
2967 --
2968 -- -------------------------------------------------------------------------
2969 -- split_str
2970 -- -------------------------------------------------------------------------
2971 function split_str(
2972 p_text in varchar2,
2973 p_n in number)
2974 return varchar2
2975 is
2976 --
2977 l_text varchar2(4000);
2978 --
2979 l_pos number;
2980 l_prev_pos number;
2981 --
2982 begin
2983 --
2984 if lengthb(p_text) > 0
2985 and p_n > 0 then
2986 --
2987 l_pos := nvl(instrb(p_text,c_comma_delimiter,1,p_n),0);
2988 -- first part
2989 if p_n = 1 then
2990 --
2991 if l_pos > 0 then
2992 --
2993 l_text := substrb(p_text,1,l_pos-1);
2994 --
2995 end if;
2996 --
2997 else
2998 --
2999 l_prev_pos := nvl(instrb(p_text,c_comma_delimiter,1,p_n-1),0);
3000 --
3001 if l_prev_pos > 0 then
3002 --
3003 -- last part
3004 if l_pos = 0 then
3005 --
3006 l_text := substrb(p_text,l_prev_pos+1);
3007 --
3008 else
3009 --
3010 l_text := substrb(p_text,l_prev_pos+1,l_pos-l_prev_pos-1);
3011 --
3012 end if;
3013 --
3014 end if;
3015 --
3016 end if;
3017 --
3018 end if;
3019 --
3020 return l_text;
3021 --
3022 end split_str;
3023 --
3024 -- -------------------------------------------------------------------------
3025 -- cnv_str
3026 -- -------------------------------------------------------------------------
3027 function cnv_str(
3028 p_text in varchar2,
3029 p_start in number default null,
3030 p_end in number default null)
3031 return varchar2
3032 is
3033 --
3034 l_text varchar2(4000);
3035 --
3036 begin
3037 --
3038 l_text := ltrim(rtrim(replace(p_text,to_multi_byte(' '),' ')));
3039 --
3040 if p_start is not null
3041 and p_end is not null then
3042 --
3043 -- use substr (not substrb)
3044 l_text := substr(l_text,p_start,p_end);
3045 --
3046 end if;
3047 --
3048 return l_text;
3049 --
3050 end cnv_str;
3051 --
3052 -- -------------------------------------------------------------------------
3053 -- cnv_siz (text)
3054 -- -------------------------------------------------------------------------
3055 function cnv_siz(
3056 p_type in varchar2,
3057 p_len in number,
3058 p_text in varchar2)
3059 return varchar2
3060 is
3061 --
3062 l_len number;
3063 --
3064 begin
3065 --
3066 -- use substr (not substrb)
3067 if p_type = 'z' then
3068 return substr(hr_jp_standard_pkg.to_zenkaku(p_text),1,p_len);
3069 elsif p_type = 'h' then
3070 return substr(hr_jp_standard_pkg.to_hankaku(p_text),1,p_len);
3071 else
3072 return substr(p_text,1,p_len);
3073 end if;
3074 --
3075 end cnv_siz;
3076 --
3077 -- -------------------------------------------------------------------------
3078 -- cnv_siz (number)
3079 -- -------------------------------------------------------------------------
3080 function cnv_siz(
3081 p_type in varchar2,
3082 p_len in number,
3083 p_text in number)
3084 return number
3085 is
3086 begin
3087 --
3088 return to_number(to_single_byte(cnv_siz(p_type,p_len,to_char(p_text))));
3089 --
3090 end cnv_siz;
3091 --
3092 -- -------------------------------------------------------------------------
3093 -- cnv_txt (text)
3094 -- -------------------------------------------------------------------------
3095 function cnv_txt(
3096 p_text in varchar2,
3097 p_char_set in varchar2 default null)
3098 return varchar2
3099 is
3100 --
3101 l_char_set varchar2(30);
3102 --l_db_char_set varchar2(30);
3103 --
3104 --cursor csr_db_char_set
3105 --is
3106 --select tag
3107 --from fnd_lookup_values
3108 --where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
3109 --and lookup_code = substr(userenv('language'),instr(userenv('language'),'.') + 1)
3110 --and language = 'US';
3111 --
3112 begin
3113 --
3114 --open csr_db_char_set;
3115 --fetch csr_db_char_set into l_db_char_set;
3116 --close csr_db_char_set;
3117 --
3118 l_char_set := p_char_set;
3119 if l_char_set is null then
3120 --
3121 --if pay_jp_report_pkg.g_char_set is null then
3122 -- hr_utility.set_message(800,'HR_7944_CHECK_FMT_BAD_FORMAT');
3123 -- hr_utility.raise_error;
3124 --else
3125 l_char_set := pay_jp_report_pkg.g_char_set;
3126 --end if;
3127 --
3128 end if;
3129 --
3130 --return convert(convert(p_text,l_db_char_set),l_char_set,l_db_char_set);
3131 return convert(p_text,l_char_set);
3132 --
3133 end cnv_txt;
3134 --
3135 -- -------------------------------------------------------------------------
3136 -- cnv_txt (number)
3137 -- -------------------------------------------------------------------------
3138 function cnv_txt(
3139 p_text in number,
3140 p_char_set in varchar2 default null)
3141 return varchar2
3142 is
3143 begin
3144 --
3145 -- not use fnd_number.number_to_canonical
3146 return cnv_txt(to_char(p_text),p_char_set);
3147 --
3148 end cnv_txt;
3149 --
3150 -- -------------------------------------------------------------------------
3151 -- cnv_db_txt (text)
3152 -- -------------------------------------------------------------------------
3153 function cnv_db_txt(
3154 p_text in varchar2,
3155 p_char_set in varchar2 default null,
3156 p_db_char_set in varchar2 default null)
3157 return varchar2
3158 is
3159 --
3160 l_char_set varchar2(30);
3161 l_db_char_set varchar2(30);
3162 --
3163 --cursor csr_db_char_set
3164 --is
3165 --select tag
3166 --from fnd_lookup_values
3167 --where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
3168 --and lookup_code = substr(userenv('language'),instr(userenv('language'),'.') + 1)
3169 --and language = 'US';
3170 --
3171 begin
3172 --
3173 --open csr_db_char_set;
3174 --fetch csr_db_char_set into l_db_char_set;
3175 --close csr_db_char_set;
3176 --
3177 l_char_set := p_char_set;
3178 if l_char_set is null then
3179 --
3180 --if pay_jp_report_pkg.g_char_set is null then
3181 -- hr_utility.set_message(800,'HR_7944_CHECK_FMT_BAD_FORMAT');
3182 -- hr_utility.raise_error;
3183 --else
3184 l_char_set := pay_jp_report_pkg.g_char_set;
3185 --end if;
3186 --
3187 end if;
3188 --
3189 l_db_char_set := p_db_char_set;
3190 if l_db_char_set is null then
3191 --
3192 --if pay_jp_report_pkg.g_db_char_set is null then
3193 -- hr_utility.set_message(800,'HR_7944_CHECK_FMT_BAD_FORMAT');
3194 -- hr_utility.raise_error;
3195 --else
3196 l_db_char_set := pay_jp_report_pkg.g_db_char_set;
3197 --end if;
3198 --
3199 end if;
3200 --
3201 return convert(p_text,l_db_char_set,l_char_set);
3202 --
3203 end cnv_db_txt;
3204 --
3205 -- -------------------------------------------------------------------------
3206 -- add_tag (text)
3207 -- -------------------------------------------------------------------------
3208 function add_tag(
3209 p_tag in varchar2,
3210 p_text in varchar2)
3211 return varchar2
3212 is
3213 begin
3214 --
3215 return '<'||p_tag||'>'||p_text||'</'||p_tag||'>';
3216 --
3217 end add_tag;
3218 --
3219 -- -------------------------------------------------------------------------
3220 -- add_tag (date)
3221 -- -------------------------------------------------------------------------
3222 function add_tag(
3223 p_tag in varchar2,
3224 p_text in date)
3225 return varchar2
3226 is
3227 begin
3228 --
3229 return add_tag(p_tag,fnd_date.date_to_canonical(p_text));
3230 --
3231 end add_tag;
3232 --
3233 -- -------------------------------------------------------------------------
3234 -- add_tag (number)
3235 -- -------------------------------------------------------------------------
3236 function add_tag(
3237 p_tag in varchar2,
3238 p_text in number)
3239 return varchar2
3240 is
3241 begin
3242 --
3243 return add_tag(p_tag,fnd_number.number_to_canonical(p_text));
3244 --
3245 end add_tag;
3246 --
3247 -- -------------------------------------------------------------------------
3248 -- add_tag_m (money)
3249 -- -------------------------------------------------------------------------
3250 function add_tag_m(
3251 p_tag in varchar2,
3252 p_text in number)
3253 return varchar2
3254 is
3255 begin
3256 --
3257 return add_tag(p_tag,htmlspchar(to_char(p_text,fnd_currency.get_format_mask('JPY',40))));
3258 --
3259 end add_tag_m;
3260 --
3261 -- -------------------------------------------------------------------------
3262 -- add_tag_v (text)
3263 -- -------------------------------------------------------------------------
3264 function add_tag_v(
3265 p_tag in varchar2,
3266 p_text in varchar2)
3267 return varchar2
3268 is
3269 begin
3270 --
3271 return add_tag(p_tag,htmlspchar(p_text));
3272 --
3273 end add_tag_v;
3274 --
3275 -- -------------------------------------------------------------------------
3276 -- htmlspchar
3277 -- -------------------------------------------------------------------------
3278 function htmlspchar(
3279 p_text in varchar2)
3280 return varchar2
3281 is
3282 --
3283 l_htmlspchar varchar2(1) := 'N';
3284 --
3285 begin
3286 --
3287 if nvl(instr(p_text,'<'),0) > 0 then
3288 l_htmlspchar := 'Y';
3289 end if;
3290 --
3291 if l_htmlspchar = 'N'
3292 and nvl(instr(p_text,'>'),0) > 0 then
3293 l_htmlspchar := 'Y';
3294 end if;
3295 --
3296 if l_htmlspchar = 'N'
3297 and nvl(instr(p_text,'\&'),0) > 0 then
3298 l_htmlspchar := 'Y';
3299 end if;
3300 --
3301 if l_htmlspchar = 'N'
3302 and nvl(instr(p_text,''''),0) > 0 then
3303 l_htmlspchar := 'Y';
3304 end if;
3305 --
3306 if l_htmlspchar = 'N'
3307 and nvl(instr(p_text,'"'),0) > 0 then
3308 l_htmlspchar := 'Y';
3309 end if;
3310 --
3311 if l_htmlspchar = 'Y' then
3312 return '<![CDATA['||p_text||']]>';
3313 else
3314 return p_text;
3315 end if;
3316 end htmlspchar;
3317 --
3318 -- -------------------------------------------------------------------------
3319 -- set_delimiter
3320 -- -------------------------------------------------------------------------
3321 procedure set_delimiter(
3322 p_delimiter in varchar2)
3323 is
3324 begin
3325 --
3326 pay_jp_report_pkg.g_delimiter := p_delimiter;
3327 --
3328 end set_delimiter;
3329 --
3330 -- -------------------------------------------------------------------------
3331 -- csvspchar
3332 -- -------------------------------------------------------------------------
3333 function csvspchar(
3334 p_text in varchar2)
3335 return varchar2
3336 is
3337 --
3338 l_text varchar2(4000);
3339 --
3340 begin
3341 --
3342 if pay_jp_report_pkg.g_delimiter is null then
3343 set_delimiter(c_dot_delimiter);
3344 end if;
3345 --
3346 l_text := replace(p_text,c_comma_delimiter,pay_jp_report_pkg.g_delimiter);
3347 --
3348 return l_text;
3349 end csvspchar;
3350 --
3351 -- -------------------------------------------------------------------------
3352 -- decode_value
3353 -- -------------------------------------------------------------------------
3354 function decode_value(
3355 p_condition in boolean,
3356 p_true_value in varchar2,
3357 p_false_value in varchar2)
3358 return varchar2
3359 is
3360 begin
3361 --
3362 if p_condition then
3363 return p_true_value;
3364 else
3365 return p_false_value;
3366 end if;
3367 --
3368 end decode_value;
3369 --
3370 -- -------------------------------------------------------------------------
3371 -- jp_era_date
3372 -- -------------------------------------------------------------------------
3373 function jp_era_date(
3374 p_date in date,
3375 p_format in varchar2)
3376 return varchar2
3377 is
3378 --
3379 o_era_date varchar2(300);
3380 --
3381 cursor jp_era_date
3382 is
3383 select to_char(p_date,p_format,'nls_calendar=''Japanese Imperial''')
3384 from dual;
3385 --
3386 begin
3387 --
3388 open jp_era_date;
3389 fetch jp_era_date into o_era_date;
3390 close jp_era_date;
3391 --
3392 return o_era_date;
3393 --
3394 end jp_era_date;
3395 --
3396 -- -------------------------------------------------------------------------
3397 -- get_udt_value
3398 -- -------------------------------------------------------------------------
3399 function get_udt_value(
3400 p_business_group_id in number,
3401 p_table in varchar2,
3402 p_column in varchar2,
3403 p_row in varchar2,
3404 p_effective_date in date)
3405 return varchar2
3406 is
3407 --
3408 l_table_id number;
3409 l_range_or_match pay_user_tables.range_or_match%type;
3410 l_user_key_units pay_user_tables.user_key_units%type;
3411 o_value pay_user_column_instances_f.value%type;
3412 --
3413 cursor csr_udt
3414 is
3415 select put.user_table_id,
3416 put.range_or_match,
3417 put.user_key_units
3418 from pay_user_tables put
3419 where put.user_table_name = p_table
3420 and nvl(put.business_group_id,p_business_group_id) = p_business_group_id
3421 and nvl(put.legislation_code,c_legislation_code) = c_legislation_code;
3422 --
3423 cursor csr_match_value
3424 is
3425 select puci.value
3426 from pay_user_columns puc,
3427 pay_user_rows_f pur,
3428 pay_user_column_instances_f puci
3429 where puc.user_table_id = l_table_id
3430 and puc.user_column_name = p_column
3431 and nvl(puc.business_group_id,p_business_group_id) = p_business_group_id
3432 and nvl(puc.legislation_code,c_legislation_code) = c_legislation_code
3433 and pur.user_table_id = l_table_id
3434 and p_effective_date
3435 between pur.effective_start_date and pur.effective_end_date
3436 and decode(l_user_key_units,
3437 'D',to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
3438 pur.row_low_range_or_name) =
3439 decode(l_user_key_units,
3440 'D', to_char(fnd_date.canonical_to_date(p_row)),
3441 p_row)
3442 and nvl(pur.business_group_id,p_business_group_id) = p_business_group_id
3443 and nvl(pur.legislation_code,c_legislation_code) = c_legislation_code
3444 and puci.user_column_id = puc.user_column_id
3445 and puci.user_row_id = pur.user_row_id
3446 and p_effective_date
3447 between puci.effective_start_date and puci.effective_end_date;
3448 --
3449 cursor csr_range_value
3450 is
3451 select puci.value
3452 from pay_user_columns puc,
3453 pay_user_rows_f pur,
3454 pay_user_column_instances_f puci
3455 where puc.user_table_id = l_table_id
3456 and puc.user_column_name = p_column
3457 and nvl(puc.business_group_id,p_business_group_id) = p_business_group_id
3458 and nvl(puc.legislation_code,c_legislation_code) = c_legislation_code
3459 and pur.user_table_id = l_table_id
3460 and p_effective_date
3461 between pur.effective_start_date and pur.effective_end_date
3462 and fnd_number.canonical_to_number(p_row)
3463 between fnd_number.canonical_to_number(pur.row_low_range_or_name) and fnd_number.canonical_to_number(pur.row_high_range)
3464 and nvl(pur.business_group_id,p_business_group_id) = p_business_group_id
3465 and nvl(pur.legislation_code,c_legislation_code) = c_legislation_code
3466 and puci.user_column_id = puc.user_column_id
3467 and puci.user_row_id = pur.user_row_id
3468 and p_effective_date
3469 between puci.effective_start_date and puci.effective_end_date;
3470 --
3471 begin
3472 --
3473 open csr_udt;
3474 fetch csr_udt into l_table_id, l_range_or_match, l_user_key_units;
3475 close csr_udt;
3476 --
3477 if l_table_id is not null then
3478 --
3479 if l_range_or_match = 'M' then
3480 --
3481 open csr_match_value;
3482 fetch csr_match_value into o_value;
3483 close csr_match_value;
3484 --
3485 else
3486 --
3487 if l_user_key_units = 'N' then
3488 --
3489 open csr_range_value;
3490 fetch csr_range_value into o_value;
3491 close csr_range_value;
3492 --
3493 end if;
3494 --
3495 end if;
3496 --
3497 end if;
3498 --
3499 return o_value;
3500 --
3501 end get_udt_value;
3502 --
3503 -- -------------------------------------------------------------------------
3504 -- set_era_code
3505 -- -------------------------------------------------------------------------
3506 function set_era_code(
3507 p_date in date,
3508 p_era_code in varchar2)
3509 return varchar2
3510 is
3511 --
3512 l_era_code varchar2(30);
3513 --
3514 o_match_era varchar2(60);
3515 --
3516 begin
3517 --
3518 l_era_code := pay_jp_report_pkg.jp_era_date(p_date,'E');
3519 --
3520 if l_era_code = p_era_code then
3521 --
3522 o_match_era := c_circle;
3523 --
3524 end if;
3525 --
3526 return o_match_era;
3527 end set_era_code;
3528 --
3529 -- -------------------------------------------------------------------------
3530 -- set_circle
3531 -- -------------------------------------------------------------------------
3532 function set_circle(
3533 p_data in varchar2,
3534 p_condition in varchar2)
3535 return varchar2
3536 is
3537 --
3538 o_circle varchar2(60);
3539 --
3540 begin
3541 --
3542 if p_data = p_condition then
3543 --
3544 o_circle := c_circle;
3545 --
3546 end if;
3547 --
3548 return o_circle;
3549 end set_circle;
3550 --
3551 -- -------------------------------------------------------------------------
3552 -- concatenate
3553 -- -------------------------------------------------------------------------
3554 function concatenate(
3555 p_str1 in varchar2,
3556 p_str2 in varchar2)
3557 return varchar2
3558 is
3559 begin
3560 --
3561 if p_str1 is null then
3562 --
3563 return p_str2;
3564 --
3565 elsif p_str2 is null then
3566 --
3567 return p_str1;
3568 --
3569 else
3570 --
3571 return p_str1||', '||p_str2;
3572 --
3573 end if;
3574 --
3575 end concatenate;
3576 --
3577 -- -------------------------------------------------------------------------
3578 -- set_detail_debug
3579 -- -------------------------------------------------------------------------
3580 procedure set_detail_debug(
3581 p_parm_name in varchar2,
3582 p_yn in varchar2)
3583 is
3584 --
3585 -- hidden option for tracking
3586 cursor csr_hidden_debug
3587 is
3588 select parameter_value
3589 from pay_action_parameters
3590 where parameter_name = p_parm_name;
3591 --
3592 l_hidden_debug pay_action_parameters.parameter_value%type;
3593 --
3594 begin
3595 --
3596 if p_yn is not null then
3597 --
3598 pay_jp_report_pkg.g_detail_debug := p_yn;
3599 --
3600 else
3601 --
3602 open csr_hidden_debug;
3603 fetch csr_hidden_debug into l_hidden_debug;
3604 close csr_hidden_debug;
3605 --
3606 if l_hidden_debug = 'Y' then
3607 --
3608 pay_jp_report_pkg.g_detail_debug := l_hidden_debug;
3609 --
3610 end if;
3611 --
3612 end if;
3613 --
3614 if g_debug then
3615 --
3616 hr_utility.trace('l_hidden_debug : '||l_hidden_debug);
3617 hr_utility.trace('g_detail_debug : '||pay_jp_report_pkg.g_detail_debug);
3618 --
3619 end if;
3620 --
3621 end set_detail_debug;
3622 --
3623 END PAY_JP_REPORT_PKG;