[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_role_resp_version IS
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
NULL
FROM HZ_ROLE_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = p_col_id;
l_last_update_date DATE;
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');
select count(1)
into unique_count
from hz_role_responsibility cr
where cr.cust_account_role_id = p_contact_id
and cr.responsibility_type = p_usage_code
and (( p_contact_role_id is null) or
(cr.responsibility_id <> p_contact_role_id));
select 1
into dummy
from hz_role_responsibility
where cust_account_role_id = p_contact_id
and responsibility_type = p_usage_code;
select count(1)
into primary_count
from hz_role_responsibility cr
where cr.cust_account_role_id = p_contact_id
and cr.primary_flag = 'Y'
and (( p_contact_role_id is null)
or (cr.responsibility_id <> p_contact_role_id));
PROCEDURE Insert_Row(
X_Contact_Role_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Usage_Code VARCHAR2,
X_Contact_Id NUMBER,
X_Last_Update_Login NUMBER,
X_Primary_Flag 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_org_contact_id NUMBER,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2
) IS
--role_rec hz_party_pub.org_contact_role_rec_type;
select hz_org_contact_roles_s.nextval into x_contact_role_id from dual;
select count(*) into l_count from hz_org_contact_roles
where org_contact_role_id = x_contact_role_id ;
select hz_role_responsibility_s.nextval into x_contact_role_id from dual;
select count(*) into l_count from hz_role_responsibility
where responsibility_id = x_contact_role_id;
END Insert_Row;
PROCEDURE Update_Row(
X_Contact_Role_Id NUMBER,
X_Last_Update_Date in out NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Usage_Code VARCHAR2,
X_Contact_Id NUMBER,
X_Last_Update_Login NUMBER,
X_Primary_Flag 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_org_contact_id NUMBER,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2,
x_object_version IN OUT NOCOPY NUMBER
)
IS
-- role_rec hz_party_pub.org_contact_role_rec_type;
object_version_select
(p_table_name => 'HZ_ROLE_RESPONSIBILITY',
p_col_id => X_Contact_Role_Id,
x_rowid => l_rol_res_rowid,
x_object_version_number => l_rol_res_obj_version,
x_last_update_date => l_rol_res_last_upd_date,
x_id_value => l_dummy,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
HZ_CUST_ACCOUNT_ROLE_V2PUB.update_role_responsibility (
p_role_responsibility_rec => role_resp_rec,
p_object_version_number => l_rol_res_obj_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
hz_customer_accounts_pub.update_role_resp(
1,
null,
null,
role_resp_rec,
X_Last_Update_Date,
x_return_status,
x_msg_count,
x_msg_data);
select last_update_date,
object_version_number
into X_Last_Update_Date,
x_object_version
from hz_role_responsibility
where responsibility_id = X_Contact_Role_Id;
END Update_Row;
PROCEDURE Update_Row(
X_Contact_Role_Id NUMBER,
X_Last_Update_Date in out NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Usage_Code VARCHAR2,
X_Contact_Id NUMBER,
X_Last_Update_Login NUMBER,
X_Primary_Flag 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_org_contact_id NUMBER,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2
)
IS
l_object_version NUMBER := -1;
Update_Row(
X_Contact_Role_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Usage_Code ,
X_Contact_Id ,
X_Last_Update_Login,
X_Primary_Flag ,
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_org_contact_id,
x_return_status ,
x_msg_count ,
x_msg_data ,
l_object_version );
PROCEDURE delete_row(x_contact_id in number ,x_usage_code in varchar2) is
BEGIN
delete from hz_role_responsibility
where cust_account_role_id = x_contact_id
and responsibility_type = x_usage_code;
-- This procedure is called from the arp_contacts_pkg.update_row
-- the row for deletion may not exist. Not raising the exception
-- means we do not have to check for existence before delete.
-- Do NOT call this procedure form a forms on-delete trigger.
--
-- if (SQL%NOTFOUND) then
-- raise NO_DATA_FOUND;
END delete_row;
PROCEDURE Delete_Row(X_contact_role_id number) IS
BEGIN
DELETE FROM hz_role_responsibility
WHERE responsibility_id = X_contact_role_id;
END Delete_Row;