[Home] [Help]
PACKAGE BODY: APPS.INVPVDR6
Source
1 PACKAGE BODY INVPVDR6 AS
2 /* $Header: INVPVD6B.pls 120.23.12020000.3 2012/07/17 09:04:44 qixia ship $ */
3 FUNCTION validate_item_header6(
4 org_id NUMBER,
5 all_org NUMBER := 2,
6 prog_appid NUMBER := -1,
7 prog_id NUMBER := -1,
8 request_id NUMBER := -1,
9 user_id NUMBER := -1,
10 login_id NUMBER := -1,
11 err_text IN OUT NOCOPY VARCHAR2,
12 xset_id IN NUMBER DEFAULT -999) RETURN INTEGER IS
13
14 loc_ctrl_code NUMBER;
15 cost_flag VARCHAR2(1);
16 inv_asset_flag VARCHAR2(1);
17 mrp_stock_code NUMBER;
18 base_item NUMBER;
19 lead_lot_size NUMBER;
20 out_op_flag VARCHAR2(1);
21 shelf_code NUMBER;
22 temp VARCHAR2(2);
23 temp_uom_code VARCHAR2(3);
24 temp_u_o_m VARCHAR2(25);
25 temp_uom_class VARCHAR2(10);
26 temp_enabled_flag VARCHAR2(1);
27 reqst_id NUMBER ;
28 masterorg_id NUMBER ;
29
30 CURSOR cc IS
31 SELECT *
32 FROM MTL_SYSTEM_ITEMS_INTERFACE
33 WHERE ((organization_id = org_id) OR (all_Org = 1))
34 AND set_process_id = xset_id
35 AND process_flag in (31, 32, 33, 34 , 35 , 45);
36
37 --Bug: 3028216 Get the category sets which does not have default cat
38 CURSOR Func_Area_csr(cp_farea_id NUMBER) IS
39 SELECT FUNCTIONAL_AREA_DESC, mcs.category_set_name
40 FROM mtl_category_sets_vl mcs
41 ,mtl_default_category_sets_fk_v mdcs
42 WHERE mcs.category_set_id = mdcs.category_set_id
43 AND mcs.default_category_id IS NULL
44 AND mdcs.functional_area_id = cp_farea_id; --Bug 4654433
45
46 -- Added 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 --4195218 : Changed query for TAX_CODE
67 /* Fix for bug 6350384- Tax Codes are stored at O.U level, so modified below cursor
68 to add a subquery for fetching operating_unit */
69 /*Bug 7437620 Modified the query to fetch the operating unit
70 Table hr_organization_information is used instead of org_organization_defintions*/
71
72 CURSOR c_tax_code_exists(cp_tax_code VARCHAR2, cp_org_id NUMBER) IS
73 SELECT 'x'
74 FROM ZX_OUTPUT_CLASSIFICATIONS_V
75 WHERE lookup_code = cp_tax_code
76 AND enabled_flag = 'Y'
77 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
78 AND org_id IN (-99,(SELECT org_information3 FROM hr_organization_information
79 WHERE ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
80 AND ORGANIZATION_ID=cp_org_id));
81
82 --Start 6531903:Default catalog to be run only once
83 CURSOR c_function_area_setup IS
84 SELECT functional_area_id
85 FROM mtl_default_category_sets dcs
86 ,mtl_category_sets_b cs
87 WHERE cs.category_set_id = dcs.category_set_id
88 AND cs.default_category_id IS NULL
89 AND dcs.functional_area_id IN (1,2,3,4,5,6,7,9,10);
90
91 l_functional_area1 NUMBER := NULL;
92 l_functional_area2 NUMBER := NULL;
93 l_functional_area3 NUMBER := NULL;
94 l_functional_area4 NUMBER := NULL;
95 l_functional_area5 NUMBER := NULL;
96 l_functional_area6 NUMBER := NULL;
97 l_functional_area7 NUMBER := NULL;
98 l_functional_area9 NUMBER := NULL;
99 l_functional_area10 NUMBER := NULL;
100 l_functional_area_id NUMBER := NULL;
101 --End 6531903:Default catalog to be run only once
102
103 msicount NUMBER;
104 msiicount NUMBER;
105 resersal_flag NUMBER;
106 dup_item_id NUMBER;
107 l_item_id NUMBER;
108 l_org_id NUMBER;
109 cat_set_id NUMBER;
110 trans_id NUMBER;
111 ext_flag NUMBER := 0;
112 error_msg VARCHAR2(2000);
113 status NUMBER;
114 dumm_status NUMBER;
115 master_org_id NUMBER;
116 stmt NUMBER;
117 LOGGING_ERR EXCEPTION;
118 VALIDATE_ERR EXCEPTION;
119 chart_of_acc_id NUMBER; /*NP 30AUG94*/
120 temp_proc_flag NUMBER;
121 temp_count NUMBER;
122 count_is_zero EXCEPTION;
123 l_process_flag_2 NUMBER := 2 ;
124 l_process_flag_3 NUMBER := 3 ;
125 l_process_flag_4 NUMBER := 4 ;
126 l_Func_Area VARCHAR2(2000);
127 l_Cat_Set_Name VARCHAR2(2000);
128 Cat_Set_No_Default_Cat EXCEPTION;
129 validate_source NUMBER;
130 l_dummy VARCHAR2(1);
131 l_logerr NUMBER;
132 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
133
134 BEGIN
135
136 IF l_inv_debug_level IN(101, 102) THEN
137 INVPUTLI.info('INVPVDR6.validate_item_header6 : begin');
138 END IF;
139 reqst_id := request_id ;
140 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
141
142 --Start 3515652: Performance enhancements
143 dumm_status := INVPUPI2.validate_flags(
144 org_id
145 ,all_org
146 ,prog_appid
147 ,prog_id
148 ,request_id
149 ,user_id
150 ,login_id
151 ,xset_id
152 ,err_text);
153
154 if dumm_status <> 0 then
155 raise LOGGING_ERR;
156 end if;
157 --End 3515652: Performance enhancements
158
159 --Start 6531903:Default catalog to be run only once
160 OPEN c_function_area_setup;
161 LOOP
162 FETCH c_function_area_setup INTO l_functional_area_id;
163 EXIT WHEN c_function_area_setup%NOTFOUND;
164 IF l_functional_area_id = 1 THEN
165 l_functional_area1 := 1;
166 ELSIF l_functional_area_id = 2 THEN
167 l_functional_area2 := 2;
168 ELSIF l_functional_area_id = 3 THEN
169 l_functional_area3 := 3;
170 ELSIF l_functional_area_id = 4 THEN
171 l_functional_area4 := 4;
172 ELSIF l_functional_area_id = 5 THEN
173 l_functional_area5 := 5;
174 ELSIF l_functional_area_id = 6 THEN
175 l_functional_area6 := 6;
176 ELSIF l_functional_area_id = 7 THEN
177 l_functional_area7 := 7;
178 ELSIF l_functional_area_id = 9 THEN
179 l_functional_area9 := 9;
180 ELSIF l_functional_area_id = 10 THEN
181 l_functional_area10 := 10;
182 END IF;
183 END LOOP;
184 --End 6531903:Default catalog to be run only once
185
186 FOR cr IN cc LOOP
187 status := 0;
188 trans_id := cr.transaction_id;
189 l_org_id := cr.organization_id;
190 l_item_id := cr.inventory_item_id; -- Bug 4705184
191 temp_proc_flag := cr.process_flag; -- Bug 4705184
192
193 /* Bug 4705184
194 select inventory_item_id into l_item_id
195 from mtl_system_items_interface
196 where transaction_id = cr.transaction_id
197 and set_process_id = xset_id; */
198
199 -- Validate second group of foreign keys
200 IF l_inv_debug_level IN(101, 102) THEN
201 INVPUTLI.info('INVPVDR6.validate_item_header6: validating foreign keys set 3');
202 END IF;
203
204 stmt := 31;
205 -- validate foreign keys
206 temp_count := 0;
207 if cr.PLANNING_EXCEPTION_SET is not null then
208 begin
209 select count(*) into temp_count
210 from MRP_PLANNING_EXCEPTION_SETS
211 where EXCEPTION_SET_NAME = cr.PLANNING_EXCEPTION_SET;
212 if temp_count = 0 then
213 RAISE count_is_zero;
214 end if;
215 exception
216 WHEN count_is_zero THEN
217 dumm_status := INVPUOPI.mtl_log_interface_err(
218 cr.organization_id,
219 user_id,
220 login_id,
221 prog_appid,
222 prog_id,
223 request_id,
224 cr.TRANSACTION_ID,
225 error_msg,
226 'PLANNING_EXCEPTION_SET',
227 'MTL_SYSTEM_ITEMS_INTERFACE',
228 'INV_IOI_PLN_EXC_SET',
229 err_text);
230 if dumm_status < 0 then
231 raise LOGGING_ERR;
232 end if;
233 status := 1;
234 end;
235 end if;
236
237 stmt := 32;
238 -- validate foreign keys
239 if cr.ATP_RULE_ID is not null then
240 begin
241 select 'x' into temp
242 from MTL_ATP_RULES
243 where RULE_ID = cr.ATP_RULE_ID;
244 exception
245 when NO_DATA_FOUND then
246 dumm_status := INVPUOPI.mtl_log_interface_err(
247 cr.organization_id,
248 user_id,
249 login_id,
250 prog_appid,
251 prog_id,
252 request_id,
253 cr.TRANSACTION_ID,
254 error_msg,
255 'ATP_RULE_ID',
256 'MTL_SYSTEM_ITEMS_INTERFACE',
257 'INV_IOI_ATP_RULE',
258 err_text);
259 if dumm_status < 0 then
260 raise LOGGING_ERR;
261 end if;
262 status := 1;
263 end;
264 end if;
265
266 stmt := 33;
267 -- validate foreign keys
268 if cr.BASE_WARRANTY_SERVICE_ID is not null then
269 begin
270 select 'x' into temp
271 from MTL_SYSTEM_ITEMS_B
272 where INVENTORY_ITEM_ID = cr.BASE_WARRANTY_SERVICE_ID
273 and ORGANIZATION_ID = cr.ORGANIZATION_ID;
274 exception
275 when NO_DATA_FOUND then
276 dumm_status := INVPUOPI.mtl_log_interface_err(
277 cr.organization_id,
278 user_id,
279 login_id,
280 prog_appid,
281 prog_id,
282 request_id,
283 cr.TRANSACTION_ID,
284 error_msg,
285 'BASE_WARRANTY_SERVICE_ID',
286 'MTL_SYSTEM_ITEMS_INTERFACE',
287 'INV_IOI_BASE_WAR_SERV',
288 err_text);
289 if dumm_status < 0 then
290 raise LOGGING_ERR;
291 end if;
292 status := 1;
293 end;
294 end if;
295
296 stmt := 34;
297 -- validate foreign keys
298 if cr.PAYMENT_TERMS_ID is not null then
299 begin
300 select 'x' into temp
301 from RA_TERMS
302 where TERM_ID = cr.PAYMENT_TERMS_ID;
303 exception
304 when NO_DATA_FOUND then
305 dumm_status := INVPUOPI.mtl_log_interface_err(
306 cr.organization_id,
307 user_id,
308 login_id,
309 prog_appid,
310 prog_id,
311 request_id,
312 cr.TRANSACTION_ID,
313 error_msg,
314 'PAYMENT_TERMS_ID',
315 'MTL_SYSTEM_ITEMS_INTERFACE',
316 'INV_IOI_PAYMENT_TERMS',
317 err_text);
318 if dumm_status < 0 then
319 raise LOGGING_ERR;
320 end if;
321 status := 1;
322 end;
323 end if;
324
325 stmt := 34;
326 -- validate foreign keys
327 if cr.UNIT_OF_ISSUE is not null then
328 begin
329 select master_organization_id
330 into masterorg_id
331 from mtl_parameters
332 where organization_id = cr.organization_id ;
333
334 if (cr.transaction_type = 'CREATE') then
335 if (masterorg_id = cr.organization_id) then
336 select 'x' into temp
337 from MTL_UNITS_OF_MEASURE muom1
338 where muom1.UNIT_OF_MEASURE = cr.UNIT_OF_ISSUE
339 and muom1.UOM_CLASS in
340 (select UOM_CLASS
341 from MTL_UNITS_OF_MEASURE muom2
342 where muom2.UNIT_OF_MEASURE = cr.PRIMARY_UNIT_OF_MEASURE);
343 else
344 select 'x' into temp
345 from mtl_item_uoms_view
346 where organization_id = masterorg_id
347 and inventory_item_id = cr.inventory_item_id
348 and unit_of_measure = cr.unit_of_issue ;
349 end if ;
350 else
351 select 'x' into temp
352 from mtl_item_uoms_view
353 where organization_id = cr.organization_id
354 and inventory_item_id = cr.inventory_item_id
355 and unit_of_measure = cr.unit_of_issue ;
356 end if ;
357 exception
358 when NO_DATA_FOUND then
359 dumm_status := INVPUOPI.mtl_log_interface_err(
360 cr.organization_id,
361 user_id,
362 login_id,
363 prog_appid,
364 prog_id,
365 request_id,
366 cr.TRANSACTION_ID,
367 error_msg,
368 'UNIT_OF_ISSUE',
369 'MTL_SYSTEM_ITEMS_INTERFACE',
370 'INV_IOI_UNIT_OF_ISSUE',
371 err_text);
372 if dumm_status < 0 then
373 raise LOGGING_ERR;
374 end if;
375 status := 1;
376 end;
377 end if;
378
379 stmt := 38;
380 -- validate foreign keys
381 if cr.SOURCE_ORGANIZATION_ID is not null then
382 begin
383 /* Fix for bug 5844510-Use org_organization_definitions view
384 instead of mtl_parameters to validate the source org, since
385 an organization's disable date can be obtained from ood view.
386 select 'x' into temp
387 from MTL_PARAMETERS
388 where ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID;
389 */
390 select 'x' into temp
391 from org_organization_definitions
392 where ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
393 and nvl(disable_date,sysdate+1) > sysdate;
394 exception
395 when NO_DATA_FOUND then
396 dumm_status := INVPUOPI.mtl_log_interface_err(
397 cr.organization_id,
398 user_id,
399 login_id,
400 prog_appid,
401 prog_id,
402 request_id,
403 cr.TRANSACTION_ID,
404 error_msg,
405 'SOURCE_ORGANIZATION_ID',
406 'MTL_SYSTEM_ITEMS_INTERFACE',
407 'INV_IOI_SOURCE_ORG_ID',
408 err_text);
409 if dumm_status < 0 then
410 raise LOGGING_ERR;
411 end if;
412 status := 1;
413 end;
414 end if;
415
416 stmt := 39;
417 -- validate foreign keys
418 if cr.DEFAULT_SHIPPING_ORG is not null then
419 begin
420 select 'x' into temp
421 from MTL_PARAMETERS
422 where ORGANIZATION_ID = cr.DEFAULT_SHIPPING_ORG;
423 exception
424 when NO_DATA_FOUND then
425 dumm_status := INVPUOPI.mtl_log_interface_err(
426 cr.organization_id,
427 user_id,
428 login_id,
429 prog_appid,
430 prog_id,
431 request_id,
432 cr.TRANSACTION_ID,
433 error_msg,
434 'DEFAULT_SHIPPING_ORG',
435 'MTL_SYSTEM_ITEMS_INTERFACE',
436 'INV_IOI_DEF_SHIPPING_ORG',
437 err_text);
438 if dumm_status < 0 then
439 raise LOGGING_ERR;
440 end if;
441 status := 1;
442 end;
443 end if;
444
445 stmt := 40;
446 -- validate foreign keys
447 --Bug: 5032896. Modified the query below to include all Accounting Rules and exclude Invoicing Rules
448 if cr.ACCOUNTING_RULE_ID is not null then
449 begin
450 select 'x' into temp
451 from RA_RULES
452 where RULE_ID = cr.ACCOUNTING_RULE_ID
453 and TYPE not in ('I')
454 and STATUS = 'A';
455 exception
456 when NO_DATA_FOUND then
457 dumm_status := INVPUOPI.mtl_log_interface_err(
458 cr.organization_id,
459 user_id,
460 login_id,
461 prog_appid,
462 prog_id,
463 request_id,
464 cr.TRANSACTION_ID,
465 error_msg,
466 'ACCOUNTING_RULE_ID',
467 'MTL_SYSTEM_ITEMS_INTERFACE',
468 'INV_IOI_ACCT_RULE_ID',
469 err_text);
470 if dumm_status < 0 then
471 raise LOGGING_ERR;
472 end if;
473 status := 1;
474 end;
475 end if;
476
477 IF l_inv_debug_level IN(101, 102) THEN
478 INVPUTLI.info('INVPVDR6: Validating foreign keys set 4');
479 END IF;
480
481 stmt := 41;
482 -- validate foreign keys
483 if cr.INVOICING_RULE_ID is not null then
484 begin
485 select 'x' into temp
486 from RA_RULES
487 where RULE_ID = cr.INVOICING_RULE_ID;
488 exception
489 when NO_DATA_FOUND then
490 dumm_status := INVPUOPI.mtl_log_interface_err(
491 cr.organization_id,
492 user_id,
493 login_id,
494 prog_appid,
495 prog_id,
496 request_id,
497 cr.TRANSACTION_ID,
498 error_msg,
499 'INVOICING_RULE_ID',
500 'MTL_SYSTEM_ITEMS_INTERFACE',
501 'INV_IOI_INVOICING_RULE_ID',
502 err_text);
503 if dumm_status < 0 then
504 raise LOGGING_ERR;
505 end if;
506 status := 1;
507 end;
508 end if;
509
510 stmt := 43;
511 -- validate foreign keys
512 if cr.PLANNER_CODE is not null then
513 begin
514 select 'x' into temp
515 from MTL_PLANNERS
516 where PLANNER_CODE = cr.PLANNER_CODE
517 and ORGANIZATION_ID = cr.ORGANIZATION_ID
518 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
519 exception
520 when NO_DATA_FOUND then
521 dumm_status := INVPUOPI.mtl_log_interface_err(
522 cr.organization_id,
523 user_id,
524 login_id,
525 prog_appid,
526 prog_id,
527 request_id,
528 cr.TRANSACTION_ID,
529 error_msg,
530 'PLANNER_CODE',
531 'MTL_SYSTEM_ITEMS_INTERFACE',
532 'INV_IOI_PLANNER_CODE',
533 err_text);
534 if dumm_status < 0 then
535 raise LOGGING_ERR;
536 end if;
537 status := 1;
538 end;
539 end if;
540
541 --Added for 11.5.10 CTO
542 stmt := 44;
543 --* Added for Bug #4457440
544 if cr.START_DATE_ACTIVE is not null then
545 dumm_status := INVPUOPI.mtl_log_interface_err(
546 cr.organization_id,
547 user_id,
548 login_id,
549 prog_appid,
550 prog_id,
551 request_id,
552 cr.TRANSACTION_ID,
553 error_msg,
554 'START_DATE_ACTIVE',
555 'MTL_SYSTEM_ITEMS_INTERFACE',
556 'INV_START_DATE_END_DATE_WARN',
557 err_text);
558 if dumm_status < 0 then
559 raise LOGGING_ERR;
560 end if;
561 end if;
562
563 if cr.END_DATE_ACTIVE is not null then
564 dumm_status := INVPUOPI.mtl_log_interface_err(
565 cr.organization_id,
566 user_id,
567 login_id,
568 prog_appid,
569 prog_id,
570 request_id,
571 cr.TRANSACTION_ID,
572 error_msg,
573 'END_DATE_ACTIVE',
574 'MTL_SYSTEM_ITEMS_INTERFACE',
575 'INV_START_DATE_END_DATE_WARN',
576 err_text);
577 if dumm_status < 0 then
578 raise LOGGING_ERR;
579 end if;
580 end if;
581 --* End of Bug #4457440
582
583 -- validate foreign keys
584 if (cr.CONFIG_ORGS is not null) then
585 --3762750: Using cursor call to avoid multiple parsing
586 temp := null;
587 OPEN c_fndlookup_exists('INV_CONFIG_ORGS_TYPE',cr.CONFIG_ORGS);
588 FETCH c_fndlookup_exists INTO temp;
589 CLOSE c_fndlookup_exists;
590 IF temp IS NULL THEN
591 dumm_status := INVPUOPI.mtl_log_interface_err(
592 cr.organization_id,
593 user_id,
594 login_id,
595 prog_appid,
596 prog_id,
597 request_id,
598 cr.TRANSACTION_ID,
599 error_msg,
600 'CONFIG_ORGS',
601 'MTL_SYSTEM_ITEMS_INTERFACE',
602 'INV_INVALID_ATTR_COL_VALUE',
603 err_text);
604 if dumm_status < 0 then
605 raise LOGGING_ERR;
606 end if;
607 status := 1;
608 end if;
609 end if;
610
611 if (cr.CONFIG_MATCH is not null) then
612 --3762750: Using cursor call to avoid multiple parsing
613 temp := null;
614 OPEN c_fndlookup_exists('INV_CONFIG_MATCH_TYPE',cr.CONFIG_MATCH);
615 FETCH c_fndlookup_exists INTO temp;
616 CLOSE c_fndlookup_exists;
617 IF temp IS NULL THEN
618 dumm_status := INVPUOPI.mtl_log_interface_err(
619 cr.organization_id,
620 user_id,
621 login_id,
622 prog_appid,
623 prog_id,
624 request_id,
625 cr.TRANSACTION_ID,
626 error_msg,
627 'CONFIG_MATCH',
628 'MTL_SYSTEM_ITEMS_INTERFACE',
629 'INV_INVALID_ATTR_COL_VALUE',
630 err_text);
631 if dumm_status < 0 then
632 raise LOGGING_ERR;
633 end if;
634 status := 1;
635 end if;
636 end if;
637
638 stmt := 45;
639 -- validate foreign keys
640 if (cr.CONFIG_ORGS is not null OR cr.CONFIG_MATCH is not null) then
641 if NOT (cr.BOM_ITEM_TYPE = 1 and NVL(cr.PICK_COMPONENTS_FLAG,'N') = 'N') then
642 dumm_status := INVPUOPI.mtl_log_interface_err(
643 cr.organization_id,
644 user_id,
645 login_id,
646 prog_appid,
647 prog_id,
648 request_id,
649 cr.TRANSACTION_ID,
650 error_msg,
651 'CONFIG_ORGS_OR_CONFIG_MATCH',
652 'MTL_SYSTEM_ITEMS_INTERFACE',
653 'INV_CONFIG_ORG_MATCH',
654 err_text);
655 if dumm_status < 0 then
656 raise LOGGING_ERR;
657 end if;
658 status := 1;
659 end if;
660 end if;
661
662 --Start : 3436146 WIP Supply Type must be Phantom when BOM Item Type is Option Class.
663 if (cr.BOM_ITEM_TYPE = 2 and cr.WIP_SUPPLY_TYPE <> 6) then
664 dumm_status := INVPUOPI.mtl_log_interface_err(
665 cr.organization_id,
666 user_id,
667 login_id,
668 prog_appid,
669 prog_id,
670 request_id,
671 cr.TRANSACTION_ID,
672 error_msg,
673 'BOM_ITEM_TYPE',
674 'MTL_SYSTEM_ITEMS_INTERFACE',
675 'INV_ITEM_OPTION_PHANTOM',
676 err_text);
677 if dumm_status < 0 then
678 raise LOGGING_ERR;
679 end if;
680 status := 1;
681 end if;
682 --End : 3436146 WIP Supply Type must be Phantom when BOM Item Type is Option Class.
683
684 stmt := 47;
685 if (cr.INVENTORY_PLANNING_CODE is not null) then
686 --3762750: Using cursor call to avoid multiple parsing
687 temp := null;
688 OPEN c_fndlookup_exists('MTL_MATERIAL_PLANNING',cr.INVENTORY_PLANNING_CODE);
689 FETCH c_fndlookup_exists INTO temp;
690 CLOSE c_fndlookup_exists;
691 IF temp IS NULL 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 'INVENTORY_PLANNING_CODE',
702 'MTL_SYSTEM_ITEMS_INTERFACE',
703 'INV_INVALID_ATTR_COL_VALUE',
704 err_text);
705 if dumm_status < 0 then
706 raise LOGGING_ERR;
707 end if;
708 status := 1;
709 end if;
710 end if;
711
712 if (cr.INVENTORY_PLANNING_CODE = 7) then
713 begin
714 select 'x' into temp
715 from hr_organization_information
716 where org_information_context = 'Customer/Supplier Association'
717 and (org_information1 is not null or org_information3 is not null)
718 and organization_id = cr.organization_id;
719 exception
720 when NO_DATA_FOUND then
721 dumm_status := INVPUOPI.mtl_log_interface_err(
722 cr.organization_id,
723 user_id,
724 login_id,
725 prog_appid,
726 prog_id,
727 request_id,
728 cr.TRANSACTION_ID,
729 error_msg,
730 'INVENTORY_PLANNING_CODE',
731 'MTL_SYSTEM_ITEMS_INTERFACE',
732 'MTL_MSI_GP_INV_PLAN_CODE',
733 err_text);
734 if dumm_status < 0 then
735 raise LOGGING_ERR;
736 end if;
737 status := 1;
738 end;
739 end if;
740
741 /* Adding validation to throw an error when Min-Max quantities are set to NOT NULL with
742 Inventory Planning Code NOT Min-Max - Bug 5478211 */
743 --Bug: 5478211 Error will only be thrown when Inventory Planning Code is Vendor Managed
744
745 if ( cr.inventory_planning_code = 7 AND
746 (cr.MIN_MINMAX_QUANTITY IS NOT NULL OR
747 cr.MAX_MINMAX_QUANTITY IS NOT NULL OR
748 cr.MINIMUM_ORDER_QUANTITY IS NOT NULL OR
749 cr.MAXIMUM_ORDER_QUANTITY IS NOT NULL)) then
750 dumm_status := INVPUOPI.mtl_log_interface_err(
751 cr.organization_id,
752 user_id,
753 login_id,
754 prog_appid,
755 prog_id,
756 request_id,
757 cr.TRANSACTION_ID,
758 error_msg,
759 'INVENTORY_PLANNING_CODE',
760 'MTL_SYSTEM_ITEMS_INTERFACE',
761 'INV_MINMAX_QUANTITIES',
762 err_text);
763 if dumm_status < 0 then
764 raise LOGGING_ERR;
765 end if;
766 status := 1;
767 end if;
768
769 -- validate foreign keys
770 if ( cr.vmi_minimum_units is not null) then
771 begin
772 if floor(abs(cr.vmi_minimum_units)) - abs(cr.vmi_minimum_units ) <> 0 then
773 raise no_data_found;
774 end if;
775 if cr.vmi_minimum_units <= 0 then
776 raise too_many_rows;
777 end if;
778 exception
779 when NO_DATA_FOUND then
780 dumm_status := INVPUOPI.mtl_log_interface_err(
781 cr.organization_id,
782 user_id,
783 login_id,
784 prog_appid,
785 prog_id,
786 request_id,
787 cr.TRANSACTION_ID,
788 error_msg,
789 'VMI_MINIMUM_UNITS',
790 'MTL_SYSTEM_ITEMS_INTERFACE',
791 'INV_VMI_INTEGER_VALUE',
792 err_text);
793 if dumm_status < 0 then
794 raise LOGGING_ERR;
795 end if;
796 status := 1;
797 when TOO_MANY_ROWS then
798 dumm_status := INVPUOPI.mtl_log_interface_err(
799 cr.organization_id,
800 user_id,
801 login_id,
802 prog_appid,
803 prog_id,
804 request_id,
805 cr.TRANSACTION_ID,
806 error_msg,
807 'VMI_MINIMUM_UNITS',
808 'MTL_SYSTEM_ITEMS_INTERFACE',
809 'INV_VMI_GREATER_THAN_ZERO',
810 err_text);
811 if dumm_status < 0 then
812 raise LOGGING_ERR;
813 end if;
814 status := 1;
815 end;
816 end if;
817
818 -- validate foreign keys
819 if ( cr.vmi_minimum_days is not null) then
820 begin
821 if floor(abs(cr.vmi_minimum_days)) - abs(cr.vmi_minimum_days ) <> 0 then
822 raise no_data_found;
823 end if;
824 if cr.vmi_minimum_days <= 0 then
825 raise too_many_rows;
826 end if;
827 exception
828 when NO_DATA_FOUND then
829 dumm_status := INVPUOPI.mtl_log_interface_err(
830 cr.organization_id,
831 user_id,
832 login_id,
833 prog_appid,
834 prog_id,
835 request_id,
836 cr.TRANSACTION_ID,
837 error_msg,
838 'VMI_MINIMUM_DAYS',
839 'MTL_SYSTEM_ITEMS_INTERFACE',
840 'INV_VMI_INTEGER_VALUE',
841 err_text);
842 if dumm_status < 0 then
843 raise LOGGING_ERR;
844 end if;
845 status := 1;
846 when TOO_MANY_ROWS then
847 dumm_status := INVPUOPI.mtl_log_interface_err(
848 cr.organization_id,
849 user_id,
850 login_id,
851 prog_appid,
852 prog_id,
853 request_id,
854 cr.TRANSACTION_ID,
855 error_msg,
856 'VMI_MINIMUM_DAYS',
857 'MTL_SYSTEM_ITEMS_INTERFACE',
858 'INV_VMI_GREATER_THAN_ZERO',
859 err_text);
860 if dumm_status < 0 then
861 raise LOGGING_ERR;
862 end if;
863 status := 1;
864 end;
865 end if;
866
867 -- validate foreign keys
868 if ( cr.vmi_maximum_units is not null) then
869 begin
870 if floor(abs(cr.vmi_maximum_units)) - abs(cr.vmi_maximum_units ) <> 0 then
871 raise no_data_found;
872 end if;
873 if cr.vmi_maximum_units <= 0 then
874 raise too_many_rows;
875 end if;
876 exception
877 when NO_DATA_FOUND then
878 dumm_status := INVPUOPI.mtl_log_interface_err(
879 cr.organization_id,
880 user_id,
881 login_id,
882 prog_appid,
883 prog_id,
884 request_id,
885 cr.TRANSACTION_ID,
886 error_msg,
887 'VMI_MAXIMUM_UNITS',
888 'MTL_SYSTEM_ITEMS_INTERFACE',
889 'INV_VMI_INTEGER_VALUE',
890 err_text);
891 if dumm_status < 0 then
892 raise LOGGING_ERR;
893 end if;
894 status := 1;
895 when TOO_MANY_ROWS then
896 dumm_status := INVPUOPI.mtl_log_interface_err(
897 cr.organization_id,
898 user_id,
899 login_id,
900 prog_appid,
901 prog_id,
902 request_id,
903 cr.TRANSACTION_ID,
904 error_msg,
905 'VMI_MAXIMUM_UNITS',
906 'MTL_SYSTEM_ITEMS_INTERFACE',
907 'INV_VMI_GREATER_THAN_ZERO',
908 err_text);
909 if dumm_status < 0 then
910 raise LOGGING_ERR;
911 end if;
912 status := 1;
913 end;
914 end if;
915
916 -- validate foreign keys
917 if ( cr.vmi_maximum_days is not null) then
918 begin
919 if floor(abs(cr.vmi_maximum_days)) - abs(cr.vmi_maximum_days ) <> 0 then
920 raise no_data_found;
921 end if;
922 if cr.vmi_maximum_days <= 0 then
923 raise too_many_rows;
924 end if;
925 exception
926 when NO_DATA_FOUND then
927 dumm_status := INVPUOPI.mtl_log_interface_err(
928 cr.organization_id,
929 user_id,
930 login_id,
931 prog_appid,
932 prog_id,
933 request_id,
934 cr.TRANSACTION_ID,
935 error_msg,
936 'VMI_MAXIMUM_DAYS',
937 'MTL_SYSTEM_ITEMS_INTERFACE',
938 'INV_VMI_INTEGER_VALUE',
939 err_text);
940 if dumm_status < 0 then
941 raise LOGGING_ERR;
942 end if;
943 status := 1;
944 when TOO_MANY_ROWS then
945 dumm_status := INVPUOPI.mtl_log_interface_err(
946 cr.organization_id,
947 user_id,
948 login_id,
949 prog_appid,
950 prog_id,
951 request_id,
952 cr.TRANSACTION_ID,
953 error_msg,
954 'VMI_MAXIMUM_DAYS',
955 'MTL_SYSTEM_ITEMS_INTERFACE',
956 'INV_VMI_GREATER_THAN_ZERO',
957 err_text);
958 if dumm_status < 0 then
959 raise LOGGING_ERR;
960 end if;
961 status := 1;
962 end;
963 end if;
964
965 -- validate foreign keys
966 if ( cr.vmi_fixed_order_quantity is not null) then
967 begin
968 if floor(abs(cr.vmi_fixed_order_quantity)) - abs(cr.vmi_fixed_order_quantity ) <> 0 then
969 raise no_data_found;
970 end if;
971 if cr.vmi_fixed_order_quantity <= 0 then
972 raise too_many_rows;
973 end if;
974 exception
975 when NO_DATA_FOUND 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 'VMI_FIXED_ORDER_QUANTITY',
986 'MTL_SYSTEM_ITEMS_INTERFACE',
987 'INV_VMI_INTEGER_VALUE',
988 err_text);
989 if dumm_status < 0 then
990 raise LOGGING_ERR;
991 end if;
992 status := 1;
993 when TOO_MANY_ROWS then
994 dumm_status := INVPUOPI.mtl_log_interface_err(
995 cr.organization_id,
996 user_id,
997 login_id,
998 prog_appid,
999 prog_id,
1000 request_id,
1001 cr.TRANSACTION_ID,
1002 error_msg,
1003 'VMI_FIXED_ORDER_QUANTITY',
1004 'MTL_SYSTEM_ITEMS_INTERFACE',
1005 'INV_VMI_GREATER_THAN_ZERO',
1006 err_text);
1007 if dumm_status < 0 then
1008 raise LOGGING_ERR;
1009 end if;
1010 status := 1;
1011 end;
1012 end if;
1013
1014 if ( cr.vmi_minimum_units is not null)
1015 and ( cr.vmi_minimum_days is not null) then
1016 dumm_status := INVPUOPI.mtl_log_interface_err(
1017 cr.organization_id,
1018 user_id,
1019 login_id,
1020 prog_appid,
1021 prog_id,
1022 request_id,
1023 cr.TRANSACTION_ID,
1024 error_msg,
1025 'VMI_MINIMUM_UNITS',
1026 'MTL_SYSTEM_ITEMS_INTERFACE',
1027 'INV_MIN_UNITS_DAYS_VALUE',
1028 err_text);
1029 if dumm_status < 0 then
1030 raise LOGGING_ERR;
1031 end if;
1032 status := 1;
1033 end if;
1034
1035 if ( cr.vmi_maximum_units is not null)
1036 and ( cr.vmi_fixed_order_quantity is not null) then
1037 dumm_status := INVPUOPI.mtl_log_interface_err(
1038 cr.organization_id,
1039 user_id,
1040 login_id,
1041 prog_appid,
1042 prog_id,
1043 request_id,
1044 cr.TRANSACTION_ID,
1045 error_msg,
1046 'VMI_MAXIMUM_UNITS',
1047 'MTL_SYSTEM_ITEMS_INTERFACE',
1048 'INV_MAX_UNITS_DAYS_VALUE',
1049 err_text);
1050 if dumm_status < 0 then
1051 raise LOGGING_ERR;
1052 end if;
1053 status := 1;
1054 end if;
1055
1056 if ( cr.vmi_maximum_days is not null)
1057 and ( cr.vmi_fixed_order_quantity is not null) then
1058 dumm_status := INVPUOPI.mtl_log_interface_err(
1059 cr.organization_id,
1060 user_id,
1061 login_id,
1062 prog_appid,
1063 prog_id,
1064 request_id,
1065 cr.TRANSACTION_ID,
1066 error_msg,
1067 'VMI_MAXIMUM_DAYS',
1068 'MTL_SYSTEM_ITEMS_INTERFACE',
1069 'INV_MAX_UNITS_DAYS_VALUE',
1070 err_text);
1071 if dumm_status < 0 then
1072 raise LOGGING_ERR;
1073 end if;
1074 status := 1;
1075 end if;
1076
1077 if ( cr.vmi_maximum_days is not null)
1078 and ( cr.vmi_maximum_units is not null) then
1079 dumm_status := INVPUOPI.mtl_log_interface_err(
1080 cr.organization_id,
1081 user_id,
1082 login_id,
1083 prog_appid,
1084 prog_id,
1085 request_id,
1086 cr.TRANSACTION_ID,
1087 error_msg,
1088 'VMI_MAXIMUM_UNITS',
1089 'MTL_SYSTEM_ITEMS_INTERFACE',
1090 'INV_MAX_DAYS_UNIT_VALUE',
1091 err_text);
1092 if dumm_status < 0 then
1093 raise LOGGING_ERR;
1094 end if;
1095 status := 1;
1096 end if;
1097
1098 if ( cr.vmi_minimum_units is not null)
1099 and ( cr.vmi_maximum_days is not null) then
1100 dumm_status := INVPUOPI.mtl_log_interface_err(
1101 cr.organization_id,
1102 user_id,
1103 login_id,
1104 prog_appid,
1105 prog_id,
1106 request_id,
1107 cr.TRANSACTION_ID,
1108 error_msg,
1109 'VMI_MAXIMUM_DAYS',
1110 'MTL_SYSTEM_ITEMS_INTERFACE',
1111 'INV_MIN_MAX_UNITS_DAYS_VALUE',
1112 err_text);
1113 if dumm_status < 0 then
1114 raise LOGGING_ERR;
1115 end if;
1116 status := 1;
1117 end if;
1118
1119 if ( cr.vmi_minimum_days is not null)
1120 and ( cr.vmi_maximum_units is not null) then
1121 dumm_status := INVPUOPI.mtl_log_interface_err(
1122 cr.organization_id,
1123 user_id,
1124 login_id,
1125 prog_appid,
1126 prog_id,
1127 request_id,
1128 cr.TRANSACTION_ID,
1129 error_msg,
1130 'VMI_MINIMUM_DAYS',
1131 'MTL_SYSTEM_ITEMS_INTERFACE',
1132 'INV_MIN_MAX_UNITS_DAYS_VALUE',
1133 err_text);
1134 if dumm_status < 0 then
1135 raise LOGGING_ERR;
1136 end if;
1137 status := 1;
1138 end if;
1139
1140 if ( (nvl(cr.vmi_fixed_order_quantity,0) = 0 )
1141 and (cr.vmi_minimum_units > nvl(cr.vmi_maximum_units,0)
1142 and (cr.vmi_minimum_units is not null and cr.vmi_maximum_units is not null))) then
1143 dumm_status := INVPUOPI.mtl_log_interface_err(
1144 cr.organization_id,
1145 user_id,
1146 login_id,
1147 prog_appid,
1148 prog_id,
1149 request_id,
1150 cr.TRANSACTION_ID,
1151 error_msg,
1152 'VMI_MINIMUM_UNITS',
1153 'MTL_SYSTEM_ITEMS_INTERFACE',
1154 'INV_MIN_MAX_QTY_VALUE',
1155 err_text);
1156 if dumm_status < 0 then
1157 raise LOGGING_ERR;
1158 end if;
1159 status := 1;
1160 end if;
1161
1162 if nvl(cr.vmi_fixed_order_quantity,0) = 0
1163 and cr.vmi_minimum_days > nvl(cr.vmi_maximum_days,0)
1164 and (cr.vmi_minimum_days is not null and cr.vmi_maximum_days is not null) then
1165 dumm_status := INVPUOPI.mtl_log_interface_err(
1166 cr.organization_id,
1167 user_id,
1168 login_id,
1169 prog_appid,
1170 prog_id,
1171 request_id,
1172 cr.TRANSACTION_ID,
1173 error_msg,
1174 'VMI_MINIMUM_DAYS',
1175 'MTL_SYSTEM_ITEMS_INTERFACE',
1176 'INV_MIN_DAYS_MAX_DAYS_VALUE',
1177 err_text);
1178 if dumm_status < 0 then
1179 raise LOGGING_ERR;
1180 end if;
1181 status := 1;
1182 end if;
1183
1184 -- validate foreign keys
1185 if ( cr.forecast_horizon is not null) then
1186 begin
1187 if floor(abs(cr.forecast_horizon)) - abs(cr.forecast_horizon ) <> 0 then
1188 raise no_data_found;
1189 end if;
1190 if cr.forecast_horizon < 0 then
1191 raise too_many_rows;
1192 end if;
1193 exception
1194 when NO_DATA_FOUND then
1195 dumm_status := INVPUOPI.mtl_log_interface_err(
1196 cr.organization_id,
1197 user_id,
1198 login_id,
1199 prog_appid,
1200 prog_id,
1201 request_id,
1202 cr.TRANSACTION_ID,
1203 error_msg,
1204 'FORECAST_HORIZON',
1205 'MTL_SYSTEM_ITEMS_INTERFACE',
1206 'INV_VMI_INTEGER_VALUE',
1207 err_text);
1208 if dumm_status < 0 then
1209 raise LOGGING_ERR;
1210 end if;
1211 status := 1;
1212 when TOO_MANY_ROWS then
1213 dumm_status := INVPUOPI.mtl_log_interface_err(
1214 cr.organization_id,
1215 user_id,
1216 login_id,
1217 prog_appid,
1218 prog_id,
1219 request_id,
1220 cr.TRANSACTION_ID,
1221 error_msg,
1222 'FORECAST_HORIZON',
1223 'MTL_SYSTEM_ITEMS_INTERFACE',
1224 'INV_VMI_GREATER_THAN_ZERO',
1225 err_text);
1226 if dumm_status < 0 then
1227 raise LOGGING_ERR;
1228 end if;
1229 status := 1;
1230 end;
1231 end if;
1232
1233 -- validate foreign keys
1234 if ( cr.days_tgt_inv_supply is not null) then
1235 begin
1236 if floor(abs(cr.days_tgt_inv_supply)) - abs(cr.days_tgt_inv_supply ) <> 0 then
1237 raise no_data_found;
1238 end if;
1239 if cr.days_tgt_inv_supply < 0 then
1240 raise too_many_rows;
1241 end if;
1242 exception
1243 when NO_DATA_FOUND then
1244 dumm_status := INVPUOPI.mtl_log_interface_err(
1245 cr.organization_id,
1246 user_id,
1247 login_id,
1248 prog_appid,
1249 prog_id,
1250 request_id,
1251 cr.TRANSACTION_ID,
1252 error_msg,
1253 'DAYS_TGT_INV_SUPPLY',
1254 'MTL_SYSTEM_ITEMS_INTERFACE',
1255 'INV_VMI_INTEGER_VALUE',
1256 err_text);
1257 if dumm_status < 0 then
1258 raise LOGGING_ERR;
1259 end if;
1260 status := 1;
1261 when TOO_MANY_ROWS then
1262 dumm_status := INVPUOPI.mtl_log_interface_err(
1263 cr.organization_id,
1264 user_id,
1265 login_id,
1266 prog_appid,
1267 prog_id,
1268 request_id,
1269 cr.TRANSACTION_ID,
1270 error_msg,
1271 'DAYS_TGT_INV_SUPPLY',
1272 'MTL_SYSTEM_ITEMS_INTERFACE',
1273 'INV_VMI_GREATER_THAN_ZERO',
1274 err_text);
1275 if dumm_status < 0 then
1276 raise LOGGING_ERR;
1277 end if;
1278 status := 1;
1279 end;
1280 end if;
1281
1282 -- validate foreign keys
1283 if ( cr.days_tgt_inv_window is not null) then
1284 begin
1285 if floor(abs(cr.days_tgt_inv_window)) - abs(cr.days_tgt_inv_window ) <> 0 then
1286 raise no_data_found;
1287 end if;
1288 if cr.days_tgt_inv_window < 0 then
1289 raise too_many_rows;
1290 end if;
1291 exception
1292 when NO_DATA_FOUND then
1293 dumm_status := INVPUOPI.mtl_log_interface_err(
1294 cr.organization_id,
1295 user_id,
1296 login_id,
1297 prog_appid,
1298 prog_id,
1299 request_id,
1300 cr.TRANSACTION_ID,
1301 error_msg,
1302 'DAYS_TGT_INV_WINDOW',
1303 'MTL_SYSTEM_ITEMS_INTERFACE',
1304 'INV_VMI_INTEGER_VALUE',
1305 err_text);
1306 if dumm_status < 0 then
1307 raise LOGGING_ERR;
1308 end if;
1309 status := 1;
1310 when TOO_MANY_ROWS then
1311 dumm_status := INVPUOPI.mtl_log_interface_err(
1312 cr.organization_id,
1313 user_id,
1314 login_id,
1315 prog_appid,
1316 prog_id,
1317 request_id,
1318 cr.TRANSACTION_ID,
1319 error_msg,
1320 'DAYS_TGT_INV_WINDOW',
1321 'MTL_SYSTEM_ITEMS_INTERFACE',
1322 'INV_VMI_GREATER_THAN_ZERO',
1323 err_text);
1324 if dumm_status < 0 then
1325 raise LOGGING_ERR;
1326 end if;
1327 status := 1;
1328 end;
1329 end if;
1330
1331 -- validate foreign keys
1332 if ( cr.days_max_inv_window is not null) then
1333 begin
1334 if floor(abs(cr.days_max_inv_window)) - abs(cr.days_max_inv_window ) <> 0 then
1335 raise no_data_found;
1336 end if;
1337 if cr.days_max_inv_window < 0 then
1338 raise too_many_rows;
1339 end if;
1340 exception
1341 when NO_DATA_FOUND then
1342 dumm_status := INVPUOPI.mtl_log_interface_err(
1343 cr.organization_id,
1344 user_id,
1345 login_id,
1346 prog_appid,
1347 prog_id,
1348 request_id,
1349 cr.TRANSACTION_ID,
1350 error_msg,
1351 'DAYS_MAX_INV_WINDOW',
1352 'MTL_SYSTEM_ITEMS_INTERFACE',
1353 'INV_VMI_INTEGER_VALUE',
1354 err_text);
1355 if dumm_status < 0 then
1356 raise LOGGING_ERR;
1357 end if;
1358 status := 1;
1359 when TOO_MANY_ROWS then
1360 dumm_status := INVPUOPI.mtl_log_interface_err(
1361 cr.organization_id,
1362 user_id,
1363 login_id,
1364 prog_appid,
1365 prog_id,
1366 request_id,
1367 cr.TRANSACTION_ID,
1368 error_msg,
1369 'DAYS_MAX_INV_WINDOW',
1370 'MTL_SYSTEM_ITEMS_INTERFACE',
1371 'INV_VMI_GREATER_THAN_ZERO',
1372 err_text);
1373 if dumm_status < 0 then
1374 raise LOGGING_ERR;
1375 end if;
1376 status := 1;
1377 end;
1378 end if;
1379
1380 -- validate foreign keys
1381 if ( cr.days_max_inv_supply is not null) then
1382 begin
1383 if floor(abs(cr.days_max_inv_supply)) - abs(cr.days_max_inv_supply ) <> 0 then
1384 raise no_data_found;
1385 end if;
1386 if cr.days_max_inv_supply < 0 then
1387 raise too_many_rows;
1388 end if;
1389 exception
1390 when NO_DATA_FOUND then
1391 dumm_status := INVPUOPI.mtl_log_interface_err(
1392 cr.organization_id,
1393 user_id,
1394 login_id,
1395 prog_appid,
1396 prog_id,
1397 request_id,
1398 cr.TRANSACTION_ID,
1399 error_msg,
1400 'DAYS_MAX_INV_SUPPLY',
1401 'MTL_SYSTEM_ITEMS_INTERFACE',
1402 'INV_VMI_INTEGER_VALUE',
1403 err_text);
1404 if dumm_status < 0 then
1405 raise LOGGING_ERR;
1406 end if;
1407 status := 1;
1408 when TOO_MANY_ROWS then
1409 dumm_status := INVPUOPI.mtl_log_interface_err(
1410 cr.organization_id,
1411 user_id,
1412 login_id,
1413 prog_appid,
1414 prog_id,
1415 request_id,
1416 cr.TRANSACTION_ID,
1417 error_msg,
1418 'DAYS_MAX_INV_SUPPLY',
1419 'MTL_SYSTEM_ITEMS_INTERFACE',
1420 'INV_VMI_GREATER_THAN_ZERO',
1421 err_text);
1422 if dumm_status < 0 then
1423 raise LOGGING_ERR;
1424 end if;
1425 status := 1;
1426 end;
1427 end if;
1428
1429
1430 -- validate foreign keys
1431 if ( cr.consigned_flag is not null) then
1432
1433 -- 3762750: Using cursor call to avoid multiple parsing
1434 temp := null;
1435 OPEN c_mfglookup_exists('SYS_YES_NO',CR.CONSIGNED_FLAG);
1436 FETCH c_mfglookup_exists INTO temp;
1437 CLOSE c_mfglookup_exists;
1438
1439 IF (temp IS NULL) THEN
1440 dumm_status := INVPUOPI.mtl_log_interface_err(
1441 cr.organization_id,
1442 user_id,
1443 login_id,
1444 prog_appid,
1445 prog_id,
1446 request_id,
1447 cr.TRANSACTION_ID,
1448 error_msg,
1449 'CONSIGNED_FLAG',
1450 'MTL_SYSTEM_ITEMS_INTERFACE',
1451 'INV_INVALID_ATTR_COL_VALUE',
1452 err_text);
1453 if dumm_status < 0 then
1454 raise LOGGING_ERR;
1455 end if;
1456 status := 1;
1457 END IF;
1458 end if;
1459
1460 -- validate foreign keys
1461 if ( cr.asn_autoexpire_flag is not null) then
1462
1463 -- 3762750: Using cursor call to avoid multiple parsing
1464 temp := null;
1465 OPEN c_mfglookup_exists('SYS_YES_NO',CR.ASN_AUTOEXPIRE_FLAG);
1466 FETCH c_mfglookup_exists INTO temp;
1467 CLOSE c_mfglookup_exists;
1468
1469 IF (temp IS NULL) THEN
1470 dumm_status := INVPUOPI.mtl_log_interface_err(
1471 cr.organization_id,
1472 user_id,
1473 login_id,
1474 prog_appid,
1475 prog_id,
1476 request_id,
1477 cr.TRANSACTION_ID,
1478 error_msg,
1479 'ASN_AUTOEXPIRE_FLAG',
1480 'MTL_SYSTEM_ITEMS_INTERFACE',
1481 'INV_INVALID_ATTR_COL_VALUE',
1482 err_text);
1483 if dumm_status < 0 then
1484 raise LOGGING_ERR;
1485 end if;
1486 status := 1;
1487 END IF;
1488 end if;
1489
1490 -- validate foreign keys
1491 if ( cr.exclude_from_budget_flag is not null) then
1492 -- 3762750: Using cursor call to avoid multiple parsing
1493 temp := null;
1494 OPEN c_mfglookup_exists('SYS_YES_NO',CR.EXCLUDE_FROM_BUDGET_FLAG);
1495 FETCH c_mfglookup_exists INTO temp;
1496 CLOSE c_mfglookup_exists;
1497
1498 IF (temp IS NULL) THEN
1499 dumm_status := INVPUOPI.mtl_log_interface_err(
1500 cr.organization_id,
1501 user_id,
1502 login_id,
1503 prog_appid,
1504 prog_id,
1505 request_id,
1506 cr.TRANSACTION_ID,
1507 error_msg,
1508 'EXCLUDE_FROM_BUDGET_FLAG',
1509 'MTL_SYSTEM_ITEMS_INTERFACE',
1510 'INV_INVALID_ATTR_COL_VALUE',
1511 err_text);
1512 if dumm_status < 0 then
1513 raise LOGGING_ERR;
1514 end if;
1515 status := 1;
1516 END IF;
1517 end if;
1518
1519 -- validate foreign keys
1520 if ( cr.drp_planned_flag is not null) then
1521 -- 3762750: Using cursor call to avoid multiple parsing
1522 temp := null;
1523 OPEN c_mfglookup_exists('SYS_YES_NO',CR.DRP_PLANNED_FLAG);
1524 FETCH c_mfglookup_exists INTO temp;
1525 CLOSE c_mfglookup_exists;
1526
1527 IF (temp IS NULL) THEN
1528 dumm_status := INVPUOPI.mtl_log_interface_err(
1529 cr.organization_id,
1530 user_id,
1531 login_id,
1532 prog_appid,
1533 prog_id,
1534 request_id,
1535 cr.TRANSACTION_ID,
1536 error_msg,
1537 'DRP_PLANNED_FLAG',
1538 'MTL_SYSTEM_ITEMS_INTERFACE',
1539 'INV_INVALID_ATTR_COL_VALUE',
1540 err_text);
1541 if dumm_status < 0 then
1542 raise LOGGING_ERR;
1543 end if;
1544 status := 1;
1545 END IF;
1546 end if;
1547
1548 -- validate foreign keys
1549 if ( cr.critical_component_flag is not null) then
1550 -- 3762750: Using cursor call to avoid multiple parsing
1551 temp := null;
1552 OPEN c_mfglookup_exists('SYS_YES_NO',CR.CRITICAL_COMPONENT_FLAG);
1553 FETCH c_mfglookup_exists INTO temp;
1554 CLOSE c_mfglookup_exists;
1555
1556 IF (temp IS NULL) THEN
1557 dumm_status := INVPUOPI.mtl_log_interface_err(
1558 cr.organization_id,
1559 user_id,
1560 login_id,
1561 prog_appid,
1562 prog_id,
1563 request_id,
1564 cr.TRANSACTION_ID,
1565 error_msg,
1566 'CRITICAL_COMPONENT_FLAG',
1567 'MTL_SYSTEM_ITEMS_INTERFACE',
1568 'INV_INVALID_ATTR_COL_VALUE',
1569 err_text);
1570 if dumm_status < 0 then
1571 raise LOGGING_ERR;
1572 end if;
1573 status := 1;
1574 END IF;
1575 end if;
1576
1577
1578 -- validate foreign keys
1579 if ( cr.so_authorization_flag is not null) then
1580 -- 3762750: Using cursor call to avoid multiple parsing
1581 temp := null;
1582 OPEN c_mfglookup_exists('MTL_MSI_GP_RELEASE_AUTH',CR.SO_AUTHORIZATION_FLAG);
1583 FETCH c_mfglookup_exists INTO temp;
1584 CLOSE c_mfglookup_exists;
1585
1586 IF (temp IS NULL) THEN
1587 dumm_status := INVPUOPI.mtl_log_interface_err(
1588 cr.organization_id,
1589 user_id,
1590 login_id,
1591 prog_appid,
1592 prog_id,
1593 request_id,
1594 cr.TRANSACTION_ID,
1595 error_msg,
1596 'SO_AUTHORIZATION_FLAG',
1597 'MTL_SYSTEM_ITEMS_INTERFACE',
1598 'INV_INVALID_ATTR_COL_VALUE',
1599 err_text);
1600 if dumm_status < 0 then
1601 raise LOGGING_ERR;
1602 end if;
1603 status := 1;
1604 END IF;
1605 end if;
1606 -- validate foreign keys
1607 if ( cr.vmi_forecast_type is not null) then
1608 -- 3762750: Using cursor call to avoid multiple parsing
1609 temp := null;
1610 OPEN c_mfglookup_exists('MTL_MSI_GP_FORECAST_TYPE',CR.VMI_FORECAST_TYPE);
1611 FETCH c_mfglookup_exists INTO temp;
1612 CLOSE c_mfglookup_exists;
1613
1614 IF (temp IS NULL) THEN
1615 dumm_status := INVPUOPI.mtl_log_interface_err(
1616 cr.organization_id,
1617 user_id,
1618 login_id,
1619 prog_appid,
1620 prog_id,
1621 request_id,
1622 cr.TRANSACTION_ID,
1623 error_msg,
1624 'VMI_FORECAST_TYPE',
1625 'MTL_SYSTEM_ITEMS_INTERFACE',
1626 'INV_INVALID_ATTR_COL_VALUE',
1627 err_text);
1628 if dumm_status < 0 then
1629 raise LOGGING_ERR;
1630 end if;
1631 status := 1;
1632 END IF;
1633 end if;
1634 -- validate foreign keys
1635 if ( cr.continous_transfer is not null) then
1636 -- 3762750: Using cursor call to avoid multiple parsing
1637 temp := null;
1638 OPEN c_mfglookup_exists('MTL_MSI_MRP_INT_ORG',CR.CONTINOUS_TRANSFER);
1639 FETCH c_mfglookup_exists INTO temp;
1640 CLOSE c_mfglookup_exists;
1641
1642 IF (temp IS NULL) THEN
1643 dumm_status := INVPUOPI.mtl_log_interface_err(
1644 cr.organization_id,
1645 user_id,
1646 login_id,
1647 prog_appid,
1648 prog_id,
1649 request_id,
1650 cr.TRANSACTION_ID,
1651 error_msg,
1652 'CONTINOUS_TRANSFER',
1653 'MTL_SYSTEM_ITEMS_INTERFACE',
1654 'INV_INVALID_ATTR_COL_VALUE',
1655 err_text);
1656 if dumm_status < 0 then
1657 raise LOGGING_ERR;
1658 end if;
1659 status := 1;
1660 END IF;
1661 end if;
1662 -- validate foreign keys
1663 if ( cr.convergence is not null) then
1664 -- 3762750: Using cursor call to avoid multiple parsing
1665 temp := null;
1666 OPEN c_mfglookup_exists('MTL_MSI_MRP_CONV_SUPP',CR.CONVERGENCE);
1667 FETCH c_mfglookup_exists INTO temp;
1668 CLOSE c_mfglookup_exists;
1669
1670 IF (temp IS NULL) THEN
1671 dumm_status := INVPUOPI.mtl_log_interface_err(
1672 cr.organization_id,
1673 user_id,
1674 login_id,
1675 prog_appid,
1676 prog_id,
1677 request_id,
1678 cr.TRANSACTION_ID,
1679 error_msg,
1680 'CONVERGENCE',
1681 'MTL_SYSTEM_ITEMS_INTERFACE',
1682 'INV_INVALID_ATTR_COL_VALUE',
1683 err_text);
1684 if dumm_status < 0 then
1685 raise LOGGING_ERR;
1686 end if;
1687 status := 1;
1688 END IF;
1689 end if;
1690 -- validate foreign keys
1691 if ( cr.divergence is not null) then
1692 -- 3762750: Using cursor call to avoid multiple parsing
1693 temp := null;
1694 OPEN c_mfglookup_exists('MTL_MSI_MRP_DIV_SUPP',CR.DIVERGENCE);
1695 FETCH c_mfglookup_exists INTO temp;
1696 CLOSE c_mfglookup_exists;
1697
1698 IF (temp IS NULL) THEN
1699 dumm_status := INVPUOPI.mtl_log_interface_err(
1700 cr.organization_id,
1701 user_id,
1702 login_id,
1703 prog_appid,
1704 prog_id,
1705 request_id,
1706 cr.TRANSACTION_ID,
1707 error_msg,
1708 'DIVERGENCE',
1709 'MTL_SYSTEM_ITEMS_INTERFACE',
1710 'INV_INVALID_ATTR_COL_VALUE',
1711 err_text);
1712 if dumm_status < 0 then
1713 raise LOGGING_ERR;
1714 end if;
1715 status := 1;
1716 END IF;
1717 end if;
1718
1719 --Start of bug fix: 3095347
1720 /* Fix for bug 5844510- Source Org/SubInv Should be Null
1721 when source_type is either null or Supplier(2).
1722 Changed the error msg too to indicate the same since source_type
1723 is the driving column and source org/SubInv are dependent on it.
1724 Prior to fix, error msg indicated that source_type has to corrected.*/
1725
1726 /* Bug #14272507, remove this validation,
1727 because customer expect to clear the dependent fields when source_type is either null or Supplier(2).
1728 instead of blocking by the validation. */
1729 /*
1730 if ( ( (cr.source_type is null) or (cr.source_type = 2) )
1731 and (cr.source_organization_id is not null
1732 or cr.source_subinventory is not null))
1733 then
1734 dumm_status := INVPUOPI.mtl_log_interface_err(
1735 cr.organization_id,
1736 user_id,
1737 login_id,
1738 prog_appid,
1739 prog_id,
1740 request_id,
1741 cr.TRANSACTION_ID,
1742 error_msg,
1743 'SOURCE_TYPE',
1744 'MTL_SYSTEM_ITEMS_INTERFACE',
1745 'INV_SOURCE_ORG_MUST_BE_NULL',
1746 err_text);
1747 if dumm_status < 0 then
1748 raise LOGGING_ERR;
1749 end if;
1750 status := 1;
1751 end if;
1752 */
1753
1754 if (cr.source_type is not null
1755 and cr.source_organization_id = nvl(org_id,cr.organization_id)
1756 and cr.mrp_planning_code = 3
1757 and cr.source_subinventory is null)
1758 then
1759 dumm_status := INVPUOPI.mtl_log_interface_err(
1760 cr.organization_id,
1761 user_id,
1762 login_id,
1763 prog_appid,
1764 prog_id,
1765 request_id,
1766 cr.TRANSACTION_ID,
1767 error_msg,
1768 'SOURCE_SUBINVENTORY',
1769 'MTL_SYSTEM_ITEMS_INTERFACE',
1770 'INV_IOI_SOURCE_SUBINV_REQUIRED',
1771 err_text);
1772 if dumm_status < 0 then
1773 raise LOGGING_ERR;
1774 end if;
1775 status := 1;
1776 end if;
1777
1778 if cr.source_type in (1,3)
1779 and cr.source_organization_id is not null
1780 then
1781 validate_source := INVIDIT1.validate_source_org(
1782 cr.organization_id,
1783 cr.inventory_item_id,
1784 cr.inventory_item_id,
1785 cr.source_organization_id,
1786 cr.mrp_planning_code,
1787 cr.source_subinventory);
1788 /* Fix for bug 5844510- For throwing INV_ITEM_IN_SOURCE_ORG error,
1789 compare validate_source with value 1. Prior to the fix, it was
1790 comparing with value 3 which is wrong.*/
1791 if (validate_source = 1
1792 and cr.source_organization_id <> cr.organization_id )
1793 then
1794 dumm_status := INVPUOPI.mtl_log_interface_err(
1795 cr.organization_id,
1796 user_id,
1797 login_id,
1798 prog_appid,
1799 prog_id,
1800 request_id,
1801 cr.TRANSACTION_ID,
1802 error_msg,
1803 'SOURCE_SUBINVENTORY',
1804 'MTL_SYSTEM_ITEMS_INTERFACE',
1805 'INV_ITEM_IN_SOURCE_ORG',
1806 err_text);
1807 if dumm_status < 0 then
1808 raise LOGGING_ERR;
1809 end if;
1810 status := 1;
1811 elsif ( validate_source = 3 ) then
1812 dumm_status := INVPUOPI.mtl_log_interface_err(
1813 cr.organization_id,
1814 user_id,
1815 login_id,
1816 prog_appid,
1817 prog_id,
1818 request_id,
1819 cr.TRANSACTION_ID,
1820 error_msg,
1821 'SOURCE_SUBINVENTORY',
1822 'MTL_SYSTEM_ITEMS_INTERFACE',
1823 'INV_INTERORG_NTWK',
1824 err_text);
1825 if dumm_status < 0 then
1826 raise LOGGING_ERR;
1827 end if;
1828 status := 1;
1829 end if;
1830 end if;
1831
1832 /* R12 Enhancement - Adding validation */
1833
1834 if cr.TAX_CODE is not null then
1835 IF l_inv_debug_level IN(101, 102) THEN
1836 INVPUTLI.info('INVPVDR6: Calling INVPVDR6 - Verifying lookup type for tax code ');
1837 END IF;
1838
1839 temp := null;
1840 OPEN c_tax_code_exists(cr.TAX_CODE,cr.organization_id);
1841 FETCH c_tax_code_exists INTO temp;
1842 CLOSE c_tax_code_exists;
1843
1844 IF temp IS NULL THEN
1845
1846 dumm_status := INVPUOPI.mtl_log_interface_err(
1847 cr.organization_id,
1848 user_id,
1849 login_id,
1850 prog_appid,
1851 prog_id,
1852 request_id,
1853 cr.TRANSACTION_ID,
1854 error_msg,
1855 'TAX_CODE',
1856 'MTL_SYSTEM_ITEMS_INTERFACE',
1857 'INV_IOI_TAX_CODE',
1858 err_text);
1859 if dumm_status < 0 then
1860 raise LOGGING_ERR;
1861 end if;
1862 status := 1;
1863
1864 END IF;
1865
1866 END IF;
1867
1868
1869 --End of bug fix: 3095347
1870 /* Bug: 5178297 Commenting this block to allow negative values for Lead Times
1871 --Start : 2990665 Lead Time Quantities cannot be negative.
1872 if (cr.PREPROCESSING_LEAD_TIME < 0
1873 or cr.FULL_LEAD_TIME < 0
1874 or cr.POSTPROCESSING_LEAD_TIME < 0
1875 or cr.FIXED_LEAD_TIME < 0
1876 or cr.VARIABLE_LEAD_TIME < 0
1877 or cr.CUM_MANUFACTURING_LEAD_TIME < 0
1878 or cr.CUMULATIVE_TOTAL_LEAD_TIME < 0
1879 or cr.LEAD_TIME_LOT_SIZE < 0)
1880 then
1881 dumm_status := INVPUOPI.mtl_log_interface_err(
1882 cr.organization_id,
1883 user_id,
1884 login_id,
1885 prog_appid,
1886 prog_id,
1887 request_id,
1888 cr.TRANSACTION_ID,
1889 error_msg,
1890 'LEAD_TIMES_QUANTITIES',
1891 'MTL_SYSTEM_ITEMS_INTERFACE',
1892 'INV_IOI_NEGATIVE_LEAD_TIME_QTY',
1893 err_text);
1894 if dumm_status < 0 then
1895 raise LOGGING_ERR;
1896 end if;
1897 status := 1;
1898 end if;
1899 --End : 2990665
1900 End Commenting Bug: 5178297*/
1901 --Bug:3309789 DRP planned items cannot be enabled with Assemble to Order
1902 if (cr.drp_planned_flag = 1
1903 and cr.replenish_to_order_flag = 'Y' )
1904 then
1905 dumm_status := INVPUOPI.mtl_log_interface_err(
1906 cr.organization_id,
1907 user_id,
1908 login_id,
1909 prog_appid,
1910 prog_id,
1911 request_id,
1912 cr.TRANSACTION_ID,
1913 error_msg,
1914 'DRP_PLANNED_FLAG',
1915 'MTL_SYSTEM_ITEMS_INTERFACE',
1916 'INV_DRP_CANNOT_BE_ATO',
1917 err_text);
1918 if dumm_status < 0 then
1919 raise LOGGING_ERR;
1920 end if;
1921 status := 1;
1922 end if;
1923
1924
1925 /* NP 09SEP94
1926 ** call INVPUOPI.validate_flags to validate the yes/no flags
1927 ** function validate_flags now resides in INVPUPI2 instead of INVPVALI
1928 ** NP 06MAY96: No need to add xset_id call since it processes with row_id
1929 ** which is absolutely unique.Moving this code above all beacause of perf reasons.
1930 3515652: Performance enhancements, now validate flag works on setid
1931
1932 IF l_inv_debug_level IN(101, 102) THEN
1933 INVPUTLI.info('INVPVDR6: Calling INVPUPI2.validate_flags');
1934 END IF;
1935 dumm_status := INVPUPI2.validate_flags (
1936 cr.rowid,
1937 prog_appid,
1938 prog_id,
1939 request_id,
1940 user_id,
1941 login_id,
1942 err_text);
1943
1944 if dumm_status < 0 then
1945 raise LOGGING_ERR;
1946 end if;
1947
1948 if dumm_status <> 0 then
1949 status := 1;
1950 end if;
1951 */
1952 /*
1953 ** call the costing package to validate the cost columns
1954 ** NP 06MAY96: No need to pass xset_id to CSTFVSUB procedure
1955 ** The transaction_id is passed and that is unique enough
1956 ** for all the updates that CSTFVSUB does to MSII.
1957 */
1958
1959 IF l_inv_debug_level IN(101, 102) THEN
1960 INVPUTLI.info('INVPVDR6: Calling INVPCOII.CSTFVSUB Costing package');
1961 END IF;
1962
1963
1964 /* INVPUTLI.info(cr.rowid|| ' ' ||user_id|| ' ' ||login_id||' ');
1965 ** INVPUTLI.info(request_id||' ' || prog_id||' '||prog_appid||' '|| err_text);
1966 */
1967 dumm_status := INVPCOII.CSTFVSUB(cr.transaction_id,
1968 cr.material_sub_elem,
1969 cr.material_oh_sub_elem,
1970 cr.organization_id,
1971 user_id,
1972 login_id,
1973 request_id,
1974 prog_id,
1975 prog_appid,
1976 err_text);
1977 if dumm_status < 0 then
1978 raise LOGGING_ERR;
1979 end if;
1980 if dumm_status <> 0 then
1981 status := 1;
1982 end if;
1983
1984 stmt := 48;
1985
1986 --Bug: 3028216 check of defautl categories
1987 IF l_inv_debug_level IN(101, 102) THEN
1988 INVPUTLI.info('INVPPROC.inproit_process_item: Checking Default categories before insert');
1989 END IF;
1990
1991 BEGIN
1992 l_logerr := 0;
1993
1994 --Start 6531903:Default catalog to be run only once
1995 IF cr.inventory_item_flag = 'Y' AND l_functional_area1 IS NOT NULL THEN
1996 l_logerr := l_functional_area1;
1997 ELSIF (cr.purchasing_item_flag = 'Y' OR cr.internal_order_flag ='Y') AND l_functional_area2 IS NOT NULL THEN
1998 l_logerr := l_functional_area2;
1999 ELSIF cr.mrp_planning_code = 6 AND l_functional_area3 IS NOT NULL THEN
2000 l_logerr := l_functional_area3;
2001 ELSIF cr.serviceable_product_flag = 'Y' AND l_functional_area4 IS NOT NULL THEN
2002 l_logerr := l_functional_area4;
2003 ELSIF cr.costing_enabled_flag = 'Y' AND l_functional_area5 IS NOT NULL THEN
2004 l_logerr := l_functional_area5;
2005 ELSIF cr.eng_item_flag = 'Y' AND l_functional_area6 IS NOT NULL THEN
2006 l_logerr := l_functional_area6;
2007 ELSIF cr.customer_order_flag = 'Y' AND l_functional_area7 IS NOT NULL THEN
2008 l_logerr := l_functional_area7;
2009 ELSIF cr.eam_item_type IS NOT NULL AND l_functional_area9 IS NOT NULL THEN
2010 l_logerr := l_functional_area9;
2011 ELSIF cr.contract_item_type_code IS NOT NULL AND l_functional_area10 IS NOT NULL THEN
2012 l_logerr := l_functional_area10;
2013 END IF;
2014 --End 6531903:Default catalog to be run only once
2015
2016 IF l_logerr <> 0 THEN
2017 OPEN Func_Area_csr(l_logerr);
2018 FETCH Func_Area_csr INTO l_Func_Area,l_Cat_Set_Name;
2019 CLOSE Func_Area_csr;
2020 RAISE Cat_Set_No_Default_Cat;
2021 END IF;
2022 EXCEPTION
2023 WHEN Cat_Set_No_Default_Cat THEN
2024 IF ( Func_Area_csr%ISOPEN ) THEN
2025 CLOSE Func_Area_csr;
2026 END IF;
2027 FND_MESSAGE.SET_NAME ('INV', 'INV_CAT_SET_NO_DEFAULT_CAT');
2028 FND_MESSAGE.SET_TOKEN ('ENTITY1', l_Func_Area);
2029 FND_MESSAGE.SET_TOKEN ('ENTITY2', l_Cat_Set_Name);
2030 error_msg := FND_MESSAGE.GET;
2031 dumm_status := INVPUOPI.mtl_log_interface_err(
2032 cr.organization_id,
2033 user_id,
2034 login_id,
2035 prog_appid,
2036 prog_id,
2037 request_id,
2038 cr.TRANSACTION_ID,
2039 error_msg,
2040 null,
2041 'MTL_SYSTEM_ITEMS_INTERFACE',
2042 'INV_CAT_SET_NO_DEFAULT_CAT',
2043 err_text);
2044 if dumm_status < 0 then
2045 raise LOGGING_ERR;
2046 end if;
2047 status := 1;
2048 END; -- Check of default category
2049
2050 /* NP26DEC94 : New code to update process_flag.
2051 ** This code necessiated due to the breaking up INVPVHDR into
2052 ** 6 smaller packages to overcome PL/SQL limitations
2053 ** with code size.
2054 ** Let's update the process flag for the record
2055 ** Give it value 42 if all okay and 32 if some
2056 ** validation failed in this procedure
2057 ** Need to do this ONLY if all previous validation okay.
2058 ** The process flag values that are possible at this time are
2059 ** 31 :set by INVPVHDR
2060 ** 32 :set by INVPVDR2
2061 ** 33 :set by INVPVDR3
2062 ** 34 :set by INVPVDR4
2063 ** 35, 45 :set by INVPVDR5
2064 ** 36, 46 :set by INVPVDR7
2065 */
2066
2067 /* Bug 4705184
2068 select process_flag into temp_proc_flag
2069 from MTL_SYSTEM_ITEMS_INTERFACE
2070 where inventory_item_id = l_item_id
2071 and set_process_id + 0 = xset_id
2072 and process_flag in (31, 32, 33, 34, 35, 45)
2073 and organization_id = cr.organization_id
2074 and rownum < 2; */
2075
2076 /* Bug 3713912 set value of process_flag to 46 or 36 depending on
2077 ** value of the variable: status.
2078 ** Essentially, we check to see if validation has not already failed in one of
2079 ** the previous packages.
2080 */
2081
2082 if (temp_proc_flag <> 31 and temp_proc_flag <> 32
2083 and temp_proc_flag <> 33 and temp_proc_flag <> 34
2084 and temp_proc_flag <> 35) then
2085 update MTL_SYSTEM_ITEMS_INTERFACE
2086 set process_flag = DECODE(status,0,46,36),
2087 PRIMARY_UOM_CODE = cr.primary_uom_code,
2088 primary_unit_of_measure = cr.primary_unit_of_measure
2089 where inventory_item_id = l_item_id
2090 --and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
2091 and set_process_id = xset_id
2092 and process_flag = 45
2093 and organization_id = cr.organization_id;
2094 end if;
2095
2096 end loop;
2097
2098 return(0);
2099
2100 exception
2101
2102 when LOGGING_ERR then
2103 return(dumm_status);
2104 when VALIDATE_ERR then
2105 dumm_status := INVPUOPI.mtl_log_interface_err(
2106 l_org_id,
2107 user_id,
2108 login_id,
2109 prog_appid,
2110 prog_id,
2111 request_id,
2112 trans_id,
2113 err_text,
2114 'validation_error ' || stmt,
2115 'MTL_SYSTEM_ITEMS_INTERFACE',
2116 'BOM_OP_VALIDATION_ERR',
2117 err_text);
2118 return(status);
2119
2120 when OTHERS then
2121 err_text := substr('INVPVALI.validate_item_header6' || SQLERRM , 1,240);
2122 return(SQLCODE);
2123
2124 END validate_item_header6;
2125
2126
2127 end INVPVDR6;