[Home] [Help]
PACKAGE BODY: APPS.INVPVLM3
Source
1 PACKAGE BODY INVPVLM3 AS
2 /* $Header: INVPVM3B.pls 120.2 2005/08/23 22:32:31 lparihar ship $ */
3
4 FUNCTION validate_item_org7
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 /*
20 ** Retrieve column values for validation
21 */
22
23 CURSOR cc is
24 select
25 msii.TRANSACTION_ID,
26 msii.INVENTORY_ITEM_ID III,
27 msii.ORGANIZATION_ID ORGID,
28 mp.MASTER_ORGANIZATION_ID MORGID,
29 msii.NEGATIVE_MEASUREMENT_ERROR,
30 msii.ENGINEERING_ECN_CODE,
31 msii.ENGINEERING_ITEM_ID,
32 msii.ENGINEERING_DATE,
33 msii.SERVICE_STARTING_DELAY,
34 msii.SERVICEABLE_COMPONENT_FLAG,
35 msii.SERVICEABLE_PRODUCT_FLAG,
36 msii.BASE_WARRANTY_SERVICE_ID,
37 msii.PAYMENT_TERMS_ID,
38 msii.PREVENTIVE_MAINTENANCE_FLAG,
39 msii.PRIMARY_SPECIALIST_ID,
40 msii.SECONDARY_SPECIALIST_ID,
41 msii.SERVICEABLE_ITEM_CLASS_ID,
42 msii.TIME_BILLABLE_FLAG,
43 msii.MATERIAL_BILLABLE_FLAG,
44 msii.EXPENSE_BILLABLE_FLAG,
45 msii.PRORATE_SERVICE_FLAG,
46 msii.COVERAGE_SCHEDULE_ID,
47 msii.SERVICE_DURATION_PERIOD_CODE,
48 msii.SERVICE_DURATION,
49 msii.WARRANTY_VENDOR_ID,
50 msii.MAX_WARRANTY_AMOUNT,
51 msii.RESPONSE_TIME_PERIOD_CODE,
52 msii.RESPONSE_TIME_VALUE,
53 msii.NEW_REVISION_CODE,
54 msii.INVOICEABLE_ITEM_FLAG,
55 msii.TAX_CODE,
56 msii.INVOICE_ENABLED_FLAG,
57 msii.MUST_USE_APPROVED_VENDOR_FLAG,
58 msii.RELEASE_TIME_FENCE_CODE,
59 msii.RELEASE_TIME_FENCE_DAYS,
60 msii.CONTAINER_ITEM_FLAG,
61 msii.CONTAINER_TYPE_CODE,
62 msii.INTERNAL_VOLUME,
63 msii.MAXIMUM_LOAD_WEIGHT,
64 msii.MINIMUM_FILL_PERCENT,
65 msii.VEHICLE_ITEM_FLAG,
66 -- Added for 11.5.10
67 msii.TRACKING_QUANTITY_IND ,
68 msii.ONT_PRICING_QTY_SOURCE,
69 msii.SECONDARY_DEFAULT_IND ,
70 msii.CONFIG_ORGS,
71 msii.CONFIG_MATCH,
72 msii.VMI_MINIMUM_UNITS ,
73 msii.VMI_MINIMUM_DAYS ,
74 msii.VMI_MAXIMUM_UNITS ,
75 msii.VMI_MAXIMUM_DAYS ,
76 msii.VMI_FIXED_ORDER_QUANTITY ,
77 msii.SO_AUTHORIZATION_FLAG ,
78 msii.CONSIGNED_FLAG ,
79 msii.ASN_AUTOEXPIRE_FLAG ,
80 msii.VMI_FORECAST_TYPE ,
81 msii.FORECAST_HORIZON ,
82 msii.EXCLUDE_FROM_BUDGET_FLAG ,
83 msii.DAYS_TGT_INV_SUPPLY ,
84 msii.DAYS_TGT_INV_WINDOW ,
85 msii.DAYS_MAX_INV_SUPPLY ,
86 msii.DAYS_MAX_INV_WINDOW ,
87 msii.DRP_PLANNED_FLAG ,
88 msii.CRITICAL_COMPONENT_FLAG ,
89 msii.CONTINOUS_TRANSFER ,
90 msii.CONVERGENCE ,
91 msii.DIVERGENCE ,
92 /* Start Bug 3713912 */
93 msii.LOT_DIVISIBLE_FLAG ,
94 msii.GRADE_CONTROL_FLAG ,
95 msii.DEFAULT_GRADE ,
96 msii.CHILD_LOT_FLAG ,
97 msii.PARENT_CHILD_GENERATION_FLAG ,
98 msii.CHILD_LOT_PREFIX ,
99 msii.CHILD_LOT_STARTING_NUMBER ,
100 msii.CHILD_LOT_VALIDATION_FLAG ,
101 msii.COPY_LOT_ATTRIBUTE_FLAG ,
102 msii.RECIPE_ENABLED_FLAG ,
103 msii.PROCESS_QUALITY_ENABLED_FLAG ,
104 msii.PROCESS_EXECUTION_ENABLED_FLAG ,
105 msii.PROCESS_COSTING_ENABLED_FLAG ,
106 msii.PROCESS_SUPPLY_SUBINVENTORY ,
107 msii.PROCESS_SUPPLY_LOCATOR_ID ,
108 msii.PROCESS_YIELD_SUBINVENTORY ,
109 msii.PROCESS_YIELD_LOCATOR_ID ,
110 msii.HAZARDOUS_MATERIAL_FLAG ,
111 msii.CAS_NUMBER ,
112 msii.RETEST_INTERVAL ,
113 msii.EXPIRATION_ACTION_INTERVAL ,
114 msii.EXPIRATION_ACTION_CODE ,
115 msii.MATURITY_DAYS ,
116 msii.HOLD_DAYS
117 /* End Bug 3713912 */
118 from MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
119 where ((msii.organization_id = org_id) or
120 (all_Org = 1))
121 and msii.process_flag = 2
122 and msii.organization_id = mp.organization_id
123 and msii.set_process_id = xset_id
124 and msii.organization_id <> mp.master_organization_id;
125
126 /*
127 ** Attributes that are Item level (can't be different from master org's value)
128 */
129
130 CURSOR ee is
131 select attribute_name,
132 control_level
133 from MTL_ITEM_ATTRIBUTES
134 where control_level = 1;
135
136 msicount number;
137 msiicount number;
138 l_item_id number;
139 l_org_id number;
140 trans_id number;
141 ext_flag number := 0;
142 error_msg varchar2(70);
143 status number;
144 dumm_status number;
145 master_org_id number;
146 LOGGING_ERR exception;
147 VALIDATE_ERR exception;
148 X_TRUE number := 1;
149
150 A_NEGATIVE_MEASUREMENT_ERROR number := 2;
151 A_ENGINEERING_ECN_CODE number := 2;
152 A_ENGINEERING_ITEM_ID number := 2;
153 A_ENGINEERING_DATE number := 2;
154 A_SERVICE_STARTING_DELAY number := 2;
155 A_SERVICEABLE_COMPONENT_FLAG number := 2;
156 A_SERVICEABLE_PRODUCT_FLAG number := 2;
157 A_BASE_WARRANTY_SERVICE_ID number := 2;
158 A_PAYMENT_TERMS_ID number := 2;
159 A_PREVENTIVE_MAINTENANCE_FLAG number := 2;
160 A_PRIMARY_SPECIALIST_ID number := 2;
161 A_SECONDARY_SPECIALIST_ID number := 2;
162 A_SERVICEABLE_ITEM_CLASS_ID number := 2;
163 A_TIME_BILLABLE_FLAG number := 2;
164 A_MATERIAL_BILLABLE_FLAG number := 2;
165 A_EXPENSE_BILLABLE_FLAG number := 2;
166 A_PRORATE_SERVICE_FLAG number := 2;
167 A_COVERAGE_SCHEDULE_ID number := 2;
168 A_SERVICE_DURATION_PERIOD_CODE number := 2;
169 A_SERVICE_DURATION number := 2;
170 A_WARRANTY_VENDOR_ID number := 2;
171 A_MAX_WARRANTY_AMOUNT number := 2;
172 A_RESPONSE_TIME_PERIOD_CODE number := 2;
173 A_RESPONSE_TIME_VALUE number := 2;
174 A_NEW_REVISION_CODE number := 2;
175 A_INVOICEABLE_ITEM_FLAG number := 2;
176 A_TAX_CODE number := 2;
177 A_INVOICE_ENABLED_FLAG number := 2;
178 A_MUST_USE_APPROVED_VENDOR_F number := 2;
179 A_RELEASE_TIME_FENCE_CODE number := 2;
180 A_RELEASE_TIME_FENCE_DAYS number := 2;
181 A_CONTAINER_ITEM_FLAG number := 2;
182 A_CONTAINER_TYPE_CODE number := 2;
183 A_INTERNAL_VOLUME number := 2;
184 A_MAXIMUM_LOAD_WEIGHT number := 2;
185 A_MINIMUM_FILL_PERCENT number := 2;
186 A_VEHICLE_ITEM_FLAG number := 2;
187 -- Added for 11.5.10
188 A_TRACKING_QUANTITY_IND number := 2;
189 A_ONT_PRICING_QTY_SOURCE number := 2;
190 A_SECONDARY_DEFAULT_IND number := 2;
191 A_CONFIG_ORGS number := 2;
192 A_CONFIG_MATCH number := 2;
193 A_VMI_MINIMUM_UNITS number := 2;
194 A_VMI_MINIMUM_DAYS number := 2;
195 A_VMI_MAXIMUM_UNITS number := 2;
196 A_VMI_MAXIMUM_DAYS number := 2;
197 A_VMI_FIXED_ORDER_QUANTITY number := 2;
198 A_SO_AUTHORIZATION_FLAG number := 2;
199 A_CONSIGNED_FLAG number := 2;
200 A_ASN_AUTOEXPIRE_FLAG number := 2;
201 A_VMI_FORECAST_TYPE number := 2;
202 A_FORECAST_HORIZON number := 2;
203 A_EXCLUDE_FROM_BUDGET_FLAG number := 2;
204 A_DAYS_TGT_INV_SUPPLY number := 2;
205 A_DAYS_TGT_INV_WINDOW number := 2;
206 A_DAYS_MAX_INV_SUPPLY number := 2;
207 A_DAYS_MAX_INV_WINDOW number := 2;
208 A_DRP_PLANNED_FLAG number := 2;
209 A_CRITICAL_COMPONENT_FLAG number := 2;
210 A_CONTINOUS_TRANSFER number := 2;
211 A_CONVERGENCE number := 2;
212 A_DIVERGENCE number := 2;
213
214 /* Start Bug 3713912 */
215 A_LOT_DIVISIBLE_FLAG NUMBER := 2;
216 A_GRADE_CONTROL_FLAG NUMBER := 2;
217 A_DEFAULT_GRADE NUMBER := 2;
218 A_CHILD_LOT_FLAG NUMBER := 2;
219 A_PARENT_CHILD_GENERATION_FLAG NUMBER := 2;
220 A_CHILD_LOT_PREFIX NUMBER := 2;
221 A_CHILD_LOT_STARTING_NUMBER NUMBER := 2;
222 A_CHILD_LOT_VALIDATION_FLAG NUMBER := 2;
223 A_COPY_LOT_ATTRIBUTE_FLAG NUMBER := 2;
224 A_RECIPE_ENABLED_FLAG NUMBER := 2;
225 A_PROCESS_QUALITY_ENABLED_FLAG NUMBER := 2;
226 A_PROCESS_EXEC_ENABLED_FLAG NUMBER := 2;
227 A_PROCESS_COSTING_ENABLED_FLAG NUMBER := 2;
228 A_PROCESS_SUPPLY_SUBINVENTORY NUMBER := 2;
229 A_PROCESS_SUPPLY_LOCATOR_ID NUMBER := 2;
230 A_PROCESS_YIELD_SUBINVENTORY NUMBER := 2;
231 A_PROCESS_YIELD_LOCATOR_ID NUMBER := 2;
232 A_HAZARDOUS_MATERIAL_FLAG NUMBER := 2;
233 A_CAS_NUMBER NUMBER := 2;
234 A_RETEST_INTERVAL NUMBER := 2;
235 A_EXPIRATION_ACTION_INTERVAL NUMBER := 2;
236 A_EXPIRATION_ACTION_CODE NUMBER := 2;
237 A_MATURITY_DAYS NUMBER := 2;
238 A_HOLD_DAYS NUMBER := 2;
239
240 /* End Bug 3713912 */
241
242 begin
243
244 error_msg := 'Validation error in validating MTL_SYSTEM_ITEMS_INTERFACE with ';
245
246 /* set the attribute level variables to be used when validating a child's item
247 ** level attributes against the master org's attribute value. this is done
248 ** outside the loop so that it is only done once for all the records
249 ** instead of once PER record.
250 */
251
252 for att in ee loop
253 if substr(att.attribute_name,18) = 'NEGATIVE_MEASUREMENT_ERROR' then
254 A_NEGATIVE_MEASUREMENT_ERROR := att.control_level;
255 end if;
256 if substr(att.attribute_name,18) = 'ENGINEERING_ECN_CODE' then
257 A_ENGINEERING_ECN_CODE := att.control_level;
258 end if;
259 if substr(att.attribute_name,18) = 'ENGINEERING_ITEM_ID' then
260 A_ENGINEERING_ITEM_ID := att.control_level;
261 end if;
262 if substr(att.attribute_name,18) = 'ENGINEERING_DATE' then
263 A_ENGINEERING_DATE := att.control_level;
264 end if;
265 if substr(att.attribute_name,18) = 'SERVICE_STARTING_DELAY' then
266 A_SERVICE_STARTING_DELAY := att.control_level;
267 end if;
268 if substr(att.attribute_name,18) = 'SERVICEABLE_COMPONENT_FLAG' then
269 A_SERVICEABLE_COMPONENT_FLAG := att.control_level;
270 end if;
271 if substr(att.attribute_name,18) = 'SERVICEABLE_PRODUCT_FLAG' then
272 A_SERVICEABLE_PRODUCT_FLAG := att.control_level;
273 end if;
274 if substr(att.attribute_name,18) = 'BASE_WARRANTY_SERVICE_ID' then
275 A_BASE_WARRANTY_SERVICE_ID := att.control_level;
276 end if;
277 if substr(att.attribute_name,18) = 'PAYMENT_TERMS_ID' then
278 A_PAYMENT_TERMS_ID := att.control_level;
279 end if;
280 if substr(att.attribute_name,18) = 'PREVENTIVE_MAINTENANCE_FLAG' then
281 A_PREVENTIVE_MAINTENANCE_FLAG := att.control_level;
282 end if;
283 if substr(att.attribute_name,18) = 'PRIMARY_SPECIALIST_ID' then
284 A_PRIMARY_SPECIALIST_ID := att.control_level;
285 end if;
286 if substr(att.attribute_name,18) = 'SECONDARY_SPECIALIST_ID' then
287 A_SECONDARY_SPECIALIST_ID := att.control_level;
288 end if;
289 if substr(att.attribute_name,18) = 'SERVICEABLE_ITEM_CLASS_ID' then
290 A_SERVICEABLE_ITEM_CLASS_ID := att.control_level;
291 end if;
292 if substr(att.attribute_name,18) = 'TIME_BILLABLE_FLAG' then
293 A_TIME_BILLABLE_FLAG := att.control_level;
294 end if;
295 if substr(att.attribute_name,18) = 'MATERIAL_BILLABLE_FLAG' then
296 A_MATERIAL_BILLABLE_FLAG := att.control_level;
297 end if;
298 if substr(att.attribute_name,18) = 'EXPENSE_BILLABLE_FLAG' then
299 A_EXPENSE_BILLABLE_FLAG := att.control_level;
300 end if;
301 if substr(att.attribute_name,18) = 'PRORATE_SERVICE_FLAG' then
302 A_PRORATE_SERVICE_FLAG := att.control_level;
303 end if;
304 if substr(att.attribute_name,18) = 'COVERAGE_SCHEDULE_ID' then
305 A_COVERAGE_SCHEDULE_ID := att.control_level;
306 end if;
307 if substr(att.attribute_name,18) = 'SERVICE_DURATION_PERIOD_CODE' then
308 A_SERVICE_DURATION_PERIOD_CODE := att.control_level;
309 end if;
310 if substr(att.attribute_name,18) = 'SERVICE_DURATION' then
311 A_SERVICE_DURATION := att.control_level;
312 end if;
313 if substr(att.attribute_name,18) = 'WARRANTY_VENDOR_ID' then
314 A_WARRANTY_VENDOR_ID := att.control_level;
315 end if;
316 if substr(att.attribute_name,18) = 'MAX_WARRANTY_AMOUNT' then
317 A_MAX_WARRANTY_AMOUNT := att.control_level;
318 end if;
319 if substr(att.attribute_name,18) = 'RESPONSE_TIME_PERIOD_CODE' then
320 A_RESPONSE_TIME_PERIOD_CODE := att.control_level;
321 end if;
322 if substr(att.attribute_name,18) = 'RESPONSE_TIME_VALUE' then
323 A_RESPONSE_TIME_VALUE := att.control_level;
324 end if;
325 if substr(att.attribute_name,18) = 'NEW_REVISION_CODE' then
326 A_NEW_REVISION_CODE := att.control_level;
327 end if;
328 if substr(att.attribute_name,18) = 'INVOICEABLE_ITEM_FLAG' then
329 A_INVOICEABLE_ITEM_FLAG := att.control_level;
330 end if;
331 if substr(att.attribute_name,18) = 'TAX_CODE' then
332 A_TAX_CODE := att.control_level;
333 end if;
334 if substr(att.attribute_name,18) = 'INVOICE_ENABLED_FLAG' then
335 A_INVOICE_ENABLED_FLAG := att.control_level;
336 end if;
340 /*NP 19AUG96 Eight new cols added for 10.7 */
337 if substr(att.attribute_name,18) = 'MUST_USE_APPROVED_VENDOR_FLAG' then
338 A_MUST_USE_APPROVED_VENDOR_F := att.control_level;
339 end if;
341 if substr(att.attribute_name,18) = 'RELEASE_TIME_FENCE_CODE' then
342 A_RELEASE_TIME_FENCE_CODE := att.control_level;
343 end if;
344 if substr(att.attribute_name,18) = 'RELEASE_TIME_FENCE_DAYS' then
345 A_RELEASE_TIME_FENCE_DAYS := att.control_level;
346 end if;
347 if substr(att.attribute_name,18) = 'CONTAINER_ITEM_FLAG' then
348 A_CONTAINER_ITEM_FLAG:= att.control_level;
349 end if;
350 if substr(att.attribute_name,18) = 'CONTAINER_TYPE_CODE' then
351 A_CONTAINER_TYPE_CODE := att.control_level;
352 end if;
353 if substr(att.attribute_name,18) = 'INTERNAL_VOLUME' then
354 A_INTERNAL_VOLUME := att.control_level;
355 end if;
356 if substr(att.attribute_name,18) = 'MAXIMUM_LOAD_WEIGHT' then
357 A_MAXIMUM_LOAD_WEIGHT := att.control_level;
358 end if;
359 if substr(att.attribute_name,18) = 'MINIMUM_FILL_PERCENT' then
360 A_MINIMUM_FILL_PERCENT := att.control_level;
361 end if;
362 if substr(att.attribute_name,18) = 'VEHICLE_ITEM_FLAG' then
363 A_VEHICLE_ITEM_FLAG := att.control_level;
364 end if;
365 /*New cols added for 11.5.10 */
366 if substr(att.attribute_name,18) = 'TRACKING_QUANTITY_IND' then
367 A_TRACKING_QUANTITY_IND := att.control_level;
368 end if;
369 if substr(att.attribute_name,18) = 'ONT_PRICING_QTY_SOURCE' then
370 A_ONT_PRICING_QTY_SOURCE := att.control_level;
371 end if;
372 if substr(att.attribute_name,18) = 'SECONDARY_DEFAULT_IND' then
373 A_SECONDARY_DEFAULT_IND:= att.control_level;
374 end if;
375 if substr(att.attribute_name,18) = 'CONFIG_ORGS' then
376 A_CONFIG_ORGS := att.control_level;
377 end if;
378 if substr(att.attribute_name,18) = 'CONFIG_MATCH' then
379 A_CONFIG_MATCH := att.control_level;
380 end if;
381 IF substr(att.attribute_name,18) = 'VMI_MINIMUM_UNITS' then
382 A_VMI_MINIMUM_UNITS := att.control_level;
383 END IF;
384 IF substr(att.attribute_name,18) = 'VMI_MINIMUM_DAYS' then
385 A_VMI_MINIMUM_DAYS := att.control_level;
386 END IF;
387 IF substr(att.attribute_name,18) = 'VMI_MAXIMUM_UNITS' then
388 A_VMI_MAXIMUM_UNITS := att.control_level;
389 END IF;
390 IF substr(att.attribute_name,18) = 'VMI_MAXIMUM_DAYS' then
391 A_VMI_MAXIMUM_DAYS := att.control_level;
392 END IF;
393 IF substr(att.attribute_name,18) = 'VMI_FIXED_ORDER_QUANTITY' then
394 A_VMI_FIXED_ORDER_QUANTITY := att.control_level;
395 END IF;
396 IF substr(att.attribute_name,18) = 'SO_AUTHORIZATION_FLAG' then
397 A_SO_AUTHORIZATION_FLAG := att.control_level;
398 END IF;
399 IF substr(att.attribute_name,18) = 'CONSIGNED_FLAG' then
400 A_CONSIGNED_FLAG := att.control_level;
401 END IF;
402 IF substr(att.attribute_name,18) = 'ASN_AUTOEXPIRE_FLAG' then
403 A_ASN_AUTOEXPIRE_FLAG := att.control_level;
404 END IF;
405 IF substr(att.attribute_name,18) = 'VMI_FORECAST_TYPE' then
406 A_VMI_FORECAST_TYPE := att.control_level;
407 END IF;
408 IF substr(att.attribute_name,18) = 'FORECAST_HORIZON' then
409 A_FORECAST_HORIZON := att.control_level;
410 END IF;
411 IF substr(att.attribute_name,18) = 'EXCLUDE_FROM_BUDGET_FLAG' then
412 A_EXCLUDE_FROM_BUDGET_FLAG := att.control_level;
413 END IF;
414 IF substr(att.attribute_name,18) = 'DAYS_TGT_INV_SUPPLY' then
415 A_DAYS_TGT_INV_SUPPLY := att.control_level;
416 END IF;
417 IF substr(att.attribute_name,18) = 'DAYS_TGT_INV_WINDOW' then
418 A_DAYS_TGT_INV_WINDOW := att.control_level;
419 END IF;
420 IF substr(att.attribute_name,18) = 'DAYS_MAX_INV_SUPPLY' then
421 A_DAYS_MAX_INV_SUPPLY := att.control_level;
422 END IF;
423 IF substr(att.attribute_name,18) = 'DAYS_MAX_INV_WINDOW' then
424 A_DAYS_MAX_INV_WINDOW := att.control_level;
425 END IF;
426 IF substr(att.attribute_name,18) = 'DRP_PLANNED_FLAG' then
427 A_DRP_PLANNED_FLAG := att.control_level;
428 END IF;
429 IF substr(att.attribute_name,18) = 'CRITICAL_COMPONENT_FLAG' then
430 A_CRITICAL_COMPONENT_FLAG := att.control_level;
431 END IF;
432 IF substr(att.attribute_name,18) = 'CONTINOUS_TRANSFER' then
436 A_CONVERGENCE:= att.control_level;
433 A_CONTINOUS_TRANSFER:= att.control_level;
434 END IF;
435 IF substr(att.attribute_name,18) = 'CONVERGENCE' then
437 END IF;
438 IF substr(att.attribute_name,18) = 'DIVERGENCE' then
439 A_DIVERGENCE:= att.control_level;
440 END IF;
441
442 /*Begin Bug 3713912 */
443 IF substr(att.attribute_name,18) = 'LOT_DIVISIBLE_FLAG' THEN
444 A_LOT_DIVISIBLE_FLAG := att.control_level;
445 END IF;
446 IF substr(att.attribute_name,18) = 'GRADE_CONTROL_FLAG' THEN
447 A_GRADE_CONTROL_FLAG := att.control_level;
448 END IF;
449 IF substr(att.attribute_name,18) = 'DEFAULT_GRADE' THEN
450 A_DEFAULT_GRADE := att.control_level;
451 END IF;
452 IF substr(att.attribute_name,18) = 'CHILD_LOT_FLAG' THEN
453 A_CHILD_LOT_FLAG := att.control_level;
454 END IF;
455 IF substr(att.attribute_name,18) = 'PARENT_CHILD_GENERATION_FLAG' THEN
456 A_PARENT_CHILD_GENERATION_FLAG := att.control_level;
457 END IF;
458 IF substr(att.attribute_name,18) = 'CHILD_LOT_PREFIX' THEN
459 A_CHILD_LOT_PREFIX := att.control_level;
460 END IF;
461 IF substr(att.attribute_name,18) = 'CHILD_LOT_STARTING_NUMBER' THEN
462 A_CHILD_LOT_STARTING_NUMBER := att.control_level;
463 END IF;
464 IF substr(att.attribute_name,18) = 'CHILD_LOT_VALIDATION_FLAG' THEN
465 A_CHILD_LOT_VALIDATION_FLAG := att.control_level;
466 END IF;
467 IF substr(att.attribute_name,18) = 'COPY_LOT_ATTRIBUTE_FLAG' THEN
468 A_COPY_LOT_ATTRIBUTE_FLAG := att.control_level;
469 END IF;
470 IF substr(att.attribute_name,18) = 'RECIPE_ENABLED_FLAG' THEN
471 A_RECIPE_ENABLED_FLAG := att.control_level;
472 END IF;
473 IF substr(att.attribute_name,18) = 'PROCESS_QUALITY_ENABLED_FLAG' THEN
474 A_PROCESS_QUALITY_ENABLED_FLAG := att.control_level;
475 END IF;
476 IF substr(att.attribute_name,18) = 'PROCESS_EXECUTION_ENABLED_FLAG' THEN
477 A_PROCESS_EXEC_ENABLED_FLAG := att.control_level;
478 END IF;
479 IF substr(att.attribute_name,18) = 'PROCESS_COSTING_ENABLED_FLAG' THEN
480 A_PROCESS_COSTING_ENABLED_FLAG := att.control_level;
481 END IF;
482 IF substr(att.attribute_name,18) = 'PROCESS_SUPPLY_SUBINVENTORY' THEN
483 A_PROCESS_SUPPLY_SUBINVENTORY := att.control_level;
484 END IF;
485 IF substr(att.attribute_name,18) = 'PROCESS_SUPPLY_LOCATOR_ID' THEN
486 A_PROCESS_SUPPLY_LOCATOR_ID := att.control_level;
487 END IF;
488 IF substr(att.attribute_name,18) = 'PROCESS_YIELD_SUBINVENTORY' THEN
489 A_PROCESS_YIELD_SUBINVENTORY := att.control_level;
490 END IF;
491 IF substr(att.attribute_name,18) = 'PROCESS_YIELD_LOCATOR_ID' THEN
492 A_PROCESS_YIELD_LOCATOR_ID := att.control_level;
493 END IF;
494 IF substr(att.attribute_name,18) = 'HAZARDOUS_MATERIAL_FLAG' THEN
495 A_HAZARDOUS_MATERIAL_FLAG := att.control_level;
496 END IF;
497 IF substr(att.attribute_name,18) = 'CAS_NUMBER' THEN
498 A_CAS_NUMBER := att.control_level;
499 END IF;
500 IF substr(att.attribute_name,18) = 'RETEST_INTERVAL' THEN
501 A_RETEST_INTERVAL := att.control_level;
502 END IF;
503 IF substr(att.attribute_name,18) = 'EXPIRATION_ACTION_INTERVAL' THEN
504 A_EXPIRATION_ACTION_INTERVAL := att.control_level;
505 END IF;
506 IF substr(att.attribute_name,18) = 'EXPIRATION_ACTION_CODE' THEN
507 A_EXPIRATION_ACTION_CODE := att.control_level;
508 END IF;
509 IF substr(att.attribute_name,18) = 'MATURITY_DAYS' THEN
510 A_MATURITY_DAYS := att.control_level;
511 END IF;
512 IF substr(att.attribute_name,18) = 'HOLD_DAYS' THEN
513 A_HOLD_DAYS := att.control_level;
514 END IF;
515 /* End Bug 3713912 */
516
517 end loop;
518
519 -- validate the records
520
521 for cr in cc loop
522 status := 0;
523 trans_id := cr.transaction_id;
524 l_org_id := cr.ORGID;
525
526 begin /* MASTER_CHILD_7A */
527
528 select inventory_item_id into msicount
529 from mtl_system_items msi
530 where msi.inventory_item_id = cr.III
531 and msi.organization_id = cr.MORGID
532 and decode(A_ENGINEERING_DATE,X_TRUE,nvl(cr.ENGINEERING_DATE,TO_DATE('1000/01/01','YYYY/MM/DD')),nvl(msi.ENGINEERING_DATE,TO_DATE('1000/01/01','YYYY/MM/DD')))=nvl(msi.ENGINEERING_DATE,TO_DATE('1000/01/01','YYYY/MM/DD'))
533 and decode(A_NEGATIVE_MEASUREMENT_ERROR,X_TRUE,nvl(cr.NEGATIVE_MEASUREMENT_ERROR,-1),nvl(msi.NEGATIVE_MEASUREMENT_ERROR,-1))=nvl(msi.NEGATIVE_MEASUREMENT_ERROR,-1)
537 and decode(A_SERVICEABLE_COMPONENT_FLAG,X_TRUE,nvl(cr.SERVICEABLE_COMPONENT_FLAG,-1),nvl(msi.SERVICEABLE_COMPONENT_FLAG,-1))=nvl(msi.SERVICEABLE_COMPONENT_FLAG,-1);
534 and decode(A_ENGINEERING_ECN_CODE,X_TRUE,nvl(cr.ENGINEERING_ECN_CODE,-1),nvl(msi.ENGINEERING_ECN_CODE,-1))=nvl(msi.ENGINEERING_ECN_CODE,-1)
535 and decode(A_ENGINEERING_ITEM_ID,X_TRUE,nvl(cr.ENGINEERING_ITEM_ID,-1),nvl(msi.ENGINEERING_ITEM_ID,-1))=nvl(msi.ENGINEERING_ITEM_ID,-1)
536 and decode(A_SERVICE_STARTING_DELAY,X_TRUE,nvl(cr.SERVICE_STARTING_DELAY,-1),nvl(msi.SERVICE_STARTING_DELAY,-1))=nvl(msi.SERVICE_STARTING_DELAY,-1)
538
539 exception
540 when NO_DATA_FOUND then
541 dumm_status := INVPUOPI.mtl_log_interface_err(
542 cr.ORGID,
543 user_id,
544 login_id,
545 prog_appid,
546 prog_id,
547 request_id,
548 cr.TRANSACTION_ID,
549 error_msg,
550 'MASTER_CHILD_7A',
551 'MTL_SYSTEM_ITEMS_INTERFACE',
552 'INV_IOI_MASTER_CHILD_7A',
553 err_text);
554 If dumm_status < 0 then
555 raise LOGGING_ERR ;
556 End if;
557 update mtl_system_items_interface msii
558 set process_flag = 3
559 where msii.transaction_id = cr.transaction_id;
560
561 end; /* MASTER_CHILD_7A */
562
563
564 begin /* MASTER_CHILD_7B */
565
566 select inventory_item_id into msicount
567 from mtl_system_items msi
568 where msi.inventory_item_id = cr.III
569 and msi.organization_id = cr.MORGID
570 and decode(A_SERVICEABLE_PRODUCT_FLAG,X_TRUE,nvl(cr.SERVICEABLE_PRODUCT_FLAG,-1),nvl(msi.SERVICEABLE_PRODUCT_FLAG,-1))=nvl(msi.SERVICEABLE_PRODUCT_FLAG,-1)
571 and decode(A_BASE_WARRANTY_SERVICE_ID,X_TRUE,nvl(cr.BASE_WARRANTY_SERVICE_ID,-1),nvl(msi.BASE_WARRANTY_SERVICE_ID,-1))=nvl(msi.BASE_WARRANTY_SERVICE_ID,-1)
572 and decode(A_PAYMENT_TERMS_ID,X_TRUE,nvl(cr.PAYMENT_TERMS_ID,-1),nvl(msi.PAYMENT_TERMS_ID,-1))=nvl(msi.PAYMENT_TERMS_ID,-1)
573 and decode(A_PREVENTIVE_MAINTENANCE_FLAG,X_TRUE,nvl(cr.PREVENTIVE_MAINTENANCE_FLAG,-1),nvl(msi.PREVENTIVE_MAINTENANCE_FLAG,-1))=nvl(msi.PREVENTIVE_MAINTENANCE_FLAG,-1)
574 and decode(A_PRIMARY_SPECIALIST_ID,X_TRUE,nvl(cr.PRIMARY_SPECIALIST_ID,-1),nvl(msi.PRIMARY_SPECIALIST_ID,-1))=nvl(msi.PRIMARY_SPECIALIST_ID,-1)
575 and decode(A_SECONDARY_SPECIALIST_ID,X_TRUE,nvl(cr.SECONDARY_SPECIALIST_ID,-1),nvl(msi.SECONDARY_SPECIALIST_ID,-1))=nvl(msi.SECONDARY_SPECIALIST_ID,-1)
576 and decode(A_SERVICEABLE_ITEM_CLASS_ID,X_TRUE,nvl(cr.SERVICEABLE_ITEM_CLASS_ID,-1),nvl(msi.SERVICEABLE_ITEM_CLASS_ID,-1))=nvl(msi.SERVICEABLE_ITEM_CLASS_ID,-1);
577
578 exception
579 when NO_DATA_FOUND then
580 dumm_status := INVPUOPI.mtl_log_interface_err(
581 cr.ORGID,
582 user_id,
583 login_id,
584 prog_appid,
585 prog_id,
586 request_id,
587 cr.TRANSACTION_ID,
588 error_msg,
589 'MASTER_CHILD_7B',
590 'MTL_SYSTEM_ITEMS_INTERFACE',
591 'INV_IOI_MASTER_CHILD_7B',
592 err_text);
593 If dumm_status < 0 then
594 raise LOGGING_ERR ;
595 End if;
596 update mtl_system_items_interface msii
597 set process_flag = 3
598 where msii.transaction_id = cr.transaction_id;
599
600 end; /* MASTER_CHILD_7B */
601
602 begin /* MASTER_CHILD_7C */
603
604 select inventory_item_id into msicount
605 from mtl_system_items msi
606 where msi.inventory_item_id = cr.III
607 and msi.organization_id = cr.MORGID
608 --Bug 4496767 and decode(A_TIME_BILLABLE_FLAG,X_TRUE,nvl(cr.TIME_BILLABLE_FLAG,-1),nvl(msi.TIME_BILLABLE_FLAG,-1))=nvl(msi.TIME_BILLABLE_FLAG,-1)
609 and decode(A_MATERIAL_BILLABLE_FLAG,X_TRUE,nvl(cr.MATERIAL_BILLABLE_FLAG,-1),nvl(msi.MATERIAL_BILLABLE_FLAG,-1))=nvl(msi.MATERIAL_BILLABLE_FLAG,-1)
610 --Bug 4496767 and decode(A_EXPENSE_BILLABLE_FLAG,X_TRUE,nvl(cr.EXPENSE_BILLABLE_FLAG,-1),nvl(msi.EXPENSE_BILLABLE_FLAG,-1))=nvl(msi.EXPENSE_BILLABLE_FLAG,-1)
611 and decode(A_PRORATE_SERVICE_FLAG,X_TRUE,nvl(cr.PRORATE_SERVICE_FLAG,-1),nvl(msi.PRORATE_SERVICE_FLAG,-1))=nvl(msi.PRORATE_SERVICE_FLAG,-1)
612 and decode(A_COVERAGE_SCHEDULE_ID,X_TRUE,nvl(cr.COVERAGE_SCHEDULE_ID,-1),nvl(msi.COVERAGE_SCHEDULE_ID,-1))=nvl(msi.COVERAGE_SCHEDULE_ID,-1)
613 and decode(A_SERVICE_DURATION_PERIOD_CODE,X_TRUE,nvl(cr.SERVICE_DURATION_PERIOD_CODE,-1),nvl(msi.SERVICE_DURATION_PERIOD_CODE,-1))=nvl(msi.SERVICE_DURATION_PERIOD_CODE,-1)
614 and decode(A_SERVICE_DURATION,X_TRUE,nvl(cr.SERVICE_DURATION,-1),nvl(msi.SERVICE_DURATION,-1))=nvl(msi.SERVICE_DURATION,-1)
618 when NO_DATA_FOUND then
615 and decode(A_WARRANTY_VENDOR_ID,X_TRUE,nvl(cr.WARRANTY_VENDOR_ID,-1),nvl(msi.WARRANTY_VENDOR_ID,-1))=nvl(msi.WARRANTY_VENDOR_ID,-1);
616
617 exception
619 dumm_status := INVPUOPI.mtl_log_interface_err(
620 cr.ORGID,
621 user_id,
622 login_id,
623 prog_appid,
624 prog_id,
625 request_id,
626 cr.TRANSACTION_ID,
627 error_msg,
628 'MASTER_CHILD_7C',
629 'MTL_SYSTEM_ITEMS_INTERFACE',
630 'INV_IOI_MASTER_CHILD_7C',
631 err_text);
632 If dumm_status < 0 then
633 raise LOGGING_ERR ;
634 End if;
635 update mtl_system_items_interface msii
636 set process_flag = 3
637 where msii.transaction_id = cr.transaction_id;
638
639 end; /* MASTER_CHILD_7C */
640
641 begin /* MASTER_CHILD_7D */
642
643 select inventory_item_id into msicount
644 from mtl_system_items msi
645 where msi.inventory_item_id = cr.III
646 and msi.organization_id = cr.MORGID
647 and decode(A_MAX_WARRANTY_AMOUNT,X_TRUE,nvl(cr.MAX_WARRANTY_AMOUNT,-1),nvl(msi.MAX_WARRANTY_AMOUNT,-1))=nvl(msi.MAX_WARRANTY_AMOUNT,-1)
648 and decode(A_RESPONSE_TIME_PERIOD_CODE,X_TRUE,nvl(cr.RESPONSE_TIME_PERIOD_CODE,-1),nvl(msi.RESPONSE_TIME_PERIOD_CODE,-1))=nvl(msi.RESPONSE_TIME_PERIOD_CODE,-1)
649 and decode(A_RESPONSE_TIME_VALUE,X_TRUE,nvl(cr.RESPONSE_TIME_VALUE,-1),nvl(msi.RESPONSE_TIME_VALUE,-1))=nvl(msi.RESPONSE_TIME_VALUE,-1)
650 and decode(A_NEW_REVISION_CODE,X_TRUE,nvl(cr.NEW_REVISION_CODE,-1),nvl(msi.NEW_REVISION_CODE,-1))=nvl(msi.NEW_REVISION_CODE,-1)
651 and decode(A_INVOICEABLE_ITEM_FLAG,X_TRUE,nvl(cr.INVOICEABLE_ITEM_FLAG,-1),nvl(msi.INVOICEABLE_ITEM_FLAG,-1))=nvl(msi.INVOICEABLE_ITEM_FLAG,-1)
652 and decode(A_TAX_CODE,X_TRUE,nvl(cr.TAX_CODE,-1),nvl(msi.TAX_CODE,-1))=nvl(msi.TAX_CODE,-1)
653 and decode(A_INVOICE_ENABLED_FLAG,X_TRUE,nvl(cr.INVOICE_ENABLED_FLAG,-1),nvl(msi.INVOICE_ENABLED_FLAG,-1))=nvl(msi.INVOICE_ENABLED_FLAG,-1)
654 and decode(A_MUST_USE_APPROVED_VENDOR_F,X_TRUE,nvl(cr.MUST_USE_APPROVED_VENDOR_FLAG,-1),nvl(msi.MUST_USE_APPROVED_VENDOR_FLAG,-1))=nvl(msi.MUST_USE_APPROVED_VENDOR_FLAG,-1);
655
656 exception
657 when NO_DATA_FOUND then
658 dumm_status := INVPUOPI.mtl_log_interface_err(
659 cr.ORGID,
660 user_id,
661 login_id,
662 prog_appid,
663 prog_id,
664 request_id,
665 cr.TRANSACTION_ID,
666 error_msg,
667 'MASTER_CHILD_7D',
668 'MTL_SYSTEM_ITEMS_INTERFACE',
669 'INV_IOI_MASTER_CHILD_7D',
670 err_text);
671 If dumm_status < 0 then
672 raise LOGGING_ERR ;
673 End if;
674 update mtl_system_items_interface msii
675 set process_flag = 3
676 where msii.transaction_id = cr.transaction_id;
677
678 end; /* MASTER_CHILD_7D */
679
680
681 begin /* MASTER_CHILD_7E */
682
683 select inventory_item_id into msicount
684 from mtl_system_items msi
685 where msi.inventory_item_id = cr.III
686 and msi.organization_id = cr.MORGID
687 and decode(A_RELEASE_TIME_FENCE_CODE,X_TRUE,nvl(cr.RELEASE_TIME_FENCE_CODE,-1),nvl(msi.RELEASE_TIME_FENCE_CODE,-1))=nvl(msi.RELEASE_TIME_FENCE_CODE,-1)
688 and decode(A_RELEASE_TIME_FENCE_DAYS,X_TRUE,nvl(cr.RELEASE_TIME_FENCE_DAYS,-1),nvl(msi.RELEASE_TIME_FENCE_DAYS,-1))=nvl(msi.RELEASE_TIME_FENCE_DAYS,-1)
689 and decode(A_CONTAINER_ITEM_FLAG,X_TRUE,nvl(cr.CONTAINER_ITEM_FLAG,-1),nvl(msi.CONTAINER_ITEM_FLAG,-1))=nvl(msi.CONTAINER_ITEM_FLAG,-1)
690 and decode(A_CONTAINER_TYPE_CODE,X_TRUE,nvl(cr.CONTAINER_TYPE_CODE,-1),nvl(msi.CONTAINER_TYPE_CODE,-1))=nvl(msi.CONTAINER_TYPE_CODE,-1)
691 and decode(A_INTERNAL_VOLUME,X_TRUE,nvl(cr.INTERNAL_VOLUME,-1),nvl(msi.INTERNAL_VOLUME,-1))=nvl(msi.INTERNAL_VOLUME,-1)
692 and decode(A_MAXIMUM_LOAD_WEIGHT,X_TRUE,nvl(cr.MAXIMUM_LOAD_WEIGHT,-1),nvl(msi.MAXIMUM_LOAD_WEIGHT,-1))=nvl(msi.MAXIMUM_LOAD_WEIGHT,-1)
693 and decode(A_MINIMUM_FILL_PERCENT,X_TRUE,nvl(cr.MINIMUM_FILL_PERCENT,-1),nvl(msi.MINIMUM_FILL_PERCENT,-1))=nvl(msi.MINIMUM_FILL_PERCENT,-1)
694 and decode(A_VEHICLE_ITEM_FLAG,X_TRUE,nvl(cr.VEHICLE_ITEM_FLAG,-1),nvl(msi.VEHICLE_ITEM_FLAG,-1))=nvl(msi.VEHICLE_ITEM_FLAG,-1);
695
696 exception
697 when NO_DATA_FOUND then
698 dumm_status := INVPUOPI.mtl_log_interface_err(
699 cr.ORGID,
700 user_id,
701 login_id,
702 prog_appid,
703 prog_id,
707 'MASTER_CHILD_7E',
704 request_id,
705 cr.TRANSACTION_ID,
706 error_msg,
708 'MTL_SYSTEM_ITEMS_INTERFACE',
709 'INV_IOI_MASTER_CHILD_7E',
710 err_text);
711 If dumm_status < 0 then
712 raise LOGGING_ERR ;
713 End if;
714 update mtl_system_items_interface msii
715 set process_flag = 3
716 where msii.transaction_id = cr.transaction_id;
717
718 end; /* MASTER_CHILD_7E */
719 -- Added for 11.5.10
720 begin /* MASTER_CHILD_7F */
721
722 select inventory_item_id into msicount
723 from mtl_system_items msi
724 where msi.inventory_item_id = cr.III
725 and msi.organization_id = cr.MORGID
726 and decode(A_TRACKING_QUANTITY_IND,X_TRUE,nvl(cr.TRACKING_QUANTITY_IND,-1),nvl(msi.TRACKING_QUANTITY_IND,-1))=nvl(msi.TRACKING_QUANTITY_IND,-1)
727 and decode(A_ONT_PRICING_QTY_SOURCE,X_TRUE,nvl(cr.ONT_PRICING_QTY_SOURCE,-1),nvl(msi.ONT_PRICING_QTY_SOURCE,-1))=nvl(msi.ONT_PRICING_QTY_SOURCE,-1)
728 and decode(A_SECONDARY_DEFAULT_IND,X_TRUE,nvl(cr.SECONDARY_DEFAULT_IND,-1),nvl(msi.SECONDARY_DEFAULT_IND,-1))=nvl(msi.SECONDARY_DEFAULT_IND,-1);
729 exception
730 when NO_DATA_FOUND then
731 dumm_status := INVPUOPI.mtl_log_interface_err(
732 cr.ORGID,
733 user_id,
734 login_id,
735 prog_appid,
736 prog_id,
737 request_id,
738 cr.TRANSACTION_ID,
739 error_msg,
740 'MASTER_CHILD_7F',
741 'MTL_SYSTEM_ITEMS_INTERFACE',
742 'INV_IOI_MASTER_CHILD_7F',
743 err_text);
744 If dumm_status < 0 then
745 raise LOGGING_ERR ;
746 End if;
747 update mtl_system_items_interface msii
748 set process_flag = 3
749 where msii.transaction_id = cr.transaction_id;
750
751 end; /* MASTER_CHILD_7F */
752
753 begin /* MASTER_CHILD_7G */
754
755 select inventory_item_id into msicount
756 from mtl_system_items msi
757 where msi.inventory_item_id = cr.III
758 and msi.organization_id = cr.MORGID
759 and decode(A_CONFIG_ORGS,X_TRUE,nvl(cr.CONFIG_ORGS,-1),nvl(msi.CONFIG_ORGS,-1))=nvl(msi.CONFIG_ORGS,-1)
760 and decode(A_CONFIG_MATCH,X_TRUE,nvl(cr.CONFIG_MATCH,-1),nvl(msi.CONFIG_MATCH,-1))=nvl(msi.CONFIG_MATCH,-1);
761 exception
762 when NO_DATA_FOUND then
763 dumm_status := INVPUOPI.mtl_log_interface_err(
764 cr.ORGID,
765 user_id,
766 login_id,
767 prog_appid,
768 prog_id,
769 request_id,
770 cr.TRANSACTION_ID,
771 error_msg,
772 'MASTER_CHILD_7G',
773 'MTL_SYSTEM_ITEMS_INTERFACE',
774 'INV_IOI_MASTER_CHILD_7G',
775 err_text);
776 If dumm_status < 0 then
777 raise LOGGING_ERR ;
778 End if;
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_7G */
784
785 begin /* MASTER_CHILD_7H */
786
787 select inventory_item_id into msicount
788 from mtl_system_items msi
789 where msi.inventory_item_id = cr.III
790 and msi.organization_id = cr.MORGID
791 and decode(A_SO_AUTHORIZATION_FLAG,X_TRUE,nvl(cr.SO_AUTHORIZATION_FLAG,-1),nvl(msi.SO_AUTHORIZATION_FLAG,-1))=nvl(msi.SO_AUTHORIZATION_FLAG,-1)
792 and decode(A_VMI_MINIMUM_UNITS,X_TRUE,nvl(cr.VMI_MINIMUM_UNITS,-1),nvl(msi.VMI_MINIMUM_UNITS,-1))=nvl(msi.VMI_MINIMUM_UNITS,-1)
793 and decode(A_VMI_MINIMUM_DAYS,X_TRUE,nvl(cr.VMI_MINIMUM_DAYS,-1),nvl(msi.VMI_MINIMUM_DAYS,-1))= nvl(msi.VMI_MINIMUM_DAYS,-1)
794 and decode(A_VMI_MAXIMUM_DAYS,X_TRUE,nvl(cr.VMI_MAXIMUM_DAYS,-1),nvl(msi.VMI_MAXIMUM_DAYS,-1))= nvl(msi.VMI_MAXIMUM_DAYS,-1)
795 and decode(A_VMI_MAXIMUM_UNITS,X_TRUE,nvl(cr.VMI_MAXIMUM_UNITS,-1),nvl(msi.VMI_MAXIMUM_UNITS,-1))= nvl(msi.VMI_MAXIMUM_UNITS,-1)
796 and decode(A_VMI_FIXED_ORDER_QUANTITY,X_TRUE,nvl(cr.VMI_FIXED_ORDER_QUANTITY,-1),nvl(msi.VMI_FIXED_ORDER_QUANTITY,-1))= nvl(msi.VMI_FIXED_ORDER_QUANTITY,-1)
797 and decode(A_CONSIGNED_FLAG,X_TRUE,nvl(cr.CONSIGNED_FLAG,-1),nvl(msi.CONSIGNED_FLAG,-1))= nvl(msi.CONSIGNED_FLAG,-1)
798 and decode(A_ASN_AUTOEXPIRE_FLAG,X_TRUE,nvl(cr.ASN_AUTOEXPIRE_FLAG,-1),nvl(msi.ASN_AUTOEXPIRE_FLAG,-1))= nvl(msi.ASN_AUTOEXPIRE_FLAG,-1)
802 and decode(A_DAYS_TGT_INV_WINDOW,X_TRUE,nvl(cr.DAYS_TGT_INV_WINDOW,-1),nvl(msi.DAYS_TGT_INV_WINDOW,-1))= nvl(msi.DAYS_TGT_INV_WINDOW,-1)
799 and decode(A_FORECAST_HORIZON,X_TRUE,nvl(cr.FORECAST_HORIZON,-1),nvl(msi.FORECAST_HORIZON,-1))= nvl(msi.FORECAST_HORIZON,-1)
800 and decode(A_EXCLUDE_FROM_BUDGET_FLAG,X_TRUE,nvl(cr.EXCLUDE_FROM_BUDGET_FLAG,-1),nvl(msi.EXCLUDE_FROM_BUDGET_FLAG,-1))= nvl(msi.EXCLUDE_FROM_BUDGET_FLAG,-1)
801 and decode(A_DAYS_TGT_INV_SUPPLY,X_TRUE,nvl(cr.DAYS_TGT_INV_SUPPLY,-1),nvl(msi.DAYS_TGT_INV_SUPPLY,-1))= nvl(msi.DAYS_TGT_INV_SUPPLY,-1)
803 and decode(A_DAYS_MAX_INV_WINDOW,X_TRUE,nvl(cr.DAYS_MAX_INV_WINDOW,-1),nvl(msi.DAYS_MAX_INV_WINDOW,-1))= nvl(msi.DAYS_MAX_INV_WINDOW,-1)
804 and decode(A_DAYS_MAX_INV_SUPPLY,X_TRUE,nvl(cr.DAYS_MAX_INV_SUPPLY,-1),nvl(msi.DAYS_MAX_INV_SUPPLY,-1))= nvl(msi.DAYS_MAX_INV_SUPPLY,-1)
805 and decode(A_DRP_PLANNED_FLAG,X_TRUE,nvl(cr.DRP_PLANNED_FLAG,-1),nvl(msi.DRP_PLANNED_FLAG,-1))= nvl(msi.DRP_PLANNED_FLAG,-1)
806 and decode(A_CRITICAL_COMPONENT_FLAG,X_TRUE,nvl(cr.CRITICAL_COMPONENT_FLAG,-1),nvl(msi.CRITICAL_COMPONENT_FLAG,-1))= nvl(msi.CRITICAL_COMPONENT_FLAG,-1)
807 and decode(A_CONTINOUS_TRANSFER,X_TRUE,nvl(cr.CONTINOUS_TRANSFER,-1),nvl(msi.CONTINOUS_TRANSFER,-1))=nvl(msi.CONTINOUS_TRANSFER,-1)
808 and decode(A_VMI_FORECAST_TYPE,X_TRUE,nvl(cr.VMI_FORECAST_TYPE,-1),nvl(msi.VMI_FORECAST_TYPE,-1))=nvl(msi.VMI_FORECAST_TYPE,-1)
809 and decode(A_CONVERGENCE,X_TRUE,nvl(cr.CONVERGENCE,-1),nvl(msi.CONVERGENCE,-1))=nvl(msi.CONVERGENCE,-1)
810 and decode(A_DIVERGENCE,X_TRUE,nvl(cr.DIVERGENCE,-1),nvl(msi.DIVERGENCE,-1))=nvl(msi.DIVERGENCE,-1) ;
811 exception
812 when NO_DATA_FOUND then
813 dumm_status := INVPUOPI.mtl_log_interface_err(
814 cr.ORGID,
815 user_id,
816 login_id,
817 prog_appid,
818 prog_id,
819 request_id,
820 cr.TRANSACTION_ID,
821 error_msg,
822 'MASTER_CHILD_7G',
823 'MTL_SYSTEM_ITEMS_INTERFACE',
824 'INV_IOI_MASTER_CHILD_7H',
825 err_text);
826 If dumm_status < 0 then
827 raise LOGGING_ERR ;
828 End if;
829 update mtl_system_items_interface msii
830 set process_flag = 3
831 where msii.transaction_id = cr.transaction_id;
832
833 end; /* MASTER_CHILD_7H */
834
835 /* Start Bug 3713912 */
836 begin /* MASTER_CHILD_7I */
837
838 select inventory_item_id into msicount
839 from mtl_system_items msi
840 where msi.inventory_item_id = cr.III
841 and msi.organization_id = cr.MORGID
842 and decode(A_LOT_DIVISIBLE_FLAG ,X_TRUE,nvl(cr.LOT_DIVISIBLE_FLAG ,-1),nvl(msi.LOT_DIVISIBLE_FLAG ,-1))= nvl(msi.LOT_DIVISIBLE_FLAG,-1)
843 and decode(A_GRADE_CONTROL_FLAG ,X_TRUE,nvl(cr.GRADE_CONTROL_FLAG ,-1),nvl(msi.GRADE_CONTROL_FLAG ,-1))= nvl(msi.GRADE_CONTROL_FLAG,-1)
844 and decode(A_DEFAULT_GRADE ,X_TRUE,nvl(cr.DEFAULT_GRADE ,-1),nvl(msi.DEFAULT_GRADE ,-1))= nvl(msi.DEFAULT_GRADE,-1)
845 and decode(A_CHILD_LOT_FLAG ,X_TRUE,nvl(cr.CHILD_LOT_FLAG ,-1),nvl(msi.CHILD_LOT_FLAG ,-1))= nvl(msi.CHILD_LOT_FLAG,-1)
846 and decode(A_PARENT_CHILD_GENERATION_FLAG ,X_TRUE,nvl(cr.PARENT_CHILD_GENERATION_FLAG ,-1),nvl(msi.PARENT_CHILD_GENERATION_FLAG ,-1))= nvl(msi.PARENT_CHILD_GENERATION_FLAG,-1)
847 and decode(A_CHILD_LOT_PREFIX ,X_TRUE,nvl(cr.CHILD_LOT_PREFIX ,-1),nvl(msi.CHILD_LOT_PREFIX ,-1))= nvl(msi.CHILD_LOT_PREFIX,-1)
848 and decode(A_CHILD_LOT_STARTING_NUMBER ,X_TRUE,nvl(cr.CHILD_LOT_STARTING_NUMBER ,-1),nvl(msi.CHILD_LOT_STARTING_NUMBER ,-1))= nvl(msi.CHILD_LOT_STARTING_NUMBER,-1)
849 and decode(A_CHILD_LOT_VALIDATION_FLAG ,X_TRUE,nvl(cr.CHILD_LOT_VALIDATION_FLAG ,-1),nvl(msi.CHILD_LOT_VALIDATION_FLAG ,-1))= nvl(msi.CHILD_LOT_VALIDATION_FLAG,-1)
850 and decode(A_COPY_LOT_ATTRIBUTE_FLAG ,X_TRUE,nvl(cr.COPY_LOT_ATTRIBUTE_FLAG ,-1),nvl(msi.COPY_LOT_ATTRIBUTE_FLAG ,-1))= nvl(msi.COPY_LOT_ATTRIBUTE_FLAG,-1)
851 and decode(A_RECIPE_ENABLED_FLAG ,X_TRUE,nvl(cr.RECIPE_ENABLED_FLAG ,-1),nvl(msi.RECIPE_ENABLED_FLAG ,-1))= nvl(msi.RECIPE_ENABLED_FLAG,-1)
852 and decode(A_PROCESS_QUALITY_ENABLED_FLAG ,X_TRUE,nvl(cr.PROCESS_QUALITY_ENABLED_FLAG ,-1),nvl(msi.PROCESS_QUALITY_ENABLED_FLAG ,-1))= nvl(msi.PROCESS_QUALITY_ENABLED_FLAG,-1)
853 and decode(A_PROCESS_EXEC_ENABLED_FLAG ,X_TRUE,nvl(cr.PROCESS_EXECUTION_ENABLED_FLAG,-1),nvl(msi.PROCESS_EXECUTION_ENABLED_FLAG,-1))= nvl(msi.PROCESS_EXECUTION_ENABLED_FLAG,-1);
854 exception
855 when NO_DATA_FOUND then
856 dumm_status := INVPUOPI.mtl_log_interface_err(
857 cr.ORGID,
858 user_id,
859 login_id,
860 prog_appid,
861 prog_id,
862 request_id,
863 cr.TRANSACTION_ID,
864 error_msg,
865 'MASTER_CHILD_7I',
866 'MTL_SYSTEM_ITEMS_INTERFACE',
867 'INV_IOI_MASTER_CHILD_7I',
868 err_text);
869 If dumm_status < 0 then
870 raise LOGGING_ERR ;
871 End if;
872 update mtl_system_items_interface msii
873 set process_flag = 3
874 where msii.transaction_id = cr.transaction_id;
875
876 end; /* MASTER_CHILD_7I */
877
878
879 /* Start Bug 3713912 */
880 begin /* MASTER_CHILD_7J */
881
882 select inventory_item_id into msicount
883 from mtl_system_items msi
884 where msi.inventory_item_id = cr.III
885 and msi.organization_id = cr.MORGID
886 and decode(A_PROCESS_COSTING_ENABLED_FLAG ,X_TRUE,nvl(cr.PROCESS_COSTING_ENABLED_FLAG,-1),nvl(msi.PROCESS_COSTING_ENABLED_FLAG,-1))= nvl(msi.PROCESS_COSTING_ENABLED_FLAG,-1)
887 and decode(A_HAZARDOUS_MATERIAL_FLAG ,X_TRUE,nvl(cr.HAZARDOUS_MATERIAL_FLAG,-1),nvl(msi.HAZARDOUS_MATERIAL_FLAG,-1))= nvl(msi.HAZARDOUS_MATERIAL_FLAG,-1)
888 and decode(A_CAS_NUMBER ,X_TRUE,nvl(cr.CAS_NUMBER,-1),nvl(msi.CAS_NUMBER,-1))= nvl(msi.CAS_NUMBER,-1)
889 and decode(A_RETEST_INTERVAL ,X_TRUE,nvl(cr.RETEST_INTERVAL,-1),nvl(msi.RETEST_INTERVAL,-1))= nvl(msi.RETEST_INTERVAL,-1)
890 and decode(A_EXPIRATION_ACTION_INTERVAL ,X_TRUE,nvl(cr.EXPIRATION_ACTION_INTERVAL,-1),nvl(msi.EXPIRATION_ACTION_INTERVAL,-1))= nvl(msi.EXPIRATION_ACTION_INTERVAL,-1)
891 and decode(A_EXPIRATION_ACTION_CODE ,X_TRUE,nvl(cr.EXPIRATION_ACTION_CODE,-1),nvl(msi.EXPIRATION_ACTION_CODE,-1))= nvl(msi.EXPIRATION_ACTION_CODE,-1)
895 when NO_DATA_FOUND then
892 and decode(A_MATURITY_DAYS ,X_TRUE,nvl(cr.MATURITY_DAYS,-1),nvl(msi.MATURITY_DAYS,-1))= nvl(msi.MATURITY_DAYS,-1)
893 and decode(A_HOLD_DAYS ,X_TRUE,nvl(cr.HOLD_DAYS,-1),nvl(msi.HOLD_DAYS,-1))= nvl(msi.HOLD_DAYS,-1) ;
894 exception
896 dumm_status := INVPUOPI.mtl_log_interface_err(
897 cr.ORGID,
898 user_id,
899 login_id,
900 prog_appid,
901 prog_id,
902 request_id,
903 cr.TRANSACTION_ID,
904 error_msg,
905 'MASTER_CHILD_7J',
906 'MTL_SYSTEM_ITEMS_INTERFACE',
907 'INV_IOI_MASTER_CHILD_7J',
908 err_text);
909 If dumm_status < 0 then
910 raise LOGGING_ERR ;
911 End if;
912 update mtl_system_items_interface msii
913 set process_flag = 3
914 where msii.transaction_id = cr.transaction_id;
915
916 end; /* MASTER_CHILD_7J */
917 /* End Bug 3713912 */
918
919 end loop;
920
921 return(0);
922 exception
923 when LOGGING_ERR then
924 return(dumm_status);
925 when VALIDATE_ERR then
926 dumm_status := INVPUOPI.mtl_log_interface_err(
927 l_org_id,
928 user_id,
929 login_id,
930 prog_appid,
931 prog_id,
932 request_id,
933 trans_id,
934 err_text,
935 'MASTER_CHILD_7',
936 'MTL_SYSTEM_ITEMS_INTERFACE',
937 'BOM_OP_VALIDATION_ERR',
938 err_text);
939 return(status);
940 when OTHERS then
941 err_text := substr('INVPVALI.validate_item_org7' || SQLERRM ,1 , 240);
942 return(SQLCODE);
943
944 END validate_item_org7;
945
946
947 END INVPVLM3;