[Home] [Help]
PACKAGE: APPS.WSH_DELIVERY_DETAILS_PKG
Source
1 PACKAGE WSH_DELIVERY_DETAILS_PKG AUTHID CURRENT_USER AS
2 /* $Header: WSHDDTHS.pls 120.5 2010/08/06 16:18:26 anvarshn ship $ */
3
4 --==============DECLARE CONSTANTS =======================================
5 -- Declare Constants for Various released status of delivery detail
6
7 C_READY_TO_RELEASE CONSTANT VARCHAR2(1) := 'R';
8 C_RELEASED_TO_WAREHOUSE CONSTANT VARCHAR2(1) := 'S';
9 C_STAGED CONSTANT VARCHAR2(1) := 'Y';
10 C_SHIP_CONFIRMED CONSTANT VARCHAR2(1) := 'C';
11 C_INTERFACED CONSTANT VARCHAR2(1) := 'I';
12 C_BACKORDERED CONSTANT VARCHAR2(1) := 'B';
13 C_PLANNED_FOR_XDOCK CONSTANT VARCHAR2(1) := 'K';
14 C_NOT_APPLICABLE CONSTANT VARCHAR2(1) := 'X';
15 C_CANCELLED CONSTANT VARCHAR2(1) := 'D';
19 --==============DECLARE RECORD/TABLE STRUCTURES=======================================
16
17 --========================================================================
18
20
21 /* Do not use Delivery_Details_Rec_Type, use the record defined in
22 wsh_glbl_var_strct_grp
23 */
24 TYPE Delivery_Details_Rec_Type IS RECORD
25 (delivery_detail_id NUMBER,
26 source_code VARCHAR2(30),
27 source_header_id NUMBER,
28 source_line_id NUMBER,
29 customer_id NUMBER,
30 sold_to_contact_id NUMBER,
31 inventory_item_id NUMBER,
32 item_description VARCHAR2(250),
33 hazard_class_id NUMBER,
34 country_of_origin VARCHAR2(50),
35 classification VARCHAR2(30),
36 ship_from_location_id NUMBER,
37 ship_to_location_id NUMBER,
38 ship_to_contact_id NUMBER,
39 ship_to_site_use_id NUMBER,
40 deliver_to_location_id NUMBER,
41 deliver_to_contact_id NUMBER,
42 deliver_to_site_use_id NUMBER,
43 intmed_ship_to_location_id NUMBER,
44 intmed_ship_to_contact_id NUMBER,
45 hold_code VARCHAR2(1),
46 ship_tolerance_above NUMBER,
47 ship_tolerance_below NUMBER,
48 requested_quantity NUMBER,
49 shipped_quantity NUMBER,
50 delivered_quantity NUMBER,
51 requested_quantity_uom VARCHAR2(3),
52 subinventory VARCHAR2(10),
53 revision VARCHAR2(3),
54 -- HW OPMCONV. Need to expand length of lot_number to 80
55 lot_number VARCHAR2(80),
56 customer_requested_lot_flag VARCHAR2(1),
57 serial_number VARCHAR2(30),
58 locator_id NUMBER,
59 date_requested DATE,
60 date_scheduled DATE,
61 master_container_item_id NUMBER,
62 detail_container_item_id NUMBER,
63 load_seq_number NUMBER,
64 ship_method_code VARCHAR2(30),
65 carrier_id NUMBER,
66 freight_terms_code VARCHAR2(30),
67 shipment_priority_code VARCHAR2(30),
68 fob_code VARCHAR2(30),
69 customer_item_id NUMBER,
70 dep_plan_required_flag VARCHAR2(1),
71 customer_prod_seq VARCHAR2(50),
72 customer_dock_code VARCHAR2(50),
73 cust_model_serial_number VARCHAR2(50),
74 customer_job VARCHAR2(50),
75 customer_production_line VARCHAR2(50),
76 net_weight NUMBER,
77 weight_uom_code VARCHAR2(3),
78 volume NUMBER,
79 volume_uom_code VARCHAR2(3),
80 tp_attribute_category VARCHAR2(240),
81 tp_attribute1 VARCHAR2(240),
82 tp_attribute2 VARCHAR2(240),
83 tp_attribute3 VARCHAR2(240),
84 tp_attribute4 VARCHAR2(240),
85 tp_attribute5 VARCHAR2(240),
86 tp_attribute6 VARCHAR2(240),
87 tp_attribute7 VARCHAR2(240),
88 tp_attribute8 VARCHAR2(240),
89 tp_attribute9 VARCHAR2(240),
90 tp_attribute10 VARCHAR2(240),
91 tp_attribute11 VARCHAR2(240),
92 tp_attribute12 VARCHAR2(240),
93 tp_attribute13 VARCHAR2(240),
94 tp_attribute14 VARCHAR2(240),
95 tp_attribute15 VARCHAR2(240),
96 attribute_category VARCHAR2(150),
97 attribute1 VARCHAR2(150),
98 attribute2 VARCHAR2(150),
99 attribute3 VARCHAR2(150),
100 attribute4 VARCHAR2(150),
101 attribute5 VARCHAR2(150),
102 attribute6 VARCHAR2(150),
103 attribute7 VARCHAR2(150),
104 attribute8 VARCHAR2(150),
105 attribute9 VARCHAR2(150),
106 attribute10 VARCHAR2(150),
107 attribute11 VARCHAR2(150),
108 attribute12 VARCHAR2(150),
109 attribute13 VARCHAR2(150),
110 attribute14 VARCHAR2(150),
111 attribute15 VARCHAR2(150),
112 created_by NUMBER,
113 creation_date DATE,
114 last_update_date DATE,
115 last_update_login NUMBER,
116 last_updated_by NUMBER,
117 program_application_id NUMBER,
118 program_id NUMBER,
119 program_update_date DATE,
120 request_id NUMBER,
121 mvt_stat_status VARCHAR2(30),
122 released_flag VARCHAR2(1),
123 organization_id NUMBER,
124 transaction_temp_id NUMBER,
125 ship_set_id NUMBER,
126 arrival_set_id NUMBER,
127 ship_model_complete_flag VARCHAR2(1),
128 top_model_line_id NUMBER,
129 source_header_number VARCHAR2(150),
130 source_header_type_id NUMBER,
131 source_header_type_name VARCHAR2(240),
132 cust_po_number VARCHAR2(50),
133 ato_line_id NUMBER,
134 src_requested_quantity NUMBER,
135 src_requested_quantity_uom VARCHAR2(3),
136 move_order_line_id NUMBER,
137 cancelled_quantity NUMBER,
138 quality_control_quantity NUMBER,
139 cycle_count_quantity NUMBER,
140 tracking_number VARCHAR2(30),
141 movement_id NUMBER,
142 shipping_instructions VARCHAR2(2000),
143 packing_instructions VARCHAR2(2000),
144 project_id NUMBER,
145 task_id NUMBER,
146 org_id NUMBER,
147 oe_interfaced_flag VARCHAR2(1),
148 split_from_detail_id NUMBER,
149 inv_interfaced_flag VARCHAR2(1),
150 source_line_number VARCHAR2(150),
151 inspection_flag VARCHAR2(1),
152 released_status VARCHAR2(1),
153 container_flag VARCHAR2(1),
154 container_type_code VARCHAR2(30),
155 container_name VARCHAR2(30),
156 fill_percent NUMBER,
157 gross_weight NUMBER,
158 master_serial_number VARCHAR2(30),
159 maximum_load_weight NUMBER,
160 maximum_volume NUMBER,
161 minimum_fill_percent NUMBER,
162 seal_code VARCHAR2(30),
163 unit_number VARCHAR2(30),
164 unit_price NUMBER,
168 -- hverddin 26-jun-2000 start of OPM changes
165 currency_code VARCHAR2(15),
166 freight_class_cat_id NUMBER,
167 commodity_code_cat_id NUMBER,
169 -- HW OPMCONV. Need to expand length of grade to 150
170 preferred_grade VARCHAR2(150),
171 src_requested_quantity2 NUMBER,
172 src_requested_quantity_uom2 VARCHAR2(3),
173 requested_quantity2 NUMBER,
174 shipped_quantity2 NUMBER,
175 delivered_quantity2 NUMBER,
176 cancelled_quantity2 NUMBER,
177 quality_control_quantity2 NUMBER,
178 cycle_count_quantity2 NUMBER,
179 requested_quantity_uom2 VARCHAR2(3),
180 -- HW OPMCONV. No need for sublot anymore
181 -- sublot_number VARCHAR2(32) ,
182 -- hverddin 26-jun-2000 end of OPM changes
183 lpn_id NUMBER ,
184 pickable_flag VARCHAR2(1),
185 original_subinventory VARCHAR2(10),
186 to_serial_number VARCHAR2(30),
187 picked_quantity NUMBER,
188 picked_quantity2 NUMBER,
189 received_quantity NUMBER,
190 received_quantity2 NUMBER,
191 source_line_set_id NUMBER,
192 batch_id NUMBER,
193 ROWID VARCHAR2(4000),
194 transaction_id NUMBER, -----2803570
195 VENDOR_ID NUMBER,
196 SHIP_FROM_SITE_ID NUMBER,
197 LINE_DIRECTION VARCHAR2(30),
198 PARTY_ID NUMBER,
199 ROUTING_REQ_ID NUMBER,
200 SHIPPING_CONTROL VARCHAR2(30),
201 SOURCE_BLANKET_REFERENCE_ID NUMBER,
202 SOURCE_BLANKET_REFERENCE_NUM NUMBER,
203 PO_SHIPMENT_LINE_ID NUMBER,
204 PO_SHIPMENT_LINE_NUMBER NUMBER,
205 RETURNED_QUANTITY NUMBER,
206 RETURNED_QUANTITY2 NUMBER,
207 RCV_SHIPMENT_LINE_ID NUMBER,
208 SOURCE_LINE_TYPE_CODE VARCHAR2(30),
209 SUPPLIER_ITEM_NUMBER VARCHAR2(50),
210 IGNORE_FOR_PLANNING VARCHAR2(1),
211 EARLIEST_PICKUP_DATE DATE,
212 LATEST_PICKUP_DATE DATE,
213 EARLIEST_DROPOFF_DATE DATE,
214 LATEST_DROPOFF_DATE DATE,
215 REQUEST_DATE_TYPE_CODE VARCHAR2(30),
216 tp_delivery_detail_id NUMBER,
217 source_document_type_id NUMBER,
218 -- J: W/V Changes
219 unit_weight NUMBER,
220 unit_volume NUMBER,
221 filled_volume NUMBER,
222 wv_frozen_flag VARCHAR2(1),
223 mode_of_transport VARCHAR2(30),
224 service_level VARCHAR2(30),
225 po_revision_number NUMBER,
226 release_revision_number NUMBER,
227 consignee_flag VARCHAR2(1) --RTV changes
228 );
229
230 TYPE Delivery_Assignments_Rec_Type IS RECORD(
231 delivery_assignment_id NUMBER,
232 delivery_id NUMBER,
233 parent_delivery_id NUMBER,
234 delivery_detail_id NUMBER,
235 parent_delivery_detail_id NUMBER,
236 creation_date DATE,
237 created_by NUMBER,
238 last_update_date DATE,
239 last_updated_by NUMBER,
240 last_update_login NUMBER,
241 program_application_id NUMBER,
242 program_id NUMBER,
243 program_update_date DATE,
244 request_id NUMBER,
245 active_flag VARCHAR2(1),
246 received_quantity NUMBER,
247 received_quantity2 NUMBER,
248 source_line_set_id NUMBER,
249 TYPE VARCHAR2(30)
250 );
251
252 /* Do not use Delivery_Details_Attr_Tbl_Type, use the table defined in
253 wsh_glbl_var_strct_grp
254 */
255 TYPE Delivery_Details_Attr_Tbl_Type is TABLE of Delivery_Details_Rec_Type index by binary_integer;
256
257 --========================================================================
258
259 --==============DECLARE PROCEDURES =======================================
260 --
261
262 --
263 -- Procedure: create_new_detail_from_old
264 -- Parameters: A Delivery Detail RecType with values only in the columns to be changed,
265 -- Old delivery detail id from which the new to be copied
266 -- Row_id out
267 -- Delivery_Detail_id out
268 -- Return_Status out
269 -- Description: This procedure will create a new delivery detail.
270 -- It copies values of unchanged attributes from the old delivery detail
271 -- It will return the delivery_detail_id
272 -- Prereq: Use initialize_detail() to initialize the record type
273 -- Then (optionally) modify whichever attributes you need to in that record
274 -- And then pass it along with the old delivery delivery detail id to be copied from
275 PROCEDURE create_new_detail_from_old(
276 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
277 p_delivery_detail_id IN NUMBER,
278 x_row_id OUT NOCOPY VARCHAR2,
279 x_delivery_detail_id OUT NOCOPY NUMBER,
280 x_return_status OUT NOCOPY VARCHAR2);
281
282 -- This API has been created for
283 -- BULK OPERATION in Auto Packing
284 PROCEDURE create_dd_from_old_bulk(
285 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
289 x_return_status OUT NOCOPY VARCHAR2
286 p_delivery_detail_id IN NUMBER,
287 p_num_of_rec IN NUMBER,
288 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
290 );
291
292 -- This API has been created for
293 -- BULK OPERATION in Auto Packing
294 -- used after call to create_delivery_details as well as
295 -- create_new_dd_from_old
296
297 PROCEDURE Create_Deliv_Assignment_bulk(
298 p_delivery_assignments_info IN Delivery_Assignments_Rec_TYPE,
299 p_num_of_rec IN NUMBER,
300 p_dd_id_tab IN WSH_UTIL_CORE.id_tab_type,
301 x_da_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
302 x_return_status OUT NOCOPY VARCHAR2
303 );
304
305
306 --
307 -- Procedure: Create_Delivery_Detail
308 -- Parameters: All Attributes of a Delivery Detail Record,
309 -- Row_id out
310 -- Delivery_Detail_id out
311 -- Return_Status out
312 -- Description: This procedure will create a delivery detail.
313 -- It will return to the use the delivery_detail_id
314 -- if not provided as a parameter.
315 --
316
317
318 -- added new parameter to use this API
319 PROCEDURE Create_Delivery_Details(
320 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
321 x_rowid OUT NOCOPY VARCHAR2,
322 x_delivery_detail_id OUT NOCOPY NUMBER,
323 x_return_status OUT NOCOPY VARCHAR2
324 );
325
326 PROCEDURE Create_Delivery_Details_Bulk(
327 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
328 p_num_of_rec IN NUMBER,
329 -- lpn conv
330 p_container_info_rec IN WSH_GLBL_VAR_STRCT_GRP.ContInfoRectype,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type
333 );
334
335 --
336 -- Procedure: Delete_Delivery_Detail
337 -- Parameters: All Attributes of a Delivery Detail Record
338 -- Description: This procedure will delete a delivery detail.
339 --
340
341 PROCEDURE Delete_Delivery_Details(
342 p_rowid IN VARCHAR2 := NULL,
343 p_delivery_detail_id IN NUMBER := NULL,
344 p_cancel_flag IN VARCHAR2 DEFAULT NULL,
345 x_return_status OUT NOCOPY VARCHAR2);
346
347
348 --
349 -- Procedure: Lock_Delivery_Details
350 -- Parameters: All Attributes of a Delivery Detail Record
351 -- Description: This procedure will lock a delivery detail
352 -- record. It is specifically designed for
353 -- use by the form.
354 --
355
356 PROCEDURE Lock_Delivery_Details(
357 p_rowid IN VARCHAR2,
358 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type);
359
360
361 --
362 -- Procedure: Update_Delivery_Line
363 -- Parameters: All Attributes of a Delivery Line Record
364 -- Description: This procedure will update attributes of
365 -- a delivery line.
366 --
367
368
369 PROCEDURE Update_Delivery_Details(
370 p_rowid IN VARCHAR2 := NULL,
371 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
372 x_return_status OUT NOCOPY VARCHAR2
373 );
374 --
375 -- Procedure: Create_Delivery_Assignments
376 -- Parameters: All Attributes of a Delivery Assignment Record,
377 -- Row_id out
378 -- Delivery_Assignment_id out
379 -- Return_Status out
380 -- Description: This procedure will create a delivery_assignment
381 -- It will return to the use the delivery_assignment_id
382 -- if not provided as a parameter.
383 --
384
385 PROCEDURE Create_Delivery_Assignments(
386 p_delivery_assignments_info IN Delivery_Assignments_Rec_TYPE,
387 x_rowid OUT NOCOPY VARCHAR2,
388 x_delivery_assignment_id OUT NOCOPY NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2
390 );
391 --
392 -- Procedure: Delete_Delivery_Assignments
393 -- Parameters: All Attributes of a Delivery Assignment Record,
394 -- Row_id out
395 -- Delivery_Assignment_id out
396 -- Return_Status out
397 -- Description: This procedure will delete a delivery assignment.
398 -- It will return to the use the delivery_assignment id
399 -- if not provided as a parameter.
400 --
401 -- OTM R12 : This procedure was reviewed during OTM R12 frontport
402 -- but not modified since it's not called from anywhere.
403 -- Procedure body should be modified properly when it will be
404 -- in use. Refer to TDD for the details of expected changes.
405
406
407 PROCEDURE Delete_Delivery_Assignments(
408 p_rowid IN VARCHAR2 := NULL,
409 p_delivery_assignment_id IN NUMBER := NULL,
410 x_return_status OUT NOCOPY VARCHAR2
411 );
412
413 --
414 -- Procedure: Update_Delivery_Assignments
415 -- Parameters:
416 -- Row_id in
417 -- Return_Status out
418 -- Description: This procedure will update a delivery assignment.
419 --
420 -- OTM R12 : This procedure was reviewed during OTM R12 frontport
421 -- but not modified since it's not called from anywhere.
422 -- Procedure body should be modified properly when it will be
423 -- in use. Refer to TDD for the details of expected changes.
424
425 PROCEDURE Update_Delivery_Assignments(
426 p_rowid IN VARCHAR2 := NULL,
427 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
428 x_return_status OUT NOCOPY VARCHAR2);
429 --
430 -- Procedure: Lock_Delivery_Assignments
431 -- Parameters: All Attributes of a Delivery Assignment Record,
432 -- Row_id in
433 -- Return_Status out
434 -- Description: This procedure will lock a delivery assignment.
435 --
436
437 PROCEDURE Lock_Delivery_Assignments(
438 p_rowid IN VARCHAR2,
439 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
440 x_return_status OUT NOCOPY VARCHAR2);
441
442 --
443 -- Procedure: Lock_Delivery_Details Wrapper
444 -- Parameters: A table of all attributes of a Delivery detail Record,
445 -- Caller in
446 -- Return_Status,Valid_index_id_tab out
447 -- Description: This procedure will lock multiple delivery details.
448
449 procedure Lock_Delivery_Details(
450 p_rec_attr_tab IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type,
451 p_caller IN VARCHAR2,
452 p_valid_index_tab IN wsh_util_core.id_tab_type,
453 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
454 x_return_status OUT NOCOPY VARCHAR2
455 );
456
457
458 /* ---------------------------------------------------------------------
459 Procedure: Lock_Detail_No_Compare
460
461 Parameters: Delivery_Detail Id DEFAULT NULL
462 Delivery Id DEFAULT NULL
463
464 Description: This procedure is used for obtaining locks of lines/lpns
465 using the delivery_detail_id or the delivery_id.
466 This is called by delivery detail's wrapper lock API when the p_caller is NOT WSHFSTRX.
467 It is also called by delivery's wrapper lock API when the
468 action is CONFIRM, AUTO-PACK or AUTO-PACK-MASTER.
469 This procedure does not compare the attributes. It just
470 does a SELECT using FOR UPDATE NOWAIT
471 Created: Harmonization Project. Patchset I
472 ----------------------------------------------------------------------- */
473
474 Procedure lock_detail_no_compare(
475 p_delivery_detail_id IN NUMBER DEFAULT NULL,
476 p_delivery_id IN NUMBER DEFAULT NULL);
477
478 -- Bug 3292364
479 -- Procedure: Table_To_Record
480 -- Parameters: x_delivery_detail_rec: A record of all attributes of a Delivery detail Record
481 -- p_delivery_detail_id : delivery_detail_id of the detail that is to be copied
482 -- Return_Status,
483 -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
484 -- and copy it to a record.
485
486
487 Procedure Table_To_Record(
488 p_delivery_detail_id IN NUMBER,
489 x_delivery_detail_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
490 x_return_status OUT NOCOPY VARCHAR2);
491
492
493 -- Locks wsh_delivery assignments based on delivery or delivery detail entered.
494 Procedure lock_wda_no_compare(
495 p_delivery_detail_id IN NUMBER DEFAULT NULL,
496 p_delivery_id IN NUMBER DEFAULT NULL);
497
498
499 END WSH_DELIVERY_DETAILS_PKG;