4 -- This procedure performs several queries to determine
1 PACKAGE BODY INVIDIT3 AS
2 /* $Header: INVIDI3B.pls 120.24.12020000.4 2012/07/26 06:14:04 yifwang ship $ */
3
5 -- if attributes are updateable. This only needs to be called
6 -- when working on an existing record.
7
8 PROCEDURE Table_Queries(
9 p_org_id IN NUMBER,
10 p_item_id IN NUMBER,
11 p_master_org IN NUMBER,
12 p_primary_uom_code IN VARCHAR2,
13 p_catalog_group_id IN NUMBER,
14 p_calling_routine IN VARCHAR2,
15 x_onhand_lot IN OUT NOCOPY NUMBER,
16 x_onhand_serial IN OUT NOCOPY NUMBER,
17 x_onhand_shelf IN OUT NOCOPY NUMBER,
18 x_onhand_rev IN OUT NOCOPY NUMBER,
19 x_onhand_loc IN OUT NOCOPY NUMBER,
20 x_onhand_all IN OUT NOCOPY NUMBER,
21 x_onhand_trackable IN OUT NOCOPY NUMBER,
22 x_wip_repetitive_item IN OUT NOCOPY NUMBER,
23 x_rsv_exists IN OUT NOCOPY NUMBER,
24 x_so_rsv IN OUT NOCOPY NUMBER,
25 x_so_ship IN OUT NOCOPY NUMBER,
26 x_so_txn IN OUT NOCOPY NUMBER,
27 x_demand_exists IN OUT NOCOPY NUMBER,
28 x_uom_conv IN OUT NOCOPY NUMBER,
29 x_comp_atp IN OUT NOCOPY NUMBER,
30 x_bom_exists IN OUT NOCOPY NUMBER,
31 x_cost_txn IN OUT NOCOPY NUMBER,
32 x_bom_item IN OUT NOCOPY NUMBER,
33 x_mrp_schedule IN OUT NOCOPY NUMBER,
34 x_null_elem_exists IN OUT NOCOPY NUMBER,
35 x_so_open_exists IN OUT NOCOPY NUMBER,
36 x_fte_vechicle_exists IN OUT NOCOPY NUMBER,
37 x_pendadj_lot IN OUT NOCOPY NUMBER,
38 x_pendadj_rev IN OUT NOCOPY NUMBER,
39 x_pendadj_loc IN OUT NOCOPY NUMBER,
40 x_so_ato IN OUT NOCOPY NUMBER,
41 x_vmiorconsign_enabled IN OUT NOCOPY NUMBER,
42 x_consign_enabled IN OUT NOCOPY NUMBER,
43 x_process_enabled IN OUT NOCOPY NUMBER,
44 x_onhand_tracking_qty_ind IN OUT NOCOPY NUMBER,
45 x_pendadj_tracking_qty_ind IN OUT NOCOPY NUMBER,
46 x_onhand_primary_uom IN OUT NOCOPY NUMBER,
47 x_pendadj_primary_uom IN OUT NOCOPY NUMBER,
48 x_onhand_secondary_uom IN OUT NOCOPY NUMBER,
49 x_pendadj_secondary_uom IN OUT NOCOPY NUMBER,
50 x_onhand_sec_default_ind IN OUT NOCOPY NUMBER,
51 x_pendadj_sec_default_ind IN OUT NOCOPY NUMBER,
52 x_onhand_deviation_high IN OUT NOCOPY NUMBER,
53 x_pendadj_deviation_high IN OUT NOCOPY NUMBER,
54 x_onhand_deviation_low IN OUT NOCOPY NUMBER,
55 x_pendadj_deviation_low IN OUT NOCOPY NUMBER,
56 x_onhand_child_lot IN OUT NOCOPY NUMBER,
57 x_pendadj_child_lot IN OUT NOCOPY NUMBER,
58 x_onhand_lot_divisible IN OUT NOCOPY NUMBER,
59 x_pendadj_lot_divisible IN OUT NOCOPY NUMBER,
60 x_onhand_grade IN OUT NOCOPY NUMBER,
61 x_pendadj_grade IN OUT NOCOPY NUMBER,
62 x_intr_ship_lot IN OUT NOCOPY NUMBER,
63 x_intr_ship_serial IN OUT NOCOPY NUMBER,
64 X_revision_control OUT NOCOPY number, -- Bug 6501149
65 X_stockable OUT NOCOPY number, -- Bug 6501149
66 X_lot_control OUT NOCOPY number, -- Bug 6501149
67 X_serial_control OUT NOCOPY number, -- Bug 6501149
68 X_open_shipment_lot IN OUT NOCOPY number, -- Bug 9043779
69 X_open_shipment_serial IN OUT NOCOPY number -- Bug 9043779
70 ) IS
71
72 -- Local variables
73
74 lot_onhand NUMBER := 0;
75 lot_txn_pending NUMBER := 0;
76 ser_onhand NUMBER := 0;
77 ser_txn_pending NUMBER := 0;
78 shelf_onhand NUMBER := 0;
79 shelf_txn_pending NUMBER := 0;
80 rev_onhand NUMBER := 0;
81 rev_txn_pending NUMBER := 0;
82 loc_onhand NUMBER := 0;
83 loc_txn_pending NUMBER := 0;
84 uom_conv NUMBER := 0;
85 uom_other_conv NUMBER := 0;
86 cost_moq NUMBER := 0;
87 cost_moq2 NUMBER := 0;
88 cost_tmp NUMBER := 0;
89 cost_mmt NUMBER := 0;
90 cost_mmt2 number := 0;
91 cost_tmp2 number := 0;
92 cost_cst NUMBER := 0;
93 cost_cst2 NUMBER := 0;
94 bom_row_exists NUMBER := 0;
95 bom_substitute NUMBER := 0;
96 bom_inventory NUMBER := 0;
97 onhand_org_count NUMBER := 0;
98 onhand_master_count NUMBER := 0;
99 material_org_count NUMBER := 0;
100 material_org_count_ls number := 0; --bug 7155924
101 material_master_count NUMBER := 0;
102 material_master_count_ls number := 0; --bug 7155924
103 lot_level NUMBER := 0;
104 rev_level NUMBER := 0;
105 loc_level NUMBER := 0;
106 shelf_level NUMBER := 0;
107 serial_level NUMBER := 0;
108 trackable_level NUMBER := 0;
109 attr_name VARCHAR2(50);
110 l_tab_exists NUMBER := 0;
111 onhand_tracking_qty_ind NUMBER := 0;
112 pendadj_tracking_qty_ind NUMBER := 0;
113 tracking_qty_ind_level NUMBER := 0;
114 onhand_primary_uom NUMBER := 0;
115 pendadj_primary_uom NUMBER := 0;
116 primary_uom_level NUMBER := 0;
117 onhand_secondary_uom NUMBER := 0;
118 pendadj_secondary_uom NUMBER := 0;
119 secondary_uom_level NUMBER := 0;
120 onhand_sec_default_ind NUMBER := 0;
121 pendadj_sec_default_ind NUMBER := 0;
122 sec_default_ind_level NUMBER := 0;
123 onhand_deviation_high NUMBER := 0;
124 pendadj_deviation_high NUMBER := 0;
125 onhand_deviation_low NUMBER := 0;
126 pendadj_deviation_low NUMBER := 0;
127 deviation_high_level NUMBER := 0;
128 deviation_low_level NUMBER := 0;
129 onhand_child_lot NUMBER := 0;
130 pendadj_child_lot NUMBER := 0;
131 child_lot_level NUMBER := 0;
132 onhand_lot_divisible NUMBER := 0;
133 pendadj_lot_divisible NUMBER := 0;
134 lot_divisible_level NUMBER := 0;
135 onhand_grade NUMBER := 0;
136 pendadj_grade NUMBER := 0;
137 grade_level NUMBER := 0;
138 lots_org_count NUMBER := 0;
139 lots_master_count NUMBER := 0;
140 l_intr_ship_org NUMBER := 0;
141 l_intr_ship_master NUMBER := 0;
142 l_attr_control_level NUMBER; -- Bug: 4139938
143 reservable_level NUMBER := 0;
144 ship_item_level NUMBER := 0;
145 transaction_level NUMBER := 0;
146 bom_enabled_level NUMBER := 0;
147 inv_asset_level NUMBER := 0;
148 cost_enabled_level NUMBER := 0;
149 /* Bug 6501149 */
150 stockable_level NUMBER := 0;
151 l_org NUMBER := 0;
152 l_master NUMBER := 0;
153
154 /* bug 9043779 */
155 open_shipment_org_count NUMBER := 0;
156 open_shipment_master_count NUMBER := 0;
157 /* end bug 9043779 */
158 -- bug 8406930
159 opm_enabled_org varchar2(1);
160 BEGIN
161
162 -- 6531911 : Removed the intialization of out variables.
163 -- Values for these will be passed from calling routines
164 -- If not NULL, we will query the downstream tables.
165 -- Intialise the variable only when called from Item form.
166 -- Changes are too huge to make in certification phase.
167 IF p_calling_routine = 'INVIDITM' THEN
168 x_onhand_lot := 0;
169 x_onhand_serial := 0;
170 x_onhand_shelf := 0;
171 x_onhand_rev := 0;
172 x_onhand_loc := 0;
173 x_onhand_all := 0;
174 x_onhand_trackable := 0;
175 x_wip_repetitive_item := 0;
176 x_rsv_exists := 0;
177 x_so_rsv := 0;
178 x_so_ship := 0;
179 x_so_txn := 0;
180 x_demand_exists := 0;
181 x_uom_conv := 0;
182 x_comp_atp := 0;
183 x_bom_exists := 0;
184 x_cost_txn := 0;
185 x_bom_item := 0;
186 x_mrp_schedule := 0;
187 x_null_elem_exists := 0;
188 x_so_open_exists := 0;
189 x_fte_vechicle_exists := 0;
190 x_pendadj_lot := 0;
191 x_pendadj_rev := 0;
192 x_pendadj_loc := 0;
193 x_so_ato := 0;
194 x_vmiorconsign_enabled := 0;
195 x_consign_enabled := 0;
196 x_process_enabled := 0;
197 x_onhand_tracking_qty_ind := 0;
198 x_pendadj_tracking_qty_ind := 0;
199 x_onhand_primary_uom := 0;
200 x_pendadj_primary_uom := 0;
201 x_onhand_secondary_uom := 0;
202 x_pendadj_secondary_uom := 0;
203 x_onhand_sec_default_ind := 0;
204 x_pendadj_sec_default_ind := 0;
205 x_onhand_deviation_high := 0;
206 x_pendadj_deviation_high := 0;
207 x_onhand_deviation_low := 0;
208 x_pendadj_deviation_low := 0;
209 x_onhand_child_lot := 0;
210 x_pendadj_child_lot := 0;
211 x_onhand_lot_divisible := 0;
212 x_pendadj_lot_divisible := 0;
213 x_onhand_grade := 0;
214 x_pendadj_grade := 0;
215 x_intr_ship_lot := 0;
216 x_intr_ship_serial := 0;
217 /* Bug 6501149 */
218 X_revision_control := 0;
219 X_stockable := 0;
220 X_lot_control := 0;
221 X_serial_control := 0;
222 X_open_shipment_lot := 0; -- bug 9043779
223 X_open_shipment_serial := 0; -- bug 9043779
224 END IF;
225
226 --6531911 : Removed multiple select stmts into one cursor stmt
227 FOR cur IN (SELECT control_level, attribute_name
228 FROM mtl_item_attributes
229 WHERE attribute_name IN ('MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE'
230 ,'MTL_SYSTEM_ITEMS.SHELF_LIFE_CODE'
231 ,'MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE'
232 ,'MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE'
233 ,'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE'
234 ,'MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG'
235 ,'MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND'
236 ,'MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE'
237 ,'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE'
238 ,'MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND'
239 ,'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH'
240 ,'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW'
241 ,'MTL_SYSTEM_ITEMS.CHILD_LOT_FLAG'
242 ,'MTL_SYSTEM_ITEMS.LOT_DIVISIBLE_FLAG'
243 ,'MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG'
244 ,'MTL_SYSTEM_ITEMS.RESERVABLE_TYPE'
245 ,'MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG'
246 ,'MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG'
247 ,'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
248 ,'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG')) -- bug 6501149
249 LOOP
250 IF cur.attribute_name = 'MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE' THEN
251 lot_level := cur.control_level;
252 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SHELF_LIFE_CODE' THEN
253 shelf_level := cur.control_level;
254 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE' THEN
255 serial_level := cur.control_level;
256 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE' THEN
257 rev_level := cur.control_level;
258 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE' THEN
259 loc_level := cur.control_level;
260 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG' THEN
261 trackable_level := cur.control_level;
262 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND' THEN
263 tracking_qty_ind_level := cur.control_level;
264 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE' THEN
265 primary_uom_level := cur.control_level;
266 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE' THEN
267 secondary_uom_level:= cur.control_level;
268 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND' THEN
269 sec_default_ind_level := cur.control_level;
270 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH' THEN
271 deviation_high_level := cur.control_level;
272 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW' THEN
273 deviation_low_level:= cur.control_level;
274 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.CHILD_LOT_FLAG' THEN
275 child_lot_level := cur.control_level;
276 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.LOT_DIVISIBLE_FLAG' THEN
277 lot_divisible_level:= cur.control_level;
278 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG' THEN
279 grade_level := cur.control_level;
280 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.RESERVABLE_TYPE' THEN
281 reservable_level := cur.control_level;
282 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG' THEN
283 ship_item_level := cur.control_level;
284 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG' THEN
285 transaction_level := cur.control_level;
286 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG' THEN
287 bom_enabled_level := cur.control_level;
288 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG' THEN
289 inv_asset_level := cur.control_level;
290 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.COSTING_ENABLED_FLAG' THEN
291 cost_enabled_level := cur.control_level;
292 ELSIF cur.attribute_name = 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG' THEN -- bug 6501149
293 stockable_level := cur.control_level;
294 END IF;
295 END LOOP;
296
297 IF x_onhand_lot IS NOT NULL
298 OR x_onhand_shelf IS NOT NULL
299 OR x_onhand_serial IS NOT NULL
300 OR x_onhand_rev IS NOT NULL
301 OR x_onhand_loc IS NOT NULL
302 OR x_onhand_trackable IS NOT NULL
303 OR x_onhand_tracking_qty_ind IS NOT NULL
304 OR x_onhand_primary_uom IS NOT NULL
305 OR x_onhand_secondary_uom IS NOT NULL
306 OR x_onhand_sec_default_ind IS NOT NULL
307 OR x_onhand_deviation_high IS NOT NULL
308 OR x_onhand_deviation_low IS NOT NULL
309 OR x_onhand_child_lot IS NOT NULL
310 OR x_onhand_lot_divisible IS NOT NULL
311 OR x_onhand_grade IS NOT NULL
312 THEN
313
314 SELECT COUNT(1) INTO onhand_org_count
315 FROM mtl_onhand_quantities_detail -- Bug:2687570
316 WHERE inventory_item_id = p_item_id
317 AND organization_id = p_org_id
318 AND ROWNUM = 1;
319
320 IF (onhand_org_count <> 1) THEN
321 SELECT COUNT(1) INTO onhand_master_count
322 FROM mtl_onhand_quantities_detail -- Bug:2687570
323 WHERE inventory_item_id = p_item_id
324 AND (organization_id IN (SELECT organization_id
325 FROM mtl_parameters
326 WHERE master_organization_id = p_master_org))
327 AND ROWNUM = 1;
328
329 SELECT count(1) INTO material_org_count
330 FROM mtl_material_transactions_temp
331 WHERE inventory_item_id = p_item_id
332 AND organization_id = p_org_id
333 AND rownum = 1;
334
335 --bug 7355994(7356679,7356680): setting lot-serial flag to be 1 in case
336 --rows exist in mtl_material_transactions_temp
337 IF (material_org_count = 1) THEN
338 material_org_count_ls := 1;
339 END IF;
340
341 IF (material_org_count <> 1) THEN
342 SELECT count(1) INTO material_org_count
343 FROM mtl_supply
344 WHERE item_id = p_item_id
345 AND (from_organization_id = p_org_id OR to_organization_id = p_org_id)
346 AND rownum = 1;
347
348 --bug 7155924(7327865,7327866): for lot and serial control attributes
349 --we need to check for records in MTL_Supply with supply_type_code
350 --in Receiving and Shipment only
351 SELECT count(1) INTO material_org_count_ls
352 FROM mtl_supply
353 WHERE item_id = p_item_id
354 AND (from_organization_id = p_org_id
355 OR to_organization_id = p_org_id)
356 and supply_type_code in ('RECEIVING', 'SHIPMENT')
357 AND rownum = 1;
358 END IF;
359
360 IF (material_org_count <> 1) THEN
361 SELECT COUNT(1) INTO material_org_count
362 FROM mtl_demand
363 WHERE inventory_item_id = p_item_id
364 AND organization_id = p_org_id
365 AND rownum = 1;
366
367 --bug 7155924(7327865,7327866): setting lot-serial flag to be 1 in case
368 --rows exist in mtl_demand
369 IF(material_org_count = 1) THEN
370 material_org_count_ls := 1;
371 END IF;
372 END IF;
373
374 IF (material_org_count <> 1) THEN
375 SELECT COUNT(1) INTO material_master_count
376 FROM mtl_material_transactions_temp
377 WHERE inventory_item_id = p_item_id
378 AND (organization_id IN (SELECT organization_id
379 FROM mtl_parameters
380 WHERE master_organization_id = p_master_org))
381 AND rownum = 1;
382
383 --bug 7355994(7356679,7356680): setting lot-serial flag to be 1 in
384 --case rows exist in mtl_material_transactions_temp
385 IF(material_master_count = 1) THEN
386 material_master_count_ls := 1;
387 END IF;
388
389
390 --3713912 :check for pending transactions such as approved PO
391 IF (material_master_count <> 1) THEN
392 SELECT COUNT(1) INTO material_master_count
393 FROM mtl_supply ms
394 WHERE ms.item_id = p_item_id
395 AND EXISTS (SELECT 1
396 FROM mtl_parameters
397 WHERE master_organization_id = p_master_org
398 AND (organization_id = ms.from_organization_id OR organization_id = ms.to_organization_id))
399 AND rownum = 1;
400
401 --bug 7155924(7327865,7327866): for lot and serial control attributes
402 --we need to check for records in MTL_Supply with supply_type_code
403 --in Receiving and Shipment only
404 SELECT COUNT(1)
405 INTO material_master_count_ls
406 FROM mtl_supply ms
407 WHERE ms.item_id = p_item_id
408 AND EXISTS (SELECT 1
409 FROM mtl_parameters
410 WHERE master_organization_id = p_master_org
411 AND (organization_id = ms.from_organization_id
412 OR organization_id = ms .to_organization_id))
413 AND supply_type_code in ('RECEIVING', 'SHIPMENT')
414 AND rownum = 1;
415 END IF;
416
417 --Check for pending transactions such as a booked order
418 IF (material_master_count <> 1) THEN
419 SELECT COUNT(1) INTO material_master_count
420 FROM mtl_demand md
421 WHERE md.inventory_item_id = p_item_id
422 AND EXISTS (SELECT 1
423 FROM mtl_parameters
424 WHERE master_organization_id = p_master_org
425 AND organization_id = md.organization_id)
426 AND rownum = 1;
427
428 --bug 7155924(7327865,7327866): setting lot-serial flag
429 --to be 1 in case rows exist in mtl_demand
430 IF (material_master_count = 1) THEN
431 material_master_count_ls := 1;
432 END IF;
433
434 END IF;
435 END IF;
436 END IF;
437
438 IF ( onhand_org_count = 1 or material_org_count = 1) THEN
439 --7155924,7355994
440 IF (onhand_org_count = 1 or material_org_count_ls = 1) THEN
441 X_onhand_lot := 1;
442 X_onhand_serial := 1;
443 X_onhand_shelf := 1; /* Bug 8521729. Shelf Life is also handled as same as Lot control. */
444 END IF;
445
446 --x_onhand_shelf := 1;
447 x_onhand_rev := 1;
448 x_onhand_loc := 1;
449 x_onhand_trackable := 1;
450 x_onhand_tracking_qty_ind := 1;
451 x_onhand_primary_uom := 1;
452 x_onhand_secondary_uom := 1;
453 x_onhand_sec_default_ind := 1;
454 x_onhand_deviation_high := 1;
455 x_onhand_deviation_low := 1;
456 x_onhand_child_lot := 1;
457 x_onhand_lot_divisible := 1;
458 x_onhand_grade := 1;
459 X_stockable := 1; /*Bug 9094398*/
460
461 ELSIF ( onhand_master_count = 1 or material_master_count = 1) THEN
462 --bug_7155924_7355994
463 if (lot_level = 1
464 and (onhand_master_count = 1 or material_master_count_ls = 1))
465 then
466 x_onhand_lot := 1;
467 end if;
468
469 /* Bug 8521729 Shelf life is also handled similar to lot control */
470 if (shelf_level = 1 and (onhand_master_count = 1 or material_master_count_ls = 1)) then
471 x_onhand_shelf := 1;
472 end if;
473
474 --bug_7155924_7355994
475 if (serial_level = 1
476 and (onhand_master_count = 1 or material_master_count_ls = 1))
477 then
478 x_onhand_serial := 1;
479 end if;
480
481 if (rev_level = 1) then
482 x_onhand_rev := 1;
483 end if;
484 if (loc_level = 1) then
485 x_onhand_loc := 1;
486 end if;
487 if ( trackable_level = 1 ) then
488 x_onhand_trackable := 1;
489 end if;
490 if (tracking_qty_ind_level = 1) then
491 x_onhand_tracking_qty_ind := 1;
492 end if;
493 if (primary_uom_level = 1) then
494 x_onhand_primary_uom := 1;
495 end if;
496 if (secondary_uom_level = 1) then
497 x_onhand_secondary_uom:= 1;
498 end if;
499 if (sec_default_ind_level = 1) then
500 x_onhand_sec_default_ind := 1;
501 end if;
502 if (deviation_high_level = 1) then
503 x_onhand_deviation_high := 1;
504 end if;
505 if (deviation_low_level = 1) then
506 x_onhand_deviation_low := 1;
507 end if;
508 if (child_lot_level = 1) then
509 x_onhand_child_lot := 1;
510 end if;
511 if (lot_divisible_level = 1) then
512 x_onhand_lot_divisible := 1;
513 end if;
514 if (grade_level = 1) then
515 x_onhand_grade := 1;
516 end if;
517 /*Added for Bug 9094398*/
518 if (stockable_level = 1) then
519 X_stockable := 1;
520 end if;
521 /*End of comment*/
522
523 end if;
524 --Start Bug 3713912 we have to check if lots have been defined.
525 --If lots are defined the all lot dependent fields are non updateable.
526
527 /* start bug 8406930 */
528 select process_enabled_flag
529 into opm_enabled_org
530 from mtl_parameters
531 where organization_id = p_org_id;
532
533 if opm_enabled_org ='Y' then -- to check the OPM_ENABLED_ORGS
534
535 select count(1)
536 into lots_org_count
537 from mtl_lot_numbers
538 where inventory_item_id = p_item_id and
539 organization_id = p_org_id and
540 rownum = 1;
541
542 if (lots_org_count <> 1) then
543 select count(1)
544 into lots_master_count
545 from mtl_lot_numbers mln
546 where inventory_item_id = p_item_id
547 and exists (select 1
548 from mtl_parameters
549 where master_organization_id = p_master_org
550 and organization_id = mln.organization_id)
551 and rownum = 1;
552
553 end if;
554
555 if ( lots_org_count = 1 ) then
556 x_onhand_lot := 1;
557 x_onhand_child_lot := 1;
558 x_onhand_lot_divisible := 1;
559 x_onhand_grade := 1;
560 x_onhand_shelf := 1;
561 elsif ( lots_master_count = 1) then
562 if (lot_level = 1) then
563 x_onhand_lot := 1;
564 end if;
565 if (child_lot_level = 1) then
566 x_onhand_child_lot := 1;
567 end if;
568 if (lot_divisible_level = 1) then
569 x_onhand_lot_divisible := 1;
570 end if;
571 if (grade_level = 1) then
572 x_onhand_grade := 1;
573 end if;
574 if (shelf_level = 1) then
575 x_onhand_shelf := 1;
576 end if;
577 end if;
578 END IF;
579 --end if; -- bug 8406930 (If opm_enabled_org ='Y')
580 END IF;
581
582 IF x_intr_ship_lot IS NOT NULL
583 OR x_intr_ship_serial IS NOT NULL THEN
584 -- Start bug 4387538
585 if (lot_level = 2 or serial_level = 2) then
586 select count(1)
587 into l_intr_ship_org
588 from mtl_supply
589 where supply_type_code = 'SHIPMENT'
590 and item_id = p_item_id
591 and to_organization_id = p_org_id
592 and from_organization_id is not null
593 and po_line_location_id is null
594 and rownum = 1;
595 end if;
596
597 if (lot_level = 1 or serial_level = 1) then
598 select count(1)
599 into l_intr_ship_master
600 from mtl_supply
601 where supply_type_code = 'SHIPMENT'
602 and item_id = p_item_id
603 and to_organization_id in
604 (select organization_id
605 from mtl_parameters
606 where master_organization_id = p_master_org)
607 and from_organization_id is not null
608 and po_line_location_id is null
609 and rownum = 1;
610
611 end if;
612 if (l_intr_ship_org = 1 ) then
613 x_intr_ship_lot := 1;
614 x_intr_ship_serial := 1;
615 elsif (l_intr_ship_master = 1) then
616 if (lot_level = 1) then
617 x_intr_ship_lot := 1;
618 end if;
619 if (serial_level = 1) then
620 x_intr_ship_serial := 1;
621 end if;
622 end if;
623 -- End bug 4387538
624 END IF;
625
626 /*Bug 6501149 Code changes Start
627 Updating the Values of the attributes to 1 if there are Open Deliver transactions.
628 The below query returns rows if Open Deliver transactions present */
629 if (rev_level = 2 or stockable_level = 2 or lot_level = 2 or serial_level = 2) then
630
631 select count(1)
632 into l_org
633 from mtl_supply
634 where supply_type_code in ('RECEIVING', 'SHIPMENT')
635 and item_id = p_item_id
636 and to_organization_id =p_org_id
637 and rownum =1 ;
638
639 end if;
640
641 if (rev_level = 1 or stockable_level = 1 or lot_level = 1 or serial_level = 1 ) then
642
643 select count(1)
644 into l_master
645 from mtl_supply
646 where supply_type_code in ('RECEIVING', 'SHIPMENT')
647 and item_id = p_item_id
648 and to_organization_id in
649 (select organization_id
650 from mtl_parameters
651 where master_organization_id = p_master_org)
652 and rownum = 1;
653
654 end if;
655
656 if (l_org = 1 ) then
657
658 X_revision_control := 1;
659 X_stockable := 1;
660 X_lot_control := 1;
661 X_serial_control := 1;
662 elsif (l_master = 1) then
663
664 if (rev_level = 1) then
665 X_revision_control := 1;
666 end if;
667 if (stockable_level = 1) then
668 X_stockable := 1;
669 end if;
670 if(lot_level = 1) then
671 X_lot_control := 1;
672 end if;
673 if (serial_level = 1) then
674 X_serial_control := 1;
675 end if;
676 end if;
677 /*Bug 6501149 Code changes ends */
678
679 --Following code added for Bug 3058650
680 if (x_onhand_rev <> 1) then
681 if (inv_attribute_control_pvt.check_pending_adjustments
682 (p_org_id => p_org_id,
683 p_item_id => p_item_id,
684 p_source_item => 'REVISION_QTY_CONTROL_CODE')) then
685 x_pendadj_rev := 1;
686 end if ;
687 end if ;
688 if (x_onhand_lot <> 1) then
689 if (inv_attribute_control_pvt.check_pending_adjustments
690 (p_org_id => p_org_id,
691 p_item_id => p_item_id,
692 p_source_item => 'LOT_CONTROL_CODE')) then
693 x_pendadj_lot := 1;
694 end if;
695 end if;
696 if (x_onhand_loc <> 1) then
697 if (inv_attribute_control_pvt.check_pending_adjustments
698 (p_org_id => p_org_id,
699 p_item_id => p_item_id,
700 p_source_item => 'LOCATION_CONTROL_CODE')) then
701 x_pendadj_loc := 1;
702 end if;
703 end if;
704 if (INV_ATTRIBUTE_CONTROL_PVT.ato_uncheck(p_org_id => p_org_id,
705 p_item_id => p_item_id)) then
706 x_so_ato := 1;
707 end if;
708
709 -- Start Bug 3713912
710 if (x_onhand_tracking_qty_ind <> 1) then
711 if (inv_attribute_control_pvt.check_pending_adjustments
712 (p_org_id => p_org_id,
713 p_item_id => p_item_id,
714 p_source_item => 'TRACKING_QTY_IND')) then
715 x_pendadj_tracking_qty_ind := 1;
716 end if;
717 end if;
718 if (x_onhand_primary_uom <> 1) then
719 if (inv_attribute_control_pvt.check_pending_adjustments
720 (p_org_id => p_org_id,
721 p_item_id => p_item_id,
722 p_source_item => 'PRIMARY_UOM_CODE')) then
723 x_pendadj_primary_uom := 1;
724 end if;
725 end if;
726 if (x_onhand_secondary_uom <> 1) then
727 if (inv_attribute_control_pvt.check_pending_adjustments
728 (p_org_id => p_org_id,
729 p_item_id => p_item_id,
730 p_source_item => 'SECONDARY_UOM_CODE')) then
731 x_pendadj_secondary_uom := 1;
732 end if;
733 end if;
734 if (x_onhand_sec_default_ind <> 1) then
735 if (inv_attribute_control_pvt.check_pending_adjustments
736 (p_org_id => p_org_id,
737 p_item_id => p_item_id,
738 p_source_item => 'SECONDARY_DEFAULT_IND')) then
739 x_pendadj_sec_default_ind := 1;
740 end if;
741 end if;
742 if (x_onhand_deviation_high <> 1) then
743 if (inv_attribute_control_pvt.check_pending_adjustments
744 (p_org_id => p_org_id,
745 p_item_id => p_item_id,
746 p_source_item => 'DUAL_UOM_DEVIATION_HIGH')) then
747 x_pendadj_deviation_high := 1;
748 end if;
749 end if;
750 if (x_onhand_deviation_low <> 1) then
751 if (inv_attribute_control_pvt.check_pending_adjustments
752 (p_org_id => p_org_id,
753 p_item_id => p_item_id,
754 p_source_item => 'DUAL_UOM_DEVIATION_LOW')) then
755 x_pendadj_deviation_low := 1;
756 end if;
757 end if;
758 if (x_onhand_child_lot <> 1) then
759 if (inv_attribute_control_pvt.check_pending_adjustments
760 (p_org_id => p_org_id,
761 p_item_id => p_item_id,
762 p_source_item => 'CHILD_LOT_FLAG')) then
763 X_pendadj_child_lot := 1;
764 end if;
765 end if;
766 if (x_onhand_lot_divisible <> 1) then
767 if (inv_attribute_control_pvt.check_pending_adjustments
768 (p_org_id => p_org_id,
769 p_item_id => p_item_id,
770 p_source_item => 'LOT_DIVISIBLE_FLAG')) then
771 x_pendadj_lot_divisible := 1;
772 end if;
773 end if;
774 if (x_onhand_grade <> 1) then
775 if (inv_attribute_control_pvt.check_pending_adjustments
776 (p_org_id => p_org_id,
777 p_item_id => p_item_id,
778 p_source_item => 'GRADE_CONTROL_FLAG')) then
779 x_pendadj_grade := 1;
780 end if;
781 end if;
782 --End Bug 3713912
783
784 -- Check if on-hand quantity for the item exists in master and all child orgs.
785 if x_onhand_all IS NOT NULL THEN
786 select count(*) into X_onhand_all
787 from dual
788 where exists ( select 'x'
789 from mtl_onhand_quantities_detail moh -- Bug:2687570
790 where moh.inventory_item_id = p_item_id
791 and moh.organization_id in ( select mp.organization_id
792 from mtl_parameters mp
793 where mp.master_organization_id = p_master_org));
794 end if;
795
796 if X_wip_repetitive_item IS NOT NULL THEN
797 select count(*) into X_wip_repetitive_item
798 from dual
799 where exists ( select 'x'
800 from WIP_REPETITIVE_ITEMS wri
801 where wri.PRIMARY_ITEM_ID = p_item_id
802 and wri.ORGANIZATION_ID in ( select mp.organization_id
803 from mtl_parameters mp
804 where mp.master_organization_id = p_master_org));
805 end if;
806
807 IF x_rsv_exists IS NOT NULL THEN
808 if (reservable_level = 1) then
809 select count(1) into x_rsv_exists
810 from mtl_reservations res,
811 mtl_parameters param
812 where res.inventory_item_id = p_item_id
813 AND res.organization_id = param.organization_id
814 and param.master_organization_id = p_master_org
815 and reservation_quantity > 0
816 and rownum = 1 ;
817 else
818 select count(1) into X_rsv_exists
819 from mtl_reservations
820 where organization_id = p_org_id
821 and inventory_item_id = p_item_id
822 and reservation_quantity > 0
823 and rownum = 1 ;
824 end if ;
825 end if ;
826
827 x_so_rsv := x_rsv_exists; --- Bug 1923215
828 attr_name := 'MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG';
829
830 -- bug 9043779, Check if the shipping org has open shipment or supply for internal requistion or inventory REQ
831 IF (X_open_shipment_lot IS NOT NULL or X_open_shipment_serial IS NOT NULL ) THEN
832
833 select count(1)
834 into open_shipment_org_count
835 from rcv_shipment_headers rsh, rcv_shipment_lines rsl
836 where rsh.shipment_header_id = rsl.shipment_header_id
837 and rsh.receipt_source_code IN ('INTERNAL ORDER', 'INVENTORY')
838 and exists (select 1
839 from mtl_supply ms
840 where ms.shipment_header_id = rsh.shipment_header_id
841 and ms.item_id = rsl.item_id -- bug 13538019
842 and ms.shipment_line_id = rsl.shipment_line_id
843 and ms.supply_type_code in ('SHIPMENT', 'RECEIVING'))
844 and rsl.item_id = p_item_id
845 and rsl.from_organization_id = p_org_id
846 and rownum = 1;
847
848 if (open_shipment_org_count <> 1) then
849 select count(1)
850 into open_shipment_master_count
851 from rcv_shipment_headers rsh, rcv_shipment_lines rsl
852 where rsh.shipment_header_id = rsl.shipment_header_id
853 and rsh.receipt_source_code IN ('INTERNAL ORDER', 'INVENTORY')
854 and exists (select 1
855 from mtl_supply ms
856 where ms.shipment_header_id = rsh.shipment_header_id
857 and ms.item_id = rsl.item_id -- bug 13538019
858 and ms.shipment_line_id = rsl.shipment_line_id
859 and ms.supply_type_code in ('SHIPMENT', 'RECEIVING'))
860 and rsl.item_id = p_item_id
861 and rsl.from_organization_id IN (SELECT organization_id
862 FROM mtl_parameters
863 WHERE master_organization_id = p_master_org)
864 and rownum = 1;
865 end if;
866
867 if (open_shipment_org_count = 1) then
868 X_open_shipment_lot := 1;
869 X_open_shipment_serial := 1;
870 elsif (lot_level = 1 and open_shipment_master_count = 1) then
871 X_open_shipment_lot := 1;
872 elsif (serial_level = 1 and open_shipment_master_count = 1) then
873 X_open_shipment_serial := 1;
874 end if;
875 end if;
876 -- end bug 9043779
877
878 -- Bug 4139938 - for improving performance, breaking statement into parts
879 -- logic of the complete SQL remains same.
880 IF X_so_ship IS NOT NULL THEN
881
882 declare /*Changes made for perf issue for bug 7567261*/
883 shipping_level NUMBER :=0;
884 begin
885 select count(1) into shipping_level
886 from mtl_item_attributes
887 where control_level = 1
888 and attribute_name=attr_name;
889
890 if (shipping_level = 0) then
891 select count(1) into X_so_ship -- bug 10405137
892 from oe_order_lines_all l
893 where l.inventory_item_id = p_item_id
894 and l.open_flag = 'Y'
895 and nvl(l.shipping_interfaced_flag,'N') = 'N'
896 and l.ship_from_org_id = p_org_id
897 and rownum = 1;
898 else
899 select count(1) into X_so_ship -- bug 10405137
900 from oe_order_lines_all l
901 where l.inventory_item_id = p_item_id
902 and l.open_flag = 'Y'
903 and nvl(l.shipping_interfaced_flag,'N') = 'N'
904 and l.ship_from_org_id in
905 (select organization_id
906 from mtl_parameters
907 where master_organization_id = p_master_org
908 )
909 and rownum = 1;
910 end if;
911 -- Bug 12669090: Start
912 IF (X_so_ship = 0) THEN
913 IF (shipping_level = 0) THEN
914 select count(1) into X_so_ship -- bug 10405137
915
916 from wsh_delivery_details wdd
917 where wdd.inventory_item_id = p_item_id
918 and wdd.inv_interfaced_flag in ('N','P')
919 -- Bug 3963689 Condition added so that if no sales order and on hand qty 0
920 --then shippable flag of the item can be modified - Anmurali
921 and wdd.released_status <> 'D'
922 and wdd.source_code = 'OE'
923 and wdd.organization_id = p_org_id
924 and rownum = 1;
925 ELSE
926 select count(1) into X_so_ship -- bug 10405137
927 from wsh_delivery_details wdd
928 where wdd.inventory_item_id = p_item_id
929 and wdd.inv_interfaced_flag in ('N','P')
930 -- Bug 3963689 Condition added so that if no sales order and on hand qty 0
931 --then shippable flag of the item can be modified - Anmurali
932 and wdd.released_status <> 'D'
933 and wdd.source_code = 'OE'
934 and wdd.organization_id in (select organization_id
935 from mtl_parameters
936 where master_organization_id = p_master_org)
937 and rownum = 1;
938 END IF;
939 END IF;
940 end;
941 END IF;
942 -- Bug 4139938 end
943
944 /* Bug 1923215
945 Modified the below SQL to use the oe_order_lines_all Table
946 as so_lines_all and so_line_details are obsoleted in R11i
947 */
948 -- Check for open sales order line with a different value for
949 -- so_transactions_flag than in mtl_system_items
950 -- If controlled at Item level, check child orgs too
951 -- Used for validation on so_transactions_flag
952 -- X_so_txn will have either 0 or 1
953 attr_name := 'MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG';
954 IF X_so_txn IS NOT NULL THEN
955 select count(1)
956 into X_so_txn
957 from oe_order_lines_all l
958 where l.inventory_item_id = p_item_id
959 and l.open_flag = 'Y' -- Bug 8435071
960 and (l.ship_from_org_id in
961 (select organization_id
962 from mtl_parameters
963 where master_organization_id= p_master_org
964 and 1=transaction_level)
965 or l.ship_from_org_id= p_org_id)
966 and rownum = 1;
967 END IF;
968
969 -- bug 10405137, comment it as it wouldn't be executed
970 /* if (SQL%NOTFOUND) then
971 Raise NO_DATA_FOUND;
972 end if;*/
973
974 -- Check for open sales order line for the item
975 IF X_so_open_exists IS NOT NULL THEN
976 select count(*) into X_so_open_exists from dual
977 where exists
978 ( select * from oe_order_lines_all
979 where inventory_item_id = p_item_id
980 and open_flag = 'Y' -- Bug 8435071
981 );
982 END IF;
983
984 -- Check for reservations + open demand
985 -- If controlled at Item level, check child orgs too
986 -- Used to determine if so_transactions_flag is updateable
987 -- X_demand_exists will have either 0 or 1
988
989 /* Bug 1923215
990 Commenting the following Code
991 Confirmed with OM Team that they are not using so_transactions Flag
992 to control the Open Demand.
993 */
994
995 /*
996 select count(1)
997 into X_demand_exists
998 from oe_order_lines_all
999 where inventory_item_id = p_item_id
1000 and visible_demand_flag = 'Y'
1001 and shipped_quantity is NOT null
1002 and (ship_from_org_id in
1003 (select organization_id
1004 from mtl_parameters
1005 where master_organization_id = p_master_org
1006 and 1=(select control_level
1007 from mtl_item_attributes
1008 where
1009 attribute_name='MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG'
1010 )
1011 )
1012 or ship_from_org_id = p_org_id)
1013 and rownum = 1;
1014
1015 if (SQL%NOTFOUND) then
1016 Raise NO_DATA_FOUND;
1017 end if;
1018
1019 */
1020
1021
1022 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1023 -- First check if a conversion exists for this item's primary uom
1024 -- or if the item's primary uom is base unit of measure.
1025 -- Check if uom conversions exist for this item (either item-specific
1026 -- or inter-class conversions)
1027 -- Used to determine if allowed_units_lookup_code is updateable
1028 -- Use local variables for each sql statement, return 1 value to form
1029 IF X_uom_conv IS NOT NULL THEN
1030 select count(*)
1031 into uom_conv
1032 from dual
1033 where exists
1034 ( select 'x'
1035 from mtl_uom_conversions
1036 where inventory_item_id = p_item_id
1037 and uom_code = p_primary_uom_code
1038 );
1039
1040 if (uom_conv = 0) then
1041
1042 select decode(base_uom_flag, 'Y', 1, 0)
1043 into uom_conv
1044 from mtl_units_of_measure_vl
1045 where uom_code = p_primary_uom_code;
1046
1047 end if;
1048
1049 if (uom_conv = 0) then
1050
1051 select count(1)
1052 into uom_other_conv
1053 from sys.dual
1054 where exists
1055 (select 'x' from mtl_uom_conversions
1056 where inventory_item_id = p_item_id)
1057 or exists
1058 (select 'x' from mtl_uom_class_conversions
1059 where inventory_item_id = p_item_id);
1060
1061 end if;
1062
1063 -- Only need to know if there is no conversion for primary uom but
1064 -- there are other conversions - return 1 in this case.
1065 --
1066 if (uom_conv = 0 and uom_other_conv = 1) then
1067 X_uom_conv := 1;
1068 end if;
1069 END IF;
1070
1071 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1072
1073 -- Check if the item is a component with Check ATP = 1 (yes) in
1074 -- bom_inventory_components.
1075 -- If controlled at Item level, check in child orgs too
1076 -- Used to determine if atp_flag is updateable
1077 -- X_comp_atp will have either 0 or 1
1078
1079 --3432253 atp check nolonger required...please refer to below mentioned bugs.
1080 --The below query is causing a performance problem and query result is not used.
1081 --Bug 1457730 : we do not need this validation after bugfix 1123857 in BOM
1082 /*
1083 select LEADING(BIC) INDEX(BIC BOM_INVENTORY_COMPONENTS_N1) USE_NL(BIC BOM)
1084 count(1)
1085 into X_comp_atp
1086 from bom_inventory_components bic, bom_bill_of_materials bom
1087 where bic.bill_sequence_id = bom.common_bill_sequence_id
1088 and bic.component_item_id = p_item_id
1089 and bic.check_atp = 1
1090 and (bom.organization_id in
1091 (select organization_id
1092 from mtl_parameters
1093 where master_organization_id = p_master_org
1094 and 1 = (select control_level
1095 from mtl_item_attributes
1096 where attribute_name= 'MTL_SYSTEM_ITEMS.ATP_FLAG')
1097 )
1098 or bom.organization_id = p_org_id)
1099 and rownum = 1;
1100
1101
1102 if (SQL%NOTFOUND) then
1103 Raise NO_DATA_FOUND;
1104 end if;
1105 */
1106 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1107
1108 -- Check if a BOM is defined in master or any child orgs
1109 -- Used to determine if bom_item_type is updateable
1110 -- X_bom_exists will have either 0 or 1
1111
1112 /*select count(*) into bom_row_exists from dual
1113 where exists
1114 ( select 'x' from bom_bill_of_materials bom
1115 where bom.assembly_item_id = p_item_id
1116 and bom.organization_id in
1117 ( select organization_id
1118 from mtl_parameters
1119 where master_organization_id = p_master_org
1120 )
1121 );*/
1122
1123 -- Added the bill existence check based on attribute control for BOM allowed
1124 -- as part of fix for bug#3451941.
1125 IF X_bom_exists IS NOT NULL THEN
1126 select count(*) into bom_row_exists from dual
1127 where exists
1128 ( select 'x' from bom_bill_of_materials bom
1129 where bom.assembly_item_id = p_item_id
1130 and bom.organization_id in
1131 ( select organization_id
1132 from mtl_parameters
1133 where master_organization_id = p_master_org
1134 and 1 = bom_enabled_level
1135 union all
1136 select organization_id
1137 from mtl_parameters
1138 where organization_id = p_org_id
1139 and 2 = bom_enabled_level
1140 )
1141 );
1142 END IF;
1143
1144 X_bom_exists := bom_row_exists;
1145
1146 -- Check if there are rows in bom_substitute_components
1147 -- bom_item_type is always controlled at Item level, so check all child orgs
1148 -- Used to determine if bom_item_type is updateable
1149 -- Local variable bom_substitute will have either 0 or 1
1150 IF X_bom_item IS NOT NULL THEN
1151 if (bom_row_exists <> 1) then
1152
1153 select count(1)
1154 into bom_substitute
1155 from bom_substitute_components sub,
1156 bom_inventory_components inv,
1157 bom_bill_of_materials bom
1158 where sub.substitute_component_id = p_item_id
1159 and sub.component_sequence_id = inv.component_sequence_id
1160 and inv.bill_sequence_id = bom.bill_sequence_id
1161 and bom.organization_id in
1162 (select organization_id
1163 from mtl_parameters
1164 where master_organization_id= p_master_org)
1165 and rownum = 1;
1166
1167 end if;
1168
1169 -- Check if there are rows in bom_inventory_components
1170 -- bom_item_type is always controlled at Item level, so check all child orgs
1171 -- Used to determine if bom_item_type is updateable
1172 -- Local variable bom_inventory will have either 0 or 1
1173
1174 if (bom_row_exists <> 1 and bom_substitute <> 1) then
1175
1176 select /*+ LEADING(INV) INDEX(INV BOM_INVENTORY_COMPONENTS_N1) USE_NL(INV BOM) */
1177 count(1)
1178 into bom_inventory
1179 from bom_inventory_components inv, bom_bill_of_materials bom
1180 where inv.component_item_id = p_item_id
1181 and inv.bill_sequence_id=bom.bill_sequence_id
1182 and exists (select count(1)
1183 from MTL_PARAMETERS
1184 WHERE MASTER_ORGANIZATION_ID= p_master_org AND BOM.ORGANIZATION_ID = ORGANIZATION_ID)
1185 and rownum = 1;
1186
1187 end if;
1188
1189 if (bom_row_exists = 1 or bom_substitute = 1 or bom_inventory = 1) then
1190 X_bom_item := 1;
1191 end if;
1192 END IF;
1193
1194 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1195 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1196 -- Check if there is onhand or transactions pending or uncosted
1197 -- transactions for this item in any orgs where the costing org
1198 -- is the current org
1199 -- Used to determine if inventory_asset_flag is updateable
1200 -- Use local variables for each sql statement, return 1 variable to form
1201 -- X_cost_txn will have either 0 or 1 or 2
1202 /* Bug 14334921 START
1203 We need to perform the below checks on the tables
1204 mtl_onhand_quantities_detail
1205 mtl_material_transactions_temp
1206 mtl_material_transactions
1207 while updating the Costing_enabled_flag or inventory_asset_flag.
1208 In addition,
1209 1) If org controlled, then we check for current org
1210 2) If master controlled, then we check for all child orgs
1211
1212 These are done based on the below assumptions
1213
1214 1. If inventory_asset_flag is controlled at Master level,
1215 For Standard type Costing, all the child org will have the same Costing org
1216 which is nothing but the Inventory Master org.
1217 For Average costing, the costing org will be the org itself.
1218
1219 2. If inventory_asset_flag is controlled at org level, the costing org would
1220 be the org itself. This holds good for all types of Costing.
1221
1222 */
1223
1224 IF X_cost_txn IS NOT NULL THEN
1225 select count(1)
1226 into cost_moq
1227 from mtl_onhand_quantities_detail -- Bug:2687570
1228 where inventory_item_id = p_item_id
1229 and organization_id = p_org_id
1230 /* Bug 14334921 (select organization_id
1231 from mtl_parameters
1232 where cost_organization_id = p_org_id) */
1233 and rownum = 1;
1234
1235 if (cost_moq <> 1) then
1236 select count(1) + count(2)
1237 into cost_moq2
1238 from mtl_onhand_quantities_detail -- Bug:2687570
1239 where inventory_item_id = p_item_id
1240 and organization_id in
1241 (select organization_id
1242 from mtl_parameters
1243 where master_organization_id = p_master_org
1244 and (1=inv_asset_level OR 1=cost_enabled_level))
1245 and rownum = 1;
1246
1247 end if;
1248 -- If the INVENTORY_ASSET_FLAG or COSTING_ENABLED_FLAG is maintained at Org control level, even if the
1249 -- check for any child orgs is failed, we should still allow if there are no onhand in the particular org in which
1250 -- the user edits the flag, hence commenting out 'and cost_moq2 <> 1' in the below if condition
1251
1252 -- if (cost_moq <> 1 and cost_moq2 <> 1) then
1253 if (cost_moq <> 1) then
1254 select count(1)
1255 into cost_tmp
1256 from mtl_material_transactions_temp
1257 where inventory_item_id = p_item_id
1258 and organization_id = p_org_id
1259 /* Bug 14334921 (select organization_id
1260 from mtl_parameters
1261 where cost_organization_id = p_org_id)*/
1262 and rownum = 1;
1263
1264 end if;
1265
1266
1267 if (cost_moq <> 1 AND cost_tmp <> 1 AND cost_moq2 <> 2) then
1268
1269 select count(1) + Count(2)
1270 into cost_tmp2
1271 from mtl_material_transactions_temp
1272 where inventory_item_id = p_item_id
1273 and organization_id in
1274 (select organization_id
1275 from mtl_parameters
1276 where master_organization_id = p_master_org AND
1277 ( 1=(select control_level from mtl_item_attributes
1278 where attribute_name= 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG')
1279
1280 OR
1281 1=(select control_level from mtl_item_attributes
1282 where attribute_name='MTL_SYSTEM_ITEMS.COSTING_ENABLED_FLAG'))
1283 )
1284 and rownum = 1;
1285
1286 end if;
1287
1288 -- If the INVENTORY_ASSET_FLAG or COSTING_ENABLED_FLAG is maintained at Org control level, even if the
1289 -- check for aby child orgs is failed, we should still allow if there are no onhand in the particular org in which
1290 -- the user edits the flag, hence commenting out 'and cost_moq2 <> 1' in the below if condition
1291 -- if (cost_moq <> 1 and cost_moq2 <> 1 and cost_tmp <> 1) then
1292
1293 if (cost_moq <> 1 and cost_tmp <> 1) then
1294 select count(1)
1295 into cost_mmt
1296 from mtl_material_transactions
1297 where inventory_item_id = p_item_id
1298 and organization_id = p_org_id
1299 /* Bug 14334921 (select organization_id
1300 from mtl_parameters
1301 where cost_organization_id = p_org_id)*/
1302 and costed_flag is not null
1303 and rownum = 1;
1304
1305 end if;
1306
1307 if (cost_moq <> 1 and cost_moq2 <> 2 and cost_tmp <> 1 AND cost_mmt <> 1 AND cost_tmp2 <> 2) then
1308
1309 select count(1) + Count(2)
1310 into cost_mmt2
1311 from mtl_material_transactions
1312 where inventory_item_id = p_item_id
1313 and organization_id in
1314 (select organization_id
1315 from mtl_parameters
1316 where master_organization_id = p_master_org AND
1317 ( 1=(select control_level from mtl_item_attributes
1318 where attribute_name= 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG')
1319
1320 OR
1321 1=(select control_level from mtl_item_attributes
1322 where attribute_name='MTL_SYSTEM_ITEMS.COSTING_ENABLED_FLAG'))
1323 )
1324
1325 and costed_flag is not null
1326 and rownum = 1;
1327
1328 end if;
1329
1330 if (cost_moq <> 1 and cost_tmp <> 1 AND cost_mmt <> 1) then
1331
1332 select count(1)
1333 into cost_cst
1334 from cst_quantity_layers
1335 where inventory_item_id = p_item_id
1336 and organization_id = p_org_id
1337 and layer_quantity <> 0
1338 and exists (select 1
1339 from mtl_parameters
1340 where organization_id = p_org_id
1341 and primary_cost_method in (2,5,6))
1342 and rownum = 1;
1343
1344 END IF;
1345
1346 if (cost_moq <> 1 and cost_moq2 <> 2 and cost_tmp <> 1 AND cost_mmt <> 1 AND cost_tmp2 <> 2 AND cost_cst <> 1) then
1347
1348 select count(1)
1349 into cost_cst2
1350 from cst_quantity_layers
1351 where inventory_item_id = p_item_id
1352 and layer_quantity <> 0
1353 and organization_id in
1354 (select organization_id
1355 from mtl_parameters
1356 where master_organization_id = p_master_org AND
1357 ( 1=(select control_level from mtl_item_attributes
1358 where attribute_name= 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG')
1359
1360 OR
1361 1=(select control_level from mtl_item_attributes
1362 where attribute_name='MTL_SYSTEM_ITEMS.COSTING_ENABLED_FLAG'))
1363
1364
1365 and primary_cost_method in (2,5,6))
1366 and rownum = 1;
1367
1368 END IF;
1369
1370 if (cost_moq = 1 or cost_moq2 = 1 or cost_tmp = 1 or cost_mmt = 1) then
1371 X_cost_txn := 1;
1372 end if;
1373 if (cost_moq2 = 2 OR cost_mmt2 = 2 OR cost_tmp2 = 2 OR cost_cst2 = 1) then
1374 X_cost_txn := 2;
1375 end if;
1376 -- Bug 14334921 END
1377 END IF;
1378
1379 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1380 -- Check if there are required descriptive elements that are currently
1381 -- null for this item
1382 -- Used to determine if catalog_status_flag can be set to 'Y'
1383 -- X_null_elem_exists will have either 0 or 1
1384 -- We need to do this check only in master org items, since the
1385 -- catalog_status_flag is always controlled at the master org level
1386
1387 if (p_master_org = p_org_id) AND (X_null_elem_exists IS NOT NULL) then
1388 select count(1)
1389 into X_null_elem_exists
1390 from mtl_descriptive_elements e,
1391 mtl_descr_element_values v
1392 where e.required_element_flag = 'Y'
1393 and e.item_catalog_group_id = p_catalog_group_id
1394 and v.inventory_item_id = p_item_id
1395 and v.element_name = e.element_name
1396 and v.element_value is null
1397 and rownum = 1;
1398 end if ;
1399
1400 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1401 -- Check if the item exists on an mrp schedule
1402 -- X_mrp_schedule = 1 if it exists on a schedule, 0 otherwise
1403 IF X_mrp_schedule IS NOT NULL THEN
1404 select count(1)
1405 into X_mrp_schedule
1406 from mrp_schedule_items
1407 where inventory_item_id = p_item_id
1408 and organization_id = p_org_id
1409 and rownum = 1;
1410 END IF;
1411 --Bug: 2691174 Disabling vehicle items from being edited if a corresponding
1412 -- vehicle type is exists in FTE_VEHICLE_TYPES
1413 IF INV_ITEM_UTIL.Appl_Inst_FTE <> 0 AND X_fte_vechicle_exists IS NOT NULL THEN
1414 SELECT count(1)
1415 INTO l_tab_exists
1416 FROM TAB
1417 WHERE TNAME = 'FTE_VEHICLE_TYPES'
1418 AND ROWNUM = 1;
1419 IF l_tab_exists > 0 THEN
1420 --Bug: 2812994 Corrected the Dynamic SQL
1421 EXECUTE IMMEDIATE
1422 'SELECT count(1) '||
1423 'FROM FTE_VEHICLE_TYPES '||
1424 'WHERE INVENTORY_ITEM_ID = :p_item_id '||
1425 'AND ORGANIZATION_ID = :p_org_id '||
1426 'AND ROWNUM = 1'
1427 INTO X_fte_vechicle_exists USING IN p_item_id, IN p_org_id;
1428 END IF;
1429 END IF;
1430
1431 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1432 -- Added for 11.5.10 bug 3012796
1433 -- Check if the master org is process enabled org
1434 -- X_process_enabled = 1 if master org is process enabled, 0 otherwise
1435 -- Check if the item is VMI/Consign enabled
1436 IF X_process_enabled IS NOT NULL THEN
1437 select count(1)
1438 into X_process_enabled
1439 from mtl_parameters
1440 where organization_id = p_org_id
1441 and process_enabled_flag = 'Y'
1442 and rownum = 1;
1443 END IF;
1444
1445 IF X_vmiorconsign_enabled IS NOT NULL OR X_consign_enabled IS NOT NULL THEN
1446 VMI_Table_Queries(
1447 p_org_id
1448 , p_item_id
1449 , X_vmiorconsign_enabled
1450 , X_consign_enabled);
1451 END IF;
1452
1453 END Table_Queries;
1454
1455 FUNCTION Get_inv_item_id Return Number is
1456 BEGIN
1457
1458 RETURN (G_inv_item_id);
1459
1460 END Get_inv_item_id;
1461
1462 PROCEDURE Set_inv_item_id(item_id number)
1463 IS
1464 BEGIN
1465
1466 G_inv_item_id := item_id;
1467
1468 END Set_inv_item_id;
1469
1470 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1471 -- Added for 11.5.10 bug 3012796
1472 -- Check if the item is VMI/Consign enabled
1473 -- X_vmiorconsign_enabled = 'Y' if the item is VMI/Consign enabled, 'N' otherwise
1474 -- X_consign_enabled = 'Y' if the item is Consign enabled, 'N' otherwise
1475
1476 PROCEDURE VMI_Table_Queries
1477 ( p_org_id IN NUMBER
1478 , p_item_id IN NUMBER
1479 , X_vmiorconsign_enabled OUT NOCOPY NUMBER
1480 , X_consign_enabled OUT NOCOPY NUMBER
1481 )
1482 IS
1483 -- Local variables
1484 l_return_status VARCHAR2(1);
1485 l_msg_count NUMBER;
1486 l_msg_data VARCHAR2(1000);
1487 l_consign_flag VARCHAR2(1);
1488 l_vmiorconsign_flag VARCHAR2(1);
1489 BEGIN
1490
1491 INV_PO_ITEMVALID_MDTR.check_vmiorconsign_enabled (
1492 p_api_version => 1
1493 , p_init_msg_list => 'T'
1494 , x_return_status => l_return_status
1495 , x_msg_count => l_msg_count
1496 , x_msg_data => l_msg_data
1497 , p_item_id => p_item_id
1498 , p_organization_id => p_org_id
1499 , x_vmiorconsign_flag => l_vmiorconsign_flag );
1500
1501 if (l_vmiorconsign_flag = 'Y') then
1502 X_vmiorconsign_enabled := 1;
1503 else
1504 X_vmiorconsign_enabled := 0;
1505 end if;
1506 INV_PO_ITEMVALID_MDTR.check_consign_enabled (
1507 p_api_version => 1
1508 , p_init_msg_list => 'T'
1509 , x_return_status => l_return_status
1510 , x_msg_count => l_msg_count
1511 , x_msg_data => l_msg_data
1512 , p_item_id => p_item_id
1513 , p_organization_id => p_org_id
1514 , x_consign_flag => l_consign_flag );
1515
1516 if (l_consign_flag = 'Y') then
1517 X_consign_enabled := 1;
1518 else
1519 X_consign_enabled := 0;
1520 end if;
1521
1522 END VMI_Table_Queries;
1523
1524 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1525 -- Added for 11.5.10 bug 3171098
1526 -- Check for proper catalog category setup for PLM
1527 --Bug: 3491746 New catalog should not have NIR and if not child catalog then
1528 -- check whether item has any open/hold CO's for an item
1529 FUNCTION Is_Catalog_Group_Valid(
1530 old_catalog_group_id VARCHAR2,
1531 new_catalog_group_id VARCHAR2,
1532 item_id NUMBER) RETURN VARCHAR2
1533 IS
1534
1535 l_sql VARCHAR2(4000) :=
1536 'SELECT ''Y''
1537 FROM eng_revised_items eri
1538 WHERE eri.revised_item_id = :cp_item_id
1539 AND eri.status_type NOT IN (5, 6)
1540 AND ( eri.NEW_ITEM_REVISION_ID IS NOT null --this CO creates a revision
1541 OR EXISTS --this CO has AML Change
1542 (SELECT NULL
1543 FROM ego_mfg_part_num_chgs
1544 WHERE change_line_id = eri.revised_item_sequence_id )
1545 OR EXISTS --this CO has UDA Change
1546 (SELECT NULL
1547 FROM ego_items_attrs_changes_b
1548 WHERE change_line_id = eri.revised_item_sequence_id )
1549 OR EXISTS --this CO has Attachment Change
1550 (SELECT NULL
1551 FROM eng_attachment_changes
1552 WHERE revised_item_sequence_id = eri.revised_item_sequence_id )
1553 OR EXISTS --this CO has Operational Attribute Change
1554 (SELECT NULL
1555 FROM ego_mtl_sy_items_chg_b
1556 WHERE change_line_id = eri.revised_item_sequence_id
1557 AND change_id = eri.change_id)
1558 OR EXISTS --this CO has GTIN Single Change
1559 (SELECT NULL
1560 FROM ego_gtn_attr_chg_b
1561 WHERE change_line_id = eri.revised_item_sequence_id
1562 AND change_id = eri.change_id)
1563 OR EXISTS --this CO has GTIN Multi Change
1564 (SELECT NULL
1565 FROM ego_gtn_mul_attr_chg_b
1566 WHERE change_line_id = eri.revised_item_sequence_id
1567 AND change_id = eri.change_id)
1568 OR EXISTS --this CO has Related Doc Change
1569 (SELECT NULL
1570 FROM eng_relationship_changes
1571 WHERE ENTITY_ID = eri.revised_item_sequence_id
1572 AND change_id = eri.change_id
1573 AND ENTITY_NAME=''ITEM'')
1574 OR EXISTS --this CO has Structure Changes
1575 (SELECT NULL
1576 FROM bom_components_b
1577 WHERE revised_item_sequence_id = eri.revised_item_sequence_id)
1578 )
1579 AND ROWNUM =1 ';
1580
1581 l_child_catalog VARCHAR2(200) := NULL;
1582 l_co_exists VARCHAR2(200) := NULL;
1583
1584 BEGIN
1585 l_child_catalog := 'N';
1586 IF new_catalog_group_id IS NOT NULL THEN
1587 BEGIN
1588 SELECT 'Y' INTO L_child_catalog
1589 FROM mtl_item_catalog_groups_b icg
1590 WHERE icg.item_creation_allowed_flag = 'Y' AND
1591 ((inactive_date is null) or ((trunc(inactive_date) > trunc(sysdate)) OR
1592 (icg.item_catalog_group_id=item_catalog_group_id))) AND
1593 icg.item_catalog_group_id = new_catalog_group_id
1594 CONNECT BY prior icg.item_catalog_group_id = icg.parent_catalog_group_id
1595 START WITH icg.item_catalog_group_id = old_catalog_group_id;
1596
1597 EXCEPTION
1598 WHEN NO_DATA_FOUND THEN
1599 l_child_catalog := 'N';
1600 END;
1601 END IF;
1602
1603 IF (l_child_catalog <> 'Y') THEN --If not child catalog
1604 IF (old_catalog_group_id) IS NOT NULL
1605 THEN
1606 IF FND_PROFILE.DEFINED('EGO_OCD_ENABLED') THEN--Bug: 3570886
1607 BEGIN
1608 EXECUTE IMMEDIATE l_sql INTO l_co_exists USING item_id;
1609 EXCEPTION
1610 WHEN OTHERS THEN --Bug:3610290
1611 l_co_exists := 'N';
1612 END;
1613 END IF;
1614 END IF;
1615 END IF;
1616
1617 IF (l_child_catalog <> 'Y' AND
1618 l_co_exists = 'Y') THEN
1619 RETURN('INV_INVALID_CATALOG_SETUP');
1620 END IF;
1621 RETURN NULL;
1622
1623 END Is_Catalog_Group_Valid;
1624
1625 FUNCTION CHECK_NPR_CATALOG(p_catalog_group_id NUMBER)
1626 RETURN BOOLEAN IS
1627
1628 CURSOR c_get_npr_flag(cp_catalog_group_id NUMBER) IS
1629 SELECT new_item_request_reqd
1630 FROM mtl_item_catalog_groups_b
1631 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1632 START WITH item_catalog_group_id = cp_catalog_group_id;
1633
1634 l_npr_flag mtl_item_catalog_groups_b.new_item_request_reqd%TYPE;
1635 l_return_value BOOLEAN := FALSE;
1636 l_miss_num CONSTANT NUMBER := 9.99E125;
1637 BEGIN
1638
1639 IF p_catalog_group_id IS NOT NULL
1640 AND p_catalog_group_id <> l_miss_num THEN
1641
1642 FOR cur IN c_get_npr_flag(p_catalog_group_id) LOOP
1643 IF cur.new_item_request_reqd = 'Y' THEN
1644 l_return_value := TRUE;
1645 ELSIF NVL(cur.new_item_request_reqd,'N') = 'N' THEN
1646 l_return_value := FALSE;
1647 END IF;
1648
1649 EXIT WHEN NVL(cur.new_item_request_reqd,'N') <> 'I';
1650
1651 END LOOP;
1652
1653 END IF;
1654
1655 RETURN l_return_value;
1656
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 RETURN l_return_value;
1660 END CHECK_NPR_CATALOG;
1661
1662 FUNCTION CHECK_ITEM_APPROVED(p_inventory_item_id NUMBER
1663 ,p_organization_id NUMBER)
1664 RETURN BOOLEAN IS
1665
1666 CURSOR c_get_item_status(cp_inventory_item_id NUMBER
1667 ,cp_organization_id NUMBER)
1668 IS
1669 SELECT approval_status
1670 FROM mtl_system_items_b
1671 WHERE inventory_item_id = cp_inventory_item_id
1672 AND organization_id = cp_organization_id;
1673
1674 l_return_value BOOLEAN := FALSE;
1675 l_item_approval_status mtl_system_items_b.approval_status%TYPE;
1676
1677 BEGIN
1678
1679 OPEN c_get_item_status(p_inventory_item_id,p_organization_id);
1680 FETCH c_get_item_status INTO l_item_approval_status;
1681 CLOSE c_get_item_status;
1682
1683 IF NVL(l_item_approval_status,'A') = 'A' THEN
1684 l_return_value := TRUE;
1685 END IF;
1686
1687 RETURN l_return_value;
1688
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 IF c_get_item_status%ISOPEN THEN
1692 CLOSE c_get_item_status;
1693 END IF;
1694 RETURN l_return_value;
1695 END CHECK_ITEM_APPROVED;
1696
1697 --Added for Bug: 4569555
1698 PROCEDURE CSI_Table_Queries (
1699 p_inventory_item_id IN NUMBER
1700 ,p_organization_id IN NUMBER
1701 ,X_ib_ret_status OUT NOCOPY VARCHAR2
1702 ,X_ib_msg OUT NOCOPY VARCHAR2) IS
1703
1704 l_plsql_blk VARCHAR2(2000);
1705 l_msg_count NUMBER;
1706 BEGIN
1707 IF INV_ITEM_UTIL.Appl_Inst_CSI <> 0 THEN
1708 BEGIN
1709 l_plsql_blk :=
1710 'BEGIN
1711 CSI_UTILITY_GRP.vld_item_ctrl_changes (
1712 p_api_version => 1.0
1713 ,p_commit => fnd_api.g_false
1714 ,p_init_msg_list => fnd_api.g_false
1715 ,p_validation_level => fnd_api.g_valid_level_full
1716 ,p_inventory_item_id => :1
1717 ,p_organization_id => :2
1718 ,p_item_attr_name => NULL
1719 ,p_new_item_attr_value => NULL
1720 ,p_old_item_attr_value => NULL
1721 ,x_return_status => :X_ib_ret_status
1722 ,x_msg_count => :l_msg_count
1723 ,x_msg_data => :X_ib_msg);
1724 END;';
1725 EXECUTE IMMEDIATE l_plsql_blk
1726 USING p_inventory_item_id, p_organization_id,OUT X_ib_ret_status,OUT l_msg_count,OUT X_ib_msg;
1727 X_ib_ret_status:= FND_API.G_RET_STS_ERROR;
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 X_ib_ret_status:= FND_API.G_RET_STS_SUCCESS;
1731 X_ib_msg := NULL;
1732 END;
1733 ELSE
1734 X_ib_ret_status:= FND_API.G_RET_STS_SUCCESS;
1735 X_ib_msg := NULL;
1736 END IF;
1737 END CSI_Table_Queries;
1738
1739 END INVIDIT3;
[Home] [Help]