物料导入 Inventory Item Migration

来源：互联网发布：centos安装oracle 编辑：程序博客网时间：2024/06/05 22:07

--INV Items

--Concurrent programs:Import Items N: INV/Items/Import/Import Items

SELECT *
FROM mtl_system_items_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT *
FROM mtl_item_revisions_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT *
FROM mtl_interface_errors
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

--Delete
DELETE FROM mtl_system_items_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

DELETE FROM mtl_item_revisions_interface
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

DELETE FROM mtl_interface_errors
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

--******************************************************************************
SELECT *
FROM mtl_system_items_b
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT *
FROM mtl_system_items_tl
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT *
FROM mtl_item_revisions_b
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

SELECT *
FROM mtl_item_revisions_tl
WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

--******************************************************************************
--Table Relation:
/*mtl_system_items_interface.organization_code = mtl_item_revisions_interface.organization_code
AND
mtl_system_items_interface.item_number = mtl_item_revisions_interface.item_number
mtl_interface_errors*/
SELECT mie.error_message
FROM mtl_system_items_interface mii, mtl_interface_errors mie
WHERE mii.transaction_id = mie.transaction_id;

--******************************************************************************
--Use templete creat item

DECLARE
   l_iface_rec        inv.mtl_system_items_interface%ROWTYPE;
   l_user_id          NUMBER                                   := 1318;
   --User ID, Sysadmin here
   l_mst_mfg_org_id   NUMBER                                   := 204;
   --Master Inv Organization
   l_cur_mfg_org_id   NUMBER                                   := 207;
--Current Inv Organization
BEGIN
   l_iface_rec.last_update_date := SYSDATE;
   l_iface_rec.last_updated_by := l_user_id;
   l_iface_rec.creation_date := SYSDATE;
   l_iface_rec.created_by := l_user_id;
   l_iface_rec.last_update_login := -1;
   l_iface_rec.process_flag := 1;
   l_iface_rec.transaction_type := 'CREATE';
   l_iface_rec.set_process_id := 987654321;
   l_iface_rec.segment1 := 'MON_TEST_ITEM_0001';
   l_iface_rec.primary_uom_code := 'Ea';
   l_iface_rec.description := 'For Interface Test';
   l_iface_rec.template_id := 259;
                                --Purchased Item?table inv.mtl_item_templates
   --Master Inv Organization
   l_iface_rec.organization_id := l_mst_mfg_org_id;

INSERT INTO mtl_system_items_interface
VALUES l_iface_rec;

--Current Inv Organization
l_iface_rec.organization_id := l_cur_mfg_org_id;

INSERT INTO inv.mtl_system_items_interface
VALUES l_iface_rec;

COMMIT;
END;

--******************************************************************************
SELECT *
FROM inv.mtl_item_templates mit
WHERE mit.template_name LIKE 'Purchased%';

--******************************************************************************
SELECT mp.*
FROM inv.mtl_parameters mp
WHERE mp.organization_id = 84;
--******************************************************************************
--Assign organization

SELECT *
FROM mtl_parameters mp
WHERE mp.organization_id = 84;

DECLARE
   l_iface_rec        inv.mtl_system_items_interface%ROWTYPE;
   l_user_id          NUMBER                                   := 1318;
   --User ID, Sysadmin here
   l_cur_mfg_org_id   NUMBER                                   := 208;
--Current Inv Organization
BEGIN
   l_iface_rec.last_update_date := SYSDATE;
   l_iface_rec.last_updated_by := l_user_id;
   l_iface_rec.creation_date := SYSDATE;
   l_iface_rec.created_by := l_user_id;
   l_iface_rec.last_update_login := -1;
   l_iface_rec.process_flag := 1;
   l_iface_rec.transaction_type := 'CREATE';
   l_iface_rec.set_process_id := 987654321;
   l_iface_rec.organization_id := l_cur_mfg_org_id;
   l_iface_rec.segment1 := 'MON_TEST_ITEM_0001';

INSERT INTO inv.mtl_system_items_interface
VALUES l_iface_rec;

COMMIT;
END;

--******************************************************************************
--Use API Create item templete

DECLARE
   l_item_rec         inv_item_grp.item_rec_type;
   x_item_rec         inv_item_grp.item_rec_type;
   x_error_tbl        inv_item_grp.error_tbl_type;
   l_template_id      NUMBER                      := 259;
   --Purchased Item£table inv.mtl_item_templates
   x_return_status    VARCHAR2 (1);
   l_user_id          NUMBER                      := 1318;
   --User ID, Sysadmin here
   l_mst_mfg_org_id   NUMBER                      := 204;
   --Master Inv Organization
   l_cur_mfg_org_id   NUMBER                      := 207;
--Current Inv Organization
BEGIN
   --Initialize first, or create_by will be -1
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => 50583,
                               resp_appl_id      => 401
                              );
   --Item
   l_item_rec.item_number := 'MON_TEST_ITEM_0002';
   l_item_rec.description := 'For API Test';
   l_item_rec.primary_uom_code := 'Ea';
   --Master Inv Organization first, then Current Inv Organization
   --l_item_rec.organization_id := l_mst_mfg_org_id;
   l_item_rec.organization_id := l_cur_mfg_org_id;
   --API
   inv_item_grp.create_item (p_commit             => fnd_api.g_false,
                             p_item_rec           => l_item_rec,
                             x_item_rec           => x_item_rec,
                             x_return_status      => x_return_status,
                             x_error_tbl          => x_error_tbl,
                             p_template_id        => l_template_id
                            );

   --Result
   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      ROLLBACK;

      FOR i IN 1 .. x_error_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line (   'Transaction ID   :'
                               || x_error_tbl (i).transaction_id
                              );
         DBMS_OUTPUT.put_line (   'Unique ID           :'
                               || x_error_tbl (i).unique_id
                              );
         DBMS_OUTPUT.put_line (   'Message Name :'
                               || x_error_tbl (i).message_name
                              );
         DBMS_OUTPUT.put_line (   'Message Text:     :'
                               || x_error_tbl (i).MESSAGE_TEXT
                              );
         DBMS_OUTPUT.put_line (   'Table Name        :'
                               || x_error_tbl (i).table_name
                              );
         DBMS_OUTPUT.put_line ('Column Name   :'
                               || x_error_tbl (i).column_name
                              );
         DBMS_OUTPUT.put_line (   'Organization ID :'
                               || x_error_tbl (i).organization_id
                              );
      END LOOP;

      IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
      THEN
         RAISE fnd_api.g_exc_unexpected_error;
      ELSIF (x_return_status = fnd_api.g_ret_sts_error)
      THEN
         RAISE fnd_api.g_exc_error;
      END IF;
   ELSE
      COMMIT;
      DBMS_OUTPUT.put_line (x_item_rec.inventory_item_id);
   END IF;

COMMIT;
END;

--******************************************************************************
--use API Update Item flag

DECLARE
   l_item_rec         inv_item_grp.item_rec_type;
   x_item_rec         inv_item_grp.item_rec_type;
   x_error_tbl        inv_item_grp.error_tbl_type;
   x_return_status    VARCHAR2 (1);
   l_user_id          NUMBER                      := 1318;
   --User ID, Sysadmin here
   l_cur_mfg_org_id   NUMBER                      := 208;
--Current Inv Organization
BEGIN
   --Initialize first, or create_by will be -1
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => 50583,
                               resp_appl_id      => 401
                              );
   --Item
   l_item_rec.item_number := '100E033L';
   l_item_rec.inventory_item_status_code := 'Inactive';      --Active Inactive
   --Master Inv Organization first, then Current Inv Organization
   l_item_rec.organization_id := l_cur_mfg_org_id;
   --API
   inv_item_grp.update_item (p_commit             => fnd_api.g_false,
                             p_item_rec           => l_item_rec,
                             x_item_rec           => x_item_rec,
                             x_return_status      => x_return_status,
                             x_error_tbl          => x_error_tbl
                            );

   --Result
   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      ROLLBACK;