The following lines contain the word 'select', 'insert', 'update' or 'delete':
function insert_fnd_reg(
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
return NUMBER
IS
l_registration_id NUMBER;
select fnd_registrations_s.nextval
into l_registration_id
from dual;
INSERT INTO fnd_registrations(
registration_id,
registration_key,
application_id,
party_id,
registration_type,
requested_user_name,
assigned_user_name,
registration_status,
exists_in_fnd_user_flag,
user_title,
first_name,
middle_name,
last_name,
user_suffix,
email_contact_point_id,
email,
phone_contact_point_id,
phone_country_code,
phone_area_code,
phone,
phone_extension,
fax_contact_point_id,
fax_country_code,
fax_area_code,
fax,
fax_extension,
language_code,
time_zone,
territory_code,
location_id,
address1,
address2,
city,
state,
province,
zip,
postal_code,
country,
date_requested,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login
)VALUES(
l_registration_id,
l_registration_id,
p_application_id,
p_party_id,
p_registration_type,
p_requested_user_name,
p_assigned_user_name,
p_registration_status,
p_exists_in_fnd_user_flag,
p_user_title,
p_first_name,
p_middle_name,
p_last_name,
p_user_suffix,
p_email_contact_point_id,
p_email,
p_phone_contact_point_id,
p_phone_country_code,
p_phone_area_code,
p_phone,
p_phone_extension,
p_fax_contact_point_id,
p_fax_country_code,
p_fax_area_code,
p_fax,
p_fax_extension,
p_language_code,
p_time_zone,
p_territory_code,
p_location_id,
p_address1,
p_address2,
p_city,
p_state,
p_province,
p_zip,
p_postal_code,
p_country,
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
END insert_fnd_reg;
procedure update_fnd_reg(
p_registration_id IN NUMBER,
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
IS
BEGIN
UPDATE fnd_registrations
SET application_id = p_application_id,
party_id = p_party_id,
registration_type = p_registration_type,
requested_user_name = p_requested_user_name,
assigned_user_name = p_assigned_user_name,
registration_status = p_registration_status,
exists_in_fnd_user_flag = p_exists_in_fnd_user_flag,
user_title = p_user_title,
first_name = p_first_name,
middle_name = p_middle_name,
last_name = p_last_name,
user_suffix = p_user_suffix,
email_contact_point_id = p_email_contact_point_id,
email = p_email,
phone_contact_point_id = p_phone_contact_point_id,
phone_country_code = p_phone_country_code,
phone_area_code = p_phone_area_code,
phone = p_phone,
phone_extension = p_phone_extension,
fax_contact_point_id = p_fax_contact_point_id,
fax_country_code = p_fax_country_code,
fax_area_code = p_fax_area_code,
fax = p_fax,
fax_extension = p_fax_extension,
language_code = p_language_code,
time_zone = p_time_zone,
territory_code = p_territory_code,
location_id = p_location_id,
address1 = p_address1,
address2 = p_address2,
city = p_city,
state = p_state,
province = p_province,
zip = p_zip,
postal_code = p_postal_code,
country = p_country,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE
registration_id = p_registration_id;
END update_fnd_reg;
procedure delete_fnd_reg(
p_registration_id IN VARCHAR2)
IS
BEGIN
DELETE from fnd_registrations
WHERE registration_id = p_registration_id;
END delete_fnd_reg;
procedure insert_fnd_reg_details(
p_registration_id IN NUMBER,
p_application_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_field_name IN VARCHAR2,
p_field_type IN VARCHAR2,
p_field_format IN VARCHAR2 default null,
p_field_value_string IN VARCHAR2 default null,
p_field_value_number IN NUMBER default null,
p_field_value_date IN DATE default null)
IS
BEGIN
INSERT INTO fnd_registration_details(
registration_id,
application_id,
registration_type,
field_name,
field_type,
field_format,
field_value_string,
field_value_number,
field_value_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login
)VALUES(
p_registration_id,
p_application_id,
p_registration_type,
p_field_name,
p_field_type,
p_field_format,
p_field_value_string,
p_field_value_number,
p_field_value_date,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
END insert_fnd_reg_details;
procedure update_fnd_reg_details(
p_registration_id IN NUMBER,
p_field_name IN VARCHAR2,
p_field_type IN VARCHAR2,
p_field_format IN VARCHAR2 default null,
p_field_value_string IN VARCHAR2 default null,
p_field_value_number IN NUMBER default null,
p_field_value_date IN DATE default null)
IS
BEGIN
UPDATE fnd_registration_details
SET field_type = p_field_type,
field_format = p_field_format,
field_value_string = p_field_value_string,
field_value_number = p_field_value_number,
field_value_date = p_field_value_date,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE registration_id = p_registration_id
AND field_name = p_field_name;
END update_fnd_reg_details;
procedure delete_fnd_reg_details(
p_registration_id IN NUMBER,
p_field_name IN VARCHAR2)
IS
BEGIN
DELETE FROM fnd_registration_details
WHERE registration_id = p_registration_id
AND field_name = p_field_name;
END delete_fnd_reg_details;
procedure update_reg_status(
p_registration_id IN NUMBER,
p_new_status IN VARCHAR2)
IS
BEGIN
UPDATE fnd_registrations
SET registration_status = p_new_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE registration_id = p_registration_id;
END update_reg_status;
procedure update_reg_status_by_key(
p_registration_key IN VARCHAR2,
p_new_status IN VARCHAR2)
IS
l_registration_id NUMBER;
update_reg_status(l_registration_id, p_new_status);
END update_reg_status_by_key;
procedure update_fnd_reg_by_key(
p_registration_key IN VARCHAR2,
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
IS
l_registration_id NUMBER;
update_fnd_reg(
l_registration_id,
p_application_id,
p_party_id,
p_registration_type,
p_requested_user_name,
p_assigned_user_name,
p_registration_status,
p_exists_in_fnd_user_flag,
p_user_title,
p_first_name,
p_middle_name,
p_last_name,
p_user_suffix,
p_email_contact_point_id,
p_email,
p_phone_contact_point_id,
p_phone_country_code,
p_phone_area_code,
p_phone,
p_phone_extension,
p_fax_contact_point_id,
p_fax_country_code,
p_fax_area_code,
p_fax,
p_fax_extension,
p_language_code,
p_time_zone,
p_territory_code,
p_location_id,
p_address1,
p_address2,
p_city,
p_state,
p_province,
p_zip,
p_postal_code,
p_country);
END update_fnd_reg_by_key;
procedure delete_fnd_reg_by_key(
p_registration_key IN VARCHAR2)
IS
l_registration_id NUMBER;
delete_fnd_reg(l_registration_id);
END delete_fnd_reg_by_key;
function insert_reg(
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_reg_details IN fnd_reg_details_ref_cursor,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
return NUMBER
IS
l_registration_id NUMBER;
l_registration_id := insert_fnd_reg(
p_application_id,
p_party_id,
p_registration_type,
p_requested_user_name,
p_assigned_user_name,
p_registration_status,
p_exists_in_fnd_user_flag,
p_user_title,
p_first_name,
p_middle_name,
p_last_name,
p_user_suffix,
p_email_contact_point_id,
p_email,
p_phone_contact_point_id,
p_phone_country_code,
p_phone_area_code,
p_phone,
p_phone_extension,
p_fax_contact_point_id,
p_fax_country_code,
p_fax_area_code,
p_fax,
p_fax_extension,
p_language_code,
p_time_zone,
p_territory_code,
p_location_id,
p_address1,
p_address2,
p_city,
p_state,
p_province,
p_zip,
p_postal_code,
p_country);
insert_fnd_reg_details(
l_registration_id,
p_application_id,
p_registration_type,
l_field_name,
l_field_type,
l_field_format,
l_field_value_string,
l_field_value_number,
l_field_value_date
);
END insert_reg;
procedure update_reg(
p_registration_id IN VARCHAR2,
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_reg_details IN fnd_reg_details_ref_cursor,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
IS
l_registration_id NUMBER;
update_fnd_reg(
p_registration_id,
p_application_id,
p_party_id,
p_registration_type,
p_requested_user_name,
p_assigned_user_name,
p_registration_status,
p_exists_in_fnd_user_flag,
p_user_title,
p_first_name,
p_middle_name,
p_last_name,
p_user_suffix,
p_email_contact_point_id,
p_email,
p_phone_contact_point_id,
p_phone_country_code,
p_phone_area_code,
p_phone,
p_phone_extension,
p_fax_contact_point_id,
p_fax_country_code,
p_fax_area_code,
p_fax,
p_fax_extension,
p_language_code,
p_time_zone,
p_territory_code,
p_location_id,
p_address1,
p_address2,
p_city,
p_state,
p_province,
p_zip,
p_postal_code,
p_country);
insert_fnd_reg_details(
l_registration_id,
p_application_id,
p_registration_type,
l_field_name,
l_field_type,
l_field_format,
l_field_value_string,
l_field_value_number,
l_field_value_date
);
END update_reg;
procedure update_reg_by_key(
p_registration_key IN VARCHAR2,
p_application_id IN NUMBER,
p_party_id IN NUMBER,
p_registration_type IN VARCHAR2,
p_requested_user_name IN VARCHAR2,
p_assigned_user_name IN VARCHAR2,
p_registration_status IN VARCHAR2,
p_exists_in_fnd_user_flag IN VARCHAR2,
p_reg_details IN fnd_reg_details_ref_cursor,
p_user_title IN VARCHAR2 default null,
p_first_name IN VARCHAR2 default null,
p_middle_name IN VARCHAR2 default null,
p_last_name IN VARCHAR2 default null,
p_user_suffix IN VARCHAR2 default null,
p_email_contact_point_id IN NUMBER default null,
p_email IN VARCHAR2 default null,
p_phone_contact_point_id IN NUMBER default null,
p_phone_country_code IN VARCHAR2 default null,
p_phone_area_code IN VARCHAR2 default null,
p_phone IN VARCHAR2 default null,
p_phone_extension IN VARCHAR2 default null,
p_fax_contact_point_id IN NUMBER default null,
p_fax_country_code IN VARCHAR2 default null,
p_fax_area_code IN VARCHAR2 default null,
p_fax IN VARCHAR2 default null,
p_fax_extension IN VARCHAR2 default null,
p_language_code IN VARCHAR2 default null,
p_time_zone IN VARCHAR2 default null,
p_territory_code IN VARCHAR2 default null,
p_location_id IN NUMBER default null,
p_address1 IN VARCHAR2 default null,
p_address2 IN VARCHAR2 default null,
p_city IN VARCHAR2 default null,
p_state IN VARCHAR2 default null,
p_province IN VARCHAR2 default null,
p_zip IN VARCHAR2 default null,
p_postal_code IN VARCHAR2 default null,
p_country IN VARCHAR2 default null)
IS
l_registration_id NUMBER;
update_reg(
l_registration_id,
p_application_id,
p_party_id,
p_registration_type,
p_requested_user_name,
p_assigned_user_name,
p_registration_status,
p_exists_in_fnd_user_flag,
p_reg_details,
p_user_title,
p_first_name,
p_middle_name,
p_last_name,
p_user_suffix,
p_email_contact_point_id,
p_email,
p_phone_contact_point_id,
p_phone_country_code,
p_phone_area_code,
p_phone,
p_phone_extension,
p_fax_contact_point_id,
p_fax_country_code,
p_fax_area_code,
p_fax,
p_fax_extension,
p_language_code,
p_time_zone,
p_territory_code,
p_location_id,
p_address1,
p_address2,
p_city,
p_state,
p_province,
p_zip,
p_postal_code,
p_country);
END update_reg_by_key;
procedure delete_reg(
p_registration_id IN NUMBER)
IS
BEGIN
DELETE FROM fnd_registration_details
WHERE registration_id = p_registration_id;
DELETE FROM fnd_registrations
WHERE registration_id = p_registration_id;
procedure delete_reg_by_key(
p_registration_key IN VARCHAR2)
IS
l_registration_id NUMBER;
delete_reg(l_registration_id);
END delete_reg_by_key;
SELECT application_id,
party_id,
registration_type,
requested_user_name,
assigned_user_name,
registration_status,
exists_in_fnd_user_flag,
user_title,
first_name,
middle_name,
last_name,
user_suffix,
email_contact_point_id,
email,
phone_contact_point_id,
phone_country_code,
phone_area_code,
phone,
phone_extension,
fax_contact_point_id,
fax_country_code,
fax_area_code,
fax,
fax_extension,
language_code,
time_zone,
territory_code,
location_id,
address1,
address2,
city,
state,
province,
zip,
postal_code,
country
INTO x_application_id,
x_party_id,
x_registration_type,
x_requested_user_name,
x_assigned_user_name,
x_registration_status,
x_exists_in_fnd_user_flag,
x_user_title,
x_first_name,
x_middle_name,
x_last_name,
x_user_suffix,
x_email_contact_point_id,
x_email,
x_phone_contact_point_id,
x_phone_country_code,
x_phone_area_code,
x_phone,
x_phone_extension,
x_fax_contact_point_id,
x_fax_country_code,
x_fax_area_code,
x_fax,
x_fax_extension,
x_language_code,
x_time_zone,
x_territory_code,
x_location_id,
x_address1,
x_address2,
x_city,
x_state,
x_province,
x_zip,
x_postal_code,
x_country
FROM fnd_registrations
WHERE registration_id = p_registration_id;
SELECT field_type,
field_format,
field_value_string,
field_value_number,
field_value_date
INTO x_field_type,
x_field_format,
x_field_value_string,
x_field_value_number,
x_field_value_date
FROM fnd_registration_details
WHERE registration_id = p_registration_id
AND field_name = p_field_name;
SELECT registration_id,
application_id,
registration_type,
field_name,
field_type,
field_format,
field_value_string,
field_value_number,
field_value_date,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login
FROM fnd_registration_details
WHERE registration_id = p_registration_id;
SELECT registration_id
INTO l_registration_id
FROM fnd_registrations
WHERE assigned_user_name = p_assigned_user_name
AND application_id = p_application_id
AND registration_type = p_registration_type;
SELECT registration_id
INTO l_registration_id
FROM fnd_registrations
WHERE registration_type = p_registration_type
AND application_id = p_application_id
AND party_id = p_party_id;
SELECT registration_id
INTO l_registration_id
FROM fnd_registrations
WHERE registration_key = p_registration_key;
SELECT registration_key
INTO l_registration_key
FROM fnd_registrations
WHERE registration_id = p_registration_id;