The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_rel_selected_datasources VARCHAR2(255);
g_rel_is_datasource_selected VARCHAR2(1) := 'N';
PROCEDURE do_update_rel(
p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_party(
p_party_type IN VARCHAR2,
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_party_flags(
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_party_id IN NUMBER
);
SELECT LEVEL, RELATIONSHIP_ID, SUBJECT_ID, OBJECT_ID, STATUS, START_DATE, END_DATE,
SUBJECT_TABLE_NAME, SUBJECT_TYPE
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID <> p_relationship_id
START WITH OBJECT_ID = p_parent_id
AND OBJECT_TABLE_NAME = p_parent_table_name
AND OBJECT_TYPE = p_parent_object_type
AND DIRECTION_CODE = 'P'
AND RELATIONSHIP_TYPE = p_relationship_type
AND RELATIONSHIP_ID <> p_relationship_id
-- check for intersection with base relationship
AND START_DATE <= p_end_date
AND END_DATE >= p_start_date
-- Bug 3364626 : Added status='A' condition
AND STATUS='A'
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID AND OBJECT_TYPE = PRIOR SUBJECT_TYPE AND OBJECT_TABLE_NAME = PRIOR SUBJECT_TABLE_NAME
AND DIRECTION_CODE = 'P' AND RELATIONSHIP_TYPE = p_relationship_type
AND RELATIONSHIP_ID <> p_relationship_id
-- check for intersection against prior relationship
AND START_DATE <= PRIOR END_DATE
AND END_DATE >= PRIOR START_DATE
-- check for intersection against base relationship
AND START_DATE <= p_end_date
AND END_DATE >= p_start_date
-- Bug 3364626 : Added status='A' condition
AND STATUS='A';
l_max_start_date_stack.DELETE(v_rel.level+1,l_max_start_date_stack.COUNT);
l_min_end_date_stack.DELETE(v_rel.level+1,l_min_end_date_stack.COUNT);
| For non-profile entities, the concept of select
| /de-select data-sources is obsoleted.
+===========================================================================*/
PROCEDURE do_create_rel(
p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
x_created_party OUT NOCOPY VARCHAR2,
x_relationship_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
p_create_org_contact IN VARCHAR2,
p_contact_party_id IN NUMBER,
p_contact_party_usage_code IN VARCHAR2
) IS
-- this cursor will uniquely identify a record
-- in hz_relationship_types package
CURSOR c_rel_type
IS
SELECT RELATIONSHIP_TYPE,
FORWARD_REL_CODE,
BACKWARD_REL_CODE,
CREATE_PARTY_FLAG,
ALLOW_RELATE_TO_SELF_FLAG,
HIERARCHICAL_FLAG,
ALLOW_CIRCULAR_RELATIONSHIPS,
DIRECTION_CODE,
RELATIONSHIP_TYPE_ID,
NVL(MULTIPLE_PARENT_ALLOWED, 'N') MULTIPLE_PARENT_ALLOWED
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
AND FORWARD_REL_CODE = p_relationship_rec.relationship_code
AND SUBJECT_TYPE = p_relationship_rec.subject_type
AND OBJECT_TYPE = p_relationship_rec.object_type
AND STATUS = 'A';
SELECT HZ_RELATIONSHIPS_S.NEXTVAL
INTO l_relationship_id
FROM DUAL;
SELECT 'Y'
INTO l_dummy
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = l_relationship_id
AND DIRECTIONAL_FLAG = 'F';
SELECT 'Y'
INTO l_dummy
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = l_relationship_id
AND DIRECTIONAL_FLAG = 'F';
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE OBJECT_ID = l_child_id
AND OBJECT_TABLE_NAME = l_child_table_name
AND OBJECT_TYPE = l_child_object_type
AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
AND DIRECTION_CODE = 'P'
-- Bug 3817438 : Added condition to check only active relationships
AND STATUS= 'A'
AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))
OR
NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
OR
NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
)
-- Bug 3294936 : Added ROWNUM=1 condition
AND ROWNUM=1;
SELECT MEANING
INTO l_meaning
-- Bug 3664939 : Use fnd_lookup_values_vl to get lookup meaning
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = 'HZ_RELATIONSHIP_TYPE'
AND LOOKUP_CODE = p_relationship_rec.relationship_type
AND VIEW_APPLICATION_ID = 222
AND ROWNUM =1;
/* g_rel_is_datasource_selected = 'Y' AND */
/*
(p_relationship_rec.content_source_type = 'USER_ENTERED'
OR
p_relationship_rec.content_source_type IS NULL
)
*/
p_relationship_rec.subject_table_name = 'HZ_PARTIES'
AND
p_relationship_rec.object_table_name = 'HZ_PARTIES'
THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'denormalizing to hz_parties',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
do_update_party_flags(
p_relationship_rec,
p_relationship_rec.subject_id);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-1 (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_RELATIONSHIPS_PKG.Insert_Row (
X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
X_SUBJECT_ID => p_relationship_rec.subject_id,
X_SUBJECT_TYPE => p_relationship_rec.subject_type,
X_SUBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
X_OBJECT_ID => p_relationship_rec.object_id,
X_OBJECT_TYPE => p_relationship_rec.object_type,
X_OBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
X_PARTY_ID => x_party_id,
X_RELATIONSHIP_CODE => p_relationship_rec.relationship_code,
X_DIRECTIONAL_FLAG => 'F',
X_COMMENTS => p_relationship_rec.comments,
X_START_DATE => p_relationship_rec.start_date,
X_END_DATE => l_end_date,
X_STATUS => p_relationship_rec.status,
X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
X_ATTRIBUTE1 => p_relationship_rec.attribute1,
X_ATTRIBUTE2 => p_relationship_rec.attribute2,
X_ATTRIBUTE3 => p_relationship_rec.attribute3,
X_ATTRIBUTE4 => p_relationship_rec.attribute4,
X_ATTRIBUTE5 => p_relationship_rec.attribute5,
X_ATTRIBUTE6 => p_relationship_rec.attribute6,
X_ATTRIBUTE7 => p_relationship_rec.attribute7,
X_ATTRIBUTE8 => p_relationship_rec.attribute8,
X_ATTRIBUTE9 => p_relationship_rec.attribute9,
X_ATTRIBUTE10 => p_relationship_rec.attribute10,
X_ATTRIBUTE11 => p_relationship_rec.attribute11,
X_ATTRIBUTE12 => p_relationship_rec.attribute12,
X_ATTRIBUTE13 => p_relationship_rec.attribute13,
X_ATTRIBUTE14 => p_relationship_rec.attribute14,
X_ATTRIBUTE15 => p_relationship_rec.attribute15,
X_ATTRIBUTE16 => p_relationship_rec.attribute16,
X_ATTRIBUTE17 => p_relationship_rec.attribute17,
X_ATTRIBUTE18 => p_relationship_rec.attribute18,
X_ATTRIBUTE19 => p_relationship_rec.attribute19,
X_ATTRIBUTE20 => p_relationship_rec.attribute20,
X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
X_RELATIONSHIP_TYPE => p_relationship_rec.relationship_type,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id,
X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
X_DIRECTION_CODE => r_rel_type.direction_code,
X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-1 (-) ' ||
'x_relationship_id = ' || p_relationship_rec.relationship_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-2 (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_RELATIONSHIPS_PKG.Insert_Row (
X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
X_SUBJECT_ID => p_relationship_rec.object_id,
X_SUBJECT_TYPE => p_relationship_rec.object_type,
X_SUBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
X_OBJECT_ID => p_relationship_rec.subject_id,
X_OBJECT_TYPE => p_relationship_rec.subject_type,
X_OBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
X_PARTY_ID => x_party_id,
X_RELATIONSHIP_CODE => r_rel_type.backward_rel_code,
X_DIRECTIONAL_FLAG => 'B',
X_COMMENTS => p_relationship_rec.comments,
X_START_DATE => p_relationship_rec.start_date,
X_END_DATE => l_end_date,
X_STATUS => p_relationship_rec.status,
X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
X_ATTRIBUTE1 => p_relationship_rec.attribute1,
X_ATTRIBUTE2 => p_relationship_rec.attribute2,
X_ATTRIBUTE3 => p_relationship_rec.attribute3,
X_ATTRIBUTE4 => p_relationship_rec.attribute4,
X_ATTRIBUTE5 => p_relationship_rec.attribute5,
X_ATTRIBUTE6 => p_relationship_rec.attribute6,
X_ATTRIBUTE7 => p_relationship_rec.attribute7,
X_ATTRIBUTE8 => p_relationship_rec.attribute8,
X_ATTRIBUTE9 => p_relationship_rec.attribute9,
X_ATTRIBUTE10 => p_relationship_rec.attribute10,
X_ATTRIBUTE11 => p_relationship_rec.attribute11,
X_ATTRIBUTE12 => p_relationship_rec.attribute12,
X_ATTRIBUTE13 => p_relationship_rec.attribute13,
X_ATTRIBUTE14 => p_relationship_rec.attribute14,
X_ATTRIBUTE15 => p_relationship_rec.attribute15,
X_ATTRIBUTE16 => p_relationship_rec.attribute16,
X_ATTRIBUTE17 => p_relationship_rec.attribute17,
X_ATTRIBUTE18 => p_relationship_rec.attribute18,
X_ATTRIBUTE19 => p_relationship_rec.attribute19,
X_ATTRIBUTE20 => p_relationship_rec.attribute20,
X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
X_RELATIONSHIP_TYPE => r_rel_type.relationship_type,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id,
X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
X_DIRECTION_CODE => l_back_direction,
X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Insert_Row-2 (-) ' ||
'x_relationship_id = ' || p_relationship_rec.relationship_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
/* Put in the NVL since otherwise insert to hz_hierarchy_nodes was failing */
l_hierarchy_rec.effective_start_date := NVL(p_relationship_rec.start_date,SYSDATE);
SELECT to_char(hz_contact_numbers_s.nextval)
INTO l_contact_number FROM dual;
SELECT 1
INTO l_count
FROM HZ_ORG_CONTACTS
WHERE CONTACT_NUMBER = l_contact_number;
HZ_ORG_CONTACTS_PKG.Insert_Row (
X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
X_PARTY_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
X_COMMENTS => p_org_contact_rec.comments,
X_CONTACT_NUMBER => p_org_contact_rec.contact_number,
X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
X_DEPARTMENT => p_org_contact_rec.department,
X_TITLE => p_org_contact_rec.title,
X_JOB_TITLE => p_org_contact_rec.job_title,
X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
X_RANK => p_org_contact_rec.rank,
X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
X_APPLICATION_ID => p_org_contact_rec.application_id,
X_STATUS => p_org_contact_rec.party_rel_rec.status
);
| do_update_rel
|
| DESCRIPTION
| Updates relationship and party for party_relationship.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/OUT:
| p_party_rel_rec
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 09-DEC-2003 Rajib Ranjan Borah o Bug 3274236.Start date and end date were not
| getting updated if the user passed FND_API.G_MISS_CHAR.
| o Modified some previous code to remove redundancy.
| 19-FEB-2004 Rajib Ranjan Borah o Bug 3306941.The meaning should be passed as token
| for the error message HZ_API_MULTIPLE_PARENT instead
| of the relationsihp_type.
| 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| For non-profile entities, the concept of select
| /de-select data-sources is obsoleted.
| 17-FEB-2009 Sudhir Gokavarapu o Bug8241997 :l_party_id was not containing proper value
| of Party Id. Getting it from Old Relation Record istead
| of present Record.
+===========================================================================*/
PROCEDURE do_update_rel(
p_relationship_rec IN OUT NOCOPY RELATIONSHIP_REC_TYPE,
p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
hz_utility_v2pub.debug(p_message=>'do_update_rel (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
/* Bug 4873016 : Select the record from hz_relationships based on
* the values passed by the user to this API. If there is no record
* found, it means that user is trying to update any of the non-updateable
* columns. In this case, we continue to selecte the forward relationship
* record and error will be raised from the validate_relationship call.
* If a record is found, user the selected directional_flag to update
* the reciprocal record
*/
BEGIN
SELECT OBJECT_VERSION_NUMBER,
ROWID,
CONTENT_SOURCE_TYPE,
SUBJECT_TABLE_NAME,
OBJECT_TABLE_NAME,
SUBJECT_ID,
OBJECT_ID,
SUBJECT_TYPE,
OBJECT_TYPE,
RELATIONSHIP_TYPE,
RELATIONSHIP_CODE,
START_DATE,
END_DATE,
DIRECTION_CODE,
STATUS,
actual_content_source,
DIRECTIONAL_FLAG
INTO l_object_version_number,
l_rowid,
l_content_source_type,
l_subject_table_name,
l_object_table_name,
l_subject_id,
l_object_id,
l_subject_type,
l_object_type,
l_relationship_type,
l_relationship_code,
l_start_date,
l_end_date,
l_direction_code,
l_status,
db_actual_content_source,
l_directional_flag
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
AND SUBJECT_TABLE_NAME = nvl(p_relationship_rec.SUBJECT_TABLE_NAME, SUBJECT_TABLE_NAME)
AND OBJECT_TABLE_NAME = nvl(p_relationship_rec.OBJECT_TABLE_NAME, OBJECT_TABLE_NAME)
AND SUBJECT_ID = nvl(p_relationship_rec.SUBJECT_ID, SUBJECT_ID)
AND OBJECT_ID = nvl(p_relationship_rec.OBJECT_ID, OBJECT_ID)
AND SUBJECT_TYPE = nvl(p_relationship_rec.SUBJECT_TYPE, SUBJECT_TYPE)
AND OBJECT_TYPE = nvl(p_relationship_rec.OBJECT_TYPE, OBJECT_TYPE)
AND RELATIONSHIP_TYPE = nvl(p_relationship_rec.RELATIONSHIP_TYPE, RELATIONSHIP_TYPE)
AND RELATIONSHIP_CODE = nvl(p_relationship_rec.RELATIONSHIP_CODE, RELATIONSHIP_CODE)
AND ROWNUM = 1
FOR UPDATE OF RELATIONSHIP_ID NOWAIT;
SELECT OBJECT_VERSION_NUMBER,
ROWID,
CONTENT_SOURCE_TYPE,
SUBJECT_TABLE_NAME,
OBJECT_TABLE_NAME,
SUBJECT_ID,
OBJECT_ID,
SUBJECT_TYPE,
OBJECT_TYPE,
RELATIONSHIP_TYPE,
RELATIONSHIP_CODE,
START_DATE,
END_DATE,
DIRECTION_CODE,
STATUS,
actual_content_source,
-- Bug 4873016 : select DIRECTIONAL_FLAG also
DIRECTIONAL_FLAG
INTO l_object_version_number,
l_rowid,
l_content_source_type,
l_subject_table_name,
l_object_table_name,
l_subject_id,
l_object_id,
l_subject_type,
l_object_type,
l_relationship_type,
l_relationship_code,
l_start_date,
l_end_date,
l_direction_code,
l_status,
db_actual_content_source,
l_directional_flag
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
AND DIRECTIONAL_FLAG = 'F'
FOR UPDATE OF RELATIONSHIP_ID NOWAIT;
SELECT HIERARCHICAL_FLAG,
ALLOW_CIRCULAR_RELATIONSHIPS,
NVL(MULTIPLE_PARENT_ALLOWED, 'N') MULTIPLE_PARENT_ALLOWED
INTO l_hierarchical_flag,
l_allow_circular_relationships,
l_mult_parent_allowed
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = l_relationship_type
AND ROWNUM = 1;
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE OBJECT_ID = l_child_id
AND OBJECT_TABLE_NAME = l_child_table_name
AND OBJECT_TYPE = l_child_object_type
AND RELATIONSHIP_TYPE = l_relationship_type
AND DIRECTION_CODE = 'P'
AND RELATIONSHIP_ID <> p_relationship_rec.relationship_id
-- Bug 3817438 : Added condition to check only active relationships
AND STATUS= 'A'
AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, l_start_date)
AND NVL(p_relationship_rec.end_date, l_end_date)
OR
END_DATE BETWEEN NVL(p_relationship_rec.start_date, l_start_date)
AND NVL(p_relationship_rec.end_date, l_end_date)
OR
NVL(p_relationship_rec.start_date, l_start_date) BETWEEN START_DATE AND END_DATE
OR
NVL(p_relationship_rec.end_date, l_end_date) BETWEEN START_DATE AND END_DATE
)
AND ROWNUM = 1;
SELECT MEANING
INTO l_meaning
-- Bug 3664939 : Use fnd_lookup_values_vl to get lookup meaning
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE='HZ_RELATIONSHIP_TYPE'
AND LOOKUP_CODE = l_relationship_type
AND VIEW_APPLICATION_ID = 222
AND ROWNUM = 1;
/* g_rel_is_datasource_selected = 'Y' AND */
/*
l_content_source_type = 'USER_ENTERED'
AND
*/
l_subject_table_name = 'HZ_PARTIES'
AND
l_object_table_name = 'HZ_PARTIES'
THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'denormalizing to hz_parties',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
do_update_party_flags(l_rel_rec,
l_rel_rec.subject_id);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-1 (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
l_acs := nvl(p_relationship_rec.actual_content_source, 'USER_ENTERED');
HZ_RELATIONSHIPS_PKG.Update_Row (
X_Rowid => l_rowid,
X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
-- Bug 4873016 : pass NULL for non updateable columns
/*
X_SUBJECT_ID => p_relationship_rec.subject_id,
X_SUBJECT_TYPE => p_relationship_rec.subject_type,
X_SUBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
X_OBJECT_ID => p_relationship_rec.object_id,
X_OBJECT_TYPE => p_relationship_rec.object_type,
X_OBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
X_PARTY_ID => NULL,
X_RELATIONSHIP_CODE => p_relationship_rec.relationship_code,
*/
X_SUBJECT_ID => NULL,
X_SUBJECT_TYPE => NULL,
X_SUBJECT_TABLE_NAME => NULL,
X_OBJECT_ID => NULL,
X_OBJECT_TYPE => NULL,
X_OBJECT_TABLE_NAME => NULL,
X_PARTY_ID => NULL,
X_RELATIONSHIP_CODE => NULL,
X_DIRECTIONAL_FLAG => NULL,
X_COMMENTS => p_relationship_rec.comments,
X_START_DATE => p_relationship_rec.start_date,
X_END_DATE => p_relationship_rec.end_date,
X_STATUS => p_relationship_rec.status,
X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
X_ATTRIBUTE1 => p_relationship_rec.attribute1,
X_ATTRIBUTE2 => p_relationship_rec.attribute2,
X_ATTRIBUTE3 => p_relationship_rec.attribute3,
X_ATTRIBUTE4 => p_relationship_rec.attribute4,
X_ATTRIBUTE5 => p_relationship_rec.attribute5,
X_ATTRIBUTE6 => p_relationship_rec.attribute6,
X_ATTRIBUTE7 => p_relationship_rec.attribute7,
X_ATTRIBUTE8 => p_relationship_rec.attribute8,
X_ATTRIBUTE9 => p_relationship_rec.attribute9,
X_ATTRIBUTE10 => p_relationship_rec.attribute10,
X_ATTRIBUTE11 => p_relationship_rec.attribute11,
X_ATTRIBUTE12 => p_relationship_rec.attribute12,
X_ATTRIBUTE13 => p_relationship_rec.attribute13,
X_ATTRIBUTE14 => p_relationship_rec.attribute14,
X_ATTRIBUTE15 => p_relationship_rec.attribute15,
X_ATTRIBUTE16 => p_relationship_rec.attribute16,
X_ATTRIBUTE17 => p_relationship_rec.attribute17,
X_ATTRIBUTE18 => p_relationship_rec.attribute18,
X_ATTRIBUTE19 => p_relationship_rec.attribute19,
X_ATTRIBUTE20 => p_relationship_rec.attribute20,
-- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
X_CONTENT_SOURCE_TYPE => NULL,
X_RELATIONSHIP_TYPE => p_relationship_rec.relationship_type,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id,
X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
X_DIRECTION_CODE => NULL,
X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
-- Bug 4693719 : Pass correct value for ACS
X_ACTUAL_CONTENT_SOURCE => l_acs
);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-1 (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT ROWID
INTO l_rowid
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = p_relationship_rec.relationship_id
/* Bug 4873016 : query the reciprocal record based on previously
* selected directional flag. If it was 'F' select 'B'
* if it was 'B' select 'F'
*/
AND DIRECTIONAL_FLAG = decode(l_directional_flag, 'F', 'B','B', 'F');
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-2 (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_RELATIONSHIPS_PKG.Update_Row (
X_Rowid => l_rowid,
X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
X_SUBJECT_ID => NULL,
X_SUBJECT_TYPE => NULL,
X_SUBJECT_TABLE_NAME => NULL,
X_OBJECT_ID => NULL,
X_OBJECT_TYPE => NULL,
X_OBJECT_TABLE_NAME => NULL,
X_PARTY_ID => NULL,
X_RELATIONSHIP_CODE => NULL,
X_DIRECTIONAL_FLAG => NULL,
X_COMMENTS => p_relationship_rec.comments,
X_START_DATE => p_relationship_rec.start_date,
X_END_DATE => p_relationship_rec.end_date,
X_STATUS => p_relationship_rec.status,
X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
X_ATTRIBUTE1 => p_relationship_rec.attribute1,
X_ATTRIBUTE2 => p_relationship_rec.attribute2,
X_ATTRIBUTE3 => p_relationship_rec.attribute3,
X_ATTRIBUTE4 => p_relationship_rec.attribute4,
X_ATTRIBUTE5 => p_relationship_rec.attribute5,
X_ATTRIBUTE6 => p_relationship_rec.attribute6,
X_ATTRIBUTE7 => p_relationship_rec.attribute7,
X_ATTRIBUTE8 => p_relationship_rec.attribute8,
X_ATTRIBUTE9 => p_relationship_rec.attribute9,
X_ATTRIBUTE10 => p_relationship_rec.attribute10,
X_ATTRIBUTE11 => p_relationship_rec.attribute11,
X_ATTRIBUTE12 => p_relationship_rec.attribute12,
X_ATTRIBUTE13 => p_relationship_rec.attribute13,
X_ATTRIBUTE14 => p_relationship_rec.attribute14,
X_ATTRIBUTE15 => p_relationship_rec.attribute15,
X_ATTRIBUTE16 => p_relationship_rec.attribute16,
X_ATTRIBUTE17 => p_relationship_rec.attribute17,
X_ATTRIBUTE18 => p_relationship_rec.attribute18,
X_ATTRIBUTE19 => p_relationship_rec.attribute19,
X_ATTRIBUTE20 => p_relationship_rec.attribute20,
X_CONTENT_SOURCE_TYPE => NULL,
X_RELATIONSHIP_TYPE => NULL,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id,
X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
X_DIRECTION_CODE => NULL,
X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.actual_content_source
);
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Update_Row-2 (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
do_update_party(
p_party_type => 'PARTY_RELATIONSHIP',
p_relationship_rec => l_party_rel_rec,
p_old_relationship_rec => p_old_relationship_rec,
p_party_object_version_number => l_party_object_version_number,
x_profile_id => l_profile_id,
x_return_status => x_return_status
);
--Hierarchy Nodes are not getting updated.
--Instead of updating, creating a fresh hierarchy data.
IF l_status = 'I' AND p_relationship_rec.status = 'A' THEN
l_hierarchy_rec.status := 'A';
HZ_HIERARCHY_PUB.update_link(
p_init_msg_list => FND_API.G_FALSE,
p_hierarchy_node_rec => l_hierarchy_rec,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
HZ_PARTY_USG_ASSIGNMENT_PVT.update_usg_assignment (
p_validation_level => HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_NONE,
p_party_usg_assignment_rec => l_party_usg_assignment_rec,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
hz_utility_v2pub.debug(p_message=>'do_update_rel (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_rel;
| do_update_party_flags
|
| DESCRIPTION
| Denormalize flags to hz_parties:
| COMPETITOR_FLAG, REFERENCE_USE_FLAG, THIRD_PARTY_FLAG
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_relationship_rec
| p_party_id
|
| RETURNS : NONE
|
| NOTES
| If the end_date is today, we will denormailize the flags to 'N'
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_party_flags(
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_party_id IN NUMBER
) IS
l_party_id NUMBER;
SELECT party_id INTO l_party_id
FROM hz_parties
WHERE party_id = p_party_id
FOR UPDATE NOWAIT;
UPDATE HZ_PARTIES
SET COMPETITOR_FLAG = l_competitor_flag
WHERE PARTY_ID = p_party_id;
UPDATE HZ_PARTIES
SET REFERENCE_USE_FLAG = l_reference_use_flag
WHERE PARTY_ID = p_party_id;
UPDATE HZ_PARTIES
SET THIRD_PARTY_FLAG = l_third_party_flag
WHERE PARTY_ID = p_party_id;
END do_update_party_flags;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = l_party_id;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_NUMBER = l_party_number;
SELECT PARTY_NAME
INTO l_subject_name
FROM HZ_PARTIES
WHERE PARTY_ID = p_relationship_rec.subject_id;
SELECT PARTY_NAME
INTO l_object_name
FROM HZ_PARTIES
WHERE PARTY_ID = p_relationship_rec.object_id;
hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Insert_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTIES_PKG.Insert_Row (
X_PARTY_ID => l_party_rec.party_id,
X_PARTY_NUMBER => l_party_rec.party_number,
X_PARTY_NAME => l_party_name,
X_PARTY_TYPE => p_party_type,
X_VALIDATED_FLAG => l_party_rec.validated_flag,
X_ATTRIBUTE_CATEGORY => l_party_rec.attribute_category,
X_ATTRIBUTE1 => l_party_rec.attribute1,
X_ATTRIBUTE2 => l_party_rec.attribute2,
X_ATTRIBUTE3 => l_party_rec.attribute3,
X_ATTRIBUTE4 => l_party_rec.attribute4,
X_ATTRIBUTE5 => l_party_rec.attribute5,
X_ATTRIBUTE6 => l_party_rec.attribute6,
X_ATTRIBUTE7 => l_party_rec.attribute7,
X_ATTRIBUTE8 => l_party_rec.attribute8,
X_ATTRIBUTE9 => l_party_rec.attribute9,
X_ATTRIBUTE10 => l_party_rec.attribute10,
X_ATTRIBUTE11 => l_party_rec.attribute11,
X_ATTRIBUTE12 => l_party_rec.attribute12,
X_ATTRIBUTE13 => l_party_rec.attribute13,
X_ATTRIBUTE14 => l_party_rec.attribute14,
X_ATTRIBUTE15 => l_party_rec.attribute15,
X_ATTRIBUTE16 => l_party_rec.attribute16,
X_ATTRIBUTE17 => l_party_rec.attribute17,
X_ATTRIBUTE18 => l_party_rec.attribute18,
X_ATTRIBUTE19 => l_party_rec.attribute19,
X_ATTRIBUTE20 => l_party_rec.attribute20,
X_ATTRIBUTE21 => l_party_rec.attribute21,
X_ATTRIBUTE22 => l_party_rec.attribute22,
X_ATTRIBUTE23 => l_party_rec.attribute23,
X_ATTRIBUTE24 => l_party_rec.attribute24,
X_ORIG_SYSTEM_REFERENCE => l_party_rec.orig_system_reference,
X_SIC_CODE => null,
X_HQ_BRANCH_IND => null,
X_CUSTOMER_KEY => null,
X_TAX_REFERENCE => null,
X_JGZZ_FISCAL_CODE => null,
X_PERSON_PRE_NAME_ADJUNCT => null,
X_PERSON_FIRST_NAME => null,
X_PERSON_MIDDLE_NAME => null,
X_PERSON_LAST_NAME => null,
X_PERSON_NAME_SUFFIX => null,
X_PERSON_TITLE => null,
X_PERSON_ACADEMIC_TITLE => null,
X_PERSON_PREVIOUS_LAST_NAME => null,
X_KNOWN_AS => null,
X_PERSON_IDEN_TYPE => null,
X_PERSON_IDENTIFIER => null,
X_GROUP_TYPE => null,
X_COUNTRY => NULL,
X_ADDRESS1 => NULL,
X_ADDRESS2 => NULL,
X_ADDRESS3 => NULL,
X_ADDRESS4 => NULL,
X_CITY => NULL,
X_POSTAL_CODE => NULL,
X_STATE => NULL,
X_PROVINCE => NULL,
X_STATUS => l_party_rec.status,
X_COUNTY => NULL,
X_SIC_CODE_TYPE => null,
X_URL => NULL,
X_EMAIL_ADDRESS => NULL,
X_ANALYSIS_FY => null,
X_FISCAL_YEAREND_MONTH => null,
X_EMPLOYEES_TOTAL => null,
X_CURR_FY_POTENTIAL_REVENUE => null,
X_NEXT_FY_POTENTIAL_REVENUE => null,
X_YEAR_ESTABLISHED => null,
X_GSA_INDICATOR_FLAG => null,
X_MISSION_STATEMENT => null,
X_ORGANIZATION_NAME_PHONETIC => null,
X_PERSON_FIRST_NAME_PHONETIC => null,
X_PERSON_LAST_NAME_PHONETIC => null,
X_LANGUAGE_NAME => NULL,
X_CATEGORY_CODE => l_party_rec.category_code,
X_SALUTATION => l_party_rec.salutation,
X_KNOWN_AS2 => null,
X_KNOWN_AS3 => null,
X_KNOWN_AS4 => null,
X_KNOWN_AS5 => null,
X_OBJECT_VERSION_NUMBER => 1,
X_DUNS_NUMBER_C => null,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Insert_Row (-) ' ||
'x_party_id = ' || x_party_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
UPDATE HZ_PARTIES SET PARTY_NAME = l_party_name WHERE PARTY_ID = x_party_id;
| do_update_party
|
| DESCRIPTION
| Updates person and party for person.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_party_type
| OUT:
| x_profile_id
| IN/ OUT:
| p_person_rec
| p_organization_rec
| p_group_rec
| p_party_rel_rec
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_party(
p_party_type IN VARCHAR2,
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_old_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_profile_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE := p_relationship_rec.party_rec;
hz_utility_v2pub.debug(p_message=>'do_update_party (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT NVL(OBJECT_VERSION_NUMBER,1),
ROWID,
CREATED_BY_MODULE
INTO l_party_object_version_number,
l_rowid,
db_created_by_module
FROM HZ_PARTIES
WHERE PARTY_ID = l_party_rec.party_id
FOR UPDATE OF PARTY_ID NOWAIT;
hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Update_Row (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
HZ_PARTIES_PKG.Update_Row (
X_Rowid => l_rowid,
X_PARTY_ID => l_party_rec.party_id,
X_PARTY_NUMBER => NULL,
X_PARTY_NAME => NULL,
-- X_VALIDATED_FLAG => l_party_rec.validated_flag, -- Bug #6341070
X_VALIDATED_FLAG => NULL, -- Set NULL value as per Bug #6341070
X_PARTY_TYPE => p_party_type,
X_ATTRIBUTE_CATEGORY => l_party_rec.attribute_category,
X_ATTRIBUTE1 => l_party_rec.attribute1,
X_ATTRIBUTE2 => l_party_rec.attribute2,
X_ATTRIBUTE3 => l_party_rec.attribute3,
X_ATTRIBUTE4 => l_party_rec.attribute4,
X_ATTRIBUTE5 => l_party_rec.attribute5,
X_ATTRIBUTE6 => l_party_rec.attribute6,
X_ATTRIBUTE7 => l_party_rec.attribute7,
X_ATTRIBUTE8 => l_party_rec.attribute8,
X_ATTRIBUTE9 => l_party_rec.attribute9,
X_ATTRIBUTE10 => l_party_rec.attribute10,
X_ATTRIBUTE11 => l_party_rec.attribute11,
X_ATTRIBUTE12 => l_party_rec.attribute12,
X_ATTRIBUTE13 => l_party_rec.attribute13,
X_ATTRIBUTE14 => l_party_rec.attribute14,
X_ATTRIBUTE15 => l_party_rec.attribute15,
X_ATTRIBUTE16 => l_party_rec.attribute16,
X_ATTRIBUTE17 => l_party_rec.attribute17,
X_ATTRIBUTE18 => l_party_rec.attribute18,
X_ATTRIBUTE19 => l_party_rec.attribute19,
X_ATTRIBUTE20 => l_party_rec.attribute20,
X_ATTRIBUTE21 => l_party_rec.attribute21,
X_ATTRIBUTE22 => l_party_rec.attribute22,
X_ATTRIBUTE23 => l_party_rec.attribute23,
X_ATTRIBUTE24 => l_party_rec.attribute24,
X_ORIG_SYSTEM_REFERENCE => l_party_rec.orig_system_reference,
X_SIC_CODE => null,
X_HQ_BRANCH_IND => null,
X_CUSTOMER_KEY => null,
X_TAX_REFERENCE => null,
X_JGZZ_FISCAL_CODE => null,
X_PERSON_PRE_NAME_ADJUNCT => null,
X_PERSON_FIRST_NAME => null,
X_PERSON_MIDDLE_NAME => null,
X_PERSON_LAST_NAME => null,
X_PERSON_NAME_SUFFIX => null,
X_PERSON_TITLE => null,
X_PERSON_ACADEMIC_TITLE => null,
X_PERSON_PREVIOUS_LAST_NAME => null,
X_KNOWN_AS => null,
X_PERSON_IDEN_TYPE => null,
X_PERSON_IDENTIFIER => null,
X_GROUP_TYPE => null,
X_COUNTRY => NULL,
X_ADDRESS1 => NULL,
X_ADDRESS2 => NULL,
X_ADDRESS3 => NULL,
X_ADDRESS4 => NULL,
X_CITY => NULL,
X_POSTAL_CODE => NULL,
X_STATE => NULL,
X_PROVINCE => NULL,
X_STATUS => l_party_rec.status,
X_COUNTY => NULL,
X_SIC_CODE_TYPE => null,
X_URL => NULL,
X_EMAIL_ADDRESS => NULL,
X_ANALYSIS_FY => null,
X_FISCAL_YEAREND_MONTH => null,
X_EMPLOYEES_TOTAL => null,
X_CURR_FY_POTENTIAL_REVENUE => null,
X_NEXT_FY_POTENTIAL_REVENUE => null,
X_YEAR_ESTABLISHED => null,
X_GSA_INDICATOR_FLAG => null,
X_MISSION_STATEMENT => null,
X_ORGANIZATION_NAME_PHONETIC => null,
X_PERSON_FIRST_NAME_PHONETIC => null,
X_PERSON_LAST_NAME_PHONETIC => null,
X_LANGUAGE_NAME => NULL,
X_CATEGORY_CODE => l_party_rec.category_code,
X_SALUTATION => l_party_rec.salutation,
X_KNOWN_AS2 => null,
X_KNOWN_AS3 => null,
X_KNOWN_AS4 => null,
X_KNOWN_AS5 => null,
X_OBJECT_VERSION_NUMBER => p_party_object_version_number,
X_DUNS_NUMBER_C => null,
X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
X_APPLICATION_ID => p_relationship_rec.application_id
);
hz_utility_v2pub.debug(p_message=>'HZ_PARTIES_PKG.Update_Row (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'do_update_party (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_party;
| For non-profile entities, the concept of select
| /de-select data-sources is obsoleted.
+===========================================================================*/
PROCEDURE create_relationship (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
x_relationship_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER,
x_party_number OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
create_relationship_with_usg (
p_init_msg_list => p_init_msg_list,
p_relationship_rec => p_relationship_rec,
p_contact_party_id => null,
p_contact_party_usage_code => null,
p_create_org_contact => 'Y',
x_relationship_id => x_relationship_id,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_RELATIONSHIPS',
p_entity_attr_id => g_rel_entity_attr_id,
p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
p_selected_datasources => g_rel_selected_datasources );
p_selected_datasources => g_rel_selected_datasources,
p_content_source_type => l_rel_rec.content_source_type,
p_actual_content_source => l_rel_rec.actual_content_source,
x_is_datasource_selected => g_rel_is_datasource_selected,
x_return_status => x_return_status );
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_RELATIONSHIPS',
p_instance_pk1_value => x_relationship_id,
p_instance_pk2_value => 'F',
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
g_rel_is_datasource_selected = 'Y' */
THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
HZ_BUSINESS_EVENT_V2PVT.create_relationship_event (
l_rel_rec, l_created_party );
| update_relationship
|
| DESCRIPTION
| Updates relationship and party for party_relationship.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_init_msg_list
| p_party_rel_rec
| OUT:
| x_return_status
| x_msg_count
| x_msg_data
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| For non-profile entities, the concept of select
| /de-select data-sources is obsoleted.
+===========================================================================*/
PROCEDURE update_relationship (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_relationship_rec IN RELATIONSHIP_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
p_party_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_rel_rec RELATIONSHIP_REC_TYPE := p_relationship_rec;
SAVEPOINT update_relationship;
hz_utility_v2pub.debug(p_message=>'update_relationship (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_RELATIONSHIPS',
p_instance_pk1_value => l_rel_rec.relationship_id,
p_instance_pk2_value => 'F',
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
* There is no need to check if the data-source is selected.
-- IF g_rel_mixnmatch_enabled IS NULL THEN
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_RELATIONSHIPS',
p_entity_attr_id => g_rel_entity_attr_id,
p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
p_selected_datasources => g_rel_selected_datasources );
g_rel_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_rel_selected_datasources,
p_actual_content_source => l_old_rel_rec.actual_content_source );
do_update_rel(
l_rel_rec,
l_old_rel_rec,
p_object_version_number,
p_party_object_version_number,
x_return_status);
g_rel_is_datasource_selected = 'Y' */
THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
HZ_BUSINESS_EVENT_V2PVT.update_relationship_event (
l_rel_rec, l_old_rel_rec );
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_relationship;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_relationship;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_relationship;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_relationship (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_relationship;
hz_utility_v2pub.debug(p_message=>'HZ_RELATIONSHIPS_PKG.Select_Row',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
HZ_RELATIONSHIPS_PKG.Select_Row (
X_RELATIONSHIP_ID => x_rel_rec.relationship_id,
X_DIRECTIONAL_FLAG => l_directional_flag,
X_SUBJECT_ID => x_rel_rec.subject_id,
X_SUBJECT_TYPE => x_rel_rec.subject_type,
X_SUBJECT_TABLE_NAME => x_rel_rec.subject_table_name,
X_OBJECT_ID => x_rel_rec.object_id,
X_OBJECT_TYPE => x_rel_rec.object_type,
X_OBJECT_TABLE_NAME => x_rel_rec.object_table_name,
X_PARTY_ID => l_party_id,
X_RELATIONSHIP_CODE => x_rel_rec.relationship_code,
X_COMMENTS => x_rel_rec.comments,
X_START_DATE => x_rel_rec.start_date,
X_END_DATE => x_rel_rec.end_date,
X_STATUS => x_rel_rec.status,
X_ATTRIBUTE_CATEGORY => x_rel_rec.attribute_category,
X_ATTRIBUTE1 => x_rel_rec.attribute1,
X_ATTRIBUTE2 => x_rel_rec.attribute2,
X_ATTRIBUTE3 => x_rel_rec.attribute3,
X_ATTRIBUTE4 => x_rel_rec.attribute4,
X_ATTRIBUTE5 => x_rel_rec.attribute5,
X_ATTRIBUTE6 => x_rel_rec.attribute6,
X_ATTRIBUTE7 => x_rel_rec.attribute7,
X_ATTRIBUTE8 => x_rel_rec.attribute8,
X_ATTRIBUTE9 => x_rel_rec.attribute9,
X_ATTRIBUTE10 => x_rel_rec.attribute10,
X_ATTRIBUTE11 => x_rel_rec.attribute11,
X_ATTRIBUTE12 => x_rel_rec.attribute12,
X_ATTRIBUTE13 => x_rel_rec.attribute13,
X_ATTRIBUTE14 => x_rel_rec.attribute14,
X_ATTRIBUTE15 => x_rel_rec.attribute15,
X_ATTRIBUTE16 => x_rel_rec.attribute16,
X_ATTRIBUTE17 => x_rel_rec.attribute17,
X_ATTRIBUTE18 => x_rel_rec.attribute18,
X_ATTRIBUTE19 => x_rel_rec.attribute19,
X_ATTRIBUTE20 => x_rel_rec.attribute20,
X_CONTENT_SOURCE_TYPE => x_rel_rec.content_source_type,
X_RELATIONSHIP_TYPE => x_rel_rec.relationship_type,
X_CREATED_BY_MODULE => x_rel_rec.created_by_module,
X_APPLICATION_ID => x_rel_rec.application_id,
X_ADDITIONAL_INFORMATION1 => x_rel_rec.additional_information1,
X_ADDITIONAL_INFORMATION2 => x_rel_rec.additional_information2,
X_ADDITIONAL_INFORMATION3 => x_rel_rec.additional_information3,
X_ADDITIONAL_INFORMATION4 => x_rel_rec.additional_information4,
X_ADDITIONAL_INFORMATION5 => x_rel_rec.additional_information5,
X_ADDITIONAL_INFORMATION6 => x_rel_rec.additional_information6,
X_ADDITIONAL_INFORMATION7 => x_rel_rec.additional_information7,
X_ADDITIONAL_INFORMATION8 => x_rel_rec.additional_information8,
X_ADDITIONAL_INFORMATION9 => x_rel_rec.additional_information9,
X_ADDITIONAL_INFORMATION10 => x_rel_rec.additional_information10,
X_ADDITIONAL_INFORMATION11 => x_rel_rec.additional_information11,
X_ADDITIONAL_INFORMATION12 => x_rel_rec.additional_information12,
X_ADDITIONAL_INFORMATION13 => x_rel_rec.additional_information13,
X_ADDITIONAL_INFORMATION14 => x_rel_rec.additional_information14,
X_ADDITIONAL_INFORMATION15 => x_rel_rec.additional_information15,
X_ADDITIONAL_INFORMATION16 => x_rel_rec.additional_information16,
X_ADDITIONAL_INFORMATION17 => x_rel_rec.additional_information17,
X_ADDITIONAL_INFORMATION18 => x_rel_rec.additional_information18,
X_ADDITIONAL_INFORMATION19 => x_rel_rec.additional_information19,
X_ADDITIONAL_INFORMATION20 => x_rel_rec.additional_information20,
X_ADDITIONAL_INFORMATION21 => x_rel_rec.additional_information21,
X_ADDITIONAL_INFORMATION22 => x_rel_rec.additional_information22,
X_ADDITIONAL_INFORMATION23 => x_rel_rec.additional_information23,
X_ADDITIONAL_INFORMATION24 => x_rel_rec.additional_information24,
X_ADDITIONAL_INFORMATION25 => x_rel_rec.additional_information25,
X_ADDITIONAL_INFORMATION26 => x_rel_rec.additional_information26,
X_ADDITIONAL_INFORMATION27 => x_rel_rec.additional_information27,
X_ADDITIONAL_INFORMATION28 => x_rel_rec.additional_information28,
X_ADDITIONAL_INFORMATION29 => x_rel_rec.additional_information29,
X_ADDITIONAL_INFORMATION30 => x_rel_rec.additional_information30,
X_DIRECTION_CODE => l_direction_code,
X_PERCENTAGE_OWNERSHIP => x_rel_rec.percentage_ownership,
X_ACTUAL_CONTENT_SOURCE => x_rel_rec.actual_content_source
);