1 PACKAGE WMS_XDock_Pegging_Pub AUTHID CURRENT_USER AS
2 /* $Header: WMSXDCKS.pls 120.4.12020000.1 2012/06/26 09:05:40 appldev ship $ */
3
4 -- Record to store a valid supply or demand line for planned or opportunistic crossdocking.
5 -- This corresponds to the ROWTYPE of the global temp table, wms_xdock_pegging_gtmp
6 -- but also includes the ROWID column so the record can be updated easily later on
7 -- once retrieved.
8 TYPE shopping_basket_rec IS RECORD
9 (ROWID urowid,
10 inventory_item_id NUMBER,
11 xdock_source_code NUMBER,
12 source_type_id NUMBER,
13 source_header_id NUMBER,
14 source_line_id NUMBER,
15 source_line_detail_id NUMBER,
16 dock_start_time DATE,
17 dock_mean_time DATE,
18 dock_end_time DATE,
19 expected_time DATE,
20 quantity NUMBER,
21 reservable_quantity NUMBER,
22 uom_code VARCHAR2(3),
23 primary_quantity NUMBER,
24 secondary_quantity NUMBER,
25 secondary_uom_code VARCHAR2(3),
26 project_id NUMBER,
27 task_id NUMBER,
28 lpn_id NUMBER,
29 wip_supply_type NUMBER
30 );
31
32 -- Table to store the valid supply or demand lines for planned or opportunistic crossdocking.
33 -- This type is defined in the specs so the custom logic package can reference it.
34 TYPE shopping_basket_tb IS TABLE OF shopping_basket_rec INDEX BY BINARY_INTEGER;
35
36 -- Table type used to store the sorted order of supply line records in the shopping basket
37 -- table when using custom logic.
38 TYPE sorted_order_tb IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
39
40
41 -- For Wave Planning -- > Crossdocking Simulation
42 l_split_flag varchar2(1) := 'N';
43
44 -- The following 4 functions are used to cache crossdock criteria records. The caller can
45 -- Set, Get, Delete, or Clear the crossdock criteria cache.
46
47 -- This function will store the crossdock criteria record inputted into the cache
48 FUNCTION set_crossdock_criteria
49 (p_criterion_id IN NUMBER) RETURN BOOLEAN;
50
51 -- This function will retrieve the crossdock criteria record inputted from the cache
52 FUNCTION get_crossdock_criteria
53 (p_criterion_id IN NUMBER) RETURN wms_crossdock_criteria%ROWTYPE;
54
55 -- This function will delete the crossdock criteria record inputted from the cache
56 FUNCTION delete_crossdock_criteria
57 (p_criterion_id IN NUMBER) RETURN BOOLEAN;
58
59 -- This function will clear all of the crossdock criteria records stored in the cache
60 FUNCTION clear_crossdock_cache RETURN BOOLEAN;
61
62
63 -- This is a function used to retrieve the default routing ID given an item, org,
64 -- and vendor as inputs. This function will use the same logic as the get_defaul_routing_id
65 -- in the INV_RCV_COMMON_APIS package. However we will cache all of the values retrieved
66 -- for performance. The order to search for a default routing ID is: item, vendor, org.
67 -- The org and item should always be inputted and be non-null.
68 FUNCTION get_default_routing_id
69 (p_organization_id IN NUMBER,
70 p_item_id IN NUMBER,
71 p_vendor_id IN NUMBER
72 ) RETURN NUMBER DETERMINISTIC;
73
74
75 -- API Name : Planned_Cross_Dock
76 -- Package : WMS_XDock_Pegging_Pub
77 -- Description : This API will perform crossdock pegging to fulfill demand lines during
78 -- Pick Release. This procedure will be called from WSH_PICK_LIST.Release_Batch
79 -- (For 'Crossdock Only' or 'Prioritize Crossdock' allocation modes) and
80 -- INV_Pick_Release_Pub.Pick_Release (for 'Prioritize Inventory' allocation mode).
81 -- The main input is p_wsh_release_table which is a table of valid WDD lines to
82 -- allocate material against. This API will loop through each WDD line and try to find
83 -- valid supply lines to fulfill it. If a WDD line is satisfied through crossdock,
84 -- the released_status on p_wsh_release_table and also the database table
85 -- WSH_DELIVERY_DETAILS will be updated to 'S' (Released to Warehouse).
86 -- p_trolin_delivery_ids and p_del_detail_id must be kept in sync with each other.
87 -- Those two tables are needed if we split WDD lines since shipping would not have
88 -- visibility to the new WDD lines.
89 --
90 -- Input parameters:
91 -- p_api_version API Version (Should always be 1.0)
92 -- p_init_msg_list Initialize Message List (Shipping should pass FALSE)
93 -- p_commit Commit (Shipping should always pass FALSE)
94 -- p_batch_id Batch ID for the pick release batch. This corresponds to the table
95 -- WSH_PICKING_BATCHES which will contain all of the information we
96 -- need. This includes, org, allocation mode, crossdock criterion ID
97 -- and existing reservations only flag.
98 --
99 -- IN OUT parameters:
100 -- p_wsh_release_table Table of valid demand lines to pick release against.
101 -- Assume that all WDD records are for the same org, p_organization_id.
102 -- API will only process WDD lines with released_status of
103 -- 'R' (Ready to Release) or 'B' (Backordered).
104 -- Shipping should pass in WSH_PR_CRITERIA.release_table in the
105 -- WSH_PICK_LIST.Release_Batch API when pick release is run.
106 -- p_trolin_delivery_ids Table of delivery IDs for transactable demand lines.
107 -- Crossdocked lines needs to keep this table updated so crossdocked
108 -- or split WDD lines can be picked up to autocreate/merge deliveries.
109 -- Shipping should pass in local variable 'l_trolin_delivery_ids' from
110 -- the Release_Batch API. This table has a one to one relationship
111 -- with p_del_detail_id and stores the delivery_id for the corresponding
112 -- delivery_detail_id in p_del_detail_id.
113 -- p_del_detail_id Table of delivery detail IDs for transactable demand lines.
114 -- Crossdocked lines needs to keep this table updated so crossdocked
115 -- or split WDD lines can be picked up to autocreate/merge deliveries.
116 -- Shipping should pass in local variable 'l_del_detail_id' from
117 -- the Release_Batch API. This table has a one to one relationship
118 -- with p_trolin_delivery_ids and stores a list of delivery_detail_id
119 -- values for all of the transactable WDD lines in p_wsh_release_table.
120 --
121 -- Output parameters:
122 -- x_return_status
123 -- if the Planned_Cross_Dock API succeeds, the value is
124 -- fnd_api.g_ret_sts_success;
125 -- if there is an expected error, the value is
126 -- fnd_api.g_ret_sts_error;
127 -- if there is an unexpected error, the value is
128 -- fnd_api.g_ret_sts_unexp_error;
129 -- x_msg_count
130 -- if there are one or more errors, the number of error messages in the buffer
131 -- x_msg_data
132 -- if there is one and only one error, the error message
133 -- (See fnd_api package for more details about the above output parameters)
134 --
135
136
137 PROCEDURE Planned_Cross_Dock
138 (p_api_version IN NUMBER,
139 p_init_msg_list IN VARCHAR2,
143 x_msg_data OUT NOCOPY VARCHAR2,
140 p_commit IN VARCHAR2,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
144 p_batch_id IN NUMBER,
145 p_wsh_release_table IN OUT NOCOPY WSH_PR_CRITERIA.relRecTabTyp,
146 p_trolin_delivery_ids IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
147 p_del_detail_id IN OUT NOCOPY WSH_PICK_LIST.DelDetTabTyp,
148 p_simulation_mode in varchar2 default 'N');
149
150
151 -- API Name : Opportunistic_Cross_Dock
152 -- Package : WMS_XDock_Pegging_Pub
153 -- Description : This API will perform opportunistic crossdock pegging to fulfill a move order
154 -- line supply that has been received. It will find valid demand lines to peg this
155 -- supply to. The splitting of WDD lines, creation and splitting of reservations,
156 -- splitting and updating of MOL will all be done in this API. It will first try to
157 -- satisfy any high level receiving reservations for the same org/item. Then it will
158 -- use the demand sources as listed in the crossdock criterion passed. Note that if the
159 -- move order line does not have a crossdock criterion ID stamped on it, we will not
160 -- try to peg demand lines to it. This should not occur since there is a default
161 -- crossdock rule at the org level.
162 --
163 -- Input parameters:
164 -- p_organization_id Organization ID where MOL is received
165 -- p_move_order_line_id MOL supply line for which we are trying to find demand lines to peg to.
166 -- p_crossdock_criterion_id Crossdock criterion ID. This will determine what is valid for pegging
167 -- and should always be passed.
168 --
169 -- IN OUT parameters:
170 --
171 -- Output parameters:
172 -- x_return_status
173 -- if the Opportunistic_Cross_Dock API succeeds, the value is
174 -- fnd_api.g_ret_sts_success;
175 -- if there is an expected error, the value is
176 -- fnd_api.g_ret_sts_error;
177 -- if there is an unexpected error, the value is
178 -- fnd_api.g_ret_sts_unexp_error;
179 -- x_msg_count
180 -- if there are one or more errors, the number of error messages in the buffer
181 -- x_msg_data
182 -- if there is one and only one error, the error message
183 -- (See fnd_api package for more details about the above output parameters)
184 --
185 PROCEDURE Opportunistic_Cross_Dock
186 (p_organization_id IN NUMBER,
187 p_move_order_line_id IN NUMBER,
188 p_crossdock_criterion_id IN NUMBER,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2);
192
193
194 -- API Name : Get_Expected_Time
195 -- Package : WMS_XDock_Pegging_Pub
196 -- Description : This API will calculate the expected receipt or ship time for a given
197 -- Supply or Demand line used for crossdocking. This will be called from the
198 -- crossdock pegging engine and the concurrent exception program to recalculate
199 -- the expected receipt and ship times for crossdocked reservations.
200 --
201 -- Input parameters:
202 -- p_source_type_id Source type of supply or demand line. This corresponds to the
203 -- lookup 'Reservation_Types' used by reservations. e.g. 1 = PO,
204 -- 2 = Sales Order, 5 = WIP, 7 = Internal Req, 8 = Internal Order, etc.
205 -- p_source_header_id Source Header ID such as PO Header ID, RCV Shipment Header ID,
206 -- OE Order Header ID, etc. This corresponds to what reservations
207 -- inserts for various supply or demand types.
208 -- p_source_line_id Source Line ID such as PO Line Location ID, RCV Shipment Line ID,
209 -- OE Order Line ID, etc. This corresponds to what reservations
210 -- inserts for various supply or demand types.
211 -- p_source_line_detail_id Source Line Detail ID such as Delivery Detail ID. This corresponds
212 -- to what reservations inserts for various supply or demand types.
213 -- p_supply_or_demand Line type (Supply or Demand) you want to get the expected receipt or
214 -- ship time for: 1 = Supply
215 -- 2 = Demand
216 -- This variable might be needed since WIP type reservations use the
217 -- same source type code for WIP as a supply AND demand (backordered
218 -- component demand)
219 -- p_crossdock_criterion_id Crossdock criterion ID if available. This will determine how to
220 -- calculate the expected receipt or ship time in case a dock appointment
221 -- exists. Dock appointments are an interval with a start and
222 -- end time so it is possible to use the begin, mean, or end time.
223 --
224 -- IN OUT parameters:
225 --
226 -- Output parameters:
227 -- x_return_status
228 -- if the Get_Expected_Time API succeeds, the value is
229 -- fnd_api.g_ret_sts_success;
230 -- if there is an expected error, the value is
234 -- x_msg_count
231 -- fnd_api.g_ret_sts_error;
232 -- if there is an unexpected error, the value is
233 -- fnd_api.g_ret_sts_unexp_error;
235 -- if there are one or more errors, the number of error messages in the buffer
236 -- x_msg_data
237 -- if there is one and only one error, the error message
238 -- (See fnd_api package for more details about the above output parameters)
239 -- x_dock_start_time
240 -- The dock appointment start time (if it exists)
241 -- x_dock_mean_time
242 -- The dock appointment mean time (if it exists)
243 -- x_dock_end_time
244 -- The dock appointment end time (if it exists)
245 -- x_expected_time
246 -- The expected receipt or ship time for the line. If the crossdock criterion is passed
247 -- and a dock appointment exists, we will calculate the expected time based on the dock
248 -- start and end time using the supply_schedule_method or demand_schedule_method.
249 --
250 PROCEDURE Get_Expected_Time
251 (p_source_type_id IN NUMBER,
252 p_source_header_id IN NUMBER,
253 p_source_line_id IN NUMBER,
254 p_source_line_detail_id IN NUMBER := NULL,
255 p_supply_or_demand IN NUMBER,
256 p_crossdock_criterion_id IN NUMBER := NULL,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2,
260 x_dock_start_time OUT NOCOPY DATE,
261 x_dock_mean_time OUT NOCOPY DATE,
262 x_dock_end_time OUT NOCOPY DATE,
263 x_expected_time OUT NOCOPY DATE);
264
265
266 -- API Name : Get_Expected_Delivery_Time
267 -- Package : WMS_XDock_Pegging_Pub
268 -- Description : This API will calculate the expected ship time for a given delivery.
269 -- This will be called from opportunistic crossdocking when determining
270 -- which deliveries unassigned pegged WDD lines can be merged with.
271 -- Since shipping merges a line to a delivery without taking time into
272 -- account (crossdock window), opportunistic crossdock will need to first
273 -- get a list of valid deliveries we can merge to. Then for each
274 -- delivery, find the expected ship time so we merge only to deliveries
275 -- within the crossdock window. If none of the deliveries fall within
276 -- the crossdock window, we will need to create a new delivery.
277 --
278 -- Input parameters:
279 -- p_delivery_id Delivery ID we are trying to find the expected ship time for.
280 -- p_crossdock_criterion_id Crossdock criterion ID. This will determine how to calculate
281 -- the expected ship time in case a dock appointment exists.
282 -- Dock appointments are an interval with a start and end time, so
283 -- so it is possible to use the start, mean, or end time for the
284 -- expected ship time. In case multiple dock appointments exist,
285 -- this will also let us evaluate a dock appointment as a single
286 -- time so we can find the closest appointment to the WDD lines
287 -- assigned to the delivery.
288
289 -- IN OUT parameters:
290 --
291 -- Output parameters:
292 -- x_return_status
293 -- if the Get_Expected_Delivery_Time API succeeds, the value is
294 -- fnd_api.g_ret_sts_success;
295 -- if there is an expected error, the value is
296 -- fnd_api.g_ret_sts_error;
297 -- if there is an unexpected error, the value is
298 -- fnd_api.g_ret_sts_unexp_error;
299 -- x_msg_count
300 -- if there are one or more errors, the number of error messages in the buffer
301 -- x_msg_data
302 -- if there is one and only one error, the error message
303 -- (See fnd_api package for more details about the above output parameters)
304 -- x_dock_appointment_id
305 -- If a dock appointment exists, this corresponds to the dock appointment ID.
306 -- x_dock_start_time
307 -- The dock appointment start time (if it exists). If a dock appointment is not found
308 -- for the delivery and the trip stop does not have a departure date, this output
309 -- parameter will correspond to the minimum of the expected ship date for the WDD records
310 -- tied to the delivery.
311 -- x_dock_end_time
312 -- The dock appointment end time (if it exists). If a dock appointment is not found
313 -- for the delivery and the trip stop does not have a departure date, this output
314 -- parameter will correspond to the maximum of the expected ship date for the WDD records
315 -- tied to the delivery.
316 -- x_expected_time
317 -- The expected ship time for the delivery. If the crossdock criterion is passed and a
318 -- dock appointment exists, we will calculate the expected ship time based on the dock
319 -- start and end time using the demand_schedule_method. In this case, the dock appointment
320 -- output parameters will also be populated. If a dock appointment is not found and the
321 -- trip stop does not have a departure date, this parameter will be NULL.
322 --
323 PROCEDURE Get_Expected_Delivery_Time
324 (p_delivery_id IN NUMBER,
325 p_crossdock_criterion_id IN NUMBER,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 x_dock_appointment_id OUT NOCOPY NUMBER,
330 x_dock_start_time OUT NOCOPY DATE,
331 x_dock_end_time OUT NOCOPY DATE,
332 x_expected_time OUT NOCOPY DATE);
333
334
335 END WMS_XDock_Pegging_Pub;
336
[Home] [Help]