1 PACKAGE WMS_SHIPPING_TRANSACTION_PUB AS
2 /* $Header: WMSPSHPS.pls 120.3.12010000.1 2008/07/28 18:36:08 appldev ship $ */
3 /*#
4 * These procedures act as an integration between Shipping and WMS
5 * for direct ship, LPN ship, managing trips and delivery, closing trucks.
6 * @rep:scope public
7 * @rep:product WMS
8 * @rep:lifecycle active
9 * @rep:displayname Integraion object between Shipping and WMS
10 * @rep:category BUSINESS_ENTITY WMS_SHIPPING_TRANSACTION
11 */
12
13
14 TYPE t_genref IS REF CURSOR;
15
16 G_PKG_NAME constant VARCHAR2(30) := 'WMS_SHIPPING_TRANSACTION_PUB';
17 G_ALLOW_SHIP_SET_BREAK VARCHAR2(1) := 'N';
18
19 FUNCTION IS_LOADED(p_organization_id IN NUMBER,
20 p_dock_door_id IN NUMBER,
21 p_dock_appoint_flag IN VARCHAR2,
22 p_direct_ship_flag IN VARCHAR2 DEFAULT 'N') RETURN VARCHAR2;
23
24 PROCEDURE GET_DOCK_DOORS(x_dock_door_LOV OUT NOCOPY t_genref,
25 p_txn_dock_app IN VARCHAR2,
26 p_organization_id IN NUMBER,
27 p_dock_door IN VARCHAR2);
28
29 procedure get_deliveries( x_delivery_lov OUT NOCOPY t_genref,
30 p_trip_id IN NUMBER);
31
32 procedure get_delivery_info(x_delivery_info OUT NOCOPY t_genref,
33 p_delivery_id IN NUMBER);
34
35 PROCEDURE GET_STAGING_LANE(x_staging_lane_LOV OUT NOCOPY t_genref,
36 p_txn_dock IN VARCHAR2,
37 p_organization_id IN NUMBER,
38 p_sub_code IN VARCHAR2,
39 p_dock_appointment_id IN NUMBER,
40 p_staging_lane IN VARCHAR2);
41
42 PROCEDURE GET_DELIVERY_DETAIL_ID(x_delivery_detail_id OUT NOCOPY t_genref,
43 p_organization_id IN NUMBER,
44 p_locator_id IN NUMBER,
45 p_trip_id IN NUMBER);
46
47 PROCEDURE POPULATE_WSTT(x_return OUT NOCOPY NUMBER,
48 x_msg_code OUT NOCOPY VARCHAR2,
49 p_organization_id IN NUMBER,
50 p_lpn_id IN NUMBER,
51 p_trip_id IN NUMBER,
52 p_dock_door_id IN NUMBER,
53 p_direct_ship_flag IN VARCHAR2 DEFAULT 'N');
54
55 FUNCTION GET_DELIVERY_NAME(p_delivery_id IN NUMBER)
56 RETURN VARCHAR2;
57
58 PROCEDURE GET_LPN_LOV(x_lpn_lov out NOCOPY t_genref,
59 p_organization_id IN NUMBER,
60 p_locator_id IN NUMBER,
61 p_trip_id IN NUMBER,
62 p_trip_stop_id IN NUMBER,
63 p_lpn IN VARCHAR2);
64
65 procedure nested_serial_check(x_result OUT NOCOPY NUMBER,
66 x_outermost_lpn OUT NOCOPY VARCHAR2,
67 x_outermost_lpn_id OUT NOCOPY NUMBER,
68 x_parent_lpn_id OUT NOCOPY NUMBER,
69 x_parent_lpn OUT NOCOPY VARCHAR2,
70 x_inventory_item_id OUT NOCOPY NUMBER,
71 x_quantity OUT NOCOPY NUMBER,
72 x_requested_quantity OUT NOCOPY NUMBER,
73 x_delivery_detail_id OUT NOCOPY NUMBER,
74 x_transaction_temp_id OUT NOCOPY NUMBER,
75 x_item_name OUT NOCOPY VARCHAR2,
76 x_subinventory_code OUT NOCOPY VARCHAR2,
77 x_revision OUT NOCOPY VARCHAR2,
78 x_locator_id OUT NOCOPY NUMBER,
79 x_lot_number OUT NOCOPY VARCHAR2,
80 p_trip_id IN NUMBER,
81 p_outermost_lpn_id IN NUMBER);
82
83 PROCEDURE LPN_DISCREPANCY_CHECK( x_result OUT NOCOPY NUMBER,
84 x_parent_lpn_id OUT NOCOPY NUMBER,
85 x_parent_lpn OUT NOCOPY VARCHAR2,
86 x_inventory_item_id OUT NOCOPY NUMBER,
87 x_quantity OUT NOCOPY NUMBER,
88 x_requested_quantity OUT NOCOPY NUMBER,
89 x_item_name OUT NOCOPY VARCHAR2,
90 p_trip_id IN NUMBER,
91 p_delivery_id IN NUMBER,
92 p_outermost_lpn_id IN NUMBER);
93
94 PROCEDURE check_lpn_in_same_trip(p_outermost_lpn_id IN NUMBER,
95 p_organization_id IN NUMBER,
96 p_dock_door_id IN NUMBER,
97 x_result OUT NOCOPY NUMBER,
98 x_loaded_dock_door OUT NOCOPY VARCHAR2,
99 x_delivery_name OUT NOCOPY VARCHAR2,
100 x_trip_name OUT NOCOPY VARCHAR2);
101
102 PROCEDURE LPN_SUBMIT(p_outermost_lpn_id IN NUMBER,
103 p_trip_id IN NUMBER,
104 p_organization_id IN NUMBER,
105 p_dock_door_id IN NUMBER,
106 x_error_code OUT NOCOPY NUMBER,
107 x_outermost_lpn OUT NOCOPY VARCHAR2,
108 x_outermost_lpn_id OUT NOCOPY NUMBER,
109 x_parent_lpn_id OUT NOCOPY NUMBER,
110 x_parent_lpn OUT NOCOPY VARCHAR2,
111 x_inventory_item_id OUT NOCOPY NUMBER,
112 x_quantity OUT NOCOPY NUMBER,
113 x_requested_quantity OUT NOCOPY NUMBER,
114 x_delivery_detail_id OUT NOCOPY NUMBER,
115 x_transaction_Temp_id OUT NOCOPY NUMBER,
116 x_item_name OUT NOCOPY VARCHAR2,
117 x_subinventory_code OUT NOCOPY VARCHAR2,
118 x_revision OUT NOCOPY VARCHAR2,
119 x_locator_id OUT NOCOPY NUMBER,
120 x_lot_number OUT NOCOPY VARCHAR2,
121 x_loaded_dock_door OUT NOCOPY VARCHAR2,
122 x_delivery_name OUT NOCOPY VARCHAR2,
123 x_trip_name OUT NOCOPY VARCHAR2,
124 x_delivery_detail_ids OUT NOCOPY VARCHAR2,
125 p_is_rfid_call IN VARCHAR2 DEFAULT 'N'
126 );
127
128 PROCEDURE CHECK_LPN_DELIVERIES( p_trip_id IN NUMBER,
129 p_organization_id IN NUMBER,
130 p_dock_door_id IN NUMBER,
131 p_outermost_lpn_id IN NUMBER,
132 p_delivery_id IN NUMBER,
133 x_error_code OUT NOCOPY NUMBER,
134 x_missing_item OUT NOCOPY t_genref,
135 x_missing_lpns OUT NOCOPY t_genref,
136 x_ship_set OUT NOCOPY VARCHAR2,
137 x_delivery_info OUT NOCOPY t_genref,
138 x_deli_count OUT NOCOPY NUMBER,
139 p_rfid_call IN VARCHAR2 DEFAULT 'N');
140
141 PROCEDURE CREATE_DELIVERY(p_outermost_lpn_id IN NUMBER,
142 p_trip_id IN NUMBER,
143 p_organization_id IN NUMBER,
144 p_dock_door_id IN NUMBER,
145 x_delivery_id OUT NOCOPY NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_message OUT NOCOPY VARCHAR2,
148 p_direct_ship_flag IN VARCHAR2 DEFAULT 'N');
149
150 PROCEDURE GET_LPN_DELIVERY(x_deliveryLOV OUT NOCOPY t_genref,
151 p_trip_id IN NUMBER,
152 p_organization_id IN NUMBER,
153 p_dock_door_id IN NUMBER,
154 p_delivery_name IN VARCHAR2);
155
156 PROCEDURE update_trip(p_delivery_id IN NUMBER DEFAULT NULL
157 ,p_trip_id IN NUMBER DEFAULT NULL
158 ,p_ship_method_code IN VARCHAR2
159 ,x_return_status OUT nocopy VARCHAR2
160 ,x_msg_data OUT nocopy VARCHAR2
161 ,x_msg_count OUT nocopy number);
162
163 --This procedure calls update_trip if delivery belongs to a trip
164 --and ship_method_code can be propagated up to the trip level
165 PROCEDURE UPDATE_DELIVERY(p_delivery_id IN NUMBER,
166 p_gross_weight IN NUMBER,
167 p_weight_uom IN VARCHAR2,
168 p_waybill IN VARCHAR2,
169 p_bol IN VARCHAR2,
170 p_ship_method_code IN VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2);
172
173 PROCEDURE MISSING_ITEM_CHECK( x_missing_item OUT NOCOPY t_genref,
174 p_trip_id IN NUMBER,
175 p_dock_door_id IN NUMBER,
176 p_organization_id IN NUMBER,
177 x_missing_count OUT NOCOPY NUMBER);
178
179 PROCEDURE nontransactable_item_check(x_nt_item OUT NOCOPY t_genref,
180 p_trip_id IN NUMBER,
181 p_dock_door_id IN NUMBER,
182 p_organization_id IN NUMBER,
183 x_nt_count OUT NOCOPY NUMBER);
184
185 PROCEDURE SHIP_SET_CHECK( p_trip_id IN NUMBER,
186 p_dock_door_id IN NUMBER,
187 p_organization_id IN NUMBER,
188 x_ship_set OUT NOCOPY VARCHAR2,
189 x_return_Status OUT NOCOPY VARCHAR2,
190 x_error_msg OUT NOCOPY VARCHAR2,
191 p_direct_ship_flag IN varchar2 default 'N');
192
193 PROCEDURE MISSING_LPN_CHECK(x_missing_lpns OUT NOCOPY t_genref,
194 p_trip_id IN NUMBER,
195 p_dock_door_id IN NUMBER,
196 p_organization_id IN NUMBER,
197 x_missing_count OUT NOCOPY NUMBER);
198
199 PROCEDURE GET_MISSING_LPN_LOV(x_lpn_lov out NOCOPY t_genref,
200 p_organization_id IN NUMBER,
201 p_dock_door_id IN NUMBER,
202 p_trip_id IN NUMBER,
203 p_lpn IN VARCHAR2);
204
205 PROCEDURE PRINT_LABEL(p_del_rows IN wsh_util_core.id_tab_type,
206 x_return_status OUT NOCOPY VARCHAR2);
207
208 PROCEDURE SHIP_CONFIRM(p_delivery_id IN NUMBER,
209 p_organization_id IN NUMBER,
210 p_delivery_name IN VARCHAR2,
211 p_carrier_id IN NUMBER,
212 p_ship_method_code IN VARCHAR2,
213 p_gross_weight IN NUMBER,
214 p_gross_weight_uom IN VARCHAR2,
215 p_bol IN VARCHAR2,
216 p_waybill IN VARCHAR2,
217 p_action_flag IN VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_data OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER);
221
222 PROCEDURE SHIP_CONFIRM_ALL(p_delivery_id IN NUMBER,
223 p_organization_id IN NUMBER,
224 p_delivery_name IN VARCHAR2,
225 p_carrier_id IN NUMBER,
226 p_ship_method_code IN VARCHAR2,
227 p_gross_weight IN NUMBER,
228 p_gross_weight_uom IN VARCHAR2,
229 p_bol IN VARCHAR2,
230 p_waybill IN VARCHAR2,
231 p_action_flag IN VARCHAR2,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_data OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER);
235
236 PROCEDURE SHIP_CONFIRM_LPN_DELIVERIES(x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_data OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 p_trip_stop_id IN NUMBER,
240 p_trip_id IN NUMBER,
241 p_dock_door_id IN NUMBER,
242 p_organization_id IN NUMBER,
243 p_verify_only IN VARCHAR2,
244 p_close_trip_flag IN VARCHAR2 DEFAULT 'N',
245 p_allow_ship_set_break IN VARCHAR2 DEFAULT 'N');
246
247 procedure get_serial_number_for_so(
248 x_serial_lov out NOCOPY t_genref,
249 p_inventory_item_id IN NUMBER,
250 p_organization_id IN NUMBER,
251 p_subinventory_code IN VARCHAR2,
252 p_locator_id IN NUMBER,
253 p_revision IN VARCHAR2,
254 p_lot_number IN VARCHAR2,
255 p_serial_number IN VARCHAR2);
256
257 PROCEDURE insert_Serial_Numbers(
258 x_status OUT NOCOPY VARCHAR2,
259 p_fm_serial_number IN VARCHAR2,
260 p_to_serial_number IN VARCHAR2,
261 p_transaction_Temp_id IN NUMBER);
262
263 PROCEDURE Validate_LPN_Status(x_result OUT NOCOPY NUMBER,
264 x_msg_code OUT NOCOPY VARCHAR2,
265 p_trip_id IN NUMBER,
266 p_organization_id IN NUMBER,
267 p_lpn_id IN NUMBER);
268
269 procedure wms_installed_status(x_status OUT NOCOPY VARCHAR2);
270
271 procedure GET_SERIAL_STATUS_CODE(x_serial_status_id OUT NOCOPY NUMBER,
272 x_serial_status_code OUT NOCOPY VARCHAR2,
273 p_organization_id IN NUMBER,
274 p_inventory_item_id IN NUMBER);
275
276 procedure UNASSIGN_DELIVERY_LINE(p_delivery_detail_id IN NUMBER,
277 x_return_status OUT NOCOPY VARCHAR2,
278 p_delivery_id IN NUMBER DEFAULT NULL,
279 p_commit_flag IN VARCHAR2
280 DEFAULT FND_API.G_TRUE );
281
282
283 PROCEDURE update_wdd_loc_by_lpn
287 p_locator_id NUMBER);
284 (x_return_status OUT NOCOPY VARCHAR2,
285 p_lpn_id NUMBER,
286 p_subinventory_code VARCHAR2,
288
289 PROCEDURE GET_LOADED_LPN_LOV(x_lpn_lov out NOCOPY t_genref,
290 p_organization_id IN NUMBER,
291 p_dock_door_id IN NUMBER,
292 p_lpn IN VARCHAR2);
293
294 PROCEDURE GET_LOADED_DOCK_DOORS(x_dock_door_LOV OUT NOCOPY t_genref,
295 p_organization_id in NUMBER,
296 p_dock_door IN VARCHAR2);
297 PROCEDURE lpn_unload(p_organization_id IN NUMBER,
298 p_outermost_lpn_id IN NUMBER,
299 x_error_code OUT NOCOPY NUMBER);
300
301
302
303 /* Direct Shipping */
304
305 -- LOV for Direct Ship LPN
306 PROCEDURE get_directshiplpn_lov (
307 x_lpn OUT NOCOPY t_genref
308 , p_organization_id IN NUMBER
309 , p_lpn IN VARCHAR2);
310
311 -- LOV for Order
312 PROCEDURE get_order_lov(
313 x_order_lov OUT NOCOPY t_genref
314 , p_org_id IN NUMBER
315 , p_order IN VARCHAR2);
316
317 -- LOV for Order line
318 /* PROCEDURE get_orderline_lov(
319 x_orderline_lov OUT t_genref
320 , p_org_id IN NUMBER
321 , p_header_id IN NUMBER
322 , p_outermost_lpn_id IN NUMBER
323 , p_order_line IN VARCHAR2);
324 */
325 PROCEDURE get_orderline_lov(
326 x_orderline_lov OUT NOCOPY T_GENREF
327 , p_org_id IN NUMBER
328 , p_header_id IN NUMBER
329 , p_order_line IN VARCHAR2
330 , p_outermost_lpn_id IN NUMBER
331 , p_cross_proj_flag IN VARCHAR2
332 , p_project_id IN NUMBER
333 , p_task_id IN NUMBER
334 );
335
336 -- LOV for Freight Cost Type
337 PROCEDURE Get_FreightCost_Type(
338 x_freight_type_code out NOCOPY t_genref
339 , p_text in VARCHAR2);
340
341 -- LOV for Freight Term
342 PROCEDURE Get_Freight_Term (
343 x_freight_terms out NOCOPY t_genref
344 , p_text in varchar2);
345
346 -- LOV for Document Set
347 Procedure Get_document_set_lov(
348 x_report_set out NOCOPY t_genref
349 , p_text in varchar2);
350
351 -- LOV for Conversion Type
352 Procedure GET_CONVERSION_TYPE(
353 x_conversion_type out NOCOPY t_genref
354 , p_text in varchar2);
355
356 -- LOV for Currency
357 Procedure GET_CURRENCY_CODE(
358 x_currency out NOCOPY t_genref
359 , p_text in varchar2);
360
361 -- LOV for Unload Truck LPN
362 Procedure Get_unloadTruck_lpn_lov (
363 x_lpn_lov out NOCOPY t_genref
364 , p_organization_id IN NUMBER
365 , p_dock_door_id IN NUMBER
366 , p_lpn IN VARCHAR2);
367
368
369 PROCEDURE Get_LPN_Contents (
370 x_lpn_contents OUT NOCOPY t_genref,
371 p_lpn_id IN NUMBER,
372 p_org_id IN NUMBER);
373
374 /* Direct Sbipping */
375
376 FUNCTION get_container_name(p_container_name IN VARCHAR2) RETURN
377 VARCHAR2 ;
378
379 --This procedure updates the serial_summary_entry
380 --column in wms_lpn_contents for content LPN that
381 --has serial @ SO issue items. This procedure is
382 --called after user has entered serial numbers at the
383 --time of loading to dock.
384 PROCEDURE update_lpn_contents
385 (p_outermost_lpn_id IN NUMBER,
386 p_org_id IN NUMBER,
387 x_return_status OUT nocopy VARCHAR2,
388 x_msg_count OUT nocopy NUMBER,
389 x_msg_data OUT nocopy VARCHAR2);
390
391 /*
392 API Name:close_truck
393
394 Input parameters:
395 P_dock_door_id : Shipping dock door id
396 P_organization_id : organization_id
397 p_shipping_mode : 'NORMAL'--Equivalent to normal LPN ship;
398 'DIRECT'--Equivalent to Direct LPN ship;
399 NULL --will process both above;
400 Output parameters:
401 x_return_status : 'S' --Sucess,
402 'W' --Warning
403 'E' --ERROR
404 x_return_msg : Returned message
405
406 */
407
408
409 /*#
410 * This procedure CLOSE_TRUCK is used to close the truck after LPNs have
411 * been loaded on the truck at the dock door through an RFID device event.
412 * @ param P_dock_door_id Shipping dock door id
413 * @ paraminfo {@rep:required}
414 * @ param P_organization_id organization_id
415 * @ paraminfo {@rep:required}
416 * @ param p_shipping_mode NORMAL(Normal LPN ship),DIRECT (Direct LPN ship),NULL(Both)
417 * @ paraminfo {@rep:required}
418 * @ param p_commit_flag commit the transaction or not
419 * @ paraminfo {@rep:required}
420 * @ param x_return_status Status of request. ( S = Success, E = Error, W = Warning)
421 * @ paraminfo {@rep:required}
422 * @ param x_return_msg Returned message
423 * @ paraminfo {@rep:required}
424 * @rep:scope public
425 * @rep:lifecycle active
426 * @rep:displayname Close truck after the Truck Load through RFID
427 * @rep:businessevent close_truck
428 */
429 procedure close_truck
430 (P_dock_door_id IN NUMBER,
431 P_organization_id IN NUMBER,
432 p_shipping_mode IN VARCHAR2 DEFAULT NULL,
433 p_commit_flag IN VARCHAR2 DEFAULT fnd_api.g_true,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_return_msg OUT NOCOPY VARCHAR2);
436
437 END WMS_SHIPPING_TRANSACTION_PUB;
[Home] [Help]