[Home] [Help]
89:
90: -- Bug 13630492 - Transfer reservations for batches also.
91: Cursor check_whse (p_batch_line_id IN NUMBER) IS
92: Select distinct organization_id
93: From mtl_reservations
94: Where supply_source_line_id = p_batch_line_id
95: and supply_source_type_id = 5
96: -- and demand_source_type_id = inv_reservation_global.g_source_type_oe
97: and demand_source_type_id IN (inv_reservation_global.g_source_type_oe, gme_common_pvt.g_txn_source_type) -- Bug 13630492
109:
110: Cursor get_res_for_whse (p_organization_id IN NUMBER
111: , p_batch_line_id IN NUMBER) IS
112: Select *
113: From mtl_reservations
114: Where supply_source_line_id = p_batch_line_id
115: and supply_source_type_id = 5
116: and demand_source_type_id = inv_reservation_global.g_source_type_oe
117: and organization_id = p_organization_id
121:
122: Cursor get_res_count(p_batch_line_id IN NUMBER
123: ) Is
124: Select count(1)
125: From mtl_reservations
126: Where supply_source_line_id = p_batch_line_id
127: --and organization_id = p_organization_id
128: and supply_source_type_id = 5
129: and reservation_quantity <> 0
388:
389:
390: CURSOR So_line_id_for_batch(p_batch_id IN NUMBER) Is
391: SELECT Distinct orig_demand_source_line_id
392: FROM mtl_reservations
393: WHERE supply_source_header_id = p_batch_id
394: and supply_source_type_id = 5
395: and demand_source_type_id = 2
396: and reservation_quantity <> 0;
402:
403:
404: CURSOR So_line_id_for_batch_line(p_batch_line_id IN NUMBER) Is
405: SELECT Distinct orig_demand_source_line_id
406: FROM mtl_reservations
407: WHERE supply_source_line_id = p_batch_line_id
408: and supply_source_type_id = 5
409: and demand_source_type_id = 2
410: and reservation_quantity <> 0;
420: SELECT last_updated_by, created_by,header_id
421: FROM oe_order_lines_all
422: WHERE line_id = p_so_line_id;
423:
424: -- nsinghi perf bug#5212566. Re-written the 2 cursors to ensure that there is no FTS on mtl_reservations.
425: /*
426: Cursor check_mul_line_id1 (p_user_id IN number
427: , p_batch_id IN NUMBER) IS
428: Select distinct orig_demand_source_line_id
425: /*
426: Cursor check_mul_line_id1 (p_user_id IN number
427: , p_batch_id IN NUMBER) IS
428: Select distinct orig_demand_source_line_id
429: From mtl_reservations
430: Where created_by = p_user_id
431: and orig_supply_source_header_id = p_batch_id
432: and demand_source_type_id = 2
433: and orig_supply_source_type_id = 5;
435: Cursor check_mul_line_id2 (p_user_id IN number
436: , p_batch_id IN NUMBER
437: , p_batch_line_id IN NUMBER) IS
438: Select distinct orig_demand_source_line_id
439: From mtl_reservations
440: Where created_by = p_user_id
441: and orig_supply_source_header_id = p_batch_id
442: and orig_supply_source_line_id = p_batch_line_id
443: and demand_source_type_id = 2
445: */
446: Cursor check_mul_line_id1 (p_user_id IN number
447: , p_batch_id IN NUMBER) IS
448: SELECT DISTINCT orig_demand_source_line_id
449: FROM mtl_reservations mr, gme_batch_header gbh
450: WHERE mr.created_by = p_user_id
451: AND mr.orig_supply_source_header_id = p_batch_id
452: AND mr.orig_supply_source_header_id = gbh.batch_id
453: AND mr.organization_id = gbh.organization_id
457: Cursor check_mul_line_id2 (p_user_id IN number
458: , p_batch_id IN NUMBER
459: , p_batch_line_id IN NUMBER) IS
460: SELECT DISTINCT orig_demand_source_line_id
461: FROM mtl_reservations mr, gme_material_details gmd
462: WHERE mr.created_by = p_user_id
463: AND mr.orig_supply_source_header_id = p_batch_id
464: AND mr.orig_supply_source_line_id = p_batch_line_id
465: AND mr.inventory_item_id = gmd.inventory_item_id
1036: -- Bug 12805400 - Put in check for indivisible item.
1037: -- Update the reservation for indivisible items to avoid qty issues.
1038: -- Take the whole transaction qty if the item is indivisible.
1039: IF l_lot_divisible_flag = 'N' THEN
1040: update mtl_reservations
1041: set reservation_quantity = l_txn_quantity,
1042: primary_reservation_quantity = l_txn_primary,
1043: secondary_reservation_quantity = l_txn_secondary
1044: where reservation_id = l_rsv_rec.reservation_id;