[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 2007/05/28 11:48:05 anmurali 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 AND rownum < 2
276 AND (mdei.inventory_item_id = mdei_dup.inventory_item_id
277 OR mdei.item_number = mdei_dup.item_number )
278 AND mdei.element_name = mdei_dup.element_name
279 )
280 FOR UPDATE OF mdei_dup.transaction_id;
281
282 --
283 -- Cursor for the main loop (Create_Catalog_group_Assignment)
284 --
285 CURSOR icoi_csr
286 IS
287 SELECT
288 mdei.rowid, mdei.transaction_id
289 , mdei.inventory_item_id
290 , mdei.element_name, mdei.element_value
291 , mdei.element_sequence, mdei.item_number
292 , mdei.default_element_flag
293 FROM
294 mtl_desc_elem_val_interface mdei
295 WHERE
296 mdei.set_process_id = g_xset_id
297 AND mdei.process_flag IN (1, 2, 4) --R12C
298 ORDER BY mdei.item_number,mdei.inventory_item_id
299 FOR UPDATE OF mdei.transaction_id;
300
301 l_process_flag NUMBER;
302
303 Processing_Error EXCEPTION;
304
305 ret_code NUMBER := 0;
306 l_err_text VARCHAR2(2000);
307
308 l_commit VARCHAR2(1);
309 l_return_status VARCHAR2(1); -- := fnd_api.g_MISS_CHAR
310 l_msg_count NUMBER;
311 l_msg_data VARCHAR2(2000);
312
313 l_msg_name VARCHAR2(2000);
314
315 l_RETCODE NUMBER; -- G_SUCCESS, G_WARNING, G_ERROR
316 l_column_name VARCHAR2(30);
317 l_token VARCHAR2(30);
318 l_token_value VARCHAR2(30);
319 l_element_name VARCHAR2(200);
320 l_element_value VARCHAR2(200);
321 l_default_element_flag VARCHAR2(1);
322 l_transaction_id NUMBER;
323 l_inventory_item_id NUMBER;
324 l_item_number VARCHAR2(200);
325 flex_id NUMBER;
326 item_id NUMBER;
327
328 BEGIN
329
330 INV_ITEM_MSG.Initialize;
331
332 INV_ITEM_MSG.set_Message_Mode ('CP_LOG');
333
334 -- Set message level
335
336 -- INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Statement);
337 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
338
339 -- Define message context
340 Mctx.Package_Name := G_PKG_NAME;
341 Mctx.Procedure_Name := l_api_name;
342
343 INV_ITEM_MSG.Debug(Mctx, 'start rec_set_id = '|| TO_CHAR(p_rec_set_id));
344
345 -- Set global package variables for the current import session
346
347 g_xset_id := p_rec_set_id;
348
349 g_User_id := NVL(p_user_id, FND_GLOBAL.user_id );
350 g_Login_id := NVL(p_login_id, FND_GLOBAL.login_id );
351 g_Prog_appid := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
352 g_Prog_id := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
353 g_Request_id := NVL(p_request_id, FND_GLOBAL.conc_request_id );
354
355 INV_ITEM_MSG.g_Table_Name := 'MTL_DESC_ELEM_VAL_INTERFACE';
356
357 INV_ITEM_MSG.g_User_id := g_User_id;
358 INV_ITEM_MSG.g_Login_id := g_Login_id;
359 INV_ITEM_MSG.g_Prog_appid := g_Prog_appid;
360 INV_ITEM_MSG.g_Prog_id := g_Prog_id;
361 INV_ITEM_MSG.g_Request_id := g_Request_id;
362
363 IF ( p_commit_flag = 1 ) THEN
364 l_commit := fnd_api.g_TRUE;
365 ELSE
366 l_commit := fnd_api.g_FALSE;
367 END IF;
368
369 l_RETCODE := G_SUCCESS;
370
371 ---------------------------------------------------------------------------------------
372 -- Process step 1: Set process flag to 2 --
373 ---------------------------------------------------------------------------------------
374
375 INV_ITEM_MSG.Debug(Mctx, ' Set process flag to 2');
376
377 UPDATE mtl_desc_elem_val_interface mdei
378 SET process_flag = 2
379 WHERE
380 mdei.set_process_id = g_xset_id
381 AND mdei.process_flag = 1;
382
383 ------------------------------------------------------------------------------------------
384 -- Process step 2: Loop through item catlog group elements interface records --
385 -- (a) convert the item_number to irem_id --
386 -- (b) update the interface records accordingly --
387 ------------------------------------------------------------------------------------------
388 INV_ITEM_MSG.Debug(Mctx, 'starting the ICatalogOI loop to convert the item_number');
389
390 IF p_upload_rec_flag = 1 THEN
391
392 SELECT mtl_system_items_interface_s.NEXTVAL
393 INTO l_transaction_id
394 FROM dual;
395
396 FOR icoi_rec1 IN icoi_csr LOOP
397
398 -- Process flag for the current record is initially set to 4 (validation success).
399 -- May be changed to 3 or 5, if any errors occur during validation.
400 l_process_flag := 4;
401 l_inventory_item_id := NULL;
402 --
403 -- Assign missing inventory_item_id from item_number
404 --
405
406 l_return_status := fnd_api.g_RET_STS_SUCCESS;
407
408 item_id := icoi_rec1.inventory_item_id;
409 l_item_number := icoi_rec1.item_number;
410
411 IF ( l_item_number IS NOT NULL ) THEN
412 ret_code := INVPUOPI.mtl_pr_parse_item_name (
413 l_item_number,
414 flex_id,
415 l_err_text );
416 IF ( ret_code = 0 ) THEN
417 l_inventory_item_id := flex_id;
418 IF ((item_id IS NOT NULL)AND
419 (l_inventory_item_id <> item_id)) THEN
420 l_return_status := fnd_api.g_RET_STS_ERROR;
421 l_msg_name := 'INV_CEOI_ITEM_NUM_ID_MISMATCH';
422 l_token := 'VALUE1';
423 l_token_value := l_item_number;
424 l_column_name := 'ITEM_NUMBER';
425 l_token := 'VALUE2';
426 l_token_value := item_id;
427 l_column_name := 'INVENTORY_ITEM_ID';
428 END IF;
429 ELSE
430 l_return_status := fnd_api.g_RET_STS_ERROR;
431 l_msg_name := 'INV_ICOI_INVALID_ITEM_NUMBER';
432 l_token := 'VALUE';
433 l_token_value := l_item_number;
434 l_column_name := 'ITEM_NUMBER';
435 END IF;
436
437 ELSIF ( item_id IS NULL )THEN
438 l_return_status := fnd_api.g_RET_STS_ERROR;
439 l_msg_name := 'INV_CEOI_MISS_ITEM_NUMBER';
440 l_token := fnd_api.g_MISS_CHAR;
441 l_token_value := l_item_number||item_id;
442 l_column_name := 'ITEM_NUMBER';
443 END IF;
444 IF (l_return_status = fnd_api.g_RET_STS_ERROR) THEN
445 INV_ITEM_MSG.Add_Message
446 ( p_Msg_Name => l_msg_name
447 , p_token1 => l_token
448 , p_value1 => l_token_value
449 , p_column_name => l_column_name
450 , p_transaction_id => l_transaction_id
451 );
452 UPDATE mtl_desc_elem_val_interface
453 SET
454 transaction_id = l_transaction_id,
455 request_id = g_request_id,
456 process_flag = 3
457 WHERE
458 CURRENT OF icoi_csr;
459 ELSIF ( l_inventory_item_id IS NOT NULL )THEN
460 UPDATE mtl_desc_elem_val_interface
461 SET
462 inventory_item_id = l_inventory_item_id
463 WHERE
464 CURRENT OF icoi_csr;
465 END IF;
466
467 END LOOP; -- icoi_rec1
468
469 -- Check of commit
470 IF ( FND_API.To_Boolean(l_commit) ) THEN
471 COMMIT WORK;
472 END IF;
473 -- Write all accumulated messages
474 INV_ITEM_MSG.Write_List (p_delete => TRUE);
475
476 ------------------------------------------------------------------------------------------
477 -- Process step 3: Loop through item catlog group elements interface records --
478 -- (a) Check for duplicate records in the interface table table --
479 -- (b) update the duplicate interface records process_flag --
480 ------------------------------------------------------------------------------------------
481 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step3');
482
483 SELECT mtl_system_items_interface_s.NEXTVAL
484 INTO l_transaction_id
485 FROM dual;
486
487 FOR icoi_rec_dup IN icoi_csr_dup LOOP --{
488
489 INV_ITEM_MSG.Add_Message
490 ( p_Msg_Name => 'INV_CEOI_DUP_ELEM_REC'
491 , p_token1 => 'ELEMENT_NAME'
492 , p_value1 => icoi_rec_dup.ELEMENT_NAME
493 , p_transaction_id => l_transaction_id
494 );
495
496
497 UPDATE mtl_desc_elem_val_interface
498 SET
499 transaction_id = l_transaction_id,
500 request_id = g_request_id,
501 process_flag = 3
502 WHERE
503 CURRENT OF icoi_csr_dup;
504
505 END LOOP; --} icoi_csr_dup
506 END IF; -- Upload Rec Flag is 1
507 -- Check of commit
508 IF ( FND_API.To_Boolean(l_commit) ) THEN
509 COMMIT WORK;
510 END IF;
511 -- Write all accumulated messages
512 INV_ITEM_MSG.Write_List (p_delete => TRUE);
513
514 ------------------------------------------------------------------------------------------
515 -- Process step 4: Loop through item catlog group elements interface records --
516 -- (a) call the API to create item catalog group element values assignment record in the production table --
517 -- (b) update the current interface record process_flag and other converted values --
518 ------------------------------------------------------------------------------------------
519
520 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICatalogOI loop step4');
521
522 SELECT mtl_system_items_interface_s.NEXTVAL
523 INTO l_transaction_id
524 FROM dual;
525
526 FOR icoi_rec IN icoi_csr LOOP --{
527
528 -- Process flag for the current record is initially set to 4 (validation success).
529 -- May be changed to 3 or 5, if any errors occur during validation.
530 l_process_flag := 4;
531
532 --
533 -- Assign missing inventory_item_id from item_number
534 --
535
536 l_return_status := fnd_api.g_RET_STS_SUCCESS;
537 l_inventory_item_id := icoi_rec.inventory_item_id;
538 l_item_number := icoi_rec.item_number;
539 l_element_value := icoi_rec.element_value;
540 l_element_name := icoi_rec.element_name;
541 l_default_element_flag := icoi_rec.default_element_flag;
542 --
543 -- If value-to-id conversions are successful,
544 -- call the API to process item catalog element values
545
546 IF ( l_process_flag = 4 AND p_upload_rec_flag = 1 ) THEN
547
548 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATALOG_ELE_PVT.Create_Catalog_group_ele_Assignment:'||l_element_name);
549
550 INV_ITEM_CATALOG_ELE_PVT.Catalog_Grp_Ele_Val_Assignment
551 (
552 p_api_version => 1.0
553 , p_init_msg_list => fnd_api.g_TRUE
554 , p_commit => fnd_api.g_FALSE
555 , p_validation_level => g_VALIDATE_IDS
556 , p_inventory_item_id => l_inventory_item_id
557 , p_item_number => l_item_number
558 , p_element_name => l_element_name
559 , p_element_value => l_element_value
560 , p_default_element_flag => l_default_element_flag
561 , x_return_status => l_return_status
562 , x_msg_count => l_msg_count
563 , x_msg_data => l_msg_data
564 );
565
566 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
567 l_process_flag := 7;
568 ELSE
569 l_process_flag := 3;
570 INV_ITEM_MSG.Debug(Mctx, 'error in Catalog_Grp_Ele_Val_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
571 l_RETCODE := G_WARNING;
572 END IF; -- l_return_status
573
574 -- If unexpected error in Catalog_Grp_Ele_Val_Assignment API, stop the processing
575 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
576 l_RETCODE := G_ERROR;
577 RAISE Processing_Error;
578 END IF;
579
580 END IF; -- process_flag = 4 AND p_upload_rec_flag = 1
581
582 -- Write all accumulated messages
583 INV_ITEM_MSG.Write_List (p_delete => TRUE);
584
585 --
586 -- Update the current interface record
587 --
588
589 INV_ITEM_MSG.Debug(Mctx, 'update interface record');
590
591 UPDATE mtl_desc_elem_val_interface
592 SET
593 transaction_id = l_transaction_id
594 , inventory_item_id = l_inventory_item_id
595 , item_number = l_item_number
596 , process_flag = l_process_flag
597 , program_application_id = g_prog_appid
598 , program_id = g_prog_id
599 , program_update_date= SYSDATE
600 , request_id = g_request_id
601 , last_update_date = SYSDATE
602 , last_updated_by = g_user_id
603 , last_update_login = g_login_id
604 WHERE
605 CURRENT OF icoi_csr;
606
607 END LOOP; --} icoi_csr
608
609 -- Check of commit
610 IF ( FND_API.To_Boolean(l_commit) ) THEN
611 COMMIT WORK;
612 END IF;
613
614 --
615 -- Delete successfully processed records from the interface table
616 --
617
618 IF (p_delete_rec_flag = 1) THEN
619
620 INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');
621
622 INV_ITEM_CATALOG_ELEM_PUB.delete_OI_records
623 ( p_commit => l_commit
624 , p_rec_set_id => g_xset_id
625 , x_return_status => l_return_status
626 );
627
628 INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);
629
630 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
631 RAISE Processing_Error;
632 END IF;
633
634 -- Write all accumulated messages
635 INV_ITEM_MSG.Write_List (p_delete => TRUE);
636
637 END IF; -- p_delete_rec_flag = 1
638
639 --
640 -- Determine request return code
641 --
642
643 RETCODE := l_RETCODE;
644 IF ( l_RETCODE = G_SUCCESS ) THEN
645 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_SUCCESS');
646 ELSIF ( l_RETCODE = G_WARNING ) THEN
647 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_WARNING');
648 ELSE
649 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
650 END IF;
651
652 EXCEPTION
653
654 WHEN Processing_Error THEN
655 RETCODE := G_ERROR;
656 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
657
658 -- Write all accumulated messages
659 INV_ITEM_MSG.Write_List (p_delete => TRUE);
660
661 -- Check of commit
662 IF ( FND_API.To_Boolean(l_commit) ) THEN
663 COMMIT WORK;
664 END IF;
665
666 WHEN others THEN
667 RETCODE := G_ERROR;
668 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICG_DESC_ELEM_FAILURE');
669
670 l_err_text := SUBSTRB(SQLERRM, 1,240);
671
672 INV_ITEM_MSG.Add_Message
673 ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
674 , p_token1 => 'PACKAGE_NAME'
675 , p_value1 => G_PKG_NAME
676 , p_token2 => 'PROCEDURE_NAME'
677 , p_value2 => l_api_name
678 , p_token3 => 'ERROR_TEXT'
679 , p_value3 => l_err_text
680 , p_transaction_id => l_transaction_id
681 );
682
683 -- Write all accumulated messages
684 INV_ITEM_MSG.Write_List (p_delete => TRUE);
685
686 -- Check of commit
687 IF ( FND_API.To_Boolean(l_commit) ) THEN
688 COMMIT WORK;
689 END IF;
690
691 END process_Item_Catalog_grp_recs;
692 ------------------------------------------------------------------------------
693
694 ------------------------------ delete_OI_records -----------------------------
695
696 PROCEDURE delete_OI_records
697 (
698 p_commit IN VARCHAR2
699 , p_rec_set_id IN NUMBER
700 , x_return_status OUT NOCOPY VARCHAR2
701 )
702 IS
703 l_api_name CONSTANT VARCHAR2(30) := 'delete_OI_records';
704 Mctx INV_ITEM_MSG.Msg_Ctx_type;
705
706 l_del_process_flag NUMBER := 7; -- process_flag value for records to be deleted
707 BEGIN
708
709 Mctx.Package_Name := G_PKG_NAME;
710 Mctx.Procedure_Name := l_api_name;
711
712 INV_ITEM_MSG.Debug(Mctx, 'begin');
713
714 -- Initialize API return status to success
715 x_return_status := FND_API.g_RET_STS_SUCCESS;
716
717 LOOP
718 DELETE FROM mtl_desc_elem_val_interface
719 WHERE set_process_id = p_rec_set_id
720 AND process_flag = l_del_process_flag
721 AND rownum < G_ROWS_TO_COMMIT;
722
723 EXIT WHEN SQL%NOTFOUND;
724
725 INV_ITEM_MSG.Debug(Mctx, 'deleted ' || TO_CHAR(SQL%ROWCOUNT) || ' record(s)');
726
727 -- Check of commit
728 IF ( FND_API.To_Boolean(p_commit) ) THEN
729 COMMIT WORK;
730 END IF;
731
732 END LOOP;
733
734 EXCEPTION
735
736 WHEN others THEN
737 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
738
739 INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
740
741 -- Check of commit
742 IF ( FND_API.To_Boolean(p_commit) ) THEN
743 COMMIT WORK;
744 END IF;
745
746 END delete_OI_records;
747 ------------------------------------------------------------------------------
748
749 END INV_ITEM_CATALOG_ELEM_PUB;