ORACLE EBS物料事务处理API

最新推荐文章于 2021-11-04 10:58:31 发布
千灬尘
最新推荐文章于 2021-11-04 10:58:31 发布
阅读量1k
点赞数
分类专栏： EBS
本文链接：https://blog.csdn.net/u013555394/article/details/116006854
版权
EBS 专栏收录该内容
4 篇文章 1 订阅
订阅专栏
根据不同的transaction_type_id生成对应的事务处理
--插入库存事务接口表
DECLARE
  l_uom_code              VARCHAR2(30);
  l_ccid                  NUMBER;
  l_transaction_header_id NUMBER;
  l_retval                NUMBER;
  l_mtl_txn_interface_rec mtl_transactions_interface%ROWTYPE;
  l_txn_count             NUMBER;
  l_count                 NUMBER := 0;

  x_return_status VARCHAR2(4);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(2000);

  x_trans_count NUMBER;
  x_err_msg     VARCHAR2(2000);

  x_transaction_header_id NUMBER;
  l_segment1              VARCHAR2(100);
  l_segment2              VARCHAR2(100);
  l_segment3              VARCHAR2(100) := '6401010101';
  l_segment4              VARCHAR2(100) := '0';
  l_segment5              VARCHAR2(100) := '0';
  l_segment6              VARCHAR2(100) := '0';
  l_segment7              VARCHAR2(100) := '0';
  l_segment8              VARCHAR2(100) := '0';
  l_segment9              VARCHAR2(100) := '0';
  l_segment10             VARCHAR2(100) := '0';
  l_coa_code              VARCHAR2(1000);
  l_create_result         BOOLEAN;
  l_header_id             NUMBER;
  --l_transaction_type_id   NUMBER := 102; -- 销售退货入库
  --l_transaction_action_id NUMBER := 27; -- 销售退货入库
  l_transaction_type_id   NUMBER := 100; -- 订单投妥
  l_transaction_action_id NUMBER := 1; -- 订单投妥
  l_primary_quantity NUMBER;
  l_cost_center      VARCHAR2(100) := '100803'; -- 全球供应链
  l_trx_date         DATE := SYSDATE /*to_date('2020/10/31'
                                                   ,'YYYY/MM/DD')*/
   ;

  --l_actual_cost NUMBER := 4.2212;
  l_actual_cost NUMBER := NULL;

  CURSOR c IS
    SELECT m.organization_id
          ,m.inventory_item_id
          ,m.subinventory_code
          ,m.transaction_type_id
          ,m.transaction_source_name
          ,m.source_line_id
          ,msi.primary_uom_code
          ,m.distribution_account_id
          --,m.actual_cost
          ,SUM(m.primary_quantity) * -1 primary_quantity
      FROM mtl_material_transactions m
          ,mtl_system_items_b        msi
     WHERE m.transaction_source_name IN ('RWE20201111103998')
       AND m.organization_id = msi.organization_id
       AND m.inventory_item_id = msi.inventory_item_id
       AND m.organization_id = 181
          --AND msi.segment1 = '10165943'
       AND m.transaction_type_id = 100 --订单投妥
    --AND m.creation_date >= SYSDATE -1
    -- AND ROWNUM =1
     GROUP BY m.organization_id
             ,m.inventory_item_id
             ,m.subinventory_code
             ,m.transaction_type_id
             ,m.transaction_source_name
             ,m.source_line_id
             ,msi.primary_uom_code
             ,m.distribution_account_id
             /*,m.actual_cost*/;

BEGIN

  -- qd_inv
  fnd_global.apps_initialize(user_id      => 1210
                            ,resp_id      => 50972
                            ,resp_appl_id => 50188);
  --mo_global.init('S');

  FOR r IN c LOOP
  
    /*IF r.organization_id = 143 THEN
      l_segment1 := 'BLYS0';
    ELSIF r.organization_id = 144 THEN
      l_segment1 := 'QQGM0';
    ELSIF r.organization_id = 241 THEN
      l_segment1 := 'JHGS0';
    END IF;
    
    BEGIN
      SELECT pha.attribute1
            ,coo.header_id
        INTO l_segment2
            ,l_header_id
        FROM po_headers_all             pha
            ,cux_3_om_order_headers_all coo
       WHERE coo.header_id = to_number(pha.attribute5)
         AND coo.order_number = r.transaction_source_name
         AND coo.org_id = r.organization_id
         AND rownum = 1;
    EXCEPTION
      WHEN OTHERS THEN
        l_segment2 := NULL;
        dbms_output.put_line('get cost center error, order number is ' ||
                             r.transaction_source_name);
        continue;
    END;
    --l_header_id:= 13788431;
    
    IF l_segment2 IS NULL THEN
      l_segment2 := l_cost_center;
    END IF;
    --l_segment2 := '0';
    
    l_coa_code := l_segment1 || '.' || l_segment2 || '.' || l_segment3 || '.' ||
                  l_segment4 || '.' || l_segment5 || '.' || l_segment6 || '.' ||
                  l_segment7 || '.' || l_segment8 || '.' || l_segment9 || '.' ||
                  l_segment10;
    
    --获取账户CCID
    dbms_output.put_line('l_coa_code : ' || l_coa_code);
    BEGIN
      SELECT gcc.code_combination_id
        INTO l_ccid
        FROM gl_code_combinations_kfv gcc
       WHERE gcc.concatenated_segments = l_coa_code;
    EXCEPTION
      WHEN OTHERS THEN
        l_create_result := fnd_flex_keyval.validate_segs(operation        => 'CREATE_COMBINATION'
                                                        ,appl_short_name  => 'SQLGL'
                                                        ,key_flex_code    => 'GL#'
                                                        ,structure_number => 50409
                                                        ,concat_segments  => l_coa_code);
      
        IF (l_create_result) THEN
          dbms_output.put_line('new l_ccid : ' || l_ccid);
          l_ccid := fnd_flex_keyval.combination_id;
        ELSE
          dbms_output.put_line('create ccid error : ' ||
                               fnd_flex_keyval.error_message);
          continue;
        END IF;
    END;*/
  
    dbms_output.put_line('l_ccid : ' || l_ccid);
  
    l_transaction_header_id := mtl_material_transactions_s.nextval;
  
    dbms_output.put_line('l_transaction_header_id:' ||
                         l_transaction_header_id);
  
    IF l_transaction_type_id = 102 THEN
      l_primary_quantity := r.primary_quantity;
    ELSIF l_transaction_type_id = 100 THEN
      l_primary_quantity := r.primary_quantity * -1;
    END IF;
  
    l_mtl_txn_interface_rec.transaction_interface_id := l_transaction_header_id;
    l_mtl_txn_interface_rec.transaction_header_id    := l_transaction_header_id;
    l_mtl_txn_interface_rec.process_flag             := 1;
    l_mtl_txn_interface_rec.transaction_mode         := 3; --On-line processing模式
    l_mtl_txn_interface_rec.transaction_type_id      := l_transaction_type_id; -- 销售退货入库
    l_mtl_txn_interface_rec.transaction_action_id    := l_transaction_action_id;
    l_mtl_txn_interface_rec.inventory_item_id        := r.inventory_item_id;
    l_mtl_txn_interface_rec.organization_id          := r.organization_id;
    l_mtl_txn_interface_rec.subinventory_code        := r.subinventory_code;
    l_mtl_txn_interface_rec.transaction_date         := l_trx_date;
    l_mtl_txn_interface_rec.transaction_quantity     := l_primary_quantity;
    l_mtl_txn_interface_rec.primary_quantity         := l_primary_quantity;
    l_mtl_txn_interface_rec.transaction_uom          := r.primary_uom_code;
  
    --l_actual_cost := r.actual_cost;
    IF l_actual_cost IS NOT NULL THEN
      l_mtl_txn_interface_rec.transaction_cost := l_actual_cost;
    END IF;
  
    l_mtl_txn_interface_rec.distribution_account_id := r.distribution_account_id /*l_ccid*/
     ; --分配账户
    l_mtl_txn_interface_rec.transaction_source_name := r.transaction_source_name; --l_document_number; --单据号
    l_mtl_txn_interface_rec.source_code             := r.transaction_source_name;
    l_mtl_txn_interface_rec.source_line_id          := -1 /*r.source_line_id*/
     ;
    l_mtl_txn_interface_rec.source_header_id        := -1 /*l_header_id*/
     ; --so header id
    l_mtl_txn_interface_rec.last_update_date        := SYSDATE;
    l_mtl_txn_interface_rec.last_updated_by         := fnd_global.user_id;
    l_mtl_txn_interface_rec.creation_date           := SYSDATE;
    l_mtl_txn_interface_rec.created_by              := fnd_global.user_id;
    l_mtl_txn_interface_rec.last_update_login       := fnd_global.login_id;
  
    --插入事务处理接口表
    INSERT INTO mtl_transactions_interface VALUES l_mtl_txn_interface_rec;
    -- commit;
    l_retval := inv_txn_manager_pub.process_transactions(p_api_version   => '1.0' --p_api_version
                                                        ,p_init_msg_list => fnd_api.g_false -- p_init_msg_list
                                                        ,p_commit        => fnd_api.g_false --p_commit
                                                        ,x_return_status => x_return_status
                                                        ,x_msg_count     => x_msg_count
                                                        ,x_msg_data      => x_msg_data
                                                        ,x_trans_count   => l_txn_count
                                                        ,p_table         => 1
                                                        ,p_header_id     => l_transaction_header_id);
    COMMIT; --注意此处务必要commit                                                         
    dbms_output.put_line('8-' || l_retval || ',' || x_return_status || ',' ||
                         x_msg_count || ',' || x_msg_data);
  
    SELECT COUNT(1)
      INTO l_count
      FROM mtl_transactions_interface
     WHERE transaction_header_id = l_transaction_header_id;
  
    IF l_retval <> 0 OR l_count > 0 THEN
      -- 返回出错信息记录组
      BEGIN
        SELECT 'ERROR_CODE:' || mti.error_code || ';' || 'ERROR_MSG:' ||
               mti.error_explanation
          INTO x_err_msg
          FROM mtl_transactions_interface mti
         WHERE transaction_header_id = l_transaction_header_id
           AND rownum = 1;
        dbms_output.put_line('errmsg:' || x_err_msg);
      EXCEPTION
        WHEN OTHERS THEN
          x_err_msg := 'unexpected error!';
          dbms_output.put_line('errmsg:' || x_err_msg);
      END;
    ELSE
      dbms_output.put_line('SUCCESS');
      /*DELETE FROM mtl_transactions_interface
       WHERE transaction_header_id = l_transaction_header_id;
      COMMIT;*/
    END IF;
  
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('error ohters : ' || SQLCODE || '-' || SQLERRM);
    ROLLBACK;
END;