[Home] [Help]
1197: SELECT email_address
1198: FROM hz_contact_points
1199: WHERE contact_point_id = p_contact_point_id
1200: AND contact_point_type = 'EMAIL'
1201: AND owner_table_name = 'HZ_PARTIES'
1202: AND owner_table_id = p_party_id ;
1203:
1204: -- Get any customer email address starting with the primary email address.
1205:
1206: CURSOR get_any_cust_email_address(p_party_id IN NUMBER) IS
1207: SELECT email_address
1208: FROM hz_contact_points
1209: WHERE contact_point_type = 'EMAIL'
1210: AND owner_table_name = 'HZ_PARTIES'
1211: AND owner_table_id = p_party_id
1212: ORDER BY Primary_flag DESC;
1213:
1214: -- Get the email information from the HR schema for the primary contact.
1226:
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:
1236: CURSOR get_prim_cont_party_info_per (p_party_id IN NUMBER) IS
1237: SELECT hzp.person_first_name first_name,
1238: hzp.person_last_name last_name
1239: FROM hz_parties hzp
1240: WHERE hzp.PARTY_ID = p_party_id;
1241:
1242: CURSOR get_prim_cont_party_info_empl (p_party_id IN NUMBER) IS
1243: SELECT ppf.first_name first_name,
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:
1532: /* CURSOR sel_contact_name_csr IS
1533: SELECT hzp.person_first_name first_name,
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
1548: SELECT hzp.person_first_name first_name,
1549: hzp.person_last_name last_name,
1550: cshcp.contact_type,
1551: cshcp.contact_point_id
1552: FROM hz_parties hzp,
1553: cs_hz_sr_contact_points cshcp
1554: WHERE cshcp.INCIDENT_ID = l_request_id
1555: AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
1556: AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
1571:
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
1584: AND hzr.SUBJECT_TYPE = 'PERSON'
1585: UNION --start bug 12666217
1586: SELECT hzp.person_first_name first_name,
1587: hzp.person_last_name last_name
1588: FROM hz_parties hzp,
1589: cs_hz_sr_contact_points cshcp
1590: WHERE cshcp.INCIDENT_ID = l_request_id
1591: AND cshcp.sr_contact_point_id = l_tmp_contact_point_id
1592: AND cshcp.CONTACT_POINT_TYPE = 'EMAIL'
4184:
4185:
4186: CURSOR sel_contact_party_name_csr IS
4187: SELECT PARTY_NAME
4188: FROM HZ_PARTIES
4189: WHERE
4190: PARTY_ID = l_contact_party_id;
4191:
4192: BEGIN
6311: CS_INCIDENT_SEVERITIES_TL SEVERITY,
6312: CS_INCIDENT_STATUSES_VL STATUS,
6313: --CS_INCIDENT_URGENCIES_VL URGENCY,
6314: CS_INCIDENT_URGENCIES_TL URGENCY,
6315: HZ_PARTIES RA2,
6316: CS_LOOKUPS CSLKUP,
6317: --MTL_SYSTEM_ITEMS_VL MTL
6318: MTL_SYSTEM_ITEMS_TL MTL
6319: WHERE INC.INCIDENT_NUMBER = l_request_number
6834:
6835:
6836: CURSOR sel_contact_party_name_csr IS
6837: SELECT PARTY_NAME
6838: FROM HZ_PARTIES
6839: WHERE
6840: PARTY_ID = p_party_id;
6841:
6842: l_int_profile varchar2(30);