[Home] [Help]
PACKAGE BODY: APPS.INVPVDR2
Source
1 PACKAGE BODY INVPVDR2 AS
2 /* $Header: INVPVD2B.pls 120.7 2006/07/06 08:23:01 nshariff ship $ */
3
4 FUNCTION validate_item_header2
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 -- Variable required for the validation for UPDATES
19 loc_ctrl_code NUMBER;
20 cost_flag VARCHAR2(1);
21 inv_asset_flag VARCHAR2(1);
22 mrp_stock_code NUMBER;
23 base_item NUMBER;
24 lead_lot_size NUMBER;
25 out_op_flag VARCHAR2(1);
26 shelf_code NUMBER;
27 temp VARCHAR2(2);
28 temp_uom_code VARCHAR2(3);
29 temp_u_o_m VARCHAR2(25);
30 temp_uom_class VARCHAR2(10);
31 temp_enabled_flag VARCHAR2(1);
32 Prof_INV_CTP VARCHAR2(80);
33
34 -- Retrieve column values for validation
35
36 CURSOR cc is
37 select
38 ROWID,
39 TRANSACTION_ID,
40 ORGANIZATION_ID,
41 TRANSACTION_TYPE,
42 PROCESS_FLAG,
43 INVENTORY_ITEM_ID,
44 SUMMARY_FLAG,
45 ENABLED_FLAG,
46 START_DATE_ACTIVE,
47 END_DATE_ACTIVE,
48 DESCRIPTION,
49 BUYER_ID,
50 ACCOUNTING_RULE_ID,
51 INVOICING_RULE_ID,
52 SEGMENT1,
53 SEGMENT2,
54 SEGMENT3,
55 SEGMENT4,
56 SEGMENT5,
57 SEGMENT6,
58 SEGMENT7,
59 SEGMENT8,
60 SEGMENT9,
61 SEGMENT10,
62 SEGMENT11,
63 SEGMENT12,
64 SEGMENT13,
65 SEGMENT14,
66 SEGMENT15,
67 SEGMENT16,
68 SEGMENT17,
69 SEGMENT18,
70 SEGMENT19,
71 SEGMENT20,
72 ATTRIBUTE_CATEGORY,
73 ATTRIBUTE1,
74 ATTRIBUTE2,
75 ATTRIBUTE3,
76 ATTRIBUTE4,
77 ATTRIBUTE5,
78 ATTRIBUTE6,
79 ATTRIBUTE7,
80 ATTRIBUTE8,
81 ATTRIBUTE9,
82 ATTRIBUTE10,
83 ATTRIBUTE11,
84 ATTRIBUTE12,
85 ATTRIBUTE13,
86 ATTRIBUTE14,
87 ATTRIBUTE15,
88 PURCHASING_ITEM_FLAG,
89 SHIPPABLE_ITEM_FLAG,
90 CUSTOMER_ORDER_FLAG,
91 INTERNAL_ORDER_FLAG,
92 SERVICE_ITEM_FLAG,
93 INVENTORY_ITEM_FLAG,
94 ENG_ITEM_FLAG,
95 INVENTORY_ASSET_FLAG,
96 PURCHASING_ENABLED_FLAG,
97 CUSTOMER_ORDER_ENABLED_FLAG,
98 INTERNAL_ORDER_ENABLED_FLAG,
99 SO_TRANSACTIONS_FLAG,
100 MTL_TRANSACTIONS_ENABLED_FLAG,
101 STOCK_ENABLED_FLAG,
102 BOM_ENABLED_FLAG,
103 BUILD_IN_WIP_FLAG,
104 REVISION_QTY_CONTROL_CODE,
105 ITEM_CATALOG_GROUP_ID,
106 CATALOG_STATUS_FLAG,
107 RETURNABLE_FLAG,
108 DEFAULT_SHIPPING_ORG,
109 COLLATERAL_FLAG,
110 TAXABLE_FLAG,
111 QTY_RCV_EXCEPTION_CODE,
112 ALLOW_ITEM_DESC_UPDATE_FLAG,
113 INSPECTION_REQUIRED_FLAG,
114 RECEIPT_REQUIRED_FLAG,
115 MARKET_PRICE,
116 HAZARD_CLASS_ID,
117 RFQ_REQUIRED_FLAG,
118 QTY_RCV_TOLERANCE,
119 LIST_PRICE_PER_UNIT,
120 UN_NUMBER_ID,
121 PRICE_TOLERANCE_PERCENT,
122 ASSET_CATEGORY_ID,
123 ROUNDING_FACTOR,
124 UNIT_OF_ISSUE,
125 ENFORCE_SHIP_TO_LOCATION_CODE,
126 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
127 ALLOW_UNORDERED_RECEIPTS_FLAG,
128 ALLOW_EXPRESS_DELIVERY_FLAG,
129 DAYS_EARLY_RECEIPT_ALLOWED,
130 DAYS_LATE_RECEIPT_ALLOWED,
131 RECEIPT_DAYS_EXCEPTION_CODE,
132 RECEIVING_ROUTING_ID,
133 INVOICE_CLOSE_TOLERANCE,
134 RECEIVE_CLOSE_TOLERANCE,
135 AUTO_LOT_ALPHA_PREFIX,
136 START_AUTO_LOT_NUMBER,
137 LOT_CONTROL_CODE,
138 SHELF_LIFE_CODE,
139 SHELF_LIFE_DAYS,
140 SERIAL_NUMBER_CONTROL_CODE,
141 START_AUTO_SERIAL_NUMBER,
142 AUTO_SERIAL_ALPHA_PREFIX,
143 SOURCE_TYPE,
144 SOURCE_ORGANIZATION_ID,
145 SOURCE_SUBINVENTORY,
146 EXPENSE_ACCOUNT,
147 ENCUMBRANCE_ACCOUNT,
148 RESTRICT_SUBINVENTORIES_CODE,
149 UNIT_WEIGHT,
150 WEIGHT_UOM_CODE,
151 VOLUME_UOM_CODE,
152 UNIT_VOLUME,
153 RESTRICT_LOCATORS_CODE,
154 LOCATION_CONTROL_CODE,
155 SHRINKAGE_RATE,
156 ACCEPTABLE_EARLY_DAYS,
157 PLANNING_TIME_FENCE_CODE,
158 DEMAND_TIME_FENCE_CODE,
159 LEAD_TIME_LOT_SIZE,
160 STD_LOT_SIZE,
161 CUM_MANUFACTURING_LEAD_TIME,
162 OVERRUN_PERCENTAGE,
163 MRP_CALCULATE_ATP_FLAG,
164 ACCEPTABLE_RATE_INCREASE,
165 ACCEPTABLE_RATE_DECREASE,
166 CUMULATIVE_TOTAL_LEAD_TIME,
167 PLANNING_TIME_FENCE_DAYS,
168 DEMAND_TIME_FENCE_DAYS,
169 END_ASSEMBLY_PEGGING_FLAG,
170 REPETITIVE_PLANNING_FLAG,
171 PLANNING_EXCEPTION_SET,
172 BOM_ITEM_TYPE,
173 PICK_COMPONENTS_FLAG,
174 REPLENISH_TO_ORDER_FLAG,
175 BASE_ITEM_ID,
176 ATP_COMPONENTS_FLAG,
177 ATP_FLAG,
178 FIXED_LEAD_TIME,
179 VARIABLE_LEAD_TIME,
180 WIP_SUPPLY_LOCATOR_ID,
181 WIP_SUPPLY_TYPE,
182 WIP_SUPPLY_SUBINVENTORY,
183 PRIMARY_UOM_CODE,
184 PRIMARY_UNIT_OF_MEASURE,
185 ALLOWED_UNITS_LOOKUP_CODE,
186 COST_OF_SALES_ACCOUNT,
187 SALES_ACCOUNT,
188 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
189 INVENTORY_ITEM_STATUS_CODE,
190 INVENTORY_PLANNING_CODE,
191 PLANNER_CODE,
192 PLANNING_MAKE_BUY_CODE,
193 FIXED_LOT_MULTIPLIER,
194 ROUNDING_CONTROL_TYPE,
195 CARRYING_COST,
196 POSTPROCESSING_LEAD_TIME,
197 PREPROCESSING_LEAD_TIME,
198 FULL_LEAD_TIME,
199 ORDER_COST,
200 MRP_SAFETY_STOCK_PERCENT,
201 MRP_SAFETY_STOCK_CODE,
202 MIN_MINMAX_QUANTITY,
203 MAX_MINMAX_QUANTITY,
204 MINIMUM_ORDER_QUANTITY,
205 FIXED_ORDER_QUANTITY,
206 FIXED_DAYS_SUPPLY,
207 MAXIMUM_ORDER_QUANTITY,
208 ATP_RULE_ID,
209 PICKING_RULE_ID,
210 RESERVABLE_TYPE,
211 POSITIVE_MEASUREMENT_ERROR,
212 NEGATIVE_MEASUREMENT_ERROR,
213 ENGINEERING_ECN_CODE,
214 ENGINEERING_ITEM_ID,
215 ENGINEERING_DATE,
216 SERVICE_STARTING_DELAY,
217 VENDOR_WARRANTY_FLAG,
218 SERVICEABLE_COMPONENT_FLAG,
219 SERVICEABLE_PRODUCT_FLAG,
220 BASE_WARRANTY_SERVICE_ID,
221 PAYMENT_TERMS_ID,
222 PREVENTIVE_MAINTENANCE_FLAG,
223 PRIMARY_SPECIALIST_ID,
224 SECONDARY_SPECIALIST_ID,
225 SERVICEABLE_ITEM_CLASS_ID,
226 TIME_BILLABLE_FLAG,
227 MATERIAL_BILLABLE_FLAG,
228 EXPENSE_BILLABLE_FLAG,
229 PRORATE_SERVICE_FLAG,
230 COVERAGE_SCHEDULE_ID,
231 SERVICE_DURATION_PERIOD_CODE,
232 SERVICE_DURATION,
233 WARRANTY_VENDOR_ID,
234 MAX_WARRANTY_AMOUNT,
235 RESPONSE_TIME_PERIOD_CODE,
236 RESPONSE_TIME_VALUE,
237 NEW_REVISION_CODE,
238 INVOICEABLE_ITEM_FLAG,
239 TAX_CODE,
240 INVOICE_ENABLED_FLAG,
241 MUST_USE_APPROVED_VENDOR_FLAG,
242 REQUEST_ID,
243 PROGRAM_APPLICATION_ID,
244 PROGRAM_ID,
245 PROGRAM_UPDATE_DATE,
246 OUTSIDE_OPERATION_FLAG,
247 OUTSIDE_OPERATION_UOM_TYPE,
248 SAFETY_STOCK_BUCKET_DAYS,
249 AUTO_REDUCE_MPS,
250 COSTING_ENABLED_FLAG,
251 CYCLE_COUNT_ENABLED_FLAG,
252 AUTO_CREATED_CONFIG_FLAG,
253 ITEM_TYPE,
254 MODEL_CONFIG_CLAUSE_NAME,
255 SHIP_MODEL_COMPLETE_FLAG,
256 MRP_PLANNING_CODE,
257 RETURN_INSPECTION_REQUIREMENT,
258 ATO_FORECAST_CONTROL,
259 RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight cols added for 10.7 */
260 RELEASE_TIME_FENCE_DAYS,
261 CONTAINER_ITEM_FLAG,
262 CONTAINER_TYPE_CODE,
263 INTERNAL_VOLUME,
264 MAXIMUM_LOAD_WEIGHT,
265 MINIMUM_FILL_PERCENT,
266 VEHICLE_ITEM_FLAG
267 from MTL_SYSTEM_ITEMS_INTERFACE
268 where ((organization_id = org_id) or
269 (all_Org = 1))
270 and set_process_id = xset_id
271 and process_flag in ( 31,41);
272
273 msicount number;
274 msiicount number;
275 resersal_flag number;
276 dup_item_id number;
277 l_item_id number;
278 l_org_id number;
279 cat_set_id number;
280 trans_id number;
281 ext_flag number := 0;
282 error_msg varchar2(70);
283 status number;
284 dumm_status number;
285 master_org_id number;
286 stmt number;
287 LOGGING_ERR exception;
288 VALIDATE_ERR exception;
289 chart_of_acc_id number; /*NP 30AUG94*/
290 temp_proc_flag number;
291
292 l_org_name HR_ALL_ORGANIZATION_UNITS_VL.name%TYPE;
293 l_msg_text fnd_new_messages.message_text%TYPE;
294
295 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
296
297 begin
298
299 -- Retrieving fnd_profile values outside the loop for perf reasons.
300 Prof_INV_CTP := nvl( fnd_profile.value('INV_CTP'), 3);
301
302 for cr in cc loop
303 status := 0;
304 trans_id := cr.transaction_id;
305 l_org_id := cr.organization_id;
306 l_item_id := cr.inventory_item_id;
307 temp_proc_flag := cr.process_flag; -- Bug 4705184
308
309 -- Check for the second set of integrity rules/restrictions for item attributes
310
311 -- Validate Fixed Order Qty
312 IF cr.fixed_order_quantity <= 0 THEN
313 dumm_status := INVPUOPI.mtl_log_interface_err(
314 cr.organization_id,
315 user_id,
316 login_id,
317 prog_appid,
318 prog_id,
319 request_id,
320 cr.TRANSACTION_ID,
321 'Validation Error : Fixed Order Qty is <= 0 - Use a Value > 0 or Null',
322 'FIXED_ORDER_QUANTITY',
323 'MTL_SYSTEM_ITEMS_INTERFACE',
324 'INV_IOI_FIXED_ORDER_QTY',
325 err_text);
326 if dumm_status < 0 then
327 raise LOGGING_ERR;
328 end if;
329 status := 1;
330 END IF;
331
332 -- Validate Fixed Days Supply
333 IF cr.fixed_days_supply < 0 THEN
334 dumm_status := INVPUOPI.mtl_log_interface_err(
335 cr.organization_id,
336 user_id,
337 login_id,
338 prog_appid,
339 prog_id,
340 request_id,
341 cr.TRANSACTION_ID,
342 'Validation Error : Fixed Days Supply is < 0 - Use a Value >= 0 or Null',
343 'FIXED_DAYS_SUPPLY',
344 'MTL_SYSTEM_ITEMS_INTERFACE',
345 'INV_IOI_FIXED_SUP_DAYS',
346 err_text);
347 if dumm_status < 0 then
348 raise LOGGING_ERR;
349 end if;
350 status := 1;
351 END IF;
352 IF l_inv_debug_level IN(101, 102) THEN
353 INVPUTLI.info('INVPVDR2: Validating flags');
354 END IF;
355 -- validate ENCUMBRANCE_ACCOUNT
356 select ENCUMBRANCE_REVERSAL_FLAG
357 into resersal_flag
358 from mtl_parameters
359 where organization_id = cr.organization_id;
360
361 if resersal_flag = 1 and
362 cr.ENCUMBRANCE_ACCOUNT is NULL then
363 -- fix for 3108469
364 BEGIN
365 SELECT name
366 INTO l_org_name
367 FROM hr_all_organization_units_vl
368 WHERE organization_id = cr.organization_id;
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_org_name := cr.organization_id;
372 END;
373 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_ENC_ACCT_REQ');
374 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', l_org_name);
375 l_msg_text := FND_MESSAGE.GET;
376 dumm_status := INVPUOPI.mtl_log_interface_err(
377 cr.organization_id,
378 user_id,
379 login_id,
380 prog_appid,
381 prog_id,
382 request_id,
383 cr.TRANSACTION_ID,
384 l_msg_text,
385 'ENCUMBRANCE_ACCOUNT',
386 'MTL_SYSTEM_ITEMS_INTERFACE',
387 'INV_IOI_ERR',
388 err_text);
389 if dumm_status < 0 then
390 raise LOGGING_ERR;
391 end if;
392 status := 1;
393 end if;
394
395 stmt := 10;
396 -- validate SERVICE_DURATION
397 IF cr.service_duration_period_code IS NOT NULL AND
398 cr.service_duration IS NULL THEN
399 dumm_status := INVPUOPI.mtl_log_interface_err(
400 cr.organization_id,
401 user_id,
402 login_id,
403 prog_appid,
404 prog_id,
405 request_id,
406 cr.TRANSACTION_ID,
407 error_msg,
408 'SERVICE_DURATION',
409 'MTL_SYSTEM_ITEMS_INTERFACE',
410 'INV_SER_DURATION_MAND',
411 err_text);
412 if dumm_status < 0 then
413 raise LOGGING_ERR;
414 end if;
415 status := 1;
416 END IF;
417 /* Bug 1529024 : Validation of warranty_vendor_id is not required
418 if cr.vendor_warranty_flag = 'Y' and
419 cr.WARRANTY_VENDOR_ID is NULL then
420 dumm_status := INVPUOPI.mtl_log_interface_err(
421 cr.organization_id,
422 user_id,
423 login_id,
424 prog_appid,
425 prog_id,
426 request_id,
427 cr.TRANSACTION_ID,
428 error_msg,
429 'WARRANTY_VENDOR_ID',
430 'MTL_SYSTEM_ITEMS_INTERFACE',
431 'INV_WARRANTY_VEND_ID',
432 err_text);
433 if dumm_status < 0 then
434 raise LOGGING_ERR;
435 end if;
436 status := 1;
437 end if;
438 */
439 -- validate INSPECTION_REQUIRED_FLAG
440 if cr.INSPECTION_REQUIRED_FLAG = 'Y' and
441 (cr.RECEIVING_ROUTING_ID <> 2 or
442 cr.RECEIVING_ROUTING_ID is NULL)then
443 dumm_status := INVPUOPI.mtl_log_interface_err(
444 cr.organization_id,
445 user_id,
446 login_id,
447 prog_appid,
448 prog_id,
449 request_id,
450 cr.TRANSACTION_ID,
451 error_msg,
452 'INSPECTION_REQUIRED_FLAG',
453 'MTL_SYSTEM_ITEMS_INTERFACE',
454 'INV_INSPECTION_FLAG_ERR',
455 err_text);
456 if dumm_status < 0 then
457 raise LOGGING_ERR;
458 end if;
459 status := 1;
460 end if;
461
462 -- validate RESPONSE_TIME_VALUE
463 if cr.response_time_period_code is not null and
464 cr.RESPONSE_TIME_VALUE is NULL then
465 dumm_status := INVPUOPI.mtl_log_interface_err(
466 cr.organization_id,
467 user_id,
468 login_id,
469 prog_appid,
470 prog_id,
471 request_id,
472 cr.TRANSACTION_ID,
473 error_msg,
474 'RESPONSE_TIME_VALUE',
475 'MTL_SYSTEM_ITEMS_INTERFACE',
476 'INV_IOI_RESP_TIME_VAL',
477 err_text);
478 if dumm_status < 0 then
479 raise LOGGING_ERR;
480 end if;
481 status := 1;
482 end if;
483
484 -- validate DEMAND_TIME_FENCE_DAYS
485 if cr.demand_time_fence_code = 4 and
486 nvl(cr.DEMAND_TIME_FENCE_DAYS,-9) < 0 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 'DEMAND_TIME_FENCE_DAYS',
497 'MTL_SYSTEM_ITEMS_INTERFACE',
498 'INV_DEMAND_DAYS',
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 DEMAND_TIME_FENCE_DAYS
507 -- Bug 4485018 - Adding a clause to check for DEMAND_TIME_FENCE_CODE with null value
508
509 if ((cr.demand_time_fence_code <> 4 or cr.demand_time_fence_code is null) and
510 (cr.DEMAND_TIME_FENCE_DAYS is NOT NULL)) 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 'DEMAND_TIME_FENCE_DAYS',
521 'MTL_SYSTEM_ITEMS_INTERFACE',
522 'INV_IOI_DEMAND_TM_FENCE_DAYS',
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 PLANNING_TIME_FENCE_DAYS
531 if cr.planning_time_fence_code = 4 and
532 nvl(cr.PLANNING_TIME_FENCE_DAYS,-9) < 0 then
533 dumm_status := INVPUOPI.mtl_log_interface_err(
534 cr.organization_id,
535 user_id,
536 login_id,
537 prog_appid,
538 prog_id,
539 request_id,
540 cr.TRANSACTION_ID,
541 error_msg,
542 'PLANNING_TIME_FENCE_DAYS',
543 'MTL_SYSTEM_ITEMS_INTERFACE',
544 'INV_PLANNING_DAYS',
545 err_text);
546 if dumm_status < 0 then
547 raise LOGGING_ERR;
548 end if;
549 status := 1;
550 end if;
551
552 -- validate PLANNING_TIME_FENCE_DAYS
553 if cr.planning_time_fence_code <> 4 and
554 cr.PLANNING_TIME_FENCE_DAYS is NOT NULL then
555 dumm_status := INVPUOPI.mtl_log_interface_err(
556 cr.organization_id,
557 user_id,
558 login_id,
559 prog_appid,
560 prog_id,
561 request_id,
562 cr.TRANSACTION_ID,
563 error_msg,
564 'PLANNING_TIME_FENCE_DAYS',
565 'MTL_SYSTEM_ITEMS_INTERFACE',
566 'INV_IOI_PLANNING_TM_FENCE_DAYS',
567 err_text);
568 if dumm_status < 0 then
569 raise LOGGING_ERR;
570 end if;
571 status := 1;
572 end if;
573
574 -- validate RESTRICT_LOCATORS_CODE
575 if cr.location_control_code = 3 and
576 cr.RESTRICT_LOCATORS_CODE <> 2 then
577 dumm_status := INVPUOPI.mtl_log_interface_err(
578 cr.organization_id,
579 user_id,
580 login_id,
581 prog_appid,
582 prog_id,
583 request_id,
584 cr.TRANSACTION_ID,
585 error_msg,
586 'location_control_code',
587 'MTL_SYSTEM_ITEMS_INTERFACE',
588 'INV_DYNAMIC_LOCATORS',
589 err_text);
590 if dumm_status < 0 then
591 raise LOGGING_ERR;
592 end if;
593 status := 1;
594 end if;
595
596 -- validate RESTRICT_LOCATORS_CODE
597 /*NP 22-JUL-96
598 ** RESTRICT_LOCATORS_CODE = 2 related
599 ** incorrect validation taken out: see bug 383278
600 */
601 if cr.RESTRICT_LOCATORS_CODE = 1 then
602 if cr.RESTRICT_SUBINVENTORIES_CODE <> 1 then
603 dumm_status := INVPUOPI.mtl_log_interface_err(
604 cr.organization_id,
605 user_id,
606 login_id,
607 prog_appid,
608 prog_id,
609 request_id,
610 cr.TRANSACTION_ID,
611 error_msg,
612 'RESTRICT_LOCATORS_CODE',
613 'MTL_SYSTEM_ITEMS_INTERFACE',
614 'INV_LOC_NOT_RESTRICT',
615 err_text);
616 if dumm_status < 0 then
617 raise LOGGING_ERR;
618 end if;
619 status := 1;
620 end if;
621 if (cr.LOCATION_CONTROL_CODE <> 1 and
622 cr.LOCATION_CONTROL_CODE <> 2) 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 'RESTRICT_LOCATORS_CODE',
633 'MTL_SYSTEM_ITEMS_INTERFACE',
634 'INV_DYNAMIC_LOCATORS',
635 err_text);
636 if dumm_status < 0 then
637 raise LOGGING_ERR;
638 end if;
639 status := 1;
640 end if;
641 end if;
642
643 -- validate EXPENSE_ACCOUNT
644 if cr.inventory_asset_flag = 'N' and
645 cr.inventory_item_flag = 'Y' and
646 cr.EXPENSE_ACCOUNT is NULL 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 'EXPENSE_ACCOUNT',
657 'MTL_SYSTEM_ITEMS_INTERFACE',
658 'INV_EXP_ACCT_REQ', -- fix for BUG 3069139
659 err_text);
660 if dumm_status < 0 then
661 raise LOGGING_ERR;
662 end if;
663 status := 1;
664 end if;
665
666 -- validate inventory_asset_flag
667
668 if cr.costing_enabled_flag = 'N' and
669 cr.inventory_asset_flag = 'Y'
670 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 'INVENTORY_ASSET_FLAG',
681 'MTL_SYSTEM_ITEMS_INTERFACE',
682 'INV_COST_ASSET',
683 err_text);
684 if dumm_status < 0 then
685 raise LOGGING_ERR;
686 end if;
687 status := 1;
688 end if;
689
690 -- validate POSTPROCESSING_LEAD_TIME
691 if (cr.POSTPROCESSING_LEAD_TIME > 0 or
692 cr.POSTPROCESSING_LEAD_TIME < 0) and
693 cr.PLANNING_MAKE_BUY_CODE = 1 then
694 dumm_status := INVPUOPI.mtl_log_interface_err(
695 cr.organization_id,
696 user_id,
697 login_id,
698 prog_appid,
699 prog_id,
700 request_id,
701 cr.TRANSACTION_ID,
702 error_msg,
703 'POSTPROCESSING_LEADTIME',
704 'MTL_SYSTEM_ITEMS_INTERFACE',
705 'INV_IOI_POST_PROC_LEAD_TIME',
706 err_text);
707 if dumm_status < 0 then
708 raise LOGGING_ERR;
709 end if;
710 status := 1;
711 end if;
712
713 -- validate LEAD_TIME_LOT_SIZE
714 if (cr.LEAD_TIME_LOT_SIZE > 1 or
715 cr.LEAD_TIME_LOT_SIZE < 1) and
716 cr.REPETITIVE_PLANNING_FLAG = 'Y' then
717 dumm_status := INVPUOPI.mtl_log_interface_err(
718 cr.organization_id,
719 user_id,
720 login_id,
721 prog_appid,
722 prog_id,
723 request_id,
724 cr.TRANSACTION_ID,
725 error_msg,
726 'LEAD_TIME_LOT_SIZE',
727 'MTL_SYSTEM_ITEMS_INTERFACE',
728 'INV_IOI_LEAD_TIME_LOT_SIZE',
729 err_text);
730 if dumm_status < 0 then
731 raise LOGGING_ERR;
732 end if;
733 status := 1;
734 end if;
735
736 -- validate ATP_COMPONENTS_FLAG
737
738
739 if (Prof_INV_CTP <> 4 and cr.WIP_SUPPLY_TYPE <> 6 ) and
740 cr.ATP_COMPONENTS_FLAG <> 'N' and
741 cr.PICK_COMPONENTS_FLAG = 'N' and
742 cr.REPLENISH_TO_ORDER_FLAG = 'N' then
743 dumm_status := INVPUOPI.mtl_log_interface_err(
744 cr.organization_id,
745 user_id,
746 login_id,
747 prog_appid,
748 prog_id,
749 request_id,
750 cr.TRANSACTION_ID,
751 error_msg,
752 'ATP_COMPONENTS_FLAG1',
753 'MTL_SYSTEM_ITEMS_INTERFACE',
754 'INV_IOI_ATP_COMPS',
755 err_text);
756 if dumm_status < 0 then
757 raise LOGGING_ERR;
758 end if;
759 status := 1;
760 end if;
761 if( (Prof_INV_CTP = 1 and
762 cr.ATP_COMPONENTS_FLAG in ('C','R') and
763 cr.REPLENISH_TO_ORDER_FLAG = 'N' and
764 cr.BOM_ITEM_TYPE in (1,2,4)) or
765 (Prof_INV_CTP in (2, 5) and
766 cr.ATP_COMPONENTS_FLAG in ('C','R')) or
767 (Prof_INV_CTP = 3 and
768 cr.ATP_COMPONENTS_FLAG in ('C','R') and
769 cr.BOM_ITEM_TYPE in (1,2,4, 5)) or
770 (Prof_INV_CTP = 4 and
771 cr.ATP_COMPONENTS_FLAG in ('C','Y') and
772 cr.BOM_ITEM_TYPE = 5)) then
773 dumm_status := INVPUOPI.mtl_log_interface_err(
774 cr.organization_id,
775 user_id,
776 login_id,
777 prog_appid,
778 prog_id,
779 request_id,
780 cr.TRANSACTION_ID,
781 error_msg,
782 'ATP_COMPONENTS_FLAG1',
783 'MTL_SYSTEM_ITEMS_INTERFACE',
784 'INV_ATP_COMPS',
785 err_text);
786 if dumm_status < 0 then
787 raise LOGGING_ERR;
788 end if;
789 status := 1;
790 end if;
791
792 -- validate REPETITIVE_PLANNING_FLAG
793 if (cr.MRP_PLANNING_CODE = 3 or
794 cr.MRP_PLANNING_CODE = 4) and
795 cr.REPETITIVE_PLANNING_FLAG is null 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 'REPETITIVE_PLANNING_FLAG',
806 'MTL_SYSTEM_ITEMS_INTERFACE',
807 'INV_REPETITIVE_MAND',
808 err_text);
809 if dumm_status < 0 then
810 raise LOGGING_ERR;
811 end if;
812 status := 1;
813 end if;
814
815 -- validate MTL_TRANSACTIONS_ENABLED_FLAG
816 if cr.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y' and
817 cr.STOCK_ENABLED_FLAG = 'N' 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 'MTL_TRANSACTIONS_ENABLED_FLAG',
828 'MTL_SYSTEM_ITEMS_INTERFACE',
829 'INV_TRX',
830 err_text);
831 if dumm_status < 0 then
832 raise LOGGING_ERR;
833 end if;
834 status := 1;
835 end if;
836
837 -- validate INTERNAL_ORDER_ENABLED_FLAG
838 if cr.INTERNAL_ORDER_ENABLED_FLAG = 'Y' and
839 cr.INTERNAL_ORDER_FLAG = 'N' then
840 dumm_status := INVPUOPI.mtl_log_interface_err(
841 cr.organization_id,
842 user_id,
843 login_id,
844 prog_appid,
845 prog_id,
846 request_id,
847 cr.TRANSACTION_ID,
848 error_msg,
849 'INTERNAL_ORDER_ENABLED_FLAG',
850 'MTL_SYSTEM_ITEMS_INTERFACE',
851 'INV_INTERNAL_ENABLED',
852 err_text);
853 if dumm_status < 0 then
854 raise LOGGING_ERR;
855 end if;
856 status := 1;
857 end if;
858
859 -- BugFix # 1402975
860 --Validate INTERNAL_ORDER_FLAG and PICK_COMPONENTS_FLAG
861 if cr.INTERNAL_ORDER_FLAG = 'Y' and
862 cr.PICK_COMPONENTS_FLAG = 'Y' then
863 dumm_status := INVPUOPI.mtl_log_interface_err(
864 cr.organization_id,
865 user_id,
866 login_id,
867 prog_appid,
868 prog_id,
869 request_id,
870 cr.TRANSACTION_ID,
871 error_msg,
872 'PICK_COMPONENTS_FLAG',
873 'MTL_SYSTEM_ITEMS_INTERFACE',
874 'INV_PICK_COMPONENTS_FLAG',
875 err_text);
876 if dumm_status < 0 then
877 raise LOGGING_ERR;
878 end if;
879 status := 1;
880 end if;
881 -- End of Bug Fix 1402975
882
883 /*NP 20AUG96 new validation for 10.7 columns*/
884 -- validate CONTAINER_ITEM_FLAG
885 if cr.CONTAINER_ITEM_FLAG not in ('Y', 'N') then
886 dumm_status := INVPUOPI.mtl_log_interface_err(
887 cr.organization_id,
888 user_id,
889 login_id,
890 prog_appid,
891 prog_id,
892 request_id,
893 cr.TRANSACTION_ID,
894 error_msg,
895 'CONTAINER_ITEM_FLAG',
896 'MTL_SYSTEM_ITEMS_INTERFACE',
897 'INV_IOI_FLAG_Y_N',
898 err_text);
899 if dumm_status < 0 then
900 raise LOGGING_ERR;
901 end if;
902 status := 1;
903 end if;
904
905 -- validate VEHICLE_ITEM_FLAG
906 if cr.VEHICLE_ITEM_FLAG not in ('Y', 'N') then
907 dumm_status := INVPUOPI.mtl_log_interface_err(
908 cr.organization_id,
909 user_id,
910 login_id,
911 prog_appid,
912 prog_id,
913 request_id,
914 cr.TRANSACTION_ID,
915 error_msg,
916 'VEHICLE_ITEM_FLAG',
917 'MTL_SYSTEM_ITEMS_INTERFACE',
918 'INV_IOI_FLAG_Y_N',
919 err_text);
920 if dumm_status < 0 then
921 raise LOGGING_ERR;
922 end if;
923 status := 1;
924 end if;
925
926 -- validate MINIMUM_FILL_PERCENTAGE
927 if (cr.MINIMUM_FILL_PERCENT < 0
928 OR cr.MINIMUM_FILL_PERCENT > 100) 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 'MINIMUM_FILL_PERCENTAGE',
939 'MTL_SYSTEM_ITEMS_INTERFACE',
940 'INV_IOI_MIN_FILL_PERCENT',
941 err_text);
942 if dumm_status < 0 then
943 raise LOGGING_ERR;
944 end if;
945 status := 1;
946 end if;
947
948 -- validate CONTAINER_ITEM_CODE
949 if (cr.CONTAINER_ITEM_FLAG = 'N'
950 AND cr.CONTAINER_TYPE_CODE IS NOT NULL ) then
951 dumm_status := INVPUOPI.mtl_log_interface_err(
952 cr.organization_id,
953 user_id,
954 login_id,
955 prog_appid,
956 prog_id,
957 request_id,
958 cr.TRANSACTION_ID,
959 error_msg,
960 'CONTAINER_ITEM_CODE',
961 'MTL_SYSTEM_ITEMS_INTERFACE',
962 'INV_CONTAINER',
963 err_text);
964 if dumm_status < 0 then
965 raise LOGGING_ERR;
966 end if;
967 status := 1;
968 end if;
969
970 -- validate MAXIMUM_LOAD_WEIGHT
971 if (cr.CONTAINER_ITEM_FLAG = 'N'
972 AND cr.VEHICLE_ITEM_FLAG = 'N'
973 AND cr.MAXIMUM_LOAD_WEIGHT IS NOT NULL ) then
974 dumm_status := INVPUOPI.mtl_log_interface_err(
975 cr.organization_id,
976 user_id,
977 login_id,
978 prog_appid,
979 prog_id,
980 request_id,
981 cr.TRANSACTION_ID,
982 error_msg,
983 'MAXIMUM_LOAD_WEIGHT',
984 'MTL_SYSTEM_ITEMS_INTERFACE',
985 'INV_CONTAINER_OR_VEHICLE',
986 err_text);
987 if dumm_status < 0 then
988 raise LOGGING_ERR;
989 end if;
990 status := 1;
991 end if;
992
993 -- validate MINIMUM_FILL_PERCENT
994 if (cr.CONTAINER_ITEM_FLAG = 'N'
995 AND cr.VEHICLE_ITEM_FLAG = 'N'
996 AND cr.MINIMUM_FILL_PERCENT IS NOT NULL ) then
997 dumm_status := INVPUOPI.mtl_log_interface_err(
998 cr.organization_id,
999 user_id,
1000 login_id,
1001 prog_appid,
1002 prog_id,
1003 request_id,
1004 cr.TRANSACTION_ID,
1005 error_msg,
1006 'MINIMUM_FILL_PERCENT',
1007 'MTL_SYSTEM_ITEMS_INTERFACE',
1008 'INV_CONTAINER_OR_VEHICLE',
1009 err_text);
1010 if dumm_status < 0 then
1011 raise LOGGING_ERR;
1012 end if;
1013 status := 1;
1014 end if;
1015
1016 -- validate INTERNAL_VOLUME
1017 if (cr.CONTAINER_ITEM_FLAG = 'N'
1018 AND cr.VEHICLE_ITEM_FLAG = 'N'
1019 AND cr.INTERNAL_VOLUME IS NOT NULL ) then
1020 dumm_status := INVPUOPI.mtl_log_interface_err(
1021 cr.organization_id,
1022 user_id,
1023 login_id,
1024 prog_appid,
1025 prog_id,
1026 request_id,
1027 cr.TRANSACTION_ID,
1028 error_msg,
1029 'INTERNAL_VOLUME',
1030 'MTL_SYSTEM_ITEMS_INTERFACE',
1031 'INV_CONTAINER_OR_VEHICLE',
1032 err_text);
1033 if dumm_status < 0 then
1034 raise LOGGING_ERR;
1035 end if;
1036 status := 1;
1037 end if;
1038
1039 -- validate RELEASE_TIME_FENCE_CODE (RELEASE_TIME_FENCE_CODE)
1040 -- Added value 7 to existing values of Release Time Fence Code - R12 - Anmurali
1041 if (cr.RELEASE_TIME_FENCE_CODE not in (1,2,3,4,5,6,7)) then
1042 dumm_status := INVPUOPI.mtl_log_interface_err(
1043 cr.organization_id,
1044 user_id,
1045 login_id,
1046 prog_appid,
1047 prog_id,
1048 request_id,
1049 cr.TRANSACTION_ID,
1050 error_msg,
1051 'RELEASE_TIME_FENCE_CODE',
1052 'MTL_SYSTEM_ITEMS_INTERFACE',
1053 'INV_IOI_REL_TIME_FENCE_CODE',
1054 err_text);
1055 if dumm_status < 0 then
1056 raise LOGGING_ERR;
1057 end if;
1058 status := 1;
1059 end if;
1060
1061 -- validate RELEASE_TIME_FENCE_DAYS
1062 if cr.RELEASE_TIME_FENCE_CODE = 4 and
1063 nvl(cr.RELEASE_TIME_FENCE_DAYS,-9) < 0 then
1064 dumm_status := INVPUOPI.mtl_log_interface_err(
1065 cr.organization_id,
1066 user_id,
1067 login_id,
1068 prog_appid,
1069 prog_id,
1070 request_id,
1071 cr.TRANSACTION_ID,
1072 error_msg,
1073 'RELEASE_TIME_FENCE_DAYS',
1074 'MTL_SYSTEM_ITEMS_INTERFACE',
1075 'INV_RELEASE_DAYS',
1076 err_text);
1077 if dumm_status < 0 then
1078 raise LOGGING_ERR;
1079 end if;
1080 status := 1;
1081 end if;
1082
1083
1084 /* Bug 948771
1085 Added Validation For Release Time Fence Days
1086 When The Release Time Fence Code is Not USER DEFINED
1087 */
1088 -- validate AUTO_REL_TIME_FENCE_DAYS
1089 if (cr.RELEASE_TIME_FENCE_CODE <> 4 OR cr.RELEASE_TIME_FENCE_CODE IS NULL) and
1090 cr.RELEASE_TIME_FENCE_DAYS is NOT NULL then
1091 dumm_status := INVPUOPI.mtl_log_interface_err(
1092 cr.organization_id,
1093 user_id,
1094 login_id,
1095 prog_appid,
1096 prog_id,
1097 request_id,
1098 cr.TRANSACTION_ID,
1099 error_msg,
1100 'RELEASE_TIME_FENCE_DAYS',
1101 'MTL_SYSTEM_ITEMS_INTERFACE',
1102 'INV_IOI_RELEASE_TM_FENCE_DAYS',
1103 err_text);
1104 if dumm_status < 0 then
1105 raise LOGGING_ERR;
1106 end if;
1107 status := 1;
1108 end if;
1109
1110
1111 -- validate PICK_COMPONENTS_FLAG
1112 if cr.SHIP_MODEL_COMPLETE_FLAG = 'Y' and
1113 cr.PICK_COMPONENTS_FLAG = 'N' then
1114 dumm_status := INVPUOPI.mtl_log_interface_err(
1115 cr.organization_id,
1116 user_id,
1117 login_id,
1118 prog_appid,
1119 prog_id,
1120 request_id,
1121 cr.TRANSACTION_ID,
1122 error_msg,
1123 'PICK_COMPONENTS_FLAG',
1124 'MTL_SYSTEM_ITEMS_INTERFACE',
1125 'INV_IOI_PICK_COMPONENTS_FLAG',
1126 err_text);
1127 if dumm_status < 0 then
1128 raise LOGGING_ERR;
1129 end if;
1130 status := 1;
1131 end if;
1132
1133 /** Bug 1649399 added validation for shrinkage rate */
1134
1135 -- validate SHRINKAGE_RATE
1136 if (cr.SHRINKAGE_RATE >= 1) or (cr.SHRINKAGE_RATE < 0) then
1137 dumm_status := INVPUOPI.mtl_log_interface_err(
1138 cr.organization_id,
1139 user_id,
1140 login_id,
1141 prog_appid,
1142 prog_id,
1143 request_id,
1144 cr.TRANSACTION_ID,
1145 error_msg,
1146 'SHRINKAGE_RATE',
1147 'MTL_SYSTEM_ITEMS_INTERFACE',
1148 'INV_SHRINKAGE_RATE_ERR',
1149 err_text);
1150 if dumm_status < 0 then
1151 raise LOGGING_ERR;
1152 end if;
1153 status := 1;
1154 end if;
1155
1156 /* NP26DEC94 : New code to update process_flag.
1157 ** This code necessiated due to the breaking up INVPVHDR into
1158 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1159 ** Let's update the process flag for the record
1160 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1161 ** Need to do this ONLY if all previous validation okay.
1162 ** The process flag values that are possible at this time are
1163 ** 31, 41 :set by INVPVHDR
1164 */
1165 /* Bug 4705184. Get the process_flag value from cursor itself.
1166 select process_flag into temp_proc_flag
1167 from MTL_SYSTEM_ITEMS_INTERFACE
1168 where inventory_item_id = l_item_id
1169 and set_process_id + 0 = xset_id
1170 and process_flag in (31,41)
1171 and organization_id = cr.organization_id
1172 and rownum < 2; */
1173
1174 /*set value of process_flag to 42 or 32 depending on
1175 ** value of the variable: status.
1176 ** Essentially, we check to see if validation has not
1177 ** already failed in one of the previous packages.
1178 */
1179
1180 if temp_proc_flag <> 31 then
1181 update MTL_SYSTEM_ITEMS_INTERFACE
1182 set process_flag = DECODE(status,0,42,32),
1183 PRIMARY_UOM_CODE = cr.primary_uom_code,
1184 primary_unit_of_measure = cr.primary_unit_of_measure
1185 where inventory_item_id = l_item_id
1186 and set_process_id + 0 = xset_id
1187 and process_flag = 41
1188 and organization_id = cr.organization_id;
1189 end if;
1190
1191 end loop;
1192
1193 return(0);
1194 exception
1195 when LOGGING_ERR then
1196 return(dumm_status);
1197 when VALIDATE_ERR then
1198 dumm_status := INVPUOPI.mtl_log_interface_err(
1199 l_org_id,
1200 user_id,
1201 login_id,
1202 prog_appid,
1203 prog_id,
1204 request_id,
1205 trans_id,
1206 err_text,
1207 'validation_error ' || stmt,
1208 'MTL_SYSTEM_ITEMS_INTERFACE',
1209 'BOM_OP_VALIDATION_ERR',
1210 err_text);
1211 return(status);
1212 when OTHERS then
1213 err_text := substr('INVPVALI.validate_item_header2' || SQLERRM, 1,240);
1214 return(SQLCODE);
1215 end validate_item_header2;
1216
1217 end INVPVDR2;