[Home] [Help]
PACKAGE BODY: APPS.INVIDIT2
Source
1 PACKAGE BODY INVIDIT2 AS
2 /* $Header: INVIDI2B.pls 120.6 2008/08/07 09:03:49 rmpartha ship $ */
3
4 -- After inserting an item:
5 -- insert into mtl_pending_item_status
6 -- insert into mtl_item_revisions
7 -- insert into mtl_item_categories
8 -- call CSTPIICI to insert into cst_item_costs
9 -- insert into mtl_uom_conversions
10 -- Call other procedures passing event='INSERT'
11
12 -- After updating an item:
13 -- insert into mtl_pending_item_status
14 -- insert into mtl_item_categories
15 -- insert into mtl_uom_conversions
16 -- Call other procedures passing event = 'UPDATE'
17
18 -- After assigning the item to a child org:
19 -- delete from mtl_item_categories
20 -- insert into mtl_item_categories
21 -- insert into mtl_pending_item_status
22 -- insert into mtl_item_revisions
23 -- call CSTPIICI to insert into cst_item_costs
24 -- Call other procedures passing event = 'ORG_ASSIGN'
25
26 -- After updating item/org attributes:
27 -- insert into mtl_pending_item_status
28 -- insert into mtl_item_categories
29 -- insert into mtl_uom_conversions
30 -- Call other procedures passing event = 'ITEM_ORG'
31
32 l_cat_ins_upd BOOLEAN := FALSE; --* Added for Bug 4491340
33
34 PROCEDURE Table_Inserts
35 (
36 X_event VARCHAR2
37 , X_item_id NUMBER
38 , X_org_id NUMBER
39 , X_master_org_id NUMBER
40 , X_status_code VARCHAR2 DEFAULT NULL
41 , X_inventory_item_flag VARCHAR2
42 , X_purchasing_item_flag VARCHAR2
43 , X_internal_order_flag VARCHAR2
44 , X_mrp_planning_code NUMBER
45 , X_serviceable_product_flag VARCHAR2
46 , X_costing_enabled_flag VARCHAR2
47 , X_eng_item_flag VARCHAR2
48 , X_customer_order_flag VARCHAR2
49 , X_eam_item_type NUMBER
50 , X_contract_item_type_code VARCHAR2
51 , p_Folder_Category_Set_id IN NUMBER
52 , p_Folder_Item_Category_id IN NUMBER
53 , X_allowed_unit_code NUMBER DEFAULT 0
54 , X_primary_uom VARCHAR2 DEFAULT NULL
55 , X_primary_uom_code VARCHAR2 DEFAULT NULL
56 , X_primary_uom_class VARCHAR2 DEFAULT NULL
57 , X_inv_install NUMBER DEFAULT 0
58 , X_last_updated_by NUMBER DEFAULT 0
59 , X_last_update_login NUMBER DEFAULT 0
60 , X_item_catalog_group_id NUMBER
61 , P_Default_Move_Order_Sub_Inv VARCHAR2 -- Item Transaction Defaults for 11.5.9
62 , P_Default_Receiving_Sub_Inv VARCHAR2
63 , P_Default_Shipping_Sub_Inv VARCHAR2
64 , P_Lifecycle_Id NUMBER DEFAULT NULL
65 , P_Current_Phase_Id NUMBER DEFAULT NULL
66 )
67 IS
68 BEGIN
69
70 if ( X_event = 'INSERT' ) then
71
72 Insert_Pending_Status('INSERT', X_item_id, X_org_id, X_master_org_id,
73 X_status_code,P_Lifecycle_Id,P_Current_Phase_Id);
74
75 Insert_Revision('INSERT', X_item_id, X_org_id, X_last_updated_by,
76 X_last_update_login);
77
78 Insert_Categories
79 (
80 X_event => 'INSERT'
81 , X_item_id => X_item_id
82 , X_org_id => X_org_id
83 , X_master_org_id => X_master_org_id
84 , X_inventory_item_flag => X_inventory_item_flag
85 , X_purchasing_item_flag => X_purchasing_item_flag
86 , X_internal_order_flag => X_internal_order_flag
87 , X_mrp_planning_code => X_mrp_planning_code
88 , X_serviceable_product_flag => X_serviceable_product_flag
89 , X_costing_enabled_flag => X_costing_enabled_flag
90 , X_eng_item_flag => X_eng_item_flag
91 , X_customer_order_flag => X_customer_order_flag
92 , X_eam_item_type => X_eam_item_type
93 , X_contract_item_type_code => X_contract_item_type_code
94 , p_Folder_Category_Set_id => p_Folder_Category_Set_id
95 , p_Folder_Item_Category_id => p_Folder_Item_Category_id
96 , X_last_updated_by => X_last_updated_by
97 );
98
99 Insert_Cost_Row(X_item_id, X_master_org_id, X_inv_install,
100 X_last_updated_by);
101
102 Insert_Uom_Conversion(X_item_id, X_allowed_unit_code, X_primary_uom,
103 X_primary_uom_code, X_primary_uom_class);
104
105 elsif ( X_event = 'UPDATE' ) then
106
107 -- If X_status_code is null, then the status was not changed in the
108 -- form, so don't insert another row into mtl_pending_item_status.
109
110 if ( X_status_code is not null ) then
111 Insert_Pending_Status('UPDATE', X_item_id, X_org_id, X_master_org_id,
112 X_status_code,P_Lifecycle_Id,P_Current_Phase_Id);
113 end if;
114
115 Insert_Categories
116 (
117 X_event => 'UPDATE'
118 , X_item_id => X_item_id
119 , X_org_id => X_org_id
120 , X_master_org_id => X_master_org_id
121 , X_inventory_item_flag => X_inventory_item_flag
122 , X_purchasing_item_flag => X_purchasing_item_flag
123 , X_internal_order_flag => X_internal_order_flag
124 , X_mrp_planning_code => X_mrp_planning_code
125 , X_serviceable_product_flag => X_serviceable_product_flag
126 , X_costing_enabled_flag => X_costing_enabled_flag
127 , X_eng_item_flag => X_eng_item_flag
128 , X_customer_order_flag => X_customer_order_flag
129 , X_eam_item_type => X_eam_item_type
130 , X_contract_item_type_code => X_contract_item_type_code
131 , p_Folder_Category_Set_id => p_Folder_Category_Set_id
132 , p_Folder_Item_Category_id => p_Folder_Item_Category_id
133 , X_last_updated_by => X_last_updated_by
134 );
135
136 Insert_Uom_Conversion(X_item_id, X_allowed_unit_code, X_primary_uom,
137 X_primary_uom_code, X_primary_uom_class);
138
139 -- Sync item catalog group. item across all orgs should have only one
140 -- catalog group. checks to see if same item in some other org has a
141 -- different item catalog and if so, syncs up.
142
143 update mtl_system_items_b
144 set
145 item_catalog_group_id = X_item_catalog_group_id
146 where
147 inventory_item_id = X_item_id
148 and organization_id <> X_org_id
149 and exists
150 ( select 1 from mtl_system_items_b
151 where inventory_item_id = X_item_id and
152 organization_id <> X_org_id and
153 nvl (item_catalog_group_id, -1) <>
154 nvl (X_item_catalog_group_id, -1)
155 )
156 ;
157
158 elsif ( X_event = 'ITEM_ORG' ) then
159
160 -- If X_status_code is null, then the status was not changed in the
161 -- form, so don't insert another row into mtl_pending_item_status.
162
163 if ( X_status_code is not null ) then
164 Insert_Pending_Status('ITEM_ORG', X_item_id, X_org_id, X_master_org_id,
165 X_status_code,P_Lifecycle_Id,P_Current_Phase_Id);
166 end if;
167
168 Insert_Categories
169 (
170 X_event => 'ITEM_ORG'
171 , X_item_id => X_item_id
172 , X_org_id => X_org_id
173 , X_master_org_id => X_master_org_id
174 , X_inventory_item_flag => X_inventory_item_flag
175 , X_purchasing_item_flag => X_purchasing_item_flag
176 , X_internal_order_flag => X_internal_order_flag
177 , X_mrp_planning_code => X_mrp_planning_code
178 , X_serviceable_product_flag => X_serviceable_product_flag
179 , X_costing_enabled_flag => X_costing_enabled_flag
180 , X_eng_item_flag => X_eng_item_flag
181 , X_customer_order_flag => X_customer_order_flag
182 , X_eam_item_type => X_eam_item_type
183 , X_contract_item_type_code => X_contract_item_type_code
184 , p_Folder_Category_Set_id => p_Folder_Category_Set_id
185 , p_Folder_Item_Category_id => p_Folder_Item_Category_id
186 , X_last_updated_by => X_last_updated_by
187 );
188
189 Insert_Uom_Conversion(X_item_id, X_allowed_unit_code, X_primary_uom,
190 X_primary_uom_code, X_primary_uom_class);
191
192 elsif ( X_event = 'ORG_ASSIGN' ) then
193
194 Delete_Categories(X_item_id, X_org_id);
195
196 Insert_Categories
197 (
198 X_event => 'ORG_ASSIGN'
199 , X_item_id => X_item_id
200 , X_org_id => X_org_id
201 , X_master_org_id => X_master_org_id
202 , X_inventory_item_flag => X_inventory_item_flag
203 , X_purchasing_item_flag => X_purchasing_item_flag
204 , X_internal_order_flag => X_internal_order_flag
205 , X_mrp_planning_code => X_mrp_planning_code
206 , X_serviceable_product_flag => X_serviceable_product_flag
207 , X_costing_enabled_flag => X_costing_enabled_flag
208 , X_eng_item_flag => X_eng_item_flag
209 , X_customer_order_flag => X_customer_order_flag
210 , X_eam_item_type => X_eam_item_type
211 , X_contract_item_type_code => X_contract_item_type_code
212 , p_Folder_Category_Set_id => p_Folder_Category_Set_id
213 , p_Folder_Item_Category_id => p_Folder_Item_Category_id
214 , X_last_updated_by => X_last_updated_by
215 );
216
217 Insert_Pending_Status('ORG_ASSIGN', X_item_id, X_org_id, X_master_org_id,
218 X_status_code,P_Lifecycle_Id,P_Current_Phase_Id);
219
220 Insert_Revision('ORG_ASSIGN', X_item_id, X_org_id, X_last_updated_by,
221 X_last_update_login);
222
223 Insert_Cost_Row(X_item_id, X_org_id, X_inv_install, X_last_updated_by);
224
225 end if; -- event
226
227 -- Insert Item Transaction Default SubInventories
228
229 if ( X_event IN ('INSERT','UPDATE','ITEM_ORG') ) then
230
231 Insert_Default_SubInventories ( X_event => X_event
232 , X_item_id => X_item_id
233 , X_org_id => X_org_id
234 , P_Default_Move_Order_Sub_Inv => P_Default_Move_Order_Sub_Inv
235 , P_Default_Receiving_Sub_Inv => P_Default_Receiving_Sub_Inv
236 , P_Default_Shipping_Sub_Inv => P_Default_Shipping_Sub_Inv
237 );
238
239 end if;
240
241 END Table_Inserts;
242
243
244 PROCEDURE Insert_Pending_Status
245 (
246 X_event varchar2,
247 X_item_id number,
248 X_org_id number,
249 X_master_org_id number,
250 X_status varchar2,
251 X_Lifecycle_Id number default null,
252 X_Current_Phase_Id number default null
253 )
254 IS
255 status_level number;
256 attr_name varchar2(50);
257 l_user_id NUMBER := NVL(FND_GLOBAL.User_Id, 0);
258 -- l_debug NUMBER := NVL(FND_PROFILE.Value('INV_DEBUG_TRACE'), 0);
259 BEGIN
260
261 if (X_event = 'INSERT') then
262
263 -- X_org_id will be the master org
264
265 insert into mtl_pending_item_status
266 (inventory_item_id,
267 organization_id,
268 status_code,
269 effective_date,
270 implemented_date,
271 pending_flag,
272 lifecycle_id,
273 phase_id,
274 last_update_date,
275 last_updated_by,
276 creation_date,
277 created_by)
278 values(X_item_id,
279 X_org_id,
280 X_status,
281 sysdate,
282 sysdate,
283 'N',
284 x_lifecycle_id,
285 x_current_phase_id,
286 sysdate,
287 l_user_id,
288 sysdate,
289 l_user_id);
290
291 elsif (X_event = 'UPDATE') then
292
293 attr_name := 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
294
295 select control_level
296 into status_level
297 from mtl_item_attributes
298 where attribute_name = attr_name;
299
300 if (status_level = 2) then
301
302 -- Use this same sql if called from Update Item form
303 -- pass in current org instead of master org
304 insert into mtl_pending_item_status
305 (inventory_item_id,
306 organization_id,
307 status_code,
308 effective_date,
309 implemented_date,
310 pending_flag,
311 lifecycle_id,
312 phase_id,
313 last_update_date,
314 last_updated_by,
315 creation_date,
316 created_by)
317 values
318 (X_item_id,
319 X_org_id,
320 X_status,
321 sysdate,
322 sysdate,
323 'N',
324 x_lifecycle_id,
325 x_current_phase_id,
326 sysdate,
327 l_user_id,
328 sysdate,
329 l_user_id);
330
331 elsif (status_level = 1) then
332
333 insert into mtl_pending_item_status
334 (inventory_item_id,
335 organization_id,
336 status_code,
337 effective_date,
338 implemented_date,
339 pending_flag,
340 lifecycle_id,
341 phase_id,
342 last_update_date,
343 last_updated_by,
344 creation_date,
345 created_by)
346 select
347 X_item_id,
348 p.organization_id,
349 X_status,
350 sysdate,
351 sysdate,
352 'N',
353 x_lifecycle_id,
354 x_current_phase_id,
355 sysdate,
356 l_user_id,
357 sysdate,
358 l_user_id
359 from mtl_parameters p
360 where p.master_organization_id = X_master_org_id
361 and exists (select 'x' from mtl_system_items_B i
362 where i.inventory_item_id =
363 X_item_id
364 and p.organization_id = i.organization_id);
365 end if;
366
367 elsif (X_event = 'ITEM_ORG') then
368
369 insert into mtl_pending_item_status
370 (inventory_item_id,
371 organization_id,
372 status_code,
373 effective_date,
374 implemented_date,
375 pending_flag,
376 lifecycle_id,
377 phase_id,
378 last_update_date,
379 last_updated_by,
380 creation_date,
381 created_by)
382 values
383 (X_item_id,
384 X_org_id,
385 X_status,
386 sysdate,
387 sysdate,
388 'N',
389 x_lifecycle_id,
390 x_current_phase_id,
391 sysdate,
392 l_user_id,
393 sysdate,
394 l_user_id);
395
396 elsif (X_event = 'ORG_ASSIGN') then
397
398 insert into mtl_pending_item_status
399 (inventory_item_id,
400 organization_id,
401 status_code,
402 effective_date,
403 implemented_date,
404 pending_flag,
405 lifecycle_id,
406 phase_id,
407 last_update_date,
408 last_updated_by,
409 creation_date,
410 created_by)
411 values
412 (X_item_id,
413 X_org_id,
414 X_status,
415 sysdate,
416 sysdate,
417 'N',
418 x_lifecycle_id,
419 x_current_phase_id,
420 sysdate,
421 l_user_id,
422 sysdate,
423 l_user_id);
424
425 end if; -- event
426
427 END Insert_Pending_Status;
428
429
430 PROCEDURE Insert_Revision
431 ( X_event varchar2,
432 X_item_id number,
433 X_org_id number,
434 X_last_updated_by number,
435 X_last_update_login number)
436 IS
437 l_sys_date DATE := SYSDATE;
438 l_revision_id mtl_item_revisions_b.revision_id%TYPE;
439 BEGIN
440 if (X_event = 'INSERT') then
441
442 select mtl_item_revisions_b_s.nextval
443 into l_revision_id from dual;
444
445 insert into mtl_item_revisions_b
446 (inventory_item_id,
447 organization_id,
448 last_update_date,
449 last_updated_by,
450 creation_date,
451 created_by,
452 last_update_login,
453 effectivity_date,
454 implementation_date,
455 revision,
456 revision_label,-- Bug: 3017253
457 revision_id,
458 object_version_number)
459 select X_item_id,
460 X_org_id,
461 l_sys_date,
462 X_last_updated_by,
463 l_sys_date,
464 X_last_updated_by,
465 -1,
466 l_sys_date,
467 l_sys_date,
468 starting_revision,
469 starting_revision,-- Bug:3017253
470 l_revision_id,
471 1
472 from mtl_parameters
473 where organization_id = X_org_id;
474
475 INSERT INTO MTL_ITEM_REVISIONS_TL (
476 INVENTORY_ITEM_ID,
477 ORGANIZATION_ID,
478 REVISION_ID,
479 DESCRIPTION,
480 CREATION_DATE,
481 CREATED_BY,
482 LAST_UPDATE_DATE,
483 LAST_UPDATED_BY,
484 LAST_UPDATE_LOGIN,
485 LANGUAGE,
486 SOURCE_LANG
487 ) SELECT X_item_id,
488 X_org_id,
489 l_revision_id,
490 NULL,
491 l_sys_date,
492 X_last_updated_by,
493 l_sys_date,
494 X_last_updated_by,
495 -1,
496 L.LANGUAGE_CODE,
497 USERENV('LANG')
498 FROM FND_LANGUAGES L
499 WHERE L.INSTALLED_FLAG in ('I', 'B')
500 AND NOT EXISTS (SELECT NULL
501 FROM MTL_ITEM_REVISIONS_TL T
502 WHERE T.INVENTORY_ITEM_ID = X_item_id
503 AND T.ORGANIZATION_ID = X_org_id
504 AND T.REVISION_ID = l_revision_id
505 AND T.LANGUAGE = L.LANGUAGE_CODE);
506
507 elsif (X_event = 'ORG_ASSIGN') then
508
509 select mtl_item_revisions_b_s.nextval
510 into l_revision_id from dual;
511
512 insert into mtl_item_revisions_b
513 (inventory_item_id,
514 organization_id,
515 last_update_date,
516 last_updated_by,
517 creation_date,
518 created_by,
519 last_update_login,
520 effectivity_date,
521 implementation_date,
522 revision,
523 revision_label,-- Bug: 3017253
524 revision_id,
525 object_version_number)
526 select X_item_id,
527 X_org_id,
528 l_sys_date,
529 X_last_updated_by,
530 l_sys_date,
531 X_last_updated_by,
532 X_last_update_login,
533 l_sys_date,
534 l_sys_date,
535 starting_revision,
536 starting_revision,-- Bug:3017253
537 l_revision_id,
538 1
539 from mtl_parameters
540 where organization_id = X_org_id;
541
542 INSERT INTO MTL_ITEM_REVISIONS_TL (
543 INVENTORY_ITEM_ID,
544 ORGANIZATION_ID,
545 REVISION_ID,
546 DESCRIPTION,
547 CREATION_DATE,
548 CREATED_BY,
549 LAST_UPDATE_DATE,
550 LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN,
552 LANGUAGE,
553 SOURCE_LANG
554 ) SELECT X_item_id,
555 X_org_id,
556 l_revision_id,
557 NULL,
558 l_sys_date,
559 X_last_updated_by,
560 l_sys_date,
561 X_last_updated_by,
562 X_last_update_login,
563 L.LANGUAGE_CODE,
564 USERENV('LANG')
565 FROM FND_LANGUAGES L
566 WHERE L.INSTALLED_FLAG in ('I', 'B')
567 AND NOT EXISTS (SELECT NULL
568 FROM MTL_ITEM_REVISIONS_TL T
569 WHERE T.INVENTORY_ITEM_ID = X_item_id
570 AND T.ORGANIZATION_ID = X_org_id
571 AND T.REVISION_ID = l_revision_id
572 AND T.LANGUAGE = L.LANGUAGE_CODE);
573
574 end if; -- event
575
576 --Bug 5525199 BE for implicit revision creation
577 IF (X_event = 'ORG_ASSIGN' OR X_event = 'INSERT') THEN
578 BEGIN
579 INV_ITEM_EVENTS_PVT.Raise_Events(
580 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
581 ,p_dml_type => 'CREATE'
582 ,p_inventory_item_id => X_item_id
583 ,p_organization_id => X_org_id
584 ,p_revision_id => l_revision_id);
585 EXCEPTION
586 WHEN OTHERS THEN
587 NULL;
588 END;
589 END IF;
590
591 END Insert_Revision;
592
593 PROCEDURE Insert_Categories
594 (
595 X_event VARCHAR2
596 , X_item_id NUMBER
597 , X_org_id NUMBER
598 , X_master_org_id NUMBER
599 , X_inventory_item_flag VARCHAR2
600 , X_purchasing_item_flag VARCHAR2
601 , X_internal_order_flag VARCHAR2
602 , X_mrp_planning_code NUMBER
603 , X_serviceable_product_flag VARCHAR2
604 , X_costing_enabled_flag VARCHAR2
605 , X_eng_item_flag VARCHAR2
606 , X_customer_order_flag VARCHAR2
607 , X_eam_item_type NUMBER
608 , X_contract_item_type_code VARCHAR2
609 , p_Folder_Category_Set_id IN NUMBER
610 , p_Folder_Item_Category_id IN NUMBER
611 , X_last_updated_by NUMBER
612 )
613 IS
614 TYPE ORG_LISTS IS TABLE OF MTL_ITEM_CATEGORIES.ORGANIZATION_ID%TYPE;
615 TYPE CATSET_LISTS IS TABLE OF MTL_ITEM_CATEGORIES.CATEGORY_SET_ID%TYPE;
616 TYPE CAT_LISTS IS TABLE OF MTL_ITEM_CATEGORIES.CATEGORY_ID%TYPE;
617
618 l_organizations_rec ORG_LISTS;
619 l_category_sets_rec CATSET_LISTS;
620 l_categories_rec CAT_LISTS;
621
622 l_the_item_assign_count NUMBER;
623 l_the_cat_assign_count NUMBER;
624
625 Cat_Set_No_Default_Cat EXCEPTION;
626 l_Func_Area Varchar2(80);
627 l_Cat_Set_Name Varchar2(30);
628
629 CURSOR Func_Area_csr IS
630 SELECT
631 mdcs.functional_area_id
632 , FUNCTIONAL_AREA_DESC, mcs.category_set_name
633 FROM
634 mtl_category_sets_vl mcs
635 , mtl_default_category_sets_fk_v mdcs
636 WHERE
637 mcs.category_set_id = mdcs.category_set_id
638 AND mcs.default_category_id IS NULL;
639
640 CURSOR item_cat_assign_count_csr
641 ( p_inventory_item_id NUMBER
642 , p_organization_id NUMBER
643 , p_category_set_id NUMBER
644 , p_category_id NUMBER
645 ) IS
646 SELECT COUNT( category_id ), COUNT( DECODE(category_id, p_category_id, 1, NULL) )
647 FROM mtl_item_categories
648 WHERE
649 inventory_item_id = p_inventory_item_id
650 AND organization_id = p_organization_id
651 AND category_set_id = p_category_set_id;
652
653 CURSOR get_item_categories(cp_org_id NUMBER
654 ,cp_item_id NUMBER)
655 IS
656 SELECT category_set_id
657 ,category_id
658 FROM mtl_item_categories
659 WHERE organization_id = cp_org_id
660 AND inventory_item_id = cp_item_id;
661
662 l_return_status VARCHAR2(1);
663 l_msg_count NUMBER;
664 l_msg_data VARCHAR2(2000);
665
666 BEGIN
667 -- Check if default category id is defined.
668
669 BEGIN
670
671 FOR Funct_Val IN Func_Area_csr LOOP
672 IF ( (Funct_Val.functional_area_id = 1 and X_inventory_item_flag = 'Y' )
673 Or (Funct_Val.functional_area_id = 2 and X_purchasing_item_flag = 'Y')
674 Or (Funct_Val.functional_area_id = 2 and X_internal_order_flag = 'Y')
675 Or (Funct_Val.functional_area_id = 3 and X_mrp_planning_code <> 6)
676 Or (Funct_Val.functional_area_id = 4 and X_serviceable_product_flag ='Y
677 ')
678 Or (Funct_Val.functional_area_id = 5 and X_costing_enabled_flag = 'Y')
679 Or (Funct_Val.functional_area_id = 6 and X_eng_item_flag = 'Y')
680 Or (Funct_Val.functional_area_id = 7 and X_customer_order_flag = 'Y')
681 Or (Funct_Val.functional_area_id = 9 and X_eam_item_type is NOT NULL)
682 Or (Funct_Val.functional_area_id = 10 and X_contract_item_type_code is NOT NULL)
683 --Bug: 2433351
684 /**Bug: 2801594 Commented No need to check for Product Functional Area.
685 Or (Funct_Val.functional_area_id = 11 and X_customer_order_flag = 'Y')
686 Or (Funct_Val.functional_area_id = 11 and X_internal_order_flag = 'Y')
687 **/
688 )THEN
689 l_Func_Area := Funct_Val.FUNCTIONAL_AREA_DESC;
690 l_Cat_Set_Name := Funct_Val.CATEGORY_SET_NAME;
691 RAISE Cat_Set_No_Default_Cat;
692 END IF;
693 END LOOP;
694
695 IF ( Func_Area_csr%ISOPEN ) THEN
696 CLOSE Func_Area_csr;
697 END IF;
698
699 EXCEPTION
700
701 WHEN NO_DATA_FOUND THEN
702 IF ( Func_Area_csr%ISOPEN ) THEN
703 CLOSE Func_Area_csr;
704 END IF;
705
706 WHEN Cat_Set_No_Default_Cat THEN
707 IF ( Func_Area_csr%ISOPEN ) THEN
708 CLOSE Func_Area_csr;
709 END IF;
710 FND_MESSAGE.SET_NAME ('INV', 'INV_CAT_SET_NO_DEFAULT_CAT');
711 FND_MESSAGE.SET_TOKEN ('ENTITY1', l_Func_Area);
712 FND_MESSAGE.SET_TOKEN ('ENTITY2', l_Cat_Set_Name);
713 APP_EXCEPTION.Raise_Exception;
714
715 END; -- Check of default category
716
717
718 -- Get the folder item all category assignments count,
719 -- and the folder item new category (passed in as a parameter) assignments count.
720
721 IF ( p_Folder_Category_Set_id IS NOT NULL
722 AND p_Folder_Item_Category_id IS NOT NULL ) THEN
723
724 OPEN item_cat_assign_count_csr
725 ( p_inventory_item_id => X_item_id
726 , p_organization_id => X_org_id
727 , p_category_set_id => p_Folder_Category_Set_id
728 , p_category_id => p_Folder_Item_Category_id
729 );
730
731 FETCH item_cat_assign_count_csr
732 INTO l_the_item_assign_count, l_the_cat_assign_count;
733
734 CLOSE item_cat_assign_count_csr;
735
736 END IF; -- Folder category id IS NOT NULL
737
738 ---------------------------------
739 -- Insert Master or Org Update --
740 ---------------------------------
741
742 if ( X_event in ('INSERT', 'ITEM_ORG') ) then
743
744 -- Insert/Update folder item category assignment.
745 -- Since this in effect superceedes a default assignment, do this
746 -- before a functional area default category assignment.
747 IF ( p_Folder_Category_Set_id IS NOT NULL
748 AND p_Folder_Item_Category_id IS NOT NULL ) THEN
749 -- INSERT or UPDATE folder item category assignment, depending
750 -- on the item current assignments.
751
752 IF ( l_the_item_assign_count = 0 ) THEN
753
754 INSERT INTO mtl_item_categories
755 (
756 inventory_item_id
757 , organization_id
758 , category_set_id
759 , category_id
760 , last_update_date
761 , last_updated_by
762 , creation_date
763 , created_by
764 , last_update_login,
765 program_application_id,
766 program_id,
767 program_update_date,
768 request_id
769 )
770 SELECT
771 X_item_id
772 , X_org_id
773 , p_Folder_Category_Set_id
774 , p_Folder_Item_Category_id
775 , SYSDATE
776 , X_last_updated_by
777 , SYSDATE
778 , X_last_updated_by
779 , -1,
780 -1,
781 -1,
782 SYSDATE,
783 -1
784 FROM
785 dual;
786
787 --* Added for Bug 4491340
788 IF SQL%ROWCOUNT > 0 THEN
789 l_cat_ins_upd := TRUE;
790 INV_ITEM_EVENTS_PVT.Raise_Events(
791 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
792 ,p_dml_type => 'CREATE'
793 ,p_inventory_item_id => X_item_id
794 ,p_organization_id => X_org_id
795 ,p_category_set_id => p_Folder_Category_Set_id
796 ,p_category_id => p_Folder_Item_Category_id);
797 END IF;
798
799 /*
800 WHERE
801 -- Check if the item already has a category assignment in this category set
802 NOT EXISTS
803 ( SELECT 'x'
804 FROM mtl_item_categories mic
805 WHERE
806 mic.inventory_item_id = X_item_id
807 AND mic.organization_id = X_org_id
808 AND mic.category_set_id = p_Folder_Category_Set_id
809 )
810 ;
811 */
812 --* End of Bug 4491340
813
814 ELSIF ( l_the_item_assign_count = 1
815 AND l_the_cat_assign_count = 0 ) THEN
816
817 UPDATE mtl_item_categories
818 SET
819 category_id = p_Folder_Item_Category_id
820 , last_update_date = SYSDATE
821 , last_updated_by = X_last_updated_by
822 , last_update_login = -1
823 WHERE
824 inventory_item_id = X_item_id
825 AND organization_id = X_org_id
826 AND category_set_id = p_Folder_Category_Set_id
827 AND category_id =
828 ( SELECT mic.category_id
829 FROM mtl_item_categories mic
830 WHERE
831 mic.inventory_item_id = X_item_id
832 AND mic.organization_id = X_org_id
833 AND mic.category_set_id = p_Folder_Category_Set_id
834 )
835 ;
836
837 --* Added for Bug 4491340
838 IF SQL%ROWCOUNT > 0 THEN
839 l_cat_ins_upd := TRUE;
840 INV_ITEM_EVENTS_PVT.Raise_Events(
841 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
842 ,p_dml_type => 'UPDATE'
843 ,p_inventory_item_id => X_item_id
844 ,p_organization_id => X_org_id
845 ,p_category_set_id => p_Folder_Category_Set_id
846 ,p_category_id => p_Folder_Item_Category_id);
847 END IF;
848 --* End of Bug 4491340
849
850 END IF; -- insert/update
851
852 END IF; -- Folder category id IS NOT NULL
853
854 -- Default category assignment for a functional area.
855 -- Use the same statement if called from either Define or Update Item form.
856
857 -- Bug:2433351 an org item update belonging to the Product Reporting functional area
858
859 if ( X_event = 'ITEM_ORG') then
860
861 SELECT
862 p.organization_id
863 ,s.category_set_id
864 ,s.default_category_id
865 BULK COLLECT INTO
866 l_organizations_rec
867 ,l_category_sets_rec
868 ,l_categories_rec
869 FROM
870 mtl_category_sets_b s
871 , mtl_parameters p
872 WHERE
873 p.master_organization_id = X_master_org_id
874 AND s.default_category_id IS NOT NULL --Bug: 2801594
875 AND s.category_set_id =
876 ( SELECT d.category_set_id
877 FROM mtl_default_category_sets d
878 WHERE
879 d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 11, 0 )
880 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 11, 0 )
881 )
882 AND EXISTS
883 ( SELECT 'x'
884 FROM mtl_system_items_b i
885 WHERE
886 i.inventory_item_id = X_item_id
887 AND i.organization_id = p.organization_id
888 )
889 -- Check if the item already has any category assignment
890 AND NOT EXISTS
891 ( SELECT 'x'
892 FROM mtl_item_categories mic
893 WHERE
894 mic.inventory_item_id = X_item_id
895 AND mic.organization_id = p.organization_id
896 AND mic.category_set_id = s.category_set_id);
897
898 FORALL I in l_organizations_rec.FIRST .. l_organizations_rec.LAST
899 INSERT INTO mtl_item_categories(
900 inventory_item_id
901 ,organization_id
902 ,category_set_id
903 ,category_id
904 ,last_update_date
905 ,last_updated_by
906 ,creation_date
907 ,created_by
908 ,last_update_login
909 ,program_application_id
910 ,program_id
911 ,program_update_date
912 ,request_id)
913 VALUES(
914 x_item_id
915 ,l_organizations_rec(i)
916 ,l_category_sets_rec(i)
917 ,l_categories_rec(i)
918 ,sysdate
919 ,X_last_updated_by
920 ,sysdate
921 ,X_last_updated_by
922 ,-1
923 ,-1
924 ,-1
925 ,sysdate
926 ,-1);
927
928 IF l_organizations_rec.COUNT > 0 THEN
929 --* Added for Bug 4491340
930 l_cat_ins_upd := TRUE;
931
932 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST
933 LOOP
934 INV_ITEM_EVENTS_PVT.Raise_Events(
935 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
936 ,p_dml_type => 'CREATE'
937 ,p_inventory_item_id => X_item_id
938 ,p_organization_id => l_organizations_rec(i)
939 ,p_category_set_id => l_category_sets_rec(i)
940 ,p_category_id => l_categories_rec(i) );
941 END LOOP;
942 END IF;
943
944
945 end if;
946
947 SELECT
948 s.category_set_id
949 ,s.default_category_id
950 BULK COLLECT INTO
951 l_category_sets_rec
952 ,l_categories_rec
953 FROM
954 mtl_category_sets_b s
955 WHERE
956 s.category_set_id IN
957 ( SELECT d.category_set_id
958 FROM mtl_default_category_sets d
959 WHERE
960 d.functional_area_id = DECODE( X_inventory_item_flag, 'Y', 1, 0 )
961 OR d.functional_area_id = DECODE( X_purchasing_item_flag, 'Y', 2, 0 )
962 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 2, 0 )
963 OR d.functional_area_id = DECODE( X_mrp_planning_code, 6, 0, 3 )
964 OR d.functional_area_id = DECODE( X_serviceable_product_flag, 'Y', 4, 0 )
965 OR d.functional_area_id = DECODE( X_costing_enabled_flag, 'Y', 5, 0 )
966 OR d.functional_area_id = DECODE( X_eng_item_flag, 'Y', 6, 0 )
967 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 7, 0 )
968 OR d.functional_area_id = DECODE( NVL(X_eam_item_type, 0), 0, 0, 9 )
969 OR d.functional_area_id =
970 DECODE( X_contract_item_type_code,
971 'SERVICE' , 10,
972 'WARRANTY' , 10,
973 'SUBSCRIPTION' , 10,
974 'USAGE' , 10, 0 )
975 -- These Contract Item types also imply an item belonging to the Service functional area
976 OR d.functional_area_id =
977 DECODE( X_contract_item_type_code,
978 'SERVICE' , 4,
979 'WARRANTY' , 4, 0 )
980 --Bug:2433351
981 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 11, 0 )
982 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 11, 0 )
983 )
984 AND s.default_category_id IS NOT NULL --Bug: 2801594
985 -- Check if the item already has any category assignment
986 AND NOT EXISTS
987 ( SELECT 'x'
988 FROM mtl_item_categories mic
989 WHERE
990 mic.inventory_item_id = X_item_id
991 AND mic.organization_id = X_org_id
992 AND mic.category_set_id = s.category_set_id
993 );
994
995
996 FORALL I IN l_categories_rec.FIRST .. l_categories_rec.LAST
997 INSERT INTO mtl_item_categories(
998 inventory_item_id
999 ,organization_id
1000 ,category_set_id,
1001 category_id,
1002 last_update_date,
1003 last_updated_by,
1004 creation_date,
1005 created_by,
1006 last_update_login,
1007 program_application_id,
1008 program_id,
1009 program_update_date,
1010 request_id)
1011 VALUES(
1012 X_item_id
1013 ,X_org_id
1014 ,l_category_sets_rec(i),
1015 l_categories_rec(i),
1016 sysdate,
1017 X_last_updated_by,
1018 sysdate,
1019 X_last_updated_by,
1020 -1,
1021 -1,
1022 -1,
1023 sysdate,
1024 -1);
1025
1026
1027 IF l_categories_rec.COUNT > 0 THEN
1028 --* Added for Bug 4491340
1029 l_cat_ins_upd := TRUE;
1030 FOR I IN l_categories_rec.FIRST .. l_categories_rec.LAST
1031 LOOP
1032 INV_ITEM_EVENTS_PVT.Raise_Events(
1033 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1034 ,p_dml_type => 'CREATE'
1035 ,p_inventory_item_id => X_item_id
1036 ,p_organization_id => X_org_id
1037 ,p_category_set_id => l_category_sets_rec(i)
1038 ,p_category_id => l_categories_rec(i) );
1039 END LOOP;
1040 END IF;
1041
1042
1043 -------------------
1044 -- Update Master --
1045 -------------------
1046
1047 elsif ( X_event = 'UPDATE' ) then
1048
1049 -- Insert folder item category assignment.
1050 -- Since this in effect superceedes a default assignment, do this
1051 -- before a functional area default category assignment.
1052
1053 IF ( p_Folder_Category_Set_id IS NOT NULL
1054 AND p_Folder_Item_Category_id IS NOT NULL ) THEN
1055
1056 -- INSERT or UPDATE category assignment, depending on the item
1057 -- current assignments.
1058
1059 IF ( l_the_item_assign_count = 0 ) THEN
1060
1061 SELECT
1062 p.organization_id
1063 BULK COLLECT INTO
1064 l_organizations_rec
1065 FROM
1066 mtl_parameters p
1067 WHERE
1068 p.master_organization_id = X_master_org_id
1069 AND EXISTS
1070 ( SELECT 'x'
1071 FROM mtl_system_items_b i
1072 WHERE
1073 i.inventory_item_id = X_item_id
1074 AND i.organization_id = p.organization_id
1075 )
1076 -- Check if org item already has a category assignment in this category set
1077 AND NOT EXISTS
1078 ( SELECT 'x'
1079 FROM mtl_item_categories mic
1080 WHERE
1081 mic.inventory_item_id = X_item_id
1082 AND mic.organization_id = p.organization_id
1083 AND mic.category_set_id = p_Folder_Category_Set_id
1084 );
1085
1086 FORALL I in l_organizations_rec.FIRST .. l_organizations_rec.LAST
1087 INSERT INTO mtl_item_categories(
1088 inventory_item_id
1089 ,organization_id
1090 ,category_set_id
1091 ,category_id
1092 ,last_update_date
1093 ,last_updated_by
1094 ,creation_date
1095 ,created_by
1096 ,last_update_login,
1097 program_application_id,
1098 program_id,
1099 program_update_date,
1100 request_id
1101 )
1102 VALUES(
1103 X_item_id
1104 ,l_organizations_rec(i)
1105 ,p_Folder_Category_Set_id
1106 ,p_Folder_Item_Category_id
1107 ,SYSDATE
1108 ,X_last_updated_by
1109 ,SYSDATE
1110 ,X_last_updated_by
1111 ,-1,
1112 -1,
1113 -1,
1114 SYSDATE,
1115 -1);
1116
1117 IF l_organizations_rec.COUNT > 0 THEN
1118 --* Added for Bug 4491340
1119 l_cat_ins_upd := TRUE;
1120 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST
1121 LOOP
1122 INV_ITEM_EVENTS_PVT.Raise_Events(
1123 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1124 ,p_dml_type => 'CREATE'
1125 ,p_inventory_item_id => X_item_id
1126 ,p_organization_id => l_organizations_rec(i)
1127 ,p_category_set_id => p_Folder_Category_Set_id
1128 ,p_category_id => p_Folder_Item_Category_id);
1129 END LOOP;
1130 END IF;
1131
1132 ELSIF ( l_the_item_assign_count = 1
1133 AND l_the_cat_assign_count = 0 ) THEN
1134
1135 UPDATE mtl_item_categories
1136 SET
1137 category_id = p_Folder_Item_Category_id
1138 , last_update_date = SYSDATE
1139 , last_updated_by = X_last_updated_by
1140 , last_update_login = -1
1141 WHERE
1142 inventory_item_id = X_item_id
1143 AND organization_id IN
1144 ( SELECT p.organization_id
1145 FROM mtl_parameters p
1146 WHERE
1147 p.master_organization_id = X_master_org_id
1148 )
1149 AND category_set_id = p_Folder_Category_Set_id
1150 RETURNING ORGANIZATION_ID
1151 BULK COLLECT INTO l_organizations_rec
1152 ;
1153
1154 IF l_organizations_rec.COUNT > 0 THEN
1155 --* Added for Bug 4491340
1156 l_cat_ins_upd := TRUE;
1157
1158 FOR I IN l_organizations_rec.FIRST .. l_organizations_rec.LAST
1159 LOOP
1160 INV_ITEM_EVENTS_PVT.Raise_Events(
1161 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1162 ,p_dml_type => 'UPDATE'
1163 ,p_inventory_item_id => X_item_id
1164 ,p_organization_id => l_organizations_rec(i)
1165 ,p_category_set_id => p_Folder_Category_Set_id
1166 ,p_category_id => p_Folder_Item_Category_id);
1167 END LOOP;
1168 END IF;
1169
1170 END IF; -- insert/update
1171
1172 END IF; -- Folder category id IS NOT NULL
1173
1174 -- Default category assignment for a functional area.
1175
1176 SELECT
1177 p.organization_id
1178 ,s.category_set_id
1179 ,s.default_category_id
1180 BULK COLLECT INTO
1181 l_organizations_rec
1182 ,l_category_sets_rec
1183 ,l_categories_rec
1184 FROM
1185 mtl_category_sets_b s
1186 , mtl_parameters p
1187 WHERE
1188 p.master_organization_id = X_master_org_id
1189 AND s.default_category_id IS NOT NULL --Bug: 2801594
1190 AND s.category_set_id IN
1191 ( SELECT d.category_set_id
1192 FROM mtl_default_category_sets d
1193 WHERE
1194 d.functional_area_id = DECODE( X_inventory_item_flag, 'Y', 1, 0 )
1195 OR d.functional_area_id = DECODE( X_purchasing_item_flag, 'Y', 2, 0 )
1196 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 2, 0 )
1197 OR d.functional_area_id = DECODE( X_mrp_planning_code, 6, 0, 3 )
1198 OR d.functional_area_id = DECODE( X_serviceable_product_flag, 'Y', 4, 0 )
1199 OR d.functional_area_id = DECODE( X_costing_enabled_flag, 'Y', 5, 0 )
1200 OR d.functional_area_id = DECODE( X_eng_item_flag, 'Y', 6, 0 )
1201 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 7, 0 )
1202 OR d.functional_area_id = DECODE( NVL(X_eam_item_type, 0), 0, 0, 9 )
1203 OR d.functional_area_id =
1204 DECODE( X_contract_item_type_code,
1205 'SERVICE' , 10,
1206 'WARRANTY' , 10,
1207 'SUBSCRIPTION' , 10,
1208 'USAGE' , 10, 0 )
1209 -- These Contract Item types also imply an item belonging to the Service functional area
1210 OR d.functional_area_id =
1211 DECODE( X_contract_item_type_code,
1212 'SERVICE' , 4,
1213 'WARRANTY' , 4, 0 )
1214 --Bug:2433351
1215 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 11, 0 )
1216 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 11, 0 )
1217 )
1218 AND EXISTS
1219 ( SELECT 'x'
1220 FROM mtl_system_items_b i
1221 WHERE
1222 i.inventory_item_id = X_item_id
1223 AND i.organization_id = p.organization_id
1224 )
1225 -- Check if the item already has any category assignment
1226 AND NOT EXISTS
1227 ( SELECT 'x'
1228 FROM mtl_item_categories mic
1229 WHERE
1230 mic.inventory_item_id = X_item_id
1231 AND mic.organization_id = p.organization_id
1232 AND mic.category_set_id = s.category_set_id
1233 )
1234 /* Bug 2666280 */
1235 AND EXISTS
1236 --Replaced org_organizations_definitions view
1237 ( SELECT 'x'
1238 FROM hr_organization_information
1239 WHERE organization_id = p.organization_id
1240 AND org_information1 = 'INV' -- Inventory Enabled flag.
1241 AND org_information2 = 'Y'
1242 AND org_information_context || '' = 'CLASS');
1243
1244 FORALL I IN l_categories_rec.FIRST .. l_categories_rec.LAST
1245 INSERT INTO mtl_item_categories
1246 (
1247 inventory_item_id
1248 ,organization_id
1249 ,category_set_id,
1250 category_id,
1251 last_update_date,
1252 last_updated_by,
1253 creation_date,
1254 created_by,
1255 last_update_login,
1256 program_application_id,
1257 program_id,
1258 program_update_date,
1259 request_id)
1260 VALUES(
1261 X_item_id
1262 ,l_organizations_rec(i)
1263 ,l_category_sets_rec(i),
1264 l_categories_rec(i),
1265 sysdate,
1266 X_last_updated_by,
1267 sysdate,
1268 X_last_updated_by,
1269 -1,
1270 -1,
1271 -1,
1272 sysdate,
1273 -1);
1274
1275 IF l_organizations_rec.COUNT > 0 THEN
1276 --* Added for Bug 4491340
1277 l_cat_ins_upd := TRUE;
1278
1279 FOR I IN l_categories_rec.FIRST .. l_categories_rec.LAST
1280 LOOP
1281 INV_ITEM_EVENTS_PVT.Raise_Events(
1282 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1283 ,p_dml_type => 'CREATE'
1284 ,p_inventory_item_id => X_item_id
1285 ,p_organization_id => l_organizations_rec(i)
1286 ,p_category_set_id => l_category_sets_rec(i)
1287 ,p_category_id => l_categories_rec(i) );
1288 END LOOP;
1289 END IF;
1290
1291 --------------------
1292 -- Org Assignment --
1293 --------------------
1294
1295 elsif ( X_event = 'ORG_ASSIGN' ) then
1296
1297 -- Insert folder item category assignment.
1298 -- Since this in effect superceedes a default assignment, do this
1299 -- before a functional area default category assignment.
1300
1301 IF ( p_Folder_Category_Set_id IS NOT NULL
1302 AND p_Folder_Item_Category_id IS NOT NULL ) THEN
1303
1304 -- INSERT or UPDATE folder item category assignment, depending
1305
1306 INSERT INTO mtl_item_categories(
1307 inventory_item_id
1308 ,organization_id
1309 ,category_set_id
1310 ,category_id
1311 ,last_update_date,
1312 last_updated_by,
1313 creation_date,
1314 created_by,
1315 last_update_login,
1316 program_application_id,
1317 program_id,
1318 program_update_date,
1319 request_id)
1320 SELECT
1321 X_item_id
1322 ,X_org_id
1323 ,p_Folder_Category_Set_id
1324 ,p_Folder_Item_Category_id
1325 ,sysdate,
1326 X_last_updated_by,
1327 sysdate,
1328 X_last_updated_by,
1329 -1,
1330 -1,
1331 -1,
1332 sysdate,
1333 -1
1334 FROM
1335 mtl_category_sets_b s
1336 , mtl_item_categories c
1337 WHERE
1338 c.inventory_item_id = X_item_id
1339 AND c.organization_id = X_master_org_id
1340 AND c.category_set_id = p_Folder_Category_Set_id
1341 AND c.category_id = p_Folder_Item_Category_id
1342 AND s.category_set_id = p_Folder_Category_Set_id
1343 AND s.control_level = 1
1344 -- Check if the item already has a category assignment in this category set
1345 AND NOT EXISTS
1346 ( SELECT 'x'
1347 FROM mtl_item_categories mic
1348 WHERE
1349 mic.inventory_item_id = X_item_id
1350 AND mic.organization_id = X_org_id
1351 AND mic.category_set_id = p_Folder_Category_Set_id
1352 )
1353 ;
1354
1355 --* Added for Bug 4491340
1356 IF SQL%ROWCOUNT > 0 THEN
1357 INV_ITEM_EVENTS_PVT.Raise_Events(
1358 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1359 ,p_dml_type => 'CREATE'
1360 ,p_inventory_item_id => X_item_id
1361 ,p_organization_id => X_org_id
1362 ,p_category_set_id => p_Folder_Category_Set_id
1363 ,p_category_id => p_Folder_Item_Category_id);
1364 l_cat_ins_upd := TRUE;
1365 END IF;
1366 --* End of Bug 4491340
1367
1368 END IF; -- Folder category id IS NOT NULL
1369
1370 -- Default category assignment for a functional area.
1371
1372 SELECT
1373 c.category_set_id
1374 ,c.category_id
1375 BULK COLLECT INTO
1376 l_category_sets_rec
1377 ,l_categories_rec
1378 FROM
1379 mtl_category_sets_b s
1380 , mtl_item_categories c
1381 WHERE
1382 c.inventory_item_id = X_item_id
1383 AND c.organization_id = X_master_org_id
1384 AND s.category_set_id = c.category_set_id
1385 AND ( s.control_level = 1
1386 OR EXISTS
1387 ( SELECT 'x'
1388 FROM mtl_default_category_sets d
1389 WHERE
1390 d.category_set_id = s.category_set_id
1391 AND
1392 (d.functional_area_id = DECODE( X_inventory_item_flag, 'Y', 1, 0 )
1393 OR d.functional_area_id = DECODE( X_purchasing_item_flag, 'Y', 2, 0 )
1394 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 2, 0 )
1395 OR d.functional_area_id = DECODE( X_mrp_planning_code, 6, 0, 3 )
1396 OR d.functional_area_id = DECODE( X_serviceable_product_flag, 'Y', 4, 0 )
1397 OR d.functional_area_id = DECODE( X_costing_enabled_flag, 'Y', 5, 0 )
1398 OR d.functional_area_id = DECODE( X_eng_item_flag, 'Y', 6, 0 )
1399 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 7, 0 )
1400 OR d.functional_area_id = DECODE( NVL(X_eam_item_type, 0), 0, 0, 9 )
1401 OR d.functional_area_id =
1402 DECODE( X_contract_item_type_code,
1403 'SERVICE' , 10,
1404 'WARRANTY' , 10,
1405 'SUBSCRIPTION' , 10,
1406 'USAGE' , 10, 0 )
1407 -- These Contract Item types also imply an item belonging to the Service functional area
1408 OR d.functional_area_id =
1409 DECODE( X_contract_item_type_code,
1410 'SERVICE' , 4,
1411 'WARRANTY' , 4, 0 )
1412 --Bug:2433351
1413 OR d.functional_area_id = DECODE( X_customer_order_flag, 'Y', 11, 0 )
1414 OR d.functional_area_id = DECODE( X_internal_order_flag, 'Y', 11, 0 ))
1415 ))
1416 -- Check if the item already has any category assignment
1417 -- Bug #1814719.
1418 AND NOT EXISTS
1419 ( SELECT 'x'
1420 FROM mtl_item_categories mic
1421 WHERE
1422 mic.inventory_item_id = X_item_id
1423 AND mic.organization_id = X_org_id
1424 AND mic.category_set_id = s.category_set_id
1425 );
1426
1427 FORALL I IN l_categories_rec.FIRST .. l_categories_rec.LAST
1428 INSERT INTO mtl_item_categories
1429 (
1430 inventory_item_id
1431 ,organization_id
1432 ,category_set_id,
1433 category_id,
1434 last_update_date,
1435 last_updated_by,
1436 creation_date,
1437 created_by,
1438 last_update_login,
1439 program_application_id,
1440 program_id,
1441 program_update_date,
1442 request_id)
1443 Values(
1444 X_item_id
1445 ,X_org_id
1446 ,l_category_sets_rec(i),
1447 l_categories_rec(i),
1448 sysdate,
1449 X_last_updated_by,
1450 sysdate,
1451 X_last_updated_by,
1452 -1,
1453 -1,
1454 -1,
1455 sysdate,
1456 -1);
1457
1458 IF l_categories_rec.COUNT > 0 THEN
1459 --* Added for Bug 4491340
1460 l_cat_ins_upd := TRUE;
1461
1462 FOR I IN l_categories_rec.FIRST .. l_categories_rec.LAST
1463 LOOP
1464 INV_ITEM_EVENTS_PVT.Raise_Events(
1465 p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
1466 ,p_dml_type => 'CREATE'
1467 ,p_inventory_item_id => X_item_id
1468 ,p_organization_id => X_org_id
1469 ,p_category_set_id => l_category_sets_rec(i)
1470 ,p_category_id => l_categories_rec(i) );
1471 END LOOP;
1472 END IF;
1473
1474 end if; -- X_event
1475
1476 --
1477 -- Sync item category assignment with item record in STAR table.
1478 --
1479 --Bug: 2718703 checking for ENI product before calling their package
1480
1481 --* Added IF condition for Bug 4491340
1482 IF l_cat_ins_upd THEN
1483 -- Start Bug: 3185516
1484 FOR cr IN get_item_categories(X_org_id,X_item_id)
1485 LOOP
1486 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1487 p_api_version => 1.0
1488 ,p_init_msg_list => FND_API.g_TRUE
1489 ,p_inventory_item_id => X_item_id
1490 ,p_organization_id => X_org_id
1491 ,p_category_set_id => cr.category_set_id
1492 ,p_old_category_id => NULL
1493 ,p_new_category_id => cr.category_id
1494 ,x_return_status => l_return_Status
1495 ,x_msg_count => l_msg_count
1496 ,x_msg_data => l_msg_data);
1497
1498 IF ( l_return_status = FND_API.g_RET_STS_ERROR ) THEN
1499 FND_MESSAGE.Set_Encoded (l_msg_data);
1500 APP_EXCEPTION.Raise_Exception;
1501 ELSIF ( l_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
1502 FND_MESSAGE.Set_Encoded (l_msg_data);
1503 APP_EXCEPTION.Raise_Exception;
1504 END IF;
1505 END LOOP;
1506 -- End Bug: 3185516
1507 l_cat_ins_upd := FALSE; --* Added for Bug 4491340
1508
1509 END IF;
1510 END Insert_Categories;
1511
1512
1513 PROCEDURE Insert_Costing_Category
1514 (
1515 X_item_id number
1516 , X_org_id number
1517 )
1518 IS
1519 l_user_id NUMBER := NVL(FND_GLOBAL.User_Id, 0);
1520 BEGIN
1521 insert into mtl_item_categories
1522 (inventory_item_id,
1523 category_set_id,
1524 category_id,
1525 last_update_date,
1526 last_updated_by,
1527 creation_date,
1528 created_by,
1529 last_update_login,
1530 program_application_id,
1531 program_id,
1532 program_update_date,
1533 request_id,
1534 organization_id)
1535 select
1536 X_item_id,
1537 s.category_set_id,
1538 s.default_category_id,
1539 sysdate,
1540 l_user_id,
1541 sysdate,
1542 l_user_id,
1543 -1,
1544 -1,
1545 -1,
1546 NULL,
1547 -1,
1548 X_org_id
1549 from mtl_category_sets_B s
1550 where s.category_set_id in
1551 (select d.category_set_id
1552 from mtl_default_category_sets d
1553 where d.functional_area_id = 5)
1554 and not exists
1555 ( select 'x'
1556 from mtl_item_categories c
1557 where c.inventory_item_id = X_item_id
1558 and c.organization_id = X_org_id
1559 and c.category_set_id = s.category_set_id
1560 );
1561
1562 --* Added for Bug 4491340
1563 IF SQL%ROWCOUNT > 0 THEN
1564 l_cat_ins_upd := TRUE;
1565 END IF;
1566 --* End of Bug 4491340
1567
1568 END Insert_Costing_Category;
1569
1570
1571 PROCEDURE Insert_Cost_Row
1572 (
1573 X_item_id number
1574 , X_org_id number
1575 , X_inv_install number
1576 , X_last_updated_by number
1577 )
1578 IS
1579 cst_return number;
1580 cst_error varchar2(50);
1581
1582 -- if org_assign, use org_assign.last_updated_by
1583
1584 BEGIN
1585
1586 if (X_inv_install = 401) then
1587
1588 CSTPIICC.CSTPIICI(X_item_id,
1589 X_org_id,
1590 X_last_updated_by,
1591 cst_return,
1592 cst_error);
1593
1594 -- how to handle if error returned
1595
1596 end if;
1597
1598 END Insert_Cost_Row;
1599
1600
1601 -- This procedure should be called only if inventory_asset_flag is
1602 -- updated to Y and costing_enabled_flag = Y.
1603 -- Check in the form if that condition is true before calling this
1604 -- procedure.
1605
1606 PROCEDURE Insert_Cost_Details
1607 (
1608 X_item_id number
1609 , X_org_id number
1610 , X_inv_install number
1611 , X_last_updated_by number
1612 , X_cst_item_type number
1613 )
1614 IS
1615 cost_method number;
1616 cst_lot_size number;
1617 cst_shrink_rate number;
1618 cst_return number;
1619 cst_error varchar2(50);
1620 BEGIN
1621
1622 if (X_inv_install = 401) then
1623
1624 INVIDIT2.Insert_Costing_Category(X_item_id, X_org_id);
1625
1626 select primary_cost_method
1627 into cost_method
1628 from mtl_parameters
1629 where organization_id = X_org_id;
1630
1631 begin
1632
1633 select lot_size, shrinkage_rate
1634 into cst_lot_size, cst_shrink_rate
1635 from cst_item_costs
1636 where inventory_item_id = X_item_id
1637 and organization_id = X_org_id
1638 and cost_type_id = cost_method; --Bug#7149985 : Changed from cost_type_id = 1;
1639
1640 exception
1641 when NO_DATA_FOUND then
1642 cst_lot_size := null;
1643 cst_shrink_rate := null;
1644 end;
1645
1646 CSTPIDIC.CSTPIDIO(X_item_id,
1647 X_org_id,
1648 X_last_updated_by,
1649 cost_method,
1650 X_cst_item_type,
1651 cst_lot_size,
1652 cst_shrink_rate,
1653 cst_return,
1654 cst_error);
1655
1656 -- if (cst_return <> 0) then
1657 -- show error
1658 -- end if;
1659
1660 end if;
1661
1662 END Insert_Cost_Details;
1663
1664
1665 PROCEDURE Insert_Uom_Conversion
1666 (
1667 X_item_id number
1668 , X_allowed_unit_code number
1669 , X_primary_uom varchar2
1670 , X_primary_uom_code varchar2
1671 , X_primary_uom_class varchar2
1672 )
1673 IS
1674 v_rate number;
1675 BEGIN
1676
1677 -- Only insert if conversion is item-specific (= 1)
1678 --
1679 if (X_allowed_unit_code = 1) then
1680
1681 begin
1682 select conversion_rate
1683 into v_rate
1684 from mtl_uom_conversions
1685 where inventory_item_id = 0
1686 and uom_code = X_primary_uom_code;
1687
1688 --and unit_of_measure = X_primary_uom;
1689
1690 exception
1691 when NO_DATA_FOUND then
1692 v_rate := null;
1693 end;
1694
1695 insert into mtl_uom_conversions
1696 ( unit_of_measure,
1697 uom_code,
1698 uom_class,
1699 inventory_item_id,
1700 conversion_rate,
1701 default_conversion_flag,
1702 last_update_date,
1703 last_updated_by,
1704 creation_date,
1705 created_by
1706 ) select
1707 unit_of_measure,
1708 uom_code,
1709 uom_class,
1710 X_item_id,
1711 decode(base_uom_flag, 'Y', 1, v_rate),
1712 'N',
1713 sysdate,
1714 0,
1715 sysdate,
1716 0
1717 from mtl_units_of_measure_vl
1718 where uom_code = X_primary_uom_code
1719 and not exists
1720 ( select 'x'
1721 from mtl_uom_conversions
1722 where inventory_item_id = X_item_id
1723 and uom_code = X_primary_uom_code
1724 );
1725
1726 end if;
1727
1728 END Insert_Uom_Conversion;
1729
1730
1731 PROCEDURE Delete_Categories
1732 (
1733 X_item_id number
1734 , X_org_id number
1735 )
1736 IS
1737
1738 CURSOR get_item_categories(cp_org_id NUMBER
1739 ,cp_item_id NUMBER
1740 ,cp_cat_set NUMBER)
1741 IS
1742 SELECT category_set_id
1743 ,category_id
1744 FROM mtl_item_categories
1745 WHERE organization_id = cp_org_id
1746 AND inventory_item_id = cp_item_id
1747 AND category_set_id <> cp_cat_set;
1748
1749 -- Product Family Category Set ID
1750 G_PF_Category_Set_ID CONSTANT NUMBER := 3;
1751
1752 l_return_status VARCHAR2(1);
1753 l_msg_count NUMBER;
1754 l_msg_data VARCHAR2(2000);
1755
1756
1757 BEGIN
1758
1759 -- Start Bug: 3185516
1760 FOR cr IN get_item_categories(X_org_id,X_item_id,G_PF_Category_Set_ID)
1761 LOOP
1762 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1763 p_api_version => 1.0
1764 ,p_init_msg_list => FND_API.g_TRUE
1765 ,p_inventory_item_id => X_item_id
1766 ,p_organization_id => X_org_id
1767 ,p_category_set_id => cr.category_set_id
1768 ,p_old_category_id => NULL
1769 ,p_new_category_id => cr.category_id
1770 ,x_return_status => l_return_Status
1771 ,x_msg_count => l_msg_count
1772 ,x_msg_data => l_msg_data);
1773
1774 IF ( l_return_status = FND_API.g_RET_STS_ERROR ) THEN
1775 FND_MESSAGE.Set_Encoded (l_msg_data);
1776 APP_EXCEPTION.Raise_Exception;
1777 ELSIF ( l_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
1778 FND_MESSAGE.Set_Encoded (l_msg_data);
1779 APP_EXCEPTION.Raise_Exception;
1780 END IF;
1781 END LOOP;
1782 -- End Bug: 3185516
1783
1784 delete from mtl_item_categories
1785 where inventory_item_id = X_item_id
1786 and organization_id = X_org_id
1787 and CATEGORY_SET_ID <> G_PF_Category_Set_ID;
1788
1789 END Delete_Categories;
1790
1791
1792 PROCEDURE Match_Catalog_Descr_Elements
1793 (
1794 X_item_id number
1795 , X_catalog_group_id number
1796 )
1797 IS
1798 BEGIN
1799
1800 -- First, delete old descriptive element values for this item.
1801 -- Then insert new elements for new catalog group.
1802
1803 delete from mtl_descr_element_values
1804 where inventory_item_id = X_item_id;
1805
1806 insert into mtl_descr_element_values
1807 ( inventory_item_id,
1808 element_name,
1809 default_element_flag,
1810 last_update_date,
1811 last_updated_by,
1812 creation_date,
1813 created_by,
1814 element_sequence
1815 )
1816 select
1817 X_item_id,
1818 element_name,
1819 default_element_flag,
1820 sysdate,
1821 0,
1822 sysdate,
1823 0,
1824 element_sequence
1825 from mtl_descriptive_elements
1826 where item_catalog_group_id = X_catalog_group_id;
1827
1828 END Match_Catalog_Descr_Elements;
1829
1830
1831 -- Procedure to insert Item Transaction Default SubInventories.
1832
1833 PROCEDURE Insert_Default_SubInventories ( X_Event VARCHAR2
1834 , X_item_id NUMBER
1835 , X_org_id NUMBER
1836 , P_Default_Move_Order_Sub_Inv VARCHAR2
1837 , P_Default_Receiving_Sub_Inv VARCHAR2
1838 , P_Default_Shipping_Sub_Inv VARCHAR2
1839 )
1840 IS
1841 l_user_id NUMBER := NVL(FND_GLOBAL.User_Id, 0);
1842
1843 l_process_code VARCHAR2(30);
1844 x_return_status VARCHAR2(100);
1845 x_msg_count NUMBER;
1846 x_msg_data VARCHAR2(2000);
1847 l_success VARCHAR2(100) := fnd_api.g_ret_sts_success;
1848 BEGIN
1849
1850 IF ( X_Event = 'INSERT' ) THEN
1851 l_process_code := 'INSERT';
1852 ELSE
1853 l_process_code := 'SYNC';
1854 END IF;
1855
1856 IF ( X_Event = 'INSERT' AND P_Default_Move_Order_Sub_Inv IS NULL )THEN
1857 NULL;
1858
1859 ELSIF ( NVL(P_Default_Move_Order_Sub_Inv,'x') <> '!' ) THEN
1860
1861 INV_ITEM_SUB_DEFAULT_PKG.INSERT_UPD_ITEM_SUB_DEFAULTS
1862 (
1863 p_organization_id => x_org_id
1864 , p_inventory_item_id => x_item_id
1865 , p_subinventory_code => P_Default_Move_Order_Sub_Inv
1866 , p_default_type => 3
1867 , p_creation_date => sysdate
1868 , p_created_by => l_user_id
1869 , p_last_update_date => sysdate
1870 , p_last_updated_by => l_user_id
1871 , p_process_code => l_process_code
1872 , p_commit => fnd_api.g_true
1873 , x_return_status => x_return_status
1874 , x_msg_count => x_msg_count
1875 , x_msg_data => x_msg_data);
1876
1877 IF NOT (x_return_status = l_success) THEN
1878 APP_EXCEPTION.Raise_Exception;
1879 END IF;
1880
1881 END IF;
1882
1883 IF ( X_Event = 'INSERT' AND P_Default_Receiving_Sub_Inv IS NULL )THEN
1884 NULL;
1885
1886 ELSIF ( NVL(P_Default_Receiving_Sub_Inv,'x') <> '!' ) THEN
1887
1888 INV_ITEM_SUB_DEFAULT_PKG.INSERT_UPD_ITEM_SUB_DEFAULTS
1889 (
1890 p_organization_id => x_org_id
1891 , p_inventory_item_id => x_item_id
1892 , p_subinventory_code => P_Default_Receiving_Sub_Inv
1893 , p_default_type => 2
1894 , p_creation_date => sysdate
1895 , p_created_by => l_user_id
1896 , p_last_update_date => sysdate
1897 , p_last_updated_by => l_user_id
1898 , p_process_code => l_process_code
1899 , p_commit => fnd_api.g_true
1900 , x_return_status => x_return_status
1901 , x_msg_count => x_msg_count
1902 , x_msg_data => x_msg_data);
1903
1904 IF NOT (x_return_status = l_success) THEN
1905 APP_EXCEPTION.Raise_Exception;
1906 END IF;
1907
1908 END IF;
1909
1910 IF ( X_Event = 'INSERT' AND P_Default_Shipping_Sub_Inv IS NULL )THEN
1911 NULL;
1912
1913 ELSIF ( NVL(P_Default_Shipping_Sub_Inv,'x') <> '!' ) THEN
1914
1915 INV_ITEM_SUB_DEFAULT_PKG.INSERT_UPD_ITEM_SUB_DEFAULTS
1916 (
1917 p_organization_id => x_org_id
1918 , p_inventory_item_id => x_item_id
1919 , p_subinventory_code => P_Default_Shipping_Sub_Inv
1920 , p_default_type => 1
1921 , p_creation_date => sysdate
1922 , p_created_by => l_user_id
1923 , p_last_update_date => sysdate
1924 , p_last_updated_by => l_user_id
1925 , p_process_code => l_process_code
1926 , p_commit => fnd_api.g_true
1927 , x_return_status => x_return_status
1928 , x_msg_count => x_msg_count
1929 , x_msg_data => x_msg_data);
1930
1931 IF NOT (x_return_status = l_success) THEN
1932 APP_EXCEPTION.Raise_Exception;
1933 END IF;
1934
1935 END IF;
1936
1937 END Insert_Default_SubInventories;
1938
1939 END INVIDIT2;