[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT relationship_id
FROM hz_relationships
WHERE subject_id = cp_subject_id
AND subject_type = cp_subject_table_name
AND object_id = cp_object_id
AND object_type = cp_object_table_name
AND relationship_code = cp_relationship_code
AND status = 'A'
AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);
procedure Update_Relationship (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_party_rel_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
p_object_version_no_rel IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
------------------------------------------------------------------------
-- Start of comments
-- API name : Update_Relationship
-- TYPE : Private
-- Pre-reqs : An existing Relationship
-- FUNCTION : Update a Relationship between 2 Party Ids.
--
-- Parameters:
-- IN : p_api_version IN NUMBER (required)
-- API Version of this procedure
-- p_init_msg_level IN VARCHAR2 (optional)
-- DEFAULT = FND_API.G_FALSE
-- Indicates whether the message stack needs to be cleared
-- p_commit IN VARCHAR2 (optional)
-- DEFAULT = FND_API.G_FALSE
-- Indicates whether the data should be committed
-- p_party_rel_rec IN NUMBER (required)
-- The party relation record that needs to be updated
-- Record type -> HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE
--
-- IN/OUT : p_object_version_no_rel IN OUT NUMBER (required)
-- Takes in the version of the record to be updated
-- Returns the version of the record after updation
--
-- OUT : x_return_status OUT NUMBER
-- Result of all the operations
-- FND_API.G_RET_STS_SUCCESS if success
-- FND_API.G_RET_STS_ERROR if error
-- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
-- x_msg_count OUT NUMBER
-- number of messages in the message list
-- x_msg_data OUT VARCHAR2
-- if number of messages is 1, then this parameter
-- contains the message itself
--
-- Called From:
-- ego_party_pub.update_group
-- ego_party_pub.remove_group_member
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes :
--
-- END OF comments
------------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RELATIONSHIP';
SAVEPOINT EGO_UPDATE_RELATIONSHIP;
HZ_RELATIONSHIP_V2PUB.update_relationship
(p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_relationship_rec => p_party_rel_rec
,p_object_version_number => p_object_version_no_rel
,p_party_object_version_number => l_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
mdebug('. UPDATE_RELATIONSHIP: Succesfully updated the relationship ');
mdebug('. UPDATE_RELATIONSHIP: return_status '|| x_return_status);
mdebug('. UPDATE_RELATIONSHIP: x_msg_data ' || x_msg_data);
mdebug('. UPDATE_RELATIONSHIP: Tracing....');
ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
mdebug('. UPDATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_ERROR''');
ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
mdebug('. UPDATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
mdebug('. UPDATE_RELATIONSHIP: Ending : Returning UNEXPECTED ERROR');
END Update_Relationship;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO';
UPDATE hz_parties
SET mission_statement = p_description
WHERE party_id = l_group_id;
procedure Update_Group (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_group_id IN NUMBER,
p_group_name IN VARCHAR2,
p_description IN VARCHAR2,
p_email_address IN VARCHAR2,
-- p_owner_person_id IN NUMBER,
p_object_version_no_group IN OUT NOCOPY NUMBER,
--p_object_version_no_owner_rel IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
------------------------------------------------------------------------
-- Start of comments
-- API name : Update_Group
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Update a Group.
-- p_object_version_number is a mandatory field used to check
-- whether the record is updated after query
-- Looks for the following relationships
-- If the Group Owner has changed
-- update the owner relationship record
-- If the new Group Owner is not a member
-- create a new member record
-- If this operation fails then the category is not
-- created and error code is returned.
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes :
--
-- END OF comments
------------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP';
l_update_owner BOOLEAN;
l_last_update_date DATE;
SELECT subject_id
FROM hz_relationships
WHERE object_id = cp_group_id
AND object_type = 'GROUP'
AND subject_id = cp_member_id
AND subject_type = 'PERSON'
AND relationship_type = G_MEMBER_GROUP_REL_TYPE
AND status = 'A'
AND SYSDATE between start_date and NVL(end_date,SYSDATE);
SELECT contact_point_id, object_version_number, email_address, status, email_format
FROM hz_contact_points
WHERE owner_table_id = cp_group_id
AND owner_table_name = 'HZ_PARTIES'
AND status = 'A';
mdebug('UPDATE_GROUP: ....1....... ');
mdebug('UPDATE_GROUP: All required params are passed ');
SAVEPOINT EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP: Before calling HZ_PARTY_V2PUB.update_group');
HZ_PARTY_V2PUB.update_group
(p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_group_rec => l_group_rec
,p_party_object_version_number => p_object_version_no_group
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
mdebug('UPDATE_GROUP: Existed out of HZ_PARTY_V2PUB.update_group with status '''|| l_return_status||'''');
ROLLBACK TO EGO_UPDATE_GROUP;
UPDATE hz_parties
SET mission_statement = p_description
WHERE party_id = p_group_id;
mdebug('UPDATE_GROUP: No need to create Contact Point during update of Group');
mdebug(' UPDATE_GROUP: Creating Contact point now !!! ');
ROLLBACK TO EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP: Deleted the existing contact point');
mdebug('UPDATE_GROUP: before calling HZ_CONTACT_POINT_V2PUB.update_contact_point');
HZ_CONTACT_POINT_V2PUB.update_contact_point
(p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_contact_point_rec => l_contact_point_rec
,p_edi_rec => l_edi_rec
,p_email_rec => l_email_rec
,p_phone_rec => l_phone_rec
,p_telex_rec => l_telex_rec
,p_web_rec => l_web_rec
,p_object_version_number => l_object_version_no_contact
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
mdebug('UPDATE_GROUP: Exited from HZ_CONTACT_POINT_V2PUB.update_contact_point with status '''||l_return_status||'''');
ROLLBACK TO EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP updated group '|| to_char(p_group_id));
mdebug('UPDATE_GROUP Tracing....');
ROLLBACK TO EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP Ending : Returning ''FND_API.G_EXC_ERROR'' ERROR');
ROLLBACK TO EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR'' ERROR');
ROLLBACK TO EGO_UPDATE_GROUP;
mdebug('UPDATE_GROUP Ending : Returning UNEXPECTED ERROR');
END update_group;
procedure Delete_Group (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_group_id IN NUMBER,
p_object_version_no_group IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
------------------------------------------------------------------------
-- Start of comments
-- API name : Delete_Group
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete a Group.
-- p_object_version_no_group is a mandatory field used to check
-- whether the record is updated after query
-- Delete the Group, owner and all members of the Group
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes :
--
-- END OF comments
------------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP';
SELECT relationship_id, object_version_number
FROM hz_relationships
WHERE object_id = cp_group_id
AND relationship_type = G_MEMBER_GROUP_REL_TYPE
AND status = 'A'
AND SYSDATE between start_date and NVL(end_date,SYSDATE);
SELECT contact_point_id, object_version_number
FROM hz_contact_points
WHERE owner_table_id = cp_group_id
AND owner_table_name = 'HZ_PARTIES'
AND status = 'A';
SAVEPOINT EGO_DELETE_GROUP;
update_relationship
(p_api_version => 1.0
,p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_commit => NVL(p_commit, 'F')
,p_party_rel_rec => l_party_rel_rec
,p_object_version_no_rel => l_object_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO EGO_DELETE_GROUP;
HZ_CONTACT_POINT_V2PUB.update_contact_point
(p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_contact_point_rec => l_contact_point_rec
,p_edi_rec => l_edi_rec
,p_email_rec => l_email_rec
,p_phone_rec => l_phone_rec
,p_telex_rec => l_telex_rec
,p_web_rec => l_web_rec
,p_object_version_number => l_object_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO EGO_DELETE_GROUP;
HZ_PARTY_V2PUB.update_group
(p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_group_rec => l_group_rec
,p_party_object_version_number => p_object_version_no_group
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO EGO_DELETE_GROUP;
ROLLBACK TO EGO_DELETE_GROUP;
mdebug('DELETE_GROUP Ending : Returning ''FND_API.G_EXC_ERROR'' ERROR');
ROLLBACK TO EGO_DELETE_GROUP;
mdebug('DELETE_GROUP Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR'' ERROR');
ROLLBACK TO EGO_DELETE_GROUP;
mdebug('DELETE_GROPU Ending : Returning UNEXPECTED ERROR');
END delete_group;
SELECT grants.grant_guid
FROM fnd_grants grants,
fnd_menus menus,
fnd_objects obj
WHERE menus.menu_name='EGO_VIEW_GROUP_MEMBERS'
AND menus.menu_id=grants.menu_id
AND obj.object_id=grants.object_id
AND obj.obj_name='EGO_GROUP'
AND grants.instance_pk1_value=cp_instance_id
AND grantee_key='HZ_PARTY:'||cp_party_id;
SELECT subject_id, object_id
INTO l_member_id, l_group_id
FROM hz_relationships
WHERE RELATIONSHIP_ID = p_relationship_id
AND directional_flag = 'F';
update_relationship
(p_api_version => 1.0
,p_init_msg_list => NVL(p_init_msg_list, 'F')
,p_commit => NVL(p_commit, 'F')
,p_party_rel_rec => l_party_rel_rec
,p_object_version_no_rel => p_object_version_no_rel
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
FND_MESSAGE.Set_Name('EGO', 'EGO_GRP_MEMB_CANNOT_DELETE');
l_last_update_date DATE;
SELECT member.email_address
FROM hz_relationships grp_rel,
EGO_PEOPLE_V member
WHERE grp_rel.object_id = cp_group_id
AND grp_rel.object_type = 'GROUP'
AND grp_rel.relationship_type = 'MEMBERSHIP'
AND grp_rel.status = 'A'
AND grp_rel.start_date <= SYSDATE
AND NVL(grp_rel.end_date, SYSDATE) >= SYSDATE
AND grp_rel.subject_type = 'PERSON'
AND grp_rel.subject_id = member.person_id;
mdebug('GET_EMAIL_ADDRESSES: selecting party type ');
SELECT party_type
INTO l_party_type
FROM hz_parties
WHERE party_id = p_party_id;
mdebug('GET_EMAIL_ADDRESSES: party type selected as ' || l_party_type );
SELECT email_address
INTO l_concat_email_addresses
FROM ego_people_v
WHERE person_id = p_party_id;
SELECT COUNT(*)
INTO l_class_count
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND class_category = p_category;
PROCEDURE update_code_assignment (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_party_id IN NUMBER,
p_category IN VARCHAR2,
p_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
------------------------------------------------------------------------
-- Start of comments
-- API name : create_code_assignment
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Get Email Address.
-- Then intention is to Get all e-mail addresses of the
-- persons in the collapsed list of members for the Group
--
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes :
--
-- END OF comments
------------------------------------------------------------------------
l_class_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CODE_ASSIGNMENT';
ROLLBACK TO EGO_UPDATE_CODE_ASSIGNMENT;
mdebug('UPDATE_CODE_ASSIGNMENT: ....1......');
SELECT code_assignment_id, object_version_number
INTO l_assignment_id, l_version_number
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND class_category = p_category;
HZ_CLASSIFICATION_V2PUB.update_code_assignment
(
FND_API.G_FALSE,
l_code_assignment_rec,
l_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
END update_code_assignment
;
SELECT hca.owner_table_id
INTO l_party_id
FROM hz_code_assignments hca
WHERE hca.owner_table_id = p_company_id
AND hca.owner_table_name = 'HZ_PARTIES'
AND hca.class_category = 'POS_PARTICIPANT_TYPE'
AND hca.class_code = 'ENTERPRISE';
SELECT hr_employee.party_id person_id
INTO l_party_id
FROM fnd_user fnd_user, per_all_people_f hr_employee
WHERE fnd_user.EMPLOYEE_ID = hr_employee.PERSON_ID
AND fnd_user.person_party_id = hr_employee.party_id
AND fnd_user.start_date <= SYSDATE
AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
AND (hr_employee.CURRENT_EMPLOYEE_FLAG = 'Y'
OR
(fnd_profile.value('HR_TREAT_CWK_AS_EMP') = 'Y' AND
hr_employee.current_npw_flag = 'Y')
)
AND hr_employee.EFFECTIVE_START_DATE <= SYSDATE
AND NVL(hr_employee.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
AND NOT EXISTS
(SELECT null
FROM hz_relationships emp_cmpy
WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
AND emp_cmpy.subject_type = 'PERSON'
AND emp_cmpy.subject_id = hr_employee.PARTY_ID
AND emp_cmpy.object_type = 'ORGANIZATION'
AND NVL(emp_cmpy.start_date,SYSDATE) <= SYSDATE
AND NVL(emp_cmpy.end_date,SYSDATE) >= SYSDATE
);
SELECT hr_employee.party_id person_id
FROM fnd_user fnd_user, per_all_people_f hr_employee
WHERE fnd_user.employee_id = hr_employee.person_id
AND fnd_user.person_party_id = hr_employee.party_id
AND fnd_user.start_date <= SYSDATE
AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
AND (hr_employee.current_employee_flag = 'Y'
OR
(fnd_profile.value('HR_TREAT_CWK_AS_EMP') = 'Y' AND
hr_employee.current_npw_flag = 'Y')
)
AND hr_employee.effective_start_date <= SYSDATE
AND NVL(hr_employee.effective_end_date,SYSDATE) >= SYSDATE
AND NOT EXISTS
(SELECT NULL
FROM hz_relationships emp_cmpy
WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
AND emp_cmpy.subject_type = 'PERSON'
AND emp_cmpy.subject_id = hr_employee.party_id
AND emp_cmpy.object_type = 'ORGANIZATION'
AND NVL(emp_cmpy.start_date,SYSDATE) <= SYSDATE
AND NVL(emp_cmpy.end_date,SYSDATE) >= SYSDATE
);
SELECT hp.party_id
INTO l_org_id
FROM hz_parties hp, hz_code_assignments hca
WHERE hca.owner_table_id = hp.party_id
AND hca.owner_table_name = 'HZ_PARTIES'
AND hca.class_category = 'POS_PARTICIPANT_TYPE'
AND hca.class_code = 'ENTERPRISE'
AND hp.status = 'A';