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