物料
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_old_category_id1 NUMBER; 20 l_category_id NUMBER; 21 l_category_id1 NUMBER; 22 l_category_set_name VARCHAR2(240) := '库存'; 23 --l_item_id NUMBER; 24 --l_organization_id NUMBER; 25 l_errorcode VARCHAR2(100); 26 l_msg_count NUMBER; 27 l_structure_id NUMBER; 28 l_structure_id1 NUMBER; 29 l_category_set_id NUMBER; 30 l_category_set_id1 NUMBER; 31 32 l_chart_of_account_id NUMBER; 33 l_ccid1 NUMBER; 34 l_ccid2 NUMBER; 35 l_ccid3 NUMBER; 36 l_ccid4 NUMBER; 37 l_default_org NUMBER; 38 l_organization_id NUMBER; 39 l_organization_id1 NUMBER; 40 l_item_id NUMBER; 41 l_organization_id2 NUMBER; 42 l_item_id1 NUMBER; 43 44 l_err_count NUMBER; 45 46 --更新类别信息 47 l_category_rec inv_item_category_pub.category_rec_type; 48 BEGIN 49 50 -- and initialize message list, include debug message hint to enter api 51 x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name, 52 p_api_name => l_api_name, 53 p_init_msg_list => p_init_msg_list); 54 -- check if activity started successfully 55 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN 56 RAISE fnd_api.g_exc_unexpected_error; 57 ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN 58 RAISE fnd_api.g_exc_error; 59 END IF; 60 61 BEGIN 62 SELECT mcs.category_set_id, 63 mcs.structure_id 64 INTO l_category_set_id, 65 l_structure_id 66 FROM mtl_category_sets mcs 67 WHERE mcs.category_set_name = l_category_set_name; 68 EXCEPTION 69 WHEN OTHERS THEN 70 hss_conc_utl.log_msg('无效的类别集！'); 71 RETURN; 72 END; 73 74 l_err_count := 0; 75 FOR c IN (SELECT * 76 FROM cux.cux_mtl_update_prperty ccp 77 WHERE nvl(ccp.return_stastus, 78 'N') <> 'S' 79 ORDER BY ccp.item_code) 80 LOOP 81 SAVEPOINT l_savepoint_name; --设置保存点 82 83 hss_conc_utl.log_msg('物料编码：' || c.item_code); 84 85 l_message := NULL; 86 --============================================================================= 87 /*当需要更新物料说明和默认发运组织时，需先更新主组织的物料说明和默认发运组织*/ 88 --============================================================================= 89 IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN 90 91 l_item_rec1.organization_code := 'MST'; 92 l_item_rec1.item_number := c.item_code; 93 94 --IF (c.desc_mir IS NOT NULL) THEN 95 -- l_item_rec1.description := c.desc_mir; 96 --ELSE 97 -- l_item_rec1.description := NULL; 98 ---END IF; 99 100 IF c.item_desc IS NOT NULL THEN 101 l_item_rec1.long_description := c.item_desc; 102 ELSE 103 l_item_rec1.long_description := NULL; 104 END IF; 105 106 IF (c.default_shipping_org IS NOT NULL) THEN 107 BEGIN 108 SELECT oog.organization_id 109 INTO l_default_org 110 FROM org_organization_definitions oog 111 WHERE oog.organization_code = c.default_shipping_org; 112 EXCEPTION 113 WHEN OTHERS THEN 114 l_default_org := NULL; 115 END; 116 117 l_item_rec1.default_shipping_org := l_default_org; 118 ELSE 119 l_item_rec1.default_shipping_org := NULL; 120 END IF; 121 122 --API 123 ----更新物料信息----- 124 inv_item_grp.update_item(p_commit => fnd_api.g_false, 125 p_item_rec => l_item_rec1, 126 x_item_rec => x_item_rec1, 127 x_return_status => x_return_status, 128 x_error_tbl => x_error_tbl); 129 IF x_return_status <> fnd_api.g_ret_sts_success THEN 130 -- l_message := NULL; 131 FOR i IN 1 .. x_error_tbl.count 132 LOOP 133 l_message := l_message || x_error_tbl(i).message_name; 134 END LOOP; 135 x_error_tbl.delete; 136 --ELSE 137 -- l_message := NULL; 138 END IF; 139 END IF; --IF (c.desc_mir IS NOT NULL OR c.default_shipping_org IS NOT NULL) THEN 140 141 --============================================================================= 142 --更新子组织信息 143 --============================================================================ 144 --Master Inv Organization first, then Current Inv Organization 145 l_item_rec.organization_code := c.organization_code; 146 l_item_rec.item_number := c.item_code; 147 148 IF c.buyer IS NOT NULL THEN 149 BEGIN 150 SELECT he.employee_id 151 INTO l_buyer_id 152 FROM hr_employees he 153 WHERE he.full_name = c.buyer; 154 EXCEPTION 155 WHEN OTHERS THEN 156 l_buyer_id := NULL; 157 END; 158 IF l_buyer_id IS NOT NULL THEN 159 l_item_rec.buyer_id := l_buyer_id; 160 END IF; 161 ELSE 162 l_item_rec.buyer_id := NULL; 163 END IF; 164 IF c.planer IS NOT NULL THEN 165 l_item_rec.planner_code := c.planer; 166 ELSE 167 l_item_rec.planner_code := NULL; 168 END IF; 169 IF c.attribute1 IS NOT NULL THEN 170 l_item_rec.attribute1 := c.attribute1; 171 ELSE 172 l_item_rec.attribute1 := NULL; 173 END IF; 174 IF c.attribute2 IS NOT NULL THEN 175 l_item_rec.attribute2 := c.attribute2; 176 ELSE 177 l_item_rec.attribute2 := NULL; 178 END IF; 179 IF c.attribute3 IS NOT NULL THEN 180 l_item_rec.attribute3 := c.attribute3; 181 ELSE 182 l_item_rec.attribute3 := NULL; 183 END IF; 184 IF c.attribute4 IS NOT NULL THEN 185 l_item_rec.attribute4 := c.attribute4; 186 ELSE 187 l_item_rec.attribute4 := NULL; 188 END IF; 189 IF c.attribute5 IS NOT NULL THEN 190 l_item_rec.attribute5 := c.attribute5; 191 ELSE 192 l_item_rec.attribute5 := NULL; 193 END IF; 194 IF c.attribute6 IS NOT NULL THEN 195 l_item_rec.attribute6 := c.attribute6; 196 ELSE 197 l_item_rec.attribute6 := NULL; 198 END IF; 199 IF c.attribute7 IS NOT NULL THEN 200 l_item_rec.attribute7 := c.attribute7; 201 ELSE 202 l_item_rec.attribute7 := NULL; 203 END IF; 204 IF c.attribute8 IS NOT NULL THEN 205 l_item_rec.attribute8 := c.attribute8; 206 ELSE 207 l_item_rec.attribute8 := NULL; 208 END IF; 209 IF c.attribute9 IS NOT NULL THEN 210 l_item_rec.attribute9 := c.attribute9; 211 ELSE 212 l_item_rec.attribute9 := NULL; 213 END IF; 214 IF c.attribute10 IS NOT NULL THEN 215 l_item_rec.attribute10 := c.attribute10; 216 ELSE 217 l_item_rec.attribute10 := NULL; 218 END IF; 219 IF c.attribute11 IS NOT NULL THEN 220 l_item_rec.attribute11 := c.attribute11; 221 ELSE 222 l_item_rec.attribute11 := NULL; 223 END IF; 224 IF c.attribute12 IS NOT NULL THEN 225 l_item_rec.attribute12 := c.attribute12; 226 ELSE 227 l_item_rec.attribute12 := NULL; 228 END IF; 229 IF c.attribute13 IS NOT NULL THEN 230 l_item_rec.attribute13 := c.attribute13; 231 ELSE 232 l_item_rec.attribute13 := NULL; 233 END IF; 234 IF c.attribute14 IS NOT NULL THEN 235 l_item_rec.attribute14 := c.attribute14; 236 ELSE 237 l_item_rec.attribute14 := NULL; 238 END IF; 239 IF c.attribute15 IS NOT NULL THEN 240 l_item_rec.attribute15 := c.attribute15; 241 ELSE 242 l_item_rec.attribute15 := NULL; 243 END IF; 244 IF c.attribute16 IS NOT NULL THEN 245 l_item_rec.attribute16 := c.attribute16; 246 ELSE 247 l_item_rec.attribute16 := NULL; 248 END IF; 249 IF c.attribute17 IS NOT NULL THEN 250 l_item_rec.attribute17 := c.attribute17; 251 ELSE 252 l_item_rec.attribute17 := NULL; 253 END IF; 254 IF c.attribute18 IS NOT NULL THEN 255 l_item_rec.attribute18 := c.attribute18; 256 ELSE 257 l_item_rec.attribute18 := NULL; 258 END IF; 259 IF c.attribute19 IS NOT NULL THEN 260 l_item_rec.attribute19 := c.attribute19; 261 ELSE 262 l_item_rec.attribute19 := NULL; 263 END IF; 264 IF c.attribute20 IS NOT NULL THEN 265 l_item_rec.attribute20 := c.attribute20; 266 ELSE 267 l_item_rec.attribute20 := NULL; 268 END IF; 269 270 IF (c.supply_subinventory IS NOT NULL) THEN 271 l_item_rec.wip_supply_subinventory := c.supply_subinventory; 272 ELSE 273 l_item_rec.wip_supply_subinventory := NULL; 274 END IF; 275 276 IF (c.min_quantity IS NOT NULL) THEN 277 l_item_rec.min_minmax_quantity := c.min_quantity; 278 ELSE 279 l_item_rec.min_minmax_quantity := NULL; 280 END IF; 281 IF (c.max_quantity IS NOT NULL) THEN 282 l_item_rec.max_minmax_quantity := c.max_quantity; 283 ELSE 284 l_item_rec.max_minmax_quantity := NULL; 285 END IF; 286 IF (c.min_order_quantity IS NOT NULL) THEN 287 l_item_rec.minimum_order_quantity := c.min_order_quantity; 288 ELSE 289 l_item_rec.minimum_order_quantity := NULL; 290 END IF; 291 IF (c.max_order_quantity IS NOT NULL) THEN 292 l_item_rec.maximum_order_quantity := c.max_order_quantity; 293 ELSE 294 l_item_rec.maximum_order_quantity := NULL; 295 END IF; 296 IF (c.fixed_order_quantity IS NOT NULL) THEN 297 l_item_rec.fixed_order_quantity := c.fixed_order_quantity; 298 ELSE 299 l_item_rec.fixed_order_quantity := NULL; 300 END IF; 301 IF (c.fixed_days_supply IS NOT NULL) THEN 302 l_item_rec.fixed_days_supply := c.fixed_days_supply; 303 ELSE 304 l_item_rec.fixed_days_supply := NULL; 305 END IF; 306 IF (c.fixed_lot_multiplier IS NOT NULL) THEN 307 l_item_rec.fixed_lot_multiplier := c.fixed_lot_multiplier; 308 ELSE 309 l_item_rec.fixed_lot_multiplier := NULL; 310 END IF; 311 312 BEGIN 313 SELECT gsob.chart_of_accounts_id 314 INTO l_chart_of_account_id 315 FROM gl_sets_of_books gsob, 316 hr_operating_units hou, 317 org_organization_definitions ood 318 WHERE gsob.set_of_books_id = hou.set_of_books_id 319 AND hou.organization_id = ood.operating_unit 320 AND ood.organization_code = c.organization_code; 321 EXCEPTION 322 WHEN OTHERS THEN 323 hss_conc_utl.log_msg('chart_of_account_id 没有找到'); 324 325 l_message := l_message || '组织对应chart_of_account_id 没有找到=='; 326 END; 327 328 ----------------------- 329 330 IF (c.encumbrance_account IS NOT NULL) THEN 331 l_ccid1 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL', 332 key_flex_code => 'GL#', 333 structure_number => l_chart_of_account_id, 334 validation_date => to_char(SYSDATE, 335 'DD-MON-YYYY'), 336 concatenated_segments => c.encumbrance_account); 337 l_item_rec.encumbrance_account := l_ccid1; 338 339 IF (l_ccid1 IS NULL) THEN 340 hss_conc_utl.log_msg('更新保留款账户异常！'); 341 l_message := l_message || '保留款账户异常=='; 342 END IF; 343 ELSE 344 l_item_rec.encumbrance_account := NULL; 345 END IF; 346 IF (c.expense_account IS NOT NULL) THEN 347 l_ccid2 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL', 348 key_flex_code => 'GL#', 349 structure_number => l_chart_of_account_id, 350 validation_date => to_char(SYSDATE, 351 'DD-MON-YYYY'), 352 concatenated_segments => c.expense_account); 353 l_item_rec.expense_account := l_ccid2; 354 355 IF (l_ccid2 IS NULL) THEN 356 hss_conc_utl.log_msg('更新费用账户异常！'); 357 l_message := l_message || '费用账户异常=='; 358 END IF; 359 ELSE 360 l_item_rec.expense_account := NULL; 361 END IF; 362 363 --l_item_rec.sales_account := NULL; 364 IF (c.sales_account IS NOT NULL) THEN 365 l_ccid3 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL', 366 key_flex_code => 'GL#', 367 structure_number => l_chart_of_account_id, 368 validation_date => to_char(SYSDATE, 369 'DD-MON-YYYY'), 370 concatenated_segments => c.sales_account); 371 l_item_rec.sales_account := l_ccid3; 372 373 IF (l_ccid3 IS NULL) THEN 374 hss_conc_utl.log_msg('更新销售账户异常！'); 375 l_message := l_message || '销售账户异常=='; 376 END IF; 377 ELSE 378 hss_conc_utl.log_msg('物料' || c.item_code || '下的销售账户不能为空'); 379 l_message := l_message || '物料' || c.item_code || '下的销售账户不能为空=='; 380 -- l_item_rec.sales_account := NULL; 381 END IF; 382 383 --l_item_rec.cost_of_sales_account := NULL; 384 IF (c.cost_of_sales_account IS NOT NULL) THEN 385 l_ccid4 := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL', 386 key_flex_code => 'GL#', 387 structure_number => l_chart_of_account_id, 388 validation_date => to_char(SYSDATE, 389 'DD-MON-YYYY'), 390 concatenated_segments => c.cost_of_sales_account); 391 l_item_rec.cost_of_sales_account := l_ccid4; 392 393 IF (l_ccid4 IS NULL) THEN 394 hss_conc_utl.log_msg('更新销售成本账户异常！'); 395 l_message := l_message || '销售成本账户异常=='; 396 END IF; 397 ELSE 398 hss_conc_utl.log_msg('物料' || c.item_code || '下的销售成本账户不能为空'); 399 l_message := l_message || '物料' || c.item_code || '下的销售成本账户不能为空=='; 400 -- l_item_rec.cost_of_sales_account := NULL; 401 END IF; 402 --API 403 ----更新物料信息----- 404 inv_item_grp.update_item(p_commit => fnd_api.g_false, 405 p_item_rec => l_item_rec, 406 x_item_rec => x_item_rec, 407 x_return_status => x_return_status, 408 x_error_tbl => x_error_tbl); 409 410 --Result 411 IF x_return_status <> fnd_api.g_ret_sts_success THEN 412 FOR i IN 1 .. x_error_tbl.count 413 LOOP 414 l_message := l_message || x_error_tbl(i).message_name; 415 END LOOP; 416 x_error_tbl.delete; 417 END IF; 418 419 IF x_return_status = fnd_api.g_ret_sts_success THEN 420 IF c.inv_category_contractsegment IS NOT NULL THEN 421 422 BEGIN 423 --获取物料类别ID-------- 424 SELECT mcv.category_id 425 INTO l_category_id 426 FROM mtl_categories_v mcv 427 WHERE 1 = 1 428 AND mcv.structure_id = l_structure_id 429 AND mcv.category_concat_segs = c.inv_category_contractsegment 430 AND (mcv.disable_date IS NULL OR mcv.disable_date > SYSDATE); 431 EXCEPTION 432 WHEN OTHERS THEN 433 l_category_id := NULL; 434 END; 435 436 IF l_category_id IS NULL THEN 437 x_return_status := 'E'; 438 l_message := l_message || '物料类别 :' || c.inv_category_contractsegment || '无效'; 439 ELSE 440 441 ----获取原来类别id 442 BEGIN 443 SELECT mic.category_id 444 INTO l_old_category_id 445 FROM mtl_system_items_b msi, 446 mtl_item_categories mic 447 WHERE 1 = 1 448 AND msi.inventory_item_id = mic.inventory_item_id 449 AND msi.organization_id = mic.organization_id 450 AND msi.inventory_item_id = x_item_rec.inventory_item_id 451 AND msi.organization_id = x_item_rec.organization_id 452 AND mic.category_set_id = l_category_set_id; 453 EXCEPTION 454 WHEN OTHERS THEN 455 l_old_category_id := NULL; 456 END; 457 458 --- inv_item_category_pub.Create_Category_Assignment 459 IF l_old_category_id IS NOT NULL 460 AND l_old_category_id <> l_category_id THEN 461 ---更新物料类别分配 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 THEN 476 477 hss_conc_utl.log_msg('l_category_id:=' || l_category_id); 478 hss_conc_utl.log_msg('l_category_set_id:=' || l_category_set_id); 479 480 l_message := l_message || '==分配物料类别报错：' || x_message; 481 482 END IF; 483 ELSIF l_old_category_id IS NULL THEN 484 inv_item_category_pub.create_category_assignment(p_api_version => '1.0', 485 p_init_msg_list => fnd_api.g_true, 486 p_commit => fnd_api.g_false, 487 x_return_status => x_return_status, 488 x_errorcode => l_errorcode, 489 x_msg_count => l_msg_count, 490 x_msg_data => x_message, 491 p_category_id => l_category_id, 492 p_category_set_id => l_category_set_id, 493 p_inventory_item_id => x_item_rec.inventory_item_id, 494 p_organization_id => x_item_rec.organization_id); 495 IF x_return_status <> fnd_api.g_ret_sts_success THEN 496 hss_conc_utl.log_msg('l_category_id1:=' || l_category_id); 497 hss_conc_utl.log_msg('l_category_set_id1:=' || l_category_set_id); 498 499 l_message := l_message || '==创建物料类别报错：' || x_message; 500 END IF; 501 END IF; 502 END IF; --IF l_old_category_id IS NOT NULL 503 504 END IF; -- IF c.inv_category_contractsegment IS NOT NULL THEN 505 506 507 --更新类别集 508 FOR rec_cate IN (SELECT mcs.category_set_id, 509 mcs.structure_id, 510 mcs.category_set_name 511 FROM mtl_category_sets mcs 512 WHERE 1 = 1 513 AND ((mcs.category_set_name = 'BOSUN成本类别集' AND c.chenben_category IS NOT NULL) OR 514 (mcs.category_set_name = 'BOSUN生产类别集' AND c.shengchan_category IS NOT NULL) OR 515 (mcs.category_set_name = 'BOSUN折扣类别集' AND c.zhekou_category IS NOT NULL))) 516 LOOP 517 hss_conc_utl.log_msg('-----------------------------------'); 518 519 l_structure_id1 := rec_cate.structure_id; 520 l_category_set_id1 := rec_cate.category_set_id; 521 522 BEGIN 523 --获取物料类别ID-------- 524 l_category_id := NULL; 525 SELECT mcv.category_id 526 INTO l_category_id1 527 FROM mtl_categories_v mcv 528 WHERE 1 = 1 529 AND mcv.structure_id = l_structure_id1 530 AND mcv.category_concat_segs = decode(rec_cate.category_set_name, 531 'BOSUN成本类别集', 532 c.chenben_category, 533 'BOSUN生产类别集', 534 c.shengchan_category, 535 'BOSUN折扣类别集', 536 c.zhekou_category, 537 NULL) 538 AND (mcv.disable_date IS NULL OR mcv.disable_date > SYSDATE); 539 EXCEPTION 540 WHEN OTHERS THEN 541 l_category_id1 := NULL; 542 END; 543 544 IF (rec_cate.category_set_name IN ('BOSUN生产类别集', 545 'BOSUN成本类别集')) THEN 546 547 l_organization_id2 := 102; 548 ELSE 549 l_organization_id2 := x_item_rec.organization_id; 550 END IF; 551 552 ----获取原来类别id 553 BEGIN 554 SELECT mic.category_id 555 INTO l_old_category_id1 556 FROM mtl_system_items_b msi, 557 mtl_item_categories mic 558 WHERE 1 = 1 559 AND msi.inventory_item_id = mic.inventory_item_id 560 AND msi.organization_id = mic.organization_id 561 AND msi.inventory_item_id = x_item_rec.inventory_item_id 562 AND msi.organization_id = l_organization_id2 563 AND mic.category_set_id = l_category_set_id1; 564 EXCEPTION 565 WHEN OTHERS THEN 566 l_old_category_id1 := NULL; 567 END; 568 569 hss_conc_utl.log_msg('l_old_category_id1:=' || l_old_category_id1); 570 571 IF l_old_category_id1 IS NOT NULL 572 -- AND l_old_category_id1 <> l_category_id 573 THEN 574 ---更新物料类别分配 575 hss_conc_utl.log_msg('---更新类别集'); 576 inv_item_category_pub.update_category_assignment(p_api_version => '1.0', 577 p_init_msg_list => fnd_api.g_true, 578 p_commit => fnd_api.g_false, 579 p_category_id => l_category_id1, 580 p_old_category_id => l_old_category_id1, 581 p_category_set_id => l_category_set_id1, 582 p_inventory_item_id => x_item_rec.inventory_item_id, 583 p_organization_id => l_organization_id2, 584 x_return_status => x_return_status, 585 x_errorcode => l_errorcode, 586 x_msg_count => l_msg_count, 587 x_msg_data => x_message); 588 589 IF x_return_status <> fnd_api.g_ret_sts_success THEN 590 591 hss_conc_utl.log_msg('l_category_id1:=' || l_category_id1); 592 hss_conc_utl.log_msg('l_category_set_id:=' || l_category_set_id1); 593 594 l_message := l_message || '--分配物料类别报错：' || x_message; 595 END IF; 596 ELSIF l_old_category_id1 IS NULL THEN 597 hss_conc_utl.log_msg('--创建类别集'); 598 599 inv_item_category_pub.create_category_assignment(p_api_version => '1.0', 600 p_init_msg_list => fnd_api.g_true, 601 p_commit => fnd_api.g_false, 602 x_return_status => x_return_status, 603 x_errorcode => l_errorcode, 604 x_msg_count => l_msg_count, 605 x_msg_data => x_message, 606 p_category_id => l_category_id1, 607 p_category_set_id => l_category_set_id1, 608 p_inventory_item_id => x_item_rec.inventory_item_id, 609 p_organization_id => l_organization_id2); 610 IF x_return_status <> fnd_api.g_ret_sts_success THEN 611 612 hss_conc_utl.log_msg('l_category_id1:=' || l_category_id); 613 hss_conc_utl.log_msg('l_category_set_id1:=' || l_category_set_id1); 614 615 l_message := l_message || '--创建物料类别报错：' || x_message; 616 END IF; 617 END IF; 618 619 END LOOP; 620 621 END IF; --IF x_return_status = fnd_api.g_ret_sts_success THEN 622 623 IF (l_message IS NOT NULL) THEN 624 --dbms_transaction.rollback_savepoint(l_savepoint_name); 625 hss_conc_utl.log_msg('l_message:=' || l_message); 626 l_err_count := l_err_count + 1; 627 628 ROLLBACK TO l_savepoint_name; 629 630 UPDATE cux.cux_mtl_update_prperty t 631 SET t.return_stastus = 'E', 632 t.mes_text = substr(l_message, 633 1, 634 2000) 635 WHERE t.id = c.id; 636 637 l_message := NULL; 638 continue; 639 ELSE 640 UPDATE cux.cux_mtl_update_prperty t 641 SET t.return_stastus = x_return_status, 642 t.mes_text = substr(l_message, 643 1, 644 200) 645 WHERE t.id = c.id; 646 l_message := NULL; 647 END IF; 648 END LOOP; 649 650 IF (l_err_count > 0) THEN 651 hss_conc_utl.log_msg('存在异常未处理的数据，请求报黄！'); 652 RAISE fnd_api.g_exc_error; 653 END IF; 654 -- end activity, include debug message hint to exit api 655 x_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name, 656 p_api_name => l_api_name, 657 x_msg_count => x_msg_count, 658 x_msg_data => x_msg_data); 659 660 EXCEPTION 661 WHEN fnd_api.g_exc_error THEN 662 x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, 663 p_api_name => l_api_name, 664 p_exc_name => hss_api.g_exc_name_error, 665 x_msg_count => x_msg_count, 666 x_msg_data => x_msg_data); 667 WHEN fnd_api.g_exc_unexpected_error THEN 668 x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, 669 p_api_name => l_api_name, 670 p_exc_name => hss_api.g_exc_name_unexp, 671 x_msg_count => x_msg_count, 672 x_msg_data => x_msg_data); 673 WHEN OTHERS THEN 674 x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, 675 p_api_name => l_api_name, 676 p_exc_name => hss_api.g_exc_name_others, 677 x_msg_count => x_msg_count, 678 x_msg_data => x_msg_data); 679 END update_item;