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