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