[Home] [Help]
PACKAGE BODY: APPS.INVPVDR6
Source
1 PACKAGE BODY INVPVDR6 AS
2 /* $Header: INVPVD6B.pls 120.20.12010000.2 2008/09/26 13:12:57 pgandhik 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))) then
1142 dumm_status := INVPUOPI.mtl_log_interface_err(
1143 cr.organization_id,
1144 user_id,
1145 login_id,
1146 prog_appid,
1147 prog_id,
1148 request_id,
1149 cr.TRANSACTION_ID,
1150 error_msg,
1151 'VMI_MINIMUM_UNITS',
1152 'MTL_SYSTEM_ITEMS_INTERFACE',
1153 'INV_MIN_MAX_QTY_VALUE',
1154 err_text);
1155 if dumm_status < 0 then
1156 raise LOGGING_ERR;
1157 end if;
1158 status := 1;
1159 end if;
1160
1161 if nvl(cr.vmi_fixed_order_quantity,0) = 0
1162 and cr.vmi_minimum_days > nvl(cr.vmi_maximum_days,0) then
1163 dumm_status := INVPUOPI.mtl_log_interface_err(
1164 cr.organization_id,
1165 user_id,
1166 login_id,
1167 prog_appid,
1168 prog_id,
1169 request_id,
1170 cr.TRANSACTION_ID,
1171 error_msg,
1172 'VMI_MINIMUM_DAYS',
1173 'MTL_SYSTEM_ITEMS_INTERFACE',
1174 'INV_MIN_DAYS_MAX_DAYS_VALUE',
1175 err_text);
1176 if dumm_status < 0 then
1177 raise LOGGING_ERR;
1178 end if;
1179 status := 1;
1180 end if;
1181
1182 -- validate foreign keys
1183 if ( cr.forecast_horizon is not null) then
1184 begin
1185 if floor(abs(cr.forecast_horizon)) - abs(cr.forecast_horizon ) <> 0 then
1186 raise no_data_found;
1187 end if;
1188 if cr.forecast_horizon < 0 then
1189 raise too_many_rows;
1190 end if;
1191 exception
1192 when NO_DATA_FOUND then
1193 dumm_status := INVPUOPI.mtl_log_interface_err(
1194 cr.organization_id,
1195 user_id,
1196 login_id,
1197 prog_appid,
1198 prog_id,
1199 request_id,
1200 cr.TRANSACTION_ID,
1201 error_msg,
1202 'FORECAST_HORIZON',
1203 'MTL_SYSTEM_ITEMS_INTERFACE',
1204 'INV_VMI_INTEGER_VALUE',
1205 err_text);
1206 if dumm_status < 0 then
1207 raise LOGGING_ERR;
1208 end if;
1209 status := 1;
1210 when TOO_MANY_ROWS then
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 'FORECAST_HORIZON',
1221 'MTL_SYSTEM_ITEMS_INTERFACE',
1222 'INV_VMI_GREATER_THAN_ZERO',
1223 err_text);
1224 if dumm_status < 0 then
1225 raise LOGGING_ERR;
1226 end if;
1227 status := 1;
1228 end;
1229 end if;
1230
1231 -- validate foreign keys
1232 if ( cr.days_tgt_inv_supply is not null) then
1233 begin
1234 if floor(abs(cr.days_tgt_inv_supply)) - abs(cr.days_tgt_inv_supply ) <> 0 then
1235 raise no_data_found;
1236 end if;
1237 if cr.days_tgt_inv_supply < 0 then
1238 raise too_many_rows;
1239 end if;
1240 exception
1241 when NO_DATA_FOUND then
1242 dumm_status := INVPUOPI.mtl_log_interface_err(
1243 cr.organization_id,
1244 user_id,
1245 login_id,
1246 prog_appid,
1247 prog_id,
1248 request_id,
1249 cr.TRANSACTION_ID,
1250 error_msg,
1251 'DAYS_TGT_INV_SUPPLY',
1252 'MTL_SYSTEM_ITEMS_INTERFACE',
1253 'INV_VMI_INTEGER_VALUE',
1254 err_text);
1255 if dumm_status < 0 then
1256 raise LOGGING_ERR;
1257 end if;
1258 status := 1;
1259 when TOO_MANY_ROWS then
1260 dumm_status := INVPUOPI.mtl_log_interface_err(
1261 cr.organization_id,
1262 user_id,
1263 login_id,
1264 prog_appid,
1265 prog_id,
1266 request_id,
1267 cr.TRANSACTION_ID,
1268 error_msg,
1269 'DAYS_TGT_INV_SUPPLY',
1270 'MTL_SYSTEM_ITEMS_INTERFACE',
1271 'INV_VMI_GREATER_THAN_ZERO',
1272 err_text);
1273 if dumm_status < 0 then
1274 raise LOGGING_ERR;
1275 end if;
1276 status := 1;
1277 end;
1278 end if;
1279
1280 -- validate foreign keys
1281 if ( cr.days_tgt_inv_window is not null) then
1282 begin
1283 if floor(abs(cr.days_tgt_inv_window)) - abs(cr.days_tgt_inv_window ) <> 0 then
1284 raise no_data_found;
1285 end if;
1286 if cr.days_tgt_inv_window < 0 then
1287 raise too_many_rows;
1288 end if;
1289 exception
1290 when NO_DATA_FOUND then
1291 dumm_status := INVPUOPI.mtl_log_interface_err(
1292 cr.organization_id,
1293 user_id,
1294 login_id,
1295 prog_appid,
1296 prog_id,
1297 request_id,
1298 cr.TRANSACTION_ID,
1299 error_msg,
1300 'DAYS_TGT_INV_WINDOW',
1301 'MTL_SYSTEM_ITEMS_INTERFACE',
1302 'INV_VMI_INTEGER_VALUE',
1303 err_text);
1304 if dumm_status < 0 then
1305 raise LOGGING_ERR;
1306 end if;
1307 status := 1;
1308 when TOO_MANY_ROWS then
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 'DAYS_TGT_INV_WINDOW',
1319 'MTL_SYSTEM_ITEMS_INTERFACE',
1320 'INV_VMI_GREATER_THAN_ZERO',
1321 err_text);
1322 if dumm_status < 0 then
1323 raise LOGGING_ERR;
1324 end if;
1325 status := 1;
1326 end;
1327 end if;
1328
1329 -- validate foreign keys
1330 if ( cr.days_max_inv_window is not null) then
1331 begin
1332 if floor(abs(cr.days_max_inv_window)) - abs(cr.days_max_inv_window ) <> 0 then
1333 raise no_data_found;
1334 end if;
1335 if cr.days_max_inv_window < 0 then
1336 raise too_many_rows;
1337 end if;
1338 exception
1339 when NO_DATA_FOUND then
1340 dumm_status := INVPUOPI.mtl_log_interface_err(
1341 cr.organization_id,
1342 user_id,
1343 login_id,
1344 prog_appid,
1345 prog_id,
1346 request_id,
1347 cr.TRANSACTION_ID,
1348 error_msg,
1349 'DAYS_MAX_INV_WINDOW',
1350 'MTL_SYSTEM_ITEMS_INTERFACE',
1351 'INV_VMI_INTEGER_VALUE',
1352 err_text);
1353 if dumm_status < 0 then
1354 raise LOGGING_ERR;
1355 end if;
1356 status := 1;
1357 when TOO_MANY_ROWS then
1358 dumm_status := INVPUOPI.mtl_log_interface_err(
1359 cr.organization_id,
1360 user_id,
1361 login_id,
1362 prog_appid,
1363 prog_id,
1364 request_id,
1365 cr.TRANSACTION_ID,
1366 error_msg,
1367 'DAYS_MAX_INV_WINDOW',
1368 'MTL_SYSTEM_ITEMS_INTERFACE',
1369 'INV_VMI_GREATER_THAN_ZERO',
1370 err_text);
1371 if dumm_status < 0 then
1372 raise LOGGING_ERR;
1373 end if;
1374 status := 1;
1375 end;
1376 end if;
1377
1378 -- validate foreign keys
1379 if ( cr.days_max_inv_supply is not null) then
1380 begin
1381 if floor(abs(cr.days_max_inv_supply)) - abs(cr.days_max_inv_supply ) <> 0 then
1382 raise no_data_found;
1383 end if;
1384 if cr.days_max_inv_supply < 0 then
1385 raise too_many_rows;
1386 end if;
1387 exception
1388 when NO_DATA_FOUND then
1389 dumm_status := INVPUOPI.mtl_log_interface_err(
1390 cr.organization_id,
1391 user_id,
1392 login_id,
1393 prog_appid,
1394 prog_id,
1395 request_id,
1396 cr.TRANSACTION_ID,
1397 error_msg,
1398 'DAYS_MAX_INV_SUPPLY',
1399 'MTL_SYSTEM_ITEMS_INTERFACE',
1400 'INV_VMI_INTEGER_VALUE',
1401 err_text);
1402 if dumm_status < 0 then
1403 raise LOGGING_ERR;
1404 end if;
1405 status := 1;
1406 when TOO_MANY_ROWS then
1407 dumm_status := INVPUOPI.mtl_log_interface_err(
1408 cr.organization_id,
1409 user_id,
1410 login_id,
1411 prog_appid,
1412 prog_id,
1413 request_id,
1414 cr.TRANSACTION_ID,
1415 error_msg,
1416 'DAYS_MAX_INV_SUPPLY',
1417 'MTL_SYSTEM_ITEMS_INTERFACE',
1418 'INV_VMI_GREATER_THAN_ZERO',
1419 err_text);
1420 if dumm_status < 0 then
1421 raise LOGGING_ERR;
1422 end if;
1423 status := 1;
1424 end;
1425 end if;
1426
1427
1428 -- validate foreign keys
1429 if ( cr.consigned_flag is not null) then
1430
1431 -- 3762750: Using cursor call to avoid multiple parsing
1432 temp := null;
1433 OPEN c_mfglookup_exists('SYS_YES_NO',CR.CONSIGNED_FLAG);
1434 FETCH c_mfglookup_exists INTO temp;
1435 CLOSE c_mfglookup_exists;
1436
1437 IF (temp IS NULL) THEN
1438 dumm_status := INVPUOPI.mtl_log_interface_err(
1439 cr.organization_id,
1440 user_id,
1441 login_id,
1442 prog_appid,
1443 prog_id,
1444 request_id,
1445 cr.TRANSACTION_ID,
1446 error_msg,
1447 'CONSIGNED_FLAG',
1448 'MTL_SYSTEM_ITEMS_INTERFACE',
1449 'INV_INVALID_ATTR_COL_VALUE',
1450 err_text);
1451 if dumm_status < 0 then
1452 raise LOGGING_ERR;
1453 end if;
1454 status := 1;
1455 END IF;
1456 end if;
1457
1458 -- validate foreign keys
1459 if ( cr.asn_autoexpire_flag is not null) then
1460
1461 -- 3762750: Using cursor call to avoid multiple parsing
1462 temp := null;
1463 OPEN c_mfglookup_exists('SYS_YES_NO',CR.ASN_AUTOEXPIRE_FLAG);
1464 FETCH c_mfglookup_exists INTO temp;
1465 CLOSE c_mfglookup_exists;
1466
1467 IF (temp IS NULL) THEN
1468 dumm_status := INVPUOPI.mtl_log_interface_err(
1469 cr.organization_id,
1470 user_id,
1471 login_id,
1472 prog_appid,
1473 prog_id,
1474 request_id,
1475 cr.TRANSACTION_ID,
1476 error_msg,
1477 'ASN_AUTOEXPIRE_FLAG',
1478 'MTL_SYSTEM_ITEMS_INTERFACE',
1479 'INV_INVALID_ATTR_COL_VALUE',
1480 err_text);
1481 if dumm_status < 0 then
1482 raise LOGGING_ERR;
1483 end if;
1484 status := 1;
1485 END IF;
1486 end if;
1487
1488 -- validate foreign keys
1489 if ( cr.exclude_from_budget_flag is not null) then
1490 -- 3762750: Using cursor call to avoid multiple parsing
1491 temp := null;
1492 OPEN c_mfglookup_exists('SYS_YES_NO',CR.EXCLUDE_FROM_BUDGET_FLAG);
1493 FETCH c_mfglookup_exists INTO temp;
1494 CLOSE c_mfglookup_exists;
1495
1496 IF (temp IS NULL) THEN
1497 dumm_status := INVPUOPI.mtl_log_interface_err(
1498 cr.organization_id,
1499 user_id,
1500 login_id,
1501 prog_appid,
1502 prog_id,
1503 request_id,
1504 cr.TRANSACTION_ID,
1505 error_msg,
1506 'EXCLUDE_FROM_BUDGET_FLAG',
1507 'MTL_SYSTEM_ITEMS_INTERFACE',
1508 'INV_INVALID_ATTR_COL_VALUE',
1509 err_text);
1510 if dumm_status < 0 then
1511 raise LOGGING_ERR;
1512 end if;
1513 status := 1;
1514 END IF;
1515 end if;
1516
1517 -- validate foreign keys
1518 if ( cr.drp_planned_flag is not null) then
1519 -- 3762750: Using cursor call to avoid multiple parsing
1520 temp := null;
1521 OPEN c_mfglookup_exists('SYS_YES_NO',CR.DRP_PLANNED_FLAG);
1522 FETCH c_mfglookup_exists INTO temp;
1523 CLOSE c_mfglookup_exists;
1524
1525 IF (temp IS NULL) THEN
1526 dumm_status := INVPUOPI.mtl_log_interface_err(
1527 cr.organization_id,
1528 user_id,
1529 login_id,
1530 prog_appid,
1531 prog_id,
1532 request_id,
1533 cr.TRANSACTION_ID,
1534 error_msg,
1535 'DRP_PLANNED_FLAG',
1536 'MTL_SYSTEM_ITEMS_INTERFACE',
1537 'INV_INVALID_ATTR_COL_VALUE',
1538 err_text);
1539 if dumm_status < 0 then
1540 raise LOGGING_ERR;
1541 end if;
1542 status := 1;
1543 END IF;
1544 end if;
1545
1546 -- validate foreign keys
1547 if ( cr.critical_component_flag is not null) then
1548 -- 3762750: Using cursor call to avoid multiple parsing
1549 temp := null;
1550 OPEN c_mfglookup_exists('SYS_YES_NO',CR.CRITICAL_COMPONENT_FLAG);
1551 FETCH c_mfglookup_exists INTO temp;
1552 CLOSE c_mfglookup_exists;
1553
1554 IF (temp IS NULL) THEN
1555 dumm_status := INVPUOPI.mtl_log_interface_err(
1556 cr.organization_id,
1557 user_id,
1558 login_id,
1559 prog_appid,
1560 prog_id,
1561 request_id,
1562 cr.TRANSACTION_ID,
1563 error_msg,
1564 'CRITICAL_COMPONENT_FLAG',
1565 'MTL_SYSTEM_ITEMS_INTERFACE',
1566 'INV_INVALID_ATTR_COL_VALUE',
1567 err_text);
1568 if dumm_status < 0 then
1569 raise LOGGING_ERR;
1570 end if;
1571 status := 1;
1572 END IF;
1573 end if;
1574
1575
1576 -- validate foreign keys
1577 if ( cr.so_authorization_flag is not null) then
1578 -- 3762750: Using cursor call to avoid multiple parsing
1579 temp := null;
1580 OPEN c_mfglookup_exists('MTL_MSI_GP_RELEASE_AUTH',CR.SO_AUTHORIZATION_FLAG);
1581 FETCH c_mfglookup_exists INTO temp;
1582 CLOSE c_mfglookup_exists;
1583
1584 IF (temp IS NULL) THEN
1585 dumm_status := INVPUOPI.mtl_log_interface_err(
1586 cr.organization_id,
1587 user_id,
1588 login_id,
1589 prog_appid,
1590 prog_id,
1591 request_id,
1592 cr.TRANSACTION_ID,
1593 error_msg,
1594 'SO_AUTHORIZATION_FLAG',
1595 'MTL_SYSTEM_ITEMS_INTERFACE',
1596 'INV_INVALID_ATTR_COL_VALUE',
1597 err_text);
1598 if dumm_status < 0 then
1599 raise LOGGING_ERR;
1600 end if;
1601 status := 1;
1602 END IF;
1603 end if;
1604 -- validate foreign keys
1605 if ( cr.vmi_forecast_type is not null) then
1606 -- 3762750: Using cursor call to avoid multiple parsing
1607 temp := null;
1608 OPEN c_mfglookup_exists('MTL_MSI_GP_FORECAST_TYPE',CR.VMI_FORECAST_TYPE);
1609 FETCH c_mfglookup_exists INTO temp;
1610 CLOSE c_mfglookup_exists;
1611
1612 IF (temp IS NULL) THEN
1613 dumm_status := INVPUOPI.mtl_log_interface_err(
1614 cr.organization_id,
1615 user_id,
1616 login_id,
1617 prog_appid,
1618 prog_id,
1619 request_id,
1620 cr.TRANSACTION_ID,
1621 error_msg,
1622 'VMI_FORECAST_TYPE',
1623 'MTL_SYSTEM_ITEMS_INTERFACE',
1624 'INV_INVALID_ATTR_COL_VALUE',
1625 err_text);
1626 if dumm_status < 0 then
1627 raise LOGGING_ERR;
1628 end if;
1629 status := 1;
1630 END IF;
1631 end if;
1632 -- validate foreign keys
1633 if ( cr.continous_transfer is not null) then
1634 -- 3762750: Using cursor call to avoid multiple parsing
1635 temp := null;
1636 OPEN c_mfglookup_exists('MTL_MSI_MRP_INT_ORG',CR.CONTINOUS_TRANSFER);
1637 FETCH c_mfglookup_exists INTO temp;
1638 CLOSE c_mfglookup_exists;
1639
1640 IF (temp IS NULL) THEN
1641 dumm_status := INVPUOPI.mtl_log_interface_err(
1642 cr.organization_id,
1643 user_id,
1644 login_id,
1645 prog_appid,
1646 prog_id,
1647 request_id,
1648 cr.TRANSACTION_ID,
1649 error_msg,
1650 'CONTINOUS_TRANSFER',
1651 'MTL_SYSTEM_ITEMS_INTERFACE',
1652 'INV_INVALID_ATTR_COL_VALUE',
1653 err_text);
1654 if dumm_status < 0 then
1655 raise LOGGING_ERR;
1656 end if;
1657 status := 1;
1658 END IF;
1659 end if;
1660 -- validate foreign keys
1661 if ( cr.convergence is not null) then
1662 -- 3762750: Using cursor call to avoid multiple parsing
1663 temp := null;
1664 OPEN c_mfglookup_exists('MTL_MSI_MRP_CONV_SUPP',CR.CONVERGENCE);
1665 FETCH c_mfglookup_exists INTO temp;
1666 CLOSE c_mfglookup_exists;
1667
1668 IF (temp IS NULL) THEN
1669 dumm_status := INVPUOPI.mtl_log_interface_err(
1670 cr.organization_id,
1671 user_id,
1672 login_id,
1673 prog_appid,
1674 prog_id,
1675 request_id,
1676 cr.TRANSACTION_ID,
1677 error_msg,
1678 'CONVERGENCE',
1679 'MTL_SYSTEM_ITEMS_INTERFACE',
1680 'INV_INVALID_ATTR_COL_VALUE',
1681 err_text);
1682 if dumm_status < 0 then
1683 raise LOGGING_ERR;
1684 end if;
1685 status := 1;
1686 END IF;
1687 end if;
1688 -- validate foreign keys
1689 if ( cr.divergence is not null) then
1690 -- 3762750: Using cursor call to avoid multiple parsing
1691 temp := null;
1692 OPEN c_mfglookup_exists('MTL_MSI_MRP_DIV_SUPP',CR.DIVERGENCE);
1693 FETCH c_mfglookup_exists INTO temp;
1694 CLOSE c_mfglookup_exists;
1695
1696 IF (temp IS NULL) THEN
1697 dumm_status := INVPUOPI.mtl_log_interface_err(
1698 cr.organization_id,
1699 user_id,
1700 login_id,
1701 prog_appid,
1702 prog_id,
1703 request_id,
1704 cr.TRANSACTION_ID,
1705 error_msg,
1706 'DIVERGENCE',
1707 'MTL_SYSTEM_ITEMS_INTERFACE',
1708 'INV_INVALID_ATTR_COL_VALUE',
1709 err_text);
1710 if dumm_status < 0 then
1711 raise LOGGING_ERR;
1712 end if;
1713 status := 1;
1714 END IF;
1715 end if;
1716 --Start of bug fix: 3095347
1717 /* Fix for bug 5844510- Source Org/SubInv Should be Null
1718 when source_type is either null or Supplier(2).
1719 Changed the error msg too to indicate the same since source_type
1720 is the driving column and source org/SubInv are dependent on it.
1721 Prior to fix, error msg indicated that source_type has to corrected.*/
1722 if ( ( (cr.source_type is null) or (cr.source_type = 2) )
1723 and (cr.source_organization_id is not null
1724 or cr.source_subinventory is not null))
1725 then
1726 dumm_status := INVPUOPI.mtl_log_interface_err(
1727 cr.organization_id,
1728 user_id,
1729 login_id,
1730 prog_appid,
1731 prog_id,
1732 request_id,
1733 cr.TRANSACTION_ID,
1734 error_msg,
1735 'SOURCE_TYPE',
1736 'MTL_SYSTEM_ITEMS_INTERFACE',
1737 'INV_SOURCE_ORG_MUST_BE_NULL',
1738 err_text);
1739 if dumm_status < 0 then
1740 raise LOGGING_ERR;
1741 end if;
1742 status := 1;
1743 end if;
1744
1745 if (cr.source_type is not null
1746 and cr.source_organization_id = nvl(org_id,cr.organization_id)
1747 and cr.mrp_planning_code = 3
1748 and cr.source_subinventory is null)
1749 then
1750 dumm_status := INVPUOPI.mtl_log_interface_err(
1751 cr.organization_id,
1752 user_id,
1753 login_id,
1754 prog_appid,
1755 prog_id,
1756 request_id,
1757 cr.TRANSACTION_ID,
1758 error_msg,
1759 'SOURCE_SUBINVENTORY',
1760 'MTL_SYSTEM_ITEMS_INTERFACE',
1761 'INV_IOI_SOURCE_SUBINV_REQUIRED',
1762 err_text);
1763 if dumm_status < 0 then
1764 raise LOGGING_ERR;
1765 end if;
1766 status := 1;
1767 end if;
1768
1769 if cr.source_type in (1,3)
1770 and cr.source_organization_id is not null
1771 then
1772 validate_source := INVIDIT1.validate_source_org(
1773 cr.organization_id,
1774 cr.inventory_item_id,
1775 cr.inventory_item_id,
1776 cr.source_organization_id,
1777 cr.mrp_planning_code,
1778 cr.source_subinventory);
1779 /* Fix for bug 5844510- For throwing INV_ITEM_IN_SOURCE_ORG error,
1780 compare validate_source with value 1. Prior to the fix, it was
1781 comparing with value 3 which is wrong.*/
1782 if (validate_source = 1
1783 and cr.source_organization_id <> cr.organization_id )
1784 then
1785 dumm_status := INVPUOPI.mtl_log_interface_err(
1786 cr.organization_id,
1787 user_id,
1788 login_id,
1789 prog_appid,
1790 prog_id,
1791 request_id,
1792 cr.TRANSACTION_ID,
1793 error_msg,
1794 'SOURCE_SUBINVENTORY',
1795 'MTL_SYSTEM_ITEMS_INTERFACE',
1796 'INV_ITEM_IN_SOURCE_ORG',
1797 err_text);
1798 if dumm_status < 0 then
1799 raise LOGGING_ERR;
1800 end if;
1801 status := 1;
1802 elsif ( validate_source = 3 ) then
1803 dumm_status := INVPUOPI.mtl_log_interface_err(
1804 cr.organization_id,
1805 user_id,
1806 login_id,
1807 prog_appid,
1808 prog_id,
1809 request_id,
1810 cr.TRANSACTION_ID,
1811 error_msg,
1812 'SOURCE_SUBINVENTORY',
1813 'MTL_SYSTEM_ITEMS_INTERFACE',
1814 'INV_INTERORG_NTWK',
1815 err_text);
1816 if dumm_status < 0 then
1817 raise LOGGING_ERR;
1818 end if;
1819 status := 1;
1820 end if;
1821 end if;
1822
1823 /* R12 Enhancement - Adding validation */
1824
1825 if cr.TAX_CODE is not null then
1826 IF l_inv_debug_level IN(101, 102) THEN
1827 INVPUTLI.info('INVPVDR6: Calling INVPVDR6 - Verifying lookup type for tax code ');
1828 END IF;
1829
1830 temp := null;
1831 OPEN c_tax_code_exists(cr.TAX_CODE,cr.organization_id);
1832 FETCH c_tax_code_exists INTO temp;
1833 CLOSE c_tax_code_exists;
1834
1835 IF temp IS NULL THEN
1836
1837 dumm_status := INVPUOPI.mtl_log_interface_err(
1838 cr.organization_id,
1839 user_id,
1840 login_id,
1841 prog_appid,
1842 prog_id,
1843 request_id,
1844 cr.TRANSACTION_ID,
1845 error_msg,
1846 'TAX_CODE',
1847 'MTL_SYSTEM_ITEMS_INTERFACE',
1848 'INV_IOI_TAX_CODE',
1849 err_text);
1850 if dumm_status < 0 then
1851 raise LOGGING_ERR;
1852 end if;
1853 status := 1;
1854
1855 END IF;
1856
1857 END IF;
1858
1859
1860 --End of bug fix: 3095347
1861 /* Bug: 5178297 Commenting this block to allow negative values for Lead Times
1862 --Start : 2990665 Lead Time Quantities cannot be negative.
1863 if (cr.PREPROCESSING_LEAD_TIME < 0
1864 or cr.FULL_LEAD_TIME < 0
1865 or cr.POSTPROCESSING_LEAD_TIME < 0
1866 or cr.FIXED_LEAD_TIME < 0
1867 or cr.VARIABLE_LEAD_TIME < 0
1868 or cr.CUM_MANUFACTURING_LEAD_TIME < 0
1869 or cr.CUMULATIVE_TOTAL_LEAD_TIME < 0
1870 or cr.LEAD_TIME_LOT_SIZE < 0)
1871 then
1872 dumm_status := INVPUOPI.mtl_log_interface_err(
1873 cr.organization_id,
1874 user_id,
1875 login_id,
1876 prog_appid,
1877 prog_id,
1878 request_id,
1879 cr.TRANSACTION_ID,
1880 error_msg,
1881 'LEAD_TIMES_QUANTITIES',
1882 'MTL_SYSTEM_ITEMS_INTERFACE',
1883 'INV_IOI_NEGATIVE_LEAD_TIME_QTY',
1884 err_text);
1885 if dumm_status < 0 then
1886 raise LOGGING_ERR;
1887 end if;
1888 status := 1;
1889 end if;
1890 --End : 2990665
1891 End Commenting Bug: 5178297*/
1892 --Bug:3309789 DRP planned items cannot be enabled with Assemble to Order
1893 if (cr.drp_planned_flag = 1
1894 and cr.replenish_to_order_flag = 'Y' )
1895 then
1896 dumm_status := INVPUOPI.mtl_log_interface_err(
1897 cr.organization_id,
1898 user_id,
1899 login_id,
1900 prog_appid,
1901 prog_id,
1902 request_id,
1903 cr.TRANSACTION_ID,
1904 error_msg,
1905 'DRP_PLANNED_FLAG',
1906 'MTL_SYSTEM_ITEMS_INTERFACE',
1907 'INV_DRP_CANNOT_BE_ATO',
1908 err_text);
1909 if dumm_status < 0 then
1910 raise LOGGING_ERR;
1911 end if;
1912 status := 1;
1913 end if;
1914
1915
1916 /* NP 09SEP94
1917 ** call INVPUOPI.validate_flags to validate the yes/no flags
1918 ** function validate_flags now resides in INVPUPI2 instead of INVPVALI
1919 ** NP 06MAY96: No need to add xset_id call since it processes with row_id
1920 ** which is absolutely unique.Moving this code above all beacause of perf reasons.
1921 3515652: Performance enhancements, now validate flag works on setid
1922
1923 IF l_inv_debug_level IN(101, 102) THEN
1924 INVPUTLI.info('INVPVDR6: Calling INVPUPI2.validate_flags');
1925 END IF;
1926 dumm_status := INVPUPI2.validate_flags (
1927 cr.rowid,
1928 prog_appid,
1929 prog_id,
1930 request_id,
1931 user_id,
1932 login_id,
1933 err_text);
1934
1935 if dumm_status < 0 then
1936 raise LOGGING_ERR;
1937 end if;
1938
1939 if dumm_status <> 0 then
1940 status := 1;
1941 end if;
1942 */
1943 /*
1944 ** call the costing package to validate the cost columns
1945 ** NP 06MAY96: No need to pass xset_id to CSTFVSUB procedure
1946 ** The transaction_id is passed and that is unique enough
1947 ** for all the updates that CSTFVSUB does to MSII.
1948 */
1949
1950 IF l_inv_debug_level IN(101, 102) THEN
1951 INVPUTLI.info('INVPVDR6: Calling INVPCOII.CSTFVSUB Costing package');
1952 END IF;
1953
1954
1955 /* INVPUTLI.info(cr.rowid|| ' ' ||user_id|| ' ' ||login_id||' ');
1956 ** INVPUTLI.info(request_id||' ' || prog_id||' '||prog_appid||' '|| err_text);
1957 */
1958 dumm_status := INVPCOII.CSTFVSUB(cr.transaction_id,
1959 cr.material_sub_elem,
1960 cr.material_oh_sub_elem,
1961 cr.organization_id,
1962 user_id,
1963 login_id,
1964 request_id,
1965 prog_id,
1966 prog_appid,
1967 err_text);
1968 if dumm_status < 0 then
1969 raise LOGGING_ERR;
1970 end if;
1971 if dumm_status <> 0 then
1972 status := 1;
1973 end if;
1974
1975 stmt := 48;
1976
1977 --Bug: 3028216 check of defautl categories
1978 IF l_inv_debug_level IN(101, 102) THEN
1979 INVPUTLI.info('INVPPROC.inproit_process_item: Checking Default categories before insert');
1980 END IF;
1981
1982 BEGIN
1983 l_logerr := 0;
1984
1985 --Start 6531903:Default catalog to be run only once
1986 IF cr.inventory_item_flag = 'Y' AND l_functional_area1 IS NOT NULL THEN
1987 l_logerr := l_functional_area1;
1988 ELSIF (cr.purchasing_item_flag = 'Y' OR cr.internal_order_flag ='Y') AND l_functional_area2 IS NOT NULL THEN
1989 l_logerr := l_functional_area2;
1990 ELSIF cr.mrp_planning_code = 6 AND l_functional_area3 IS NOT NULL THEN
1991 l_logerr := l_functional_area3;
1992 ELSIF cr.serviceable_product_flag = 'Y' AND l_functional_area4 IS NOT NULL THEN
1993 l_logerr := l_functional_area4;
1994 ELSIF cr.costing_enabled_flag = 'Y' AND l_functional_area5 IS NOT NULL THEN
1995 l_logerr := l_functional_area5;
1996 ELSIF cr.eng_item_flag = 'Y' AND l_functional_area6 IS NOT NULL THEN
1997 l_logerr := l_functional_area6;
1998 ELSIF cr.customer_order_flag = 'Y' AND l_functional_area7 IS NOT NULL THEN
1999 l_logerr := l_functional_area7;
2000 ELSIF cr.eam_item_type IS NOT NULL AND l_functional_area9 IS NOT NULL THEN
2001 l_logerr := l_functional_area9;
2002 ELSIF cr.contract_item_type_code IS NOT NULL AND l_functional_area10 IS NOT NULL THEN
2003 l_logerr := l_functional_area10;
2004 END IF;
2005 --End 6531903:Default catalog to be run only once
2006
2007 IF l_logerr <> 0 THEN
2008 OPEN Func_Area_csr(l_logerr);
2009 FETCH Func_Area_csr INTO l_Func_Area,l_Cat_Set_Name;
2010 CLOSE Func_Area_csr;
2011 RAISE Cat_Set_No_Default_Cat;
2012 END IF;
2013 EXCEPTION
2014 WHEN Cat_Set_No_Default_Cat THEN
2015 IF ( Func_Area_csr%ISOPEN ) THEN
2016 CLOSE Func_Area_csr;
2017 END IF;
2018 FND_MESSAGE.SET_NAME ('INV', 'INV_CAT_SET_NO_DEFAULT_CAT');
2019 FND_MESSAGE.SET_TOKEN ('ENTITY1', l_Func_Area);
2020 FND_MESSAGE.SET_TOKEN ('ENTITY2', l_Cat_Set_Name);
2021 error_msg := FND_MESSAGE.GET;
2022 dumm_status := INVPUOPI.mtl_log_interface_err(
2023 cr.organization_id,
2024 user_id,
2025 login_id,
2026 prog_appid,
2027 prog_id,
2028 request_id,
2029 cr.TRANSACTION_ID,
2030 error_msg,
2031 null,
2032 'MTL_SYSTEM_ITEMS_INTERFACE',
2033 'INV_CAT_SET_NO_DEFAULT_CAT',
2034 err_text);
2035 if dumm_status < 0 then
2036 raise LOGGING_ERR;
2037 end if;
2038 status := 1;
2039 END; -- Check of default category
2040
2041 /* NP26DEC94 : New code to update process_flag.
2042 ** This code necessiated due to the breaking up INVPVHDR into
2043 ** 6 smaller packages to overcome PL/SQL limitations
2044 ** with code size.
2045 ** Let's update the process flag for the record
2046 ** Give it value 42 if all okay and 32 if some
2047 ** validation failed in this procedure
2048 ** Need to do this ONLY if all previous validation okay.
2049 ** The process flag values that are possible at this time are
2050 ** 31 :set by INVPVHDR
2051 ** 32 :set by INVPVDR2
2052 ** 33 :set by INVPVDR3
2053 ** 34 :set by INVPVDR4
2054 ** 35, 45 :set by INVPVDR5
2055 ** 36, 46 :set by INVPVDR7
2056 */
2057
2058 /* Bug 4705184
2059 select process_flag into temp_proc_flag
2060 from MTL_SYSTEM_ITEMS_INTERFACE
2061 where inventory_item_id = l_item_id
2062 and set_process_id + 0 = xset_id
2063 and process_flag in (31, 32, 33, 34, 35, 45)
2064 and organization_id = cr.organization_id
2065 and rownum < 2; */
2066
2067 /* Bug 3713912 set value of process_flag to 46 or 36 depending on
2068 ** value of the variable: status.
2069 ** Essentially, we check to see if validation has not already failed in one of
2070 ** the previous packages.
2071 */
2072
2073 if (temp_proc_flag <> 31 and temp_proc_flag <> 32
2074 and temp_proc_flag <> 33 and temp_proc_flag <> 34
2075 and temp_proc_flag <> 35) then
2076 update MTL_SYSTEM_ITEMS_INTERFACE
2077 set process_flag = DECODE(status,0,46,36),
2078 PRIMARY_UOM_CODE = cr.primary_uom_code,
2079 primary_unit_of_measure = cr.primary_unit_of_measure
2080 where inventory_item_id = l_item_id
2081 and set_process_id + 0 = xset_id
2082 and process_flag = 45
2083 and organization_id = cr.organization_id;
2084 end if;
2085
2086 end loop;
2087
2088 return(0);
2089
2090 exception
2091
2092 when LOGGING_ERR then
2093 return(dumm_status);
2094 when VALIDATE_ERR then
2095 dumm_status := INVPUOPI.mtl_log_interface_err(
2096 l_org_id,
2097 user_id,
2098 login_id,
2099 prog_appid,
2100 prog_id,
2101 request_id,
2102 trans_id,
2103 err_text,
2104 'validation_error ' || stmt,
2105 'MTL_SYSTEM_ITEMS_INTERFACE',
2106 'BOM_OP_VALIDATION_ERR',
2107 err_text);
2108 return(status);
2109
2110 when OTHERS then
2111 err_text := substr('INVPVALI.validate_item_header6' || SQLERRM , 1,240);
2112 return(SQLCODE);
2113
2114 END validate_item_header6;
2115
2116
2117 end INVPVDR6;