1 PACKAGE inv_inv_lovs AS
2 /* $Header: INVINVLS.pls 120.5.12010000.2 2008/09/25 17:39:24 abasheer ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_LOT_LOV
7 --
8 -- Input parameters:
9 -- p_organization_id Organization ID
10 -- p_item_id Inventory Item id
11 -- p_lot_number Lot Number
12 -- p_transaction_type_id Used for Material Status Applicability Check
13 -- p_wms_installed Used for Material Status Applicability Check
14 -- p_lpn_id LPN ID
15 -- p_subinventory_code SubInventory Code
16 -- p_locator_id Locator ID
17 -- p_planning_org_id Planning Organization ID - Consignment and VMI Changes
18 -- p_planning_tp_type Planning TP Type - Consignment and VMI Changes
19 -- p_owning_org_id Owning Organization ID - Consignment and VMI Changes
20 -- p_owning_tp_type Owning TP Type - Consignment and VMI Changes
21 --
22 -- Output parameters:
23 -- x_lot_num_lov Returns the LOV rows as a Reference Cursor
24 --
25 -- Functions: This API returns Lot number for a given org and Item Id
26 PROCEDURE get_lot_lov(
27 x_lot_num_lov OUT NOCOPY t_genref
28 , p_organization_id IN NUMBER
29 , p_item_id IN NUMBER
30 , p_lot_number IN VARCHAR2
31 , p_transaction_type_id IN NUMBER
32 , p_wms_installed IN VARCHAR2
33 , p_lpn_id IN NUMBER DEFAULT NULL
34 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
35 , p_locator_id IN NUMBER DEFAULT NULL
36 , p_planning_org_id IN NUMBER DEFAULT NULL
37 , p_planning_tp_type IN NUMBER DEFAULT NULL
38 , p_owning_org_id IN NUMBER DEFAULT NULL
39 , p_owning_tp_type IN NUMBER DEFAULT NULL
40 );
41
42 -- Name: GET_LOT_LOV_FOR_RECEIVING
43 --
44 -- Input parameters:
45 -- p_Organization_Id which restricts LOV SQL to current org
46 -- p_item_id Inventory Item id
47 -- p_lot_number which restricts LOV SQL to the user input text
48 -- p_lpn_id which restricts LOV SQL to the given lpn
49 -- p_subinventory_code which restricts LOV SQL to the given sub
50 -- p_locator_id which restricts LOV SQL to the given locator
51 --
52 -- Output parameters:
53 -- x_lot_num_lov returns LOV rows as reference cursor
54 --
55 -- Functions: This API returns Lot number for a given org and
56 -- and Item Id
57 --
58
59 PROCEDURE get_lot_lov_for_receiving(
60 x_lot_num_lov OUT NOCOPY t_genref
61 , p_organization_id IN NUMBER
62 , p_item_id IN NUMBER
63 , p_lot_number IN VARCHAR2
64 , p_transaction_type_id IN NUMBER
65 , p_wms_installed IN VARCHAR2
66 , p_lpn_id IN NUMBER DEFAULT NULL
67 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
68 , p_locator_id IN NUMBER DEFAULT NULL
69 );
70
71 -- Name: ASN_LOT_LOV
72 --
73 -- Input parameters:
74 -- p_Organization_Id which restricts LOV SQL to current org
75 -- p_item_id Inventory Item id
76 -- p_lot_number which restricts LOV SQL to the user input text
77 -- p_source_header_id which restricts to the shipment
78 --
79 -- Output parameters:
80 -- x_lot_num_lov returns LOV rows as reference cursor
81 --
82 -- Functions: This API returns Lot number for a given org and
83 -- and Item Id
84 --
85 --
86
87 PROCEDURE asn_lot_lov(
88 x_lot_num_lov OUT NOCOPY t_genref
89 , p_organization_id IN NUMBER
90 , p_item_id IN NUMBER
91 , p_lot_number IN VARCHAR2
92 , p_transaction_type_id IN NUMBER
93 , p_wms_installed IN VARCHAR2
94 , p_lpn_id IN NUMBER DEFAULT NULL
95 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
96 , p_locator_id IN NUMBER DEFAULT NULL
97 , p_source_header_id IN NUMBER DEFAULT NULL
98 );
99
100 -- Name: GET_LOT_LOV_INT_SHP_RCV
101 --
102 -- Input parameters:
103 -- p_Organization_Id which restricts LOV SQL to current org
104 -- p_item_id Inventory Item id
105 -- p_shipment_header_id
106 -- p_lot_number which restricts LOV SQL to the user input text
107 --
108 -- Output parameters:
109 -- x_lot_num_lov returns LOV rows as reference cursor
110 --
111 -- Functions: This API returns Lot number for a given org, lpn
112 -- and Item Id
113 --
114 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
115 PROCEDURE get_lot_lov_int_shp_rcv(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_shipment_header_id IN NUMBER,p_lot_number IN VARCHAR2,
116 p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2 ,p_subinventory_code IN VARCHAR2 DEFAULT NULL,p_locator_id IN NUMBER DEFAULT NULL, p_from_lpn_id IN NUMBER DEFAULT NULL); -- Bug 6908946
117
118 -- Input parameters:
119 -- p_Organization_Id which restricts LOV SQL to current org
120 -- p_item_id Inventory Item id
121 -- p_lot_number which restricts LOV SQL to the user input text
122 --
123 -- Output parameters:
124 -- x_lot_num_lov returns LOV rows as reference cursor
125 --
126 -- Functions: This API returns Lot number for a given org and
127 -- and Item Id
128 --
129
130 PROCEDURE get_pack_lot_lov(
131 x_lot_num_lov OUT NOCOPY t_genref
132 , p_organization_id IN NUMBER
133 , p_item_id IN NUMBER
134 , p_revision IN VARCHAR2 := NULL
135 , p_subinventory_code IN VARCHAR2 := NULL
136 , p_locator_id IN NUMBER := 0
137 , p_lot_number IN VARCHAR2
138 , p_transaction_type_id IN NUMBER := 0
139 , p_wms_installed IN VARCHAR2 := 'TRUE'
140 );
141
142 -- Name: GET_INQ_LOT_LOV
143 --
144 -- Input parameters:
145 -- p_Organization_Id which restricts LOV SQL to current org
146 -- p_item_id Inventory Item id
147 -- p_lot_number which restricts LOV SQL to the user input text
148 --
149 -- Output parameters:
150 -- x_lot_num_lov returns LOV rows as reference cursor
151 --
152 -- Functions: This API returns Lot number for a given org and
153 -- and Item Id without status restrictiong for inquiry purpose.
154 --
155
156 PROCEDURE get_inq_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2);
157
158 PROCEDURE get_from_status_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2);
159
160 -- Name: GET_TO_STATUS_LOT_LOV
161 --
162 -- Input parameters:
163 -- p_Organization_Id which restricts LOV SQL to current org
164 -- p_item_id Inventory Item id
165 -- p_lot_number which restricts LOV SQL to the user input text
166 -- p_from_lot_number starting lot number
167 --
168 -- Output parameters:
169 -- x_lot_num_lov returns LOV rows as reference cursor
170 --
171 -- Functions: This API returns Lot number for a given org and
172 -- and Item Id
173 --
174
175
176 PROCEDURE get_to_status_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_from_lot_number IN VARCHAR2, p_lot_number IN VARCHAR2);
177
178 -- Name: GET_REASON_LOV
179 --
180 -- Input parameters:
181 -- p_reason which restricts LOV SQL to the user input text
182 --
183 -- Output parameters:
184 -- x_reason_lov returns LOV rows as reference cursor
185 --
186 -- Functions: This API returns Transaction Reasons
187 --
188
189 PROCEDURE get_reason_lov(x_reason_lov OUT NOCOPY t_genref, p_reason IN VARCHAR2);
190
191 -- Name: GET_REASON_LOV
192 -- Overloaed Procedure for Transaction Reason Security build. 4505091, nsrivast
193 -- Input parameters:
194 -- p_reason restricts LOV SQL to the user input text
195 -- p_txn_type_id restricts LOV SQL specific transaction type id.
196 -- Output parameters:
197 -- x_reason_lov returns LOV rows as reference cursor
198 --
199 -- Functions: This API returns Transaction Reasons
200 --
201 PROCEDURE get_reason_lov(x_reason_lov OUT NOCOPY t_genref, p_reason IN VARCHAR2, p_txn_type_id IN VARCHAR2 );
202
203
204 PROCEDURE get_to_org_lov(x_to_org_lov OUT NOCOPY t_genref, p_from_organization_id IN NUMBER, p_to_organization_code IN VARCHAR2);
205
206 -- used by org transfer
207 PROCEDURE get_to_org(x_organizations OUT NOCOPY t_genref, p_from_organization_id IN NUMBER, p_to_organization_code IN VARCHAR2);
208
209 PROCEDURE get_all_orgs(x_organizations OUT NOCOPY t_genref, p_organization_code IN VARCHAR2);
210
211 PROCEDURE get_cost_group(x_cost_group_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_cost_group IN VARCHAR2);
212
213 -- Name: GET_CGUPDATE_COST_GROUP
214 --
215 -- Input parameters:
216 -- p_organization_id Restricts LOV SQL to specific org
217 -- p_lpn_id Restricts LOV SQL to specific LPN
218 -- p_inventory_item_id Restricts LOV SQL to specific item
219 -- p_subinventory Restricts LOV SQL to specific sub
220 -- p_locator_id Restricts LOV SQL to specific loc if given
221 -- p_from_cost_group_id Restricts LOV SQL to not include the
222 -- from cost group if not null
223 -- p_from_cost_group Restricts LOV SQL to user input text
224 -- p_to_cost_group Restricts LOV SQL to user input text
225 --
226 -- Output parameters:
227 -- x_cost_group_lov Output reference cursor which stores
228 -- the LOV rows for valid cost groups
229 --
230 -- Functions: This API returns a reference cursor for valid cost groups
231 -- in Cost Group update UI associated with the given parameters
232 --
233 PROCEDURE get_cgupdate_cost_group(
234 x_cost_group_lov OUT NOCOPY t_genref
235 , p_organization_id IN NUMBER
236 , p_lpn_id IN NUMBER
237 , p_inventory_item_id IN NUMBER
238 , p_revision IN VARCHAR2
239 , p_subinventory_code IN VARCHAR2
240 , p_locator_id IN NUMBER
241 , p_from_cost_group_id IN NUMBER
242 , p_from_cost_group IN VARCHAR2
243 , p_to_cost_group IN VARCHAR2
244 );
245
246 -- Name: GET_PHYINV_COST_GROUP
247 --
248 -- Input parameters:
249 -- p_organization_id Restricts LOV SQL to specific org
250 -- p_cost_group Restricts LOV SQL to user inputted text
251 -- p_inventory_item_id Restricts LOV SQL to specific item
252 -- p_subinventory Restricts LOV SQL to specific sub
253 -- p_locator_id Restricts LOV SQL to specific loc if given
254 -- p_dynamic_entry_flag Indicates whether or not dynamic
255 -- entries are allowed
259 --
256 -- p_physical_inventory_id Restricts LOV SQL to specific physical inventory
257 -- p_parent_lpn_id Restricts LOV SQL to specific parent
258 -- LPN if given
260 -- Output parameters:
261 -- x_cost_group_lov Output reference cursor which stores
262 -- the LOV rows for valid cost groups
263 --
264 -- Functions: This API returns a reference cursor for valid cost groups
265 -- associated with the given parameters
266 --
267 PROCEDURE get_phyinv_cost_group(
268 x_cost_group_lov OUT NOCOPY t_genref
269 , p_organization_id IN NUMBER
270 , p_cost_group IN VARCHAR2
271 , p_inventory_item_id IN NUMBER
272 , p_subinventory IN VARCHAR2
273 , p_locator_id IN NUMBER
274 , p_dynamic_entry_flag IN NUMBER
275 , p_physical_inventory_id IN NUMBER
276 , p_parent_lpn_id IN NUMBER
277 );
278
279 -- Name: GET_CYC_COST_GROUP
280 --
281 -- Input parameters:
282 -- p_organization_id Restricts LOV SQL to specific org
283 -- p_cost_group Restricts LOV SQL to user inputted text
284 -- p_inventory_item_id Restricts LOV SQL to specific item
285 -- p_subinventory Restricts LOV SQL to specific sub
286 -- p_locator_id Restricts LOV SQL to specific loc if given
287 -- p_unscheduled_entry Indicates whether or not unscheduled
288 -- entries are allowed
289 -- p_cycle_count_header_id Restricts LOV SQL to specific cycle count
290 -- p_parent_lpn_id Restricts LOV SQL to specific parent
291 -- LPN if given
292 --
293 -- Output parameters:
294 -- x_cost_group_lov Output reference cursor which stores
295 -- the LOV rows for valid cost groups
296 --
297 -- Functions: This API returns a reference cursor for valid cost groups
298 -- associated with the given parameters in a cycle count
299 --
300 PROCEDURE get_cyc_cost_group(
301 x_cost_group_lov OUT NOCOPY t_genref
302 , p_organization_id IN NUMBER
303 , p_cost_group IN VARCHAR2
304 , p_inventory_item_id IN NUMBER
305 , p_subinventory IN VARCHAR2
306 , p_locator_id IN NUMBER
307 , p_unscheduled_entry IN NUMBER
308 , p_cycle_count_header_id IN NUMBER
309 , p_parent_lpn_id IN NUMBER
310 );
311
312 PROCEDURE get_txn_types(x_txntypelov OUT NOCOPY t_genref, p_transaction_action_id IN NUMBER, p_transaction_source_type_id IN NUMBER, p_transaction_type_name IN VARCHAR2);
313
314 -- Name: GET_ITEM_LOT_LOV
315 --
316 -- Input parameters:
317 -- p_wms_installed which restricts LOV SQL to wms installed
318 -- p_Organization_Id which restricts LOV SQL to current org
319 -- p_txn_type_id which restricts LOV SQL to txn type
320 -- p_inventory_item_id which restricts LOV SQL to inventory item
321 -- p_lot_number which restricts LOV SQL to the user input text
322 -- p_project_id which restricts LOV SQL to project
323 -- p_task_id which restricts LOV SQL to task
324 --
325 -- Output parameters:
326 -- x_lot_num_lov returns LOV rows as reference cursor
327 --
328 -- Functions: This API returns Lot number for a given org
329 --
330 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
331 PROCEDURE get_item_lot_lov(
332 x_lot_num_lov OUT NOCOPY t_genref
333 , p_wms_installed IN VARCHAR2
334 , p_organization_id IN NUMBER
335 , p_txn_type_id IN NUMBER
336 , p_inventory_item_id IN VARCHAR2
337 , p_lot_number IN VARCHAR2
338 , p_project_id IN NUMBER DEFAULT NULL
339 , p_task_id IN NUMBER DEFAULT NULL
340 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
341 , p_locator_id IN NUMBER DEFAULT NULL
342 );
343
344 -- Name: GET_ITEM_LOT_LOV, overloaded with lot status id
345 --
346 -- Input parameters:
347 -- p_wms_installed which restricts LOV SQL to wms installed
348 -- p_Organization_Id which restricts LOV SQL to current org
349 -- p_txn_type_id which restricts LOV SQL to txn type
350 -- p_inventory_item_id which restricts LOV SQL to inventory item
351 -- p_lot_number which restricts LOV SQL to the user input text
352 -- p_project_id which restricts LOV SQL to project
353 -- p_task_id which restricts LOV SQL to task
354 -- p_status_id which restricts LOV SQL to lot_status
355 --
356 -- Output parameters:
357 -- x_lot_num_lov returns LOV rows as reference cursor
358 --
359 -- Functions: This API returns Lot number for a given org
360 --
361
362 PROCEDURE get_item_lot_lov(
363 x_lot_num_lov OUT NOCOPY t_genref
364 , p_wms_installed IN VARCHAR2
365 , p_organization_id IN NUMBER
366 , p_txn_type_id IN NUMBER
367 , p_inventory_item_id IN VARCHAR2
371 , p_status_id IN NUMBER
368 , p_lot_number IN VARCHAR2
369 , p_project_id IN NUMBER DEFAULT NULL
370 , p_task_id IN NUMBER DEFAULT NULL
372 );
373
374 PROCEDURE get_account_alias(x_accounts_info OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_description IN VARCHAR2);
375
376 PROCEDURE get_accounts(x_accounts OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2);
377
378 --
379 -- Get accounts specific to a Move Order
380 --
381 PROCEDURE get_mo_accounts(x_accounts OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2);
382
383 -- Name: GET_PHYINV_LOT_LOV
384 --
385 -- Input parameters:
386 -- p_organization_id - Restricts LOV SQL to current org
387 -- p_subinventory_code - Restricts LOV SQL to current subinventory
388 -- p_locator_id - Restricts LOV SQL to current locator
389 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
390 -- p_lot_number - Restricts LOV SQL to the user input text
391 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
392 -- p_physical_inventory_id - Restricts LOV SQL to current physical inventory
393 -- p_parent_lpn_id - Restricts LOV SQL to lot numbers within
394 -- the given parent lpn ID
395 --
396 -- Output parameters:
397 -- x_lots - returns LOV rows as reference cursor
398 --
399 -- Functions: This API returns lot number for a given org and inventory
400 -- item within a particular physical inventory
401 --
402
403 PROCEDURE get_phyinv_lot_lov(
404 x_lots OUT NOCOPY t_genref
405 , p_organization_id IN NUMBER
406 , p_subinventory_code IN VARCHAR2
407 , p_locator_id IN NUMBER
408 , p_inventory_item_id IN NUMBER
409 , p_lot_number IN VARCHAR2
410 , p_dynamic_entry_flag IN NUMBER
411 , p_physical_inventory_id IN NUMBER
412 , p_parent_lpn_id IN NUMBER
413 );
414
415 -- Name: GET_CYC_LOT_LOV
416 --
417 -- Input parameters:
418 -- p_organization_id - Restricts LOV SQL to current org
419 -- p_subinventory_code - Restricts LOV SQL to current subinventory
420 -- p_locator_id - Restricts LOV SQL to current locator
421 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
422 -- p_lot_number - Restricts LOV SQL to the user input text
423 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
424 -- p_cycle_count_header_id - Restricts LOV SQL to current cycle count
425 -- p_parent_lpn_id - Restricts LOV SQL to lot numbers within
426 -- the given parent lpn ID
427 --
428 -- Output parameters:
429 -- x_lots - returns LOV rows as reference cursor
430 --
431 -- Functions: This API returns lot number for a given org and inventory
432 -- item within a particular cycle count
433 --
434
435 PROCEDURE get_cyc_lot_lov(
436 x_lots OUT NOCOPY t_genref
437 , p_organization_id IN NUMBER
438 , p_subinventory_code IN VARCHAR2
439 , p_locator_id IN NUMBER
440 , p_inventory_item_id IN NUMBER
441 , p_lot_number IN VARCHAR2
442 , p_unscheduled_entry IN NUMBER
443 , p_cycle_count_header_id IN NUMBER
444 , p_parent_lpn_id IN NUMBER
445 );
446
447 -- Name: GET_CGUPDATE_LOT_LOV
448 --
449 -- Input parameters:
450 -- p_Organization_Id restricts LOV SQL to current org
451 -- p_lpn_id
452 -- p_inventory_item_id restricts LOV SQL to Inventory Item id
453 -- p_revision
454 -- p_subinventory_code
455 -- p_locator_id
456 -- p_from_cost_Group_id
457 -- p_lot_number restricts LOV SQL to the user input text
458 --
459 -- Output parameters:
460 -- x_lot_num_lov returns LOV rows as reference cursor
461 --
462 -- Functions: This API returns Lot number for a given org and
463 -- and Item Id
464 --
465
466 PROCEDURE get_cgupdate_lot_lov(
467 x_lot_num_lov OUT NOCOPY t_genref
468 , p_organization_id IN NUMBER
469 , p_lpn_id IN NUMBER
470 , p_inventory_item_id IN NUMBER
471 , p_revision IN VARCHAR2
472 , p_subinventory_code IN VARCHAR2
473 , p_locator_id IN NUMBER
474 , p_from_cost_group_id IN NUMBER
475 , p_lot_number IN VARCHAR2
476 );
477
478 -- Name: GET_INSPECT_LOT_LOV
479 --
480 -- Input parameters:
481 -- p_Organization_Id which restricts LOV SQL to current org
482 -- p_item_id Inventory Item id
483 -- p_lpn_id LPN that is being inspected
484 -- p_lot_number which restricts LOV SQL to the user input text
485 -- p_uom_code the uom the user has chosen from the uom lov
486 --
487 -- Output parameters:
488 -- x_lot_num_lov returns LOV rows as reference cursor
492 --
489 --
490 -- Functions: This API returns Lot number for a given org and
491 -- and Item Id
493
494 PROCEDURE get_inspect_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
495 p_organization_id IN NUMBER,
496 p_item_id IN NUMBER,
497 p_lpn_id IN NUMBER,
498 p_lot_number IN VARCHAR2,
499 p_uom_code IN VARCHAR2 DEFAULT NULL);
500
501 -- Name: GET_CONT_LOT_LOV
502 --
503 -- Input parameters:
504 -- p_organization_id - Restricts LOV SQL to current org
505 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
506 -- p_parent_lpn_id - Restricts LOV SQL to lot numbers within
507 -- the given parent lpn ID
508 -- p_lot_number - Restricts LOV SQL to the user input text
509 --
510 --
511 -- Functions: This API returns lot number for a given org and inventory
512 -- item within a particular lpn
513 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
514 PROCEDURE get_cont_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lpn_id IN NUMBER,p_lot_number IN VARCHAR2,p_subinventory_code IN VARCHAR2 DEFAULT NULL,p_locator_id IN NUMBER DEFAULT NULL);
515
516 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
517 PROCEDURE get_split_cont_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lpn_id IN NUMBER,p_lot_number IN VARCHAR2,p_subinventory_code IN VARCHAR2 DEFAULT NULL,p_locator_id IN NUMBER DEFAULT NULL);
518
519 -- Name: GET_ALL_LOT_LOV
520 --
521 -- Input parameters:
522 -- p_organization_id - Restricts LOV SQL to current org
523 -- p_lot_number - Restricts LOV SQL to the user input text
524 --
525 --
526 -- Functions: This API returns all lot numbers for a given org
527 --
528 PROCEDURE get_all_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lot_number IN VARCHAR2);
529
530 -- Name: GET_OH_COST_GROUP_LOV
531 --
532 -- Input parameters:
533 -- p_organization_id - Restricts LOV SQL to current org
534 -- p_inventory_item_id - Restricts LOV SQL to current item id
535 -- p_subinventory_code - Restricts LOV SQL to current sub
536 -- p_locator_id - Restricts LOV SQL to current loc
537 -- p_cost_group - Restricts LOV SQL to the user input text
538 --
539 -- Functions: This API returns all lot numbers for a given org
540 --
541 PROCEDURE get_oh_cost_group_lov(x_cost_group OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_locator_id IN VARCHAR2, p_cost_group IN VARCHAR2);
542
543 PROCEDURE tdatechk(p_org_id IN INTEGER, p_transaction_date IN DATE, x_period_id OUT NOCOPY INTEGER);
544
545 -- Name: GET_LABEL_TYPE_LOV
546 --
547 -- Input parameters:
548 -- p_wms_installed true/false if wms is installed
549 -- p_lookup_type partial completion on lookup type
550 -- Functions: This API returns all label types
551 PROCEDURE get_label_type_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2);
552
553 -- Name: GET_BUSINESSFLOW_TYPE_LOV
554 --
555 -- Input parameters:
556 -- p_wms_installed TRUE/FALSE if WMS is installed
557 -- p_lookup_type Partial completion on lookup type
558 -- Functions: This API returns all label types
559 PROCEDURE get_businessflow_type_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2);
560
561 -- Name: GET_LABEL_TYPE_REPRINT_LOV
562 --
563 -- Input parameters:
564 -- p_wms_installed TRUE/FALSE if WMS is installed
565 -- p_lookup_type Partial completion on lookup type
566 -- Functions: This API returns all label types
567
568
569 PROCEDURE get_label_type_reprint_lov(x_source_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_lookup_type IN VARCHAR2);
570
571 -- Name: GET_NOTRX_ITEM_LOT_LOV
572 --
573 -- Input parameters:
574 -- p_organization_id - Restricts LOV SQL to current org
575 -- p_inventory_item_id - Restricts LOV SQL to current item id
576 -- Functions: This API returns all lot numbers for a given org
577 -- for items without transaction type
578 PROCEDURE get_notrx_item_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_lot_number IN VARCHAR2);
579
580 -- Name: GET_TD_LOT_LOV
581 --
582 -- Input parameters:
583 -- p_Organization_Id which restricts LOV SQL to current org
584 -- p_item_id Inventory Item id
585 -- p_lot_number which restricts LOV SQL to the user input text
586 -- p_lpn_id which restricts LOV SQL to the given lpn
587 -- p_subinventory_code which restricts LOV SQL to the given sub
588 -- p_locator_id which restricts LOV SQL to the given locator
589 -- p_txn_temp_id which restricts LOV SQL to the Allocated Lots
590 --
591 -- Output parameters:
592 -- x_lot_num_lov returns LOV rows as reference cursor
593 --
594 -- Functions: This API returns Lot number for a given org and
595 -- and Item Id
596 --
597
601 -- p_wms_installed true/false if wms is installed
598 -- Name: GET_ALL_LABEL_TYPE_LOV
599 --
600 -- Input parameters:
602 -- p_all_label_str translated string for all label types
603 -- p_lookup_type partial completion on lookup type
604 -- Functions: This API returns all label types
605
606 PROCEDURE GET_ALL_LABEL_TYPE_LOV(
607 x_source_lov OUT NOCOPY t_genref,
608 p_wms_installed IN VARCHAR2,
609 p_all_label_str IN VARCHAR2,
610 p_lookup_type IN VARCHAR2
611 );
612
613 PROCEDURE get_td_lot_lov(
614 x_lot_num_lov OUT NOCOPY t_genref
615 , p_organization_id IN NUMBER
616 , p_item_id IN NUMBER
617 , p_lot_number IN VARCHAR2
618 , p_transaction_type_id IN NUMBER
619 , p_wms_installed IN VARCHAR2
620 , p_lpn_id IN NUMBER DEFAULT NULL
621 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
622 , p_locator_id IN NUMBER DEFAULT NULL
623 , p_txn_temp_id IN NUMBER
624 );
625
626
627 PROCEDURE get_apl_lot_lov(
628 x_lot_num_lov OUT NOCOPY t_genref
629 , p_organization_id IN NUMBER
630 , p_item_id IN NUMBER
631 , p_lot_number IN VARCHAR2
632 , p_transaction_type_id IN NUMBER
633 , p_wms_installed IN VARCHAR2
634 , p_lpn_id IN NUMBER
635 , p_subinventory_code IN VARCHAR2
636 , p_locator_id IN NUMBER
637 , p_txn_temp_id IN NUMBER
638 );
639
640 --"Returns"
641 -- Name: GET_RETURN_LOT_LOV
642 --
643 -- Input parameters:
644 -- p_org_id - Restricts LOV SQL to input org
645 -- p_lpn_id - Restricts LOV SQL to input LPN_ID
646 -- p_item_id - Restricts LOV SQL to input item id
647 -- p_revision - Restricts LOV SQL to input Revision
648 -- p_lot_number - Restricts LOV SQL to input Lot with %
649
650 -- Functions: This API returns all lot numbers for a given LPN
651 -- and item that are marked for Return in LPN Contents
652 PROCEDURE get_return_lot_lov(x_lot_num_lov OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item_id IN NUMBER, p_revision IN VARCHAR2, p_lot_number IN VARCHAR2);
653
654 --"Returns"
655
656 -- Name: GET_LOT_LOV_FOR_UNLOAD
657 --
658 -- Input parameters:
659 -- p_temp_id transaction_temp_id
660 -- Output parameters:
661 -- x_lot_num_lov returns LOV rows as reference cursor
662 --
663 -- Functions: This API returns Lot numbers allocated for a given task
664
665 PROCEDURE get_lot_lov_for_unload(x_lot_num_lov OUT NOCOPY t_genref, p_temp_id IN NUMBER);
666
667 -- Name: GET_FLOW_SCHEDULE_LOV
668 -- Added by joabraha for jsheu
669 -- Input parameters:
670 -- p_organization_id p_schedule_number
671 -- Output parameters:
672 -- x_flow_lov returns LOV rows as reference cursor
673 --
674 -- Functions: This API returns flow schedule numbers
675 PROCEDURE get_flow_schedule_lov(x_flow_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER DEFAULT NULL, p_from_schedule_number IN VARCHAR2 DEFAULT NULL, p_schedule_number IN VARCHAR2);
676
677 PROCEDURE get_lot_control_from_org(x_lot_control_code OUT NOCOPY NUMBER, x_from_org_id OUT NOCOPY NUMBER, p_organization_id IN NUMBER, p_shipment_header_id IN NUMBER, p_item_id IN NUMBER);
678
679 -- Name: GET_FORMAT_LOV
680 --
681 -- Input parameters:
682 -- p_label_type_id SELECTED label type.
683 -- Functions: This API returns all formats for a specific label type.
684 PROCEDURE get_format_lov(x_format_lov OUT NOCOPY t_genref, p_label_type_id IN NUMBER, p_format_name IN VARCHAR2);
685
686 -- Name: GET_USER_PRINTERS_LOV
687 -- Added by joabraha
688 -- Input parameters:
689 -- p_printer_name partial completion on printer_name
690 -- Functions: This API returns all printers
691 PROCEDURE get_user_printers_lov(x_printer_lov OUT NOCOPY t_genref, p_printer_name IN VARCHAR2);
692
693 -- Name: GET_ITEM_LOAD_LOT_LOV
694 --
695 -- Input parameters:
696 -- p_organization_id - Restricts LOV SQL to current org
697 -- p_inventory_item_id - Restricts LOV SQL to current inventory item
698 -- p_lpn_id - Restricts LOV SQL to lot numbers within
699 -- the given lpn ID
700 -- p_lot_number - Restricts LOV SQL to the user input text
701 --
702 -- Output parameters:
703 -- x_lot_num_lov - returns LOV rows as reference cursor
704 --
705 -- Functions: This API returns lot number for a given org and inventory
706 -- item within a particular LPN used for Inbound Item
707 -- Load functionality in patchset J.
708 -- Added p_subinventory_code and p_locator_id parameters as part of onhand status support project
709
710 PROCEDURE get_item_load_lot_lov
711 (x_lot_num_lov OUT NOCOPY t_genref ,
712 p_organization_id IN NUMBER ,
713 p_item_id IN NUMBER ,
714 p_lpn_id IN NUMBER ,
715 p_lot_number IN VARCHAR2 ,
716 p_subinventory_code IN VARCHAR2 DEFAULT NULL ,
717 p_locator_id IN NUMBER DEFAULT NULL
718 );
719
720
721 FUNCTION validate_account_segments(
722 p_segments VARCHAR2,
723 p_data_set NUMBER
724 ) RETURN VARCHAR2;
725
726 --added for lpn status project to handle lot in lpn and loose
730 p_item_id IN NUMBER,
727 PROCEDURE get_from_onstatus_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
728 p_organization_id IN NUMBER,
729 p_lpn VARCHAR2 ,
731 p_lot_number IN VARCHAR2);
732
736 p_item_id IN NUMBER,
733 PROCEDURE get_to_onstatus_lot_lov(x_lot_num_lov OUT NOCOPY t_genref,
734 p_organization_id IN NUMBER,
735 p_lpn varchar2,
737 p_from_lot_number IN VARCHAR2,
738 p_lot_number IN VARCHAR2);
739
740
741
742 END inv_inv_lovs;