[Home] [Help]
PACKAGE BODY: APPS.INVPVHDR
Source
1 package body INVPVHDR as
2 /* $Header: INVPVD1B.pls 120.13 2007/05/30 12:17:28 anmurali ship $ */
3
4 g_max_segment number := NULL;
5 g_totalsegs number := NULL;
6 g_segment_delimiter varchar2(10) := NULL;
7
8
9 function validate_item_header
10 (
11 org_id number,
12 all_org NUMBER := 2,
13 prog_appid NUMBER := -1,
14 prog_id NUMBER := -1,
15 request_id NUMBER := -1,
16 user_id NUMBER := -1,
17 login_id NUMBER := -1,
18 err_text in out NOCOPY varchar2,
19 xset_id IN NUMBER DEFAULT -999
20 )
21 return integer
22 is
23 /******************************************************/
24 /* Variable required for the validation for UPDATES */
25 /******************************************************/
26 loc_ctrl_code NUMBER;
27 cost_flag VARCHAR2(1);
28 inv_asset_flag VARCHAR2(1);
29 mrp_stock_code NUMBER;
30 base_item NUMBER;
31 lead_lot_size NUMBER;
32 out_op_flag VARCHAR2(1);
33 shelf_code NUMBER;
34 temp VARCHAR2(2);
35 temp_uom_code VARCHAR2(3);
36 temp_u_o_m VARCHAR2(25);
37 temp_uom_class VARCHAR2(10);
38 temp_enabled_flag VARCHAR2(1);
39 /*
40 ** Retrieve column values for validation
41 */
42 CURSOR cc is
43 select
44 ROWID,
45 ITEM_NUMBER,
46 TRANSACTION_ID,
47 ORGANIZATION_ID,
48 ORGANIZATION_CODE,
49 TRANSACTION_TYPE,
50 PROCESS_FLAG,
51 INVENTORY_ITEM_ID,
52 SUMMARY_FLAG,
53 ENABLED_FLAG,
54 START_DATE_ACTIVE,
55 END_DATE_ACTIVE,
56 DESCRIPTION,
57 BUYER_ID,
58 ACCOUNTING_RULE_ID,
59 INVOICING_RULE_ID,
60 SEGMENT1,
61 SEGMENT2,
62 SEGMENT3,
63 SEGMENT4,
64 SEGMENT5,
65 SEGMENT6,
66 SEGMENT7,
67 SEGMENT8,
68 SEGMENT9,
69 SEGMENT10,
70 SEGMENT11,
71 SEGMENT12,
72 SEGMENT13,
73 SEGMENT14,
74 SEGMENT15,
75 SEGMENT16,
76 SEGMENT17,
77 SEGMENT18,
78 SEGMENT19,
79 SEGMENT20,
80 ATTRIBUTE_CATEGORY,
81 ATTRIBUTE1,
82 ATTRIBUTE2,
83 ATTRIBUTE3,
84 ATTRIBUTE4,
85 ATTRIBUTE5,
86 ATTRIBUTE6,
87 ATTRIBUTE7,
88 ATTRIBUTE8,
89 ATTRIBUTE9,
90 ATTRIBUTE10,
91 ATTRIBUTE11,
92 ATTRIBUTE12,
93 ATTRIBUTE13,
94 ATTRIBUTE14,
95 ATTRIBUTE15,
96 PURCHASING_ITEM_FLAG,
97 SHIPPABLE_ITEM_FLAG,
98 CUSTOMER_ORDER_FLAG,
99 INTERNAL_ORDER_FLAG,
100 -- SERVICE_ITEM_FLAG,
101 INVENTORY_ITEM_FLAG,
102 ENG_ITEM_FLAG,
103 INVENTORY_ASSET_FLAG,
104 PURCHASING_ENABLED_FLAG,
105 CUSTOMER_ORDER_ENABLED_FLAG,
106 INTERNAL_ORDER_ENABLED_FLAG,
107 SO_TRANSACTIONS_FLAG,
108 MTL_TRANSACTIONS_ENABLED_FLAG,
109 STOCK_ENABLED_FLAG,
110 BOM_ENABLED_FLAG,
111 BUILD_IN_WIP_FLAG,
112 REVISION_QTY_CONTROL_CODE,
113 ITEM_CATALOG_GROUP_ID,
114 CATALOG_STATUS_FLAG,
115 RETURNABLE_FLAG,
116 DEFAULT_SHIPPING_ORG,
117 COLLATERAL_FLAG,
118 TAXABLE_FLAG,
119 QTY_RCV_EXCEPTION_CODE,
120 ALLOW_ITEM_DESC_UPDATE_FLAG,
121 INSPECTION_REQUIRED_FLAG,
122 RECEIPT_REQUIRED_FLAG,
123 MARKET_PRICE,
124 HAZARD_CLASS_ID,
125 RFQ_REQUIRED_FLAG,
126 QTY_RCV_TOLERANCE,
127 LIST_PRICE_PER_UNIT,
128 UN_NUMBER_ID,
129 PRICE_TOLERANCE_PERCENT,
130 ASSET_CATEGORY_ID,
131 ROUNDING_FACTOR,
132 UNIT_OF_ISSUE,
133 ENFORCE_SHIP_TO_LOCATION_CODE,
134 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
135 ALLOW_UNORDERED_RECEIPTS_FLAG,
136 ALLOW_EXPRESS_DELIVERY_FLAG,
137 DAYS_EARLY_RECEIPT_ALLOWED,
138 DAYS_LATE_RECEIPT_ALLOWED,
139 RECEIPT_DAYS_EXCEPTION_CODE,
140 RECEIVING_ROUTING_ID,
141 INVOICE_CLOSE_TOLERANCE,
142 RECEIVE_CLOSE_TOLERANCE,
143 AUTO_LOT_ALPHA_PREFIX,
144 START_AUTO_LOT_NUMBER,
145 LOT_CONTROL_CODE,
146 SHELF_LIFE_CODE,
147 SHELF_LIFE_DAYS,
148 SERIAL_NUMBER_CONTROL_CODE,
149 START_AUTO_SERIAL_NUMBER,
150 AUTO_SERIAL_ALPHA_PREFIX,
151 SOURCE_TYPE,
152 SOURCE_ORGANIZATION_ID,
153 SOURCE_SUBINVENTORY,
154 EXPENSE_ACCOUNT,
155 ENCUMBRANCE_ACCOUNT,
156 RESTRICT_SUBINVENTORIES_CODE,
157 UNIT_WEIGHT,
158 WEIGHT_UOM_CODE,
159 VOLUME_UOM_CODE,
160 UNIT_VOLUME,
161 RESTRICT_LOCATORS_CODE,
162 LOCATION_CONTROL_CODE,
163 SHRINKAGE_RATE,
164 ACCEPTABLE_EARLY_DAYS,
165 PLANNING_TIME_FENCE_CODE,
166 DEMAND_TIME_FENCE_CODE,
167 LEAD_TIME_LOT_SIZE,
168 STD_LOT_SIZE,
169 CUM_MANUFACTURING_LEAD_TIME,
170 OVERRUN_PERCENTAGE,
171 MRP_CALCULATE_ATP_FLAG,
172 ACCEPTABLE_RATE_INCREASE,
173 ACCEPTABLE_RATE_DECREASE,
174 CUMULATIVE_TOTAL_LEAD_TIME,
175 PLANNING_TIME_FENCE_DAYS,
176 DEMAND_TIME_FENCE_DAYS,
177 END_ASSEMBLY_PEGGING_FLAG,
178 REPETITIVE_PLANNING_FLAG,
179 PLANNING_EXCEPTION_SET,
180 BOM_ITEM_TYPE,
181 PICK_COMPONENTS_FLAG,
182 REPLENISH_TO_ORDER_FLAG,
183 BASE_ITEM_ID,
184 ATP_COMPONENTS_FLAG,
185 ATP_FLAG,
186 FIXED_LEAD_TIME,
187 VARIABLE_LEAD_TIME,
188 WIP_SUPPLY_LOCATOR_ID,
189 WIP_SUPPLY_TYPE,
190 WIP_SUPPLY_SUBINVENTORY,
191 PRIMARY_UOM_CODE,
192 PRIMARY_UNIT_OF_MEASURE,
193 ALLOWED_UNITS_LOOKUP_CODE,
194 COST_OF_SALES_ACCOUNT,
195 SALES_ACCOUNT,
196 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
197 INVENTORY_ITEM_STATUS_CODE,
198 INVENTORY_PLANNING_CODE,
199 PLANNER_CODE,
200 PLANNING_MAKE_BUY_CODE,
201 FIXED_LOT_MULTIPLIER,
202 ROUNDING_CONTROL_TYPE,
203 CARRYING_COST,
204 POSTPROCESSING_LEAD_TIME,
205 PREPROCESSING_LEAD_TIME,
206 FULL_LEAD_TIME,
207 ORDER_COST,
208 MRP_SAFETY_STOCK_PERCENT,
209 MRP_SAFETY_STOCK_CODE,
210 MIN_MINMAX_QUANTITY,
211 MAX_MINMAX_QUANTITY,
212 MINIMUM_ORDER_QUANTITY,
213 FIXED_ORDER_QUANTITY,
214 FIXED_DAYS_SUPPLY,
215 MAXIMUM_ORDER_QUANTITY,
216 ATP_RULE_ID,
217 PICKING_RULE_ID,
218 RESERVABLE_TYPE,
219 POSITIVE_MEASUREMENT_ERROR,
220 NEGATIVE_MEASUREMENT_ERROR,
221 ENGINEERING_ECN_CODE,
222 ENGINEERING_ITEM_ID,
223 ENGINEERING_DATE,
224 SERVICE_STARTING_DELAY,
225 -- VENDOR_WARRANTY_FLAG,
226 -- SERVICEABLE_COMPONENT_FLAG,
227 SERVICEABLE_PRODUCT_FLAG,
228 BASE_WARRANTY_SERVICE_ID,
229 PAYMENT_TERMS_ID,
230 PREVENTIVE_MAINTENANCE_FLAG,
231 PRIMARY_SPECIALIST_ID,
232 SECONDARY_SPECIALIST_ID,
233 SERVICEABLE_ITEM_CLASS_ID,
234 TIME_BILLABLE_FLAG,
235 MATERIAL_BILLABLE_FLAG,
236 EXPENSE_BILLABLE_FLAG,
237 PRORATE_SERVICE_FLAG,
238 COVERAGE_SCHEDULE_ID,
239 SERVICE_DURATION_PERIOD_CODE,
240 SERVICE_DURATION,
241 WARRANTY_VENDOR_ID,
242 MAX_WARRANTY_AMOUNT,
243 RESPONSE_TIME_PERIOD_CODE,
244 RESPONSE_TIME_VALUE,
245 NEW_REVISION_CODE,
246 INVOICEABLE_ITEM_FLAG,
247 TAX_CODE,
248 INVOICE_ENABLED_FLAG,
249 MUST_USE_APPROVED_VENDOR_FLAG,
250 REQUEST_ID,
251 PROGRAM_APPLICATION_ID,
252 PROGRAM_ID,
253 PROGRAM_UPDATE_DATE,
254 OUTSIDE_OPERATION_FLAG,
255 OUTSIDE_OPERATION_UOM_TYPE,
256 SAFETY_STOCK_BUCKET_DAYS,
257 AUTO_REDUCE_MPS,
258 COSTING_ENABLED_FLAG,
259 CYCLE_COUNT_ENABLED_FLAG,
260 AUTO_CREATED_CONFIG_FLAG,
261 ITEM_TYPE,
262 MODEL_CONFIG_CLAUSE_NAME,
263 SHIP_MODEL_COMPLETE_FLAG,
264 MRP_PLANNING_CODE,
265 RETURN_INSPECTION_REQUIREMENT,
266 ATO_FORECAST_CONTROL,
267 RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight new cols added for 10.7 */
268 RELEASE_TIME_FENCE_DAYS,
269 CONTAINER_ITEM_FLAG,
270 CONTAINER_TYPE_CODE,
271 INTERNAL_VOLUME,
272 MAXIMUM_LOAD_WEIGHT,
273 MINIMUM_FILL_PERCENT,
274 VEHICLE_ITEM_FLAG,
275 CHECK_SHORTAGES_FLAG, /*CK 21MAY98 Added new attribute*/
276 INDIVISIBLE_FLAG,
277 CONTRACT_ITEM_TYPE_CODE,
278 --Adding attributes now updateable for Pending items R12 C
279 DIMENSION_UOM_CODE,
280 UNIT_LENGTH,
281 UNIT_WIDTH,
282 UNIT_HEIGHT
283 from MTL_SYSTEM_ITEMS_INTERFACE
284 where ((organization_id + 0 = org_id) or
285 (all_Org = 1))
286 and set_process_id = xset_id
287 and process_flag = 2;
288
289 -- Bug: 4654433
290 CURSOR get_organization_code (cp_org_id VARCHAR2) IS
291 SELECT
292 name
293 FROM hr_organization_units
294 WHERE organization_id = cp_org_id;
295
296 CURSOR is_gdsn_batch(cp_xset_id NUMBER) IS
297 SELECT 1 FROM ego_import_option_sets
298 WHERE batch_id = cp_xset_id
299 AND enabled_for_data_pool = 'Y';
300 /*
301 ** Items have the same key segment values must have the same item id
302 ** NP 13-OCT-94 Comment
303 ** The cursor dd1 has largely been obsoleted by the TWO_PASS design
304 ** Because of the two pass design an item will always find its
305 ** inventory item id from the master, since we insert an item
306 ** in master org
307 ** in the first pass (before the insertion into the child org).
308 ** However it performs ONE very important task:
309 ** It allows you to insert an item in more than one MASTER org
310 ** in the same FIRST pass
311 ** and it ensures that they all get the same InvItemId
312 ** (which has to be constant across ALL orgs)
313 ** Following cursor now being replaced by Dyn SQL 2
314 ** CURSOR dd1 (seg1 varchar2,seg2 varchar2,seg3 varchar2,
315 ** blah blah...
316 ** seg17 varchar2,seg18 varchar2,
317 ** seg19 varchar2,seg20 varchar2,
318 ** item_id_in number) is
319 ** select inventory_item_id,
320 ** transaction_id,
321 ** organization_id
322 ** from MTL_SYSTEM_ITEMS_INTERFACE
323 ** where inventory_item_id <> item_id_in
324 ** and set_process_id = nvl(xset_id, set_process_id)
325 ** and nvl(segment1,'.') = nvl(seg1,'.')
326 ** and nvl(segment2,'.') = nvl(seg2,'.')
327 ** and so on so forth...
328 ** and nvl(segment20,'.') = nvl(seg20,'.');
329 */
330
331 msicount number;
332 msiicount number;
333 resersal_flag number;
334 dup_item_id number;
335 l_item_id number;
336 l_org_id number;
337 cat_set_id number;
338 trans_id number;
339 ext_flag number := 0;
340 error_msg varchar2(400);
341 status number;
342 dumm_status number;
343 master_org_id number;
344 stmt number;
345 LOGGING_ERR exception;
346 VALIDATE_ERR exception;
347 lot_num_generation_val number; /*NP 21DEC94*/
348 ser_num_generation_val number; /*NP 21DEC94*/
349 is_master_org number ;
350 no_of_masterorgs number ;
351 org_name varchar2(240);
352
353 /* Dynamic SQL variables*/
354
355 type BindVarType is table of varchar2(80) index by binary_integer;
356 type BindValType is table of varchar2(80) index by binary_integer;
357 type NumType is table of number index by binary_integer;
358
359 bindvars1 BindVarType;
360 bindvals1 BindValType;
361
362 SEG_TAB NumType;
363 segnum_temp varchar2(2);
364 max_segment number;
365 return_status number;
366 pos number;
367 ind number;
368 segnum number := NULL;
369 totalsegs number;
370
371 DSQL_inventory_item_id number; /*dynamic sql column*/
372 DSQL_count_star number; /*dynamic sql column*/
373 DSQL2_inventory_item_id number;
374 DSQL2_transaction_id number;
375 DSQL2_organization_id number;
376 DSQL_statement1 varchar2(3000);
377 DSQL_statement2 varchar2(3000);
378 DSQL_statement3 varchar2(3000);
379 DSQL_statement4 varchar2(3000);
380 DSQL_c1 integer; /*pointer to dynamic SQL cursor*/
381 DSQL_c2 integer; /*pointer to dynamic SQL cursor*/
382 DSQL_c3 integer; /*pointer to dynamic SQL cursor*/
383 DSQL_c4 integer; /*pointer to dynamic SQL cursor*/
384 statement_temp1 varchar2(2000) := NULL;
385 statement_temp2 varchar2(2000) := NULL;
386 statement_temp3 varchar2(2000) := NULL;
387 statement_temp4 varchar2(2000) := NULL;
388 err_temp varchar2(1000) := NULL;
389 DSQL_rows_processed integer;
390 transaction_id_bind integer;
391 dummy_ret_code integer;
392 l_application_id number(10) := 401;
393 l_id_flex_code varchar2(4) := 'MSTK';
394 l_enabled_flag varchar2(1) := 'Y';
395 l_id_flex_num number(15) := 101;
396 l_dummy varchar2(50);
397 l_seg_size number := 0;
398 l_uppercase_flag varchar2(1) ;
399 l_msg_name VARCHAR2(1000) := NULL;
403 l_segment_required NUMBER(10):=0;
400 l_start_auto_lot_num mtl_system_items_b.START_AUTO_LOT_NUMBER%TYPE;
401
402 --2967569 : Required Segments check
404 l_required_flag varchar2(1) := 'Y';
405
406 --3360280:KFV validation using fnd_flex_keyval
407 l_valid_segments BOOLEAN := FALSE;
408 l_item_number mtl_system_items_interface.item_number%TYPE;
409 l_deliminator_count NUMBER(10) := 0;
410 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
411 l_col_name VARCHAR2(100);
412 l_is_gdsn_batch NUMBER;
413
414 begin
415
416
417
418 /**** Begin section for dynamic sql bind variables*/
419
420 for n in 1..20 loop
421 SEG_TAB(n) := NULL;
422 end loop;
423
424 IF g_max_segment IS NULL THEN
425 select max(FS.segment_num)
426 into g_max_segment
427 from FND_ID_FLEX_SEGMENTS FS
428 where FS.APPLICATION_ID = l_application_id
429 and FS.id_flex_code = l_id_flex_code
430 and FS.ENABLED_FLAG = l_enabled_flag
431 and FS.id_flex_num = l_id_flex_num;
432 END IF;
433
434 max_segment := g_max_segment;
435
436 IF g_totalsegs IS NULL THEN
437 select count(*) into g_totalsegs
438 from FND_ID_FLEX_SEGMENTS FS
439 where FS.APPLICATION_ID = l_application_id
440 and FS.id_flex_code = l_id_flex_code
441 and FS.ENABLED_FLAG = l_enabled_flag
442 and FS.id_flex_num = l_id_flex_num;
443 END IF;
444
445 totalsegs := g_totalsegs;
446
447 IF g_segment_delimiter IS NULL THEN
448 select concatenated_segment_delimiter
449 into g_segment_delimiter
450 from fnd_id_flex_structures
451 where id_flex_code = l_id_flex_code
452 and APPLICATION_ID = l_application_id
453 and ID_FLEX_NUM = l_id_flex_num;
454 END IF;
455
456 pos := 1;
457 ind := 1;
458
459 for n in 1..max_segment loop
460 begin
461 /* NP 05SEP96 changed this select FS.segment_num
462 ** to match the changes made in INVPUL1B.pls get_dynamic_sql_str
463 */
464 select to_number(substr(FS.application_column_name, 8))
465 into segnum
466 from FND_ID_FLEX_SEGMENTS FS
467 where FS.SEGMENT_NUM = n
468 and FS.ID_FLEX_CODE = l_id_flex_code
469 and FS.ID_FLEX_NUM = l_id_flex_num
470 and FS.ENABLED_FLAG = l_enabled_flag
471 and FS.APPLICATION_ID = l_application_id;
472 exception
473 when NO_DATA_FOUND then
474 segnum := NULL;
475 when OTHERS then
476 raise_application_error(-20001, SQLERRM);
477 end;
478
479 if segnum is not NULL then
480 SEG_TAB(ind) := segnum;
481 ind := ind + 1;
482 end if;
483 end loop;
484
485 /* This dynamic sql is trying to create entries in plsql tables
486 ** bindvarsX bindvalsX
487 ** to facilitate the dynamic binding of variables: For example:
488 ** dbms_sql.bind_variable(DSQL_c1, 'cr_segment1_bind', cr.segment1);
489 ** The code fragment here mimics the code in INVPUTLI.get_dynamic_sql_str()
490 */
491
492 for n in 1..totalsegs loop
493 segnum_temp := to_char(SEG_TAB(n));
494 bindvars1(n) := 'cr_segment'||segnum_temp||'_bind';
495 end loop;
496
497 /* Call the function to dynamically build the where clause */
498 dummy_ret_code := INVPUTLI.get_dynamic_sql_str(2, statement_temp1,
499 err_temp);
500 /* The same where clause is needed for the 1st
501 ** and 3rd dynamic sql statements in this package
502 */
503 statement_temp3 := statement_temp1;
504
505 err_temp := NULL;
506 dummy_ret_code := INVPUTLI.get_dynamic_sql_str(3, statement_temp4,
507 err_temp);
508 /* The same where clause is needed for the 2nd and 4th
509 ** dynamic sql statements in this package
510 */
511 statement_temp2 := statement_temp4;
512
513
514 /*
515 ** End section for dynamic sql bind variables
516 */
517
518 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
519
520 /*
521 ** validate the records
522 */
523 for cr in cc loop
524 status := 0;
525 trans_id := cr.transaction_id;
526 l_org_id := cr.organization_id;
527 l_item_id := cr.inventory_item_id;
528
529 /*
530 ** Since the item_id might be changed
531 ** in this code when segments match etc.,
532 ** get the current one
533 */
534
535 /* Bug 4705184. Get item id from the cursor. At this point there is no change in item id.
536 select inventory_item_id
537 into l_item_id
538 from mtl_system_items_interface
539 where transaction_id = cr.transaction_id
540 and set_process_id = xset_id ; */
541
542 /* Put in the values of cr.segmentX in the right
543 ** place in the bindvals1 table
544 ** The bindvars1 table has already been populated
545 ** outside the cursor scope because it will remain constant.
546 ** The bindvals1 table will have different entries depending
547 ** on the cursor record cr
548 */
549
550 for n in 1..totalsegs loop
551 segnum_temp := to_char(SEG_TAB(n));
552
556 bindvals1(n) := cr.segment2;
553 if (segnum_temp = 1) then
554 bindvals1(n) := cr.segment1;
555 elsif (segnum_temp = 2) then
557 elsif (segnum_temp = 3) then
558 bindvals1(n) := cr.segment3;
559 elsif (segnum_temp = 4) then
560 bindvals1(n) := cr.segment4;
561 elsif (segnum_temp = 5) then
562 bindvals1(n) := cr.segment5;
563 elsif (segnum_temp = 6) then
564 bindvals1(n) := cr.segment6;
565 elsif (segnum_temp = 7) then
566 bindvals1(n) := cr.segment7;
567 elsif (segnum_temp = 8) then
568 bindvals1(n) := cr.segment8;
569 elsif (segnum_temp = 9) then
570 bindvals1(n) := cr.segment9;
571 elsif (segnum_temp = 10) then
572 bindvals1(n) := cr.segment10;
573 elsif (segnum_temp = 11) then
574 bindvals1(n) := cr.segment11;
575 elsif (segnum_temp = 12) then
576 bindvals1(n) := cr.segment12;
577 elsif (segnum_temp = 13) then
578 bindvals1(n) := cr.segment13;
579 elsif (segnum_temp = 14) then
580 bindvals1(n) := cr.segment14;
581 elsif (segnum_temp = 15) then
582 bindvals1(n) := cr.segment15;
583 elsif (segnum_temp = 16) then
584 bindvals1(n) := cr.segment16;
585 elsif (segnum_temp = 17) then
586 bindvals1(n) := cr.segment17;
587 elsif (segnum_temp = 18) then
588 bindvals1(n) := cr.segment18;
589 elsif (segnum_temp = 19) then
590 bindvals1(n) := cr.segment19;
591 elsif (segnum_temp = 20) then
592 bindvals1(n) := cr.segment20;
593 end if;
594
595 end loop; /* Finished populating the bindvals1() plsql table*/
596
597 --Start 3360280:KFV validation using fnd_flex_keyval
598 if cr.item_number IS NULL then
599 dumm_status := INVPUOPI.mtl_pr_parse_item_segments
600 (p_row_id => cr.rowid
601 ,item_number => l_item_number
602 ,item_id => l_item_id
603 ,err_text => err_text);
604
605 --Bug: 5512333
606 if cr.item_number IS NOT NULL THEN
607 dumm_status := INVPUOPI.mtl_pr_parse_item_number(l_item_number
608 ,cr.inventory_item_id
609 ,cr.transaction_id
610 ,cr.organization_id
611 ,err_text
612 ,cr.rowid);
613 if dumm_status < 0 THEN
614 status := 1;
615 dumm_status := INVPUOPI.mtl_log_interface_err(
616 cr.organization_id
617 ,user_id
618 ,login_id
619 ,prog_appid
620 ,prog_id
621 ,request_id
622 ,cr.transaction_id
623 ,error_msg
624 ,null
625 ,'MTL_SYSTEM_ITEMS_INTERFACE'
626 ,'BOM_PARSE_ITEM_ERROR'
627 ,err_text);
628 if dumm_status < 0 then
629 raise LOGGING_ERR;
630 end if;
631 end if;
632 end if;
633 --End Bug: 5512333
634 else
635 l_item_number := cr.item_number;
636 end if;
637
638 --Start 3610290: Item number should have deliminator
639 /*If one+ segments are enabled irrespective of required
640 or not fnd_flex_keyval.validate_segs expects item number
641 to be passed in segment1.segment..*/
642
643 l_deliminator_count := 0;
644 IF totalsegs > 1 THEN
645 WHILE l_deliminator_count < totalsegs -1 LOOP
646 SELECT INSTR(l_item_number,g_segment_delimiter,1,totalsegs -1)
647 INTO l_deliminator_count FROM DUAL;
648 IF l_deliminator_count < totalsegs -1 THEN
649 l_item_number := l_item_number ||g_segment_delimiter;
650 END IF;
651 END LOOP;
652 END IF;
653 --End 3610290: Item number should have deliminator
654
655 l_valid_segments := fnd_flex_keyval.validate_segs
656 (operation =>'CHECK_SEGMENTS',
657 appl_short_name => 'INV',
658 key_flex_code => 'MSTK',
659 structure_number => 101,
660 concat_segments => l_item_number);
661
662 IF NOT l_valid_segments THEN
663 status := 1;
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 substr(FND_FLEX_KEYVAL.error_message,1,239),
673 'ITEM_NUMBER',
674 'MTL_SYSTEM_ITEMS_INTERFACE',
675 'INV_IOI_ERR',
676 err_text);
680 ELSE
677 if dumm_status < 0 then
678 raise LOGGING_ERR;
679 end if;
681 --Bug: 5200023 Added the ELSE BLOCK
682 IF l_item_number <> fnd_flex_keyval.concatenated_values THEN
683 status := 1;
684 FND_MESSAGE.SET_NAME ('INV', 'INV_ITEM_SEGMENTS_INVALID');
685 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', l_item_number);
686 error_msg := FND_MESSAGE.GET;
687
688 dumm_status := INVPUOPI.mtl_log_interface_err(
689 cr.organization_id,
690 user_id,
691 login_id,
692 prog_appid,
693 prog_id,
694 request_id,
695 cr.TRANSACTION_ID,
696 error_msg,
697 'ITEM_NUMBER',
698 'MTL_SYSTEM_ITEMS_INTERFACE',
699 'INV_IOI_ERR',
700 err_text);
701 if dumm_status < 0 then
702 raise LOGGING_ERR;
703 end if;
704 END IF;
705 END IF;
706 --End 3360280:KFV validation using fnd_flex_keyval
707
708
709 /*
710 ** For any two items have the same key segment values,
711 ** ensure that they have the same item id
712 ** 08-APR-96 This code now completely rewritten using
713 ** dynamic sql.
714 ** 04SEP96 To remove full table scan on msi by using the msi index
715 ** on org_id and segment(s), add a (dummy) join to mtl_parameters
716 ** The + 0 has been put in so the mtl_parameters index is not used
717 */
718 select count(*)
719 into is_master_org
720 from mtl_parameters
721 where organization_id = cr.organization_id
722 and master_organization_id = cr.organization_id ;
723
724 If (is_master_org = 1) then
725 select count(*)
726 into no_of_masterorgs
727 from mtl_parameters
728 where organization_id = master_organization_id ;
729
730 If (no_of_masterorgs > 1) then
731 BEGIN /* PLSQL Block1 */
732 IF l_inv_debug_level IN(101, 102) THEN
733 INVPUTLI.info('INVPVHDR: stmt0 latest');
734 END IF;
735
736 DSQL_statement1 := 'select distinct msi.inventory_item_id
737 from mtl_system_items msi,
738 mtl_parameters mp
739 where msi.organization_id <>
740 :organization_id_bind
741 and msi.inventory_item_id <> :l_item_id_bind
742 and msi.organization_id = mp.organization_id + 0
743 and ' || statement_temp1;
744
745
746
747 /* statement_temp1 is from call to get_dyn_sql_stmt
748 ** organization_id_bind gets cr.organization_id
749 ** l_item_id_bind gets l_item_id
750 ** Now, open cursor DSQL_c1, parse it and bind the variables
751 */
752
753 DSQL_c1 := dbms_sql.open_cursor;
754 dbms_sql.parse(DSQL_c1, DSQL_statement1, dbms_sql.native);
755 dbms_sql.define_column(DSQL_c1, 1, DSQL_inventory_item_id);
756 dbms_sql.bind_variable(DSQL_c1, 'organization_id_bind',
757 cr.organization_id);
758 dbms_sql.bind_variable(DSQL_c1, 'l_item_id_bind', l_item_id);
759
760 /* The following is to facilitate the dynamic binding
761 ** of the where clause variables:
762 ** dbms_sql.bind_variable(DSQL_c1,
763 ** 'cr_segment1_bind', cr.segment1);
764 ** The PL/SQL tables bindvars1 and bindvals1 have alreadey
765 ** been populated before.
766 */
767
768 for n in 1..totalsegs loop
769 BEGIN
770 IF l_inv_debug_level IN(101, 102) THEN
771 INVPUTLI.info('INVPVHDR: BIND values ' ||
772 bindvars1(n)||' gets '|| bindvals1(n));
773 END IF;
774
775 dbms_sql.bind_variable(DSQL_c1, bindvars1(n), bindvals1(n) );
776
777 EXCEPTION
778 when others then
779 err_text:= substr('validate_item_header DSQL 1'|| SQLERRM, 1, 240);
780 return(SQLCODE);
781
782 END;
783 end loop;
784
785
786 Begin
787 DSQL_rows_processed := dbms_sql.execute(DSQL_c1);
788 Exception
789 when others then
790 err_text:= substr('validate_item_header DSQL 2'|| SQLERRM , 1, 240) ;
791 return(SQLCODE);
792 End;
793
794 /*NP 10-APR-95
795 **This is the code that is being replaced by dynamic sql
796 **select distinct inventory_item_id
800 **msi.organization_id <> cr.organization_id
797 **into dup_item_id
798 **from mtl_system_items msi
799 **where
801 **and msi.inventory_item_id <> l_item_id
802 **and segment1 = nvl(cr.segment1,'.');
803 **and segment2 = nvl(cr.segment2,'.') .........
804 **and so on so forth : Now with dynamic SQL, only the
805 **relevant portions of the where clause are activated
806 **depending on which segments
807 **of the system_item flexfield have been enabled
808 */
809
810 /*
811 ** Note that we are not going to loop here
812 ** over the cursor DSQL_c1, as is traditionally done.
813 ** Because we expect just one row at most to be
814 ** returned from the MSI table because of the distinct
815 ** clause
816 */
817
818 if dbms_sql.fetch_rows(DSQL_c1) > 0 then /* BIG If*/
819
820 Begin
821 dbms_sql.column_value(DSQL_c1,1,DSQL_inventory_item_id);
822
823 dup_item_id := DSQL_inventory_item_id;
824
825 update mtl_system_items_interface
826 set inventory_item_id = dup_item_id
827 where transaction_id = cr.transaction_id
828 and set_process_id = xset_id;
829
830 l_item_id := dup_item_id;
831
832 Exception
833 When NO_DATA_FOUND then NULL;
834 When OTHERS then
835 err_text := substr('CK_DU_ISEG:' || SQLERRM , 1, 240);
836 status := SQLCODE;
837 raise VALIDATE_ERR;
838 End;
839
840 else /*The cursor DSQL_c1 fetched no rows at all*/
841 dbms_sql.close_cursor(DSQL_c1);
842 IF l_inv_debug_level IN(101, 102) THEN
843 INVPUTLI.info('INVPVHDR: entering DSQL2 ');
844 END IF;
845
846 /* This else clause simulates the earlier
847 ** exception when NO_DATA_FOUND then
848 ** clause
849 ** Now close that DSQL_c1 cursor and
850 ** do the processing for items is multiple
851 ** masters case
852 */
853
854 BEGIN /*Dyn SQL BLOCK 2*/
855 /*NP 30AUG96 This statement has been identified as being
856 **a big resource hog, because of nvl to nvl
857 **comparison in statement_temp2
858 ** 04SEP96 To remove full table scan on msii by using the
859 ** msii index on org_id and segment(s), add a (dummy)
860 ** join to mtl_parameters The + 0 has been put in so
861 ** the mtl_parameters index is not used
862 */
863
864 DSQL_statement2 := ' select msii.inventory_item_id,
865 msii.transaction_id,
866 msii.organization_id
867 from mtl_system_items_interface msii,
868 mtl_parameters mp
869 where msii.inventory_item_id <>
870 :l_item_id_bind
871 and set_process_id = :xset_id_bind
872 and msii.organization_id = mp.organization_id + 0
873 and ' || statement_temp2;
874
875 DSQL_c2 := dbms_sql.open_cursor;
876 dbms_sql.parse(DSQL_c2, DSQL_statement2, dbms_sql.native);
877 dbms_sql.define_column(DSQL_c2, 1, DSQL2_inventory_item_id);
878 dbms_sql.define_column(DSQL_c2, 2, DSQL2_transaction_id);
879 dbms_sql.define_column(DSQL_c2, 3, DSQL2_organization_id);
880 dbms_sql.bind_variable(DSQL_c2, 'l_item_id_bind', l_item_id);
881 dbms_sql.bind_variable(DSQL_c2, 'xset_id_bind', xset_id);
882
883 for n in 1..totalsegs loop
884 BEGIN
885 IF l_inv_debug_level IN(101, 102) THEN
886 INVPUTLI.info('INVPVHDR: BIND2 values ' ||
887 bindvars1(n)||' gets '|| bindvals1(n));
888 END IF;
889
890 dbms_sql.bind_variable(DSQL_c2, bindvars1(n), bindvals1(n) );
891 EXCEPTION
892 when others then
893 err_text:= substr('validate_item_header DSQL 2'|| SQLERRM , 1, 240);
894 return(SQLCODE);
895 END;
896 end loop;
897
898 begin
899 DSQL_rows_processed := dbms_sql.execute(DSQL_c2);
900 exception
901 when others then
902 err_text:= substr('validate_item_header DSQL 2.2'|| SQLERRM , 1, 240);
903 return(SQLCODE);
904 end;
905
906 loop
907 if dbms_sql.fetch_rows(DSQL_c2) > 0 then
908 dbms_sql.column_value(DSQL_c2, 1, DSQL2_inventory_item_id);
909 dbms_sql.column_value(DSQL_c2, 2, DSQL2_transaction_id);
910 dbms_sql.column_value(DSQL_c2, 3, DSQL2_organization_id);
911
912
913 /* update item header with new item id
914 ** This is for updating the III for similar
915 ** segment items
916 ** going to different master orgs
917 ** Child items will not enter this NO DATA FOUND
921 IF l_inv_debug_level IN(101, 102) THEN
918 ** clause because
919 ** the TWO_PASS design ensures that item WILL be
920 ** found in masterorg in MSI*/
922 INVPUTLI.info('INVPVHDR: Same item being added to MULTIPLE masters ');
923 INVPUTLI.info('INVPVHDR: So now updating inv_item_id in MSII, MIRI ');
924 END IF;
925 update mtl_system_items_interface
926 set inventory_item_id = l_item_id
927 where transaction_id = DSQL2_transaction_id
928 and set_process_id = xset_id;
929
930
931 /* 09-APR-96 Added update to MIRI
932 ** because if the inv_item_id is being
933 ** changed in MSII
934 ** it should also be changed in MIRI and MICI
935 ** otherwise there will be dangling
936 ** references that will
937 ** be flagged as errors in mtl_interface_errors
938 ** Also do so only where the org ids don't match
939 ** since now the iii will be same we definitely
940 ** don't want to do it for a record with same
941 ** orgs: it will be a violation
942 ** This violation WILL be caught elsewhere
943 ** on checking that similar segs don't have
944 ** same org in msii (duplicate record)
945 **
946 **Also,the following not needed since categories INSERT
947 **takes place much later..so no point in updating.
948 **update mtl_item_categories_interface
949 **set inventory_item_id = l_item_id
950 **where inventory_item_id = DSQL2_inventory_item_id;
951 ** 06/18/97 Changed below
952 ** and organization_id <> DSQL2_organization_id
953 ** will ensure that the inventory_item_id
954 ** is updated correctly in MIRI. The <> is
955 ** changed to equals.
956 */
957
958 update mtl_item_revisions_interface
959 set inventory_item_id = l_item_id
960 where inventory_item_id = DSQL2_inventory_item_id
961 and organization_id = DSQL2_organization_id
962 and set_process_id + 0 = xset_id;
963
964 else
965 -- no more rows, Close cursor and exit
966 dbms_sql.close_cursor(DSQL_c2);
967 exit;
968 end if;
969 end loop;
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 if dbms_sql.is_open(DSQL_c2) then
973 dbms_sql.close_cursor(DSQL_c2);
974 end if;
975 WHEN OTHERS THEN
976 if dbms_sql.is_open(DSQL_c2) then
977 dbms_sql.close_cursor(DSQL_c2);
978 end if;
979 err_text:= substr('validate_item_header DSQL STMT2 '|| SQLERRM , 1,240) ;
980 return(SQLCODE);
981 END; /*PLSQL Block 2 (inside block 1)*/
982
983
984 end if; /*BIG if from BLOCK 1*/
985
986 if dbms_sql.is_open(DSQL_c1) then
987 dbms_sql.close_cursor(DSQL_c1);
988 end if;
989
990 EXCEPTION
991
992 WHEN NO_DATA_FOUND THEN
993 if dbms_sql.is_open(DSQL_c1) then
994 dbms_sql.close_cursor(DSQL_c1);
995 end if;
996 WHEN OTHERS THEN
997 if dbms_sql.is_open(DSQL_c1) then
998 dbms_sql.close_cursor(DSQL_c1);
999 end if;
1000 err_text:= substr('validate_item_header DSQL STMT1'|| SQLERRM , 1,240);
1001 return(SQLCODE);
1002
1003 END; /*PLSQL Block 1 (This block also contains the PL/SQL Block2)*/
1004 END IF;
1005 END IF;
1006
1007 /*
1008 ** Check for uniqueness of INVENTORY_ITEM_ID and
1009 ** ORGANIZATION_ID.
1010 */
1011 stmt := 1;
1012
1013 IF l_inv_debug_level IN(101, 102) THEN
1014 INVPUTLI.info('INVPVHDR: check 3-1');
1015 END IF;
1016
1017 select count(*)
1018 into ext_flag
1019 from MTL_SYSTEM_ITEMS_B
1020 where inventory_item_id = l_item_id
1021 and organization_id = cr.organization_id
1022 and cr.transaction_type = 'CREATE';
1023 IF l_inv_debug_level IN(101, 102) THEN
1024 INVPUTLI.info('INVPVHDR: check 3-1');
1025 END IF;
1026
1027 stmt := 2;
1028 if ext_flag > 0 then
1029 -- Bug: 4654433
1030 Open get_organization_code(cr.organization_id);
1031 Fetch get_organization_code Into org_name;
1032 Close get_organization_code;
1033 org_name := NVL(org_name, cr.organization_code);
1034 status := 1;
1035 stmt := 3;
1036
1037 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_ITEM_MSI');
1038 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', cr.item_number);
1039 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
1043 user_id,
1040 error_msg := FND_MESSAGE.GET;
1041 dumm_status := INVPUOPI.mtl_log_interface_err(
1042 cr.organization_id,
1044 login_id,
1045 prog_appid,
1046 prog_id,
1047 request_id,
1048 cr.TRANSACTION_ID,
1049 error_msg,
1050 'INVENTORY_ITEM_ID',
1051 'MTL_SYSTEM_ITEMS_INTERFACE',
1052 'INV_IOI_ERR',
1053 err_text);
1054 if dumm_status < 0 then
1055 raise LOGGING_ERR;
1056 end if;
1057 else
1058 stmt := 4;
1059 select count(*)
1060 into ext_flag
1061 from MTL_SYSTEM_ITEMS_INTERFACE
1062 where inventory_item_id = l_item_id
1063 and organization_id = cr.organization_id
1064 and process_flag = 2
1065 and set_process_id = xset_id;
1066 IF l_inv_debug_level IN(101, 102) THEN
1067 INVPUTLI.info('INVPVHDR: check 4-1');
1068 END IF;
1069
1070 if ext_flag > 1 then
1071 stmt := 5;
1072 --Bypassing validation for GDSN batches
1073 l_is_gdsn_batch := 0;
1074 Open is_gdsn_batch(xset_id);
1075 Fetch is_gdsn_batch INTO l_is_gdsn_batch;
1076 Close is_gdsn_batch;
1077
1078 if l_is_gdsn_batch <> 1 then
1079 -- Bug: 4654433
1080 Open get_organization_code(cr.organization_id);
1081 Fetch get_organization_code Into org_name;
1082 Close get_organization_code;
1083 org_name := NVL(org_name, cr.organization_code);
1084 status := 1;
1085 --Bug: 4777089 Flagging INV_IOI_DUPLICATE_REC_MSII in place of INV_IOI_DUPLICATE_ITEM_MSI
1086 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_REC_MSII');
1087 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', cr.item_number);
1088 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
1089 error_msg := FND_MESSAGE.GET;
1090 dumm_status := INVPUOPI.mtl_log_interface_err(
1091 cr.organization_id,
1092 user_id,
1093 login_id,
1094 prog_appid,
1095 prog_id,
1096 request_id,
1097 cr.TRANSACTION_ID,
1098 error_msg,
1099 'INVENTORY_ITEM_ID',
1100 'MTL_SYSTEM_ITEMS_INTERFACE',
1101 'INV_IOI_ERR',
1102 err_text);
1103 if dumm_status < 0 then
1104 raise LOGGING_ERR;
1105 end if;
1106 end if; --Not GDSN Batch
1107 end if;
1108 end if;
1109
1110 /*
1111 ** Check for uniqueness of combination of key segment values
1112 ** and organization id
1113 */
1114 stmt := 6;
1115 IF l_inv_debug_level IN(101, 102) THEN
1116 INVPUTLI.info('INVPVHDR: stmt6');
1117 END IF;
1118 BEGIN /* PLSQL Block 2*/
1119
1120 DSQL_statement3 := 'select count(*)
1121 from MTL_SYSTEM_ITEMS msi
1122 where organization_id = :organization_id_bind
1123 and :transaction_type_bind = ''CREATE''
1124 and ' || statement_temp3;
1125
1126 DSQL_c3 := dbms_sql.open_cursor;
1127 dbms_sql.parse(DSQL_c3, DSQL_statement3, dbms_sql.native);
1128 dbms_sql.define_column(DSQL_c3, 1, DSQL_count_star);
1129
1130 /* Now bind the variables*/
1131 dbms_sql.bind_variable(DSQL_c3, 'organization_id_bind',
1132 cr.organization_id);
1133 dbms_sql.bind_variable(DSQL_c3, 'transaction_type_bind',
1134 cr.transaction_type);
1135 /*
1136 ** The following binding for
1137 ** DSQL_c3 is exactly the same as for DSQL_c1
1138 ** since the where clauses happen to be the same
1139 ** Same bind variables and Bind values, just a different cursor
1140 */
1141
1142 for n in 1..totalsegs loop
1143 BEGIN
1144 dbms_sql.bind_variable(DSQL_c3, bindvars1(n), bindvals1(n) );
1145 EXCEPTION
1146 when others then
1147 err_text:= substr('validate_item_header DSQL 1.1'|| SQLERRM , 1, 240);
1148 return(SQLCODE);
1149 END;
1150 end loop;
1151
1152 BEGIN
1153 DSQL_rows_processed := dbms_sql.execute(DSQL_c3);
1154 EXCEPTION
1155 when others then
1156 err_text:= substr('validate_item_header DSQL 1.2'|| SQLERRM , 1, 240);
1157 return(SQLCODE);
1158 END;
1159
1160 ext_flag := 0;
1164 else
1161 if dbms_sql.fetch_rows(DSQL_c3) > 0 then
1162 dbms_sql.column_value(DSQL_c3,1,DSQL_count_star);
1163 ext_flag := DSQL_count_star;
1165 dbms_sql.close_cursor(DSQL_c3);
1166 end if;
1167
1168 if dbms_sql.is_open(DSQL_c3) then
1169 dbms_sql.close_cursor(DSQL_c3);
1170 end if;
1171
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 if dbms_sql.is_open(DSQL_c3) then
1176 dbms_sql.close_cursor(DSQL_c3);
1177 end if;
1178 err_text:= substr('validate_item_header DSQL STMT3'|| SQLERRM , 1,240);
1179 return(SQLCODE);
1180
1181 END; /*Plsql block2*/
1182
1183 stmt := 7;
1184
1185 IF l_inv_debug_level IN(101, 102) THEN
1186 INVPUTLI.info('INVPVHDR: stmt7');
1187 END IF;
1188 IF ext_flag > 0 AND status=0 THEN /* Crucial If:*/ --Bug:5208039
1189
1190 /*
1191 ** There is a record in MSI with same segs and org_id
1192 ** So error this record out
1193 */
1194 -- Bug: 4654433
1195 Open get_organization_code(cr.organization_id);
1196 Fetch get_organization_code Into org_name;
1197 Close get_organization_code;
1198 org_name := NVL(org_name, cr.organization_code);
1199 status := 1;
1200
1201 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_ITEM_MSI');
1202 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', cr.item_number);
1203 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
1204 error_msg := FND_MESSAGE.GET;
1205 dumm_status := INVPUOPI.mtl_log_interface_err(
1206 cr.organization_id,
1207 user_id,
1208 login_id,
1209 prog_appid,
1210 prog_id,
1211 request_id,
1212 cr.TRANSACTION_ID,
1213 error_msg,
1214 'SEGMENTS',
1215 'MTL_SYSTEM_ITEMS_INTERFACE',
1216 'INV_IOI_ERR',
1217 err_text);
1218 /*NP 08-APR-96 Changed the table to
1219 **be msi instead of MSI_intf
1220 */
1221 if dumm_status < 0 then
1222 raise LOGGING_ERR;
1223 end if;
1224
1225 ELSE /* Check the same for duplicate records in MSII */
1226
1227 BEGIN /* PLSQL block 3 for dyn sql*/
1228 stmt := 8;
1229 /*
1230 ** Changing following stmt to dynamic sql
1231 **select count(*)
1232 **into ext_flag
1233 **from MTL_SYSTEM_ITEMS_INTERFACE msii
1234 **where organization_id = cr.organization_id
1235 **and nvl(msii.segment1, '.') = nvl(cr.segment1,'.');
1236 **and so on......
1237 ** intermediate step: ...... = nvl(:cr_segment1_bind, '.')
1238 ** and so on., and then use dbms_sql.bind_variable to bind the
1239 ** values to the cursor DSQL_c4
1240 ** 03MAY96 Added xset_id processing to this dynamic sql stmt.
1241 */
1242
1243 DSQL_statement4 := 'select count(*)
1244 from MTL_SYSTEM_ITEMS_INTERFACE msii
1245 where organization_id = :organization_id_bind
1246 and set_process_id = :xset_id_bind
1247 and process_flag = 2
1248 and ' || statement_temp4;
1249
1250 DSQL_c4 := dbms_sql.open_cursor;
1251 dbms_sql.parse(DSQL_c4, DSQL_statement4, dbms_sql.native);
1252 dbms_sql.define_column(DSQL_c4, 1, DSQL_count_star);
1253 dbms_sql.bind_variable(DSQL_c4, 'xset_id_bind', xset_id);
1254 dbms_sql.bind_variable(DSQL_c4, 'organization_id_bind',
1255 cr.organization_id);
1256
1257 /*
1258 ** The following binding for
1259 ** DSQL_c4 is exactly the same as for DSQL_c1
1260 ** since the where clauses happen to be the same
1261 ** Same bind variables and Bind values, just a different cursor
1262 */
1263
1264 for n in 1..totalsegs loop
1265 begin
1266 dbms_sql.bind_variable(DSQL_c4, bindvars1(n), bindvals1(n) );
1267 exception
1268 when others then
1269 err_text:= substr('validate_item_header DSQL 4.1'|| SQLERRM,1,240);
1270 return(SQLCODE);
1271 end;
1272 end loop;
1273
1274 begin
1275 DSQL_rows_processed := dbms_sql.execute(DSQL_c4);
1276 exception
1277 when others then
1278 err_text:= substr('validate_item_header DSQL 4.2'|| SQLERRM , 1 , 240);
1279 return(SQLCODE);
1280 end;
1281
1282 ext_flag := 0;
1283 if dbms_sql.fetch_rows(DSQL_c4) > 0 then
1284 dbms_sql.column_value(DSQL_c4,1,DSQL_count_star);
1285 ext_flag := DSQL_count_star;
1286
1287 else
1288 dbms_sql.close_cursor(DSQL_c4);
1289 end if;
1290
1291 if dbms_sql.is_open(DSQL_c4) then
1292 dbms_sql.close_cursor(DSQL_c4);
1296 WHEN OTHERS THEN
1293 end if;
1294
1295 EXCEPTION
1297 if dbms_sql.is_open(DSQL_c4) then
1298 dbms_sql.close_cursor(DSQL_c4);
1299 dbms_sql.close_cursor(DSQL_c4);
1300 end if;
1301 err_text:= substr('validate_item_header DSQL STMT4'|| SQLERRM , 1,240);
1302 return(SQLCODE);
1303
1304 END; /*Plsql block3 for dynamic SQL*/
1305
1306
1307 stmt := 9;
1308 if ext_flag > 1 then
1309 /* That is, there is another row in msii
1310 ** with exactly the same segment and org info
1311 ** Flag as error.
1312 */
1313 l_is_gdsn_batch := 0;
1314 Open is_gdsn_batch(xset_id);
1315 Fetch is_gdsn_batch INTO l_is_gdsn_batch;
1316 Close is_gdsn_batch;
1317
1318 if l_is_gdsn_batch <> 1 then
1319 -- Bug: 4654433
1320 Open get_organization_code(cr.organization_id);
1321 Fetch get_organization_code Into org_name;
1322 Close get_organization_code;
1323 org_name := NVL(org_name, cr.organization_code);
1324 status := 1;
1325 --Bug: 4777089 Flagging INV_IOI_DUPLICATE_REC_MSII in place of INV_IOI_DUPLICATE_ITEM_MSI
1326 FND_MESSAGE.SET_NAME ('INV', 'INV_IOI_DUPLICATE_REC_MSII');
1327 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', cr.item_number);
1328 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', org_name);
1329 error_msg := FND_MESSAGE.GET;
1330 dumm_status := INVPUOPI.mtl_log_interface_err(
1331 cr.organization_id,
1332 user_id,
1333 login_id,
1334 prog_appid,
1335 prog_id,
1336 request_id,
1337 cr.TRANSACTION_ID,
1338 error_msg,
1339 'SEGMENTS',
1340 'MTL_SYSTEM_ITEMS_INTERFACE',
1341 'INV_IOI_ERR',
1342 err_text);
1343 if dumm_status < 0 then
1344 raise LOGGING_ERR;
1345 end if;
1346 end if; --Not GDSN Batch
1347 end if;
1348
1349 END IF; /* Crucial If*/
1350
1351 /* R12C Desc Null chk, since check in Defaulting phase is bypassed for func generated ICC catalog items */
1352 IF cr.description IS NULL THEN
1353 dumm_status := INVPUOPI.mtl_log_interface_err(
1354 cr.organization_id,
1355 user_id,
1356 login_id,
1357 prog_appid,
1358 prog_id,
1359 request_id,
1360 cr.TRANSACTION_ID,
1361 error_msg,
1362 'DESCRIPTION',
1363 'MTL_SYSTEM_ITEMS_INTERFACE',
1364 'INV_DESC_ITEM_ERROR',
1365 err_text);
1366 status := 1;
1367 if dumm_status < 0 then
1368 raise LOGGING_ERR;
1369 end if;
1370 END IF;
1371
1372 /*NP 10-APR-96 End of all performance improving dynamic sql changes*/
1373
1374
1375 /*
1376 ** Check for integrity rules/restrictions for item attributes
1377 */
1378 /*INVPUTLI.info('INVPVHDR: Validating flags'); */
1379
1380 -- validate that UOM values exist
1381 /*NP 28DEC94 New validation
1382 ** if both PRIMARY_UOM_CODE and PRIMARY_UNIT_OF_MEASURE null
1383 ** then we have a problem
1384 ** if either exists then INVPVDR5 handles it later.
1385 */
1386 if cr.PRIMARY_UOM_CODE is NULL
1387 and cr.PRIMARY_UNIT_OF_MEASURE is NULL then
1388 dumm_status := INVPUOPI.mtl_log_interface_err(
1389 cr.organization_id,
1390 user_id,
1391 login_id,
1392 prog_appid,
1393 prog_id,
1394 request_id,
1395 cr.TRANSACTION_ID,
1396 error_msg,
1397 'UOM_PROFILE',
1398 'MTL_SYSTEM_ITEMS_INTERFACE',
1399 'INV_INVALID_PRIMARY_UOM',
1400 err_text);
1401 status := 1;
1402 if dumm_status < 0 then
1403 raise LOGGING_ERR;
1404 end if;
1405 end if;
1406
1407 -- validate INTERNAL_ORDER_FLAG
1408 -- Added for bug 4260213
1409 if cr.INTERNAL_ORDER_FLAG = 'Y' AND cr.SHIPPABLE_ITEM_FLAG = 'N'
1410 then
1411 dumm_status := INVPUOPI.mtl_log_interface_err(
1412 cr.organization_id,
1413 user_id,
1414 login_id,
1415 prog_appid,
1416 prog_id,
1417 request_id,
1418 cr.TRANSACTION_ID,
1419 error_msg,
1420 'SHIPPABLE_ITEM_FLAG',
1421 'MTL_SYSTEM_ITEMS_INTERFACE',
1422 'INV_SHIP_INT_YES',
1423 err_text);
1424 status := 1;
1425 if dumm_status < 0 then
1426 raise LOGGING_ERR;
1427 end if;
1428 end if;
1429
1430 -- validate BOM_ITEM_TYPE
1434 cr.organization_id,
1431 --Added for bug 3436384
1432 if cr.BOM_ITEM_TYPE = 5 AND cr.CUSTOMER_ORDER_FLAG <> 'N' then
1433 dumm_status := INVPUOPI.mtl_log_interface_err(
1435 user_id,
1436 login_id,
1437 prog_appid,
1438 prog_id,
1439 request_id,
1440 cr.TRANSACTION_ID,
1441 error_msg,
1442 'BOM_ITEM_TYPE',
1443 'MTL_SYSTEM_ITEMS_INTERFACE',
1444 'INV_CUSTOMER',
1445 err_text);
1446 status := 1;
1447 if dumm_status < 0 then
1448 raise LOGGING_ERR;
1449 end if;
1450 end if;
1451
1452 if cr.BOM_ITEM_TYPE = 3 then
1453 if cr.CUSTOMER_ORDER_FLAG <> 'N' then
1454 dumm_status := INVPUOPI.mtl_log_interface_err(
1455 cr.organization_id,
1456 user_id,
1457 login_id,
1458 prog_appid,
1459 prog_id,
1460 request_id,
1461 cr.TRANSACTION_ID,
1462 error_msg,
1463 'BOM_ITEM_TYPE',
1464 'MTL_SYSTEM_ITEMS_INTERFACE',
1465 'INV_CUSTOMER',
1466 err_text);
1467 status := 1;
1468 if dumm_status < 0 then
1469 raise LOGGING_ERR;
1470 end if;
1471 end if;
1472 if cr.SHIPPABLE_ITEM_FLAG <> 'N' then
1473 dumm_status := INVPUOPI.mtl_log_interface_err(
1474 cr.organization_id,
1475 user_id,
1476 login_id,
1477 prog_appid,
1478 prog_id,
1479 request_id,
1480 cr.TRANSACTION_ID,
1481 error_msg,
1482 'SHIPPABLE_ITEM_FLAG',
1483 'MTL_SYSTEM_ITEMS_INTERFACE',
1484 'INV_SHIPPABLE',
1485 err_text);
1486 status := 1;
1487 if dumm_status < 0 then
1488 raise LOGGING_ERR;
1489 end if;
1490 end if;
1491 if cr.INTERNAL_ORDER_FLAG <> 'N' or
1492 cr.CUSTOMER_ORDER_FLAG <> 'N' or
1493 cr.PICK_COMPONENTS_FLAG <> 'N' or
1494 cr.REPLENISH_TO_ORDER_FLAG <> 'N' then
1495 dumm_status := INVPUOPI.mtl_log_interface_err(
1496 cr.organization_id,
1497 user_id,
1498 login_id,
1499 prog_appid,
1500 prog_id,
1501 request_id,
1502 cr.TRANSACTION_ID,
1503 error_msg,
1504 'BOM_ITEM_TYPE',
1505 'MTL_SYSTEM_ITEMS_INTERFACE',
1506 'INV_IOI_PLANNING_DEP',
1507 err_text);
1508 status := 1;
1509 if dumm_status < 0 then
1510 raise LOGGING_ERR;
1511 end if;
1512 end if;
1513 end if;
1514 if cr.BOM_ITEM_TYPE <> 4 then
1515 if cr.BUILD_IN_WIP_FLAG <> 'N' or
1516 cr.BASE_ITEM_ID is not NULL then
1517 dumm_status := INVPUOPI.mtl_log_interface_err(
1518 cr.organization_id,
1519 user_id,
1520 login_id,
1521 prog_appid,
1522 prog_id,
1523 request_id,
1524 cr.TRANSACTION_ID,
1525 error_msg,
1526 'BOM_ITEM_TYPE',
1527 'MTL_SYSTEM_ITEMS_INTERFACE',
1528 'INV_IOI_BOM_STANDARD',
1529 err_text);
1530 status := 1;
1531 if dumm_status < 0 then
1532 raise LOGGING_ERR;
1533 end if;
1534 end if;
1535 if cr.INTERNAL_ORDER_FLAG <> 'N' then
1536 --bug: 2731125 Modified the msgs for each bom item type
1537 l_msg_name := 'INV_INTERNAL';
1538 if cr.BOM_ITEM_TYPE = 1 then
1539 l_msg_name := 'INV_INTERNAL_MODEL' ;
1540 elsif cr.BOM_ITEM_TYPE = 2 then
1541 l_msg_name := 'INV_INTERNAL_OPTION_CLASS' ;
1542 elsif cr.BOM_ITEM_TYPE = 5 then
1543 l_msg_name := 'INV_INTERNAL_PRODUCT_FAMILY' ;
1544 end if;
1545 dumm_status := INVPUOPI.mtl_log_interface_err(
1546 cr.organization_id,
1547 user_id,
1548 login_id,
1549 prog_appid,
1550 prog_id,
1551 request_id,
1552 cr.TRANSACTION_ID,
1553 error_msg,
1554 'INTERNAL_ORDER_FLAG',
1555 'MTL_SYSTEM_ITEMS_INTERFACE',
1556 -- 'INV_INTERNAL',
1557 l_msg_name,
1561 raise LOGGING_ERR;
1558 err_text);
1559 status := 1;
1560 if dumm_status < 0 then
1562 end if;
1563 end if;
1564 end if;
1565
1566 -- validate stock_enabled_flag
1567 if cr.stock_enabled_flag = 'Y' and
1568 cr.INVENTORY_ITEM_FLAG <> 'Y' then
1569 dumm_status := INVPUOPI.mtl_log_interface_err(
1570 cr.organization_id,
1571 user_id,
1572 login_id,
1573 prog_appid,
1574 prog_id,
1575 request_id,
1576 cr.TRANSACTION_ID,
1577 error_msg,
1578 'STOCK_ENABLED_FLAG',
1579 'MTL_SYSTEM_ITEMS_INTERFACE',
1580 'INV_STOCKABLE',
1581 err_text);
1582 if dumm_status < 0 then
1583 raise LOGGING_ERR;
1584 end if;
1585 status := 1;
1586 end if;
1587
1588 -- validate inventory_item_flag
1589
1590 -- NP 24OCT96
1591 -- if inventory_item_flag Y then SERVICE_ITEM_FLAG has to be 'N'
1592 -- Bug; 2696647 Inventory Item and Service/Warranty/Usage Contract Item
1593 -- Types are mutually exclusive item types.
1594
1595 if cr.inventory_item_flag = 'Y' and
1596 --Bug: 2731125 cr.service_item_flag = 'Y' then
1597 cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE','WARRANTY','USAGE') then
1598 dumm_status := INVPUOPI.mtl_log_interface_err(
1599 cr.organization_id,
1600 user_id,
1601 login_id,
1602 prog_appid,
1603 prog_id,
1604 request_id,
1605 cr.TRANSACTION_ID,
1606 error_msg,
1607 'INVENTORY_ITEM_FLAG',
1608 'MTL_SYSTEM_ITEMS_INTERFACE',
1609 -- 'INV_ITM_SERVICE_ITM',
1610 'INV_NO_INVENTORY_ITEM',
1611 err_text);
1612 if dumm_status < 0 then
1613 raise LOGGING_ERR;
1614 end if;
1615 status := 1;
1616 end if;
1617
1618 -- NP 24OCT96
1619 -- if inventory_item_flag Y
1620 -- or (SERVICE_ITEM_FLAG and VENDOR_WARRANTY_FLAG are both Y )
1621 -- then BOM_ENABLED_FLAG can be anything; else it must be N
1622 /**Bug: 3546140 Removed this validation for PLM RBOMS
1623 if ( cr.inventory_item_flag = 'Y' or
1624 --Bug: 2731125 or ( cr.SERVICE_ITEM_FLAG = 'Y' and cr.VENDOR_WARRANTY_FLAG = 'Y'))
1625 cr.CONTRACT_ITEM_TYPE_CODE IS NOT NULL )
1626 then
1627 null; -- need not check BOM_ENABLED_FLAG
1628 else
1629 if cr.BOM_ENABLED_FLAG = 'Y' then
1630 dumm_status := INVPUOPI.mtl_log_interface_err(
1631 cr.organization_id,
1632 user_id,
1633 login_id,
1634 prog_appid,
1635 prog_id,
1636 request_id,
1637 cr.TRANSACTION_ID,
1638 error_msg,
1639 'INVENTORY_ITEM_FLAG',
1640 'MTL_SYSTEM_ITEMS_INTERFACE',
1641 'INV_BOM_ENABLED1',
1642 err_text);
1643 if dumm_status < 0 then
1644 raise LOGGING_ERR;
1645 end if;
1646 status := 1;
1647 end if;
1648 end if;
1649 **/
1650 if cr.inventory_item_flag = 'N' and
1651 cr.BUILD_IN_WIP_FLAG <> 'N' then
1652 dumm_status := INVPUOPI.mtl_log_interface_err(
1653 cr.organization_id,
1654 user_id,
1655 login_id,
1656 prog_appid,
1657 prog_id,
1658 request_id,
1659 cr.TRANSACTION_ID,
1660 error_msg,
1661 'INVENTORY_ITEM_FLAG',
1662 'MTL_SYSTEM_ITEMS_INTERFACE',
1663 'INV_BUILD_WIP_NO',
1664 err_text);
1665 if dumm_status < 0 then
1666 raise LOGGING_ERR;
1667 end if;
1668 status := 1;
1669 end if;
1670
1671 -- validate purchasing_item_flag
1672 if cr.purchasing_item_flag = 'N' and
1673 cr.PURCHASING_ENABLED_FLAG <> 'N' then
1674 dumm_status := INVPUOPI.mtl_log_interface_err(
1675 cr.organization_id,
1679 prog_id,
1676 user_id,
1677 login_id,
1678 prog_appid,
1680 request_id,
1681 cr.TRANSACTION_ID,
1682 error_msg,
1683 'PURCHASING_ITEM_FLAG',
1684 'MTL_SYSTEM_ITEMS_INTERFACE',
1685 'INV_PO_ENABLED',
1686 err_text);
1687 if dumm_status < 0 then
1688 raise LOGGING_ERR;
1689 end if;
1690 status := 1;
1691 end if;
1692
1693 -- validate customer_order_flag
1694 if cr.customer_order_flag = 'N' and
1695 cr.CUSTOMER_ORDER_ENABLED_FLAG <> 'N' then
1696 dumm_status := INVPUOPI.mtl_log_interface_err(
1697 cr.organization_id,
1698 user_id,
1699 login_id,
1700 prog_appid,
1701 prog_id,
1702 request_id,
1703 cr.TRANSACTION_ID,
1704 error_msg,
1705 'CUSTOMER_ORDER_FLAG',
1706 'MTL_SYSTEM_ITEMS_INTERFACE',
1707 'INV_CUSTOMER_ENABLED',
1708 err_text);
1709 if dumm_status < 0 then
1710 raise LOGGING_ERR;
1711 end if;
1712 status := 1;
1713 end if;
1714
1715 -- validate invoiceable_item_flag
1716 if cr.invoiceable_item_flag = 'N' and
1717 cr.INVOICE_ENABLED_FLAG <> 'N' then
1718 dumm_status := INVPUOPI.mtl_log_interface_err(
1719 cr.organization_id,
1720 user_id,
1721 login_id,
1722 prog_appid,
1723 prog_id,
1724 request_id,
1725 cr.TRANSACTION_ID,
1726 error_msg,
1727 'INVOICEABLE_ITEM_FLAG',
1728 'MTL_SYSTEM_ITEMS_INTERFACE',
1729 'INV_INVOICE_ENABLED',
1730 err_text);
1731 if dumm_status < 0 then
1732 raise LOGGING_ERR;
1733 end if;
1734 status := 1;
1735 end if;
1736 /*** Bug: 2731125 SERVICEABLE_COMPONENT_FLAG got obsoleted
1737 -- validate service_item_flag
1738 if cr.service_item_flag = 'Y' and
1739 cr.SERVICEABLE_COMPONENT_FLAG <> 'N'then
1740 dumm_status := INVPUOPI.mtl_log_interface_err(
1741 cr.organization_id,
1742 user_id,
1743 login_id,
1744 prog_appid,
1745 prog_id,
1746 request_id,
1747 cr.TRANSACTION_ID,
1748 error_msg,
1749 'SERVICE_ITEM_FLAG',
1750 'MTL_SYSTEM_ITEMS_INTERFACE',
1751 'BOM_OP_VALIDATION_ERR',
1752 err_text);
1753 if dumm_status < 0 then
1754 raise LOGGING_ERR;
1755 end if;
1756 status := 1;
1757 end if;
1758 ***/
1759 -- if cr.service_item_flag = 'Y' and
1760 if cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE','USAGE','WARRANTY') and
1761 cr.SERVICEABLE_PRODUCT_FLAG <> 'N' then
1762 dumm_status := INVPUOPI.mtl_log_interface_err(
1763 cr.organization_id,
1764 user_id,
1765 login_id,
1766 prog_appid,
1767 prog_id,
1768 request_id,
1769 cr.TRANSACTION_ID,
1770 error_msg,
1771 -- 'SERVICE_ITEM_FLAG',
1772 'CONTRACT_ITEM_TYPE_CODE',
1773 'MTL_SYSTEM_ITEMS_INTERFACE',
1774 -- 'INV_SERVICE',
1775 'INV_SERVICEABLE_CONTRACT',
1776 err_text);
1777 if dumm_status < 0 then
1778 raise LOGGING_ERR;
1779 end if;
1780 status := 1;
1781 end if;
1782
1783 /*NP 24OCT96 New Service related validations
1784 ** INVENTORY_ITEM_FLAG, service_item_flag validation already done
1785 ** in the INVENTORY_ITEM_FLAG section above
1786 */
1787 --Bug: 2731125 if cr.service_item_flag = 'Y' and
1788 --Bug: 2696647 SUBSCRIPTION Contract Items can be shippable and purchasable.
1789 if cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE','WARRANTY','USAGE') and
1790 (cr.CYCLE_COUNT_ENABLED_FLAG <> 'N'
1791 or cr.PURCHASING_ITEM_FLAG <> 'N'
1792 or cr.SHIPPABLE_ITEM_FLAG <> 'N'
1796 dumm_status := INVPUOPI.mtl_log_interface_err(
1793 or cr.RETURNABLE_FLAG <> 'N'
1794 or cr.material_billable_flag is not NULL
1795 or cr.ATP_FLAG <> 'N') then
1797 cr.organization_id,
1798 user_id,
1799 login_id,
1800 prog_appid,
1801 prog_id,
1802 request_id,
1803 cr.TRANSACTION_ID,
1804 error_msg,
1805 -- 'SERVICE_ITEM_FLAG',
1806 'CONTRACT_ITEM_TYPE_CODE',
1807 'MTL_SYSTEM_ITEMS_INTERFACE',
1808 'INV_SERVICE1',
1809 err_text);
1810 if dumm_status < 0 then
1811 raise LOGGING_ERR;
1812 end if;
1813 status := 1;
1814 end if;
1815
1816 /*NP 24OCT96 New Service related validations
1817 ** if service_item_flag is N then
1818 ** VENDOR_WARRANTY_FLAG has to be N
1819 ** and coverage_schedule_id has to be null
1820 ** and service_duration has to be ZERO
1821 ** and service_duration_period_code has to be null
1822 */
1823 --Bug: 2731125 if cr.service_item_flag = 'N' and
1824 if cr.CONTRACT_ITEM_TYPE_CODE IS NULL and
1825 /** ( cr.VENDOR_WARRANTY_FLAG <> 'N'
1826 or **/
1827 ( cr.coverage_schedule_id is not NULL
1828 or cr.service_duration <> 0
1829 or cr.service_duration_period_code is not NULL) then
1830 dumm_status := INVPUOPI.mtl_log_interface_err(
1831 cr.organization_id,
1832 user_id,
1833 login_id,
1834 prog_appid,
1835 prog_id,
1836 request_id,
1837 cr.TRANSACTION_ID,
1838 error_msg,
1839 -- 'SERVICE_ITEM_FLAG',
1840 'CONTRACT_ITEM_TYPE_CODE',
1841 'MTL_SYSTEM_ITEMS_INTERFACE',
1842 'INV_SERVICE2',
1843 err_text);
1844 if dumm_status < 0 then
1845 raise LOGGING_ERR;
1846 end if;
1847 status := 1;
1848 end if;
1849
1850 /*NP 24OCT96 New Service related validations
1851 ** if service_item_flag is Y and
1852 ** VENDOR_WARRANTY_FLAG is Y then
1853 ** service_duration_period_code mustbe not NULL
1854 */
1855 /**Bug: 2731125 if cr.service_item_flag = 'Y' and
1856 cr.VENDOR_WARRANTY_FLAG = 'Y' and
1857 **/
1858 -- Bug: 2811878 This validation will be in 11.5.10 in IOI
1859 if cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE','WARRANTY') and
1860 cr.service_duration_period_code is NULL then
1861 dumm_status := INVPUOPI.mtl_log_interface_err(
1862 cr.organization_id,
1863 user_id,
1864 login_id,
1865 prog_appid,
1866 prog_id,
1867 request_id,
1868 cr.TRANSACTION_ID,
1869 error_msg,
1870 -- 'SERVICE_ITEM_FLAG',
1871 'CONTRACT_ITEM_TYPE_CODE',
1872 'MTL_SYSTEM_ITEMS_INTERFACE',
1873 -- 'INV_SERVICE3',
1874 'INV_SER_DURATION_MAND',
1875 err_text);
1876 if dumm_status < 0 then
1877 raise LOGGING_ERR;
1878 end if;
1879 status := 1;
1880 end if;
1881
1882 /*Bug:3697824 removed this validation
1883 -- validate seviceable_product_flag
1884 if cr.SERVICEABLE_PRODUCT_FLAG = 'N'
1885 and cr.service_starting_delay <> 0 then
1886 dumm_status := INVPUOPI.mtl_log_interface_err(
1887 cr.organization_id,
1888 user_id,
1889 login_id,
1890 prog_appid,
1891 prog_id,
1892 request_id,
1893 cr.TRANSACTION_ID,
1894 error_msg,
1895 'SERVICEABLE_PRODUCT_FLAG',
1896 'MTL_SYSTEM_ITEMS_INTERFACE',
1897 'INV_SERVICE_PROD_FL',
1898 err_text);
1899 if dumm_status < 0 then
1900 raise LOGGING_ERR;
1901 end if;
1902 status := 1;
1903 end if;
1904 */
1905 -- validate pick_components_flag
1906 if cr.pick_components_flag='Y' then
1907 if cr.REPLENISH_TO_ORDER_FLAG <> 'N' or
1911 user_id,
1908 cr.BASE_ITEM_ID is not NULL then
1909 dumm_status := INVPUOPI.mtl_log_interface_err(
1910 cr.organization_id,
1912 login_id,
1913 prog_appid,
1914 prog_id,
1915 request_id,
1916 cr.TRANSACTION_ID,
1917 error_msg,
1918 'PICK_COMPONENTS_FLAG',
1919 'MTL_SYSTEM_ITEMS_INTERFACE',
1920 'INV_IOI_PICK_DEP',
1921 err_text);
1922 if dumm_status < 0 then
1923 raise LOGGING_ERR;
1924 end if;
1925 status := 1;
1926 end if;
1927 if cr.MRP_PLANNING_CODE <> 6 then
1928 dumm_status := INVPUOPI.mtl_log_interface_err(
1929 cr.organization_id,
1930 user_id,
1931 login_id,
1932 prog_appid,
1933 prog_id,
1934 request_id,
1935 cr.TRANSACTION_ID,
1936 error_msg,
1937 'PICK_COMPONENTS_FLAG',
1938 'MTL_SYSTEM_ITEMS_INTERFACE',
1939 'INV_MRP_PLANNING',
1940 err_text);
1941 if dumm_status < 0 then
1942 raise LOGGING_ERR;
1943 end if;
1944 status := 1;
1945 end if;
1946 end if;
1947
1948 -- validate replenish_to_order_flag
1949 if cr.replenish_to_order_flag ='Y' and (
1950 cr.PICK_COMPONENTS_FLAG = 'Y' or
1951 cr.BOM_ITEM_TYPE = 3 ) then
1952 dumm_status := INVPUOPI.mtl_log_interface_err(
1953 cr.organization_id,
1954 user_id,
1955 login_id,
1956 prog_appid,
1957 prog_id,
1958 request_id,
1959 cr.TRANSACTION_ID,
1960 error_msg,
1961 'REPLENISH_TO_ORDER',
1962 'MTL_SYSTEM_ITEMS_INTERFACE',
1963 'INV_IOI_REPL_TO_ORDER_DEP',
1964 err_text);
1965 if dumm_status < 0 then
1966 raise LOGGING_ERR;
1967 end if;
1968 status := 1;
1969 end if;
1970
1971 -- validate lot_control_code
1972 lot_num_generation_val := NULL;
1973 select lot_number_generation into lot_num_generation_val
1974 from mtl_parameters
1975 where organization_id = cr.organization_id
1976 and rownum =1; /*NP 21DEC94 */
1977
1978 -- Bug 3333917 : Message name corrected - Anmurali
1979 if cr.lot_control_code = 2 and
1980 lot_num_generation_val = 2 and
1981 cr.AUTO_LOT_ALPHA_PREFIX is NULL then
1982 dumm_status := INVPUOPI.mtl_log_interface_err(
1983 cr.organization_id,
1984 user_id,
1985 login_id,
1986 prog_appid,
1987 prog_id,
1988 request_id,
1989 cr.TRANSACTION_ID,
1990 error_msg,
1991 'LOT_CONTROL_CODE',
1992 'MTL_SYSTEM_ITEMS_INTERFACE',
1993 'INV_AUTO_LOT_PREFIX',
1994 err_text);
1995 if dumm_status < 0 then
1996 raise LOGGING_ERR;
1997 end if;
1998 status := 1;
1999 end if;
2000
2001 -- validate lot_control_code
2002 lot_num_generation_val := NULL;
2003 select lot_number_generation into lot_num_generation_val
2004 from mtl_parameters
2005 where organization_id = cr.organization_id
2006 and rownum =1; /*NP 21DEC94 */
2007
2008 --3296460:START_AUTO_LOT_NUMBER should be number.
2009 if cr.START_AUTO_LOT_NUMBER IS NOT NULL then
2010 begin
2011
2012 SELECT TO_CHAR(TO_NUMBER(cr.START_AUTO_LOT_NUMBER))
2013 INTO l_start_auto_lot_num
2014 FROM DUAL;
2015
2016 exception
2017 when others then
2018 --Catch ORA-01722: invalid number and raise a error message.
2019 dumm_status := INVPUOPI.mtl_log_interface_err(
2020 cr.organization_id,
2021 user_id,
2022 login_id,
2023 prog_appid,
2024 prog_id,
2025 request_id,
2029 'MTL_SYSTEM_ITEMS_INTERFACE',
2026 cr.TRANSACTION_ID,
2027 error_msg,
2028 'START_AUTO_LOT_NUMBER',
2030 'INV_START_AUTO_LOT_INVALID_NUM',
2031 err_text);
2032 if dumm_status < 0 then
2033 raise LOGGING_ERR;
2034 end if;
2035 status := 1;
2036 end;
2037 end if;
2038
2039 -- Bug 3333917 : Message name corrected - Anmurali
2040 if cr.lot_control_code = 2 and
2041 lot_num_generation_val = 2 and
2042 cr.START_AUTO_LOT_NUMBER is NULL then
2043 dumm_status := INVPUOPI.mtl_log_interface_err(
2044 cr.organization_id,
2045 user_id,
2046 login_id,
2047 prog_appid,
2048 prog_id,
2049 request_id,
2050 cr.TRANSACTION_ID,
2051 error_msg,
2052 'LOT_CONTROL_CODE',
2053 'MTL_SYSTEM_ITEMS_INTERFACE',
2054 'INV_START_LOT_NUM',
2055 err_text);
2056 if dumm_status < 0 then
2057 raise LOGGING_ERR;
2058 end if;
2059 status := 1;
2060 end if;
2061
2062 -- validate serial_number_control_code
2063 ser_num_generation_val := NULL;
2064 select serial_number_generation into ser_num_generation_val
2065 from mtl_parameters
2066 where organization_id = cr.organization_id
2067 and rownum =1; /*NP 21DEC94 */
2068
2069 if cr.serial_number_control_code = 2 and
2070 ser_num_generation_val = 2 and
2071 cr.START_AUTO_SERIAL_NUMBER is NULL then
2072 dumm_status := INVPUOPI.mtl_log_interface_err(
2073 cr.organization_id,
2074 user_id,
2075 login_id,
2076 prog_appid,
2077 prog_id,
2078 request_id,
2079 cr.TRANSACTION_ID,
2080 error_msg,
2081 'SERIAL_NUMBER_CONTROL_CODE',
2082 'MTL_SYSTEM_ITEMS_INTERFACE',
2083 'INV_START_SERIAL_NUM',
2084 err_text);
2085 if dumm_status < 0 then
2086 raise LOGGING_ERR;
2087 end if;
2088 status := 1;
2089 end if;
2090
2091 -- validate serial_number_control_code
2092 ser_num_generation_val := NULL;
2093 select serial_number_generation into ser_num_generation_val
2094 from mtl_parameters
2095 where organization_id = cr.organization_id
2096 and rownum =1; /*NP 21DEC94 */
2097
2098 if cr.serial_number_control_code = 2 and
2099 ser_num_generation_val = 2 and
2100 cr.AUTO_SERIAL_ALPHA_PREFIX is NULL then
2101 dumm_status := INVPUOPI.mtl_log_interface_err(
2102 cr.organization_id,
2103 user_id,
2104 login_id,
2105 prog_appid,
2106 prog_id,
2107 request_id,
2108 cr.TRANSACTION_ID,
2109 error_msg,
2110 'SERIAL_NUMBER_CONTROL_CODE',
2111 'MTL_SYSTEM_ITEMS_INTERFACE',
2112 'INV_AUTO_SERIAL_PREFIX',
2113 err_text);
2114 if dumm_status < 0 then
2115 raise LOGGING_ERR;
2116 end if;
2117 status := 1;
2118 end if;
2119
2120 -- validate shelf_life_code
2121 if cr.shelf_life_code = 2 and
2122 cr.SHELF_LIFE_DAYS is NULL then
2123 dumm_status := INVPUOPI.mtl_log_interface_err(
2124 cr.organization_id,
2125 user_id,
2126 login_id,
2127 prog_appid,
2128 prog_id,
2129 request_id,
2130 cr.TRANSACTION_ID,
2131 error_msg,
2132 'SHELF_LIFE_CODE',
2133 'MTL_SYSTEM_ITEMS_INTERFACE',
2134 'INV_SHELF_DAYS_MAND',
2135 err_text);
2136 if dumm_status < 0 then
2137 raise LOGGING_ERR;
2138 end if;
2139 status := 1;
2140 end if;
2141
2142 -- validate OUTSIDE_OPERATION_FLAG
2146 cr.organization_id,
2143 if cr.OUTSIDE_OPERATION_FLAG = 'Y' then
2144 if cr.purchasing_item_flag ='N' then
2145 dumm_status := INVPUOPI.mtl_log_interface_err(
2147 user_id,
2148 login_id,
2149 prog_appid,
2150 prog_id,
2151 request_id,
2152 cr.TRANSACTION_ID,
2153 error_msg,
2154 'OUTSIDE_OPERATION_FLAG',
2155 'MTL_SYSTEM_ITEMS_INTERFACE',
2156 'INV_IOI_OUT_OP_FLAG_DEP',
2157 err_text);
2158 if dumm_status < 0 then
2159 raise LOGGING_ERR;
2160 end if;
2161 status := 1;
2162 end if;
2163 if cr.OUTSIDE_OPERATION_UOM_TYPE is NULL then
2164 dumm_status := INVPUOPI.mtl_log_interface_err(
2165 cr.organization_id,
2166 user_id,
2167 login_id,
2168 prog_appid,
2169 prog_id,
2170 request_id,
2171 cr.TRANSACTION_ID,
2172 error_msg,
2173 'OUTSIDE_OPERATION_FLAG',
2174 'MTL_SYSTEM_ITEMS_INTERFACE',
2175 'INV_OUTSIDE_OP_UNIT',
2176 err_text);
2177 if dumm_status < 0 then
2178 raise LOGGING_ERR;
2179 end if;
2180 status := 1;
2181
2182 end if;
2183 end if;
2184
2185 /* Fix for bug 5844510- Commented all the source org related validations
2186 as they are redundant. These are done in INVPVD6B.pls
2187
2188 -- Validate SOURCE_ORGANIZATION_ID
2189 --
2190 if (cr.SOURCE_ORGANIZATION_ID is NOT NULL) then
2191
2192 if (cr.SOURCE_ORGANIZATION_ID <> cr.ORGANIZATION_ID) then
2193
2194 -- Check if the item exists in the source organization.
2195 --
2196 begin
2197 select 'item_in_source_org'
2198 into l_dummy
2199 from mtl_system_items_b
2200 where inventory_item_id = l_item_id
2201 and organization_id = cr.source_organization_id;
2202
2203 exception
2204 when no_data_found then
2205 dumm_status := INVPUOPI.mtl_log_interface_err(
2206 cr.organization_id,
2207 user_id,
2208 login_id,
2209 prog_appid,
2210 prog_id,
2211 request_id,
2212 cr.TRANSACTION_ID,
2213 error_msg,
2214 'SOURCE_ORGANIZATION_ID',
2215 'MTL_SYSTEM_ITEMS_INTERFACE',
2216 'INV_ITEM_IN_SOURCE_ORG',
2217 err_text);
2218 if dumm_status < 0 then
2219 raise LOGGING_ERR;
2220 end if;
2221 status := 1;
2222 end;
2223
2224 begin
2225 Select 'inter-org network defined'
2226 into l_dummy
2227 from mtl_interorg_parameters
2228 where to_organization_id = cr.organization_id
2229 and from_organization_id = cr.source_organization_id;
2230
2231 exception
2232 when no_data_found then
2233 dumm_status := INVPUOPI.mtl_log_interface_err(
2234 cr.organization_id,
2235 user_id,
2236 login_id,
2237 prog_appid,
2238 prog_id,
2239 request_id,
2240 cr.TRANSACTION_ID,
2241 error_msg,
2242 'SOURCE_ORGANIZATION_ID',
2243 'MTL_SYSTEM_ITEMS_INTERFACE',
2244 'INV_INTERORG_NTWK',
2245 err_text);
2246 if dumm_status < 0 then
2247 raise LOGGING_ERR;
2248 end if;
2249 status := 1;
2250 end;
2251 end if;
2252
2253 If cr.SOURCE_ORGANIZATION_ID = cr.ORGANIZATION_ID and
2254 cr.mrp_planning_code = 3 then
2255 begin
2256 select 'nettable or null source sub'
2257 into l_dummy
2258 from mtl_secondary_inventories
2259 where secondary_inventory_name =
2263 and rownum < 2 ;
2260 nvl(cr.source_subinventory,
2261 secondary_inventory_name)
2262 and availability_type = 1
2264 dumm_status := INVPUOPI.mtl_log_interface_err(
2265 cr.organization_id,
2266 user_id,
2267 login_id,
2268 prog_appid,
2269 prog_id,
2270 request_id,
2271 cr.TRANSACTION_ID,
2272 error_msg,
2273 'SOURCE_ORGANIZATION_ID',
2274 'MTL_SYSTEM_ITEMS_INTERFACE',
2275 'INV_INTRAORG_SOURCE',
2276 err_text);
2277 if dumm_status < 0 then
2278 raise LOGGING_ERR;
2279 end if;
2280 status := 1;
2281 Exception
2282 When no_data_found then
2283 null;
2284 end ;
2285 End if ;
2286
2287 end if;
2288
2289 -- validate SOURCE_TYPE/ SOURCE_ORGANIZATION_ID
2290 -- 13-FEB-98, Source Organization Not Mandatory for source_type 1
2291 -- 15-APR-98, Source Org/Sub Should be Null if source_type <> 1
2292 IF (cr.source_type = 2 OR
2293 cr.source_type IS NULL) AND
2294 (cr.SOURCE_ORGANIZATION_ID is NOT NULL) THEN
2295 dumm_status := INVPUOPI.mtl_log_interface_err(
2296 cr.organization_id,
2297 user_id,
2298 login_id,
2299 prog_appid,
2300 prog_id,
2301 request_id,
2302 cr.TRANSACTION_ID,
2303 error_msg,
2304 'SOURCE_TYPE',
2305 'MTL_SYSTEM_ITEMS_INTERFACE',
2306 'INV_SOURCE_ORG_MUST_BE_NULL',
2307 err_text);
2308 if dumm_status < 0 then
2309 raise LOGGING_ERR;
2310 end if;
2311 status := 1;
2312 END IF;
2313
2314 End of commented code for fixing bug 5844510 */
2315
2316 -- validate MINIMUM_ORDER_QUANTITY
2317 if cr.MINIMUM_ORDER_QUANTITY < 0 or
2318 cr.MINIMUM_ORDER_QUANTITY >
2319 nvl(cr.maximum_order_quantity, cr.MINIMUM_ORDER_QUANTITY) then
2320 dumm_status := INVPUOPI.mtl_log_interface_err(
2321 cr.organization_id,
2322 user_id,
2323 login_id,
2324 prog_appid,
2325 prog_id,
2326 request_id,
2327 cr.TRANSACTION_ID,
2328 error_msg,
2329 'MINIMUM_ORDER_QUANTITY',
2330 'MTL_SYSTEM_ITEMS_INTERFACE',
2331 'INV_IOI_MIN_ORD_QTY',
2332 err_text);
2333 if dumm_status < 0 then
2334 raise LOGGING_ERR;
2335 end if;
2336 status := 1;
2337 end if;
2338
2339 -- validate MAXIMUM_ORDER_QUANTITY
2340
2341 if cr.MAXIMUM_ORDER_QUANTITY < 0 or
2342 cr.MAXIMUM_ORDER_QUANTITY <
2343 nvl(cr.minimum_order_quantity, cr.MAXIMUM_ORDER_QUANTITY) then
2344 dumm_status := INVPUOPI.mtl_log_interface_err(
2345 cr.organization_id,
2346 user_id,
2347 login_id,
2348 prog_appid,
2349 prog_id,
2350 request_id,
2351 cr.TRANSACTION_ID,
2352 error_msg,
2353 'MAXIMUM_ORDER_QUANTITY',
2354 'MTL_SYSTEM_ITEMS_INTERFACE',
2355 'INV_IOI_MAX_ORD_QTY',
2356 err_text);
2357 if dumm_status < 0 then
2358 raise LOGGING_ERR;
2359 end if;
2360 status := 1;
2361 end if;
2362
2363 -- validate MIN_MINMAX_QUANTITY
2364 if cr.MIN_MINMAX_QUANTITY < 0 or
2365 cr.MIN_MINMAX_QUANTITY >
2366 nvl(cr.max_minmax_quantity, cr.MIN_MINMAX_QUANTITY +1 ) then
2367 dumm_status := INVPUOPI.mtl_log_interface_err(
2368 cr.organization_id,
2369 user_id,
2373 request_id,
2370 login_id,
2371 prog_appid,
2372 prog_id,
2374 cr.TRANSACTION_ID,
2375 error_msg,
2376 'MIN_MINMAX_QUANTITY',
2377 'MTL_SYSTEM_ITEMS_INTERFACE',
2378 'INV_IOI_MINMAX_MIN',
2379 err_text);
2380 if dumm_status < 0 then
2381 raise LOGGING_ERR;
2382 end if;
2383 status := 1;
2384 end if;
2385
2386 -- validate MAX_MINMAX_QUANTITY
2387 if cr.MAX_MINMAX_QUANTITY < nvl(cr.min_minmax_quantity,0) then
2388 dumm_status := INVPUOPI.mtl_log_interface_err(
2389 cr.organization_id,
2390 user_id,
2391 login_id,
2392 prog_appid,
2393 prog_id,
2394 request_id,
2395 cr.TRANSACTION_ID,
2396 error_msg,
2397 'MAX_MINMAX_QUANTITY',
2398 'MTL_SYSTEM_ITEMS_INTERFACE',
2399 'INV_IOI_MINMAX_MAX',
2400 err_text);
2401 if dumm_status < 0 then
2402 raise LOGGING_ERR;
2403 end if;
2404 status := 1;
2405 end if;
2406
2407 -- validate check_shortages_flag
2408 if cr.CHECK_SHORTAGES_FLAG = 'Y' and
2409 cr.MTL_TRANSACTIONS_ENABLED_FLAG <> 'Y' then
2410 dumm_status := INVPUOPI.mtl_log_interface_err(
2411 cr.organization_id,
2412 user_id,
2413 login_id,
2414 prog_appid,
2415 prog_id,
2416 request_id,
2417 cr.TRANSACTION_ID,
2418 error_msg,
2419 'CHECK_SHORTAGES_FLAG',
2420 'MTL_SYSTEM_ITEMS_INTERFACE',
2421 'INV_ENABLE_SHORT_CHECK',
2422 err_text);
2423 if dumm_status < 0 then
2424 raise LOGGING_ERR;
2425 end if;
2426 status := 1;
2427 end if;
2428 if cr.CONTRACT_ITEM_TYPE_CODE ='WARRANTY' and
2429 cr.CUSTOMER_ORDER_FLAG = 'Y' then
2430 dumm_status := INVPUOPI.mtl_log_interface_err(
2431 cr.organization_id,
2432 user_id,
2433 login_id,
2434 prog_appid,
2435 prog_id,
2436 request_id,
2437 cr.TRANSACTION_ID,
2438 error_msg,
2439 'CONTRACT_ITEM_TYPE_CODE',
2440 'MTL_SYSTEM_ITEMS_INTERFACE',
2441 'INV_CUSTOMER_CONTRACT',
2442 err_text);
2443 if dumm_status < 0 then
2444 raise LOGGING_ERR;
2445 end if;
2446 status := 1;
2447 end if;
2448
2449 /* R12 C Unit Weight can now be updated for Pending items. Moving the below set of validations to INVPVHDR */
2450 IF cr.WEIGHT_UOM_CODE IS NOT NULL THEN
2451 BEGIN
2452 SELECT 'x' INTO temp
2456 WHEN NO_DATA_FOUND THEN
2453 FROM MTL_UNITS_OF_MEASURE
2454 WHERE UOM_CODE = cr.WEIGHT_UOM_CODE;
2455 EXCEPTION
2457 dumm_status := INVPUOPI.mtl_log_interface_err(
2458 cr.organization_id,
2459 user_id,
2460 login_id,
2461 prog_appid,
2462 prog_id,
2463 request_id,
2464 cr.TRANSACTION_ID,
2465 error_msg,
2466 'WEIGHT_UOM_CODE',
2467 'MTL_SYSTEM_ITEMS_INTERFACE',
2468 'INV_IOI_WEIGHT_UOM_CODE',
2469 err_text);
2470 IF dumm_status < 0 THEN
2471 raise LOGGING_ERR;
2472 END IF;
2473 status := 1;
2474 END;
2475 END IF;
2476
2477 /* Moved the Weight UOM Code and Unit Weight validations from INVPVDR4 and INVPVDR5 */
2478 l_col_name := NULL;
2479 l_msg_name := NULL;
2480
2481 IF cr.WEIGHT_UOM_CODE IS NULL
2482 AND (cr.UNIT_WEIGHT IS NOT NULL OR cr.MAXIMUM_LOAD_WEIGHT IS NOT NULL) THEN --Bug: 3503944
2483 l_col_name := 'WEIGHT_UOM_CODE';
2484 l_msg_name := 'INV_IOI_WEIGHT_UOM_MISSING';
2485 ELSIF cr.VOLUME_UOM_CODE IS NULL
2486 AND (cr.UNIT_VOLUME IS NOT NULL OR cr.INTERNAL_VOLUME IS NOT NULL) THEN --Bug: 3503944
2487 l_col_name := 'VOLUME_UOM_CODE';
2488 l_msg_name := 'INV_IOI_VOLUME_UOM_MISSING';
2489 ELSIF cr.DIMENSION_UOM_CODE IS NULL
2490 AND (cr.UNIT_LENGTH IS NOT NULL
2491 OR cr.UNIT_WIDTH IS NOT NULL
2492 OR cr.UNIT_HEIGHT IS NOT NULL)
2493 THEN
2494 l_col_name := 'DIMENSION_UOM_CODE';
2495 l_msg_name := 'INV_IOI_DIMENSION_UOM_MISSING';
2496 END IF;
2497
2498 IF l_col_name IS NOT NULL THEN
2499 dumm_status := INVPUOPI.mtl_log_interface_err
2500 (cr.organization_id,
2501 user_id,
2502 login_id,
2503 prog_appid,
2504 prog_id,
2505 request_id,
2506 cr.TRANSACTION_ID,
2507 error_msg,
2508 l_col_name,
2509 'MTL_SYSTEM_ITEMS_INTERFACE',
2510 l_msg_name,
2511 err_text );
2512 IF dumm_status < 0 THEN
2513 raise LOGGING_ERR;
2514 END IF;
2515 status := 1;
2516 END IF;
2517
2518 -- Validate INDIVISIBLE_FLAG
2519 --
2520 /* Independent attribute
2521 */
2522
2523
2524 /* NP26DEC94 : New code to update process_flag.
2525 ** This modified/new code necessiated due to the breaking up INVPVHDR into
2526 ** 6 smaller packages to overcome PL/SQL limitations with code size.
2527 ** Let's update the process flag for the record
2528 ** Give it value 41 if all okay and 31 if some validation failed in this procedure
2529 ** The process flag values that are possible at this time are
2530 ** 2 set by the previous procedure.
2531 ** Since this is the first validation procedure..the logic is a little different
2532 ** from the 5 other validation procedures in files
2533 ** INVPVDR2.sql thru INVPVDR6.sql
2534 */
2535
2536 --R12C WHERE clause changed to ROWID
2537 update MTL_SYSTEM_ITEMS_INTERFACE
2538 set process_flag = DECODE(status,0,41,31),
2539 PRIMARY_UOM_CODE = cr.primary_uom_code,
2540 primary_unit_of_measure = cr.primary_unit_of_measure
2541 where rowid = cr.rowid;
2542
2543 end loop;
2544
2545 return(0);
2546
2547 exception
2548 when LOGGING_ERR then
2549 return(dumm_status);
2550 when VALIDATE_ERR then
2551 dumm_status := INVPUOPI.mtl_log_interface_err(
2552 l_org_id,
2553 user_id,
2554 login_id,
2555 prog_appid,
2556 prog_id,
2557 request_id,
2558 trans_id,
2559 err_text,
2560 'validation_error ' || stmt,
2561 'MTL_SYSTEM_ITEMS_INTERFACE',
2562 'BOM_OP_VALIDATION_ERR',
2563 err_text);
2564 return(status);
2565 when OTHERS then
2566 err_text := substr('INVPVALI.validate_item_header' || SQLERRM , 1, 240);
2567 return(SQLCODE);
2568
2569 end validate_item_header;
2570
2571
2572 end INVPVHDR;