[Home] [Help]
PACKAGE BODY: APPS.INVPVDR2
Source
1 PACKAGE BODY INVPVDR2 AS
2 /* $Header: INVPVD2B.pls 120.8.12020000.5 2013/01/23 08:35:18 lmai 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_ENC_ACCT_REQ', --bug 11894684, replace 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.costing_enabled_flag = 'N' AND -- Bug 13418048
645 (cr.purchasing_item_flag = 'N' AND
646 cr.purchasing_enabled_flag = 'N') THEN
647
648 INVPUTLI.info('Do not do the expense account checking for the item which is not costed or purchased');
649
650 -- check expense a/c for the item which is costed and purchased
651 elsif cr.inventory_asset_flag = 'N' and
652 cr.inventory_item_flag = 'Y' and
653 cr.EXPENSE_ACCOUNT is NULL then
654 dumm_status := INVPUOPI.mtl_log_interface_err(
655 cr.organization_id,
656 user_id,
657 login_id,
658 prog_appid,
659 prog_id,
660 request_id,
661 cr.TRANSACTION_ID,
662 error_msg,
663 'EXPENSE_ACCOUNT',
664 'MTL_SYSTEM_ITEMS_INTERFACE',
665 'INV_EXP_ACCT_REQ', -- fix for BUG 3069139
666 err_text);
667 if dumm_status < 0 then
668 raise LOGGING_ERR;
669 end if;
670 status := 1;
671 end if;
672
673 -- validate inventory_asset_flag
674
675 if cr.costing_enabled_flag = 'N' and
676 cr.inventory_asset_flag = 'Y'
677 then
678 dumm_status := INVPUOPI.mtl_log_interface_err(
679 cr.organization_id,
680 user_id,
681 login_id,
682 prog_appid,
683 prog_id,
684 request_id,
685 cr.TRANSACTION_ID,
686 error_msg,
687 'INVENTORY_ASSET_FLAG',
688 'MTL_SYSTEM_ITEMS_INTERFACE',
689 'INV_COST_ASSET',
690 err_text);
691 if dumm_status < 0 then
692 raise LOGGING_ERR;
693 end if;
694 status := 1;
695 end if;
696
697 -- validate POSTPROCESSING_LEAD_TIME
698 if (cr.POSTPROCESSING_LEAD_TIME > 0 or
699 cr.POSTPROCESSING_LEAD_TIME < 0) and
700 cr.PLANNING_MAKE_BUY_CODE = 1 then
701 dumm_status := INVPUOPI.mtl_log_interface_err(
702 cr.organization_id,
703 user_id,
704 login_id,
705 prog_appid,
706 prog_id,
707 request_id,
708 cr.TRANSACTION_ID,
709 error_msg,
710 'POSTPROCESSING_LEADTIME',
711 'MTL_SYSTEM_ITEMS_INTERFACE',
712 'INV_IOI_POST_PROC_LEAD_TIME',
713 err_text);
714 if dumm_status < 0 then
715 raise LOGGING_ERR;
716 end if;
717 status := 1;
718 end if;
719
720 -- validate LEAD_TIME_LOT_SIZE
721 if (cr.LEAD_TIME_LOT_SIZE > 1 or
722 cr.LEAD_TIME_LOT_SIZE < 1) and
723 cr.REPETITIVE_PLANNING_FLAG = 'Y' then
724 dumm_status := INVPUOPI.mtl_log_interface_err(
725 cr.organization_id,
726 user_id,
727 login_id,
728 prog_appid,
729 prog_id,
730 request_id,
731 cr.TRANSACTION_ID,
732 error_msg,
733 'LEAD_TIME_LOT_SIZE',
734 'MTL_SYSTEM_ITEMS_INTERFACE',
735 'INV_IOI_LEAD_TIME_LOT_SIZE',
736 err_text);
737 if dumm_status < 0 then
738 raise LOGGING_ERR;
739 end if;
740 status := 1;
741 end if;
742
743 -- validate ATP_COMPONENTS_FLAG
744
745
746 if (Prof_INV_CTP <> 4 and cr.WIP_SUPPLY_TYPE <> 6 ) and
747 cr.ATP_COMPONENTS_FLAG <> 'N' and
748 cr.PICK_COMPONENTS_FLAG = 'N' and
749 cr.REPLENISH_TO_ORDER_FLAG = 'N' 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 'ATP_COMPONENTS_FLAG1',
760 'MTL_SYSTEM_ITEMS_INTERFACE',
761 'INV_IOI_ATP_COMPS',
762 err_text);
763 if dumm_status < 0 then
764 raise LOGGING_ERR;
765 end if;
766 status := 1;
767 end if;
768 if( (Prof_INV_CTP = 1 and
769 cr.ATP_COMPONENTS_FLAG in ('C','R') and
770 cr.REPLENISH_TO_ORDER_FLAG = 'N' and
771 cr.BOM_ITEM_TYPE in (1,2,4)) or
772 (Prof_INV_CTP in (2, 5) and
773 cr.ATP_COMPONENTS_FLAG in ('C','R')) or
774 (Prof_INV_CTP = 3 and
775 cr.ATP_COMPONENTS_FLAG in ('C','R') and
776 cr.BOM_ITEM_TYPE in (1,2,4, 5)) or
777 (Prof_INV_CTP = 4 and
778 cr.ATP_COMPONENTS_FLAG in ('C','Y') and
779 cr.BOM_ITEM_TYPE = 5)) then
780 dumm_status := INVPUOPI.mtl_log_interface_err(
781 cr.organization_id,
782 user_id,
783 login_id,
784 prog_appid,
785 prog_id,
786 request_id,
787 cr.TRANSACTION_ID,
788 error_msg,
789 'ATP_COMPONENTS_FLAG1',
790 'MTL_SYSTEM_ITEMS_INTERFACE',
791 'INV_ATP_COMPS',
792 err_text);
793 if dumm_status < 0 then
794 raise LOGGING_ERR;
795 end if;
796 status := 1;
797 end if;
798
799 -- validate REPETITIVE_PLANNING_FLAG
800 if (cr.MRP_PLANNING_CODE = 3 or
801 cr.MRP_PLANNING_CODE = 4) and
802 cr.REPETITIVE_PLANNING_FLAG is null then
803 dumm_status := INVPUOPI.mtl_log_interface_err(
804 cr.organization_id,
805 user_id,
806 login_id,
807 prog_appid,
808 prog_id,
809 request_id,
810 cr.TRANSACTION_ID,
811 error_msg,
812 'REPETITIVE_PLANNING_FLAG',
813 'MTL_SYSTEM_ITEMS_INTERFACE',
814 'INV_REPETITIVE_MAND',
815 err_text);
816 if dumm_status < 0 then
817 raise LOGGING_ERR;
818 end if;
819 status := 1;
820 end if;
821
822 -- validate MTL_TRANSACTIONS_ENABLED_FLAG
823 if cr.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y' and
824 cr.STOCK_ENABLED_FLAG = 'N' then
825 dumm_status := INVPUOPI.mtl_log_interface_err(
826 cr.organization_id,
827 user_id,
828 login_id,
829 prog_appid,
830 prog_id,
831 request_id,
832 cr.TRANSACTION_ID,
833 error_msg,
834 'MTL_TRANSACTIONS_ENABLED_FLAG',
835 'MTL_SYSTEM_ITEMS_INTERFACE',
836 'INV_TRX',
837 err_text);
838 if dumm_status < 0 then
839 raise LOGGING_ERR;
840 end if;
841 status := 1;
842 end if;
843
844 -- validate INTERNAL_ORDER_ENABLED_FLAG
845 if cr.INTERNAL_ORDER_ENABLED_FLAG = 'Y' and
846 cr.INTERNAL_ORDER_FLAG = 'N' then
847 dumm_status := INVPUOPI.mtl_log_interface_err(
848 cr.organization_id,
849 user_id,
850 login_id,
851 prog_appid,
852 prog_id,
853 request_id,
854 cr.TRANSACTION_ID,
855 error_msg,
856 'INTERNAL_ORDER_ENABLED_FLAG',
857 'MTL_SYSTEM_ITEMS_INTERFACE',
858 'INV_INTERNAL_ENABLED',
859 err_text);
860 if dumm_status < 0 then
861 raise LOGGING_ERR;
862 end if;
863 status := 1;
864 end if;
865
866 -- BugFix # 1402975
867 --Validate INTERNAL_ORDER_FLAG and PICK_COMPONENTS_FLAG
868 if cr.INTERNAL_ORDER_FLAG = 'Y' and
869 cr.PICK_COMPONENTS_FLAG = 'Y' then
870 dumm_status := INVPUOPI.mtl_log_interface_err(
871 cr.organization_id,
872 user_id,
873 login_id,
874 prog_appid,
875 prog_id,
876 request_id,
877 cr.TRANSACTION_ID,
878 error_msg,
879 'PICK_COMPONENTS_FLAG',
880 'MTL_SYSTEM_ITEMS_INTERFACE',
881 'INV_PICK_COMPONENTS_FLAG',
882 err_text);
883 if dumm_status < 0 then
884 raise LOGGING_ERR;
885 end if;
886 status := 1;
887 end if;
888 -- End of Bug Fix 1402975
889
890 /*NP 20AUG96 new validation for 10.7 columns*/
891 -- validate CONTAINER_ITEM_FLAG
892 if cr.CONTAINER_ITEM_FLAG not in ('Y', 'N') then
893 dumm_status := INVPUOPI.mtl_log_interface_err(
894 cr.organization_id,
895 user_id,
896 login_id,
897 prog_appid,
898 prog_id,
899 request_id,
900 cr.TRANSACTION_ID,
901 error_msg,
902 'CONTAINER_ITEM_FLAG',
903 'MTL_SYSTEM_ITEMS_INTERFACE',
904 'INV_IOI_FLAG_Y_N',
905 err_text);
906 if dumm_status < 0 then
907 raise LOGGING_ERR;
908 end if;
909 status := 1;
910 end if;
911
912 -- validate VEHICLE_ITEM_FLAG
913 if cr.VEHICLE_ITEM_FLAG not in ('Y', 'N') then
914 dumm_status := INVPUOPI.mtl_log_interface_err(
915 cr.organization_id,
916 user_id,
917 login_id,
918 prog_appid,
919 prog_id,
920 request_id,
921 cr.TRANSACTION_ID,
922 error_msg,
923 'VEHICLE_ITEM_FLAG',
924 'MTL_SYSTEM_ITEMS_INTERFACE',
925 'INV_IOI_FLAG_Y_N',
926 err_text);
927 if dumm_status < 0 then
928 raise LOGGING_ERR;
929 end if;
930 status := 1;
931 end if;
932
933 -- validate MINIMUM_FILL_PERCENTAGE
934 if (cr.MINIMUM_FILL_PERCENT < 0
935 OR cr.MINIMUM_FILL_PERCENT > 100) then
936 dumm_status := INVPUOPI.mtl_log_interface_err(
937 cr.organization_id,
938 user_id,
939 login_id,
940 prog_appid,
941 prog_id,
942 request_id,
943 cr.TRANSACTION_ID,
944 error_msg,
945 'MINIMUM_FILL_PERCENTAGE',
946 'MTL_SYSTEM_ITEMS_INTERFACE',
947 'INV_IOI_MIN_FILL_PERCENT',
948 err_text);
949 if dumm_status < 0 then
950 raise LOGGING_ERR;
951 end if;
952 status := 1;
953 end if;
954
955 -- validate CONTAINER_ITEM_CODE
956 if (cr.CONTAINER_ITEM_FLAG = 'N'
957 AND cr.CONTAINER_TYPE_CODE IS NOT NULL ) then
958 dumm_status := INVPUOPI.mtl_log_interface_err(
959 cr.organization_id,
960 user_id,
961 login_id,
962 prog_appid,
963 prog_id,
964 request_id,
965 cr.TRANSACTION_ID,
966 error_msg,
967 'CONTAINER_ITEM_CODE',
968 'MTL_SYSTEM_ITEMS_INTERFACE',
969 'INV_CONTAINER',
970 err_text);
971 if dumm_status < 0 then
972 raise LOGGING_ERR;
973 end if;
974 status := 1;
975 end if;
976
977 -- validate MAXIMUM_LOAD_WEIGHT
978 if (cr.CONTAINER_ITEM_FLAG = 'N'
979 AND cr.VEHICLE_ITEM_FLAG = 'N'
980 AND cr.MAXIMUM_LOAD_WEIGHT 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 'MAXIMUM_LOAD_WEIGHT',
991 'MTL_SYSTEM_ITEMS_INTERFACE',
992 'INV_CONTAINER_OR_VEHICLE',
993 err_text);
994 if dumm_status < 0 then
995 raise LOGGING_ERR;
996 end if;
997 status := 1;
998 end if;
999
1000 -- validate MINIMUM_FILL_PERCENT
1001 if (cr.CONTAINER_ITEM_FLAG = 'N'
1002 AND cr.VEHICLE_ITEM_FLAG = 'N'
1003 AND cr.MINIMUM_FILL_PERCENT IS NOT NULL ) then
1004 dumm_status := INVPUOPI.mtl_log_interface_err(
1005 cr.organization_id,
1006 user_id,
1007 login_id,
1008 prog_appid,
1009 prog_id,
1010 request_id,
1011 cr.TRANSACTION_ID,
1012 error_msg,
1013 'MINIMUM_FILL_PERCENT',
1014 'MTL_SYSTEM_ITEMS_INTERFACE',
1015 'INV_CONTAINER_OR_VEHICLE',
1016 err_text);
1017 if dumm_status < 0 then
1018 raise LOGGING_ERR;
1019 end if;
1020 status := 1;
1021 end if;
1022
1023 -- validate INTERNAL_VOLUME
1024 if (cr.CONTAINER_ITEM_FLAG = 'N'
1025 AND cr.VEHICLE_ITEM_FLAG = 'N'
1026 AND cr.INTERNAL_VOLUME IS NOT NULL ) then
1027 dumm_status := INVPUOPI.mtl_log_interface_err(
1028 cr.organization_id,
1029 user_id,
1030 login_id,
1031 prog_appid,
1032 prog_id,
1033 request_id,
1034 cr.TRANSACTION_ID,
1035 error_msg,
1036 'INTERNAL_VOLUME',
1037 'MTL_SYSTEM_ITEMS_INTERFACE',
1038 'INV_CONTAINER_OR_VEHICLE',
1039 err_text);
1040 if dumm_status < 0 then
1041 raise LOGGING_ERR;
1042 end if;
1043 status := 1;
1044 end if;
1045
1046 -- validate RELEASE_TIME_FENCE_CODE (RELEASE_TIME_FENCE_CODE)
1047 -- Added value 7 to existing values of Release Time Fence Code - R12 - Anmurali
1048 if (cr.RELEASE_TIME_FENCE_CODE not in (1,2,3,4,5,6,7)) then
1049 dumm_status := INVPUOPI.mtl_log_interface_err(
1050 cr.organization_id,
1051 user_id,
1052 login_id,
1053 prog_appid,
1054 prog_id,
1055 request_id,
1056 cr.TRANSACTION_ID,
1057 error_msg,
1058 'RELEASE_TIME_FENCE_CODE',
1059 'MTL_SYSTEM_ITEMS_INTERFACE',
1060 'INV_IOI_REL_TIME_FENCE_CODE',
1061 err_text);
1062 if dumm_status < 0 then
1063 raise LOGGING_ERR;
1064 end if;
1065 status := 1;
1066 end if;
1067
1068 -- validate RELEASE_TIME_FENCE_DAYS
1069 if cr.RELEASE_TIME_FENCE_CODE = 4 and
1070 nvl(cr.RELEASE_TIME_FENCE_DAYS,-9) < 0 then
1071 dumm_status := INVPUOPI.mtl_log_interface_err(
1072 cr.organization_id,
1073 user_id,
1074 login_id,
1075 prog_appid,
1076 prog_id,
1077 request_id,
1078 cr.TRANSACTION_ID,
1079 error_msg,
1080 'RELEASE_TIME_FENCE_DAYS',
1081 'MTL_SYSTEM_ITEMS_INTERFACE',
1082 'INV_RELEASE_DAYS',
1083 err_text);
1084 if dumm_status < 0 then
1085 raise LOGGING_ERR;
1086 end if;
1087 status := 1;
1088 end if;
1089
1090
1091 /* Bug 948771
1092 Added Validation For Release Time Fence Days
1093 When The Release Time Fence Code is Not USER DEFINED
1094 */
1095 -- validate AUTO_REL_TIME_FENCE_DAYS
1096 if (cr.RELEASE_TIME_FENCE_CODE <> 4 OR cr.RELEASE_TIME_FENCE_CODE IS NULL) and
1097 cr.RELEASE_TIME_FENCE_DAYS is NOT NULL then
1098 dumm_status := INVPUOPI.mtl_log_interface_err(
1099 cr.organization_id,
1100 user_id,
1101 login_id,
1102 prog_appid,
1103 prog_id,
1104 request_id,
1105 cr.TRANSACTION_ID,
1106 error_msg,
1107 'RELEASE_TIME_FENCE_DAYS',
1108 'MTL_SYSTEM_ITEMS_INTERFACE',
1109 'INV_IOI_RELEASE_TM_FENCE_DAYS',
1110 err_text);
1111 if dumm_status < 0 then
1112 raise LOGGING_ERR;
1113 end if;
1114 status := 1;
1115 end if;
1116
1117
1118 -- validate PICK_COMPONENTS_FLAG
1119 if cr.SHIP_MODEL_COMPLETE_FLAG = 'Y' and
1120 cr.PICK_COMPONENTS_FLAG = 'N' then
1121 dumm_status := INVPUOPI.mtl_log_interface_err(
1122 cr.organization_id,
1123 user_id,
1124 login_id,
1125 prog_appid,
1126 prog_id,
1127 request_id,
1128 cr.TRANSACTION_ID,
1129 error_msg,
1130 'PICK_COMPONENTS_FLAG',
1131 'MTL_SYSTEM_ITEMS_INTERFACE',
1132 'INV_IOI_PICK_COMPONENTS_FLAG',
1133 err_text);
1134 if dumm_status < 0 then
1135 raise LOGGING_ERR;
1136 end if;
1137 status := 1;
1138 end if;
1139
1140 /** Bug 1649399 added validation for shrinkage rate */
1141
1142 -- validate SHRINKAGE_RATE
1143 if (cr.SHRINKAGE_RATE >= 1) or (cr.SHRINKAGE_RATE < 0) then
1144 dumm_status := INVPUOPI.mtl_log_interface_err(
1145 cr.organization_id,
1146 user_id,
1147 login_id,
1148 prog_appid,
1149 prog_id,
1150 request_id,
1151 cr.TRANSACTION_ID,
1152 error_msg,
1153 'SHRINKAGE_RATE',
1154 'MTL_SYSTEM_ITEMS_INTERFACE',
1155 'INV_SHRINKAGE_RATE_ERR',
1156 err_text);
1157 if dumm_status < 0 then
1158 raise LOGGING_ERR;
1159 end if;
1160 status := 1;
1161 end if;
1162
1163 /* NP26DEC94 : New code to update process_flag.
1164 ** This code necessiated due to the breaking up INVPVHDR into
1165 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1166 ** Let's update the process flag for the record
1167 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1168 ** Need to do this ONLY if all previous validation okay.
1169 ** The process flag values that are possible at this time are
1170 ** 31, 41 :set by INVPVHDR
1171 */
1172 /* Bug 4705184. Get the process_flag value from cursor itself.
1173 select process_flag into temp_proc_flag
1174 from MTL_SYSTEM_ITEMS_INTERFACE
1175 where inventory_item_id = l_item_id
1176 and set_process_id + 0 = xset_id
1177 and process_flag in (31,41)
1178 and organization_id = cr.organization_id
1179 and rownum < 2; */
1180
1181 /*set value of process_flag to 42 or 32 depending on
1182 ** value of the variable: status.
1183 ** Essentially, we check to see if validation has not
1184 ** already failed in one of the previous packages.
1185 */
1186
1187 if temp_proc_flag <> 31 then
1188 update MTL_SYSTEM_ITEMS_INTERFACE
1189 set process_flag = DECODE(status,0,42,32),
1190 PRIMARY_UOM_CODE = cr.primary_uom_code,
1191 primary_unit_of_measure = cr.primary_unit_of_measure
1192 where inventory_item_id = l_item_id
1193 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
1194 and set_process_id = xset_id
1195 and process_flag = 41
1196 and organization_id = cr.organization_id;
1197 end if;
1198
1199 end loop;
1200
1201 return(0);
1202 exception
1203 when LOGGING_ERR then
1204 return(dumm_status);
1205 when VALIDATE_ERR then
1206 dumm_status := INVPUOPI.mtl_log_interface_err(
1207 l_org_id,
1208 user_id,
1209 login_id,
1210 prog_appid,
1211 prog_id,
1212 request_id,
1213 trans_id,
1214 err_text,
1215 'validation_error ' || stmt,
1216 'MTL_SYSTEM_ITEMS_INTERFACE',
1217 'BOM_OP_VALIDATION_ERR',
1218 err_text);
1219 return(status);
1220 when OTHERS then
1221 err_text := substr('INVPVALI.validate_item_header2' || SQLERRM, 1,240);
1222 return(SQLCODE);
1223 end validate_item_header2;
1224
1225 end INVPVDR2;