[Home] [Help]
PACKAGE BODY: APPS.INVPVDR7
Source
1 PACKAGE BODY INVPVDR7 AS
2 /* $Header: INVPVD7B.pls 120.15 2007/12/18 00:04:27 mshirkol 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 + 0 = org_id) or
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
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)
403 when NO_DATA_FOUND then
400 and ENABLED_FLAG = 'Y';
401
402 exception
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
528 user_id,
525 nvl(cr.child_lot_flag,'N') = 'Y' then
526 dumm_status := INVPUOPI.mtl_log_interface_err(
527 cr.organization_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',
642 END IF;
639 err_text );
640 IF dumm_status < 0 THEN
641 raise LOGGING_ERR;
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
739 exception
736 and i.ORGANIZATION_ID = cr.ORGANIZATION_ID
737 and i.SECONDARY_INVENTORY = cr.PROCESS_SUPPLY_SUBINVENTORY;
738
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
814 user_id,
811 AND cr.PROCESS_SUPPLY_LOCATOR_ID IS NULL then
812 dumm_status := INVPUOPI.mtl_log_interface_err(
813 cr.organization_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,
883 cr.TRANSACTION_ID,
880 prog_appid,
881 prog_id,
882 request_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,
1007 err_text);
1004 'PROCESS_YIELD_LOCATOR_ID',
1005 'MTL_SYSTEM_ITEMS_INTERFACE',
1006 'INV_IOI_PROCESS_YLD_LOC_ID',
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;
1133 end if;
1130 end if;
1131 status := 1;
1132 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',
1214 if dumm_status < 0 then
1211 'MTL_SYSTEM_ITEMS_INTERFACE',
1212 'INV_INVALID_HOLD_DAYS',
1213 err_text);
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;
1281 IF l_inv_debug_level IN(101, 102) THEN
1278 STATUS := 1;
1279 END;
1280 END IF;
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',--Bug 5216657
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,
1395 request_id,
1392 login_id,
1393 prog_appid,
1394 prog_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,
1468 'OUTSOURCED_ASSEMBLY',
1465 request_id,
1466 cr.TRANSACTION_ID,
1467 error_msg,
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 IF( NOT(l_wms_enabled = 'N' AND l_process_enabled = 'N' AND l_eam_enabled = 'N')AND
1483 cr.OUTSOURCED_ASSEMBLY = 1) THEN
1484 dumm_status := INVPUOPI.mtl_log_interface_err(
1485 cr.organization_id,
1486 user_id,
1487 login_id,
1488 prog_appid,
1489 prog_id,
1490 request_id,
1491 cr.TRANSACTION_ID,
1492 error_msg,
1493 'OUTSOURCED_ASSEMBLY',
1494 'MTL_SYSTEM_ITEMS_INTERFACE',
1495 'INV_OS_ASMBLY_INVALID_ORG',
1496 err_text );
1497 IF dumm_status < 0 THEN
1498 RAISE LOGGING_ERR;
1499 END IF;
1500 STATUS := 1;
1501 END IF;
1502
1503 IF l_inv_debug_level IN(101, 102) THEN
1504 INVPUTLI.info('INVPVDR7: verifying outsourced assembly.cost method....');
1505 END IF;
1506 --
1507 -- Fix for bug#6447581
1508 -- Outsourced Assembly and Costing Method validation is commented.
1509 --
1510 -- IF(l_cost_method <> 1 AND cr.OUTSOURCED_ASSEMBLY = 1) THEN
1511 -- dumm_status := INVPUOPI.mtl_log_interface_err(
1512 -- cr.organization_id,
1513 -- user_id,
1514 -- login_id,
1515 -- prog_appid,
1516 -- prog_id,
1517 -- request_id,
1518 -- cr.TRANSACTION_ID,
1519 -- error_msg,
1520 -- 'OUTSOURCED_ASSEMBLY',
1521 -- 'MTL_SYSTEM_ITEMS_INTERFACE',
1522 -- 'INV_OS_ASMBLY_STD_COST_ORG',
1523 -- err_text );
1524 -- IF dumm_status < 0 THEN
1525 -- RAISE LOGGING_ERR;
1526 -- END IF;
1527 -- STATUS := 1;
1528 -- END IF;
1529 --
1530 IF l_inv_debug_level IN(101, 102) THEN
1531 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,bom item type....');
1532 END IF;
1533 IF( NOT(cr.BOM_ITEM_TYPE = 4 AND cr.EFFECTIVITY_CONTROL = 1)AND
1534 (cr.OUTSOURCED_ASSEMBLY = 1)) THEN
1535 dumm_status := INVPUOPI.mtl_log_interface_err(
1536 cr.organization_id,
1537 user_id,
1538 login_id,
1539 prog_appid,
1540 prog_id,
1541 request_id,
1542 cr.TRANSACTION_ID,
1543 error_msg,
1544 'OUTSOURCED_ASSEMBLY',
1545 'MTL_SYSTEM_ITEMS_INTERFACE',
1546 'INV_OS_ASMBLY_INVALID_BOM_ATTR',
1547 err_text );
1548 IF dumm_status < 0 THEN
1549 RAISE LOGGING_ERR;
1550 END IF;
1551 STATUS := 1;
1552 END IF;
1553
1554 --Bug: 5139950
1555 --Bom item type can be 5 (product family) only if inventory flag is Y
1556 --and planning_make_buy_code is 1 (Make)
1557 IF( cr.BOM_ITEM_TYPE = 5 and cr.INVENTORY_ITEM_FLAG <> 'Y') THEN
1558 dumm_status := INVPUOPI.mtl_log_interface_err(
1559 cr.organization_id,
1560 user_id,
1561 login_id,
1562 prog_appid,
1563 prog_id,
1564 request_id,
1565 cr.transaction_id,
1566 error_msg,
1567 'INVENTORY_ITEM_FLAG',
1568 'MTL_SYSTEM_ITEMS_INTERFACE',
1569 'INV_BOM_ITEM_TYPE_PF_INV',
1570 err_text);
1571 IF dumm_status < 0 THEN
1572 RAISE LOGGING_ERR;
1573 END IF;
1574 STATUS := 1;
1575 END IF;
1576 --Bug: 5139950
1577 --Bom item type can be 5 (product family) only if inventory flag is Y
1578 --and planning_make_buy_code is 1 (Make)
1579 IF (cr.BOM_ITEM_TYPE = 5 AND cr.INVENTORY_ITEM_FLAG = 'Y' AND cr.PLANNING_MAKE_BUY_CODE <> 1)THEN
1580 dumm_status := INVPUOPI.mtl_log_interface_err(
1581 cr.organization_id,
1582 user_id,
1583 login_id,
1584 prog_appid,
1585 prog_id,
1589 'PLANNING_MAKE_BUY_CODE',
1586 request_id,
1587 cr.transaction_id,
1588 error_msg,
1590 'MTL_SYSTEM_ITEMS_INTERFACE',
1591 'INV_PLANNING_MAKE_BUY_CODE',
1592 err_text);
1593 IF dumm_status < 0 THEN
1594 RAISE LOGGING_ERR;
1595 END IF;
1596 STATUS := 1;
1597 END IF;
1598
1599 IF l_inv_debug_level IN(101, 102) THEN
1600 INVPUTLI.info('INVPVDR7: verifying outsourced assembly,outside operation....');
1601 END IF;
1602
1603 IF(cr.OUTSIDE_OPERATION_FLAG = 'Y' AND cr.OUTSOURCED_ASSEMBLY = 1)
1604 THEN
1605 dumm_status := INVPUOPI.mtl_log_interface_err(
1606 cr.organization_id,
1607 user_id,
1608 login_id,
1609 prog_appid,
1610 prog_id,
1611 request_id,
1612 cr.TRANSACTION_ID,
1613 error_msg,
1614 'OUTSOURCED_ASSEMBLY',
1615 'MTL_SYSTEM_ITEMS_INTERFACE',
1616 'INV_OS_ASMBLY_OUTSIDE_OPRN',
1617 err_text );
1618 IF dumm_status < 0 THEN
1619 RAISE LOGGING_ERR;
1620 END IF;
1621 STATUS := 1;
1622 END IF;
1623
1624 IF((cr.INTERNAL_ORDER_FLAG = 'Y' OR cr.INTERNAL_ORDER_ENABLED_FLAG = 'Y' OR
1625 cr.PICK_COMPONENTS_FLAG ='Y' OR cr.REPLENISH_TO_ORDER_FLAG = 'Y')AND
1626 cr.OUTSOURCED_ASSEMBLY = 1) THEN
1627 dumm_status := INVPUOPI.mtl_log_interface_err(
1628 cr.organization_id,
1629 user_id,
1630 login_id,
1631 prog_appid,
1632 prog_id,
1633 request_id,
1634 cr.TRANSACTION_ID,
1635 error_msg,
1636 'OUTSOURCED_ASSEMBLY',
1637 'MTL_SYSTEM_ITEMS_INTERFACE',
1638 'INV_OS_ASMBLY_INVALID_OM_ATTR',
1639 err_text );
1640 IF dumm_status < 0 THEN
1641 RAISE LOGGING_ERR;
1642 END IF;
1643 STATUS := 1;
1644 END IF;
1645 IF l_inv_debug_level IN(101, 102) THEN
1646 INVPUTLI.info('INVPVDR7: verifying subcontracting component,SHIKYU....');
1647 END IF;
1648
1649 IF((NVL(l_SHIKYU_profile,'N') = 'N') AND
1650 cr.SUBCONTRACTING_COMPONENT IS NOT NULL)
1651 THEN
1652 dumm_status := INVPUOPI.mtl_log_interface_err(
1653 cr.organization_id,
1654 user_id,
1655 login_id,
1656 prog_appid,
1657 prog_id,
1658 request_id,
1659 cr.TRANSACTION_ID,
1660 error_msg,
1661 'SUBCONTRACTING_COMPONENT',
1662 'MTL_SYSTEM_ITEMS_INTERFACE',
1663 'INV_SUBCONTR_COMP_NO_JMF_PRFL',
1664 err_text );
1665 IF dumm_status < 0 THEN
1666 RAISE LOGGING_ERR;
1667 END IF;
1668 STATUS := 1;
1669 END IF;
1670
1671 IF l_inv_debug_level IN(101, 102) THEN
1672 INVPUTLI.info('INVPVDR7: verifying subcontracting component organizations....');
1673 END IF;
1674 IF(NOT(l_wms_enabled = 'N' AND l_process_enabled = 'N' AND l_eam_enabled = 'N')AND
1675 cr.SUBCONTRACTING_COMPONENT IS NOT NULL) THEN
1676 dumm_status := INVPUOPI.mtl_log_interface_err(
1677 cr.organization_id,
1678 user_id,
1679 login_id,
1680 prog_appid,
1681 prog_id,
1682 request_id,
1683 cr.TRANSACTION_ID,
1684 error_msg,
1685 'SUBCONTRACTING_COMPONENT',
1686 'MTL_SYSTEM_ITEMS_INTERFACE',
1687 'INV_OS_ASMBLY_INVALID_ORG',
1688 err_text );
1689 IF dumm_status < 0 THEN
1690 RAISE LOGGING_ERR;
1691 END IF;
1692 STATUS := 1;
1693 END IF;
1694
1695 IF(l_cost_method <> 1 AND cr.SUBCONTRACTING_COMPONENT IS NOT NULL) THEN
1696 dumm_status := INVPUOPI.mtl_log_interface_err(
1697 cr.organization_id,
1698 user_id,
1699 login_id,
1700 prog_appid,
1704 error_msg,
1701 prog_id,
1702 request_id,
1703 cr.TRANSACTION_ID,
1705 'SUBCONTRACTING_COMPONENT',
1706 'MTL_SYSTEM_ITEMS_INTERFACE',
1707 'INV_SUBCONTR_COMP_STD_COST_ORG',
1708 err_text );
1709 IF dumm_status < 0 THEN
1710 RAISE LOGGING_ERR;
1711 END IF;
1712 STATUS := 1;
1713 END IF;
1714
1715
1716 -------------------------------------------------------------------------
1717 /* New code to update process_flag.
1718 ** This code necessiated due to the breaking up INVPVHDR into
1719 ** 6 smaller packages to overcome PL/SQL limitations
1720 ** with code size.
1721 ** Let's update the process flag for the record
1722 ** Give it value 42 if all okay and 32 if some
1723 ** validation failed in this procedure
1724 ** Need to do this ONLY if all previous validation okay.
1725 ** The process flag values that are possible at this time are
1726 ** 31 :set by INVPVHDR
1727 ** 32 :set by INVPVDR2
1728 ** 33 :set by INVPVDR3
1729 ** 34 :set by INVPVDR4
1730 ** 35, 45 :set by INVPVDR5
1731 ** 36, 46 :set by INVPVDR7
1732 */
1733
1734 /* Bug 4705184
1735 select process_flag into temp_proc_flag
1736 from MTL_SYSTEM_ITEMS_INTERFACE
1737 where inventory_item_id = l_item_id
1738 and set_process_id + 0 = xset_id
1739 and process_flag in (3,31,32,33,34,35,36,46) --3571136
1740 and organization_id = cr.organization_id
1741 and rownum < 2; */
1742
1743 /* set value of process_flag to 4 or 3
1744 ** depending on value of the variable: status.
1745 ** NOTE: not 47, 37..this is the FINAL set of
1746 ** validations. All the values 31-35 and 41-45 were
1747 ** just temporary values.
1748 ** Essentially, we check to see if validation has not
1749 ** already failed in one of the previous packages.
1750 */
1751
1752 if (temp_proc_flag = 46 ) then
1753 /*The validations are all clear up until this package*/
1754
1755 update MTL_SYSTEM_ITEMS_INTERFACE
1756 set process_flag = DECODE(status,0,4,3),
1757 PRIMARY_UOM_CODE = cr.primary_uom_code,
1758 primary_unit_of_measure = cr.primary_unit_of_measure
1759 where inventory_item_id = l_item_id
1760 and set_process_id + 0 = xset_id
1761 and process_flag = 46
1762 and organization_id = cr.organization_id;
1763
1764 update MTL_ITEM_CATEGORIES_INTERFACE
1765 set process_flag = DECODE(status,0,4,3)
1766 where process_flag = 4
1767 and set_process_id = xset_id
1768 and inventory_item_id = l_item_id
1769 and organization_id = cr.organization_id;
1770
1771 else
1772 /*there is some validation problem somewhere: process
1773 **flag is one of 31,32,33,34,35,36:
1774 **Set it to 3 unconditionally*/
1775
1776 update MTL_SYSTEM_ITEMS_INTERFACE
1777 set process_flag = 3,
1778 PRIMARY_UOM_CODE = cr.primary_uom_code,
1779 primary_unit_of_measure = cr.primary_unit_of_measure,
1780 request_id = reqst_id,
1781 program_application_id = nvl(program_application_id,prog_appid)
1782 ,
1783 PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
1784 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1785 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1786 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1787 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1788 CREATED_BY = nvl(CREATED_BY,user_id),
1789 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1790 where inventory_item_id = l_item_id
1791 and set_process_id + 0 = xset_id
1792 and process_flag in (31,32,33,34,35,36)
1793 and organization_id = cr.organization_id;
1794
1795 /*NP 04/11/95 Also need to reset process_flag = 3 for child
1796 **reset process_flag = 3 for table
1797 **mtl_item_categories_interface
1798 **and set process_flag = 3 for mtl_item_revisions_interface
1799 ** for the relevant inventory_item_id
1800 */
1801 update MTL_ITEM_CATEGORIES_INTERFACE
1802 set process_flag = l_process_flag_3,
1803 request_id = reqst_id,
1804 program_application_id = nvl(program_application_id,prog_appid)
1805 ,
1806 PROGRAM_ID = nvl(PROGRAM_ID,prog_id),
1807 PROGRAM_UPDATE_DATE = nvl(PROGRAM_UPDATE_DATE,sysdate),
1808 LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,sysdate),
1809 LAST_UPDATED_BY = nvl(LAST_UPDATED_BY,user_id),
1810 CREATION_DATE = nvl(CREATION_DATE,sysdate),
1811 CREATED_BY = nvl(CREATED_BY,user_id),
1812 LAST_UPDATE_LOGIN = nvl(LAST_UPDATE_LOGIN,login_id)
1813 where process_flag = l_process_flag_4
1814 and set_process_id = xset_id
1815 and inventory_item_id = l_item_id
1816 and organization_id = cr.organization_id;
1817
1818 /*SETID: not setid related: what about revisions_interface here??*/
1819
1820 end if;
1821
1822
1823 end loop;
1824
1825 return(0);
1826
1827 exception
1828 when LOGGING_ERR then
1829 return(dumm_status);
1830
1831 when VALIDATE_ERR then
1832 dumm_status := INVPUOPI.mtl_log_interface_err(
1833 l_org_id,
1834 user_id,
1835 login_id,
1836 prog_appid,
1837 prog_id,
1838 request_id,
1839 trans_id,
1840 err_text,
1841 'validation_error ' || stmt,
1842 'MTL_SYSTEM_ITEMS_INTERFACE',
1843 'BOM_OP_VALIDATION_ERR',
1844 err_text);
1845 return(status);
1846 when OTHERS then
1847 err_text := substr('INVPVDR7.validate_item_header7' || SQLERRM, 1,240);
1848 return(SQLCODE);
1849
1850 end validate_item_header7;
1851
1852 end INVPVDR7;