[Home] [Help]
PACKAGE: APPS.WSH_MDC_SRS
Source
1 PACKAGE WSH_MDC_SRS AUTHID CURRENT_USER AS
2 /* $Header: WSHMDSRS.pls 120.3 2005/09/02 01:47:02 agbennet noship $ */
3
4 --===================
5 -- PUBLIC VARIABLES
6 --===================
7
8 --========================================================================
9 -- TYPE : addnl_del_attr_rec_type
10 --
11 -- COMMENT : The delivery details are stored in two parallel tables.
12 -- The generic information is stored using the type
13 -- WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type.
14 -- All the additional details are stored using the record
15 -- described below.
16 --========================================================================
17
18 TYPE addnl_del_attr_rec_type IS RECORD(
19 delivery_id NUMBER,
20 fob_code VARCHAR2(30),
21 freight_terms_code VARCHAR2(30),
22 loading_sequence NUMBER,
23 gross_weight NUMBER,
24 weight_uom_code VARCHAR2(3),
25 ignore_for_planning VARCHAR2(1),
26 ship_to_country VARCHAR2(120),
27 ship_to_state VARCHAR2(120),
28 ship_to_city VARCHAR2(120),
29 ship_to_postal_code VARCHAR2(60),
30 consol_index NUMBER,
31 intermediate_pickup_date DATE,
32 deconsol_trip_id NUMBER,
33 deconsol_trip_name VARCHAR2(30),
34 group_id NUMBER,
35 hash_value NUMBER,
36 hash_string VARCHAR2(1000)
37 );
38
39
40 --========================================================================
41 -- TYPE : addnl_del_attr_tab_type
42 --
43 -- COMMENT : The table stores the additional information regarding
44 -- deliveries.
45 --========================================================================
46 TYPE addnl_del_attr_tab_type IS TABLE OF addnl_del_attr_rec_type INDEX BY binary_integer;
47
48
49 --========================================================================
50 -- TYPE : grp_attr_rec_type
51 --
52 -- COMMENT : Record type to store the consolidation groupings created
53 -- along with the deliveries going beonging to the group.
54 --========================================================================
55 TYPE grp_attr_rec_type IS RECORD(
56 group_id NUMBER,
57 delivery_list wsh_util_core.id_tab_type,
58 hash_value NUMBER,
59 hash_string VARCHAR2(1000)
60 );
61
62
63 --========================================================================
64 -- TYPE : grp_attr_tab_type
65 --
66 -- COMMENT : Table holding the groups for consolidaiton.
67 --========================================================================
68 TYPE grp_attr_tab_type IS TABLE OF grp_attr_rec_type INDEX BY binary_integer;
69
70
71 --========================================================================
72 -- TYPE : group_by_flags_rec_type
73 --
74 -- COMMENT : Record containing the grouping attributes specified in the
75 -- consolidation grouping rule.
76 --========================================================================
77 TYPE group_by_flags_rec_type IS RECORD(
78 ship_from VARCHAR2(1),
79 customer VARCHAR2(1),
80 intmed_ship_to VARCHAR2(1),
81 carrier VARCHAR2(1),
82 mode_of_transport VARCHAR2(1),
83 service_level VARCHAR2(1),
84 fob VARCHAR2(1),
85 freight_terms VARCHAR2(1),
86 loading_sequence VARCHAR2(1),
87 ship_to_code VARCHAR2(1),
88 ship_to_country VARCHAR2(1),
89 ship_to_state VARCHAR2(1),
90 ship_to_city VARCHAR2(1),
91 ship_to_postal_code VARCHAR2(1),
92 ship_to_zone NUMBER
93
94 );
95
96
97 --========================================================================
98 -- TYPE : group_by_flags_tab_type
99 --
100 -- COMMENT : Table containing the grouping rules.
101 --========================================================================
102 TYPE group_by_flags_tab_type IS TABLE OF group_by_flags_rec_type INDEX BY binary_integer;
103
104
105 --========================================================================
106 -- TYPE : select_del_flags_rec_type
107 --
108 -- COMMENT : Contains the delivery selection criteria specified while
109 -- submitting the conolidation batch.
110 --========================================================================
111 TYPE select_del_flags_rec_type IS RECORD(
112 org_id NUMBER,
113 rule_id NUMBER,
114 consol_ship_to_loc_id NUMBER,
115 consol_shipto_override_flag VARCHAR2(1),
116 delivery_name_from VARCHAR2(30),
117 delivery_name_to VARCHAR2(30),
118 pick_up_DATE_starts_within NUMBER,
119 pick_up_DATE_ends_within NUMBER,
120 drop_off_DATE_starts_within NUMBER,
121 drop_off_DATE_ends_within NUMBER,
122 pick_release_batch_id NUMBER,
123 customer_id NUMBER,
124 fob_code VARCHAR2(30),
125 freight_terms_code VARCHAR2(30),
126 carrier_id NUMBER,
127 mode_of_transport VARCHAR2(30),
128 service_level VARCHAR2(30),
129 loading_sequence NUMBER,
130 intmed_ship_to_loc_id NUMBER,
131 ulti_ship_to_loc_id NUMBER,
132 ulti_ship_to_region_id NUMBER,
133 ulti_ship_to_zip_from VARCHAR2(30),
134 ulti_ship_to_zip_to VARCHAR2(30),
135 ulti_ship_to_zone_id NUMBER,
136 inc_staged_del_flag VARCHAR2(1),
137 inc_del_assgnd_trip_flag VARCHAR2(1),
138 create_deconsol_trips_flag VARCHAR2(1),
139 route_trips_flag VARCHAR2(1),
140 rate_trips_flag VARCHAR2(1),
141 trip_name_prefix VARCHAR2(30),
142 max_trip_weight NUMBER,
143 max_trip_weight_uom VARCHAR2(3)
144 );
145
146
147 --===================
148 -- PROCEDURES
149 --===================
150
151 --========================================================================
152 -- PROCEDURE : Schedule_Batch
153 --
154 -- PARAMETERS: errbuf Concurrent request error buffer
155 -- retcode Concurrent request return code
156 -- p_batch_id Concurrent submission batch id
157 -- p_log_level Concurrent request log level
158 --
159 -- COMMENT : This procedure is the entry point of the MDC SRS request.
160 -- The procedure accepts the batch id for which consolidation
161 -- is requested.
162 --========================================================================
163 PROCEDURE Schedule_Batch(
164 errbuf OUT NOCOPY VARCHAR2,
165 retcode OUT NOCOPY VARCHAR2,
166 p_batch_id IN NUMBER,
167 p_log_level IN NUMBER);
168
169
170 --========================================================================
171 -- PROCEDURE : Get_Batch_Parameters
172 --
173 -- PARAMETERS: p_batch_id Concurrent submission batch id
174 -- x_sel_del_attr Delivery selection parameters
175 -- x_return_status Return status
176 --
177 -- COMMENT : This procedure gets the delivery selection criteria specified
178 -- for the batch.
179 --========================================================================
180 PROCEDURE Get_Batch_Parameters(
181 p_batch_id IN NUMBER,
182 x_sel_del_attr OUT NOCOPY select_del_flags_rec_type,
183 x_return_status OUT NOCOPY VARCHAR2);
184
185
186 --========================================================================
187 -- PROCEDURE : Get_Deliveries
188 --
189 -- PARAMETERS: p_sel_del_attr Delivery selection parameters
190 -- x_delivery_tab Deliveries selected for consolidation
191 -- x_delivery_addnl_attr_tab Deliveries selected for consolidation
192 -- x_return_status Return status
193 --
194 -- COMMENT : This procedure fetches the deliveries to be consolidated.
195 --========================================================================
196 PROCEDURE Get_Deliveries(
197 p_sel_del_attr IN select_del_flags_rec_type,
198 x_delivery_tab OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
199 x_delivery_addnl_attr_tab OUT NOCOPY addnl_del_attr_tab_type,
200 x_return_status OUT NOCOPY VARCHAR2);
201
202
203 --========================================================================
204 -- PROCEDURE : Set_Intermediate_Location
205 --
206 -- PARAMETERS: p_consol_loc_id Default intermediate location id
207 -- p_override_ship_to_flag Intermediate location override flag
208 -- p_rule_zone_id Zone specified in the grouping rule
209 -- x_delivery_tab Delivery records
210 -- x_delivery_addnl_attr_tab Delivery records
211 -- x_failed_records Deliveries which failed to get intermedidate location
212 -- x_return_status Return status
213 --
214 -- COMMENT : This procedure updates the delivery records with the intermediate
215 -- location. The intermediate location is fecthed by calling
216 -- constraints engine which futher checks the Regions form
217 -- for an intermediate location, if required. The default intermediate
218 -- location is applied if the constraints engine fails to fecth
219 -- an intermediate location. If the override flag is set,
220 -- all the deliveries are updated with the default value.
221 --========================================================================
222 PROCEDURE Set_Intermediate_Location(
223 p_consol_loc_id IN NUMBER,
224 p_override_ship_to_flag IN VARCHAR2,
225 p_rule_zone_id IN NUMBER,
226 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
227 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
228 x_failed_records IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
229 x_return_status OUT NOCOPY VARCHAR2);
230
231
232 --========================================================================
233 -- PROCEDURE : Get_Grouping_Attrs
234 --
235 -- PARAMETERS: p_grouping_rule_id Consolidation grouping rule id
236 -- x_group_by_flags Grouping attributes
237 -- x_return_status Return status
238 --
239 -- COMMENT : This procedure gets the grouping attributes for the
240 -- rule specified.
241 --========================================================================
242 PROCEDURE Get_Grouping_Attrs(
243 p_grouping_rule_id IN NUMBER,
244 x_group_by_flags OUT NOCOPY group_by_flags_rec_type,
245 x_return_status OUT NOCOPY VARCHAR2);
246
247
248 --========================================================================
249 -- PROCEDURE : Get_Hash_Value
250 --
251 -- PARAMETERS: p_delivery_rec Delivery record
252 -- p_delivery_addnl_attr_rec Delivery record
253 -- p_group_by_flags Grouping attributes
254 -- p_hash_base Hash base
255 -- p_hash_size Hash size
256 -- x_hash_string Hash string
257 -- x_hash_value Hash value
258 -- x_return_status Return status
259 --
260 -- COMMENT : This procedure takes the delivery records and the
261 -- grouping attributes and return the hash value. The hash
262 -- size and base also needs to be passed.
263 -- The procedure also clears out all the non-grouping
264 -- attributes from the delivery records
265 --========================================================================
266 PROCEDURE Get_Hash_Value(
267 x_delivery_rec IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type,
268 x_delivery_addnl_attr_rec IN OUT NOCOPY addnl_del_attr_rec_type,
269 p_group_by_flags IN group_by_flags_rec_type,
270 p_hash_base IN NUMBER,
271 p_hash_size IN NUMBER,
272 x_hash_string OUT NOCOPY VARCHAR2,
273 x_hash_value OUT NOCOPY NUMBER,
274 x_return_status OUT NOCOPY VARCHAR2);
275
276
277 --========================================================================
278 -- PROCEDURE : Create_Consolidations
279 --
280 -- PARAMETERS: x_delivery_tab Delivery records
281 -- x_delivery_addnl_attr_tab Delivery records
282 -- p_group_tab Groups created
283 -- p_max_trip_weight Max weight of consolidation trip
284 -- p_max_weight_uom Max weight uom
285 -- p_trip_name_prefix Consolidation trip name prefix
286 -- x_consol_trip_id Consolidation trip ids
287 -- x_consol_del_id Consolidation delivery ids
288 -- x_trips_all List of all the trips created
289 -- x_failed_records Records that failed consolidation
290 -- x_return_status Return status
291 --
292 -- COMMENT : This procedure takes the delivery records and the
293 -- groups created and creates consolidated deliveries.
294 -- The max consolidation trip weight and trip name prefix
295 -- can also be specified.
296 --========================================================================
297 PROCEDURE Create_Consolidations(
298 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
299 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
300 p_group_tab IN grp_attr_tab_type,
301 p_max_trip_weight IN NUMBER,
302 p_max_weight_uom IN VARCHAR2,
303 p_trip_name_prefix IN VARCHAR2,
304 x_consol_trip_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
305 x_consol_del_id OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
306 x_trips_all OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
307 x_failed_records OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
308 x_return_status OUT NOCOPY VARCHAR2);
309
310
311 --========================================================================
312 -- PROCEDURE : Create_Deconsol_Trips
313 --
314 -- PARAMETERS: x_delivery_tab Delivery records
315 -- x_delivery_addnl_attr_tab Delivery records
316 -- p_consol_trip_id Consolidation trip ids
317 -- x_trips_all List of all the trips created
318 -- x_return_status Return status
319 --
320 -- COMMENT : This procedure takes the delivery records and and creates
321 -- cdeconsolidation trips for the deliveries.
322 --========================================================================
323 PROCEDURE Create_Deconsol_Trips(
324 x_delivery_tab IN OUT NOCOPY WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type,
325 x_delivery_addnl_attr_tab IN OUT NOCOPY addnl_del_attr_tab_type,
326 p_consol_trip_id IN WSH_UTIL_CORE.id_tab_type,
327 p_trip_name_prefix IN VARCHAR2,
328 x_trips_all IN OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
329 x_return_status OUT NOCOPY VARCHAR2);
330
331 END WSH_MDC_SRS;