[Home] [Help]
130: -- contains the message itself
131: -- x_relationship_id OUT NUMBER
132: -- Relationship_Id created between Group AND member
133: -- These valuee is stored at
134: -- hz_relationships.PARTY_RELATIONSHIP_ID
135: --
136: -- Called From:
137: -- ego_party_pub.create_group
138: -- ego_party_pub.add_group_member
171: ,cp_object_id IN NUMBER
172: ,cp_object_table_name IN VARCHAR2
173: ,cp_relationship_code IN VARCHAR2) IS
174: SELECT relationship_id
175: FROM hz_relationships
176: WHERE subject_id = cp_subject_id
177: AND subject_type = cp_subject_table_name
178: AND object_id = cp_object_id
179: AND object_type = cp_object_table_name
946: l_grp_member_id NUMBER;
947:
948: -- CURSOR c_get_owner_details (cp_group_id IN NUMBER) IS
949: -- SELECT subject_id, relationship_id, object_version_number
950: -- FROM hz_relationships
951: -- WHERE object_id = cp_group_id
952: -- AND object_type = 'GROUP'
953: -- AND subject_type = 'PERSON'
954: -- AND relationship_type = G_OWNER_GROUP_REL_TYPE
957:
958: CURSOR c_is_group_member(cp_group_id IN NUMBER
959: ,cp_member_id IN NUMBER) IS
960: SELECT subject_id
961: FROM hz_relationships
962: WHERE object_id = cp_group_id
963: AND object_type = 'GROUP'
964: AND subject_id = cp_member_id
965: AND subject_type = 'PERSON'
1363:
1364:
1365: CURSOR c_get_group_members(cp_group_id IN NUMBER) IS
1366: SELECT relationship_id, object_version_number
1367: FROM hz_relationships
1368: WHERE object_id = cp_group_id
1369: AND relationship_type = G_MEMBER_GROUP_REL_TYPE
1370: AND status = 'A'
1371: AND SYSDATE between start_date and NVL(end_date,SYSDATE);
1371: AND SYSDATE between start_date and NVL(end_date,SYSDATE);
1372:
1373: -- CURSOR c_get_group_owner (cp_group_id IN NUMBER) IS
1374: -- SELECT relationship_id, object_version_number
1375: -- FROM hz_relationships
1376: -- WHERE object_id = cp_group_id
1377: -- AND relationship_type = G_OWNER_GROUP_REL_TYPE
1378: -- AND status = 'A'
1379: -- AND SYSDATE between start_date and NVL(end_date,SYSDATE);
1633: --
1634: -- x_relationship_id OUT NUMBER
1635: -- Relationship_Id that has been created between Group_id.
1636: -- and the Member_Id, which is finally stored in
1637: -- hz_relationships.PARTY_RELATIONSHIP_ID
1638: --
1639: --
1640: -- Version: Current Version 1.0
1641: -- Previous Version : None
1930: IF (p_relationship_id IS NOT NULL) THEN
1931: -- added Directional flag in where clause since two records are getting returned for each relationship one forward and one backward
1932: SELECT subject_id, object_id
1933: INTO l_member_id, l_group_id
1934: FROM hz_relationships
1935: WHERE RELATIONSHIP_ID = p_relationship_id
1936: AND directional_flag = 'F';
1937:
1938: l_party_rel_rec.status := 'I';
2105: x_party_number HZ_PARTIES.party_number%TYPE;
2106:
2107: CURSOR c_grp_member_emailaddr (cp_group_id IN NUMBER) IS
2108: SELECT member.email_address
2109: FROM hz_relationships grp_rel,
2110: EGO_PEOPLE_V member
2111: WHERE grp_rel.object_id = cp_group_id
2112: AND grp_rel.object_type = 'GROUP'
2113: AND grp_rel.relationship_type = 'MEMBERSHIP'
2537: AND hr_employee.EFFECTIVE_START_DATE <= SYSDATE
2538: AND NVL(hr_employee.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
2539: AND NOT EXISTS
2540: (SELECT null
2541: FROM hz_relationships emp_cmpy
2542: WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
2543: AND emp_cmpy.subject_type = 'PERSON'
2544: AND emp_cmpy.subject_id = hr_employee.PARTY_ID
2545: AND emp_cmpy.object_type = 'ORGANIZATION'
2623: AND hr_employee.effective_start_date <= SYSDATE
2624: AND NVL(hr_employee.effective_end_date,SYSDATE) >= SYSDATE
2625: AND NOT EXISTS
2626: (SELECT NULL
2627: FROM hz_relationships emp_cmpy
2628: WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
2629: AND emp_cmpy.subject_type = 'PERSON'
2630: AND emp_cmpy.subject_id = hr_employee.party_id
2631: AND emp_cmpy.object_type = 'ORGANIZATION'