[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_contact (p_contact_id out nocopy number,
p_customer_id in number,
p_last_name in varchar2,
p_first_name in varchar2,
p_title in varchar2,
p_administrator in number) is
--
l_proc varchar2(72) := g_package||'insert_contact';
Insert_Row(
X_Contact_Id => P_Contact_Id,
X_Created_By => p_administrator,
X_Customer_Id => P_Customer_Id,
X_Last_Name => P_Last_Name,
X_Last_Updated_By => p_administrator,
X_Orig_System_Reference => X_Orig_System_Reference,
X_First_Name => P_First_Name,
X_Title => P_Title,
X_Contact_Number => X_Contact_Number,
X_Contact_Party_Id => X_Contact_Party_Id,
X_Rel_Party_Id => i_rel_party_id,
X_Org_Contact_Id => X_Org_Contact_Id,
X_Cust_Account_Role_Id => X_Cust_Account_Role_Id,
X_Return_Status => i_Return_Status,
X_Msg_Count => i_Msg_Count,
X_Msg_Data => i_Msg_Data
) ;
end insert_contact;
procedure update_contact (p_contact_id in number,
p_last_name in varchar2,
p_first_name in varchar2,
p_title in varchar2) is
--
l_proc varchar2(72) := g_package||'update_contact';
update HZ_PARTIES
set
PERSON_LAST_NAME = substrb( p_last_name,1,50),
PERSON_FIRST_NAME = substrb(p_first_name,1,40),
PERSON_PRE_NAME_ADJUNCT = p_title
where
PARTY_ID = (select PARTY.PARTY_ID
from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_CUST_ACCOUNTS ROLE_ACCT
where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id );
SELECT party.party_id, party.object_version_number
INTO x_party_id, x_party_object_version_number
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id;
HZ_PARTY_V2PUB.update_person(
null,
per_rec,
:x_party_object_version_number,
:x_profile_id,
:x_return_status,
:x_msg_count,
:x_msg_data
);
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 ,
X_First_Name_Alt VARCHAR2 ,
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_Rel_Party_Id IN OUT NOCOPY NUMBER ,
X_Org_Contact_Id IN OUT NOCOPY NUMBER ,
X_Contact_Point_Id 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_subject_party_id HZ_PARTIES.PARTY_ID%type;
SELECT hz_contact_numbers_s.nextval INTO X_Contact_Number FROM DUAL;
select party_id into customer_party_id from hz_cust_accounts where
cust_account_id = x_customer_id;
END Insert_Row;