4
1 PACKAGE WMS_XDOCK_CUSTOM_APIS_PUB AUTHID CURRENT_USER AS
2 /* $Header: WMSXDCAS.pls 120.2 2005/07/01 16:49:24 appldev noship $ */
3
5 -- Global constant indicating if we allow partial WIP crossdocking, i.e. if a demand
6 -- can be fulfilled through supplies of type WIP and other non-Inventory supply types.
7 -- This should only be used for OE demand lines. WIP demand lines are backordered component
8 -- demand which we do not create crossdock reservations for.
9 g_allow_partial_wip_xdock CONSTANT VARCHAR2(1) := 'N';
10
11
12 -- API Name : Get_Crossdock_Criteria
13 -- Package : WMS_XDOCK_CUSTOM_APIS_PUB
14 -- Description : This API is used to define a custom method of determining a valid
15 -- crossdock criteria to return for a given WDD demand line during Planned
16 -- Crossdocking. This would be called in lieu of the rules engine in the pegging
17 -- logic. The output is expected to be a valid crossdock criteria ID of type
18 -- 'Planned'. If the variable x_api_is_implemented is TRUE, then we will use
19 -- the value in x_crossdock_criteria_id. If this value is NULL, we assume that a
23 -- criteria.
20 -- crossdock criteria could not be determined and the WDD demand line will not be
21 -- crossdocked. If the variable x_api_is_implemented is FALSE, then the pegging
22 -- logic will call the rules engine instead to determine a valid crossdock
24 --
25 -- Input parameters:
26 -- p_wdd_release_record WDD demand line we are trying to determine a valid planned
27 -- crossdock criteria for. The demand line record contains relevant
28 -- information such as org, item, customer ID, project, task, etc.
29 --
30 -- IN OUT parameters:
31 --
32 -- Output parameters:
33 -- x_return_status
34 -- if the Get_Planned_Crossdock_Criteria API succeeds, the value is
35 -- fnd_api.g_ret_sts_success;
36 -- if there is an expected error, the value is
37 -- fnd_api.g_ret_sts_error;
38 -- if there is an unexpected error, the value is
39 -- fnd_api.g_ret_sts_unexp_error;
40 -- x_msg_count
41 -- if there are one or more errors, the number of error messages in the buffer
42 -- x_msg_data
43 -- if there is one and only one error, the error message
44 -- (See fnd_api package for more details about the above output parameters)
45 -- x_api_is_implemented
46 -- Indicates if the custom API is implemented or not (TRUE or FALSE BOOlEAN value)
47 -- x_crossdock_criteria_id
48 -- The output crossdock criteria ID of type 'Planned'. If a NULL value is returned,
49 -- this means no valid crossdock criteria could be determined. If a value of -1 is returned,
50 -- this will be interpreted as 'Not Implemented'. The pegging logic will then call the rules
51 -- engine to determine a valid crossdock criteria instead.
52 --
53 PROCEDURE Get_Crossdock_Criteria
54 (p_wdd_release_record IN WSH_PR_CRITERIA.relRecTyp,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 x_api_is_implemented OUT NOCOPY BOOLEAN,
59 x_crossdock_criteria_id OUT NOCOPY NUMBER);
60
61
62 -- API Name : Get_Expected_Time
63 -- Package : WMS_XDOCK_CUSTOM_APIS_PUB
64 -- Description : This API is used to define a custom method of determining the expected receipt
65 -- or ship time for a given Supply or Demand line used for crossdocking.
66 -- This will be called from the crossdock pegging engine first. If the API is not
67 -- implemented, we will then call the default Get_Expected_Time API instead.
68 --
69 -- Input parameters:
70 -- p_source_type_id Source type of supply or demand line. This corresponds to the
71 -- lookup 'Reservation_Types' used by reservations. e.g. 1 = PO,
72 -- 2 = Sales Order, 5 = WIP, 7 = Internal Req, 8 = Internal Order, etc.
73 -- p_source_header_id Source Header ID such as PO Header ID, RCV Shipment Header ID,
74 -- OE Order Header ID, etc. This corresponds to what reservations
75 -- inserts for various supply or demand types.
76 -- p_source_line_id Source Line ID such as PO Line Location ID, RCV Shipment Line ID,
77 -- OE Order Line ID, etc. This corresponds to what reservations
78 -- inserts for various supply or demand types.
79 -- p_source_line_detail_id Source Line Detail ID such as Delivery Detail ID. This corresponds
80 -- to what reservations inserts for various supply or demand types.
81 -- p_supply_or_demand Line type (Supply or Demand) you want to get the expected receipt or
82 -- ship time for: 1 = Supply
83 -- 2 = Demand
84 -- This variable might be needed since WIP type reservations use the
85 -- same source type code for WIP as a supply AND demand (backordered
86 -- component demand)
87 -- p_crossdock_criterion_id Crossdock criterion ID if available. This will determine how to
88 -- calculate the expected receipt or ship time in case a dock appointment
89 -- exists. Dock appointments are an interval with a start and
90 -- end time so it is possible to use the begin, mean, or end time.
91 -- p_dock_schedule_method Dock schedule method for the crossdock criterion (if value is passed).
92 -- This will correspond to either the supply_schedule_method or
93 -- demand_schedule_method depending on the line type (supply or demand).
94 --
95 -- IN OUT parameters:
96 --
97 -- Output parameters:
98 -- x_return_status
99 -- if the Get_Expected_Time API succeeds, the value is
100 -- fnd_api.g_ret_sts_success;
101 -- if there is an expected error, the value is
102 -- fnd_api.g_ret_sts_error;
103 -- if there is an unexpected error, the value is
104 -- fnd_api.g_ret_sts_unexp_error;
105 -- x_msg_count
106 -- if there are one or more errors, the number of error messages in the buffer
107 -- x_msg_data
108 -- if there is one and only one error, the error message
109 -- (See fnd_api package for more details about the above output parameters)
110 -- x_api_is_implemented
111 -- Indicates if the custom API is implemented or not (TRUE or FALSE BOOlEAN value)
112 -- x_dock_start_time
113 -- The dock appointment start time (if it exists)
117 -- The dock appointment end time (if it exists)
114 -- x_dock_mean_time
115 -- The dock appointment mean time (if it exists)
116 -- x_dock_end_time
118 -- x_expected_time
119 -- The expected receipt or ship time for the line. If the crossdock criterion is passed
120 -- and a dock appointment exists, we will calculate the expected time based on the dock
121 -- start and end time using the supply_schedule_method or demand_schedule_method.
122 -- If a dock appointment exists but a crossdock criterion is not passed, this should have
123 -- a NULL value.
124 --
125 PROCEDURE Get_Expected_Time
126 (p_source_type_id IN NUMBER,
127 p_source_header_id IN NUMBER,
128 p_source_line_id IN NUMBER,
129 p_source_line_detail_id IN NUMBER,
130 p_supply_or_demand IN NUMBER,
131 p_crossdock_criterion_id IN NUMBER,
132 p_dock_schedule_method IN NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2,
136 x_api_is_implemented OUT NOCOPY BOOLEAN,
137 x_dock_start_time OUT NOCOPY DATE,
138 x_dock_mean_time OUT NOCOPY DATE,
139 x_dock_end_time OUT NOCOPY DATE,
140 x_expected_time OUT NOCOPY DATE);
141
142
143 -- API Name : Get_Expected_Delivery_Time
144 -- Package : WMS_XDOCK_CUSTOM_APIS_PUB
145 -- Description : This API is used to define a custom method of determining the expected ship
146 -- time for a given delivery. This will be called from the crossdock pegging
147 -- engine first. It is used for opportunistic crossdocking when determining
148 -- which deliveries unassigned pegged WDD lines can be merged with. If the API
149 -- is not implemented, we will then call the default Get_Expected_Delivery_Time
150 -- API instead. Since shipping merges a line to a delivery without taking time
151 -- into account (crossdock window), opportunistic crossdock will need to first
152 -- get a list of valid deliveries we can merge to. Then for each
153 -- delivery, find the expected ship time so we merge only to deliveries
154 -- within the crossdock window. If none of the deliveries fall within
155 -- the crossdock window, we will need to create a new delivery.
156 --
157 -- Input parameters:
158 -- p_delivery_id Delivery ID we are trying to find the expected ship time for.
159 -- p_crossdock_criterion_id Crossdock criterion ID. This will determine how to calculate
160 -- the expected ship time in case a dock appointment exists.
161 -- Dock appointments are an interval with a start and end time, so
162 -- so it is possible to use the start, mean, or end time for the
163 -- expected ship time. In case multiple dock appointments exist,
164 -- this will also let us evaluate a dock appointment as a single
165 -- time so we can find the closest appointment to the WDD lines
166 -- assigned to the delivery.
167 -- p_dock_schedule_method Dock schedule method for the crossdock criterion.
168 -- This will correspond to the demand_schedule_method for the
169 -- crossdock criterion.
170 --
171 -- IN OUT parameters:
172 --
173 -- Output parameters:
174 -- x_return_status
175 -- if the Get_Expected_Delivery_Time API succeeds, the value is
176 -- fnd_api.g_ret_sts_success;
177 -- if there is an expected error, the value is
178 -- fnd_api.g_ret_sts_error;
179 -- if there is an unexpected error, the value is
180 -- fnd_api.g_ret_sts_unexp_error;
181 -- x_msg_count
182 -- if there are one or more errors, the number of error messages in the buffer
183 -- x_msg_data
184 -- if there is one and only one error, the error message
185 -- (See fnd_api package for more details about the above output parameters)
186 -- x_api_is_implemented
187 -- Indicates if the custom API is implemented or not (TRUE or FALSE BOOlEAN value)
188 -- x_dock_appointment_id
189 -- If a dock appointment exists, this corresponds to the dock appointment ID.
190 -- x_dock_start_time
191 -- The dock appointment start time (if it exists). If a dock appointment is not found
192 -- for the delivery and the trip stop does not have a departure date, this output
193 -- parameter will correspond to the minimum of the expected ship date for the WDD records
194 -- tied to the delivery.
195 -- x_dock_end_time
196 -- The dock appointment end time (if it exists). If a dock appointment is not found
197 -- for the delivery and the trip stop does not have a departure date, this output
198 -- parameter will correspond to the maximum of the expected ship date for the WDD records
199 -- tied to the delivery.
200 -- x_expected_time
201 -- The expected ship time for the delivery. If the crossdock criterion is passed and a
202 -- dock appointment exists, we will calculate the expected ship time based on the dock
203 -- start and end time using the demand_schedule_method. In this case, the dock appointment
204 -- output parameters will also be populated. If a dock appointment is not found and the
205 -- trip stop does not have a departure date, this parameter will be NULL.
206 --
207 PROCEDURE Get_Expected_Delivery_Time
208 (p_delivery_id IN NUMBER,
209 p_crossdock_criterion_id IN NUMBER,
210 p_dock_schedule_method IN NUMBER,
211 x_return_status OUT NOCOPY VARCHAR2,
215 x_dock_appointment_id OUT NOCOPY NUMBER,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 x_api_is_implemented OUT NOCOPY BOOLEAN,
216 x_dock_start_time OUT NOCOPY DATE,
217 x_dock_end_time OUT NOCOPY DATE,
218 x_expected_time OUT NOCOPY DATE);
219
220
221 -- API Name : Sort_Supply_Lines
222 -- Package : WMS_XDOCK_CUSTOM_APIS_PUB
226 -- inputted demand. The prioritize documents input comes from the crossdock criteria
223 -- Description : This API is used to sort an input table of valid supply lines for crossdocking.
224 -- This is called for Planned Crossdocking if the crossdocking goal is 'Custom'.
225 -- The input table contains supply lines that are all valid for crossdocking to the
227 -- used and indicates if we should maintain the relative ordering of the supply source
228 -- types in p_shopping_basket_tb.
229 --
230 -- Input parameters:
231 -- p_wdd_release_record WDD demand line record we are trying to crossdock.
232 -- p_prioritize_documents Flag indicating if the supply source documents should be
233 -- prioritized or not. The possible values are:
234 -- 1 = Yes
235 -- 2 = No
236 -- If the documents are to be prioritized, the supply lines in
237 -- the input p_shopping_basket_tb will already be sorted by the
238 -- supply source types in the order they should be consumed. In
239 -- this case, the custom API should sort the supply lines by the source
240 -- types to maintain the relative ordering of the supply documents.
241 -- For example, if the following are the supply lines returned in
242 -- p_shopping_basket_tb and p_prioritize_documents = 1 (Yes), the custom
243 -- API should first sort Supply Lines 1, 2, and 3 in one set. Then
244 -- it should sort Supply Lines 4 and 5.
245 -- Supply Lines before sorting by custom API
246 -- Supply Line 1: PO1
247 -- Supply Line 2: PO2
248 -- Supply Line 3: PO3
249 -- Supply Line 4: ASN1
250 -- Supply Line 5: ASN2
251 -- Supply Lines after sorting by custom API. (Note that the PO supply
252 -- lines all still come before the ASN ones).
253 -- Supply Line 1: PO3
254 -- Supply Line 2: PO1
255 -- Supply Line 3: PO2
256 -- Supply Line 4: ASN2
257 -- Supply Line 5: ASN1
261 -- in this table are the row records from the global temp table,
258 -- p_shopping_basket_tb Table of valid supply lines for crossdocking. The supply lines
259 -- all lie within the crossdock window of the demand and are valid
260 -- source types for crossdocking. The supply line records stored
262 -- wms_xdock_pegging_gtmp. They have the following data structure.
263 -- inventory_item_id NUMBER
264 -- xdock_source_code NUMBER,
265 -- source_type_id NUMBER
266 -- source_header_id NUMBER
267 -- source_line_id NUMBER
268 -- source_line_detail_id NUMBER
269 -- dock_start_time DATE
270 -- dock_mean_time DATE
271 -- dock_end_time DATE
272 -- expected_time DATE
273 -- quantity NUMBER
274 -- reservable_quantity NUMBER
275 -- uom_code VARCHAR2(3)
276 -- project_id NUMBER
277 -- task_id NUMBER
278 -- Note that this table can be sparsely populated if an exception
279 -- occurs while calculating the available to reserve quantity for the
280 -- supply lines.
281 --
282 -- IN OUT parameters:
283 --
284 -- Output parameters:
285 -- x_return_status
286 -- if the Sort_Supply_Lines API succeeds, the value is
287 -- fnd_api.g_ret_sts_success;
288 -- if there is an expected error, the value is
289 -- fnd_api.g_ret_sts_error;
290 -- if there is an unexpected error, the value is
291 -- fnd_api.g_ret_sts_unexp_error;
292 -- x_msg_count
293 -- if there are one or more errors, the number of error messages in the buffer
294 -- x_msg_data
295 -- if there is one and only one error, the error message
296 -- (See fnd_api package for more details about the above output parameters)
297 -- x_api_is_implemented
298 -- Indicates if the custom API is implemented or not (TRUE or FALSE BOOlEAN value)
299 -- x_sorted_order_tb
300 -- Output table that indicates what order the supply lines in p_shopping_basket_tb
301 -- are to be sorted. This table should be indexed by consecutive integers starting
302 -- with 1, i.e. 1, 2, 3, 4, ... The table entry with index 1 will contain the pointer
303 -- to the index value in p_shopping_basket_tb corresponding to the first supply line
304 -- record that should be consumed. The same logic applies to index 2, 3, 4, ... in
305 -- x_sorted_order_tb.
306 -- For example:
307 -- p_shopping_basket_tb(1) := SUPPLY1
308 -- p_shopping_basket_tb(3) := SUPPLY2
309 -- p_shopping_basket_tb(4) := SUPPLY3
310 -- p_shopping_basket_tb(7) := SUPPLY9
311 -- We want to sort these supplies and consume in this order:
312 -- SUPPLY3, SUPPLY1, SUPPLY9, SUPPLY2
313 -- The output x_sorted_order_tb should look like this:
314 -- x_sorted_order_tb(1) := 4
315 -- x_sorted_order_tb(2) := 1
316 -- x_sorted_order_tb(3) := 7
317 -- x_sorted_order_tb(4) := 3
318 -- If for some reason the custom logic does not want to use a supply line that exists
319 -- in p_shopping_basket_tb, just do not include that entry in x_sorted_order_tb. This output
320 -- table can have less than or an equal amount of entries as p_shopping_basket_tb. It can
321 -- never have more. Additionally, (very important!!!) there should not be multiple entries
322 -- in x_sorted_order_tb pointing to the same index value. If an invalid sorted order table
323 -- is returned, the custom logic will not be used to sort the shopping basket table.
324 PROCEDURE Sort_Supply_Lines
325 (p_wdd_release_record IN WSH_PR_CRITERIA.relRecTyp,
326 p_prioritize_documents IN NUMBER,
327 p_shopping_basket_tb IN WMS_XDock_Pegging_Pub.shopping_basket_tb,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2,
331 x_api_is_implemented OUT NOCOPY BOOLEAN,
332 x_sorted_order_tb OUT NOCOPY WMS_XDock_Pegging_Pub.sorted_order_tb);
333
334
335 -- API Name : Sort_Demand_Lines
336 -- Package : WMS_XDOCK_CUSTOM_APIS_PUB
337 -- Description : This API is used to sort an input table of valid demand lines for crossdocking.
338 -- This is called for Opportunistic Crossdocking if the crossdocking goal is 'Custom'.
339 -- The input table contains demand lines that are all valid for crossdocking to the
340 -- inputted MOL supply. The prioritize documents input comes from the crossdock criteria
341 -- used and indicates if we should maintain the relative ordering of the demand source
342 -- types in p_shopping_basket_tb.
343 --
344 -- Input parameters:
345 -- p_move_order_line_id Move Order line ID for the supply we are trying to crossdock.
346 -- p_prioritize_documents Flag indicating if the demand source documents should be
350 -- If the documents are to be prioritized, the demand lines in
347 -- prioritized or not. The possible values are:
348 -- 1 = Yes
349 -- 2 = No
351 -- the input p_shopping_basket_tb will already be sorted by the
352 -- demand source types in the order they should be consumed. In
353 -- this case, the custom API should sort the demand lines by the source
354 -- types to maintain the relative ordering of the demand documents.
355 -- For example, if the following are the demand lines returned in
356 -- p_shopping_basket_tb and p_prioritize_documents = 1 (Yes), the custom
357 -- API should first sort Demand Lines 1, 2, and 3 in one set. Then
358 -- it should sort Demand Lines 4 and 5.
359 -- Demand Lines before sorting by custom API
360 -- Demand Line 1: Scheduled Sales Order1
361 -- Demand Line 2: Scheduled Sales Order2
362 -- Demand Line 3: Scheduled Sales Order3
363 -- Demand Line 4: Backordered Internal Order1
364 -- Demand Line 5: Backordered Internal Order2
365 -- Demand Lines after sorting by custom API. (Note that the
366 -- Scheduled Sales Order demand lines all still come before the
367 -- Backordered Internal Order ones).
368 -- Demand Line 1: Scheduled Sales Order3
369 -- Demand Line 2: Scheduled Sales Order1
370 -- Demand Line 3: Scheduled Sales Order2
371 -- Demand Line 4: Backordered Internal Order2
372 -- Demand Line 5: Backordered Internal Order1
373 -- p_shopping_basket_tb Table of valid demand lines for crossdocking. The demand lines
374 -- all lie within the crossdock window of the demand and are valid
375 -- source types for crossdocking. The demand line records stored
376 -- in this table are the row records from the global temp table,
377 -- wms_xdock_pegging_gtmp. They have the following data structure.
378 -- inventory_item_id NUMBER
379 -- xdock_source_code NUMBER,
380 -- source_type_id NUMBER
381 -- source_header_id NUMBER
382 -- source_line_id NUMBER
383 -- source_line_detail_id NUMBER
384 -- dock_start_time DATE
385 -- dock_mean_time DATE
386 -- dock_end_time DATE
387 -- expected_time DATE
388 -- quantity NUMBER
389 -- reservable_quantity NUMBER
390 -- uom_code VARCHAR2(3)
391 -- project_id NUMBER
392 -- task_id NUMBER
393 -- Note that this table can be sparsely populated if an exception
394 -- occurs while calculating the available to reserve quantity for the
395 -- demand lines.
396 --
397 -- IN OUT parameters:
398 --
399 -- Output parameters:
400 -- x_return_status
401 -- if the Sort_Demand_Lines API succeeds, the value is
402 -- fnd_api.g_ret_sts_success;
403 -- if there is an expected error, the value is
404 -- fnd_api.g_ret_sts_error;
405 -- if there is an unexpected error, the value is
406 -- fnd_api.g_ret_sts_unexp_error;
407 -- x_msg_count
408 -- if there are one or more errors, the number of error messages in the buffer
409 -- x_msg_data
410 -- if there is one and only one error, the error message
411 -- (See fnd_api package for more details about the above output parameters)
412 -- x_api_is_implemented
413 -- Indicates if the custom API is implemented or not (TRUE or FALSE BOOlEAN value)
414 -- x_sorted_order_tb
415 -- Output table that indicates what order the demand lines in p_shopping_basket_tb
419 -- record that should be consumed. The same logic applies to index 2, 3, 4, ... in
416 -- are to be sorted. This table should be indexed by consecutive integers starting
417 -- with 1, i.e. 1, 2, 3, 4, ... The table entry with index 1 will contain the pointer
418 -- to the index value in p_shopping_basket_tb corresponding to the first demand line
420 -- x_sorted_order_tb.
421 -- For example:
422 -- p_shopping_basket_tb(1) := DEMAND1
423 -- p_shopping_basket_tb(3) := DEMAND2
424 -- p_shopping_basket_tb(4) := DEMAND3
425 -- p_shopping_basket_tb(7) := DEMAND9
426 -- We want to sort these demands and consume in this order:
427 -- DEMAND3, DEMAND1, DEMAND9, DEMAND2
428 -- The output x_sorted_order_tb should look like this:
429 -- x_sorted_order_tb(1) := 4
430 -- x_sorted_order_tb(2) := 1
431 -- x_sorted_order_tb(3) := 7
432 -- x_sorted_order_tb(4) := 3
433 -- If for some reason the custom logic does not want to use a demand line that exists
434 -- in p_shopping_basket_tb, just do not include that entry in x_sorted_order_tb. This output
435 -- table can have less than or an equal amount of entries as p_shopping_basket_tb. It can
436 -- never have more. Additionally, (very important!!!) there should not be multiple entries
437 -- in x_sorted_order_tb pointing to the same index value. If an invalid sorted order table
438 -- is returned, the custom logic will not be used to sort the shopping basket table.
439 PROCEDURE Sort_Demand_Lines
440 (p_move_order_line_id IN NUMBER,
441 p_prioritize_documents IN NUMBER,
442 p_shopping_basket_tb IN WMS_XDock_Pegging_Pub.shopping_basket_tb,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_msg_count OUT NOCOPY NUMBER,
445 x_msg_data OUT NOCOPY VARCHAR2,
446 x_api_is_implemented OUT NOCOPY BOOLEAN,
447 x_sorted_order_tb OUT NOCOPY WMS_XDock_Pegging_Pub.sorted_order_tb);
448
449
450 END WMS_XDOCK_CUSTOM_APIS_PUB;
451
[Home] [Help]