The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
fwc.FLXDU_COLUMN_XML_TAG
,fwc.FLXDU_SEQ_NUM
,fwc.FLXDU_GROUP_NAME
,fwc.FLXDU_COLUMN_XML_DATA
from
PQP_FLXDU_COLUMNS fwc,
bne_layout_cols blc
where
blc.INTERFACE_SEQ_NUM = fwc.FLXDU_SEQ_NUM
and blc.LAYOUT_CODE =c_layout_code
and blc.interface_code =c_interface_code
and blc.INTERFACE_SEQ_NUM in (Select SEQUENCE_NUM from BNE_INTERFACE_cols_b where INTERFACE_CODE =c_interface_code
and DISPLAY_FLAG ='Y' AND sequence_num not in (19, 62, 93, 124, 155))
and fwc.DISPLAY_FLAG ='Y'
and fwc.entity_type in ('PERSON', 'ASSIGNMENT', 'ADDRESS')
order by blc.sequence_num,blc.BLOCK_ID;
SELECT FLXDU_COLUMN_NAME
,FLXDU_COLUMN_XML_TAG
,FLXDU_SEQ_NUM
,FLXDU_GROUP_NAME
,flxdu_column_xml_data
FROM PQP_FLXDU_COLUMNS
WHERE FLXDU_GROUP_NAME = c_flxdu_group_name
AND DISPLAY_FLAG ='N'
ORDER BY FLXDU_SEQ_NUM;
SELECT MAPPING_CODE
,OBJECT_VERSION_NUMBER
,INTEGRATOR_APP_ID
,INTEGRATOR_CODE
,REPORTING_FLAG
,REPORTING_INTERFACE_APP_ID
,REPORTING_INTERFACE_CODE
,APPLICATION_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
FROM BNE_MAPPINGS_B
WHERE application_id = p_application_id
AND MAPPING_CODE = c_mapping_code;
SELECT description INTO l_mapping_code FROM pqp_flxdu_columns where
entity_type = p_entity_name and
flxdu_column_name = 'MAPPING_CODE';
BNE_MAPPINGS_PKG.insert_row
(x_rowid => l_rowid
,x_application_id => l_mapping_row.application_id
,X_MAPPING_CODE => p_new_mapping_code
,x_object_version_number => 1
,X_INTEGRATOR_APP_ID => l_mapping_row.INTEGRATOR_APP_ID
,X_INTEGRATOR_CODE => p_new_intg_code--l_mapping_row.INTEGRATOR_CODE
,X_REPORTING_FLAG => l_mapping_row.REPORTING_FLAG
,X_REPORTING_INTERFACE_APP_ID => l_mapping_row.REPORTING_INTERFACE_APP_ID
,X_REPORTING_INTERFACE_CODE => l_mapping_row.REPORTING_INTERFACE_CODE
,X_USER_NAME => p_user_name
,X_CREATION_DATE => Sysdate
,X_CREATED_BY => l_mapping_row.CREATED_BY
,X_LAST_UPDATE_DATE => Sysdate
,X_LAST_UPDATED_BY => l_mapping_row.CREATED_BY
,X_LAST_UPDATE_LOGIN => l_mapping_row.CREATED_BY);
SELECT INTERFACE_CODE
,INTERFACE_SEQ_NUM
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,SEQUENCE_NUM
,INTERFACE_APP_ID
,CONTENT_CODE
,CONTENT_SEQ_NUM
,APPLICATION_ID
,MAPPING_CODE
,CONTENT_APP_ID
,DECODE_FLAG
FROM BNE_MAPPING_LINES
WHERE application_id = p_application_id
AND MAPPING_CODE = c_mapping_code;
SELECT description INTO l_mapping_code FROM pqp_flxdu_columns where
entity_type = p_entity_name and
flxdu_column_name = 'MAPPING_CODE';
--Checking the base Interface Seq number exist in selected interface number
--or by default provided sequence number
l_temp_interface_seq_num := l_mapping_cols_row.INTERFACE_SEQ_NUM;
BNE_MAPPING_LINES_PKG.INSERT_ROW
(x_rowid => l_rowid
,x_application_id => p_application_id
,X_MAPPING_CODE => p_new_mapping_code
,X_INTERFACE_APP_ID => l_mapping_cols_row.INTERFACE_APP_ID
,X_INTERFACE_CODE => p_new_interface_code
,X_INTERFACE_SEQ_NUM => l_mapping_cols_row.INTERFACE_SEQ_NUM
,X_DECODE_FLAG => l_mapping_cols_row.DECODE_FLAG
,X_OBJECT_VERSION_NUMBER => 1
,X_SEQUENCE_NUM => l_mapping_cols_row.SEQUENCE_NUM
,X_CONTENT_SEQ_NUM => l_mapping_cols_row.CONTENT_SEQ_NUM
,X_CONTENT_APP_ID => l_mapping_cols_row.CONTENT_APP_ID
,X_CONTENT_CODE => p_content_out--l_mapping_cols_row.CONTENT_CODE
,X_CREATION_DATE => sysdate
,X_CREATED_BY => l_mapping_cols_row.CREATED_BY
,X_LAST_UPDATE_DATE => Sysdate
,X_LAST_UPDATED_BY => l_mapping_cols_row.CREATED_BY
,X_LAST_UPDATE_LOGIN => l_mapping_cols_row.CREATED_BY);
BNE_MAPPING_LINES_PKG.INSERT_ROW
(x_rowid => l_rowid
,x_application_id => p_application_id
,X_MAPPING_CODE => p_new_mapping_code
,X_INTERFACE_APP_ID => l_mapping_cols_row.INTERFACE_APP_ID
,X_INTERFACE_CODE => p_new_interface_code
,X_INTERFACE_SEQ_NUM => l_mapping_cols_row.INTERFACE_SEQ_NUM
,X_DECODE_FLAG => l_mapping_cols_row.DECODE_FLAG
,X_OBJECT_VERSION_NUMBER => 1
,X_SEQUENCE_NUM => l_mapping_cols_row.SEQUENCE_NUM
,X_CONTENT_SEQ_NUM => l_mapping_cols_row.CONTENT_SEQ_NUM
,X_CONTENT_APP_ID => l_mapping_cols_row.CONTENT_APP_ID
,X_CONTENT_CODE => p_content_out--l_mapping_cols_row.CONTENT_CODE
,X_CREATION_DATE => sysdate
,X_CREATED_BY => l_mapping_cols_row.CREATED_BY
,X_LAST_UPDATE_DATE => Sysdate
,X_LAST_UPDATED_BY => l_mapping_cols_row.CREATED_BY
,X_LAST_UPDATE_LOGIN => l_mapping_cols_row.CREATED_BY);
SELECT application_id
,layout_code
,block_id
,interface_app_id
,interface_code
,interface_seq_num
,sequence_num
,style
,style_class
,hint_style
,hint_style_class
,prompt_style
,prompt_style_class
,default_type
,DEFAULT_VALUE
,created_by
,last_updated_by
,last_update_login
,display_width
,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
FROM bne_layout_cols
WHERE application_id = p_application_id
AND layout_code = p_base_layout_code
AND interface_seq_num =c_interface_seq_num
ORDER BY block_id;
SELECT application_id
,layout_code
,block_id
,interface_app_id
,interface_code
,interface_seq_num
,sequence_num
,style
,style_class
,hint_style
,hint_style_class
,prompt_style
,prompt_style_class
,default_type
,DEFAULT_VALUE
,created_by
,last_updated_by
,last_update_login
,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
FROM bne_layout_cols
WHERE application_id = p_application_id
AND layout_code = p_base_layout_code
AND INTERFACE_SEQ_NUM in (59,176,253,254,332,334,335,333,331,330,328,177,19,
62,93,124,155,340
,351 --$ Add Batch Exception as well for correct errors
,350 --$ Add Batch Link value for correct errors
)
ORDER BY block_id;
SELECT application_id
,layout_code
,block_id
,interface_app_id
,interface_code
,interface_seq_num
,sequence_num
,style
,style_class
,hint_style
,hint_style_class
,prompt_style
,prompt_style_class
,default_type
,DEFAULT_VALUE
,created_by
,last_updated_by
,last_update_login
,READ_ONLY_FLAG --$ added new column as in BNE Layout Table
FROM bne_layout_cols
WHERE application_id = p_application_id
AND layout_code = p_base_layout_code
AND INTERFACE_SEQ_NUM = c_seq_num
ORDER BY block_id;
l_insert_flag VARCHAR2(10);
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_seq_row.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_placement_block --has to change
,x_sequence_num => l_final_seq_number --has to change
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_seq_row.interface_app_id
,x_interface_code => p_new_interface_code
,x_interface_seq_num => l_layout_cols_seq_row.interface_seq_num
,x_style_class => l_layout_cols_seq_row.style_class
,x_hint_style => l_layout_cols_seq_row.hint_style
,x_hint_style_class => l_layout_cols_seq_row.hint_style_class
,x_prompt_style => l_layout_cols_seq_row.prompt_style
,x_prompt_style_class => l_layout_cols_seq_row.prompt_style_class
,x_default_type => l_default_type --has to change
,x_default_value => l_default_value --has to change
,x_style => l_layout_cols_seq_row.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_seq_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_seq_row.last_updated_by
,x_last_update_login => l_layout_cols_seq_row.last_update_login
,x_display_width => l_layout_cols_seq_row.display_width
--$ Added New Column as in BNE Layout Table
,X_READ_ONLY_FLAG => l_layout_cols_seq_row.READ_ONLY_FLAG);
/* l_insert_flag := 'false';
--Checking to know the flexfields are selected by user
IF l_layout_cols_row.interface_seq_num = 155 THEN
IF g_riw_data.EXISTS(181) THEN
l_insert_flag := 'true';
l_insert_flag := 'true';
l_insert_flag := 'true';
l_insert_flag := 'true';
l_insert_flag := 'true';
l_insert_flag := 'true';
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_layout_cols_row.block_id
,x_sequence_num => l_final_seq_number --has to change
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row.interface_app_id
,x_interface_code => p_new_interface_code
,x_interface_seq_num => l_layout_cols_row.interface_seq_num
,x_style_class => l_layout_cols_row.style_class
,x_hint_style => l_layout_cols_row.hint_style
,x_hint_style_class => l_layout_cols_row.hint_style_class
,x_prompt_style => l_layout_cols_row.prompt_style
,x_prompt_style_class => l_layout_cols_row.prompt_style_class
,x_default_type => l_layout_cols_row.default_type
,x_default_value => l_layout_cols_row.default_value
,x_style => l_layout_cols_row.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row.last_updated_by
,x_last_update_login => l_layout_cols_row.last_update_login
--$ Added New Column as in BNE Layout Table
,X_READ_ONLY_FLAG => l_layout_cols_row.READ_ONLY_FLAG);
SELECT distinct(interface_code) into l_base_intf_code
from bne_layout_cols where layout_code = p_base_layout_code;
Select sequence_num into l_context_seq_num from bne_interface_cols_b
where interface_code = l_base_intf_code and
interface_col_name = 'P_WEBADI_CONTEXT';
Select sequence_num into l_header_seq_num from bne_interface_cols_b
where interface_code = l_base_intf_code and
interface_col_name = 'P_WEBADI_HEADER';
Select sequence_num into l_bg_seq_num from bne_interface_cols_b
where interface_code = l_base_intf_code and
interface_col_name = 'BUSINESS_GRP_NAME';
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row_others.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_layout_cols_row_others.block_id
,x_sequence_num => l_final_seq_number --has to change
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row_others.interface_app_id
,x_interface_code => p_new_interface_code
,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
,x_style_class => l_layout_cols_row_others.style_class
,x_hint_style => l_layout_cols_row_others.hint_style
,x_hint_style_class => l_layout_cols_row_others.hint_style_class
,x_prompt_style => l_layout_cols_row_others.prompt_style
,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
,x_default_type => l_layout_cols_row_others.default_type
,x_default_value => l_layout_cols_row_others.default_value
,x_style => l_layout_cols_row_others.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row_others.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row_others.last_updated_by
,x_last_update_login => l_layout_cols_row_others.last_update_login
--$ Added New Column as in BNE Layout Table
,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row_others.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_layout_cols_row_others.block_id
,x_sequence_num => l_final_seq_number --has to change
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row_others.interface_app_id
,x_interface_code => p_new_interface_code
,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
,x_style_class => l_layout_cols_row_others.style_class
,x_hint_style => l_layout_cols_row_others.hint_style
,x_hint_style_class => l_layout_cols_row_others.hint_style_class
,x_prompt_style => l_layout_cols_row_others.prompt_style
,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
,x_default_type => l_layout_cols_row_others.default_type
,x_default_value => l_layout_cols_row_others.default_value
,x_style => l_layout_cols_row_others.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row_others.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row_others.last_updated_by
,x_last_update_login => l_layout_cols_row_others.last_update_login
--$ Added New Column as in BNE Layout Table
,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
bne_layout_cols_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_cols_row_others.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_layout_cols_row_others.block_id
,x_sequence_num => l_final_seq_number --has to change
,x_object_version_number => 1
,x_interface_app_id => l_layout_cols_row_others.interface_app_id
,x_interface_code => p_new_interface_code
,x_interface_seq_num => l_layout_cols_row_others.interface_seq_num
,x_style_class => l_layout_cols_row_others.style_class
,x_hint_style => l_layout_cols_row_others.hint_style
,x_hint_style_class => l_layout_cols_row_others.hint_style_class
,x_prompt_style => l_layout_cols_row_others.prompt_style
,x_prompt_style_class => l_layout_cols_row_others.prompt_style_class
,x_default_type => l_layout_cols_row_others.default_type
,x_default_value => l_layout_cols_row_others.default_value
,x_style => l_layout_cols_row_others.style
,x_creation_date => SYSDATE
,x_created_by => l_layout_cols_row_others.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_cols_row_others.last_updated_by
,x_last_update_login => l_layout_cols_row_others.last_update_login
--$ Added New Column as in BNE Layout Table
,X_READ_ONLY_FLAG => l_layout_cols_row_others.READ_ONLY_FLAG);
SELECT application_id
,block_id
,parent_id
,layout_element
,style_class
,style
,row_style_class
,row_style
,col_style_class
,col_style
,prompt_displayed_flag
,prompt_style_class
,prompt_style
,hint_displayed_flag
,hint_style_class
,hint_style
,orientation
,layout_control
,display_flag
,BLOCKSIZE
,minsize
,MAXSIZE
,sequence_num
,prompt_colspan
,hint_colspan
,row_colspan
,summary_style_class
,summary_style
,created_by
,last_updated_by
,last_update_login
,user_name
FROM bne_layout_blocks_vl
WHERE application_id = p_application_id
AND layout_code = p_base_layout_code
ORDER BY block_id;
bne_layout_blocks_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_blocks_row.application_id
,x_layout_code => p_new_layout_code
,x_block_id => l_layout_blocks_row.block_id
,x_object_version_number => 1
,x_parent_id => l_layout_blocks_row.parent_id
,x_layout_element => l_layout_blocks_row.layout_element
,x_style_class => l_layout_blocks_row.style_class
,x_style => l_layout_blocks_row.style
,x_row_style_class => l_layout_blocks_row.row_style_class
,x_row_style => l_layout_blocks_row.row_style
,x_col_style_class => l_layout_blocks_row.col_style_class
,x_col_style => l_layout_blocks_row.col_style
,x_prompt_displayed_flag => l_layout_blocks_row.prompt_displayed_flag
,x_prompt_style_class => l_layout_blocks_row.prompt_style_class
,x_prompt_style => l_layout_blocks_row.prompt_style
,x_hint_displayed_flag => l_layout_blocks_row.hint_displayed_flag
,x_hint_style_class => l_layout_blocks_row.hint_style_class
,x_hint_style => l_layout_blocks_row.hint_style
,x_orientation => l_layout_blocks_row.orientation
,x_layout_control => l_layout_blocks_row.layout_control
,x_display_flag => l_layout_blocks_row.display_flag
,x_blocksize => l_layout_blocks_row.BLOCKSIZE
,x_minsize => l_layout_blocks_row.minsize
,x_maxsize => l_layout_blocks_row.MAXSIZE
,x_sequence_num => l_layout_blocks_row.sequence_num
,x_prompt_colspan => l_layout_blocks_row.prompt_colspan
,x_hint_colspan => l_layout_blocks_row.hint_colspan
,x_row_colspan => l_layout_blocks_row.row_colspan
,x_summary_style_class => l_layout_blocks_row.summary_style_class
,x_summary_style => l_layout_blocks_row.summary_style
,x_user_name => l_layout_blocks_row.user_name
,x_creation_date => SYSDATE
,x_created_by => l_layout_blocks_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_blocks_row.last_updated_by
,x_last_update_login => l_layout_blocks_row.last_update_login);
SELECT APPLICATION_ID
,INTERFACE_CODE
,OBJECT_VERSION_NUMBER
,INTEGRATOR_APP_ID
,INTEGRATOR_CODE
,INTERFACE_NAME
,UPLOAD_TYPE
,UPLOAD_OBJ_NAME
,UPLOAD_PARAM_LIST_APP_ID
,UPLOAD_PARAM_LIST_CODE
,UPLOAD_ORDER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
FROM BNE_INTERFACES_B
WHERE application_id = p_application_id
AND INTERFACE_CODE = p_base_interface_code;
SELECT INTERFACE_CODE
INTO VV_INTERFACE_CODE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_new_interface_code;
INSERT INTO BNE_INTERFACES_B
(APPLICATION_ID
,INTERFACE_CODE
,OBJECT_VERSION_NUMBER
,INTEGRATOR_APP_ID
,INTEGRATOR_CODE
,INTERFACE_NAME
,UPLOAD_TYPE
,UPLOAD_PARAM_LIST_APP_ID
,UPLOAD_PARAM_LIST_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES
(l_interface_row.APPLICATION_ID
,p_new_interface_code
,1
,l_interface_row.APPLICATION_ID
,p_new_intg_code--l_interface_row.INTEGRATOR_CODE
,l_interface_row.INTERFACE_NAME
,l_interface_row.UPLOAD_TYPE
,l_interface_row.UPLOAD_PARAM_LIST_APP_ID
,l_interface_row.UPLOAD_PARAM_LIST_CODE
,l_interface_row.CREATED_BY
,SYSDATE
,l_interface_row.CREATED_BY
,SYSDATE);
INSERT INTO BNE_INTERFACES_TL
(APPLICATION_ID
,INTERFACE_CODE
,LANGUAGE
,SOURCE_LANG
,USER_NAME
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES
(l_interface_row.APPLICATION_ID
,p_new_interface_code
,userenv('LANG')
,userenv('LANG')
,p_user_name
,l_interface_row.CREATED_BY
,SYSDATE
,l_interface_row.CREATED_BY
,SYSDATE);
SELECT APPLICATION_ID
,INTERFACE_CODE
,OBJECT_VERSION_NUMBER
,SEQUENCE_NUM
,INTERFACE_COL_TYPE
,INTERFACE_COL_NAME
,ENABLED_FLAG
,REQUIRED_FLAG
,DISPLAY_FLAG
,READ_ONLY_FLAG
,NOT_NULL_FLAG
,SUMMARY_FLAG
,MAPPING_ENABLED_FLAG
,DATA_TYPE
,FIELD_SIZE
,DEFAULT_TYPE
,DEFAULT_VALUE
,SEGMENT_NUMBER
,GROUP_NAME
,OA_FLEX_CODE
,OA_CONCAT_FLEX
,VAL_TYPE
,VAL_ID_COL
,VAL_MEAN_COL
,VAL_DESC_COL
,VAL_OBJ_NAME
,VAL_ADDL_W_C
,VAL_COMPONENT_APP_ID
,VAL_COMPONENT_CODE
,OA_FLEX_NUM
,OA_FLEX_APPLICATION_ID
,DISPLAY_ORDER
,UPLOAD_PARAM_LIST_ITEM_NUM
,EXPANDED_SQL_QUERY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LOV_TYPE
,OFFLINE_LOV_ENABLED_FLAG
,VARIABLE_DATA_TYPE_CLASS
FROM BNE_INTERFACE_COLS_B
WHERE application_id = p_application_id
AND INTERFACE_CODE = p_base_interface_code;
SELECT APPLICATION_ID
,INTERFACE_CODE
,SEQUENCE_NUM
,LANGUAGE
,SOURCE_LANG
,USER_HINT
,PROMPT_LEFT
,USER_HELP_TEXT
,PROMPT_ABOVE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
FROM BNE_INTERFACE_COLS_TL
WHERE application_id = p_application_id
AND INTERFACE_CODE = p_base_interface_code
AND SEQUENCE_NUM = c_seq_num
AND LANGUAGE = user_lang;
SELECT sequence_num INTO l_crt_upd_seq_num FROM BNE_INTERFACE_COLS_B WHERE
INTERFACE_CODE = p_base_interface_code AND
INTERFACE_COL_NAME = 'P_CRT_UPD';
SELECT 1
INTO VN_NO_INTERFACE_COL_FLAG
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_new_interface_code;
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID
,INTERFACE_CODE
,OBJECT_VERSION_NUMBER
,SEQUENCE_NUM
,INTERFACE_COL_TYPE
,INTERFACE_COL_NAME
,ENABLED_FLAG
,REQUIRED_FLAG
,DISPLAY_FLAG
,READ_ONLY_FLAG
,NOT_NULL_FLAG
,SUMMARY_FLAG
,MAPPING_ENABLED_FLAG
,DATA_TYPE
,FIELD_SIZE
,DEFAULT_TYPE
,DEFAULT_VALUE
,SEGMENT_NUMBER
,GROUP_NAME
,OA_FLEX_CODE
,OA_CONCAT_FLEX
,VAL_TYPE
,VAL_ID_COL
,VAL_MEAN_COL
,VAL_DESC_COL
,VAL_OBJ_NAME
,VAL_ADDL_W_C
,VAL_COMPONENT_APP_ID
,VAL_COMPONENT_CODE
,OA_FLEX_NUM
,OA_FLEX_APPLICATION_ID
,DISPLAY_ORDER
,UPLOAD_PARAM_LIST_ITEM_NUM
,EXPANDED_SQL_QUERY
,LOV_TYPE
,OFFLINE_LOV_ENABLED_FLAG
,VARIABLE_DATA_TYPE_CLASS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES
(l_interface_cols_row.APPLICATION_ID
,p_new_interface_code
,1
,l_interface_cols_row.SEQUENCE_NUM
,l_interface_cols_row.INTERFACE_COL_TYPE
,l_interface_cols_row.INTERFACE_COL_NAME
,l_interface_cols_row.ENABLED_FLAG
,l_interface_cols_row.REQUIRED_FLAG
,l_display --have to change based on layout selection
,NVL(l_interface_cols_row.READ_ONLY_FLAG,'N')
,l_interface_cols_row.NOT_NULL_FLAG
,NVL(l_interface_cols_row.SUMMARY_FLAG,'N')
,l_interface_cols_row.MAPPING_ENABLED_FLAG
,l_interface_cols_row.DATA_TYPE
,l_interface_cols_row.FIELD_SIZE
,l_default_type
,l_default_value
,l_interface_cols_row.SEGMENT_NUMBER
,l_interface_cols_row.GROUP_NAME
,l_interface_cols_row.OA_FLEX_CODE
,l_interface_cols_row.OA_CONCAT_FLEX
,l_interface_cols_row.VAL_TYPE
,l_interface_cols_row.VAL_ID_COL
,l_interface_cols_row.VAL_MEAN_COL
,l_interface_cols_row.VAL_DESC_COL
,l_interface_cols_row.VAL_OBJ_NAME
,l_interface_cols_row.VAL_ADDL_W_C
,l_interface_cols_row.VAL_COMPONENT_APP_ID
,l_interface_cols_row.VAL_COMPONENT_CODE
,l_interface_cols_row.OA_FLEX_NUM
,l_interface_cols_row.OA_FLEX_APPLICATION_ID
,l_interface_cols_row.DISPLAY_ORDER
,l_interface_cols_row.UPLOAD_PARAM_LIST_ITEM_NUM
,l_interface_cols_row.EXPANDED_SQL_QUERY
,l_interface_cols_row.LOV_TYPE
,l_interface_cols_row.OFFLINE_LOV_ENABLED_FLAG
,l_interface_cols_row.VARIABLE_DATA_TYPE_CLASS
,l_interface_cols_row.CREATED_BY
,SYSDATE
,l_interface_cols_row.CREATED_BY
,SYSDATE);
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID
,INTERFACE_CODE
,SEQUENCE_NUM
,LANGUAGE
,SOURCE_LANG
,USER_HINT
,PROMPT_LEFT
,USER_HELP_TEXT
,PROMPT_ABOVE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES
(l_interface_tl_cols_row.APPLICATION_ID
,p_new_interface_code
,l_interface_tl_cols_row.SEQUENCE_NUM
,userenv('LANG')
,userenv('LANG')
,l_interface_tl_cols_row.USER_HINT
,l_prompt_left --have to change based on layout selection
,l_interface_tl_cols_row.USER_HELP_TEXT
,l_prompt_above --have to change based on layout selection
,l_interface_cols_row.CREATED_BY
,SYSDATE
,l_interface_cols_row.CREATED_BY
,SYSDATE);
SELECT application_id
,object_version_number
,stylesheet_app_id
,stylesheet_code
,integrator_app_id
,integrator_code
,style
,style_class
,reporting_flag
,reporting_interface_app_id
,reporting_interface_code
,created_by
,last_updated_by
,last_update_login
,create_doc_list_app_id
,create_doc_list_code
FROM bne_layouts_b
WHERE application_id = p_application_id
AND layout_code = p_base_layout_code;
bne_layouts_pkg.insert_row
(x_rowid => l_rowid
,x_application_id => l_layout_row.application_id
,x_layout_code => p_new_layout_code
,x_object_version_number => 1
,x_stylesheet_app_id => l_layout_row.stylesheet_app_id
,x_stylesheet_code => l_layout_row.stylesheet_code
,x_integrator_app_id => l_layout_row.integrator_app_id
,x_integrator_code => p_new_intg_code--l_layout_row.integrator_code
,x_style => l_layout_row.style
,x_style_class => l_layout_row.style_class
,x_reporting_flag => l_layout_row.reporting_flag
,x_reporting_interface_app_id => l_layout_row.reporting_interface_app_id
,x_reporting_interface_code => l_layout_row.reporting_interface_code
,x_user_name => p_user_name
,x_creation_date => SYSDATE
,x_created_by => l_layout_row.created_by
,x_last_update_date => SYSDATE
,x_last_updated_by => l_layout_row.last_updated_by
,x_last_update_login => l_layout_row.last_update_login
,x_create_doc_list_app_id => l_layout_row.create_doc_list_app_id
,x_create_doc_list_code => l_layout_row.create_doc_list_code);
SELECT WEB_ICON
,WEB_HOST_NAME
,WEB_AGENT_NAME
,WEB_HTML_CALL
,WEB_ENCRYPT_PARAMETERS
,WEB_SECURED
,OBJECT_ID
,REGION_APPLICATION_ID
,REGION_CODE
,FUNCTION_ID
,FUNCTION_NAME
,APPLICATION_ID
,FORM_ID
,PARAMETERS
,TYPE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,MAINTENANCE_MODE_SUPPORT
,CONTEXT_DEPENDENCE
,JRAD_REF_PATH
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_NAME = c_function_name;
IF p_action_type = 'Update' THEN
SELECT fff.function_name into l_function_name
FROM fnd_form_functions fff, fnd_form_functions_tl ffft
WHERE fff.FUNCTION_ID = ffft.FUNCTION_ID
AND ffft.SOURCE_LANG = userenv('LANG')
AND ffft.LANGUAGE = userenv('LANG')
AND ffft.USER_FUNCTION_NAME = p_user_function_name;
IF p_action_type = 'Update' THEN
l_func_parameters := l_func_parameters||'$pFunctionId='|| l_function_row.FUNCTION_ID;
fnd_form_functions_pkg.UPDATE_ROW
(X_FUNCTION_ID => l_function_row.FUNCTION_ID
,X_WEB_HOST_NAME => l_function_row.WEB_HOST_NAME
,X_WEB_AGENT_NAME => l_function_row.WEB_AGENT_NAME
,X_WEB_HTML_CALL => l_function_row.WEB_HTML_CALL --can be changed later
,X_WEB_ENCRYPT_PARAMETERS => l_function_row.WEB_ENCRYPT_PARAMETERS
,X_WEB_SECURED => l_function_row.WEB_SECURED
,X_WEB_ICON => l_function_row.WEB_ICON
,X_OBJECT_ID => l_function_row.OBJECT_ID
,X_REGION_APPLICATION_ID => l_function_row.REGION_APPLICATION_ID
,X_REGION_CODE => l_function_row.REGION_CODE
,X_FUNCTION_NAME => l_function_row.FUNCTION_NAME
,X_APPLICATION_ID => l_function_row.APPLICATION_ID
,X_FORM_ID => l_function_row.FORM_ID
,X_PARAMETERS => l_func_parameters --can be changed later
,X_TYPE => l_function_row.TYPE
,X_USER_FUNCTION_NAME => p_user_function_name --can be changed later
,X_DESCRIPTION => p_user_function_name --can be changed later
,X_LAST_UPDATE_DATE => sysdate
,X_LAST_UPDATED_BY => l_function_row.CREATED_BY
,X_LAST_UPDATE_LOGIN => 0);
update PQP_FLXDU_FUNC_ATTRIBUTES
set FLXDU_FUNC_INTEGRATOR_CODE = p_new_interface_code
where flxdu_func_attribute_id = l_function_row.FUNCTION_ID
and flxdu_func_name = l_function_row.FUNCTION_NAME;
SELECT fnd_form_functions_s.nextval
INTO l_fun_id
FROM dual;
fnd_form_functions_pkg.INSERT_ROW
(X_ROWID => l_rowid
,X_FUNCTION_ID => l_fun_id
,X_WEB_HOST_NAME => l_function_row.WEB_HOST_NAME
,X_WEB_AGENT_NAME => l_function_row.WEB_AGENT_NAME
,X_WEB_HTML_CALL => l_function_row.WEB_HTML_CALL--can be changed later
,X_WEB_ENCRYPT_PARAMETERS => l_function_row.WEB_ENCRYPT_PARAMETERS
,X_WEB_SECURED => l_function_row.WEB_SECURED
,X_WEB_ICON => l_function_row.WEB_ICON
,X_OBJECT_ID => l_function_row.OBJECT_ID
,X_REGION_APPLICATION_ID => l_function_row.REGION_APPLICATION_ID
,X_REGION_CODE => l_function_row.REGION_CODE
,X_FUNCTION_NAME => p_function_name
,X_APPLICATION_ID => p_application_id
,X_FORM_ID => l_function_row.FORM_ID
,X_PARAMETERS => l_func_parameters --can be changed later
,X_TYPE => l_function_row.TYPE
,X_USER_FUNCTION_NAME => p_user_function_name --can be changed later
,X_DESCRIPTION => p_user_function_name --can be changed later
,X_CREATION_DATE => Sysdate
,X_CREATED_BY => l_function_row.CREATED_BY
,X_LAST_UPDATE_DATE => sysdate
,X_LAST_UPDATED_BY => l_function_row.CREATED_BY
,X_LAST_UPDATE_LOGIN => 0
,X_MAINTENANCE_MODE_SUPPORT => l_function_row.MAINTENANCE_MODE_SUPPORT
,X_CONTEXT_DEPENDENCE => l_function_row.CONTEXT_DEPENDENCE
,X_JRAD_REF_PATH => l_function_row.JRAD_REF_PATH);
IF p_action_type = 'Update' THEN
update PQP_FLXDU_FUNC_ATTRIBUTES set flxdu_func_xml_data = poXML
where flxdu_func_attribute_id = l_function_row.FUNCTION_ID
and flxdu_func_name = l_function_row.FUNCTION_NAME;
INSERT INTO PQP_FLXDU_FUNC_ATTRIBUTES
(FLXDU_FUNC_ATTRIBUTE_ID
,FLXDU_FUNC_NAME
,FLXDU_FUNC_SOURCE_TYPE
,FLXDU_FUNC_INTEGRATOR_CODE
,FLXDU_FUNC_XML_DATA
,LEGISLATION_CODE
,DESCRIPTION
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES
(l_fun_id
,p_function_name
,p_data_source
,p_new_interface_code
,poXML
,'US'
,p_user_function_name
,l_function_row.CREATED_BY
,SYSDATE
,l_function_row.CREATED_BY
,SYSDATE
,l_function_row.CREATED_BY
,1);
SELECT function_id
INTO l_fun_id
FROM fnd_form_functions
WHERE function_name = p_function_name;
SELECT max(ENTRY_SEQUENCE)
INTO l_ENTRY_SEQUENCE
FROM fnd_menu_entries
WHERE MENU_ID= p_menu_id ;
fnd_menu_entries_pkg.insert_row
(X_ROWID => l_rowid
,X_MENU_ID => p_menu_id
,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE+1
,X_SUB_MENU_ID => null
,X_FUNCTION_ID => l_fun_id
,X_GRANT_FLAG => 'Y'
,X_PROMPT => p_user_function_name --Can be change later
,X_DESCRIPTION => p_user_function_name --can be change later
,X_CREATION_DATE => sysdate
,X_CREATED_BY => 1
,X_LAST_UPDATE_DATE => sysdate
,X_LAST_UPDATED_BY => 1
,X_LAST_UPDATE_LOGIN => 0);
PROCEDURE Delete_riw_integrator(p_LAYOUT_CODE IN VARCHAR2 default null
,p_MAPPING_CODE IN VARCHAR2 default null
,p_INTERFACE_CODE IN VARCHAR2
,p_application_id IN NUMBER ) IS
--$ get Integrator Code to delete entire integrator setup in one shot using
-- Function bne_integrator_utils.DELETE_INTEGRATOR
CURSOR csr_get_integrator_code is
select integrator_code from bne_interfaces_b where interface_code
= p_INTERFACE_CODE and application_id = p_application_id;
l_proc varchar2(72) := g_package||'Delete_riw_integrator';
DELETE FROM BNE_PARAM_LISTS_TL WHERE PARAM_LIST_CODE = l_param_list_code and application_id = p_application_id;
DELETE FROM BNE_PARAM_LISTS_B WHERE PARAM_LIST_CODE = l_param_list_code and application_id = p_application_id;
cnt := bne_integrator_utils.DELETE_INTEGRATOR
( P_INTEGRATOR_CODE => l_intg_code,
P_APPLICATION_ID => p_application_id);
/* DELETE
FROM BNE_MAPPING_LINES
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = p_MAPPING_CODE
AND INTERFACE_CODE =p_INTERFACE_CODE;
DELETE
FROM BNE_MAPPINGS_tl
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = p_MAPPING_CODE;
DELETE
FROM BNE_MAPPINGS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = p_MAPPING_CODE;
DELETE
FROM BNE_LAYOUT_COLS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = p_LAYOUT_CODE;
DELETE
FROM bne_layout_blocks_tl
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = p_LAYOUT_CODE;
DELETE
FROM bne_layout_blocks_b
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = p_LAYOUT_CODE;
DELETE
FROM bne_layouts_tl
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = p_LAYOUT_CODE;
DELETE
FROM bne_layouts_b
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = p_LAYOUT_CODE;
DELETE
FROM BNE_INTERFACE_cols_tl
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_INTERFACE_CODE;
DELETE
FROM BNE_INTERFACE_cols_b
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_INTERFACE_CODE;
DELETE
FROM BNE_INTERFACES_tl
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_INTERFACE_CODE;
DELETE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = p_INTERFACE_CODE; */
END Delete_riw_integrator;
SELECT APPLICATION_ID
,INTEGRATOR_CODE
,OBJECT_VERSION_NUMBER
,ENABLED_FLAG
,UPLOAD_PARAM_LIST_APP_ID
,UPLOAD_PARAM_LIST_CODE
,UPLOAD_SERV_PARAM_LIST_APP_ID
,UPLOAD_SERV_PARAM_LIST_CODE
,IMPORT_PARAM_LIST_APP_ID
,IMPORT_PARAM_LIST_CODE
,UPLOADER_CLASS
,DATE_FORMAT
,IMPORT_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,CREATE_DOC_LIST_APP_ID
,CREATE_DOC_LIST_CODE
,NEW_SESSION_FLAG
FROM BNE_INTEGRATORS_B
WHERE application_id = p_application_id
AND INTEGRATOR_CODE = p_base_intg_code;
SELECT INTEGRATOR_CODE
INTO VV_INTEGRATOR_CODE
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = p_new_intg_code;
INSERT INTO BNE_INTEGRATORS_B
(APPLICATION_ID
,INTEGRATOR_CODE
,OBJECT_VERSION_NUMBER
,ENABLED_FLAG
,UPLOAD_PARAM_LIST_APP_ID
,UPLOAD_PARAM_LIST_CODE
,UPLOAD_SERV_PARAM_LIST_APP_ID
,UPLOAD_SERV_PARAM_LIST_CODE
,IMPORT_PARAM_LIST_APP_ID
,IMPORT_PARAM_LIST_CODE
,UPLOADER_CLASS
,DATE_FORMAT
,IMPORT_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,CREATE_DOC_LIST_APP_ID
,CREATE_DOC_LIST_CODE
,NEW_SESSION_FLAG )
VALUES
(l_intg_row.APPLICATION_ID
,p_new_intg_code
,1
,l_intg_row.ENABLED_FLAG
,l_intg_row.UPLOAD_PARAM_LIST_APP_ID
,l_intg_row.UPLOAD_PARAM_LIST_CODE
,l_intg_row.UPLOAD_SERV_PARAM_LIST_APP_ID
,l_intg_row.UPLOAD_SERV_PARAM_LIST_CODE
,l_intg_row.IMPORT_PARAM_LIST_APP_ID
,l_intg_row.IMPORT_PARAM_LIST_CODE
,l_intg_row.UPLOADER_CLASS
,l_intg_row.DATE_FORMAT
,l_intg_row.IMPORT_TYPE
,l_intg_row.CREATED_BY
,SYSDATE
,l_intg_row.LAST_UPDATED_BY
,l_intg_row.LAST_UPDATE_LOGIN
,SYSDATE
,l_intg_row.CREATE_DOC_LIST_APP_ID
,l_intg_row.CREATE_DOC_LIST_CODE
,l_intg_row.NEW_SESSION_FLAG
);
INSERT INTO BNE_INTEGRATORS_TL
(APPLICATION_ID
,INTEGRATOR_CODE
,LANGUAGE
,SOURCE_LANG
,USER_NAME
,UPLOAD_HEADER
,UPLOAD_TITLE_BAR
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES
(l_intg_row.APPLICATION_ID
,p_new_intg_code
,userenv('LANG')
,userenv('LANG')
,p_integrator_name
,'Upload Parameters'
,'Upload Parameters'
,l_intg_row.CREATED_BY
,SYSDATE
,l_intg_row.LAST_UPDATED_BY
,SYSDATE);
SELECT APPLICATION_ID
,CONTENT_CODE
,OBJECT_VERSION_NUMBER
,INTEGRATOR_APP_ID
,INTEGRATOR_CODE
,PARAM_LIST_APP_ID
,PARAM_LIST_CODE
,CONTENT_CLASS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
FROM bne_contents_b
WHERE application_id = p_application_id
AND CONTENT_CODE = c_base_content_code;
l_temp_val := l_temp_val ||'ProjectedStartDate,ResumeExists,ResumeLastUpdated,SecondPassportExists,StudentStatus,WorkSchedule,Suffix,';
update bne_contents_b
set param_list_code = l_content_row.param_list_code
,param_list_app_id = l_content_row.param_list_app_id
where content_code =l_content_out;
update bne_content_cols_b set read_only_flag = 'Y' where content_code =
l_content_out and sequence_num in (2,316,317);
update bne_contents_b
set param_list_code = l_content_row.param_list_code
,param_list_app_id = l_content_row.param_list_app_id
where content_code = l_ce_content_out;
update bne_content_cols_b set read_only_flag = 'Y' where content_code in
(l_ce_content_out) and sequence_num in (2,317,318);
update bne_content_cols_b set read_only_flag = 'Y' where content_code in
(l_ce_content_out) and sequence_num = 270;
SELECT APPLICATION_ID
,CONTENT_CODE
,OBJECT_VERSION_NUMBER
,INTEGRATOR_APP_ID
,INTEGRATOR_CODE
,PARAM_LIST_APP_ID
,PARAM_LIST_CODE
,CONTENT_CLASS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
FROM bne_contents_b
WHERE application_id = p_application_id
AND CONTENT_CODE = c_base_content_code;
SELECT col_name, read_only_flag, sequence_num
FROM bne_content_cols_b
WHERE application_id = p_application_id
AND content_code = c_base_content_code
ORDER BY sequence_num;
update bne_contents_b
set param_list_code = l_content_row.param_list_code
,param_list_app_id = l_content_row.param_list_app_id
where content_code =l_content_out;
update bne_content_cols_b set read_only_flag = l_read_only_flag
where content_code = l_content_out
and sequence_num = l_sequence_num;
l_allow_insert VARCHAR2(10);
l_allow_update VARCHAR2(10);
l_allow_insert_loc NUMBER;
l_allow_update_loc NUMBER;
SELECT description into l_func_type
FROM pqp_flxdu_columns WHERE
entity_type = p_entity_name AND
flxdu_column_name = 'PQP_TYPE_FLAG';
SELECT PQP_FLXDU_FUNCTIONS_S.nextval
INTO l_riw_seq_id
FROM dual;
SELECT description INTO l_base_intg_code FROM PQP_FLXDU_COLUMNS WHERE
FLXDU_COLUMN_NAME = 'INTEGRATOR_CODE' and
ENTITY_TYPE = p_entity_name;
SELECT description INTO l_base_layout_code FROM PQP_FLXDU_COLUMNS WHERE
FLXDU_COLUMN_NAME = 'LAYOUT_CODE' and
ENTITY_TYPE = p_entity_name;
SELECT description INTO l_base_content_code FROM PQP_FLXDU_COLUMNS WHERE
FLXDU_COLUMN_NAME = 'CONTENT_CODE' and
ENTITY_TYPE = p_entity_name;
SELECT description INTO l_base_blnk_content_code FROM PQP_FLXDU_COLUMNS WHERE
FLXDU_COLUMN_NAME = 'BLANK_CONTENT_CODE' and
ENTITY_TYPE = p_entity_name;
SELECT description INTO l_base_intf_code FROM PQP_FLXDU_COLUMNS WHERE
FLXDU_COLUMN_NAME = 'INTERFACE_CODE' and
ENTITY_TYPE = p_entity_name;
l_allow_insert_loc := INSTR(l_ins_upd_datapmp_flag, ':');
l_allow_insert := SUBSTR(l_ins_upd_datapmp_flag, 1, l_allow_insert_loc-1);
l_flag1 := SUBSTR(l_ins_upd_datapmp_flag, l_allow_insert_loc+1, LENGTH(l_ins_upd_datapmp_flag));
hr_utility.set_location('Allow Insert Is '|| l_allow_insert, 7);
IF l_allow_insert = 'Create' THEN
g_ins_upd_flag := 'C';
IF l_allow_insert = 'Update' THEN
g_ins_upd_flag := 'U';
IF l_allow_insert = 'Download' THEN
g_ins_upd_flag := 'D';
hr_utility.set_location('Allow Insert Is '|| g_ins_upd_flag, 7);
IF p_action_type ='Update' THEN
hr_utility.trace('Inside Update:');
Delete_riw_integrator
(p_LAYOUT_CODE => p_upd_layout_code
,p_MAPPING_CODE => p_upd_mapping_code
,p_INTERFACE_CODE => p_upd_interface_code
,p_application_id => p_application_id ) ;
IF p_action_type = 'Update' THEN
SELECT fff.function_name into l_function_name
FROM fnd_form_functions fff, fnd_form_functions_tl ffft
WHERE fff.FUNCTION_ID = ffft.FUNCTION_ID
AND ffft.SOURCE_LANG = userenv('LANG')
AND ffft.LANGUAGE = userenv('LANG')
AND ffft.USER_FUNCTION_NAME = p_user_function_name;
SELECT object_version_number
into l_ovn
FROM per_ri_setup_sub_tasks
WHERE setup_sub_task_code = l_function_name;
per_ri_setup_sub_task_api.update_setup_sub_task(
p_validate => false
,p_setup_sub_task_code => l_function_name
,p_setup_sub_task_action => l_setup_sub_task_action
,p_effective_date => sysdate
,p_object_version_number => l_ovn
);
IF p_action_type <> 'Update' THEN
Create_RIW_Menu_Entries
(p_application_id =>p_application_id
,p_menu_id =>p_menu_id
,p_function_name =>l_function_name
,p_user_function_name =>p_user_function_name);
SELECT max(SETUP_SUB_TASK_SEQUENCE)
INTO l_seq
FROM per_ri_setup_sub_tasks
WHERE SETUP_TASK_CODE='LOAD_EMPLOYEE_DETAILS';
g_temp_riw_data.DELETE;
g_riw_data.DELETE;
g_temp_riw_data.DELETE;
g_riw_data.DELETE;
PROCEDURE Delete_RIW_Webadi_Setup
(p_function_id IN NUMBER
,p_menu_id IN NUMBER) IS
CURSOR csr_get_fun_name IS
SELECT setup_sub_task_code
,object_version_number
FROM per_ri_setup_sub_tasks
WHERE setup_sub_task_code --$ setup_sub_task_action
= (SELECT FLXDU_FUNC_NAME
FROM PQP_FLXDU_FUNC_ATTRIBUTES
WHERE FLXDU_FUNC_ATTRIBUTE_ID = p_function_id );
select flxdu_func_integrator_code from
PQP_FLXDU_FUNC_ATTRIBUTES where FLXDU_FUNC_ATTRIBUTE_ID = p_function_id;
l_proc_name VARCHAR2(72) := g_package||'Delete_RIW_Webadi_Setup';
SELECT count(function_id)
INTO l_count
FROM fnd_menu_entries
WHERE function_id = p_function_id;
SELECT ENTRY_SEQUENCE
,MENU_ID
INTO l_ENTRY_SEQUENCE
,l_menu_id
FROM fnd_menu_entries
WHERE function_id = p_function_id;
FND_MENU_ENTRIES_PKG.DELETE_ROW(
X_MENU_ID => l_menu_id
,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE);
per_ri_setup_sub_task_api.delete_setup_sub_task
(p_validate => false
,p_setup_sub_task_code => l_setup_sub_task_code
,p_object_version_number => l_ovn);
DELETE
FROM PQP_FLXDU_FUNC_ATTRIBUTES
WHERE FLXDU_FUNC_ATTRIBUTE_ID = p_function_id;
FND_FORM_FUNCTIONS_PKG.DELETE_ROW(
X_FUNCTION_ID => p_function_id);
hr_utility.trace('DELETE: INTERFACE='|| l_intf_code);
Delete_riw_integrator(p_INTERFACE_CODE => l_intf_code
,p_application_id => 8303 );
SELECT ENTRY_SEQUENCE INTO l_ENTRY_SEQUENCE
FROM fnd_menu_entries
WHERE MENU_ID = l_menu_id
AND function_id = p_function_id;
FND_MENU_ENTRIES_PKG.DELETE_ROW(
X_MENU_ID => p_menu_id
,X_ENTRY_SEQUENCE => l_ENTRY_SEQUENCE);
END Delete_RIW_Webadi_Setup;
SELECT PQP_FLXDU_XML_TAGS_S.nextval
INTO l_riw_xml_id
FROM dual;
SELECT count(FLXDU_XML_TAG_ID)
INTO l_count
FROM PQP_FLXDU_XML_TAGS
WHERE BUSINESS_GROUP_ID = p_business_group_id
AND FLXDU_COLUMN_ID = p_field_id;
insert into PQP_FLXDU_XML_TAGS
(FLXDU_COLUMN_ID
,FLXDU_XML_TAG_ID
,FLXDU_XML_TAG_NAME
,BUSINESS_GROUP_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
values
(p_field_id
,l_riw_xml_id
,p_xml_tag_name
,p_business_group_id
,1
,sysdate
,1
,sysdate
,1
,1);
UPDATE PQP_FLXDU_XML_TAGS
SET FLXDU_XML_TAG_NAME = p_xml_tag_name
WHERE FLXDU_XML_TAG_ID = p_xml_tag_id;
DELETE
FROM PQP_FLXDU_XML_TAGS
WHERE FLXDU_XML_TAG_ID = p_xml_tag_id;
PROCEDURE Delete_RIW_XML_Tag
(p_xml_tag_id IN NUMBER
,p_business_group_id IN NUMBER) IS
l_count NUMBER;
l_proc VARCHAR(72) := g_package||'Delete_RIW_XML_Tag';
DELETE
FROM PQP_FLXDU_XML_TAGS
WHERE FLXDU_XML_TAG_ID = p_xml_tag_id
AND BUSINESS_GROUP_ID = p_business_group_id;
END Delete_RIW_XML_Tag;
select exception_text from hr_pump_batch_exceptions where source_id in (select
batch_line_id from hr_pump_batch_lines where batch_id = p_batch_id and
link_value = p_batch_link);
SELECT concatenated_segment_delimiter, context_column_name
FROM fnd_descriptive_flexs
WHERE descriptive_flexfield_name = (SELECT descriptive_flexfield_name
FROM fnd_descriptive_flexs_tl
WHERE title = c_dff_name
AND language = userenv('LANG'))
AND application_id = c_app_id;
SELECT fd.application_column_name
FROM fnd_descr_flex_column_usages fd,
fnd_descr_flex_col_usage_tl fdtl
WHERE fd.descriptive_flexfield_name = fdtl.descriptive_flexfield_name
AND fd.descriptive_flex_context_code=fdtl.descriptive_flex_context_code
AND fd.descriptive_flexfield_name = (SELECT descriptive_flexfield_name
FROM fnd_descriptive_flexs_tl
WHERE title = c_dff_name
AND language = userenv('LANG'))
AND fd.descriptive_flex_context_code in (c_context)
AND fd.application_column_name = fdtl.application_column_name
AND fdtl.language = userenv('LANG')
AND fd.ENABLED_FLAG = 'Y' --Changed by pkagrawa
-- AND fd.DISPLAY_FLAG = 'Y' --Changed by pkagrawa
ORDER BY fd.column_seq_num;
l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
' FROM per_people_f ' ||
' WHERE person_id = :p_entity_id' ||
' AND :dt BETWEEN effective_start_date AND effective_end_date';
l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
' FROM per_assignments_f ' ||
' WHERE assignment_id = :p_entity_id' ||
' AND :dt BETWEEN effective_start_date AND effective_end_date';
l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
' FROM ota_delegate_bookings ' ||
' WHERE booking_id = :p_entity_id';
l_dyn_sql_qry := ' SELECT ' || l_segment_list ||
' FROM per_addresses ' ||
' WHERE address_id = :p_entity_id';