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