API-INV-物料更新-默认接收子库存

摘自：INV*物料接收子库存更新 - 旺仔丶小馒头 - 博客园 (cnblogs.com)

INV*物料接收子库存更新

DECLARE

      x_return_status VARCHAR2(30);

      l_error_tbl     inv_item_grp.error_tbl_type;

      CURSOR cur_item IS

                 SELECT        t.organization_name,

                               t.item_number,

                               t.item_desc,

                               t.receiving_routing,

                               t.attribute1,

                               t.attribute2,

                               t.attribute3,

                               t.attribute4,

                               t.attribute5,

                               t.attribute6,

                               t.attribute7,

                               t.attribute8,

                               t.attribute9,

                               t.attribute10,

                               t.attribute11,

                               t.attribute12,

                               t.attribute13,

                               t.attribute14,

                               t.attribute15

            FROM   cux_item_update_temp t

            WHERE  1 = 1;

      l_organization_id      NUMBER;

      l_inventory_item_id    NUMBER;

      l_error_msg            VARCHAR2(32767);

      l_receiving_routing_id NUMBER;

      l_return_status VARCHAR2(240);

      l_msg_count     NUMBER;

      x_msg_data      VARCHAR2(240);

BEGIN

      FOR rec IN cur_item

      LOOP

      

            --组织

            l_organization_id := NULL;

            BEGIN

                  SELECTood.organization_id

                  INTO   l_organization_id

                  FROM   org_organization_definitions ood

                  WHERE  1 = 1

                  ANDood.organization_name=rec.organization_name;

            EXCEPTION

                  WHEN OTHERS THEN

                        l_organization_id := NULL;

            END;

            IF (l_organization_id IS NULL) THEN

                  l_error_msg := l_error_msg || '组织' ||rec.organization_name|| '不存在！';

            END IF;

      

            --物料

            l_inventory_item_id := NULL;

            BEGIN

                  SELECT msi.inventory_item_id

                  INTO   l_inventory_item_id

                  FROM   mtl_system_items_b msi

                  WHERE  1 = 1

                  ANDmsi.organization_id= l_organization_id

                  AND    msi.segment1 = rec.item_number;

            EXCEPTION

                  WHEN OTHERS THEN

                        l_inventory_item_id := NULL;

            END;

            IF (l_inventory_item_id IS NULL) THEN

                  l_error_msg := l_error_msg || '物料' || rec.item_number || '不存在！';

            END IF;

      

            --导入

            IF (l_error_msg IS NOT NULL) THEN

                  dbms_output.put_line(rec.item_number || ' 出错：' || l_error_msg);

            ELSE

                  inv_item_sub_default_pkg.insert_upd_item_sub_defaults(p_organization_id   => l_organization_id,

                                                                                                                                                                                    p_inventory_item_id => l_inventory_item_id,

                                                                                                                                                                                    p_subinventory_code => rec.receiving_routing,

                                                                                                                                                                                    p_default_type      => 2, --1. default shipping subinventory; 2. default receiving subinventory

                                                                                                                                                                                    p_creation_date     => SYSDATE,

                                                                                                                                                                                    p_created_by        => -1,

                                                                                                                                                                                    p_last_update_date  => SYSDATE,

                                                                                                                                                                                    p_last_updated_by   => -1,

                                                                                                                                                                                    p_process_code      => 'INSERT', --INSERT/UPDATE

                                                                                                                                                                                    p_commit            => fnd_api.g_true,

                                                                                                                                                                                    x_return_status     => l_return_status,

                                                                                                                                                                                    x_msg_count         => l_msg_count,

                                                                                                                                                                                    x_msg_data          => x_msg_data);

            

                  IF x_return_status <> fnd_api.g_ret_sts_success THEN

                  

                        FOR i IN 1 .. l_error_tbl.last

                        LOOP

                              l_error_msg := substrb(l_error_msg || l_error_tbl(i).column_name || ':' || l_error_tbl(i)

                                                                                                 .message_text || '#',

                                                                                                 1,

                                                                                                 200);

                        END LOOP;

                        dbms_output.put_line(rec.item_number || ' 出错：' || l_error_msg);

                  END IF;

            

            END IF;

      

      END LOOP; --FOR rec_item IN cur_item LOOP

EXCEPTION

      WHEN OTHERS THEN

            dbms_output.put_line('Exception');

END;