[Home] [Help]
PACKAGE BODY: APPS.INVPVALI
Source
1 PACKAGE BODY INVPVALI AS
2 /* $Header: INVPVALB.pls 120.14 2011/06/13 09:28:43 erwu ship $ */
3
4 ------------------------ validate_item_revs -----------------------------------
5
6 function validate_item_revs
7 (
8 org_id number,
9 all_org NUMBER := 2,
10 prog_appid NUMBER := -1,
11 prog_id NUMBER := -1,
12 request_id NUMBER := -1,
13 user_id NUMBER := -1,
14 login_id NUMBER := -1,
15 err_text in out NOCOPY varchar2,
16 xset_id IN NUMBER DEFAULT -999
17 )
18 return integer
19 is
20
21 /*
22 ??? ** we have already validate org_id, catg_set_id and catg_id
23 ** in assign function. we only validate item_id here
24 */
25
26 /*
27 ** any row that does not have a corresponding item row in prod or
28 ** inteface table must be flagged as error
29 */
30
31 CURSOR cc is
32 select i.transaction_id,
33 i.transaction_type,
34 i.inventory_item_id,
35 i.organization_id ,
36 i.revision,
37 rowid
38 from mtl_item_revisions_interface i
39 where i.process_flag = 2
40 and i.set_process_id = xset_id
41 and (i.organization_id = org_id or all_org = 1)
42 and not exists (select 'X'
43 from mtl_system_items m
44 where m.organization_id = i.organization_id
45 and m.inventory_item_id = i.inventory_item_id)
46 and not exists (select 'X'
47 from mtl_system_items_interface mi
48 where mi.organization_id = i.organization_id
49 and mi.inventory_item_id = i.inventory_item_id
50 and process_flag = 4);
51
52 /*
53 ** We are going to check for the validity against the unique index
54 ** ORGANIZATION_ID,REVISION and INVENTORY_ITEM_ID
55 */
56 /*
57 ** if the item-revision combination already exists in production, then
58 ** mark it as an error
59 */
60 /*
61 Bug 1725851 : Removed + 0 on Set Process Id as it was causing
62 Performance issues. Removing this uses Index
63 mtl_item_revs_interface_N3 index instead of FULL Table Scan.
64 Also removed mtl_parameters as it is not required here
65 */
66 --User can now populate revision_id in the interface table.
67 CURSOR dd is
68 select transaction_id,
69 transaction_type,
70 organization_id,
71 inventory_item_id,
72 revision,
73 revision_id,
74 rowid
75 from mtl_item_revisions_interface
76 where set_process_id = xset_id
77 and process_flag = 2;
78 --2808277 : Above validation applicable to only CREATE
79 --Above cursor modified to check revision id uniqueness
80
81 --Start: Check for data security and user privileges
82 --6318972:Privledge check missing on Rev creation
83 CURSOR c_get_rev_item is
84 select i.rowid,
85 i.organization_id,
86 i.inventory_item_id,
87 i.transaction_id,
88 i.created_by
89 from mtl_item_revisions_interface i
90 where i.set_process_id = xset_id
91 and i.process_flag = 2
92 and (i.transaction_type = 'UPDATE'
93 or exists (select null
94 from mtl_system_items_b m
95 where m.organization_id = i.organization_id
96 and m.inventory_item_id = i.inventory_item_id));
97 --End: Check for data security and user privileges
98 /*
99 ** if there are duplicate rows in interface table for item-rev
100 ** combination, mark as error
101 */
102 /* Bug 1725851
103 Rewriting the below cursor to avoid performance issue
104 */
105 CURSOR ee is
106 select m.transaction_id,
107 m.transaction_type,
108 m.organization_id,
109 m.inventory_item_id,
110 m.revision,
111 m.rowid
112 from mtl_item_revisions_interface m
113 where m.set_process_id = xset_id
114 and m.process_flag = 2;
115
116 /*
117 ** item revs must be in alphanumeric and chronological order must check against
118 ** the interface table AND the database table
119 */
120 /* Bug 1725851
121 Rewriting the below cursor to avoid performance issue
122 2806275 : Revision Reason validation
123 */
124 CURSOR ff is
125 select m.transaction_id,
126 m.transaction_type,
127 m.organization_id,
128 m.inventory_item_id,
129 m.revision,
130 m.revision_id,
131 m.effectivity_date,
132 m.ecn_initiation_date,
133 m.implementation_date,
134 m.revision_reason
135 from mtl_item_revisions_interface m
136 where m.set_process_id = xset_id
137 and m.process_flag = 2;
138
139 /*NP 07SEP94 In cursor gg added an or clause here
140 **effectivity date being less or equal to current
141 **effective date..ie an invalid condition
142 **NP 21DEC95 removed check on past effectivity dates.
143 **They are allowed now.
144 */
145 -- Bug 4299292. Use base table mtl_item_revisions_b to fix performance issue
146 CURSOR gg is
147 select i.transaction_id,
148 i.organization_id,
149 i.rowid
150 from mtl_item_revisions_b m,
151 mtl_item_revisions_interface i
152 where m.organization_id = i.organization_id
153 and i.set_process_id = xset_id
154 and m.inventory_item_id = i.inventory_item_id
155 and ( (m.revision < i.revision and m.effectivity_date >=
156 i.effectivity_date)
157 or (i.revision < m.revision and i.effectivity_date >=
158 m.effectivity_date)
159 )
160 and i.process_flag = 2;
161 --3569925 : Added = condition for > on effectivity dates.
162
163 /*
164 ** item revs life cycle and phases validation
165 */
166
167 CURSOR c_get_revision_lifecycle IS
168 SELECT rowid,
169 inventory_item_id,
170 organization_id,
171 lifecycle_id,
172 current_phase_id,
173 transaction_id,
174 transaction_type,
175 revision,
176 revision_id
177 FROM mtl_item_revisions_interface i
178 WHERE set_process_id = xset_id
179 AND process_flag = 2
180 FOR UPDATE OF current_phase_id NOWAIT;
181
182 --3059993:Revision create should honour items phase policy
183 --Changes added lifecycle,phase ids in the select list.
184 CURSOR c_get_item_ids(cp_org_id NUMBER,
185 cp_item_id NUMBER)
186 IS
187 SELECT mi.item_catalog_group_id
188 ,mi.lifecycle_id
189 ,mi.current_phase_id
190 ,'U'
191 ,mi.transaction_type
192 FROM mtl_system_items_interface mi
193 WHERE mi.organization_id = cp_org_id
194 AND mi.inventory_item_id = cp_item_id
195 AND mi.process_flag = 4
196 UNION
197 SELECT m.item_catalog_group_id
198 ,m.lifecycle_id
199 ,m.current_phase_id
200 ,NVL(m.approval_status,'A')
201 ,'EXISTS'
202 FROM mtl_system_items_b m
203 WHERE m.organization_id = cp_org_id
204 AND m.inventory_item_id = cp_item_id;
205
206
207 --2806275 : Revision Reason validation
208 CURSOR c_check_lookup (cp_type fnd_lookup_values_vl.lookup_type%TYPE,
209 cp_code fnd_lookup_values_vl.lookup_code%TYPE)
210 IS
211 SELECT 'Y'
212 FROM fnd_lookup_values_vl
213 WHERE lookup_type = cp_type
214 AND lookup_code = cp_code
215 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
216 AND enabled_flag = 'Y';
217
218 --2885843: default revision error propagated to imported item
219 CURSOR c_get_default_rev(cp_org_id NUMBER)
220 IS
221 select starting_revision
222 from mtl_parameters
223 where organization_id = cp_org_id;
224
225 CURSOR is_gdsn_batch(cp_xset_id NUMBER) IS
226 SELECT 1 FROM ego_import_option_sets
227 WHERE batch_id = cp_xset_id
228 AND enabled_for_data_pool = 'Y';
229
230 status number;
231 error_msg varchar2(70);
232 l_process_flag_2 number := 2 ;
233 l_process_flag_3 number := 3 ;
234 l_process_flag_4 number := 4 ;
235 l_all_org number := 1 ;
236 temp_count number := 0;
237 LOGGING_ERR exception;
238 l_item_catalog NUMBER;
239 l_lookup_exist VARCHAR2(1):='N';
240 l_default_rev VARCHAR2(3);
241 l_lifecycle_error BOOLEAN := FALSE;
242
243 --2808277: Update validations for Lifecycle-Phase
244 l_row_count NUMBER(3) := 0;
245 l_Old_Phase_Id mtl_item_revisions_b.current_phase_id%TYPE;
246 l_Policy_Code VARCHAR2(20);
247 l_Return_Status VARCHAR2(1);
248 l_Error_Code NUMBER;
249 l_Msg_Count NUMBER;
250 l_Msg_Data VARCHAR2(2000);
251 l_has_privilege VARCHAR2(1) := 'F';
252
253 --Start:3059993:Revision create should honour items phase policy
254 l_item_phase_id mtl_item_revisions_b.current_phase_id%TYPE;
255 l_item_lifecycle_id mtl_item_revisions_b.lifecycle_id%TYPE;
256 --End:3059993:Revision create should honour items phase policy
257 l_revision_id mtl_item_revisions_interface.revision_id%TYPE;
258 l_revid_error BOOLEAN := FALSE;
259
260 l_item_approved mtl_system_items_b.approval_status%TYPE := NULL;
261 l_item_trans_type mtl_system_items_interface.transaction_type%TYPE := NULL;
262
263 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
264 l_process_control VARCHAR2(2000) := NULL; --Used by EGO API only for internal flow control
265 l_is_gdsn_batch NUMBER;
266
267 /* Bug 7513461*/
268 currval_not_def exception;
269 pragma exception_init(currval_not_def, -8002);
270
271 begin
272
273 l_process_control := INV_EGO_REVISION_VALIDATE.Get_Process_Control;
274
275 for cr in cc loop
276 update mtl_item_revisions_interface
277 set process_flag = l_process_flag_3
278 where rowid = cr.rowid ;
279
280 status := INVPUOPI.mtl_log_interface_err(
281 cr.organization_id,
282 user_id,
283 login_id,
284 prog_appid,
285 prog_id,
286 request_id,
287 cr.TRANSACTION_ID,
288 error_msg,
289 'ITEM_ID',
290 'MTL_ITEM_REVISIONS_INTERFACE',
291 'INV_IOI_REV_NO_ITEM',
292 err_text);
293 if status < 0 then
294 raise LOGGING_ERR;
295 end if;
296 end loop;
297
298 --Start User can now populate revision_id in the interface table
299 for cr in dd loop
300 l_revid_error := FALSE;
301 IF cr.transaction_type ='CREATE' AND cr.revision_id IS NOT NULL THEN
302 /* Bug 7513461*/
303 BEGIN
304 SELECT MTL_ITEM_REVISIONS_B_S.CURRVAL
305 INTO l_revision_id FROM DUAL;
306 EXCEPTION
307 WHEN currval_not_def THEN
308 SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL
309 INTO l_revision_id FROM DUAL;
310 WHEN OTHERS THEN
311 l_revision_id := cr.revision_id - 1;
312 END;
313
314 IF cr.revision_id > l_revision_id THEN
315 l_revid_error := TRUE;
316 END IF;
317
318 IF l_revid_error THEN
319 update mtl_item_revisions_interface
320 set process_flag = l_process_flag_3
321 where rowid = cr.rowid;
322
323 status := INVPUOPI.mtl_log_interface_err(
324 cr.organization_id,
325 user_id,
326 login_id,
327 prog_appid,
328 prog_id,
329 request_id,
330 cr.TRANSACTION_ID,
331 error_msg,
332 'REVISION_ID',
333 'MTL_ITEM_REVISIONS_INTERFACE',
334 'INV_IOI_INVALID_REVISION_ID',
335 err_text);
336 if status < 0 then
337 raise LOGGING_ERR;
338 end if;
339 END IF;
340
341 END IF;
342
343 --Start :3456560 Revision code validation.
344 IF cr.transaction_type ='CREATE'
345 AND cr.revision IS NULL THEN
346 l_revid_error := TRUE;
347 ELSIF cr.transaction_type ='UPDATE'
348 AND cr.revision IS NULL
349 AND cr.revision_id IS NULL THEN
350 l_revid_error := TRUE;
351 END IF;
352 IF l_revid_error THEN
353 update mtl_item_revisions_interface
354 set process_flag = l_process_flag_3
355 where rowid = cr.rowid;
356 status := INVPUOPI.mtl_log_interface_err(
357 cr.organization_id,
358 user_id,
359 login_id,
360 prog_appid,
361 prog_id,
362 request_id,
363 cr.TRANSACTION_ID,
364 error_msg,
365 'REVISION',
366 'MTL_ITEM_REVISIONS_INTERFACE',
367 'INV_IOI_INVALID_REVISION',
368 err_text);
369 if status < 0 then
370 raise LOGGING_ERR;
371 end if;
372 END IF;
373 --End :3456560 Revision code validation.
374
375 SELECT count(1) INTO l_row_count
376 FROM mtl_item_revisions_b
377 WHERE organization_id = cr.organization_id
378 AND inventory_item_id = cr.inventory_item_id
379 AND (revision = cr.revision OR revision_id = cr.revision_id);
380
381 IF cr.transaction_type ='CREATE' AND l_row_count > 0 THEN
382
383 update mtl_item_revisions_interface
384 set process_flag = l_process_flag_3
385 where rowid = cr.rowid;
386
387 status := INVPUOPI.mtl_log_interface_err(
388 cr.organization_id,
389 user_id,
390 login_id,
391 prog_appid,
392 prog_id,
393 request_id,
394 cr.TRANSACTION_ID,
395 error_msg,
396 'REVISION',
397 'MTL_ITEM_REVISIONS_INTERFACE',
398 'INV_IOI_REV_DUP_2',
399 err_text);
400 if status < 0 then
401 raise LOGGING_ERR;
402 end if;
403 ELSIF cr.transaction_type ='UPDATE' AND l_row_count = 0 THEN
404 update mtl_item_revisions_interface
405 set process_flag = l_process_flag_3
406 where rowid = cr.rowid;
407 status := INVPUOPI.mtl_log_interface_err(
408 cr.organization_id,
409 user_id,
410 login_id,
411 prog_appid,
412 prog_id,
413 request_id,
414 cr.TRANSACTION_ID,
415 error_msg,
416 'REVISION',
417 'MTL_ITEM_REVISIONS_INTERFACE',
418 'INV_IOI_REV_NO_ITEM',
419 err_text);
420 if status < 0 then
421 raise LOGGING_ERR;
422 end if;
423 END IF;
424
425 end loop;
426 --End User can now populate revision_id in the interface table
427
428 --Start : Check for data security and user privileges
429 -- Bug 4538382 - NOT chk when control is from PLM:UI for perf
430 -- Bug 5218491 - Modified the IF clause : Changed <> to = in the below check
431 IF (INSTR(NVL(l_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 )
432 THEN
433 for cr in c_get_rev_item loop
434 --Fix for bug# 3032994. Changed the privilege to check from
435 --EGO_EDIT_ITEM to EGO_ADD_ITEM_REVISION
436 IF ( cr.created_by <> -99 ) THEN
437 l_has_privilege := INV_EGO_REVISION_VALIDATE.check_data_security(
438 p_function => 'EGO_ADD_ITEM_REVISION'
439 ,p_object_name => 'EGO_ITEM'
440 ,p_instance_pk1_value => cr.inventory_item_id
441 ,p_instance_pk2_value => cr.organization_id
442 ,P_User_Id => user_id);
443
444 IF l_has_privilege <> 'T' THEN
445 update mtl_item_revisions_interface
446 set process_flag = l_process_flag_3
447 where rowid = cr.rowid;
448
449 status := INVPUOPI.mtl_log_interface_err(
450 cr.organization_id,
451 user_id,
452 login_id,
453 prog_appid,
454 prog_id,
455 request_id,
456 cr.TRANSACTION_ID,
457 error_msg,
458 'INVENTORY_ITEM_ID',
459 'MTL_ITEM_REVISIONS_INTERFACE',
460 'INV_IOI_ITEMREV_UPDATE_PRIV',
461 err_text);
462 if status < 0 then
463 raise LOGGING_ERR;
464 end if;
465 END IF; --has privilege
466 ELSE
467 IF l_inv_debug_level IN(101, 102) THEN
468 INVPUTLI.info('INVPVALI.Security skipped for Item Org:' || cr.inventory_item_id || '-' || cr.organization_id );
469 END IF;
470 END IF; --created_by
471
472 end loop;
473 END IF;
474 --End : Check for data security and user privileges
475
476 for cr in ee loop
477
478 select count(*)
479 into temp_count
480 from mtl_item_revisions_interface i
481 where i.organization_id = cr.organization_id
482 and i.inventory_item_id = cr.inventory_item_id
483 -- and i.set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
484 and i.set_process_id = xset_id
485 and i.revision = cr.revision
486 --and i.transaction_id = cr.transaction_id --2808277 Removed for bug 5458317
487 and i.process_flag = 2;
488
489 if temp_count > 1 then
490
491 --Bypassing validation for GDSN batches
492 l_is_gdsn_batch := 0;
493 Open is_gdsn_batch(xset_id);
494 Fetch is_gdsn_batch INTO l_is_gdsn_batch;
495 Close is_gdsn_batch;
496
497 if l_is_gdsn_batch <> 1 then
498
499 update mtl_item_revisions_interface
500 set process_flag = l_process_flag_3
501 where transaction_id = cr.transaction_id
502 and set_process_id = xset_id
503 and revision = cr.revision;
504
505 -- R12C The duplicate Item-Org combination is already reported in INVPVHDR
506 /* 2885843: Start default revision error propagated to imported item
507
508 OPEN c_get_default_rev(cp_org_id => cr.organization_id);
509 FETCH c_get_default_rev INTO l_default_rev;
510 CLOSE c_get_default_rev;
511
512 IF cr.transaction_type ='CREATE'
513 AND l_default_rev = cr.revision THEN
514
515 UPDATE mtl_system_items_interface
516 SET process_flag = l_process_flag_3
517 WHERE inventory_item_id = cr.inventory_item_id
518 AND organization_id = cr.organization_id
519 AND set_process_id = xset_id;
520
521 END IF;
522 2885843: End default revision error propagated to imported item */
523
524 status := INVPUOPI.mtl_log_interface_err(
525 cr.organization_id,
526 user_id,
527 login_id,
528 prog_appid,
529 prog_id,
530 request_id,
531 cr.TRANSACTION_ID,
532 error_msg,
533 'DUP2',
534 'MTL_ITEM_REVISIONS_INTERFACE',
535 'INV_IOI_REV_DUP_1',
536 err_text);
537 if status < 0 then
538 raise LOGGING_ERR;
539 end if;
540 end if; --Is not GDSN Batch
541 end if;
542 -- Bug 4538382 - NOT chk when control is from PLM:UI for perf
543 -- Bug 5218491 - Modified the IF clause : Changed <> to = in the below check
544 IF (INSTR(NVL(l_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 )
545 THEN
546 --Start: 3059993:Revision create should honour items phase policy
547 IF cr.transaction_type ='CREATE' THEN
548
549 OPEN c_get_default_rev(cp_org_id => cr.organization_id);
550 FETCH c_get_default_rev INTO l_default_rev;
551 CLOSE c_get_default_rev;
552
553 IF l_default_rev <> cr.revision THEN
554
555 OPEN c_get_item_ids(cp_org_id => cr.organization_id,
556 cp_item_id => cr.inventory_item_id);
557 FETCH c_get_item_ids
558 INTO l_item_catalog
559 ,l_item_lifecycle_id
560 ,l_item_phase_id
561 ,l_item_approved
562 ,l_item_trans_type;
563
564 CLOSE c_get_item_ids;
565
566 IF l_item_lifecycle_id IS NOT NULL AND l_item_phase_id IS NOT NULL THEN
567 INV_EGO_REVISION_VALIDATE.phase_change_policy
568 (P_ORGANIZATION_ID => cr.organization_id
569 ,P_INVENTORY_ITEM_ID => cr.inventory_item_id
570 ,P_CURR_PHASE_ID => l_item_phase_id
571 ,P_FUTURE_PHASE_ID => NULL
572 ,P_PHASE_CHANGE_CODE => 'REVISE'
573 ,P_LIFECYCLE_ID => l_item_lifecycle_id
574 ,X_POLICY_CODE => l_Policy_Code
575 ,X_RETURN_STATUS => l_Return_Status
576 ,X_ERRORCODE => l_Error_Code
577 ,X_MSG_COUNT => l_Msg_Count
578 ,X_MSG_DATA => l_Msg_Data);
579
580 IF l_Policy_Code <> 'ALLOWED' THEN
581
582 UPDATE mtl_item_revisions_interface
583 SET process_flag = l_process_flag_3
584 WHERE rowid = cr.rowid;
585 status := INVPUOPI.mtl_log_interface_err(
586 cr.organization_id,
587 user_id,
588 login_id,
589 prog_appid,
590 prog_id,
591 request_id,
592 cr.TRANSACTION_ID,
593 error_msg,
594 'TRANSACTION_TYPE',
595 'MTL_ITEM_REVISIONS_INTERFACE',
596 'INV_IOI_REV_PHASE_CONFLICT',
597 err_text);
598 if status < 0 then
599 raise LOGGING_ERR;
600 end if;
601
602 END IF; -- l_Policy_Code <> 'ALLOWED'
603 END IF;
604
605 --Revisions cannot be created for unapproved items.
606 --We allow only default revision
607 IF (l_item_trans_type = 'EXISTS' AND l_item_approved <> 'A')
608 OR (l_item_trans_type = 'CREATE' AND INVIDIT3.CHECK_NPR_CATALOG(l_item_catalog))
609 THEN
610 UPDATE mtl_item_revisions_interface
611 SET process_flag = l_process_flag_3
612 WHERE rowid = cr.rowid;
613 status := INVPUOPI.mtl_log_interface_err(
614 cr.organization_id,
615 user_id,
616 login_id,
617 prog_appid,
618 prog_id,
619 request_id,
620 cr.TRANSACTION_ID,
621 error_msg,
622 'TRANSACTION_TYPE',
623 'MTL_ITEM_REVISIONS_INTERFACE',
624 'INV_IOI_UNAPPROVED_ITEM_REV',
625 err_text);
626 if status < 0 then
627 raise LOGGING_ERR;
628 end if;
629 END IF;
630
631 END IF; --l_default_rev <> cr.revision
632 END IF;
633 END IF;
634 --End:3059993:Revision create should honour items phase policy
635
636 end loop;
637
638 for cr in ff loop
639
640 --2808277: Included trans id , create/update will run under different trans id.
641 -- Cannot update past/current effective dates.
642 IF (cr.transaction_type='UPDATE') THEN
643
644 l_row_count:= 0;
645
646 SELECT count(1) INTO l_row_count
647 FROM mtl_item_revisions_b
648 WHERE revision_id = cr.revision_id
649 AND TRUNC(effectivity_date)<= TRUNC(sysdate)
650 AND TRUNC(effectivity_date) <> TRUNC(cr.effectivity_date);
651
652 IF (l_row_count > 0) THEN
653 update mtl_item_revisions_interface
654 set process_flag = l_process_flag_3
655 where transaction_id = cr.transaction_id
656 and set_process_id = xset_id
657 and revision = cr.revision;--Bug: 2593490
658
659 status := INVPUOPI.mtl_log_interface_err(
660 cr.organization_id,
661 user_id,
662 login_id,
663 prog_appid,
664 prog_id,
665 request_id,
666 cr.TRANSACTION_ID,
667 error_msg,
668 'REVISION',
669 'MTL_ITEM_REVISIONS_INTERFACE',
670 'INV_REV_DATE_CHANGE_NOTALLOWED',
671 err_text);
672 if status < 0 then
673 raise LOGGING_ERR;
674 end if;
675 END IF;
676
677 --3070781:ECO Rev's update allow on description and rev label.
678 SELECT COUNT(1) INTO l_row_count
679 FROM mtl_item_revisions_b
680 where revision_id = cr.revision_id
681 AND change_notice IS NOT NULL
682 --12555926: revise the NULL checking for ecn_initiation_date, effectivity_date and implementation_date
683 AND (
684 (cr.ecn_initiation_date IS NOT NULL AND ecn_initiation_date <> cr.ecn_initiation_date) OR
685 (cr.effectivity_date IS NOT NULL AND effectivity_date <> cr.effectivity_date) OR
686 (cr.implementation_date IS NOT NULL AND implementation_date <> cr.implementation_date)
687 );
688 --12555926: end
689
690 IF (l_row_count > 0) THEN
691
692 update mtl_item_revisions_interface
693 set process_flag = l_process_flag_3
694 where transaction_id = cr.transaction_id
695 and set_process_id = xset_id
696 and revision = cr.revision;--Bug: 2593490
697
698 status := INVPUOPI.mtl_log_interface_err(
699 cr.organization_id,
700 user_id,
701 login_id,
702 prog_appid,
703 prog_id,
704 request_id,
705 cr.TRANSACTION_ID,
706 error_msg,
707 'REVISION',
708 'MTL_ITEM_REVISIONS_INTERFACE',
709 'INV_REV_ECO_CHANGE_NOTALLOWED',
710 err_text);
711 if status < 0 then
712 raise LOGGING_ERR;
713 end if;
714 END IF;
715
716
717 END IF;
718
719 select count(*)
720 into temp_count
721 from mtl_item_revisions_interface i
722 where i.organization_id = cr.organization_id
723 and i.inventory_item_id = cr.inventory_item_id
724 -- and i.set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
725 and i.set_process_id = xset_id
726 --and i.transaction_id = cr.transaction_id Commented for bug 5458317
727 and ((i.revision < cr.revision)
728 AND ((TRUNC(i.effectivity_date) = TRUNC(SYSDATE)
729 AND TRUNC(cr.effectivity_date) = TRUNC(SYSDATE)
730 AND i.effectivity_date = cr.effectivity_date)
731 OR(i.effectivity_date >= cr.effectivity_date)))
732 --2861248 : Effective date validation changed
733 --3569925 : Added = condition for > on effectivity dates.
734 /**Bug: 2593490 No need to check with greater revisions
735 or
736 ( cr.revision < i.revision and cr.effectivity_date > i.effectivity_date)
737 ***/
738 and i.process_flag = 2 ;
739
740 IF temp_count >= 1 THEN
741
742 update mtl_item_revisions_interface
743 set process_flag = l_process_flag_3
744 where transaction_id = cr.transaction_id
745 and set_process_id = xset_id
746 and revision = cr.revision;--Bug: 2593490
747
748 IF l_inv_debug_level IN(101, 102) THEN
749 INVPUTLI.info('INVPVALI.validate_item_revs: validation error: conflict with MIRI');
750 END IF;
751 status := INVPUOPI.mtl_log_interface_err(
752 cr.organization_id,
753 user_id,
754 login_id,
755 prog_appid,
756 prog_id,
757 request_id,
758 cr.TRANSACTION_ID,
759 error_msg,
760 'EFF1',
761 'MTL_ITEM_REVISIONS_INTERFACE',
762 'INV_IOI_REV_BAD_ORDER',
763 err_text);
764 if status < 0 then
765 raise LOGGING_ERR;
766 end if;
767 end if; -- If temp_count >=1
768
769 --Start 2806275 : Revision Reason validation
770 IF cr.revision_reason IS NOT NULL THEN
771
772 l_lookup_exist := 'N';
773
774 OPEN c_check_lookup(cp_type => 'EGO_ITEM_REVISION_REASON',
775 cp_code => cr.revision_reason);
776 FETCH c_check_lookup INTO l_lookup_exist;
777 CLOSE c_check_lookup;
778
779 IF l_lookup_exist <> 'Y' THEN
780
781 update mtl_item_revisions_interface
782 set process_flag = l_process_flag_3
783 where transaction_id = cr.transaction_id
784 and set_process_id = xset_id
785 and revision = cr.revision;
786
787 --2885843: Start default revision error propagated to imported item
788 OPEN c_get_default_rev(cp_org_id => cr.organization_id);
789 FETCH c_get_default_rev INTO l_default_rev;
790 CLOSE c_get_default_rev;
791
792 IF cr.transaction_type ='CREATE'
793 AND l_default_rev = cr.revision THEN
794
795 UPDATE mtl_system_items_interface
796 SET process_flag = l_process_flag_3
797 WHERE inventory_item_id = cr.inventory_item_id
798 AND organization_id = cr.organization_id
799 AND set_process_id = xset_id;
800
801 END IF;
802 --2885843: End default revision error propagated to imported item
803
804 status := INVPUOPI.mtl_log_interface_err(
805 cr.organization_id,
806 user_id,
807 login_id,
808 prog_appid,
809 prog_id,
810 request_id,
811 cr.TRANSACTION_ID,
812 error_msg,
813 'REVISION_REASON',
814 'MTL_ITEM_REVISIONS_INTERFACE',
815 'INV_IOI_INVALID_REV_REASON',
816 err_text);
817
818 IF status < 0 THEN
819 raise LOGGING_ERR;
820 END IF;
821
822 END IF; -- l_lookup_exist != 'Y'
823 END IF;
824 -- End 2806275 : Revision Reason validation
825
826 end loop;
827
828 for cr in gg loop
829
830 update mtl_item_revisions_interface
831 set process_flag = l_process_flag_3
832 where rowid = cr.rowid;
833
834 IF l_inv_debug_level IN(101, 102) THEN
835 INVPUTLI.info('INVPVALI.validate_item_revs: validation error: conflict with MIR');
836 END IF;
837
838 status := INVPUOPI.mtl_log_interface_err(
839 cr.organization_id,
840 user_id,
841 login_id,
842 prog_appid,
843 prog_id,
844 request_id,
845 cr.TRANSACTION_ID,
846 error_msg,
847 'EFF2',
848 'MTL_ITEM_REVISIONS_INTERFACE',
849 'INV_IOI_REV_BAD_ORDER',
850 err_text);
851
852 if status < 0 then
853 raise LOGGING_ERR;
854 end if;
855
856 end loop;
857
858 FOR cr in c_get_revision_lifecycle LOOP
859 l_lifecycle_error := FALSE;
860
861 --2808277: Start Revision update changes
862 IF cr.transaction_type = 'UPDATE' THEN
863 SELECT current_phase_id
864 INTO l_Old_Phase_Id
865 FROM mtl_item_revisions_b
866 WHERE revision_id = cr.revision_id;
867 END IF;
868 --2808277: End Revision update changes
869
870 -- Bug: 3769153 -- added OR part
871 IF cr.lifecycle_id IS NOT NULL OR cr.current_phase_id IS NOT NULL THEN
872
873 OPEN c_get_item_ids(cp_org_id => cr.organization_id,
874 cp_item_id => cr.inventory_item_id);
875 FETCH c_get_item_ids
876 INTO l_item_catalog
877 ,l_item_lifecycle_id
878 ,l_item_phase_id
879 ,l_item_approved
880 ,l_item_trans_type;
881 CLOSE c_get_item_ids;
882
883 -- Bug: 3769153 - if lifecycle is not specified and phase is specified,
884 -- then update the lifecycle of item to the revision
885 IF cr.lifecycle_id IS NULL AND l_item_lifecycle_id IS NOT NULL THEN
886 update mtl_item_revisions_interface
887 set lifecycle_id = l_item_lifecycle_id
888 where rowid = cr.rowid;
889 END IF;
890
891 -- 3624686 null check incorporated
892 IF cr.lifecycle_id <> NVL(l_item_lifecycle_id,-1) THEN
893 update mtl_item_revisions_interface
894 set process_flag = l_process_flag_3
895 where rowid = cr.rowid;
896
897 l_lifecycle_error := TRUE;
898
899 status := INVPUOPI.mtl_log_interface_err(
900 cr.organization_id,
901 user_id,
902 login_id,
903 prog_appid,
904 prog_id,
905 request_id,
906 cr.TRANSACTION_ID,
907 error_msg,
908 'LIFECYCLE_ID',
909 'MTL_ITEM_REVISIONS_INTERFACE',
910 'INV_IOI_REV_INVALID_LIFECYCLE',
911 err_text);
912 IF status < 0 THEN
913 raise LOGGING_ERR;
914 END IF;
915 END IF;
916
917 IF cr.current_phase_id IS NOT NULL THEN
918 -- Bug: 3769153 - added NVL in nvl(cr.lifecycle_id, l_item_lifecycle_id)
919 -- Bug 4538382 - NOT chk when control is from PLM:UI for perf
920 -- Bug 5218491 - Modified the IF clause : Changed <> to = in the below check
921 IF (INSTR(NVL(l_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 )
922 THEN
923 IF NOT INV_EGO_REVISION_VALIDATE.Check_LifeCycle_Phase
924 ( p_lifecycle_id => nvl(cr.lifecycle_id, l_item_lifecycle_id),
925 p_lifecycle_phase_id => cr.current_phase_id)
926 THEN
927 update mtl_item_revisions_interface
928 set process_flag = l_process_flag_3
929 where rowid = cr.rowid;
930
931 l_lifecycle_error := TRUE;
932
933 status := INVPUOPI.mtl_log_interface_err(
934 cr.organization_id,
935 user_id,
936 login_id,
937 prog_appid,
938 prog_id,
939 request_id,
940 cr.TRANSACTION_ID,
941 error_msg,
942 'CURRENT_PHASSE_ID',
943 'MTL_ITEM_REVISIONS_INTERFACE',
944 'INV_IOI_REV_INVALID_PHASE',
945 err_text);
946 IF status < 0 THEN
947 raise LOGGING_ERR;
948 END IF;
949 ELSE
950 --Start: 3809876 :Unapproved item rev can have first phase ONLY.
951 IF (l_item_trans_type = 'EXISTS' AND l_item_approved <> 'A')
952 OR (l_item_trans_type = 'CREATE' AND INVIDIT3.CHECK_NPR_CATALOG(l_item_catalog))
953 THEN
954 IF cr.current_phase_id <> INV_EGO_REVISION_VALIDATE.Get_Initial_Lifecycle_Phase(nvl(cr.lifecycle_id, l_item_lifecycle_id))
955 THEN
956 update mtl_item_revisions_interface
957 set process_flag = l_process_flag_3
958 where rowid = cr.rowid;
959
960 l_lifecycle_error := TRUE;
961
962 status := INVPUOPI.mtl_log_interface_err(
963 cr.organization_id,
964 user_id,
965 login_id,
966 prog_appid,
967 prog_id,
968 request_id,
969 cr.TRANSACTION_ID,
970 error_msg,
971 'CURRENT_PHASSE_ID',
972 'MTL_ITEM_REVISIONS_INTERFACE',
973 'INV_IOI_REV_UNAPPROVED_PHASE',
974 err_text);
975 IF status < 0 THEN
976 raise LOGGING_ERR;
977 END IF;
978 END IF;
979 END IF;
980 -- End: 3809876 :Unapproved item rev can have first phase ONLY.
981 END IF;
982 END IF;
983 ELSE --cr.current_phase_id IS NOT NULL THEN
984 --2891650 : Start IOI should not default LC phase.
985 update mtl_item_revisions_interface
986 set process_flag = l_process_flag_3
987 where rowid = cr.rowid;
988
989 l_lifecycle_error := TRUE;
990
991 status := INVPUOPI.mtl_log_interface_err(
992 cr.organization_id,
993 user_id,
994 login_id,
995 prog_appid,
996 prog_id,
997 request_id,
998 cr.TRANSACTION_ID,
999 error_msg,
1000 'CURRENT_PHASSE_ID',
1001 'MTL_ITEM_REVISIONS_INTERFACE',
1002 'INV_IOI_PHASE_MANDATORY',
1003 err_text);
1004 IF status < 0 THEN
1005 raise LOGGING_ERR;
1006 END IF;
1007 --2891650 : End IOI should not default LC phase.
1008
1009 END IF; --Phase id if
1010 -- Bug 4538382 - NOT chk when control is from PLM:UI for perf
1011 -- Bug 5218491 - Modified the IF clause : Changed <> to = in the below check
1012 IF (INSTR(NVL(l_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 )
1013 THEN
1014 -- 2808277 : Start Check phase change is allowed or not
1015 IF (cr.transaction_type = 'UPDATE')
1016 AND (cr.current_phase_id <> l_Old_Phase_Id) THEN
1017
1018 --EGO Phase change policy called through INV wrapper.
1019 INV_EGO_REVISION_VALIDATE.phase_change_policy
1020 (P_ORGANIZATION_ID => cr.organization_id
1021 ,P_INVENTORY_ITEM_ID => cr.inventory_item_id
1022 ,P_CURR_PHASE_ID => l_item_phase_id
1023 ,P_FUTURE_PHASE_ID => cr.current_phase_id
1024 ,P_PHASE_CHANGE_CODE => NULL
1025 ,P_LIFECYCLE_ID => cr.lifecycle_id
1026 ,X_POLICY_CODE => l_Policy_Code
1027 ,X_RETURN_STATUS => l_Return_Status
1028 ,X_ERRORCODE => l_Error_Code
1029 ,X_MSG_COUNT => l_Msg_Count
1030 ,X_MSG_DATA => l_Msg_Data);
1031
1032 IF l_Policy_Code <> 'ALLOWED' THEN
1033
1034 update mtl_item_revisions_interface
1035 set process_flag = l_process_flag_3
1036 where rowid = cr.rowid;
1037
1038 status := INVPUOPI.mtl_log_interface_err(
1039 cr.organization_id,
1040 user_id,
1041 login_id,
1042 prog_appid,
1043 prog_id,
1044 request_id,
1045 cr.TRANSACTION_ID,
1046 error_msg,
1047 'CURRENT_PHASE_ID',
1048 'MTL_ITEM_REVISIONS_INTERFACE',
1049 'INV_IOI_PHASE_CHANGE_NOT_VALID',
1050 err_text);
1051 IF status < 0 THEN
1052 raise LOGGING_ERR;
1053 END IF;
1054
1055 END IF; --Policy code
1056
1057 END IF;
1058
1059 END IF;
1060 -- 2808277 : End Check phase change is allowed or not
1061 END IF; -- cr.lifecycle_id IS NOT NULL OR cr.current_phase_id IS NOT NULL
1062
1063 --2885843: Start default revision error propagated to imported item
1064 IF l_lifecycle_error AND cr.transaction_type ='CREATE' THEN
1065
1066 OPEN c_get_default_rev(cp_org_id => cr.organization_id);
1067 FETCH c_get_default_rev INTO l_default_rev;
1068 CLOSE c_get_default_rev;
1069
1070 IF l_default_rev = cr.revision THEN
1071 UPDATE mtl_system_items_interface
1072 SET process_flag = l_process_flag_3
1073 WHERE inventory_item_id = cr.inventory_item_id
1074 AND organization_id = cr.organization_id
1075 AND set_process_id = xset_id;
1076
1077 END IF;
1078 END IF;
1079 --2885843: End default revision error propagated to imported item
1080
1081 END LOOP;
1082
1083 update mtl_item_revisions_interface
1084 set process_flag = l_process_flag_4,
1085 revision_label = NVL(revision_label,revision),
1086 revision_id = NVL(revision_id,MTL_ITEM_REVISIONS_B_S.NEXTVAL) --2808277
1087 where process_flag = l_process_flag_2
1088 and set_process_id = xset_id
1089 and (organization_id = org_id or all_org = l_all_org);
1090
1091 return(0);
1092
1093 exception
1094
1095 when LOGGING_ERR then
1096 IF c_check_lookup%ISOPEN THEN
1097 CLOSE c_check_lookup;
1098 END IF;
1099 return(status);
1100 when OTHERS then
1101 IF c_check_lookup%ISOPEN THEN
1102 CLOSE c_check_lookup;
1103 END IF;
1104 err_text := substr('INVPVALI.validate_item_revs ' || SQLERRM, 1,240);
1105 return(SQLCODE);
1106
1107 end validate_item_revs;
1108
1109 ----------------------------- mtl_pr_validate_item ----------------------------
1110
1111 FUNCTION mtl_pr_validate_item
1112 (
1113 org_id number,
1114 all_org NUMBER := 2,
1115 prog_appid NUMBER := -1,
1116 prog_id NUMBER := -1,
1117 request_id NUMBER := -1,
1118 user_id NUMBER := -1,
1119 login_id NUMBER := -1,
1120 err_text in out NOCOPY varchar2,
1121 xset_id IN NUMBER DEFAULT -999
1122 )
1123 RETURN INTEGER
1124 IS
1125 status NUMBER := 0;
1126 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
1127 l_process_control VARCHAR2(2000) := NULL; --Used by EGO API only for internal flow control
1128
1129 BEGIN
1130
1131 l_process_control := INV_EGO_REVISION_VALIDATE.Get_Process_Control;
1132
1133 IF l_inv_debug_level IN(101, 102) THEN
1134 INVPUTLI.info('INVPVALI: first sta..set_id'||to_char(xset_id)||'org'||to_char(org_id)||'all'||to_char(all_org));
1135 END IF;
1136
1137 if (xset_id < 900000000000) then
1138
1139 IF l_inv_debug_level IN(101, 102) THEN
1140 INVPUTLI.info('INVPVALI: before INVPVALM.validate_item_org1');
1141 END IF;
1142 status := INVPVALM.validate_item_org1(
1143 org_id,
1144 all_org,
1145 prog_appid,
1146 prog_id,
1147 request_id,
1148 user_id,
1149 login_id,
1150 err_text,
1151 xset_id);
1152 end if;
1153
1154 if ( (status = 0) and (xset_id < 900000000000) ) then
1155
1156 IF l_inv_debug_level IN(101, 102) THEN
1157 INVPUTLI.info('INVPVALI: before INVPALM2.validate_item_org4');
1158 END IF;
1159 status := INVPVLM2.validate_item_org4(
1160 org_id,
1161 all_org,
1162 prog_appid,
1163 prog_id,
1164 request_id,
1165 user_id,
1166 login_id,
1167 err_text,
1168 xset_id);
1169 end if;
1170
1171 if ( status = 0 and xset_id < 900000000000 ) then
1172
1173 IF l_inv_debug_level IN(101, 102) THEN
1174 INVPUTLI.info('INVPVALI: before INVPVLM3.validate_item_org7');
1175 END IF;
1176 status := INVPVLM3.validate_item_org7(
1177 org_id,
1178 all_org,
1179 prog_appid,
1180 prog_id,
1181 request_id,
1182 user_id,
1183 login_id,
1184 err_text,
1185 xset_id);
1186 end if;
1187
1188 --Start : Check for data security and user privileges
1189 if (status = 0) then
1190 -- Bug 4538382 - NOT chk when control is from PLM:UI for perf
1191 -- Bug 5218491 - Modified the IF clause : Changed <> to = in the below check
1192 IF (INSTR(NVL(l_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 )
1193 THEN
1194 IF l_inv_debug_level IN(101, 102) THEN
1195 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
1196 END IF;
1197
1198 status := INV_EGO_REVISION_VALIDATE.validate_item_user_privileges(
1199 P_Org_Id => org_id
1200 ,P_All_Org => all_org
1201 ,P_Prog_AppId => prog_appid
1202 ,P_Prog_Id => prog_id
1203 ,P_Request_Id => request_id
1204 ,P_User_Id => user_id
1205 ,P_Login_Id => login_id
1206 ,P_Set_Id => xset_id
1207 ,X_Err_Text => err_text);
1208
1209 IF l_inv_debug_level IN(101, 102) THEN
1210 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: INV_EGO_REVISION_VALIDATE.validate_item_user_privileges');
1211 END IF;
1212 END IF;
1213 end if;
1214 --End : Check for data security and user privileges
1215
1216 if (status = 0) then
1217
1218 IF l_inv_debug_level IN(101, 102) THEN
1219 INVPUTLI.info('INVPVALI: before INVPVHDR.validate_item_header'||to_char(xset_id)||'org'||to_char(org_id)||'all'||to_char(all_org));
1220 END IF;
1221 status := INVPVHDR.validate_item_header(
1222 org_id,
1223 all_org,
1224 prog_appid,
1225 prog_id,
1226 request_id,
1227 user_id,
1228 login_id,
1229 err_text,
1230 xset_id);
1231 end if;
1232
1233 if (status = 0) then
1234
1235 IF l_inv_debug_level IN(101, 102) THEN
1236 INVPUTLI.info('INVPVALI: before INVPVDR2.validate_item_header2');
1237 END IF;
1238
1239 status := INVPVDR2.validate_item_header2(
1240 org_id,
1241 all_org,
1242 prog_appid,
1243 prog_id,
1244 request_id,
1245 user_id,
1246 login_id,
1247 err_text,
1248 xset_id);
1249 end if;
1250
1251 if (status = 0) then
1252
1253 IF l_inv_debug_level IN(101, 102) THEN
1254 INVPUTLI.info('INVPVALI: before INVPVDR3.validate_item_header3');
1255 END IF;
1256
1257 status := INVPVDR3.validate_item_header3(
1258 org_id,
1259 all_org,
1260 prog_appid,
1261 prog_id,
1262 request_id,
1263 user_id,
1264 login_id,
1265 err_text,
1266 xset_id);
1267 end if;
1268
1269 if (status = 0) then
1270
1271 IF l_inv_debug_level IN(101, 102) THEN
1272 INVPUTLI.info('INVPVALI: before INVPVDR4.validate_item_header4');
1273 END IF;
1274
1275
1276 status := INVPVDR4.validate_item_header4(
1277 org_id,
1278 all_org,
1279 prog_appid,
1280 prog_id,
1281 request_id,
1282 user_id,
1283 login_id,
1284 err_text,
1285 xset_id);
1286 end if;
1287
1288 if (status = 0) then
1289
1290 IF l_inv_debug_level IN(101, 102) THEN
1291 INVPUTLI.info('INVPVALI: before INVPVDR5.validate_item_header5');
1292 END IF;
1293
1294 status := INVPVDR5.validate_item_header5(
1295 org_id,
1296 all_org,
1297 prog_appid,
1298 prog_id,
1299 request_id,
1300 user_id,
1301 login_id,
1302 err_text,
1303 xset_id);
1304 end if;
1305
1306 if (status = 0) then
1307
1308 IF l_inv_debug_level IN(101, 102) THEN
1309 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before INVPVDR6.validate_item_header6');
1310 END IF;
1311 status := INVPVDR6.validate_item_header6 (
1312 org_id,
1313 all_org,
1314 prog_appid,
1315 prog_id,
1316 request_id,
1317 user_id,
1318 login_id,
1319 err_text,
1320 xset_id);
1321
1322 IF l_inv_debug_level IN(101, 102) THEN
1323 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: after INVPVDR6.validate_item_header6');
1324 END IF;
1325 end if;
1326
1327
1328 /* Start Bug 3713912 */
1329 if (status = 0) then
1330
1331 IF l_inv_debug_level IN(101, 102) THEN
1332 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before INVPVDR7.validate_item_header7');
1333 END IF;
1334
1335 status := INVPVDR7.validate_item_header7 (
1336 org_id,
1337 all_org,
1338 prog_appid,
1339 prog_id,
1340 request_id,
1341 user_id,
1342 login_id,
1343 err_text,
1344 xset_id);
1345
1346 IF l_inv_debug_level IN(101, 102) THEN
1347 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: after INVPVDR7.validate_item_header7');
1348 END IF;
1349 end if;
1350 /* End Bug 3713912 */
1351
1352
1353 -- Start 2777118 : Lifecycle and Phase validations for IOI
1354 IF (status = 0) THEN
1355 IF l_inv_debug_level IN(101, 102) THEN
1356 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before INV_EGO_REVISION_VALIDATE.validate_items_lifecycle');
1357 END IF;
1358 status := INV_EGO_REVISION_VALIDATE.validate_items_lifecycle(
1359 P_Org_Id => org_id
1360 ,P_All_Org => all_org
1361 ,P_Prog_AppId => prog_appid
1362 ,P_Prog_Id => prog_id
1363 ,P_Request_Id => request_id
1364 ,P_User_Id => user_id
1365 ,P_Login_Id => login_id
1366 ,P_Set_Id => xset_id
1367 ,X_Err_Text => err_text);
1368
1369 IF l_inv_debug_level IN(101, 102) THEN
1370 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: INV_EGO_REVISION_VALIDATE.validate_items_lifecycle');
1371 END IF;
1372 END IF;
1373 -- End 2777118 : Lifecycle and Phase validations for IOI
1374
1375
1376
1377 -- validate item revisions
1378
1379 --Bug:3531430 Validate Item revs irrespective of item is succeeded or not.
1380 -- if (status = 0) then
1381
1382 IF l_inv_debug_level IN(101, 102) THEN
1383 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before validate_item_revs');
1384 END IF;
1385
1386 status := INVPVALI.validate_item_revs (
1387 org_id,
1388 all_org,
1389 prog_appid,
1390 prog_id,
1391 request_id,
1392 user_id,
1393 login_id,
1394 err_text,
1395 xset_id );
1396
1397 IF l_inv_debug_level IN(101, 102) THEN
1398 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: after validate_item_revs');
1399 END IF;
1400
1401 -- end if;
1402 IF l_inv_debug_level IN(101, 102) THEN
1403 INVPUTLI.info('INVPVALI.mtl_pr_validate_item: before validate_items_catalog_group');
1404 END IF;
1405
1406 --5208102
1407 INV_EGO_REVISION_VALIDATE.Insert_Revision_UserAttr(P_Set_id=>xset_id);
1408
1409 RETURN (status);
1410
1411 EXCEPTION
1412
1413 when OTHERS then
1414 err_text := substr('INVPVALI.mtl_pr_validate_item ' || SQLERRM, 1,240);
1415 return(SQLCODE);
1416
1417 END mtl_pr_validate_item;
1418
1419
1420 END INVPVALI;