[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_contact_point_version IS
SELECT ROWID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
NULL
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_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 primary_count
from hz_contact_points cont_point,
hz_cust_account_roles car
where car.party_id = cont_point.owner_table_id
and cont_point.owner_table_name = 'HZ_PARTIES'
and cont_point.contact_point_type not
in ('EDI','EMAIL','WEB')
and car.cust_account_id = p_id
and car.cust_acct_site_id is null
and car.cust_account_role_id is null
and cont_point.primary_flag = 'Y'
and ( (p_phone_id is null)
or cont_point.contact_point_id <> p_phone_id );
select count(1)
into primary_count
from hz_contact_points cont_point,
hz_cust_account_roles car
where car.party_id = cont_point.owner_table_id
and cont_point.owner_table_name = 'HZ_PARTIES'
and cont_point.contact_point_type not
in ('EDI','EMAIL','WEB')
and car.cust_acct_site_id = p_id
and car.cust_account_role_id is null
and cont_point.primary_flag = 'Y'
and ( (p_phone_id is null)
or cont_point.contact_point_id <> p_phone_id );
select count(1)
into primary_count
from hz_contact_points cont_point,
hz_cust_account_roles car
where car.party_id = cont_point.owner_table_id
and cont_point.owner_table_name = 'HZ_PARTIES'
and cont_point.contact_point_type not
in ('EDI','EMAIL','WEB')
and car.cust_account_role_id = p_id
and cont_point.primary_flag = 'Y'
and ( (p_phone_id is null)
or cont_point.contact_point_id <> p_phone_id );
PROCEDURE Insert_Row(
X_Phone_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Phone_Number VARCHAR2,
X_Status VARCHAR2,
X_Phone_Type VARCHAR2,
X_Last_Update_Login NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Contact_Id NUMBER,
X_Country_Code VARCHAR2,
X_Area_Code VARCHAR2,
X_Extension VARCHAR2,
X_Primary_Flag VARCHAR2,
X_Orig_System_Reference IN OUT NOCOPY 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_party_id NUMBER,
x_party_site_id NUMBER,
x_primary_by_purpose VARCHAR2,
x_contact_point_purpose VARCHAR2,
x_email_format VARCHAR2,
x_email_address VARCHAR2,
x_url VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
--
l_type varchar2(4);
SELECT hz_contact_points_s.nextval
INTO x_phone_id
FROM dual;
select count(*) into l_count from hz_contact_points
where contact_point_id = x_phone_id ;
SELECT hz_cust_contact_points_s.nextval
into x_cust_contact_point_id
from dual;
END Insert_Row;
PROCEDURE Update_Row(
X_Phone_Id NUMBER,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Phone_Number VARCHAR2,
X_Status VARCHAR2,
X_Phone_Type VARCHAR2,
X_Last_Update_Login NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Contact_Id NUMBER,
X_Country_code VARCHAR2,
X_Area_Code VARCHAR2,
X_Extension VARCHAR2,
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_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
x_cust_contact_point_id NUMBER,
x_primary_by_purpose VARCHAR2,
x_contact_point_purpose VARCHAR2,
x_email_format VARCHAR2,
x_email_address VARCHAR2,
x_url VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_object_version IN OUT NOCOPY NUMBER
) IS
l_type VARCHAR2(4);
l_update_date DATE;
object_version_select
(p_table_name => 'HZ_CONTACT_POINTS',
p_col_id => x_phone_Id,
x_rowid => l_rowid,
x_object_version_number => l_object_version,
x_last_update_date => l_update_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_CONTACT_POINT_V2PUB.update_contact_point (
p_contact_point_rec => cpoint_rec,
p_edi_rec => edi_rec,
p_email_rec => email_rec,
p_phone_rec => phone_rec,
p_telex_rec => telex_rec,
p_web_rec => web_rec,
p_object_version_number => l_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_Last_Update_Date,
x_object_version
from hz_contact_points
where contact_point_id = X_Phone_Id;
END Update_Row;
PROCEDURE Update_Row(
X_Phone_Id NUMBER,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Phone_Number VARCHAR2,
X_Status VARCHAR2,
X_Phone_Type VARCHAR2,
X_Last_Update_Login NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Contact_Id NUMBER,
X_Country_code VARCHAR2,
X_Area_Code VARCHAR2,
X_Extension VARCHAR2,
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_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
x_cust_contact_point_id NUMBER,
x_primary_by_purpose VARCHAR2,
x_contact_point_purpose VARCHAR2,
x_email_format VARCHAR2,
x_email_address VARCHAR2,
x_url VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_object_version NUMBER := -1;
Update_Row( X_Phone_Id ,
X_Last_Update_Date,
X_Last_Updated_By,
X_Phone_Number ,
X_Status ,
X_Phone_Type ,
X_Last_Update_Login,
X_Customer_Id ,
X_Address_Id ,
X_Contact_Id ,
X_Country_code ,
X_Area_Code ,
X_Extension ,
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_Attribute16 ,
X_Attribute17 ,
X_Attribute18 ,
X_Attribute19 ,
X_Attribute20 ,
x_cust_contact_point_id,
x_primary_by_purpose,
x_contact_point_purpose,
x_email_format,
x_email_address,
x_url,
x_msg_count ,
x_msg_data ,
x_return_status ,
l_object_version );
PROCEDURE Insert_Row(
X_Phone_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Phone_Number VARCHAR2,
X_Status VARCHAR2,
X_Phone_Type VARCHAR2,
X_Last_Update_Login NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Contact_Id NUMBER,
X_Area_Code VARCHAR2,
X_Extension VARCHAR2,
X_Primary_Flag VARCHAR2,
X_Orig_System_Reference IN OUT NOCOPY 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_party_id NUMBER,
x_party_site_id NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
i_return_status VARCHAR2(1);
Insert_Row(
X_Phone_Id
, X_Last_Update_Date
, X_Last_Updated_By
, X_Creation_Date
, X_Created_By
, X_Phone_Number
, X_Status
, X_Phone_Type
, X_Last_Update_Login
, X_Customer_Id
, X_Address_Id
, X_Contact_Id
, i_Default_Phone_Country_Code
, X_Area_Code
, X_Extension
, X_Primary_Flag
, X_Orig_System_Reference
, 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_party_id
, x_party_site_id
, 'N'
, NULL
, NULL
, NULL
, NULL
, i_msg_count
, i_msg_data
, i_return_status
);
END Insert_Row;
PROCEDURE Update_Row(
X_Phone_Id NUMBER,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_Last_Updated_By NUMBER,
X_Phone_Number VARCHAR2,
X_Status VARCHAR2,
X_Phone_Type VARCHAR2,
X_Last_Update_Login NUMBER,
X_Customer_Id NUMBER,
X_Address_Id NUMBER,
X_Contact_Id NUMBER,
X_Area_Code VARCHAR2,
X_Extension VARCHAR2,
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_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
x_cust_contact_point_id NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
i_return_status VARCHAR2(1);
Update_Row(
X_Phone_Id
, X_Last_Update_Date
, X_Last_Updated_By
, X_Phone_Number
, X_Status
, X_Phone_Type
, X_Last_Update_Login
, X_Customer_Id
, X_Address_Id
, X_Contact_Id
, i_Default_Phone_Country_Code
, X_Area_Code
, X_Extension
, 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_Attribute16
, X_Attribute17
, X_Attribute18
, X_Attribute19
, X_Attribute20
, x_cust_contact_point_id
, 'N'
, NULL
, NULL
, NULL
, NULL
, i_msg_count
, i_msg_data
, i_return_status
);
END Update_Row;
PROCEDURE Delete_Row(X_phoneid VARCHAR2) IS
BEGIN
DELETE FROM hz_contact_points
WHERE contact_point_id = X_phoneid;
END Delete_Row;
SELECT a.phone_country_code
INTO l_phone_country_code
FROM hz_phone_country_codes a
WHERE a.territory_code = l_default_country_code;