[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO dummy
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM pn_contacts_all c
WHERE c.last_name = x_last_name
AND c.first_name = x_first_name
AND c.company_site_id = x_company_site_id
AND (( x_rowid is null) or (c.rowid <> x_rowid))
AND org_id = x_org_id
);
SELECT count(1)
INTO primary_count
FROM pn_contacts_all pc
WHERE pc.company_site_id = p_company_site_id
AND pc.primary_flag = 'Y'
AND ((p_contact_id is null) or pc.contact_id <> p_contact_id )
AND org_id = p_org_id;
PROCEDURE insert_row ( x_rowid IN OUT NOCOPY VARCHAR2,
x_contact_id IN OUT NOCOPY NUMBER,
x_company_site_id NUMBER,
x_last_name VARCHAR2,
x_created_by NUMBER,
x_creation_date DATE,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_status VARCHAR2,
x_first_Name VARCHAR2,
x_job_title VARCHAR2,
x_mail_stop VARCHAR2,
x_email_address VARCHAR2,
x_primary_flag VARCHAR2,
x_company_or_location 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_id NUMBER
)
IS
CURSOR c IS
SELECT rowid
FROM pn_contacts_all
WHERE contact_id = x_contact_id;
SELECT org_id
FROM pn_company_sites_all
WHERE company_site_id = x_company_site_id;
SELECT pn_contacts_s.nextval
INTO x_contact_id
FROM dual;
INSERT INTO pn_contacts_all
(
contact_id,
company_site_id,
last_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
status,
first_name,
job_title,
mail_stop,
email_address,
primary_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
)
VALUES
(
x_contact_id,
x_company_site_id,
x_last_name,
x_created_by,
x_creation_date,
x_last_updated_by,
x_last_update_date,
x_last_update_login,
x_status,
x_first_name,
x_job_title,
x_mail_stop,
x_email_address,
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,
l_org_id
);
END insert_row;
PROCEDURE update_row ( x_rowid VARCHAR2,
x_contact_id NUMBER,
x_company_site_id NUMBER,
x_last_name VARCHAR2,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_status VARCHAR2,
x_first_Name VARCHAR2,
x_job_title VARCHAR2,
x_mail_stop VARCHAR2,
x_email_address 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
)
IS
BEGIN
UPDATE pn_contacts_all
SET
last_name = x_last_name,
first_name = x_first_name,
status = x_status,
job_title = x_job_title,
mail_stop = x_mail_stop,
email_address = x_email_address,
primary_flag = x_primary_flag,
last_updated_by = x_last_updated_by,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15
WHERE contact_id = x_contact_id;
END update_row;
SELECT *
FROM pn_contacts_all
WHERE ROWID = x_rowid
FOR UPDATE OF contact_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE check_delete ( x_contact_id NUMBER )
IS
CURSOR pn_phones_cur IS
SELECT 1
FROM pn_phones_all
WHERE contact_id = x_contact_id;
fnd_message.set_name ('PN', 'PN_PHONES_EXIST_CANNOT_DELETE');
END check_delete;
PROCEDURE delete_row
(
x_contact_id NUMBER
)
IS
BEGIN
DELETE FROM pn_phones_all
WHERE contact_id = x_contact_id;
DELETE FROM pn_contacts_all
WHERE contact_id = x_contact_id;
END delete_row;