[Home] [Help]
PACKAGE BODY: APPS.INVPVDR3
Source
1 package body INVPVDR3 as
2 /* $Header: INVPVD3B.pls 120.6 2006/05/30 10:45:55 anmurali ship $ */
3
4 function validate_item_header3
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 /******************************************************/
19 /* Variable required for the validation for UPDATES */
20 /******************************************************/
21 loc_ctrl_code NUMBER;
22 cost_flag VARCHAR2(1);
23 inv_asset_flag VARCHAR2(1);
24 mrp_stock_code NUMBER;
25 base_item NUMBER;
26 lead_lot_size NUMBER;
27 out_op_flag VARCHAR2(1);
28 shelf_code NUMBER;
29 temp VARCHAR2(2);
30 temp_uom_code VARCHAR2(3);
31 temp_u_o_m VARCHAR2(25);
32 temp_uom_class VARCHAR2(10);
33 temp_enabled_flag VARCHAR2(1);
34 /*
35 ** Retrieve column values for validation
36 */
37 CURSOR cc is
38 select
39 ROWID,
40 TRANSACTION_ID,
41 ORGANIZATION_ID,
42 TRANSACTION_TYPE,
43 PROCESS_FLAG,
44 INVENTORY_ITEM_ID,
45 SUMMARY_FLAG,
46 ENABLED_FLAG,
47 START_DATE_ACTIVE,
48 END_DATE_ACTIVE,
49 DESCRIPTION,
50 BUYER_ID,
51 ACCOUNTING_RULE_ID,
52 INVOICING_RULE_ID,
53 SEGMENT1,
54 SEGMENT2,
55 SEGMENT3,
56 SEGMENT4,
57 SEGMENT5,
58 SEGMENT6,
59 SEGMENT7,
60 SEGMENT8,
61 SEGMENT9,
62 SEGMENT10,
63 SEGMENT11,
64 SEGMENT12,
65 SEGMENT13,
66 SEGMENT14,
67 SEGMENT15,
68 SEGMENT16,
69 SEGMENT17,
70 SEGMENT18,
71 SEGMENT19,
72 SEGMENT20,
73 ATTRIBUTE_CATEGORY,
74 ATTRIBUTE1,
75 ATTRIBUTE2,
76 ATTRIBUTE3,
77 ATTRIBUTE4,
78 ATTRIBUTE5,
79 ATTRIBUTE6,
80 ATTRIBUTE7,
81 ATTRIBUTE8,
82 ATTRIBUTE9,
83 ATTRIBUTE10,
84 ATTRIBUTE11,
85 ATTRIBUTE12,
86 ATTRIBUTE13,
87 ATTRIBUTE14,
88 ATTRIBUTE15,
89 PURCHASING_ITEM_FLAG,
90 SHIPPABLE_ITEM_FLAG,
91 CUSTOMER_ORDER_FLAG,
92 INTERNAL_ORDER_FLAG,
93 SERVICE_ITEM_FLAG,
94 INVENTORY_ITEM_FLAG,
95 ENG_ITEM_FLAG,
96 INVENTORY_ASSET_FLAG,
97 PURCHASING_ENABLED_FLAG,
98 CUSTOMER_ORDER_ENABLED_FLAG,
99 INTERNAL_ORDER_ENABLED_FLAG,
100 SO_TRANSACTIONS_FLAG,
101 MTL_TRANSACTIONS_ENABLED_FLAG,
102 STOCK_ENABLED_FLAG,
103 BOM_ENABLED_FLAG,
104 BUILD_IN_WIP_FLAG,
105 REVISION_QTY_CONTROL_CODE,
106 ITEM_CATALOG_GROUP_ID,
107 CATALOG_STATUS_FLAG,
108 RETURNABLE_FLAG,
109 DEFAULT_SHIPPING_ORG,
110 COLLATERAL_FLAG,
111 TAXABLE_FLAG,
112 QTY_RCV_EXCEPTION_CODE,
113 ALLOW_ITEM_DESC_UPDATE_FLAG,
114 INSPECTION_REQUIRED_FLAG,
115 RECEIPT_REQUIRED_FLAG,
116 MARKET_PRICE,
117 HAZARD_CLASS_ID,
118 RFQ_REQUIRED_FLAG,
119 QTY_RCV_TOLERANCE,
120 LIST_PRICE_PER_UNIT,
121 UN_NUMBER_ID,
122 PRICE_TOLERANCE_PERCENT,
123 ASSET_CATEGORY_ID,
124 ROUNDING_FACTOR,
125 UNIT_OF_ISSUE,
126 ENFORCE_SHIP_TO_LOCATION_CODE,
127 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
128 ALLOW_UNORDERED_RECEIPTS_FLAG,
129 ALLOW_EXPRESS_DELIVERY_FLAG,
130 DAYS_EARLY_RECEIPT_ALLOWED,
131 DAYS_LATE_RECEIPT_ALLOWED,
132 RECEIPT_DAYS_EXCEPTION_CODE,
133 RECEIVING_ROUTING_ID,
134 INVOICE_CLOSE_TOLERANCE,
135 RECEIVE_CLOSE_TOLERANCE,
136 AUTO_LOT_ALPHA_PREFIX,
137 START_AUTO_LOT_NUMBER,
138 LOT_CONTROL_CODE,
139 SHELF_LIFE_CODE,
140 SHELF_LIFE_DAYS,
141 SERIAL_NUMBER_CONTROL_CODE,
142 START_AUTO_SERIAL_NUMBER,
143 AUTO_SERIAL_ALPHA_PREFIX,
144 SOURCE_TYPE,
145 SOURCE_ORGANIZATION_ID,
146 SOURCE_SUBINVENTORY,
147 EXPENSE_ACCOUNT,
148 ENCUMBRANCE_ACCOUNT,
149 RESTRICT_SUBINVENTORIES_CODE,
150 UNIT_WEIGHT,
151 WEIGHT_UOM_CODE,
152 VOLUME_UOM_CODE,
153 UNIT_VOLUME,
154 RESTRICT_LOCATORS_CODE,
155 LOCATION_CONTROL_CODE,
156 SHRINKAGE_RATE,
157 ACCEPTABLE_EARLY_DAYS,
158 PLANNING_TIME_FENCE_CODE,
159 DEMAND_TIME_FENCE_CODE,
160 LEAD_TIME_LOT_SIZE,
161 STD_LOT_SIZE,
162 CUM_MANUFACTURING_LEAD_TIME,
163 OVERRUN_PERCENTAGE,
164 MRP_CALCULATE_ATP_FLAG,
165 ACCEPTABLE_RATE_INCREASE,
166 ACCEPTABLE_RATE_DECREASE,
167 CUMULATIVE_TOTAL_LEAD_TIME,
168 PLANNING_TIME_FENCE_DAYS,
169 DEMAND_TIME_FENCE_DAYS,
170 END_ASSEMBLY_PEGGING_FLAG,
171 REPETITIVE_PLANNING_FLAG,
172 PLANNING_EXCEPTION_SET,
173 BOM_ITEM_TYPE,
174 PICK_COMPONENTS_FLAG,
175 REPLENISH_TO_ORDER_FLAG,
176 BASE_ITEM_ID,
177 ATP_COMPONENTS_FLAG,
178 ATP_FLAG,
179 FIXED_LEAD_TIME,
180 VARIABLE_LEAD_TIME,
181 WIP_SUPPLY_LOCATOR_ID,
182 WIP_SUPPLY_TYPE,
183 WIP_SUPPLY_SUBINVENTORY,
184 PRIMARY_UOM_CODE,
185 PRIMARY_UNIT_OF_MEASURE,
186 ALLOWED_UNITS_LOOKUP_CODE,
187 COST_OF_SALES_ACCOUNT,
188 SALES_ACCOUNT,
189 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
190 INVENTORY_ITEM_STATUS_CODE,
191 INVENTORY_PLANNING_CODE,
192 PLANNER_CODE,
193 PLANNING_MAKE_BUY_CODE,
194 FIXED_LOT_MULTIPLIER,
195 ROUNDING_CONTROL_TYPE,
196 CARRYING_COST,
197 POSTPROCESSING_LEAD_TIME,
198 PREPROCESSING_LEAD_TIME,
199 FULL_LEAD_TIME,
200 ORDER_COST,
201 MRP_SAFETY_STOCK_PERCENT,
202 MRP_SAFETY_STOCK_CODE,
203 MIN_MINMAX_QUANTITY,
204 MAX_MINMAX_QUANTITY,
205 MINIMUM_ORDER_QUANTITY,
206 FIXED_ORDER_QUANTITY,
207 FIXED_DAYS_SUPPLY,
208 MAXIMUM_ORDER_QUANTITY,
209 ATP_RULE_ID,
210 PICKING_RULE_ID,
211 RESERVABLE_TYPE,
212 POSITIVE_MEASUREMENT_ERROR,
213 NEGATIVE_MEASUREMENT_ERROR,
214 ENGINEERING_ECN_CODE,
215 ENGINEERING_ITEM_ID,
216 ENGINEERING_DATE,
217 SERVICE_STARTING_DELAY,
218 VENDOR_WARRANTY_FLAG,
219 SERVICEABLE_COMPONENT_FLAG,
220 SERVICEABLE_PRODUCT_FLAG,
221 BASE_WARRANTY_SERVICE_ID,
222 PAYMENT_TERMS_ID,
223 PREVENTIVE_MAINTENANCE_FLAG,
224 PRIMARY_SPECIALIST_ID,
225 SECONDARY_SPECIALIST_ID,
226 SERVICEABLE_ITEM_CLASS_ID,
227 TIME_BILLABLE_FLAG,
228 MATERIAL_BILLABLE_FLAG,
229 EXPENSE_BILLABLE_FLAG,
230 PRORATE_SERVICE_FLAG,
231 COVERAGE_SCHEDULE_ID,
232 SERVICE_DURATION_PERIOD_CODE,
233 SERVICE_DURATION,
234 WARRANTY_VENDOR_ID,
235 MAX_WARRANTY_AMOUNT,
236 RESPONSE_TIME_PERIOD_CODE,
237 RESPONSE_TIME_VALUE,
238 NEW_REVISION_CODE,
239 INVOICEABLE_ITEM_FLAG,
240 TAX_CODE,
241 INVOICE_ENABLED_FLAG,
242 MUST_USE_APPROVED_VENDOR_FLAG,
243 REQUEST_ID,
244 PROGRAM_APPLICATION_ID,
245 PROGRAM_ID,
246 PROGRAM_UPDATE_DATE,
247 OUTSIDE_OPERATION_FLAG,
248 OUTSIDE_OPERATION_UOM_TYPE,
249 SAFETY_STOCK_BUCKET_DAYS,
250 AUTO_REDUCE_MPS,
251 COSTING_ENABLED_FLAG,
252 CYCLE_COUNT_ENABLED_FLAG,
253 AUTO_CREATED_CONFIG_FLAG,
254 ITEM_TYPE,
255 MODEL_CONFIG_CLAUSE_NAME,
256 SHIP_MODEL_COMPLETE_FLAG,
257 MRP_PLANNING_CODE,
258 RETURN_INSPECTION_REQUIREMENT,
259 ATO_FORECAST_CONTROL,
260 RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight cols added for 10.7 */
261 RELEASE_TIME_FENCE_DAYS,
262 CONTAINER_ITEM_FLAG,
263 CONTAINER_TYPE_CODE,
264 INTERNAL_VOLUME,
265 MAXIMUM_LOAD_WEIGHT,
266 MINIMUM_FILL_PERCENT,
267 VEHICLE_ITEM_FLAG,
268 LOT_STATUS_ENABLED,
269 SERIAL_STATUS_ENABLED,
270 EFFECTIVITY_CONTROL,
271 ORDERABLE_ON_WEB_FLAG,
272 EQUIPMENT_TYPE,
273 DEFAULT_LOT_STATUS_ID,
274 DEFAULT_SERIAL_STATUS_ID,
275 --Adding 2 attributes for bug fix 3969580 By Anmurali
276 OVERCOMPLETION_TOLERANCE_TYPE,
277 OVERCOMPLETION_TOLERANCE_VALUE,
278 --Adding an attribute for R12
279 DRP_PLANNED_FLAG
280 from MTL_SYSTEM_ITEMS_INTERFACE
281 --Replacing organization_id + 0 with organization_id - Anmurali -Bug 4175124
282 where ((organization_id = org_id) or
283 (all_Org = 1))
284 and set_process_id = xset_id
285 and process_flag in ( 31, 32, 42);
286
287 /*NP 27DEC94
288 *New condition for process_flag;
289 *As a result of the breakup of INVPVHDR into smaller packages
290 *there are now new process_flag values possible
291 *INVPVHDR sets it to either 31 or 41
292 *INVPVDR2 may overwrite 41 by 42 or 32; will not overwrite 31
293 * Thus the values possible are: 31, 32, 42
294 */
295
296
297 msicount number;
298 msiicount number;
299 resersal_flag number;
300 dup_item_id number;
301 l_item_id number;
302 l_org_id number;
303 cat_set_id number;
304 trans_id number;
305 ext_flag number := 0;
306 error_msg varchar2(70);
307 status number;
308 dumm_status number;
309 master_org_id number;
310 stmt number;
311 LOGGING_ERR exception;
312 VALIDATE_ERR exception;
313 temp_proc_flag number;
314 l_err_msg varchar2(2000) := NULL;
315
316 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
317
318 begin
319
320 IF l_inv_debug_level IN(101, 102) THEN
321 INVPUTLI.info('Inside INVPVDR3');
322 END IF;
323 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
324
325 /*
326 ** validate the records
327 */
328
329 for cr in cc loop
330 status := 0;
331 trans_id := cr.transaction_id;
332 l_org_id := cr.organization_id;
333 l_item_id := cr.inventory_item_id; -- Bug 4705184
334 temp_proc_flag := cr.process_flag; -- Bug 4705184
335
336 /*NP 28DEC94
337 **A lot of item_id realted validation has now been removed from all the new
338 **INVPVDR*.sql packages that once constituted INVPVHDR
339 **This validation was redundant (and expensive!).
340 **
341 **The item_id related validation now takes place ONLY in INVPVHDR.sql.
342 **
343 **However, this has meant that the value for l_item_id (see INVPVHDR.sql) now
344 **needs to be gotten from the database
345 */
346 /* Bug 4705184. Get item_id from the cursor itself
347 select inventory_item_id
348 into l_item_id
349 from mtl_system_items_interface
350 where transaction_id = cr.transaction_id
351 and set_process_id = xset_id; */
352
353
354 /*
355 ** Validate fields with lookup values @@
356 */
357
358 /*INVPUTLI.info('INVPVDR3: Validating lookups'); */
359
360 IF l_inv_debug_level IN(101, 102) THEN
361 INVPUTLI.info('INVPVDR3: L8R values: '|| l_item_id||' '|| l_org_id);
362 INVPUTLI.info('INVPVDR3: L8R revision_qty_control_code: '|| cr.revision_qty_control_code);
363 END IF;
364
365 -- validate lookup
366 if (cr.revision_qty_control_code <> 1 and
367 cr.revision_qty_control_code <> 2) then
368
369 IF l_inv_debug_level IN(101, 102) THEN
370 INVPUTLI.info('INVPVDR3: inside the if..ie validation failed');
371 END IF;
372 dumm_status := INVPUOPI.mtl_log_interface_err(
373 cr.organization_id,
374 user_id,
375 login_id,
376 prog_appid,
377 prog_id,
378 request_id,
379 cr.TRANSACTION_ID,
380 error_msg,
381 'REVISION_QTY_CONTROL_CODE',
382 'MTL_SYSTEM_ITEMS_INTERFACE',
383 'INV_IOI_REV_QTY_CTRL_CODE',
384 err_text);
385 if dumm_status < 0 then
386 raise LOGGING_ERR;
387 end if;
388 status := 1;
389 end if;
390
391 -- validate lookup
392 if (cr.qty_rcv_exception_code <> 'NONE' and
393 cr.qty_rcv_exception_code <> 'REJECT' and
394 cr.qty_rcv_exception_code <> 'WARNING') then
395 dumm_status := INVPUOPI.mtl_log_interface_err(
396 cr.organization_id,
397 user_id,
398 login_id,
399 prog_appid,
400 prog_id,
401 request_id,
402 cr.TRANSACTION_ID,
403 error_msg,
404 'QTY_RCV_EXCEPTION_CODE',
405 'MTL_SYSTEM_ITEMS_INTERFACE',
406 'INV_IOI_QTY_RCV_EXC_CODE',
407 err_text);
408 if dumm_status < 0 then
409 raise LOGGING_ERR;
410 end if;
411 status := 1;
412 end if;
413
414 -- validate lookup
415 if (cr.enforce_ship_to_location_code <> 'NONE' and
416 cr.enforce_ship_to_location_code <> 'REJECT' and
417 cr.enforce_ship_to_location_code <> 'WARNING') then
418 dumm_status := INVPUOPI.mtl_log_interface_err(
419 cr.organization_id,
420 user_id,
421 login_id,
422 prog_appid,
423 prog_id,
424 request_id,
425 cr.TRANSACTION_ID,
426 error_msg,
427 'ENFORCE_SHIP_TO_LOCATION_CODE',
428 'MTL_SYSTEM_ITEMS_INTERFACE',
429 'INV_IOI_ENF_SHIP_TO_LOC',
430 err_text);
431 if dumm_status < 0 then
432 raise LOGGING_ERR;
433 end if;
434 status := 1;
435 end if;
436
437 -- validate lookup
438 if (cr.receipt_days_exception_code <> 'NONE' and
439 cr.receipt_days_exception_code <> 'REJECT' and
440 cr.receipt_days_exception_code <> 'WARNING') 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 'RECEIPT_DAYS_EXCEPTION_CODE',
451 'MTL_SYSTEM_ITEMS_INTERFACE',
452 'INV_IOI_REC_DAYS_EXC',
453 err_text);
454 if dumm_status < 0 then
455 raise LOGGING_ERR;
456 end if;
457 status := 1;
458 end if;
459
460 -- validate lookup
461 if (cr.lot_control_code <> 1 and
462 cr.lot_control_code <> 2) 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 'LOT_CONTROL_CODE',
473 'MTL_SYSTEM_ITEMS_INTERFACE',
474 'INV_IOI_LOT_CTRL_CODE',
475 err_text);
476 if dumm_status < 0 then
477 raise LOGGING_ERR;
478 end if;
479 status := 1;
480 end if;
481
482 -- validate lookup
483 if (cr.shelf_life_code not in (1, 2, 4)) then
484 dumm_status := INVPUOPI.mtl_log_interface_err(
485 cr.organization_id,
486 user_id,
487 login_id,
488 prog_appid,
489 prog_id,
490 request_id,
491 cr.TRANSACTION_ID,
492 error_msg,
493 'SHELF_LIFE_CODE',
494 'MTL_SYSTEM_ITEMS_INTERFACE',
495 'INV_IOI_SHELF_LIFE_CODE',
496 err_text);
497 if dumm_status < 0 then
498 raise LOGGING_ERR;
499 end if;
500 status := 1;
501 end if;
502
503 -- validate lookup
504 if (cr.serial_number_control_code <> 1 and
505 cr.serial_number_control_code <> 2 and
506 cr.serial_number_control_code <> 5 and
507 cr.serial_number_control_code <> 6 ) then
508 dumm_status := INVPUOPI.mtl_log_interface_err(
509 cr.organization_id,
510 user_id,
511 login_id,
512 prog_appid,
513 prog_id,
514 request_id,
515 cr.TRANSACTION_ID,
516 error_msg,
517 'SERIAL_NUMBER_CONTROL_CODE',
518 'MTL_SYSTEM_ITEMS_INTERFACE',
519 'INV_IOI_SERIAL_NUM_CONTROL',
520 err_text);
521 if dumm_status < 0 then
522 raise LOGGING_ERR;
523 end if;
524 status := 1;
525 end if;
526
527 -- validate lookup
528 if (cr.source_type is not null) then
529 if (cr.source_type <> 1 and
530 cr.source_type <> 2 and
531 cr.source_type <> 3) then
532 dumm_status := INVPUOPI.mtl_log_interface_err(
533 cr.organization_id,
534 user_id,
535 login_id,
536 prog_appid,
537 prog_id,
538 request_id,
539 cr.TRANSACTION_ID,
540 error_msg,
541 'SOURCE_TYPE',
542 'MTL_SYSTEM_ITEMS_INTERFACE',
543 'INV_IOI_SOURCE_TYPE',
544 err_text);
545 if dumm_status < 0 then
546 raise LOGGING_ERR;
547 end if;
548 status := 1;
549 end if;
550 end if;
551 -- validate lookup
552 if (cr.restrict_subinventories_code <> 1 and
553 cr.restrict_subinventories_code <> 2) then
554 dumm_status := INVPUOPI.mtl_log_interface_err(
555 cr.organization_id,
556 user_id,
557 login_id,
558 prog_appid,
559 prog_id,
560 request_id,
561 cr.TRANSACTION_ID,
562 error_msg,
563 'RESTRICT_SUBINVENTORIES_CODE',
564 'MTL_SYSTEM_ITEMS_INTERFACE',
565 'BOM_OP_VALIDATION_ERR',
566 err_text);
567 if dumm_status < 0 then
568 raise LOGGING_ERR;
569 end if;
570 status := 1;
571 end if;
572
573 -- validate lookup
574 if (cr.restrict_locators_code <> 1 and
575 cr.restrict_locators_code <> 2) then
576 dumm_status := INVPUOPI.mtl_log_interface_err(
577 cr.organization_id,
578 user_id,
579 login_id,
580 prog_appid,
581 prog_id,
582 request_id,
583 cr.TRANSACTION_ID,
584 error_msg,
585 'RESTRICT_LOCATOR_CODE',
586 'MTL_SYSTEM_ITEMS_INTERFACE',
587 'INV_IOI_REST_LOC_CODE',
588 err_text);
589 if dumm_status < 0 then
590 raise LOGGING_ERR;
591 end if;
592 status := 1;
593 end if;
594
595 -- validate lookup
596 if (cr.location_control_code <> 1 and
597 cr.location_control_code <> 2 and
598 cr.location_control_code <> 3) then
599 dumm_status := INVPUOPI.mtl_log_interface_err(
600 cr.organization_id,
601 user_id,
602 login_id,
603 prog_appid,
604 prog_id,
605 request_id,
606 cr.TRANSACTION_ID,
607 error_msg,
608 'LOCATION_CONTROL_CODE',
609 'MTL_SYSTEM_ITEMS_INTERFACE',
610 'INV_IOI_LOC_CTRL_CODE',
611 err_text);
612 if dumm_status < 0 then
613 raise LOGGING_ERR;
614 end if;
615 status := 1;
616 end if;
617
618 -- validate lookup
619 if (cr.planning_time_fence_code <> 1 and
620 cr.planning_time_fence_code <> 2 and
621 cr.planning_time_fence_code <> 3 and
622 cr.planning_time_fence_code <> 4) then
623 dumm_status := INVPUOPI.mtl_log_interface_err(
624 cr.organization_id,
625 user_id,
626 login_id,
627 prog_appid,
628 prog_id,
629 request_id,
630 cr.TRANSACTION_ID,
631 error_msg,
632 'PLANNING_TIME_FENCE_CODE',
633 'MTL_SYSTEM_ITEMS_INTERFACE',
634 'INV_IOI_PLN_TIME_FENCE_CODE',
635 err_text);
636 if dumm_status < 0 then
637 raise LOGGING_ERR;
638 end if;
639 status := 1;
640 end if;
641
642 -- validate lookup
643 if (cr.demand_time_fence_code <> 1 and
644 cr.demand_time_fence_code <> 2 and
645 cr.demand_time_fence_code <> 3 and
646 cr.demand_time_fence_code <> 4) then
647 dumm_status := INVPUOPI.mtl_log_interface_err(
648 cr.organization_id,
649 user_id,
650 login_id,
651 prog_appid,
652 prog_id,
653 request_id,
654 cr.TRANSACTION_ID,
655 error_msg,
656 'DEMAND_TIME_FENCE_CODE',
657 'MTL_SYSTEM_ITEMS_INTERFACE',
658 'INV_IOI_DEM_TIME_FENCE_CODE',
659 err_text);
660 if dumm_status < 0 then
661 raise LOGGING_ERR;
662 end if;
663 status := 1;
664 end if;
665 -- Bug No:3296502 Lot Status can be enabled only if Lot Control is Full Controlled
666 IF l_inv_debug_level IN(101, 102) THEN
667 INVPUTLI.info('INVPVDR3: verifying lot status....');
668 END IF;
669
670 if (cr.LOT_CONTROL_CODE = 1 and NVL(cr.LOT_STATUS_ENABLED,'N') = 'Y') then
671 dumm_status := INVPUOPI.mtl_log_interface_err(
672 cr.organization_id,
673 user_id,
674 login_id,
675 prog_appid,
676 prog_id,
677 request_id,
678 cr.TRANSACTION_ID,
679 error_msg,
680 'LOT_STATUS_ENABLED',
681 'MTL_SYSTEM_ITEMS_INTERFACE',
682 'INV_INVALID_LOT_STA_ENABLED',
683 err_text);
684 if dumm_status < 0 then
685 raise LOGGING_ERR;
686 end if;
687 status := 1;
688 end if;
689 -- Bug No:3296526 Serial Status can be enabled only if the item is Serial Controlled.
690
691 IF l_inv_debug_level IN(101, 102) THEN
692 INVPUTLI.info('INVPVDR3: verifying serial status....');
693 END IF;
694 if (cr.SERIAL_NUMBER_CONTROL_CODE = 1 and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'Y') then
695 dumm_status := INVPUOPI.mtl_log_interface_err(
696 cr.organization_id,
697 user_id,
698 login_id,
699 prog_appid,
700 prog_id,
701 request_id,
702 cr.TRANSACTION_ID,
703 error_msg,
704 'SERIAL_STATUS_ENABLED',
705 'MTL_SYSTEM_ITEMS_INTERFACE',
706 'INV_INVALID_SER_STA_ENABLED',
707 err_text);
708 if dumm_status < 0 then
709 raise LOGGING_ERR;
710 end if;
711 status := 1;
712 end if;
713
714 -- Bug No:3306166 Safety COde must = 1 when mrp_planning_code=6
715 if ( cr.MRP_PLANNING_CODE = 6
716 -- Adding the NVL for Bug 5239406
717 and NVL(cr.drp_planned_flag,2) = 2 -- Adding the clause for R12
718 and cr.MRP_SAFETY_STOCK_CODE <> 1) then
719 dumm_status := INVPUOPI.mtl_log_interface_err(
720 cr.organization_id,
721 user_id,
722 login_id,
723 prog_appid,
724 prog_id,
725 request_id,
726 cr.TRANSACTION_ID,
727 error_msg,
728 'MRP_SAFETY_STOCK_CODE',
729 'MTL_SYSTEM_ITEMS_INTERFACE',
730 'INV_SAFETY_STOCK',
731 err_text);
732 if dumm_status < 0 then
733 raise LOGGING_ERR;
734 end if;
735 status := 1;
736 end if;
737 -- Start 2958422 : Effectivity Control Validations
738 if cr.EFFECTIVITY_CONTROL IS NOT NULL
739 AND cr.EFFECTIVITY_CONTROL NOT IN (1,2)
740 then
741 dumm_status := INVPUOPI.mtl_log_interface_err(
742 cr.organization_id,
743 user_id,
744 login_id,
745 prog_appid,
746 prog_id,
747 request_id,
748 cr.TRANSACTION_ID,
749 error_msg,
750 'EFFECTIVITY_CONTROL',
751 'MTL_SYSTEM_ITEMS_INTERFACE',
752 'INV_IOI_INVALID_EFFECT_CONTL',
753 err_text);
754 if dumm_status < 0 then
755 raise LOGGING_ERR;
756 end if;
757 status := 1;
758 end if;
759
760 if cr.EFFECTIVITY_CONTROL = 2
761 AND INV_ITEM_UTIL.g_Appl_Inst.PJM_Unit_Eff_flag ='N'
762 then
763 dumm_status := INVPUOPI.mtl_log_interface_err(
764 cr.organization_id,
765 user_id,
766 login_id,
767 prog_appid,
768 prog_id,
769 request_id,
770 cr.TRANSACTION_ID,
771 error_msg,
772 'EFFECTIVITY_CONTROL',
773 'MTL_SYSTEM_ITEMS_INTERFACE',
774 'INV_IOI_MODEL_UNIT_CONTROL_NA',
775 err_text);
776 if dumm_status < 0 then
777 raise LOGGING_ERR;
778 end if;
779 status := 1;
780 end if;
781 -- End 2958422 : Effectivity Control Validations
782
783 -- Bug No:3296755 SERIAL_NUMBER_CONTROL_CODE Must be 'At Recept' or 'Predefined' if Effectivity_control is 'Model/Unit Number'.
784 IF l_inv_debug_level IN(101, 102) THEN
785 INVPUTLI.info('INVPVDR3: verifying serial status....');
786 END IF;
787 if (cr.SERIAL_NUMBER_CONTROL_CODE = 1 and cr.EFFECTIVITY_CONTROL = 2) then
788 dumm_status := INVPUOPI.mtl_log_interface_err(
789 cr.organization_id,
790 user_id,
791 login_id,
792 prog_appid,
793 prog_id,
794 request_id,
795 cr.TRANSACTION_ID,
796 error_msg,
797 'SERIAL_NUMBER_CONTROL_CODE',
798 'MTL_SYSTEM_ITEMS_INTERFACE',
799 'ITM-EFFC-Invalid Serial Ctrl',
800 err_text);
801 if dumm_status < 0 then
802 raise LOGGING_ERR;
803 end if;
804 status := 1;
805 end if;
806
807 --Bug 4319349 Effectivity Control can be Model/Unit Number ONLY for BOM Item type Standard items -Anmurali
808 IF l_inv_debug_level IN(101, 102) THEN
809 INVPUTLI.info('INVPVDR3: verifying bom item type for effectivity control....');
810 END IF;
811 if (cr.BOM_ITEM_TYPE <> 4 and cr.EFFECTIVITY_CONTROL = 2) 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 'BOM_ITEM_TYPE',
822 'MTL_SYSTEM_ITEMS_INTERFACE',
823 'ITM-EFFC-BOM Type is not Std',
824 err_text);
825 if dumm_status < 0 then
826 raise LOGGING_ERR;
827 end if;
828 status := 1;
829 end if;
830 -- Bug No:3308701 ORDERABLE_ON_WEB_FLAG Must be 'N' if CUSTOMER_ORDER_ENABLED_FLAG is not set.
831 IF l_inv_debug_level IN(101, 102) THEN
832 INVPUTLI.info('INVPVDR3: verifying serial status....');
833 END IF;
834 if (NVL(cr.CUSTOMER_ORDER_ENABLED_FLAG,'N') = 'N' and NVL(cr.ORDERABLE_ON_WEB_FLAG,'N') = 'Y') then
835 dumm_status := INVPUOPI.mtl_log_interface_err(
836 cr.organization_id,
837 user_id,
838 login_id,
839 prog_appid,
840 prog_id,
841 request_id,
842 cr.TRANSACTION_ID,
843 error_msg,
844 'ORDERABLE_ON_WEB_FLAG',
845 'MTL_SYSTEM_ITEMS_INTERFACE',
846 'INV_ORDERABLE_ON_WEB',
847 err_text);
848 if dumm_status < 0 then
849 raise LOGGING_ERR;
850 end if;
851 status := 1;
852 end if;
853
854 -- Bug No:3311672 When BOM Item type is Option Class or Model, then Assemble to order or Pick Components must be Yes
855 IF l_inv_debug_level IN(101, 102) THEN
856 INVPUTLI.info('INVPVDR3: verifying serial status....');
857 END IF;
858 if ((cr.BOM_ITEM_TYPE = 1 OR cr.BOM_ITEM_TYPE = 2 )
859 AND NVL(cr.PICK_COMPONENTS_FLAG,'N') = 'N' AND NVL(cr.REPLENISH_TO_ORDER_FLAG,'N') = 'N') then
860 dumm_status := INVPUOPI.mtl_log_interface_err(
861 cr.organization_id,
862 user_id,
863 login_id,
864 prog_appid,
865 prog_id,
866 request_id,
867 cr.TRANSACTION_ID,
868 error_msg,
869 'BOM_ITEM_TYPE',
870 'MTL_SYSTEM_ITEMS_INTERFACE',
871 'INV_BOM_TYPE',
872 err_text);
873 if dumm_status < 0 then
874 raise LOGGING_ERR;
875 end if;
876 status := 1;
877 end if;
878
879 -- Bug No:3294341 When SHELF_LIFE_CODE= 2 then SHELF_LIFE_DAYS should be > 0 and SHELF_LIFE_DAYS should always +Integer
880 /* Jalaj Srivastava Bug 4079744
881 Shelf life should be a positive number less than 999999 */
882 if (cr.SHELF_LIFE_DAYS < 0 OR cr.SHELF_LIFE_DAYS > 999999) then
883 l_err_msg := 'INV_INVALID_SHELF_LIFE_DAYS';
884 elsif(cr.SHELF_LIFE_CODE = 2 AND cr.SHELF_LIFE_DAYS = 0 )then
885 l_err_msg := 'INV_SHELF_DAYS_MUST_BE_GT_ZERO';
886 end if;
887 if (l_err_msg IS NOT NULL) then
888 dumm_status := INVPUOPI.mtl_log_interface_err(
889 cr.organization_id,
890 user_id,
891 login_id,
892 prog_appid,
893 prog_id,
894 request_id,
895 cr.TRANSACTION_ID,
896 error_msg,
897 'SHELF_LIFE_DAYS',
898 'MTL_SYSTEM_ITEMS_INTERFACE',
899 l_err_msg,
900 err_text);
901 if dumm_status < 0 then
902 raise LOGGING_ERR;
903 end if;
904 status := 1;
905 end if;
906
907 -- Bug No:3436107 if Equipment is set to yes, then Serial number generation has to be `Receipt or Predefined
908 if (cr.SERIAL_NUMBER_CONTROL_CODE NOT IN (2,5) and cr.EQUIPMENT_TYPE = 1) then
909 dumm_status := INVPUOPI.mtl_log_interface_err(
910 cr.organization_id,
911 user_id,
912 login_id,
913 prog_appid,
914 prog_id,
915 request_id,
916 cr.TRANSACTION_ID,
917 error_msg,
918 'SERIAL_NUMBER_CONTROL_CODE',
919 'MTL_SYSTEM_ITEMS_INTERFACE',
920 'INV-ITM-EQMT-INVALID_SER_CTRL',
921 err_text);
922 if dumm_status < 0 then
923 raise LOGGING_ERR;
924 end if;
925 status := 1;
926 end if;
927 -- Bug No:3969580 If OVERCOMPLETION_TOLERANCE_TYPE is not specified then OVER_COMPLETION_TOLERANCE_VALUE must throw an error msg on entry
928 if (cr.OVERCOMPLETION_TOLERANCE_TYPE IS NULL)and (cr.OVERCOMPLETION_TOLERANCE_VALUE IS NOT NULL) then
929 dumm_status := INVPUOPI.mtl_log_interface_err(
930 cr.organization_id,
931 user_id,
932 login_id,
933 prog_appid,
934 prog_id,
935 request_id,
936 cr.TRANSACTION_ID,
937 error_msg,
938 'OVERCOMPLETION_TOLERANCE_VALUE',
939 'MTL_SYSTEM_ITEMS_INTERFACE',
940 'INV_WIP_OC_TOLERANCE_V_NUPD',
941 err_text);
942 if dumm_status < 0 then
943 raise LOGGING_ERR;
944 end if;
945 status := 1;
946 end if;
947 --End of bug fix 3969580 By Anmurali
948 -- Bug No:3421324 Default Lot/Serial Status is mandatory if Lot/Serial status is enabled
949 l_err_msg := NULL;
950 if (cr.DEFAULT_LOT_STATUS_ID IS NULL and NVL(cr.LOT_STATUS_ENABLED,'N') = 'Y') then
951 l_err_msg := 'INV_DEF_LOT_STATUS_MAND';
952 elsif (cr.DEFAULT_LOT_STATUS_ID IS NOT NULL and NVL(cr.LOT_STATUS_ENABLED,'N') = 'N') then
953 l_err_msg := 'INV_DEF_LOT_STATUS_ID_NULL';
954 end if;
955 if l_err_msg IS NOT NULL then
956 dumm_status := INVPUOPI.mtl_log_interface_err(
957 cr.organization_id,
958 user_id,
959 login_id,
960 prog_appid,
961 prog_id,
962 request_id,
963 cr.TRANSACTION_ID,
964 error_msg,
965 'DEFAULT_LOT_STATUS_ID',
966 'MTL_SYSTEM_ITEMS_INTERFACE',
967 l_err_msg,
968 err_text);
969 if dumm_status < 0 then
970 raise LOGGING_ERR;
971 end if;
972 status := 1;
973 end if;
974 l_err_msg := NULL;
975 if (cr.DEFAULT_SERIAL_STATUS_ID IS NULL and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'Y') then
976 l_err_msg := 'INV_DEF_SERIAL_STATUS_MAND';
977 elsif (cr.DEFAULT_SERIAL_STATUS_ID IS NOT NULL and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'N') then
978 l_err_msg := 'INV_DEF_SERIAL_STATUS_ID_NULL';
979 end if;
980 if l_err_msg IS NOT NULL then
981 dumm_status := INVPUOPI.mtl_log_interface_err(
982 cr.organization_id,
983 user_id,
984 login_id,
985 prog_appid,
986 prog_id,
987 request_id,
988 cr.TRANSACTION_ID,
989 error_msg,
990 'DEFAULT_SERIAL_STATUS_ID',
991 'MTL_SYSTEM_ITEMS_INTERFACE',
992 l_err_msg,
993 err_text);
994 if dumm_status < 0 then
995 raise LOGGING_ERR;
996 end if;
997 status := 1;
998 end if;
999 /* NP26DEC94 : New code to update process_flag.
1000 ** This code necessiated due to the breaking up INVPVHDR into
1001 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1002 ** Let's update the process flag for the record
1003 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1004 ** Need to do this ONLY if all previous validation okay.
1005 ** The process flag values that are possible at this time are
1006 ** 31 :set by INVPVHDR
1007 ** 32, 42 :set by INVPVDR2
1008 */
1009 /* Bug 4705184. Get process flag from the cursor itself
1010 select process_flag into temp_proc_flag
1011 from MTL_SYSTEM_ITEMS_INTERFACE
1012 where inventory_item_id = l_item_id
1013 and organization_id = cr.organization_id
1014 and process_flag in (31,32,42)
1015 and set_process_id = xset_id
1016 and rownum < 2 ; */
1017
1018 /*set value of process_flag to 43 or 33 depending on
1019 ** value of the variable: status.
1020 ** Essentially, we check to see if validation has not already failed in one of
1021 ** the previous packages.
1022 */
1023 if (temp_proc_flag <> 31 and temp_proc_flag <> 32) then
1024 update MTL_SYSTEM_ITEMS_INTERFACE
1025 set process_flag = DECODE(status,0,43,33),
1026 PRIMARY_UOM_CODE = cr.primary_uom_code,
1027 primary_unit_of_measure = cr.primary_unit_of_measure
1028 where inventory_item_id = l_item_id
1029 and set_process_id + 0 = xset_id
1030 and process_flag = 42
1031 and organization_id = cr.organization_id;
1032 end if;
1033
1034
1035
1036 end loop;
1037
1038 return(0);
1039 exception
1040 when LOGGING_ERR then
1041 return(dumm_status);
1042 when VALIDATE_ERR then
1043 dumm_status := INVPUOPI.mtl_log_interface_err(
1044 l_org_id,
1045 user_id,
1046 login_id,
1047 prog_appid,
1048 prog_id,
1049 request_id,
1050 trans_id,
1051 err_text,
1052 'validation_error ' || stmt,
1053 'MTL_SYSTEM_ITEMS_INTERFACE',
1054 'BOM_OP_VALIDATION_ERR',
1055 err_text);
1056 return(status);
1057 when OTHERS then
1058 err_text := substr('INVPVALI.validate_item_header3' || SQLERRM , 1 , 240 );
1059 return(SQLCODE);
1060 end validate_item_header3;
1061
1062 end INVPVDR3;