[Home] [Help]
1227: CURSOR get_prim_cont_party_info_reln (p_party_id IN NUMBER) IS
1228: SELECT hzp.person_first_name first_name,
1229: hzp.person_last_name last_name
1230: FROM hz_parties hzp,
1231: hz_relationships hzr
1232: WHERE hzr.PARTY_ID = p_party_id
1233: AND hzr.SUBJECT_ID = hzp.PARTY_ID
1234: AND hzr.SUBJECT_TYPE = 'PERSON';
1235:
1523: -- At the point of opening this cursor, we already have the contact point id.
1524: -- We use this contact point id to go against the following 2 tables to derive the
1525: -- contact's full name :
1526: -- 1) PER_ALL_PEOPLE_F - for contact type = EMPLOYEE
1527: -- 2) HZ_RELATIONSHIPS - for contact type = PARTY_RELATIONSHIP
1528: -- 3) HZ_PARTIES - for contact type = PERSON
1529:
1530: l_tmp_contact_point_id NUMBER;
1531:
1534: hzp.person_last_name last_name,
1535: cshcp.contact_type,
1536: cshcp.contact_point_id
1537: FROM hz_parties hzp,
1538: hz_relationships hzr,
1539: cs_hz_sr_contact_points cshcp
1540: WHERE cshcp.INCIDENT_ID = l_request_id
1541: AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
1542: AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
1572: CURSOR sel_contact_name_csr IS
1573: SELECT hzp.person_first_name first_name,
1574: hzp.person_last_name last_name
1575: FROM hz_parties hzp,
1576: hz_relationships hzr,
1577: cs_hz_sr_contact_points cshcp
1578: WHERE cshcp.INCIDENT_ID = l_request_id
1579: AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
1580: AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'