2694: debug_print('reservation_id = ' || p_reservation_id);
2695: debug_print('update_serial_qty = ' || p_update_serial_qty);
2696: END IF;
2697:
2698: update mtl_reservations
2699: set serial_reservation_quantity = serial_reservation_quantity + p_update_serial_qty
2700: where reservation_id = p_reservation_id;
2701: l_update_count := SQL%ROWCOUNT;
2702:
2885: , orig_demand_source_header_id
2886: , orig_demand_source_line_id
2887: , orig_demand_source_line_detail
2888: , serial_number
2889: FROM mtl_reservations
2890: WHERE reservation_id = p_reservation_id;
2891: BEGIN
2892: IF (g_debug is NULL) THEN
2893: g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3958: , orig_demand_source_line_id
3959: , orig_demand_source_line_detail
3960: , serial_number
3961: /***** End R12 ***/
3962: FROM mtl_reservations
3963: WHERE reservation_id = p_query_input.reservation_id
3964: FOR UPDATE --NOWAIT
3965: ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
3966:
4040: , orig_demand_source_line_id
4041: , orig_demand_source_line_detail
4042: , serial_number
4043: /***** End R12 ***/
4044: FROM mtl_reservations
4045: WHERE reservation_id = p_query_input.reservation_id
4046: ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
4047:
4048: -- INVCONV - Incorporate secondary quantities
4122: , orig_demand_source_line_id
4123: , orig_demand_source_line_detail
4124: , serial_number
4125: /***** End R12 ***/
4126: FROM mtl_reservations
4127: WHERE demand_source_line_id = p_query_input.demand_source_line_id
4128: AND (p_query_input.lpn_id = l_miss_num
4129: OR p_query_input.lpn_id IS NULL
4130: AND lpn_id IS NULL
4466: , orig_demand_source_line_id
4467: , orig_demand_source_line_detail
4468: , serial_number
4469: /***** End R12 ***/
4470: FROM mtl_reservations
4471: WHERE demand_source_line_id = p_query_input.demand_source_line_id
4472: AND (p_query_input.requirement_date = l_miss_date
4473: OR p_query_input.requirement_date IS NULL
4474: AND requirement_date IS NULL
4940: , orig_demand_source_line_id
4941: , orig_demand_source_line_detail
4942: , serial_number
4943: /***** End R12 ***/
4944: FROM mtl_reservations
4945: WHERE
4946: :reservation_id = reservation_id '
4947: || l_lock_stmt
4948: || l_sort_stmt USING p_query_input.reservation_id;
5047: , orig_demand_source_line_id
5048: , orig_demand_source_line_detail
5049: , serial_number
5050: /***** End R12 ***/
5051: FROM mtl_reservations
5052: WHERE
5053: demand_source_line_id = :demand_source_line_id
5054: AND
5055: (:requirement_date = :l_miss_date
5685: , orig_demand_source_line_id
5686: , orig_demand_source_line_detail
5687: , serial_number
5688: /***** End R12 ***/
5689: FROM mtl_reservations
5690: WHERE
5691: inventory_item_id = :inventory_item_id
5692: and organization_id = :organization_id
5693: AND
6319: , orig_demand_source_line_id
6320: , orig_demand_source_line_detail
6321: , serial_number
6322: /***** End R12 ***/
6323: FROM mtl_reservations
6324: WHERE
6325: supply_source_header_id = :supply_source_header_id
6326: and supply_source_type_id = :supply_source_type_id
6327: AND
7163: , orig_demand_source_line_id
7164: , orig_demand_source_line_detail
7165: , serial_number
7166: /***** End R12 ***/
7167: FROM mtl_reservations
7168: WHERE
7169: (:requirement_date = :l_miss_date
7170: OR :requirement_date IS NULL
7171: AND requirement_date IS NULL
9440: debug_print(' serial reservation qty = ' || l_rsv_rec.serial_reservation_quantity);
9441: END IF;
9442:
9443: -- INVCONV - Upgrade call to incorporate secondaries
9444: mtl_reservations_pkg.insert_row
9445: (
9446: x_rowid => l_rowid
9447: , x_reservation_id => l_reservation_id
9448: , x_requirement_date => l_rsv_rec.requirement_date
9536: END IF;
9537: IF (l_debug = 1) THEN
9538: debug_print('before sync ' || l_return_status);
9539: END IF;
9540: -- for data sync b/w mtl_demand and mtl_reservations
9541: inv_rsv_synch.for_insert(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
9542:
9543: IF (l_debug = 1) THEN
9544: debug_print('After sync ' || l_return_status);
11536: l_program_id := fnd_global.conc_program_id;
11537: --
11538: -- update the table
11539: IF (l_debug = 1) THEN
11540: debug_print('Calling mtl_reservations_pkg.update_row');
11541: END IF;
11542:
11543: -- Bug 3461990: Reservations API should not update reservations with more
11544: -- than 5 decimal places, since the transaction quantity is being
11570: debug_print(' After rounding sec detailed quantity' || l_to_rsv_rec.secondary_detailed_quantity); --INVCONV
11571: END IF;
11572:
11573: -- INVCONV - Upgrade to incorporate secondaries
11574: mtl_reservations_pkg.update_row
11575: (
11576: x_reservation_id => l_orig_rsv_tbl(1).reservation_id
11577: , x_requirement_date => l_to_rsv_rec.requirement_date
11578: , x_organization_id => l_to_rsv_rec.organization_id
11656: END IF;
11657: -- bug 16434922
11658: l_secondary_quantity_reserved := l_to_rsv_rec.secondary_reservation_quantity;
11659:
11660: -- for data sync b/w mtl_demand and mtl_reservations
11661: IF (l_debug = 1) THEN
11662: debug_print('Calling inv_rsv_synch.for_update');
11663: END IF;
11664: inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
12222: END;
12223: /*** End R12 }} ***/
12224:
12225: IF (l_debug = 1) THEN
12226: debug_print('calling mtl_reservations_pkg.delete_row');
12227: END IF;
12228: mtl_reservations_pkg.delete_row(x_reservation_id => l_reservation_id);
12229: NULL;
12230:
12224:
12225: IF (l_debug = 1) THEN
12226: debug_print('calling mtl_reservations_pkg.delete_row');
12227: END IF;
12228: mtl_reservations_pkg.delete_row(x_reservation_id => l_reservation_id);
12229: NULL;
12230:
12231: ELSE
12232:
12333:
12334: /*** {{ R12 Enhanced reservations code changes ***/
12335: -- if serial record is not empty, then unmark the group_mark_id and
12336: -- reservation_id in mtl_serial_numbers of the serial numbers pass in the record.
12337: -- also update the serial_reservation_quantity in the mtl_reservations.
12338: -- if serial record is empty, need to get the serials with reservation_id and
12339: -- unmark the serial until the serial_reservation_quantity = primary_reservation_quantity
12340: IF (p_original_serial_number.COUNT > 0) THEN
12341: l_count := 0;
12529: debug_print('update reservation');
12530: END IF;
12531:
12532: -- INVCONV - Incorporate secondaries
12533: UPDATE mtl_reservations
12534: SET primary_reservation_quantity = l_tmp_rsv_tbl(1).primary_reservation_quantity
12535: , secondary_reservation_quantity = l_tmp_rsv_tbl(1).secondary_reservation_quantity
12536: , reservation_quantity = l_tmp_rsv_tbl(1).reservation_quantity
12537: , last_update_date = l_date
12867: debug_print('After calling inventory_unreservation_check...' || l_return_status);
12868: END IF;
12869:
12870: --
12871: -- for data sync b/w mtl_demand and mtl_reservations
12872: inv_rsv_synch.for_delete(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
12873:
12874: IF l_return_status = fnd_api.g_ret_sts_error THEN
12875: RAISE fnd_api.g_exc_error;
12901:
12902: --
12903: --
12904: -- delete the reservation from the db table
12905: mtl_reservations_pkg.delete_row(x_reservation_id => l_reservation_id);
12906:
12907: -- Post Delete CTO Validation
12908: IF l_tmp_rsv_tbl(1).demand_source_type_id IN (inv_reservation_global.g_source_type_oe, inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_rma) THEN
12909: --
14492: debug_print(' After rounding detailed quantity' || l_detailed_quantity);
14493: END IF;
14494:
14495: -- INVCONV - Incorporate secondary_reservation_quantity
14496: UPDATE mtl_reservations
14497: SET primary_reservation_quantity = l_new_orig_prim_qty
14498: , secondary_reservation_quantity = l_orig_second_rsv_qty
14499: , reservation_quantity = l_new_orig_rsv_qty
14500: , detailed_quantity = l_detailed_quantity
14507: , program_id = l_program_id
14508: , program_update_date = l_date
14509: WHERE reservation_id = l_orig_rsv_tbl(1).reservation_id;
14510:
14511: -- for data sync b/w mtl_demand and mtl_reservations
14512: inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
14513:
14514: IF l_return_status = fnd_api.g_ret_sts_error THEN
14515: RAISE fnd_api.g_exc_error;
14575: END IF;
14576: --
14577: END IF;
14578:
14579: -- for data sync b/w mtl_demand and mtl_reservations
14580: inv_rsv_synch.for_delete(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
14581:
14582: IF l_return_status = fnd_api.g_ret_sts_error THEN
14583: RAISE fnd_api.g_exc_error;
14588: RAISE fnd_api.g_exc_unexpected_error;
14589: END IF;
14590:
14591: -- 2884492
14592: mtl_reservations_pkg.delete_row
14593: (x_reservation_id => l_orig_rsv_tbl(1).reservation_id
14594: ,x_to_reservation_id => l_to_rsv_tbl(1).reservation_id);
14595: /** commented out by request of CTO. The workflow was not processing
14596: * correctly.
14730: debug_print(' After rounding detailed quantity' ||l_to_rsv_rec.detailed_quantity );
14731: END IF;
14732:
14733: -- INVCONV - Incorporate secondaries in update
14734: mtl_reservations_pkg.update_row
14735: (
14736: x_reservation_id => l_orig_rsv_tbl(1).reservation_id
14737: , x_requirement_date => l_to_rsv_rec.requirement_date
14738: , x_organization_id => l_to_rsv_rec.organization_id
14809: , x_task_id => l_to_rsv_rec.task_id
14810: /*** End R12 ***/
14811: );
14812: --
14813: -- for data sync b/w mtl_demand and mtl_reservations
14814: inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
14815:
14816: debug_print(' return status after updating row' || l_return_status);
14817:
14935:
14936: -- INVCONV - Retrieve secondaries
14937: SELECT primary_reservation_quantity, secondary_reservation_quantity, reservation_quantity
14938: INTO l_primary_rsv_quantity, l_secondary_rsv_quantity, l_rsv_quantity
14939: FROM mtl_reservations
14940: WHERE reservation_id = l_to_rsv_tbl(1).reservation_id;
14941:
14942: l_primary_rsv_quantity := l_primary_rsv_quantity + l_to_rsv_rec.primary_reservation_quantity;
14943:
14983: -- wont call table handler since only quantities are changed along
14984: -- with the who column this is simpler to read and understand
14985:
14986: -- INVCONV - Incorporate secondary_reservation_quantity which could be null
14987: UPDATE mtl_reservations
14988: SET primary_reservation_quantity = Round((primary_reservation_quantity + l_to_rsv_rec.primary_reservation_quantity),5)
14989: , secondary_reservation_quantity = Round((secondary_reservation_quantity + l_to_rsv_rec.secondary_reservation_quantity),5)
14990: , reservation_quantity = Round((reservation_quantity + l_to_rsv_rec.reservation_quantity),5)
14991: , detailed_quantity = Round(NVL(detailed_quantity, 0) + NVL(l_to_rsv_rec.detailed_quantity, 0),5)
14998: , program_id = l_program_id
14999: , program_update_date = l_date
15000: WHERE reservation_id = l_to_rsv_tbl(1).reservation_id;
15001:
15002: -- for data sync b/w mtl_demand and mtl_reservations
15003: inv_rsv_synch.for_update(p_reservation_id => l_to_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
15004:
15005: IF l_return_status = fnd_api.g_ret_sts_error THEN
15006: RAISE fnd_api.g_exc_error;
15167: END IF;
15168: --12362469
15169:
15170: -- INVCONV - Incorporate secondary columns
15171: mtl_reservations_pkg.insert_row
15172: (
15173: x_rowid => l_rowid
15174: , x_reservation_id => l_reservation_id
15175: , x_requirement_date => l_to_rsv_rec.requirement_date
15259: );
15260:
15261: debug_print(' After call to insert_row : reservation_id : ' || l_reservation_id);
15262:
15263: -- insert into mtl_reservations
15264: x_reservation_id := l_reservation_id;
15265:
15266: -- for data sync b/w mtl_demand and mtl_reservations
15267: inv_rsv_synch.for_insert(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
15262:
15263: -- insert into mtl_reservations
15264: x_reservation_id := l_reservation_id;
15265:
15266: -- for data sync b/w mtl_demand and mtl_reservations
15267: inv_rsv_synch.for_insert(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
15268:
15269: IF l_return_status = fnd_api.g_ret_sts_error THEN
15270: RAISE fnd_api.g_exc_error;
15338:
15339: --check to see if serial are reserved.
15340: BEGIN
15341: SELECT primary_reservation_quantity INTO
15342: l_from_primary_reservation_qty FROM mtl_reservations WHERE
15343: reservation_id = l_orig_rsv_tbl(1).reservation_id;
15344: EXCEPTION
15345: WHEN no_data_found THEN
15346: IF l_debug=1 THEN
15348: END IF;
15349: END;
15350: BEGIN
15351: SELECT primary_reservation_quantity INTO
15352: l_to_primary_reservation_qty FROM mtl_reservations WHERE
15353: reservation_id = l_to_reservation_id;
15354: EXCEPTION
15355: WHEN no_data_found THEN
15356: IF l_debug=1 THEN
15367: -- set the serial reservation qty and do nothing.
15368: IF ((l_serial_param = 1) AND (l_total_serials_reserved = 0)) THEN
15369: -- set the serial reservation qty to zero
15370: BEGIN
15371: UPDATE mtl_reservations SET serial_reservation_quantity = 0
15372: WHERE reservation_id = l_orig_rsv_tbl(1).reservation_id;
15373: EXCEPTION
15374: WHEN no_data_found THEN
15375: IF l_debug=1 THEN
16169: -- update the to serial reservation quantity. dont have
16170: -- to update from as the from record is deleted.
16171:
16172: BEGIN
16173: UPDATE mtl_reservations SET serial_reservation_quantity
16174: = l_total_to_serials_reserved WHERE reservation_id =
16175: l_to_reservation_id;
16176:
16177: EXCEPTION
16254: RAISE fnd_api.g_exc_error;
16255: END IF;
16256:
16257: BEGIN
16258: UPDATE mtl_reservations SET serial_reservation_quantity
16259: = l_total_to_serials_reserved WHERE reservation_id =
16260: l_orig_rsv_tbl(1).reservation_id;
16261:
16262: EXCEPTION
16390: RAISE fnd_api.g_exc_error;
16391: END IF;
16392:
16393: BEGIN
16394: UPDATE mtl_reservations SET serial_reservation_quantity
16395: = l_total_to_serials_reserved WHERE reservation_id =
16396: l_to_reservation_id;
16397:
16398: EXCEPTION
16417: END IF;
16418: END;
16419:
16420: BEGIN
16421: UPDATE mtl_reservations SET serial_reservation_quantity
16422: = l_total_from_serials_reserved WHERE reservation_id =
16423: l_orig_rsv_tbl(1).reservation_id;
16424:
16425: EXCEPTION
16632: RAISE fnd_api.g_exc_error;
16633: END IF;
16634:
16635: BEGIN
16636: UPDATE mtl_reservations SET serial_reservation_quantity
16637: = l_total_to_serials_reserved WHERE reservation_id =
16638: l_orig_rsv_tbl(1).reservation_id;
16639:
16640: EXCEPTION
16720: RAISE fnd_api.g_exc_error;
16721: END IF;
16722:
16723: BEGIN
16724: UPDATE mtl_reservations SET serial_reservation_quantity
16725: = l_total_to_serials_reserved WHERE reservation_id =
16726: l_orig_rsv_tbl(1).reservation_id;
16727:
16728: EXCEPTION
16841: RAISE fnd_api.g_exc_error;
16842: END IF;
16843:
16844: BEGIN
16845: UPDATE mtl_reservations SET serial_reservation_quantity
16846: = l_total_to_serials_reserved WHERE reservation_id =
16847: l_to_reservation_id;
16848:
16849: EXCEPTION
16868: END IF;
16869: END;
16870:
16871: BEGIN
16872: UPDATE mtl_reservations SET serial_reservation_quantity
16873: = l_total_from_serials_reserved WHERE reservation_id =
16874: l_orig_rsv_tbl(1).reservation_id;
16875:
16876: EXCEPTION
17073: , orig_demand_source_line_detail
17074: , serial_number
17075: /***** End R12 ***/
17076:
17077: FROM mtl_reservations mr
17078: WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
17079: , inv_reservation_global.g_source_type_internal_ord
17080: , inv_reservation_global.g_source_type_rma
17081: )
17165: , orig_demand_source_line_detail
17166: , serial_number
17167: /***** End R12 ***/
17168:
17169: FROM mtl_reservations mr
17170: WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
17171: , inv_reservation_global.g_source_type_internal_ord
17172: , inv_reservation_global.g_source_type_rma
17173: )
17258: , orig_demand_source_line_detail
17259: , serial_number
17260: /***** End R12 ***/
17261:
17262: FROM mtl_reservations mr
17263: WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
17264: , inv_reservation_global.g_source_type_internal_ord
17265: , inv_reservation_global.g_source_type_rma
17266: )
17413: l_call_xfr_rsv BOOLEAN := FALSE;
17414: l_reservation_id NUMBER;
17415: l_demand_source_header_id NUMBER;
17416:
17417: -- MTL_RESERVATIONS record type declarations
17418: l_src_rsv inv_reservation_global.mtl_reservation_rec_type;
17419: l_xfr_rsv inv_reservation_global.mtl_reservation_rec_type;
17420: l_serial_number inv_reservation_global.serial_number_tbl_type;
17421:
17498: , orig_demand_source_line_detail
17499: , serial_number
17500: /***** End R12 ***/
17501:
17502: FROM mtl_reservations
17503: WHERE organization_id = p_organization_id
17504: AND inventory_item_id = p_inventory_item_id
17505: AND (p_revision IS NULL OR revision = p_revision)
17506: AND (demand_source_header_id = l_demand_source_header_id OR l_demand_source_header_id IS NULL)
18517: CURSOR serials_outer_lpn_with_item IS
18518: SELECT msn.reservation_id,
18519: msn.serial_number,
18520: msn.inventory_item_id
18521: FROM mtl_reservations mr,
18522: mtl_serial_numbers msn
18523: WHERE mr.organization_id = p_organization_id
18524: AND mr.inventory_item_id = p_inventory_item_id
18525: AND mr.reservation_id = msn.reservation_id
18535: CURSOR serials_outer_lpn_no_item IS
18536: SELECT msn.reservation_id,
18537: msn.serial_number,
18538: msn.inventory_item_id
18539: FROM mtl_reservations mr,
18540: mtl_serial_numbers msn
18541: WHERE mr.organization_id = p_organization_id
18542: AND mr.reservation_id = msn.reservation_id
18543: AND mr.lpn_id = null
18552: CURSOR serials_lpn_with_item IS
18553: SELECT msn.reservation_id,
18554: msn.serial_number,
18555: msn.inventory_item_id
18556: FROM mtl_reservations mr,
18557: mtl_serial_numbers msn
18558: WHERE mr.organization_id = p_organization_id
18559: AND mr.inventory_item_id = p_inventory_item_id
18560: AND mr.reservation_id = msn.reservation_id
18568: CURSOR serials_lpn_no_item IS
18569: SELECT msn.reservation_id,
18570: msn.serial_number,
18571: msn.inventory_item_id
18572: FROM mtl_reservations mr,
18573: mtl_serial_numbers msn
18574: WHERE mr.organization_id = p_organization_id
18575: AND mr.reservation_id = msn.reservation_id
18576: AND mr.lpn_id = null