The following lines contain the word 'select', 'insert', 'update' or 'delete':
select form_id func_id
, application_id app_id
from fnd_form_vl
where form_name=p_function_name
and p_function_type='O'
UNION
select function_id func_id
, application_id app_is
from fnd_form_functions_vl
where function_name=p_function_name
and p_function_type='F';
select attachment_function_id
from fnd_attachment_functions
where function_type=p_function_type
and function_id=p_function_id;
select fnd_attachment_functions_s.nextval
into l_attachment_function_id
from sys.dual;
insert into fnd_attachment_functions (
attachment_function_id,
function_type,
function_id,
function_name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
application_id,
session_context_field,
enabled_flag) VALUES (
l_attachment_function_id,
p_function_type,
l_function_id,
p_function_name,
sysdate,
1,
sysdate,
1,
1,
l_application_id,
'',
'Y');
select 1
from fnd_attachment_functions
where attachment_function_id=p_attachment_function_id;
select category_id
from fnd_doc_categories_active_vl
where name=p_category_name;
select doc_category_usage_id
from fnd_doc_category_usages
where attachment_function_id=p_attachment_function_id
and category_id=p_category_id;
select fnd_doc_category_usages_s.nextval
into l_doc_category_usages_id
from sys.dual;
INSERT INTO fnd_doc_category_usages(
doc_category_usage_id,
category_id,
attachment_function_id,
enabled_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login) VALUES (
l_doc_category_usages_id,
l_category_id,
p_attachment_function_id,
'Y',
sysdate,
1,
sysdate,
1,
1);
procedure create_or_update_block
(p_attachment_function_id IN NUMBER
,p_block_name IN VARCHAR2
,p_query_flag IN VARCHAR2 default 'N'
,p_security_type IN NUMBER default 4
,p_org_context_field IN VARCHAR2 default null
,p_set_of_books_context_field IN VARCHAR2 default null
,p_business_unit_context_field IN VARCHAR2 default null
,p_context1_field IN VARCHAR2 default null
,p_context2_field IN VARCHAR2 default null
,p_context3_field IN VARCHAR2 default null
,p_attachment_blk_id OUT NUMBER) is
--
cursor att_func_exists is
select 1
from fnd_attachment_functions
where attachment_function_id=p_attachment_function_id;
select attachment_blk_id
from fnd_attachment_blocks
where block_name=p_block_name
and attachment_function_id=p_attachment_function_id;
select fnd_attachment_blocks_s.nextval
into l_attachment_blk_id
from sys.dual;
INSERT INTO fnd_attachment_blocks (
attachment_blk_id,
attachment_function_id,
block_name,
query_flag,
security_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_context_field,
set_of_books_context_field,
business_unit_context_field,
context1_field,
context2_field,
context3_field) VALUES (
l_attachment_blk_id,
p_attachment_function_id,
p_block_name,
p_query_flag,
p_security_type,
sysdate,
1,
sysdate,
1,
1,
p_org_context_field,
p_set_of_books_context_field,
p_business_unit_context_field,
p_context1_field,
p_context2_field,
p_context3_field);
update fnd_attachment_blocks
set
query_flag=p_query_flag,
security_type=p_security_type,
creation_date=sysdate,
created_by=1,
last_update_date=sysdate,
last_updated_by=1,
last_update_login=1,
org_context_field=p_org_context_field,
set_of_books_context_field=p_set_of_books_context_field,
business_unit_context_field=p_business_unit_context_field,
context1_field=p_context1_field,
context2_field=p_context2_field,
context3_field=p_context3_field
where attachment_blk_id=l_attachment_blk_id;
hr_utility.set_location('Updated block '||p_block_name,20);
end create_or_update_block;
procedure create_or_select_entity
(p_data_object_code IN VARCHAR2
,p_entity_user_name IN VARCHAR2 default null
,p_language_code IN VARCHAR2 default null
,p_application_id IN NUMBER default null
,p_table_name IN VARCHAR2 default null
,p_entity_name IN VARCHAR2 default null
,p_pk1_column IN VARCHAR2 default null
,p_pk2_column IN VARCHAR2 default null
,p_pk3_column IN VARCHAR2 default null
,p_pk4_column IN VARCHAR2 default null
,p_pk5_column IN VARCHAR2 default null
,p_document_entity_id OUT NUMBER) is
--
cursor entity_exists is
select document_entity_id
from fnd_document_entities
where data_object_code =p_data_object_code;
select fnd_document_entities_s.nextval
into l_document_entity_id
from sys.dual;
insert into fnd_document_entities (
DOCUMENT_ENTITY_ID,
DATA_OBJECT_CODE,
APPLICATION_ID,
TABLE_NAME,
ENTITY_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PK1_COLUMN,
PK2_COLUMN,
PK3_COLUMN,
PK4_COLUMN,
PK5_COLUMN ) VALUES (
l_document_entity_id,
p_data_object_code,
p_application_id,
p_table_name,
p_entity_name,
sysdate,
1,
sysdate,
1,
1,
p_pk1_column,
p_pk2_column,
p_pk3_column,
p_pk4_column,
p_pk5_column);
insert into fnd_document_entities_tl (
DOCUMENT_ENTITY_ID,
DATA_OBJECT_CODE,
LANGUAGE,
USER_ENTITY_NAME,
USER_ENTITY_PROMPT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SOURCE_LANG) VALUES (
l_document_entity_id,
p_data_object_code,
p_language_code,
p_entity_user_name,
p_entity_user_name,
sysdate,
1,
sysdate,
1,
1,
p_language_code);
end create_or_select_entity;
,p_insert_permission_type IN VARCHAR2 default 'Y'
,p_update_permission_type IN VARCHAR2 default 'Y'
,p_delete_permission_type IN VARCHAR2 default 'Y'
,p_condition_field IN VARCHAR2 default null
,p_condition_operator IN VARCHAR2 default null
,p_condition_value1 IN VARCHAR2 default null
,p_condition_value2 IN VARCHAR2 default null
,p_attachment_blk_entity_id OUT NUMBER) is
--
cursor block_exists is
select 1
from fnd_attachment_blocks
where attachment_blk_id=p_attachment_blk_id;
select attachment_blk_entity_id
from fnd_attachment_blk_entities
where data_object_code=p_data_object_code
and attachment_blk_id=p_attachment_blk_id;
select fnd_attachment_blk_entities_s.nextval
into l_attachment_blk_entity_id
from sys.dual;
INSERT INTO fnd_attachment_blk_entities (
attachment_blk_entity_id,
attachment_blk_id,
data_object_code,
display_method,
include_in_indicator_flag,
indicator_in_view_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
pk1_field,
pk2_field,
pk3_field,
pk4_field,
pk5_field,
sql_statement,
query_permission_type,
insert_permission_type,
update_permission_type,
delete_permission_type,
condition_field,
condition_operator,
condition_value1,
condition_value2) VALUES (
l_attachment_blk_entity_id,
p_attachment_blk_id,
p_data_object_code,
p_display_method,
p_include_in_indicator_flag,
p_indicator_in_view_flag,
sysdate,
1,
sysdate,
1,
1,
p_pk1_field,
p_pk2_field,
p_pk3_field,
p_pk4_field,
p_pk5_field,
p_sql_statement,
p_query_permission_type,
p_insert_permission_type,
p_update_permission_type,
p_delete_permission_type,
p_condition_field,
p_condition_operator,
p_condition_value1,
p_condition_value2);
update fnd_attachment_blk_entities
set
display_method=p_display_method,
include_in_indicator_flag=p_include_in_indicator_flag,
indicator_in_view_flag=p_indicator_in_view_flag,
creation_date=sysdate,
created_by=1,
last_update_date=sysdate,
last_updated_by=1,
last_update_login=1,
pk1_field=p_pk1_field,
pk2_field=p_pk2_field,
pk3_field=p_pk3_field,
pk4_field=p_pk4_field,
pk5_field=p_pk5_field,
sql_statement=p_sql_statement,
query_permission_type=p_query_permission_type,
insert_permission_type=p_insert_permission_type,
update_permission_type=p_update_permission_type,
delete_permission_type=p_delete_permission_type,
condition_field=p_condition_field,
condition_operator=p_condition_operator,
condition_value1=p_condition_value1,
condition_value2=p_condition_value2
where attachment_blk_entity_id=l_attachment_blk_entity_id;
hr_utility.set_location('Updated block entity '||p_data_object_code,20);