[Home] [Help]
233: from
234: hr_lookups flv2,
235: hr_lookups flv1,
236: per_all_people_f pcon,
237: per_contact_relationships pcr,
238: per_all_people_f pp
239: where pp.person_id=p_person_id
240: and p_effective_date
241: between pp.effective_start_date and pp.effective_end_date
1401: p_effective_date IN DATE) RETURN VARCHAR2 IS
1402: --
1403: CURSOR cel_disability_details IS
1404: SELECT /*+ ORDERED
1405: INDEX(PCR PER_CONTACT_RELATIONSHIPS_N2)
1406: INDEX(PCEIF PER_CONTACT_EXTRA_INFO_N1)
1407: INDEX(PAPF PER_PEOPLE_F_PK) */
1408: DECODE(pceif.cei_information7,
1409: NULL,SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 ||
1413: SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 || ' (' || pceif.cei_information7 ||
1414: DECODE(pceif.cei_information6,
1415: '20', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY'),
1416: '30', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER'), NULL) || ')',1,2000)) details
1417: FROM per_contact_relationships pcr,
1418: per_contact_extra_info_f pceif,
1419: per_all_people_f papf
1420: WHERE pcr.person_id = p_person_id
1421: AND pcr.cont_information_category = 'JP'
1481: FROM dual
1482: WHERE EXISTS(
1483: SELECT /*+ ORDERED */
1484: NULL
1485: FROM per_contact_relationships pcr,
1486: per_contact_extra_info_f pceif
1487: WHERE pcr.person_id = p_person_id
1488: AND pcr.cont_information_category = 'JP'
1489: AND pcr.cont_information1 = 'Y'
1573: --
1574: cursor cel_hi_dependent_number
1575: is
1576: select count(pcr.person_id)
1577: from per_contact_relationships pcr
1578: where pcr.person_id = p_person_id
1579: and pcr.cont_information_category = 'JP'
1580: and pcr.cont_information1 = 'Y'
1581: and p_effective_date