[Home] [Help]
PACKAGE BODY: APPS.INVPVDR4
Source
1 PACKAGE BODY INVPVDR4 AS
2 /* $Header: INVPVD4B.pls 120.16.12010000.2 2008/09/26 13:14:24 pgandhik 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
390 user_id,
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,
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 WHERE tax_type not in ('AWT','OFFSET')
441 AND enabled_flag = 'Y'
442 AND sysdate between start_date_active and nvl(end_date_active,sysdate)
443 AND lookup_code = cr.purchasing_tax_code
444 AND org_id IN (-99,(SELECT org_information3 FROM hr_organization_information
445 WHERE ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
446 AND ORGANIZATION_ID=cr.organization_id)))
447 AND ROWNUM = 1;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 dumm_status := INVPUOPI.mtl_log_interface_err(
451 cr.organization_id,
452 user_id,
453 login_id,
454 prog_appid,
455 prog_id,
456 request_id,
457 cr.TRANSACTION_ID,
458 error_msg,
459 'PURCHASING_TAX_CODE',
460 'MTL_SYSTEM_ITEMS_INTERFACE',
461 'INV_IOI_PUR_TAX_CODE',
462 err_text);
463 IF dumm_status < 0 THEN
464 raise LOGGING_ERR;
465 END IF;
466 status := 1;
467 END;
468 END IF; -- purchasing_tax_code is not null
469
470 -- bug 2843301 Validate Market and List Price.
471
472 IF (nvl(cr.MARKET_PRICE, 0) < 0 ) THEN
473 dumm_status := INVPUOPI.mtl_log_interface_err(
474 cr.organization_id,
475 user_id,
476 login_id,
477 prog_appid,
478 prog_id,
479 request_id,
480 cr.TRANSACTION_ID,
481 error_msg,
482 'MARKET_PRICE',
483 'MTL_SYSTEM_ITEMS_INTERFACE',
484 'INV_GREATER_EQUAL_ZERO',
485 err_text);
486 IF dumm_status < 0 THEN
487 raise LOGGING_ERR;
488 END IF;
489 status := 1;
490 END IF;
491
492 IF (NVL(cr.LIST_PRICE_PER_UNIT, 0) < 0 ) THEN
493 dumm_status := INVPUOPI.mtl_log_interface_err(
494 cr.organization_id,
495 user_id,
496 login_id,
497 prog_appid,
498 prog_id,
499 request_id,
500 cr.TRANSACTION_ID,
504 'INV_GREATER_EQUAL_ZERO',
501 error_msg,
502 'LIST_PRICE_PER_UNIT',
503 'MTL_SYSTEM_ITEMS_INTERFACE',
505 err_text);
506 IF dumm_status < 0 THEN
507 raise LOGGING_ERR;
508 END IF;
509 status := 1;
510 END IF;
511
512 -- validate lookup
513 IF (cr.return_inspection_requirement <> 1 AND cr.return_inspection_requirement <> 2) THEN
514 dumm_status := INVPUOPI.mtl_log_interface_err(
515 cr.organization_id,
516 user_id,
517 login_id,
518 prog_appid,
519 prog_id,
520 request_id,
521 cr.TRANSACTION_ID,
522 error_msg,
523 'RETURN_INSPECTION_REQUIREMENT',
524 'MTL_SYSTEM_ITEMS_INTERFACE',
525 'INV_IOI_RETURN_INSP_REQ',
526 err_text);
527 IF dumm_status < 0 THEN
528 raise LOGGING_ERR;
529 END IF;
530 status := 1;
531 END IF;
532
533 -- validate lookup
534 IF (cr.ato_forecast_control <> 1 AND
535 cr.ato_forecast_control <> 2 AND
536 cr.ato_forecast_control <> 3)
537 THEN
538 dumm_status := INVPUOPI.mtl_log_interface_err(
539 cr.organization_id,
540 user_id,
541 login_id,
542 prog_appid,
543 prog_id,
544 request_id,
545 cr.TRANSACTION_ID,
546 error_msg,
547 'ATO_FORECAST_CONTROL',
548 'MTL_SYSTEM_ITEMS_INTERFACE',
549 'INV_IOI_ATO_FORECAST_CTRL',
550 err_text);
551 IF dumm_status < 0 THEN
552 raise LOGGING_ERR;
553 END IF;
554 status := 1;
555 END IF;
556
557
558 -- Bug 3969864 - Anmurali
559 -- validate CUMULATIVE_TOTAL_LEAD_TIME
560 -- length should not exceed 42 digits
561 IF (LENGTH(TO_CHAR(cr.cumulative_total_lead_time)) > 42 ) THEN
562 dumm_status := INVPUOPI.mtl_log_interface_err(
563 cr.organization_id,
564 user_id,
565 login_id,
566 prog_appid,
567 prog_id,
568 request_id,
569 cr.TRANSACTION_ID,
570 error_msg,
571 'CUMULATIVE_TOTAL_LEAD_TIME',
572 'MTL_SYSTEM_ITEMS_INTERFACE',
573 'INV_IOI_NUMBER_TOO_LONG',
574 err_text);
575 IF dumm_status < 0 THEN
576 raise LOGGING_ERR;
577 END IF;
578 status := 1;
579 END IF;
580
581 -- Bug 5724477
582 -- validate CUM_MANUFACTURING_LEAD_TIME
583 -- length should not exceed 42 digits
584 IF (length(to_char(cr.CUM_MANUFACTURING_LEAD_TIME)) > 42 ) THEN
585 dumm_status := INVPUOPI.mtl_log_interface_err(
586 cr.organization_id,
587 user_id,
588 login_id,
589 prog_appid,
590 prog_id,
591 request_id,
592 cr.TRANSACTION_ID,
593 error_msg,
594 'CUM_MANUFACTURING_LEAD_TIME',
595 'MTL_SYSTEM_ITEMS_INTERFACE',
596 'INV_IOI_CUM_MANU_TOO_LONG',
597 err_text);
598 IF dumm_status < 0 THEN
599 raise LOGGING_ERR;
600 END IF;
601 status := 1;
602 END IF;
603
604
605 -- Validate Coverage Template value Added as part of 11.5.9 ENH.
606 IF (cr.COVERAGE_SCHEDULE_ID IS NOT NULL ) THEN
607 IF (cr.contract_item_type_code IS NULL ) THEN
608 dumm_status := INVPUOPI.mtl_log_interface_err(
609 cr.organization_id,
610 user_id,
611 login_id,
612 prog_appid,
613 prog_id,
614 request_id,
615 cr.TRANSACTION_ID,
616 error_msg,
617 'COVERAGE_SCHEDULE_ID',
618 'MTL_SYSTEM_ITEMS_INTERFACE',
619 'INV_COVERAGE_TEMP_CONTRACT',
620 err_text);
621 IF dumm_status < 0 THEN
622 raise LOGGING_ERR;
623 END IF;
624 status := 1;
628 l_msg_name := 'INV_IOI_ERR';
625 ELSE
626 BEGIN
627 l_col_name := 'COVERAGE_SCHEDULE_ID';
629 fnd_message.SET_NAME ('INV', 'INV_INVALID_ATTR_NAME_VALUE');
630 fnd_message.SET_TOKEN ('ATTR', 'Template');
631 error_msg := fnd_message.get;
632 OPEN c_check_oks_template;
633 FETCH c_check_oks_template INTO l_oks_exits;
634 CLOSE c_check_oks_template;
635 IF l_oks_exits = 'Y' THEN
636 EXECUTE IMMEDIATE
637 'BEGIN '||
638 'SELECT ''x'' INTO :temp '||
639 'FROM OKS_COVERAGE_TEMPLTS_V '||
640 'WHERE ITEM_TYPE = :cr.CONTRACT_ITEM_TYPE_CODE '||
641 'AND ID = :cr.COVERAGE_SCHEDULE_ID '||
642 'AND SYSDATE BETWEEN NVL(start_date, SYSDATE-1) AND NVL(end_date, SYSDATE+1); '||
643 'END;'
644 USING OUT temp, IN cr.CONTRACT_ITEM_TYPE_CODE, IN cr.COVERAGE_SCHEDULE_ID;
645 ELSE
646 SELECT 'x' INTO temp
647 FROM OKS_COVERAGE_TEMPLTS_V
648 WHERE ID = cr.COVERAGE_SCHEDULE_ID
649 AND SYSDATE BETWEEN NVL(start_date, SYSDATE-1) AND NVL(end_date, SYSDATE+1);
650 END IF;
651 EXCEPTION
652 WHEN NO_DATA_FOUND THEN
653 dumm_status := INVPUOPI.mtl_log_interface_err
654 ( cr.organization_id,
655 user_id, login_id, prog_appid, prog_id, request_id,
656 cr.TRANSACTION_ID, error_msg,
657 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
658 l_msg_name,
659 err_text );
660 IF dumm_status < 0 THEN
661 raise LOGGING_ERR;
662 END IF;
663 status := 1;
664 END;
665 END IF;
666 -- Bug: 2811878 This validation will be in 11.5.10 in IOI
667 ELSIF(cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY')) THEN
668 dumm_status := INVPUOPI.mtl_log_interface_err(
669 cr.organization_id,
670 user_id,
671 login_id,
672 prog_appid,
673 prog_id,
674 request_id,
675 cr.TRANSACTION_ID,
676 error_msg,
677 'COVERAGE_SCHEDULE_ID',
678 'MTL_SYSTEM_ITEMS_INTERFACE',
679 'INV_COVERAGE_TEMPL_MAND',
680 err_text);
681 IF dumm_status < 0 THEN
682 raise LOGGING_ERR;
683 END IF;
684 status := 1;
685 END IF; -- Coverage Template end
686
687 -- Validate Installed Base tracking Flag.
688 IF (cr.COMMS_NL_TRACKABLE_FLAG = 'Y' AND
689 --Bug: 2696647 Subscription Items can be Installed Base trackable
690 cr.contract_item_type_code IN ('SERVICE','WARRANTY','USAGE'))
691 THEN
692 dumm_status := INVPUOPI.mtl_log_interface_err(
693 cr.organization_id,
694 user_id,
695 login_id,
696 prog_appid,
697 prog_id,
698 request_id,
699 cr.TRANSACTION_ID,
700 error_msg,
701 'COMMS_NL_TRACKABLE_FLAG',
702 'MTL_SYSTEM_ITEMS_INTERFACE',
703 'INV_IB_TRACKING_CONTRACT',
704 err_text);
705 IF dumm_status < 0 THEN
706 raise LOGGING_ERR;
707 END IF;
708 status := 1;
709 END IF;
710
711 --Start 3416621 Subscription should be IB trackble
712 IF (cr.contract_item_type_code = 'SUBSCRIPTION' AND
713 NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y')
714 THEN
715 dumm_status := INVPUOPI.mtl_log_interface_err(
716 cr.organization_id,
717 user_id,
718 login_id,
719 prog_appid,
720 prog_id,
721 request_id,
722 cr.TRANSACTION_ID,
723 error_msg,
724 'COMMS_NL_TRACKABLE_FLAG',
725 'MTL_SYSTEM_ITEMS_INTERFACE',
726 'INV_CTTYPE_INSBASE_VALID',
727 err_text);
728 IF dumm_status < 0 THEN
729 raise LOGGING_ERR;
730 END IF;
731 status := 1;
732 END IF;
733
734 --End 3416621 Subscription should be IB trackble
735 --Added for 11.5.10
736 IF (NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') = 'N' AND
740 cr.organization_id,
737 NVL(cr.asset_creation_code,'0') = '1')
738 THEN
739 dumm_status := INVPUOPI.mtl_log_interface_err(
741 user_id,
742 login_id,
743 prog_appid,
744 prog_id,
745 request_id,
746 cr.TRANSACTION_ID,
747 error_msg,
748 'ASSET_CREATION_CODE',
749 'MTL_SYSTEM_ITEMS_INTERFACE',
750 'INV_INST_BASE_ASSET_CREATE_DEP',
751 err_text);
752 IF dumm_status < 0 THEN
753 raise LOGGING_ERR;
754 END IF;
755 status := 1;
756 END IF;
757
758 --Bug: 2710463 NVL added
759 IF (NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y' AND
760 cr.CONTRACT_ITEM_TYPE_CODE IS NULL AND
761 cr.SERVICEABLE_PRODUCT_FLAG = 'Y')
762 THEN
763 dumm_status := INVPUOPI.mtl_log_interface_err(
764 cr.organization_id,
765 user_id,
766 login_id,
767 prog_appid,
768 prog_id,
769 request_id,
770 cr.TRANSACTION_ID,
771 error_msg,
772 'COMMS_NL_TRACKABLE_FLAG',
773 'MTL_SYSTEM_ITEMS_INTERFACE',
774 'INV_IB_TRACKING_SERVICEABLE',
775 err_text);
776 IF dumm_status < 0 THEN
777 raise LOGGING_ERR;
778 END IF;
779 status := 1;
780 END IF;
781
782 --Validate LOT_SUBSTITUTION_ENABLED
783 --Added validation for new attributes as part of 11.5.9
784 IF ( cr.LOT_SUBSTITUTION_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
785 dumm_status := INVPUOPI.mtl_log_interface_err(
786 cr.organization_id,
787 user_id,
788 login_id,
789 prog_appid,
790 prog_id,
791 request_id,
792 cr.TRANSACTION_ID,
793 error_msg,
794 'LOT_SUBSTITUTION_ENABLED',
795 'MTL_SYSTEM_ITEMS_INTERFACE',
796 'INV_INVALID_LOT_SUB_ENABLED',
797 err_text);
798 IF dumm_status < 0 THEN
799 raise LOGGING_ERR;
800 END IF;
801 status := 1;
802 END IF;
803
804 -- Lot Subsitution flag must be either NULL or 'Y'
805 IF ( cr.LOT_SUBSTITUTION_ENABLED IS NOT NULL AND cr.LOT_SUBSTITUTION_ENABLED <> 'Y' )THEN
806 l_col_name := 'LOT_SUBSTITUTION_ENABLED';
807 l_msg_name := 'INV_NOT_VALID_FLAG';
808
809 dumm_status := INVPUOPI.mtl_log_interface_err
810 ( cr.organization_id,
811 user_id, login_id, prog_appid, prog_id, request_id,
812 cr.TRANSACTION_ID, error_msg,
813 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
814 l_msg_name,
815 err_text);
816 IF dumm_status < 0 THEN
817 raise LOGGING_ERR;
818 END IF;
819 status := 1;
820 END IF; -- LOT_SUBSTITUTION_ENABLED is not null
821
822 /*Bug: 5140047 Commenting out this validation as this is duplicate.
823 IF ( cr.LOT_SUBSTITUTION_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
824 dumm_status := INVPUOPI.mtl_log_interface_err(
825 cr.organization_id,
826 user_id,
827 login_id,
828 prog_appid,
829 prog_id,
830 request_id,
831 cr.TRANSACTION_ID,
832 error_msg,
833 'LOT_SUBSTITUTION_ENABLED',
834 'MTL_SYSTEM_ITEMS_INTERFACE',
835 'INV_INVALID_LOT_SUB_ENABLED',
836 err_text);
837 IF dumm_status < 0 THEN
838 raise LOGGING_ERR;
839 END IF;
840 status := 1;
841 END IF;
842 */
843 -- Lot Subsitution flag must be either NULL or 'Y'
844 --BEGIN : Fix for Bug# 2760857 (PPEDDAMA)
845 --Begin : ***Validate LOT_TRANSLATE_ENABLED***
846 IF ( cr.LOT_TRANSLATE_ENABLED IS NOT NULL AND cr.LOT_TRANSLATE_ENABLED NOT IN ( 'Y','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,
857 'MTL_SYSTEM_ITEMS_INTERFACE',
854 cr.TRANSACTION_ID,
855 error_msg,
856 'LOT_TRANSLATE_ENABLED',
858 'INV_IOI_FLAG_Y_N_NULL',
859 err_text);
860 IF dumm_status < 0 THEN
861 raise LOGGING_ERR;
862 END IF;
863 status := 1;
864 END IF; -- LOT_TRANSLATE_ENABLED is not null AND <> 'Y'
865
866 IF ( cr.LOT_TRANSLATE_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
867 dumm_status := INVPUOPI.mtl_log_interface_err(
868 cr.organization_id,
869 user_id,
870 login_id,
871 prog_appid,
872 prog_id,
873 request_id,
874 cr.TRANSACTION_ID,
875 error_msg,
876 'LOT_TRANSLATE_ENABLED',
877 'MTL_SYSTEM_ITEMS_INTERFACE',
878 'INV_INVALID_LOT_TRANS_ENABLED',
879 err_text);
880 IF dumm_status < 0 THEN
881 raise LOGGING_ERR;
882 END IF;
883 status := 1;
884 END IF;-- LOT_TRANSLATE_ENABLED = 'Y' AND <<NO LOT CONTROL>>
885 --End : ***Validate LOT_TRANSLATE_ENABLED***
886
887 --Begin : ***Validate LOT_SPLIT_ENABLED***
888 IF ( cr.LOT_SPLIT_ENABLED IS NOT NULL AND cr.LOT_SPLIT_ENABLED NOT IN ( 'Y','N') )THEN
889 dumm_status := INVPUOPI.mtl_log_interface_err(
890 cr.organization_id,
891 user_id,
892 login_id,
893 prog_appid,
894 prog_id,
895 request_id,
896 cr.TRANSACTION_ID,
897 error_msg,
898 'LOT_SPLIT_ENABLED',
899 'MTL_SYSTEM_ITEMS_INTERFACE',
900 'INV_IOI_FLAG_Y_N_NULL',
901 err_text);
902 IF dumm_status < 0 THEN
903 raise LOGGING_ERR;
904 END IF;
905 status := 1;
906 END IF;-- LOT_SPLIT_ENABLED is not null AND <> 'Y'
907
908 IF ( cr.LOT_SPLIT_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) THEN
909 dumm_status := INVPUOPI.mtl_log_interface_err(
910 cr.organization_id,
911 user_id,
912 login_id,
913 prog_appid,
914 prog_id,
915 request_id,
916 cr.TRANSACTION_ID,
917 error_msg,
918 'LOT_SPLIT_ENABLED',
919 'MTL_SYSTEM_ITEMS_INTERFACE',
920 'INV_INVALID_LOT_SPLIT_ENABLED',
921 err_text);
922 IF dumm_status < 0 THEN
923 raise LOGGING_ERR;
924 END IF;
925 status := 1;
926 END IF;-- LOT_SPLIT_ENABLED = 'Y' AND <<NO LOT CONTROL>>
927 --End : ***Validate LOT_SPLIT_ENABLED***
928
929 --Begin : ***Validate LOT_MERGE_ENABLED***
930 IF ( cr.LOT_MERGE_ENABLED IS NOT NULL AND cr.LOT_MERGE_ENABLED NOT IN ( 'Y','N') )THEN
931 dumm_status := INVPUOPI.mtl_log_interface_err(
932 cr.organization_id,
933 user_id,
934 login_id,
935 prog_appid,
936 prog_id,
937 request_id,
938 cr.TRANSACTION_ID,
939 error_msg,
940 'LOT_MERGE_ENABLED',
941 'MTL_SYSTEM_ITEMS_INTERFACE',
942 'INV_IOI_FLAG_Y_N_NULL',
943 err_text);
944 IF dumm_status < 0 THEN
945 raise LOGGING_ERR;
946 END IF;
947 status := 1;
948 END IF;-- LOT_MERGE_ENABLED is not null AND <> 'Y'
949
950 IF ( cr.LOT_MERGE_ENABLED = 'Y' AND NVL(cr.LOT_CONTROL_CODE,1)=1) 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 'LOT_MERGE_ENABLED',
961 'MTL_SYSTEM_ITEMS_INTERFACE',
962 'INV_INVALID_LOT_MERGE_ENABLED',
963 err_text);
964 IF dumm_status < 0 THEN
965 raise LOGGING_ERR;
966 END IF;
967 status := 1;
968 END IF;-- LOT_MERGE_ENABLED = 'Y' AND <<NO LOT CONTROL>>
972 --Bug No: 3285381 Modified IF st.
969 --End : ***Validate LOT_MERGE_ENABLED***
970 --END : Fix for Bug# 2760857 (PPEDDAMA)
971
973 IF ( NVL(cr.MINIMUM_LICENSE_QUANTITY,1) < 1 OR
974 NVL(cr.MINIMUM_LICENSE_QUANTITY,1) <> CEIL(NVL(cr.MINIMUM_LICENSE_QUANTITY,1)) )
975 THEN
976 dumm_status := INVPUOPI.mtl_log_interface_err(
977 cr.organization_id,
978 user_id,
979 login_id,
980 prog_appid,
981 prog_id,
982 request_id,
983 cr.TRANSACTION_ID,
984 error_msg,
985 'MINIMUM_LICENSE_QUANTITY',
986 'MTL_SYSTEM_ITEMS_INTERFACE',
987 'INV_INVALID_MIN_LICENSE',
988 err_text);
989 IF dumm_status < 0 THEN
990 raise LOGGING_ERR;
991 END IF;
992 status := 1;
993 END IF; -- MINIMUM_LICENSE_QUANTITY < 1
994
995 IF ( NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y' AND cr.IB_ITEM_INSTANCE_CLASS IS NOT NULL) THEN
996 dumm_status := INVPUOPI.mtl_log_interface_err(
997 cr.organization_id,
998 user_id,
999 login_id,
1000 prog_appid,
1001 prog_id,
1002 request_id,
1003 cr.TRANSACTION_ID,
1004 error_msg,
1005 'IB_ITEM_INSTANCE_CLASS',
1006 'MTL_SYSTEM_ITEMS_INTERFACE',
1007 'INV_IB_INSTANCE_CLASS_IB_TRACK',
1008 err_text);
1009 IF dumm_status < 0 THEN
1010 raise LOGGING_ERR;
1011 END IF;
1012 status := 1;
1013 END IF;
1014
1015 IF ( cr.IB_ITEM_INSTANCE_CLASS IS NOT NULL ) THEN
1016 l_col_name := 'IB_ITEM_INSTANCE_CLASS';
1017 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1018 -- 3762750: Using cursor call to avoid multiple parsing
1019 OPEN c_fndlookup_exists('CSI_ITEM_CLASS',cr.IB_ITEM_INSTANCE_CLASS);
1020 FETCH c_fndlookup_exists INTO temp;
1021 CLOSE c_fndlookup_exists;
1022
1023 IF temp IS NULL THEN
1024 dumm_status := INVPUOPI.mtl_log_interface_err
1025 ( cr.organization_id,
1026 user_id, login_id, prog_appid, prog_id, request_id,
1027 cr.TRANSACTION_ID, error_msg,
1028 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1029 l_msg_name,
1030 err_text );
1031 IF dumm_status < 0 THEN
1032 raise LOGGING_ERR;
1033 END IF;
1034 status := 1;
1035 END IF;
1036 END IF;-- IB_ITEM_INSTANCE_CLASS IS NOTNULL
1037
1038 -- This Can be NOT NULL only for orderable and configurable PTO Models.
1039 IF ( cr.CONFIG_MODEL_TYPE IS NOT NULL AND
1040 NOT ( cr.BOM_ITEM_TYPE = 1 AND
1041 cr.CUSTOMER_ORDER_ENABLED_FLAG = 'Y' ) )
1042 THEN
1043 dumm_status := INVPUOPI.mtl_log_interface_err(
1044 cr.organization_id,
1045 user_id,
1046 login_id,
1047 prog_appid,
1048 prog_id,
1049 request_id,
1050 cr.TRANSACTION_ID,
1051 error_msg,
1052 'CONFIG_MODEL_TYPE',
1053 'MTL_SYSTEM_ITEMS_INTERFACE',
1054 'INV_CONFIG_MODEL_TYPE_MOD',
1055 err_text);
1056 IF dumm_status < 0 THEN
1057 raise LOGGING_ERR;
1058 END IF;
1059 status := 1;
1060 END IF; -- CONFIG_MODEL_TYPE IS NOTNULL
1061
1062 -- CONFIG_MODEL_TYPE "Network Container Model" (N) cannot be
1063 -- Installed Base trackable item.
1064 IF ( NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') = 'Y' AND cr.CONFIG_MODEL_TYPE = 'N' ) THEN
1065 dumm_status := INVPUOPI.mtl_log_interface_err(
1066 cr.organization_id,
1067 user_id,
1068 login_id,
1069 prog_appid,
1070 prog_id,
1071 request_id,
1072 cr.TRANSACTION_ID,
1073 error_msg,
1074 'CONFIG_MODEL_TYPE',
1075 'MTL_SYSTEM_ITEMS_INTERFACE',
1076 'INV_CONFIG_MODEL_TYPE_IB_TRACK',
1077 err_text);
1078 IF dumm_status < 0 THEN
1079 raise LOGGING_ERR;
1080 END IF;
1081 status := 1;
1082 END IF; -- CONFIG_MODEL_TYPE IS NOTNULL
1083
1084 IF ( cr.CONFIG_MODEL_TYPE IS NOT NULL ) THEN
1088 OPEN c_fndlookup_exists('CZ_CONFIG_MODEL_TYPE',cr.CONFIG_MODEL_TYPE);
1085 l_col_name := 'CONFIG_MODEL_TYPE';
1086 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1087 -- 3762750: Using cursor call to avoid multiple parsing
1089 FETCH c_fndlookup_exists INTO temp;
1090 CLOSE c_fndlookup_exists;
1091
1092 IF temp IS NULL THEN
1093 dumm_status := INVPUOPI.mtl_log_interface_err
1094 ( cr.organization_id,
1095 user_id, login_id, prog_appid, prog_id, request_id,
1096 cr.TRANSACTION_ID, error_msg,
1097 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1098 l_msg_name,
1099 err_text );
1100 IF dumm_status < 0 THEN
1101 raise LOGGING_ERR;
1102 END IF;
1103 status := 1;
1104 END IF;
1105 END IF;-- CONFIG_MODEL_TYPE IS NOTNULL
1106
1107 -- Ended FOR 11.5.9
1108 -- Validate WEB_STATUS
1109 IF ( cr.WEB_STATUS is not null ) THEN
1110 -- 3762750: Using cursor call to avoid multiple parsing
1111 OPEN c_fndlookup_exists('IBE_ITEM_STATUS',cr.WEB_STATUS);
1112 FETCH c_fndlookup_exists INTO temp;
1113 CLOSE c_fndlookup_exists;
1114 IF temp IS NULL THEN
1115 dumm_status := INVPUOPI.mtl_log_interface_err(
1116 cr.organization_id,
1117 user_id,
1118 login_id,
1119 prog_appid,
1120 prog_id,
1121 request_id,
1122 cr.TRANSACTION_ID,
1123 error_msg,
1124 'WEB_STATUS',
1125 'MTL_SYSTEM_ITEMS_INTERFACE',
1126 'INV_IOI_WEB_STATUS',
1127 err_text);
1128 IF dumm_status < 0 THEN
1129 raise LOGGING_ERR;
1130 END IF;
1131 status := 1;
1132 END IF;
1133 END IF; -- WEB_STATUS is not null
1134
1135 --Validate INDIVISIBLE_FLAG
1136 -- Do not raise error
1137 -- Validate EAM Asset Item attributes
1138 IF l_inv_debug_level IN(101, 102) THEN
1139 INVPUTLI.info('INVPVDR4.validate_item_header4: validate EAM attributes (EAM_ITEM_TYPE = ' || cr.EAM_ITEM_TYPE || ')');
1140 END IF;
1141
1142 IF ( cr.EAM_ITEM_TYPE IS NOT NULL ) THEN
1143 BEGIN
1144 -- Organization must be EAM enabled for Asset Items to be imported
1145 l_col_name := 'EAM_ITEM_TYPE';
1146 l_msg_name := 'INV_EAM_ORG_NOT_ENABLED';
1147
1148 stmt := 811;
1149 SELECT 'x' INTO temp
1150 FROM MTL_PARAMETERS
1151 WHERE ORGANIZATION_ID = cr.organization_id
1152 AND (NVL(EAM_ENABLED_FLAG, 'N') = 'Y' or cr.organization_id = master_organization_id);
1153 --Bug:2672219 and NVL(EAM_ENABLED_FLAG, 'N') = 'Y';
1154 -- Check Asset Item type value based on mfg_lookups
1155 l_col_name := 'EAM_ITEM_TYPE';
1156 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1157 --l_msg_token := l_col_name;
1158
1159 stmt := 812;
1160
1161 -- 3762750: Using cursor call to avoid multiple parsing
1162 OPEN c_mfglookup_exists('MTL_EAM_ITEM_TYPE',cr.EAM_ITEM_TYPE);
1163 FETCH c_mfglookup_exists INTO temp;
1164 CLOSE c_mfglookup_exists;
1165 IF temp IS NULL THEN
1166 RAISE no_data_found;
1167 END IF;
1168 EXCEPTION
1169 WHEN no_data_found THEN
1170 dumm_status := INVPUOPI.mtl_log_interface_err
1171 ( cr.organization_id,
1172 user_id, login_id, prog_appid, prog_id, request_id,
1173 cr.TRANSACTION_ID, error_msg,
1174 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1175 l_msg_name, -- l_msg_token
1176 err_text );
1177 IF dumm_status < 0 THEN
1178 raise LOGGING_ERR;
1179 END IF;
1180 status := 1;
1181 END;
1182 stmt := 821;
1183 -- All Asset Items must be Inventory items
1184 IF ( NOT ( cr.INVENTORY_ITEM_FLAG = 'Y' ) ) THEN
1185 l_col_name := 'INVENTORY_ITEM_FLAG';
1186 l_msg_name := 'INV_EAM_ITEM_TYPE_PF_INV';
1187
1188 dumm_status := INVPUOPI.mtl_log_interface_err
1189 ( cr.organization_id,
1190 user_id, login_id, prog_appid, prog_id, request_id,
1191 cr.TRANSACTION_ID, error_msg,
1192 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1193 l_msg_name,
1194 err_text );
1195 IF dumm_status < 0 THEN
1196 raise LOGGING_ERR;
1197 END IF;
1198 status := 1;
1199 END IF;
1200
1201 stmt := 822;
1202 --R12 enhacement : Added validation Asset Group Item and Rebuidable item which is serial controlled must be IB Trackabale
1203
1207 THEN
1204 IF ( (cr.EAM_ITEM_TYPE = 1 OR
1205 (cr.EAM_ITEM_TYPE = 3 AND cr.SERIAL_NUMBER_CONTROL_CODE <> 1 ) )
1206 AND NVL(cr.COMMS_NL_TRACKABLE_FLAG,'N') <> 'Y')
1208 l_col_name := 'COMMS_NL_TRACKABLE_FLAG';
1209 l_msg_name := 'INV_EAM_IB_TRACKABLE';
1210
1211 dumm_status := INVPUOPI.mtl_log_interface_err
1212 (cr.organization_id,
1213 user_id,
1214 login_id,
1215 prog_appid,
1216 prog_id,
1217 request_id,
1218 cr.TRANSACTION_ID,
1219 error_msg,
1220 l_col_name,
1221 'MTL_SYSTEM_ITEMS_INTERFACE',
1222 l_msg_name,
1223 err_text );
1224 IF dumm_status < 0 THEN
1225 raise LOGGING_ERR;
1226 END IF;
1227 status := 1;
1228 END IF;
1229
1230 -- Asset Group has unit effective BOM
1231 IF ( cr.EAM_ITEM_TYPE = 1 AND NOT ( cr.EFFECTIVITY_CONTROL = 2 ) ) THEN
1232 l_col_name := 'EFFECTIVITY_CONTROL';
1233 l_msg_name := 'INV_EAM_ASSET_UNIT_CONTROL';
1234
1235 dumm_status := INVPUOPI.mtl_log_interface_err
1236 ( cr.organization_id,
1237 user_id, login_id, prog_appid, prog_id, request_id,
1238 cr.TRANSACTION_ID, error_msg,
1239 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1240 l_msg_name,
1241 err_text );
1242 IF dumm_status < 0 THEN
1243 raise LOGGING_ERR;
1244 END IF;
1245 status := 1;
1246 END IF;
1247
1248 stmt := 824;
1249
1250 -- Asset Group must be serial controlled of type Pre-Defined
1251 -- Change the second clause to include 'At Reciept' Items for R12
1252 IF ( cr.EAM_ITEM_TYPE = 1 AND cr.SERIAL_NUMBER_CONTROL_CODE NOT IN (2,5)) THEN
1253 l_col_name := 'SERIAL_NUMBER_CONTROL_CODE';
1254 l_msg_name := 'INV_EAM_ASSET_GRP_NO_SERIAL';
1255
1256 dumm_status := INVPUOPI.mtl_log_interface_err
1257 ( cr.organization_id,
1258 user_id, login_id, prog_appid, prog_id, request_id,
1259 cr.TRANSACTION_ID, error_msg,
1260 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1261 l_msg_name,
1262 err_text );
1263 IF dumm_status < 0 THEN
1264 raise LOGGING_ERR;
1265 END IF;
1266 status := 1;
1267 END IF;
1268
1269 stmt := 825;
1270
1271 -- Asset Activity must not be serial controlled
1272
1273 IF ( cr.EAM_ITEM_TYPE = 2
1274 AND NOT ( cr.SERIAL_NUMBER_CONTROL_CODE = 1 ) )
1275 THEN
1276 l_col_name := 'SERIAL_NUMBER_CONTROL_CODE';
1277 l_msg_name := 'INV_EAM_ACTIVITY_NEVER_SERIAL';
1278
1279 dumm_status := INVPUOPI.mtl_log_interface_err
1280 ( cr.organization_id,
1281 user_id, login_id, prog_appid, prog_id, request_id,
1282 cr.TRANSACTION_ID, error_msg,
1283 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1284 l_msg_name,
1285 err_text );
1286 IF dumm_status < 0 THEN
1287 raise LOGGING_ERR;
1288 END IF;
1289 status := 1;
1290 END IF;
1291
1292 END IF; -- EAM_ITEM_TYPE IS NOT NULL
1293
1294 --R12 enhacement : Tracking UOM is PS, Organization is process_enabled Bom Allowed must be No
1295 --and BOM item type cannot be MODEL or OPTION CLASS
1296 --Reverting the above fix for OPM - No chk for Process Enabled org
1297 --4756500 Using l_org_id instead of org_id
1298 --Moving the validation out of the EAM_ITEM_TYPE IF BLOCK - UT Bug
1299
1300 IF l_inv_debug_level IN(101, 102) THEN
1301 INVPUTLI.info('INVPVDR4.validate_item_header4: Validating Tracking and BOM attributes');
1302 END IF;
1303
1304 IF ( cr.TRACKING_QUANTITY_IND = 'PS' AND
1305 (cr.BOM_ITEM_TYPE in (1,2) OR cr.BOM_ENABLED_FLAG='Y' ) )
1306 THEN
1307 l_col_name := 'TRACKING_QUANTITY_IND';
1308 l_msg_name := 'INV_TRACKING_OPM_BOM_ATTR';
1309 dumm_status := INVPUOPI.mtl_log_interface_err
1310 ( cr.organization_id,
1311 user_id,
1312 login_id,
1313 prog_appid,
1314 prog_id,
1315 request_id,
1316 cr.TRANSACTION_ID,
1317 error_msg,
1318 l_col_name,
1319 'MTL_SYSTEM_ITEMS_INTERFACE',
1320 l_msg_name,
1321 err_text );
1325 status := 1;
1322 IF dumm_status < 0 THEN
1323 raise LOGGING_ERR;
1324 END IF;
1326 END IF;
1327
1328 -- For EAM Asset Activity item, validate Activity Type, Activity Cause,
1329 -- and Shutdown Type attribute values based on mfg_lookups.
1330 -- Convert lookup code to char since columns are varchar2, and
1331 -- mfg_lookups code is number.
1332
1333 IF ( cr.EAM_ITEM_TYPE = 2 ) THEN
1334 stmt := 831;
1335 IF ( cr.EAM_ACTIVITY_TYPE_CODE IS NOT NULL ) THEN
1336 l_col_name := 'EAM_ACTIVITY_TYPE_CODE';
1337 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1338
1339 -- 3762750: Using cursor call to avoid multiple parsing
1340 OPEN c_mfglookup_exists('MTL_EAM_ACTIVITY_TYPE',cr.EAM_ACTIVITY_TYPE_CODE);
1341 FETCH c_mfglookup_exists INTO temp;
1342 CLOSE c_mfglookup_exists;
1343
1344 IF (temp IS NULL) THEN
1345 dumm_status := INVPUOPI.mtl_log_interface_err
1346 ( cr.organization_id,user_id, login_id, prog_appid,
1347 prog_id, request_id,cr.TRANSACTION_ID, error_msg,
1348 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',l_msg_name,err_text );
1349 IF dumm_status < 0 THEN
1350 raise LOGGING_ERR;
1351 END IF;
1352 status := 1;
1353 END IF;
1354 END IF;
1355
1356 stmt := 832;
1357
1358 IF ( cr.EAM_ACTIVITY_CAUSE_CODE IS NOT NULL ) THEN
1359 l_col_name := 'EAM_ACTIVITY_CAUSE_CODE';
1360 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1361
1362 -- 3762750: Using cursor call to avoid multiple parsing
1363 OPEN c_mfglookup_exists('MTL_EAM_ACTIVITY_CAUSE',cr.EAM_ACTIVITY_CAUSE_CODE);
1364 FETCH c_mfglookup_exists INTO temp;
1365 CLOSE c_mfglookup_exists;
1366 IF (temp IS NULL) THEN
1367 dumm_status := INVPUOPI.mtl_log_interface_err
1368 ( cr.organization_id,
1369 user_id, login_id, prog_appid, prog_id, request_id,
1370 cr.TRANSACTION_ID, error_msg,
1371 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1372 l_msg_name,
1373 err_text );
1374 IF dumm_status < 0 THEN
1375 raise LOGGING_ERR;
1376 END IF;
1377 status := 1;
1378 END IF;
1379 END IF;
1380
1381 stmt := 833;
1382
1383 IF ( cr.EAM_ACT_SHUTDOWN_STATUS IS NOT NULL ) THEN
1384 l_col_name := 'EAM_ACT_SHUTDOWN_STATUS';
1385 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1386
1387 -- 3762750: Using cursor call to avoid multiple parsing
1388 OPEN c_mfglookup_exists('BOM_EAM_SHUTDOWN_TYPE',cr.EAM_ACT_SHUTDOWN_STATUS);
1389 FETCH c_mfglookup_exists INTO temp;
1390 CLOSE c_mfglookup_exists;
1391 IF (temp IS NULL) THEN
1392 dumm_status := INVPUOPI.mtl_log_interface_err
1393 ( cr.organization_id,
1394 user_id, login_id, prog_appid, prog_id, request_id,
1395 cr.TRANSACTION_ID, error_msg,
1396 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1397 l_msg_name, err_text );
1398 IF dumm_status < 0 THEN
1399 raise LOGGING_ERR;
1400 END IF;
1401 status := 1;
1402 END IF;
1403 END IF;
1404
1405 stmt := 834;
1406
1407 -- Asset Activity Notification Required flag must be
1408 -- either NULL, 'Y' or 'N' for Asset Activity items.
1409
1410 IF ( NOT ( cr.EAM_ACT_NOTIFICATION_FLAG IS NULL
1411 OR cr.EAM_ACT_NOTIFICATION_FLAG IN ('Y', 'N') ))
1412 THEN
1413 l_col_name := 'EAM_ACT_NOTIFICATION_FLAG';
1414 l_msg_name := 'INV_IOI_FLAG_Y_N';
1415 dumm_status := INVPUOPI.mtl_log_interface_err
1416 ( cr.organization_id,
1417 user_id, login_id, prog_appid, prog_id, request_id,
1418 cr.TRANSACTION_ID, error_msg,
1419 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1420 l_msg_name,
1421 err_text );
1422 IF dumm_status < 0 THEN
1423 raise LOGGING_ERR;
1424 END IF;
1425 status := 1;
1426 END IF;
1427
1428 --Added EAM attribute as part of 11.5.9
1429 stmt := 835;
1430
1431 -- Asset Activity source must be NULL or valid value
1432 -- for Asset Activity items.
1433
1434 IF ( cr.EAM_ACTIVITY_SOURCE_CODE IS NOT NULL ) THEN
1435 l_col_name := 'EAM_ACTIVITY_SOURCE_CODE';
1436 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1437 -- 3762750: Using cursor call to avoid multiple parsing
1438 OPEN c_fndlookup_exists('MTL_EAM_ACTIVITY_SOURCE',cr.EAM_ACTIVITY_SOURCE_CODE);
1439 FETCH c_fndlookup_exists INTO temp;
1440 CLOSE c_fndlookup_exists;
1441
1442 IF temp IS NULL THEN
1443 dumm_status := INVPUOPI.mtl_log_interface_err
1447 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1444 ( cr.organization_id,
1445 user_id, login_id, prog_appid, prog_id, request_id,
1446 cr.TRANSACTION_ID, error_msg,
1448 l_msg_name,
1449 err_text );
1450 IF dumm_status < 0 THEN
1451 raise LOGGING_ERR;
1452 END IF;
1453 status := 1;
1454 END IF;
1455 END IF;
1456
1457 ELSE -- cr.EAM_ITEM_TYPE <> 2 OR cr.EAM_ITEM_TYPE IS NULL
1458 -- Asset Activity attribute columns must be NULL for non-Asset Activity
1459 -- items (Asset Group, Rebuildable Asset and others).
1460 stmt := 841;
1461 IF ( cr.EAM_ACTIVITY_TYPE_CODE IS NOT NULL ) THEN
1462 l_col_name := 'EAM_ACTIVITY_TYPE_CODE';
1463 l_msg_name := 'INV_EAM_NON_ACT_ACT_TYPE';
1464
1465 dumm_status := INVPUOPI.mtl_log_interface_err
1466 ( cr.organization_id,
1467 user_id, login_id, prog_appid, prog_id, request_id,
1468 cr.TRANSACTION_ID, error_msg,
1469 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1470 l_msg_name,
1471 err_text );
1472 IF dumm_status < 0 THEN
1473 raise LOGGING_ERR;
1474 END IF;
1475 status := 1;
1476 END IF;
1477
1478 stmt := 842;
1479
1480 IF ( cr.EAM_ACTIVITY_CAUSE_CODE IS NOT NULL ) THEN
1481
1482 l_col_name := 'EAM_ACTIVITY_CAUSE_CODE';
1483 l_msg_name := 'INV_EAM_NON_ACT_ACT_CAUSE';
1484
1485 dumm_status := INVPUOPI.mtl_log_interface_err
1486 ( cr.organization_id,
1487 user_id, login_id, prog_appid, prog_id, request_id,
1488 cr.TRANSACTION_ID, error_msg,
1489 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1490 l_msg_name,
1491 err_text );
1492 IF dumm_status < 0 THEN
1493 raise LOGGING_ERR;
1494 END IF;
1495 status := 1;
1496 END IF;
1497
1498 stmt := 843;
1499
1500 IF ( cr.EAM_ACT_SHUTDOWN_STATUS IS NOT NULL ) THEN
1501
1502 l_col_name := 'EAM_ACT_SHUTDOWN_STATUS';
1503 l_msg_name := 'INV_EAM_NON_ACT_ACT_SHUTDOWN';
1504
1505 dumm_status := INVPUOPI.mtl_log_interface_err
1506 ( cr.organization_id,
1507 user_id, login_id, prog_appid, prog_id, request_id,
1508 cr.TRANSACTION_ID, error_msg,
1509 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1510 l_msg_name,
1511 err_text );
1512 IF dumm_status < 0 THEN
1513 raise LOGGING_ERR;
1514 END IF;
1515 status := 1;
1516 END IF;
1517
1518 stmt := 844;
1519
1520 IF ( NVL(cr.EAM_ACT_NOTIFICATION_FLAG, 'N') <> 'N' ) THEN
1521
1522 l_col_name := 'EAM_ACT_NOTIFICATION_FLAG';
1523 l_msg_name := 'INV_EAM_NON_ACT_ACT_NOTIF_FLAG';
1524
1525 dumm_status := INVPUOPI.mtl_log_interface_err
1526 ( cr.organization_id,
1527 user_id, login_id, prog_appid, prog_id, request_id,
1528 cr.TRANSACTION_ID, error_msg,
1529 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1530 l_msg_name,
1531 err_text );
1532 IF dumm_status < 0 THEN
1533 raise LOGGING_ERR;
1534 END IF;
1535 status := 1;
1536 END IF;
1537
1538 --Added EAM attribute as part of 11.5.9
1539 stmt := 845;
1540 IF ( cr.EAM_ACTIVITY_SOURCE_CODE IS NOT NULL ) THEN
1541 l_col_name := 'EAM_ACTIVITY_SOURCE_CODE';
1542 l_msg_name := 'INV_EAM_NON_ACT_ACT_SOURCE';
1543
1544 dumm_status := INVPUOPI.mtl_log_interface_err
1545 ( cr.organization_id,
1546 user_id, login_id, prog_appid, prog_id, request_id,
1547 cr.TRANSACTION_ID, error_msg,
1548 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1549 l_msg_name,
1550 err_text );
1551 IF dumm_status < 0 THEN
1552 raise LOGGING_ERR;
1553 END IF;
1554 status := 1;
1555 END IF;
1556
1557 END IF; -- EAM_ITEM_TYPE = 2
1558
1559 stmt := 851;
1560
1561 --2949730 : Check on valid values on so source types introduced.
1562 IF (cr.DEFAULT_SO_SOURCE_TYPE IS NOT NULL) THEN
1563 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1564 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1565
1566 BEGIN
1567 SELECT 'x' INTO temp
1568 FROM OE_LOOKUPS
1569 WHERE LOOKUP_TYPE = 'SOURCE_TYPE'
1573
1570 AND LOOKUP_CODE = cr.DEFAULT_SO_SOURCE_TYPE
1571 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
1572 AND ENABLED_FLAG = 'Y';
1574 EXCEPTION
1575 WHEN no_data_found THEN
1576 dumm_status := INVPUOPI.mtl_log_interface_err
1577 (cr.organization_id,
1578 user_id, login_id, prog_appid, prog_id, request_id,
1579 cr.TRANSACTION_ID, error_msg,
1580 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1581 l_msg_name, err_text );
1582 IF dumm_status < 0 THEN
1583 raise LOGGING_ERR;
1584 END IF;
1585 status := 1;
1586 END;
1587 END IF;
1588
1589 --Start 2904941 SO_SOURCE_TYPE->(PURCHASING_ENABLED-SHIP_MODEL) Validations
1590 IF (cr.DEFAULT_SO_SOURCE_TYPE ='EXTERNAL') THEN
1591
1592 IF (cr.PURCHASING_ENABLED_FLAG <>'Y' OR cr.PURCHASING_ENABLED_FLAG IS NULL) THEN
1593 l_msg_name := 'INV_PURCHASING_SO_SOURCE_TYPE';
1594 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1595 dumm_status := INVPUOPI.mtl_log_interface_err
1596 (cr.organization_id,
1597 user_id, login_id, prog_appid, prog_id, request_id,
1598 cr.TRANSACTION_ID, error_msg,
1599 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1600 l_msg_name, err_text );
1601 IF dumm_status < 0 THEN
1602 raise LOGGING_ERR;
1603 END IF;
1604 status := 1;
1605 END IF;
1606
1607 IF (cr.SHIP_MODEL_COMPLETE_FLAG ='Y') THEN
1608 l_msg_name := 'INV_DEFAULT_SO_SOURCE_TYPE_EXT';
1609 l_col_name := 'DEFAULT_SO_SOURCE_TYPE';
1610 dumm_status := INVPUOPI.mtl_log_interface_err
1611 (cr.organization_id,
1612 user_id, login_id, prog_appid, prog_id, request_id,
1613 cr.TRANSACTION_ID, error_msg,
1614 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1615 l_msg_name, err_text );
1616 IF dumm_status < 0 THEN
1617 raise LOGGING_ERR;
1618 END IF;
1619 status := 1;
1620 END IF;
1621 END IF; --cr.DEFAULT_SO_SOURCE_TYPE
1622 stmt := 852;
1623 --End 2904941 SO_SOURCE_TYPE->(PURCHASING_ENABLED-SHIP_MODEL) Validations
1624
1625 --Start 2993300: New Attributes validation
1626 stmt := 912;
1627
1628 l_col_name := NULL;
1629 IF NVL(cr.SERV_BILLING_ENABLED_FLAG,'N') NOT IN ('Y','N') THEN
1630 l_col_name := 'SERV_BILLING_ENABLED_FLAG';
1631 ELSIF NOT (cr.PLANNED_INV_POINT_FLAG IS NULL OR cr.PLANNED_INV_POINT_FLAG ='Y') THEN
1632 l_col_name := 'PLANNED_INV_POINT_FLAG';
1633 ELSIF NVL(cr.CREATE_SUPPLY_FLAG,'Z') NOT IN ('Y','N') THEN
1634 l_col_name := 'CREATE_SUPPLY_FLAG';
1635 ELSIF NOT(cr.SUBSTITUTION_WINDOW_CODE IS NULL OR cr.SUBSTITUTION_WINDOW_CODE IN (1,2,3,4)) THEN
1636 l_col_name := 'SUBSTITUTION_WINDOW_CODE';
1637 ELSIF ( cr.SUBSTITUTION_WINDOW_DAYS < 0) THEN
1638 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1639 ELSIF ( cr.SUBSTITUTION_WINDOW_DAYS < 0) THEN
1640 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1641 ELSIF ( NVL( cr.ASSET_CREATION_CODE,'0' ) NOT IN ('0','1')) THEN--11.5.10
1642 l_col_name := 'ASSET_CREATION_CODE';
1643 END IF;
1644
1645 IF l_col_name IS NOT NULL THEN
1646 l_msg_name := 'INV_INVALID_ATTR_COL_VALUE';
1647 dumm_status := INVPUOPI.mtl_log_interface_err
1648 (cr.organization_id,
1649 user_id, login_id, prog_appid, prog_id, request_id,
1650 cr.TRANSACTION_ID, error_msg,
1651 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1652 l_msg_name, err_text );
1653 IF dumm_status < 0 THEN
1654 raise LOGGING_ERR;
1655 END IF;
1656 status := 1;
1657 END IF;
1658
1659 l_col_name := NULL;
1660 IF cr.SUBSTITUTION_WINDOW_CODE = 4
1661 AND cr.SUBSTITUTION_WINDOW_DAYS IS NULL THEN
1662 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1663 l_msg_name := 'INV_IOI_SUBS_WIN_DAYS_MAND';
1664 ELSIF (cr.SUBSTITUTION_WINDOW_CODE IS NULL
1665 OR cr.SUBSTITUTION_WINDOW_CODE <> 4) --Bug: 3289000
1666 AND cr.SUBSTITUTION_WINDOW_DAYS IS NOT NULL THEN
1667 l_col_name := 'SUBSTITUTION_WINDOW_DAYS';
1668 l_msg_name := 'INV_IOI_SUBS_WIN_DAYS_NULL';
1669 END IF;
1670
1671 IF l_col_name IS NOT NULL THEN
1672 dumm_status := INVPUOPI.mtl_log_interface_err
1673 (cr.organization_id,
1674 user_id, login_id, prog_appid, prog_id, request_id,
1675 cr.TRANSACTION_ID, error_msg,
1676 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1677 l_msg_name, err_text );
1678 IF dumm_status < 0 THEN
1679 raise LOGGING_ERR;
1680 END IF;
1681 status := 1;
1682 END IF;
1683
1684 --Start 2339789:uom units without uom code
1688
1685 /* R12 C Weight UOM Code can now be updated for Pending items. Moving the below set of validations to INVPVHDR
1686 l_col_name := NULL;
1687 l_msg_name := NULL;
1689 IF cr.WEIGHT_UOM_CODE IS NULL
1690 AND (cr.UNIT_WEIGHT IS NOT NULL OR cr.MAXIMUM_LOAD_WEIGHT IS NOT NULL) THEN --Bug: 3503944
1691 l_col_name := 'WEIGHT_UOM_CODE';
1692 l_msg_name := 'INV_IOI_WEIGHT_UOM_MISSING';
1693 ELSIF cr.VOLUME_UOM_CODE IS NULL
1694 AND (cr.UNIT_VOLUME IS NOT NULL OR cr.INTERNAL_VOLUME IS NOT NULL) THEN --Bug: 3503944
1695 l_col_name := 'VOLUME_UOM_CODE';
1696 l_msg_name := 'INV_IOI_VOLUME_UOM_MISSING';
1697 ELSIF cr.DIMENSION_UOM_CODE IS NULL
1698 AND (cr.UNIT_LENGTH IS NOT NULL
1699 OR cr.UNIT_WIDTH IS NOT NULL
1700 OR cr.UNIT_HEIGHT IS NOT NULL)
1701 THEN
1702 l_col_name := 'DIMENSION_UOM_CODE';
1703 l_msg_name := 'INV_IOI_DIMENSION_UOM_MISSING';
1704 END IF;
1705
1706 IF l_col_name IS NOT NULL THEN
1707 dumm_status := INVPUOPI.mtl_log_interface_err
1708 (cr.organization_id,
1709 user_id, login_id, prog_appid, prog_id, request_id,
1710 cr.TRANSACTION_ID, error_msg,
1711 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1712 l_msg_name, err_text );
1713 IF dumm_status < 0 THEN
1714 raise LOGGING_ERR;
1715 END IF;
1716 status := 1;
1717 END IF; */
1718 --End 2339789:uom units without uom code
1719 --End 2993300: New Attributes validation
1720
1721 /*
1722 INVPUTLI.info('INVPVDR4.validate_item_header4: validate EAM attributes (EAM_ITEM_TYPE = ' || cr.EAM_ITEM_TYPE || ')');
1723
1724 IF ( cr.XXX IS NOT NULL ) THEN
1725
1726 BEGIN
1727
1728 -- ...
1729
1730 l_col_name := '';
1731 l_msg_name := '';
1732
1733
1734 EXCEPTION
1735 WHEN no_data_found THEN
1736 dumm_status := INVPUOPI.mtl_log_interface_err
1737 ( cr.organization_id,
1738 user_id, login_id, prog_appid, prog_id, request_id,
1739 cr.TRANSACTION_ID,
1740 error_msg,
1741 l_col_name, 'MTL_SYSTEM_ITEMS_INTERFACE',
1742 l_msg_name, -- l_msg_token
1743 err_text );
1744 IF dumm_status < 0 THEN
1745 raise LOGGING_ERR;
1746 END IF;
1747 status := 1;
1748 END;
1749
1750 END IF; -- ...
1751 */
1752 /* NP26DEC94 : New code to update process_flag.
1753 ** This code necessiated due to the breaking up INVPVHDR into
1754 ** 6 smaller packages to overcome PL/SQL limitations with code size.
1755 ** Let's update the process flag for the record
1756 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
1757 ** Need to do this ONLY if all previous validation okay.
1758 ** The process flag values that are possible at this time are
1759 ** 31, :set by INVPVHDR
1760 ** 32, :set by INVPVDR2
1761 ** 33, 43 :set by INVPVDR3
1762 */
1763
1764 stmt := 911;
1765
1766 /* Bug 4705184
1767 SELECT process_flag into temp_proc_flag
1768 FROM MTL_SYSTEM_ITEMS_INTERFACE
1769 WHERE inventory_item_id = l_item_id
1770 AND set_process_id + 0 = xset_id
1771 AND process_flag in (31,32,33,43)
1772 AND organization_id = cr.organization_id
1773 AND rownum < 2; */
1774
1775 /* set value of process_flag to 44 or 34 depending on
1776 ** value of the variable: status.
1777 ** Essentially, we check to see if validation has not already failed in one of
1778 ** the previous packages.
1779 */
1780
1781 stmt := 913;
1782
1783 IF (temp_proc_flag <> 31 AND
1784 temp_proc_flag <> 32 AND
1785 temp_proc_flag <> 33 )
1786 THEN
1787 UPDATE MTL_SYSTEM_ITEMS_INTERFACE
1788 SET process_flag = DECODE(status,0,44,34),
1789 PRIMARY_UOM_CODE = cr.primary_uom_code,
1790 primary_unit_of_measure = cr.primary_unit_of_measure
1791 WHERE inventory_item_id = l_item_id
1792 AND set_process_id + 0 = xset_id
1793 AND process_flag = 43
1794 AND organization_id = cr.organization_id;
1795 END IF;
1796 END LOOP; -- cr IN cc
1797
1798 RETURN (0);
1799
1800 EXCEPTION
1801 WHEN LOGGING_ERR THEN
1802 return (dumm_status);
1803
1804 WHEN VALIDATE_ERR THEN
1805 dumm_status := INVPUOPI.mtl_log_interface_err(
1806 l_org_id,
1807 user_id,
1808 login_id,
1809 prog_appid,
1810 prog_id,
1811 request_id,
1812 trans_id,
1816 'BOM_OP_VALIDATION_ERR',
1813 err_text,
1814 'validation_error ' || stmt,
1815 'MTL_SYSTEM_ITEMS_INTERFACE',
1817 err_text);
1818 return (status);
1819 WHEN OTHERS THEN
1820 err_text := substr('INVPVDR4.validate_item_header4: ' || SQLERRM , 1, 240);
1821 IF l_inv_debug_level IN(101, 102) THEN
1825 END validate_item_header4;
1822 INVPUTLI.info(err_text || ' (stmt=' || TO_CHAR(stmt) || ') TRANSACTION_ID : ' || TO_CHAR(trans_id));
1823 END IF;
1824 return (SQLCODE);
1826
1827 END INVPVDR4;