/* 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;