1 PACKAGE inv_ui_item_lovs AS
2 /* $Header: INVITMLS.pls 120.2.12010000.1 2008/07/24 01:36:50 appldev ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_ITEM_LOV
7 --
8 -- Input parameters:
9 -- p_Organization_Id which restricts LOV SQL to current org
10 -- p_(LOVField) which restricts LOV SQL to the user input text
11 -- e.g. AS% for item LOV's contanenated_segment
12 -- p_where_clause different LOV beans pass in different where clause string
13 -- for their LOV SQL
14 -- The String should start with AND and conform with dynamic
15 -- SQL syntax e.g. 'AND purchasing_enabled_flag = ''Y'''
16 --
17 -- Output parameters:
18 -- x_Items returns LOV rows as reference cursor
19 -- Functions: This procedure uses dynamic SQL to handle different where clauses for
20 -- LOV query. To addd more columns to LOV subfield, one should append the
21 -- new columns to the end of the existing ones. Specifically, one should
22 -- modify the following local variable, l_sql_stmt, in the packge body
23 --
24
25
26
27 PROCEDURE get_item_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_where_clause IN VARCHAR2);
28
29 PROCEDURE get_item_lov_sub_loc_moqd(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_locator_id IN NUMBER, p_where_clause IN VARCHAR2) ;
30
31 PROCEDURE get_transactable_items(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_transaction_action_id IN NUMBER, p_to_organization_id IN NUMBER DEFAULT NULL);
32
33 -- Name: GET_REVISION_LOV
34 --
35 -- Input parameters:
36 -- p_organization_id Organization ID
37 -- p_item_id Inventory Item id
38 -- p_revision Revision
39 -- p_planning_org_id Planning Organization ID - Consignment and VMI Changes
40 -- p_planning_tp_type Planning TP Type - Consignment and VMI Changes
41 -- p_owning_org_id Owning Organization ID - Consignment and VMI Changes
42 -- p_owning_tp_type Owning TP Type - Consignment and VMI Changes
43 --
44 -- Output parameters:
45 -- x_revs Returns LOV rows as Reference Cursor
46 --
47 -- Functions: This procedure returns valid Revisions after restricting it by
48 -- Org, Item, Planning and Owning criterions.
49 --
50 --
51 PROCEDURE get_revision_lov(
52 x_revs OUT NOCOPY t_genref
53 , p_organization_id IN NUMBER
54 , p_inventory_item_id IN NUMBER
55 , p_revision IN VARCHAR2
56 , p_planning_org_id IN NUMBER DEFAULT NULL
57 , p_planning_tp_type IN NUMBER DEFAULT NULL
58 , p_owning_org_id IN NUMBER DEFAULT NULL
59 , p_owning_tp_type IN NUMBER DEFAULT NULL
60 );
61
62 -- Name: GET_UOM_LOV
63 --
64 -- Input parameters:
65 -- p_Organization_Id which restricts LOV SQL to current org
66 -- p_Inventory_Item_Id restrict LOV for a given item
67 -- p_UOM_code which restricts LOV SQL to the user input text
68 -- e.g. Ea%
69 --
70 -- Output parameters:
71 -- x_UOMS returns LOV rows as reference cursor
72 --
73 -- Functions: This procedure returns LOV rows for a given org, item and
74 -- user input text
75 --
76
77
78 PROCEDURE get_uom_lov(x_uoms OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_uom_code IN VARCHAR2);
79
80 -- Name: GET_UOM_LOV_RCV
81 --
82 -- Input parameters:
83 -- p_Organization_Id which restricts LOV SQL to current org
84 -- p_Inventory_Item_Id restrict LOV for a given item
85 -- p_uom_type restrict LOV to certain UOM type
86 -- p_UOM_code which restricts LOV SQL to the user input text
87 -- e.g. Ea%
88 --
89 -- Output parameters:
90 -- x_UOMS returns LOV rows as reference cursor
91 --
92 -- Functions: This procedure returns UOM LOV rows for a given org, item and
93 -- user input text.
94 -- This API is for RECEIVING transaction only
95 --
96
97 PROCEDURE get_uom_lov_rcv(x_uoms OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_item_id IN NUMBER, p_uom_type IN NUMBER, p_uom_code IN VARCHAR2);
98
99 -- Name: GET_LOT_ITEMS_LOV
100 --
101 -- Input parameters:
102 -- I/P Parameters OUT Cursor containing the LOV
103 -- IN Organization ID (N)
104 -- IN Lot Number (S)
105 -- IN Transaction ID (N)
106 -- IN Current Value entered (S)
107 --
108 -- Output parameters:
109 -- x_Items returns LOV rows as reference cursor
110 -- Functions: This procedure projects item details from mtl_system_items_kfv for the
111 -- items that are valid for the given i/p lot number and org_id
112 --
113
114
115 PROCEDURE get_lot_items_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lot_number IN VARCHAR2, p_transaction_type_id IN VARCHAR2, p_concatenated_segments IN VARCHAR2);
116
117 -- Name: GET_LOT_ITEM_DETAILS
118 --
119 -- Input parameters:
120 -- I/P Parameters OUT Revision Control Code (N)
121 -- OUT Serial Number Control Code (N)
122 -- OUT Restrict Subinventories Code (N)
123 -- OUT Restrict Locators Code (N)
124 -- OUT Primary UOM Code (S)
125 -- OUT Shelf Life Code (N)
126 -- OUT Shelf Life Days (N)
127 -- OUT Allowed Units Lookup Code (N)
128 -- OUT Lot Status Enabled (S)
129 -- OUT Default Lot Status Id (N)
130 -- OUT Return Status (S)
131 -- OUT Message Count (N)
132 -- OUT Message Data (S)
133 -- IN Organization ID (N)
134 -- IN Lot Number (S)
135 -- IN Transaction ID (S)
136 -- IN Inventory Item ID (N)
137 --
138 -- Output parameters:
139 -- x_revision_qty_control_code
140 -- x_serial_number_control_code
141 -- x_restrict_subinventories_code
142 -- x_restrict_locators_code
143 -- x_location_control_code
144 -- x_primary_uom_code
145 -- x_shelf_life_code
146 -- x_shelf_life_days
147 -- x_allowed_units_lookup_code
148 -- x_lot_status_enabled
149 -- x_default_lot_status_id
150 -- x_return_status
151 -- x_msg_count
152 -- x_msg_data
153 --
154 -- Functions: This procedure projects item details from mtl_system_items_kfv for the
155 -- items that are valid for the given i/p org_id and lot number
156
157 PROCEDURE get_lot_item_details(
158 x_revision_qty_control_code OUT NOCOPY NUMBER
159 , x_serial_number_control_code OUT NOCOPY NUMBER
160 , x_restrict_subinventories_code OUT NOCOPY NUMBER
161 , x_restrict_locators_code OUT NOCOPY NUMBER
162 , x_location_control_code OUT NOCOPY NUMBER
163 , x_primary_uom_code OUT NOCOPY VARCHAR2
164 , x_shelf_life_code OUT NOCOPY NUMBER
165 , x_shelf_life_days OUT NOCOPY NUMBER
166 , x_allowed_units_lookup_code OUT NOCOPY NUMBER
167 , x_lot_status_enabled OUT NOCOPY VARCHAR2
168 , x_default_lot_status_id OUT NOCOPY NUMBER
169 , x_return_status OUT NOCOPY VARCHAR2
170 , x_msg_count OUT NOCOPY NUMBER
171 , x_msg_data OUT NOCOPY VARCHAR2
172 , p_organization_id IN NUMBER
173 , p_lot_number IN VARCHAR2
174 , p_transaction_type_id IN VARCHAR2
175 , p_inventory_item_id IN NUMBER
176 );
177
178 -- Name: GET_LOT_ITEM_DETAILS
179 --
180 -- Input parameters:
181 -- I/P Parameters OUT Revision Control Code (N)
182 -- OUT Serial Number Control Code (N)
183 -- OUT Restrict Subinventories Code (N)
184 -- OUT Restrict Locators Code (N)
185 -- OUT Primary UOM Code (S)
186 -- OUT Shelf Life Code (N)
187 -- OUT Shelf Life Days (N)
188 -- OUT Allowed Units Lookup Code (N)
189 -- OUT Lot Status Enabled (S)
190 -- OUT Default Lot Status Id (N)
191 -- OUT x_GRADE_CONTROL_FLAG (S)
192 -- OUT x_DEFAULT_GRADE (S)
193 -- OUT x_EXPIRATION_ACTION_INTERVAL (N)
194 -- OUT x_EXPIRATION_ACTION_CODE (S)
195 -- OUT x_HOLD_DAYS (N)
196 -- OUT x_MATURITY_DAYS (N)
197 -- OUT x_RETEST_INTERVAL (N)
198 -- OUT x_COPY_LOT_ATTRIBUTE_FLAG (S)
199 -- OUT x_CHILD_LOT_FLAG (S)
200 -- OUT x_CHILD_LOT_VALIDATION_FLAG (S)
201 -- OUT x_LOT_DIVISIBLE_FLAG (S)
202 -- OUT x_SECONDARY_UOM_CODE (S)
203 -- OUT x_SECONDARY_DEFAULT_IND (S)
204 -- OUT x_TRACKING_QUANTITY_IND (S)
205 -- OUT x_DUAL_UOM_DEVIATION_HIGH (N)
206 -- OUT x_DUAL_UOM_DEVIATION_LOW (N)
207 -- OUT Return Status (S)
208 -- OUT Message Count (N)
209 -- OUT Message Data (S)
210 -- IN Organization ID (N)
211 -- IN Lot Number (S)
212 -- IN Transaction ID (S)
213 -- IN Inventory Item ID (N)
214 --
215 -- Output parameters:
216 -- x_revision_qty_control_code
217 -- x_serial_number_control_code
218 -- x_restrict_subinventories_code
219 -- x_restrict_locators_code
220 -- x_location_control_code
221 -- x_primary_uom_code
222 -- x_shelf_life_code
223 -- x_shelf_life_days
224 -- x_allowed_units_lookup_code
225 -- x_lot_status_enabled
226 -- x_default_lot_status_id
227 -- x_GRADE_CONTROL_FLAG
228 -- x_DEFAULT_GRADE
229 -- x_EXPIRATION_ACTION_INTERVAL
230 -- x_EXPIRATION_ACTION_CODE
231 -- x_HOLD_DAYS
232 -- x_MATURITY_DAYS
233 -- x_RETEST_INTERVAL
234 -- x_COPY_LOT_ATTRIBUTE_FLAG
235 -- x_CHILD_LOT_FLAG
236 -- x_CHILD_LOT_VALIDATION_FLAG
237 -- x_LOT_DIVISIBLE_FLAG
238 -- x_SECONDARY_UOM_CODE
239 -- x_SECONDARY_DEFAULT_IND
240 -- x_TRACKING_QUANTITY_IND
241 -- x_DUAL_UOM_DEVIATION_HIGH
242 -- x_DUAL_UOM_DEVIATION_LOW
243 -- x_return_status
244 -- x_msg_count
245 -- x_msg_data
246 --
247 -- Functions: This overridden procedure projects item details including DUOM attributes
248 -- from mtl_system_items_kfv for the items that are valid for the given
249 -- i/p org_id and lot number
250
251 PROCEDURE get_lot_item_details(
252 x_revision_qty_control_code OUT NOCOPY NUMBER
253 , x_serial_number_control_code OUT NOCOPY NUMBER
254 , x_restrict_subinventories_code OUT NOCOPY NUMBER
255 , x_restrict_locators_code OUT NOCOPY NUMBER
256 , x_location_control_code OUT NOCOPY NUMBER
257 , x_primary_uom_code OUT NOCOPY VARCHAR2
258 , x_shelf_life_code OUT NOCOPY NUMBER
259 , x_shelf_life_days OUT NOCOPY NUMBER
260 , x_allowed_units_lookup_code OUT NOCOPY NUMBER
261 , x_lot_status_enabled OUT NOCOPY VARCHAR2
262 , x_default_lot_status_id OUT NOCOPY NUMBER
263 , x_GRADE_CONTROL_FLAG OUT NOCOPY VARCHAR2
264 , x_DEFAULT_GRADE OUT NOCOPY VARCHAR2
265 , x_EXPIRATION_ACTION_INTERVAL OUT NOCOPY NUMBER
266 , x_EXPIRATION_ACTION_CODE OUT NOCOPY VARCHAR2
267 , x_HOLD_DAYS OUT NOCOPY NUMBER
268 , x_MATURITY_DAYS OUT NOCOPY NUMBER
269 , x_RETEST_INTERVAL OUT NOCOPY NUMBER
270 , x_COPY_LOT_ATTRIBUTE_FLAG OUT NOCOPY VARCHAR2
271 , x_CHILD_LOT_FLAG OUT NOCOPY VARCHAR2
272 , x_CHILD_LOT_VALIDATION_FLAG OUT NOCOPY VARCHAR2
273 , x_LOT_DIVISIBLE_FLAG OUT NOCOPY VARCHAR2
274 , x_SECONDARY_UOM_CODE OUT NOCOPY VARCHAR2
275 , x_SECONDARY_DEFAULT_IND OUT NOCOPY VARCHAR2
276 , x_TRACKING_QUANTITY_IND OUT NOCOPY VARCHAR2
277 , x_DUAL_UOM_DEVIATION_HIGH OUT NOCOPY NUMBER
278 , x_DUAL_UOM_DEVIATION_LOW OUT NOCOPY NUMBER
279 , x_return_status OUT NOCOPY VARCHAR2
280 , x_msg_count OUT NOCOPY NUMBER
281 , x_msg_data OUT NOCOPY VARCHAR2
282 , p_organization_id IN NUMBER
283 , p_lot_number IN VARCHAR2
284 , p_transaction_type_id IN VARCHAR2
285 , p_inventory_item_id IN NUMBER
286 );
287
288 -- Name: GET_STATUS_ITEMS_LOV
289 --
290 -- Input parameters:
291 -- I/P Parameters OUT Cursor containing the LOV
292 -- IN Organization ID (N)
293 -- IN Current Value entered (S)
294 -- IN Subinventory Code (S)
295 -- IN Locator Id (N)
296 --
300 -- items that are valid for the given i/p org_id and entered Concatenated_Segments
297 -- Output parameters:
298 -- x_Items returns LOV rows as reference cursor
299 -- Functions: This procedure projects item details from mtl_system_items_kfv for the
301 -- If Subinventory code is provided, then Return Item list is filtered for this subinventory
302 -- If Locator code is provided and NOT EQUAL to -1, then Return Item List is filtered for this Locator id
303
304 PROCEDURE get_status_items_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_locator_id IN NUMBER);
305
306 -- Name: GET_SHIP_ITEMS_LOV
307 --
308 -- Input parameters:
309 -- I/P Parameters OUT Cursor containing the LOV
310 -- IN Organization ID (N)
311 -- IN Delivery ID (N)
312 -- IN Current Value entered (S)
313 --
314 -- Output parameters:
315 -- x_Items returns LOV rows as reference cursor
316 -- Functions: This procedure projects item details from mtl_system_items_kfv for the
317 -- items that are valid for the given i/p org_id and entered
318 -- Concatenated_Segments and within the given delivery id
319 --
320
321 PROCEDURE get_ship_items_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_delivery_id IN NUMBER, p_concatenated_segments IN VARCHAR2);
322
323 -- Name: GET_PHYINV_ITEM_LOV
324 --
325 -- Input parameters:
326 --
327 -- p_concatenated_segments - User inputted value
328 -- p_organization_id - Organization ID
329 -- p_subinventory_code - Subinventory
330 -- p_locator_id - Locator ID
331 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
332 -- p_physical_inventory_id - Restricts output to the given physical inventory
333 -- p_parent_lpn_id - Restricts output to only items with the
334 -- given parent lpn ID
335 --
336 -- Output parameters:
337 -- x_items - returns LOV rows as reference cursor
338 --
339 -- Functions: This procedure returns valid items that are associated
340 -- with the given physical inventory
341 --
342
343 PROCEDURE get_phyinv_item_lov(
344 x_items OUT NOCOPY t_genref
345 , p_concatenated_segments IN VARCHAR2
346 , p_organization_id IN NUMBER
347 , p_subinventory_code IN VARCHAR2
348 , p_locator_id IN NUMBER
349 , p_dynamic_entry_flag IN NUMBER
350 , p_physical_inventory_id IN NUMBER
351 , p_parent_lpn_id IN NUMBER
352 );
353
354 -- Name: GET_PHYINV_REV_LOV
355 --
356 -- Input parameters:
357 -- p_organization_id - restricts LOV SQL to current org
358 -- p_inventory_item_id - restrict LOV for a given item
359 -- p_revision - restricts LOV SQL to the user input text
360 -- e.g. A101%
361 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
362 -- p_physical_inventory_id - restricts LOV SQL to current physical inventory
363 -- p_parent_lpn_id - Restricts output to only items with the
364 -- given parent lpn ID
365 --
366 -- Output parameters:
367 -- x_revs returns LOV rows as reference cursor
368 --
369 -- Functions: This procedure returns LOV rows for a given org, item and
370 -- user input text for a given physical inventory
371 --
372
373 PROCEDURE get_phyinv_rev_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2, p_dynamic_entry_flag IN NUMBER, p_physical_inventory_id IN NUMBER, p_parent_lpn_id IN NUMBER);
374
375 -- Name: GET_PHYINV_UOM_LOV
376 --
377 -- Input parameters:
378 -- p_organization_id - Restricts LOV SQL to current org
379 -- p_inventory_item_id - Restricts LOV for a given item
380 -- p_uom_code - Restricts LOV SQL to the user input text
381 -- e.g. Ea%
382 -- p_dynamic_entry_flag - Indicates if dynamic entries are allowed
383 -- p_physical_inventory_id - Restricts LOV SQL to current physical inventory
384 --
385 -- Output parameters:
386 -- x_uoms returns LOV rows as reference cursor
387 --
388 -- Functions: This procedure returns LOV rows for a given org, item and
389 -- user inputted text for valid UOM's for a particular
390 -- physical inventory
391 --
392
393 PROCEDURE get_phyinv_uom_lov(x_uoms OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_uom_code IN VARCHAR2);
394
395 -- Name: GET_CONTAINER_ITEM_LOV
396 --
397 -- Input parameters:
398 --
399 -- p_concatenated_segments - Restricts output to user inputted value
400 -- p_organization_id - Organization ID
401 --
402 -- Output parameters:
403 -- x_items - returns LOV rows as reference cursor
404 --
405 -- Functions: This procedure returns valid container items
409 PROCEDURE get_container_item_lov(x_items OUT NOCOPY t_genref, p_concatenated_segments IN VARCHAR2, p_organization_id IN NUMBER);
406 -- within the given org
407 --
408
410
411 -- Name: GET_CYC_ITEM_LOV
412 --
413 -- Input parameters:
414 --
415 -- p_concatenated_segments - User inputted value
416 -- p_organization_id - Organization ID
417 -- p_subinventory_code - Subinventory
418 -- p_locator_id - Locator ID
419 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
420 -- p_cycle_count_header_id - Restricts output to the given cycle count
421 -- p_parent_lpn_id - Restricts output to only items with the
422 -- given parent lpn ID
423 --
424 -- Output parameters:
425 -- x_items - returns LOV rows as reference cursor
426 --
427 -- Functions: This procedure returns valid items that are associated
428 -- with the given cycle count
429 --
430
431 PROCEDURE get_cyc_item_lov(
432 x_items OUT NOCOPY t_genref
433 , p_concatenated_segments IN VARCHAR2
434 , p_organization_id IN NUMBER
435 , p_subinventory_code IN VARCHAR2
436 , p_locator_id IN NUMBER
437 , p_unscheduled_entry IN NUMBER
438 , p_cycle_count_header_id IN NUMBER
439 , p_parent_lpn_id IN NUMBER
440 );
441
442 -- Name: GET_CYC_REV_LOV
443 --
444 -- Input parameters:
445 -- p_organization_id - restricts LOV SQL to current org
446 -- p_inventory_item_id - restrict LOV for a given item
447 -- p_revision - restricts LOV SQL to the user input text
448 -- e.g. A101%
449 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
450 -- p_cycle_count_header_id - restricts LOV SQL to current cycle count
451 -- p_parent_lpn_id - Restricts output to only items with the
452 -- given parent lpn ID
453 --
454 -- Output parameters:
455 -- x_revs returns LOV rows as reference cursor
456 --
457 -- Functions: This procedure returns LOV rows for a given org, item and
458 -- user input text for a given cycle count
459 --
460
461 PROCEDURE get_cyc_rev_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2, p_unscheduled_entry IN NUMBER, p_cycle_count_header_id IN NUMBER, p_parent_lpn_id IN NUMBER);
462
463 -- Name: GET_CYC_UOM_LOV
464 --
465 -- Input parameters:
466 -- p_organization_id - Restricts LOV SQL to current org
467 -- p_inventory_item_id - Restricts LOV for a given item
468 -- p_uom_code - Restricts LOV SQL to the user input text
469 -- e.g. Ea%
470 -- p_unscheduled_entry - Indicates if unscheduled entries are allowed
471 -- p_cycle_count_header_id - Restricts LOV SQL to current cycle count
472 --
473 -- Output parameters:
474 -- x_uoms returns LOV rows as reference cursor
475 --
476 -- Functions: This procedure returns LOV rows for a given org, item and
477 -- user inputted text for valid UOM's for a particular
478 -- cycle count
479 --
480
481 PROCEDURE get_cyc_uom_lov(x_uoms OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_uom_code IN VARCHAR2);
482
483 -- Name: GET_INSPECT_ITEM_LOV
484 --
485 -- Input parameters:
486 -- p_organization_id organization where the inspection occurs
487 -- p_concatenated_segments restricts output to user entered search pattern for item
488 -- p_lpn_id id of lpn that contains items to be inspected
489 --
490 -- Output parameters:
491 -- x_items returns LOV rows as reference cursor
492 --
493 -- Functions:
494 -- This procedure returns the items that need inspection
495 --
496
497 PROCEDURE get_inspect_item_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_lpn_id IN NUMBER);
498
499 -- Name: GET_INSPECT_REVISION_LOV
500 --
501 -- Input parameters:
502 -- p_Organization_Id which restricts LOV SQL to current org
503 -- p_Inventory_Item_Id restrict LOV for a given item
504 -- p_lpn_id restricts items to lpn that is being inspected
505 -- p_Revision which restricts LOV SQL to the user input text
506 -- e.g. A101%
507 --
508 -- Output parameters:
509 -- x_Revs returns LOV rows as reference cursor
510 --
511 -- Functions: This procedure returns LOV rows for a given org, item and
512 -- user input text
513 --
514 --
515 --
516
517 PROCEDURE get_inspect_revision_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_lpn_id IN NUMBER, p_revision IN VARCHAR2);
518
519 -- Name: GET_OH_ITEM_LOV
520 --
521 -- Input parameters:
522 -- p_org_id which restricts LOV SQL to current org
523 -- p_subinventory_code Subinventory
527 -- p_item_id which restricts LOV SQL to the user input text
524 -- p_locator_id Locator ID
525 -- p_lpn_id restricts items to lpn that is being inspected
526 -- p_container_item_flag container or content item 'Y' = container item
528 -- e.g. A101%
529 --
530 -- Output parameters:
531 -- x_Items returns LOV rows as reference cursor
532 --
533 -- Functions: This procedure returns LOV rows for a given org, item and
534 -- user input text
535 --
536 --
537 --
538 PROCEDURE get_oh_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_subinventory_code VARCHAR2 DEFAULT NULL, p_locator_id VARCHAR2 DEFAULT NULL, p_container_item_flag VARCHAR2 DEFAULT NULL, p_item IN VARCHAR2);
539
540 -- Name: GET_CONT_ITEM_LOV
541 --
542 -- Input parameters:
543 -- p_org_id which restricts LOV SQL to current org
544 -- p_lpn_id lpn in which items are contained inside
545 -- p_item_id which restricts LOV SQL to the user input text
546 -- e.g. A101%
547 --
548 -- Output parameters:
549 -- x_Items returns LOV rows as reference cursor
550 --
551 -- Functions: This procedure returns LOV rows for a given org, item and
552 -- user input text
553 --
554 --
555 --
556 PROCEDURE get_cont_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN VARCHAR2, p_item IN VARCHAR2);
557
558 -- Name: GET_BP_ITEM_LOV
559 --
560 -- Input parameters:
561 -- p_org_id which restricts LOV SQL to current org
562 -- p_subinventory_code Subinventory
563 -- p_locator_id Locator ID
564 -- p_container_item_flag container or content item 'Y' = container item
565 -- p_source item source 1,2,3 (inventory/wip/rec)
566 -- p_item_id which restricts LOV SQL to the user input text
567 -- e.g. A101%
568 --
569 -- Output parameters:
570 -- x_Items returns LOV rows as reference cursor
571 --
572 -- Functions: This procedure returns LOV rows for a given org, item and
573 -- user input text
574 --
575 --
576 --
577 PROCEDURE get_bp_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_subinventory_code VARCHAR2 DEFAULT NULL, p_locator_id VARCHAR2 DEFAULT NULL, p_container_item_flag VARCHAR2 DEFAULT NULL, p_source VARCHAR2 DEFAULT NULL, p_item VARCHAR2);
578
579 -- Name: GET_CONT_UOM_LOV
580 --
581 -- Input parameters:
582 -- p_organization_id which restricts LOV SQL to current org
583 -- p_Inventory_Item_Id restrict LOV for a given item
584 -- p_lpn_id lpn in which items reside
585 -- p_UOM_code which restricts LOV SQL to the user input text
586 -- e.g. A101%
587 --
588 -- Output parameters:
589 -- x_UOMS returns LOV rows as reference cursor
590 --
591 -- Functions: This procedure returns LOV rows for a given org, item and
592 -- user input text
593 --
594 --
595 --
596 PROCEDURE get_cont_uom_lov(x_uoms OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_lpn_id IN NUMBER, p_uom_code IN VARCHAR2);
597
598 -- Name: GET_ALL_UOM_LOV
599 --
600 -- Input parameters:
601 -- p_UOM_code which restricts LOV SQL to the user input text
602 -- e.g. A101%
603 --
604 -- Output parameters:
605 -- x_UOMS returns LOV rows as reference cursor
606 --
607 -- Functions: This procedure returns LOV rows for a given org, item and
608 -- user input text
609 --
610 --
611 --
612 PROCEDURE get_all_uom_lov(x_uoms OUT NOCOPY t_genref, p_uom_code IN VARCHAR2);
613
614 -- Name: GET_INV_INSPECT_ITEM_LOV
615 --
616 -- Input parameters:
617 -- p_organization_id organization where the inspection occurs
618 -- p_concatenated_segments restricts output to user entered search pattern for item
619 -- p_source document source type being inspected
620 -- PO, INTSHIP, RMA, RECEIPT
621 -- p_source_id relevant document id based on p_source
622 -- po_header_id, shipment_header_id, oe_order_header_id,
623 -- receipt_num
624 --
625 -- Output parameters:
626 -- x_items returns LOV rows as reference cursor
627 --
628 -- Functions:
629 -- This procedure returns the items that need inspection
630 --
631
632 PROCEDURE get_inv_inspect_item_lov(x_items OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_source IN VARCHAR2, p_source_id IN NUMBER);
633
634 -- Name: GET_INV_INSPECT_REVISION_LOV
635 --
636 -- Input parameters:
637 -- p_Organization_Id which restricts LOV SQL to current org
638 -- p_Inventory_Item_Id restrict LOV for a given item
639 -- p_source document source type being inspected
640 -- PO, INTSHIP, RMA, RECEIPT
641 -- p_source_id relevant document id based on p_source
645 -- e.g. A101%
642 -- po_header_id, shipment_header_id, oe_order_header_id,
643 -- receipt_num
644 -- p_Revision which restricts LOV SQL to the user input text
646 --
647 -- Output parameters:
648 -- x_Revs returns LOV rows as reference cursor
649 --
650 -- Functions: This procedure returns LOV rows for a given org, item and
651 -- user input text
652 --
653 --
654 --
655
656 PROCEDURE get_inv_inspect_revision_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_source IN VARCHAR2, p_source_id IN NUMBER, p_revision IN VARCHAR2);
657
658 -- Name: GET_CGUPDATE_ITEM_LOV
659 --
660 -- Input parameters:
661 -- p_org_id - restricts LOV SQL to current org
662 -- p_lpn_id - restricts LOV SQL to given lpn
663 -- p_item - which restricts LOV SQL to the user input text
664 -- e.g. A101%
665 --
666 -- Output parameters:
667 -- x_items - returns item LOV rows as reference cursor
668 --
669 -- Functions: This procedure returns item LOV rows for a given org and
670 -- user input text for the item
671 PROCEDURE get_cgupdate_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item IN VARCHAR2);
672
673 -- Name: GET_CONTENT_REVISION_LOV
674 --
675 -- Input parameters:
676 -- p_organization_id - restricts LOV SQL to current org
677 -- p_inventory_item_id - restricts LOV SQL to given item
678 -- p_lpn_id - restricts LOV SQL to given lpn
679 -- p_revision - which restricts LOV SQL to the user input text
680 -- e.g. A101%
681 --
682 -- Output parameters:
683 -- x_items - returns item LOV rows as reference cursor
684 --
685 -- Functions: This procedure returns item LOV rows for a given org and
686 -- user input text for the item
687 PROCEDURE get_content_revision_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_lpn_id IN VARCHAR2, p_revision IN VARCHAR2);
688
689 -- Name: GET_SYSTEM_ITEM_LOV
690 --
691 -- Input parameters:
692 -- p_organization_id - restricts LOV SQL to current org
693 -- p_item_id - restricts LOV SQL to given item
694 -- Output parameters:
695 -- x_items - returns item LOV rows as reference cursor
696 --
697 -- Functions: This procedure returns item LOV rows for a given org and
698 -- user input text for the item
699 PROCEDURE get_system_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_item IN VARCHAR2);
700
701 -- Name: GET_SERIAL_ITEM_LOV
702 --
703 -- Input parameters:
704 -- p_organization_id - restricts LOV SQL to current org
705 -- p_serial - restricts LOV SQL to specific serial number
706 -- p_item_id - restricts LOV SQL to given item
707 -- Output parameters:
708 -- x_items - returns item LOV rows as reference cursor
709 --
710 -- Functions: This procedure returns item LOV rows for a given org, sn ,and
711 -- user input text for the item
712 PROCEDURE get_serial_item_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_serial IN VARCHAR2, p_item IN VARCHAR2);
713
714 --"Returns"
715 -- Name: GET_RETURN_ITEMS_LOV
716 --
717 -- Input parameters:
718 -- p_org_id - restricts LOV SQL to current org
719 -- p_lpn_id - restricts LOV SQL to given lpn
720 -- p_item_id - restricts LOV SQL to given item
721 --
722 -- Output parameters:
723 -- x_Items - returns item LOV rows as reference cursor
724 --
725 -- Functions: This procedure returns item LOV rows for a given org, lpn and
726 -- user input text for the item
727
728 PROCEDURE get_return_items_lov(x_items OUT NOCOPY t_genref, p_org_id IN NUMBER, p_lpn_id IN NUMBER, p_item IN VARCHAR2);
729
730 -- Name: GET_RETURN_REVISION_LOV
731 --
732 -- Input parameters:
733 -- p_organization_id - restricts LOV SQL to current org
734 -- p_inventory_item_id - restricts LOV SQL to given item
735 -- p_lpn_id - restricts LOV SQL to given lpn
736 -- p_revision - which restricts LOV SQL to the user input text
737 -- e.g. A101%
738 --
739 -- Output parameters:
740 -- x_Revs - returns Revision LOV rows as reference cursor
741 --
742 -- Functions: This procedure returns Revision LOV rows for a given org,
743 -- inventory item id, lpn id and user input revision text
744 PROCEDURE get_return_revision_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_lpn_id IN VARCHAR2, p_revision IN VARCHAR2);
745
746 --"Returns"
747
748 /* Direct Shipping */
749
750 PROCEDURE get_vehicle_lov(x_vehicle OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_concatenated_segments IN VARCHAR2);
751
752 --Bug#2310308
753 PROCEDURE get_direct_ship_uom_lov(x_uom OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lpn_id IN NUMBER, p_uom_text IN VARCHAR2);
754
755 --Bug#2310308
756 /* Direct Shipping */
757
758 --Bug#2252193
759 PROCEDURE get_deliver_revision_lov(x_revs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_po_header_id IN NUMBER, p_shipment_header_id IN NUMBER, p_revision IN VARCHAR2);
760
761 --Bug# 2647045
762 FUNCTION conversion_order(p_uom_string VARCHAR2) RETURN NUMBER;
763
764 --Bug# 2647045
765 FUNCTION get_conversion_rate(p_from_uom_code varchar2,
766 p_organization_id NUMBER,
767 p_item_id NUMBER)
768 RETURN VARCHAR2;
769
770
771 -- Name: GET_MO_ITEM_LOV
772 --
773 -- Input parameters:
774 -- p_Organization_Id which restricts LOV SQL to current org
775 -- p_Concatenated_segments which resticts the LOV to the Item that user has enteredA
776 -- p_header_id HeaderId from mtl_txn_request_lines
777 -- Output parameters:
778 -- x_Items returns LOV rows as reference cursor
779 --
780 -- Functions: This procedure returns LOV rows for a given org, item and
781 -- header_id
782 --
783 --
784 --
785 PROCEDURE get_mo_item_lov
786 (x_Items OUT NOCOPY t_genref,
787 p_Organization_Id IN NUMBER,
788 p_Concatenated_Segments IN VARCHAR2,
789 p_header_id IN VARCHAR2);
790
791 --added the procedure for handling lpn and loose in update status page for LPN status project
792 PROCEDURE get_ostatus_items_lov(x_items OUT NOCOPY t_genref,
793 p_organization_id IN NUMBER,
794 p_lpn IN VARCHAR2,
795 p_concatenated_segments IN VARCHAR2,
796 p_subinventory_code IN VARCHAR2,
797 p_locator_id IN NUMBER);
798
799
800
801 END inv_ui_item_lovs;