The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_update_required IN OUT NOCOPY VARCHAR2,
x_relationship_id IN OUT NOCOPY NUMBER,
x_ocon_version_number IN OUT NOCOPY NUMBER );
PROCEDURE upd_ocon_update
(p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type);
PROCEDURE object_version_select
(p_table_name IN VARCHAR2,
p_col_id IN VARCHAR2,
x_rowid IN OUT NOCOPY ROWID,
x_object_version_number IN OUT NOCOPY NUMBER,
x_last_update_date IN OUT NOCOPY DATE,
x_id_value IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2 )
IS
CURSOR cu_party_version IS
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
NULL
FROM HZ_PARTIES
WHERE PARTY_ID = p_col_id;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
PARTY_RELATIONSHIP_ID
FROM HZ_ORG_CONTACTS
WHERE ORG_CONTACT_ID = p_col_id;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
PARTY_ID
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_ID = p_col_id
AND DIRECTIONAL_FLAG = 'F';
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
NULL
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_col_id;
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
NULL
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_col_id;
l_last_update_date DATE;
l_last_update_date ,
x_id_value;
l_last_update_date ,
x_id_value;
l_last_update_date ,
x_id_value;
l_last_update_date ,
x_id_value;
l_last_update_date ,
x_id_value;
IF TO_CHAR(x_last_update_date,'DD-MON-YYYY HH:MI:SS') <>
TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
PROCEDURE update_mail_stop
( p_org_contact_id IN NUMBER,
p_mail_stop IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_data IN OUT NOCOPY VARCHAR2)
IS
CURSOR cu_org_contact_update
IS
SELECT org_contact_id
FROM hz_org_contacts
WHERE org_contact_id = p_org_contact_id
FOR UPDATE OF org_contact_id NOWAIT;
OPEN cu_org_contact_update;
FETCH cu_org_contact_update INTO l_lock;
IF cu_org_contact_update%FOUND THEN
UPDATE hz_org_contacts
SET mail_stop = p_mail_stop
WHERE org_contact_id = p_org_contact_id;
CLOSE cu_org_contact_update;
IF cu_org_contact_update%ISOPEN THEN
CLOSE cu_org_contact_update;
select 1
into dummy
from dual
where not exists ( select 1
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and rel.subject_id = party.party_id
and party.person_last_name = x_last_name
and party.person_first_name = x_first_name
and acct_role.cust_account_id = x_customer_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
);
select 1
into dummy
from dual
where not exists ( select 1
from hz_cust_account_roles acct_role
where acct_role.orig_system_reference
= x_orig_system_reference
);
PROCEDURE Insert_Row(
X_Contact_Id IN OUT NOCOPY NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Customer_Id NUMBER,
X_Last_Name VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
X_Status VARCHAR2,
X_Address_Id NUMBER,
X_Contact_Key VARCHAR2,
X_First_Name VARCHAR2,
X_Job_Title VARCHAR2,
X_Last_Update_Login NUMBER,
X_Mail_Stop VARCHAR2,
X_Title VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Email_Address VARCHAR2,
X_Last_Name_Alt VARCHAR2 DEFAULT NULL,
X_First_Name_Alt VARCHAR2 DEFAULT NULL,
X_Contact_Number IN OUT NOCOPY VARCHAR2,
X_Party_Id NUMBER,
X_Party_Site_Id NUMBER,
X_Contact_Party_Id IN OUT NOCOPY NUMBER,
X_Org_Contact_Id IN OUT NOCOPY NUMBER,
X_Contact_Point_Id IN OUT NOCOPY NUMBER,
X_Cust_Account_Role_Id IN OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
) IS
i_rel_party_id NUMBER;
Insert_Row( X_Contact_Id
, X_Created_By
, X_Creation_Date
, X_Customer_Id
, X_Last_Name
, X_Last_Updated_By
, X_Last_Update_Date
, X_Orig_System_Reference
, X_Status
, X_Address_Id
, X_Contact_Key
, X_First_Name
, X_Job_Title
, I_Job_Title_Code
, X_Last_Update_Login
, X_Mail_Stop
, X_Title
, X_Attribute_Category
, X_Attribute1
, X_Attribute2
, X_Attribute3
, X_Attribute4
, X_Attribute5
, X_Attribute6
, X_Attribute7
, X_Attribute8
, X_Attribute9
, X_Attribute10
, X_Attribute11
, X_Attribute12
, X_Attribute13
, X_Attribute14
, X_Attribute15
, X_Attribute16
, X_Attribute17
, X_Attribute18
, X_Attribute19
, X_Attribute20
, X_Attribute21
, X_Attribute22
, X_Attribute23
, X_Attribute24
, X_Attribute25
, X_Email_Address
, X_Last_Name_Alt
, X_First_Name_Alt
, X_Contact_Number
, X_Party_Id
, i_sub_party_id
, X_Party_Site_Id
, X_Contact_Party_Id
, i_rel_party_id
, X_Org_Contact_Id
, X_Contact_Point_Id
, X_Cust_Account_Role_Id
, i_return_status
, i_msg_count
, i_msg_data
);
END Insert_Row;
PROCEDURE Insert_Row(
X_Contact_Id IN OUT NOCOPY NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE,
X_Customer_Id NUMBER,
X_Last_Name VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
X_Status VARCHAR2,
X_Address_Id NUMBER,
X_Contact_Key VARCHAR2,
X_First_Name VARCHAR2,
X_Job_Title VARCHAR2,
X_Job_Title_Code VARCHAR2,
X_Last_Update_Login NUMBER,
X_Mail_Stop VARCHAR2,
X_Title VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Email_Address VARCHAR2,
X_Last_Name_Alt VARCHAR2 DEFAULT NULL,
X_First_Name_Alt VARCHAR2 DEFAULT NULL,
X_Contact_Number IN OUT NOCOPY VARCHAR2,
X_Party_Id NUMBER,
X_Sub_Party_Id NUMBER,
X_Party_Site_Id NUMBER,
X_Contact_Party_Id IN OUT NOCOPY NUMBER,
X_Rel_Party_Id IN OUT NOCOPY NUMBER,
X_Org_Contact_Id IN OUT NOCOPY NUMBER,
X_Contact_Point_Id IN OUT NOCOPY NUMBER,
X_Cust_Account_Role_Id IN OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
) IS
--party_rec hz_party_pub.party_rec_type;
SELECT party_type
FROM hz_parties
WHERE party_id = i_party_id;
l_update_required VARCHAR2(1) := 'N';
arp_standard.debug('arh_cont_pkg.insert_row +');
SELECT hz_contact_numbers_s.nextval INTO X_Contact_Number FROM DUAL;
SELECT hz_org_contacts_s.nextval INTO x_contact_id FROM DUAL;
select hz_party_number_s.nextval into i_subject_party_number from dual;
select hz_parties_s.nextval into i_subject_party_id from dual;
select hz_contact_points_s.nextval into i_contact_point_id from dual;
select hz_cust_account_roles_s.nextval into x_cust_account_role_id from dual;
select hz_cust_contact_points_s.nextval into x_cust_contact_point_id from dual;
SELECT hz_party_number_s.nextval INTO ii_party_number FROM DUAL;
update_mail_stop
( p_org_contact_id => x_org_contact_id,
p_mail_stop => X_Mail_Stop,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
x_update_required => l_update_required,
x_relationship_id => l_relationship_id,
x_ocon_version_number => l_ocon_version_number );
arp_standard.debug(' l_update_required :'||l_update_required);
IF l_update_required = 'Y' THEN
arp_standard.debug('Calling upd_ocon_update ');
upd_ocon_update(p_ocon_rec => ocon_rec);
SELECT p.party_id
INTO cpoint_rec.owner_table_id
FROM hz_relationships p,
hz_org_contacts o
WHERE o.org_contact_id = x_org_contact_id
AND o.party_relationship_id = p.relationship_id
AND p.subject_table_name = 'HZ_PARTIES'
AND p.object_table_name = 'HZ_PARTIES'
AND p.directional_flag = 'F';
update hz_cust_account_roles
set orig_system_reference = x_orig_system_reference
where cust_account_role_id = x_cust_account_role_id;
select orig_system_reference
into x_orig_system_reference
from hz_cust_account_roles
where cust_account_role_id = x_cust_account_role_id;
select cust_account_role_id
into i_lock_id
from hz_cust_account_roles
where cust_account_role_id = x_cust_account_role_id
for update of cust_account_role_id nowait;
update hz_cust_account_roles
set attribute21 = X_Attribute21,
attribute22 = X_Attribute22,
attribute23 = X_Attribute23,
attribute24 = X_Attribute24,
attribute25 = X_Attribute25
where cust_account_role_id = x_cust_account_role_id;
select orig_system_reference
into x_orig_system_reference
from hz_cust_account_roles
where cust_account_role_id = x_cust_account_role_id;
END Insert_Row;
PROCEDURE Update_Row( X_contact_id number,
X_Last_Name VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date in out NOCOPY DATE,
X_party_Last_Update_Date in out NOCOPY DATE,
X_org_cont_Last_Update_Date in out NOCOPY DATE,
X_cont_point_Last_Update_Date in out NOCOPY DATE,
X_prel_Last_Update_Date in out NOCOPY DATE,
X_rel_party_Last_Update_Date in out NOCOPY DATE,
X_Status VARCHAR2,
X_Contact_Key VARCHAR2,
X_First_Name VARCHAR2,
X_Job_Title VARCHAR2,
X_Job_Title_Code VARCHAR2,
X_Last_Update_Login NUMBER,
X_Mail_Stop VARCHAR2,
X_Title VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Email_Address VARCHAR2,
X_Last_Name_Alt VARCHAR2 default g_varchar2,
X_First_Name_Alt VARCHAR2 default g_varchar2,
x_contact_number VARCHAR2,
x_party_id number,
x_party_site_id number,
x_contact_party_id number,
x_org_contact_id number,
x_contact_point_id in out NOCOPY number,
x_cust_account_role_id number,
x_party_relationship_id number,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2,
x_rel_party_id number default null)
IS
X_PERSON_OBJECT_VERSION NUMBER :=-1;
Update_Row(
X_contact_id,
X_Last_Name,
X_Last_Updated_By,
X_Last_Update_Date,
X_party_Last_Update_Date,
X_org_cont_Last_Update_Date,
X_cont_point_Last_Update_Date,
X_prel_Last_Update_Date,
X_rel_party_Last_Update_Date,
X_Status ,
X_Contact_Key ,
X_First_Name ,
X_Job_Title ,
X_Job_Title_Code ,
X_Last_Update_Login ,
X_Mail_Stop ,
X_Title ,
X_Attribute_Category ,
X_Attribute1 ,
X_Attribute2 ,
X_Attribute3 ,
X_Attribute4 ,
X_Attribute5 ,
X_Attribute6 ,
X_Attribute7 ,
X_Attribute8 ,
X_Attribute9 ,
X_Attribute10 ,
X_Attribute11 ,
X_Attribute12 ,
X_Attribute13 ,
X_Attribute14 ,
X_Attribute15 ,
X_Attribute16 ,
X_Attribute17 ,
X_Attribute18 ,
X_Attribute19 ,
X_Attribute20 ,
X_Attribute21 ,
X_Attribute22 ,
X_Attribute23 ,
X_Attribute24 ,
X_Attribute25 ,
X_Email_Address ,
X_Last_Name_Alt ,
X_First_Name_Alt ,
x_contact_number ,
x_party_id ,
x_party_site_id ,
x_contact_party_id ,
x_org_contact_id ,
x_contact_point_id ,
x_cust_account_role_id ,
x_party_relationship_id ,
x_return_status ,
x_msg_count ,
x_msg_data ,
x_rel_party_id ,
x_person_object_version ,
x_org_contact_object_version,
x_rel_object_version ,
x_party_object_version ,
x_contact_pt_object_version ,
x_acct_role_object_version );
PROCEDURE Update_Row(
X_contact_id number,
X_Last_Name VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date in out NOCOPY DATE,
X_party_Last_Update_Date in out NOCOPY DATE,
X_org_cont_Last_Update_Date in out NOCOPY DATE,
X_cont_point_Last_Update_Date in out NOCOPY DATE,
X_prel_Last_Update_Date in out NOCOPY DATE,
X_rel_party_Last_Update_Date in out NOCOPY DATE,
X_Status VARCHAR2,
X_Contact_Key VARCHAR2,
X_First_Name VARCHAR2,
X_Job_Title VARCHAR2,
X_Job_Title_Code VARCHAR2,
X_Last_Update_Login NUMBER,
X_Mail_Stop VARCHAR2,
X_Title VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Attribute21 VARCHAR2,
X_Attribute22 VARCHAR2,
X_Attribute23 VARCHAR2,
X_Attribute24 VARCHAR2,
X_Attribute25 VARCHAR2,
X_Email_Address VARCHAR2,
X_Last_Name_Alt VARCHAR2 default g_varchar2,
X_First_Name_Alt VARCHAR2 default g_varchar2,
x_contact_number VARCHAR2,
x_party_id number,
x_party_site_id number,
x_contact_party_id number,
x_org_contact_id number,
x_contact_point_id in out NOCOPY number,
x_cust_account_role_id number,
x_party_relationship_id number,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2,
x_rel_party_id number default null,
x_person_object_version in out NOCOPY NUMBER,
x_org_contact_object_version in out NOCOPY NUMBER,
x_rel_object_version in out NOCOPY NUMBER,
x_party_object_version in out NOCOPY NUMBER,
x_contact_pt_object_version in out NOCOPY NUMBER,
x_acct_role_object_version in out NOCOPY NUMBER
)
IS
party_rec hz_party_v2pub.party_rec_type;
l_person_last_update_date DATE;
l_org_contact_last_update_date DATE;
l_rel_last_update_date DATE;
l_party_last_update_date DATE;
l_contact_pt_last_update_date DATE;
l_acct_role_last_update_date DATE;
object_version_select(
p_table_name => 'HZ_PARTIES',
p_col_id => x_contact_party_id,
x_rowid => l_person_rowid,
x_object_version_number => l_person_object_version,
x_last_update_date => l_person_last_update_date,
x_id_value => l_dummy_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
HZ_PARTY_V2PUB.update_person (
p_person_rec => per_rec,
p_party_object_version_number => l_person_object_version,
x_profile_id => x_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT last_update_date,
object_version_number
INTO x_party_Last_Update_Date,
x_person_object_version
FROM hz_parties
WHERE party_id = x_contact_party_id;
object_version_select(
p_table_name => 'HZ_ORG_CONTACTS',
p_col_id => x_org_contact_id,
x_rowid => l_org_contact_rowid,
x_object_version_number => l_org_contact_object_version,
x_last_update_date => l_org_contact_last_update_date,
x_id_value => l_rel_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
object_version_select(
p_table_name => 'HZ_RELATIONSHIPS',
p_col_id => l_rel_id,
x_rowid => l_rel_rowid,
x_object_version_number => l_rel_object_version,
x_last_update_date => l_rel_last_update_date,
x_id_value => l_party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
object_version_select(
p_table_name => 'HZ_PARTIES',
p_col_id => l_party_id,
x_rowid => l_party_rowid,
x_object_version_number => l_party_object_version,
x_last_update_date => l_party_last_update_date,
x_id_value => l_dummy_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
IF X_org_cont_Last_Update_Date = FND_API.G_MISS_DATE
OR
X_org_cont_Last_Update_Date IS NULL
THEN
ocon_rec.party_rel_rec.party_rec.party_id := NULL;
IF l_rel_last_update_date IS NOT NULL AND
l_rel_last_update_date <> FND_API.G_MISS_DATE
THEN
ocon_rec.party_rel_rec.relationship_id := l_rel_id;
HZ_PARTY_CONTACT_V2PUB.update_org_contact (
p_org_contact_rec => ocon_rec,
p_cont_object_version_number => l_org_contact_object_version,
p_rel_object_version_number => l_rel_object_version,
p_party_object_version_number => l_party_object_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
update_mail_stop
( p_org_contact_id => x_org_contact_id,
p_mail_stop => X_Mail_Stop,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
select last_update_date,
object_version_number
into X_org_cont_Last_Update_Date,
x_org_contact_object_version
from hz_org_contacts
where org_contact_id = x_org_contact_id;
select last_update_date,
object_version_number
into X_prel_Last_Update_Date,
x_rel_object_version
from hz_relationships
where relationship_id = x_party_relationship_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
select party_id
into i_rel_party_id
from hz_relationships
where relationship_id = x_party_relationship_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
select last_update_date ,
object_version_number
into X_rel_party_Last_Update_Date ,
x_party_object_version
from hz_parties
where party_id = i_rel_party_id;
l_date := X_Cont_Point_Last_Update_Date;
select email_address into email_rec.email_address
from hz_contact_points
where contact_point_id = x_contact_point_id;
object_version_select(
p_table_name => 'HZ_CONTACT_POINTS',
p_col_id => x_contact_point_Id,
x_rowid => l_contact_pt_rowid,
x_object_version_number => l_contact_pt_object_version,
x_last_update_date => l_contact_pt_last_update_date,
x_id_value => l_dummy_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT owner_table_name,
owner_table_id
INTO cpoint_rec.owner_table_name,
cpoint_rec.owner_table_id
FROM hz_contact_points
WHERE contact_point_id = x_contact_point_Id;
HZ_CONTACT_POINT_V2PUB.update_contact_point (
p_contact_point_rec => cpoint_rec,
p_email_rec => email_rec,
p_object_version_number => l_contact_pt_object_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select last_update_date,
object_version_number
into X_cont_point_last_update_date,
x_contact_pt_object_version
from hz_contact_points
where contact_point_id = x_contact_point_id;
select hz_contact_points_s.nextval
into i_contact_point_id
from dual;
select last_update_date,
object_version_number
into X_cont_point_last_update_date,
x_contact_pt_object_version
from hz_contact_points
where contact_point_id = x_contact_point_id;
object_version_select(
p_table_name => 'HZ_CUST_ACCOUNT_ROLES',
p_col_id => X_cust_account_role_id,
x_rowid => l_acct_role_rowid,
x_object_version_number => l_acct_role_object_version,
x_last_update_date => l_acct_role_last_update_date,
x_id_value => l_dummy_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
p_cust_account_role_rec => arole_rec,
p_object_version_number => l_acct_role_object_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select cust_account_role_id
into i_lock_id
from hz_cust_account_roles
where cust_account_role_id = x_cust_account_role_id
for update of cust_account_role_id nowait;
update hz_cust_account_roles
set attribute21 = X_Attribute21,
attribute22 = X_Attribute22,
attribute23 = X_Attribute23,
attribute24 = X_Attribute24,
attribute25 = X_Attribute25
where cust_account_role_id = X_contact_id ;
select last_update_date,
object_version_number
into X_Last_Update_Date,
x_acct_role_object_version
from hz_cust_account_roles
where cust_account_role_id = X_contact_id;
END Update_Row;
x_update_required IN OUT NOCOPY VARCHAR2,
x_relationship_id IN OUT NOCOPY NUMBER,
x_ocon_version_number IN OUT NOCOPY NUMBER )
IS
CURSOR c IS
SELECT contact_number,
title ,
job_title ,
job_title_code,
party_site_id ,
orig_system_reference,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
object_version_number,
party_relationship_id
FROM hz_org_contacts
WHERE org_contact_id = p_ocon_rec.org_contact_id;
x_update_required := 'N';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
x_update_required := 'Y';
PROCEDURE upd_ocon_update
(p_ocon_rec IN hz_party_contact_v2pub.org_contact_rec_type)
IS
BEGIN
UPDATE hz_org_contacts
SET contact_number = p_ocon_rec.contact_number,
title = p_ocon_rec.title,
job_title = p_ocon_rec.job_title,
job_title_code = p_ocon_rec.job_title_code,
attribute_category = p_ocon_rec.attribute_category,
attribute1 = p_ocon_rec.attribute1,
attribute2 = p_ocon_rec.attribute2,
attribute3 = p_ocon_rec.attribute3,
attribute4 = p_ocon_rec.attribute4,
attribute5 = p_ocon_rec.attribute5,
attribute6 = p_ocon_rec.attribute6,
attribute7 = p_ocon_rec.attribute7,
attribute8 = p_ocon_rec.attribute8,
attribute9 = p_ocon_rec.attribute9,
attribute10 = p_ocon_rec.attribute10,
attribute11 = p_ocon_rec.attribute11,
attribute12 = p_ocon_rec.attribute12,
attribute13 = p_ocon_rec.attribute13,
attribute14 = p_ocon_rec.attribute14,
attribute15 = p_ocon_rec.attribute15,
attribute16 = p_ocon_rec.attribute16,
attribute17 = p_ocon_rec.attribute17,
attribute18 = p_ocon_rec.attribute18,
attribute19 = p_ocon_rec.attribute18,
attribute20 = p_ocon_rec.attribute20,
object_version_number= object_version_number + 1
WHERE org_contact_id = p_ocon_rec.org_contact_id;