[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_CATALOG_ELEM_PUB
Source
1 PACKAGE BODY INV_ITEM_CATALOG_ELEM_PUB AS
2 /* $Header: INVCEOIB.pls 120.2.12020000.3 2012/11/22 08:59:06 kaizhao ship $ */
3 ---------------------- Package variables and constants -----------------------
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_CATALOG_ELEM_PUB';
6
7 G_SUCCESS CONSTANT NUMBER := 0;
8 G_WARNING CONSTANT NUMBER := 1;
9 G_ERROR CONSTANT NUMBER := 2;
10
11 G_ROWS_TO_COMMIT CONSTANT NUMBER := 500;
12
13 ------------------------------------------------------------------------------
14
15 ------------------------ process_Item_Catalog_element_records ---------------------
16
17 PROCEDURE Process_item_descr_elements
18 (
19 p_api_version IN NUMBER
20 , p_init_msg_list IN VARCHAR2
21 , p_commit_flag IN VARCHAR2
22 , p_validation_level IN NUMBER
23 , p_inventory_item_id IN NUMBER
24 , p_item_number IN VARCHAR2
25 , p_item_desc_element_table IN ITEM_DESC_ELEMENT_TABLE
26 , x_generated_descr OUT NOCOPY VARCHAR2
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_msg_count OUT NOCOPY NUMBER
29 , x_msg_data OUT NOCOPY VARCHAR2
30 )
31 IS
32 l_api_name CONSTANT VARCHAR2(30) := 'Process_item_descr_elements';
33 -- On addition of any Required parameters the major version needs
34 -- to change i.e. for eg. 1.X to 2.X.
35 -- On addition of any Optional parameters the minor version needs
36 -- to change i.e. for eg. X.6 to X.7.
37
38 l_api_version CONSTANT NUMBER := 1.0;
39
40 Mctx INV_ITEM_MSG.Msg_Ctx_type;
41
42 Processing_Error EXCEPTION;
43
44 ret_code NUMBER := 0;
45 l_err_text VARCHAR2(2000);
46
47 l_return_status VARCHAR2(1); -- := fnd_api.g_MISS_CHAR
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50
51 l_msg_name VARCHAR2(2000);
52
53 l_column_name VARCHAR2(30);
54 l_token VARCHAR2(30);
55 l_token_value VARCHAR2(30);
56 l_error_msg VARCHAR2(2000);
57 l_item_desc_element_table ITEM_DESC_ELEMENT_TABLE;
58 BEGIN
59
60 -- Check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call (l_api_version,
62 p_api_version ,
63 l_api_name ,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 -- Standard Start of API savepoint
70 SAVEPOINT Process_item_descr_elem_PUB;
71
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF FND_API.to_Boolean( p_init_msg_list ) THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76
77 INV_ITEM_MSG.set_Message_Mode('PLSQL');
78
79 -- Set message level
80 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
81 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
82 END IF;
83
84 -- Define message context
85 Mctx.Package_Name := G_PKG_NAME;
86 Mctx.Procedure_Name := l_api_name;
87
88 -- Set global package variables for the current import session
89
90 INV_ITEM_MSG.g_Table_Name := 'p_item_desc_element_table';
91
92 INV_ITEM_MSG.g_User_id := FND_GLOBAL.user_id ;
93 INV_ITEM_MSG.g_Login_id := FND_GLOBAL.login_id ;
94 INV_ITEM_MSG.g_Prog_appid := FND_GLOBAL.prog_appl_id ;
95 INV_ITEM_MSG.g_Prog_id := FND_GLOBAL.conc_program_id;
96 INV_ITEM_MSG.g_Request_id := FND_GLOBAL.conc_request_id;
97
98 x_return_status := fnd_api.g_RET_STS_SUCCESS;
99 ------------------------------------------------------------------------------------------
100 -- Process step 1: Loop through item catlog group elements interface records --
101 -- (a) Check for duplicate records in the interface table table --
102 ------------------------------------------------------------------------------------------
103 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step1');
104 l_item_desc_element_table := p_item_desc_element_table;
105 FOR icoi_rec IN l_item_desc_element_table.first .. l_item_desc_element_table.last-1 LOOP --{
106 FOR icoi_rec_dup IN icoi_rec+1 .. l_item_desc_element_table.last LOOP --{
107 IF ( l_item_desc_element_table.EXISTS(icoi_rec) ) THEN
108 IF (l_item_desc_element_table(icoi_rec).ELEMENT_NAME = l_item_desc_element_table(icoi_rec_dup).ELEMENT_NAME)
109 THEN
110 INV_ITEM_MSG.Debug(Mctx, 'Duplicate record found' || to_char(icoi_rec_dup) );
111 INV_ITEM_MSG.Add_Message
112 ( p_Msg_Name => 'INV_CEOI_DUP_ELEM_REC'
113 , p_token1 => 'ELEMENT_NAME'
114 , p_value1 => l_item_desc_element_table(icoi_rec).ELEMENT_NAME
115 );
116 l_item_desc_element_table.DELETE(icoi_rec_dup);
117 x_return_status := fnd_api.g_RET_STS_ERROR;
118 END IF;
119 END IF;
120 END LOOP;--} icoi_csr_dup
121 END LOOP; --} icoi_csr
122
123 INV_ITEM_MSG.Debug(Mctx, 'Write all accumulated messages' );
124 -- Write all accumulated messages
125 INV_ITEM_MSG.Write_List (p_delete => TRUE);
126
127 ------------------------------------------------------------------------------------------
128 -- Process step 2: Loop through item catlog group elements interface records --
129 -- (a) call the API to create item catalog group element values assignment record in the production table --
130 -- (b) update the current interface record process_flag and other converted values --
131 ------------------------------------------------------------------------------------------
132
133 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step2');
134
135 FOR icoi_rec IN l_item_desc_element_table.first .. l_item_desc_element_table.last LOOP --{
136
137 l_return_status := fnd_api.g_RET_STS_SUCCESS;
138
139 -- call the API to process item catalog element values
140
141 IF ( l_item_desc_element_table.EXISTS(icoi_rec) ) THEN
142
143 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATALOG_ELE_PVT.Create_Catalog_group_ele_Assignment');
144 INV_ITEM_MSG.Debug(Mctx, 'Element Name:'||l_item_desc_element_table(icoi_rec).ELEMENT_NAME);
145
146 INV_ITEM_CATALOG_ELE_PVT.Catalog_Grp_Ele_Val_Assignment
147 (
148 p_api_version => 1.0
149 , p_init_msg_list => fnd_api.g_TRUE
150 , p_commit => fnd_api.g_FALSE
151 , p_validation_level => p_validation_level
152 , p_inventory_item_id => p_inventory_item_id
153 , p_item_number => p_item_number
154 , p_element_name => l_item_desc_element_table(icoi_rec).ELEMENT_NAME
155 , p_element_value => l_item_desc_element_table(icoi_rec).ELEMENT_VALUE
156 , p_default_element_flag => l_item_desc_element_table(icoi_rec).DESCRIPTION_DEFAULT
157 , x_return_status => l_return_status
158 , x_msg_count => l_msg_count
159 , x_msg_data => l_msg_data
160 );
161
162 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
163 NULL;
164 ELSE
165 INV_ITEM_MSG.Debug(Mctx, 'error in Catalog_Grp_Ele_Val_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
166 x_return_status := l_return_status;
167 END IF; -- l_return_status
168
169 -- If unexpected error in Catalog_Grp_Ele_Val_Assignment API, stop the processing
170 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
171 x_return_status := l_return_status;
172 RAISE Processing_Error;
173 END IF;
174
175 END IF; -- p_item_desc_element_table.EXISTS
176 -- Write all accumulated messages
177 INV_ITEM_MSG.Write_List (p_delete => TRUE);
178
179 END LOOP; --} icoi_csr
180
181 INVICGDS.inv_get_icg_desc(
182 inv_item_id=>p_inventory_item_id,
183 first_elem_break=>30,
184 use_name_as_first_elem=>fnd_profile.value('USE_NAME_ICG_DESC'),
185 description_for_item=>x_generated_descr,
186 delimiter=>null,
187 show_all_delim=>'Y',
188 error_text=>l_error_msg);
189
190 -- Check of commit
191 IF ( FND_API.To_Boolean(p_commit_flag) ) THEN
192 COMMIT WORK;
193 END IF;
194 --
195 -- Determine request return code
196 --
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201
202 EXCEPTION
203 WHEN Processing_Error THEN
204 ROLLBACK TO Process_item_descr_elem_PUB;
205 -- Write all accumulated messages
206 INV_ITEM_MSG.Write_List (p_delete => TRUE);
207
208 FND_MSG_PUB.Count_And_Get
209 ( p_count => x_msg_count,
210 p_data => x_msg_data
211 );
212
213 WHEN others THEN
214 ROLLBACK TO Process_item_descr_elem_PUB;
215 l_err_text := SUBSTRB(SQLERRM, 1,240);
216 x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
217 INV_ITEM_MSG.Add_Message
218 ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
219 , p_token1 => 'PKG_NAME'
220 , p_value1 => G_PKG_NAME
221 , p_token2 => 'PROCEDURE_NAME'
222 , p_value2 => l_api_name
223 , p_token3 => 'ERROR_TEXT'
224 , p_value3 => l_err_text
225 );
226
227 -- Write all accumulated messages
228 INV_ITEM_MSG.Write_List (p_delete => TRUE);
229
230 FND_MSG_PUB.Count_And_Get
231 ( p_count => x_msg_count,
232 p_data => x_msg_data
233 );
234
235 END Process_item_descr_elements;
236 ------------------------------------------------------------------------------
237
238
239 ------------------------ process_Item_Catlog_group_Interface_records -----------------------
240
241 PROCEDURE process_Item_Catalog_grp_recs
242 (
243 ERRBUF OUT NOCOPY VARCHAR2
244 , RETCODE OUT NOCOPY NUMBER
245 , p_rec_set_id IN NUMBER
246 , p_upload_rec_flag IN NUMBER
247 , p_delete_rec_flag IN NUMBER
248 , p_commit_flag IN NUMBER
249 , p_prog_appid IN NUMBER
250 , p_prog_id IN NUMBER
251 , p_request_id IN NUMBER
252 , p_user_id IN NUMBER
253 , p_login_id IN NUMBER
254 )
255 IS
256 l_api_name CONSTANT VARCHAR2(30) := 'process_Item_Catalog_grp_recs';
257 Mctx INV_ITEM_MSG.Msg_Ctx_type;
258
259 --
260 -- Cursor for the duplicate check (Create_Catalog_group_Assignment)
261 --
262 CURSOR icoi_csr_dup
263 IS
264 SELECT
265 mdei_dup.rowid
266 , mdei_dup.transaction_id
267 , mdei_dup.element_name
268 FROM
269 mtl_desc_elem_val_interface mdei_dup
270 WHERE
271 mdei_dup.rowid > (select rowid
272 FROM mtl_desc_elem_val_interface mdei
273 WHERE mdei.set_process_id = g_xset_id
274 AND mdei.process_flag IN (1, 2)
275 --bug 9693293,8490530
276 AND rownum =1
277 --end bug 9693293
278 AND (mdei.inventory_item_id = mdei_dup.inventory_item_id
279 OR mdei.item_number = mdei_dup.item_number )
280 AND mdei.element_name = mdei_dup.element_name
281 )
282 FOR UPDATE OF mdei_dup.transaction_id;
283
284 --
285 -- Cursor for the main loop (Create_Catalog_group_Assignment)
286 --
287 CURSOR icoi_csr
288 IS
289 SELECT
290 mdei.rowid, mdei.transaction_id
291 , mdei.inventory_item_id
292 , mdei.element_name, mdei.element_value
293 , mdei.element_sequence, mdei.item_number
294 , mdei.default_element_flag
295 FROM
296 mtl_desc_elem_val_interface mdei
297 WHERE
298 mdei.set_process_id = g_xset_id
299 AND mdei.process_flag IN (1, 2, 4) --R12C
300 ORDER BY mdei.item_number,mdei.inventory_item_id
301 FOR UPDATE OF mdei.transaction_id;
302
303 l_process_flag NUMBER;
304
305 Processing_Error EXCEPTION;
306
307 ret_code NUMBER := 0;
308 l_err_text VARCHAR2(2000);
309
310 l_commit VARCHAR2(1);
311 l_return_status VARCHAR2(1); -- := fnd_api.g_MISS_CHAR
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(2000);
314
315 l_msg_name VARCHAR2(2000);
316
317 l_RETCODE NUMBER; -- G_SUCCESS, G_WARNING, G_ERROR
318 l_column_name VARCHAR2(30);
319 l_token VARCHAR2(30);
320 l_token_value VARCHAR2(30);
321 l_element_name VARCHAR2(200);
322 l_element_value VARCHAR2(200);
323 l_default_element_flag VARCHAR2(1);
324 l_transaction_id NUMBER;
325 l_inventory_item_id NUMBER;
326 l_item_number VARCHAR2(200);
327 flex_id NUMBER;
328 item_id NUMBER;
329
330 BEGIN
331
332 INV_ITEM_MSG.Initialize;
333
334 INV_ITEM_MSG.set_Message_Mode ('CP_LOG');
335
336 -- Set message level
337
338 -- INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Statement);
339 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
340
341 -- Define message context
342 Mctx.Package_Name := G_PKG_NAME;
343 Mctx.Procedure_Name := l_api_name;
344
345 INV_ITEM_MSG.Debug(Mctx, 'start rec_set_id = '|| TO_CHAR(p_rec_set_id));
346
347 -- Set global package variables for the current import session
348
349 g_xset_id := p_rec_set_id;
350
351 g_User_id := NVL(p_user_id, FND_GLOBAL.user_id );
352 g_Login_id := NVL(p_login_id, FND_GLOBAL.login_id );
353 g_Prog_appid := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
354 g_Prog_id := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
355 g_Request_id := NVL(p_request_id, FND_GLOBAL.conc_request_id );
356
357 INV_ITEM_MSG.g_Table_Name := 'MTL_DESC_ELEM_VAL_INTERFACE';
358
359 INV_ITEM_MSG.g_User_id := g_User_id;
360 INV_ITEM_MSG.g_Login_id := g_Login_id;
361 INV_ITEM_MSG.g_Prog_appid := g_Prog_appid;
362 INV_ITEM_MSG.g_Prog_id := g_Prog_id;
363 INV_ITEM_MSG.g_Request_id := g_Request_id;
364
365 IF ( p_commit_flag = 1 ) THEN
366 l_commit := fnd_api.g_TRUE;
367 ELSE
368 l_commit := fnd_api.g_FALSE;
369 END IF;
370
371 l_RETCODE := G_SUCCESS;
372
373 ---------------------------------------------------------------------------------------
374 -- Process step 1: Set process flag to 2 --
375 ---------------------------------------------------------------------------------------
376
377 INV_ITEM_MSG.Debug(Mctx, ' Set process flag to 2');
378
379 UPDATE mtl_desc_elem_val_interface mdei
380 SET process_flag = 2
381 WHERE
382 mdei.set_process_id = g_xset_id
383 AND mdei.process_flag = 1;
384
385 ------------------------------------------------------------------------------------------
386 -- Process step 2: Loop through item catlog group elements interface records --
387 -- (a) convert the item_number to irem_id --
388 -- (b) update the interface records accordingly --
389 ------------------------------------------------------------------------------------------
390 INV_ITEM_MSG.Debug(Mctx, 'starting the ICatalogOI loop to convert the item_number');
391
392 IF p_upload_rec_flag = 1 THEN
393
394
395 FOR icoi_rec1 IN icoi_csr LOOP
396
397 -- Process flag for the current record is initially set to 4 (validation success).
398 -- May be changed to 3 or 5, if any errors occur during validation.
399 l_process_flag := 4;
400 l_inventory_item_id := NULL;
401 --
402 -- Assign missing inventory_item_id from item_number
403 --
404
405 l_return_status := fnd_api.g_RET_STS_SUCCESS;
406
407 item_id := icoi_rec1.inventory_item_id;
408 l_item_number := icoi_rec1.item_number;
409
410 IF ( l_item_number IS NOT NULL ) THEN
411 ret_code := INVPUOPI.mtl_pr_parse_item_name (
412 l_item_number,
413 flex_id,
414 l_err_text );
415 IF ( ret_code = 0 ) THEN
416 l_inventory_item_id := flex_id;
417 IF ((item_id IS NOT NULL)AND
418 (l_inventory_item_id <> item_id)) THEN
419 l_return_status := fnd_api.g_RET_STS_ERROR;
420 l_msg_name := 'INV_CEOI_ITEM_NUM_ID_MISMATCH';
421 l_token := 'VALUE1';
422 l_token_value := l_item_number;
423 l_column_name := 'ITEM_NUMBER';
424 l_token := 'VALUE2';
425 l_token_value := item_id;
426 l_column_name := 'INVENTORY_ITEM_ID';
427 END IF;
428 ELSE
429 l_return_status := fnd_api.g_RET_STS_ERROR;
430 l_msg_name := 'INV_ICOI_INVALID_ITEM_NUMBER';
431 l_token := 'VALUE';
432 l_token_value := l_item_number;
433 l_column_name := 'ITEM_NUMBER';
434 END IF;
435
436 ELSIF ( item_id IS NULL )THEN
437 l_return_status := fnd_api.g_RET_STS_ERROR;
438 l_msg_name := 'INV_CEOI_MISS_ITEM_NUMBER';
439 l_token := fnd_api.g_MISS_CHAR;
440 l_token_value := l_item_number||item_id;
441 l_column_name := 'ITEM_NUMBER';
442 END IF;
443 IF (l_return_status = fnd_api.g_RET_STS_ERROR) THEN
444 --Bug 14068499
445 SELECT mtl_system_items_interface_s.NEXTVAL
446 INTO l_transaction_id
447 FROM dual;
448
449
450 INV_ITEM_MSG.Add_Message
451 ( p_Msg_Name => l_msg_name
452 , p_token1 => l_token
453 , p_value1 => l_token_value
454 , p_column_name => l_column_name
455 , p_transaction_id => l_transaction_id --Bug 14068499
456 );
457 UPDATE mtl_desc_elem_val_interface
458 SET
459 transaction_id = l_transaction_id,
460 request_id = g_request_id,
461 process_flag = 3
462 WHERE
463 CURRENT OF icoi_csr;
464 ELSIF ( l_inventory_item_id IS NOT NULL )THEN
465 UPDATE mtl_desc_elem_val_interface
466 SET
467 inventory_item_id = l_inventory_item_id
468 WHERE
469 CURRENT OF icoi_csr;
470 END IF;
471
472 END LOOP; -- icoi_rec1
473
474 -- Check of commit
475 IF ( FND_API.To_Boolean(l_commit) ) THEN
476 COMMIT WORK;
477 END IF;
478 -- Write all accumulated messages
479 INV_ITEM_MSG.Write_List (p_delete => TRUE);
480
481 ------------------------------------------------------------------------------------------
482 -- Process step 3: Loop through item catlog group elements interface records --
483 -- (a) Check for duplicate records in the interface table table --
484 -- (b) update the duplicate interface records process_flag --
485 ------------------------------------------------------------------------------------------
486 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step3');
487
488
489 FOR icoi_rec_dup IN icoi_csr_dup LOOP --{
490 --YJAIN
491 --Bug 14068499
492 SELECT mtl_system_items_interface_s.NEXTVAL
493 INTO l_transaction_id
494 FROM dual;
495
496
497 INV_ITEM_MSG.Add_Message
498 ( p_Msg_Name => 'INV_CEOI_DUP_ELEM_REC'
499 , p_token1 => 'ELEMENT_NAME'
500 , p_value1 => icoi_rec_dup.ELEMENT_NAME
501 , p_transaction_id => l_transaction_id
502 );
503
504
505 UPDATE mtl_desc_elem_val_interface
506 SET
507 transaction_id = l_transaction_id,
508 request_id = g_request_id,
509 process_flag = 3
510 WHERE
511 CURRENT OF icoi_csr_dup;
512
513 END LOOP; --} icoi_csr_dup
514 END IF; -- Upload Rec Flag is 1
515 -- Check of commit
516 IF ( FND_API.To_Boolean(l_commit) ) THEN
517 COMMIT WORK;
518 END IF;
519 -- Write all accumulated messages
520 INV_ITEM_MSG.Write_List (p_delete => TRUE);
521
522 ------------------------------------------------------------------------------------------
523 -- Process step 4: Loop through item catlog group elements interface records --
524 -- (a) call the API to create item catalog group element values assignment record in the production table --
525 -- (b) update the current interface record process_flag and other converted values --
526 ------------------------------------------------------------------------------------------
527
528 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step4');
529
530
531 FOR icoi_rec IN icoi_csr LOOP --{
532
533 --YJAIN
534 -- Bug 14068499
535
536 SELECT mtl_system_items_interface_s.NEXTVAL
537 INTO l_transaction_id
538 FROM dual;
539
540 -- Process flag for the current record is initially set to 4 (validation success).
541 -- May be changed to 3 or 5, if any errors occur during validation.
542 l_process_flag := 4;
543
544 --
545 -- Assign missing inventory_item_id from item_number
546 --
547
548 l_return_status := fnd_api.g_RET_STS_SUCCESS;
549 l_inventory_item_id := icoi_rec.inventory_item_id;
550 l_item_number := icoi_rec.item_number;
551 l_element_value := icoi_rec.element_value;
552 l_element_name := icoi_rec.element_name;
553 l_default_element_flag := icoi_rec.default_element_flag;
554 --
555 -- If value-to-id conversions are successful,
556 -- call the API to process item catalog element values
557
558 IF ( l_process_flag = 4 AND p_upload_rec_flag = 1 ) THEN
559
560 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATALOG_ELE_PVT.Create_Catalog_group_ele_Assignment:'||l_element_name);
561
562 INV_ITEM_CATALOG_ELE_PVT.Catalog_Grp_Ele_Val_Assignment
563 (
564 p_api_version => 1.0
565 , p_init_msg_list => fnd_api.g_TRUE
566 , p_commit => fnd_api.g_FALSE
567 , p_validation_level => g_VALIDATE_IDS
568 , p_inventory_item_id => l_inventory_item_id
569 , p_item_number => l_item_number
570 , p_element_name => l_element_name
571 , p_element_value => l_element_value
572 , p_default_element_flag => l_default_element_flag
573 , p_transaction_id => l_transaction_id --Bug 14068499
574 , x_return_status => l_return_status
575 , x_msg_count => l_msg_count
576 , x_msg_data => l_msg_data
577 );
578
579 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
580 l_process_flag := 7;
581 ELSE
582 l_process_flag := 3;
583 INV_ITEM_MSG.Debug(Mctx, 'error in Catalog_Grp_Ele_Val_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
584 l_RETCODE := G_WARNING;
585 END IF; -- l_return_status
586
587 -- If unexpected error in Catalog_Grp_Ele_Val_Assignment API, stop the processing
588 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
589 l_RETCODE := G_ERROR;
590 RAISE Processing_Error;
591 END IF;
592
593 END IF; -- process_flag = 4 AND p_upload_rec_flag = 1
594
595 -- Write all accumulated messages
596 INV_ITEM_MSG.Write_List (p_delete => TRUE);
597
598 --
599 -- Update the current interface record
600 --
601
602 INV_ITEM_MSG.Debug(Mctx, 'update interface record');
603
604 UPDATE mtl_desc_elem_val_interface
605 SET
606 transaction_id = l_transaction_id
607 , inventory_item_id = l_inventory_item_id
608 , item_number = l_item_number
609 , process_flag = l_process_flag
610 , program_application_id = g_prog_appid
611 , program_id = g_prog_id
612 , program_update_date= SYSDATE
613 , request_id = g_request_id
614 , last_update_date = SYSDATE
615 , last_updated_by = g_user_id
616 , last_update_login = g_login_id
617 WHERE
618 CURRENT OF icoi_csr;
619
620 END LOOP; --} icoi_csr
621
622 -- Check of commit
623 IF ( FND_API.To_Boolean(l_commit) ) THEN
624 COMMIT WORK;
625 END IF;
626
627 --
628 -- Delete successfully processed records from the interface table
629 --
630
631 IF (p_delete_rec_flag = 1) THEN
632
633 INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');
634
635 INV_ITEM_CATALOG_ELEM_PUB.delete_OI_records
636 ( p_commit => l_commit
637 , p_rec_set_id => g_xset_id
638 , x_return_status => l_return_status
639 );
640
641 INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);
642
643 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
644 RAISE Processing_Error;
645 END IF;
646
647 -- Write all accumulated messages
648 INV_ITEM_MSG.Write_List (p_delete => TRUE);
649
650 END IF; -- p_delete_rec_flag = 1
651
652 --
653 -- Determine request return code
654 --
655
656 RETCODE := l_RETCODE;
657 IF ( l_RETCODE = G_SUCCESS ) THEN
658 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_SUCCESS');
659 ELSIF ( l_RETCODE = G_WARNING ) THEN
660 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_WARNING');
661 ELSE
662 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
663 END IF;
664
665 EXCEPTION
666
667 WHEN Processing_Error THEN
668 RETCODE := G_ERROR;
669 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
670
671 -- Write all accumulated messages
672 INV_ITEM_MSG.Write_List (p_delete => TRUE);
673
674 -- Check of commit
675 IF ( FND_API.To_Boolean(l_commit) ) THEN
676 COMMIT WORK;
677 END IF;
678
679 WHEN others THEN
680 RETCODE := G_ERROR;
681 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
682
683 l_err_text := SUBSTRB(SQLERRM, 1,240);
684
685 INV_ITEM_MSG.Add_Message
686 ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
687 , p_token1 => 'PACKAGE_NAME'
688 , p_value1 => G_PKG_NAME
689 , p_token2 => 'PROCEDURE_NAME'
690 , p_value2 => l_api_name
691 , p_token3 => 'ERROR_TEXT'
692 , p_value3 => l_err_text
693 , p_transaction_id => l_transaction_id
694 );
695
696 -- Write all accumulated messages
697 INV_ITEM_MSG.Write_List (p_delete => TRUE);
698
699 -- Check of commit
700 IF ( FND_API.To_Boolean(l_commit) ) THEN
701 COMMIT WORK;
702 END IF;
703
704 END process_Item_Catalog_grp_recs;
705 ------------------------------------------------------------------------------
706
707 ------------------------------ delete_OI_records -----------------------------
708
709 PROCEDURE delete_OI_records
710 (
711 p_commit IN VARCHAR2
712 , p_rec_set_id IN NUMBER
713 , x_return_status OUT NOCOPY VARCHAR2
714 )
715 IS
716 l_api_name CONSTANT VARCHAR2(30) := 'delete_OI_records';
717 Mctx INV_ITEM_MSG.Msg_Ctx_type;
718
719 l_del_process_flag NUMBER := 7; -- process_flag value for records to be deleted
720 BEGIN
721
722 Mctx.Package_Name := G_PKG_NAME;
723 Mctx.Procedure_Name := l_api_name;
724
725 INV_ITEM_MSG.Debug(Mctx, 'begin');
726
727 -- Initialize API return status to success
728 x_return_status := FND_API.g_RET_STS_SUCCESS;
729
730 LOOP
731 DELETE FROM mtl_desc_elem_val_interface
732 WHERE set_process_id = p_rec_set_id
733 AND process_flag = l_del_process_flag
734 AND rownum < G_ROWS_TO_COMMIT;
735
736 EXIT WHEN SQL%NOTFOUND;
737
738 INV_ITEM_MSG.Debug(Mctx, 'deleted ' || TO_CHAR(SQL%ROWCOUNT) || ' record(s)');
739
740 -- Check of commit
741 IF ( FND_API.To_Boolean(p_commit) ) THEN
742 COMMIT WORK;
743 END IF;
744
745 END LOOP;
746
747 EXCEPTION
748
749 WHEN others THEN
750 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
751
752 INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
753
754 -- Check of commit
755 IF ( FND_API.To_Boolean(p_commit) ) THEN
756 COMMIT WORK;
757 END IF;
758
759 END delete_OI_records;
760 ------------------------------------------------------------------------------
761
762 END INV_ITEM_CATALOG_ELEM_PUB;