1 PACKAGE WMS_DIRECT_SHIP_PVT AUTHID CURRENT_USER AS
2 /* $Header: WMSDSPVS.pls 120.3.12020000.5 2013/03/13 20:05:03 sahmahes ship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_DIRECT_SHIP_PVT';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8
9 TYPE t_genref IS REF CURSOR;
10
11 PROCEDURE DEBUG(p_message IN VARCHAR2,
12 p_module IN VARCHAR2 default 'abc',
13 p_level IN VARCHAR2 DEFAULT 9);
14
15 PROCEDURE GET_TRIPSTOP_INFO( x_tripstop_info OUT NOCOPY t_genref
16 ,p_trip_id IN NUMBER
17 ,p_org_id IN NUMBER);
18
19 PROCEDURE GET_DELIVERY_INFO(x_delivery_info OUT NOCOPY t_genref,
20 p_delivery_id IN NUMBER,
21 p_org_id IN NUMBER); /*Bug 2767767: Passing the org id to get the correct value of Enforce Ship Method*/
22
23 FUNCTION GET_DELIVERY_LPN(p_delivery_id NUMBER) RETURN VARCHAR2;
24
25 FUNCTION check_direct_ship_for_lpn (p_lpn_id IN NUMBER) RETURN BOOLEAN; --bug11828449
26
27 FUNCTION GET_SHIPMETHOD_MEANING(p_ship_method_code IN VARCHAR2) RETURN VARCHAR2;
28
29 FUNCTION GET_FOBLOC_CODE_MEANING(p_fob_code IN VARCHAR2) RETURN VARCHAR2;
30
31 FUNCTION GET_FOB_LOCATION(p_fob_location_id IN NUMBER) RETURN VARCHAR2;
32
33 FUNCTION GET_FREIGHT_TERM(p_freight_term_code VARCHAR2)RETURN VARCHAR2;
34
35 FUNCTION GET_BOL(p_delivery_id NUMBER)RETURN NUMBER;
36
37 FUNCTION get_enforce_ship RETURN VARCHAR2;
38
39 PROCEDURE CHECK_DELIVERY(x_return_status OUT NOCOPY VARCHAR2
40 ,x_msg_count OUT NOCOPY NUMBER
41 ,x_msg_data OUT NOCOPY VARCHAR2
42 ,x_error_code OUT NOCOPY NUMBER
43 ,p_delivery_id IN NUMBER
44 ,p_org_id IN NUMBER
45 ,p_dock_door_id IN NUMBER
46 );
47
48 PROCEDURE UPDATE_DELIVERY(
49 x_return_status OUT NOCOPY VARCHAR2
50 ,x_msg_count OUT NOCOPY NUMBER
51 ,x_msg_data OUT NOCOPY VARCHAR2
52 ,p_delivery_id IN NUMBER
53 ,p_net_weight IN NUMBER
54 ,p_gross_weight IN NUMBER
55 ,p_wt_uom_code IN VARCHAR2
56 ,p_waybill IN VARCHAR2
57 ,p_ship_method_code IN VARCHAR2
58 ,p_fob_code IN VARCHAR2
59 ,p_fob_location_id IN NUMBER
60 ,p_freight_term_code IN VARCHAR2
61 ,p_freight_term_name IN VARCHAR2
62 ,p_intmed_shipto_loc_id IN NUMBER
63 );
64
65 PROCEDURE MISSING_ITEM_CUR( x_missing_item_cur OUT NOCOPY t_genref
66 ,p_delivery_id IN NUMBER
67 ,p_dock_door_id IN NUMBER
68 ,p_organization_id IN NUMBER
69 );
70
71
72 -- Call this API from Continue button of Delivery Info page. If delivery_id
73 -- is passed then it will confirm the delivery, if LPN id is passed, then it will
74 -- confirm the delivery belonging to a LPN, if org_id and dock door id is passed and
75 -- delivery id/ lpn id is kept null then all deliveries loaded on the dock will be
76 -- Shipconfirmed
77
78 PROCEDURE SHIP_CONFIRM(
79 x_return_status OUT NOCOPY VARCHAR2
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 ,x_missing_item_cur OUT NOCOPY t_genref
83 ,x_error_code OUT NOCOPY NUMBER
84 ,p_delivery_id IN NUMBER
85 ,p_net_weight IN NUMBER DEFAULT NULL
86 ,p_gross_weight IN NUMBER DEFAULT NULL
87 ,p_wt_uom_code IN VARCHAR2 DEFAULT NULL
88 ,p_waybill IN VARCHAR2 DEFAULT NULL
89 ,p_ship_method_code IN VARCHAR2 DEFAULT NULL
90 ,p_fob_code IN VARCHAR2 DEFAULT NULL
91 ,p_fob_location_id IN NUMBER DEFAULT NULL
92 ,p_freight_term_code IN VARCHAR2 DEFAULT NULL
93 ,p_freight_term_name IN VARCHAR2 DEFAULT NULL
94 ,p_intmed_shipto_loc_id IN NUMBER DEFAULT NULL
95 ,p_org_id IN NUMBER DEFAULT NULL
96 ,p_dock_door_id IN NUMBER DEFAULT NULL
97 );
98
99 PROCEDURE CONFIRM_ALL_DELIVERIES(
100 x_return_status OUT NOCOPY VARCHAR2
101 ,x_msg_count OUT NOCOPY NUMBER
102 ,x_msg_data OUT NOCOPY VARCHAR2
103 ,x_missing_item_cur OUT NOCOPY t_genref
104 ,x_error_code OUT NOCOPY NUMBER
105 ,p_delivery_id IN NUMBER
106 ,p_net_weight IN NUMBER
107 ,p_gross_weight IN NUMBER
108 ,p_wt_uom_code IN VARCHAR2
109 ,p_waybill IN VARCHAR2
110 ,p_ship_method_code IN VARCHAR2
111 ,p_fob_code IN VARCHAR2
112 ,p_fob_location_id IN NUMBER
113 ,p_freight_term_code IN VARCHAR2
114 ,p_freight_term_name IN VARCHAR2
115 ,p_intmed_shipto_loc_id IN NUMBER
116 ,p_org_id IN NUMBER
117 ,p_dock_door_id IN NUMBER
118 );
119
120 PROCEDURE CREATE_TRIP(
121 x_return_status OUT NOCOPY VARCHAR2
122 ,p_organization_id IN NUMBER
123 ,p_dock_door_id IN NUMBER
124 ,p_delivery_id IN NUMBER /*bug 2741857 */
125 ,p_direct_ship_flag IN VARCHAR2 DEFAULT 'N'
126 );
127
128
129 PROCEDURE UPDATE_TRIPSTOP(
130 x_return_status OUT NOCOPY VARCHAR2
131 ,x_msg_count OUT NOCOPY NUMBER
132 ,x_msg_data OUT NOCOPY VARCHAR2
133 ,p_trip_id IN NUMBER
134 ,p_vehicle_item_id IN NUMBER
135 ,p_vehicle_num_prefix IN VARCHAR2
136 ,p_vehicle_num IN VARCHAR2
137 ,p_seal_code IN VARCHAR2
138 ,p_org_id IN NUMBER DEFAULT NULL
139 ,p_dock_door_id IN NUMBER DEFAULT NULL
140 ,p_ship_method_code IN VARCHAR2 DEFAULT NULL
141 );
142
143
144 PROCEDURE PRINT_SHIPPING_DOCUMENT(
145 x_return_status OUT NOCOPY VARCHAR2
146 ,x_msg_count OUT NOCOPY NUMBER
147 ,x_msg_data OUT NOCOPY VARCHAR2
148 ,p_trip_id IN NUMBER
149 ,p_vehicle_item_id IN NUMBER
150 ,p_vehicle_num_prefix IN VARCHAR2
151 ,p_vehicle_num IN VARCHAR2
152 ,p_seal_code IN VARCHAR2
153 ,p_document_set_id IN NUMBER
154 ,p_org_id IN NUMBER DEFAULT NULL
155 ,p_dock_door_id IN NUMBER DEFAULT NULL
156 ,p_ship_method_code IN VARCHAR2 DEFAULT NULL
157 );
158
159 PROCEDURE CLOSE_TRIP(
160 x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER
162 ,x_msg_data OUT NOCOPY VARCHAR2
163 ,p_trip_id IN NUMBER
164 ,p_vehicle_item_id IN NUMBER
165 ,p_vehicle_num_prefix IN VARCHAR2
166 ,p_vehicle_num IN VARCHAR2
167 ,p_seal_code IN VARCHAR2
168 ,p_document_set_id IN NUMBER
169 ,p_org_id IN NUMBER DEFAULT NULL
170 ,p_dock_door_id IN NUMBER DEFAULT NULL
171 ,p_ship_method_code IN VARCHAR2 DEFAULT NULL
172 );
173
174 -- API Name : UNLOAD_TRUCK
175 -- Type : Procedure
176 -- Function : This procedure does the following:
177 -- 1.Change LPN context to "Resides in Inventory"
178 -- 2.Unpack the LPN in Shipping
179 -- 3.Remove inventory details from Shipping (Sub, Loc, Qty).
180 -- 4.Remove the Reservation records
181 -- 5.Reset the serial_number_control_code,current_status
182 -- ->if serial control code is "At SO Issue", reset current status to "Defined but not used"
183 -- ->if serial control code is "Predefined" or "At Receipt" reset status to "resides in stores"
184 -- ->Reset Group Mark Id
185 -- Input Parameters :
186 -- p_org_id Organization Id
187 -- p_outermost_lpn_id Outermost LPN Id
188 -- Output Parameters :
189 -- x_return_status Standard Output Parameter
190 -- x_msg_count Standard Output Parameter
191 -- x_msg_data Standard Output Parameter
192
193 PROCEDURE UNLOAD_TRUCK ( x_return_status OUT NOCOPY VARCHAR2
194 ,x_msg_count OUT NOCOPY NUMBER
195 ,x_msg_data OUT NOCOPY VARCHAR2
196 ,p_org_id IN NUMBER
197 ,p_outermost_lpn_id IN NUMBER
198 ,p_relieve_rsv IN VARCHAR2 DEFAULT 'Y');
199
200 PROCEDURE CLEANUP_TEMP_RECS (x_return_status OUT NOCOPY VARCHAR2
201 ,x_msg_count OUT NOCOPY NUMBER
202 ,x_msg_data OUT NOCOPY VARCHAR2
203 ,p_org_id IN NUMBER
204 ,p_outermost_lpn_id IN NUMBER
205 ,p_trip_id IN NUMBER
206 ,p_dock_door_id IN NUMBER DEFAULT NULL);
207
208
209 PROCEDURE get_global_values(x_userid OUT NOCOPY number,
210 x_logonid OUT NOCOPY number,
211 x_last_upd_date OUT NOCOPY date,
212 x_current_date OUT NOCOPY date );
213
214
215 PROCEDURE update_freight_cost(x_return_status OUT NOCOPY VARCHAR2
216 ,x_msg_count OUT NOCOPY NUMBER
217 ,x_msg_data OUT NOCOPY VARCHAR2
218 ,p_lpn_id IN NUMBER
219 );
220
221 procedure create_resv(x_return_status OUT NOCOPY VARCHAR2
222 ,x_msg_count OUT NOCOPY NUMBER
223 ,x_msg_data OUT NOCOPY VARCHAR2
224 ,p_group_id IN NUMBER
225 ,p_org_id IN NUMBER
226 );
227
228 PROCEDURE load_truck (x_return_status OUT NOCOPY VARCHAR2
229 ,x_msg_data OUT NOCOPY VARCHAR2
230 ,x_msg_count OUT NOCOPY NUMBER
231 ,p_group_id IN NUMBER
232 ,P_ORG_ID IN NUMBER
233 ,p_dock_door_id IN NUMBER
234 );
235
236 PROCEDURE close_truck (x_return_status OUT NOCOPY VARCHAR2
237 ,x_msg_data OUT NOCOPY VARCHAR2
238 ,x_msg_count OUT NOCOPY NUMBER
239 ,x_error_code OUT NOCOPY NUMBER
240 ,x_missing_item_cur OUT NOCOPY t_genref
241 ,p_dock_door_id IN NUMBER
242 ,p_group_id IN NUMBER
243 ,p_org_id IN NUMBER
244 );
245
246
247 PROCEDURE EXPLODE_DELIVERY_DETAILS(
248 x_return_status OUT NOCOPY VARCHAR2
249 ,x_msg_count OUT NOCOPY NUMBER
250 ,X_MSG_DATA OUT NOCOPY VARCHAR2
251 --Bug No 3390432
252 -- New Out Parameter
253 ,x_transaction_temp_id OUT NOCOPY NUMBER
254 ,p_organization_id IN NUMBER
255 ,p_lpn_id IN NUMBER
256 ,p_serial_number_control_code IN NUMBER
257 ,p_delivery_detail_id IN NUMBER
258 ,p_quantity IN NUMBER
259 ,p_transaction_temp_id IN NUMBER DEFAULT NULL
260 ,p_reservation_id IN NUMBER DEFAULT NULL
261 ,p_last_action IN VARCHAR2 DEFAULT 'U');
262
263 PROCEDURE STAGE_LPNS(
264 x_return_status OUT NOCOPY VARCHAR2
265 ,x_msg_count OUT NOCOPY NUMBER
266 ,x_msg_data OUT NOCOPY VARCHAR2
267 ,p_group_id IN NUMBER
268 ,p_organization_id IN NUMBER
269 ,p_dock_door_id IN NUMBER
270 ) ;
271
272 PROCEDURE GET_LPN_AVAILABLE_QUANTITY(
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 p_organization_id IN NUMBER,
277 p_lpn_id IN NUMBER,
278 p_inventory_item_id IN NUMBER,
279 p_revision IN VARCHAR2,
280 p_line_id IN NUMBER,
281 p_header_id IN NUMBER,
282 x_qoh OUT NOCOPY NUMBER,
283 x_att OUT NOCOPY NUMBER);
284
285 PROCEDURE create_update_containers(
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 -- x_container_wdds OUT nocopy wsh_util_core.id_tab_type,
290 p_org_id IN NUMBER,
291 p_outermost_lpn_id IN NUMBER,
292 p_delivery_id IN NUMBER DEFAULT null);
293
294 PROCEDURE UPDATE_SHIPPED_QUANTITY(
295 x_return_status OUT NOCOPY VARCHAR2
296 ,x_msg_count OUT NOCOPY NUMBER
300 );
297 ,x_msg_data OUT NOCOPY VARCHAR2
298 ,p_delivery_id IN NUMBER
299 ,p_org_id IN NUMBER DEFAULT NULL
301 PROCEDURE Container_Nesting(
302 x_return_status OUT NOCOPY VARCHAR2,
303 x_msg_count OUT NOCOPY VARCHAR2,
304 x_msg_data OUT NOCOPY VARCHAR2,
305 p_organization_id IN NUMBER,
306 p_outermost_lpn_id IN NUMBER,
307 p_action_code IN VARCHAR2 DEFAULT 'PACK') ;
308
309 procedure check_order_line_split(
310 x_return_status OUT NOCOPY VARCHAR2
311 ,x_msg_count OUT NOCOPY NUMBER
312 ,x_msg_data OUT NOCOPY VARCHAR2
313 ,x_error_code OUT NOCOPY NUMBER
314 ,p_delivery_id IN NUMBER
315 );
316 procedure CHECK_MISSING_ITEM_CUR(p_delivery_id IN NUMBER
317 ,p_dock_door_id IN NUMBER
318 ,p_organization_id IN number
319 ,x_return_Status OUT NOCOPY VARCHAR2
320 ,x_missing_count OUT NOCOPY NUMBER
321 );
322 PROCEDURE chk_del_for_direct_ship(x_return_status OUT NOCOPY VARCHAR2
323 ,x_msg_count OUT NOCOPY NUMBER
324 ,x_msg_data OUT NOCOPY VARCHAR2
325 ,p_delivery_id IN NUMBER
326 );
327
328 /* procedures added for Patchset I*/
329
330 PROCEDURE process_lpn(p_lpn_id IN NUMBER,
331 p_org_id IN NUMBER,
332 p_dock_door_id IN NUMBER,
333 p_fulfillment_base IN VARCHAR2,
334 x_remaining_qty OUT NOCOPY NUMBER,
335 x_sec_remaining_qty OUT NOCOPY NUMBER,
336 x_num_line_processed OUT NOCOPY NUMBER,
337 x_project_id OUT NOCOPY NUMBER,
338 x_task_id OUT NOCOPY NUMBER,
339 x_cross_project_allowed OUT NOCOPY VARCHAR2,
340 x_cross_unit_allowed OUT NOCOPY VARCHAR2,
341 x_group_by_customer_flag OUT NOCOPY VARCHAR2,
342 x_group_by_fob_flag OUT NOCOPY VARCHAR2,
343 x_group_by_freight_terms_flag OUT NOCOPY VARCHAR2,
344 x_group_by_intmed_ship_flag OUT NOCOPY VARCHAR2,
345 x_group_by_ship_method_flag OUT NOCOPY VARCHAR2,
346 x_group_by_ship_to_loc_value OUT NOCOPY VARCHAR2,
347 x_group_by_ship_from_loc_value OUT NOCOPY VARCHAR2,
348 x_group_by_customer_value OUT NOCOPY VARCHAR2,
349 x_group_by_fob_value OUT NOCOPY VARCHAR2,
350 x_group_by_freight_terms_value OUT NOCOPY VARCHAR2,
351 x_group_by_intmed_value OUT NOCOPY VARCHAR2,
352 x_group_by_ship_method_value OUT NOCOPY VARCHAR2,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2
356 );
357
358 PROCEDURE IS_CT_WT_ENABLED(p_lpn_id IN NUMBER,
359 p_org_id IN NUMBER,
360 x_ct_wt_enabled OUT NOCOPY NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2);
364
365 /* This procedure creates reservations for a line if it is not there and
366 inserts processed line record into WDS.
367 */
368 PROCEDURE process_line(
369 p_lpn_id IN NUMBER
370 , p_org_id IN NUMBER
371 , p_dock_door_id IN NUMBER
372 , p_order_header_id IN NUMBER
373 , p_order_line_id IN NUMBER
374 , p_inventory_item_id IN NUMBER
375 , p_revision IN VARCHAR2
376 , p_end_item_unit_number IN VARCHAR2
377 , p_ordered_quantity IN NUMBER
378 , p_sec_ordered_qty IN NUMBER
379 , p_processed_quantity IN NUMBER
380 , p_sec_processed_qty IN NUMBER
381 , p_date_requested IN DATE
382 , x_remaining_quantity OUT NOCOPY NUMBER
383 , x_sec_remaining_qty OUT NOCOPY NUMBER
384 , x_return_status OUT NOCOPY VARCHAR2
385 , x_msg_count OUT NOCOPY NUMBER
386 , x_msg_data OUT NOCOPY VARCHAR2
387 );
388
389 /*
390 This procedure perform the following processing for a lpn.
391 1. Update staged flag of all reservations for all the lines packed into LPN.
392 2. Stage LPN
393 3. Update Freight Cost for LPN
394 */
395 PROCEDURE Load_LPN (x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_lpn_id IN NUMBER,
399 p_org_id IN NUMBER,
400 p_dock_door_id IN NUMBER
401 );
402 /*
403 This procedure distributes the un-used quantity in the lpn among all the loaded lines.
407
404 First it checks if for a lpn_content record a exact matching reservation is found the
405 update the existing reservation else create new reservation.
406 */
408 PROCEDURE Perform_Overship_Distribution (p_lpn_id IN NUMBER,
409 p_org_id IN NUMBER,
410 p_dock_door_id IN NUMBER,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2
414 ) ;
415 FUNCTION Validate_Del_Grp_Rules(p_line_processed IN NUMBER,
416 p_header_id IN NUMBER,
417 p_line_id IN NUMBER) RETURN BOOLEAN ;
418
419
420 /*This procedure checks if any holds are applied on a particular order line being shipped and
421 also applies the credit check hold for a customer of a particular line
422 */
423 PROCEDURE Check_Holds(p_order_header_id IN NUMBER,
424 p_order_line_id IN NUMBER,
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2
428 );
429 /* This procedure cleanup all the temp data for a backordered delivery for this lpn */
430 PROCEDURE cleanup_orphan_rec(
431 p_org_id IN NUMBER
432 );
433 /*
434 This method checks that there should be no record for this lpn in wstt (delivery)
435 having direct_ship_flag N or loaded by some other method than direct ship.
436 */
437
438 FUNCTION Validate_Del_For_DS(p_lpn_id IN NUMBER,
439 p_org_id IN NUMBER,
440 p_dock_door_id IN NUMBER,
441 p_header_id IN NUMBER,
442 p_line_id IN NUMBER
443 )RETURN BOOLEAN;
444
445 /*
446 This function finds out if there is any record in lpn contents having
447 available quantity >0 and end_item_unit_number=p_end_unit_number
448 */
449 FUNCTION Validate_End_Unit_Num(p_item_id IN NUMBER,
450 p_end_unit_number IN VARCHAR2
451 )RETURN BOOLEAN ;
452
453 /* This function finds out that the item in g_lpn_contents_tab at index is having available quantity
454 and its end_item_unit_number matches p_end_unit_number.
455 */
456 FUNCTION Validate_End_Unit_Num_At( p_index IN NUMBER,
457 p_end_unit_number IN VARCHAR2
458 ) RETURN BOOLEAN ;
459
460 FUNCTION get_fulfillment_base RETURN VARCHAR2;
461
462 END; -- WMS_DIRECT_SHIP_PVT
[Home] [Help]