CREATE OR REPLACE PACKAGE BODY TMS.tms_ws_v2
AS
FUNCTION download_drivers (p_hub IN VARCHAR2)
RETURN driver_names
IS
v_driver_names driver_names;
BEGIN
NULL;
SELECT driver_name ("Username", "Title", "Name")
BULK COLLECT
INTO v_driver_names
FROM tms.tms_driver_location dloc, tms_location loc
WHERE loc.business_unit_4 = p_hub
AND loc.business_unit = dloc."Location";
RETURN v_driver_names;
END;
FUNCTION download_exceptlist
RETURN ex_table
IS
v_ex_table ex_table;
BEGIN
SELECT ex_rec (exception_code,
exception_code_description,
exception_type)
BULK COLLECT
INTO v_ex_table
FROM tms_exception_code
WHERE language_code = 'EN'
AND exception_type IN ('STOP', 'DROP', 'PICK')
AND is_active = 'Y';
RETURN v_ex_table;
END;
FUNCTION download_bol (p_bol IN NUMBER,
p_force_download IN INTEGER,
p_driver_name IN VARCHAR2,
p_device_id IN VARCHAR2,
p_version IN VARCHAR2,
o_req_result OUT reqresult)
RETURN route_data_v2
AS
ex_finished_bol exception;
ex_cancelled_bol exception;
ex_downloaded_bol exception;
ex_nodata_bol exception;
ex_exempt_bol exception;
ex_device_bol exception;
ex_version_bol exception;
v_instance_id INTEGER;
v_route_data route_data_v2
:= NEW route_data_v2 (NULL,
NULL,
NULL,
NULL) ;
v_route_header route_header_v2;
v_route_stops route_stops_v2;
v_stop_details stop_details_v2;
v_cnt INTEGER;
v_date DATE;
v_download_date DATE := SYSDATE;
v_num_version INTEGER;
BEGIN
-- ex_device_bol
SELECT COUNT (1)
INTO v_cnt
FROM tms_location
WHERE location_type = 'HUB'
AND business_unit_4 =
SUBSTR (UPPER (TRIM (p_device_id)), 1, 4)
AND REGEXP_LIKE (TRIM (p_device_id), '^[A-Z]{4}+');
IF v_cnt = 0
THEN
RAISE ex_device_bol;
END IF;
BEGIN
SELECT TO_NUMBER (
SUBSTR (p_version, 0, INSTR (p_version, '.') - 1)
|| LPAD (SUBSTR (p_version, INSTR (p_version,
'.',
1,
1)
+ 1, INSTR (p_version,
'.',
1,
2)
- 1
- INSTR (p_version,
'.',
1,
1)), 2, '0')
|| SUBSTR (p_version, INSTR (p_version, '.', 3) + 1, 1)
)
INTO v_num_version
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
RAISE ex_version_bol;
END;
BEGIN
SELECT COUNT (1)
INTO v_cnt
FROM tms_client_version
WHERE hub = SUBSTR (UPPER (TRIM (p_device_id)), 1, 4)
AND TO_NUMBER (
SUBSTR (version, 0, INSTR (version, '.') - 1)
|| LPAD (SUBSTR (version, INSTR (version,
'.',
1,
1)
+ 1, INSTR (version,
'.',
1,
2)
- 1
- INSTR (version,
'.',
1,
1)), 2, '0')
|| SUBSTR (version, INSTR (version, '.', 3) + 1, 1)
) <= v_num_version;
IF v_cnt = 0
THEN
RAISE ex_version_bol;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RAISE ex_version_bol;
END;
IF p_force_download = 200
THEN
UPDATE tms_route_download trd1
SET confirm_date = SYSDATE
WHERE trd1.ROWID =
(SELECT detailrow
FROM (SELECT trd2.ROWID detailrow,
ROW_NUMBER ()
OVER (
PARTITION BY bill_of_lading_id,
device_id,
driver_name
ORDER BY download_date DESC
)
rn
FROM tms_route_download trd2
WHERE trd2.bill_of_lading_id = p_bol
AND trd2.device_id =
TRIM (p_device_id)
AND trd2.driver_name = p_driver_name)
WHERE rn = 1);
IF sql%FOUND
THEN
o_req_result :=
NEW reqresult (1,
0,
'Route download be confirmed.',
SYSDATE);
RETURN v_route_data;
ELSE
o_req_result :=
NEW reqresult (-20005,
0,
'No download records founded.',
SYSDATE);
RETURN v_route_data;
END IF;
END IF;
--Check the cancelled bols .
SELECT COUNT (1)
INTO v_cnt
FROM wms_intf_bol_cancelled
WHERE bill_of_lading_id = p_bol;
IF v_cnt > 0
THEN
RAISE ex_cancelled_bol;
END IF;
--Check if it's a finished bols.
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_header
WHERE bill_of_lading_id = p_bol AND end_of_day IS NOT NULL;
IF v_cnt > 0
THEN
RAISE ex_finished_bol;
END IF;
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_header
WHERE bill_of_lading_id = p_bol;
--Check if route data exists.Also, fill the route header.
IF v_cnt = 0
THEN
RAISE ex_nodata_bol;
ELSE
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_header
WHERE bill_of_lading_id = p_bol AND download_exempt = 'Y';
IF v_cnt > 0
THEN
RAISE ex_exempt_bol;
END IF;
END IF;
SELECT NEW route_header_v2 (rh.bill_of_lading_id,
rh.truck_route_instance_id,
rh.trailer_id_original_1,
rh.truck_route_id,
rh.dow,
rh.scheduled_departure_date,
rh.completed_by)
INTO v_route_header
FROM tms.tms_route_header rh
WHERE rh.bill_of_lading_id = p_bol;
--Check if it's already downloaded.
IF p_force_download = 0
THEN
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_download d, tms_route_header h
WHERE h.truck_route_instance_id = d.truck_route_instance_id
AND h.bill_of_lading_id = p_bol;
IF v_cnt > 0
THEN
SELECT download_date
INTO v_date
FROM ( SELECT d.download_date
FROM tms_route_download d, tms_route_header h
WHERE h.truck_route_instance_id =
d.truck_route_instance_id
AND h.bill_of_lading_id = p_bol
ORDER BY download_date DESC)
WHERE ROWNUM = 1;
RAISE ex_downloaded_bol;
END IF;
END IF;
v_route_data.route_header := v_route_header;
--Fill route stops.
SELECT route_stop_v2 (
h.truck_route_instance_id,
s.stop_id,
l.location_name,
s.location_id,
l.address_1,
l.address_2,
l.city,
l.state_abbr,
l.alarm_code,
s.scheduled_arrival_dt,
s.scheduled_departure_dt,
s.actual_arrival_dt,
s.contacts,
s.special_instructions,
SUBSTR (s.status, 1, 1),
s.exception_code,
'Y',
s.stop_seq,
s.enroute_time,
s.exception_comments,
(CASE
WHEN l.business_unit_4 IS NULL THEN 'vendor'
ELSE 'store'
END),
s.is_blank_stop,
s.first_scan_time,
NULL,
NULL
)
BULK COLLECT
INTO v_route_stops
FROM tms.tms_route_stop s, tms_location l, tms_route_header h
WHERE h.truck_route_instance_id = s.truck_route_instance_id
AND h.bill_of_lading_id = p_bol
AND l.location_id = s.location_id
AND s.delete_flag = 'N'
ORDER BY stop_seq ASC;
v_route_data.route_stops := v_route_stops;
--Fill STOP_DETAILS
SELECT stop_detail_v2 (
stop_detail_id,
SUBSTR (task_type, 0, 1),
shipping_unit_id,
description,
package_type,
DECODE (status, 'Y', 'C', 'N', 'O', NULL, 'O', status),
scan_flag,
exception_code,
scan_dt,
weight,
weight_uom,
stop_id,
'Y',
exception_comment,
container_id,
item_id,
hazmat_flag,
NULL,
NULL
)
BULK COLLECT
INTO v_stop_details
FROM tms.tms_stop_detail d, tms_route_header h
WHERE h.bill_of_lading_id = p_bol
AND d.truck_route_instance_id = h.truck_route_instance_id
AND d.delete_flag = 'N';
v_route_data.stop_details := v_stop_details;
--Update the route download with latest download information.
-- UPDATE tms_route_header
-- SET download_date = v_download_date, device_id = p_device_id
-- WHERE bill_of_lading_id = p_bol;
--Fill into download history table.
INSERT INTO tms_route_download (truck_route_instance_id,
device_id,
download_date,
bill_of_lading_id,
driver_name)
VALUES (v_route_header.instance_id,
TRIM (p_device_id),
v_download_date,
p_bol,
p_driver_name);
o_req_result :=
NEW reqresult (1,
0,
'Route download successfully.',
SYSDATE);
COMMIT;
RETURN v_route_data;
EXCEPTION
/*
ex_finished_bol exception;
ex_canceled_bol exception;
ex_downloaded_bol exception;
ex_nodata_bol exception;
ex_device_bol exception;
ex_version_bol exception;
*/
WHEN ex_nodata_bol
THEN
o_req_result :=
NEW reqresult (0,
-20000,
'Route download without data.',
SYSDATE);
RETURN v_route_data;
WHEN ex_finished_bol
THEN
o_req_result :=
NEW reqresult (0,
-20001,
'Route already finished.',
SYSDATE);
RETURN v_route_data;
WHEN ex_exempt_bol
THEN
o_req_result :=
NEW reqresult (0,
-20002,
'Route be exempted.',
SYSDATE);
RETURN v_route_data;
WHEN ex_cancelled_bol
THEN
o_req_result :=
NEW reqresult (0,
-20003,
'BOL just be cancelled.',
SYSDATE);
RETURN v_route_data;
WHEN ex_downloaded_bol
THEN
o_req_result :=
NEW reqresult (0,
-20004,
TO_CHAR (v_date, 'YYYY-MM-DD HH24:MI:SS'),
SYSDATE);
RETURN v_route_data;
WHEN ex_device_bol
THEN
o_req_result :=
NEW reqresult (0,
-20005,
'Device id is not in valid format.',
SYSDATE);
RETURN v_route_data;
WHEN ex_version_bol
THEN
o_req_result :=
NEW reqresult (0,
-20006,
'VERSION is outdated for this hub',
SYSDATE);
RETURN v_route_data;
/*
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
*/
END;
FUNCTION update_eod (p_route_data IN route_data_v2, p_device_id VARCHAR2)
RETURN reqresult
AS
---------------------------------------------*Declare some variable*--------------------------------------------------
-------
v_cnt INTEGER;
v_indicator INTEGER := 0;
v_message VARCHAR2 (100);
o_req_result reqresult
:= NEW reqresult (1,
0,
'Update successfully.',
SYSDATE) ;
--miss_route_status exception;
--------------------------------------------------*End declare*----------------------------------------------------------
-----
BEGIN
EXECUTE IMMEDIATE 'truncate table tms_wstemp_route_header_v2';
EXECUTE IMMEDIATE 'truncate table tms_wstemp_route_stop_v2';
EXECUTE IMMEDIATE 'truncate table tms_wstemp_stop_detail_v2';
--Insert upload data to temp table
INSERT INTO tms_wstemp_route_header_v2 (bol,
instance_id,
trailer_id1,
route_no,
dow,
scheduled_departure_date,
completed_by)
VALUES (p_route_data.route_header.bol,
p_route_data.route_header.instance_id,
p_route_data.route_header.trailer_id1,
p_route_data.route_header.truck_route_id,
p_route_data.route_header.dow,
p_route_data.route_header.scheduled_departure_date,
p_route_data.route_header.completed_by);
--Check BOL EXISTS
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_header rh, tms_wstemp_route_header_v2 rhtmep
WHERE rh.truck_route_instance_id = rhtmep.instance_id;
IF v_cnt = 0
THEN
o_req_result :=
NEW reqresult (0,
-20019,
'BOL NOT EXIST',
SYSDATE);
RETURN o_req_result;
END IF;
--Check IF already uploaded
SELECT COUNT (1)
INTO v_cnt
FROM tms_route_header rh, tms_wstemp_route_header_v2 rhtmep
WHERE rh.truck_route_instance_id = rhtmep.instance_id
AND end_of_day IS NOT NULL;
IF v_cnt > 0
THEN
o_req_result :=
NEW reqresult (0,
-20020,
'Already Upload',
SYSDATE);
RETURN o_req_result;
END IF;
INSERT INTO tms_wstemp_route_stop_v2
SELECT * FROM table (p_route_data.route_stops);
INSERT INTO tms_wstemp_stop_detail_v2
SELECT * FROM table (p_route_data.stop_details);
--Send email
--check if all stops have been handled
SELECT COUNT (1)
INTO v_cnt
FROM tms_wstemp_route_stop_v2
WHERE status = 'O';
IF v_cnt > 0
THEN
v_indicator := v_indicator + 1;
v_message := v_cnt || ' stops have not been handled.' || CHR (13);
END IF;
--check if all stop detail have been handled
SELECT COUNT (1)
INTO v_cnt
FROM tms_wstemp_stop_detail_v2
WHERE line_status = 'O';
IF v_cnt > 0
THEN
v_indicator := v_indicator + 1;
v_message := v_message || v_cnt || ' items have not been handled.';
END IF;
IF v_indicator > 0
THEN
SYSTEM.dba_maint.mailusers (
'LOG',
'You have some items which have not been handled in your uploaded data.',
'You have some items which have not been handled in your uploaded data.',
v_message
);
END IF;
--merge with stop
--SELECT * FROM tms_route_stop
-- SELECT * FROM tms_route_stop where stop_id in(SELECT stop_id from tms_wstemp_route_stop_v2)
MERGE INTO tms_route_stop rs
USING tms_wstemp_route_stop_v2 vrs
ON (rs.stop_id = vrs.stop_id)
WHEN MATCHED
THEN
UPDATE SET
actual_arrival_dt = vrs.actual_arrival,
exception_code = vrs.exception_code,
exception_comments = vrs.exception_comments,
status =
DECODE (NVL (vrs.status, 'O'),
'O', 'OPEN',
'C', 'COMPLETE',
'E', 'EXCEPT',
vrs.status),
last_update = SYSDATE,
first_scan_time = vrs.first_scan_time,
is_blank_stop = vrs.is_blank_stop;
--merge with stop_detail
--SELECT * FROM tms_stop_detail where status is not null
--SELECT * FROM tms_stop_detail where TRUCK_ROUTE_INSTANCE_ID='85838' in(SELECT stop_detail_id FROM tms_wstemp_stop_detail_v2)
MERGE INTO tms_stop_detail sd
USING (SELECT routestoptemp.instance_id, v_stop_detail.*
FROM tms_wstemp_stop_detail_v2 v_stop_detail,
tms_wstemp_route_stop_v2 routestoptemp
WHERE v_stop_detail.stop_id = routestoptemp.stop_id)
stopdetailtemp
ON (task_type =
DECODE (stopdetailtemp.pick_up_drop_off_ind,
'D', 'DROP',
'P', 'PICK',
'E', 'EMPT',
'L', 'LOAD',
'ERR')
--For none drop, stop_id should be the same
AND sd.stop_id = stopdetailtemp.stop_id
AND shipping_unit_id = stopdetailtemp.scan_id --AND task_type != 'DROP'
)
WHEN MATCHED
THEN
UPDATE SET --shipping_unit_id = v_stop_detail.scan_id,
-- stop_detail_id=v_stop_detail.stop_detail_id,
intf_status =
DECODE (stopdetailtemp.scan_timestamp,
NULL, intf_status,
scan_dt, intf_status,
'READY'),
status = stopdetailtemp.line_status,
scan_flag = stopdetailtemp.scan_ind,
scan_dt = stopdetailtemp.scan_timestamp,
description = stopdetailtemp.description,
weight = stopdetailtemp.weight,
weight_uom = stopdetailtemp.weight_uom,
exception_code = stopdetailtemp.exception_code,
exception_comment = stopdetailtemp.exception_comment,
--v_stop_detail.exception_comment,
package_type = stopdetailtemp.package_type,
last_update = SYSDATE,
container_id = stopdetailtemp.container_id,
--always set delete_flag to 'Y' for drop item, see DST-1144
delete_flag = 'N'
WHEN NOT MATCHED
THEN
INSERT (stop_detail_id,
truck_route_instance_id,
stop_id,
destination_id,
task_type,
shipping_unit_id,
description,
package_type,
weight,
weight_uom,
host_created_dt,
status,
exception_code,
scan_flag,
scan_dt,
last_update,
exception_comment,
container_id,
intf_status)
VALUES (
tms.tms_bgl_route_stop_detail_seq.NEXTVAL,
stopdetailtemp.instance_id,
stopdetailtemp.stop_id,
NVL (
(SELECT sd.stop_id
FROM tms_stop_detail sd
WHERE sd.truck_route_instance_id =
stopdetailtemp.instance_id
AND sd.stop_id <>
stopdetailtemp.stop_id
AND sd.shipping_unit_id =
stopdetailtemp.scan_id
AND sd.task_type = 'DROP'
AND sd.is_client_created = 'N'
AND stopdetailtemp.pick_up_drop_off_ind =
'D'
AND stopdetailtemp.line_status = 'C'
AND ROWNUM = 1),
stopdetailtemp.stop_id
),
DECODE (stopdetailtemp.pick_up_drop_off_ind,
'D', 'DROP',
'P', 'PICK',
'E', 'EMPT',
'L', 'LOAD',
'ERR'),
stopdetailtemp.scan_id,
stopdetailtemp.description,
stopdetailtemp.package_type,
NVL (stopdetailtemp.weight, 0),
stopdetailtemp.weight_uom,
SYSDATE,
stopdetailtemp.line_status,
stopdetailtemp.exception_code,
stopdetailtemp.scan_ind,
stopdetailtemp.scan_timestamp,
SYSDATE,
stopdetailtemp.exception_comment,
stopdetailtemp.container_id,
'READY'
);
--Update route header
UPDATE tms_route_header h
SET end_of_day = SYSDATE,
completed_by =
(SELECT routeheadertemp.completed_by
FROM tms_wstemp_route_header_v2 routeheadertemp
WHERE h.truck_route_instance_id =
routeheadertemp.instance_id),
completion_program_name = 'BGL',
device_id = p_device_id,
download_date =
NVL (
(SELECT MAX (download_date)
FROM tms_route_download
WHERE truck_route_instance_id =
h.truck_route_instance_id
AND device_id = p_device_id),
download_date
--The purpose to add this is to clarify the correct datetime for download.
)
WHERE EXISTS
(SELECT (1)
FROM tms_wstemp_route_header_v2 routeheadertemp
WHERE h.truck_route_instance_id =
routeheadertemp.instance_id);
--insert into route completion
INSERT INTO tms.tms_route_completion (truck_route_instance_id,
end_of_day,
completed_by,
completion_program_name,
task_type,
operation_user,
last_update)
SELECT routeheadertemp.instance_id,
SYSDATE,
routeheadertemp.completed_by,
'BGL',
'COMPLETE',
routeheadertemp.completed_by,
SYSDATE
FROM tms_wstemp_route_header_v2 routeheadertemp;
COMMIT;
o_req_result :=
NEW reqresult (1,
0,
'Update successfully.',
SYSDATE);
RETURN o_req_result;
/*
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
*/
END;
END tms_ws_v2;
/