CREATE OR REPLACE TYPE tms.route_header_v2
AS
OBJECT (
bol INTEGER,
instance_id INTEGER,
trailer_id1 VARCHAR2 (20),
truck_route_id INTEGER,
dow VARCHAR2 (3),
scheduled_departure_date DATE,
completed_by VARCHAR2 (8)
);
/
GRANT EXECUTE ON tms.route_header_v2 TO websrv;
CREATE OR REPLACE TYPE tms.route_stop_v2
AS
OBJECT (
stop_id INTEGER,
location_name VARCHAR2 (40),
location_id INTEGER,
address1 VARCHAR2 (40),
address2 VARCHAR2 (40),
city VARCHAR2 (40),
state VARCHAR2 (6),
alarm_code VARCHAR2 (10),
sched_arrival DATE,
sched_departure DATE,
actual_arrival DATE,
actual_departure DATE,
contacts VARCHAR2 (255),
special_instructions VARCHAR2 (255),
status VARCHAR2 (8),
exception_code VARCHAR2 (4),
sync_status CHAR (1),
stop_seq INTEGER,
enroute_dt DATE,
exception_comments VARCHAR2 (255),
stop_type VARCHAR (1),
first_scan_time DATE
);
/
GRANT EXECUTE ON tms.route_stop_v2 TO websrv;
CREATE OR REPLACE TYPE tms.stop_detail_v2
AS
OBJECT (
stop_detail_id INTEGER,
bol_no VARCHAR2 (16),
pick_up_drop_off_ind VARCHAR2 (1),
scan_id VARCHAR2 (255),
description VARCHAR2 (20),
package_type VARCHAR2 (10),
line_status VARCHAR2 (1),
scan_ind VARCHAR2 (1),
exception_code VARCHAR2 (4),
uploaded_dt DATE,
scan_timestamp DATE,
weight INTEGER,
weight_uom VARCHAR2 (3),
stop_id INTEGER,
sync_status CHAR (1),
exception_comment VARCHAR2 (255),
container_id VARCHAR2 (255),
item_id VARCHAR2 (18)
);
/
GRANT EXECUTE ON tms.stop_detail_v2 TO websrv;
CREATE OR REPLACE TYPE tms.po_v2
AS
OBJECT (
stop_id INTEGER,
po_id VARCHAR (10),
delete_flag CHAR (1),
weight INTEGER,
weight_uom VARCHAR (3),
sync_status CHAR (1),
exception_code VARCHAR2 (4),
exception_comment VARCHAR2 (255)
)
/
GRANT EXECUTE ON tms.po_v2 TO websrv;
CREATE OR REPLACE TYPE tms.route_stops_v2 AS TABLE OF route_stop_v2;
/
GRANT EXECUTE ON tms.route_stops_v2 TO websrv;
CREATE OR REPLACE TYPE tms.stop_details_v2 AS TABLE OF stop_detail_v2;
GRANT EXECUTE ON tms.stop_details_v2 TO websrv;
CREATE OR REPLACE TYPE tms.pos_v2 AS TABLE OF po_v2;
/
GRANT EXECUTE ON tms.pos_v2 TO websrv;
CREATE OR REPLACE TYPE tms.route_data_v2
AS
OBJECT (
-- Attributes declaration
route_header tms.route_header_v2,
route_stops tms.route_stops_v2,
stop_pos tms.pos_v2,
stop_details tms.stop_details_v2,
po_stop_details tms.po_stop_details
-- Methods declaration
);
/
GRANT EXECUTE ON tms.route_data_v2 TO websrv;