物料类别导入 Inventory Item Categories Migration

--INV Item Categories

--Concurrent programs:N: INV/Items/Import/Item Category Assignment Open Interface

SELECT *
  FROM mtl_item_categories_interface mici
 WHERE TRUNC (creation_date) = TRUNC (SYSDATE);

--Delete

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

--******************************************************************************

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

--******************************************************************************

DECLARE
   l_iface_rec        mtl_item_categories_interface%ROWTYPE;
   l_cur_mfg_org_id   NUMBER                                  := 204;
   --Current Inv Organization
   l_user_id          NUMBER                                  := 1318;
--User ID, Sysadmin here
BEGIN
   l_iface_rec.last_updated_by := l_user_id;
   l_iface_rec.last_update_date := SYSDATE;
   l_iface_rec.created_by := l_user_id;
   l_iface_rec.creation_date := SYSDATE;
   l_iface_rec.set_process_id := 987654321;
   l_iface_rec.transaction_type := 'CREATE';
   l_iface_rec.process_flag := 1;
   l_iface_rec.organization_id := l_cur_mfg_org_id;
   l_iface_rec.inventory_item_id := 20830;
   l_iface_rec.category_set_id := 90;
   l_iface_rec.category_id := 1447;

   INSERT INTO mtl_item_categories_interface
        VALUES l_iface_rec;

   COMMIT;
END;

--******************************************************************************

DECLARE
   x_return_status    VARCHAR2 (1);
   x_msg_count        NUMBER;
   x_msg_data         VARCHAR2 (2000);
   x_errorcode        VARCHAR2 (30);
   l_cur_mfg_org_id   NUMBER          := 204;       --Current Inv Organization
   l_user_id          NUMBER          := 1318;          --User ID, Sysadmin here
BEGIN
   --Initialize first, or create_by will be -1
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => 401,
                               resp_appl_id      => 50583
                              );
   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          => x_return_status,
                                         x_errorcode              => x_errorcode,
                                         x_msg_count              => x_msg_count,
                                         x_msg_data               => x_msg_data,
                                         p_category_id            => 1447,
                                         p_category_set_id        => 90,
                                                                 --1 Inventory
                                         p_inventory_item_id      => 20833,
                                         p_organization_id        => l_cur_mfg_org_id
                                        );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line ('error code  : ' || x_errorcode);
      fnd_msg_pub.count_and_get (p_count      => x_msg_count,
                                 p_data       => x_msg_data);
      DBMS_OUTPUT.put_line ('error count  : ' || TO_CHAR (x_msg_count));
      DBMS_OUTPUT.put_line (REPLACE (x_msg_data, CHR (0), ' '));

      FOR i IN 2 .. x_msg_count
      LOOP
         x_msg_data := fnd_msg_pub.get;
         DBMS_OUTPUT.put_line (REPLACE (x_msg_data, CHR (0), ' '));
      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 ('Successfully.');
   END IF;
   COMMIT;
END;