1 PACKAGE WMS_LPN_LOVS AUTHID CURRENT_USER AS
2 /* $Header: WMSLPNLS.pls 120.7.12020000.2 2013/01/31 11:23:26 ssingams ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_SOURCE_LOV
7 --
8 -- Input parameters:
9 -- p_lookup_type which restricts LOV SQL to the user input text
10 --
11 -- Output parameters:
12 -- x_source_lov returns LOV rows as reference cursor
13 --
14 -- Functions: This API returns valid source (context) from WMS_PREPACK_SOURCE
15 -- in mfg_lookups
16 --
17
18 PROCEDURE GET_SOURCE_LOV(x_source_lov OUT NOCOPY t_genref ,
19 p_lookup_type IN VARCHAR2 );
20
21 -- Name: GET_LABEL_PICK_LPN_LOV
22 --
23 -- Input parameters:
24 -- p_lpn which restricts LOV SQL to the user input text
25 --
26 -- Output parameters:
27 -- x_lpn_lov returns LOV rows as reference cursor
28 --
29 -- Functions: This API returns valid LPN and lpn_id which can
30 -- be used in picking by label
31 --
32
33 PROCEDURE GET_LABEL_PICK_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
34 p_lpn IN VARCHAR2,
35 p_org_id IN NUMBER,
36 p_sub_code IN VARCHAR2 DEFAULT NULL );
37
38 -- Name: GET_LPN_LOV
39 --
40 -- Input parameters:
41 -- p_lpn which restricts LOV SQL to the user input text
42 --
43 -- Output parameters:
44 -- x_lpn_lov returns LOV rows as reference cursor
45 --
46 -- Functions: This API returns valid LPN and lpn_id
47 --
48
49 PROCEDURE GET_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
50 p_lpn IN VARCHAR2 );
51
52 PROCEDURE GET_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
53 p_lpn IN VARCHAR2 ,
54 p_orgid IN VARCHAR2 );
55
56 -- Name: GET_WHERE_LPN_LOV
57 --
58 -- Input parameters:
59 -- p_lpn which restricts LOV SQL to the user input text
60 -- p_where_clause pass a where clause
61 --
62 -- Output parameters:
63 -- x_lpn_lov returns LOV rows as reference cursor
64 --
65 -- Functions: This API returns valid LPN and lpn_id
66 --
67
68 PROCEDURE GET_WHERE_LPN_LOV
69 (x_lpn_lov OUT NOCOPY t_genref,
70 p_lpn IN VARCHAR2,
71 p_where_clause IN VARCHAR2);
72
73 PROCEDURE GET_WHERE_PJM_LPN_LOV
74 (x_lpn_lov OUT NOCOPY t_genref,
75 p_lpn IN VARCHAR2,
76 p_where_clause IN VARCHAR2);
77
78 PROCEDURE GET_PUTAWAY_WHERE_LPN_LOV
79 (x_lpn_lov OUT NOCOPY t_genref,
80 p_lpn IN VARCHAR2,
81 p_organization_id IN VARCHAR2
82 );
83
84 -- Bug 2774506/2905646 : Added project_id and task_id to show LPN's belonging to PJM locators.
85 PROCEDURE GET_PICK_LOAD_LPN_LOV
86 (x_lpn_lov OUT NOCOPY t_genref,
87 p_lpn IN VARCHAR2,
88 p_organization_id IN NUMBER,
89 p_revision IN VARCHAR2,
90 p_inventory_item_id IN NUMBER,
91 p_cost_group_id IN NUMBER,
92 p_subinventory_code IN VARCHAR2,
93 p_locator_id IN NUMBER,
94 p_project_id IN NUMBER := NULL,
95 p_task_id IN NUMBER := NULL);
96
97 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
98 -- This LOV fetches all the LPN in the given Org, containing the givn Item
99 PROCEDURE GET_ALL_APL_LPN_LOV
100 (x_lpn_lov OUT NOCOPY t_genref,
101 p_lpn IN VARCHAR2,
102 p_organization_id IN NUMBER,
103 p_revision IN VARCHAR2,
104 p_inventory_item_id IN NUMBER,
105 p_project_id IN NUMBER := NULL,
106 p_task_id IN NUMBER := NULL);
107
108 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
109 -- This LOV fetches all the LPN in the given Org, Sub, containing the givn Item
110 PROCEDURE GET_SUB_APL_LPN_LOV
111 (x_lpn_lov OUT NOCOPY t_genref,
112 p_lpn IN VARCHAR2,
113 p_organization_id IN NUMBER,
114 p_revision IN VARCHAR2,
115 p_inventory_item_id IN NUMBER,
116 p_subinventory_code IN VARCHAR2,
117 p_project_id IN NUMBER := NULL,
118 p_task_id IN NUMBER := NULL);
119
120
121 PROCEDURE validate_pick_load_lpn_lov
122 (p_fromlpn IN VARCHAR2,
123 p_organization_id IN NUMBER,
124 p_revision IN VARCHAR2,
125 p_inventory_item_id IN NUMBER,
126 p_cost_group_id IN NUMBER,
127 p_subinventory_code IN VARCHAR2,
128 p_locator_id IN NUMBER,
129 p_project_id IN NUMBER := NULL,
130 p_task_id IN NUMBER := NULL,
131 p_transaction_temp_id IN NUMBER,
132 p_serial_allocated IN VARCHAR2,
133 x_is_valid_fromlpn OUT NOCOPY VARCHAR2,
134 x_fromlpn_id OUT NOCOPY NUMBER);
135
136 PROCEDURE GET_PICK_LOAD_TO_LPN_LOV
137 (x_lpn_lov OUT NOCOPY t_genref,
138 p_lpn IN VARCHAR2
139 );
140
141 PROCEDURE validate_pick_load_to_lpn
142 (p_tolpn IN VARCHAR2,
143 x_is_valid_tolpn OUT NOCOPY VARCHAR2,
144 x_tolpn_id OUT NOCOPY NUMBER
145 );
146
147 PROCEDURE GET_PICK_DROP_LPN_LOV
148 (x_lpn_lov OUT NOCOPY t_genref,
149 p_lpn IN VARCHAR2,
150 p_pick_to_lpn_id IN NUMBER,
151 p_org_id IN NUMBER,
152 p_drop_sub IN VARCHAR2,
153 p_drop_loc IN NUMBER
154 );
155 -- Added p_drop_sub and p_drop_loc to be passed to the drop LPN LOV --vipartha
156
157 PROCEDURE GET_WHERE_SERIAL_LPN_LOV
158 (x_lpn_lov OUT NOCOPY t_genref,
159 p_lpn IN VARCHAR2,
160 p_where_clause IN VARCHAR2
161 );
162
163
164 PROCEDURE GET_PICK_LOAD_SERIAL_LPN_LOV
165 (x_lpn_lov OUT NOCOPY t_genref,
166 p_lpn IN VARCHAR2,
167 p_organization_id IN NUMBER,
168 p_revision IN VARCHAR2,
169 p_inventory_item_id IN NUMBER,
170 p_cost_group_id IN NUMBER,
171 p_subinventory_code IN VARCHAR2,
172 p_locator_id IN NUMBER,
173 p_transaction_temp_id IN NUMBER
174 );
175
176 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
177 -- This LOV fetches all the LPN in the given Org, containing the givn Item
178 -- and allocated serials
179 PROCEDURE GET_ALL_APL_SERIAL_LPN_LOV
180 (x_lpn_lov OUT NOCOPY t_genref,
181 p_lpn IN VARCHAR2,
182 p_organization_id IN NUMBER,
183 p_revision IN VARCHAR2,
184 p_inventory_item_id IN NUMBER,
185 p_transaction_temp_id IN NUMBER
186 );
187
188 -- Bug 3452436 : Added for patchset J project Advanced Pick Load.
189 -- This LOV fetches all the LPN in the given Org, sub, containing the givn Item
190 -- and allocated serials
191 PROCEDURE GET_SUB_APL_SERIAL_LPN_LOV
192 (x_lpn_lov OUT NOCOPY t_genref,
193 p_lpn IN VARCHAR2,
194 p_organization_id IN NUMBER,
195 p_revision IN VARCHAR2,
196 p_inventory_item_id IN NUMBER,
197 p_subinventory_code IN VARCHAR2,
198 p_transaction_temp_id IN NUMBER
199 );
200
201
202 -- Name: GET_PHYINV_PARENT_LPN_LOV
203 --
204 -- Input parameters:
205 -- p_lpn - restricts LOV SQL to the user inputted text
206 -- p_dynamic_entry_flag - determines whether or not dynamic
207 -- - entries are allowed. if not allowed,
208 -- - then only those LPN's that are
209 -- - associated WITH existing physical
210 -- - inventory tags are queried up.
211 -- p_physical_inventory_id - current physical inventory ID
212 -- p_organization_id - Organization that LPN's should be in
213 -- p_subinventory_code - Subinventory that LPN's should be in
214 -- p_locator_id - Locator that that LPN's should be in
215 -- p_project_id - Project that LPN's should be in
216 -- p_task_id - Task that LPN's should be in
217 --
218 -- Output parameters:
219 -- x_lpn_lov returns LOV rows as reference cursor
220 --
221 -- Functions: This API returns valid parent LPN's for use in
222 -- physical inventory counting
223 --
224
225 PROCEDURE GET_PHYINV_PARENT_LPN_LOV
226 (x_lpn_lov OUT NOCOPY t_genref ,
227 p_lpn IN VARCHAR2 ,
228 p_dynamic_entry_flag IN NUMBER ,
229 p_physical_inventory_id IN NUMBER ,
230 p_organization_id IN NUMBER ,
231 p_subinventory_code IN VARCHAR2 ,
232 p_locator_id IN NUMBER ,
233 p_project_id IN NUMBER := NULL,
234 p_task_id IN NUMBER := NULL
235 );
236
237
238 -- Name: GET_PHYINV_LPN_LOV
239 --
240 -- Input parameters:
241 -- p_lpn - restricts LOV SQL to the user inputted text
242 -- p_dynamic_entry_flag - determines whether or not dynamic
243 -- - entries are allowed. if not allowed,
244 -- - then only those LPN's that are
245 -- - associated WITH existing physical
246 -- - inventory tags are queried up.
247 -- p_physical_inventory_id - current physical inventory ID
248 -- p_organization_id - Organization that LPN's should be in
249 -- p_subinventory_code - Subinventory that LPN's should be in
250 -- p_locator_id - Locator that that LPN's should be in
251 -- p_parent_lpn_id - Parent LPN for which LPN's should be in
252 -- p_project_id - Project that LPN's should be in
253 -- p_task_id - Task that LPN's should be in
254 --
255 -- Output parameters:
256 -- x_lpn_lov returns LOV rows as reference cursor
257 --
258 -- Functions: This API returns valid children LPN's for use in
259 -- physical inventory counting
260 --
261
262 PROCEDURE GET_PHYINV_LPN_LOV
263 (x_lpn_lov OUT NOCOPY t_genref ,
264 p_lpn IN VARCHAR2 ,
265 p_dynamic_entry_flag IN NUMBER ,
266 p_physical_inventory_id IN NUMBER ,
267 p_organization_id IN NUMBER ,
268 p_subinventory_code IN VARCHAR2 ,
269 p_locator_id IN NUMBER ,
270 p_parent_lpn_id IN NUMBER ,
271 p_project_id IN NUMBER := NULL,
272 p_task_id IN NUMBER := NULL
273 );
274
275 -- Name: GET_PUP_LPN_LOV
276 --
277 -- Input parameters:
278 -- p_org_id - Organization that LPN's should be in
279 -- p_sub - Subinventory that LPN's should be in
280 -- p_loc_id - Locator that that LPN's should be in
281 -- p_not_lpn_id - the LPN to exclude in query (for merge and split LPN)
282 -- p_parent_lpn_id - Parent LPN for which LPN's should be in. If you want
283 -- - want items with parent_lpn is null enter '0' as param
284 -- p_lpn - restricts LOV SQL to the user inputted text
285 --
286 -- Output parameters:
287 -- x_lpn_lov returns LOV rows as reference cursor
288 --
289 -- Functions: This API returns valid children LPN's for use in
290 -- Pack Unpack functions
291 --
292
293 PROCEDURE GET_PUP_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
294 p_org_id IN NUMBER ,
295 p_sub IN VARCHAR2 := NULL ,
296 p_loc_id IN VARCHAR2 := NULL ,
297 p_not_lpn_id IN VARCHAR2 := NULL ,
298 p_parent_lpn_id IN VARCHAR2 := '0' ,
299 p_lpn IN VARCHAR2
300 );
301 PROCEDURE GET_PKUPK_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref ,
302 p_org_id IN NUMBER ,
303 p_sub IN VARCHAR2 := NULL ,
304 p_loc_id IN VARCHAR2 := NULL ,
305 p_not_lpn_id IN VARCHAR2 := NULL ,
306 p_parent_lpn_id IN VARCHAR2 := '0' ,
310 p_context IN NUMBER := 0,
307 p_txn_type_id IN NUMBER := 0 ,
308 p_incl_pre_gen_lpn IN VARCHAR2 :='TRUE',
309 p_lpn IN VARCHAR2,
311 p_project_id IN NUMBER := NULL,
312 p_task_id IN NUMBER := NULL,
313 p_mtrl_sts_check IN VARCHAR2 := 'Y', --Bug 3980914- Added the parameter
314 p_calling IN VARCHAR2 := NULL -- Bug 7210544
315 );
316
317 /* WMS - PJM Integration Changes */
318 PROCEDURE GET_PUTAWAY_LPN_LOV(
319 x_lpn_lov OUT NOCOPY t_genref,
320 p_org_id IN NUMBER,
321 p_sub IN VARCHAR2 := NULL,
322 p_loc_id IN VARCHAR2 := NULL,
323 p_orig_lpn_id IN VARCHAR2 := NULL,
324 p_lpn IN VARCHAR2,
325 p_project_id IN NUMBER := NULL,
326 p_task_id IN NUMBER := NULL,
327 p_lpn_context IN NUMBER := NULL,
328 p_rcv_sub_only IN NUMBER DEFAULT 2
329 );
330
331 -- Name: CHILD_LPN_EXISTS
332 --
333 -- Input parameters:
334 -- p_lpn_id - LPN ID to determine if it contains any child LPN's
335 --
336 -- Output parameters:
337 -- x_out - output number 1 = Yes, 2 = No
338 --
339 -- Functions: This procedure returns a number indicating whether or
340 -- not the given LPN ID contains any children
341 --
342
346
343 PROCEDURE CHILD_LPN_EXISTS(p_lpn_id IN NUMBER ,
344 x_out OUT NOCOPY NUMBER
345 );
347 -- Name: VALIDATE_PHYINV_LPN
348 --
349 -- Input parameters:
350 -- p_lpn_id - LPN ID we are trying to validate
351 -- p_dynamic_entry_flag - determines whether or not dynamic
352 -- - entries are allowed. if not allowed,
353 -- - then only those LPN's that are
354 -- - associated WITH existing physical
355 -- - inventory tags are queried up.
356 -- p_physical_inventory_id - current physical inventory ID
357 -- p_organization_id - Organization that LPN should be in
358 -- p_subinventory_code - Subinventory that LPN should be in
359 -- p_locator_id - Locator that that LPN should be in
360 --
361 -- Output parameters:
362 -- x_result - output result 1 = Yes, 2 = No
363 --
364 -- Functions: This procedure validates whether or not the given LPN
365 -- along with the given inputs exists. Used to manually
366 -- call the same validations as in the GET_PHYINV_LPN_LOV
367 -- procedure.
368
369 PROCEDURE VALIDATE_PHYINV_LPN
370 (p_lpn IN VARCHAR2 ,
371 p_dynamic_entry_flag IN NUMBER ,
372 p_physical_inventory_id IN NUMBER ,
373 p_organization_id IN NUMBER ,
374 p_subinventory_code IN VARCHAR2 ,
375 p_locator_id IN NUMBER ,
376 x_result OUT NOCOPY NUMBER);
377
378
379 -- Name: VALIDATE_CYCLECOUNT_LPN
380 --
381 -- Input parameters:
382 -- p_lpn_id - LPN ID we are trying to validate
383 -- p_unscheduled_entry - determines whether or not unscheduled
384 -- - entries are allowed. if not allowed,
385 -- - then only those LPN's that are
386 -- - associated WITH existing cycle
387 -- - count entries are queried up.
388 -- p_cycle_count_header_id - current cycle count header ID
389 -- p_organization_id - Organization that LPN should be in
390 -- p_subinventory_code - Subinventory that LPN should be in
391 -- p_locator_id - Locator that that LPN should be in
392 --
393 -- Output parameters:
394 -- x_result - output result 1 = Yes, 2 = No
395 --
396 -- Functions: This procedure validates whether or not the given LPN
397 -- along with the given inputs exists. Used to manually
398 -- call the same validations as in the GET_CYC_LPN_LOV
399 -- procedure.
400
401 PROCEDURE VALIDATE_CYCLECOUNT_LPN
402 (p_lpn IN VARCHAR2 ,
403 p_unscheduled_entry IN NUMBER ,
404 p_cycle_count_header_id IN NUMBER ,
405 p_organization_id IN NUMBER ,
406 p_subinventory_code IN VARCHAR2 ,
407 p_locator_id IN NUMBER ,
408 x_result OUT NOCOPY NUMBER);
409
410
411 -- Name: VALIDATE_LPN_AGAINST_ORG
412 --
413 -- Input parameters:
414 -- p_lpn_id - LPN ID we are trying to validate
415 -- p_organization_id - Organization that LPN should be in
416 --
417 -- Output parameters:
418 -- x_result - output result 1 = Yes, 2 = No
419 --
420 -- Functions: This procedure validates whether or not the given LPN
421 -- exists in the given org
422 --
423
424 PROCEDURE VALIDATE_LPN_AGAINST_ORG
425 (p_lpn IN VARCHAR2 ,
426 p_organization_id IN NUMBER ,
427 x_result OUT NOCOPY NUMBER);
428
429
430 TYPE LPN_RECORD IS RECORD
431 (license_plate_number VARCHAR2(30) ,
432 lpn_id NUMBER ,
433 inventory_item_id NUMBER ,
434 organization_id NUMBER ,
435 revision VARCHAR2(3) ,
436 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
437 lot_number VARCHAR2(80) ,
438 serial_number VARCHAR2(30) ,
439 subinventory_code VARCHAR2(10) ,
440 locator_id NUMBER ,
441 parent_lpn_id NUMBER ,
442 sealed_status NUMBER ,
443 gross_weight_uom_code VARCHAR2(3) ,
444 gross_weight NUMBER ,
445 content_volume_uom_code VARCHAR2(3) ,
446 content_volume NUMBER);
447
448 -- Name: GET_LPN_VALUES
449 --
450 -- Input parameters:
451 -- p_lpn_id - LPN ID we are trying to retrieve info for
452 -- p_organization_id - Organization that LPN is in
453 --
454 -- Output parameters:
455 -- x_license_plate_number -
456 -- x_lpn_id -
457 -- x_inventory_item_id -
458 -- x_organization_id -
459 -- x_revision -
460 -- x_lot_number -
461 -- x_serial_number -
462 -- x_subinventory_code -
463 -- x_locator_id -
464 -- x_parent_lpn_id -
465 -- x_sealed_status -
466 -- x_gross_weight_uom_code -
467 -- x_gross_weight -
468 -- x_content_volume_uom_code -
469 -- x_content_volume -
470 --
471 -- Functions: This procedure retrieves the LPN information for a given
475
472 -- LPN value. This is used in the mobile forms to manually
473 -- populate the values in an LPN LOV field bean.
474 --
476 PROCEDURE GET_LPN_VALUES
477 (p_lpn IN VARCHAR2 ,
478 p_organization_id IN NUMBER ,
479 x_license_plate_number OUT NOCOPY VARCHAR2 ,
480 x_lpn_id OUT NOCOPY NUMBER ,
481 x_inventory_item_id OUT NOCOPY NUMBER ,
482 x_organization_id OUT NOCOPY NUMBER ,
483 x_revision OUT NOCOPY VARCHAR2 ,
484 x_lot_number OUT NOCOPY VARCHAR2 ,
485 x_serial_number OUT NOCOPY VARCHAR2 ,
486 x_subinventory_code OUT NOCOPY VARCHAR2 ,
487 x_locator_id OUT NOCOPY NUMBER ,
488 x_parent_lpn_id OUT NOCOPY NUMBER ,
489 x_sealed_status OUT NOCOPY NUMBER ,
490 x_gross_weight_uom_code OUT NOCOPY VARCHAR2 ,
491 x_gross_weight OUT NOCOPY NUMBER ,
492 x_content_volume_uom_code OUT NOCOPY VARCHAR2 ,
493 x_content_volume OUT NOCOPY NUMBER);
494
495 -- Name: GET_INSPECT_LPN_LOV
496 --
497 -- Input parameters:
498 -- p_lpn which restricts LOV SQL to the user input text
499 --
500 -- Output parameters:
501 -- x_lpn_lov returns LOV rows as reference cursor
502 --
503 -- Functions: This API returns valid LPN and lpn_id whose contents have to be inspected
504 -- by Inspection Moboile form
505 --
506
507 PROCEDURE GET_INSPECT_LPN_LOV(
508 x_lpn_lov OUT NOCOPY t_genref
509 , p_lpn IN VARCHAR2
510 , p_organization_id IN NUMBER );
511
512
513
514 --
515 -- Name: GET_MO_LPN
516 --
517 -- Input parameters:
518 -- p_lpn - restricts LOV SQL to the user inputted text
519 -- p_inv_item_id - Inventory Item Id
520 -- p_organization_id - Organization that LPN's should be in
521 -- p_subinventory_code - Subinventory that LPN's should be in
522 -- p_locator_id - Locator that that LPN's should be in
523 --
524 -- Output parameters:
525 -- x_lpn_lov returns LOV rows as reference cursor
526 --
527 -- Functions: This API returns valid parent LPN's for use in
528 -- physical inventory counting
529 --
530 PROCEDURE GET_MO_LPN
531 (x_lpn_lov OUT NOCOPY t_genref ,
532 p_lpn IN VARCHAR2 ,
533 p_inv_item_id IN NUMBER ,
534 p_organization_id IN NUMBER ,
535 p_subinventory_code IN VARCHAR2 ,
536 p_locator_id IN NUMBER ,
537 p_qty IN NUMBER );
538
539
540 --
541 -- Name: GET_VENDOR_LPN
542 --
543 -- Input parameters:
544 -- p_lpn - restricts LOV SQL to the user inputted text
545 -- p_shipment_header_id - shipment header id
546 --
547 --
548 -- Output parameters:
549 -- x_lpn_lov returns LOV rows as reference cursor
550 --
551 -- Functions: This API returns valid vendor LPN's for a given shipment
552 --
553 -- Nested LPN changes
554 -- p_mode will have two values
555 -- 1. 'E' -- This means the page is called from Express Page.
556 -- If the Lov is called for express page then show
557 -- Check are there any existing Unprocessed RTI records
558 -- For It, This is because In Exprss Page If user pressed
559 -- <NextLPN> button, by this time we would not have processed
560 -- the LPN, so we should not show the same LPN again
561 -- 2. 'C' -- This means the page is called from confirm page.
562 -- If the page is called from confirm page then show LPNs
563 -- with immediate contents, and do not show empty LPNs.
564 --
565 PROCEDURE GET_VENDOR_LPN
566 (x_lpn_lov OUT NOCOPY t_genref ,
567 p_lpn IN VARCHAR2 ,
568 p_shipment_header_id IN VARCHAR2 ,
569 p_mode IN VARCHAR2 DEFAULT NULL,
570 p_inventory_item_id IN VARCHAR2 DEFAULT NULL
571 );
572
573 -- Name: GET_ITEM_LPN_LOV
574 --
575 -- Input parameters:
576 -- p_organization_id restricts LOV SQL to the user input
577 -- p_lot_number restricts LOV SQL to the user input
578 -- p_inventory_item_id restricts LOV SQL to the user input
579 -- p_revision restricts LOV SQL to the user input
580 -- p_lpn which restricts LOV SQL to the user input text
581 --
582 -- Output parameters:
583 -- x_lpn_lov returns LOV rows as reference cursor
584
585 PROCEDURE GET_ITEM_LPN_LOV
586 (x_lpn_lov OUT NOCOPY t_genref,
587 p_organization_id IN NUMBER,
588 p_lot_number IN VARCHAR2,
589 p_inventory_item_id IN NUMBER,
590 p_revision IN VARCHAR2,
591 p_lpn IN VARCHAR2);
592
593 -- Name: GET_LOT_LPN_LOV
594 --
595 -- Input parameters:
596 -- p_organization_id - Organization that LPNs should be in
597 -- p_lpn - restricts LOV SQL to the user input
598 --
599 -- Output parameters:
600 -- x_lpn_lov - returns LOV rows as reference cursor
601 --
602 -- Functions: This API returns LPNs for use in the result page of Lot Transactions.
603
604 PROCEDURE GET_LOT_LPN_LOV
605 (x_lpn_lov OUT NOCOPY t_genref,
606 p_organization_id IN NUMBER,
610 PROCEDURE GET_RCV_LPN
607 p_lpn IN VARCHAR2);
608
609
611 (x_lpn_lov OUT NOCOPY t_genref,
612 p_org_id IN NUMBER,
613 p_lpn IN VARCHAR2,
614 p_from_lpn_id IN VARCHAR2,
615 p_project_id IN NUMBER,
616 p_task_id IN NUMBER
617 );
618
619
620 -- Name: GET_CYC_PARENT_LPN_LOV
621 --
622 -- Input parameters:
623 -- p_lpn - restricts LOV SQL to the user inputted text
624 -- p_unscheduled_entry - determines whether or not unscheduled
625 -- - entries are allowed. if not allowed,
626 -- - then only those LPN's that are
627 -- - associated WITH existing cycle count
628 -- - entries are queried up.
629 -- p_cycle_count_header_id - current cycle count header ID
630 -- p_organization_id - Organization that LPN's should be in
631 -- p_subinventory_code - Subinventory that LPN's should be in
632 -- p_locator_id - Locator that that LPN's should be in
633 --
634 -- Output parameters:
635 -- x_lpn_lov returns LOV rows as reference cursor
636 --
637 -- Functions: This API returns valid parent LPN's for use in
638 -- cycle counting
639 --
640
641 PROCEDURE GET_CYC_PARENT_LPN_LOV
642 (x_lpn_lov OUT NOCOPY t_genref ,
643 p_lpn IN VARCHAR2 ,
644 p_unscheduled_entry IN NUMBER ,
645 p_cycle_count_header_id IN NUMBER ,
646 p_organization_id IN NUMBER ,
647 p_subinventory_code IN VARCHAR2 ,
648 p_locator_id IN NUMBER ,
649 p_project_id IN NUMBER ,
650 p_task_id IN NUMBER );
651
652
653 -- Name: GET_CYC_LPN_LOV
654 --
655 -- Input parameters:
656 -- p_lpn - restricts LOV SQL to the user inputted text
657 -- p_unscheduled_entry - determines whether or not unscheduled
658 -- - entries are allowed. if not allowed,
659 -- - then only those LPN's that are
660 -- - associated WITH existing cycle count
661 -- - entries are queried up.
662 -- p_cycle_count_header_id - current cycle count header ID
663 -- p_organization_id - Organization that LPN's should be in
664 -- p_subinventory_code - Subinventory that LPN's should be in
665 -- p_locator_id - Locator that that LPN's should be in
666 -- p_parent_lpn_id - Parent LPN for which LPN's should be in
667 --
668 -- Output parameters:
669 -- x_lpn_lov returns LOV rows as reference cursor
670 --
671 -- Functions: This API returns valid children LPN's for use in
672 -- cycle counting
673 --
674
675 PROCEDURE GET_CYC_LPN_LOV
676 (x_lpn_lov OUT NOCOPY t_genref ,
677 p_lpn IN VARCHAR2 ,
678 p_unscheduled_entry IN NUMBER ,
679 p_cycle_count_header_id IN NUMBER ,
680 p_organization_id IN NUMBER ,
681 p_subinventory_code IN VARCHAR2 ,
682 p_locator_id IN NUMBER ,
683 p_parent_lpn_id IN NUMBER ,
684 p_project_id IN NUMBER ,
685 p_task_id IN NUMBER );
686
687 -- Name: GET_CGUPDATE_LPN
688 --
689 -- Input parameters:
690 -- p_org_id - Organization that LPNs should be in
691 -- p_lpn - restricts LOV SQL to the user inputted text
692 --
693 -- Output parameters:
694 -- x_lpn_lov - returns LOV rows as reference cursor
695 --
696 -- Functions: This API returns onhand LPNs for use in the cost group
697 -- update UI
698 PROCEDURE GET_CGUPDATE_LPN
699 (x_lpn_lov OUT NOCOPY t_genref,
700 p_org_id IN NUMBER,
701 p_lpn IN VARCHAR2);
702
703 -- Name: GET_PALLET_LPN_LOV
704 --
705 -- Input parameters:
706 -- p_org_id restricts LOV SQL to the user input
707 -- p_lpn which restricts LOV SQL to the user input text
708 --
709 -- Output parameters:
710 -- x_lpn_lov returns LOV rows as reference cursor
711 PROCEDURE GET_PALLET_LPN_LOV(x_lpn_lov OUT NOCOPY t_genref,
712 p_org_id IN NUMBER,
713 p_lpn VARCHAR2
714 );
715
716
717 -- procedure to get the information for a LPN
718 PROCEDURE CHECK_LPN_LOV
719 ( p_lpn IN VARCHAR2,
720 p_organization_id IN NUMBER,
721 x_lpn_id OUT NOCOPY NUMBER,
722 x_inventory_item_id OUT NOCOPY NUMBER,
723 x_organization_id OUT NOCOPY NUMBER,
724 x_lot_number OUT NOCOPY VARCHAR2,
725 x_revision OUT NOCOPY VARCHAR2,
726 x_serial_number OUT NOCOPY VARCHAR2,
727 x_subinventory OUT NOCOPY VARCHAR2,
728 x_locator_id OUT NOCOPY NUMBER,
729 x_parent_lpn_id OUT NOCOPY NUMBER,
730 x_sealed_status OUT NOCOPY NUMBER,
731 x_gross_weight OUT NOCOPY NUMBER,
732 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
733 x_content_volume OUT NOCOPY NUMBER,
734 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
735 x_source_type_id OUT NOCOPY NUMBER,
736 x_source_header_id OUT NOCOPY NUMBER,
737 x_source_name OUT NOCOPY VARCHAR2,
738 x_source_line_id OUT NOCOPY NUMBER,
739 x_source_line_detail_id OUT NOCOPY NUMBER,
740 x_cost_group_id OUT NOCOPY NUMBER,
741 x_newLPN OUT NOCOPY VARCHAR2,
742 x_concat_segments OUT NOCOPY VARCHAR2,
746 p_createnewlpn_flag IN VARCHAR2
743 x_context OUT NOCOPY VARCHAR2,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_data OUT NOCOPY VARCHAR2,
747 );
748
749 /**********************************************************************************
750 WMS - PJM Integration Enhancements
751 Differences from CHECK_LPN_LOV
752 1. Returns the locator concatenated segments without SEGMENT19 and SEGMENT20.
753 2. Returns the Project ID, Project Number, Task ID and Task Number associated
754 with the locator.
755 **********************************************************************************/
756
757 PROCEDURE CHECK_PJM_LPN_LOV
758 (
759 p_lpn IN VARCHAR2,
760 p_organization_id IN NUMBER,
761 x_lpn_id OUT NOCOPY NUMBER,
762 x_inventory_item_id OUT NOCOPY NUMBER,
763 x_organization_id OUT NOCOPY NUMBER,
764 x_lot_number OUT NOCOPY VARCHAR2,
765 x_revision OUT NOCOPY VARCHAR2,
766 x_serial_number OUT NOCOPY VARCHAR2,
767 x_subinventory OUT NOCOPY VARCHAR2,
768 x_locator_id OUT NOCOPY NUMBER,
769 x_parent_lpn_id OUT NOCOPY NUMBER,
770 x_sealed_status OUT NOCOPY NUMBER,
771 x_gross_weight OUT NOCOPY NUMBER,
772 x_gross_weight_uom_code OUT NOCOPY VARCHAR2,
773 x_content_volume OUT NOCOPY NUMBER,
774 x_content_volume_uom_code OUT NOCOPY VARCHAR2,
775 x_source_type_id OUT NOCOPY NUMBER,
776 x_source_header_id OUT NOCOPY NUMBER,
777 x_source_name OUT NOCOPY VARCHAR2,
778 x_source_line_id OUT NOCOPY NUMBER,
779 x_source_line_detail_id OUT NOCOPY NUMBER,
780 x_cost_group_id OUT NOCOPY NUMBER,
781 x_newLPN OUT NOCOPY VARCHAR2,
782 x_concat_segments OUT NOCOPY VARCHAR2,
783 x_project_id OUT NOCOPY VARCHAR2,
784 x_project_number OUT NOCOPY VARCHAR2,
785 x_task_id OUT NOCOPY VARCHAR2,
786 x_task_number OUT NOCOPY VARCHAR2,
787 x_context OUT NOCOPY VARCHAR2,
788 x_return_status OUT NOCOPY VARCHAR2,
789 x_msg_data OUT NOCOPY VARCHAR2,
790 p_createnewlpn_flag IN VARCHAR2
791 );
792
793 -- procedure to get lpns based on lpn context if context
794 -- is left blank assumes all contexts are valid
795 PROCEDURE GET_CONTEXT_LPN_LOV
796 (x_lpn_lov OUT NOCOPY t_genref,
797 p_organization_id IN NUMBER,
798 p_context IN VARCHAR2,
799 p_lpn IN VARCHAR2
800 );
801
802 --"Returns"
803 -- procedure to get lpns that have atleast one Content
804 -- record as 'To Return'
805 PROCEDURE GET_RETURN_LPN
806 (x_lpn_lov OUT NOCOPY t_genref,
807 p_org_id IN NUMBER,
808 p_lpn IN VARCHAR2
809 );
810 --"Returns"
811
812 -- procedure to get the lpns shipped for an internal order.
813 PROCEDURE GET_REQEXP_LPN (
814 x_lpn_lov OUT NOCOPY t_genref,
815 p_lpn IN VARCHAR2 ,
816 p_requisition_header_id IN VARCHAR2 ,
817 p_mode IN VARCHAR2 DEFAULT NULL,
818 p_inventory_item_id IN VARCHAR2 DEFAULT NULL
819 );
820
821
822 -- procedure to get lpns available for update
823 PROCEDURE GET_UPDATE_LPN
824 (x_lpn_lov OUT NOCOPY t_genref,
825 p_org_id IN NUMBER,
826 p_lpn IN VARCHAR2);
827
828 PROCEDURE GET_BULK_PACK_LPN
829 (x_lpn_lov OUT NOCOPY t_genref,
830 p_org_id IN NUMBER,
831 p_lpn IN VARCHAR2,
832 p_subinventory IN VARCHAR2,
833 p_locator IN NUMBER
834 );
835
836 -- Start of comments
837 -- API name: Get_Picked_Split_From_LPNs
838 -- Type : Private
839 -- Pre-reqs: None.
840 -- Function: Returns a list of Staged (Picked) LPNs along with
841 -- the delivery_detail_id in WSH_DELIVERY_DETAILS for
842 -- that particular LPN. Used in Oubound LPN Split Page
843 -- for the From LPN field
844 -- Parameters:
845 -- IN: p_organization_id IN NUMBER Required
846 -- Organization ID where the LPN resides
847 -- p_lpn_id IN VARCHAR2 Required
848 -- Partial string value to limit search results
849 -- OUT: x_order_lov OUT NOCOPY T_GENREF
850 -- Standard LOV out parameter
851 -- Version : Current version 1.0
852 -- End of comments
853
854 PROCEDURE Get_Picked_Split_From_LPNs(
855 x_lpn_lov OUT NOCOPY t_genref
856 , p_organization_id IN NUMBER
857 , p_lpn_id IN VARCHAR2
858 );
859
860 --RTV Change 16197273
861 --New navigation is created for split,under Inbound flow.
862 --Created new prceedures for from and to_lpn fields specific for RTV ER.
863
864 PROCEDURE Get_Return_Split_From_LPNs(
865 x_lpn_lov OUT NOCOPY t_genref
866 , p_organization_id IN NUMBER
867 , p_lpn_id IN VARCHAR2
868 );
869
870 PROCEDURE Get_Return_Split_To_LPNs(
871 x_lpn_lov OUT NOCOPY t_genref
872 , p_organization_id IN NUMBER
873 , p_lpn_id IN VARCHAR2
874 );
875
876
877 --function to check if a sub is LPN Controlled
878 FUNCTION SUB_LPN_CONTROLLED(p_subinventory_code IN VARCHAR2,
882
879 p_org_id IN NUMBER)
880 RETURN VARCHAR2;
881
883 -- Name: GET_ITEM_LOAD_LPN_LOV
884 --
885 -- Input parameters:
886 -- p_organization_id - Organization that LPN's should be in
887 -- p_lpn_id - Source LPN ID we are loading material from
888 -- p_lpn_context - LPN context of source LPN
889 -- p_employee_id - Employee ID of person loading the material
890 -- p_into_lpn - Restricts Into LOV SQL to the user inputted text
891 --
892 -- Output parameters:
893 -- x_lpn_lov Returns LOV rows as reference cursor
894 --
895 -- Functions: This API returns valid Into LPN's for use in
896 -- Inbound Item Load for putaway as part of a
897 -- patchset J project.
898 --
899 PROCEDURE get_item_load_lpn_lov
900 (x_lpn_lov OUT NOCOPY t_genref ,
901 p_organization_id IN NUMBER ,
902 p_lpn_id IN NUMBER ,
903 p_lpn_context IN NUMBER ,
904 p_employee_id IN NUMBER ,
905 p_into_lpn IN VARCHAR2);
906
907
908 PROCEDURE get_from_gtmp_lov
909 (x_lpn_lov OUT NOCOPY t_genref ,
910 p_organization_id IN NUMBER ,
911 p_drop_type IN VARCHAR2 ,
912 p_lpn_name IN VARCHAR2
913 );
914
915
916 -- procedure to get lpns which have innerLPNs and suitable
917 -- for LPN-merge/LPN-break
918 PROCEDURE GET_RECONFIG_LPN
919 (x_lpn_lov OUT NOCOPY t_genref,
920 p_org_id IN NUMBER,
921 p_lpn IN VARCHAR2);
922
923 -- Procedure to get lpns in status 5 and 1 for the org, sub,locator combination. For bug 6708036
924 PROCEDURE GET_PICK_DROP_SUBXFR_LPN_LOV
925 ( x_lpn_lov OUT NOCOPY t_genref
926 , p_lpn IN VARCHAR2
927 , p_pick_to_lpn_id IN NUMBER
928 , p_org_id IN NUMBER
929 , p_drop_sub IN VARCHAR2
930 , p_drop_loc IN NUMBER
931 );
932
933 END WMS_LPN_LOVS;
[Home] [Help]