[Home] [Help]
PACKAGE BODY: APPS.INVPVDR3
Source
1 package body INVPVDR3 as
2 /* $Header: INVPVD3B.pls 120.9.12020000.3 2012/08/24 02:53:49 kaizhao 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,
186 ALLOWED_UNITS_LOOKUP_CODE,
183 WIP_SUPPLY_SUBINVENTORY,
184 PRIMARY_UOM_CODE,
185 PRIMARY_UNIT_OF_MEASURE,
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 -- bug 10230460
281 template_id
282 from MTL_SYSTEM_ITEMS_INTERFACE
283 --Replacing organization_id + 0 with organization_id - Anmurali -Bug 4175124
284 where ((organization_id = org_id) or
285 (all_Org = 1))
286 and set_process_id = xset_id
287 and process_flag in ( 31, 32, 42);
288
289 /*NP 27DEC94
290 *New condition for process_flag;
291 *As a result of the breakup of INVPVHDR into smaller packages
292 *there are now new process_flag values possible
293 *INVPVHDR sets it to either 31 or 41
294 *INVPVDR2 may overwrite 41 by 42 or 32; will not overwrite 31
295 * Thus the values possible are: 31, 32, 42
296 */
297
298
299 msicount number;
300 msiicount number;
301 resersal_flag number;
302 dup_item_id number;
303 l_item_id number;
304 l_org_id number;
305 cat_set_id number;
306 trans_id number;
307 ext_flag number := 0;
308 error_msg varchar2(70);
309 status number;
310 dumm_status number;
311 master_org_id number;
312 stmt number;
313 LOGGING_ERR exception;
314 VALIDATE_ERR exception;
315 temp_proc_flag number;
316 l_err_msg varchar2(2000) := NULL;
317
318 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
319 -- bug10433961
320 l_temp_org_id NUMBER ;
321 begin
322
323 IF l_inv_debug_level IN(101, 102) THEN
324 INVPUTLI.info('Inside INVPVDR3');
325 END IF;
326 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
327
328 /*
329 ** validate the records
330 */
331
332 for cr in cc loop
333 status := 0;
334 trans_id := cr.transaction_id;
335 l_org_id := cr.organization_id;
336 l_item_id := cr.inventory_item_id; -- Bug 4705184
337 temp_proc_flag := cr.process_flag; -- Bug 4705184
338
339 /*NP 28DEC94
340 **A lot of item_id realted validation has now been removed from all the new
341 **INVPVDR*.sql packages that once constituted INVPVHDR
342 **This validation was redundant (and expensive!).
343 **
344 **The item_id related validation now takes place ONLY in INVPVHDR.sql.
345 **
346 **However, this has meant that the value for l_item_id (see INVPVHDR.sql) now
347 **needs to be gotten from the database
348 */
349 /* Bug 4705184. Get item_id from the cursor itself
350 select inventory_item_id
354 and set_process_id = xset_id; */
351 into l_item_id
352 from mtl_system_items_interface
353 where transaction_id = cr.transaction_id
355
356
357 /*
358 ** Validate fields with lookup values @@
359 */
360
361 /*INVPUTLI.info('INVPVDR3: Validating lookups'); */
362
363 IF l_inv_debug_level IN(101, 102) THEN
364 INVPUTLI.info('INVPVDR3: L8R values: '|| l_item_id||' '|| l_org_id);
365 INVPUTLI.info('INVPVDR3: L8R revision_qty_control_code: '|| cr.revision_qty_control_code);
366 END IF;
367
368 -- validate lookup
369 if (cr.revision_qty_control_code <> 1 and
370 cr.revision_qty_control_code <> 2) then
371
372 IF l_inv_debug_level IN(101, 102) THEN
373 INVPUTLI.info('INVPVDR3: inside the if..ie validation failed');
374 END IF;
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 'REVISION_QTY_CONTROL_CODE',
385 'MTL_SYSTEM_ITEMS_INTERFACE',
386 'INV_IOI_REV_QTY_CTRL_CODE',
387 err_text);
388 if dumm_status < 0 then
389 raise LOGGING_ERR;
390 end if;
391 status := 1;
392 end if;
393
394 -- validate lookup
395 if (cr.qty_rcv_exception_code <> 'NONE' and
396 cr.qty_rcv_exception_code <> 'REJECT' and
397 cr.qty_rcv_exception_code <> 'WARNING') then
398 dumm_status := INVPUOPI.mtl_log_interface_err(
399 cr.organization_id,
400 user_id,
401 login_id,
402 prog_appid,
403 prog_id,
404 request_id,
405 cr.TRANSACTION_ID,
406 error_msg,
407 'QTY_RCV_EXCEPTION_CODE',
408 'MTL_SYSTEM_ITEMS_INTERFACE',
409 'INV_IOI_QTY_RCV_EXC_CODE',
410 err_text);
411 if dumm_status < 0 then
412 raise LOGGING_ERR;
413 end if;
414 status := 1;
415 end if;
416
417 -- validate lookup
418 if (cr.enforce_ship_to_location_code <> 'NONE' and
419 cr.enforce_ship_to_location_code <> 'REJECT' and
420 cr.enforce_ship_to_location_code <> 'WARNING') then
421 dumm_status := INVPUOPI.mtl_log_interface_err(
422 cr.organization_id,
423 user_id,
424 login_id,
425 prog_appid,
426 prog_id,
427 request_id,
428 cr.TRANSACTION_ID,
429 error_msg,
430 'ENFORCE_SHIP_TO_LOCATION_CODE',
431 'MTL_SYSTEM_ITEMS_INTERFACE',
432 'INV_IOI_ENF_SHIP_TO_LOC',
433 err_text);
434 if dumm_status < 0 then
435 raise LOGGING_ERR;
436 end if;
437 status := 1;
438 end if;
439
440 -- validate lookup
441 if (cr.receipt_days_exception_code <> 'NONE' and
442 cr.receipt_days_exception_code <> 'REJECT' and
443 cr.receipt_days_exception_code <> 'WARNING') then
444 dumm_status := INVPUOPI.mtl_log_interface_err(
445 cr.organization_id,
446 user_id,
447 login_id,
448 prog_appid,
449 prog_id,
450 request_id,
451 cr.TRANSACTION_ID,
452 error_msg,
453 'RECEIPT_DAYS_EXCEPTION_CODE',
454 'MTL_SYSTEM_ITEMS_INTERFACE',
455 'INV_IOI_REC_DAYS_EXC',
456 err_text);
457 if dumm_status < 0 then
458 raise LOGGING_ERR;
459 end if;
460 status := 1;
461 end if;
462
463 -- validate lookup
464 if (cr.lot_control_code <> 1 and
465 cr.lot_control_code <> 2) then
466 dumm_status := INVPUOPI.mtl_log_interface_err(
467 cr.organization_id,
468 user_id,
469 login_id,
470 prog_appid,
471 prog_id,
472 request_id,
473 cr.TRANSACTION_ID,
474 error_msg,
475 'LOT_CONTROL_CODE',
476 'MTL_SYSTEM_ITEMS_INTERFACE',
477 'INV_IOI_LOT_CTRL_CODE',
478 err_text);
482 status := 1;
479 if dumm_status < 0 then
480 raise LOGGING_ERR;
481 end if;
483 end if;
484
485 -- validate lookup
486 if (cr.shelf_life_code not in (1, 2, 4)) then
487 dumm_status := INVPUOPI.mtl_log_interface_err(
488 cr.organization_id,
489 user_id,
490 login_id,
491 prog_appid,
492 prog_id,
493 request_id,
494 cr.TRANSACTION_ID,
495 error_msg,
496 'SHELF_LIFE_CODE',
497 'MTL_SYSTEM_ITEMS_INTERFACE',
498 'INV_IOI_SHELF_LIFE_CODE',
499 err_text);
500 if dumm_status < 0 then
501 raise LOGGING_ERR;
502 end if;
503 status := 1;
504 end if;
505
506 -- validate lookup
507 if (cr.serial_number_control_code <> 1 and
508 cr.serial_number_control_code <> 2 and
509 cr.serial_number_control_code <> 5 and
510 cr.serial_number_control_code <> 6 ) then
511 dumm_status := INVPUOPI.mtl_log_interface_err(
512 cr.organization_id,
513 user_id,
514 login_id,
515 prog_appid,
516 prog_id,
517 request_id,
518 cr.TRANSACTION_ID,
519 error_msg,
520 'SERIAL_NUMBER_CONTROL_CODE',
521 'MTL_SYSTEM_ITEMS_INTERFACE',
522 'INV_IOI_SERIAL_NUM_CONTROL',
523 err_text);
524 if dumm_status < 0 then
525 raise LOGGING_ERR;
526 end if;
527 status := 1;
528 end if;
529
530 -- validate lookup
531 if (cr.source_type is not null) then
532 if (cr.source_type <> 1 and
533 cr.source_type <> 2 and
534 cr.source_type <> 3) then
535 dumm_status := INVPUOPI.mtl_log_interface_err(
536 cr.organization_id,
537 user_id,
538 login_id,
539 prog_appid,
540 prog_id,
541 request_id,
542 cr.TRANSACTION_ID,
543 error_msg,
544 'SOURCE_TYPE',
545 'MTL_SYSTEM_ITEMS_INTERFACE',
546 'INV_IOI_SOURCE_TYPE',
547 err_text);
548 if dumm_status < 0 then
549 raise LOGGING_ERR;
550 end if;
551 status := 1;
552 end if;
553 end if;
554 -- validate lookup
555 if (cr.restrict_subinventories_code <> 1 and
556 cr.restrict_subinventories_code <> 2) then
557 dumm_status := INVPUOPI.mtl_log_interface_err(
558 cr.organization_id,
559 user_id,
560 login_id,
561 prog_appid,
562 prog_id,
563 request_id,
564 cr.TRANSACTION_ID,
565 error_msg,
566 'RESTRICT_SUBINVENTORIES_CODE',
567 'MTL_SYSTEM_ITEMS_INTERFACE',
568 'BOM_OP_VALIDATION_ERR',
569 err_text);
570 if dumm_status < 0 then
571 raise LOGGING_ERR;
572 end if;
573 status := 1;
574 end if;
575
576 -- validate lookup
577 if (cr.restrict_locators_code <> 1 and
578 cr.restrict_locators_code <> 2) then
579 dumm_status := INVPUOPI.mtl_log_interface_err(
580 cr.organization_id,
581 user_id,
582 login_id,
583 prog_appid,
584 prog_id,
585 request_id,
586 cr.TRANSACTION_ID,
587 error_msg,
588 'RESTRICT_LOCATOR_CODE',
589 'MTL_SYSTEM_ITEMS_INTERFACE',
590 'INV_IOI_REST_LOC_CODE',
591 err_text);
592 if dumm_status < 0 then
593 raise LOGGING_ERR;
594 end if;
595 status := 1;
596 end if;
597
598 -- validate lookup
599 if (cr.location_control_code <> 1 and
600 cr.location_control_code <> 2 and
601 cr.location_control_code <> 3) then
602 dumm_status := INVPUOPI.mtl_log_interface_err(
606 prog_appid,
603 cr.organization_id,
604 user_id,
605 login_id,
607 prog_id,
608 request_id,
609 cr.TRANSACTION_ID,
610 error_msg,
611 'LOCATION_CONTROL_CODE',
612 'MTL_SYSTEM_ITEMS_INTERFACE',
613 'INV_IOI_LOC_CTRL_CODE',
614 err_text);
615 if dumm_status < 0 then
616 raise LOGGING_ERR;
617 end if;
618 status := 1;
619 end if;
620
621 -- validate lookup
622 if (cr.planning_time_fence_code <> 1 and
623 cr.planning_time_fence_code <> 2 and
624 cr.planning_time_fence_code <> 3 and
625 cr.planning_time_fence_code <> 4) then
626 dumm_status := INVPUOPI.mtl_log_interface_err(
627 cr.organization_id,
628 user_id,
629 login_id,
630 prog_appid,
631 prog_id,
632 request_id,
633 cr.TRANSACTION_ID,
634 error_msg,
635 'PLANNING_TIME_FENCE_CODE',
636 'MTL_SYSTEM_ITEMS_INTERFACE',
637 'INV_IOI_PLN_TIME_FENCE_CODE',
638 err_text);
639 if dumm_status < 0 then
640 raise LOGGING_ERR;
641 end if;
642 status := 1;
643 end if;
644
645 -- validate lookup
646 if (cr.demand_time_fence_code <> 1 and
647 cr.demand_time_fence_code <> 2 and
648 cr.demand_time_fence_code <> 3 and
649 cr.demand_time_fence_code <> 4) then
650 dumm_status := INVPUOPI.mtl_log_interface_err(
651 cr.organization_id,
652 user_id,
653 login_id,
654 prog_appid,
655 prog_id,
656 request_id,
657 cr.TRANSACTION_ID,
658 error_msg,
659 'DEMAND_TIME_FENCE_CODE',
660 'MTL_SYSTEM_ITEMS_INTERFACE',
661 'INV_IOI_DEM_TIME_FENCE_CODE',
662 err_text);
663 if dumm_status < 0 then
664 raise LOGGING_ERR;
665 end if;
666 status := 1;
667 end if;
668 -- Bug No:3296502 Lot Status can be enabled only if Lot Control is Full Controlled
669 IF l_inv_debug_level IN(101, 102) THEN
670 INVPUTLI.info('INVPVDR3: verifying lot status....');
671 END IF;
672
673 if (cr.LOT_CONTROL_CODE = 1 and NVL(cr.LOT_STATUS_ENABLED,'N') = 'Y') then
674 dumm_status := INVPUOPI.mtl_log_interface_err(
675 cr.organization_id,
676 user_id,
677 login_id,
678 prog_appid,
679 prog_id,
680 request_id,
681 cr.TRANSACTION_ID,
682 error_msg,
683 'LOT_STATUS_ENABLED',
684 'MTL_SYSTEM_ITEMS_INTERFACE',
685 'INV_INVALID_LOT_STA_ENABLED',
686 err_text);
687 if dumm_status < 0 then
688 raise LOGGING_ERR;
689 end if;
690 status := 1;
691 end if;
692
693 -- serial_tagging Enh -- bug 9913552
694
695 IF l_inv_debug_level IN(101, 102) THEN
696 INVPUTLI.info('INVPVDR3: verifying Serial Number Control for serial tagging');
697 END IF;
698 if (cr.SERIAL_NUMBER_CONTROL_CODE in (2,5)) then
699 -- bug10433961
700 BEGIN
701 IF cr.template_id IS NOT NULL AND cr.template_id <>-1 AND cr.template_id <> FND_API.G_MISS_NUM THEN --bug14522565, avoid G_MISS_NUM
702 SELECT CONTEXT_ORGANIZATION_ID into l_temp_org_id
703 FROM mtl_item_templates
704 WHERE template_id=cr.template_id;
705 END IF;
706 end;
707
708
709 if INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => cr.inventory_item_id,
710 p_organization_id =>cr.organization_id)=2
711 --bug10433961
712 OR
713 (INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_template_id => cr.template_id
714 , p_organization_id => l_temp_org_id)=2) then
715
716 dumm_status := INVPUOPI.mtl_log_interface_err(
717 cr.organization_id,
718 user_id,
719 login_id,
720 prog_appid,
721 prog_id,
722 request_id,
723 cr.TRANSACTION_ID,
724 error_msg,
725 'SERIAL_NUMBER_CONTROL_CODE',
729 if dumm_status < 0 then
726 'MTL_SYSTEM_ITEMS_INTERFACE',
727 'INV_SER_CNT_FLG_NT_CHG',
728 err_text);
730 raise LOGGING_ERR;
731 end if;
732 status := 1;
733 end if;
734 end if;
735
736 -- Bug No:3296526 Serial Status can be enabled only if the item is Serial Controlled.
737
738 IF l_inv_debug_level IN(101, 102) THEN
739 INVPUTLI.info('INVPVDR3: verifying serial status....');
740 END IF;
741 if (cr.SERIAL_NUMBER_CONTROL_CODE = 1 and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'Y') then
742 --serial_tagging enh -- bug 9913552
743 IF (INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => cr.inventory_item_id,
744 p_organization_id => cr.organization_id)<>2)
745 -- bug 10230460
746 AND (INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_template_id => cr.template_id)<>2)
747 THEN
748
749 dumm_status := INVPUOPI.mtl_log_interface_err(
750 cr.organization_id,
751 user_id,
752 login_id,
753 prog_appid,
754 prog_id,
755 request_id,
756 cr.TRANSACTION_ID,
757 error_msg,
758 'SERIAL_STATUS_ENABLED',
759 'MTL_SYSTEM_ITEMS_INTERFACE',
760 'INV_INVALID_SER_STA_ENABLED',
761 err_text);
762 if dumm_status < 0 then
763 raise LOGGING_ERR;
764 end if;
765 status := 1;
766 end if;
767 end if;
768
769 -- Bug No:3306166 Safety COde must = 1 when mrp_planning_code=6
770 if ( cr.MRP_PLANNING_CODE = 6
771 -- Adding the NVL for Bug 5239406
772 and NVL(cr.drp_planned_flag,2) = 2 -- Adding the clause for R12
773 and cr.MRP_SAFETY_STOCK_CODE <> 1) then
774 dumm_status := INVPUOPI.mtl_log_interface_err(
775 cr.organization_id,
776 user_id,
777 login_id,
778 prog_appid,
779 prog_id,
780 request_id,
781 cr.TRANSACTION_ID,
782 error_msg,
783 'MRP_SAFETY_STOCK_CODE',
784 'MTL_SYSTEM_ITEMS_INTERFACE',
785 'INV_SAFETY_STOCK',
786 err_text);
787 if dumm_status < 0 then
788 raise LOGGING_ERR;
789 end if;
790 status := 1;
791 end if;
792 -- Start 2958422 : Effectivity Control Validations
793 if cr.EFFECTIVITY_CONTROL IS NOT NULL
794 AND cr.EFFECTIVITY_CONTROL NOT IN (1,2)
795 then
796 dumm_status := INVPUOPI.mtl_log_interface_err(
797 cr.organization_id,
798 user_id,
799 login_id,
800 prog_appid,
801 prog_id,
802 request_id,
803 cr.TRANSACTION_ID,
804 error_msg,
805 'EFFECTIVITY_CONTROL',
806 'MTL_SYSTEM_ITEMS_INTERFACE',
807 'INV_IOI_INVALID_EFFECT_CONTL',
808 err_text);
809 if dumm_status < 0 then
810 raise LOGGING_ERR;
811 end if;
812 status := 1;
813 end if;
814
815 if cr.EFFECTIVITY_CONTROL = 2
816 AND INV_ITEM_UTIL.g_Appl_Inst.PJM_Unit_Eff_flag ='N'
817 then
818 dumm_status := INVPUOPI.mtl_log_interface_err(
819 cr.organization_id,
820 user_id,
821 login_id,
822 prog_appid,
823 prog_id,
824 request_id,
825 cr.TRANSACTION_ID,
826 error_msg,
827 'EFFECTIVITY_CONTROL',
828 'MTL_SYSTEM_ITEMS_INTERFACE',
829 'INV_IOI_MODEL_UNIT_CONTROL_NA',
830 err_text);
831 if dumm_status < 0 then
832 raise LOGGING_ERR;
833 end if;
834 status := 1;
835 end if;
836 -- End 2958422 : Effectivity Control Validations
837
838 -- Bug No:3296755 SERIAL_NUMBER_CONTROL_CODE Must be 'At Recept' or 'Predefined' if Effectivity_control is 'Model/Unit Number'.
839 IF l_inv_debug_level IN(101, 102) THEN
840 INVPUTLI.info('INVPVDR3: verifying serial status....');
841 END IF;
842 if (cr.SERIAL_NUMBER_CONTROL_CODE = 1 and cr.EFFECTIVITY_CONTROL = 2) then
843 dumm_status := INVPUOPI.mtl_log_interface_err(
844 cr.organization_id,
848 prog_id,
845 user_id,
846 login_id,
847 prog_appid,
849 request_id,
850 cr.TRANSACTION_ID,
851 error_msg,
852 'SERIAL_NUMBER_CONTROL_CODE',
853 'MTL_SYSTEM_ITEMS_INTERFACE',
854 'ITM-EFFC-Invalid Serial Ctrl',
855 err_text);
856 if dumm_status < 0 then
857 raise LOGGING_ERR;
858 end if;
859 status := 1;
860 end if;
861
862 --Bug 4319349 Effectivity Control can be Model/Unit Number ONLY for BOM Item type Standard items -Anmurali
863 IF l_inv_debug_level IN(101, 102) THEN
864 INVPUTLI.info('INVPVDR3: verifying bom item type for effectivity control....');
865 END IF;
866 if (cr.BOM_ITEM_TYPE <> 4 and cr.EFFECTIVITY_CONTROL = 2) then
867 dumm_status := INVPUOPI.mtl_log_interface_err(
868 cr.organization_id,
869 user_id,
870 login_id,
871 prog_appid,
872 prog_id,
873 request_id,
874 cr.TRANSACTION_ID,
875 error_msg,
876 'BOM_ITEM_TYPE',
877 'MTL_SYSTEM_ITEMS_INTERFACE',
878 'ITM-EFFC-BOM Type is not Std',
879 err_text);
880 if dumm_status < 0 then
881 raise LOGGING_ERR;
882 end if;
883 status := 1;
884 end if;
885 -- Bug No:3308701 ORDERABLE_ON_WEB_FLAG Must be 'N' if CUSTOMER_ORDER_ENABLED_FLAG is not set.
886 IF l_inv_debug_level IN(101, 102) THEN
887 INVPUTLI.info('INVPVDR3: verifying serial status....');
888 END IF;
889 if (NVL(cr.CUSTOMER_ORDER_ENABLED_FLAG,'N') = 'N' and NVL(cr.ORDERABLE_ON_WEB_FLAG,'N') = 'Y') then
890 dumm_status := INVPUOPI.mtl_log_interface_err(
891 cr.organization_id,
892 user_id,
893 login_id,
894 prog_appid,
895 prog_id,
896 request_id,
897 cr.TRANSACTION_ID,
898 error_msg,
899 'ORDERABLE_ON_WEB_FLAG',
900 'MTL_SYSTEM_ITEMS_INTERFACE',
901 'INV_ORDERABLE_ON_WEB',
902 err_text);
903 if dumm_status < 0 then
904 raise LOGGING_ERR;
905 end if;
906 status := 1;
907 end if;
908
909 -- Bug No:3311672 When BOM Item type is Option Class or Model, then Assemble to order or Pick Components must be Yes
910 IF l_inv_debug_level IN(101, 102) THEN
911 INVPUTLI.info('INVPVDR3: verifying serial status....');
912 END IF;
913 if ((cr.BOM_ITEM_TYPE = 1 OR cr.BOM_ITEM_TYPE = 2 )
914 AND NVL(cr.PICK_COMPONENTS_FLAG,'N') = 'N' AND NVL(cr.REPLENISH_TO_ORDER_FLAG,'N') = 'N') then
915 dumm_status := INVPUOPI.mtl_log_interface_err(
916 cr.organization_id,
917 user_id,
918 login_id,
919 prog_appid,
920 prog_id,
921 request_id,
922 cr.TRANSACTION_ID,
923 error_msg,
924 'BOM_ITEM_TYPE',
925 'MTL_SYSTEM_ITEMS_INTERFACE',
926 'INV_BOM_TYPE',
927 err_text);
928 if dumm_status < 0 then
929 raise LOGGING_ERR;
930 end if;
931 status := 1;
932 end if;
933
934 -- Bug No:3294341 When SHELF_LIFE_CODE= 2 then SHELF_LIFE_DAYS should be > 0 and SHELF_LIFE_DAYS should always +Integer
935 /* Jalaj Srivastava Bug 4079744
936 Shelf life should be a positive number less than 999999 */
937 if (cr.SHELF_LIFE_DAYS < 0 OR cr.SHELF_LIFE_DAYS > 999999) then
938 l_err_msg := 'INV_INVALID_SHELF_LIFE_DAYS';
939 elsif(cr.SHELF_LIFE_CODE = 2 AND cr.SHELF_LIFE_DAYS = 0 )then
940 l_err_msg := 'INV_SHELF_DAYS_MUST_BE_GT_ZERO';
941 end if;
942 if (l_err_msg IS NOT NULL) then
943 dumm_status := INVPUOPI.mtl_log_interface_err(
944 cr.organization_id,
945 user_id,
946 login_id,
947 prog_appid,
948 prog_id,
949 request_id,
950 cr.TRANSACTION_ID,
951 error_msg,
952 'SHELF_LIFE_DAYS',
953 'MTL_SYSTEM_ITEMS_INTERFACE',
954 l_err_msg,
955 err_text);
956 if dumm_status < 0 then
957 raise LOGGING_ERR;
958 end if;
962 -- Bug No:3436107 if Equipment is set to yes, then Serial number generation has to be `Receipt or Predefined
959 status := 1;
960 end if;
961
963 if (cr.SERIAL_NUMBER_CONTROL_CODE NOT IN (2,5) and cr.EQUIPMENT_TYPE = 1) 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 'SERIAL_NUMBER_CONTROL_CODE',
974 'MTL_SYSTEM_ITEMS_INTERFACE',
975 'INV-ITM-EQMT-INVALID_SER_CTRL',
976 err_text);
977 if dumm_status < 0 then
978 raise LOGGING_ERR;
979 end if;
980 status := 1;
981 end if;
982 -- Bug No:3969580 If OVERCOMPLETION_TOLERANCE_TYPE is not specified then OVER_COMPLETION_TOLERANCE_VALUE must throw an error msg on entry
983 if (cr.OVERCOMPLETION_TOLERANCE_TYPE IS NULL)and (cr.OVERCOMPLETION_TOLERANCE_VALUE IS NOT NULL) then
984 dumm_status := INVPUOPI.mtl_log_interface_err(
985 cr.organization_id,
986 user_id,
987 login_id,
988 prog_appid,
989 prog_id,
990 request_id,
991 cr.TRANSACTION_ID,
992 error_msg,
993 'OVERCOMPLETION_TOLERANCE_VALUE',
994 'MTL_SYSTEM_ITEMS_INTERFACE',
995 'INV_WIP_OC_TOLERANCE_V_NUPD',
996 err_text);
997 if dumm_status < 0 then
998 raise LOGGING_ERR;
999 end if;
1000 status := 1;
1001 end if;
1002 --End of bug fix 3969580 By Anmurali
1003 -- Bug No:3421324 Default Lot/Serial Status is mandatory if Lot/Serial status is enabled
1004 l_err_msg := NULL;
1005 if (cr.DEFAULT_LOT_STATUS_ID IS NULL and NVL(cr.LOT_STATUS_ENABLED,'N') = 'Y') then
1006 l_err_msg := 'INV_DEF_LOT_STATUS_MAND';
1007 elsif (cr.DEFAULT_LOT_STATUS_ID IS NOT NULL and NVL(cr.LOT_STATUS_ENABLED,'N') = 'N') then
1008 l_err_msg := 'INV_DEF_LOT_STATUS_ID_NULL';
1009 end if;
1010 if l_err_msg IS NOT NULL then
1011 dumm_status := INVPUOPI.mtl_log_interface_err(
1012 cr.organization_id,
1013 user_id,
1014 login_id,
1015 prog_appid,
1016 prog_id,
1017 request_id,
1018 cr.TRANSACTION_ID,
1019 error_msg,
1020 'DEFAULT_LOT_STATUS_ID',
1021 'MTL_SYSTEM_ITEMS_INTERFACE',
1022 l_err_msg,
1023 err_text);
1024 if dumm_status < 0 then
1025 raise LOGGING_ERR;
1026 end if;
1027 status := 1;
1028 end if;
1029 l_err_msg := NULL;
1030 if (cr.DEFAULT_SERIAL_STATUS_ID IS NULL and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'Y') then
1031 l_err_msg := 'INV_DEF_SERIAL_STATUS_MAND';
1032 elsif (cr.DEFAULT_SERIAL_STATUS_ID IS NOT NULL and NVL(cr.SERIAL_STATUS_ENABLED,'N') = 'N') then
1033 l_err_msg := 'INV_DEF_SERIAL_STATUS_ID_NULL';
1034 end if;
1035 if l_err_msg IS NOT NULL then
1036 dumm_status := INVPUOPI.mtl_log_interface_err(
1037 cr.organization_id,
1038 user_id,
1039 login_id,
1040 prog_appid,
1041 prog_id,
1042 request_id,
1043 cr.TRANSACTION_ID,
1044 error_msg,
1045 'DEFAULT_SERIAL_STATUS_ID',
1046 'MTL_SYSTEM_ITEMS_INTERFACE',
1047 l_err_msg,
1048 err_text);
1049 if dumm_status < 0 then
1050 raise LOGGING_ERR;
1051 end if;
1052 status := 1;
1053 end if;
1054 /* NP26DEC94 : New code to update process_flag.
1055 ** This code necessiated due to the breaking up INVPVHDR into
1056 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1057 ** Let's update the process flag for the record
1058 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1059 ** Need to do this ONLY if all previous validation okay.
1060 ** The process flag values that are possible at this time are
1061 ** 31 :set by INVPVHDR
1062 ** 32, 42 :set by INVPVDR2
1063 */
1064 /* Bug 4705184. Get process flag from the cursor itself
1065 select process_flag into temp_proc_flag
1066 from MTL_SYSTEM_ITEMS_INTERFACE
1067 where inventory_item_id = l_item_id
1068 and organization_id = cr.organization_id
1069 and process_flag in (31,32,42)
1070 and set_process_id = xset_id
1071 and rownum < 2 ; */
1072
1073 /*set value of process_flag to 43 or 33 depending on
1074 ** value of the variable: status.
1075 ** Essentially, we check to see if validation has not already failed in one of
1076 ** the previous packages.
1077 */
1078 if (temp_proc_flag <> 31 and temp_proc_flag <> 32) then
1079 update MTL_SYSTEM_ITEMS_INTERFACE
1080 set process_flag = DECODE(status,0,43,33),
1081 PRIMARY_UOM_CODE = cr.primary_uom_code,
1082 primary_unit_of_measure = cr.primary_unit_of_measure
1083 where inventory_item_id = l_item_id
1084 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
1085 and set_process_id = xset_id
1086 and process_flag = 42
1087 and organization_id = cr.organization_id;
1088 end if;
1089
1090
1091
1092 end loop;
1093
1094 return(0);
1095 exception
1096 when LOGGING_ERR then
1097 return(dumm_status);
1098 when VALIDATE_ERR then
1099 dumm_status := INVPUOPI.mtl_log_interface_err(
1100 l_org_id,
1101 user_id,
1102 login_id,
1103 prog_appid,
1104 prog_id,
1105 request_id,
1106 trans_id,
1107 err_text,
1108 'validation_error ' || stmt,
1109 'MTL_SYSTEM_ITEMS_INTERFACE',
1110 'BOM_OP_VALIDATION_ERR',
1111 err_text);
1112 return(status);
1113 when OTHERS then
1114 err_text := substr('INVPVALI.validate_item_header3' || SQLERRM , 1 , 240 );
1115 return(SQLCODE);
1116 end validate_item_header3;
1117
1118 end INVPVDR3;