[Home] [Help]
PACKAGE BODY: APPS.INVPVDR7
Source
1 PACKAGE BODY INVPVDR7 AS
2 /* $Header: INVPVD7B.pls 120.22.12020000.5 2013/01/23 08:44:40 lmai ship $ */
3
4 FUNCTION validate_item_header7
5 (
6 org_id number,
7 all_org NUMBER := 2,
8 prog_appid NUMBER := -1,
9 prog_id NUMBER := -1,
10 request_id NUMBER := -1,
11 user_id NUMBER := -1,
12 login_id NUMBER := -1,
13 err_text IN OUT NOCOPY VARCHAR2,
14 xset_id IN NUMBER DEFAULT -999
15 )
16 RETURN INTEGER
17 IS
18 l_col_name VARCHAR2(30);
19 l_msg_name VARCHAR2(30);
20
21 /*
22 ** Retrieve column values for validation
23 */
24 CURSOR cc is
25 select intf.ROWID, intf.*
26 from MTL_SYSTEM_ITEMS_INTERFACE intf
27 where ((organization_id = org_id) or -- fix for bug#8757041,removed + 0
28 (all_Org = 1))
29 and set_process_id = xset_id
30 and process_flag in (31, 32, 33, 34, 35, 36, 46);
31
32 CURSOR c_org_loc_control(cp_org_id number) IS
33 SELECT stock_locator_control_code,
34 primary_cost_method,
35 NVL(wms_enabled_flag,'N'),
36 NVL(process_enabled_flag,'N'),
37 NVL(eam_enabled_flag,'N'),
38 NVL(trading_partner_org_flag,'N')
39 FROM mtl_parameters
40 where organization_id = cp_org_id;
41
42 CURSOR c_subinv_loc_control(cp_org_id number,
43 cp_subinv_name varchar2) IS
44 SELECT locator_type
45 FROM mtl_secondary_inventories
46 WHERE secondary_inventory_name = cp_subinv_name
47 AND organization_id = cp_org_id
48 AND SYSDATE < nvl(disable_date, SYSDATE+1);
49
50 l_org_loc_ctrl NUMBER;
51 l_subinv_loc_ctrl NUMBER;
52 l_loc_mandatory BOOLEAN := FALSE;
53
54 l_process_subinv_error BOOLEAN := FALSE;
55 l_process_locator_error BOOLEAN := FALSE;
56 l_message_name VARCHAR2(30):= NULL;
57 l_msg_text fnd_new_messages.message_text%TYPE;
58
59 l_item_id NUMBER;
60 l_org_id NUMBER;
61 trans_id NUMBER;
62
63 error_msg VARCHAR2(240);-- Bug 5216657, increasing size
64 status NUMBER;
65 dumm_status NUMBER;
66 stmt NUMBER;
67 LOGGING_ERR EXCEPTION;
68 VALIDATE_ERR EXCEPTION;
69 lot_num_generation_val NUMBER;
70 l_temp VARCHAR2(10);
71 l_child_lot_starting_number pls_integer;
72 l_trading_partner_org VARCHAR2(1);
73 l_process_enabled VARCHAR2(1);
74 l_wms_enabled VARCHAR2(1);
75 l_eam_enabled VARCHAR2(1);
76 l_cost_method NUMBER;
77 l_process_flag_2 number := 2 ;
78 l_process_flag_3 number := 3 ;
79 l_process_flag_4 number := 4 ;
80 temp_proc_flag number;
81 reqst_id NUMBER ;
82
83 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
84 l_charge_periodicity_class VARCHAR2(10);
85 l_SHIKYU_profile VARCHAR2(1);
86
87 BEGIN
88
89 IF l_inv_debug_level IN(101, 102) THEN
90 INVPUTLI.info('INVPVDR7.validate_item_header7: begin');
91 END IF;
92
93 -- Retrieving fnd_profile values outside the loop for perf reasons.
94 l_charge_periodicity_class := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
95 l_SHIKYU_profile := fnd_profile.value('JMF_SHK_CHARGE_BASED_ENABLED');
96
97 reqst_id := request_id ;
98 -- Validate the records
99
100 FOR cr IN cc LOOP
101
102 status := 0;
103
104 trans_id := cr.transaction_id;
105 l_org_id := cr.organization_id;
106 l_item_id := cr.inventory_item_id; --Bug: 4705184
107 temp_proc_flag := cr.process_flag; -- Bug 4705184
108
109 /* Bug 4705184
110 select inventory_item_id
111 into l_item_id
112 from mtl_system_items_interface
113 where transaction_id = cr.transaction_id
114 and set_process_id = xset_id; */
115
116 open c_org_loc_control(cr.organization_id);
117 fetch c_org_loc_control INTO l_org_loc_ctrl,l_cost_method,l_wms_enabled,l_process_enabled,
118 l_eam_enabled,l_trading_partner_org;
119 close c_org_loc_control;
120
121 /*Validate following attributes in this file*/
122 /*LOT_DIVISIBLE_FLAG
123 GRADE_CONTROL_FLAG
124 DEFAULT_GRADE
125 CHILD_LOT_FLAG
126 PARENT_CHILD_GENERATION_FLAG
127 CHILD_LOT_PREFIX
128 CHILD_LOT_STARTING_NUMBER
129 CHILD_LOT_VALIDATION_FLAG
130 COPY_LOT_ATTRIBUTE_FLAG
131 RECIPE_ENABLED_FLAG
132 PROCESS_QUALITY_ENABLED_FLAG
133 PROCESS_EXECUTION_ENABLED_FLAG
134 PROCESS_COSTING_ENABLED_FLAG
135 PROCESS_SUPPLY_SUBINVENTORY
136 PROCESS_SUPPLY_LOCATOR_ID
137 PROCESS_YIELD_SUBINVENTORY
138 PROCESS_YIELD_LOCATOR_ID
139 HAZARDOUS_MATERIAL_FLAG
140 CAS_NUMBER
141 RETEST_INTERVAL
142 EXPIRATION_ACTION_INTERVAL
143 EXPIRATION_ACTION_CODE
144 MATURITY_DAYS
145 HOLD_DAYS
146 */
147
148
149
150 --Item can be Lot In/Divisible only if Lot Control is Full Controlled
151 IF l_inv_debug_level IN(101, 102) THEN
152 INVPUTLI.info('INVPVDR7: verifying lot divisible flag.... LOT_DIVISIBLE_FLAG');
153 END IF;
154
155 IF ( cr.LOT_DIVISIBLE_FLAG IS NOT NULL
156 AND cr.LOT_DIVISIBLE_FLAG NOT IN ( 'Y','N') )THEN
157 dumm_status := INVPUOPI.mtl_log_interface_err(
158 cr.organization_id,
159 user_id,
160 login_id,
161 prog_appid,
162 prog_id,
163 request_id,
164 cr.TRANSACTION_ID,
165 error_msg,
166 'LOT_DIVISIBLE_FLAG',
167 'MTL_SYSTEM_ITEMS_INTERFACE',
168 'INV_IOI_FLAG_Y_N_NULL',
169 err_text );
170 IF dumm_status < 0 THEN
171 raise LOGGING_ERR;
172 END IF;
173 status := 1;
174 END IF;-- LOT_DIVISIBLE_FLAG is not null AND <> 'N','Y'
175 /* Bug #14232952, clear dependent field automatically if LOT CONTROL is NO CONTROL
176 if (cr.LOT_CONTROL_CODE <> 2 and NVL(cr.LOT_DIVISIBLE_FLAG,'N') = 'Y') then
177
178 dumm_status := INVPUOPI.mtl_log_interface_err(
179 cr.organization_id,
180 user_id,
181 login_id,
182 prog_appid,
183 prog_id,
184 request_id,
185 cr.TRANSACTION_ID,
186 error_msg,
187 'LOT_DIVISIBLE_FLAG',
188 'MTL_SYSTEM_ITEMS_INTERFACE',
189 'INV_INVALID_LOT_DIVISIBLE',
190 err_text);
191 if dumm_status < 0 then
192 raise LOGGING_ERR;
193 end if;
194 status := 1;
195 end if;
196 */
197 --Item can be Grade Controlled only if Lot Control is Full Controlled
198 IF l_inv_debug_level IN(101, 102) THEN
199 INVPUTLI.info('INVPVDR7: verifying grade control flag GRADE_CONTROL_FLAG....');
200 END IF;
201 IF ( cr.GRADE_CONTROL_FLAG IS NOT NULL
202 AND cr.GRADE_CONTROL_FLAG NOT IN ( 'Y','N') )THEN
203 dumm_status := INVPUOPI.mtl_log_interface_err(
204 cr.organization_id,
205 user_id,
206 login_id,
207 prog_appid,
208 prog_id,
209 request_id,
210 cr.TRANSACTION_ID,
211 error_msg,
212 'GRADE_CONTROL_FLAG',
213 'MTL_SYSTEM_ITEMS_INTERFACE',
214 'INV_IOI_FLAG_Y_N_NULL',
215 err_text );
216 IF dumm_status < 0 THEN
217 raise LOGGING_ERR;
218 END IF;
219
220 status := 1;
221 END IF;-- GRADE_CONTROL_FLAG is not null AND <> 'N','Y'
222
223 if (cr.LOT_CONTROL_CODE <> 2 and NVL(cr.GRADE_CONTROL_FLAG,'N') = 'Y') then
224
225 dumm_status := INVPUOPI.mtl_log_interface_err(
226 cr.organization_id,
227 user_id,
228 login_id,
229 prog_appid,
230 prog_id,
231 request_id,
232 cr.TRANSACTION_ID,
233 error_msg,
234 'GRADE_CONTROL_FLAG',
235 'MTL_SYSTEM_ITEMS_INTERFACE',
236 'INV_INVALID_GRADE_CONTROL',
237 err_text);
238 if dumm_status < 0 then
239 raise LOGGING_ERR;
240 end if;
241
242 status := 1;
243 end if;
244
245 --Item can have Default Grade specified only if Lot Control is Full Controlled
246 --and item is grade controlled.
247 IF l_inv_debug_level IN(101, 102) THEN
248 INVPUTLI.info('INVPVDR7: verifying Default Grade....');
249 END IF;
250 if (cr.DEFAULT_GRADE IS NOT NULL) then
251 if (cr.LOT_CONTROL_CODE <> 2 or NVL(cr.GRADE_CONTROL_FLAG,'N') <> 'Y') then
252
253 dumm_status := INVPUOPI.mtl_log_interface_err(
254 cr.organization_id,
255 user_id,
256 login_id,
257 prog_appid,
258 prog_id,
259 request_id,
260 cr.TRANSACTION_ID,
261 error_msg,
262 'DEFAULT_GRADE',
263 'MTL_SYSTEM_ITEMS_INTERFACE',
264 'INV_INVALID_DEFAULT_GRADE_NULL',
265 err_text);
266 if dumm_status < 0 then
267 raise LOGGING_ERR;
268 end if;
269
270 status := 1;
271
272 elsif (cr.LOT_CONTROL_CODE = 2 or NVL(cr.GRADE_CONTROL_FLAG,'N') = 'Y') then
273 begin
274 select 'x' into l_temp
275 from MTL_GRADES_B
276 where GRADE_CODE = cr.DEFAULT_GRADE
277 and nvl(DISABLE_FLAG,'N') <> 'Y';
278
279 exception
280 when NO_DATA_FOUND then
281 dumm_status := INVPUOPI.mtl_log_interface_err(
282 cr.organization_id,
283 user_id,
284 login_id,
285 prog_appid,
286 prog_id,
287 request_id,
288 cr.TRANSACTION_ID,
289 error_msg,
290 'DEFAULT_GRADE',
291 'MTL_SYSTEM_ITEMS_INTERFACE',
292 'INV_INVALID_DEFAULT_GRADE',
293 err_text);
294
295 if dumm_status < 0 then
296 raise LOGGING_ERR;
297 end if;
298
299 status := 1;
300 end;
301 end if;
302 else /*if cr.DEFAULT_GRADE IS NULL) then*/
303 --if item is grade controlled and has no default grade then log error.
304 if (cr.LOT_CONTROL_CODE = 2 and NVL(cr.GRADE_CONTROL_FLAG,'N') = 'Y') then
305 dumm_status := INVPUOPI.mtl_log_interface_err(
306 cr.organization_id,
307 user_id,
308 login_id,
309 prog_appid,
310 prog_id,
311 request_id,
312 cr.TRANSACTION_ID,
313 error_msg,
314 'DEFAULT_GRADE',
315 'MTL_SYSTEM_ITEMS_INTERFACE',
316 'INV_INVALID_DEFAULT_GRADE',
317 err_text);
318
319 if dumm_status < 0 then
320 raise LOGGING_ERR;
321 end if;
322
323 status := 1;
324 end if;
325 end if;
326
327
328 --validate CHILD_LOT_FLAG
329 --Item can be CHILD_LOT_FLAG enabled only if its lot controlled.
330 IF l_inv_debug_level IN(101, 102) THEN
331 INVPUTLI.info('INVPVDR7: verifying child lot flag....');
332 END IF;
333 IF ( cr.CHILD_LOT_FLAG IS NOT NULL
334 AND cr.CHILD_LOT_FLAG NOT IN ( 'Y','N') )THEN
335 dumm_status := INVPUOPI.mtl_log_interface_err(
336 cr.organization_id,
337 user_id,
338 login_id,
339 prog_appid,
340 prog_id,
341 request_id,
342 cr.TRANSACTION_ID,
343 error_msg,
344 'CHILD_LOT_FLAG',
345 'MTL_SYSTEM_ITEMS_INTERFACE',
346 'INV_IOI_FLAG_Y_N_NULL',
347 err_text );
348 IF dumm_status < 0 THEN
349 raise LOGGING_ERR;
350 END IF;
351
352 status := 1;
353 END IF;-- CHILD_LOT_FLAG is not null AND <> 'N','Y'
354
355 if (cr.LOT_CONTROL_CODE <> 2 and NVL(cr.CHILD_LOT_FLAG,'N') = 'Y') then
356
357 dumm_status := INVPUOPI.mtl_log_interface_err(
358 cr.organization_id,
359 user_id,
360 login_id,
361 prog_appid,
362 prog_id,
363 request_id,
364 cr.TRANSACTION_ID,
365 error_msg,
366 'CHILD_LOT_FLAG',
367 'MTL_SYSTEM_ITEMS_INTERFACE',
368 'INV_INVALID_CHILD_LOT_FLAG',
369 err_text);
370 if dumm_status < 0 then
371 raise LOGGING_ERR;
372 end if;
373
374 status := 1;
375 end if;
376
377 --validate PARENT_CHILD_GENERATION_FLAG
378 --Check the lookup and it can be not null only if item is child lot enabled and lot controlled.
379 IF l_inv_debug_level IN(101, 102) THEN
380 INVPUTLI.info('INVPVDR7: verifying parent child generation flag....');
381 END IF;
382 lot_num_generation_val := NULL;
383 select lot_number_generation
384 into lot_num_generation_val
385 from mtl_parameters
386 where organization_id = cr.organization_id
387 and rownum =1;
388
389 if (cr.PARENT_CHILD_GENERATION_FLAG IS NOT NULL) then
390 l_col_name := 'PARENT_CHILD_GENERATION_FLAG';
391 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
392
393 begin
394 select 'x' into l_temp
395 from FND_LOOKUP_VALUES_VL
396 where LOOKUP_TYPE = 'INV_PARENT_CHILD_GENERATION'
397 and LOOKUP_CODE = cr.PARENT_CHILD_GENERATION_FLAG
398 and SYSDATE between
399 NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
400 and ENABLED_FLAG = 'Y';
401
402 exception
403 when NO_DATA_FOUND then
404 dumm_status := INVPUOPI.mtl_log_interface_err(
405 cr.organization_id,
406 user_id,
407 login_id,
408 prog_appid,
409 prog_id,
410 request_id,
411 cr.TRANSACTION_ID,
412 error_msg,
413 'PARENT_CHILD_GENERATION_FLAG',
414 'MTL_SYSTEM_ITEMS_INTERFACE',
415 l_msg_name,
416 err_text);
417 if dumm_status < 0 then
418 raise LOGGING_ERR;
419 end if;
420
421 status := 1;
422 end;
423 else
424 if (cr.LOT_CONTROL_CODE = 2 and NVL(cr.CHILD_LOT_FLAG,'N') = 'Y' and lot_num_generation_val = 2) then
425
426 dumm_status := INVPUOPI.mtl_log_interface_err(
427 cr.organization_id,
428 user_id,
429 login_id,
430 prog_appid,
431 prog_id,
432 request_id,
433 cr.TRANSACTION_ID,
434 error_msg,
435 'PARENT_CHILD_GENERATION_FLAG',
436 'MTL_SYSTEM_ITEMS_INTERFACE',
437 'INV_INVALID_PARENT_CHILD_FLAG',
438 err_text);
439 if dumm_status < 0 then
440 raise LOGGING_ERR;
441 end if;
442
443 status := 1;
444 end if;
445 end if;
446
447 --validate CHILD_LOT_STARTING_NUMBER
448 --if CHILD_LOT_STARTING_NUMBER is not null it should be a number and
449 --if item is child lot enabled and lot numer generation is at item level then
450 --CHILD_LOT_STARTING_NUMBER has to be not null
451 IF l_inv_debug_level IN(101, 102) THEN
452 INVPUTLI.info('INVPVDR7: verifying child lot starting number....');
453 END IF;
454
455 if (cr.CHILD_LOT_STARTING_NUMBER IS NOT NULL) then
456 if (cr.CHILD_LOT_STARTING_NUMBER < 0) THEN
457 dumm_status := INVPUOPI.mtl_log_interface_err(
458 cr.organization_id,
459 user_id,
460 login_id,
461 prog_appid,
462 prog_id,
463 request_id,
464 cr.TRANSACTION_ID,
465 error_msg,
466 'CHILD_LOT_STARTING_NUMBER',
467 'MTL_SYSTEM_ITEMS_INTERFACE',
468 'INV_INVALID_CHILD_LOT_START_NO',
469 err_text);
470 if dumm_status < 0 then
471 raise LOGGING_ERR;
472 end if;
473 status := 1;
474 end if;
475
476 begin
477 SELECT TO_CHAR(TO_NUMBER(cr.CHILD_LOT_STARTING_NUMBER))
478 INTO l_child_lot_starting_number
479 FROM DUAL;
480
481 if l_child_lot_starting_number <> cr.CHILD_LOT_STARTING_NUMBER then
482 dumm_status := INVPUOPI.mtl_log_interface_err(
483 cr.organization_id,
484 user_id,
485 login_id,
486 prog_appid,
487 prog_id,
488 request_id,
489 cr.TRANSACTION_ID,
490 error_msg,
491 'CHILD_LOT_STARTING_NUMBER',
492 'MTL_SYSTEM_ITEMS_INTERFACE',
493 'INV_INVALID_CHILD_LOT_START_NO',
494 err_text);
495 if dumm_status < 0 then
496 raise LOGGING_ERR;
497 end if;
498 status := 1;
499 end if;
500
501 exception
502 when others then
503 --check to see that the child lot start number is an integer.
504 dumm_status := INVPUOPI.mtl_log_interface_err(
505 cr.organization_id,
506 user_id,
507 login_id,
508 prog_appid,
509 prog_id,
510 request_id,
511 cr.TRANSACTION_ID,
512 error_msg,
513 'CHILD_LOT_STARTING_NUMBER',
514 'MTL_SYSTEM_ITEMS_INTERFACE',
515 'INV_INVALID_CHILD_LOT_START_NO',
516 err_text);
517 if dumm_status < 0 then
518 raise LOGGING_ERR;
519 end if;
520 status := 1;
521 end;
522 elsif cr.CHILD_LOT_STARTING_NUMBER IS NULL then
523 if cr.lot_control_code = 2 and
524 lot_num_generation_val = 2 and
525 nvl(cr.child_lot_flag,'N') = 'Y' then
526 dumm_status := INVPUOPI.mtl_log_interface_err(
527 cr.organization_id,
528 user_id,
529 login_id,
530 prog_appid,
531 prog_id,
532 request_id,
533 cr.TRANSACTION_ID,
534 error_msg,
535 'CHILD_LOT_STARTING_NUMBER',
536 'MTL_SYSTEM_ITEMS_INTERFACE',
537 'INV_INVALID_CHILD_LOT_START_NO',
538 err_text);
539 if dumm_status < 0 then
540 raise LOGGING_ERR;
541 end if;
542 status := 1;
543 end if;
544 end if;
545
546 --validate CHILD_LOT_VALIDATION_FLAG
547 --Item can have child lot validation flag ON only if it is child lot enabled
548 IF l_inv_debug_level IN(101, 102) THEN
549 INVPUTLI.info('INVPVDR7: verifying child lot validate flag....');
550 END IF;
551 IF ( cr.CHILD_LOT_VALIDATION_FLAG IS NOT NULL
552 AND cr.CHILD_LOT_VALIDATION_FLAG NOT IN ( 'Y','N') )THEN
553 dumm_status := INVPUOPI.mtl_log_interface_err(
554 cr.organization_id,
555 user_id,
556 login_id,
557 prog_appid,
558 prog_id,
559 request_id,
560 cr.TRANSACTION_ID,
561 error_msg,
562 'CHILD_LOT_VALIDATION_FLAG',
563 'MTL_SYSTEM_ITEMS_INTERFACE',
564 'INV_IOI_FLAG_Y_N_NULL',
565 err_text );
566 IF dumm_status < 0 THEN
567 raise LOGGING_ERR;
568 END IF;
569
570 status := 1;
571 END IF;-- CHILD_LOT_VALIDATION_FLAG is not null AND <> 'N','Y'
572
573 --validate COPY_LOT_ATTRIBUTE_FLAG
574 --Item can have child lot validation flag ON only if it is child lot enabled
575 IF l_inv_debug_level IN(101, 102) THEN
576 INVPUTLI.info('INVPVDR7: verifying copy lot attribute flag....');
577 END IF;
578
579 IF ( cr.COPY_LOT_ATTRIBUTE_FLAG IS NOT NULL
580 AND cr.COPY_LOT_ATTRIBUTE_FLAG NOT IN ( 'Y','N') )THEN
581 dumm_status := INVPUOPI.mtl_log_interface_err(
582 cr.organization_id,
583 user_id,
584 login_id,
585 prog_appid,
586 prog_id,
587 request_id,
588 cr.TRANSACTION_ID,
589 error_msg,
590 'COPY_LOT_ATTRIBUTE_FLAG',
591 'MTL_SYSTEM_ITEMS_INTERFACE',
592 'INV_IOI_FLAG_Y_N_NULL',
593 err_text );
594 IF dumm_status < 0 THEN
595 raise LOGGING_ERR;
596 END IF;
597
598 status := 1;
599 END IF;-- COPY_LOT_ATTRIBUTE_FLAG is not null AND <> 'N','Y'
600
601 --validate RECIPE_ENABLED_FLAG
602 IF ( cr.RECIPE_ENABLED_FLAG IS NOT NULL
603 AND cr.RECIPE_ENABLED_FLAG NOT IN ( 'Y','N') )THEN
604 dumm_status := INVPUOPI.mtl_log_interface_err(
605 cr.organization_id,
606 user_id,
607 login_id,
608 prog_appid,
609 prog_id,
610 request_id,
611 cr.TRANSACTION_ID,
612 error_msg,
613 'RECIPE_ENABLED_FLAG',
614 'MTL_SYSTEM_ITEMS_INTERFACE',
615 'INV_IOI_FLAG_Y_N_NULL',
616 err_text );
617 IF dumm_status < 0 THEN
618 raise LOGGING_ERR;
619 END IF;
620
621 status := 1;
622 END IF;-- RECIPE_ENABLED_FLAG is not null AND <> 'N','Y'
623
624 --validate PROCESS_EXECUTION_ENABLED_FLAG
625 IF ( cr.PROCESS_EXECUTION_ENABLED_FLAG IS NOT NULL
626 AND cr.PROCESS_EXECUTION_ENABLED_FLAG NOT IN ( 'Y','N') )THEN
627 dumm_status := INVPUOPI.mtl_log_interface_err(
628 cr.organization_id,
629 user_id,
630 login_id,
631 prog_appid,
632 prog_id,
633 request_id,
634 cr.TRANSACTION_ID,
635 error_msg,
636 'PROCESS_EXECUTION_ENABLED_FLAG',
637 'MTL_SYSTEM_ITEMS_INTERFACE',
638 'INV_IOI_FLAG_Y_N_NULL',
639 err_text );
640 IF dumm_status < 0 THEN
641 raise LOGGING_ERR;
642 END IF;
643
644 status := 1;
645 END IF;-- PROCESS_EXECUTION_ENABLED_FLAG is not null AND <> 'N','Y'
646
647 --Added for bug 5300040
648 --Process execution must be No if either of inventory item or reciped enabled flags are No
649 IF ( cr.PROCESS_EXECUTION_ENABLED_FLAG = 'Y' AND
650 (cr.INVENTORY_ITEM_FLAG ='N' OR cr.RECIPE_ENABLED_FLAG ='N' ))THEN
651 dumm_status := INVPUOPI.mtl_log_interface_err(
652 cr.organization_id,
653 user_id,
654 login_id,
655 prog_appid,
656 prog_id,
657 request_id,
658 cr.TRANSACTION_ID,
659 error_msg,
660 'PROCESS_EXECUTION_ENABLED_FLAG',
661 'MTL_SYSTEM_ITEMS_INTERFACE',
662 'INV_PR_EXEC_NOT_ALLOWED',
663 err_text );
664 IF dumm_status < 0 THEN
665 raise LOGGING_ERR;
666 END IF;
667
668 status := 1;
669 END IF;-- PROCESS_EXECUTION_ENABLED_FLAG cannot be 'Y' if inventory or recipe enabled flag is 'N'
670
671
672 --validate PROCESS_COSTING_ENABLED_FLAG
673 IF ( cr.PROCESS_COSTING_ENABLED_FLAG IS NOT NULL
674 AND cr.PROCESS_COSTING_ENABLED_FLAG NOT IN ( 'Y','N') )THEN
675 dumm_status := INVPUOPI.mtl_log_interface_err(
676 cr.organization_id,
677 user_id,
678 login_id,
679 prog_appid,
680 prog_id,
681 request_id,
682 cr.TRANSACTION_ID,
683 error_msg,
684 'PROCESS_COSTING_ENABLED_FLAG',
685 'MTL_SYSTEM_ITEMS_INTERFACE',
686 'INV_IOI_FLAG_Y_N_NULL',
687 err_text );
688 IF dumm_status < 0 THEN
689 raise LOGGING_ERR;
690 END IF;
691
692 status := 1;
693 END IF;-- PROCESS_COSTING_ENABLED_FLAG is not null AND <> 'N','Y'
694
695 --validate PROCESS_QUALITY_ENABLED_FLAG
696 IF ( cr.PROCESS_QUALITY_ENABLED_FLAG IS NOT NULL
697 AND cr.PROCESS_QUALITY_ENABLED_FLAG NOT IN ( 'Y','N') )THEN
698 dumm_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 'PROCESS_QUALITY_ENABLED_FLAG',
708 'MTL_SYSTEM_ITEMS_INTERFACE',
709 'INV_IOI_FLAG_Y_N_NULL',
710 err_text );
711 IF dumm_status < 0 THEN
712 raise LOGGING_ERR;
713 END IF;
714
715 status := 1;
716 END IF;-- PROCESS_QUALITY_ENABLED_FLAG is not null AND <> 'N','Y'
717
718 --validate PROCESS_SUPPLY_SUBINVENTORY
719 --validate foreign keys
720 IF l_inv_debug_level IN(101, 102) THEN
721 INVPUTLI.info('INVPVDR7: verifying process supply subinventory....');
722 END IF;
723
724 if (cr.PROCESS_SUPPLY_SUBINVENTORY IS NOT NULL ) then
725
726 l_process_subinv_error := FALSE;
727
728 if cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='CREATE' then
729 l_process_subinv_error := TRUE;
730 l_message_name := 'INV_IOI_PROCESS_SUP_SUB';
731 elsif cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
732 begin
733 select 'x' INTO l_temp
734 from MTL_ITEM_SUB_INVENTORIES i
735 where i.inventory_item_id = cr.inventory_item_id
736 and i.ORGANIZATION_ID = cr.ORGANIZATION_ID
737 and i.SECONDARY_INVENTORY = cr.PROCESS_SUPPLY_SUBINVENTORY;
738
739 exception
740 when no_data_found then
741 l_process_subinv_error := TRUE;
742 l_message_name := 'INV_INT_RESSUBEXP';
743 end;
744 elsif NVL(cr.RESTRICT_SUBINVENTORIES_CODE,2) = 2 then
745 begin
746 select 'x' INTO l_temp
747 from MTL_SECONDARY_INVENTORIES
748 where SECONDARY_INVENTORY_NAME = cr.PROCESS_SUPPLY_SUBINVENTORY
749 and ORGANIZATION_ID = cr.ORGANIZATION_ID
750 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
751
752 exception
753 when no_data_found then
754 l_process_subinv_error := TRUE;
755 l_message_name := 'INV_IOI_PROCESS_SUP_SUB';
756 end;
757 end if;
758
759 if l_process_subinv_error then
760 dumm_status := INVPUOPI.mtl_log_interface_err(
761 cr.organization_id,
762 user_id,
763 login_id,
764 prog_appid,
765 prog_id,
766 request_id,
767 cr.TRANSACTION_ID,
768 error_msg,
769 'PROCESS_SUPPLY_SUBINVENTORY',
770 'MTL_SYSTEM_ITEMS_INTERFACE',
771 l_message_name,
772 err_text);
773 if dumm_status < 0 then
774 raise LOGGING_ERR;
775 end if;
776 status := 1;
777 end if;
778 end if;
779
780 --validate PROCESS_SUPPLY_LOCATOR_ID
781 IF l_inv_debug_level IN(101, 102) THEN
782 INVPUTLI.info('INVPVDR7: verifying process supply locator id....');
783 END IF;
784
785 --{
786 if cr.PROCESS_SUPPLY_SUBINVENTORY IS NOT NULL then
787
788 l_loc_mandatory := FALSE;
789
790 if l_org_loc_ctrl IN (2,3) then
791 l_loc_mandatory := TRUE;
792 end if;
793
794 if NOT l_loc_mandatory then
795
796 open c_subinv_loc_control(cr.organization_id,cr.process_supply_subinventory);
797 fetch c_subinv_loc_control INTO l_subinv_loc_ctrl;
798 close c_subinv_loc_control;
799
800 if l_subinv_loc_ctrl NOT IN (1,5) then
801 l_loc_mandatory := TRUE;
802 end if;
803 end if;
804
805 if NOT l_loc_mandatory
806 and cr.LOCATION_CONTROL_CODE <> 1 then
807 l_loc_mandatory := TRUE;
808 end if;
809 --{
810 if l_loc_mandatory
811 AND cr.PROCESS_SUPPLY_LOCATOR_ID IS NULL then
812 dumm_status := INVPUOPI.mtl_log_interface_err(
813 cr.organization_id,
814 user_id,
815 login_id,
816 prog_appid,
817 prog_id,
818 request_id,
819 cr.TRANSACTION_ID,
820 error_msg,
821 'PROCESS_SUPPLY_LOCATOR_ID',
822 'MTL_SYSTEM_ITEMS_INTERFACE',
823 'INV_IOI_PROCESS_SUP_LOC_ID',
824 err_text);
825 if dumm_status < 0 THEN
826 raise LOGGING_ERR;
827 end if;
828
829 status := 1;
830 end if;--}
831
832 end if;--}
833
834 -- validate foreign keys
835 --{
836 if cr.PROCESS_SUPPLY_LOCATOR_ID IS NOT NULL then
837
838 l_process_locator_error := FALSE;
839
840 --if (nvl(cr.PROCESS_EXECUTION_ENABLED_FLAG,'N') = 'N') then
841 -- l_process_subinv_error := TRUE;
842 -- l_message_name := 'INV_IOI_PROCESS_SUP_LOC_ID';
843 --end if;
844
845 if cr.RESTRICT_LOCATORS_CODE = 1 and cr.TRANSACTION_TYPE ='CREATE' then
846 l_process_locator_error := TRUE;
847 elsif cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
848 BEGIN
849 select 'x' INTO l_temp
850 from MTL_SECONDARY_LOCATORS
851 where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
852 and ORGANIZATION_ID = cr.ORGANIZATION_ID
853 and SECONDARY_LOCATOR = cr.PROCESS_SUPPLY_LOCATOR_ID
854 and SUBINVENTORY_CODE = cr.PROCESS_SUPPLY_SUBINVENTORY;
855
856 exception
857 when NO_DATA_FOUND then
858 l_process_locator_error := TRUE;
859 end;
860 elsif NVL(cr.RESTRICT_LOCATORS_CODE,2) = 2 THEN
861 begin
862 select 'x' INTO l_temp
863 from MTL_ITEM_LOCATIONS
864 where INVENTORY_LOCATION_ID = cr.PROCESS_SUPPLY_LOCATOR_ID
865 and SUBINVENTORY_CODE = cr.PROCESS_SUPPLY_SUBINVENTORY
866 and ORGANIZATION_ID = cr.ORGANIZATION_ID
867 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
868
869 exception
870 when NO_DATA_FOUND then
871 l_process_locator_error := TRUE;
872 end;
873 end if;
874
875 if l_process_locator_error then
876 dumm_status := INVPUOPI.mtl_log_interface_err(
877 cr.organization_id,
878 user_id,
879 login_id,
880 prog_appid,
881 prog_id,
882 request_id,
883 cr.TRANSACTION_ID,
884 error_msg,
885 'PROCESS_SUPPLY_LOCATOR_ID',
886 'MTL_SYSTEM_ITEMS_INTERFACE',
887 'INV_IOI_PROCESS_SUP_LOC_ID',
888 err_text);
889 if dumm_status < 0 then
890 raise LOGGING_ERR;
891 end if;
892 status := 1;
893 end if;
894 end if; --}
895
896
897 --validate PROCESS_YIELD_SUBINVENTORY
898 --validate foreign keys
899 IF l_inv_debug_level IN(101, 102) THEN
900 INVPUTLI.info('INVPVDR7: verifying process yield subinventory....');
901 END IF;
902
903 if (cr.PROCESS_YIELD_SUBINVENTORY IS NOT NULL ) then
904
905 l_process_subinv_error := FALSE;
906
907 --if (nvl(cr.PROCESS_EXECUTION_ENABLED_FLAG,'N') = 'N') then
908 -- l_process_subinv_error := TRUE;
909 -- l_message_name := 'INV_IOI_PROCESS_YIELD_SUB';
910 --end if;
911
912 if cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='CREATE' then
913 l_process_subinv_error := TRUE;
914 l_message_name := 'INV_IOI_PROCESS_YIELD_SUB';
915 elsif cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
916 begin
917 select 'x' INTO l_temp
918 from MTL_ITEM_SUB_INVENTORIES i
919 where i.inventory_item_id = cr.inventory_item_id
920 and i.ORGANIZATION_ID = cr.ORGANIZATION_ID
921 and i.SECONDARY_INVENTORY = cr.PROCESS_YIELD_SUBINVENTORY;
922
923 exception
924 when no_data_found then
925 l_process_subinv_error := TRUE;
926 l_message_name := 'INV_INT_RESSUBEXP';
927 end;
928 elsif NVL(cr.RESTRICT_SUBINVENTORIES_CODE,2) = 2 then
929 begin
930 select 'x' INTO l_temp
931 from MTL_SECONDARY_INVENTORIES
932 where SECONDARY_INVENTORY_NAME = cr.PROCESS_YIELD_SUBINVENTORY
933 and ORGANIZATION_ID = cr.ORGANIZATION_ID
934 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
935
936 exception
937 when no_data_found then
938 l_process_subinv_error := TRUE;
939 l_message_name := 'INV_IOI_PROCESS_YIELD_SUB';
940 end;
941 end if;
942
943 if l_process_subinv_error then
944 dumm_status := INVPUOPI.mtl_log_interface_err(
945 cr.organization_id,
946 user_id,
947 login_id,
948 prog_appid,
949 prog_id,
950 request_id,
951 cr.TRANSACTION_ID,
952 error_msg,
953 'PROCESS_YIELD_SUBINVENTORY',
954 'MTL_SYSTEM_ITEMS_INTERFACE',
955 l_message_name,
956 err_text);
957 if dumm_status < 0 then
958 raise LOGGING_ERR;
959 end if;
960 status := 1;
961 end if;
962 end if;
963
964 --validate PROCESS_YIELD_LOCATOR_ID
965 IF l_inv_debug_level IN(101, 102) THEN
966 INVPUTLI.info('INVPVDR7: verifying process yield locator....');
967 END IF;
968
969 if cr.PROCESS_YIELD_SUBINVENTORY IS NOT NULL then
970
971 l_loc_mandatory := FALSE;
972
973 if l_org_loc_ctrl IN (2,3) then
974 l_loc_mandatory := TRUE;
975 end if;
976
977 if NOT l_loc_mandatory then
978
979 open c_subinv_loc_control(cr.organization_id,cr.process_yield_subinventory);
980 fetch c_subinv_loc_control INTO l_subinv_loc_ctrl;
981 close c_subinv_loc_control;
982
983 if l_subinv_loc_ctrl NOT IN (1,5) then
984 l_loc_mandatory := TRUE;
985 end if;
986 end if;
987
988 if NOT l_loc_mandatory
989 and cr.LOCATION_CONTROL_CODE <> 1 then
990 l_loc_mandatory := TRUE;
991 end if;
992
993 if l_loc_mandatory
994 AND cr.PROCESS_YIELD_LOCATOR_ID IS NULL then
995 dumm_status := INVPUOPI.mtl_log_interface_err(
996 cr.organization_id,
997 user_id,
998 login_id,
999 prog_appid,
1000 prog_id,
1001 request_id,
1002 cr.TRANSACTION_ID,
1003 error_msg,
1004 'PROCESS_YIELD_LOCATOR_ID',
1005 'MTL_SYSTEM_ITEMS_INTERFACE',
1006 'INV_IOI_PROCESS_YLD_LOC_ID',
1007 err_text);
1008 if dumm_status < 0 THEN
1009 raise LOGGING_ERR;
1010 end if;
1011
1012 status := 1;
1013 end if;
1014
1015 end if;
1016
1017 -- validate foreign keys
1018 if cr.PROCESS_YIELD_LOCATOR_ID IS NOT NULL then
1019
1020 l_process_locator_error := FALSE;
1021
1022 if cr.RESTRICT_LOCATORS_CODE = 1 and cr.TRANSACTION_TYPE ='CREATE' then
1023 l_process_locator_error := TRUE;
1024 elsif cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' then
1025 BEGIN
1026 select 'x' INTO l_temp
1027 from MTL_SECONDARY_LOCATORS
1028 where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
1029 and ORGANIZATION_ID = cr.ORGANIZATION_ID
1030 and SECONDARY_LOCATOR = cr.PROCESS_YIELD_LOCATOR_ID
1031 and SUBINVENTORY_CODE = cr.PROCESS_YIELD_SUBINVENTORY;
1032
1033 exception
1034 when NO_DATA_FOUND then
1035 l_process_locator_error := TRUE;
1036 end;
1037 elsif NVL(cr.RESTRICT_LOCATORS_CODE,2) = 2 THEN
1038 begin
1039 select 'x' INTO l_temp
1040 from MTL_ITEM_LOCATIONS
1041 where INVENTORY_LOCATION_ID = cr.PROCESS_YIELD_LOCATOR_ID
1042 and SUBINVENTORY_CODE = cr.PROCESS_YIELD_SUBINVENTORY
1043 and ORGANIZATION_ID = cr.ORGANIZATION_ID
1044 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
1045
1046 exception
1047 when NO_DATA_FOUND then
1048 l_process_locator_error := TRUE;
1049 end;
1050 end if;
1051
1052 if l_process_locator_error then
1053 dumm_status := INVPUOPI.mtl_log_interface_err(
1054 cr.organization_id,
1055 user_id,
1056 login_id,
1057 prog_appid,
1058 prog_id,
1059 request_id,
1060 cr.TRANSACTION_ID,
1061 error_msg,
1062 'PROCESS_YIELD_LOCATOR_ID',
1063 'MTL_SYSTEM_ITEMS_INTERFACE',
1064 'INV_IOI_PROCESS_YLD_LOC_ID',
1065 err_text);
1066 if dumm_status < 0 then
1067 raise LOGGING_ERR;
1068 end if;
1069 status := 1;
1070 end if;
1071 end if;
1072
1073 --validate EXPIRATION_ACTION_CODE
1074 IF l_inv_debug_level IN(101, 102) THEN
1075 INVPUTLI.info('INVPVDR7: verifying expiration action code....');
1076 END IF;
1077 if (cr.EXPIRATION_ACTION_CODE is NOT NULL) then
1078 begin
1079 select 'x' into l_temp
1080 from mtl_actions_b
1081 where action_code = cr.EXPIRATION_ACTION_CODE
1082 and disable_flag= 'N';
1083
1084 exception
1085 when NO_DATA_FOUND then
1086 dumm_status := INVPUOPI.mtl_log_interface_err(
1087 cr.organization_id,
1088 user_id,
1089 login_id,
1090 prog_appid,
1091 prog_id,
1092 request_id,
1093 cr.TRANSACTION_ID,
1094 error_msg,
1095 'EXPIRATION_ACTION_CODE',
1096 'MTL_SYSTEM_ITEMS_INTERFACE',
1097 'INV_INVALID_EXPRTN_ACTN_CODE',
1098 err_text);
1099
1100 if dumm_status < 0 then
1101 raise LOGGING_ERR;
1102 end if;
1103 status := 1;
1104 end;
1105 end if;
1106
1107 --validate RETEST_INTERVAL
1108 --it should be greater than zero
1109 IF l_inv_debug_level IN(101, 102) THEN
1110 INVPUTLI.info('INVPVDR7: verifying retest interval....');
1111 END IF;
1112 if (cr.RETEST_INTERVAL is NOT NULL) then
1113 if (cr.RETEST_INTERVAL < 0 OR cr.RETEST_INTERVAL > 999999) then
1114
1115 dumm_status := INVPUOPI.mtl_log_interface_err(
1116 cr.organization_id,
1117 user_id,
1118 login_id,
1119 prog_appid,
1120 prog_id,
1121 request_id,
1122 cr.TRANSACTION_ID,
1123 error_msg,
1124 'RETEST_INTERVAL',
1125 'MTL_SYSTEM_ITEMS_INTERFACE',
1126 'INV_INVALID_RETEST_INTERVAL',
1127 err_text);
1128 if dumm_status < 0 then
1129 raise LOGGING_ERR;
1130 end if;
1131 status := 1;
1132 end if;
1133 end if;
1134
1135 --validate EXPIRATION ACTION INTERVAL
1136 --it should be greater than or equal to -999999 and less than or equal to 999999
1137 IF l_inv_debug_level IN(101, 102) THEN
1138 INVPUTLI.info('INVPVDR7: verifying expiration action interval....');
1139 END IF;
1140 if (cr.EXPIRATION_ACTION_INTERVAL is NOT NULL) then
1141 if (cr.EXPIRATION_ACTION_INTERVAL < -999999 OR cr.EXPIRATION_ACTION_INTERVAL > 999999) then
1142
1143 dumm_status := INVPUOPI.mtl_log_interface_err(
1144 cr.organization_id,
1145 user_id,
1146 login_id,
1147 prog_appid,
1148 prog_id,
1149 request_id,
1150 cr.TRANSACTION_ID,
1151 error_msg,
1152 'EXPIRATION_ACTION_INTERVAL',
1153 'MTL_SYSTEM_ITEMS_INTERFACE',
1154 'INV_INVALID_EXPRTN_ACTN_INTRVL',
1155 err_text);
1156 if dumm_status < 0 then
1157 raise LOGGING_ERR;
1158 end if;
1159 status := 1;
1160 end if;
1161 end if;
1162
1163
1164 --validate MATURITY_DAYS
1165 --it should be greater than zero
1166 IF l_inv_debug_level IN(101, 102) THEN
1167 INVPUTLI.info('INVPVDR7: verifying maturity days....');
1168 END IF;
1169 if (cr.MATURITY_DAYS is NOT NULL) then
1170 if (cr.MATURITY_DAYS < 0 OR cr.MATURITY_DAYS > 999999) then
1171
1172 dumm_status := INVPUOPI.mtl_log_interface_err(
1173 cr.organization_id,
1174 user_id,
1175 login_id,
1176 prog_appid,
1177 prog_id,
1178 request_id,
1179 cr.TRANSACTION_ID,
1180 error_msg,
1181 'MATURITY_DAYS',
1182 'MTL_SYSTEM_ITEMS_INTERFACE',
1183 'INV_INVALID_MATURITY_DAYS',
1184 err_text);
1185 if dumm_status < 0 then
1186 raise LOGGING_ERR;
1187 end if;
1188 status := 1;
1189 end if;
1190 end if;
1191
1192 --validate HOLD_DAYS
1193 --it should be greater than zero
1194 IF l_inv_debug_level IN(101, 102) THEN
1195 INVPUTLI.info('INVPVDR7: verifying hold days....');
1196 END IF;
1197
1198 if (cr.HOLD_DAYS is NOT NULL) then
1199 if (cr.HOLD_DAYS < 0 OR cr.HOLD_DAYS > 999999) then
1200
1201 dumm_status := INVPUOPI.mtl_log_interface_err(
1202 cr.organization_id,
1203 user_id,
1204 login_id,
1205 prog_appid,
1206 prog_id,
1207 request_id,
1208 cr.TRANSACTION_ID,
1209 error_msg,
1210 'HOLD_DAYS',
1211 'MTL_SYSTEM_ITEMS_INTERFACE',
1212 'INV_INVALID_HOLD_DAYS',
1213 err_text);
1214 if dumm_status < 0 then
1215 raise LOGGING_ERR;
1216 end if;
1217 status := 1;
1218 end if;
1219 end if;
1220
1221 IF l_inv_debug_level IN(101, 102) THEN
1222 INVPUTLI.info('INVPVDR7: verifying hazardous material flag....');
1223 END IF;
1224
1225 IF ( cr.HAZARDOUS_MATERIAL_FLAG IS NOT NULL
1226 AND cr.HAZARDOUS_MATERIAL_FLAG NOT IN ( 'Y','N') )THEN
1227 dumm_status := INVPUOPI.mtl_log_interface_err(
1228 cr.organization_id,
1229 user_id,
1230 login_id,
1231 prog_appid,
1232 prog_id,
1233 request_id,
1234 cr.TRANSACTION_ID,
1235 error_msg,
1236 'HAZARDOUS_MATERIAL_FLAG',
1237 'MTL_SYSTEM_ITEMS_INTERFACE',
1238 'INV_IOI_FLAG_Y_N_NULL',
1239 err_text );
1240 IF dumm_status < 0 THEN
1241 raise LOGGING_ERR;
1242 END IF;
1243
1244 status := 1;
1245 END IF;-- HAZARDOUS_MATERIAL_FLAG is not null AND <> 'N','Y'
1246
1247 /* R12 Enhancement : Validations added for the new attributes */
1248 IF l_inv_debug_level IN(101, 102) THEN
1249 INVPUTLI.info('INVPVDR7: verifying charge periodicity code....');
1250 END IF;
1251
1252 IF cr.CHARGE_PERIODICITY_CODE IS NOT NULL THEN
1253 BEGIN
1254 SELECT 'x' INTO l_temp
1255 FROM MTL_UOM_CONVERSIONS
1256 WHERE UOM_CLASS = l_charge_periodicity_class
1257 AND UOM_CODE = cr.CHARGE_PERIODICITY_CODE;
1258
1259 EXCEPTION
1260 WHEN NO_DATA_FOUND THEN
1261 dumm_status := INVPUOPI.mtl_log_interface_err(
1262 cr.organization_id,
1263 user_id,
1264 login_id,
1265 prog_appid,
1266 prog_id,
1267 request_id,
1268 cr.TRANSACTION_ID,
1269 error_msg,
1270 'CHARGE_PERIODICITY_CODE',
1271 'MTL_SYSTEM_ITEMS_INTERFACE',
1272 'INV_IOI_INVALID_CHARGE_CODE',
1273 err_text );
1274
1275 IF dumm_status < 0 THEN
1276 RAISE LOGGING_ERR;
1277 END IF;
1278 STATUS := 1;
1279 END;
1280 END IF;
1281 IF l_inv_debug_level IN(101, 102) THEN
1282 INVPUTLI.info('INVPVDR7: verifying repair leadtime....');
1283 END IF;
1284 IF cr.REPAIR_LEADTIME IS NOT NULL
1285 AND cr.REPAIR_LEADTIME < 0 THEN
1286 dumm_status := INVPUOPI.mtl_log_interface_err(
1287 cr.organization_id,
1288 user_id,
1289 login_id,
1290 prog_appid,
1291 prog_id,
1292 request_id,
1293 cr.TRANSACTION_ID,
1294 error_msg,
1295 'REPAIR_LEADTIME',
1296 'MTL_SYSTEM_ITEMS_INTERFACE',
1297 'INV_INVALID_ATTR_COL_VALUE',
1298 err_text );
1299 IF dumm_status < 0 THEN
1300 RAISE LOGGING_ERR;
1301 END IF;
1302 STATUS := 1;
1303 END IF;
1304 IF l_inv_debug_level IN(101, 102) THEN
1305 INVPUTLI.info('INVPVDR7: verifying repair yield....');
1306 END IF;
1307 IF cr.REPAIR_YIELD IS NOT NULL
1308 --Bug 4473603 AND cr.REPAIR_YIELD > 100 AND cr.REPAIR_YIELD < 0 THEN
1309 AND (cr.REPAIR_YIELD > 100 OR cr.REPAIR_YIELD < 0 )THEN
1310 --Bug 5216657 start
1311 FND_MESSAGE.SET_NAME('INV','INV_REPAIR_YIELD_COL_NAME');
1312 error_msg := FND_MESSAGE.GET;
1313 FND_MESSAGE.SET_NAME('INV','INV_IOI_INVALID_PERCENT_VALUE');
1314 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', error_msg);
1315 error_msg := FND_MESSAGE.GET;
1316 --Bug 5216657 end
1317
1318 dumm_status := INVPUOPI.mtl_log_interface_err(
1319 cr.organization_id,
1320 user_id,
1321 login_id,
1322 prog_appid,
1323 prog_id,
1324 request_id,
1325 cr.TRANSACTION_ID,
1326 error_msg,
1327 'REPAIR_YIELD',
1328 'MTL_SYSTEM_ITEMS_INTERFACE',
1329 'INV_IOI_ERR_IVLD_PCENT_VALUE',--bug 11894684, replace INV_IOI_ERR
1330 err_text );
1331 IF dumm_status < 0 THEN
1332 RAISE LOGGING_ERR;
1333 END IF;
1334 STATUS := 1;
1335 END IF;
1336 IF l_inv_debug_level IN(101, 102) THEN
1337 INVPUTLI.info('INVPVDR7: verifying preposition point....');
1338 END IF;
1339
1340 IF cr.PREPOSITION_POINT NOT IN ('Y','N') THEN
1341 dumm_status := INVPUOPI.mtl_log_interface_err(
1342 cr.organization_id,
1343 user_id,
1344 login_id,
1345 prog_appid,
1346 prog_id,
1347 request_id,
1348 cr.TRANSACTION_ID,
1349 error_msg,
1350 'PREPOSITION_POINT',
1351 'MTL_SYSTEM_ITEMS_INTERFACE',
1352 'INV_INVALID_ATTR_COL_VALUE',
1353 err_text );
1354 IF dumm_status < 0 THEN
1355 RAISE LOGGING_ERR;
1356 END IF;
1357 STATUS := 1;
1358 END IF;
1359 IF l_inv_debug_level IN(101, 102) THEN
1360 INVPUTLI.info('INVPVDR7: verifying repair program....');
1361 END IF;
1362
1363 IF cr.REPAIR_PROGRAM NOT IN (1,2,3) THEN
1364 dumm_status := INVPUOPI.mtl_log_interface_err(
1365 cr.organization_id,
1366 user_id,
1367 login_id,
1368 prog_appid,
1369 prog_id,
1370 request_id,
1371 cr.TRANSACTION_ID,
1372 error_msg,
1373 'REPAIR_PROGRAM',
1374 'MTL_SYSTEM_ITEMS_INTERFACE',
1375 'INV_INVALID_ATTR_COL_VALUE',
1376 err_text );
1377 IF dumm_status < 0 THEN
1378 RAISE LOGGING_ERR;
1379 END IF;
1380 STATUS := 1;
1381 END IF;
1382
1383 IF l_inv_debug_level IN(101, 102) THEN
1384 INVPUTLI.info('INVPVDR7: verifying subcontracting component....');
1385 END IF;
1386
1387 IF cr.SUBCONTRACTING_COMPONENT IS NOT NULL AND
1388 cr.SUBCONTRACTING_COMPONENT NOT IN (1,2) THEN
1389 dumm_status := INVPUOPI.mtl_log_interface_err(
1390 cr.organization_id,
1391 user_id,
1392 login_id,
1393 prog_appid,
1394 prog_id,
1395 request_id,
1396 cr.TRANSACTION_ID,
1397 error_msg,
1398 'SUBCONTRACTING_COMPONENT',
1399 'MTL_SYSTEM_ITEMS_INTERFACE',
1400 'INV_INVALID_ATTR_COL_VALUE',
1401 err_text );
1402 IF dumm_status < 0 THEN
1403 RAISE LOGGING_ERR;
1404 END IF;
1405 STATUS := 1;
1406 END IF;
1407 IF l_inv_debug_level IN(101, 102) THEN
1408 INVPUTLI.info('INVPVDR7: verifying outsourced assembly....');
1409 END IF;
1410
1411 IF cr.OUTSOURCED_ASSEMBLY NOT IN (1,2) THEN
1412 dumm_status := INVPUOPI.mtl_log_interface_err(
1413 cr.organization_id,
1414 user_id,
1415 login_id,
1416 prog_appid,
1417 prog_id,
1418 request_id,
1419 cr.TRANSACTION_ID,
1420 error_msg,
1421 'OUTSOURCED_ASSEMBLY',
1422 'MTL_SYSTEM_ITEMS_INTERFACE',
1423 'INV_INVALID_ATTR_COL_VALUE',
1424 err_text );
1425 IF dumm_status < 0 THEN
1426 RAISE LOGGING_ERR;
1427 END IF;
1428 STATUS := 1;
1429 END IF;
1430 IF l_inv_debug_level IN(101, 102) THEN
1431 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,SHIKYU enabled....');
1432 END IF;
1433 IF ((NVL(l_SHIKYU_profile,'N') = 'N') AND
1434 cr.OUTSOURCED_ASSEMBLY = 1) THEN
1435 dumm_status := INVPUOPI.mtl_log_interface_err(
1436 cr.organization_id,
1437 user_id,
1438 login_id,
1439 prog_appid,
1440 prog_id,
1441 request_id,
1442 cr.TRANSACTION_ID,
1443 error_msg,
1444 'OUTSOURCED_ASSEMBLY',
1445 'MTL_SYSTEM_ITEMS_INTERFACE',
1446 'INV_OS_ASMBLY_NO_JMF_PROFILE',
1447 err_text );
1448 IF dumm_status < 0 THEN
1449 RAISE LOGGING_ERR;
1450 END IF;
1451 STATUS := 1;
1452 END IF;
1453 IF l_inv_debug_level IN(101, 102) THEN
1454 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,Release time fence....');
1455 END IF;
1456
1457 IF (NVL(cr.RELEASE_TIME_FENCE_CODE,6) <> 7 AND l_trading_partner_org = 'Y'
1458 AND cr.OUTSOURCED_ASSEMBLY = 1) THEN
1459 dumm_status := INVPUOPI.mtl_log_interface_err(
1460 cr.organization_id,
1461 user_id,
1462 login_id,
1463 prog_appid,
1464 prog_id,
1465 request_id,
1466 cr.TRANSACTION_ID,
1467 error_msg,
1468 'OUTSOURCED_ASSEMBLY',
1469 'MTL_SYSTEM_ITEMS_INTERFACE',
1470 'INV_OS_ASMBLY_TP_TIME_FENSE',
1471 err_text );
1472 IF dumm_status < 0 THEN
1473 RAISE LOGGING_ERR;
1474 END IF;
1475 STATUS := 1;
1476 END IF;
1477
1478 IF l_inv_debug_level IN(101, 102) THEN
1479 INVPUTLI.info('INVPVDR7: verifying outsourced assembly, organizations......');
1480 END IF;
1481
1482 --
1483 -- bug 13822871.
1484 -- The restriction on EAM and WMS enabled orgs has to be relaxed
1485 --
1486 -- IF( NOT(l_wms_enabled = 'N' AND l_process_enabled = 'N' AND l_eam_enabled = 'N')AND
1487 IF( NOT(l_process_enabled = 'N')AND
1488 cr.OUTSOURCED_ASSEMBLY = 1) THEN
1489 dumm_status := INVPUOPI.mtl_log_interface_err(
1490 cr.organization_id,
1491 user_id,
1492 login_id,
1493 prog_appid,
1494 prog_id,
1495 request_id,
1496 cr.TRANSACTION_ID,
1497 error_msg,
1498 'OUTSOURCED_ASSEMBLY',
1499 'MTL_SYSTEM_ITEMS_INTERFACE',
1500 'INV_OS_ASMBLY_INVALID_ORG',
1501 err_text );
1502 IF dumm_status < 0 THEN
1503 RAISE LOGGING_ERR;
1504 END IF;
1505 STATUS := 1;
1506 END IF;
1507
1508 IF l_inv_debug_level IN(101, 102) THEN
1509 INVPUTLI.info('INVPVDR7: verifying outsourced assembly.cost method....');
1510 END IF;
1511 --
1512 -- Fix for bug#6447581
1513 -- Outsourced Assembly and Costing Method validation is commented.
1514 --
1515 -- IF(l_cost_method <> 1 AND cr.OUTSOURCED_ASSEMBLY = 1) THEN
1516 -- dumm_status := INVPUOPI.mtl_log_interface_err(
1517 -- cr.organization_id,
1518 -- user_id,
1519 -- login_id,
1520 -- prog_appid,
1521 -- prog_id,
1522 -- request_id,
1523 -- cr.TRANSACTION_ID,
1524 -- error_msg,
1525 -- 'OUTSOURCED_ASSEMBLY',
1526 -- 'MTL_SYSTEM_ITEMS_INTERFACE',
1527 -- 'INV_OS_ASMBLY_STD_COST_ORG',
1528 -- err_text );
1529 -- IF dumm_status < 0 THEN
1530 -- RAISE LOGGING_ERR;
1531 -- END IF;
1532 -- STATUS := 1;
1533 -- END IF;
1534 --
1535 IF l_inv_debug_level IN(101, 102) THEN
1536 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,bom item type....');
1537 END IF;
1538 IF( NOT(cr.BOM_ITEM_TYPE = 4 AND cr.EFFECTIVITY_CONTROL = 1)AND
1539 (cr.OUTSOURCED_ASSEMBLY = 1)) THEN
1540 dumm_status := INVPUOPI.mtl_log_interface_err(
1541 cr.organization_id,
1542 user_id,
1543 login_id,
1544 prog_appid,
1545 prog_id,
1546 request_id,
1547 cr.TRANSACTION_ID,
1548 error_msg,
1549 'OUTSOURCED_ASSEMBLY',
1550 'MTL_SYSTEM_ITEMS_INTERFACE',
1551 'INV_OS_ASMBLY_INVALID_BOM_ATTR',
1552 err_text );
1553 IF dumm_status < 0 THEN
1554 RAISE LOGGING_ERR;
1555 END IF;
1556 STATUS := 1;
1557 END IF;
1558
1559 --Bug: 5139950
1560 --Bom item type can be 5 (product family) only if inventory flag is Y
1561 --and planning_make_buy_code is 1 (Make)
1562 IF( cr.BOM_ITEM_TYPE = 5 and cr.INVENTORY_ITEM_FLAG <> 'Y') THEN
1563 dumm_status := INVPUOPI.mtl_log_interface_err(
1564 cr.organization_id,
1565 user_id,
1566 login_id,
1567 prog_appid,
1568 prog_id,
1569 request_id,
1570 cr.transaction_id,
1571 error_msg,
1572 'INVENTORY_ITEM_FLAG',
1573 'MTL_SYSTEM_ITEMS_INTERFACE',
1574 'INV_BOM_ITEM_TYPE_PF_INV',
1575 err_text);
1576 IF dumm_status < 0 THEN
1577 RAISE LOGGING_ERR;
1578 END IF;
1579 STATUS := 1;
1580 END IF;
1581 --Bug: 5139950
1582 --Bom item type can be 5 (product family) only if inventory flag is Y
1583 --and planning_make_buy_code is 1 (Make)
1584 IF (cr.BOM_ITEM_TYPE = 5 AND cr.INVENTORY_ITEM_FLAG = 'Y' AND cr.PLANNING_MAKE_BUY_CODE <> 1)THEN
1585 dumm_status := INVPUOPI.mtl_log_interface_err(
1586 cr.organization_id,
1587 user_id,
1588 login_id,
1589 prog_appid,
1590 prog_id,
1591 request_id,
1592 cr.transaction_id,
1593 error_msg,
1594 'PLANNING_MAKE_BUY_CODE',
1595 'MTL_SYSTEM_ITEMS_INTERFACE',
1596 'INV_PLANNING_MAKE_BUY_CODE',
1597 err_text);
1598 IF dumm_status < 0 THEN
1599 RAISE LOGGING_ERR;
1600 END IF;
1601 STATUS := 1;
1602 END IF;
1603
1604 IF l_inv_debug_level IN(101, 102) THEN
1605 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,outside operation....');
1606 END IF;
1607
1608 IF(cr.OUTSIDE_OPERATION_FLAG = 'Y' AND cr.OUTSOURCED_ASSEMBLY = 1)
1609 THEN
1610 dumm_status := INVPUOPI.mtl_log_interface_err(
1611 cr.organization_id,
1612 user_id,
1613 login_id,
1614 prog_appid,
1615 prog_id,
1616 request_id,
1617 cr.TRANSACTION_ID,
1618 error_msg,
1619 'OUTSOURCED_ASSEMBLY',
1620 'MTL_SYSTEM_ITEMS_INTERFACE',
1621 'INV_OS_ASMBLY_OUTSIDE_OPRN',
1622 err_text );
1623 IF dumm_status < 0 THEN
1624 RAISE LOGGING_ERR;
1625 END IF;
1626 STATUS := 1;
1627 END IF;
1628
1629 IF((/*cr.INTERNAL_ORDER_FLAG = 'Y' OR cr.INTERNAL_ORDER_ENABLED_FLAG = 'Y' OR*/ -- Bug 9246127
1630 cr.PICK_COMPONENTS_FLAG ='Y' OR cr.REPLENISH_TO_ORDER_FLAG = 'Y')AND
1631 cr.OUTSOURCED_ASSEMBLY = 1) THEN
1632 dumm_status := INVPUOPI.mtl_log_interface_err(
1633 cr.organization_id,
1634 user_id,
1635 login_id,
1636 prog_appid,
1637 prog_id,
1638 request_id,
1639 cr.TRANSACTION_ID,
1640 error_msg,
1641 'OUTSOURCED_ASSEMBLY',
1642 'MTL_SYSTEM_ITEMS_INTERFACE',
1643 'INV_OS_ASMBLY_INVALID_OM_ATTR',
1644 err_text );
1645 IF dumm_status < 0 THEN
1646 RAISE LOGGING_ERR;
1647 END IF;
1648 STATUS := 1;
1649 END IF;
1650 IF l_inv_debug_level IN(101, 102) THEN
1651 INVPUTLI.info('INVPVDR7: verifying subcontracting component,SHIKYU....');
1652 END IF;
1653
1654 IF((NVL(l_SHIKYU_profile,'N') = 'N') AND
1655 cr.SUBCONTRACTING_COMPONENT IS NOT NULL)
1656 THEN
1657 dumm_status := INVPUOPI.mtl_log_interface_err(
1658 cr.organization_id,
1659 user_id,
1660 login_id,
1661 prog_appid,
1662 prog_id,
1663 request_id,
1664 cr.TRANSACTION_ID,
1665 error_msg,
1666 'SUBCONTRACTING_COMPONENT',
1667 'MTL_SYSTEM_ITEMS_INTERFACE',
1668 'INV_SUBCONTR_COMP_NO_JMF_PRFL',
1669 err_text );
1670 IF dumm_status < 0 THEN
1671 RAISE LOGGING_ERR;
1672 END IF;
1673 STATUS := 1;
1674 END IF;
1675
1676 IF l_inv_debug_level IN(101, 102) THEN
1677 INVPUTLI.info('INVPVDR7: verifying subcontracting component organizations....');
1678 END IF;
1679
1680 --
1681 -- bug 13822871.
1682 -- The restriction on EAM and WMS enabled orgs has to be relaxed
1683 --
1684 -- IF(NOT(l_wms_enabled = 'N' AND l_process_enabled = 'N' AND l_eam_enabled = 'N')AND
1685 IF(NOT(l_process_enabled = 'N')AND
1686 cr.SUBCONTRACTING_COMPONENT IS NOT NULL) THEN
1687 dumm_status := INVPUOPI.mtl_log_interface_err(
1688 cr.organization_id,
1689 user_id,
1690 login_id,
1691 prog_appid,
1692 prog_id,
1693 request_id,
1694 cr.TRANSACTION_ID,
1695 error_msg,
1696 'SUBCONTRACTING_COMPONENT',
1697 'MTL_SYSTEM_ITEMS_INTERFACE',
1698 'INV_OS_ASMBLY_INVALID_ORG',
1699 err_text );
1700 IF dumm_status < 0 THEN
1701 RAISE LOGGING_ERR;
1702 END IF;
1703 STATUS := 1;
1704 END IF;
1705
1706 --Bug8687179
1707 --commenting the following validation.
1708 /*IF(l_cost_method <> 1 AND cr.SUBCONTRACTING_COMPONENT IS NOT NULL) THEN
1709 dumm_status := INVPUOPI.mtl_log_interface_err(
1710 cr.organization_id,
1711 user_id,
1712 login_id,
1713 prog_appid,
1714 prog_id,
1715 request_id,
1716 cr.TRANSACTION_ID,
1717 error_msg,
1718 'SUBCONTRACTING_COMPONENT',
1719 'MTL_SYSTEM_ITEMS_INTERFACE',
1720 'INV_SUBCONTR_COMP_STD_COST_ORG',
1721 err_text );
1722 IF dumm_status < 0 THEN
1723 RAISE LOGGING_ERR;
1724 END IF;
1725 STATUS := 1;
1726 END IF;
1727 */
1728
1729 -------------------------------------------------------------------------
1730 /* New code to update process_flag.
1731 ** This code necessiated due to the breaking up INVPVHDR into
1732 ** 6 smaller packages to overcome PL/SQL limitations
1733 ** with code size.
1734 ** Let's update the process flag for the record
1735 ** Give it value 42 if all okay and 32 if some
1736 ** validation failed in this procedure
1737 ** Need to do this ONLY if all previous validation okay.
1738 ** The process flag values that are possible at this time are
1739 ** 31 :set by INVPVHDR
1740 ** 32 :set by INVPVDR2
1741 ** 33 :set by INVPVDR3
1742 ** 34 :set by INVPVDR4
1743 ** 35, 45 :set by INVPVDR5
1744 ** 36, 46 :set by INVPVDR7
1745 */
1746
1747 /* Bug 4705184
1748 select process_flag into temp_proc_flag
1749 from MTL_SYSTEM_ITEMS_INTERFACE
1750 where inventory_item_id = l_item_id
1751 and set_process_id + 0 = xset_id
1752 and process_flag in (3,31,32,33,34,35,36,46) --3571136
1753 and organization_id = cr.organization_id
1754 and rownum < 2; */
1755
1756 /* set value of process_flag to 4 or 3
1757 ** depending on value of the variable: status.
1758 ** NOTE: not 47, 37..this is the FINAL set of
1759 ** validations. All the values 31-35 and 41-45 were
1760 ** just temporary values.
1761 ** Essentially, we check to see if validation has not
1762 ** already failed in one of the previous packages.
1763 */
1764
1765 if (temp_proc_flag = 46 ) then
1766 /*The validations are all clear up until this package*/
1767
1768 update MTL_SYSTEM_ITEMS_INTERFACE
1769 set process_flag = DECODE(status,0,4,3),
1770 PRIMARY_UOM_CODE = cr.primary_uom_code,
1771 primary_unit_of_measure = cr.primary_unit_of_measure
1772 where inventory_item_id = l_item_id
1773 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
1774 and set_process_id = xset_id
1775 and process_flag = 46
1776 and organization_id = cr.organization_id;
1777
1778 update MTL_ITEM_CATEGORIES_INTERFACE
1779 set process_flag = DECODE(status,0,4,3)
1780 where process_flag = 4
1781 and set_process_id = xset_id
1782 and inventory_item_id = l_item_id
1783 and organization_id = cr.organization_id;
1784
1785 else
1786 /*there is some validation problem somewhere: process
1787 **flag is one of 31,32,33,34,35,36:
1788 **Set it to 3 unconditionally*/
1789
1790 update MTL_SYSTEM_ITEMS_INTERFACE
1791 set process_flag = 3,
1792 PRIMARY_UOM_CODE = cr.primary_uom_code,
1793 primary_unit_of_measure = cr.primary_unit_of_measure,
1794 request_id = reqst_id,
1795 program_application_id = nvl(program_application_id,prog_appid)
1796 ,
1797 PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
1798 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1799 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1800 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1801 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1802 CREATED_BY = nvl(CREATED_BY,user_id),
1803 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1804 where inventory_item_id = l_item_id
1805 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
1806 and set_process_id = xset_id
1807 and process_flag in (31,32,33,34,35,36)
1808 and organization_id = cr.organization_id;
1809
1810 /*NP 04/11/95 Also need to reset process_flag = 3 for child
1811 **reset process_flag = 3 for table
1812 **mtl_item_categories_interface
1813 **and set process_flag = 3 for mtl_item_revisions_interface
1814 ** for the relevant inventory_item_id
1815 */
1816 update MTL_ITEM_CATEGORIES_INTERFACE
1817 set process_flag = l_process_flag_3,
1818 request_id = reqst_id,
1819 program_application_id = nvl(program_application_id,prog_appid)
1820 ,
1821 PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
1822 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1823 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1824 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1825 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1826 CREATED_BY = nvl(CREATED_BY,user_id),
1827 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1828 where process_flag = l_process_flag_4
1829 and set_process_id = xset_id
1830 and inventory_item_id = l_item_id
1831 and organization_id = cr.organization_id;
1832
1833 /*SETID: not setid related: what about revisions_interface here??*/
1834
1835 end if;
1836
1837
1838 end loop;
1839
1840 return(0);
1841
1842 exception
1843 when LOGGING_ERR then
1844 return(dumm_status);
1845
1846 when VALIDATE_ERR then
1847 dumm_status := INVPUOPI.mtl_log_interface_err(
1848 l_org_id,
1849 user_id,
1850 login_id,
1851 prog_appid,
1852 prog_id,
1853 request_id,
1854 trans_id,
1855 err_text,
1856 'validation_error ' || stmt,
1857 'MTL_SYSTEM_ITEMS_INTERFACE',
1858 'BOM_OP_VALIDATION_ERR',
1859 err_text);
1860 return(status);
1861 when OTHERS then
1862 err_text := substr('INVPVDR7.validate_item_header7' || SQLERRM, 1,240);
1863 return(SQLCODE);
1864
1865 end validate_item_header7;
1866
1867 end INVPVDR7;