[Home] [Help]
PACKAGE BODY: APPS.INVPVDR5
Source
1 package body INVPVDR5 as
2 /* $Header: INVPVD5B.pls 120.15.12020000.4 2013/01/23 08:42:58 lmai ship $ */
3 -- private function to get the message text
4 -- created for fixing 3108469
5 function get_msg_text (p_message_name IN VARCHAR2
6 ,p_organization_id IN NUMBER)
7 RETURN VARCHAR2 IS
8 l_org_name HR_ALL_ORGANIZATION_UNITS_VL.name%TYPE;
9 l_msg_text fnd_new_messages.message_text%TYPE;
10 BEGIN
11
12 BEGIN
13 SELECT name
14 INTO l_org_name
15 FROM hr_all_organization_units_vl
16 WHERE organization_id = p_organization_id;
17 EXCEPTION
18 WHEN OTHERS THEN
19 l_org_name := p_organization_id;
20 END;
21 FND_MESSAGE.SET_NAME ('INV', p_message_name);
22 FND_MESSAGE.SET_TOKEN ('ORGANIZATION', l_org_name);
23 l_msg_text := FND_MESSAGE.GET;
24 RETURN l_msg_text;
25 EXCEPTION
26 WHEN OTHERS THEN
27 RETURN p_message_name;
28 END get_msg_text;
29
30 function validate_item_header5
31 (
32 org_id number,
33 all_org NUMBER := 2,
34 prog_appid NUMBER := -1,
35 prog_id NUMBER := -1,
36 request_id NUMBER := -1,
37 user_id NUMBER := -1,
38 login_id NUMBER := -1,
39 err_text in out NOCOPY varchar2,
40 xset_id IN NUMBER DEFAULT -999
41 )
42 return integer
43 is
44 /******************************************************/
45 /* Variable required for the validation for UPDATES */
46 /******************************************************/
47 loc_ctrl_code NUMBER;
48 cost_flag VARCHAR2(1);
49 inv_asset_flag VARCHAR2(1);
50 mrp_stock_code NUMBER;
51 base_item NUMBER;
52 lead_lot_size NUMBER;
53 out_op_flag VARCHAR2(1);
54 shelf_code NUMBER;
55 temp VARCHAR2(2);
56 temp_uom_code VARCHAR2(3);
57 temp_u_o_m VARCHAR2(25);
58 temp_uom_class VARCHAR2(10);
59 temp_enabled_flag VARCHAR2(1);
60 temp_base_uom_flag VARCHAR2(1);
61 temp_uom_valid BOOLEAN; /* Added bug 14104913 */
62
63
64 /*
65 ** Retrieve column values for validation
66 */
67 CURSOR cc is
68 select
69 ROWID,
70 TRANSACTION_ID,
71 ORGANIZATION_ID,
72 TRANSACTION_TYPE,
73 PROCESS_FLAG,
74 INVENTORY_ITEM_ID,
75 SUMMARY_FLAG,
76 ENABLED_FLAG,
77 START_DATE_ACTIVE,
78 END_DATE_ACTIVE,
79 DESCRIPTION,
80 BUYER_ID,
81 ACCOUNTING_RULE_ID,
82 INVOICING_RULE_ID,
83 SEGMENT1,
84 SEGMENT2,
85 SEGMENT3,
86 SEGMENT4,
87 SEGMENT5,
88 SEGMENT6,
89 SEGMENT7,
90 SEGMENT8,
91 SEGMENT9,
92 SEGMENT10,
93 SEGMENT11,
94 SEGMENT12,
95 SEGMENT13,
96 SEGMENT14,
97 SEGMENT15,
98 SEGMENT16,
99 SEGMENT17,
100 SEGMENT18,
101 SEGMENT19,
102 SEGMENT20,
103 ATTRIBUTE_CATEGORY,
104 ATTRIBUTE1,
105 ATTRIBUTE2,
106 ATTRIBUTE3,
107 ATTRIBUTE4,
108 ATTRIBUTE5,
109 ATTRIBUTE6,
110 ATTRIBUTE7,
111 ATTRIBUTE8,
112 ATTRIBUTE9,
113 ATTRIBUTE10,
114 ATTRIBUTE11,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
118 ATTRIBUTE15,
119 PURCHASING_ITEM_FLAG,
120 SHIPPABLE_ITEM_FLAG,
121 CUSTOMER_ORDER_FLAG,
122 INTERNAL_ORDER_FLAG,
123 SERVICE_ITEM_FLAG,
124 INVENTORY_ITEM_FLAG,
125 ENG_ITEM_FLAG,
126 INVENTORY_ASSET_FLAG,
127 PURCHASING_ENABLED_FLAG,
128 CUSTOMER_ORDER_ENABLED_FLAG,
129 INTERNAL_ORDER_ENABLED_FLAG,
130 SO_TRANSACTIONS_FLAG,
131 MTL_TRANSACTIONS_ENABLED_FLAG,
132 STOCK_ENABLED_FLAG,
133 BOM_ENABLED_FLAG,
134 BUILD_IN_WIP_FLAG,
135 REVISION_QTY_CONTROL_CODE,
136 ITEM_CATALOG_GROUP_ID,
137 CATALOG_STATUS_FLAG,
138 RETURNABLE_FLAG,
139 DEFAULT_SHIPPING_ORG,
140 COLLATERAL_FLAG,
141 TAXABLE_FLAG,
142 QTY_RCV_EXCEPTION_CODE,
143 ALLOW_ITEM_DESC_UPDATE_FLAG,
144 INSPECTION_REQUIRED_FLAG,
145 RECEIPT_REQUIRED_FLAG,
146 MARKET_PRICE,
147 HAZARD_CLASS_ID,
148 RFQ_REQUIRED_FLAG,
149 QTY_RCV_TOLERANCE,
150 LIST_PRICE_PER_UNIT,
151 UN_NUMBER_ID,
152 PRICE_TOLERANCE_PERCENT,
153 ASSET_CATEGORY_ID,
154 ROUNDING_FACTOR,
155 UNIT_OF_ISSUE,
156 ENFORCE_SHIP_TO_LOCATION_CODE,
157 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
158 ALLOW_UNORDERED_RECEIPTS_FLAG,
159 ALLOW_EXPRESS_DELIVERY_FLAG,
160 DAYS_EARLY_RECEIPT_ALLOWED,
161 DAYS_LATE_RECEIPT_ALLOWED,
162 RECEIPT_DAYS_EXCEPTION_CODE,
163 RECEIVING_ROUTING_ID,
164 INVOICE_CLOSE_TOLERANCE,
165 RECEIVE_CLOSE_TOLERANCE,
166 AUTO_LOT_ALPHA_PREFIX,
167 START_AUTO_LOT_NUMBER,
168 LOT_CONTROL_CODE,
169 SHELF_LIFE_CODE,
170 SHELF_LIFE_DAYS,
171 SERIAL_NUMBER_CONTROL_CODE,
172 START_AUTO_SERIAL_NUMBER,
173 AUTO_SERIAL_ALPHA_PREFIX,
174 SOURCE_TYPE,
175 SOURCE_ORGANIZATION_ID,
176 SOURCE_SUBINVENTORY,
177 EXPENSE_ACCOUNT,
178 ENCUMBRANCE_ACCOUNT,
179 RESTRICT_SUBINVENTORIES_CODE,
180 UNIT_WEIGHT,
181 WEIGHT_UOM_CODE,
182 VOLUME_UOM_CODE,
183 UNIT_VOLUME,
184 DIMENSION_UOM_CODE,
185 UNIT_LENGTH,
186 UNIT_WIDTH,
187 UNIT_HEIGHT,
188 RESTRICT_LOCATORS_CODE,
189 LOCATION_CONTROL_CODE,
190 SHRINKAGE_RATE,
191 ACCEPTABLE_EARLY_DAYS,
192 PLANNING_TIME_FENCE_CODE,
193 DEMAND_TIME_FENCE_CODE,
194 LEAD_TIME_LOT_SIZE,
195 STD_LOT_SIZE,
196 CUM_MANUFACTURING_LEAD_TIME,
197 OVERRUN_PERCENTAGE,
198 MRP_CALCULATE_ATP_FLAG,
199 ACCEPTABLE_RATE_INCREASE,
200 ACCEPTABLE_RATE_DECREASE,
201 CUMULATIVE_TOTAL_LEAD_TIME,
202 PLANNING_TIME_FENCE_DAYS,
203 DEMAND_TIME_FENCE_DAYS,
204 END_ASSEMBLY_PEGGING_FLAG,
205 REPETITIVE_PLANNING_FLAG,
206 PLANNING_EXCEPTION_SET,
207 BOM_ITEM_TYPE,
208 PICK_COMPONENTS_FLAG,
209 REPLENISH_TO_ORDER_FLAG,
210 BASE_ITEM_ID,
211 ATP_COMPONENTS_FLAG,
212 ATP_FLAG,
213 FIXED_LEAD_TIME,
214 VARIABLE_LEAD_TIME,
215 WIP_SUPPLY_LOCATOR_ID,
216 WIP_SUPPLY_TYPE,
217 WIP_SUPPLY_SUBINVENTORY,
218 PRIMARY_UOM_CODE,
219 PRIMARY_UNIT_OF_MEASURE,
220 ALLOWED_UNITS_LOOKUP_CODE,
221 COST_OF_SALES_ACCOUNT,
222 SALES_ACCOUNT,
223 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
224 INVENTORY_ITEM_STATUS_CODE,
225 INVENTORY_PLANNING_CODE,
226 PLANNER_CODE,
227 PLANNING_MAKE_BUY_CODE,
228 FIXED_LOT_MULTIPLIER,
229 ROUNDING_CONTROL_TYPE,
230 CARRYING_COST,
231 POSTPROCESSING_LEAD_TIME,
232 PREPROCESSING_LEAD_TIME,
233 FULL_LEAD_TIME,
234 ORDER_COST,
235 MRP_SAFETY_STOCK_PERCENT,
236 MRP_SAFETY_STOCK_CODE,
237 MIN_MINMAX_QUANTITY,
238 MAX_MINMAX_QUANTITY,
239 MINIMUM_ORDER_QUANTITY,
240 FIXED_ORDER_QUANTITY,
241 FIXED_DAYS_SUPPLY,
242 MAXIMUM_ORDER_QUANTITY,
243 ATP_RULE_ID,
244 PICKING_RULE_ID,
245 RESERVABLE_TYPE,
246 POSITIVE_MEASUREMENT_ERROR,
247 NEGATIVE_MEASUREMENT_ERROR,
248 ENGINEERING_ECN_CODE,
249 ENGINEERING_ITEM_ID,
250 ENGINEERING_DATE,
251 SERVICE_STARTING_DELAY,
252 VENDOR_WARRANTY_FLAG,
253 SERVICEABLE_COMPONENT_FLAG,
254 SERVICEABLE_PRODUCT_FLAG,
255 BASE_WARRANTY_SERVICE_ID,
256 PAYMENT_TERMS_ID,
257 PREVENTIVE_MAINTENANCE_FLAG,
258 PRIMARY_SPECIALIST_ID,
259 SECONDARY_SPECIALIST_ID,
260 SERVICEABLE_ITEM_CLASS_ID,
261 TIME_BILLABLE_FLAG,
262 MATERIAL_BILLABLE_FLAG,
263 EXPENSE_BILLABLE_FLAG,
264 PRORATE_SERVICE_FLAG,
265 COVERAGE_SCHEDULE_ID,
266 SERVICE_DURATION_PERIOD_CODE,
267 SERVICE_DURATION,
268 WARRANTY_VENDOR_ID,
269 MAX_WARRANTY_AMOUNT,
270 RESPONSE_TIME_PERIOD_CODE,
271 RESPONSE_TIME_VALUE,
272 NEW_REVISION_CODE,
273 INVOICEABLE_ITEM_FLAG,
274 TAX_CODE,
275 INVOICE_ENABLED_FLAG,
276 MUST_USE_APPROVED_VENDOR_FLAG,
277 REQUEST_ID,
278 PROGRAM_APPLICATION_ID,
279 PROGRAM_ID,
280 PROGRAM_UPDATE_DATE,
281 OUTSIDE_OPERATION_FLAG,
282 OUTSIDE_OPERATION_UOM_TYPE,
283 SAFETY_STOCK_BUCKET_DAYS,
284 AUTO_REDUCE_MPS,
285 COSTING_ENABLED_FLAG,
286 CYCLE_COUNT_ENABLED_FLAG,
287 AUTO_CREATED_CONFIG_FLAG,
288 ITEM_TYPE,
289 MODEL_CONFIG_CLAUSE_NAME,
290 SHIP_MODEL_COMPLETE_FLAG,
291 MRP_PLANNING_CODE,
292 RETURN_INSPECTION_REQUIREMENT,
293 ATO_FORECAST_CONTROL,
294 RELEASE_TIME_FENCE_CODE, /*NP 19AUG96 Eight cols added for 10.7 */
295 RELEASE_TIME_FENCE_DAYS,
296 CONTAINER_ITEM_FLAG,
297 CONTAINER_TYPE_CODE,
298 INTERNAL_VOLUME,
299 MAXIMUM_LOAD_WEIGHT,
300 MINIMUM_FILL_PERCENT,
301 VEHICLE_ITEM_FLAG,
302 SERV_REQ_ENABLED_CODE,
303 SERV_BILLING_ENABLED_FLAG,
304 -- SERV_IMPORTANCE_LEVEL,
305 PLANNED_INV_POINT_FLAG
306 -- Added for 11.5.10
307 , TRACKING_QUANTITY_IND
308 , ONT_PRICING_QTY_SOURCE
309 , SECONDARY_DEFAULT_IND
310 , SECONDARY_UOM_CODE
311 , DUAL_UOM_DEVIATION_HIGH
312 , DUAL_UOM_DEVIATION_LOW
313 , CONTRACT_ITEM_TYPE_CODE
314 , RECOVERED_PART_DISP_CODE
315 from MTL_SYSTEM_ITEMS_INTERFACE
316 -- Replacing organization_id + 0 with organization_id - Anmurali - Bug 4175124
317 where ((organization_id = org_id) or
318 (all_Org = 1))
319 and set_process_id = xset_id
320 and process_flag in (31, 32, 33, 34, 44);
321
322 /*NP 27DEC94
323 *New condition for process_flag;
324 *As a result of the breakup of INVPVHDR into smaller packages
325 *there are now new process_flag values possible
326 *INVPVHDR sets it to either 31 or 41
327 *INVPVDR2 may overwrite 41 by 42 or 32; will not overwrite 31
328 *INVPVDR3 may overwrite 42 by 43 or 33; will not overwrite 32
329 *INVPVDR4 may overwrite 43 by 44 or 34; will not overwrite 33
330 * Thus the values possible are: 31, 32, 33, 34, 44
331 */
332
333 --Start 2878098 WIP Locator mandatory by loc contrl
334 CURSOR c_org_loc_control(cp_org_id number) IS
335 SELECT stock_locator_control_code,negative_inv_receipt_code
336 FROM mtl_parameters
337 where organization_id = cp_org_id;
338
339 CURSOR c_subinv_loc_control(cp_org_id number,
340 cp_subinv_name varchar2) IS
341 SELECT locator_type
342 FROM mtl_secondary_inventories
343 WHERE secondary_inventory_name = cp_subinv_name
344 AND organization_id = cp_org_id
345 AND SYSDATE < nvl(disable_date, SYSDATE+1);
346
347 -- Added the cursor for bug # 3762750
348 CURSOR c_fndcomlookup_exists(cp_lookup_type VARCHAR2,
349 cp_lookup_code VARCHAR2) IS
350 SELECT enabled_flag
351 FROM FND_COMMON_LOOKUPS
352 WHERE lookup_type = cp_lookup_type
353 AND lookup_code = cp_lookup_code;
354
355 -- Added the cursor for bug # 3762750
356 CURSOR c_fndlookup_exists(cp_lookup_type VARCHAR2,
357 cp_lookup_code VARCHAR2) IS
358 SELECT 'x'
359 FROM FND_LOOKUP_VALUES_VL
360 WHERE LOOKUP_TYPE = cp_lookup_type
361 AND LOOKUP_CODE = cp_lookup_code
362 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
363 AND ENABLED_FLAG = 'Y';
364
365 l_org_loc_ctrl number;
366 l_subinv_loc_ctrl number;
367 l_locator_control number;
368 l_loc_mandatory boolean := FALSE;
369 l_allow_neg_bal_flag number;
370 l_return_status varchar2(10);
371 l_msg_count number;
372 l_msg_data varchar2(240);
373
374
375 --End 2878098 WIP Locator mandatory by loc contrl
376
377 msicount number;
378 msiicount number;
379 resersal_flag number;
380 dup_item_id number;
381 l_item_id number;
382 l_org_id number;
383 cat_set_id number;
384 trans_id number;
385 ext_flag number := 0;
386 error_msg varchar2(70);
387 status number;
388 dumm_status number;
389 master_org_id number;
390 stmt number;
391 LOGGING_ERR exception;
392 VALIDATE_ERR exception;
393 chart_of_acc_id number;
394 temp_proc_flag number;
395 l_old_catalog_id number;
396 l_wip_subinv_error BOOLEAN := FALSE;
397 l_wip_locator_error BOOLEAN := FALSE;
398 l_message_name VARCHAR2(30):= NULL;
399 l_msg_text fnd_new_messages.message_text%TYPE;
400 l_source_subinv_error BOOLEAN := FALSE; -- Bug 4489727
401 l_source_restrict_sub number; -- Bug 4489727
402 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
403 l_prev_org_id NUMBER := -1; -- Bug: 4654433
404
405 begin
406
407 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
408
409
410 -- validate the records
411
412 for cr in cc loop
413 status := 0;
414 trans_id := cr.transaction_id;
415 l_org_id := cr.organization_id;
416 l_item_id := cr.inventory_item_id; --Bug: 4654433
417 temp_proc_flag := cr.process_flag; -- Bug 4705184
418
419
420 /*NP 28DEC94
421 **A lot of item_id related validation has now been removed from all the new
422 **INVPVDR*.sql packages that once constituted INVPVHDR
423 **This validation was redundant (and expensive!).
424 **
425 **The item_id related validation now takes place ONLY in INVPVHDR.sql.
426 **
427 **However, this has meant that the value for l_item_id (see INVPVHDR.sql) now
428 **needs to be gotten from the database
429 */
430 /* Bug: 4654433 Commneting out the following code
431 select inventory_item_id
432 into l_item_id
433 from mtl_system_items_interface
434 where transaction_id = cr.transaction_id
435 and set_process_id = xset_id;
436 */
437 -- Validate first group of foreign keys
438
439 IF l_inv_debug_level IN(101, 102) THEN
440 INVPUTLI.info('INVPVDR5: Validating foreign keys set 1');
441 END IF;
442
443
444 stmt := 11;
445
446 -- validate foreign keys: COST_OF_SALES_ACCOUNT
447 /*NP 31AUG94 Get chart_of_accounts_id for the org where
448 ** There is only one chart_of_accounts_id per organization_id.
449 ** The chart_of_accounts_id will be used to validate the 4 expense accounts
450 ** cost_of_sales_account, sales_account, expense_account and encumbrance_account
451 */
452
453 -- Bug fix # 3742121
454 -- If an organization in mtl_system_items_interface does not exist in
455 -- org_organization_definitions, error message is logged.
456 --Bug: 4654433 Query chart_of_accounts_id only if organization_id has changed
457 if l_org_id <> l_prev_org_id then
458 begin
459 --Perf Issue : Replaced org_organizations_definitions view.
460 SELECT lgr.CHART_OF_ACCOUNTS_ID into chart_of_acc_id
461 FROM gl_ledgers lgr,
462 hr_organization_information hoi
463 where hoi.organization_id = cr.organization_id
464 and (HOI.ORG_INFORMATION_CONTEXT|| '') ='Accounting Information'
465 and TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI.ORG_INFORMATION1,'0123456789',' ')), NULL, HOI.ORG_INFORMATION1,-99999)) = LGR.LEDGER_ID
466 and lgr.object_type_code = 'L'
467 and rownum = 1;
468
469 exception
470 when NO_DATA_FOUND then
471 dumm_status := INVPUOPI.mtl_log_interface_err(
472 cr.organization_id,
473 user_id,
474 login_id,
475 prog_appid,
476 prog_id,
477 request_id,
478 cr.TRANSACTION_ID,
479 l_msg_text,
480 'ORGANIZATION_ID',
481 'MTL_SYSTEM_ITEMS_INTERFACE',
482 'INV_IOI_INVALID_ORG',
483 err_text);
484 if dumm_status < 0 then
485 raise LOGGING_ERR;
486 end if;
487 status := 1;
488 end;
489 end if;
490 l_prev_org_id := l_org_id;
491 -- End of bug fix # 3742121
492
493 if cr.costing_enabled_flag = 'N' AND -- Bug 14614669
494 (cr.purchasing_item_flag = 'N' AND
495 cr.purchasing_enabled_flag = 'N') then
496
497 INVPUTLI.info('Do not do the Cost of Sales Account checking for the item which is not costed or purchased');
498
499 elsif cr.COST_OF_SALES_ACCOUNT is not null then
500 begin
501 select 'x' into temp
502 from GL_CODE_COMBINATIONS
503 where CODE_COMBINATION_ID = cr.COST_OF_SALES_ACCOUNT
504 and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
505 and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
506 and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
507 and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 - Anmurali
508 /*NP 31AUG94 added CHART_OF_ACCOUNTS_ID clause*/
509 exception
510 when NO_DATA_FOUND then
511 -- 3108469
512 l_msg_text := get_msg_text (p_message_name => 'INV_IOI_COST_OF_SALES_ACNT'
513 ,p_organization_id => cr.organization_id);
514 dumm_status := INVPUOPI.mtl_log_interface_err(
515 cr.organization_id,
516 user_id,
517 login_id,
518 prog_appid,
519 prog_id,
520 request_id,
521 cr.TRANSACTION_ID,
522 l_msg_text,
523 'COST_OF_SALES_ACCOUNT',
524 'MTL_SYSTEM_ITEMS_INTERFACE',
525 'INV_IOI_ERR_CST_OF_SLS_ACNT', --bug 11894684, replace INV_IOI_ERR
526 err_text);
527 if dumm_status < 0 then
528 raise LOGGING_ERR;
529 end if;
530 status := 1;
531 end;
532 end if;
533
534 stmt := 12;
535 -- validate foreign keys: SALES_ACCOUNT
536 if cr.costing_enabled_flag = 'N' AND -- Bug 14614669
537 (cr.purchasing_item_flag = 'N' AND
538 cr.purchasing_enabled_flag = 'N') then
539
540 INVPUTLI.info('Do not do the Sales Account checking for the item which is not costed or purchased');
541
542 elsif cr.SALES_ACCOUNT is not null then
543 begin
544 select 'x' into temp
545 from GL_CODE_COMBINATIONS
546 where CODE_COMBINATION_ID = cr.SALES_ACCOUNT
547 and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
548 and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
549 and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
550 and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 - Anmurali
551 /*NP 31AUG94 added CHART_OF_ACCOUNTS_ID clause*/
552 exception
553 when NO_DATA_FOUND then
554 -- 3108469
555 l_msg_text := get_msg_text (p_message_name => 'INV_IOI_SALES_ACCOUNT'
556 ,p_organization_id => cr.organization_id);
557 dumm_status := INVPUOPI.mtl_log_interface_err(
558 cr.organization_id,
559 user_id,
560 login_id,
561 prog_appid,
562 prog_id,
563 request_id,
564 cr.TRANSACTION_ID,
565 l_msg_text,
566 'SALES_ACCOUNT',
567 'MTL_SYSTEM_ITEMS_INTERFACE',
568 'INV_IOI_ERR_SLS_ACCNT',--bug 11894684, replace INV_IOI_ERR
569 err_text);
570 if dumm_status < 0 then
571 raise LOGGING_ERR;
572 end if;
573 status := 1;
574 end;
575 end if;
576
577 stmt := 36;
578
579 -- validate Expense_account
580 if cr.costing_enabled_flag = 'N' AND -- Bug 14614669
581 (cr.purchasing_item_flag = 'N' AND
582 cr.purchasing_enabled_flag = 'N') then
583
584 INVPUTLI.info('Do not do the Expense Account checking for the item which is not costed or purchased');
585
586 elsif cr.EXPENSE_ACCOUNT is not null then
587 begin
588 select 'x' into temp
589 from GL_CODE_COMBINATIONS
590 where CODE_COMBINATION_ID = cr.EXPENSE_ACCOUNT
591 and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
592 and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
593 and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
594 and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 -Anmurali
595 exception
596 when NO_DATA_FOUND then
597 -- 3108469
598 l_msg_text := get_msg_text (p_message_name => 'INV_IOI_EXEPENSE_ACCOUNT'
599 ,p_organization_id => cr.organization_id);
600 dumm_status := INVPUOPI.mtl_log_interface_err(
601 cr.organization_id,
602 user_id,
603 login_id,
604 prog_appid,
605 prog_id,
606 request_id,
607 cr.TRANSACTION_ID,
608 l_msg_text,
609 'EXPENSE_ACCOUNT',
610 'MTL_SYSTEM_ITEMS_INTERFACE',
611 'INV_IOI_ERR_EXEPENSE_ACCOUNT',--bug 11894684, replace INV_IOI_ERR
612 err_text);
613 if dumm_status < 0 then
614 raise LOGGING_ERR;
615 end if;
616 status := 1;
617 end;
618 end if;
619
620 stmt := 37;
621
622 -- validate Encumbrance Account
623 if cr.ENCUMBRANCE_ACCOUNT is not null then
624 begin
625 select 'x' into temp
626 from GL_CODE_COMBINATIONS
627 where CODE_COMBINATION_ID = cr.ENCUMBRANCE_ACCOUNT
628 and CHART_OF_ACCOUNTS_ID = chart_of_acc_id
629 and nvl(START_DATE_ACTIVE,sysdate) <= sysdate
630 and nvl(END_DATE_ACTIVE,sysdate) >= sysdate
631 and DETAIL_POSTING_ALLOWED_FLAG = 'Y' ; --* Added for bug #4229090 -Anmurali
632 exception
633 when NO_DATA_FOUND then
634 -- 3108469
635 l_msg_text := get_msg_text (p_message_name => 'INV_IOI_ENCUMB_ACCT'
636 ,p_organization_id => cr.organization_id);
637 dumm_status := INVPUOPI.mtl_log_interface_err(
638 cr.organization_id,
639 user_id,
640 login_id,
641 prog_appid,
642 prog_id,
643 request_id,
644 cr.TRANSACTION_ID,
645 l_msg_text,
646 'ENCUMBRANCE_ACCOUNT',
647 'MTL_SYSTEM_ITEMS_INTERFACE',
648 'INV_IOI_ERR_ENCUMB_ACCT',--bug 11894684, replace INV_IOI_ERR
649 err_text);
650 if dumm_status < 0 then
651 raise LOGGING_ERR;
652 end if;
653 status := 1;
654 end;
655 end if;
656
657 if (cr.material_billable_flag IN ('E', 'L') AND
658 ( cr.stock_enabled_flag = 'Y' OR cr.mtl_transactions_enabled_flag = 'Y')) then
659 dumm_status := INVPUOPI.mtl_log_interface_err(
660 cr.organization_id,
661 user_id,
662 login_id,
663 prog_appid,
664 prog_id,
665 request_id,
666 cr.TRANSACTION_ID,
667 error_msg,
668 'MATERIAL_BILLABLE_FLAG',
669 'MTL_SYSTEM_ITEMS_INTERFACE',
670 'INV_MATERIAL_BILLABLE_NON_TXN',
671 err_text);
672 if dumm_status < 0 then
673 raise LOGGING_ERR;
674 end if;
675 status := 1;
676 end if ;
677 --Added for 11.5.10 Recovered Part Disposition should be null For LABOR and EXPENSE Item Billing Types
678 if (cr.material_billable_flag IN ('E', 'L') AND
679 cr.RECOVERED_PART_DISP_CODE IS NOT NULL) then
680 dumm_status := INVPUOPI.mtl_log_interface_err(
681 cr.organization_id,
682 user_id,
683 login_id,
684 prog_appid,
685 prog_id,
686 request_id,
687 cr.TRANSACTION_ID,
688 error_msg,
689 'MATERIAL_BILLABLE_FLAG',
690 'MTL_SYSTEM_ITEMS_INTERFACE',
691 'INV_REC_PART_BIILLING_TYPE',
692 err_text);
693 if dumm_status < 0 then
694 raise LOGGING_ERR;
695 end if;
696 status := 1;
697 end if ;
698 --Added for 11.5.10 If Billing Enabled Flag is not set then Billing Type cannot be defined.
699 if (cr.material_billable_flag IS NOT NULL AND
700 NVL(cr.SERV_BILLING_ENABLED_FLAG,'N') = 'N') then
701 dumm_status := INVPUOPI.mtl_log_interface_err(
702 cr.organization_id,
703 user_id,
704 login_id,
705 prog_appid,
706 prog_id,
707 request_id,
708 cr.TRANSACTION_ID,
709 error_msg,
710 'MATERIAL_BILLABLE_FLAG',
711 'MTL_SYSTEM_ITEMS_INTERFACE',
712 'INV_SERV_BILLING_MATERIAL_DEP',
713 err_text);
714 if dumm_status < 0 then
715 raise LOGGING_ERR;
716 end if;
717 status := 1;
718 end if ;
719 stmt := 13;
720 -- validate foreign keys
721 if cr.PICKING_RULE_ID is not null then
722 begin
723 select 'x' into temp
724 from MTL_PICKING_RULES
725 where PICKING_RULE_ID = cr.PICKING_RULE_ID;
726 exception
727 when NO_DATA_FOUND then
728 dumm_status := INVPUOPI.mtl_log_interface_err(
729 cr.organization_id,
730 user_id,
731 login_id,
732 prog_appid,
733 prog_id,
734 request_id,
735 cr.TRANSACTION_ID,
736 error_msg,
737 'PICKING_RULE_ID',
738 'MTL_SYSTEM_ITEMS_INTERFACE',
739 'INV_IOI_PICKING_RULE',
740 err_text);
741 if dumm_status < 0 then
742 raise LOGGING_ERR;
743 end if;
744 status := 1;
745 end;
746 end if;
747
748 stmt := 14;
749 -- validate foreign keys
750 if cr.INVENTORY_ITEM_STATUS_CODE is not null then
751 begin
752 select 'x' into temp
753 from MTL_ITEM_STATUS
754 where INVENTORY_ITEM_STATUS_CODE = cr.INVENTORY_ITEM_STATUS_CODE
755 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
756 exception
757 when NO_DATA_FOUND then
758 dumm_status := INVPUOPI.mtl_log_interface_err(
759 cr.organization_id,
760 user_id,
761 login_id,
762 prog_appid,
763 prog_id,
764 request_id,
765 cr.TRANSACTION_ID,
766 error_msg,
767 'INVENTORY_ITEM_STATUS_CODE',
768 'MTL_SYSTEM_ITEMS_INTERFACE',
769 'INV_IOI_STATUS_CODE',
770 err_text);
771 if dumm_status < 0 then
772 raise LOGGING_ERR;
773 end if;
774 status := 1;
775 end;
776 end if;
777
778 stmt := 16;
779 -- validate foreign keys
780 if cr.ENGINEERING_ITEM_ID is not null then
781 begin
782 select 'x' into temp
783 from MTL_SYSTEM_ITEMS
784 where INVENTORY_ITEM_ID = cr.ENGINEERING_ITEM_ID
785 and ORGANIZATION_ID = cr.ORGANIZATION_ID;
786 exception
787 when NO_DATA_FOUND then
788 dumm_status := INVPUOPI.mtl_log_interface_err(
789 cr.organization_id,
790 user_id,
791 login_id,
792 prog_appid,
793 prog_id,
794 request_id,
795 cr.TRANSACTION_ID,
796 error_msg,
797 'ENGINEERING_ITEM_ID',
798 'MTL_SYSTEM_ITEMS_INTERFACE',
799 'BOM_OP_VALIDATION_ERR',
800 err_text);
801 if dumm_status < 0 then
802 raise LOGGING_ERR;
803 end if;
804 status := 1;
805 end;
806 end if;
807
808 stmt := 17;
809 -- validate foreign keys
810
811 /*Fix for bug 4564472 - Since primary_unit_of_measure and primary_uom_code cannot be updated it is sufficient
812 to make the below uom related validations for create txn only.
813 Added below If condition to check for TRANSACTION_TYPE=CREATE. */
814
815 IF cr.TRANSACTION_TYPE='CREATE' THEN
816 temp_uom_valid := TRUE; /* Added bug 14104913 */
817 IF cr.PRIMARY_UNIT_OF_MEASURE IS NOT NULL THEN
818 BEGIN
819 select UOM_CODE into temp_uom_code
820 from MTL_UNITS_OF_MEASURE
821 where UNIT_OF_MEASURE = cr.PRIMARY_UNIT_OF_MEASURE /*Bug 5192495*/
822 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
823
824 if cr.PRIMARY_UOM_CODE is null then
825 cr.PRIMARY_UOM_CODE := temp_uom_code;
826 else
827 if cr.PRIMARY_UOM_CODE <> temp_uom_code then
828 dumm_status := INVPUOPI.mtl_log_interface_err(
829 cr.organization_id,
830 user_id,
831 login_id,
832 prog_appid,
833 prog_id,
834 request_id,
835 cr.TRANSACTION_ID,
836 error_msg,
837 'PRIMARY_UOM_CODE',
838 'MTL_SYSTEM_ITEMS_INTERFACE',
839 'INV_IOI_PRIMARY_UOM',
840 err_text);
841 if dumm_status < 0 then
842 raise LOGGING_ERR;
843 end if;
844 status := 1;
845 temp_uom_valid := FALSE; /* bug 14104913 */
846 end if;
847 end if;
848 exception
849 when NO_DATA_FOUND then
850 dumm_status := INVPUOPI.mtl_log_interface_err(
851 cr.organization_id,
852 user_id,
853 login_id,
854 prog_appid,
855 prog_id,
856 request_id,
857 cr.TRANSACTION_ID,
858 error_msg,
859 'PRIMARY_UNIT_OF_MEASURE',
860 'MTL_SYSTEM_ITEMS_INTERFACE',
861 'INV_IOI_PRIMARY_UOM',
862 err_text);
863 if dumm_status < 0 then
864 raise LOGGING_ERR;
865 end if;
866 status := 1;
867 temp_uom_valid := FALSE; /* bug 14104913 */
868 stmt := 18;
869 end;
870
871 /*NP 22SEP94 if PRIMARY_UNIT_OF_MEASURE is null
872 ** and UOM_CODE is not
873 */
874 ELSIF cr.PRIMARY_UOM_CODE is not null THEN
875 BEGIN
876 select UNIT_OF_MEASURE into temp_u_o_m /*Bug 5192495*/
877 from MTL_UNITS_OF_MEASURE
878 where UOM_CODE = cr.PRIMARY_UOM_CODE
879 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
880
881 cr.PRIMARY_UNIT_OF_MEASURE := temp_u_o_m;
882
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN
885 dumm_status := INVPUOPI.mtl_log_interface_err(
886 cr.organization_id,
887 user_id,
888 login_id,
889 prog_appid,
890 prog_id,
891 request_id,
892 cr.TRANSACTION_ID,
893 error_msg,
894 'PRIMARY_UOM_CODE',
895 'MTL_SYSTEM_ITEMS_INTERFACE',
896 'INV_IOI_PRIMARY_UOM',
897 err_text);
898 IF dumm_status < 0 THEN
899 RAISE LOGGING_ERR;
900 END IF;
901 status := 1;
902 temp_uom_valid := FALSE; /* bug 14104913 */
903 END;
904 END IF; --IF cr.PRIMARY_UNIT_OF_MEASURE is not null THEN
905
906
907 /*NP 17OCT94 New code to fix bug # 241374
908 ** By this time the cr.inventory_item_id, cr.PRIMARY_UNIT_OF_MEASURE
909 ** and cr.PRIMARY_UOM_CODE fields already have values assigned to them.
910 ** Here we validate that for any given inv_item_id
911 ** the primary_unit_of_measure (and correspondingly the primary_uom_code)
912 ** should be in the same uom_class across ALL orgs
913 */
914 /* bug 14104913, validate uom class etc when the uom is valid
915 * added AND temp_uom_valid to below condition
916 */
917 -- validate foreign keys
918 if cr.PRIMARY_UOM_CODE is not null AND temp_uom_valid THEN --* Modified for Bug 4366615
919 begin
920 select UOM_CLASS into temp_uom_class
921 from MTL_UNITS_OF_MEASURE
922 where UOM_CODE = cr.PRIMARY_UOM_CODE; --* Modified for Bug 4366615
923
924 /*Fix for bug 4564472 -Organizaition_id filter was added in the where clause of the below query
925 to improve its performance. */
926
927 select 'x' into temp
928 from MTL_SYSTEM_ITEMS msi
929 where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
930 and ORGANIZATION_ID in
931 ( select MASTER_ORGANIZATION_ID
932 from MTL_PARAMETERS
933 where ORGANIZATION_ID = MASTER_ORGANIZATION_ID )
934 and rownum = 1
935 and not exists
936 (select UNIT_OF_MEASURE
937 from MTL_UNITS_OF_MEASURE MUOM
938 where UOM_CLASS = temp_uom_class
939 and msi.PRIMARY_UOM_CODE
940 = MUOM.UOM_CODE);
941
942 /*NP If any rows are fetched here then it is invalid
943 ** Call the error routine
944 ** else goto the exception and exit this validation
945 ** succesfully.
946 */
947
948 dumm_status := INVPUOPI.mtl_log_interface_err(
949 cr.organization_id,
950 user_id,
951 login_id,
952 prog_appid,
953 prog_id,
954 request_id,
955 cr.TRANSACTION_ID,
956 error_msg,
957 'PRIMARY_UNIT_OF_MEASURE',
958 'MTL_SYSTEM_ITEMS_INTERFACE',
959 'INV_IOI_PRIMARY_UOM2',
960 err_text);
961 if dumm_status < 0 then
962 raise LOGGING_ERR;
963 end if;
964 status := 1;
965
966 exception
967 when NO_DATA_FOUND then
968 /*NP Do nothing. Valid data */
969 null;
970 end;
971 end if;
972
973
974 -- Bug 1320556: when the UOM entered is Item Specific,
975 -- and the UOM is not the base UOM of the Class,
976 -- check if there is a conversion defined for the UOM.
977 --
978 -- Bug 1386939: the UOM conversion must exist irrespective
979 -- of Conversions (ALLOWED_UNITS_LOOKUP_CODE).
980
981 /* bug 14104913, validate uom class, conversion etc when the uom is valid
982 * added AND temp_uom_valid to below condition
983 */
984 /* if (cr.ALLOWED_UNITS_LOOKUP_CODE = 1) and */
985 if cr.PRIMARY_UOM_CODE is not null AND temp_uom_valid THEN --* Modified for Bug 4366615
986 select BASE_UOM_FLAG
987 into temp_base_uom_flag
988 from MTL_UNITS_OF_MEASURE
989 where UOM_CODE = cr.PRIMARY_UOM_CODE; --* Modified for Bug 4366615
990
991 if (temp_base_uom_flag <> 'Y') then
992 begin
993 select 'x'
994 into temp
995 from mtl_uom_conversions
996 where INVENTORY_ITEM_ID = 0
997 and UOM_CODE = cr.PRIMARY_UOM_CODE;
998
999 exception
1000 when no_data_found then
1001 dumm_status := INVPUOPI.mtl_log_interface_err (
1002 cr.organization_id,
1003 user_id,
1004 login_id,
1005 prog_appid,
1006 prog_id,
1007 request_id,
1008 cr.TRANSACTION_ID,
1009 error_msg,
1010 'PRIMARY_UNIT_OF_MEASURE',
1011 'MTL_SYSTEM_ITEMS_INTERFACE',
1012 'INV_IOI_NO_UOM_CONV',
1013 err_text);
1014 if dumm_status < 0 then
1015 raise LOGGING_ERR;
1016 end if;
1017 status := 1;
1018 end;
1019 end if;
1020 end if;
1021
1022 END IF; /* Fix for bug 4564472 - End of if for transaction_type='create' */
1023
1024 -- Validate ITEM_TYPE
1025 --
1026 if cr.ITEM_TYPE is not null then
1027 -- 3762750: Using cursor call to avoid multiple parsing
1028 OPEN c_fndcomlookup_exists('ITEM_TYPE',cr.ITEM_TYPE);
1029 FETCH c_fndcomlookup_exists INTO temp_enabled_flag;
1030 CLOSE c_fndcomlookup_exists;
1031
1032 if (temp_enabled_flag <> 'Y' OR temp_enabled_flag IS NULL) then
1033 dumm_status := INVPUOPI.mtl_log_interface_err(
1034 cr.organization_id,
1035 user_id,
1036 login_id,
1037 prog_appid,
1038 prog_id,
1039 request_id,
1040 cr.TRANSACTION_ID,
1041 error_msg,
1042 'ITEM_TYPE',
1043 'MTL_SYSTEM_ITEMS_INTERFACE',
1044 'INV_IOI_ITEM_TYPE',
1045 err_text);
1046 if dumm_status < 0 then
1047 raise LOGGING_ERR;
1048 end if;
1049 status := 1;
1050 end if;
1051 end if;
1052
1053 --validate CONTAINER_TYPE_CODE
1054
1055 if cr.CONTAINER_TYPE_CODE is not null then
1056 -- 3762750: Using cursor call to avoid multiple parsing
1057 OPEN c_fndcomlookup_exists('CONTAINER_TYPE',cr.CONTAINER_TYPE_CODE);
1058 FETCH c_fndcomlookup_exists INTO temp_enabled_flag;
1059 CLOSE c_fndcomlookup_exists;
1060
1061 if (temp_enabled_flag <> 'Y' OR temp_enabled_flag IS NULL) then
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 'CONTAINER_TYPE_CODE',
1072 'MTL_SYSTEM_ITEMS_INTERFACE',
1073 'INV_IOI_CONTAINER_TYPE_CODE',
1074 err_text);
1075 if dumm_status < 0 then
1076 raise LOGGING_ERR;
1077 end if;
1078 status := 1;
1079 end if;
1080 end if;
1081
1082 stmt := 19;
1083 -- validate foreign keys
1084 if cr.VOLUME_UOM_CODE is not null then
1085 begin
1086 select 'x' into temp
1087 from MTL_UNITS_OF_MEASURE
1088 where UOM_CODE = cr.VOLUME_UOM_CODE;
1089 exception
1090 when NO_DATA_FOUND then
1091 dumm_status := INVPUOPI.mtl_log_interface_err(
1092 cr.organization_id,
1093 user_id,
1094 login_id,
1095 prog_appid,
1096 prog_id,
1097 request_id,
1098 cr.TRANSACTION_ID,
1099 error_msg,
1100 'VOLUME_UOM_CODE',
1101 'MTL_SYSTEM_ITEMS_INTERFACE',
1102 'INV_IOI_VOLUME_UOM_CODE',
1103 err_text);
1104 if dumm_status < 0 then
1105 raise LOGGING_ERR;
1106 end if;
1107 status := 1;
1108 end;
1109 end if;
1110
1111 stmt := 20;
1112 -- validate foreign keys
1113 /* R12 C Unit Weight can now be updated for Pending items. Moving the below set of validations to INVPVHDR
1114 if cr.WEIGHT_UOM_CODE is not null then
1115 begin
1116 select 'x' into temp
1117 from MTL_UNITS_OF_MEASURE
1118 where UOM_CODE = cr.WEIGHT_UOM_CODE;
1119 exception
1120 when NO_DATA_FOUND then
1121 dumm_status := INVPUOPI.mtl_log_interface_err(
1122 cr.organization_id,
1123 user_id,
1124 login_id,
1125 prog_appid,
1126 prog_id,
1127 request_id,
1128 cr.TRANSACTION_ID,
1129 error_msg,
1130 'WEIGHT_UOM_CODE',
1131 'MTL_SYSTEM_ITEMS_INTERFACE',
1132 'INV_IOI_WEIGHT_UOM_CODE',
1133 err_text);
1134 if dumm_status < 0 then
1135 raise LOGGING_ERR;
1136 end if;
1137 status := 1;
1138 end;
1139 end if; */
1140
1141 IF l_inv_debug_level IN(101, 102) THEN
1142 INVPUTLI.info('INVPVDR5: Validating foreign keys set 2');
1143 END IF;
1144
1145 stmt := 21;
1146 -- validate foreign keys
1147 if cr.ITEM_CATALOG_GROUP_ID is not null then
1148 --2777118: Start Catalog Group check enhanced
1149 IF cr.transaction_type ='CREATE' THEN
1150 BEGIN
1151 select 'x' into temp
1152 from mtl_item_catalog_groups
1153 where item_catalog_group_id = cr.ITEM_CATALOG_GROUP_ID
1154 and item_creation_allowed_flag = 'Y'
1155 and NVL(inactive_date,sysdate+1) > sysdate;
1156 exception
1157 when NO_DATA_FOUND then
1158 dumm_status := INVPUOPI.mtl_log_interface_err(
1159 cr.organization_id,
1160 user_id,
1161 login_id,
1162 prog_appid,
1163 prog_id,
1164 request_id,
1165 cr.TRANSACTION_ID,
1166 error_msg,
1167 'ITEM_CATALOG_GROUP_ID',
1168 'MTL_SYSTEM_ITEMS_INTERFACE',
1169 'INV_IOI_ITEM_CAT_GROUP',
1170 err_text);
1171 if dumm_status < 0 then
1172 raise LOGGING_ERR;
1173 end if;
1174 status := 1;
1175 END;
1176 ELSIF cr.transaction_type ='UPDATE' THEN
1177
1178 SELECT item_catalog_group_id
1179 INTO l_old_catalog_id
1180 FROM mtl_system_items_b
1181 WHERE inventory_item_id = cr.inventory_item_id
1182 AND organization_id = cr.organization_id;
1183
1184 --Bug: 2805253 Modified the validation
1185 -- IF (l_old_catalog_id IS NULL OR l_old_catalog_id <> cr.item_catalog_group_id) THEN
1186 IF (NVL(l_old_catalog_id,-1) <> NVL(cr.item_catalog_group_id,-1) AND cr.item_catalog_group_id IS NOT NULL) THEN
1187 BEGIN
1188 /*Bug:3491746
1189 SELECT 'x' into temp
1190 FROM mtl_item_catalog_groups_b
1191 WHERE item_catalog_group_id = cr.item_catalog_group_id
1192 AND item_creation_allowed_flag = 'Y'
1193 AND item_catalog_group_id IN
1194 (SELECT ICG.item_catalog_group_id
1195 FROM mtl_item_catalog_groups_b ICG
1196 WHERE ICG.item_creation_allowed_flag = 'Y'
1197 AND ((ICG.inactive_date IS NULL) OR (TRUNC(ICG.inactive_date) > TRUNC(SYSDATE))));
1198 ELSE
1199 SELECT 'x' into temp
1200 FROM mtl_item_catalog_groups_b
1201 WHERE item_catalog_group_id = cr.item_catalog_group_id
1202 AND item_creation_allowed_flag = 'Y'
1203 AND item_catalog_group_id IN
1204 (SELECT ICG.item_catalog_group_id
1205 FROM mtl_item_catalog_groups_b ICG
1206 WHERE ICG.item_creation_allowed_flag = 'Y'
1207 --Bug: 2805253 Removed NVL
1208 AND ((ICG.inactive_date IS NULL) OR (TRUNC(ICG.inactive_date) > TRUNC(SYSDATE)))
1209 CONNECT BY PRIOR ICG.item_catalog_group_id = ICG.parent_catalog_group_id
1210 START WITH ICG.item_catalog_group_id = l_old_catalog_id);
1211 END IF;
1212 */
1213 SELECT 'x' into temp
1214 FROM mtl_item_catalog_groups_b
1215 WHERE item_catalog_group_id = cr.item_catalog_group_id
1216 AND item_creation_allowed_flag = 'Y'
1217 AND (inactive_date IS NULL OR TRUNC(inactive_date) > TRUNC(SYSDATE));
1218 EXCEPTION
1219 WHEN NO_DATA_FOUND THEN
1220 dumm_status := INVPUOPI.mtl_log_interface_err(
1221 cr.organization_id,
1222 user_id,
1223 login_id,
1224 prog_appid,
1225 prog_id,
1226 request_id,
1227 cr.TRANSACTION_ID,
1228 error_msg,
1229 'ITEM_CATALOG_GROUP_ID',
1230 'MTL_SYSTEM_ITEMS_INTERFACE',
1231 'INV_IOI_ONLY_CATALOG_DOWNCAST',
1232 err_text);
1233 if dumm_status < 0 then
1234 raise LOGGING_ERR;
1235 end if;
1236 status := 1;
1237 END;
1238 END IF;
1239 END IF;
1240 --2777118: End Catalog Group check enhanced
1241 end if;
1242 stmt := 22;
1243 -- validate foreign keys
1244 if cr.SOURCE_SUBINVENTORY is not null then
1245
1246 -- Bug4489727. Restrict subinventories based on source organization's restrict sub checkbox
1247 l_source_subinv_error := FALSE;
1248 if ( cr.ORGANIZATION_ID <> cr.SOURCE_ORGANIZATION_ID ) then
1249 select restrict_subinventories_code
1250 into l_source_restrict_sub
1251 from mtl_system_items_b
1252 where organization_id = cr.SOURCE_ORGANIZATION_ID
1253 and inventory_item_id = cr.INVENTORY_ITEM_ID;
1254 else
1255 l_source_restrict_sub := nvl(cr.RESTRICT_SUBINVENTORIES_CODE, 2) ;
1256 end if;
1257
1258 if ( l_source_restrict_sub = 1 ) then
1259 begin
1260 select 'x' into temp
1261 from MTL_SECONDARY_INVENTORIES s, MTL_ITEM_SUB_INVENTORIES i
1262 where s.SECONDARY_INVENTORY_NAME = cr.SOURCE_SUBINVENTORY
1263 and s.ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
1264 and nvl(s.DISABLE_DATE, sysdate + 1 ) > sysdate
1265 and i.INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
1266 and i.ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
1267 and i.SECONDARY_INVENTORY = s.SECONDARY_INVENTORY_NAME
1268 and 2 = decode(cr.source_organization_id, cr.organization_id,
1269 decode(cr.mrp_planning_code, 3, availability_type, 7, availability_type, 9, availability_type, 2), 2);
1270 exception
1271 when NO_DATA_FOUND then
1272 l_source_subinv_error := TRUE;
1273 l_message_name := 'INV_INT_RESSUBEXP';
1274 end;
1275 else
1276 begin
1277 select 'x' into temp
1278 from MTL_SECONDARY_INVENTORIES
1279 where SECONDARY_INVENTORY_NAME = cr.SOURCE_SUBINVENTORY
1280 and ORGANIZATION_ID = cr.SOURCE_ORGANIZATION_ID
1281 and nvl(DISABLE_DATE, sysdate + 1 ) > sysdate
1282 and 2 = decode(cr.source_organization_id, cr.organization_id,
1283 decode(cr.mrp_planning_code, 3, availability_type, 7, availability_type, 9, availability_type, 2), 2);
1284 exception
1285 when NO_DATA_FOUND then
1286 l_source_subinv_error := TRUE;
1287 l_message_name := 'INV_IOI_SOURCE_SUB';
1288 end;
1289 end if;
1290
1291 if ( l_source_subinv_error ) then
1292 dumm_status := INVPUOPI.mtl_log_interface_err(
1293 cr.organization_id,
1294 user_id,
1295 login_id,
1296 prog_appid,
1297 prog_id,
1298 request_id,
1299 cr.TRANSACTION_ID,
1300 error_msg,
1301 'SOURCE_SUBINVENTORY',
1302 'MTL_SYSTEM_ITEMS_INTERFACE',
1303 l_message_name,
1304 err_text);
1305 if dumm_status < 0 then
1306 raise LOGGING_ERR;
1307 end if;
1308 status := 1;
1309 end if;
1310 end if;
1311
1312 stmt := 23;
1313 -- validate foreign keys
1314 if cr.HAZARD_CLASS_ID is not null then
1315 begin
1316 select 'x' into temp
1317 from PO_HAZARD_CLASSES
1318 where HAZARD_CLASS_ID = cr.HAZARD_CLASS_ID
1319 and (INACTIVE_DATE is null or INACTIVE_DATE > sysdate);
1320 exception
1321 when NO_DATA_FOUND then
1322 dumm_status := INVPUOPI.mtl_log_interface_err(
1323 cr.organization_id,
1324 user_id,
1325 login_id,
1326 prog_appid,
1327 prog_id,
1328 request_id,
1329 cr.TRANSACTION_ID,
1330 error_msg,
1331 'HAZARD_CLASS_ID',
1332 'MTL_SYSTEM_ITEMS_INTERFACE',
1333 'INV_IOI_HAZARD_CLASS',
1334 err_text);
1335 if dumm_status < 0 then
1336 raise LOGGING_ERR;
1337 end if;
1338 status := 1;
1339 end;
1340 end if;
1341
1342 stmt := 24;
1343 -- validate foreign keys
1344 if cr.UN_NUMBER_ID is not null then
1345 begin
1346 select 'x' into temp
1347 from PO_UN_NUMBERS
1348 where UN_NUMBER_ID = cr.UN_NUMBER_ID;
1349 exception
1350 when NO_DATA_FOUND then
1351 dumm_status := INVPUOPI.mtl_log_interface_err(
1352 cr.organization_id,
1353 user_id,
1354 login_id,
1355 prog_appid,
1356 prog_id,
1357 request_id,
1358 cr.TRANSACTION_ID,
1359 error_msg,
1360 'UN_NUMBER_ID',
1361 'MTL_SYSTEM_ITEMS_INTERFACE',
1362 'INV_IOI_UN_NUMBER',
1363 err_text);
1364 if dumm_status < 0 then
1365 raise LOGGING_ERR;
1366 end if;
1367 status := 1;
1368 end;
1369 end if;
1370
1371 stmt := 25;
1372 -- validate foreign keys
1373 if cr.ASSET_CATEGORY_ID is not null then
1374 begin
1375 select 'x' into temp
1376 from FA_CATEGORIES
1377 where CATEGORY_ID = cr.ASSET_CATEGORY_ID;
1378 exception
1379 when NO_DATA_FOUND then
1380 dumm_status := INVPUOPI.mtl_log_interface_err(
1381 cr.organization_id,
1382 user_id,
1383 login_id,
1384 prog_appid,
1385 prog_id,
1386 request_id,
1387 cr.TRANSACTION_ID,
1388 error_msg,
1389 'ASSET_CATEGORY_ID',
1390 'MTL_SYSTEM_ITEMS_INTERFACE',
1391 'INV_IOI_ASSET_CAT_ID',
1392 err_text);
1393 if dumm_status < 0 then
1394 raise LOGGING_ERR;
1395 end if;
1396 status := 1;
1397 end;
1398 end if;
1399
1400 stmt := 26;
1401 -- validate foreign keys
1402 if cr.BASE_ITEM_ID is not null then
1403 begin
1404 select 'x' into temp
1405 from MTL_SYSTEM_ITEMS
1406 where INVENTORY_ITEM_ID = cr.BASE_ITEM_ID
1407 and ORGANIZATION_ID = cr.ORGANIZATION_ID;
1408 exception
1409 when NO_DATA_FOUND then
1410 dumm_status := INVPUOPI.mtl_log_interface_err(
1411 cr.organization_id,
1412 user_id,
1413 login_id,
1414 prog_appid,
1415 prog_id,
1416 request_id,
1417 cr.TRANSACTION_ID,
1418 error_msg,
1419 'BASE_ITEM_ID',
1420 'MTL_SYSTEM_ITEMS_INTERFACE',
1421 'INV_IOI_BASE_ITEM_ID',
1422 err_text);
1423 if dumm_status < 0 then
1424 raise LOGGING_ERR;
1425 end if;
1426 status := 1;
1427 end;
1428 --Added for 11.5.10 If Base Model is null then Autocreated Configuration flag cannot be defined.
1429 elsif cr.AUTO_CREATED_CONFIG_FLAG = 'Y' then
1430 dumm_status := INVPUOPI.mtl_log_interface_err(
1431 cr.organization_id,
1432 user_id,
1433 login_id,
1434 prog_appid,
1435 prog_id,
1436 request_id,
1437 cr.TRANSACTION_ID,
1438 error_msg,
1439 'AUTO_CREATED_CONFIG_FLAG',
1440 'MTL_SYSTEM_ITEMS_INTERFACE',
1441 'INV_BASE_ITEM_AUTO_CREATE_DEP',
1442 err_text);
1443 if dumm_status < 0 then
1444 raise LOGGING_ERR;
1445 end if;
1446 status := 1;
1447 end if;
1448
1449 stmt := 28;
1450 -- validate foreign keys
1451 -- 2870552 : WIP SUB INV validation sequenced properly
1452 IF (cr.WIP_SUPPLY_SUBINVENTORY IS NOT NULL ) THEN
1453 l_wip_subinv_error := FALSE;
1454 IF cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='CREATE' THEN
1455 l_wip_subinv_error := TRUE;
1456 l_message_name := 'INV_IOI_WIP_SUP_SUB';
1457 ELSIF cr.RESTRICT_SUBINVENTORIES_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' THEN
1458 BEGIN
1459 SELECT 'x' INTO temp
1460 FROM MTL_ITEM_SUB_INVENTORIES i
1461 WHERE i.inventory_item_id = cr.inventory_item_id
1462 AND i.ORGANIZATION_ID = cr.ORGANIZATION_ID
1463 AND i.SECONDARY_INVENTORY = cr.WIP_SUPPLY_SUBINVENTORY;
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466 l_wip_subinv_error := TRUE;
1467 l_message_name := 'INV_INT_RESSUBEXP';
1468 END;
1469 ELSIF NVL(cr.RESTRICT_SUBINVENTORIES_CODE,2) = 2 THEN
1470 BEGIN
1471 SELECT 'x' INTO temp
1472 FROM MTL_SECONDARY_INVENTORIES
1473 WHERE SECONDARY_INVENTORY_NAME = cr.WIP_SUPPLY_SUBINVENTORY
1474 AND ORGANIZATION_ID = cr.ORGANIZATION_ID
1475 AND SYSDATE < nvl(DISABLE_DATE, SYSDATE+1);
1476 EXCEPTION
1477 WHEN NO_DATA_FOUND THEN
1478 l_wip_subinv_error := TRUE;
1479 l_message_name := 'INV_IOI_WIP_SUP_SUB';
1480 END;
1481 END IF;
1482
1483 IF l_wip_subinv_error THEN
1484 dumm_status := INVPUOPI.mtl_log_interface_err(
1485 cr.organization_id,
1486 user_id,
1487 login_id,
1488 prog_appid,
1489 prog_id,
1490 request_id,
1491 cr.TRANSACTION_ID,
1492 error_msg,
1493 'WIP_SUPPLY_SUBINVENTORY',
1494 'MTL_SYSTEM_ITEMS_INTERFACE',
1495 l_message_name,
1496 err_text);
1497 if dumm_status < 0 then
1498 raise LOGGING_ERR;
1499 end if;
1500 status := 1;
1501 END IF;
1502 END IF;
1503 --End of fix 2870552
1504 stmt := 48;
1505 --Start 2878098,3799430: WIP Locator mandatory by loc contrl
1506 IF cr.WIP_SUPPLY_SUBINVENTORY IS NOT NULL THEN
1507
1508 l_loc_mandatory := FALSE;
1509 OPEN c_org_loc_control(cr.organization_id);
1510 FETCH c_org_loc_control INTO l_org_loc_ctrl,l_allow_neg_bal_flag;
1511 CLOSE c_org_loc_control;
1512
1513 OPEN c_subinv_loc_control(cr.organization_id,cr.wip_supply_subinventory);
1514 FETCH c_subinv_loc_control INTO l_subinv_loc_ctrl;
1515 CLOSE c_subinv_loc_control;
1516
1517 l_locator_control :=
1518 inv_globals.locator_control(l_return_status,l_msg_count,l_msg_data,
1519 l_org_loc_ctrl,l_subinv_loc_ctrl,cr.location_control_code,
1520 cr.restrict_locators_code,l_allow_neg_bal_flag,1);
1521
1522 if nvl(l_locator_control,1) in (2,3) then
1523 l_loc_mandatory := TRUE;
1524 end if;
1525
1526
1527 /* Bug 3799430. Modifying fix 2878098 as its incomplete
1528
1529 IF l_org_loc_ctrl IN (2,3) THEN
1530 l_loc_mandatory := TRUE;
1531 END IF;
1532
1533 IF NOT l_loc_mandatory THEN
1534 OPEN c_subinv_loc_control(cr.organization_id,cr.wip_supply_subinventory);
1535 FETCH c_subinv_loc_control INTO l_subinv_loc_ctrl;
1536 CLOSE c_subinv_loc_control;
1537 IF l_subinv_loc_ctrl NOT IN (1,5) THEN
1538 l_loc_mandatory := TRUE;
1539 END IF;
1540 END IF;
1541
1542 IF NOT l_loc_mandatory
1543 AND cr.LOCATION_CONTROL_CODE <> 1 THEN
1544 l_loc_mandatory := TRUE;
1545 END IF;
1546 */
1547 IF l_loc_mandatory
1548 AND cr.WIP_SUPPLY_LOCATOR_ID IS NULL THEN
1549 dumm_status := INVPUOPI.mtl_log_interface_err(
1550 cr.organization_id,
1551 user_id,
1552 login_id,
1553 prog_appid,
1554 prog_id,
1555 request_id,
1556 cr.TRANSACTION_ID,
1557 error_msg,
1558 'WIP_SUPPLY_LOCATOR_ID',
1559 'MTL_SYSTEM_ITEMS_INTERFACE',
1560 'INV_IOI_SUP_LOC_ID_MANDATORY',
1561 err_text);
1562 IF dumm_status < 0 THEN
1563 raise LOGGING_ERR;
1564 END IF;
1565 status := 1;
1566 END IF;
1567
1568 END IF;
1569 --End 2878098: WIP Locator mandatory by loc contrl
1570
1571 stmt := 27;
1572 -- 2870552: Locators LOV should be redefined by Restrict Locators option.
1573 -- validate foreign keys
1574 IF cr.WIP_SUPPLY_LOCATOR_ID IS NOT NULL THEN
1575
1576 l_wip_locator_error := FALSE;
1577
1578 IF cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='CREATE' THEN
1579 l_wip_locator_error := TRUE;
1580 ELSIF cr.RESTRICT_LOCATORS_CODE = 1 AND cr.TRANSACTION_TYPE ='UPDATE' THEN
1581 BEGIN
1582 SELECT 'x' INTO temp
1583 FROM MTL_SECONDARY_LOCATORS
1584 WHERE INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
1585 AND ORGANIZATION_ID = cr.ORGANIZATION_ID
1586 AND SECONDARY_LOCATOR = cr.WIP_SUPPLY_LOCATOR_ID
1587 AND SUBINVENTORY_CODE = cr.WIP_SUPPLY_SUBINVENTORY;
1588 EXCEPTION
1589 WHEN NO_DATA_FOUND THEN
1590 l_wip_locator_error := TRUE;
1591 END;
1592 ELSIF NVL(cr.RESTRICT_LOCATORS_CODE,2) = 2 THEN
1593 BEGIN
1594 SELECT 'x' INTO temp
1595 FROM MTL_ITEM_LOCATIONS
1596 WHERE INVENTORY_LOCATION_ID = cr.WIP_SUPPLY_LOCATOR_ID
1597 AND SUBINVENTORY_CODE = cr.WIP_SUPPLY_SUBINVENTORY
1598 AND ORGANIZATION_ID = cr.ORGANIZATION_ID
1599 AND SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND THEN
1602 l_wip_locator_error := TRUE;
1603 END;
1604 END IF;
1605
1606 IF l_wip_locator_error THEN
1607 dumm_status := INVPUOPI.mtl_log_interface_err(
1608 cr.organization_id,
1609 user_id,
1610 login_id,
1611 prog_appid,
1612 prog_id,
1613 request_id,
1614 cr.TRANSACTION_ID,
1615 error_msg,
1616 'WIP_SUPPLY_LOCATOR_ID',
1617 'MTL_SYSTEM_ITEMS_INTERFACE',
1618 'INV_IOI_WIP_SUP_LOC_ID',
1619 err_text);
1620 if dumm_status < 0 then
1621 raise LOGGING_ERR;
1622 end if;
1623 status := 1;
1624 END IF;
1625 END IF;
1626 --End of fix 2870552
1627
1628 stmt := 29;
1629 -- validate foreign keys
1630 if cr.BUYER_ID is not null then
1631 begin
1632
1633 -- Lines changed by Ppeddama on 2/2/2000 for bug#1171778
1634 -- For the bug fix 3845910 from base bug 3810566-- anmurali
1635 -- Bug 4695915 - Replacing references to per_all_workforce_v with per_people_f
1636
1637 if (nvl(hr_general.get_xbg_profile, 'N') = 'Y') then
1638 SELECT 'x' into temp
1639 FROM PER_PEOPLE_F PPF,
1640 PO_AGENTS POA, PER_BUSINESS_GROUPS_PERF PB
1641 WHERE PPF.PERSON_ID = POA.AGENT_ID
1642 AND PPF.BUSINESS_GROUP_ID= PB.BUSINESS_GROUP_ID
1643 AND SYSDATE BETWEEN NVL(POA.START_DATE_ACTIVE, SYSDATE-1)
1644 AND NVL(POA.END_DATE_ACTIVE,SYSDATE+1)
1645 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
1646 AND PPF.PERSON_ID = cr.BUYER_ID;
1647 else
1648 SELECT 'x' into temp
1649 FROM PER_PEOPLE_F PPF,
1650 PO_AGENTS POA , HR_ORGANIZATION_UNITS ORG
1651 WHERE PPF.PERSON_ID = POA.AGENT_ID
1652 AND PPF.BUSINESS_GROUP_ID= ORG.BUSINESS_GROUP_ID
1653 AND ORG.ORGANIZATION_ID = cr.ORGANIZATION_ID
1654 AND SYSDATE BETWEEN NVL(POA.START_DATE_ACTIVE, SYSDATE-1)
1655 AND NVL(POA.END_DATE_ACTIVE,SYSDATE+1)
1656 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
1657 AND PPF.PERSON_ID = cr.BUYER_ID;
1658 end if;
1659
1660 -- End of Bug 3845910 -- anmurali
1661
1662 exception
1663 when NO_DATA_FOUND then
1664 dumm_status := INVPUOPI.mtl_log_interface_err(
1665 cr.organization_id,
1666 user_id,
1667 login_id,
1668 prog_appid,
1669 prog_id,
1670 request_id,
1671 cr.TRANSACTION_ID,
1672 error_msg,
1673 'BUYER_ID',
1674 'MTL_SYSTEM_ITEMS_INTERFACE',
1675 'INV_IOI_BUYER_ID',
1676 err_text);
1677 if dumm_status < 0 then
1678 raise LOGGING_ERR;
1679 end if;
1680 status := 1;
1681 end;
1682 end if;
1683
1684
1685 stmt := 30;
1686 -- validate foreign keys
1687 if cr.RECEIVING_ROUTING_ID is not null then
1688 begin
1689 select 'x' into temp
1690 from RCV_ROUTING_HEADERS
1691 where ROUTING_HEADER_ID = cr.RECEIVING_ROUTING_ID;
1692 exception
1693 when NO_DATA_FOUND then
1694 dumm_status := INVPUOPI.mtl_log_interface_err(
1695 cr.organization_id,
1696 user_id,
1697 login_id,
1698 prog_appid,
1699 prog_id,
1700 request_id,
1701 cr.TRANSACTION_ID,
1702 error_msg,
1703 'RECEIVING_ROUTING_ID',
1704 'MTL_SYSTEM_ITEMS_INTERFACE',
1705 'INV_IOI_REC_ROUTING_ID',
1706 err_text);
1707 if dumm_status < 0 then
1708 raise LOGGING_ERR;
1709 end if;
1710 status := 1;
1711 end;
1712 end if;
1713 if cr.SERV_REQ_ENABLED_CODE is not null AND
1714 cr.SERV_REQ_ENABLED_CODE not in ('E','D','I') then
1715 dumm_status := INVPUOPI.mtl_log_interface_err(
1716 cr.organization_id,
1717 user_id,
1718 login_id,
1719 prog_appid,
1720 prog_id,
1721 request_id,
1722 cr.TRANSACTION_ID,
1723 error_msg,
1724 'SERV_REQ_ENABLED_FLAG',
1725 'MTL_SYSTEM_ITEMS_INTERFACE',
1726 'INV_IOI_SERV_REQ_ENABLED',
1727 err_text);
1728 if dumm_status < 0 then
1729 raise LOGGING_ERR;
1730 end if;
1731 status := 1;
1732 end if;
1733 --Added for 11.5.10 SERV_REQ_ENABLED_CODE must be mutually exclusive with 'SERVICE,WARRANTY and USAGE'
1734 if cr.SERV_REQ_ENABLED_CODE ='E' AND
1735 cr.CONTRACT_ITEM_TYPE_CODE IN ('SERVICE', 'WARRANTY', 'USAGE') then
1736 dumm_status := INVPUOPI.mtl_log_interface_err(
1737 cr.organization_id,
1738 user_id,
1739 login_id,
1740 prog_appid,
1741 prog_id,
1742 request_id,
1743 cr.TRANSACTION_ID,
1744 error_msg,
1745 'SERV_REQ_ENABLED_FLAG',
1746 'MTL_SYSTEM_ITEMS_INTERFACE',
1747 'INV_SERV_REQ_CONTRACT_DEP',
1748 err_text);
1749 if dumm_status < 0 then
1750 raise LOGGING_ERR;
1751 end if;
1752 status := 1;
1753 end if;
1754 --Added for 11.5.10 SERVICE_STARTING_DELAY cannot have a value if Item contract type is not 'SUBSCRIPTION' and not NULL
1755 if cr.SERVICE_STARTING_DELAY IS NOT NULL AND
1756 NVL(cr.CONTRACT_ITEM_TYPE_CODE,'SUBSCRIPTION') <> 'SUBSCRIPTION' then
1757 dumm_status := INVPUOPI.mtl_log_interface_err(
1758 cr.organization_id,
1759 user_id,
1760 login_id,
1761 prog_appid,
1762 prog_id,
1763 request_id,
1764 cr.TRANSACTION_ID,
1765 error_msg,
1766 'SERVICE_STARTING_DELAY',
1767 'MTL_SYSTEM_ITEMS_INTERFACE',
1768 'INV_CONTRACT_START_DELAY_DEP',
1769 err_text);
1770 if dumm_status < 0 then
1771 raise LOGGING_ERR;
1772 end if;
1773 status := 1;
1774 end if;
1775 /* Removed in 11.5.10
1776 if nvl(cr.SERV_IMPORTANCE_LEVEL,1) < 0 then
1777 dumm_status := INVPUOPI.mtl_log_interface_err(
1778 cr.organization_id,
1779 user_id,
1780 login_id,
1781 prog_appid,
1782 prog_id,
1783 request_id,
1784 cr.TRANSACTION_ID,
1785 error_msg,
1786 'SERV_IMPORTANCE_LEVEL',
1787 'MTL_SYSTEM_ITEMS_INTERFACE',
1788 'INV_IOI_SERV_IMPORTANCE_LEVEL',
1789 err_text);
1790 if dumm_status < 0 then
1791 raise LOGGING_ERR;
1792 end if;
1793 status := 1;
1794 end if;
1795 */
1796 stmt := 32;
1797 --Jalaj Srivastava Bug 5017588
1798 --uom fields should only be validated while creating the item
1799 --{
1800 IF cr.TRANSACTION_TYPE='CREATE' THEN
1801 -- validate foreign keys for 11.5.10
1802 if cr.TRACKING_QUANTITY_IND is not null then
1803 --3762750: Using cursor call to avoid multiple parsing
1804 -- Added this assignment for Bug 4096886 -- Anmurali
1805 temp := null;
1806 OPEN c_fndlookup_exists('INV_TRACKING_UOM_TYPE',cr.TRACKING_QUANTITY_IND);
1807 FETCH c_fndlookup_exists INTO temp;
1808 CLOSE c_fndlookup_exists;
1809 IF temp IS NULL THEN
1810 dumm_status := INVPUOPI.mtl_log_interface_err(
1811 cr.organization_id,
1812 user_id,
1813 login_id,
1814 prog_appid,
1815 prog_id,
1816 request_id,
1817 cr.TRANSACTION_ID,
1818 error_msg,
1819 'TRACKING_QUANTITY_IND',
1820 'MTL_SYSTEM_ITEMS_INTERFACE',
1821 'INV_INVALID_ATTR_COL_VALUE',
1822 err_text);
1823 if dumm_status < 0 then
1824 raise LOGGING_ERR;
1825 end if;
1826 status := 1;
1827 END IF;
1828 end if;
1829
1830 if cr.ONT_PRICING_QTY_SOURCE is not null then
1831 begin
1832 --3762750: Using cursor call to avoid multiple parsing
1833 -- Added this assignment for Bug 4096886 -- Anmurali
1834 temp := null;
1835 OPEN c_fndlookup_exists('INV_PRICING_UOM_TYPE',cr.ONT_PRICING_QTY_SOURCE);
1836 FETCH c_fndlookup_exists INTO temp;
1837 CLOSE c_fndlookup_exists;
1838 IF temp IS NULL THEN
1839 RAISE NO_DATA_FOUND;
1840 END IF;
1841 --Jalaj Srivastava Bug 5017588
1842 --remove the condition "Pricing will be supported
1843 --only in OM.J and WMS.J installed environments."
1844 exception
1845 when NO_DATA_FOUND then
1846 dumm_status := INVPUOPI.mtl_log_interface_err(
1847 cr.organization_id,
1848 user_id,
1849 login_id,
1850 prog_appid,
1851 prog_id,
1852 request_id,
1853 cr.TRANSACTION_ID,
1854 error_msg,
1855 'ONT_PRICING_QTY_SOURCE',
1856 'MTL_SYSTEM_ITEMS_INTERFACE',
1857 'INV_INVALID_ATTR_COL_VALUE',
1858 err_text);
1859 if dumm_status < 0 then
1860 raise LOGGING_ERR;
1861 end if;
1862 status := 1;
1863 end;
1864 end if;
1865
1866 if cr.SECONDARY_DEFAULT_IND is not null then
1867 --3762750: Using cursor call to avoid multiple parsing
1868 --Added the assignment for Bug 4096886 -- Anmurali
1869 temp := null;
1870 OPEN c_fndlookup_exists('INV_DEFAULTING_UOM_TYPE',cr.SECONDARY_DEFAULT_IND);
1871 FETCH c_fndlookup_exists INTO temp;
1872 CLOSE c_fndlookup_exists;
1873 IF temp IS NULL THEN
1874 dumm_status := INVPUOPI.mtl_log_interface_err(
1875 cr.organization_id,
1876 user_id,
1877 login_id,
1878 prog_appid,
1879 prog_id,
1880 request_id,
1881 cr.TRANSACTION_ID,
1882 error_msg,
1883 'SECONDARY_DEFAULT_IND',
1884 'MTL_SYSTEM_ITEMS_INTERFACE',
1885 'INV_INVALID_ATTR_COL_VALUE',
1886 err_text);
1887 if dumm_status < 0 then
1888 raise LOGGING_ERR;
1889 end if;
1890 status := 1;
1891 end if;
1892 end if;
1893 if cr.SECONDARY_UOM_CODE is not null THEN
1894 BEGIN
1895 select UNIT_OF_MEASURE into temp_u_o_m
1896 from MTL_UNITS_OF_MEASURE
1897 where UOM_CODE = cr.SECONDARY_UOM_CODE
1898 and SYSDATE < nvl(DISABLE_DATE, SYSDATE+1); /*NP 16OCT94*/
1899 if(cr.SECONDARY_UOM_CODE = cr.PRIMARY_UOM_CODE ) then
1900 dumm_status := INVPUOPI.mtl_log_interface_err(
1901 cr.organization_id,
1902 user_id,
1903 login_id,
1904 prog_appid,
1905 prog_id,
1906 request_id,
1907 cr.TRANSACTION_ID,
1908 error_msg,
1909 'SECONDARY_UOM_CODE',
1910 'MTL_SYSTEM_ITEMS_INTERFACE',
1911 'INV_PRIMARY_SEC_UOM_SAME',
1912 err_text);
1913 if dumm_status < 0 then
1914 raise LOGGING_ERR;
1915 end if;
1916 status := 1;
1917 end if;
1918 EXCEPTION
1919 WHEN NO_DATA_FOUND THEN
1920 dumm_status := INVPUOPI.mtl_log_interface_err(
1921 cr.organization_id,
1922 user_id,
1923 login_id,
1924 prog_appid,
1925 prog_id,
1926 request_id,
1927 cr.TRANSACTION_ID,
1928 error_msg,
1929 'SECONDARY_UOM_CODE',
1930 'MTL_SYSTEM_ITEMS_INTERFACE',
1931 'INV_INVALID_ATTR_COL_VALUE',
1932 err_text);
1933 IF dumm_status < 0 THEN
1934 RAISE LOGGING_ERR;
1935 END IF;
1936 status := 1;
1937 END;
1938 end if;
1939
1940 --If tracking is set to Primary and pricing is set to Secondary, then
1941 --Defaulting can be set to either Default or No Default.
1942 if (nvl(cr.TRACKING_QUANTITY_IND,'P') = 'P'
1943 and (nvl(cr.ONT_PRICING_QTY_SOURCE,'P') = 'S'
1944 and nvl(cr.SECONDARY_DEFAULT_IND,'F') = 'F')) then
1945 dumm_status := INVPUOPI.mtl_log_interface_err(
1946 cr.organization_id,
1947 user_id,
1948 login_id,
1949 prog_appid,
1950 prog_id,
1951 request_id,
1952 cr.TRANSACTION_ID,
1953 error_msg,
1954 'SECONDARY_DEFAULT_IND',
1955 'MTL_SYSTEM_ITEMS_INTERFACE',
1956 'INV_SEC_DEFULT_IS_FIXED',
1957 err_text);
1958 if dumm_status < 0 then
1959 raise LOGGING_ERR;
1960 end if;
1961 status := 1;
1962 end if;
1963
1964 if (nvl(cr.TRACKING_QUANTITY_IND,'P') = 'P'
1965 and nvl(cr.ONT_PRICING_QTY_SOURCE,'P') = 'P') then
1966 if(cr.SECONDARY_DEFAULT_IND IS NOT NULL ) then
1967 dumm_status := INVPUOPI.mtl_log_interface_err(
1968 cr.organization_id,
1969 user_id,
1970 login_id,
1971 prog_appid,
1972 prog_id,
1973 request_id,
1974 cr.TRANSACTION_ID,
1975 error_msg,
1976 'SECONDARY_DEFAULT_IND',
1977 'MTL_SYSTEM_ITEMS_INTERFACE',
1978 'INV_SEC_DEFAULT_NOT_NULL',
1979 err_text);
1980 if dumm_status < 0 then
1981 raise LOGGING_ERR;
1982 end if;
1983 status := 1;
1984 end if;
1985 if(cr.SECONDARY_UOM_CODE IS NOT NULL ) then
1986 dumm_status := INVPUOPI.mtl_log_interface_err(
1987 cr.organization_id,
1988 user_id,
1989 login_id,
1990 prog_appid,
1991 prog_id,
1992 request_id,
1993 cr.TRANSACTION_ID,
1994 error_msg,
1995 'SECONDARY_UOM_CODE',
1996 'MTL_SYSTEM_ITEMS_INTERFACE',
1997 'INV_SEC_UOM_IS_NOT_NULL',
1998 err_text);
1999 if dumm_status < 0 then
2000 raise LOGGING_ERR;
2001 end if;
2002 status := 1;
2003 end if;
2004 end if;
2005 if ( (cr.SECONDARY_DEFAULT_IND IS NULL OR cr.SECONDARY_DEFAULT_IND NOT IN('D','N'))--Bug:3574973
2006 and (cr.DUAL_UOM_DEVIATION_HIGH <> 0
2007 or cr.DUAL_UOM_DEVIATION_LOW <> 0))then
2008 dumm_status := INVPUOPI.mtl_log_interface_err(
2009 cr.organization_id,
2010 user_id,
2011 login_id,
2012 prog_appid,
2013 prog_id,
2014 request_id,
2015 cr.TRANSACTION_ID,
2016 error_msg,
2017 'DUAL_UOM_DEVIATION_HIGH',
2018 'MTL_SYSTEM_ITEMS_INTERFACE',
2019 'INV_UOM_DEV_IS_NOT_ZERO',
2020 err_text);
2021 if dumm_status < 0 then
2022 raise LOGGING_ERR;
2023 end if;
2024 status := 1;
2025 end if;
2026 -- Required values check
2027 if ( (nvl(cr.TRACKING_QUANTITY_IND,'P') = 'PS'
2028 or nvl(cr.ONT_PRICING_QTY_SOURCE,'P') = 'S')
2029 and cr.SECONDARY_UOM_CODE IS NULL) then
2030 dumm_status := INVPUOPI.mtl_log_interface_err(
2031 cr.organization_id,
2032 user_id,
2033 login_id,
2034 prog_appid,
2035 prog_id,
2036 request_id,
2037 cr.TRANSACTION_ID,
2038 error_msg,
2039 'SECONDARY_UOM_CODE',
2040 'MTL_SYSTEM_ITEMS_INTERFACE',
2041 'INV_REQUIRED_FIELDS',
2042 err_text);
2043 if dumm_status < 0 then
2044 raise LOGGING_ERR;
2045 end if;
2046 status := 1;
2047 end if;
2048 if ( (nvl(cr.TRACKING_QUANTITY_IND,'P') = 'PS'
2049 or nvl(cr.ONT_PRICING_QTY_SOURCE,'P') = 'S')
2050 and cr.SECONDARY_DEFAULT_IND IS NULL) then
2051 dumm_status := INVPUOPI.mtl_log_interface_err(
2052 cr.organization_id,
2053 user_id,
2054 login_id,
2055 prog_appid,
2056 prog_id,
2057 request_id,
2058 cr.TRANSACTION_ID,
2059 error_msg,
2060 'SECONDARY_DEFAULT_IND',
2061 'MTL_SYSTEM_ITEMS_INTERFACE',
2062 'INV_REQUIRED_FIELDS',
2063 err_text);
2064 if dumm_status < 0 then
2065 raise LOGGING_ERR;
2066 end if;
2067 status := 1;
2068 end if;
2069 if ( NVL(cr.DUAL_UOM_DEVIATION_HIGH,-1) < 0
2070 or NVL(cr.DUAL_UOM_DEVIATION_LOW,-1) < 0) then
2071 dumm_status := INVPUOPI.mtl_log_interface_err(
2072 cr.organization_id,
2073 user_id,
2074 login_id,
2075 prog_appid,
2076 prog_id,
2077 request_id,
2078 cr.TRANSACTION_ID,
2079 error_msg,
2080 'DUAL_UOM_DEVIATION_FACTORS',
2081 'MTL_SYSTEM_ITEMS_INTERFACE',
2082 'INV_POSITIVE_NUMBER',
2083 err_text);
2084 if dumm_status < 0 then
2085 raise LOGGING_ERR;
2086 end if;
2087 status := 1;
2088 end if;
2089
2090 /* Jalaj Srivastava Bug 5017588
2091 secondary uom class should be same across all orgs*/
2092 --{
2093 if cr.SECONDARY_UOM_CODE is not null THEN
2094 begin
2095 select UOM_CLASS
2096 into temp_uom_class
2097 from MTL_UNITS_OF_MEASURE
2098 where UOM_CODE = cr.SECONDARY_UOM_CODE;
2099
2100 select 'x' into temp
2101 from MTL_SYSTEM_ITEMS msi
2102 where INVENTORY_ITEM_ID = cr.INVENTORY_ITEM_ID
2103 and secondary_uom_code IS NOT NULL
2104 and not exists (select UNIT_OF_MEASURE
2105 from MTL_UNITS_OF_MEASURE MUOM
2106 where UOM_CLASS = temp_uom_class
2107 and MUOM.UOM_CODE = msi.SECONDARY_UOM_CODE)
2108 and rownum = 1;
2109
2110 /* If any rows are fetched here then it is invalid
2111 ** Call the error routine
2112 ** else goto the exception and exit this validation
2113 ** succesfully.
2114 */
2115
2116 dumm_status := INVPUOPI.mtl_log_interface_err(
2117 cr.organization_id,
2118 user_id,
2119 login_id,
2120 prog_appid,
2121 prog_id,
2122 request_id,
2123 cr.TRANSACTION_ID,
2124 error_msg,
2125 'SECONDARY_UOM_CODE',
2126 'MTL_SYSTEM_ITEMS_INTERFACE',
2127 'INV_SEC_UOM_MISMATCH_CLASS',
2128 err_text);
2129 if dumm_status < 0 then
2130 raise LOGGING_ERR;
2131 end if;
2132 status := 1;
2133
2134 exception
2135 when NO_DATA_FOUND then
2136 /*Do nothing. Valid data */
2137 null;
2138 end;
2139 end if;--}
2140
2141 END IF;--}
2142 /* Start Bug 3713912 */
2143 if (cr.TRACKING_QUANTITY_IND <> 'P'
2144 and nvl(cr.SERIAL_NUMBER_CONTROL_CODE,1) <> 1) then
2145 dumm_status := INVPUOPI.mtl_log_interface_err(
2146 cr.organization_id,
2147 user_id,
2148 login_id,
2149 prog_appid,
2150 prog_id,
2151 request_id,
2152 cr.TRANSACTION_ID,
2153 error_msg,
2154 'TRACKING_QUANTITY_IND',
2155 'MTL_SYSTEM_ITEMS_INTERFACE',
2156 'INV_INVALID_TRACKING_QTY_IND',
2157 err_text);
2158 if dumm_status < 0 then
2159 raise LOGGING_ERR;
2160 end if;
2161 status := 1;
2162
2163 end if;
2164 /* End Bug 3713912 */
2165 stmt := 34;
2166 -- Start : 2339789: Dimension UOM code validation
2167 -- validate foreign keys
2168 if cr.DIMENSION_UOM_CODE is not null then
2169 begin
2170 select 'x' into temp
2171 from MTL_UNITS_OF_MEASURE
2172 where UOM_CODE = cr.DIMENSION_UOM_CODE;
2173 exception
2174 when NO_DATA_FOUND then
2175 dumm_status := INVPUOPI.mtl_log_interface_err(
2176 cr.organization_id,
2177 user_id,
2178 login_id,
2179 prog_appid,
2180 prog_id,
2181 request_id,
2182 cr.TRANSACTION_ID,
2183 error_msg,
2184 'DIMENSION_UOM_CODE',
2185 'MTL_SYSTEM_ITEMS_INTERFACE',
2186 'INV_IOI_DIMENSION_UOM_CODE',
2187 err_text);
2188 if dumm_status < 0 then
2189 raise LOGGING_ERR;
2190 end if;
2191 status := 1;
2192 end;
2193 end if;
2194 -- End : 2339789
2195 stmt := 36;
2196 /* NP26DEC94 : New code to update process_flag.
2197 ** This code necessiated due to the breaking up INVPVHDR into
2198 ** 6 smaller packages to overcome PL/SQL limitations with code size.
2199 ** Let's update the process flag for the record
2200 ** Give it value 42 if all okay and 32 if some validation failed in this procedure
2201 ** Need to do this ONLY if all previous validation okay.
2202 ** The process flag values that are possible at this time are
2203 ** 31, 41 :set by INVPVHDR
2204 ** 32, 42 :set by INVPVDR2
2205 ** 33, 43 :set by INVPVDR3
2206 ** 34, 44 :set by INVPVDR4
2207 */
2208
2209 /* Bug 4705184
2210 select process_flag into temp_proc_flag
2211 from MTL_SYSTEM_ITEMS_INTERFACE
2212 where inventory_item_id = l_item_id
2213 and set_process_id + 0 = xset_id
2214 and process_flag in (31, 32, 33, 34, 44)
2215 and organization_id = cr.organization_id
2216 and rownum < 2; */
2217
2218 /* set value of process_flag to 45 or 35 depending on
2219 ** value of the variable: status.
2220 ** Essentially, we check to see if validation has not already failed in one of
2221 ** the previous packages.
2222 */
2223 if (temp_proc_flag <> 31 and temp_proc_flag <> 32
2224 and temp_proc_flag <> 33 and temp_proc_flag <> 34) then
2225 update MTL_SYSTEM_ITEMS_INTERFACE
2226 set process_flag = DECODE(status,0,45,35),
2227 PRIMARY_UOM_CODE = cr.primary_uom_code,
2228 primary_unit_of_measure = cr.primary_unit_of_measure
2229 where inventory_item_id = l_item_id
2230 -- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
2231 and set_process_id = xset_id
2232 and process_flag = 44
2233 and organization_id = cr.organization_id;
2234 end if;
2235
2236
2237 end loop;
2238
2239 return(0);
2240
2241 exception
2242 when LOGGING_ERR then
2243 return(dumm_status);
2244 when VALIDATE_ERR then
2245 dumm_status := INVPUOPI.mtl_log_interface_err(
2246 l_org_id,
2247 user_id,
2248 login_id,
2249 prog_appid,
2250 prog_id,
2251 request_id,
2252 trans_id,
2253 err_text,
2254 'validation_error ' || stmt,
2255 'MTL_SYSTEM_ITEMS_INTERFACE',
2256 'BOM_OP_VALIDATION_ERR',
2257 err_text);
2258 return(status);
2259 when OTHERS then
2260 err_text := substr('INVPVALI.validate_item_header5' || SQLERRM , 1, 240);
2261 return(SQLCODE);
2262
2263 end validate_item_header5;
2264
2265 end INVPVDR5;