[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode ( attr_value,'GLOBAL','Y','N')
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
SELECT 'Y'
FROM hz_relationships rel
, pv_partner_profiles prof
WHERE rel.status='A'
AND prof.partner_id=p_ptr_id
AND relationship_type = 'PARTNER_HIERARCHY'
AND rel.subject_id = prof.partner_party_id
AND rel.relationship_code = 'SUBSIDIARY_OF'
AND rel.start_date <= SYSDATE
AND ( rel.end_date is null or rel.end_date>=sysdate);
SELECT 'X'
FROM PV_ATTRIBUTE_CODES_VL
WHERE ATTRIBUTE_ID = 6
AND ENABLED_FLAG = 'Y'
AND ATTR_CODE =attr_cd;
SELECT 'Y'
FROM pv_partner_profiles
WHERE partner_id=ptr_id
AND STATUS='A';
SELECT attr_value,version
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
SELECT version
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
SELECT partner_party_id
FROM pv_partner_profiles
WHERE partner_id=p_ptr_id;
SELECT subs_prof.partner_id
, subs_prof.partner_party_id
, subs_enty_val.version
, rel.relationship_id
, rel.object_version_number
, rel.status
, rel.start_date
FROM pv_partner_profiles subs_prof
, pv_partner_profiles global_prof
, pv_enty_attr_values subs_enty_val
, hz_relationships rel
WHERE
global_prof.partner_id = p_ptr_id
AND global_prof.partner_party_id = rel.subject_id
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.object_id = subs_prof.partner_party_id
AND rel.relationship_code = 'PARENT_OF'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.entity_id = subs_prof.partner_id
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY';
SELECT subs_prof.partner_id
, subs_prof.partner_party_id
, subs_enty_val.version
, rel.relationship_id
, rel.object_version_number
, rel.status
, rel.start_date
FROM pv_partner_profiles subs_prof
, pv_partner_profiles global_prof
, pv_enty_attr_values subs_enty_val
, pv_enty_attr_values global_enty_val
, hz_relationships rel
WHERE global_enty_val.entity = 'PARTNER'
AND global_enty_val.entity_id = global_prof.partner_id
AND global_enty_val.attribute_id = 6
AND global_enty_val.latest_flag = 'Y'
AND global_enty_val.attr_value = 'GLOBAL'
AND global_prof.partner_id = p_ptr_id
AND global_prof.partner_party_id = rel.subject_id
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.object_id = subs_prof.partner_party_id
AND rel.relationship_code = 'PARENT_OF'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.entity_id = subs_prof.partner_id
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY';
SELECT rel.relationship_id relationship_id
, rel.start_date
, rel.object_version_number object_version_number
, subs_prof.partner_party_id partner_party_id
FROM pv_partner_profiles subs_prof
, hz_relationships rel
WHERE rel.subject_id=subs_prof.partner_party_id
AND rel.relationship_code = 'SUBSIDIARY_OF'
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_prof.partner_id=p_subs_ptr_id;
SELECT rel.relationship_id relationship_id
, rel.start_date
, rel.object_version_number object_version_number
, subs_prof.partner_party_id partner_party_id
, subs_enty_val.version version
FROM pv_partner_profiles subs_prof
, pv_enty_attr_values subs_enty_val
, hz_relationships rel
WHERE rel.subject_id=subs_prof.partner_party_id
AND rel.relationship_code = 'SUBSIDIARY_OF'
AND rel.relationship_type = 'PARTNER_HIERARCHY'
AND rel.status = 'A'
AND NVL(rel.start_date, SYSDATE) <= SYSDATE
AND NVL(rel.end_date, SYSDATE) >= SYSDATE
AND subs_prof.partner_id=p_subs_ptr_id
AND subs_enty_val.entity = 'PARTNER'
AND subs_enty_val.entity_id = p_subs_ptr_id
AND subs_enty_val.attribute_id = 6
AND subs_enty_val.latest_flag = 'Y'
AND subs_enty_val.attr_value = 'SUBSIDIARY';
SELECT object_version_number
FROM hz_parties
WHERE party_id=p_ptnr_party_id;
SELECT DESCRIPTION
FROM PV_ATTRIBUTE_CODES_VL
WHERE ATTRIBUTE_ID = 6
AND ENABLED_FLAG = 'Y'
AND ATTR_CODE =attr_cd;
SELECT party_name
FROM hz_parties party
,pv_partner_profiles prof
WHERE prof.partner_id=p_partner_id
AND prof.partner_party_id=party.party_id;
HZ_RELATIONSHIP_V2PUB.update_relationship
(
p_init_msg_list => FND_API.g_false
,p_relationship_rec => l_relationship_rec
,p_object_version_number => subs.object_version_number
,p_party_object_version_number => l_party_obj_ver_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
HZ_RELATIONSHIP_V2PUB.update_relationship
(
p_init_msg_list => FND_API.g_false
,p_relationship_rec => l_relationship_rec
,p_object_version_number => l_object_version_number
,p_party_object_version_number => l_party_obj_ver_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT attr_value
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
once to terminate the existing relationship and update profile attribute value
by passing p_new_membtype as null
Call it again to create new relationship and update profile attributes
by passing the p_new_membtype with the member type you want to tag the partner with
the values would be STANDARD,GLOBAL,SUBSIDIARY.
But if the partner is getting registered for the first time , you just need to call
Register_term_ptr_memb_type once with p_new_membtype = to the member type.
b). Terminate_ptr_memberships to terminate all the program memberships.
*/
-- Standard Start of API savepoint
SAVEPOINT Process_ptr_member_type;
select 1 from dual where exists
(select 1 from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
where hzp.party_number= cv_orgzn_number
and hzp.party_id = pvpp.partner_party_id
and pvpp.status = 'A'
and pvpp.partner_id = pvev.entity_id
and pvev.entity = 'PARTNER'
and pvev.enabled_flag = 'Y'
and pvev.latest_flag = 'Y'
and pvev.attr_value = 'GLOBAL'
and pvev.attribute_id = 6
);
select pvpp.partner_id from hz_parties hzp, pv_partner_profiles pvpp, pv_enty_attr_values pvev
where hzp.party_number= cv_orgzn_number
and hzp.party_id = pvpp.partner_party_id
and pvpp.status = 'A'
and pvpp.partner_id = pvev.entity_id
and pvev.entity = 'PARTNER'
and pvev.enabled_flag = 'Y'
and pvev.latest_flag = 'Y'
and pvev.attr_value = 'GLOBAL';
SELECT status
, subject_id
FROM hz_relationships
WHERE relationship_id= id;
SELECT partner_id
FROM pv_partner_profiles prof
, hz_relationships rel
, pv_enty_attr_values enty
WHERE rel.relationship_id = rel_id
AND rel.relationship_type= 'PARTNER_HIERARCHY'
AND rel.relationship_code= 'SUBSIDIARY_OF'
AND rel.subject_id=prof.partner_party_id
AND prof.partner_id=enty.entity_id
AND enty.attribute_id=6
AND enty.latest_flag='Y'
AND enty.attr_value='SUBSIDIARY';
SELECT sprof.partner_id subsidiary_partner_id
, gprof.partner_id global_partner_id
, enty.attr_value attr_value
FROM pv_partner_profiles sprof
, pv_partner_profiles gprof
, hz_relationships rel
, pv_enty_attr_values enty
WHERE rel.relationship_id = rel_id
AND rel.relationship_type= 'PARTNER_HIERARCHY'
AND rel.relationship_code= 'SUBSIDIARY_OF'
AND rel.subject_id=sprof.partner_party_id
AND rel.object_id= gprof.partner_party_id
AND gprof.partner_id=enty.entity_id
AND enty.attribute_id=6
AND enty.latest_flag='Y' ;
SELECT version,attr_value
FROM pv_enty_attr_values
WHERE entity='PARTNER'
AND entity_id=p_ptr_id
AND attribute_id=6
AND latest_flag='Y';
IF ( l_key like 'oracle.apps.ar.hz.Relationship.update%' OR l_key like 'oracle.apps.ar.hz.Relationship.create%' ) THEN
l_org_id := p_event.GetValueForParameter('ORG_ID');
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE SUBJECT_ID = l_subject_id -- subsidiary partner party id
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TYPE = 'ORGANIZATION'
AND RELATIONSHIP_TYPE = 'PARTNER_HIERARCHY'
AND DIRECTION_CODE = 'C'
AND STATUS='A';
END IF; -- end of IF ( l_key like 'oracle.apps.ar.hz.Relationship.update%' OR l_key like 'oracle.apps.ar.hz.Relationship.create%' ) THEN
PROCEDURE update_partner_dtl
(
p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_partner_id IN NUMBER
, p_old_partner_status IN VARCHAR2
, p_new_partner_status IN VARCHAR2
, p_chg_from_memb_type IN VARCHAR2
, p_chg_to_memb_type IN VARCHAR2
, p_old_global_ptr_id IN NUMBER DEFAULT NULL
, p_new_global_ptr_id IN NUMBER DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_partner_dtl';
SELECT DESCRIPTION
FROM PV_ATTRIBUTE_CODES_VL
WHERE ATTRIBUTE_ID = 6
AND ENABLED_FLAG = 'Y'
AND ATTR_CODE =attr_cd;
SELECT party_name
FROM hz_parties party
,pv_partner_profiles prof
WHERE prof.partner_id=p_partner_id
AND prof.partner_party_id=party.party_id;
SAVEPOINT update_partner_dtl ;
ROLLBACK TO update_partner_dtl;
ROLLBACK TO update_partner_dtl;
ROLLBACK TO update_partner_dtl;
END update_partner_dtl;