[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- Delete previous occurrences of this exception in mst_exceptions and the details table
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 704;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 704;
--Create fresh entry for exception and keep exception_id for updates in details table
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 700, 704, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 704;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
location_id,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
SELECT mst_exception_details_s.nextval, excptnId , plan_idIn, 704,
location_id,
userIdIn, userIdIn,-1,sysdate, sysdate, 3
FROM (SELECT distinct wsh_location_id as location_id
FROM WSH_LOCATIONS loc,
MST_DELIVERY_DETAILS det
WHERE det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND ( det.ship_from_location_id = loc.wsh_location_id
OR det.ship_to_location_id = loc.wsh_location_id)
AND ( latitude is null OR longitude is null)
UNION
SELECT distinct wsh_location_id as location_id
FROM FTE_LOCATION_PARAMETERS param,
WSH_LOCATIONS loc
WHERE loc.wsh_location_id = param.location_id
AND ( param.consolidation_allowed = 'Y'
OR param.deconsolidation_allowed = 'Y'
OR param.crossdocking_allowed = 'Y')
AND ( loc.latitude is null OR loc.longitude is null));
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generate delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
-- Delete previous occurrences of this exception in mst_exceptions and the details table
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 900;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 900;
--Create fresh entry for exception and keep exception_id for updates in details table
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 900, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 900;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS,
delivery_detail_id, delivery_id)
SELECT mst_exception_details_s.nextval, excptnId , plan_idIn, 900,
userIdIn, userIdIn,-1,sysdate, sysdate,3,
delivery_detail_id, delivery_id
FROM ( SELECT DISTINCT det.delivery_detail_id AS delivery_detail_id, da.delivery_id AS delivery_id
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da,
WSH_REGION_LOCATIONS origReg,
WSH_REGION_LOCATIONS destReg
WHERE det.PLAN_ID = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND da.parent_delivery_detail_id is null
AND origReg.location_id = det.ship_from_location_id
AND destReg.location_id = det.ship_to_location_id
--FTE_LOCATION_MILEAGES is distance table
--key is . Origin and destination are FK to WSH_REGIONS.
--IDENTIFIER_TYPE can be city or state (Anuj)
AND NOT EXISTS (SELECT DISTANCE
FROM FTE_LOCATION_MILEAGES
WHERE origReg.region_id = origin_id
AND destReg.region_id = destination_id));
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
--Delete previous occurrences of this exception in mst_exceptions and the details table
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 219;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 219;
--Create fresh entry for exception and keep exception_id for updates in details table
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 200, 219, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 219;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
SELECT mst_exception_details_s.nextval, excptnId , det.plan_id, 219,
det.delivery_detail_id, da.delivery_id,
userIdIn, userIdIn,-1,sysdate, sysdate,3
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da
WHERE det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND da.parent_delivery_detail_id is null
AND ( det.NET_WEIGHT = 0 OR det.NET_WEIGHT IS NULL
OR det.VOLUME = 0 OR det.VOLUME IS NULL
OR det.REQUESTED_QUANTITY = 0);
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE,STATUS)
SELECT mst_exception_details_s.nextval, excptnId , det.plan_id,219,
det.delivery_detail_id, da.delivery_id,
userIdIn, userIdIn,-1,sysdate, sysdate,3
FROM MST_DELIVERY_DETAILS det,
MTL_SYSTEM_ITEMS it,
MST_DELIVERY_ASSIGNMENTS da
WHERE det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND it.inventory_item_id = det.inventory_item_id
AND it.organization_id = det.organization_id
AND ( it.unit_volume = 0 OR it.unit_volume IS NULL
OR it.unit_weight = 0 OR it.unit_weight IS NULL);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
SELECT det.delivery_detail_id,
IT.UNIT_LENGTH, IT.UNIT_WIDTH, IT.UNIT_HEIGHT, it.DIMENSION_UOM_CODE, it.inventory_item_id
FROM MST_DELIVERY_DETAILS DET,
MTL_SYSTEM_ITEMS IT
WHERE IT.INVENTORY_ITEM_ID = DET.INVENTORY_ITEM_ID
AND det.organization_id = it.organization_id
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.plan_id = plan_idIn;
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 902;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 902;
SELECT
NVL(CONV_TO_UOM(vt.usable_length, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99),
NVL(CONV_TO_UOM(vt.usable_width, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99),
NVL(CONV_TO_UOM(vt.usable_height, si.dimension_uom_code, tp_dimension_uom, si.inventory_item_id),-99)
INTO length, width, height
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.usable_length is not null
AND vt.usable_width is not null
AND vt.usable_height is not null
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv,
wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id)
AND NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0) =
(SELECT MAX(NVL(CONV_TO_UOM(si2.internal_volume, si2.volume_uom_code, tp_volume_uom, si2.inventory_item_id),0))
FROM fte_vehicle_types vt2,
mtl_system_items_b si2
WHERE vt2.inventory_item_id = si2.inventory_item_id
AND vt2.organization_id = si2.organization_id
AND vt2.usable_length is not null
AND vt2.usable_width is not null
AND vt2.usable_height is not null
AND vt2.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv,
wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id))
AND ROWNUM = 1; --we need to pick one vehicle
SELECT GREATEST(width, height) INTO midDimV FROM dual;
SELECT LEAST(width, height) INTO minDimV FROM dual;
SELECT GREATEST(length, height) INTO midDimV FROM dual;
SELECT LEAST(length, height) INTO minDimV FROM dual;
SELECT GREATEST(width, length) INTO midDimV FROM dual;
SELECT LEAST(width, length) INTO minDimV FROM dual;
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 902, 0,userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 902;
SELECT GREATEST(c1rec.UNIT_WIDTH, c1rec.UNIT_HEIGHT) INTO midDimP FROM dual;
SELECT LEAST(c1rec.UNIT_WIDTH, c1rec.UNIT_HEIGHT) INTO minDimP FROM dual;
SELECT GREATEST(c1rec.UNIT_LENGTH, c1rec.UNIT_HEIGHT) INTO midDimP FROM dual;
SELECT LEAST(c1rec.UNIT_LENGTH, c1rec.UNIT_HEIGHT) INTO minDimP FROM dual;
SELECT GREATEST(c1rec.UNIT_WIDTH, c1rec.UNIT_LENGTH) INTO midDimP FROM dual;
SELECT LEAST(c1rec.UNIT_WIDTH, c1rec.UNIT_LENGTH) INTO minDimP FROM dual;
SELECT da.delivery_id
INTO deliveryId
FROM mst_delivery_assignments da,
mst_delivery_details det
WHERE det.delivery_detail_id = da.delivery_detail_id (+)
AND det.plan_id = plan_idIn
AND c1rec.delivery_detail_id = det.delivery_detail_id;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id,
exception_type, delivery_detail_id,
delivery_id, number1, number2, number3, char1,
created_by, last_updated_by, LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE, CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId, plan_idIn,
902, c1rec.delivery_detail_id,
deliveryId, length, width, height, tp_dimension_uom,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
SELECT det.delivery_detail_id, da.delivery_id,
NVL(det.net_weight,0) weight,
NVL(det.volume,0) volume
FROM MST_DELIVERY_DETAILS det,
MTL_SYSTEM_ITEMS si,
MST_DELIVERY_ASSIGNMENTS da
WHERE det.PLAN_ID = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND (det.requested_quantity = 1 OR det.container_flag = 1)
AND det.inventory_item_id = si.inventory_item_id
AND det.organization_id = si.organization_id;
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 903;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 903;
SELECT weight_uom, volume_uom
INTO tp_weight_uom, tp_volume_uom
FROM mst_plans
WHERE plan_id = plan_idIn;
SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
INTO maxVolumeV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
INTO maxWeightV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 903, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 903;
--insert exception detected into mst_exception_details
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
number1, number2,
char1, char2,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 903,
piece.delivery_detail_id, piece.delivery_id,
maxWeightV, maxVolumeV,
tp_weight_uom, tp_volume_uom,
userIdIn, userIdIn,-1,sysdate, sysdate,3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
SELECT det.delivery_detail_id, da.delivery_id,
NVL(det.net_weight,0) weight,
NVL(det.volume,0) volume
FROM MST_DELIVERY_DETAILS det,
MTL_SYSTEM_ITEMS si,
MST_DELIVERY_ASSIGNMENTS da
WHERE det.PLAN_ID = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND det.requested_quantity > 1
AND det.inventory_item_id = si.inventory_item_id
AND det.organization_id = si.organization_id;
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 904;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 904;
SELECT weight_uom, volume_uom
INTO tp_weight_uom, tp_volume_uom
FROM mst_plans
WHERE plan_id = plan_idIn;
SELECT si.internal_volume as volume, si.volume_uom_code, VEHICLE_TYPE_ID, vt.inventory_item_id , vt.organization_id
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id in (select distinct vehicle_type_id
from wsh_carriers carr, wsh_carrier_services serv, WSH_CARRIER_VEHICLE_TYPES carrVeh
where carr.carrier_id = serv.carrier_id
and serv.mode_of_transport like 'TRUCK'
and carrVeh.carrier_id = carr.carrier_id)
AND si.internal_volume =
(SELECT MAX(si.internal_volume) as volume
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id in (select distinct vehicle_type_id
from wsh_carriers carr, wsh_carrier_services serv, WSH_CARRIER_VEHICLE_TYPES carrVeh
where carr.carrier_id = serv.carrier_id
and serv.mode_of_transport like 'TRUCK'
and carrVeh.carrier_id = carr.carrier_id))
AND ROWNUM = 1;
SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
INTO maxVolumeV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
INTO maxWeightV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 904, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 904;
--insert exception detected into mst_exception_details
debug_output('delivery_detail_id = ' || TO_CHAR(deliveryLine.DELIVERY_DETAIL_ID) || ', volume = ' || deliveryLine.volume || ', weight = ' || deliveryLine.weight);
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
number1, number2, char1, char2,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 904,
deliveryLine.DELIVERY_DETAIL_ID, deliveryLine.delivery_id,
maxWeightV, maxVolumeV, tp_weight_uom, tp_volume_uom,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
excptnId NUMBER; -- used to get the parent exception id when inserting records in exception details.
SELECT delivery_id, NVL(gross_weight,0) weight, NVL(volume,0) volume
FROM MST_DELIVERIES
WHERE PLAN_ID = plan_idIn
AND (PLANNED_FLAG <> 2
OR PRESERVE_GROUPING_FLAG <> 2
OR KNOWN_TE_FIRM_STATUS <> 3);
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 905;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 905;
SELECT weight_uom, volume_uom
INTO tp_weight_uom, tp_volume_uom
FROM mst_plans
WHERE plan_id = plan_idIn;
SELECT MAX(NVL(CONV_TO_UOM(si.internal_volume, si.volume_uom_code, tp_volume_uom, si.inventory_item_id),0)) as volume
INTO maxVolumeV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
SELECT MAX(NVL(CONV_TO_UOM(si.maximum_load_weight, si.weight_uom_code,tp_weight_uom, si.inventory_item_id),0)) as load_weight
INTO maxWeightV
FROM fte_vehicle_types vt,
mtl_system_items_b si
WHERE vt.inventory_item_id = si.inventory_item_id
AND vt.organization_id = si.organization_id
AND vt.vehicle_type_id IN (SELECT distinct vehicle_type_id
FROM wsh_carriers carr, wsh_carrier_services serv, wsh_carrier_vehicle_types carrVeh
WHERE carr.carrier_id = serv.carrier_id
AND serv.mode_of_transport like 'TRUCK'
AND carrVeh.carrier_id = carr.carrier_id);
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 900, 905, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 905;
--insert exception detected into mst_exception_details
debug_output('delivery_id = ' || TO_CHAR(delivery.DELIVERY_ID) || ', volume = ' || delivery.volume || ', weight = ' || delivery.weight);
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_id,
number1, number2, char1, char2,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 905,
delivery.delivery_id,
maxWeightV, maxVolumeV, tp_weight_uom, tp_volume_uom,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
--in case no exception of this type was generated delete entry in mst_exceptions table
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
SELECT distinct delivery_id, det.delivery_detail_id,
det.ship_from_location_id ship_from_location_id,
det.ship_to_location_id ship_to_location_id,
latest_acceptable_date, earliest_pickup_date
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da,
WSH_LOCATIONS loc1,
WSH_LOCATIONS loc2
WHERE det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND parent_delivery_detail_id is null
AND loc1.wsh_location_id = det.ship_from_location_id
AND loc2.wsh_location_id = det.ship_to_location_id;
--Auxiliary data structures to cache all the data to insert in exception_details
TYPE delivery_id_t IS TABLE OF MST_DELIVERIES.DELIVERY_ID%TYPE INDEX BY BINARY_INTEGER;
--Delete previous occurrences of this exception in mst_exceptions and the details table
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 104;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 104;
--SELECT plan_start_date into planStartDate
SELECT start_date into planStartDate
FROM mst_plans
WHERE plan_id = plan_idIn;
--Create fresh entry for exception and keep exception_id for updates in details table
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 100, 104, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 104;
SELECT MIN(lm.distance)
INTO distance
FROM FTE_LOCATION_MILEAGES lm,
WSH_REGION_LOCATIONS origReg,
WSH_REGION_LOCATIONS destReg
WHERE origReg.location_id = delivLine.ship_from_location_id
AND destReg.location_id = delivLine.ship_to_location_id
AND origReg.region_id = origin_id
AND destReg.region_id = destination_id;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_id, delivery_detail_id,
number1, number2,
number3,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_IdIn, 104,
all_details_delivery_id(k), all_details_delivery_detail_id(k),
all_details_distance(k), all_details_time(k),
all_details_availTime(k),
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
SELECT DISTINCT da.delivery_id, det.delivery_detail_id,
det.ship_from_location_id, det.ship_to_location_id,
((det.latest_acceptable_date - plan.start_date)*24) AS availTime
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da,
MST_PLANS plan
WHERE det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null --added so we consider only input data from TE
AND det.delivery_detail_id = da.delivery_detail_id (+)
AND da.parent_delivery_detail_id is null
AND plan.plan_id = plan_idIn
AND ( (det.latest_acceptable_date + thresholdInHrs/24 < plan.start_date)
OR (det.latest_pickup_date + thresholdInHrs/24 < plan.start_date) );
--Delete previous occurrences of this exception in mst_exceptions and the details table
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 105;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 105;
--Create fresh entry for exception and keep exception_id for updates in details table
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 100, 105, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 105;
INSERT INTO mst_exception_details (exception_detail_id, exception_id, plan_id, exception_type,
delivery_id, delivery_detail_id,
number2, number3,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_IdIn, 105,
delivLine.delivery_id, delivLine.delivery_detail_id,
requiredTransitTimeInHrs, availTransitTimeInHrs,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
SELECT NVL(MIN(NVL(CONV_TO_UOM(sm.INTRANSIT_TIME,sm.TIME_UOM_CODE,tp_time_uom,0),99999)),99999)
INTO minimumTransitTime
FROM MTL_INTERORG_SHIP_METHODS sm,
WSH_REGION_LOCATIONS rl1,
WSH_REGION_LOCATIONS rl2
WHERE rl1.location_id = ship_from
AND rl2.location_id = ship_to
AND ( ship_from = sm.from_location_id AND ship_to = sm.to_location_id
OR (ship_from = sm.from_location_id AND rl2.region_id = sm.to_region_id)
OR (rl1.region_id = sm.from_region_id AND ship_to = sm.to_location_id)
OR (rl1.region_id = sm.from_region_id AND rl2.region_id = sm.to_region_id));
SELECT MIN(CONV_TO_UOM(distTable.transit_time,distTable.transit_time_uom,tp_time_uom,0))
INTO tl_minimumTransitTime
FROM FTE_LOCATION_MILEAGES distTable,
WSH_REGION_LOCATIONS origReg,
WSH_REGION_LOCATIONS destReg
WHERE origReg.location_id = ship_from
AND destReg.location_id = ship_to
AND origReg.region_id = distTable.origin_id
AND destReg.region_id = distTable.destination_id;
SELECT NVL(latitude,0), NVL(longitude,0)
INTO lat_1, lon_1
FROM WSH_LOCATIONS
WHERE wsh_location_id = ship_from;
SELECT NVL(latitude,0), NVL(longitude,0)
INTO lat_2, lon_2
FROM WSH_LOCATIONS
WHERE wsh_location_id = ship_to;
SELECT det.delivery_detail_id, det.ship_from_location_id, da.delivery_id,
det.earliest_pickup_date, det.latest_pickup_date
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da
WHERE da.delivery_detail_id = det.delivery_detail_id (+)
AND da.parent_delivery_detail_id is null
AND det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null; --added so we consider only input data from TE
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 404;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 404;
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval,plan_idIn, 400, 404, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 404;
SELECT 2 INTO exceptionFlag --SYS_NO (no exception since there is at least one overlapping shift)
FROM DUAL
WHERE EXISTS
( SELECT caldates.CALENDAR_DATE
FROM BOM_CALENDAR_DATES caldates,
BOM_SHIFT_DATES sdates,
BOM_SHIFT_TIMES bshift,
MST_PLANS plan
WHERE plan.plan_id = plan_idIn
AND caldates.CALENDAR_CODE = sdates.CALENDAR_CODE (+)
AND caldates.CALENDAR_DATE = sdates.SHIFT_DATE (+)
AND sdates.CALENDAR_CODE = bshift.CALENDAR_CODE (+)
AND sdates.SHIFT_NUM = bshift.SHIFT_NUM (+)
AND caldates.SEQ_NUM is not null
AND sdates.SEQ_NUM(+) is not null
-- AND caldates.CALENDAR_DATE between sysdate and plan.CUTOFF_DATE+numberOfCalDays
AND caldates.CALENDAR_DATE between local_EPD-1 and local_LPD+1
AND caldates.CALENDAR_CODE = calCode
--check if any shift falls inside [EPD,LPD]
AND ( ( local_EPD <= (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) AND
(caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LPD)
OR
( local_EPD <= (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) AND
(caldates.CALENDAR_DATE+bshift.TO_TIME/86400) <= local_LPD)
--check if EPD or LPD falls inside any shift
OR
( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_EPD AND
local_EPD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) )
OR
( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LPD AND
local_LPD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) ) ) );
--insert exception detected into mst_exception_details
INSERT INTO mst_exception_details
(exception_detail_id,exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
date1, date2,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 404,
delivDetail, delivId,
server_EPD, server_LPD,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
SELECT det.delivery_detail_id, det.ship_to_location_id, da.delivery_id,
det.earliest_acceptable_date, det.latest_acceptable_date
FROM MST_DELIVERY_DETAILS det,
MST_DELIVERY_ASSIGNMENTS da
WHERE da.delivery_detail_id = det.delivery_detail_id (+)
AND da.parent_delivery_detail_id is null
AND det.plan_id = plan_idIn
AND det.SPLIT_FROM_DELIVERY_DETAIL_ID is null; --added so we consider only input data from TE
DELETE FROM mst_exception_details
WHERE plan_id = plan_idIn
AND exception_type = 405;
DELETE FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 405;
--insert entry in mst_exceptions
INSERT INTO mst_exceptions (exception_id, plan_id, exception_group, exception_type, exception_count,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE)
VALUES (mst_exceptions_s.nextval, plan_idIn, 400, 405, 0, userIdIn,
userIdIn,-1,sysdate, sysdate);
SELECT exception_id INTO excptnId
FROM mst_exceptions
WHERE plan_id = plan_idIn
AND exception_type = 405;
SELECT 2 INTO exceptionFlag --SYS_NO (no exception since there is at least one overlapping shift)
FROM DUAL
WHERE EXISTS
( SELECT caldates.CALENDAR_DATE
FROM BOM_CALENDAR_DATES caldates,
BOM_SHIFT_DATES sdates,
BOM_SHIFT_TIMES bshift,
MST_PLANS plan
WHERE plan.plan_id = plan_idIn
AND caldates.CALENDAR_CODE = sdates.CALENDAR_CODE (+)
AND caldates.CALENDAR_DATE = sdates.SHIFT_DATE (+)
AND sdates.CALENDAR_CODE = bshift.CALENDAR_CODE (+)
AND sdates.SHIFT_NUM = bshift.SHIFT_NUM (+)
AND caldates.SEQ_NUM is not null
AND sdates.SEQ_NUM(+) is not null
-- AND caldates.CALENDAR_DATE between sysdate and plan.CUTOFF_DATE+numberOfCalDays
AND caldates.CALENDAR_DATE between local_EDD-1 and local_LDD+1
AND caldates.CALENDAR_CODE = calCode
--check if any shift falls inside [EDD,LDD]
AND ( ( local_EDD <= (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) AND
(caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LDD)
OR
( local_EDD <= (caldates.CALENDAR_DATE+bshift.TO_TIME/86400) AND
(caldates.CALENDAR_DATE+bshift.TO_TIME/86400) <= local_LDD)
--check if EDD or LDD falls inside any shift
OR
( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_EDD AND
local_EDD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) )
OR
( (caldates.CALENDAR_DATE+bshift.FROM_TIME/86400) <= local_LDD AND
local_LDD <= ( caldates.CALENDAR_DATE+bshift.TO_TIME/86400) ) ) );
--insert exception detected into mst_exception_details
INSERT INTO mst_exception_details
(exception_detail_id, exception_id, plan_id, exception_type,
delivery_detail_id, delivery_id,
date1, date2,
created_by, last_updated_by, LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
CREATION_DATE, STATUS)
VALUES (mst_exception_details_s.nextval, excptnId , plan_idIn, 405,
delivDetail, delivId,
server_EDD, server_LDD,
userIdIn, userIdIn,-1,sysdate, sysdate, 3);
--update count of this exception in mst_exceptions
UPDATE mst_exceptions
SET EXCEPTION_COUNT = ( select count(*)
from mst_exception_details det
where det.exception_id = excptnId)
WHERE EXCEPTION_id = excptnId;
DELETE FROM mst_exceptions
WHERE exception_count = 0 and exception_id = excptnId;
SELECT uom_class INTO l_uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code
AND ROWNUM = 1;
SELECT uom_class INTO l_dest_uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_dest_uom_code
AND ROWNUM = 1;
SELECT muc1.conversion_rate/muc2.conversion_rate INTO p_conv_rate
FROM mtl_uom_conversions muc1,
mtl_uom_conversions muc2
WHERE muc1.inventory_item_id = 0
AND muc2.inventory_item_id = 0
AND muc1.uom_class = muc2.uom_class
AND muc1.uom_class = l_uom_class
AND muc1.uom_code = p_uom_code
AND muc2.uom_code = p_dest_uom_code
AND ROWNUM = 1;
SELECT muc.conversion_rate INTO p_conv_rate
FROM mtl_uom_conversions_view muc
WHERE muc.inventory_item_id = p_inventory_item_id
AND muc.primary_uom_code = p_uom_code
AND muc.uom_code = p_dest_uom_code
AND rownum = 1;
SELECT muc.conversion_rate INTO p_conv_rate
FROM mtl_uom_conversions_view muc
WHERE muc.inventory_item_id = p_inventory_item_id
AND muc.primary_uom_code = p_dest_uom_code
AND muc.uom_code = p_uom_code
AND rownum = 1;
SELECT nvl(threshold_value,0) INTO threshold
FROM mst_excep_preferences
WHERE exception_type = exceptionType
and user_id = -9999
and ROWNUM = 1;
SELECT p.TIME_UOM, p.DISTANCE_UOM, p.dimension_uom, p.volume_uom
INTO tp_time_uom, tp_distance_uom, tp_dimension_uom, tp_volume_uom
FROM MST_PLANS p
WHERE plan_id = plan_idIn;
SELECT nvl(wgp.AVG_HWAY_SPEED,'-99') , nvl(wgp.TL_HWAY_DIS_EMP_CONSTANT,'-99'), wgp.GU_DISTANCE_UOM, wgp.TIME_UOM
INTO lAvgHwayDist, lDisConstant, lDistanceUomCode, lTimeUomCode
FROM MST_PARAMETERS mgp, WSH_GLOBAL_PARAMETERS wgp, MST_PARAMETERS mup
WHERE mgp.user_id = -9999
and mup.user_id = user_idIn
and rownum = 1;
debug_output('first select gave no results in parameters query');
SELECT nvl(wgp.AVG_HWAY_SPEED,'-99'), nvl(wgp.TL_HWAY_DIS_EMP_CONSTANT,'-99'), wgp.GU_DISTANCE_UOM, wgp.TIME_UOM
INTO lAvgHwayDist, lDisConstant, lDistanceUomCode, lTimeUomCode
FROM MST_PARAMETERS mgp, WSH_GLOBAL_PARAMETERS wgp
WHERE mgp.user_id = -9999
and rownum = 1;
debug_output('second select gave no results in parameters query, assuming defaults');
SELECT count(*) into planRows
FROM mst_plans
WHERE plan_id = plan_idIn;
SELECT 'no entry or too many entries in mst plans for plan ' || TO_CHAR(plan_idIn)
INTO errbuf
FROM dual;
SELECT created_by, state, program, request_id
INTO user_id, mst_state, mst_program, mst_request_id
FROM mst_plans
WHERE plan_id = plan_idIn;
SELECT 'Data needs to be snapshoted before running audit report'
INTO errbuf
FROM dual;
SELECT 'Audit report cannot be run in current state, state = ' || TO_CHAR(mst_state)
INTO errbuf
FROM dual;
--update MST_PLANS signaling successful start of audit report
UPDATE MST_PLANS
SET state=4, program=8, request_id = own_request_id
WHERE plan_id = plan_idIn;
--if snapshotIsCaller == 1 then update state to 1
IF snapshotIsCaller = 1 THEN
mst_state := 1;
--update MST_PLANS signaling successful end of audit report
UPDATE MST_PLANS
SET state=mst_state, program=NULL
WHERE plan_id = plan_idIn;
debug_output('Update MST_PLANS.program = NULL');
debug_output('Update MST_PLANS.state = ' || mst_state);
Then uncomment code to insert messages to that table
*/
/*
insert into mst_audit_rep_excp_debug(pk, plan_id,message_seq_num, date_message,message)
values (mst_audit_rep_excp_debug_s.nextval, tp_plan_id, msg_seq_num, sysdate, p_str);