EBS Webadi Stored Procedures Add parameters

CREATE OR REPLACE FUNCTION cux_exec_sql (p_sql in VARCHAR2)
RETURN number
As
L_CNT number;
BEGIN
EXECUTE IMMEDIATE p_sql into l_cnt;

RETURN l_cnt;
EXCEPTION
When OTHERS
Then
RETURN 0;
END;

--drop TABLE Cux_tables_counter;

CREATE TABLE Cux_tables_counter as
Select ' Select COUNT (*) from ' | | OWNER | | '. ' | | object_name string_cnt,-1 CNT1,-1 CNT2
From Dba_objects X
WHERE 1=1
and X.owner = ' BNE '
and X.object_type = ' TABLE '
and x.object_name like ' bne% '
;

UPDATE Cux_tables_counter
SET CNT1 = Cux_exec_sql (string_cnt);

UPDATE Cux_tables_counter
SET CNT2 = Cux_exec_sql (string_cnt);

SELECT *
From Cux_tables_counter
where 1=1
and Cnt1 <> Cnt2;

--select COUNT (*) from BNE. Bne_attributes
--select COUNT (*) from BNE. Bne_contents_b
--select COUNT (*) from BNE. BNE_CONTENTS_TL
--select COUNT (*) from BNE. Bne_integrators_b
--select COUNT (*) from BNE. BNE_INTEGRATORS_TL
--select COUNT (*) from BNE. Bne_interfaces_b
--select COUNT (*) from BNE. BNE_INTERFACES_TL
--select COUNT (*) from BNE. Bne_interface_cols_b
--select COUNT (*) from BNE. BNE_INTERFACE_COLS_TL
--select COUNT (*) from BNE. Bne_layouts_b
--select COUNT (*) from BNE. BNE_LAYOUTS_TL
--select COUNT (*) from BNE. Bne_layout_blocks_b
--select COUNT (*) from BNE. BNE_LAYOUT_BLOCKS_TL
--select COUNT (*) from BNE. Bne_layout_cols
--select COUNT (*) from BNE. Bne_param_lists_b
--select COUNT (*) from BNE. BNE_PARAM_LISTS_TL
--select COUNT (*) from BNE. Bne_param_list_items
--select COUNT (*) from BNE. Bne_secured_objects
--select COUNT (*) from BNE. Bne_security_rules
 
 

--webadi process interface, the original has 18 incoming parameters, in the definition of Webadi attribute field, added 5 not in the process parameters of the custom field, currently need to add two parameters to the process
--cux_job_import_pkg. Import_job Interface_code like CUXJIMP01

--Pushes the custom field of a non-attribute procedure parameter two positions backwards
UPDATE Bne_interface_cols_b
SET Sequence_num = Sequence_num + 2,
Display_order = Display_order + 2
WHERE 1 = 1
and Sequence_num > 18
and Interface_code = ' CUXJIMP01_XINTG_INTF1 '
;

--insert PARAMETERS to TABLE bne_interface_cols_b
INSERT into Bne_interface_cols_b
SELECT
application_id, Interface_code, Object_version_number, Sequence_num, Interface_col_type, ' P_ALTERNATE_BOM_ Designator ' 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, val_query_app_id, Val_query_code, expanded_sql_query_app_id, EXPANDED_SQL_ Query_code, Viewer_group, Edit_type, Display_width, Zd_edition_name
From Bne_interface_cols_b
WHERE 1 = 1
and interface_col_name = ' p_comments '
and Interface_code = ' CUXJIMP01_XINTG_INTF1 ';

INSERT into Bne_interface_cols_b
SELECT
application_id, Interface_code, Object_version_number, Sequence_num, Interface_col_type, ' P_ALTERNATE_ROUTING_ Designator ' 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, val_query_app_id, Val_query_code, expanded_sql_query_app_id, EXPANDED_SQL_ Query_code, Viewer_group, Edit_type, Display_width, Zd_edition_name
From Bne_interface_cols_b
WHERE 1 = 1
and interface_col_name = ' p_comments '
and Interface_code = ' CUXJIMP01_XINTG_INTF1 ';

--Pushes the custom field of a non-attribute procedure parameter two positions backwards
UPDATE BNE_INTERFACE_COLS_TL
SET Sequence_num = sequence_num + 2
WHERE 1 = 1
and Sequence_num > 18
and Interface_code = ' CUXJIMP01_XINTG_INTF1 '
;

--insert PARAMETERS to TABLE BNE_INTERFACE_COLS_TL
INSERT into BNE_INTERFACE_COLS_TL
SELECT
application_id, Interface_code, Sequence_num, LANGUAGE, Source_lang, User_hint, ' Alternate_bom_designator ' PROMPT_ Left, User_help_text, ' List of alternatives ' Prompt_above, created_by, Creation_date, last_updated_by, Last_update_login, LAST_UPDATE _date, Zd_edition_name
From BNE_INTERFACE_COLS_TL
WHERE 1 = 1
and prompt_left = ' COMMENTS '
and Interface_code = ' CUXJIMP01_XINTG_INTF1 '
;

INSERT into BNE_INTERFACE_COLS_TL
SELECT
application_id, Interface_code, Sequence_num, LANGUAGE, Source_lang, User_hint, ' Alternate_routing_designator ' Prompt_left, User_help_text, ' craft Alternatives ' prompt_above, created_by, Creation_date, Last_updated_by, LAST_UPDATE_LOGIN, last _update_date, Zd_edition_name
From BNE_INTERFACE_COLS_TL
WHERE 1 = 1
and prompt_left = ' COMMENTS '
and Interface_code = ' CUXJIMP01_XINTG_INTF1 '
;

--insert PARAMETERS to TABLE bne_attributes
INSERT into Bne_attributes
SELECT
application_id, ' Cuxjimp01_xintg_upl1_a19 ' Attribute_code, Object_version_number, ' P_alternate_bom_designator ' ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10 , ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, created_by, Creation_date, last_updated_by, Last_update_date, Last_update_login, Attribute_ NAME1, Attribute_name2, Attribute_name3, Attribute_name4, Attribute_name5, Attribute_name6, ATTRIBUTE_NAME7, Attribute_name8, Attribute_name9, Attribute_name10, Attribute_name11, Attribute_name12, ATTRIBUTE_NAME13, ATTRIBUTE_ NAME14, Attribute_name15, Attribute_name16, Attribute_name17, Attribute_name18, Attribute_name19, ATTRIBUTE_NAME20, Attribute_name21, Attribute_name22, Attribute_name23, Attribute_name24, ATTRIbute_name25, Attribute_name26, Attribute_name27, Attribute_name28, Attribute_name29, ATTRIBUTE_NAME30, ZD_EDITION_ NAME
From Bne_attributes
WHERE 1 = 1
and ATTRIBUTE1 = ' p_comments '
and attribute_code like ' cuxjimp01_xintg_upl1_a% '
ORDER by To_number (REPLACE (Attribute_code, ' cuxjimp01_xintg_upl1_a ', "));

INSERT into Bne_attributes
SELECT
application_id, ' cuxjimp01_xintg_upl1_a20 ' Attribute_code, Object_version_number, ' P_alternate_routing_designator ' ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10 , ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24, ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30, created_by, Creation_date, last_updated_by, Last_update_date, Last_update_login, Attribute_ NAME1, Attribute_name2, Attribute_name3, Attribute_name4, Attribute_name5, Attribute_name6, ATTRIBUTE_NAME7, Attribute_name8, Attribute_name9, Attribute_name10, Attribute_name11, Attribute_name12, ATTRIBUTE_NAME13, ATTRIBUTE_ NAME14, Attribute_name15, Attribute_name16, Attribute_name17, Attribute_name18, Attribute_name19, ATTRIBUTE_NAME20, Attribute_name21, Attribute_name22, Attribute_name23, Attribute_name24,Attribute_name25, Attribute_name26, Attribute_name27, Attribute_name28, Attribute_name29, ATTRIBUTE_NAME30, ZD_ Edition_name
From Bne_attributes
WHERE 1 = 1
and ATTRIBUTE1 = ' p_comments '
and attribute_code like ' cuxjimp01_xintg_upl1_a% '
ORDER by To_number (REPLACE (Attribute_code, ' cuxjimp01_xintg_upl1_a ', "));

--insert PARAMETERS to TABLE Bne_param_list_items
INSERT into Bne_param_list_items
SELECT
application_id, Param_list_code, Sequence_num, Object_version_number, param_defn_app_id, PARAM_DEFN_CODE, ' P_ Alternate_bom_designator ' Param_name, attribute_app_id, ' Cuxjimp01_xintg_upl1_a19 ' Attribute_code, STRING_VALUE, Date_value, Number_value, Boolean_value_flag, Formula_value, Desc_value, created_by, Creation_date, LAST_UPDATED_BY, Last_update_date, Last_update_login, Zd_edition_name
From Bne_param_list_items
WHERE 1 = 1
and param_name = ' p_comments '
and param_list_code like ' cuxjimp01_xintg_upl1% ';

INSERT into Bne_param_list_items
SELECT
application_id, Param_list_code, Sequence_num, object_ Version_number, param_defn_app_id, Param_defn_code, ' P_alternate_routing_designator ' PARAM_NAME, ATTRIBUTE_APP_ID, ' Cuxjimp01_xintg_upl1_a20 ' Attribute_code, string_value, Date_value, Number_value, Boolean_value_flag, FORMULA_VALUE, Desc_value, Created_by, Creation_date, last_updated_by, Last_update_date, Last_update_login, ZD_EDITION_NAME
from Bne_param_list_items
WHERE 1 = 1
and param_name = ' p_comments '
and param_list_code like ' cuxjimp01_xintg_upl1% ';

--insert PARAMETERS to TABLE bne_layout_cols
INSERT into Bne_layout_cols
SELECT
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, Display_width, READ_ONLY_FLAG, ZD_ Edition_name
from Bne_layout_cols
WHERE 1 = 1
and interface_seq_num =
and Interface_code = ' cuxjimp01_ XINTG_INTF1 ';

INSERT into Bne_layout_cols
SELECT
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, DISPLAY_WIDTH, READ_ Only_flag, Zd_edition_name
From Bne_layout_cols
WHERE 1 = 1
and interface_seq_num = 18
and Interface_code = ' CUXJIMP01_XINTG_INTF1 ';

--To do the above script processing, or to move the Desktop Integration Manager responsibilities, to the interface to update the integration interface function, and more layout, the purpose is to the background of data and the foreground glue to initialize.

--alter session Set nls_language= ' Simplified Chinese ';
--alter session Set nls_language= ' AMERICAN ';
SELECT bni.application_id
    , bni.integrator_code                                                                                                                                                                             --Integrator code
    , bni.user_name                                                                                                                                                                                     --Integrator name
    , blv.layout_code                                                                                                                                                                                    --Layout code
    , blv.user_name                                                                                                                                                                                    --Layout user name
, Blc.sequence_num
, bic.*
From BNE_INTEGRATORS_VL BNI
, BNE_LAYOUTS_VL BLV
, Bne_layout_cols BLC
, BNE_INTERFACE_COLS_VL Bic
WHERE bni.user_name like ' cuxjimp01% '
and blv.integrator_app_id = bni.application_id
and Blv.integrator_code = Bni.integrator_code
and Blv.layout_code = Blc.layout_code
and blc.interface_app_id = bic.application_id
and Blc.interface_code = Bic.interface_code
and blc.interface_seq_num = Bic.sequence_num;

EBS Webadi Stored Procedures Add parameters