[Home] [Help]
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
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 ||
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'
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'
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