[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_exception_msg := 'pos_create_relationship():select object_type';
select party_type
into l_object_type
from hz_parties
where party_id = p_object_id;
x_exception_msg := 'select subject_type';
select party_type
into l_subject_type
from hz_parties
where party_id = p_subject_id;
select count(*)
into l_count
from hz_relationships
where object_table_name = p_object_table_name
and subject_table_name = p_subject_table_name
and object_type = p_party_object_type
and subject_type = p_party_subject_type
and relationship_code = p_relationship_code
and relationship_type = p_relationship_type
and object_id = p_object_id
and subject_id = p_subject_id;
update hz_relationships
set end_date := SYSDATE, status := 'A'
where object_table_name = p_object_table_name
and subject_table_name = p_subject_table_name
and object_type = p_party_object_type
and subject_type = p_party_subject_type
and relationship_code = p_relationship_code
and relationship_type = p_relationship_type
and object_id = p_object_id
and subject_id = p_subject_id;
procedure pos_hz_update_relationship(p_subject_id IN NUMBER,
p_object_id IN NUMBER,
p_relationship_type IN VARCHAR2,
p_relationship_code IN VARCHAR2,
p_party_object_type IN VARCHAR2,
p_party_subject_type IN VARCHAR2,
p_subject_table_name IN VARCHAR2,
p_object_table_name IN VARCHAR2,
-- p_relationship_status IN VARCHAR2, -- should not be updated
p_relationship_start_date IN DATE, -- can be null
p_relationship_end_date IN DATE, -- can be null
p_relationship_id IN NUMBER,
p_object_version_number in number,
p_rel_last_update_date IN OUT NOCOPY DATE,
p_party_last_update_date IN OUT NOCOPY DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_exception_msg OUT NOCOPY VARCHAR2)
IS
l_rel_rec hz_relationship_v2pub.relationship_rec_type;
l_rel_last_update_date date;
l_party_last_update_date date;
l_proc_name := 'pos_hz_update_relationship';
/* Do not update the l_rel_rec.status variable. Because this value
is slightly confusing is typically handled by TCA.
*/
hz_relationship_v2pub.update_relationship(
--p_api_version => 1.0
p_init_msg_list => FND_API.G_TRUE
--,p_commit => FND_API.G_FALSE
,p_relationship_rec => l_rel_rec
,p_object_version_number => l_obj_ver
,p_party_object_version_number => l_party_obj_ver
--,p_rel_last_update_date => l_rel_last_update_date
--,p_party_last_update_date => l_party_last_update_date
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
--,x_relationship_id => l_party_relationship_id
--,x_party_id => l_party_id
--,x_party_number => l_party_number
);
raise_application_error(-20001,'POS_HZ_RELATIONSHIPS_PKG:pos_outdate_relationship(): Update relationship failed :' || x_exception_msg, true);
raise_application_error(-20001,'POS_HZ_RELATIONSHIPS_PKG:pos_outdate_relationship(): Update relationship failed :' || x_exception_msg,true);
END pos_hz_update_relationship;
x_exception_msg := 'pos_outdate_relationship():select object_type';
select party_type
into l_object_type
from hz_parties
where party_id = p_object_id;
x_exception_msg := 'select subject_type';
select party_type
into l_subject_type
from hz_parties
where party_id = p_subject_id;
x_exception_msg := 'selecting distinct relationship id';
select distinct relationship_id, object_version_number
into l_relationship_id, l_obj_ver
from hz_relationships
where start_date <= sysdate
and end_date >= sysdate
and status = 'A'
and object_table_name = 'HZ_PARTIES'
and subject_table_name = 'HZ_PARTIES'
and object_type = l_object_type
and subject_type = l_subject_type
and relationship_code = p_relationship_code
and relationship_type = p_relationship_type
and object_id = p_object_id
and subject_id = p_subject_id;
x_exception_msg := 'Calling the pos_hz_update_relationship';
pos_hz_update_relationship( p_subject_id => p_subject_id
, p_object_id => p_object_id
, p_relationship_type => p_relationship_type
, p_relationship_code => p_relationship_code
, p_party_object_type => l_object_type
, p_party_subject_type => l_subject_type
, p_subject_table_name => 'HZ_PARTIES'
, p_object_table_name => 'HZ_PARTIES'
, p_relationship_end_date => SYSDATE
, p_relationship_start_date => NULL
, p_relationship_id => l_relationship_id
, p_object_version_number => l_obj_ver
, p_rel_last_update_date => l_rel_date
, p_party_last_update_date => l_party_date
, x_return_status => x_return_status
, x_exception_msg => x_exception_msg
);
select object_id
into l_object_id
from hz_relationships
where subject_id = p_subject_id
and relationship_type = p_relationship_type
and relationship_code = p_relationship_code
and status = 'A'
and start_date <= sysdate
and end_date >= sysdate;
l_exception_msg := 'POSHZRPB:V2:Calling hz update relationship:';
hz_relationship_v2pub.update_relationship(
p_init_msg_list => FND_API.G_TRUE
,p_relationship_rec => l_rel_rec
,p_object_version_number => l_obj_ver
,p_party_object_version_number => l_party_obj_ver
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);