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