[Home] [Help]
PACKAGE BODY: APPS.INVPVDR4
Source
1 PACKAGE BODY INVPVDR4 AS
2 /* $Header: INVPVD4B.pls 120.22.12020000.5 2013/01/23 08:38:38 lmai ship $ */
3
4 FUNCTION validate_item_header4 (
5 org_id NUMBER,
6 all_org NUMBER := 2,
7 prog_appid NUMBER := -1,
8 prog_id NUMBER := -1,
9 request_id NUMBER := -1,
10 user_id NUMBER := -1,
11 login_id NUMBER := -1,
12 err_text IN OUT NOCOPY VARCHAR2,
13 xset_id IN NUMBER DEFAULT -999 ) RETURN INTEGER IS
14
15 loc_ctrl_code NUMBER;
16 cost_flag VARCHAR2(1);
17 inv_asset_flag VARCHAR2(1);
18 mrp_stock_code NUMBER;
19 base_item NUMBER;
20 lead_lot_size NUMBER;
21 out_op_flag VARCHAR2(1);
22 shelf_code NUMBER;
23 temp VARCHAR2(2);
24 temp_uom_code VARCHAR2(3);
25 temp_u_o_m VARCHAR2(25);
26 temp_uom_class VARCHAR2(10);
27 temp_enabled_flag VARCHAR2(1);
28 pur_dummy VARCHAR2(30);
29 l_col_name VARCHAR2(30);
30 l_msg_name VARCHAR2(30);
31 l_test NUMBER;
32
33 CURSOR cc IS
34 SELECT ROWID, intf.*
35 FROM mtl_system_items_interface intf
36 WHERE((intf.organization_id = org_id) OR (all_Org = 1) )
37 AND intf.set_process_id = xset_id
38 AND intf.process_flag in (31, 32, 33, 43);
39
40 CURSOR c_check_oks_template IS
41 SELECT 'Y'
42 FROM USER_TAB_COLUMNS
43 WHERE TABLE_NAME = 'OKS_COVERAGE_TEMPLTS_V'
44 AND COLUMN_NAME = 'ITEM_TYPE';
45
46 -- Added the cursor for bug # 3762750
47 CURSOR c_mfglookup_exists(cp_lookup_type VARCHAR2,
48 cp_lookup_code VARCHAR2) IS
49 SELECT 'x'
50 FROM MFG_LOOKUPS
51 WHERE LOOKUP_TYPE = cp_lookup_type
52 AND LOOKUP_CODE = cp_lookup_code
53 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
54 AND ENABLED_FLAG = 'Y';
55
56 -- Added the cursor for bug # 3762750
57 CURSOR c_fndlookup_exists(cp_lookup_type VARCHAR2,
58 cp_lookup_code VARCHAR2) IS
59 SELECT 'x'
60 FROM FND_LOOKUP_VALUES_VL
61 WHERE LOOKUP_TYPE = cp_lookup_type
62 AND LOOKUP_CODE = cp_lookup_code
63 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
64 AND ENABLED_FLAG = 'Y';
65
66 msicount NUMBER;
67 msiicount NUMBER;
68 resersal_flag NUMBER;
69 dup_item_id NUMBER;
70 l_item_id NUMBER;
71 l_org_id NUMBER;
72 cat_set_id NUMBER;
73 trans_id NUMBER;
74 ext_flag NUMBER := 0;
75 error_msg VARCHAR2(70);
76 status NUMBER;
77 dumm_status NUMBER;
78 master_org_id NUMBER;
79 l_oks_exits VARCHAR2(1) := 'N';
80 stmt NUMBER;
81 LOGGING_ERR EXCEPTION;
82 VALIDATE_ERR EXCEPTION;
83 temp_proc_flag NUMBER;
84 proc_enab_org VARCHAR2(1);
85 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
86
87 BEGIN
88
89 IF l_inv_debug_level IN(101, 102) THEN
90 INVPUTLI.info('INVPVDR4.validate_item_header4: begin');
91 END IF;
92 -- Validate the records
93
94 FOR cr IN cc LOOP
95 status := 0;
96 trans_id := cr.transaction_id;
97 l_org_id := cr.organization_id;
98 l_item_id := cr.inventory_item_id; -- Bug 4705184
99 temp_proc_flag := cr.process_flag; -- Bug 4705184
100
101 -- Validate second set of fields with lookup values @@
102 --INVPUTLI.info('INVPVDR4: Validating second set of lookups');
103 -- validate lookup
104 IF (cr.bom_item_type <> 1 AND
105 cr.bom_item_type <> 2 AND
106 cr.bom_item_type <> 3 AND
107 cr.bom_item_type <> 4 AND
108 cr.bom_item_type <> 5)
109 THEN
110 dumm_status := INVPUOPI.mtl_log_interface_err(
111 cr.organization_id,
112 user_id,
113 login_id,
114 prog_appid,
115 prog_id,
116 request_id,
117 cr.TRANSACTION_ID,
118 error_msg,
119 'BOM_ITEM_TYPE',
120 'MTL_SYSTEM_ITEMS_INTERFACE',
121 'INV_IOI_BOM_ITEM_TYPE',
122 err_text);
123 IF dumm_status < 0 THEN
124 raise LOGGING_ERR;
125 END IF;
126 status := 1;
127 END IF;
128
129 -- validate lookup
130 IF (cr.wip_supply_type <> 1 AND
131 cr.wip_supply_type <> 2 AND
132 cr.wip_supply_type <> 3 AND
133 cr.wip_supply_type <> 4 AND
134 cr.wip_supply_type <> 5 AND
135 cr.wip_supply_type <> 6)
136 THEN
137 dumm_status := INVPUOPI.mtl_log_interface_err(
138 cr.organization_id,
139 user_id,
140 login_id,
141 prog_appid,
142 prog_id,
143 request_id,
144 cr.TRANSACTION_ID,
145 error_msg,
146 'WIP_SUPPLY_TYPE',
147 'MTL_SYSTEM_ITEMS_INTERFACE',
148 'INV_IOI_WIP_SUPP_TYPE',
149 err_text);
150 IF dumm_status < 0 THEN
151 raise LOGGING_ERR;
152 END IF;
153 status := 1;
154 END IF;
155
156 -- validate lookup
157 IF (cr.allowed_units_lookup_code <> 1 and
158 cr.allowed_units_lookup_code <> 2 and
159 cr.allowed_units_lookup_code <> 3)
160 THEN
161 dumm_status := INVPUOPI.mtl_log_interface_err(
162 cr.organization_id,
163 user_id,
164 login_id,
165 prog_appid,
166 prog_id,
167 request_id,
168 cr.TRANSACTION_ID,
169 error_msg,
170 'ALLOWED_UNITS_LOOKUP_CODE',
171 'MTL_SYSTEM_ITEMS_INTERFACE',
172 'INV_IOI_ALWD_UN_LKUP_CODE',
173 err_text);
174 IF dumm_status < 0 THEN
175 raise LOGGING_ERR;
176 END IF;
177 status := 1;
178 END IF;
179
180 -- validate lookup
181 -- 3357700:Vendor manage included in lookup for inv_plan_code
182 IF (cr.inventory_planning_code <> 1 AND
183 cr.inventory_planning_code <> 2 AND
184 cr.inventory_planning_code <> 6 AND
185 cr.inventory_planning_code <> 7)
186 THEN
187 dumm_status := INVPUOPI.mtl_log_interface_err(
188 cr.organization_id,
189 user_id,
190 login_id,
191 prog_appid,
192 prog_id,
193 request_id,
194 cr.TRANSACTION_ID,
195 error_msg,
196 'INVENTORY_PLANNING_CODE',
197 'MTL_SYSTEM_ITEMS_INTERFACE',
198 'INV_IOI_INV_PLN_CODE',
199 err_text);
200 IF dumm_status < 0 THEN
201 raise LOGGING_ERR;
202 END IF;
203 status := 1;
204 END IF;
205
206 -- validate lookup
207 IF (cr.planning_make_buy_code <> 1 AND cr.planning_make_buy_code <> 2) THEN
208 dumm_status := INVPUOPI.mtl_log_interface_err(
209 cr.organization_id,
210 user_id,
211 login_id,
212 prog_appid,
213 prog_id,
214 request_id,
215 cr.TRANSACTION_ID,
216 error_msg,
217 'PLANNING_MAKE_BUY_CODE',
218 'MTL_SYSTEM_ITEMS_INTERFACE',
219 'INV_IOI_PLN_MAKE_BUY_CODE',
220 err_text);
221 IF dumm_status < 0 THEN
222 raise LOGGING_ERR;
223 END IF;
224 status := 1;
225 END IF;
226
227
228 -- validate lookup
229 IF (cr.rounding_control_type <> 1 AND cr.rounding_control_type <> 2) THEN
230 dumm_status := INVPUOPI.mtl_log_interface_err(
231 cr.organization_id,
232 user_id,
233 login_id,
234 prog_appid,
235 prog_id,
236 request_id,
237 cr.TRANSACTION_ID,
238 error_msg,
239 'ROUNDING_CONTROL_TYPE',
240 'MTL_SYSTEM_ITEMS_INTERFACE',
241 'INV_IOI_RND_CTRL_TYPE',
242 err_text);
243 IF dumm_status < 0 THEN
244 raise LOGGING_ERR;
245 END IF;
246 status := 1;
247 END IF;
248
249 -- validate lookup
250 IF (cr.mrp_safety_stock_code <> 1 AND cr.mrp_safety_stock_code <> 2) THEN
251 dumm_status := INVPUOPI.mtl_log_interface_err(
252 cr.organization_id,
253 user_id,
254 login_id,
255 prog_appid,
256 prog_id,
257 request_id,
258 cr.TRANSACTION_ID,
259 error_msg,
260 'MRP_SAFETY_STOCK_CODE',
261 'MTL_SYSTEM_ITEMS_INTERFACE',
262 'INV_IOI_MRP_SAF_STK_CODE',
263 err_text);
264 IF dumm_status < 0 THEN
265 raise LOGGING_ERR;
266 END IF;
267 status := 1;
268 END IF;
269
270 -- validate lookup
271 IF (cr.reservable_type <> 1 AND cr.reservable_type <> 2) THEN
272 dumm_status := INVPUOPI.mtl_log_interface_err(
273 cr.organization_id,
274 user_id,
275 login_id,
276 prog_appid,
277 prog_id,
278 request_id,
279 cr.TRANSACTION_ID,
280 error_msg,
281 'RESERVABLE_TYPE',
282 'MTL_SYSTEM_ITEMS_INTERFACE',
283 'INV_IOI_RESER_TYPE',
284 err_text);
285 IF dumm_status < 0 THEN
286 raise LOGGING_ERR;
287 END IF;
288 status := 1;
289 END IF;
290
291 -- validate lookup
292 IF (cr.response_time_period_code <> 'DAY' AND
293 cr.response_time_period_code <> 'HOU' AND
294 cr.response_time_period_code <> 'WEE')
295 THEN
296 dumm_status := INVPUOPI.mtl_log_interface_err(
297 cr.organization_id,
298 user_id,
299 login_id,
300 prog_appid,
301 prog_id,
302 request_id,
303 cr.TRANSACTION_ID,
304 error_msg,
305 'RESPONSE_TIME_PERIOD_CODE',
306 'MTL_SYSTEM_ITEMS_INTERFACE',
307 'INV_IOI_RESP_TIME_PER_CODE',
308 err_text);
309 IF dumm_status < 0 THEN
310 raise LOGGING_ERR;
311 END IF;
312 status := 1;
313 END IF;
314
315 -- validate lookup
316 IF (cr.new_revision_code <> 'NOTIFY' AND
317 cr.new_revision_code <> 'SEND_AUTOMATIC')
318 THEN
319 dumm_status := INVPUOPI.mtl_log_interface_err(
320 cr.organization_id,
321 user_id,
322 login_id,
323 prog_appid,
324 prog_id,
325 request_id,
326 cr.TRANSACTION_ID,
327 error_msg,
328 'NEW_REVISION_CODE',
329 'MTL_SYSTEM_ITEMS_INTERFACE',
330 'INV_IOI_NEW_REV_CODE',
331 err_text);
332 IF dumm_status < 0 THEN
333 raise LOGGING_ERR;
334 END IF;
335 status := 1;
336 END IF;
337
338 -- validate lookup /*NP 12OCT94 ASS, RES changed to ASSEMBLY, RESOURCE */
339 IF (cr.outside_operation_uom_type <> 'ASSEMBLY' AND
340 cr.outside_operation_uom_type <> 'RESOURCE') THEN
341 dumm_status := INVPUOPI.mtl_log_interface_err(
342 cr.organization_id,
343 user_id,
344 login_id,
345 prog_appid,
346 prog_id,
347 request_id,
348 cr.TRANSACTION_ID,
349 error_msg,
350 'OUTSIDE_OPERATION_UOM_TYPE',
351 'MTL_SYSTEM_ITEMS_INTERFACE',
352 'INV_IOI_OUTSIDE_OP_UOM_TYPE',
353 err_text);
354 IF dumm_status < 0 THEN
355 raise LOGGING_ERR;
356 END IF;
357 status := 1;
358 END IF;
359
360 -- validate lookup
361 IF (cr.auto_reduce_mps <> 1 AND
362 cr.auto_reduce_mps <> 2 AND
363 cr.auto_reduce_mps <> 3 AND
364 cr.auto_reduce_mps <> 4)
365 THEN
366 dumm_status := INVPUOPI.mtl_log_interface_err(
367 cr.organization_id,
368 user_id,
369 login_id,
370 prog_appid,
371 prog_id,
372 request_id,
373 cr.TRANSACTION_ID,
374 error_msg,
375 'AUTO_REDUCE_MPS',
376 'MTL_SYSTEM_ITEMS_INTERFACE',
377 'INV_IOI_AUTO_REDUCE_MPS',
378 err_text);
379 IF dumm_status < 0 THEN
380 raise LOGGING_ERR;
381 END IF;
382 status := 1;
383 END IF;
384
385
386 -- validate lookup mrp_planning_code
387 IF (cr.mrp_planning_code not in (3,4,6,7,8,9)) THEN
388 dumm_status := INVPUOPI.mtl_log_interface_err(
389 cr.organization_id,
390 user_id,
391 login_id,
392 prog_appid,
393 prog_id,
394 request_id,
395 cr.TRANSACTION_ID,
396 error_msg,
397 'MRP_PLANNING_CODE',
398 'MTL_SYSTEM_ITEMS_INTERFACE',
399 'INV_IOI_MRP_PLANNING_CODE',
400 err_text);
401 IF dumm_status < 0 THEN
402 raise LOGGING_ERR;
403 END IF;
404 status := 1;
405 END IF;
406
407 -- Validate purchasing_tax_code
408 IF ( (cr.taxable_flag <> 'Y') AND (cr.purchasing_tax_code IS NOT NULL) ) THEN
409 dumm_status := INVPUOPI.mtl_log_interface_err(
410 cr.organization_id,
411 user_id,
412 login_id,
413 prog_appid,
414 prog_id,
415 request_id,
416 cr.TRANSACTION_ID,
417 error_msg,
418 'PURCHASING_TAX_CODE',
419 'MTL_SYSTEM_ITEMS_INTERFACE',
420 'INV_IOI_TAXABLE_FLAG',
421 err_text);
422 IF dumm_status < 0 THEN
423 raise LOGGING_ERR;
424 END IF;
425 status := 1;
426 END IF;
427
428 /* Changed the validation for R12 - Anmurali */
429 IF (cr.purchasing_tax_code IS NOT NULL) THEN
430 BEGIN
431 /* Fix for bug 6804003 - Tax Codes are stored at O.U. level, so added
432 an inner subquery to fetch the operating_unit */
433 /*Bug 7437620 Modified the query to fetch the operating unit
434 Table hr_organization_information is used instead of org_organization_defintions*/
435
436 SELECT 'valid_tax_code' into pur_dummy
437 FROM dual
438 WHERE EXISTS( SELECT NULL
439 FROM zx_input_classifications_v
440 /* Bug 7588091 Added the nvl around the tax_type in the query*/
441 WHERE nvl(tax_type,'X') not in ('AWT','OFFSET')
442 AND enabled_flag = 'Y'
443 AND sysdate between start_date_active and nvl(end_date_active,sysdate)
444 AND lookup_code = cr.purchasing_tax_code
445 AND org_id IN (-99,(SELECT org_information3 FROM hr_organization_information
446 WHERE ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
447 AND ORGANIZATION_ID=cr.organization_id)))
448 AND ROWNUM = 1;
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 dumm_status := INVPUOPI.mtl_log_interface_err(
452 cr.organization_id,
453 user_id,
454 login_id,
455 prog_appid,
456 prog_id,
457 request_id,
458 cr.TRANSACTION_ID,
459 error_msg,
460 'PURCHASING_TAX_CODE',
461 'MTL_SYSTEM_ITEMS_INTERFACE',
462 'INV_IOI_PUR_TAX_CODE',
463 err_text);
464 IF dumm_status < 0 THEN
465 raise LOGGING_ERR;
466 END IF;
467 status := 1;
468 END;
469 END IF; -- purchasing_tax_code is not null
470
471 -- bug 2843301 Validate Market and List Price.
472
473 IF (nvl(cr.MARKET_PRICE, 0) < 0 ) THEN
474 dumm_status := INVPUOPI.mtl_log_interface_err(
475 cr.organization_id,
476 user_id,
477 login_id,
478 prog_appid,
479 prog_id,
480 request_id,
481 cr.TRANSACTION_ID,
482 error_msg,
483 'MARKET_PRICE',
484 'MTL_SYSTEM_ITEMS_INTERFACE',
485 'INV_GREATER_EQUAL_ZERO',
486 err_text);
487 IF dumm_status < 0 THEN
488 raise LOGGING_ERR;
489 END IF;
490 status := 1;
491 END IF;
492
493 IF (NVL(cr.LIST_PRICE_PER_UNIT, 0) < 0 ) THEN
494 dumm_status := INVPUOPI.mtl_log_interface_err(
495 cr.organization_id,
496 user_id,
497 login_id,
498 prog_appid,
499 prog_id,
500 request_id,
501 cr.TRANSACTION_ID,
502 error_msg,
503 'LIST_PRICE_PER_UNIT',
504 'MTL_SYSTEM_ITEMS_INTERFACE',
505 'INV_GREATER_EQUAL_ZERO',
506 err_text);
507 IF dumm_status < 0 THEN
508 raise LOGGING_ERR;
509 END IF;
510 status := 1;
511 END IF;
512
513 -- validate lookup
514 IF (cr.return_inspection_requirement <> 1 AND cr.return_inspection_requirement <> 2) THEN
515 dumm_status := INVPUOPI.mtl_log_interface_err(
516 cr.organization_id,
517 user_id,
518 login_id,
519 prog_appid,
520 prog_id,
521 request_id,
522 cr.TRANSACTION_ID,
523 error_msg,
524 'RETURN_INSPECTION_REQUIREMENT',
525 'MTL_SYSTEM_ITEMS_INTERFACE',
526 'INV_IOI_RETURN_INSP_REQ',
527 err_text);
528 IF dumm_status < 0 THEN
529 raise LOGGING_ERR;
530 END IF;
531 status := 1;
532 END IF;
533
534 -- validate lookup
535 IF (cr.ato_forecast_control <> 1 AND
536 cr.ato_forecast_control <> 2 AND
537 cr.ato_forecast_control <> 3)
538 THEN
539 dumm_status := INVPUOPI.mtl_log_interface_err(
540 cr.organization_id,
541 user_id,
542 login_id,
543 prog_appid,
544 prog_id,
545 request_id,
546 cr.TRANSACTION_ID,
547 error_msg,
548 'ATO_FORECAST_CONTROL',
549 'MTL_SYSTEM_ITEMS_INTERFACE',
550 'INV_IOI_ATO_FORECAST_CTRL',
551 err_text);
552 IF dumm_status < 0 THEN
553 raise LOGGING_ERR;
554 END IF;
555 status := 1;
556 END IF;
557
558
559 -- Bug 3969864 - Anmurali
560 -- validate CUMULATIVE_TOTAL_LEAD_TIME
561 -- length should not exceed 42 digits
562 IF (LENGTH(TO_CHAR(cr.cumulative_total_lead_time)) > 42 ) THEN
563 dumm_status := INVPUOPI.mtl_log_interface_err(
564 cr.organization_id,
565 user_id,
566 login_id,
567 prog_appid,
568 prog_id,
569 request_id,
570 cr.TRANSACTION_ID,
571 error_msg,
572 'CUMULATIVE_TOTAL_LEAD_TIME',
573 'MTL_SYSTEM_ITEMS_INTERFACE',
574 'INV_IOI_NUMBER_TOO_LONG',
575 err_text);
576 IF dumm_status < 0 THEN
577 raise LOGGING_ERR;
578 END IF;
579 status := 1;
580 END IF;
581
582 -- Bug 5724477
583 -- validate CUM_MANUFACTURING_LEAD_TIME
584 -- length should not exceed 42 digits
585 IF (length(to_char(cr.CUM_MANUFACTURING_LEAD_TIME)) > 42 ) THEN
586 dumm_status := INVPUOPI.mtl_log_interface_err(
587 cr.organization_id,
588 user_id,
589 login_id,
590 prog_appid,
591 prog_id,
592 request_id,
593 cr.TRANSACTION_ID,
594 error_msg,
595 'CUM_MANUFACTURING_LEAD_TIME',
596 'MTL_SYSTEM_ITEMS_INTERFACE',
597 'INV_IOI_CUM_MANU_TOO_LONG',
598 err_text);
599 IF dumm_status < 0 THEN
600 raise LOGGING_ERR;
601 END IF;
602 status := 1;
603 END IF;
604
605
606 -- Validate Coverage Template value Added as part of 11.5.9 ENH.
607 IF (cr.COVERAGE_SCHEDULE_ID IS NOT NULL ) THEN
608 IF (cr.contract_item_type_code IS NULL ) THEN
609 dumm_status := INVPUOPI.mtl_log_interface_err(
610 cr.organization_id,
611 user_id,
612 login_id,
613 prog_appid,
614 prog_id,
615 request_id,
616 cr.TRANSACTION_ID,
617 error_msg,
618 'COVERAGE_SCHEDULE_ID',
619 'MTL_SYSTEM_ITEMS_INTERFACE',
620 'INV_COVERAGE_TEMP_CONTRACT',
621 err_text);
622 IF dumm_status < 0 THEN
623 raise LOGGING_ERR;
624 END IF;
625 status := 1;
626 ELSE
627 BEGIN
628 l_col_name := 'COVERAGE_SCHEDULE_ID';
629 l_msg_name := 'INV_IOI_ERR_INVALID_ATTR_NV'; --update by bug 11894684, replace INV_IOI_ERR
630 fnd_message.SET_NAME ('INV', 'INV_INVALID_ATTR_NAME_VALUE');
631 fnd_message.SET_TOKEN ('ATTR', 'Template');
632 error_msg := fnd_message.get;
633 OPEN c_check_oks_template;
634 FETCH c_check_oks_template INTO l_oks_exits;
635 CLOSE c_check_oks_template;
636 IF l_oks_exits = 'Y' THEN
637 EXECUTE IMMEDIATE
638 'BEGIN '||
639 'SELECT ''x'' INTO :temp '||
640 'FROM OKS_COVERAGE_TEMPLTS_V '||
641 'WHERE ITEM_TYPE = :cr.CONTRACT_ITEM_TYPE_CODE '||
642 'AND ID = :cr.COVERAGE_SCHEDULE_ID '||
643 'AND SYSDATE BETWEEN NVL(start_date, SYSDATE-1) AND NVL(end_date, SYSDATE+1); '||
644 'END;'
645 USING OUT temp, IN cr.CONTRACT_ITEM_TYPE_CODE, IN cr.COVERAGE_SCHEDULE_ID;
646 ELSE
647 SELECT 'x' INTO temp
648 FROM OKS_COVERAGE_TEMPLTS_V
649 WHERE ID = cr.COVERAGE_SCHEDULE_ID
650 AND SYSDATE BETWEEN NVL(start_date, SYSDATE-1) AND NVL(end_date, SYSDATE+1);
651 END IF;
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 dumm_status := INVPUOPI.mtl_log_interface_err
655 ( cr.organization_id,
656 user_id, login_id, prog_appid, prog_id, request_id,
657 cr.TRANSACTION_ID, error_msg,
658 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
659 l_msg_name,
660 err_text );
661 IF dumm_status < 0 THEN
662 raise LOGGING_ERR;
663 END IF;
664 status := 1;
665 END;
666 END IF;
667 -- Bug: 2811878 This validation will be in 11.5.10 in IOI
668 ELSIF(cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY')) THEN
669 dumm_status := INVPUOPI.mtl_log_interface_err(
670 cr.organization_id,
671 user_id,
672 login_id,
673 prog_appid,
674 prog_id,
675 request_id,
676 cr.TRANSACTION_ID,
677 error_msg,
678 'COVERAGE_SCHEDULE_ID',
679 'MTL_SYSTEM_ITEMS_INTERFACE',
680 'INV_COVERAGE_TEMPL_MAND',
681 err_text);
682 IF dumm_status < 0 THEN
683 raise LOGGING_ERR;
684 END IF;
685 status := 1;
686 END IF; -- Coverage Template end
687
688 -- Validate Installed Base tracking Flag.
689 IF (cr.COMMS_NL_TRACKABLE_FLAG = 'Y' AND
690 --Bug: 2696647 Subscription Items can be Installed Base trackable
691 cr.contract_item_type_code IN ('SERVICE','WARRANTY','USAGE'))
692 THEN
693 dumm_status := INVPUOPI.mtl_log_interface_err(
694 cr.organization_id,
695 user_id,
696 login_id,
697 prog_appid,
698 prog_id,
699 request_id,
700 cr.TRANSACTION_ID,
701 error_msg,
702 'COMMS_NL_TRACKABLE_FLAG',
703 'MTL_SYSTEM_ITEMS_INTERFACE',
704 'INV_IB_TRACKING_CONTRACT',
705 err_text);
706 IF dumm_status < 0 THEN
707 raise LOGGING_ERR;
708 END IF;
709 status := 1;
710 END IF;
711
712 --Start 3416621 Subscription should be IB trackble
713 IF (cr.contract_item_type_code = 'SUBSCRIPTION' AND
714 NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y')
715 THEN
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 'COMMS_NL_TRACKABLE_FLAG',
726 'MTL_SYSTEM_ITEMS_INTERFACE',
727 'INV_CTTYPE_INSBASE_VALID',
728 err_text);
729 IF dumm_status < 0 THEN
730 raise LOGGING_ERR;
731 END IF;
732 status := 1;
733 END IF;
734
735 --End 3416621 Subscription should be IB trackble
736 --Added for 11.5.10
737 IF (NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') = 'N' AND
738 NVL(cr.asset_creation_code,'0') = '1')
739 THEN
740 dumm_status := INVPUOPI.mtl_log_interface_err(
741 cr.organization_id,
742 user_id,
743 login_id,
744 prog_appid,
745 prog_id,
746 request_id,
747 cr.TRANSACTION_ID,
748 error_msg,
749 'ASSET_CREATION_CODE',
750 'MTL_SYSTEM_ITEMS_INTERFACE',
751 'INV_INST_BASE_ASSET_CREATE_DEP',
752 err_text);
753 IF dumm_status < 0 THEN
754 raise LOGGING_ERR;
755 END IF;
756 status := 1;
757 END IF;
758
759 --Bug: 2710463 NVL added
760 IF (NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y' AND
761 cr.CONTRACT_ITEM_TYPE_CODE IS NULL AND
762 cr.SERVICEABLE_PRODUCT_FLAG = 'Y')
763 THEN
764 dumm_status := INVPUOPI.mtl_log_interface_err(
765 cr.organization_id,
766 user_id,
767 login_id,
768 prog_appid,
769 prog_id,
770 request_id,
771 cr.TRANSACTION_ID,
772 error_msg,
773 'COMMS_NL_TRACKABLE_FLAG',
774 'MTL_SYSTEM_ITEMS_INTERFACE',
775 'INV_IB_TRACKING_SERVICEABLE',
776 err_text);
777 IF dumm_status < 0 THEN
778 raise LOGGING_ERR;
779 END IF;
780 status := 1;
781 END IF;
782 /*
783 --Validate LOT_SUBSTITUTION_ENABLED
784 --Added validation for new attributes as part of 11.5.9
785 IF ( cr.LOT_SUBSTITUTION_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
786 dumm_status := INVPUOPI.mtl_log_interface_err(
787 cr.organization_id,
788 user_id,
789 login_id,
790 prog_appid,
791 prog_id,
792 request_id,
793 cr.TRANSACTION_ID,
794 error_msg,
795 'LOT_SUBSTITUTION_ENABLED',
796 'MTL_SYSTEM_ITEMS_INTERFACE',
797 'INV_INVALID_LOT_SUB_ENABLED',
798 err_text);
799 IF dumm_status < 0 THEN
800 raise LOGGING_ERR;
801 END IF;
802 status := 1;
803 END IF;
804 */
805 -- Lot Subsitution flag must be either NULL or 'Y'
806 -- FP for Bug #10131935
807 -- Added additional check for when LOT_SUBSTITUTION_ENABLED is 'N'
808 IF ( cr.LOT_SUBSTITUTION_ENABLED IS NOT NULL AND cr.LOT_SUBSTITUTION_ENABLED NOT IN ( 'Y','N') )THEN
809 l_col_name := 'LOT_SUBSTITUTION_ENABLED';
810 l_msg_name := 'INV_NOT_VALID_FLAG';
811
812 dumm_status := INVPUOPI.mtl_log_interface_err
813 ( cr.organization_id,
814 user_id, login_id, prog_appid, prog_id, request_id,
815 cr.TRANSACTION_ID, error_msg,
816 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
817 l_msg_name,
818 err_text);
819 IF dumm_status < 0 THEN
820 raise LOGGING_ERR;
821 END IF;
822 status := 1;
823 END IF; -- LOT_SUBSTITUTION_ENABLED is not null
824
825 /*Bug: 5140047 Commenting out this validation as this is duplicate.
826 IF ( cr.LOT_SUBSTITUTION_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
827 dumm_status := INVPUOPI.mtl_log_interface_err(
828 cr.organization_id,
829 user_id,
830 login_id,
831 prog_appid,
832 prog_id,
833 request_id,
834 cr.TRANSACTION_ID,
835 error_msg,
836 'LOT_SUBSTITUTION_ENABLED',
837 'MTL_SYSTEM_ITEMS_INTERFACE',
838 'INV_INVALID_LOT_SUB_ENABLED',
839 err_text);
840 IF dumm_status < 0 THEN
841 raise LOGGING_ERR;
842 END IF;
843 status := 1;
844 END IF;
845 */
846 -- Lot Subsitution flag must be either NULL or 'Y'
847 --BEGIN : Fix for Bug# 2760857 (PPEDDAMA)
848 --Begin : ***Validate LOT_TRANSLATE_ENABLED***
849 IF ( cr.LOT_TRANSLATE_ENABLED IS NOT NULL AND cr.LOT_TRANSLATE_ENABLED NOT IN ( 'Y','N') )THEN
850 dumm_status := INVPUOPI.mtl_log_interface_err(
851 cr.organization_id,
852 user_id,
853 login_id,
854 prog_appid,
855 prog_id,
856 request_id,
857 cr.TRANSACTION_ID,
858 error_msg,
859 'LOT_TRANSLATE_ENABLED',
860 'MTL_SYSTEM_ITEMS_INTERFACE',
861 'INV_IOI_FLAG_Y_N_NULL',
862 err_text);
863 IF dumm_status < 0 THEN
864 raise LOGGING_ERR;
865 END IF;
866 status := 1;
867 END IF; -- LOT_TRANSLATE_ENABLED is not null AND <> 'Y'
868
869 IF ( cr.LOT_TRANSLATE_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) 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 'LOT_TRANSLATE_ENABLED',
880 'MTL_SYSTEM_ITEMS_INTERFACE',
881 'INV_INVALID_LOT_TRANS_ENABLED',
882 err_text);
883 IF dumm_status < 0 THEN
884 raise LOGGING_ERR;
885 END IF;
886 status := 1;
887 END IF;-- LOT_TRANSLATE_ENABLED = 'Y' AND <<NO LOT CONTROL>>
888 --End : ***Validate LOT_TRANSLATE_ENABLED***
889
890 --Begin : ***Validate LOT_SPLIT_ENABLED***
891 IF ( cr.LOT_SPLIT_ENABLED IS NOT NULL AND cr.LOT_SPLIT_ENABLED NOT IN ( 'Y','N') )THEN
892 dumm_status := INVPUOPI.mtl_log_interface_err(
893 cr.organization_id,
894 user_id,
895 login_id,
896 prog_appid,
897 prog_id,
898 request_id,
899 cr.TRANSACTION_ID,
900 error_msg,
901 'LOT_SPLIT_ENABLED',
902 'MTL_SYSTEM_ITEMS_INTERFACE',
903 'INV_IOI_FLAG_Y_N_NULL',
904 err_text);
905 IF dumm_status < 0 THEN
906 raise LOGGING_ERR;
907 END IF;
908 status := 1;
909 END IF;-- LOT_SPLIT_ENABLED is not null AND <> 'Y'
910
911 IF ( cr.LOT_SPLIT_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
912 dumm_status := INVPUOPI.mtl_log_interface_err(
913 cr.organization_id,
914 user_id,
915 login_id,
916 prog_appid,
917 prog_id,
918 request_id,
919 cr.TRANSACTION_ID,
920 error_msg,
921 'LOT_SPLIT_ENABLED',
922 'MTL_SYSTEM_ITEMS_INTERFACE',
923 'INV_INVALID_LOT_SPLIT_ENABLED',
924 err_text);
925 IF dumm_status < 0 THEN
926 raise LOGGING_ERR;
927 END IF;
928 status := 1;
929 END IF;-- LOT_SPLIT_ENABLED = 'Y' AND <<NO LOT CONTROL>>
930 --End : ***Validate LOT_SPLIT_ENABLED***
931
932 --Begin : ***Validate LOT_MERGE_ENABLED***
933 IF ( cr.LOT_MERGE_ENABLED IS NOT NULL AND cr.LOT_MERGE_ENABLED NOT IN ( 'Y','N') )THEN
934 dumm_status := INVPUOPI.mtl_log_interface_err(
935 cr.organization_id,
936 user_id,
937 login_id,
938 prog_appid,
939 prog_id,
940 request_id,
941 cr.TRANSACTION_ID,
942 error_msg,
943 'LOT_MERGE_ENABLED',
944 'MTL_SYSTEM_ITEMS_INTERFACE',
945 'INV_IOI_FLAG_Y_N_NULL',
946 err_text);
947 IF dumm_status < 0 THEN
948 raise LOGGING_ERR;
949 END IF;
950 status := 1;
951 END IF;-- LOT_MERGE_ENABLED is not null AND <> 'Y'
952
953 IF ( cr.LOT_MERGE_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
954 dumm_status := INVPUOPI.mtl_log_interface_err(
955 cr.organization_id,
956 user_id,
957 login_id,
958 prog_appid,
959 prog_id,
960 request_id,
961 cr.TRANSACTION_ID,
962 error_msg,
963 'LOT_MERGE_ENABLED',
964 'MTL_SYSTEM_ITEMS_INTERFACE',
965 'INV_INVALID_LOT_MERGE_ENABLED',
966 err_text);
967 IF dumm_status < 0 THEN
968 raise LOGGING_ERR;
969 END IF;
970 status := 1;
971 END IF;-- LOT_MERGE_ENABLED = 'Y' AND <<NO LOT CONTROL>>
972 --End : ***Validate LOT_MERGE_ENABLED***
973 --END : Fix for Bug# 2760857 (PPEDDAMA)
974
975 --Bug No: 3285381 Modified IF st.
976 IF ( NVL(cr.MINIMUM_LICENSE_QUANTITY,1) < 1 OR
977 NVL(cr.MINIMUM_LICENSE_QUANTITY,1) <> CEIL(NVL(cr.MINIMUM_LICENSE_QUANTITY,1)) )
978 THEN
979 dumm_status := INVPUOPI.mtl_log_interface_err(
980 cr.organization_id,
981 user_id,
982 login_id,
983 prog_appid,
984 prog_id,
985 request_id,
986 cr.TRANSACTION_ID,
987 error_msg,
988 'MINIMUM_LICENSE_QUANTITY',
989 'MTL_SYSTEM_ITEMS_INTERFACE',
990 'INV_INVALID_MIN_LICENSE',
991 err_text);
992 IF dumm_status < 0 THEN
993 raise LOGGING_ERR;
994 END IF;
995 status := 1;
996 END IF; -- MINIMUM_LICENSE_QUANTITY < 1
997
998 IF ( NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y' AND cr.IB_ITEM_INSTANCE_CLASS IS NOT NULL) THEN
999 dumm_status := INVPUOPI.mtl_log_interface_err(
1000 cr.organization_id,
1001 user_id,
1002 login_id,
1003 prog_appid,
1004 prog_id,
1005 request_id,
1006 cr.TRANSACTION_ID,
1007 error_msg,
1008 'IB_ITEM_INSTANCE_CLASS',
1009 'MTL_SYSTEM_ITEMS_INTERFACE',
1010 'INV_IB_INSTANCE_CLASS_IB_TRACK',
1011 err_text);
1012 IF dumm_status < 0 THEN
1013 raise LOGGING_ERR;
1014 END IF;
1015 status := 1;
1016 END IF;
1017
1018 IF ( cr.IB_ITEM_INSTANCE_CLASS IS NOT NULL ) THEN
1019 l_col_name := 'IB_ITEM_INSTANCE_CLASS';
1020 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1021 -- 3762750: Using cursor call to avoid multiple parsing
1022 OPEN c_fndlookup_exists('CSI_ITEM_CLASS',cr.IB_ITEM_INSTANCE_CLASS);
1023 FETCH c_fndlookup_exists INTO temp;
1024 CLOSE c_fndlookup_exists;
1025
1026 IF temp IS NULL THEN
1027 dumm_status := INVPUOPI.mtl_log_interface_err
1028 ( cr.organization_id,
1029 user_id, login_id, prog_appid, prog_id, request_id,
1030 cr.TRANSACTION_ID, error_msg,
1031 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1032 l_msg_name,
1033 err_text );
1034 IF dumm_status < 0 THEN
1035 raise LOGGING_ERR;
1036 END IF;
1037 status := 1;
1038 END IF;
1039 END IF;-- IB_ITEM_INSTANCE_CLASS IS NOTNULL
1040
1041 -- This Can be NOT NULL only for orderable and configurable PTO Models.
1042 IF ( cr.CONFIG_MODEL_TYPE IS NOT NULL AND
1043 NOT ( cr.BOM_ITEM_TYPE = 1 AND
1044 cr.CUSTOMER_ORDER_ENABLED_FLAG = 'Y' ) )
1045 THEN
1046 dumm_status := INVPUOPI.mtl_log_interface_err(
1047 cr.organization_id,
1048 user_id,
1049 login_id,
1050 prog_appid,
1051 prog_id,
1052 request_id,
1053 cr.TRANSACTION_ID,
1054 error_msg,
1055 'CONFIG_MODEL_TYPE',
1056 'MTL_SYSTEM_ITEMS_INTERFACE',
1057 'INV_CONFIG_MODEL_TYPE_MOD',
1058 err_text);
1059 IF dumm_status < 0 THEN
1060 raise LOGGING_ERR;
1061 END IF;
1062 status := 1;
1063 END IF; -- CONFIG_MODEL_TYPE IS NOTNULL
1064
1065 -- CONFIG_MODEL_TYPE "Network Container Model" (N) cannot be
1066 -- Installed Base trackable item.
1067 IF ( NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') = 'Y' AND cr.CONFIG_MODEL_TYPE = 'N' ) THEN
1068 dumm_status := INVPUOPI.mtl_log_interface_err(
1069 cr.organization_id,
1070 user_id,
1071 login_id,
1072 prog_appid,
1073 prog_id,
1074 request_id,
1075 cr.TRANSACTION_ID,
1076 error_msg,
1077 'CONFIG_MODEL_TYPE',
1078 'MTL_SYSTEM_ITEMS_INTERFACE',
1079 'INV_CONFIG_MODEL_TYPE_IB_TRACK',
1080 err_text);
1081 IF dumm_status < 0 THEN
1082 raise LOGGING_ERR;
1083 END IF;
1084 status := 1;
1085 END IF; -- CONFIG_MODEL_TYPE IS NOTNULL
1086
1087 IF ( cr.CONFIG_MODEL_TYPE IS NOT NULL ) THEN
1088 l_col_name := 'CONFIG_MODEL_TYPE';
1089 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1090 -- 3762750: Using cursor call to avoid multiple parsing
1091 OPEN c_fndlookup_exists('CZ_CONFIG_MODEL_TYPE',cr.CONFIG_MODEL_TYPE);
1092 FETCH c_fndlookup_exists INTO temp;
1093 CLOSE c_fndlookup_exists;
1094
1095 IF temp IS NULL THEN
1096 dumm_status := INVPUOPI.mtl_log_interface_err
1097 ( cr.organization_id,
1098 user_id, login_id, prog_appid, prog_id, request_id,
1099 cr.TRANSACTION_ID, error_msg,
1100 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1101 l_msg_name,
1102 err_text );
1103 IF dumm_status < 0 THEN
1104 raise LOGGING_ERR;
1105 END IF;
1106 status := 1;
1107 END IF;
1108 END IF;-- CONFIG_MODEL_TYPE IS NOTNULL
1109
1110 -- Ended FOR 11.5.9
1111 -- Validate WEB_STATUS
1112 IF ( cr.WEB_STATUS is not null ) THEN
1113 -- 3762750: Using cursor call to avoid multiple parsing
1114 OPEN c_fndlookup_exists('IBE_ITEM_STATUS',cr.WEB_STATUS);
1115 FETCH c_fndlookup_exists INTO temp;
1116 CLOSE c_fndlookup_exists;
1117 IF temp IS NULL THEN
1118 dumm_status := INVPUOPI.mtl_log_interface_err(
1119 cr.organization_id,
1120 user_id,
1121 login_id,
1122 prog_appid,
1123 prog_id,
1124 request_id,
1125 cr.TRANSACTION_ID,
1126 error_msg,
1127 'WEB_STATUS',
1128 'MTL_SYSTEM_ITEMS_INTERFACE',
1129 'INV_IOI_WEB_STATUS',
1130 err_text);
1131 IF dumm_status < 0 THEN
1132 raise LOGGING_ERR;
1133 END IF;
1134 status := 1;
1135 END IF;
1136 END IF; -- WEB_STATUS is not null
1137
1138 --Validate INDIVISIBLE_FLAG
1139 -- Do not raise error
1140 -- Validate EAM Asset Item attributes
1141 IF l_inv_debug_level IN(101, 102) THEN
1142 INVPUTLI.info('INVPVDR4.validate_item_header4: validate EAM attributes (EAM_ITEM_TYPE = ' || cr.EAM_ITEM_TYPE || ')');
1143 END IF;
1144
1145 IF ( cr.EAM_ITEM_TYPE IS NOT NULL ) THEN
1146 BEGIN
1147 -- Organization must be EAM enabled for Asset Items to be imported
1148 /* FP Bug 8214318 with base Bug 7713558. Commenting 'Organization EAM enabled' validation,
1149 as EAM functionality allows EAM Items to be assigned to non-EAM enabled Organizations */
1150 /*
1151 l_col_name := 'EAM_ITEM_TYPE';
1152 l_msg_name := 'INV_EAM_ORG_NOT_ENABLED';
1153
1154 stmt := 811;
1155 SELECT 'x' INTO temp
1156 FROM MTL_PARAMETERS
1157 WHERE ORGANIZATION_ID = cr.organization_id
1158 AND (NVL(EAM_ENABLED_FLAG, 'N') = 'Y' or cr.organization_id = master_organization_id);*/
1159 --Bug:2672219 and NVL(EAM_ENABLED_FLAG, 'N') = 'Y';
1160 -- Check Asset Item type value based on mfg_lookups
1161 l_col_name := 'EAM_ITEM_TYPE';
1162 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1163 --l_msg_token := l_col_name;
1164
1165 stmt := 812;
1166
1167 -- 3762750: Using cursor call to avoid multiple parsing
1168 OPEN c_mfglookup_exists('MTL_EAM_ITEM_TYPE',cr.EAM_ITEM_TYPE);
1169 FETCH c_mfglookup_exists INTO temp;
1170 CLOSE c_mfglookup_exists;
1171 IF temp IS NULL THEN
1172 RAISE no_data_found;
1173 END IF;
1174 EXCEPTION
1175 WHEN no_data_found THEN
1176 dumm_status := INVPUOPI.mtl_log_interface_err
1177 ( cr.organization_id,
1178 user_id, login_id, prog_appid, prog_id, request_id,
1179 cr.TRANSACTION_ID, error_msg,
1180 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1181 l_msg_name, -- l_msg_token
1182 err_text );
1183 IF dumm_status < 0 THEN
1184 raise LOGGING_ERR;
1185 END IF;
1186 status := 1;
1187 END;
1188 stmt := 821;
1189 -- All Asset Items must be Inventory items
1190 IF ( NOT ( cr.INVENTORY_ITEM_FLAG = 'Y' ) ) THEN
1191 l_col_name := 'INVENTORY_ITEM_FLAG';
1192 l_msg_name := 'INV_EAM_ITEM_TYPE_PF_INV';
1193
1194 dumm_status := INVPUOPI.mtl_log_interface_err
1195 ( cr.organization_id,
1196 user_id, login_id, prog_appid, prog_id, request_id,
1197 cr.TRANSACTION_ID, error_msg,
1198 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1199 l_msg_name,
1200 err_text );
1201 IF dumm_status < 0 THEN
1202 raise LOGGING_ERR;
1203 END IF;
1204 status := 1;
1205 END IF;
1206
1207 stmt := 822;
1208 --R12 enhacement : Added validation Asset Group Item and Rebuidable item which is serial controlled must be IB Trackabale
1209
1210 IF ( (cr.EAM_ITEM_TYPE = 1 OR
1211 (cr.EAM_ITEM_TYPE = 3 AND cr.SERIAL_NUMBER_CONTROL_CODE <> 1 ) )
1212 AND NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y')
1213 THEN
1214 l_col_name := 'COMMS_NL_TRACKABLE_FLAG';
1215 l_msg_name := 'INV_EAM_IB_TRACKABLE';
1216
1217 dumm_status := INVPUOPI.mtl_log_interface_err
1218 (cr.organization_id,
1219 user_id,
1220 login_id,
1221 prog_appid,
1222 prog_id,
1223 request_id,
1224 cr.TRANSACTION_ID,
1225 error_msg,
1226 l_col_name,
1227 'MTL_SYSTEM_ITEMS_INTERFACE',
1228 l_msg_name,
1229 err_text );
1230 IF dumm_status < 0 THEN
1231 raise LOGGING_ERR;
1232 END IF;
1233 status := 1;
1234 END IF;
1235
1236 -- Asset Group has unit effective BOM
1237 IF ( cr.EAM_ITEM_TYPE = 1 AND NOT ( cr.EFFECTIVITY_CONTROL = 2 ) ) THEN
1238 l_col_name := 'EFFECTIVITY_CONTROL';
1239 l_msg_name := 'INV_EAM_ASSET_UNIT_CONTROL';
1240
1241 dumm_status := INVPUOPI.mtl_log_interface_err
1242 ( cr.organization_id,
1243 user_id, login_id, prog_appid, prog_id, request_id,
1244 cr.TRANSACTION_ID, error_msg,
1245 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1246 l_msg_name,
1247 err_text );
1248 IF dumm_status < 0 THEN
1249 raise LOGGING_ERR;
1250 END IF;
1251 status := 1;
1252 END IF;
1253
1254 stmt := 824;
1255
1256 -- Asset Group must be serial controlled of type Pre-Defined
1257 -- Change the second clause to include 'At Reciept' Items for R12
1258 IF ( cr.EAM_ITEM_TYPE = 1 AND cr.SERIAL_NUMBER_CONTROL_CODE NOT IN (2,5)) THEN
1259 l_col_name := 'SERIAL_NUMBER_CONTROL_CODE';
1260 l_msg_name := 'INV_EAM_ASSET_GRP_NO_SERIAL';
1261
1262 dumm_status := INVPUOPI.mtl_log_interface_err
1263 ( cr.organization_id,
1264 user_id, login_id, prog_appid, prog_id, request_id,
1265 cr.TRANSACTION_ID, error_msg,
1266 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1267 l_msg_name,
1268 err_text );
1269 IF dumm_status < 0 THEN
1270 raise LOGGING_ERR;
1271 END IF;
1272 status := 1;
1273 END IF;
1274
1275 stmt := 825;
1276
1277 -- Asset Activity must not be serial controlled
1278
1279 IF ( cr.EAM_ITEM_TYPE = 2
1280 AND NOT ( cr.SERIAL_NUMBER_CONTROL_CODE = 1 ) )
1281 THEN
1282 l_col_name := 'SERIAL_NUMBER_CONTROL_CODE';
1283 l_msg_name := 'INV_EAM_ACTIVITY_NEVER_SERIAL';
1284
1285 dumm_status := INVPUOPI.mtl_log_interface_err
1286 ( cr.organization_id,
1287 user_id, login_id, prog_appid, prog_id, request_id,
1288 cr.TRANSACTION_ID, error_msg,
1289 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1290 l_msg_name,
1291 err_text );
1292 IF dumm_status < 0 THEN
1293 raise LOGGING_ERR;
1294 END IF;
1295 status := 1;
1296 END IF;
1297
1298 END IF; -- EAM_ITEM_TYPE IS NOT NULL
1299
1300 --R12 enhacement : Tracking UOM is PS, Organization is process_enabled Bom Allowed must be No
1301 --and BOM item type cannot be MODEL or OPTION CLASS
1302 --Reverting the above fix for OPM - No chk for Process Enabled org
1303 --4756500 Using l_org_id instead of org_id
1304 --Moving the validation out of the EAM_ITEM_TYPE IF BLOCK - UT Bug
1305
1306 IF l_inv_debug_level IN(101, 102) THEN
1307 INVPUTLI.info('INVPVDR4.validate_item_header4: Validating Tracking and BOM attributes');
1308 END IF;
1309
1310 /*Comment the code to fix bug7477872
1311 IF ( cr.TRACKING_QUANTITY_IND = 'PS' AND
1312 (cr.BOM_ITEM_TYPE in (1,2) OR cr.BOM_ENABLED_FLAG='Y' ) )
1313 THEN
1314 l_col_name := 'TRACKING_QUANTITY_IND';
1315 l_msg_name := 'INV_TRACKING_OPM_BOM_ATTR';
1316 dumm_status := INVPUOPI.mtl_log_interface_err
1317 ( cr.organization_id,
1318 user_id,
1319 login_id,
1320 prog_appid,
1321 prog_id,
1322 request_id,
1323 cr.TRANSACTION_ID,
1324 error_msg,
1325 l_col_name,
1326 'MTL_SYSTEM_ITEMS_INTERFACE',
1327 l_msg_name,
1328 err_text );
1329 IF dumm_status < 0 THEN
1330 raise LOGGING_ERR;
1331 END IF;
1332 status := 1;
1333 END IF;
1334 */
1335 -- For EAM Asset Activity item, validate Activity Type, Activity Cause,
1336 -- and Shutdown Type attribute values based on mfg_lookups.
1337 -- Convert lookup code to char since columns are varchar2, and
1338 -- mfg_lookups code is number.
1339
1340 IF ( cr.EAM_ITEM_TYPE = 2 ) THEN
1341 stmt := 831;
1342 IF ( cr.EAM_ACTIVITY_TYPE_CODE IS NOT NULL ) THEN
1343 l_col_name := 'EAM_ACTIVITY_TYPE_CODE';
1344 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1345
1346 -- 3762750: Using cursor call to avoid multiple parsing
1347 OPEN c_mfglookup_exists('MTL_EAM_ACTIVITY_TYPE',cr.EAM_ACTIVITY_TYPE_CODE);
1348 FETCH c_mfglookup_exists INTO temp;
1349 CLOSE c_mfglookup_exists;
1350
1351 IF (temp IS NULL) THEN
1352 dumm_status := INVPUOPI.mtl_log_interface_err
1353 ( cr.organization_id,user_id, login_id, prog_appid,
1354 prog_id, request_id,cr.TRANSACTION_ID, error_msg,
1355 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',l_msg_name,err_text );
1356 IF dumm_status < 0 THEN
1357 raise LOGGING_ERR;
1358 END IF;
1359 status := 1;
1360 END IF;
1361 END IF;
1362
1363 stmt := 832;
1364
1365 IF ( cr.EAM_ACTIVITY_CAUSE_CODE IS NOT NULL ) THEN
1366 l_col_name := 'EAM_ACTIVITY_CAUSE_CODE';
1367 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1368
1369 -- 3762750: Using cursor call to avoid multiple parsing
1370 OPEN c_mfglookup_exists('MTL_EAM_ACTIVITY_CAUSE',cr.EAM_ACTIVITY_CAUSE_CODE);
1371 FETCH c_mfglookup_exists INTO temp;
1372 CLOSE c_mfglookup_exists;
1373 IF (temp IS NULL) THEN
1374 dumm_status := INVPUOPI.mtl_log_interface_err
1375 ( cr.organization_id,
1376 user_id, login_id, prog_appid, prog_id, request_id,
1377 cr.TRANSACTION_ID, error_msg,
1378 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1379 l_msg_name,
1380 err_text );
1381 IF dumm_status < 0 THEN
1382 raise LOGGING_ERR;
1383 END IF;
1384 status := 1;
1385 END IF;
1386 END IF;
1387
1388 stmt := 833;
1389
1390 IF ( cr.EAM_ACT_SHUTDOWN_STATUS IS NOT NULL ) THEN
1391 l_col_name := 'EAM_ACT_SHUTDOWN_STATUS';
1392 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1393
1394 -- 3762750: Using cursor call to avoid multiple parsing
1395 OPEN c_mfglookup_exists('BOM_EAM_SHUTDOWN_TYPE',cr.EAM_ACT_SHUTDOWN_STATUS);
1396 FETCH c_mfglookup_exists INTO temp;
1397 CLOSE c_mfglookup_exists;
1398 IF (temp IS NULL) THEN
1399 dumm_status := INVPUOPI.mtl_log_interface_err
1400 ( cr.organization_id,
1401 user_id, login_id, prog_appid, prog_id, request_id,
1402 cr.TRANSACTION_ID, error_msg,
1403 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1404 l_msg_name, err_text );
1405 IF dumm_status < 0 THEN
1406 raise LOGGING_ERR;
1407 END IF;
1408 status := 1;
1409 END IF;
1410 END IF;
1411
1412 stmt := 834;
1413
1414 -- Asset Activity Notification Required flag must be
1415 -- either NULL, 'Y' or 'N' for Asset Activity items.
1416
1417 IF ( NOT ( cr.EAM_ACT_NOTIFICATION_FLAG IS NULL
1418 OR cr.EAM_ACT_NOTIFICATION_FLAG IN ('Y', 'N') ))
1419 THEN
1420 l_col_name := 'EAM_ACT_NOTIFICATION_FLAG';
1421 l_msg_name := 'INV_IOI_FLAG_Y_N';
1422 dumm_status := INVPUOPI.mtl_log_interface_err
1423 ( cr.organization_id,
1424 user_id, login_id, prog_appid, prog_id, request_id,
1425 cr.TRANSACTION_ID, error_msg,
1426 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1427 l_msg_name,
1428 err_text );
1429 IF dumm_status < 0 THEN
1430 raise LOGGING_ERR;
1431 END IF;
1432 status := 1;
1433 END IF;
1434
1435 --Added EAM attribute as part of 11.5.9
1436 stmt := 835;
1437
1438 -- Asset Activity source must be NULL or valid value
1439 -- for Asset Activity items.
1440
1441 IF ( cr.EAM_ACTIVITY_SOURCE_CODE IS NOT NULL ) THEN
1442 l_col_name := 'EAM_ACTIVITY_SOURCE_CODE';
1443 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1444 -- 3762750: Using cursor call to avoid multiple parsing
1445 OPEN c_fndlookup_exists('MTL_EAM_ACTIVITY_SOURCE',cr.EAM_ACTIVITY_SOURCE_CODE);
1446 FETCH c_fndlookup_exists INTO temp;
1447 CLOSE c_fndlookup_exists;
1448
1449 IF temp IS NULL THEN
1450 dumm_status := INVPUOPI.mtl_log_interface_err
1451 ( cr.organization_id,
1452 user_id, login_id, prog_appid, prog_id, request_id,
1453 cr.TRANSACTION_ID, error_msg,
1454 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1455 l_msg_name,
1456 err_text );
1457 IF dumm_status < 0 THEN
1458 raise LOGGING_ERR;
1459 END IF;
1460 status := 1;
1461 END IF;
1462 END IF;
1463
1464 ELSE -- cr.EAM_ITEM_TYPE <> 2 OR cr.EAM_ITEM_TYPE IS NULL
1465 -- Asset Activity attribute columns must be NULL for non-Asset Activity
1466 -- items (Asset Group, Rebuildable Asset and others).
1467 stmt := 841;
1468 IF ( cr.EAM_ACTIVITY_TYPE_CODE IS NOT NULL ) THEN
1469 l_col_name := 'EAM_ACTIVITY_TYPE_CODE';
1470 l_msg_name := 'INV_EAM_NON_ACT_ACT_TYPE';
1471
1472 dumm_status := INVPUOPI.mtl_log_interface_err
1473 ( cr.organization_id,
1474 user_id, login_id, prog_appid, prog_id, request_id,
1475 cr.TRANSACTION_ID, error_msg,
1476 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1477 l_msg_name,
1478 err_text );
1479 IF dumm_status < 0 THEN
1480 raise LOGGING_ERR;
1481 END IF;
1482 status := 1;
1483 END IF;
1484
1485 stmt := 842;
1486
1487 IF ( cr.EAM_ACTIVITY_CAUSE_CODE IS NOT NULL ) THEN
1488
1489 l_col_name := 'EAM_ACTIVITY_CAUSE_CODE';
1490 l_msg_name := 'INV_EAM_NON_ACT_ACT_CAUSE';
1491
1492 dumm_status := INVPUOPI.mtl_log_interface_err
1493 ( cr.organization_id,
1494 user_id, login_id, prog_appid, prog_id, request_id,
1495 cr.TRANSACTION_ID, error_msg,
1496 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1497 l_msg_name,
1498 err_text );
1499 IF dumm_status < 0 THEN
1500 raise LOGGING_ERR;
1501 END IF;
1502 status := 1;
1503 END IF;
1504
1505 stmt := 843;
1506
1507 IF ( cr.EAM_ACT_SHUTDOWN_STATUS IS NOT NULL ) THEN
1508
1509 l_col_name := 'EAM_ACT_SHUTDOWN_STATUS';
1510 l_msg_name := 'INV_EAM_NON_ACT_ACT_SHUTDOWN';
1511
1512 dumm_status := INVPUOPI.mtl_log_interface_err
1513 ( cr.organization_id,
1514 user_id, login_id, prog_appid, prog_id, request_id,
1515 cr.TRANSACTION_ID, error_msg,
1516 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1517 l_msg_name,
1518 err_text );
1519 IF dumm_status < 0 THEN
1520 raise LOGGING_ERR;
1521 END IF;
1522 status := 1;
1523 END IF;
1524
1525 stmt := 844;
1526
1527 IF ( NVL(cr.EAM_ACT_NOTIFICATION_FLAG, 'N') <> 'N' ) THEN
1528
1529 l_col_name := 'EAM_ACT_NOTIFICATION_FLAG';
1530 l_msg_name := 'INV_EAM_NON_ACT_ACT_NOTIF_FLAG';
1531
1532 dumm_status := INVPUOPI.mtl_log_interface_err
1533 ( cr.organization_id,
1534 user_id, login_id, prog_appid, prog_id, request_id,
1535 cr.TRANSACTION_ID, error_msg,
1536 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1537 l_msg_name,
1538 err_text );
1539 IF dumm_status < 0 THEN
1540 raise LOGGING_ERR;
1541 END IF;
1542 status := 1;
1543 END IF;
1544
1545 --Added EAM attribute as part of 11.5.9
1546 stmt := 845;
1547 IF ( cr.EAM_ACTIVITY_SOURCE_CODE IS NOT NULL ) THEN
1548 l_col_name := 'EAM_ACTIVITY_SOURCE_CODE';
1549 l_msg_name := 'INV_EAM_NON_ACT_ACT_SOURCE';
1550
1551 dumm_status := INVPUOPI.mtl_log_interface_err
1552 ( cr.organization_id,
1553 user_id, login_id, prog_appid, prog_id, request_id,
1554 cr.TRANSACTION_ID, error_msg,
1555 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1556 l_msg_name,
1557 err_text );
1558 IF dumm_status < 0 THEN
1559 raise LOGGING_ERR;
1560 END IF;
1561 status := 1;
1562 END IF;
1563
1564 END IF; -- EAM_ITEM_TYPE = 2
1565
1566 stmt := 851;
1567
1568 --2949730 : Check on valid values on so source types introduced.
1569 IF (cr.DEFAULT_SO_SOURCE_TYPE IS NOT NULL) THEN
1570 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1571 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1572
1573 BEGIN
1574 SELECT 'x' INTO temp
1575 FROM OE_LOOKUPS
1576 WHERE LOOKUP_TYPE = 'SOURCE_TYPE'
1577 AND LOOKUP_CODE = cr.DEFAULT_SO_SOURCE_TYPE
1578 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
1579 AND ENABLED_FLAG = 'Y';
1580
1581 EXCEPTION
1582 WHEN no_data_found THEN
1583 dumm_status := INVPUOPI.mtl_log_interface_err
1584 (cr.organization_id,
1585 user_id, login_id, prog_appid, prog_id, request_id,
1586 cr.TRANSACTION_ID, error_msg,
1587 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1588 l_msg_name, err_text );
1589 IF dumm_status < 0 THEN
1590 raise LOGGING_ERR;
1591 END IF;
1592 status := 1;
1593 END;
1594 END IF;
1595
1596 --Start 2904941 SO_SOURCE_TYPE->(PURCHASING_ENABLED-SHIP_MODEL) Validations
1597 IF (cr.DEFAULT_SO_SOURCE_TYPE ='EXTERNAL') THEN
1598
1599 IF (cr.PURCHASING_ENABLED_FLAG <>'Y' OR cr.PURCHASING_ENABLED_FLAG IS NULL) THEN
1600 l_msg_name := 'INV_PURCHASING_SO_SOURCE_TYPE';
1601 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1602 dumm_status := INVPUOPI.mtl_log_interface_err
1603 (cr.organization_id,
1604 user_id, login_id, prog_appid, prog_id, request_id,
1605 cr.TRANSACTION_ID, error_msg,
1606 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1607 l_msg_name, err_text );
1608 IF dumm_status < 0 THEN
1609 raise LOGGING_ERR;
1610 END IF;
1611 status := 1;
1612 END IF;
1613
1614 IF (cr.SHIP_MODEL_COMPLETE_FLAG ='Y') THEN
1615 l_msg_name := 'INV_DEFAULT_SO_SOURCE_TYPE_EXT';
1616 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1617 dumm_status := INVPUOPI.mtl_log_interface_err
1618 (cr.organization_id,
1619 user_id, login_id, prog_appid, prog_id, request_id,
1620 cr.TRANSACTION_ID, error_msg,
1621 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1622 l_msg_name, err_text );
1623 IF dumm_status < 0 THEN
1624 raise LOGGING_ERR;
1625 END IF;
1626 status := 1;
1627 END IF;
1628 END IF; --cr.DEFAULT_SO_SOURCE_TYPE
1629 stmt := 852;
1630 --End 2904941 SO_SOURCE_TYPE->(PURCHASING_ENABLED-SHIP_MODEL) Validations
1631
1632 --Start 2993300: New Attributes validation
1633 stmt := 912;
1634
1635 l_col_name := NULL;
1636 IF NVL(cr.SERV_BILLING_ENABLED_FLAG,'N') NOT IN ('Y','N') THEN
1637 l_col_name := 'SERV_BILLING_ENABLED_FLAG';
1638 ELSIF NOT (cr.PLANNED_INV_POINT_FLAG IS NULL OR cr.PLANNED_INV_POINT_FLAG ='Y') THEN
1639 l_col_name := 'PLANNED_INV_POINT_FLAG';
1640 ELSIF NVL(cr.CREATE_SUPPLY_FLAG,'Z') NOT IN ('Y','N') THEN
1641 l_col_name := 'CREATE_SUPPLY_FLAG';
1642 ELSIF NOT(cr.SUBSTITUTION_WINDOW_CODE IS NULL OR cr.SUBSTITUTION_WINDOW_CODE IN (1,2,3,4)) THEN
1643 l_col_name := 'SUBSTITUTION_WINDOW_CODE';
1644 ELSIF ( cr.SUBSTITUTION_WINDOW_DAYS < 0) THEN
1645 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1646 ELSIF ( cr.SUBSTITUTION_WINDOW_DAYS < 0) THEN
1647 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1648 ELSIF ( NVL( cr.ASSET_CREATION_CODE,'0' ) NOT IN ('0','1')) THEN--11.5.10
1649 l_col_name := 'ASSET_CREATION_CODE';
1650 END IF;
1651
1652 IF l_col_name IS NOT NULL THEN
1653 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1654 dumm_status := INVPUOPI.mtl_log_interface_err
1655 (cr.organization_id,
1656 user_id, login_id, prog_appid, prog_id, request_id,
1657 cr.TRANSACTION_ID, error_msg,
1658 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1659 l_msg_name, err_text );
1660 IF dumm_status < 0 THEN
1661 raise LOGGING_ERR;
1662 END IF;
1663 status := 1;
1664 END IF;
1665
1666 l_col_name := NULL;
1667 IF cr.SUBSTITUTION_WINDOW_CODE = 4
1668 AND cr.SUBSTITUTION_WINDOW_DAYS IS NULL THEN
1669 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1670 l_msg_name := 'INV_IOI_SUBS_WIN_DAYS_MAND';
1671 ELSIF (cr.SUBSTITUTION_WINDOW_CODE IS NULL
1672 OR cr.SUBSTITUTION_WINDOW_CODE <> 4) --Bug: 3289000
1673 AND cr.SUBSTITUTION_WINDOW_DAYS IS NOT NULL THEN
1674 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1675 l_msg_name := 'INV_IOI_SUBS_WIN_DAYS_NULL';
1676 END IF;
1677
1678 IF l_col_name IS NOT NULL THEN
1679 dumm_status := INVPUOPI.mtl_log_interface_err
1680 (cr.organization_id,
1681 user_id, login_id, prog_appid, prog_id, request_id,
1682 cr.TRANSACTION_ID, error_msg,
1683 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1684 l_msg_name, err_text );
1685 IF dumm_status < 0 THEN
1686 raise LOGGING_ERR;
1687 END IF;
1688 status := 1;
1689 END IF;
1690
1691 --Start 2339789:uom units without uom code
1692 /* R12 C Weight UOM Code can now be updated for Pending items. Moving the below set of validations to INVPVHDR
1693 l_col_name := NULL;
1694 l_msg_name := NULL;
1695
1696 IF cr.WEIGHT_UOM_CODE IS NULL
1697 AND (cr.UNIT_WEIGHT IS NOT NULL OR cr.MAXIMUM_LOAD_WEIGHT IS NOT NULL) THEN --Bug: 3503944
1698 l_col_name := 'WEIGHT_UOM_CODE';
1699 l_msg_name := 'INV_IOI_WEIGHT_UOM_MISSING';
1700 ELSIF cr.VOLUME_UOM_CODE IS NULL
1701 AND (cr.UNIT_VOLUME IS NOT NULL OR cr.INTERNAL_VOLUME IS NOT NULL) THEN --Bug: 3503944
1702 l_col_name := 'VOLUME_UOM_CODE';
1703 l_msg_name := 'INV_IOI_VOLUME_UOM_MISSING';
1704 ELSIF cr.DIMENSION_UOM_CODE IS NULL
1705 AND (cr.UNIT_LENGTH IS NOT NULL
1706 OR cr.UNIT_WIDTH IS NOT NULL
1707 OR cr.UNIT_HEIGHT IS NOT NULL)
1708 THEN
1709 l_col_name := 'DIMENSION_UOM_CODE';
1710 l_msg_name := 'INV_IOI_DIMENSION_UOM_MISSING';
1711 END IF;
1712
1713 IF l_col_name IS NOT NULL THEN
1714 dumm_status := INVPUOPI.mtl_log_interface_err
1715 (cr.organization_id,
1716 user_id, login_id, prog_appid, prog_id, request_id,
1717 cr.TRANSACTION_ID, error_msg,
1718 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1719 l_msg_name, err_text );
1720 IF dumm_status < 0 THEN
1721 raise LOGGING_ERR;
1722 END IF;
1723 status := 1;
1724 END IF; */
1725 --End 2339789:uom units without uom code
1726 --End 2993300: New Attributes validation
1727
1728 /*
1729 INVPUTLI.info('INVPVDR4.validate_item_header4: validate EAM attributes (EAM_ITEM_TYPE = ' || cr.EAM_ITEM_TYPE || ')');
1730
1731 IF ( cr.XXX IS NOT NULL ) THEN
1732
1733 BEGIN
1734
1735 -- ...
1736
1737 l_col_name := '';
1738 l_msg_name := '';
1739
1740
1741 EXCEPTION
1742 WHEN no_data_found THEN
1743 dumm_status := INVPUOPI.mtl_log_interface_err
1744 ( cr.organization_id,
1745 user_id, login_id, prog_appid, prog_id, request_id,
1746 cr.TRANSACTION_ID,
1747 error_msg,
1748 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1749 l_msg_name, -- l_msg_token
1750 err_text );
1751 IF dumm_status < 0 THEN
1752 raise LOGGING_ERR;
1753 END IF;
1754 status := 1;
1755 END;
1756
1757 END IF; -- ...
1758 */
1759 /* NP26DEC94 : New code to update process_flag.
1760 ** This code necessiated due to the breaking up INVPVHDR into
1761 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1762 ** Let's update the process flag for the record
1763 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1764 ** Need to do this ONLY if all previous validation okay.
1765 ** The process flag values that are possible at this time are
1766 ** 31, :set by INVPVHDR
1767 ** 32, :set by INVPVDR2
1768 ** 33, 43 :set by INVPVDR3
1769 */
1770
1771 stmt := 911;
1772
1773 /* Bug 4705184
1774 SELECT process_flag into temp_proc_flag
1775 FROM MTL_SYSTEM_ITEMS_INTERFACE
1776 WHERE inventory_item_id = l_item_id
1777 AND set_process_id + 0 = xset_id
1778 AND process_flag in (31,32,33,43)
1779 AND organization_id = cr.organization_id
1780 AND rownum < 2; */
1781
1782 /* set value of process_flag to 44 or 34 depending on
1783 ** value of the variable: status.
1784 ** Essentially, we check to see if validation has not already failed in one of
1785 ** the previous packages.
1786 */
1787
1788 stmt := 913;
1789
1790 IF (temp_proc_flag <> 31 AND
1791 temp_proc_flag <> 32 AND
1792 temp_proc_flag <> 33 )
1793 THEN
1794 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
1795 SET process_flag = DECODE(status,0,44,34),
1796 PRIMARY_UOM_CODE = cr.primary_uom_code,
1797 primary_unit_of_measure = cr.primary_unit_of_measure
1798 WHERE inventory_item_id = l_item_id
1799 -- AND set_process_id + 0 = xset_id --fix for bug#8757041,removed + 0
1800 AND set_process_id = xset_id
1801 AND process_flag = 43
1802 AND organization_id = cr.organization_id;
1803 END IF;
1804 END LOOP; -- cr IN cc
1805
1806 RETURN (0);
1807
1808 EXCEPTION
1809 WHEN LOGGING_ERR THEN
1810 return (dumm_status);
1811
1812 WHEN VALIDATE_ERR THEN
1813 dumm_status := INVPUOPI.mtl_log_interface_err(
1814 l_org_id,
1815 user_id,
1816 login_id,
1817 prog_appid,
1818 prog_id,
1819 request_id,
1820 trans_id,
1821 err_text,
1822 'validation_error ' || stmt,
1823 'MTL_SYSTEM_ITEMS_INTERFACE',
1824 'BOM_OP_VALIDATION_ERR',
1825 err_text);
1826 return (status);
1827 WHEN OTHERS THEN
1828 err_text := substr('INVPVDR4.validate_item_header4: ' || SQLERRM , 1, 240);
1829 IF l_inv_debug_level IN(101, 102) THEN
1830 INVPUTLI.info(err_text || ' (stmt=' || TO_CHAR(stmt) || ') TRANSACTION_ID : ' || TO_CHAR(trans_id));
1831 END IF;
1832 return (SQLCODE);
1833 END validate_item_header4;
1834
1835 END INVPVDR4;