1 PACKAGE INV_MATERIAL_STATUS_GRP as
2 /* $Header: INVMSGRS.pls 120.6.12010000.2 2008/10/24 10:24:20 aambulka ship $ */
3
4
5 ------------------------------------------------------------------------------
6 -- Function
7 -- is_trx_allowed
8 --
9 -- Description
10 -- check to see if the input status allows the input transaction
11 -- type or not
12 --
13 -- Return
14 -- 'Y': allowed or any error occurred
15 -- 'N': disallowed
16 -- Input Paramters
17 --
18 -- p_status_id input status id
19 -- p_transaction_type_id input transaction_type
20 --
21 -- Output Parameters
22 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
23 -- = fnd_api.g_ret_sts_exc_error, if an expected
24 -- error occurred
25 -- = fnd_api.g_ret_sts_unexp_error, if
26 -- an unexpected error occurred
27 --
28 -- x_msg_count Number of error message in the error message
29 -- list
30 --
31 -- x_msg_data If the number of error message in the error
32 -- message list is one, the error message
33 -- is in this output parameter
34 --
35 ------------------------------------------------------------------------------
36
37 FUNCTION is_trx_allowed
38 (
39 p_status_id IN NUMBER
40 , p_transaction_type_id IN NUMBER
41 , x_return_status OUT NOCOPY VARCHAR2
42 , x_msg_count OUT NOCOPY NUMBER
43 , x_msg_data OUT NOCOPY VARCHAR2
44 ) return varchar2;
45 ------------------------------------------------------------------------------
46 --LPN Status Project
47 -- Function
48 -- is_status_applicable_lpns
49 --
50 -- Description
51 -- check to see for the validation of the on-hand status for this transaction
52 --
53 -- Return
54 -- 0 would indicate that both the validations are successful and the transaction can be performed
55 -- 1 would indicate that the transaction would result in Mixed Status in the LPN
56 -- or in the other LPNs in the Outer LPN or in the Outer LPN itself.
57 -- The message WMS_RESULTS_MIXED_STATUS is thrown to the user
58 -- 2 indicate that this transaction is not allowed for the status of the source material or for the destination
59 -- The message WMS_DISALLOW_TRANSACTION is thrown to the user
60 --
61 -- Input Paramters
62 --
63 -- p_wms_installed input WMS Installed parameter
64 -- p_trx_status_enabled input transaction status enabled or not
65 -- p_trx_type_id input transaction type
66 -- p_lot_status_enabled input lot status enabled or not
67 -- p_serial_status_enabled input serial status or not
68 -- p_organization_id input organization id
69 -- p_inventory_item_id input inventory item identifier
70 -- p_sub_code input subinventory
71 -- p_locator_id input locator
72 -- p_lot_number input lot number
73 -- p_serial_number input serial number
74 -- p_object_type input object type
75 -- p_fromlpn_id input from LPN ID
76 -- p_xfer_lpn_id input transfer LPN ID
77 -- p_xfer_sub_code input transfer sub-inventory code
78 -- p_xfer_locator_id input transfer locator
79 -- p_xfer_org_id input transfer organization
80 ------------------------------------------------------------------------------
81
82
83
84 FUNCTION is_status_applicable_lpns
85 (p_wms_installed IN VARCHAR2,
86 p_trx_status_enabled IN NUMBER,
87 p_trx_type_id IN NUMBER,
88 p_lot_status_enabled IN VARCHAR2,
89 p_serial_status_enabled IN VARCHAR2,
90 p_organization_id IN NUMBER,
91 p_inventory_item_id IN NUMBER,
92 p_sub_code IN VARCHAR2,
93 p_locator_id IN NUMBER,
94 p_lot_number IN VARCHAR2,
95 p_serial_number IN VARCHAR2,
96 p_object_type IN VARCHAR2,
97 p_fromlpn_id IN NUMBER,
98 p_xfer_lpn_id IN NUMBER,
99 p_xfer_sub_code IN VARCHAR2,
100 p_xfer_locator_id IN NUMBER,
101 p_xfer_org_id IN NUMBER)
102 RETURN NUMBER;
103 ------------------------------------------------------------------------------
104 ------------------------------------------------------------------------------
105 -- Procedure
106 -- get_lot_serial_status_control
107 --
108 -- Description
109 -- Inquire if the item is under lot status controlled, serial status controlled
110 -- and corresponding default statuses
111 --
112 -- Input Paramters
113 --
114 -- p_organization_id organization the item resides in
115 -- p_inventory_item_id given item id we query for
116 --
117 -- Output Parameters
118 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
119 -- = fnd_api.g_ret_sts_exc_error, if an expected
120 -- error occurred
121 -- = fnd_api.g_ret_sts_unexp_error, if
122 -- an unexpected error occurred
123 --
124 -- x_msg_count Number of error message in the error message
125 -- list
126 --
127 -- x_msg_data If the number of error message in the error
128 -- message list is one, the error message
129 -- is in this output parameter
130 -- x_lot_status_enabled Indicate if the item is lot status controlled
131 -- 'Y': YES 'N': NO
132 -- x_default_lot_status_id default lot status ID
133 -- x_serial_status_enabled Indicate if the item is serial status controlled
134 -- 'Y': YES 'N': NO
135 -- x_default_serial_status_id default serial status ID
136 ------------------------------------------------------------------------------
137 PROCEDURE get_lot_serial_status_control
138 (
139 p_organization_id IN NUMBER
140 , p_inventory_item_id IN NUMBER
141 , x_return_status OUT NOCOPY VARCHAR2
142 , x_msg_count OUT NOCOPY NUMBER
143 , x_msg_data OUT NOCOPY VARCHAR2
144 , x_lot_status_enabled OUT NOCOPY VARCHAR2
145 , x_default_lot_status_id OUT NOCOPY NUMBER
146 , x_serial_status_enabled OUT NOCOPY VARCHAR2
147 , x_default_serial_status_id OUT NOCOPY NUMBER
148 );
149
150 ------------------------------------------------------------------------------
151 -- Function
152 -- is_status_applicable
153 --
154 -- Description
155 -- check if the sub, locator, lot, serial is applicable for certain transaction type
156 -- based on its status
157 --
158 -- Input Paramters
159 --
160 -- p_wms_installed Indicate if WMS is installed
161 -- passing 'TRUE' or 'FALSE'
162 -- p_trx_status_enabled Indicate if the transaction type is status control
163 -- Enabled or not
164 -- passing 1 for enabled, 2 for disabled
165 -- this is optional, passing this value can increase the
166 -- the processing speed
167 -- p_trx_type_id transaction type id
168 -- p_lot_status_enabled Indicate if the item is lot status control
169 -- Enabled or not
170 -- passing 'Y' for enabled, 'N' for disabled
171 -- this is optional, passing this value can increase the
172 -- the processing speed
173 -- p_serial_status_enabled Indicate if the item is serial status control
174 -- Enabled or not
175 -- passing 'Y' for enabled, 'N' for disabled
176 -- this is optional, passing this value can increase the
177 -- the processing speed
178 -- p_organization_id organization id the item resides in
179 -- p_inventory_item_id given item id we query for
180 -- p_sub_code subinventory code
181 -- p_locator_id locator id
182 -- p_lot_number lot number
183 -- p_serial_number serial number
184 -- p_lpn_id lpn_id -- Onhand Material Status Support
185 -- p_object_type this parameter is for performance purpose
186 -- must be specified to get the proper function
187 -- 'Z' checking zone (subinventory)
188 -- 'L' checking locator
189 -- 'O' checking lot
190 -- 'S' checking serial
191 -- 'A' checking all including sub, locator, lot, serial
192 --
193 --
194 -- Return:
195 -- 'Y' the given object's status allow the given transaction type or any error occurred
196 -- 'N' the given object's status disallow the given transaction type
197 --
198 -- Usage:
199 -- p_wms_installed must be specified.
200 -- TO check any object (sub, locator, lot or serial) is applicable or not,
201 -- p_trx_type_id, p_organization_id, p_object_type must be specified.
202 -- Additionally,
203 -- to check subinventory, p_sub_code must be specified;
204 -- to check locator, p_locator_id must be specified;
205 -- to check lot,p_inventory_item_id, p_lot_number must be specified
206 -- to check serial, p_inventory_item_id, p_serial_number must be specified
207 --
208 -- p_trx_status_enabled is optional for all checkings
209 -- p_lot_status_enabledled is optional for checking lot status,
210 -- p_serial_status_enabled is optional for checking serial status
211 -- The default value is NULL for all input parameters except p_wms_installed
212 -------------------------------------------------------------------------------------------------------
213
214 Function is_status_applicable(p_wms_installed IN VARCHAR2:=NULL,
215 p_trx_status_enabled IN NUMBER:=NULL,
216 p_trx_type_id IN NUMBER:=NULL,
217 p_lot_status_enabled IN VARCHAR2:=NULL,
218 p_serial_status_enabled IN VARCHAR2:=NULL,
219 p_organization_id IN NUMBER:=NULL,
220 p_inventory_item_id IN NUMBER:=NULL,
221 p_sub_code IN VARCHAR2:=NULL,
222 p_locator_id IN NUMBER:=NULL,
223 p_lot_number IN VARCHAR2:=NULL,
224 p_serial_number IN VARCHAR2:=NULL,
225 p_object_type IN VARCHAR2:=NULL)
226 return varchar2;
227
228 ------------------------------------------------------------------------------
229 -- Overloaded function for the Onhand Material Status Support
230 -- Function is_status_applicable
231 -- Description overloaded function.
232 -- p_lpn_id new parameter lpn_id
233 -------------------------------------------------------------------------------------------------------
234
235 Function is_status_applicable(p_wms_installed IN VARCHAR2:=NULL,
236 p_trx_status_enabled IN NUMBER:=NULL,
237 p_trx_type_id IN NUMBER:=NULL,
238 p_lot_status_enabled IN VARCHAR2:=NULL,
239 p_serial_status_enabled IN VARCHAR2:=NULL,
240 p_organization_id IN NUMBER:=NULL,
241 p_inventory_item_id IN NUMBER:=NULL,
242 p_sub_code IN VARCHAR2:=NULL,
243 p_locator_id IN NUMBER:=NULL,
244 p_lot_number IN VARCHAR2:=NULL,
245 p_serial_number IN VARCHAR2:=NULL,
246 p_object_type IN VARCHAR2:=NULL,
247 p_lpn_id IN NUMBER) -- Onhand Material Status Support
248 return varchar2;
249
250 ------------------------------------------------------------------------------
251
252 -- Procedure
253 -- update_status
254 --
255 -- Description
256 -- update status of sub, locator, lot, serial
257 --
258 -- Input Paramters
259 -- p_api_version_number API version number (current version is 1.0)
260 -- p_init_msg_lst Whether initialize the error message list or
261 -- not.
262 -- Should be fnd_api.g_false or fnd_api.g_true
263 -- p_update_method update method, refer to the global constant
264 -- definition in INVMSPUB.pls
265 -- p_status_id status_id which should be changed to
266 -- p_organization_id organization the item resides in
267 -- p_inventory_item_id given item id we query for
268 -- p_sub_code subinventory code
269 -- p_locator_id locator id
270 -- p_lot_number lot number
271 -- p_serial_number serial number
272 -- p_lpn_id lpn id -- Onhand Material Status Support
273 -- p_object_type this parameter is for performance purpose
274 -- must be specified for the proper function
275 -- 'Z' checking zone (subinventory)
276 -- 'L' checking locator
277 -- 'O' checking lot
278 -- 'S' checking serial
279 -- p_update_reason_id update reason id which is a primary key in
280 -- MTL_transaction_reasons
281 -- p_initial_status_flag To track the first status assigned to the entity.
282
283 ----------------------------------------------------------------------------------------
284 PROCEDURE update_status
285 ( p_api_version_number IN NUMBER
286 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
287 , x_return_status OUT NOCOPY VARCHAR2
288 , x_msg_count OUT NOCOPY NUMBER
289 , x_msg_data OUT NOCOPY VARCHAR2
290 , p_update_method IN NUMBER
291 , p_status_id IN NUMBER
292 , p_organization_id IN NUMBER
293 , p_inventory_item_id IN NUMBER:=NULL
294 , p_sub_code IN VARCHAR2:=NULL
295 , p_locator_id IN NUMBER:=NULL
296 , p_lot_number IN VARCHAR2:=NULL
297 , p_serial_number IN VARCHAR2:=NULL
298 , p_to_serial_number IN VARCHAR2:=NULL
299 , p_object_type IN VARCHAR2
300 , p_update_reason_id IN NUMBER:=NULL
301 , p_lpn_id IN NUMBER:=NULL -- Onhand Material Status Support
302 , p_initial_status_flag IN VARCHAR2:='N' -- Onhand Material Status Support
303 );
304
305 ----------------------------------------------------------------------------------------
306 --Function added for Bug# 2879164
307 FUNCTION loc_valid_for_item(p_loc_id NUMBER:=NULL,
308 p_org_id NUMBER:=NULL,
309 p_inventory_item_id NUMBER:=NULL,
310 p_sub_code VARCHAR2:=NULL)
311 RETURN VARCHAR2;
312 ----------------------------------------------------------------------------------------
313 --Function added for Bug# 2879164
314 FUNCTION sub_valid_for_item(p_org_id NUMBER:=NULL,
315 p_inventory_item_id NUMBER:=NULL,
316 p_sub_code VARCHAR2:=NULL)
317 RETURN VARCHAR2;
318 -----------------------------------------------------------------------------------------
319 -- Function added for On-hand Material Status support.
320 -- This function returns the defaul material status for an onhand record in the table MOQD.
321 FUNCTION get_default_status( p_organization_id IN NUMBER,
322 p_inventory_item_id IN NUMBER,
323 p_sub_code IN VARCHAR2,
324 p_loc_id IN NUMBER :=NULL,
325 p_lot_number IN VARCHAR2 :=NULL,
326 p_lpn_id IN NUMBER := NULL,
327 p_transaction_action_id IN NUMBER := NULL,
328 p_src_status_id IN NUMBER := NULL,
329 p_lock_id IN NUMBER := 0,
330 p_header_id IN NUMBER :=NULL)
331 RETURN NUMBER;
332 -----------------------------------------------------------------------------------------
333 -- Function added for On-hand Material Status support.
334 -- This function returns the defaul material status for an onhand record in the table MOQD
335 -- for the concurrent program.
336
337 Function get_default_status_conc(p_organization_id IN NUMBER,
338 p_inventory_item_id IN NUMBER,
339 p_sub_code IN VARCHAR2,
340 p_loc_id IN NUMBER :=NULL,
341 p_lot_number IN VARCHAR2 :=NULL,
342 p_lpn_id IN NUMBER := NULL)
343 RETURN NUMBER;
344 -----------------------------------------------------------------------------------------
345
346 -- Function added for On-hand Material Status support.
347 -- This function checks whether an item is locator controlled or not.
348 FUNCTION get_locator_control
349 ( p_org_id NUMBER
350 , p_inventory_item_id NUMBER
351 , p_sub_code VARCHAR2
352 ) RETURN NUMBER;
353
354
355 -----------------------------------------------------------------------------------------
356 -- Function added for On-hand Material Status support.
357 -- This function returns the transaction action id for a given transaction type id.
358 FUNCTION get_action_id( p_trx_type_id NUMBER)
359 RETURN NUMBER;
360
361 -----------------------------------------------------------------------------------------
362
363 -----------------------------------------------------------------------------------------
364 --Bug #6633612, Adding following procedure for onhand status support project
365 PROCEDURE get_onhand_status_id
366 ( p_organization_id IN NUMBER
367 ,p_inventory_item_id IN NUMBER
368 ,p_subinventory_code IN VARCHAR2
369 ,p_locator_id IN NUMBER := NULL
370 ,p_lot_number IN VARCHAR2 := NULL
371 ,p_lpn_id IN NUMBER := NULL
372 ,x_onhand_status_id OUT NOCOPY NUMBER );
373 ------------------------------------------------------------------------------------------
374
375 -----------------------------------------------------------------------------------------
376 --Bug #6633612, Adding following procedure for onhand status support project
377 -- This procedure validates the material status with respect to transfer transactions
378 PROCEDURE check_move_diff_status(
379 p_org_id IN NUMBER
380 , p_inventory_item_id IN NUMBER
381 , p_subinventory_code IN VARCHAR2
382 , p_locator_id IN NUMBER DEFAULT NULL
383 , p_transfer_org_id IN NUMBER DEFAULT NULL
384 , p_transfer_subinventory IN VARCHAR2 DEFAULT NULL
385 , p_transfer_locator_id IN NUMBER DEFAULT NULL
386 , p_lot_number IN VARCHAR2 DEFAULT NULL
387 , p_transaction_action_id IN NUMBER
388 , p_object_type IN VARCHAR2
389 , p_lpn_id IN NUMBER DEFAULT NULL
390 , p_demand_src_header_id IN NUMBER DEFAULT NULL
391 , p_revision IN VARCHAR2 DEFAULT NULL
392 , x_return_status OUT NOCOPY VARCHAR2
393 , x_msg_count OUT NOCOPY NUMBER
394 , x_msg_data OUT NOCOPY VARCHAR2
395 , x_post_action OUT NOCOPY VARCHAR2
396 );
397 --added the spec for the procedure for getting the lpn status as a part of lpn status project
398 ------------------------------------------------------------------------------
399 -- Procedure
400 -- get_lpn_status
401 --
402 -- Description
403 -- To get the status of a LPN
404
405 -- Input Paramters
406 --
407 -- p_organization_id = organization id of the lpn
408 -- p_lpn_id = lpn id of the lpn
409 -- p_sub_code = subinventory code of the lpn
410 -- p_loc_id = locator id of lpn
411 -- p_lpn_context = Lpn context
412
413 --
414 -- Output Parameters
415 -- x_return_status_id = status id if LPN has unique status else -1
416 -- x_return_status_code = status code it lpn has unique status else translated message for 'Mixed'
417 ------------------------------------------------------------------------------
418
419 PROCEDURE get_lpn_status
420 (
421 p_organization_id IN NUMBER,
422 p_lpn_id IN NUMBER,
423 p_sub_code IN VARCHAR2 := NULL,
424 p_loc_id IN NUMBER := NULL,
425 p_lpn_context IN NUMBER,
426 x_return_status_id OUT NOCOPY NUMBER,
427 x_return_status_code OUT NOCOPY VARCHAR2
428 );
429
430 -----------------------------------------------------------------------------------------
431 /* -- LPN Status Project --*/
432 FUNCTION Status_Commingle_Check (
433 p_item_id IN NUMBER
434 , p_lot_number IN VARCHAR2 := NULL
435 , p_org_id IN NUMBER
436 , p_trx_action_id IN NUMBER
437 , p_subinv_code IN VARCHAR2
438 , p_tosubinv_code IN VARCHAR2 := NULL
439 , p_locator_id IN NUMBER := NULL
440 , p_tolocator_id IN NUMBER := NULL
441 , p_xfr_org_id IN NUMBER := NULL
442 , p_from_lpn_id IN NUMBER := NULL
443 , p_cnt_lpn_id IN NUMBER := NULL
444 , p_xfr_lpn_id IN NUMBER := NULL )
445
446 RETURN VARCHAR2;
447 ----------------------------------------------------------------------------------------
448 FUNCTION is_trx_allow_lpns(
449 p_wms_installed IN VARCHAR2,
450 p_trx_status_enabled IN NUMBER,
451 p_trx_type_id IN NUMBER,
452 p_lot_status_enabled IN VARCHAR2,
453 p_serial_status_enabled IN VARCHAR2,
454 p_organization_id IN NUMBER,
455 p_inventory_item_id IN NUMBER,
456 p_sub_code IN VARCHAR2,
457 p_locator_id IN NUMBER,
458 p_lot_number IN VARCHAR2,
459 p_serial_number IN VARCHAR2,
460 p_object_type IN VARCHAR2,
461 p_fromlpn_id IN NUMBER,
462 p_xfer_lpn_id IN NUMBER,
463 p_xfer_sub_code IN VARCHAR2,
464 p_xfer_locator_id IN NUMBER,
465 p_xfer_org_id IN NUMBER)
466 RETURN NUMBER;
467 ----------------------------------------------------------------------------------------
468 -- Function added for On-hand Material Status support, Bug 6798024
469 -- This will insert the newly created onhand record's status into the
470 -- history table:mtl_material_status_history
471 Procedure insert_status_history(p_organization_id IN NUMBER,
472 p_inventory_item_id IN NUMBER,
473 p_sub_code IN VARCHAR2,
474 p_loc_id IN NUMBER :=NULL,
475 p_lot_number IN VARCHAR2 :=NULL,
476 p_lpn_id IN NUMBER := NULL,
477 p_status_id IN NUMBER := NULL,
478 p_lock_id IN NUMBER := 0);
479 ----------------------------------------------------------------------------------------
480 /* Bug 6918409: Added a wrapper to call the is_trx_allowed function */
481 FUNCTION is_trx_allowed_wrap
482 (
483 p_status_id IN NUMBER
484 , p_transaction_type_id IN NUMBER
485 ) return varchar2;
486 -----------------------------------------------------------------------------------------
487
488 END INV_MATERIAL_STATUS_GRP;
[Home] [Help]