1 PACKAGE wsh_container_actions AUTHID CURRENT_USER as
2 /* $Header: WSHCMACS.pls 120.3.12020000.2 2012/07/04 10:35:55 suppal ship $ */
3
4
5 --lpn conv
6
7 /*
8 procedure Create_Multiple_Cont_name will be deleted, once WMS
9 provides functionality for container name generation.
10 */
11 PROCEDURE Create_Multiple_Cont_name (
12 p_cont_name IN VARCHAR2,
13 p_cont_name_pre IN VARCHAR2,
14 p_cont_name_suf IN VARCHAR2,
15 p_cont_name_num IN NUMBER,
16 p_cont_name_dig IN NUMBER,
17 p_quantity IN NUMBER,
18 x_cont_names OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.v50_Tbl_Type,
19 x_return_status OUT NOCOPY VARCHAR2);
20
21
22 /* ------------------------------------------------------------------
23
24 PROCEDURE default_container_attr
25 This procedure is called from Create_Cont_Instance_Multi to default the
26 neccessary atributes for the container to be created.
27
28 ------------------------------------------------------------------- */
29
30 PROCEDURE default_container_attr (
31 P_container_rec IN OUT NOCOPY
32 wsh_glbl_var_strct_grp.Delivery_Details_Rec_Type,
33 p_additional_cont_attr IN wsh_glbl_var_strct_grp.LPNRecordType,
34 p_caller IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2);
36
37 /*
38 -----------------------------------------------------------------------------
39 RECORD TYPE : line_cont_info
40 DESCRIPTION : This record type stores some of the delivery detail attributes
41 of the delivery details that need to be autopacked. If a
42 number of lines are selected for autopack, the line attributes
43 are retrieved and stored in a table of line_cont_info records.
44 ------------------------------------------------------------------------------
45 */
46
47 TYPE line_cont_info IS RECORD (
48 group_id NUMBER,
49 organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
50 fill_pc_basis WSH_SHIPPING_PARAMETERS.percent_fill_basis_flag%TYPE,
51 process_flag VARCHAR2(1),
52 inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
53 indivisible_flag MTL_SYSTEM_ITEMS.indivisible_flag%TYPE,
57 shp_qty2 WSH_DELIVERY_DETAILS.shipped_quantity%TYPE,
54 delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
55 shp_qty WSH_DELIVERY_DETAILS.shipped_quantity%TYPE,
56 req_qty WSH_DELIVERY_DETAILS.requested_quantity%TYPE,
58 req_qty2 WSH_DELIVERY_DETAILS.requested_quantity%TYPE,
59 req_qty_uom WSH_DELIVERY_DETAILS.requested_quantity_uom%TYPE,
60 detail_cont_item_id WSH_DELIVERY_DETAILS.detail_container_item_id%TYPE,
61 master_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
62 gross_weight WSH_DELIVERY_DETAILS.gross_weight%TYPE,
63 net_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
64 converted_wt WSH_DELIVERY_DETAILS.net_weight%TYPE,
65 weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
66 volume WSH_DELIVERY_DETAILS.volume%TYPE,
67 converted_vol WSH_DELIVERY_DETAILS.volume%TYPE,
68 volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
69 det_cont_qty WSH_DELIVERY_DETAILS.requested_quantity%TYPE,
70 mast_max_load_qty WSH_CONTAINER_ITEMS.max_load_quantity%TYPE,
71 source_line_id WSH_DELIVERY_DETAILS.source_line_id%TYPE,
72 source_code WSH_DELIVERY_DETAILS.source_code%TYPE,
73 preferred_container WSH_CONTAINER_ITEMS.container_item_id%TYPE,
74 max_load_qty NUMBER,
75 cont_wt WSH_DELIVERY_DETAILS.net_weight%TYPE,
76 delivery_id wsh_delivery_assignments_v.DELIVERY_ID%TYPE, -- anxsharm
77 -- HW OPMCONV - Change variable name from opm_lot_ind to lot_divisible_flag
78 lot_divisible_flag MTL_SYSTEM_ITEMS.lot_divisible_flag%TYPE,
79 cont_vol WSH_DELIVERY_DETAILS.volume%TYPE);
80
81 -- table of line_cont_info records that stores delivery line information
82 -- of the lines that are selected for autopacking
83
84 TYPE line_cont_info_tab IS TABLE OF line_cont_info INDEX BY BINARY_INTEGER;
85
86
87 TYPE cont_inst_rec IS RECORD (
88 cont_name WSH_DELIVERY_DETAILS.container_name%TYPE,
89 cont_instance_id NUMBER,
90 delivery_detail_id NUMBER,
91 delivery_assignment_id NUMBER, --added for WDA
92 gross_weight NUMBER,
93 net_weight NUMBER,
94 volume NUMBER,
95 cont_fill_pc NUMBER,
96 row_id VARCHAR2(20)
97 );
98
99 -- table of cont_info records that stores delivery line information
100 -- of the containers that are created for autopacking
101
102 TYPE cont_inst_tab IS TABLE OF cont_inst_rec INDEX BY BINARY_INTEGER;
103
104
105 /*
106 -----------------------------------------------------------------------------
107 RECORD TYPE : empty_cont_info
108 DESCRIPTION : This record type stores some of the container attributes
109 of the containers that remain partially empty during the
110 packing of a delivery detail line. If a container still has
111 some space remaining to be packed, the container item id,
112 container instance id and percent empty is stored in a
113 empty_cont_info record.
114 ------------------------------------------------------------------------------
115 */
116
117 TYPE empty_cont_info IS RECORD (
118 container_instance_id NUMBER,
119 container_item_id NUMBER);
120
121 -- table of empty_cont_info records that stores all the containers that
122 -- remain partially empty during autopacking of multiple lines.
123
124 TYPE empty_cont_info_tab IS TABLE OF empty_cont_info INDEX BY BINARY_INTEGER;
125
126 TYPE cont_info IS RECORD (
127 group_id NUMBER,
128 organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
129 fill_pc_basis WSH_SHIPPING_PARAMETERS.percent_fill_basis_flag%TYPE,
130 inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
131 delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
132 shp_qty WSH_DELIVERY_DETAILS.shipped_quantity%TYPE,
133 req_qty WSH_DELIVERY_DETAILS.requested_quantity%TYPE,
134 gross_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
135 converted_wt WSH_DELIVERY_DETAILS.net_weight%TYPE,
136 weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
137 volume WSH_DELIVERY_DETAILS.volume%TYPE,
138 converted_vol WSH_DELIVERY_DETAILS.volume%TYPE,
139 volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
140 preferred_container WSH_CONTAINER_ITEMS.container_item_id%TYPE,
141 max_load_qty NUMBER,
142 cont_wt WSH_DELIVERY_DETAILS.net_weight%TYPE,
143 cont_vol WSH_DELIVERY_DETAILS.volume%TYPE);
144
145 TYPE cont_info_tab IS TABLE OF cont_info INDEX BY BINARY_INTEGER;
146
147
148
149 /*
150 -----------------------------------------------------------------------------
151 PROCEDURE : Create_Container_Instance
152 PARAMETERS : p_cont_name - name for the container
153 p_cont_item_id - container item id (containers inv item id)
154 x_cont_instance_id - delivery_detail_id for new container - if
155 null then it will return a new id
156 p_par_detail_id - the parent detail id (parent container)
157 p_organization_id - organization id
158 p_container_type_code - the container type code of container
159 x_row_id - rowid of the new container record
160 x_return_status - return status of API
161 DESCRIPTION : This procedure creates a new container and defaults some of the
162 container item attributes. The container item id of the
163 container that is being created is required. If the container
164 name is not specified it defaults the name to be equal to the
165 delivery detail id.
166 ------------------------------------------------------------------------------
167 */
168
172 p_cont_item_id IN NUMBER,
169
170 PROCEDURE Create_Container_Instance (
171 x_cont_name IN OUT NOCOPY VARCHAR2,
173 x_cont_instance_id IN OUT NOCOPY NUMBER,
174 p_par_detail_id IN NUMBER,
175 p_organization_id IN NUMBER,
176 p_container_type_code IN VARCHAR2,
177 x_row_id OUT NOCOPY VARCHAR2,
178 x_return_status OUT NOCOPY VARCHAR2);
179
180 -- anxsharm
181 -- This API has been called for
182 -- Creating Multiple Containers of single type
183 -- during Auto Pack
184
185 PROCEDURE Create_Cont_Instance_Multi (
186 x_cont_name IN OUT NOCOPY VARCHAR2,
187 p_cont_item_id IN NUMBER,
188 x_cont_instance_id IN OUT NOCOPY NUMBER,
189 p_par_detail_id IN NUMBER,
190 p_organization_id IN NUMBER,
191 p_container_type_code IN VARCHAR2,
192 p_num_of_containers IN NUMBER,
193 x_row_id OUT NOCOPY VARCHAR2,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_cont_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
196 -- J: W/V Changes
197 x_unit_weight OUT NOCOPY NUMBER,
198 x_unit_volume OUT NOCOPY NUMBER,
199 x_weight_uom_code OUT NOCOPY VARCHAR2,
200 x_volume_uom_code OUT NOCOPY VARCHAR2,
201 p_lpn_id IN NUMBER DEFAULT NULL,
202 p_ignore_for_planning IN VARCHAR2 DEFAULT 'N',
203 p_caller IN VARCHAR2 DEFAULT 'WSH'
204 );
205
206
207 /*
208 -----------------------------------------------------------------------------
209 PROCEDURE : Create_Multiple_Containers
210 PARAMETERS : p_cont_item_id - container item id (containers inv item id)
211 p_organization_id - organization id
212 p_container_type_code - the container type code of container
213 p_cont_name - name for the container if qty is 1 (mainly used
214 by public APIs)
215 p_cont_name_pre - prefix for container name
216 p_cont_name_suf - suffix for container name
217 p_cont_name_num - starting number for number part of container
218 name
219 p_cont_name_dig - number of digits to use for the number part
220 of the container name
221 p_quantity - number of containers to create
222 x_cont_instance_tab - table of delivery_detail_ids for new
223 containers - if null then it will return a
224 table with new ids
225 x_return_status - return status of API
226 DESCRIPTION : This procedure creates a new container and defaults some of the
227 container item attributes. The container item id of the
228 container that is being created is required. If the container
229 name is not specified it defaults the name to be equal to the
230 delivery detail id.
231 ------------------------------------------------------------------------------
232 */
233
234
235 PROCEDURE Create_Multiple_Containers (
236 p_cont_item_id IN NUMBER,
237 p_organization_id IN NUMBER,
238 p_container_type_code IN VARCHAR2,
239 p_cont_name IN VARCHAR2,
240 p_cont_name_pre IN VARCHAR2,
241 p_cont_name_suf IN VARCHAR2,
242 p_cont_name_num IN NUMBER,
243 p_cont_name_dig IN NUMBER,
244 p_quantity IN NUMBER,
245 x_cont_instance_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
246 x_return_status OUT NOCOPY VARCHAR2);
247
248
249 /*
250 -----------------------------------------------------------------------------
251 PROCEDURE : Assign_Detail
252 PARAMETERS : p_container_instance_id - container instance id of container
253 p_del_detail_tab - table of delivery detail ids
254 x_pack_status - status of container after packing the lines
255 into it : underpacked or overpacked
256 x_return_status - return status of API
257 DESCRIPTION : This procedure assigns a number of lines to the specified
258 container instance and returns a pack status of underpacked
259 or overpacked or success.
260 ------------------------------------------------------------------------------
261 */
262
263
264
265 PROCEDURE Assign_Detail(
266 p_container_instance_id IN NUMBER,
267 p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
268 x_pack_status OUT NOCOPY VARCHAR2,
269 x_return_status OUT NOCOPY VARCHAR2,
270 p_check_credit_holds IN BOOLEAN DEFAULT TRUE );
271
272
273 /*
274 -----------------------------------------------------------------------------
275 PROCEDURE : Unassign_Detail
276 PARAMETERS : p_container_instance_id - container instance id of container
277 p_delivery_id - delivery id from which detail needs to be
278 unassigned
279 p_del_detail_tab - table of delivery detail ids
280 p_cont_unassign - flag to determine whether to unassign from
281 container or not.
282 p_del_unassign - flag to determine whether to unassign from
283 delivery or not
284 x_pack_status - status of container after packing the lines
285 into it : underpacked or overpacked
286 x_return_status - return status of API
287 DESCRIPTION : This procedure unassigns a number of lines from the specified
288 container instance or delivery and returns a pack status of
289 underpacked or overpacked or success. The unassigning is
290 determined using the two unassign flags or by specific ids.
291 ------------------------------------------------------------------------------
292 */
293
294
295
296 PROCEDURE Unassign_Detail(
297 p_container_instance_id IN NUMBER DEFAULT NULL,
298 p_delivery_id IN NUMBER DEFAULT NULL,
299 p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
300 p_cont_unassign IN VARCHAR2,
301 p_del_unassign IN VARCHAR2,
302 x_pack_status OUT NOCOPY VARCHAR2,
306
303 x_return_status OUT NOCOPY VARCHAR2,
304 p_action_prms IN wsh_glbl_var_strct_grp.dd_action_parameters_rec_type
305 );
307 /*
308 -----------------------------------------------------------------------------
309 PROCEDURE : Unassign_Detail
310 PARAMETERS : p_container_instance_id - container instance id of container
311 p_delivery_id - delivery id from which detail needs to be
312 unassigned
313 p_del_detail_tab - table of delivery detail ids
314 p_cont_unassign - flag to determine whether to unassign from
315 container or not.
316 p_del_unassign - flag to determine whether to unassign from
317 delivery or not
318 x_pack_status - status of container after packing the lines
319 into it : underpacked or overpacked
320 x_return_status - return status of API
321 DESCRIPTION : This procedure is for backward compatibility only. Do not use this.
322 ------------------------------------------------------------------------------
323 */
324
325 PROCEDURE Unassign_Detail(
326 p_container_instance_id IN NUMBER DEFAULT NULL,
327 p_delivery_id IN NUMBER DEFAULT NULL,
328 p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
329 p_cont_unassign IN VARCHAR2,
330 p_del_unassign IN VARCHAR2,
331 x_pack_status OUT NOCOPY VARCHAR2,
332 x_return_status OUT NOCOPY VARCHAR2);
333 /*
334 -----------------------------------------------------------------------------
335 PROCEDURE : Assign_To_Delivery
336 PARAMETERS : p_container_instance_id - container instance id of container
337 p_delivery_id - delivery id
338 x_return_status - return status of API
339 DESCRIPTION : This procedure checks to see if a container can be assigned to
340 the specified delivery and returns a success or failure.
341 ------------------------------------------------------------------------------
342 */
343
344 PROCEDURE Assign_To_Delivery(
345 p_container_instance_id IN NUMBER,
346 p_delivery_id IN NUMBER,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_dlvy_has_lines IN OUT NOCOPY VARCHAR2, -- J-IB-NPARIKH
349 x_dlvy_freight_terms_code IN OUT NOCOPY VARCHAR2 -- J-IB-NPARIKH
350 );
351
352 -------------------------------------------------------------------
353 -- This procedure is only for backward compatibility. No one should call
354 -- this procedure.
355 -------------------------------------------------------------------
356
357 PROCEDURE Assign_To_Delivery(
358 p_container_instance_id IN NUMBER,
359 p_delivery_id IN NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2
361 ) ;
362
363
364 /*
365 -----------------------------------------------------------------------------
366 PROCEDURE : Create_Delivery_Assignment
367 PARAMETERS : p_container_instance_id - container instance id of container
368 p_delivery_id - delivery id
369 x_return_status - return status of API.
370 DESCRIPTION : This procedure assigns a container to the specified delivery.
371 ------------------------------------------------------------------------------
372 */
373
374 PROCEDURE Create_Delivery_Assignment (
375 p_container_instance_id IN NUMBER,
376 p_delivery_id IN NUMBER,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_dlvy_has_lines IN OUT NOCOPY VARCHAR2, -- J-IB-NPARIKH
379 x_dlvy_freight_terms_code IN OUT NOCOPY VARCHAR2 -- J-IB-NPARIKH
380 );
381
382 -------------------------------------------------------------------
383 -- This procedure is only for backward compatibility. No one should call
384 -- this procedure.
385 -------------------------------------------------------------------
386 PROCEDURE Create_Delivery_Assignment (
387 p_container_instance_id IN NUMBER,
388 p_delivery_id IN NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2);
390
391
392 /*
393 -----------------------------------------------------------------------------
394 PROCEDURE : Unassign_Delivery
395 PARAMETERS : p_container_instance_id - container instance id of container
396 p_delivery_id - delivery id
397 x_return_status - return status of API
398 DESCRIPTION : This procedure checks unassigns a container from the specified
399 delivery and returns a success or failure.
400 ------------------------------------------------------------------------------
401 */
402
403 PROCEDURE Unassign_Delivery(
404 p_container_instance_id IN NUMBER,
405 p_delivery_id IN NUMBER,
406 x_return_status OUT NOCOPY VARCHAR2);
407
408
409 /*
410 -----------------------------------------------------------------------------
411 PROCEDURE : Assign_To_Container
412 PARAMETERS : p_det_cont_inst_id - container instance id of child container
413 p_par_cont_inst_id - container instance id of parent container
414 x_return_status - return status of API
415 DESCRIPTION : This procedure checks to see if a container can be assigned to
416 a specified parent container and returns a success or failure.
417 ------------------------------------------------------------------------------
418 */
419
420
421 PROCEDURE Assign_To_Container(
422 p_det_cont_inst_id IN NUMBER,
423 p_par_cont_inst_id IN NUMBER,
424 x_return_status OUT NOCOPY VARCHAR2);
425
426 /*
427 -----------------------------------------------------------------------------
428 PROCEDURE : Auto_Pack_Lines
429 PARAMETERS : p_group_id_tab_id - table of group ids for lines that need to
430 be autopacked.
434 x_cont_instance_tab - table of container instance ids that were
431 p_del_detail_tab - table of delivery detail ids
432 p_pack_cont_flag - 'Y' or 'N' to determine whether to try and
433 autopack the detail containers into master containers.
435 created during the autopacking.
436 x_return_status - return status of API.
437 DESCRIPTION : This procedure takes the number of lines and groups them by
438 common grouping attributes - similar to grouping attributes of
439 delivery. If a group id table is specified it uses the
440 group ids in the table to decided which lines can be grouped
441 into the same container. If a group id table is not specified,
442 it creates the group id table before autopacking. It creates
443 the required number and type of containers per line and keeps
444 track of all partially filled containers in the empty
445 containers table. Before creating new container instances, it
446 searches for available space using the empty container table
447 and after filling up a container, it creates a new one if
448 there are no empty containers of the same type.
449 ------------------------------------------------------------------------------
450 */
451
452
453 PROCEDURE Auto_Pack_Lines (
454 p_group_id_tab IN WSH_UTIL_CORE.id_tab_type,
455 p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
456 p_pack_cont_flag IN VARCHAR2,
457 x_cont_instance_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
458 x_return_status OUT NOCOPY VARCHAR2);
459
460
461 /*
462 -----------------------------------------------------------------------------
463 PROCEDURE : Auto_Pack_Delivery
464 PARAMETERS : p_delivery_tab - table of delivery ids that need to be
465 autopacked.
466 p_pack_cont_flag - 'Y' or 'N' to determine whether to try and
467 autopack the detail containers into master containers.
468 x_cont_instance_tab - table of container instance ids that were
469 created during the autopacking.
470 x_return_status - return status of API.
471 DESCRIPTION : This procedure takes a table of deliveries that need to be
472 autopacked and checks for all unpacked lines in each of the
473 deliveries. After fetching all unpacked lines in each delivery,
474 it calls the Auto_Pack_Lines with the table of unpacked lines.
475 After autopacking the lines, it recalculates the weight and
476 volume of the delivery.
477 ------------------------------------------------------------------------------
478 */
479
480
481 PROCEDURE Auto_Pack_Delivery (
482 p_delivery_tab IN WSH_UTIL_CORE.id_tab_type,
483 p_pack_cont_flag IN VARCHAR2,
484 x_cont_instance_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
485 x_return_status OUT NOCOPY VARCHAR2);
486
487
488 /*
489 -----------------------------------------------------------------------------
490 PROCEDURE : Pack_Multi
491 PARAMETERS : p_cont_tab - table of container instance ids that are being
492 packed.
493 p_del_detail_tab - table of unpacked delivery detail ids.
494 p_pack_mode - indicates whether containers are packed in
495 equal/proportional mode ('E') or in full/sequential mode ('F')
496 p_split_pc - the percentage by which each line is going to be
497 split in the case of equal packing mode.
498 x_pack_status - the packed status of containers after the multi
499 pack is performed - indicates whether any underpacked or
500 overpacked containers.
501 x_return_status - return status of API.
502 DESCRIPTION : This procedure takes the specified delivery detail ids and
503 packs them into the selected containers in either the full mode
504 or equal mode. In the full mode, it packs the first container
505 fully before packing the next. In the equal mode, all lines
506 are split equally between all the containers and packed
507 equally between them.
508 ------------------------------------------------------------------------------
509 */
510
511
512 PROCEDURE Pack_Multi (
513 p_cont_tab IN WSH_UTIL_CORE.id_tab_type,
514 p_del_detail_tab IN WSH_UTIL_CORE.id_tab_type,
515 p_pack_mode IN VARCHAR2,
516 p_split_pc IN NUMBER,
517 x_pack_status OUT NOCOPY VARCHAR2,
518 x_return_status OUT NOCOPY VARCHAR2);
519
520
521 /*
522 -----------------------------------------------------------------------------
523 PROCEDURE : Update_Shipped_Qty
524 PARAMETERS : p_delivery_detail_id - delivery detail id of the original line
525 that was split
526 p_split_detail_id - delivery detail id of the newly created
527 split line
528 p_split_qty - quantity used to split original delivery line
529 x_return_status - return status of API
530 DESCRIPTION : This procedure updates the shipped quantities of the original
531 delivery line that was split and the new line that was created
532 due to the split. The shipped quantity of the original line is
533 decremented by split qty and that of the new line is increased
534 to be equal to the split qty. The updating is done only if the
535 original shipped quantity is not null.
536 ------------------------------------------------------------------------------
537 */
538
539 PROCEDURE Update_Shipped_Qty(
540 p_delivery_detail_id IN NUMBER,
541 p_split_detail_id IN NUMBER,
542 p_split_qty IN NUMBER,
543 x_return_status OUT NOCOPY VARCHAR2);
544
545 /*
546 -----------------------------------------------------------------------------
547 PROCEDURE : Auto_Pack_Conts
548 PARAMETERS : p_group_id_tab_id - table of group ids for containers that need
549 to be autopacked.
550 p_cont_info_tab - table of detail containers created during the
551 autopack process consisting of container instances, master
552 container item ids and percentage empty.
556 created during the autopacking.
553 p_cont_detail_tab - table of container delivery detail ids that
554 were created during the autopacking of lines.
555 x_cont_instance_id - table of container instance ids that were
557 x_return_status - return status of API.
558 DESCRIPTION : This procedure takes the number of containers and groups them
559 by common grouping attributes - similar to grouping attributes
560 of delivery. If a group id table is specified it uses the
561 group ids in the table to decided which container can be
562 grouped into the same parent container. If a group id table is
563 not specified, it creates the group id table before autopacking
564 It creates the required number and type of parent containers
565 per detail container and keeps track of all partially filled
566 containers in the empty containers table. Before creating new
567 container instances, it searches for available space using the
568 empty container table and after filling up a container, it
569 creates a new one if there are no empty containers of the same
570 type. The difference between this API and the autopack lines is
571 that this API does not split containers if they don't fit
572 entirely into a parent container.
573 ------------------------------------------------------------------------------
574 */
575
576
577 PROCEDURE Auto_Pack_Conts (
578 p_group_id_tab IN WSH_UTIL_CORE.id_tab_type,
579 p_cont_info_tab IN wsh_container_actions.empty_cont_info_tab,
580 p_cont_detail_tab IN WSH_UTIL_CORE.id_tab_type,
581 x_cont_instance_tab IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
582 x_return_status OUT NOCOPY VARCHAR2);
583
584
585 /*
586 -----------------------------------------------------------------------------
587 PROCEDURE : Update_Cont_Attributes
588 PARAMETERS : p_delivery_detail_id - delivery detail id
589 p_delivery_id - delivery id if container assigned to delivery
590 p_container_instance_id - delivery detail id for the container
591 x_return_status - return status of API
592 DESCRIPTION : This procedure updates the grouping attribute columns of the
593 container with the grouping attribute values derived from the
594 delivery line that is input.
595 ------------------------------------------------------------------------------
596 */
597
598 PROCEDURE Update_Cont_Attributes (
599 p_delivery_detail_id IN NUMBER,
600 p_delivery_id IN NUMBER DEFAULT NULL,
601 p_container_instance_id IN NUMBER,
602 x_return_status OUT NOCOPY VARCHAR2);
603
604
605 /*
606 -----------------------------------------------------------------------------
607 PROCEDURE : Check_Cont_Attributes
608 PARAMETERS : p_container_instance_id - delivery detail id for the container
609 x_attr_flag - 'Y' or 'N' to determine if any of the grouping
610 attributes other than org id and ship from has been populated.
611 x_return_status - return status of API
612 DESCRIPTION : This procedure fetched the grouping attribute columns of the
613 container and checks to see if the columns are null or if they
614 are populated. If any of the values are not null, then the API
615 returns a x_attr_flag of 'Y' else it returns a 'N'.
616 ------------------------------------------------------------------------------
617 */
618
619 PROCEDURE Check_Cont_Attributes (
620 p_container_instance_id IN NUMBER,
621 x_attr_flag OUT NOCOPY VARCHAR2,
622 x_return_status OUT NOCOPY VARCHAR2);
623
624
625 /*
626 -----------------------------------------------------------------------------
627 PROCEDURE : Update_Cont_Hierarchy
628 PARAMETERS : p_delivery_detail_id - delivery detail id
629 p_delivery_id - delivery id if container assigned to delivery
630 p_container_instance_id - delivery detail id for the container
631 x_return_status - return status of API
632 DESCRIPTION : This procedure updates the grouping attribute columns of the
633 the entire container hierarchy for the specified container
634 with the grouping attribute values derived from the
635 delivery line that is input.
636 ------------------------------------------------------------------------------
637 */
638
639 PROCEDURE Update_Cont_Hierarchy (
640 p_del_detail_id IN NUMBER,
641 p_delivery_id IN NUMBER DEFAULT NULL,
642 p_container_instance_id IN NUMBER,
643 x_return_status OUT NOCOPY VARCHAR2);
644
645
646 /*
647 -----------------------------------------------------------------------------
648 PROCEDURE : Last_Assigned_Line
649 PARAMETERS : p_delivery_detail_id - delivery detail id
650 p_container_instance_id - delivery detail id for the container
651 x_last_line_flag - 'Y' or 'N' depending on whether it is the
652 last line in the container hierarchy or not.
653 x_return_status - return status of API
654 DESCRIPTION : This procedure checks to see if the delivery detail id is the
655 last assigned line in the container hierarchy for the input
656 container. If it is, x_last_line_flag is set to 'Y' else it is
657 set to 'N'.
658 ------------------------------------------------------------------------------
659 */
660
661 PROCEDURE Last_Assigned_Line (
662 p_del_detail_id IN NUMBER,
663 p_container_instance_id IN NUMBER,
664 x_last_line_flag OUT NOCOPY VARCHAR2,
665 x_return_status OUT NOCOPY VARCHAR2);
666
667 /*
668 -----------------------------------------------------------------------------
669 PROCEDURE : Delete_Containers
670 PARAMETERS : p_container_id - container instance to be deleted.
671 x_return_status - return status of API
672 DESCRIPTION : This procedure takes in a table of container instances and
673 deletes the containers. If the containers are not empty or
674 they are assigned to deliveries that are not open, they will
675 not be deleted. Also, if the containers are either assigned to
676 or container other containers packed into it, they will not be
677 deleted.
678 ------------------------------------------------------------------------------
679 */
680
681
682 PROCEDURE Delete_Containers (
683 p_container_id IN NUMBER,
684 x_return_status OUT NOCOPY VARCHAR2);
685
686
687 /*
688 -----------------------------------------------------------------------------
689 PROCEDURE : Update_Container
690 PARAMETERS : p_container_name - new container name that needs to be assigned
691 to the existing container.
692 p_container_instance_id - the delivery detail id for the
693 container that needs to be updated.
694 p_old_cont_name - exisiting container name for the container,
695 to be used only if container instance id in the input parameter
696 is null.
697 x_return_status - return status of API
698 DESCRIPTION : This procedure takes in a new container name and existing
699 container information like the delivery detail id and existing
700 container name that needs to be updated. The API checks to see
701 if the container that is being updated is assigned to a closed,
702 confirmed or in-transit delivery. If it is, no update is
703 allowed - if not, only the container name can be updated.
704 ------------------------------------------------------------------------------
705 */
706
707
708 PROCEDURE Update_Container (
709 p_container_name IN VARCHAR2,
710 p_container_instance_id IN NUMBER,
711 p_old_cont_name IN VARCHAR2,
712 x_return_status OUT NOCOPY VARCHAR2);
713
714
715 /*
716 -----------------------------------------------------------------------------
717 PROCEDURE : Get_Deliver_To_Location
718 PARAMETERS : p_container_id - Delivery detail id for the container
719 x_deliver_to_location_id - Deliver to location id depending
720 upon delivery details packed in container.
721 x_return_status - return status of API
722 DESCRIPTION : This procedure will return deliver to location id for
723 conatiner if all the delivery details in container have
724 same deliver to location id else this procedure will return
725 NULL deliver to location id
726 ------------------------------------------------------------------------------
727 */
728
729 PROCEDURE Get_Deliver_To_Location(
730 p_container_id IN NUMBER,
731 x_deliver_to_location_id OUT NOCOPY NUMBER,
732 x_return_status OUT NOCOPY VARCHAR2);
733
734 PROCEDURE unpack_inbound_delivery
735 (
736 p_delivery_id IN NUMBER,
737 x_return_status OUT NOCOPY VARCHAR2
738 ) ;
739
740 PROCEDURE pack_inbound_lines
741 (
742 p_lines_tbl IN WSH_UTIL_CORE.id_tab_type,
743 p_lpn_id IN NUMBER,
744 p_lpn_name IN VARCHAR2,
745 p_delivery_id IN NUMBER,
746 p_transactionType IN VARCHAR2 DEFAULT 'ASN',
747 x_return_status OUT NOCOPY VARCHAR2,
748 p_waybill_number IN VARCHAR2 DEFAULT NULL,
749 p_caller IN VARCHAR2 DEFAULT NULL
750 ) ;
751
752 --lpn conv
753 PROCEDURE Update_child_inv_info(p_container_id IN NUMBER,
754 P_locator_id IN NUMBER,
755 P_subinventory IN VARCHAR2,
756 X_return_status OUT NOCOPY VARCHAR2);
757
758
759 PROCEDURE Assign_Container_to_Consol(
760 p_child_container_id IN NUMBER,
761 p_parent_container_id IN NUMBER,
762 p_caller IN VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2);
764
765 PROCEDURE Unpack_Details_from_Consol
766 (p_delivery_details_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
767 p_caller IN VARCHAR2,
768 x_return_status OUT NOCOPY VARCHAR2);
769
770 END wsh_container_actions;