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