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