Create item

inv_item_grpDECLARE
  PROCEDURE p_create_item(p_detail VARCHAR2,
                          p_code   VARCHAR2,
                          p_id     NUMBER) IS
    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 := 2; --Purchased Item table inv.mtl_item_templates
    x_return_status  VARCHAR2(1);
    l_user_id        NUMBER := 0; --User ID, Sysadmin here
    l_mst_mfg_org_id NUMBER := 85; --Master Inv Organization
    l_cur_mfg_org_id NUMBER := 3498; --Current Inv Organization
  BEGIN
    --Initialize first, or create_by will be -1
    fnd_global.apps_initialize(user_id      => l_user_id,
                               resp_id      => 20420,
                               resp_appl_id => 1);
    --Item
    l_item_rec.item_number                 := upper(p_detail);
    l_item_rec.description                 := p_detail;
    l_item_rec.item_type                   := 'PSI';
    l_item_rec.primary_uom_code            := 'EA';
    l_item_rec.attribute_category          := 3138;
    l_item_rec.attribute5                  := 'Y';
    l_item_rec.attribute7                  := p_code;
    l_item_rec.attribute12                 := 1;
    l_item_rec.customer_order_flag         := 'Y';
    l_item_rec.customer_order_enabled_flag := 'Y';
    l_item_rec.shippable_item_flag         := 'Y';
    l_item_rec.so_transactions_flag        := 'Y';
    --Master Inv Organization first, then Current Inv Organization
    l_item_rec.organization_id := l_mst_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   => 541);
    --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;
    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   => 541);
    --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;
    l_item_rec.organization_id := 2547;
    --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   => 541);
    --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);
      UPDATE ps_services
      SET    om_item_id = x_item_rec.inventory_item_id
      WHERE  service_id = p_id;
      COMMIT;
    END IF;
  END;

BEGIN
  FOR f IN (SELECT service_details,
                   service_code,
                   service_id
            FROM   ps_services
            WHERE  service_id > 862
            AND    om_item_id IS NULL) LOOP
    p_create_item(f.service_details,
                  f.service_code,
                  f.service_id);
  END LOOP;
END;

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

转载于:http://blog.itpub.net/269025/viewspace-1480702/