/*
Steps to create the custom integrator.
Step 1. Using bne_integrator_utils.CREATE_INTEGRATOR package create the custom integrator
Step 2. Using bne_integrator_utils.CREATE_INTERFACE_FOR_API package create the interface for the created integrator.
Step 3. Using bne_integrator_utils.CREATE_DEFAULT_LAYOUT package create the default layout for the created integrator with the interface.
Step 4. Adding POPList for the required columns.
Step 5. Change the excel column prompts, by default the column headers will come as Database column name. (bne_interface_cols_tl )
*/
DECLARE
FUNCTION is_exists_map(p_application_id IN NUMBER,
p_content_code IN VARCHAR2) RETURN BOOLEAN IS
CURSOR csr IS
SELECT 1
FROM bne_mapping_lines
WHERE application_id = p_application_id
AND content_code = p_content_code
l_dummy NUMBER
retval BOOLEAN
BEGIN
OPEN csr
FETCH csr
INTO l_dummy
IF csr%NOTFOUND THEN
retval := FALSE
ELSE
retval := TRUE
END IF
CLOSE csr
RETURN retval
END;
--BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP
PROCEDURE create_map_line(p_application_id IN NUMBER,
p_interface_code IN VARCHAR2,
p_content_code IN VARCHAR2,
p_mapping_code IN VARCHAR2) IS
CURSOR mapping_cols_c(cp_application_id IN NUMBER, cp_content_code IN VARCHAR2, cp_interface_code IN VARCHAR2) IS
SELECT cc.application_id content_app_id,
cc.content_code,
cc.sequence_num content_seq_num,
ic.application_id interface_app_id,
ic.interface_code,
ic.sequence_num interface_seq_num
FROM bne_content_cols_b cc, bne_interface_cols_b ic
WHERE cc.application_id = ic.application_id
AND cc.application_id = cp_application_id
AND (cc.col_name = ic.interface_col_name OR
cc.col_name = substr(ic.interface_col_name, 3) OR
cc.sequence_num = ic.sequence_num --add by sky.chan
)
AND cc.content_code = cp_content_code
AND ic.interface_code = cp_interface_code
ORDER BY cc.sequence_num ASC
vn_sequence NUMBER
BEGIN
IF NOT is_exists_map(p_application_id, p_content_code) THEN
vn_sequence := 0
FOR mapping_cols_rec IN mapping_cols_c(p_application_id,
p_content_code,
p_interface_code) LOOP
vn_sequence := vn_sequence + 1
-- Create the Mapping records in the BNE_MAPPING_LINES table
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_date)
VALUES
(p_application_id,
p_mapping_code,
vn_sequence,
mapping_cols_rec.content_app_id,
mapping_cols_rec.content_code,
mapping_cols_rec.content_seq_num,
mapping_cols_rec.interface_app_id,
mapping_cols_rec.interface_code,
mapping_cols_rec.interface_seq_num,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE)
EXIT WHEN mapping_cols_c%NOTFOUND
END LOOP
END IF
END;
PROCEDURE create_bne_integrator(p_application_id IN VARCHAR2,
p_object_code IN VARCHAR2,
p_integrator_name IN VARCHAR2,
p_package_name IN VARCHAR2,
p_procedure_name IN VARCHAR2) IS
l_integrator_name VARCHAR2(200) := ''
l_integrator_code VARCHAR2(100)
l_param_list_code VARCHAR2(100)
l_interface_code VARCHAR2(100)
l_mapping_code VARCHAR2(100)
l_layout_code VARCHAR2(100)
l_content_code VARCHAR2(100)
l_parameters VARCHAR2(500) := 'bne:page=BneCreateDoc'
chr(38) 'bne:'
l_rowid VARCHAR2(100)
l_function_id NUMBER
BEGIN
--todo 1 create integrator
bne_integrator_utils.create_integrator(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_user_name => p_integrator_name,
p_language => userenv('LANG'),
p_source_language => userenv('LANG'),
p_user_id => fnd_global.user_id,
p_integrator_code => l_integrator_code)
--todo 2 create content
----todo 2.1 create empty content
bne_content_utils.create_content(p_application_id,
p_object_code,
l_integrator_code,
'None',
userenv('LANG'),
userenv('LANG'),
'',
fnd_global.user_id,
l_content_code)
----todo 2.2 create text content
bne_content_utils.create_content_text(p_application_id,
p_object_code '_TXT',
l_integrator_code,
'Text',
2,
'P',
userenv('LANG'),
userenv('LANG'),
fnd_global.user_id,
l_content_code)
--todo 3 create interface & map
bne_integrator_utils.create_api_interface_and_map(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_code => l_integrator_code,
p_api_package_name => p_package_name,
p_api_procedure_name => p_procedure_name,
p_interface_user_name => p_integrator_name,
p_content_code => l_content_code,
p_view_name => '',
p_param_list_name => p_object_code
'LIST',
p_api_type => 'PROCEDURE',
p_api_return_type => '',
p_upload_type => 2, --1= upload to Table. 2 = Upload to PL/SQL API
p_language => userenv('LANG'),
p_source_lang => userenv('LANG'),
p_user_id => fnd_global.user_id,
p_param_list_code => l_param_list_code,
p_interface_code => l_interface_code,
p_mapping_code => l_mapping_code)
--todo 4 create layout
bne_integrator_utils.create_default_layout(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_code => l_integrator_code,
p_interface_code => l_interface_code,
p_user_id => fnd_global.user_id,
p_force => FALSE,
p_all_columns => TRUE,
p_layout_code => l_layout_code)
--todo 5 change default block to line
UPDATE bne_layout_blocks_b
SET block_id = 1, sequence_num = 1
WHERE layout_code = l_layout_code
UPDATE bne_layout_blocks_tl
SET block_id = 1
WHERE layout_code = l_layout_code
--todo 6 create layout column
FOR i IN (SELECT *
FROM bne_interface_cols_tl
WHERE interface_code = l_interface_code) LOOP
bne_layout_cols_pkg.insert_row(l_rowid,
p_application_id, --APPLICATION_ID
l_layout_code, --LAYOUT_CODE
1, --BLOCK_ID
(i.sequence_num * 10), --SEQUENCE_NUM
1, --OBJECT_VERSION_NUMBER
p_application_id, --INTERFACE_APP_ID
l_interface_code, --INTERFACE_CODE
i.sequence_num, --INTERFACE_SEQ_NUM
NULL, --STYLE_CLASS
NULL, --HINT_STYLE
NULL, --HINT_STYLE_CLASS
NULL, --PROMPT_STYLE
NULL, --PROMPT_STYLE_CLASS
NULL, --DEFAULT_TYPE
NULL, --DEFAULT_VALUE
NULL, --STYLE
SYSDATE, --CREATION_DATE
fnd_global.user_id, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
fnd_global.user_id, --LAST_UPDATED_BY
fnd_global.user_id, --LAST_UPDATE_LOGIN
NULL, --DISPLAY_WIDTH
'N' --READ_ONLY_FLAG
)
END LOOP
--todo 7 create map line .I think there is a bug at: BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP. because they cannot create the map line.
create_map_line(p_application_id => p_application_id,
p_interface_code => l_interface_code,
p_content_code => l_content_code,
p_mapping_code => l_mapping_code)
dbms_output.put_line('l_integrator_code:' l_integrator_code)
dbms_output.put_line('l_param_list_code' l_param_list_code)
dbms_output.put_line('l_interface_code:' l_interface_code)
dbms_output.put_line('l_mapping_code:' l_mapping_code)
dbms_output.put_line('l_layout_code:' l_layout_code)
dbms_output.put_line('l_content_code:' l_content_code)
-- todo 8 create function
---begin create function
l_parameters := l_parameters chr(38) 'bne:integrator='
p_application_id ':' l_integrator_code chr(38)
'bne:noreview=true'
dbms_output.put_line('parameters:' l_parameters);
SELECT fnd_form_functions_s.NEXTVAL INTO l_function_id FROM sys.dual;
fnd_form_functions_pkg.insert_row(x_rowid => l_rowid,
x_function_id => l_function_id,
x_web_host_name => '',
x_web_agent_name => '',
x_web_html_call => 'BneApplicationService',
x_web_encrypt_parameters => '',
x_web_secured => '',
x_object_id => '',
x_region_application_id => '',
x_region_code => '',
x_web_icon => '',
x_function_name => l_integrator_code,
x_application_id => '',
x_form_id => '',
x_parameters => l_parameters,
x_type => 'SERVLET',
x_user_function_name => p_integrator_name,
x_description => '',
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_maintenance_mode_support => '',
x_context_dependence => '',
x_jrad_ref_path => '');
dbms_output.put_line('function_id:' l_function_id);
---end create function
--todo 9 add other language
/*bne_integrators_pkg.add_language
bne_interface_cols_pkg.add_language
bne_content_cols_pkg.add_language
bne_contents_pkg.add_language
bne_integrators_pkg.add_language
bne_layouts_pkg.add_language
bne_layout_blocks_pkg.add_language
bne_mappings_pkg.add_language
bne_mapping_lines_pkg.add_language*/
-- todo 10 update col name OPTIONAL
UPDATE bne_interface_cols_tl
SET prompt_left = sky_test_webadi.get_col_name(prompt_left),
prompt_above = sky_test_webadi.get_col_name(prompt_above)
WHERE application_id = p_application_id
AND interface_code = l_interface_code;
END;
BEGIN
create_bne_integrator(p_application_id => 200,
p_object_code => 'SKY_TEST_WEBADI1',
p_integrator_name => 'SKY_TEST_WEBADI1',
p_package_name => 'SKY_TEST_WEBADI',
p_procedure_name => 'CREATE_TEST');
END;
Steps to create the custom integrator.
Step 1. Using bne_integrator_utils.CREATE_INTEGRATOR package create the custom integrator
Step 2. Using bne_integrator_utils.CREATE_INTERFACE_FOR_API package create the interface for the created integrator.
Step 3. Using bne_integrator_utils.CREATE_DEFAULT_LAYOUT package create the default layout for the created integrator with the interface.
Step 4. Adding POPList for the required columns.
Step 5. Change the excel column prompts, by default the column headers will come as Database column name. (bne_interface_cols_tl )
*/
DECLARE
FUNCTION is_exists_map(p_application_id IN NUMBER,
p_content_code IN VARCHAR2) RETURN BOOLEAN IS
CURSOR csr IS
SELECT 1
FROM bne_mapping_lines
WHERE application_id = p_application_id
AND content_code = p_content_code
l_dummy NUMBER
retval BOOLEAN
BEGIN
OPEN csr
FETCH csr
INTO l_dummy
IF csr%NOTFOUND THEN
retval := FALSE
ELSE
retval := TRUE
END IF
CLOSE csr
RETURN retval
END;
--BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP
PROCEDURE create_map_line(p_application_id IN NUMBER,
p_interface_code IN VARCHAR2,
p_content_code IN VARCHAR2,
p_mapping_code IN VARCHAR2) IS
CURSOR mapping_cols_c(cp_application_id IN NUMBER, cp_content_code IN VARCHAR2, cp_interface_code IN VARCHAR2) IS
SELECT cc.application_id content_app_id,
cc.content_code,
cc.sequence_num content_seq_num,
ic.application_id interface_app_id,
ic.interface_code,
ic.sequence_num interface_seq_num
FROM bne_content_cols_b cc, bne_interface_cols_b ic
WHERE cc.application_id = ic.application_id
AND cc.application_id = cp_application_id
AND (cc.col_name = ic.interface_col_name OR
cc.col_name = substr(ic.interface_col_name, 3) OR
cc.sequence_num = ic.sequence_num --add by sky.chan
)
AND cc.content_code = cp_content_code
AND ic.interface_code = cp_interface_code
ORDER BY cc.sequence_num ASC
vn_sequence NUMBER
BEGIN
IF NOT is_exists_map(p_application_id, p_content_code) THEN
vn_sequence := 0
FOR mapping_cols_rec IN mapping_cols_c(p_application_id,
p_content_code,
p_interface_code) LOOP
vn_sequence := vn_sequence + 1
-- Create the Mapping records in the BNE_MAPPING_LINES table
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_date)
VALUES
(p_application_id,
p_mapping_code,
vn_sequence,
mapping_cols_rec.content_app_id,
mapping_cols_rec.content_code,
mapping_cols_rec.content_seq_num,
mapping_cols_rec.interface_app_id,
mapping_cols_rec.interface_code,
mapping_cols_rec.interface_seq_num,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE)
EXIT WHEN mapping_cols_c%NOTFOUND
END LOOP
END IF
END;
PROCEDURE create_bne_integrator(p_application_id IN VARCHAR2,
p_object_code IN VARCHAR2,
p_integrator_name IN VARCHAR2,
p_package_name IN VARCHAR2,
p_procedure_name IN VARCHAR2) IS
l_integrator_name VARCHAR2(200) := ''
l_integrator_code VARCHAR2(100)
l_param_list_code VARCHAR2(100)
l_interface_code VARCHAR2(100)
l_mapping_code VARCHAR2(100)
l_layout_code VARCHAR2(100)
l_content_code VARCHAR2(100)
l_parameters VARCHAR2(500) := 'bne:page=BneCreateDoc'
chr(38) 'bne:'
l_rowid VARCHAR2(100)
l_function_id NUMBER
BEGIN
--todo 1 create integrator
bne_integrator_utils.create_integrator(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_user_name => p_integrator_name,
p_language => userenv('LANG'),
p_source_language => userenv('LANG'),
p_user_id => fnd_global.user_id,
p_integrator_code => l_integrator_code)
--todo 2 create content
----todo 2.1 create empty content
bne_content_utils.create_content(p_application_id,
p_object_code,
l_integrator_code,
'None',
userenv('LANG'),
userenv('LANG'),
'',
fnd_global.user_id,
l_content_code)
----todo 2.2 create text content
bne_content_utils.create_content_text(p_application_id,
p_object_code '_TXT',
l_integrator_code,
'Text',
2,
'P',
userenv('LANG'),
userenv('LANG'),
fnd_global.user_id,
l_content_code)
--todo 3 create interface & map
bne_integrator_utils.create_api_interface_and_map(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_code => l_integrator_code,
p_api_package_name => p_package_name,
p_api_procedure_name => p_procedure_name,
p_interface_user_name => p_integrator_name,
p_content_code => l_content_code,
p_view_name => '',
p_param_list_name => p_object_code
'LIST',
p_api_type => 'PROCEDURE',
p_api_return_type => '',
p_upload_type => 2, --1= upload to Table. 2 = Upload to PL/SQL API
p_language => userenv('LANG'),
p_source_lang => userenv('LANG'),
p_user_id => fnd_global.user_id,
p_param_list_code => l_param_list_code,
p_interface_code => l_interface_code,
p_mapping_code => l_mapping_code)
--todo 4 create layout
bne_integrator_utils.create_default_layout(p_application_id => p_application_id,
p_object_code => p_object_code,
p_integrator_code => l_integrator_code,
p_interface_code => l_interface_code,
p_user_id => fnd_global.user_id,
p_force => FALSE,
p_all_columns => TRUE,
p_layout_code => l_layout_code)
--todo 5 change default block to line
UPDATE bne_layout_blocks_b
SET block_id = 1, sequence_num = 1
WHERE layout_code = l_layout_code
UPDATE bne_layout_blocks_tl
SET block_id = 1
WHERE layout_code = l_layout_code
--todo 6 create layout column
FOR i IN (SELECT *
FROM bne_interface_cols_tl
WHERE interface_code = l_interface_code) LOOP
bne_layout_cols_pkg.insert_row(l_rowid,
p_application_id, --APPLICATION_ID
l_layout_code, --LAYOUT_CODE
1, --BLOCK_ID
(i.sequence_num * 10), --SEQUENCE_NUM
1, --OBJECT_VERSION_NUMBER
p_application_id, --INTERFACE_APP_ID
l_interface_code, --INTERFACE_CODE
i.sequence_num, --INTERFACE_SEQ_NUM
NULL, --STYLE_CLASS
NULL, --HINT_STYLE
NULL, --HINT_STYLE_CLASS
NULL, --PROMPT_STYLE
NULL, --PROMPT_STYLE_CLASS
NULL, --DEFAULT_TYPE
NULL, --DEFAULT_VALUE
NULL, --STYLE
SYSDATE, --CREATION_DATE
fnd_global.user_id, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
fnd_global.user_id, --LAST_UPDATED_BY
fnd_global.user_id, --LAST_UPDATE_LOGIN
NULL, --DISPLAY_WIDTH
'N' --READ_ONLY_FLAG
)
END LOOP
--todo 7 create map line .I think there is a bug at: BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP. because they cannot create the map line.
create_map_line(p_application_id => p_application_id,
p_interface_code => l_interface_code,
p_content_code => l_content_code,
p_mapping_code => l_mapping_code)
dbms_output.put_line('l_integrator_code:' l_integrator_code)
dbms_output.put_line('l_param_list_code' l_param_list_code)
dbms_output.put_line('l_interface_code:' l_interface_code)
dbms_output.put_line('l_mapping_code:' l_mapping_code)
dbms_output.put_line('l_layout_code:' l_layout_code)
dbms_output.put_line('l_content_code:' l_content_code)
-- todo 8 create function
---begin create function
l_parameters := l_parameters chr(38) 'bne:integrator='
p_application_id ':' l_integrator_code chr(38)
'bne:noreview=true'
dbms_output.put_line('parameters:' l_parameters);
SELECT fnd_form_functions_s.NEXTVAL INTO l_function_id FROM sys.dual;
fnd_form_functions_pkg.insert_row(x_rowid => l_rowid,
x_function_id => l_function_id,
x_web_host_name => '',
x_web_agent_name => '',
x_web_html_call => 'BneApplicationService',
x_web_encrypt_parameters => '',
x_web_secured => '',
x_object_id => '',
x_region_application_id => '',
x_region_code => '',
x_web_icon => '',
x_function_name => l_integrator_code,
x_application_id => '',
x_form_id => '',
x_parameters => l_parameters,
x_type => 'SERVLET',
x_user_function_name => p_integrator_name,
x_description => '',
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_maintenance_mode_support => '',
x_context_dependence => '',
x_jrad_ref_path => '');
dbms_output.put_line('function_id:' l_function_id);
---end create function
--todo 9 add other language
/*bne_integrators_pkg.add_language
bne_interface_cols_pkg.add_language
bne_content_cols_pkg.add_language
bne_contents_pkg.add_language
bne_integrators_pkg.add_language
bne_layouts_pkg.add_language
bne_layout_blocks_pkg.add_language
bne_mappings_pkg.add_language
bne_mapping_lines_pkg.add_language*/
-- todo 10 update col name OPTIONAL
UPDATE bne_interface_cols_tl
SET prompt_left = sky_test_webadi.get_col_name(prompt_left),
prompt_above = sky_test_webadi.get_col_name(prompt_above)
WHERE application_id = p_application_id
AND interface_code = l_interface_code;
END;
BEGIN
create_bne_integrator(p_application_id => 200,
p_object_code => 'SKY_TEST_WEBADI1',
p_integrator_name => 'SKY_TEST_WEBADI1',
p_package_name => 'SKY_TEST_WEBADI',
p_procedure_name => 'CREATE_TEST');
END;