DECLARE
l_Api_Version Number := 1;
L_Object_Name Varchar2(20) := 'EGO_ITEM';
l_attributes_row_table EGO_USER_ATTR_ROW_TABLE := EGO_USER_ATTR_ROW_TABLE();
l_attributes_data_table EGO_USER_ATTR_DATA_TABLE := EGO_USER_ATTR_DATA_TABLE();
L_Entity_Id Number := Null;
L_Entity_Index Number := Null;
L_Entity_Code Varchar2(1) := Null;
L_Debug_Level Number := 3;
l_init_error_handler VARCHAR2(1) := FND_API.G_TRUE;
L_Write_To_Concurrent_Log Varchar2(1) := FND_API.G_TRUE;
L_Init_Fnd_Msg_List Varchar2(1) := FND_API.G_TRUE;
l_log_errors VARCHAR2(1) := FND_API.G_TRUE;
L_Add_Errors_To_Fnd_Stack Varchar2(1) := FND_API.G_TRUE;
L_Commit Varchar2(1) := FND_API.G_False; -- Do NOT set to TRUE, Verify the data and then COMMIT !
X_Failed_Row_Id_List Varchar2(255);
X_Return_Status Varchar2(10);
X_Errorcode Number;
X_Msg_Count Number;
x_msg_data VARCHAR2(255);
x_message_list Error_Handler.Error_Tbl_Type;
L_User_Id Number := -1;
L_Resp_Id Number := -1;
L_Application_Id Number := -1;
l_rowcnt NUMBER := 1;
L_Inventory_Item_Id Number := 0;
l_organization_id NUMBER := 0;
L_Item_Catalog_Group_Id Number := 0;
L_Attr_Group_Type Number := 0;
L_User_Name Varchar2(20) := 'CALT';
L_Resp_Name Varchar2(40) := '产品信息数据管理员';
P_Attr_Group_Name Varchar2(100) := 'CASC_COMPONENT_GROUP';
P_Attr_Group_Type Varchar2(100) := 'EGO_ITEMMGMT_GROUP';
P_Attr_Group_App_Id Number := 431;
p_data_level VARCHAR2(100) := 'ITEM_REVISION_LEVEL';
p_organization_code VARCHAR2(5) := 'WL';
p_item_name VARCHAR2(40) := 'LS00000001';
p_item_cat_grp_name VARCHAR2(40) := 'YY0102010101';
L_Attr_Name1 Varchar2(100) := 'SEPC_CONDITION'; -- attribute1
L_Attr_Value1 Varchar2(100) := 'ZZ';
L_Attr_Name2 Varchar2(100) := 'SU_SROW_UDA_N1'; -- attribute2
L_Attr_Value2 Varchar2(100) := '9';
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
Into L_Application_Id, L_Resp_Id
FROM fnd_responsibility_vl
WHERE responsibility_name = l_resp_name;
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- MGRPLM / Development Manager / EGO
dbms_output.put_line('Initialized applications context: ' || l_user_id || ' ' ||
l_resp_id || ' ' || l_application_id);
-- Get the item for which the attributes are to be defined
SELECT msi.organization_id, msi.inventory_item_id
INTO l_organization_id, l_inventory_item_id
FROM mtl_system_items_b msi, mtl_parameters mp
WHERE mp.organization_id = msi.organization_id
AND segment1 = p_item_name -- Item name
AND organization_code = p_organization_code; -- Organization Code
dbms_output.put_line('Organization ID: ' || l_organization_id ||
' Inventory Item ID: ' || l_inventory_item_id);
-- Get the item catalog category where the item is defined
SELECT catalog_group_id
INTO l_item_catalog_group_id
FROM ego_catalog_groups_v
WHERE catalog_group = p_item_cat_grp_name;
dbms_output.put_line('Item Catalog Group ID: ' ||
l_item_catalog_group_id);
-- Add in the attribute groups here, multirow attribute groups should have the same attr_group_id
-- transaction_type can be CREATE | UPDATE | SYNC
l_attributes_row_table := Ego_User_Attr_Row_Table(EGO_USER_ATTR_ROW_OBJ(9991 -- ROW_IDENTIFIER
,
Null -- ATTR_GROUP_ID from EGO_ATTR_GROUPS_V
,
P_Attr_Group_App_Id -- ATTR_GROUP_APP_ID
,
P_Attr_Group_Type -- ATTR_GROUP_TYPE
,
P_Attr_Group_Name -- ATTR_GROUP_NAME
,
p_data_level -- NDATA_LEVEL
,
3792664 -- DATA_LEVEL_1 (Required if attribute groups are at revision level)
,
NULL -- DATA_LEVEL_2
,
Null -- DATA_LEVEL_3
,
NULL -- DATA_LEVEL_4
,
NULL -- DATA_LEVEL_5
,
Ego_User_Attrs_Data_Pvt.G_Sync_Mode -- TRANSACTION_TYPE
));
-- Add the attribute - attribute values here, for the attr groups above
-- row_identifier is the foriegn key from the attribute group defined above
-- user_row_identifier is used for error handling
l_attributes_data_table.EXTEND;
l_attributes_data_table(l_rowcnt) := Ego_User_Attr_Data_Obj(9991 -- ROW_IDENTIFIER from above
,
L_Attr_Name1 -- ATTR_NAME
,
Null -- ATTR_VALUE_STR
,
Null -- ATTR_VALUE_NUM
,
NULL -- ATTR_VALUE_DATE
,
L_Attr_Value1 -- ATTR_DISP_VALUE
,
Null -- ATTR_UNIT_OF_MEASURE
,
l_rowcnt -- USER_ROW_IDENTIFIER
);
/* l_rowcnt := l_rowcnt + 1;
l_attributes_data_table.EXTEND;
l_attributes_data_table(l_rowcnt) := Ego_User_Attr_Data_Obj(9991 -- ROW_IDENTIFIER from above
,
L_Attr_Name2 -- ATTR_NAME
,
Null -- ATTR_VALUE_STR
,
Null -- ATTR_VALUE_NUM
,
Null -- ATTR_VALUE_DATE
,
L_Attr_Value2 -- ATTR_DISP_VALUE
,
Null -- ATTR_UNIT_OF_MEASURE
,
l_rowcnt -- USER_ROW_IDENTIFIER
);
-- call API to load Items
DBMS_OUTPUT.PUT_LINE('===========================================');
DBMS_OUTPUT.PUT_LINE('Calling EGO_USER_ATTRS_DATA_PUB.PROCESS_USER_ATTRS_FOR_ITEM API');*/
EGO_ITEM_PUB.PROCESS_USER_ATTRS_FOR_ITEM(l_api_version,
l_inventory_item_id,
l_organization_id,
l_attributes_row_table,
l_attributes_data_table,
l_entity_id,
l_entity_index,
l_entity_code,
l_debug_level,
l_init_error_handler,
l_write_to_concurrent_log,
l_init_fnd_msg_list,
l_log_errors,
l_add_errors_to_fnd_stack,
l_commit,
x_failed_row_id_list,
x_return_status,
x_errorcode,
x_msg_count,
x_msg_data);
DBMS_OUTPUT.PUT_LINE('=========================================');
DBMS_OUTPUT.PUT_LINE('Return Status: ' || x_return_status);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('=========================================');
EXCEPTION
When Others Then
DBMS_OUTPUT.ENABLE();
Dbms_Output.Put_Line('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('========================================');
END;