Oracle Applications: Adding Columns to Webadi

Using Stored Procedure

The Datatype will take VARCHAR2 by default with size 240

begin

XXEXPD_ADD_WEBADI_COLS(p_parameter_name => 'P_REMITTERS_UNIT_CODE',

p_display_name => 'REMITTERS_UNIT_CODE',

p_webadi_name => 'Webadi Name'

);

end;

To Find the Webadi Details Using Webadi Name

--TO Get the Webadi Details

SELECT biv.application_id,

biv.user_name webadi_name,

bi.interface_name,

biv.integrator_code,

bi.upload_param_list_code,

ba.attribute_code,

ba.attribute1 Type,

ba.attribute2 Package_Name,

blb.layout_code,

bi.interface_code

FROM bne_integrators_vl biv,

bne_layouts_b blb,

bne_interfaces_b bi,

bne_param_lists_b bpl,

bne_attributes ba

WHERE 1 = 1

AND biv.user_name like '%Customer%' --Webadi Name

AND bi.integrator_code = biv.integrator_code

AND biv.integrator_code = blb.integrator_code

AND bi.upload_param_list_code = bpl.param_list_code

AND bpl.attribute_code = ba.attribute_code;

Adding Columns to Webadi

INSERT INTO bne_param_list_items (application_id,

param_list_code,

sequence_num,

object_version_number,

param_name,

attribute_app_id,

attribute_code,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login)

VALUES (20003,

'XXYZ_XINTG_UPL1',

39,

'P_ATTR_FLAG',

20003,

'XXYZ_XINTG_UPL1_A39',

SYSDATE,

0);

INSERT INTO bne_attributes (APPLICATION_ID,

ATTRIBUTE_CODE,

object_version_number,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3,

ATTRIBUTE4,

ATTRIBUTE6,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login)

VALUES (20003,

'XXYZ_XINTG_UPL1_A39',

'P_ATTR_FLAG',

'VARCHAR2',

'IN',

'N',

2000,

SYSDATE,

0);

BEGIN

BNE_INTEGRATOR_UTILS.UPSERT_INTERFACE_COLUMN (

P_APPLICATION_ID => 20003, --P_APPLICATION_ID,

P_INTERFACE_CODE => 'XXYZ_XINTG_INTF1',

P_SEQUENCE_NUM => '40',

P_INTERFACE_COL_TYPE => '1',

P_INTERFACE_COL_NAME => 'P_ATTR_FLAG',

P_ENABLED_FLAG => 'Y',

P_REQUIRED_FLAG => 'N',

P_DISPLAY_FLAG => 'Y',

P_FIELD_SIZE => NULL,

P_DEFAULT_TYPE => NULL,

P_DEFAULT_VALUE => NULL,

P_SEGMENT_NUMBER => NULL,

P_GROUP_NAME => NULL,

P_OA_FLEX_CODE => NULL,

P_OA_CONCAT_FLEX => NULL,

P_READ_ONLY_FLAG => 'N',

P_VAL_TYPE => NULL,

P_VAL_ID_COL => NULL,

P_VAL_MEAN_COL => NULL,

P_VAL_DESC_COL => NULL,

P_VAL_OBJ_NAME => NULL,

P_VAL_ADDL_W_C => NULL,

P_DATA_TYPE => 2,

P_NOT_NULL_FLAG => 'N',

P_VAL_COMPONENT_APP_ID => NULL,

P_VAL_COMPONENT_CODE => NULL,

P_SUMMARY_FLAG => 'N',

P_MAPPING_ENABLED_FLAG => 'Y',

P_PROMPT_LEFT => 'PAY ATTR FLAG',

P_PROMPT_ABOVE => 'PAY ATTR FLAG',

P_USER_HINT => NULL,

P_USER_HELP_TEXT => NULL,

P_LANGUAGE => 'US',

P_SOURCE_LANG => 'US',

P_OA_FLEX_NUM => NULL,

P_OA_FLEX_APPLICATION_ID => NULL,

P_DISPLAY_ORDER => '40',

P_UPLOAD_PARAM_LIST_ITEM_NUM => '40',

P_EXPANDED_SQL_QUERY => NULL,

P_LOV_TYPE => NULL,

P_OFFLINE_LOV_ENABLED_FLAG => NULL,

P_VARIABLE_DATA_TYPE_CLASS => NULL,

P_USER_ID => 0);

DBMS_OUTPUT.PUT_LINE ('SUCESS');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

Procedure Name

CREATE OR REPLACE PROCEDURE APPS.XXEXPD_ADD_WEBADI_COLS(

p_parameter_name IN VARCHAR2,

p_display_name IN VARCHAR2,

p_webadi_name IN VARCHAR2,

p_datatype IN VARCHAR2 DEFAULT NULL,

p_size IN NUMBER DEFAULT NULL

) IS

l_sequence_num NUMBER;

l_exception EXCEPTION;

l_data_type VARCHAR2(100);

ln_data_type NUMBER;

l_size NUMBER;

l_interface_code VARCHAR2(100);

l_param_list_code VARCHAR2(100);

BEGIN

begin

fnd_global.apps_initialize(51067, 20419,0);

end;

IF fnd_global.user_id = -1 THEN

DBMS_OUTPUT.PUT_LINE('Please Initialize the User Vlaues');

raise l_exception;

END IF;

IF p_datatype IS NOT NULL AND p_datatype NOT IN ('NUMBER','VARCHAR2','DATE') THEN

RAISE l_exception;

END IF;

IF p_datatype IS NULL THEN

l_data_type := 'VARCHAR2';

ln_data_type := 2;

l_size := 2000;

END IF;

IF p_datatype = 'NUMBER' OR p_datatype = 'DATE' THEN

l_data_type := p_datatype;

l_size := NULL;

IF p_datatype = 'NUMBER' THEN ln_data_type := 1; END IF;

IF p_datatype = 'DATE' THEN ln_data_type := 3; END IF;

END IF;

BEGIN

SELECT --biv.integrator_code,

bi.interface_code,

bi.upload_param_list_code

/*biv.application_id,

biv.user_name webadi_name,

bi.interface_name,

bi.upload_param_list_code,

ba.attribute_code,

ba.attribute1 Type,

ba.attribute2 Package_Name,

blb.layout_code*/

INTO l_interface_code

, l_param_list_code

FROM bne_integrators_vl biv,

bne_layouts_b blb,

bne_interfaces_b bi,

bne_param_lists_b bpl,

bne_attributes ba

WHERE 1 = 1

AND biv.user_name = p_webadi_name-- 'EXPD AR Offline Customer Upload Integrator' --Webadi Name

AND bi.integrator_code = biv.integrator_code

AND biv.integrator_code = blb.integrator_code

AND bi.upload_param_list_code = bpl.param_list_code

AND bpl.attribute_code = ba.attribute_code;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Invalid Webadi Name ');

RAISE l_exception;

END;

BEGIN

select max(sequence_num)+1

INTO l_sequence_num

from BNE_INTERFACE_COLS_B

where interface_code = l_interface_code;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected Error While Fetching the Sequence '||l_sequence_num);

RAISE l_exception;

END;

IF l_sequence_num IS NULL THEN

Raise l_exception;

END IF;

BEGIN

INSERT INTO bne_attributes (APPLICATION_ID,

ATTRIBUTE_CODE,

object_version_number,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3,

ATTRIBUTE4,

ATTRIBUTE6,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login)

VALUES (20003,

l_param_list_code||'_A'||l_sequence_num,--'EXPDOCUD_XINTG_UPL1_A50',

p_parameter_name,--'P_CUSTOMER_ATTRIBUTE1',

l_data_type,

'IN',

'N',

l_size,

fnd_global.user_id,

SYSDATE,

fnd_global.user_id,

SYSDATE,

fnd_global.login_id);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected Error While Inserting BNE Attributes');

END;

BEGIN

INSERT INTO bne_param_list_items (application_id,

param_list_code,

sequence_num,

object_version_number,

param_name,

attribute_app_id,

attribute_code,

created_by,

creation_date,

last_updated_by,

last_update_date,

last_update_login)

VALUES (20003,

l_param_list_code,--'EXPDOCUD_XINTG_UPL1',

l_sequence_num,

p_parameter_name,--'P_CUSTOMER_ATTRIBUTE1',

20003,

l_param_list_code||'_A'||l_sequence_num,--'EXPDOCUD_XINTG_UPL1_A50',

fnd_global.user_id,

SYSDATE,

fnd_global.user_id,

SYSDATE,

fnd_global.login_id);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected Error While Inserting BNE Parameter List');

END;

BEGIN

BNE_INTEGRATOR_UTILS.UPSERT_INTERFACE_COLUMN (

P_APPLICATION_ID => 20003, --P_APPLICATION_ID,

P_INTERFACE_CODE => l_interface_code,--'EXPDOCUD_XINTG_INTF1',

P_SEQUENCE_NUM => l_sequence_num,--'50',

P_INTERFACE_COL_TYPE => '1',

P_INTERFACE_COL_NAME => p_parameter_name,--'P_CUSTOMER_ATTRIBUTE1',

P_ENABLED_FLAG => 'Y',

P_REQUIRED_FLAG => 'N',

P_DISPLAY_FLAG => 'Y',

P_FIELD_SIZE => NULL,

P_DEFAULT_TYPE => NULL,

P_DEFAULT_VALUE => NULL,

P_SEGMENT_NUMBER => NULL,

P_GROUP_NAME => NULL,

P_OA_FLEX_CODE => NULL,

P_OA_CONCAT_FLEX => NULL,

P_READ_ONLY_FLAG => 'N',

P_VAL_TYPE => NULL,

P_VAL_ID_COL => NULL,

P_VAL_MEAN_COL => NULL,

P_VAL_DESC_COL => NULL,

P_VAL_OBJ_NAME => NULL,

P_VAL_ADDL_W_C => NULL,

P_DATA_TYPE => ln_data_type,

P_NOT_NULL_FLAG => 'N',

P_VAL_COMPONENT_APP_ID => NULL,

P_VAL_COMPONENT_CODE => NULL,

P_SUMMARY_FLAG => 'N',

P_MAPPING_ENABLED_FLAG => 'Y',

P_PROMPT_LEFT => p_display_name,

P_PROMPT_ABOVE => p_display_name,

P_USER_HINT => NULL,

P_USER_HELP_TEXT => NULL,

P_LANGUAGE => 'US',

P_SOURCE_LANG => 'US',

P_OA_FLEX_NUM => NULL,

P_OA_FLEX_APPLICATION_ID => NULL,

P_DISPLAY_ORDER => l_sequence_num,

P_UPLOAD_PARAM_LIST_ITEM_NUM => l_sequence_num,

P_EXPANDED_SQL_QUERY => NULL,

P_LOV_TYPE => NULL,

P_OFFLINE_LOV_ENABLED_FLAG => NULL,

P_VARIABLE_DATA_TYPE_CLASS => NULL,

P_USER_ID => fnd_global.user_id);

DBMS_OUTPUT.PUT_LINE ('SUCESS');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected Error While Inserting BNE Upsert');

END;

EXCEPTION

WHEN l_exception THEN

DBMS_OUTPUT.PUT_LINE('Unexpected While Passing the Parameters');

END;

Deleting the Webadi Columns

--To Delete the Webadi Columns

delete from bne_attributes where attribute_code like 'XXYZ_XINTG_UPL1_A%';

delete from bne_param_list_items where param_list_code= 'XXYZ_XINTG_UPL1' and sequence_num IN (61,62);

delete from BNE_INTERFACE_COLS_B where interface_code = 'XXYZ_XINTG_UPL1' and sequence_num IN ( 61,62);

Oracle Applications

Pages

Wednesday, May 30, 2018

Adding Columns to Webadi

No comments:

Post a Comment