[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Insert_row and cust_acct_role_id is removed
| from Update Row Procedure .Org_contact_role_id is added
| in both insert_row and update_row .Procedure to create
| org_contact_role is created .Cursor to get last_update_date
| from hz_org_contact_roles table is written . Call To HZ_CUSTOMER_ACCOUTN.CREATE_ACCT_ROLES
| is removed .Sequences HZ_CUST_ACCT_ROLES_S and HZ_CUST_CONTACT_POINTS are removed
| pkpatel 03-APR-2002 Bug ID - 2283145 : Added the Code for Generating And Passing the party number
| if the profile value is set to 'No' for generating the party number
| ssawhney 7-may Bug 2338473 -- allow for more than one HZ error to appear.
| kpadiyar 15-May-2002 Bug 2373159 -- OSSIMP15: ERROR MESSAGE FOR DUPLICTED INSTITUTION CONTACT NOT INFORMATIVE.
| kpadiyar 19-May-2002 Bug 2373159 -- Code changed to return the party number instead of the party id - Review comments.
gmuralid 25-NOV-2002 BUG 2466674 -- V2 API UPTAKE CHANGED REFERENCE OF HZ_CONTACT_POINT_PUB TO
HZ_CONTACT_POINT_V2PUB FOR BOTH CREATE AND UPDATE CONTACT POINTS THROUGHOUT
THE PACKAGE BODY.
| masehgal 26-Nov-2002 TCA_V2API_uptake corrected as per V2
ssawhney 30-apr-2003 V2API OVN changes
pkpatel 4-Oct-2005 Bug 4654735 (Replaced the reference of HZ_PARTY_RELATIONSHIPS with HZ_RELATIONSHIPS)
*/
g_email CONSTANT VARCHAR2(10) := 'EMAIL';
PROCEDURE Insert_Row( x_last_name VARCHAR2 ,
x_orig_system_reference IN OUT NOCOPY VARCHAR2 ,
x_status VARCHAR2 ,
x_contact_key VARCHAR2 ,
x_first_name VARCHAR2 ,
x_job_title VARCHAR2 ,
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_org_contact_id IN OUT NOCOPY NUMBER ,
x_contact_point_id IN OUT NOCOPY NUMBER ,
x_rel_party_id IN OUT NOCOPY NUMBER ,
x_created_by NUMBER ,
x_creation_date DATE ,
x_updated_by NUMBER ,
x_update_date DATE ,
x_last_update_login NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_org_contact_role_id IN OUT NOCOPY NUMBER,
P_ORG_ROLE_OVN IN OUT NOCOPY NUMBER,
P_REL_OVN IN OUT NOCOPY NUMBER,
P_REL_PARTY_OVN IN OUT NOCOPY NUMBER,
P_ORG_CONT_OVN IN OUT NOCOPY NUMBER,
P_CONTACT_POINT_OVN IN OUT NOCOPY NUMBER
) AS
/******************************************************************
Created By: Amit Gairola
Date Created By: 01-AUG-2001
Purpose: This procedure creates the records in the Org Contacts
Creates the Customer Contact Points and the Cust Account
Roles
Known limitations,enhancements,remarks:
Change History
Who When What
ssawhney bug 2338473. logic for more than one error modified.
kpadiyar 15-May-2002 Added the call to the local get_error_msg function to get the correct message
kpadiyar 19-May-2002 Added local function get_party_number to returnt the party_number instead of party_id.- Review comments
kumma 07-JUN-2002 Commented out NOCOPY the call to fnd_msg_pub.add as a call to igs_ge_msg_stact.add is also present
vskumar 24-May-2006 xbuild3 performace fix. changed first select statment related to table hz_relationships.
***************************************************************** */
-- masehgal changed to hz_xxxx_V2PUB
ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
SELECT 1
FROM hz_relationships rel
WHERE object_id = cp_org_party_id AND
subject_id = cp_contact_party_id AND
REL.SUBJECT_TYPE = 'PERSON' AND
REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND
REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND
REL.DIRECTIONAL_FLAG = 'F' AND
REL.RELATIONSHIP_TYPE = 'CONTACT';
SELECT party_number
FROM hz_parties
WHERE party_id = p_party_id;
SELECT hz_contact_points_s.NEXTVAL
INTO l_contact_point_id
FROM DUAL;
SELECT hz_party_number_s.NEXTVAL
INTO l_party_rec.party_number
FROM dual;
END insert_row;
PROCEDURE Update_Row(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_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 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_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 ,
x_org_contact_role_id IN OUT NOCOPY NUMBER,
P_ORG_ROLE_OVN IN OUT NOCOPY NUMBER,
P_REL_OVN IN OUT NOCOPY NUMBER,
P_REL_PARTY_OVN IN OUT NOCOPY NUMBER,
P_ORG_CONT_OVN IN OUT NOCOPY NUMBER,
P_CONTACT_POINT_OVN IN OUT NOCOPY NUMBER
) AS
/******************************************************************
Created By: Amit Gairola
Date Created By: 01-AUG-2001
Purpose: This procedure Updates the records in the Org Contacts
Updates the Customer Contact Points
Known limitations,enhancements,remarks:
Change History
Who When What
ssawhney 2338473 -- to display all message if mroe than one error
***************************************************************** */
ocon_rec hz_party_contact_v2pub.org_contact_rec_type;
l_last_update_date DATE;
SELECT email_address
FROM hz_contact_points
WHERE contact_point_id = cp_contact_point_id;
hz_party_contact_v2pub.update_org_contact(
p_init_msg_list => 'T',
p_org_contact_rec => ocon_rec,
p_cont_object_version_number => l_org_contacts_obj_ver_num,
p_rel_object_version_number => l_rel_obj_ver_num,
p_party_object_version_number => l_hz_parties_obj_ver_num,
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_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => cpoint_rec,
p_edi_rec => NULL,
p_email_rec => email_rec,
p_phone_rec => NULL,
p_telex_rec => NULL,
p_web_rec => NULL,
p_object_version_number => l_obj_ver,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT hz_contact_points_s.NEXTVAL
INTO cpoint_rec.contact_point_id
FROM dual;
END update_row;