杂项入库和出库导入

118 阅读 0 评论 78 点赞

我是靠谱客的博主妩媚洋葱，最近开发中收集的这篇文章主要介绍杂项入库和出库导入，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、首先创建一个临时表

-- 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';

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;