[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from BNE_INTEGRATORS_TL T
where not exists
(select NULL
from BNE_INTEGRATORS_B B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.INTEGRATOR_CODE = T.INTEGRATOR_CODE
) and t.application_id=274 and t.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG');
update BNE_INTEGRATORS_TL T set (
USER_NAME,
UPLOAD_TITLE_BAR,
UPLOAD_HEADER
) = (select
B.USER_NAME,
B.UPLOAD_TITLE_BAR,
B.UPLOAD_HEADER
from BNE_INTEGRATORS_TL B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.INTEGRATOR_CODE = T.INTEGRATOR_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.APPLICATION_ID,
T.INTEGRATOR_CODE,
T.LANGUAGE
) in (select
SUBT.APPLICATION_ID,
SUBT.INTEGRATOR_CODE,
SUBT.LANGUAGE
from BNE_INTEGRATORS_TL SUBB, BNE_INTEGRATORS_TL SUBT
where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
and SUBB.INTEGRATOR_CODE = SUBT.INTEGRATOR_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_NAME <> SUBT.USER_NAME
or SUBB.UPLOAD_TITLE_BAR <> SUBT.UPLOAD_TITLE_BAR
or (SUBB.UPLOAD_TITLE_BAR is null and SUBT.UPLOAD_TITLE_BAR is not null)
or (SUBB.UPLOAD_TITLE_BAR is not null and SUBT.UPLOAD_TITLE_BAR is null)
or SUBB.UPLOAD_HEADER <> SUBT.UPLOAD_HEADER
or (SUBB.UPLOAD_HEADER is null and SUBT.UPLOAD_HEADER is not null)
or (SUBB.UPLOAD_HEADER is not null and SUBT.UPLOAD_HEADER is null)
)) and t.application_id = 274 and t.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG') ;
insert into BNE_INTEGRATORS_TL (
APPLICATION_ID,
INTEGRATOR_CODE,
USER_NAME,
UPLOAD_HEADER,
UPLOAD_TITLE_BAR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.APPLICATION_ID,
B.INTEGRATOR_CODE,
B.USER_NAME,
B.UPLOAD_HEADER,
B.UPLOAD_TITLE_BAR,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.LAST_UPDATE_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from BNE_INTEGRATORS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from BNE_INTEGRATORS_TL T
where T.APPLICATION_ID = B.APPLICATION_ID
and T.INTEGRATOR_CODE = B.INTEGRATOR_CODE
and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.integrator_code not in ('FEM_BALANCES_INTG','FEM_DIM_MEMBER_INTG','FEM_HIERARCHY_INTG');
delete from BNE_CONTENTS_TL T
where not exists
(select NULL
from BNE_CONTENTS_B B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.CONTENT_CODE = T.CONTENT_CODE
) and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
update BNE_CONTENTS_TL T set (
USER_NAME
) = (select
B.USER_NAME
from BNE_CONTENTS_TL B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.CONTENT_CODE = T.CONTENT_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.APPLICATION_ID,
T.CONTENT_CODE,
T.LANGUAGE
) in (select
SUBT.APPLICATION_ID,
SUBT.CONTENT_CODE,
SUBT.LANGUAGE
from BNE_CONTENTS_TL SUBB, BNE_CONTENTS_TL SUBT
where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
and SUBB.CONTENT_CODE = SUBT.CONTENT_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_NAME <> SUBT.USER_NAME))
and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
insert into BNE_CONTENTS_TL (
CONTENT_CODE,
USER_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
APPLICATION_ID,
LANGUAGE,
SOURCE_LANG
) select
B.CONTENT_CODE,
B.USER_NAME,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.APPLICATION_ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from BNE_CONTENTS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from BNE_CONTENTS_TL T
where T.APPLICATION_ID = B.APPLICATION_ID
and T.CONTENT_CODE = B.CONTENT_CODE
and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
delete from BNE_CONTENT_COLS_TL T
where not exists
(select NULL
from BNE_CONTENT_COLS_B B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.CONTENT_CODE = T.CONTENT_CODE
and B.SEQUENCE_NUM = T.SEQUENCE_NUM
) and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
update BNE_CONTENT_COLS_TL T set (
USER_NAME
) = (select
B.USER_NAME
from BNE_CONTENT_COLS_TL B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.CONTENT_CODE = T.CONTENT_CODE
and B.SEQUENCE_NUM = T.SEQUENCE_NUM
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.APPLICATION_ID,
T.CONTENT_CODE,
T.SEQUENCE_NUM,
T.LANGUAGE
) in (select
SUBT.APPLICATION_ID,
SUBT.CONTENT_CODE,
SUBT.SEQUENCE_NUM,
SUBT.LANGUAGE
from BNE_CONTENT_COLS_TL SUBB, BNE_CONTENT_COLS_TL SUBT
where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
and SUBB.CONTENT_CODE = SUBT.CONTENT_CODE
and SUBB.SEQUENCE_NUM = SUBT.SEQUENCE_NUM
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_NAME <> SUBT.USER_NAME))
and t.application_id=274 and t.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
insert into BNE_CONTENT_COLS_TL (
APPLICATION_ID,
CONTENT_CODE,
SEQUENCE_NUM,
USER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.APPLICATION_ID,
B.CONTENT_CODE,
B.SEQUENCE_NUM,
B.USER_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.LAST_UPDATE_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from BNE_CONTENT_COLS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from BNE_CONTENT_COLS_TL T
where T.APPLICATION_ID = B.APPLICATION_ID
and T.CONTENT_CODE = B.CONTENT_CODE
and T.SEQUENCE_NUM = B.SEQUENCE_NUM
and T.LANGUAGE = L.LANGUAGE_CODE) and b.application_id=274 and b.content_code not in ('FEM_BALANCES_CNT','FEM_DIM_MEMBER_CNT','FEM_HIERARCHY_CNT');
select display_name into l_table_display_name from fem_tables_vl
where table_name = p_table_name;
select interface_table_name into l_interface_table from fem_tables_b
where table_name = p_table_name;
for missing_rec in (select M.interface_column_name interface_column_name
from FEM_WEBADI_TABLE_COLS_MAPS M
where M.INTERFACE_TABLE_NAME = l_interface_table
and M.NULLABLE = 'N'
and trim(interface_column_name) not in ('CAL_PERIOD_END_DATE','CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','STATUS','LEDGER_DISPLAY_CODE','SOURCE_SYSTEM_DISPLAY_CODE',
'DATASET_DISPLAY_CODE')
and not exists (SELECT 1 FROM FEM_TAB_COLUMNS_B T WHERE TABLE_NAME = p_table_name AND T.INTERFACE_COLUMN_NAME = M.INTERFACE_COLUMN_NAME))
loop
l_no_map_columns := missing_rec.interface_column_name || ' ,' || l_no_map_columns;
select 'Y' into l_integrator_exists from dual
where exists(select integrator_code from bne_integrators_b where
integrator_code = l_integrator_code);
update bne_integrators_b
set upload_param_list_app_id = 274,
upload_param_list_code = 'FEM_TABLES_UPL_LIST',
upload_serv_param_list_app_id = 231,
upload_serv_param_list_code = 'UPL_SERV_INTERF_COLS',
import_param_list_app_id = 274,
import_param_list_code = 'FEM_TABLES_IMP_LIST',
uploader_class = 'oracle.apps.bne.integrator.upload.BneUploader',
import_type = 1
where integrator_code = l_integrator_code;
select 'Y' into l_interface_exists from dual
where exists(select interface_code from bne_interfaces_b where
interface_code = l_object_code || '_INTF');
SELECT NVL(MAX(UPLOAD_ORDER), 0) + 1
INTO l_order_seq
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = 274
AND INTEGRATOR_APP_ID = 274
AND INTEGRATOR_CODE = P_TABLE_NAME || '_INTG';
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,
UPLOAD_ORDER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(274,
l_interface_code,
1,
274,
l_integrator_code,
'UPLOAD_FEM_TABLES_INTERFACE',
2,
274,
l_intf_upl_param_list_code,
NULL,
l_user_id,
SYSDATE,
l_user_id,
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)
SELECT 274
, l_interface_code
, T.LANGUAGE
, T.SOURCE_LANG
, SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
T.DISPLAY_NAME ||
SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
FROM FEM_TABLES_TL T, FND_NEW_MESSAGES M, FND_LANGUAGES L
WHERE T.TABLE_NAME = p_table_name
AND M.APPLICATION_ID= 274
AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_INTERFACE'
AND M.LANGUAGE_CODE = T.LANGUAGE
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B');
select display_name into l_table_display_name from fem_tables_vl
where table_name = p_table_name;
P_QUERY => 'select display_name from fem_tables_vl where table_name = $PARAM$.table_name',
P_LANGUAGE => USERENV('LANG'),
P_SOURCE_LANGUAGE => USERENV('LANG'),
P_USER_ID => l_user_id,
P_CONTENT_CODE => l_content_code);
update bne_contents_b
set param_list_app_id = 274,
param_list_code = 'FEM_TABLE_DNLD_LIST'
where content_code = l_content_code;
insert into bne_mappings_b(APPLICATION_ID,
MAPPING_CODE,
OBJECT_VERSION_NUMBER,
INTEGRATOR_APP_ID,
INTEGRATOR_CODE,
REPORTING_FLAG,
REPORTING_INTERFACE_APP_ID,
REPORTING_INTERFACE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
values
(274,l_mapping_code,1,274,l_integrator_code,'N',NULL,NULL,l_user_id,SYSDATE,l_user_id,0,SYSDATE);
insert into bne_mappings_tl(APPLICATION_ID,
MAPPING_CODE,
LANGUAGE,
SOURCE_LANG,
USER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT 274
, l_mapping_code
, T.LANGUAGE
, T.SOURCE_LANG
, SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
T.DISPLAY_NAME ||
SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
, l_user_id
, SYSDATE
, l_user_id
, 0
, SYSDATE
FROM FEM_TABLES_TL T, FND_NEW_MESSAGES M, FND_LANGUAGES L
WHERE T.TABLE_NAME = p_table_name
AND M.APPLICATION_ID= 274
AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_MAPPING'
AND M.LANGUAGE_CODE = T.LANGUAGE
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B');
insert into bne_mapping_lines
(APPLICATION_ID,
MAPPING_CODE,
SEQUENCE_NUM,
CONTENT_APP_ID,
CONTENT_CODE,
CONTENT_SEQ_NUM,
INTERFACE_APP_ID,
INTERFACE_CODE,
INTERFACE_SEQ_NUM,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
DECODE_FLAG)
values
(274,l_mapping_code,1,274,l_content_code,1,274,l_interface_code,1,1,2,SYSDATE,2,0,SYSDATE,'N');
SELECT 'Y'
INTO l_layout_exists
FROM BNE_LAYOUTS_B
WHERE APPLICATION_ID = 274
AND LAYOUT_CODE = l_layout_code;
INSERT INTO BNE_LAYOUTS_B
( APPLICATION_ID
, LAYOUT_CODE
, 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
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATE_DOC_LIST_APP_ID
, CREATE_DOC_LIST_CODE
)
VALUES
( 274
, l_layout_code
, 1
, 231
, 'DEFAULT'
, 274
, l_integrator_code
, NULL
, 'BNE_PAGE'
, 'N'
, NULL
, NULL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, NULL
, NULL
);
INSERT INTO BNE_LAYOUTS_TL
( APPLICATION_ID
, LAYOUT_CODE
, USER_NAME
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
, LANGUAGE
, SOURCE_LANG
)
SELECT 274
, l_layout_code
, SUBSTR(M.MESSAGE_TEXT,0, INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')-2) ||
T.DISPLAY_NAME ||
SUBSTR(M.MESSAGE_TEXT,INSTR(M.MESSAGE_TEXT, 'TABLE_NAME')+10)
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, T.LANGUAGE
, T.SOURCE_LANG
FROM FEM_TABLES_TL T,FND_NEW_MESSAGES M,FND_LANGUAGES L
WHERE T.TABLE_NAME = p_table_name
AND M.APPLICATION_ID= 274
AND M.MESSAGE_NAME = 'FEM_ADI_TABLES_LAYOUT'
AND M.LANGUAGE_CODE = T.LANGUAGE
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B');
SELECT B.BLOCK_ID
INTO l_header_block_id
FROM BNE_LAYOUT_BLOCKS_B B
WHERE B.APPLICATION_ID = 274
AND B.LAYOUT_CODE = l_layout_code
AND B.LAYOUT_ELEMENT = 'HEADER';
INSERT INTO BNE_LAYOUT_BLOCKS_B
( APPLICATION_ID
, LAYOUT_CODE
, BLOCK_ID
, OBJECT_VERSION_NUMBER
, 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
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
) VALUES
( 274
, l_layout_code
, l_header_block_id
, 1
, NULL
, 'HEADER'
, 'BNE_HEADER'
, NULL
, 'BNE_HEADER_ROW'
, NULL
, NULL
, NULL
, 'Y'
, 'BNE_HEADER_HEADER'
, NULL
, 'Y'
, 'BNE_HEADER_HINT'
, NULL
, 'HORIZONTAL'
, 'COLUMN_FLOW'
, 'Y'
, 1
, 1
, 1
, 10
, 3
, 1
, 2
, 'BNE_LINES_TOTAL'
, NULL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
);
INSERT INTO BNE_LAYOUT_BLOCKS_TL
( APPLICATION_ID
, LAYOUT_CODE
, BLOCK_ID
, USER_NAME
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
, LANGUAGE
, SOURCE_LANG
)
SELECT 274
, l_layout_code
, l_header_block_id
, M.MESSAGE_TEXT
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, L.LANGUAGE_CODE
, USERENV('LANG')
FROM FND_NEW_MESSAGES M,
FND_LANGUAGES L
WHERE M.MESSAGE_NAME = 'LAY_LB_HEADER'
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B');
SELECT B.BLOCK_ID
INTO l_line_block_id
FROM BNE_LAYOUT_BLOCKS_B B
WHERE B.APPLICATION_ID = 274
AND B.LAYOUT_CODE = l_layout_code
AND B.LAYOUT_ELEMENT = 'LINE'
AND B.PARENT_ID =
(
SELECT BLOCK_ID
FROM BNE_LAYOUT_BLOCKS_B
WHERE APPLICATION_ID = B.APPLICATION_ID
AND LAYOUT_CODE = B.LAYOUT_CODE
AND LAYOUT_ELEMENT = 'HEADER'
);
INSERT INTO BNE_LAYOUT_BLOCKS_B
( APPLICATION_ID
, LAYOUT_CODE
, BLOCK_ID
, OBJECT_VERSION_NUMBER
, 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
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
) VALUES
( 274
, l_layout_code
, l_line_block_id
, 1
, l_header_block_id
, 'LINE'
, 'BNE_LINES'
, NULL
, 'BNE_LINES_ROW'
, NULL
, NULL
, NULL
, 'Y'
, 'BNE_LINES_HEADER'
, NULL
, 'Y'
, 'BNE_LINES_HINT'
, NULL
, 'VERTICAL'
, 'TABLE_FLOW'
, 'Y'
, 10
, 1
, 1
, 20
, NULL
, NULL
, NULL
, 'BNE_LINES_TOTAL'
, NULL
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
);
INSERT INTO BNE_LAYOUT_BLOCKS_TL
( APPLICATION_ID
, LAYOUT_CODE
, BLOCK_ID
, USER_NAME
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
, LANGUAGE
, SOURCE_LANG
)
SELECT 274
, l_layout_code
, l_line_block_id
, M.MESSAGE_TEXT
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, L.LANGUAGE_CODE
, USERENV('LANG')
FROM FND_NEW_MESSAGES M,
FND_LANGUAGES L
WHERE M.MESSAGE_NAME = 'LAY_LB_LINE'
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND L.INSTALLED_FLAG IN ('I', 'B');
delete from
bne_layout_cols
where
layout_code = l_layout_code;
select * from bne_interface_cols_b where interface_code = l_interface_code
and application_id = 274 and interface_col_type = 1 and enabled_flag = 'Y'
and display_flag = 'Y'
)
loop
INSERT INTO BNE_LAYOUT_COLS
( APPLICATION_ID
, LAYOUT_CODE
, BLOCK_ID
, OBJECT_VERSION_NUMBER
, 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
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, LAST_UPDATE_DATE
)
VALUES(
274
,l_layout_code
,decode(layout_cols.interface_col_name,'P_TABLE_NAME',1,'P_LEDGER_DISPLAY_CODE',1,'P_CAL_PERIOD',1,'P_DATASET_CODE',1,'P_SOURCE_SYSTEM_DISPLAY_CODE',1,2)
,1
,274
,l_interface_code
,layout_cols.sequence_num
,l_layout_seq * 10
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,l_user_id
,SYSDATE
,l_user_id
,l_login_id
,SYSDATE);
update bne_layout_cols b
set interface_seq_num = (select sequence_num from bne_interface_cols_b where interface_code = l_interface_code and display_order = b.sequence_num)
where layout_code = l_layout_code
and block_id =2;
select max(sequence_num) into l_sequence_num from
bne_interface_cols_b where interface_code = l_interface_code;
select bne_interface_col_name from fem_webadi_table_cols_maps
where interface_table_name = (select interface_table_name from fem_tables_b where table_name = p_table_name)
and bne_interface_col_name not in(select interface_col_name from
bne_interface_cols_b where interface_code = l_interface_code)
)
loop
INSERT INTO BNE_INTERFACE_COLS_B (
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,
APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
LOV_TYPE,
OFFLINE_LOV_ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VARIABLE_DATA_TYPE_CLASS
)
VALUES
( 1, -- interface col type
extra_params.bne_interface_col_name, -- interface col name
'N', -- enabled flag
'N', -- initially 'N' required flag
'N', -- display flag 'Y' initially
'N', -- read only flag
'N', -- not null flag
'N', -- summary flag
'N', -- mapping enabled flag
2, -- Data Type Varchar for all
NULL, -- field size
NULL, -- default type
NULL, -- default value
NULL, -- segment number
NULL, -- group name
NULL, -- oa flex code
NULL, -- oa concat flex
NULL, -- validation type
NULL, -- val id col
NULL, -- val mean col
NULL, -- val desc col
NULL, -- val object name
NULL, -- vsl where clause
NULL, -- val component app id
NULL, -- val component code
NULL, -- oa flex num
NULL, -- oa flex app id
NULL, -- display order
TO_NUMBER(SUBSTR(extra_params.bne_interface_col_name, 8)) + 5, -- upload param list item number
NULL, -- expanded sql query
274, -- application id
l_interface_code, -- interface code
1, -- object version number
l_sequence_num + 1, -- sequence number (max(seq) +1)
NULL, -- lov type
NULL, -- offline enabled lov flag
SYSDATE, -- creation date
l_user_id, -- created by
SYSDATE, -- last updated by
l_user_id, -- last updated by
l_login_id, -- last update login
NULL -- variable data type class
);
select max(sequence_num) into l_sequence_num from
bne_interface_cols_b where interface_code = l_interface_code;
select (max(UPLOAD_PARAM_LIST_ITEM_NUM) - 5) into l_list_item_num from bne_interface_cols_b
where interface_code = l_interface_code;
INSERT INTO BNE_INTERFACE_COLS_B (
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,
APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
LOV_TYPE,
OFFLINE_LOV_ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VARIABLE_DATA_TYPE_CLASS
)
VALUES
( 1, -- interface col type
'P_PARAM' || l_list_item_num , -- interface col name
'N', -- enabled flag
'N', -- initially 'N' required flag
'N', -- display flag 'Y' initially
'N', -- read only flag
'N', -- not null flag
'N', -- summary flag
'N', -- mapping enabled flag
2, -- Data Type Varchar for all
NULL, -- field size
NULL, -- default type
NULL, -- default value
NULL, -- segment number
NULL, -- group name
NULL, -- oa flex code
NULL, -- oa concat flex
NULL, -- validation type
NULL, -- val id col
NULL, -- val mean col
NULL, -- val desc col
NULL, -- val object name
NULL, -- vsl where clause
NULL, -- val component app id
NULL, -- val component code
NULL, -- oa flex num
NULL, -- oa flex app id
NULL, -- display order
l_list_item_num + 5, -- upload param list item number
NULL, -- expanded sql query
274, -- application id
l_interface_code, -- interface code
1, -- object version number
l_sequence_num, -- sequence number (max(seq) +1)
NULL, -- lov type
NULL, -- offline enabled lov flag
SYSDATE, -- creation date
l_user_id, -- created by
SYSDATE, -- last updated by
l_user_id, -- last updated by
l_login_id, -- last update login
NULL -- variable data type class
);
select interface_table_name into l_interface_table_name from
fem_tables_b where table_name = l_table_name;
select M.bne_interface_col_name bne_interface_col_name,M.interface_column_name interface_column_name,M.data_type data_type ,M.nullable nullable,
T.fem_data_type_code fem_data_type,T.column_name column_name
from
FEM_WEBADI_TABLE_COLS_MAPS M,FEM_TAB_COLUMNS_B T
where T.TABLE_NAME = l_table_name
and M.INTERFACE_TABLE_NAME = l_interface_table_name
and T.INTERFACE_COLUMN_NAME = M.INTERFACE_COLUMN_NAME
)
loop -- Starting the loop to insert entries one by one
INSERT INTO BNE_INTERFACE_COLS_B (
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,
APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
LOV_TYPE,
OFFLINE_LOV_ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VARIABLE_DATA_TYPE_CLASS
)
VALUES
( 1, -- interface col type
new_rec.bne_interface_col_name, -- interface col name
'Y', -- enabled flag
decode(new_rec.nullable,'Y','N','N','Y','N'), -- initially 'N' required flag
'Y', -- display flag 'Y' initially
'N', -- read only flag
decode(new_rec.nullable,'Y','N','N','Y','N'), -- not null flag
'N', -- summary flag
'N',-- mapping enabled flag
2, -- decode(new_rec.data_type,'DATE',3,2), -- Data Type Varchar for all
2000, -- field size
NULL, -- default type
NULL, -- default value
NULL, -- decode(new_rec.nullable,'N',l_segment_num,NULL), -- segment number
NULL, -- decode(new_rec.nullable,'N','TABLE_GROUP_VALIDATOR',NULL), -- group name
NULL, -- oa flex code
NULL, -- oa concat flex
DECODE(new_rec.fem_data_type, 'DIMENSION', 'JAVA', NULL), -- validation type
NULL, -- val id col
NULL, -- val mean col
NULL, -- val desc col
DECODE(new_rec.fem_data_type, 'DIMENSION', 'oracle.apps.fem.integrator.tables.validators.FemMemberValidator', NULL), -- val object name
NULL, -- vsl where clause
DECODE(new_rec.fem_data_type, 'DIMENSION', 274, NULL), -- val component app id
DECODE(new_rec.fem_data_type, 'DIMENSION', l_component_code, NULL), -- val component code
NULL, -- oa flex num
NULL, -- oa flex app id
l_sequence_num*10, -- display order
TO_NUMBER(SUBSTR(new_rec.bne_interface_col_name, 8)) + 5, -- upload param list item number
NULL, -- expanded sql query
274, -- application id
p_interface_code, -- interface code
1, -- object version number
l_sequence_num, -- sequence number
DECODE(new_rec.fem_data_type, 'DIMENSION', 'STANDARD', NULL), -- lov type
DECODE(new_rec.fem_data_type, 'DIMENSION', 'N', NULL), -- offline enabled lov flag
SYSDATE, -- creation date
l_user_id, -- created by
SYSDATE, -- last updated by
l_user_id, -- last updated by
l_login_id, -- last update login
DECODE(new_rec.DATA_TYPE, 'DATE',
'oracle.apps.fem.integrator.dimension.validators.FemAttributeDateTypeValidator',
DECODE(new_rec.DATA_TYPE, 'NUMBER',NULL,
--'oracle.apps.fem.integrator.dimension.validators.FemAttributeNumericTypeValidator',
NULL)) -- variable data type class
);
INSERT INTO BNE_INTERFACE_COLS_TL
(
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
USER_HINT,
PROMPT_LEFT,
USER_HELP_TEXT,
PROMPT_ABOVE,
INTERFACE_CODE,
SEQUENCE_NUM,
APPLICATION_ID,
LANGUAGE,
SOURCE_LANG
)
SELECT l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, M.MESSAGE_TEXT
, TL.DISPLAY_NAME
, NULL
, TL.DISPLAY_NAME
, p_interface_code
, l_sequence_num
, 274
, L.LANGUAGE_CODE
, TL.SOURCE_LANG
FROM FEM_TAB_COLUMNS_B TB
, FEM_TAB_COLUMNS_TL TL
, FND_NEW_MESSAGES M
, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND TB.COLUMN_NAME = new_rec.column_name
AND TB.TABLE_NAME = l_table_name -- substr(p_interface_code,0,instr(p_interface_code,'_INTF')-1)
AND TL.COLUMN_NAME = TB.COLUMN_NAME
AND TL.TABLE_NAME = TB.TABLE_NAME
AND TB.FEM_DATA_TYPE_CODE = 'DIMENSION'
AND TL.LANGUAGE (+) = L.LANGUAGE_CODE
AND M.MESSAGE_NAME (+) =
DECODE(new_rec.fem_data_type,'DIMENSION',DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_LOV_REQ', 'FEM_ADI_USER_HINT_LOV'),NULL)
AND M.LANGUAGE_CODE (+) = L.LANGUAGE_CODE;
INSERT INTO BNE_INTERFACE_COLS_TL
(
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
USER_HINT,
PROMPT_LEFT,
USER_HELP_TEXT,
PROMPT_ABOVE,
INTERFACE_CODE,
SEQUENCE_NUM,
APPLICATION_ID,
LANGUAGE,
SOURCE_LANG
)
SELECT l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, M.MESSAGE_TEXT
, TL.DISPLAY_NAME
, NULL
, TL.DISPLAY_NAME
, p_interface_code
, l_sequence_num
, 274
, L.LANGUAGE_CODE
, TL.SOURCE_LANG
FROM FEM_TAB_COLUMNS_B TB
, FEM_TAB_COLUMNS_TL TL
, FND_NEW_MESSAGES M
, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND TB.COLUMN_NAME = new_rec.column_name
AND TB.TABLE_NAME = l_table_name -- substr(p_interface_code,0,instr(p_interface_code,'_INTF')-1)
AND TL.COLUMN_NAME = TB.COLUMN_NAME
AND TL.TABLE_NAME = TB.TABLE_NAME
AND TB.FEM_DATA_TYPE_CODE <> 'DIMENSION'
AND TL.LANGUAGE (+) = L.LANGUAGE_CODE
AND M.MESSAGE_NAME (+)=
DECODE(new_rec.DATA_TYPE, 'VARCHAR2',
DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_TEXT_REQ', 'FEM_ADI_USER_HINT_TEXT'),
DECODE(new_rec.DATA_TYPE, 'NUMBER',
DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_NUMBER_REQ', 'FEM_ADI_USER_HINT_NUMBER'),
DECODE(new_rec.DATA_TYPE, 'DATE',
DECODE(new_rec.nullable, 'N', 'FEM_ADI_USER_HINT_DATE_REQ', 'FEM_ADI_USER_HINT_DATE'), NULL)))
AND M.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
update bne_interface_cols_b
set enabled_flag = 'N',
display_flag = 'N',
required_flag = 'N',
not_null_flag = 'N'
where
interface_code = p_interface_code and
interface_col_name in
(
select interface_col_name from bne_interface_cols_b tb,fem_webadi_table_cols_maps m, fem_tab_columns_b b where
tb.interface_col_name = m.bne_interface_col_name and
m.interface_table_name = l_interface_table_name and
tb.interface_code = p_interface_code and
m.interface_column_name = b.interface_column_name and
b.table_name = l_table_name and
(b.dimension_id in (select dimension_id from fem_dimensions_b where dimension_varchar_label in
('LEDGER','CAL_PERIOD','DATASET','SOURCE_SYSTEM'))
or not exists( select interface_col_name from bne_interface_cols_vl tl
where tl.interface_col_name = tb.interface_col_name and interface_code = p_interface_code) or
m.interface_column_name in ('CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_NUMBER','CAL_PERIOD_END_DATE'))
);
update bne_interface_cols_b
set display_order = null
where interface_code = p_interface_code and sequence_num > 6;
select interface_col_name from bne_interface_cols_vl
where interface_code = p_interface_code
and display_flag = 'Y' and enabled_flag = 'Y'
and sequence_num > 6
order by not_null_flag desc,upper(prompt_above)
)
loop -- Starting the loop to update entries one by one
update bne_interface_cols_b
set display_order = l_display_order * 10
where interface_code = p_interface_code
and interface_col_name = bne_cols.interface_col_name;
delete from bne_interface_cols_b
where interface_code = p_interface_code;
delete from bne_interface_cols_tl
where interface_code = p_interface_code;
select display_name into l_table_display_name from fem_tables_vl
where table_name = l_table_name;
l_interface_cols_tbl(1).DEFAULT_VALUE := 'select display_name from fem_tables_vl where table_name = $PARAM$.table_name';
INSERT INTO BNE_INTERFACE_COLS_B (
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,
APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
LOV_TYPE,
OFFLINE_LOV_ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VARIABLE_DATA_TYPE_CLASS
)
VALUES
( l_interface_cols_tbl(i).INTERFACE_COL_TYPE,
l_interface_cols_tbl(i).INTERFACE_COL_NAME,
'Y',
decode(l_interface_cols_tbl(i).INTERFACE_COL_TYPE,2,'N','Y'),
l_interface_cols_tbl(i).DISPLAY_FLAG,
l_interface_cols_tbl(i).READ_ONLY_FLAG,
decode(l_interface_cols_tbl(i).INTERFACE_COL_TYPE,2,'N','Y'),
'N',
l_interface_cols_tbl(i).MAPPING_ENABLED_FLAG,
l_interface_cols_tbl(i).DATA_TYPE,
l_interface_cols_tbl(i).FIELD_SIZE,
l_interface_cols_tbl(i).DEFAULT_TYPE,
l_interface_cols_tbl(i).DEFAULT_VALUE,
l_interface_cols_tbl(i).SEGMENT_VALUE,
l_interface_cols_tbl(i).GROUP_NAME,
NULL,
NULL,
l_interface_cols_tbl(i).VAL_TYPE,
l_interface_cols_tbl(i).VAL_ID_COL,
l_interface_cols_tbl(i).VAL_MEAN_COL,
l_interface_cols_tbl(i).VAL_DESC_COL,
l_interface_cols_tbl(i).VAL_OBJ_NAME,
l_interface_cols_tbl(i).VAL_ADDL_W_C,
l_interface_cols_tbl(i).VAL_COMPONENT_APP_ID,
l_interface_cols_tbl(i).VAL_COMPONENT_CODE,
NULL,
NULL,
l_interface_cols_tbl(i).DISPLAY_ORDER,
l_interface_cols_tbl(i).UPLOAD_PARAM_LIST_ITEM_NUM,
NULL,
274,
p_interface_code,
1,
l_interface_cols_tbl(i).SEQUENCE_NUM,
l_interface_cols_tbl(i).LOV_TYPE,
l_interface_cols_tbl(i).OFFLINE_LOV_ENABLED_FLAG,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
NULL
);
INSERT INTO BNE_INTERFACE_COLS_TL (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
USER_HINT,
PROMPT_LEFT,
USER_HELP_TEXT,
PROMPT_ABOVE,
INTERFACE_CODE,
SEQUENCE_NUM,
APPLICATION_ID,
LANGUAGE,
SOURCE_LANG
)
SELECT l_user_id
, SYSDATE
, l_user_id
, l_login_id
, SYSDATE
, M2.MESSAGE_TEXT
, M1.MESSAGE_TEXT
, NULL
, M1.MESSAGE_TEXT
, p_interface_code
, l_interface_cols_tbl(i).SEQUENCE_NUM
, 274
, L.LANGUAGE_CODE
, USERENV('LANG')
FROM FND_NEW_MESSAGES M1,
FND_NEW_MESSAGES M2,
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND M1.MESSAGE_NAME (+)= l_interface_cols_tbl(i).FND_MESSAGE_NAME
AND M1.LANGUAGE_CODE (+)= L.LANGUAGE_CODE
AND M2.MESSAGE_NAME (+)= l_interface_cols_tbl(i).USER_HINT_FND_MESSAGE_NAME
AND M2.LANGUAGE_CODE (+)= L.LANGUAGE_CODE;
select column_name,data_type,nullable from dba_tab_columns
where owner = (select table_owner from user_synonyms where synonym_name = p_table_name)
and table_name = p_table_name
order by nullable asc,data_type desc,column_name asc;
select 'Y' into l_map_exists from dual
where exists(select interface_table_name from FEM_WEBADI_TABLE_COLS_MAPS where
interface_table_name = p_table_name);
delete from FEM_WEBADI_TABLE_COLS_MAPS
where interface_table_name = p_table_name;
insert into FEM_WEBADI_TABLE_COLS_MAPS(BNE_INTERFACE_COL_NAME,INTERFACE_TABLE_NAME,INTERFACE_COLUMN_NAME,DATA_TYPE,NULLABLE)
values(l_bne_interface_col_name,p_table_name,l_interface_column_name,l_data_type,l_nullable);
l_insert_query long;
select date_assign_value into l_cal_period_end_date from fem_cal_periods_attr
where cal_period_id = P_CAL_PERIOD
and attribute_id = (
select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
where a.attribute_id = v.attribute_id
and a.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
and v.default_version_flag = 'Y' );
select number_assign_value into l_cal_period_number from FEM_CAL_PERIODS_ATTR
where cal_period_id = P_CAL_PERIOD
and attribute_id = (
select a.attribute_id from fem_dim_attributes_b a,fem_dim_attr_versions_b v
where a.attribute_id = v.attribute_id
and a.attribute_varchar_label = 'GL_PERIOD_NUM'
and v.default_version_flag = 'Y' );
select dimension_group_display_code into l_cal_grp_display_code from FEM_DIMENSION_GRPS_B
where dimension_group_id = (select dimension_group_id from fem_cal_periods_b where
cal_period_id = P_CAL_PERIOD);
select dataset_display_code into l_dataset_display_code from fem_datasets_b
where dataset_code = P_DATASET_DISPLAY_CODE;
select table_name into l_table_name from fem_tables_vl
where display_name = p_table_name;
select interface_table_name,table_name || '_INTF' into l_interface_table_name,l_interface_code from
fem_tables_vl where display_name = p_table_name;
select interface_code into l_interface_code from bne_interfaces_vl where instr(user_name,p_table_name) <>0;
select bne_interface_col_name bne_interface_col_name,interface_column_name,data_type from
fem_webadi_table_cols_maps
where
interface_table_name = l_interface_table_name
and bne_interface_col_name in
(select interface_col_name from bne_interface_cols_b where
interface_code = l_interface_code and enabled_flag = 'Y')
)
loop
l_query_columns := l_query_columns || ',' || columns_values.interface_column_name;
select to_number(l_column_value) into l_valid_number from dual;
l_insert_query := 'INSERT INTO ' || l_interface_table_name || l_query_columns || ' VALUES ' || l_query_values;
execute immediate l_insert_query;
select PROMPT_ABOVE into l_column_name from bne_interface_cols_vl where
interface_code = l_interface_code and interface_col_name = l_column_name;
select interface_table_name into l_intf_table_name from fem_tables_b
where table_name = p_table_name;
select count(1) into l_total_columns from dba_tab_columns
where owner = (select table_owner from user_synonyms where synonym_name = l_intf_table_name)
and table_name = l_intf_table_name;
PROCEDURE DELETE_METADATA
IS
begin
delete from bne_security_rules where application_id = 274
and security_code not in ('FEM_BALANCES_SECURITY_RULE');
delete from bne_secured_objects where application_id = 274
and object_code not in ('FEM_BALANCES_INTG');
delete from bne_layouts_b where application_id = 274
and layout_code not in ('FEM_BALANCES_LAYOUT');
delete from bne_layouts_tl where application_id = 274
and layout_code not in ('FEM_BALANCES_LAYOUT');
delete from bne_layout_cols where application_id = 274
and layout_code not in ('FEM_BALANCES_LAYOUT');
delete from bne_layout_blocks_tl where application_id = 274
and layout_code not in ('FEM_BALANCES_LAYOUT');
delete from bne_layout_blocks_b where application_id = 274
and layout_code not in ('FEM_BALANCES_LAYOUT');
delete from bne_content_cols_tl where application_id = 274
and content_code not in ('FEM_BALANCES_CNT');
delete from bne_content_cols_b where application_id = 274
and content_code not in ('FEM_BALANCES_CNT');
delete from bne_contents_tl where application_id = 274
and content_code not in ('FEM_BALANCES_CNT');
delete from bne_contents_b where application_id = 274
and content_code not in ('FEM_BALANCES_CNT');
delete from bne_mapping_lines where application_id = 274
and mapping_code not in ('FEM_BALANCES_MAP');
delete from bne_mappings_tl where application_id = 274
and mapping_code not in ('FEM_BALANCES_MAP');
delete from bne_mappings_b where application_id = 274
and mapping_code not in ('FEM_BALANCES_MAP');
delete from bne_interfaces_b where application_id = 274
and interface_code not in ('FEM_BALANCES_INTF');
delete from bne_interfaces_tl where application_id = 274
and interface_code not in ('FEM_BALANCES_INTF');
delete from bne_interface_cols_b where application_id = 274
and interface_code not in ('FEM_BALANCES_INTF');
delete from bne_interface_cols_tl where application_id = 274
and interface_code not in ('FEM_BALANCES_INTF');
delete from bne_integrators_tl where application_id = 274
and integrator_code not in ('FEM_BALANCES_INTG');
delete from bne_integrators_b where application_id = 274
and integrator_code not in ('FEM_BALANCES_INTG');
delete from bne_stored_sql where application_id = 274
and content_code not in ('FEM_BALANCES_CNT');
END DELETE_METADATA;