[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_REPORT_PKG
Source
1 PACKAGE BODY PAY_JP_REPORT_PKG AS
2 /* $Header: pyjprep.pkb 120.7.12000000.2 2007/05/21 08:20:55 keyazawa noship $ */
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 --------------------------------------------------------------
46 -- INSERT_SESSION_DATE --
47 --------------------------------------------------------------
48 PROCEDURE INSERT_SESSION_DATE(
49 P_EFFECTIVE_DATE IN DATE)
50 IS
51 BEGIN
52 delete_session_date;
53
54 insert into fnd_sessions(session_id,effective_date)
55 select userenv('sessionid'),trunc(p_effective_date)
56 from dual;
57 END INSERT_SESSION_DATE;
58
59 --------------------------------------------------------------
60 -- DELETE_SESSION_DATE --
61 --------------------------------------------------------------
62 PROCEDURE DELETE_SESSION_DATE
63 IS
64 BEGIN
65 delete from fnd_sessions where session_id=userenv('sessionid');
66 END DELETE_SESSION_DATE;
67
68 -----------------------------------------------------
69 -- TO_ERA --
70 -----------------------------------------------------
71 PROCEDURE TO_ERA( p_date IN DATE,
72 p_era_code OUT NOCOPY NUMBER,
73 p_year OUT NOCOPY NUMBER,
74 p_month OUT NOCOPY NUMBER,
75 p_day OUT NOCOPY NUMBER)
76 IS
77 BEGIN
78 if p_date between g_meiji_from and g_meiji_to then
79 p_era_code := '1';
80 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_meiji_from,'YYYY'))+1;
81 p_month := to_number(to_char(p_date,'MM'));
82 p_day := to_number(to_char(p_date,'DD'));
83 elsif p_date between g_taishou_from and g_taishou_to then
84 p_era_code := '3';
85 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_taishou_from,'YYYY'))+1;
86 p_month := to_number(to_char(p_date,'MM'));
87 p_day := to_number(to_char(p_date,'DD'));
88 elsif p_date between g_shouwa_from and g_shouwa_to then
89 p_era_code := '5';
90 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_shouwa_from,'YYYY'))+1;
91 p_month := to_number(to_char(p_date,'MM'));
92 p_day := to_number(to_char(p_date,'DD'));
93 elsif p_date between g_heisei_from and g_heisei_to then
94 p_era_code := '7';
95 p_year := to_number(to_char(p_date,'YYYY'))-to_number(to_char(g_heisei_from,'YYYY'))+1;
96 p_month := to_number(to_char(p_date,'MM'));
97 p_day := to_number(to_char(p_date,'DD'));
98 else
99 p_era_code := NULL;
100 p_year := NULL;
101 p_month := NULL;
102 p_day := NULL;
103 end if;
104 END TO_ERA;
105
106 -----------------------------------------------------
107 -- GET_CONCATENATED_NUMBERS --
108 -----------------------------------------------------
109 FUNCTION get_concatenated_numbers(
110 p_number1 IN NUMBER,
111 p_number2 IN NUMBER,
112 p_number3 IN NUMBER,
113 p_number4 IN NUMBER,
114 p_number5 IN NUMBER,
115 p_number6 IN NUMBER,
116 p_number7 IN NUMBER,
117 p_number8 IN NUMBER,
118 p_number9 IN NUMBER,
119 p_number10 IN NUMBER) RETURN VARCHAR2
120 IS
121 l_concatenated_numbers VARCHAR2(150);
122 BEGIN
123 l_concatenated_numbers:=NULL;
124
125 if p_number1 is not NULL then
126 if l_concatenated_numbers is NULL then
127 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number1);
128 else
129 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number1);
130 end if;
131 end if;
132 if p_number2 is not NULL then
133 if l_concatenated_numbers is NULL then
134 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number2);
135 else
136 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number2);
137 end if;
138 end if;
139 if p_number3 is not NULL then
140 if l_concatenated_numbers is NULL then
141 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number3);
142 else
143 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number3);
144 end if;
145 end if;
146 if p_number4 is not NULL then
147 if l_concatenated_numbers is NULL then
148 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number4);
149 else
150 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number4);
151 end if;
152 end if;
153 if p_number5 is not NULL then
154 if l_concatenated_numbers is NULL then
155 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number5);
156 else
157 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number5);
158 end if;
159 end if;
160 if p_number6 is not NULL then
161 if l_concatenated_numbers is NULL then
162 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number6);
163 else
164 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number6);
165 end if;
166 end if;
167 if p_number7 is not NULL then
168 if l_concatenated_numbers is NULL then
169 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number7);
170 else
171 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number7);
172 end if;
173 end if;
174 if p_number8 is not NULL then
175 if l_concatenated_numbers is NULL then
176 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number8);
177 else
178 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number8);
179 end if;
180 end if;
181 if p_number9 is not NULL then
182 if l_concatenated_numbers is NULL then
183 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number9);
184 else
185 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number9);
186 end if;
187 end if;
188 if p_number10 is not NULL then
189 if l_concatenated_numbers is NULL then
190 l_concatenated_numbers:=fnd_number.number_to_canonical(p_number10);
191 else
192 l_concatenated_numbers:=l_concatenated_numbers || ',' || fnd_number.number_to_canonical(p_number10);
193 end if;
194 end if;
195
196 return l_concatenated_numbers;
197 END get_concatenated_numbers;
198
199 -----------------------------------------------------
200 -- GET_CONCATENATED_DEPENDENTS --
201 -----------------------------------------------------
202 FUNCTION get_concatenated_dependents(
203 p_person_id IN NUMBER,
204 p_effective_date IN DATE,
205 p_kanji_flag IN VARCHAR2
206 ) RETURN VARCHAR2
207 IS
208 l_first_flag BOOLEAN := TRUE;
209 l_terminator VARCHAR2(10);
210 l_description VARCHAR2(2000);
211 --
212 CURSOR csr_dependent IS
213 -- Pay attention that can't rename the column name of view
214 select pp.last_name EE_LAST_NAME_KANA,
215 pp.per_information18 EE_LAST_NAME,
216 pcon.last_name CON_LAST_NAME_KANA,
217 pcon.first_name CON_FIRST_NAME_KANA,
218 pcon.per_information18 CON_LAST_NAME,
219 pcon.per_information19 CON_FIRST_NAME,
220 decode(pcon.sex,'M',1,'F',2,3) SEX_ORDER,
221 pcon.date_of_birth DATE_OF_BIRTH,
222 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,
223 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
224 from
225 hr_lookups flv2,
226 hr_lookups flv1,
227 per_all_people_f pcon,
228 per_contact_relationships pcr,
229 per_all_people_f pp
230 where pp.person_id=p_person_id
231 and p_effective_date
232 between pp.effective_start_date and pp.effective_end_date
233 and pcr.person_id=pp.person_id
234 and pcr.dependent_flag='Y'
235 and p_effective_date
236 between pcr.date_start and nvl(pcr.date_end,to_date('4712-12-31','YYYY-MM-DD'))
237 and pcon.person_id=pcr.contact_person_id
238 and ( (p_effective_date
239 between pcon.effective_start_date and pcon.effective_end_date)
240 or (not exists(
241 select NULL
242 from per_all_people_f pcon2
243 where pcon2.person_id=pcon.person_id
244 and p_effective_date
245 between pcon2.effective_start_date and pcon2.effective_end_date)
246 and pcon.effective_start_date=pcon.start_date))
247 and flv1.lookup_type='CONTACT'
248 and flv1.lookup_code=pcr.contact_type
249 and flv2.lookup_type(+)='JP_CONTACT_KANA'
250 and flv2.lookup_code(+)=pcr.contact_type
251 order by 8,7,3,4;
252 BEGIN
253 l_description := NULL;
254
255 for l_rec_dependent in csr_dependent loop
256 if l_first_flag then
257 l_terminator := '';
258 l_first_flag := FALSE;
259 else
260 l_terminator := ',';
261 end if;
262
263 if nvl(p_kanji_flag,'1') = '1' then
264 if l_rec_dependent.ee_last_name <> l_rec_dependent.con_last_name
265 or l_rec_dependent.ee_last_name_kana <> l_rec_dependent.con_last_name_kana then
266 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);
267 else
268 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type || ' ' || l_rec_dependent.con_first_name,1,2000);
269 end if;
270 else
271 if l_rec_dependent.ee_last_name <> l_rec_dependent.con_last_name
272 or l_rec_dependent.ee_last_name_kana <> l_rec_dependent.con_last_name_kana then
273 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);
274 else
275 l_description := substrb(l_description || l_terminator || l_rec_dependent.contact_type_kana || ' ' || l_rec_dependent.con_first_name_kana,1,2000);
276 end if;
277 end if;
278 end loop;
279
280 return l_description;
281 END get_concatenated_dependents;
282
283 -----------------------------------------------------
284 FUNCTION convert2(
285 -----------------------------------------------------
286 str IN VARCHAR2,
287 dest_set IN VARCHAR2) RETURN VARCHAR2
288 IS
289 l_value VARCHAR2(2000);
290 BEGIN
291 l_value := convert(str,dest_set);
292
293 return l_value;
294 END convert2;
295
296 -----------------------------------------------------
297 FUNCTION substrb2(
298 -----------------------------------------------------
299 str IN VARCHAR2,
300 pos IN NUMBER,
301 len IN NUMBER) RETURN VARCHAR2
302 IS
303 l_value VARCHAR2(2000);
304 BEGIN
305 l_value := substrb(str,pos,len);
306
307 return l_value;
308 END substrb2;
309
310 -----------------------------------------------------
311 FUNCTION substr2(
312 -----------------------------------------------------
313 str IN VARCHAR2,
314 pos IN NUMBER,
315 len IN NUMBER) RETURN VARCHAR2
316 IS
317 l_value VARCHAR2(2000);
318 BEGIN
319 l_value := substr(str,pos,len);
320
321 return l_value;
322 END substr2;
323
324 -----------------------------------------------------
325 -- DYNAMIC_SQL --
326 -----------------------------------------------------
327 PROCEDURE dynamic_sql(
328 p_sql_statement IN VARCHAR2,
329 p_bind_variables IN g_tab_bind_variables,
330 p_column_names IN g_tab_column_names)
331 IS
332 i INTEGER;
333 j INTEGER;
334 l_cursor_id INTEGER;
335 l_dummy_integer INTEGER;
336 l_dummy_varchar2 VARCHAR2(255);
337 BEGIN
338 l_cursor_id:=dbms_sql.open_cursor;
339 dbms_sql.parse(l_cursor_id,p_sql_statement,dbms_sql.v7);
340
341 BEGIN
342 i:=0;
343 LOOP
344 i:=i+1;
345 if p_bind_variables(i).datatype='NUMBER' then
346 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,to_number(p_bind_variables(i).value));
347 elsif p_bind_variables(i).datatype='VARCHAR2' then
348 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,p_bind_variables(i).value);
349 elsif p_bind_variables(i).datatype='DATE' then
350 dbms_sql.bind_variable(l_cursor_id,':' || p_bind_variables(i).name,fnd_date.canonical_to_date(p_bind_variables(i).value));
351 end if;
352 END LOOP;
353 EXCEPTION
354 WHEN OTHERS THEN
355 i:=i-1;
356 NULL;
357 END;
358
359 BEGIN
360 j:=0;
361 LOOP
362 j:=j+1;
363 l_dummy_varchar2:=p_column_names(j);
364 dbms_sql.define_column(l_cursor_id,j,l_dummy_varchar2,255);
365 END LOOP;
366 EXCEPTION
367 WHEN OTHERS THEN
368 j:=j-1;
369 NULL;
370 END;
371
372 l_dummy_integer:=dbms_sql.execute(l_cursor_id);
373 loop
374 -- dbms_output.put_line(rpad('=',30,'='));
375 l_dummy_integer:=dbms_sql.fetch_rows(l_cursor_id);
376 exit when l_dummy_integer<>1;
377 for i in 1..j loop
378 dbms_sql.column_value(l_cursor_id,i,l_dummy_varchar2);
379 -- dbms_output.put_line(rpad(p_column_names(i),30,' ') || ' = ' || l_dummy_varchar2);
380 end loop;
381 end loop;
382
383 dbms_sql.close_cursor(l_cursor_id);
384 EXCEPTION
385 when others then
386 -- dbms_output.put_line(SQLERRM);
387 dbms_sql.close_cursor(l_cursor_id);
388 END dynamic_sql;
389
390 -----------------------------------------------------
391 -- SET_SPACE_ON_ADDRESSS --
392 -----------------------------------------------------
393 -- This part is out of scope for seed conversion.
394 FUNCTION SET_SPACE_ON_ADDRESS(
395 p_address IN VARCHAR2,
396 p_district_name IN VARCHAR2,
397 p_kana_flag IN NUMBER) RETURN VARCHAR2
398 IS
399 l_text varchar2(80) := p_address;
400 l_district_name varchar2(80) := p_district_name;
401 l_prefecture_name varchar2(80) := replace(l_text,p_district_name);
402
403 BEGIN
404 if l_text is not NULL then
405 if p_kana_flag = 1 then
406 l_district_name := replace(l_district_name,
407 hr_jp_standard_pkg.sjhextochar('B8DEDD'),
408 hr_jp_standard_pkg.sjhextochar('B8DEDD20'));
409 l_district_name := replace(l_district_name,
410 hr_jp_standard_pkg.sjhextochar('20CFB8DEDD20'),
411 hr_jp_standard_pkg.sjhextochar('CFB8DEDD20'));
412 l_district_name := replace(l_district_name,
413 hr_jp_standard_pkg.sjhextochar('20B8DEDD20'),
414 hr_jp_standard_pkg.sjhextochar('20B8DEDD'));
415 --SEIREI SHITEI TOSHI 12
416 l_district_name := replace(l_district_name,
417 hr_jp_standard_pkg.sjhextochar('C1CADEBC'),
418 hr_jp_standard_pkg.sjhextochar('C1CADEBC20'));
419 l_district_name := replace(l_district_name,
420 hr_jp_standard_pkg.sjhextochar('D6BACACFBC'),
421 hr_jp_standard_pkg.sjhextochar('D6BACACFBC20'));
422 l_district_name := replace(l_district_name,
423 hr_jp_standard_pkg.sjhextochar('C5BADED4BC'),
424 hr_jp_standard_pkg.sjhextochar('C5BADED4BC20'));
425 l_district_name := replace(l_district_name,
426 hr_jp_standard_pkg.sjhextochar('BBAFCEDFDBBC'),
427 hr_jp_standard_pkg.sjhextochar('BBAFCEDFDBBC20'));
428 l_district_name := replace(l_district_name,
429 hr_jp_standard_pkg.sjhextochar('BEDDC0DEB2BC'),
430 hr_jp_standard_pkg.sjhextochar('BEDDC0DEB2BC20'));
431 l_district_name := replace(l_district_name,
432 hr_jp_standard_pkg.sjhextochar('BBB2C0CFBC'),
433 hr_jp_standard_pkg.sjhextochar('BBB2C0CFBC20'));
434 l_district_name := replace(l_district_name,
435 hr_jp_standard_pkg.sjhextochar('BCBDDEB5B6BC'),
436 hr_jp_standard_pkg.sjhextochar('BCBDDEB5B6BC20'));
437 l_district_name := replace(l_district_name,
438 hr_jp_standard_pkg.sjhextochar('B7AEB3C4BC'),
439 hr_jp_standard_pkg.sjhextochar('B7AEB3C4BC20'));
440 l_district_name := replace(l_district_name,
441 hr_jp_standard_pkg.sjhextochar('B5B5BBB6BC'),
442 hr_jp_standard_pkg.sjhextochar('B5B5BBB6BC20'));
443 l_district_name := replace(l_district_name,
444 hr_jp_standard_pkg.sjhextochar('BAB3CDDEBC'),
445 hr_jp_standard_pkg.sjhextochar('BAB3CDDEBC20'));
446 l_district_name := replace(l_district_name,
447 hr_jp_standard_pkg.sjhextochar('CBDBBCCFBC'),
448 hr_jp_standard_pkg.sjhextochar('CBDBBCCFBC20'));
449 l_district_name := replace(l_district_name,
450 hr_jp_standard_pkg.sjhextochar('B7C0B7ADB3BCADB3BC'),
451 hr_jp_standard_pkg.sjhextochar('B7C0B7ADB3BCADB3BC20'));
452 l_district_name := replace(l_district_name,
453 hr_jp_standard_pkg.sjhextochar('CCB8B5B6BC'),
454 hr_jp_standard_pkg.sjhextochar('CCB8B5B6BC20'));
455 l_district_name := replace(l_district_name,
456 hr_jp_standard_pkg.sjhextochar('B5B5BCCFB5B5BCCFCFC1'),
457 hr_jp_standard_pkg.sjhextochar('B5B5BCCF20B5B5BCCFCFC1'));
458 --
459 l_district_name := replace(l_district_name,
460 hr_jp_standard_pkg.sjhextochar('C4BCCFC4BCCFD1D7'),
461 hr_jp_standard_pkg.sjhextochar('C4BCCF20C4BCCFD1D7'));
462 l_district_name := replace(l_district_name,
463 hr_jp_standard_pkg.sjhextochar('C6B2BCDECFC6B2BCDECFD1D7'),
464 hr_jp_standard_pkg.sjhextochar('C6B2BCDECF20C6B2BCDECFD1D7'));
465 l_district_name := replace(l_district_name,
466 hr_jp_standard_pkg.sjhextochar('BAB3BDDEBCCFBAB3C2DEBCCFD1D7'),
467 hr_jp_standard_pkg.sjhextochar('BAB3BDDEBCCF20BAB3C2DEBCCFD1D7'));
468 l_district_name := replace(l_district_name,
469 hr_jp_standard_pkg.sjhextochar('D0D4B9BCDECFD0D4B9D1D7'),
470 hr_jp_standard_pkg.sjhextochar('D0D4B9BCDECF20D0D4B9D1D7'));
471 l_district_name := replace(l_district_name,
472 hr_jp_standard_pkg.sjhextochar('D0B8D7BCDECFD0B8D7BCDECFD1D7'),
473 hr_jp_standard_pkg.sjhextochar('D0B8D7BCDECF20D0B8D7BCDECFD1D7'));
474 l_district_name := replace(l_district_name,
475 hr_jp_standard_pkg.sjhextochar('CAC1BCDED6B3BCDECFCAC1BCDED6B3CFC1'),
476 hr_jp_standard_pkg.sjhextochar('CAC1BCDED6B3BCDECF20CAC1BCDED6B3CFC1'));
477 l_district_name := replace(l_district_name,
478 hr_jp_standard_pkg.sjhextochar('B1B5B6DEBCCFB1B5B6DEBCCFD1D7'),
479 hr_jp_standard_pkg.sjhextochar('B1B5B6DEBCCF20B1B5B6DEBCCFD1D7'));
480
481 l_district_name := replace(l_district_name,
482 hr_jp_standard_pkg.sjhextochar('B5B6DEBBDCD7BCAEC4B3B5B6DEBBDCD7D1D7'),
483 hr_jp_standard_pkg.sjhextochar('B5B6DEBBDCD7BCAEC4B320B5B6DEBBDCD7D1D7'));
484 l_text := l_prefecture_name||' '||l_district_name;
485 else
486 l_text := replace(l_text,
487 hr_jp_standard_pkg.sjhextochar('8C53'),
488 hr_jp_standard_pkg.sjhextochar('8C538140'));
489 l_text := replace(l_text,
490 hr_jp_standard_pkg.sjhextochar('8E73'),
491 hr_jp_standard_pkg.sjhextochar('8E738140'));
492 l_text := replace(l_text,
493 hr_jp_standard_pkg.sjhextochar('8CA7'),
494 hr_jp_standard_pkg.sjhextochar('8CA78140'));
495 --
496 l_text := replace(l_text,
497 hr_jp_standard_pkg.sjhextochar('966B8A4393B9'),
498 hr_jp_standard_pkg.sjhextochar('966B8A4393B98140'));
499 l_text := replace(l_text,
500 hr_jp_standard_pkg.sjhextochar('938C8B9E9373'),
501 hr_jp_standard_pkg.sjhextochar('938C8B9E93738140'));
502 l_text := replace(l_text,
503 hr_jp_standard_pkg.sjhextochar('8B9E9373957B'),
504 hr_jp_standard_pkg.sjhextochar('8B9E9373957B8140'));
505 l_text := replace(l_text,
506 hr_jp_standard_pkg.sjhextochar('91E58DE3957B'),
507 hr_jp_standard_pkg.sjhextochar('91E58DE3957B8140'));
508 --
509 l_text := replace(l_text,
510 hr_jp_standard_pkg.sjhextochar('8C5381408C538140'),
511 hr_jp_standard_pkg.sjhextochar('8C5381408C53'));
512 l_text := replace(l_text,
513 hr_jp_standard_pkg.sjhextochar('8C5381408E738140'),
514 hr_jp_standard_pkg.sjhextochar('8C5381408E73'));
515
516 --case of GIFU or FUKUSIMA
517 if substrb(l_text,1,6) = hr_jp_standard_pkg.sjhextochar('8AF2958C8CA7')
518 or substrb(l_text,1,6) = hr_jp_standard_pkg.sjhextochar('959F93878CA7') then
519 l_text := replace(l_text,
520 hr_jp_standard_pkg.sjhextochar('8CA781408C538140'),
521 hr_jp_standard_pkg.sjhextochar('8CA781408C53'));
522 else
523 l_text := replace(l_text,
524 hr_jp_standard_pkg.sjhextochar('8CA781408C538140'),
525 hr_jp_standard_pkg.sjhextochar('8CA78C538140'));
526 end if;
527 l_text := replace(l_text,
528 hr_jp_standard_pkg.sjhextochar('8CA781408E738140'),
529 hr_jp_standard_pkg.sjhextochar('8CA781408E73'));
530 l_text := replace(l_text,
531 hr_jp_standard_pkg.sjhextochar('8E7381408C538140'),
532 hr_jp_standard_pkg.sjhextochar('8E738C538140'));
533 l_text := replace(l_text,
534 hr_jp_standard_pkg.sjhextochar('94AA93FA8E7381408FEA8E73'),
535 hr_jp_standard_pkg.sjhextochar('94AA93FA8E738FEA8E73'));
536 l_text := replace(l_text,
537 hr_jp_standard_pkg.sjhextochar('91E598618C5381408E528E73'),
538 hr_jp_standard_pkg.sjhextochar('91E598618C538E528E73'));
539 l_text := replace(l_text,
540 hr_jp_standard_pkg.sjhextochar('938C8E738140978892AC'),
541 hr_jp_standard_pkg.sjhextochar('938C8E73978892AC'));
542 --
543 l_text := replace(l_text,
544 hr_jp_standard_pkg.sjhextochar('91E5938791E5938792AC'),
545 hr_jp_standard_pkg.sjhextochar('91E59387814091E5938792AC'));
546 l_text := replace(l_text,
547 hr_jp_standard_pkg.sjhextochar('979893879798938791BA'),
548 hr_jp_standard_pkg.sjhextochar('9798938781409798938791BA'));
549 l_text := replace(l_text,
550 hr_jp_standard_pkg.sjhextochar('905693879056938791BA'),
551 hr_jp_standard_pkg.sjhextochar('9056938781409056938791BA'));
552 l_text := replace(l_text,
553 hr_jp_standard_pkg.sjhextochar('905F92C39387905F92C3938791BA'),
554 hr_jp_standard_pkg.sjhextochar('905F92C393878140905F92C3938791BA'));
555 l_text := replace(l_text,
556 hr_jp_standard_pkg.sjhextochar('8E4F91EE93878E4F91EE91BA'),
557 hr_jp_standard_pkg.sjhextochar('8E4F91EE938781408E4F91EE91BA'));
558 l_text := replace(l_text,
559 hr_jp_standard_pkg.sjhextochar('8CE491A093878CE491A0938791BA'),
560 hr_jp_standard_pkg.sjhextochar('8CE491A0938781408CE491A0938791BA'));
561 l_text := replace(l_text,
562 hr_jp_standard_pkg.sjhextochar('94AA8FE4938794AA8FE492AC'),
563 hr_jp_standard_pkg.sjhextochar('94AA8FE49387814094AA8FE492AC'));
564 l_text := replace(l_text,
565 hr_jp_standard_pkg.sjhextochar('90C28350938790C28350938791BA'),
566 hr_jp_standard_pkg.sjhextochar('90C283509387814090C28350938791BA'));
567 l_text := replace(l_text,
568 hr_jp_standard_pkg.sjhextochar('8FAC8A7D8CB48F9493878FAC8A7D8CB491BA'),
569 hr_jp_standard_pkg.sjhextochar('8FAC8A7D8CB48F94938781408FAC8A7D8CB491BA'));
570 end if;
571 end if;
572 return l_text;
573 END set_space_on_address;
574
575 -----------------------------------------------------
576 -- GET_MAX_VALUE --
577 -----------------------------------------------------
578 FUNCTION GET_MAX_VALUE(
579 p_user_table_name IN VARCHAR2,
580 p_udt_column_name IN VARCHAR2,
581 p_effective_date IN DATE ) RETURN NUMBER
582 IS
583 l_value number := null;
584 CURSOR cur_max_value IS
585 select max(to_number(value))
586 from pay_user_tables put,
587 pay_user_columns puc,
588 pay_user_column_instances_f puci
589 where put.user_table_name = p_user_table_name
590 and puc.user_table_id = put.user_table_id
591 and puc.user_column_name = p_udt_column_name
592 and puci.user_column_id = puc.user_column_id
593 and p_effective_date
594 between puci.effective_start_date and puci.effective_end_date;
595 BEGIN
596 open cur_max_value;
597 fetch cur_max_value into l_value;
598 close cur_max_value;
599
600 return l_value;
601 END get_max_value;
602
603 -----------------------------------------------------
604 -- GET_MIN_VALUE --
605 -----------------------------------------------------
606 FUNCTION GET_MIN_VALUE(
607 p_user_table_name IN VARCHAR2,
608 p_udt_column_name IN VARCHAR2,
609 p_effective_date IN DATE ) RETURN NUMBER
610 IS
611 l_value number := null;
612 CURSOR cur_min_value IS
613 select min(to_number(value))
614 from pay_user_tables put,
615 pay_user_columns puc,
616 pay_user_column_instances_f puci
617 where put.user_table_name = p_user_table_name
618 and puc.user_table_id = put.user_table_id
619 and puc.user_column_name = p_udt_column_name
620 and puci.user_column_id = puc.user_column_id
621 and p_effective_date
622 between puci.effective_start_date and puci.effective_end_date;
623 BEGIN
624 open cur_min_value;
625 fetch cur_min_value into l_value;
626 close cur_min_value;
627
628 return l_value;
629 END get_min_value;
630
631 -----------------------------------------------------
632 -- SJTOJIS --
633 -----------------------------------------------------
634 FUNCTION sjtojis(p_src IN VARCHAR2) RETURN VARCHAR2
635 IS
636 l_jis VARCHAR2(2000) := '';
637 l_src VARCHAR2(4);
638 l_b1 VARCHAR2(2);
639 l_b2 VARCHAR2(2);
640 l_b3 VARCHAR2(2);
641 l_b4 VARCHAR2(2);
642 l_kanji NUMBER := 0;
643 l_ank NUMBER := 0;
644
645 BEGIN
646 if length(p_src) is null then
647 return NULL;
648 end if;
649
650 for i in 1.. length(p_src) loop
651 l_src := hr_jp_standard_pkg.chartohex(substr(p_src,i,1),'JA16SJIS');
652 if length(l_src) = 2 then --1byte character
653 if l_kanji = 1 and l_ank = 0 then
654 l_ank := 1;
655 l_kanji := 0;
656 -- escape sequence KO (kanji out, ANK shift code "ESC(H")
657 l_jis := l_jis || '1B2848';
658 end if;
659 l_jis := l_jis || l_src;
660 else --2byte caracter
661 l_b1 := substr(l_src,1,1);
662 l_b2 := substr(l_src,2,1);
663 l_b3 := substr(l_src,3,1);
664 l_b4 := substr(l_src,4,1);
665
666 -- hex A..F -> 10..15
667 if to_number(hr_jp_standard_pkg.chartohex(l_b1,'JA16SJIS')) >= 41 then
668 l_b1 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b1,'JA16SJIS'))) - 31;
669 end if;
670 if to_number(hr_jp_standard_pkg.chartohex(l_b2,'JA16SJIS')) >= 41 then
671 l_b2 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b2,'JA16SJIS'))) - 31;
672 end if;
673 if to_number(hr_jp_standard_pkg.chartohex(l_b3,'JA16SJIS')) >= 41 then
674 l_b3 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b3,'JA16SJIS'))) - 31;
675 end if;
676 if to_number(hr_jp_standard_pkg.chartohex(l_b4,'JA16SJIS')) >= 41 then
677 l_b4 := to_char(to_number(hr_jp_standard_pkg.chartohex(l_b4,'JA16SJIS'))) - 31;
678 end if;
679
680 -- if 1byte >= 0xE0 then 1byte := 1byte - 0x40;
681 if to_number(l_b1) >= 14 then
682 l_b1 := to_char(to_number(l_b1) - 4);
683 end if;
684 -- if 2byte >= 0x80 then 2byte := 2byte - 0x01;
685 if to_number(l_b3) >= 8 then
686 if to_number(l_b4) < 1 then
687 l_b3 := to_char(to_number(l_b3) - 1);
688 l_b4 := '15';
689 else
690 l_b4 := to_char(to_number(l_b4) - 1);
691 end if;
692 end if;
693 -- if 2byte >= 0x9E then 1byte := (1byte - 0x70) * 2, 2byte := 2byte - 0x7D;
694 -- else 1byte := ((1byte - 0x70) * 2) - 1, 2byte := 2byte - 0x1F;
695 if to_number(l_b3 || lpad(l_b4,2,'0')) >= 914 then
696 l_b1 := to_char((to_number(l_b1) - 7) * 2);
697 l_b2 := to_char(to_number(l_b2) * 2);
698 if to_number(l_b2) > 15 then
699 l_b1 := to_char(to_number(l_b1) + 1);
700 l_b2 := to_char(to_number(l_b2) - 16);
701 end if;
702 if to_number(l_b4) < 13 then
703 l_b3 := to_char(to_number(l_b3) - 8);
704 l_b4 := to_char(to_number(l_b4) + 3);
705 else
706 l_b3 := to_char(to_number(l_b3) - 7);
707 l_b4 := to_char(to_number(l_b4) - 13);
708 end if;
709 else
710 if to_number(l_b2) < 1 then
711 l_b1 := to_char(((to_number(l_b1) - 7) * 2) - 1);
712 l_b2 := '15';
713 else
714 l_b1 := to_char((to_number(l_b1) - 7) * 2);
715 l_b2 := to_char((to_number(l_b2) * 2) - 1);
716 if to_number(l_b2) > 15 then
717 l_b1 := to_char(to_number(l_b1) + 1);
718 l_b2 := to_char(to_number(l_b2) - 16);
719 end if;
720 end if;
721 if to_number(l_b4) < 15 then
722 l_b3 := to_char(to_number(l_b3) - 2);
723 l_b4 := to_char(to_number(l_b4) + 1);
724 else
725 l_b3 := to_char(to_number(l_b3) - 1);
726 l_b4 := to_char(to_number(l_b4) - 15);
727 end if;
728 end if;
729
730 if l_kanji = 0 then
731 l_kanji := 1;
732 l_ank := 0;
733 -- escape sequence KI (kanji in, KANJI shift code "ESC$@")
734 l_jis := l_jis || '1B2440';
735 end if;
736
737 if to_number(l_b1) > 9 then
738 l_b1 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b1)+31));
739 end if;
740 if to_number(l_b2) > 9 then
741 l_b2 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b2)+31));
742 end if;
743 if to_number(l_b3) > 9 then
744 l_b3 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b3)+31));
745 end if;
746 if to_number(l_b4) > 9 then
747 l_b4 := hr_jp_standard_pkg.sjhextochar(to_char(to_number(l_b4)+31));
748 end if;
749
750 l_jis := l_jis || l_b1 || l_b2 || l_b3 || l_b4;
751 end if;
752 end loop;
753
754 if l_kanji = 1 and l_ank = 0 then
755 -- escape sequence KO (kanji out, ANK shift code "ESC(H")
756 l_jis := l_jis || '1B2848';
757 end if;
758
759 return hr_jp_standard_pkg.sjhextochar(l_jis);
760 -- dbms_output.put_line(l_jis);
761 END sjtojis;
762 -----------------------------------------------------
763 -- ELIGIBLE_FOR_SUBMISSION --
764 -----------------------------------------------------
765 FUNCTION eligible_for_submission (
766 p_year IN NUMBER,
767 p_itax_yea_category IN VARCHAR2,
768 p_gross_taxable_amt IN NUMBER,
769 p_taxable_amt IN NUMBER,
770 p_prev_swot_taxable_amt IN NUMBER,
771 p_executive_flag IN VARCHAR2,
772 p_itax_category IN VARCHAR2
773 ) RETURN VARCHAR2
774 IS
775 l_eligible_flag VARCHAR2(1) := 'N';
776 l_taxable_amt_total NUMBER;
777 l_prev_swot_taxable_amt NUMBER := 0;
778 BEGIN
779 if p_year between 0 and 4712 then
780 -- Total salary doesn't depend on YEA status.
781 if p_itax_yea_category is not NULL then
782 l_taxable_amt_total := p_gross_taxable_amt;
783 else
784 l_taxable_amt_total := p_taxable_amt
785 + l_prev_swot_taxable_amt;
786 end if;
787
788 -- In case YEA is processed.
789 if p_itax_yea_category = '0' then
790 if p_executive_flag = 'Y' then
791 if l_taxable_amt_total > 1500000 then
792 l_eligible_flag := 'Y';
793 end if;
794 else
795 if l_taxable_amt_total > 5000000 then
796 l_eligible_flag := 'Y';
797 end if;
798 end if;
799 else
800 if p_itax_category in ('M_KOU', 'D_KOU') then
801 if p_executive_flag = 'Y' then
802 if l_taxable_amt_total > 500000 then
803 l_eligible_flag := 'Y';
804 end if;
805 else
806 if l_taxable_amt_total > 2500000 then
807 l_eligible_flag := 'Y';
808 end if;
809 end if;
810 else
811 if l_taxable_amt_total > 500000 then
812 l_eligible_flag := 'Y';
813 end if;
814 end if;
815 end if;
816 end if;
817 return l_eligible_flag;
818 END eligible_for_submission;
819 --
820 -----------------------------------------------------
821 -- GET_PREV_SWOT_INFO --
822 -----------------------------------------------------
823 FUNCTION get_prev_swot_info (
824 p_business_group_id in NUMBER,
825 p_assignment_id in NUMBER,
826 p_year in NUMBER,
827 p_itax_organization_id in NUMBER,
828 p_swot_iv_id in NUMBER,
829 p_action_sequence in NUMBER,
830 p_kanji_flag in VARCHAR2,
831 p_media_type in VARCHAR2) RETURN VARCHAR2
832 IS
833 cursor csr_prev_swot_1 is
834 select v1.business_group_id,
835 v1.itax_organization_id,
836 v1.effective_date,
837 v1.date_earned,
838 v1.assignment_id,
839 v1.action_sequence
840 from pay_jp_pre_itax_v1 v1
841 where v1.business_group_id = p_business_group_id
842 and to_char(v1.effective_date, 'YYYY') = p_year
843 and v1.assignment_id = p_assignment_id
844 --
845 and v1.itax_organization_id <> p_itax_organization_id
846 and v1.action_sequence < p_action_sequence
847 order by v1.date_earned desc;
848 --
849 cursor csr_prev_swot_2(cp_itax_organization_id NUMBER, cp_action_sequence NUMBER) is
850 select /* Removed the hint as per Bug# 4767108 */
851 nvl(sum(decode(pai.action_information13, 'TERM',
852 NULL, decode(pai.action_information21, cp_itax_organization_id,
853 pai.action_information2 + pai.action_information3, NULL ))),0) PREV_SWOT_TAXABLE_AMT,
854 nvl(sum(decode(pai.action_information13, 'TERM',
855 NULL, decode(pai.action_information21, cp_itax_organization_id,
856 pai.action_information24 + pai.action_information25, NULL))),0) PREV_SWOT_ITAX,
857 nvl(sum(decode(pai.action_information13, 'TERM',
858 NULL, decode(pai.action_information21, cp_itax_organization_id,
859 pai.action_information6 + pai.action_information9 + pai.action_information12 + pai.action_information20 + pai.action_information14, NULL))),0) PREV_SWOT_SI_PREM,
860 nvl(sum(decode(pai.action_information13, 'TERM',
861 NULL, decode(pai.action_information21, cp_itax_organization_id, pai.action_information14, NULL))), 0) PREV_SWOT_MUTUAL_AID
862 from pay_assignment_actions paa,
863 pay_payroll_actions ppa,
864 pay_action_information pai,
865 per_all_assignments_f pa
866 where paa.assignment_id = p_assignment_id
867 /* Below conditions have already been taken care in Pre-Tax Archiver
868 process. So they are redundant here and removed.
869 for Bug# 5033800 */
870 -- and paa.action_status = 'C'
871 -- and ppa.action_type in ('R', 'Q', 'B', 'I')
872 /* Below conditions were removed, as they are redundant ones.
873 for Bug# 5033800 */
874 -- and pai.action_context_type = 'AAP'
875 -- and pai.assignment_id = pass.assignment_id
876 and ppa.payroll_action_id = paa.payroll_action_id
877 and to_char(ppa.effective_date, 'YYYY') = p_year
878 and pai.action_information_category = 'JP_PRE_TAX_1'
879 and pai.action_information1 = paa.assignment_action_id
880 and ((pai.action_information13 in ('SALARY', 'BONUS', 'SP_BONUS', 'YEA', 'RE_YEA')
881 and paa.action_sequence <= cp_action_sequence)
882 or
883 (pai.action_information13 = 'TERM'))
884 and pai.action_information22 in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU', 'D_HEI')
885 and pa.assignment_id = paa.assignment_id
886 and ppa.effective_date between pa.effective_start_date and pa.effective_end_date
887 --
888 and not exists(
889 select NULL /* Removed the hint as per Bug# 5033800 */
890 from pay_action_interlocks pai2,
891 pay_assignment_actions paa2,
892 pay_payroll_actions ppa2
893 where pai2.locked_action_id = paa.assignment_action_id
894 and paa2.assignment_action_id = pai2.locking_action_id
895 and ppa2.payroll_action_id = paa2.payroll_action_id
896 and ppa2.action_type = 'V');
897 --
898 -- /* Join peev before pee to avoid merge join cartesian */
899 cursor csr_prev_swot(cp_date_earned DATE, cp_itax_organization_id NUMBER) is
900 select /* Removed the hint as per Bug# 4767108 */
901 decode(p_kanji_flag,
902 '1',hoi.org_information1,hoi.org_information2) EMPLOYER_NAME,
903 pay_jp_report_pkg.substrb2(
904 decode(p_kanji_flag,
905 '1',hoi.org_information6||hoi.org_information7||hoi.org_information8,
906 hoi.org_information9||hoi.org_information10||hoi.org_information11),1,255) EMPLOYER_ADDRESS,
907 peev.effective_end_date PREV_SWOT_TERM_DATE
908 from hr_organization_information hoi,
909 pay_element_entry_values_f peev,
910 pay_element_entries_f pee
911 where hoi.organization_id(+) = cp_itax_organization_id
912 and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
913 -- Previous SWOT term date
914 and cp_date_earned between peev.effective_start_date and peev.effective_end_date
915 and peev.input_value_id = p_swot_iv_id
916 and peev.screen_entry_value = hoi.organization_id
917 and pee.element_entry_id = peev.element_entry_id
918 and pee.assignment_id = p_assignment_id
919 and pee.effective_start_date = peev.effective_start_date
920 and pee.effective_end_date = peev.effective_end_date;
921
922 l_prev_swot_rec csr_prev_swot%ROWTYPE;
923
924 l_description varchar2(2000);
925 l_prev_term_era_code NUMBER;
926 l_prev_term_year NUMBER;
927 l_prev_term_month NUMBER;
928 l_prev_term_day NUMBER;
929 l_prev_taxable_amt VARCHAR2(255);
930 l_prev_itax VARCHAR2(255);
931 l_prev_si_prem VARCHAR2(255);
932 l_prev_mutual_aid VARCHAR2(255);
933 l_prev_add VARCHAR2(255);
934 l_prev_name VARCHAR2(255);
935 l_prev_term VARCHAR2(255);
936 l_prev_add_id_for_file VARCHAR2(10) := NULL;
937 l_prev_name_id_for_file VARCHAR2(10) := NULL;
938 l_prev_term_id_for_file VARCHAR2(10) := NULL;
939
940 BEGIN
941
942 l_description := NULL;
943
944 if nvl(p_media_type,'NULL') <> 'NULL' then
945 l_prev_add_id_for_file := 'P_ADDRESS';
946 l_prev_name_id_for_file := 'P_NAME';
947 l_prev_term_id_for_file := 'P_TERM';
948 end if;
949
950 for l_prev_swot_1_rec in csr_prev_swot_1 loop
951 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
952 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
953
954 l_prev_taxable_amt := NULL;
955 l_prev_itax := NULL;
956 l_prev_si_prem := NULL;
957 l_prev_mutual_aid := NULL;
958 l_prev_add := NULL;
959 l_prev_name := NULL;
960 l_prev_term := NULL;
961
962 pay_jp_report_pkg.to_era(l_prev_swot_rec.prev_swot_term_date,
963 l_prev_term_era_code,
964 l_prev_term_year,
965 l_prev_term_month,
966 l_prev_term_day);
967 l_prev_term_year := l_prev_term_year - trunc(l_prev_term_year,-2);
968
969 if not (l_prev_swot_2_rec.prev_swot_taxable_amt = 0
970 and l_prev_swot_2_rec.prev_swot_itax = 0
971 and l_prev_swot_2_rec.prev_swot_si_prem = 0) then
972
973 if P_KANJI_FLAG = '1' then -- Kanji
974 if (l_description is NULL) then
975 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
976 else
977 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
978 end if;
979 l_prev_taxable_amt := fnd_message.get_string('PAY','PAY_JP_SALARY')
980 || to_char(l_prev_swot_2_rec.prev_swot_taxable_amt)
981 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
982 l_prev_si_prem := ',' ||fnd_message.get_string('PAY','PAY_JP_TRANS_SI')
983 || to_char(l_prev_swot_2_rec.prev_swot_si_prem)
984 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
985 if (l_prev_swot_2_rec.prev_swot_mutual_aid is not NULL) then
986 l_prev_mutual_aid := '(' ||fnd_message.get_string('PAY','PAY_JP_WITHIN')
987 || to_char(l_prev_swot_2_rec.prev_swot_mutual_aid)
988 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX') ||')';
989 end if;
990 l_prev_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX')
991 || to_char(l_prev_swot_2_rec.prev_swot_itax)
992 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
993 if nvl(l_prev_swot_rec.employer_address,' ') <> ' ' then
994 l_prev_add := ',' || l_prev_swot_rec.employer_address;
995 end if;
996 if nvl(l_prev_swot_rec.employer_name,' ') <> ' ' then
997 l_prev_name := ',' || l_prev_swot_rec.employer_name;
998 end if;
999 if (l_prev_swot_rec.prev_swot_term_date is not NULL) then
1000 l_prev_term := ','
1001 || lpad(to_char(l_prev_term_year),2,'0') || fnd_message.get_string('PER','HR_JP_YY')
1002 || lpad(to_char(l_prev_term_month),2,'0') || fnd_message.get_string('PER','HR_JP_MM')
1003 || lpad(to_char(l_prev_term_day),2,'0') || fnd_message.get_string('PER','HR_JP_DD')
1004 || fnd_message.get_string('PAY','PAY_JP_TERM');
1005 end if;
1006 else -- Kana
1007 if (l_description is NULL) then
1008 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1009 else
1010 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1011 end if;
1012 l_prev_taxable_amt := fnd_message.get_string('PAY','PAY_JP_TRANS_SALARY_KANA')
1013 || to_char(l_prev_swot_2_rec.prev_swot_taxable_amt)
1014 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1015 l_prev_si_prem := ',' || fnd_message.get_string('PAY','PAY_JP_TRANS_SI_KANA')
1016 || to_char(l_prev_swot_2_rec.prev_swot_si_prem)
1017 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1018 if (l_prev_swot_2_rec.prev_swot_mutual_aid is not NULL) then
1019 l_prev_mutual_aid := '(' ||fnd_message.get_string('PAY','PAY_JP_WITHIN_KANA')
1020 || to_char(l_prev_swot_2_rec.prev_swot_mutual_aid)
1021 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA') || ')';
1022 end if;
1023 l_prev_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX_KANA')
1024 || to_char(l_prev_swot_2_rec.prev_swot_itax)
1025 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1026 if nvl(l_prev_swot_rec.employer_address,' ') <> ' ' then
1027 l_prev_add := ',' || l_prev_swot_rec.employer_address;
1028 end if;
1029 if nvl(l_prev_swot_rec.employer_name,' ') <> ' ' then
1030 l_prev_name := ',' || l_prev_swot_rec.employer_name;
1031 end if;
1032 if (l_prev_swot_rec.prev_swot_term_date is not NULL) then
1033 l_prev_term := ','
1034 || lpad(to_char(l_prev_term_year),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_YY_KANA')
1035 || lpad(to_char(l_prev_term_month),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_MM_KANA')
1036 || lpad(to_char(l_prev_term_day),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_DD_KANA')
1037 || fnd_message.get_string('PAY','PAY_JP_TRANS_TERM_KANA');
1038 end if;
1039 end if;
1040 l_description := pay_jp_report_pkg.substrb2(l_description
1041 || l_prev_taxable_amt || l_prev_si_prem
1042 || l_prev_mutual_aid || l_prev_itax
1043 || l_prev_add_id_for_file || l_prev_add
1044 || l_prev_name_id_for_file || l_prev_name
1045 || l_prev_term_id_for_file || l_prev_term,1,2000);
1046 end if;
1047
1048 end loop;
1049 end loop;
1050 end loop;
1051 return l_description;
1052
1053 END get_prev_swot_info;
1054
1055 -----------------------------------------------------
1056 -- GET_PJOB_INFO --
1057 -----------------------------------------------------
1058 FUNCTION get_pjob_info (
1059 p_assignment_id in NUMBER,
1060 p_effective_date in DATE,
1061 p_business_group_id in NUMBER,
1062 p_pjob_ele_type_id in NUMBER,
1063 p_taxable_amt_iv_id in NUMBER,
1064 p_si_prem_iv_id in NUMBER,
1065 p_mutual_aid_iv_id in NUMBER,
1066 p_itax_iv_id in NUMBER,
1067 p_term_date_iv_id in NUMBER,
1068 p_addr_iv_id in NUMBER,
1069 p_employer_name_iv_id in NUMBER,
1070 p_kanji_flag in VARCHAR2,
1071 p_media_type in VARCHAR2) RETURN VARCHAR2
1072 IS
1073
1074 cursor csr_get_entry_values is
1075 select /*+ ORDERED
1076 NO_MERGE(entry_type_v)
1077 INDEX(TAXABLE_AMT PAY_ELEMENT_ENTRY_VALUES_F_N50)
1078 INDEX(ITAX PAY_ELEMENT_ENTRY_VALUES_F_N50)
1079 INDEX(SI_PREM PAY_ELEMENT_ENTRY_VALUES_F_N50)
1080 INDEX(MUTUAL_AID PAY_ELEMENT_ENTRY_VALUES_F_N50)
1081 INDEX(TERM_DATE PAY_ELEMENT_ENTRY_VALUES_F_N50)
1082 INDEX(ADDR PAY_ELEMENT_ENTRY_VALUES_F_N50)
1083 INDEX(EMPLOYER_NAME PAY_ELEMENT_ENTRY_VALUES_F_N50) */
1084 nvl(taxable_amt.screen_entry_value,0) PJOB_TAXABLE_AMT,
1085 nvl(itax.screen_entry_value,0) PJOB_ITAX,
1086 nvl(si_prem.screen_entry_value,0) PJOB_SI_PREM,
1087 nvl(mutual_aid.screen_entry_value,0) PJOB_MUTUAL_AID,
1088 fnd_date.canonical_to_date(term_date.screen_entry_value) PJOB_TERM_DATE,
1089 addr.screen_entry_value PJOB_ADDR,
1090 employer_name.screen_entry_value PJOB_EMPLOYER_NAME
1091 from (select /*+ ORDERED
1092 INDEX(PETF PAY_ELEMENT_TYPES_F_PK)
1093 INDEX(PELF PAY_ELEMENT_LINKS_F_N7)
1094 INDEX(PEEF PAY_ELEMENT_ENTRIES_F_N51) */
1095 peef.element_entry_id
1096 from pay_element_types_f petf,
1097 pay_element_links_f pelf,
1098 pay_element_entries_f peef
1099 where petf.element_type_id = p_pjob_ele_type_id
1100 and pelf.element_type_id = petf.element_type_id
1101 and pelf.business_group_id +0 = p_business_group_id
1102 and peef.element_link_id = pelf.element_link_id
1103 and peef.assignment_id = p_assignment_id) entry_type_v,
1104 pay_element_entry_values_f taxable_amt,
1105 pay_element_entry_values_f itax,
1106 pay_element_entry_values_f si_prem,
1107 pay_element_entry_values_f mutual_aid,
1108 pay_element_entry_values_f term_date,
1109 pay_element_entry_values_f addr,
1110 pay_element_entry_values_f employer_name
1111 where taxable_amt.element_entry_id = entry_type_v.element_entry_id
1112 and taxable_amt.input_value_id = p_taxable_amt_iv_id
1113 and p_effective_date
1114 between taxable_amt.effective_start_date and taxable_amt.effective_end_date
1115 and itax.element_entry_id = entry_type_v.element_entry_id
1116 and itax.input_value_id = p_itax_iv_id
1117 and p_effective_date
1118 between itax.effective_start_date and itax.effective_end_date
1119 and si_prem.element_entry_id = entry_type_v.element_entry_id
1120 and si_prem.input_value_id = p_si_prem_iv_id
1121 and p_effective_date
1122 between si_prem.effective_start_date and si_prem.effective_end_date
1123 and mutual_aid.element_entry_id = entry_type_v.element_entry_id
1124 and mutual_aid.input_value_id = p_mutual_aid_iv_id
1125 and p_effective_date
1126 between mutual_aid.effective_start_date and mutual_aid.effective_end_date
1127 and term_date.element_entry_id = entry_type_v.element_entry_id
1128 and term_date.input_value_id = p_term_date_iv_id
1129 and p_effective_date
1130 between term_date.effective_start_date and term_date.effective_end_date
1131 and addr.element_entry_id = entry_type_v.element_entry_id
1132 and addr.input_value_id = p_addr_iv_id
1133 and p_effective_date
1134 between addr.effective_start_date and addr.effective_end_date
1135 and employer_name.element_entry_id = entry_type_v.element_entry_id
1136 and employer_name.input_value_id = p_employer_name_iv_id
1137 and p_effective_date
1138 between employer_name.effective_start_date and employer_name.effective_end_date
1139 order by pjob_term_date desc;
1140
1141 l_get_entry_values_rec csr_get_entry_values%ROWTYPE;
1142
1143 l_description VARCHAR2(2000);
1144 l_pjob_taxable_amt VARCHAR2(255);
1145 l_pjob_itax VARCHAR2(255);
1146 l_pjob_si_prem VARCHAR2(255);
1147 l_pjob_mutual_aid VARCHAR2(255);
1148 l_pjob_term_date VARCHAR2(255);
1149 l_pjob_addr VARCHAR2(255);
1150 l_pjob_employer_name VARCHAR2(255);
1151
1152 l_year NUMBER;
1153 l_month NUMBER;
1154 l_day NUMBER;
1155 l_era_code NUMBER;
1156
1157 l_prev_add_id_for_file VARCHAR2(10) := NULL;
1158 l_prev_name_id_for_file VARCHAR2(10) := NULL;
1159 l_prev_term_id_for_file VARCHAR2(10) := NULL;
1160
1161 BEGIN
1162 l_description := NULL;
1163
1164 if nvl(p_media_type,'NULL') <> 'NULL' then
1165 l_prev_add_id_for_file := 'P_ADDRESS';
1166 l_prev_name_id_for_file := 'P_NAME';
1167 l_prev_term_id_for_file := 'P_TERM';
1168 end if;
1169
1170 for l_get_entry_values_rec in csr_get_entry_values loop
1171 l_pjob_taxable_amt := NULL;
1172 l_pjob_si_prem := NULL;
1173 l_pjob_mutual_aid := NULL;
1174 l_pjob_itax := NULL;
1175 l_pjob_term_date := NULL;
1176 l_pjob_addr := NULL;
1177 l_pjob_employer_name := NULL;
1178
1179 pay_jp_report_pkg.to_era(l_get_entry_values_rec.pjob_term_date,
1180 l_era_code,
1181 l_year,
1182 l_month,
1183 l_day);
1184 l_year := l_year - trunc(l_year,-2);
1185
1186 if not (l_get_entry_values_rec.pjob_taxable_amt = 0
1187 and l_get_entry_values_rec.pjob_si_prem = 0
1188 and l_get_entry_values_rec.pjob_itax = 0) then
1189 if p_kanji_flag = '1' then
1190 if (l_description is NULL) then
1191 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
1192 else
1193 l_description := l_description || ',' ||fnd_message.get_string('PAY','PAY_JP_PREVIOUS_EMPLOYMENT');
1194 end if;
1195 l_pjob_taxable_amt := fnd_message.get_string('PAY','PAY_JP_SALARY')
1196 || l_get_entry_values_rec.pjob_taxable_amt
1197 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1198 l_pjob_si_prem := ','||fnd_message.get_string('PAY','PAY_JP_TRANS_SI')
1199 || l_get_entry_values_rec.pjob_si_prem
1200 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1201 if (l_get_entry_values_rec.pjob_mutual_aid is not NULL) then
1202 l_pjob_mutual_aid := '('||fnd_message.get_string('PAY','PAY_JP_WITHIN')
1203 || l_get_entry_values_rec.pjob_mutual_aid
1204 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX')||')';
1205 end if;
1206 l_pjob_itax := ','||fnd_message.get_string('PAY','PAY_JP_TAX')
1207 || l_get_entry_values_rec.pjob_itax
1208 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX');
1209 if nvl(l_get_entry_values_rec.pjob_addr, ' ') <> ' ' then
1210 l_pjob_addr := ',' || l_get_entry_values_rec.pjob_addr;
1211 end if;
1212 if nvl(l_get_entry_values_rec.pjob_employer_name, ' ') <> ' ' then
1213 l_pjob_employer_name := ',' || l_get_entry_values_rec.pjob_employer_name;
1214 end if;
1215 if (l_get_entry_values_rec.pjob_term_date is not NULL) then
1216 l_pjob_term_date := ','
1217 || lpad(to_char(l_year),2,'0') || fnd_message.get_string('PER','HR_JP_YY')
1218 || lpad(to_char(l_month),2,'0') || fnd_message.get_string('PER','HR_JP_MM')
1219 || lpad(to_char(l_day),2,'0') || fnd_message.get_string('PER','HR_JP_DD')
1220 || fnd_message.get_string('PAY','PAY_JP_TERM');
1221 end if;
1222 else
1223 if (l_description is NULL) then
1224 l_description := l_description || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1225 else
1226 l_description := l_description || ',' || fnd_message.get_string('PAY','PAY_JP_PREV_EMPLOYMENT_KANA');
1227 end if;
1228 l_pjob_taxable_amt := fnd_message.get_string('PAY','PAY_JP_TRANS_SALARY_KANA')
1229 || l_get_entry_values_rec.pjob_taxable_amt
1230 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1231 l_pjob_si_prem := ',' || fnd_message.get_string('PAY','PAY_JP_TRANS_SI_KANA')
1232 || l_get_entry_values_rec.pjob_si_prem
1233 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1234 if (l_get_entry_values_rec.pjob_mutual_aid is not NULL) then
1235 l_pjob_mutual_aid := '('||fnd_message.get_string('PAY','PAY_JP_WITHIN_KANA')
1236 || l_get_entry_values_rec.pjob_mutual_aid
1237 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA') ||')';
1238 end if;
1239 l_pjob_itax := ',' ||fnd_message.get_string('PAY','PAY_JP_TAX_KANA')
1240 || l_get_entry_values_rec.pjob_itax
1241 || fnd_message.get_string('PAY','PAY_JP_JBA_MONEY_SUFFIX_KANA');
1242 if nvl(l_get_entry_values_rec.pjob_addr, ' ') <> ' ' then
1243 l_pjob_addr := ',' || l_get_entry_values_rec.pjob_addr;
1244 end if;
1245 if nvl(l_get_entry_values_rec.pjob_employer_name, ' ') <> ' ' then
1246 l_pjob_employer_name := ',' || l_get_entry_values_rec.pjob_employer_name;
1247 end if;
1248 if (l_get_entry_values_rec.pjob_term_date is not NULL) then
1249 l_pjob_term_date := ','
1250 || lpad(to_char(l_year),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_YY_KANA')
1251 || lpad(to_char(l_month),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_MM_KANA')
1252 || lpad(to_char(l_day),2,'0') || fnd_message.get_string('PAY','PAY_JP_TRANS_DD_KANA')
1253 || fnd_message.get_string('PAY','PAY_JP_TRANS_TERM_KANA');
1254 end if;
1255 end if;
1256 l_description := pay_jp_report_pkg.substrb2(l_description
1257 || l_pjob_taxable_amt || l_pjob_si_prem
1258 || l_pjob_mutual_aid || l_pjob_itax
1259 || l_prev_add_id_for_file || l_pjob_addr
1260 || l_prev_name_id_for_file || l_pjob_employer_name
1261 || l_prev_term_id_for_file || l_pjob_term_date,1,2000);
1262 end if;
1263
1264 end loop;
1265
1266 return l_description;
1267
1268 END get_pjob_info;
1269 --
1270 -----------------------------------------------------
1271 -- CONVERT_TO_WTM_FORMAT --
1272 -----------------------------------------------------
1273 -- This part is out of scope for seed conversion.
1274 FUNCTION convert_to_wtm_format(
1275 p_text IN VARCHAR2,
1276 p_kanji_flag IN VARCHAR2,
1277 p_media_type IN VARCHAR2
1278 ) RETURN VARCHAR2
1279 IS
1280 l_text VARCHAR2(4000) := ltrim(rtrim(substrb(p_text,1,2000)));
1281 BEGIN
1282 if nvl(l_text,' ') = ' ' then
1283 return l_text;
1284 end if;
1285
1286 if nvl(p_media_type,'MT') <> 'MT' then
1287 l_text := replace(l_text,',','');
1288 end if;
1289 if nvl(p_kanji_flag,'1') = '0' then
1290 -- Translate KANA 2 byte to 1 byte
1291 l_text := translate(l_text,
1292 hr_jp_standard_pkg.sjhextochar('83418343834583478349834A834C834E83508352835483568358835A835C835E83608363836583678369836A836B836C836D836E837183748377837A837D837E8380838183828384838683888389838A838B838C838D838F83928393'),
1293 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCA6DD'));
1294
1295 -- for voiced sound
1296 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834B'),hr_jp_standard_pkg.sjhextochar('B6DE'));
1297 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834D'),hr_jp_standard_pkg.sjhextochar('B7DE'));
1298 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('834F'),hr_jp_standard_pkg.sjhextochar('B8DE'));
1299 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8351'),hr_jp_standard_pkg.sjhextochar('B9DE'));
1300 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8353'),hr_jp_standard_pkg.sjhextochar('BADE'));
1301 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8355'),hr_jp_standard_pkg.sjhextochar('BBDE'));
1302 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8357'),hr_jp_standard_pkg.sjhextochar('BCDE'));
1303 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8359'),hr_jp_standard_pkg.sjhextochar('BDDE'));
1304 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835B'),hr_jp_standard_pkg.sjhextochar('BEDE'));
1305 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835D'),hr_jp_standard_pkg.sjhextochar('BFDE'));
1306 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('835F'),hr_jp_standard_pkg.sjhextochar('C0DE'));
1307 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8361'),hr_jp_standard_pkg.sjhextochar('C1DE'));
1308 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8364'),hr_jp_standard_pkg.sjhextochar('C2DE'));
1309 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8366'),hr_jp_standard_pkg.sjhextochar('C3DE'));
1310 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8368'),hr_jp_standard_pkg.sjhextochar('C4DE'));
1311 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('836F'),hr_jp_standard_pkg.sjhextochar('CADE'));
1312 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8370'),hr_jp_standard_pkg.sjhextochar('CADF'));
1313 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8372'),hr_jp_standard_pkg.sjhextochar('CBDE'));
1314 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8373'),hr_jp_standard_pkg.sjhextochar('CBDF'));
1315 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8375'),hr_jp_standard_pkg.sjhextochar('CCDE'));
1316 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8376'),hr_jp_standard_pkg.sjhextochar('CCDF'));
1317 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8378'),hr_jp_standard_pkg.sjhextochar('CDDE'));
1318 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('8379'),hr_jp_standard_pkg.sjhextochar('CDDF'));
1319 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('837B'),hr_jp_standard_pkg.sjhextochar('CEDE'));
1320 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('837C'),hr_jp_standard_pkg.sjhextochar('CEDF'));
1321
1322 -- for double consonant and so on
1323 l_text := translate(l_text,
1324 hr_jp_standard_pkg.sjhextochar('834083428344834683488383838583878362A7A8A9AAABACADAEAF'),
1325 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5D4D5D6C2B1B2B3B4B5D4D5D6C2'));
1326
1327 -- for others
1328 l_text := translate(l_text,
1329 hr_jp_standard_pkg.sjhextochar('81428175817681418145815B'),
1330 hr_jp_standard_pkg.sjhextochar('A1A2A3A4A5B0'));
1331
1332 -- for space
1333 l_text := translate(l_text,hr_jp_standard_pkg.sjhextochar('8140'),' ');
1334
1335 else
1336 -- Translate 1 byte to 2 byte
1337 l_text := to_multi_byte(l_text);
1338
1339 -- for voiced sound and so on
1340 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B6DE'),hr_jp_standard_pkg.sjhextochar('834B'));
1341 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B7DE'),hr_jp_standard_pkg.sjhextochar('834D'));
1342 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B8DE'),hr_jp_standard_pkg.sjhextochar('834F'));
1343 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('B9DE'),hr_jp_standard_pkg.sjhextochar('8351'));
1344 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BADE'),hr_jp_standard_pkg.sjhextochar('8353'));
1345 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BBDE'),hr_jp_standard_pkg.sjhextochar('8355'));
1346 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BCDE'),hr_jp_standard_pkg.sjhextochar('8357'));
1347 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BDDE'),hr_jp_standard_pkg.sjhextochar('8359'));
1348 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BEDE'),hr_jp_standard_pkg.sjhextochar('835B'));
1349 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('BFDE'),hr_jp_standard_pkg.sjhextochar('835D'));
1350 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C0DE'),hr_jp_standard_pkg.sjhextochar('835F'));
1351 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C1DE'),hr_jp_standard_pkg.sjhextochar('8361'));
1352 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C2DE'),hr_jp_standard_pkg.sjhextochar('8364'));
1353 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C3DE'),hr_jp_standard_pkg.sjhextochar('8366'));
1354 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('C4DE'),hr_jp_standard_pkg.sjhextochar('8368'));
1355 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CADE'),hr_jp_standard_pkg.sjhextochar('836F'));
1356 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CADF'),hr_jp_standard_pkg.sjhextochar('8370'));
1357 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CBDE'),hr_jp_standard_pkg.sjhextochar('8372'));
1358 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CBDF'),hr_jp_standard_pkg.sjhextochar('8373'));
1359 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CCDE'),hr_jp_standard_pkg.sjhextochar('8375'));
1360 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CCDF'),hr_jp_standard_pkg.sjhextochar('8376'));
1361 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CDDE'),hr_jp_standard_pkg.sjhextochar('8378'));
1362 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CDDF'),hr_jp_standard_pkg.sjhextochar('8379'));
1363 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CEDE'),hr_jp_standard_pkg.sjhextochar('837B'));
1364 l_text := replace(l_text,hr_jp_standard_pkg.sjhextochar('CEDF'),hr_jp_standard_pkg.sjhextochar('837C'));
1365
1366 -- for KANA
1367 l_text := translate(l_text,
1368 hr_jp_standard_pkg.sjhextochar('B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCA6DD'),
1369 hr_jp_standard_pkg.sjhextochar('83418343834583478349834A834C834E83508352835483568358835A835C835E83608363836583678369836A836B836C836D836E837183748377837A837D837E8380838183828384838683888389838A838B838C838D838F83928393'));
1370
1371 -- for double consonant and so on
1372 l_text := translate(l_text,
1373 hr_jp_standard_pkg.sjhextochar('A7A8A9AAABACADAEAF'),
1374 hr_jp_standard_pkg.sjhextochar('834083428344834683488383838583878362'));
1375
1376 -- for others
1377 l_text := translate(l_text,
1378 hr_jp_standard_pkg.sjhextochar('A1A2A3A4A5B0'),
1379 hr_jp_standard_pkg.sjhextochar('81428175817681418145815B'));
1380
1381 -- for space
1382 l_text := translate(l_text,' ',hr_jp_standard_pkg.sjhextochar('8140'));
1383 end if;
1384
1385
1386 return ltrim(rtrim(substrb(l_text,1,2000)));
1387
1388 END convert_to_wtm_format;
1389 --
1390 FUNCTION get_concatenated_disability(
1391 p_person_id IN NUMBER,
1392 p_effective_date IN DATE) RETURN VARCHAR2 IS
1393 --
1394 CURSOR cel_disability_details IS
1395 SELECT /*+ ORDERED
1396 INDEX(PCR PER_CONTACT_RELATIONSHIPS_N2)
1397 INDEX(PCEIF PER_CONTACT_EXTRA_INFO_N1)
1398 INDEX(PAPF PER_PEOPLE_F_PK) */
1399 DECODE(pceif.cei_information7,
1400 NULL,SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 ||
1401 DECODE(pceif.cei_information6,
1402 '20', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY') || ')',
1403 '30', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER') || ')' , NULL), 1, 2000),
1404 SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 || ' (' || pceif.cei_information7 ||
1405 DECODE(pceif.cei_information6,
1406 '20', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY'),
1407 '30', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER'), NULL) || ')',1,2000)) details
1408 FROM per_contact_relationships pcr,
1409 per_contact_extra_info_f pceif,
1410 per_all_people_f papf
1411 WHERE pcr.person_id = p_person_id
1412 AND pcr.cont_information_category = 'JP'
1413 AND pcr.cont_information1 = 'Y'
1414 AND p_effective_date
1415 BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
1416 AND pceif.contact_relationship_id = pcr.contact_relationship_id
1417 AND pceif.information_type = 'JP_ITAX_DEPENDENT'
1418 AND pceif.cei_information6 <> '0'
1419 AND p_effective_date
1420 BETWEEN pceif.effective_start_date AND pceif.effective_end_date
1421 AND papf.person_id = pcr.contact_person_id
1422 AND p_effective_date
1423 BETWEEN papf.effective_start_date AND papf.effective_end_date
1424 ORDER BY pcr.cont_information2,
1425 papf.date_of_birth;
1426 --
1427 l_first_flag BOOLEAN := TRUE;
1428 l_celrec_disability_details cel_disability_details%ROWTYPE;
1429 l_terminator VARCHAR2(5);
1430 l_disability_details VARCHAR2(2000);
1431 --
1432 BEGIN
1433 --
1434 FOR l_celrec_disability_details IN cel_disability_details LOOP
1435 --
1436 IF l_first_flag THEN
1437 --
1438 l_terminator := '';
1439 l_first_flag := FALSE;
1440 --
1441 ELSE
1442 --
1443 l_terminator := fnd_global.local_chr(10);
1444 --
1445 END IF;
1446 --
1447 l_disability_details := SUBSTRB(l_disability_details || l_terminator || l_celrec_disability_details.details, 1, 2000);
1448 --
1449 END LOOP;
1450 --
1451 RETURN(l_disability_details);
1452 --
1453 END get_concatenated_disability;
1454 --
1455 -- -----------------------------------------------------------------------------
1456 -- get_hi_dependent_exists
1457 -- -----------------------------------------------------------------------------
1458 --
1459 -- This function will be obsolete according to superseded with get_hi_dependent_number.
1460 --
1461 FUNCTION get_hi_dependent_exists(
1462 p_person_id IN NUMBER,
1463 p_effective_date IN DATE)
1464 --
1465 RETURN VARCHAR2 IS
1466 --
1467 l_return VARCHAR2(1);
1468 --
1469 CURSOR cel_hi_dependent_exists
1470 IS
1471 SELECT 'Y'
1472 FROM dual
1473 WHERE EXISTS(
1474 SELECT /*+ ORDERED */
1475 NULL
1476 FROM per_contact_relationships pcr,
1477 per_contact_extra_info_f pceif
1478 WHERE pcr.person_id = p_person_id
1479 AND pcr.cont_information_category = 'JP'
1480 AND pcr.cont_information1 = 'Y'
1481 AND p_effective_date
1482 BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
1483 AND pceif.contact_relationship_id = pcr.contact_relationship_id
1484 AND pceif.information_type LIKE 'JP_HI%'
1485 AND p_effective_date
1486 between pceif.effective_start_date and pceif.effective_end_date
1487 AND p_effective_date
1488 between DECODE(pceif.information_type,
1489 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
1490 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
1491 null)
1492 and nvl(DECODE(pceif.information_type,
1493 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
1494 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
1495 null),pceif.effective_end_date));
1496 --
1497 BEGIN
1498 --
1499 OPEN cel_hi_dependent_exists;
1500 FETCH cel_hi_dependent_exists INTO l_return;
1501 --
1502 IF cel_hi_dependent_exists%NOTFOUND THEN
1503 --
1504 l_return := 'N';
1505 --
1506 END IF;
1507 --
1508 CLOSE cel_hi_dependent_exists;
1509 --
1510 RETURN(l_return);
1511 --
1512 END get_hi_dependent_exists;
1513 --
1514 -- -----------------------------------------------------------------------------
1515 -- get_hi_dependent_number
1516 -- -----------------------------------------------------------------------------
1517 --
1518 function get_hi_dependent_number(
1519 --
1520 p_person_id in number,
1521 p_effective_date in date)
1522 --
1523 return number is
1524 --
1525 l_return number := 0;
1526 --
1527 -- Owing to distinguish contact is qualified/disqualified,
1528 -- It is required to point historical(date track) data on target date.
1529 --
1530 -- eg. 1. Employee HI qualified at 2004/01/01 (Session Date 2004/02/01)
1531 -- Contact HI_SPOUSE DFF
1532 -- QD : 2004/01/01
1533 -- DQD : Null
1534 -- ESD : 2004/02/01
1535 -- EED : 4712/12/31
1536 -- 2. Contact Start work at 2004/03/01 (Session Date 2004/03/01)
1537 -- Contact HI_SPOUSE DFF
1538 -- QD : 2004/01/01 2004/01/01
1539 -- DQD : Null 2004/03/01
1540 -- ESD : 2004/02/01 2004/03/01
1541 -- EED : 2004/02/29 4712/12/31
1542 -- 3. Contact Back family at 2004/04/01 (Session Date 2004/04/01)
1543 -- Contact HI_SPOUSE DFF
1544 -- QD : 2004/01/01 2004/01/01 2004/04/01
1545 -- DQD : Null 2004/03/01 Null
1546 -- ESD : 2004/02/01 2004/03/01 2004/04/01
1547 -- EED : 2004/02/29 2004/03/31 4712/12/31
1548 -- Employee is qualified in January, But Contact is not spouse
1549 -- since there is no data in the period. This is bad operation.
1550 -- Session Date(ESD) should be same or earlier than qualified date.
1551 -- If Employee is disqualified in Feburary, Contact is spouse.
1552 -- If Employee is disqualified in March, Contact is not spouse.
1553 -- If Employee is disqualified in April, Contact is spouse.
1554 --
1555 -- Following are example of coverage.
1556 --
1557 -- p_effective_date = 2004/01/01
1558 --
1559 -- Case1. o Case2. o Case3. x Case4. x Case5. x Case6. x Case7. x
1560 -- ESD 1990/01/01 1990/01/01 1990/01/01 1990/01/01 1990/01/01 2005/01/01 1990/01/01
1561 -- EED 4712/12/31 4712/12/31 4712/12/31 4712/12/31 4712/12/31 4712/12/31 2003/01/01
1562 -- QD 2003/01/01 2003/01/01 N/A N/A 2010/12/31 2003/01/01 2003/01/01
1563 -- DQD 2010/12/31 N/A 2010/12/31 N/A 2003/01/01 2010/12/31 2010/12/31
1564 --
1565 cursor cel_hi_dependent_number
1566 is
1567 select count(pcr.person_id)
1568 from per_contact_relationships pcr
1569 where pcr.person_id = p_person_id
1570 and pcr.cont_information_category = 'JP'
1571 and pcr.cont_information1 = 'Y'
1572 and p_effective_date
1573 between nvl(pcr.date_start, p_effective_date) and nvl(pcr.date_end, p_effective_date)
1574 and exists(
1575 select null
1576 from per_contact_extra_info_f pceif
1577 where pceif.contact_relationship_id = pcr.contact_relationship_id
1578 and p_effective_date
1579 between pceif.effective_start_date and pceif.effective_end_date
1580 and pceif.information_type like 'JP_HI%'
1581 and p_effective_date
1582 between decode(pceif.information_type,
1583 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
1584 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
1585 null)
1586 and nvl(DECODE(pceif.information_type,
1587 'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
1588 'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
1589 null),pceif.effective_end_date));
1590 --
1591 begin
1592 --
1593 open cel_hi_dependent_number;
1594 fetch cel_hi_dependent_number into l_return;
1595 close cel_hi_dependent_number;
1596 --
1597 return(l_return);
1598 --
1599 end get_hi_dependent_number;
1600 --
1601 -- -----------------------------------------------------------------------------
1602 -- chk_use_contact_extra_info
1603 -- -----------------------------------------------------------------------------
1604 function chk_use_contact_extra_info(
1605 p_business_group_id in number)
1606 return varchar2
1607 is
1608 --
1609 l_dpnt_control_method hr_organization_information.org_information1%type;
1610 l_return varchar2(1);
1611 --
1612 cursor csr_get_bg_info
1613 is
1614 select hoi.org_information1
1615 from hr_organization_information hoi
1616 where hoi.organization_id = p_business_group_id
1617 and hoi.org_information_context = 'JP_BUSINESS_GROUP_INFO';
1618 --
1619 begin
1620 --
1621 open csr_get_bg_info;
1622 fetch csr_get_bg_info into l_dpnt_control_method;
1623 close csr_get_bg_info;
1624 --
1625 -- Distinguish if contact relationship data can be used.
1626 --
1627 -- if bg info is null then return N
1628 l_return := 'N';
1629 --
1630 if l_dpnt_control_method = 'CEI' then
1631 l_return := 'Y';
1632 end if;
1633 --
1634 return(l_return);
1635 --
1636 end chk_use_contact_extra_info;
1637 --
1638 FUNCTION get_si_dependent_report_type(
1639 p_person_id per_all_people_f.person_id%TYPE,
1640 p_qualified_date DATE) RETURN NUMBER IS
1641 --
1642 CURSOR cel_added IS
1643 SELECT 1 FROM per_jp_si_dependent_transfer_v
1644 WHERE person_id = p_person_id
1645 AND dependent_type IN ('S', 'D')
1646 AND transfer_type = 'I'
1647 AND TRUNC(transfer_date) <> p_qualified_date;
1648 --
1649 CURSOR cel_hi_removed IS
1650 SELECT 2 FROM per_jp_si_dependent_transfer_v
1651 WHERE person_id = p_person_id
1652 AND dependent_type IN ('S', 'D')
1653 AND transfer_type = 'E';
1654 --
1655 CURSOR cel_np_added IS
1656 SELECT 4 FROM per_jp_si_dependent_transfer_v
1657 WHERE person_id = p_person_id
1658 AND dependent_type = '3'
1659 AND transfer_type = 'I'
1660 AND TRUNC(transfer_date) <> p_qualified_date;
1661 --
1662 CURSOR cel_np_removed IS
1663 SELECT 8 FROM per_jp_si_dependent_transfer_v
1664 WHERE person_id = p_person_id
1665 AND dependent_type = '3'
1666 AND transfer_type = 'E'
1667 AND type3_disqualified_notice = 'Y';
1668 --
1669 l_cursor NUMBER;
1670 l_return NUMBER;
1671 --
1672 BEGIN
1673 --
1674 l_return := 0;
1675 --
1676 OPEN cel_added;
1677 FETCH cel_added INTO l_cursor;
1678 --
1679 IF cel_added%FOUND THEN
1680 --
1681 l_return := l_cursor;
1682 --
1683 END IF;
1684 --
1685 CLOSE cel_added;
1686 --
1687 OPEN cel_hi_removed;
1688 FETCH cel_hi_removed INTO l_cursor;
1689 --
1690 IF cel_hi_removed%FOUND THEN
1691 --
1692 l_return := l_return + l_cursor;
1693 --
1694 END IF;
1695 --
1696 CLOSE cel_hi_removed;
1697 --
1698 OPEN cel_np_added;
1699 FETCH cel_np_added INTO l_cursor;
1700 --
1701 IF cel_np_added%FOUND THEN
1702 --
1703 l_return := l_return + l_cursor;
1704 --
1705 END IF;
1706 --
1707 CLOSE cel_np_added;
1708 --
1709 OPEN cel_np_removed;
1710 FETCH cel_np_removed INTO l_cursor;
1711 --
1712 IF cel_np_removed%FOUND THEN
1713 --
1714 l_return := l_return + l_cursor;
1715 --
1716 END IF;
1717 --
1718 CLOSE cel_np_removed;
1719 --
1720 RETURN(l_return);
1721 --
1722 END get_si_dependent_report_type;
1723 --
1724 FUNCTION get_si_dep_ee_effective_date(
1725 p_person_id per_all_people_f.person_id%TYPE,
1726 p_date_from DATE,
1727 p_date_to DATE,
1728 p_report_type hr_lookups.lookup_code%TYPE) RETURN DATE IS
1729 --
1730 CURSOR cel_max_effective_date IS
1731 SELECT transfer_date
1732 FROM per_jp_si_dependent_transfer_v
1733 WHERE person_id = p_person_id
1734 AND DECODE(transfer_type, 'I', transfer_date, 'E', transfer_date + 1) BETWEEN p_date_from AND p_date_to
1735 AND (p_report_type = '0'
1736 OR (p_report_type = '10'
1737 AND dependent_type IN ('S', 'D'))
1738 OR (p_report_type = '20'
1739 AND dependent_type = '3'))
1740 ORDER BY transfer_date DESC;
1741 --
1742 l_return DATE;
1743 --
1744 BEGIN
1745 --
1746 OPEN cel_max_effective_date;
1747 FETCH cel_max_effective_date INTO l_return;
1748 CLOSE cel_max_effective_date;
1749 --
1750 RETURN(l_return);
1751 --
1752 END get_si_dep_ee_effective_date;
1753 --
1754 -----------------------------------------------------
1755 -- DECODE_ASS_SET_NAME --
1756 -----------------------------------------------------
1757 --
1758 FUNCTION decode_ass_set_name(
1759 p_assignment_set_id in hr_assignment_sets.assignment_set_id%type)
1760 RETURN VARCHAR2
1761 IS
1762 --
1763 l_meaning varchar2(80) := null;
1764 --
1765 cursor csr_ass_set_name
1766 is
1767 select assignment_set_name
1768 from hr_assignment_sets
1769 where assignment_set_id = p_assignment_set_id;
1770 --
1771 BEGIN
1772 --
1773 -- Only open the cursor if the parameter is going to retrieve anything
1774 --
1775 if p_assignment_set_id is not null then
1776 open csr_ass_set_name;
1777 fetch csr_ass_set_name into l_meaning;
1778 close csr_ass_set_name;
1779 end if;
1780 --
1781 return l_meaning;
1782 --
1783 END decode_ass_set_name;
1784 --
1785 function get_si_rec_id(
1786 p_rec_name in varchar2)
1787 return number
1788 is
1789 --
1790 l_elm_id number;
1791 l_rslt_id number;
1792 --
1793 begin
1794 --
1795 if pay_jp_report_pkg.g_legislation_code is null
1796 or pay_jp_report_pkg.g_legislation_code <> c_legislation_code then
1797 --
1798 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1799 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);
1800 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);
1801 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);
1802 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);
1803 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);
1804 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);
1805 --
1806 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1807 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1808 --
1809 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1810 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);
1811 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);
1812 --
1813 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1814 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);
1815 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);
1816 --
1817 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1818 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);
1819 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);
1820 --
1821 pay_jp_report_pkg.g_legislation_code := c_legislation_code;
1822 --
1823 end if;
1824 --
1825 if p_rec_name = 'hi_org_iv_id' then
1826 if pay_jp_report_pkg.g_si_rec.hi_org_iv_id is null then
1827 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1828 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);
1829 end if;
1830 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_org_iv_id;
1831 elsif p_rec_name = 'wp_org_iv_id' then
1832 if pay_jp_report_pkg.g_si_rec.wp_org_iv_id is null then
1833 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1834 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);
1835 end if;
1836 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_org_iv_id;
1837 elsif p_rec_name = 'wpf_org_iv_id' then
1838 if pay_jp_report_pkg.g_si_rec.wpf_org_iv_id is null then
1839 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1840 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);
1841 end if;
1842 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_org_iv_id;
1843 elsif p_rec_name = 'hi_num_iv_id' then
1844 if pay_jp_report_pkg.g_si_rec.hi_num_iv_id is null then
1845 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1846 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);
1847 end if;
1848 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_num_iv_id;
1849 elsif p_rec_name = 'wp_num_iv_id' then
1850 if pay_jp_report_pkg.g_si_rec.wp_num_iv_id is null then
1851 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1852 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);
1853 end if;
1854 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_num_iv_id;
1855 elsif p_rec_name = 'bp_num_iv_id' then
1856 if pay_jp_report_pkg.g_si_rec.bp_num_iv_id is null then
1857 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1858 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);
1859 end if;
1860 l_rslt_id := pay_jp_report_pkg.g_si_rec.bp_num_iv_id;
1861 elsif p_rec_name = 'exc_iv_id' then
1862 if pay_jp_report_pkg.g_si_rec.exc_iv_id is null then
1863 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1864 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1865 end if;
1866 l_rslt_id := pay_jp_report_pkg.g_si_rec.exc_iv_id;
1867 elsif p_rec_name = 'hi_qd_iv_id' then
1868 if pay_jp_report_pkg.g_si_rec.hi_qd_iv_id is null then
1869 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1870 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);
1871 end if;
1872 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_qd_iv_id;
1873 elsif p_rec_name = 'wp_qd_iv_id' then
1874 if pay_jp_report_pkg.g_si_rec.wp_qd_iv_id is null then
1875 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1876 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);
1877 end if;
1878 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_qd_iv_id;
1879 elsif p_rec_name = 'wpf_qd_iv_id' then
1880 if pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id is null then
1881 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1882 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);
1883 end if;
1884 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_qd_iv_id;
1885 elsif p_rec_name = 'hi_dqd_iv_id' then
1886 if pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id is null then
1887 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1888 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);
1889 end if;
1890 l_rslt_id := pay_jp_report_pkg.g_si_rec.hi_dqd_iv_id;
1891 elsif p_rec_name = 'wp_dqd_iv_id' then
1892 if pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id is null then
1893 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1894 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);
1895 end if;
1896 l_rslt_id := pay_jp_report_pkg.g_si_rec.wp_dqd_iv_id;
1897 elsif p_rec_name = 'wpf_dqd_iv_id' then
1898 if pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id is null then
1899 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1900 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);
1901 end if;
1902 l_rslt_id := pay_jp_report_pkg.g_si_rec.wpf_dqd_iv_id;
1903 end if;
1904 --
1905 return l_rslt_id;
1906 end get_si_rec_id;
1907 --
1908 function get_gs_rec_id(
1909 p_rec_name in varchar2)
1910 return number
1911 is
1912 l_ele_set pay_element_sets.element_set_name%type;
1913 l_elm_id number;
1914 l_rslt_id number;
1915 --
1916 cursor csr_ele_set
1917 is
1918 select pes.element_set_id
1919 from pay_element_sets pes
1920 where pes.legislation_code = c_legislation_code
1921 and pes.element_set_name = l_ele_set;
1922 --
1923 begin
1924 --
1925 if pay_jp_report_pkg.g_legislation_code is null
1926 or pay_jp_report_pkg.g_legislation_code <> c_legislation_code then
1927 --
1928 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,-1,c_legislation_code);
1929 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);
1930 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);
1931 --
1932 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,-1,c_legislation_code);
1933 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);
1934 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);
1935 --
1936 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_info_elm,-1,c_legislation_code);
1937 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);
1938 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);
1939 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);
1940 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);
1941 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);
1942 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);
1943 --
1944 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_si_rep_elm,-1,c_legislation_code);
1945 pay_jp_report_pkg.g_si_rec.exc_iv_id := pay_jp_balance_pkg.get_input_value_id(l_elm_id,c_exc_iv);
1946 --
1947 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_q_info_elm,-1,c_legislation_code);
1948 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);
1949 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);
1950 --
1951 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_q_info_elm,-1,c_legislation_code);
1952 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);
1953 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);
1954 --
1955 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wpf_q_info_elm,-1,c_legislation_code);
1956 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);
1957 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);
1958 --
1959 l_ele_set := c_san_ele_set;
1960 open csr_ele_set;
1961 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
1962 close csr_ele_set;
1963 --
1964 l_ele_set := c_gep_ele_set;
1965 open csr_ele_set;
1966 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
1967 close csr_ele_set;
1968 --
1969 l_ele_set := c_iku_ele_set;
1970 open csr_ele_set;
1971 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
1972 close csr_ele_set;
1973 --
1974 pay_jp_report_pkg.g_legislation_code := c_legislation_code;
1975 --
1976 end if;
1977 --
1978 if p_rec_name = 'hi_appl_mth_iv_id' then
1979 if pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id is null then
1980 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,null,c_legislation_code);
1981 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);
1982 end if;
1983 l_rslt_id := pay_jp_report_pkg.g_gs_rec.hi_appl_mth_iv_id;
1984 elsif p_rec_name = 'wp_appl_mth_iv_id' then
1985 if pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id is null then
1986 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,null,c_legislation_code);
1987 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);
1988 end if;
1989 l_rslt_id := pay_jp_report_pkg.g_gs_rec.wp_appl_mth_iv_id;
1990 elsif p_rec_name = 'hi_appl_cat_iv_id' then
1991 if pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id is null then
1992 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_hi_smr_info_elm,null,c_legislation_code);
1993 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);
1994 end if;
1995 l_rslt_id := pay_jp_report_pkg.g_gs_rec.hi_appl_cat_iv_id;
1996 elsif p_rec_name = 'wp_appl_cat_iv_id' then
1997 if pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id is null then
1998 l_elm_id := pay_jp_balance_pkg.get_element_type_id(c_com_wp_smr_info_elm,null,c_legislation_code);
1999 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);
2000 end if;
2001 l_rslt_id := pay_jp_report_pkg.g_gs_rec.wp_appl_cat_iv_id;
2002 elsif p_rec_name = 'san_ele_set_id' then
2003 if pay_jp_report_pkg.g_gs_rec.san_ele_set_id is null then
2004 open csr_ele_set;
2005 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
2006 close csr_ele_set;
2007 end if;
2008 l_rslt_id := pay_jp_report_pkg.g_gs_rec.san_ele_set_id;
2009 elsif p_rec_name = 'gep_ele_set_id' then
2010 if pay_jp_report_pkg.g_gs_rec.gep_ele_set_id is null then
2011 open csr_ele_set;
2012 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
2013 close csr_ele_set;
2014 end if;
2015 l_rslt_id := pay_jp_report_pkg.g_gs_rec.gep_ele_set_id;
2016 elsif p_rec_name = 'iku_ele_set_id' then
2017 if pay_jp_report_pkg.g_gs_rec.iku_ele_set_id is null then
2018 open csr_ele_set;
2019 fetch csr_ele_set into pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
2020 close csr_ele_set;
2021 end if;
2022 l_rslt_id := pay_jp_report_pkg.g_gs_rec.iku_ele_set_id;
2023 end if;
2024 --
2025 return l_rslt_id;
2026 end get_gs_rec_id;
2027 --
2028 function chk_hi_wp(
2029 p_sort_order in varchar2,
2030 p_submit_type in number,
2031 p_si_type in number)
2032 return number
2033 is
2034 --
2035 l_hi_wp number := c_hi_num_sort;
2036 --
2037 begin
2038 --
2039 if p_sort_order = c_wp_number then
2040 --
2041 l_hi_wp := c_wp_num_sort;
2042 --
2043 else
2044 --
2045 if p_submit_type in (3,7) then
2046 --
2047 if p_si_type in (2,6) then
2048 --
2049 l_hi_wp := c_wp_num_sort;
2050 --
2051 end if;
2052 --
2053 end if;
2054 --
2055 end if;
2056 --
2057 return l_hi_wp;
2058 end chk_hi_wp;
2059 --
2060 procedure get_latest_std_mth_comp_info(
2061 p_assignment_id in number,
2062 p_effective_date in date,
2063 p_date_earned in date,
2064 p_applied_mth_iv_id in number,
2065 p_new_std_mth_comp_iv_id in number,
2066 p_old_std_mth_comp_iv_id in number,
2067 p_latest_applied_date out nocopy date,
2068 p_latest_std_mth_comp out nocopy varchar2)
2069 is
2070 --
2071 /* Limitation: This logic does not check whether employee is qualified at the past time. */
2072 /* Only related to current status of qualification. */
2073 /* Include updating entry and new entry as qualification. */
2074 cursor csr_past_std_mth_comp is
2075 select /*+ ORDERED
2076 USE_NL(PLIV1, PLIV2, PEE, PEEV1, PEEV2)
2077 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV1)
2078 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV2)
2079 INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
2080 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV1)
2081 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV2) */
2082 pee.element_entry_id ee_id,
2083 pee.effective_start_date ee_esd,
2084 pee.effective_end_date ee_eed,
2085 peev1.screen_entry_value applied_mth,
2086 peev2.screen_entry_value new_std_mth_comp
2087 from pay_link_input_values_f pliv1,
2088 pay_link_input_values_f pliv2,
2089 pay_element_entries_f pee,
2090 pay_element_entry_values_f peev1,
2091 pay_element_entry_values_f peev2
2092 where pliv1.input_value_id = p_applied_mth_iv_id
2093 and pliv2.input_value_id = p_new_std_mth_comp_iv_id
2094 and pee.assignment_id = p_assignment_id
2095 /* use not eed but esd to include entry data as qualification */
2096 /* DBItem Entry is referred by date earned, */
2097 /* but if update recurring has been occurred, */
2098 /* all future entry are updating from effective date. */
2099 /* therefore, don't need to include future entry till date earned. */
2100 and pee.entry_type = 'E'
2101 and pee.effective_start_date < p_effective_date
2102 and pee.element_link_id = pliv1.element_link_id
2103 and pee.element_link_id = pliv2.element_link_id
2104 and pee.effective_start_date
2105 between pliv1.effective_start_date and pliv1.effective_end_date
2106 and pee.effective_start_date
2107 between pliv2.effective_start_date and pliv2.effective_end_date
2108 and peev1.element_entry_id = pee.element_entry_id
2109 and peev1.input_value_id = pliv1.input_value_id
2110 and peev1.effective_start_date = pee.effective_start_date
2111 and peev1.effective_end_date = pee.effective_end_date
2112 and peev2.element_entry_id = pee.element_entry_id
2113 and peev2.input_value_id = pliv2.input_value_id
2114 and peev2.effective_start_date = pee.effective_start_date
2115 and peev2.effective_end_date = pee.effective_end_date
2116 order by pee.effective_start_date desc;
2117 --
2118 l_csr_past_std_mth_comp csr_past_std_mth_comp%rowtype;
2119 l_applied_mth_one_day_before varchar2(60);
2120 l_applied_mth varchar2(60);
2121 l_applied_mth_old varchar2(60);
2122 l_std_mth_comp_old varchar2(60);
2123 --
2124 begin
2125 --
2126 l_applied_mth := pay_jp_balance_pkg.get_entry_value_char(p_applied_mth_iv_id,p_assignment_id,p_effective_date);
2127 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);
2128 --
2129 if trunc(to_date(nvl(l_applied_mth_one_day_before,'000101')||'01','YYYYMMDD'),'MM')
2130 < trunc(p_date_earned,'MM') then
2131 l_applied_mth_old := l_applied_mth_one_day_before;
2132 if nvl(l_applied_mth,'000101') <> nvl(l_applied_mth_one_day_before,'000101') then
2133 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);
2134 else
2135 if l_applied_mth is not null and l_applied_mth_one_day_before is not null then
2136 /* This case is for entry that is not updated(process) ie. qualificaiton data */
2137 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);
2138 end if;
2139 end if;
2140 else
2141 --
2142 open csr_past_std_mth_comp;
2143 loop
2144 fetch csr_past_std_mth_comp into l_csr_past_std_mth_comp;
2145 exit when csr_past_std_mth_comp%notfound;
2146 /* Applied data on the Same Date Earned Month can not be applicable since short term. */
2147 /* Geppen Applied 9 > Santei(8) => Previous entry should be used.) */
2148 /* Date Earned of Santei is always 8/1, One of Geppen is always X/1 */
2149 if trunc(to_date(nvl(l_csr_past_std_mth_comp.applied_mth,'000101')||'01','YYYYMMDD'),'MM')
2150 < trunc(p_date_earned,'MM') then
2151 l_applied_mth_old := l_csr_past_std_mth_comp.applied_mth;
2152 if l_applied_mth_old is not null then
2153 l_std_mth_comp_old := l_csr_past_std_mth_comp.new_std_mth_comp;
2154 --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);
2155 end if;
2156 exit;
2157 end if;
2158 end loop;
2159 close csr_past_std_mth_comp;
2160 --
2161 end if;
2162 --
2163 if l_applied_mth_old is not null then
2164 p_latest_applied_date := to_date(l_applied_mth_old||'01','YYYYMMDD');
2165 else
2166 p_latest_applied_date := null;
2167 end if;
2168 --
2169 p_latest_std_mth_comp := l_std_mth_comp_old;
2170 --
2171 end get_latest_std_mth_comp_info;
2172 --
2173 function chk_hi_wp_invalid(
2174 p_qualified_date in date,
2175 p_disqualified_date in date,
2176 p_date_earned in date)
2177 return number
2178 is
2179 --
2180 l_qualified_date date := p_qualified_date;
2181 l_disqualified_date date := p_disqualified_date;
2182 --
2183 /* 0: N, 1: Y */
2184 l_hi_wp_invalid number := 0;
2185 --
2186 begin
2187 --
2188 if l_qualified_date is null then
2189 -- no entry value, not insured.
2190 if l_disqualified_date is null then
2191 --
2192 l_qualified_date := hr_api.g_eot;
2193 l_disqualified_date := hr_api.g_sot;
2194 -- this paterns identify "not insured" as shortage of data.
2195 -- qualified date should be under disqualified date.
2196 else
2197 --
2198 l_qualified_date := hr_api.g_eot;
2199 --
2200 end if;
2201 --
2202 else
2203 --
2204 -- This is normal patern.
2205 -- disqualified date should be over qualified date.
2206 if l_disqualified_date is null then
2207 --
2208 l_disqualified_date := hr_api.g_eot;
2209 --
2210 end if;
2211 --
2212 end if;
2213 --
2214 if p_date_earned < l_qualified_date
2215 or l_disqualified_date <= p_date_earned then
2216 --
2217 l_hi_wp_invalid := 1;
2218 --
2219 end if;
2220 --
2221 return l_hi_wp_invalid;
2222 end chk_hi_wp_invalid;
2223 --
2224 function get_applied_date_old(
2225 p_hi_invalid in number,
2226 p_wp_invalid in number,
2227 p_hi_applied_date_old in date,
2228 p_wp_applied_date_old in date,
2229 p_si_submit_type in number)
2230 return date
2231 is
2232 --
2233 l_applied_date_old date;
2234 --
2235 begin
2236 --
2237 if p_hi_invalid = 0 and p_wp_invalid = 0 then
2238 --
2239 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')
2240 or p_si_submit_type = 2 then
2241 --
2242 l_applied_date_old := p_wp_applied_date_old;
2243 --
2244 else
2245 --
2246 l_applied_date_old := p_hi_applied_date_old;
2247 --
2248 end if;
2249 --
2250 else
2251 --
2252 if p_hi_invalid = 1 then
2253 --
2254 l_applied_date_old := p_wp_applied_date_old;
2255 --
2256 end if;
2257 --
2258 if p_wp_invalid = 1 then
2259 --
2260 l_applied_date_old := p_hi_applied_date_old;
2261 --
2262 end if;
2263 --
2264 end if;
2265 --
2266 return l_applied_date_old;
2267 end get_applied_date_old;
2268 --
2269 function get_user_elm_name(p_base_elm_name in varchar2)
2270 return varchar2
2271 is
2272 --
2273 l_user_elm_name pay_element_types_f_tl.element_name%type;
2274 --
2275 cursor csr_user_elm_name
2276 is
2277 select pett.element_name
2278 from pay_element_types_f pet,
2279 pay_element_types_f_tl pett
2280 where pet.element_name = p_base_elm_name
2281 and pett.element_type_id = pet.element_type_id
2282 and pett.language = userenv('LANG');
2283 --
2284 begin
2285 --
2286 open csr_user_elm_name;
2287 fetch csr_user_elm_name into l_user_elm_name;
2288 close csr_user_elm_name;
2289 --
2290 return l_user_elm_name;
2291 end get_user_elm_name;
2292 --
2293 END PAY_JP_REPORT_PKG;