EBS与外来项目系统对接接口item interface coding

最新推荐文章于 2024-04-08 10:18:33 发布

huangwenkangz

最新推荐文章于 2024-04-08 10:18:33 发布

阅读量2k

点赞数

分类专栏： INV 文章标签： CREATE_ITEM API

本文链接：https://blog.csdn.net/huangwenkangz/article/details/78731137

版权

目前有一个外来系统需要与EBS系统进行对接，对接的部分包括料号和BOM逻辑：
1、不管新建还是更新，它都会把这个物料的所有属性都抛过来，EBS自己检查是新建还是更新
2、EBS先新建主组织物料，再新建子组织，再更新库存分类，和成本分类，再对成品料号新建工艺路线（都是API）
coding如下：

create or replace package body JW_PLM_CREATE_ITEM_PKG is

  FUNCTION GET_OLD_CATEGORY_ID (p_ORGANIZATION_ID      in NUMBER
                               ,p_kind                 in varchar2
                               ,p_INVENTORY_ITEM_ID    in NUMBER
                                ) return number is
      v_category_set_id        number;                          
      v_old_category_id    number;
  begin
       
       begin 
        select T.CATEGORY_SET_ID
          into v_category_set_id
          from mtl_category_sets_v t
         where t.CATEGORY_SET_NAME = p_kind;
       exception when others then 
          v_category_set_id:=null;
       end;
       
       
       
       begin
          SELECT T.Category_Id INTO v_old_category_id 
            FROM MTL_ITEM_CATEGORIES T
           WHERE T.ORGANIZATION_ID = p_ORGANIZATION_ID
             AND T.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
             AND T.CATEGORY_SET_ID = v_category_set_id;
       exception when others then 
          v_old_category_id:=null;
       end;
       
       return v_old_category_id;
  end  GET_OLD_CATEGORY_ID;
  
  PROCEDURE UPDATE_CATEGORY  (x_error_status      out varchar2
                             ,x_error_msg         out varchar2
                             ,p_organization_id   in number
                             ,p_category_id       in number
                             ,p_old_category_id   in number
                             ,p_inventory_item_id in number
                             ,p_category_set_id   in number
                             ) IS
      v_errorcode             number;
      v_msg_count             number;
      v_return_status         varchar2(300);
      v_msg_data              varchar2(6000);
  BEGIN 
        x_error_status:='S';
        /*
        inv_item_category_pub.create_category_assignment
                              (p_api_version => '1.0',
                              p_init_msg_list => fnd_api.g_true,
                              p_commit => fnd_api.g_false,
                              x_return_status => v_return_status,
                              x_errorcode => v_errorcode,
                              x_msg_count => v_msg_count,
                              x_msg_data => v_msg_data,
                              p_category_id => p_category_id,
                              p_category_set_id => p_category_set_id,
                              p_inventory_item_id=>p_inventory_item_id,
                              p_organization_id => p_organization_id);
*/


        inv_item_category_pub.update_category_assignment
                              (p_api_version         => '1.0'
                              ,p_init_msg_list       => fnd_api.g_true
                              ,p_commit              => 'F'
                              ,p_category_id         =>p_category_id
                              ,p_old_category_id     =>p_old_category_id
                              ,p_category_set_id     =>p_category_set_id
                              ,p_inventory_item_id   =>p_inventory_item_id
                              ,p_organization_id     =>p_organization_id
                              ,x_return_status       =>v_return_status
                              ,x_errorcode           => v_errorcode
                              ,x_msg_count           =>v_msg_count
                              ,x_msg_data            =>v_msg_data
                              );


        IF v_return_status <> fnd_api.g_ret_sts_success THEN
          ROLLBACK;  
          
          x_error_status:='E';
          x_error_msg:='Message Text:'||v_msg_data; 
      
        ELSE

          COMMIT;  

        END IF; 

  END UPDATE_CATEGORY;
  
  PROCEDURE PROCESS_RTG(p_organization_id  IN NUMBER,
                        p_item_number      IN VARCHAR2,
                        p_m_class          IN VARCHAR2,
                        x_error_status   IN OUT VARCHAR2,
                        x_msg_data        OUT VARCHAR2) IS
                        
                        
    l_rtg_header_rec  bom_rtg_pub.rtg_header_rec_type;
    l_operation_tbl   bom_rtg_pub.operation_tbl_type;
    l_op_resource_tbl bom_rtg_pub.op_resource_tbl_type;
                           
    x_rtg_header_rec   bom_rtg_pub.rtg_header_rec_type;
    x_rtg_revision_tbl bom_rtg_pub.rtg_revision_tbl_type;
    x_operation_tbl    bom_rtg_pub.operation_tbl_type;
    x_op_resource_tbl  bom_rtg_pub.op_resource_tbl_type;
    x_sub_resource_tbl bom_rtg_pub.sub_resource_tbl_type;
    x_op_network_tbl   bom_rtg_pub.op_network_tbl_type;
    x_message_list     error_handler.error_tbl_type;
    x_msg_count        NUMBER;
    --l_user_id          NUMBER := 1110; --User ID
    
    l_cnt              NUMBER:=1;
    v_organization_code   VARCHAR2(90);
    x_return_status       VARCHAR2(30); 
  BEGIN
    -- Initialize first, or create_by will be -1

  
    apps.fnd_global.APPS_INITIALIZE(user_id      => 1553,
                               resp_id         => 50672,
                               resp_appl_id => 724);

    x_error_status:='S';
     
    begin
       select mp.organization_code
         into v_organization_code
         from mtl_parameters mp
        where mp.organization_id = p_organization_id;   
    exception when others then 
       null;
    end;
    
    
     -- Create the routing header
     l_rtg_header_rec.assembly_item_name:=p_item_number;
     l_rtg_header_rec.organization_code:=v_organization_code;
     l_rtg_header_rec.alternate_routing_code:=NULL;
     l_rtg_header_rec.transaction_type:='CREATE';
     
     -- Create the routing header 工艺路线序号栏位依次为10 SMT,20 DIP,30 ASS, 40 PK, 50 COM
     -- operation 10 
     l_operation_tbl(l_cnt).assembly_item_name := p_ITEM_NUMBER;
     l_operation_tbl(l_cnt).organization_code := v_organization_code;
     l_operation_tbl(l_cnt).alternate_routing_code := NULL; 
     l_operation_tbl(l_cnt).operation_sequence_number :=10; 
     l_operation_tbl(l_cnt).operation_type := 1;  
     l_operation_tbl(l_cnt).start_effective_date := SYSDATE; 
     l_operation_tbl(l_cnt).standard_operation_code := 'SMT';
     --l_operation_tbl(l_cnt).yield :=1;--产出率 
     l_operation_tbl(l_cnt).transaction_type := 'CREATE';
     l_operation_tbl(l_cnt).REFERENCE_FLAG :=1;--2;--参考字段的Flag不能勾选，否则无法更改工序资源的单位使用量 
     
     -- operation 20
     l_cnt:=l_cnt+1;
     
     l_operation_tbl(l_cnt).assembly_item_name:=p_ITEM_NUMBER;  
     l_operation_tbl(l_cnt).organization_code:=  v_organization_code;
     l_operation_tbl(l_cnt).alternate_routing_code := NULL; 
     l_operation_tbl(l_cnt).operation_sequence_number := 20;
     l_operation_tbl(l_cnt).operation_type := 1;  
     l_operation_tbl(l_cnt).start_effective_date := SYSDATE; 
     l_operation_tbl(l_cnt).standard_operation_code := 'DIP';
     --l_operation_tbl(l_cnt).yield :=1;--产出率 
     l_operation_tbl(l_cnt).transaction_type := 'CREATE';
     l_operation_tbl(l_cnt).REFERENCE_FLAG :=1;--2;--参考字段的Flag不能勾选，否则无法更改工序资源的单位使用量 
     
     -- operation 30
     l_cnt:=l_cnt+1;
     
     l_operation_tbl(l_cnt).assembly_item_name:= p_ITEM_NUMBER; 
     l_operation_tbl(l_cnt).organization_code:=  v_organization_code;
     l_operation_tbl(l_cnt).alternate_routing_code := NULL; 
     l_operation_tbl(l_cnt).operation_sequence_number := 30;
     l_operation_tbl(l_cnt).operation_type := 1;  
     l_operation_tbl(l_cnt).start_effective_date := SYSDATE; 
     l_operation_tbl(l_cnt).standard_operation_code := 'ASS';
     --l_operation_tbl(l_cnt).yield :=1;--产出率 
     l_operation_tbl(l_cnt).transaction_type := 'CREATE';
     l_operation_tbl(l_cnt).REFERENCE_FLAG :=1;--2;--参考字段的Flag不能勾选，否则无法更改工序资源的单位使用量 
     
     -- operation 40
     l_cnt:=l_cnt+1;
     
     l_operation_tbl(l_cnt).assembly_item_name := p_ITEM_NUMBER;
     l_operation_tbl(l_cnt).organization_code := v_organization_code;
     l_operation_tbl(l_cnt).alternate_routing_code := NULL; 
     l_operation_tbl(l_cnt).operation_sequence_number := 40;
     l_operation_tbl(l_cnt).operation_type := 1;  
     l_operation_tbl(l_cnt).start_effective_date := SYSDATE; 
     l_operation_tbl(l_cnt).standard_operation_code := 'PK';
     --l_operation_tbl(l_cnt).yield :=1;--产出率 
     l_operation_tbl(l_cnt).transaction_type := 'CREATE';
     l_operation_tbl(l_cnt).REFERENCE_FLAG :=1;--2;--参考字段的Flag不能勾选，否则无法更改工序资源的单位使用量 
     
     
     if p_m_