1 PACKAGE WSH_TRIP_STOPS_VALIDATIONS AUTHID CURRENT_USER as
2 /* $Header: WSHSTVLS.pls 120.0.12000000.1 2007/01/16 05:50:53 appldev ship $ */
3
4 --Harmonizing Project
5 TYPE StopActionsRec IS RECORD(
6 status_code wsh_trip_stops.status_code%TYPE,
7 caller VARCHAR2(100),
8 action_not_allowed VARCHAR2(100),
9 shipments_type_flag VARCHAR2(30)); -- J Inbound Logistics jckwok
10
11 TYPE StopActionsTabType IS TABLE of StopActionsRec INDEX BY BINARY_INTEGER;
12
13 TYPE stop_rec_type IS RECORD(
14 stop_id NUMBER,
15 organization_id NUMBER,
16 status_code VARCHAR2(32000),
17 shipments_type_flag VARCHAR2(30)); -- J Inbound Logistics jckwok
18
19 TYPE stop_rec_tab_type IS TABLE OF stop_rec_type INDEX BY BINARY_INTEGER;
20 --Harmonizing Project
21
22 /*
23 For every New stop entered or Updated
24 1. Check if stop sequence number is positive integer
25 2. Check if stop status is OPEN
26 3. Check if there is any existing stop on the trip with identical stop sequence
27 number
28 4. Check if the new planned arrival date is greater than the planned arrival date of
29 arrived or closed stop
30 */
31 TYPE stop_details IS RECORD(
32 stop_id NUMBER,
33 trip_id NUMBER,
34 status_code VARCHAR2(1),
35 stop_sequence_number NUMBER,
36 planned_arrival_date DATE,
37 planned_departure_date DATE,
38 physical_location_id NUMBER,
39 physical_stop_id NUMBER
40 );
41
42 TYPE stop_details_tab IS TABLE OF stop_details INDEX BY BINARY_INTEGER;
43
44 TYPE dleg_details IS RECORD(
45 pick_up_stop_id NUMBER,
46 drop_off_stop_id NUMBER,
47 delivery_id NUMBER
48 );
49
50 TYPE dleg_details_tab IS TABLE OF dleg_details INDEX BY BINARY_INTEGER;
51
52 PROCEDURE validate_sequence_number
53 (p_stop_id IN NUMBER,
54 p_stop_sequence_number IN NUMBER,
55 p_trip_id IN NUMBER,
56 p_status_code IN VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2);
58
59 PROCEDURE validate_closed_stop_seq
60 (p_trip_id IN NUMBER ,
61 p_stop_sequence_number IN NUMBER,
62 x_return_status OUT NOCOPY VARCHAR2);
63
64 PROCEDURE validate_unique_sequence
65 (p_trip_id IN NUMBER ,
66 p_stop_id IN NUMBER ,
67 p_stop_sequence_number IN NUMBER,
68 x_return_status OUT NOCOPY VARCHAR2);
69
70
71 PROCEDURE check_for_negative_number
72 (p_stop_sequence_number IN NUMBER,
73 x_return_status OUT NOCOPY VARCHAR2) ;
74
75 PROCEDURE validate_stop_status
76 (p_stop_status IN VARCHAR2,
77 x_return_status OUT NOCOPY VARCHAR2) ;
78
79 PROCEDURE valid_delivery_on_trip
80 (p_stop_id IN NUMBER,
81 p_trip_id IN NUMBER,
82 p_stop_sequence_number IN NUMBER,
83 x_del_to_unassign OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
84 x_return_status OUT NOCOPY VARCHAR2);
85
86 PROCEDURE get_new_sequence_number
87 (x_stop_sequence_number IN OUT NOCOPY NUMBER,
88 p_trip_id IN NUMBER,
89 p_status_code IN VARCHAR2,
90 p_stop_id IN NUMBER,
91 p_new_flag IN VARCHAR2,
92 x_return_status OUT NOCOPY VARCHAR2);
93
94
95 --Harmonizing Project
96 PROCEDURE Is_Action_Enabled(
97 p_stop_rec_tab IN stop_rec_tab_type,
98 p_action IN VARCHAR2,
99 p_caller IN VARCHAR2,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
102 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
103 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type);
104
105
106 --
107 -- Bug 2678363 - Added p_in_rec as a parameter instead of p_action
108 --
109 PROCEDURE Get_Disabled_List (
110 p_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type
111 , p_parent_entity_id IN NUMBER
112 , p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType
113 , x_return_status OUT NOCOPY VARCHAR2
114 , x_msg_count OUT NOCOPY NUMBER
115 , x_msg_data OUT NOCOPY VARCHAR2
116 , x_stop_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type
117 );
118
119
120 PROCEDURE Init_Stop_Actions_Tbl (
121 p_action IN VARCHAR2
122 , x_stop_actions_tab OUT NOCOPY StopActionsTabType
123 , x_return_status OUT NOCOPY VARCHAR2
124 );
125 --Harmonizing Project
126
127 -- J-IB-NPARIKH-{
128 --
129 -- 3 Record types added for stop close check API.
130 --
131 TYPE chkClose_in_rec_type
132 IS RECORD
133 (
134 stop_id NUMBER,
135 put_messages BOOLEAN DEFAULT TRUE,
136 -- FALSE means do not put error messages on stack
137 -- can be used by callers who just want to check whether a stop
138 -- can be closed or not.
139 --
140 --
141 manual_flag BOOLEAN DEFAULT TRUE,
142 caller VARCHAR2(32767),
143 actual_date DATE -- Stop close date
144 );
145 --
146 --
147 TYPE dlvy_rec_type
148 IS RECORD
149 (
150 id_tbl wsh_util_core.id_tab_type,
151 name_tbl wsh_util_core.Column_Tab_Type,
152 statusCode_tbl wsh_util_core.Column_Tab_Type,
153 orgId_tbl wsh_util_core.id_tab_type
154 );
155 --
156 --
157 TYPE chkclose_out_rec_type
158 IS RECORD
159 (
160 close_allowed VARCHAR2(10),
161 -- 'Y' : Allowed
162 -- 'YW': Allowed with warnings
163 -- 'N' : Not Allowed
164 -- 'NW': Not Allowed with warnings
165 --
166 --
167 stop_name VARCHAR2(60),
168 stop_sequence_number NUMBER,
169 initial_pu_dlvy_recTbl dlvy_rec_type,
170 -- Deliveries starting from this stop, which can be set to in-transit.
171 --
172 initial_pu_err_dlvy_id_tbl wsh_util_core.id_tab_type,
173 -- Deliveries starting from this stop, which cannot be set to in-transit.
174 --
175 ultimate_do_dlvy_recTbl dlvy_rec_type,
176 -- Deliveries ending at this stop, which can be closed.
177 --
178 trip_id NUMBER,
179 trip_status_code VARCHAR2(30),
180 ship_method_code VARCHAR2(30),
181 carrier_id NUMBER,
182 mode_of_transport VARCHAR2(30),
183 service_level VARCHAR2(30),
184 trip_new_status_code VARCHAR2(30),
185 -- new status code of trip, as result of stop closure
186 --
187 trip_seal_code VARCHAR2(32767),
188 trip_name VARCHAR2(30),
189 linked_stop_id NUMBER --wr
190 );
191
192 PROCEDURE refreshShipmentsTypeFlag
193 (
194 p_trip_id IN NUMBER,
195 p_stop_id IN NUMBER,
196 p_action IN VARCHAR2 DEFAULT 'ASSIGN',
197 p_shipment_direction IN VARCHAR2 DEFAULT 'O',
198 x_shipments_type_flag IN OUT NOCOPY VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2
200 ) ;
201
202
203 PROCEDURE get_stop_close_date
204 (
205 p_trip_id IN NUMBER,
206 p_stop_id IN NUMBER,
207 p_stop_sequence_number IN NUMBER,
208 x_stop_close_date OUT NOCOPY DATE,
209 x_return_status OUT NOCOPY VARCHAR2
210 );
211
212 PROCEDURE Check_Stop_Close -----trvlb
213 (
214 p_in_rec IN chkClose_in_rec_type,
215 x_out_rec OUT NOCOPY chkClose_out_rec_type,
216 x_return_status OUT NOCOPY VARCHAR2
217 ) ;
218
219 -- J-IB-NPARIKH-}
220
221
222 END WSH_TRIP_STOPS_VALIDATIONS;
[Home] [Help]