[Home] [Help]
PACKAGE BODY: APPS.INVPUPI2
Source
1 PACKAGE BODY INVPUPI2 AS
2 /* $Header: INVPUP2B.pls 120.4 2007/04/12 07:43:16 anmurali ship $ */
3
4 FUNCTION validate_flags(
5 org_id IN NUMBER
6 ,all_org IN NUMBER := 2
7 ,prog_appid IN NUMBER := -1
8 ,prog_id IN NUMBER := -1
9 ,request_id IN NUMBER := -1
10 ,user_id IN NUMBER := -1
11 ,login_id IN NUMBER := -1
12 ,xset_id IN NUMBER := -999
13 ,err_text IN OUT NOCOPY VARCHAR2
14 )
15 RETURN INTEGER IS
16
17 CURSOR cc IS
18 SELECT INVENTORY_ITEM_ID,
19 TRANSACTION_ID,
20 ORGANIZATION_ID,
21 PICK_COMPONENTS_FLAG,
22 PURCHASING_ITEM_FLAG,
23 SHIPPABLE_ITEM_FLAG,
24 CUSTOMER_ORDER_FLAG,
25 INTERNAL_ORDER_FLAG,
26 INVENTORY_ITEM_FLAG,
27 ENG_ITEM_FLAG,
28 INVENTORY_ASSET_FLAG,
29 PURCHASING_ENABLED_FLAG,
30 CUSTOMER_ORDER_ENABLED_FLAG,
31 INTERNAL_ORDER_ENABLED_FLAG,
32 SO_TRANSACTIONS_FLAG,
33 MTL_TRANSACTIONS_ENABLED_FLAG,
34 STOCK_ENABLED_FLAG,
35 BOM_ENABLED_FLAG,
36 BUILD_IN_WIP_FLAG,
37 CATALOG_STATUS_FLAG,
38 RETURNABLE_FLAG,
39 COLLATERAL_FLAG,
40 TAXABLE_FLAG,
41 ALLOW_ITEM_DESC_UPDATE_FLAG,
42 INSPECTION_REQUIRED_FLAG,
43 RECEIPT_REQUIRED_FLAG,
44 RFQ_REQUIRED_FLAG,
45 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
46 ALLOW_UNORDERED_RECEIPTS_FLAG,
47 ALLOW_EXPRESS_DELIVERY_FLAG,
48 MRP_CALCULATE_ATP_FLAG,
49 END_ASSEMBLY_PEGGING_FLAG,
50 REPETITIVE_PLANNING_FLAG,
51 REPLENISH_TO_ORDER_FLAG,
52 ATP_COMPONENTS_FLAG,
53 ATP_FLAG,
54 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
55 ENGINEERING_ECN_CODE,
56 SERVICEABLE_PRODUCT_FLAG,
57 PREVENTIVE_MAINTENANCE_FLAG,
58 TIME_BILLABLE_FLAG,
59 MATERIAL_BILLABLE_FLAG,
60 EXPENSE_BILLABLE_FLAG,
61 PRORATE_SERVICE_FLAG,
62 SERVICE_DURATION_PERIOD_CODE,
63 INVOICEABLE_ITEM_FLAG,
64 INVOICE_ENABLED_FLAG,
65 MUST_USE_APPROVED_VENDOR_FLAG,
66 OUTSIDE_OPERATION_FLAG,
67 COSTING_ENABLED_FLAG,
68 CYCLE_COUNT_ENABLED_FLAG,
69 AUTO_CREATED_CONFIG_FLAG,
70 /*CK 18NOV98 Added new attribute*/
71 CHECK_SHORTAGES_FLAG,
72 BOM_ITEM_TYPE,
73 CONTRACT_ITEM_TYPE_CODE,
74 --R12 FPC
75 GDSN_OUTBOUND_ENABLED_FLAG,
76 STYLE_ITEM_FLAG
77 FROM MTL_SYSTEM_ITEMS_INTERFACE
78 --3515652: Performance enhancements
79 WHERE ((organization_id = org_id) or (all_Org = 1))
80 AND set_process_id = xset_id
81 AND process_flag in (31, 32, 33, 34 , 35 , 45);
82
83 --3760498: Using below query instead of Get_Material_Billable_flag
84 CURSOR c_check_billable_flag(cp_lookup_type VARCHAR2
85 ,cp_lookup_code VARCHAR2) IS
86 select meaning
87 from cs_lookups
88 where lookup_type = cp_lookup_type
89 and lookup_code = cp_lookup_code
90 and enabled_flag = 'Y';
91
92
93 dumm_status NUMBER;
94 status NUMBER := 0;
95 stmt NUMBER;
96 LOGGING_ERR EXCEPTION;
97 VALIDATE_ERR EXCEPTION;
98 error_msg VARCHAR2(70);
99 time_uom_class VARCHAR2(2000);
100 ctp_prof_val NUMBER;
101 temp_proc_flag NUMBER;
102
103 -- Added code for bug #1498054
104 l_installed BOOLEAN;
105 l_status VARCHAR2(10);
106 l_industry VARCHAR2(10);
107 bill_value VARCHAR2(2000);
108 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
109 l_prev_uom_code MTL_SYSTEM_ITEMS_INTERFACE.service_duration_period_code%TYPE := '!'; --Bug: 4654433
110 l_serv_dur_per_code_err NUMBER := 0; --Bug: 4654433
111
112
113 BEGIN
114
115 IF l_inv_debug_level IN(101, 102) THEN
116 INVPUTLI.info('Inside INVPUPI2.validate_flags');
117 END IF;
118
119 -- Retrieving fnd_profile values outside the loop for perf reasons.
120 fnd_profile.get('TIME_UOM_CLASS', time_uom_class);
121 ctp_prof_val := nvl(fnd_profile.value('INV_CTP'), 3);
122
123 FOR cr IN cc LOOP
124 -- Validate YES/NO flags
125 status := 0;
126 stmt := 1;
127 IF (cr.PURCHASING_ITEM_FLAG <> 'Y' AND
128 cr.PURCHASING_ITEM_FLAG <> 'N') THEN
129 dumm_status := INVPUOPI.mtl_log_interface_err(
130 cr.organization_id,
131 user_id,
132 login_id,
133 prog_appid,
134 prog_id,
135 request_id,
136 cr.TRANSACTION_ID,
137 error_msg,
138 'PURCHASING_ITEM_FLAG',
139 'MTL_SYSTEM_ITEMS_INTERFACE',
140 'INV_IOI_FLAG_Y_N',
141 err_text);
142 IF dumm_status < 0 THEN
143 raise LOGGING_ERR;
144 END IF;
145 status := 1;
146 END IF;
147
148 stmt := 2;
149 IF (cr.SHIPPABLE_ITEM_FLAG <> 'Y' AND
150 cr.SHIPPABLE_ITEM_FLAG <> 'N') THEN
151 dumm_status := INVPUOPI.mtl_log_interface_err(
152 cr.organization_id,
153 user_id,
154 login_id,
155 prog_appid,
156 prog_id,
157 request_id,
158 cr.TRANSACTION_ID,
159 error_msg,
160 'SHIPPABLE_ITEM_FLAG',
161 'MTL_SYSTEM_ITEMS_INTERFACE',
162 'INV_IOI_FLAG_Y_N',
163 err_text);
164 IF dumm_status < 0 THEN
165 RAISE LOGGING_ERR;
166 END IF;
167 status := 1;
168 END IF;
169
170 stmt := 3;
171 IF (cr.CUSTOMER_ORDER_FLAG <> 'Y' AND
172 cr.CUSTOMER_ORDER_FLAG <> 'N') THEN
173 dumm_status := INVPUOPI.mtl_log_interface_err(
174 cr.organization_id,
175 user_id,
176 login_id,
177 prog_appid,
178 prog_id,
179 request_id,
180 cr.TRANSACTION_ID,
181 error_msg,
182 'CUSTOMER_ORDER_FLAG',
183 'MTL_SYSTEM_ITEMS_INTERFACE',
184 'INV_IOI_FLAG_Y_N',
185 err_text);
186 IF dumm_status < 0 THEN
187 RAISE LOGGING_ERR;
188 END IF;
189 status := 1;
190 END IF;
191
192 stmt := 4;
193 if (cr.INTERNAL_ORDER_FLAG <> 'Y' and
194 cr.INTERNAL_ORDER_FLAG <> 'N') then
195 dumm_status := INVPUOPI.mtl_log_interface_err(
196 cr.organization_id,
197 user_id,
198 login_id,
199 prog_appid,
200 prog_id,
201 request_id,
202 cr.TRANSACTION_ID,
203 error_msg,
204 'INTERNAL_ORDER_FLAG',
205 'MTL_SYSTEM_ITEMS_INTERFACE',
206 'INV_IOI_FLAG_Y_N',
207 err_text);
208 if dumm_status < 0 then
209 raise LOGGING_ERR;
210 end if;
211 status := 1;
212 end if;
213 /*** Bug:2731125
214 stmt := 5;
215 if (cr.SERVICE_ITEM_FLAG <> 'Y' and
216 cr.SERVICE_ITEM_FLAG <> 'N') then
217 **/
218 stmt := 5;
219 if (cr.CONTRACT_ITEM_TYPE_CODE NOT IN ('SERVICE','WARRANTY','USAGE','SUBSCRIPTION')) then
220 dumm_status := INVPUOPI.mtl_log_interface_err(
221 cr.organization_id,
222 user_id,
223 login_id,
224 prog_appid,
225 prog_id,
226 request_id,
227 cr.TRANSACTION_ID,
228 error_msg,
229 'CONTRACT_ITEM_TYPE_CODE',
230 'MTL_SYSTEM_ITEMS_INTERFACE',
231 'INV_INVALID_ATTR_COL_VALUE',
232 err_text);
233 if dumm_status < 0 then
234 raise LOGGING_ERR;
235 end if;
236 status := 1;
237 end if;
238
239 stmt := 6;
240 if (cr.INVENTORY_ITEM_FLAG <> 'Y' and
241 cr.INVENTORY_ITEM_FLAG <> 'N') then
242 dumm_status := INVPUOPI.mtl_log_interface_err(
243 cr.organization_id,
244 user_id,
245 login_id,
246 prog_appid,
247 prog_id,
248 request_id,
249 cr.TRANSACTION_ID,
250 error_msg,
251 'INVENTORY_ITEM_FLAG',
252 'MTL_SYSTEM_ITEMS_INTERFACE',
253 'INV_IOI_FLAG_Y_N',
254 err_text);
255 if dumm_status < 0 then
256 raise LOGGING_ERR;
257 end if;
258 status := 1;
259 end if;
260
261 stmt := 7;
262 if (cr.ENG_ITEM_FLAG <> 'Y' and
263 cr.ENG_ITEM_FLAG <> 'N') then
264 dumm_status := INVPUOPI.mtl_log_interface_err(
265 cr.organization_id,
266 user_id,
267 login_id,
268 prog_appid,
269 prog_id,
270 request_id,
271 cr.TRANSACTION_ID,
272 error_msg,
273 'ENG_ITEM_FLAG',
274 'MTL_SYSTEM_ITEMS_INTERFACE',
275 'INV_IOI_FLAG_Y_N',
276 err_text);
277 if dumm_status < 0 then
278 raise LOGGING_ERR;
279 end if;
280 status := 1;
281 end if;
282
283 stmt := 8;
284 if (cr.INVENTORY_ASSET_FLAG <> 'Y' and
285 cr.INVENTORY_ASSET_FLAG <> 'N') then
286 dumm_status := INVPUOPI.mtl_log_interface_err(
287 cr.organization_id,
288 user_id,
289 login_id,
290 prog_appid,
291 prog_id,
292 request_id,
293 cr.TRANSACTION_ID,
294 error_msg,
295 'INVENTORY_ASSET_FLAG',
296 'MTL_SYSTEM_ITEMS_INTERFACE',
297 'INV_IOI_FLAG_Y_N',
298 err_text);
299 if dumm_status < 0 then
300 raise LOGGING_ERR;
301 end if;
302 status := 1;
303 end if;
304
305 stmt := 9;
306 if (cr.PURCHASING_ENABLED_FLAG <> 'Y' and
307 cr.PURCHASING_ENABLED_FLAG <> 'N') then
308 dumm_status := INVPUOPI.mtl_log_interface_err(
309 cr.organization_id,
310 user_id,
311 login_id,
312 prog_appid,
313 prog_id,
314 request_id,
315 cr.TRANSACTION_ID,
316 error_msg,
317 'PURCHASING_ENABLED_FLAG',
318 'MTL_SYSTEM_ITEMS_INTERFACE',
319 'INV_IOI_FLAG_Y_N',
320 err_text);
321 if dumm_status < 0 then
322 raise LOGGING_ERR;
323
324 end if;
325 status := 1;
326 end if;
327
328 stmt := 10;
329 if (cr.CUSTOMER_ORDER_ENABLED_FLAG <> 'Y' and
330 cr.CUSTOMER_ORDER_ENABLED_FLAG <> 'N') then
331 dumm_status := INVPUOPI.mtl_log_interface_err(
332 cr.organization_id,
333 user_id,
334 login_id,
335 prog_appid,
336 prog_id,
337 request_id,
338 cr.TRANSACTION_ID,
339 error_msg,
340 'CUSTOMER_ORDER_ENABLED_FLAG',
341 'MTL_SYSTEM_ITEMS_INTERFACE',
342 'INV_IOI_FLAG_Y_N',
343 err_text);
344 if dumm_status < 0 then
345 raise LOGGING_ERR;
346 end if;
347 status := 1;
348 end if;
349
350 stmt := 11;
351 if (cr.INTERNAL_ORDER_ENABLED_FLAG <> 'Y' and
352 cr.INTERNAL_ORDER_ENABLED_FLAG <> 'N') then
353 dumm_status := INVPUOPI.mtl_log_interface_err(
354 cr.organization_id,
355 user_id,
356 login_id,
357 prog_appid,
358 prog_id,
359 request_id,
360 cr.TRANSACTION_ID,
361 error_msg,
362 'INTERNAL_ORDER_ENABLED_FLAG',
363 'MTL_SYSTEM_ITEMS_INTERFACE',
364 'INV_IOI_FLAG_Y_N',
365 err_text);
366 if dumm_status < 0 then
367 raise LOGGING_ERR;
368 end if;
369 status := 1;
370 end if;
371
372 stmt := 12;
373 if (cr.SO_TRANSACTIONS_FLAG <> 'Y' and
374 cr.SO_TRANSACTIONS_FLAG <> 'N') then
375 dumm_status := INVPUOPI.mtl_log_interface_err(
376 cr.organization_id,
377 user_id,
378 login_id,
379 prog_appid,
380 prog_id,
381 request_id,
382 cr.TRANSACTION_ID,
383 error_msg,
384 'SO_TRANSACTIONS_FLAG',
385 'MTL_SYSTEM_ITEMS_INTERFACE',
386 'INV_IOI_FLAG_Y_N',
387 err_text);
388 if dumm_status < 0 then
389 raise LOGGING_ERR;
390 end if;
391 status := 1;
392 end if;
393
394 stmt := 13;
395 if (cr.MTL_TRANSACTIONS_ENABLED_FLAG <> 'Y' and
396 cr.MTL_TRANSACTIONS_ENABLED_FLAG <> 'N') then
397 dumm_status := INVPUOPI.mtl_log_interface_err(
398 cr.organization_id,
399 user_id,
400 login_id,
401 prog_appid,
402 prog_id,
403 request_id,
404 cr.TRANSACTION_ID,
405 error_msg,
406 'MTL_TRANSACTIONS_ENABLED_FLAG',
407 'MTL_SYSTEM_ITEMS_INTERFACE',
408 'INV_IOI_FLAG_Y_N',
409 err_text);
410 if dumm_status < 0 then
411 raise LOGGING_ERR;
412 end if;
413 status := 1;
414 end if;
415
416 stmt := 14;
417 if (cr.STOCK_ENABLED_FLAG <> 'Y' and
418 cr.STOCK_ENABLED_FLAG <> 'N') then
419 dumm_status := INVPUOPI.mtl_log_interface_err(
420 cr.organization_id,
421 user_id,
422 login_id,
423 prog_appid,
424 prog_id,
425 request_id,
426 cr.TRANSACTION_ID,
427 error_msg,
428 'STOCK_ENABLED_FLAG',
429 'MTL_SYSTEM_ITEMS_INTERFACE',
430 'INV_IOI_FLAG_Y_N',
431 err_text);
432 if dumm_status < 0 then
433 raise LOGGING_ERR;
434 end if;
435 status := 1;
436 end if;
437
438 stmt := 15;
439 if (cr.BOM_ENABLED_FLAG <> 'Y' and
440 cr.BOM_ENABLED_FLAG <> 'N') then
441 dumm_status := INVPUOPI.mtl_log_interface_err(
442 cr.organization_id,
443 user_id,
444 login_id,
445 prog_appid,
446 prog_id,
447 request_id,
448 cr.TRANSACTION_ID,
449 error_msg,
450 'BOM_ENABLED_FLAG',
451 'MTL_SYSTEM_ITEMS_INTERFACE',
452 'INV_IOI_FLAG_Y_N',
453 err_text);
454 if dumm_status < 0 then
455 raise LOGGING_ERR;
456 end if;
457 status := 1;
458 end if;
459
460 stmt := 16;
461 if (cr.BUILD_IN_WIP_FLAG <> 'Y' and
462 cr.BUILD_IN_WIP_FLAG <> 'N') then
463 dumm_status := INVPUOPI.mtl_log_interface_err(
464 cr.organization_id,
465 user_id,
466 login_id,
467 prog_appid,
468 prog_id,
469 request_id,
470 cr.TRANSACTION_ID,
471 error_msg,
472 'BUILD_IN_WIP_FLAG',
473 'MTL_SYSTEM_ITEMS_INTERFACE',
474 'INV_IOI_FLAG_Y_N',
475 err_text);
476 if dumm_status < 0 then
477 raise LOGGING_ERR;
478 end if;
479 status := 1;
480 end if;
481
482 stmt := 18;
483 if (cr.CATALOG_STATUS_FLAG <> 'Y' and
484 cr.CATALOG_STATUS_FLAG <> 'N') then
485 dumm_status := INVPUOPI.mtl_log_interface_err(
486 cr.organization_id,
487 user_id,
488 login_id,
489 prog_appid,
490 prog_id,
491 request_id,
492 cr.TRANSACTION_ID,
493 error_msg,
494 'CATALOG_STATUS_FLAG',
495 'MTL_SYSTEM_ITEMS_INTERFACE',
496 'INV_IOI_FLAG_Y_N',
497 err_text);
498 if dumm_status < 0 then
499 raise LOGGING_ERR;
500 end if;
501 status := 1;
502 end if;
503
504 stmt := 19;
505 if (cr.RETURNABLE_FLAG <> 'Y' and
506 cr.RETURNABLE_FLAG <> 'N') then
507 dumm_status := INVPUOPI.mtl_log_interface_err(
508 cr.organization_id,
509 user_id,
510 login_id,
511 prog_appid,
512 prog_id,
513 request_id,
514 cr.TRANSACTION_ID,
515 error_msg,
516 'RETURNABLE_FLAG',
517 'MTL_SYSTEM_ITEMS_INTERFACE',
518 'INV_IOI_FLAG_Y_N',
519 err_text);
520 if dumm_status < 0 then
521 raise LOGGING_ERR;
522 end if;
523 status := 1;
524 end if;
525
526 stmt := 20;
527 if (cr.COLLATERAL_FLAG <> 'Y' and
528 cr.COLLATERAL_FLAG <> 'N') then
529 dumm_status := INVPUOPI.mtl_log_interface_err(
530 cr.organization_id,
531 user_id,
532 login_id,
533 prog_appid,
534 prog_id,
535 request_id,
536 cr.TRANSACTION_ID,
537 error_msg,
538 'COLLATERAL_FLAG',
539 'MTL_SYSTEM_ITEMS_INTERFACE',
540 'INV_IOI_FLAG_Y_N',
541 err_text);
542 if dumm_status < 0 then
543 raise LOGGING_ERR;
544 end if;
545 status := 1;
546 end if;
547
548 stmt := 21;
549 if (cr.TAXABLE_FLAG <> 'Y' and
550 cr.TAXABLE_FLAG <> 'N') then
551 dumm_status := INVPUOPI.mtl_log_interface_err(
552 cr.organization_id,
553 user_id,
554 login_id,
555 prog_appid,
556 prog_id,
557 request_id,
558 cr.TRANSACTION_ID,
559 error_msg,
560 'TAXABLE_FLAG',
561 'MTL_SYSTEM_ITEMS_INTERFACE',
562 'INV_IOI_FLAG_Y_N',
563 err_text);
564 if dumm_status < 0 then
565 raise LOGGING_ERR;
566 end if;
567 status := 1;
568 end if;
569
570 stmt := 22;
571 if (cr.ALLOW_ITEM_DESC_UPDATE_FLAG <> 'Y' and
572 cr.ALLOW_ITEM_DESC_UPDATE_FLAG <> 'N') then
573 dumm_status := INVPUOPI.mtl_log_interface_err(
574 cr.organization_id,
575 user_id,
576 login_id,
577 prog_appid,
578 prog_id,
579 request_id,
580 cr.TRANSACTION_ID,
581 error_msg,
582 'ALLOW_ITEM_DESC_UPDATE_FLAG',
583 'MTL_SYSTEM_ITEMS_INTERFACE',
584 'INV_IOI_FLAG_Y_N',
585 err_text);
586 if dumm_status < 0 then
587 raise LOGGING_ERR;
588 end if;
589 status := 1;
590 end if;
591
592 stmt := 23;
593 if (cr.INSPECTION_REQUIRED_FLAG <> 'Y' and
594 cr.INSPECTION_REQUIRED_FLAG <> 'N') then
595 dumm_status := INVPUOPI.mtl_log_interface_err(
596 cr.organization_id,
597 user_id,
598 login_id,
599 prog_appid,
600 prog_id,
601 request_id,
602 cr.TRANSACTION_ID,
603 error_msg,
604 'INSPECTION_REQUIRED_FLAG',
605 'MTL_SYSTEM_ITEMS_INTERFACE',
606 'INV_IOI_FLAG_Y_N',
607 err_text);
608 if dumm_status < 0 then
609 raise LOGGING_ERR;
610 end if;
611 status := 1;
612 end if;
613
614 stmt := 24;
615 if (cr.RECEIPT_REQUIRED_FLAG <> 'Y' and
616 cr.RECEIPT_REQUIRED_FLAG <> 'N') then
617 dumm_status := INVPUOPI.mtl_log_interface_err(
618 cr.organization_id,
619 user_id,
620 login_id,
621 prog_appid,
622 prog_id,
623 request_id,
624 cr.TRANSACTION_ID,
625 error_msg,
626 'RECEIPT_REQUIRED_FLAG',
627 'MTL_SYSTEM_ITEMS_INTERFACE',
628 'INV_IOI_FLAG_Y_N',
629 err_text);
630 if dumm_status < 0 then
631 raise LOGGING_ERR;
632 end if;
633 status := 1;
634 end if;
635
636 stmt := 25;
637 if (cr.RFQ_REQUIRED_FLAG <> 'Y' and
638 cr.RFQ_REQUIRED_FLAG <> 'N') then
639 dumm_status := INVPUOPI.mtl_log_interface_err(
640 cr.organization_id,
641 user_id,
642 login_id,
643 prog_appid,
644 prog_id,
645 request_id,
646 cr.TRANSACTION_ID,
647 error_msg,
648 'RFQ_REQUIRED_FLAG',
649 'MTL_SYSTEM_ITEMS_INTERFACE',
650 'INV_IOI_FLAG_Y_N',
651 err_text);
652 if dumm_status < 0 then
653 raise LOGGING_ERR;
654 end if;
655 status := 1;
656 end if;
657
658 stmt := 26;
659 if (cr.ALLOW_SUBSTITUTE_RECEIPTS_FLAG <> 'Y' and
660 cr.ALLOW_SUBSTITUTE_RECEIPTS_FLAG <> 'N') then
661 dumm_status := INVPUOPI.mtl_log_interface_err(
662 cr.organization_id,
663 user_id,
664 login_id,
665 prog_appid,
666 prog_id,
667 request_id,
668 cr.TRANSACTION_ID,
669 error_msg,
670 'ALLOW_SUBSTITUTE_RECEIPTS_FLAG',
671 'MTL_SYSTEM_ITEMS_INTERFACE',
672 'INV_IOI_FLAG_Y_N',
673 err_text);
674 if dumm_status < 0 then
675 raise LOGGING_ERR;
676 end if;
677 status := 1;
678 end if;
679
680 stmt := 27;
681 if (cr.ALLOW_UNORDERED_RECEIPTS_FLAG <> 'Y' and
682 cr.ALLOW_UNORDERED_RECEIPTS_FLAG <> 'N') then
683 dumm_status := INVPUOPI.mtl_log_interface_err(
684 cr.organization_id,
685 user_id,
686 login_id,
687 prog_appid,
688 prog_id,
689 request_id,
690 cr.TRANSACTION_ID,
691 error_msg,
692 'ALLOW_UNORDERED_RECEIPTS_FLAG',
693 'MTL_SYSTEM_ITEMS_INTERFACE',
694 'INV_IOI_FLAG_Y_N',
695 err_text);
696 if dumm_status < 0 then
697 raise LOGGING_ERR;
698 end if;
699 status := 1;
700 end if;
701
702 stmt := 28;
703 if (cr.ALLOW_EXPRESS_DELIVERY_FLAG <> 'Y' and
704 cr.ALLOW_EXPRESS_DELIVERY_FLAG <> 'N') then
705 dumm_status := INVPUOPI.mtl_log_interface_err(
706 cr.organization_id,
707 user_id,
708 login_id,
709 prog_appid,
710 prog_id,
711 request_id,
712 cr.TRANSACTION_ID,
713 error_msg,
714 'ALLOW_EXPRESS_DELIVERY_FLAG',
715 'MTL_SYSTEM_ITEMS_INTERFACE',
716 'INV_IOI_FLAG_Y_N',
717 err_text);
718 if dumm_status < 0 then
719 raise LOGGING_ERR;
720 end if;
721 status := 1;
722 end if;
723
724 stmt := 29;
725 if (cr.MRP_CALCULATE_ATP_FLAG <> 'Y' and
726 cr.MRP_CALCULATE_ATP_FLAG <> 'N') then
727 dumm_status := INVPUOPI.mtl_log_interface_err(
728 cr.organization_id,
729 user_id,
730 login_id,
731 prog_appid,
732 prog_id,
733 request_id,
734 cr.TRANSACTION_ID,
735 error_msg,
736 'MRP_CALCULATE_ATP_FLAG',
737 'MTL_SYSTEM_ITEMS_INTERFACE',
738 'INV_IOI_FLAG_Y_N',
739 err_text);
740 if dumm_status < 0 then
741 raise LOGGING_ERR;
742 end if;
743 status := 1;
744 end if;
745
746 stmt := 30;
747 /*NP 20AUG96 new values added for END_ASSEMBLY_PEGGING_FLAG 10.7*/
748 if (cr.END_ASSEMBLY_PEGGING_FLAG not in ('Y','N','A','B','I','X'))
749 then
750 dumm_status := INVPUOPI.mtl_log_interface_err(
751 cr.organization_id,
752 user_id,
753 login_id,
754 prog_appid,
755 prog_id,
756 request_id,
757 cr.TRANSACTION_ID,
758 error_msg,
759 'END_ASSEMBLY_PEGGING_FLAG',
760 'MTL_SYSTEM_ITEMS_INTERFACE',
761 'INV_IOI_END_ASSEM_PEGG_FLAG',
762 err_text);
763 if dumm_status < 0 then
764 raise LOGGING_ERR;
765 end if;
766 status := 1;
767 end if;
768
769 stmt := 31;
770 if (cr.REPETITIVE_PLANNING_FLAG <> 'Y' and
771 cr.REPETITIVE_PLANNING_FLAG <> 'N') then
772 dumm_status := INVPUOPI.mtl_log_interface_err(
773 cr.organization_id,
774 user_id,
775 login_id,
776 prog_appid,
777 prog_id,
778 request_id,
779 cr.TRANSACTION_ID,
780 error_msg,
781 'REPETITIVE_PLANNING_FLAG',
782 'MTL_SYSTEM_ITEMS_INTERFACE',
783 'INV_IOI_FLAG_Y_N',
784 err_text);
785 if dumm_status < 0 then
786 raise LOGGING_ERR;
787 end if;
788 status := 1;
789 end if;
790
791 stmt := 32;
792 if (cr.PICK_COMPONENTS_FLAG <> 'Y' and
793 cr.PICK_COMPONENTS_FLAG <> 'N') then
794 dumm_status := INVPUOPI.mtl_log_interface_err(
795 cr.organization_id,
796 user_id,
797 login_id,
798 prog_appid,
799 prog_id,
800 request_id,
801 cr.TRANSACTION_ID,
802 error_msg,
803 'PICK_COMPONENTS_FLAG',
804 'MTL_SYSTEM_ITEMS_INTERFACE',
805 'INV_IOI_FLAG_Y_N',
806 err_text);
807 if dumm_status < 0 then
808 raise LOGGING_ERR;
809 end if;
810 status := 1;
811 end if;
812
813 stmt := 33;
814 if (cr.REPLENISH_TO_ORDER_FLAG <> 'Y' and
815 cr.REPLENISH_TO_ORDER_FLAG <> 'N') then
816 dumm_status := INVPUOPI.mtl_log_interface_err(
817 cr.organization_id,
818 user_id,
819 login_id,
820 prog_appid,
821 prog_id,
822 request_id,
823 cr.TRANSACTION_ID,
824 error_msg,
825 'REPLENISH_TO_ORDER_FLAG',
826 'MTL_SYSTEM_ITEMS_INTERFACE',
827 'INV_IOI_FLAG_Y_N',
828 err_text);
829 if dumm_status < 0 then
830 raise LOGGING_ERR;
831 end if;
832 status := 1;
833 end if;
834
835 stmt := 34;
836 if (cr.ATP_COMPONENTS_FLAG not in ('N','Y','R','C')) then
837 dumm_status := INVPUOPI.mtl_log_interface_err(
838 cr.organization_id,
839 user_id,
840 login_id,
841 prog_appid,
842 prog_id,
843 request_id,
844 cr.TRANSACTION_ID,
845 error_msg,
846 'ATP_COMPONENTS_FLAG',
847 'MTL_SYSTEM_ITEMS_INTERFACE',
848 'INV_IOI_ATP_COMPONENTS_FLAG',
849 err_text);
850 if dumm_status < 0 then
851 raise LOGGING_ERR;
852 end if;
853 status := 1;
854 end if;
855
856 stmt := 35;
857 if (cr.ATP_FLAG not in ('N','Y','R','C')) then
858 dumm_status := INVPUOPI.mtl_log_interface_err(
859 cr.organization_id,
860 user_id,
861 login_id,
862 prog_appid,
863 prog_id,
864 request_id,
865 cr.TRANSACTION_ID,
866 error_msg,
867 'ATP_FLAG',
868 'MTL_SYSTEM_ITEMS_INTERFACE',
869 'INV_IOI_ATP_FLAG',
870 err_text);
871 if dumm_status < 0 then
872 raise LOGGING_ERR;
873 end if;
874 status := 1;
875 end if;
876
877 /* Bug 1477935 */
878 if ( ctp_prof_val = 1 and cr.atp_flag in ('C','R') and
879 ((cr.replenish_to_order_flag = 'Y') or
880 (cr.replenish_to_order_flag = 'N' and cr.bom_item_type = 5) or
881 (cr.replenish_to_order_flag = 'N' and cr.bom_item_type in (1,2,4))
882 )) then
883 dumm_status := INVPUOPI.mtl_log_interface_err(
884 cr.organization_id,
885 user_id,
886 login_id,
887 prog_appid,
888 prog_id,
889 request_id,
890 cr.TRANSACTION_ID,
891 error_msg,
892 'ATP_FLAG',
893 'MTL_SYSTEM_ITEMS_INTERFACE',
894 'INV_IOI_CHECK_ATP_CTP_1',
895 err_text);
896 if dumm_status < 0 then
897 raise LOGGING_ERR;
898 end if;
899 status := 1;
900 elsif ( ctp_prof_val in (2,5) and cr.atp_flag in ('C','R') ) then
901 dumm_status := INVPUOPI.mtl_log_interface_err(
902 cr.organization_id,
903 user_id,
904 login_id,
905 prog_appid,
906 prog_id,
907 request_id,
908 cr.TRANSACTION_ID,
909 error_msg,
910 'ATP_FLAG',
911 'MTL_SYSTEM_ITEMS_INTERFACE',
912 'INV_IOI_CHECK_ATP_CTP_2',
913 err_text);
914 if dumm_status < 0 then
915 raise LOGGING_ERR;
916 end if;
917
918 status := 1;
919 elsif (ctp_prof_val = 3 and cr.bom_item_type in (1,2,4)
920 and cr.atp_flag in ('C','R')) then
921 dumm_status := INVPUOPI.mtl_log_interface_err(
922 cr.organization_id,
923 user_id,
924 login_id,
925 prog_appid,
926 prog_id,
927 request_id,
928 cr.TRANSACTION_ID,
929 error_msg,
930 'ATP_FLAG',
931 'MTL_SYSTEM_ITEMS_INTERFACE',
932 'INV_IOI_CHECK_ATP_CTP_3A',
933 err_text);
934 if dumm_status < 0 then
935 raise LOGGING_ERR;
936 end if;
937
938 status := 1;
939 elsif ( ctp_prof_val = 3 and cr.bom_item_type = 5
940 and cr.atp_flag <> 'N' --Bug:3436199
941 ) then
942 dumm_status := INVPUOPI.mtl_log_interface_err(
943 cr.organization_id,
944 user_id,
945 login_id,
946 prog_appid,
947 prog_id,
948 request_id,
949 cr.TRANSACTION_ID,
950 error_msg,
951 'ATP_FLAG',
952 'MTL_SYSTEM_ITEMS_INTERFACE',
953 'INV_CHECK_ATP_CTP_3B',--'INV_IOI_CHECK_ATP_CTP_3B',
954 err_text);
955 if dumm_status < 0 then
956 raise LOGGING_ERR;
957 end if;
958
959 status := 1;
960 end if;
961 stmt := 36;
962 if (cr.DEFAULT_INCLUDE_IN_ROLLUP_FLAG <> 'Y' and
963 cr.DEFAULT_INCLUDE_IN_ROLLUP_FLAG <> 'N') then
964 dumm_status := INVPUOPI.mtl_log_interface_err(
965 cr.organization_id,
966 user_id,
967 login_id,
968 prog_appid,
969 prog_id,
970 request_id,
971 cr.TRANSACTION_ID,
972 error_msg,
973 'DEFAULT_INCLUDE_IN_ROLLUP_FLAG',
974 'MTL_SYSTEM_ITEMS_INTERFACE',
975 'INV_IOI_FLAG_Y_N',
976 err_text);
977 if dumm_status < 0 then
978 raise LOGGING_ERR;
979 end if;
980 status := 1;
981 end if;
982
983 stmt := 37;
984 /*NP 02-OCT-95 Removed validation for ENGINEERING_ECN_CODE */
985 /*** Bug: 2731125
986 stmt := 38;
987 if (cr.VENDOR_WARRANTY_FLAG <> 'Y' and
988 cr.VENDOR_WARRANTY_FLAG <> 'N') then
989 dumm_status := INVPUOPI.mtl_log_interface_err(
990 cr.organization_id,
991 user_id,
992 login_id,
993 prog_appid,
994 prog_id,
995 request_id,
996 cr.TRANSACTION_ID,
997 error_msg,
998 'VENDOR_WARRANTY_FLAG',
999 'MTL_SYSTEM_ITEMS_INTERFACE',
1000 'INV_IOI_FLAG_Y_N',
1001 err_text);
1002 if dumm_status < 0 then
1003 raise LOGGING_ERR;
1004 end if;
1005 status := 1;
1006 end if;
1007
1008 -- SERVICEABLE_COMPONENT_FLAG got obsoleted
1009 stmt := 39;
1010 if (cr.SERVICEABLE_COMPONENT_FLAG <> 'Y' and
1011 cr.SERVICEABLE_COMPONENT_FLAG <> 'N') then
1012 dumm_status := INVPUOPI.mtl_log_interface_err(
1013 cr.organization_id,
1014 user_id,
1015 login_id,
1016 prog_appid,
1017 prog_id,
1018 request_id,
1019 cr.TRANSACTION_ID,
1020 error_msg,
1021 'SERVICEABLE_COMPONENT_FLAG',
1022 'MTL_SYSTEM_ITEMS_INTERFACE',
1023 'INV_IOI_FLAG_Y_N',
1024 err_text);
1025 if dumm_status < 0 then
1026 raise LOGGING_ERR;
1027 end if;
1028 status := 1;
1029 end if;
1030 ***/
1031 stmt := 40;
1032 if (cr.SERVICEABLE_PRODUCT_FLAG <> 'Y' and
1033 cr.SERVICEABLE_PRODUCT_FLAG <> 'N') then
1034 dumm_status := INVPUOPI.mtl_log_interface_err(
1035 cr.organization_id,
1036 user_id,
1037 login_id,
1038 prog_appid,
1039 prog_id,
1040 request_id,
1041 cr.TRANSACTION_ID,
1042 error_msg,
1043 'SERVICEABLE_PRODUCT_FLAG',
1044 'MTL_SYSTEM_ITEMS_INTERFACE',
1045 'INV_IOI_FLAG_Y_N',
1046 err_text);
1047 if dumm_status < 0 then
1048 raise LOGGING_ERR;
1049 end if;
1050 status := 1;
1051 end if;
1052
1053 stmt := 41;
1054 if (cr.PREVENTIVE_MAINTENANCE_FLAG <> 'Y' and
1055 cr.PREVENTIVE_MAINTENANCE_FLAG <> 'N') then
1056 dumm_status := INVPUOPI.mtl_log_interface_err(
1057 cr.organization_id,
1058 user_id,
1059 login_id,
1060 prog_appid,
1061 prog_id,
1062 request_id,
1063 cr.TRANSACTION_ID,
1064 error_msg,
1065 'PREVENTIVE_MAINTENANCE_FLAG',
1066 'MTL_SYSTEM_ITEMS_INTERFACE',
1067 'INV_IOI_FLAG_Y_N',
1068 err_text);
1069 if dumm_status < 0 then
1070 raise LOGGING_ERR;
1071 end if;
1072 status := 1;
1073 end if;
1074 stmt := 42;
1075 if (cr.TIME_BILLABLE_FLAG <> 'Y' and
1076 cr.TIME_BILLABLE_FLAG <> 'N') then
1077 dumm_status := INVPUOPI.mtl_log_interface_err(
1078 cr.organization_id,
1079 user_id,
1080 login_id,
1081 prog_appid,
1082 prog_id,
1083 request_id,
1084 cr.TRANSACTION_ID,
1085 error_msg,
1086 'TIME_BILLABLE_FLAG',
1087 'MTL_SYSTEM_ITEMS_INTERFACE',
1088 'INV_IOI_FLAG_Y_N',
1089 err_text);
1090 if dumm_status < 0 then
1091 raise LOGGING_ERR;
1092 end if;
1093 status := 1;
1094 end if;
1095
1096 -- Validate Material Billable Flag (Y,N,M,E,B,L,NULL), Y,N for 10.6
1097 -- Added 'L' for bug# 738612
1098 -- Removed 'Y','N','B' for bug 943383, as M,E,L,NULL are valid
1099 -- values in the Items form.
1100 -- 11.5.10 chnaged the Billing type lov query
1101 if (cr.MATERIAL_BILLABLE_FLAG IS NOT NULL) then
1102 stmt := 43;
1103 OPEN c_check_billable_flag('MTL_SERVICE_BILLABLE_FLAG',cr.MATERIAL_BILLABLE_FLAG);
1104 FETCH c_check_billable_flag INTO bill_value;
1105 CLOSE c_check_billable_flag;
1106 if (bill_value IS NULL) then
1107 dumm_status := INVPUOPI.mtl_log_interface_err(
1108 cr.organization_id,
1109 user_id,
1110 login_id,
1111 prog_appid,
1112 prog_id,
1113 request_id,
1114 cr.TRANSACTION_ID,
1115 error_msg,
1116 'MATERIAL_BILLABLE_FLAG',
1117 'MTL_SYSTEM_ITEMS_INTERFACE',
1118 'INV_INVALID_ATTR_COL_VALUE',--'INV_IOI_MATERIAL_BILLABLE_FLAG',
1119 err_text);
1120 if dumm_status < 0 then
1121 raise LOGGING_ERR;
1122 end if;
1123 status := 1;
1124 end if;
1125 end if;
1126
1127 stmt := 44;
1128 if (cr.EXPENSE_BILLABLE_FLAG <> 'Y' and
1129 cr.EXPENSE_BILLABLE_FLAG <> 'N') then
1130 dumm_status := INVPUOPI.mtl_log_interface_err(
1131 cr.organization_id,
1132 user_id,
1133 login_id,
1134 prog_appid,
1135 prog_id,
1136 request_id,
1137 cr.TRANSACTION_ID,
1138 error_msg,
1139 'EXPENSE_BILLABLE_FLAG',
1140 'MTL_SYSTEM_ITEMS_INTERFACE',
1141 'INV_IOI_FLAG_Y_N',
1142 err_text);
1143 if dumm_status < 0 then
1144 raise LOGGING_ERR;
1145 end if;
1146 status := 1;
1147 end if;
1148
1149 stmt := 45;
1150 if (cr.PRORATE_SERVICE_FLAG <> 'Y' and
1151 cr.PRORATE_SERVICE_FLAG <> 'N') then
1152 dumm_status := INVPUOPI.mtl_log_interface_err(
1153 cr.organization_id,
1154 user_id,
1155 login_id,
1156 prog_appid,
1157 prog_id,
1158 request_id,
1159 cr.TRANSACTION_ID,
1160 error_msg,
1161 'PRORATE_SERVICE_FLAG',
1162 'MTL_SYSTEM_ITEMS_INTERFACE',
1163 'INV_IOI_FLAG_Y_N',
1164 err_text);
1165 if dumm_status < 0 then
1166 raise LOGGING_ERR;
1167 end if;
1168 status := 1;
1169 end if;
1170
1171 -- Validate Service Duration Period Code
1172 --Bug: 4654433 Performance fix
1173 stmt := 46;
1174 IF cr.service_duration_period_code IS NOT NULL THEN
1175 IF cr.service_duration_period_code <> l_prev_uom_code THEN
1176 BEGIN
1177 SELECT status
1178 INTO status
1179 FROM mtl_all_primary_uoms_vv
1180 WHERE uom_code = cr.service_duration_period_code
1181 AND uom_class = time_uom_class;
1182 l_serv_dur_per_code_err := 0;
1183 EXCEPTION
1184 --Fix for bug# 2767335 (details below)
1185 WHEN TOO_MANY_ROWS THEN
1186 --MTL_ALL_PRIMARY_UOMS_VV view contains Stamdard UOM
1187 --conversions and Item Specific UOM conversions.
1188 --Hence for a particular UOM_CODE of an UOM_CLASS
1189 --There can be 2 or more rows in the above view.
1190 --This is a valid case, and hence donot error.
1191 NULL ; -- Do nothing
1192 l_serv_dur_per_code_err := 0;
1193
1194 WHEN OTHERS THEN
1195 dumm_status := INVPUOPI.mtl_log_interface_err(
1196 cr.organization_id,
1197 user_id,
1198 login_id,
1199 prog_appid,
1200 prog_id,
1201 request_id,
1202 cr.TRANSACTION_ID,
1203 error_msg,
1204 'SERVICE_DURATION_PERIOD_CODE',
1205 'MTL_SYSTEM_ITEMS_INTERFACE',
1206 'INV_IOI_SERV_DUR_PER_CODE',
1207 err_text);
1208 l_serv_dur_per_code_err := 1;
1209 IF dumm_status < 0 THEN
1210 RAISE LOGGING_ERR;
1211 END IF;
1212 status := 1;
1213 END;
1214 ELSIF l_serv_dur_per_code_err = 1 THEN
1215 dumm_status := INVPUOPI.mtl_log_interface_err(
1216 cr.organization_id,
1217 user_id,
1218 login_id,
1219 prog_appid,
1220 prog_id,
1221 request_id,
1222 cr.TRANSACTION_ID,
1223 error_msg,
1224 'SERVICE_DURATION_PERIOD_CODE',
1225 'MTL_SYSTEM_ITEMS_INTERFACE',
1226 'INV_IOI_SERV_DUR_PER_CODE',
1227 err_text);
1228 l_serv_dur_per_code_err := 1;
1229 IF dumm_status < 0 THEN
1230 RAISE LOGGING_ERR;
1231 END IF;
1232 status := 1;
1233 END IF;
1234 END IF;
1235 l_prev_uom_code := cr.service_duration_period_code; --Bug: 4654433
1236
1237 stmt := 47;
1238 if (cr.INVOICEABLE_ITEM_FLAG <> 'Y' and
1239 cr.INVOICEABLE_ITEM_FLAG <> 'N') then
1240 dumm_status := INVPUOPI.mtl_log_interface_err(
1241 cr.organization_id,
1242 user_id,
1243 login_id,
1244 prog_appid,
1245 prog_id,
1246 request_id,
1247 cr.TRANSACTION_ID,
1248 error_msg,
1249 'INVOICEABLE_ITEM_FLAG',
1250 'MTL_SYSTEM_ITEMS_INTERFACE',
1251 'INV_IOI_FLAG_Y_N',
1252 err_text);
1253 if dumm_status < 0 then
1254 raise LOGGING_ERR;
1255 end if;
1256 status := 1;
1257 end if;
1258
1259 stmt := 48;
1260 if (cr.INVOICE_ENABLED_FLAG <> 'Y' and
1261 cr.INVOICE_ENABLED_FLAG <> 'N') then
1262 dumm_status := INVPUOPI.mtl_log_interface_err(
1263 cr.organization_id,
1264 user_id,
1265 login_id,
1266 prog_appid,
1267 prog_id,
1268 request_id,
1269 cr.TRANSACTION_ID,
1270 error_msg,
1271 'INVOICE_ENABLED_FLAG',
1272 'MTL_SYSTEM_ITEMS_INTERFACE',
1273 'INV_IOI_FLAG_Y_N',
1274 err_text);
1275 if dumm_status < 0 then
1276 raise LOGGING_ERR;
1277 end if;
1278 status := 1;
1279 end if;
1280
1281 stmt := 49;
1282 if (cr.MUST_USE_APPROVED_VENDOR_FLAG <> 'Y' and
1283 cr.MUST_USE_APPROVED_VENDOR_FLAG <> 'N') then
1284 dumm_status := INVPUOPI.mtl_log_interface_err(
1285 cr.organization_id,
1286 user_id,
1287 login_id,
1288 prog_appid,
1289 prog_id,
1290 request_id,
1291 cr.TRANSACTION_ID,
1292 error_msg,
1293 'MUST_USE_APPROVED_VENDOR_FLAG',
1294 'MTL_SYSTEM_ITEMS_INTERFACE',
1295 'INV_IOI_FLAG_Y_N',
1296 err_text);
1297 if dumm_status < 0 then
1298 raise LOGGING_ERR;
1299 end if;
1300 status := 1;
1301 end if;
1302
1303 stmt := 50;
1304 if (cr.OUTSIDE_OPERATION_FLAG <> 'Y' and
1305 cr.OUTSIDE_OPERATION_FLAG <> 'N') then
1306 dumm_status := INVPUOPI.mtl_log_interface_err(
1307 cr.organization_id,
1308 user_id,
1309 login_id,
1310 prog_appid,
1311 prog_id,
1312 request_id,
1313 cr.TRANSACTION_ID,
1314 error_msg,
1315 'OUTSIDE_OPERATION_FLAG',
1316 'MTL_SYSTEM_ITEMS_INTERFACE',
1317 'INV_IOI_FLAG_Y_N',
1318 err_text);
1319 if dumm_status < 0 then
1320 raise LOGGING_ERR;
1321 end if;
1322 status := 1;
1323 end if;
1324
1325 stmt := 51;
1326 if (cr.COSTING_ENABLED_FLAG <> 'Y' and
1327 cr.COSTING_ENABLED_FLAG <> 'N') then
1328 dumm_status := INVPUOPI.mtl_log_interface_err(
1329 cr.organization_id,
1330 user_id,
1331 login_id,
1332 prog_appid,
1333 prog_id,
1334 request_id,
1335 cr.TRANSACTION_ID,
1336 error_msg,
1337 'COSTING_ENABLED_FLAG',
1338 'MTL_SYSTEM_ITEMS_INTERFACE',
1339 'INV_IOI_FLAG_Y_N',
1340 err_text);
1341 if dumm_status < 0 then
1342 raise LOGGING_ERR;
1343 end if;
1344 status := 1;
1345 end if;
1346
1347 stmt := 52;
1348 if (cr.CYCLE_COUNT_ENABLED_FLAG <> 'Y' and
1349 cr.CYCLE_COUNT_ENABLED_FLAG <> 'N') then
1350 dumm_status := INVPUOPI.mtl_log_interface_err(
1351 cr.organization_id,
1352 user_id,
1353 login_id,
1354 prog_appid,
1355 prog_id,
1356 request_id,
1357 cr.TRANSACTION_ID,
1358 error_msg,
1359 'CYCLE_COUNT_ENABLED_FLAG',
1360 'MTL_SYSTEM_ITEMS_INTERFACE',
1361 'INV_IOI_FLAG_Y_N',
1362 err_text);
1363 if dumm_status < 0 then
1364 raise LOGGING_ERR;
1365 end if;
1366 status := 1;
1367 end if;
1368
1369 stmt := 53;
1370 if (cr.AUTO_CREATED_CONFIG_FLAG <> 'Y' and
1371 cr.AUTO_CREATED_CONFIG_FLAG <> 'N') then
1372 dumm_status := INVPUOPI.mtl_log_interface_err(
1373 cr.organization_id,
1374 user_id,
1375 login_id,
1376 prog_appid,
1377 prog_id,
1378 request_id,
1379 cr.TRANSACTION_ID,
1380 error_msg,
1381 'AUTO_CREATED_CONFIG_FLAG',
1382 'MTL_SYSTEM_ITEMS_INTERFACE',
1383 'INV_IOI_FLAG_Y_N',
1384 err_text);
1385 if dumm_status < 0 then
1386 raise LOGGING_ERR;
1387 end if;
1388 status := 1;
1389 end if;
1390
1391
1392 stmt := 54;
1393 IF (cr.CHECK_SHORTAGES_FLAG <> 'Y' AND
1394 cr.CHECK_SHORTAGES_FLAG <> 'N') THEN
1395 dumm_status := INVPUOPI.mtl_log_interface_err(
1396 cr.organization_id,
1397 user_id,
1398 login_id,
1399 prog_appid,
1400 prog_id,
1401 request_id,
1402 cr.TRANSACTION_ID,
1403 error_msg,
1404 'CHECK_SHORTAGES_FLAG',
1405 'MTL_SYSTEM_ITEMS_INTERFACE',
1406 'BOM_OP_VALIDATION_ERR',
1407 err_text);
1408 IF dumm_status < 0 THEN
1409 raise LOGGING_ERR;
1410 END IF;
1411 status := 1;
1412 END IF;
1413
1414 /* Adding validation for GDSN_OUTBOUND_ENABLED and STYLE_ITEM_FLAG for R12 C */
1415 stmt := 55;
1416 IF (cr.GDSN_OUTBOUND_ENABLED_FLAG <> 'Y' AND
1417 cr.GDSN_OUTBOUND_ENABLED_FLAG <> 'N') THEN
1418 dumm_status := INVPUOPI.mtl_log_interface_err(
1419 cr.organization_id,
1420 user_id,
1421 login_id,
1422 prog_appid,
1423 prog_id,
1424 request_id,
1425 cr.TRANSACTION_ID,
1426 error_msg,
1427 'GDSN_OUTBOUND_ENABLED_FLAG',
1428 'MTL_SYSTEM_ITEMS_INTERFACE',
1429 'INV_IOI_FLAG_Y_N',
1430 err_text);
1431 IF dumm_status < 0 THEN
1432 raise LOGGING_ERR;
1433 END IF;
1434 status := 1;
1435 END IF;
1436
1437 stmt := 56;
1438 IF (cr.STYLE_ITEM_FLAG <> 'Y' AND
1439 cr.STYLE_ITEM_FLAG <> 'N' AND
1440 cr.STYLE_ITEM_FLAG IS NOT NULL) THEN
1441 dumm_status := INVPUOPI.mtl_log_interface_err(
1442 cr.organization_id,
1443 user_id,
1444 login_id,
1445 prog_appid,
1446 prog_id,
1447 request_id,
1448 cr.TRANSACTION_ID,
1449 error_msg,
1450 'STYLE_ITEM_FLAG',
1451 'MTL_SYSTEM_ITEMS_INTERFACE',
1452 'BOM_OP_VALIDATION_ERR',
1453 err_text);
1454 IF dumm_status < 0 THEN
1455 raise LOGGING_ERR;
1456 END IF;
1457 status := 1;
1458 END IF;
1459 --Start 3515652: Performance enhancements
1460 select process_flag into temp_proc_flag
1461 from MTL_SYSTEM_ITEMS_INTERFACE
1462 where inventory_item_id = cr.inventory_item_id
1463 and set_process_id + 0 = xset_id
1464 and process_flag in (31, 32, 33, 34 , 35 , 45)
1465 and organization_id = cr.organization_id
1466 and rownum < 2;
1467
1468 IF (temp_proc_flag <> 31
1469 and temp_proc_flag <> 32
1470 and temp_proc_flag <> 33
1471 and temp_proc_flag <> 34)
1472 THEN
1473 update MTL_SYSTEM_ITEMS_INTERFACE
1474 set process_flag = DECODE(status,0,45,35)
1475 where inventory_item_id = cr.inventory_item_id
1476 and set_process_id + 0 = xset_id
1477 and process_flag = 45
1478 and organization_id = cr.organization_id;
1479 END IF;
1480
1481 --End 3515652: Performance enhancements
1482
1483 END LOOP;
1484
1485 RETURN(0);
1486
1487 EXCEPTION
1488
1489 WHEN LOGGING_ERR THEN
1490 IF l_inv_debug_level IN(101, 102) THEN
1491 INVPUTLI.info('INVPUPI2:LOGGING_ERR : stmt ='||stmt);
1492 END IF;
1493
1494 RETURN(dumm_status);
1495 WHEN VALIDATE_ERR THEN
1496 IF l_inv_debug_level IN(101, 102) THEN
1497 INVPUTLI.info('INVPUPI2:VALIDATE_ERR ');
1498 END IF;
1499
1500 dumm_status := INVPUOPI.mtl_log_interface_err(
1501 null,
1502 user_id,
1503 login_id,
1504 prog_appid,
1505 prog_id,
1506 request_id,
1507 null,
1508 err_text,
1509 null,
1510 'MTL_SYSTEM_ITEMS_INTERFACE',
1511 'BOM_OP_VALIDATION_ERR',
1512 err_text);
1513 RETURN(status);
1514 WHEN OTHERS THEN
1515 err_text := substr('INVPUPI2.validate_flags' || SQLERRM, 1, 240);
1516 return(SQLCODE);
1517
1518 END validate_flags;
1519
1520 END INVPUPI2;