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