[Home] [Help]
PACKAGE: APPS.WSH_FTE_INTEGRATION
Source
1 PACKAGE WSH_FTE_INTEGRATION as
2 /* $Header: WSHFTEIS.pls 120.4.12000000.1 2007/01/16 05:46:06 appldev ship $ */
3
4
5 -- constants for the Source attribute in rank lists
6 C_RANKLIST_SOURCE_TP CONSTANT VARCHAR2(2) := 'TP';
7
8 -- constants for the entity type in GET_ORG_ORGANIZATION_INFO
9 C_ORG_INFO_TRIP CONSTANT VARCHAR2(4) := 'TRIP';
10 C_ORG_INFO_STOP CONSTANT VARCHAR2(4) := 'STOP';
11 C_ORG_INFO_DELIVERY CONSTANT VARCHAR2(8) := 'DELIVERY';
12 C_ORG_INFO_DETAIL CONSTANT VARCHAR2(6) := 'DETAIL';
13
14
15
16 -- ------------------------------------------------- --
17 -- --
18 -- Tables and records for Carrier Selection --
19 -- ---------------------------- --
20 -- --
21 -- ------------------------------------------------- --
22 --SBAKSHI(S)
23 --
24 TYPE wsh_cs_output_message_rec IS RECORD (sequence_number NUMBER,
25 message_type VARCHAR2(1),
26 message_code VARCHAR2(30),
27 message_text VARCHAR2(2000),
28 level NUMBER,
29 query_id NUMBER,
30 group_id NUMBER,
31 rule_id NUMBER,
32 result_id NUMBER);
33
34 TYPE wsh_cs_output_message_tab IS TABLE OF wsh_cs_output_message_rec INDEX BY BINARY_INTEGER;
35
36 TYPE WSH_CS_ENTITY_REC_TYPE IS RECORD(
37 delivery_id NUMBER,
38 delivery_name VARCHAR2(30),
39 trip_id NUMBER,
40 trip_name VARCHAR2(30),
41 organization_id NUMBER,
42 triporigin_internalorg_id NUMBER,
43 gross_weight NUMBER,
44 weight_uom_code VARCHAR2(3),
45 volume NUMBER,
46 volume_uom_code VARCHAR2(3),
47 initial_pickup_loc_id NUMBER,
48 ultimate_dropoff_loc_id NUMBER,
49 customer_id NUMBER,
50 customer_site_id NUMBER,
51 freight_terms_code VARCHAR2(30),
52 initial_pickup_date DATE,
53 ultimate_dropoff_date DATE,
54 fob_code VARCHAR2(30),
55 start_search_level VARCHAR2(10),
56 transit_time NUMBER,
57 rule_id NUMBER,
58 result_found_flag VARCHAR2(1));
59
60 TYPE WSH_CS_ENTITY_TAB_TYPE IS TABLE OF WSH_CS_ENTITY_REC_TYPE INDEX BY BINARY_INTEGER;
61
62 TYPE WSH_CS_RESULT_REC_TYPE IS RECORD(
63 rule_id NUMBER,
64 rule_name VARCHAR2(30),
65 delivery_id NUMBER,
66 organization_id NUMBER,
67 initial_pickup_location_id NUMBER,
68 ultimate_dropoff_location_id NUMBER,
69 trip_id NUMBER,
70 result_type VARCHAR2(30), -- Rank / Multileg / Ranked multileg / Ranked itinerary
71 rank NUMBER,
72 leg_destination NUMBER,
73 leg_sequence NUMBER,
74 -- itinerary_id NUMBER, -- Future use for ranked itenerary
75 carrier_id NUMBER,
76 mode_of_transport VARCHAR2(30),
77 service_level VARCHAR2(30),
78 ship_method_code VARCHAR2(30),
79 freight_terms_code VARCHAR2(30),
80 consignee_carrier_ac_no VARCHAR2(240), --WSH_TRIPS
81 -- track_only_flag VARCHAR2(1),
82 result_level VARCHAR(5),
83 pickup_date DATE,
84 dropoff_date DATE,
85 min_transit_time NUMBER,
86 max_transit_time NUMBER,
87 append_flag VARCHAR2(1)
88 --,routing_rule_id NUMBER
89 );
90
91 TYPE WSH_CS_RESULT_TAB_TYPE IS TABLE OF WSH_CS_RESULT_REC_TYPE INDEX BY BINARY_INTEGER;
92
93 TYPE CARRIER_RANK_LIST_REC_TYPE IS RECORD (
94 rank_id NUMBER,
95 trip_id NUMBER,
96 rank_sequence NUMBER,
97 carrier_id NUMBER,
98 mode_of_transport VARCHAR2(30),
99 service_level VARCHAR2(30),
100 freight_terms_code VARCHAR2(30),
101 consignee_carrier_ac_no VARCHAR2(240), --WSH_TRIPS
102 lane_id NUMBER,
103 source VARCHAR2(30),
104 is_current VARCHAR2(1),
105 call_rg_flag VARCHAR2(1)
106 );
107
108 TYPE CARRIER_RANK_LIST_TBL_TYPE IS TABLE OF CARRIER_RANK_LIST_REC_TYPE INDEX BY BINARY_INTEGER;
109
110 --
111 --SBAKSHI(E)
112 --
113
114 --
115 -- Types for Rating
116 --
117
118 TYPE rating_action_param_rec IS RECORD (
119 caller VARCHAR2(30),
120 event VARCHAR2(30),
121 action VARCHAR2(30),
122 trip_id_list WSH_UTIL_CORE.id_tab_type); -- list of trip_ids
123
124 -- types for Rate_Delivery
125 TYPE rate_del_in_param_rec IS RECORD(
126 delivery_id_list WSH_UTIL_CORE.id_tab_type,
127 seq_tender_flag VARCHAR2(1),
128 action VARCHAR2(30));
129
130 TYPE rate_del_out_param_rec IS RECORD(
131 failed_delivery_id_list WSH_UTIL_CORE.id_tab_type);
132
133 -- J+ Types for Auto Tender Project
134 TYPE WSH_TRIP_ACTION_PARAM_REC is RECORD
135 (phase NUMBER,
136 action_code VARCHAR2(500),
137 organization_id NUMBER,
138 report_set_id NUMBER,
139 override_flag VARCHAR2(500),
140 trip_name VARCHAR2(30),
141 actual_date DATE,
142 stop_id NUMBER,
143 action_flag VARCHAR2(1),
144 autointransit_flag VARCHAR2(1),
145 autoclose_flag VARCHAR2(1),
146 stage_del_flag VARCHAR2(1),
147 ship_method VARCHAR2(30),
148 bill_of_lading_flag VARCHAR2(1),
149 defer_interface_flag VARCHAR2(1),
150 actual_departure_date DATE);
151
152 TYPE WSH_TRIP_ACTION_OUT_REC IS RECORD
153 ( result_id_tab WSH_UTIL_CORE.id_tab_type,
154 valid_ids_tab WSH_UTIL_CORE.id_tab_type,
155 delivery_id_tab WSH_UTIL_CORE.id_tab_type,
156 failed_ids_tab WSH_UTIL_CORE.id_tab_type, -- different from FTE datastructure
157 selection_issue_flag VARCHAR2(1),
158 packing_slip_number VARCHAR2(50),
159 num_success_delivs NUMBER,
160 split_quantity NUMBER,
161 split_quantity2 NUMBER);
162
163 --
164 --
165 -- Procedure: Shipment_Price_Consolidate
166 -- Parameters: p_delivery_leg_id
167 -- p_trip_id ( segment id for FTE, trip id for WSH)
168 -- x_return_status
169 -- Description: This procedure is a wapper for
170 -- FTE_FREIGHT_PRICING.shipment_price_consolidate
171 -- to calculate the cost from FTE
172 -- and have the result records populated in
173 -- wsh_freight_costs table
174 --
175 PROCEDURE shipment_price_consolidate (
176 p_delivery_leg_id IN NUMBER DEFAULT NULL,
177 p_trip_id IN NUMBER DEFAULT NULL,
178 x_return_status OUT NOCOPY VARCHAR2 ) ;
179
180 -- WSH get_rate_from_FTE demo flow
181 PROCEDURE Rate_Delivery (
182 p_api_version IN NUMBER DEFAULT 1.0,
183 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
184 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
185 p_in_param_rec IN rate_del_in_param_rec,
186 x_out_param_rec OUT NOCOPY rate_del_out_param_rec,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2);
190
191 -- WSH get_rate_from_FTE demo flow (multiple deliveries)
192 PROCEDURE Cancel_Service (
193 p_api_version IN NUMBER DEFAULT 1.0,
194 p_init_msg_list VARCHAR2 DEFAULT FND_API.G_FALSE,
195 p_delivery_list IN WSH_UTIL_CORE.id_tab_type,
196 p_action IN VARCHAR2 DEFAULT 'CANCEL',
197 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2);
201
202 -- WSH get_rate_from_FTE demo flow (single delivery)
203 PROCEDURE Cancel_Service (
204 p_api_version IN NUMBER DEFAULT 1.0,
205 p_init_msg_list VARCHAR2 DEFAULT FND_API.G_FALSE,
206 p_delivery_id IN NUMBER,
207 p_action IN VARCHAR2 DEFAULT 'CANCEL',
208 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2);
212
213 PROCEDURE trip_stop_validations
214 ( p_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
215 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type,
216 p_action IN VARCHAR2,
217 x_return_status OUT NOCOPY VARCHAR2) ;
218
219
220 --
221 --SBAKSHI(S)
222 --
223 /*
224 -- Commenting for R12
225 PROCEDURE CARRIER_SELECTION(p_cs_input_header_rec IN OUT NOCOPY wsh_cs_input_header_rec,
226 p_cs_input_attribute_tab IN OUT NOCOPY wsh_cs_input_attribute_tab,
227 p_object_name IN VARCHAR2,
228 p_object_id IN NUMBER,
229 p_messaging_yn IN VARCHAR2,
230 x_cs_output_result_tab OUT NOCOPY wsh_cs_output_result_tab,
231 x_cs_output_message_tab OUT NOCOPY wsh_cs_output_message_tab,
232 x_return_message OUT NOCOPY VARCHAR2,
233 x_return_status OUT NOCOPY VARCHAR2);
234 */
235
236 PROCEDURE CARRIER_SELECTION( p_format_cs_tab IN OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_entity_tab_type,
237 p_messaging_yn IN VARCHAR2,
238 p_caller IN VARCHAR2,
239 p_entity IN VARCHAR2,
240 x_cs_output_tab OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_result_tab_type,
241 x_cs_output_message_tab OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_output_message_tab,
242 x_return_message OUT NOCOPY VARCHAR2,
243 x_return_status OUT NOCOPY VARCHAR2);
244
245 --
246 --SBAKSHI(E)
247 --
248 FUNCTION get_cc_object_name(
249 --p_comp_class_id IN NUMBER,
250 p_object_type IN VARCHAR2,
251 p_object_value_num IN NUMBER DEFAULT NULL,
252 p_object_parent_id IN NUMBER DEFAULT NULL,
253 p_object_value_char IN VARCHAR2 DEFAULT NULL,
254 x_fac_company_name OUT NOCOPY VARCHAR2,
255 x_fac_company_type OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2;
256
257
258 -- Procedure : Get_Vehicle_Type
259 -- Purpose : Gets vehicle type ID from FTE_VEHICLE_TYPES
260
261 PROCEDURE Get_Vehicle_Type(
262 p_vehicle_item_id IN NUMBER,
263 p_vehicle_org_id IN NUMBER,
264 x_vehicle_type_id OUT NOCOPY NUMBER,
265 x_return_status OUT NOCOPY VARCHAR2);
266
267 --PROCEDURE GET_VEHICLE_ORG_ID : Gets the vehicle org in fte_vehicle_types
268 PROCEDURE GET_VEHICLE_ORG_ID
269 (p_inventory_item_id IN NUMBER,
270 x_vehicle_org_id OUT NOCOPY NUMBER,
271 x_return_status OUT NOCOPY VARCHAR2);
272
273 -- +======================================================================+
274 -- Procedure :
275 -- Rate_Trip (pack J+)
276 --
277 -- Description:
278 -- Rate Trip from various event points
279 -- Inputs:
280 -- p_api_version => api version
281 -- p_init_msg_list => init message list
282 -- p_action_params => parameters identifying the
283 -- action to be performed
284 -- -> caller -> 'FTE','WSH'
285 -- -> event -> 'TP-RELEASE','SHIP-CONFIRM'
286 -- -> action -> 'RATE'
287 -- -> trip_id_list -> valid trip_id list
288 -- p_commit => FND_API.G_FALSE / G_TRUE
289 -- Output:
290 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
291 -- x_msg_count OUT NOCOPY NUMBER,
292 -- x_msg_data OUT NOCOPY VARCHAR2);
293 --
294 -- +======================================================================+
295
296 PROCEDURE Rate_Trip (
297 p_api_version IN NUMBER DEFAULT 1.0,
298 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
299 p_action_params IN rating_action_param_rec,
300 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
301 x_return_status OUT NOCOPY VARCHAR2,
302 x_msg_count OUT NOCOPY NUMBER,
303 x_msg_data OUT NOCOPY VARCHAR2);
304
305 -- +======================================================================+
306 -- Procedure :
307 -- Trip_Action (pack J+)
308 --
309 -- Description:
310 -- Execute Trip Actions for Input of trips
311 -- Inputs:
312 -- p_api_version => api version
313 -- p_init_msg_list => init message list
314 -- p_action_params => parameters identifying the
315 -- action to be performed
316 -- -> action_code -> 'TENDER'
317 -- p_commit => FND_API.G_FALSE / G_TRUE
318 -- Output:
319 -- x_action_out_rec => parameters identifying the Success
320 -- and Error Trip ids
321 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
322 -- x_msg_count OUT NOCOPY NUMBER,
323 -- x_msg_data OUT NOCOPY VARCHAR2);
324 --
325 -- +======================================================================+
326 PROCEDURE Trip_Action (
327 p_api_version IN NUMBER DEFAULT 1.0,
328 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
329 p_trip_id_tab IN WSH_UTIL_CORE.id_tab_type,
330 p_action_params IN wsh_trip_action_param_rec,
331 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
332 x_action_out_rec OUT NOCOPY wsh_trip_action_out_rec,
333 x_return_status OUT NOCOPY VARCHAR2,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2);
336
337 PROCEDURE RANK_LIST_ACTION(
338 p_api_version IN NUMBER DEFAULT 1.0,
339 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 p_action_code IN VARCHAR2,
344 p_ranklist IN OUT NOCOPY CARRIER_RANK_LIST_TBL_TYPE,
345 p_trip_id IN NUMBER,
346 p_rank_id IN NUMBER
347 --x_ranklist OUT NOCOPY CARRIER_RANK_LIST_TBL_TYPE
348 );
349
350 FUNCTION GET_TRIP_MOVE(
351 p_trip_id IN NUMBER) RETURN NUMBER;
352
353
354 -- ----------------------------------------------------------------------
355 -- Procedure: CARRIER_SEL_CREATE_TRIP
356 --
357 -- Parameters: p_delivery_id Delivery ID
358 -- p_carrier_sel_result_rec WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE
359 -- x_trip_id Trip Id
360 -- x_trip_name Trip Name
361 -- x_return_message Return Message
362 -- x_return_status Return Status
363 --
364 -- COMMENT : This procedure is called from Process Carrier Selection API
365 -- in order to create trip for deliveries not assigned to trips
366 --
367 -- This procedure is a wrapper for FTE_ACS_TRIP_PKG.CARRIER_SEL_CREATE_TRIP
368 --
369 -- ----------------------------------------------------------------------
370 PROCEDURE CARRIER_SEL_CREATE_TRIP( p_delivery_id IN NUMBER,
371 --p_initial_pickup_loc_id IN NUMBER,
372 --p_ultimate_dropoff_loc_id IN NUMBER,
373 --p_initial_pickup_date IN DATE,
374 --p_ultimate_dropoff_date IN DATE,
375 p_carrier_sel_result_rec IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE,
376 x_trip_id OUT NOCOPY NUMBER,
377 x_trip_name OUT NOCOPY VARCHAR2,
378 x_return_message OUT NOCOPY VARCHAR2,
379 x_return_status OUT NOCOPY VARCHAR2);
380
381
382
383
384 -- ----------------------------------------------------------------------
385 -- Procedure: GET_ORG_ORGANIZATION_INFO
386 --
387 -- Parameters:
388 -- p_init_msg_list Flag to initialize message stack
389 -- x_return_message Return Message
390 -- x_msg_count count of messages
391 -- p_msg_data message text
395 -- p_entity_type 'TRIP' or 'DELIVERY'
392 -- x_organization_id inventory organization identifier
393 -- x_org_id operating unit identifier
394 -- p_entity_id entity identifier
396 -- p_org_id_flag flag to optionally get x_org_id
397 -- FND_API.G_TRUE -> yes
398 -- FND_API.G_FALSE -> no
399 --
400 --
401 -- COMMENT : This procedure calls FTE to associate a trip with
402 -- inventory organization and optionally the operating unit.
403 --
404 -- This procedure is a wrapper for
405 -- FTE_WSH_INTEGRATION_PKG.GET_ORG_ORGANIZATION_INFO
406 --
407 -- FTE will always be called regardless of
408 -- WSH_UTIL_CORE.FTE_Is_Installed value.
409 --
410 -- ----------------------------------------------------------------------
411 PROCEDURE GET_ORG_ORGANIZATION_INFO(
412 p_init_msg_list IN VARCHAR2,
413 x_return_status OUT NOCOPY VARCHAR2,
414 x_msg_count OUT NOCOPY NUMBER,
415 x_msg_data OUT NOCOPY VARCHAR2,
416 x_organization_id OUT NOCOPY NUMBER,
417 x_org_id OUT NOCOPY NUMBER,
418 p_entity_id IN NUMBER,
419 p_entity_type IN VARCHAR2,
420 p_org_id_flag IN VARCHAR2);
421
422
423
424 -- ----------------------------------------------------------------------
425 -- Procedure: CREATE_RANK_LIST_BULK
426 --
427 -- Parameters:
428 -- p_api_version_number API version number (1)
429 -- p_init_msg_list Flag to initialize message stack
430 -- x_return_message Return Message
431 -- x_msg_count count of messages
432 -- p_msg_data message text
433 -- p_source source of call; valid values:
434 -- C_RANKLIST_SOURCE_%
435 -- p_trip_id_tab table of trip identifiers
436 --
437 --
438 -- COMMENT : This procedure calls FTE to perform a bulk operation
439 -- on ranking carriers in trips.
440 --
441 -- This procedure is a wrapper for
442 -- FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST_BULK
443 --
444 -- It will pull the required values from WSH_TRIPS to
445 -- build the rank list for the FTE API.
446 --
447 -- ----------------------------------------------------------------------
448 PROCEDURE CREATE_RANK_LIST_BULK(
449 p_api_version_number IN NUMBER,
450 p_init_msg_list IN VARCHAR2,
451 x_return_status OUT NOCOPY VARCHAR2,
452 x_msg_count OUT NOCOPY NUMBER,
453 x_msg_data OUT NOCOPY VARCHAR2,
454 p_source IN VARCHAR2,
455 p_trip_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE);
456
457 END WSH_FTE_INTEGRATION;