[Home] [Help]
PACKAGE BODY: APPS.INV_EGO_REVISION_VALIDATE
Source
1 PACKAGE BODY INV_EGO_REVISION_VALIDATE AS
2 /* $Header: INVEGRVB.pls 120.75.12020000.6 2013/04/24 10:14:25 zewhuang ship $ */
3
4 LOGGING_ERR EXCEPTION;
5 G_PROCESS_CONTROL VARCHAR2(2000) := NULL;--Used by EGO API only for internal flow occntrol
6 G_PROCESS_CONTROL_HTML_API VARCHAR2(100) := NULL;
7 /* Added to fix Bug#8434681 : This variable was added to check if the code path flow is comming from HTML pages ('HTML') or through open API ('API')
8 variable G_PROCESS_CONTROL could have been used for this purpose. However, this varible is is utilized in many places in the code and reusing it might
9 compromise the existing functionality. */
10
11 --Bug: 5258295
12 -- ============================================================================
13 -- API Name : mtl_catalog_group_update
14 -- Purpose : NIR creation/cancellation and validations for ICC Update
15 -- ============================================================================
16 FUNCTION mtl_catalog_group_update(
17 p_rowid IN ROWID
18 ,p_process_flag IN NUMBER --Added for R12C
19 ,p_inventory_item_id IN NUMBER
20 ,p_organization_id IN NUMBER
21 ,p_old_item_cat_grp_id IN NUMBER
22 ,p_new_item_cat_grp_id IN NUMBER
23 ,p_approval_status IN VARCHAR2
24 ,p_item_number IN VARCHAR2
25 ,p_transaction_id IN NUMBER
26 ,p_prog_appid IN NUMBER
27 ,p_prog_id IN NUMBER
28 ,p_request_id IN NUMBER
29 ,p_xset_id IN NUMBER --Adding for R12 C
30 ,p_user_id IN NUMBER
31 ,p_login_id IN NUMBER
32 ,x_err_text OUT NOCOPY VARCHAR2) RETURN INTEGER;
33 -- ============================================================================
34 -- API Name : Cancel_New_Item_Request
35 -- Purpose : Cancels an NIR
36 -- Changing signature for R12 C
37 -- ============================================================================
38 PROCEDURE Cancel_New_Item_Request(
39 p_inventory_item_id IN NUMBER
40 ,p_organization_id IN NUMBER
41 ,p_item_number IN VARCHAR2
42 ,p_auto_commit IN VARCHAR2
43 ,p_wf_user_id IN NUMBER
44 ,p_fnd_user_id IN NUMBER
45 ,x_return_status OUT NOCOPY VARCHAR2);
46
47 -- ============================================================================
48 -- API Name : Create_New_Item_Req_Upd
49 -- Purpose : Creates a new NIR for ttype UPDATE
50 -- ============================================================================
51
52 PROCEDURE Create_New_Item_Req_Upd(
53 p_row_id IN ROWID
54 ,p_item_catalog_group_id IN NUMBER
55 ,p_item_number IN VARCHAR2
56 ,p_inventory_item_id IN NUMBER
57 ,p_organization_id IN NUMBER
58 ,p_xset_id IN NUMBER --Adding for R12 C
59 ,p_process_flag IN NUMBER
60 ,x_return_status OUT NOCOPY VARCHAR2);
61
62 -- ============================================================================
63 -- API Name : validate_style_sku
64 -- Description : This procedure will be called from IOI
65 -- (after all other validations, including lifecycle and phase)
66 -- to validate the Style/SKU attributes of the item.
67 -- ============================================================================
68 FUNCTION validate_style_sku (P_Row_Id IN ROWID,
69 P_Xset_id IN NUMBER,
70 X_Err_Text IN OUT NOCOPY VARCHAR2)
71 RETURN INTEGER;
72
73 --End Bug: 5258295
74 -- ============================================================================
75 -- API Name: Check_LifeCycle
76 --
77 -- IN: Catalog Group Id
78 -- Lifecycle Id
79 --
80 -- Returns: TRUE if the lifecycle is valid for the catalog group
81 -- FALSE if the lifecycle is NOT valid for the catalog group
82 -- ============================================================================
83
84 FUNCTION Check_LifeCycle (p_catalog_group_id IN NUMBER,
85 p_lifecycle_id IN NUMBER)
86 RETURN BOOLEAN IS
87
88 CURSOR c_Check_LifeCycle (p_catalog_group_id NUMBER,
89 p_lifecycle_id NUMBER) IS
90 SELECT 'x'
91 FROM ego_obj_type_lifecycles eotl,
92 pa_ego_lifecycles_phases_v lc,
93 fnd_objects o
94 WHERE lc.proj_element_id = p_lifecycle_id
95 AND lc.proj_element_id = eotl.lifecycle_id
96 AND lc.object_type = 'PA_STRUCTURES'
97 AND eotl.object_id = o.object_id
98 AND eotl.object_classification_code in
99 ( SELECT to_char(ic.item_catalog_group_id)
100 FROM mtl_item_catalog_groups_b ic
101 CONNECT BY PRIOR ic.parent_catalog_group_id = ic.item_catalog_group_id
102 START WITH ic.item_catalog_group_id = p_catalog_group_id
103 )
104 AND o.obj_name = 'EGO_ITEM';
105
106 l_exists VARCHAR2(1);
107
108 BEGIN
109
110 OPEN c_Check_LifeCycle ( p_catalog_group_id => p_catalog_group_id,
111 p_lifecycle_id => p_lifecycle_id);
112
113 FETCH c_Check_LifeCycle INTO l_exists;
114
115 IF ( c_Check_LifeCycle%NOTFOUND ) THEN
116 CLOSE c_Check_LifeCycle;
117 Return FALSE;
118 END IF;
119
120 CLOSE c_Check_LifeCycle;
121
122 RETURN TRUE;
123
124 EXCEPTION WHEN OTHERS THEN
125 Return FALSE;
126
127 END Check_LifeCycle;
128
129 -- ============================================================================
130 -- API Name: Check_LifeCycle_Phase
131 --
132 -- IN: Lifecycle Id
133 -- Lifecycle Phase Id
134 --
135 -- Returns: TRUE if the lifecycle phase is valid for the lifecycle
136 -- FALSE if the lifecycle is NOT valid for the lifecycle
137 -- ============================================================================
138
139
140 FUNCTION Check_LifeCycle_Phase ( p_lifecycle_id IN NUMBER,
141 p_lifecycle_phase_id IN NUMBER)
142 RETURN BOOLEAN IS
143
144 CURSOR c_Check_Lifecyle_Phase ( p_lifecycle_id NUMBER,
145 p_lifecycle_phase_id NUMBER) IS
146 --4932389 : Perf fixes for lifecycle-phase-status queries.
147 SELECT 'x'
148 FROM pa_proj_element_versions pev_l
149 ,pa_lifecycle_usages plu
150 ,pa_proj_element_versions pev_p
151 ,pa_proj_elements ppe_p
152 ,pa_project_statuses pc
153 WHERE pev_l.object_type = 'PA_STRUCTURES'
154 AND pev_l.proj_element_id = p_lifecycle_id
155 AND pev_l.project_id = 0
156 AND plu.usage_type = 'PRODUCTS'
157 AND plu.lifecycle_id = pev_l.proj_element_id
158 AND pev_p.proj_element_id = p_lifecycle_phase_id
159 AND pev_l.element_version_id = pev_p.parent_structure_version_id
160 AND pev_p.proj_element_id = ppe_p.proj_element_id
161 AND ppe_p.phase_code = pc.project_status_code
162 AND (pc.start_date_active IS NULL OR pc.start_date_active <= SYSDATE)
163 AND (pc.end_date_active IS NULL OR pc.end_date_active >= SYSDATE)
164 ORDER BY pev_p.display_sequence ;
165
166
167 l_exists VARCHAR2(1);
168
169 BEGIN
170
171 OPEN c_Check_Lifecyle_Phase ( p_lifecycle_id => p_lifecycle_id,
172 p_lifecycle_phase_id => p_lifecycle_phase_id);
173
174 FETCH c_Check_Lifecyle_Phase INTO l_exists;
175
176 IF ( c_Check_Lifecyle_Phase%NOTFOUND ) THEN
177 CLOSE c_Check_Lifecyle_Phase;
178 Return FALSE;
179 END IF;
180
181 CLOSE c_Check_Lifecyle_Phase;
182
183 RETURN TRUE;
184
185 EXCEPTION WHEN OTHERS THEN
186 Return FALSE;
187
188 END Check_LifeCycle_Phase;
189
190 -- ============================================================================
191 -- API Name: Get_Initial_LifeCycle_Phase
192 --
193 -- IN: Lifecycle Id
194 --
195 -- Returns: Initial Phase Id if found for the given lifecycle
196 -- 0 if NO phases found for the given lifecycle
197 -- ============================================================================
198
199 FUNCTION Get_Initial_Lifecycle_Phase ( p_lifecycle_id IN NUMBER)
200 RETURN NUMBER IS
201
202 CURSOR c_Get_Lifecyle_Phase ( p_lifecycle_id NUMBER) IS
203 --4932389 : Perf fixes for lifecycle-phase-status queries.
204 SELECT pev_p.proj_element_id
205 FROM pa_proj_element_versions pev_l
206 ,pa_lifecycle_usages plu
207 ,pa_proj_element_versions pev_p
208 ,pa_proj_elements ppe_p
209 ,pa_project_statuses pc
210 WHERE pev_l.object_type = 'PA_STRUCTURES'
211 AND pev_l.proj_element_id = p_lifecycle_id
212 AND pev_l.project_id = 0
213 AND plu.usage_type = 'PRODUCTS'
214 AND plu.lifecycle_id = pev_l.proj_element_id
215 AND pev_l.element_version_id = pev_p.parent_structure_version_id
216 AND pev_p.proj_element_id = ppe_p.proj_element_id
217 AND ppe_p.phase_code = pc.project_status_code
218 AND (pc.start_date_active IS NULL OR pc.start_date_active <= SYSDATE)
219 AND (pc.end_date_active IS NULL OR pc.end_date_active >= SYSDATE)
220 ORDER BY pev_p.display_sequence ;
221
222 l_phase_id NUMBER;
223
224 BEGIN
225
226 OPEN c_Get_Lifecyle_Phase ( p_lifecycle_id => p_lifecycle_id );
227
228 FETCH c_Get_Lifecyle_Phase INTO l_phase_id;
229
230 IF ( c_Get_Lifecyle_Phase%NOTFOUND ) THEN
231 CLOSE c_Get_Lifecyle_Phase;
232 Return 0;
233 END IF;
234
235 CLOSE c_Get_Lifecyle_Phase;
236
237 RETURN l_phase_id;
238
239 EXCEPTION WHEN OTHERS THEN
240 Return 0;
241
242 END Get_Initial_Lifecycle_Phase;
243
244 --Start 2777118:Item Lifecycle and Phase validations
245
246 PROCEDURE Create_Validation(
247 P_Lifecycle_Id IN NUMBER
248 ,P_Phase_Id IN NUMBER
249 ,P_Catalog_Group_Id IN NUMBER
250 ,P_Status_Code IN VARCHAR2
251 ,P_Rowid IN ROWID
252 ,X_Error_Column OUT NOCOPY VARCHAR2
253 ,X_Error_Code OUT NOCOPY VARCHAR2) IS
254
255 CURSOR c_check_item_phase_status(cp_phase_id NUMBER
256 ,cp_status_code VARCHAR2)IS
257 SELECT 'Y'
258 FROM DUAL
259 WHERE EXISTS ( SELECT NULL
260 FROM mtl_item_Catalog_groups_b
261 WHERE NVL(NEW_ITEM_REQUEST_REQD,'N') = 'Y'
262 AND item_Catalog_group_id = P_Catalog_Group_Id)
263 --4932389 : Perf fixes for lifecycle-phase-status queries.
264 OR EXISTS (SELECT NULL
265 FROM ego_lcphase_item_status status
266 ,pa_ego_phases_v phase
267 WHERE status.phase_code = phase.phase_code
268 AND proj_element_id = cp_phase_id
269 AND status.item_status_code = cp_status_code);
270
271 --Bug 7651939: item_type check is moved to validate_items_lifecycle function
272 --Refer to that function in this same package
273
274 l_valid_status VARCHAR2(1) := 'N';
275
276 BEGIN
277
278 X_Error_Code := NULL;
279 X_Error_Column := NULL;
280
281 --2891650 : IOI should not default LC phase.
282 IF P_Phase_Id IS NULL THEN
283 X_Error_Code := 'INV_IOI_PHASE_MANDATORY';
284 X_Error_Column := 'CURRENT_PHASE_ID';
285 Raise LOGGING_ERR;
286 END IF;
287
288 -- Lifecycle validation
289 IF NOT INV_EGO_REVISION_VALIDATE.Check_LifeCycle
290 (p_catalog_group_id => P_Catalog_Group_Id,
291 p_lifecycle_id => P_Lifecycle_Id)
292 THEN
293 X_Error_Code := 'INV_IOI_INVALID_LC_CATALOG';
294 X_Error_Column := 'LIFECYCLE_ID';
295 Raise LOGGING_ERR;
296 ELSE
297 -- Lifecycle Phase Validation
298 IF NOT INV_EGO_REVISION_VALIDATE.Check_LifeCycle_Phase
299 ( p_lifecycle_id => P_Lifecycle_Id,
300 p_lifecycle_phase_id => P_Phase_Id)
301 THEN
302 X_Error_Code := 'INV_IOI_INVALID_PHASE';
303 X_Error_Column := 'CURRENT_PHASE_ID';
304 Raise LOGGING_ERR;
305 END IF;
306
307 -- Phase Item Status validation
308 OPEN c_check_item_phase_status(cp_phase_id => P_Phase_Id
309 ,cp_status_code => P_Status_Code);
310 FETCH c_check_item_phase_status INTO l_valid_status;
311 CLOSE c_check_item_phase_status;
312
313 IF l_valid_status IS NULL OR l_valid_status ='N' THEN
314 X_Error_Code := 'INV_IOI_INVALID_PHASE_STATUS';
315 X_Error_Column := 'INVENTORY_ITEM_STATUS';
316 Raise LOGGING_ERR;
317 END IF;
318
319 END IF; -- Life Cycle Validation
320
321 END Create_Validation;
322
323 PROCEDURE Update_Validation(
324 P_Org_Id IN NUMBER
325 ,P_Item_Id IN NUMBER
326 ,P_Lifecycle_Id IN NUMBER
327 ,P_Phase_Id IN NUMBER
328 ,P_Catalog_Group_Id IN NUMBER
329 ,P_Status_Code IN VARCHAR2
330 ,P_Rowid IN ROWID
331 ,X_Error_Column OUT NOCOPY VARCHAR2
332 ,X_Error_Code OUT NOCOPY VARCHAR2) IS
333
334
335 Cursor c_get_lifecycle_phase IS
336 SELECT lifecycle_id,
337 current_phase_id,
338 item_catalog_group_id,
339 approval_status -- Added for 4046435
340 FROM mtl_system_items_b
341 WHERE inventory_item_id = P_Item_Id
342 /*Changed for FP bug 8213894 with base bug 7492587*/
343 /* AND organization_id IN
344 (SELECT organization_id
345 FROM mtl_parameters
346 WHERE organization_id = master_organization_id); */
347 AND organization_id = P_Org_Id;
348
349 --In update mode if item is unapproved donot validate "Pending" status
350 Cursor c_check_item_phase_status
351 (cp_phase_id NUMBER
352 ,cp_status_code VARCHAR2)IS
353 SELECT 'Y'
354 FROM DUAL
355 WHERE EXISTS ( SELECT NULL
356 FROM mtl_system_items_b
357 WHERE inventory_item_id = P_Item_Id
358 AND organization_id = P_Org_Id
359 AND NVL(approval_status,'A') <> 'A'
360 )
361 --4932389 : Perf fixes for lifecycle-phase-status queries.
362 OR EXISTS (SELECT NULL
363 FROM ego_lcphase_item_status status
364 ,pa_ego_phases_v phase
365 WHERE status.phase_code = phase.phase_code
366 AND proj_element_id = cp_phase_id
367 AND status.item_status_code = cp_status_code);
368
369 --Promote/Demote only to the immediate phase before/after current -Bug5375723
370 Cursor c_display_seq_phase(cp_phase_id NUMBER)
371 IS
372 SELECT display_sequence
373 FROM pa_ego_phases_v
374 WHERE proj_element_id = cp_phase_id ;
375
376 l_Old_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
377 l_Old_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
378 l_Old_catalog_group_Id mtl_system_items_b.item_catalog_group_id%TYPE;
379 l_valid_status VARCHAR2(1) := 'N';
380 l_Policy_Code VARCHAR2(20);
381 l_Return_Status VARCHAR2(1);
382 l_Error_Code NUMBER;
383 l_Msg_Count NUMBER;
384 l_Msg_Data VARCHAR2(2000);
385 l_approval_status mtl_system_items_b.approval_status%TYPE; --Bug 4046435
386 -- Bug 5375723
387 l_old_disp_seq NUMBER;
388 l_new_disp_seq NUMBER;
389
390 BEGIN
391
392 X_Error_Code := NULL;
393 X_Error_Column := NULL;
394
395 --Bug 4046435
396 OPEN c_get_lifecycle_phase;
397 FETCH c_get_lifecycle_phase
398 INTO l_Old_Lifecycle_Id,l_Old_Phase_Id,l_Old_catalog_group_Id,l_approval_status ;
399 CLOSE c_get_lifecycle_phase;
400
401 IF ( (P_Lifecycle_Id <> NVL(l_Old_Lifecycle_Id, -1)) OR
402 (P_Phase_Id <> NVL(l_Old_Phase_Id, -1))
403 ) AND (NVL(l_approval_status, 'NULL VALUE') = 'S')
404 AND (l_Old_Lifecycle_Id is NOT NULL) THEN --BUG 4046435
405 X_Error_Code := 'INV_IOI_LC_CHANGE_DISALLOWED';
406 X_Error_Column := 'LIFECYCLE_ID';
407 Raise LOGGING_ERR;
408 END IF;
409 --Bug 4046435
410
411 IF P_Lifecycle_Id <> NVL(l_Old_Lifecycle_Id, -1) OR
412 NVL(P_Catalog_Group_Id,-1) <> NVL(l_Old_catalog_group_Id, -1) THEN--Bug:4114952
413 /* 3342860: Life Cycle now can be changed during Item update.
414 X_Error_Code := 'INV_IOI_INVALID_LC_CHANGE';
415 X_Error_Column := 'LIFECYCLE_ID';
416 Raise LOGGING_ERR;
417 */
418 Create_Validation(
419 P_Lifecycle_Id => P_Lifecycle_Id
420 ,P_Phase_Id => P_Phase_Id
421 ,P_Catalog_Group_Id => P_Catalog_Group_Id
422 ,P_Status_Code => P_Status_Code
423 ,P_Rowid => P_Rowid
424 ,X_Error_Column => X_Error_Column
425 ,X_Error_Code => X_Error_Code);
426 ELSE
427 --2891650 : IOI should not default LC phase.
428 IF P_Phase_Id IS NULL THEN
429 X_Error_Code := 'INV_IOI_PHASE_MANDATORY';
430 X_Error_Column := 'CURRENT_PHASE_ID';
431 Raise LOGGING_ERR;
432 END IF;
433
434 -- Lifecycle validation
435 IF P_Lifecycle_Id <> l_Old_Lifecycle_Id THEN
436 IF NOT INV_EGO_REVISION_VALIDATE.Check_LifeCycle
437 (p_catalog_group_id => P_Catalog_Group_Id,
438 p_lifecycle_id => P_Lifecycle_Id)
439 THEN
440 X_Error_Code := 'INV_IOI_INVALID_LC_CATALOG';
441 X_Error_Column := 'LIFECYCLE_ID';
442 Raise LOGGING_ERR;
443 END IF;
444 END IF;
445
446 IF X_Error_Code IS NULL THEN
447 -- Lifecycle Phase Validation
448 --Check phase change is allowed or not
449 IF P_Phase_Id <> l_Old_Phase_Id THEN
450
451 IF NOT INV_EGO_REVISION_VALIDATE.Check_LifeCycle_Phase
452 ( p_lifecycle_id => P_Lifecycle_Id,
453 p_lifecycle_phase_id => P_Phase_Id)
454 THEN
455 X_Error_Code := 'INV_IOI_INVALID_PHASE';
456 X_Error_Column := 'CURRENT_PHASE_ID';
457 Raise LOGGING_ERR;
458 END IF;
459
460 IF P_Lifecycle_Id = l_Old_Lifecycle_Id THEN
461 -- Start of bug 5375723
462 /*
463 BUG 11064547 swuppala: allow skipping of Phase during Promote/ Demote START
464
465 OPEN c_display_seq_phase(cp_phase_id => l_Old_Phase_Id);
466 FETCH c_display_seq_phase INTO l_old_disp_seq;
467 CLOSE c_display_seq_phase;
468
469 OPEN c_display_seq_phase(cp_phase_id => P_Phase_Id);
470 FETCH c_display_seq_phase INTO l_new_disp_seq;
471 CLOSE c_display_seq_phase;
472
473 IF ( abs(l_old_disp_seq - l_new_disp_seq) <> 1 ) THEN
474 X_Error_Code := 'INV_IOI_INVALID_PHASE';
475 X_Error_Column := 'CURRENT_PHASE_ID';
476 Raise LOGGING_ERR;
477 END IF;
478 -- End of bug 5375723
479 BUG 11064547 swuppala: allow skipping of Phase during Promote/ Demote END
480 */
481
482 EGO_LIFECYCLE_USER_PUB.get_policy_for_phase_change
483 (P_API_VERSION => 1.0
484 ,P_INVENTORY_ITEM_ID => P_Item_Id
485 ,P_ORGANIZATION_ID => P_Org_Id
486 ,P_CURR_PHASE_ID => l_Old_Phase_Id
487 ,P_FUTURE_PHASE_ID => P_Phase_Id
488 ,P_PHASE_CHANGE_CODE => NULL
489 ,P_LIFECYCLE_ID => P_Lifecycle_Id
490 ,X_POLICY_CODE => l_Policy_Code
491 ,X_RETURN_STATUS => l_Return_Status
492 ,X_ERRORCODE => l_Error_Code
493 ,X_MSG_COUNT => l_Msg_Count
494 ,X_MSG_DATA => l_Msg_Data);
495
496 IF l_Policy_Code <> 'ALLOWED' THEN
497 X_Error_Code := 'INV_IOI_PHASE_CHANGE_NOT_VALID';
498 X_Error_Column := 'CURRENT_PHASE_ID';
499 Raise LOGGING_ERR;
500 END IF;
501 END IF;
502
503 END IF;
504
505 -- Phase Item Status validation
506 OPEN c_check_item_phase_status(cp_phase_id => P_Phase_Id
507 ,cp_status_code => P_Status_Code);
508 FETCH c_check_item_phase_status INTO l_valid_status;
509 CLOSE c_check_item_phase_status;
510
511
512 IF l_valid_status IS NULL OR l_valid_status ='N' THEN
513 X_Error_Code := 'INV_IOI_INVALID_PHASE_STATUS';
514 X_Error_Column := 'INVENTORY_ITEM_STATUS';
515 Raise LOGGING_ERR;
516 END IF;
517 END IF; -- Life Cycle Validation X_error_code
518
519 END IF;
520
521 END Update_Validation;
522
523 PROCEDURE Validate_Child_Items(
524 P_Org_Id IN NUMBER
525 ,P_Item_Id IN NUMBER
526 ,P_Lifecycle_Id IN NUMBER
527 ,P_Phase_Id IN NUMBER
528 ,P_Catalog_Group_Id IN NUMBER
529 ,P_Status_Code IN VARCHAR2
530 ,P_Transaction_Type IN VARCHAR2
531 ,P_Rowid IN ROWID
532 ,X_Error_Column OUT NOCOPY VARCHAR2
533 ,X_Error_Code OUT NOCOPY VARCHAR2) IS
534
535 CURSOR c_get_master_details(cp_item_id NUMBER) IS
536 SELECT Lifecycle_id, Current_Phase_Id
537 FROM mtl_system_items_b
538 WHERE inventory_item_id = cp_item_id
539 AND organization_id IN
540 (SELECT organization_id
541 FROM mtl_parameters
542 WHERE organization_id = master_organization_id)
543 UNION
544 SELECT Lifecycle_id, Current_Phase_Id
545 FROM mtl_system_items_interface
546 WHERE inventory_item_id = cp_item_id
547 AND process_flag = 4
548 AND organization_id IN
549 (SELECT organization_id
550 FROM mtl_parameters
551 WHERE organization_id = master_organization_id);
552
553 CURSOR c_get_control_level IS
554 SELECT control_level
555 FROM mtl_item_attributes
556 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
557
558
559 l_Master_Phase_Id mtl_system_items_b.current_phase_id%TYPE;
560 l_Master_Lifecycle_Id mtl_system_items_b.lifecycle_id%TYPE;
561 l_status_control NUMBER(2) := 1; --Master controlled
562 BEGIN
563 X_Error_Code := NULL;
564 X_Error_Column := NULL;
565
566 OPEN c_get_master_details(cp_item_id => P_Item_Id);
567 FETCH c_get_master_details
568 INTO l_Master_Lifecycle_Id,l_Master_Phase_Id;
569 CLOSE c_get_master_details;
570
571 IF l_Master_Lifecycle_Id IS NULL OR
572 l_Master_Lifecycle_Id <> P_Lifecycle_Id
573 THEN
574 X_Error_Code := 'INV_IOI_ORGLIFECYCLE_CONFLICT';
575 X_Error_Column := 'LIFECYCLE_ID';
576 Raise LOGGING_ERR;
577 ELSE
578 OPEN c_get_control_level;
579 FETCH c_get_control_level INTO l_status_control;
580 CLOSE c_get_control_level ;
581
582 --2891650 : IOI should not default LC phase.
583 IF P_Phase_Id IS NULL THEN
584 X_Error_Code := 'INV_IOI_PHASE_MANDATORY';
585 X_Error_Column := 'CURRENT_PHASE_ID';
586 Raise LOGGING_ERR;
587 END IF;
588
589 IF l_Master_Phase_Id IS NULL OR
590 (l_Master_Phase_Id <> P_Phase_Id AND l_status_control = 1)
591 THEN
592 X_Error_Code := 'INV_IOI_ORGPHASE_CONFLICT';
593 X_Error_Column := 'CURRENT_PHASE_ID';
594 Raise LOGGING_ERR;
595 END IF;
596
597 IF P_Transaction_Type ='CREATE' THEN
598
599 Create_Validation(
600 P_Lifecycle_Id => P_Lifecycle_Id
601 ,P_Phase_Id => P_Phase_Id
602 ,P_Catalog_Group_Id => P_Catalog_Group_Id
603 ,P_Status_Code => P_Status_Code
604 ,P_Rowid => P_Rowid
605 ,X_Error_Column => X_Error_Column
606 ,X_Error_Code => X_Error_Code);
607
608 ELSIF P_Transaction_Type ='UPDATE' THEN
609
610 Update_Validation(
611 P_Org_Id => P_Org_Id
612 ,P_Item_Id => P_Item_Id
613 ,P_Lifecycle_Id => P_Lifecycle_Id
614 ,P_Phase_Id => P_Phase_Id
615 ,P_Catalog_Group_Id => P_Catalog_Group_Id
616 ,P_Status_Code => P_Status_Code
617 ,P_Rowid => P_Rowid
618 ,X_Error_Column => X_Error_Column
619 ,X_Error_Code => X_Error_Code);
620
621 END IF; --Transaction Type
622 END IF;
623 END Validate_Child_Items;
624
625 /*
626 * Private API to get the display name of attributes
627 */
628 FUNCTION Get_Attr_Display_Name(p_attr_group_type VARCHAR2,
629 p_attr_group_name VARCHAR2,
630 p_attr_name VARCHAR2)
631 RETURN VARCHAR2 IS
632 l_disp_name VARCHAR2(4000);
633 BEGIN
634 SELECT TL.FORM_LEFT_PROMPT ATTR_DISPLAY_NAME
635 INTO l_disp_name
636 FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL ,FND_DESCR_FLEX_COL_USAGE_TL TL
637 WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
638 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
639 AND FL_COL.APPLICATION_ID = 431
640 AND FL_COL.END_USER_COLUMN_NAME = p_attr_name
641 AND FL_COL.APPLICATION_ID = TL.APPLICATION_ID
642 AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
643 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
644 AND FL_COL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
645 AND TL.LANGUAGE = USERENV('LANG');
646
647 RETURN l_disp_name;
648 EXCEPTION WHEN NO_DATA_FOUND THEN
649 RETURN p_attr_name;
650 END Get_Attr_Display_Name;
651
652 FUNCTION validate_items_lifecycle(
653 P_Org_Id IN NUMBER
654 ,P_All_Org IN NUMBER DEFAULT 2
655 ,P_Prog_AppId IN NUMBER DEFAULT -1
656 ,P_Prog_Id IN NUMBER DEFAULT -1
657 ,P_Request_Id IN NUMBER DEFAULT -1
658 ,P_User_Id IN NUMBER DEFAULT -1
659 ,P_Login_Id IN NUMBER DEFAULT -1
660 ,P_Set_id IN NUMBER DEFAULT -999
661 ,P_Process_Flag IN NUMBER DEFAULT 4
662 ,X_Err_Text IN OUT NOCOPY VARCHAR2
663 )
664 RETURN INTEGER IS
665
666 -- 5351611 Modified both the cursors to use EXISTS/NOT EXISTS
667 -- instead of IN/NOT IN
668 CURSOR c_get_master_items IS
669 SELECT rowid,
670 organization_id,
671 inventory_item_id,
672 lifecycle_id,
673 current_phase_Id,
674 inventory_item_status_code,
675 item_catalog_group_Id,
676 transaction_id,
677 transaction_type,
678 item_number,
679 unit_weight,
680 weight_uom_code,
681 style_item_flag,
682 trade_item_descriptor,
683 gdsn_outbound_enabled_flag,
684 primary_uom_code
685 FROM mtl_system_items_interface int
686 WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
687 AND int.set_process_id = P_Set_id
688 AND int.process_flag = P_Process_Flag
689 AND EXISTS -- organization_id IN
690 (SELECT 1 --organization_id
691 FROM mtl_parameters mp
692 WHERE int.organization_id = mp.master_organization_id
693 AND mp.organization_id = mp.master_organization_id )
694 FOR UPDATE OF int.current_phase_id, int.process_flag;
695
696 CURSOR c_get_child_items IS
697 SELECT rowid,
698 organization_id,
699 inventory_item_id,
700 lifecycle_id,
701 current_phase_Id,
702 inventory_item_status_code,
703 item_catalog_group_Id,
704 transaction_id,
705 transaction_type,
706 item_number,
707 unit_weight,
708 weight_uom_code,
709 style_item_flag,
710 trade_item_descriptor,
711 gdsn_outbound_enabled_flag,
712 primary_uom_code
713 FROM mtl_system_items_interface int
714 WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
715 AND int.set_process_id = P_Set_id
716 AND int.process_flag = P_Process_Flag
717 AND NOT EXISTS -- organization_id NOT IN
718 (SELECT 1 -- organization_id
719 FROM mtl_parameters mp
720 WHERE int.organization_id = mp.master_organization_id
721 AND mp.organization_id = mp.master_organization_id)
722 FOR UPDATE OF int.current_phase_id,int.process_flag;
723
724 CURSOR c_ego_exists IS
725 SELECT 'Y'
726 FROM fnd_objects
727 WHERE obj_name = 'EGO_ITEM';
728
729 CURSOR c_get_existing_item_details(cp_inventory_item_id IN NUMBER,
730 cp_organization_id IN NUMBER)
731 IS
732 SELECT item_catalog_group_Id,approval_status
733 ,unit_weight, weight_uom_code,trade_item_descriptor
734 ,gdsn_outbound_enabled_flag
735 ,lifecycle_id, current_phase_id -- Bug 11768298
736 FROM mtl_system_items
737 WHERE inventory_item_id = cp_inventory_item_id
738 AND organization_id = cp_organization_id;
739
740 CURSOR c_pack_item_type (cp_pack_item_type IN VARCHAR2)
741 IS
742 SELECT 1 FROM ego_value_set_values_v
743 WHERE value_set_name = 'TradeItemDescVS'
744 AND internal_name = cp_pack_item_type;
745
746 CURSOR c_base_uom (cp_primary_uom_code IN VARCHAR2)
747 IS
748 SELECT base_uom_flag
749 FROM mtl_units_of_measure
750 WHERE uom_code = cp_primary_uom_code;
751
752 --Bug 7651939: move item_type check from create_validation to here
753 --Bug:3777954 In create mode if catalog category has NIR setup , item must be engineering item
754 Cursor c_check_item_type ( cp_row_id IN ROWID
755 , cp_catalog_group_id IN NUMBER
756 )
757 IS
758 SELECT 'Y'
759 FROM DUAL
760 WHERE EXISTS ( SELECT NULL
761 FROM mtl_item_Catalog_groups_b
762 WHERE NVL(NEW_ITEM_REQUEST_REQD,'N') = 'Y'
763 AND item_Catalog_group_id = cp_catalog_group_id
764 )
765 AND EXISTS ( SELECT NULL
766 FROM mtl_system_items_interface
767 WHERE NVL(eng_item_flag,'N')='N'
768 AND rowid = cp_row_id);
769
770 l_valid_status VARCHAR2(1) := 'N';
771 l_old_pack_item_type MTL_SYSTEM_ITEMS_B.trade_item_descriptor%TYPE;
772 l_ret_status VARCHAR2(100);
773 l_valid_pack_type NUMBER := 0;
774 l_item_in_pack VARCHAR2(1) := FND_API.G_FALSE;
775 l_old_gdsn_flag VARCHAR2(1);
776 l_is_primary_uom_base VARCHAR2(1);
777 l_err_text VARCHAR2(1000);
778 l_ego_exists VARCHAR2(1) := 'N';
779 l_error_status NUMBER := 0;
780 l_error_logged NUMBER := 0;
781 l_master_lifecycle_id NUMBER;
782 X_Error_Code VARCHAR2(100);
783 X_Error_Column VARCHAR2(100) := 'LIFECYCLE_ID';
784 l_error_msg VARCHAR2(2000);
785 l_return_status VARCHAR2(100);
786 X_RETURN_STATUS VARCHAR2(3);
787 X_MSG_COUNT NUMBER;
788 X_MSG_DATA VARCHAR2(240);
789 l_old_catalog_group_id NUMBER;
790 l_approval_status VARCHAR2(1);
791 l_old_unit_weight MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT%TYPE;
792 l_old_weight_uom_code MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE%TYPE;
793 l_valid VARCHAR2(100) := FND_API.G_TRUE;
794 l_is_gdsn NUMBER;
795 l_unit_wt_disp_name VARCHAR2(1000);
796 l_unit_wt_uom_disp_name VARCHAR2(1000);
797 l_gtid_disp_name VARCHAR2(1000);
798
799 -- Bug 11768298 : Start
800 l_old_lifecycle_id MTL_SYSTEM_ITEMS_B.lifecycle_id%TYPE;
801 l_old_current_phase_id MTL_SYSTEM_ITEMS_B.current_phase_id%TYPE;
802 -- Bug 11768298 : End
803
804 BEGIN
805
806 OPEN c_ego_exists;
807 FETCH c_ego_exists INTO l_ego_exists;
808 CLOSE c_ego_exists;
809
810 IF (l_ego_exists ='Y' AND INV_Item_Util.g_Appl_Inst.EGO <> 0) THEN -- Bug 4175124 THEN
811
812
813 -- Bug 11787276: Bug fix 6670893 introduced G_PROCESS_CONTROL_HTML_API.
814 -- Similar to bug fix 6670893, other cases than HTML UI, the code flow needs to go through
815 -- G_PROCESS_CONTRL and G_PROCESS_CONTROL_HTML_API are set in EGO_ITEM_PUB package
816 -- (G_PROCESS_CONTROL, G_PROCESS_CONTROL_HTML_API) = ('PLM_UI:Y', 'API') in PWB/Open API
817 -- (G_PROCESS_CONTROL, G_PROCESS_CONTROL_HTML_API) = ('EGO_ITEM_BULK_LOAD', 'HTML') in Concurrent/Excel import
818 -- (G_PROCESS_CONTROL, G_PROCESS_CONTROL_HTML_API) = ('PLM_UI:Y', 'HTML') in HTML UI
819
820 IF (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'PLM_UI:Y') = 0 OR
821 SUBSTR(NVL(G_PROCESS_CONTROL_HTML_API, 'HTML'), 1,3) = 'API' ) --Bug 11787276
822 THEN
823
824 FOR cur IN c_get_master_items LOOP
825
826 l_error_status := 0;
827 l_error_logged := 0;
828
829 -- Bug 7651939: move item_type check from create_validation to here
830 -- Only check type when item is CREATED for MASTER ORG
831 l_valid_status := 'N';
832 IF cur.transaction_type ='CREATE' THEN
833 OPEN c_check_item_type(cur.rowid, cur.item_catalog_group_id);
834 FETCH c_check_item_type INTO l_valid_status;
835 CLOSE c_check_item_type;
836
837 IF l_valid_status ='Y' THEN
838 l_error_logged :=
839 INVPUOPI.mtl_log_interface_err(
840 cur.organization_id,
841 P_User_Id,
842 P_Login_Id,
843 P_Prog_AppId,
844 P_Prog_Id,
845 P_Request_id,
846 cur.transaction_id,
847 l_error_msg,
848 'ENG_ITEM_FLAG',
849 'MTL_SYSTEM_ITEMS_INTERFACE',
850 'INV_IOI_NIR_NO_MFG_ITEM',
851 X_Err_Text);
852 IF l_error_logged < 0 THEN
853 Raise LOGGING_ERR;
854 END IF;
855 l_error_status := 1;
856 END IF; -- valid_status
857
858 END IF; -- transaction_type
859
860 IF cur.lifecycle_id IS NOT NULL THEN
861 IF cur.transaction_type ='CREATE' THEN
862 BEGIN
863 Create_Validation(
864 P_Lifecycle_Id => cur.lifecycle_id
865 ,P_Phase_Id => cur.current_phase_id
866 ,P_Catalog_Group_Id => cur.item_catalog_group_id
867 ,P_Status_Code => cur.inventory_item_status_code
868 ,P_Rowid => cur.rowid
869 ,X_Error_Column => X_Error_Column
870 ,X_Error_Code => X_Error_Code);
871
872 EXCEPTION
873 WHEN LOGGING_ERR THEN
874 l_error_logged :=
875 INVPUOPI.mtl_log_interface_err(
876 cur.organization_id,
877 P_User_Id,
878 P_Login_Id,
879 P_Prog_AppId,
880 P_Prog_Id,
881 P_Request_id,
882 cur.transaction_id,
883 l_error_msg,
884 X_Error_Column,
885 'MTL_SYSTEM_ITEMS_INTERFACE',
886 X_Error_Code,
887 X_Err_Text);
888 IF l_error_logged < 0 THEN
889 Raise LOGGING_ERR;
890 END IF;
891 l_error_status := 1;
892 END; -- Exception Block
893
894 ELSIF cur.transaction_type ='UPDATE' THEN
895
896 BEGIN
897
898 Update_Validation(
899 P_Org_Id => cur.organization_id
900 ,P_Item_Id => cur.inventory_item_id
901 ,P_Lifecycle_Id => cur.lifecycle_id
902 ,P_Phase_Id => cur.current_phase_id
903 ,P_Catalog_Group_Id => cur.item_catalog_group_id
904 ,P_Status_Code => cur.inventory_item_status_code
905 ,P_Rowid => cur.rowid
906 ,X_Error_Column => X_Error_Column
907 ,X_Error_Code => X_Error_Code);
908
909 EXCEPTION
910 WHEN LOGGING_ERR THEN
911 l_error_logged :=
912 INVPUOPI.mtl_log_interface_err(
913 cur.organization_id,
914 P_User_Id,
915 P_Login_Id,
916 P_Prog_AppId,
917 P_Prog_Id,
918 P_Request_id,
919 cur.transaction_id,
920 l_error_msg,
921 X_Error_Column,
922 'MTL_SYSTEM_ITEMS_INTERFACE',
923 X_Error_Code,
924 X_Err_Text);
925
926 IF l_error_logged < 0 THEN
927 Raise LOGGING_ERR;
928 END IF;
929 l_error_status := 1;
930 END; -- Exception Block
931 END IF; -- Transaction Type
932 ELSE
933 --3457443 : lifecycle and phase validation during update also.
934 --Life Cyle is null but phase has been provided
935 IF cur.current_phase_id IS NOT NULL THEN
936 l_error_logged := INVPUOPI.mtl_log_interface_err(
937 cur.organization_id,
938 P_User_Id,
939 P_Login_Id,
940 P_Prog_AppId,
941 P_Prog_Id,
942 P_Request_id,
943 cur.transaction_id,
944 l_error_msg,
945 'CURRENT_PHASE_ID',
946 'MTL_SYSTEM_ITEMS_INTERFACE',
947 'INV_IOI_LIFECYCLE_MANDATORY',
948 X_Err_Text);
949
950 IF l_error_logged < 0 THEN
951 Raise LOGGING_ERR;
952 END IF;
953 l_error_status := 1;
954
955 END IF; --Phase id is not null
956 END IF; -- Lifecycle is not null
957
958 IF p_process_flag = 4 THEN --To prevent validations from firing during Change Policy Check
959 /* Bug 5523228 - Added to calidate Unit wt and wt uom against Trade Item Descriptor */
960 /* Bug 5571909 - added check for G_PROCESS_CONTROL and also modified messaging*/
961 /* Following check is commented out for bug 6126443, Fetching old values have to
962 be moved out side the conditional since these values are expected even if the condition fails
963 other loops as well */
964 -- IF ( l_error_status <> 1 AND NVL(G_PROCESS_CONTROL, 'N') <> 'SUPPRESS_ROLLUP' ) THEN
965 OPEN c_get_existing_item_details(cur.inventory_item_id, cur.organization_id);
966 FETCH c_get_existing_item_details INTO l_old_catalog_group_id,l_approval_status,
967 l_old_unit_weight, l_old_weight_uom_code,
968 l_old_pack_item_type,l_old_gdsn_flag
969 ,l_old_lifecycle_id, l_old_current_phase_id; -- Bug 11768298
970 CLOSE c_get_existing_item_details;
971
972 IF ( l_error_status <> 1 AND NVL(G_PROCESS_CONTROL, 'N') <> 'SUPPRESS_ROLLUP' ) THEN
973 IF ( (((cur.unit_weight IS NOT NULL) AND (NVL(cur.unit_weight,-1) <> NVL(l_old_unit_weight,-1)))
974 OR((cur.weight_uom_code IS NOT NULL) AND (NVL(cur.weight_uom_code,-1) <> NVL(l_old_weight_uom_code,-1))))
975 AND cur.transaction_type = 'UPDATE'
976 AND cur.gdsn_outbound_enabled_flag = 'Y')
977 THEN
978 l_valid := EGO_GTIN_PVT.Validate_Unit_Wt_Uom(p_inventory_item_id => cur.inventory_item_id,
979 p_org_id => cur.organization_id );
980
981 IF (l_valid <> FND_API.G_TRUE) THEN
982 l_unit_wt_disp_name := Get_Attr_Display_Name('EGO_MASTER_ITEMS', 'PhysicalAttributes' , 'UNIT_WEIGHT');
983 l_unit_wt_uom_disp_name := Get_Attr_Display_Name('EGO_MASTER_ITEMS', 'PhysicalAttributes' , 'WEIGHT_UOM_CODE');
984 l_gtid_disp_name := Get_Attr_Display_Name('EGO_ITEM_GTIN_ATTRS', 'Trade_Item_Description' , 'Trade_Item_Descriptor');
985 FND_MESSAGE.SET_NAME ( 'EGO', 'EGO_2ATTRS_NOT_EDITABLE');
986 FND_MESSAGE.SET_TOKEN ('ATTR1', l_unit_wt_disp_name);
987 FND_MESSAGE.SET_TOKEN ('ATTR2', l_unit_wt_uom_disp_name);
988 FND_MESSAGE.SET_TOKEN ('GTID', l_gtid_disp_name);
989 l_error_msg := FND_MESSAGE.GET;
990
991 l_error_logged := INVPUOPI.mtl_log_interface_err(
992 cur.organization_id,
993 P_User_Id,
994 P_Login_Id,
995 P_Prog_AppId,
996 P_Prog_Id,
997 P_Request_id,
998 cur.transaction_id,
999 l_error_msg,
1000 'UNIT_WEIGHT',
1001 'MTL_SYSTEM_ITEMS_INTERFACE',
1002 'INV_IOI_ERR_2ATTRS_UNEDITABLE',--added by bug 11894684, replace INV_IOI_ERR with specific message name so we can identify error easily
1003 X_Err_Text);
1004
1005 IF l_error_logged < 0 THEN
1006 Raise LOGGING_ERR;
1007 END IF;
1008 l_error_status := 1;
1009 END IF;
1010 END IF;
1011 END IF;
1012
1013 IF cur.style_item_flag IS NOT NULL THEN
1014 l_ret_status := validate_style_sku ( p_row_id => cur.rowid,
1015 p_xset_id => P_Set_id,
1016 x_err_text => x_err_text);
1017 IF l_ret_status <> 0 THEN
1018
1019 UPDATE mtl_system_items_interface
1020 SET process_flag = 3
1021 WHERE rowid = cur.rowid;
1022
1023 l_error_logged := INVPUOPI.mtl_log_interface_err(
1024 cur.organization_id,
1025 P_User_Id,
1026 P_Login_Id,
1027 P_Prog_AppId,
1028 P_Prog_Id,
1029 P_Request_id,
1030 cur.transaction_id,
1031 SQLERRM,
1032 'STYLE_ITEM_FLAG',
1033 'MTL_SYSTEM_ITEMS_INTERFACE',
1034 'INV_IOI_ERR_VLDATE_STYL_SKU01',--added by bug 11894684, replace INV_IOI_ERR with specific message name so we can identify error easily
1035 x_err_text);
1036 IF l_error_logged < 0 THEN
1037 Raise LOGGING_ERR;
1038 END IF;
1039 l_error_status := 1;
1040 END IF;
1041 END IF;
1042
1043 /* Bug 5389029 - Adding a condition to chk for errors so far */
1044 IF ((cur.item_catalog_group_id IS NOT NULL) AND ( l_error_status <> 1 ) AND (cur.transaction_type = 'UPDATE'))THEN
1045 /* Adding the ttype check and the NVL clause to the ICC change condition */
1046 IF (NVL(l_old_catalog_group_id, -1) <> NVL(cur.item_catalog_group_id,-1)) THEN
1047 /* Bug 5389029 - Passing Int Table values for Lifecycle/Phase */
1048 EGO_INV_ITEM_CATALOG_PVT.Change_Item_Catalog (
1049 P_INVENTORY_ITEM_ID => cur.inventory_item_id
1050 ,P_ORGANIZATION_ID => cur.organization_id
1051 ,P_CATALOG_GROUP_ID => l_old_catalog_group_id
1052 ,P_NEW_CATALOG_GROUP_ID => cur.item_catalog_group_id
1053 ,P_NEW_LIFECYCLE_ID => cur.lifecycle_id
1054 ,P_NEW_PHASE_ID => cur.current_phase_id
1055 ,P_NEW_ITEM_STATUS_CODE => cur.inventory_item_status_code
1056 ,P_COMMIT => FND_API.G_FALSE
1057 ,X_RETURN_STATUS => X_RETURN_STATUS
1058 ,X_MSG_COUNT => X_MSG_COUNT
1059 ,X_MSG_DATA => X_MSG_DATA );
1060
1061 /* Bug 5389029 - Passing the right arguments for clear error log */
1062 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1063 l_error_logged := INVPUOPI.mtl_log_interface_err(
1064 cur.organization_id,
1065 P_User_Id,
1066 P_Login_Id,
1067 P_Prog_AppId,
1068 P_Prog_Id,
1069 P_Request_id,
1070 cur.transaction_id,
1071 X_MSG_DATA,
1072 'ITEM_CATALOG_GROUP_ID',
1073 'MTL_SYSTEM_ITEMS_INTERFACE',
1074 'INV_IOI_ERR_CHANGE_IC_01',--bug 11894684, replace INV_IOI_ERR
1075 X_ERR_TEXT);
1076
1077 IF l_error_logged < 0 THEN
1078 Raise LOGGING_ERR;
1079 END IF;
1080 l_error_status := 1;
1081 ELSE
1082 --Bug: 5258295
1083 l_return_status := INV_EGO_REVISION_VALIDATE.mtl_catalog_group_update (
1084 p_rowid => cur.rowid
1085 ,p_process_flag => p_process_flag --Added for R12 C
1086 ,p_inventory_item_id => cur.inventory_item_id
1087 ,p_organization_id => cur.organization_id
1088 ,p_old_item_cat_grp_id => l_old_catalog_group_id
1089 ,p_new_item_cat_grp_id => cur.item_catalog_group_id
1090 ,p_approval_status => l_approval_status
1091 ,p_item_number => cur.item_number
1092 ,p_transaction_id => cur.transaction_id
1093 ,p_prog_appid => p_prog_appid
1094 ,p_prog_id => p_prog_id
1095 ,p_request_id => p_request_id
1096 ,p_xset_id => P_Set_Id --Added for R12 C
1097 ,p_user_id => p_user_id
1098 ,p_login_id => p_login_id
1099 ,x_err_text => x_err_text);
1100
1101 IF l_return_status <> 0 THEN
1102 l_error_logged := INVPUOPI.mtl_log_interface_err(
1103 cur.organization_id,
1104 P_User_Id,
1105 P_Login_Id,
1106 P_Prog_AppId,
1107 P_Prog_Id,
1108 P_Request_id,
1109 cur.transaction_id,
1110 l_error_msg,
1111 'ITEM_CATALOG_GROUP_ID',
1112 'MTL_SYSTEM_ITEMS_INTERFACE',
1113 x_err_text,
1114 x_err_text);
1115
1116 IF l_error_logged < 0 THEN
1117 Raise LOGGING_ERR;
1118 END IF;
1119 l_error_status := 1;
1120 END IF;
1121 END IF; -- X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS
1122 -- Bug 11768298 : Start
1123 -- If the ICC is not getting changed, checkif LC or LCP are getting changed then handle the LC/LCP change
1124 -- for master and child orgs also.
1125 ELSE
1126 IF(((cur.lifecycle_id IS NOT NULL) OR (cur.current_phase_id IS NOT NULL)) AND (cur.transaction_type = 'UPDATE')) THEN
1127 IF ( (NVL(l_old_lifecycle_id, -1) <> NVL(cur.lifecycle_id,-1)) OR (NVL(l_old_current_phase_id, -1) <> NVL(cur.current_phase_id,-1)) ) THEN
1128 EGO_INV_ITEM_CATALOG_PVT.Change_Item_Lifecycle(
1129 p_inventory_item_id => cur.inventory_item_id,
1130 p_organization_id => cur.organization_id,
1131 p_new_catalog_category_id => cur.item_catalog_group_id,
1132 p_new_lifecycle_id => cur.lifecycle_id,
1133 p_new_phase_id => cur.current_phase_id,
1134 p_new_item_status_code => cur.inventory_item_status_code,
1135 p_commit => FND_API.G_FALSE,
1136 x_return_status => x_return_status,
1137 x_msg_data => x_msg_data,
1138 x_msg_count => x_msg_count);
1139
1140 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1141 l_error_logged := INVPUOPI.mtl_log_interface_err(
1142 cur.organization_id,
1143 P_User_Id,
1144 P_Login_Id,
1145 P_Prog_AppId,
1146 P_Prog_Id,
1147 P_Request_id,
1148 cur.transaction_id,
1149 x_msg_data,
1150 'lifecycle_id',
1151 'MTL_SYSTEM_ITEMS_INTERFACE',
1152 'INV_IOI_ERR_CHANGE_ITEM_LC',--bug 11894684, replace INV_IOI_ERR
1153 x_err_text);
1154 IF l_error_logged < 0 THEN
1155 Raise LOGGING_ERR;
1156 END IF;
1157 l_error_status := 1;
1158 END IF;
1159 END IF;
1160 END IF;
1161 -- Bug 11768298 : End
1162 END IF; -- (l_old_catalog_group_id <> cur.item_catalog_group_id)
1163 END IF; --(cur.item_catalog_group_id IS NOT NULL)
1164
1165 /* Pack Item Type Validations */
1166 IF ((cur.gdsn_outbound_enabled_flag IS NULL OR cur.gdsn_outbound_enabled_flag = 'N')
1167 AND (l_old_gdsn_flag = 'Y')) THEN
1168 l_error_logged := INVPUOPI.mtl_log_interface_err (
1169 Cur.organization_id,
1170 P_User_Id,
1171 P_Login_Id,
1172 P_Prog_AppId,
1173 P_Prog_Id,
1174 P_Request_id,
1175 cur.transaction_id,
1176 l_error_msg,
1177 'GDSN_OUTBOUND_ENABLED_FLAG',
1178 'MTL_SYSTEM_ITEMS_INTERFACE',
1179 'INV_GDSN_UPD_NO_INVALID',
1180 X_Err_Text);
1181 l_error_status := 1;
1182 END IF;
1183
1184 /* Section 2.5.2 Condition 2 Pack item type should be a valid value */
1185 IF cur.trade_item_descriptor IS NOT NULL AND
1186 ((cur.trade_item_descriptor <> NVL(l_old_pack_item_type,'X') AND
1187 cur.transaction_type = 'UPDATE') OR (cur.transaction_type = 'CREATE'))
1188 THEN
1189
1190 OPEN c_pack_item_type (cp_pack_item_type => cur.trade_item_descriptor);
1191 FETCH c_pack_item_type INTO l_valid_pack_type;
1192 CLOSE c_pack_item_type;
1193
1194 IF l_valid_pack_type <> 1 THEN
1195 l_error_logged := INVPUOPI.mtl_log_interface_err (
1196 Cur.organization_id,
1197 P_User_Id,
1198 P_Login_Id,
1199 P_Prog_AppId,
1200 P_Prog_Id,
1201 P_Request_id,
1202 cur.transaction_id,
1203 l_error_msg,
1204 'TRADE_ITEM_DESCRIPTOR',
1205 'MTL_SYSTEM_ITEMS_INTERFACE',
1206 'INV_INVALID_PACK_TYPE',
1207 X_Err_Text);
1208 l_error_status := 1;
1209 END IF;
1210
1211 IF cur.trade_item_descriptor = 'BASE_UNIT_OR_EACH' THEN
1212 OPEN c_base_uom(cp_primary_uom_code => cur.primary_uom_code);
1213 FETCH c_base_uom INTO l_is_primary_uom_base;
1214 CLOSE c_base_uom;
1215 IF (l_is_primary_uom_base <> 'Y') THEN
1216 FND_MESSAGE.Set_Name('EGO', 'EGO_GTID_CANNOT_BE_BASE');
1217 FND_MESSAGE.Set_Token('ATTR_NAME', l_gtid_disp_name);
1218 l_err_text := FND_MESSAGE.GET;
1219 l_error_logged := INVPUOPI.mtl_log_interface_err (
1220 Cur.organization_id,
1221 P_User_Id,
1222 P_Login_Id,
1223 P_Prog_AppId,
1224 P_Prog_Id,
1225 P_Request_id,
1226 cur.transaction_id,
1227 l_err_text,
1228 'TRADE_ITEM_DESCRIPTOR',
1229 'MTL_SYSTEM_ITEMS_INTERFACE',
1230 'INV_IOI_ERR_GTID_NOT_BE_BS_01',--bug 11894684, replace INV_IOI_ERR
1231 X_Err_Text);
1232 l_error_status := 1;
1233 END IF;
1234 END IF;
1235
1236 IF (l_error_status <> 1 AND cur.transaction_type = 'UPDATE')
1237 THEN
1238 /* API call to resolve item in pack or not */
1239 BOM_IMPLODER_PUB.IMPLODER_USEREXIT(
1240 SEQUENCE_ID => null,
1241 ENG_MFG_FLAG => 2,
1242 ORG_ID => cur.organization_id,
1243 IMPL_FLAG => 2,
1244 DISPLAY_OPTION => 1,
1245 LEVELS_TO_IMPLODE => 60,
1246 OBJ_NAME => 'EGO_ITEM',
1247 PK1_VALUE => cur.inventory_item_id,
1248 PK2_VALUE => cur.organization_id,
1249 PK3_VALUE => null,
1250 PK4_VALUE => null,
1251 PK5_VALUE => null,
1252 IMPL_DATE => to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') ,
1253 UNIT_NUMBER_FROM => 'N',
1254 UNIT_NUMBER_TO => 'Y',
1255 ERR_MSG => x_err_text,
1256 ERR_CODE => l_error_logged,
1257 ORGANIZATION_OPTION => 1,
1258 ORGANIZATION_HIERARCHY => null,
1259 SERIAL_NUMBER_FROM => null,
1260 SERIAL_NUMBER_TO => null,
1261 STRUCT_NAME => 'PIM_PBOM_S',
1262 STRUCT_TYPE => 'Packaging Hierarchy',
1263 PREFERRED_ONLY => 2 ,
1264 USED_IN_STRUCTURE => l_item_in_pack);
1265
1266 IF l_item_in_pack = FND_API.G_TRUE THEN
1267 l_error_logged := INVPUOPI.mtl_log_interface_err (
1268 Cur.organization_id,
1269 P_User_Id,
1270 P_Login_Id,
1271 P_Prog_AppId,
1272 P_Prog_Id,
1273 P_Request_id,
1274 cur.transaction_id,
1275 l_error_msg,
1276 'TRADE_ITEM_DESCRIPTOR',
1277 'MTL_SYSTEM_ITEMS_INTERFACE',
1278 'INV_ITEM_IN_PACK',
1279 X_Err_Text);
1280 l_error_status := 1;
1281 END IF;
1282
1283 IF l_error_status <> 1 THEN
1284 /* For update of Trade Item Descriptor GDSN Post Processing is triggered */
1285 EGO_GTIN_PVT.process_gtid_update ( p_inventory_item_id => cur.inventory_item_id
1286 ,p_organization_id => cur.organization_id
1287 ,p_trade_item_desc => cur.trade_item_descriptor
1288 ,x_return_status => x_return_status
1289 ,x_msg_count => x_msg_count
1290 ,x_msg_data => x_msg_data);
1291
1292 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1293 l_error_logged := INVPUOPI.mtl_log_interface_err (
1294 Cur.organization_id,
1295 P_User_Id,
1296 P_Login_Id,
1297 P_Prog_AppId,
1298 P_Prog_Id,
1299 P_Request_id,
1300 cur.transaction_id,
1301 x_msg_data,
1302 'TRADE_ITEM_DESCRIPTOR',
1303 'MTL_SYSTEM_ITEMS_INTERFACE',
1304 'INV_IOI_ERR_GTID_UPDATE_01',--bug 11894684, replace INV_IOI_ERR
1305 X_Err_Text);
1306 l_error_status := 1;
1307 END IF;
1308 END IF;
1309 END IF;
1310 END IF;
1311 END IF; --R12 C validations ONLY at the end
1312
1313 IF l_error_status = 1 THEN
1314 --Set process flag since failed in LC validation
1315 UPDATE mtl_system_items_interface
1316 SET process_flag = 3
1317 WHERE rowid = cur.rowid;
1318 END IF;
1319
1320 END LOOP; -- End of c_get_master_items
1321
1322 FOR cur IN c_get_child_items LOOP
1323
1324 l_error_status := 0;
1325 l_error_logged := 0;
1326
1327 IF cur.lifecycle_id IS NOT NULL THEN
1328 BEGIN
1329 Validate_Child_Items(
1330 P_Org_Id => cur.organization_id
1331 ,P_Item_Id => cur.inventory_item_id
1332 ,P_Lifecycle_Id => cur.lifecycle_id
1333 ,P_Phase_Id => cur.current_phase_id
1334 ,P_Catalog_Group_Id => cur.item_catalog_group_id
1335 ,P_Status_Code => cur.inventory_item_status_code
1336 ,P_Transaction_Type => cur.transaction_type
1337 ,P_Rowid => cur.rowid
1338 ,X_Error_Column => X_Error_Column
1339 ,X_Error_Code => X_Error_Code);
1340 EXCEPTION
1341 WHEN LOGGING_ERR THEN
1342 l_error_logged :=
1343 INVPUOPI.mtl_log_interface_err(
1344 cur.organization_id,
1345 P_User_Id,
1346 P_Login_Id,
1347 P_Prog_AppId,
1348 P_Prog_Id,
1349 P_Request_id,
1350 cur.transaction_id,
1351 l_error_msg,
1352 X_Error_Column,
1353 'MTL_SYSTEM_ITEMS_INTERFACE',
1354 X_Error_Code,
1355 X_Err_Text);
1356
1357 IF l_error_logged < 0 THEN
1358 Raise LOGGING_ERR;
1359 END IF;
1360 l_error_status := 1;
1361 END;
1362 ELSE
1363 --3457443 : lifecycle and phase validation during update also.
1364 --Life Cyle is null but phase has been provided
1365 IF cur.current_phase_id IS NOT NULL THEN
1366 l_error_logged := INVPUOPI.mtl_log_interface_err(
1367 cur.organization_id,
1368 P_User_Id,
1369 P_Login_Id,
1370 P_Prog_AppId,
1371 P_Prog_Id,
1372 P_Request_id,
1373 cur.transaction_id,
1374 l_error_msg,
1375 'CURRENT_PHASE_ID',
1376 'MTL_SYSTEM_ITEMS_INTERFACE',
1377 'INV_IOI_LIFECYCLE_MANDATORY',
1378 X_Err_Text);
1379
1380 IF l_error_logged < 0 THEN
1381 Raise LOGGING_ERR;
1382 END IF;
1383 l_error_status := 1;
1384 END IF; --Phase id is not null
1385 END IF; -- Lifecycle is not null
1386
1387
1388 IF p_process_flag = 4 THEN --To prevent validations from firing during Change Policy Check
1389 /* Bug 5523228 - Added to calidate Unit wt and wt uom against Trade Item Descriptor */
1390 IF(l_error_status <> 1) THEN
1391
1392 OPEN c_get_existing_item_details(cur.inventory_item_id, cur.organization_id);
1393 FETCH c_get_existing_item_details INTO l_old_catalog_group_id,l_approval_status,
1394 l_old_unit_weight, l_old_weight_uom_code,
1395 l_old_pack_item_type,l_old_gdsn_flag
1396 ,l_old_lifecycle_id, l_old_current_phase_id; -- Bug 11768298
1397 CLOSE c_get_existing_item_details;
1398
1399 IF( (((cur.unit_weight IS NOT NULL) AND (NVL(cur.unit_weight,-1) <> NVL(l_old_unit_weight,-1)))
1400 OR((cur.weight_uom_code IS NOT NULL) AND (NVL(cur.weight_uom_code,-1) <> NVL(l_old_weight_uom_code,-1))))
1401 AND cur.transaction_type = 'UPDATE'
1402 AND cur.gdsn_outbound_enabled_flag = 'Y')
1403 THEN
1404 l_valid := EGO_GTIN_PVT.Validate_Unit_Wt_Uom(p_inventory_item_id => cur.inventory_item_id,
1405 p_org_id => cur.organization_id );
1406
1407 IF (l_valid <> FND_API.G_TRUE) THEN
1408 FND_MESSAGE.SET_NAME ( 'EGO', 'EGO_ATTR_NOT_EDITABLE');
1409 FND_MESSAGE.SET_TOKEN ('ATTR_NAME', 'UNIT WEIGHT AND WEIGHT_UOM_CODE');
1410 FND_MESSAGE.SET_TOKEN ('GTID', 'TRADE ITEM DESCRIPTOR');
1411 l_error_msg := FND_MESSAGE.GET;
1412
1413 l_error_logged := INVPUOPI.mtl_log_interface_err(
1414 cur.organization_id,
1415 P_User_Id,
1416 P_Login_Id,
1417 P_Prog_AppId,
1418 P_Prog_Id,
1419 P_Request_id,
1420 cur.transaction_id,
1421 l_error_msg,
1422 'UNIT_WEIGHT',
1423 'MTL_SYSTEM_ITEMS_INTERFACE',
1424 'INV_IOI_ERR_E_ATTR_UNEDITABLE',--bug 11894684, replace INV_IOI_ERR
1425 X_Err_Text);
1426
1427 IF l_error_logged < 0 THEN
1428 Raise LOGGING_ERR;
1429 END IF;
1430 l_error_status := 1;
1431 END IF;
1432 END IF;
1433 END IF;
1434
1435 /* Validating Style/SKU attributes for non-std items */
1436 IF cur.style_item_flag IS NOT NULL THEN
1437
1438 l_error_status := validate_style_sku ( p_row_id => cur.rowid,
1439 p_xset_id => P_Set_id,
1440 x_err_text => x_err_text);
1441 IF l_error_status <> 0 THEN
1442
1443 UPDATE mtl_system_items_interface
1444 SET process_flag = 3
1445 WHERE rowid = cur.rowid;
1446
1447 l_error_logged := INVPUOPI.mtl_log_interface_err(
1448 cur.organization_id,
1449 P_User_Id,
1450 P_Login_Id,
1451 P_Prog_AppId,
1452 P_Prog_Id,
1453 P_Request_id,
1454 cur.transaction_id,
1455 SQLERRM,
1456 'STYLE_ITEM_FLAG',
1457 'MTL_SYSTEM_ITEMS_INTERFACE',
1458 'INV_IOI_ERR_VLDATE_STYL_SKU02',--bug 11894684, replace INV_IOI_ERR
1459 x_err_text);
1460 IF l_error_logged < 0 THEN
1461 Raise LOGGING_ERR;
1462 END IF;
1463 END IF;
1464 END IF;
1465
1466 /* Bug 5389029 - Adding a condition to chk for errors so far */
1467 IF ((cur.item_catalog_group_id IS NOT NULL) AND ( l_error_status <> 1) AND (cur.transaction_type = 'UPDATE')) THEN
1468 /* Adding the ttype check and the NVL clause to the ICC change condition */
1469 IF (NVL(l_old_catalog_group_id,-1) <> NVL(cur.item_catalog_group_id,-1)) THEN
1470 /* Bug 5389029 - Passing Int Table values for Lifecycle/Phase */
1471 EGO_INV_ITEM_CATALOG_PVT.Change_Item_Catalog (
1472 P_INVENTORY_ITEM_ID => cur.inventory_item_id
1473 ,P_ORGANIZATION_ID => cur.organization_id
1474 ,P_CATALOG_GROUP_ID => l_old_catalog_group_id
1475 ,P_NEW_CATALOG_GROUP_ID => cur.item_catalog_group_id
1476 ,P_NEW_LIFECYCLE_ID => cur.lifecycle_id
1477 ,P_NEW_PHASE_ID => cur.current_phase_id
1478 ,P_NEW_ITEM_STATUS_CODE => cur.inventory_item_status_code
1479 ,P_COMMIT => FND_API.G_FALSE
1480 ,X_RETURN_STATUS => X_RETURN_STATUS
1481 ,X_MSG_COUNT => X_MSG_COUNT
1482 ,X_MSG_DATA => X_MSG_DATA );
1483
1484 /* Bug 5389029 - Passing the right arguments for clear error log */
1485 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1486 l_error_logged := INVPUOPI.mtl_log_interface_err(
1487 cur.organization_id,
1488 P_User_Id,
1489 P_Login_Id,
1490 P_Prog_AppId,
1491 P_Prog_Id,
1492 P_Request_id,
1493 cur.transaction_id,
1494 X_MSG_DATA,
1495 'ITEM_CATALOG_GROUP_ID',
1496 'MTL_SYSTEM_ITEMS_INTERFACE',
1497 'INV_IOI_ERR_CHANGE_IC_02', --bug 11894684, replace INV_IOI_ERR
1498 X_ERR_TEXT);
1499
1500 IF l_error_logged < 0 THEN
1501 Raise LOGGING_ERR;
1502 END IF;
1503 l_error_status := 1;
1504 ELSE
1505 --Bug: 5258295
1506 l_return_status := INV_EGO_REVISION_VALIDATE.mtl_catalog_group_update (
1507 p_rowid => cur.rowid
1508 ,p_process_flag => p_process_flag --Added for R12 C
1509 ,p_inventory_item_id => cur.inventory_item_id
1510 ,p_organization_id => cur.organization_id
1511 ,p_old_item_cat_grp_id => l_old_catalog_group_id
1512 ,p_new_item_cat_grp_id => cur.item_catalog_group_id
1513 ,p_approval_status => l_approval_status
1514 ,p_item_number => cur.item_number
1515 ,p_transaction_id => cur.transaction_id
1516 ,p_prog_appid => p_prog_appid
1517 ,p_prog_id => p_prog_id
1518 ,p_request_id => p_request_id
1519 ,p_xset_id => P_Set_Id --Added for R12 C
1520 ,p_user_id => p_user_id
1521 ,p_login_id => p_login_id
1522 ,x_err_text => x_err_text);
1523
1524 IF l_return_status <> 0 THEN
1525 l_error_logged := INVPUOPI.mtl_log_interface_err(
1526 cur.organization_id,
1527 P_User_Id,
1528 P_Login_Id,
1529 P_Prog_AppId,
1530 P_Prog_Id,
1531 P_Request_id,
1532 cur.transaction_id,
1533 l_error_msg,
1534 'ITEM_CATALOG_GROUP_ID',
1535 'MTL_SYSTEM_ITEMS_INTERFACE',
1536 x_err_text,
1537 x_err_text);
1538
1539 IF l_error_logged < 0 THEN
1540 Raise LOGGING_ERR;
1541 END IF;
1542 l_error_status := 1;
1543 END IF;
1544 END IF;
1545 END IF;
1546 END IF;
1547
1548 /* Pack Item Type Validations */
1549 IF ((cur.gdsn_outbound_enabled_flag IS NULL OR cur.gdsn_outbound_enabled_flag = 'N')
1550 AND(l_old_gdsn_flag = 'Y')) THEN
1551 l_error_logged := INVPUOPI.mtl_log_interface_err (
1552 Cur.organization_id,
1553 P_User_Id,
1554 P_Login_Id,
1555 P_Prog_AppId,
1556 P_Prog_Id,
1557 P_Request_id,
1558 cur.transaction_id,
1559 l_error_msg,
1560 'GDSN_OUTBOUND_ENABLED_FLAG',
1561 'MTL_SYSTEM_ITEMS_INTERFACE',
1562 'INV_GDSN_UPD_NO_INVALID',
1563 X_Err_Text);
1564 l_error_status := 1;
1565 END IF;
1566
1567 /* Section 2.5.2 Condition 2 Pack item type should be a valid value */
1568 IF ( cur.trade_item_descriptor IS NOT NULL AND
1569 ( ( cur.trade_item_descriptor <> NVL(l_old_pack_item_type,'X') AND
1570 cur.transaction_type = 'UPDATE')
1571 OR cur.transaction_type = 'CREATE' ) )
1572 THEN
1573 OPEN c_pack_item_type (cp_pack_item_type => cur.trade_item_descriptor);
1574 FETCH c_pack_item_type INTO l_valid_pack_type;
1575 CLOSE c_pack_item_type;
1576
1577 IF l_valid_pack_type <> 1 THEN
1578 l_error_logged := INVPUOPI.mtl_log_interface_err (
1579 Cur.organization_id,
1580 P_User_Id,
1581 P_Login_Id,
1582 P_Prog_AppId,
1583 P_Prog_Id,
1584 P_Request_id,
1585 cur.transaction_id,
1586 l_error_msg,
1587 'TRADE_ITEM_DESCRIPTOR',
1588 'MTL_SYSTEM_ITEMS_INTERFACE',
1589 'INV_INVALID_PACK_TYPE',
1590 X_Err_Text);
1591 l_error_status := 1;
1592 END IF;
1593
1594 IF cur.trade_item_descriptor = 'BASE_UNIT_OR_EACH' THEN
1595 OPEN c_base_uom(cp_primary_uom_code => cur.primary_uom_code);
1596 FETCH c_base_uom INTO l_is_primary_uom_base;
1597 CLOSE c_base_uom;
1598
1599 IF (l_is_primary_uom_base <> 'Y') THEN
1600 FND_MESSAGE.Set_Name('EGO', 'EGO_GTID_CANNOT_BE_BASE');
1601 FND_MESSAGE.Set_Token('ATTR_NAME', l_gtid_disp_name);
1602 l_err_text := FND_MESSAGE.GET;
1603 l_error_logged := INVPUOPI.mtl_log_interface_err (
1604 Cur.organization_id,
1605 P_User_Id,
1606 P_Login_Id,
1607 P_Prog_AppId,
1608 P_Prog_Id,
1609 P_Request_id,
1610 cur.transaction_id,
1611 l_err_text,
1612 'TRADE_ITEM_DESCRIPTOR',
1613 'MTL_SYSTEM_ITEMS_INTERFACE',
1614 'INV_IOI_ERR_GTID_NOT_BE_BS_02',--bug 11894684, replace INV_IOI_ERR
1615 X_Err_Text);
1616 l_error_status := 1;
1617 END IF;
1618 END IF;
1619
1620 IF (l_error_status <> 1 AND cur.transaction_type = 'UPDATE')
1621 THEN
1622 /* API call to resolve item in pack or not */
1623 BOM_IMPLODER_PUB.IMPLODER_USEREXIT(
1624 SEQUENCE_ID => null,
1625 ENG_MFG_FLAG => 2,
1626 ORG_ID => cur.organization_id,
1627 IMPL_FLAG => 2,
1628 DISPLAY_OPTION => 1,
1629 LEVELS_TO_IMPLODE => 60,
1630 OBJ_NAME => 'EGO_ITEM',
1631 PK1_VALUE => cur.inventory_item_id,
1632 PK2_VALUE => cur.organization_id,
1633 PK3_VALUE => null,
1634 PK4_VALUE => null,
1635 PK5_VALUE => null,
1636 IMPL_DATE => to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') ,
1637 UNIT_NUMBER_FROM => 'N',
1638 UNIT_NUMBER_TO => 'Y',
1639 ERR_MSG => x_err_text,
1640 ERR_CODE => l_error_logged,
1641 ORGANIZATION_OPTION => 1,
1642 ORGANIZATION_HIERARCHY => null,
1643 SERIAL_NUMBER_FROM => null,
1644 SERIAL_NUMBER_TO => null,
1645 STRUCT_TYPE => 'Packaging Hierarchy',
1646 PREFERRED_ONLY => 2 ,
1647 USED_IN_STRUCTURE => l_item_in_pack);
1648
1649 IF l_item_in_pack = FND_API.G_TRUE THEN
1650 l_error_logged := INVPUOPI.mtl_log_interface_err (
1651 Cur.organization_id,
1652 P_User_Id,
1653 P_Login_Id,
1654 P_Prog_AppId,
1655 P_Prog_Id,
1656 P_Request_id,
1657 cur.transaction_id,
1658 l_error_msg,
1659 'TRADE_ITEM_DESCRIPTOR',
1660 'MTL_SYSTEM_ITEMS_INTERFACE',
1661 'INV_ITEM_IN_PACK',
1662 X_Err_Text);
1663 l_error_status := 1;
1664 END IF;
1665
1666 IF l_error_status <> 1 THEN
1667 /* For update of Trade Item Descriptor GDSN Post Processing is triggered */
1668 EGO_GTIN_PVT.process_gtid_update ( p_inventory_item_id => cur.inventory_item_id
1669 ,p_organization_id => cur.organization_id
1670 ,p_trade_item_desc => cur.trade_item_descriptor
1671 ,x_return_status => x_return_status
1672 ,x_msg_count => x_msg_count
1673 ,x_msg_data => x_msg_data);
1674
1675 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1676 l_error_logged := INVPUOPI.mtl_log_interface_err (
1677 Cur.organization_id,
1678 P_User_Id,
1679 P_Login_Id,
1680 P_Prog_AppId,
1681 P_Prog_Id,
1682 P_Request_id,
1683 cur.transaction_id,
1684 x_msg_data,
1685 'TRADE_ITEM_DESCRIPTOR',
1686 'MTL_SYSTEM_ITEMS_INTERFACE',
1687 'INV_IOI_ERR_GTID_UPDATE_02',--bug 11894684, replace INV_IOI_ERR
1688 X_Err_Text);
1689 l_error_status := 1;
1690 END IF;
1691 END IF;
1692 END IF;
1693 END IF;
1694 END IF; --process flag 4
1695
1696 IF l_error_status = 1 THEN
1697 --Set process flag since failed in LC validation
1698 UPDATE mtl_system_items_interface
1699 SET process_flag = 3
1700 WHERE rowid = cur.rowid;
1701 END IF;
1702
1703 END LOOP; -- End of c_get_child_items
1704
1705 END IF;
1706 --Support of user attributes through template
1707 INV_EGO_REVISION_VALIDATE.Insert_Grants_And_UserAttr(P_Set_id);
1708
1709 END IF; --Ego exists
1710
1711 RETURN (0);
1712
1713 EXCEPTION
1714 WHEN LOGGING_ERR then
1715 RETURN(l_error_logged);
1716 WHEN OTHERS THEN
1717 X_Err_Text := SUBSTR('INV_EGO_REVISION_VALIDATE.validate_revision_lifecycle' || SQLERRM , 1,240);
1718 RETURN (SQLCODE);
1719 END validate_items_lifecycle;
1720 --End 2777118:Item Lifecycle and Phase validations
1721
1722 --Start : Check for data security and user priv.
1723 FUNCTION check_data_security(
1724 P_Function IN VARCHAR2
1725 ,P_Object_Name IN VARCHAR2
1726 ,P_Instance_PK1_Value IN VARCHAR2
1727 ,P_Instance_PK2_Value IN VARCHAR2 DEFAULT NULL
1728 ,P_Instance_PK3_Value IN VARCHAR2 DEFAULT NULL
1729 ,P_Instance_PK4_Value IN VARCHAR2 DEFAULT NULL
1730 ,P_Instance_PK5_Value IN VARCHAR2 DEFAULT NULL
1731 ,P_User_Id IN NUMBER)
1732 RETURN VARCHAR2 IS
1733
1734 --4932512 : Replacing ego_people with ego_user
1735 CURSOR c_get_party_id(cp_user_id NUMBER) IS
1736 SELECT TO_CHAR(party_id)
1737 FROM ego_user_v
1738 WHERE user_id = cp_user_id;
1739
1740 l_has_privilege VARCHAR2(1) := 'T';
1741 l_party_name fnd_grants.grantee_key%TYPE;
1742 l_user_id fnd_user.user_id%TYPE := P_User_Id;
1743
1744 BEGIN
1745
1746 IF INV_EGO_REVISION_VALIDATE.Get_Process_Control ='EGO_ITEM_BULKLOAD' THEN
1747
1748 IF l_user_id IS NULL OR l_user_id = -1 THEN
1749 l_user_id := FND_GLOBAL.user_id;
1750 END IF;
1751
1752 OPEN c_get_party_id(l_user_id);
1753 FETCH c_get_party_id INTO l_party_name;
1754 CLOSE c_get_party_id;
1755
1756 l_party_name := 'HZ_PARTY:'||l_party_name;
1757
1758 l_has_privilege := EGO_DATA_SECURITY.check_function(
1759 p_api_version => 1.0
1760 ,p_function => P_Function
1761 ,p_object_name => P_Object_Name
1762 ,p_instance_pk1_value => P_Instance_PK1_Value
1763 ,p_instance_pk2_value => P_Instance_PK2_Value
1764 ,p_instance_pk3_value => P_Instance_PK3_Value
1765 ,p_instance_pk4_value => P_Instance_PK4_Value
1766 ,p_instance_pk5_value => P_Instance_PK5_Value
1767 ,p_user_name => l_party_name);
1768
1769 l_has_privilege := NVL(l_has_privilege,'F');
1770
1771 END IF;
1772
1773 RETURN(l_has_privilege);
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 l_has_privilege := 'F';
1778 IF c_get_party_id%ISOPEN THEN
1779 CLOSE c_get_party_id;
1780 END IF;
1781 RETURN(l_has_privilege);
1782 END check_data_security;
1783
1784 /* Bug: 5238510
1785 Added process flag parameter with a default value of 2
1786 If the caller wants to pick rows other than thie process
1787 flag value they can pass that value explicitly. The behavior
1788 remains the same otherwise.
1789 */
1790 FUNCTION validate_item_user_privileges(
1791 P_Org_Id IN NUMBER
1792 ,P_All_Org IN NUMBER DEFAULT 2
1793 ,P_Prog_AppId IN NUMBER DEFAULT -1
1794 ,P_Prog_Id IN NUMBER DEFAULT -1
1795 ,P_Request_Id IN NUMBER DEFAULT -1
1796 ,P_User_Id IN NUMBER DEFAULT -1
1797 ,P_Login_Id IN NUMBER DEFAULT -1
1798 ,P_Set_id IN NUMBER DEFAULT -999
1799 ,X_Err_Text IN OUT NOCOPY VARCHAR2
1800 ,P_Process_flag IN NUMBER DEFAULT 2 )
1801 RETURN INTEGER IS
1802
1803 CURSOR c_get_items IS
1804 SELECT msii.rowid,
1805 msii.organization_id,
1806 msii.inventory_item_id,
1807 msii.item_catalog_group_Id,
1808 msii.inventory_item_status_code,
1809 msii.lifecycle_id,
1810 msii.current_phase_id,
1811 msii.transaction_id,
1812 msii.transaction_type,
1813 msii.item_number, --5522789
1814 mp.master_organization_id,
1815 msii.created_by
1816 FROM mtl_system_items_interface msii,
1817 mtl_parameters mp
1818 WHERE (msii.organization_id = P_Org_Id OR P_All_Org = 1)
1819 AND msii.set_process_id = P_Set_id
1820 AND msii.process_flag = P_Process_flag
1821 AND msii.organization_id = mp.organization_id--Bug 5238510
1822 FOR UPDATE OF process_flag;
1823
1824 CURSOR c_get_item_rec(cp_org_id NUMBER
1825 ,cp_item_id NUMBER)
1826 IS
1827 SELECT item_catalog_group_id, approval_status,
1828 inventory_item_status_code,lifecycle_id,
1829 current_phase_id
1830 FROM mtl_system_items_b
1831 WHERE organization_id = cp_org_id
1832 AND inventory_item_id = cp_item_id;
1833
1834 l_has_privilege VARCHAR2(1) := 'F';
1835 l_log_error BOOLEAN := FALSE;
1836 l_error_status NUMBER := 0;
1837 l_error_logged NUMBER := 0;
1838 l_error_msg VARCHAR2(70);
1839 l_error_column VARCHAR2(50);
1840 l_error_code VARCHAR2(70);
1841 l_err_text VARCHAR2(240);
1842 l_item_catalog NUMBER := 0;
1843 l_has_access VARCHAR2(1):= 'F';
1844 l_approval_status mtl_system_items_b.approval_status%TYPE;
1845 l_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
1846 l_lifecycle_id mtl_system_items.lifecycle_id%TYPE;
1847 l_current_phase_id mtl_system_items.current_phase_id%TYPE;
1848 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
1849 BEGIN
1850
1851 IF INV_EGO_REVISION_VALIDATE.Get_Process_Control ='EGO_ITEM_BULKLOAD' THEN
1852
1853 FOR cur in c_get_items LOOP
1854
1855 l_has_privilege := 'F';
1856 l_log_error := FALSE;
1857 l_has_access := 'F';
1858
1859 IF cur.transaction_type ='CREATE' THEN
1860
1861 -- Bug 6126225: Do not check User ICC privilege for Item Organization Assignment
1862 IF (cur.item_catalog_group_Id IS NOT NULL AND cur.master_organization_id = cur.organization_id)
1863 THEN
1864 l_has_privilege := check_data_security(
1865 p_function => 'EGO_CREATE_ITEM_OF_GROUP'
1866 ,p_object_name => 'EGO_CATALOG_GROUP'
1867 ,p_instance_pk1_value => cur.item_catalog_group_Id
1868 ,P_User_Id => P_User_Id);
1869 IF l_has_privilege <> 'T' THEN
1870 l_log_error := TRUE;
1871 l_error_column := 'ITEM_CATALOG_GROUP_ID';
1872 l_error_code := 'INV_IOI_NOT_CATALOG_USER';
1873 END IF;
1874 END IF;
1875
1876 IF cur.master_organization_id <> cur.organization_id THEN
1877 --Start 3342860: Check for EGO_EDIT_ITEM for org-item assignment.
1878 --3429418 should check for EGO_EDIT_ITEM_ORG_ASSIGN instead of EGO_EDIT_ITEM.
1879 --Item-Org assignment not allowed for unapproved items.
1880 OPEN c_get_item_rec(cur.master_organization_id, cur.inventory_item_id);
1881 FETCH c_get_item_rec INTO l_item_catalog, l_approval_status,
1882 l_inventory_item_status_code,l_lifecycle_id,
1883 l_current_phase_id;
1884 CLOSE c_get_item_rec;
1885
1886 l_has_access := Check_Org_Access(
1887 p_org_id => cur.organization_id);
1888 IF l_has_access <> 'T' THEN
1889 l_log_error := TRUE;
1890 l_error_column := 'INVENTORY_ITEM_ID';
1891 l_error_code := 'INV_IOI_ITEM_ORGAS_ACCESS_PRIV';
1892 END IF;
1893
1894 IF ( cur.created_by <> -99 ) THEN
1895 l_has_privilege := check_data_security(
1896 p_function => 'EGO_EDIT_ITEM_ORG_ASSIGN'
1897 ,p_object_name => 'EGO_ITEM'
1898 ,p_instance_pk1_value => cur.inventory_item_id
1899 ,p_instance_pk2_value => cur.master_organization_id
1900 ,P_User_Id => P_User_Id);
1901 IF l_has_privilege <> 'T' THEN
1902 l_log_error := TRUE;
1903 l_error_column := 'INVENTORY_ITEM_ID';
1904 l_error_code := 'INV_IOI_ITEM_ORGASSIGN_PRIV';
1905 END IF;
1906 ELSE
1907 IF l_inv_debug_level IN(101, 102) THEN
1908 INVPUTLI.info('INVEGRVB.Secutiry skip for:' || cur.Inventory_Item_ID || '-' || cur.organization_id);
1909 END IF;
1910 END IF;
1911
1912 IF NOT l_log_error AND NVL(l_approval_status,'A') <> 'A' THEN
1913 l_log_error := TRUE;
1914 l_error_column := 'INVENTORY_ITEM_ID';
1915 l_error_code := 'INV_IOI_UNAPPROVED_ITEM_ORG';
1916 END IF;
1917
1918 END IF;
1919 --End 3342860: Check for EGO_EDIT_ITEM for org-item assignment.
1920
1921 ELSIF cur.transaction_type ='UPDATE' THEN
1922 -- Bug 10243450 : Created_By had null value for some excel update cases and would fail this check causing security to be skipped
1923
1924 IF ((nvl(cur.created_by,FND_GLOBAL.user_id)) <> -99 ) THEN
1925 l_has_privilege := check_data_security(
1926 p_function => 'EGO_EDIT_ITEM'
1927 ,p_object_name => 'EGO_ITEM'
1928 ,p_instance_pk1_value => cur.inventory_item_id
1929 ,p_instance_pk2_value => cur.organization_id
1930 ,P_User_Id => P_User_Id);
1931
1932 IF l_has_privilege <> 'T' THEN
1933 l_log_error := TRUE;
1934 l_error_column := 'INVENTORY_ITEM_ID';
1935 -- 5522789
1936 -- l_error_code := 'INV_IOI_ITEM_UPDATE_PRIV';
1937 FND_MESSAGE.SET_NAME ( 'INV', 'INV_IOI_ITEM_UPDATE_PRIV');
1938 FND_MESSAGE.SET_TOKEN ('VALUE', cur.item_number);
1939 l_error_msg := FND_MESSAGE.GET;
1940 l_error_code := 'INV_IOI_ERR_ITEM_UPDATE_PRIV';--bug 11894684, replace INV_IOI_ERR
1941 END IF;
1942 ELSE
1943 IF l_inv_debug_level IN(101, 102) THEN
1944 INVPUTLI.info('INVEGRVB.Secutiry skip for:' || cur.Inventory_Item_ID || '-' || cur.organization_id);
1945 END IF;
1946 END IF;
1947
1948 IF NOT l_log_error THEN
1949 OPEN c_get_item_rec(cp_org_id => cur.organization_id
1950 ,cp_item_id => cur.inventory_item_id);
1951 FETCH c_get_item_rec INTO l_item_catalog, l_approval_status,
1952 l_inventory_item_status_code,l_lifecycle_id,
1953 l_current_phase_id;
1954 CLOSE c_get_item_rec;
1955
1956 --Bug: 5079137 Check EGO_EDIT_ITEM_STATUS_PRIVILEGE for update of Status,ICC,Lifecycle and Phase
1957 IF ( NVL(cur.item_catalog_group_id,0) <> NVL(l_item_catalog,0) OR
1958 cur.inventory_item_status_code <> l_inventory_item_status_code OR
1959 NVL(cur.lifecycle_id,0) <> NVL(l_lifecycle_id,0) OR
1960 NVL(cur.current_phase_id,0) <> NVL(l_current_phase_id,0) ) THEN
1961
1962 l_has_privilege := check_data_security(
1963 p_function => 'EGO_EDIT_ITEM_STATUS'
1964 ,p_object_name => 'EGO_ITEM'
1965 ,p_instance_pk1_value => cur.inventory_item_id
1966 ,p_instance_pk2_value => cur.organization_id
1967 ,P_User_Id => P_User_Id);
1968
1969 IF l_has_privilege <> 'T' THEN
1970 l_log_error := TRUE;
1971 l_error_column := 'INVENTORY_ITEM_ID';
1972 l_error_code := 'INV_IOI_STATUS_UPDATE_PRIV';
1973 END IF;
1974 END IF;
1975 END IF;
1976
1977 --Start : Catalog update changes
1978 IF NOT l_log_error THEN
1979
1980 /*Bug: 5258295
1981 --Bug:3491746 Added catalog group validation PLM
1982 IF NVL(l_approval_status,'A') <> 'A' AND NVL(l_item_catalog,-1) <> NVL(cur.item_catalog_group_Id,-1) THEN
1983 l_log_error := TRUE;
1984 l_error_column := 'ITEM_CATALOG_GROUP_ID';
1985 l_error_code := 'INV_IOI_UNAPPROVED_ITEM_CTLG';
1986 --Bug:3491746 Added catalog group validation PLM
1987 */
1988 IF NVL(l_item_catalog,-1) <> cur.item_catalog_group_Id AND cur.item_catalog_group_Id IS NOT NULL THEN
1989 l_has_privilege := check_data_security(
1990 p_function => 'EGO_CREATE_ITEM_OF_GROUP'
1991 ,p_object_name => 'EGO_CATALOG_GROUP'
1992 ,p_instance_pk1_value => cur.item_catalog_group_Id
1993 ,P_User_Id => P_User_Id);
1994 IF l_has_privilege <> 'T' THEN
1995 l_log_error := TRUE;
1996 l_error_column := 'ITEM_CATALOG_GROUP_ID';
1997 l_error_code := 'INV_IOI_NOT_CATALOG_USER';
1998 END IF;
1999 END IF;
2000 END IF;
2001 --End : Catalog update changes
2002 --Uncommented for 5260528
2003 --Bug:3491746 Added catalog group validation PLM
2004 IF NOT l_log_error THEN
2005 --Bug: 4020501 Claused the call to INVIDIT3.Is_Catalog_Group_Valid
2006 IF cur.item_catalog_group_id IS NOT NULL
2007 AND NVL(l_item_catalog,0) <> cur.item_catalog_group_id THEN
2008 l_error_msg := INVIDIT3.Is_Catalog_Group_Valid(
2009 old_catalog_group_id => l_item_catalog,
2010 new_catalog_group_id => cur.item_catalog_group_id,
2011 item_id => cur.inventory_item_id);
2012 IF l_error_msg IS NOT NULL THEN
2013 l_log_error := TRUE;
2014 l_error_column := 'ITEM_CATALOG_GROUP_ID';
2015 l_error_code := l_error_msg;
2016 END IF;
2017 END IF;
2018 END IF;
2019 --Bug:3491746 Added catalog group validation PLM Ended
2020 END IF;
2021
2022 IF l_log_error THEN
2023 l_error_logged := INVPUOPI.mtl_log_interface_err(
2024 cur.organization_id,
2025 P_User_Id,
2026 P_Login_Id,
2027 P_Prog_AppId,
2028 P_Prog_Id,
2029 P_Request_id,
2030 cur.transaction_id,
2031 l_error_msg,
2032 l_error_column,
2033 'MTL_SYSTEM_ITEMS_INTERFACE',
2034 l_error_code,
2035 l_err_text);
2036 IF l_error_logged < 0 THEN
2037 Raise LOGGING_ERR;
2038 END IF;
2039
2040 UPDATE mtl_system_items_interface
2041 SET process_flag = 3
2042 WHERE rowid = cur.rowid;
2043
2044 END IF;
2045
2046 END LOOP;
2047
2048 END IF; -- IF security_enabled
2049
2050 RETURN (0);
2051
2052 EXCEPTION
2053 WHEN LOGGING_ERR then
2054 RETURN(l_error_logged);
2055 WHEN OTHERS THEN
2056 X_Err_Text := SUBSTR('INV_EGO_REVISION_VALIDATE.validate_revision_lifecycle' || SQLERRM , 1,240);
2057 RETURN (SQLCODE);
2058 END validate_item_user_privileges;
2059 --End : Check for data security and user priv.
2060
2061 -- ============================================================================
2062 -- API Name: Insert_Grants_And_UserAttr
2063 --
2064 -- IN: P_Set_id
2065 -- Bug: 3033702 Moved this code from INVPPROB.pls
2066 -- ============================================================================
2067 PROCEDURE Insert_Grants_And_UserAttr (P_Set_id IN NUMBER) IS
2068
2069 CURSOR c_get_processed_records (cp_set_process_id NUMBER) IS
2070 SELECT interface.inventory_item_id,
2071 interface.item_catalog_group_id,
2072 interface.organization_id,
2073 interface.template_id,
2074 interface.transaction_id,
2075 interface.transaction_type,
2076 interface.rowid
2077 FROM mtl_system_items_interface interface
2078 WHERE interface.set_process_id = cp_set_process_id
2079 AND interface.process_flag = 4
2080 AND interface.transaction_type = 'CREATE'
2081 --4676088AND interface.transaction_type IN ('CREATE','UPDATE')
2082 FOR UPDATE OF process_flag;
2083
2084 --4676088
2085 /*
2086 CURSOR c_parent_catalogs(cp_catalog_group_id NUMBER) IS
2087 SELECT ITEM_CATALOG_GROUP_ID
2088 ,PARENT_CATALOG_GROUP_ID
2089 FROM MTL_ITEM_CATALOG_GROUPS_B
2090 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
2091 START WITH ITEM_CATALOG_GROUP_ID = cp_catalog_group_id;
2092 */
2093
2094 l_party_id EGO_PEOPLE_V.PERSON_ID%TYPE;
2095 l_grant_guid fnd_grants.grant_guid%TYPE;
2096 l_error_code NUMBER;
2097 l_return_status VARCHAR2(1);
2098 l_msg_count NUMBER;
2099 l_msg_data VARCHAR2(2000);
2100 l_err_text VARCHAR2(240);
2101 l_error_logged NUMBER := 0;
2102 l_parent_catalog VARCHAR2(150):= NULL;
2103 --4676088
2104 --l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2105 --l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2106
2107 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
2108
2109 /* vggarg bug 9039870 added to check for duplicate item roles when Item is created with NIR */
2110 CURSOR c_get_overlap_grantid
2111 (cp_inv_item_id IN NUMBER
2112 ,cp_organization_id IN NUMBER
2113 ,cp_menu_name IN VARCHAR2
2114 ,cp_object_name IN VARCHAR2
2115 ,cp_user_party_id_char IN VARCHAR2
2116 ,cp_group_party_id_char IN VARCHAR2
2117 ,cp_company_party_id_char IN VARCHAR2
2118 ,cp_global_party_id_char IN VARCHAR2
2119 ,cp_start_date IN DATE
2120 ,cp_end_date IN DATE
2121 ) IS
2122 SELECT grant_guid
2123 FROM fnd_grants grants,fnd_menus menus, fnd_objects objects
2124 WHERE grants.object_id = objects.object_id
2125 AND grants.menu_id = menus.menu_id
2126 AND objects.obj_name = cp_object_name
2127 AND menus.menu_name = cp_menu_name
2128 AND grants.instance_type = 'INSTANCE'
2129 AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
2130 AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
2131 AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
2132 (grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
2133 (grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
2134 (grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
2135 )
2136 AND start_date <= NVL(cp_end_date, start_date)
2137 AND NVL(end_date,cp_start_date) >= cp_start_date;
2138
2139 /* vggarg bug 9039870 end added to check for duplicate item roles when Item is created with NIR */
2140
2141
2142 BEGIN
2143
2144 -- For Internal Users , Customer_Id may not getting populated in FND_USER
2145 -- Hence checking USER_ID from ego_people_v which always return all registered
2146 -- Users (Customer, Internal and Vendor)
2147 -- Bug Fix : 3048453
2148 -- Calling bulk seeding procedure
2149 EGO_ITEM_PUB.Seed_Item_Long_Desc_In_Bulk(
2150 p_set_process_id => P_Set_id
2151 ,x_return_status => l_return_status
2152 ,x_msg_data => l_msg_data
2153 );
2154
2155 --4932512 : Replacing ego_people with ego_user
2156 SELECT party_id INTO l_party_id
2157 FROM EGO_USER_V
2158 WHERE USER_ID = FND_GLOBAL.User_ID;
2159
2160 FOR cur in c_get_processed_records(P_Set_id) LOOP
2161 IF cur.transaction_type ='CREATE' THEN
2162 IF l_inv_debug_level IN(101, 102) THEN
2163 INVPUTLI.info('INVEGRVB.Insert_Grants_And_UserAttr: inserting i grants for an Item'||cur.Inventory_Item_ID);
2164 END IF;
2165 /* vggarg bug 9039870 added to check for duplicate item roles when Item is created with NIR */
2166 OPEN c_get_overlap_grantid
2167 (cp_inv_item_id => cur.Inventory_Item_ID
2168 ,cp_organization_id => cur.Organization_ID
2169 ,cp_menu_name => 'EGO_ITEM_OWNER'
2170 ,cp_object_name => 'EGO_ITEM'
2171 ,cp_user_party_id_char => 'HZ_PARTY:'||TO_CHAR(l_party_id)
2172 ,cp_group_party_id_char => 'HZ_PARTY:'||TO_CHAR(l_party_id)
2173 ,cp_company_party_id_char => 'HZ_PARTY:'||TO_CHAR(l_party_id)
2174 ,cp_global_party_id_char => 'HZ_PARTY:'||TO_CHAR(l_party_id)
2175 ,cp_start_date => SYSDATE
2176 ,cp_end_date => NULL);
2177
2178 FETCH c_get_overlap_grantid INTO l_grant_guid;
2179 IF c_get_overlap_grantid%NOTFOUND THEN
2180 /* vggarg bug 9039870 end added to check for duplicate item roles when Item is created with NIR */
2181
2182 FND_GRANTS_PKG.GRANT_FUNCTION(
2183 P_API_VERSION => 1.0
2184 ,P_MENU_NAME => 'EGO_ITEM_OWNER'
2185 ,P_OBJECT_NAME => 'EGO_ITEM'
2186 ,P_INSTANCE_TYPE => 'INSTANCE'
2187 ,P_INSTANCE_PK1_VALUE => cur.Inventory_Item_ID
2188 ,P_INSTANCE_PK2_VALUE => cur.Organization_ID
2189 ,P_GRANTEE_KEY => 'HZ_PARTY:'||TO_CHAR(l_party_id)
2190 ,P_START_DATE => SYSDATE
2191 ,P_END_DATE => NULL
2192 ,X_GRANT_GUID => l_grant_Guid
2193 ,X_SUCCESS => l_return_status
2194 ,X_ERRORCODE => l_msg_count);
2195
2196 IF l_inv_debug_level IN(101, 102) THEN
2197 INVPUTLI.info('INVEGRVB.Insert_Grants_And_UserAttr: inserting Long description Userdefined Attribute.');
2198 END IF;
2199 /* vggarg bug 9039870 start added to check for duplicate item roles when Item is created with NIR */
2200 END IF;
2201 /* vggarg bug 9039870 end added to check for duplicate item roles when Item is created with NIR */
2202 CLOSE c_get_overlap_grantid; --close cursor, for bug 9780818
2203 END IF;
2204
2205 /* Start:4676088: Template - User defined attributes will done through EGO_ITEM_USER_ATTRS_CP_PUB
2206 --Start : Support of user attributes through template.
2207 IF cur.template_id IS NOT NULL
2208 AND cur.template_id <> -1
2209 AND cur.item_catalog_group_id IS NOT NULL THEN
2210
2211 -- As documented in EGOCIUAB.pls
2212 -- We build a list of all parent catalog groups, as long as the --
2213 -- list is less than 151 characters long (the longest we can fit --
2214 -- into the EGO_COL_NAME_VALUE_PAIR_OBJ is 150 chars); if the --
2215 -- list is too long to fully copy, we can only hope that the --
2216 -- portion we copied will contain all the information we need. --
2217
2218 l_parent_catalog := NULL;
2219
2220 BEGIN
2221 FOR parent_cur IN c_parent_catalogs(cur.item_catalog_group_id) LOOP
2222 IF parent_cur.parent_catalog_group_id IS NOT NULL THEN
2223 IF l_parent_catalog IS NULL THEN
2224 l_parent_catalog := parent_cur.parent_catalog_group_id;
2225 ELSE
2226 l_parent_catalog := l_parent_catalog||','||parent_cur.parent_catalog_group_id;
2227 END IF;
2228 END IF;
2229 END LOOP;
2230 EXCEPTION
2231 --Only exception which will occur is when concatenated parent_catalog_groups_id
2232 --lenght exceeds 150,which is max VALUE_PAIR_OBJ can hold. This is good enough
2233 --for the program, nothing needs to be done in the exception, let things proceed.
2234 WHEN OTHERS THEN
2235 NULL;
2236 END;
2237
2238 IF l_inv_debug_level IN(101, 102) THEN
2239 INVPUTLI.info('EGO_USER_ATTRS_DATA_PVT.Perform_DML_From_Template: Attaching user defined attribs through template');
2240 END IF;
2241 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2242 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID',cur.INVENTORY_ITEM_ID)
2243 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID',cur.ORGANIZATION_ID));
2244 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2245 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', cur.ITEM_CATALOG_GROUP_ID)
2246 ,EGO_COL_NAME_VALUE_PAIR_OBJ('RELATED_CLASS_CODE_LIST_1',l_parent_catalog));
2247 -- achampan, bug 3781216: new API for applying template user attrs
2248 EGO_ITEM_PUB.Apply_Templ_User_Attrs_To_Item
2249 (p_api_version => 1.0
2250 ,p_mode => cur.transaction_type
2251 ,p_item_id => cur.inventory_item_id
2252 ,p_organization_id => cur.organization_id
2253 ,p_template_id => ABS(cur.template_id)
2254 ,p_object_name => 'EGO_ITEM'
2255 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
2256 ,x_return_status => l_return_status
2257 ,x_errorcode => l_error_code
2258 ,x_msg_count => l_msg_count
2259 ,x_msg_data => l_msg_data);
2260
2261 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2262
2263 UPDATE mtl_system_items_interface
2264 SET process_flag = 3
2265 WHERE rowid = cur.rowid;
2266 l_error_logged := INVPUOPI.mtl_log_interface_err(
2267 cur.organization_id,
2268 FND_GLOBAL.USER_ID,
2269 FND_GLOBAL.LOGIN_ID,
2270 FND_GLOBAL.PROG_APPL_ID,
2271 FND_GLOBAL.CONC_PROGRAM_ID,
2272 FND_GLOBAL.CONC_REQUEST_ID,
2273 cur.transaction_id,
2274 l_msg_data,
2275 'TEMPLATE_ID',
2276 'MTL_SYSTEM_ITEMS_INTERFACE',
2277 'INV_IOI_ERR_APLTMPLUDA2ITMS',--bug 11894684, replace INV_IOI_ERR
2278 l_err_text);
2279 IF l_error_logged < 0 THEN
2280 Raise LOGGING_ERR;
2281 END IF;
2282 END IF;
2283
2284 END IF;
2285 END 4676088: Template - User defined attributes will done through EGO_ITEM_USER_ATTRS_CP_PUB
2286 --End : Support of user attributes through template.*/
2287
2288 END LOOP ;
2289 EXCEPTION
2290 --Bug: 3155733: Added exception
2291 --Bug: 3685994: Added when no_data_found exception - Anmurali
2292 WHEN no_data_found THEN
2293 --4759137 : Grants error not be logged for through INV_ITEM_GRP or INV IOI
2294 IF (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'PLM_UI:Y') <> 0 ) OR
2295 (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'ENG_CALL:Y') <> 0 )
2296 THEN
2297 FND_MESSAGE.SET_NAME ( 'INV', 'INV_INVALID_USER');
2298 FND_MESSAGE.SET_TOKEN ('USER_ID', FND_GLOBAL.User_Id);
2299 l_msg_data := FND_MESSAGE.GET;
2300 For exc IN c_get_processed_records (P_Set_Id)
2301 LOOP
2302 UPDATE mtl_system_items_interface
2303 SET process_flag = 3
2304 WHERE rowid = exc.rowid;
2305 l_error_logged := INVPUOPI.mtl_log_interface_err(
2306 exc.organization_id,
2307 FND_GLOBAL.USER_ID,
2308 FND_GLOBAL.LOGIN_ID,
2309 FND_GLOBAL.PROG_APPL_ID,
2310 FND_GLOBAL.CONC_PROGRAM_ID,
2311 FND_GLOBAL.CONC_REQUEST_ID,
2312 exc.transaction_id,
2313 l_msg_data,
2314 'LOGIN_USER_ID',
2315 'MTL_SYSTEM_ITEMS_INTERFACE',
2316 'INV_IOI_ERR_INVALID_USER',--bug 11894684, replace INV_IOI_ERR
2317 l_err_text);
2318 IF l_error_logged < 0 THEN
2319 Raise LOGGING_ERR;
2320 END IF;
2321 END LOOP;
2322 END IF;
2323 WHEN OTHERS THEN
2324 NULL;
2325 END Insert_Grants_And_UserAttr;
2326
2327 -- ============================================================================
2328 -- API Name : phase_change_policy
2329 -- Description : This procedure will be called from IOI (INVPVALB.pls)
2330 -- Stuffed version will return 'ALLOWED' through l_Policy_Code.
2331 -- EGO_LIFECYCLE_USER_PUB.get_policy_for_phase_change will be called.
2332 -- ============================================================================
2333
2334 PROCEDURE phase_change_policy(P_ORGANIZATION_ID IN NUMBER
2335 ,P_INVENTORY_ITEM_ID IN NUMBER
2336 ,P_CURR_PHASE_ID IN NUMBER
2337 ,P_FUTURE_PHASE_ID IN NUMBER
2338 ,P_PHASE_CHANGE_CODE IN VARCHAR2
2339 ,P_LIFECYCLE_ID IN NUMBER
2340 ,X_POLICY_CODE OUT NOCOPY VARCHAR2
2341 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
2342 ,X_ERRORCODE OUT NOCOPY NUMBER
2343 ,X_MSG_COUNT OUT NOCOPY NUMBER
2344 ,X_MSG_DATA OUT NOCOPY VARCHAR2) IS
2345 BEGIN
2346
2347 EGO_LIFECYCLE_USER_PUB.GET_POLICY_FOR_PHASE_CHANGE
2348 (P_API_VERSION => 1.0
2349 ,P_ORGANIZATION_ID => P_ORGANIZATION_ID
2350 ,P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID
2351 ,P_CURR_PHASE_ID => P_CURR_PHASE_ID
2352 ,P_FUTURE_PHASE_ID => P_FUTURE_PHASE_ID
2353 ,P_PHASE_CHANGE_CODE => P_PHASE_CHANGE_CODE
2354 ,P_LIFECYCLE_ID => P_LIFECYCLE_ID
2355 ,X_POLICY_CODE => X_POLICY_CODE
2356 ,X_RETURN_STATUS => X_RETURN_STATUS
2357 ,X_ERRORCODE => X_ERRORCODE
2358 ,X_MSG_COUNT => X_MSG_COUNT
2359 ,X_MSG_DATA => X_MSG_DATA);
2360
2361 END phase_change_policy;
2362
2363 --Start : 2803833
2364 FUNCTION get_default_template(p_catalog_group_id IN NUMBER)
2365 RETURN NUMBER IS
2366
2367 CURSOR C_Default_Template(cp_catalog_group_id NUMBER) IS
2368 SELECT cat_temp.TEMPLATE_ID
2369 FROM EGO_CAT_GRP_TEMPLATES cat_temp,
2370 MTL_ITEM_TEMPLATES temp
2371 WHERE cat_temp.CATALOG_GROUP_ID = cp_catalog_group_id
2372 AND cat_temp.DEFAULT_FLAG = 'Y'
2373 AND cat_temp.TEMPLATE_ID = temp.TEMPLATE_ID;
2374
2375 l_template_id number := NULL;
2376
2377 BEGIN
2378
2379 OPEN C_Default_Template(p_catalog_group_id);
2380 FETCH C_Default_Template INTO l_template_id;
2381 CLOSE C_Default_Template;
2382
2383 RETURN l_template_id;
2384
2385 END get_default_template;
2386 --End 2803833
2387
2388 ----------------------------------------------------------------
2389 --API Name : Sync_Template_Attribute Bug:3405225
2390 --Description : To sync up operational attribute values in mtl_item_templ_attributes
2391 -- with ego_templ_attributes
2392 --parameters:
2393 -- p_attribute_name is the full attribute name in mtl_item_templ_attributes
2394 -- is NULL for INSERTING new Template and attributes
2395 ----------------------------------------------------------------
2396
2397 PROCEDURE Sync_Template_Attribute(
2398 p_template_id IN NUMBER,
2399 p_attribute_name IN VARCHAR2)
2400 IS
2401 l_return_status VARCHAR2(2000);
2402 l_message_text VARCHAR2(2000);
2403 BEGIN
2404 IF (p_attribute_name IS NULL) THEN --Insert
2405 EGO_TEMPL_ATTRS_PUB.Sync_Template(
2406 p_template_id => p_template_id,
2407 p_commit => FND_API.G_FALSE,
2408 x_return_status => l_return_status,
2409 x_message_text => l_message_text);
2410 ELSE --Update
2411 EGO_TEMPL_ATTRS_PUB.Sync_Template_Attribute(
2412 p_template_id => p_template_id,
2413 p_attribute_name => p_attribute_name,
2414 p_commit => FND_API.G_FALSE,
2415 x_return_status => l_return_status,
2416 x_message_text => l_message_text);
2417 END IF;
2418 EXCEPTION
2419 WHEN OTHERS THEN
2420 NULL;
2421 END Sync_Template_Attribute;
2422
2423 ------------------------------------------------------------------------------------------
2424 --API Name : Update_Attribute_Control_Level Bug:3405225
2425 --Description : To update the control level of an attribute in EGO_FND_DF_COL_USGS_EXT
2426 --Parameteres required : 1) p_control_level is a valid control level
2427 -- as represented in lookup 'EGO_PC_CONTROL_LEVEL' in fnd_lookups
2428 -- 2) p_application_column_name is not null and is a valid column name
2429 -- 3) p_application_id is not null and is valid
2430 -- 4) p_descriptive_flexfield_name corresponds to a valid attribute group type
2431 ------------------------------------------------------------------------------------------
2432 PROCEDURE Update_Attribute_Control_Level (
2433 p_application_column_name IN VARCHAR2
2434 ,p_control_level IN NUMBER)
2435 IS
2436 l_return_status VARCHAR2(2000);
2437 l_message_count NUMBER;
2438 l_msg_data VARCHAR2(2000);
2439 BEGIN
2440 EGO_EXT_FWK_PUB.Update_Attribute_Control_Level(
2441 p_api_version => 1,
2442 p_application_id => 431,
2443 p_descriptive_flexfield_name => 'EGO_MASTER_ITEMS' ,
2444 p_application_column_name => p_application_column_name,
2445 p_control_level => p_control_level,
2446 p_init_msg_list => FND_API.G_FALSE,
2447 p_commit => FND_API.G_FALSE,
2448 x_return_status => l_return_status,
2449 x_msg_count => l_message_count,
2450 x_msg_data => l_msg_data);
2451
2452 -- Bug 14285936 begin
2453 IF ( p_application_column_name = 'INVENTORY_ITEM_STATUS_CODE' ) THEN
2454 FOR cur in ( select substr(attribute_name, 18) att_name from MTL_ITEM_ATTRIBUTES WHERE STATUS_CONTROL_CODE IN (1,2) ) LOOP
2455 EGO_EXT_FWK_PUB.Update_Attribute_Control_Level(
2456 p_api_version => 1,
2457 p_application_id => 431,
2458 p_descriptive_flexfield_name => 'EGO_MASTER_ITEMS' ,
2459 p_application_column_name => cur.att_name,
2460 p_control_level => p_control_level,
2461 p_init_msg_list => FND_API.G_FALSE,
2462 p_commit => FND_API.G_FALSE,
2463 x_return_status => l_return_status,
2464 x_msg_count => l_message_count,
2465 x_msg_data => l_msg_data);
2466 END LOOP;
2467 END IF;
2468 -- Bug 14285936 begin
2469
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 NULL;
2473 END Update_Attribute_Control_Level;
2474
2475 ------------------------------------------------------------------------------------------
2476 --API Name : Pending_Eco_Check_Sync_Ids
2477 --Description : Pending ECO check and sync lifecycles
2478 ------------------------------------------------------------------------------------------
2479 --Start :3637854
2480 PROCEDURE Pending_Eco_Check_Sync_Ids(
2481 P_Prog_AppId IN NUMBER DEFAULT -1
2482 ,P_Prog_Id IN NUMBER DEFAULT -1
2483 ,P_Request_Id IN NUMBER DEFAULT -1
2484 ,P_User_Id IN NUMBER DEFAULT -1
2485 ,P_Login_Id IN NUMBER DEFAULT -1
2486 ,P_Set_id IN NUMBER DEFAULT -999)
2487 IS
2488 CURSOR c_get_valid_item_records IS
2489 SELECT inventory_item_id
2490 ,organization_id
2491 ,item_catalog_group_id
2492 ,lifecycle_id
2493 ,current_phase_id
2494 ,transaction_id
2495 ,inventory_item_status_code
2496 ,rowid
2497 FROM MTL_SYSTEM_ITEMS_INTERFACE
2498 WHERE (set_process_id = p_set_id OR set_process_id = p_set_id + 1000000000000)
2499 AND transaction_type IN ('UPDATE', 'AUTO_CHILD')
2500 AND process_flag = 4;
2501
2502 CURSOR c_get_existing_item_record(cp_item_id NUMBER, cp_org_id NUMBER) IS
2503 SELECT item_catalog_group_id
2504 ,lifecycle_id
2505 ,current_phase_id
2506 FROM MTL_SYSTEM_ITEMS_B
2507 WHERE inventory_item_id = cp_item_id
2508 AND organization_id = cp_org_id;
2509
2510
2511 CURSOR c_get_valid_rev_records IS
2512 SELECT inventory_item_id
2513 ,organization_id
2514 ,revision_id
2515 ,lifecycle_id
2516 ,current_phase_id
2517 ,transaction_id
2518 ,rowid
2519 FROM mtl_item_revisions_interface
2520 WHERE (set_process_id = p_set_id OR set_process_id = p_set_id + 1000000000000)
2521 AND transaction_type = 'UPDATE'
2522 AND process_flag = 4;
2523
2524 CURSOR c_get_existing_rev_record(cp_rev_id NUMBER) IS
2525 SELECT lifecycle_id
2526 ,current_phase_id
2527 FROM mtl_item_revisions_b
2528 WHERE revision_id = cp_rev_id;
2529
2530 l_old_item_rec c_get_existing_item_record%ROWTYPE;
2531 l_old_rev_rec c_get_existing_rev_record%ROWTYPE;
2532 l_return_status VARCHAR2(1);
2533 l_msg_text VARCHAR2(2000);
2534 l_msg_count NUMBER;
2535 l_column_name VARCHAR2(30);
2536 dumm_status NUMBER;
2537 err_text VARCHAR2(2000);
2538
2539 BEGIN
2540
2541 IF (INV_Item_Util.g_Appl_Inst.EGO <> 0 AND
2542 INV_ITEM_UTIL.Object_Exists(p_object_type => 'PACKAGE'
2543 ,p_object_name => 'EGO_INV_ITEM_CATALOG_PVT') ='Y')
2544 THEN
2545
2546 FOR cur IN c_get_valid_item_records LOOP
2547
2548 OPEN c_get_existing_item_record(cur.inventory_item_id,cur.organization_id);
2549 FETCH c_get_existing_item_record INTO l_old_item_rec;
2550 CLOSE c_get_existing_item_record;
2551
2552 l_return_status := NULL;
2553
2554 IF l_old_item_rec.lifecycle_id IS NOT NULL
2555 AND cur.lifecycle_id IS NULL
2556 THEN
2557
2558 l_column_name := 'LIFECYCLE_ID';
2559
2560 EXECUTE IMMEDIATE
2561 ' BEGIN '
2562 ||' EGO_INV_ITEM_CATALOG_PVT.CHANGE_ITEM_LC_DEPENDECIES( '
2563 ||' P_API_VERSION => 1.0 '
2564 ||' ,P_INVENTORY_ITEM_ID => :cur.inventory_item_id '
2565 ||' ,P_ORGANIZATION_ID => :cur.organization_id '
2566 ||' ,P_ITEM_REVISION_ID => NULL '
2567 ||' ,P_LIFECYCLE_ID => NULL '
2568 ||' ,P_LIFECYCLE_PHASE_ID => NULL '
2569 ||' ,P_LIFECYCLE_CHANGED => NULL '
2570 ||' ,P_LIFECYCLE_PHASE_CHANGED => NULL '
2571 ||' ,P_PERFORM_SYNC_ONLY => FND_API.G_TRUE '
2572 ||' ,X_RETURN_STATUS => :l_return_status '
2573 ||' ,X_MSG_COUNT => :l_msg_count '
2574 ||' ,X_MSG_DATA => :l_msg_text); '
2575 ||' EXCEPTION '
2576 ||' WHEN OTHERS THEN '
2577 ||' NULL; '
2578 ||' END; '
2579 USING IN cur.inventory_item_id,
2580 IN cur.organization_id,
2581 OUT l_return_status,
2582 OUT l_msg_count,
2583 OUT l_msg_text;
2584
2585 ELSIF ( l_old_item_rec.lifecycle_id IS NOT NULL
2586 AND cur.lifecycle_id IS NOT NULL
2587 AND l_old_item_rec.lifecycle_id <> cur.lifecycle_id)
2588 --3802017 : Pending ECO check for NULL to NOTNULL case.
2589 OR (l_old_item_rec.lifecycle_id IS NULL
2590 AND cur.lifecycle_id IS NOT NULL)
2591 THEN
2592
2593 l_column_name := 'LIFECYCLE_ID';
2594
2595 EXECUTE IMMEDIATE
2596 ' BEGIN '
2597 ||' EGO_INV_ITEM_CATALOG_PVT.CHANGE_ITEM_LC_DEPENDECIES( '
2598 ||' P_API_VERSION => 1.0 '
2599 ||' ,P_INVENTORY_ITEM_ID => :cur.inventory_item_id '
2600 ||' ,P_ORGANIZATION_ID => :cur.organization_id '
2601 ||' ,P_ITEM_REVISION_ID => NULL '
2602 ||' ,P_LIFECYCLE_ID => :cur.lifecycle_id '
2603 ||' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '
2604 ||' ,P_LIFECYCLE_CHANGED => FND_API.G_TRUE '
2605 ||' ,P_LIFECYCLE_PHASE_CHANGED => FND_API.G_TRUE '
2606 ||' ,P_PERFORM_SYNC_ONLY => FND_API.G_FALSE '
2607 ||' ,X_RETURN_STATUS => :l_return_status '
2608 ||' ,X_MSG_COUNT => :l_msg_count '
2609 ||' ,X_MSG_DATA => :l_msg_text); '
2610 ||' EXCEPTION '
2611 ||' WHEN OTHERS THEN '
2612 ||' NULL; '
2613 ||' END; '
2614 USING IN cur.inventory_item_id,
2615 IN cur.organization_id,
2616 IN cur.lifecycle_id,
2617 IN cur.current_phase_id,
2618 OUT l_return_status,
2619 OUT l_msg_count,
2620 OUT l_msg_text;
2621
2622 ELSIF (l_old_item_rec.lifecycle_id = cur.lifecycle_id
2623 AND l_old_item_rec.current_phase_id <> cur.current_phase_id)
2624 THEN
2625
2626 l_column_name := 'CURRENT_PHASE_ID';
2627
2628 EXECUTE IMMEDIATE
2629 ' BEGIN '
2630 ||' EGO_INV_ITEM_CATALOG_PVT.CHANGE_ITEM_LC_DEPENDECIES( '
2631 ||' P_API_VERSION => 1.0 '
2632 ||' ,P_INVENTORY_ITEM_ID => :cur.inventory_item_id '
2633 ||' ,P_ORGANIZATION_ID => :cur.organization_id '
2634 ||' ,P_ITEM_REVISION_ID => NULL '
2635 ||' ,P_LIFECYCLE_ID => :cur.lifecycle_id '
2636 ||' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '
2637 ||' ,P_LIFECYCLE_CHANGED => FND_API.G_FALSE '
2638 ||' ,P_LIFECYCLE_PHASE_CHANGED => FND_API.G_TRUE '
2639 ||' ,P_PERFORM_SYNC_ONLY => FND_API.G_FALSE '
2640 ||' ,X_RETURN_STATUS => :l_return_status '
2641 ||' ,X_MSG_COUNT => :l_msg_count '
2642 ||' ,X_MSG_DATA => :l_msg_text); '
2643 ||' EXCEPTION '
2644 ||' WHEN OTHERS THEN '
2645 ||' NULL; '
2646 ||' END; '
2647 USING IN cur.inventory_item_id,
2648 IN cur.organization_id,
2649 IN cur.lifecycle_id,
2650 IN cur.current_phase_id,
2651 OUT l_return_status,
2652 OUT l_msg_count,
2653 OUT l_msg_text;
2654 END IF;
2655
2656 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657
2658 UPDATE mtl_system_items_interface
2659 SET process_flag = 3
2660 WHERE rowid = cur.rowid;
2661
2662 dumm_status := INVPUOPI.mtl_log_interface_err(
2663 cur.organization_id
2664 ,P_User_Id
2665 ,P_Login_Id
2666 ,P_Prog_AppId
2667 ,P_Prog_Id
2668 ,P_Request_Id
2669 ,cur.transaction_id
2670 ,l_msg_text
2671 ,l_column_name
2672 ,'MTL_SYSTEM_ITEMS_INTERFACE'
2673 ,'INV_IOI_ERR_C_ITM_LC_DEPND_01'--bug 11894684, replace INV_IOI_ERR
2674 ,err_text);
2675 END IF;
2676 END LOOP;
2677
2678
2679 FOR cur IN c_get_valid_rev_records LOOP
2680
2681 OPEN c_get_existing_rev_record(cp_rev_id=>cur.revision_id);
2682 FETCH c_get_existing_rev_record INTO l_old_rev_rec;
2683 CLOSE c_get_existing_rev_record;
2684
2685 l_return_status := NULL;
2686
2687 IF (l_old_rev_rec.lifecycle_id IS NOT NULL AND cur.lifecycle_id IS NULL)
2688 OR(l_old_rev_rec.lifecycle_id IS NOT NULL
2689 AND cur.lifecycle_id IS NOT NULL
2690 AND l_old_rev_rec.lifecycle_id <> cur.lifecycle_id)
2691 --3802017 : Pending ECO check for NULL to NOTNULL case.
2692 OR (l_old_rev_rec.lifecycle_id IS NULL
2693 AND cur.lifecycle_id IS NOT NULL)
2694 THEN
2695
2696 l_column_name := 'LIFECYCLE_ID';
2697
2698 EXECUTE IMMEDIATE
2699 ' BEGIN '
2700 ||' EGO_INV_ITEM_CATALOG_PVT.CHANGE_ITEM_LC_DEPENDECIES( '
2701 ||' P_API_VERSION => 1.0 '
2702 ||' ,P_INVENTORY_ITEM_ID => :cur.inventory_item_id '
2703 ||' ,P_ORGANIZATION_ID => :cur.organization_id '
2704 ||' ,P_ITEM_REVISION_ID => :cur.revision_id '
2705 ||' ,P_LIFECYCLE_ID => :cur.lifecycle_id '
2706 ||' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '
2707 ||' ,P_LIFECYCLE_CHANGED => FND_API.G_TRUE '
2708 ||' ,P_LIFECYCLE_PHASE_CHANGED => FND_API.G_TRUE '
2709 ||' ,P_PERFORM_SYNC_ONLY => FND_API.G_FALSE '
2710 ||' ,X_RETURN_STATUS => :l_return_status '
2711 ||' ,X_MSG_COUNT => :l_msg_count '
2712 ||' ,X_MSG_DATA => :l_msg_text); '
2713 ||' EXCEPTION '
2714 ||' WHEN OTHERS THEN '
2715 ||' NULL; '
2716 ||' END; '
2717 USING IN cur.inventory_item_id,
2718 IN cur.organization_id,
2719 IN cur.revision_id,
2720 IN cur.lifecycle_id,
2721 IN cur.current_phase_id,
2722 OUT l_return_status,
2723 OUT l_msg_count,
2724 OUT l_msg_text;
2725
2726 ELSIF (l_old_rev_rec.lifecycle_id = cur.lifecycle_id
2727 AND l_old_rev_rec.current_phase_id <> cur.current_phase_id)
2728 THEN
2729
2730 l_column_name := 'CURRENT_PHASE_ID';
2731
2732 EXECUTE IMMEDIATE
2733 ' BEGIN '
2734 ||' EGO_INV_ITEM_CATALOG_PVT.CHANGE_ITEM_LC_DEPENDECIES( '
2735 ||' P_API_VERSION => 1.0 '
2736 ||' ,P_INVENTORY_ITEM_ID => :cur.inventory_item_id '
2737 ||' ,P_ORGANIZATION_ID => :cur.organization_id '
2738 ||' ,P_ITEM_REVISION_ID => :cur.revision_id '
2739 ||' ,P_LIFECYCLE_ID => :cur.lifecycle_id '
2740 ||' ,P_LIFECYCLE_PHASE_ID => :cur.current_phase_id '
2741 ||' ,P_LIFECYCLE_CHANGED => FND_API.G_FALSE '
2742 ||' ,P_LIFECYCLE_PHASE_CHANGED => FND_API.G_TRUE '
2743 ||' ,P_PERFORM_SYNC_ONLY => FND_API.G_FALSE '
2744 ||' ,X_RETURN_STATUS => :l_return_status '
2745 ||' ,X_MSG_COUNT => :l_msg_count '
2746 ||' ,X_MSG_DATA => :l_msg_text); '
2747 ||' EXCEPTION '
2748 ||' WHEN OTHERS THEN '
2749 ||' NULL; '
2750 ||' END; '
2751 USING IN cur.inventory_item_id,
2752 IN cur.organization_id,
2753 IN cur.revision_id,
2754 IN cur.lifecycle_id,
2755 IN cur.current_phase_id,
2756 OUT l_return_status,
2757 OUT l_msg_count,
2758 OUT l_msg_text;
2759 END IF;
2760
2761 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2762
2763 UPDATE mtl_item_revisions_interface
2764 SET process_flag = 3
2765 WHERE rowid = cur.rowid;
2766
2767 dumm_status := INVPUOPI.mtl_log_interface_err(
2768 cur.organization_id
2769 ,P_User_Id
2770 ,P_Login_Id
2771 ,P_Prog_AppId
2772 ,P_Prog_Id
2773 ,P_Request_Id
2774 ,cur.transaction_id
2775 ,l_msg_text
2776 ,l_column_name
2777 ,'MTL_ITEM_REVISIONS_INTERFACE'
2778 ,'INV_IOI_ERR_C_ITM_LC_DEPND_02'--bug 11894684, replace INV_IOI_ERR
2779 ,err_text);
2780 END IF;
2781 END LOOP;
2782
2783 END IF; --INV_Item_Util.g_Appl_Inst.EGO <> 0
2784
2785 EXCEPTION
2786 WHEN OTHERS THEN
2787 NULL;
2788 END Pending_Eco_Check_Sync_Ids;
2789 --End : 3637854
2790 ------------------------------------------------------------------------------------------
2791 --API Name : Upgrade_cat_User_Attrs_Data
2792 --Description : Bug: 3527633 Added for EGO
2793 -- There are certain extensible attribute groups that are associated with the
2794 -- default category set of the product reporting functional area. When the
2795 -- default category set is changed we need to call an EGO API that will
2796 -- automatically associate these attribute groups with the new category set.
2797 --Parameteres required : 1) p_functional_area_id is a unctional area
2798 ------------------------------------------------------------------------------------------
2799 PROCEDURE Upgrade_cat_User_Attrs_Data ( p_functional_area_id IN NUMBER ) IS
2800 l_return_status VARCHAR2(2000);
2801 l_message_count NUMBER;
2802 l_msg_data VARCHAR2(2000);
2803 l_errorcode NUMBER;
2804 BEGIN
2805 IF (INV_Item_Util.g_Appl_Inst.EGO <> 0 AND
2806 INV_ITEM_UTIL.Object_Exists(p_object_type => 'PACKAGE'
2807 ,p_object_name => 'EGO_UPGRADE_USER_ATTR_VAL_PUB') ='Y')
2808 THEN
2809 EXECUTE IMMEDIATE
2810 'BEGIN
2811 EGO_UPGRADE_USER_ATTR_VAL_PUB.Upgrade_Cat_User_Attrs_Data
2812 (
2813 p_api_version => 1.0
2814 ,p_functional_area_id => :p_functional_area_id
2815 ,p_attr_group_name => NULL
2816 ,x_return_status => :l_return_status
2817 ,x_errorcode => :l_errorcode
2818 ,x_msg_count => :l_message_count
2819 ,x_msg_data => :l_msg_data
2820 ); '||
2821 'EXCEPTION '||
2822 ' WHEN OTHERS THEN '||
2823 ' NULL; '||
2824 'END;'
2825 USING IN p_functional_area_id, OUT l_return_status, OUT l_errorcode, OUT l_message_count, OUT l_msg_data;
2826 END IF;
2827 EXCEPTION
2828 WHEN OTHERS THEN
2829 NULL;
2830 END Upgrade_cat_User_Attrs_Data;
2831
2832 ------------------------------------------------------------------------------------------
2833 --API Name : Check_No_MFG_Associations
2834 --Description : Bug: 3735702 Added for EGO
2835 -- There are certain associations to the manufacturers which are used by EGO
2836 -- So, when deleting the Manufacturer, we need to check for the associations
2837 -- and flash an error if any associations exist
2838 --Parameteres required : 1) p_manufacturer_id 2)p_api_version
2839 --Return parameteres : 1) x_return_status = 'Y' if no associations exist
2840 -- 'N' in all other cases
2841 -- 2) x_message_text = valid only if x_return_status = 'N'
2842 --
2843 ------------------------------------------------------------------------------------------
2844 PROCEDURE Check_No_MFG_Associations
2845 (p_api_version IN NUMBER
2846 ,p_manufacturer_id IN NUMBER
2847 ,p_manufacturer_name IN VARCHAR2
2848 ,x_return_status OUT NOCOPY VARCHAR2
2849 ,x_message_name OUT NOCOPY VARCHAR2
2850 ,x_message_text OUT NOCOPY VARCHAR2
2851 ) IS
2852
2853 BEGIN
2854 IF (INV_Item_Util.g_Appl_Inst.EGO <> 0 AND
2855 INV_ITEM_UTIL.Object_Exists(p_object_type => 'PACKAGE'
2856 ,p_object_name => 'EGO_ITEM_AML_PUB') ='Y')
2857 THEN
2858 EXECUTE IMMEDIATE
2859 ' BEGIN '
2860 ||' EGO_ITEM_AML_PUB.Check_No_MFG_Associations( '
2861 ||' P_API_VERSION => :p_api_version '
2862 ||' ,P_MANUFACTURER_ID => :p_manufacturer_id '
2863 ||' ,P_MANUFACTURER_NAME => :p_manufacturer_name '
2864 ||' ,X_RETURN_STATUS => :x_return_status '
2865 ||' ,X_MESSAGE_NAME => :x_message_name '
2866 ||' ,X_MESSAGE_TEXT => :x_message_text); '
2867 ||' EXCEPTION '
2868 ||' WHEN OTHERS THEN '
2869 ||' NULL; '
2870 ||' END; '
2871 USING IN p_api_version,
2872 IN p_manufacturer_id,
2873 IN p_manufacturer_name,
2874 OUT x_return_status,
2875 OUT x_message_name,
2876 OUT x_message_text;
2877 ELSE
2878 x_return_status := 'Y';
2879 END IF;
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 x_return_status := 'N';
2883 x_message_text := SUBSTR('INV_EGO_REVISION_VALIDATE.Check_No_MFG_Associations ' || SQLERRM , 1,240);
2884 END CHECK_NO_MFG_ASSOCIATIONS;
2885
2886 ------------------------------------------------------------------------------------------
2887 --API Name : Check_Template_Cat_Associations
2888 --Description : Bug # 3326991 Added for Delete template Operation.
2889 --This procedure is used in the deletion of Item templates in the form
2890 --INVIDTMP.fmb (MTL_ITEM_TEMPLATES.check_delete_row)
2891
2892 -- An Item Template cannot be deleted if any associations to catalog categories exist
2893
2894 --Parameteres required : 1) p_template_id
2895 --Return parametere : 1) x_return_status = 1 if no associations exist
2896 -- 0 in all other cases
2897 ------------------------------------------------------------------------------------------
2898 PROCEDURE CHECK_TEMPLATE_CAT_ASSOCS
2899 (p_template_id IN NUMBER
2900 ,x_return_status OUT NOCOPY NUMBER
2901 ) IS
2902 l_template_exists NUMBER := 0;
2903 BEGIN
2904
2905 IF (INV_ITEM_UTIL.APPL_INST_EGO <> 0) THEN
2906 EXECUTE IMMEDIATE
2907 'SELECT count(*)
2908 FROM dual
2909 WHERE EXISTS(SELECT 1 FROM ego_cat_grp_templates
2910 WHERE template_id = :p_template_id)'
2911 INTO l_template_exists
2912 USING p_template_id;
2913 END IF;
2914 IF (l_template_exists <= 0) THEN
2915 x_return_status := 1;
2916 ELSE
2917 x_return_status := 0;
2918 END IF;
2919
2920 EXCEPTION
2921 WHEN OTHERS THEN
2922 x_return_status := 1;
2923 END CHECK_TEMPLATE_CAT_ASSOCS;
2924 ------------------------------------------------------------------------------------------
2925
2926 -- Added for 11.5.10+ UCCnet functionality
2927 ------------------------------------------------------------------------------------------
2928 --API Name : Process_UCCnet_Attributes
2929 --Description : Calls the method to update the REGISTRATION_UPDATE_DATE
2930 -- and TP_NEUTRAL_UPDATE_DATE for each Item/GTIN, when the respective
2931 -- attributes are changed
2932 ------------------------------------------------------------------------------------------
2933 PROCEDURE Process_UCCnet_Attributes(
2934 P_Prog_AppId IN NUMBER DEFAULT -1
2935 ,P_Prog_Id IN NUMBER DEFAULT -1
2936 ,P_Request_Id IN NUMBER DEFAULT -1
2937 ,P_User_Id IN NUMBER DEFAULT -1
2938 ,P_Login_Id IN NUMBER DEFAULT -1
2939 ,P_Set_id IN NUMBER DEFAULT -999)
2940 IS
2941 l_sql VARCHAR2(15000);
2942 l_suppress_flag VARCHAR2(10);
2943 BEGIN
2944 IF (INV_Item_Util.g_Appl_Inst.EGO <> 0 AND
2945 INV_ITEM_UTIL.Object_Exists(p_object_type => 'PACKAGE'
2946 ,p_object_name => 'EGO_GTIN_PVT') ='Y') THEN
2947 --Bug:4174218 BOM rollup code is calling ego-pvt with supress_rollup
2948 IF NVL(G_PROCESS_CONTROL,'N') <> 'SUPPRESS_ROLLUP' THEN
2949 l_suppress_flag := 'N';
2950 ELSE
2951 l_suppress_flag := 'Y';
2952 END IF;
2953 l_sql := 'BEGIN '||
2954 ' EGO_GTIN_PVT.PROCESS_UCCNET_ATTRIBUTES( '||
2955 ' P_Prog_AppId => :P_Prog_AppId, '||
2956 ' P_Prog_Id => :P_Prog_Id, '||
2957 ' P_Request_Id => :P_Request_Id, '||
2958 ' P_User_Id => :P_User_Id, '||
2959 ' P_Login_Id => :P_Login_Id, '||
2960 ' P_Set_id => :P_Set_id, '||
2961 ' P_Suppress_Rollup => :l_suppress_flag);'||
2962 'END; ';
2963 EXECUTE IMMEDIATE l_sql USING
2964 IN P_Prog_AppId,
2965 IN P_Prog_Id,
2966 IN P_Request_Id,
2967 IN P_User_Id,
2968 IN P_Login_Id,
2969 IN P_Set_id,
2970 IN l_suppress_flag;
2971 END IF;
2972 EXCEPTION
2973 WHEN OTHERS THEN
2974 NULL;
2975 END Process_UCCnet_Attributes;
2976
2977 /*------------------------------------------------------------------------------------------
2978 --API Name : Create_New_Item_Request
2979 --Description : Bug# 3777954
2980 --This procedure is used to create new item request for an item with 'CREATE' option.
2981 -- Only for EGO IOI/EXCEL this needs to be called.
2982 -- Current Phase, Approval Status and Item status will be reset accordingly.
2983 --Parameteres required : 1) p_set_process_id => Record Set Id
2984 ------------------------------------------------------------------------------------------*/
2985 PROCEDURE Create_New_Item_Request
2986 ( p_set_process_id IN NUMBER)
2987 IS
2988
2989 /* R12C : Changing the New Item Req Reqd = 'Y' sub-query for hierarchy enabled Catalogs */
2990 CURSOR c_nir_rec (cp_batch_id IN NUMBER)
2991 IS
2992 SELECT interface.inventory_item_id, interface.organization_id,
2993 interface.item_catalog_group_id
2994 FROM mtl_system_items_interface interface,
2995 mtl_parameters mp
2996 WHERE interface.SET_PROCESS_ID = p_set_process_id
2997 AND interface.PROCESS_FLAG = 4
2998 AND interface.TRANSACTION_TYPE = 'CREATE'
2999 AND interface.ITEM_CATALOG_GROUP_ID IS NOT NULL
3000 AND mp.ORGANIZATION_ID = interface.ORGANIZATION_ID
3001 AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID;
3002
3003 /* R12C : Introducing cursor for hierarchy enabled Catalogs */
3004 CURSOR c_nir_reqd (cp_item_catalog_group_id IN NUMBER)
3005 IS
3006 SELECT ICC.NEW_ITEM_REQUEST_REQD
3007 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
3008 WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
3009 AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
3010 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
3011 START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_item_catalog_group_id
3012 ORDER BY LEVEL ASC;
3013
3014 CURSOR c_nir_option
3015 IS
3016 SELECT nir_option
3017 FROM ego_import_option_sets
3018 WHERE batch_id = p_set_process_id;
3019
3020 --To fetch Batch level option in the case of Bulk SKU creation from UI
3021 CURSOR c_nir_batch_option
3022 IS
3023 SELECT selection_flag
3024 FROM ego_import_copy_options
3025 WHERE copy_option = 'NIR_OPTION'
3026 AND batch_id = p_set_process_id;
3027
3028 l_nir_import_option VARCHAR2(1);
3029 l_ret_status VARCHAR2(1);
3030 l_msg_data VARCHAR2(1000);
3031 l_msg_count NUMBER;
3032 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
3033 l_rec_for_process NUMBER := 0;
3034 l_nir_reqd VARCHAR2(1) := 'N';
3035
3036 BEGIN
3037
3038 IF (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'PLM_UI:Y') = 0 ) THEN
3039 /* EMTAPIA: commented out for bug: 7036113. Replacing this for loop with a single SQL statement
3040 FOR nir_item IN c_nir_rec(cp_batch_id => p_set_process_id)
3041 LOOP
3042 l_nir_reqd := 'N';
3043 -- R12C : Retrieving NIR reqd for hierarchy enabled Catalogs
3044 OPEN c_nir_reqd(cp_item_catalog_group_id => nir_item.item_catalog_group_id);
3045 FETCH c_nir_reqd INTO l_nir_reqd;
3046 CLOSE c_nir_reqd;
3047
3048 IF l_nir_reqd = 'Y' THEN
3049 UPDATE MTL_SYSTEM_ITEMS_B
3050 SET INVENTORY_ITEM_STATUS_CODE='Pending'
3051 ,APPROVAL_STATUS = 'N'
3052 ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3053 WHERE INVENTORY_ITEM_ID = nir_item.inventory_item_id
3054 AND ORGANIZATION_ID = nir_item.organization_id;
3055
3056 l_rec_for_process := 1;
3057 END IF;
3058 END LOOP;
3059 */
3060
3061 --EMTAPIA: added for bug 7036113
3062 /* Bug 8799957. Replacing LEVEL = 1 condition with ROWNUM = 1 to check if New Item Request is
3063 required or not, in case child ICC is inheriting NIR from parent ICC */
3064 UPDATE mtl_system_items_b
3065 SET inventory_item_status_code = 'Pending',
3066 approval_status = 'N',
3067 current_phase_id = DECODE(lifecycle_id, NULL, NULL,
3068 Get_Initial_Lifecycle_Phase(lifecycle_id))
3069 WHERE (inventory_item_id, organization_id) IN
3070 (SELECT interface.inventory_item_id, interface.organization_id
3071 FROM mtl_system_items_interface interface,
3072 mtl_parameters mp
3073 WHERE interface.set_process_id = p_set_process_id
3074 AND interface.process_flag = 4
3075 AND interface.transaction_type = 'CREATE'
3076 AND interface.item_catalog_group_id IS NOT NULL
3077 AND mp.organization_id = interface.organization_id
3078 AND mp.organization_id = mp.master_organization_id
3079 AND 'Y' =
3080 (SELECT icc.new_item_request_reqd
3081 FROM mtl_item_catalog_groups_b icc
3082 WHERE icc.new_item_request_reqd IS NOT NULL
3083 AND icc.new_item_request_reqd <> 'I'
3084 AND ROWNUM = 1
3085 CONNECT BY PRIOR
3086 icc.parent_catalog_group_id = icc.item_catalog_group_id
3087 START WITH
3088 icc.item_catalog_group_id = interface.item_catalog_group_id
3089 ));
3090
3091
3092 IF SQL%ROWCOUNT > 0 THEN
3093 l_rec_for_process := 1;
3094 END IF;
3095 --EMTAPIA: end added for bug 7036113
3096
3097
3098
3099 IF l_rec_for_process = 1 THEN
3100 OPEN c_nir_option;
3101 FETCH c_nir_option INTO l_nir_import_option;
3102
3103 IF c_nir_option%NOTFOUND THEN
3104 OPEN c_nir_batch_option;
3105 FETCH c_nir_batch_option INTO l_nir_import_option;
3106
3107 IF c_nir_batch_option%NOTFOUND THEN
3108 l_nir_import_option := 'I';
3109 END IF;
3110 CLOSE c_nir_batch_option;
3111 END IF;
3112 CLOSE c_nir_option;
3113
3114 IF l_nir_import_option <> 'N' THEN
3115
3116 /* R12C Modifying stmt to support hierarchy enabled catalogs for NIR reqd */
3117 UPDATE mtl_system_items_interface msii
3118 SET msii.process_flag = 5
3119 WHERE msii.ROWID IN
3120 ( SELECT interface.ROWID
3121 FROM mtl_system_items_interface interface,
3122 -- MTL_ITEM_CATALOG_GROUPS_B micb,
3123 MTL_PARAMETERS mp
3124 WHERE interface.SET_PROCESS_ID = p_set_process_id
3125 AND interface.PROCESS_FLAG = 4
3126 AND interface.TRANSACTION_TYPE = 'CREATE'
3127 --AND interface.ITEM_CATALOG_GROUP_ID = micb.item_catalog_group_id
3128 AND interface.ITEM_CATALOG_GROUP_ID IS NOT NULL
3129 --AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
3130 AND mp.organization_id = interface.organization_id
3131 AND interface.organization_id = mp.master_organization_id
3132 AND 'Y' =
3133 ( SELECT ICC.NEW_ITEM_REQUEST_REQD
3134 FROM MTL_ITEM_CATALOG_GROUPS_B ICC
3135 WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
3136 AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
3137 AND ROWNUM = 1
3138 CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
3139 START WITH ICC.ITEM_CATALOG_GROUP_ID = interface.ITEM_CATALOG_GROUP_ID ) );
3140
3141 ENG_NEW_ITEM_REQ_UTIL.create_new_item_requests( p_batch_id => p_set_process_id,
3142 p_nir_option => l_nir_import_option,
3143 x_return_status => l_ret_status,
3144 x_msg_data => l_msg_data,
3145 x_msg_count => l_msg_count );
3146
3147 IF (l_ret_status = FND_API.G_RET_STS_SUCCESS )THEN
3148 UPDATE mtl_system_items_interface
3149 SET process_flag = 4
3150 WHERE set_process_id = p_set_process_id
3151 AND process_flag = 5;
3152 ELSE
3153 IF l_inv_debug_level IN(101, 102) THEN
3154 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Following exception from CM during NIR creation');
3155 INVPUTLI.info(l_msg_data);
3156 END IF;
3157
3158 UPDATE mtl_system_items_interface
3159 SET process_flag = 3,
3160 change_id = NULL,
3161 change_line_id = NULL
3162 WHERE set_process_id = p_set_process_id
3163 AND process_flag = 5;
3164 END IF;
3165 END IF; -- NIR batch option NOT 'N'
3166 END IF; --More than 0 items for NIR creation
3167 END IF; -- Not called for UI create
3168 EXCEPTION
3169 WHEN OTHERS THEN
3170 IF l_inv_debug_level IN(101, 102) THEN
3171 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Exception'||substr(SQLERRM,1,200));
3172 END IF;
3173
3174 UPDATE mtl_system_items_interface
3175 SET process_flag = 3,
3176 change_id = NULL,
3177 change_line_id = NULL
3178 WHERE set_process_id = p_set_process_id
3179 AND process_flag = 5;
3180
3181 END Create_New_Item_Request;
3182
3183 /* Obsoleting the below procedure and replacing its definition with the one above - R12 FPC
3184 PROCEDURE Create_New_Item_Request
3185 ( p_set_process_id NUMBER
3186 ) IS
3187
3188 CURSOR c_get_processed_records (cp_set_process_id NUMBER) IS
3189 SELECT interface.INVENTORY_ITEM_ID,
3190 interface.ITEM_CATALOG_GROUP_ID,
3191 interface.ORGANIZATION_ID,
3192 interface.TRANSACTION_ID,
3193 interface.ITEM_NUMBER,
3194 interface.rowid,
3195 micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
3196 mp.ORGANIZATION_CODE
3197 FROM MTL_SYSTEM_ITEMS_INTERFACE interface,
3198 MTL_ITEM_CATALOG_GROUPS_B micb,
3199 MTL_PARAMETERS mp
3200 WHERE interface.SET_PROCESS_ID = cp_set_process_id
3201 AND interface.PROCESS_FLAG = 4
3202 AND interface.TRANSACTION_TYPE = 'CREATE'
3203 AND interface.ITEM_CATALOG_GROUP_ID = micb.ITEM_CATALOG_GROUP_ID
3204 AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
3205 AND mp.ORGANIZATION_ID = interface.ORGANIZATION_ID
3206 AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID --Bug 4517161
3207 FOR UPDATE OF interface.INVENTORY_ITEM_ID;
3208
3209 l_return_status VARCHAR2(10) := 'S';
3210 err_text VARCHAR2(240);
3211 l_dynamic_sql VARCHAR2(2000);
3212 l_dummy NUMBER;
3213 dumm_status NUMBER;
3214 l_cursor_id NUMBER;
3215 l_change_id NUMBER;
3216 l_error_table Error_Handler.Error_Tbl_Type;
3217 l_type_name VARCHAR2(1000);
3218 l_error boolean;
3219
3220 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
3221 BEGIN
3222
3223 IF (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'PLM_UI:Y') = 0 ) THEN
3224
3225 FOR i in c_get_processed_records(p_set_process_id) LOOP
3226 --Ideally this update should be done only after NIR created succesfully
3227 -- but for technical reasons we are not stoping item creation even though
3228 -- NIR is not created.
3229 UPDATE MTL_SYSTEM_ITEMS_B
3230 SET INVENTORY_ITEM_STATUS_CODE='Pending'
3231 ,APPROVAL_STATUS = 'N'
3232 ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3233 WHERE INVENTORY_ITEM_ID = i.inventory_item_id
3234 AND ORGANIZATION_ID = i.organization_id;
3235
3236 IF l_cursor_id IS NULL THEN
3237 l_cursor_id := DBMS_SQL.Open_Cursor;
3238 l_dynamic_sql := 'select type_name from eng_change_order_types_vl '||
3239 'where change_order_type_id = :type_id ';
3240 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.NATIVE);
3241 DBMS_SQL.define_column(l_cursor_id,1,l_type_name,1000);
3242 END IF;
3243 DBMS_SQL.Bind_Variable(l_cursor_id, 'type_id', i.NEW_ITEM_REQ_CHANGE_TYPE_ID);
3244 l_dummy := DBMS_SQL.Execute(l_cursor_id);
3245 IF DBMS_SQL.fetch_rows(l_cursor_id) > 0 THEN
3246 dbms_sql.column_value(l_cursor_id,1,l_type_name);
3247 EXECUTE IMMEDIATE
3248 ' BEGIN '
3249 ||' ENG_NEW_ITEM_REQ_UTIL.Create_New_Item_Request( '
3250 ||' change_number => :i.ITEM_NUMBER '
3251 ||' ,change_name => :i.ITEM_NUMBER '
3252 ||' ,change_type_code => :l_type_name '
3253 ||' ,item_number => :i.ITEM_NUMBER '
3254 ||' ,organization_code => :i.ORGANIZATION_CODE '
3255 ||' ,requestor_user_name => FND_GLOBAL.user_name '
3256 ||' ,batch_id => :p_set_process_id '
3257 ||' ,X_CHANGE_ID => :l_change_id '
3258 ||' ,X_RETURN_STATUS => :l_return_status ); '
3259 ||' EXCEPTION '
3260 ||' WHEN OTHERS THEN '
3261 ||' NULL; '
3262 ||' END; '
3263 USING IN i.ITEM_NUMBER,
3264 IN l_type_name,
3265 IN i.ORGANIZATION_CODE,
3266 IN p_set_process_id,
3267 OUT l_change_id,
3268 OUT l_return_status;
3269 ELSE
3270 l_error := true;
3271 END IF;
3272 IF (l_error OR l_return_status ='G' )THEN
3273 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3274 SET process_flag = 3
3275 WHERE rowid = i.rowid;
3276
3277 dumm_status := INVPUOPI.mtl_log_interface_err(
3278 i.organization_id,
3279 FND_GLOBAL.USER_ID,
3280 FND_GLOBAL.LOGIN_ID,
3281 FND_GLOBAL.PROG_APPL_ID,
3282 FND_GLOBAL.CONC_PROGRAM_ID,
3283 FND_GLOBAL.CONC_REQUEST_ID
3284 ,i.transaction_id
3285 ,err_text
3286 ,'ITEM_NUMBER'
3287 ,'MTL_SYSTEM_ITEMS_INTERFACE'
3288 ,'INV_IOI_NO_AUTO_NIR'
3289 ,err_text);
3290 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3291 Error_Handler.Get_Message_List( x_message_list => l_error_table);
3292 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3293 SET process_flag = 3
3294 WHERE rowid = i.rowid;
3295
3296 dumm_status := INVPUOPI.mtl_log_interface_err(
3297 i.organization_id,
3298 FND_GLOBAL.USER_ID,
3299 FND_GLOBAL.LOGIN_ID,
3300 FND_GLOBAL.PROG_APPL_ID,
3301 FND_GLOBAL.CONC_PROGRAM_ID,
3302 FND_GLOBAL.CONC_REQUEST_ID
3303 ,i.transaction_id
3304 ,l_error_table(1).message_text
3305 ,'ITEM_CATALOG_GORUP_ID'
3306 ,'MTL_SYSTEM_ITEMS_INTERFACE'
3307 ,'INV_IOI_ERR_CREATE_NIR'--bug 11894684, replace INV_IOI_ERR
3308 ,err_text);
3309 ELSE
3310 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3311 SET CHANGE_ID = l_change_id
3312 WHERE rowid = i.rowid;
3313 END IF;
3314 END LOOP;
3315 IF l_cursor_id IS NOT NULL THEN
3316 dbms_sql.close_cursor(l_cursor_id);
3317 END IF;
3318 END IF;
3319
3320 EXCEPTION
3321 WHEN OTHERS THEN
3322 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3323 SET process_flag = 3
3324 WHERE set_process_id= p_set_process_id;
3325
3326
3327 IF l_inv_debug_level IN(101, 102) THEN
3328 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Exception'||substr(SQLERRM,1,200));
3329 END IF;
3330
3331 dumm_status := INVPUOPI.mtl_log_interface_err(
3332 null,--i.organization_id,
3333 FND_GLOBAL.USER_ID,
3334 FND_GLOBAL.LOGIN_ID,
3335 FND_GLOBAL.PROG_APPL_ID,
3336 FND_GLOBAL.CONC_PROGRAM_ID,
3337 FND_GLOBAL.CONC_REQUEST_ID,
3338 p_set_process_id--i.transaction_id
3339 ,'CO NIR is having problem for this set proecess id'||p_set_process_id
3340 ,'ITEM_CATALOG_GORUP_ID'
3341 ,'MTL_SYSTEM_ITEMS_INTERFACE'
3342 ,'INV_IOI_ERR_E_CREATE_NIR'--bug 11894684, replace INV_IOI_ERR
3343 ,err_text);
3344 END Create_New_Item_Request; */
3345 ------------------------------------------------------------------------------------------
3346 FUNCTION Get_Process_Control RETURN VARCHAR2 IS
3347 BEGIN
3348 IF ( G_PROCESS_CONTROL IS NOT NULL ) THEN
3349 RETURN (G_PROCESS_CONTROL);
3350 ELSE
3351 RETURN ('PLM_UI:N');
3352 END IF;
3353 END Get_Process_Control;
3354 ------------------------------------------------------------------------------------------
3355 PROCEDURE Set_Process_Control(p_process_control VARCHAR2) IS
3356 BEGIN
3357 G_PROCESS_CONTROL := p_process_control;
3358 END Set_Process_Control;
3359 ------------------------------------------------------------------------------------------
3360
3361 FUNCTION Get_Process_Control_HTML_API RETURN VARCHAR2 IS
3362 BEGIN
3363 RETURN (G_PROCESS_CONTROL_HTML_API);
3364 END Get_Process_Control_HTML_API; /* Added to fix Bug#8434681*/
3365
3366
3367 PROCEDURE Set_Process_Control_HTML_API(p_process_control VARCHAR2) IS
3368 BEGIN
3369 G_PROCESS_CONTROL_HTML_API := p_process_control;
3370 END Set_Process_Control_HTML_API; /* Added to fix Bug#8434681*/
3371
3372 PROCEDURE Populate_Seq_Gen_Item_Nums
3373 (p_set_id IN NUMBER
3374 ,p_org_id IN NUMBER
3375 ,p_all_org IN NUMBER
3376 ,p_rec_status IN NUMBER
3377 ,x_return_status OUT NOCOPY VARCHAR2
3378 ,x_msg_count OUT NOCOPY NUMBER
3379 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3380
3381 l_sql VARCHAR2(1000);
3382 BEGIN
3383 x_return_status := FND_API.G_RET_STS_SUCCESS;
3384 x_msg_count := 0;
3385 x_msg_data := NULL;
3386 IF NOT (INV_Item_Util.g_Appl_Inst.EGO <> 0 AND
3387 INV_ITEM_UTIL.Object_Exists
3388 (p_object_type => 'PACKAGE'
3389 ,p_object_name => 'EGO_ITEM_BULKLOAD_PKG') ='Y') THEN
3390 -- EGO not installed here no need to do further processing
3391 RETURN;
3392 END IF;
3393
3394 l_sql := ' BEGIN '||
3395 ' EGO_ITEM_BULKLOAD_PKG.Populate_Seq_Gen_Item_Nums( '||
3396 ' p_set_id => :p_set_id, '||
3397 ' p_org_id => :p_org_id, '||
3398 ' p_all_org => :p_all_org, '||
3399 ' p_rec_status => :p_rec_status, '||
3400 ' x_return_status => :x_return_status, '||
3401 ' x_msg_count => :x_msg_count, '||
3402 ' x_msg_data => :x_msg_data); '||
3403 ' END; ';
3404 EXECUTE IMMEDIATE l_sql USING
3405 IN p_set_id,
3406 IN p_org_id,
3407 IN p_all_org,
3408 IN p_rec_status,
3409 OUT x_return_status,
3410 OUT x_msg_count,
3411 OUT x_msg_data;
3412
3413 EXCEPTION
3414 WHEN OTHERS THEN
3415 x_return_status := FND_API.G_RET_STS_ERROR;
3416 x_msg_count := 1;
3417 x_msg_data := SQLERRM;
3418 END Populate_Seq_Gen_Item_Nums;
3419
3420 --Removed the procedure body for 5498078
3421 --5208102 :Insert_Revision_UserAttr added
3422 --Code is identical to Insert_Grants_And_UserAttr.
3423 PROCEDURE Insert_Revision_UserAttr(P_Set_id IN NUMBER DEFAULT -999) IS
3424 BEGIN
3425 RETURN;
3426 END Insert_Revision_UserAttr;
3427
3428 -- Bug: 5258295
3429 FUNCTION mtl_catalog_group_update(
3430 p_rowid IN ROWID
3431 ,p_process_flag IN NUMBER --Added for R12C
3432 ,p_inventory_item_id IN NUMBER
3433 ,p_organization_id IN NUMBER
3434 ,p_old_item_cat_grp_id IN NUMBER
3435 ,p_new_item_cat_grp_id IN NUMBER
3436 ,p_approval_status IN VARCHAR2
3437 ,p_item_number IN VARCHAR2
3438 ,p_transaction_id IN NUMBER
3439 ,p_prog_appid IN NUMBER
3440 ,p_prog_id IN NUMBER
3441 ,p_request_id IN NUMBER
3442 ,p_xset_id IN NUMBER
3443 ,p_user_id IN NUMBER
3444 ,p_login_id IN NUMBER
3445 ,x_err_text OUT NOCOPY VARCHAR2) RETURN INTEGER IS
3446
3447 CURSOR c_get_nir_setup(cp_item_catalog_group_id IN NUMBER) IS
3448 SELECT new_item_request_reqd
3449 FROM mtl_item_catalog_groups_b
3450 WHERE new_item_request_reqd IS NOT NULL
3451 AND new_item_request_reqd <> 'I'
3452 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
3453 START WITH item_catalog_group_id = cp_item_catalog_group_id
3454 ORDER BY LEVEL ASC;
3455
3456 l_nir_status_type NUMBER;
3457 l_nir_approval_status_type NUMBER;
3458 l_old_nir_change_type NUMBER;
3459 l_new_nir_change_type NUMBER;
3460 l_old_nir_reqd VARCHAR2(1);
3461 l_old_item_cat_grp_id NUMBER;
3462 l_new_nir_reqd VARCHAR2(1);
3463 dumm_status NUMBER;
3464 l_type_name VARCHAR2(1000);
3465 l_error BOOLEAN;
3466 l_dynamic_sql VARCHAR2(2000);
3467 l_error_msg VARCHAR2(2000);
3468 l_dummy NUMBER;
3469 l_cursor_id NUMBER;
3470 l_error_table Error_Handler.Error_Tbl_Type;
3471 l_change_id NUMBER;
3472 l_change_notice VARCHAR2(100);
3473 l_return_status VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
3474 l_pk1_value VARCHAR2(100);
3475 l_raise_create_nir BOOLEAN := FALSE;
3476 l_raise_cancel_nir BOOLEAN := FALSE;
3477 l_update_msb BOOLEAN := FALSE;
3478 l_error_occured BOOLEAN := FALSE;
3479
3480
3481 BEGIN
3482
3483 l_raise_create_nir := FALSE;
3484 l_raise_cancel_nir := FALSE;
3485 l_update_msb := FALSE;
3486
3487 IF l_cursor_id IS NULL THEN
3488 l_cursor_id := DBMS_SQL.Open_Cursor;
3489
3490 /* Bug 5253300 /5253294 Using Status Type Column from eng_change_statuses_vl corresponding to
3491 Status Code from eng_engineering_changes - Anmurali */
3492 l_dynamic_sql := 'SELECT st.status_type
3493 ,ch.approval_status_type
3494 ,ch.change_id
3495 ,ch.change_notice
3496 FROM eng_engineering_changes ch
3497 ,eng_change_subjects sb
3498 ,eng_change_order_types_vl tp
3499 ,eng_change_statuses_vl st
3500 WHERE tp.type_classification = :l_type_classification
3501 AND tp.change_mgmt_type_code = :l_change_mgmt_type_code
3502 AND ch.change_mgmt_type_code = tp.change_mgmt_type_code
3503 AND ch.change_id = sb.change_id
3504 AND sb.entity_name = :l_entity_name
3505 AND st.status_code = ch.status_code
3506 AND sb.pk1_value = :l_inv_item_id
3507 AND sb.pk2_value = :l_org_id';
3508
3509 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.NATIVE);
3510 DBMS_SQL.define_column(l_cursor_id,1,l_nir_status_type);
3511 DBMS_SQL.define_column(l_cursor_id,2,l_nir_approval_status_type);
3512 DBMS_SQL.define_column(l_cursor_id,3,l_change_id);
3513 DBMS_SQL.define_column(l_cursor_id,4,l_change_notice,100);
3514 END IF;
3515
3516 DBMS_SQL.Bind_Variable(l_cursor_id, 'l_type_classification' , 'CATEGORY');
3517 DBMS_SQL.Bind_Variable(l_cursor_id, 'l_change_mgmt_type_code', 'NEW_ITEM_REQUEST');
3518 DBMS_SQL.Bind_Variable(l_cursor_id, 'l_entity_name' , 'EGO_ITEM');
3519 DBMS_SQL.Bind_Variable(l_cursor_id, 'l_inv_item_id' , p_inventory_item_id);
3520 DBMS_SQL.Bind_Variable(l_cursor_id, 'l_org_id' , p_organization_id);
3521 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
3522
3523 IF DBMS_SQL.fetch_rows(l_cursor_id) > 0 THEN
3524 dbms_sql.column_value(l_cursor_id,1,l_nir_status_type);
3525 dbms_sql.column_value(l_cursor_id,2,l_nir_approval_status_type);
3526 dbms_sql.column_value(l_cursor_id,3,l_change_id);
3527 dbms_sql.column_value(l_cursor_id,4,l_change_notice);
3528 END IF;
3529
3530 OPEN c_get_nir_setup (p_new_item_cat_grp_id);
3531 FETCH c_get_nir_setup INTO l_new_nir_reqd;
3532 CLOSE c_get_nir_setup;
3533
3534 OPEN c_get_nir_setup (p_old_item_cat_grp_id);
3535 FETCH c_get_nir_setup INTO l_old_nir_reqd;
3536 CLOSE c_get_nir_setup;
3537
3538 IF l_old_nir_reqd = 'Y' THEN -- Originating ICC HAS NIR
3539
3540 IF l_new_nir_reqd = 'Y' THEN -- If both originating and destination ICC HAS NIR
3541
3542 IF l_nir_status_type = 5
3543 AND l_nir_approval_status_type IN (1,4)
3544 AND p_approval_status IN ( 'N', 'R' )
3545 THEN
3546
3547 l_raise_create_nir := TRUE;
3548
3549 ELSIF (p_approval_status = 'N'
3550 AND l_nir_approval_status_type = 1
3551 AND l_nir_status_type = 1)
3552 OR (p_approval_status = 'S'
3553 AND l_nir_approval_status_type = 3
3554 AND l_nir_status_type = 8)
3555 OR (p_approval_status = 'A'
3556 AND l_nir_approval_status_type = 5
3557 AND l_nir_status_type = 8)
3558 -- Added to handle the case of rejected items - Bug 5224208
3559 OR (p_approval_status = 'R'
3560 AND l_nir_approval_status_type = 4
3561 AND l_nir_status_type = 8)
3562
3563 THEN
3564
3565 l_raise_cancel_nir := TRUE;
3566 l_raise_create_nir := TRUE;
3567
3568 END IF;
3569
3570 ELSIF l_new_nir_reqd = 'N' THEN -- If the destination ICC does not have NIR
3571
3572 IF l_nir_status_type = 5
3573 AND l_nir_approval_status_type IN (1,4)
3574 AND p_approval_status IN ( 'N', 'R' )
3575 THEN
3576 l_update_msb := TRUE;
3577
3578 ELSIF (l_nir_status_type = 1
3579 AND l_nir_approval_status_type = 1
3580 AND p_approval_status = 'N' )
3581 OR (p_approval_status = 'S'
3582 AND l_nir_approval_status_type = 3
3583 AND l_nir_status_type = 8)
3584 -- Added to handle the case of rejected items - Bug 5224213
3585 OR (p_approval_status = 'R'
3586 AND l_nir_approval_status_type = 4
3587 AND l_nir_status_type = 8)
3588
3589 THEN
3590
3591 l_raise_cancel_nir := TRUE;
3592 l_update_msb := TRUE;
3593
3594 ELSIF p_approval_status = 'A'
3595 AND l_nir_approval_status_type = 5
3596 AND l_nir_status_type = 8
3597 THEN
3598 --Replacing the erroneous create call with cancel -5222730-Anmurali
3599 l_raise_cancel_nir := TRUE;
3600 END IF;
3601 END IF; --IF l_new_nir_reqd = 'Y' THEN
3602 END IF; --IF l_old_nir_reqd = 'Y' THEN
3603
3604 l_error_occured := FALSE;
3605
3606 IF l_raise_cancel_nir THEN
3607 --Changing the call as per signature change - R12 C
3608 Cancel_New_Item_Request(
3609 p_inventory_item_id => p_inventory_item_id
3610 ,p_organization_id => p_organization_id
3611 ,p_item_number => p_item_number
3612 ,p_auto_commit => FND_API.G_FALSE
3613 ,p_wf_user_id => p_user_id
3614 ,p_fnd_user_id => p_login_id
3615 ,x_return_status => l_return_status );
3616
3617 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3618 l_error_occured := TRUE;
3619
3620 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3621 SET process_flag = 3
3622 WHERE rowid = p_rowid;
3623
3624 dumm_status := INVPUOPI.mtl_log_interface_err(
3625 p_organization_id,
3626 p_user_id,
3627 p_login_id,
3628 p_prog_appid,
3629 p_prog_id,
3630 p_request_id,
3631 p_transaction_id,
3632 l_error_msg,
3633 'ITEM_CATALOG_GORUP_ID',
3634 'MTL_SYSTEM_ITEMS_INTERFACE',
3635 'INV_IOI_CANCEL_NIR_FAILED',
3636 x_err_text);
3637 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3638 l_error_occured := TRUE;
3639 Error_Handler.Get_Message_List( x_message_list => l_error_table);
3640
3641 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3642 SET process_flag = 3
3643 WHERE rowid = p_rowid;
3644
3645 dumm_status := INVPUOPI.mtl_log_interface_err(
3646 p_organization_id,
3647 p_user_id,
3648 p_login_id,
3649 p_prog_appid,
3650 p_prog_id,
3651 p_request_id,
3652 p_transaction_id,
3653 l_error_table(1).message_text,
3654 'ITEM_CATALOG_GORUP_ID',
3655 'MTL_SYSTEM_ITEMS_INTERFACE',
3656 'INV_IOI_ERR_CANCEL_NIR',--bug 11894684, replace INV_IOI_ERR
3657 x_err_text);
3658 END IF;
3659 END IF; --IF l_raise_cancel_nir THEN
3660
3661 IF l_raise_create_nir AND NOT l_error_occured THEN
3662 Create_New_Item_Req_Upd (p_row_id => p_rowid
3663 ,p_item_catalog_group_id => p_new_item_cat_grp_id
3664 ,p_item_number => p_item_number
3665 ,p_inventory_item_id => p_inventory_item_id
3666 ,p_organization_id => p_organization_id
3667 ,p_xset_id => p_xset_id --Adding for R12 C
3668 ,p_process_flag => p_process_flag
3669 ,x_return_status => l_return_status);
3670
3671 IF (l_return_status ='G' )THEN
3672 l_error_occured := TRUE;
3673
3674 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3675 SET process_flag = 3
3676 WHERE rowid = p_rowid;
3677
3678 dumm_status := INVPUOPI.mtl_log_interface_err(
3679 p_organization_id
3680 ,p_user_id
3681 ,p_login_id
3682 ,p_prog_appid
3683 ,p_prog_id
3684 ,p_request_id
3685 ,p_transaction_id
3686 ,l_error_msg
3687 ,'ITEM_NUMBER'
3688 ,'MTL_SYSTEM_ITEMS_INTERFACE'
3689 ,'INV_IOI_NO_AUTO_NIR'
3690 ,x_err_text);
3691 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS )THEN
3692 l_error_occured := TRUE;
3693 Error_Handler.Get_Message_List( x_message_list => l_error_table);
3694 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
3695 SET process_flag = 3
3696 WHERE rowid = p_rowid;
3697
3698 dumm_status := INVPUOPI.mtl_log_interface_err(
3699 p_organization_id
3700 ,p_user_id
3701 ,p_login_id
3702 ,p_prog_appid
3703 ,p_prog_id
3704 ,p_request_id
3705 ,p_transaction_id
3706 ,l_error_table(1).message_text
3707 ,'ITEM_CATALOG_GORUP_ID'
3708 ,'MTL_SYSTEM_ITEMS_INTERFACE'
3709 ,'INV_IOI_ERR_E_C_NIR_UPD'--bug 11894684, replace INV_IOI_ERR
3710 ,x_err_text);
3711 END IF;
3712 END IF; --IF l_raise_create_nir AND NOT l_error_occured THEN
3713
3714 IF l_update_msb AND NOT l_error_occured THEN
3715 UPDATE mtl_system_items_b
3716 SET approval_status = 'A'
3717 WHERE inventory_item_id = p_inventory_item_id
3718 AND organization_id = p_organization_id;
3719 END IF;
3720
3721 IF l_cursor_id IS NOT NULL THEN
3722 dbms_sql.close_cursor(l_cursor_id);
3723 END IF;
3724
3725 RETURN (0);
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 IF l_cursor_id IS NOT NULL THEN
3730 dbms_sql.close_cursor(l_cursor_id);
3731 END IF;
3732 RETURN(SQLCODE);
3733 END mtl_catalog_group_update;
3734
3735
3736 --Changing signature for R12 C
3737 PROCEDURE Cancel_New_Item_Request ( p_inventory_item_id IN NUMBER,
3738 p_organization_id IN NUMBER,
3739 p_item_number IN VARCHAR2,
3740 p_auto_commit IN VARCHAR2,
3741 p_wf_user_id IN NUMBER,
3742 p_fnd_user_id IN NUMBER,
3743 x_return_status OUT NOCOPY VARCHAR2 )
3744 IS
3745
3746 dumm_status NUMBER;
3747 l_translated_text fnd_new_messages.message_text%TYPE;
3748 l_nir_cancel_status VARCHAR2(10);
3749
3750 BEGIN
3751
3752 x_return_status := FND_API.G_RET_STS_SUCCESS;
3753 dumm_status := INVUPD2B.get_message('EGO_NIR_CANCEL_COMMENT', l_translated_text);
3754
3755 ENG_NIR_UTIL_PKG.Cancel_nir_for_item(
3756 p_item_id => p_inventory_item_id
3757 ,p_org_id => p_organization_id
3758 -- ,p_item_number => p_item_number
3759 ,p_auto_commit => p_auto_commit
3760 -- ,p_mode => 'CANCEL'
3761 ,p_wf_user_id => p_wf_user_id
3762 ,p_fnd_user_id => p_fnd_user_id
3763 ,p_cancel_comments => l_translated_text
3764 ,x_nir_cancel_status => l_nir_cancel_status);
3765
3766 /* EXECUTE IMMEDIATE
3767 ' BEGIN '
3768 ||' ENG_NIR_UTIL_PKG.Cancel_NIR( '
3769 ||' p_change_id => :p_change_id '
3770 ||' ,p_org_id => :p_org_id '
3771 ||' ,p_change_notice => :p_change_notice '
3772 ||' ,p_auto_commit => :p_auto_commit '
3773 ||' ,p_wf_user_id => :p_wf_user_id '
3774 ||' ,p_fnd_user_id => :p_fnd_user_id '
3775 ||' ,p_cancel_comments => :l_translated_text '
3776 ||' ,x_nir_cancel_status => :l_nir_cancel_status); '
3777 ||' EXCEPTION '
3778 ||' WHEN OTHERS THEN '
3779 ||' NULL; '
3780 ||' END; '
3781 USING IN p_change_id,
3782 IN p_org_id,
3783 IN p_change_notice,
3784 IN p_auto_commit,
3785 IN p_wf_user_id,
3786 IN p_fnd_user_id,
3787 IN l_translated_text,
3788 OUT l_nir_cancel_status; */
3789 EXCEPTION
3790 WHEN others THEN
3791 x_return_status := FND_API.G_RET_STS_ERROR;
3792 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Cancel_NIR: Exception'||substr(SQLERRM,1,200));
3793 END Cancel_New_Item_Request;
3794
3795 /* Obsoleting this procedure and recreating it below with new NIR create signature for R12 FPC
3796 PROCEDURE Create_New_Item_Req_Upd ( p_item_catalog_group_id IN NUMBER,
3797 p_item_number IN VARCHAR2,
3798 p_inventory_item_id IN NUMBER,
3799 p_organization_id IN NUMBER,
3800 x_return_status OUT NOCOPY VARCHAR2 )
3801 IS
3802 l_type_name VARCHAR2(1000);
3803 l_type_id NUMBER;
3804 l_org_code VARCHAR2(10);
3805 l_dynamic_sql VARCHAR2(2000);
3806 l_dummy NUMBER;
3807 l_cursor_id NUMBER;
3808 l_error_table Error_Handler.Error_Tbl_Type;
3809 l_return_status VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
3810 BEGIN
3811
3812 SELECT organization_code INTO l_org_code
3813 FROM mtl_parameters
3814 WHERE organization_id = p_organization_id;
3815
3816 SELECT new_item_req_change_type_id INTO l_type_id
3817 FROM mtl_item_catalog_groups_b
3818 WHERE item_catalog_group_id = p_item_catalog_group_id;
3819
3820 UPDATE MTL_SYSTEM_ITEMS_B
3821 SET INVENTORY_ITEM_STATUS_CODE='Pending'
3822 ,APPROVAL_STATUS = 'N'
3823 ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3824 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3825 AND ORGANIZATION_ID = p_organization_id;
3826
3827 IF l_cursor_id IS NULL THEN
3828 l_cursor_id := DBMS_SQL.Open_Cursor;
3829 l_dynamic_sql := 'select type_name from eng_change_order_types_vl '||
3830 'where change_order_type_id = :type_id ';
3831 DBMS_SQL.Parse(l_cursor_id, l_dynamic_sql, DBMS_SQL.NATIVE);
3832 DBMS_SQL.define_column(l_cursor_id,1,l_type_name,1000);
3833 END IF;
3834 DBMS_SQL.Bind_Variable(l_cursor_id, 'type_id', l_type_id);
3835 l_dummy := DBMS_SQL.Execute(l_cursor_id);
3836 IF DBMS_SQL.fetch_rows(l_cursor_id) > 0 THEN
3837 dbms_sql.column_value(l_cursor_id,1,l_type_name);
3838 EXECUTE IMMEDIATE
3839 ' BEGIN '
3840 ||' ENG_NEW_ITEM_REQ_UTIL.Create_New_Item_Request( '
3841 ||' change_number => :p_item_number '
3842 ||' ,change_name => :p_item_number '
3843 ||' ,change_type_code => :l_type_code '
3844 ||' ,item_number => :p_item_number '
3845 ||' ,organization_code => :l_org_code '
3846 ||' ,requestor_user_name => FND_GLOBAL.USER_NAME '
3847 ||' ,X_RETURN_STATUS => :l_return_status ); '
3848 ||' EXCEPTION '
3849 ||' WHEN OTHERS THEN '
3850 ||' NULL; '
3851 ||' END; '
3852 USING IN p_item_number,
3853 IN l_type_name,
3854 IN l_org_code,
3855 OUT l_return_status;
3856 ELSE
3857 l_return_status := FND_API.G_RET_STS_ERROR;
3858 END IF;
3859 x_return_status := l_return_status;
3860 IF l_cursor_id IS NOT NULL THEN
3861 dbms_sql.close_cursor(l_cursor_id);
3862 END IF;
3863 EXCEPTION
3864 WHEN others THEN
3865 x_return_status := FND_API.G_RET_STS_ERROR;
3866 IF l_cursor_id IS NOT NULL THEN
3867 dbms_sql.close_cursor(l_cursor_id);
3868 END IF;
3869 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Exception'||substr(SQLERRM,1,200));
3870 END Create_New_Item_Req_Upd; */
3871 -- End : 5258295
3872
3873 PROCEDURE Create_New_Item_Req_Upd ( p_row_id IN ROWID,
3874 p_item_catalog_group_id IN NUMBER,
3875 p_item_number IN VARCHAR2,
3876 p_inventory_item_id IN NUMBER,
3877 p_organization_id IN NUMBER,
3878 p_xset_id IN NUMBER,
3879 p_process_flag IN NUMBER,
3880 x_return_status OUT NOCOPY VARCHAR2 )
3881 IS
3882 l_return_status VARCHAR2(1);
3883 l_err_text VARCHAR2(1000);
3884 l_msg_count NUMBER;
3885 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
3886 l_proc_flag NUMBER;
3887 BEGIN
3888 UPDATE MTL_SYSTEM_ITEMS_B
3889 SET INVENTORY_ITEM_STATUS_CODE='Pending'
3890 ,APPROVAL_STATUS = 'N'
3891 ,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
3892 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
3893 AND ORGANIZATION_ID = p_organization_id;
3894
3895 UPDATE mtl_system_items_interface
3896 SET process_flag = 5
3897 WHERE rowid = p_row_id;
3898
3899 ENG_NEW_ITEM_REQ_UTIL.Create_New_Item_Requests( p_batch_id => p_xset_id
3900 ,p_nir_option => 'I'
3901 ,x_return_status => l_return_status
3902 ,x_msg_data => l_err_text
3903 ,x_msg_count => l_msg_count);
3904
3905 x_return_status := l_return_status;
3906
3907 IF (l_return_status = FND_API.G_RET_STS_SUCCESS )THEN
3908 UPDATE mtl_system_items_interface
3909 SET process_flag = p_process_flag
3910 WHERE rowid = p_row_id;
3911 ELSE
3912 IF l_inv_debug_level IN(101, 102) THEN
3913 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Following exception from CM during NIR creation');
3914 INVPUTLI.info(l_err_text);
3915 END IF;
3916
3917 UPDATE mtl_system_items_interface
3918 SET process_flag = 3,
3919 change_id = NULL,
3920 change_line_id = NULL
3921 WHERE rowid = p_row_id;
3922 END IF;
3923
3924 EXCEPTION
3925 WHEN OTHERS THEN
3926 IF l_inv_debug_level IN(101, 102) THEN
3927 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.Create_New_Item_Request: Exception'||substr(SQLERRM,1,200));
3928 END IF;
3929
3930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3931
3932 UPDATE mtl_system_items_interface
3933 SET process_flag = 3,
3934 change_id = NULL,
3935 change_line_id = NULL
3936 WHERE rowid = p_row_id;
3937
3938 END Create_New_Item_Req_Upd;
3939
3940 --Removed the procedure body for 5498078
3941 --Added for bug 5435229
3942 PROCEDURE apply_default_uda_values(P_Set_id IN NUMBER DEFAULT -999,p_commit IN NUMBER DEFAULT 1) IS /* Added to fix Bug#7422423*/
3943 l_err_bug VARCHAR2(1000); /* Added to fix Bug#8434681*/
3944 l_ret_code VARCHAR2(1000); /* Added to fix Bug#8434681*/
3945 l_commit_flag VARCHAR2(1) ; /* Added to fix Bug#7422423*/
3946 BEGIN
3947 /* Added to fix Bug#8434681: Make sure template is not applied for HTML pages. This is because the template is pre-applied using java code in HTML pages.
3948 However, the template must be applied if code is being called from 1. open API, 2.excel import, 3.concurrent request (import items batch) */
3949 IF (INSTR(NVL(G_PROCESS_CONTROL,'PLM_UI:N'),'PLM_UI:Y') <> 0 AND
3950 SUBSTR(NVL(G_PROCESS_CONTROL_HTML_API,'HTML'),1,3) <> 'API')
3951 THEN
3952
3953 return;
3954 END IF;
3955
3956 INVPUTLI.info('Start : INV_EGO_REVISION_VALIDATE.apply_default_uda_values');
3957 IF SUBSTR(NVL(G_PROCESS_CONTROL,'SSSSSS'),1,6) <> 'NO_NIR'
3958 AND NVL(G_PROCESS_CONTROL,'SSSSSS') NOT IN ('EGO_XL_IMPORT_ITEM1','EGO_XL_IMPORT_ITEM2','EGO_XL_IMPORT_ITEM3')
3959 THEN
3960 -- commit; /* Commented to fix Bug#7422423*/
3961 /* Bug 8275121. Pass the parameter p_validate_only as FND_API.G_TRUE to validate Item Associations against both
3962 EGO_ITEM_ASSOCIATIONS and EGO_ITEM_ASSOCIATIONS_INTF tables, in the function
3963 EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data(). */
3964
3965 /* Bug 9336604 - Commenting the below call since EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data
3966 does not handle templates in 12.1 code
3967 EGO_ITEM_USER_ATTRS_CP_PUB.Process_Item_User_Attrs_Data(
3968 ERRBUF => l_err_bug
3969 ,RETCODE => l_err_bug
3970 ,p_data_set_id => P_Set_id
3971 ,p_initialize_error_handler => FND_API.G_FALSE -- Added for bug 8351181
3972 ,p_validate_only => FND_API.G_TRUE
3973 ,p_commit => l_commit_flag ); */ -- Bug 9336604 /* Added to fix Bug#7422423*/
3974
3975 NULL; -- Bug 9336604
3976
3977 END IF;
3978 INVPUTLI.info('End : INV_EGO_REVISION_VALIDATE.apply_default_uda_values');
3979 EXCEPTION
3980 WHEN OTHERS THEN
3981 NULL; -- Donot propogate any error message.
3982 INVPUTLI.info('INV_EGO_REVISION_VALIDATE.apply_default_uda_values: Exception'||substr(SQLERRM,1,200));
3983
3984 END apply_default_uda_values;
3985
3986 -- ============================================================================
3987 -- API Name : validate_style_sku
3988 -- Description : This procedure will be called from IOI
3989 -- (after all other validations, including lifecycle and phase)
3990 -- to validate the Style/SKU attributes of the item.
3991 -- ============================================================================
3992
3993 FUNCTION validate_style_sku (P_Row_Id IN ROWID,
3994 P_Xset_id IN NUMBER,
3995 X_Err_Text IN OUT NOCOPY VARCHAR2)
3996 RETURN INTEGER IS
3997
3998 CURSOR c_interface_row (cp_row_id IN ROWID)
3999 IS
4000 SELECT item_catalog_group_id,
4001 style_item_id,
4002 style_item_flag,
4003 gdsn_outbound_enabled_flag,
4004 inventory_item_id,
4005 organization_id,
4006 transaction_type,
4007 request_id,
4008 transaction_id
4009 FROM mtl_system_items_interface
4010 WHERE rowid = cp_row_id;
4011
4012 CURSOR c_master_org (cp_organization_id IN NUMBER)
4013 IS
4014 SELECT master_organization_id
4015 FROM mtl_parameters
4016 WHERE organization_id = cp_organization_id;
4017
4018 CURSOR c_get_existing_item_details (Cp_inventory_item_id IN NUMBER,
4019 Cp_organization_id IN NUMBER)
4020 IS
4021 SELECT style_item_flag,style_item_id,
4022 item_catalog_group_id
4023 FROM mtl_system_items_b
4024 WHERE inventory_item_id = cp_inventory_item_id
4025 AND organization_id = cp_organization_id;
4026
4027 l_user_id NUMBER := FND_GLOBAL.User_Id;
4028 l_login_id NUMBER := FND_GLOBAL.Login_Id;
4029 l_Prog_Appl_Id NUMBER := FND_GLOBAL.Prog_Appl_Id;
4030 l_conc_prog_id NUMBER := FND_GLOBAL.Conc_program_id;
4031
4032 l_msii_icc_id NUMBER;
4033 l_msi_icc_id NUMBER;
4034 l_msii_style_item_flag VARCHAR2(1);
4035 l_msii_style_item_id NUMBER;
4036 l_msii_inv_item_id NUMBER;
4037 l_msii_org_id NUMBER;
4038 l_transaction_type VARCHAR2(10);
4039 l_transaction_id NUMBER;
4040 l_request_id NUMBER;
4041 l_msi_style_item_flag VARCHAR2(1);
4042 l_msi_style_item_id NUMBER;
4043 l_error_logged NUMBER;
4044 l_err_text VARCHAR2(1000);
4045 l_valid_icc NUMBER;
4046 l_style_icc_id NUMBER;
4047 l_sku_variant BOOLEAN;
4048 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
4049 l_error_exists NUMBER := 0;
4050 l_null_icc_id NUMBER := -1;
4051 l_sku_exists NUMBER;
4052 l_master_org NUMBER;
4053 l_msii_gdsn_flag VARCHAR2(1);
4054 l_var_attrs_missing BOOLEAN;
4055
4056 BEGIN
4057 OPEN c_interface_row (cp_row_id => P_Row_Id);
4058 FETCH c_interface_row INTO l_msii_icc_id, l_msii_style_item_id,l_msii_style_item_flag,
4059 l_msii_gdsn_flag, l_msii_inv_item_id, l_msii_org_id,
4060 l_transaction_type, l_request_id, l_transaction_id;
4061 CLOSE c_interface_row;
4062
4063 OPEN c_master_org(cp_organization_id => l_msii_org_id);
4064 FETCH c_master_org INTO l_master_org;
4065 CLOSE c_master_org;
4066
4067 OPEN c_get_existing_item_details (Cp_inventory_item_id => l_msii_inv_item_id,
4068 Cp_organization_id => l_msii_org_id);
4069 FETCH c_get_existing_item_details INTO l_msi_style_item_flag,l_msi_style_item_id,l_msi_icc_id;
4070 CLOSE c_get_existing_item_details;
4071
4072 IF l_inv_debug_level IN(101, 102) THEN
4073 INVPUTLI.info('MSII ICC ID ' ||l_msii_icc_id ||' MSI ICC ID ' ||l_msi_icc_id);
4074 END IF;
4075
4076 IF l_msii_icc_id IS NULL THEN
4077 IF l_inv_debug_level IN(101, 102) THEN
4078 INVPUTLI.info('INVEGRVB.validate_style_sku: ICC is mandatory');
4079 END IF;
4080 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4081 l_User_Id,
4082 l_Login_Id,
4083 l_Prog_Appl_Id,
4084 l_Conc_prog_id,
4085 l_request_id,
4086 l_transaction_id,
4087 l_err_text,
4088 'ITEM_CATALOG_GROUP_ID',
4089 'MTL_SYSTEM_ITEMS_INTERFACE',
4090 'INV_STYLE_SKU_REQUIRED_ICC',
4091 X_Err_Text);
4092 l_error_exists := 1;
4093 END IF;
4094
4095 /* The Style/SKU status of the item cannot be updated - Sec 2.5.1 - Condition 1*/
4096 IF ((l_transaction_type = 'UPDATE') AND (l_msii_style_item_flag <> l_msi_style_item_flag))
4097 THEN
4098 IF l_inv_debug_level IN(101, 102) THEN
4099 INVPUTLI.info('INVEGRVB.validate_style_sku: Cannot update Style Item Flag ');
4100 END IF;
4101 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4102 l_User_Id,
4103 l_Login_Id,
4104 l_Prog_Appl_Id,
4105 l_Conc_prog_id,
4106 l_request_id,
4107 l_transaction_id,
4108 l_err_text,
4109 'STYLE_ITEM_FLAG',
4110 'MTL_SYSTEM_ITEMS_INTERFACE',
4111 'INV_STYLE_SKU_NOT_UPD',
4112 X_Err_Text);
4113 l_error_exists := 1;
4114 END IF;
4115
4116 /* The Style Item for a given SKU item cannot be updated - Sec 2.5.1 - Condition 1.1*/
4117 IF (l_transaction_type = 'UPDATE') AND (l_msii_style_item_id <> l_msi_style_item_id)
4118 THEN
4119 IF l_inv_debug_level IN(101, 102) THEN
4120 INVPUTLI.info('INVEGRVB.validate_style_sku: Cannot update Style Item Id ');
4121 END IF;
4122 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4123 l_User_Id,
4124 l_Login_Id,
4125 l_Prog_Appl_Id,
4126 l_Conc_prog_id,
4127 l_request_id,
4128 l_transaction_id,
4129 l_err_text,
4130 'STYLE_ITEM_FLAG',
4131 'MTL_SYSTEM_ITEMS_INTERFACE',
4132 'INV_STYLE_ID_NOT_UPD',
4133 X_Err_Text);
4134 l_error_exists := 1;
4135 END IF;
4136
4137 --Bug 6161263: Adding validation to disallow the creation/updation of GDSN Syndicated Style items
4138 IF (l_msii_style_item_flag = 'Y' AND l_msii_gdsn_flag = 'Y') THEN
4139 IF l_inv_debug_level IN(101, 102) THEN
4140 INVPUTLI.info('INVEGRVB.validate_style_sku: Style Item cannot be GDSN SYndicated ');
4141 END IF;
4142 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4143 l_User_Id,
4144 l_Login_Id,
4145 l_Prog_Appl_Id,
4146 l_Conc_prog_id,
4147 l_request_id,
4148 l_transaction_id,
4149 l_err_text,
4150 'STYLE_ITEM_FLAG',
4151 'MTL_SYSTEM_ITEMS_INTERFACE',
4152 'INV_STYLE_NOT_GDSN',
4153 X_Err_Text);
4154 l_error_exists := 1;
4155 END IF;
4156
4157 IF ((l_transaction_type = 'CREATE')OR
4158 (l_transaction_type = 'UPDATE' AND NVL(l_msi_icc_id,l_null_icc_id) <> NVL(l_msii_icc_id,l_null_icc_id)) )THEN
4159 /* Styles to be created in only those ICCs that contain Variant AGs - Sec 2.5.1 -Condition 2 */
4160 IF l_msii_style_item_flag = 'Y' THEN
4161 SELECT COUNT(*) INTO l_sku_exists
4162 FROM mtl_system_items_b
4163 WHERE style_item_id = l_msii_inv_item_id
4164 AND organization_id = l_msii_org_id;
4165
4166 IF l_sku_exists <> 0 THEN
4167 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4168 l_User_Id,
4169 l_Login_Id,
4170 l_Prog_Appl_Id,
4171 l_Conc_prog_id,
4172 l_request_id,
4173 l_transaction_id,
4174 l_err_text,
4175 'ITEM_CATALOG_GROUP_ID',
4176 'MTL_SYSTEM_ITEMS_INTERFACE',
4177 'INV_STYLE_ICC_SKU_EXISTS',
4178 X_Err_Text);
4179 l_error_exists := 1;
4180 ELSE
4181 -- Modifying query to take inherited attribute groups into account
4182 SELECT count(*) INTO l_valid_icc
4183 FROM ego_obj_attr_grp_assocs_v
4184 WHERE variant = 'Y'
4185 AND classification_code IN ( SELECT to_char(item_catalog_group_id)
4186 FROM mtl_item_catalog_groups_b
4187 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
4188 START WITH item_catalog_group_id = l_msii_icc_id );
4189
4190 IF l_valid_icc = 0 THEN
4191 IF l_inv_debug_level IN(101, 102) THEN
4192 INVPUTLI.info('INVEGRVB.validate_style_sku: Invalid ICC ');
4193 END IF;
4194 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4195 l_User_Id,
4196 l_Login_Id,
4197 l_Prog_Appl_Id,
4198 l_Conc_prog_id,
4199 l_request_id,
4200 l_transaction_id,
4201 l_err_text,
4202 'ITEM_CATALOG_GROUP_ID',
4203 'MTL_SYSTEM_ITEMS_INTERFACE',
4204 'INV_STYLE_INVALID_ICC',
4205 X_Err_Text);
4206 l_error_exists := 1;
4207 ELSE
4208 --Added condition to avoid entry twice, from INVNIRIB and INVPVALB
4209 IF ((l_transaction_type = 'CREATE') AND
4210 (p_xset_id < 3000000000000 AND p_xset_id <> 3000000000000-999) AND
4211 (l_msii_org_id = l_master_org) )THEN
4212 l_error_logged := EGO_STYLE_SKU_ITEM_PVT.Default_Style_Variant_Attrs(
4213 p_inventory_item_id => l_msii_inv_item_id,
4214 p_item_catalog_group_id => l_msii_icc_id,
4215 x_err_text => l_err_text );
4216 IF l_error_logged <> 0 THEN
4217 l_error_logged := INVPUOPI.mtl_log_interface_err(
4218 l_msii_org_id,
4219 l_User_Id,
4220 l_Login_Id,
4221 l_Prog_Appl_Id,
4222 l_Conc_prog_id,
4223 l_request_id,
4224 l_transaction_id,
4225 l_err_text,
4226 'ITEM_CATALOG_GROUP_ID',
4227 'MTL_SYSTEM_ITEMS_INTERFACE',
4228 'INV_IOI_ERR_DFT_STYLE_ATTRS',--bug 11894684, replace INV_IOI_ERR
4229 X_Err_Text);
4230 l_error_exists := 1;
4231 END IF; --Create
4232 END IF; --Valid ICC
4233 END IF; --SKU does not exist
4234 END IF; --Style Item
4235 ELSE
4236 BEGIN
4237 SELECT item_catalog_group_id INTO l_style_icc_id
4238 FROM mtl_system_items_b
4239 WHERE inventory_item_id = l_msii_style_item_id
4240 AND organization_id = l_msii_org_id;
4241 EXCEPTION
4242 WHEN no_data_found THEN
4243 Null;
4244 END;
4245 /* SKUs and their corresponding Styles must belong to the same ICC - Sec 2.5.1 -Condition 3 */
4246 IF l_style_icc_id IS NULL OR l_style_icc_id <> l_msii_icc_id THEN
4247 IF l_inv_debug_level IN(101, 102) THEN
4248 INVPUTLI.info('INVEGRVB.validate_style_sku: SKU and Style must belong to same ICC ');
4249 END IF;
4250 l_error_logged := INVPUOPI.mtl_log_interface_err (l_msii_org_id,
4251 l_User_Id,
4252 l_Login_Id,
4253 l_Prog_Appl_Id,
4254 l_Conc_prog_id,
4255 l_request_id,
4256 l_transaction_id,
4257 l_err_text,
4258 'ITEM_CATALOG_GROUP_ID',
4259 'MTL_SYSTEM_ITEMS_INTERFACE',
4260 'INV_SKU_INVALID_ICC',
4261 X_Err_Text);
4262 l_error_exists := 1;
4263 END IF;
4264 END IF;
4265 END IF;
4266
4267 IF (l_error_exists <> 1 AND l_msii_style_item_flag = 'N' AND
4268 (l_transaction_type = 'CREATE'
4269 OR (l_transaction_type = 'UPDATE' AND l_msii_style_item_flag = 'N' AND l_msi_style_item_flag IS NULL) --bug 6345529
4270 )
4271 AND l_msii_org_id = l_master_org )THEN
4272 /* The Variant attribute combination for SKU items must be unique - Sec 2.5.1 -Condition 8 */
4273 --Added condition to avoid entry twice, from INVNIRIB and INVPVALB
4274 IF (p_xset_id < 3000000000000 AND p_xset_id <> 3000000000000-999) THEN
4275 l_error_logged := EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage (
4276 p_intf_row_id => P_Row_Id
4277 ,x_sku_exists => l_sku_variant
4278 ,x_err_text => l_err_text
4279 ,x_var_attrs_missing => l_var_attrs_missing);
4280 IF l_sku_variant THEN
4281 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4282 l_User_Id,
4283 l_Login_Id,
4284 l_Prog_Appl_Id,
4285 l_Conc_prog_id,
4286 l_request_id,
4287 l_transaction_id,
4288 l_err_text,
4289 'VARIANT_ATTRIBUTE_COMB',
4290 'MTL_SYSTEM_ITEMS_INTERFACE',
4291 'INV_SKU_VAR_NO_UNIQUE',
4292 X_Err_Text);
4293 l_error_exists := 1;
4294 ELSIF l_error_logged <> 0 THEN
4295 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4296 l_User_Id,
4297 l_Login_Id,
4298 l_Prog_Appl_Id,
4299 l_Conc_prog_id,
4300 l_request_id,
4301 l_transaction_id,
4302 l_err_text,
4303 'VARIANT_ATTRIBUTE_COMB',
4304 'MTL_SYSTEM_ITEMS_INTERFACE',
4305 'INV_IOI_ERR_VLD_SKU_USAGE',--bug 11894684, replace INV_IOI_ERR
4306 X_Err_Text);
4307 l_error_exists := 1;
4308 ELSIF l_var_attrs_missing = TRUE THEN
4309 l_error_logged := INVPUOPI.mtl_log_interface_err( l_msii_org_id,
4310 l_User_Id,
4311 l_Login_Id,
4312 l_Prog_Appl_Id,
4313 l_Conc_prog_id,
4314 l_request_id,
4315 l_transaction_id,
4316 l_err_text,
4317 'VARIANT_ATTRIBUTE_COMB',
4318 'MTL_SYSTEM_ITEMS_INTERFACE',
4319 'INV_SKU_VAR_ATTR_MISSING',
4320 X_Err_Text);
4321 l_error_exists := 1;
4322 END IF;
4323 END IF;
4324 END IF;
4325
4326 IF l_error_exists = 1 THEN
4327 UPDATE mtl_system_items_interface
4328 SET process_flag = 3
4329 WHERE rowid = p_row_id;
4330 END IF;
4331
4332 RETURN(l_error_exists);
4333
4334 EXCEPTION
4335 WHEN others THEN
4336 INVPUTLI.info('INVEGRVB.validate_style_sku: Exception '||SQLERRM);
4337 RETURN(SQLCODE);
4338
4339 END validate_style_sku;
4340
4341 -- ============================================================================
4342 -- API Name : Check_Org_Access
4343 -- Description : This procedure will be called from IOI to check if org_access_view
4344 -- has this org
4345 -- ============================================================================
4346 FUNCTION Check_Org_Access (p_org_id IN NUMBER)
4347 RETURN VARCHAR2 IS
4348
4349 CURSOR c_check_org_access (p_org_id IN NUMBER) IS
4350 SELECT 'X'
4351 FROM ORG_ACCESS_VIEW
4352 WHERE ORGANIZATION_ID = p_org_id
4353 AND RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
4354 AND RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID;
4355
4356 l_has_access VARCHAR2(1) := 'T';
4357 l_exists VARCHAR2(1);
4358
4359 BEGIN
4360
4361 OPEN c_check_org_access(p_org_id);
4362 FETCH c_check_org_access INTO l_exists;
4363 IF (c_check_org_access%NOTFOUND ) THEN
4364 l_has_access := 'F';
4365 END IF;
4366 CLOSE c_check_org_access;
4367
4368 RETURN(l_has_access);
4369
4370 EXCEPTION WHEN OTHERS THEN
4371 IF c_check_org_access%ISOPEN THEN
4372 CLOSE c_check_org_access;
4373 END IF;
4374 INVPUTLI.info('INVEGRVB.Check_Org_Access '||SQLERRM);
4375 RETURN(SQLCODE);
4376
4377 END Check_Org_Access;
4378
4379 END INV_EGO_REVISION_VALIDATE;