ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 杂项入库和出库导入

杂项入库和出库导入

原创 Linux操作系统 作者:smallnavy 时间:2008-12-22 21:55:13 0 删除 编辑
一、 首先创建一个临时表
-- Create table
create table CUX_TRANSACTIONS_INTERFACE
(
  ORGANIZATION_CODE    VARCHAR2(30),
  ITEM_NUMBER          VARCHAR2(240),
  SUBINVENTORY_CODE    VARCHAR2(30),
  LOCATOR_ID           NUMBER,
  TRANSACTION_UOM      VARCHAR2(30),
  TRANSACTION_QUANTITY NUMBER,
  TRANSACTION_COST     NUMBER,
  BATCH_CODE           VARCHAR2(30),
  STATUS               VARCHAR2(1),
  ERROR_MESSAGE        VARCHAR2(2000)
);
-- Add comments to the columns
comment on column CUX_TRANSACTIONS_INTERFACE.ORGANIZATION_CODE  is '库存组织';
comment on column CUX_TRANSACTIONS_INTERFACE.ITEM_NUMBER  is '物料代码';
comment on column CUX_TRANSACTIONS_INTERFACE.SUBINVENTORY_CODE  is '子库';
comment on column CUX_TRANSACTIONS_INTERFACE.TRANSACTION_UOM  is '单位';
comment on column CUX_TRANSACTIONS_INTERFACE.TRANSACTION_QUANTITY  is '数量';
comment on column CUX_TRANSACTIONS_INTERFACE.TRANSACTION_COST  is '成本';
comment on column CUX_TRANSACTIONS_INTERFACE.BATCH_CODE  is '批';
comment on column CUX_TRANSACTIONS_INTERFACE.STATUS  is '状态';

二、通过接口导入导出

-- 通过接口表,帐号别名接收
DECLARE
  l_organization_id       NUMBER;
  l_user_id               NUMBER := 1171; --User ID
  l_resp_id               NUMBER := 50194;
  l_resp_appl_id          NUMBER := 401;
  l_transaction_date      DATE := to_date('2008-10-30', 'YYYY-MM-DD');
  l_transaction_mode      NUMBER := 3; -- background
  l_transaction_type_name VARCHAR2(30) := 'Account alias receipt';
  l_transaction_source    VARCHAR2(30) := '期初导入'; -- '期初导入'
  l_source_code           VARCHAR2(30) := 'Initialization Insert';
  l_transaction_type_id   NUMBER; 
  l_transaction_source_id NUMBER;
  l_inventory_item_id     NUMBER;
  l_revision_control      NUMBER;
  l_revision              NUMBER;
  l_subinventory_code     NUMBER;
  l_secondary_locator     NUMBER;
  l_source_header_id      NUMBER := 0;
  l_source_line_id        NUMBER := 0;
  l_success               BOOLEAN;
  l_primary_uom_code      VARCHAR2(3);
  lv_phase                VARCHAR2(20);
  lv_status               VARCHAR2(20);
  lv_dev_phase            VARCHAR2(20);
  lv_dev_status           VARCHAR2(20);
  lv_err_msg              VARCHAR2(2000);
  ln_request_id           NUMBER;
  lb_return               BOOLEAN;

  CURSOR cur_trx_h IS
    SELECT DISTINCT cti.batch_code
      FROM cux_transactions_interface cti
      WHERE status = 'P';

  CURSOR cur_trx(p_batch_code VARCHAR2) IS
    SELECT cti.ROWID row_id,
           cti.organization_code,
           cti.item_number,
           cti.subinventory_code,
           cti.locator_id,
           cti.transaction_uom,
           cti.transaction_quantity,
           cti.transaction_cost
      FROM cux_transactions_interface cti
     WHERE batch_code = p_batch_code
       AND status = 'P';
BEGIN
  fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

  SELECT mtt.transaction_type_id
    INTO l_transaction_type_id
    FROM mtl_transaction_types mtt
   WHERE mtt.transaction_type_name = l_transaction_type_name;

  FOR rec_h IN cur_trx_h LOOP
    l_source_header_id := l_source_header_id + 1;
    l_source_line_id   := 0;
    FOR rec IN cur_trx(rec_h.batch_code) LOOP
      l_success        := TRUE;
      l_source_line_id := l_source_line_id + 1;
      BEGIN
        --  Organization_ID
        SELECT ood.organization_id
          INTO l_organization_id
          FROM org_organization_definitions ood
         WHERE ood.organization_code = rec.organization_code;
        -- 来源
        SELECT mgd.disposition_id
          INTO l_transaction_source_id
          FROM mtl_generic_dispositions mgd
         WHERE mgd.organization_id = l_organization_id
           AND mgd.segment1 = l_transaction_source;
        -- 物料
        SELECT msi.inventory_item_id, msi.revision_qty_control_code,msi.primary_uom_code
          INTO l_inventory_item_id, l_revision_control,l_primary_uom_code
          FROM mtl_system_items_b msi
         WHERE msi.segment1 = rec.item_number
           AND msi.organization_id = l_organization_id;
         --版本
        IF l_revision_control = 2 THEN
          --1 不控制
          --2 控制
          SELECT MAX(x.revision)
            INTO l_revision
            FROM mtl_item_revisions x
           WHERE x.inventory_item_id = l_inventory_item_id
             AND x.organization_id = l_organization_id;
     --        AND x.effectivity_date <= l_transaction_date
        ELSE
          l_revision := NULL;
        END IF;  

      EXCEPTION
        WHEN OTHERS THEN
          l_success := FALSE;
      END;

      IF l_success THEN
 
        INSERT INTO inv.mtl_transactions_interface
          (last_update_date,
           last_updated_by,
           creation_date,
           created_by,
           last_update_login,
           transaction_interface_id,
           transaction_mode,
           process_flag,
           transaction_type_id,
           transaction_source_id,
           organization_id,
           inventory_item_id,
           revision,
           subinventory_code,
           locator_id,
           transaction_uom,
           transaction_quantity,
           transaction_cost,
           transaction_date,
           currency_code,
           source_code,
           source_header_id,
           source_line_id)
        VALUES
          (SYSDATE,
           l_user_id,
           SYSDATE,
           l_user_id,
           -1,
           mtl_material_transactions_s.NEXTVAL,
           l_transaction_mode,
           1, -- Yes
           l_transaction_type_id,
           l_transaction_source_id,
           l_organization_id,
           l_inventory_item_id,
           l_revision,
           rec.subinventory_code,
           rec.locator_id,
           nvl(rec.transaction_uom,l_primary_uom_code),
           rec.transaction_quantity,
           rec.transaction_cost,
           l_transaction_date,
           'CNY',
           l_source_code,
           l_source_header_id,
           l_source_line_id);
          
        UPDATE cux_transactions_interface
           SET status = 'C'
         WHERE ROWID = rec.row_id;
        
      ELSE
        UPDATE cux_transactions_interface
           SET status = 'E'
         WHERE ROWID = rec.row_id;
        --error;
      END IF;
    END LOOP;
    COMMIT;
    -- 提交请求
    /*
    ln_request_id := fnd_request.submit_request('INV',
                                                'INCTCM',
                                                '',
                                                SYSDATE,
                                                FALSE,
                                                chr(0));
    */
    COMMIT;
  END LOOP;
END;


三、通过临时表导入(慎用,当库存会计期关闭的情况下使用)

DECLARE
  l_organization_id       NUMBER;
  l_user_id               NUMBER := 1171; --User ID
  l_resp_id               NUMBER := 50194;
  l_resp_appl_id          NUMBER := 401;
  l_transaction_date      DATE := to_date('2008-10-31', 'YYYY-MM-DD');
  l_transaction_mode      NUMBER := 3; -- background
  l_transaction_type_name VARCHAR2(30) := 'Account alias issue';
  l_transaction_type_id   NUMBER; -- 'Account alias issue'
  l_transaction_action_id NUMBER;
  l_transaction_source    VARCHAR2(30) := '期初导入'; -- '期初导入'
  l_source_code           VARCHAR2(30) := 'Initialization Insert';
  l_transaction_source_id NUMBER;
  l_transaction_source_type_id  NUMBER;
  l_inventory_item_id     NUMBER;
  l_revision_control      NUMBER;
  l_revision              NUMBER;
  l_subinventory_code     NUMBER;
  l_secondary_locator     NUMBER;
  l_source_header_id      NUMBER := 0;
  l_source_line_id        NUMBER := 0;
  l_success               BOOLEAN;
  l_primary_uom_code      VARCHAR2(3);
  lv_err_msg              VARCHAR2(2000);
  ln_request_id           NUMBER;
  lb_return               BOOLEAN;
  l_acct_period_id        NUMBER;

  CURSOR cur_trx_h IS
    SELECT DISTINCT cti.batch_code
      FROM cux_transactions_interface cti
      WHERE status = 'P';

  CURSOR cur_trx(p_batch_code VARCHAR2) IS
    SELECT cti.ROWID row_id,
           cti.organization_code,
           cti.item_number,
           cti.subinventory_code,
           cti.locator_id,
           cti.transaction_uom,
           cti.transaction_quantity,
           cti.transaction_cost
      FROM cux_transactions_interface cti
     WHERE batch_code = p_batch_code
       AND status = 'P';
BEGIN
  fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);

  SELECT mtt.transaction_type_id,mtt.transaction_action_id
    INTO l_transaction_type_id,l_transaction_action_id
    FROM mtl_transaction_types mtt
   WHERE mtt.transaction_type_name = l_transaction_type_name;
  
   SELECT transaction_source_type_id
     INTO l_transaction_source_type_id
     FROM mtl_txn_source_types mtst
    WHERE mtst.transaction_source_type_name = 'Account alias';

  FOR rec_h IN cur_trx_h LOOP
    l_source_header_id := l_source_header_id + 1;
    l_source_line_id   := 0;
    FOR rec IN cur_trx(rec_h.batch_code) LOOP
      l_success        := TRUE;
      l_source_line_id := l_source_line_id + 1;
      BEGIN
        --  Organization_ID
        SELECT ood.organization_id
          INTO l_organization_id
          FROM org_organization_definitions ood
         WHERE ood.organization_code = rec.organization_code;
        
        -- Period_ID
      SELECT acct_period_id
        INTO l_acct_period_id
        FROM org_acct_periods oap
       WHERE oap.organization_id = l_organization_id
         AND oap.period_start_date =
             to_date(to_char(l_transaction_date, 'YYYY-MM') || '-01','YYYY-MM-DD');
            
        -- 来源
        SELECT mgd.disposition_id
          INTO l_transaction_source_id
          FROM mtl_generic_dispositions mgd
         WHERE mgd.organization_id = l_organization_id
           AND mgd.segment1 = l_transaction_source;
        -- 物料
        SELECT msi.inventory_item_id, msi.revision_qty_control_code,msi.primary_uom_code
          INTO l_inventory_item_id, l_revision_control,l_primary_uom_code
          FROM mtl_system_items_b msi
         WHERE msi.segment1 = rec.item_number
           AND msi.organization_id = l_organization_id;
         --版本
        IF l_revision_control = 2 THEN
          --1 不控制
          --2 控制
          SELECT MAX(x.revision)
            INTO l_revision
            FROM mtl_item_revisions x
           WHERE x.inventory_item_id = l_inventory_item_id
             AND x.organization_id = l_organization_id
             AND x.effectivity_date <= l_transaction_date;
        ELSE
          l_revision := NULL;
        END IF;  

      EXCEPTION
        WHEN OTHERS THEN
          l_success := FALSE;
      END;

   
      IF l_success THEN
 
        INSERT INTO mtl_material_transactions_temp
          (last_update_date,
           last_updated_by,
           creation_date,
           created_by,
           last_update_login,
           transaction_temp_id,   --
           transaction_mode,
           process_flag,
           transaction_type_id,
           transaction_source_id,
           transaction_action_id, --
           transaction_source_type_id,
           organization_id,
           inventory_item_id,
           revision,
           subinventory_code,
           locator_id,
           transaction_uom,
           transaction_quantity,
           primary_quantity, --
           transaction_cost,
           transaction_date,
           --currency_code,
           source_code,
         --  source_header_id,
           source_line_id,
           acct_period_id )
        VALUES
          (SYSDATE,
           l_user_id,
           SYSDATE,
           l_user_id,
           -1,
           mtl_material_transactions_s.NEXTVAL,
           l_transaction_mode,
           'Y', -- Yes
           l_transaction_type_id,
           l_transaction_source_id,
           l_transaction_action_id,
           l_transaction_source_type_id,
           l_organization_id,
           l_inventory_item_id,
           l_revision,
           rec.subinventory_code,
           rec.locator_id,
           nvl(rec.transaction_uom,l_primary_uom_code),
           rec.transaction_quantity,
           rec.transaction_quantity,
           NULL,                    -- rec.transaction_cost,
           l_transaction_date,
           --'CNY',
           l_source_code,
          --  l_source_header_id,
           l_source_line_id,
           l_acct_period_id);
          
        UPDATE cux_transactions_interface
           SET status = 'C'
         WHERE ROWID = rec.row_id;
        
      ELSE
        UPDATE cux_transactions_interface
           SET status = 'E'
         WHERE ROWID = rec.row_id;
        --error;
      END IF;
    END LOOP;
    COMMIT;
    -- 提交请求
    /*
    ln_request_id := fnd_request.submit_request('INV',
                                                'INCTCM',
                                                '',
                                                SYSDATE,
                                                FALSE,
                                                chr(0));
    */
    COMMIT;
  END LOOP;
END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/57020/viewspace-520845/,如需转载,请注明出处,否则将追究法律责任。

上一篇: APPWORX安装指南
请登录后发表评论 登录
全部评论

注册时间:2008-07-29

  • 博文量
    20
  • 访问量
    175668