[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_PVT
Source
1 PACKAGE BODY EGO_ITEM_PVT AS
2 /* $Header: EGOVITMB.pls 120.33.12010000.5 2008/11/26 22:10:09 akbharga ship $ */
3
4 -----------------------
5 -- Private Data Type --
6 -----------------------
7
8 TYPE LOCAL_MEDIUM_VARCHAR_TABLE IS TABLE OF VARCHAR2(4000)
9 INDEX BY BINARY_INTEGER;
10
11 -- =============================================================================
12 -- Package constants and cursors
13 -- =============================================================================
14
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EGOVITMB.pls';
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_PVT';
17 G_APP_NAME CONSTANT VARCHAR2(3) := 'EGO';
18 G_PKG_NAME_TOKEN CONSTANT VARCHAR2(8) := 'PKG_NAME';
19 G_API_NAME_TOKEN CONSTANT VARCHAR2(8) := 'API_NAME';
20 G_PROC_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROC_NAME';
21 G_SQL_ERR_MSG_TOKEN CONSTANT VARCHAR2(11) := 'SQL_ERR_MSG';
22 G_PLSQL_ERR CONSTANT VARCHAR2(17) := 'EGO_PLSQL_ERR';
23 G_INVALID_PARAMS_MSG CONSTANT VARCHAR2(30) := 'EGO_API_INVALID_PARAMS';
24
25 G_EQ_VAL CONSTANT VARCHAR2(2) := 'EQ';
26 G_GT_VAL CONSTANT VARCHAR2(2) := 'GT';
27 G_GE_VAL CONSTANT VARCHAR2(2) := 'GE';
28 G_LT_VAL CONSTANT VARCHAR2(2) := 'LT';
29 G_LE_VAL CONSTANT VARCHAR2(2) := 'LE';
30
31 G_TRUE CONSTANT VARCHAR2(1) := 'T'; -- FND_API.G_TRUE;
32 G_FALSE CONSTANT VARCHAR2(1) := 'F'; -- FND_API.G_FALSE;
33
34 G_EGO_ITEM CONSTANT VARCHAR2(20) := 'EGO_ITEM';
35 G_HZ_USER_PARTY_TYPE CONSTANT VARCHAR2(20) := 'PERSON';
36 G_HZ_COMPANY_PARTY_TYPE CONSTANT VARCHAR2(20) := 'ORGANIZATION';
37
38 -- data securiry functions
39 G_FN_NAME_ADD_ROLE CONSTANT VARCHAR2(50) := 'EGO_ADD_ITEM_PEOPLE';
40 G_FN_NAME_PROMOTE CONSTANT VARCHAR2(50) := 'EGO_PRO_ITEM_LIFE_CYCLE';
41 G_FN_NAME_DEMOTE CONSTANT VARCHAR2(50) := 'EGO_DEM_ITEM_LIFE_CYCLE';
42 G_FN_NAME_CHANGE_STATUS CONSTANT VARCHAR2(50) := 'EGO_EDIT_ITEM_STATUS';
43 G_FN_NAME_EDIT_LC_PROJ CONSTANT VARCHAR2(50) := 'EGO_CREATE_ITEM_LC_TRACK_PROJ';
44
45 -- functional securiry functions
46 G_FN_NAME_ADMIN CONSTANT VARCHAR2(50) := 'EGO_ITEM_ADMINISTRATION';
47
48 TYPE DYNAMIC_CUR IS REF CURSOR;
49
50 -- =============================================================================
51 -- Package variables
52 -- =============================================================================
53
54 ------------------- BEGIN Bug 6531908: Cached user information --------------
55
56 g_username VARCHAR2(100);
57 g_party_id VARCHAR2(30);
58
59 --------------------- END Bug 6531908 ---------------------------------------
60
61
62 -- =============================================================================
63 -- Private Procedures
64 -- =============================================================================
65
66 -- ----------------------
67 --
68 -- Developer debugging
69 -- ----------------------
70 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
71 BEGIN
72 --sri_debug ('ITEM_PVT '||p_msg);
73 RETURN;
74 EXCEPTION
75 WHEN OTHERS THEN
76 NULL;
77 END code_debug;
78
79 ------------------------------------------
80 --
81 -- Check validation of start date
82 -- in context with the end date passed
83 --
84 ------------------------------------------
85 FUNCTION date_check (p_start_date IN DATE
86 ,p_end_date IN DATE
87 ,p_validation_type IN VARCHAR2
88 ) RETURN BOOLEAN IS
89 BEGIN
90 IF p_validation_type NOT IN
91 (G_EQ_VAL
92 ,G_GT_VAL
93 ,G_GE_VAL
94 ,G_LT_VAL
95 ,G_LE_VAL
96 ) THEN
97 RETURN FALSE;
98 END IF;
99 IF p_validation_type = G_EQ_VAL THEN
100 IF ( (p_start_date IS NULL AND p_end_date IS NULL)
101 OR
102 (p_start_date = p_end_date)
103 ) THEN
104 RETURN TRUE;
105 END IF;
106 ELSIF p_validation_type = G_GT_VAL THEN
107 IF (p_end_date IS NOT NULL AND NVL(p_start_date,p_end_date+1)>p_end_date) THEN
108 RETURN TRUE;
109 END IF;
110 ELSIF p_validation_type = G_GE_VAL THEN
111 IF ( (p_start_date IS NULL AND p_end_date IS NULL )
112 OR
113 (p_end_date IS NOT NULL AND NVL(p_start_date,p_end_date)>=p_end_date)
114 ) THEN
115 RETURN TRUE;
116 END IF;
117 ELSIF p_validation_type = G_LT_VAL THEN
118 IF (p_start_date IS NOT NULL AND p_start_date < NVL(p_end_date,p_start_date+1)) THEN
119 RETURN TRUE;
120 END IF;
121 ELSIF p_validation_type = G_LE_VAL THEN
122 IF ( (p_start_date IS NULL AND p_end_date IS NULL)
123 OR
124 (p_start_date IS NOT NULL AND p_start_date <= NVL(p_end_date,p_start_date))
125 ) THEN
126 RETURN TRUE;
127 END IF;
128 END IF;
129 RETURN FALSE;
130 END date_check;
131
132 ------------------------------------------
133 --
134 -- Check validity of organization details passed
135 --
136 ------------------------------------------
137 FUNCTION validate_org (x_organization_id IN OUT NOCOPY NUMBER
138 ,p_organization_code IN VARCHAR2
139 ,p_set_message IN VARCHAR2
140 ) RETURN BOOLEAN IS
141 l_dummy_char VARCHAR2(32767);
142 BEGIN
143 IF x_organization_id IS NOT NULL THEN
144 BEGIN
145 SELECT organization_id
146 INTO x_organization_id
147 from mtl_parameters
148 where organization_id = x_organization_id;
149 RETURN TRUE;
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF FND_API.To_Boolean(p_set_message) THEN
153 fnd_message.Set_Name(G_APP_NAME, 'EGO_ORGANIZATION_ID');
154 l_dummy_char := fnd_message.get();
155 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
156 fnd_message.Set_Token('NAME', l_dummy_char);
157 fnd_message.Set_Token('VALUE', x_organization_id);
158 fnd_msg_pub.Add;
159 END IF;
160 RETURN FALSE;
161 END;
162 ELSIF p_organization_code IS NOT NULL THEN
163 BEGIN
164 SELECT organization_id
165 INTO x_organization_id
166 from mtl_parameters
167 where organization_code = p_organization_code;
168 RETURN TRUE;
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF FND_API.To_Boolean(p_set_message) THEN
172 fnd_message.Set_Name(G_APP_NAME, 'EGO_ORGANIZATION_CODE');
173 l_dummy_char := fnd_message.get();
174 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
175 fnd_message.Set_Token('NAME', l_dummy_char);
176 fnd_message.Set_Token('VALUE', p_organization_code);
177 fnd_msg_pub.Add;
178 END IF;
179 RETURN FALSE;
180 END;
181 ELSE
182 -- x_organization_id := NULL;
183 RETURN FALSE;
184 END IF;
185 EXCEPTION
186 WHEN OTHERS THEN
187 -- x_organization_id := NULL;
188 RETURN FALSE;
189 END validate_org;
190
191 ------------------------------------------
192 --
193 -- Check validity of item details passed
194 --
195 ------------------------------------------
196 FUNCTION validate_item (x_inventory_item_id IN OUT NOCOPY NUMBER
197 ,x_item_number IN OUT NOCOPY VARCHAR2
198 ,x_approval_status OUT NOCOPY VARCHAR2
199 ,p_organization_id IN NUMBER
200 ,p_set_message IN VARCHAR2
201 ) RETURN BOOLEAN IS
202 l_dummy_char VARCHAR2(32767);
203 BEGIN
204 IF p_organization_id IS NOT NULL THEN
205 IF x_inventory_item_id IS NOT NULL THEN
206 BEGIN
207 SELECT itm.inventory_item_id, itm.approval_status, itm_num.concatenated_segments
208 INTO x_inventory_item_id, x_approval_status, x_item_number
209 FROM mtl_system_items_b itm, mtl_system_items_b_kfv itm_num
210 WHERE itm.inventory_item_id = x_inventory_item_id
211 AND itm.organization_id = p_organization_id
212 AND itm_num.inventory_item_id = itm.inventory_item_id
213 AND itm_num.organization_id = itm.organization_id;
214 RETURN TRUE;
215 EXCEPTION
216 WHEN OTHERS THEN
217 IF FND_API.To_Boolean(p_set_message) THEN
218 fnd_message.Set_Name(G_APP_NAME, 'EGO_EF_BL_INV_ITEM_ID_ERR');
219 fnd_message.Set_Token('ITEM_ID', x_inventory_item_id);
220 fnd_msg_pub.Add;
221 END IF;
222 RETURN FALSE;
223 END;
224 ELSIF x_item_number IS NOT NULL THEN
225 BEGIN
226 SELECT itm.inventory_item_id, itm.approval_status, itm_num.concatenated_segments
227 INTO x_inventory_item_id, x_approval_status, x_item_number
228 FROM mtl_system_items_b itm, mtl_system_items_b_kfv itm_num
229 WHERE itm_num.organization_id = p_organization_id
230 AND itm_num.concatenated_segments = x_item_number
231 AND itm.inventory_item_id = itm_num.inventory_item_id
232 AND itm.organization_id = itm_num.organization_id;
233 RETURN TRUE;
234 EXCEPTION
235 WHEN OTHERS THEN
236 IF FND_API.To_Boolean(p_set_message) THEN
237 fnd_message.Set_Name(G_APP_NAME, 'EGO_EF_BL_ITEM_NUM_ERR');
238 fnd_message.Set_Token('ITEM_NUMBER', x_item_number);
239 fnd_msg_pub.Add;
240 END IF;
241 RETURN FALSE;
242 END;
243 ELSE
244 -- x_inventory_item_id := NULL;
245 -- x_item_number := NULL;
246 -- x_approval_status := NULL;
247 RETURN FALSE;
248 END IF;
249 ELSE
250 IF FND_API.To_Boolean(p_set_message) THEN
251 fnd_message.Set_Name(G_APP_NAME, 'EGO_ORGANIZATION');
252 l_dummy_char := fnd_message.get();
253 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
254 fnd_message.Set_Token('NAME', l_dummy_char);
255 fnd_message.Set_Token('VALUE', ' ');
256 fnd_msg_pub.Add;
257 END IF;
258 RETURN FALSE;
259 END IF;
260 EXCEPTION
261 WHEN OTHERS THEN
262 -- x_inventory_item_id := NULL;
263 -- x_item_number := NULL;
264 -- x_approval_status := NULL;
265 RETURN FALSE;
266 END validate_item;
267
268
269 ------------------------------------------
270 --
271 -- Check validity of item revision details
272 --
273 ------------------------------------------
274 FUNCTION validate_item_rev (x_revision_id IN OUT NOCOPY NUMBER
275 ,x_revision IN OUT NOCOPY VARCHAR2
276 ,p_inventory_item_id IN NUMBER
277 ,p_organization_id IN NUMBER
278 ,p_set_message IN VARCHAR2
279 ) RETURN BOOLEAN IS
280 l_dummy_char VARCHAR2(32767);
281 BEGIN
282 IF p_organization_id IS NOT NULL AND p_inventory_item_id IS NOT NULL THEN
283 IF x_revision_id IS NOT NULL THEN
284 BEGIN
285 SELECT revision_id, revision
286 INTO x_revision_id, x_revision
287 FROM mtl_item_revisions_b
288 WHERE inventory_item_id = p_inventory_item_id
289 AND organization_id = p_organization_id
290 AND revision_id = x_revision_id;
291 RETURN TRUE;
292 EXCEPTION
293 WHEN OTHERS THEN
294 IF FND_API.To_Boolean(p_set_message) THEN
295 fnd_message.Set_Name(G_APP_NAME, 'EGO_REVISIONID_INVALID');
296 fnd_message.Set_Token('REVISION_ID', x_revision_id);
297 fnd_msg_pub.Add;
298 END IF;
299 RETURN FALSE;
300 END;
301 ELSIF x_revision IS NOT NULL THEN
302 BEGIN
303 SELECT revision_id, revision
304 INTO x_revision_id, x_revision
305 FROM mtl_item_revisions_b
306 WHERE inventory_item_id = p_inventory_item_id
307 AND organization_id = p_organization_id
308 AND revision = x_revision;
309 RETURN TRUE;
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF FND_API.To_Boolean(p_set_message) THEN
313 fnd_message.Set_Name(G_APP_NAME, 'EGO_REVISION_INVALID');
314 fnd_message.Set_Token('REVISION', x_revision);
315 fnd_msg_pub.Add;
316 END IF;
317 RETURN FALSE;
318 END;
319 ELSE
320 -- x_revision_id := NULL;
321 -- x_revision := NULL;
322 RETURN FALSE;
323 END IF;
324 ELSE
325 RETURN FALSE;
326 END IF;
327 EXCEPTION
328 WHEN OTHERS THEN
329 -- x_revision_id := NULL;
330 -- x_revision := NULL;
331 RETURN FALSE;
332 END validate_item_rev;
333
334 ------------------------------------------
335 --
336 -- check validity of party details passed
337 --
338 ------------------------------------------
339 FUNCTION validate_party (p_party_type IN VARCHAR2
340 ,x_party_id IN OUT NOCOPY NUMBER
341 ,x_party_name IN OUT NOCOPY VARCHAR2
342 ) RETURN BOOLEAN IS
343 l_hz_party_type HZ_PARTIES.party_type%TYPE;
344 l_dummy_char VARCHAR2(32767);
345 BEGIN
346 IF p_party_type IN (EGO_ITEM_PUB.G_USER_PARTY_TYPE
347 ,EGO_ITEM_PUB.G_GROUP_PARTY_TYPE
348 ,EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE
349 ,EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE
350 ) THEN
351 IF p_party_type = EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE THEN
352 x_party_id := -1000; -- needed by EGO_SECURITY_PUB
353 x_party_name := p_party_type;
354 RETURN TRUE;
355 ELSE
356 IF p_party_type = EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE THEN
357 l_hz_party_type := G_HZ_COMPANY_PARTY_TYPE;
358 ELSIF p_party_type = EGO_ITEM_PUB.G_GROUP_PARTY_TYPE THEN
359 l_hz_party_type := p_party_type;
360 ELSIF p_party_type = EGO_ITEM_PUB.G_USER_PARTY_TYPE THEN
361 l_hz_party_type := G_HZ_USER_PARTY_TYPE;
362 END IF;
363 IF x_party_id IS NOT NULL THEN
364 -- validate the party_id passed.
365 BEGIN
366 SELECT party_id, party_name
367 INTO x_party_id, x_party_name
368 FROM hz_parties
369 WHERE party_id = x_party_id
370 AND party_type = l_hz_party_type;
371 RETURN TRUE;
372 EXCEPTION
373 WHEN OTHERS THEN
374 -- x_party_id := NULL;
375 -- x_party_name := NULL;
376 RETURN FALSE;
377 END;
378 ELSIF x_party_name IS NOT NULL THEN
379 -- validate the party_name passed.
380 BEGIN
381 SELECT party_id, party_name
382 INTO x_party_id, x_party_name
383 FROM hz_parties
384 WHERE party_name = x_party_name
385 AND party_type = l_hz_party_type;
386 RETURN TRUE;
387 EXCEPTION
388 WHEN OTHERS THEN
389 -- x_party_id := NULL;
390 -- x_party_name := NULL;
391 RETURN FALSE;
392 END;
393 END IF;
394 END IF;
395 ELSE
396 -- x_party_name := NULL;
397 -- x_party_id := NULL;
398 RETURN FALSE;
399 END IF;
400 EXCEPTION
401 WHEN OTHERS THEN
402 -- x_party_id := NULL;
403 -- x_party_name := NULL;
404 RETURN FALSE;
405 END validate_party;
406
407 ------------------------------------------
408 --
409 -- check validity of instance set details
410 --
411 ------------------------------------------
412 FUNCTION validate_instance_set (x_instance_set_id IN OUT NOCOPY NUMBER
413 ,p_set_disp_name IN VARCHAR2
414 ) RETURN BOOLEAN IS
415 l_dummy_char VARCHAR2(32767);
416 BEGIN
417 IF x_instance_set_id IS NOT NULL THEN
418 BEGIN
419 SELECT instance_set_id
420 INTO x_instance_set_id
421 FROM fnd_object_instance_sets
422 WHERE instance_set_id = x_instance_set_id
423 AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
424 RETURN TRUE;
425 EXCEPTION
426 WHEN OTHERS THEN
427 -- x_instance_set_id := NULL;
428 RETURN FALSE;
429 END;
430 ELSIF p_set_disp_name IS NOT NULL THEN
431 BEGIN
432 SELECT instance_set_id
433 INTO x_instance_set_id
434 FROM fnd_object_instance_sets_vl
435 WHERE display_name = p_set_disp_name
436 AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
437 RETURN TRUE;
438 EXCEPTION
439 WHEN OTHERS THEN
440 -- x_instance_set_id := NULL;
441 RETURN FALSE;
442 END;
443 ELSE
444 -- x_instance_set_id := NULL;
445 RETURN FALSE;
446 END IF;
447 EXCEPTION
448 WHEN OTHERS THEN
449 -- x_instance_set_id := NULL;
450 RETURN FALSE;
451 END validate_instance_set;
452
453 -----------------------------
454 --
455 -- check validity of menu
456 --
457 -----------------------------
458 FUNCTION validate_menu (x_menu_id IN OUT NOCOPY NUMBER
459 ,x_menu_name IN OUT NOCOPY VARCHAR2
460 ,p_user_menu_name IN VARCHAR2
461 ,p_menu_type IN VARCHAR2
462 ) RETURN BOOLEAN IS
463 l_dummy_char VARCHAR2(32767);
464 BEGIN
465 IF p_menu_type IS NOT NULL THEN
466 IF x_menu_id IS NOT NULL THEN
467 BEGIN
468 SELECT menu_id, menu_name
469 INTO x_menu_id, x_menu_name
470 FROM fnd_menus
471 WHERE menu_id = x_menu_id
472 AND type = p_menu_type;
473 RETURN TRUE;
474 EXCEPTION
475 WHEN OTHERS THEN
476 -- x_menu_id := NULL;
477 -- x_menu_name := NULL;
478 RETURN FALSE;
479 END;
480 ELSIF p_user_menu_name IS NOT NULL THEN
481 BEGIN
482 SELECT menu_id, menu_name
483 INTO x_menu_id, x_menu_name
484 FROM fnd_menus_vl
485 WHERE user_menu_name = p_user_menu_name
486 AND type = p_menu_type;
487 RETURN TRUE;
488 EXCEPTION
489 WHEN OTHERS THEN
490 -- x_menu_id := NULL;
491 -- x_menu_name := NULL;
492 RETURN FALSE;
493 END;
494 END IF;
495 ELSE
496 -- x_menu_id := NULL;
497 -- x_menu_name := NULL;
498 RETURN FALSE;
499 END IF;
500 EXCEPTION
501 WHEN OTHERS THEN
502 -- x_menu_id := NULL;
503 -- x_menu_name := NULL;
504 RETURN FALSE;
505 END validate_menu;
506
507 ---------------------------------
508 --
509 -- Get Lifecycle / Phase Names
510 --
511 ---------------------------------
512 FUNCTION get_lifecycle_name (p_lc_phase_type IN VARCHAR2
513 ,p_proj_element_id IN NUMBER
514 ) RETURN VARCHAR2 IS
515 l_dummy_char VARCHAR2(32767);
516 BEGIN
517 IF p_lc_phase_type = 'LIFECYCLE' THEN
518 SELECT name
519 INTO l_dummy_char
520 FROM PA_EGO_LIFECYCLES_V
521 WHERE proj_element_id = p_proj_element_id;
522 ELSIF p_lc_phase_type = 'PHASE' THEN
523 SELECT name
524 INTO l_dummy_char
525 FROM PA_EGO_PHASES_V
526 WHERE proj_element_id = p_proj_element_id;
527 END IF;
528 RETURN l_dummy_char;
529 EXCEPTION
530 WHEN OTHERS THEN
531 RETURN NULL;
532 END get_lifecycle_name;
533
534 ---------------------------------
535 --
536 -- check whether the user has functional privilege
537 --
538 ---------------------------------
539 FUNCTION validate_function_security (p_function_name IN VARCHAR2 -- 'EGO_ITEM_ADMINISTRATION'
540 ,p_set_message IN VARCHAR2
541 ) RETURN BOOLEAN IS
542 BEGIN
543 RETURN TRUE;
544 IF fnd_function.test(p_function_name) THEN
545 RETURN TRUE;
546 ELSE
547 IF FND_API.To_Boolean(p_set_message) THEN
548 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_FUNC_PRIVILEGE_FOR_USER');
549 fnd_msg_pub.Add;
550 END IF;
551 RETURN FALSE;
552 END IF;
553 EXCEPTION
554 WHEN OTHERS THEN
555 RETURN FALSE;
556 END validate_function_security;
557
558 -- -----------------------------------------------------------------------------------
559 -- API Name: Chg_Order_required
560 --
561 -- Description:
562 -- Returns Y if a change order or update is not allowed on a particular Attribute
563 -- based on the given inventoryitemid,orgId,ObjectId,attributegroupid and revision level
564 -- ------------------------------------------------------------------------------------
565 FUNCTION Chg_Order_Required(
566 p_inventory_item_id IN NUMBER
567 ,p_organization_id IN NUMBER
568 ,p_object_id IN NUMBER
569 ,p_attr_grp_id IN NUMBER
570 ,p_data_level_1 IN VARCHAR2
571 )
572 RETURN VARCHAR2
573 IS
574
575 p_chg_ord_req VARCHAR2(1);
576 l_policy_check_sql VARCHAR2(32767);
577
578 BEGIN
579 l_policy_check_sql:= 'SELECT ''Y'''||
580 ' FROM MTL_SYSTEM_ITEMS_B MSI,'||
581 ' MTL_ITEM_REVISIONS_B MIR,'||
582 ' ENG_CHANGE_POLICIES_V ECP'||
583 ' WHERE '||
584 ' MSI.INVENTORY_ITEM_ID = :1'||
585 ' AND MSI.ORGANIZATION_ID = :2'||
586 ' AND MSI.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID'||
587 ' AND MSI.ORGANIZATION_ID = MIR.ORGANIZATION_ID '||
588 ' AND MSI.LIFECYCLE_ID IS NOT NULL'||
589 ' AND (MSI.APPROVAL_STATUS IS NULL OR MSI.APPROVAL_STATUS =''A'') '||
590 ' AND ECP.POLICY_OBJECT_PK1_VALUE = '||
591 ' (SELECT TO_CHAR(ic.item_catalog_group_id) '||
592 ' FROM mtl_item_catalog_groups_b ic'||
593 ' WHERE EXISTS '||
594 ' ( SELECT olc.object_classification_code CatalogId '||
595 ' FROM ego_obj_type_lifecycles olc '||
596 ' WHERE olc.object_id = :3 '||
597 ' AND olc.lifecycle_id = MSI.lifecycle_id '||
598 ' AND olc.object_classification_code = ic.item_catalog_group_id '||
599 ' ) '||
600 ' AND ROWNUM = 1 '||
601 ' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id '||
602 ' START WITH item_catalog_group_id = MSI.item_catalog_group_id '||
603 ' )'||
604 ' AND ECP.ATTRIBUTE_OBJECT_NAME = ''EGO_CATALOG_GROUP'' '||
605 ' AND ECP.ATTRIBUTE_CODE = ''ATTRIBUTE_GROUP'' '||
606 ' AND ECP.POLICY_OBJECT_NAME =''CATALOG_LIFECYCLE_PHASE'' '||
607 ' AND ECP.POLICY_CHAR_VALUE IS NOT NULL '||
608 ' AND ECP.POLICY_CHAR_VALUE IN (''CHANGE_ORDER_REQUIRED'' ,''NOT_ALLOWED'')'||
609 ' AND ECP.ATTRIBUTE_NUMBER_VALUE =:4 '||
610 ' AND ( '||
611 ' ( (:5 IS NOT NULL '||
612 ' AND MIR.REVISION_ID = :6)'||
613 ' AND ECP.POLICY_OBJECT_PK2_VALUE = NVL(MIR.LIFECYCLE_ID, MSI.LIFECYCLE_ID) '||
614 ' AND ECP.POLICY_OBJECT_PK3_VALUE = NVL(MIR.CURRENT_PHASE_ID, MSI.CURRENT_PHASE_ID) '||
615 ' ) '||
616 ' OR '||
617 ' ( ECP.POLICY_OBJECT_PK2_VALUE = MSI.LIFECYCLE_ID '||
618 ' AND ECP.POLICY_OBJECT_PK3_VALUE = MSI.CURRENT_PHASE_ID '||
619 ' ))';
620
621 BEGIN
622
623 EXECUTE IMMEDIATE l_policy_check_sql INTO p_chg_ord_req USING p_inventory_item_id,
624 p_organization_id,
625 p_object_id,
626 p_attr_grp_id,
627 p_data_level_1,
628 p_data_level_1;
629
630 RETURN p_chg_ord_req;
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 RETURN 'N';
634 WHEN OTHERS THEN
635 RETURN NULL;
636 END;
637 END Chg_Order_Required;
638
639 -- -----------------------------------------------------------------------------------
640 -- API Name: Remove_Rows_After_Policy_Check
641 --
642 -- Description:
643 -- Updates the error table and deletes the corresponding rows from the
644 -- EGO_USER_ATTR_ROW_TABLE if a change order is required or update is not allowed
645 -- for a given attribute
646 -- ------------------------------------------------------------------------------------
647 FUNCTION Remove_Rows_After_Policy_Check (
648 p_inventory_item_id IN NUMBER
649 ,p_organization_id IN NUMBER
650 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
651 ,p_entity_id IN NUMBER DEFAULT NULL
652 ,p_entity_index IN NUMBER DEFAULT NULL
653 ,p_entity_code IN VARCHAR2 DEFAULT NULL
654 ,x_return_status OUT NOCOPY VARCHAR2
655 ) RETURN EGO_USER_ATTR_ROW_TABLE IS
656
657 l_object_id NUMBER;
658 l_is_required VARCHAR2(1);
659 l_token_table ERROR_HANDLER.Token_Tbl_Type;
660 l_attr_grp_id NUMBER;
661 l_rev_id VARCHAR2(150);
662 l_delete_sql VARCHAR2(200);
663 l_attr_data_table_index NUMBER;
664
665 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
666 l_current_data_element EGO_USER_ATTR_ROW_OBJ;
667
668 l_current_category_name MTL_ITEM_CATALOG_GROUPS_KFV.concatenated_segments%TYPE;
669 l_current_life_cycle PA_EGO_LIFECYCLES_V.NAME%TYPE;
670 l_current_phase_name PA_EGO_PHASES_V.NAME%TYPE;
671 l_policy_cat_id NUMBER;
672 l_catalog_category_names_table LOCAL_MEDIUM_VARCHAR_TABLE;
673 l_item_number MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
674 l_row_identifier NUMBER;
675 i NUMBER :=0;
676 BEGIN
677 l_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
678 l_attr_data_table_index := p_attributes_row_table.FIRST;
679 SELECT OBJECT_ID into l_object_id FROM fnd_objects WHERE obj_name ='EGO_ITEM';
680
681 LOOP
682 l_current_data_element := p_attributes_row_table(l_attr_data_table_index);
683 l_rev_id := l_current_data_element.DATA_LEVEL_1;
684 l_attr_grp_id := l_current_data_element.ATTR_GROUP_ID;
685 l_row_identifier :=l_current_data_element.ROW_IDENTIFIER;
686
687 l_is_required:=Chg_Order_Required(
688 p_inventory_item_id =>p_inventory_item_id
689 ,p_organization_id =>p_organization_id
690 ,p_object_id =>l_object_id
691 ,p_attr_grp_id =>l_attr_grp_id
692 ,p_data_level_1 =>l_rev_id
693 );
694
695 IF (l_is_required='Y') THEN
696
697 l_token_table(1).TOKEN_NAME := 'ATTR_GROUP_NAME';
698 l_token_table(1).TOKEN_VALUE := l_current_data_element.ATTR_GROUP_NAME;
699
700 SELECT segment1 INTO l_item_number FROM MTL_SYSTEM_ITEMS_B WHERE inventory_item_id=p_inventory_item_id;
701
702
703 l_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
704 l_token_table(2).TOKEN_VALUE := l_item_number;
705
706 SELECT PEP.NAME
707 INTO l_current_life_cycle
708 FROM MTL_SYSTEM_ITEMS_B MSI
709 ,PA_EGO_LIFECYCLES_V PEP
710 WHERE MSI.INVENTORY_ITEM_ID =p_inventory_item_id
711 AND MSI.ORGANIZATION_ID = p_organization_id
712 AND MSI.LIFECYCLE_ID = PEP.PROJ_ELEMENT_ID;
713
714 l_token_table(3).TOKEN_NAME := 'LIFE_CYCLE';
715 l_token_table(3).TOKEN_VALUE := l_current_life_cycle;
716
717 SELECT PEP.NAME
718 INTO l_current_phase_name
719 FROM MTL_SYSTEM_ITEMS_B MSI
720 ,PA_EGO_PHASES_V PEP
721 WHERE MSI.INVENTORY_ITEM_ID = p_inventory_item_id
722 AND MSI.ORGANIZATION_ID = p_organization_id
723 AND MSI.CURRENT_PHASE_ID = PEP.PROJ_ELEMENT_ID;
724
725 l_token_table(4).TOKEN_NAME := 'PHASE';
726 l_token_table(4).TOKEN_VALUE := l_current_phase_name;
727
728 SELECT item_catalog_group_id
729 INTO l_policy_cat_id
730 FROM (SELECT item_catalog_group_id
731 FROM mtl_item_catalog_groups_b ic
732 WHERE EXISTS
733 ( SELECT olc.object_classification_code CatalogId
734 FROM ego_obj_type_lifecycles olc, mtl_system_items_b MSI
735 WHERE olc.object_id = l_object_id
736 AND olc.lifecycle_id = MSI.lifecycle_id
737 AND MSI.inventory_item_id = p_inventory_item_id
738 AND MSI.organization_id = p_organization_id
739 AND olc.object_classification_code = ic.item_catalog_group_id
740 )
741 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
742 START WITH item_catalog_group_id
743 =(SELECT item_catalog_group_id
744 FROM MTL_SYSTEM_ITEMS_B
745 WHERE inventory_item_id=p_inventory_item_id
746 )
747 ) CAT_HIER
748 WHERE ROWNUM = 1;
749
750 IF (l_catalog_category_names_table.EXISTS(l_policy_cat_id)) THEN
751 l_current_category_name := l_catalog_category_names_table(l_policy_cat_id);
752 ELSE
753 SELECT concatenated_segments
754 INTO l_current_category_name
755 FROM MTL_ITEM_CATALOG_GROUPS_KFV
756 WHERE ITEM_CATALOG_GROUP_ID = l_policy_cat_id;
757 l_catalog_category_names_table(l_policy_cat_id) := l_current_category_name;
758 END IF;
759
760 l_token_table(5).TOKEN_NAME := 'CATALOG_CATEGORY_NAME';
761 l_token_table(5).TOKEN_VALUE := l_current_category_name;
762
763 ERROR_HANDLER.Add_Error_Message(
764 p_message_name => 'EGO_EF_BL_ITM_NOT_ALLOW_ERR'
765 ,p_application_id => 'EGO'
766 ,p_token_tbl => l_token_table
767 ,p_message_type => FND_API.G_RET_STS_ERROR
768 ,p_row_identifier => l_row_identifier
769 ,p_entity_id => p_entity_id
770 ,p_entity_index => p_entity_index
771 ,p_entity_code => p_entity_code
772 );
773 x_return_status:=FND_API.G_RET_STS_ERROR;
774 l_token_table.DELETE();
775 --Delete the row from the ROW_TABLE as it is an error case.
776 ELSIF (l_is_required='N') THEN
777 i:=i+1;
778 l_attributes_row_table.EXTEND();
779 l_attributes_row_table(i):=l_current_data_element;
780 END IF;--IF(l_is_required='Y')
781 l_attr_data_table_index := p_attributes_row_table.NEXT(l_attr_data_table_index);
782 IF (l_attr_data_table_index IS NULL) THEN
783 EXIT ;
784 END IF;--IF(l_attr_data_table_index IS NULL)
785 END LOOP;
786 RETURN l_attributes_row_table;
787 EXCEPTION
788 WHEN OTHERS THEN
789 RETURN NULL;
790 END Remove_Rows_After_Policy_Check;
791
792 -- =============================================================================
793 -- Procedures
794 -- =============================================================================
795
796 -- -----------------------------------------------------------------------------
797 -- API Name: Process_Items
798 -- -----------------------------------------------------------------------------
799
800 PROCEDURE Process_Items
801 (
802 p_commit IN VARCHAR2 DEFAULT FND_API.g_FALSE
803 , x_return_status OUT NOCOPY VARCHAR2
804 , x_msg_count OUT NOCOPY NUMBER
805 )
806 IS
807 CURSOR c_get_org_code(cp_org_id NUMBER) IS
808 SELECT organization_code
809 ,master_organization_id
810 FROM mtl_parameters
811 WHERE organization_id = cp_org_id;
812
813 l_api_name CONSTANT VARCHAR2(30) := 'Process_Items';
814 l_return_status VARCHAR2(1) := G_MISS_CHAR;
815 l_msg_count NUMBER := 0;
816
817 l_error_code NUMBER;
818 --R12 C
819 l_return_err VARCHAR2(1000);
820 l_batch_id NUMBER;
821 G_Item_Rec EGO_Item_PUB.Item_Rec_Type;
822 -- added for bug 7431714
823 l_icc_change_flag BOOLEAN := FALSE;
824 l_init_msg_list VARCHAR2(1) := NULL;
825 l_curr_icc_id NUMBER;
826 -- end adding bug 7431714
827 ----------------------------------------------------------------------------
828 -- Business Event For Implicit Revision/Category Assignments
829 ----------------------------------------------------------------------------
830 CURSOR DEFAULT_CAT_ASSIGN_CREATE ( CP_ITEM_ID NUMBER
831 ,CP_ORG_ID NUMBER ) IS
832 SELECT S.CATEGORY_SET_ID,
833 S.CATEGORY_ID
834 FROM MTL_ITEM_CATEGORIES S
835 WHERE S.INVENTORY_ITEM_ID = CP_ITEM_ID
836 AND S.ORGANIZATION_ID = CP_ORG_ID
837 AND EXISTS
838 (SELECT 'X'
839 FROM MTL_DEFAULT_CATEGORY_SETS D
840 WHERE D.CATEGORY_SET_ID = S.CATEGORY_SET_ID
841 AND (D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
842 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
843 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
844 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.MRP_PLANNING_CODE, 6, 0, 3 )
845 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
846 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
847 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.ENG_ITEM_FLAG, 'Y', 6, 0 )
848 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
849 OR D.FUNCTIONAL_AREA_ID = DECODE( NVL(G_Item_Rec.EAM_ITEM_TYPE, 0), 0, 0, 9 )
850 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 10, 'WARRANTY' , 10, 'SUBSCRIPTION' , 10, 'USAGE' , 10, 0 )
851 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 4, 'WARRANTY' , 4, 0 )
852 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
853 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )));
854 -------------------------------------------------------------------------------
855 CURSOR DEFAULT_CAT_ASSIGN_UPDATE ( CP_ITEM_ID NUMBER
856 ,CP_ORG_ID NUMBER ) IS
857 SELECT S.CATEGORY_SET_ID,
858 S.CATEGORY_ID
859 FROM MTL_ITEM_CATEGORIES S,
860 MTL_CATEGORY_SETS_B D
861 WHERE S.INVENTORY_ITEM_ID = CP_ITEM_ID
862 AND S.CATEGORY_SET_ID = D.CATEGORY_SET_ID
863 AND S.ORGANIZATION_ID = CP_ORG_ID
864 AND (D.CONTROL_LEVEL = 1
865 OR EXISTS
866 (SELECT 'X'
867 FROM MTL_DEFAULT_CATEGORY_SETS D
868 WHERE D.CATEGORY_SET_ID = S.CATEGORY_SET_ID
869 AND (D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
870 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
871 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
872 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.MRP_PLANNING_CODE, 6, 0, 3 )
873 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
874 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
875 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.ENG_ITEM_FLAG, 'Y', 6, 0 )
876 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
877 OR D.FUNCTIONAL_AREA_ID = DECODE( NVL(G_Item_Rec.EAM_ITEM_TYPE, 0), 0, 0, 9 )
878 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 10, 'WARRANTY' , 10, 'SUBSCRIPTION' , 10, 'USAGE' , 10, 0 )
879 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 4, 'WARRANTY' , 4, 0 )
880 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
881 OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 11, 0 ))
882 )) ;
883 -----------------------------------------------------------------------------
884 CURSOR REV_RECORDS_CREATE ( CP_ITEM_ID NUMBER
885 ,CP_ORG_ID NUMBER ) IS
886 SELECT REVISION_ID
887 FROM MTL_ITEM_REVISIONS_B
888 WHERE INVENTORY_ITEM_ID = CP_ITEM_ID
889 AND ORGANIZATION_ID = CP_ORG_ID ;
890 -----------------------------------------------------------------------------
891 l_Item_rec_in INV_ITEM_GRP.Item_Rec_Type;
892 l_revision_rec INV_ITEM_GRP.Item_Revision_Rec_Type;
893 l_rev_index_failure BOOLEAN := FALSE;
894
895 l_Item_rec_out INV_ITEM_GRP.Item_Rec_Type;
896
897 l_Template_Id NUMBER;
898 l_Template_Name VARCHAR2(30);
899 l_Error_tbl INV_ITEM_GRP.Error_Tbl_Type;
900 --------------------------------------------------------------------------
901 -- Business Event enhancement
902 --------------------------------------------------------------------------
903 l_event_return_status VARCHAR2(1) ;
904 l_msg_data VARCHAR2(2000);
905 l_org_code_rec c_get_org_code%ROWTYPE;
906 l_item_desc mtl_system_items_kfv.DESCRIPTION%TYPE;
907 l_item_number mtl_system_items_kfv.CONCATENATED_SEGMENTS%TYPE;
908 ----------------------------------------------------------------
909 l_revision_id_out MTL_ITEM_REVISIONS_B.REVISION_ID%TYPE;
910 l_cat_tab_index NUMBER := G_MISS_NUM ;
911 l_cat_match VARCHAR2(1) DEFAULT FND_API.G_FALSE ;
912 --Bug: 4881908
913 l_process_control VARCHAR2(2000) := INV_EGO_REVISION_VALIDATE.Get_Process_Control;
914 ---------------------------------------------------------------------------
915 TYPE CATEGORY_ASSIGN_REC IS RECORD (
916 CATEGORY_SET_ID NUMBER := G_MISS_NUM,
917 CATEGORY_ID NUMBER := G_MISS_NUM,
918 INVENTORY_ITEM_ID NUMBER := G_MISS_NUM,
919 ORGANIZATION_ID NUMBER := G_MISS_NUM );
920 TYPE CATEGORY_ASSIGN_TAB IS TABLE OF CATEGORY_ASSIGN_REC INDEX BY BINARY_INTEGER;
921
922 l_cat_assign_rec_table_bef CATEGORY_ASSIGN_TAB;
923 l_cat_assign_rec_table_aft CATEGORY_ASSIGN_TAB;
924 BEGIN
925
926 x_return_status := G_RET_STS_SUCCESS;
927
928 -----------------------------------------------------------------------------
929 -- Loop through item records in the global table.
930 -----------------------------------------------------------------------------
931
932 G_Item_indx := G_Item_Tbl.FIRST;
933
934 WHILE G_Item_indx <= G_Item_Tbl.LAST LOOP
935
936 -- Clear the Item GRP API message table before processing an item
937 l_Error_tbl.DELETE;
938
939 G_Item_Rec := G_Item_Tbl(G_Item_indx);
940
941 -- Copy item from
942 l_Template_Id := G_Item_Rec.Template_Id;
943 l_Template_Name := G_Item_Rec.Template_Name;
944
945 -- Item identifier
946 l_Item_rec_in.INVENTORY_ITEM_ID := G_Item_Rec.Inventory_Item_Id;
947 l_Item_rec_in.ITEM_NUMBER := G_Item_Rec.Item_Number;
948 l_Item_rec_in.SEGMENT1 := G_Item_Rec.Segment1;
949 l_Item_rec_in.SEGMENT2 := G_Item_Rec.Segment2;
950 l_Item_rec_in.SEGMENT3 := G_Item_Rec.Segment3;
951 l_Item_rec_in.SEGMENT4 := G_Item_Rec.Segment4;
952 l_Item_rec_in.SEGMENT5 := G_Item_Rec.Segment5;
953 l_Item_rec_in.SEGMENT6 := G_Item_Rec.Segment6;
954 l_Item_rec_in.SEGMENT7 := G_Item_Rec.Segment7;
955 l_Item_rec_in.SEGMENT8 := G_Item_Rec.Segment8;
956 l_Item_rec_in.SEGMENT9 := G_Item_Rec.Segment9;
957 l_Item_rec_in.SEGMENT10 := G_Item_Rec.Segment10;
958 l_Item_rec_in.SEGMENT11 := G_Item_Rec.Segment11;
959 l_Item_rec_in.SEGMENT12 := G_Item_Rec.Segment12;
960 l_Item_rec_in.SEGMENT13 := G_Item_Rec.Segment13;
961 l_Item_rec_in.SEGMENT14 := G_Item_Rec.Segment14;
962 l_Item_rec_in.SEGMENT15 := G_Item_Rec.Segment15;
963 l_Item_rec_in.SEGMENT16 := G_Item_Rec.Segment16;
964 l_Item_rec_in.SEGMENT17 := G_Item_Rec.Segment17;
965 l_Item_rec_in.SEGMENT18 := G_Item_Rec.Segment18;
966 l_Item_rec_in.SEGMENT19 := G_Item_Rec.Segment19;
967 l_Item_rec_in.SEGMENT20 := G_Item_Rec.Segment20;
968 l_Item_rec_in.SUMMARY_FLAG := G_Item_Rec.Summary_Flag;
969 l_Item_rec_in.ENABLED_FLAG := G_Item_Rec.Enabled_Flag;
970 l_Item_rec_in.START_DATE_ACTIVE := G_Item_Rec.Start_Date_Active;
971 l_Item_rec_in.END_DATE_ACTIVE := G_Item_Rec.End_Date_Active;
972
973 -- Organization
974 l_Item_rec_in.ORGANIZATION_ID := G_Item_Rec.Organization_Id;
975 l_Item_rec_in.ORGANIZATION_CODE := G_Item_Rec.Organization_Code;
976
977 -- Item catalog group (user item type)
978 l_Item_rec_in.ITEM_CATALOG_GROUP_ID := G_Item_Rec.Item_Catalog_Group_Id;
979 l_Item_rec_in.CATALOG_STATUS_FLAG := G_Item_Rec.Catalog_Status_Flag;
980
981 -- Lifecycle
982 l_Item_rec_in.LIFECYCLE_ID := G_Item_Rec.Lifecycle_Id;
983 l_Item_rec_in.CURRENT_PHASE_ID := G_Item_Rec.Current_Phase_Id;
984
985 -- Main attributes
986 l_Item_rec_in.DESCRIPTION := G_Item_Rec.Description;
987 l_Item_rec_in.LONG_DESCRIPTION := G_Item_Rec.Long_Description;
988 l_Item_rec_in.PRIMARY_UOM_CODE := G_Item_Rec.Primary_Uom_Code;
989
990 --PRIMARY_UNIT_OF_MEASURE
991 l_Item_rec_in.ALLOWED_UNITS_LOOKUP_CODE := G_Item_Rec.ALLOWED_UNITS_LOOKUP_CODE;
992 l_Item_rec_in.INVENTORY_ITEM_STATUS_CODE := G_Item_Rec.Inventory_Item_Status_Code;
993
994 l_Item_rec_in.DUAL_UOM_CONTROL := G_Item_Rec.DUAL_UOM_CONTROL;
995 l_Item_rec_in.SECONDARY_UOM_CODE := G_Item_Rec.SECONDARY_UOM_CODE;
996 l_Item_rec_in.DUAL_UOM_DEVIATION_HIGH := G_Item_Rec.DUAL_UOM_DEVIATION_HIGH;
997 l_Item_rec_in.DUAL_UOM_DEVIATION_LOW := G_Item_Rec.DUAL_UOM_DEVIATION_LOW;
998 l_Item_rec_in.ITEM_TYPE := G_Item_Rec.ITEM_TYPE;
999
1000 -- Inventory
1001 l_Item_rec_in.INVENTORY_ITEM_FLAG := G_Item_Rec.INVENTORY_ITEM_FLAG;
1002 l_Item_rec_in.STOCK_ENABLED_FLAG := G_Item_Rec.STOCK_ENABLED_FLAG;
1003 l_Item_rec_in.MTL_TRANSACTIONS_ENABLED_FLAG := G_Item_Rec.MTL_TRANSACTIONS_ENABLED_FLAG;
1004 l_Item_rec_in.REVISION_QTY_CONTROL_CODE := G_Item_Rec.REVISION_QTY_CONTROL_CODE;
1005 l_Item_rec_in.LOT_CONTROL_CODE := G_Item_Rec.LOT_CONTROL_CODE;
1006 l_Item_rec_in.AUTO_LOT_ALPHA_PREFIX := G_Item_Rec.AUTO_LOT_ALPHA_PREFIX;
1007 l_Item_rec_in.START_AUTO_LOT_NUMBER := G_Item_Rec.START_AUTO_LOT_NUMBER;
1008 l_Item_rec_in.SERIAL_NUMBER_CONTROL_CODE := G_Item_Rec.SERIAL_NUMBER_CONTROL_CODE;
1009 l_Item_rec_in.AUTO_SERIAL_ALPHA_PREFIX := G_Item_Rec.AUTO_SERIAL_ALPHA_PREFIX;
1010 l_Item_rec_in.START_AUTO_SERIAL_NUMBER := G_Item_Rec.START_AUTO_SERIAL_NUMBER;
1011 l_Item_rec_in.SHELF_LIFE_CODE := G_Item_Rec.SHELF_LIFE_CODE;
1012 l_Item_rec_in.SHELF_LIFE_DAYS := G_Item_Rec.SHELF_LIFE_DAYS;
1013 l_Item_rec_in.RESTRICT_SUBINVENTORIES_CODE := G_Item_Rec.RESTRICT_SUBINVENTORIES_CODE;
1014 l_Item_rec_in.LOCATION_CONTROL_CODE := G_Item_Rec.LOCATION_CONTROL_CODE;
1015 l_Item_rec_in.RESTRICT_LOCATORS_CODE := G_Item_Rec.RESTRICT_LOCATORS_CODE;
1016 l_Item_rec_in.RESERVABLE_TYPE := G_Item_Rec.RESERVABLE_TYPE;
1017 l_Item_rec_in.CYCLE_COUNT_ENABLED_FLAG := G_Item_Rec.CYCLE_COUNT_ENABLED_FLAG;
1018 l_Item_rec_in.NEGATIVE_MEASUREMENT_ERROR := G_Item_Rec.NEGATIVE_MEASUREMENT_ERROR;
1019 l_Item_rec_in.POSITIVE_MEASUREMENT_ERROR := G_Item_Rec.POSITIVE_MEASUREMENT_ERROR;
1020 l_Item_rec_in.CHECK_SHORTAGES_FLAG := G_Item_Rec.CHECK_SHORTAGES_FLAG;
1021 l_Item_rec_in.LOT_STATUS_ENABLED := G_Item_Rec.LOT_STATUS_ENABLED;
1022 l_Item_rec_in.DEFAULT_LOT_STATUS_ID := G_Item_Rec.DEFAULT_LOT_STATUS_ID;
1023 l_Item_rec_in.SERIAL_STATUS_ENABLED := G_Item_Rec.SERIAL_STATUS_ENABLED;
1024 l_Item_rec_in.DEFAULT_SERIAL_STATUS_ID := G_Item_Rec.DEFAULT_SERIAL_STATUS_ID;
1025 l_Item_rec_in.LOT_SPLIT_ENABLED := G_Item_Rec.LOT_SPLIT_ENABLED;
1026 l_Item_rec_in.LOT_MERGE_ENABLED := G_Item_Rec.LOT_MERGE_ENABLED;
1027 l_Item_rec_in.LOT_TRANSLATE_ENABLED := G_Item_Rec.LOT_TRANSLATE_ENABLED;
1028 l_Item_rec_in.LOT_SUBSTITUTION_ENABLED := G_Item_Rec.LOT_SUBSTITUTION_ENABLED;
1029 l_Item_rec_in.BULK_PICKED_FLAG := G_Item_Rec.BULK_PICKED_FLAG;
1030
1031 -- Bills of Material
1032 l_Item_rec_in.BOM_ITEM_TYPE := G_Item_Rec.BOM_ITEM_TYPE;
1033 l_Item_rec_in.BOM_ENABLED_FLAG := G_Item_Rec.BOM_ENABLED_FLAG;
1034 l_Item_rec_in.BASE_ITEM_ID := G_Item_Rec.BASE_ITEM_ID;
1035 l_Item_rec_in.ENG_ITEM_FLAG := G_Item_Rec.ENG_ITEM_FLAG;
1036 l_Item_rec_in.ENGINEERING_ITEM_ID := G_Item_Rec.ENGINEERING_ITEM_ID;
1037 l_Item_rec_in.ENGINEERING_ECN_CODE := G_Item_Rec.ENGINEERING_ECN_CODE;
1038 l_Item_rec_in.ENGINEERING_DATE := G_Item_Rec.ENGINEERING_DATE;
1039 l_Item_rec_in.EFFECTIVITY_CONTROL := G_Item_Rec.EFFECTIVITY_CONTROL;
1040 l_Item_rec_in.CONFIG_MODEL_TYPE := G_Item_Rec.CONFIG_MODEL_TYPE;
1041 l_Item_rec_in.PRODUCT_FAMILY_ITEM_ID := G_Item_Rec.Product_Family_Item_Id;
1042 l_Item_rec_in.AUTO_CREATED_CONFIG_FLAG := G_Item_Rec.auto_created_config_flag;--3911562
1043 -- Costing
1044 l_Item_rec_in.COSTING_ENABLED_FLAG := G_Item_Rec.COSTING_ENABLED_FLAG;
1045 l_Item_rec_in.INVENTORY_ASSET_FLAG := G_Item_Rec.INVENTORY_ASSET_FLAG;
1046 l_Item_rec_in.COST_OF_SALES_ACCOUNT := G_Item_Rec.COST_OF_SALES_ACCOUNT;
1047 l_Item_rec_in.DEFAULT_INCLUDE_IN_ROLLUP_FLAG := G_Item_Rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG;
1048 l_Item_rec_in.STD_LOT_SIZE := G_Item_Rec.STD_LOT_SIZE;
1049
1050 -- Enterprise Asset Management
1051 l_Item_rec_in.EAM_ITEM_TYPE := G_Item_Rec.EAM_ITEM_TYPE;
1052 l_Item_rec_in.EAM_ACTIVITY_TYPE_CODE := G_Item_Rec.EAM_ACTIVITY_TYPE_CODE;
1053 l_Item_rec_in.EAM_ACTIVITY_CAUSE_CODE := G_Item_Rec.EAM_ACTIVITY_CAUSE_CODE;
1054 l_Item_rec_in.EAM_ACTIVITY_SOURCE_CODE := G_Item_Rec.EAM_ACTIVITY_SOURCE_CODE;
1055 l_Item_rec_in.EAM_ACT_SHUTDOWN_STATUS := G_Item_Rec.EAM_ACT_SHUTDOWN_STATUS;
1056 l_Item_rec_in.EAM_ACT_NOTIFICATION_FLAG := G_Item_Rec.EAM_ACT_NOTIFICATION_FLAG;
1057
1058 -- Purchasing
1059 l_Item_rec_in.PURCHASING_ITEM_FLAG := G_Item_Rec.PURCHASING_ITEM_FLAG;
1060 l_Item_rec_in.PURCHASING_ENABLED_FLAG := G_Item_Rec.PURCHASING_ENABLED_FLAG;
1061 l_Item_rec_in.BUYER_ID := G_Item_Rec.BUYER_ID;
1062 l_Item_rec_in.MUST_USE_APPROVED_VENDOR_FLAG := G_Item_Rec.MUST_USE_APPROVED_VENDOR_FLAG;
1063 l_Item_rec_in.PURCHASING_TAX_CODE := G_Item_Rec.PURCHASING_TAX_CODE;
1064 l_Item_rec_in.TAXABLE_FLAG := G_Item_Rec.TAXABLE_FLAG;
1065 l_Item_rec_in.RECEIVE_CLOSE_TOLERANCE := G_Item_Rec.RECEIVE_CLOSE_TOLERANCE;
1066 l_Item_rec_in.ALLOW_ITEM_DESC_UPDATE_FLAG:= G_Item_Rec.ALLOW_ITEM_DESC_UPDATE_FLAG;
1067 l_Item_rec_in.INSPECTION_REQUIRED_FLAG := G_Item_Rec.INSPECTION_REQUIRED_FLAG;
1068 l_Item_rec_in.RECEIPT_REQUIRED_FLAG := G_Item_Rec.RECEIPT_REQUIRED_FLAG;
1069 l_Item_rec_in.MARKET_PRICE := G_Item_Rec.MARKET_PRICE;
1070 l_Item_rec_in.UN_NUMBER_ID := G_Item_Rec.UN_NUMBER_ID;
1071 l_Item_rec_in.HAZARD_CLASS_ID := G_Item_Rec.HAZARD_CLASS_ID;
1072 l_Item_rec_in.RFQ_REQUIRED_FLAG := G_Item_Rec.RFQ_REQUIRED_FLAG;
1073 l_Item_rec_in.LIST_PRICE_PER_UNIT := G_Item_Rec.LIST_PRICE_PER_UNIT;
1074 l_Item_rec_in.PRICE_TOLERANCE_PERCENT := G_Item_Rec.PRICE_TOLERANCE_PERCENT;
1075 l_Item_rec_in.ASSET_CATEGORY_ID := G_Item_Rec.ASSET_CATEGORY_ID;
1076 l_Item_rec_in.ROUNDING_FACTOR := G_Item_Rec.ROUNDING_FACTOR;
1077 l_Item_rec_in.UNIT_OF_ISSUE := G_Item_Rec.UNIT_OF_ISSUE;
1078 l_Item_rec_in.OUTSIDE_OPERATION_FLAG := G_Item_Rec.OUTSIDE_OPERATION_FLAG;
1079 l_Item_rec_in.OUTSIDE_OPERATION_UOM_TYPE := G_Item_Rec.OUTSIDE_OPERATION_UOM_TYPE;
1080 l_Item_rec_in.INVOICE_CLOSE_TOLERANCE := G_Item_Rec.INVOICE_CLOSE_TOLERANCE;
1081 l_Item_rec_in.ENCUMBRANCE_ACCOUNT := G_Item_Rec.ENCUMBRANCE_ACCOUNT;
1082 l_Item_rec_in.EXPENSE_ACCOUNT := G_Item_Rec.EXPENSE_ACCOUNT;
1083 l_Item_rec_in.QTY_RCV_EXCEPTION_CODE := G_Item_Rec.QTY_RCV_EXCEPTION_CODE;
1084 l_Item_rec_in.RECEIVING_ROUTING_ID := G_Item_Rec.RECEIVING_ROUTING_ID;
1085 l_Item_rec_in.QTY_RCV_TOLERANCE := G_Item_Rec.QTY_RCV_TOLERANCE;
1086 l_Item_rec_in.ENFORCE_SHIP_TO_LOCATION_CODE := G_Item_Rec.ENFORCE_SHIP_TO_LOCATION_CODE;
1087 l_Item_rec_in.ALLOW_SUBSTITUTE_RECEIPTS_FLAG := G_Item_Rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG;
1088 l_Item_rec_in.ALLOW_UNORDERED_RECEIPTS_FLAG := G_Item_Rec.ALLOW_UNORDERED_RECEIPTS_FLAG;
1089 l_Item_rec_in.ALLOW_EXPRESS_DELIVERY_FLAG := G_Item_Rec.ALLOW_EXPRESS_DELIVERY_FLAG;
1090 l_Item_rec_in.DAYS_EARLY_RECEIPT_ALLOWED := G_Item_Rec.DAYS_EARLY_RECEIPT_ALLOWED;
1091 l_Item_rec_in.DAYS_LATE_RECEIPT_ALLOWED := G_Item_Rec.DAYS_LATE_RECEIPT_ALLOWED;
1092 l_Item_rec_in.RECEIPT_DAYS_EXCEPTION_CODE:= G_Item_Rec.RECEIPT_DAYS_EXCEPTION_CODE;
1093
1094 -- Physical
1095 l_Item_rec_in.WEIGHT_UOM_CODE := G_Item_Rec.WEIGHT_UOM_CODE;
1096 l_Item_rec_in.UNIT_WEIGHT := G_Item_Rec.UNIT_WEIGHT;
1097 l_Item_rec_in.VOLUME_UOM_CODE := G_Item_Rec.VOLUME_UOM_CODE;
1098 l_Item_rec_in.UNIT_VOLUME := G_Item_Rec.UNIT_VOLUME;
1099 l_Item_rec_in.CONTAINER_ITEM_FLAG := G_Item_Rec.CONTAINER_ITEM_FLAG;
1100 l_Item_rec_in.VEHICLE_ITEM_FLAG := G_Item_Rec.VEHICLE_ITEM_FLAG;
1101 l_Item_rec_in.MAXIMUM_LOAD_WEIGHT := G_Item_Rec.MAXIMUM_LOAD_WEIGHT;
1102 l_Item_rec_in.MINIMUM_FILL_PERCENT := G_Item_Rec.MINIMUM_FILL_PERCENT;
1103 l_Item_rec_in.INTERNAL_VOLUME := G_Item_Rec.INTERNAL_VOLUME;
1104 l_Item_rec_in.CONTAINER_TYPE_CODE := G_Item_Rec.CONTAINER_TYPE_CODE;
1105 l_Item_rec_in.COLLATERAL_FLAG := G_Item_Rec.COLLATERAL_FLAG;
1106 l_Item_rec_in.EVENT_FLAG := G_Item_Rec.EVENT_FLAG;
1107 l_Item_rec_in.EQUIPMENT_TYPE := G_Item_Rec.EQUIPMENT_TYPE;
1108 l_Item_rec_in.ELECTRONIC_FLAG := G_Item_Rec.ELECTRONIC_FLAG;
1109 l_Item_rec_in.DOWNLOADABLE_FLAG := G_Item_Rec.DOWNLOADABLE_FLAG;
1110 l_Item_rec_in.INDIVISIBLE_FLAG := G_Item_Rec.INDIVISIBLE_FLAG;
1111 l_Item_rec_in.DIMENSION_UOM_CODE := G_Item_Rec.DIMENSION_UOM_CODE;
1112 l_Item_rec_in.UNIT_LENGTH := G_Item_Rec.UNIT_LENGTH;
1113 l_Item_rec_in.UNIT_WIDTH := G_Item_Rec.UNIT_WIDTH;
1114 l_Item_rec_in.UNIT_HEIGHT := G_Item_Rec.UNIT_HEIGHT;
1115 --
1116 l_Item_rec_in.INVENTORY_PLANNING_CODE := G_Item_Rec.INVENTORY_PLANNING_CODE;
1117 l_Item_rec_in.PLANNER_CODE := G_Item_Rec.PLANNER_CODE;
1118 l_Item_rec_in.PLANNING_MAKE_BUY_CODE := G_Item_Rec.PLANNING_MAKE_BUY_CODE;
1119 l_Item_rec_in.MIN_MINMAX_QUANTITY := G_Item_Rec.MIN_MINMAX_QUANTITY;
1120 l_Item_rec_in.MAX_MINMAX_QUANTITY := G_Item_Rec.MAX_MINMAX_QUANTITY;
1121 l_Item_rec_in.SAFETY_STOCK_BUCKET_DAYS := G_Item_Rec.SAFETY_STOCK_BUCKET_DAYS;
1122 l_Item_rec_in.CARRYING_COST := G_Item_Rec.CARRYING_COST;
1123 l_Item_rec_in.ORDER_COST := G_Item_Rec.ORDER_COST;
1124 l_Item_rec_in.MRP_SAFETY_STOCK_PERCENT := G_Item_Rec.MRP_SAFETY_STOCK_PERCENT;
1125 l_Item_rec_in.MRP_SAFETY_STOCK_CODE := G_Item_Rec.MRP_SAFETY_STOCK_CODE;
1126 l_Item_rec_in.FIXED_ORDER_QUANTITY := G_Item_Rec.FIXED_ORDER_QUANTITY;
1127 l_Item_rec_in.FIXED_DAYS_SUPPLY := G_Item_Rec.FIXED_DAYS_SUPPLY;
1128 l_Item_rec_in.MINIMUM_ORDER_QUANTITY := G_Item_Rec.MINIMUM_ORDER_QUANTITY;
1129 l_Item_rec_in.MAXIMUM_ORDER_QUANTITY := G_Item_Rec.MAXIMUM_ORDER_QUANTITY;
1130 l_Item_rec_in.FIXED_LOT_MULTIPLIER := G_Item_Rec.FIXED_LOT_MULTIPLIER;
1131 l_Item_rec_in.SOURCE_TYPE := G_Item_Rec.SOURCE_TYPE;
1132 l_Item_rec_in.SOURCE_ORGANIZATION_ID := G_Item_Rec.SOURCE_ORGANIZATION_ID;
1133 l_Item_rec_in.SOURCE_SUBINVENTORY := G_Item_Rec.SOURCE_SUBINVENTORY;
1134 l_Item_rec_in.MRP_PLANNING_CODE := G_Item_Rec.MRP_PLANNING_CODE;
1135 l_Item_rec_in.ATO_FORECAST_CONTROL := G_Item_Rec.ATO_FORECAST_CONTROL;
1136 l_Item_rec_in.PLANNING_EXCEPTION_SET := G_Item_Rec.PLANNING_EXCEPTION_SET;
1137 l_Item_rec_in.SHRINKAGE_RATE := G_Item_Rec.SHRINKAGE_RATE;
1138 l_Item_rec_in.END_ASSEMBLY_PEGGING_FLAG := G_Item_Rec.END_ASSEMBLY_PEGGING_FLAG;
1139 l_Item_rec_in.ROUNDING_CONTROL_TYPE := G_Item_Rec.ROUNDING_CONTROL_TYPE;
1140 l_Item_rec_in.PLANNED_INV_POINT_FLAG := G_Item_Rec.PLANNED_INV_POINT_FLAG;
1141 l_Item_rec_in.CREATE_SUPPLY_FLAG := G_Item_Rec.CREATE_SUPPLY_FLAG;
1142 l_Item_rec_in.ACCEPTABLE_EARLY_DAYS := G_Item_Rec.ACCEPTABLE_EARLY_DAYS;
1143 l_Item_rec_in.MRP_CALCULATE_ATP_FLAG := G_Item_Rec.MRP_CALCULATE_ATP_FLAG;
1144 l_Item_rec_in.AUTO_REDUCE_MPS := G_Item_Rec.AUTO_REDUCE_MPS;
1145 l_Item_rec_in.REPETITIVE_PLANNING_FLAG := G_Item_Rec.REPETITIVE_PLANNING_FLAG;
1146 l_Item_rec_in.OVERRUN_PERCENTAGE := G_Item_Rec.OVERRUN_PERCENTAGE;
1147 l_Item_rec_in.ACCEPTABLE_RATE_DECREASE := G_Item_Rec.ACCEPTABLE_RATE_DECREASE;
1148 l_Item_rec_in.ACCEPTABLE_RATE_INCREASE := G_Item_Rec.ACCEPTABLE_RATE_INCREASE;
1149 l_Item_rec_in.PLANNING_TIME_FENCE_CODE := G_Item_Rec.PLANNING_TIME_FENCE_CODE;
1150 l_Item_rec_in.PLANNING_TIME_FENCE_DAYS := G_Item_Rec.PLANNING_TIME_FENCE_DAYS;
1151 l_Item_rec_in.DEMAND_TIME_FENCE_CODE := G_Item_Rec.DEMAND_TIME_FENCE_CODE;
1152 l_Item_rec_in.DEMAND_TIME_FENCE_DAYS := G_Item_Rec.DEMAND_TIME_FENCE_DAYS;
1153 l_Item_rec_in.RELEASE_TIME_FENCE_CODE := G_Item_Rec.RELEASE_TIME_FENCE_CODE;
1154 l_Item_rec_in.RELEASE_TIME_FENCE_DAYS := G_Item_Rec.RELEASE_TIME_FENCE_DAYS;
1155 l_Item_rec_in.SUBSTITUTION_WINDOW_CODE := G_Item_Rec.SUBSTITUTION_WINDOW_CODE;
1156 l_Item_rec_in.SUBSTITUTION_WINDOW_DAYS := G_Item_Rec.SUBSTITUTION_WINDOW_DAYS;
1157
1158 -- Lead Times
1159 l_Item_rec_in.PREPROCESSING_LEAD_TIME := G_Item_Rec.PREPROCESSING_LEAD_TIME;
1160 l_Item_rec_in.FULL_LEAD_TIME := G_Item_Rec.FULL_LEAD_TIME;
1161 l_Item_rec_in.POSTPROCESSING_LEAD_TIME := G_Item_Rec.POSTPROCESSING_LEAD_TIME;
1162 l_Item_rec_in.FIXED_LEAD_TIME := G_Item_Rec.FIXED_LEAD_TIME;
1163 l_Item_rec_in.VARIABLE_LEAD_TIME := G_Item_Rec.VARIABLE_LEAD_TIME;
1164 l_Item_rec_in.CUM_MANUFACTURING_LEAD_TIME:= G_Item_Rec.CUM_MANUFACTURING_LEAD_TIME;
1165 l_Item_rec_in.CUMULATIVE_TOTAL_LEAD_TIME := G_Item_Rec.CUMULATIVE_TOTAL_LEAD_TIME;
1166 l_Item_rec_in.LEAD_TIME_LOT_SIZE := G_Item_Rec.LEAD_TIME_LOT_SIZE;
1167
1168 -- WIP
1169 l_Item_rec_in.BUILD_IN_WIP_FLAG := G_Item_Rec.BUILD_IN_WIP_FLAG;
1170 l_Item_rec_in.WIP_SUPPLY_TYPE := G_Item_Rec.WIP_SUPPLY_TYPE;
1171 l_Item_rec_in.WIP_SUPPLY_SUBINVENTORY := G_Item_Rec.WIP_SUPPLY_SUBINVENTORY;
1172 l_Item_rec_in.WIP_SUPPLY_LOCATOR_ID := G_Item_Rec.WIP_SUPPLY_LOCATOR_ID;
1173 l_Item_rec_in.OVERCOMPLETION_TOLERANCE_TYPE := G_Item_Rec.OVERCOMPLETION_TOLERANCE_TYPE;
1174 l_Item_rec_in.OVERCOMPLETION_TOLERANCE_VALUE := G_Item_Rec.OVERCOMPLETION_TOLERANCE_VALUE;
1175 l_Item_rec_in.INVENTORY_CARRY_PENALTY := G_Item_Rec.INVENTORY_CARRY_PENALTY;
1176 l_Item_rec_in.OPERATION_SLACK_PENALTY := G_Item_Rec.OPERATION_SLACK_PENALTY;
1177
1178 -- Order Management
1179 l_Item_rec_in.CUSTOMER_ORDER_FLAG := G_Item_Rec.CUSTOMER_ORDER_FLAG;
1180 l_Item_rec_in.CUSTOMER_ORDER_ENABLED_FLAG:= G_Item_Rec.CUSTOMER_ORDER_ENABLED_FLAG;
1181 l_Item_rec_in.INTERNAL_ORDER_FLAG := G_Item_Rec.INTERNAL_ORDER_FLAG;
1182 l_Item_rec_in.INTERNAL_ORDER_ENABLED_FLAG:= G_Item_Rec.INTERNAL_ORDER_ENABLED_FLAG;
1183 l_Item_rec_in.SHIPPABLE_ITEM_FLAG := G_Item_Rec.SHIPPABLE_ITEM_FLAG;
1184 l_Item_rec_in.SO_TRANSACTIONS_FLAG := G_Item_Rec.SO_TRANSACTIONS_FLAG;
1185 l_Item_rec_in.PICKING_RULE_ID := G_Item_Rec.PICKING_RULE_ID;
1186 l_Item_rec_in.PICK_COMPONENTS_FLAG := G_Item_Rec.PICK_COMPONENTS_FLAG;
1187 l_Item_rec_in.REPLENISH_TO_ORDER_FLAG := G_Item_Rec.REPLENISH_TO_ORDER_FLAG;
1188 l_Item_rec_in.ATP_FLAG := G_Item_Rec.ATP_FLAG;
1189 l_Item_rec_in.ATP_COMPONENTS_FLAG := G_Item_Rec.ATP_COMPONENTS_FLAG;
1190 l_Item_rec_in.ATP_RULE_ID := G_Item_Rec.ATP_RULE_ID;
1191 l_Item_rec_in.SHIP_MODEL_COMPLETE_FLAG := G_Item_Rec.SHIP_MODEL_COMPLETE_FLAG;
1192 l_Item_rec_in.DEFAULT_SHIPPING_ORG := G_Item_Rec.DEFAULT_SHIPPING_ORG;
1193 l_Item_rec_in.DEFAULT_SO_SOURCE_TYPE := G_Item_Rec.DEFAULT_SO_SOURCE_TYPE;
1194 l_Item_rec_in.RETURNABLE_FLAG := G_Item_Rec.RETURNABLE_FLAG;
1195 l_Item_rec_in.RETURN_INSPECTION_REQUIREMENT := G_Item_Rec.RETURN_INSPECTION_REQUIREMENT;
1196 l_Item_rec_in.OVER_SHIPMENT_TOLERANCE := G_Item_Rec.OVER_SHIPMENT_TOLERANCE;
1197 l_Item_rec_in.UNDER_SHIPMENT_TOLERANCE := G_Item_Rec.UNDER_SHIPMENT_TOLERANCE;
1198 l_Item_rec_in.OVER_RETURN_TOLERANCE := G_Item_Rec.OVER_RETURN_TOLERANCE;
1199 l_Item_rec_in.UNDER_RETURN_TOLERANCE := G_Item_Rec.UNDER_RETURN_TOLERANCE;
1200 l_Item_rec_in.FINANCING_ALLOWED_FLAG := G_Item_Rec.FINANCING_ALLOWED_FLAG;
1201 l_Item_rec_in.VOL_DISCOUNT_EXEMPT_FLAG := G_Item_Rec.VOL_DISCOUNT_EXEMPT_FLAG;
1202 l_Item_rec_in.COUPON_EXEMPT_FLAG := G_Item_Rec.COUPON_EXEMPT_FLAG;
1203 l_Item_rec_in.INVOICEABLE_ITEM_FLAG := G_Item_Rec.INVOICEABLE_ITEM_FLAG;
1204 l_Item_rec_in.INVOICE_ENABLED_FLAG := G_Item_Rec.INVOICE_ENABLED_FLAG;
1205 l_Item_rec_in.ACCOUNTING_RULE_ID := G_Item_Rec.ACCOUNTING_RULE_ID;
1206 l_Item_rec_in.INVOICING_RULE_ID := G_Item_Rec.INVOICING_RULE_ID;
1207 l_Item_rec_in.TAX_CODE := G_Item_Rec.TAX_CODE;
1208 l_Item_rec_in.SALES_ACCOUNT := G_Item_Rec.SALES_ACCOUNT;
1209 l_Item_rec_in.PAYMENT_TERMS_ID := G_Item_Rec.PAYMENT_TERMS_ID;
1210
1211 -- Service
1212 l_Item_rec_in.CONTRACT_ITEM_TYPE_CODE := G_Item_Rec.CONTRACT_ITEM_TYPE_CODE;
1213 l_Item_rec_in.SERVICE_DURATION_PERIOD_CODE := G_Item_Rec.SERVICE_DURATION_PERIOD_CODE;
1214 l_Item_rec_in.SERVICE_DURATION := G_Item_Rec.SERVICE_DURATION;
1215 l_Item_rec_in.COVERAGE_SCHEDULE_ID := G_Item_Rec.COVERAGE_SCHEDULE_ID;
1216 l_Item_rec_in.SUBSCRIPTION_DEPEND_FLAG := G_Item_Rec.SUBSCRIPTION_DEPEND_FLAG;
1217 l_Item_rec_in.SERV_IMPORTANCE_LEVEL := G_Item_Rec.SERV_IMPORTANCE_LEVEL;
1218 l_Item_rec_in.SERV_REQ_ENABLED_CODE := G_Item_Rec.SERV_REQ_ENABLED_CODE;
1219 l_Item_rec_in.COMMS_ACTIVATION_REQD_FLAG := G_Item_Rec.COMMS_ACTIVATION_REQD_FLAG;
1220 l_Item_rec_in.SERVICEABLE_PRODUCT_FLAG := G_Item_Rec.SERVICEABLE_PRODUCT_FLAG;
1221 l_Item_rec_in.MATERIAL_BILLABLE_FLAG := G_Item_Rec.MATERIAL_BILLABLE_FLAG;
1222 l_Item_rec_in.SERV_BILLING_ENABLED_FLAG := G_Item_Rec.SERV_BILLING_ENABLED_FLAG;
1223 l_Item_rec_in.DEFECT_TRACKING_ON_FLAG := G_Item_Rec.DEFECT_TRACKING_ON_FLAG;
1224 l_Item_rec_in.RECOVERED_PART_DISP_CODE := G_Item_Rec.RECOVERED_PART_DISP_CODE;
1225 l_Item_rec_in.COMMS_NL_TRACKABLE_FLAG := G_Item_Rec.COMMS_NL_TRACKABLE_FLAG;
1226 l_Item_rec_in.ASSET_CREATION_CODE := G_Item_Rec.ASSET_CREATION_CODE;
1227 l_Item_rec_in.IB_ITEM_INSTANCE_CLASS := G_Item_Rec.IB_ITEM_INSTANCE_CLASS;
1228 l_Item_rec_in.SERVICE_STARTING_DELAY := G_Item_Rec.SERVICE_STARTING_DELAY;
1229
1230 -- Web Option
1231 l_Item_rec_in.WEB_STATUS := G_Item_Rec.WEB_STATUS;
1232 l_Item_rec_in.ORDERABLE_ON_WEB_FLAG := G_Item_Rec.ORDERABLE_ON_WEB_FLAG;
1233 l_Item_rec_in.BACK_ORDERABLE_FLAG := G_Item_Rec.BACK_ORDERABLE_FLAG;
1234 l_Item_rec_in.MINIMUM_LICENSE_QUANTITY := G_Item_Rec.MINIMUM_LICENSE_QUANTITY;
1235
1236 --Start: 26 new attributes
1237 l_Item_rec_in.TRACKING_QUANTITY_IND := G_Item_Rec.TRACKING_QUANTITY_IND;
1238 l_Item_rec_in.ONT_PRICING_QTY_SOURCE := G_Item_Rec.ONT_PRICING_QTY_SOURCE;
1239 l_Item_rec_in.SECONDARY_DEFAULT_IND := G_Item_Rec.SECONDARY_DEFAULT_IND;
1240
1241 --Option specific sourced not used in grp package. This is a invisible field.
1242 --l_Item_rec_in.OPTION_SPECIFIC_SOURCED := G_Item_Rec.OPTION_SPECIFIC_SOURCED;
1243 l_Item_rec_in.VMI_MINIMUM_UNITS := G_Item_Rec.VMI_MINIMUM_UNITS;
1244 l_Item_rec_in.VMI_MINIMUM_DAYS := G_Item_Rec.VMI_MINIMUM_DAYS;
1245 l_Item_rec_in.VMI_MAXIMUM_UNITS := G_Item_Rec.VMI_MAXIMUM_UNITS;
1246 l_Item_rec_in.VMI_MAXIMUM_DAYS := G_Item_Rec.VMI_MAXIMUM_DAYS;
1247 l_Item_rec_in.VMI_FIXED_ORDER_QUANTITY := G_Item_Rec.VMI_FIXED_ORDER_QUANTITY;
1248 l_Item_rec_in.SO_AUTHORIZATION_FLAG := G_Item_Rec.SO_AUTHORIZATION_FLAG;
1249 l_Item_rec_in.CONSIGNED_FLAG := G_Item_Rec.CONSIGNED_FLAG;
1250 l_Item_rec_in.ASN_AUTOEXPIRE_FLAG := G_Item_Rec.ASN_AUTOEXPIRE_FLAG;
1251 l_Item_rec_in.VMI_FORECAST_TYPE := G_Item_Rec.VMI_FORECAST_TYPE;
1252 l_Item_rec_in.FORECAST_HORIZON := G_Item_Rec.FORECAST_HORIZON;
1253 l_Item_rec_in.EXCLUDE_FROM_BUDGET_FLAG := G_Item_Rec.EXCLUDE_FROM_BUDGET_FLAG;
1254 l_Item_rec_in.DAYS_TGT_INV_SUPPLY := G_Item_Rec.DAYS_TGT_INV_SUPPLY;
1255 l_Item_rec_in.DAYS_TGT_INV_WINDOW := G_Item_Rec.DAYS_TGT_INV_WINDOW;
1256 l_Item_rec_in.DAYS_MAX_INV_SUPPLY := G_Item_Rec.DAYS_MAX_INV_SUPPLY;
1257 l_Item_rec_in.DAYS_MAX_INV_WINDOW := G_Item_Rec.DAYS_MAX_INV_WINDOW;
1258 l_Item_rec_in.DRP_PLANNED_FLAG := G_Item_Rec.DRP_PLANNED_FLAG;
1259 l_Item_rec_in.CRITICAL_COMPONENT_FLAG := G_Item_Rec.CRITICAL_COMPONENT_FLAG;
1260 l_Item_rec_in.CONTINOUS_TRANSFER := G_Item_Rec.CONTINOUS_TRANSFER;
1261 l_Item_rec_in.CONVERGENCE := G_Item_Rec.CONVERGENCE;
1262 l_Item_rec_in.DIVERGENCE := G_Item_Rec.DIVERGENCE;
1263 l_Item_rec_in.CONFIG_ORGS := G_Item_Rec.CONFIG_ORGS;
1264 l_Item_rec_in.CONFIG_MATCH := G_Item_Rec.CONFIG_MATCH;
1265 --End: 26 new attributes
1266 IF G_Item_Rec.Process_Item_Record NOT IN (1,2) THEN
1267 G_Item_Rec.Process_Item_Record := 1;
1268 END IF;
1269 l_Item_rec_in.Process_Item_Record := G_Item_Rec.Process_Item_Record;
1270
1271 -- Descriptive flex
1272 l_Item_rec_in.ATTRIBUTE_CATEGORY := G_Item_Rec.Attribute_Category;
1273 l_Item_rec_in.ATTRIBUTE1 := G_Item_Rec.Attribute1;
1274 l_Item_rec_in.ATTRIBUTE2 := G_Item_Rec.Attribute2;
1275 l_Item_rec_in.ATTRIBUTE3 := G_Item_Rec.Attribute3;
1276 l_Item_rec_in.ATTRIBUTE4 := G_Item_Rec.Attribute4;
1277 l_Item_rec_in.ATTRIBUTE5 := G_Item_Rec.Attribute5;
1278 l_Item_rec_in.ATTRIBUTE6 := G_Item_Rec.Attribute6;
1279 l_Item_rec_in.ATTRIBUTE7 := G_Item_Rec.Attribute7;
1280 l_Item_rec_in.ATTRIBUTE8 := G_Item_Rec.Attribute8;
1281 l_Item_rec_in.ATTRIBUTE9 := G_Item_Rec.Attribute9;
1282 l_Item_rec_in.ATTRIBUTE10 := G_Item_Rec.Attribute10;
1283 l_Item_rec_in.ATTRIBUTE11 := G_Item_Rec.Attribute11;
1284 l_Item_rec_in.ATTRIBUTE12 := G_Item_Rec.Attribute12;
1285 l_Item_rec_in.ATTRIBUTE13 := G_Item_Rec.Attribute13;
1286 l_Item_rec_in.ATTRIBUTE14 := G_Item_Rec.Attribute14;
1287 l_Item_rec_in.ATTRIBUTE15 := G_Item_Rec.Attribute15;
1288 l_Item_rec_in.ATTRIBUTE16 := G_Item_Rec.Attribute16;
1289 l_Item_rec_in.ATTRIBUTE17 := G_Item_Rec.Attribute17;
1290 l_Item_rec_in.ATTRIBUTE18 := G_Item_Rec.Attribute18;
1291 l_Item_rec_in.ATTRIBUTE19 := G_Item_Rec.Attribute19;
1292 l_Item_rec_in.ATTRIBUTE20 := G_Item_Rec.Attribute20;
1293 l_Item_rec_in.ATTRIBUTE21 := G_Item_Rec.Attribute21;
1294 l_Item_rec_in.ATTRIBUTE22 := G_Item_Rec.Attribute22;
1295 l_Item_rec_in.ATTRIBUTE23 := G_Item_Rec.Attribute23;
1296 l_Item_rec_in.ATTRIBUTE24 := G_Item_Rec.Attribute24;
1297 l_Item_rec_in.ATTRIBUTE25 := G_Item_Rec.Attribute25;
1298 l_Item_rec_in.ATTRIBUTE26 := G_Item_Rec.Attribute26;
1299 l_Item_rec_in.ATTRIBUTE27 := G_Item_Rec.Attribute27;
1300 l_Item_rec_in.ATTRIBUTE28 := G_Item_Rec.Attribute28;
1301 l_Item_rec_in.ATTRIBUTE29 := G_Item_Rec.Attribute29;
1302 l_Item_rec_in.ATTRIBUTE30 := G_Item_Rec.Attribute30;
1303 -- Global Descriptive flex
1304 l_Item_rec_in.GLOBAL_ATTRIBUTE_CATEGORY := G_Item_Rec.Global_Attribute_Category;
1305 l_Item_rec_in.GLOBAL_ATTRIBUTE1 := G_Item_Rec.Global_Attribute1;
1306 l_Item_rec_in.GLOBAL_ATTRIBUTE2 := G_Item_Rec.Global_Attribute2;
1307 l_Item_rec_in.GLOBAL_ATTRIBUTE3 := G_Item_Rec.Global_Attribute3;
1308 l_Item_rec_in.GLOBAL_ATTRIBUTE4 := G_Item_Rec.Global_Attribute4;
1309 l_Item_rec_in.GLOBAL_ATTRIBUTE5 := G_Item_Rec.Global_Attribute5;
1310 l_Item_rec_in.GLOBAL_ATTRIBUTE6 := G_Item_Rec.Global_Attribute6;
1311 l_Item_rec_in.GLOBAL_ATTRIBUTE7 := G_Item_Rec.Global_Attribute7;
1312 l_Item_rec_in.GLOBAL_ATTRIBUTE8 := G_Item_Rec.Global_Attribute8;
1313 l_Item_rec_in.GLOBAL_ATTRIBUTE9 := G_Item_Rec.Global_Attribute9;
1314 l_Item_rec_in.GLOBAL_ATTRIBUTE10 := G_Item_Rec.Global_Attribute10;
1315
1316
1317 l_Item_rec_in.GLOBAL_ATTRIBUTE11 := G_Item_Rec.Global_Attribute11;
1318 l_Item_rec_in.GLOBAL_ATTRIBUTE12 := G_Item_Rec.Global_Attribute12;
1319 l_Item_rec_in.GLOBAL_ATTRIBUTE13 := G_Item_Rec.Global_Attribute13;
1320 l_Item_rec_in.GLOBAL_ATTRIBUTE14 := G_Item_Rec.Global_Attribute14;
1321 l_Item_rec_in.GLOBAL_ATTRIBUTE15 := G_Item_Rec.Global_Attribute15;
1322 l_Item_rec_in.GLOBAL_ATTRIBUTE16 := G_Item_Rec.Global_Attribute16;
1323 l_Item_rec_in.GLOBAL_ATTRIBUTE17 := G_Item_Rec.Global_Attribute17;
1324 l_Item_rec_in.GLOBAL_ATTRIBUTE18 := G_Item_Rec.Global_Attribute18;
1325 l_Item_rec_in.GLOBAL_ATTRIBUTE19 := G_Item_Rec.Global_Attribute19;
1326 l_Item_rec_in.GLOBAL_ATTRIBUTE20 := G_Item_Rec.Global_Attribute20;
1327
1328 /* R12 Enhacement */
1329
1330 l_Item_rec_in.CAS_NUMBER := G_Item_Rec.CAS_NUMBER;
1331 l_Item_rec_in.CHILD_LOT_FLAG := G_Item_Rec.CHILD_LOT_FLAG;
1332 l_Item_rec_in.CHILD_LOT_PREFIX := G_Item_Rec.CHILD_LOT_PREFIX;
1333 l_Item_rec_in.CHILD_LOT_STARTING_NUMBER := G_Item_Rec.CHILD_LOT_STARTING_NUMBER;
1334 l_Item_rec_in.CHILD_LOT_VALIDATION_FLAG := G_Item_Rec.CHILD_LOT_VALIDATION_FLAG;
1335 l_Item_rec_in.COPY_LOT_ATTRIBUTE_FLAG := G_Item_Rec.COPY_LOT_ATTRIBUTE_FLAG;
1336 l_Item_rec_in.DEFAULT_GRADE := G_Item_Rec.DEFAULT_GRADE;
1337 l_Item_rec_in.EXPIRATION_ACTION_CODE := G_Item_Rec.EXPIRATION_ACTION_CODE;
1338 l_Item_rec_in.EXPIRATION_ACTION_INTERVAL := G_Item_Rec.EXPIRATION_ACTION_INTERVAL;
1339 l_Item_rec_in.GRADE_CONTROL_FLAG := G_Item_Rec.GRADE_CONTROL_FLAG;
1340 l_Item_rec_in.HAZARDOUS_MATERIAL_FLAG := G_Item_Rec.HAZARDOUS_MATERIAL_FLAG;
1341 l_Item_rec_in.HOLD_DAYS := G_Item_Rec.HOLD_DAYS;
1342 l_Item_rec_in.LOT_DIVISIBLE_FLAG := G_Item_Rec.LOT_DIVISIBLE_FLAG;
1343 l_Item_rec_in.MATURITY_DAYS := G_Item_Rec.MATURITY_DAYS;
1344 l_Item_rec_in.PARENT_CHILD_GENERATION_FLAG := G_Item_Rec.PARENT_CHILD_GENERATION_FLAG;
1345 l_Item_rec_in.PROCESS_COSTING_ENABLED_FLAG := G_Item_Rec.PROCESS_COSTING_ENABLED_FLAG;
1346 l_Item_rec_in.PROCESS_EXECUTION_ENABLED_FLAG := G_Item_Rec.PROCESS_EXECUTION_ENABLED_FLAG;
1347 l_Item_rec_in.PROCESS_QUALITY_ENABLED_FLAG := G_Item_Rec.PROCESS_QUALITY_ENABLED_FLAG;
1348 l_Item_rec_in.PROCESS_SUPPLY_LOCATOR_ID := G_Item_Rec.PROCESS_SUPPLY_LOCATOR_ID;
1349 l_Item_rec_in.PROCESS_SUPPLY_SUBINVENTORY := G_Item_Rec.PROCESS_SUPPLY_SUBINVENTORY;
1350 l_Item_rec_in.PROCESS_YIELD_LOCATOR_ID := G_Item_Rec.PROCESS_YIELD_LOCATOR_ID;
1351 l_Item_rec_in.PROCESS_YIELD_SUBINVENTORY := G_Item_Rec.PROCESS_YIELD_SUBINVENTORY;
1352 l_Item_rec_in.RECIPE_ENABLED_FLAG := G_Item_Rec.RECIPE_ENABLED_FLAG;
1353 l_Item_rec_in.RETEST_INTERVAL := G_Item_Rec.RETEST_INTERVAL;
1354 l_Item_rec_in.CHARGE_PERIODICITY_CODE := G_Item_Rec.CHARGE_PERIODICITY_CODE;
1355 l_Item_rec_in.REPAIR_LEADTIME := G_Item_Rec.REPAIR_LEADTIME;
1356 l_Item_rec_in.REPAIR_YIELD := G_Item_Rec.REPAIR_YIELD;
1357 l_Item_rec_in.PREPOSITION_POINT := G_Item_Rec.PREPOSITION_POINT;
1358 l_Item_rec_in.REPAIR_PROGRAM := G_Item_Rec.REPAIR_PROGRAM;
1359 l_Item_rec_in.SUBCONTRACTING_COMPONENT := G_Item_Rec.SUBCONTRACTING_COMPONENT ;
1360 l_Item_rec_in.OUTSOURCED_ASSEMBLY := G_Item_Rec.OUTSOURCED_ASSEMBLY;
1361 --R12 C attributes
1362 l_Item_rec_in.GDSN_OUTBOUND_ENABLED_FLAG := G_Item_Rec.GDSN_OUTBOUND_ENABLED_FLAG;
1363 l_Item_rec_in.TRADE_ITEM_DESCRIPTOR := G_Item_Rec.TRADE_ITEM_DESCRIPTOR;
1364 l_item_rec_in.STYLE_ITEM_FLAG := G_Item_Rec.STYLE_ITEM_FLAG;
1365 l_item_rec_in.STYLE_ITEM_ID := G_Item_Rec.STYLE_ITEM_ID;
1366
1367 --Start : Added revision record processing
1368 BEGIN
1369 IF NOT l_rev_index_failure THEN
1370 l_revision_rec.Transaction_Type := G_Revision_Tbl(G_Item_indx).Transaction_Type;
1371 l_revision_rec.Inventory_Item_Id := G_Revision_Tbl(G_Item_indx).Inventory_Item_Id;
1372 l_revision_rec.Item_Number := G_Revision_Tbl(G_Item_indx).Item_Number;
1373 l_revision_rec.Organization_Id := G_Revision_Tbl(G_Item_indx).Organization_Id;
1374 l_revision_rec.Revision_Id := G_Revision_Tbl(G_Item_indx).Revision_Id;
1375 l_revision_rec.Revision_Code := G_Revision_Tbl(G_Item_indx).Revision_Code;
1376 l_revision_rec.Revision_Label := G_Revision_Tbl(G_Item_indx).Revision_Label;
1377 l_revision_rec.Description := G_Revision_Tbl(G_Item_indx).Description;
1378 l_revision_rec.Effectivity_Date := G_Revision_Tbl(G_Item_indx).Effectivity_Date;
1379 l_revision_rec.Lifecycle_Id := G_Revision_Tbl(G_Item_indx).Lifecycle_Id;
1380 l_revision_rec.Current_Phase_Id := G_Revision_Tbl(G_Item_indx).Current_Phase_Id;
1381 -- 5208102: Supporting template for UDA's at revisions
1382 l_revision_rec.template_Id := G_Revision_Tbl(G_Item_indx).Template_Id;
1383 l_revision_rec.template_Name := G_Revision_Tbl(G_Item_indx).Template_Name;
1384
1385 l_revision_rec.Attribute_Category := G_Revision_Tbl(G_Item_indx).Attribute_Category;
1386 l_revision_rec.Attribute1 := G_Revision_Tbl(G_Item_indx).Attribute1;
1387 l_revision_rec.Attribute2 := G_Revision_Tbl(G_Item_indx).Attribute2;
1388 l_revision_rec.Attribute3 := G_Revision_Tbl(G_Item_indx).Attribute3;
1389 l_revision_rec.Attribute4 := G_Revision_Tbl(G_Item_indx).Attribute4;
1390 l_revision_rec.Attribute5 := G_Revision_Tbl(G_Item_indx).Attribute5;
1391 l_revision_rec.Attribute6 := G_Revision_Tbl(G_Item_indx).Attribute6;
1392 l_revision_rec.Attribute7 := G_Revision_Tbl(G_Item_indx).Attribute7;
1393 l_revision_rec.Attribute8 := G_Revision_Tbl(G_Item_indx).Attribute8;
1394 l_revision_rec.Attribute9 := G_Revision_Tbl(G_Item_indx).Attribute9;
1395 l_revision_rec.Attribute10 := G_Revision_Tbl(G_Item_indx).Attribute10;
1396 l_revision_rec.Attribute11 := G_Revision_Tbl(G_Item_indx).Attribute11;
1397 l_revision_rec.Attribute12 := G_Revision_Tbl(G_Item_indx).Attribute12;
1398 l_revision_rec.Attribute13 := G_Revision_Tbl(G_Item_indx).Attribute13;
1399 l_revision_rec.Attribute14 := G_Revision_Tbl(G_Item_indx).Attribute14;
1400 l_revision_rec.Attribute15 := G_Revision_Tbl(G_Item_indx).Attribute15;
1401 ELSE
1402 l_revision_rec := NULL;
1403 END IF;
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 l_revision_rec := NULL;
1407 l_rev_index_failure := TRUE;
1408 -- Item and Item Rev should ideally be have same
1409 -- index and same number of pl/sql records.
1410 END;
1411 --End : Added revision record processing
1412
1413 IF l_Process_Control = 'EGO_INTERFACE_HANDLER' THEN
1414 INV_ITEM_GRP.Interface_Handler
1415 (
1416 p_commit => FND_API.G_TRUE
1417 ,p_transaction_type => G_Item_Rec.Transaction_Type
1418 ,p_Item_rec => l_Item_rec_in
1419 ,P_revision_rec => l_revision_rec
1420 ,p_Template_Id => l_template_id
1421 ,P_Template_Name => l_Template_Name
1422 ,x_batch_id => l_Batch_id
1423 ,x_return_status => l_return_status
1424 ,x_return_err => l_return_err
1425 );
1426
1427 FND_MESSAGE.set_name('INV', l_return_err); --Setting error message to be returned
1428
1429 ELSIF ( G_Item_Rec.Transaction_Type = 'CREATE' ) THEN
1430
1431 INV_ITEM_GRP.Create_Item
1432 (
1433 p_commit => p_commit
1434 , p_Item_rec => l_Item_rec_in
1435 , p_Revision_rec => l_revision_rec
1436 , p_Template_Id => l_Template_Id
1437 , p_Template_Name => l_Template_Name
1438 , x_Item_rec => l_Item_rec_out
1439 , x_return_status => l_return_status
1440 , x_Error_tbl => l_Error_tbl
1441 );
1442
1443 --Start 4105841 : Business Event Enhancement
1444 IF ( l_return_status = G_RET_STS_SUCCESS and G_Item_Rec.Process_Item_Record = 1) THEN
1445
1446 OPEN c_get_org_code(cp_org_id => l_Item_rec_in.ORGANIZATION_ID);
1447 FETCH c_get_org_code INTO l_org_code_rec;
1448 CLOSE c_get_org_code;
1449
1450 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) THEN --Bug: 4881908
1451 -----------------------------------------------------------------
1452 -- Get the functional Area deafulting Attribute Values for the
1453 -- Record and raise business Event.
1454 -----------------------------------------------------------------
1455 FOR DEFAULT_CAT_ASSIGN_REC IN DEFAULT_CAT_ASSIGN_CREATE(CP_ITEM_ID => l_Item_rec_out.INVENTORY_ITEM_ID ,CP_ORG_ID => l_Item_rec_out.ORGANIZATION_ID)
1456 LOOP
1457 EGO_WF_WRAPPER_PVT.Raise_Item_Event(
1458 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT
1459 ,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
1460 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1461 ,p_catalog_id => DEFAULT_CAT_ASSIGN_REC.CATEGORY_SET_ID
1462 ,p_category_id => DEFAULT_CAT_ASSIGN_REC.CATEGORY_ID
1463 ,x_msg_data => l_msg_data
1464 ,x_return_status => l_event_return_status);
1465 END LOOP;
1466 -----------------------------------------------------------------
1467 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
1468 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT
1469 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1470 ,p_organization_code => l_org_code_rec.ORGANIZATION_CODE
1471 ,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
1472 ,p_item_number => l_Item_rec_in.ITEM_NUMBER
1473 ,p_item_description => l_Item_rec_in.DESCRIPTION
1474 ,x_msg_data => l_msg_data
1475 ,x_return_status => l_event_return_status);
1476 -----------------------------------------------------------------
1477 -- Default Revision Business Event Raising
1478 -----------------------------------------------------------------
1479 FOR REV_RECORDS_CREATE_REC IN REV_RECORDS_CREATE(CP_ITEM_ID => l_Item_rec_out.INVENTORY_ITEM_ID ,CP_ORG_ID => l_Item_rec_out.ORGANIZATION_ID)
1480 LOOP
1481 EGO_WF_WRAPPER_PVT.Raise_Item_Event(
1482 p_event_name => EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT
1483 ,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
1484 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1485 ,p_revision_id => REV_RECORDS_CREATE_REC.REVISION_ID
1486 ,x_msg_data => l_msg_data
1487 ,x_return_status => l_event_return_status);
1488 END LOOP;
1489 -----------------------------------------------------------------
1490 END IF;
1491
1492 /*Removed the call for default Revision creation
1493 Will be raising events for explicit actions only*/
1494
1495 --Call ICX APIs
1496 BEGIN
1497 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
1498 p_entity_type => 'ITEM'
1499 ,p_dml_type => 'CREATE'
1500 ,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
1501 ,p_item_number => l_Item_rec_in.ITEM_NUMBER
1502 ,p_item_description => l_Item_rec_in.DESCRIPTION
1503 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1504 ,p_organization_code => l_org_code_rec.ORGANIZATION_CODE );
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 NULL;
1508 END;
1509 --R12: Business Event Enhancement
1510 END IF;
1511 --End 4105841 : Business Event Enhancement
1512
1513 ELSIF ( G_Item_Rec.Transaction_Type = 'UPDATE' ) THEN
1514 -----------------------------------------------------------------------
1515 -- added for bug 7431714
1516 SELECT item_catalog_group_id
1517 INTO l_curr_icc_id
1518 FROM MTL_SYSTEM_ITEMS_B
1519 WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
1520 AND organization_id = l_Item_rec_in.ORGANIZATION_ID;
1521
1522 IF (l_Item_rec_in.ITEM_CATALOG_GROUP_ID <> l_curr_icc_id
1523 AND l_Item_rec_in.ITEM_CATALOG_GROUP_ID <> G_MISS_NUM) THEN -- added condition for the bug 7422745
1524 -- Deleteing records for AGs that are not associated to the new ICC
1525 DELETE
1526 FROM ego_mtl_sy_items_ext_b
1527 WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
1528 AND attr_group_id NOT IN
1529 (SELECT ATTR_GROUP_ID
1530 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V AGV,
1531 FND_OBJECTS FO
1532 WHERE AGV.OBJECT_ID = FO.OBJECT_ID
1533 AND AGV.OBJECT_NAME ='EGO_ITEM'
1534 AND AGV.CLASSIFICATION_CODE IS NOT NULL
1535 AND AGV.CLASSIFICATION_CODE IN
1536 (SELECT TO_CHAR(item_catalog_group_id)
1537 FROM mtl_item_catalog_groups_b CONNECT BY prior parent_catalog_group_id = item_catalog_group_id
1538 START WITH item_catalog_group_id = l_Item_rec_in.ITEM_CATALOG_GROUP_ID
1539 )
1540 UNION ALL
1541 SELECT ATTR_GROUP_ID
1542 FROM EGO_ATTR_GROUPS_V
1543 WHERE APPLICATION_ID = 431
1544 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
1545 AND (ATTR_GROUP_NAME = 'ItemDetailDesc'
1546 OR ATTR_GROUP_NAME = 'ItemDetailImage')
1547 );
1548
1549 DELETE
1550 FROM ego_mtl_sy_items_ext_tl
1551 WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
1552 AND attr_group_id NOT IN
1553 (SELECT ATTR_GROUP_ID
1554 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V AGV,
1555 FND_OBJECTS FO
1556 WHERE AGV.OBJECT_ID = FO.OBJECT_ID
1557 AND AGV.OBJECT_NAME ='EGO_ITEM'
1558 AND AGV.CLASSIFICATION_CODE IS NOT NULL
1559 AND AGV.CLASSIFICATION_CODE IN
1560 (SELECT TO_CHAR(item_catalog_group_id)
1561 FROM mtl_item_catalog_groups_b CONNECT BY prior parent_catalog_group_id = item_catalog_group_id
1562 START WITH item_catalog_group_id = l_Item_rec_in.ITEM_CATALOG_GROUP_ID
1563 )
1564 UNION ALL
1565 SELECT ATTR_GROUP_ID
1566 FROM EGO_ATTR_GROUPS_V
1567 WHERE APPLICATION_ID = 431
1568 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
1569 AND (ATTR_GROUP_NAME = 'ItemDetailDesc'
1570 OR ATTR_GROUP_NAME = 'ItemDetailImage')
1571 );
1572 l_icc_change_flag:= TRUE;
1573 END IF;
1574 -- end adding for bug 7431714
1575
1576 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) THEN
1577 l_cat_tab_index := 1 ;
1578 FOR DEFAULT_CAT_ASSIGN_REC IN DEFAULT_CAT_ASSIGN_CREATE(CP_ITEM_ID => l_Item_rec_in.INVENTORY_ITEM_ID ,CP_ORG_ID => l_Item_rec_in.ORGANIZATION_ID)
1579 LOOP
1580 l_cat_assign_rec_table_bef(l_cat_tab_index).CATEGORY_SET_ID := DEFAULT_CAT_ASSIGN_REC.CATEGORY_SET_ID;
1581 l_cat_assign_rec_table_bef(l_cat_tab_index).CATEGORY_ID := DEFAULT_CAT_ASSIGN_REC.CATEGORY_ID;
1582 l_cat_assign_rec_table_bef(l_cat_tab_index).INVENTORY_ITEM_ID := l_Item_rec_in.INVENTORY_ITEM_ID;
1583 l_cat_assign_rec_table_bef(l_cat_tab_index).ORGANIZATION_ID := l_Item_rec_in.ORGANIZATION_ID;
1584 l_cat_tab_index := l_cat_tab_index + 1 ;
1585 END LOOP;
1586 END IF;
1587 ---------------------------------------------------------------------------
1588 INV_Item_GRP.Update_Item
1589 (
1590 p_commit => p_commit
1591 , p_Item_rec => l_Item_rec_in
1592 , p_Revision_rec => l_revision_rec
1593 , p_Template_Id => l_Template_Id
1594 , p_Template_Name => l_Template_Name
1595 , x_Item_rec => l_Item_rec_out
1596 , x_return_status => l_return_status
1597 , x_Error_tbl => l_Error_tbl
1598 );
1599
1600 --Start 4105841 : Business Event Enhancement
1601 IF ( l_return_status = G_RET_STS_SUCCESS
1602 and G_Item_Rec.Process_Item_Record = 1) THEN
1603 OPEN c_get_org_code(cp_org_id => l_Item_rec_in.ORGANIZATION_ID);
1604 FETCH c_get_org_code INTO l_org_code_rec;
1605 CLOSE c_get_org_code;
1606
1607 --Bug 4586769 Fetch Item Description and number if NULL
1608 IF ( trim(l_item_rec_in.DESCRIPTION) IS NULL
1609 OR l_item_rec_in.DESCRIPTION = G_MISS_CHAR
1610 OR trim(l_Item_rec_in.ITEM_NUMBER) IS NULL) THEN
1611 SELECT CONCATENATED_SEGMENTS, DESCRIPTION
1612 INTO l_item_number, l_item_desc
1613 FROM MTL_SYSTEM_ITEMS_KFV
1614 WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
1615 AND organization_id = l_Item_rec_in.ORGANIZATION_ID;
1616 ELSE
1617 l_item_desc := l_item_rec_in.DESCRIPTION;
1618 l_item_number := l_item_rec_in.ITEM_NUMBER;
1619 END IF;
1620 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) THEN --Bug: 4881908
1621 -------------------------------------------------------------------------
1622 -- Implicit Business Event for Cat assignments on Update
1623 -------------------------------------------------------------------------
1624 l_cat_tab_index := 1 ;
1625 FOR DEFAULT_CAT_ASSIGN_REC IN DEFAULT_CAT_ASSIGN_UPDATE(CP_ITEM_ID => l_Item_rec_out.INVENTORY_ITEM_ID ,CP_ORG_ID => l_Item_rec_out.ORGANIZATION_ID)
1626 LOOP
1627 l_cat_match := FND_API.G_FALSE;
1628 l_cat_assign_rec_table_aft(l_cat_tab_index).CATEGORY_SET_ID := DEFAULT_CAT_ASSIGN_REC.CATEGORY_SET_ID;
1629 l_cat_assign_rec_table_aft(l_cat_tab_index).CATEGORY_ID := DEFAULT_CAT_ASSIGN_REC.CATEGORY_ID;
1630 l_cat_assign_rec_table_aft(l_cat_tab_index).INVENTORY_ITEM_ID := l_Item_rec_out.INVENTORY_ITEM_ID;
1631 l_cat_assign_rec_table_aft(l_cat_tab_index).ORGANIZATION_ID := l_Item_rec_in.ORGANIZATION_ID;
1632 IF l_cat_assign_rec_table_bef IS NOT NULL AND l_cat_assign_rec_table_bef.FIRST IS NOT NULL THEN
1633 FOR CAT_REC_BEF IN l_cat_assign_rec_table_bef.FIRST .. l_cat_assign_rec_table_bef.LAST
1634 LOOP
1635 IF l_cat_assign_rec_table_aft(l_cat_tab_index).CATEGORY_SET_ID = l_cat_assign_rec_table_bef(CAT_REC_BEF).CATEGORY_SET_ID THEN
1636 l_cat_match := FND_API.G_TRUE;
1637 ELSE
1638 NULL;
1639 END IF;
1640 END LOOP;
1641 END IF;
1642 IF l_cat_match = FND_API.G_FALSE THEN
1643 EGO_WF_WRAPPER_PVT.Raise_Item_Event(
1644 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT
1645 ,p_inventory_item_id => l_cat_assign_rec_table_aft(l_cat_tab_index).INVENTORY_ITEM_ID
1646 ,p_organization_id => l_cat_assign_rec_table_aft(l_cat_tab_index).ORGANIZATION_ID
1647 ,p_catalog_id => TO_CHAR(l_cat_assign_rec_table_aft(l_cat_tab_index).CATEGORY_SET_ID)
1648 ,p_category_id => TO_CHAR(l_cat_assign_rec_table_aft(l_cat_tab_index).CATEGORY_ID)
1649 ,x_msg_data => l_msg_data
1650 ,x_return_status => l_event_return_status);
1651 END IF ;
1652 l_cat_tab_index := l_cat_tab_index + 1 ;
1653 END LOOP;
1654 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
1655 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
1656 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1657 ,p_organization_code => l_org_code_rec.ORGANIZATION_CODE
1658 ,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
1659 ,p_item_number => l_item_number
1660 ,p_item_description => l_item_desc
1661 ,x_msg_data => l_msg_data
1662 ,x_return_status => l_event_return_status);
1663 -------------------------------------------------------------------------
1664 BEGIN
1665 SELECT REVISION_ID INTO l_revision_id_out
1666 FROM MTL_ITEM_REVISIONS_B
1667 WHERE INVENTORY_ITEM_ID = l_Item_rec_out.INVENTORY_ITEM_ID
1668 AND ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID
1669 AND REVISION = l_revision_rec.Revision_Code ;
1670 EXCEPTION
1671 WHEN NO_DATA_FOUND
1672 THEN
1673 l_revision_id_out := NULL;
1674 END;
1675 IF l_revision_id_out IS NOT NULL
1676 THEN
1677 EGO_WF_WRAPPER_PVT.Raise_Item_Event(
1678 p_event_name => EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT
1679 ,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
1680 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1681 ,p_revision_id => l_revision_id_out
1682 ,x_msg_data => l_msg_data
1683 ,x_return_status => l_event_return_status);
1684
1685 END IF;
1686 END IF;
1687 /*Removed the call for updates to child orgs
1688 Will be raising events for explicit actions only*/
1689
1690 --Call ICX APIs
1691 BEGIN
1692 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
1693 p_entity_type => 'ITEM'
1694 ,p_dml_type => 'UPDATE'
1695 ,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
1696 ,p_item_number => l_item_number
1697 ,p_item_description => l_item_desc
1698 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1699 ,p_organization_code => l_org_code_rec.ORGANIZATION_CODE );
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 NULL;
1703 END;
1704 --R12: Business Event Enhancement
1705 END IF;
1706 --End 4105841 : Business Event Enhancement
1707 --- added for bug 7431714
1708 IF (l_icc_change_flag) THEN
1709 EGO_ITEM_PUB.Update_Item_Attr_Ext(
1710 P_API_VERSION => 1.0,
1711 P_INIT_MSG_LIST => l_init_msg_list,
1712 P_COMMIT => P_COMMIT,
1713 P_INVENTORY_ITEM_ID => l_Item_rec_in.INVENTORY_ITEM_ID,
1714 P_ITEM_CATALOG_GROUP_ID => l_Item_rec_in.ITEM_CATALOG_GROUP_ID,
1715 x_return_status => l_return_status,
1716 X_MSG_COUNT => l_msg_count);
1717
1718 END IF;
1719 -- end adding for bug 7431714
1720 END IF;
1721
1722 -----------------------------------------------------------------------------
1723 -- Re-populate item record back to the global table.
1724 -----------------------------------------------------------------------------
1725
1726 IF l_process_control = 'EGO_INTERFACE_HANDLER' THEN
1727 G_Item_Tbl(G_Item_indx).Inventory_Item_Id := l_batch_id;
1728 ELSIF G_Item_Rec.Process_Item_Record = 1 THEN
1729 G_Item_Tbl(G_Item_indx).Inventory_Item_Id := l_Item_rec_out.INVENTORY_ITEM_ID;
1730 G_Item_Tbl(G_Item_indx).Organization_Id := l_Item_rec_out.ORGANIZATION_ID;
1731 G_Item_Tbl(G_Item_indx).Item_Catalog_Group_Id := l_Item_rec_out.ITEM_CATALOG_GROUP_ID;
1732 G_Item_Tbl(G_Item_indx).Description := l_Item_rec_out.DESCRIPTION;
1733 G_Item_Tbl(G_Item_indx).Long_Description := l_Item_rec_out.LONG_DESCRIPTION;
1734 G_Item_Tbl(G_Item_indx).Primary_Uom_Code := l_Item_rec_out.PRIMARY_UOM_CODE;
1735 G_Item_Tbl(G_Item_indx).Allowed_Units_Lookup_Code := l_Item_rec_out.ALLOWED_UNITS_LOOKUP_CODE;
1736 G_Item_Tbl(G_Item_indx).Inventory_Item_Status_Code := l_Item_rec_out.INVENTORY_ITEM_STATUS_CODE;
1737 G_Item_Tbl(G_Item_indx).Bom_Enabled_Flag := l_Item_rec_out.BOM_ENABLED_FLAG;
1738 G_Item_Tbl(G_Item_indx).Eng_Item_Flag := l_Item_rec_out.ENG_ITEM_FLAG;
1739
1740 END IF;
1741
1742 G_Item_Tbl(G_Item_indx).Return_Status := l_return_status;
1743
1744
1745 IF ( l_return_status <> G_RET_STS_SUCCESS ) THEN
1746 x_return_status := l_return_status;
1747 END IF;
1748
1749 l_msg_count := l_msg_count + NVL(l_Error_tbl.COUNT, 0);
1750 x_msg_count := l_msg_count;
1751
1752 -----------------------------------------------------------------------------
1753 -- Populate the item error message table.
1754 -----------------------------------------------------------------------------
1755
1756 FOR errno IN 1 .. NVL(l_Error_tbl.LAST, 0) LOOP
1757
1758 IF ( l_Error_tbl(errno).MESSAGE_TEXT IS NOT NULL ) THEN
1759 EGO_Item_Msg.Add_Error_Text (G_Item_indx, l_Error_tbl(errno).MESSAGE_TEXT);
1760 ELSE
1761 EGO_Item_Msg.Add_Error_Message (G_Item_indx, 'INV', l_Error_tbl(errno).MESSAGE_NAME);
1762 END IF;
1763
1764 END LOOP; -- l_Error_tbl
1765
1766 -----------------------------------------------------------------------------
1767 -- Assign the next value of the global index variable of the global table.
1768 -----------------------------------------------------------------------------
1769
1770 G_Item_indx := G_Item_indx + 1;
1771
1772 END LOOP; -- G_Item_Tbl
1773
1774 EXCEPTION
1775 WHEN others THEN
1776 x_return_status := G_RET_STS_UNEXP_ERROR;
1777 EGO_Item_Msg.Add_Error_Message ( G_Item_indx, 'INV', 'INV_ITEM_UNEXPECTED_ERROR',
1778 'PACKAGE_NAME', G_PKG_NAME, FALSE,
1779 'PROCEDURE_NAME', l_api_name, FALSE,
1780 'ERROR_TEXT', SQLERRM, FALSE );
1781 END Process_Items;
1782
1783
1784 -- -----------------------------------------------------------------------------
1785 -- API Name: Process_Item_Org_Assignments
1786 -- -----------------------------------------------------------------------------
1787
1788 PROCEDURE Process_Item_Org_Assignments
1789 (
1790 p_commit IN VARCHAR2 DEFAULT FND_API.g_FALSE
1791 , x_return_status OUT NOCOPY VARCHAR2
1792 , x_msg_count OUT NOCOPY NUMBER
1793 )
1794 IS
1795 CURSOR c_get_item_rev_rec (cp_item_id NUMBER
1796 ,cp_org_id NUMBER) IS
1797 SELECT item.concatenated_segments
1798 ,item.description
1799 ,item.organization_id
1800 ,rev.revision_id
1801 FROM mtl_system_items_b_kfv item
1802 ,mtl_item_revisions_b rev
1803 WHERE item.inventory_item_id = cp_item_id
1804 AND item.organization_id = cp_org_id
1805 AND rev.inventory_item_id = cp_item_id
1806 AND rev.organization_id = cp_org_id;
1807
1808 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Org_Assignments';
1809 l_return_status VARCHAR2(1) := G_MISS_CHAR;
1810 l_msg_count NUMBER := 0;
1811 l_Item_Org_Assignment_Rec EGO_Item_PUB.Item_Org_Assignment_Rec_Type;
1812 l_Item_rec_in INV_ITEM_GRP.Item_Rec_Type;
1813 l_revision_rec INV_ITEM_GRP.Item_Revision_Rec_Type;
1814 l_rev_index_failure BOOLEAN := FALSE;
1815 l_Item_rec_out INV_ITEM_GRP.Item_Rec_Type;
1816 l_Error_tbl INV_ITEM_GRP.Error_Tbl_Type;
1817 l_master_org INV.MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
1818 l_event_return_status VARCHAR2(1) ; --business event enhancement
1819 l_item_rev_rec c_get_item_rev_rec%ROWTYPE;
1820 l_msg_data VARCHAR2(2000);
1821 l_process_control VARCHAR2(2000) := INV_EGO_REVISION_VALIDATE.Get_Process_Control; --Bug: 4881908
1822
1823 BEGIN
1824 x_return_status := G_RET_STS_SUCCESS;
1825
1826 -- Loop through item records in the global table
1827 G_Item_Org_indx := G_Item_Org_Assignment_Tbl.FIRST;
1828
1829 WHILE G_Item_Org_indx <= G_Item_Org_Assignment_Tbl.LAST LOOP
1830
1831 -- Clear the Item GRP API message table before processing an item
1832 l_Error_tbl.DELETE;
1833
1834 l_Item_Org_Assignment_Rec := G_Item_Org_Assignment_Tbl(G_Item_Org_indx);
1835
1836 -- Item identifier
1837 l_Item_rec_in.INVENTORY_ITEM_ID := l_Item_Org_Assignment_Rec.Inventory_Item_Id;
1838 l_Item_rec_in.ITEM_NUMBER := l_Item_Org_Assignment_Rec.Item_Number;
1839 -- Organization
1840 l_Item_rec_in.ORGANIZATION_ID := l_Item_Org_Assignment_Rec.Organization_Id;
1841 l_Item_rec_in.ORGANIZATION_CODE := l_Item_Org_Assignment_Rec.Organization_Code;
1842 --Fix for Bug# 2768532
1843 l_Item_rec_in.PRIMARY_UOM_CODE := l_Item_Org_Assignment_Rec.Primary_Uom_Code;
1844
1845 --Start : Added revision record processing
1846 BEGIN
1847 IF NOT l_rev_index_failure THEN
1848 l_revision_rec.Transaction_Type := G_Revision_Tbl(G_Item_indx).Transaction_Type;
1849 l_revision_rec.Inventory_Item_Id := G_Revision_Tbl(G_Item_indx).Inventory_Item_Id;
1850 l_revision_rec.Item_Number := G_Revision_Tbl(G_Item_indx).Item_Number;
1851 l_revision_rec.Organization_Id := G_Revision_Tbl(G_Item_indx).Organization_Id;
1852 l_revision_rec.Revision_Id := G_Revision_Tbl(G_Item_indx).Revision_Id;
1853 l_revision_rec.Revision_Code := G_Revision_Tbl(G_Item_indx).Revision_Code;
1854 l_revision_rec.Revision_Label := G_Revision_Tbl(G_Item_indx).Revision_Label;
1855 l_revision_rec.Description := G_Revision_Tbl(G_Item_indx).Description;
1856 l_revision_rec.Effectivity_Date := G_Revision_Tbl(G_Item_indx).Effectivity_Date;
1857 l_revision_rec.Lifecycle_Id := G_Revision_Tbl(G_Item_indx).Lifecycle_Id;
1858 l_revision_rec.Current_Phase_Id := G_Revision_Tbl(G_Item_indx).Current_Phase_Id;
1859 -- 5208102: Supporting template for UDA's at revisions
1860 l_revision_rec.template_Id := G_Revision_Tbl(G_Item_indx).Template_Id;
1861 l_revision_rec.template_Name := G_Revision_Tbl(G_Item_indx).Template_Name;
1862
1863 l_revision_rec.Attribute_Category := G_Revision_Tbl(G_Item_indx).Attribute_Category;
1864 l_revision_rec.Attribute1 := G_Revision_Tbl(G_Item_indx).Attribute1;
1865 l_revision_rec.Attribute2 := G_Revision_Tbl(G_Item_indx).Attribute2;
1866 l_revision_rec.Attribute3 := G_Revision_Tbl(G_Item_indx).Attribute3;
1867 l_revision_rec.Attribute4 := G_Revision_Tbl(G_Item_indx).Attribute4;
1868 l_revision_rec.Attribute5 := G_Revision_Tbl(G_Item_indx).Attribute5;
1869 l_revision_rec.Attribute6 := G_Revision_Tbl(G_Item_indx).Attribute6;
1870 l_revision_rec.Attribute7 := G_Revision_Tbl(G_Item_indx).Attribute7;
1871 l_revision_rec.Attribute8 := G_Revision_Tbl(G_Item_indx).Attribute8;
1872 l_revision_rec.Attribute9 := G_Revision_Tbl(G_Item_indx).Attribute9;
1873 l_revision_rec.Attribute10 := G_Revision_Tbl(G_Item_indx).Attribute10;
1874 l_revision_rec.Attribute11 := G_Revision_Tbl(G_Item_indx).Attribute11;
1875 l_revision_rec.Attribute12 := G_Revision_Tbl(G_Item_indx).Attribute12;
1876 l_revision_rec.Attribute13 := G_Revision_Tbl(G_Item_indx).Attribute13;
1877 l_revision_rec.Attribute14 := G_Revision_Tbl(G_Item_indx).Attribute14;
1878 l_revision_rec.Attribute15 := G_Revision_Tbl(G_Item_indx).Attribute15;
1879 ELSE
1880 l_revision_rec := NULL;
1881 END IF;
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 l_revision_rec := NULL;
1885 l_rev_index_failure := TRUE;
1886 -- Item and Item Rev should ideally be have same
1887 -- index and same number of pl/sql records.
1888 END;
1889 --End : Added revision record processing
1890
1891
1892 INV_ITEM_GRP.Create_Item
1893 (
1894 p_commit => p_commit
1895 , p_Item_rec => l_Item_rec_in
1896 , p_Revision_rec => l_revision_rec
1897 , p_Template_Id => NULL
1898 , p_Template_Name => NULL
1899 , x_Item_rec => l_Item_rec_out
1900 , x_return_status => l_return_status
1901 , x_Error_tbl => l_Error_tbl
1902 );
1903
1904
1905
1906 G_Item_Org_Assignment_Tbl(G_Item_Org_indx).Return_Status := l_return_status;
1907
1908 IF ( l_return_status <> G_RET_STS_SUCCESS ) THEN
1909 x_return_status := l_return_status;
1910 /*Fix for Bug 4013187*/
1911 ELSE /*Copy the Description into TL tables for all the languages supported*/
1912 SELECT MASTER_ORGANIZATION_ID INTO l_master_org
1913 FROM mtl_parameters
1914 WHERE ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID;
1915
1916 UPDATE MTL_SYSTEM_ITEMS_TL ASSIGNEE
1917 SET (ASSIGNEE.DESCRIPTION, ASSIGNEE.LONG_DESCRIPTION, ASSIGNEE.SOURCE_LANG)
1918 = (SELECT DESCRIPTION, LONG_DESCRIPTION, SOURCE_LANG
1919 FROM MTL_SYSTEM_ITEMS_TL MASTER
1920 WHERE INVENTORY_ITEM_ID = l_Item_rec_in.INVENTORY_ITEM_ID
1921 AND ORGANIZATION_ID =L_MASTER_ORG
1922 AND MASTER."LANGUAGE" = ASSIGNEE."LANGUAGE")
1923 WHERE INVENTORY_ITEM_ID = l_Item_rec_in.INVENTORY_ITEM_ID
1924 AND ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID;
1925
1926 --Start 4105841 : Business Event Enhancement
1927 IF ( l_return_status = G_RET_STS_SUCCESS ) THEN
1928 OPEN c_get_item_rev_rec (cp_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
1929 ,cp_org_id => l_Item_rec_in.ORGANIZATION_ID);
1930
1931 FETCH c_get_item_rev_rec INTO l_item_rev_rec;
1932 CLOSE c_get_item_rev_rec;
1933
1934 IF (INSTR(l_process_control,'PLM_UI:Y') = 0) THEN --Bug: 4881908
1935 EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
1936 p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT
1937 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1938 ,p_organization_code => l_Item_rec_in.ORGANIZATION_CODE
1939 ,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
1940 ,p_item_number => l_item_rev_rec.concatenated_segments
1941 ,p_item_description => l_item_rev_rec.description
1942 ,x_msg_data => l_msg_data
1943 ,x_return_status => l_event_return_status);
1944 END IF;
1945 /*Removed the call for default Revision creation
1946 Will be raising events for explicit actions only*/
1947 --Call ICX APIs
1948 BEGIN
1949 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
1950 p_entity_type => 'ITEM'
1951 ,p_dml_type => 'CREATE'
1952 ,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
1953 ,p_item_number => l_item_rev_rec.concatenated_segments
1954 ,p_item_description => l_item_rev_rec.description
1955 ,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
1956 ,p_organization_code => l_Item_rec_in.ORGANIZATION_CODE );
1957 EXCEPTION
1958 WHEN OTHERS THEN
1959 NULL;
1960 END;
1961 --R12: Business Event Enhancement
1962
1963 --End 4105841 : Business Event Enhancement
1964 END IF;
1965 END IF;
1966
1967 l_msg_count := l_msg_count + NVL(l_Error_tbl.COUNT, 0);
1968 x_msg_count := l_msg_count;
1969
1970 FOR errno IN 1 .. NVL(l_Error_tbl.LAST, 0) LOOP
1971
1972 IF ( l_Error_tbl(errno).MESSAGE_TEXT IS NOT NULL ) THEN
1973 EGO_Item_Msg.Add_Error_Text (G_Item_Org_indx, l_Error_tbl(errno).MESSAGE_TEXT);
1974 ELSE
1975 EGO_Item_Msg.Add_Error_Message (G_Item_Org_indx, 'INV', l_Error_tbl(errno).MESSAGE_NAME);
1976 END IF;
1977
1978 END LOOP; -- l_Error_tbl
1979
1980 G_Item_Org_indx := G_Item_Org_indx + 1;
1981
1982 END LOOP; -- G_Item_Org_Assignment_Tbl
1983
1984
1985 EXCEPTION
1986
1987 WHEN others THEN
1988 x_return_status := G_RET_STS_UNEXP_ERROR;
1989 EGO_Item_Msg.Add_Error_Message ( G_Item_Org_indx, 'INV', 'INV_ITEM_UNEXPECTED_ERROR',
1990 'PACKAGE_NAME', G_PKG_NAME, FALSE,
1991 'PROCEDURE_NAME', l_api_name, FALSE,
1992 'ERROR_TEXT', SQLERRM, FALSE );
1993
1994 END Process_Item_Org_Assignments;
1995
1996 -- -----------------------------------------------------------------------------
1997 -- API Name: Seed_Item_Long_Desc_Attr_Group
1998 --
1999 -- Description:
2000 -- Add a row to the User-Defined Attribute Group 'Detailed Descriptions'
2001 -- so that the Item Long Description is shown on the Item Detail page.
2002 -- -----------------------------------------------------------------------------
2003
2004 PROCEDURE Seed_Item_Long_Desc_Attr_Group (
2005 p_inventory_item_id IN NUMBER
2006 ,p_organization_id IN NUMBER
2007 ,p_item_catalog_group_id IN NUMBER
2008 ,x_return_status OUT NOCOPY VARCHAR2
2009 ,x_errorcode OUT NOCOPY NUMBER
2010 ,x_msg_count OUT NOCOPY NUMBER
2011 ,x_msg_data OUT NOCOPY VARCHAR2
2012 ) IS
2013
2014 l_api_name CONSTANT VARCHAR2(30) := 'Seed_Item_Long_Desc_Attr_Group';
2015
2016 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2017 l_class_code_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2018 l_label VARCHAR2(80);
2019 l_attr_value_info_table EGO_USER_ATTR_DATA_TABLE;
2020
2021 BEGIN
2022
2023 l_pk_column_values := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2024 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_inventory_item_id)
2025 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id)
2026 );
2027
2028 l_class_code_values := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2029 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', NVL(p_item_catalog_group_id, -1))
2030 );
2031
2032 SELECT DISPLAY_NAME
2033 INTO l_label
2034 FROM EGO_VALUE_SET_VALUES_V
2035 WHERE VALUE_SET_NAME = 'DescSource'
2036 AND INTERNAL_NAME = 'D';
2037
2038 l_attr_value_info_table := EGO_USER_ATTR_DATA_TABLE(
2039 EGO_USER_ATTR_DATA_OBJ(1, 'Sequence', null, 10, null, null, null, null)
2040 ,EGO_USER_ATTR_DATA_OBJ(1, 'DescriptionSource', 'D', null, null, null, null, null)
2041 ,EGO_USER_ATTR_DATA_OBJ(1, 'Label', l_label, null, null, null, null, null)
2042 ,EGO_USER_ATTR_DATA_OBJ(1, 'DisplayMode', 'AsText', null, null, null, null, null)
2043 );
2044
2045 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
2046 p_api_version => 1.0
2047 ,p_object_name => 'EGO_ITEM'
2048 ,p_application_id => 431
2049 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP'
2050 ,p_attr_group_name => 'ItemDetailDesc'
2051 ,p_pk_column_name_value_pairs => l_pk_column_values
2052 ,p_class_code_name_value_pairs => l_class_code_values
2053 ,p_data_level_name_value_pairs => NULL
2054 ,p_attr_name_value_pairs => l_attr_value_info_table
2055 ,x_return_status => x_return_status
2056 ,x_errorcode => x_errorcode
2057 ,x_msg_count => x_msg_count
2058 ,x_msg_data => x_msg_data
2059 );
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
2065
2066 END Seed_Item_Long_Desc_Attr_Group;
2067
2068 -- -----------------------------------------------------------------------------
2069 -- API Name: Seed_Item_Long_Desc_In_Bulk
2070 --
2071 -- Add a row to the User-Defined Attribute Group 'Detailed Descriptions'
2072 -- for all newly created items in the set identified by p_set_process_id
2073 -- -----------------------------------------------------------------------------
2074
2075 PROCEDURE Seed_Item_Long_Desc_In_Bulk (
2076 p_set_process_id IN NUMBER
2077 ,x_return_status OUT NOCOPY VARCHAR2
2078 ,x_msg_data OUT NOCOPY VARCHAR2
2079 ) IS
2080
2081 l_api_name CONSTANT VARCHAR2(30) := 'Seed_Item_Long_Desc_In_Bulk';
2082 l_attr_group_id NUMBER;
2083 --commented out as a part of Bug 4906499
2084 --l_label VARCHAR2(80);
2085 BEGIN
2086
2087 -----------------------------------------------------------
2088 -- Find the Attr Group ID and Label we will be inserting --
2089 -----------------------------------------------------------
2090 SELECT ATTR_GROUP_ID
2091 INTO l_attr_group_id
2092 FROM EGO_FND_DSC_FLX_CTX_EXT
2093 WHERE APPLICATION_ID = 431
2094 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
2095 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc';
2096 --commented out as a part of Bug 4906499
2097 /*SELECT DISPLAY_NAME
2098 INTO l_label
2099 FROM EGO_VALUE_SET_VALUES_V
2100 WHERE VALUE_SET_NAME = 'DescSource'
2101 AND INTERNAL_NAME = 'D';*/
2102
2103 ----------------------------------------------------------------------
2104 -- Insert a row for every newly created item in the interface table --
2105 ----------------------------------------------------------------------
2106 INSERT INTO EGO_MTL_SY_ITEMS_EXT_B
2107 (
2108 EXTENSION_ID
2109 ,ORGANIZATION_ID
2110 ,INVENTORY_ITEM_ID
2111 ,ITEM_CATALOG_GROUP_ID
2112 ,ATTR_GROUP_ID
2113 ,CREATED_BY
2114 ,CREATION_DATE
2115 ,LAST_UPDATED_BY
2116 ,LAST_UPDATE_DATE
2117 ,N_EXT_ATTR1
2118 ,C_EXT_ATTR1
2119 -- ,C_EXT_ATTR2 -- commented out as a part of Bug 4906499
2120 ,C_EXT_ATTR4
2121 ,DATA_LEVEL_ID --Added for bug 6155995
2122 )
2123 SELECT EGO_EXTFWK_S.NEXTVAL
2124 ,MTL.ORGANIZATION_ID
2125 ,MTL.INVENTORY_ITEM_ID
2126 ,NVL(MTL.ITEM_CATALOG_GROUP_ID, -1)
2127 ,l_attr_group_id
2128 ,1
2129 ,SYSDATE
2130 ,1
2131 ,SYSDATE
2132 ,10
2133 ,'D'
2134 -- ,l_label --commented out as a part of Bug 4906499
2135 ,'AsText'
2136 ,43102 --Added for bug 6155995
2137 FROM MTL_SYSTEM_ITEMS_INTERFACE MTL
2138 WHERE MTL.SET_PROCESS_ID = p_set_process_id
2139 AND MTL.PROCESS_FLAG = 4
2140 AND MTL.TRANSACTION_TYPE = 'CREATE';
2141
2142 -----------------------------------------------------------------------------
2143 -- Insert corresponding rows in the TL table for each B table row we added --
2144 -----------------------------------------------------------------------------
2145 INSERT INTO EGO_MTL_SY_ITEMS_EXT_TL
2146 (
2147 EXTENSION_ID
2148 ,ORGANIZATION_ID
2149 ,INVENTORY_ITEM_ID
2150 ,ITEM_CATALOG_GROUP_ID
2151 ,ATTR_GROUP_ID
2152 ,SOURCE_LANG
2153 ,LANGUAGE
2154 ,CREATED_BY
2155 ,CREATION_DATE
2156 ,LAST_UPDATED_BY
2157 ,LAST_UPDATE_DATE
2158 ,TL_EXT_ATTR2 -- Added as a part of Bug 4906499
2159 ,DATA_LEVEL_ID --Added for bug 6155995
2160 )
2161 SELECT EXT.EXTENSION_ID
2162 ,EXT.ORGANIZATION_ID
2163 ,EXT.INVENTORY_ITEM_ID
2164 ,EXT.ITEM_CATALOG_GROUP_ID
2165 ,EXT.ATTR_GROUP_ID
2166 ,USERENV('LANG')
2167 ,L.LANGUAGE_CODE
2168 ,EXT.CREATED_BY
2169 ,EXT.CREATION_DATE
2170 ,EXT.LAST_UPDATED_BY
2171 ,EXT.LAST_UPDATE_DATE
2172 ,NVL(M.MESSAGE_TEXT,'Long Description')
2173 ,43102 --Added for bug 6155995
2174 FROM MTL_SYSTEM_ITEMS_INTERFACE MTL
2175 ,EGO_MTL_SY_ITEMS_EXT_B EXT
2176 ,FND_LANGUAGES L
2177 ,FND_NEW_MESSAGES M
2178 WHERE MTL.SET_PROCESS_ID = p_set_process_id
2179 AND MTL.PROCESS_FLAG = 4
2180 AND MTL.TRANSACTION_TYPE = 'CREATE'
2181 AND MTL.ORGANIZATION_ID = EXT.ORGANIZATION_ID
2182 AND MTL.INVENTORY_ITEM_ID = EXT.INVENTORY_ITEM_ID
2183 AND EXT.ATTR_GROUP_ID = l_attr_group_id
2184 AND L.INSTALLED_FLAG IN ('I', 'B')
2185 AND MESSAGE_NAME = 'EGO_ITEM_LONG_DESCRIPTION'
2186 AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
2187 AND M.APPLICATION_ID = 431;
2188
2189 x_return_status := FND_API.G_RET_STS_SUCCESS;
2190
2191 EXCEPTION
2192 WHEN OTHERS THEN
2193
2194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2195 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
2196
2197 END Seed_Item_Long_Desc_In_Bulk;
2198
2199 -- -----------------------------------------------------------------------------
2200 -- API Name: Build_Parent_Cat_Group_List
2201 --
2202 -- Description:
2203 -- This Function is copied from EGO_ITEM_USER_ATTRS_CP_PUB for Installation bug
2204 -- 3873647.
2205 -- Gets the parent catalog group/categories list for a given catalog group/category code
2206 -- -----------------------------------------------------------------------------
2207
2208 FUNCTION Build_Parent_Cat_Group_List (
2209 p_catalog_group_id IN NUMBER
2210 ,p_entity_id IN NUMBER DEFAULT NULL
2211 ,p_entity_index IN NUMBER DEFAULT NULL
2212 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2213 )
2214 RETURN VARCHAR2
2215 IS
2216
2217 l_parent_cat_group_list VARCHAR2(150) := '';
2218 l_token_table ERROR_HANDLER.Token_Tbl_Type;
2219 l_dummy_transaction_id NUMBER;
2220
2221 -------------------------------------------------------------------------
2222 -- For finding all parent catalog groups for the current catalog group --
2223 -------------------------------------------------------------------------
2224 CURSOR parent_catalog_group_cursor
2225 IS
2226 SELECT ITEM_CATALOG_GROUP_ID
2227 ,PARENT_CATALOG_GROUP_ID
2228 FROM MTL_ITEM_CATALOG_GROUPS_B
2229 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
2230 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
2231
2232 BEGIN
2233
2234 -------------------------------------------------------------------
2235 -- We build a list of all parent catalog groups, as long as the --
2236 -- list is less than 151 characters long (the longest we can fit --
2237 -- into the EGO_COL_NAME_VALUE_PAIR_OBJ is 150 chars); if the --
2238 -- list is too long to fully copy, we can only hope that the --
2239 -- portion we copied will contain all the information we need. --
2240 -------------------------------------------------------------------
2241 FOR cat_rec IN parent_catalog_group_cursor
2242 LOOP
2243 IF (cat_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
2244
2245 l_parent_cat_group_list := l_parent_cat_group_list ||
2246 cat_rec.PARENT_CATALOG_GROUP_ID || ',';
2247 END IF;
2248 END LOOP;
2249
2250 ---------------------------------------------------------------------
2251 -- Trim the trailing ',' from l_parent_cat_group_list if necessary --
2252 ---------------------------------------------------------------------
2253 IF (LENGTH(l_parent_cat_group_list) > 0) THEN
2254 l_parent_cat_group_list := SUBSTR(l_parent_cat_group_list, 1, LENGTH(l_parent_cat_group_list) - LENGTH(','));
2255 END IF;
2256
2257 RETURN l_parent_cat_group_list;
2258
2259 EXCEPTION
2260 WHEN OTHERS THEN
2261
2262 l_token_table(1).TOKEN_NAME := 'CAT_GROUP_NAME';
2263 SELECT CONCATENATED_SEGMENTS
2264 INTO l_token_table(1).TOKEN_VALUE
2265 FROM MTL_ITEM_CATALOG_GROUPS_KFV
2266 WHERE ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
2267
2268 ERROR_HANDLER.Add_Error_Message(
2269 p_message_name => 'EGO_TOO_MANY_CAT_GROUPS'
2270 ,p_application_id => 'EGO'
2271 ,p_token_tbl => l_token_table
2272 ,p_message_type => FND_API.G_RET_STS_ERROR
2273 ,p_entity_id => p_entity_id
2274 ,p_entity_index => p_entity_index
2275 ,p_entity_code => p_entity_code
2276 );
2277
2278 ---------------------------------------------------------------------
2279 -- Trim the trailing ',' from l_parent_cat_group_list if necessary --
2280 ---------------------------------------------------------------------
2281 IF (LENGTH(l_parent_cat_group_list) > 0) THEN
2282 l_parent_cat_group_list := SUBSTR(l_parent_cat_group_list, 1, LENGTH(l_parent_cat_group_list) - LENGTH(','));
2283 END IF;
2284
2285 RETURN l_parent_cat_group_list;
2286
2287 END Build_Parent_Cat_Group_List;
2288
2289 -- -----------------------------------------------------------------------------
2290 -- API Name: Get_Related_Class_Codes
2291 --
2292 -- Description:
2293 -- This Procedure is copied from EGO_ITEM_USER_ATTRS_CP_PUB for Installation bug
2294 -- 3873647.
2295 -- Gets the related classification codes list for a given classification code
2296 -- -----------------------------------------------------------------------------
2297
2298 PROCEDURE Get_Related_Class_Codes (
2299 p_classification_code IN VARCHAR2
2300 ,p_entity_id IN NUMBER DEFAULT NULL
2301 ,p_entity_index IN NUMBER DEFAULT NULL
2302 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2303 ,x_related_class_codes_list OUT NOCOPY VARCHAR2
2304 ) IS
2305
2306 BEGIN
2307
2308 x_related_class_codes_list :=
2309 Build_Parent_Cat_Group_List(TO_NUMBER(p_classification_code), p_entity_id, p_entity_index, p_entity_code);
2310
2311 END Get_Related_Class_Codes;
2312
2313 -- -----------------------------------------------------------------------------
2314 -- API Name: Get_User_Attrs_Privs
2315 --
2316 -- Description:
2317 -- Helper function (private) to build a table of privileges;
2318 -- should never be exposed as a public API. Raises an exception
2319 -- if anything fails.
2320 -- -----------------------------------------------------------------------------
2321 FUNCTION Get_User_Attrs_Privs (
2322 p_inventory_item_id IN NUMBER
2323 ,p_organization_id IN NUMBER
2324 ,p_entity_id IN NUMBER DEFAULT NULL
2325 ,p_entity_index IN NUMBER DEFAULT NULL
2326 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2327 )
2328 RETURN EGO_VARCHAR_TBL_TYPE
2329 IS
2330
2331 l_party_id VARCHAR2(30);
2332 l_return_status VARCHAR2(1);
2333 l_user_privileges_table EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
2334 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
2335 l_privilege_table_index NUMBER;
2336 l_token_table ERROR_HANDLER.Token_Tbl_Type;
2337 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_Privs';
2338 BEGIN
2339
2340 --------------------------------------------------------------------------
2341 -- Get the party ID if we don't already know it --
2342 --------------------------------------------------------------------------
2343
2344 IF L_PARTY_ID IS NULL OR
2345 FND_GLOBAL.USER_NAME <> g_username THEN
2346
2347 --
2348 -- New user - find out its party ID.
2349 --
2350
2351 -------------------------------------------------------------
2352 -- This query assumes that the user is logged in correctly --
2353 -------------------------------------------------------------
2354
2355 BEGIN
2356 SELECT 'HZ_PARTY:'||TO_CHAR(PARTY_ID)
2357 INTO l_party_id
2358 FROM EGO_USER_V
2359 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
2360 EXCEPTION
2361 WHEN NO_DATA_FOUND THEN
2362
2363 ERROR_HANDLER.Add_Error_Message(
2364 p_message_name => 'EGO_EF_NO_NAME_TO_VALIDATE'
2365 ,p_application_id => 'EGO'
2366 ,p_message_type => FND_API.G_RET_STS_ERROR
2367 ,p_entity_id => p_entity_id
2368 ,p_entity_index => p_entity_index
2369 ,p_entity_code => p_entity_code
2370 );
2371
2372 RAISE FND_API.G_EXC_ERROR;
2373
2374 END;
2375
2376 ------------------- BEGIN Bug 6531908 -----------------------------------
2377
2378 code_debug(l_api_name ||
2379 ' Writing username to party ID mapping to cache.' );
2380
2381 -- Cache this most recent username to party ID mapping to avoid
2382 -- redundantly performing this lookup again.
2383 g_party_id := l_party_id;
2384 g_username := FND_GLOBAL.USER_NAME;
2385
2386 ELSE
2387
2388 -- Bug 6531908: The user is the same as previously, so reuse the same
2389 -- party ID as that determined previously.
2390 l_party_id := g_party_id;
2391
2392 code_debug(l_api_name||' Querying cached party ID.' );
2393
2394
2395 END IF;
2396
2397 ---------------------- END Bug 6531908 -----------------------------------
2398
2399
2400 EGO_DATA_SECURITY.Get_Functions(
2401 p_api_version => 1.0
2402 ,p_object_name => 'EGO_ITEM'
2403 ,p_instance_pk1_value => p_inventory_item_id
2404 ,p_instance_pk2_value => p_organization_id
2405 ,p_user_name => l_party_id
2406 ,x_return_status => l_return_status
2407 ,x_privilege_tbl => l_user_privileges_table
2408 );
2409
2410 ---------------------------------------------------------------------
2411 -- If the user has privileges on this instance, we need to convert --
2412 -- the table we have into a table of type EGO_VARCHAR_TBL_TYPE --
2413 ---------------------------------------------------------------------
2414 IF (l_return_status = 'T' AND
2415 l_user_privileges_table.COUNT > 0) THEN
2416
2417 l_user_privileges_on_object := EGO_VARCHAR_TBL_TYPE();
2418
2419 l_privilege_table_index := l_user_privileges_table.FIRST;
2420 WHILE (l_privilege_table_index <= l_user_privileges_table.LAST)
2421 LOOP
2422 l_user_privileges_on_object.EXTEND();
2423 l_user_privileges_on_object(l_user_privileges_on_object.LAST) := l_user_privileges_table(l_privilege_table_index);
2424 l_privilege_table_index := l_user_privileges_table.NEXT(l_privilege_table_index);
2425 END LOOP;
2426
2427 ELSE
2428
2429 -----------------------------------------------
2430 -- If Get_Functions failed, report the error --
2431 -----------------------------------------------
2432 DECLARE
2433
2434 l_error_message_name VARCHAR2(30);
2435 l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
2436 l_item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
2437
2438 BEGIN
2439
2440 IF (l_return_status = 'F') THEN
2441 l_error_message_name := 'EGO_EF_BL_NO_PRIVS_ON_INSTANCE';
2442 ELSE
2443 l_error_message_name := 'EGO_EF_BL_PRIV_CHECK_ERROR';
2444 END IF;
2445
2446 SELECT CONCATENATED_SEGMENTS
2447 INTO l_item_number
2448 FROM MTL_SYSTEM_ITEMS_KFV
2449 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2450 AND ORGANIZATION_ID = p_organization_id;
2451
2452 SELECT ORGANIZATION_CODE
2453 INTO l_org_code
2454 FROM MTL_PARAMETERS
2455 WHERE ORGANIZATION_ID = p_organization_id;
2456
2457 l_token_table(1).TOKEN_NAME := 'USER_NAME';
2458 l_token_table(1).TOKEN_VALUE := FND_GLOBAL.USER_NAME;
2459 l_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
2460 l_token_table(2).TOKEN_VALUE := l_item_number;
2461 l_token_table(3).TOKEN_NAME := 'ORG_CODE';
2462 l_token_table(3).TOKEN_VALUE := l_org_code;
2463
2464 ERROR_HANDLER.Add_Error_Message(
2465 p_message_name => l_error_message_name
2466 ,p_application_id => 'EGO'
2467 ,p_token_tbl => l_token_table
2468 ,p_message_type => FND_API.G_RET_STS_ERROR
2469 ,p_entity_id => p_entity_id
2470 ,p_entity_index => p_entity_index
2471 ,p_entity_code => p_entity_code
2472 );
2473
2474 RAISE FND_API.G_EXC_ERROR;
2475
2476 END;
2477 END IF;
2478
2479 RETURN l_user_privileges_on_object;
2480
2481 END Get_User_Attrs_Privs;
2482
2483 -- -----------------------------------------------------------------------------
2484 -- API Name: Process_User_Attrs_For_Item
2485 --
2486 -- Description:
2487 -- Process passed-in User-Defined Attrs data for
2488 -- the Item whose Primary Keys are passed in
2489 -- -----------------------------------------------------------------------------
2490 PROCEDURE Process_User_Attrs_For_Item (
2491 p_api_version IN NUMBER
2492 ,p_inventory_item_id IN NUMBER
2493 ,p_organization_id IN NUMBER
2494 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
2495 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
2496 ,p_change_info_table IN EGO_USER_ATTR_CHANGE_TABLE DEFAULT NULL
2497 ,p_entity_id IN NUMBER DEFAULT NULL
2498 ,p_entity_index IN NUMBER DEFAULT NULL
2499 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2500 ,p_debug_level IN NUMBER DEFAULT 0
2501 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
2502 ,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
2503 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2504 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_TRUE
2505 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
2506 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2507 ,p_do_policy_check IN VARCHAR2 DEFAULT FND_API.G_TRUE
2508 ,p_validate_hierarchy IN VARCHAR2 DEFAULT FND_API.G_TRUE--Added for bugFix:5275391
2509 ,x_failed_row_id_list OUT NOCOPY VARCHAR2
2510 ,x_return_status OUT NOCOPY VARCHAR2
2511 ,x_errorcode OUT NOCOPY NUMBER
2512 ,x_msg_count OUT NOCOPY NUMBER
2513 ,x_msg_data OUT NOCOPY VARCHAR2
2514 ) IS
2515
2516 l_api_name CONSTANT VARCHAR2(30) := 'Process_User_Attrs_For_Item';
2517
2518 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2519 l_class_code_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2520 l_item_catalog_group_id NUMBER;
2521 l_related_class_codes_list VARCHAR2(150);
2522 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
2523 l_token_table ERROR_HANDLER.Token_Tbl_Type;
2524 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
2525 l_return_status VARCHAR2(2);
2526
2527 BEGIN
2528 l_attributes_row_table:=EGO_USER_ATTR_ROW_TABLE();
2529 -------------------------------------------------------------------------
2530 -- First we build tables of Primary Key and Classification Code values --
2531 -------------------------------------------------------------------------
2532
2533 -----------------------
2534 -- Get PKs organized --
2535 -----------------------
2536 l_pk_column_values :=
2537 EGO_COL_NAME_VALUE_PAIR_ARRAY(
2538 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', TO_CHAR(p_inventory_item_id))
2539 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', TO_CHAR(p_organization_id))
2540 );
2541
2542 -----------------------------------------------------------------
2543 -- Get the Item Catalog Group ID as well as a comma-delimited --
2544 -- list of all parent Catalog Group IDs, which we then pass as --
2545 -- the second element in this array (Item Catalog Group ID is --
2546 -- Item's Classification Code) --
2547 -----------------------------------------------------------------
2548 -- Bug: 5636895 added NVL(ITEM...., -1)
2549 BEGIN
2550 SELECT NVL(ITEM_CATALOG_GROUP_ID, -1)
2551 INTO l_item_catalog_group_id
2552 FROM MTL_SYSTEM_ITEMS_B
2553 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2554 AND ORGANIZATION_ID = p_organization_id;
2555 EXCEPTION
2556 WHEN NO_DATA_FOUND THEN
2557
2558 ----------------------------------------------------
2559 -- If the passed-in PKs are bad, report the error --
2560 ----------------------------------------------------
2561 l_token_table(1).TOKEN_NAME := 'ITEM_ID';
2562 l_token_table(1).TOKEN_VALUE := p_inventory_item_id;
2563
2564 IF (FND_API.To_Boolean(p_add_errors_to_fnd_stack)) THEN
2565 ERROR_HANDLER.Add_Error_Message(
2566 p_message_name => 'EGO_EF_BL_INV_ITEM_ID_ERR'
2567 ,p_application_id => 'EGO'
2568 ,p_token_tbl => l_token_table
2569 ,p_message_type => FND_API.G_RET_STS_ERROR
2570 ,p_entity_id => p_entity_id
2571 ,p_entity_index => p_entity_index
2572 ,p_entity_code => p_entity_code
2573 ,p_addto_fnd_stack => 'Y'
2574 );
2575 ELSE
2576 ERROR_HANDLER.Add_Error_Message(
2577 p_message_name => 'EGO_EF_BL_INV_ITEM_ID_ERR'
2578 ,p_application_id => 'EGO'
2579 ,p_token_tbl => l_token_table
2580 ,p_message_type => FND_API.G_RET_STS_ERROR
2581 ,p_entity_id => p_entity_id
2582 ,p_entity_index => p_entity_index
2583 ,p_entity_code => p_entity_code
2584 );
2585 END IF;
2586
2587 RAISE FND_API.G_EXC_ERROR;
2588
2589 END;
2590
2591 Get_Related_Class_Codes(
2592 l_item_catalog_group_id
2593 ,p_entity_id
2594 ,p_entity_index
2595 ,p_entity_code
2596 ,l_related_class_codes_list
2597 );
2598
2599 l_class_code_values :=
2600 EGO_COL_NAME_VALUE_PAIR_ARRAY(
2601 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', TO_CHAR(l_item_catalog_group_id))
2602 ,EGO_COL_NAME_VALUE_PAIR_OBJ('RELATED_CLASS_CODE_LIST_1', l_related_class_codes_list)
2603 );
2604
2605 ---------------------------------------------------------------
2606 -- Next, we build our privileges table for the current user; --
2607 -- any error in this helper function will be raised as an --
2608 -- exception, which will prevent us from calling PUAD at all --
2609 ---------------------------------------------------------------
2610 l_user_privileges_on_object := Get_User_Attrs_Privs(
2611 p_inventory_item_id,
2612 p_organization_id,
2613 p_entity_id,
2614 p_entity_index,
2615 p_entity_code
2616 );
2617
2618 ---------------------------------------------------------------
2619 -- we are calling the API to check and error out if an attribute
2620 --update needs to have a change order or update is not allowed.
2621 ---------------------------------------------------------------
2622 IF (p_do_policy_check = FND_API.G_TRUE ) THEN
2623 l_attributes_row_table:=
2624 Remove_Rows_After_Policy_Check(
2625 p_inventory_item_id => p_inventory_item_id
2626 ,p_organization_id => p_organization_id
2627 ,p_attributes_row_table => p_attributes_row_table
2628 ,p_entity_id => p_entity_id
2629 ,p_entity_code => p_entity_code
2630 ,x_return_status => l_return_status
2631 );
2632 ELSE
2633 l_attributes_row_table:=p_attributes_row_table;
2634 END IF;
2635 --writing the errors to the log
2636 IF (l_return_status=FND_API.G_RET_STS_ERROR) THEN
2637 ERROR_HANDLER.Log_Error(
2638 p_write_err_to_inttable => 'Y'
2639 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
2640 );
2641 END IF;
2642
2643 ---------------------------------------------------------------
2644 -- If all went well with retrieving privileges, we call PUAD --
2645 ---------------------------------------------------------------
2646 EGO_USER_ATTRS_DATA_PVT.Process_User_Attrs_Data(
2647 p_api_version => 1.0
2648 ,p_object_name => 'EGO_ITEM'
2649 ,p_attributes_row_table => l_attributes_row_table
2650 ,p_attributes_data_table => p_attributes_data_table
2651 ,p_pk_column_name_value_pairs => l_pk_column_values
2652 ,p_class_code_name_value_pairs => l_class_code_values
2653 ,p_user_privileges_on_object => l_user_privileges_on_object
2654 ,p_change_info_table => p_change_info_table
2655 --,p_pending_b_table_name => 'EGO_ITEMS_ATTRS_CHANGES_B'
2656 --,p_pending_tl_table_name => 'EGO_ITEMS_ATTRS_CHANGES_TL'
2657 --,p_pending_vl_name => 'EGO_ITEMS_ATTRS_CHANGES_VL'
2658 ,p_entity_id => p_entity_id
2659 ,p_entity_index => p_entity_index
2660 ,p_entity_code => p_entity_code
2661 ,p_debug_level => p_debug_level
2662 ,p_validate_hierarchy => p_validate_hierarchy
2663 ,p_init_error_handler => p_init_error_handler
2664 ,p_write_to_concurrent_log => p_write_to_concurrent_log
2665 ,p_init_fnd_msg_list => p_init_fnd_msg_list
2666 ,p_log_errors => p_log_errors
2667 ,p_add_errors_to_fnd_stack => p_add_errors_to_fnd_stack
2668 ,p_commit => p_commit
2669 ,x_failed_row_id_list => x_failed_row_id_list
2670 ,x_return_status => x_return_status
2671 ,x_errorcode => x_errorcode
2672 ,x_msg_count => x_msg_count
2673 ,x_msg_data => x_msg_data
2674 );
2675
2676 EXCEPTION
2677 WHEN FND_API.G_EXC_ERROR THEN
2678
2679 x_return_status := FND_API.G_RET_STS_ERROR;
2680
2681 x_msg_count := ERROR_HANDLER.Get_Message_Count();
2682
2683 IF (x_msg_count > 0) THEN
2684 IF (FND_API.To_Boolean(p_log_errors)) THEN
2685 IF (FND_API.To_Boolean(p_write_to_concurrent_log)) THEN
2686 ERROR_HANDLER.Log_Error(
2687 p_write_err_to_inttable => 'Y'
2688 ,p_write_err_to_conclog => 'Y'
2689 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
2690 );
2691 ELSE
2692 ERROR_HANDLER.Log_Error(
2693 p_write_err_to_inttable => 'Y'
2694 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
2695 );
2696 END IF;
2697 END IF;
2698
2699 IF (x_msg_count = 1) THEN
2700 DECLARE
2701 message_list ERROR_HANDLER.Error_Tbl_Type;
2702 BEGIN
2703 ERROR_HANDLER.Get_Message_List(message_list);
2704 x_msg_data := message_list(message_list.FIRST).message_text;
2705 END;
2706 ELSE
2707 x_msg_data := NULL;
2708 END IF;
2709 END IF;
2710
2711 WHEN OTHERS THEN
2712
2713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2714
2715 DECLARE
2716 l_dummy_entity_index NUMBER;
2717 l_dummy_entity_id VARCHAR2(60);
2718 l_dummy_message_type VARCHAR2(1);
2719 BEGIN
2720 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
2721 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
2722 l_token_table(2).TOKEN_NAME := 'API_NAME';
2723 l_token_table(2).TOKEN_VALUE := l_api_name;
2724 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
2725 l_token_table(3).TOKEN_VALUE := SQLERRM;
2726
2727 IF (FND_API.To_Boolean(p_add_errors_to_fnd_stack)) THEN
2728 ERROR_HANDLER.Add_Error_Message(
2729 p_message_name => 'EGO_PLSQL_ERR'
2730 ,p_application_id => 'EGO'
2731 ,p_token_tbl => l_token_table
2732 ,p_message_type => FND_API.G_RET_STS_ERROR
2733 ,p_addto_fnd_stack => 'Y'
2734 );
2735 ELSE
2736 ERROR_HANDLER.Add_Error_Message(
2737 p_message_name => 'EGO_PLSQL_ERR'
2738 ,p_application_id => 'EGO'
2739 ,p_token_tbl => l_token_table
2740 ,p_message_type => FND_API.G_RET_STS_ERROR
2741 );
2742 END IF;
2743
2744 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
2745 ,x_entity_index => l_dummy_entity_index
2746 ,x_entity_id => l_dummy_entity_id
2747 ,x_message_type => l_dummy_message_type);
2748
2749 END;
2750
2751 END Process_User_Attrs_For_Item;
2752
2753 -- -----------------------------------------------------------------------------
2754 -- API Name: Get_User_Attrs_For_Item
2755 --
2756 -- Description:
2757 -- Fetch passed-in User-Defined Attrs data for
2758 -- the Item whose Primary Keys are passed in
2759 -- -----------------------------------------------------------------------------
2760 PROCEDURE Get_User_Attrs_For_Item (
2761 p_api_version IN NUMBER
2762 ,p_inventory_item_id IN NUMBER
2763 ,p_organization_id IN NUMBER
2764 ,p_attr_group_request_table IN EGO_ATTR_GROUP_REQUEST_TABLE
2765 ,p_entity_id IN NUMBER DEFAULT NULL
2766 ,p_entity_index IN NUMBER DEFAULT NULL
2767 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2768 ,p_debug_level IN NUMBER DEFAULT 0
2769 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
2770 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2771 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
2772 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2773 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
2774 ,x_attributes_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
2775 ,x_return_status OUT NOCOPY VARCHAR2
2776 ,x_errorcode OUT NOCOPY NUMBER
2777 ,x_msg_count OUT NOCOPY NUMBER
2778 ,x_msg_data OUT NOCOPY VARCHAR2
2779 ) IS
2780
2781 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_For_Item';
2782
2783 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
2784 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
2785
2786 BEGIN
2787
2788 -----------------------
2789 -- Get PKs organized --
2790 -----------------------
2791 l_pk_column_values :=
2792 EGO_COL_NAME_VALUE_PAIR_ARRAY(
2793 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', TO_CHAR(p_inventory_item_id))
2794 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', TO_CHAR(p_organization_id))
2795 );
2796
2797 ---------------------------------------------------------------
2798 -- Next, we build our privileges table for the current user; --
2799 -- any error in this helper function will be raised as an --
2800 -- exception, which will prevent us from calling GUAD at all --
2801 ---------------------------------------------------------------
2802 l_user_privileges_on_object := Get_User_Attrs_Privs(
2803 p_inventory_item_id,
2804 p_organization_id,
2805 p_entity_id,
2806 p_entity_index,
2807 p_entity_code
2808 );
2809
2810 ---------------------------------------------------------------
2811 -- If all went well with retrieving privileges, we call GUAD --
2812 ---------------------------------------------------------------
2813 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
2814 p_api_version => p_api_version
2815 ,p_object_name => 'EGO_ITEM'
2816 ,p_pk_column_name_value_pairs => l_pk_column_values
2817 ,p_attr_group_request_table => p_attr_group_request_table
2818 ,p_user_privileges_on_object => l_user_privileges_on_object
2819 ,p_entity_id => p_entity_id
2820 ,p_entity_index => p_entity_index
2821 ,p_entity_code => p_entity_code
2822 ,p_debug_level => p_debug_level
2823 ,p_init_error_handler => p_init_error_handler
2824 ,p_init_fnd_msg_list => p_init_fnd_msg_list
2825 ,p_add_errors_to_fnd_stack => p_add_errors_to_fnd_stack
2826 ,p_commit => p_commit
2827 ,x_attributes_row_table => x_attributes_row_table
2828 ,x_attributes_data_table => x_attributes_data_table
2829 ,x_return_status => x_return_status
2830 ,x_errorcode => x_errorcode
2831 ,x_msg_count => x_msg_count
2832 ,x_msg_data => x_msg_data
2833 );
2834
2835
2836 EXCEPTION
2837 WHEN FND_API.G_EXC_ERROR THEN
2838
2839 x_return_status := FND_API.G_RET_STS_ERROR;
2840
2841 x_msg_count := ERROR_HANDLER.Get_Message_Count();
2842
2843 IF (x_msg_count > 0) THEN
2844 ERROR_HANDLER.Log_Error(
2845 p_write_err_to_inttable => 'Y'
2846 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
2847 );
2848
2849 IF (x_msg_count = 1) THEN
2850 DECLARE
2851 message_list ERROR_HANDLER.Error_Tbl_Type;
2852 BEGIN
2853 ERROR_HANDLER.Get_Message_List(message_list);
2854 x_msg_data := message_list(message_list.FIRST).message_text;
2855 END;
2856 ELSE
2857 x_msg_data := NULL;
2858 END IF;
2859 END IF;
2860
2861 WHEN OTHERS THEN
2862
2863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2864
2865 DECLARE
2866 l_token_table ERROR_HANDLER.Token_Tbl_Type;
2867 l_dummy_entity_index NUMBER;
2868 l_dummy_entity_id VARCHAR2(60);
2869 l_dummy_message_type VARCHAR2(1);
2870 BEGIN
2871 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
2872 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
2873 l_token_table(2).TOKEN_NAME := 'API_NAME';
2874 l_token_table(2).TOKEN_VALUE := l_api_name;
2875 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
2876 l_token_table(3).TOKEN_VALUE := SQLERRM;
2877
2878 IF (FND_API.To_Boolean(p_add_errors_to_fnd_stack)) THEN
2879 ERROR_HANDLER.Add_Error_Message(
2880 p_message_name => 'EGO_PLSQL_ERR'
2881 ,p_application_id => 'EGO'
2882 ,p_token_tbl => l_token_table
2883 ,p_message_type => FND_API.G_RET_STS_ERROR
2884 ,p_addto_fnd_stack => 'Y'
2885 );
2886 ELSE
2887 ERROR_HANDLER.Add_Error_Message(
2888 p_message_name => 'EGO_PLSQL_ERR'
2889 ,p_application_id => 'EGO'
2890 ,p_token_tbl => l_token_table
2891 ,p_message_type => FND_API.G_RET_STS_ERROR
2892 );
2893 END IF;
2894
2895 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
2896 ,x_entity_index => l_dummy_entity_index
2897 ,x_entity_id => l_dummy_entity_id
2898 ,x_message_type => l_dummy_message_type);
2899
2900 END;
2901
2902 END Get_User_Attrs_For_Item;
2903
2904 -- -----------------------------------------------------------------------------
2905 -- API Name: Generate_Seq_For_Item_Catalog
2906 --
2907 -- Description:
2908 -- Generates the Item Sequence For Number Generation
2909 -- -----------------------------------------------------------------------------
2910 PROCEDURE Generate_Seq_For_Item_Catalog (
2911 p_item_catalog_group_id IN NUMBER
2912 ,p_seq_start_num IN NUMBER
2913 ,p_seq_increment_by IN NUMBER
2914 ,x_return_status OUT NOCOPY VARCHAR2
2915 ,x_errorcode OUT NOCOPY NUMBER
2916 ,x_msg_count OUT NOCOPY NUMBER
2917 ,x_msg_data OUT NOCOPY VARCHAR2
2918 )IS
2919 l_api_name CONSTANT VARCHAR2(50) := 'Generate_Sequence_For_Item_Catalog';
2920 l_seq_name VARCHAR2(100);
2921 l_syn_name VARCHAR2(100);
2922 l_seq_name_prefix VARCHAR2(70) ;
2923 l_syn_name_prefix CONSTANT VARCHAR2(70) := 'ITEM_NUM_SEQ_';
2924 l_seq_name_suffix CONSTANT VARCHAR2(10) := '_S' ;
2925 l_dyn_sql VARCHAR2(100);
2926
2927 l_status VARCHAR2(1);
2928 l_industry VARCHAR2(1);
2929 l_schema VARCHAR2(30);
2930
2931 BEGIN
2932
2933 IF FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
2934 IF l_schema IS NULL THEN
2935 Raise_Application_Error (-20001, 'INV Schema could not be located.');
2936 END IF;
2937 ELSE
2938 Raise_Application_Error (-20001, 'INV Schema could not be located.');
2939 END IF;
2940
2941 l_seq_name_prefix := l_schema ||'.'||'ITEM_NUM_SEQ_';
2942
2943 l_seq_name := l_seq_name_prefix || p_item_catalog_group_id || l_seq_name_suffix;
2944 l_dyn_sql := 'CREATE SEQUENCE '||l_seq_name||' INCREMENT BY '||p_seq_increment_by||' START WITH '||p_seq_start_num || ' NOCACHE';
2945 EXECUTE IMMEDIATE l_dyn_sql;
2946 l_syn_name := l_syn_name_prefix || p_item_catalog_group_id || l_seq_name_suffix;
2947 l_dyn_sql := 'CREATE SYNONYM '||l_syn_name||' FOR '||l_seq_name;
2948 EXECUTE IMMEDIATE l_dyn_sql;
2949 EXCEPTION
2950 WHEN others THEN
2951 x_return_status := G_RET_STS_UNEXP_ERROR;
2952 x_msg_data := G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
2953 END Generate_Seq_For_Item_Catalog;
2954
2955 ----------------------------------------------------------------------
2956 -- -----------------------------------------------------------------------------
2957 -- API Name: Drop_Sequence_For_Item_Catalog
2958 --
2959 -- Description:
2960 -- Drops the Item Sequence For Number Generation
2961 -- -----------------------------------------------------------------------------
2962 PROCEDURE Drop_Sequence_For_Item_Catalog (
2963 p_item_catalog_seq_name IN VARCHAR2
2964 ,x_return_status OUT NOCOPY VARCHAR2
2965 ,x_errorcode OUT NOCOPY NUMBER
2966 ,x_msg_count OUT NOCOPY NUMBER
2967 ,x_msg_data OUT NOCOPY VARCHAR2)
2968 IS
2969 l_api_name CONSTANT VARCHAR2(50) := 'Drop_Sequence_For_Item_Catalog';
2970 l_dyn_sql VARCHAR2(100);
2971 l_status VARCHAR2(1);
2972 l_industry VARCHAR2(1);
2973 l_schema VARCHAR2(30);
2974 BEGIN
2975
2976 IF FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
2977 IF l_schema IS NULL THEN
2978 Raise_Application_Error (-20001, 'INV Schema could not be located.');
2979 END IF;
2980 ELSE
2981 Raise_Application_Error (-20001, 'INV Schema could not be located.');
2982 END IF;
2983
2984 l_dyn_sql := 'DROP SYNONYM '||p_item_catalog_seq_name;
2985 EXECUTE IMMEDIATE l_dyn_sql;
2986 l_dyn_sql := 'DROP SEQUENCE '||l_schema||'.'||p_item_catalog_seq_name;
2987 EXECUTE IMMEDIATE l_dyn_sql;
2988 EXCEPTION
2989 WHEN others THEN
2990 x_return_status := G_RET_STS_UNEXP_ERROR;
2991 x_msg_data := G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
2992 END Drop_Sequence_For_Item_Catalog;
2993
2994
2995 -- -----------------------------------------------------------------------------
2996 -- API Name: has_role_on_item
2997 --
2998 -- Description:
2999 -- API to check whether the user has a role on Item or Not
3000 -- TRUE if the user has the specified role on the item
3001 -- FALSE if the user does not have the specified role on the item
3002 --
3003 -- -----------------------------------------------------------------------------
3004 FUNCTION has_role_on_item (p_function_name IN VARCHAR2
3005 ,p_instance_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
3006 ,p_inventory_item_id IN NUMBER
3007 ,p_item_number IN VARCHAR2
3008 ,p_organization_id IN VARCHAR2
3009 ,p_organization_name IN VARCHAR2
3010 ,p_user_id IN NUMBER
3011 ,p_party_id IN NUMBER
3012 ,p_set_message IN VARCHAR2
3013 ) RETURN BOOLEAN IS
3014 TYPE dynamic_cur IS REF CURSOR;
3015 c_priv_cursor dynamic_cur;
3016 l_owner_party_id hz_parties.party_id%TYPE;
3017 l_owner_party_name hz_parties.party_name%TYPE;
3018 l_sec_predicate VARCHAR2(32767);
3019 l_return_status VARCHAR2(10);
3020 l_select_sql VARCHAR2(32767);
3021 l_dummy_number NUMBER;
3022 l_dummy_char VARCHAR2(32767);
3023
3024
3025 CURSOR c_user_party_id (cp_user_id IN NUMBER) IS
3026 SELECT party_id, party_name
3027 FROM ego_user_v
3028 WHERE user_id = cp_user_id;
3029
3030 CURSOR c_user_party_name (cp_party_id IN NUMBER) IS
3031 SELECT party_name
3032 FROM hz_parties
3033 WHERE party_id = cp_party_id;
3034
3035
3036 BEGIN
3037 l_owner_party_name := NULL;
3038 IF p_user_id IS NULL THEN
3039 OPEN c_user_party_id(cp_user_id => FND_GLOBAL.User_Id);
3040 ELSE
3041 OPEN c_user_party_id(cp_user_id => p_user_id);
3042 END IF;
3043 FETCH c_user_party_id INTO l_owner_party_id, l_owner_party_name;
3044 IF c_user_party_id%NOTFOUND THEN
3045 CLOSE c_user_party_id;
3046 --
3047 -- user is not registered properly
3048 --
3049 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_FUNC_PRIVILEGE_FOR_USER');
3050 IF FND_API.To_Boolean(p_set_message) THEN
3051 fnd_msg_pub.Add;
3052 END IF;
3053 RETURN FALSE;
3054 ELSE
3055 CLOSE c_user_party_id;
3056 END IF;
3057
3058 EGO_DATA_SECURITY.get_security_predicate(
3059 p_api_version => 1.0,
3060 p_function => p_function_name,
3061 p_object_name => G_EGO_ITEM,
3062 p_grant_instance_type => p_instance_type,
3063 p_user_name => 'HZ_PARTY:'||TO_CHAR(l_owner_party_id),
3064 p_statement_type => 'EXISTS',
3065 p_pk1_alias => 'MSIB.INVENTORY_ITEM_ID',
3066 p_pk2_alias => 'MSIB.ORGANIZATION_ID',
3067 p_pk3_alias => NULL,
3068 p_pk4_alias => NULL,
3069 p_pk5_alias => NULL,
3070 x_predicate => l_sec_predicate,
3071 x_return_status => l_return_status );
3072 code_debug(' Security Predicate '||l_sec_predicate);
3073 IF (l_sec_predicate IS NULL OR l_sec_predicate = '') THEN
3074 RETURN TRUE;
3075 ELSE
3076 l_select_sql :=
3077 ' SELECT 1 '||
3078 ' FROM MTL_SYSTEM_ITEMS MSIB '||
3079 ' WHERE MSIB.INVENTORY_ITEM_ID = :1'||
3080 ' AND MSIB.ORGANIZATION_ID = :2'||
3081 ' AND ' ||l_sec_predicate;
3082 code_debug(' Priv Query '||l_select_sql);
3083 OPEN c_priv_cursor FOR l_select_sql USING p_inventory_item_id,p_organization_id;
3084 FETCH c_priv_cursor INTO l_dummy_number;
3085 IF c_priv_cursor%NOTFOUND THEN
3086 CLOSE c_priv_cursor;
3087 IF FND_API.To_Boolean(p_set_message) THEN
3088 code_debug (' user does not have privilege '||p_function_name ||' on item '||p_inventory_item_id);
3089 IF p_function_name = G_FN_NAME_ADD_ROLE THEN
3090 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_CANNOT_GRANT');
3091 -- EGO_IPI_CANNOT_GRANT : User "USER" does not have privilege to give grants on Item "ITEM" in Organization "ORGANIZATION".
3092 ELSIF p_function_name IN (G_FN_NAME_PROMOTE) THEN
3093 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_PRIV_PROMOTE');
3094 -- EGO_NO_PRIV_PROMOTE : User "USER" does not have privilege to create, update, or delete pending phase records for promotion of Item "ITEM_NUMBER" in Organization "ORGANIZATION".
3095 ELSIF p_function_name IN (G_FN_NAME_DEMOTE) THEN
3096 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_PRIV_DEMOTE');
3097 -- EGO_NO_PRIV_DEMOTE : User "USER" does not have privilege to create, update, or delete pending phase records for demotion of Item "ITEM_NUMBER" in Organization "ORGANIZATION".
3098 ELSIF p_function_name = G_FN_NAME_CHANGE_STATUS THEN
3099 fnd_message.Set_Name(G_APP_NAME, 'EGO_NO_PRIV_CHANGE_STATUS');
3100 -- EGO_NO_PRIV_CHANGE_STATUS : User "USER" does not have privilege to change status of Item "ITEM" in Organization "ORGANIZATION".
3101 ELSIF p_function_name = G_FN_NAME_EDIT_LC_PROJ THEN
3102 fnd_message.set_name(G_APP_NAME, 'EGO_NO_PRIV_ITEM_PROJ_ASSOC');
3103 -- EGO_NO_PRIV_ITEM_PROJ_ASSOC : User "USER" does not have privilege to create, update or delete project associtions on Item "ITEM" in Organization "ORGANIZATION".
3104 END IF;
3105 IF l_owner_party_name IS NULL THEN
3106 OPEN c_user_party_name (cp_party_id => l_owner_party_id);
3107 FETCH c_user_party_name INTO l_owner_party_name;
3108 CLOSE c_user_party_name;
3109 END IF;
3110 fnd_message.Set_Token('USER', l_owner_party_name);
3111 IF p_item_number IS NULL THEN
3112 SELECT concatenated_segments
3113 INTO l_dummy_char
3114 FROM mtl_system_items_b_kfv
3115 WHERE organization_id = p_organization_id
3116 AND inventory_item_id = p_inventory_item_id;
3117 ELSE
3118 l_dummy_char := p_item_number;
3119 END IF;
3120 IF p_function_name = G_FN_NAME_ADD_ROLE THEN
3121 fnd_message.Set_Token('ITEM', l_dummy_char);
3122 ELSE
3123 fnd_message.Set_Token('ITEM_NUMBER', l_dummy_char);
3124 END IF;
3125 IF p_organization_name IS NULL THEN
3126 SELECT name
3127 INTO l_dummy_char
3128 FROM hr_all_organization_units_vl
3129 WHERE organization_id = p_organization_id;
3130 ELSE
3131 l_dummy_char := p_organization_name;
3132 END IF;
3133 fnd_message.Set_Token('ORGANIZATION', l_dummy_char);
3134 fnd_msg_pub.Add;
3135 END IF;
3136 RETURN FALSE;
3137 ELSE
3138 CLOSE c_priv_cursor;
3139 RETURN TRUE;
3140 END IF;
3141 END IF;
3142 EXCEPTION
3143 WHEN OTHERS THEN
3144 RETURN FALSE;
3145 END has_role_on_item;
3146
3147 ------------------------------------------------------------------------------
3148 -- Start OF comments
3149 -- API name : Process_item_role
3150 -- TYPE : Public
3151 -- Pre-reqs : None
3152 -- PROCEDURE : Grants a role on an item.
3153 --
3154 ------------------------------------------------------------------------------
3155 PROCEDURE Process_item_role
3156 (p_api_version IN NUMBER
3157 ,p_commit IN VARCHAR2
3158 ,p_init_msg_list IN VARCHAR2
3159 ,p_transaction_type IN VARCHAR2
3160 ,p_inventory_item_id IN NUMBER
3161 ,p_item_number IN VARCHAR2
3162 ,p_organization_id IN NUMBER
3163 ,p_organization_code IN VARCHAR2
3164 ,p_role_id IN NUMBER
3165 ,p_role_name IN VARCHAR2
3166 ,p_instance_type IN VARCHAR2
3167 ,p_instance_set_id IN NUMBER
3168 ,p_instance_set_name IN VARCHAR2
3169 ,p_party_type IN VARCHAR2
3170 ,p_party_id IN NUMBER
3171 ,p_party_name IN VARCHAR2
3172 ,p_start_date IN DATE
3173 ,p_end_date IN DATE
3174 ,x_grant_guid IN OUT NOCOPY RAW
3175 ,x_return_status OUT NOCOPY VARCHAR2
3176 ,x_msg_count OUT NOCOPY NUMBER
3177 ,x_msg_data OUT NOCOPY VARCHAR2
3178 ) IS
3179 l_api_name VARCHAR2(30);
3180 l_api_version NUMBER;
3181 l_error_token_table ERROR_HANDLER.Token_Tbl_Type;
3182 l_dummy_number NUMBER;
3183 l_dummy_char VARCHAR2(32767);
3184 l_sysdate DATE;
3185 l_start_date DATE;
3186 l_end_date DATE;
3187 l_organization_id mtl_system_items_b.organization_id%TYPE;
3188 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
3189 l_approval_status mtl_system_items_b.approval_status%TYPE;
3190 l_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE;
3191 l_hz_party_type hz_parties.party_type%TYPE;
3192 l_instance_set_id fnd_object_instance_sets.instance_set_id%TYPE;
3193 l_party_id hz_parties.party_id%TYPE;
3194 l_party_name hz_parties.party_name%TYPE;
3195 l_role_id fnd_menus_vl.menu_id%TYPE;
3196 l_role_name fnd_menus_vl.menu_name%TYPE;
3197 l_instance_type fnd_grants.instance_type%TYPE;
3198 l_pk1_value fnd_grants.instance_pk1_value%TYPE;
3199 l_pk2_value fnd_grants.instance_pk2_value%TYPE;
3200 l_invalid_flag BOOLEAN;
3201 l_create_grant_flag BOOLEAN;
3202 l_user_id NUMBER;
3203 BEGIN
3204
3205 -- user must not be able to delete his own grants
3206 l_api_name := 'Process_item_role';
3207 l_api_version := 1.0;
3208 x_return_status := FND_API.G_RET_STS_SUCCESS;
3209 l_user_id := FND_GLOBAL.User_Id;
3210 code_debug(l_api_name ||' started with params - grant guid '||RAWTOHEX(x_grant_guid));
3211 code_debug(' p_api_version '|| p_api_version||' p_commit '||p_commit||' p_init_msg_list '||p_init_msg_list );
3212 code_debug(' p_transaction_type '||p_transaction_type ||' p_inventory_item_id '||p_inventory_item_id||' p_item_number '||p_item_number );
3213 code_debug(' p_organization_id '||p_organization_id ||' p_organization_code '||p_organization_code||' p_role_id '||p_role_id );
3214 code_debug(' p_role_name '||p_role_name ||' p_instance_type '||p_instance_type||' p_instance_set_id '||p_instance_set_id );
3215 code_debug(' p_instance_set_name '||p_instance_set_name ||' p_party_type '||p_party_type||' p_party_id '||p_party_id );
3216 code_debug(' p_party_name'||p_party_name ||' p_start_date'||p_start_date||' p_end_date'||p_end_date );
3217
3218 IF FND_API.To_Boolean( p_commit ) THEN
3219 SAVEPOINT PROCESS_ITEM_ROLE_SP;
3220 END IF;
3221 --
3222 -- Initialize message list
3223 --
3224 IF FND_API.To_Boolean(p_init_msg_list) THEN
3225 FND_MSG_PUB.Initialize;
3226 END IF;
3227 code_debug(l_api_name||' msg pub initialized ' );
3228 --
3229 --Standard checks
3230 --
3231 IF NOT FND_API.Compatible_API_Call (l_api_version
3232 ,p_api_version
3233 ,l_api_name
3234 ,g_pkg_name)THEN
3235 code_debug (l_api_version ||' invalid api version ');
3236 RAISE FND_API.G_EXC_ERROR;
3237 END IF;
3238 code_debug(l_api_name||' valid api ' );
3239 --
3240 -- invalid params passed
3241 --
3242 IF ( p_transaction_type IS NULL
3243 OR
3244 p_transaction_type NOT IN
3245 (EGO_ITEM_PUB.G_TTYPE_CREATE
3246 ,EGO_ITEM_PUB.G_TTYPE_DELETE
3247 ,EGO_ITEM_PUB.G_TTYPE_UPDATE
3248 )
3249 OR
3250 (p_transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
3251 AND( (p_role_name IS NULL AND p_role_id IS NULL)
3252 OR
3253 p_party_type IS NULL
3254 OR
3255 p_party_type NOT IN
3256 (EGO_ITEM_PUB.G_USER_PARTY_TYPE
3257 ,EGO_ITEM_PUB.G_GROUP_PARTY_TYPE
3258 ,EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE
3259 ,EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE
3260 )
3261 OR
3262 (p_party_type IN (EGO_ITEM_PUB.G_GROUP_PARTY_TYPE
3263 ,EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE
3264 ,EGO_ITEM_PUB.G_USER_PARTY_TYPE)
3265 AND p_party_name IS NULL
3266 AND p_party_id IS NULL
3267 )
3268 OR
3269 p_instance_type IS NULL
3270 OR
3271 p_instance_type NOT IN
3272 (EGO_ITEM_PUB.G_INSTANCE_TYPE_SET
3273 ,EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
3274 )
3275 OR
3276 (p_instance_type = EGO_ITEM_PUB.G_INSTANCE_TYPE_SET
3277 AND
3278 ( (p_instance_set_id IS NULL AND p_instance_set_name IS NULL)
3279 OR
3280 p_inventory_item_id IS NOT NULL
3281 OR
3282 p_organization_id IS NOT NULL
3283 )
3284 )
3285 OR
3286 (p_instance_type = EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
3287 AND
3288 ( (p_inventory_item_id IS NULL AND p_item_number IS NULL)
3289 OR
3290 (p_organization_id IS NULL AND p_organization_code IS NULL)
3291 OR
3292 (p_instance_set_id IS NOT NULL OR p_instance_set_name IS NOT NULL)
3293 )
3294 )
3295 )
3296 )
3297 OR
3298 (p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
3299 AND (x_grant_guid IS NULL)
3300 )
3301 OR
3302 (p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE
3303 AND (x_grant_guid IS NULL)
3304 )
3305 ) THEN
3306 --
3307 -- inalid parameters passed
3308 --
3309 code_debug (l_api_version ||' invalid parameters passed ');
3310 fnd_message.Set_Name(G_APP_NAME, G_INVALID_PARAMS_MSG);
3311 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
3312 fnd_message.Set_Token(G_PROC_NAME_TOKEN, l_api_name);
3313 fnd_msg_pub.Add;
3314 RAISE FND_API.G_EXC_ERROR;
3315 END IF;
3316 code_debug(l_api_name||' valid params passed ' );
3317 l_sysdate := SYSDATE;
3318 l_start_date := NVL(p_start_date, l_sysdate);
3319 l_organization_id := p_organization_id;
3320 l_inventory_item_id := p_inventory_item_id;
3321 l_item_number := p_item_number;
3322 l_role_id := p_role_id;
3323 l_instance_set_id := p_instance_set_id;
3324 l_party_id := p_party_id;
3325 l_party_name := p_party_name;
3326 l_create_grant_flag := TRUE;
3327
3328 IF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_DELETE
3329 ,EGO_ITEM_PUB.G_TTYPE_UPDATE) THEN
3330
3331 BEGIN
3332 SELECT instance_type, instance_set_id, instance_pk1_value,
3333 instance_pk2_value, start_date, end_date
3334 INTO l_instance_type, l_instance_set_id, l_pk1_value,
3335 l_pk2_value, l_start_date, l_end_date
3336 FROM fnd_grants
3337 WHERE grant_guid = x_grant_guid
3338 AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
3339 code_debug(l_api_name||' grant validation check done ' );
3340 EXCEPTION
3341 WHEN NO_DATA_FOUND THEN
3342 code_debug (l_api_version ||' no grant found for modification ');
3343 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
3344 fnd_message.set_name (G_APP_NAME, 'EGO_NO_REC_UPDATE');
3345 ELSE
3346 fnd_message.set_name (G_APP_NAME, 'EGO_NO_REC_DELETE');
3347 END IF;
3348 fnd_msg_pub.Add;
3349 RAISE FND_API.G_EXC_ERROR;
3350 END;
3351 --
3352 -- no security check done for the update of grants of type instance set.
3353 -- should this be done??
3354 --
3355 IF l_instance_type = EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE THEN
3356 l_inventory_item_id := l_pk1_value;
3357 l_organization_id := l_pk2_value;
3358 -- 4052565
3359 -- modified call to has_role_on_item from validate_role_privilege
3360 IF NOT has_role_on_item
3361 (p_function_name => G_FN_NAME_ADD_ROLE
3362 ,p_instance_type => p_instance_type
3363 ,p_inventory_item_id => l_inventory_item_id
3364 ,p_item_number => NULL
3365 ,p_organization_id => l_organization_id
3366 ,p_organization_name => NULL
3367 ,p_user_id => l_user_id
3368 ,p_party_id => l_party_id
3369 ,p_set_message => G_TRUE
3370 ) THEN
3371 code_debug(l_api_name ||' user does not have privilege to update the roles on item');
3372 RAISE FND_API.G_EXC_ERROR;
3373 END IF;
3374 code_debug(l_api_name ||' user has privilege to update the roles on item');
3375 ELSE
3376 IF NOT validate_function_security(p_function_name => G_FN_NAME_ADMIN
3377 ,p_set_message => G_TRUE) THEN
3378 code_debug(l_api_name ||' user does not have function privilege to update roles in instance set');
3379 RAISE FND_API.G_EXC_ERROR;
3380 END IF;
3381 code_debug(l_api_name ||' user has function privilege to update the roles in instance set');
3382 END IF;
3383
3384 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE THEN
3385 --
3386 -- delete the grant given
3387 --
3388 code_debug(l_api_name||' calling EGO_SECURITY_PUB.revoke_grant ' );
3389 EGO_SECURITY_PUB.revoke_grant
3390 (p_api_version => 1.0
3391 ,p_grant_guid => RAWTOHEX(x_grant_guid)
3392 ,x_return_status => x_return_status
3393 ,x_errorcode => l_dummy_number
3394 );
3395 code_debug(l_api_name||' returning EGO_SECURITY_PUB.revoke_grant with status '||x_return_status );
3396 IF x_return_status <> G_TRUE THEN
3397 --
3398 -- should never occur as the grant is already valid
3399 --
3400 RAISE FND_API.G_EXC_ERROR;
3401 ELSE
3402 x_return_status := FND_API.G_RET_STS_SUCCESS;
3403 IF FND_API.To_Boolean( p_commit ) THEN
3404 COMMIT WORK;
3405 END IF;
3406 RETURN;
3407 END IF;
3408 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
3409 --
3410 -- update the grant given
3411 --
3412 IF date_check (p_start_date => l_sysdate
3413 ,p_end_date => l_end_date
3414 ,p_validation_type => G_GT_VAL
3415 ) THEN
3416 code_debug (l_api_version ||' grant is already end dated ');
3417 fnd_message.Set_Name(G_APP_NAME, 'EGO_GRANT_END_DATED');
3418 fnd_msg_pub.Add;
3419 RAISE FND_API.G_EXC_ERROR;
3420 END IF;
3421 IF date_check (p_start_date => l_sysdate
3422 ,p_end_date => p_end_date
3423 ,p_validation_type => G_GT_VAL
3424 ) THEN
3425 code_debug (l_api_version ||' end date less than sysdate ');
3426 fnd_message.Set_Name(G_APP_NAME, 'EGO_ENDDATE_LT_CURRDATE');
3427 fnd_msg_pub.Add;
3428 RAISE FND_API.G_EXC_ERROR;
3429 END IF;
3430 IF (p_start_date > l_sysdate and l_start_date > l_sysdate) THEN
3431 l_start_date := NVL(p_start_date,l_start_date);
3432 END IF;
3433 code_debug(l_api_name||' calling EGO_SECURITY_PUB.set_grant_date ');
3434 EGO_SECURITY_PUB.set_grant_date
3435 (p_api_version => 1.0
3436 ,p_grant_guid => RAWTOHEX(x_grant_guid)
3437 ,p_start_date => l_start_date
3438 ,p_end_date => p_end_date
3439 ,x_return_status => x_return_status
3440 );
3441 code_debug(l_api_name||' returning EGO_SECURITY_PUB.set_grant_date with status '||x_return_status );
3442 IF x_return_status = G_FALSE THEN
3443 code_debug (l_api_version ||' overlap grant found for update ');
3444 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_OVERLAP_GRANT');
3445 fnd_message.Set_Token('START_DATE', l_start_date);
3446 fnd_message.Set_Token('END_DATE', p_end_date);
3447 fnd_msg_pub.Add;
3448 RAISE FND_API.G_EXC_ERROR;
3449 ELSE
3450 x_return_status := FND_API.G_RET_STS_SUCCESS;
3451 IF FND_API.To_Boolean( p_commit ) THEN
3452 COMMIT WORK;
3453 END IF;
3454 RETURN;
3455 END IF;
3456 END IF;
3457 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE THEN
3458 code_debug(l_api_name||' started validations for CREATE ');
3459 --
3460 -- start validations for create
3461 --
3462 --
3463 -- validate the party_id / party_name
3464 --
3465 IF NOT validate_party (p_party_type => p_party_type
3466 ,x_party_id => l_party_id
3467 ,x_party_name => l_party_name
3468 ) THEN
3469 code_debug (l_api_version ||' invalid party passed ');
3470 l_create_grant_flag := FALSE;
3471 IF p_party_type = EGO_ITEM_PUB.G_USER_PARTY_TYPE THEN
3472 fnd_message.Set_Name(G_APP_NAME, 'EGO_USER');
3473 l_dummy_char := fnd_message.get();
3474 ELSIF p_party_type = EGO_ITEM_PUB.G_GROUP_PARTY_TYPE THEN
3475 fnd_message.Set_Name(G_APP_NAME, 'EGO_GROUP_NAME');
3476 l_dummy_char := fnd_message.get();
3477 ELSIF p_party_type = EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE THEN
3478 fnd_message.Set_Name(G_APP_NAME, 'EGO_COMPANY');
3479 l_dummy_char := fnd_message.get();
3480 END IF;
3481 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
3482 fnd_message.Set_Token('NAME', l_dummy_char);
3483 IF l_party_id IS NOT NULL THEN
3484 fnd_message.Set_Token('VALUE', l_party_id);
3485 ELSE
3486 fnd_message.Set_Token('VALUE', l_party_name);
3487 END IF;
3488 fnd_msg_pub.Add;
3489 END IF;
3490 code_debug(l_api_name||' validate party done ');
3491 --
3492 -- validate the menu details passed
3493 --
3494 IF NOT validate_menu (x_menu_id => l_role_id
3495 ,x_menu_name => l_role_name
3496 ,p_user_menu_name => p_role_name
3497 ,p_menu_type => 'SECURITY'
3498 ) THEN
3499 code_debug (l_api_version ||' invalid menu passed ');
3500 l_create_grant_flag := FALSE;
3501 fnd_message.Set_Name(G_APP_NAME, 'EGO_ROLE');
3502 l_dummy_char := fnd_message.get();
3503 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
3504 fnd_message.Set_Token('NAME', l_dummy_char);
3505 IF l_role_id IS NOT NULL THEN
3506 fnd_message.Set_Token('VALUE', l_role_id);
3507 ELSE
3508 fnd_message.Set_Token('VALUE', p_role_name);
3509 END IF;
3510 fnd_msg_pub.Add;
3511 END IF;
3512 code_debug(l_api_name||' validate menu done ');
3513 --
3514 -- validate the start_date - end_date standrad checks
3515 --
3516 IF date_check (p_start_date => l_start_date
3517 ,p_end_date => l_sysdate
3518 ,p_validation_type => G_LT_VAL
3519 ) THEN
3520 code_debug (l_api_version ||' start date less than sysdate ');
3521 l_create_grant_flag := FALSE;
3522 fnd_message.Set_Name(G_APP_NAME, 'EGO_INVALID_GRANT_START_DATE');
3523 fnd_msg_pub.Add;
3524 END IF;
3525 IF date_check (p_start_date => l_sysdate
3526 ,p_end_date => p_end_date
3527 ,p_validation_type => G_GT_VAL
3528 ) THEN
3529 code_debug (l_api_version ||' end date less than sysdate ');
3530 l_create_grant_flag := FALSE;
3531 fnd_message.Set_Name(G_APP_NAME, 'EGO_ENDDATE_LT_CURRDATE');
3532 fnd_msg_pub.Add;
3533 END IF;
3534 IF date_check (p_start_date => l_start_date
3535 ,p_end_date => p_end_date
3536 ,p_validation_type => G_GT_VAL
3537 ) THEN
3538 code_debug (l_api_version ||' end date less than startdate ');
3539 l_create_grant_flag := FALSE;
3540 fnd_message.Set_Name(G_APP_NAME, 'EGO_ENDDATE_EXCEEDS_STARTDATE');
3541 fnd_msg_pub.Add;
3542 END IF;
3543 code_debug(l_api_name||' validate date done ');
3544
3545 IF l_instance_set_id IS NOT NULL OR p_instance_set_name IS NOT NULL THEN
3546 --
3547 -- validate instance set
3548 --
3549 IF NOT validate_instance_set (x_instance_set_id => l_instance_set_id
3550 ,p_set_disp_name => p_instance_set_name
3551 ) THEN
3552 code_debug (l_api_version ||' invalid instance set ');
3553 l_create_grant_flag := FALSE;
3554 fnd_message.Set_Name(G_APP_NAME, 'EGO_INSTANCE');
3555 l_dummy_char := fnd_message.get();
3556 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_INVALID_VALUE');
3557 fnd_message.Set_Token('NAME', l_dummy_char);
3558 IF l_instance_set_id IS NOT NULL THEN
3559 fnd_message.Set_Token('VALUE', l_instance_set_id);
3560 ELSE
3561 fnd_message.Set_Token('VALUE', p_instance_set_name);
3562 END IF;
3563 fnd_msg_pub.Add;
3564 END IF;
3565 code_debug(l_api_name||' validate instance done ');
3566 IF NOT validate_function_security(p_function_name => G_FN_NAME_ADMIN
3567 ,p_set_message => G_TRUE) THEN
3568 code_debug(l_api_name ||' user does not have function privilege to update the roles on item');
3569 l_create_grant_flag := FALSE;
3570 END IF;
3571 ELSE
3572 --
3573 -- validate organization details
3574 --
3575 IF NOT validate_org (x_organization_id => l_organization_id
3576 ,p_organization_code => p_organization_code
3577 ,p_set_message => G_TRUE) THEN
3578 code_debug (l_api_version ||' invalid organization ');
3579 l_create_grant_flag := FALSE;
3580 ELSE
3581 code_debug(l_api_name||' validate org done ');
3582 --
3583 -- validate item details
3584 --
3585 IF NOT validate_item (x_inventory_item_id => l_inventory_item_id
3586 ,x_item_number => l_item_number
3587 ,x_approval_status => l_approval_status
3588 ,p_organization_id => l_organization_id
3589 ,p_set_message => G_TRUE) THEN
3590 code_debug (l_api_version ||' invalid item ');
3591 l_create_grant_flag := FALSE;
3592 ELSE
3593 code_debug(l_api_name||' validate item done ');
3594 -- 4052565
3595 -- modified call to has_role_on_item from validate_role_privilege
3596 IF NOT has_role_on_item
3597 (p_function_name => G_FN_NAME_ADD_ROLE
3598 ,p_instance_type => p_instance_type
3599 ,p_inventory_item_id => l_inventory_item_id
3600 ,p_item_number => l_item_number
3601 ,p_organization_id => l_organization_id
3602 ,p_organization_name => NULL
3603 ,p_user_id => l_user_id
3604 ,p_party_id => l_party_id
3605 ,p_set_message => G_TRUE
3606 ) THEN
3607 code_debug(l_api_name ||' user does not have privilege to create roles ');
3608 l_create_grant_flag := FALSE;
3609 END IF;
3610 code_debug(l_api_name||' validate role privilege done ');
3611 END IF;
3612 END IF;
3613 END IF;
3614
3615 IF l_create_grant_flag THEN
3616 --
3617 -- create a new grant
3618 --
3619 code_debug(l_api_name||' calling EGO_SECURITY_PUB.grant_role_guid ');
3620 EGO_SECURITY_PUB.grant_role_guid
3621 (p_api_version => 1.0
3622 ,p_role_name => l_role_name
3623 ,p_object_name => G_EGO_ITEM
3624 ,p_instance_type => p_instance_type
3625 ,p_instance_set_id => l_instance_set_id
3626 ,p_instance_pk1_value => TO_CHAR(l_inventory_item_id)
3627 ,p_instance_pk2_value => TO_CHAR(l_organization_id)
3628 ,p_instance_pk3_value => NULL
3629 ,p_instance_pk4_value => NULL
3630 ,p_instance_pk5_value => NULL
3631 ,p_party_id => l_party_id
3632 ,p_start_date => l_start_date
3633 ,p_end_date => p_end_date
3634 ,x_return_status => x_return_status
3635 ,x_errorcode => x_msg_data
3636 ,x_grant_guid => x_grant_guid
3637 );
3638 code_debug(l_api_name||' returning EGO_SECURITY_PUB.grant_role_guid with status '||x_return_status);
3639 IF x_return_status = G_FALSE THEN
3640 code_debug (l_api_version ||' grant overlap when creating new grant ');
3641 fnd_message.Set_Name(G_APP_NAME, 'EGO_IPI_OVERLAP_GRANT');
3642 fnd_message.Set_Token('START_DATE', l_start_date);
3643 fnd_message.Set_Token('END_DATE', p_end_date);
3644 fnd_msg_pub.Add;
3645 RAISE FND_API.G_EXC_ERROR;
3646 ELSE
3647 -- changing return status to FND_API.G_RET_STS_SUCCESS
3648 -- as per standards
3649 x_return_status := FND_API.G_RET_STS_SUCCESS;
3650 END IF;
3651 ELSE
3652 code_debug(l_api_name||' raising errors ');
3653 RAISE FND_API.G_EXC_ERROR;
3654 END IF;
3655 END IF;
3656
3657 -- commit data
3658 IF FND_API.To_Boolean( p_commit ) THEN
3659 COMMIT WORK;
3660 END IF;
3661
3662 EXCEPTION
3663 WHEN FND_API.G_EXC_ERROR THEN
3664 code_debug(l_api_name||' returning expected error ');
3665 IF FND_API.To_Boolean( p_commit ) THEN
3666 ROLLBACK TO PROCESS_ITEM_ROLE_SP;
3667 END IF;
3668 x_return_status := FND_API.G_RET_STS_ERROR;
3669 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3670 ,p_count => x_msg_count
3671 ,p_data => x_msg_data);
3672 WHEN OTHERS THEN
3673 IF FND_API.To_Boolean( p_commit ) THEN
3674 ROLLBACK TO PROCESS_ITEM_ROLE_SP;
3675 END IF;
3676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3677 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
3678 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
3679 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
3680 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
3681 FND_MSG_PUB.Add;
3682 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3683 ,p_count => x_msg_count
3684 ,p_data => x_msg_data);
3685 code_debug(' EXCEPTION in '||l_api_name||' : ' ||x_msg_data );
3686 END Process_item_role;
3687
3688
3689 ------------------------------------------------------------------------------
3690 -- Start OF comments
3691 -- API name : Process_item_phase_and_status
3692 -- TYPE : Public
3693 -- Pre-reqs : None
3694 -- PROCEDURE : Changes item phase and status
3695 --
3696 ------------------------------------------------------------------------------
3697 PROCEDURE Process_item_phase_and_status
3698 (p_api_version IN NUMBER
3699 ,p_commit IN VARCHAR2
3700 ,p_init_msg_list IN VARCHAR2
3701 ,p_transaction_type IN VARCHAR2
3702 ,p_inventory_item_id IN NUMBER
3703 ,p_item_number IN VARCHAR2
3704 ,p_organization_id IN NUMBER
3705 ,p_organization_code IN VARCHAR2
3706 ,p_revision_id IN NUMBER
3707 ,p_revision IN VARCHAR2
3708 ,p_implement_changes IN VARCHAR2
3709 ,p_status IN VARCHAR2
3710 ,p_effective_date IN DATE
3711 ,p_lifecycle_id IN NUMBER
3712 ,p_phase_id IN NUMBER
3713 ,p_new_effective_date IN DATE
3714 ,x_return_status OUT NOCOPY VARCHAR2
3715 ,x_msg_count OUT NOCOPY NUMBER
3716 ,x_msg_data OUT NOCOPY VARCHAR2
3717 ) IS
3718 l_api_name VARCHAR2(30);
3719 l_api_version NUMBER;
3720 l_organization_id mtl_system_items_b.organization_id%TYPE;
3721 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
3722 l_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE;
3723 l_approval_status mtl_system_items_b.approval_status%TYPE;
3724 l_revision_id mtl_item_revisions_b.revision_id%TYPE;
3725 l_revision mtl_item_revisions_b.revision%TYPE;
3726 l_curr_cc_id mtl_system_items_b.item_catalog_group_id%TYPE;
3727 l_curr_lifecycle_id mtl_system_items_b.lifecycle_id%TYPE;
3728 l_curr_phase_id mtl_system_items_b.current_phase_id%TYPE;
3729 l_future_phase_id mtl_system_items_b.current_phase_id%TYPE;
3730 l_item_sequence NUMBER;
3731 l_phase_sequence NUMBER;
3732 l_curr_status mtl_system_items_b.inventory_item_status_code%TYPE;
3733 l_policy_code VARCHAR2(99);
3734 l_policy_co_required VARCHAR2(99);
3735 l_policy_not_allowed VARCHAR2(99);
3736 l_sysdate DATE;
3737 l_effective_date DATE;
3738 l_invalid_flag BOOLEAN;
3739 l_change_status_flag BOOLEAN;
3740 l_dummy_char VARCHAR2(32767);
3741 l_dummy_number NUMBER;
3742 l_revision_master_controlled VARCHAR2(1);
3743 l_status_master_controlled VARCHAR2(1);
3744 l_is_master_org VARCHAR2(1);
3745 l_org_name hr_all_organization_units_vl.name%TYPE;
3746 l_error_message fnd_new_messages.message_name%TYPE;
3747 l_priv_function_name fnd_form_functions.function_name%TYPE;
3748 l_user_id NUMBER;
3749
3750 CURSOR c_get_item_det (cp_inventory_item_id IN NUMBER
3751 ,cp_organization_id IN NUMBER) IS
3752 SELECT item_catalog_group_id, lifecycle_id,
3753 current_phase_id, inventory_item_status_code
3754 FROM mtl_system_items_b
3755 WHERE inventory_item_id = cp_inventory_item_id
3756 AND organization_id = cp_organization_id;
3757
3758 CURSOR c_get_item_rev_det (cp_inventory_item_id IN NUMBER
3759 ,cp_organization_id IN NUMBER
3760 ,cp_revision_id IN NUMBER) IS
3761 SELECT itm.item_catalog_group_id, rev.lifecycle_id, rev.current_phase_id, itm.inventory_item_status_code
3762 FROM mtl_system_items_b itm, mtl_item_revisions_b rev
3763 WHERE itm.inventory_item_id = cp_inventory_item_id
3764 AND itm.organization_id = cp_organization_id
3765 AND rev.inventory_item_id = itm.inventory_item_id
3766 AND rev.organization_id = itm.organization_id --changed = rev.organization_id to itm.organization_id bug 7324207
3767 AND rev.revision_id = cp_revision_id; --changed =rev.revision_id to cp_revision_id bug 7324207
3768
3769 CURSOR c_get_phase_seq (cp_phase_id IN NUMBER ) IS
3770 SELECT p1.display_sequence
3771 FROM PA_PROJ_ELEMENT_VERSIONS P1
3772 WHERE P1.PROJ_ELEMENT_ID = cp_phase_id;
3773
3774 CURSOR c_get_next_phase (cp_lifecycle_id IN NUMBER
3775 ,cp_phase_id IN NUMBER ) IS
3776 SELECT p1.proj_element_id, p1.display_sequence
3777 FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
3778 WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
3779 AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
3780 AND P1.display_sequence >
3781 (SELECT P3.display_sequence
3782 FROM PA_PROJ_ELEMENT_VERSIONS P3
3783 WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
3784 AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
3785 )
3786 ORDER BY p1.DISPLAY_SEQUENCE ASC;
3787
3788 CURSOR c_get_priv_phase (cp_lifecycle_id IN NUMBER
3789 ,cp_phase_id IN NUMBER ) IS
3790 SELECT p1.proj_element_id, p1.display_sequence
3791 FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
3792 WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
3793 AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
3794 AND P1.display_sequence <
3795 (SELECT P3.display_sequence
3796 FROM PA_PROJ_ELEMENT_VERSIONS P3
3797 WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
3798 AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
3799 )
3800 ORDER BY p1.DISPLAY_SEQUENCE DESC;
3801
3802
3803 BEGIN
3804 l_api_name := 'Process_item_phase_and_status';
3805 l_api_version := 1.0;
3806 x_return_status := FND_API.G_RET_STS_SUCCESS;
3807 l_user_id := FND_GLOBAL.User_Id;
3808 l_policy_co_required := 'CHANGE_ORDER_REQUIRED';
3809 l_policy_not_allowed := 'NOT_ALLOWED';
3810 code_debug(l_api_name ||' started with params -- effective date '|| p_effective_date);
3811 code_debug(' p_api_version '|| p_api_version||' p_commit '||p_commit||' p_init_msg_list '||p_init_msg_list );
3812 code_debug(' p_transaction_type '||p_transaction_type ||' p_inventory_item_id '||p_inventory_item_id||' p_item_number '||p_item_number );
3813 code_debug(' p_organization_id '||p_organization_id ||' p_organization_code '||p_organization_code||' p_revision_id '||p_revision_id );
3814 code_debug(' p_revision '||p_revision ||' p_implement_changes '||p_implement_changes||' p_status '||p_status );
3815
3816 IF FND_API.To_Boolean( p_commit ) THEN
3817 SAVEPOINT PROCESS_ITEM_PHASE_SP;
3818 END IF;
3819 --
3820 -- Initialize message list
3821 --
3822 IF FND_API.To_Boolean(p_init_msg_list) THEN
3823 FND_MSG_PUB.Initialize;
3824 END IF;
3825 code_debug(l_api_name||' msg pub initialized ' );
3826 --
3827 --Standard checks
3828 --
3829 IF NOT FND_API.Compatible_API_Call (l_api_version
3830 ,p_api_version
3831 ,l_api_name
3832 ,g_pkg_name) THEN
3833 code_debug (l_api_version ||' invalid api version ');
3834 RAISE FND_API.G_EXC_ERROR;
3835 END IF;
3836 --
3837 -- check for invalid params
3838 --
3839 IF ( p_transaction_type IS NULL
3840 OR
3841 p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
3842 ,EGO_ITEM_PUB.G_TTYPE_DELETE
3843 ,EGO_ITEM_PUB.G_TTYPE_PROMOTE
3844 ,EGO_ITEM_PUB.G_TTYPE_DEMOTE
3845 ,EGO_ITEM_PUB.G_TTYPE_CHANGE_STATUS
3846 )
3847 OR
3848 ( p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
3849 ,EGO_ITEM_PUB.G_TTYPE_DELETE)
3850 AND
3851 (p_revision IS NOT NULL OR p_revision_id IS NOT NULL)
3852 AND
3853 p_status IS NOT NULL
3854 )
3855 OR
3856 (p_inventory_item_id IS NULL AND p_item_number IS NULL)
3857 OR
3858 (p_organization_id IS NULL AND p_organization_code IS NULL)
3859 ) THEN
3860 --
3861 -- inalid parameters passed
3862 --
3863 code_debug (l_api_version ||' invalid parameters passed ');
3864 fnd_message.Set_Name(G_APP_NAME, G_INVALID_PARAMS_MSG);
3865 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
3866 fnd_message.Set_Token(G_PROC_NAME_TOKEN, l_api_name);
3867 FND_MSG_PUB.Add;
3868 RAISE FND_API.G_EXC_ERROR;
3869 END IF;
3870 l_change_status_flag := TRUE;
3871 l_organization_id := p_organization_id;
3872 l_inventory_item_id := p_inventory_item_id;
3873 l_item_number := p_item_number;
3874 l_sysdate := SYSDATE;
3875
3876 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
3877 l_effective_date := NVL(p_new_effective_date,l_sysdate);
3878 ELSIF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_PROMOTE
3879 ,EGO_ITEM_PUB.G_TTYPE_DEMOTE
3880 ,EGO_ITEM_PUB.G_TTYPE_CHANGE_STATUS) THEN
3881 l_effective_date := NVL(p_effective_date, l_sysdate);
3882 ELSE
3883 l_effective_date := l_sysdate;
3884 END IF;
3885
3886 IF date_check (p_start_date => l_effective_date
3887 ,p_end_date => l_sysdate
3888 ,p_validation_type => G_LT_VAL
3889 ) THEN
3890 code_debug (l_api_name ||' effective date is less than system date ');
3891 l_change_status_flag := FALSE;
3892 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_INVALID_EFFCT_DATE');
3893 fnd_msg_pub.Add;
3894 ELSE
3895 code_debug (l_api_name ||' effective date is valid ');
3896 END IF;
3897 --
3898 -- validate organization details
3899 --
3900 IF NOT validate_org (x_organization_id => l_organization_id
3901 ,p_organization_code => p_organization_code
3902 ,p_set_message => G_TRUE) THEN
3903 code_debug (l_api_name ||' invalid organization ');
3904 l_change_status_flag := FALSE;
3905 ELSE
3906 code_debug (l_api_name ||' valid organization ');
3907 SELECT name
3908 INTO l_org_name
3909 FROM hr_all_organization_units_vl
3910 WHERE organization_id = l_organization_id;
3911 --
3912 -- validate item details
3913 --
3914 IF NOT validate_item (x_inventory_item_id => l_inventory_item_id
3915 ,x_item_number => l_item_number
3916 ,x_approval_status => l_approval_status
3917 ,p_organization_id => l_organization_id
3918 ,p_set_message => G_TRUE) THEN
3919 code_debug (l_api_name ||' invalid item ');
3920 l_change_status_flag := FALSE;
3921 ELSE
3922 code_debug (l_api_name ||' valid item ');
3923 IF NVL(l_approval_status,'A') <> 'A' THEN
3924 --
3925 -- item is not approved no operations permitted
3926 --
3927 code_debug (l_api_name ||' unapproved item ');
3928 l_change_status_flag := FALSE;
3929 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_NOT_APPROVED');
3930 fnd_msg_pub.Add;
3931 ELSE
3932 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_PROMOTE THEN
3933 l_priv_function_name := G_FN_NAME_PROMOTE;
3934 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DEMOTE THEN
3935 l_priv_function_name := G_FN_NAME_DEMOTE;
3936 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_CHANGE_STATUS THEN
3937 l_priv_function_name := G_FN_NAME_CHANGE_STATUS;
3938 END IF;
3939 IF NOT has_role_on_item
3940 (p_function_name => l_priv_function_name
3941 ,p_inventory_item_id => l_inventory_item_id
3942 ,p_item_number => l_item_number
3943 ,p_organization_id => l_organization_id
3944 ,p_organization_name => NULL
3945 ,p_user_id => l_user_id
3946 ,p_party_id => NULL
3947 ,p_set_message => G_TRUE
3948 ) THEN
3949 code_debug(l_api_name ||' user does not have privilege to perform specified action '||p_transaction_type);
3950 l_change_status_flag := FALSE;
3951 ELSE
3952 code_debug(l_api_name ||' user has privilege to perform the action '||l_priv_function_name);
3953 END IF;
3954 --
3955 -- validate revision details
3956 --
3957 l_revision_id := p_revision_id;
3958 l_revision := p_revision;
3959 IF (l_revision IS NOT NULL OR l_revision_id IS NOT NULL) THEN
3960 IF NOT validate_item_rev
3961 (x_revision_id => l_revision_id
3962 ,x_revision => l_revision
3963 ,p_inventory_item_id => l_inventory_item_id
3964 ,p_organization_id => l_organization_id
3965 ,p_set_message => G_TRUE) THEN
3966 code_debug (l_api_name ||' invalid item revision ');
3967 l_change_status_flag := FALSE;
3968 ELSE
3969 code_debug (l_api_name ||' valid item revision ');
3970 --
3971 -- context of rev
3972 --
3973 OPEN c_get_item_rev_det (cp_inventory_item_id => l_inventory_item_id
3974 ,cp_organization_id => l_organization_id
3975 ,cp_revision_id => l_revision_id
3976 );
3977 FETCH c_get_item_rev_det INTO l_curr_cc_id, l_curr_lifecycle_id, l_curr_phase_id, l_curr_status;
3978 CLOSE c_get_item_rev_det;
3979 END IF;
3980 ELSE
3981 --
3982 -- context of item
3983 --
3984 OPEN c_get_item_det (cp_inventory_item_id => l_inventory_item_id
3985 ,cp_organization_id => l_organization_id
3986 );
3987 FETCH c_get_item_det INTO l_curr_cc_id, l_curr_lifecycle_id, l_curr_phase_id, l_curr_status;
3988 CLOSE c_get_item_det;
3989 END IF;
3990 IF ( p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_PROMOTE, EGO_ITEM_PUB.G_TTYPE_DEMOTE)
3991 AND
3992 l_curr_lifecycle_id IS NULL
3993 ) THEN
3994 l_change_status_flag := FALSE;
3995 code_debug (l_api_name ||' no lifecycle associated to item / revision ');
3996 IF l_revision_id IS NULL THEN
3997 fnd_message.Set_Name(G_APP_NAME, 'EGO_ITEM_NO_LC_FOR_CHANGE');
3998 ELSE
3999 fnd_message.Set_Name(G_APP_NAME, 'EGO_REV_NO_LC_FOR_CHANGE');
4000 END IF;
4001 fnd_msg_pub.Add;
4002 END IF;
4003 END IF; -- approval status
4004 END IF; -- validate item
4005 END IF; -- validate org
4006
4007 IF NOT l_change_status_flag THEN
4008 --
4009 -- logical set of errors completed
4010 -- further validations assume that there are no errors
4011 --
4012 RAISE FND_API.G_EXC_ERROR;
4013 END IF;
4014
4015 IF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_PROMOTE, EGO_ITEM_PUB.G_TTYPE_DEMOTE) THEN
4016 --
4017 -- check if there is a project associated
4018 --
4019 IF 'TRUE' = EGO_LIFECYCLE_USER_PUB.Has_LC_Tracking_Project
4020 (p_inventory_item_id => l_inventory_item_id
4021 ,p_organization_id => l_organization_id
4022 ,p_revision => l_revision) THEN
4023 code_debug (l_api_name ||' lc project associated to item / revision ');
4024 l_change_status_flag := FALSE;
4025 IF l_revision IS NOT NULL THEN
4026 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_REV_LC_PROJ_EXISTS');
4027 fnd_message.set_token('REVISION', l_revision);
4028 ELSE
4029 fnd_message.Set_Name(G_APP_NAME,'ITEM_LC_PROJ_EXISTS');
4030 END IF;
4031 fnd_message.set_token('ITEM_NUMBER', l_item_number);
4032 fnd_message.set_token('ORG_NAME', l_org_name);
4033 fnd_msg_pub.Add;
4034 END IF;
4035
4036 IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_PROMOTE THEN
4037 OPEN c_get_next_phase (cp_lifecycle_id => l_curr_lifecycle_id
4038 ,cp_phase_id => l_curr_phase_id
4039 );
4040 FETCH c_get_next_phase INTO l_future_phase_id, l_phase_sequence;
4041 IF c_get_next_phase%NOTFOUND THEN
4042 CLOSE c_get_next_phase;
4043 code_debug (l_api_name ||' no phase to promote ');
4044 l_change_status_flag := FALSE;
4045 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_CANNOT_PROMOTE');
4046 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'LIFECYCLE'
4047 ,p_proj_element_id => l_curr_lifecycle_id);
4048 fnd_message.set_token('LIFE_CYCLE', l_dummy_char);
4049 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'PHASE'
4050 ,p_proj_element_id => l_curr_phase_id);
4051 fnd_message.set_token('PHASE', l_dummy_char);
4052 fnd_msg_pub.Add;
4053 ELSE
4054 CLOSE c_get_next_phase;
4055 END IF;
4056 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DEMOTE THEN
4057 OPEN c_get_priv_phase (cp_lifecycle_id => l_curr_lifecycle_id
4058 ,cp_phase_id => l_curr_phase_id
4059 );
4060 FETCH c_get_priv_phase INTO l_future_phase_id, l_phase_sequence;
4061 IF c_get_priv_phase%NOTFOUND THEN
4062 CLOSE c_get_priv_phase;
4063 code_debug (l_api_name ||' no phase to demote ');
4064 l_change_status_flag := FALSE;
4065 fnd_message.Set_Name(G_APP_NAME,'EGO_ITEM_CANNOT_DEMOTE');
4066 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'LIFECYCLE'
4067 ,p_proj_element_id => l_curr_lifecycle_id);
4068 fnd_message.set_token('LIFE_CYCLE', l_dummy_char);
4069 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'PHASE'
4070 ,p_proj_element_id => l_curr_phase_id);
4071 fnd_message.set_token('PHASE', l_dummy_char);
4072 fnd_msg_pub.Add;
4073 fnd_msg_pub.Add;
4074 ELSE
4075 CLOSE c_get_priv_phase;
4076 END IF;
4077 END IF;
4078 --
4079 -- get the policy for changes
4080 --
4081 code_debug (l_api_name ||' calling EGO_LIFECYCLE_USER_PUB.Get_Policy_For_Phase_Change ');
4082 EGO_LIFECYCLE_USER_PUB.Get_Policy_For_Phase_Change
4083 ( p_api_version => p_api_version
4084 , p_project_id => NULL
4085 , p_inventory_item_id => l_inventory_item_id
4086 , p_organization_id => l_organization_id
4087 , p_curr_phase_id => l_curr_phase_id
4088 , p_future_phase_id => l_future_phase_id
4089 , p_phase_change_code => p_transaction_type
4090 , p_lifecycle_id => l_curr_lifecycle_id
4091 , x_policy_code => l_policy_code
4092 , x_return_status => x_return_status
4093 , x_errorcode => l_dummy_char
4094 , x_msg_count => x_msg_count
4095 , x_msg_data => x_msg_data
4096 );
4097 code_debug (l_api_name ||' returning EGO_LIFECYCLE_USER_PUB.Get_Policy_For_Phase_Change with status '||x_return_status);
4098 IF l_policy_code IN (l_policy_co_required, l_policy_not_allowed) THEN
4099 l_change_status_flag := FALSE;
4100 -- decide the message based upon
4101 -- item /item revision
4102 -- change order required OR changes not allowed
4103 IF l_policy_code = l_policy_co_required THEN
4104 code_debug (l_api_name ||' policy requires CO to promote / demote ');
4105 IF l_revision_id IS NULL THEN
4106 l_error_message := 'EGO_ITEM_NO_PROMOTE';
4107 ELSE
4108 l_error_message := 'EGO_ITEM_REV_NO_PROMOTE';
4109 END IF;
4110 ELSIF l_policy_code = l_policy_not_allowed THEN
4111 code_debug (l_api_name ||' policy says not allowed to promote / demote ');
4112 IF l_revision_id IS NULL THEN
4113 l_error_message := 'EGO_ITEM_NO_DEMOTE';
4114 ELSE
4115 l_error_message := 'EGO_ITEM_REV_NO_DEMOTE';
4116 END IF;
4117 END IF;
4118 fnd_message.Set_Name(G_APP_NAME,l_error_message);
4119 fnd_message.set_token('ITEM_NUMBER', l_item_number);
4120 IF l_revision_id IS NOT NULL THEN
4121 fnd_message.set_token('REVISION', l_revision);
4122 END IF;
4123 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'LIFECYCLE'
4124 ,p_proj_element_id => l_curr_lifecycle_id);
4125 fnd_message.set_token('LIFE_CYCLE', l_dummy_char);
4126 l_dummy_char := get_lifecycle_name (p_lc_phase_type => 'PHASE'
4127 ,p_proj_element_id => l_curr_phase_id);
4128 fnd_message.set_token('PHASE', l_dummy_char);
4129 SELECT concatenated_segments
4130 INTO l_dummy_char
4131 FROM MTL_ITEM_CATALOG_GROUPS_KFV
4132 WHERE ITEM_CATALOG_GROUP_ID = (
4133 SELECT item_catalog_group_id
4134 FROM (SELECT item_catalog_group_id
4135 FROM mtl_item_catalog_groups_b ic
4136 WHERE EXISTS
4137 ( SELECT olc.object_classification_code CatalogId
4138 FROM ego_obj_type_lifecycles olc, fnd_objects o
4139 WHERE o.obj_name = G_EGO_ITEM
4140 AND olc.object_id = o.object_id
4141 AND olc.lifecycle_id = l_curr_lifecycle_id
4142 AND olc.object_classification_code = l_curr_cc_id
4143 )
4144 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
4145 START WITH item_catalog_group_id = l_curr_cc_id
4146 ) CAT_HIER
4147 WHERE ROWNUM = 1
4148 );
4149 fnd_message.set_token('CATALOG_CATEGORY_NAME', l_dummy_char);
4150 fnd_msg_pub.Add;
4151 END IF;
4152 ELSIF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE) THEN
4153 -- check if the user has privilege to update the item.
4154 IF p_phase_id IS NOT NULL THEN
4155 -- user changing phase
4156 OPEN c_get_phase_seq(cp_phase_id => l_curr_phase_id);
4157 FETCH c_get_phase_seq INTO l_item_sequence;
4158 CLOSE c_get_phase_seq;
4159 OPEN c_get_phase_seq(cp_phase_id => p_phase_id);
4160 FETCH c_get_phase_seq INTO l_phase_sequence;
4161 CLOSE c_get_phase_seq;
4162 IF l_item_sequence < l_phase_sequence THEN
4163 l_priv_function_name := G_FN_NAME_PROMOTE;
4164 l_error_message := 'EGO_NO_PRIV_PROMOTE';
4165 ELSIF l_item_sequence > l_phase_sequence THEN
4166 l_priv_function_name := G_FN_NAME_DEMOTE;
4167 l_error_message := 'EGO_NO_PRIV_DEMOTE';
4168 ELSE
4169 -- phase is same, doing status change
4170 l_priv_function_name := G_FN_NAME_CHANGE_STATUS;
4171 l_error_message := 'EGO_NO_PRIV_CHANGE_STATUS';
4172 END IF;
4173 ELSE
4174 l_priv_function_name := G_FN_NAME_CHANGE_STATUS;
4175 l_error_message := 'EGO_NO_PRIV_CHANGE_STATUS';
4176 END IF;
4177 -- 4052565
4178 -- modified call to has_role_on_item from validate_role_privilege
4179 IF NOT has_role_on_item
4180 (p_function_name => l_priv_function_name
4181 ,p_inventory_item_id => l_inventory_item_id
4182 ,p_item_number => l_item_number
4183 ,p_organization_id => l_organization_id
4184 ,p_organization_name => NULL
4185 ,p_user_id => l_user_id
4186 ,p_party_id => NULL
4187 ,p_set_message => G_TRUE
4188 ) THEN
4189 code_debug(l_api_name ||' user does not have privilege to update the existing change '||p_transaction_type);
4190 l_change_status_flag := FALSE;
4191 fnd_message.Set_Name(G_APP_NAME,l_error_message);
4192 fnd_message.set_token('USER', FND_GLOBAL.USER_NAME);
4193 fnd_message.set_token('ITEM_NUMBER', l_item_number);
4194 fnd_message.set_token('ORGANIZATION', l_org_name);
4195 fnd_msg_pub.Add;
4196 END IF;
4197 END IF;
4198
4199 IF l_change_status_flag THEN
4200 code_debug (l_api_name ||' calling EGO_ITEM_LC_IMP_PC_PUB.Create_Pending_Phase_Change ');
4201 IF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_PROMOTE, EGO_ITEM_PUB.G_TTYPE_DEMOTE) THEN
4202 EGO_ITEM_LC_IMP_PC_PUB.Create_Pending_Phase_Change
4203 (p_api_version => p_api_version
4204 ,p_commit => FND_API.G_FALSE
4205 ,p_inventory_item_id => l_inventory_item_id
4206 ,p_item_number => l_item_number
4207 ,p_organization_id => l_organization_id
4208 ,p_effective_date => l_effective_date
4209 ,p_pending_flag => NULL
4210 ,p_revision => l_revision
4211 ,p_lifecycle_id => l_curr_lifecycle_id
4212 ,p_phase_id => l_future_phase_id
4213 ,p_status_code => p_status
4214 ,p_change_id => NULL
4215 ,p_change_line_id => NULL
4216 ,p_perform_security_check => FND_API.G_FALSE
4217 ,x_return_status => x_return_status
4218 ,x_errorcode => l_dummy_char
4219 ,x_msg_count => x_msg_count
4220 ,x_msg_data => x_msg_data
4221 );
4222 ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_CHANGE_STATUS THEN
4223 EGO_ITEM_LC_IMP_PC_PUB.Create_Pending_Phase_Change
4224 (p_api_version => p_api_version
4225 ,p_commit => FND_API.G_FALSE
4226 ,p_inventory_item_id => l_inventory_item_id
4227 ,p_item_number => l_item_number
4228 ,p_organization_id => l_organization_id
4229 ,p_effective_date => l_effective_date
4230 ,p_pending_flag => NULL
4231 ,p_revision => NULL
4232 ,p_lifecycle_id => NULL
4233 ,p_phase_id => NULL
4234 ,p_status_code => p_status
4235 ,p_change_id => NULL
4236 ,p_change_line_id => NULL
4237 ,p_perform_security_check => FND_API.G_FALSE
4238 ,x_return_status => x_return_status
4239 ,x_errorcode => l_dummy_char
4240 ,x_msg_count => x_msg_count
4241 ,x_msg_data => x_msg_data
4242 );
4243 ELSIF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE) THEN
4244 EGO_ITEM_LC_IMP_PC_PUB.Modify_Pending_Phase_Change
4245 (p_api_version => p_api_version
4246 ,p_commit => FND_API.G_FALSE
4247 ,p_transaction_type => p_transaction_type
4248 ,p_inventory_item_id => l_inventory_item_id
4249 ,p_organization_id => l_organization_id
4250 ,p_revision_id => l_revision_id
4251 ,p_lifecycle_id => p_lifecycle_id
4252 ,p_phase_id => p_phase_id
4253 ,p_status_code => p_status
4254 ,p_change_id => NULL
4255 ,p_change_line_id => NULL
4256 ,p_effective_date => p_effective_date
4257 ,p_new_effective_date => l_effective_date
4258 ,p_perform_security_check => FND_API.G_FALSE
4259 ,x_return_status => x_return_status
4260 ,x_errorcode => l_dummy_char
4261 ,x_msg_count => x_msg_count
4262 ,x_msg_data => x_msg_data
4263 );
4264 END IF;
4265
4266 code_debug (l_api_name ||' return status from ego_item_lc_imp_pc_pub.create_pending_phase_change '||x_return_status);
4267 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4268 code_debug (l_api_name ||' cannot create/modify/delete pending phase change '||x_msg_data);
4269 --
4270 -- this will occur only in case of exception
4271 -- all valid values are passed.
4272 --
4273 IF x_msg_count = 1 THEN
4274 fnd_message.Set_Name(G_APP_NAME,'EGO_GENERIC_MSG_TEXT');
4275 fnd_message.set_token('MESSAGE', x_msg_data);
4276 fnd_msg_pub.Add;
4277 END IF;
4278 RAISE FND_API.G_EXC_ERROR;
4279 ELSIF FND_API.To_Boolean(p_implement_changes) THEN
4280 code_debug (l_api_name ||' calling EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes ');
4281 l_is_master_org := EGO_ITEM_LC_IMP_PC_PUB.get_master_org_status(l_organization_id);
4282 l_revision_master_controlled := FND_API.g_false;
4283 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
4284 EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes
4285 (p_api_version => p_api_version
4286 ,p_inventory_item_id => l_inventory_item_id
4287 ,p_organization_id => l_organization_id
4288 ,p_revision_id => l_revision_id
4289 ,p_revision_master_controlled => l_revision_master_controlled
4290 ,p_status_master_controlled => l_status_master_controlled
4291 ,p_is_master_org => l_is_master_org
4292 ,p_perform_security_check => FND_API.G_FALSE
4293 ,x_return_status => x_return_status
4294 ,x_errorcode => l_dummy_char
4295 ,x_msg_count => x_msg_count
4296 ,x_msg_data => x_msg_data
4297 );
4298 code_debug (l_api_name ||' return status from ego_item_lc_imp_pc_pub.Implement_Pending_Changes '||x_return_status);
4299 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4300 code_debug (l_api_name ||' cannot implement changes '||x_msg_data);
4301 IF x_msg_count = 1 THEN
4302 fnd_message.Set_Name(G_APP_NAME,'EGO_GENERIC_MSG_TEXT');
4303 fnd_message.set_token('MESSAGE', x_msg_data);
4304 fnd_msg_pub.Add;
4305 END IF;
4306 RAISE FND_API.G_EXC_ERROR;
4307 END IF;
4308 END IF;
4309 ELSE
4310 code_debug (l_api_name ||' flashing all errors ');
4311 RAISE FND_API.G_EXC_ERROR;
4312 END IF;
4313 --
4314 -- commit data
4315 --
4316 IF FND_API.To_Boolean( p_commit ) THEN
4317 COMMIT WORK;
4318 END IF;
4319 x_return_status := FND_API.G_RET_STS_SUCCESS;
4320 EXCEPTION
4321 WHEN FND_API.G_EXC_ERROR THEN
4322 IF FND_API.To_Boolean( p_commit ) THEN
4323 ROLLBACK TO PROCESS_ITEM_PHASE_SP;
4324 END IF;
4325 x_return_status := FND_API.G_RET_STS_ERROR;
4326 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
4327 ,p_count => x_msg_count
4328 ,p_data => x_msg_data);
4329 WHEN OTHERS THEN
4330 IF FND_API.To_Boolean( p_commit ) THEN
4331 ROLLBACK TO PROCESS_ITEM_PHASE_SP;
4332 END IF;
4333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4334 IF c_get_item_det%ISOPEN THEN
4335 CLOSE c_get_item_det;
4336 END IF;
4337 IF c_get_item_rev_det%ISOPEN THEN
4338 CLOSE c_get_item_rev_det;
4339 END IF;
4340 IF c_get_phase_seq%ISOPEN THEN
4341 CLOSE c_get_phase_seq;
4342 END IF;
4343 IF c_get_next_phase%ISOPEN THEN
4344 CLOSE c_get_next_phase;
4345 END IF;
4346 IF c_get_priv_phase%ISOPEN THEN
4347 CLOSE c_get_priv_phase;
4348 END IF;
4349 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
4350 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
4351 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
4352 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
4353 FND_MSG_PUB.Add;
4354 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
4355 ,p_count => x_msg_count
4356 ,p_data => x_msg_data);
4357 code_debug(' Exception in '||l_api_name||' : ' ||x_msg_data );
4358 END Process_item_phase_and_status;
4359
4360 ------------------------------------------------------------------------------
4361 -- Start OF comments
4362 -- API name : Implement_Item_Pending_Changes
4363 -- TYPE : Public
4364 -- Pre-reqs : None
4365 -- PROCEDURE : Implement the pending changes on the item
4366 --
4367 ------------------------------------------------------------------------------
4368 PROCEDURE Implement_Item_Pending_Changes
4369 (p_api_version IN NUMBER
4370 ,p_commit IN VARCHAR2
4371 ,p_init_msg_list IN VARCHAR2
4372 ,p_inventory_item_id IN NUMBER
4373 ,p_item_number IN VARCHAR2
4374 ,p_organization_id IN NUMBER
4375 ,p_organization_code IN VARCHAR2
4376 ,p_revision_id IN NUMBER
4377 ,p_revision IN VARCHAR2
4378 ,x_return_status OUT NOCOPY VARCHAR2
4379 ,x_msg_count OUT NOCOPY NUMBER
4380 ,x_msg_data OUT NOCOPY VARCHAR2
4381 ) IS
4382 l_api_name VARCHAR2(30);
4383 l_api_version NUMBER;
4384 l_organization_id mtl_system_items_b.organization_id%TYPE;
4385 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
4386 l_item_number mtl_system_items_b_kfv.concatenated_segments%TYPE;
4387 l_approval_status mtl_system_items_b.approval_status%TYPE;
4388 l_revision_id mtl_item_revisions_b.revision_id%TYPE;
4389 l_revision mtl_item_revisions_b.revision%TYPE;
4390 l_dummy_char VARCHAR2(999);
4391 l_revision_master_controlled VARCHAR2(1);
4392 l_status_master_controlled VARCHAR2(1);
4393 l_is_master_org VARCHAR2(1);
4394 l_invalid_flag BOOLEAN;
4395 l_implement_flag BOOLEAN;
4396
4397 BEGIN
4398 l_api_name := 'Implement_Item_Pending_Changes';
4399 l_api_version := 1.0;
4400 code_debug(l_api_name ||' started with params ');
4401 code_debug(' p_api_version '|| p_api_version||' p_commit '||p_commit||' p_init_msg_list '||p_init_msg_list );
4402 code_debug(' p_inventory_item_id '||p_inventory_item_id||' p_item_number '||p_item_number||' p_revision '||p_revision );
4403 code_debug(' p_organization_id '||p_organization_id ||' p_organization_code '||p_organization_code||' p_revision_id '||p_revision_id );
4404 IF FND_API.To_Boolean( p_commit ) THEN
4405 SAVEPOINT IMPLEMENT_CHANGES_SP;
4406 END IF;
4407 --
4408 -- Initialize message list
4409 --
4410 IF FND_API.To_Boolean(p_init_msg_list) THEN
4411 FND_MSG_PUB.Initialize;
4412 END IF;
4413 code_debug(l_api_name||' msg pub initialized ' );
4414 --
4415 --Standard checks
4416 --
4417 IF NOT FND_API.Compatible_API_Call (l_api_version
4418 ,p_api_version
4419 ,l_api_name
4420 ,g_pkg_name) THEN
4421 code_debug (l_api_name ||' invalid api version ');
4422 RAISE FND_API.G_EXC_ERROR;
4423 END IF;
4424 --
4425 -- check for mandatory params
4426 --
4427 IF ( (p_inventory_item_id IS NULL AND p_item_number IS NULL)
4428 OR
4429 (p_organization_id IS NULL AND p_organization_code IS NULL)
4430 ) THEN
4431 --
4432 -- invalid params passed
4433 --
4434 code_debug (l_api_name ||' invalid parameters passed ');
4435 fnd_message.Set_Name(G_APP_NAME, G_INVALID_PARAMS_MSG);
4436 fnd_message.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
4437 fnd_message.Set_Token(G_PROC_NAME_TOKEN, l_api_name);
4438 FND_MSG_PUB.Add;
4439 RAISE FND_API.G_EXC_ERROR;
4440 END IF;
4441
4442 --
4443 -- validate organization details
4444 --
4445 l_organization_id := p_organization_id;
4446 l_inventory_item_id := p_inventory_item_id;
4447 l_item_number := p_item_number;
4448 l_revision_id := p_revision_id;
4449 l_revision := p_revision;
4450 l_implement_flag := TRUE;
4451
4452 IF NOT validate_org (x_organization_id => l_organization_id
4453 ,p_organization_code => p_organization_code
4454 ,p_set_message => G_TRUE) THEN
4455 code_debug (l_api_name ||' invalid organiation passed ');
4456 l_implement_flag := FALSE;
4457 ELSE
4458 --
4459 -- validate item details
4460 --
4461 IF NOT validate_item (x_inventory_item_id => l_inventory_item_id
4462 ,x_item_number => l_item_number
4463 ,x_approval_status => l_approval_status
4464 ,p_organization_id => l_organization_id
4465 ,p_set_message => G_TRUE) THEN
4466 code_debug (l_api_name ||' invalid item passed ');
4467 l_implement_flag := FALSE;
4468 ELSE
4469 -- 4052565
4470 -- privilege check is now done in Implement_Pending_changes
4471 -- IF NOT validate_role_privilege (p_function_name => G_FN_NAME_CHANGE_STATUS
4472 -- ,p_inventory_item_id => l_inventory_item_id
4473 -- ,p_item_number => l_item_number
4474 -- ,p_organization_id => l_organization_id
4475 -- ,p_organization_name => NULL
4476 -- ,p_set_message => G_TRUE) THEN
4477 -- code_debug(l_api_name ||' user does not have privilege to implement pending changes');
4478 -- l_implement_flag := FALSE;
4479 -- END IF;
4480 --
4481 -- validate revision details
4482 --
4483 IF (l_revision_id IS NOT NULL OR l_revision IS NOT NULL) THEN
4484 IF NOT validate_item_rev
4485 (x_revision_id => l_revision_id
4486 ,x_revision => l_revision
4487 ,p_inventory_item_id => l_inventory_item_id
4488 ,p_organization_id => l_organization_id
4489 ,p_set_message => G_TRUE ) THEN
4490 code_debug (l_api_name ||' invalid revision passed ');
4491 l_implement_flag := FALSE;
4492 END IF;
4493 END IF;
4494 END IF;
4495 END IF;
4496
4497 IF l_implement_flag THEN
4498 code_debug (l_api_name ||' calling EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes ');
4499 l_is_master_org := EGO_ITEM_LC_IMP_PC_PUB.get_master_org_status(l_organization_id);
4500 l_revision_master_controlled := FND_API.g_false;
4501 l_status_master_controlled := EGO_ITEM_LC_IMP_PC_PUB.get_master_controlled_status();
4502 EGO_ITEM_LC_IMP_PC_PUB.Implement_Pending_Changes
4503 (p_api_version => p_api_version
4504 ,p_inventory_item_id => l_inventory_item_id
4505 ,p_organization_id => l_organization_id
4506 ,p_revision_id => l_revision_id
4507 ,p_revision_master_controlled => l_revision_master_controlled
4508 ,p_status_master_controlled => l_status_master_controlled
4509 ,p_is_master_org => l_is_master_org
4510 ,p_perform_security_check => FND_API.G_TRUE
4511 ,x_return_status => x_return_status
4512 ,x_errorcode => l_dummy_char
4513 ,x_msg_count => x_msg_count
4514 ,x_msg_data => x_msg_data
4515 );
4516 code_debug (l_api_name ||' return status from ego_item_lc_imp_pc_pub.Implement_Pending_Changes '||x_return_status);
4517 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4518 IF x_msg_count = 1 THEN
4519 code_debug (l_api_name ||' cannot implement changes for '||x_msg_data);
4520 fnd_message.Set_Name(G_APP_NAME,'EGO_GENERIC_MSG_TEXT');
4521 fnd_message.set_token('MESSAGE', x_msg_data);
4522 fnd_msg_pub.Add;
4523 END IF;
4524 RAISE FND_API.G_EXC_ERROR;
4525 END IF;
4526 ELSE
4527 code_debug (l_api_name ||' flashing all errors ');
4528 RAISE FND_API.G_EXC_ERROR;
4529 END IF;
4530 --
4531 -- commit data
4532 --
4533 IF FND_API.To_Boolean( p_commit ) THEN
4534 COMMIT WORK;
4535 END IF;
4536
4537 EXCEPTION
4538 WHEN FND_API.G_EXC_ERROR THEN
4539 IF FND_API.To_Boolean( p_commit ) THEN
4540 ROLLBACK TO IMPLEMENT_CHANGES_SP;
4541 END IF;
4542 x_return_status := FND_API.G_RET_STS_ERROR;
4543 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
4544 ,p_count => x_msg_count
4545 ,p_data => x_msg_data);
4546 WHEN OTHERS THEN
4547 IF FND_API.To_Boolean( p_commit ) THEN
4548 ROLLBACK TO IMPLEMENT_CHANGES_SP;
4549 END IF;
4550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4551 FND_MESSAGE.Set_Name(G_APP_NAME, G_PLSQL_ERR);
4552 FND_MESSAGE.Set_Token(G_PKG_NAME_TOKEN, G_PKG_NAME);
4553 FND_MESSAGE.Set_Token(G_API_NAME_TOKEN, l_api_name);
4554 FND_MESSAGE.Set_Token(G_SQL_ERR_MSG_TOKEN, SQLERRM);
4555 FND_MSG_PUB.Add;
4556 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
4557 ,p_count => x_msg_count
4558 ,p_data => x_msg_data);
4559 code_debug(' Exception in '||l_api_name||' : ' ||x_msg_data );
4560 END Implement_Item_Pending_Changes;
4561
4562
4563 -- -----------------------------------------------------------------------------
4564 -- Fix for Bug# 3945885.
4565 --
4566 -- API Name: Get_Seq_Gen_Item_Nums
4567 --
4568 -- Description:
4569 -- API to return a Sequence of Item Numbers, given the Item Catalog Group ID.
4570 --
4571 -- -----------------------------------------------------------------------------
4572 PROCEDURE Get_Seq_Gen_Item_Nums( p_item_catalog_group_id IN NUMBER
4573 ,p_org_id_tbl IN DBMS_SQL.VARCHAR2_TABLE
4574 ,x_item_num_tbl IN OUT NOCOPY EGO_VARCHAR_TBL_TYPE
4575 ) IS
4576
4577 -----------------------------------------------------------------------
4578 -- Variables used to query Item Number Generation Method
4579 -----------------------------------------------------------------------
4580 l_itemgen_method_table DBMS_SQL.VARCHAR2_TABLE;
4581 l_itemgen_seq_table DBMS_SQL.VARCHAR2_TABLE;
4582 l_itemgen_prefix_table DBMS_SQL.VARCHAR2_TABLE;
4583 l_itemgen_suffix_table DBMS_SQL.VARCHAR2_TABLE;
4584 l_itemgen_method_cursor INTEGER;
4585 l_itemgen_method_exec INTEGER;
4586 l_itemgen_method_rows_cnt NUMBER;
4587
4588 l_item_num VARCHAR(1000);
4589 l_item_num_tbl EGO_VARCHAR_TBL_TYPE;
4590 l_exists VARCHAR2(1);
4591 l_can_itemnum_gen BOOLEAN;
4592 l_itemgen_rownum NUMBER;
4593 l_new_itemgen_sql VARCHAR2(1000);
4594 -----------------------------------------------------------------------
4595
4596 l_itemgen_hierarchy_sql VARCHAR2(10000) :=
4597 ' SELECT EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID, '||
4598 ' EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID, '||
4599 ' DECODE(EgoNewItemReqSetupEO.ITEM_NUM_GEN_METHOD, '||
4600 ' null, DECODE(EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID, null, ''U'', ''I''), '||
4601 ' EgoNewItemReqSetupEO.ITEM_NUM_GEN_METHOD) ITEM_NUM_GEN_METHOD, '||
4602 ' EgoNewItemReqSetupEO.ITEM_NUM_SEQ_NAME, '||
4603 ' EgoNewItemReqSetupEO.PREFIX, '||
4604 ' EgoNewItemReqSetupEO.SUFFIX '||
4605 ' FROM MTL_ITEM_CATALOG_GROUPS_B EgoNewItemReqSetupEO '||
4606 ' CONNECT BY PRIOR EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID = '||
4607 ' EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID '||
4608 ' START WITH EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID = :ITEM_CATALOG_GROUP_ID ';
4609
4610
4611 CURSOR c_itemnum_exists_cursor (cp_item_number IN VARCHAR2
4612 ,cp_organization_id IN NUMBER ) IS
4613 SELECT 'x'
4614 FROM mtl_system_items_b_kfv
4615 WHERE concatenated_segments = cp_item_number
4616 AND organization_id = cp_organization_id;
4617
4618 BEGIN
4619
4620 l_itemgen_method_cursor := DBMS_SQL.OPEN_CURSOR;
4621 DBMS_SQL.PARSE(l_itemgen_method_cursor, l_itemgen_hierarchy_sql, DBMS_SQL.NATIVE);
4622
4623 LOOP -- Loop for every 2500 rows.
4624
4625 DBMS_SQL.DEFINE_ARRAY(
4626 c => l_itemgen_method_cursor -- cursor --
4627 , position => 3 -- select position --
4628 , c_tab => l_itemgen_method_table -- table of chars --
4629 , cnt => 2500 -- rows requested --
4630 , lower_bound => 1 -- start at --
4631 );
4632
4633 DBMS_SQL.DEFINE_ARRAY(
4634 c => l_itemgen_method_cursor -- cursor --
4635 , position => 4 -- select position --
4636 , c_tab => l_itemgen_seq_table -- table of chars --
4637 , cnt => 2500 -- rows requested --
4638 , lower_bound => 1 -- start at --
4639 );
4640
4641 DBMS_SQL.DEFINE_ARRAY(
4642 c => l_itemgen_method_cursor -- cursor --
4643 , position => 5 -- select position --
4644 , c_tab => l_itemgen_prefix_table -- table of chars --
4645 , cnt => 2500 -- rows requested --
4646 , lower_bound => 1 -- start at --
4647 );
4648
4649 DBMS_SQL.DEFINE_ARRAY(
4650 c => l_itemgen_method_cursor -- cursor --
4651 , position => 6 -- select position --
4652 , c_tab => l_itemgen_suffix_table -- table of chars --
4653 , cnt => 2500 -- rows requested --
4654 , lower_bound => 1 -- start at --
4655 );
4656
4657 DBMS_SQL.BIND_VARIABLE(l_itemgen_method_cursor, ':ITEM_CATALOG_GROUP_ID', p_item_catalog_group_id);
4658
4659 l_itemgen_method_exec := DBMS_SQL.EXECUTE(l_itemgen_method_cursor);
4660 l_itemgen_method_rows_cnt := DBMS_SQL.FETCH_ROWS(l_itemgen_method_cursor);
4661
4662 DBMS_SQL.COLUMN_VALUE(l_itemgen_method_cursor, 3, l_itemgen_method_table);
4663 DBMS_SQL.COLUMN_VALUE(l_itemgen_method_cursor, 4, l_itemgen_seq_table);
4664 DBMS_SQL.COLUMN_VALUE(l_itemgen_method_cursor, 5, l_itemgen_prefix_table);
4665 DBMS_SQL.COLUMN_VALUE(l_itemgen_method_cursor, 6, l_itemgen_suffix_table);
4666
4667 --DBMS_OUTPUT.PUT_LINE('load_item_oper_attr_values: Retrieved rows => '||To_char(l_itemgen_method_rows_cnt));
4668
4669 l_can_itemnum_gen := FALSE;
4670 l_itemgen_rownum := 0;
4671 FOR i IN 1..l_itemgen_method_table.COUNT LOOP
4672 --DBMS_OUTPUT.PUT_LINE('Item Gen Method => '|| l_itemgen_method_table(i));
4673 IF (l_itemgen_method_table(i) IN ('I','S')) THEN
4674 IF (l_itemgen_method_table(i) IN ('S')) THEN
4675 l_can_itemnum_gen := TRUE;
4676 l_itemgen_rownum := i;
4677 --DBMS_OUTPUT.PUT_LINE('Item Number will be generated using Row => '||i);
4678 EXIT;
4679 ELSE
4680 --DBMS_OUTPUT.PUT_LINE('Need to traverse up 1 more level');
4681 NULL;
4682 END IF; --end: IF (l_itemgen_method_table(i) IN ('S'))
4683 ELSE
4684 --DBMS_OUTPUT.PUT_LINE('Item Number cannot be generated !!! ');
4685 EXIT;
4686 END IF; --end: IF (l_itemgen_method_table(i) IN ('I','S'))
4687 END LOOP; --end: FOR (i:=0; i < l_itemgen_method_table.COUNT; i++)
4688
4689 l_new_itemgen_sql := '';
4690 IF (l_can_itemnum_gen) THEN
4691 ---------------------------------------------------------------------
4692 --As many Org IDs in the table, so many Item Numbers to be generated
4693 ---------------------------------------------------------------------
4694 FOR i IN 1..p_org_id_tbl.LAST
4695 LOOP --To generate ~requested number~ of Item Numbers
4696 LOOP -- To generate 1 Valid Item Number
4697 -----------------------------------------------------
4698 --Since this is a loop, re-set the string each time.
4699 -----------------------------------------------------
4700 l_new_itemgen_sql := '';
4701 l_new_itemgen_sql := l_new_itemgen_sql || ' SELECT ';
4702 l_new_itemgen_sql := l_new_itemgen_sql ||''''|| l_itemgen_prefix_table(l_itemgen_rownum) || ''' || ';
4703 l_new_itemgen_sql := l_new_itemgen_sql || l_itemgen_seq_table(l_itemgen_rownum) || '.NEXTVAL || ';
4704 l_new_itemgen_sql := l_new_itemgen_sql ||''''|| l_itemgen_suffix_table(l_itemgen_rownum)||'''';
4705 l_new_itemgen_sql := l_new_itemgen_sql || ' FROM DUAL ';
4706
4707 --DBMS_OUTPUT.PUT_LINE('l_new_itemgen_sql => '||l_new_itemgen_sql);
4708 EXECUTE IMMEDIATE l_new_itemgen_sql INTO l_item_num;
4709
4710 ------------------------------------------------------------------------
4711 --Check if this Item Number *doesnt* already exist.
4712 --NOTE: Convert the VARCHAR2 Org ID to NUMBER before calling the Cursor
4713 ------------------------------------------------------------------------
4714 OPEN c_itemnum_exists_cursor(l_item_num, FND_NUMBER.CANONICAL_TO_NUMBER(p_org_id_tbl(i)));
4715 FETCH c_itemnum_exists_cursor INTO l_exists;
4716 IF (c_itemnum_exists_cursor%NOTFOUND) THEN
4717 CLOSE c_itemnum_exists_cursor;
4718 EXIT;
4719 END IF; --end: IF (c_itemnum_exists_cursor%NOTFOUND) THEN
4720 CLOSE c_itemnum_exists_cursor;
4721 END LOOP; --end: To generate 1 Valid Item Number
4722
4723 -----------------------------------------------------------------------
4724 -- If NULL, then create a New VARCHAR table.
4725 -----------------------------------------------------------------------
4726 IF l_item_num_tbl IS NULL THEN
4727 l_item_num_tbl := EGO_VARCHAR_TBL_TYPE();
4728 END IF;
4729
4730 -----------------------------------------------------------------------
4731 -- Add newly generated Item Number to the end of existing table.
4732 -----------------------------------------------------------------------
4733 l_item_num_tbl.EXTEND();
4734 l_item_num_tbl(l_item_num_tbl.LAST) := l_item_num;
4735
4736 END LOOP; --end: FOR i IN 1..p_num_of_items
4737
4738 x_item_num_tbl := l_item_num_tbl;
4739
4740 ELSE
4741 x_item_num_tbl := NULL;
4742 END IF; --end: IF (l_can_itemnum_gen) THEN
4743
4744 EXIT WHEN l_itemgen_method_rows_cnt < 2500;
4745
4746 END LOOP; --end: Loop for every 2500 rows.
4747
4748 END Get_Seq_Gen_Item_Nums;
4749
4750
4751 -------------------------------------------------------------------------------------
4752 -- API Name: Get_Default_Template_Id --
4753 -- --
4754 -- Description: This function takes a catalog group ID as a parameter and returns --
4755 -- the template ID corresponding to the default template for the specified --
4756 -- catalog group. --
4757 -- --
4758 -- Parameters: p_category_id NUMBER Catalog group ID whose default template --
4759 -- is to be returned; if null, return --
4760 -- value is null. --
4761 -------------------------------------------------------------------------------------
4762 FUNCTION Get_Default_Template_Id (
4763 p_category_id IN NUMBER
4764 ) RETURN NUMBER IS
4765
4766 l_parent_id NUMBER;
4767 l_default_template NUMBER;
4768
4769 BEGIN
4770
4771 l_parent_id := NULL;
4772 l_default_template := NULL;
4773
4774 IF (p_category_id IS NULL) THEN
4775
4776 -- the case when the catalog group ID is null
4777 RETURN FND_PROFILE.VALUE('INV_ITEM_DEFAULT_TEMPLATE');
4778
4779 ELSE
4780
4781 BEGIN
4782 -- search for a default template for the given category
4783 SELECT TEMPLATE_ID
4784 INTO l_default_template
4785 FROM EGO_CAT_GRP_TEMPLATES
4786 WHERE CATALOG_GROUP_ID = p_category_id
4787 AND DEFAULT_FLAG = 'Y'
4788 AND ROWNUM = 1;
4789
4790 RETURN l_default_template;
4791
4792 EXCEPTION
4793 WHEN NO_DATA_FOUND THEN
4794
4795 -- if a default is not found, get the parent category ID
4796 SELECT PARENT_CATALOG_GROUP_ID
4797 INTO l_parent_id
4798 FROM MTL_ITEM_CATALOG_GROUPS_B
4799 WHERE ITEM_CATALOG_GROUP_ID = p_category_id;
4800
4801 -- recurse on the parent category
4802 RETURN Get_Default_Template_Id(
4803 p_category_id => l_parent_id
4804 );
4805
4806 END;
4807
4808 END IF; -- IF (p_category_id IS NULL)
4809
4810 END Get_Default_Template_Id;
4811
4812 -- -----------------------------------------------------------------------------
4813 -- API Name: Validate_Required_Attrs
4814 --
4815 -- Description:
4816 -- Given an Item whose Primary Keys are passed in, find those attributes
4817 -- whose values are required but is null for the Item.
4818 -- Returns EGO_USER_ATTR_TABLE containing list of required
4819 -- attributes information.
4820 -- -----------------------------------------------------------------------------
4821 --
4822 PROCEDURE Validate_Required_Attrs (
4823 p_api_version IN NUMBER
4824 ,p_inventory_item_id IN NUMBER
4825 ,p_organization_id IN NUMBER
4826 ,p_revision_id IN NUMBER
4827 ,x_attributes_req_table OUT NOCOPY EGO_USER_ATTR_TABLE
4828 ,x_return_status OUT NOCOPY VARCHAR2
4829 ,x_errorcode OUT NOCOPY NUMBER
4830 ,x_msg_count OUT NOCOPY NUMBER
4831 ,x_msg_data OUT NOCOPY VARCHAR2
4832 ) IS
4833 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Required_Attrs';
4834 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
4835 l_data_level_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
4836 l_class_code_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
4837 l_attr_group_type_table EGO_VARCHAR_TBL_TYPE;
4838 l_item_catalog_group_id NUMBER;
4839 l_related_class_codes_list VARCHAR2(150);
4840 l_token_table ERROR_HANDLER.Token_Tbl_Type;
4841
4842 CURSOR get_catalog_group_id IS
4843 SELECT ITEM_CATALOG_GROUP_ID
4844 INTO l_item_catalog_group_id
4845 FROM MTL_SYSTEM_ITEMS_B
4846 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
4847 AND ORGANIZATION_ID = p_organization_id;
4848
4849 BEGIN
4850
4851 -----------------------
4852 -- Get PKs organized --
4853 -----------------------
4854 l_pk_column_values :=
4855 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4856 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', TO_CHAR(p_inventory_item_id))
4857 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', TO_CHAR(p_organization_id))
4858 );
4859
4860 ----------------------
4861 -- Get Class Codes --
4862 ----------------------
4863 FOR catalog_group_rec IN get_catalog_group_id LOOP
4864 l_item_catalog_group_id := catalog_group_rec.ITEM_CATALOG_GROUP_ID;
4865 END LOOP;
4866
4867 Get_Related_Class_Codes(
4868 p_classification_code => l_item_catalog_group_id
4869 ,x_related_class_codes_list => l_related_class_codes_list
4870 );
4871
4872 l_class_code_values :=
4873 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4874 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', TO_CHAR(l_item_catalog_group_id))
4875 ,EGO_COL_NAME_VALUE_PAIR_OBJ('RELATED_CLASS_CODE_LIST_1', l_related_class_codes_list)
4876 );
4877
4878 ----------------------
4879 -- Get Data Levels --
4880 ----------------------
4881 l_data_level_values :=
4882 EGO_COL_NAME_VALUE_PAIR_ARRAY(
4883 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_LEVEL', null)
4884 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_REVISION_LEVEL', TO_CHAR(p_revision_id))
4885 );
4886
4887 ---------------------------------
4888 -- Setup attribute group type --
4889 ---------------------------------
4890 l_attr_group_type_table := EGO_VARCHAR_TBL_TYPE();
4891 l_attr_group_type_table.EXTEND();
4892 l_attr_group_type_table(l_attr_group_type_table.LAST):= 'EGO_ITEMMGMT_GROUP';
4893
4894
4895 EGO_USER_ATTRS_DATA_PVT.Validate_Required_Attrs (
4896 p_api_version => p_api_version
4897 ,p_object_name => 'EGO_ITEM'
4898 ,p_pk_column_name_value_pairs => l_pk_column_values
4899 ,p_class_code_name_value_pairs => l_class_code_values
4900 ,p_data_level_name_value_pairs => l_data_level_values
4901 ,p_attr_group_type_table => l_attr_group_type_table
4902 ,x_attributes_req_table => x_attributes_req_table
4903 ,x_return_status => x_return_status
4904 ,x_errorcode => x_errorcode
4905 ,x_msg_count => x_msg_count
4906 ,x_msg_data => x_msg_data
4907 );
4908
4909 EXCEPTION
4910 WHEN FND_API.G_EXC_ERROR THEN
4911
4912 x_return_status := FND_API.G_RET_STS_ERROR;
4913
4914 x_msg_count := ERROR_HANDLER.Get_Message_Count();
4915
4916 IF (x_msg_count > 0) THEN
4917 ERROR_HANDLER.Log_Error(
4918 p_write_err_to_inttable => 'Y'
4919 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
4920 );
4921
4922 IF (x_msg_count = 1) THEN
4923 DECLARE
4924 message_list ERROR_HANDLER.Error_Tbl_Type;
4925 BEGIN
4926 ERROR_HANDLER.Get_Message_List(message_list);
4927 x_msg_data := message_list(message_list.FIRST).message_text;
4928 END;
4929 ELSE
4930 x_msg_data := NULL;
4931 END IF;
4932 END IF;
4933
4934 WHEN OTHERS THEN
4935
4936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4937
4938 DECLARE
4939 l_token_table ERROR_HANDLER.Token_Tbl_Type;
4940 l_dummy_entity_index NUMBER;
4941 l_dummy_entity_id VARCHAR2(60);
4942 l_dummy_message_type VARCHAR2(1);
4943 BEGIN
4944 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
4945 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
4946 l_token_table(2).TOKEN_NAME := 'API_NAME';
4947 l_token_table(2).TOKEN_VALUE := l_api_name;
4948 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
4949 l_token_table(3).TOKEN_VALUE := SQLERRM;
4950
4951 ERROR_HANDLER.Add_Error_Message(
4952 p_message_name => 'EGO_PLSQL_ERR'
4953 ,p_application_id => 'EGO'
4954 ,p_token_tbl => l_token_table
4955 ,p_message_type => FND_API.G_RET_STS_ERROR
4956 );
4957
4958 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
4959 ,x_entity_index => l_dummy_entity_index
4960 ,x_entity_id => l_dummy_entity_id
4961 ,x_message_type => l_dummy_message_type);
4962
4963 END;
4964
4965 END Validate_Required_Attrs;
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976 PROCEDURE Generate_GDSN_Ext_AG_TP_Views (
4977 p_attr_group_name IN VARCHAR2 DEFAULT NULL
4978 ,ERRBUF OUT NOCOPY VARCHAR2
4979 ,RETCODE OUT NOCOPY VARCHAR2
4980 )
4981 IS
4982
4983 TYPE VIEW_INFO IS RECORD
4984 (
4985 ITEM_ATTR_GROUP_NAME VARCHAR2(30)
4986 ,TP_ATTR_GROUP_NAME VARCHAR2(30)
4987 ,ITEM_ATTR_AGV_NAME VARCHAR2(30)
4988 ,TP_ATTR_AGV_NAME VARCHAR2(30)
4989 ,ITEM_AGV_ALIAS VARCHAR2(30)
4990 ,TP_AGV_ALIAS VARCHAR2(30)
4991 ,FINAL_WRAPPER_AGV_NAME VARCHAR2(30)
4992 );
4993
4994 TYPE VIEW_INFO_TABLE IS TABLE OF VIEW_INFO
4995 INDEX BY BINARY_INTEGER;
4996
4997 CURSOR c_check_agv_name (cp_agv_name IN VARCHAR2
4998 ,cp_application_id IN NUMBER
4999 ,cp_attr_group_type IN VARCHAR2
5000 ,cp_attr_group_name IN VARCHAR2
5001 ) IS
5002 SELECT AGV_NAME
5003 FROM EGO_FND_DSC_FLX_CTX_EXT EXT1
5004 WHERE EXT1.AGV_NAME = cp_agv_name
5005 AND EXT1.ATTR_GROUP_ID NOT IN (SELECT ATTR_GROUP_ID
5006 FROM EGO_FND_DSC_FLX_CTX_EXT EXT2
5007 WHERE EXT2.AGV_NAME = cp_agv_name
5008 AND EXT2.APPLICATION_ID = cp_application_id
5009 AND EXT2.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
5010 AND EXT2.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name);
5011
5012 CURSOR c_check_obj_name (cp_agv_name IN VARCHAR2
5013 ,cp_application_id IN NUMBER
5014 ,cp_attr_group_type IN VARCHAR2
5015 ,cp_attr_group_name IN VARCHAR2
5016 )IS
5017 SELECT OBJECT_NAME
5018 FROM SYS.ALL_OBJECTS
5019 WHERE OBJECT_NAME = cp_agv_name
5020 AND OBJECT_NAME NOT IN (SELECT AGV_NAME
5021 FROM EGO_FND_DSC_FLX_CTX_EXT
5022 WHERE AGV_NAME = cp_agv_name
5023 AND APPLICATION_ID = cp_application_id
5024 AND DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
5025 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
5026 );
5027
5028
5029 l_views_to_process VIEW_INFO_TABLE;
5030
5031 l_return_status VARCHAR2(3);
5032 l_errorcode NUMBER;
5033 l_msg_count NUMBER;
5034 l_msg_data VARCHAR2(1000);
5035 l_temp_num NUMBER;
5036 l_multi_row_ag VARCHAR2(2);
5037 l_ag_id VARCHAR2(20);
5038 l_temp_agv_name VARCHAR2(30);
5039 l_views_exist VARCHAR2(32767);
5040 l_view_compile_errs VARCHAR2(32767);
5041 l_item_view_failed BOOLEAN;
5042 l_tp_view_failed BOOLEAN;
5043
5044 ERR_OCCURED EXCEPTION;
5045
5046 BEGIN
5047
5048
5049 ----------------------------------------------
5050 -- Building up the table of Attr groups to --
5051 -- be processed --
5052 ----------------------------------------------
5053
5054
5055 l_views_to_process(1).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_TRADE_ITEM_INFO';
5056 l_views_to_process(1).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_TRADE_ITEM_INFO_AGV';
5057
5058
5059 l_views_to_process(2).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_PRICE';
5060 l_views_to_process(2).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_PRICE';
5061 l_views_to_process(2).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_PRICE_INFO_AGV';
5062 l_views_to_process(2).TP_ATTR_AGV_NAME := 'EGO_SBDH_PRICE_INFO_TPAGV';
5063 l_views_to_process(2).ITEM_AGV_ALIAS := 'ITEMAGV';
5064 l_views_to_process(2).TP_AGV_ALIAS := 'TPAGV';
5065 l_views_to_process(2).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_PRICE_INFO_TPV';
5066
5067 l_views_to_process(3).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_VAR_TRD_ITEM_TYPE';
5068 l_views_to_process(3).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_VAR_TRD_ITEM_TYPE';
5069 l_views_to_process(3).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_VAR_TRD_ITEM_TYPE_AGV';
5070 l_views_to_process(3).TP_ATTR_AGV_NAME := 'EGO_SBDH_VAR_TRD_ITM_TYP_TPAGV';
5071 l_views_to_process(3).ITEM_AGV_ALIAS := 'ITEMAGV';
5072 l_views_to_process(3).TP_AGV_ALIAS := 'TPAGV';
5073 l_views_to_process(3).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_VAR_TRD_ITEM_TYPE_TPV';
5074
5075 l_views_to_process(4).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_DEP_VAL_DATE_INFO';
5076 l_views_to_process(4).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_DEP_VAL_DATE_INFO_AGV';
5077
5078 l_views_to_process(5).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_RETURNS';
5079 l_views_to_process(5).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_RETURNS_AGV';
5080
5081 l_views_to_process(6).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_BUYING_QTY_INFO';
5082 l_views_to_process(6).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_BUYING_QTY_INFO';
5083 l_views_to_process(6).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_BUYING_QTY_INFO_AGV';
5084 l_views_to_process(6).TP_ATTR_AGV_NAME := 'EGO_SBDH_BUYING_QTY_INFO_TPAGV';
5085 l_views_to_process(6).ITEM_AGV_ALIAS := 'ITEMAGV';
5086 l_views_to_process(6).TP_AGV_ALIAS := 'TPAGV';
5087 l_views_to_process(6).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_BUYING_QTY_INFO_TPV';
5088
5089 l_views_to_process(7).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_SHIP_EXCL_DATES';
5090 l_views_to_process(7).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_SHIP_EXCL_DATES';
5091 l_views_to_process(7).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_SHIP_EXCL_DATES_AGV';
5092 l_views_to_process(7).TP_ATTR_AGV_NAME := 'EGO_SBDH_SHIP_EXCL_DATES_TPAGV';
5093 l_views_to_process(7).ITEM_AGV_ALIAS := 'ITEMAGV';
5094 l_views_to_process(7).TP_AGV_ALIAS := 'TPAGV';
5095 l_views_to_process(7).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_SHIP_EXCL_DATES_TPV';
5096
5097 l_views_to_process(8).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_ORDERING_INFO';
5098 l_views_to_process(8).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_ORDERING_INFO';
5099 l_views_to_process(8).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_ORDERING_INFO_AGV';
5100 l_views_to_process(8).TP_ATTR_AGV_NAME := 'EGO_SBDH_ORDERING_INFO_TPAGV';
5101 l_views_to_process(8).ITEM_AGV_ALIAS := 'ITEMAGV';
5102 l_views_to_process(8).TP_AGV_ALIAS := 'TPAGV';
5103 l_views_to_process(8).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_ORDERING_INFO_TPV';
5104
5105 l_views_to_process(9).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_TRD_ITM_LIFESPAN';
5106 l_views_to_process(9).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_TRD_ITM_LIFESPAN';
5107 l_views_to_process(9).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_TRD_ITM_LIFESPAN_AGV';
5108 l_views_to_process(9).TP_ATTR_AGV_NAME := 'EGO_SBDH_TRD_ITM_LIFSPAN_TPAGV';
5109 l_views_to_process(9).ITEM_AGV_ALIAS := 'ITEMAGV';
5110 l_views_to_process(9).TP_AGV_ALIAS := 'TPAGV';
5111 l_views_to_process(9).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_TRD_ITM_LIFESPAN_TPV';
5112
5113
5114 l_views_to_process(10).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_PAYMENT_METHOD';
5115 l_views_to_process(10).TP_ATTR_GROUP_NAME := 'EGOINT_GDSN_PAYMENT_METHOD';
5116 l_views_to_process(10).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_PAYMENT_METHOD_AGV';
5117 l_views_to_process(10).TP_ATTR_AGV_NAME := 'EGO_SBDH_PAYMENT_METHOD_TPAGV';
5118 l_views_to_process(10).ITEM_AGV_ALIAS := 'ITEMAGV';
5119 l_views_to_process(10).TP_AGV_ALIAS := 'TPAGV';
5120 l_views_to_process(10).FINAL_WRAPPER_AGV_NAME := 'EGO_SBDH_PAYMENT_METHOD_TPV';
5121
5122
5123 l_views_to_process(11).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_PRC_CMPRSN_CONTNT';
5124 l_views_to_process(11).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_PRC_CMPRSN_CONTNT_AGV';
5125
5126 l_views_to_process(12).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_PRC_CMPRSN_MSRMT';
5127 l_views_to_process(12).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_PRC_CMPRSN_MSRMT_AGV';
5128
5129 l_views_to_process(13).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_STRG_HNDLG_HUMIDTY';
5130 l_views_to_process(13).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_STRG_HNDLG_HMDTY_AGV';
5131
5132 l_views_to_process(14).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_CLASS_COMPLIANCE';
5133 l_views_to_process(14).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_CLASS_COMPLIANCE_AGV';
5134
5135 l_views_to_process(15).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_COUNTRY_OF_ASSY';
5136 l_views_to_process(15).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_COUNTRY_OF_ASSY_AGV';
5137
5138 l_views_to_process(16).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_CAMPAIGN_INFO';
5139 l_views_to_process(16).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_CAMPAIGN_INFO_AGV';
5140
5141 l_views_to_process(17).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_SPECIAL_ITEM';
5142 l_views_to_process(17).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_SPECIAL_ITEM_AGV';
5143
5144
5145 l_views_to_process(18).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_ITEM_FEATURE_BNFT';
5146 l_views_to_process(18).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_ITEM_FEATURE_BNFT_AGV';
5147
5148 l_views_to_process(19).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_MATERIAL_INFO';
5149 l_views_to_process(19).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_MATERIAL_INFO_AGV';
5150
5151 l_views_to_process(20).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_SIZE_INFO';
5152 l_views_to_process(20).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_SIZE_INFO_AGV';
5153
5154 l_views_to_process(21).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_SEASON_AVL_DATE';
5155 l_views_to_process(21).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_SEASON_AVL_DATE_AGV';
5156
5157 l_views_to_process(22).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_SEASON';
5158 l_views_to_process(22).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_SEASON_AGV';
5159
5160 l_views_to_process(23).ITEM_ATTR_GROUP_NAME := 'EGOINT_GDSN_TARGET_CONSUMER';
5161 l_views_to_process(23).ITEM_ATTR_AGV_NAME := 'EGO_SBDH_TARGET_CONSUMER_AGV';
5162
5163
5164 ----------------------------------------------
5165 -- Here we loop through the complete table --
5166 -- built above for processing each of the --
5167 -- attribute groups view --
5168 ----------------------------------------------
5169 l_views_exist := '';
5170 l_view_compile_errs := '';
5171 FOR i in 1 .. l_views_to_process.LAST
5172 LOOP
5173
5174 IF( p_attr_group_name IS NULL OR
5175 (p_attr_group_name IS NOT NULL AND p_attr_group_name = l_views_to_process(i).ITEM_ATTR_GROUP_NAME)
5176 )
5177 THEN
5178 ----------------------------------------
5179 -- Firstly we need to sync up the --
5180 -- metadata for all the tp attr group --
5181 ----------------------------------------
5182
5183 l_return_status := NULL;
5184 l_errorcode := NULL;
5185 l_msg_count := NULL;
5186 l_msg_data := NULL;
5187
5188 IF ( l_views_to_process(i).ITEM_ATTR_GROUP_NAME IS NOT NULL
5189 AND l_views_to_process(i).TP_ATTR_GROUP_NAME IS NOT NULL) THEN
5190
5191 EGO_EXT_FWK_PUB.Sync_Up_Attr_Metadata (
5192 p_source_ag_name => l_views_to_process(i).ITEM_ATTR_GROUP_NAME
5193 ,p_source_ag_type => 'EGO_ITEMMGMT_GROUP'
5194 ,p_source_appl_id => 431
5195 ,p_target_ag_name => l_views_to_process(i).TP_ATTR_GROUP_NAME
5196 ,p_target_ag_type => 'EGO_ITEM_TP_EXT_ATTRS'
5197 ,p_target_appl_id => 431
5198 ,x_return_status => l_return_status
5199 ,x_errorcode => l_errorcode
5200 ,x_msg_count => l_msg_count
5201 ,x_msg_data => l_msg_data
5202 );
5203 END IF;
5204
5205
5206 l_item_view_failed := FALSE;
5207 l_multi_row_ag := '';
5208 --------------------------------
5209 -- Validating the ITEM AGV NAME
5210 --------------------------------
5211
5212 OPEN c_check_agv_name (cp_agv_name => UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME)
5213 ,cp_application_id => 431
5214 ,cp_attr_group_type => 'EGO_ITEMMGMT_GROUP'
5215 ,cp_attr_group_name => l_views_to_process(i).ITEM_ATTR_GROUP_NAME
5216 );
5217 FETCH c_check_agv_name INTO l_temp_agv_name;
5218
5219 OPEN c_check_obj_name (cp_agv_name => UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME)
5220 ,cp_application_id => 431
5221 ,cp_attr_group_type => 'EGO_ITEMMGMT_GROUP'
5222 ,cp_attr_group_name => l_views_to_process(i).ITEM_ATTR_GROUP_NAME
5223 );
5224 FETCH c_check_obj_name INTO l_temp_agv_name;
5225
5226
5227 IF c_check_agv_name%FOUND OR c_check_obj_name%FOUND THEN
5228 l_views_exist := l_views_exist||' '||UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME);--RAISE L_ITEM_VNAME_EXISTS;
5229 l_item_view_failed :=TRUE;
5230 ELSE
5231 UPDATE ego_fnd_dsc_flx_ctx_ext
5232 SET agv_name = UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME)
5233 WHERE application_id = 431
5234 AND descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
5235 AND descriptive_flex_context_code = l_views_to_process(i).ITEM_ATTR_GROUP_NAME;
5236 END IF;
5237
5238 CLOSE c_check_agv_name;
5239 CLOSE c_check_obj_name;
5240
5241
5242 --------------------------------
5243 -- Validating the TP AGV NAME
5244 --------------------------------
5245 IF (l_views_to_process(i).TP_ATTR_AGV_NAME IS NOT NULL AND
5246 l_views_to_process(i).TP_ATTR_GROUP_NAME IS NOT NULL) THEN
5247
5248 OPEN c_check_agv_name (cp_agv_name => UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME)
5249 ,cp_application_id => 431
5250 ,cp_attr_group_type => 'EGO_ITEM_TP_EXT_ATTRS'
5251 ,cp_attr_group_name => l_views_to_process(i).TP_ATTR_GROUP_NAME
5252 );
5253 FETCH c_check_agv_name INTO l_temp_agv_name;
5254
5255 OPEN c_check_obj_name (cp_agv_name => UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME)
5256 ,cp_application_id => 431
5257 ,cp_attr_group_type => 'EGO_ITEM_TP_EXT_ATTRS'
5258 ,cp_attr_group_name => l_views_to_process(i).TP_ATTR_GROUP_NAME
5259 );
5260 FETCH c_check_obj_name INTO l_temp_agv_name;
5261
5262
5263 IF c_check_agv_name%FOUND OR c_check_obj_name%FOUND THEN
5264 l_views_exist := l_views_exist||' '||UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME);--RAISE L_TP_VNAME_EXISTS;
5265 l_tp_view_failed := TRUE;
5266 ELSE
5267 UPDATE ego_fnd_dsc_flx_ctx_ext
5268 SET agv_name = UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME)
5269 WHERE application_id = 431
5270 AND descriptive_flexfield_name = 'EGO_ITEM_TP_EXT_ATTRS'
5271 AND descriptive_flex_context_code = l_views_to_process(i).TP_ATTR_GROUP_NAME;
5272 END IF;
5273
5274 CLOSE c_check_agv_name;
5275 CLOSE c_check_obj_name;
5276
5277 END IF;
5278
5279 --------------------------------------------------------------
5280 -- Fetching the attr group id for the item attr group --
5281 --------------------------------------------------------------
5282 SELECT ATTR_GROUP_ID, MULTI_ROW
5283 INTO l_temp_num, l_multi_row_ag
5284 FROM EGO_FND_DSC_FLX_CTX_EXT
5285 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
5286 AND APPLICATION_ID = 431
5287 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_views_to_process(i).ITEM_ATTR_GROUP_NAME;
5288
5289 l_return_status := NULL;
5290 l_errorcode := NULL;
5291 l_msg_count := NULL;
5292 l_msg_data := NULL;
5293
5294 -------------------------------------------------------------
5295 -- Here we generate the view for item attribute group --
5296 -------------------------------------------------------------
5297 IF (l_temp_num >0) THEN
5298
5299 l_ag_id := TO_CHAR(l_temp_num);
5300 EGO_EXT_FWK_PUB.Compile_Attr_Group_Views(
5301 ERRBUF => l_msg_data
5302 ,RETCODE => l_return_status
5303 ,p_attr_group_id_list => l_ag_id
5304 ,p_init_msg_list => fnd_api.g_FALSE
5305 ,p_commit => fnd_api.g_TRUE
5306 );
5307
5308 END IF;
5309
5310 IF (l_return_status <> 'S') THEN
5311 l_view_compile_errs := l_view_compile_errs||' ,Error while creating view for :'||l_views_to_process(i).ITEM_ATTR_GROUP_NAME||
5312 '('||l_msg_data||')';
5313 l_item_view_failed := TRUE;
5314 END IF;
5315
5316
5317
5318
5319 -----------------------------------------------
5320 -- Processing the TPAGV and the wrapper VIEW --
5321 -----------------------------------------------
5322
5323 IF (l_views_to_process(i).TP_ATTR_AGV_NAME IS NOT NULL AND
5324 l_views_to_process(i).TP_ATTR_GROUP_NAME IS NOT NULL) THEN
5325
5326 ----------------------------------------------------------------------
5327 -- Fetching the attr group id for the corresponding tp attr group --
5328 ----------------------------------------------------------------------
5329 BEGIN
5330 SELECT ATTR_GROUP_ID
5331 INTO l_temp_num
5332 FROM EGO_FND_DSC_FLX_CTX_EXT
5333 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEM_TP_EXT_ATTRS'
5334 AND APPLICATION_ID = 431
5335 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_views_to_process(i).TP_ATTR_GROUP_NAME;
5336
5337 EXCEPTION
5338 WHEN NO_DATA_FOUND THEN
5339 l_temp_num := -1;
5340 END;
5341
5342 -------------------------------------------------------------
5343 -- Here we generate the view for the corresponding trading --
5344 -- partner attr grp --
5345 -------------------------------------------------------------
5346 IF (l_temp_num >0) THEN
5347 EGO_EXT_FWK_PUB.Compile_Attr_Group_Views(
5348 ERRBUF => l_msg_data
5349 ,RETCODE => l_return_status
5350 ,p_attr_group_id_list => TO_CHAR(l_temp_num)
5351 ,p_init_msg_list => fnd_api.g_FALSE
5352 ,p_commit => fnd_api.g_TRUE
5353 );
5354 END IF;
5355
5356 IF (l_return_status <> 'S') THEN
5357 l_view_compile_errs := l_view_compile_errs||' ,Error while creating view for :'||l_views_to_process(i).TP_ATTR_GROUP_NAME||
5358 '('||l_msg_data||') ';
5359 l_tp_view_failed := TRUE;
5360 END IF;
5361
5362 -------------------------------------------------------------
5363 -- Generating the wrapper view over the tp and item attr --
5364 -- attr group views. --
5365 -------------------------------------------------------------
5366
5367 IF (l_views_to_process(i).FINAL_WRAPPER_AGV_NAME IS NOT NULL )THEN
5368
5369 IF (l_item_view_failed OR l_tp_view_failed) THEN
5370 l_view_compile_errs := l_view_compile_errs||' ,Could not create the view '||l_views_to_process(i).FINAL_WRAPPER_AGV_NAME||
5371 ' since the base view creation failed :';
5372 ELSE
5373 EGO_Item_PVT.GENERATE_GTIN_TP_ATTRS_VIEW (
5374 p_item_attr_agv_name => UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME)
5375 ,p_tp_agv_name => UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME)
5376 ,p_item_attr_agv_alias => l_views_to_process(i).ITEM_AGV_ALIAS
5377 ,p_tp_agv_alias => l_views_to_process(i).TP_AGV_ALIAS
5378 ,p_final_agv_name => l_views_to_process(i).FINAL_WRAPPER_AGV_NAME
5379 ,p_multi_row_ag => l_multi_row_ag
5380 ,x_return_status => l_return_status
5381 ,x_msg_data => l_msg_data
5382 );
5383 END IF;
5384
5385
5386 IF (l_return_status <> 'S') THEN
5387 l_view_compile_errs := l_view_compile_errs||' ,Error while creating view :'||l_views_to_process(i).FINAL_WRAPPER_AGV_NAME||
5388 '('||l_msg_data||') ';
5389 END IF;
5390
5391 END IF;
5392
5393 END IF;
5394 END IF;--
5395 END LOOP;
5396
5397 IF (LENGTH(l_view_compile_errs)>1 OR LENGTH(l_views_exist)>1) THEN
5398 RAISE ERR_OCCURED;
5399 END IF;
5400
5401 ERRBUF := '';
5402 RETCODE := FND_API.G_RET_STS_SUCCESS;
5403
5404
5405 EXCEPTION
5406 WHEN ERR_OCCURED THEN
5407
5408 IF(LENGTH(l_views_exist)>1) THEN
5409 l_views_exist := 'Could not create the following views as already an object exists by this name - '||l_views_exist;
5410 END IF;
5411
5412 ERRBUF := l_views_exist||' '||l_view_compile_errs;
5413 RETCODE := FND_API.G_RET_STS_ERROR;
5414
5415 RAISE_APPLICATION_ERROR(-20000, l_views_exist||' '||l_view_compile_errs);
5416
5417 END Generate_GDSN_Ext_AG_TP_Views;
5418
5419
5420
5421
5422
5423
5424
5425
5426
5427
5428
5429
5430 PROCEDURE GENERATE_GTIN_TP_ATTRS_VIEW (
5431 p_item_attr_agv_name IN VARCHAR2
5432 ,p_tp_agv_name IN VARCHAR2
5433 ,p_item_attr_agv_alias IN VARCHAR2
5434 ,p_tp_agv_alias IN VARCHAR2
5435 ,p_final_agv_name IN VARCHAR2
5436 ,p_multi_row_ag IN VARCHAR2 -- 'Y' or 'N'
5437 ,x_return_status OUT NOCOPY VARCHAR2
5438 ,x_msg_data OUT NOCOPY VARCHAR2
5439 )
5440 IS
5441
5442 TYPE LOCAL_VIEW_COLUMN_RECORD IS RECORD
5443 (
5444 VIEW_NAME VARCHAR2(30)
5445 ,COLUMN_NAME VARCHAR2(30)
5446 );
5447
5448 TYPE VIEW_COLUMNS_TABLE IS TABLE OF LOCAL_VIEW_COLUMN_RECORD
5449 INDEX BY BINARY_INTEGER;
5450
5451 l_item_agv_cols_tab VIEW_COLUMNS_TABLE;
5452 l_tp_agv_cols_tab VIEW_COLUMNS_TABLE;
5453
5454 l_dynamic_sql VARCHAR2(2000);
5455 l_no_of_item_agv_cols NUMBER;
5456 l_no_of_tp_agv_cols NUMBER;
5457 l_item_agv_col_list VARCHAR2(10000);
5458 l_final_view_query VARCHAR2(10000);
5459 l_final_view_col_list VARCHAR2(10000);
5460 l_final_view_sql VARCHAR2(20000);
5461 l_item_agv_alias VARCHAR2(30);
5462 l_tp_agv_alias VARCHAR2(30);
5463 l_current_col_name VARCHAR2(30);
5464 l_item_cur_col VARCHAR2(30);
5465
5466 BEGIN
5467
5468 IF(p_item_attr_agv_alias IS NULL ) THEN
5469 l_item_agv_alias := p_item_attr_agv_name;
5470 ELSE
5471 l_item_agv_alias := p_item_attr_agv_alias;
5472 END IF;
5473
5474 IF(p_tp_agv_alias IS NULL ) THEN
5475 l_tp_agv_alias := p_tp_agv_name;
5476 ELSE
5477 l_tp_agv_alias := p_tp_agv_alias;
5478 END IF;
5479
5480 ---------------------------------------------------
5481 -- Fetching the column names of primary and the --
5482 -- secondry view --
5483 ---------------------------------------------------
5484
5485 l_dynamic_sql := ' SELECT TABLE_NAME, COLUMN_NAME '||
5486 ' FROM SYS.ALL_TAB_COLUMNS '||
5487 ' WHERE TABLE_NAME = :1 ';
5488
5489 EXECUTE IMMEDIATE l_dynamic_sql
5490 BULK COLLECT INTO l_item_agv_cols_tab
5491 USING p_item_attr_agv_name;
5492
5493 EXECUTE IMMEDIATE l_dynamic_sql
5494 BULK COLLECT INTO l_tp_agv_cols_tab
5495 USING p_tp_agv_name;
5496
5497 l_no_of_item_agv_cols := l_item_agv_cols_tab.COUNT;
5498 l_no_of_tp_agv_cols := l_tp_agv_cols_tab.COUNT;
5499
5500 --------------------------------------------------------
5501 -- Building the concatenated list of sec view columns --
5502 --------------------------------------------------------
5503
5504 l_item_agv_col_list := ' ';
5505 FOR i IN 1 .. l_no_of_item_agv_cols
5506 LOOP
5507 l_item_agv_col_list := l_item_agv_col_list || l_item_agv_cols_tab(i).COLUMN_NAME;
5508 END LOOP;
5509
5510
5511 ---------------------------------------------------
5512 -- Building the VIEW SQL --
5513 ---------------------------------------------------
5514
5515 l_final_view_query := ' SELECT ';
5516 l_final_view_col_list := ' ';
5517
5518 FOR i IN 1 .. l_no_of_tp_agv_cols
5519 LOOP
5520
5521 l_current_col_name := l_tp_agv_cols_tab(i).COLUMN_NAME;
5522 l_final_view_col_list := l_final_view_col_list||' '||l_current_col_name;
5523
5524 IF (l_current_col_name = 'MASTER_ORGANIZATION_ID') THEN
5525 l_item_cur_col := 'ORGANIZATION_ID';
5526 ELSE
5527 l_item_cur_col := l_current_col_name;
5528 END IF;
5529
5530 IF (INSTR(l_item_agv_col_list,l_item_cur_col) > 0) THEN
5531 l_final_view_query := l_final_view_query||' NVL('||l_tp_agv_alias||'.'||l_current_col_name||
5532 ' , '||l_item_agv_alias||'.'||l_item_cur_col||
5533 ' ) '|| l_current_col_name;
5534 ELSE
5535 l_final_view_query := l_final_view_query||' '||l_tp_agv_alias||'.'||l_current_col_name||
5536 ' '||l_current_col_name;
5537 END IF;
5538
5539 IF (i <> l_no_of_item_agv_cols) THEN
5540 -- commented following two lines to fix bug 6910417
5541 --l_final_view_query := l_final_view_query||' , ';
5542 --l_final_view_col_list := l_final_view_col_list||' , ';
5543
5544 -- added following IF..ELSE..END IF to fix bug 6910417
5545 IF (i = l_no_of_tp_agv_cols) THEN
5546 l_final_view_query := l_final_view_query;
5547 l_final_view_col_list := l_final_view_col_list;
5548 ELSE
5549 l_final_view_query := l_final_view_query||' , ';
5550 l_final_view_col_list := l_final_view_col_list||' , ';
5551 END IF;
5552
5553 END IF;
5554
5555 END LOOP;
5556
5557 IF (p_multi_row_ag = 'Y') THEN
5558
5559 l_final_view_sql:= ' CREATE OR REPLACE VIEW '||p_final_agv_name||' ( '||l_final_view_col_list||') '||
5560 ' AS SELECT '||l_final_view_col_list||
5561 ' FROM '||p_tp_agv_name||
5562 ' UNION ALL '||
5563 ' SELECT '||l_final_view_col_list||
5564 ' FROM (SELECT NULL PARTY_SITE_ID, '||l_item_agv_alias||'.* , '||
5565 ' '||l_item_agv_alias||'.ORGANIZATION_ID MASTER_ORGANIZATION_ID '||
5566 ' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
5567 ' ) ';
5568
5569
5570 ELSE
5571
5572 l_final_view_sql := ' CREATE OR REPLACE VIEW '||p_final_agv_name||' ( '||l_final_view_col_list||') '||
5573 ' AS '||l_final_view_query||
5574 ' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
5575 ' , '||p_tp_agv_name||' '||l_tp_agv_alias||
5576 ' WHERE '||l_item_agv_alias||'.INVENTORY_ITEM_ID = '||l_tp_agv_alias||'.INVENTORY_ITEM_ID(+) '||
5577 ' AND '||l_item_agv_alias||'.ORGANIZATION_ID = '||l_tp_agv_alias||'.MASTER_ORGANIZATION_ID(+) '||
5578 ' UNION ALL '||
5579 ' SELECT '||l_final_view_col_list||
5580 ' FROM '||p_tp_agv_name||' '||l_tp_agv_alias||
5581 ' WHERE NOT EXISTS ( SELECT ''X'' '||
5582 ' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
5583 ' WHERE INVENTORY_ITEM_ID = '||l_tp_agv_alias||'.INVENTORY_ITEM_ID '||
5584 ' AND ORGANIZATION_ID = '||l_tp_agv_alias||'.MASTER_ORGANIZATION_ID ) '||
5585 ' UNION ALL '||
5586 ' SELECT '||l_final_view_col_list||
5587 ' FROM (SELECT NULL PARTY_SITE_ID, '||l_item_agv_alias||'.* , '||
5588 ' '||l_item_agv_alias||'.ORGANIZATION_ID MASTER_ORGANIZATION_ID '||
5589 ' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
5590 ' WHERE EXISTS ( SELECT ''X'' '||
5591 ' FROM '||p_tp_agv_name||' '||l_tp_agv_alias||
5592 ' WHERE INVENTORY_ITEM_ID = '||l_item_agv_alias||'.INVENTORY_ITEM_ID '||
5593 ' AND MASTER_ORGANIZATION_ID = '||l_item_agv_alias||'.ORGANIZATION_ID '||
5594 ' ) '||
5595 ' ) ';
5596
5597
5598
5599 END IF;
5600
5601
5602 EXECUTE IMMEDIATE l_final_view_sql;
5603
5604 x_return_status := FND_API.G_RET_STS_SUCCESS;
5605
5606 EXCEPTION
5607 WHEN OTHERS THEN
5608
5609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5610 x_msg_data := SQLERRM;
5611
5612 END GENERATE_GTIN_TP_ATTRS_VIEW;
5613
5614 PROCEDURE process_attribute_defaulting(p_item_attr_def_tab IN OUT NOCOPY SYSTEM.EGO_ITEM_ATTR_DEFAULT_TABLE
5615 ,p_gdsn_enabled IN VARCHAR2
5616 ,p_commit IN VARCHAR2
5617 ,x_return_status OUT NOCOPY VARCHAR2
5618 ,x_msg_data OUT NOCOPY VARCHAR2
5619 ,x_msg_count OUT NOCOPY NUMBER)
5620
5621 IS
5622
5623 l_error_code VARCHAR2(2000);
5624 l_msg_count NUMBER;
5625 l_msg_data VARCHAR2(2000);
5626 l_failed_row_id_list VARCHAR2(2000);
5627
5628 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5629 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
5630 l_data_level_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
5631 l_inventory_item_id NUMBER;
5632 l_organization_id NUMBER;
5633 l_revision_id NUMBER;
5634 l_object_name VARCHAR2(10);
5635 l_application_id NUMBER;
5636 l_catalog_group_id NUMBER;
5637 l_additional_class_Code_list VARCHAR2(32000);
5638 l_attribute_group_type VARCHAR2(20);
5639 l_record_first NUMBER;
5640 l_record_last NUMBER;
5641 l_return_status VARCHAR2(10);
5642 l_commit VARCHAR2(2);
5643 l_attr_groups_to_exclude VARCHAR2(2000);
5644
5645 CURSOR attr_default_recs IS
5646 SELECT A.ORGANIZATION_ID ORGANIZATION_ID
5647 ,A.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
5648 ,A.REVISION_ID REVISION_ID
5649 ,A.APPLICATION_ID APPLICATION_ID
5650 ,A.ITEM_CATALOG_GROUP_ID ITEM_CATALOG_GROUP_ID
5651 ,A.OBJECT_NAME OBJECT_NAME
5652 ,A.ATTRIBUTE_GROUP_TYPE ATTRIBUTE_GROUP_TYPE
5653 FROM THE (SELECT CAST(p_item_attr_def_tab AS "SYSTEM".EGO_ITEM_ATTR_DEFAULT_TABLE)
5654 FROM dual) A
5655 ORDER BY INVENTORY_ITEM_ID;
5656
5657 BEGIN
5658 x_return_status := l_return_status;
5659 x_msg_count := 0;
5660
5661 l_record_first := p_item_attr_def_tab.FIRST;
5662 l_record_last := p_item_attr_def_tab.LAST;
5663 FOR attr_default_rec IN attr_default_recs LOOP
5664 l_inventory_item_id := attr_default_rec.INVENTORY_ITEM_ID;
5665 l_organization_id := attr_default_rec.ORGANIZATION_ID;
5666 l_revision_id := attr_default_rec.REVISION_ID;
5667 l_object_name := attr_default_rec.OBJECT_NAME;
5668 l_application_id := attr_default_rec.APPLICATION_ID;
5669 l_catalog_group_id := attr_default_rec.ITEM_CATALOG_GROUP_ID;
5670 l_attribute_group_type := attr_default_rec.ATTRIBUTE_GROUP_TYPE;
5671 l_commit := p_commit;
5672 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
5673 ( EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', TO_CHAR(attr_default_rec.INVENTORY_ITEM_ID))
5674 , EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', TO_CHAR(attr_default_rec.ORGANIZATION_ID)));
5675
5676 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
5677 (EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', to_char(attr_default_rec.ITEM_CATALOG_GROUP_ID)));
5678
5679 l_data_level_values := EGO_COL_NAME_VALUE_PAIR_ARRAY
5680 (EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', to_char(attr_default_rec.REVISION_ID)));
5681
5682 Get_Related_Class_Codes ( p_classification_code => l_catalog_group_id,
5683 x_related_class_codes_list => l_additional_class_Code_list);
5684 IF l_additional_class_Code_list IS NULL THEN
5685 l_additional_class_Code_list := '-1';
5686 ELSE
5687 l_additional_class_Code_list := l_additional_class_Code_list|| ','||l_catalog_group_id||',-1';
5688 END IF;
5689 IF p_gdsn_enabled = 'N' THEN
5690
5691 l_attr_groups_to_exclude := 'SELECT ATTR_GROUP_ID FROM '||
5692 ' EGO_FND_DSC_FLX_CTX_EXT WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE ' ||
5693 ' LIKE ''EGOINT_GDSN%'' AND APPLICATION_ID = 431 ' ||
5694 ' AND DESCRIPTIVE_FLEXFIELD_NAME = ''EGO_ITEMMGMT_GROUP'' ' ;
5695 END IF;
5696 EGO_USER_ATTRS_DATA_PVT.Apply_Default_Vals_For_Entity
5697 ( p_object_name => l_object_name
5698 ,p_application_id => l_application_id
5699 ,p_attr_group_type => l_attribute_group_type
5700 ,p_attr_groups_to_exclude => l_attr_groups_to_exclude
5701 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
5702 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
5703 ,p_data_level_values => l_data_level_values
5704 ,p_additional_class_Code_list => l_additional_class_Code_list
5705 ,p_init_error_handler => 'T'
5706 ,p_init_fnd_msg_list => 'T'
5707 ,p_log_errors => 'T'
5708 ,p_add_errors_to_fnd_stack => 'T'
5709 ,P_commit => l_commit
5710 ,x_failed_row_id_list => l_failed_row_id_list
5711 ,x_return_status => l_return_status
5712 ,x_errorcode => l_error_code
5713 ,x_msg_count => l_msg_count
5714 ,x_msg_data => l_msg_data
5715 );
5716 x_return_status := l_return_status ;
5717 END LOOP;
5718 EXCEPTION
5719 WHEN OTHERS THEN
5720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5721 x_msg_data := SQLERRM;
5722
5723 END process_attribute_defaulting;
5724
5725 END EGO_ITEM_PVT;