Oracle EBS ICC demo

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;