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