1 PACKAGE WSH_DELIVERY_VALIDATIONS as
2 /* $Header: WSHDEVLS.pls 120.3.12010000.1 2008/07/29 06:00:40 appldev ship $ */
3 --<TPA_PUBLIC_NAME=WSH_TPA_DELIVERY_PKG>
4 --<TPA_PUBLIC_FILE_NAME=WSHTPDE>
5
6 -----------------------------------------------------------------------------
7 --
8 -- Procedure: Check_Ship_Set
9 -- Parameters: delivery_id, x_return_status
10 -- Description: Checks if Ship Set is together and returns x_valid_flag
11 -- TRUE - if Ship Set is together
12 -- FALSE - if Ship Set is not together
13 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
14 --
15 -----------------------------------------------------------------------------
16
17 PROCEDURE Check_Ship_Set( p_delivery_id IN NUMBER,
18 x_valid_flag OUT NOCOPY BOOLEAN,
19 x_return_status OUT NOCOPY VARCHAR2);
20 --<TPA_PUBLIC_NAME>
21 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
22
23 -----------------------------------------------------------------------------
24 --
25 -- Procedure: Check_Smc
26 -- Parameters: delivery_id, x_return_status
27 -- Description: Checks if Ship Model is complete and returns x_valid_flag
28 -- TRUE - if Ship Model is complete
29 -- FALSE - if Ship Model is not complete
30 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
31 --
32 -----------------------------------------------------------------------------
33
34 PROCEDURE Check_Smc( p_delivery_id IN NUMBER,
35 x_valid_flag OUT NOCOPY BOOLEAN,
36 x_return_status OUT NOCOPY VARCHAR2);
37 --<TPA_PUBLIC_NAME>
38 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
39
40 -----------------------------------------------------------------------------
41 --
42 -- Procedure: Check_Arrival_Set
43 -- Parameters: p_delivery_id, x_return_status
44 -- Description: Checks if Arrival set is complete and returns a valid_flag
45 -- TRUE - if Arrival Set is complete
46 -- FALSE - if Arrival Set is not complete
47 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
48 --
49 -----------------------------------------------------------------------------
50
51 PROCEDURE Check_Arrival_Set( p_delivery_id IN NUMBER,
52 x_valid_flag OUT NOCOPY BOOLEAN,
53 x_return_status OUT NOCOPY VARCHAR2);
54 --<TPA_PUBLIC_NAME>
55 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
56
57 -----------------------------------------------------------------------------
58 --
59 -- Procedure: Check_Del_Not_I_T
60 -- Parameters: p_delivery_id, delivery_status, x_return_status
61 -- Description: Checks if delivery is In-transit status and sets a warning
62 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
63 --
64 -----------------------------------------------------------------------------
65
66 PROCEDURE Check_Del_Not_I_T( p_delivery_id IN NUMBER,
67 p_delivery_status IN VARCHAR2,
68 x_return_status OUT NOCOPY VARCHAR2);
69 --<TPA_PUBLIC_NAME>
70 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
71
72 -----------------------------------------------------------------------------
73 --
74 -- Procedure: Check_Released_Lines
75 -- Parameters: p_delivery_id, rel_num, unrel_num, x_return_status
76 -- Description: Checks if delivery has atleast one released and one unreleased lines and sets a warning
77 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
78 --
79 -----------------------------------------------------------------------------
80
81 PROCEDURE Check_Released_Lines( p_delivery_id IN NUMBER,
82 rel_num IN NUMBER,
83 unrel_num IN NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2);
85 --<TPA_PUBLIC_NAME>
86 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
87
88 -----------------------------------------------------------------------------
89 --
90 -- Procedure: Check_Del_Unpacked
91 -- Parameters: p_delivery_id, p_cont_exists_flag, p_unpacked_flag, x_return_status
92 -- Description: Checks if delivery has containers and is unpacked and issues a warning
93 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
94 --
95 -----------------------------------------------------------------------------
96
97 PROCEDURE Check_Del_Unpacked( p_delivery_id IN NUMBER,
98 p_cont_exists_flag IN BOOLEAN,
99 p_unpacked_flag IN BOOLEAN,
100 x_return_status OUT NOCOPY VARCHAR2);
101 --<TPA_PUBLIC_NAME>
102 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
103
104 -----------------------------------------------------------------------------
105 --
106 -- Procedure: Check_Del_Overfilled
107 -- Parameters: p_delivery_id, p_cont_exists_flag, p_unpacked_flag, x_return_status
108 -- Description: Checks if delivery has containers and does not have overfilled containers and issues a warning
109 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
110 --
111 -----------------------------------------------------------------------------
112
113 PROCEDURE Check_Del_Overfilled( p_delivery_id IN NUMBER,
114 p_cont_exists_flag IN BOOLEAN,
115 p_overfilled_flag IN BOOLEAN,
116 x_return_status OUT NOCOPY VARCHAR2);
117 --<TPA_PUBLIC_NAME>
118 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
119
120 -----------------------------------------------------------------------------
121 --
122 -- Procedure: Check_Del_Underfilled
123 -- Parameters: p_delivery_id, p_cont_exists_flag, p_underfilled_flag, x_return_status
124 -- Description: Checks if delivery has containers and does not have under filled containers and issues a warning
125 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
126 --
127 -----------------------------------------------------------------------------
128
129 PROCEDURE Check_Del_Underfilled( p_delivery_id IN NUMBER,
130 p_cont_exists_flag IN BOOLEAN,
131 p_underfilled_flag IN BOOLEAN,
132 x_return_status OUT NOCOPY VARCHAR2);
133 --<TPA_PUBLIC_NAME>
134 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
135
136 -----------------------------------------------------------------------------
137 --
138 -- Procedure: Check_Del_Final_Dest
139 -- Parameters: p_delivery_id, p_final_dropoff_id, p_ultimate_dropoff_id, x_return_status
140 -- Description: Checks if delivery final destination matches ultimate dropoff destination and returns a warning if it does not
141 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.deliveryTP
142 --
143 -----------------------------------------------------------------------------
144
145 PROCEDURE Check_Del_Final_Dest( p_delivery_id IN NUMBER,
146 p_final_dropoff_id IN NUMBER,
147 p_ultimate_dropoff_id IN NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2);
149 --<TPA_PUBLIC_NAME>
150 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
151
152 -----------------------------------------------------------------------------
153 --
154 -- Procedure: Check_Calendar
155 -- Parameters: p_entity_type, p_ship_date, p_ship_assoc_type, p_ship_location_id,
156 -- p_freight_code, p_freight_org_id,
157 -- p_receive_date, p_receive_assoc_type, p_receive_location_id,
158 -- x_return_status
159 -- Description: Checks if p_ship_date and p_rec_date are valid for the calendar
160 -- at location p_location_id
161 -- Values for p_entity_type are
162 -- DELIVERY
163 -- Values for p_assoc_type are
164 -- CUSTOMER
165 -- VENDOR
166 -- ORG
167 -- CARRIER
168 -- FOR TPA SELECTOR USE: wsh_tpa_selector_pkg.defaultTP
169 --
170 -----------------------------------------------------------------------------
171
172 PROCEDURE Check_Calendar ( p_entity_type IN VARCHAR2,
173 p_entity_id IN NUMBER,
174 p_ship_date IN DATE,
175 p_ship_assoc_type IN VARCHAR2,
176 p_ship_location_id IN NUMBER,
177 p_freight_code IN VARCHAR2,
178 p_freight_org_id IN NUMBER,
179 p_receive_date IN DATE,
180 p_receive_assoc_type IN VARCHAR2,
181 p_receive_location_id IN NUMBER,
182 p_update_flag IN VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2);
184 --<TPA_PUBLIC_NAME>
185 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DEFAULTTP>
186
187 -----------------------------------------------------------------------------
188 --
189 -- Procedure: Check_Plan
190 -- Parameters: delivery_id, x_return_status
191 -- Description: Checks for Plan action pre-requisites which are
192 -- - Delivery status is OPEN or PACKED
193 -- - At least one delivery detail is assigned
194 -- - SMC models must be together [warning]
195 -- - Ship Sets must be complete [warning]
196 -- - Delivery flow on trip/s is valid [error/warning]
197 --
198 -----------------------------------------------------------------------------
199
200 PROCEDURE Check_Plan ( p_delivery_id IN NUMBER,
201 x_return_status OUT NOCOPY VARCHAR2,
202 p_called_for_sc IN BOOLEAN default false);
203
204
205
206
207 -----------------------------------------------------------------------------
208 --
209 -- Procedure: Check_Unplan
210 -- Parameters: p_delivery_id, x_return_status
211 -- Description: Checks for Unplan action pre-requisites which are
212 -- - Delivery status is OPEN or IN-TRANSIT
213 -- - Delivery is planned
214 --
215 -----------------------------------------------------------------------------
216
217 PROCEDURE Check_Unplan ( p_delivery_id IN NUMBER,
218 x_return_status OUT NOCOPY VARCHAR2);
219
220
221
222
223 -----------------------------------------------------------------------------
224 --
225 -- Procedure: Check_Tender_Load
226 -- Parameters: p_delivery_leg_id, x_return_status
227 -- Description: Checks for Tender Load action pre-requisites which are
228 -- - Satisfies rules for Plan
229 -- - Weight/Volume information must be specified
230 --
231 -----------------------------------------------------------------------------
232
233 PROCEDURE Check_Tender_Load ( p_delivery_leg_id IN NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2);
235
236
237
238
239 -----------------------------------------------------------------------------
240 --
241 -- Procedure: Check_Assign_Trip
242 -- Parameters: p_delivery_id, x_return_status
243 -- Description: Checks for Assign Trip action pre-requisites which are
244 -- - Delivery status is not CLOSED
245 -- - Trip status is not CLOSED
246 -- - Pickup Stop status is OPEN or ARRIVED
247 -- - Pickup Stop sequence number is smaller than Dropoff Stop sequence number
248 -- - If GROUP_BY_CARRIER_FLAG set then freight carrier same as that on trip [warning]
249 -- - Delivery status is not IN-TRANSIT [warning]
250 -- - If trip is Planned and has Vehicle information then no stops on the trip are over filled by addition of this delivery [warning]
251
252 --
253 -----------------------------------------------------------------------------
254
255 PROCEDURE Check_Assign_Trip ( p_delivery_id IN NUMBER,
256 p_trip_id IN NUMBER,
257 p_pickup_stop_id IN NUMBER,
258 p_dropoff_stop_id IN NUMBER,
259 x_return_status OUT NOCOPY VARCHAR2);
260
261
262
263 /******* Commented this out for bug 2554849
264 -----------------------------------------------------------------------------
265 --
266 -- Procedure: Check_Unassign_Trip
267 -- Parameters: p_delivery_id, x_return_status
268 -- Description: Checks for Unassign Trip action pre-requisites which are
269 -- - Delivery status is not CLOSED
270 -- - Trip status is not CLOSED
271 -- - Delivery status is not IN-TRANSIT [warning]
272 -- - If trip is Planned and has Vehicle information then no stops on the trip are under filled by removal of this delivery [warning]
273 -- - No Bill of Lading is assigned to this delivery for this trip [warning] NOTE: this warning will inform the user that all Bill of Ladings will be deleted.
274 --
275 -----------------------------------------------------------------------------
276
277 PROCEDURE Check_Unassign_Trip ( p_delivery_id IN NUMBER,
278 p_trip_id IN NUMBER,
279 x_return_status OUT NOCOPY VARCHAR2);
280 ********** */
281
282
283
284
285 -----------------------------------------------------------------------------
286 --
287 -- Procedure: Check_Pack
288 -- Parameters: p_delivery_id, x_return_status
289 -- Description: Checks for Pack action pre-requisites which are
290 -- - Delivery status is OPEN
291 -- - All items being shipped on this delivery are packed in containers
292 -- - Details (shipped quantity, inventory controls ) must be specified for all delivery lines
293 -- - If containers are assigned to the delivery then they are not over/under packed [warning]
294 --
295 -----------------------------------------------------------------------------
296
297 PROCEDURE Check_Pack ( p_delivery_id IN NUMBER,
298 x_return_status OUT NOCOPY VARCHAR2);
299
300
301
302 -----------------------------------------------------------------------------
303 --
304 -- Procedure: Check_Confirm
305 -- Parameters: p_delivery_id, x_return_status
306 -- Description: Checks for Confirm action pre-requisites which are
307 -- - Delivery Status is OPEN or PACKED
308 -- - Details (shipped quantity, inventory controls ) must be specified for all delivery details (lines)
309 -- - At least one delivery detail (line) is released
310 -- - All delivery details (lines) are released [warning]
311 -- - If delivery status is OPEN and containers are assigned to this delivery then all items being shipped on this delivery are packed [warning]
312 -- - If delivery status is OPEN then containers for this delivery are not over/under packed [warning]
313 -- - SMC models must be together [warning]
314 -- - Ship Sets must be complete [warning]
315 -- - Delivery flow on trip/s is valid [error/warning]
316 --
317 -----------------------------------------------------------------------------
318
319
320 PROCEDURE Check_Confirm ( p_delivery_id IN NUMBER,
321 p_cont_exists_flag IN BOOLEAN,
322 p_enforce_packing_flag IN VARCHAR2,
323 p_overfilled_flag IN BOOLEAN,
324 p_underfilled_flag IN BOOLEAN,
325 p_ship_from_location IN NUMBER ,
326 p_ship_to_location IN NUMBER ,
327 p_freight_code IN VARCHAR2 ,
328 p_organization_id IN NUMBER ,
329 p_initial_pickup_date IN DATE ,
330 p_ultimate_dropoff_date IN DATE ,
331 p_actual_dep_date IN DATE ,
332 x_return_status OUT NOCOPY VARCHAR2) ;
333
334 -----------------------------------------------------------------------------
335 --
336 -- Procedure: Check_Reopen
337 -- Parameters: p_delivery_id, x_return_status
338 -- Description: Checks for Reopen action pre-requisites which are
339 -- - Delivery status is PACKED or CONFIRMED
340 --
341 -----------------------------------------------------------------------------
342
343 PROCEDURE Check_Reopen ( p_delivery_id IN NUMBER,
344 x_return_status OUT NOCOPY VARCHAR2);
345
346
347
348 -----------------------------------------------------------------------------
349 --
350 -- Procedure: Check_Intransit
351 -- Parameters: p_delivery_id, x_return_status
352 -- Description: Checks for Reopen action pre-requisites which are
353 -- - Delivery status is CONFIRMED
354 -- - First pickup stop status is CLOSED
355 --
356 -----------------------------------------------------------------------------
357
358 PROCEDURE Check_Intransit ( p_delivery_id IN NUMBER,
359 x_return_status OUT NOCOPY VARCHAR2);
360
361
362 -----------------------------------------------------------------------------
363 --
364 -- Procedure: Check_Close
365 -- Parameters: p_delivery_id = delivery being closed
366 -- p_manual_flag = 'Y' if user invokes the UI action Close Del.
367 -- 'N' if its drop-off stop is being closed
368 -- p_old_status_code = delivery's original status_code
369 -- x_return_status = SUCCESS -> can close,
370 -- ERROR -> cannot close
371 --
372 -- Description: Checks for Close action pre-requisites which are
373 -- - POD has been received
374 -- - If POD not received then last drop-off stop status is ARRIVED or CLOSED
375 -- If manually closing, the pre-requisite is either:
376 -- - Delivery is open with no details or legs assigned.
377 -- - Delivery is IN TRANSIT and owns all stops still open.
378 --
379 -----------------------------------------------------------------------------
380
381 PROCEDURE Check_Close ( p_delivery_id IN NUMBER,
382 p_manual_flag IN VARCHAR2,
383 p_old_status_code IN VARCHAR2,
384 x_return_status OUT NOCOPY VARCHAR2);
385
386
387
388 -----------------------------------------------------------------------------
389 --
390 -- Procedure: Check_Delete_Delivery
391 -- Parameters: p_delivery_id, x_return_status
392 -- Description: Checks for Delete Delivery action pre-requisites which are
393 -- - Delivery status is OPEN
394 -- - No freight costs assigned to delivery [warning]
395 -- - No Bill of Ladings assigned to delivery [warning]
396 -- - No delivery details assigned to this delivery [warning]
397 --
398 -----------------------------------------------------------------------------
399
400 PROCEDURE Check_Delete_Delivery ( p_delivery_id IN NUMBER,
401 x_return_status OUT NOCOPY VARCHAR2);
402
403
404 -----------------------------------------------------------------------------
405 --
406 -- Procedure: Check_Change_Carrier
407 -- Parameters: p_delivery_id, x_return_status
408 -- Description: Checks for Change Carrier action pre-requisites which are
409 -- - Delivery status is OPEN
410 -- - If GROUP_BY_CARRIER_FLAG is set then delivery details do not have a Ship Method specified
411 --
412 -----------------------------------------------------------------------------
413
414 PROCEDURE Check_Change_Carrier ( p_delivery_id IN NUMBER,
415 x_return_status OUT NOCOPY VARCHAR2);
416
417 -----------------------------------------------------------------------------
418
419
420 -- This procedure added for bug 2074768
421 --
422 -- Procedure: Check_Delivery_for_tolerances
423 -- Parameters: p_delivery_id, x_source_line_id , x_source_code , x_max_quantity , x_return_status
424 -- Description: Checks for Confirm shipped_quantities in a delivery's lines
425
426 -- History : HW OPM added x_max_quantity2 for OPM
427 -----------------------------------------------------------------------------
428 PROCEDURE Check_Delivery_for_tolerances ( p_delivery_id IN NUMBER,
429 x_source_line_id OUT NOCOPY NUMBER,
430 x_source_code OUT NOCOPY VARCHAR,
431 x_max_quantity OUT NOCOPY NUMBER,
432 x_max_quantity2 OUT NOCOPY NUMBER,
433 x_return_status OUT NOCOPY VARCHAR2 );
434
435 -----------------------------------------------------------------------------
436 --
437 -- Procedure: Check_Detail_for_Confirm
438 -- Parameters: p_detail_id, p_check_credit_init_flag, x_line_inv_flag_rec, x_return_status
439 -- Description: Checks for Confirm action pre-requisites which are
440 -- Check for inventory controls
441 -- Check for credit and holds
442 -- NOT YET IMPLEMENTED Check for exceptions
443 --
444 -----------------------------------------------------------------------------
445 PROCEDURE Check_Detail_for_Confirm ( p_detail_id IN NUMBER,
446 p_check_credit_init_flag IN BOOLEAN, -- bug 2343058
447 x_line_inv_flag_rec OUT NOCOPY wsh_delivery_details_inv.inv_control_flag_rec,
448 x_return_status OUT NOCOPY VARCHAR2);
449
450
451 --
452 -- Procedure: Get_Disabled_List
453 --
454 -- Parameters: p_delivery_id -- delivery the detail is assigned to
455 -- p_list_type -- 'FORM', will return list of form field names
456 -- 'TABLE', will return list of table column
457 -- names
458 -- x_return_status -- return status for execution of this API
459 -- x_disabled_list -- the disabled columns/fields in a trip
460 -- x_msg_count -- number of error message
461 -- x_msg_data -- error message if API failed
462 --
463
464 PROCEDURE Get_Disabled_List(
465 p_delivery_id IN NUMBER
466 , p_list_type IN VARCHAR2
467 , x_return_status OUT NOCOPY VARCHAR2
468 , x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type
469 , x_msg_count OUT NOCOPY NUMBER
470 , x_msg_data OUT NOCOPY VARCHAR2
471 , p_caller IN VARCHAR2 DEFAULT NULL --public api changes
472 );
473
474 -----------------------------------------------------------------------------
475 --
476 -- Function: Check_SS_Imp_Pending
477 -- Parameters: p_source_code,
478 -- p_source_header_id, p_ship_set_id, p_check_transactable,
479 -- x_return_status
480 -- p_check_transactable: If p_check_transactable is Y then
481 -- check whether any transactable
482 -- line is not imported
483 -- else
484 -- check whether any line is not imported
485 -- Description: Checks if any lines in the ship set are not yet imported
486 -- FALSE - All lines are imported into shipping
487 -- TRUE - Some lines are not imported into shipping
488 --
489 -----------------------------------------------------------------------------
490 FUNCTION Check_SS_Imp_Pending(
491 p_source_code IN VARCHAR2
492 , p_source_header_id IN NUMBER
493 , p_ship_set_id IN NUMBER
494 , p_check_transactable IN VARCHAR2
495 , x_return_status OUT NOCOPY VARCHAR2
496 ) return BOOLEAN;
497
498 ----------------------------------------------------------------------------
499 --
500 -- FUNCTION: Del_Assigned_To_Trip
501 --
502 -- PARAMETERS: p_delivery_id
503 --
504 -- DESCRIPTION: Returns 'Y' if delivery is assigned to a trip
505 --
506 -------------------------------------------------------------------------------
507
508 FUNCTION Del_Assigned_To_Trip(
509 p_delivery_id IN NUMBER,
510 x_return_status OUT NOCOPY VARCHAR2
511 ) RETURN VARCHAR2;
512
513
514 --Harmonizing Project
515 TYPE DeliveryActionsRec IS RECORD(
516 status_code wsh_new_deliveries.status_code%TYPE,
517 planned_flag wsh_new_deliveries.planned_flag%TYPE,
518 caller VARCHAR2(100),
519 action_not_allowed VARCHAR2(100),
520 org_type VARCHAR2(30),
521 message_name VARCHAR2(2000),
522 shipment_direction VARCHAR2(30),
523 --OTM R12
524 ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE,
525 tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE,
526 otm_enabled WSH_SHIPPING_PARAMETERS.OTM_ENABLED%TYPE -- OTM R12 - Bug#5399341
527 --
528 );
529 -- A Column called message_name has been added to the record
530 -- "DeliveryActionsRec" so that we can set the exact message
531 -- for each record as to why an action is not valid.
532 -- The message_name will contain the message short name
533 -- and appended with its respective tokens with
534 -- "-" as a separator between the message name and the
535 -- tokens and a "," seperator between each of
536 -- the tokens.
537
538 TYPE DeliveryActionsTabType IS TABLE of DeliveryActionsRec INDEX BY BINARY_INTEGER;
539
540 TYPE dlvy_rec_type IS RECORD(
541 delivery_id NUMBER,
542 organization_id NUMBER,
543 status_code VARCHAR2(32000),
544 planned_flag VARCHAR2(32000),
545 shipment_direction VARCHAR2(30),
546 delivery_type VARCHAR2(30),
547 --OTM R12
548 ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE,
549 tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE,
550 otm_enabled WSH_SHIPPING_PARAMETERS.OTM_ENABLED%TYPE -- OTM R12 - Bug#5399341
551 --
552 );
553
554 TYPE dlvy_rec_tab_type IS TABLE OF dlvy_rec_type INDEX BY BINARY_INTEGER;
555
556 --OTM R12
557 TYPE trip_info_rec_type IS RECORD(
558 trip_id WSH_TRIPS.TRIP_ID%TYPE,
559 name WSH_TRIPS.NAME%TYPE,
560 status_code WSH_TRIPS.STATUS_CODE%TYPE
561 );
562
563 TYPE trip_info_tab_type IS TABLE OF trip_info_rec_type INDEX BY BINARY_INTEGER;
564 --
565
566 PROCEDURE Is_Action_Enabled(
567 p_dlvy_rec_tab IN dlvy_rec_tab_type,
568 p_action IN VARCHAR2,
569 p_caller IN VARCHAR2,
570 p_tripid IN NUMBER DEFAULT null,
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
573 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
574 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type);
575
576
577 --
578 -- Overloaded procedure
579 --
580 PROCEDURE Get_Disabled_List (
581 p_delivery_rec IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
582 , p_in_rec IN WSH_DELIVERIES_GRP.Del_In_Rec_Type
583 , x_return_status OUT NOCOPY VARCHAR2
584 , x_msg_count OUT NOCOPY NUMBER
585 , x_msg_data OUT NOCOPY VARCHAR2
586 , x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
587 );
588
589 PROCEDURE Init_Delivery_Actions_Tbl (
590 p_action IN VARCHAR2
591 , x_delivery_actions_tab OUT NOCOPY DeliveryActionsTabType
592 , x_return_status OUT NOCOPY VARCHAR2
593 );
594
595 PROCEDURE Derive_Delivery_Uom (
596 p_delivery_id IN NUMBER,
597 p_organization_id IN NUMBER,
598 x_volume_uom_code IN OUT NOCOPY VARCHAR2,
599 x_weight_uom_code IN OUT NOCOPY VARCHAR2,
600 x_wt_nullify_flag OUT NOCOPY BOOLEAN,
601 x_vol_nullify_flag OUT NOCOPY BOOLEAN,
602 x_return_status OUT NOCOPY VARCHAR2);
603
604 PROCEDURE Validate_Routed_Export_Txn(
605 x_rtd_expt_txn_code IN OUT NOCOPY VARCHAR2,
606 p_rtd_expt_txn_meaning IN VARCHAR2,
607 x_return_status OUT NOCOPY VARCHAR2);
608
609 PROCEDURE Derive_Number_Of_LPN(
610 p_delivery_id IN NUMBER,
611 x_number_of_lpn IN OUT NOCOPY NUMBER,
612 x_return_status OUT NOCOPY VARCHAR2);
613
614 --Harmonizing Project
615
616 PROCEDURE Chk_Dup_Pickup_Dropoff_Locns(
617 p_delivery_id IN NUMBER,
618 p_pickup_location_id IN NUMBER,
619 p_dropoff_location_id IN NUMBER,
620 x_return_status OUT NOCOPY VARCHAR2);
621
622
623
624 FUNCTION Check_ITM_Required( p_delivery_id IN NUMBER,
625 x_return_status OUT NOCOPY VARCHAR2)
626 RETURN VARCHAR2;
627
628 -- PROCEDURE: Log_ITM_Exception (Pack J: ITM integration)
629 -- DESCRIPTION: If the delivery need to be marked for ITM screening, log an
630 -- exception against the delivery.
631 -- PARAMETERS: p_delivery_id - delivery that needs to be checked for ITM.
632 -- p_location_id - ship from location of delivery, required when
633 -- loggin exceptions.
634 -- p_action_type - Whether the check is made at 'SHIP_CONFIRM'
635 -- time or at 'CREATION' of delivery.
636
637 PROCEDURE Log_ITM_Exception(p_delivery_id in NUMBER,
638 p_ship_from_location_id in NUMBER,
639 p_action_type in VARCHAR2,
640 x_return_status out nocopy VARCHAR2);
641
642
643
644 TYPE exception_rec_type IS RECORD (
645 delivery_id NUMBER,
646 severity VARCHAR2(30),
647 exception_id NUMBER);
648
649 TYPE exception_rec_tab_type is table of exception_rec_type index by binary_integer;
650
651
652 PROCEDURE check_exception(
653 p_deliveries_tab IN wsh_util_core.id_tab_type
654 , x_exceptions_exist OUT NOCOPY VARCHAR2
655 , x_exceptions_tab OUT NOCOPY wsh_delivery_validations.exception_rec_tab_type
656 , x_return_status OUT NOCOPY VARCHAR2);
657
658
659 -- J-IB-NPARIKH-{
660 --
661 --
662 -- ----------------------------------------------------------------------
663 -- Procedure: has_lines
664 -- Parameters: p_delivery_id in number
665 -- returns varchar2
666 -- 'Y' -- Has non-container lines
667 -- 'N' -- Does not have any non-container lines
668 -- Description: Checks if delivery has any non-container lines
669 -- ----------------------------------------------------------------------
670 FUNCTION has_lines
671 (
672 p_delivery_id IN NUMBER
673 )
674 RETURN VARCHAR2;
675 --
676 --
677 --
678 -- This record type is used by following APIs
679 -- check_close, check_inTransit and
680 -- WSH_NEW_DELIVERY_ACTIONS.setInTransit, WSH_NEW_DELIVERY_ACTIONS.setClose
681 --
682 TYPE ChgStatus_in_rec_type
683 IS RECORD
684 (
685 delivery_id NUMBER,
686 name VARCHAR2(30),
687 status_code VARCHAR2(30), --Delivery's current status (DB Value)
688 put_messages BOOLEAN DEFAULT TRUE,
689 -- Put error/warning messages on stack during check_close/check_inTransit
690 -- FALSE means do not put error messages on stack
691 -- can be used by callers who just want to check whether a delivery's
692 -- status can be changed or not.
693 --
694 --
695 manual_flag VARCHAR2(10),
696 -- 'Y', if called from UI as part of delivery close action
697 -- 'N', if called from stop close action
698 --
699 --
700 caller VARCHAR2(32767),
701 actual_date DATE, -- Stop close date
702 stop_id NUMBER -- Stop being closed
703 );
704 --
705 --
706 PROCEDURE Check_Close
707 (
708 p_in_rec IN ChgStatus_in_rec_type,
709 x_return_status OUT NOCOPY VARCHAR2,
710 x_allowed OUT NOCOPY VARCHAR2
711 ) ;
712
713 --
714 --
715 PROCEDURE check_inTransit
716 (
717 p_in_rec IN ChgStatus_in_rec_type,
718 x_return_status OUT NOCOPY VARCHAR2,
719 x_allowed OUT NOCOPY VARCHAR2
720 ) ;
721 --
722 PROCEDURE get_shipping_control
723 (
724 p_delivery_id IN NUMBER,
725 x_shipping_control OUT NOCOPY VARCHAR2,
726 x_routing_response_id OUT NOCOPY NUMBER,
727 x_routing_request_flag OUT NOCOPY VARCHAR2,
728 x_return_status OUT NOCOPY VARCHAR2
729 );
730 --
731 -- J-IB-NPARIKH-}
732
733 --Function added for Bugfix 3562492
734 --========================================================================
735 -- FUNCTION : Is_del_eligible_pick
736 --
737 -- PARAMETERS:
738 -- x_return_status return status
739 -- p_delivery_id Delivery ID
740 -- VERSION : current version 1.0
741 -- initial version 1.0
742 -- COMMENT : This Function checks whether a Delivery id is eligible for Pick Release
743 -- , if a Delivery is eligible it returns TRUE else it returns FALSE.
744 -- The return status of this Function is always Success except in case
745 -- of unexpected error.
746 --========================================================================
747 FUNCTION Is_del_eligible_pick(
748 p_delivery_id IN NUMBER,
749 x_return_status OUT NOCOPY VARCHAR2)
750 RETURN BOOLEAN;
751
752 --OTM R12
753 PROCEDURE get_trip_information
754 (p_delivery_id IN NUMBER,
755 x_trip_info_rec OUT NOCOPY TRIP_INFO_REC_TYPE,
756 x_return_status OUT NOCOPY VARCHAR2
757 );
758
759 PROCEDURE GET_DELIVERY_INFORMATION
760 (p_delivery_id IN NUMBER,
761 x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
762 x_return_status OUT NOCOPY VARCHAR2);
763 --END OTM R12
764
765 END WSH_DELIVERY_VALIDATIONS;