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