[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
organization_id
, negative_inv_receipt_code
, project_reference_enabled
, stock_locator_control_code
INTO l_rec
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT
inventory_item_id
, organization_id
, lot_control_code
, serial_number_control_code
, reservable_type
, restrict_subinventories_code
, restrict_locators_code
, revision_qty_control_code
, location_control_code
, primary_uom_code
INTO l_rec
FROM
mtl_system_items
WHERE
inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id ;
SELECT 'Y'
FROM po_distributions_all
WHERE po_distribution_id = p_supply_source_line_id
AND po_header_id = p_supply_source_header_id;
select count(1)
into l_dropship_count
from oe_drop_ship_sources
where po_header_id = p_supply_source_header_id
and line_location_id = p_supply_source_line_id;
SELECT 'Y'
FROM po_requisition_lines_all
WHERE requisition_line_id = p_supply_source_line_id
AND requisition_header_id = p_supply_source_header_id;
select count(1)
into l_dropship_count
from oe_drop_ship_sources
where requisition_header_id = p_supply_source_header_id
and requisition_line_id = p_supply_source_line_id;
SELECT wms_enabled_flag
INTO l_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT wms_enabled_flag
INTO l_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT replenish_to_order_flag
INTO l_replenish_to_order
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT wms_enabled_flag
INTO l_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT replenish_to_order_flag
INTO l_replenish_to_order
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT replenish_to_order_flag
INTO l_replenish_to_order
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT replenish_to_order_flag
INTO l_replenish_to_order
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
select count(1)
into l_dropship_count
from oe_drop_ship_sources
where header_id = p_demand_source_header_id
and line_id = p_demand_source_line_id;
SELECT
secondary_inventory_name
, organization_id
, locator_type
, quantity_tracked
, asset_inventory
, reservable_type
INTO l_rec
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
SELECT 'Y' INTO l_found
FROM mtl_item_revisions
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision = p_revision ;
SELECT expiration_date INTO l_lot_expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT 'Y' INTO l_found
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
SELECT 'Y' INTO l_found
FROM mtl_item_sub_trk_all_v
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT 'Y' INTO l_found
FROM mtl_subinventories_trk_val_v
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code ;
/* SELECT 'Y' INTO l_found
FROM
mtl_secondary_locators msl
, mtl_item_locations mil
WHERE msl.inventory_item_id = p_inventory_item_id
AND msl.organization_id = p_organization_id
AND msl.subinventory_code = p_subinventory_code
AND msl.secondary_locator = p_locator_id
AND msl.secondary_locator = mil.inventory_location_id
AND (mil.disable_date > sysdate
OR mil.disable_date IS NULL
);
SELECT 'Y' INTO l_found
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND inventory_location_id = p_locator_id
AND (disable_date > sysdate
OR disable_date IS NULL
);
SELECT inventory_item_id, ordered_quantity
, order_quantity_uom, ship_from_org_id
, open_flag, source_type_code,flow_status_code
, booked_flag /*** {{ R12 Enhanced reservations code changes ***/
INTO l_line_rec_inventory_item_id,
l_line_rec_ordered_quantity,
l_line_rec_order_quantity_uom,
l_line_rec_org_id,
l_line_rec_open_flag,
l_source_type_code,
l_flow_status_code,
l_booked_flag
FROM oe_order_lines_all
WHERE line_id = p_demand_line_id ;
((p_rsv_action_name IN ('UPDATE','TRANSFER')) AND
(Nvl(p_orig_demand_type_id,-99) <>
Nvl(p_demand_type_id,-99)) OR
(Nvl(p_orig_demand_header_id,-99) <>
Nvl(p_demand_header_id,-99)) OR
(Nvl(p_orig_demand_line_id,-99) <> Nvl(p_demand_line_id,-99))) THEN
IF nvl(l_line_rec_open_flag, 'N') <> 'Y' AND Nvl(l_source_type_code, 'INTERNAL') <> 'EXTERNAL' THEN
FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_CLOSED_SO');
*SELECT nvl(sum(primary_reservation_quantity),0)
*INTO l_primary_reserved_quantity
*FROM mtl_reservations
*WHERE demand_source_type_id = p_demand_type_id
*AND demand_source_header_id = p_demand_header_id
*AND demand_source_line_id = p_demand_line_id
*AND reservation_id <> nvl(p_reservation_id,-1);
* select primary_uom_code
* into l_primary_uom_code
* from mtl_system_items
* where organization_id = l_line_rec_org_id
* and inventory_item_id = l_line_rec_inventory_item_id;
SELECT
id_flex_num
INTO l_structure_num
FROM
org_organization_definitions ood
, fnd_id_flex_structures ffs
WHERE
ood.organization_id = p_organization_id
AND ffs.id_flex_code = 'GL#'
AND ood.chart_of_accounts_id = ffs.id_flex_num;
PROCEDURE update_crossdock_reservation
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
debug_print('In update_crossdock_reservation');
wms_xdock_utils_pvt.update_crossdock_reservation(
x_return_status => l_return_status
, p_orig_rsv_rec => p_orig_rsv_rec
, p_new_rsv_rec => p_to_rsv_rec
);
debug_print('update_crossdock_reservation returns error');
debug_print('update_crossdock_reservation returns unexpected error');
, 'update_crossdock_reservation'
);
END update_crossdock_reservation;
PROCEDURE delete_crossdock_reservation
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
debug_print('In delete_crossdock_reservation');
wms_xdock_utils_pvt.delete_crossdock_reservation(
x_return_status => l_return_status
, p_rsv_rec => p_rsv_rec
);
debug_print('delete_crossdock_reservation returns error');
debug_print('delete_crossdock_reservation returns unexpected error');
, 'delete_crossdock_reservation'
);
END delete_crossdock_reservation;
l_delete_flag VARCHAR2(1) := 'N';
SELECT wms_enabled_flag, project_reference_enabled
INTO l_wms_enabled, l_pjm_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT count(min(po_distribution_id))
INTO l_project_count
FROM po_distributions_all
WHERE po_header_id = p_supply_source_header_id
AND line_location_id = p_supply_source_line_id
group by project_id, task_id;
debug_print('We need to delete the reservations for this supply');
-- delete_flag to yes. delete all reservations for that
-- supply line.
l_delete_flag := 'Y';
p_delete_flag => l_delete_flag,
p_sort_by_criteria => l_sort_by_criteria,
x_quantity_modified => l_qty_modified
);
SELECT MIN(project_id), MIN(task_id)
INTO l_project_id, l_task_id
FROM po_distributions_all
WHERE po_header_id = p_supply_source_header_id
AND line_location_id = p_supply_source_line_id;
debug_print('We need to delete the reservations for this supply');
-- delete_flag to yes. delete all reservations for that
-- supply line.
l_delete_flag := 'Y';
p_delete_flag => l_delete_flag,
p_sort_by_criteria => l_sort_by_criteria,
x_quantity_modified => l_qty_modified
);
SELECT count(1)
INTO l_project_count
FROM po_requisition_lines_all prl, po_req_distributions_all prd
WHERE prl.requisition_header_id = p_supply_source_header_id
AND prl.requisition_line_id = p_supply_source_line_id
AND prl.requisition_line_id = prd.requisition_line_id
group by prd.project_id, prd.task_id;
SELECT MIN(prd.project_id), MIN(prd.task_id)
INTO l_project_id, l_task_id
FROM po_requisition_lines_all prl, po_req_distributions_all prd
WHERE prl.requisition_header_id = p_supply_source_header_id
AND prl.requisition_line_id = p_supply_source_line_id
AND prl.requisition_line_id = prd.requisition_line_id;
SELECT count(1)
INTO l_project_count
FROM wip_discrete_jobs
WHERE wip_entity_id = p_supply_source_header_id
group by project_id, task_id;
SELECT project_id, task_id
INTO l_project_id, l_task_id
FROM wip_discrete_jobs
WHERE wip_entity_id = p_supply_source_header_id;
SELECT *
FROM mtl_system_items
WHERE inventory_Item_Id = p_inventory_item_id;
SELECT current_status,
reservation_id,
current_organization_id,
revision,
current_subinventory_code,
current_locator_id,
lot_number,
lpn_id
INTO l_current_status,
l_reservation_id,
l_organization_id,
l_revision,
l_subinventory,
l_locator_id,
l_lot_number,
l_lpn_id
FROM mtl_serial_numbers
WHERE serial_number = p_orig_serial_array(i).serial_number
AND inventory_item_id =
p_orig_serial_array(i).inventory_item_id;
IF (p_rsv_action_name = 'CREATE' OR p_rsv_action_name = 'DELETE' OR p_rsv_action_name = 'RELIEVE') THEN
IF (l_reservation_id <> nvl(l_orig_rsv_rec.reservation_id, l_reservation_id) OR
l_organization_id <> nvl(l_orig_rsv_rec.organization_id, l_organization_id) OR
p_orig_serial_array(i).inventory_item_id <>
nvl(l_orig_rsv_rec.inventory_item_id, p_orig_serial_array(i).inventory_item_id) OR
l_revision <> nvl(l_orig_rsv_rec.revision, l_revision) OR
l_subinventory <> nvl(l_orig_rsv_rec.subinventory_code, l_subinventory) OR
l_locator_id <> nvl(l_orig_rsv_rec.locator_id, l_locator_id) OR
l_lot_number <> nvl(l_orig_rsv_rec.lot_number, l_lot_number) OR
l_lpn_id <> nvl(l_orig_rsv_rec.lpn_id, l_lpn_id)) THEN
IF (l_debug = 1) THEN
debug_print('The serial controls is not same as the reservation controls');
SELECT current_status,
reservation_id,
current_organization_id,
revision,
current_subinventory_code,
current_locator_id,
lot_number,
lpn_id
INTO l_current_status,
l_reservation_id,
l_organization_id,
l_revision,
l_subinventory,
l_locator_id,
l_lot_number,
l_lpn_id
FROM mtl_serial_numbers
WHERE serial_number = p_to_serial_array(i).serial_number
AND inventory_item_id = p_to_serial_array(i).inventory_item_id;
SELECT *
FROM mtl_system_items
WHERE inventory_Item_Id = p_orig_rsv_rec.inventory_item_id;
SELECT distinct
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
wsh_delivery_details wdd
WHERE mtrl.organization_id = p_orig_rsv_rec.organization_id
AND mtrl.inventory_item_id = p_orig_rsv_rec.inventory_item_id
AND mtrl.line_status <> 5 -- not closed move order lines
AND NVL(mtrl.quantity_delivered, 0) = 0
AND mtrl.txn_source_id = p_orig_rsv_rec.supply_source_header_id
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 2 -- WIP LPN
AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
AND mtrl.backorder_delivery_detail_id IS NOT NULL
AND mtrl.backorder_delivery_detail_id =
wdd.delivery_detail_id;
IF p_rsv_action_name IN ('UPDATE', 'TRANSFER') THEN
-- validate item and organization information
validate_organization
(
x_return_status => l_return_status
, p_organization_id => p_to_rsv_rec.organization_id
, x_org_cache_index => l_to_org_cache_index
);
IF (p_rsv_action_name = 'UPDATE') THEN
update_crossdock_reservation
(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_orig_rsv_rec => p_orig_rsv_rec
, p_to_rsv_rec => p_to_rsv_rec
);
SELECT distinct
inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
wsh_delivery_details wdd
WHERE mtrl.organization_id = p_to_rsv_rec.organization_id
AND mtrl.inventory_item_id = p_to_rsv_rec.inventory_item_id
AND mtrl.line_status <> 5 -- not closed move order lines
AND NVL(mtrl.quantity_delivered, 0) = 0
AND mtrl.txn_source_id = p_to_rsv_rec.supply_source_header_id
AND mtrl.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context = 2 -- WIP LPN
AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
AND mtrl.backorder_delivery_detail_id IS NOT NULL
AND mtrl.backorder_delivery_detail_id =
wdd.delivery_detail_id;
debug_print(' end of update/ transfer ' || l_return_status);
update_crossdock_reservation
(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_orig_rsv_rec => p_orig_rsv_rec
, p_to_rsv_rec => p_to_rsv_rec
);
IF (p_rsv_action_name = 'DELETE') THEN
validate_organization
(
x_return_status => l_return_status
, p_organization_id => p_orig_rsv_rec.organization_id
, x_org_cache_index => l_orig_org_cache_index
);
delete_crossdock_reservation
(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => p_orig_rsv_rec
);