[Home] [Help]
PACKAGE: APPS.WSH_DELIVERY_DETAILS_PKG
Source
1 PACKAGE WSH_DELIVERY_DETAILS_PKG AS
2 /* $Header: WSHDDTHS.pls 120.4.12010000.1 2008/07/29 05:59:45 appldev 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';
16
17 --========================================================================
18
19 --==============DECLARE RECORD/TABLE STRUCTURES=======================================
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,
165 currency_code VARCHAR2(15),
166 freight_class_cat_id NUMBER,
167 commodity_code_cat_id NUMBER,
168 -- hverddin 26-jun-2000 start of OPM changes
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 );
228
229 TYPE Delivery_Assignments_Rec_Type IS RECORD(
230 delivery_assignment_id NUMBER,
231 delivery_id NUMBER,
232 parent_delivery_id NUMBER,
233 delivery_detail_id NUMBER,
234 parent_delivery_detail_id NUMBER,
235 creation_date DATE,
236 created_by NUMBER,
237 last_update_date DATE,
238 last_updated_by NUMBER,
239 last_update_login NUMBER,
240 program_application_id NUMBER,
241 program_id NUMBER,
242 program_update_date DATE,
243 request_id NUMBER,
244 active_flag VARCHAR2(1),
245 received_quantity NUMBER,
246 received_quantity2 NUMBER,
247 source_line_set_id NUMBER,
248 TYPE VARCHAR2(30)
249 );
250
251 /* Do not use Delivery_Details_Attr_Tbl_Type, use the table defined in
252 wsh_glbl_var_strct_grp
253 */
254 TYPE Delivery_Details_Attr_Tbl_Type is TABLE of Delivery_Details_Rec_Type index by binary_integer;
255
256 --========================================================================
257
258 --==============DECLARE PROCEDURES =======================================
259 --
260
261 --
262 -- Procedure: create_new_detail_from_old
263 -- Parameters: A Delivery Detail RecType with values only in the columns to be changed,
264 -- Old delivery detail id from which the new to be copied
265 -- Row_id out
266 -- Delivery_Detail_id out
267 -- Return_Status out
268 -- Description: This procedure will create a new delivery detail.
269 -- It copies values of unchanged attributes from the old delivery detail
270 -- It will return the delivery_detail_id
271 -- Prereq: Use initialize_detail() to initialize the record type
272 -- Then (optionally) modify whichever attributes you need to in that record
273 -- And then pass it along with the old delivery delivery detail id to be copied from
274 PROCEDURE create_new_detail_from_old(
275 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
276 p_delivery_detail_id IN NUMBER,
277 x_row_id OUT NOCOPY VARCHAR2,
278 x_delivery_detail_id OUT NOCOPY NUMBER,
279 x_return_status OUT NOCOPY VARCHAR2);
280
281 -- This API has been created for
282 -- BULK OPERATION in Auto Packing
283 PROCEDURE create_dd_from_old_bulk(
284 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
285 p_delivery_detail_id IN NUMBER,
286 p_num_of_rec IN NUMBER,
287 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
288 x_return_status OUT NOCOPY VARCHAR2
289 );
290
291 -- This API has been created for
292 -- BULK OPERATION in Auto Packing
293 -- used after call to create_delivery_details as well as
294 -- create_new_dd_from_old
295
296 PROCEDURE Create_Deliv_Assignment_bulk(
297 p_delivery_assignments_info IN Delivery_Assignments_Rec_TYPE,
298 p_num_of_rec IN NUMBER,
299 p_dd_id_tab IN WSH_UTIL_CORE.id_tab_type,
300 x_da_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
301 x_return_status OUT NOCOPY VARCHAR2
302 );
303
304
305 --
306 -- Procedure: Create_Delivery_Detail
307 -- Parameters: All Attributes of a Delivery Detail Record,
308 -- Row_id out
309 -- Delivery_Detail_id out
310 -- Return_Status out
311 -- Description: This procedure will create a delivery detail.
312 -- It will return to the use the delivery_detail_id
313 -- if not provided as a parameter.
314 --
315
316
317 -- added new parameter to use this API
318 PROCEDURE Create_Delivery_Details(
319 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
320 x_rowid OUT NOCOPY VARCHAR2,
321 x_delivery_detail_id OUT NOCOPY NUMBER,
322 x_return_status OUT NOCOPY VARCHAR2
323 );
324
325 PROCEDURE Create_Delivery_Details_Bulk(
326 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
327 p_num_of_rec IN NUMBER,
328 -- lpn conv
329 p_container_info_rec IN WSH_GLBL_VAR_STRCT_GRP.ContInfoRectype,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type
332 );
333
334 --
335 -- Procedure: Delete_Delivery_Detail
336 -- Parameters: All Attributes of a Delivery Detail Record
337 -- Description: This procedure will delete a delivery detail.
338 --
339
340 PROCEDURE Delete_Delivery_Details(
341 p_rowid IN VARCHAR2 := NULL,
342 p_delivery_detail_id IN NUMBER := NULL,
343 p_cancel_flag IN VARCHAR2 DEFAULT NULL,
344 x_return_status OUT NOCOPY VARCHAR2);
345
346
347 --
348 -- Procedure: Lock_Delivery_Details
349 -- Parameters: All Attributes of a Delivery Detail Record
350 -- Description: This procedure will lock a delivery detail
351 -- record. It is specifically designed for
352 -- use by the form.
356 p_rowid IN VARCHAR2,
353 --
354
355 PROCEDURE Lock_Delivery_Details(
357 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type);
358
359
360 --
361 -- Procedure: Update_Delivery_Line
362 -- Parameters: All Attributes of a Delivery Line Record
363 -- Description: This procedure will update attributes of
364 -- a delivery line.
365 --
366
367
368 PROCEDURE Update_Delivery_Details(
369 p_rowid IN VARCHAR2 := NULL,
370 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
371 x_return_status OUT NOCOPY VARCHAR2
372 );
373 --
374 -- Procedure: Create_Delivery_Assignments
375 -- Parameters: All Attributes of a Delivery Assignment Record,
376 -- Row_id out
377 -- Delivery_Assignment_id out
378 -- Return_Status out
379 -- Description: This procedure will create a delivery_assignment
380 -- It will return to the use the delivery_assignment_id
381 -- if not provided as a parameter.
382 --
383
384 PROCEDURE Create_Delivery_Assignments(
385 p_delivery_assignments_info IN Delivery_Assignments_Rec_TYPE,
386 x_rowid OUT NOCOPY VARCHAR2,
387 x_delivery_assignment_id OUT NOCOPY NUMBER,
388 x_return_status OUT NOCOPY VARCHAR2
389 );
390 --
391 -- Procedure: Delete_Delivery_Assignments
392 -- Parameters: All Attributes of a Delivery Assignment Record,
393 -- Row_id out
394 -- Delivery_Assignment_id out
395 -- Return_Status out
396 -- Description: This procedure will delete a delivery assignment.
397 -- It will return to the use the delivery_assignment id
398 -- if not provided as a parameter.
399 --
400 -- OTM R12 : This procedure was reviewed during OTM R12 frontport
401 -- but not modified since it's not called from anywhere.
402 -- Procedure body should be modified properly when it will be
403 -- in use. Refer to TDD for the details of expected changes.
404
405
406 PROCEDURE Delete_Delivery_Assignments(
407 p_rowid IN VARCHAR2 := NULL,
408 p_delivery_assignment_id IN NUMBER := NULL,
409 x_return_status OUT NOCOPY VARCHAR2
410 );
411
412 --
413 -- Procedure: Update_Delivery_Assignments
414 -- Parameters:
415 -- Row_id in
416 -- Return_Status out
417 -- Description: This procedure will update a delivery assignment.
418 --
419 -- OTM R12 : This procedure was reviewed during OTM R12 frontport
420 -- but not modified since it's not called from anywhere.
421 -- Procedure body should be modified properly when it will be
422 -- in use. Refer to TDD for the details of expected changes.
423
424 PROCEDURE Update_Delivery_Assignments(
425 p_rowid IN VARCHAR2 := NULL,
426 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
427 x_return_status OUT NOCOPY VARCHAR2);
428 --
429 -- Procedure: Lock_Delivery_Assignments
430 -- Parameters: All Attributes of a Delivery Assignment Record,
431 -- Row_id in
432 -- Return_Status out
433 -- Description: This procedure will lock a delivery assignment.
434 --
435
436 PROCEDURE Lock_Delivery_Assignments(
437 p_rowid IN VARCHAR2,
438 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
439 x_return_status OUT NOCOPY VARCHAR2);
440
441 --
442 -- Procedure: Lock_Delivery_Details Wrapper
443 -- Parameters: A table of all attributes of a Delivery detail Record,
444 -- Caller in
445 -- Return_Status,Valid_index_id_tab out
446 -- Description: This procedure will lock multiple delivery details.
447
448 procedure Lock_Delivery_Details(
449 p_rec_attr_tab IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type,
450 p_caller IN VARCHAR2,
451 p_valid_index_tab IN wsh_util_core.id_tab_type,
452 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
453 x_return_status OUT NOCOPY VARCHAR2
454 );
455
456
457 /* ---------------------------------------------------------------------
458 Procedure: Lock_Detail_No_Compare
459
460 Parameters: Delivery_Detail Id DEFAULT NULL
461 Delivery Id DEFAULT NULL
462
463 Description: This procedure is used for obtaining locks of lines/lpns
464 using the delivery_detail_id or the delivery_id.
465 This is called by delivery detail's wrapper lock API when the p_caller is NOT WSHFSTRX.
466 It is also called by delivery's wrapper lock API when the
467 action is CONFIRM, AUTO-PACK or AUTO-PACK-MASTER.
468 This procedure does not compare the attributes. It just
469 does a SELECT using FOR UPDATE NOWAIT
470 Created: Harmonization Project. Patchset I
471 ----------------------------------------------------------------------- */
472
473 Procedure lock_detail_no_compare(
474 p_delivery_detail_id IN NUMBER DEFAULT NULL,
475 p_delivery_id IN NUMBER DEFAULT NULL);
476
477 -- Bug 3292364
478 -- Procedure: Table_To_Record
479 -- Parameters: x_delivery_detail_rec: A record of all attributes of a Delivery detail Record
480 -- p_delivery_detail_id : delivery_detail_id of the detail that is to be copied
481 -- Return_Status,
482 -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
483 -- and copy it to a record.
484
485
486 Procedure Table_To_Record(
487 p_delivery_detail_id IN NUMBER,
488 x_delivery_detail_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
489 x_return_status OUT NOCOPY VARCHAR2);
490
491
492 -- Locks wsh_delivery assignments based on delivery or delivery detail entered.
493 Procedure lock_wda_no_compare(
494 p_delivery_detail_id IN NUMBER DEFAULT NULL,
495 p_delivery_id IN NUMBER DEFAULT NULL);
496
497
498 END WSH_DELIVERY_DETAILS_PKG;