1 package body WMS_RE_Custom_PUB as
2 /* $Header: WMSPPPUB.pls 120.1 2005/05/27 02:01:56 appldev $ */
3
4 -- File : WMSPPPUB.pls
5 -- Content : WMS_RE_Custom_PUB package body
6 -- Description : Customizable stub procedures and functions called during rules
7 -- engine run.
8 -- Notes :
9 -- Modified : 02/08/99 mzeckzer created
10
11 g_pkg_name constant varchar2(30) := 'WMS_RE_Custom_PUB';
12
13 -- Start of comments
14 -- API name : GetTotalLocationCapacity
15 -- Type : Public
16 -- Function : Calculates and returns the total capacity of a location
17 -- ( sub or sub/locator ) in a customer-specific manner
18 -- This function is made available to be used within put away
19 -- rule setup.
20 -- Pre-reqs : none
21 -- Parameters :
22 -- p_organization_id in number required default = fnd_api.g_miss_num
23 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
24 -- p_locator_id in number optional default = null
25 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
26 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
27 -- return value out number
28 -- Version : not tracked
29 -- Notes : capacity should be returned as measured in txn UOM
30 -- End of comments
31
32 function GetTotalLocationCapacity (
33 p_organization_id number := g_miss_num
34 ,p_subinventory_code varchar2 := g_miss_char
35 ,p_locator_id number := null
36 ,p_inventory_item_id number := g_miss_num
37 ,p_transaction_uom varchar2 := g_miss_char
38 ) return number is
39 l_total_capacity number := 0;
40
41 begin
42 -- validate input parameters
43 if p_organization_id is null
44 or p_organization_id = g_miss_num
45 or p_subinventory_code is null
46 or p_subinventory_code = g_miss_char
47 or p_inventory_item_id is null
48 or p_inventory_item_id = g_miss_num
49 or p_transaction_uom is null
50 or p_transaction_uom = g_miss_char
51 then
52 return(null);
53 end if;
54
55 -- customer-specific logic
56
57 -- return calculated total capacity
58 return(l_total_capacity);
59
60 end GetTotalLocationCapacity;
61
62 -- Start of comments
63 -- API name : GetOccupiedLocationCapacity
64 -- Type : Public
65 -- Function : Calculates and returns the occupied capacity of a location
66 -- ( sub or sub/locator ) in a customer-specific manner
67 -- This function is made available to be used within put away
68 -- rule setup.
69 -- Pre-reqs : none
70 -- Parameters :
71 -- p_organization_id in number required default = fnd_api.g_miss_num
72 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
73 -- p_locator_id in number optional default = null
74 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
75 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
76 -- return value out number
77 -- Version : not tracked
78 -- Notes : capacity should be returned as measured in txn UOM
79 -- End of comments
80
81 function GetOccupiedLocationCapacity (
82 p_organization_id number := g_miss_num
83 ,p_subinventory_code varchar2 := g_miss_char
84 ,p_locator_id number := null
85 ,p_inventory_item_id number := g_miss_num
86 ,p_transaction_uom varchar2 := g_miss_char
87 ) return number is
88 l_occupied_capacity number := 0;
89
90 begin
91 -- validate input parameters
92 if p_organization_id is null
93 or p_organization_id = g_miss_num
94 or p_subinventory_code is null
95 or p_subinventory_code = g_miss_char
96 or p_inventory_item_id is null
97 or p_inventory_item_id = g_miss_num
98 or p_transaction_uom is null
99 or p_transaction_uom = g_miss_char
100 then
101 return(null);
102 end if;
103
104 -- customer-specific logic
105
106 -- return calculated occupied capacity
107 return(l_occupied_capacity);
108
109 end GetOccupiedLocationCapacity;
110
111 -- Start of comments
112 -- API name : GetAvailableLocationCapacity
113 -- Type : Public
114 -- Function : Calculates and returns the available capacity of a location
115 -- ( sub or sub/locator ) in a customer-specific manner
116 -- This function is made available to be used within put away
117 -- rule setup.
118 -- Pre-reqs : none
119 -- Parameters :
120 -- p_organization_id in number required default = fnd_api.g_miss_num
121 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
122 -- p_locator_id in number optional default = null
123 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
124 -- p_transaction_quantity in number required default = fnd_api.g_miss_num
125 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
126 -- return value out number
127 -- Version : not tracked
128 -- Notes : capacity must be returned as measured in txn UOM
129 -- End of comments
130
131 function GetAvailableLocationCapacity (
132 p_organization_id number := g_miss_num
133 ,p_subinventory_code varchar2 := g_miss_char
134 ,p_locator_id number := null
135 ,p_inventory_item_id number := g_miss_num
136 ,p_transaction_quantity number := g_miss_num
137 ,p_transaction_uom varchar2 := g_miss_char
138 ) return number is
139 l_available_capacity number := 0;
140
141 begin
142 -- validate input parameters
143 if p_organization_id is null
144 or p_organization_id = g_miss_num
145 or p_subinventory_code is null
146 or p_subinventory_code = g_miss_char
147 or p_inventory_item_id is null
148 or p_inventory_item_id = g_miss_num
149 or p_transaction_quantity is null
150 or p_transaction_quantity = g_miss_num
151 or p_transaction_uom is null
152 or p_transaction_uom = g_miss_char
153 then
154 return(null);
155 end if;
156
157 -- customer-specific logic
158
159 -- return calculated available capacity
160 return(l_available_capacity);
161
162 end GetAvailableLocationCapacity;
163
164 -- Start of comments
165 -- API name : GetRemainingLocationCapacity
166 -- Type : Public
167 -- Function : Calculates and returns the occupied capacity of a location
168 -- ( sub or sub/locator ) in a customer-specific manner
169 -- This function is made available to be used within put away
170 -- rule setup.
171 -- Pre-reqs : none
172 -- Parameters :
173 -- p_organization_id in number required default = fnd_api.g_miss_num
174 -- p_subinventory_code in varchar2 required default = fnd_api.g_miss_char
175 -- p_locator_id in number optional default = null
176 -- p_inventory_item_id in number required default = fnd_api.g_miss_num
177 -- p_transaction_quantity in number required default = fnd_api.g_miss_num
178 -- p_transaction_uom in varchar2 required default = fnd_api.g_miss_char
179 -- return value out number
180 -- Version : not tracked
181 -- Notes : capacity should be returned as measured in txn UOM
182 -- End of comments
183
184 function GetRemainingLocationCapacity (
185 p_organization_id number := g_miss_num
186 ,p_subinventory_code varchar2 := g_miss_char
187 ,p_locator_id number := null
188 ,p_inventory_item_id number := g_miss_num
189 ,p_transaction_quantity number := g_miss_num
190 ,p_transaction_uom varchar2 := g_miss_char
191 ) return number is
192 l_remaining_capacity number := 0;
193
194 begin
195 -- validate input parameters
196 if p_organization_id is null
197 or p_organization_id = g_miss_num
198 or p_subinventory_code is null
199 or p_subinventory_code = g_miss_char
200 or p_inventory_item_id is null
201 or p_inventory_item_id = g_miss_num
202 or p_transaction_quantity is null
203 or p_transaction_quantity = g_miss_num
204 or p_transaction_uom is null
205 or p_transaction_uom = g_miss_char
206 then
207 return(null);
208 end if;
209
210 -- customer-specific logic
211
212 -- return calculated remaining capacity
213 return(l_remaining_capacity);
214
215 end GetRemainingLocationCapacity;
216
217 -- Start of comments
218 -- API name : SearchForStrategy
219 -- Type : Public
220 -- Function : Searches for a wms strategy assignment to a
221 -- customer-defined business object in a customer-specific
222 -- manner.
223 -- This procedure gets called just before the standard algorithm
224 -- which searches for strategy assignments to system-defined
225 -- business objects.
226 -- Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
227 -- identified by parameters p_transaction_temp_id and
228 -- p_type_code ( already validated by calling procedure )
229 -- set up strategy assignment in WMS_STRATEGY_ASSIGNMENTS
230 -- Parameters :
231 -- p_init_msg_list in varchar2 optional default = fnd_api.g_false
232 -- x_return_status out varchar2(1)
233 -- x_msg_count out number
234 -- x_msg_data out varchar2(2000)
235 -- p_transaction_temp_id in number required default = fnd_api.g_miss_num
236 -- p_type_code in number required default = fnd_api.g_miss_num
237 -- x_strategy_id out number
238 -- Version : not tracked
239 -- Notes : type code of returned strategy has to match type code
240 -- parameter
241 -- End of comments
242
243 procedure SearchForStrategy (
244 p_init_msg_list in varchar2 := fnd_api.g_false
245 ,x_return_status out NOCOPY varchar2
246 ,x_msg_count out NOCOPY number
247 ,x_msg_data out NOCOPY varchar2
248 ,p_transaction_temp_id in number := fnd_api.g_miss_num
249 ,p_type_code in number := fnd_api.g_miss_num
250 ,x_strategy_id out NOCOPY number
251 ) is
252
253 -- API standard variables
254 l_api_name constant varchar2(30) := 'SearchForStrategy';
255
256 begin
257
258 -- Initialize message list if p_init_msg_list is set to TRUE
259 if fnd_api.to_boolean( p_init_msg_list ) then
260 fnd_msg_pub.initialize;
261 end if;
262
263 -- Initialize API return status to success
264 x_return_status := fnd_api.g_ret_sts_success;
265
266 -- Search for Strategy in a custom-specific manner, using
267 -- View WMS_STRATEGY_MAT_TXN_TMP_V ( Actual transaction values )
268 -- Table WMS_STRATEGY_ASSIGNMENTS ( Setup data )
269 -- ...
270 -- ...
271 -- ...
272 -- By default, no Strategy is found using custom-specific procedure
273 x_strategy_id := null;
274
275 if x_strategy_id is null then
276 -- Message: No strategy found using custom-specific stub procedure
277 raise fnd_api.g_exc_error;
278 end if;
279
280 -- Standard call to get message count and if count is 1, get message info
281 fnd_msg_pub.count_and_get( p_count => x_msg_count
282 ,p_data => x_msg_data );
283
284 exception
285 when fnd_api.g_exc_error then
286 x_return_status := fnd_api.g_ret_sts_error;
287 fnd_msg_pub.count_and_get( p_count => x_msg_count
288 ,p_data => x_msg_data );
289
290 when fnd_api.g_exc_unexpected_error then
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 fnd_msg_pub.count_and_get( p_count => x_msg_count
293 ,p_data => x_msg_data );
294
295 when others then
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
298 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
299 end if;
300 fnd_msg_pub.count_and_get( p_count => x_msg_count
301 ,p_data => x_msg_data );
302
303 end SearchForStrategy;
304
305
306
307 --*************************************************************************
308 /**
309
310 API name : SearchForStrategy
311 Type : Public
312 Function : Searches for a wms strategy/rule/value assignment to a
313 customer-defined business object in a customer-specific
314 manner.
315 Pre-reqs : transaction record in WMS_STRATEGY_MAT_TXN_TMP_V uniquely
316 identified by parameters p_transaction_temp_id and
317 p_type_code ( already validated by calling procedure )
318 set up strategy assignment in WMS_STRATEGY_ASSIGNMENTS
319 Parameters :
320 p_init_msg_list in varchar2 optional default = fnd_api.g_false
321 x_return_status out varchar2(1)
322 x_msg_count out number
323 x_msg_data out varchar2(2000)
324 p_transaction_temp_id in number required default = fnd_api.g_miss_num
325 p_type_code in number required default = fnd_api.g_miss_num
326 x_return_type out varchar2 'V' for Value , 'R' for Rule , 'S' for strategy
327 ,x_return_type_id out number
328 Notes : type code of returned strategy has to match type code
329 parameter
330 */
331 procedure SearchForStrategy (
332 p_init_msg_list in varchar2 := fnd_api.g_false
333 ,x_return_status out NOCOPY varchar2
334 ,x_msg_count out NOCOPY number
335 ,x_msg_data out NOCOPY varchar2
336 ,p_transaction_temp_id in number := fnd_api.g_miss_num
337 ,p_type_code in number := fnd_api.g_miss_num
338 ,x_return_type out NOCOPY varchar2 -- 'V' for Value , 'R' for Rule , 'S' for strategy
339 ,x_return_type_id out NOCOPY number
340 ) is
341
342 -- API standard variables
343 l_api_name constant varchar2(30) := 'SearchForStrategy';
344
345 begin
346
347 -- Initialize message list if p_init_msg_list is set to TRUE
348 if fnd_api.to_boolean( p_init_msg_list ) then
349 fnd_msg_pub.initialize;
350 end if;
351
352 -- Initialize API return status to success
353 x_return_status := fnd_api.g_ret_sts_success;
354
355 -- Search for Strategy in a custom-specific manner, using
356 -- View WMS_STRATEGY_MAT_TXN_TMP_V ( Actual transaction values )
357 -- Table WMS_STRATEGY_ASSIGNMENTS ( Setup data )
358 -- ...
359 -- ...
360 -- ...
361 -- By default, no Strategy/Rule/Value is found using custom-specific procedure
362 x_return_type :=NULL;
363 x_return_type_id := null;
364
365 if x_return_type_id is null then
366 -- Message: No strategy/Rule/Value found using custom-specific stub procedure
367 raise fnd_api.g_exc_error;
368 end if;
369
370 -- Standard call to get message count and if count is 1, get message info
371 fnd_msg_pub.count_and_get( p_count => x_msg_count
372 ,p_data => x_msg_data );
373
374 exception
375 when fnd_api.g_exc_error then
376 x_return_status := fnd_api.g_ret_sts_error;
377 fnd_msg_pub.count_and_get( p_count => x_msg_count
378 ,p_data => x_msg_data );
379
380 when fnd_api.g_exc_unexpected_error then
381 x_return_status := fnd_api.g_ret_sts_unexp_error;
382 fnd_msg_pub.count_and_get( p_count => x_msg_count
383 ,p_data => x_msg_data );
384
385 when others then
386 x_return_status := fnd_api.g_ret_sts_unexp_error;
387 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
388 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
389 end if;
390 fnd_msg_pub.count_and_get( p_count => x_msg_count
391 ,p_data => x_msg_data );
392
393 end SearchForStrategy;
394
395
396
397 end WMS_RE_Custom_PUB;