[Home] [Help]
PACKAGE BODY: APPS.INVPVALM
Source
1 PACKAGE BODY INVPVALM AS
2 /* $Header: INVPVM1B.pls 120.1 2006/08/08 06:47:53 lparihar noship $ */
3
4 FUNCTION validate_item_org1
5 (
6 org_id NUMBER,
7 all_org NUMBER := 2,
8 prog_appid NUMBER := -1,
9 prog_id NUMBER := -1,
10 request_id NUMBER := -1,
11 user_id NUMBER := -1,
12 login_id NUMBER := -1,
13 err_text IN OUT NOCOPY VARCHAR2,
14 xset_id IN NUMBER DEFAULT -999
15 )
16 RETURN INTEGER
17 IS
18
19 -- Cursor to retrieve item child org records
20
21 CURSOR cc
22 IS
23 SELECT
24 msii.TRANSACTION_ID,
25 msii.INVENTORY_ITEM_ID III,
26 msii.ORGANIZATION_ID ORGID,
27 mp.MASTER_ORGANIZATION_ID MORGID,
28 msii.DESCRIPTION,
29 msii.LONG_DESCRIPTION,
30 msii.BUYER_ID,
31 msii.ACCOUNTING_RULE_ID,
32 msii.INVOICING_RULE_ID,
33 msii.PURCHASING_ITEM_FLAG,
34 msii.SHIPPABLE_ITEM_FLAG,
35 msii.CUSTOMER_ORDER_FLAG,
36 msii.INTERNAL_ORDER_FLAG,
37 msii.INVENTORY_ITEM_FLAG,
38 msii.PURCHASING_ENABLED_FLAG,
39 msii.CUSTOMER_ORDER_ENABLED_FLAG,
40 msii.INTERNAL_ORDER_ENABLED_FLAG,
41 msii.SO_TRANSACTIONS_FLAG,
42 msii.MTL_TRANSACTIONS_ENABLED_FLAG,
43 msii.STOCK_ENABLED_FLAG,
44 msii.BOM_ENABLED_FLAG,
45 msii.BUILD_IN_WIP_FLAG,
46 msii.REVISION_QTY_CONTROL_CODE,
47 msii.ITEM_CATALOG_GROUP_ID,
48 msii.CATALOG_STATUS_FLAG,
49 msii.RETURNABLE_FLAG,
50 msii.DEFAULT_SHIPPING_ORG,
51 msii.COLLATERAL_FLAG,
52 msii.TAXABLE_FLAG,
53 msii.PURCHASING_TAX_CODE,
54 msii.QTY_RCV_EXCEPTION_CODE,
55 msii.ALLOW_ITEM_DESC_UPDATE_FLAG,
56 msii.INSPECTION_REQUIRED_FLAG,
57 msii.RECEIPT_REQUIRED_FLAG,
58 msii.MARKET_PRICE,
59 msii.HAZARD_CLASS_ID,
60 msii.RFQ_REQUIRED_FLAG,
61 msii.QTY_RCV_TOLERANCE,
62 msii.LIST_PRICE_PER_UNIT,
63 msii.UN_NUMBER_ID,
64 msii.PRICE_TOLERANCE_PERCENT,
65 msii.ASSET_CATEGORY_ID,
66 msii.ROUNDING_FACTOR,
67 msii.UNIT_OF_ISSUE,
68 msii.ENFORCE_SHIP_TO_LOCATION_CODE,
69 msii.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
70 msii.ALLOW_UNORDERED_RECEIPTS_FLAG,
71 msii.ALLOW_EXPRESS_DELIVERY_FLAG,
72 msii.DAYS_EARLY_RECEIPT_ALLOWED,
73 msii.DAYS_LATE_RECEIPT_ALLOWED,
74 msii.RECEIPT_DAYS_EXCEPTION_CODE,
75 msii.RECEIVING_ROUTING_ID,
76 msii.INVOICE_CLOSE_TOLERANCE,
77 msii.RECEIVE_CLOSE_TOLERANCE,
78 msii.AUTO_LOT_ALPHA_PREFIX,
79 msii.CHECK_SHORTAGES_FLAG, /*CK 21MAY98 Added new attribute*/
80 msii.EFFECTIVITY_CONTROL
81 , msii.OVERCOMPLETION_TOLERANCE_TYPE
82 , msii.OVERCOMPLETION_TOLERANCE_VALUE
83 , msii.OVER_SHIPMENT_TOLERANCE
84 , msii.UNDER_SHIPMENT_TOLERANCE
85 , msii.OVER_RETURN_TOLERANCE
86 , msii.UNDER_RETURN_TOLERANCE
87 , msii.EQUIPMENT_TYPE
88 , msii.RECOVERED_PART_DISP_CODE
89 , msii.DEFECT_TRACKING_ON_FLAG
90 , msii.EVENT_FLAG
91 , msii.ELECTRONIC_FLAG
92 , msii.DOWNLOADABLE_FLAG
93 , msii.VOL_DISCOUNT_EXEMPT_FLAG
94 , msii.COUPON_EXEMPT_FLAG
95 , msii.COMMS_NL_TRACKABLE_FLAG
96 , msii.ASSET_CREATION_CODE
97 , msii.COMMS_ACTIVATION_REQD_FLAG
98 , msii.ORDERABLE_ON_WEB_FLAG
99 , msii.BACK_ORDERABLE_FLAG
100 , msii.WEB_STATUS
101 , msii.INDIVISIBLE_FLAG
102 , msii.DIMENSION_UOM_CODE
103 , msii.UNIT_LENGTH
104 , msii.UNIT_WIDTH
105 , msii.UNIT_HEIGHT
106 , msii.BULK_PICKED_FLAG
107 , msii.LOT_STATUS_ENABLED
108 , msii.DEFAULT_LOT_STATUS_ID
109 , msii.SERIAL_STATUS_ENABLED
110 , msii.DEFAULT_SERIAL_STATUS_ID
111 , msii.LOT_SPLIT_ENABLED
112 , msii.LOT_MERGE_ENABLED
113 , msii.INVENTORY_CARRY_PENALTY
114 , msii.OPERATION_SLACK_PENALTY
115 , msii.FINANCING_ALLOWED_FLAG
116 , msii.EAM_ITEM_TYPE
117 , msii.EAM_ACTIVITY_TYPE_CODE
118 , msii.EAM_ACTIVITY_CAUSE_CODE
119 , msii.EAM_ACT_NOTIFICATION_FLAG
120 , msii.EAM_ACT_SHUTDOWN_STATUS
121 , msii.DUAL_UOM_CONTROL
122 , msii.SECONDARY_UOM_CODE
123 , msii.DUAL_UOM_DEVIATION_HIGH
124 , msii.DUAL_UOM_DEVIATION_LOW
125 --, msii.SERVICE_ITEM_FLAG
126 --, msii.USAGE_ITEM_FLAG
127 , msii.CONTRACT_ITEM_TYPE_CODE
128 -- , msii.SUBSCRIPTION_DEPEND_FLAG
129 --
130 , msii.SERV_REQ_ENABLED_CODE
131 , msii.SERV_BILLING_ENABLED_FLAG
132 -- , msii.SERV_IMPORTANCE_LEVEL
133 , msii.PLANNED_INV_POINT_FLAG
134 , msii.LOT_TRANSLATE_ENABLED
135 , msii.DEFAULT_SO_SOURCE_TYPE
136 , msii.CREATE_SUPPLY_FLAG
137 , msii.SUBSTITUTION_WINDOW_CODE
138 , msii.SUBSTITUTION_WINDOW_DAYS
139 FROM
140 MTL_SYSTEM_ITEMS_INTERFACE msii
141 , MTL_PARAMETERS mp
142 WHERE
143 ( (msii.organization_id = org_id) OR (all_Org = 1) )
144 AND msii.process_flag = 2
145 AND msii.organization_id = mp.organization_id
146 AND msii.set_process_id = xset_id
147 AND msii.organization_id <> mp.master_organization_id;
148
149 -- Attributes that are Item level;
150 -- can't be different from master org's value.
151
152 CURSOR ee
153 IS
154 --SELECT SUBSTR(ATTRIBUTE_NAME,18) Attribute_Code
155 SELECT attribute_name
156 , control_level
157 FROM mtl_item_attributes
158 WHERE
159 control_level = 1
160 AND attribute_group_id_gui IN
161 (20, 25, 30, 31, 35, 40, 41, 51,
162 60, 62, 65, 70, 80, 90, 100, 120, 130);
163 /* Start Bug 3713912 */ --added 130 in the above where clause.
164
165 msicount number;
166 msiicount number;
167 l_item_id number;
168 l_org_id number;
169 trans_id number;
170 ext_flag number := 0;
171 error_msg varchar2(70);
172 status number;
173 dumm_status number;
174 master_org_id number;
175 LOGGING_ERR exception;
176 VALIDATE_ERR exception;
177 X_TRUE NUMBER := 1;
178
179 A_DESCRIPTION number := 2;
180 A_LONG_DESCRIPTION number := 2;
181 A_BUYER_ID number := 2;
182 A_ACCOUNTING_RULE_ID number := 2;
183 A_INVOICING_RULE_ID number := 2;
184 A_PURCHASING_ITEM_FLAG number := 2;
185 A_SHIPPABLE_ITEM_FLAG number := 2;
186 A_CUSTOMER_ORDER_FLAG number := 2;
187 A_INTERNAL_ORDER_FLAG number := 2;
188 A_INVENTORY_ITEM_FLAG number := 2;
189 A_PURCHASING_ENABLED_FLAG number := 2;
190 A_CUSTOMER_ORDER_ENABLED_FLAG number := 2;
191 A_INTERNAL_ORDER_ENABLED_FLAG number := 2;
192 A_SO_TRANSACTIONS_FLAG number := 2;
193 A_MTL_TRANSACTIONS_ENABLED_F number := 2;
194 A_STOCK_ENABLED_FLAG number := 2;
195 A_BOM_ENABLED_FLAG number := 2;
196 A_BUILD_IN_WIP_FLAG number := 2;
197 A_REVISION_QTY_CONTROL_CODE number := 2;
198 A_ITEM_CATALOG_GROUP_ID number := 1;--Bug:3565730
199 A_CATALOG_STATUS_FLAG number := 2;
200 A_RETURNABLE_FLAG number := 2;
201 A_DEFAULT_SHIPPING_ORG number := 2;
202 A_COLLATERAL_FLAG number := 2;
203 A_TAXABLE_FLAG number := 2;
204 A_PURCHASING_TAX_CODE number := 2;
205 A_QTY_RCV_EXCEPTION_CODE number := 2;
206 A_ALLOW_ITEM_DESC_UPDATE_FLAG number := 2;
207 A_INSPECTION_REQUIRED_FLAG number := 2;
208 A_RECEIPT_REQUIRED_FLAG number := 2;
209 A_MARKET_PRICE number := 2;
210 A_HAZARD_CLASS_ID number := 2;
211 A_RFQ_REQUIRED_FLAG number := 2;
212 A_QTY_RCV_TOLERANCE number := 2;
213 A_LIST_PRICE_PER_UNIT number := 2;
214 A_UN_NUMBER_ID number := 2;
215 A_PRICE_TOLERANCE_PERCENT number := 2;
216 A_ASSET_CATEGORY_ID number := 2;
217 A_ROUNDING_FACTOR number := 2;
218 A_UNIT_OF_ISSUE number := 2;
219 A_ENFORCE_SHIP_TO_LOCATION_C number := 2;
220 A_ALLOW_SUBSTITUTE_RECEIPTS_F number := 2;
221 A_ALLOW_UNORDERED_RECEIPTS_F number := 2;
222 A_ALLOW_EXPRESS_DELIVERY_FLAG number := 2;
223 A_DAYS_EARLY_RECEIPT_ALLOWED number := 2;
224 A_DAYS_LATE_RECEIPT_ALLOWED number := 2;
225 A_RECEIPT_DAYS_EXCEPTION_CODE number := 2;
226 A_RECEIVING_ROUTING_ID number := 2;
227 A_INVOICE_CLOSE_TOLERANCE number := 2;
228 A_RECEIVE_CLOSE_TOLERANCE number := 2;
229 A_AUTO_LOT_ALPHA_PREFIX number := 2;
230 /*CK 21MAY98 Added for new attribute*/
231 A_CHECK_SHORTAGES_FLAG number := 2;
232 A_EFFECTIVITY_CONTROL NUMBER := 2;
233 A_OVERCOMPLETION_TOLERANCE_TYP NUMBER := 2;
234 A_OVERCOMPLETION_TOLERANCE_VAL NUMBER := 2;
235 A_OVER_SHIPMENT_TOLERANCE NUMBER := 2;
236 A_UNDER_SHIPMENT_TOLERANCE NUMBER := 2;
237 A_OVER_RETURN_TOLERANCE NUMBER := 2;
238 A_UNDER_RETURN_TOLERANCE NUMBER := 2;
239 A_EQUIPMENT_TYPE NUMBER := 2;
240 A_RECOVERED_PART_DISP_CODE NUMBER := 2;
241 A_DEFECT_TRACKING_ON_FLAG NUMBER := 2;
242 A_EVENT_FLAG NUMBER := 2;
243 A_ELECTRONIC_FLAG NUMBER := 2;
244 A_DOWNLOADABLE_FLAG NUMBER := 2;
245 A_VOL_DISCOUNT_EXEMPT_FLAG NUMBER := 2;
246 A_COUPON_EXEMPT_FLAG NUMBER := 2;
247 A_COMMS_NL_TRACKABLE_FLAG NUMBER := 2;
248 A_ASSET_CREATION_CODE NUMBER := 2;
249 A_COMMS_ACTIVATION_REQD_FLAG NUMBER := 2;
250 A_ORDERABLE_ON_WEB_FLAG NUMBER := 2;
251 A_BACK_ORDERABLE_FLAG NUMBER := 2;
252 A_WEB_STATUS number := 2;
253 A_INDIVISIBLE_FLAG number := 2;
254 A_DIMENSION_UOM_CODE NUMBER := 2;
255 A_UNIT_LENGTH NUMBER := 2;
256 A_UNIT_WIDTH NUMBER := 2;
257 A_UNIT_HEIGHT NUMBER := 2;
258 A_BULK_PICKED_FLAG NUMBER := 2;
259 A_LOT_STATUS_ENABLED NUMBER := 2;
260 A_DEFAULT_LOT_STATUS_ID NUMBER := 2;
261 A_SERIAL_STATUS_ENABLED NUMBER := 2;
262 A_DEFAULT_SERIAL_STATUS_ID NUMBER := 2;
263 A_LOT_SPLIT_ENABLED NUMBER := 2;
264 A_LOT_MERGE_ENABLED NUMBER := 2;
265 A_INVENTORY_CARRY_PENALTY NUMBER := 2;
266 A_OPERATION_SLACK_PENALTY NUMBER := 2;
267 A_FINANCING_ALLOWED_FLAG NUMBER := 2;
268
269 A_EAM_ITEM_TYPE NUMBER := 2;
270 A_EAM_ACTIVITY_TYPE_CODE NUMBER := 2;
271 A_EAM_ACTIVITY_CAUSE_CODE NUMBER := 2;
272 A_EAM_ACT_NOTIFICATION_FLAG NUMBER := 2;
273 A_EAM_ACT_SHUTDOWN_STATUS NUMBER := 2;
274 A_DUAL_UOM_CONTROL NUMBER := 2;
275 A_SECONDARY_UOM_CODE NUMBER := 2;
276 A_DUAL_UOM_DEVIATION_HIGH NUMBER := 2;
277 A_DUAL_UOM_DEVIATION_LOW NUMBER := 2;
278 -- A_SERVICE_ITEM_FLAG NUMBER := 2;
279 -- A_USAGE_ITEM_FLAG NUMBER := 2;
280 A_CONTRACT_ITEM_TYPE_CODE NUMBER := 2;
281 -- A_SUBSCRIPTION_DEPEND_FLAG NUMBER := 2;
282
283 A_SERV_REQ_ENABLED_CODE NUMBER := 2;
284 A_SERV_BILLING_ENABLED_FLAG NUMBER := 2;
285 -- A_SERV_IMPORTANCE_LEVEL NUMBER := 2;
286 A_PLANNED_INV_POINT_FLAG NUMBER := 2;
287 A_LOT_TRANSLATE_ENABLED NUMBER := 2;
288 A_DEFAULT_SO_SOURCE_TYPE NUMBER := 2;
289 A_CREATE_SUPPLY_FLAG NUMBER := 2;
290 A_SUBSTITUTION_WINDOW_CODE NUMBER := 2;
291 A_SUBSTITUTION_WINDOW_DAYS NUMBER := 2;
292
293 BEGIN
294
295 /* set the attribute level variables to be used when validating a child's item
296 ** level attributes against the master org's attribute value. this is done
297 ** outside the loop so that it is only done once for all the records
298 ** instead of once PER record.
299 */
300
301 FOR att IN ee LOOP
302
303 if substr(att.attribute_name,18) = 'DESCRIPTION' then
304 A_DESCRIPTION := att.control_level;
305 end if;
306 if substr(att.attribute_name,18) = 'LONG_DESCRIPTION' then
307 A_LONG_DESCRIPTION := att.control_level;
308 end if;
309 if substr(att.attribute_name,18) = 'BUYER_ID' then
310 A_BUYER_ID := att.control_level;
311 end if;
312 if substr(att.attribute_name,18) = 'ACCOUNTING_RULE_ID' then
313 A_ACCOUNTING_RULE_ID := att.control_level;
314 end if;
315 if substr(att.attribute_name,18) = 'INVOICING_RULE_ID' then
316 A_INVOICING_RULE_ID := att.control_level;
317 end if;
318 if substr(att.attribute_name,18) = 'PURCHASING_ITEM_FLAG' then
319 A_PURCHASING_ITEM_FLAG := att.control_level;
320 end if;
321 if substr(att.attribute_name,18) = 'SHIPPABLE_ITEM_FLAG' then
322 A_SHIPPABLE_ITEM_FLAG := att.control_level;
323 end if;
324 if substr(att.attribute_name,18) = 'CUSTOMER_ORDER_FLAG' then
325 A_CUSTOMER_ORDER_FLAG := att.control_level;
326 end if;
327 if substr(att.attribute_name,18) = 'INTERNAL_ORDER_FLAG' then
328 A_INTERNAL_ORDER_FLAG := att.control_level;
329 end if;
330 if substr(att.attribute_name,18) = 'INVENTORY_ITEM_FLAG' then
331 A_INVENTORY_ITEM_FLAG := att.control_level;
332 end if;
333 if substr(att.attribute_name,18) = 'PURCHASING_ENABLED_FLAG' then
334 A_PURCHASING_ENABLED_FLAG := att.control_level;
335 end if;
336 if substr(att.attribute_name,18) = 'CUSTOMER_ORDER_ENABLED_FLAG' then
337 A_CUSTOMER_ORDER_ENABLED_FLAG := att.control_level;
338 end if;
339 if substr(att.attribute_name,18) = 'INTERNAL_ORDER_ENABLED_FLAG' then
340 A_INTERNAL_ORDER_ENABLED_FLAG := att.control_level;
341 end if;
342 if substr(att.attribute_name,18) = 'SO_TRANSACTIONS_FLAG' then
343 A_SO_TRANSACTIONS_FLAG := att.control_level;
344 end if;
345 if substr(att.attribute_name,18) = 'MTL_TRANSACTIONS_ENABLED_FLAG' then
346 A_MTL_TRANSACTIONS_ENABLED_F := att.control_level;
347 end if;
348 if substr(att.attribute_name,18) = 'STOCK_ENABLED_FLAG' then
349 A_STOCK_ENABLED_FLAG := att.control_level;
350 end if;
351 if substr(att.attribute_name,18) = 'BOM_ENABLED_FLAG' then
352 A_BOM_ENABLED_FLAG := att.control_level;
353 end if;
354 if substr(att.attribute_name,18) = 'BUILD_IN_WIP_FLAG' then
355 A_BUILD_IN_WIP_FLAG := att.control_level;
356 end if;
357 if substr(att.attribute_name,18) = 'REVISION_QTY_CONTROL_CODE' then
358 A_REVISION_QTY_CONTROL_CODE := att.control_level;
359 end if;
360 if substr(att.attribute_name,18) = 'ITEM_CATALOG_GROUP_ID' then
361 A_ITEM_CATALOG_GROUP_ID := att.control_level;
362 end if;
363 if substr(att.attribute_name,18) = 'CATALOG_STATUS_FLAG' then
364 A_CATALOG_STATUS_FLAG := att.control_level;
365 end if;
366 if substr(att.attribute_name,18) = 'RETURNABLE_FLAG' then
367 A_RETURNABLE_FLAG := att.control_level;
368 end if;
369 if substr(att.attribute_name,18) = 'DEFAULT_SHIPPING_ORG' then
370 A_DEFAULT_SHIPPING_ORG := att.control_level;
371 end if;
372 if substr(att.attribute_name,18) = 'COLLATERAL_FLAG' then
373 A_COLLATERAL_FLAG := att.control_level;
374 end if;
375 if substr(att.attribute_name,18) = 'TAXABLE_FLAG' then
376 A_TAXABLE_FLAG := att.control_level;
377 end if;
378
379 -- Bug 1014929 (1000764)
380 if substr(att.attribute_name,18) = 'PURCHASING_TAX_CODE' then
381 A_PURCHASING_TAX_CODE := att.control_level;
382 end if;
383
384 if substr(att.attribute_name,18) = 'QTY_RCV_EXCEPTION_CODE' then
385 A_QTY_RCV_EXCEPTION_CODE := att.control_level;
386 end if;
387 if substr(att.attribute_name,18) = 'ALLOW_ITEM_DESC_UPDATE_FLAG' then
388 A_ALLOW_ITEM_DESC_UPDATE_FLAG := att.control_level;
389 end if;
390 if substr(att.attribute_name,18) = 'INSPECTION_REQUIRED_FLAG' then
391 A_INSPECTION_REQUIRED_FLAG := att.control_level;
392 end if;
393 if substr(att.attribute_name,18) = 'RECEIPT_REQUIRED_FLAG' then
394 A_RECEIPT_REQUIRED_FLAG := att.control_level;
395 end if;
396 if substr(att.attribute_name,18) = 'MARKET_PRICE' then
397 A_MARKET_PRICE := att.control_level;
398 end if;
399 if substr(att.attribute_name,18) = 'HAZARD_CLASS_ID' then
400 A_HAZARD_CLASS_ID := att.control_level;
401 end if;
402 if substr(att.attribute_name,18) = 'RFQ_REQUIRED_FLAG' then
403 A_RFQ_REQUIRED_FLAG := att.control_level;
404 end if;
405 if substr(att.attribute_name,18) = 'QTY_RCV_TOLERANCE' then
406 A_QTY_RCV_TOLERANCE := att.control_level;
407 end if;
408 if substr(att.attribute_name,18) = 'LIST_PRICE_PER_UNIT' then
409 A_LIST_PRICE_PER_UNIT := att.control_level;
410 end if;
411 if substr(att.attribute_name,18) = 'UN_NUMBER_ID' then
412 A_UN_NUMBER_ID := att.control_level;
413 end if;
414 if substr(att.attribute_name,18) = 'PRICE_TOLERANCE_PERCENT' then
415 A_PRICE_TOLERANCE_PERCENT := att.control_level;
416 end if;
417 if substr(att.attribute_name,18) = 'ASSET_CATEGORY_ID' then
418 A_ASSET_CATEGORY_ID := att.control_level;
419 end if;
420 if substr(att.attribute_name,18) = 'ROUNDING_FACTOR' then
421 A_ROUNDING_FACTOR := att.control_level;
422 end if;
423 if substr(att.attribute_name,18) = 'UNIT_OF_ISSUE' then
424 A_UNIT_OF_ISSUE := att.control_level;
425 end if;
426 if substr(att.attribute_name,18) = 'ENFORCE_SHIP_TO_LOCATION_CODE' then
427 A_ENFORCE_SHIP_TO_LOCATION_C := att.control_level;
428 end if;
429 if substr(att.attribute_name,18) = 'ALLOW_SUBSTITUTE_RECEIPTS_FLAG' then
430 A_ALLOW_SUBSTITUTE_RECEIPTS_F := att.control_level;
431 end if;
432 if substr(att.attribute_name,18) = 'ALLOW_UNORDERED_RECEIPTS_FLAG' then
433 A_ALLOW_UNORDERED_RECEIPTS_F := att.control_level;
434 end if;
435 if substr(att.attribute_name,18) = 'ALLOW_EXPRESS_DELIVERY_FLAG' then
436 A_ALLOW_EXPRESS_DELIVERY_FLAG := att.control_level;
437 end if;
438 if substr(att.attribute_name,18) = 'DAYS_EARLY_RECEIPT_ALLOWED' then
439 A_DAYS_EARLY_RECEIPT_ALLOWED := att.control_level;
440 end if;
441 if substr(att.attribute_name,18) = 'DAYS_LATE_RECEIPT_ALLOWED' then
442 A_DAYS_LATE_RECEIPT_ALLOWED := att.control_level;
443 end if;
444 if substr(att.attribute_name,18) = 'RECEIPT_DAYS_EXCEPTION_CODE' then
445 A_RECEIPT_DAYS_EXCEPTION_CODE := att.control_level;
446 end if;
447 if substr(att.attribute_name,18) = 'RECEIVING_ROUTING_ID' then
448 A_RECEIVING_ROUTING_ID := att.control_level;
449 end if;
450 if substr(att.attribute_name,18) = 'INVOICE_CLOSE_TOLERANCE' then
451 A_INVOICE_CLOSE_TOLERANCE := att.control_level;
452 end if;
453 if substr(att.attribute_name,18) = 'RECEIVE_CLOSE_TOLERANCE' then
454 A_RECEIVE_CLOSE_TOLERANCE := att.control_level;
455 end if;
456 if substr(att.attribute_name,18) = 'AUTO_LOT_ALPHA_PREFIX' then
457 A_AUTO_LOT_ALPHA_PREFIX := att.control_level;
458 end if;
459
460 /*CK 21MAY98 Added for new attribute*/
461 if substr(att.attribute_name,18) = 'CHECK_SHORTAGES_FLAG' then
462 A_CHECK_SHORTAGES_FLAG := att.control_level;
463 end if;
464
465 IF substr(att.attribute_name,18) = 'EFFECTIVITY_CONTROL' THEN
466 A_EFFECTIVITY_CONTROL := att.control_level;
467 END IF;
468
469 IF substr(att.attribute_name,18) = 'OVERCOMPLETION_TOLERANCE_TYPE' THEN
470 A_OVERCOMPLETION_TOLERANCE_TYP := att.control_level;
471 END IF;
472 IF substr(att.attribute_name,18) = 'OVERCOMPLETION_TOLERANCE_VALUE' THEN
473 A_OVERCOMPLETION_TOLERANCE_VAL := att.control_level;
474 END IF;
475 IF substr(att.attribute_name,18) = 'OVER_SHIPMENT_TOLERANCE' THEN
476 A_OVER_SHIPMENT_TOLERANCE := att.control_level;
477 END IF;
478 IF substr(att.attribute_name,18) = 'UNDER_SHIPMENT_TOLERANCE' THEN
479 A_UNDER_SHIPMENT_TOLERANCE := att.control_level;
480 END IF;
481 IF substr(att.attribute_name,18) = 'OVER_RETURN_TOLERANCE' THEN
482 A_OVER_RETURN_TOLERANCE := att.control_level;
483 END IF;
484 IF substr(att.attribute_name,18) = 'UNDER_RETURN_TOLERANCE' THEN
485 A_UNDER_RETURN_TOLERANCE := att.control_level;
486 END IF;
487 IF substr(att.attribute_name,18) = 'EQUIPMENT_TYPE' THEN
488 A_EQUIPMENT_TYPE := att.control_level;
489 END IF;
490 IF substr(att.attribute_name,18) = 'RECOVERED_PART_DISP_CODE' THEN
491 A_RECOVERED_PART_DISP_CODE := att.control_level;
492 END IF;
493 IF substr(att.attribute_name,18) = 'DEFECT_TRACKING_ON_FLAG' THEN
494 A_DEFECT_TRACKING_ON_FLAG := att.control_level;
495 END IF;
496 IF substr(att.attribute_name,18) = 'EVENT_FLAG' THEN
497 A_EVENT_FLAG := att.control_level;
498 END IF;
499 IF substr(att.attribute_name,18) = 'ELECTRONIC_FLAG' THEN
500 A_ELECTRONIC_FLAG := att.control_level;
501 END IF;
502 IF substr(att.attribute_name,18) = 'DOWNLOADABLE_FLAG' THEN
503 A_DOWNLOADABLE_FLAG := att.control_level;
504 END IF;
505 IF substr(att.attribute_name,18) = 'VOL_DISCOUNT_EXEMPT_FLAG' THEN
506 A_VOL_DISCOUNT_EXEMPT_FLAG := att.control_level;
507 END IF;
508 IF substr(att.attribute_name,18) = 'COUPON_EXEMPT_FLAG' THEN
509 A_COUPON_EXEMPT_FLAG := att.control_level;
510 END IF;
511 IF substr(att.attribute_name,18) = 'COMMS_NL_TRACKABLE_FLAG' THEN
512 A_COMMS_NL_TRACKABLE_FLAG := att.control_level;
513 END IF;
514 IF substr(att.attribute_name,18) = 'ASSET_CREATION_CODE' THEN
515 A_ASSET_CREATION_CODE := att.control_level;
516 END IF;
517 IF substr(att.attribute_name,18) = 'COMMS_ACTIVATION_REQD_FLAG' THEN
518 A_COMMS_ACTIVATION_REQD_FLAG := att.control_level;
519 END IF;
520 IF substr(att.attribute_name,18) = 'ORDERABLE_ON_WEB_FLAG' THEN
521 A_ORDERABLE_ON_WEB_FLAG := att.control_level;
522 END IF;
523 IF substr(att.attribute_name,18) = 'BACK_ORDERABLE_FLAG' THEN
524 A_BACK_ORDERABLE_FLAG := att.control_level;
525 END IF;
526
527 IF substr(att.attribute_name,18) = 'WEB_STATUS' then
528 A_WEB_STATUS := att.control_level;
529 END IF;
530 IF substr(att.attribute_name,18) = 'INDIVISIBLE_FLAG' then
531 A_INDIVISIBLE_FLAG := att.control_level;
532 END IF;
533
534 IF substr(att.attribute_name,18) = 'DIMENSION_UOM_CODE' THEN
535 A_DIMENSION_UOM_CODE := att.control_level;
536 END IF;
537 IF substr(att.attribute_name,18) = 'UNIT_LENGTH' THEN
538 A_UNIT_LENGTH := att.control_level;
539 END IF;
540 IF substr(att.attribute_name,18) = 'UNIT_WIDTH' THEN
541 A_UNIT_WIDTH := att.control_level;
542 END IF;
543 IF substr(att.attribute_name,18) = 'UNIT_HEIGHT' THEN
544 A_UNIT_HEIGHT := att.control_level;
545 END IF;
546 IF substr(att.attribute_name,18) = 'BULK_PICKED_FLAG' THEN
547 A_BULK_PICKED_FLAG := att.control_level;
548 END IF;
549 IF substr(att.attribute_name,18) = 'LOT_STATUS_ENABLED' THEN
550 A_LOT_STATUS_ENABLED := att.control_level;
551 END IF;
552 IF substr(att.attribute_name,18) = 'DEFAULT_LOT_STATUS_ID' THEN
553 A_DEFAULT_LOT_STATUS_ID := att.control_level;
554 END IF;
555 IF substr(att.attribute_name,18) = 'SERIAL_STATUS_ENABLED' THEN
556 A_SERIAL_STATUS_ENABLED := att.control_level;
557 END IF;
558 IF substr(att.attribute_name,18) = 'DEFAULT_SERIAL_STATUS_ID' THEN
559 A_DEFAULT_SERIAL_STATUS_ID := att.control_level;
560 END IF;
561 IF substr(att.attribute_name,18) = 'LOT_SPLIT_ENABLED' THEN
562 A_LOT_SPLIT_ENABLED := att.control_level;
563 END IF;
564 IF substr(att.attribute_name,18) = 'LOT_MERGE_ENABLED' THEN
565 A_LOT_MERGE_ENABLED := att.control_level;
566 END IF;
567 IF substr(att.attribute_name,18) = 'INVENTORY_CARRY_PENALTY' THEN
568 A_INVENTORY_CARRY_PENALTY := att.control_level;
569 END IF;
570 IF substr(att.attribute_name,18) = 'OPERATION_SLACK_PENALTY' THEN
571 A_OPERATION_SLACK_PENALTY := att.control_level;
572 END IF;
573 IF substr(att.attribute_name,18) = 'FINANCING_ALLOWED_FLAG' THEN
574 A_FINANCING_ALLOWED_FLAG := att.control_level;
575 END IF;
576
577 IF substr(att.attribute_name,18) = 'EAM_ITEM_TYPE' THEN
578 A_EAM_ITEM_TYPE := att.control_level;
579 END IF;
580 IF substr(att.attribute_name,18) = 'EAM_ACTIVITY_TYPE_CODE' THEN
581 A_EAM_ACTIVITY_TYPE_CODE := att.control_level;
582 END IF;
583 IF substr(att.attribute_name,18) = 'EAM_ACTIVITY_CAUSE_CODE' THEN
584 A_EAM_ACTIVITY_CAUSE_CODE := att.control_level;
585 END IF;
586 IF substr(att.attribute_name,18) = 'EAM_ACT_NOTIFICATION_FLAG' THEN
587 A_EAM_ACT_NOTIFICATION_FLAG := att.control_level;
588 END IF;
589 IF substr(att.attribute_name,18) = 'EAM_ACT_SHUTDOWN_STATUS' THEN
590 A_EAM_ACT_SHUTDOWN_STATUS := att.control_level;
591 END IF;
592 IF substr(att.attribute_name,18) = 'DUAL_UOM_CONTROL' THEN
593 A_DUAL_UOM_CONTROL := att.control_level;
594 END IF;
595 IF substr(att.attribute_name,18) = 'SECONDARY_UOM_CODE' THEN
596 A_SECONDARY_UOM_CODE := att.control_level;
597 END IF;
598 IF substr(att.attribute_name,18) = 'DUAL_UOM_DEVIATION_HIGH' THEN
599 A_DUAL_UOM_DEVIATION_HIGH := att.control_level;
600 END IF;
601 IF substr(att.attribute_name,18) = 'DUAL_UOM_DEVIATION_LOW' THEN
602 A_DUAL_UOM_DEVIATION_LOW := att.control_level;
603 END IF;
604
605 /*
606 if substr(att.attribute_name,18) = 'SERVICE_ITEM_FLAG' then
607 A_SERVICE_ITEM_FLAG := att.control_level;
608 end if;
609 IF substr(att.attribute_name,18) = 'USAGE_ITEM_FLAG' THEN
610 A_USAGE_ITEM_FLAG := att.control_level;
611 END IF;
612 */
613 IF substr(att.attribute_name,18) = 'CONTRACT_ITEM_TYPE_CODE' THEN
614 A_CONTRACT_ITEM_TYPE_CODE := att.control_level;
615 END IF;
616 /* IF substr(att.attribute_name,18) = 'SUBSCRIPTION_DEPEND_FLAG' THEN
617 A_SUBSCRIPTION_DEPEND_FLAG := att.control_level;
618 END IF;
619 */
620 IF substr(att.attribute_name,18) = 'SERV_REQ_ENABLED_CODE' THEN
621 A_SERV_REQ_ENABLED_CODE := att.control_level;
622 END IF;
623 IF substr(att.attribute_name,18) = 'SERV_BILLING_ENABLED_FLAG' THEN
624 A_SERV_BILLING_ENABLED_FLAG := att.control_level;
625 END IF;
626 /* IF substr(att.attribute_name,18) = 'SERV_IMPORTANCE_LEVEL' THEN
627 A_SERV_IMPORTANCE_LEVEL := att.control_level;
628 END IF;
629 */ IF substr(att.attribute_name,18) = 'PLANNED_INV_POINT_FLAG' THEN
630 A_PLANNED_INV_POINT_FLAG := att.control_level;
631 END IF;
632 IF substr(att.attribute_name,18) = 'LOT_TRANSLATE_ENABLED' THEN
633 A_LOT_TRANSLATE_ENABLED := att.control_level;
634 END IF;
635 IF substr(att.attribute_name,18) = 'DEFAULT_SO_SOURCE_TYPE' THEN
636 A_DEFAULT_SO_SOURCE_TYPE := att.control_level;
637 END IF;
638 IF substr(att.attribute_name,18) = 'CREATE_SUPPLY_FLAG' THEN
639 A_CREATE_SUPPLY_FLAG := att.control_level;
640 END IF;
641 IF substr(att.attribute_name,18) = 'SUBSTITUTION_WINDOW_CODE' THEN
642 A_SUBSTITUTION_WINDOW_CODE := att.control_level;
643 END IF;
644 IF substr(att.attribute_name,18) = 'SUBSTITUTION_WINDOW_DAYS' THEN
645 A_SUBSTITUTION_WINDOW_DAYS := att.control_level;
646 END IF;
647
648 END LOOP;
649
650 --
651 -- Validate the records
652 --
653 FOR cr IN cc LOOP
654
655 status := 0;
656 trans_id := cr.transaction_id;
657 l_org_id := cr.ORGID;
658
659 begin /* MASTER_CHILD_1A */
660
661 select inventory_item_id into msicount
662 from mtl_system_items_B msi
663 where msi.inventory_item_id = cr.III
664 and msi.organization_id = cr.MORGID
665 and decode(A_BUYER_ID,X_TRUE,nvl(cr.BUYER_ID,-1),nvl(msi.BUYER_ID,-1))=nvl(msi.BUYER_ID,-1)
666 and decode(A_ACCOUNTING_RULE_ID,X_TRUE,nvl(cr.ACCOUNTING_RULE_ID,-1),nvl(msi.ACCOUNTING_RULE_ID,-1))=nvl(msi.ACCOUNTING_RULE_ID,-1)
667 and decode(A_INVOICING_RULE_ID,X_TRUE,nvl(cr.INVOICING_RULE_ID,-1),nvl(msi.INVOICING_RULE_ID,-1))=nvl(msi.INVOICING_RULE_ID,-1)
668 and decode(A_PURCHASING_ITEM_FLAG,X_TRUE,nvl(cr.PURCHASING_ITEM_FLAG,-1),nvl(msi.PURCHASING_ITEM_FLAG,-1))=nvl(msi.PURCHASING_ITEM_FLAG,-1)
669 and decode(A_SHIPPABLE_ITEM_FLAG,X_TRUE,nvl(cr.SHIPPABLE_ITEM_FLAG,-1),nvl(msi.SHIPPABLE_ITEM_FLAG,-1))=nvl(msi.SHIPPABLE_ITEM_FLAG,-1)
670 and decode(A_CUSTOMER_ORDER_FLAG,X_TRUE,nvl(cr.CUSTOMER_ORDER_FLAG,-1),nvl(msi.CUSTOMER_ORDER_FLAG,-1))=nvl(msi.CUSTOMER_ORDER_FLAG,-1)
671 and decode(A_INTERNAL_ORDER_FLAG,X_TRUE,nvl(cr.INTERNAL_ORDER_FLAG,-1),nvl(msi.INTERNAL_ORDER_FLAG,-1))=nvl(msi.INTERNAL_ORDER_FLAG,-1)
672 and decode(A_INVENTORY_ITEM_FLAG,X_TRUE,nvl(cr.INVENTORY_ITEM_FLAG,-1),nvl(msi.INVENTORY_ITEM_FLAG,-1))=nvl(msi.INVENTORY_ITEM_FLAG,-1);
673
674 exception
675
676 when NO_DATA_FOUND then
677 dumm_status := INVPUOPI.mtl_log_interface_err(
678 cr.ORGID,
679 user_id,
680 login_id,
681 prog_appid,
682 prog_id,
683 request_id,
684 cr.TRANSACTION_ID,
685 error_msg,
686 'MASTER_CHILD_1A',
687 'MTL_SYSTEM_ITEMS_INTERFACE',
688 'INV_IOI_MASTER_CHILD_1A',
689 err_text);
690 If dumm_status < 0 then
691 raise LOGGING_ERR ;
692 End If;
693 update mtl_system_items_interface msii
694 set process_flag = 3
695 where msii.transaction_id = cr.transaction_id;
696
697 end; /* MASTER_CHILD_1A */
698
699 begin /* MASTER_CHILD_1C */
700
701 select inventory_item_id into msicount
702 from mtl_system_items_B msi
703 where msi.inventory_item_id = cr.III
704 and msi.organization_id = cr.MORGID
705 and decode(A_PURCHASING_ENABLED_FLAG,X_TRUE,nvl(cr.PURCHASING_ENABLED_FLAG,-1),nvl(msi.PURCHASING_ENABLED_FLAG,-1))=nvl(msi.PURCHASING_ENABLED_FLAG,-1)
706 and decode(A_CUSTOMER_ORDER_ENABLED_FLAG,X_TRUE,nvl(cr.CUSTOMER_ORDER_ENABLED_FLAG,-1),nvl(msi.CUSTOMER_ORDER_ENABLED_FLAG,-1))=nvl(msi.CUSTOMER_ORDER_ENABLED_FLAG,-1)
707 and decode(A_INTERNAL_ORDER_ENABLED_FLAG,X_TRUE,nvl(cr.INTERNAL_ORDER_ENABLED_FLAG,-1),nvl(msi.INTERNAL_ORDER_ENABLED_FLAG,-1))=nvl(msi.INTERNAL_ORDER_ENABLED_FLAG,-1)
708 and decode(A_SO_TRANSACTIONS_FLAG,X_TRUE,nvl(cr.SO_TRANSACTIONS_FLAG,-1),nvl(msi.SO_TRANSACTIONS_FLAG,-1))=nvl(msi.SO_TRANSACTIONS_FLAG,-1)
709 and decode(A_MTL_TRANSACTIONS_ENABLED_F,X_TRUE,nvl(cr.MTL_TRANSACTIONS_ENABLED_FLAG,-1),nvl(msi.MTL_TRANSACTIONS_ENABLED_FLAG,-1))=nvl(msi.MTL_TRANSACTIONS_ENABLED_FLAG,-1);
710
711 exception
712 when NO_DATA_FOUND then
713 dumm_status := INVPUOPI.mtl_log_interface_err(
714 cr.ORGID,
715 user_id,
716 login_id,
717 prog_appid,
718 prog_id,
719 request_id,
720 cr.TRANSACTION_ID,
721 error_msg,
722 'MASTER_CHILD_1C',
723 'MTL_SYSTEM_ITEMS_INTERFACE',
724 'INV_IOI_MASTER_CHILD_1C',
725 err_text);
726 If dumm_status < 0 then
727 raise LOGGING_ERR ;
728 End If;
729 update mtl_system_items_interface msii
730 set process_flag = 3
731 where msii.transaction_id = cr.transaction_id;
732
733 end; /* MASTER_CHILD_1C */
734
735 begin /* MASTER_CHILD_1B */
736
737 select inventory_item_id
738 into msicount
739 from mtl_system_items_B msi
740 where msi.inventory_item_id = cr.III
741 and msi.organization_id = cr.MORGID
742 and decode(A_STOCK_ENABLED_FLAG,X_TRUE,nvl(cr.STOCK_ENABLED_FLAG,-1),nvl(msi.STOCK_ENABLED_FLAG,-1))=nvl(msi.STOCK_ENABLED_FLAG,-1)
743 and decode(A_BOM_ENABLED_FLAG,X_TRUE,nvl(cr.BOM_ENABLED_FLAG,-1),nvl(msi.BOM_ENABLED_FLAG,-1))=nvl(msi.BOM_ENABLED_FLAG,-1)
744 and decode(A_BUILD_IN_WIP_FLAG,X_TRUE,nvl(cr.BUILD_IN_WIP_FLAG,-1),nvl(msi.BUILD_IN_WIP_FLAG,-1))=nvl(msi.BUILD_IN_WIP_FLAG,-1)
745 and decode(A_REVISION_QTY_CONTROL_CODE,X_TRUE,nvl(cr.REVISION_QTY_CONTROL_CODE,-1),nvl(msi.REVISION_QTY_CONTROL_CODE,-1))=nvl(msi.REVISION_QTY_CONTROL_CODE,-1)
746 and decode(A_ITEM_CATALOG_GROUP_ID,X_TRUE,nvl(cr.ITEM_CATALOG_GROUP_ID,-1),nvl(msi.ITEM_CATALOG_GROUP_ID,-1))=nvl(msi.ITEM_CATALOG_GROUP_ID,-1)
747 and decode(A_CHECK_SHORTAGES_FLAG,X_TRUE,nvl(cr.CHECK_SHORTAGES_FLAG,-1),nvl(msi.CHECK_SHORTAGES_FLAG,-1))=nvl(msi.CHECK_SHORTAGES_FLAG,-1)
748
749 and decode(A_WEB_STATUS,
750 X_TRUE, nvl(cr.WEB_STATUS, -1),
751 nvl(msi.WEB_STATUS, -1)
752 ) = nvl(msi.WEB_STATUS, -1)
753
754 and decode(A_INDIVISIBLE_FLAG,
755 X_TRUE, nvl(cr.INDIVISIBLE_FLAG, -1),
756 nvl(msi.INDIVISIBLE_FLAG, -1)
757 ) = nvl(msi.INDIVISIBLE_FLAG, -1) ;
758
759 exception
760 when NO_DATA_FOUND then
761 dumm_status := INVPUOPI.mtl_log_interface_err(
762 cr.ORGID,
763 user_id,
764 login_id,
765 prog_appid,
766 prog_id,
767 request_id,
768 cr.TRANSACTION_ID,
769 error_msg,
770 'MASTER_CHILD_1B',
771 'MTL_SYSTEM_ITEMS_INTERFACE',
772 'INV_IOI_MASTER_CHILD_1B',
773 err_text);
774
775 If dumm_status < 0 then
776 raise LOGGING_ERR ;
777 End If;
778
779 update mtl_system_items_interface msii
780 set process_flag = 3
781 where msii.transaction_id = cr.transaction_id;
782
783 end; /* MASTER_CHILD_1B */
784
785 begin /* MASTER_CHILD_1D */
786
787 select inventory_item_id into msicount
788 from mtl_system_items_B msi
789 where msi.inventory_item_id = cr.III
790 and msi.organization_id = cr.MORGID
791 and decode(A_CATALOG_STATUS_FLAG,X_TRUE,nvl(cr.CATALOG_STATUS_FLAG,-1),nvl(msi.CATALOG_STATUS_FLAG,-1))=nvl(msi.CATALOG_STATUS_FLAG,-1)
792 and decode(A_RETURNABLE_FLAG,X_TRUE,nvl(cr.RETURNABLE_FLAG,-1),nvl(msi.RETURNABLE_FLAG,-1))=nvl(msi.RETURNABLE_FLAG,-1)
793 and decode(A_DEFAULT_SHIPPING_ORG,X_TRUE,nvl(cr.DEFAULT_SHIPPING_ORG,-1),nvl(msi.DEFAULT_SHIPPING_ORG,-1))=nvl(msi.DEFAULT_SHIPPING_ORG,-1)
794 and decode(A_COLLATERAL_FLAG,X_TRUE,nvl(cr.COLLATERAL_FLAG,-1),nvl(msi.COLLATERAL_FLAG,-1))=nvl(msi.COLLATERAL_FLAG,-1)
795 and decode(A_TAXABLE_FLAG,X_TRUE,nvl(cr.TAXABLE_FLAG,-1),nvl(msi.TAXABLE_FLAG,-1))=nvl(msi.TAXABLE_FLAG,-1)
796
797 and decode(A_PURCHASING_TAX_CODE,X_TRUE,nvl(cr.PURCHASING_TAX_CODE,-1),nvl(msi.PURCHASING_TAX_CODE,-1))=nvl(msi.PURCHASING_TAX_CODE,-1)
798
799 and decode(A_QTY_RCV_EXCEPTION_CODE,X_TRUE,nvl(cr.QTY_RCV_EXCEPTION_CODE,-1),nvl(msi.QTY_RCV_EXCEPTION_CODE,-1))=nvl(msi.QTY_RCV_EXCEPTION_CODE,-1)
800 and decode(A_ALLOW_ITEM_DESC_UPDATE_FLAG,X_TRUE,nvl(cr.ALLOW_ITEM_DESC_UPDATE_FLAG,-1),nvl(msi.ALLOW_ITEM_DESC_UPDATE_FLAG,-1))=nvl(msi.ALLOW_ITEM_DESC_UPDATE_FLAG,-1)
801 and decode(A_INSPECTION_REQUIRED_FLAG,X_TRUE,nvl(cr.INSPECTION_REQUIRED_FLAG,-1),nvl(msi.INSPECTION_REQUIRED_FLAG,-1))=nvl(msi.INSPECTION_REQUIRED_FLAG,-1)
802 and decode(A_RECEIPT_REQUIRED_FLAG,X_TRUE,nvl(cr.RECEIPT_REQUIRED_FLAG,-1),nvl(msi.RECEIPT_REQUIRED_FLAG,-1))=nvl(msi.RECEIPT_REQUIRED_FLAG,-1)
803 and decode(A_MARKET_PRICE,X_TRUE,nvl(cr.MARKET_PRICE,-1),nvl(msi.MARKET_PRICE,-1))=nvl(msi.MARKET_PRICE,-1);
804
805 exception
806 when NO_DATA_FOUND then
807 dumm_status := INVPUOPI.mtl_log_interface_err(
808 cr.ORGID,
809 user_id,
810 login_id,
811 prog_appid,
812 prog_id,
813 request_id,
814 cr.TRANSACTION_ID,
815 error_msg,
816 'MASTER_CHILD_1D',
817 'MTL_SYSTEM_ITEMS_INTERFACE',
818 'INV_IOI_MASTER_CHILD_1D',
819 err_text);
820 If dumm_status < 0 then
821 raise LOGGING_ERR ;
822 End If;
823 update mtl_system_items_interface msii
824 set process_flag = 3
825 where msii.transaction_id = cr.transaction_id;
826
827 end; /* MASTER_CHILD_1D */
828
829 begin /* MASTER_CHILD_1E */
830
831 select inventory_item_id into msicount
832 from mtl_system_items_B msi
833 where msi.inventory_item_id = cr.III
834 and msi.organization_id = cr.MORGID
835 and decode(A_HAZARD_CLASS_ID,X_TRUE,nvl(cr.HAZARD_CLASS_ID,-1),nvl(msi.HAZARD_CLASS_ID,-1))=nvl(msi.HAZARD_CLASS_ID,-1)
836 and decode(A_RFQ_REQUIRED_FLAG,X_TRUE,nvl(cr.RFQ_REQUIRED_FLAG,-1),nvl(msi.RFQ_REQUIRED_FLAG,-1))=nvl(msi.RFQ_REQUIRED_FLAG,-1)
837 and decode(A_QTY_RCV_TOLERANCE,X_TRUE,nvl(cr.QTY_RCV_TOLERANCE,-1),nvl(msi.QTY_RCV_TOLERANCE,-1))=nvl(msi.QTY_RCV_TOLERANCE,-1)
838 and decode(A_LIST_PRICE_PER_UNIT,X_TRUE,nvl(cr.LIST_PRICE_PER_UNIT,-1),nvl(msi.LIST_PRICE_PER_UNIT,-1))=nvl(msi.LIST_PRICE_PER_UNIT,-1)
839 and decode(A_UN_NUMBER_ID,X_TRUE,nvl(cr.UN_NUMBER_ID,-1),nvl(msi.UN_NUMBER_ID,-1))=nvl(msi.UN_NUMBER_ID,-1)
840 and decode(A_PRICE_TOLERANCE_PERCENT,X_TRUE,nvl(cr.PRICE_TOLERANCE_PERCENT,-1),nvl(msi.PRICE_TOLERANCE_PERCENT,-1))=nvl(msi.PRICE_TOLERANCE_PERCENT,-1)
841 and decode(A_ASSET_CATEGORY_ID,X_TRUE,nvl(cr.ASSET_CATEGORY_ID,-1),nvl(msi.ASSET_CATEGORY_ID,-1))=nvl(msi.ASSET_CATEGORY_ID,-1)
842 and decode(A_ROUNDING_FACTOR,X_TRUE,nvl(cr.ROUNDING_FACTOR,-1),nvl(msi.ROUNDING_FACTOR,-1))=nvl(msi.ROUNDING_FACTOR,-1)
843 and decode(A_UNIT_OF_ISSUE,X_TRUE,nvl(cr.UNIT_OF_ISSUE,-1),nvl(msi.UNIT_OF_ISSUE,-1))=nvl(msi.UNIT_OF_ISSUE,-1)
844 and decode(A_ENFORCE_SHIP_TO_LOCATION_C,X_TRUE,nvl(cr.ENFORCE_SHIP_TO_LOCATION_CODE,-1),nvl(msi.ENFORCE_SHIP_TO_LOCATION_CODE,-1))=nvl(msi.ENFORCE_SHIP_TO_LOCATION_CODE,-1);
845 exception
846 when NO_DATA_FOUND then
847 dumm_status := INVPUOPI.mtl_log_interface_err(
848 cr.ORGID,
849 user_id,
850 login_id,
851 prog_appid,
852 prog_id,
853 request_id,
854 cr.TRANSACTION_ID,
855 error_msg,
856 'MASTER_CHILD_1E',
857 'MTL_SYSTEM_ITEMS_INTERFACE',
858 'INV_IOI_MASTER_CHILD_1E',
859 err_text);
860 If dumm_status < 0 then
861 raise LOGGING_ERR ;
862 End If;
863 update mtl_system_items_interface msii
864 set process_flag = 3
865 where msii.transaction_id = cr.transaction_id;
866
867 end; /* MASTER_CHILD_1E */
868
869 begin /* MASTER_CHILD_1F */
870
871 select inventory_item_id into msicount
872 from mtl_system_items_B msi
873 where msi.inventory_item_id = cr.III
874 and msi.organization_id = cr.MORGID
875 and decode(A_ALLOW_SUBSTITUTE_RECEIPTS_F,X_TRUE,nvl(cr.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,-1),nvl(msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,-1))=nvl(msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,-1)
876 and decode(A_ALLOW_UNORDERED_RECEIPTS_F,X_TRUE,nvl(cr.ALLOW_UNORDERED_RECEIPTS_FLAG,-1),nvl(msi.ALLOW_UNORDERED_RECEIPTS_FLAG,-1))=nvl(msi.ALLOW_UNORDERED_RECEIPTS_FLAG,-1)
877 and decode(A_ALLOW_EXPRESS_DELIVERY_FLAG,X_TRUE,nvl(cr.ALLOW_EXPRESS_DELIVERY_FLAG,-1),nvl(msi.ALLOW_EXPRESS_DELIVERY_FLAG,-1))=nvl(msi.ALLOW_EXPRESS_DELIVERY_FLAG,-1)
878 and decode(A_DAYS_EARLY_RECEIPT_ALLOWED,X_TRUE,nvl(cr.DAYS_EARLY_RECEIPT_ALLOWED,-1),nvl(msi.DAYS_EARLY_RECEIPT_ALLOWED,-1))=nvl(msi.DAYS_EARLY_RECEIPT_ALLOWED,-1)
879 and decode(A_DAYS_LATE_RECEIPT_ALLOWED,X_TRUE,nvl(cr.DAYS_LATE_RECEIPT_ALLOWED,-1),nvl(msi.DAYS_LATE_RECEIPT_ALLOWED,-1))=nvl(msi.DAYS_LATE_RECEIPT_ALLOWED,-1);
880
881 exception
882 when NO_DATA_FOUND then
883 dumm_status := INVPUOPI.mtl_log_interface_err(
884 cr.ORGID,
885 user_id,
886 login_id,
887 prog_appid,
888 prog_id,
889 request_id,
890 cr.TRANSACTION_ID,
891 error_msg,
892 'MASTER_CHILD_1F',
893 'MTL_SYSTEM_ITEMS_INTERFACE',
894 'INV_IOI_MASTER_CHILD_1F',
895 err_text);
896 If dumm_status < 0 then
897 raise LOGGING_ERR ;
898 End If;
899 update mtl_system_items_interface msii
900 set process_flag = 3
901 where msii.transaction_id = cr.transaction_id;
902
903 end; /* MASTER_CHILD_1F */
904
905 /*Bug 1529259
906 In the following query changed the Table Name from mtl_system_item_b
907 to mtl_system_items_vl
908 This is to get the correct description
909 */
910 --5446889 ADDED check for long_description
911 begin /* MASTER_CHILD_1G */
912
913 select inventory_item_id into msicount
914 from mtl_system_items_VL msi
915 where msi.inventory_item_id = cr.III
916 and msi.organization_id = cr.MORGID
917 and decode(A_RECEIPT_DAYS_EXCEPTION_CODE,X_TRUE,nvl(cr.RECEIPT_DAYS_EXCEPTION_CODE,-1),nvl(msi.RECEIPT_DAYS_EXCEPTION_CODE,-1))=nvl(msi.RECEIPT_DAYS_EXCEPTION_CODE,-1)
918 and decode(A_RECEIVING_ROUTING_ID,X_TRUE,nvl(cr.RECEIVING_ROUTING_ID,-1),nvl(msi.RECEIVING_ROUTING_ID,-1))=nvl(msi.RECEIVING_ROUTING_ID,-1)
919 and decode(A_INVOICE_CLOSE_TOLERANCE,X_TRUE,nvl(cr.INVOICE_CLOSE_TOLERANCE,-1),nvl(msi.INVOICE_CLOSE_TOLERANCE,-1))=nvl(msi.INVOICE_CLOSE_TOLERANCE,-1)
920 and decode(A_RECEIVE_CLOSE_TOLERANCE,X_TRUE,nvl(cr.RECEIVE_CLOSE_TOLERANCE,-1),nvl(msi.RECEIVE_CLOSE_TOLERANCE,-1))=nvl(msi.RECEIVE_CLOSE_TOLERANCE,-1)
921 and decode(A_DESCRIPTION,X_TRUE,nvl(cr.DESCRIPTION,-1),nvl(msi.DESCRIPTION,-1))=nvl(msi.DESCRIPTION,-1)
922 and decode(A_LONG_DESCRIPTION,X_TRUE,nvl(cr.LONG_DESCRIPTION,-1),nvl(msi.LONG_DESCRIPTION,-1))=nvl(msi.LONG_DESCRIPTION,-1)
923 and decode(A_AUTO_LOT_ALPHA_PREFIX,X_TRUE,nvl(cr.AUTO_LOT_ALPHA_PREFIX,-1),nvl(msi.AUTO_LOT_ALPHA_PREFIX,-1))=nvl(msi.AUTO_LOT_ALPHA_PREFIX,-1);
924
925 exception
926 when NO_DATA_FOUND then
927 dumm_status := INVPUOPI.mtl_log_interface_err(
928 cr.ORGID,
929 user_id,
930 login_id,
931 prog_appid,
932 prog_id,
933 request_id,
934 cr.TRANSACTION_ID,
935 error_msg,
936 'MASTER_CHILD_1G',
937 'MTL_SYSTEM_ITEMS_INTERFACE',
938 'INV_IOI_MASTER_CHILD_1G',
939 err_text);
940 If dumm_status < 0 then
941 raise LOGGING_ERR ;
942 End If;
943 update mtl_system_items_interface msii
944 set process_flag = 3
945 where msii.transaction_id = cr.transaction_id;
946
947 end; /* MASTER_CHILD_1G */
948
949
950 BEGIN /* MASTER_CHILD_1HA */
951
952 SELECT inventory_item_id
953 INTO msicount
954 FROM mtl_system_items_b msi
955 WHERE msi.inventory_item_id = cr.III
956 AND msi.organization_id = cr.MORGID
957
958 AND DECODE( A_OVERCOMPLETION_TOLERANCE_TYP,
959 x_true, NVL(cr.OVERCOMPLETION_TOLERANCE_TYPE, -1),
960 NVL(msi.OVERCOMPLETION_TOLERANCE_TYPE, -1)
961 ) = NVL(msi.OVERCOMPLETION_TOLERANCE_TYPE, -1)
962
963 AND DECODE( A_OVERCOMPLETION_TOLERANCE_VAL,
964 x_true, NVL(cr.OVERCOMPLETION_TOLERANCE_VALUE, -1),
965 NVL(msi.OVERCOMPLETION_TOLERANCE_VALUE, -1)
966 ) = NVL(msi.OVERCOMPLETION_TOLERANCE_VALUE, -1)
967
968 AND DECODE( A_OVER_SHIPMENT_TOLERANCE,
969 x_true, NVL(cr.OVER_SHIPMENT_TOLERANCE, -1),
970 NVL(msi.OVER_SHIPMENT_TOLERANCE, -1)
971 ) = NVL(msi.OVER_SHIPMENT_TOLERANCE, -1)
972
973 AND DECODE( A_UNDER_SHIPMENT_TOLERANCE,
974 x_true, NVL(cr.UNDER_SHIPMENT_TOLERANCE, -1),
975 NVL(msi.UNDER_SHIPMENT_TOLERANCE, -1)
976 ) = NVL(msi.UNDER_SHIPMENT_TOLERANCE, -1)
977 ;
978 EXCEPTION
979
980 when NO_DATA_FOUND then
981 dumm_status := INVPUOPI.mtl_log_interface_err (
982 cr.ORGID,
983 user_id,
984 login_id,
985 prog_appid,
986 prog_id,
987 request_id,
988 cr.TRANSACTION_ID,
989 error_msg,
990 'MASTER_CHILD_1HA',
991 'MTL_SYSTEM_ITEMS_INTERFACE',
992 'INV_IOI_MASTER_CHILD_1HA',
993 err_text );
994
995 IF dumm_status < 0 THEN
996 raise LOGGING_ERR ;
997 END IF;
998
999 update mtl_system_items_interface msii
1000 set process_flag = 3
1001 where msii.transaction_id = cr.transaction_id;
1002
1003 END; /* MASTER_CHILD_1HA */
1004
1005 BEGIN /* MASTER_CHILD_1HB */
1006
1007 SELECT inventory_item_id
1008 INTO msicount
1009 FROM mtl_system_items_b msi
1010 WHERE msi.inventory_item_id = cr.III
1011 AND msi.organization_id = cr.MORGID
1012
1013 AND DECODE( A_OVER_RETURN_TOLERANCE,
1014 x_true, NVL(cr.OVER_RETURN_TOLERANCE, -1),
1015 NVL(msi.OVER_RETURN_TOLERANCE, -1)
1016 ) = NVL(msi.OVER_RETURN_TOLERANCE, -1)
1017
1018 AND DECODE( A_UNDER_RETURN_TOLERANCE,
1019 x_true, NVL(cr.UNDER_RETURN_TOLERANCE, -1),
1020 NVL(msi.UNDER_RETURN_TOLERANCE, -1)
1021 ) = NVL(msi.UNDER_RETURN_TOLERANCE, -1)
1022
1023 AND DECODE( A_EQUIPMENT_TYPE,
1024 x_true, NVL(cr.EQUIPMENT_TYPE, -1),
1025 NVL(msi.EQUIPMENT_TYPE, -1)
1026 ) = NVL(msi.EQUIPMENT_TYPE, -1)
1027
1028 AND DECODE( A_RECOVERED_PART_DISP_CODE,
1029 x_true, NVL(cr.RECOVERED_PART_DISP_CODE, -1),
1030 NVL(msi.RECOVERED_PART_DISP_CODE, -1)
1031 ) = NVL(msi.RECOVERED_PART_DISP_CODE, -1)
1032
1033 AND DECODE( A_DEFECT_TRACKING_ON_FLAG,
1034 x_true, NVL(cr.DEFECT_TRACKING_ON_FLAG, -1),
1035 NVL(msi.DEFECT_TRACKING_ON_FLAG, -1)
1036 ) = NVL(msi.DEFECT_TRACKING_ON_FLAG, -1)
1037 ;
1038 EXCEPTION
1039
1040 when NO_DATA_FOUND then
1041 dumm_status := INVPUOPI.mtl_log_interface_err (
1042 cr.ORGID,
1043 user_id,
1044 login_id,
1045 prog_appid,
1046 prog_id,
1047 request_id,
1048 cr.TRANSACTION_ID,
1049 error_msg,
1050 'MASTER_CHILD_1HB',
1051 'MTL_SYSTEM_ITEMS_INTERFACE',
1052 'INV_IOI_MASTER_CHILD_1HB',
1053 err_text );
1054
1055 IF dumm_status < 0 THEN
1056 raise LOGGING_ERR ;
1057 END IF;
1058
1059 update mtl_system_items_interface msii
1060 set process_flag = 3
1061 where msii.transaction_id = cr.transaction_id;
1062
1063 END; /* MASTER_CHILD_1HB */
1064
1065 BEGIN /* MASTER_CHILD_1HC */
1066
1067 SELECT inventory_item_id
1068 INTO msicount
1069 FROM mtl_system_items_b msi
1070 WHERE msi.inventory_item_id = cr.III
1071 AND msi.organization_id = cr.MORGID
1072
1073 AND DECODE( A_EVENT_FLAG,
1074 x_true, NVL(cr.EVENT_FLAG, -1),
1075 NVL(msi.EVENT_FLAG, -1)
1076 ) = NVL(msi.EVENT_FLAG, -1)
1077
1078 AND DECODE( A_ELECTRONIC_FLAG,
1079 x_true, NVL(cr.ELECTRONIC_FLAG, -1),
1080 NVL(msi.ELECTRONIC_FLAG, -1)
1081 ) = NVL(msi.ELECTRONIC_FLAG, -1)
1082
1083 AND DECODE( A_DOWNLOADABLE_FLAG,
1084 x_true, NVL(cr.DOWNLOADABLE_FLAG, -1),
1085 NVL(msi.DOWNLOADABLE_FLAG, -1)
1086 ) = NVL(msi.DOWNLOADABLE_FLAG, -1)
1087
1088 AND DECODE( A_VOL_DISCOUNT_EXEMPT_FLAG,
1089 x_true, NVL(cr.VOL_DISCOUNT_EXEMPT_FLAG, -1),
1090 NVL(msi.VOL_DISCOUNT_EXEMPT_FLAG, -1)
1091 ) = NVL(msi.VOL_DISCOUNT_EXEMPT_FLAG, -1)
1092
1093 AND DECODE( A_COUPON_EXEMPT_FLAG,
1094 x_true, NVL(cr.COUPON_EXEMPT_FLAG, -1),
1095 NVL(msi.COUPON_EXEMPT_FLAG, -1)
1096 ) = NVL(msi.COUPON_EXEMPT_FLAG, -1)
1097 ;
1098 EXCEPTION
1099
1100 when NO_DATA_FOUND then
1101 dumm_status := INVPUOPI.mtl_log_interface_err (
1102 cr.ORGID,
1103 user_id,
1104 login_id,
1105 prog_appid,
1106 prog_id,
1107 request_id,
1108 cr.TRANSACTION_ID,
1109 error_msg,
1110 'MASTER_CHILD_1HC',
1111 'MTL_SYSTEM_ITEMS_INTERFACE',
1112 'INV_IOI_MASTER_CHILD_1HC',
1113 err_text );
1114
1115 IF dumm_status < 0 THEN
1116 raise LOGGING_ERR ;
1117 END IF;
1118
1119 update mtl_system_items_interface msii
1120 set process_flag = 3
1121 where msii.transaction_id = cr.transaction_id;
1122
1123 END; /* MASTER_CHILD_1HC */
1124
1125 BEGIN /* MASTER_CHILD_1HD */
1126
1127 SELECT inventory_item_id
1128 INTO msicount
1129 FROM mtl_system_items_b msi
1130 WHERE msi.inventory_item_id = cr.III
1131 AND msi.organization_id = cr.MORGID
1132
1133 AND DECODE( A_COMMS_NL_TRACKABLE_FLAG,
1134 x_true, NVL(cr.COMMS_NL_TRACKABLE_FLAG, -1),
1135 NVL(msi.COMMS_NL_TRACKABLE_FLAG, -1)
1136 ) = NVL(msi.COMMS_NL_TRACKABLE_FLAG, -1)
1137
1138 AND DECODE( A_ASSET_CREATION_CODE,
1139 x_true, NVL(cr.ASSET_CREATION_CODE, -1),
1140 NVL(msi.ASSET_CREATION_CODE, -1)
1141 ) = NVL(msi.ASSET_CREATION_CODE, -1)
1142
1143 AND DECODE( A_COMMS_ACTIVATION_REQD_FLAG,
1144 x_true, NVL(cr.COMMS_ACTIVATION_REQD_FLAG, -1),
1145 NVL(msi.COMMS_ACTIVATION_REQD_FLAG, -1)
1146 ) = NVL(msi.COMMS_ACTIVATION_REQD_FLAG, -1)
1147
1148 AND DECODE( A_ORDERABLE_ON_WEB_FLAG,
1149 x_true, NVL(cr.ORDERABLE_ON_WEB_FLAG, -1),
1150 NVL(msi.ORDERABLE_ON_WEB_FLAG, -1)
1151 ) = NVL(msi.ORDERABLE_ON_WEB_FLAG, -1)
1152
1153 AND DECODE( A_BACK_ORDERABLE_FLAG,
1154 x_true, NVL(cr.BACK_ORDERABLE_FLAG, -1),
1155 NVL(msi.BACK_ORDERABLE_FLAG, -1)
1156 ) = NVL(msi.BACK_ORDERABLE_FLAG, -1)
1157 ;
1158
1159 EXCEPTION
1160
1161 when NO_DATA_FOUND then
1162 dumm_status := INVPUOPI.mtl_log_interface_err (
1163 cr.ORGID,
1164 user_id,
1165 login_id,
1166 prog_appid,
1167 prog_id,
1168 request_id,
1169 cr.TRANSACTION_ID,
1170 error_msg,
1171 'MASTER_CHILD_1HD',
1172 'MTL_SYSTEM_ITEMS_INTERFACE',
1173 'INV_IOI_MASTER_CHILD_1HD',
1174 err_text );
1175
1176 IF dumm_status < 0 THEN
1177 raise LOGGING_ERR ;
1178 END IF;
1179
1180 update mtl_system_items_interface msii
1181 set process_flag = 3
1182 where msii.transaction_id = cr.transaction_id;
1183
1184 END; /* MASTER_CHILD_1HD */
1185
1186 BEGIN /* MASTER_CHILD_1IA */
1187
1188 SELECT inventory_item_id
1189 INTO msicount
1190 FROM mtl_system_items_b msi
1191 WHERE msi.inventory_item_id = cr.III
1192 AND msi.organization_id = cr.MORGID
1193
1194 AND DECODE( A_DIMENSION_UOM_CODE,
1195 x_true, NVL(cr.DIMENSION_UOM_CODE, -1),
1196 NVL(msi.DIMENSION_UOM_CODE, -1)
1197 ) = NVL(msi.DIMENSION_UOM_CODE, -1)
1198
1199 AND DECODE( A_UNIT_LENGTH,
1200 x_true, NVL(cr.UNIT_LENGTH, -1),
1201 NVL(msi.UNIT_LENGTH, -1)
1202 ) = NVL(msi.UNIT_LENGTH, -1)
1203
1204 AND DECODE( A_UNIT_WIDTH,
1205 x_true, NVL(cr.UNIT_WIDTH, -1),
1206 NVL(msi.UNIT_WIDTH, -1)
1207 ) = NVL(msi.UNIT_WIDTH, -1)
1208
1209 AND DECODE( A_UNIT_HEIGHT,
1210 x_true, NVL(cr.UNIT_HEIGHT, -1),
1211 NVL(msi.UNIT_HEIGHT, -1)
1212 ) = NVL(msi.UNIT_HEIGHT, -1)
1213
1214 AND DECODE( A_BULK_PICKED_FLAG,
1215 x_true, NVL(cr.BULK_PICKED_FLAG, -1),
1216 NVL(msi.BULK_PICKED_FLAG, -1)
1217 ) = NVL(msi.BULK_PICKED_FLAG, -1)
1218
1219 AND DECODE( A_LOT_STATUS_ENABLED,
1220 x_true, NVL(cr.LOT_STATUS_ENABLED, -1),
1221 NVL(msi.LOT_STATUS_ENABLED, -1)
1222 ) = NVL(msi.LOT_STATUS_ENABLED, -1)
1223
1224 AND DECODE( A_DEFAULT_LOT_STATUS_ID,
1225 x_true, NVL(cr.DEFAULT_LOT_STATUS_ID, -1),
1226 NVL(msi.DEFAULT_LOT_STATUS_ID, -1)
1227 ) = NVL(msi.DEFAULT_LOT_STATUS_ID, -1)
1228 ;
1229 EXCEPTION
1230
1231 when NO_DATA_FOUND then
1232 dumm_status := INVPUOPI.mtl_log_interface_err (
1233 cr.ORGID,
1234 user_id,
1235 login_id,
1236 prog_appid,
1237 prog_id,
1238 request_id,
1239 cr.TRANSACTION_ID,
1240 error_msg,
1241 'MASTER_CHILD_1IA',
1242 'MTL_SYSTEM_ITEMS_INTERFACE',
1243 'INV_IOI_MASTER_CHILD_1IA',
1244 err_text );
1245
1246 IF dumm_status < 0 THEN
1247 raise LOGGING_ERR ;
1248 END IF;
1249
1250 update mtl_system_items_interface msii
1251 set process_flag = 3
1252 where msii.transaction_id = cr.transaction_id;
1253
1254 END; /* MASTER_CHILD_1IA */
1255
1256 BEGIN /* MASTER_CHILD_1IB */
1257
1258 SELECT inventory_item_id
1259 INTO msicount
1260 FROM mtl_system_items_b msi
1261 WHERE msi.inventory_item_id = cr.III
1262 AND msi.organization_id = cr.MORGID
1263
1264 AND DECODE( A_SERIAL_STATUS_ENABLED,
1265 x_true, NVL(cr.SERIAL_STATUS_ENABLED, -1),
1266 NVL(msi.SERIAL_STATUS_ENABLED, -1)
1267 ) = NVL(msi.SERIAL_STATUS_ENABLED, -1)
1268
1269 AND DECODE( A_DEFAULT_SERIAL_STATUS_ID,
1270 x_true, NVL(cr.DEFAULT_SERIAL_STATUS_ID, -1),
1271 NVL(msi.DEFAULT_SERIAL_STATUS_ID, -1)
1272 ) = NVL(msi.DEFAULT_SERIAL_STATUS_ID, -1)
1273
1274 AND DECODE( A_LOT_SPLIT_ENABLED,
1275 x_true, NVL(cr.LOT_SPLIT_ENABLED, -1),
1276 NVL(msi.LOT_SPLIT_ENABLED, -1)
1277 ) = NVL(msi.LOT_SPLIT_ENABLED, -1)
1278
1279 AND DECODE( A_LOT_MERGE_ENABLED,
1280 x_true, NVL(cr.LOT_MERGE_ENABLED, -1),
1281 NVL(msi.LOT_MERGE_ENABLED, -1)
1282 ) = NVL(msi.LOT_MERGE_ENABLED, -1)
1283 ;
1284
1285 EXCEPTION
1286
1287 when NO_DATA_FOUND then
1288 dumm_status := INVPUOPI.mtl_log_interface_err (
1289 cr.ORGID,
1290 user_id,
1291 login_id,
1292 prog_appid,
1293 prog_id,
1294 request_id,
1295 cr.TRANSACTION_ID,
1296 error_msg,
1297 'MASTER_CHILD_1IB',
1298 'MTL_SYSTEM_ITEMS_INTERFACE',
1299 'INV_IOI_MASTER_CHILD_1IB',
1300 err_text );
1301
1302 IF dumm_status < 0 THEN
1303 raise LOGGING_ERR ;
1304 END IF;
1305
1306 update mtl_system_items_interface msii
1307 set process_flag = 3
1308 where msii.transaction_id = cr.transaction_id;
1309
1310 END; /* MASTER_CHILD_1IB */
1311
1312 BEGIN /* MASTER_CHILD_1IC */
1313
1314 SELECT inventory_item_id
1315 INTO msicount
1316 FROM mtl_system_items_b msi
1317 WHERE msi.inventory_item_id = cr.III
1318 AND msi.organization_id = cr.MORGID
1319
1320 AND DECODE( A_INVENTORY_CARRY_PENALTY,
1321 x_true, NVL(cr.INVENTORY_CARRY_PENALTY, -1),
1322 NVL(msi.INVENTORY_CARRY_PENALTY, -1)
1323 ) = NVL(msi.INVENTORY_CARRY_PENALTY, -1)
1324
1325 AND DECODE( A_OPERATION_SLACK_PENALTY,
1326 x_true, NVL(cr.OPERATION_SLACK_PENALTY, -1),
1327 NVL(msi.OPERATION_SLACK_PENALTY, -1)
1328 ) = NVL(msi.OPERATION_SLACK_PENALTY, -1)
1329
1330 AND DECODE( A_FINANCING_ALLOWED_FLAG,
1331 x_true, NVL(cr.FINANCING_ALLOWED_FLAG, -1),
1332 NVL(msi.FINANCING_ALLOWED_FLAG, -1)
1333 ) = NVL(msi.FINANCING_ALLOWED_FLAG, -1)
1334 ;
1335 EXCEPTION
1336
1337 when NO_DATA_FOUND then
1338 dumm_status := INVPUOPI.mtl_log_interface_err (
1339 cr.ORGID,
1340 user_id,
1341 login_id,
1342 prog_appid,
1343 prog_id,
1344 request_id,
1345 cr.TRANSACTION_ID,
1346 error_msg,
1347 'MASTER_CHILD_1IC',
1348 'MTL_SYSTEM_ITEMS_INTERFACE',
1349 'INV_IOI_MASTER_CHILD_1IC',
1350 err_text );
1351
1352 IF dumm_status < 0 THEN
1353 raise LOGGING_ERR ;
1354 END IF;
1355
1356 update mtl_system_items_interface msii
1357 set process_flag = 3
1358 where msii.transaction_id = cr.transaction_id;
1359
1360 END; /* MASTER_CHILD_1Ic */
1361
1362
1363 BEGIN /* MASTER_CHILD_1J */
1364
1365 SELECT inventory_item_id
1366 INTO msicount
1367 FROM mtl_system_items_b msi
1368 WHERE msi.inventory_item_id = cr.III
1369 AND msi.organization_id = cr.MORGID
1370
1371 AND DECODE( A_EAM_ITEM_TYPE,
1372 x_true, NVL(cr.EAM_ITEM_TYPE, -1),
1373 NVL(msi.EAM_ITEM_TYPE, -1)
1374 ) = NVL(msi.EAM_ITEM_TYPE, -1)
1375
1376 AND DECODE( A_EAM_ACTIVITY_TYPE_CODE,
1377 x_true, NVL(cr.EAM_ACTIVITY_TYPE_CODE, -1),
1378 NVL(msi.EAM_ACTIVITY_TYPE_CODE, -1)
1379 ) = NVL(msi.EAM_ACTIVITY_TYPE_CODE, -1)
1380
1381 AND DECODE( A_EAM_ACTIVITY_CAUSE_CODE,
1382 x_true, NVL(cr.EAM_ACTIVITY_CAUSE_CODE, -1),
1383 NVL(msi.EAM_ACTIVITY_CAUSE_CODE, -1)
1384 ) = NVL(msi.EAM_ACTIVITY_CAUSE_CODE, -1)
1385
1386 AND DECODE( A_EAM_ACT_NOTIFICATION_FLAG,
1387 x_true, NVL(cr.EAM_ACT_NOTIFICATION_FLAG, -1),
1388 NVL(msi.EAM_ACT_NOTIFICATION_FLAG, -1)
1389 ) = NVL(msi.EAM_ACT_NOTIFICATION_FLAG, -1)
1390
1391 AND DECODE( A_EAM_ACT_SHUTDOWN_STATUS,
1392 x_true, NVL(cr.EAM_ACT_SHUTDOWN_STATUS, -1),
1393 NVL(msi.EAM_ACT_SHUTDOWN_STATUS, -1)
1394 ) = NVL(msi.EAM_ACT_SHUTDOWN_STATUS, -1)
1395
1396 AND DECODE( A_DUAL_UOM_CONTROL,
1397 x_true, NVL(cr.DUAL_UOM_CONTROL, -1),
1398 NVL(msi.DUAL_UOM_CONTROL, -1)
1399 ) = NVL(msi.DUAL_UOM_CONTROL, -1)
1400
1401 AND DECODE( A_SECONDARY_UOM_CODE,
1402 x_true, NVL(cr.SECONDARY_UOM_CODE, -1),
1403 NVL(msi.SECONDARY_UOM_CODE, -1)
1404 ) = NVL(msi.SECONDARY_UOM_CODE, -1)
1405
1406 AND DECODE( A_DUAL_UOM_DEVIATION_HIGH,
1407 x_true, NVL(cr.DUAL_UOM_DEVIATION_HIGH, -1),
1408 NVL(msi.DUAL_UOM_DEVIATION_HIGH, -1)
1409 ) = NVL(msi.DUAL_UOM_DEVIATION_HIGH, -1)
1410
1411 AND DECODE( A_DUAL_UOM_DEVIATION_LOW,
1412 x_true, NVL(cr.DUAL_UOM_DEVIATION_LOW, -1),
1413 NVL(msi.DUAL_UOM_DEVIATION_LOW, -1)
1414 ) = NVL(msi.DUAL_UOM_DEVIATION_LOW, -1)
1415
1416 /*
1417 and decode(A_SERVICE_ITEM_FLAG, X_TRUE, nvl(cr.SERVICE_ITEM_FLAG,-1), nvl(msi.SERVICE_ITEM_FLAG,-1)) = nvl(msi.SERVICE_ITEM_FLAG,-1)
1418
1419 AND DECODE( A_USAGE_ITEM_FLAG,
1420 x_true, NVL(cr.USAGE_ITEM_FLAG, -1),
1421 NVL(msi.USAGE_ITEM_FLAG, -1)
1422 ) = NVL(msi.USAGE_ITEM_FLAG, -1)
1423 */
1424 AND DECODE( A_CONTRACT_ITEM_TYPE_CODE,
1425 x_true, NVL(cr.CONTRACT_ITEM_TYPE_CODE, -1),
1426 NVL(msi.CONTRACT_ITEM_TYPE_CODE, -1)
1427 ) = NVL(msi.CONTRACT_ITEM_TYPE_CODE, -1)
1428
1429 /* AND DECODE( A_SUBSCRIPTION_DEPEND_FLAG,
1430 x_true, NVL(cr.SUBSCRIPTION_DEPEND_FLAG, -1),
1431 NVL(msi.SUBSCRIPTION_DEPEND_FLAG, -1)
1432 ) = NVL(msi.SUBSCRIPTION_DEPEND_FLAG, -1)
1433 */ ;
1434
1435 EXCEPTION
1436
1437 when NO_DATA_FOUND then
1438 dumm_status := INVPUOPI.mtl_log_interface_err (
1439 cr.ORGID,
1440 user_id,
1441 login_id,
1442 prog_appid,
1443 prog_id,
1444 request_id,
1445 cr.TRANSACTION_ID,
1446 error_msg,
1447 'MASTER_CHILD_1J',
1448 'MTL_SYSTEM_ITEMS_INTERFACE',
1449 'INV_IOI_MASTER_CHILD_1J',
1450 err_text );
1451
1452 IF dumm_status < 0 THEN
1453 raise LOGGING_ERR;
1454 END IF;
1455
1456 update mtl_system_items_interface msii
1457 set process_flag = 3
1458 where msii.transaction_id = cr.transaction_id;
1459
1460 END; /* MASTER_CHILD_1J */
1461
1462 BEGIN /* MASTER_CHILD_1K */
1463
1464 SELECT inventory_item_id
1465 INTO msicount
1466 FROM mtl_system_items_b msi
1467 WHERE msi.inventory_item_id = cr.III
1468 AND msi.organization_id = cr.MORGID
1469
1470 AND DECODE( A_SERV_REQ_ENABLED_CODE,
1471 x_true, NVL(cr.SERV_REQ_ENABLED_CODE, -1),
1472 NVL(msi.SERV_REQ_ENABLED_CODE, -1)
1473 ) = NVL(msi.SERV_REQ_ENABLED_CODE, -1)
1474
1475 AND DECODE( A_SERV_BILLING_ENABLED_FLAG,
1476 x_true, NVL(cr.SERV_BILLING_ENABLED_FLAG, -1),
1477 NVL(msi.SERV_BILLING_ENABLED_FLAG, -1)
1478 ) = NVL(msi.SERV_BILLING_ENABLED_FLAG, -1)
1479
1480 /* AND DECODE( A_SERV_IMPORTANCE_LEVEL,
1481 x_true, NVL(cr.SERV_IMPORTANCE_LEVEL, -1),
1482 NVL(msi.SERV_IMPORTANCE_LEVEL, -1)
1483 ) = NVL(msi.SERV_IMPORTANCE_LEVEL, -1)
1484 */
1485 AND DECODE( A_PLANNED_INV_POINT_FLAG,
1486 x_true, NVL(cr.PLANNED_INV_POINT_FLAG, -1),
1487 NVL(msi.PLANNED_INV_POINT_FLAG, -1)
1488 ) = NVL(msi.PLANNED_INV_POINT_FLAG, -1)
1489
1490 AND DECODE( A_LOT_TRANSLATE_ENABLED,
1491 x_true, NVL(cr.LOT_TRANSLATE_ENABLED, -1),
1492 NVL(msi.LOT_TRANSLATE_ENABLED, -1)
1493 ) = NVL(msi.LOT_TRANSLATE_ENABLED, -1)
1494
1495 AND DECODE( A_DEFAULT_SO_SOURCE_TYPE,
1496 x_true, NVL(cr.DEFAULT_SO_SOURCE_TYPE, -1),
1497 NVL(msi.DEFAULT_SO_SOURCE_TYPE, -1)
1498 ) = NVL(msi.DEFAULT_SO_SOURCE_TYPE, -1)
1499
1500 AND DECODE( A_CREATE_SUPPLY_FLAG,
1501 x_true, NVL(cr.CREATE_SUPPLY_FLAG, -1),
1502 NVL(msi.CREATE_SUPPLY_FLAG, -1)
1503 ) = NVL(msi.CREATE_SUPPLY_FLAG, -1)
1504
1505 AND DECODE( A_SUBSTITUTION_WINDOW_CODE,
1506 x_true, NVL(cr.SUBSTITUTION_WINDOW_CODE, -1),
1507 NVL(msi.SUBSTITUTION_WINDOW_CODE, -1)
1508 ) = NVL(msi.SUBSTITUTION_WINDOW_CODE, -1)
1509
1510 AND DECODE( A_SUBSTITUTION_WINDOW_DAYS,
1511 x_true, NVL(cr.SUBSTITUTION_WINDOW_DAYS, -1),
1512 NVL(msi.SUBSTITUTION_WINDOW_DAYS, -1)
1513 ) = NVL(msi.SUBSTITUTION_WINDOW_DAYS, -1)
1514 ;
1515
1516 EXCEPTION
1517
1518 when NO_DATA_FOUND then
1519 dumm_status := INVPUOPI.mtl_log_interface_err (
1520 cr.ORGID,
1521 user_id,
1522 login_id,
1523 prog_appid,
1524 prog_id,
1525 request_id,
1526 cr.TRANSACTION_ID,
1527 error_msg,
1528 'MASTER_CHILD_1K',
1529 'MTL_SYSTEM_ITEMS_INTERFACE',
1530 'INV_IOI_MASTER_CHILD_1K',
1531 err_text );
1532
1533 IF dumm_status < 0 THEN
1534 raise LOGGING_ERR;
1535 END IF;
1536
1537 update mtl_system_items_interface msii
1538 set process_flag = 3
1539 where msii.transaction_id = cr.transaction_id;
1540
1541 END; /* MASTER_CHILD_1K */
1542
1543 END LOOP;
1544
1545 RETURN (0);
1546
1547 EXCEPTION
1548
1549 when LOGGING_ERR then
1550 return(dumm_status);
1551
1552 when VALIDATE_ERR then
1553 dumm_status := INVPUOPI.mtl_log_interface_err(
1554 l_org_id,
1555 user_id,
1556 login_id,
1557 prog_appid,
1558 prog_id,
1559 request_id,
1560 trans_id,
1561 err_text,
1562 'MASTER_CHILD_1',
1563 'MTL_SYSTEM_ITEMS_INTERFACE',
1564 'BOM_OP_VALIDATION_ERR',
1565 err_text);
1566 return(status);
1567
1568 when OTHERS then
1569 err_text := substr('INVPVALI.validate_item_org1' || SQLERRM , 1 , 240);
1570 return(SQLCODE);
1571
1572 END validate_item_org1;
1573
1574
1575 END INVPVALM;