1 PROCEDURE update_item(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,  2                         x_return_status OUT NOCOPY VARCHAR2,  3                         x_msg_count     OUT NOCOPY NUMBER,  4                         x_msg_data      OUT NOCOPY VARCHAR2) IS  5     6     l_api_name CONSTANT VARCHAR2(30) := ‘UPDATE_ITEM‘;  7     l_savepoint_name VARCHAR2(30) := ‘UPDATE_ITEM‘;  8     l_item_rec1      inv_item_grp.item_rec_type;  9     x_item_rec1      inv_item_grp.item_rec_type; 10     l_item_rec       inv_item_grp.item_rec_type; 11     x_item_rec       inv_item_grp.item_rec_type; 12     x_error_tbl      inv_item_grp.error_tbl_type; 13     -- x_return_status     VARCHAR2(10); 14     l_user_id           NUMBER := 0; --User ID, Sysadmin here 15     l_message           VARCHAR2(500); 16     x_message           VARCHAR2(500); 17     l_buyer_id          NUMBER; 18     l_old_category_id   NUMBER; 19     l_category_id       NUMBER; 20     l_category_set_id   NUMBER; 21     l_category_set_name VARCHAR2(240) := ‘库存‘; 22     --l_item_id           NUMBER; 23     --l_organization_id   NUMBER; 24     l_errorcode    VARCHAR2(100); 25     l_msg_count    NUMBER; 26     l_structure_id NUMBER; 27    28     l_chart_of_account_id NUMBER; 29     l_ccid1               NUMBER; 30     l_ccid2               NUMBER; 31     l_ccid3               NUMBER; 32     l_ccid4               NUMBER; 33     l_default_org         NUMBER; 34     l_organization_id     NUMBER; 35     l_item_id             NUMBER; 36     l_organization_id1    NUMBER; 37     l_item_id1            NUMBER; 38    39     l_err_count NUMBER; 40    41     --更新类别信息 42     l_category_rec inv_item_category_pub.category_rec_type; 43   BEGIN 44    45     -- and initialize message list, include debug message hint to enter api 46     x_return_status := hss_api.start_activity(p_pkg_name      => g_pkg_name, 47                                               p_api_name      => l_api_name, 48                                               p_init_msg_list => p_init_msg_list); 49     -- check if activity started successfully 50     IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN 51       RAISE fnd_api.g_exc_unexpected_error; 52     ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN 53       RAISE fnd_api.g_exc_error; 54     END IF; 55    56     -- hss_log.debug(‘测试=======‘);   57     BEGIN 58       SELECT mcs.category_set_id, 59              mcs.structure_id 60         INTO l_category_set_id, 61              l_structure_id 62         FROM mtl_category_sets mcs 63        WHERE mcs.category_set_name = l_category_set_name; 64     EXCEPTION 65       WHEN OTHERS THEN 66         --dbms_output.put_line(‘类别集   :‘ || l_category_set_name || ‘未定义‘); 67         RETURN; 68     END; 69    70     l_err_count := 0; 71     FOR c IN (SELECT * 72                 FROM cux.cux_mtl_update_prperty ccp 73                WHERE nvl(ccp.return_stastus, 74                          ‘N‘) <> ‘S‘ 75                ORDER BY ccp.item_code) 76     LOOP 77       SAVEPOINT l_savepoint_name; --设置保存点 78      79       hss_conc_utl.log_msg(‘物料编码：‘ || c.item_code); 80      81       l_message := NULL; 82       --============================================================================= 83       /*当需要更新物料说明和默认发运组织时，需先更新主组织的物料说明和默认发运组织*/ 84       --============================================================================= 85       IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN 86        87         l_item_rec1.organization_code := ‘MST‘; 88         l_item_rec1.item_number       := c.item_code; 89        90         --IF (c.desc_mir IS NOT NULL) THEN 91         --  l_item_rec1.description := c.desc_mir; 92         --ELSE 93         --  l_item_rec1.description := NULL; 94         ---END IF; 95        96         IF c.item_desc IS NOT NULL THEN 97           l_item_rec1.long_description := c.item_desc; 98         ELSE 99           l_item_rec1.long_description := NULL;100         END IF;101       102         IF (c.default_shipping_org IS NOT NULL) THEN103           BEGIN104             SELECT oog.organization_id105               INTO l_default_org106               FROM org_organization_definitions oog107              WHERE oog.organization_code = c.default_shipping_org;108           EXCEPTION109             WHEN OTHERS THEN110               l_default_org := NULL;111           END;112         113           l_item_rec1.default_shipping_org := l_default_org;114         ELSE115           l_item_rec1.default_shipping_org := NULL;116         END IF;117       118         --API119         ----更新物料信息-----120         inv_item_grp.update_item(p_commit        => fnd_api.g_false,121                                  p_item_rec      => l_item_rec1,122                                  x_item_rec      => x_item_rec1,123                                  x_return_status => x_return_status,124                                  x_error_tbl     => x_error_tbl);125         IF x_return_status <> fnd_api.g_ret_sts_success THEN126           -- l_message := NULL;127           FOR i IN 1 .. x_error_tbl.count128           LOOP129             l_message := l_message || x_error_tbl(i).message_name;130           END LOOP;131           x_error_tbl.delete;132           --ELSE133           --  l_message := NULL;134         END IF;135       END IF; --IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN136     137       --=============================================================================138       --更新子组织信息139       --============================================================================140       --Master Inv Organization first, then Current Inv Organization141       l_item_rec.organization_code := c.organization_code;142       l_item_rec.item_number       := c.item_code;143     144       IF c.buyer IS NOT NULL THEN145         BEGIN146           SELECT he.employee_id147             INTO l_buyer_id148             FROM hr_employees he149            WHERE he.full_name = c.buyer;150         EXCEPTION151           WHEN OTHERS THEN152             l_buyer_id := NULL;153         END;154         IF l_buyer_id IS NOT NULL THEN155           l_item_rec.buyer_id := l_buyer_id;156         END IF;157       ELSE158         l_item_rec.buyer_id := NULL;159       END IF;160       IF c.planer IS NOT NULL THEN161         l_item_rec.planner_code := c.planer;162       ELSE163         l_item_rec.planner_code := NULL;164       END IF;165       IF c.attribute1 IS NOT NULL THEN166         l_item_rec.attribute1 := c.attribute1;167       ELSE168         l_item_rec.attribute1 := NULL;169       END IF;170       IF c.attribute2 IS NOT NULL THEN171         l_item_rec.attribute2 := c.attribute2;172       ELSE173         l_item_rec.attribute2 := NULL;174       END IF;175       IF c.attribute3 IS NOT NULL THEN176         l_item_rec.attribute3 := c.attribute3;177       ELSE178         l_item_rec.attribute3 := NULL;179       END IF;180       IF c.attribute4 IS NOT NULL THEN181         l_item_rec.attribute4 := c.attribute4;182       ELSE183         l_item_rec.attribute4 := NULL;184       END IF;185       IF c.attribute5 IS NOT NULL THEN186         l_item_rec.attribute5 := c.attribute5;187       ELSE188         l_item_rec.attribute5 := NULL;189       END IF;190       IF c.attribute6 IS NOT NULL THEN191         l_item_rec.attribute6 := c.attribute6;192       ELSE193         l_item_rec.attribute6 := NULL;194       END IF;195       IF c.attribute7 IS NOT NULL THEN196         l_item_rec.attribute7 := c.attribute7;197       ELSE198         l_item_rec.attribute7 := NULL;199       END IF;200       IF c.attribute8 IS NOT NULL THEN201         l_item_rec.attribute8 := c.attribute8;202       ELSE203         l_item_rec.attribute8 := NULL;204       END IF;205       IF c.attribute9 IS NOT NULL THEN206         l_item_rec.attribute9 := c.attribute9;207       ELSE208         l_item_rec.attribute9 := NULL;209       END IF;210       IF c.attribute10 IS NOT NULL THEN211         l_item_rec.attribute10 := c.attribute10;212       ELSE213         l_item_rec.attribute10 := NULL;214       END IF;215       IF c.attribute11 IS NOT NULL THEN216         l_item_rec.attribute11 := c.attribute11;217       ELSE218         l_item_rec.attribute11 := NULL;219       END IF;220       IF c.attribute12 IS NOT NULL THEN221         l_item_rec.attribute12 := c.attribute12;222       ELSE223         l_item_rec.attribute12 := NULL;224       END IF;225       IF c.attribute13 IS NOT NULL THEN226         l_item_rec.attribute13 := c.attribute13;227       ELSE228         l_item_rec.attribute13 := NULL;229       END IF;230       IF c.attribute14 IS NOT NULL THEN231         l_item_rec.attribute14 := c.attribute14;232       ELSE233         l_item_rec.attribute14 := NULL;234       END IF;235       IF c.attribute15 IS NOT NULL THEN236         l_item_rec.attribute15 := c.attribute15;237       ELSE238         l_item_rec.attribute15 := NULL;239       END IF;240       IF c.attribute16 IS NOT NULL THEN241         l_item_rec.attribute16 := c.attribute16;242       ELSE243         l_item_rec.attribute16 := NULL;244       END IF;245       IF c.attribute17 IS NOT NULL THEN246         l_item_rec.attribute17 := c.attribute17;247       ELSE248         l_item_rec.attribute17 := NULL;249       END IF;250       IF c.attribute18 IS NOT NULL THEN251         l_item_rec.attribute18 := c.attribute18;252       ELSE253         l_item_rec.attribute18 := NULL;254       END IF;255       IF c.attribute19 IS NOT NULL THEN256         l_item_rec.attribute19 := c.attribute19;257       ELSE258         l_item_rec.attribute19 := NULL;259       END IF;260       IF c.attribute20 IS NOT NULL THEN261         l_item_rec.attribute20 := c.attribute20;262       ELSE263         l_item_rec.attribute20 := NULL;264       END IF;265     266       IF (c.supply_subinventory IS NOT NULL) THEN267         l_item_rec.wip_supply_subinventory := c.supply_subinventory;268       ELSE269         l_item_rec.wip_supply_subinventory := NULL;270       END IF;271     272       IF (c.min_quantity IS NOT NULL) THEN273         l_item_rec.min_minmax_quantity := c.min_quantity;274       ELSE275         l_item_rec.min_minmax_quantity := NULL;276       END IF;277       IF (c.max_quantity IS NOT NULL) THEN278         l_item_rec.max_minmax_quantity := c.max_quantity;279       ELSE280         l_item_rec.max_minmax_quantity := NULL;281       END IF;282       IF (c.min_order_quantity IS NOT NULL) THEN283         l_item_rec.minimum_order_quantity := c.min_order_quantity;284       ELSE285         l_item_rec.minimum_order_quantity := NULL;286       END IF;287       IF (c.max_order_quantity IS NOT NULL) THEN288         l_item_rec.maximum_order_quantity := c.max_order_quantity;289       ELSE290         l_item_rec.maximum_order_quantity := NULL;291       END IF;292       IF (c.fixed_order_quantity IS NOT NULL) THEN293         l_item_rec.fixed_order_quantity := c.fixed_order_quantity;294       ELSE295         l_item_rec.fixed_order_quantity := NULL;296       END IF;297       IF (c.fixed_days_supply IS NOT NULL) THEN298         l_item_rec.fixed_days_supply := c.fixed_days_supply;299       ELSE300         l_item_rec.fixed_days_supply := NULL;301       END IF;302       IF (c.fixed_lot_multiplier IS NOT NULL) THEN303         l_item_rec.fixed_lot_multiplier := c.fixed_lot_multiplier;304       ELSE305         l_item_rec.fixed_lot_multiplier := NULL;306       END IF;307     308       BEGIN309         SELECT gsob.chart_of_accounts_id310           INTO l_chart_of_account_id311           FROM gl_sets_of_books             gsob,312                hr_operating_units           hou,313                org_organization_definitions ood314          WHERE gsob.set_of_books_id = hou.set_of_books_id315            AND hou.organization_id = ood.operating_unit316            AND ood.organization_code = c.organization_code;317       EXCEPTION318         WHEN OTHERS THEN319           hss_conc_utl.log_msg(‘chart_of_account_id 没有找到‘);320         321           l_message := l_message || ‘组织对应chart_of_account_id 没有找到==‘;322       END;323     324       -----------------------325     326       IF (c.encumbrance_account IS NOT NULL) THEN327         l_ccid1                        := fnd_flex_ext.get_ccid(application_short_name => ‘SQLGL‘,328                                                                 key_flex_code          => ‘GL#‘,329                                                                 structure_number       => l_chart_of_account_id,330                                                                 validation_date        => to_char(SYSDATE,331                                                                                                   ‘DD-MON-YYYY‘),332                                                                 concatenated_segments  => c.encumbrance_account);333         l_item_rec.encumbrance_account := l_ccid1;334       335         IF (l_ccid1 IS NULL) THEN336           hss_conc_utl.log_msg(‘更新保留款账户异常！‘);337           l_message := l_message || ‘保留款账户异常==‘;338         END IF;339       ELSE340         l_item_rec.encumbrance_account := NULL;341       END IF;342       IF (c.expense_account IS NOT NULL) THEN343         l_ccid2                    := fnd_flex_ext.get_ccid(application_short_name => ‘SQLGL‘,344                                                             key_flex_code          => ‘GL#‘,345                                                             structure_number       => l_chart_of_account_id,346                                                             validation_date        => to_char(SYSDATE,347                                                                                               ‘DD-MON-YYYY‘),348                                                             concatenated_segments  => c.expense_account);349         l_item_rec.expense_account := l_ccid2;350       351         IF (l_ccid1 IS NULL) THEN352           hss_conc_utl.log_msg(‘更新费用账户异常！‘);353           l_message := l_message || ‘费用账户异常==‘;354         END IF;355       ELSE356         l_item_rec.expense_account := NULL;357       END IF;358     359       --l_item_rec.sales_account := NULL;360       IF (c.sales_account IS NOT NULL) THEN361         l_ccid3                  := fnd_flex_ext.get_ccid(application_short_name => ‘SQLGL‘,362                                                           key_flex_code          => ‘GL#‘,363                                                           structure_number       => l_chart_of_account_id,364                                                           validation_date        => to_char(SYSDATE,365                                                                                             ‘DD-MON-YYYY‘),366                                                           concatenated_segments  => c.sales_account);367         l_item_rec.sales_account := l_ccid3;368       369         IF (l_ccid1 IS NULL) THEN370           hss_conc_utl.log_msg(‘更新销售账户异常！‘);371           l_message := l_message || ‘销售账户异常==‘;372         END IF;373       ELSE374         hss_conc_utl.log_msg(‘物料‘ || c.item_code || ‘下的销售账户不能为空‘);375         l_message := l_message || ‘物料‘ || c.item_code || ‘下的销售账户不能为空==‘;376         --  l_item_rec.sales_account := NULL;377       END IF;378     379       --l_item_rec.cost_of_sales_account := NULL;380       IF (c.cost_of_sales_account IS NOT NULL) THEN381         l_ccid4                          := fnd_flex_ext.get_ccid(application_short_name => ‘SQLGL‘,382                                                                   key_flex_code          => ‘GL#‘,383                                                                   structure_number       => l_chart_of_account_id,384                                                                   validation_date        => to_char(SYSDATE,385                                                                                                     ‘DD-MON-YYYY‘),386                                                                   concatenated_segments  => c.cost_of_sales_account);387         l_item_rec.cost_of_sales_account := l_ccid4;388       389         IF (l_ccid1 IS NULL) THEN390           hss_conc_utl.log_msg(‘更新销售成本账户异常！‘);391           l_message := l_message || ‘销售成本账户异常==‘;392         END IF;393       ELSE394         hss_conc_utl.log_msg(‘物料‘ || c.item_code || ‘下的销售成本账户不能为空‘);395         l_message := l_message || ‘物料‘ || c.item_code || ‘下的销售成本账户不能为空==‘;396         --  l_item_rec.cost_of_sales_account := NULL;397       END IF;398       --API399       ----更新物料信息-----400       inv_item_grp.update_item(p_commit        => fnd_api.g_false,401                                p_item_rec      => l_item_rec,402                                x_item_rec      => x_item_rec,403                                x_return_status => x_return_status,404                                x_error_tbl     => x_error_tbl);405     406       --Result407       IF x_return_status <> fnd_api.g_ret_sts_success THEN408         --ROLLBACK;409         --l_message := NULL;410         FOR i IN 1 .. x_error_tbl.count411         LOOP412           l_message := l_message || x_error_tbl(i).message_name;413         END LOOP;414         x_error_tbl.delete;415         --ELSE416         -- l_message := NULL;417         -- COMMIT;418       END IF;419     420       IF x_return_status = fnd_api.g_ret_sts_success THEN421         IF c.inv_category_contractsegment IS NOT NULL THEN422           BEGIN423             --获取物料类别ID--------424             SELECT mcv.category_id425               INTO l_category_id426               FROM mtl_categories_v mcv427              WHERE 1 = 1428                AND mcv.structure_id = l_structure_id429                AND mcv.category_concat_segs = c.inv_category_contractsegment430                AND (mcv.disable_date IS NULL OR mcv.disable_date > SYSDATE);431           EXCEPTION432             WHEN OTHERS THEN433               l_category_id := NULL;434           END;435         436           IF l_category_id IS NULL THEN437             x_return_status := ‘E‘;438             l_message       := l_message || ‘物料类别 :‘ || c.inv_category_contractsegment || ‘无效‘;439           ELSE440           441             ----获取原来类别id442             BEGIN443               SELECT mic.category_id444                 INTO l_old_category_id445                 FROM mtl_system_items_b  msi,446                      mtl_item_categories mic447                WHERE 1 = 1448                  AND msi.inventory_item_id = mic.inventory_item_id449                  AND msi.organization_id = mic.organization_id450                  AND msi.inventory_item_id = x_item_rec.inventory_item_id451                  AND msi.organization_id = x_item_rec.organization_id452                  AND mic.category_set_id = l_category_set_id;453             EXCEPTION454               WHEN OTHERS THEN455                 l_old_category_id := NULL;456             END;457           458             --- inv_item_category_pub.Create_Category_Assignment459             IF l_old_category_id IS NOT NULL460                AND l_old_category_id <> l_category_id THEN461               ---更新物料类别分配462               inv_item_category_pub.update_category_assignment(p_api_version       => ‘1.0‘,463                                                                p_init_msg_list     => fnd_api.g_true,464                                                                p_commit            => fnd_api.g_false,465                                                                p_category_id       => l_category_id,466                                                                p_old_category_id   => l_old_category_id,467                                                                p_category_set_id   => l_category_set_id,468                                                                p_inventory_item_id => x_item_rec.inventory_item_id,469                                                                p_organization_id   => x_item_rec.organization_id,470                                                                x_return_status     => x_return_status,471                                                                x_errorcode         => l_errorcode,472                                                                x_msg_count         => l_msg_count,473                                                                x_msg_data          => x_message);474             475               IF x_return_status <> fnd_api.g_ret_sts_success THEN476                 /*FOR i IN 2 .. l_msg_count LOOP477                   l_message := fnd_msg_pub.get;478                   \*dbms_output.put_line(REPLACE(l_message,479                                                chr(0),480                                                ‘ ‘));*481                 END LOOP;*/482                 /*dbms_output.put_line(‘update_category_assignment:‘ ||483                 x_return_status || l_message);*/484                 hss_conc_utl.log_msg(‘l_category_id:=‘ || l_category_id);485                 hss_conc_utl.log_msg(‘l_category_set_id:=‘ || l_category_set_id);486               487                 l_message := l_message || ‘分配物料类别报错：‘ || x_message;488               ELSE489                 /*dbms_output.put_line(‘update_category_assignment:‘ ||490                 x_return_status || l_message);*/491                 NULL;492               END IF;493             ELSIF l_old_category_id IS NULL THEN494               inv_item_category_pub.create_category_assignment(p_api_version       => ‘1.0‘,495                                                                p_init_msg_list     => fnd_api.g_true,496                                                                p_commit            => fnd_api.g_false,497                                                                x_return_status     => x_return_status,498                                                                x_errorcode         => l_errorcode,499                                                                x_msg_count         => l_msg_count,500                                                                x_msg_data          => x_message,501                                                                p_category_id       => l_category_id,502                                                                p_category_set_id   => l_category_set_id,503                                                                p_inventory_item_id => x_item_rec.inventory_item_id,504                                                                p_organization_id   => x_item_rec.organization_id);505               IF x_return_status <> fnd_api.g_ret_sts_success THEN506                 /*dbms_output.put_line(‘create_category_assignment:‘ ||507                 x_return_status || l_message);*/508                 hss_conc_utl.log_msg(‘l_category_id1:=‘ || l_category_id);509                 hss_conc_utl.log_msg(‘l_category_set_id1:=‘ || l_category_set_id);510               511                 l_message := l_message || ‘分配物料类别报错：‘ || x_message;512               END IF;513             ELSE514               NULL;515             END IF;516           END IF; --IF l_old_category_id IS NOT NULL517         END IF;518       END IF;519     520       --更新物料类别信息521       FOR rec_category IN (SELECT t.*522                              FROM mtl_item_categories_v        t,523                                   org_organization_definitions ood,524                                   mtl_system_items_b           msib525                             WHERE 1 = 1526                               AND t.organization_id = ood.organization_id527                               AND ood.organization_code = c.organization_code528                               AND t.organization_id = msib.organization_id529                               AND t.inventory_item_id = msib.inventory_item_id530                               AND msib.segment1 = c.item_code531                               AND t.category_set_name IN (‘BOSUN成本类别集‘,532                                                           ‘产品类别‘,533                                                           ‘BOSUN折扣类别集‘))534       LOOP535       536         -- hss_conc_utl.log_msg(‘更新类别信息！‘);537         IF (rec_category.category_set_name = ‘BOSUN成本类别集‘) THEN538           IF (c.chenben_category IS NOT NULL) THEN539             BEGIN540               UPDATE mtl_categories_b mcb541                  SET mcb.segment1 = c.chenben_category542                WHERE mcb.category_id = rec_category.category_id;543             EXCEPTION544               WHEN OTHERS THEN545                 hss_conc_utl.log_msg(‘更新成本类别错误！‘);546                 l_message := l_message || ‘更新成本类别错误‘;547             END;548           END IF;549         ELSIF (rec_category.category_set_name = ‘产品类别‘) THEN550           IF (c.shengchan_category IS NOT NULL) THEN551             BEGIN552               UPDATE mtl_categories_b mcb553                  SET mcb.segment1 = c.shengchan_category554                WHERE mcb.category_id = rec_category.category_id;555             EXCEPTION556               WHEN OTHERS THEN557                 hss_conc_utl.log_msg(‘更新生产类别错误！‘);558                 l_message := l_message || ‘更新生产类别错误‘;559             END;560           END IF;561         ELSIF (rec_category.category_set_name = ‘BOSUN折扣类别集‘) THEN562           IF (c.zhekou_category IS NOT NULL) THEN563             BEGIN564               UPDATE mtl_categories_b mcb565                  SET mcb.segment1 = c.zhekou_category566                WHERE mcb.category_id = rec_category.category_id;567             EXCEPTION568               WHEN OTHERS THEN569                 hss_conc_utl.log_msg(‘更新折扣类别错误！‘);570                 l_message := l_message || ‘更新折扣类别错误‘;571             END;572           END IF;573         END IF;574       575       END LOOP; --FOR rec_category IN (576     577       /* FOR rec_category IN (SELECT t.*578                              FROM mtl_item_categories_v        t,579                                   org_organization_definitions ood,580                                   mtl_system_items_b           msib581                             WHERE 1 = 1582                               AND t.organization_id = ood.organization_id583                               AND ood.organization_code = c.organization_code584                               AND t.organization_id = msib.organization_id585                               AND t.inventory_item_id = msib.inventory_item_id586                               AND msib.segment1 = c.item_code587                               AND t.category_set_name IN ( --‘BOSUN成本类别集‘,588                                                           --‘产品类别‘,589                                                           ‘BOSUN折扣类别集‘))590       LOOP591         l_category_rec.category_id       := rec_category.category_id;592         l_category_rec.structure_id      := rec_category.structure_id;593         l_category_rec.summary_flag      := rec_category.summary_flag;594         l_category_rec.enabled_flag      := rec_category.enabled_flag;595         --l_category_rec. := rec_category.;596         l_category_rec.segment2          := rec_category.segment2;597         l_category_rec.segment3          := rec_category.segment3;598         l_category_rec.segment4          := rec_category.segment4;599         l_category_rec.segment5          := rec_category.segment5;600         l_category_rec.segment6          := rec_category.segment6;601         l_category_rec.segment7          := rec_category.segment7;602         l_category_rec.segment8          := rec_category.segment8;603         l_category_rec.segment9          := rec_category.segment9;604         l_category_rec.segment10         := rec_category.segment10;605         l_category_rec.segment11         := rec_category.segment11;606         l_category_rec.segment12         := rec_category.segment12;607         l_category_rec.segment13         := rec_category.segment13;608         l_category_rec.segment14         := rec_category.segment14;609         l_category_rec.segment15         := rec_category.segment15;610         l_category_rec.segment16         := rec_category.segment16;611         l_category_rec.segment17         := rec_category.segment17;612         l_category_rec.segment18         := rec_category.segment18;613         l_category_rec.segment19         := rec_category.segment19;614         l_category_rec.segment20         := rec_category.segment20;615       616         IF (rec_category.category_set_name = ‘BOSUN成本类别集‘) THEN617           IF (c.chenben_category IS NOT NULL) THEN618             l_category_rec.category_id := c.chenben_category;619           END IF;620         ELSIF (rec_category.category_set_name = ‘产品类别‘) THEN621           IF (c.chenben_category IS NOT NULL) THEN622             l_category_rec.segment1 := c.chenben_category;623           END IF;624         ELSIF (rec_category.category_set_name = ‘BOSUN折扣类别集‘) THEN625           IF (c.zhekou_category IS NOT NULL) THEN626             l_category_rec.segment1 := c.zhekou_category;627           END IF;628         END IF;629       630         inv_item_category_pub.update_category(p_api_version   => ‘1.0‘,631                                               p_init_msg_list => fnd_api.g_true,632                                               p_commit        => fnd_api.g_false,633                                               x_return_status => x_return_status,634                                               x_errorcode     => l_errorcode,635                                               x_msg_count     => l_msg_count,636                                               x_msg_data      =http://www.mamicode.com/> x_message,>637                                               p_category_rec  => l_category_rec);638         IF x_return_status <> fnd_api.g_ret_sts_success THEN639           hss_conc_utl.log_msg(‘更新物料类别信息错误！‘);640           l_message := l_message || x_message;641         END IF;642       END LOOP; --FOR rec_category IN*/643     644       IF (l_message IS NOT NULL) THEN645         --dbms_transaction.rollback_savepoint(l_savepoint_name);646         hss_conc_utl.log_msg(‘l_message:=‘ || l_message);647         l_err_count := l_err_count + 1;648       649         ROLLBACK TO l_savepoint_name;650       651         UPDATE cux.cux_mtl_update_prperty t652            SET t.return_stastus = ‘E‘,653                t.mes_text       = substr(l_message,654                                          1,655                                          2000)656          WHERE t.id = c.id;657       658         l_message := NULL;659         continue;660       ELSE661         UPDATE cux.cux_mtl_update_prperty t662            SET t.return_stastus = x_return_status,663                t.mes_text       = substr(l_message,664                                          1,665                                          200)666          WHERE t.id = c.id;667         l_message := NULL;668       END IF;669     END LOOP;670   671     IF (l_err_count > 0) THEN672       hss_conc_utl.log_msg(‘存在异常未处理的数据，请求报黄！‘);673       RAISE fnd_api.g_exc_error;674     END IF;675     -- end activity, include debug message hint to exit api676     x_return_status := hss_api.end_activity(p_pkg_name  => g_pkg_name,677                                             p_api_name  => l_api_name,678                                             x_msg_count => x_msg_count,679                                             x_msg_data  => x_msg_data);680   681   EXCEPTION682     WHEN fnd_api.g_exc_error THEN683       x_return_status := hss_api.handle_exceptions(p_pkg_name  => g_pkg_name,684                                                    p_api_name  => l_api_name,685                                                    p_exc_name  => hss_api.g_exc_name_error,686                                                    x_msg_count => x_msg_count,687                                                    x_msg_data  => x_msg_data);688     WHEN fnd_api.g_exc_unexpected_error THEN689       x_return_status := hss_api.handle_exceptions(p_pkg_name  => g_pkg_name,690                                                    p_api_name  => l_api_name,691                                                    p_exc_name  => hss_api.g_exc_name_unexp,692                                                    x_msg_count => x_msg_count,693                                                    x_msg_data  => x_msg_data);694     WHEN OTHERS THEN695       x_return_status := hss_api.handle_exceptions(p_pkg_name  => g_pkg_name,696                                                    p_api_name  => l_api_name,697                                                    p_exc_name  => hss_api.g_exc_name_others,698                                                    x_msg_count => x_msg_count,699                                                    x_msg_data  => x_msg_data);700   END update_item;