[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_CATEGORY_OI
Source
1 PACKAGE BODY INV_ITEM_CATEGORY_OI AS
2 /* $Header: INVCICIB.pls 120.22.12010000.2 2008/07/29 13:39:28 ptkumar ship $ */
3
4 ---------------------- Package variables and constants -----------------------
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_CATEGORY_OI';
7
8 G_SUCCESS CONSTANT NUMBER := 0;
9 G_WARNING CONSTANT NUMBER := 1;
10 G_ERROR CONSTANT NUMBER := 2;
11
12 G_ROWS_TO_COMMIT CONSTANT NUMBER := 500;
13
14 ------------------------------------------------------------------------------
15
16 -------------------------Update_Sync_Records----------------------------------
17 PROCEDURE UPDATE_SYNC_RECORDS(p_inventory_item_id IN NUMBER,
18 p_organization_id IN NUMBER,
19 p_category_set_id IN NUMBER,
20 p_transaction_id IN NUMBER,
21 p_row_id IN ROWID,
22 x_old_category_id OUT NOCOPY NUMBER,
23 x_transaction_type OUT NOCOPY VARCHAR2,
24 x_return_status OUT NOCOPY NUMBER)
25 IS
26
27 CURSOR c_cat_assign_exists (cp_item_id NUMBER,
28 cp_org_id NUMBER,
29 cp_cat_set_id NUMBER)
30 IS
31 SELECT category_id FROM mtl_item_categories
32 WHERE inventory_item_id = cp_item_id
33 AND organization_id = cp_org_id
34 AND category_set_id = cp_cat_set_id;
35
36 CURSOR c_mult_item_flag (cp_cat_set_id NUMBER)
37 IS
38 SELECT mult_item_cat_assign_flag
39 FROM mtl_category_sets_b
40 WHERE category_set_id = cp_cat_set_id;
41
42 l_category_id NUMBER := 0;
43 l_old_category_id NUMBER;
44 l_mult_item_assign_flag VARCHAR2(1);
45 l_msg_name VARCHAR2(2000);
46 l_column_name VARCHAR2(30);
47 l_token VARCHAR2(30);
48 l_token_value VARCHAR2(81);
49 l_transaction_type VARCHAR2(10);
50 l_return_status NUMBER;
51
52 BEGIN
53 OPEN c_cat_assign_exists( cp_item_id => p_inventory_item_id,
54 cp_org_id => p_organization_id,
55 cp_cat_set_id => p_category_set_id);
56 FETCH c_cat_assign_exists INTO l_category_id;
57
58 IF c_cat_assign_exists%FOUND THEN
59 OPEN c_mult_item_flag (cp_cat_set_id => p_category_set_id);
60 FETCH c_mult_item_flag INTO l_mult_item_assign_flag;
61 CLOSE c_mult_item_flag;
62
63 IF l_mult_item_assign_flag = 'Y' THEN
64 l_msg_name := 'INV_MULT_SYNC_INVALID';
65 l_token := 'CATEGORY_SET_ID';
66 l_token_value := TO_CHAR(p_category_set_id);
67 l_column_name := 'CATEGORY_SET_ID';
68 l_return_status := 3;
69
70 INV_ITEM_MSG.Add_Message
71 ( p_Msg_Name => l_msg_name
72 , p_token1 => l_token
73 , p_value1 => l_token_value
74 , p_transaction_id => p_transaction_id
75 , p_column_name => l_column_name
76 );
77
78 UPDATE mtl_item_categories_interface
79 SET process_flag = 3
80 WHERE rowid = p_row_id;
81
82 ELSE
83 UPDATE mtl_item_categories_interface
84 SET old_category_id = l_category_id,
85 transaction_type = 'UPDATE'
86 WHERE rowid = p_row_id;
87
88 l_transaction_type := 'UPDATE';
89 l_old_category_id := l_category_id;
90 END IF;
91 ELSE
92 UPDATE mtl_item_categories_interface
93 SET transaction_type = 'CREATE'
94 WHERE rowid = p_row_id;
95
96 l_transaction_type := 'CREATE';
97 END IF;
98 CLOSE c_cat_assign_exists;
99
100 x_transaction_type := l_transaction_type;
101 x_return_status := l_return_status;
102 x_old_category_id:= l_old_category_id;
103
104 END UPDATE_SYNC_RECORDS;
105
106 ------------------------ process_Item_Category_records -----------------------
107
108 PROCEDURE process_Item_Category_records
109 (
110 ERRBUF OUT NOCOPY VARCHAR2
111 , RETCODE OUT NOCOPY NUMBER
112 , p_rec_set_id IN NUMBER
113 , p_validate_rec_flag IN NUMBER DEFAULT 1
114 , p_upload_rec_flag IN NUMBER := 1
115 , p_delete_rec_flag IN NUMBER := 1
116 , p_commit_flag IN NUMBER := 1
117 , p_prog_appid IN NUMBER := NULL
118 , p_prog_id IN NUMBER := NULL
119 , p_request_id IN NUMBER := NULL
120 , p_user_id IN NUMBER := NULL
121 , p_login_id IN NUMBER := NULL
122 )
123 IS
124 l_api_name CONSTANT VARCHAR2(30) := 'process_Item_Category_records';
125 Mctx INV_ITEM_MSG.Msg_Ctx_type;
126
127 --
128 -- Select records to flag missing or invalid organization_id
129 --
130
131 CURSOR miss_org_id_csr
132 IS
133 SELECT
134 mici.rowid, mici.transaction_id
135 , mici.transaction_type
136 , mici.organization_id, mici.inventory_item_id
137 , mici.category_set_id, mici.category_id
138 , mici.organization_code, mici.item_number
139 , mici.category_set_name, mici.category_name
140 FROM
141 mtl_item_categories_interface mici
142 WHERE
143 set_process_id = g_xset_id
144 AND process_flag = 1
145 AND ( organization_id IS NULL
146 OR ( organization_id IS NOT NULL
147 AND NOT EXISTS
148 ( SELECT mp.organization_id
149 FROM mtl_parameters mp
150 WHERE mp.organization_id = mici.organization_id
151 )
152 )
153 )
154 FOR UPDATE OF mici.transaction_id;
155
156 --
157 -- Cursor for the main loop (Create_Category_Assignment)
158 --
159
160 CURSOR icoi_csr
161 IS
162 SELECT
163 mici.rowid, mici.transaction_id
164 , mici.transaction_type, mici.process_flag
165 , mici.organization_id, mici.inventory_item_id
166 , mici.category_set_id, mici.category_id
167 , mici.organization_code, mici.item_number
168 , mici.category_set_name, mici.category_name
169 , mici.old_category_id, mici.old_category_name --* Added for Bug #3991044
170 , mici.created_by
171 FROM
172 mtl_item_categories_interface mici
173 , mtl_parameters mp
174 WHERE
175 mici.set_process_id = g_xset_id
176 AND mici.organization_id = mp.organization_id
177 AND mici.process_flag IN (1, 2, 4) --R12C
178 ORDER BY
179 mp.master_organization_id ASC
180 , DECODE(mici.transaction_type, 'DELETE', 1, 'UPDATE', 2, 'CREATE', 3, 4) ASC
181 , DECODE(mp.organization_id, mp.master_organization_id, 1, 2) ASC
182 , mp.organization_id ASC
183 FOR UPDATE OF mici.transaction_id;
184
185 --
186 -- Select records to get category_set_id
187 --
188
189 CURSOR category_set_name_csr
190 ( p_category_set_name IN VARCHAR2
191 )
192 IS
193 SELECT category_set_id, structure_id
194 FROM mtl_category_sets_vl
195 WHERE category_set_name = p_category_set_name;
196
197 CURSOR msi_item_number_csr (cp_item_number IN VARCHAR2,
198 cp_organization_id IN NUMBER)
199 IS
200 SELECT inventory_item_id
201 FROM mtl_system_items_b_kfv
202 WHERE concatenated_segments = cp_item_number
203 AND organization_id = cp_organization_id;
204
205 CURSOR msii_item_number_csr (cp_item_number IN VARCHAR2,
206 cp_organization_id IN NUMBER,
207 cp_xset_id IN NUMBER)
208 IS
209 SELECT inventory_item_id
210 FROM mtl_system_items_interface
211 WHERE item_number = cp_item_number
212 AND organization_id = cp_organization_id
213 AND set_process_id = cp_xset_id
214 AND process_flag IN (1,2,4);
215
216 /*
217 -- To assign inventory_item_id from item_number
218
219 CURSOR miss_item_id_csr
220 IS
221 -- SELECT DISTINCT item_number, organization_id
222 SELECT rowid, transaction_id
223 , item_number, organization_id
224 FROM mtl_item_categories_interface
225 WHERE set_process_id = g_xset_id
226 AND inventory_item_id IS NULL
227 AND item_number IS NOT NULL
228 AND category_set_id IS NOT NULL
229 AND organization_id IS NOT NULL;
230
231 -- To assign category_id from category_name
232
233 CURSOR miss_category_id_csr
234 IS
235 SELECT rowid, transaction_id
236 , category_name, organization_id, category_set_id
237 FROM mtl_item_categories_interface
238 WHERE set_process_id = g_xset_id
239 AND category_id IS NULL
240 AND category_name IS NOT NULL
241 AND category_set_id IS NOT NULL
242 AND organization_id IS NOT NULL
243 AND process_flag = l_process_flag_1;
244 */
245
246
247 -- pre-validate missing category_set_id, category_id, organization_id
248 -- (not used)
249 /*
250 CURSOR miss_id_csr IS
251 SELECT i.transaction_id, i.organization_id
252 FROM mtl_item_categories_interface i
253 WHERE i.process_flag = l_process_flag_2
254 AND set_process_id = g_xset_id
255 AND ( i.organization_id = org_id OR all_org = l_All_Org )
256 AND (
257 (NOT EXISTS (select m.category_set_id
258 from mtl_category_sets_b m
259 where m.category_set_id = i.category_set_id )
260 )
261 OR
262 (NOT EXISTS (select m.category_id
263 from mtl_categories_b m,
264 mtl_category_sets_b ms
265 where m.category_id = i.category_id
266 and m.structure_id = ms.structure_id
267 and i.category_set_id = ms.category_set_id)
268 )
269 OR
270 (NOT EXISTS (select organization_id
271 from ORG_ORGANIZATION_DEFINITIONS OOD
272 where OOD.organization_id = i.organization_id)
273 )
274 );
275 */
276 /* R12 Business Events Enh
277 Populate mtl_item_bulkload_recs*/
278 Cursor populate_catg_bulkloadrecs(
279 cp_request_id NUMBER
280 ,cp_set_id NUMBER) IS
281 SELECT mic.REQUEST_ID
282 ,mic.INVENTORY_ITEM_ID
283 ,mic.ORGANIZATION_ID
284 ,mic.CATEGORY_SET_ID
285 ,mic.CATEGORY_ID
286 ,mic.TRANSACTION_TYPE
287 ,mic.CREATION_DATE
288 ,mic.CREATED_BY
289 ,mic.LAST_UPDATE_DATE
290 ,mic.LAST_UPDATED_BY
291 ,mic.LAST_UPDATE_LOGIN
292 FROM MTL_ITEM_CATEGORIES_INTERFACE mic
293 WHERE REQUEST_ID = cp_request_id
294 AND set_process_id = cp_set_id
295 AND process_flag = 7;
296
297 l_process_flag NUMBER;
298
299 Processing_Error EXCEPTION;
300
301 ret_code NUMBER := 0;
302 l_err_text VARCHAR2(2000);
303
304 l_commit VARCHAR2(1);
305 l_return_status VARCHAR2(1); -- := fnd_api.g_MISS_CHAR
306 l_return_status_flag NUMBER;
307 l_msg_count NUMBER;
308 l_msg_data VARCHAR2(2000);
309
310 l_msg_name VARCHAR2(2000);
311
312 l_RETCODE NUMBER; -- G_SUCCESS, G_WARNING, G_ERROR
313 l_column_name VARCHAR2(30);
314 l_token VARCHAR2(30);
315 l_token_value VARCHAR2(81); -- Bug # 3516745. Increased the
316 -- size from 30 to 81.
317
318 l_transaction_id NUMBER;
319 l_transaction_type VARCHAR2(10);
320 l_organization_id NUMBER;
321 l_inventory_item_id NUMBER;
322 l_category_set_id NUMBER;
323 l_structure_id NUMBER;
324 l_category_id NUMBER;
325 flex_id NUMBER;
326 l_has_privilege VARCHAR2(1) := 'F';
327 l_udex_catalog_id NUMBER;
328 l_gpc_catalog_id NUMBER; --Bug 5517473
329
330 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
331
332 --2698140 : Gather stats before running the IOI
333 l_schema VARCHAR2(30);
334 l_status VARCHAR2(1);
335 l_industry VARCHAR2(1);
336 l_records NUMBER(10);
337
338 --* Variables added for Bug #3991044
339 l_Reccount NUMBER := 0;
340 l_old_category_id NUMBER;
341 --* End of Bug #3991044
342 l_records_updated VARCHAR2(1); --bUG 4527222
343
344 l_item_number VARCHAR2(40); --5522789
345 l_ret_old_category_id NUMBER;
346 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
347
348 BEGIN
349
350 --Start 2698140 : Gather stats before running
351 --When called through pub pacs, prog_id will be null or -1.
352 -- IF NVL(fnd_global.conc_program_id,-1) <> -1 THEN Bug:3547401
353 IF NVL(p_prog_id,-1) <> -1 THEN
354
355 SELECT count(*) INTO l_records
356 FROM mtl_item_categories_interface
357 WHERE set_process_id = p_rec_set_id
358 AND process_flag IN (1,2,4); --R12C
359
360 -- Bug 6983407 Collect statistics only if the no. of records is bigger than the profile
361 -- option threshold
362 IF l_records > nvl(fnd_profile.value('EGO_GATHER_STATS'),100) AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
363 IF l_schema IS NOT NULL THEN
364 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_ITEM_CATEGORIES_INTERFACE');
365 END IF;
366 END IF;
367 END IF;
368 --End 2698140 : Gather stats before running
369
370 -- It is required to check if the category update is for GDSN item and that too GDSN Category set id ..
371 -- For which case category_set_id of Udex Catalog is fetched for further use.
372 BEGIN
373 SELECT SUM(
374 DECODE(FUNCTIONAL_AREA_ID,12,CATEGORY_SET_ID,0)) udex_catalog
375 ,SUM(
376 DECODE(FUNCTIONAL_AREA_ID,21,CATEGORY_SET_ID,0)) gpc_catalog
377 INTO l_udex_catalog_id , l_gpc_catalog_id
378 FROM MTL_DEFAULT_CATEGORY_SETS
379 WHERE FUNCTIONAL_AREA_ID IN (12,21); --Bug 5517473 added functional area 21
380 EXCEPTION
381 WHEN OTHERS THEN
382 l_udex_catalog_id := NULL;
383 END;
384
385
386 INV_ITEM_MSG.Initialize;
387
388 INV_ITEM_MSG.set_Message_Mode ('CP_LOG');
389
390 -- Set message level
391
392 -- INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Statement);
393 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
394
395 -- Define message context
396 Mctx.Package_Name := G_PKG_NAME;
397 Mctx.Procedure_Name := l_api_name;
398
399 IF (l_debug = 1) THEN
400 INV_ITEM_MSG.Debug(Mctx, 'start rec_set_id = '|| TO_CHAR(p_rec_set_id));
401 END IF;
402
403 -- Set global package variables for the current import session
404
405 g_xset_id := p_rec_set_id;
406
407 g_User_id := NVL(p_user_id, FND_GLOBAL.user_id );
408 g_Login_id := NVL(p_login_id, FND_GLOBAL.login_id );
409 g_Prog_appid := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
410 g_Prog_id := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
411 g_Request_id := NVL(p_request_id, FND_GLOBAL.conc_request_id );
412
413 INV_ITEM_MSG.g_Table_Name := 'MTL_ITEM_CATEGORIES_INTERFACE';
414
415 INV_ITEM_MSG.g_User_id := g_User_id;
416 INV_ITEM_MSG.g_Login_id := g_Login_id;
417 INV_ITEM_MSG.g_Prog_appid := g_Prog_appid;
418 INV_ITEM_MSG.g_Prog_id := g_Prog_id;
419 INV_ITEM_MSG.g_Request_id := g_Request_id;
420
421 IF ( p_commit_flag = 1 ) THEN
422 l_commit := fnd_api.g_TRUE;
423 ELSE
424 l_commit := fnd_api.g_FALSE;
425 END IF;
426
427 l_RETCODE := G_SUCCESS;
428
429 ---------------------------------------------------------------------------------------
430 -- Process step 1: Populate organization ids from codes --
431 -- (a) convert organization_code to organization_id where organization_id IS NULL --
432 -- (b) use miss_org_id_csr to flag records with missing or invalid organization_id --
433 ---------------------------------------------------------------------------------------
434
435 -- Assign all missing organization_id from organization_code
436
437 IF (l_debug = 1) THEN
438 INV_ITEM_MSG.Debug(Mctx, 'assign all missing organization_id');
439 END IF;
440
441 UPDATE mtl_item_categories_interface mici
442 SET
443 ( mici.organization_id
444 , process_flag
445 ) =
446 ( SELECT mp.organization_id, DECODE(p_validate_rec_flag, 2, 1, 2)
447 FROM mtl_parameters mp
448 WHERE mp.organization_code = mici.organization_code
449 )
450 WHERE
451 mici.set_process_id = g_xset_id
452 AND mici.process_flag = 1
453 AND mici.organization_id IS NULL
454 AND mici.organization_code IS NOT NULL
455 AND EXISTS
456 ( SELECT mp2.organization_id
457 FROM mtl_parameters mp2
458 WHERE mp2.organization_code = mici.organization_code
459 );
460
461 -- For missing organization_id, update process_flag and log an error.
462 -- Also, assign transaction_id, request_id
463
464 FOR cr IN miss_org_id_csr LOOP
465
466 SELECT mtl_system_items_interface_s.NEXTVAL
467 INTO l_transaction_id
468 FROM dual;
469
470 UPDATE mtl_item_categories_interface
471 SET
472 -- transaction_id = mtl_system_items_interface_s.NEXTVAL
473 transaction_id = l_transaction_id
474 , request_id = g_request_id
475 , process_flag = 3
476 WHERE CURRENT OF miss_org_id_csr;
477 -- RETURNING transaction_id INTO l_transaction_id;
478
479 IF ( cr.organization_id IS NULL ) THEN
480 IF ( cr.organization_code IS NULL ) THEN
481 l_msg_name := 'INV_ICOI_MISS_ORG_CODE';
482 l_token := fnd_api.g_MISS_CHAR;
483 l_token_value := cr.organization_code;
484 ELSE
485 l_msg_name := 'INV_ICOI_INVALID_ORG_CODE';
486 l_token := 'VALUE';
487 l_token_value := cr.organization_code;
488 END IF;
489 l_column_name := 'ORGANIZATION_CODE';
490 ELSE
491 l_msg_name := 'INV_ICOI_INVALID_ORG_ID';
492 l_token := 'VALUE';
493 l_token_value := TO_CHAR(cr.organization_id);
494 l_column_name := 'ORGANIZATION_ID';
495 END IF;
496
497 l_RETCODE := G_WARNING;
498
499 INV_ITEM_MSG.Add_Message
500 ( p_Msg_Name => l_msg_name
501 , p_token1 => l_token
505 );
502 , p_value1 => l_token_value
503 , p_transaction_id => l_transaction_id
504 , p_column_name => l_column_name
506
507 END LOOP; -- miss_org_id_csr
508
509 -- Check of commit
510 IF ( FND_API.To_Boolean(l_commit) ) THEN
511 COMMIT WORK;
512 END IF;
513
514 -- Write all accumulated messages
515 --
516 INV_ITEM_MSG.Write_List (p_delete => TRUE);
517
518 --------------------------------------------------------------------------
519 -- Process step 2: Loop through item category interface records --
520 -- (a) convert category set, item, category values to ids, if missing --
521 -- (b) call the API to create item category assignment record in the --
522 -- production table --
523 -- (c) update the current interface record process_flag and other --
524 -- converted values --
525 --------------------------------------------------------------------------
526
527 IF (l_debug = 1) THEN
528 INV_ITEM_MSG.Debug(Mctx, 'starting the main ICOI loop');
529 END IF;
530
531 FOR icoi_rec IN icoi_csr LOOP --{
532
533 -- Process flag for the current record is initially set to 4
534 -- (validation success);
535 -- may be changed to 3 or 5, if any errors occur during validation.
536
537 IF p_validate_rec_flag = 1 THEN
538 l_process_flag := 4;
539 ELSE
540 l_process_flag := 1;
541 END IF;
542
543 SELECT mtl_system_items_interface_s.NEXTVAL
544 INTO l_transaction_id
545 FROM dual;
546
547 l_organization_id := icoi_rec.organization_id;
548
549 --
550 -- Validate transaction_type
551 --
552
553 l_return_status := fnd_api.g_RET_STS_SUCCESS;
554
555 l_transaction_type := UPPER(icoi_rec.transaction_type);
556
557 --*Included UPDATE trans type for Bug #3991044
558 IF ( l_transaction_type NOT IN ('CREATE', 'DELETE','UPDATE', 'SYNC') ) THEN
559 l_return_status := fnd_api.g_RET_STS_ERROR;
560 l_process_flag := 3;
561
562 l_RETCODE := G_WARNING;
563
564 l_msg_name := 'INV_ICOI_INVALID_TRANSACT_TYPE';
565 l_token := fnd_api.g_MISS_CHAR;
566 l_token_value := l_transaction_type;
567 l_column_name := 'TRANSACTION_TYPE';
568
569 INV_ITEM_MSG.Add_Message
570 ( p_Msg_Name => l_msg_name
571 , p_token1 => l_token
572 , p_value1 => l_token_value
573 , p_transaction_id => l_transaction_id
574 , p_column_name => l_column_name
575 );
576
577 END IF; -- l_transaction_type
578
579 --
580 -- Assign missing category_set_id from category_set_name
581 --
582
583 l_return_status := fnd_api.g_RET_STS_SUCCESS;
584
585 l_category_set_id := icoi_rec.category_set_id;
586
587 IF ( l_category_set_id IS NULL ) THEN
588
589 IF (l_debug = 1) THEN
590 INV_ITEM_MSG.Debug(Mctx, 'assign missing category_set_id');
591 END IF;
592
593 IF ( icoi_rec.category_set_name IS NOT NULL ) THEN
594 OPEN category_set_name_csr
595 ( p_category_set_name => icoi_rec.category_set_name );
596 FETCH category_set_name_csr INTO l_category_set_id, l_structure_id;
597 IF ( category_set_name_csr%NOTFOUND ) THEN
598 l_return_status := fnd_api.g_RET_STS_ERROR;
599 l_category_set_id := NULL;
600 l_msg_name := 'INV_ICOI_INVALID_CAT_SET_NAME';
601 l_token := 'VALUE';
602 l_token_value := icoi_rec.category_set_name;
603 l_column_name := 'CATEGORY_SET_NAME';
604 END IF;
605 CLOSE category_set_name_csr;
606 ELSE
607 l_return_status := fnd_api.g_RET_STS_ERROR;
608 l_msg_name := 'INV_ICOI_MISS_CAT_SET_NAME';
609 l_token := fnd_api.g_MISS_CHAR;
610 l_token_value := icoi_rec.category_set_name;
611 l_column_name := 'CATEGORY_SET_NAME';
612 END IF;
613
614 ELSE
615 -- Pass the Id validation to Create_Category_Assignment API
616 NULL;
617
618 --l_msg_name := 'INV_ICOI_INVALID_CAT_SET_ID';
619 --l_column_name := 'CATEGORY_SET_ID';
620
621 END IF; -- category_set_id
622
623 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
624 l_process_flag := 3;
625
626 l_RETCODE := G_WARNING;
627
628 INV_ITEM_MSG.Add_Message
629 ( p_Msg_Name => l_msg_name
630 , p_token1 => l_token
631 , p_value1 => l_token_value
632 , p_transaction_id => l_transaction_id
633 , p_column_name => l_column_name
634 );
635
636 END IF;
637
638 --* Code added for Bug #3991044
639 --
640 -- Assign missing old_category_id from old_category_name
641 --
642
643 l_return_status := fnd_api.g_RET_STS_SUCCESS;
644
648 IF ( l_transaction_type IN ('UPDATE', 'SYNC') AND (l_category_set_id IS NOT NULL)
645 l_old_category_id := icoi_rec.old_category_id;
646
647
649 AND (l_old_category_id IS NULL) ) THEN
650
651 IF (l_debug = 1) THEN
652 INV_ITEM_MSG.Debug(Mctx, 'assign missing old category_id');
653 END IF;
654
655 IF ( icoi_rec.old_category_name IS NOT NULL ) THEN
656 --* Fetching Category Id using Category Name
657 IF (l_debug = 1) THEN
658 INV_ITEM_MSG.Debug(Mctx, 'Fetching Category Id using Category Name');
659 END IF;
660 BEGIN
661 SELECT Category_id
662 INTO l_old_category_id
663 FROM Mtl_Categories_B_Kfv
664 WHERE Structure_Id = ( SELECT Structure_Id
665 FROM mtl_category_sets_vl
666 WHERE category_set_id = l_category_set_id )
667 AND Concatenated_Segments = icoi_rec.old_category_name;
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 l_return_status := fnd_api.g_RET_STS_ERROR;
671 l_msg_name := 'INV_ICOI_INVALID_CAT_NAME';
672 l_token := 'VALUE';
673 l_token_value := icoi_rec.old_category_name;
674 l_column_name := 'CATEGORY_NAME';
675 END;
676 ELSE
677 IF l_transaction_type = 'UPDATE' THEN
678 l_return_status := fnd_api.g_ret_sts_error;
679 l_msg_name := 'INV_ICOI_MISS_CAT_NAME';
680 l_token := fnd_api.G_MISS_CHAR;
681 l_token_value := icoi_rec.old_category_name;
682 l_column_name := 'CATEGORY_NAME';
683 END IF;
684 END IF; --Old Category Name not null
685 END IF; -- category_id
686
687 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
688 l_process_flag := 3;
689
690 l_RETCODE := G_WARNING;
691
692 INV_ITEM_MSG.Add_Message
693 ( p_Msg_Name => l_msg_name
694 , p_token1 => l_token
695 , p_value1 => l_token_value
696 , p_transaction_id => l_transaction_id
697 , p_column_name => l_column_name
698 );
699 END IF;
700 --* End of Bug #3991044
701
702 --
703 -- Assign missing category_id from category_name
704 --
705
706 l_return_status := fnd_api.g_RET_STS_SUCCESS;
707
708 l_category_id := icoi_rec.category_id;
709
710 -- The category_set_id must be known at this point
711
712 IF ( (l_category_set_id IS NOT NULL) AND (l_category_id IS NULL) ) THEN
713
714 IF (l_debug = 1) THEN
715 INV_ITEM_MSG.Debug(Mctx, 'assign missing category_id');
716 END IF;
717
718 IF ( icoi_rec.category_name IS NOT NULL ) THEN
719 -- commented for fixing 2636268
720 -- ret_code := INVPUOPI.mtl_pr_parse_flex_name
721 -- ( l_organization_id,
722 -- 'MCAT',
723 -- icoi_rec.category_name,
724 -- flex_id,
725 -- l_category_set_id,
726 -- l_err_text );
727 --
728 -- IF ( ret_code = 0 ) THEN
729 -- l_category_id := flex_id;
730 -- ELSE
731 BEGIN
732 -- bug 3500492
733 IF l_structure_id IS NULL THEN
734 SELECT structure_id INTO l_structure_id
735 FROM mtl_category_sets_b
736 WHERE category_set_id = l_category_set_id;
737 END IF;
738 SELECT category_id INTO l_category_id
739 FROM mtl_categories_b_kfv
740 WHERE concatenated_segments = icoi_rec.category_name
741 -- bug 3500492
742 AND structure_id = l_structure_id
743 AND NVL(disable_date,SYSDATE+1) > SYSDATE;
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 l_return_status := fnd_api.g_RET_STS_ERROR;
747 l_msg_name := 'INV_ICOI_INVALID_CAT_NAME';
748 l_token := 'VALUE';
749 l_token_value := icoi_rec.category_name;
750 l_column_name := 'CATEGORY_NAME';
751 END;
752 -- END IF;
753 ELSE
754 l_return_status := fnd_api.g_RET_STS_ERROR;
755 l_msg_name := 'INV_ICOI_MISS_CAT_NAME';
756 l_token := fnd_api.g_MISS_CHAR;
757 l_token_value := icoi_rec.category_name;
758 l_column_name := 'CATEGORY_NAME';
759 END IF;
760
761 ELSE
762 -- Pass the Id validation to Create_Category_Assignment
763 NULL;
764
765 --l_msg_name := 'INV_ICOI_INVALID_CAT_ID';
766 --l_column_name := 'CATEGORY_ID';
767
768 END IF; -- category_id
769
770 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
774
771 l_process_flag := 3;
772
773 l_RETCODE := G_WARNING;
775 INV_ITEM_MSG.Add_Message
776 ( p_Msg_Name => l_msg_name
777 , p_token1 => l_token
778 , p_value1 => l_token_value
779 , p_transaction_id => l_transaction_id
780 , p_column_name => l_column_name
781 );
782
783 END IF;
784 --
785 -- Assign missing inventory_item_id from item_number
786 --
787
788 l_return_status := fnd_api.g_RET_STS_SUCCESS;
789
790 l_inventory_item_id := icoi_rec.inventory_item_id;
791
792 IF ( l_inventory_item_id IS NULL ) THEN
793
794 IF (l_debug = 1) THEN
795 INV_ITEM_MSG.Debug(Mctx, 'assign missing inventory_item_id');
796 END IF;
797
798 IF ( icoi_rec.item_number IS NOT NULL ) THEN
799 -- commented for fixing 2636268
800 -- ret_code := INVPUOPI.mtl_pr_parse_flex_name (
801 -- l_organization_id,
802 -- 'MSTK',
803 -- icoi_rec.item_number,
804 -- flex_id,
805 -- 0,
806 -- l_err_text );
807 --
808 -- IF ( ret_code = 0 ) THEN
809 -- l_inventory_item_id := flex_id;
810 -- ELSE
811 OPEN msi_item_number_csr (cp_item_number => icoi_rec.item_number,
812 cp_organization_id => icoi_rec.organization_id);
813 FETCH msi_item_number_csr INTO l_inventory_item_id;
814 CLOSE msi_item_number_csr;
815
816 IF ( l_inventory_item_id IS NULL ) THEN
817 OPEN msii_item_number_csr (cp_item_number => icoi_rec.item_number,
818 cp_organization_id => icoi_rec.organization_id,
819 cp_xset_id => g_xset_id);
820 FETCH msii_item_number_csr INTO l_inventory_item_id;
821 CLOSE msii_item_number_csr;
822 END IF;
823
824 IF ( l_inventory_item_id IS NULL ) THEN
825 l_return_status := fnd_api.g_RET_STS_ERROR;
826 l_msg_name := 'INV_ICOI_INVALID_ITEM_NUMBER';
827 l_token := 'VALUE';
828 l_token_value := icoi_rec.item_number;
829 l_column_name := 'ITEM_NUMBER';
830 END IF;
831
832 ELSE
833 l_return_status := fnd_api.g_RET_STS_ERROR;
834 l_msg_name := 'INV_ICOI_MISS_ITEM_NUMBER';
835 l_token := fnd_api.g_MISS_CHAR;
836 l_token_value := icoi_rec.item_number;
837 l_column_name := 'ITEM_NUMBER';
838 END IF;
839
840 ELSE
841 -- Pass the Id validation to Create_Category_Assignment
842 NULL;
843
844 --l_msg_name := 'INV_ICOI_INVALID_ITEM_ID';
845 --l_column_name := 'INVENTORY_ITEM_ID';
846
847 END IF; -- inventory_item_id
848
849 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
850 l_process_flag := 3;
851
852 l_RETCODE := G_WARNING;
853
854 INV_ITEM_MSG.Add_Message
855 ( p_Msg_Name => l_msg_name
856 , p_token1 => l_token
857 , p_value1 => l_token_value
858 , p_transaction_id => l_transaction_id
859 , p_column_name => l_column_name
860 );
861
862 END IF;
863
864 l_return_status := fnd_api.g_RET_STS_SUCCESS;
865
866 -- Security check to be skipped for defaulted records bug 6456493
867 IF ( icoi_rec.created_by <> -99 ) THEN
868 l_has_privilege := INV_EGO_REVISION_VALIDATE.check_data_security(
869 p_function => 'EGO_MANAGE_CATEGORY_SET'
870 ,p_object_name => 'EGO_CATEGORY_SET'
871 ,p_instance_pk1_value => l_category_set_id
872 ,P_User_Id => g_User_id);
873 IF l_has_privilege <> 'T' THEN
874 l_return_status := fnd_api.g_RET_STS_ERROR;
875 l_msg_name := 'INV_IOI_NOT_CATEGORY_USER';
876 l_token := fnd_api.g_MISS_CHAR;
877 l_token_value := icoi_rec.category_set_name;
878 l_column_name := 'CATEGORY_SET_NAME';
879 END IF;
880
881 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
882 l_process_flag := 3;
883 l_RETCODE := G_WARNING;
884 INV_ITEM_MSG.Add_Message
885 ( p_Msg_Name => l_msg_name
886 , p_token1 => l_token
887 , p_value1 => l_token_value
888 , p_transaction_id => l_transaction_id
889 , p_column_name => l_column_name);
890 END IF;
891 --End:Check for data security and user privileges
892 ELSE
893 IF l_inv_debug_level IN(101, 102) THEN
897
894 INVPUTLI.info('INVCICIB. Security check skipped for Item Org CS' || l_inventory_item_id || '-' || l_organization_id || '-' || l_category_set_id);
895 END IF;
896 END IF;
898 --Resolve SYNC records to CREATE/UPDATE
899 IF l_transaction_type = 'SYNC' THEN
900 UPDATE_SYNC_RECORDS(p_inventory_item_id => l_inventory_item_id,
901 p_organization_id => l_organization_id,
902 p_category_set_id => l_category_set_id,
903 p_transaction_id => l_transaction_id,
904 p_row_id => icoi_rec.rowid,
905 x_old_category_id => l_ret_old_category_id,
906 x_transaction_type => l_transaction_type,
907 x_return_status => l_return_status_flag);
908 IF l_return_status_flag = 3 THEN
909 l_process_flag := 3;
910 END IF;
911
912 IF l_transaction_type = 'UPDATE' AND l_ret_old_category_id IS NOT NULL THEN
913 l_old_category_id := l_ret_old_category_id;
914 END IF;
915 END IF;
916
917 IF p_upload_rec_flag = 1 THEN
918 --Start: Check for data security and user privileges
919 l_return_status := fnd_api.g_RET_STS_SUCCESS;
920
921 IF ( icoi_rec.created_by <> -99 ) THEN
922 l_has_privilege := INV_EGO_REVISION_VALIDATE.check_data_security(
923 p_function => 'EGO_EDIT_ITEM_CAT_ASSIGNMENTS'
924 ,p_object_name => 'EGO_ITEM'
925 ,p_instance_pk1_value => l_inventory_item_id
926 ,p_instance_pk2_value => l_organization_id
927 ,P_User_Id => g_User_id);
928
929 IF l_has_privilege <> 'T' THEN
930 l_return_status := fnd_api.g_RET_STS_ERROR;
931 l_msg_name := 'INV_IOI_ITEM_UPDATE_PRIV';
932
933 --Bug: 5522789 Tokenise the message to display item number.
934 l_token := 'VALUE';
935 IF icoi_rec.item_number IS NULL THEN
936 Select concatenated_segments into l_item_number
937 From mtl_system_items_b_kfv
938 where INVENTORY_ITEM_ID = l_inventory_item_id
939 AND organization_id = l_organization_id; -- org
940 ELSE
941 l_item_number := icoi_rec.item_number;
942 END IF;
943 l_token_value := l_item_number;
944 --End Bug: 5522789
945
946 l_column_name := 'ITEM_NUMBER';
947 END IF; -- has privilege
948
949 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
950 l_process_flag := 3;
951 l_RETCODE := G_WARNING;
952 INV_ITEM_MSG.Add_Message
953 ( p_Msg_Name => l_msg_name
954 , p_token1 => l_token
955 , p_value1 => l_token_value
956 , p_transaction_id => l_transaction_id
957 , p_column_name => l_column_name);
958
959 END IF;
960 ELSE
961 IF l_inv_debug_level IN(101, 102) THEN
962 INVPUTLI.info('INVCICIB. Security check skipped for Item Org CS' || l_inventory_item_id || '-' || l_organization_id || '-' || l_category_set_id);
963 END IF;
964 END IF; -- created_d by
965 END IF; -- UPLOAD REC
966
967 -- Write all accumulated messages
968 INV_ITEM_MSG.Write_List (p_delete => TRUE);
969
970 --
971 -- If value-to-id conversions are successful, call the API
972 -- to process item category assignment.
973 --
974
975 IF ( l_process_flag = 4 AND p_upload_rec_flag = 1 ) THEN
976
977 IF ( l_transaction_type = 'DELETE' ) THEN
978
979 IF (l_debug = 1) THEN
980 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment');
981 END IF;
982
983 INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment
984 (
985 p_api_version => 1.0
986 , p_init_msg_list => fnd_api.g_TRUE
987 , p_commit => fnd_api.g_FALSE
988 , p_inventory_item_id => l_inventory_item_id
989 , p_organization_id => l_organization_id
990 , p_category_set_id => l_category_set_id
991 , p_category_id => l_category_id
992 , p_transaction_id => l_transaction_id
993 , x_return_status => l_return_status
994 , x_msg_count => l_msg_count
995 , x_msg_data => l_msg_data
996 );
997
998 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
999 l_process_flag := 7;
1000 ELSE
1001 l_process_flag := 3;
1002
1003 IF (l_debug = 1) THEN
1004 INV_ITEM_MSG.Debug(Mctx, 'error in Delete_Category_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
1005 END IF;
1006
1010
1007 l_RETCODE := G_WARNING;
1008
1009 END IF; -- l_return_status
1011 -- If unexpected error in Delete_Category_Assignment API, stop the processing
1012 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1013 l_RETCODE := G_ERROR;
1014 RAISE Processing_Error;
1015 END IF;
1016
1017 ELSIF ( l_transaction_type = 'CREATE' ) THEN
1018
1019 IF (l_debug = 1) THEN
1020 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATEGORY_PVT.Create_Category_Assignment');
1021 END IF;
1022
1023 INV_ITEM_CATEGORY_PVT.Create_Category_Assignment
1024 (
1025 p_api_version => 1.0
1026 , p_init_msg_list => fnd_api.g_TRUE
1027 , p_commit => fnd_api.g_FALSE
1028 , p_validation_level => INV_ITEM_CATEGORY_PVT.g_VALIDATE_IDS
1029 , p_inventory_item_id => l_inventory_item_id
1030 , p_organization_id => l_organization_id
1031 , p_category_set_id => l_category_set_id
1032 , p_category_id => l_category_id
1033 , p_transaction_id => l_transaction_id
1034 --Bug: 2879647 Added the parameter
1035 , p_request_id => p_request_id
1036 , x_return_status => l_return_status
1037 , x_msg_count => l_msg_count
1038 , x_msg_data => l_msg_data
1039 );
1040
1041 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
1042 l_process_flag := 7;
1043 IF ( l_gpc_catalog_id = l_category_set_id) THEN
1044 -- Bug 5517473 removing the call to process_cat_assignment it is same as update_reg_pub_update_dates
1045 BEGIN
1046 EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES
1047 (p_inventory_item_id => l_inventory_item_id,
1048 p_organization_id => l_organization_id,
1049 p_update_reg => 'Y',
1050 p_commit => FND_API.G_FALSE,
1051 x_return_status => l_return_status,
1052 x_msg_count => l_msg_count,
1053 x_msg_data => l_msg_data);
1054 EXCEPTION
1055 WHEN others THEN
1056 l_msg_data := SQLERRM;
1057 END;
1058 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1059 l_process_flag := 3;
1060 IF (l_debug = 1) THEN
1061 INV_ITEM_MSG.Debug(Mctx, 'error in Create_Category_Assignment ' || l_msg_data);
1062 END IF;
1063 l_RETCODE := G_WARNING;
1064 END IF;
1065 /* End of bug 5517473 */
1066 END IF; --GPC Catalog
1067 ELSE
1068 l_process_flag := 3;
1069
1070 IF (l_debug = 1) THEN
1071 INV_ITEM_MSG.Debug(Mctx, 'error in Create_Category_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
1072 END IF;
1073
1074 l_RETCODE := G_WARNING;
1075
1076 /*
1077 -- Reset current message index value back to 0
1078 FND_MSG_PUB.Reset (FND_MSG_PUB.g_FIRST);
1079
1080 FOR idx IN 1 .. FND_MSG_PUB.Count_Msg LOOP
1081
1082 IF (l_debug = 1) THEN
1083 INV_ITEM_MSG.Debug(Mctx, 'LOOP FND_MSG_PUB Msg: idx=' || TO_CHAR(idx));
1084 END IF;
1085
1086 DECLARE
1087 l_app_short_name VARCHAR2(30);
1088 l_msg_text VARCHAR2(2000);
1089 BEGIN
1090 l_msg_data := FND_MSG_PUB.Get
1091 ( p_msg_index => idx
1092 , p_encoded => FND_API.g_TRUE
1093 );
1094
1095 FND_MESSAGE.Parse_Encoded
1096 ( encoded_message => l_msg_data
1097 , app_short_name => l_app_short_name
1098 , message_name => l_msg_name
1099 );
1100
1101 l_msg_text := FND_MSG_PUB.Get ( p_msg_index => idx
1102 , p_encoded => FND_API.g_FALSE
1103 );
1104
1105 -- INV_ITEM_MSG.Debug(Mctx, 'l_msg_name=' || SUBSTRB(l_msg_name, 1,30));
1106 IF (l_debug = 1) THEN
1107 INV_ITEM_MSG.Debug(Mctx, 'l_msg_name=' || l_msg_name);
1108 INV_ITEM_MSG.Debug(Mctx, 'l_msg_text=' || l_msg_text);
1109 END IF;
1110 -- INV_ITEM_MSG.Debug(Mctx, 'l_msg_text_length=' || TO_CHAR( LENGTH(l_msg_text) ));
1111 END;
1112
1113 END LOOP; -- loop through the messages
1114 */
1115
1116 END IF; -- l_return_status
1117
1118 -- If unexpected error in Create_Category_Assignment API,
1119 -- stop the processing.
1120
1121 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1122 l_RETCODE := G_ERROR;
1123 RAISE Processing_Error;
1124 END IF;
1125 --* Code added for Bug #3991044
1126 ELSIF ( l_transaction_type = 'UPDATE' ) THEN
1127 IF (l_debug = 1) THEN
1128 INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATEGORY_PVT.Update_Category_Assignment');
1129 END IF;
1130
1131 l_return_status := fnd_api.g_RET_STS_SUCCESS;
1132
1133 INV_ITEM_CATEGORY_PVT.Update_Category_Assignment
1134 ( p_api_version => 1.0
1135 , p_init_msg_list => fnd_api.g_TRUE
1136 , p_commit => fnd_api.g_FALSE
1137 , p_inventory_item_id => l_inventory_item_id
1138 , p_organization_id => l_organization_id
1139 , p_category_set_id => l_category_set_id
1140 , p_category_id => l_category_id
1141 , p_old_category_id => l_old_category_id
1142 , p_transaction_id => l_transaction_id
1143 , x_return_status => l_return_status
1144 , x_msg_count => l_msg_count
1145 , x_msg_data => l_msg_data
1146 );
1147
1148 IF ( l_return_status = fnd_api.g_RET_STS_SUCCESS ) THEN
1149 l_process_flag := 7;
1150 IF ( l_udex_catalog_id = l_category_set_id
1151 OR l_gpc_catalog_id = l_category_set_id) THEN
1152 -- Bug 5517473 removing the call to process_cat_assignment it is same as update_reg_pub_update_dates
1153 /*BEGIN
1154 EXECUTE IMMEDIATE 'BEGIN EGO_GTIN_PVT.PROCESS_CAT_ASSIGNMENT( :1, :2); END;' USING l_inventory_item_id, l_organization_id;
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 NULL;
1158 END;*/
1159 /* Bug 5517473 - Submit for Re-Registration of GDSN attrs when GDSN/GPC category set updated */
1160 BEGIN
1161 EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES
1162 (p_inventory_item_id => l_inventory_item_id,
1163 p_organization_id => l_organization_id,
1164 p_update_reg => 'Y',
1165 p_commit => FND_API.G_FALSE,
1166 x_return_status => l_return_status,
1167 x_msg_count => l_msg_count,
1168 x_msg_data => l_msg_data);
1169 EXCEPTION
1170 WHEN others THEN
1171 l_msg_data := SQLERRM;
1172 END;
1173 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1174 l_process_flag := 3;
1175 IF (l_debug = 1) THEN
1176 INV_ITEM_MSG.Debug(Mctx, 'error in Update_Category_Assignment ' || l_msg_data);
1177 END IF;
1178 l_RETCODE := G_WARNING;
1179 END IF;
1180 /* End of bug 5517473 */
1181 END IF; --Udex Catalog
1182 ELSE
1183 l_process_flag := 3;
1184 IF (l_debug = 1) THEN
1185 INV_ITEM_MSG.Debug(Mctx, 'error in Update_Category_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
1186 END IF;
1187 l_RETCODE := G_WARNING;
1188 END IF; -- l_return_status
1189 --* End of Bug #3991044
1190
1191 END IF; -- l_transaction_type
1192
1193 /* Bug 4527222
1194 Replacing this call with a single call after the loop
1195 IF ( l_process_flag = 7 ) THEN
1196 -- Sync item category assignment with item record in STAR.
1197
1198 IF (l_debug = 1) THEN
1199 INV_ITEM_MSG.Debug(Mctx, 'calling Sync_Category_Assignments');
1200 END IF;
1201
1202 -- Bug: 2718703 checking for ENI product before calling their package
1203 -- Start Bug: 3185516
1204 IF ( l_transaction_type = 'CREATE' ) THEN
1205 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1206 p_api_version => 1.0
1207 ,p_init_msg_list => FND_API.g_TRUE
1208 ,p_inventory_item_id => l_inventory_item_id
1209 ,p_organization_id => l_organization_id
1210 ,p_category_set_id => l_category_set_id
1211 ,p_old_category_id => NULL
1212 ,p_new_category_id => l_category_id
1213 ,x_return_status => l_return_Status
1214 ,x_msg_count => l_msg_count
1215 ,x_msg_data => l_msg_data);
1216 ELSIF( l_transaction_type = 'DELETE' ) THEN
1217 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1218 p_api_version => 1.0
1219 ,p_init_msg_list => FND_API.g_TRUE
1220 ,p_inventory_item_id => l_inventory_item_id
1221 ,p_organization_id => l_organization_id
1222 ,p_category_set_id => l_category_set_id
1223 ,p_old_category_id => l_category_id
1224 ,p_new_category_id => NULL
1225 ,x_return_status => l_return_Status
1229
1226 ,x_msg_count => l_msg_count
1227 ,x_msg_data => l_msg_data);
1228 END IF;
1230 -- End Bug: 3185516
1231 -- If unexpected error in Sync_Category_Assignments,
1232 -- stop the processing.
1233
1234 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1235 l_RETCODE := G_ERROR;
1236 RAISE Processing_Error;
1237 END IF;
1238
1239 END IF; Bug 4527222*/
1240
1241 END IF; -- process_flag = 4 AND p_upload_rec_flag = 1
1242
1243 -- Write all accumulated messages
1244 --
1245 INV_ITEM_MSG.Write_List (p_delete => TRUE);
1246
1247 --
1248 -- Update the current interface record
1249 --
1250
1251 IF (l_debug = 1) THEN
1252 INV_ITEM_MSG.Debug(Mctx, 'update interface record');
1253 END IF;
1254
1255 UPDATE mtl_item_categories_interface
1256 SET
1257 transaction_id = l_transaction_id
1258 , transaction_type = l_transaction_type
1259 , process_flag = l_process_flag
1260 , inventory_item_id = NVL(l_inventory_item_id, inventory_item_id)
1261 , category_set_id = NVL(l_category_set_id, category_set_id)
1262 , category_id = NVL(l_category_id, category_id)
1263 , program_application_id = g_prog_appid
1264 , program_id = g_prog_id
1265 , program_update_date = SYSDATE
1266 , request_id = g_request_id
1267 , last_update_date = SYSDATE
1268 , last_updated_by = g_user_id
1269 , last_update_login = g_login_id
1270 WHERE
1271 CURRENT OF icoi_csr;
1272
1273 END LOOP; --} icoi_csr
1274
1275
1276 /* Bug 4527222*/
1277 BEGIN
1278 SELECT 'Y'
1279 INTO l_records_updated
1280 FROM mtl_item_categories_interface mici
1281 WHERE mici.set_process_id = g_xset_id
1282 AND mici.process_flag = 7
1283 AND ROWNUM = 1;
1284
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND THEN
1287 l_records_updated := 'N';
1288 WHEN OTHERS THEN
1289 l_records_updated := 'Y';
1290 END;
1291
1292 IF l_records_updated = 'Y' THEN
1293 BEGIN
1294 INV_ENI_ITEMS_STAR_PKG.Sync_Star_ItemCatg_From_COI(
1295 p_api_version => 1.0
1296 ,p_init_msg_list => FND_API.g_TRUE
1297 ,p_set_process_id => g_xset_id
1298 ,x_return_status => l_return_Status
1299 ,x_msg_count => l_msg_count
1300 ,x_msg_data => l_msg_data);
1301 -- End Bug: 3185516
1302 -- If unexpected error in Sync_Star_ItemCatg_From_COI,
1303 -- stop the processing.
1304 /*Bug 4569555
1305 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1306 l_RETCODE := G_ERROR;
1307 RAISE Processing_Error;
1308 END IF;*/
1309 END;
1310 END IF;
1311 /* Bug 4527222*/
1312
1313 --Populate Bulkload Recs
1314 IF g_request_id IS NOT NULL AND g_request_id <> -1 THEN
1315 FOR cr IN populate_catg_bulkloadrecs(g_request_id ,g_xset_id)
1316 LOOP
1317 INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1318 REQUEST_ID
1319 ,ENTITY_TYPE
1320 ,INVENTORY_ITEM_ID
1321 ,ORGANIZATION_ID
1322 ,CATEGORY_SET_ID
1323 ,CATEGORY_ID
1324 ,TRANSACTION_TYPE
1325 ,CREATION_DATE
1326 ,CREATED_BY
1327 ,LAST_UPDATE_DATE
1328 ,LAST_UPDATED_BY
1329 ,LAST_UPDATE_LOGIN)
1330 VALUES(
1331 cr.REQUEST_ID
1332 ,'ITEM_CATEGORY'
1333 ,cr.INVENTORY_ITEM_ID
1334 ,cr.ORGANIZATION_ID
1335 ,cr.CATEGORY_SET_ID
1336 ,cr.CATEGORY_ID
1337 ,cr.TRANSACTION_TYPE
1338 ,NVL(cr.CREATION_DATE, SYSDATE)
1339 ,decode(cr.CREATED_BY, -99, g_user_id, NULL, g_user_id, cr.CREATED_BY)
1340 ,NVL(cr.LAST_UPDATE_DATE, SYSDATE)
1341 ,NVL(cr.LAST_UPDATED_BY, g_user_id)
1342 ,cr.LAST_UPDATE_LOGIN);
1343 END LOOP;
1344
1345 BEGIN
1346 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
1347 p_entity_type => 'ITEM_CATEGORY'
1348 ,p_xset_id => g_xset_id
1349 ,p_dml_type => 'BULK'
1350 ,p_request_id => g_request_id );
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 NULL;
1355 END ;
1356
1357 END IF;
1358 --Populate Bulkload Recs
1359
1360
1361 --R12: Business Event Enhancement
1362 IF (g_request_id <> -1) THEN
1363 BEGIN
1364 INV_ITEM_EVENTS_PVT.Raise_Events(
1365 p_request_id => g_request_id
1366 ,p_xset_id => g_xset_id
1367 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1368 ,p_dml_type => 'BULK');
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 NULL;
1372 END;
1373 END IF;
1374 --R12: Business Event Enhancement
1375
1376 -- Check of commit
1377 IF ( FND_API.To_Boolean( l_commit ) ) THEN
1378 COMMIT WORK;
1379 -- Call IP Intermedia Sync
1380 INV_ITEM_EVENTS_PVT.Sync_IP_IM_Index;
1381 END IF;
1382
1383 --
1384 -- Delete successfully processed records from the interface table
1385 --
1386
1387 IF ( p_delete_rec_flag = 1 ) THEN
1388
1389 IF (l_debug = 1) THEN
1390 INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');
1391 END IF;
1392
1393 INV_ITEM_CATEGORY_OI.delete_OI_records
1394 ( p_commit => l_commit
1395 , p_rec_set_id => g_xset_id
1396 , x_return_status => l_return_status
1397 );
1398
1399 IF (l_debug = 1) THEN
1400 INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);
1401 END IF;
1402
1403 IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1404 RAISE Processing_Error;
1405 END IF;
1406
1407 -- Write all accumulated messages
1408 INV_ITEM_MSG.Write_List (p_delete => TRUE);
1409
1410 END IF; -- p_delete_rec_flag = 1
1411
1412 --
1413 -- Assign conc request return code
1414 --
1415
1416 RETCODE := l_RETCODE;
1417 IF ( l_RETCODE = G_SUCCESS ) THEN
1418 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICOI_SUCCESS');
1419 ELSIF ( l_RETCODE = G_WARNING ) THEN
1420 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICOI_WARNING');
1421 ELSE
1422 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICOI_FAILURE');
1423 END IF;
1424
1425 EXCEPTION
1426
1427 WHEN Processing_Error THEN
1428 RETCODE := G_ERROR;
1429 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICOI_FAILURE');
1430
1431 -- Write all accumulated messages
1432 INV_ITEM_MSG.Write_List (p_delete => TRUE);
1433
1434 -- Check of commit
1435 IF ( FND_API.To_Boolean(l_commit) ) THEN
1436 COMMIT WORK;
1437 END IF;
1438
1439 WHEN others THEN
1440 RETCODE := G_ERROR;
1441 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_ICOI_FAILURE');
1442
1443 l_err_text := SUBSTRB(SQLERRM, 1,240);
1444
1445 INV_ITEM_MSG.Add_Message
1446 ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
1447 , p_token1 => 'PKG_NAME'
1448 , p_value1 => G_PKG_NAME
1449 , p_token2 => 'PROCEDURE_NAME'
1450 , p_value2 => l_api_name
1451 , p_token3 => 'ERROR_TEXT'
1452 , p_value3 => l_err_text
1453 , p_transaction_id => l_transaction_id
1454 );
1455
1456 -- Write all accumulated messages
1457 INV_ITEM_MSG.Write_List (p_delete => TRUE);
1458
1459 -- Check of commit
1460 IF ( FND_API.To_Boolean(l_commit) ) THEN
1461 COMMIT WORK;
1462 END IF;
1463
1464 END process_Item_Category_records;
1465 ------------------------------------------------------------------------------
1466
1467
1468 ---------------------------- convert_Values_to_Ids ---------------------------
1469 /*
1470 PROCEDURE convert_Values_to_Ids
1471 (
1472 )
1473 IS
1474 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1475 BEGIN
1476 END convert_Values_to_Ids;
1477 */
1478
1479 ------------------------------ delete_OI_records -----------------------------
1480
1481 PROCEDURE delete_OI_records
1482 (
1483 p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
1484 , p_rec_set_id IN NUMBER
1485 , x_return_status OUT NOCOPY VARCHAR2
1486 )
1487 IS
1488 l_api_name CONSTANT VARCHAR2(30) := 'delete_OI_records';
1489 Mctx INV_ITEM_MSG.Msg_Ctx_type;
1490
1491 l_del_process_flag NUMBER := 7; -- process_flag value for records to be deleted
1492 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1493 BEGIN
1494
1495 Mctx.Package_Name := G_PKG_NAME;
1496 Mctx.Procedure_Name := l_api_name;
1497
1498 IF (l_debug = 1) THEN
1499 INV_ITEM_MSG.Debug(Mctx, 'begin');
1500 END IF;
1501
1502 -- Initialize API return status to success
1503 x_return_status := FND_API.g_RET_STS_SUCCESS;
1504
1505 LOOP
1506 DELETE FROM mtl_item_categories_interface
1507 WHERE set_process_id = p_rec_set_id
1508 AND process_flag = l_del_process_flag
1509 AND rownum < G_ROWS_TO_COMMIT;
1510
1511 EXIT WHEN SQL%NOTFOUND;
1512
1513 --INV_ITEM_MSG.Debug(Mctx, 'deleted ' || TO_CHAR(SQL%ROWCOUNT) || ' record(s)');
1514
1515 -- Check of commit
1516 IF ( FND_API.To_Boolean(p_commit) ) THEN
1517 COMMIT WORK;
1518 END IF;
1519
1520 END LOOP;
1521
1522 EXCEPTION
1523
1524 WHEN others THEN
1525 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1526
1527 INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
1528
1529 -- Check of commit
1530 IF ( FND_API.To_Boolean(p_commit) ) THEN
1531 COMMIT WORK;
1532 END IF;
1533
1534 END delete_OI_records;
1535 ------------------------------------------------------------------------------
1536
1537
1538 END INV_ITEM_CATEGORY_OI;