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