[Home] [Help]
PACKAGE: APPS.WSH_TMS_RELEASE
Source
1 PACKAGE WSH_TMS_RELEASE as
2 /* $Header: WSHTMRLS.pls 120.1.12010000.3 2009/01/22 07:26:38 anvarshn ship $ */
3
4 G_TMS_RELEASE_CODE CONSTANT VARCHAR2(30) := 'TMS_RELEASE';
5 G_TMS_DELETE_CODE CONSTANT VARCHAR2(30) := 'TMS_DELETE';
6
7 TYPE interface_errors_tab_type IS TABLE OF WSH_INTERFACE_ERRORS%ROWTYPE INDEX BY
8 BINARY_INTEGER;
9
10 TYPE tms_dleg_rec_type IS RECORD (
11 delivery_leg_interface_id NUMBER,
12 delivery_interface_id NUMBER,
13 pickup_stop_location_id VARCHAR2(30), --NUMBER,
14 pickup_stop_sequence NUMBER,
15 dropoff_stop_location_id VARCHAR2(30), --NUMBER,
16 dropoff_stop_sequence NUMBER,
17 trip_interface_id NUMBER,
18 delivery_leg_id NUMBER,
19 delivery_id NUMBER,
20 pick_up_stop_interface_id NUMBER,
21 drop_off_stop_interface_id NUMBER,
22 weight_uom VARCHAR2(3),
23 volume_uom VARCHAR2(3),
24 organization_id NUMBER,
25 tms_version_number NUMBER, -- EBS tms_version_number
26 otm_tms_version_number NUMBER, -- OTM sent tms_version_number
27 initial_pickup_location_id NUMBER,
28 ultimate_dropoff_location_id NUMBER,
29 processed_flag VARCHAR2(1),
30 tms_interface_flag VARCHAR2(3) -- EBS tms_interface_flag.
31 );
32 TYPE tms_dleg_tab_type IS TABLE OF tms_dleg_rec_type INDEX BY BINARY_INTEGER;
33
34 TYPE tms_stop_rec_type IS RECORD (
35 stop_interface_id NUMBER,
36 stop_id NUMBER,
37 tp_stop_id NUMBER,
38 stop_location_id VARCHAR2(30),
39 stop_sequence_number NUMBER,
40 planned_arrival_date DATE,
41 planned_departure_date DATE,
42 departure_gross_weight NUMBER,
43 departure_net_weight NUMBER,
44 weight_uom_code WSH_TRIP_STOPS.WEIGHT_UOM_CODE%TYPE,
45 departure_volume NUMBER,
46 volume_uom_code WSH_TRIP_STOPS.VOLUME_UOM_CODE%TYPE,
47 departure_seal_code WSH_TRIP_STOPS.DEPARTURE_SEAL_CODE%TYPE,
48 departure_fill_percent NUMBER,
49 wkend_layover_stops WSH_TRIP_STOPS.wkend_layover_stops%TYPE,
50 wkday_layover_stops WSH_TRIP_STOPS.wkday_layover_stops%TYPE,
51 shipments_type_flag WSH_TRIP_STOPS.shipments_type_flag%TYPE,
52 trip_interface_id NUMBER,
53 timezone_code VARCHAR2(50), -- ECO 5101760
54 dml_action VARCHAR2(1),
55 tp_attribute_category WSH_TRIP_STOPS.TP_ATTRIBUTE_CATEGORY%TYPE,
56 tp_attribute1 WSH_TRIP_STOPS.TP_ATTRIBUTE1%TYPE,
57 tp_attribute2 WSH_TRIP_STOPS.TP_ATTRIBUTE2%TYPE,
58 tp_attribute3 WSH_TRIP_STOPS.TP_ATTRIBUTE3%TYPE,
59 tp_attribute4 WSH_TRIP_STOPS.TP_ATTRIBUTE4%TYPE,
60 tp_attribute5 WSH_TRIP_STOPS.TP_ATTRIBUTE5%TYPE,
61 tp_attribute6 WSH_TRIP_STOPS.TP_ATTRIBUTE6%TYPE,
62 tp_attribute7 WSH_TRIP_STOPS.TP_ATTRIBUTE7%TYPE,
63 tp_attribute8 WSH_TRIP_STOPS.TP_ATTRIBUTE8%TYPE,
64 tp_attribute9 WSH_TRIP_STOPS.TP_ATTRIBUTE9%TYPE,
65 tp_attribute10 WSH_TRIP_STOPS.TP_ATTRIBUTE10%TYPE,
66 tp_attribute11 WSH_TRIP_STOPS.TP_ATTRIBUTE11%TYPE,
67 tp_attribute12 WSH_TRIP_STOPS.TP_ATTRIBUTE12%TYPE,
68 tp_attribute13 WSH_TRIP_STOPS.TP_ATTRIBUTE13%TYPE,
69 tp_attribute14 WSH_TRIP_STOPS.TP_ATTRIBUTE14%TYPE,
70 tp_attribute15 WSH_TRIP_STOPS.TP_ATTRIBUTE15%TYPE,
71 attribute_category WSH_TRIP_STOPS.ATTRIBUTE_CATEGORY%TYPE,
72 attribute1 WSH_TRIP_STOPS.ATTRIBUTE1%TYPE,
73 attribute2 WSH_TRIP_STOPS.ATTRIBUTE2%TYPE,
74 attribute3 WSH_TRIP_STOPS.ATTRIBUTE3%TYPE,
75 attribute4 WSH_TRIP_STOPS.ATTRIBUTE4%TYPE,
76 attribute5 WSH_TRIP_STOPS.ATTRIBUTE5%TYPE,
77 attribute6 WSH_TRIP_STOPS.ATTRIBUTE6%TYPE,
78 attribute7 WSH_TRIP_STOPS.ATTRIBUTE7%TYPE,
79 attribute8 WSH_TRIP_STOPS.ATTRIBUTE8%TYPE,
80 attribute9 WSH_TRIP_STOPS.ATTRIBUTE9%TYPE,
81 attribute10 WSH_TRIP_STOPS.ATTRIBUTE10%TYPE,
82 attribute11 WSH_TRIP_STOPS.ATTRIBUTE11%TYPE,
83 attribute12 WSH_TRIP_STOPS.ATTRIBUTE12%TYPE,
84 attribute13 WSH_TRIP_STOPS.ATTRIBUTE13%TYPE,
85 attribute14 WSH_TRIP_STOPS.ATTRIBUTE14%TYPE,
86 attribute15 WSH_TRIP_STOPS.ATTRIBUTE15%TYPE
87 );
88 --wv_frozen_flag WSH_TRIP_STOPS.wv_frozen_flag%TYPE
89 --wsh_physical_location_id NUMBER -- if populated, plan stop has been mapped to a TE internal stop
90
91 TYPE tms_stop_tab_type IS TABLE OF tms_stop_rec_type INDEX BY BINARY_INTEGER;
92
93
94 TYPE tms_trip_rec_type IS RECORD (
95 trip_interface_id NUMBER,
96 group_id NUMBER, --Bug7717569
97 trip_id NUMBER,
98 tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE,
99 tp_trip_number WSH_TRIPS.TP_TRIP_NUMBER%TYPE,
100 planned_flag WSH_TRIPS.PLANNED_FLAG%TYPE,
101 wsh_planned_flag WSH_TRIPS.PLANNED_FLAG%TYPE,
102 wsh_status_code WSH_TRIPS.STATUS_CODE%TYPE,
103 name WSH_TRIPS.NAME%TYPE,
104 vehicle_item_id WSH_TRIPS.VEHICLE_ITEM_ID%TYPE,
105 vehicle_organization_id WSH_TRIPS.VEHICLE_ORGANIZATION_ID%TYPE,
106 vehicle_num_prefix WSH_TRIPS.VEHICLE_NUM_PREFIX%TYPE,
107 vehicle_number WSH_TRIPS.VEHICLE_NUMBER%TYPE,
108 carrier_id WSH_TRIPS.CARRIER_ID%TYPE,
109 ship_method_code WSH_TRIPS.SHIP_METHOD_CODE%TYPE,
110 route_id WSH_TRIPS.ROUTE_ID%TYPE,
111 routing_instructions WSH_TRIPS.ROUTING_INSTRUCTIONS%TYPE,
112 service_level WSH_TRIPS.SERVICE_LEVEL%TYPE,
113 mode_of_transport WSH_TRIPS.MODE_OF_TRANSPORT%TYPE,
114 freight_terms_code WSH_TRIPS.FREIGHT_TERMS_CODE%TYPE,
115 seal_code WSH_TRIPS.SEAL_CODE%TYPE,
116 shipments_type_flag WSH_TRIPS.SHIPMENTS_TYPE_FLAG%TYPE,
117 ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE,
118 booking_number WSH_TRIPS.BOOKING_NUMBER%TYPE,
119 vessel WSH_TRIPS.VESSEL%TYPE,
120 voyage_number WSH_TRIPS.VOYAGE_NUMBER%TYPE,
121 port_of_loading WSH_TRIPS.PORT_OF_LOADING%TYPE,
122 port_of_discharge WSH_TRIPS.PORT_OF_DISCHARGE%TYPE,
123 carrier_contact_id WSH_TRIPS.CARRIER_CONTACT_ID%TYPE,
124 shipper_wait_time WSH_TRIPS.SHIPPER_WAIT_TIME%TYPE,
125 wait_time_uom WSH_TRIPS.WAIT_TIME_UOM%TYPE,
126 carrier_response WSH_TRIPS.CARRIER_RESPONSE%TYPE,
127 operator WSH_TRIPS.OPERATOR%TYPE,
128 vehicle_item_name WSH_TRIPS_INTERFACE.VEHICLE_ITEM_NAME%TYPE,
129 interface_action_code
130 WSH_TRIPS_INTERFACE.INTERFACE_ACTION_CODE%TYPE,
131 attribute_category WSH_TRIPS.ATTRIBUTE_CATEGORY%TYPE,
132 attribute1 WSH_TRIPS.ATTRIBUTE1%TYPE,
133 attribute2 WSH_TRIPS.ATTRIBUTE2%TYPE,
134 attribute3 WSH_TRIPS.ATTRIBUTE3%TYPE,
135 attribute4 WSH_TRIPS.ATTRIBUTE4%TYPE,
136 attribute5 WSH_TRIPS.ATTRIBUTE5%TYPE,
137 attribute6 WSH_TRIPS.ATTRIBUTE6%TYPE,
138 attribute7 WSH_TRIPS.ATTRIBUTE7%TYPE,
139 attribute8 WSH_TRIPS.ATTRIBUTE8%TYPE,
140 attribute9 WSH_TRIPS.ATTRIBUTE9%TYPE,
141 attribute10 WSH_TRIPS.ATTRIBUTE10%TYPE,
142 attribute11 WSH_TRIPS.ATTRIBUTE11%TYPE,
143 attribute12 WSH_TRIPS.ATTRIBUTE12%TYPE,
144 attribute13 WSH_TRIPS.ATTRIBUTE13%TYPE,
145 attribute14 WSH_TRIPS.ATTRIBUTE14%TYPE,
146 attribute15 WSH_TRIPS.ATTRIBUTE15%TYPE
147 );
148 TYPE tms_trip_tab_type IS TABLE OF tms_trip_rec_type INDEX BY BINARY_INTEGER;
149
150 TYPE delivery_rec IS RECORD
151 (delivery_interface_id NUMBER,
152 organization_id NUMBER,
153 tms_version_number NUMBER,
154 name VARCHAR2(30),
155 status_code VARCHAR2(2));
156
157 TYPE delivery_tab IS TABLE OF delivery_rec INDEX BY VARCHAR2(38);
158
159 --
160 -- Procedure: release_planned_shipment
161 -- Parameters:
162 -- IN:
163 -- p_group_id list of group_ids to process their
164 -- WSH_TRIPS_INTERFACE records and
165 -- their associated table records.
166 --
167 -- p_latest_version if 'Y'/null then process only if inbound delivery
168 -- tms_version_number matches on the EBS delivery.
169 -- if 'N' then process the all inbound deliveries
170 -- irrespective of version number but EBS delivery
171 -- tms_interface_flag should be 'UP'/'UR'.
172 -- tms_interface_flag will be remain at 'UR'/'UP' when
173 -- procesed the lower version delivery.
174 --
175 -- p_tp_plan_low process all trips from wsh_trip_interface
176 -- table which are having tp_plan_name greater than or
177 -- equal to 'p_tp_plan_low'
178 --
179 -- p_tp_plan_high process all trips from wsh_trip_interface
180 -- table which are having tp_plan_name less than or
181 -- equal to 'p_tp_plan_high'
182 --
183 -- p_dummy Dummy parameter to disable
184 -- p_del_name_low,p_del_name_high
185 -- p_organization_id,p_del_pickup_date_low and
186 -- p_del_pickup_date_high
187 -- on the concurrent program parameters windonw
188 -- when entered p_tp_plan_low/p_tp_plan_high.
189 --
190 -- Organization_id Process all shipments which are associated to the
191 -- OTM enabled organization id.
192 --
193 -- p_del_name_low Process all shipments which are associated to the
194 -- deliveries from wsh_new_del_interface which
195 -- having delivery name greater than or equal to
196 -- p_del_name_low
197 --
198 -- p_del_name_high Process all shipments which are associated to the
199 -- deliveries from wsh_new_del_interface which
200 -- having delivery name less than or equal to
201 -- p_del_name_high
202 --
203 -- p_del_pickup_date_low Process all shipments which are associated to the
204 -- deliveries which having initial pickup date
205 -- greater than or equal to p_del_pickup_date_low
206 --
207 -- p_del_pickup_date_high low Process all shipments which are associated to
208 -- the deliveries which having initial pickup date
209 -- less than or equal to p_del_pickup_date_high
210 -- OUT:
211 -- errbuf Error message.
212 -- retcode Error Code 1:Successs, 2:Warning and 3:Error.
213 --
214 -- Description:
215 -- Reconciliate shipping data with the GC3 plan populated
216 -- in the WSH interface tables.
217 --
218 --
219 -- Bug#7491598(ER,defer planned shipment iface): Added new parameters which are
220 -- used when invoked the planned shipment interface manually.
221 PROCEDURE release_planned_shipment(
222 errbuf OUT NOCOPY VARCHAR2,
223 retcode OUT NOCOPY VARCHAR2,
224 p_group_id IN NUMBER DEFAULT NULL,
225 p_latest_version IN VARCHAR2 DEFAULT NULL,
226 p_tp_plan_low IN VARCHAR2 DEFAULT NULL,
227 p_tp_plan_high IN VARCHAR2 DEFAULT NULL,
228 p_dummy IN VARCHAR2 DEFAULT NULL,
229 p_organization_id IN NUMBER DEFAULT NULL,
230 p_del_name_low IN VARCHAR2 DEFAULT NULL,
231 p_del_name_high IN VARCHAR2 DEFAULT NULL,
232 p_del_pickup_date_low IN VARCHAR2 DEFAULT NULL,
233 p_del_pickup_date_high IN VARCHAR2 DEFAULT NULL
234 );
235
236 --
237 -- Procedure: purge_interface_data
238 -- Parameters:
239 -- p_tp_plan_name : Tp_plan_name
240 -- p_trip_interface_id : Trip Interface Id
241 -- p_commit_flag : Y - commit changes; N - do not commit
242 -- p_exception_action : 'PURGE' or 'CLOSE'
243 -- x_return_status : return status
244 --
245 -- Description:
246 -- Delete the records from WSH interface tables
247 -- and close/purge the appropriate exceptions
248 --
249 PROCEDURE purge_interface_data(
250 p_tp_plan_name IN VARCHAR2 DEFAULT NULL,
251 p_trip_interface_id IN VARCHAR2 DEFAULT NULL,
252 p_commit_flag IN VARCHAR2,
253 p_exception_action IN VARCHAR2 DEFAULT 'PURGE',
254 x_return_status OUT NOCOPY VARCHAR2);
255
256 --
257 -- Procedure: find_deliveries_for_trip
258 -- Parameters:
259 -- p_trip_id : Trip Id
260 -- p_tp_plan_name : Tp_plan_name
261 -- x_return_status : return status
262 -- Two types of outputs are required from this API
263 -- 1) x_delivery_tab : Table of Delivery ids
264 -- 2) x_delivery_id_tab : Table of Delivery id+Revision+Delivery interface_id
265 --
266 -- Description: Find deliveries for input of trip id or tp_plan_name
267 --
268 PROCEDURE find_deliveries_for_trip(
269 p_trip_id IN NUMBER,
270 p_tp_plan_name IN VARCHAR2,
271 x_delivery_tab OUT NOCOPY WSH_TMS_RELEASE.delivery_tab,
272 x_delivery_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
273 x_return_status OUT NOCOPY VARCHAR2);
274
275 --
276 PROCEDURE launch_release_request
277 (p_group_id IN NUMBER,
278 x_request_id OUT NOCOPY NUMBER,
279 x_return_status OUT NOCOPY VARCHAR2);
280
281 END WSH_TMS_RELEASE;