[Home] [Help]
26: inv_log_util.TRACE(p_message, 'INV_RESERVATION_PVT', p_level);
27: END debug_print;
28:
29: --
30: PROCEDURE print_rsv_rec(p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type) IS
31: l_debug number;
32: BEGIN
33: -- Bug 2944896 -- Commenting out the parameters which may have
34: -- g_miss_char as the default value. This leads to truncation of the
194: END get_requested_qty;
195:
196: --Bug 12978409: start
197: PROCEDURE get_reservation_qty_lot(
198: p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type,
199: p_reservation_qty_lot OUT NOCOPY NUMBER)
200:
201: IS
202: l_return_value BOOLEAN;
477:
478: ELSE
479: -- if transfer reservation from other supply to receiving, the total qty in rcv
480: -- will increase, so we need to add the qty from orig record to the reservable qty
481: IF (p_to_supply_source_type_id = inv_reservation_global.g_source_type_rcv) THEN
482: l_reservable_qty := nvl(l_qty_available_to_reserve,0) + p_fm_primary_reservation_qty;
483: ELSE
484: l_reservable_qty := nvl(l_qty_available_to_reserve,0);
485: END IF;
629: ELSE
630:
631: --for sales order or internal order, the reservable qty is the minimum of
632: --requested qty of the line detail/line and the reservable qty of the sales order line
633: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
634: inv_reservation_global.g_source_type_internal_ord,
635: inv_reservation_global.g_source_type_rma) AND
636: p_fm_demand_source_type_id = p_to_demand_source_type_id AND
637: p_fm_demand_source_header_id = p_to_demand_source_header_id AND
630:
631: --for sales order or internal order, the reservable qty is the minimum of
632: --requested qty of the line detail/line and the reservable qty of the sales order line
633: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
634: inv_reservation_global.g_source_type_internal_ord,
635: inv_reservation_global.g_source_type_rma) AND
636: p_fm_demand_source_type_id = p_to_demand_source_type_id AND
637: p_fm_demand_source_header_id = p_to_demand_source_header_id AND
638: nvl(p_fm_demand_source_line_id, -1) = nvl(p_to_demand_source_line_id, -1) AND
631: --for sales order or internal order, the reservable qty is the minimum of
632: --requested qty of the line detail/line and the reservable qty of the sales order line
633: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
634: inv_reservation_global.g_source_type_internal_ord,
635: inv_reservation_global.g_source_type_rma) AND
636: p_fm_demand_source_type_id = p_to_demand_source_type_id AND
637: p_fm_demand_source_header_id = p_to_demand_source_header_id AND
638: nvl(p_fm_demand_source_line_id, -1) = nvl(p_to_demand_source_line_id, -1) AND
639: nvl(p_fm_demand_source_line_detail, -1) <> nvl(p_to_demand_source_line_detail, -1)) THEN
676: END IF;
677:
678: END IF;
679:
680: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
681: inv_reservation_global.g_source_type_internal_ord,
682: inv_reservation_global.g_source_type_rma)) THEN
683:
684: IF NOT (lot_divisible
677:
678: END IF;
679:
680: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
681: inv_reservation_global.g_source_type_internal_ord,
682: inv_reservation_global.g_source_type_rma)) THEN
683:
684: IF NOT (lot_divisible
685: (p_inventory_item_id => p_to_inventory_item_id,
678: END IF;
679:
680: IF (p_to_demand_source_type_id in (inv_reservation_global.g_source_type_oe,
681: inv_reservation_global.g_source_type_internal_ord,
682: inv_reservation_global.g_source_type_rma)) THEN
683:
684: IF NOT (lot_divisible
685: (p_inventory_item_id => p_to_inventory_item_id,
686: p_organization_id => p_to_organization_id)) THEN
824: IF fnd_api.to_boolean(p_init_msg_lst) THEN
825: fnd_msg_pub.initialize;
826: END IF;
827:
828: IF p_demand_type_id IN (inv_reservation_global.g_source_type_oe,
829: inv_reservation_global.g_source_type_internal_ord,
830: inv_reservation_global.g_source_type_rma) then
831:
832: --INVCONV - Retrieve ship tolerance above for lot indivisible scenarios
825: fnd_msg_pub.initialize;
826: END IF;
827:
828: IF p_demand_type_id IN (inv_reservation_global.g_source_type_oe,
829: inv_reservation_global.g_source_type_internal_ord,
830: inv_reservation_global.g_source_type_rma) then
831:
832: --INVCONV - Retrieve ship tolerance above for lot indivisible scenarios
833: BEGIN
826: END IF;
827:
828: IF p_demand_type_id IN (inv_reservation_global.g_source_type_oe,
829: inv_reservation_global.g_source_type_internal_ord,
830: inv_reservation_global.g_source_type_rma) then
831:
832: --INVCONV - Retrieve ship tolerance above for lot indivisible scenarios
833: BEGIN
834: SELECT inventory_item_id, ordered_quantity, order_quantity_uom, ship_from_org_id,
964: -- Requirement
965: -- In px_rsv_rec, inventory_item_id must be valid;
966: -- either primary_uom_code, primary_reservation_quantity
967: -- or reservation_uom_code, reservation_quantity must be not null and valid
968: PROCEDURE convert_quantity(x_return_status OUT NOCOPY VARCHAR2, px_rsv_rec IN OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type) IS
969: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
970: --l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
971: l_primary_uom_code VARCHAR2(3);
972: l_tmp_secondary_quantity NUMBER := NULL; -- INVCONV
966: -- either primary_uom_code, primary_reservation_quantity
967: -- or reservation_uom_code, reservation_quantity must be not null and valid
968: PROCEDURE convert_quantity(x_return_status OUT NOCOPY VARCHAR2, px_rsv_rec IN OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type) IS
969: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
970: --l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
971: l_primary_uom_code VARCHAR2(3);
972: l_tmp_secondary_quantity NUMBER := NULL; -- INVCONV
973: l_tmp_quantity NUMBER := NULL;
974: l_tracking_quantity_ind VARCHAR2(30); --INVCONV
1182: -- convert missing value in the input record
1183: -- to null in the output record. if the value of
1184: -- field in the input record is not missing, it
1185: -- would be copied to the output record
1186: PROCEDURE convert_missing_to_null(p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type, x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type) IS
1187: BEGIN
1188: IF p_rsv_rec.reservation_id <> fnd_api.g_miss_num THEN
1189: x_rsv_rec.reservation_id := p_rsv_rec.reservation_id;
1190: ELSE
1604: --
1605: -- Description
1606: -- return true if any attribute in the input record is missing
1607: -- else return false
1608: FUNCTION check_missing(p_rsv_rec IN OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type, x_what_field OUT NOCOPY VARCHAR2)
1609: RETURN BOOLEAN IS
1610: BEGIN
1611: x_what_field := NULL;
1612:
1885: -- in x_to_rsv_rec
1886: -- 2. else, copy the value of in the corresponding field in
1887: -- p_original_rsv_rec to the corresponding field in x_to_rsv_rec
1888: PROCEDURE construct_to_reservation_row(
1889: p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1890: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1891: , x_to_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
1892: ) IS
1893:
1886: -- 2. else, copy the value of in the corresponding field in
1887: -- p_original_rsv_rec to the corresponding field in x_to_rsv_rec
1888: PROCEDURE construct_to_reservation_row(
1889: p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1890: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1891: , x_to_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
1892: ) IS
1893:
1894: l_debug NUMBER;
1887: -- p_original_rsv_rec to the corresponding field in x_to_rsv_rec
1888: PROCEDURE construct_to_reservation_row(
1889: p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1890: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
1891: , x_to_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
1892: ) IS
1893:
1894: l_debug NUMBER;
1895: -- MUOM Fulfillment project
2435: -- in the cache which has a revision control code as yes
2436: FUNCTION is_revision_control(p_item_cache_index IN INTEGER)
2437: RETURN BOOLEAN IS
2438: BEGIN
2439: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).revision_qty_control_code = inv_reservation_global.g_revision_control_yes THEN
2440: RETURN TRUE;
2441: ELSE
2442: RETURN FALSE;
2443: END IF;
2449: -- in the cache which has a lot control code as yes
2450: FUNCTION is_lot_control(p_item_cache_index IN INTEGER)
2451: RETURN BOOLEAN IS
2452: BEGIN
2453: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code = inv_reservation_global.g_lot_control_yes THEN
2454: RETURN TRUE;
2455: ELSE
2456: RETURN FALSE;
2457: END IF;
2463: -- in the cache which has a serial control code as yes
2464: FUNCTION is_serial_control(p_item_cache_index IN INTEGER)
2465: RETURN BOOLEAN IS
2466: BEGIN
2467: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).serial_number_control_code <> inv_reservation_global.g_serial_control_predefined THEN
2468: RETURN FALSE;
2469: ELSE
2470: RETURN TRUE;
2471: END IF;
2480: FUNCTION is_dual_control(p_item_cache_index IN INTEGER)
2481: RETURN BOOLEAN IS
2482: --l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2483: BEGIN
2484: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).tracking_quantity_ind <> 'PS' THEN
2485: RETURN FALSE;
2486: ELSE
2487: RETURN TRUE;
2488: END IF;
2501: END IF;
2502:
2503: l_debug := g_debug;
2504:
2505: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code
2506: = inv_reservation_global.g_lot_control_yes AND
2507: inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_divisible_flag <> 'Y' THEN
2508: IF (l_debug = 1) THEN
2509: debug_print('Lot divisible is FALSE ');
2502:
2503: l_debug := g_debug;
2504:
2505: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code
2506: = inv_reservation_global.g_lot_control_yes AND
2507: inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_divisible_flag <> 'Y' THEN
2508: IF (l_debug = 1) THEN
2509: debug_print('Lot divisible is FALSE ');
2510: END IF;
2503: l_debug := g_debug;
2504:
2505: IF inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code
2506: = inv_reservation_global.g_lot_control_yes AND
2507: inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_divisible_flag <> 'Y' THEN
2508: IF (l_debug = 1) THEN
2509: debug_print('Lot divisible is FALSE ');
2510: END IF;
2511: RETURN FALSE;
2575: , x_msg_count OUT NOCOPY NUMBER
2576: , x_msg_data OUT NOCOPY VARCHAR2
2577: , p_organization_id IN NUMBER DEFAULT null
2578: , p_item_id IN NUMBER DEFAULT null
2579: , p_source_type_id IN NUMBER DEFAULT INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP
2580: , p_source_header_id IN NUMBER
2581: , p_source_line_id IN NUMBER
2582: , p_source_line_detail IN NUMBER
2583: , x_wip_entity_type OUT NOCOPY NUMBER
2597: END IF;
2598:
2599: l_debug := g_debug;
2600:
2601: IF (p_source_type_id <> INV_RESERVATION_GLOBAL.g_source_type_wip) THEN
2602: fnd_message.set_name('INV', 'INV_INVALID_SUPPLY_SOURCE');
2603: fnd_msg_pub.add;
2604: RAISE fnd_api.g_exc_error;
2605: END IF;
2618: fnd_msg_pub.add;
2619: RAISE fnd_api.g_exc_error;
2620: END ;
2621:
2622: IF l_wip_entity_id = inv_reservation_global.g_wip_source_type_discrete then
2623: l_wip_entity_type := inv_reservation_global.g_wip_source_type_discrete;
2624: l_wip_job_type := 'DISCRETE';
2625: ELSIF l_wip_entity_id = 2 then
2626: l_wip_entity_type := inv_reservation_global.g_wip_source_type_repetitive;
2619: RAISE fnd_api.g_exc_error;
2620: END ;
2621:
2622: IF l_wip_entity_id = inv_reservation_global.g_wip_source_type_discrete then
2623: l_wip_entity_type := inv_reservation_global.g_wip_source_type_discrete;
2624: l_wip_job_type := 'DISCRETE';
2625: ELSIF l_wip_entity_id = 2 then
2626: l_wip_entity_type := inv_reservation_global.g_wip_source_type_repetitive;
2627: l_wip_job_type := 'REPETITIVE';
2622: IF l_wip_entity_id = inv_reservation_global.g_wip_source_type_discrete then
2623: l_wip_entity_type := inv_reservation_global.g_wip_source_type_discrete;
2624: l_wip_job_type := 'DISCRETE';
2625: ELSIF l_wip_entity_id = 2 then
2626: l_wip_entity_type := inv_reservation_global.g_wip_source_type_repetitive;
2627: l_wip_job_type := 'REPETITIVE';
2628: ELSIF l_wip_entity_id = 4 then
2629: l_wip_entity_type := inv_reservation_global.g_wip_source_type_flow;
2630: l_wip_job_type := 'FLOW';
2625: ELSIF l_wip_entity_id = 2 then
2626: l_wip_entity_type := inv_reservation_global.g_wip_source_type_repetitive;
2627: l_wip_job_type := 'REPETITIVE';
2628: ELSIF l_wip_entity_id = 4 then
2629: l_wip_entity_type := inv_reservation_global.g_wip_source_type_flow;
2630: l_wip_job_type := 'FLOW';
2631: ELSIF l_wip_entity_id = 5 then
2632: l_wip_entity_type := inv_reservation_global.g_wip_source_type_osfm;
2633: l_wip_job_type := 'OSFM';
2628: ELSIF l_wip_entity_id = 4 then
2629: l_wip_entity_type := inv_reservation_global.g_wip_source_type_flow;
2630: l_wip_job_type := 'FLOW';
2631: ELSIF l_wip_entity_id = 5 then
2632: l_wip_entity_type := inv_reservation_global.g_wip_source_type_osfm;
2633: l_wip_job_type := 'OSFM';
2634: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 1 then
2635: l_wip_entity_type := inv_reservation_global.g_wip_source_type_eam;
2636: l_wip_job_type := 'EAM';
2631: ELSIF l_wip_entity_id = 5 then
2632: l_wip_entity_type := inv_reservation_global.g_wip_source_type_osfm;
2633: l_wip_job_type := 'OSFM';
2634: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 1 then
2635: l_wip_entity_type := inv_reservation_global.g_wip_source_type_eam;
2636: l_wip_job_type := 'EAM';
2637: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
2638: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
2639: l_wip_job_type := 'CMRO'; -- AHL
2634: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 1 then
2635: l_wip_entity_type := inv_reservation_global.g_wip_source_type_eam;
2636: l_wip_job_type := 'EAM';
2637: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
2638: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
2639: l_wip_job_type := 'CMRO'; -- AHL
2640: ELSIF l_wip_entity_id = 9 then
2641: l_wip_entity_type := inv_reservation_global.g_wip_source_type_fpo;
2642: l_wip_job_type := 'FPO';
2637: ELSIF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
2638: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
2639: l_wip_job_type := 'CMRO'; -- AHL
2640: ELSIF l_wip_entity_id = 9 then
2641: l_wip_entity_type := inv_reservation_global.g_wip_source_type_fpo;
2642: l_wip_job_type := 'FPO';
2643: ELSIF l_wip_entity_id = 10 then
2644: l_wip_entity_type := inv_reservation_global.g_wip_source_type_batch;
2645: l_wip_job_type := 'BATCH';
2640: ELSIF l_wip_entity_id = 9 then
2641: l_wip_entity_type := inv_reservation_global.g_wip_source_type_fpo;
2642: l_wip_job_type := 'FPO';
2643: ELSIF l_wip_entity_id = 10 then
2644: l_wip_entity_type := inv_reservation_global.g_wip_source_type_batch;
2645: l_wip_job_type := 'BATCH';
2646: ELSIF l_wip_entity_id = 16 THEN
2647: l_wip_entity_type := inv_reservation_global.g_wip_source_type_depot;
2648: l_wip_job_type := 'DEPOT';
2643: ELSIF l_wip_entity_id = 10 then
2644: l_wip_entity_type := inv_reservation_global.g_wip_source_type_batch;
2645: l_wip_job_type := 'BATCH';
2646: ELSIF l_wip_entity_id = 16 THEN
2647: l_wip_entity_type := inv_reservation_global.g_wip_source_type_depot;
2648: l_wip_job_type := 'DEPOT';
2649: END IF;
2650:
2651: x_wip_entity_type := l_wip_entity_type;
2731: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
2732: , x_return_status OUT NOCOPY VARCHAR2
2733: , x_msg_count OUT NOCOPY NUMBER
2734: , x_msg_data OUT NOCOPY VARCHAR2
2735: , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
2736: , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table)
2737: IS
2738: l_api_name CONSTANT VARCHAR2(30) := 'is_serial_number_reserved';
2739: l_debug NUMBER;
2732: , x_return_status OUT NOCOPY VARCHAR2
2733: , x_msg_count OUT NOCOPY NUMBER
2734: , x_msg_data OUT NOCOPY VARCHAR2
2735: , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
2736: , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table)
2737: IS
2738: l_api_name CONSTANT VARCHAR2(30) := 'is_serial_number_reserved';
2739: l_debug NUMBER;
2740: l_index NUMBER := 0;
2805: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
2806: , x_return_status OUT NOCOPY VARCHAR2
2807: , x_msg_count OUT NOCOPY NUMBER
2808: , x_msg_data OUT NOCOPY VARCHAR2
2809: , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
2810: , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table
2811: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type)
2812: IS
2813: l_api_name CONSTANT VARCHAR2(30) := 'is_serial_number_reserved';
2806: , x_return_status OUT NOCOPY VARCHAR2
2807: , x_msg_count OUT NOCOPY NUMBER
2808: , x_msg_data OUT NOCOPY VARCHAR2
2809: , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
2810: , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table
2811: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type)
2812: IS
2813: l_api_name CONSTANT VARCHAR2(30) := 'is_serial_number_reserved';
2814: l_debug NUMBER;
2807: , x_msg_count OUT NOCOPY NUMBER
2808: , x_msg_data OUT NOCOPY VARCHAR2
2809: , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
2810: , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table
2811: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type)
2812: IS
2813: l_api_name CONSTANT VARCHAR2(30) := 'is_serial_number_reserved';
2814: l_debug NUMBER;
2815: l_index NUMBER := 0;
3352: IF l_dual_control THEN -- INVCONV
3353: l_secondary_net_qty2 := p_to_second_rsv_quantity - NVL(p_to_second_detailed_quantity, 0); -- INVCONV
3354: END IF; -- INVCONV
3355: --
3356: IF p_from_supply_source_type_id = inv_reservation_global.g_source_type_inv THEN
3357: -- INVCONV - Incorporate secondaries
3358: inv_quantity_tree_pvt.update_quantities(
3359: p_api_version_number => 1.0
3360: , p_init_msg_lst => fnd_api.g_true
3402:
3403: --
3404: l_modify_tree2 := FALSE;
3405:
3406: IF p_to_supply_source_type_id = inv_reservation_global.g_source_type_inv
3407: AND l_modify_tree1
3408: AND p_to_tree_id IS NOT NULL
3409: AND p_from_tree_id <> p_to_tree_id THEN
3410: l_modify_tree2 := TRUE;
3514: END IF;
3515: --Start changes for Bug Number#3336837
3516: -- Bug Number 3447373 Removed the qty validation for transfers
3517:
3518: IF p_action='UPDATE' AND p_from_supply_source_type_id = inv_reservation_global.g_source_type_inv then
3519: IF (l_debug = 1) THEN
3520: debug_print('Validate Qty :Action Update');
3521: END IF;
3522:
3849: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
3850: , x_return_status OUT NOCOPY VARCHAR2
3851: , x_msg_count OUT NOCOPY NUMBER
3852: , x_msg_data OUT NOCOPY VARCHAR2
3853: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
3854: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
3855: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
3856: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
3857: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
3851: , x_msg_count OUT NOCOPY NUMBER
3852: , x_msg_data OUT NOCOPY VARCHAR2
3853: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
3854: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
3855: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
3856: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
3857: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
3858: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
3859: , x_error_code OUT NOCOPY NUMBER
3852: , x_msg_data OUT NOCOPY VARCHAR2
3853: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
3854: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
3855: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
3856: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
3857: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
3858: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
3859: , x_error_code OUT NOCOPY NUMBER
3860: ) IS
3853: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
3854: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
3855: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
3856: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
3857: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
3858: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
3859: , x_error_code OUT NOCOPY NUMBER
3860: ) IS
3861: l_api_version_number CONSTANT NUMBER := 1.0;
3861: l_api_version_number CONSTANT NUMBER := 1.0;
3862: l_api_name CONSTANT VARCHAR2(30) := 'Query_Reservation';
3863: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3864: l_counter INTEGER;
3865: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
3866: l_sort_stmt VARCHAR2(2000);
3867: l_lock_stmt VARCHAR2(60);
3868: -- Bug 4881317
3869: l_qry_stmt VARCHAR2(15000);
4742:
4743:
4744: ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
4745: BEGIN
4746: x_error_code := inv_reservation_global.g_err_unexpected;
4747:
4748: -- Use cache to get value for l_debug
4749: IF g_is_pickrelease_set IS NULL THEN
4750: g_is_pickrelease_set := 2;
4791: -- 1. By requirement_date - asc or desc (For WIP)
4792: -- 2. By ship_ready_flag, detailed quantity (For OE/OM)
4793: -- 3. By revision,lot,sub,locator - high level reservations 1st(For default)
4794: --
4795: IF p_sort_by_req_date = inv_reservation_global.g_query_req_date_asc THEN
4796: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE ASC ';
4797: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_req_date_desc THEN
4798: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE DESC ';
4799: /**** {{ R12 Enhanced reservations code changes. Adding new sort by conditions }}****/
4793: -- 3. By revision,lot,sub,locator - high level reservations 1st(For default)
4794: --
4795: IF p_sort_by_req_date = inv_reservation_global.g_query_req_date_asc THEN
4796: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE ASC ';
4797: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_req_date_desc THEN
4798: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE DESC ';
4799: /**** {{ R12 Enhanced reservations code changes. Adding new sort by conditions }}****/
4800: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_asc THEN
4801: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4796: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE ASC ';
4797: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_req_date_desc THEN
4798: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE DESC ';
4799: /**** {{ R12 Enhanced reservations code changes. Adding new sort by conditions }}****/
4800: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_asc THEN
4801: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4802: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_desc THEN
4803: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4804: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4798: l_sort_stmt := ' ORDER BY REQUIREMENT_DATE DESC ';
4799: /**** {{ R12 Enhanced reservations code changes. Adding new sort by conditions }}****/
4800: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_asc THEN
4801: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4802: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_desc THEN
4803: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4804: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4805: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4806: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4800: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_asc THEN
4801: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4802: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_desc THEN
4803: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4804: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4805: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4806: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4807: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4808: /*** End R12 ***/
4802: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_demand_ship_date_desc THEN
4803: l_sort_stmt := ' ORDER BY NVL(DEMAND_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4804: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4805: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) ASC ';
4806: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4807: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4808: /*** End R12 ***/
4809:
4810: ELSIF p_cancel_order_mode = inv_reservation_global.g_cancel_order_yes THEN
4806: ELSIF p_sort_by_req_date = inv_reservation_global.g_query_supply_rcpt_date_asc THEN
4807: l_sort_stmt := ' ORDER BY NVL(SUPPLY_SHIP_DATE,REQUIREMENT_DATE) DESC ';
4808: /*** End R12 ***/
4809:
4810: ELSIF p_cancel_order_mode = inv_reservation_global.g_cancel_order_yes THEN
4811: l_sort_stmt := ' ORDER BY NVL(SHIP_READY_FLAG,2) DESC, ' || 'NVL(DETAILED_QUANTITY,0) ';
4812: ELSE
4813: -- Default order by - High level followed by detail
4814: l_sort_default := TRUE;
7787:
7788: IF (l_debug = 1) then
7789: debug_print(' Counter: ' || l_counter);
7790: debug_print(' return status: ' || l_return_status);
7791: debug_print('error code' || inv_reservation_global.g_err_no_error);
7792: END IF;
7793: --
7794: x_mtl_reservation_tbl_count := l_counter;
7795: x_return_status := l_return_status;
7792: END IF;
7793: --
7794: x_mtl_reservation_tbl_count := l_counter;
7795: x_return_status := l_return_status;
7796: x_error_code := inv_reservation_global.g_err_no_error;
7797: --
7798: EXCEPTION
7799: WHEN fnd_api.g_exc_error THEN
7800: x_return_status := fnd_api.g_ret_sts_error;
7806: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
7807: WHEN OTHERS THEN
7808: IF SQLCODE = -54 THEN -- failed to lock
7809: x_return_status := fnd_api.g_ret_sts_error;
7810: x_error_code := inv_reservation_global.g_err_fail_to_lock_rec;
7811: ELSE
7812: x_return_status := fnd_api.g_ret_sts_unexp_error;
7813:
7814: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
7830: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
7831: , x_return_status OUT NOCOPY VARCHAR2
7832: , x_msg_count OUT NOCOPY NUMBER
7833: , x_msg_data OUT NOCOPY VARCHAR2
7834: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
7835: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
7836: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7832: , x_msg_count OUT NOCOPY NUMBER
7833: , x_msg_data OUT NOCOPY VARCHAR2
7834: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
7835: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
7836: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7839: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
7840: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
7833: , x_msg_data OUT NOCOPY VARCHAR2
7834: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
7835: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
7836: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7839: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
7840: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
7841: , x_serial_number_table OUT NOCOPY inv_reservation_global.rsv_serial_number_table
7834: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
7835: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
7836: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7839: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
7840: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
7841: , x_serial_number_table OUT NOCOPY inv_reservation_global.rsv_serial_number_table
7842: , x_serial_number_table_count OUT NOCOPY NUMBER
7835: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
7836: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7839: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
7840: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
7841: , x_serial_number_table OUT NOCOPY inv_reservation_global.rsv_serial_number_table
7842: , x_serial_number_table_count OUT NOCOPY NUMBER
7843: , x_error_code OUT NOCOPY NUMBER
7837: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
7838: , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
7839: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
7840: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
7841: , x_serial_number_table OUT NOCOPY inv_reservation_global.rsv_serial_number_table
7842: , x_serial_number_table_count OUT NOCOPY NUMBER
7843: , x_error_code OUT NOCOPY NUMBER
7844: ) IS
7845:
7843: , x_error_code OUT NOCOPY NUMBER
7844: ) IS
7845:
7846: l_api_name CONSTANT VARCHAR2(30) := 'Query_Reservation';
7847: l_serial_number_table inv_reservation_global.rsv_serial_number_table;
7848: l_serial_table_index BINARY_INTEGER;
7849: l_output_index BINARY_INTEGER;
7850: l_reservation_index BINARY_INTEGER;
7851: l_reservation_id NUMBER;
7852: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
7853: l_debug NUMBER;
7854: l_serial_number varchar2(30);
7855: l_mtl_reservation_tbl
7856: inv_reservation_global.mtl_reservation_tbl_type;
7857: l_progress NUMBER;
7858: l_mtl_reservation_tbl_count NUMBER;
7859: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
7860: l_error_code NUMBER;
7855: l_mtl_reservation_tbl
7856: inv_reservation_global.mtl_reservation_tbl_type;
7857: l_progress NUMBER;
7858: l_mtl_reservation_tbl_count NUMBER;
7859: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
7860: l_error_code NUMBER;
7861: l_serial_number_table_temp inv_reservation_global.rsv_serial_number_table; --Bug# 13479815
7862:
7863: BEGIN
7857: l_progress NUMBER;
7858: l_mtl_reservation_tbl_count NUMBER;
7859: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
7860: l_error_code NUMBER;
7861: l_serial_number_table_temp inv_reservation_global.rsv_serial_number_table; --Bug# 13479815
7862:
7863: BEGIN
7864:
7865: -- The new API will be called to query a set of serial numbers or if
8008:
8009: x_mtl_reservation_tbl := l_mtl_reservation_tbl;
8010: x_mtl_reservation_tbl_count := l_mtl_reservation_tbl_count;
8011: x_return_status := l_return_status;
8012: x_error_code := inv_reservation_global.g_err_no_error;
8013: x_serial_number_table := l_serial_number_table;
8014: x_serial_number_table_count := l_serial_number_table.COUNT;
8015:
8016: EXCEPTION
8024: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
8025: WHEN OTHERS THEN
8026: IF SQLCODE = -54 THEN -- failed to lock
8027: x_return_status := fnd_api.g_ret_sts_error;
8028: x_error_code := inv_reservation_global.g_err_fail_to_lock_rec;
8029: ELSE
8030: x_return_status := fnd_api.g_ret_sts_unexp_error;
8031:
8032: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
8046: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
8047: , x_return_status OUT NOCOPY VARCHAR2
8048: , x_msg_count OUT NOCOPY NUMBER
8049: , x_msg_data OUT NOCOPY VARCHAR2
8050: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
8051: , p_serial_number IN inv_reservation_global.serial_number_tbl_type
8052: , x_serial_number OUT NOCOPY inv_reservation_global.serial_number_tbl_type
8053: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8054: , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8047: , x_return_status OUT NOCOPY VARCHAR2
8048: , x_msg_count OUT NOCOPY NUMBER
8049: , x_msg_data OUT NOCOPY VARCHAR2
8050: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
8051: , p_serial_number IN inv_reservation_global.serial_number_tbl_type
8052: , x_serial_number OUT NOCOPY inv_reservation_global.serial_number_tbl_type
8053: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8054: , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8055: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
8048: , x_msg_count OUT NOCOPY NUMBER
8049: , x_msg_data OUT NOCOPY VARCHAR2
8050: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
8051: , p_serial_number IN inv_reservation_global.serial_number_tbl_type
8052: , x_serial_number OUT NOCOPY inv_reservation_global.serial_number_tbl_type
8053: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8054: , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
8055: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
8056: , p_over_reservation_flag IN NUMBER DEFAULT 0
8065: ) IS
8066: l_api_version_number CONSTANT NUMBER := 1.0;
8067: l_api_name CONSTANT VARCHAR2(30) := 'Create_Reservation';
8068: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
8069: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8070: l_actual_primary_quantity NUMBER := NULL;
8071: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
8072: l_tmp_rsv_tbl_count NUMBER;
8073: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8067: l_api_name CONSTANT VARCHAR2(30) := 'Create_Reservation';
8068: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
8069: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8070: l_actual_primary_quantity NUMBER := NULL;
8071: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
8072: l_tmp_rsv_tbl_count NUMBER;
8073: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8074: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
8075: l_tree_id INTEGER;
8069: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8070: l_actual_primary_quantity NUMBER := NULL;
8071: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
8072: l_tmp_rsv_tbl_count NUMBER;
8073: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8074: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
8075: l_tree_id INTEGER;
8076: l_orig_item_cache_index INTEGER := NULL;
8077: l_orig_org_cache_index INTEGER := NULL;
8070: l_actual_primary_quantity NUMBER := NULL;
8071: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
8072: l_tmp_rsv_tbl_count NUMBER;
8073: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8074: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
8075: l_tree_id INTEGER;
8076: l_orig_item_cache_index INTEGER := NULL;
8077: l_orig_org_cache_index INTEGER := NULL;
8078: l_orig_demand_cache_index INTEGER := NULL;
8101: :='N'; -- INVCONV
8102: /**** {{ R12 Enhanced reservations code changes }}****/
8103: l_rsv_updated BOOLEAN :=FALSE;
8104: l_progress NUMBER;
8105: -- l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type
8106: -- := p_rsv_rec;
8107: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8108: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
8109: l_mtl_reservation_tbl_count NUMBER;
8103: l_rsv_updated BOOLEAN :=FALSE;
8104: l_progress NUMBER;
8105: -- l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type
8106: -- := p_rsv_rec;
8107: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8108: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
8109: l_mtl_reservation_tbl_count NUMBER;
8110: l_quantity_reserved NUMBER;
8111: l_secondary_quantity_reserved NUMBER;
8104: l_progress NUMBER;
8105: -- l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type
8106: -- := p_rsv_rec;
8107: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
8108: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
8109: l_mtl_reservation_tbl_count NUMBER;
8110: l_quantity_reserved NUMBER;
8111: l_secondary_quantity_reserved NUMBER;
8112: l_qty_available NUMBER;
8110: l_quantity_reserved NUMBER;
8111: l_secondary_quantity_reserved NUMBER;
8112: l_qty_available NUMBER;
8113: l_serial_index NUMBER;
8114: l_serial_number inv_reservation_global.serial_number_tbl_type;
8115: l_item_rec inv_reservation_global.item_record;
8116: l_supply_lock_handle varchar2(128);
8117: l_demand_lock_handle varchar2(128);
8118: l_lock_status NUMBER;
8111: l_secondary_quantity_reserved NUMBER;
8112: l_qty_available NUMBER;
8113: l_serial_index NUMBER;
8114: l_serial_number inv_reservation_global.serial_number_tbl_type;
8115: l_item_rec inv_reservation_global.item_record;
8116: l_supply_lock_handle varchar2(128);
8117: l_demand_lock_handle varchar2(128);
8118: l_lock_status NUMBER;
8119: l_group_mark_id NUMBER := NULL;
8464: -- the line level. Also, for ASN, pass the source type as PO so that the
8465: -- the lock name would be the same as the PO's
8466:
8467: IF (l_rsv_rec.supply_source_type_id =
8468: inv_reservation_global.g_source_type_asn) THEN
8469: l_supply_source_type_id :=
8470: inv_reservation_global.g_source_type_po;
8471: ELSE
8472: l_supply_source_type_id := l_rsv_rec.supply_source_type_id;
8466:
8467: IF (l_rsv_rec.supply_source_type_id =
8468: inv_reservation_global.g_source_type_asn) THEN
8469: l_supply_source_type_id :=
8470: inv_reservation_global.g_source_type_po;
8471: ELSE
8472: l_supply_source_type_id := l_rsv_rec.supply_source_type_id;
8473: END IF;
8474:
8471: ELSE
8472: l_supply_source_type_id := l_rsv_rec.supply_source_type_id;
8473: END IF;
8474:
8475: IF (l_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
8476: inv_reservation_lock_pvt.lock_supply_demand_record
8477: (p_organization_id => l_rsv_rec.organization_id
8478: ,p_inventory_item_id => l_rsv_rec.inventory_item_id
8479: ,p_source_type_id => l_supply_source_type_id
8693: END IF;
8694: END;
8695: END IF;
8696: IF (l_rsv_rec.demand_source_type_id IN
8697: (inv_reservation_global.g_source_type_oe,
8698: inv_reservation_global.g_source_type_internal_ord,
8699: inv_reservation_global.g_source_type_rma)) AND
8700: (l_pjm_enabled = 1) THEN
8701:
8694: END;
8695: END IF;
8696: IF (l_rsv_rec.demand_source_type_id IN
8697: (inv_reservation_global.g_source_type_oe,
8698: inv_reservation_global.g_source_type_internal_ord,
8699: inv_reservation_global.g_source_type_rma)) AND
8700: (l_pjm_enabled = 1) THEN
8701:
8702: IF (l_rsv_rec.demand_source_line_id IS NOT NULL) AND
8695: END IF;
8696: IF (l_rsv_rec.demand_source_type_id IN
8697: (inv_reservation_global.g_source_type_oe,
8698: inv_reservation_global.g_source_type_internal_ord,
8699: inv_reservation_global.g_source_type_rma)) AND
8700: (l_pjm_enabled = 1) THEN
8701:
8702: IF (l_rsv_rec.demand_source_line_id IS NOT NULL) AND
8703: (l_rsv_rec.demand_source_line_id <> fnd_api.g_miss_num)
8728: END IF;
8729: END IF;
8730:
8731: /* Added for bug 13829182 */
8732: ELSIF ( (l_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) AND (l_pjm_enabled = 1)) THEN
8733:
8734: BEGIN
8735: SELECT we.entity_type, wdj.maintenance_object_source
8736: INTO l_wip_entity_id, l_maintenance_object_source
8744: END IF;
8745: END ;
8746:
8747: IF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
8748: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
8749: l_wip_job_type := 'CMRO'; -- AHL
8750: ELSE
8751: l_wip_entity_type := null;
8752: l_wip_job_type := null; -- AHL
8850: END IF;
8851:
8852: --
8853: -- Pre Insert CTO Validation
8854: IF l_rsv_rec.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
8855: --
8856: cto_workflow_api_pk.inventory_reservation_check(p_order_line_id => l_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
8857:
8858: IF (l_debug = 1) THEN
8891: -- will call quantity validation for other supply sources in the future
8892: -- Bug 2354735: Proceed with Trees only if p_validation_flag = 'T'
8893: IF p_force_reservation_flag <> fnd_api.g_true
8894: AND p_validation_flag = fnd_api.g_true
8895: AND l_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv THEN
8896: inv_quantity_tree_pvt.create_tree
8897: (
8898: p_api_version_number => 1.0
8899: , p_init_msg_lst => fnd_api.g_true
9061: /*** End R12 ***/
9062:
9063: /**** {{ R12 Enhanced reservations code changes }}****/
9064: ELSIF (l_rsv_rec.supply_source_type_id IN
9065: (inv_reservation_global.g_source_type_wip,
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9062:
9063: /**** {{ R12 Enhanced reservations code changes }}****/
9064: ELSIF (l_rsv_rec.supply_source_type_id IN
9065: (inv_reservation_global.g_source_type_wip,
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9070: inv_reservation_global.g_source_type_rcv)) AND
9063: /**** {{ R12 Enhanced reservations code changes }}****/
9064: ELSIF (l_rsv_rec.supply_source_type_id IN
9065: (inv_reservation_global.g_source_type_wip,
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9070: inv_reservation_global.g_source_type_rcv)) AND
9071: p_over_reservation_flag NOT IN (1,3) THEN
9064: ELSIF (l_rsv_rec.supply_source_type_id IN
9065: (inv_reservation_global.g_source_type_wip,
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9070: inv_reservation_global.g_source_type_rcv)) AND
9071: p_over_reservation_flag NOT IN (1,3) THEN
9072: -- call the available to reserve API to get the supply and
9065: (inv_reservation_global.g_source_type_wip,
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9070: inv_reservation_global.g_source_type_rcv)) AND
9071: p_over_reservation_flag NOT IN (1,3) THEN
9072: -- call the available to reserve API to get the supply and
9073: -- demand availability
9066: inv_reservation_global.g_source_type_po,
9067: inv_reservation_global.g_source_type_asn,
9068: inv_reservation_global.g_source_type_intransit,
9069: inv_reservation_global.g_source_type_internal_req,
9070: inv_reservation_global.g_source_type_rcv)) AND
9071: p_over_reservation_flag NOT IN (1,3) THEN
9072: -- call the available to reserve API to get the supply and
9073: -- demand availability
9074: -- Bug 5199672: Should pass g_miss_num as default for supply
9159: END IF;
9160: END IF;
9161:
9162: IF (l_rsv_rec.demand_source_type_id IN
9163: (inv_reservation_global.g_source_type_wip,
9164: inv_reservation_global.g_source_type_oe,
9165: inv_reservation_global.g_source_type_internal_ord,
9166: inv_reservation_global.g_source_type_rma)) AND
9167: p_over_reservation_flag NOT IN (2,3) THEN
9160: END IF;
9161:
9162: IF (l_rsv_rec.demand_source_type_id IN
9163: (inv_reservation_global.g_source_type_wip,
9164: inv_reservation_global.g_source_type_oe,
9165: inv_reservation_global.g_source_type_internal_ord,
9166: inv_reservation_global.g_source_type_rma)) AND
9167: p_over_reservation_flag NOT IN (2,3) THEN
9168:
9161:
9162: IF (l_rsv_rec.demand_source_type_id IN
9163: (inv_reservation_global.g_source_type_wip,
9164: inv_reservation_global.g_source_type_oe,
9165: inv_reservation_global.g_source_type_internal_ord,
9166: inv_reservation_global.g_source_type_rma)) AND
9167: p_over_reservation_flag NOT IN (2,3) THEN
9168:
9169: -- Bug 5199672: Should pass g_miss_num as default for demand
9162: IF (l_rsv_rec.demand_source_type_id IN
9163: (inv_reservation_global.g_source_type_wip,
9164: inv_reservation_global.g_source_type_oe,
9165: inv_reservation_global.g_source_type_internal_ord,
9166: inv_reservation_global.g_source_type_rma)) AND
9167: p_over_reservation_flag NOT IN (2,3) THEN
9168:
9169: -- Bug 5199672: Should pass g_miss_num as default for demand
9170: -- source line detail. Otherwise, high level reservations
9213: RAISE fnd_api.g_exc_unexpected_error;
9214: END IF;
9215:
9216:
9217: IF (l_rsv_rec.demand_source_type_id in (inv_reservation_global.g_source_type_oe,
9218: inv_reservation_global.g_source_type_internal_ord,
9219: inv_reservation_global.g_source_type_rma)) THEN
9220:
9221: IF NOT
9214: END IF;
9215:
9216:
9217: IF (l_rsv_rec.demand_source_type_id in (inv_reservation_global.g_source_type_oe,
9218: inv_reservation_global.g_source_type_internal_ord,
9219: inv_reservation_global.g_source_type_rma)) THEN
9220:
9221: IF NOT
9222: (lot_divisible
9215:
9216:
9217: IF (l_rsv_rec.demand_source_type_id in (inv_reservation_global.g_source_type_oe,
9218: inv_reservation_global.g_source_type_internal_ord,
9219: inv_reservation_global.g_source_type_rma)) THEN
9220:
9221: IF NOT
9222: (lot_divisible
9223: (p_inventory_item_id => l_rsv_rec.inventory_item_id,
9631:
9632: /*** End R12 ***/
9633: --
9634: -- Post Insert CTO Validation
9635: IF l_rsv_rec.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
9636: --
9637: cto_workflow_api_pk.wf_update_after_inv_reserv(p_order_line_id => l_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
9638:
9639: IF (l_debug = 1) THEN
9777: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
9778: , x_return_status OUT NOCOPY VARCHAR2
9779: , x_msg_count OUT NOCOPY NUMBER
9780: , x_msg_data OUT NOCOPY VARCHAR2
9781: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9782: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9783: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9784: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9785: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9778: , x_return_status OUT NOCOPY VARCHAR2
9779: , x_msg_count OUT NOCOPY NUMBER
9780: , x_msg_data OUT NOCOPY VARCHAR2
9781: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9782: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9783: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9784: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9785: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9786: , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
9779: , x_msg_count OUT NOCOPY NUMBER
9780: , x_msg_data OUT NOCOPY VARCHAR2
9781: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9782: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9783: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9784: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9785: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9786: , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
9787: , p_over_reservation_flag IN NUMBER DEFAULT 0
9780: , x_msg_data OUT NOCOPY VARCHAR2
9781: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9782: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9783: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9784: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9785: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9786: , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
9787: , p_over_reservation_flag IN NUMBER DEFAULT 0
9788: ) IS
9923: , x_msg_count OUT NOCOPY NUMBER
9924: , x_msg_data OUT NOCOPY VARCHAR2
9925: , x_quantity_reserved OUT NOCOPY NUMBER
9926: , x_secondary_quantity_reserved OUT NOCOPY NUMBER
9927: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9928: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9929: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9930: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9931: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9924: , x_msg_data OUT NOCOPY VARCHAR2
9925: , x_quantity_reserved OUT NOCOPY NUMBER
9926: , x_secondary_quantity_reserved OUT NOCOPY NUMBER
9927: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9928: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9929: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9930: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9931: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9932: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
9925: , x_quantity_reserved OUT NOCOPY NUMBER
9926: , x_secondary_quantity_reserved OUT NOCOPY NUMBER
9927: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9928: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9929: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9930: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9931: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9932: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
9933: , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
9926: , x_secondary_quantity_reserved OUT NOCOPY NUMBER
9927: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9928: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
9929: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
9930: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
9931: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
9932: , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
9933: , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
9934: , p_over_reservation_flag IN NUMBER DEFAULT 0
9937: l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
9938: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
9939: l_miss_num NUMBER := fnd_api.g_miss_num;
9940: l_miss_char VARCHAR2(1) := fnd_api.g_miss_char;
9941: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9942: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9943: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9944: l_orig_rsv_tbl_count NUMBER;
9945: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9938: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
9939: l_miss_num NUMBER := fnd_api.g_miss_num;
9940: l_miss_char VARCHAR2(1) := fnd_api.g_miss_char;
9941: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9942: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9943: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9944: l_orig_rsv_tbl_count NUMBER;
9945: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9946: --
9939: l_miss_num NUMBER := fnd_api.g_miss_num;
9940: l_miss_char VARCHAR2(1) := fnd_api.g_miss_char;
9941: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9942: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9943: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9944: l_orig_rsv_tbl_count NUMBER;
9945: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9946: --
9947: l_to_rsv_tbl_count NUMBER;
9941: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9942: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9943: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9944: l_orig_rsv_tbl_count NUMBER;
9945: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
9946: --
9947: l_to_rsv_tbl_count NUMBER;
9948: l_tree_id1 INTEGER;
9949: l_tree_id2 INTEGER;
9972: l_lot_divisible_flag VARCHAR2(1) := 'Y' ; -- INVCONV
9973: l_dual_control_flag VARCHAR2(1) := 'N' ;
9974: -- INVCONV
9975: /**** {{ R12 Enhanced reservations code changes }}****/
9976: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
9977: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
9978: l_qty_available NUMBER := 0;
9979: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
9980: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
9973: l_dual_control_flag VARCHAR2(1) := 'N' ;
9974: -- INVCONV
9975: /**** {{ R12 Enhanced reservations code changes }}****/
9976: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
9977: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
9978: l_qty_available NUMBER := 0;
9979: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
9980: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
9981: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9975: /**** {{ R12 Enhanced reservations code changes }}****/
9976: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
9977: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
9978: l_qty_available NUMBER := 0;
9979: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
9980: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
9981: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9982: l_serials_tobe_unreserved NUMBER;
9983: l_total_serials_reserved NUMBER;
9976: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
9977: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
9978: l_qty_available NUMBER := 0;
9979: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
9980: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
9981: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9982: l_serials_tobe_unreserved NUMBER;
9983: l_total_serials_reserved NUMBER;
9984: l_reservation_id NUMBER;
9977: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
9978: l_qty_available NUMBER := 0;
9979: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
9980: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
9981: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
9982: l_serials_tobe_unreserved NUMBER;
9983: l_total_serials_reserved NUMBER;
9984: l_reservation_id NUMBER;
9985: l_original_serial_count NUMBER;
10015: SELECT *
10016: FROM mtl_system_items
10017: WHERE inventory_Item_Id = p_inventory_item_id
10018: AND organization_id = p_organization_id;
10019: l_item_rec inv_reservation_global.item_record;
10020: -- changes for bug 9874238 end
10021: l_reservation_qty_lot NUMBER := 0; --Bug 12978409
10022: BEGIN
10023:
10219: -- the line level. Also, for ASN, pass the source type as PO so that the
10220: -- the lock name would be the same as the PO's
10221:
10222: IF (l_to_rsv_rec.supply_source_type_id =
10223: inv_reservation_global.g_source_type_asn) THEN
10224: l_supply_source_type_id :=
10225: inv_reservation_global.g_source_type_po;
10226: ELSE
10227: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
10221:
10222: IF (l_to_rsv_rec.supply_source_type_id =
10223: inv_reservation_global.g_source_type_asn) THEN
10224: l_supply_source_type_id :=
10225: inv_reservation_global.g_source_type_po;
10226: ELSE
10227: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
10228: END IF;
10229:
10226: ELSE
10227: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
10228: END IF;
10229:
10230: IF (l_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
10231: inv_reservation_lock_pvt.lock_supply_demand_record
10232: (p_organization_id => l_to_rsv_rec.organization_id
10233: ,p_inventory_item_id => l_to_rsv_rec.inventory_item_id
10234: ,p_source_type_id => l_supply_source_type_id
10408: END IF;
10409: END;
10410:
10411: IF (l_to_rsv_rec.demand_source_type_id IN
10412: (inv_reservation_global.g_source_type_oe,
10413: inv_reservation_global.g_source_type_internal_ord,
10414: inv_reservation_global.g_source_type_rma)) AND
10415: (l_pjm_enabled = 1) THEN
10416:
10409: END;
10410:
10411: IF (l_to_rsv_rec.demand_source_type_id IN
10412: (inv_reservation_global.g_source_type_oe,
10413: inv_reservation_global.g_source_type_internal_ord,
10414: inv_reservation_global.g_source_type_rma)) AND
10415: (l_pjm_enabled = 1) THEN
10416:
10417: IF (l_to_rsv_rec.demand_source_line_id IS NOT NULL) AND
10410:
10411: IF (l_to_rsv_rec.demand_source_type_id IN
10412: (inv_reservation_global.g_source_type_oe,
10413: inv_reservation_global.g_source_type_internal_ord,
10414: inv_reservation_global.g_source_type_rma)) AND
10415: (l_pjm_enabled = 1) THEN
10416:
10417: IF (l_to_rsv_rec.demand_source_line_id IS NOT NULL) AND
10418: (l_to_rsv_rec.demand_source_line_id <> fnd_api.g_miss_num)
10446: END IF;
10447:
10448: END IF;
10449: /* Added elseif for CMRO bug 13829182 */
10450: ELSIF ( (l_to_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) AND (l_pjm_enabled = 1)) THEN
10451: BEGIN
10452: SELECT we.entity_type, wdj.maintenance_object_source
10453: INTO l_wip_entity_id, l_maintenance_object_source
10454: FROM wip_entities we, wip_discrete_jobs wdj
10461: END IF;
10462: END;
10463:
10464: IF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
10465: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
10466: l_wip_job_type := 'CMRO'; -- AHL
10467: ELSE
10468: l_wip_entity_type := null;
10469: l_wip_job_type := null; -- AHL
10616: -- Pick Releaser Performance - Added validation_flag = 'Q' to distinguish
10617: -- between validation above and the qty tree processing below.
10618: /**** {{ R12 Enhanced reservations code changes }}****/
10619: IF (((p_validation_flag = fnd_api.g_true) OR (p_validation_flag = 'T') OR
10620: (p_validation_flag = 'Q')) AND l_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv)
10621: THEN
10622: /*** End R12 ***/
10623:
10624: -- changes for bug 9874238 start
10850:
10851:
10852: /**** {{ R12 Enhanced reservations code changes }}****/
10853: ELSIF (l_to_rsv_rec.supply_source_type_id IN
10854: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
10855: inv_reservation_global.g_source_type_asn,
10856: inv_reservation_global.g_source_type_intransit,
10857: inv_reservation_global.g_source_type_internal_req,
10858: inv_reservation_global.g_source_type_rcv)) AND
10851:
10852: /**** {{ R12 Enhanced reservations code changes }}****/
10853: ELSIF (l_to_rsv_rec.supply_source_type_id IN
10854: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
10855: inv_reservation_global.g_source_type_asn,
10856: inv_reservation_global.g_source_type_intransit,
10857: inv_reservation_global.g_source_type_internal_req,
10858: inv_reservation_global.g_source_type_rcv)) AND
10859: p_over_reservation_flag NOT IN (1,3) THEN
10852: /**** {{ R12 Enhanced reservations code changes }}****/
10853: ELSIF (l_to_rsv_rec.supply_source_type_id IN
10854: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
10855: inv_reservation_global.g_source_type_asn,
10856: inv_reservation_global.g_source_type_intransit,
10857: inv_reservation_global.g_source_type_internal_req,
10858: inv_reservation_global.g_source_type_rcv)) AND
10859: p_over_reservation_flag NOT IN (1,3) THEN
10860: -- call the helper procedure to get the reservable qty of the supply
10853: ELSIF (l_to_rsv_rec.supply_source_type_id IN
10854: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
10855: inv_reservation_global.g_source_type_asn,
10856: inv_reservation_global.g_source_type_intransit,
10857: inv_reservation_global.g_source_type_internal_req,
10858: inv_reservation_global.g_source_type_rcv)) AND
10859: p_over_reservation_flag NOT IN (1,3) THEN
10860: -- call the helper procedure to get the reservable qty of the supply
10861: -- Bug 5199672: Should pass g_miss_num as default for supply
10854: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
10855: inv_reservation_global.g_source_type_asn,
10856: inv_reservation_global.g_source_type_intransit,
10857: inv_reservation_global.g_source_type_internal_req,
10858: inv_reservation_global.g_source_type_rcv)) AND
10859: p_over_reservation_flag NOT IN (1,3) THEN
10860: -- call the helper procedure to get the reservable qty of the supply
10861: -- Bug 5199672: Should pass g_miss_num as default for supply
10862: -- source line detail. Otherwise, high level reservations
10955: debug_print('demand_source_line_detail = ' || l_to_rsv_rec.demand_source_line_detail);
10956: END IF;
10957:
10958: IF (l_to_rsv_rec.demand_source_type_id IN
10959: (inv_reservation_global.g_source_type_oe,
10960: inv_reservation_global.g_source_type_internal_ord,inv_reservation_global.g_source_type_rma)) THEN
10961: BEGIN
10962: SELECT open_flag, booked_flag
10963: INTO l_open_flag,
10956: END IF;
10957:
10958: IF (l_to_rsv_rec.demand_source_type_id IN
10959: (inv_reservation_global.g_source_type_oe,
10960: inv_reservation_global.g_source_type_internal_ord,inv_reservation_global.g_source_type_rma)) THEN
10961: BEGIN
10962: SELECT open_flag, booked_flag
10963: INTO l_open_flag,
10964: l_booked_flag
10976: debug_print('booked flag :' || l_booked_flag);
10977: END IF;
10978:
10979: IF (((l_to_rsv_rec.demand_source_type_id IN
10980: (inv_reservation_global.g_source_type_oe,
10981: inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_rma)) AND
10982:
10983: (NOT((Nvl(l_orig_rsv_tbl(1).demand_source_type_id,-99) = Nvl(l_to_rsv_rec.demand_source_type_id,-99)) AND
10984: (Nvl(l_orig_rsv_tbl(1).demand_source_header_id,-99) = Nvl(l_to_rsv_rec.demand_source_header_id,-99)) AND
10977: END IF;
10978:
10979: IF (((l_to_rsv_rec.demand_source_type_id IN
10980: (inv_reservation_global.g_source_type_oe,
10981: inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_rma)) AND
10982:
10983: (NOT((Nvl(l_orig_rsv_tbl(1).demand_source_type_id,-99) = Nvl(l_to_rsv_rec.demand_source_type_id,-99)) AND
10984: (Nvl(l_orig_rsv_tbl(1).demand_source_header_id,-99) = Nvl(l_to_rsv_rec.demand_source_header_id,-99)) AND
10985: (Nvl(l_orig_rsv_tbl(1).demand_source_line_id,-99) = Nvl(l_to_rsv_rec.demand_source_line_id,-99)) AND
10984: (Nvl(l_orig_rsv_tbl(1).demand_source_header_id,-99) = Nvl(l_to_rsv_rec.demand_source_header_id,-99)) AND
10985: (Nvl(l_orig_rsv_tbl(1).demand_source_line_id,-99) = Nvl(l_to_rsv_rec.demand_source_line_id,-99)) AND
10986: nvl(l_open_flag, 'N') = 'N'))) OR
10987: (l_to_rsv_rec.demand_source_type_id =
10988: inv_reservation_global.g_source_type_wip)) AND
10989: p_over_reservation_flag NOT IN (2,3) THEN
10990:
10991: -- call the helper procedure to get the reservable qty of the demand
10992: IF (l_debug = 1) THEN
11109: /*** End R12 ***/
11110:
11111: --
11112: -- Pre Update CTO Validation
11113: IF l_to_rsv_rec.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
11114: --
11115: IF (l_debug = 1) THEN
11116: debug_print('Pre Update CTO validation');
11117: END IF;
11673: END IF;
11674:
11675: --
11676: -- Post Update CTO Validation
11677: IF l_to_rsv_rec.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
11678: --
11679: IF l_orig_rsv_tbl(1).primary_reservation_quantity > l_to_rsv_rec.primary_reservation_quantity THEN
11680: IF (l_debug = 1) THEN
11681: debug_print('Calling cto_workflow_api_pk.wf_update_after_inv_unreserv');
11792: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
11793: , x_return_status OUT NOCOPY VARCHAR2
11794: , x_msg_count OUT NOCOPY NUMBER
11795: , x_msg_data OUT NOCOPY VARCHAR2
11796: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
11797: , p_primary_relieved_quantity IN NUMBER
11798: , p_secondary_relieved_quantity IN NUMBER -- INVCONV
11799: , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
11800: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
11796: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
11797: , p_primary_relieved_quantity IN NUMBER
11798: , p_secondary_relieved_quantity IN NUMBER -- INVCONV
11799: , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
11800: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
11801: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
11802: , x_primary_relieved_quantity OUT NOCOPY NUMBER
11803: , x_secondary_relieved_quantity OUT NOCOPY NUMBER -- INVCONV
11804: , x_primary_remain_quantity OUT NOCOPY NUMBER
11806: ) IS
11807: l_api_version_number CONSTANT NUMBER := 1.0;
11808: l_api_name CONSTANT VARCHAR2(30) := 'Relieve_Reservation';
11809: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11810: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
11811: l_tmp_rsv_tbl_count NUMBER;
11812: l_reservation_id NUMBER;
11813: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
11814: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
11809: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11810: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
11811: l_tmp_rsv_tbl_count NUMBER;
11812: l_reservation_id NUMBER;
11813: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
11814: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
11815: --
11816: l_orig_item_cache_index INTEGER := NULL;
11817: l_orig_org_cache_index INTEGER := NULL;
11810: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
11811: l_tmp_rsv_tbl_count NUMBER;
11812: l_reservation_id NUMBER;
11813: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
11814: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
11815: --
11816: l_orig_item_cache_index INTEGER := NULL;
11817: l_orig_org_cache_index INTEGER := NULL;
11818: l_orig_demand_cache_index INTEGER := NULL;
11842: l_dual_control_flag VARCHAR2(1) := 'N'; -- INVCONV
11843: /*** {{ R12 Enhanced reservations code changes ***/
11844: l_count NUMBER;
11845: l_count_to_unrsv_serials NUMBER :=0;
11846: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
11847: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
11848: /*** End R12 }} ***/
11849: BEGIN
11850: -- Standard call to check for call compatibility
11843: /*** {{ R12 Enhanced reservations code changes ***/
11844: l_count NUMBER;
11845: l_count_to_unrsv_serials NUMBER :=0;
11846: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
11847: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
11848: /*** End R12 }} ***/
11849: BEGIN
11850: -- Standard call to check for call compatibility
11851: IF NOT fnd_api.compatible_api_call(l_api_version_number, p_api_version_number, l_api_name, g_pkg_name) THEN
12075: END IF;
12076: -- INVCONV END
12077: END IF;
12078:
12079: IF l_tmp_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_inv THEN
12080: -- call quantity processor to
12081: -- modify the tree so that the quantity tree
12082: -- reflect the deletion
12083: IF (l_debug = 1) THEN
12588: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
12589: , x_return_status OUT NOCOPY VARCHAR2
12590: , x_msg_count OUT NOCOPY NUMBER
12591: , x_msg_data OUT NOCOPY VARCHAR2
12592: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12593: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
12594: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
12595: ) IS
12596: l_api_version_number CONSTANT NUMBER := 1.0;
12589: , x_return_status OUT NOCOPY VARCHAR2
12590: , x_msg_count OUT NOCOPY NUMBER
12591: , x_msg_data OUT NOCOPY VARCHAR2
12592: , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12593: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
12594: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
12595: ) IS
12596: l_api_version_number CONSTANT NUMBER := 1.0;
12597: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Reservation';
12595: ) IS
12596: l_api_version_number CONSTANT NUMBER := 1.0;
12597: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Reservation';
12598: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12599: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
12600: l_tmp_rsv_tbl_count NUMBER;
12601: l_reservation_id NUMBER;
12602: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
12603: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
12598: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12599: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
12600: l_tmp_rsv_tbl_count NUMBER;
12601: l_reservation_id NUMBER;
12602: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
12603: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
12604: --
12605: l_orig_item_cache_index INTEGER := NULL;
12606: l_orig_org_cache_index INTEGER := NULL;
12599: l_tmp_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
12600: l_tmp_rsv_tbl_count NUMBER;
12601: l_reservation_id NUMBER;
12602: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
12603: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
12604: --
12605: l_orig_item_cache_index INTEGER := NULL;
12606: l_orig_org_cache_index INTEGER := NULL;
12607: l_orig_demand_cache_index INTEGER := NULL;
12619: l_debug number;
12620: l_lot_divisible_flag VARCHAR2(1) :='Y'; -- INVCONV
12621:
12622: /*** {{ R12 Enhanced reservations code changes ***/
12623: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
12624: /*** End R12 }} ***/
12625: BEGIN
12626: -- Use cache to get value for l_debug
12627: IF g_is_pickrelease_set IS NULL THEN
12758: END IF;
12759: -- INVCONV END
12760:
12761: -- Bug 2354735: Proceed with Trees only if p_validation_flag = 'T'
12762: IF l_tmp_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_inv
12763: AND p_validation_flag = fnd_api.g_true THEN
12764: -- call quantity processor to
12765: -- modify the tree so that the quantity tree
12766: -- reflect the deletion
12841: END IF;
12842:
12843:
12844: -- Pre Delete CTO Validation
12845: 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
12846: --
12847: cto_workflow_api_pk.inventory_unreservation_check(
12848: p_order_line_id => l_tmp_rsv_tbl(1).demand_source_line_id
12849: , p_rsv_quantity => l_tmp_rsv_tbl(1).primary_reservation_quantity
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: --
12910: cto_workflow_api_pk.wf_update_after_inv_unreserv(
12911: p_order_line_id => l_tmp_rsv_tbl(1).demand_source_line_id
12912: , x_return_status => l_return_status
12963: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
12964: , x_return_status OUT NOCOPY VARCHAR2
12965: , x_msg_count OUT NOCOPY NUMBER
12966: , x_msg_data OUT NOCOPY VARCHAR2
12967: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12968: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12969: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
12970: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
12971: , p_over_reservation_flag IN NUMBER DEFAULT 0
12964: , x_return_status OUT NOCOPY VARCHAR2
12965: , x_msg_count OUT NOCOPY NUMBER
12966: , x_msg_data OUT NOCOPY VARCHAR2
12967: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12968: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12969: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
12970: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
12971: , p_over_reservation_flag IN NUMBER DEFAULT 0
12972: , x_reservation_id OUT NOCOPY NUMBER
12965: , x_msg_count OUT NOCOPY NUMBER
12966: , x_msg_data OUT NOCOPY VARCHAR2
12967: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12968: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
12969: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
12970: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
12971: , p_over_reservation_flag IN NUMBER DEFAULT 0
12972: , x_reservation_id OUT NOCOPY NUMBER
12973: ) IS
12977: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12978: l_quantity_reserved NUMBER;
12979: l_secondary_quantity_reserved NUMBER; -- INVCONV
12980: l_debug NUMBER;
12981: l_dummy_serial_number inv_reservation_global.serial_number_tbl_type;
12982: l_reservation_id NUMBER;
12983: l_reservation_qty_lot NUMBER := 0; --Bug 12978409
12984:
12985: BEGIN
13105: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
13106: , x_return_status OUT NOCOPY VARCHAR2
13107: , x_msg_count OUT NOCOPY NUMBER
13108: , x_msg_data OUT NOCOPY VARCHAR2
13109: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13110: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13111: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
13112: /**** {{ R12 Enhanced reservations code changes }}****/
13113: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
13106: , x_return_status OUT NOCOPY VARCHAR2
13107: , x_msg_count OUT NOCOPY NUMBER
13108: , x_msg_data OUT NOCOPY VARCHAR2
13109: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13110: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13111: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
13112: /**** {{ R12 Enhanced reservations code changes }}****/
13113: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
13114: /*** End R12 ***/
13107: , x_msg_count OUT NOCOPY NUMBER
13108: , x_msg_data OUT NOCOPY VARCHAR2
13109: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13110: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13111: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
13112: /**** {{ R12 Enhanced reservations code changes }}****/
13113: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
13114: /*** End R12 ***/
13115: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
13109: , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13110: , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
13111: , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
13112: /**** {{ R12 Enhanced reservations code changes }}****/
13113: , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
13114: /*** End R12 ***/
13115: , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
13116: , p_over_reservation_flag IN NUMBER DEFAULT 0
13117: , x_reservation_id OUT NOCOPY NUMBER
13118: ) IS
13119: l_api_version_number CONSTANT NUMBER := 1.0;
13120: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation';
13121: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13122: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13123: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13124: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13125: l_orig_rsv_tbl_count NUMBER;
13126: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13119: l_api_version_number CONSTANT NUMBER := 1.0;
13120: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation';
13121: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13122: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13123: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13124: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13125: l_orig_rsv_tbl_count NUMBER;
13126: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13127: --
13120: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation';
13121: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
13122: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13123: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13124: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13125: l_orig_rsv_tbl_count NUMBER;
13126: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13127: --
13128: l_to_rsv_tbl_count NUMBER;
13122: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13123: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13124: l_orig_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13125: l_orig_rsv_tbl_count NUMBER;
13126: l_to_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
13127: --
13128: l_to_rsv_tbl_count NUMBER;
13129: l_transfer_all BOOLEAN;
13130: l_to_row_exist BOOLEAN;
13168: l_lot_divisible_flag VARCHAR2(1) :='Y'; -- INVCONV
13169: l_dual_tracking BOOLEAN := FALSE; -- INVCONV
13170:
13171: /**** {{ R12 Enhanced reservations code changes }}****/
13172: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
13173: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
13174: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
13175: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13176: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
13169: l_dual_tracking BOOLEAN := FALSE; -- INVCONV
13170:
13171: /**** {{ R12 Enhanced reservations code changes }}****/
13172: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
13173: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
13174: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
13175: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13176: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
13177: l_qty_available NUMBER := 0;
13170:
13171: /**** {{ R12 Enhanced reservations code changes }}****/
13172: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
13173: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
13174: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
13175: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13176: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
13177: l_qty_available NUMBER := 0;
13178: l_original_serial_count NUMBER;
13171: /**** {{ R12 Enhanced reservations code changes }}****/
13172: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
13173: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
13174: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
13175: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13176: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
13177: l_qty_available NUMBER := 0;
13178: l_original_serial_count NUMBER;
13179: l_to_serial_count NUMBER;
13172: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
13173: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
13174: l_serial_number_table inv_reservation_global.serial_number_tbl_type;
13175: l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
13176: l_dummy_serial_array inv_reservation_global.serial_number_tbl_type;
13177: l_qty_available NUMBER := 0;
13178: l_original_serial_count NUMBER;
13179: l_to_serial_count NUMBER;
13180: l_serial_param NUMBER;
13182: l_total_serials_reserved NUMBER;
13183: l_total_to_serials_reserved NUMBER;
13184: l_from_reservation_id NUMBER;
13185: l_validate_serials_reserved NUMBER;
13186: l_validate_serial_number_table inv_reservation_global.serial_number_tbl_type;
13187: l_serials_unreserved NUMBER;
13188: l_to_reservation_id NUMBER;
13189: l_total_from_serials_reserved NUMBER;
13190: l_from_primary_reservation_qty NUMBER;
13481: -- the line level. Also, for ASN, pass the source type as PO so that the
13482: -- the lock name would be the same as the PO's
13483:
13484: IF (l_to_rsv_rec.supply_source_type_id =
13485: inv_reservation_global.g_source_type_asn) THEN
13486: l_supply_source_type_id :=
13487: inv_reservation_global.g_source_type_po;
13488: ELSE
13489: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
13483:
13484: IF (l_to_rsv_rec.supply_source_type_id =
13485: inv_reservation_global.g_source_type_asn) THEN
13486: l_supply_source_type_id :=
13487: inv_reservation_global.g_source_type_po;
13488: ELSE
13489: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
13490: END IF;
13491:
13488: ELSE
13489: l_supply_source_type_id := l_to_rsv_rec.supply_source_type_id;
13490: END IF;
13491:
13492: IF (l_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
13493: inv_reservation_lock_pvt.lock_supply_demand_record
13494: (p_organization_id => l_to_rsv_rec.organization_id
13495: ,p_inventory_item_id => l_to_rsv_rec.inventory_item_id
13496: ,p_source_type_id => l_supply_source_type_id
13558: END IF;
13559: END;
13560: END IF;
13561: IF (l_to_rsv_rec.demand_source_type_id IN
13562: (inv_reservation_global.g_source_type_oe,
13563: inv_reservation_global.g_source_type_internal_ord,
13564: inv_reservation_global.g_source_type_rma)) AND
13565: (l_pjm_enabled = 1) THEN
13566:
13559: END;
13560: END IF;
13561: IF (l_to_rsv_rec.demand_source_type_id IN
13562: (inv_reservation_global.g_source_type_oe,
13563: inv_reservation_global.g_source_type_internal_ord,
13564: inv_reservation_global.g_source_type_rma)) AND
13565: (l_pjm_enabled = 1) THEN
13566:
13567: IF (l_to_rsv_rec.demand_source_line_id IS NOT NULL) AND
13560: END IF;
13561: IF (l_to_rsv_rec.demand_source_type_id IN
13562: (inv_reservation_global.g_source_type_oe,
13563: inv_reservation_global.g_source_type_internal_ord,
13564: inv_reservation_global.g_source_type_rma)) AND
13565: (l_pjm_enabled = 1) THEN
13566:
13567: IF (l_to_rsv_rec.demand_source_line_id IS NOT NULL) AND
13568: (l_to_rsv_rec.demand_source_line_id <> fnd_api.g_miss_num)
13602: END IF;
13603:
13604: END IF;
13605: /* Added for bug 13829182 */
13606: ELSIF ( (l_to_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) AND (l_pjm_enabled = 1)) THEN
13607: BEGIN
13608: SELECT we.entity_type, wdj.maintenance_object_source
13609: INTO l_wip_entity_id, l_maintenance_object_source
13610: FROM wip_entities we, wip_discrete_jobs wdj
13617: END IF;
13618: END;
13619:
13620: IF l_wip_entity_id = 6 and l_maintenance_object_source = 2 then
13621: l_wip_entity_type := inv_reservation_global.g_wip_source_type_cmro;
13622: l_wip_job_type := 'CMRO'; -- AHL
13623: ELSE
13624: l_wip_entity_type := null;
13625: l_wip_job_type := null; -- AHL
13785: /**** {{ R12 Enhanced reservations code changes }}****/
13786: END IF;
13787:
13788: IF p_validation_flag = fnd_api.g_true AND
13789: (l_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv) THEN
13790:
13791: /*** End R12 ***/
13792: -- INVCONV BEGIN
13793: IF NOT is_lot_divisible(l_orig_item_cache_index) THEN
13867: RAISE fnd_api.g_exc_unexpected_error;
13868: END IF;
13869:
13870: -- MUOM Fulfillment project
13871: IF l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_po
13872: AND l_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv
13873: AND l_fulfill_base = 'S' AND l_transfer_all THEN
13874:
13875: inv_quantity_tree_pvt.query_tree(
13868: END IF;
13869:
13870: -- MUOM Fulfillment project
13871: IF l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_po
13872: AND l_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv
13873: AND l_fulfill_base = 'S' AND l_transfer_all THEN
13874:
13875: inv_quantity_tree_pvt.query_tree(
13876: p_api_version_number => 1.0
13950: END IF;
13951:
13952: /**** {{ R12 Enhanced reservations code changes }}****/
13953: ELSIF (l_to_rsv_rec.supply_source_type_id IN
13954: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
13955: inv_reservation_global.g_source_type_asn,
13956: inv_reservation_global.g_source_type_intransit,
13957: inv_reservation_global.g_source_type_internal_req,
13958: inv_reservation_global.g_source_type_rcv)) AND
13951:
13952: /**** {{ R12 Enhanced reservations code changes }}****/
13953: ELSIF (l_to_rsv_rec.supply_source_type_id IN
13954: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
13955: inv_reservation_global.g_source_type_asn,
13956: inv_reservation_global.g_source_type_intransit,
13957: inv_reservation_global.g_source_type_internal_req,
13958: inv_reservation_global.g_source_type_rcv)) AND
13959: p_over_reservation_flag NOT IN (1,3) THEN
13952: /**** {{ R12 Enhanced reservations code changes }}****/
13953: ELSIF (l_to_rsv_rec.supply_source_type_id IN
13954: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
13955: inv_reservation_global.g_source_type_asn,
13956: inv_reservation_global.g_source_type_intransit,
13957: inv_reservation_global.g_source_type_internal_req,
13958: inv_reservation_global.g_source_type_rcv)) AND
13959: p_over_reservation_flag NOT IN (1,3) THEN
13960:
13953: ELSIF (l_to_rsv_rec.supply_source_type_id IN
13954: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
13955: inv_reservation_global.g_source_type_asn,
13956: inv_reservation_global.g_source_type_intransit,
13957: inv_reservation_global.g_source_type_internal_req,
13958: inv_reservation_global.g_source_type_rcv)) AND
13959: p_over_reservation_flag NOT IN (1,3) THEN
13960:
13961: --In xdock case, splitting the reservation calls transfer reservation during deliver operation.
13954: (inv_reservation_global.g_source_type_wip, inv_reservation_global.g_source_type_po,
13955: inv_reservation_global.g_source_type_asn,
13956: inv_reservation_global.g_source_type_intransit,
13957: inv_reservation_global.g_source_type_internal_req,
13958: inv_reservation_global.g_source_type_rcv)) AND
13959: p_over_reservation_flag NOT IN (1,3) THEN
13960:
13961: --In xdock case, splitting the reservation calls transfer reservation during deliver operation.
13962: --However, RTP has already processed and decremented the receiving supply, so the call to
13961: --In xdock case, splitting the reservation calls transfer reservation during deliver operation.
13962: --However, RTP has already processed and decremented the receiving supply, so the call to
13963: --get_supply_reservable_qty is not needed -- Added for bug 9879753 - Start
13964:
13965: IF (l_original_rsv_rec.supply_source_type_id=inv_reservation_global.g_source_type_rcv
13966: AND l_to_rsv_rec.supply_source_type_id=inv_reservation_global.g_source_type_rcv
13967: AND l_original_rsv_rec.external_source_code='XDOCK' AND l_to_rsv_rec.external_source_code='XDOCK') THEN
13968:
13969: NULL;
13962: --However, RTP has already processed and decremented the receiving supply, so the call to
13963: --get_supply_reservable_qty is not needed -- Added for bug 9879753 - Start
13964:
13965: IF (l_original_rsv_rec.supply_source_type_id=inv_reservation_global.g_source_type_rcv
13966: AND l_to_rsv_rec.supply_source_type_id=inv_reservation_global.g_source_type_rcv
13967: AND l_original_rsv_rec.external_source_code='XDOCK' AND l_to_rsv_rec.external_source_code='XDOCK') THEN
13968:
13969: NULL;
13970:
14023: p_org_id => l_orig_rsv_tbl(1).organization_id,
14024: x_fulfillment_base => l_fulfill_base
14025: );
14026:
14027: IF l_fulfill_base = 'S' AND l_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv
14028: AND l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_inv THEN
14029: l_to_rsv_rec.primary_reservation_quantity := inv_convert.inv_um_convert(
14030: item_id => l_to_rsv_rec.inventory_item_id
14031: , lot_number => NULL
14024: x_fulfillment_base => l_fulfill_base
14025: );
14026:
14027: IF l_fulfill_base = 'S' AND l_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv
14028: AND l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_inv THEN
14029: l_to_rsv_rec.primary_reservation_quantity := inv_convert.inv_um_convert(
14030: item_id => l_to_rsv_rec.inventory_item_id
14031: , lot_number => NULL
14032: , organization_id => l_to_rsv_rec.organization_id
14067: debug_print('demand_source_line_detail = ' || l_to_rsv_rec.demand_source_line_detail);
14068: END IF;
14069:
14070: IF (l_to_rsv_rec.demand_source_type_id IN
14071: (inv_reservation_global.g_source_type_wip,
14072: inv_reservation_global.g_source_type_oe,
14073: inv_reservation_global.g_source_type_internal_ord,
14074: inv_reservation_global.g_source_type_rma)) AND
14075: p_over_reservation_flag NOT IN (2,3) THEN
14068: END IF;
14069:
14070: IF (l_to_rsv_rec.demand_source_type_id IN
14071: (inv_reservation_global.g_source_type_wip,
14072: inv_reservation_global.g_source_type_oe,
14073: inv_reservation_global.g_source_type_internal_ord,
14074: inv_reservation_global.g_source_type_rma)) AND
14075: p_over_reservation_flag NOT IN (2,3) THEN
14076: -- call the helper procedure to get the reservable qty of the demand
14069:
14070: IF (l_to_rsv_rec.demand_source_type_id IN
14071: (inv_reservation_global.g_source_type_wip,
14072: inv_reservation_global.g_source_type_oe,
14073: inv_reservation_global.g_source_type_internal_ord,
14074: inv_reservation_global.g_source_type_rma)) AND
14075: p_over_reservation_flag NOT IN (2,3) THEN
14076: -- call the helper procedure to get the reservable qty of the demand
14077: -- Bug 5199672: Should pass g_miss_num as default for demand
14070: IF (l_to_rsv_rec.demand_source_type_id IN
14071: (inv_reservation_global.g_source_type_wip,
14072: inv_reservation_global.g_source_type_oe,
14073: inv_reservation_global.g_source_type_internal_ord,
14074: inv_reservation_global.g_source_type_rma)) AND
14075: p_over_reservation_flag NOT IN (2,3) THEN
14076: -- call the helper procedure to get the reservable qty of the demand
14077: -- Bug 5199672: Should pass g_miss_num as default for demand
14078: -- source line detail. Otherwise, high level reservations
14199: IF (l_debug = 1) THEN
14200: debug_print('Transfer all is false');
14201: END IF;
14202: -- Pre Update CTO Validation
14203: IF l_orig_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
14204: --
14205: IF (l_debug = 1) THEN
14206: debug_print('Before calling cto work flow unresv check');
14207: END IF;
14527: * No longer call the wf_update_after_inv_unreserv api from
14528: * transfer_reservation
14529: *-- Post Update CTO Validation
14530: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14531: * inv_reservation_global.g_source_type_oe
14532: * ,inv_reservation_global.g_source_type_internal_ord
14533: * ,inv_reservation_global.g_source_type_rma) THEN
14534: * --
14535: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14528: * transfer_reservation
14529: *-- Post Update CTO Validation
14530: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14531: * inv_reservation_global.g_source_type_oe
14532: * ,inv_reservation_global.g_source_type_internal_ord
14533: * ,inv_reservation_global.g_source_type_rma) THEN
14534: * --
14535: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14536: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14529: *-- Post Update CTO Validation
14530: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14531: * inv_reservation_global.g_source_type_oe
14532: * ,inv_reservation_global.g_source_type_internal_ord
14533: * ,inv_reservation_global.g_source_type_rma) THEN
14534: * --
14535: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14536: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14537: * , x_return_status => l_return_status
14553: IF (l_debug = 1) THEN
14554: debug_print('To row exists and transfer all is true');
14555: END IF;
14556: -- Pre Delete CTO Validation
14557: IF l_orig_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
14558: --
14559: cto_workflow_api_pk.inventory_unreservation_check(
14560: p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14561: , p_rsv_quantity => l_to_rsv_rec.primary_reservation_quantity
14599: * No longer call the wf_update_after_inv_unreserv api from
14600: * transfer_reservation
14601: *-- Post Delete CTO Validation
14602: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14603: * inv_reservation_global.g_source_type_oe
14604: * ,inv_reservation_global.g_source_type_internal_ord
14605: * ,inv_reservation_global.g_source_type_rma) THEN
14606: * --
14607: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14600: * transfer_reservation
14601: *-- Post Delete CTO Validation
14602: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14603: * inv_reservation_global.g_source_type_oe
14604: * ,inv_reservation_global.g_source_type_internal_ord
14605: * ,inv_reservation_global.g_source_type_rma) THEN
14606: * --
14607: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14608: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14601: *-- Post Delete CTO Validation
14602: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14603: * inv_reservation_global.g_source_type_oe
14604: * ,inv_reservation_global.g_source_type_internal_ord
14605: * ,inv_reservation_global.g_source_type_rma) THEN
14606: * --
14607: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14608: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14609: * , x_return_status => l_return_status
14625: IF (l_debug = 1) THEN
14626: debug_print('To row does not exist and transfer all is true');
14627: END IF;
14628: -- Pre Update CTO Validation
14629: IF l_orig_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
14630: --
14631: cto_workflow_api_pk.inventory_unreservation_check(
14632: p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14633: , p_rsv_quantity => l_orig_rsv_tbl(1).primary_reservation_quantity
14650:
14651: -- Added for bug 2458523
14652: -- Pre Insert CTO Validation
14653: IF l_to_rsv_rec.demand_source_type_id IN
14654: (inv_reservation_global.g_source_type_oe,
14655: inv_reservation_global.g_source_type_internal_ord,
14656: inv_reservation_global.g_source_type_rma) THEN
14657:
14658: --
14651: -- Added for bug 2458523
14652: -- Pre Insert CTO Validation
14653: IF l_to_rsv_rec.demand_source_type_id IN
14654: (inv_reservation_global.g_source_type_oe,
14655: inv_reservation_global.g_source_type_internal_ord,
14656: inv_reservation_global.g_source_type_rma) THEN
14657:
14658: --
14659: cto_workflow_api_pk.inventory_reservation_check(
14652: -- Pre Insert CTO Validation
14653: IF l_to_rsv_rec.demand_source_type_id IN
14654: (inv_reservation_global.g_source_type_oe,
14655: inv_reservation_global.g_source_type_internal_ord,
14656: inv_reservation_global.g_source_type_rma) THEN
14657:
14658: --
14659: cto_workflow_api_pk.inventory_reservation_check(
14660: p_order_line_id => l_to_rsv_rec.demand_source_line_id
14846: * No longer call the wf_update_after_inv_unreserv api from
14847: * transfer_reservation
14848: *-- Post Update CTO Validation
14849: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14850: * inv_reservation_global.g_source_type_oe
14851: * ,inv_reservation_global.g_source_type_internal_ord
14852: * ,inv_reservation_global.g_source_type_rma) THEN
14853: * --
14854: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14847: * transfer_reservation
14848: *-- Post Update CTO Validation
14849: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14850: * inv_reservation_global.g_source_type_oe
14851: * ,inv_reservation_global.g_source_type_internal_ord
14852: * ,inv_reservation_global.g_source_type_rma) THEN
14853: * --
14854: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14855: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14848: *-- Post Update CTO Validation
14849: * IF l_orig_rsv_tbl(1).demand_source_type_id in (
14850: * inv_reservation_global.g_source_type_oe
14851: * ,inv_reservation_global.g_source_type_internal_ord
14852: * ,inv_reservation_global.g_source_type_rma) THEN
14853: * --
14854: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
14855: * p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
14856: * , x_return_status => l_return_status
14879: /* Commenting out call to CTO inventory_unreservation_check API. This
14880: API has already been called in the previous if then else. This
14881: issue has been reported in bug 2458523
14882: -- Pre Update CTO Validation
14883: IF l_to_rsv_rec.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
14884: --
14885: cto_workflow_api_pk.inventory_unreservation_check(
14886: p_order_line_id => l_to_rsv_rec.demand_source_line_id
14887: , p_rsv_quantity => l_to_rsv_rec.primary_reservation_quantity
14905:
14906: -- Added for bug 2458523
14907: -- Pre Insert CTO Validation
14908: IF l_to_rsv_rec.demand_source_type_id IN
14909: (inv_reservation_global.g_source_type_oe,
14910: inv_reservation_global.g_source_type_internal_ord,
14911: inv_reservation_global.g_source_type_rma) THEN
14912:
14913: --
14906: -- Added for bug 2458523
14907: -- Pre Insert CTO Validation
14908: IF l_to_rsv_rec.demand_source_type_id IN
14909: (inv_reservation_global.g_source_type_oe,
14910: inv_reservation_global.g_source_type_internal_ord,
14911: inv_reservation_global.g_source_type_rma) THEN
14912:
14913: --
14914: cto_workflow_api_pk.inventory_reservation_check(
14907: -- Pre Insert CTO Validation
14908: IF l_to_rsv_rec.demand_source_type_id IN
14909: (inv_reservation_global.g_source_type_oe,
14910: inv_reservation_global.g_source_type_internal_ord,
14911: inv_reservation_global.g_source_type_rma) THEN
14912:
14913: --
14914: cto_workflow_api_pk.inventory_reservation_check(
14915: p_order_line_id => l_to_rsv_rec.demand_source_line_id
15020: * No longer call the wf_update_after_inv_unreserv api from
15021: * transfer_reservation
15022: *-- Post Update CTO Validation
15023: * IF l_to_rsv_rec.demand_source_type_id in (
15024: * inv_reservation_global.g_source_type_oe
15025: * ,inv_reservation_global.g_source_type_internal_ord
15026: * ,inv_reservation_global.g_source_type_rma) THEN
15027: * --
15028: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
15021: * transfer_reservation
15022: *-- Post Update CTO Validation
15023: * IF l_to_rsv_rec.demand_source_type_id in (
15024: * inv_reservation_global.g_source_type_oe
15025: * ,inv_reservation_global.g_source_type_internal_ord
15026: * ,inv_reservation_global.g_source_type_rma) THEN
15027: * --
15028: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
15029: * p_order_line_id => l_to_rsv_rec.demand_source_line_id
15022: *-- Post Update CTO Validation
15023: * IF l_to_rsv_rec.demand_source_type_id in (
15024: * inv_reservation_global.g_source_type_oe
15025: * ,inv_reservation_global.g_source_type_internal_ord
15026: * ,inv_reservation_global.g_source_type_rma) THEN
15027: * --
15028: * cto_workflow_api_pk.wf_update_after_inv_unreserv(
15029: * p_order_line_id => l_to_rsv_rec.demand_source_line_id
15030: * , x_return_status => l_return_status
15046: IF (l_debug = 1) THEN
15047: debug_print('To row does not exists and transfer all is false');
15048: END IF;
15049: -- Pre Insert CTO Validation
15050: IF l_to_rsv_rec.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
15051: --
15052: cto_workflow_api_pk.inventory_reservation_check(
15053: p_order_line_id => l_to_rsv_rec.demand_source_line_id
15054: , x_return_status => l_return_status
15139: -- we need to update the original supply as PO, because we
15140: -- will have to transfer the reservation back to ASN while reducing
15141: -- and cancelling ASNs
15142: IF (l_to_rsv_rec.supply_source_type_id =
15143: inv_reservation_global.g_source_type_asn) AND
15144: (l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_po) THEN
15145:
15146: l_orig_supply_type_id := inv_reservation_global.g_source_type_po;
15147: IF (l_debug = 1) THEN
15140: -- will have to transfer the reservation back to ASN while reducing
15141: -- and cancelling ASNs
15142: IF (l_to_rsv_rec.supply_source_type_id =
15143: inv_reservation_global.g_source_type_asn) AND
15144: (l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_po) THEN
15145:
15146: l_orig_supply_type_id := inv_reservation_global.g_source_type_po;
15147: IF (l_debug = 1) THEN
15148: debug_print('The original supply is po and the new supply is asn' ||l_orig_supply_type_id);
15142: IF (l_to_rsv_rec.supply_source_type_id =
15143: inv_reservation_global.g_source_type_asn) AND
15144: (l_orig_rsv_tbl(1).supply_source_type_id = inv_reservation_global.g_source_type_po) THEN
15145:
15146: l_orig_supply_type_id := inv_reservation_global.g_source_type_po;
15147: IF (l_debug = 1) THEN
15148: debug_print('The original supply is po and the new supply is asn' ||l_orig_supply_type_id);
15149: END IF;
15150:
15284: -- sales order, becuase we were only calling this cto api for
15285: -- partial reservation transfers. Now, call this API everytime
15286: -- transfer_reservation is called
15287: -- Post Insert CTO Validation
15288: IF l_to_rsv_rec.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
15289: --
15290: cto_workflow_api_pk.wf_update_after_inv_reserv(p_order_line_id => l_to_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
15291:
15292: debug_print(' After CTO API' || l_return_status);
16979: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
16980: , x_return_status OUT NOCOPY VARCHAR2
16981: , x_msg_count OUT NOCOPY NUMBER
16982: , x_msg_data OUT NOCOPY VARCHAR2
16983: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
16984: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
16985: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
16986: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
16987: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
16981: , x_msg_count OUT NOCOPY NUMBER
16982: , x_msg_data OUT NOCOPY VARCHAR2
16983: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
16984: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
16985: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
16986: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
16987: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
16988: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
16989: , x_error_code OUT NOCOPY NUMBER
16982: , x_msg_data OUT NOCOPY VARCHAR2
16983: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
16984: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
16985: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
16986: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
16987: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
16988: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
16989: , x_error_code OUT NOCOPY NUMBER
16990: ) IS
16983: , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
16984: , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
16985: , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
16986: , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
16987: , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
16988: , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
16989: , x_error_code OUT NOCOPY NUMBER
16990: ) IS
16991: l_api_version_number CONSTANT NUMBER := 1.0;
16991: l_api_version_number CONSTANT NUMBER := 1.0;
16992: l_api_name CONSTANT VARCHAR2(30) := 'Query_Reservation_OM_Hdr_Line';
16993: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
16994: l_counter INTEGER;
16995: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
16996:
16997: -- Cursor to fetch MTL_RESERVATION record based on passed
16998: -- demand_source_header_id and demand_source_line_id
16999:
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: )
17082: AND demand_source_header_id = dmd_source_header_id
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: )
17082: AND demand_source_header_id = dmd_source_header_id
17083: AND demand_source_line_id = dmd_source_line_id
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: )
17082: AND demand_source_header_id = dmd_source_header_id
17083: AND demand_source_line_id = dmd_source_line_id
17084: ORDER BY NVL(mr.revision, ' ')
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: )
17174: AND demand_source_header_id = dmd_source_header_id
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: )
17174: AND demand_source_header_id = dmd_source_header_id
17175: AND demand_source_line_id = dmd_source_line_id
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: )
17174: AND demand_source_header_id = dmd_source_header_id
17175: AND demand_source_line_id = dmd_source_line_id
17176: ORDER BY mr.requirement_date asc
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: )
17267: AND demand_source_header_id = dmd_source_header_id
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: )
17267: AND demand_source_header_id = dmd_source_header_id
17268: AND demand_source_line_id = dmd_source_line_id
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: )
17267: AND demand_source_header_id = dmd_source_header_id
17268: AND demand_source_line_id = dmd_source_line_id
17269: ORDER BY mr.requirement_date DESC
17271: , NVL(mr.lot_number, ' ')
17272: , NVL(mr.subinventory_code, ' ')
17273: , NVL(mr.locator_id, 0);
17274: BEGIN
17275: x_error_code := inv_reservation_global.g_err_unexpected;
17276:
17277: --
17278: -- Standard call to check for call compatibility
17279: IF NOT fnd_api.compatible_api_call(l_api_version_number, p_api_version_number, l_api_name, g_pkg_name) THEN
17309: END IF;
17310:
17311: --Bug#2872822/2914726 OM call to this API passes null. This was changed
17312: -- handle null also.
17313: IF (p_sort_by_req_date NOT IN ( inv_reservation_global.g_query_req_date_inv_asc,
17314: inv_reservation_global.g_query_req_date_inv_desc)) OR p_sort_by_req_date IS NULL
17315: then
17316: OPEN mrc(p_query_input.demand_source_header_id, p_query_input.demand_source_line_id);
17317: l_counter := 0;
17310:
17311: --Bug#2872822/2914726 OM call to this API passes null. This was changed
17312: -- handle null also.
17313: IF (p_sort_by_req_date NOT IN ( inv_reservation_global.g_query_req_date_inv_asc,
17314: inv_reservation_global.g_query_req_date_inv_desc)) OR p_sort_by_req_date IS NULL
17315: then
17316: OPEN mrc(p_query_input.demand_source_header_id, p_query_input.demand_source_line_id);
17317: l_counter := 0;
17318:
17323: x_mtl_reservation_tbl(l_counter) := l_rsv_rec;
17324: END LOOP;
17325:
17326: CLOSE mrc;
17327: ELSIF p_sort_by_req_date =inv_reservation_global.g_query_req_date_inv_asc then
17328: OPEN mrc_asc(p_query_input.demand_source_header_id,
17329: p_query_input.demand_source_line_id);
17330: l_counter := 0;
17331: LOOP
17334: l_counter := l_counter + 1;
17335: x_mtl_reservation_tbl(l_counter) := l_rsv_rec;
17336: END LOOP;
17337: CLOSE mrc_asc;
17338: ELSIF p_sort_by_req_date =inv_reservation_global.g_query_req_date_inv_desc then
17339: OPEN mrc_desc(p_query_input.demand_source_header_id,
17340: p_query_input.demand_source_line_id);
17341: l_counter := 0;
17342: LOOP
17351: END IF; --Sort by req date
17352: --
17353: x_mtl_reservation_tbl_count := l_counter;
17354: x_return_status := l_return_status;
17355: x_error_code := inv_reservation_global.g_err_no_error;
17356: --
17357: EXCEPTION
17358: WHEN fnd_api.g_exc_error THEN
17359: x_return_status := fnd_api.g_ret_sts_error;
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:
17422: l_primary_quantity NUMBER; --The two for 14011079
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:
17422: l_primary_quantity NUMBER; --The two for 14011079
17423: l_primary_uom_code VARCHAR(3);
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:
17422: l_primary_quantity NUMBER; --The two for 14011079
17423: l_primary_uom_code VARCHAR(3);
17424: -- Cursor to fetch the from reservations
18195: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
18196: , x_return_status OUT NOCOPY VARCHAR2
18197: , x_msg_count OUT NOCOPY NUMBER
18198: , x_msg_data OUT NOCOPY VARCHAR2
18199: , x_failed_rsv_temp_tbl OUT NOCOPY inv_reservation_global.mtl_failed_rsv_tbl_type
18200: ) IS
18201: l_api_version_number CONSTANT NUMBER := 1.0;
18202: l_api_name CONSTANT VARCHAR2(30) := 'Do_check_for_commit';
18203: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
18202: l_api_name CONSTANT VARCHAR2(30) := 'Do_check_for_commit';
18203: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
18204: l_no_violation BOOLEAN;
18205: l_root_id INTEGER;
18206: l_failed_rsv_temp_tbl inv_reservation_global.mtl_failed_rsv_tbl_type;
18207: l_failed_rsv_temp_rec inv_reservation_global.mtl_failed_rsv_rec_type;
18208: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18209: p_original_serial_number inv_reservation_global.serial_number_tbl_type;
18210: l_reservation_id NUMBER;
18203: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
18204: l_no_violation BOOLEAN;
18205: l_root_id INTEGER;
18206: l_failed_rsv_temp_tbl inv_reservation_global.mtl_failed_rsv_tbl_type;
18207: l_failed_rsv_temp_rec inv_reservation_global.mtl_failed_rsv_rec_type;
18208: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18209: p_original_serial_number inv_reservation_global.serial_number_tbl_type;
18210: l_reservation_id NUMBER;
18211: l_failed_rsv_temp_tbl_count INTEGER := 0;
18204: l_no_violation BOOLEAN;
18205: l_root_id INTEGER;
18206: l_failed_rsv_temp_tbl inv_reservation_global.mtl_failed_rsv_tbl_type;
18207: l_failed_rsv_temp_rec inv_reservation_global.mtl_failed_rsv_rec_type;
18208: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18209: p_original_serial_number inv_reservation_global.serial_number_tbl_type;
18210: l_reservation_id NUMBER;
18211: l_failed_rsv_temp_tbl_count INTEGER := 0;
18212: l_error_code VARCHAR2(100);
18205: l_root_id INTEGER;
18206: l_failed_rsv_temp_tbl inv_reservation_global.mtl_failed_rsv_tbl_type;
18207: l_failed_rsv_temp_rec inv_reservation_global.mtl_failed_rsv_rec_type;
18208: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18209: p_original_serial_number inv_reservation_global.serial_number_tbl_type;
18210: l_reservation_id NUMBER;
18211: l_failed_rsv_temp_tbl_count INTEGER := 0;
18212: l_error_code VARCHAR2(100);
18213: l_demand_source_line_id NUMBER;
18343: IS
18344:
18345: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation_SubXfer';
18346: -- Define local variables
18347: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18348: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18349: l_original_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18350: l_to_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18351: l_Reservation_Id Number;
18344:
18345: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation_SubXfer';
18346: -- Define local variables
18347: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18348: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18349: l_original_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18350: l_to_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18351: l_Reservation_Id Number;
18352: l_return_status VARCHAR2(1):= fnd_api.g_ret_sts_success;
18345: l_api_name CONSTANT VARCHAR2(30) := 'Transfer_Reservation_SubXfer';
18346: -- Define local variables
18347: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18348: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18349: l_original_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18350: l_to_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18351: l_Reservation_Id Number;
18352: l_return_status VARCHAR2(1):= fnd_api.g_ret_sts_success;
18353: l_debug number;
18346: -- Define local variables
18347: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18348: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type ;
18349: l_original_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18350: l_to_serial_number_Tab inv_reservation_global.serial_number_tbl_type ;
18351: l_Reservation_Id Number;
18352: l_return_status VARCHAR2(1):= fnd_api.g_ret_sts_success;
18353: l_debug number;
18354:
18503: TYPE rsv_serials_item_tbl IS TABLE OF rsv_serials_item_rec
18504: INDEX BY BINARY_INTEGER;
18505:
18506: l_rsv_serials_tbl rsv_serials_item_tbl;
18507: l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
18508: l_index NUMBER := 0;
18509: l_rsv_serials_tbl_count NUMBER := 0;
18510: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18511: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18506: l_rsv_serials_tbl rsv_serials_item_tbl;
18507: l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
18508: l_index NUMBER := 0;
18509: l_rsv_serials_tbl_count NUMBER := 0;
18510: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18511: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18512: l_reservation_id NUMBER;
18513:
18514: -- cursor of getting reservations with serial_number reserved in the lpn which
18507: l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
18508: l_index NUMBER := 0;
18509: l_rsv_serials_tbl_count NUMBER := 0;
18510: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18511: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
18512: l_reservation_id NUMBER;
18513:
18514: -- cursor of getting reservations with serial_number reserved in the lpn which
18515: -- outermost_lpn_id of the lpn = p_outermost_lpn_id and the reservation does not