[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_PARTIES PARTY,
HZ_PARTIES PARTY2,
HZ_PARTY_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT
WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = REL.PARTY_RELATIONSHIP_ID
AND REL.OBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = PARTY2.PARTY_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND ORG_CONT.ORG_CONTACT_ID = c_contact_id
AND PARTY.PARTY_ID = P_CUSTOMER_ID;
SELECT 'X'
FROM -- HZ_CONTACT_POINTS CONT_POINT,
HZ_PARTIES PARTY,
HZ_PARTIES PARTY2,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT
WHERE ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.OBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = PARTY2.PARTY_ID
-- AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
-- AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
-- AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ORG_CONT.ORG_CONTACT_ID = c_contact_id
AND PARTY.PARTY_ID = P_CUSTOMER_ID --;
Select 'X'
from hz_relationships
where party_id = c_CONTACT_PARTY_ID
and object_id = P_CUSTOMER_ID
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES' --;
SELECT 'X'
FROM HZ_PARTY_RELATIONSHIPS
WHERE object_id = P_CUSTOMER_ID
AND party_id = c_CONTACT_PARTY_ID;
SELECT 'X'
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = c_phone_id
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = P_CONTACT_PARTY_ID
AND CONTACT_POINT_TYPE IN ( 'PHONE', 'FAX') --;
SELECT 'X'
FROM as_lookups
WHERE lookup_type = X_Lookup_Type
and lookup_code = X_Lookup_Code
-- ffang 012501
and ENABLED_FLAG = 'Y';
SELECT slc.LEAD_CONTACT_ID, slc.CONTACT_PARTY_ID, slc.PHONE_ID, r.subject_id
FROM as_sales_lead_contacts slc, hz_relationships r
WHERE slc.sales_lead_id = c_SALES_LEAD_ID
and slc.enabled_flag = 'Y'
and slc.primary_contact_flag = 'Y'
and r.party_id = slc.contact_party_id
and r.object_id = slc.customer_id;
SELECT count(1)
FROM as_sales_lead_contacts
WHERE sales_lead_id = c_SALES_LEAD_ID;
l_update_access_flag VARCHAR2(1);
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_sales_lead_contact_rec.sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
UPDATE AS_SALES_LEAD_CONTACTS
SET primary_contact_flag = 'N'
WHERE lead_contact_id = l_lead_contact_id;
'Calling CONTACTS_Insert_Row');
AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Insert_Row(
px_LEAD_CONTACT_ID => l_LEAD_CONTACT_ID,
p_SALES_LEAD_ID => l_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID,
p_CONTACT_ID => l_SALES_LEAD_CONTACT_rec.CONTACT_ID,
p_CONTACT_PARTY_ID => l_SALES_LEAD_CONTACT_rec.CONTACT_PARTY_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_ENABLED_FLAG => l_SALES_LEAD_CONTACT_rec.ENABLED_FLAG,
p_RANK => l_SALES_LEAD_CONTACT_rec.RANK,
p_CUSTOMER_ID => l_SALES_LEAD_CONTACT_rec.CUSTOMER_ID,
p_ADDRESS_ID => l_SALES_LEAD_CONTACT_rec.ADDRESS_ID,
p_PHONE_ID => l_SALES_LEAD_CONTACT_rec.PHONE_ID,
p_CONTACT_ROLE_CODE => l_SALES_LEAD_CONTACT_rec.CONTACT_ROLE_CODE,
p_PRIMARY_CONTACT_FLAG =>
l_SALES_LEAD_CONTACT_rec.PRIMARY_CONTACT_FLAG,
p_ATTRIBUTE_CATEGORY =>l_SALES_LEAD_CONTACT_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_SALES_LEAD_CONTACT_rec.ATTRIBUTE15);
'Updating the header table for last updated');
UPDATE as_sales_leads
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE sales_lead_id = p_sales_lead_id;
UPDATE AS_SALES_LEADS
SET PRIMARY_CONTACT_PARTY_ID = l_contact_party_id,
PRIMARY_CONTACT_PHONE_ID = l_contact_phone_id,
PRIMARY_CNT_PERSON_PARTY_ID = l_cnt_person_party_id
WHERE sales_lead_id = p_SALES_LEAD_ID;
UPDATE AS_SALES_LEADS
SET PRIMARY_CONTACT_PARTY_ID = NULL,
PRIMARY_CONTACT_PHONE_ID = NULL,
PRIMARY_CNT_PERSON_PARTY_ID = NULL
WHERE sales_lead_id = p_SALES_LEAD_ID;
PROCEDURE Update_sales_lead_contacts(
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_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
:= AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_CONTACT_Tbl IN
AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type,
X_SALES_LEAD_CNT_OUT_Tbl OUT
AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor C_Get_contact(c_LEAD_CONTACT_ID Number) IS
Select LAST_UPDATE_DATE
From AS_SALES_LEAD_CONTACTS
Where lead_contact_id = c_LEAD_CONTACT_ID
For Update NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_lead_contacts';
l_last_update_date DATE;
l_update_access_flag VARCHAR2(1);
SELECT slc.CONTACT_PARTY_ID, slc.PHONE_ID, r.subject_id
FROM as_sales_lead_contacts slc, hz_relationships r
WHERE slc.sales_lead_id = c_SALES_LEAD_ID
and slc.enabled_flag = 'Y'
and slc.primary_contact_flag = 'Y'
and r.party_id = slc.contact_party_id
and r.object_id = slc.customer_id;
SAVEPOINT UPDATE_SALES_LEAD_CONTACTS_PVT;
Fetch C_Get_contact into l_last_update_date;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
IF (l_tar_SALES_LEAD_CONTACT_rec.last_update_date is NULL or
l_tar_SALES_LEAD_CONTACT_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
IF (l_tar_SALES_LEAD_CONTACT_rec.last_update_date <> l_last_update_date)
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
P_SALES_LEAD_CONTACT_Rec => l_tar_SALES_LEAD_CONTACT_Rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_tar_sales_lead_contact_rec.sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
UPDATE as_sales_lead_contacts
SET primary_contact_flag = 'N'
WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_Rec.sales_lead_id
and PRIMARY_CONTACT_FLAG = 'Y'
and enabled_flag = 'Y';
'Calling CONTACTS_Update_Row');
AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Update_Row(
p_LEAD_CONTACT_ID => l_tar_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID,
p_SALES_LEAD_ID => l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID,
p_CONTACT_ID => l_tar_SALES_LEAD_CONTACT_rec.CONTACT_ID,
p_CONTACT_PARTY_ID => l_tar_SALES_LEAD_CONTACT_rec.CONTACT_PARTY_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => l_tar_SALES_LEAD_CONTACT_rec.CREATION_DATE,
p_CREATED_BY => l_tar_SALES_LEAD_CONTACT_rec.CREATED_BY,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_ENABLED_FLAG => NVL(l_tar_SALES_LEAD_CONTACT_rec.ENABLED_FLAG,
'Y'),
p_RANK => l_tar_SALES_LEAD_CONTACT_rec.RANK,
p_CUSTOMER_ID => l_tar_SALES_LEAD_CONTACT_rec.CUSTOMER_ID,
p_ADDRESS_ID => l_tar_SALES_LEAD_CONTACT_rec.ADDRESS_ID,
p_PHONE_ID => l_tar_SALES_LEAD_CONTACT_rec.PHONE_ID,
p_CONTACT_ROLE_CODE =>
l_tar_SALES_LEAD_CONTACT_rec.CONTACT_ROLE_CODE,
p_PRIMARY_CONTACT_FLAG =>
l_tar_SALES_LEAD_CONTACT_rec.PRIMARY_CONTACT_FLAG,
p_ATTRIBUTE_CATEGORY =>
l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_tar_SALES_LEAD_CONTACT_rec.ATTRIBUTE15);
'Updating the header table for last updated');
UPDATE as_sales_leads
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_Rec.sales_lead_id;
UPDATE AS_SALES_LEADS
SET PRIMARY_CONTACT_PARTY_ID = l_contact_party_id,
PRIMARY_CONTACT_PHONE_ID = l_contact_phone_id,
PRIMARY_CNT_PERSON_PARTY_ID = l_cnt_person_party_id
WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID;
UPDATE AS_SALES_LEADS
SET PRIMARY_CONTACT_PARTY_ID = NULL,
PRIMARY_CONTACT_PHONE_ID = NULL,
PRIMARY_CNT_PERSON_PARTY_ID = NULL
WHERE sales_lead_id = l_tar_SALES_LEAD_CONTACT_rec.SALES_LEAD_ID;
END Update_sales_lead_contacts;
PROCEDURE Delete_sales_lead_contacts(
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_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
:= AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_CONTACT_Tbl IN
AS_SALES_LEADS_PUB.SALES_LEAD_CONTACT_Tbl_Type,
X_SALES_LEAD_CNT_OUT_Tbl OUT
AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Get_cont_del(c_LEAD_CONTACT_ID Number) IS
SELECT
SALES_LEAD_ID,
PRIMARY_CONTACT_FLAG,
CONTACT_PARTY_ID
FROM AS_SALES_LEAD_CONTACTS
WHERE lead_contact_id = c_LEAD_CONTACT_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_lead_contacts';
l_update_access_flag VARCHAR2(1);
SAVEPOINT DELETE_SALES_LEAD_CONTACTS_PVT;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => l_sales_lead_id
,p_check_access_flag => p_check_access_flag -- should be 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,p_partner_cont_party_id => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF (AS_DEBUG_ERROR_ON) THEN
AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
'API_NO_CREATE_PRIVILEGE');
'Calling CONTACTS_Delete_Row');
AS_SALES_LEAD_CONTACTS_PKG.SALES_LEAD_CONTACTS_Delete_Row(
p_LEAD_CONTACT_ID => l_SALES_LEAD_CONTACT_rec.LEAD_CONTACT_ID);
'Updating the header table for last updated');
UPDATE as_sales_leads
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE sales_lead_id = l_sales_lead_id;
'Primary contact is deleted');
update as_sales_leads
set primary_contact_party_id = NULL,
primary_contact_phone_id = NULL,
primary_cnt_person_party_id = NULL
where sales_lead_id = l_sales_lead_id
and primary_contact_party_id = l_contact_party_id;
END Delete_sales_lead_contacts;
SELECT count(*)
FROM as_sales_lead_contacts
WHERE sales_lead_id = X_SALES_LEAD_ID
and enabled_flag = 'Y'
and primary_contact_flag = 'Y';
l_update_access_flag VARCHAR2(1);