1 PACKAGE WSH_DELIVERY_VALIDATIONS AUTHID CURRENT_USER as
2 /* $Header: WSHDEVLS.pls 120.3.12010000.2 2009/12/03 15:13:53 gbhargav 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,
103
100 x_return_status OUT NOCOPY VARCHAR2);
101 --<TPA_PUBLIC_NAME>
102 --<TPA_DEFAULT_TPS=WSH_TPA_SELECTOR_PKG.DELIVERYTP>
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
229 -- - Weight/Volume information must be specified
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
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,
347
344 x_return_status OUT NOCOPY VARCHAR2);
345
346
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
467 , x_return_status OUT NOCOPY VARCHAR2
464 PROCEDURE Get_Disabled_List(
465 p_delivery_id IN NUMBER
466 , p_list_type IN 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#539934a
551 client_id NUMBER --- LSP PROJECT
552 --
553 );
554
555 TYPE dlvy_rec_tab_type IS TABLE OF dlvy_rec_type INDEX BY BINARY_INTEGER;
556
557 --OTM R12
558 TYPE trip_info_rec_type IS RECORD(
559 trip_id WSH_TRIPS.TRIP_ID%TYPE,
560 name WSH_TRIPS.NAME%TYPE,
561 status_code WSH_TRIPS.STATUS_CODE%TYPE
562 );
563
564 TYPE trip_info_tab_type IS TABLE OF trip_info_rec_type INDEX BY BINARY_INTEGER;
565 --
566
567 PROCEDURE Is_Action_Enabled(
568 p_dlvy_rec_tab IN dlvy_rec_tab_type,
569 p_action IN VARCHAR2,
570 p_caller IN VARCHAR2,
571 p_tripid IN NUMBER DEFAULT null,
572 x_return_status OUT NOCOPY VARCHAR2,
573 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
574 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
575 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type);
576
577
578 --
579 -- Overloaded procedure
580 --
581 PROCEDURE Get_Disabled_List (
582 p_delivery_rec IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
583 , p_in_rec IN WSH_DELIVERIES_GRP.Del_In_Rec_Type
584 , x_return_status OUT NOCOPY VARCHAR2
585 , x_msg_count OUT NOCOPY NUMBER
586 , x_msg_data OUT NOCOPY VARCHAR2
587 , x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
588 );
589
590 PROCEDURE Init_Delivery_Actions_Tbl (
591 p_action IN VARCHAR2
592 , x_delivery_actions_tab OUT NOCOPY DeliveryActionsTabType
593 , x_return_status OUT NOCOPY VARCHAR2
594 );
595
596 PROCEDURE Derive_Delivery_Uom (
597 p_delivery_id IN NUMBER,
598 p_organization_id IN NUMBER,
599 x_volume_uom_code IN OUT NOCOPY VARCHAR2,
603 x_return_status OUT NOCOPY VARCHAR2);
600 x_weight_uom_code IN OUT NOCOPY VARCHAR2,
601 x_wt_nullify_flag OUT NOCOPY BOOLEAN,
602 x_vol_nullify_flag OUT NOCOPY BOOLEAN,
604
605 PROCEDURE Validate_Routed_Export_Txn(
606 x_rtd_expt_txn_code IN OUT NOCOPY VARCHAR2,
607 p_rtd_expt_txn_meaning IN VARCHAR2,
608 x_return_status OUT NOCOPY VARCHAR2);
609
610 PROCEDURE Derive_Number_Of_LPN(
611 p_delivery_id IN NUMBER,
612 x_number_of_lpn IN OUT NOCOPY NUMBER,
613 x_return_status OUT NOCOPY VARCHAR2);
614
615 --Harmonizing Project
616
617 PROCEDURE Chk_Dup_Pickup_Dropoff_Locns(
618 p_delivery_id IN NUMBER,
619 p_pickup_location_id IN NUMBER,
620 p_dropoff_location_id IN NUMBER,
621 x_return_status OUT NOCOPY VARCHAR2);
622
623
624
625 FUNCTION Check_ITM_Required( p_delivery_id IN NUMBER,
626 x_return_status OUT NOCOPY VARCHAR2)
627 RETURN VARCHAR2;
628
629 -- PROCEDURE: Log_ITM_Exception (Pack J: ITM integration)
630 -- DESCRIPTION: If the delivery need to be marked for ITM screening, log an
631 -- exception against the delivery.
632 -- PARAMETERS: p_delivery_id - delivery that needs to be checked for ITM.
633 -- p_location_id - ship from location of delivery, required when
634 -- loggin exceptions.
635 -- p_action_type - Whether the check is made at 'SHIP_CONFIRM'
636 -- time or at 'CREATION' of delivery.
637
638 PROCEDURE Log_ITM_Exception(p_delivery_id in NUMBER,
639 p_ship_from_location_id in NUMBER,
640 p_action_type in VARCHAR2,
641 x_return_status out nocopy VARCHAR2);
642
643
644
645 TYPE exception_rec_type IS RECORD (
646 delivery_id NUMBER,
647 severity VARCHAR2(30),
648 exception_id NUMBER);
649
650 TYPE exception_rec_tab_type is table of exception_rec_type index by binary_integer;
651
652
653 PROCEDURE check_exception(
654 p_deliveries_tab IN wsh_util_core.id_tab_type
655 , x_exceptions_exist OUT NOCOPY VARCHAR2
656 , x_exceptions_tab OUT NOCOPY wsh_delivery_validations.exception_rec_tab_type
657 , x_return_status OUT NOCOPY VARCHAR2);
658
659
660 -- J-IB-NPARIKH-{
661 --
662 --
663 -- ----------------------------------------------------------------------
664 -- Procedure: has_lines
665 -- Parameters: p_delivery_id in number
666 -- returns varchar2
667 -- 'Y' -- Has non-container lines
668 -- 'N' -- Does not have any non-container lines
669 -- Description: Checks if delivery has any non-container lines
670 -- ----------------------------------------------------------------------
671 FUNCTION has_lines
672 (
673 p_delivery_id IN NUMBER
674 )
675 RETURN VARCHAR2;
676 --
677 --
678 --
679 -- This record type is used by following APIs
680 -- check_close, check_inTransit and
681 -- WSH_NEW_DELIVERY_ACTIONS.setInTransit, WSH_NEW_DELIVERY_ACTIONS.setClose
682 --
683 TYPE ChgStatus_in_rec_type
684 IS RECORD
685 (
686 delivery_id NUMBER,
687 name VARCHAR2(30),
688 status_code VARCHAR2(30), --Delivery's current status (DB Value)
689 put_messages BOOLEAN DEFAULT TRUE,
690 -- Put error/warning messages on stack during check_close/check_inTransit
691 -- FALSE means do not put error messages on stack
692 -- can be used by callers who just want to check whether a delivery's
693 -- status can be changed or not.
694 --
695 --
696 manual_flag VARCHAR2(10),
697 -- 'Y', if called from UI as part of delivery close action
698 -- 'N', if called from stop close action
699 --
700 --
701 caller VARCHAR2(32767),
702 actual_date DATE, -- Stop close date
703 stop_id NUMBER -- Stop being closed
704 );
705 --
706 --
707 PROCEDURE Check_Close
708 (
709 p_in_rec IN ChgStatus_in_rec_type,
710 x_return_status OUT NOCOPY VARCHAR2,
711 x_allowed OUT NOCOPY VARCHAR2
712 ) ;
713
714 --
715 --
716 PROCEDURE check_inTransit
717 (
718 p_in_rec IN ChgStatus_in_rec_type,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_allowed OUT NOCOPY VARCHAR2
721 ) ;
722 --
723 PROCEDURE get_shipping_control
724 (
725 p_delivery_id IN NUMBER,
726 x_shipping_control OUT NOCOPY VARCHAR2,
727 x_routing_response_id OUT NOCOPY NUMBER,
728 x_routing_request_flag OUT NOCOPY VARCHAR2,
729 x_return_status OUT NOCOPY VARCHAR2
730 );
731 --
732 -- J-IB-NPARIKH-}
733
734 --Function added for Bugfix 3562492
735 --========================================================================
736 -- FUNCTION : Is_del_eligible_pick
737 --
738 -- PARAMETERS:
739 -- x_return_status return status
740 -- p_delivery_id Delivery ID
741 -- VERSION : current version 1.0
742 -- initial version 1.0
743 -- COMMENT : This Function checks whether a Delivery id is eligible for Pick Release
747 --========================================================================
744 -- , if a Delivery is eligible it returns TRUE else it returns FALSE.
745 -- The return status of this Function is always Success except in case
746 -- of unexpected error.
748 FUNCTION Is_del_eligible_pick(
749 p_delivery_id IN NUMBER,
750 x_return_status OUT NOCOPY VARCHAR2)
751 RETURN BOOLEAN;
752
753 --OTM R12
754 PROCEDURE get_trip_information
755 (p_delivery_id IN NUMBER,
756 x_trip_info_rec OUT NOCOPY TRIP_INFO_REC_TYPE,
757 x_return_status OUT NOCOPY VARCHAR2
758 );
759
760 PROCEDURE GET_DELIVERY_INFORMATION
761 (p_delivery_id IN NUMBER,
762 x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
763 x_return_status OUT NOCOPY VARCHAR2);
764 --END OTM R12
765
766 END WSH_DELIVERY_VALIDATIONS;
[Home] [Help]