1 PACKAGE INV_TXN_VALIDATIONS AUTHID CURRENT_USER AS
2 /* $Header: INVMWAVS.pls 120.5.12020000.2 2013/01/17 07:15:10 avrose ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 TYPE t_Item_Out IS RECORD(
7
8 Inventory_Item_Id NUMBER,
9 Description VARCHAR2(240),
10 Revision_Qty_Control_Code NUMBER,
11 Lot_Control_Code NUMBER,
12 Serial_Number_Control_Code NUMBER,
13 Restrict_Locators_Code NUMBER,
14 Location_Control_Code NUMBER,
15 Restrict_Subinventories_Code NUMBER);
16
17 TYPE t_SN_Out IS RECORD(
18 Current_Locator_Id NUMBER,
19 Current_Subinventory_Code VARCHAR2(10),
20 Revision VARCHAR2(3),
21 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
22 Lot_Number VARCHAR2(80));
23
24
25
26
27 PROCEDURE VALIDATE_ITEM(x_Inventory_Item_Id OUT NOCOPY NUMBER,
28 x_Description OUT NOCOPY VARCHAR2,
29 x_Revision_Qty_Control_Code OUT NOCOPY NUMBER,
30 x_Lot_Control_Code OUT NOCOPY NUMBER,
31 x_Serial_Number_Control_Code OUT NOCOPY NUMBER,
32 x_Restrict_Locators_Code OUT NOCOPY NUMBER,
33 x_Location_Control_Code OUT NOCOPY NUMBER,
34 x_Restrict_Subinventories_Code OUT NOCOPY NUMBER,
35 x_Message OUT NOCOPY VARCHAR2,
36 x_Status OUT NOCOPY VARCHAR2,
37 p_Organization_Id IN NUMBER,
38 p_Concatenated_Segments IN VARCHAR2);
39
40
41
42 PROCEDURE VALIDATE_SERIAL(x_Current_Locator_Id OUT NOCOPY NUMBER,
43 x_Concatenated_Segments OUT NOCOPY VARCHAR2, --Locator Name
44 x_Current_Subinventory_Code OUT NOCOPY VARCHAR2,
45 x_Revision OUT NOCOPY VARCHAR2,
46 x_Lot_Number OUT NOCOPY VARCHAR2,
47 x_Expiration_Date OUT NOCOPY DATE,
48 x_Message OUT NOCOPY VARCHAR2,
49 x_Status OUT NOCOPY VARCHAR2,
50 p_Inventory_Item_Id IN NUMBER,
51 p_Current_Organization_Id IN NUMBER,
52 p_Serial_Number IN VARCHAR2);
53
54
55
56 -- This does not use cost_group id
57 -- Bug 5125915 Added variables demand_source_header and demand_source_line
58 PROCEDURE GET_AVAILABLE_QUANTITY(
59 x_return_status OUT NOCOPY VARCHAR2,
60 p_tree_mode IN NUMBER,
61 p_organization_id IN NUMBER,
62 p_inventory_item_id IN NUMBER,
63 p_is_revision_control IN VARCHAR2,
64 p_is_lot_control IN VARCHAR2,
65 p_is_serial_control IN VARCHAR2,
66 p_demand_source_header_id IN NUMBER DEFAULT -9999,
67 p_demand_source_line_id IN NUMBER DEFAULT -9999,
68 p_revision IN VARCHAR2,
69 p_lot_number IN VARCHAR2,
70 p_lot_expiration_date IN DATE,
71 p_subinventory_code IN VARCHAR2,
72 p_locator_id IN NUMBER,
73 p_source_type_id IN NUMBER,
74 x_qoh OUT NOCOPY NUMBER,
75 x_att OUT NOCOPY NUMBER
76 );
77
78 -- Bug# 3952081
79 -- New Overloaded Version of the previous procedure for OPM convergence
80 -- Additionally returns secondary qoh, secondary att
81 -- Additionally takes grade_code as an input param.
82 PROCEDURE GET_AVAILABLE_QUANTITY(
83 x_return_status OUT NOCOPY VARCHAR2,
84 p_tree_mode IN NUMBER,
85 p_organization_id IN NUMBER,
86 p_inventory_item_id IN NUMBER,
87 p_is_revision_control IN VARCHAR2,
88 p_is_lot_control IN VARCHAR2,
89 p_is_serial_control IN VARCHAR2,
90 p_demand_source_header_id IN NUMBER DEFAULT -9999,
91 p_demand_source_line_id IN NUMBER DEFAULT -9999,
92 p_revision IN VARCHAR2,
93 p_lot_number IN VARCHAR2,
94 p_grade_code IN VARCHAR2, -- inv converge
95 p_lot_expiration_date IN DATE,
96 p_subinventory_code IN VARCHAR2,
97 p_locator_id IN NUMBER,
98 p_source_type_id IN NUMBER,
99 x_qoh OUT NOCOPY NUMBER,
100 x_att OUT NOCOPY NUMBER,
101 x_sqoh OUT NOCOPY NUMBER, -- inv converge
102 x_satt OUT NOCOPY NUMBER -- inv converge
103 );
104
105
106
107 -- This uses cost group id
108 PROCEDURE GET_AVAILABLE_QUANTITY(
109 x_return_status OUT NOCOPY VARCHAR2,
110 p_tree_mode IN NUMBER,
111 p_organization_id IN NUMBER,
112 p_inventory_item_id IN NUMBER,
113 p_is_revision_control IN VARCHAR2,
114 p_is_lot_control IN VARCHAR2,
115 p_is_serial_control IN VARCHAR2,
116 p_revision IN VARCHAR2,
117 p_lot_number IN VARCHAR2,
118 p_lot_expiration_date IN DATE,
119 p_subinventory_code IN VARCHAR2,
120 p_locator_id IN NUMBER,
121 p_source_type_id IN NUMBER,
122 p_cost_group_id IN NUMBER,
123 x_qoh OUT NOCOPY NUMBER,
124 x_att OUT NOCOPY NUMBER
125 );
126
127 -- Bug# 3952081
128 -- New Overloaded Version of the previous procedure for OPM convergence
129 -- Additionally returns secondary qoh, secondary att
130 -- Additionally takes grade_code as an input param.
131 PROCEDURE GET_AVAILABLE_QUANTITY(
132 x_return_status OUT NOCOPY VARCHAR2,
133 p_tree_mode IN NUMBER,
134 p_organization_id IN NUMBER,
135 p_inventory_item_id IN NUMBER,
136 p_is_revision_control IN VARCHAR2,
137 p_is_lot_control IN VARCHAR2,
138 p_is_serial_control IN VARCHAR2,
139 p_revision IN VARCHAR2,
140 p_lot_number IN VARCHAR2,
141 p_grade_code IN VARCHAR2, -- inv converge
142 p_lot_expiration_date IN DATE,
143 p_subinventory_code IN VARCHAR2,
144 p_locator_id IN NUMBER,
145 p_source_type_id IN NUMBER,
146 p_cost_group_id IN NUMBER,
147 x_qoh OUT NOCOPY NUMBER,
148 x_att OUT NOCOPY NUMBER,
149 x_sqoh OUT NOCOPY NUMBER, -- inv converge
150 x_satt OUT NOCOPY NUMBER -- inv converge
151 );
152
153 -- Bug# 2358224
154 -- Overloaded version of the previous procedure
155 -- passing in the to/transfer subinventory
156 -- This uses cost group id and transfer subinventory
157 PROCEDURE GET_AVAILABLE_QUANTITY(
158 x_return_status OUT NOCOPY VARCHAR2,
159 p_tree_mode IN NUMBER,
160 p_organization_id IN NUMBER,
161 p_inventory_item_id IN NUMBER,
162 p_is_revision_control IN VARCHAR2,
163 p_is_lot_control IN VARCHAR2,
164 p_is_serial_control IN VARCHAR2,
165 p_revision IN VARCHAR2,
166 p_lot_number IN VARCHAR2,
167 p_lot_expiration_date IN DATE,
168 p_subinventory_code IN VARCHAR2,
169 p_locator_id IN NUMBER,
170 p_source_type_id IN NUMBER,
171 p_cost_group_id IN NUMBER,
172 p_to_subinventory_code IN VARCHAR2,
173 x_qoh OUT NOCOPY NUMBER,
174 x_att OUT NOCOPY NUMBER
175 );
176
177 --BUG11812327 ,
178 -- Overloaded version of the previous procedure
179 -- return the total onhand qty
180 PROCEDURE GET_AVAILABLE_QUANTITY(
181 x_return_status OUT NOCOPY VARCHAR2,
182 p_tree_mode IN NUMBER,
183 p_organization_id IN NUMBER,
184 p_inventory_item_id IN NUMBER,
185 p_is_revision_control IN VARCHAR2,
186 p_is_lot_control IN VARCHAR2,
187 p_is_serial_control IN VARCHAR2,
188 p_revision IN VARCHAR2,
189 p_lot_number IN VARCHAR2,
190 p_lot_expiration_date IN DATE,
191 p_subinventory_code IN VARCHAR2,
192 p_locator_id IN NUMBER,
193 p_source_type_id IN NUMBER,
194 p_cost_group_id IN NUMBER,
195 p_to_subinventory_code IN VARCHAR2,
196 x_qoh OUT NOCOPY NUMBER,
197 x_att OUT NOCOPY NUMBER,
198 x_tqoh OUT NOCOPY NUMBER
199 );
200
201 -- Bug# 3952081
202 -- New Overloaded Version of the previous procedure for OPM convergence
203 -- Additionally returns secondary qoh, secondary att
204 -- Additionally takes grade_code as an input param.
205 PROCEDURE GET_AVAILABLE_QUANTITY(
206 x_return_status OUT NOCOPY VARCHAR2,
207 p_tree_mode IN NUMBER,
208 p_organization_id IN NUMBER,
209 p_inventory_item_id IN NUMBER,
210 p_is_revision_control IN VARCHAR2,
211 p_is_lot_control IN VARCHAR2,
212 p_is_serial_control IN VARCHAR2,
213 p_revision IN VARCHAR2,
214 p_lot_number IN VARCHAR2,
215 p_grade_code IN VARCHAR2, -- inv converge
216 p_lot_expiration_date IN DATE,
217 p_subinventory_code IN VARCHAR2,
218 p_locator_id IN NUMBER,
219 p_source_type_id IN NUMBER,
220 p_cost_group_id IN NUMBER,
221 p_to_subinventory_code IN VARCHAR2,
222 x_qoh OUT NOCOPY NUMBER,
223 x_att OUT NOCOPY NUMBER,
224 x_sqoh OUT NOCOPY NUMBER, -- inv converge
225 x_satt OUT NOCOPY NUMBER -- inv converge
226 );
227
228
229 -- This procedure calls INV_QUANTITY_TREE_PVT to return pqoh
230 PROCEDURE GET_AVAILABLE_QUANTITY(
231 x_return_status OUT NOCOPY VARCHAR2,
232 p_tree_mode IN NUMBER,
233 p_organization_id IN NUMBER,
234 p_inventory_item_id IN NUMBER,
235 p_is_revision_control IN VARCHAR2,
236 p_is_lot_control IN VARCHAR2,
237 p_is_serial_control IN VARCHAR2,
238 p_revision IN VARCHAR2,
239 p_lot_number IN VARCHAR2,
240 p_lot_expiration_date IN DATE,
241 p_subinventory_code IN VARCHAR2,
242 p_locator_id IN NUMBER,
243 p_source_type_id IN NUMBER,
244 x_qoh OUT NOCOPY NUMBER,
245 x_att OUT NOCOPY NUMBER,
246 x_pqoh OUT NOCOPY NUMBER,
247 x_tqoh OUT NOCOPY NUMBER,
248 x_atpp1 OUT NOCOPY NUMBER,
249 x_qoh1 OUT NOCOPY NUMBER
250 );
251
252 -- Bug# 3952081
253 -- New Overloaded Version of the previous procedure for OPM convergence
254 -- Additionally returns secondary qoh, secondary att
255 -- Additionally takes grade_code as an input param.
256 PROCEDURE GET_AVAILABLE_QUANTITY(
257 x_return_status OUT NOCOPY VARCHAR2,
258 p_tree_mode IN NUMBER,
259 p_organization_id IN NUMBER,
260 p_inventory_item_id IN NUMBER,
261 p_is_revision_control IN VARCHAR2,
262 p_is_lot_control IN VARCHAR2,
263 p_is_serial_control IN VARCHAR2,
264 p_revision IN VARCHAR2,
265 p_lot_number IN VARCHAR2,
266 p_grade_code IN VARCHAR2,
267 p_lot_expiration_date IN DATE,
268 p_subinventory_code IN VARCHAR2,
269 p_locator_id IN NUMBER,
270 p_source_type_id IN NUMBER,
271 x_qoh OUT NOCOPY NUMBER,
272 x_att OUT NOCOPY NUMBER,
273 x_pqoh OUT NOCOPY NUMBER,
274 x_tqoh OUT NOCOPY NUMBER,
275 x_atpp1 OUT NOCOPY NUMBER,
276 x_qoh1 OUT NOCOPY NUMBER,
277 x_sqoh OUT NOCOPY NUMBER,
278 x_satt OUT NOCOPY NUMBER,
279 x_spqoh OUT NOCOPY NUMBER,
280 x_stqoh OUT NOCOPY NUMBER,
281 x_satpp1 OUT NOCOPY NUMBER,
282 x_sqoh1 OUT NOCOPY NUMBER
283 );
284
288
285 /* This Overloaded Procedure Calls INV_QUANTITY_TREE_PVT to return pqoh.
286 --This procedure takes in the cost group
287 */
289 PROCEDURE GET_AVAILABLE_QUANTITY(
290 x_return_status OUT NOCOPY VARCHAR2,
291 p_tree_mode IN NUMBER,
292 p_organization_id IN NUMBER,
293 p_inventory_item_id IN NUMBER,
294 p_is_revision_control IN VARCHAR2,
295 p_is_lot_control IN VARCHAR2,
296 p_is_serial_control IN VARCHAR2,
297 p_revision IN VARCHAR2,
298 p_lot_number IN VARCHAR2,
299 p_lot_expiration_date IN DATE,
300 p_subinventory_code IN VARCHAR2,
301 p_locator_id IN NUMBER,
302 p_source_type_id IN NUMBER,
303 x_qoh OUT NOCOPY NUMBER,
304 x_att OUT NOCOPY NUMBER,
305 x_pqoh OUT NOCOPY NUMBER,
306 x_tqoh OUT NOCOPY NUMBER,
307 x_atpp1 OUT NOCOPY NUMBER,
308 x_qoh1 OUT NOCOPY NUMBER,
309 p_cost_group_id IN NUMBER,
310 p_transfer_subinventory IN VARCHAR2
311 );
312
313 -- Bug# 3952081
314 -- New Overloaded Version of the previous procedure for OPM convergence
315 -- Additionally returns secondary qoh, secondary att
316 -- Additionally takes grade_code as an input param.
317 PROCEDURE GET_AVAILABLE_QUANTITY(
318 x_return_status OUT NOCOPY VARCHAR2,
319 p_tree_mode IN NUMBER,
320 p_organization_id IN NUMBER,
321 p_inventory_item_id IN NUMBER,
322 p_is_revision_control IN VARCHAR2,
323 p_is_lot_control IN VARCHAR2,
324 p_is_serial_control IN VARCHAR2,
325 p_revision IN VARCHAR2,
326 p_lot_number IN VARCHAR2,
327 p_grade_code IN VARCHAR2,
328 p_lot_expiration_date IN DATE,
329 p_subinventory_code IN VARCHAR2,
330 p_locator_id IN NUMBER,
331 p_source_type_id IN NUMBER,
332 x_qoh OUT NOCOPY NUMBER,
333 x_att OUT NOCOPY NUMBER,
334 x_pqoh OUT NOCOPY NUMBER,
335 x_tqoh OUT NOCOPY NUMBER,
336 x_atpp1 OUT NOCOPY NUMBER,
337 x_qoh1 OUT NOCOPY NUMBER,
338 x_sqoh OUT NOCOPY NUMBER,
339 x_satt OUT NOCOPY NUMBER,
340 x_spqoh OUT NOCOPY NUMBER,
341 x_stqoh OUT NOCOPY NUMBER,
342 x_satpp1 OUT NOCOPY NUMBER,
343 x_sqoh1 OUT NOCOPY NUMBER,
344 p_cost_group_id IN NUMBER,
345 p_transfer_subinventory IN VARCHAR2
346 );
347
348 -- Bug 14516283
349 -- Passing to/transfer locator
350
351 PROCEDURE GET_AVAILABLE_QUANTITY(
352 x_return_status OUT NOCOPY VARCHAR2,
353 p_tree_mode IN NUMBER,
354 p_organization_id IN NUMBER,
355 p_inventory_item_id IN NUMBER,
356 p_is_revision_control IN VARCHAR2,
357 p_is_lot_control IN VARCHAR2,
358 p_is_serial_control IN VARCHAR2,
359 p_revision IN VARCHAR2,
360 p_lot_number IN VARCHAR2,
361 p_lot_expiration_date IN DATE,
362 p_subinventory_code IN VARCHAR2,
363 p_locator_id IN NUMBER,
364 p_source_type_id IN NUMBER,
365 p_cost_group_id IN NUMBER,
366 p_to_subinventory_code IN VARCHAR2,
367 p_to_locator_id IN NUMBER,
368 x_qoh OUT NOCOPY NUMBER,
369 x_att OUT NOCOPY NUMBER,
370 x_sqoh OUT NOCOPY NUMBER, -- inv converge
371 x_satt OUT NOCOPY NUMBER -- inv converge
372 );
373
374
375 PROCEDURE CHECK_LOOSE_QUANTITY(
376 p_api_version_number IN NUMBER
377 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
378 , x_return_status OUT NOCOPY VARCHAR2
379 , x_msg_count OUT NOCOPY NUMBER
380 , x_msg_data OUT NOCOPY VARCHAR2
381 , p_organization_id IN NUMBER
382 , p_inventory_item_id IN NUMBER
383 , p_is_revision_control IN VARCHAR2
384 , p_is_lot_control IN VARCHAR2
385 , p_is_serial_control IN VARCHAR2
386 , p_revision IN VARCHAR2
387 , p_lot_number IN VARCHAR2
388 , p_transaction_quantity IN NUMBER
389 , p_transaction_uom IN VARCHAR2
390 , p_subinventory_code IN VARCHAR2
391 , p_locator_id IN NUMBER
392 , p_transaction_temp_id IN NUMBER
393 , p_ok_to_process OUT NOCOPY VARCHAR2
394 , p_transfer_subinventory IN VARCHAR2);
395
396
397 PROCEDURE CHECK_WMS_INSTALL (x_return_status OUT NOCOPY VARCHAR2,
401 );
398 p_msg_count OUT NOCOPY NUMBER,
399 p_msg_data OUT NOCOPY VARCHAR2,
400 p_org IN NUMBER
402
403 FUNCTION validate_lpn_status_quantity(
404 p_lpn_id IN NUMBER,
405 p_orgid IN NUMBER,
406 p_to_org_id IN NUMBER,
407 p_wms_installed IN VARCHAR2,
408 p_transaction_type_id IN NUMBER,
409 p_source_type_id IN NUMBER,
410 x_return_msg OUT NOCOPY VARCHAR2
411 )
412 RETURN VARCHAR2;
413
414 -- Bug# 2358224
415 -- Overloaded version of the previous function passing in
416 -- the to/transfer subinventory
417 FUNCTION validate_lpn_status_quantity(
418 p_lpn_id IN NUMBER,
419 p_orgid IN NUMBER,
420 p_to_org_id IN NUMBER,
421 p_wms_installed IN VARCHAR2,
422 p_transaction_type_id IN NUMBER,
423 p_source_type_id IN NUMBER,
424 p_to_subinventory_code IN VARCHAR2,
425 x_return_msg OUT NOCOPY VARCHAR2
426 )
427 RETURN VARCHAR2;
428
429
430 FUNCTION validate_lpn_status_quantity2(
431 p_lpn_id IN NUMBER,
432 p_orgid IN NUMBER,
433 p_to_org_id IN NUMBER,
434 p_wms_installed IN VARCHAR2,
435 p_transaction_type_id IN NUMBER,
436 p_source_type_id IN NUMBER,
437 x_return_msg OUT NOCOPY VARCHAR2
438 )
439 RETURN VARCHAR2;
440
441
442 FUNCTION orgxfer_lpn_check(
443 p_lpn_id IN NUMBER,
444 p_orgid IN NUMBER,
445 p_to_org_id IN NUMBER,
446 p_wms_installed IN VARCHAR2,
447 p_transaction_type_id IN NUMBER,
448 p_source_type_id IN NUMBER,
449 x_return_msg OUT NOCOPY VARCHAR2
450 )
451 RETURN VARCHAR2;
452
453
454 FUNCTION check_lpn_quantity(
455 p_lpn_id IN NUMBER,
456 p_organization_id IN NUMBER,
457 p_source_type_id IN NUMBER,
458 p_transaction_type_id IN NUMBER,
459 x_return_msg OUT NOCOPY VARCHAR2)
460 RETURN VARCHAR2 ;
461
462
463 -- Bug# 2358224
464 -- Overloaded version of the previous function passing in
465 -- the to/transfer subinventory
466 -- Bug # 2433095 -- Changes to LPN reservations ported to the ovreloaded function
467 FUNCTION check_lpn_quantity(
468 p_lpn_id IN NUMBER,
469 p_organization_id IN NUMBER,
470 p_source_type_id IN NUMBER,
471 p_transaction_type_id IN NUMBER,
472 p_to_subinventory_code IN VARCHAR2,
473 x_return_msg OUT NOCOPY VARCHAR2)
474 RETURN VARCHAR2 ;
475
476
477 FUNCTION get_immediate_lpn_item_qty ( p_lpn_id IN NUMBER,
478 p_organization_id IN NUMBER,
479 p_source_type_id IN NUMBER,
480 p_inventory_item_id IN NUMBER,
481 p_revision IN VARCHAR2,
482 p_locator_id IN NUMBER,
483 p_subinventory_code IN VARCHAR2,
484 p_lot_number IN VARCHAR2,
485 p_is_revision_control IN VARCHAR2,
486 p_is_serial_control IN VARCHAR2,
487 p_is_lot_control IN VARCHAR2,
488 x_transactable_qty OUT NOCOPY NUMBER,
489 x_qoh OUT NOCOPY NUMBER,
490 x_lpn_onhand OUT NOCOPY NUMBER,
491 x_return_msg OUT NOCOPY VARCHAR2)
492 RETURN VARCHAR2 ;
493
494 -- Gets the immediate quantity of an item in an LPN.
495 -- Overloaded function with the following new output parameters (INVCONV):
496 -- x_transactable_sec_qty OUT NOCOPY NUMBER,
497 -- x_sqoh OUT NOCOPY NUMBER,
498 --- x_lpn_sec_onhand OUT NOCOPY NUMBER,
499
500 FUNCTION get_immediate_lpn_item_qty(p_lpn_id IN NUMBER,
501 p_organization_id IN NUMBER,
502 p_source_type_id IN NUMBER,
503 p_inventory_item_id IN NUMBER,
504 p_revision IN VARCHAR2,
505 p_locator_id IN NUMBER,
506 p_subinventory_code IN VARCHAR2,
507 p_lot_number IN VARCHAR2,
508 p_is_revision_control IN VARCHAR2,
509 p_is_serial_control IN VARCHAR2,
510 p_is_lot_control IN VARCHAR2,
511 x_transactable_qty OUT NOCOPY NUMBER,
512 x_qoh OUT NOCOPY NUMBER,
513 x_lpn_onhand OUT NOCOPY NUMBER,
514 x_transactable_sec_qty OUT NOCOPY NUMBER,
515 x_sqoh OUT NOCOPY NUMBER,
519 RETURN VARCHAR2;
516 x_lpn_sec_onhand OUT NOCOPY NUMBER,
517 x_return_msg OUT NOCOPY VARCHAR2)
518
520
521 FUNCTION get_unpacksplit_lpn_item_qty(p_lpn_id IN NUMBER,
522 p_organization_id IN NUMBER,
523 p_source_type_id IN NUMBER,
524 p_inventory_item_id IN NUMBER,
525 p_revision IN VARCHAR2,
526 p_locator_id IN NUMBER,
527 p_subinventory_code IN VARCHAR2,
528 p_lot_number IN VARCHAR2,
529 p_is_revision_control IN VARCHAR2,
530 p_is_serial_control IN VARCHAR2,
531 p_is_lot_control IN VARCHAR2,
532 p_transfer_subinventory_code IN VARCHAR2,
533 p_transfer_locator_id IN NUMBER,
534 x_transactable_qty OUT NOCOPY NUMBER,
535 x_qoh OUT NOCOPY NUMBER,
536 x_lpn_onhand OUT NOCOPY NUMBER,
537 x_return_msg OUT NOCOPY VARCHAR2)
538 RETURN VARCHAR2 ;
539
540
541
542 FUNCTION CHECK_SERIAL_UNPACKSPLIT( p_lpn_id IN NUMBER
543 ,p_org_id IN NUMBER
544 ,p_item_id IN NUMBER
545 ,p_rev IN VARCHAR2
546 ,p_lot IN VARCHAR2
547 ,p_serial IN VARCHAR2)
548 RETURN VARCHAR2;
549
550 --"Returns"
551 PROCEDURE GET_RETURN_LOT_QUANTITIES(
552 x_lot_qty OUT NOCOPY t_genref
553 , p_org_id IN NUMBER
554 , p_lpn_id IN NUMBER
555 , p_item_id IN NUMBER
556 , p_revision IN VARCHAR2
557 , p_uom IN VARCHAR2);
558
559 PROCEDURE GET_RETURN_TOTAL_QTY(
560 x_tot_qty OUT NOCOPY t_genref
561 , p_org_id IN NUMBER
562 , p_lpn_id IN NUMBER
563 , p_item_id IN NUMBER
564 , p_revision IN VARCHAR2
565 , p_uom IN VARCHAR2);
566 --"Returns"
567
568
569
570 -- This procedure validates the serial number, to sub and to loc for a
571 -- serial triggered sub transfer. It also updates the quantity tree. It
572 -- sets the GROUP mark ID of the serial number to a non null value. It also
573 -- inserts into MMTT, MTLT and MSNT tables for the sub transfer transaction
574 PROCEDURE process_serial_subxfr(p_organization_id IN NUMBER,
575 p_serial_number IN VARCHAR2,
576 p_inventory_item_id IN NUMBER,
577 p_inventory_item IN VARCHAR2,
578 --I Development Bug 2634570
579 p_project_id IN NUMBER,
580 p_task_id IN NUMBER,
581
582 p_revision IN VARCHAR2,
583 p_primary_uom_code IN VARCHAR2,
584 p_subinventory_code IN VARCHAR2,
585 p_locator_id IN NUMBER,
586 p_locator IN VARCHAR2,
587 p_to_subinventory_code IN VARCHAR2,
588 p_to_locator IN VARCHAR2,
589 p_to_locator_id IN NUMBER,
590 p_reason_id IN NUMBER,
591 p_lot_number IN VARCHAR2,
592 p_wms_installed IN VARCHAR2,
593 p_transaction_action_id IN NUMBER,
594 p_transaction_type_id IN VARCHAR2,
595 p_source_type_id IN NUMBER,
596 p_user_id IN NUMBER,
597 p_transaction_header_id IN NUMBER,
598 p_restrict_sub_code IN NUMBER,
599 p_restrict_loc_code IN NUMBER,
600 p_from_sub_asset_inv IN NUMBER,
601 p_serial_control_code IN NUMBER,
602 p_process_serial IN VARCHAR2,
603 x_serial_processed OUT NOCOPY VARCHAR2,
604 x_transaction_header_id OUT NOCOPY NUMBER,
605 x_return_status OUT NOCOPY VARCHAR2,
606 x_return_msg OUT NOCOPY VARCHAR2);
607
608 PROCEDURE check_loose_and_packed_qty
609 (p_api_version_number IN NUMBER
610 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
611 , x_return_status OUT NOCOPY VARCHAR2
612 , x_msg_count OUT NOCOPY NUMBER
613 , x_msg_data OUT NOCOPY VARCHAR2
614 , p_organization_id IN NUMBER
615 , p_inventory_item_id IN NUMBER
616 , p_is_revision_control IN VARCHAR2
617 , p_is_lot_control IN VARCHAR2
618 , p_is_serial_control IN VARCHAR2
619 , p_revision IN VARCHAR2
620 , p_lot_number IN VARCHAR2
621 , p_transaction_quantity IN NUMBER
622 , p_transaction_uom IN VARCHAR2
623 , p_subinventory_code IN VARCHAR2
624 , p_locator_id IN NUMBER
625 , p_transaction_temp_id IN NUMBER
626 , p_ok_to_process OUT NOCOPY VARCHAR2
627 , p_transfer_subinventory IN VARCHAR2
628 );
629
630 FUNCTION check_lpn_allocation(p_lpn_id IN NUMBER,
631 p_org_id IN NUMBER,
632 x_return_msg OUT NOCOPY VARCHAR2)
633 RETURN VARCHAR2;
634
635 FUNCTION check_lpn_serial_allocation(p_lpn_id IN NUMBER,
636 p_org_id IN NUMBER,
637 x_return_msg OUT NOCOPY VARCHAR2)
638 RETURN VARCHAR2;
639
640 /* Bug 4194323 Added Procedure to get available quantity
641 when Demand Information is provided for WIP Enhancement 4163405 */
642 PROCEDURE GET_AVBL_TO_TRANSACT_QTY(
643 x_return_status OUT NOCOPY VARCHAR2,
644 p_organization_id IN NUMBER,
645 p_inventory_item_id IN NUMBER,
646 p_is_revision_control IN VARCHAR2,
647 p_is_lot_control IN VARCHAR2,
648 p_is_serial_control IN VARCHAR2,
649 p_demand_source_type_id IN NUMBER,
650 p_demand_source_header_id IN NUMBER,
651 p_demand_source_line_id IN NUMBER,
652 p_revision IN VARCHAR2,
653 p_lot_number IN VARCHAR2,
654 p_lot_expiration_date IN DATE,
655 p_subinventory_code IN VARCHAR2,
656 p_locator_id IN NUMBER,
657 x_att OUT NOCOPY NUMBER
658 ) ;
659 --Bug#4446248.Added the following function to check any pending transaction
660 --for the LPN/inner LPNs .
661 FUNCTION check_lpn_pending_txns( p_lpn_id IN NUMBER,
662 p_org_id IN NUMBER,
663 x_return_msg OUT NOCOPY VARCHAR2)
664 RETURN VARCHAR2;
665
666
667 END INV_TXN_VALIDATIONS;