1 PACKAGE inv_ui_item_att_lovs AS
2 /* $Header: INVITATS.pls 120.9 2008/04/14 12:44:31 abaid noship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_SERIAL_LOV_RCV
7 --
8 -- Input parameters:
9 -- p_Organization_Id which restricts LOV SQL to current org
10 -- p_item_id which restricts LOV SQL to current item
11 -- p_serial which restricts LOV SQL to the serial entered
12 -- p_shipment_header_id which restricts LOV SQL to the shipment being received
13 --
14 -- Output parameters:
15 -- x_serial_number returns LOV rows as reference cursor
16 --
17 -- Functions: This API is to return serial numbers for receiving for PO
18 -- This is equivalent to inv_serial4 in the serial entry form INVTTESR
19
20 PROCEDURE get_serial_lov_rcv(
21 x_serial_number OUT NOCOPY t_genref,
22 p_organization_id IN NUMBER,
23 p_item_id IN NUMBER,
24 p_serial IN VARCHAR2,
25 p_transaction_type_id IN NUMBER,
26 p_wms_installed IN VARCHAR2);
27
28 -- Name: GET_SERIAL_LOV_RMA_RCV
29 --
30 -- Input parameters:
31 -- p_Organization_Id which restricts LOV SQL to current org
32 -- p_item_id which restricts LOV SQL to current item
33 -- p_serial which restricts LOV SQL to the serial entered
34 --
35 -- Output parameters:
36 -- x_serial_number returns LOV rows as reference cursor
37 --
38 -- Functions: This API is to return serial numbers for receiving for
39 -- intransit shipments
40 -- This is equivalent to inv_serial3 in the serial entry form INVTTESR
41
42 PROCEDURE get_serial_lov_rma_rcv(
43 x_serial_number OUT NOCOPY t_genref,
44 p_organization_id IN NUMBER,
45 p_item_id IN NUMBER,
46 p_serial IN VARCHAR2,
47 p_transaction_type_id IN NUMBER,
48 p_wms_installed IN VARCHAR2,
49 p_oe_order_header_id IN NUMBER default NULL);
50
51 -- Name: GET_SERIAL_LOV_INT_SHP_RCV
52 --
53 -- Input parameters:
54 -- p_Organization_Id which restricts LOV SQL to current org
55 -- p_item_id which restricts LOV SQL to current item
56 -- p_serial which restricts LOV SQL to the serial entered
57 --
58 -- Output parameters:
59 -- x_serial_number returns LOV rows as reference cursor
60 --
61 -- Functions: This API is to return serial numbers for receiving for
62 -- intransit shipments
63 -- This is equivalent to inv_serial7 in the serial entry form INVTTESR
64
65 PROCEDURE get_serial_lov_int_shp_rcv(
66 x_serial_number OUT NOCOPY t_genref
67 , p_organization_id IN NUMBER
68 , p_item_id IN NUMBER
69 , p_shipment_header_id IN NUMBER
70 , p_lot_num IN VARCHAR2
71 , p_serial IN VARCHAR2
72 , p_transaction_type_id IN NUMBER
73 , p_wms_installed IN VARCHAR2
74 , p_from_lpn_id IN NUMBER DEFAULT NULL
75 );
76
77 -- Name: GET_SERIAL_LOV_LMT
78 --
79 -- Input parameters:
80 -- p_Organization_Id which restricts LOV SQL to current org
81 -- p_item_id which restricts LOV SQL to current item
82 -- p_subinv_code restricts to Subinventory
83 -- p_locator_id restricts to Locator ID. If not used, set to -1
84 -- p_serial which restricts LOV SQL to the serial entered
85 -- p_transaction_type_id trx_type_id
86 -- p_wms_installed whether WMS-enabled ORG
87 --
88 -- Output parameters:
89 -- x_serial_number returns LOV rows as reference cursor
90 --
91 -- Functions: This API is to return serial numbers limited by
92 -- the specified Subinventory and Locator with status='Received';
93 --
94 PROCEDURE get_serial_lov_lmt(
95 x_serial_number OUT NOCOPY t_genref
96 , p_organization_id IN NUMBER
97 , p_item_id IN NUMBER
98 , p_subinv_code IN VARCHAR2
99 , p_locator_id IN NUMBER
100 , p_serial IN VARCHAR2
101 , p_transaction_type_id IN NUMBER
102 , p_wms_installed IN VARCHAR2
103 , p_group_mark_id IN NUMBER := NULL
104 );
105
106 -- Name: get_lot_info
107 --
108 -- Input parameters:
109 --
110 -- Output parameters:
111 -- x_expiration_date - Expiration date to use for the lot number given. If
112 -- the lot exists in the table, then the expiration date entered for that
113 -- lot is returned. If that is null, then the expiration date is calculated
114 -- based on the shelf life days and shelf life code and returned.
115 -- Similar thing happens if no data is found in the lots table for the
116 -- entered lot number
117 -- x_is_new_lot - TRUE/FALSE. It is TRUE if no data exists in the
118 -- lot_number table for the lots entered and FALSE otherwise
119 -- x_is_valid_lot - TRUE/FALSE. It is TRUE if it satisfies the uniqueness
120 -- condition, otherwise it returns false here which means that the lot
121 -- number entered is not really a valid lot number.
122 PROCEDURE get_lot_info(
123 p_organization_id IN NUMBER
124 , p_inventory_item_id IN NUMBER
125 , p_lot_number IN VARCHAR2
126 , p_shelf_life_code IN NUMBER
127 , p_shelf_life_days IN NUMBER
128 , p_lot_status_enabled IN VARCHAR2
129 , p_default_lot_status_id IN NUMBER
130 , p_wms_installed IN VARCHAR2
131 , x_expiration_date OUT NOCOPY DATE
132 , x_is_new_lot OUT NOCOPY VARCHAR2
133 , x_is_valid_lot OUT NOCOPY VARCHAR2
134 , x_lot_status OUT NOCOPY VARCHAR2
135 );
136
137 -- procedure to get the serial info for the lov incase it is a
138 -- serial number created dynamically.
139 PROCEDURE get_serial_info(
140 p_item_id IN NUMBER,
141 p_serial IN VARCHAR2,
142 p_serial_status_enabled IN VARCHAR2,
143 p_default_serial_status IN NUMBER,
144 p_wms_installed IN VARCHAR2,
145 x_current_status OUT NOCOPY VARCHAR2,
146 x_serial_status OUT NOCOPY VARCHAR2);
147
148 --During an issue, if it is the first serial number then
149 --we can accept any serial that resides in stores
150 --however, after the first serial has been scanned we must
151 --make sure that all subsequent serials are from the same
152 --locator and same sub.
153 --Consignment and VMI Changes - Added Planning Org and TP Type and Owning Org and TP Type.
154 PROCEDURE get_valid_serial_issue(
155 x_rserials OUT NOCOPY t_genref
156 , p_current_organization_id IN NUMBER
157 , p_revision IN VARCHAR2
158 , p_current_subinventory_code IN VARCHAR2
159 , p_current_locator_id IN NUMBER
160 , p_current_lot_number IN VARCHAR2
161 , p_inventory_item_id IN NUMBER
162 , p_serial_number IN VARCHAR2
163 , p_transaction_type_id IN NUMBER
164 , p_wms_installed IN VARCHAR2
165 , p_lpn_id IN NUMBER DEFAULT NULL
166 , p_planning_org_id IN NUMBER DEFAULT NULL
167 , p_planning_tp_type IN NUMBER DEFAULT NULL
168 , p_owning_org_id IN NUMBER DEFAULT NULL
169 , p_owning_tp_type IN NUMBER DEFAULT NULL
170 );
171
172 -- Name: GET_COST_GROUP_LOV
173 --
174 -- Input parameters:
175 -- p_Organization_Id which restricts LOV SQL to current org
176 -- p_inventory_item_id which restricts LOV SQL to current item
177 -- p_subinventory_code restricts to Subinventory
178 -- p_locator_id restricts to Locator ID. If not used, set to -1
179 -- p_cost_group which restricts LOV SQL to the cost group entered
180 --
181 -- Output parameters:
182 -- x_cost_group returns LOV rows as reference cursor
183 --
184 -- Functions: This API is to return cost_group limited by
185 -- the specified Subinventory and Locator and cost_group_type = 3;
186 --
187 PROCEDURE get_cost_group_lov(
188 x_cost_group OUT NOCOPY t_genref,
189 p_organization_id IN NUMBER,
190 p_inventory_item_id IN VARCHAR2,
191 p_subinventory_code IN VARCHAR2,
192 p_locator_id IN VARCHAR2,
193 p_cost_group IN VARCHAR2);
194
195 -- Name: GET_PHYINV_SERIAL_LOV
196 --
197 -- Input parameters:
198 -- p_organization_id - Restricts LOV SQL to current org
199 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
200 -- p_locator_id - Restricts the LOV SQL to locator ID entered
201 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
202 -- p_serial_number - Restricts LOV SQL to the serial entered
203 -- p_lot_number - Restricts LOV SQL to the lot number entered
204 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
205 -- p_physical_inventory_id - Restricts LOV SQL to current physical inventory
206 -- p_parent_lpn_id - Restricts LOV SQL to serial numbers
207 -- stored within the given parent lpn ID
208 --
209 -- Output parameters:
210 -- x_serials - Returns LOV rows as reference cursor
211 --
212 -- Functions: This API is to return serial numbers for physical inventory
213
214 PROCEDURE get_phyinv_serial_lov(
215 x_serials OUT NOCOPY t_genref
216 , p_organization_id IN NUMBER
217 , p_subinventory_code IN VARCHAR2
218 , p_locator_id IN NUMBER
219 , p_inventory_item_id IN NUMBER
220 , p_serial_number IN VARCHAR2
221 , p_lot_number IN VARCHAR2
222 , p_dynamic_entry_flag IN NUMBER
223 , p_physical_inventory_id IN NUMBER
224 , p_parent_lpn_id IN NUMBER
225 );
226
227 -- Name: GET_PHYINV_TO_SERIAL_LOV
228 --
229 -- Input parameters:
230 -- p_organization_id - Restricts LOV SQL to current org
231 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
232 -- p_locator_id - Restricts the LOV SQL to locator ID entered
233 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
234 -- p_to_serial_number - Restricts LOV SQL to the serial entered
235 -- p_lot_number - Restricts LOV SQL to the lot number entered
236 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
237 -- p_physical_inventory_id - Restricts LOV SQL to current physical inventory
238 -- p_from_serial_number - The starting serial number so that it
239 -- restricts the LOV SQL to only serial
240 -- numbers larger than this value
241 -- p_parent_lpn_id - Restricts LOV SQL to serial numbers
242 -- stored within the given parent lpn ID
243 --
244 -- Output parameters:
245 -- x_serials - Returns LOV rows as reference cursor
246 --
247 -- Functions: This API is to return valid to serial numbers for
248 -- physical inventory
249
250 PROCEDURE get_phyinv_to_serial_lov(
251 x_serials OUT NOCOPY t_genref
252 , p_organization_id IN NUMBER
253 , p_subinventory_code IN VARCHAR2
254 , p_locator_id IN NUMBER
255 , p_inventory_item_id IN NUMBER
256 , p_to_serial_number IN VARCHAR2
257 , p_lot_number IN VARCHAR2
258 , p_dynamic_entry_flag IN NUMBER
259 , p_physical_inventory_id IN NUMBER
260 , p_from_serial_number IN VARCHAR2
261 , p_parent_lpn_id IN NUMBER
262 );
263
264 -- Name: GET_PHYINV_SERIAL_COUNT_LOV
265 --
266 -- Input parameters:
267 -- p_organization_id - Restricts LOV SQL to current org
268 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
269 -- p_locator_id - Restricts the LOV SQL to locator ID entered
270 -- p_serial_number - Restricts LOV SQL to the serial entered
271 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
272 -- p_physical_inventory_id - Restricts LOV SQL to current physical inventory
273 --
274 -- Output parameters:
275 -- x_serials - Returns LOV rows as reference cursor
276 --
277 -- Functions: This API is to return serial numbers for physical
278 -- inventory when performing serial triggered tag counts
279 -- Note that serials are only allowed to be counted once
280 -- for this particular type of physical tag counting
281
282 PROCEDURE get_phyinv_serial_count_lov(
283 x_serials OUT NOCOPY t_genref,
284 p_organization_id IN NUMBER,
285 p_subinventory_code IN VARCHAR2,
286 p_locator_id IN NUMBER,
287 p_serial_number IN VARCHAR2,
288 p_dynamic_entry_flag IN NUMBER,
289 p_physical_inventory_id IN NUMBER);
290
291 -- Name: GET_CYC_SERIAL_LOV
292 --
293 -- Input parameters:
294 -- p_organization_id - Restricts LOV SQL to current org
295 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
296 -- p_locator_id - Restricts the LOV SQL to locator ID entered
297 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
298 -- p_serial_number - Restricts LOV SQL to the serial entered
299 -- p_lot_number - Restricts LOV SQL to the lot number entered
300 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
301 -- p_cycle_count_header_id - Restricts LOV SQL to current cycle
302 -- count
303 -- p_parent_lpn_id - Restricts LOV SQL to serial numbers
304 -- stored within the given parent lpn ID
305 -- p_serial_count_option - Determines which table the serial
306 -- numbers are stored in
307 --
308 -- Output parameters:
309 -- x_serials - Returns LOV rows as reference cursor
310 --
311 -- Functions: This API is to return serial numbers for cycle count
312
313 PROCEDURE get_cyc_serial_lov(
314 x_serials OUT NOCOPY t_genref
315 , p_organization_id IN NUMBER
316 , p_subinventory_code IN VARCHAR2
317 , p_locator_id IN NUMBER
318 , p_inventory_item_id IN NUMBER
319 , p_serial_number IN VARCHAR2
320 , p_lot_number IN VARCHAR2
321 , p_unscheduled_entry IN NUMBER
322 , p_cycle_count_header_id IN NUMBER
323 , p_parent_lpn_id IN NUMBER
324 , p_serial_count_option IN NUMBER
325 );
326
327 -- Name: GET_CYC_TO_SERIAL_LOV
328 --
329 -- Input parameters:
330 -- p_organization_id - Restricts LOV SQL to current org
331 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
332 -- p_locator_id - Restricts the LOV SQL to locator ID entered
333 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
334 -- p_to_serial_number - Restricts LOV SQL to the serial entered
335 -- p_lot_number - Restricts LOV SQL to the lot number entered
336 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
337 -- p_cycle_count_header_id - Restricts LOV SQL to current cycle count
338 -- p_from_serial_number - The starting serial number so that it
339 -- restricts the LOV SQL to only serial
340 -- numbers larger than this value
341 -- p_parent_lpn_id - Restricts LOV SQL to serial numbers
342 -- stored within the given parent lpn ID
343 -- p_serial_count_option - Determines which table the serial
344 -- numbers are stored in
345 --
346 -- Output parameters:
347 -- x_serials - Returns LOV rows as reference cursor
348 --
349 -- Functions: This API is to return valid to serial numbers for
350 -- cycle count
351
352 PROCEDURE get_cyc_to_serial_lov(
353 x_serials OUT NOCOPY t_genref
354 , p_organization_id IN NUMBER
355 , p_subinventory_code IN VARCHAR2
356 , p_locator_id IN NUMBER
357 , p_inventory_item_id IN NUMBER
358 , p_to_serial_number IN VARCHAR2
359 , p_lot_number IN VARCHAR2
360 , p_unscheduled_entry IN NUMBER
361 , p_cycle_count_header_id IN NUMBER
362 , p_from_serial_number IN VARCHAR2
363 , p_parent_lpn_id IN NUMBER
364 , p_serial_count_option IN NUMBER
365 );
366
367 -- Name: GET_CYC_SERIAL_COUNT_LOV
368 --
369 -- Input parameters:
370 -- p_organization_id - Restricts LOV SQL to current org
371 -- p_subinventory_code - Restricts the LOV SQL to subinventory entered
372 -- p_locator_id - Restricts the LOV SQL to locator ID entered
373 -- p_serial_number - Restricts LOV SQL to the serial entered
374 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
375 -- p_cycle_count_header_id - Restricts LOV SQL to current cycle count
376 --
377 -- Output parameters:
378 -- x_serials - Returns LOV rows as reference cursor
379 --
380 -- Functions: This API is to return serial numbers for cycle
381 -- counting when performing serial triggered cycle counts
382 -- Note that serial triggered cycle counts are only allowed
383 -- for cycle count headers which have a serial count option
384 -- = 2 which is single serial. This is enforced in the
385 -- java mobile serial cycle counting page
386
387 PROCEDURE get_cyc_serial_count_lov(
388 x_serials OUT NOCOPY t_genref,
389 p_organization_id IN NUMBER,
390 p_subinventory_code IN VARCHAR2,
391 p_locator_id IN NUMBER,
392 p_serial_number IN VARCHAR2,
393 p_unscheduled_entry IN NUMBER,
394 p_cycle_count_header_id IN NUMBER);
395
396 -- Name: GET_SERIAL_LOV_STATUS
397 --
398 -- Input parameters:
399 -- p_organization_id - Restricts LOV SQL to current org
400 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
401 -- p_serial_number - Restricts LOV SQL to the serial entered
402 --
403 -- Output parameters:
404 -- x_serialsLOV - Returns LOV rows as reference cursor
405 --
406 -- Functions: This API is to return valid to serial numbers for
407 -- update status of from_serial_number
408
409 PROCEDURE get_serial_lov_status(
410 x_seriallov OUT NOCOPY t_genref,
411 p_organization_id IN NUMBER,
412 p_inventory_item_id IN NUMBER,
413 p_from_lot_number IN VARCHAR2,
414 p_to_lot_number IN VARCHAR2,
415 p_serial_number IN VARCHAR2);
416
417 -- Name: GET_TO_STATUS_SERIAL_LOV
418 --
419 -- Input parameters:
420 -- p_organization_id - Restricts LOV SQL to current org
421 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
422 -- p_from_serial_number - Starting point of serial number
423 -- p_serial_number - Restricts LOV SQL to the serial entered
424 --
425 -- Output parameters:
426 -- x_serialsLOV - Returns LOV rows as reference cursor
427 --
428 -- Functions: This API is to return valid to serial numbers for
429 -- update to serial number
430
431 PROCEDURE get_to_status_serial_lov(
432 x_seriallov OUT NOCOPY t_genref,
433 p_organization_id IN NUMBER,
434 p_inventory_item_id IN NUMBER,
435 p_from_lot_number IN VARCHAR2,
436 p_to_lot_number IN VARCHAR2,
437 p_from_serial_number IN VARCHAR2, p_serial_number IN VARCHAR2);
438
439 -- Name: GET_SERIAL_LOV_LPN
440 --
441 -- Input parameters:
442 -- p_LPN_Id which restricts LOV SQL to current LPN
443 -- p_org_id organization_id
444 -- p_item_id which restricts LOV SQL to current item
448 -- Output parameters:
445 -- p_lot which restricts LOV SQL to the lot
446 -- p_shipment_header_id which restricts LOV SQL to the shipment being received
447 --
449 -- x_serial_number returns LOV rows as reference cursor
450 --
451 -- Functions: This API is to return serial numbers for a given LPN
452
453 PROCEDURE get_serial_lov_lpn(
454 x_serial_number OUT NOCOPY t_genref,
455 p_lpn_id IN NUMBER,
456 p_organization_id IN NUMBER,
457 p_item_id IN NUMBER := NULL,
458 p_lot IN VARCHAR2 := NULL,
459 p_serial IN VARCHAR2);
460
461 -- Name: GET_SERIAL_INSPECT_LOV_RCV
462 --
463 -- Input parameters:
464 -- p_Organization_Id which restricts LOV SQL to current org
465 -- p_item_id which restricts LOV SQL to current item
466 -- p_lpn_id which restricts serial numbers to LPN that is being inspected
467 -- p_serial which restricts LOV SQL to the serial entered
468 --
469 -- Output parameters:
470 -- x_serial_number returns LOV rows as reference cursor
471 --
472 -- Functions: This API is to return serial numbers for mobile inspection
473 --
474 PROCEDURE get_serial_inspect_lov_rcv(
475 x_serial_number OUT NOCOPY t_genref,
476 p_organization_id IN NUMBER,
477 p_item_id IN NUMBER,
478 p_lpn_id IN NUMBER,
479 p_serial IN VARCHAR2,
480 p_lot_number IN VARCHAR2 DEFAULT NULL );
481
482 -- Name: GET_SERIAL_LOV_SO
483 --
484 -- Input parameters:
485 -- p_Organization_Id which restricts LOV SQL to current org
486 -- p_inventory_item_id which restricts LOV SQL to current item
487 -- p_subinventory_code which restricts LOV SQL to current sub
488 -- p_locator_id which restricts LOV SQL to current locator
489 -- p_revision which restricts LOV SQL to current revision
490 -- p_lot_number which restricts LOV SQL to current lot
491 -- p_serial_number which restricts LOV SQL to the serial entered
492 --
493 -- Output parameters:
494 -- x_serial_number returns LOV rows as reference cursor
495 --
496 -- Functions: This API is to return serial numbers for mobile inspection
497 --
498 PROCEDURE get_serial_lov_so(
499 x_serial OUT NOCOPY t_genref
500 , p_delivery_id IN NUMBER
501 , p_inventory_item_id IN NUMBER
502 , p_organization_id IN NUMBER
503 , p_subinventory_code IN VARCHAR2
504 , p_locator_id IN NUMBER
505 , p_revision IN VARCHAR2
506 , p_lot_number IN VARCHAR2
507 , p_serial_number IN VARCHAR2
508 );
509
510 -- Name: GET_CONT_SERIAL_LOV
511 --
512 -- Input parameters:
513 -- p_Organization_Id which restricts LOV SQL to current org
514 -- p_item_id which restricts LOV SQL to current item
515 -- p_lpn_id which restricts LOV SQL to current lpn
516 -- p_serial_number which restricts LOV SQL to the serial entered
517 --
518 -- Output parameters:
519 -- x_serial_number returns LOV rows as reference cursor
520 --
521 -- Functions: This API is to return serial numbers for mobile inspection
522
523 PROCEDURE Get_Cont_Serial_Lov(
524 x_serial_number OUT NOCOPY t_genref,
525 p_organization_id IN NUMBER,
526 p_item_id IN NUMBER,
527 p_lpn_id IN NUMBER,
528 p_revision IN VARCHAR2,
529 p_lot_number IN VARCHAR2,
530 p_serial IN VARCHAR2);
531
532 PROCEDURE Get_Split_Cont_Serial_Lov(
533 x_serial_number OUT NOCOPY t_genref,
534 p_organization_id IN NUMBER,
535 p_item_id IN NUMBER,
536 p_lpn_id IN NUMBER,
537 p_revision IN VARCHAR2,
538 p_lot_number IN VARCHAR2,
539 p_transaction_subtype IN NUMBER,
540 p_serial IN VARCHAR2);
541
542
543 PROCEDURE get_pupcont_serial_lov(
544 x_serial_number OUT NOCOPY t_genref
545 , p_organization_id IN NUMBER
546 , p_item_id IN NUMBER
547 , p_lpn_id IN NUMBER
548 , p_revision IN VARCHAR2
549 , p_lot_number IN VARCHAR2
550 , p_serial IN VARCHAR2
551 , p_txn_type_id IN NUMBER := 0
552 , p_wms_installed IN VARCHAR2 := 'TRUE'
553 );
554
555 -- Name: GET_INV_SERIAL_LOV
556 --
557 -- Input parameters:
558 -- p_Organization_Id which restricts LOV SQL to current org
559 -- p_inventory_item_id which restricts LOV SQL to current item
560 -- p_subinventory_code which restricts LOV SQL to current sub
561 -- p_locator_id which restricts LOV SQL to current locator
562 -- p_revision which restricts LOV SQL to current revision
563 -- p_lot_number which restricts LOV SQL to current lot
564 -- p_serial_number which restricts LOV SQL to the serial entered
565 --
566 -- Output parameters:
567 -- x_serial_number returns LOV rows as reference cursor
568 --
569 -- Functions: This API is to return serial numbers for mobile inspection
573 p_organization_id IN NUMBER,
570 --
571 PROCEDURE get_inv_serial_lov(
572 x_serial OUT NOCOPY t_genref,
574 p_inventory_item_id IN NUMBER,
575 p_subinventory_code IN VARCHAR2,
576 p_locator_id IN VARCHAR2,
577 p_revision IN VARCHAR2,
578 p_lot_number IN VARCHAR2,
579 p_serial_number IN VARCHAR2);
580
581 PROCEDURE get_inv_serial_lov_bulk(
582 x_serial OUT NOCOPY t_genref
583 , p_organization_id IN NUMBER
584 , p_inventory_item_id IN NUMBER
585 , p_subinventory_code IN VARCHAR2
586 , p_locator_id IN VARCHAR2
587 , p_revision IN VARCHAR2
588 , p_lot_number IN VARCHAR2
589 , p_from_serial_number IN VARCHAR2
590 , p_serial_number IN VARCHAR2
591 );
592
593 PROCEDURE get_pack_serial_lov(
594 x_serial OUT NOCOPY t_genref
595 , p_organization_id IN NUMBER
596 , p_inventory_item_id IN NUMBER
597 , p_subinventory_code IN VARCHAR2
598 , p_locator_id IN VARCHAR2
599 , p_revision IN VARCHAR2
600 , p_lot_number IN VARCHAR2
601 , p_serial_number IN VARCHAR2
602 );
603
604 -- Name: GET_CGUPDATE_SERIAL_LOV
605 PROCEDURE get_cgupdate_serial_lov(
606 x_serial OUT NOCOPY t_genref
607 , p_organization_id IN NUMBER
608 , p_inventory_item_id IN NUMBER
609 , p_lpn_id IN NUMBER
610 , p_serial_number IN VARCHAR2
611 , p_subinventory_code IN VARCHAR2
612 , p_locator_id IN NUMBER
613 , p_revision IN VARCHAR2
614 , p_cost_group_id IN NUMBER
615 );
616
617 -- added by manu gupta, copied from file from karun jain
618 PROCEDURE get_lot_expiration_date(
619 p_organization_id IN NUMBER,
620 p_inventory_item_id IN NUMBER,
621 p_lot_number IN VARCHAR2,
622 p_shelf_life_code IN NUMBER,
623 p_shelf_life_days IN NUMBER,
624 x_expiration_date OUT NOCOPY DATE);
625
626 PROCEDURE get_serial_lov_picking (
627 x_serial_number OUT NOCOPY t_genref
628 , p_organization_id IN NUMBER
629 , p_item_id IN NUMBER
630 , p_subinv_code IN VARCHAR2
631 , p_locator_id IN NUMBER
632 , p_serial IN VARCHAR2
633 , p_transaction_type_id IN NUMBER
634 , p_lpn_id IN NUMBER := NULL
635 , p_lot_number IN VARCHAR2
636 );
637
638
639 -- Name: GET_SERIAL_LOV_ALLOC_PICKING
640 --
641 -- Input parameters:
642 -- p_transaction_temp_id the transaction temp id from the
643 -- mtl_material_transactions_temp table
644 -- p_lot_code if '1' means not lot controlled
645 -- if '2' means IS lot controlled
646 -- the caller function would have to ensure that
647 -- these are the only numbers used.
648 -- Output parameters:
649 -- x_serial_number returns LOV rows as reference cursor
650 --
651 -- Functions: This API is to return serial numbers allocated at receipt
652 --
653 --
654
655 PROCEDURE get_serial_lov_alloc_picking(
656 x_serial_number OUT NOCOPY t_genref
657 , p_organization_id IN NUMBER
658 , p_item_id IN NUMBER
659 , p_subinv_code IN VARCHAR2
660 , p_locator_id IN NUMBER
661 , p_serial IN VARCHAR2
662 , p_transaction_type_id IN NUMBER
663 , p_lpn_id IN NUMBER
664 , p_transaction_temp_id IN NUMBER
665 , p_lot_code IN NUMBER
666 , p_lot_number IN VARCHAR2
667 );
668
669 PROCEDURE get_serial_lov_apl_picking(
670 x_serial_number OUT NOCOPY t_genref
671 , p_organization_id IN NUMBER
672 , p_item_id IN NUMBER
673 , p_subinv_code IN VARCHAR2
674 , p_locator_id IN NUMBER
675 , p_serial IN VARCHAR2
676 , p_transaction_type_id IN NUMBER
677 , p_lpn_id IN NUMBER := NULL
678 , p_lot_number IN VARCHAR2
679 , p_revision IN VARCHAR2
680 );
681
682 PROCEDURE get_serial_lov_apl_alloc_pick(
683 x_serial_number OUT NOCOPY t_genref
684 , p_organization_id IN NUMBER
685 , p_item_id IN NUMBER
686 , p_subinv_code IN VARCHAR2
687 , p_locator_id IN NUMBER
688 , p_serial IN VARCHAR2
689 , p_transaction_type_id IN NUMBER
690 , p_lpn_id IN NUMBER
691 , p_transaction_temp_id IN NUMBER
692 , p_lot_code IN NUMBER
693 , p_lot_number IN VARCHAR2
694 , p_revision IN VARCHAR2
695 );
696
697
698 -- Name: GET_ALL_SERIAL_LOV
699 --
700 -- Input parameters:
701 -- p_Organization_Id which restricts LOV SQL to current org
702 -- Output parameters:
706 --
703 -- x_serial_number returns LOV rows as reference cursor
704 --
705 -- Functions: This API is to return all serial numbers only by org
707 --
708 PROCEDURE get_all_serial_lov(
709 x_serial OUT NOCOPY t_genref,
710 p_organization_id IN NUMBER,
711 p_serial IN VARCHAR2);
712
713 PROCEDURE get_all_to_serial_lov(
714 x_serial OUT NOCOPY t_genref,
715 p_organization_id IN NUMBER,
716 p_from_serial_number IN VARCHAR2,
717 p_inventory_item_id IN NUMBER,
718 p_serial IN VARCHAR2);
719
720 --"Returns"
721 -- Name: GET_RETURN_SERIAL_LOV
722 --
723 -- Input parameters:
724 -- p_org_id which restricts LOV SQL to input org
725 -- p_lpn_id which restricts LOV SQL to input LPNID
726 -- p_item_id which restricts LOV SQL to input ITEMID
727 -- p_revision which restricts LOV SQL to input REVISION
728 -- p_serial which restricts LOV SQL to input Serial
729 -- p_upd_group_id which updates the group mark id of serial so that
730 -- the same serial is not selected again in any
731 -- other transaction
732 -- Output parameters:
733 -- x_serial_number returns LOV rows as reference cursor
734 --
735 -- Functions: This API is to return all serial numbers
736 -- that are marked as 'To Return' for the Item and LPN
737 --
738 --
739 PROCEDURE get_return_serial_lov(
740 x_serial OUT NOCOPY t_genref,
741 p_org_id IN NUMBER,
742 p_lpn_id IN NUMBER,
743 p_item_id IN NUMBER,
744 p_revision IN VARCHAR2,
745 p_serial IN VARCHAR2,
746 p_upd_group_id IN NUMBER DEFAULT 0);
747
748 --"Returns"
749
750
751
752 -- Name: GET_TASK_SERIAL_LOV
753 --
754 -- Input parameters:
755 -- p_temp_Id transaction_temp_id in mtl_material_transactions_temp
756 -- p_lot_code 1 if lolt controlled 0 if only serial controlled
757 -- Output parameters:
758 -- x_serial_number returns LOV rows as reference cursor
759 --
760 -- Functions: This API is to return serial numbers allocated
761 -- for a given task
762
763 PROCEDURE get_task_serial_lov(
764 x_serial_number OUT NOCOPY t_genref,
765 p_temp_id IN NUMBER,
766 p_lot_code IN NUMBER DEFAULT 0);
767
768 -- LOV query for serial triggered subinventory transfer
769 PROCEDURE get_serial_subxfr_lov(
770 x_serials OUT NOCOPY t_genref,
771 p_current_organization_id IN NUMBER,
772 p_serial_number IN VARCHAR2,
773 p_transaction_type_id IN NUMBER,
774 p_wms_installed IN VARCHAR2);
775
776 -- Name: GET_SERIAL_LOV_MO
777 --
778 -- Input parameters:
779 -- p_Organization_Id which restricts LOV SQL to current org
780 -- p_item_id which restricts LOV SQL to current item
781 -- p_serial which restricts LOV SQL to the serial entered
782 -- p_transaction_type_id trx_type_id
783 -- p_wms_installed whether WMS-enabled ORG
784 -- p_move_order_line_id which include the serials allocated to the
785 -- move order line
786 --
787 -- Output parameters:
788 -- x_serial_number returns LOV rows as reference cursor
789 --
790 -- Functions: This API is to return serial numbers limited by
791 -- the specified move order line and all other avialable serial
792 -- numbers and status='Received';
793 --
794 PROCEDURE get_serial_lov_mo(
795 x_serial_number OUT NOCOPY t_genref,
796 p_organization_id IN NUMBER,
797 p_item_id IN NUMBER,
798 p_serial IN VARCHAR2,
799 p_transaction_type_id IN NUMBER,
800 p_wms_installed IN VARCHAR2,
801 p_move_order_line_id IN NUMBER := NULL);
802
803 -- Name: GET_SERIAL_LOV_WMA_NEGISS
804 --
805 -- Input parameters:
806 -- p_Organization_Id which restricts LOV SQL to current org
807 -- p_item_id which restricts LOV SQL to current item
808 -- p_serial which restricts LOV SQL to the serial entered
809 -- p_transaction_type_id trx_type_id
810 -- p_wms_installed whether WMS-enabled ORG
811 --
812 -- Output parameters:
813 -- x_serial_number returns LOV rows as reference cursor
814 --
815 -- Functions: This API is to return serial numbers limited to
816 -- status of 'DEFINED NOT USED' and 'ISSUED OUT OF STORES' (to WIP).
817 -- Used by WMA negative issue.
818 --
819 PROCEDURE get_serial_lov_wma_negiss(
820 x_serial_number OUT NOCOPY t_genref,
821 p_organization_id IN NUMBER,
822 p_item_id IN NUMBER,
823 p_serial IN VARCHAR2,
824 p_lot_number IN VARCHAR2 DEFAULT NULL,
825 p_transaction_type_id IN NUMBER,
826 p_wms_installed IN VARCHAR2);
827
828 -- Name: GET_SERIAL_LOV_WMA_ISS
829 --
830 -- Input parameters:
831 -- p_Organization_Id which restricts LOV SQL to current org
832 -- p_item_id which restricts LOV SQL to current item
836 -- p_subinv which restricts LOV SQL to the chosen subinventory
833 -- p_serial which restricts LOV SQL to the serial entered
834 -- p_transaction_type_id trx_type_id
835 -- p_wms_installed whether WMS-enabled ORG
837 -- p_locator which restricts LOV SQL to the chosen locator
838 -- p_revision which restricts LOV SQL to the chosen revision
839 -- p_lot which restricts LOV SQL to the current lot
840 --
841 -- Output parameters:
842 -- x_serial_number returns LOV rows as reference cursor
843 --
844 -- Functions: This API is to return serial numbers limited to
845 -- a specific lot and status of 'RESIDES IN STORES'. Used by WMA
846 -- transaction that issue out of inventory.
847 --
848 PROCEDURE get_serial_lov_wma_iss(
849 x_serial_number OUT NOCOPY t_genref
850 , p_organization_id IN NUMBER
851 , p_item_id IN NUMBER
852 , p_serial IN VARCHAR2
853 , p_transaction_type_id IN NUMBER
854 , p_wms_installed IN VARCHAR2
855 , p_subinv IN VARCHAR2
856 , p_locator_id IN NUMBER
857 , p_revision IN VARCHAR2
858 , p_lot IN VARCHAR2
859 );
860
861 -- Name: GET_SERIAL_LOV_WMA_RCV
862 --
863 -- Input parameters:
864 -- p_Organization_Id which restricts LOV SQL to current org
865 -- p_item_id which restricts LOV SQL to current item
866 -- p_serial which restricts LOV SQL to the serial entered
867 -- p_transaction_type_id trx_type_id
868 -- p_wms_installed whether WMS-enabled ORG
869 --
870 -- Output parameters:
871 -- x_serial_number returns LOV rows as reference cursor
872 --
873 -- Functions: This API is to return serial numbers limited to
874 -- status of 'DEFINED NOT USED'. Used by WMA completion and negative
875 -- issue transactions.
876 --
877 PROCEDURE get_serial_lov_wma_rcv(
878 x_serial_number OUT NOCOPY t_genref
879 , p_organization_id IN NUMBER
880 , p_item_id IN NUMBER
881 , p_serial IN VARCHAR2
882 , p_lot_number IN VARCHAR2 DEFAULT NULL
883 , p_transaction_type_id IN NUMBER
884 , p_wms_installed IN VARCHAR2
885 , p_wip_entity_id IN NUMBER
886 );
887
888 -- Name: GET_SERIAL_LOV_WMA_RETCOMP
889 --
890 -- Input parameters:
891 -- p_Organization_Id which restricts LOV SQL to current org
892 -- p_item_id which restricts LOV SQL to current item
893 -- p_serial which restricts LOV SQL to the serial entered
894 -- p_transaction_type_id trx_type_id
895 -- p_wms_installed whether WMS-enabled ORG
896 --
897 -- Output parameters:
898 -- x_serial_number returns LOV rows as reference cursor
899 --
900 -- Functions: This API is to return serial numbers limited to
901 -- status of 'ISSUED OUT OF STORES". Use by WMA return transactions.
902 --
903 PROCEDURE get_serial_lov_wma_retcomp(
904 x_serial_number OUT NOCOPY t_genref,
905 p_organization_id IN NUMBER,
906 p_item_id IN NUMBER,
907 p_serial IN VARCHAR2,
908 p_transaction_type_id IN NUMBER,
909 p_wms_installed IN VARCHAR2,
910 p_wip_entity_id IN NUMBER,
911 p_lot IN VARCHAR2);
912
913 --this overloaded version of the component return lov accepts the
914 --item revision as well. The above version is left alone for
915 --backward compatibility and should not be used for future coding.
916 PROCEDURE get_serial_lov_wma_retcomp(
917 x_serial_number OUT NOCOPY t_genref,
918 p_organization_id IN NUMBER,
919 p_item_id IN NUMBER,
920 p_serial IN VARCHAR2,
921 p_transaction_type_id IN NUMBER,
922 p_wms_installed IN VARCHAR2,
923 p_wip_entity_id IN NUMBER,
924 p_lot IN VARCHAR2,
925 p_revision IN VARCHAR2);
926
927 PROCEDURE get_parent_serial_lov_wma(
928 x_serial_number OUT NOCOPY t_genref,
929 p_organization_id IN NUMBER,
930 p_item_id IN NUMBER,
931 p_serial IN VARCHAR2,
932 p_transaction_type_id IN NUMBER,
933 p_transaction_action_id IN NUMBER,
934 p_wip_entity_id IN NUMBER,
935 p_wip_assembly_id IN NUMBER := NULL,
936 p_wms_installed IN VARCHAR2);
937
938 PROCEDURE get_lot_flex_info(
939 p_org_id IN NUMBER
940 , p_lot_number IN VARCHAR2
941 , p_inventory_item_id IN NUMBER
942 , x_vendor_id OUT NOCOPY NUMBER
943 , x_grade_code OUT NOCOPY VARCHAR2
944 , x_origination_date OUT NOCOPY VARCHAR2
945 , x_date_code OUT NOCOPY VARCHAR2
946 , x_status_id OUT NOCOPY NUMBER
947 , x_change_date OUT NOCOPY VARCHAR2
948 , x_age OUT NOCOPY NUMBER
949 , x_retest_date OUT NOCOPY VARCHAR2
950 , x_maturity_date OUT NOCOPY VARCHAR2
951 , x_lot_attribute_category OUT NOCOPY VARCHAR2
955 , x_volume_uom OUT NOCOPY VARCHAR2
952 , x_item_size OUT NOCOPY NUMBER
953 , x_color OUT NOCOPY VARCHAR2
954 , x_volume OUT NOCOPY NUMBER
956 , x_place_of_origin OUT NOCOPY VARCHAR2
957 , x_best_by_date OUT NOCOPY VARCHAR2
958 , x_length OUT NOCOPY NUMBER
959 , x_length_uom OUT NOCOPY VARCHAR2
960 , x_recycled_content OUT NOCOPY NUMBER
961 , x_thickness OUT NOCOPY NUMBER
962 , x_thickness_uom OUT NOCOPY VARCHAR2
963 , x_width OUT NOCOPY NUMBER
964 , x_width_uom OUT NOCOPY VARCHAR2
965 , x_curl_wrinkle_fold OUT NOCOPY VARCHAR2
966 , x_c_attribute1 OUT NOCOPY VARCHAR2
967 , x_c_attribute2 OUT NOCOPY VARCHAR2
968 , x_c_attribute3 OUT NOCOPY VARCHAR2
969 , x_c_attribute4 OUT NOCOPY VARCHAR2
970 , x_c_attribute5 OUT NOCOPY VARCHAR2
971 , x_c_attribute6 OUT NOCOPY VARCHAR2
972 , x_c_attribute7 OUT NOCOPY VARCHAR2
973 , x_c_attribute8 OUT NOCOPY VARCHAR2
974 , x_c_attribute9 OUT NOCOPY VARCHAR2
975 , x_c_attribute10 OUT NOCOPY VARCHAR2
976 , x_c_attribute11 OUT NOCOPY VARCHAR2
977 , x_c_attribute12 OUT NOCOPY VARCHAR2
978 , x_c_attribute13 OUT NOCOPY VARCHAR2
979 , x_c_attribute14 OUT NOCOPY VARCHAR2
980 , x_c_attribute15 OUT NOCOPY VARCHAR2
981 , x_c_attribute16 OUT NOCOPY VARCHAR2
982 , x_c_attribute17 OUT NOCOPY VARCHAR2
983 , x_c_attribute18 OUT NOCOPY VARCHAR2
984 , x_c_attribute19 OUT NOCOPY VARCHAR2
985 , x_c_attribute20 OUT NOCOPY VARCHAR2
986 , x_d_attribute1 OUT NOCOPY VARCHAR2
987 , x_d_attribute2 OUT NOCOPY VARCHAR2
988 , x_d_attribute3 OUT NOCOPY VARCHAR2
989 , x_d_attribute4 OUT NOCOPY VARCHAR2
990 , x_d_attribute5 OUT NOCOPY VARCHAR2
991 , x_d_attribute6 OUT NOCOPY VARCHAR2
992 , x_d_attribute7 OUT NOCOPY VARCHAR2
993 , x_d_attribute8 OUT NOCOPY VARCHAR2
994 , x_d_attribute9 OUT NOCOPY VARCHAR2
995 , x_d_attribute10 OUT NOCOPY VARCHAR2
996 , x_n_attribute1 OUT NOCOPY NUMBER
997 , x_n_attribute2 OUT NOCOPY NUMBER
998 , x_n_attribute3 OUT NOCOPY NUMBER
999 , x_n_attribute4 OUT NOCOPY NUMBER
1000 , x_n_attribute5 OUT NOCOPY NUMBER
1001 , x_n_attribute6 OUT NOCOPY NUMBER
1002 , x_n_attribute7 OUT NOCOPY NUMBER
1003 , x_n_attribute8 OUT NOCOPY NUMBER
1004 , x_n_attribute9 OUT NOCOPY NUMBER
1005 , x_n_attribute10 OUT NOCOPY NUMBER
1006 , x_supplier_lot_number OUT NOCOPY VARCHAR2
1007 , x_territory_code OUT NOCOPY VARCHAR2
1008 , x_vendor_name OUT NOCOPY VARCHAR2
1009 , x_description OUT NOCOPY VARCHAR2
1010 );
1011
1012 -- Bug# 4176656
1013 -- New Procedure to get the Flexfield Data for a given Serial Number
1014 --
1015 --
1016 PROCEDURE get_serial_flex_info(
1017 p_serial_number IN VARCHAR2
1018 , p_inventory_item_id IN NUMBER
1019 , x_attribute_category OUT NOCOPY VARCHAR2
1020 , x_attribute1 OUT NOCOPY VARCHAR2
1021 , x_attribute2 OUT NOCOPY VARCHAR2
1022 , x_attribute3 OUT NOCOPY VARCHAR2
1023 , x_attribute4 OUT NOCOPY VARCHAR2
1024 , x_attribute5 OUT NOCOPY VARCHAR2
1025 , x_attribute6 OUT NOCOPY VARCHAR2
1026 , x_attribute7 OUT NOCOPY VARCHAR2
1027 , x_attribute8 OUT NOCOPY VARCHAR2
1028 , x_attribute9 OUT NOCOPY VARCHAR2
1029 , x_attribute10 OUT NOCOPY VARCHAR2
1030 , x_attribute11 OUT NOCOPY VARCHAR2
1031 , x_attribute12 OUT NOCOPY VARCHAR2
1032 , x_attribute13 OUT NOCOPY VARCHAR2
1033 , x_attribute14 OUT NOCOPY VARCHAR2
1034 , x_attribute15 OUT NOCOPY VARCHAR2
1035 , x_group_mark_id OUT NOCOPY NUMBER
1036 , x_serial_attribute_category OUT NOCOPY VARCHAR2
1037 , x_c_attribute1 OUT NOCOPY VARCHAR2
1038 , x_c_attribute2 OUT NOCOPY VARCHAR2
1039 , x_c_attribute3 OUT NOCOPY VARCHAR2
1040 , x_c_attribute4 OUT NOCOPY VARCHAR2
1041 , x_c_attribute5 OUT NOCOPY VARCHAR2
1042 , x_c_attribute6 OUT NOCOPY VARCHAR2
1043 , x_c_attribute7 OUT NOCOPY VARCHAR2
1044 , x_c_attribute8 OUT NOCOPY VARCHAR2
1045 , x_c_attribute9 OUT NOCOPY VARCHAR2
1046 , x_c_attribute10 OUT NOCOPY VARCHAR2
1047 , x_c_attribute11 OUT NOCOPY VARCHAR2
1048 , x_c_attribute12 OUT NOCOPY VARCHAR2
1049 , x_c_attribute13 OUT NOCOPY VARCHAR2
1050 , x_c_attribute14 OUT NOCOPY VARCHAR2
1051 , x_c_attribute15 OUT NOCOPY VARCHAR2
1052 , x_c_attribute16 OUT NOCOPY VARCHAR2
1053 , x_c_attribute17 OUT NOCOPY VARCHAR2
1054 , x_c_attribute18 OUT NOCOPY VARCHAR2
1055 , x_c_attribute19 OUT NOCOPY VARCHAR2
1056 , x_c_attribute20 OUT NOCOPY VARCHAR2
1060 , x_d_attribute4 OUT NOCOPY VARCHAR2
1057 , x_d_attribute1 OUT NOCOPY VARCHAR2
1058 , x_d_attribute2 OUT NOCOPY VARCHAR2
1059 , x_d_attribute3 OUT NOCOPY VARCHAR2
1061 , x_d_attribute5 OUT NOCOPY VARCHAR2
1062 , x_d_attribute6 OUT NOCOPY VARCHAR2
1063 , x_d_attribute7 OUT NOCOPY VARCHAR2
1064 , x_d_attribute8 OUT NOCOPY VARCHAR2
1065 , x_d_attribute9 OUT NOCOPY VARCHAR2
1066 , x_d_attribute10 OUT NOCOPY VARCHAR2
1067 , x_n_attribute1 OUT NOCOPY NUMBER
1068 , x_n_attribute2 OUT NOCOPY NUMBER
1069 , x_n_attribute3 OUT NOCOPY NUMBER
1070 , x_n_attribute4 OUT NOCOPY NUMBER
1071 , x_n_attribute5 OUT NOCOPY NUMBER
1072 , x_n_attribute6 OUT NOCOPY NUMBER
1073 , x_n_attribute7 OUT NOCOPY NUMBER
1074 , x_n_attribute8 OUT NOCOPY NUMBER
1075 , x_n_attribute9 OUT NOCOPY NUMBER
1076 , x_n_attribute10 OUT NOCOPY NUMBER
1077 );
1078
1079 -- Name: GET_ITEM_LOAD_SERIAL_LOV
1080 --
1081 -- Input parameters:
1082 -- p_lpn_id - Restricts LOV SQL to serial numbers
1083 -- stored within the given lpn ID
1084 -- p_organization_id - Restricts LOV SQL to current org
1085 -- p_item_id - Restricts LOV SQL to current inventory item
1086 -- p_lot_number - Restricts LOV SQL to the lot number entered
1087 -- p_serial_number - Restricts LOV SQL to the serial entered
1088 --
1089 -- Output parameters:
1090 -- x_serial_number - Returns LOV rows as reference cursor
1091 --
1092 -- Functions: This API will return serial numbers for Inbound Item Load
1093 -- functionality introduced in patchset J. This will
1094 -- return valid serials for a given item, org, and lot
1095 -- within an LPN.
1096 PROCEDURE get_item_load_serial_lov
1097 (x_serial_number OUT NOCOPY t_genref ,
1098 p_lpn_id IN NUMBER ,
1099 p_organization_id IN NUMBER ,
1100 p_item_id IN NUMBER ,
1101 p_lot_number IN VARCHAR2 := NULL ,
1102 p_serial_number IN VARCHAR2);
1103
1104
1105 -- Name: GET_SERIAL_LOAD_SERIAL_LOV
1106 --
1107 -- Input parameters:
1108 -- p_lpn_id - Restricts LOV SQL to serial numbers
1109 -- stored within the given lpn ID
1110 -- p_organization_id - Restricts LOV SQL to current org
1111 -- p_item_id - Restricts LOV SQL to current inventory item
1112 -- p_serial_number - Restricts LOV SQL to the serial entered
1113 --
1114 -- Output parameters:
1115 -- x_serial_number - Returns LOV rows as reference cursor
1116 --
1117 -- Functions: This API will return serial numbers for Inbound Item Load
1118 -- functionality for the serially counted flow introduced
1119 -- in patchset J. This will return valid serials for a
1120 -- given item and org within an LPN.
1121 PROCEDURE get_serial_load_serial_lov
1122 (x_serial_number OUT NOCOPY t_genref ,
1123 p_lpn_id IN NUMBER ,
1124 p_organization_id IN NUMBER ,
1125 p_item_id IN NUMBER ,
1126 p_serial_number IN VARCHAR2);
1127
1128 /**
1129 * This procedure fetches the Serial Numbers for an item
1130 * inside a LPN that "Resides in Receiving". It uses the
1131 * serial number in RCV_SERIALS_SUPPLY that corresponds to the
1132 * parent transaction.
1133 * This LOV would be called from the Item-based Putaway Drop
1134 * mobile page when the user confirms a quantity lesser than
1135 * the suggested quantity.
1136 * @param x_serial_number REF cursor containing the serial numbers fetched
1137 * @param p_lpn_id Identifer for the LPN containing the serials
1138 * @param p_organization_id Current Organization
1139 * @param p_inventory_item_id Inventory Item
1140 * @param p_lot_number Lot Number
1141 * @param p_txn_header_id Transaction Header ID. This would be used to match
1142 * with rcv_serials_supply
1143 * @param p_serial Serial Number entered on the UI
1144 **/
1145 PROCEDURE get_rcv_lpn_serial_lov(
1146 x_serial_number OUT NOCOPY t_genref
1147 , p_lpn_id IN NUMBER
1148 , p_organization_id IN NUMBER
1149 , p_inventory_item_id IN NUMBER
1150 , p_lot_number IN VARCHAR2 DEFAULT NULL
1151 , p_txn_header_id IN NUMBER
1152 , p_serial IN VARCHAR2);
1153
1154
1155 /* Bug 4574714 -Added the procedure to call insert into temp table */
1156
1157 PROCEDURE insert_temp_table_for_serials(
1158 p_organization_id IN NUMBER,
1159 p_item_id IN NUMBER,
1160 p_wms_installed IN VARCHAR2,
1161 p_oe_order_header_id IN NUMBER,
1162 x_returnSerialVal OUT NOCOPY VARCHAR2,
1163 x_return_status OUT NOCOPY VARCHAR2,
1164 x_errorcode OUT NOCOPY NUMBER );
1165
1166 /* Bug 4574714 -Added the procedure for the lov query */
1167
1168 PROCEDURE get_serial_lov_rma_restrict(
1169 x_serial_number OUT NOCOPY t_genref,
1170 p_organization_id IN NUMBER,
1171 p_item_id IN NUMBER,
1172 p_serial IN VARCHAR2,
1173 p_transaction_type_id IN NUMBER,
1174 p_wms_installed IN VARCHAR2,
1175 p_oe_order_header_id IN NUMBER,
1176 p_restrict IN VARCHAR2) ;
1177
1178 /* End of fix for Bug 4574714 */
1179
1180 /* Bug 5577789 (FP of bug 5520678)-Added the procedure to call insert into temp table for deliver */
1181
1182 PROCEDURE insert_RMA_serials_for_deliver(
1183 p_organization_id IN NUMBER,
1184 p_item_id IN NUMBER,
1185 p_wms_installed IN VARCHAR2,
1186 p_oe_order_header_id IN NUMBER,
1187 x_returnSerialVal OUT NOCOPY VARCHAR2,
1188 x_return_status OUT NOCOPY VARCHAR2,
1189 x_errorcode OUT NOCOPY NUMBER );
1190
1191 /* End of change for bug 5577789 (FP of bug 5520678) */
1192
1193
1194 /* Bug 4703782 (FP of BUG 4639427) - Added the procedure for the serial
1195 lov query for ASN receipts. */
1196
1197 PROCEDURE get_serial_lov_asn_rcv
1198 (x_serial_number OUT NOCOPY t_genref,
1199 p_organization_id IN NUMBER,
1200 p_item_id IN NUMBER,
1201 p_shipment_header_id IN NUMBER,
1202 p_serial IN VARCHAR2,
1203 p_transaction_type_id IN NUMBER,
1204 p_wms_installed IN VARCHAR2,
1205 p_from_lpn_id IN NUMBER DEFAULT NULL);
1206
1207 /* End of fix for Bug 4703782 */
1208 --bug 6928897
1209 PROCEDURE get_to_ostatus_serial_lov(
1210 x_seriallov OUT NOCOPY t_genref
1211 , p_organization_id IN NUMBER
1212 , p_inventory_item_id IN NUMBER
1213 , p_from_lot_number IN VARCHAR2
1214 , p_to_lot_number IN VARCHAR2
1215 , p_from_serial_number IN VARCHAR2
1216 , p_serial_number IN VARCHAR2
1217 );
1218
1219 PROCEDURE get_serial_lov_ostatus
1220 (x_seriallov OUT NOCOPY t_genref,
1221 p_organization_id IN NUMBER,
1222 p_inventory_item_id IN NUMBER,
1223 p_from_lot_number IN VARCHAR2,
1224 p_to_lot_number IN VARCHAR2,
1225 p_serial_number IN VARCHAR2
1226 );
1227 --end of fix for bug 6928897
1228
1229 --bug 6952533
1230 PROCEDURE GET_TO_LPN_SERIAL_LOV_OSTATUS(x_seriallov OUT NOCOPY t_genref
1231 , p_organization_id IN NUMBER
1232 , p_inventory_item_id IN NUMBER
1233 ,p_lpn_id NUMBER
1234 , p_lot_number IN VARCHAR2
1235 , p_from_serial_number IN VARCHAR2
1236 , p_serial_number IN VARCHAR2);
1237
1238 PROCEDURE GET_LPN_STATUS_SERIAL_LOV(x_seriallov OUT NOCOPY t_genref,
1239 p_organization_id IN NUMBER,
1240 p_inventory_item_id IN NUMBER,
1241 p_lpn_id IN NUMBER,
1242 p_lot_number IN VARCHAR2,
1243 p_serial_number IN VARCHAR2);
1244 --bug 6952533
1245
1246 END inv_ui_item_att_lovs;
[Home] [Help]